ЛАБОРАТОРНАЯ РАБОТА № 9
Использование расширенного фильтра и функции БИЗВЛЕЧЬ при работе со списками
Цель работы: приобретение навыков по выборке данных из базы с помощью расширенного фильтра и функции БИЗВЛЕЧЬ.
ТЕОРЕТИЧЕСКАЯ ЧАСТЬ
Одним из самых частых действий над списками в Excel является фильтрация. Фильтрация производится на основе задаваемых пользователем критериев – требований, налагаемых на информацию. Результатом фильтрации является временное скрытие записей, не удовлетворяющих критериям. Фильтрацию данных можно осуществить с помощью формы данных, автофильтра и расширенного фильтра. Особое внимание уделим расширенному фильтру, т. к. он является более гибким средством отбора записей из БД, чем автофильтр. Однако перед тем как использовать его, необходимо создать 3 разнесенных в пространстве электронной таблицы, непересекающихся друг с другом диапазона.
1. Диапазон базы данных – область, где хранятся данные списка; связанные друг с другом данные записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля.
2. Диапазон критериев – область на рабочем листе, где задаются критерии поиска информации; здесь указываются имена полей и отводится область для записи условий отбора. Диапазон критериев должен соответствовать следующим требованиям:
диапазон должен включать как минимум две строки. Причем в первой строке размещаются названия полей списка. Другая строка (или строки) содержит критерии отбора записей, определенные пользователем. Во избежание ошибок названия полей списка следует скопировать, а не вводить вручную;
значения условий фильтрации, размещенных в одной строке, объединены логическим оператором И;
если на значения поля необходимо наложить несколько условий отбора, соединенных оператором И, то в области названия поля необходимо повторить имя этого поля нужное количество раз;
значения условий, заданных в разных строках, связаны оператором ИЛИ;
пустая строка критерия означает, что условия отбора нет;
диапазон условий может находиться в любом месте рабочего листа или даже на отдельном рабочем листе. Однако при этом диапазон условий должен быть отделен от исходного списка хотя бы одной пустой строкой или столбцом. Удобнее всего диапазон критериев располагать над списком;
диапазон условий может содержать не все поля списка, а только те, которые используются при создании условий фильтрации.
Следует отметить, что в диапазоне критериев могут использоваться вычисляемые критерии, которые представляют собой условия, включающие более сложные операции, чем простое сравнение значения столбца с константой.
Применяя вычисляемый критерий, необходимо соблюдать следующие правила:
заголовок над вычисляемым критерием не должен совпадать с заголовком какого-либо поля списка. Он может быть пустой ячейкой или другим текстовым полем, отличным от названия полей анализируемого списка;
ссылки на ячейки внутри списка должны быть относительными, а ссылки на ячейки вне списка – абсолютными;
в формуле, применяемой для вычисления условия, следует использовать ссылку на целый столбец списка;
количество условий, используемых для фильтрации списка, не ограничено. Эта величина может применяться в сочетании с любыми другими невычисляемыми условиями;
вычисляющая формула представляет собой логическую формулу, которая возвращает значение Истина или Ложь.
Задание текстовых условий. Существует несколько правил задания текстовых условий.
Если необходимо найти все значения, которые начинаются с конкретной буквы, нужно в диапазоне критериев указать эту букву.
Символ «>» («<») означает: «Найти все значения, которые находятся по алфавиту после (до) введенного текстового значения». Например, при задании условия «>М» ниже заголовка Фамилия в диапазоне критериев отбираются строки, в которых фамилии начинаются с М, Н и т. д. до Я.
Формула ="=текст" означает: «Найти значения, которые точно совпадают со строкой символов текст». Например, если просто задать Иванов без формулы, то Excel отфильтрует строки с фамилиями Иванов, Иванова, Иванович, Ивановский и т. д. Условие ="=Иванов" позволит найти строки, в которых в поле Фамилия будет указана фамилия Иванов.
Кроме того, при задании текстовых условий можно использовать символы шаблона «*» и «?». Например, для того чтобы найти записи о сотрудниках, чьи имена состоят из шести букв и заканчиваются на «ей», нужно задать такой шаблон в диапазоне критериев под заголовком Имя: «????ей».
Рисунок 33 – Диалоговое окно
Расширенный фильтр
3. Диапазон для извлечения – область, в которую копируют выбранные из списка данные.
Чтобы воспользоваться расширенным фильтром, нужно выбрать команду Данные → Сортировка и фильтр → Дополнительно. Появится диалоговое окно (рисунок 33).
В элементе управления Исходный диапазон нужно указать диапазон, в котором размещается список, в элементе управления Диапазон условий – диапазон критериев. По умолчанию в группе Обработка всегда установлен переключатель фильтровать список на месте. Если выбрать вариант скопировать результат в другое место, то будет задействован диапазон для извлечения. В этом случае становится доступным элемент управления Поместить результат в диапазон, в котором нужно задать диапазон, в который будут помещены отфильтрованные данные.
Флажок Только уникальные записи позволяет исключить повторяющиеся.
В том случае, когда список фильтруется на месте, не удовлетворяющие критерию строки скрываются. Чтобы вновь их отобразить, необходимо воспользоваться командой Данные → Сортировка и фильтр →Очистиь.
Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям. У всех Д-функций один и тот же синтаксис:
= Дфункция(база_данных; поле; критерий).
Аргумент база данных задает весь список. Второй аргумент, поле, определяет столбец, в котором производятся вычисления (суммирование, усреднение и т. п.). В качестве второго аргумента можно использовать имя поля, задаваемое в виде текстового значения, – название, заключенное в кавычки, или порядковый номер столбца в списке. Если формула формируется с помощью мастера функций, при заполнении второго аргумента достаточно указать ячейку рабочего листа, в которой хранится имя соответствующего поля. Аргумент критерий задает диапазон критериев. Диапазон критериев формируется так же, как при использовании расширенного фильтра.
Одной из основных функций баз данных является БИЗВЛЕЧЬ. Функция извлекает отдельное значение из столбца списка или базы данных, которое удовлетворяет заданным условиям. Если такой ячейки не обнаружено, возвращается значение #ЗНАЧ!. Если заданным условиям удовлетворяют несколько ячеек, то возвращается ошибочное значение #ЧИСЛО!
ПРАКТИКУМ
Задача 1. Из БД «Кадровый состав» получить информацию о людях, чьи фамилии начинаются либо на букву В, либо на Д, либо на С.
Алгоритм решения задачи
В данном случае критерий отбора формируется с помощью логической операции ИЛИ. Чтобы связать условия в диапазоне критериев логической операцией ИЛИ, нужно эти условия расположить в разных строках (рисунок 34).
Рисунок 34 – Пример использования операции ИЛИ в расширенном фильтре
В нашем примере исходный диапазон – А8:F17; диапазон условий – А2:А5; диапазон вывода информации – А20:F26.
Задача 2. В БД «Кадровый состав» найти сотрудников, дата рождения которых находится в промежутке с 01.01.1950 г. по 31.12.1960 г. включительно
Алгоритм решения задачи
В данном случае критерий отбора формируется с помощью логической операции И. Для этого условия в диапазоне критериев нужно расположить в одной строке (рисунок 35). В этом примере исходный диапазон – А5:F14; диапазон условий – D1:E2; диапазон вывода информации – А17:F19.
Рисунок 35 – Пример использования операции И в расширенном фильтре
Задача 3. В БД «Кадровый состав» найти людей с окладом выше среднего оклада на предприятии
Алгоритм решения задачи
Пример решения показан на рисунке 36.
Рисунок 36 – Пример использования вычисляемого условия в расширенном фильтре
Здесь в ячейке С2 сформирована функция = СРЗНАЧ(F6:F14), которая подсчитывает средний оклад. Диапазон критериев (D1:D2) состоит только из вычисляемого условия с заголовком Выше среднего (такого заголовка в исходном списке нет). В ячейке D2 сформирован критерий поиска: = F6 > $C$2. Ссылка на ячейку С2 является абсолютной, поскольку она расположена вне диапазона базы данных. Ячейка F6 является первой ячейкой в поле Оклад неотфильтрованного списка. Эта ячейка находится в диапазоне базы данных, поэтому ссылка на нее является относительной. В процессе фильтрации Excel по порядку подставляет каждый элемент вместо этого первого значения, т. е. Excel сравнивает F6, затем F7, потом F8 и т. д., пока не дойдет до конца списка. Если сравнение дает ложный результат, то соответствующая строка скрывается. Таким образом, отображенными останутся только те строки, для которых формула дает результат ИСТИНА.
Задача 4. В базе данных «Кадровый состав» с помощью функции БИЗВЛЕЧЬ найти сотрудника с фамилией Доценко.
Алгоритм решения задачи
Пример решения задачи показан на рисунке 37. Критерий поиска сформирован в диапазоне D2:D3. В ячейке Е16 сформирована функция =БИЗВЛЕЧЬ (A5:F14; A5; D2:D3), результатом которой является фамилия Доценко.
Рисунок 37 – Пример использования функции БИЗВЛЕЧЬ
|
Скачать 1.61 Mb.