Расширенная фильтрация

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

Запись в базе данных условий может содержать пустые поля. Каждое непустое поле задает определенное условие фильтрации. Для текстовых полей требуется совпадение поля в базе данных с заданным здесь значением. Для числовых полей можно также указывать знаки сравнения. Условия, заданные в пределах одной записи, должны выполняться одновременно. Для условий, заданных в рамках разных записей, достаточно выполнения хотя бы одного.

При выборе расширенной фильтрации командой данные Фильтр ► Расширенный фильтр можно выполнить фильтрацию на месте или извлечь отфильтрованные записи и поместить их отдельно, на любой рабочий лист любой открытой рабочей книги.

Последовательность выполнения

1. Запустите программу Excel (Пуск ► Программы ► Microsoft Office ► Microsoft Excel) и откройте рабочую книгу Лабораторные работы по Excel.xls.

2. Создайте новый рабочий лист (Вставка ► Лист), дважды щелкните на его ярлычке и присвойте ему имя Сведения о поставках.

3. Предполагается, что предприятие получает пять видов материалов: бумагу, фанеру, картон, полиэтилен и ткань – от пяти поставщиков, находящихся в Братске, Казани, Курске, Мурманске и Череповце. Каждый из поставщиков может поставлять любой вид материалов. Поставки производятся не чаще раза в месяц, единица измерения – тонна.

4. В ячейки А1 – D1 введите заголовки полей базы данных, соответственно: Месяц, Поставщик, Товар, Объем.

5. Введите 15 записей, имеющих описанную выше структуру. Реальные объемы поставки значения не имеют.

6. Выделите методом протягивания диапазон А1:D1 и дайте команду Формат ► Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру.

7. На вкладке Шрифт задайте размер шрифта в 14 пунктов и в списке Начертание выберите вариант Полужирный. Щелкните на кнопке ОК.

8. Выделите методом протягивания диапазон А2:D16. Щелкните правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек. Задайте выравнивание по горизонтали По левому краю и щелкните на кнопке ОК.

9. Выделите методом протягивания диапазон A1:D16. Щелкните на раскрывающей кнопке рядом с кнопкой Границы на панели инструментов Форматирование и задайте для этих ячеек широкую внешнюю рамку (кнопка в правом нижнем углу открывшейся палитры).

10. Для этого диапазона установите внутренние границы по своему усмотрению.

11. Дважды щелкните на границе между заголовками столбцов А и В, В и С, С и D, D и E. Обратите внимание, как при этом изменяется ширина столбцов.

12. Общая сортировка базы данных. Сделайте текущей любую ячейку базы данных и дайте команду Данные ► Сортировка. Убедитесь, что при этом выделяется вся (кроме заголовков полей) база данных.

13. В списке Сортировать по выберите пункт Месяц и режим по возрастанию.

14. В Списке Затем по (вторичная сортировка) выберите пункт Поставщик и режим по возрастанию.

15. В Списке В последнюю очередь, по выберите пункт Товар и режим по возрастанию. Щелкните на кнопке ОК.

16. Убедитесь, что база данных отсортирована по указанным критериям.

17. Последовательная сортировка базы данных. С помощью кнопки Отменить на панели инструментов восстановите прежний порядок записей базы данных. Того же порядка сортировки можно добиться другим способом.

18. Выберите любую ячейку в столбце Товар и щелкните на кнопке Сортировка по возрастанию на панели инструментов.

19. Выберите любую ячейку в столбце Поставщик и щелкните на кнопке Сортировка по возрастанию на панели инструментов.

20. Выберите любую ячейку в столбце Месяц и щелкните на кнопке Сортировка по возрастанию на панели инструментов. Убедитесь, что итоговый порядок сортировки тот же, что и в предыдущем случае. Обратите внимание что в этом случае мы сначала провели третичную сортировку, затем вторичную и на последнем этапе первичную.

21. Фильтрация данных. Чтобы включить режим фильтрации, дайте команду Данные ► Фильтр ► Автофильтр. Обратите внимание на появление раскрывающих кнопок у заголовков полей базы данных.

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

23. Чтобы отобрать наиболее крупные разовые поставки, щелкните на раскрывающей стрелке у поля Объем и выберите в списке вариант Первые 10.

24. Выберите с помощью счетчика число 5 и далее пункт наибольших и вариант % от количества элементов. Щелкните на кнопке ОК. В результате будет отобрано 5 % записей, содержащих наибольшие значения объема поставок.

25. Чтобы отменить режим фильтрации записей, еще раз дайте команду данные Фильтр ► Автофильтр.

26. Сохраните рабочую книгу.

Содержание отчета

1. Выполнение всех пунктов задания.

2. Выполнение задания для самостоятельной работы.

3. Ответы на контрольные вопросы.

Контрольные вопросы

1. Что представляет собой база данных? Из чего состоит база данных?

2. Каких правил стоит придерживаться, чтобы содержимое листа рассматривалось как база данных?

3. Что такое автозавершение?

4. Какие операции можно выполнять с базой данных?

5. Каким образом выполняется сортировка данных?

6. Что называется фильтрацией данных?

7. Какие виды фильтров существуют в Excel?

8. Как устанавливается автофильтр?

9. Какие условия фильтрации могут использоваться при применении автофильтра?

10. В чем заключаются особенности расширенного фильтра?

Задания для самостоятельной работы

1. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Должность, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

2. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Стаж работы, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

3. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Удаленность, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

4. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Сложность, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

5. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Вредность, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

6. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Классность, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

7. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Ученое звание, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

8. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Ученая степень, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

9. Откройте книгу Задания для самостоятельнойработы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Воинское звание, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

10. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Образование, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

11. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Категория, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

12. Откройте книгу Задания для самостоятельной работы. Выберите неиспользуемый рабочий лист или создайте новый и переименуйте его как Задание 4. В ячейках А1-D1 введите заголовки полей базы данных: ФИО, Дата рождения, Стаж работы по специальности, Надбавка. Введите 15 записей, имеющих описанную выше структуру. Отсортируйте таблицу по 1-му, а затем по 3-му полю. Установите автофильтр и отберите записи с наибольшими значениями в поле 4.

Лабораторная работа № 15

Тема: Построение сводной таблицы и сводной диаграммы

Время на выполнение – 2 часа

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

Основные сведения по теме

Дата добавления: 2014-10-15