Опубликован: 24.04.2015 | Доступ: свободный | Студентов: 145 / 0 | Длительность: 04:57:00
Лекция 2:

Основы работы в Gnumeric

2.6.5 Функции поиска соответствий.

В тех случаях, когда использование функции if() становится неудобным по причине большого количества вложений или большой длины формулы, а также для повышения эффективности вычислений, в электронных таблицах используются функции поиска соответствий. К таким функциям относятся lookup(), vlookup(), hlookup(), match() и index(), находящиеся в категории "Поиск".

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

Функции lookup(), vlookup() и hlookup() в качестве одного из аргументов используют так называемые "ассоциативные массивы" (справочники). Ассоциативный массив – это структура данных, оформленная в виде таблицы, первый столбик которой содержит "ключи" – данные, которые участвуют в формировании условий. В следующих столбиках ассоциативного массива содержатся значения, соответствующие ключам. Таким образом, по значению ключа можно однозначно получить какие-то другие данные (в языках программирования такие структуры называются "хэш-таблицы").

В программах ЭТ ассоциативные массивы реализуются как блоки ячеек (справочные таблицы), содержащие минимум два столбца. Первый столбец содержит ключи, второй – значения, соответствующие ключам.

Для примера рассмотрим следующую задачу. В ралли участвуют гонщики на автомобилях, для которых известны марки и расход топлива в литрах на 100 км. Дан список, в котором указаны фамилии гонщиков и марки автомобилей. Известна протяженность трассы гонок в километрах L. Определить расход топлива для каждого участника гонок.

Справочник для задачи о ралли

Рис. 2.57. Справочник для задачи о ралли
Исходные данные задачи о ралли

Рис. 2.58. Исходные данные задачи о ралли

Для решения задачи составим справочную таблицу для 7-ми разных марок а/м, и расположим ее в ячейках F3:G10 (рис. 2.57). Полезно соблюдать алфавитный порядок текстовых значений в первом столбике справочной таблицы.

Данные (список участников и марки их а/м) запишем в ячейки A3:B10 (рис. 2.58). Длину трассы L, которая является параметром, запишем в ячейку C1. Для вычисления полного расхода топлива используем формулу с функцией LOOKUP().

Формула в ячейке C4 будет выглядеть следующим образом.

Решение задачи о ралли

Рис. 2.59. Решение задачи о ралли

=LOOKUP(B4;$F$4:$F$10;$G$4:$G$10)*$C$1/100

Функция LOOKUP() считывает содержание ячейки, указанной в первом аргументе, ищет это значение в диапазоне ячеек (столбце), указанном во втором аргументе и выдаёт соответствие этому значению из диапазона ячеек (столбца), указанном в третьем аргументе. Таким образом, для получения результата по названию а/м находим расход топлива на 100 км и умножаем это значение на количество сотен километров. Принципиально важно указывать абсолютные адреса блоков ячеек справочной таблицы.

Итоговая таблица показана на рис. 2.59.

Ограничение функции lookup() – только один столбец соответствий. Более "мощной" является функция vlookup(), в которой второй аргумент определяет весь блок ячеек, содержащих ассоциативный массив (справочник), а третий аргумент указывает, в каком столбце ассоциативного массива нужно искать соответствие ключу. Четвёртый (необязательный) аргумент определяет порядок сортировки первого ("ключевого") столбца справочника. Если он не указан или равен 1 (логическая ИСТИНА), то первый столбец ассоциативного массива для функции vlookup() должен содержать числа, отсортированные по возрастанию, или текст, отсортированный в алфавитном порядке. Если значения в первом столбце не отсортированы, то четвертый аргумент должен быть установлен в 0. Еще одним большим достоинством функции vlookup() является возможность работы с диапазонами значений ключа.

Для примера рассмотрим вычисление суммы годового налога при прогрессивной налоговой шкале. Пусть при годовом доходе до 10000 у.е. ставка налога составляет 12%, до 30000 у.е. – 20%, до 50000 у.е. – 25% и при большем доходе – 35%. Для создания таблицы данных используем фамилии из предыдущего примера, а суммы годового дохода запишем такие, чтобы можно было реализовать все варианты ставок налога (рис. 2.60). Таблицу данных разместим в диапазоне A3:B10.

Исходные данные задачи о налогах

Рис. 2.60. Исходные данные задачи о налогах
Справочник к задаче о налогах

Рис. 2.61. Справочник к задаче о налогах

Справочную таблицу размещаем в диапазоне E3:F7 (рис. 2.611), формула в ячейке C4 с использованием функции vlookup() выглядит следующим образом:

=VLOOKUP(B4;$E$4:$F$7;2)*B4

Во втором столбце ассоциативного массива находим ставку налога, соответствующую доходу, а затем получаем сумму налога, умножая ставку налога на величину дохода (рис 2.62).

Решение задачи о налогах

Рис. 2.62. Решение задачи о налогах

Важно, что VLOOKUP() выбирает значения, соответствующие нижней границе диапазона значений ключей, и используется минимальное значение ключа в интервале.

Четвертый аргумент функции VLOOKUP() также влияет и на возможность интервального просмотра. Если этот аргумент имеет значение ИСТИНА (1) или опущен, то интервальный просмотр работает, как описано выше. Если этот аргумент имеет значение ЛОЖЬ (0), то функция VLOOKUP() ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #N/A (#Н/Д – "нет данных"). Таким образом, для использования возможности работы с интервалами значений в VLOOKUP() первый столбец справочной таблицы (ассоциативного массива) обязательно должен быть отсортирован по возрастанию.

Функция hLOOKUP() работает аналогично VLOOKUP(), только порядок следования "ключей" – не сверху вниз, а слева направо.

Теперь рассмотрим формат функций match() и index().

Функция MATCH(искомое_значение; искомый_массив; тип_сопоставления) — находит позицию (порядковый номер) искомого значения в одномерном массиве. Значение аргумента "тип сопоставления" – (-1, 0 или 1) – зависит от того, упорядочен ли массив (-1 — массив упорядочен по убыванию, находится место наименьшего значения, которое больше или равно искомому, 0 — массив может быть неупорядоченным, находится место первого значения, равного исходному, 1 — массив упорядочен по возрастанию, находится место наибольшего значения, которое меньше или равно искомому).

Функция INDEX(массив; номер_строки; номер_столбца) — находит значение элемента, находящегося в заданном массиве на пересечении заданных строки и столбца.

Далее рассмотрим пример. Пусть на основе эксперимента получена следующая зависимость посещаемости дискотеки от входной платы:

Таблица 2.6. Входные данные
Входная плата, у.е. 1 1,5 2 2,5 3 3,5 5
Количество посетителей 200 175 160 140 124 110 70
Решение задачи о дискотеке

Рис. 2.63. Решение задачи о дискотеке

Определить оптимальную входную плату.

Очевидно, что оптимуму соответствует максимальная выручка. Соответственно, решение задачи состоит в том, чтобы подсчитать выручку в каждом случае, найти максимальную и написать формулу, показывающую входную плату, соответствующую максимальной выручке. То есть нужно определить номер столбика, в котором получается максимальная выручка и вывести значение входной платы.

Определяем выручку в каждом случае (умножив входную плату на количество посетителей), затем функцией MAX() находим наибольшую выручку. После этого функцией MATCH() определяем, на каком месте в массиве она находится и функцией INDEX() смотрим, какая входная плата находится на этом месте (рис. 2.63). Рядом с ячейками с результатами приведены формулы для получения этих результатов (используется функция expression()).

В этом примере участвует функция max(), которая относится к категории статистических функций, к знакомству с которыми теперь и перейдем.