2. Фильтрация

8. Скопируйте лист Исходные данные в конец книги. Переименуйте его в Фильтры.

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

10. Отберем сотрудников с высшим образованием. Для этого нажмите кнопку в ячейке E1 и снимите флажки со значений «среднее» и «среднее спец. ». Нажмите кнопку ОК.

11. Теперь вы видите данные сотрудников только с высшим образованием. Обратите внимание на то, что кнопка в ячейке E1 изменила вид, показывая, что была произведена фильтрация. Кроме того, номера строк выделены синим цветом и идут не подряд – это означает, что остальные данные никуда не делись, а лишь были скрыты. Их можно увидеть вновь, сняв фильтр. Для снятия фильтра нажмите кнопку в ячейке E1 и выберите Снять фильтр с «Образование» или нажмите кнопку Очистить, которая находится в группе Сортировка и фильтр на вкладке Данные.

12. Создадим более сложный фильтр. Нажмите кнопку фильтрации в ячейке G1 и из выпадающего списка Числовые фильтры выберите Больше… В появившемся диалоговом окне введите значение 200 и нажмите кнопку ОК. Теперь должны отображаться только сотрудники, чей оклад больше $200.

13. Можно вывести список сотрудников, чей оклад ниже среднего. Снимите предыдущий фильтр, нажмите кнопку фильтрации в ячейке G1 и из выпадающего списка Числовые фильтры выберите Ниже среднего.

14. Ещё один интересный фильтр – Первые 10… Он позволяет найти заданное количество минимальных или максимальных значений в столбце. Снимите предыдущий фильтр, нажмите кнопку фильтрации в ячейке C1 и из выпадающего списка Числовые фильтры выберите Первые 10… Задайте нужное количество отображаемых элементов списка (например, 3) и нажмите кнопку ОК. Теперь видны данные только о 4 сотрудниках – т. к. двое из них имеют одинаковый год рождения, то в списке отображаются оба.

15. Найдем в списке сотрудников самого старого инженера. Снимите предыдущий фильтр, нажмите кнопку фильтрации в ячейке B1 и снимите галочки со всех должностей, кроме инженера. Нажмите кнопку ОК. Нажмите кнопку фильтрации в ячейке C1. Теперь в поле с годами рождения вы можете видеть годы рождения не всех сотрудников, а только тех, которые были отобраны на предыдущем шаге фильтрации. Несложно выбрать минимальный из них и снять флажки со всех остальных. Нажмите кнопку ОК. Теперь в списке остался только один, самый старый, инженер.

16. Однако иногда бывают ситуации, когда нельзя обойтись стандартными фильтрами. Например, мы ходим отобрать сотрудников пенсионного возраста. Ими будут мужчины старше 60 и лет и женщины старше 55 лет. Получается достаточно сложно условие. Но и его можно задать, используя так называемый расширенный фильтр.

17. Скопируйте лист Исходные данные в конец книги. Переименуйте его в Пенсионный возраст.

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

19. В пустые строки надо записать условия фильтрации. При этом условия в ячейках одной строки объединяются логической операцией И, а условия в разных строках объединяются логической операцией ИЛИ.

20. В ячейку C2 введите формулу =" < 1948". В строке формул можно будет видеть именно такой текст, а в самой ячейке будет отображаться < 1948. Для фильтрации это условие означает, что значения ячеек в столбце C должны быть меньше 1948.

21. В ячейку D2 введите формулу =" =м".

22. В ячейки C3 и D3 введите формулы =" < 1953" и =" =ж" соответственно. Теперь формулы в диапазоне C2: D3 задают необходимое условие.

23. Поставьте курсор в одну из ячеек таблицы с данными и нажмите кнопку Дополнительно, которая находится в группе Сортировка и фильтр на вкладке Данные. Появится диалоговое окно Расширенный фильтр (см. рис. 3). В поле Исходный диапазон: должна автоматически появиться ссылка на диапазон с исходными данными $A$5: $G$27. Если этого не произошло, вставьте ссылку на этот диапазон самостоятельно. В поле Диапазон условий: вставьте ссылку на диапазон $A$1: $G$3. Будьте внимательны – диапазон условий не должен включать пустых строк! В этом случае фильтрация работать не будет. Нажмите кнопку ОК. Если вы всё сделали правильно, в списке должны отображаться только пять человек, удовлетворяющих условию.

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

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

Рис. 3. Диалоговое окно «Расширенный фильтр»

26. В ячейку H1 введите название условия «Пенсионный возраст».

27. В ячейку H2 введите формулу =И(C6< ГОД(СЕГОДНЯ())-60; D6=" м" ). В формуле использованы относительные ссылки на ячейки C6 и D6, которые являются первыми ячейками с данными в таблице фильтруемых данных. Функции ГОД и СЕГОДНЯ вычисляют год (из некоторой даты) и сегодняшнюю дату соответственно. Эти функции не имеют аргументов. Если необходимо использовать функции с аргументами, например, для вычисления среднего значения, аргументы должны задаваться абсолютными ссылками.

28. В ячейку H3 введите формулу =И(C6< ГОД(СЕГОДНЯ())-55; D6=" ж" ). Обратите внимание на то, что формулы, используемые для фильтрации, должны возвращать логические значения.

29. Поставьте курсор в одну из ячеек таблицы с данными и нажмите кнопку Дополнительно, которая находится в группе Сортировка и фильтр на вкладке Данные. В появившемся диалоговом окне Расширенный фильтр в поле Исходный диапазон: введите ссылку на диапазон с исходными данными $A$5: $G$27. В поле Диапазон условий: вставьте ссылку на диапазон $A$1: $H$3. Нажмите кнопку ОК. Если вы всё сделали правильно, результат должен быть таким же, как и результат, полученный предыдущим способом, но второй способ является более универсальным.

⇐ Предыдущая 123 4 Следующая ⇒