Функции для работы со ссылками и массивами
ВПР (искомое_значение; таблица; номер_столбца; интервальный_просмотр)- ищет значение в крайнем левом столбце и возвращает значение ячейки, находящейся в указанном столбце той же строки. (просматривает сверху вниз левый столбец таблицы пока не встретит искомое_значение, а затем просматривает строку до столбца с номером номер_столбца)
искомое_значение-значение, которое должно быть найдено в первом столбце массива (значение, ссылка или строка текста)
таблица-таблица с текстом, числами или логическими значениями, в котором производится поиск данных; может быть ссылкой или имя диапазона.
номер_столбца-номер столбца в таблице, из которого нужно вернуть значение. Первый столбец значений имеет номер 1.
интервальный_просмотр- логическое значение, определяющее, точно (ЛОЖЬ) или приближенно (ИСТИНА или отсутствие значения) должен производиться поиск в первом отсортированном столбце (отсортированном по возрастанию)
Функций МS Ехсеl - ВПР, облегчает работу по заполнению таблиц при необходимости использования справочных таблиц. Примером может служить заполнение накладной, в которой при вводе кода товара должно автоматически появляться наименование товара, цена товара, фирма производитель и т.д.
На первом шаге, на отдельном листе создайте справочную таблицу и присвойте ей имя «Справочник», например: __________________________________
| Код товара | Название товара | Цена |
| 0001 | Товар1 | $200,00 |
| 0002 | Товар2 | $250,00 |
| 0101 | Товар3 | $280,00 |
| 1002 | Товар3 | $300,00 |
Табл. 3. Справочник товаров
|
|
|
Предположим, на другом листе требуется вводить данные о проданных товарах. Создайте таблицу с заголовками столбцов: Код товара, Название товара, Цена, Кол-во, Сумма:________
| Код товара | Название товара | Цена | Кол-во | Сумма |
| 0101 | =ВПР(А2;справочник;2;0) | =ВПР(А2;справочник;3 ;0) | ||
Табл. 4. Фрагмент накладной на продажу товаров
Введите код товара 0001. В первую строку табл. 4 в столбце «Название товара»введите формулу =ВПР(А2;справочник;2;0).
Функция ВПР имеет следующие аргументы:
Искомое_значение:в этом окошке указывается значение (код товара), определяющее остальные параметры (наименование товара, его цену). Это значение будет искаться в первом столбце таблицы-справочника.
Таблица:в этом окошке указывается адрес или имя справочной таблицы (в нашем примере - это имя «справочник»).
Номер_столбца:в этом окошке указывается номер столбца, содержащий соответствующую характеристику значения, введенного в первом окошке (в нашем случае номер 2 определяет столбец «Наименование СРШ)
|
|
|
Интервальный_просмотр:имеет два значения «0», если таблица-справочник не отсортирована по первому столбцу, и «1», если она отсортирована по первому столбцу.
Для определения цены товара, код которого будет введен в ячейке А2, следует ввести формулу: ВПР(А2;справочник;3;0).
При копировании функции ВПР вниз при отсутствии данных в столбце А в ячейках появляется сообщение об ошибке #Н/Д. Чтобы избежать его, следует предусмотреть ввод "пробела" в случае, если код соответствующего СР11 еще не выбран. Реализовать это можно с помощью функции ЕСЛИ. В рассматриваемой ситуации функция будет иметь вид: =ЕСЛИ(А2=0;""; ВПР(А2;справочник;2;0)).При вводе кода товара в столбцах «Наименование товара и «Цена» появятся соответствующие коду значения. Для ввода кодов товаров мы рекомендуем использовать возможность МS Ехсеl по вводу значений из фиксированного списка с помощью инструмента Данные\Проверка.
Использование функций в Ехсеl
Задание 1. Расчет значения премии сотрудникам с использованием функции ЕСЛИ
| А | В | С | D | Е |
| ФИО | Тарифная ставка(руб.) | Отработано часов | Начислено | Премия |
| Сидоров В.И. | 100 | 25 | ||
| Андреева И.Т. | 150 | 30 | ||
| Ковалева О.А. | ПО | 15 | ||
| Лобанов А.О. | 150 | 20 |
|
|
|
Если сотрудник отработал больше 20 часов, то назначим премиальный коэффициент равный 20%, в противном случае -10%.
Для расчета Начислено первому сотруднику щелкните в ячейке В4, введите формулу В2*С2 Для расчета премии первому сотруднику щелкните в ячейке Е4. Вызовите Мастер функций.
В категории Логические выберите функцию ЕСЛИ. В соответствующем диалоговом окне следует указать:
• в окошке Лог_выражение:С4>20;
• в окошке Значение_если_истина:Б4*$Е$2;
• в окошке Значение_если_ложь: 04*$Е$1. Нажмите кнопку ОК.
Задание 2. Начисление «сложной» премии.
Рассмотрим пример начисления премии по следующему алгоритму: Если отработано меньше или 10 часов, то премия не назначается.
Если отработано больше или 20 часов, то назначается премия в размере 20% от Начислено. В противном случае (отработано больше 10 часов и меньше 20 часов) назначается премия в размере 10% от Начислено.
Щелкните в ячейке Е4. Вызовите функцию ЕСЛИ. Введите:
• в окошке Лог_выражение:С4>=20;
• в окошке Значение_если_истина:Э4*$Е$2;
Щелкните в окошке Значение_если_ложь и вызовите вторую функцию ЕСЛИ. Появится чистое диалоговое окно вложенной функции ЕСЛИ, в котором нужно ввести:
|
|
|
• в окошке Лог_выражение: С4<=10;
Дата добавления: 2018-04-05; просмотров: 257; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!