Работа со списками данных

Источник: excel1gb

Алексей Шмуйлович

Excel представляет массу удобных средств для обработки списков данных.

Под списком понимают двухмерную таблицу, в столбцах которой содержатся однородные данные. Если проводить аналогию с базами данных, то столбцы списка можно сопоставить с полями, а строки с записями таблицы данных.

Примером списка может служить прайс-лист, журнал бухгалтерских проводок, отчет по продажам в разрезе менеджеров.

Чтобы обработка списка проходила полноценно, он не должен содержать объединенных ячеек. Также желательно, чтобы первая строка содержала заголовки.

Итак, что Excel умеет делать со списками?

Сортировать;
Фильтровать;
Группировать и подводить промежуточные итоги по группам;
Строить сводные отчеты и диаграммы;
Проводить статистические вычисления;
Искать в списке данные, соответствующие определенным критериям.

Начнем с разговора о функциях Excel для работы с данными.

Функции для работы со списками

Excel имеет ряд функций, позволяющих извлекать данные из списка. Рассмотрим несколько наиболее полезных функций.

Функция =ИНДЕКС ()

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

Синтаксис
=ИНДЕКС (ссылка_на_список;номер_строки;номер_столбца)

Знаете ли Вы:
Что функция =СТОЛБЕЦ (ссылка)возвращает номер столбца, на который указывает ссылка? Если ссылка опущена, функция возвращает номер столбца, в котором расположена ячейка с формулой.

Функция =ВПР ()

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

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

Искомое_значение - это значение, которое должно быть найдено в первом столбце списка. Искомое_значение может быть значением, ссылкой или текстовой строкой.
Таблица- таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список.

Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента <таблица> должны быть расположены в возрастающем порядке: …, −2, −1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если <интервальный_просмотр> имеет значение ЛОЖЬ, то <таблица> не обязана быть отсортированной.
Данные можно упорядочить следующим образом: в меню Данные выбрать команду Сортировка и установить переключатель По Возрастанию.
Значения в первом столбце аргумента <таблица> могут быть текстовыми строками, числами или логическими значениями.
Текстовые строки сравниваются без учета регистра букв.
Номер_столбца- это номер столбца в массиве <таблица>, в котором должно быть найдено соответствующее значение. Если <номер_столбца> равен 1, то возвращается значение из первого столбца аргумента <таблица>; если <номер_столбца> равен 2, то возвращается значение из второго столбца аргумента <таблица> и так далее. Если <номер_столбца> меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если <номер_столбца> больше, чем количество столбцов в аргументе <таблица>, то функция ВПР возвращает значение ошибки #ССЫЛ!.
Интервальный_просмотр- это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Как использовать эту функцию?

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

Чтобы эта схема работала, инвентарный номер должен находиться в первой колонке списка данных.

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

Знаете ли Вы:
Что функция =СТРОКА (ссылка) возвращает номер строки, на которую указывает ссылка? Если ссылка опущена, функция возвращает номер строки, в которой расположена ячейка с формулой.

Ссылки по теме