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

Подведение промежуточных итогов

Скопировать данные из таблицы на листе Сведения о сотрудниках на новый лист. Для этого любым способом скопировать таблицу (без итоговой строки) в буфер обмена, перейти к новому листу, на вкладке Главная в группе Буфер обмена в списке Вставить выбрать команду Специальная вставка и в открывшемся диалоговом окне выбрать вариант Вставить значения и форматы чисел. Присвоить листу имя Итоги 1

Отсортировать таблицу на листе Итоги 1 по отделам, а внутри отделов – по должностям.

Используя инструмент подведения промежуточных итогов, разбить список на группы по отделам и подвести промежуточные и общие итоги по полю Фамилия, используя операцию Количество (подсчитать количество сотрудников в каждом отделе).

Скопировать лист Итоги 1 под именем Итоги 2.

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

В таблице на листе Итоги 2 разбить отделы на подгруппы по должностям и подвести промежуточные итоги по полю Фамилия, используя операцию Количество (подсчитать количество сотрудников в каждом отделе, занимающих каждую должность).

Структурирование рабочих листов

На примере листов Итоги 1 и Итоги 2 освоить операции по управлению структурой листа: отображать разные уровни структуры, скрывать и выводить на экран подробные данные отдельных групп.

Открыть новый лист и составить таблицу со столбцами: Фамилия, Отдел, Оклад, Премия, Налог, Пенсионный фонд.

Ввести в таблицу 10-15 строк с данными:

Поля Фамилия, Отдел и Оклад заполнить произвольными значениями. В поле Отдел использовать 3-4 значения.

Премия составляет 15% от оклада, налог – 13% от суммы оклада и премии, пенсионный фонд – 1% от суммы оклада и премии.

Присвоить листу имя Структура 1.

Создать вертикальную и горизонтальную структуру листа. Для этого:

1. отсортировать данные по отделам;

2. для горизонтальной структуры вставить итоговые столбцы:

• после столбца Премия – столбец Начислено (сумма оклада и премии);

• после столбца Пенсионный фонд – столбец Удержано (сумма налога и отчислений в пенсионный фонд.);

• после столбца Удержано – столбец Выплатить (разность столбцов Начислено и Удержано.

3. для вертикальной структуры и вставить итоговые строки после каждого отдела, а также общую итоговую строку для всей таблицы: в столбцах с денежными величинами использовать функцию суммирования, в столбце Одел – функцию СЧЁТЗ (для подсчета количества сотрудников в каждом отделе и в целом); в первых ячейках итоговых строк ввести поясняющие надписи (например, «Всего по бухгалтерии);

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

На примере листа Структура 1 освоить операции по отображению разных уровней горизонтальной и вертикальной структуры.

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

Создать вертикальную и горизонтальную структуру на листе Структура 2 (аналогичную структуре на листе Структура 1) способом группировки данных вручную.

Консолидация данных

Таблицу на листе Сдельная оплата скопировать на новый лист (включить в копируемый блок константы: норму выработки, расценку, процент повышения расценки, процент налога). Константы должны располагаться вне таблицы. Листу присвоить имя 1.

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

В таблице на листе 1 изменить несколько значений в столбце Выработка и скопировать значения и форматы чисел таблицы на листе 1 на новый лист. Листу присвоить имя Май.

Внести еще несколько изменений в таблицу на листе 1 и скопировать значения и форматы чисел таблицы на листе 1 на новый лист. Листу присвоить имя Июнь.

Открыть новый лист, присвоить ему имя II квартал. На этом листе выполнить консолидацию по расположению данных из таблиц на листах Апрель, Май и Июнь. Для этого предварительно следует скопировать строку заголовков и столбец Фамилии И. О. на лист II квартал, а затем, выполняя консолидацию, выделять только численные данные таблиц без заголовков и без столбца Фамилия И. О. Включить режим Создавать связи с исходными данными.

Таблицу на листе Сдельная оплата (вместе с константами) скопировать на новый лист. Листу присвоить имя 2.

Скопировать значения и форматы чисел таблицы на листе 2 на новый лист (не включать в копируемый блок константы и итоговую строку). Листу присвоить имя Июль.

В таблицу на листе 2 добавить две новые строки и заполнить их данными, затем скопировать значения и форматы чисел таблицы на листе 2 на новый лист. Листу присвоить имя Август.

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

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

Открыть новый лист и присвоить ему имя 3. На этот лист три раза скопировать значения и форматы столбцов Фамилии И. О. и Выработка (вместе с заголовками). В каждую из трех таблиц внести различные изменения: удалить несколько строк, добавить новые строки, изменить значения столбца Выработка.

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

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

Открыть таблицу на листе Сведения о сотрудниках.

Создать сводную таблицу, отображающую количество сотрудников в каждом отделе. Для этого в область Названия строк поместить поле Отдел, в область Значения ‑ поле Фамилии.

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

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

Создать сводную таблицу, отображающую количество сотрудников, занимающих каждую должность в каждом отделе. Для этого в область Названия строк поместить поле Должность, в область Названия столбцов ‑ поле Отдел, в область Значения ‑ поле Фамилии.

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

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

Освоить следующие операции:

• отображение или скрытие элементов сводной таблицы;

• развертывание или свертывание уровней детализации;

• отображение или скрытие подробных сведений о значениях;

• изменение функции подведения итогов;

• изменение формы сводной таблицы: компактной, табличной или структурной;

• изменение макетов столбцов, строк и итогов;

• изменение формата.