Профессиональные приемы работы в  Microsoft Excel

Top.Mail.Ru

Яндекс.Метрика

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

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

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

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

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

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

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

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

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

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

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

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

Знаете ли Вы:

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

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

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

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

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

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

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

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

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

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

Знаете ли Вы:

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