Комментарий
Приступая к выполнению лабораторных работ, необходимо предварительно изучить учебный материал по данной теме Лекция 6. Табличный процессор MS Excel, который освещает назначение и принципы работы табличного процессора. Учебный материал, как лекционный, так и лабораторные работы, осваивается с одновременной работой на компьютере. На компьютере Вы сможете выполнять действия, описанные в лекциях, и просматривать рисунки примеров.
1. Лабораторную работу необходимо выполнить два – три раза, постепенно осваивая и закрепляя новые знания
2. В учебном материале применяется следующая аббревиатура (сокращения):
a. ЛКМ – левая кнопка мыши;
b. ПКМ – правая кнопка мыши;
Лабораторная работа №17. MS EXCEL. Команда Данные. Фильтрация данных. (2 ч.)
Цель работы: ознакомиться с видами и методами фильтрации данных в электронных таблицах. Рассмотреть виды фильтров. Научиться использовать автофильтр для поиска данных в списке. Уметь применять условия отбора пользовательского автофильтра. Осуществлять фильтрацию списка посредством расширенного автофильтра.
Введение
Табличный процессор MS Excel позволяет осуществлять фильтрацию данных, то есть выводить на экран данные, востребованные пользователем. Команда Данные, Фильтр – выводит на экран только те данные, которые отвечают заданным условиям. Например, из всех жильцов – только юношей и только призывного возраста. Отображение выбранных данных может происходить на месте исходной таблицы (пользовательский автофильтр) или на новом месте рабочего листа (расширенный автофильтр).
Расширенный фильтр позволяет не только изменить место отображения отфильтрованных данных, но и расширяет возможности отбора. В расширенном фильтре условия отбора записываются в отдельную область листа. В условия отбора может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых на несколько столбцов, а также условия, создаваемые как результат выполнения формулы. При наличии для одного столбца двух и более условий отбора, условия вводятся непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Фамилия» значения "Столетов*", "Смирнов*" или "Князев*".
1. Откройте меню кнопки ПУСК.
2. Выберите элемент Программы, MS Excel.
3. Выполните команду Файл.
4. Из списка последних открываемых файлов выберите Ведомость.
5. Откройте лист Сортировка.
Пользовательский автофильтр
1. Установите курсор в любую ячейку диапазона А11:D20.
2. Выполните команду Данные, Фильтр, Автофильтр.
В заголовках столбцов фильтруемой таблицы появляются стрелки автофильтра.
Рис.17-1. Фильтрация данных.
3. Откройте список Наименование товаров (щелкните стрелку) и укажите, какие данные должны располагаться в столбце: кабачки.
4. Чтобы отобразить все данные в таблице, откройте список Наименование товаров (щелкните стрелку) и укажите (Все).
Задание 1. Отобразите в таблице данные по товарам: помидоры и салат.
· Откройте список Наименование товаров, выберите (Все).
· Откройте список Наименование товаров, выберите (Условие).
· В окне Пользовательский автофильтр: в верхней строке в левом списке выберите равно, в правом –помидоры; в нижней строке в левом списке выберите равно, в правом –салат. Установите переключатель ИЛИ: достаточное выполнение условий. Сказать проще, товар не может одновременно быть помидором и салатом.
Рис.17-2. Фильтрация по наименованию
· Нажмите кнопку ОК.
Задание 2.Отобразите в таблице данные по товарам, цена поставки которых более 30 рублей.
· Откройте список Наименование товаров, выберите (Все).
· Откройте список Цена поставки, выберите (Условие).
· В окне Пользовательский автофильтр: в верхней строке в левом списке выберите больше или равно, в правомвыберите 30р.
· Нажмите кнопку ОК.
Задание 3. Отобразите в таблице данные по товарам, дата поставки которых с 4.08.2007 по 17.08.2007.
· Откройте список Цена поставки, выберите (Все).
· Откройте список Дата поставки, выберите (Условие).
· В окне Пользовательский автофильтр: в верхней строке в левом списке выберите больше или равно, в правом04.08.2007; в нижней строке в левом списке выберите меньше или равно, в правом напишите 17.08.2007. Установите переключатель И: одновременное выполнение условий (больше 04.08.2007 – может быть 11.11.2010, а меньше 17.08.2007 – может быть 01.01.2000).
Рис.17-3. Фильтрация по дате
· Нажмите кнопку ОК.
Рис.17-4. Результаты фильтрации
Задание 4. Отобразите в таблице данные по товарам, дата поставки которых до 18.08.2007, с ценой продажи более 30 рублей.
· Откройте список Дата поставки, выберите (Все).
· Откройте список Дата поставки, выберите (Условие).
· В окне Пользовательский автофильтр: в верхней строке в левом списке выберите меньше или равно, в правом - 18.08.2007.
· Нажмите кнопку ОК.
· Откройте список Цена продажи выберите (Условие).
· В окне Пользовательский автофильтр: в верхней строке в левом списке выберите больше или равно, в правом - 30р.
· Нажмите кнопку ОК.
· Обратите внимание на цвет стрелок автофильтра для столбцов, по которым осуществлялась фильтрация данных.
Рис.17-5. Фильтрация по двум столбцам.
Чтобы убрать стрелки выполните команду Данные, Фильтр, Автофильтр.
Расширенный фильтр
1. Выполните команду Вставка, Лист.
2. Щелчком ПКМ по ярлыку листа вызовите контекстное меню и выберите Переименовать. Введите имя листа Фильтры.
3. На листе Фильтры заполните таблицу.
Рис.17-6. Начисления за вечернее и ночное время работы.
Задание 5. Отобразите в таблице данные по сотрудникам, отработавшим в вечернее и ночное время. Одновременное выполнение условий - условия отбора записываются в одну строку.
1. Составим условия отбора для расширенного фильтра.
1. В ячейке А11 напишите: Вечернее и ночное время.
2. Скопируйте в буфер памяти заголовок из ячейки D2.
3. Вставьте заголовок в ячейку А12.
4. В ячейке А13 напишите условие >=0.
5. Скопируйте в буфер памяти заголовок из ячейки F2.
6. Вставьте заголовок в ячейку B12.
7. В ячейке B13 напишите условие >=0.
8. Скопируйте заголовок таблицы A2:G2. Выделите ячейки A2:G2. ПКМ вызовите контекстное меню и выберите Копировать.
9. Вставьте заголовок в диапазон ячеек А15: G15. Установите курсор в ячейку А15. Выполните команду Вставка.
10. Выделите диапазон ячеек А2:G8.
11. Выполните команду Данные, Фильтр, Расширенный фильтр.
Рис. 17-7. Параметры расширенного фильтра.
12. Установите переключатель скопировать результат в другое место.
13. Проверьте правильность диапазона исходной таблицы в поле Исходный диапазон:
14. Установите курсор в поле Диапазон условий:. Выделите на рабочем листе диапазон ячеек А12:В13.
15. Установите курсор в поле Поместить результат в диапазон:. Выделите на рабочем листе диапазон ячеек таблицы отобранных значений А15:G21.
16. Нажмите кнопку ОК.
Рис.17-8. Сотрудники, отработавшие в вечернее и ночное время.
Задание 6. Отобразите в таблице данные по сотрудникам, отработавшим хотя бы одну смену в вечернее или ночное время. Достаточность выполнения хотя бы одного из условий – условия записываются в один столбец.
1. Составим условия отбора
2. В ячейке А18 напишите: Вечернее или ночное время.
3. Скопируйте в буфер памяти заголовок из ячейки D2.
4. Вставьте заголовок в ячейку А19.
5. В ячейке А20 запишем условие >=0.
6. Скопируйте в буфер памяти заголовок из ячейки F2.
7. Вставьте заголовок в ячейку A21.
8. В ячейке A22 напишите условие >=0.
9. Скопируйте заголовок таблицы A2:G2. Выделите ячейки A2:G2. ПКМ вызовите контекстное меню и выберите Копировать.
10. Вставьте заголовок в диапазон ячеек А24: G24. Установите курсор в ячейку А24. Выполните команду Вставка.
11. Выделите ячейки А2:G8.
12. Выполните команду Данные, Фильтр, Расширенный фильтр.
13. Установите переключатель скопировать результат в другое место.
14. Проверьте правильность диапазона исходной таблицы в поле Исходный диапазон:
15. Установите курсор в поле Диапазон условий:. Выделите на рабочем листе диапазон ячеек А19:A22.
16. Установите курсор в поле Поместить результат в диапазон:. Выделите на рабочем листе диапазон ячеек таблицы отобранных значений А24:G30.
17. Нажмите кнопку ОК.
Рис. 17-9. Сотрудники, отработавшие смены в ночное или вечернее время
Вопросы для самоконтроля
1. Что понимается под фильтрацией данных?
2. Назовите виды фильтрации.
3. Чем отличается расширенный фильтр от пользовательского фильтра?
4. Как записываются условия отбора данных при одновременном выполнении нескольких условий?
5. Как записываются условия отбора данных при достаточном выполнении хотя бы одного из условий?
Самостоятельное задание
1. Выполните фильтрацию данных для 10, 11, 12 разрядов посредством пользовательского автофильтра.
Рис..17-10. Результаты пользовательского фильтра
2. Выполните фильтрацию данных для сотрудников 11 разряда, отработавших вечерние смены.
Рис.17-11. Результаты расширенного фильтра.
