Работа со списками данных
Excel представляет массу удобных средств для обработки списков данных.
Под списком понимают двухмерную таблицу, в столбцах которой содержатся однородные данные. Если проводить аналогию с базами данных, то столбцы списка можно сопоставить с полями, а строки с записями таблицы данных.
Примером списка может служить прайс-лист, журнал бухгалтерских проводок, отчет по продажам в разрезе менеджеров.
Чтобы обработка списка проходила полноценно, он не должен содержать объединенных ячеек. Также желательно, чтобы первая строка содержала заголовки.
Итак, что Excel умеет делать со списками?
- Сортировать;
- Фильтровать;
- Группировать и подводить промежуточные итоги по группам;
- Строить сводные отчеты и диаграммы;
- Проводить статистические вычисления;
- Искать в списке данные, соответствующие определенным критериям.
Начнем сразговора о функциях Excel для работы с данными.
{mospagebreak}
Функции для работы со списками
Excel имеет ряд функций, позволяющих извлекать данные из списка. Рассмотрим несколько наиболее полезных функций.
Функция =ИНДЕКС()
Возвращает ссылку на ячейку на пересечении указанной строки и указанного столбца. Если ссылка составлена из несмежных выделенных диапазонов, то можно указать, какой именно выделенный диапазон следует использовать.
Синтаксис
=ИНДЕКС(ссылка_на_список;номер_строки;номер_столбца)
Знаете ли Вы:Что функция =СТОЛБЕЦ(ссылка)возвращает номер столбца, на который указывает ссылка? Если ссылка опущена, функция возвращает номер столбца, в котором расположена ячейка с формулой.
Функция =ВПР()
Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. Функция ВПР используется вместо функции ГПР, когда сравниваемые значения расположены в столбце слева от искомых данных.
Буква <В> в имени функции ВПР означает <вертикальный>.
Синтаксис
ВПР(искомое_значение;таблица;номер_столбца;интервальный_просмотр)
Искомое_значение - это значение, которое должно быть найдено в первом столбце списка. Искомое_значение может быть значением, ссылкой или текстовой строкой.
Таблица- таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список.
- Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента <таблица> должны быть расположены в возрастающем порядке: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если <интервальный_просмотр> имеет значение ЛОЖЬ, то <таблица> не обязана быть отсортированной.
- Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию.
- Значения в первом столбце аргумента <таблица> могут быть текстовыми строками, числами или логическими значениями.
- Текстовые строки сравниваются без учета регистра букв .
Номер_столбца- это номер столбца в массиве <таблица>, в котором должно быть найдено соответствующее значение. Если <номер_столбца> равен 1, то возвращается значение из первого столбца аргумента <таблица>; если <номер_столбца> равен 2, то возвращается значение из второго столбца аргумента <таблица> и так далее. Если <номер_столбца> меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если <номер_столбца> больше, чем количество столбцов в аргументе <таблица>, то функция ВПР возвращает значение ошибки #ССЫЛ!.
Интервальный_просмотр- это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.
Как использовать эту функцию?
Допустим, у Вас есть таблица с подробными характеристиками объектов основных средств. Тогда для составления накладной или акта Вам не нужно заполнять все подробности. Достаточно вставить инвентарный номер, а все остальные параметры появятся в документе автоматически - через функцию ВПР().
Чтобы эта схема работала, инвентарный номер должен находиться в первой колонке списка данных.
Аналогично работает функция =ГПР(), но она осуществляет поиск <по горизонтали> - ищет значение в первой строке, а возвращает значение из строки с указанным номером и столбца, в котором найдено искомое значение.
Знаете ли Вы:Что функция =СТРОКА(ссылка) возвращает номер строки, на которую указывает ссылка? Если ссылка опущена, функция возвращает номер строки, в которой расположена ячейка с формулой.