Краткие сведения. Очень часто в практической работе встречаются задачи, для решения которых требуется использовать справочную таблицу

Очень часто в практической работе встречаются задачи, для решения которых требуется использовать справочную таблицу. Например, по заданному номеру телефона найти фамилию или по названию государства - его столицу. Для решения подобных задач следует составить справочную таблицу, в первом столбце которой расположены поисковые значения (все номера телефонов), а во втором – фамилии абонентов. Специальная функция ВПР осуществит поиск в справочной таблице значений, соответствующих введенному вами аргументу и автоматически подставит найденное значение в той же строке из указанного второго столбца таблицы.

Синтаксис функции ВПР (вертикальный поисковый ряд).

ВПР (искомое значение; таблица; номер столбца; интервальный просмотр):

- Искомое значение – это значение, которое должно быть найдено в первом столбце массива. Оно может быть значением, ссылкой или текстовой строкой.

- Таблица – таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала.

- Номер столбца – это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение.

- Интервальный просмотр – в это окно вводится 0(ЛОЖЬ), если необходимо точное соответствие или 1(ИСТИНА), если соответствие приближенное. По умолчанию устанавливается приближенное соответствие, при котором, если точное значение не найдено, возвращается максимальное из значений, меньших, чем искомое. Если введена ИСТИНА, то значения в первом столбце массива должны быть отсортированы в возрастающем порядке.

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

Итак, составьте на отдельном рабочем листе (в нашем примере на первом рабочем листе) таблицу следующего вида, как показано на рисунке 30. Затем перейдите на другой рабочий лист (например, второй лист) и составьте выписку из ведомости на учащегося, как показано на рисунке 31.

Для того чтобы получить в колонке Количество часов число часов, из справочной таблицы вызовите Мастер функции и категории Ссылки и массивы, выберите функцию ВПР. Заполните аргументы функции ВПР, как показано на рисунке 33, и нажмите ОК.

Скопируйте функцию из ячейки В3 в В4.

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

Функция ПОИСКПОЗ очень похожа на функцию ВПР, но она возвращает позицию искомого значения в массиве, а не само значение. Синтаксис: ПОИСКПОЗ(искомое_значение; массив; тип_сопоставления). Искомое_значение — значение, используемое при поиске значения в таблице. Просматриваемый_массив — блок, состоящий из одного столбца или одной строки.. Тип_сопоставления — число -1, 0 или 1. Функция ПОИСКПОЗ находит первое значение, которое в точности равно аргументу искомое_значение в неупорядоченном массиве.

Например, дополним таблицу Выписка успеваемость колонкой «Номер в учебном плане», для чего введем это название в ячейку D3, а в ячейку D3 формулу.

= ПОИСКПОЗ(А3;ЛИСТ1!$А$2:$А$5;0).

Формула возвращает значение 3, так как содержимое ячейки А3 Физика – третий элемент в массиве ЛИСТ1!$А$2:$А$5 (Математика, Информатика, Физика, Философия).


Рис. 30. Справочная таблица на первом листе	Рис. 31. Основная таблица, в которой присутствуют справочные данные