Фильтр, сортировка, промежуточные итоги

Откройте новый файл, на листе 1 создайте таблицу:

Рисунок 35. Список сотрудников

В столбце Заработная плата обозначение обозначается р. – выполнено через денежный формат!

Задание:

Добавьте в книгу Лист 4. Выделите весь лист, нажав на кнопку, обозначенную на рис. Цифрой 1. Скопируйте выделение в буфер обмена, перейдите на лист 2 и вставьте содержимое буфера, начиная с ячейки А1, перейдите на Лист 3 и снова вставьте содержимое буфера, начиная с ячейки А1, с листом 4 сделайте тоже самое.

Начнем работу с листа 1.

Фильтрация

Переименуйте Лист 1 в «Фильтр». Фильтр – инструмент, который позволяет из массива данных выбрать элементы. Соответствующие заданному критерию.

Задание:

Для участия в новом проекте необходимо выбрать сотрудников, которые соответствуют требованиям: Высшее профессиональное образование. Опыт работы не менее 3-х лет.

• Выделите строку с заголовками таблицы – строка 2;

• На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Фильтр. Около каждого заголовка таблицы появится стрелка фильтра;

• Выполним первую выборку – оставим только сотрудников с высшим образованием. Разверните свиток фильтра у заголовка Образование, оставьте галочку только у группы ВПО и нажмите ОК.

• Результат – данные, которые не соответствуют требованию – скрыты.

• Выполните вторую фильтрацию по опыту работы, здесь необходимо в фильтре выбрать команду Числовые фильтры…Больше или равно и в окне задать больше или равно 3.

Выбраны 4 сотрудника, которые соответствуют требованиям.

Задание:

Рассчитаем их суммарную заработную плату.

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

Рисунок 36. Копирование отфильтрованной таблицы

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

Задание:

Рассчитайте среднее значение руководящего состава предприятия, используя фильтр и функцию среднего значения. Таблицу с результатами фильтрации вставьте, начиная с ячейки А23.

Сортировка

Перейдите на лист 2, назовите его Сортировка.

Если фильтр скрывает ненужные данные, то сортировка – выстраивает их в нужном порядке: по алфавиту, возрастанию или убыванию.

Отсортируем таблицу по фамилиям по алфавиту:

• Выделим диапазон А2:F13 – выделять необходимо полностью всю таблицу с заголовками столбцов, но без итогов строк.

• Перейдите на вкладку Данные…Сортировка. В окне поле Сортировать по выберите столбец ФИО, порядок от А до Я и нажмите ОК.

Сортировка может быть многоуровневая, например, необходимо отсортировать по должностям, а внутри каждой должности по сумме заработной платы – начиная с наибольшей.

Выделите всю таблицу, Данные…Сортировка. Сортировать по Должность от А до Я, затем нажать кнопку Добавить уровень и в поле Затем по выбрать столбец Заработная плата, по убыванию и нажать ОК.

Сортировка произведена по должности и внутри каждой должности по заработной плате по убыванию.

Промежуточные итоги

Перейдите на Лист 3, назовем его Итоги.

Задание:

Рассчитайте сумму заработной платы по каждому наименованию должностей:

• Необходимо отсортировать таблицу по столбцу Должность от А до Я.

• Выделить всю таблицу с заголовками столбцов. В этом случае итоговую строку в таблицу добавлять не нужно! Она рассчитывается автоматически!

• На вкладке Данные…в группе Структура…выбрать команду Промежуточные итоги.

Рисунок 37. Промежуточные итоги

Комментарии к рисунку: как только в столбце Должность одно значение изменится на другое, необходимо рассчитать сумму в столбце Заработная плата (для этого и производилась сортировка).

На рисунке ниже представлен результат подведения промежуточных итогов.

Рисунок 38. Результат подведения промежуточных итогов

Слева от листа появилась Структура, Вы можете оставлять только общие суммы по каждой должности. Работая с кнопками (цифра 1) или наоборот, отображать все уровни структуры.

Задание:

Отмените промежуточные итоги

Данные…Промежуточные итоги…Убрать все.

Задание:

Рассчитайте Среднее значение заработной платы для сотрудников с разным уровнем образования.

Функции СуммУсли и СчетУсли

Перейдите на лист 4, переименуйте его в СуммЕсли.

Рассчитаем сумму заработной платы руководителей:

Активизируйте ячейку F15;

Перейдите на вкладку Формулы…Вставить функцию. В диалоговом окне выберите Категория – Полный алфавитный перечень и найдите функцию СуммЕсли.

Рисунок 39. СуммЕсли

В поле Диапазон необходимо указать диапазон, котором нужно найти должность «руководитель», в поле Критерий – укажите ячейку с искомым значением – с должностью «руководитель». В поле диапазон суммирования покажите диапазон, из которого необходимо брать цифры для сложения. Нажмите ОК.

Задание:

Рассчитайте сумму заработной платы специалистов и рабочих, используя СуммЕсли.

СчетЕсли

Если стоит задача рассчитать количество упоминаний элемента в списке, используется функция СчетЕсли.

Рассчитаем сколько на предприятии руководителей, специалистов и рабочих.

В ячейках А19, А20, А21 введите соответствующие названия строк.

Активизируйте ячейку F19. Перейдите на вкладку Формулы…Вставить функцию. В диалаговом окне выберите Категория – Полный алфавитный перечень и найдите функцию СчетЕсли.

Рисунок 40. СчетЕсли

В поле Диапазон выделите те ячейки, где может содержаться искомый элемент – руководитель, в поле Критерий – щелкните по ячейке, которая содержит искомый элемент.

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

Сохраните файл в Вашей папке под именем «Сотрудники».

Сводные таблицы

Инструмент «Сводная таблица» позволяет объединить большую таблицу в более компактную по нужным условиям. На следующем листе книги создайте таблицу:

Рисунок 41. Пример для сводной таблицы

Рисунок 42. Создание сводной таблицы

Рисунок 43. Список полей сводной таблицы

Рисунок 44. Параметры полей сводной таблицы

Рисунок 45. Пример формирования сводной таблицей

Наша задача с помощью сводной таблицы рассчитать суммарное значение выручки по каждому району. А также среднее значение. Для этого:

Выделите всю анализируемую таблицу: А1:С8. Если Вы будете дополнять таблицу, тогда выделяйте больший диапазон, включающий пока пустые ячейки, например, А1:С15.

На вкладке Вставка выберите первую кнопку слева Сводная таблица…Сводная таблица. Поле Выбрать таблицу или диапазон уже заполнено (Лист4!$A1:$C$8).

Укажите куда следует поместить сводную таблицу: на новый лист или этот же. Выберите На существующий лист и щелкните на ячейке, с которой будет начинаться левый верхний угол сводной таблицы. Для нашего примера это может быть ячейка А20.

В правой части программы должно появиться окно Список полей сводной таблицы, где необходимо выбрать данные для сведения (объединения).

Поставьте галочки напротив полей Район расположения и Выручка за январь. Данные должны объединиться в таблицу с вычислением суммы по каждому району.

Добавим еще один столбец в сводной таблице, который будет отображать средне значение по каждому району. Для этого: схватите мышью поле Выручка за январь и перетащите в поле∑ Значения. Это действие приведет к добавлению в Сводной таблице еще одного столбца с суммой по районам. Щелкните правой кнопкой мыши по названию нового столбца и выберите команду Параметры полей значений. В диалоговом окне выберите операцию, которая будет выполняться в этом столбце при объединении данных. В нашем примере это Среднее.

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