Поиск данных по подписям строк и столбцов

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

=ИНДЕКС($A$1:$D$6; ПОИСКПОЗ($A$13;$A$1:$A$6;); ПОИСКПОЗ($A$14;$A$1:$D$1;))

параметры для поиска можно задавать как константы в формуле или выбирать из ячеек.

В качестве первого параметра функции ИНДЕКС указывается диапазон для поиска (в примере это $A$1:$E$6 – таблица, содержащая данные о сотрудниках фирмы). Второй параметр – номер строки в диапазоне, из которой нужно возвращать значение (может быть пропущен, но тогда обязательным становится третий параметр). Третий параметр – номер столбца, из которого нужно возвращать значение (может быть опущен, если задан номер строки).

В данном примере для определения номеров строки и столбца вызывается еще одна функция – ПОИСКПОЗ, которая возвращает относительное положение элемента массива, соответствующего заданному значению указанным образом. При вызове функции в качестве первого ее параметра указывается искомое значение или ссылка на содержащую его ячейку (в нашем примере ячейка A13 содержит фамилию "Иванов", а ячейка A14 – строку "Должность"). Второй параметр – иапазон ячеек, возможно, содержащих искомые значения (в нашем примере фамилия "Иванов" ищется в диапазоне $A1$A6, а строка "Должность" – в диапазоне $A$1:$D$1). Третий параметр задает тип сопоставления, определяющий, как Excel должен выполнять сопоставление искомого значения с элементами массива (возможно точное соответствие, поиск наибольшего значения, которое меньше или равно, чем искомое значение, или наименьшего значения, которое больше или равно искомого значения).

Функция ПОИСКПОЗ используется вместо функций типа ПРОСМОТР, если нужна позиция элемента в диапазоне, а не сам элемент.

В результате выполнения функции в ячейке, в которую записана показанная формула, вычисляется результат – значение "Инженер" (должность Иванова).

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

Упорядочение данных в таблицах

Для упорядочения данных, находящихся в базах данных Excel используется команда Сортировка… из меню Данные (метод Sort объекта Range).

Для выполнения этой команды можно записать в приложении на VBA оператор

Selection.Sort _

Key1:=Range("A2"), Order1:=xlAscending, _

Key2:=Range("C2"), Order2:=xlAscending, _

Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom

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