Технологическая карта № 3 Автофильтр. Расширенный фильтр

Технологическая карта № 3

Автофильтр. Расширенный фильтр

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

Задание 1. Автофильтр по значению

1. Найдите всех сотрудников отдела ИТО (на ленте Данные в группе Сортировка и фильтр выберите команду Фильтр).

2. Найдите всех заведующих отделом.

3. Сформируйте список сотрудников отдела МПО, имеющих стаж на данном предприятии более 5 лет.

4. Сформируйте список мужчин старше 60 лет из отдела ФЭО.

5. Найдите сотрудников по именем Александр Александрович.

Задание 2. Дополнительные критерии автофильтра

Числовые фильтры

1. Найдите 12 самых молодых сотрудников (рис. 8).

Рисунок 8 - Наложение условия по списку

2. Найдите 12% самых молодых сотрудников (рис. 9).

Рисунок 9 - Наложение условия по списку

3. Найдите 20% самых высокооплачиваемых сотрудников.

4. Старейшим сотрудникам предприятия (стаж которых наибольший) к юбилею предприятия, полагается премия в размере 25% от оклада. Найдите 20 таких сотрудников, скопируйте данные о них (ФИО, оклад) на отдельный лист Премия и высчитайте премию.

5. Найдите сотрудников, у которых стаж больше среднего по предприятию.

Текстовые фильтры

6. Сформируйте список дизайнеров.

7. Найдите сотрудников, фамилия которых начинается на букву К.

8. Найдите сотрудников, отца которых зовут Виктор.

9. Найдите сотрудников, фамилия которых заканчивается на ин (Мишин, Рубин и т.п.).

10. Найдите сотрудников, у которых в отчестве «прячется» слово тон.

Фильтры по дате

11. Найдите сотрудников, у которых день рождения в январе (Все даты за период).

12. Найдите сотрудников, родившихся в 1940-1949гг.

Настраиваемый фильтр

13. Сформируйте список сотрудников из отделов ИТО и ФЭО (рис. 10).

Рисунок 10 - Составное условие с союзом ИЛИ

14. Сформируйте список сотрудников, фамилии которых начинаются на А-И (рис. 11).

Рисунок 11 - Составное условие с союзом И

15. Сформируйте список работников отдела ИВЦ, имеющих оклад от 5000 до 15000 руб. включительно.

16. Сформируйте список мужчин из отделов МПО и ФЭО, родившихся в 70-е годы (1970-1979 гг).

Задание 3. Расширенный фильтр

Найдите сотрудников отдела МПО, оклад которых меньше 10000 руб.

Комментарии:

·         добавьте в книгу Excel новый рабочий лист Критерии;

·         заполните ячейки А1:В2 (рис. 12);

·         присвойте диапазону А1:В2 имя Кр1;

Рисунок 12 - Критерий Кр1

·         перейдите на лист с базой данных и на ленте Данные в группе Сортировка и фильтр выберите команду Дополнительно, в появившемся окне задайте параметры (рис. 13):

Обработка: фильтровать список на месте

Исходный диапазон: $A$1:$Р$101 (или База_данных)

Диапазон условий: Кр1 (или Критерии!$A$1:$B$2)

Рисунок13 - Диалоговое окно Расширенный фильтр

·         восстановите исходный список, для этого на ленте Данные в группе Сортировка и фильтр выберите команду Очистить;

·         выведите отфильтрованный список в другом месте рабочего листа, для этого в окне Расширенный фильтр установите переключатель в положение Скопировать результат в другое место, после чего заполните поле Поместить результат в диапазон, указав какую-нибудь ячейку ниже списка (например, А105).

Задание 4. Расширенный фильтр

На основе предыдущего задания и критерия Кр1 получите результирующую таблицу из полей Фамилия, Табельный_номер и Оклад.

Комментарии:

·         скопируйте заголовки столбцов Фамилия, Табельный_номер и Оклад в А105, В105 и С105 соответственно;

