 |
Вычисляемые критерии на основе логических формул
|
|
|
|
Федеральное агентство по образованию РФ
Новосибирский Государственный Университет
экономики и управления
Кафедра Экономической информатики
БОРИДЬКО О.Н.
Методические указания по выполнению
лабораторной работы
«Фильтрация в Microsoft Excel 2007,функции базы данных»
По дисциплине «Информатика»
для студентов 1 курса дневного отделения
экономических специальностей
Новосибирск
Табличный процессор Microsoft Excel 2007
Методические указания к выполнению
лабораторной работы № 3
”Фильтрация в Excel,функции базы данных”
1 СПИСКИ данных в EXCEL- 2
2 Фильтрация данных в EXCEL- 2
2.1 Типы критериев- 2
2.1.1 Критерии на основе сравнения- 3
2.1.2 Критерии в виде образца-шаблона- 4
2.1.3 Множественные критерии на основе логических операций- 4
2.1.4 Вычисляемые критерии на основе логических формул- 4
3 Средства фильтрации- 5
3.1 Автофильтр- 5
3.2 Расширенный фильтр- 8
4 ВСТРОЕННЫЕ ФУНКЦИИ базы данных- 14
5 Вопросы к защите лабораторной работы-- 18
СПИСКИ данных в EXCEL
При работе с таблицами большую помощь могут оказать содержащиеся в Excel средства работы с базой данных.
Таблица в Excel представляет собой однотабличнуюбазуданных.
В Excel базы данных называются списками.
Список – определенным образом сформированный на рабочем листе Excel массив данных со столбцами и строками.
Список может использоваться как база данных, в которой строки выступают в качестве записей, а столбцы являются полями. Первая строка списка при этом содержит названия столбцов. Список должен быть организован так, чтобы в каждом столбце содержалась однотипная информация. Пустые ячейки недопустимы.
Фильтрация данных в EXCEL
Фильтрация данных – это наиболее частые действия, производимые со списком или базой данных. Фильтрация производится на основе задаваемых пользователем критериев – требований, налагаемых на информацию. Результатом фильтрации является временное скрытие записей, не удовлетворяющих заданным критериям.
|
|
|
Для фильтрации списков в Excel существует две команды:
ü Автофильтр
ü Расширенный фильтр
Автофильтр обеспечивает простой и быстрый способ скрытия лишних записей, оставляя на экране только те, что удовлетворяют критериям.
Расширенный фильтр позволяет накладывать более сложные условия отбора, которые могут включать вычисляемые критерии.
Типы критериев
Чтобы отфильтровать данные, необходимо прежде всего описать то, что необходимо найти, другими словами, задать диапазон критериев. Принципы составления диапазона одинаковы и для автофильтра, и для расширенного фильтра, хотя записываются они в различных местах. Excel поддерживает несколько типов критериев, приведем основные из них:
ü Критерии на основе сравнения – позволяют находить точные соответствия с помощью гибкого набора операций сравнения;
ü Критерии в виде образца-шаблона – позволяют находить данные по соответствию некоторому шаблону (применяется только к тексту, либо к числам, отформатированным как текст);
ü Множественные критерии на основе логических операций – позволяют объединить несколько критериев с помощью логических операций;
ü Вычисляемые критерии на основе логических формул – позволяют создавать условия отбора, зависящие от значений логических формул.
Критерии на основе сравнения
Критерии на основе сравнения позволяют находить точные соответствия или отбирать данные из заданного интервала. При этом математические вычисления и логические операции (такие, как И, ИЛИ) не используются. Критерии сравнения можно использовать в Excel при любом способе фильтрации.
|
|
|
Простейшим критерием сравнения является проверка поля на равенство конкретному значению. Для ввода простейшего критерия достаточно указать искомое значение в качестве критерия для соответствующего поля.
В критериях на основе сравнения можно использовать различные операции сравнения. Приведем их список в таблице 1.
Таблица 1. Операции сравнения
| Операция | Название | Пример | Результат поиска |
| = | Равно | =200 | Поля со значениями, равными 200 |
| > | Больше | >200 | Поле со значениями больше 200 |
| >= | Больше или равно | >=200 | Поле со значениями меньше или равно 200 |
| < | Меньше | <200 | Поле со значениями меньше 200 |
| <= | Меньше или равно | <=200 | Поле со значениями больше или равно 200 |
| <> | Не равно | <>200 | Поля со значениями, не равными 200 |
Операции сравнения можно использовать не только с числовыми аргументами. Например, чтобы отобрать фамилии, начинающиеся на букву “T” и далее по алфавиту, следует ввести критерий >=T.
При поиске и отборе информации по датам можно пользоваться всеми операциями сравнения, приведенными в таблице 1. Можно использовать любой существующий в Excel формат даты и времени. Например, >=03.03.2007.
Критерии в виде образца-шаблона
Критерии в виде образца-шаблона используются для поиска записей, содержащих похожий, но не совпадающий полностью текст. К текстовым символам шаблона относятся звездочка (*) и знак вопроса (?). Они обозначают следующее:
ü? – любой одиночный символ в той же позиции;
ü * - любая последовательность символов в той же позиции.
Если, например, вы не помните, что стоит в поле Имя: “Дима “ или “Дина”, в качестве критерия можно ввести: Ди?а.
Звездочку можно ставить в любом месте - в начале, в середине или в конце текста. Чтобы узнать, к примеру, всех клиентов фирмы, зарегистрированных как ЗАО, достаточно набрать критерий: ЗАО*.
Множественные критерии на основе логических операций
Множественные критерии позволяют поиск записей, удовлетворяющих нескольким условиям. В режиме автофильтра для этого используется диалоговое окно Пользовательский автофильтр [1], где есть возможность определить два условия. Если используется расширенный фильтр, в распоряжении имеется область критериев, которая позвол я ет задавать разнообразные комбинации условий.
|
|
|
Для наложения нескольких условий поиска в Excel служат логические операции И, ИЛИ (AND, OR). В диалоговом окне Пользовательский автофильтр два условия можно соединить с помощью любой из двух логических операций. Расширенный фильтр допускает ввод множества условий, соединенных различными способами. Если условия находятся в одной строке, то они считаются соединенными логической операцией И, если в разных – то логической операцией ИЛИ.
Вычисляемые критерии на основе логических формул
Вычисляемые критерии отличаются от обычных условий тем, что позволяют использовать значения, возвращаемые формулой.
Правила записи вычисляемых критериев:
ü Заголовок над вычисляемым критерием должен отличаться от любого имени поля в списке, он может быть пустым или содержать произвольный текст. Если заголовок пустой, он все равно включается в диапазон критерия.
ü Ссылки на ячейки, находящиеся вне списка, как правило, должны быть абсолютными.
ü Ссылки на ячейки, находящиеся в списке, как правило, должны быть относительными.
Средства фильтрации
Автофильтр
Автофильтр предоставляет простой доступ к мощным средствам рабочего листа.
С помощью элементарных действий мышью можно быстро отфильтровать данные, оставив на экране только то, что необходимо видеть.
В режиме автофильтра строка заголовка таблицы преобразуется в группу раскрывающихся списков, снабженных характерными кнопками ▼,позволяющие задать условия фильтрации для каждого из полей. При раскрытии списка в каком-либо поле выводится список всех уникальных элементов этого поля и некоторые критерии отбора, в том числе пользовательский автофильтр, доступный посредством выбора одной из команд Текстовые фильтры, Числовые фильтры, Фильтры по дате (в зависимости от типа поля) из меню в раскрывающемся списке поля заголовка таблицы.
Замечание
Как автофильтр, так и расширенный фильтр скрывают строки рабочего листа целиком. Если около боковых границ списка имеются другие данные, они могут оказаться спрятанными вместе с записями списка.
|
|
|
Для дальнейшей работы создайте таблицу, которая представлена на рис.1.
Рис.1. Таблица исходных данных
Пример 1.
С помощью автофильтра составьте список фирм, у которых Дата счета, между 20.04.2007 и 20.07.2007.
Чтобы перевести список в режим автофильтра необходимо сделать следующее:
1. Выделите диапазон исходных данных – А1:Е11 (рис.2)
Рис.2. Диапазон исходных данных
2. Выберите на ленте вкладку Данные. В группе Сортировка и фильтр выберите команду Фильтр (рис.3).
Рис.3. Команда фильтр
Список будет преобразован соответствующим образом, (рис.4). Обратите внимание на изменившийся вид верхней строки диапазона – в каждом заголовке появилась характерная кнопка раскрывающегося списка ▼
Рис.4. Список в режиме автофильтра
3. Выберите в раскрывающемся списке нужного поля строки заголовка таблицы команду Текстовые фильтры, Числовые фильтры, Фильтры по дате (согласно типу поля).
4. В открывшемся меню выберите один из пунктов равно, не равно, больше, меньше, между и т.д. либо пункт Настраиваемый фильтр. Откроется диалоговое окно Пользовательский автофильтр.
Рис.5. Выбор критерия фильтрации в меню автофильтра
5. Задайте условие сравнения с помощью раскрывающихся списков. В левых списках задаются способы сравнения, в правых – значения, с которыми производится сравнение (см. рис.6).
6. С помощью переключателя И или ИЛИ задайте способ объединения условий.
7. Нажмите кнопку OK.
Рис.6. Выбор условияв диалоговом окне Пользовательского автофильтра примера1.
Рис.7. Результат работы Пользовательского автофильтра примера 1
Чтобы отменить фильтрацию, выберите на ленте вкладку Данные. В группе Сортировка и фильтр выберите команду Очистить (рис.8).
Рис.8. Отмена фильтрации
Выход из режима автофильтра производится так же, как и его включение. Выберите на ленте вкладку Данные. Вгруппе Сортировка и фильтр, команду Фильтр.
Расширенный фильтр
Расширенный фильтр требует большей работы, чем автофильтр, однако и предоставляемые им возможности фильтрации шире. Можно более свободно применять операции И, ИЛИ, а также составлять вычисляемые критерии.
Для использования расширенного фильтра необходимо создать диапазон критериев, где задаются условия поиска данных.
Правила записи диапазона критериев:
ü Верхняя строка диапазона должна содержать заголовки полей, которые в точности повторяют по написанию заголовки полей в области данных. Проще всего можно обеспечить точность совпадения путем копирования.
ü Не требуется включать все имеющиеся заголовки и сохранять их порядок.
|
|
|
ü Кроме того, диапазон критериев должен включать хотя бы одну пустую строку ячеек сразу под заголовками. В этой строке (или строках) записываются условия поиска, причем все, что записано в столбце под заголовком поля, относится именно к этому полю.
ü Для ввода условий можно использовать несколько строк.
Рассмотрим использование расширенного фильтра на примере 2.
Пример 2.
С помощью расширенного фильтра составьте список фирм, Сумма в счете у которых больше 50000 рублей.
1. В свободной области электронной таблицы создайте таблицу критериев (см. рекомендации по составлению таблицы критериев).
Рис.9. Создание таблица критериев
2. В свободной области электронной таблицы создайте выходной документ (где будет напечатан результат работы расширенного фильтра), в котором указываются имена тех полей из исходной таблицы базы данных, для которых осуществляется фильтрация.
Рис.10. Создание выходного документа
3. Выберите на ленте вкладку Данные. В группе Сортировка и фильтр выберите команду Дополнительно. Появится диалоговое окно Расширенный фильтр (рис.11).
Рис.11. Диалоговое окно Расширенный фильтр
4. Выберите одно из положений переключателя Обработка. При выборе положения фильтровать список на ме сте не удовлетворяющие критерию записи будут скрыты, при выборе положения скопировать результат в другое место список данных останется нетронутым, а отобранные записи будут помещены в указанный диапазон.
5. Введите диапазон списка данных в поле Исходный диапазон (ввод лучше всего осуществлять путем протаскивания указателя мыши по диапазону исходного списка данных) – A1:E11.
6. Введите диапазон критериев в поле Диапазон условий – G2:G3.
7. Если выбрано положение скопировать результат в другое ме сто, укажите диапазон на рабочем листе, где необходимо вывести результаты работы расширенного фильтра и укажите его в поле Поместить результат в диапазон –B13:C13.
8. Установите флажок Только уникальные записи, если не хотите, чтобы одинаковые записи повторялись (будет выводиться только первая из всех удовлетворяющих критерию одинаковых записей).
9. Нажмите кнопку OK.
Результаты работы расширенного фильтра сразу же отобразятся на рабочем листе.
Рис.12. Результат работы расширенного фильтра примера 2
Пример 3.
Отобрать записи фирм, Сумма в счете у которых от 50000 до 90000 рублей.
1. Введите диапазон базы данных (А1:Е11).
2. Введите диапазон условий (G2:H3). В этом примере два условия соединяются логической операцией И (И Сумма в счете > 50000, И Сумма в счете < 90000), поэтому в диапазоне условия они располагаются в одной строке.
3. Поместите результат в диапазон (В13:С13) (рис.14).
Рис.13. Пример с использованием логической операции И
Рис. 14. Результат работы расширенного фильтра примера 3.
Пример 4.
Определить, у каких фирм Сумма в счете от 30000 рублей и Дата счета позже 20.04.2007.
1. Введите диапазон базы данных (А1:Е11).
2. Введите диапазон условий (G2:H3). В этом примере два условия соединяются логической операцией И (И Сумма в счете > 30000, И Дата счета >= 20.04.2007), поэтому в диапазоне условия они располагаются в одной строке.
3. Поместите результат в диапазон (В13:D13) (рис.15).
Рис.15. Пример 4.
Рис 16. Результат работы расширенного фильтра примера 4.
Пример 5.
С помощью расширенного фильтра составьте список фирм, у которых счета, выставленные раньше июля 2007, на сумму от 10000 руб., рассматривая лишь фирмы со специализацией, соответствующей образцу Быт* ( Бытовая техника или Бытовая химия).
1. Введите диапазон базы данных (А1:Е11).
2. Введите диапазон условий (G2:I3). В этом примере три условия соединяются логической операцией И, поэтому в диапазоне критерия они располагаются в одной строке в трех столбцах.
3. Поместите результат в диапазон (В13:E13).
Рис.17. Пример 5.
Пример 6.
Отобрать фирмы и сумму в счете, которые специализируются на Цветах и Косметике.
В этом примере условия соединяются логической операцией ИЛИ (ИЛИ Цветы, ИЛИ Косметика), следовательно, в диапазоне критерия они располагаются в разных строках одного столбца (одного поля - Специализация).
Диапазон условий (G2:G4).
Рис. 18. Пример 6
Пример 7.
Отобрать записи фирм, у которых Сумма в счете больше средней суммы.
В этом примере используется вычисляемое условие. Заголовок над вычисляемым критерием должен отличаться от любого имени поля в списке, он может быть пустым или содержать произвольный текст. Если заголовок пустой, он все равно включается в диапазон критерия.
В левой части этого условия указываем адрес ячейки Е2, которая является первой ячейкой с данными в столбце Сумма в счете. В процессе фильтрации вместо этого адреса подставляются по порядку адреса всех остальных ячеек поля Сумма в счете до конца списка, т.е. до ячейки Е11.
Диапазон условий (G5:G6).
Рис.19. Вычисляемый критерий 1
или диапазон условий (G5:G6)
Рис.20. Вычисляемый критерий 2
Рис. 21. Результат работы вычисляемого критерия
|
|
|
