Работа с фильтрами и подведение итогов

Расчет заработной платы

Ознакомьтесь с разделом «Фильтры» в начале данного Практикума.

1. Наберите таблицу.

В графы, которые не выделены другим цветом, введите исходные данные. Фамилия «Иванов» должна помещаться в ячейке A4.

Составьте и введите формулы в графы, которые закрашены серым цветом:

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

Среднее «к выдаче»:

2. Рассчитайте графы: Удержано = 13% от Начислено.

К выдаче = Начислено – Удержано

Среднюю зарплату (к выдаче) с помощью функции СРЗНАЧ.

Скопируйте таблицу на лист 2.

3. Проверьте работу команды автофильтра: панель Данные- группа Сортировка и фильтр - Фильтр - Автофильтр, выбрав в раскрывшемся списке значение, затем условие «Зарплата к выдаче >6000 и <7000».

4. Используя в панели Данные – Сортировка и фильтр -Дополнительно - Расширенный фильтр, выберите сотрудников 2 и 3 отделов (выводите только данные по графам «Фамилия», «Отдел», «К выдаче»), зарплата которых к выдаче > 5000.

Для этого сформируйте область критериев, скопировав в 15-ю строку заголовки столбцов. В 16-ю строку введите необходимые условия для отдела и для зарплаты (>1 и >5000 соответственно). Для результатов выборки поместите на этот же лист в диапазон, начинающийся с ячейки G2, заголовки столбцов Фамилия, Отдел, К выдаче.

5. Выберите всех сотрудников («Фамилия», «Отдел», «К выдаче»), зарплата которых к выдаче выше средней для данного списка.

Для этого в ячейку А20 введите заголовок «Условие», а в ячейку А21 введем формулу =Е4>СРЗНАЧ($E$4:$E$10) –это вычисляемый критерий. Воспользуйтесь расширенным фильтром.Задайте диапазоны исходных данных и условий. Результаты выборки поместите на этот же лист, начиная с ячейки G10.

Запомните:

При использовании вычисляемых критериев следует придерживаться следующих правил:

а) заголовок под вычисляемым критерием не должен совпадать ни с одним из заголовков списка, он может быть пустой ячейкой;

б) ссылки на ячейки списка в левой части формулы вычисляемого критерия должны быть относительными, а ссылки на ячейки правой части формулы и ссылки на ячейки вне списка должны быть абсолютными.

6. Выберите сотрудников (только информацию, содержащуюся в графах «Фамилия», «Отдел», «К выдаче»), начисленная зарплата у которых меньше средней для данного списка. Результат поместите на этот же лист, начиная с ячейки G20.

7. Скопируйте Ведомость заработной платы (исходный список) на второй лист.

8. С помощью Данные – Структура - Промежуточные итоги рассчитайте среднее значение по графе «К выдаче» по каждому отделу.

9. С помощью функции СУММЕСЛИ подсчитайте общую сумму зарплаты по графе «К выдаче» для сотрудников, зарплата которых <6000. Результаты представьте на 2-м листе.

10. С помощью функции СЧЕТЕСЛИ подсчитайте количество человек, получающих <6000. Результат представьте на 2-м листе.

Практическая работа № 12