Использование функций рабочего листа для поиска данных в списках

ЛЕКЦИЯ 7 В Excel существуют специальные функции, которые позволяют автоматически находить данные по критериям в больших таблицах. В  этой лекции описаны функции списков для поиска элементов в массивах данных, приведены примеры использования функций поиска при решении различных прикладных задач. Внимательно изучите назначение и особенности каждой из функций для того, чтобы правильно выбрать ту или иную функцию при решении конкретной задачи.   ИСПОЛЬЗОВАНИЕ ФУНКЦИЙ РАБОЧЕГО ЛИСТА ДЛЯ ПОИСКА ДАННЫХ В СПИСКАХ Функции просмотра элементов массива В Excel встроены функции вертикального и горизонтального просмотра, принадлежащие к категории «Ссылки и массивы». Функция ВПР() – вертикального просмотра – ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Формат функции =ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр), где искомое_значение – значение, которое должно быть найдено в первом столбце таблицы. Искомое_значение может быть значением, ссылкой или текстовой строкой. При этом регистр не учитывается (т.е. строчные и прописные буквы не различаются); таблица – это таблица с информацией, в которой осуществляется поиск данных. Можно использовать ссылку на интервал или имя интервала, например, «Список»; номер_столбца – это номер столбца массива таблица, в котором должно быть найдено соответствующее значение.  Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента таблица и так далее. Если номер_столбца меньше 1, то функция ВПР() возвращает значение ошибки #ЗНАЧ!; если номер_столбца больше, чем количество столбцов в аргументе таблица, то функция ВПР() возвращает значение ошибки #ССЫЛ!; интервальный_просмотр – логическое значение, которое определяет точный или приближенный поиск. Если интервальный_просмотр имеет значение ИСТИНА, то осуществляется приближенный поиск, и значения в первом столбце аргумента таблица должны быть расположены в возрастающем порядке (например, -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА), иначе функция ВПР() может возвратить неправильный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, то осуществляется точный поиск и таблица может быть не отсортированной. По умолчанию вектор для поиска значения в таблице должен быть упорядочен по возрастанию.        Замечания. 1. Если ВПР() не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение (ведь столбец упорядочен по возрастанию!). 2. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, то функция ВПР() возвращает значение ошибки #Н/Д. 3. Если ВПР не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то ВПР() возвращает значение ошибки #Н/Д. 4. Для обработки подобной ошибки удобно использовать функцию =ЕНД() из категории «Проверка свойств и значений». Функция  =ЕНД() возвращает значение «истина», когда ее аргументами являются значения  #НД.   Функция   ГПР() – горизонтального просмотра – просматривает таблицу по строкам, начиная с первой. Функция ищет значение в первой строке таблицы и возвращает значение в том же столбце из указанной в списке аргументов строки. Функция по назначению и формату похожа на ВПР(). Все пояснения и замечания к функции ВПР() относятся и к функции горизонтального просмотра. Формат функции   =ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр), где искомое_значение – значение, которое необходимо найти в первой строке таблицы, может быть значением, ссылкой или текстовой строкой; таблица – массив, в первой строке которого осуществляется поиск значений, может быть задан ссылкой на диапазон или именем диапазона; номер_строки – номер строки, из которой нужно вернуть значение. Если функция находит соответствие, то возвращает данные из указанного номера строки; интервальный_просмотр – логическое выражение, определяющее способ поиска в первой строке. Если интервальный_просмотр имеет значение ИСТИНА, то осуществляется приближенный поиск и первая строка таблицы должна быть упорядочена по возрастанию (например, -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА), иначе функция ГПР() может выдать неверный результат. Если интервальный_просмотр имеет значение ЛОЖЬ, осуществляется точный поиск и таблица может быть не отсортированной.      Замечания. 1. По умолчанию интервальный_просмотр равен 1 и вектор для поиска значения в таблице должен быть упорядочен по возрастанию. 2. Если ГПР() не может найти искомое_значение и интервальный_просмотр имеет значение ИСТИНА, то используется наибольшее значение, которое меньше, чем искомое_значение. 3. Если искомое_значение меньше, чем наименьшее значение в первой строке аргумента таблица, то функция ВПР возвращает значение ошибки #Н/Д. 4. Если ГПР() не может найти искомое_значение и интервальный_просмотр имеет значение ЛОЖЬ, то функция вернет значение ошибки #Н/Д. Рассмотрим пример использования функции вертикального просмотра. Пример 1. Пусть в таблице «Справочник тарифов»  содержится информация о тарифах на междугородние разговоры. Рассчитаем в таблице «Учет междугородних разговоров» сумму к оплате на основании данных о тарифах и времени разговора в минутах без скидок, а также сумму к оплате с учетом того, что в выходные дни стоимость минуты уменьшается на 30%. Порядок действий: 1. На рабочем листе создадим два списка исходными данными (рисунок 1). Рисунок 1 – Диапазоны с исходными данными 2. В ячейку H4 введем формулу   =ДЕНЬНЕД(G4;2) из категории Дата и время и скопируем ее до конца диапазона значений столбца. Это позволит далее определить, какие из дней являются выходными. 3. В ячейку J4 введем формулу   =ВПР(F4;$B$3:$C$10;2;0)*I4  и скопируем ее до конца диапазона. Вид диалогового окна функции приведен на рисунке 2. Теперь заменим одно из значений населенного пункта (например, Каргат на Иркутск). Как изменились результаты? Если в справочной таблице отсутствуют данные о тарифах для какого- либо населенного пункта, то функция ВПР() возвращает в соответствующих ячейках значение #Н/Д (рисунок 3). Учтем в формуле возможность возникновения подобной ситуации.   Рисунок 2 – Диалоговое окно функции ВПР() Рисунок 3 – Пример использования функции вертикального просмотра 4. Для обработки ошибки будем использовать функции ЕНД() и ЕСЛИ(). В ячейку J4 введем новую формулу =ЕСЛИ(ЕНД(ВПР(F4;$B$3:$C$10;2;0));"нет_данных";ВПР(F4;$B$3:$C$10;2;0)*I4)  и скопируем ее до конца диапазона (рисунок 4). Результат обработки ошибки в столбце J приведен на рисунке 5. 5. Для расчета суммы к оплате с учетом дня недели в ячейку K4 введем формулу проверки сложных условий =ЕСЛИ(J4="нет_данных";"";ЕСЛИ(ИЛИ(H4=7;H4=6);J4*0,7;J4)) (рисунок 5). Рисунок 5 – Пример использования проверки сложных условий Пример 2. Следующий пример демонстрирует расчет сумм, выставленных к оплате оператором в текущем месяце. Компания предоставляет абонентам Интернет - услуги и предлагает абонентам две тарифные группы, в которые входит по пять тарифных планов: с абонентской платой и бесплатным трафиком выхода в Интернет и с неограниченной скоростью передачи данных. В связи с этим для расчетов используются две отдельные таблицы тарифных групп (рисунок 6). В ячейку I5 введена формула, которая в зависимости от принадлежности тарифа к определенной группе (Б/Т или Н/С), возвращает сумму к оплате, включающую величину абонентской платы и платы за входящий трафик. =ЕСЛИ(I5="Б/Т";ВПР(J5;$A$4:$E$9;2;0)+ВПР(J5;$A$4:$E$9;3;0)*K5; ВПР(J5;$A$14:$E$19;2;0)+ВПР(J5;$A$14:$E$19;3;0)*K5) Результаты расчетов приведены на рисунке 7. Рисунок 6 – Справочники тарифов Рисунок 7 – Расчет суммы к оплате для абонентов, пользующихся Интернет – услугами Для обработки возможных ошибок воспользуемся функцией ЕСЛИОШИБКА(), которая выполняет проверку на предмет ошибки в формуле и, если ошибка не обнаружена,  возвращается значение, иначе выводится заданный текст (сравните с вариантом обработки ошибки в предыдущем примере). В ячейку L3 введем следующую формулу: =ЕСЛИ(I5="Б/Т";ЕСЛИОШИБКА((ВПР(J5;$A$4:$E$9;2;0)+ВПР(J5;$A$4:$E$9;3;0)*K5);"нет_данных");ЕСЛИОШИБКА((ВПР(J5;$A$14:$E$19;2;0) +ВПР(J5;$A$14:$E$19;3;0)*K5);"нет данных")) Результат расчета входящего трафика и суммы к оплате для каждого абонента с обработкой ошибок представлен на рисунке 8. Рисунок 8 – Список счетов абонентов Функции ИНДЕКС() и  ПОИСКПОЗ()    Функции ИНДЕКС() и ПОИСКПОЗ()  также относятся к категории Ссылки и массивы. Функция ПОИСКПОЗ() возвращает относительное положение элемента массива, который соответствует заданному значению. Функция ПОИСКПОЗ() используется вместо функций типа ПРОСМОТР(), если нужна позиция элемента в диапазоне, а не сам элемент. Формат функции  =ПОИСКПОЗ(искомое_значение; массив;тип_сопоставления), где   искомое_значение – это значение, поиск которого ведется. Искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую число, текст или логическое значение; массив   – это непрерывный интервал ячеек, возможно, содержащих искомые значения. Просматриваемый_массив может быть массивом или ссылкой на массив; тип_сопоставления   – это число -1, 0 или 1. Тип_сопоставления указывает,  как MS Excel сопоставляет искомое_значение  со значениями в просматриваемом массиве. Если тип_сопоставления равен 1, то функция ПОИСКПОЗ() находит наибольшее значение, которое равно или меньше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА. Если тип_сопоставления не указан, то предполагается, что он равен 1. Если тип_сопоставления равен 0, то функция ПОИСКПОЗ() находит первое значение, которое в точности равно аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен. Если тип_сопоставления равен -1, то функция ПОИСКПОЗ() находит наименьшее значение, которое равно и больше, чем искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, ..., 2, 1, 0, -1, -2, ..., и так далее. Замечания. 1. ПОИСКПОЗ() не различает регистры при сопоставлении текстов. 2. Если функция ПОИСКПОЗ() не находит соответствующего значения, то возвращается значение ошибки #Н/Д. 3. Возможен поиск значения по маске. Если тип_сопоставления равен 0 и искомое_значение является текстом, то искомое_значение может содержать символы шаблона, звездочка (*) и знак вопроса (?). Звездочка соответствует любой последовательности символов, знак вопроса соответствует любому одиночному символу.   Пример 1. Фирма участвует в высокотехнологичном проекте. Определим срок возмещения первоначальных инвестиционных затрат при заданном прогнозируемом годовом движении денежных средств на 10-летний период без учета изменения стоимости денежных средств во времени. Начальные инвестиции составляют 6000000р., поток денег в первый год – 500000р., ожидаемый ежегодный прирост денежных потоков - 20%. Для решения задачи 1. Введем исходные данные на рабочем листе (рисунок 9). 2. В ячейку В8 введем поток денежных средств на начало периода. 3. В диапазоне В8:В18 вычислим денежные потоки в каждом году с учетом предполагаемого роста (значение в ячейке В3). 4. В диапазоне С8:С18 вычислим совокупные ежегодные потоки денежных средств. 5. В ячейку В5 введем функцию для нахождения срока окупаемости. В нашем случае нужно отыскать год, в котором совокупный поток денежный средств превысит нулевое значение. Функция возвратит номер строки, содержащей первый год, в течение которого денежный поток станет положительной величиной.  Столбец  С упорядочен по возрастанию и в формуле тип сопоставления равен 1. Рисунок 9 – Вид рабочего листа с исходными данными и расчетными формулами Результат решения представлен на рисунке 10. Рисунок 10 – Результат расчета срока окупаемости с помощью функции ПОИСКПОЗ() Пример 2. Фирма учитывает грейд сотрудников (квалификацию, степень ответственности, сложность труда и др.) при оплате труда. В таблице на рисунке 83 представлены данные о грейде 20 сотрудников фирмы. Требуется вывести номер сотрудника с максимальным грейдом и сотрудника с третьим по величине грейдом. В данном случае будем использовать функцию ПОИСКПОЗ() для поиска точного соответствия. Вид рабочего листа с формулами приведен на рисунке 83.  Диапазону С4:С27 присвоено имя грейд, которое используется в формулах. В ячейку Е6 введена формула =МАКС(Грейд). Функция =ПОИСКПОЗ(E6;Грейд;0) в ячейке Е4 возвращает номер работника с максимальным грейдом. В ячейке F6 введена формула =НАИБОЛЬШИЙ(Грейд;3) для нахождения третьего по величине значения грейда, а в ячейке F4 – функция для поиска соответствующего номера сотрудника. Результаты поиска приведены на рисунке 11. Рисунок 11 – Вид листа с исходными данными с функциями поиска информации Рисунок 12 – Результаты поиска Функция ИНДЕКС(). Часто функции ПОИСКПОЗ() и ИНДЕКС() используются вместе в формулах для создания сложных условий поиска значений в списках. Функция ИНДЕКС() имеет две синтаксические формы: ссылка и массив. Ссылочная форма всегда возвращает ссылку, а форма массива всегда возвращает значение или массив значений.  Функция ИНДЕКС() для массива позволяет выполнять поиск данных в списке в двух измерениях и выводит значения по указанным в аргументах строке и столбцу.   Формат функции  =ИНДЕКС(массив;номер_строки;номер_столбца), где      массив – список (таблица) для поиска данных;    номер_строки и номер_столбца определяют позицию искомого элемента списка. Если используются оба аргумента номер_строки  и номер_столбца, то функция ИНДЕКС() возвращает значение, находящееся в ячейке на пересечении номера строки и номера столбца. Если массив содержит только одну строку или один столбец, то соответствующий аргумент номер_строки или номер_столбца не является обязательным.    номер_строки   - это номер строки в массиве, из которой нужно вернуть  значение Если номер_строки пропущен, то аргумент номер_столбца нужно задавать обязательно;    номер_столбца   - это номер столбца в массиве, из которого нужно вернуть значение. Если номер_столбца пропущен, то аргумент номер_строки нужно задавать обязательно. Если массив занимает больше, чем одну строку и больше, чем один столбец, а задан только один аргумент номер_строки или номер_столбца, то функция ИНДЕКС() возвращает массив из целой строки или целого столбца аргумента массив. Если задать номер_строки или номер_столбца равным 0, то функция ИНДЕКС() также вернет массив значений для целого столбца или целой строки, соответственно. Для того, чтобы использовать значения, возвращаемые как массив, функцию ИНДЕКС() нужно ввести как формулу массива в горизонтальный интервал ячеек. Для ввода формулы массива в Microsoft Excel в окне Мастера функции вместо кнопки ОК нужно нажать комбинацию клавиш . Пример. Вывести наименование самого дорогого товара, который можно приобрести, имея сумму наличности 650$.     Исходные данные и результаты решения приведены на рисунке 13. В ячейку результата С14 введена формула для поиска товара в соответствии с условием задачи: =ИНДЕКС(A4:C11; ПОИСКПОЗ(A14; B4:B11; 1);1) Расчет остатка денежных средств после покупки товара выполнен по формуле      =A14-ВПР(A14;$B$3:$C$11;1;1) в ячейке  C17. Рисунок 13 – Пример использования функций поиска данных в списке Функции поиска можно использовать для поиска данных в разных полях, используя операцию конкатенации в списках аргументов функции. Например, нужно найти данные о сотруднике в списке, где могут встречаться однофамильцы, и вывести сведения о занимаемой им должности. Пример решения задачи приведен на рисунке 14. Результаты функции ПОИСКПОЗ() используются в аргументах функции ИНДЕКС() в формуле массива. В примере на рисунке 14 функция ПОИСКПОЗ() используется для поиска в двух измерениях: по строкам и по столбцам. Рисунок 14 – Пример использования поиска по двум столбцам таблицы Рисунок 15 – Использование функций поиска для массива данных Ссылочная форма ИНДЕКС() всегда возвращает ссылку.  Функция возвращает ссылку на искомую ячейку или ячейки в аргументе ссылка. Формат функции =ИНДЕКС(ссылка; номер_строки; номер_столбца; номер_области)    ссылка – это ссылка на один или несколько интервалов ячеек[1];    номер_строки – это номер строки в аргументе ссылка, на которую возвращается ссылка;    номер_столбца – это номер столбца в аргументе ссылка, на который возвращается ссылка;    номер_области – интервал ссылки, из которого надо возвращать пересечение номер_строки и номер_столбца. Первая введенная или выделенная область имеет номер 1, вторая – 2, и так далее. Если номер_области пропущен, то функция ИНДЕКС() использует область номер 1. Например, если аргумент ссылка описывает ячейки (A1:B4,D1:E4,G1:H4), то номер_области 1 соответствует интервалу A1:B4, номер_области 2 соответствует интервалу D1:E4 и номер_области 3 соответствует интервалу G1:H4. После того, как с помощью аргументов ссылка и номер_области выбран конкретный интервал, с помощью аргументов номер_строки и номер_столбца выбирается конкретная ячейка: номер_строки 1 соответствует первой строке интервала, номер_столбца 1 соответствует первому столбцу интервала и так далее. Ссылка, возвращаемая функцией ИНДЕКС(), это ссылка на пересечении номера_строки и номера_столбца. Если установить номер_строки или номер_столбца равным 0 (нулю), то функция ИНДЕКС() вернет ссылку соответственно на целую строку или столбец. Если каждая область в ссылке содержит только одну строку или один столбец, то соответственно аргумент номер_строки или номер_столбца указывать необязательно. Например, для одиночной строки следует использовать форму   =ИНДЕКС(ссылка;;номер_столбца).   Замечания. 1. Номер_строки, номер_столбца и номер_области должны указывать на ячейку внутри аргумента ссылка; в противном случае функция ИНДЕКС() возвращает значение ошибки #ССЫЛ!. Если номер_строки и номер_столбца пропущены, то функция ИНДЕКС() возвращает область в аргументе ссылка, заданную аргументом номер_области. 2. Результатом функции ИНДЕКС() является ссылка, и она интерпретируется в качестве таковой другими функциями. В зависимости от формулы, возвращаемое функцией ИНДЕКС() значение может быть использовано как ссылка или как значение. Например, формула ЯЧЕЙКА("ТАБЛ";ИНДЕКС(A1:С6;1;2)) эквивалентна =ЯЧЕЙКА("ТАБЛ";B1). Функция ЯЧЕЙКА() использует значение, возвращаемое функцией ИНДЕКС(), как ссылку. С другой стороны, такая формула, как   =2*ИНДЕКС(A1:B2;1;2), переводит значение, возвращаемое функцией ИНДЕКС, в число, содержащееся в ячейке B1. Рассмотрим более сложный пример с использованием необходимости проверки нескольких логических условий и поиска информации.   Вопросы для самопроверки 1. Перечислите функции поиска данных в одномерных массивах. 2. Назовите аргументы функций ВПР() и ГПР(). 3. Какой результат возвращает функция ПОИСКПОЗ()? 4. Какой формат имеет функция ИНДЕКС()? 5. Поясните возможные формы записи функции ИНДЕКС().   Контрольные задания для самопроверки  Задание 1. Создайте на рабочем листе таблицы о стоимости моделей ПК (4 вида) и сведений о продажах за неделю (20 записей). Рассчитайте графу «Сумма к оплате» на основании данных справочной таблицы.   Модель         Цена, тыс.           Модель Дата продажи Количество Сумма к оплате           Задание 2. Создайте на рабочем листе таблицы о стоимости товаров и сведений о продажах. Рассчитайте сумму покупок для каждого вида товара   № Товар Страна  изготовитель Цена   № Дата Товар Количество Сумма покупки 1         1         2          2         3          ..         4          ..         5          ..                    ..                    15           Задание 3. На рабочем листе «Прейскурант» расположен прейскурант следующего вида   ТОВАР 1 20 50 А 300 270 250 М 30 26 22 С 60 50 45 Б 35 30 25   В строке «Товар» проставлены граничные значения количества товара, а ниже – цены за единицу товара. Для оптовых покупателей цены снижаются. Приведите таблицу «Накладная», в которой рассчитывается цена покупки.   ТОВАР Дата покупки Количество Цена Стоимость покупки А 02.03.11 36     С 02.03.11 53     Б 03.03.11 36     М 04.03.11 4     А 04.03.11 16       Задание 4. Будем считать, что минимальный оклад  составляет 1000 руб. Составить таблицу со списком сотрудников (20 записей), содержащую следующие поля: ФИО, Должность, Разряд, Оклад. Рассчитать в созданной таблице графу «Оклад» с учетом коэффициентов разрядной сетки.   Разряд Коэффициент 8 3,12 9 3,53 10 3,99 11 4,51 12 5,10 13 5,76 14 6,51 15 7,36 16 8,17 17 9,07 18 10,07 [1] Если в качестве аргумента ссылка используется несмежное выделение, то аргумент Ссылка нужно заключить в дополнительные скобки.