- •Лабораторная работа № 5 Списки и базы данных
- •1 Понятие списка и базы данных в Excel
- •2 Технология создания списков
- •3 Поиск записей
- •4 Сортировка записей
- •5 Фильтрация данных
- •5.1 Возможности фильтрации
- •5.2 Автофильтр
- •Результат показать преподавателю
- •5.3 Расширенный фильтр
- •Самостоятельно! Диапазон критериев выписать в тетрадь. Результат отбора поместить рядом на том же рабочем листе, показать преподавателю!
- •Самостоятельно! Результат отбора поместить рядом на том же рабочем листе, показать преподавателю!
- •3. Контрольные вопросы
Результат показать преподавателю
При большом количестве условий фильтрации либо при необходимости построения сложных логических условий, в том числе по вычисляемым критериям, применение Автофильтра невозможно. В этих случаях, как правило, используется Расширенный фильтр.
5.3 Расширенный фильтр
Расширенный фильтр гораздо более гибкий инструмент работы с данными, чем автофильтр. Однако перед тем как использовать его, необходимо создать диапазон критериев, в котором описываются все условия поиска.
Диапазон критериев должен соответствовать следующим требованиям:
Диапазон должен включать как минимум две строки. Причем в первой строке размещаются названия полей списка. Другая строка (или строки) содержит критерии отбора записей, определенные пользователем. Во избежание ошибок названия полей списка следует скопировать, а не вводить вручную.
Значения условий фильтрации, размещенных в одной строке, объединены логическим оператором И.
Если на значения поля необходимо наложить несколько условий отбора, соединенных оператором И, то в области названия поля необходимо повторить имя этого поля нужное количество раз.
Значения условий, заданных в разных строках, связаны опера тором ИЛИ.
Пустая строка критерия означает, что условия отбора нет.
Диапазон условий может находиться в любом месте рабочего листа или даже на отдельном рабочем листе. Однако при этом диапазон условий должен быть отделен от исходного списка хотя бы одной пустой строкой или столбцом.
Диапазон условий может содержать не все поля списка, а только те, которые используются при создании условий фильтрации.
После того как создан диапазон условий, можно использовать средства расширенного фильтра. Для этого следует последовательно выполнить операции: Установить курсор в любую ячейку списка → Команда Данные → фильтр → Дополнительно. В итоге на экране появится одноименное диалоговое окно (рис. 5.16), в котором следует указать необходимые данные.
Рис. 5.16 Диалоговое окно Расширенный фильтр
Прежде всего, следует установить переключатель обработки на одно из возможных значений, определяющих, куда поместить результат: фильтровать список на месте (действует по умолчанию) или скопировать результат в другое место.
Затем в текстовом поле Исходный диапазон следует задать адрес обрабатываемой базы данных (путем выделения всех ее ячеек, включая заголовки полей). Этот адрес, как правило, устанавливается по умолчанию, и пользователь может его подтвердить либо изменить по своему усмотрению.
В текстовом поле Диапазон условий следует задать соответствующие ссылки на ячейки, содержащие условия отбора записей (диапазон критериев).
Если был выбран переключатель Скопировать результат в другое место, то активизируется текстовое поле Поместить результат в диапазон, в котором необходимо ввести адрес левой верхней ячейки диапазона результата фильтрации.
После заполнения предложенной формы ввод параметров фильтрации подтверждается кнопкой ОК. Excel отфильтрует список в соответствии с заданными условиями, не отображая записи, не удовлетворяющие критериям фильтра.
Примечания
При использовании расширенного фильтра целесообразнее помещать диапазоны условий выше и правее исходной базы данных, если она содержит не более 1020 столбцов и не более 200250 записей. В противном случае диапазон условий целесообразнее размещать на новом листе.
Результат выборки расширенного фильтра предпочтительнее помещать в новое место под диапазоном условий. Если выборка размещается на том же листе, что и исходная база данных, то для облегчения сравнительного анализа их следует располагать на одном уровне по горизонтали.
После изменения значений диапазона условий результат выборки расширенного фильтра автоматически не изменяется. Требуется его повторное применение.
Варианты применения критериев расширенного фильтра продемонстрируем на базе данных магазина «Ирикон» (рис. 5. 17), содержащей информацию о поступивших на ее склад.