Материал: ЭКСЕЛЬ2 2013 Методичка Расширенные возможности

21

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Если по Коду страны-изготовителя (критерий) требуется определить Название страны, которое находится левее критерия, то стоит воспользоваться функциями ПОИСКПОЗ и ИНДЕКС.

В случае если данные хранятся в строках:

Например, если известен Код страны-изготовителя (критерий) и необходимо из таблицы получить данные строки Компания-Изготовитель, которые находятся ниже критерия, то удобней воспользоваться функцией ГПР (применяется для горизонтальных таблиц).

Если по Коду страны-изготовителя (критерий) требуется определить Название страны, которое находится выше критерия, то стоит воспользоваться функциями ПОИСКПОЗ и ИНДЕКС.

ВПР(Искомое_значение;Таблица;Номер_столбца;Интервальный_просмотр) – ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.

VLOOKUP(Lookup_value;Table_array;Col_index_num;Range_lookup)

Искомое_значение [Lookup_value] – значение, по которому ищем совпадение в первом столбце другой таблицы.

Таблица [Table_array] – таблица, в которой в первом столбце осуществляется поиск искомого значения. Необходимо выделить таблицу таким образом, чтобы в первом левом столбце было искомое значение и правее, включая столбец для ответа по задаче. Как правило, таблица при копировании формулы должна оставаться неизменной, поэтому она должна быть в абсолютной адресации.

Номер_столбца [Col_index_lookup] – номер столбца-ответа по задаче (целое число), считается в выделенной таблице.

Просматриваемый массив [Range_lookup] – число 0 или 1.

