Использование расширенного фильтра
'Пошаговые инструкции
1. На свободном месте листа сформировать блок критериев:
1.1 скопировать имена полей из исходной таблицы полностью или частично;
1.2 записать условие поиска – критерий под соответствующим именем поля;
2. выделить исходную таблицу;
3. активизировать пункт меню Данные/Фильтр/Расширенный фильтр;
4. в диалоговом окне установить точкой режим размещения результата фильтрации: в исходной таблице (Фильтровать список на месте) или в свободном поле – блоке вывода («Скопировать результат в другое место»);
5. Установить исходный диапазон – фильтруемую таблицу, введя адрес блока в адресную строку, если предварительно таблица не была выделена, или установив это автоматически с помощью «красной кнопки» справа и курсора;
6. Указать диапазон условий;
7. В режиме «Скопировать результат в другое место» установить место размещения блока вывода результатов фильтрации. Достаточно указать адрес левого верхнего угла блока вывода.
OK.
LПримечание
Критерий может быть простым (одно условие) или составным (включает два и более логических условий, соединенных логической операцией «и»/«или»). Условия критерия располагаются со второй строки блока, а в первой - имена полей исходной таблицы. Условия составного критерия могут находиться как в одной строке, так и в разных, например, во второй и третьей блока критериев. Однострочные условия связаны логическим «и», то есть поиск дает положительный результат в случае выполнения каждого из этих условий. Условия, располагаемые на разных строках относительно первой строки имен полей, связаны логической операцией «или». Это означает, что положительный результат дает выполнение хотя бы одного из условий.
:Тренинг умений при работе со списками средствами EXCEL
Задание.По таблице (см. табл.1) выполнить следующие действия:
1. Отсортировать таблицу по профессиям и фамилиям.
2. Организовать поиск работников списка по следующим условиям:
2.1. премия не превышает 35%;
2.2. разряд больше 4 или премия меньше 20%;
2.3. профессия – слесарь и заработная плата выше средней;
2.4. слесари с максимальным разрядом.
3. Определить
3.1. средний процент премии у работников с разрядом, большим 4;
3.2. минимальную заработную плату работника списка, у которого время отработки меньше среднего;
4. Определить численность работников и среднюю заработную плату по каждой профессии.
Сортировка таблицы:
Данные/Сортировка/; в пункте «Сортировать по»установить поле «Профессия» и способ (убывание/возрастание) точкой; в пункте «Затем по» - поле «ФИО»; OK. Результат представлен на табл. 2.
Фильтрация:
1. «премия не превышает 35%»:
· Данные/Фильтр/Автофильтр;
· в поле «Премия (%)» щелкнуть по кнопке;
· выбрать «Условие…»;
· установить операцию «Меньше или равно»;
· в строке вода справа для данной операции занести число 35
· OK.
Результат представлен на табл. 3.
2.«разряд больше 4 или премия меньше 20%»:
· Скопировать в блок критериев имена полей;
· в ячейку под именем поля «Разряд» занести текст «>4»;
· под именем поля «Премия (%)» пропустить ячейку, а под ней занести текст «< 20»;
· Данные/Фильтр/Расширенный фильтр;
· В диалоговом окне установить точкой режим размещения результата фильтрации «Скопировать результат в другое место»);
· Установить исходный диапазон – фильтруемую таблицу;
· Указать диапазон условий – блок, сформированный в первых трех пунктах.
· В режиме «Скопировать результат в другое место» установить место размещения блока вывода результатов фильтрации. Достаточно указать адрес левого верхнего угла блока вывода.
· OK.
Результат представлен на Таб. 4.
3. «профессия – слесарь и заработная плата выше средней»:
· под ячейку под именем поля «Профессия» занести текст «слесарь»;
· под именем поля «Сумма1)» занести вычисляемое условие «=G2>срзнач(G$2:G$12)»;
· Далее аналогично предыдущему заданию.
Результат представлен на Таб. 5.
Таблица 1. Ведомость заработной платы
| A | B | C | D | E | F | G | |
| ФИО | Профессия | Разряд | Премия (%) | Тар. ставка | Отработано (час) | Сумма (р.) | |
| Иванов А.А. | слесарь | 5,55 | 676,40 | ||||
| Степанов Т.О. | начальник цеха | 12,52 | 1703,53 | ||||
| Иванов К.Ж. | мастер | 7,94 | 393,27 | ||||
| Михайлов П.Д. | слесарь | 5,01 | 432,28 | ||||
| Иванов К.Г. | начальник цеха | 8,52 | 265,91 | ||||
| Михайлов К.Ю. | слесарь | 4,71 | 290,25 | ||||
| Черкашин А.К. | слесарь | 3,54 | 345,83 | ||||
| Степанов М.Т. | слесарь | 7,94 | 228,41 | ||||
| Пронин С.Б. | грузчик | 1,25 | 48,27 | ||||
| Прошутин Е.Л. | охранник | 3,54 | 187,63 | ||||
| Катышев О.Э. | слесарь | 55,56 | 338,36 |
Таблица 2. Сортировка по профессии
| A | B | C | D | E | F | G | |
| ФИО | Профес-сия | Раз-ряд | Премия (%) | Тар. ставка | Отрабо-тано (час) | Сумма (р.) | |
| Пронин С.Б. | грузчик | 1,25 | 48,27 | ||||
| Иванов К.Ж. | мастер | 79,45 | 3 93,78 | ||||
| Иванов К.Г. | началь-ник цеха | 85,23 | 2 65,91 | ||||
| Степанов Т.О. | ачаль-ник цеха | 125,26 | 17 03,53 | ||||
| Прошутин Е.Л. | охран-ник | 35,47 | 1 87,63 | ||||
| Иванов А.А. | слесарь | 55,56 | 6 76,40 | ||||
| Катышев О.Э. | слесарь | 55,56 | 3 38,36 | ||||
| Михайлов К.Ю. | слесарь | 47,12 | 2 90,25 | ||||
| Михайлов П.Д. | слесарь | 50,12 | 4 32,28 | ||||
| Степанов М.Т. | слесарь | 79,45 | 2 284,19 | ||||
| Черкашин А.К. | слесарь | 35,47 | 3 45,83 |
4. «слесарь с максимальным разрядом»:
· в ячейку под именем поля «Профессия» занести текст «слесарь»;
· под именем поля «Разряд1» занести вычисляемое условие «=С2=макс(С$2:С$12)»;
· Далее аналогично предыдущему заданию.
Результат представлен на Таб. 6.
Таблица 3. Результаты фильтрации п. 1
| A | B | C | D | E | F | G | |
| ФИО | Профес-сия | Разряд | Премия (%) | Тар. ставка | Отработано (час) | Сумма (р.) | |
| Пронин С.Б. | грузчик | 1,25 | 48,7 | ||||
| Иванов К.Ж. | мастер | 7,94 | 3 93,27 | ||||
| Иванов К.Г. | началь-ник цеха | 8,52 | 2 65,91 | ||||
| Прошутин Е.Л. | охран-ник | 3,54 | 1 87,63 | ||||
| Иванов А.А. | слесарь | 5,55 | 6 76,40 | ||||
| Михайлов К.Ю. | слесарь | 4,71 | 2 90,25 | ||||
| Михайлов П.Д. | слесарь | 5,01 | 4 32,28 | ||||
| Степанов М.Т. | слесарь | 7,4 | 2 28,41 | ||||
| Черкашин А.К. | слесарь | 3,4 | 3 45,83 |
Статистическая обработка
1. «средний процент премии у работников с разрядом, большим 4»:
· Подготовить критерий: в свободную ячейку скопировать имя поля «Разряд»; в ячейку следующей строки данного столбца занести условие «>4»;
· В свободную ячейку внести формулу «=дсрзнач(А1:G12;D1;<адрес размещения блока критерия предыдущего пункта>)»;
· <Enter>.
2. «минимальную заработную плату работника списка, у которого время отработки меньше среднего»:
· Подготовить критерий: под свободной ячейкой занести условие «=F2<срзнач(F$2:F$12)»;
· В свободную ячейку внести формулу «=дмин(А1:G12;G1;<адрес размещения блока критерия предыдущего пункта>)»;
· <Enter>.
Таблица 4. Результаты фильтрации п.2
| A | B | C | D | E | F | G | |
| ФИО | Про-фессия | Раз-ряд | Пре-мия (%) | Тар. ставка | От-рабо-тано (час) | Сумма (р.) | |
| Пронин С.Б. | груз-чик | 1,254 | 48,27 | ||||
| Иванов К.Ж. | мастер | 7,94 | 3 93,27 | ||||
| Иванов К.Г. | нача-льник цеха | 8,52 | 2 65,91 | ||||
| Степанов Т.О. | нача-льник цеха | 12,52 | 17 03,53 | ||||
| Прошутин Е.Л. | охран-ник | 3,54 | 1 87,63 | ||||
| Катышев О.Э. | слесарь | 5,55 | 3 38,36 | ||||
| Михайлов К.Ю. | слесарь | 4,71 | 2 90,25 | ||||
| Михайлов П.Д. | слесарь | 5,01 | 4 32,28 | ||||
| Степанов М.Т. | слесарь | 7,94 | 2 28,41 | ||||
| Черкашин А.К. | слесарь | 3,54 | 3 45,83 |
Таблица 5. Результаты фильтрации п. 3
| A | B | C | D | E | F | G | |
| Катышев О.Э. | слесарь | 5,55 | 3 38,36 | ||||
| Михайлов К.Ю. | слесарь | 4,71 | 2 90,25 | ||||
| Михайлов П.Д. | слесарь | 5,01 | 4 32,28 | ||||
| Степанов М.Т. | слесарь | 7,94 | 2 28,41 | ||||
| Черкашин А.К. | слесарь | 3,54 | 3 45,83 |
Таблица 6. Результаты фильтрации п.4