ifempty

    Урок 9. Сводные таблицы

    Оглавление

    Введение

    Пример создания сводной таблицы

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

    Работа с полями. Добавление вычисляемых полей

    Файлы для скачивания

     

    Введение 

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

    Кнопка создания сводной таблицы находится на вкладке Вставка, в левом верхнем углу окна (см. рисунок 1).

     сводные таблицы рис1

    Рисунок 1. Расположение кнопки создания сводной таблицы на вкладке Вставка

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

     

    Пример создания сводной таблицы

    Скачайте файл Приложение 1.xlsx по ссылке ниже.

    Откройте лист Список сотрудников. Если у вас стоит защищенный просмотр, то нажмите на кнопку Разрешить редактирование в правом верхнем угла окна, как показано на рисунке 2. 

     сводные таблицы рис2

    Рисунок 2. Кнопка Разрешить редактирование

    Задача:

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

    Решение:

    Выделите таблицу на листе Список сотрудников целиком вместе с заголовками, далее перейдите на вкладку Вставка и нажмите на кнопку Сводная таблица.

     сводные таблицы рис3

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

    У вас откроется окно Создание сводной таблицы, в котором нужные поля уже заполнены. Нажмите на кнопку ОК.

     сводные таблицы рис4

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

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

     сводные таблицы рис5

    Рисунок 5. Лист сводной таблицы

    Следующий рисунок 6 показывает назначение областей:

     сводные таблицы рис6

    Рисунок 6. Назначение областей на листе сводной таблицы

    Так как нужно отобразить суммы окладов по каждому отделу, то в правой области отмечаем поля Отдел и Оклад, при этом в левой части у вас должна отобразиться сводная таблица со списком отделов и суммами окладов.

     сводные таблицы рис7

    Рисунок 7. Отчет сводной таблицы - суммы окладов по отделам 

     

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

    Продолжаем работать в файле  Приложение 1.xlsx. 

    Задача:

    Отобразить ФИО сотрудников и их возраст. При этом фамилии, имена и отчества сотрудников должны быть в разных столбцах.

    Решение:

    Справа в области полей нужно убрать флаги в полях Отдел и Оклад, и установить в полях Фамилия, Имя, Отчество и Возраст, как показано на рисунке 8.

     сводные таблицы рис8

    Рисунок 8. Отчет сводной таблицы - ФИО сотрудников и их возраст

    Обратите внимание, что таблица представлена в иерархическом виде -  поля Фамилия, Имя и Отчество расположены в одном столбце. Нам нужно чтобы они были в отдельных столбцах. Для этого переходим на вкладку Конструктор -> Макет отчета, где выбираем Показать в табличной форме, как показано на рисунке 9.

     сводные таблицы рис9

    Рисунок 9. Настройка табличного вида сводной таблицы

    Таблица примет следующий вид:

     сводные таблицы рис10

    Рисунок 10. Отчет сводной таблицы - ФИО сотрудников и их возраст

    Чтобы не было промежуточных итогов, нужно перейти на вкладку Констурктор -> Промежуточные итоги, где выбрать Не показывать промежуточные итоги.

     сводные таблицы рис11

    Рисунок 11. Отчет сводной таблицы - ФИО сотрудников и их возраст

    В результате таблица примет вид, как на рисунке 12:

     сводные таблицы рис12

    Рисунок 12. Отчет сводной таблицы - ФИО сотрудников и их возраст

     

    Работа с полями. Добавление вычисляемых полей

    Продолжаем работать в той же таблице.

    Задача:

    Показать оклады сотрудников, вычислить и показать их обязательные пенсионные взносы (ОПВ), которые рассчитываются по формуле:

    ОПВ=Оклад*10%

    Решение:

    Справа в полях сводной таблицы убираем флаг с поля Возраст и активируем флаг на поле Оклад, как на рисунке 13.

     сводные таблицы рис13

    Рисунок 13. Отчет сводной таблицы - ФИО сотрудников и их оклады

    Далее переходим на владку Анализ -> Поля, элементы и наборы -> Вычисляемое поле

     сводные таблицы рис14

    Рисунок 14. Вкладка Анализ -> Поля, элементы и наборы

    У вас откроется окно Вставка вычисляемого поля, где:

    1. В поле Имя нужно указать - ОПВ;
    2. В поле Формула нужно задать формулу расчет ОПВ: =Оклад*10%, при этом поле Оклад выбирается из списка полей в разделе Поля либо двойным щелчком, либо кнопкой Добавить поле. 

     сводные таблицы рис15

    Рисунок 15. Вставка вычисляемого поля

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

     сводные таблицы рис16

    Рисунок 16. Отчет сводной таблицы - ФИО сотрудников, суммы окладов и ОПВ 

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.