0 (Ложь [False]) – ищет первое точное совпадение при просмотре сверху вниз (если не находит – #Н/Д [#N/A]).

1 (Истина [True]) – если нет совпадения, то выдает max_значение < искомого, при этом выделенная таблица должна быть отсортирована по первому столбцу по возрастанию.

22

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Определить значение процента бонуса каждого сотрудника в зависимости от его кода.

=ВПР(D2;$H$2:$J$11;3;0) – ищет значение IVN (ячейка D2) в ячейках 1-го столбца (H) указанной таблицы. Результат формулы – значение ячейки 3-го столбца (J) выделенной таблицы строки со значением IVN.

ПРАКТИКУМ:

Открыть файл 01_4 Функции Ссылки и массивы.

На листе ЗАДАНИЕ1, Товары 2012, Товары 2012, ЗАДАНИЕ2 выполнить задание.

Сохранить изменения в файле.

ГПР(Искомое_значение;Таблица;Номер_строки;Интервальный_просмотр) – ищет значение в крайней верхней строке таблицы и возвращает значение в том же столбце из указанной строки таблицы.

HLOOKUP(Lookup_value;Table_array;Row_index_num; Range_lookup)

Искомое_значение [Lookup_value] – значение, которое должно быть найдено в первой строке другой таблицы.

Таблица [Table_array] – таблица, в которой в первой стоке осуществляется поиск искомого значения. Необходимо выделить таблицу таким образом, чтобы в первой стоке было искомое значение и ниже строки, включая строку ответа по задаче.

Номер_стоки [Row_index_lookup] – номер строки-ответа по задаче (целое число), считается в выделенной таблице.

Просматриваемый массив [Range_lookup] – число 0 или 1.

0 (Ложь [False]) – ищет первое точное совпадение (если не находит - #Н/Д [#N/A]).

1 (Истина [True]) – если нет совпадения, то выдает max_значение < искомого, при этом выделенная таблица должна быть отсортирована по первой строке по возрастанию.

23

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Определить для указанного Кода Заказа 10250 значение Кода Сотрудника из таблицы, расположенной в строках с 1-й по 4.

=ГПР(A8;1:3;3;0) – ищет значение кода заказа 10250 (ячейка A8) в ячейках 1-й строки (Код заказа) указанной таблицы. Результат формулы – значение ячейки 3-й строки таблицы (Код Сотрудника) столбца со значением кода заказа 10259.

ПРАКТИКУМ:

Открыть файл 01_4 Функции Ссылки и массивы.

На листе ЗАДАНИЕ3 выполнить задание.

Сохранить изменения в файле.

ПОИСКПОЗ(Искомое_значение;Просматриваемый_массив;Тип_сопоставления) – находит

относительное положение элемента в диапазоне данных (поиск позиции).

MATCH(Lookup_value; Lookup_array; Match_type)

Искомое_значение [Lookup_value] – значение, для которого определяется относительное положение в диапазоне данных.

Просматриваемый_массив [Lookup_array] – диапазон ячеек, в котором производится поиск (один столбец или одна строка).

Тип_сопоставления [Match_type] – может принимать значения 1, 0 и -1. Определяет, каким образом Искомое_значение сопоставляется со значениями в аргументе Просматриваемый_массив (0 – точное совпадение).

Если функция ПОИСКПОЗ не находит соответствующего значения при точном совпадении, то возвращается значение ошибки #Н/Д [#N/A].

24

Microsoft Excel 2013. Уровень 2. Расширенные возможности

ПРИМЕР: Определить номер строки в таблице, в которой находится значение Кода заказа

10250.

=ПОИСКПОЗ(G2;D:D;0) – находит для значения из ячейки G2 (Код заказа 10250) относительную позицию в просматриваемом массиве – в столбце D (Код Заказа).

ИНДЕКС(Массив;Номер_строки;Номер_столбца) – возвращает значение ячейки из диапазона,

заданной номером строки и номером столбца.

INDEX(Array;Row_num;Column_num)

Массив [Array] – таблица (массив), состоит из строк и столбцов. Для определения данных конкретного столбца, имеет смысл выделять только этот (один) столбец.

Номер_строки [Row_num] – номер строки в массиве, из которой нужно определить значение.

Номер_столбца [Column_num] – номер столбца в массиве, из которого определяется значение. Если в массиве был выделен один столбец, то поле заполнять не нужно.

ПРИМЕР: Определить значение Получателя, если известен номер строки, в которой он расположен.

=ИНДЕКС(B:B;G3) – определение значения Получателя в таблице (столбец В) с заданным номером строки (значение ячейки G3).

25

Microsoft Excel 2013. Уровень 2. Расширенные возможности

Функции ПОИСКПОЗ [MATCH] и ИНДЕКС[INDEX], применяемые последовательно, позволяют по найденному значению в одном столбце найти соответствующее значение из другого столбца.

ПРИМЕР: Определить значение Получателя для указанного Кода Заказа 10250.

=ИНДЕКС(B:B;ПОИСКПОЗ(G2;D:D;0)) – определение Получателя (данные столбца В) в номере строки, вычисляемым в столбце D для указанного Кода Заказа (значение ячейки G2).

ПРАКТИКУМ:

Открыть файл 01_4 Функции Ссылки и массивы.

На листе ЗАДАНИЕ4 выполнить задание.

Сохранить изменения в файле и закрыть его.

САМОСТОЯТЕЛЬНЫЕ УПРАЖНЕНИЯ

Открыть файл УПРАЖНЕНИЯ ЭКСЕЛЬ2. Выполнить задание на листе 4-1, 4-2 и 4-3.

Логические функции

Логические функции являются неотъемлемыми компонентами многих формул. Они используются в случаях, когда результат обработки зависит от выполнения некоторого условия, заданного в виде логического выражения.

ЕСЛИ(Лог_выражение;Значение_если_истина;Значение_если_ложь) – возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

IF(Logical_test;Value_if_true;Value_if_false)

Лог_выражение [Logical_test] – выражение, относительно которого можно судить: истина это или ложь. Необходимо задать условие, используя ссылки на адреса ячеек: >, >=, <, <=, <>, =. Можно использовать функции: И [AND], ИЛИ [OR].

Значение_если_истина [Value_if_True] – ввести текст, число или формулу для определения значения в случае, если условие будет выполнено.

Значение_если_ложь [Value_if_False] – ввести текст, число или формулу для определения значения в случае, если условие не будет выполнено.