·         вызовите диалоговое окно расширенного фильтра;

·         задайте параметры (в качестве диапазона, куда надо поместить результат задать: А105:С105);

·         скопируйте новый список в отчет.

Задание 5. Расширенный фильтр

Найдите сотрудников отдела ИТО, чей оклад в интервале от 15000 до 20000 руб. включительно. В отфильтрованный список включите поля Фамилия, Имя, Отчество, Табельный_номер, Отдел, Оклад.

Примечание. Если переформулировать задание, то получится, что нам надо найти сотрудников, для которых выполняется условие: (Отдел=ИТО) И (Оклад>=15000) И (Оклад<=20000).

Поскольку все условия связаны союзом И, то располагаться они должны в одной строке (рис. 14).

Рисунок 14 - Критерий Кр2

Задание 6. Расширенный фильтр

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

Примечание. Так как в нашей базе данных один и тот же человек не может иметь две должности, то условие будет:

директор ИЛИ зам.директора ИЛИ зав.отделом

Используя подстановочные знаки, условие можно сформулировать так:

*директор* ИЛИ *отдел*

Следовательно, при формировании критерия условия надо расположить в двух строках (рис. 15).

Рисунок 15 - Критерий Кр3

Задание 7. Расширенный фильтр

Сформируйте список женщин (с любым окладом) и сотрудников, оклад которых меньше 10000. В отфильтрованный список включите поля Фамилия, Пол и Оклад.

Примечание. Критерий показан на рис. 16. Пустая ячейка означает любой. Условия соединены операцией ИЛИ.

Рисунок 16 - Критерий Кр4

Задание 8. Расширенный фильтр

Какие отделы представлены в списке?

Примечание 1. Надо выбрать записи по критерию Отдел Все. Не забудьте в критерий включить пустую ячейку под заголовком поля (рис. 17).

Примечание 2. В окне Расширенный фильтр не забудьте установить флажок Только уникальные записи.

Рисунок 17 - Критерий Кр5

Задание 9. Расширенный фильтр

Найдите мужчин не старше 30 лет из отделов ИТО и ИВЦ (рис. 18). В ответную таблицу включите поля Фамилия, Имя, Отчество, Отдел, Возраст.

Рисунок 18 - Критерий Кр6

Задание 18. Расширенный фильтр

Кто из сотрудников отдела МПО имеет оклад, превышающий средний оклад по предприятию?

Примечание. При написании критерия с вычисляемым значением во втором столбце заголовок либо не пишут, либо он не должен совпадать ни с одним названием поля базы данных. В ячейке В2 (рис. 19) записывается формула, по которой первая ячейка с окладом из списка (I2) сравнивается со средним значением из диапазона ($I$2:$I$101).

Рисунок 19 - Критерий Кр7

Задание 19. Расширенный фильтр

Сформулируйте самостоятельно критерии и получите ответные таблицы.

1. Список мужских (женских) имен без повторений.

2. Список женщин из отделов АОП и ИТО, имеющих стаж не менее 5 лет. В список включите поля ФИО, Отдел и Стаж.

3. Список пенсионеров по возрасту (для мужчин пенсионный возраст 65 лет, для женщин 60).

4. Молодым сотрудникам в возрасте от 18 до 25 лет полагается материальная помощь в размере 25% от оклада. Сформируйте список с указанием фамилии, даты рождения и оклада. Скопируйте полученный список на отельный лист и подсчитайте размер материальной помощи.

5. Кто из сотрудников имеет оклад меньше среднего оклада по предприятию?

6. Кто из сотрудников имеет наименьший оклад по предприятию?

7. Определите, у кого средний заработок в целом по предприятия больше - у мужчин или у женщин.

8. Выясните, имеются ли в списке совпадающие табельные номера (выведите только уникальные значения табельных номеров; после чего сравните их количество (функция СЧЕТ) с количеством сотрудников по списку).