Группа 951 20.03.2020 Практическая работа №5 Тема: «Оформление итогов и создание сводных таблиц»

Инструкционно – технологическая карта

ОП.03 Информационные технологии

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

Тема: «Оформление итогов и создание сводных таблиц»

Цель работы (для студентов): научиться использовать консолидацию данных в MS Excel

Теоретическая справка

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

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

При консолидации данных объединяются значения из нескольких диапазонов данных. Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в лист корпоративных расходов.

Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.

Методы консолидации данных

В табличном редакторе Microsoft Excel предусмотрено несколько способов консолидации:

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

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

3. Если данные, вводимые с помощью нескольких листов-форм, необходимо выводить на отдельные листы, используйте мастер шаблонов с функцией автоматического сбора данных.

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

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

Задание

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

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

Для решения поставленной задачи в табличном процессоре Excel выполните следующие действия:

Сначала введите Ваши исходные данные таблицу расчета заработной платы: размер заработной платы, величину подоходного налога и сумму к выплате. Вставьте эти данные для нашего примера в диапазон ячеек B3:B6 на листах «Январь» - «Июнь», как показано на рисунке.

Ввод исходных данных в таблицу расчета заработной платы

Консолидация данных по расположению

Консолидацию по расположению следует использовать в случае, если данные всех исходных областей находятся в одном месте и размещены в одинаковом порядке; например, если имеются данные из нескольких листов, созданных на основе одного шаблона, как показано на рисунке 1.

Создайте в книге «Заработная плата 2002 год» новый лист с именем «Консолидация» Укажите верхнюю левую ячейку конечной области консолидируемых данных, т.е. левый верхний угол области в которую будут вставляться ячейки с результатами.

В меню Данные выберите команду Консолидация, как показано на рисунке.

Выбор пункта Консолидация в меню Данные

Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных, как показано на рисунке.

Функции обработки при консолидации данных

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

Таблица 1 Перечень доступных функций для консолидации данных

Введите в поле Ссылка исходную область консолидируемых данных и нажмите кнопку Добавить, как показано на рисунке.

Определение функции для консолидации данных по диапазону

Данные действия необходимо выполнить для всех консолидируемых исходных областей, в нашем примере для листов начиная с «Январь» по «Июнь».

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

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

Создание заголовка для консолидируемых данных в области назначения

Результат консолидации данных по расположению приведен на рисунках. Результат можно представить и отправить на печать в развернутом и кратком форматах.

Сводная таблица расчета заработной платы сотрудников за полугодие 2002 года

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

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

Пример консолидации данных с использованием трехмерных ссылок

Для реализации консолидации данных с использованием трехмерных ссылок в табличном процессоре Excel выполните следующие действия:

1. На листе консолидации скопируйте или задайте надписи для данных консолидации.

2. Укажите ячейку, в которую следует поместить данные консолидации.

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

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

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

Правила ввода трехмерных ссылок на исходные области в формулах консолидации данных

На том же листе   Если исходные области и область назначения находятся на одном листе, используйте имена или ссылки на диапазоны.

На разных листах   Если исходные области и область назначения находятся на разных листах, используйте имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Зарплата», находящийся в книге на листе «Январь», введите:

Январь!Зарплата.

В разных книгах   Если исходные области и область назначения находятся в разных книгах, используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Зарплата» с листа «Январь» книги «Заработная плата 2002 год», введите:

На разных устройствах   Если исходные области и область назначения находятся в разных книгах разных каталогов диска, используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Зарплата» с листа «Январь» книги «Заработная плата 2002 год», которая находится в папке «Отчетность», введите:

'[С:\Отчетность\Заработная плата 2002 год.xls] Январь'! Зарплата

Примечание Так же необходимо помнить, что для того чтобы задать описание источника данных, не нажимая клавиш клавиатуры, укажите поле Ссылка, а затем выделите исходную область. Чтобы задать исходную область в другой книге, нажмите кнопку Обзор. Чтобы убрать диалоговое окно Консолидация на время выбора исходной области, нажмите кнопку Свернуть диалоговое окно.

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

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

Для реализации консолидации данных по категории в табличном процессоре Excel выполните следующие действия:

1. Укажите верхнюю левую ячейку конечной области консолидируемых данных.

2. В меню Данные выберите команду Консолидация.

3. Выберите из раскрывающегося списка Функция функцию, которую следует использовать для обработки данных.

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

5. Нажмите кнопку Добавить.

6. Повторите шаги 4 и 5 для всех консолидируемых исходных областей.

7. В наборе флажков Использовать в качестве имен установите флажки, соответствующие расположению в исходной области заголовков: в верхней строке, в левом столбце или в верхней строке и в левом столбце одновременно.

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

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

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

Можно создать отчет сводной таблицы из нескольких диапазонов консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий.

Если данные вводятся в несколько листов-форм, основанных на одном, и при этом требуется объединить данные из форм на отдельном листе, следует воспользоваться мастером шаблонов с функцией автоматического сбора данных.

Для реализации консолидации данных в отчете сводной таблицы в табличном процессоре Excel выполните следующие действия:

1. Откройте книгу, в которой требуется создать отчет сводной диаграммы.

Если отчет создается на основе списка Microsoft Excel или базы данных, щелкните ячейку в этом списке или базе данных.

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

Выбор пункта Сводная таблица в меню Данные

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

Выбор создаваемого отчета

1. Следуйте инструкциям на шаге 2 мастера.

2. Выполните одно из следующих действий:

Если на шаге 3 была нажата кнопка Макет, выполните формирование макета отчета, нажмите кнопку OK в диалоговом окне Мастер сводных таблиц и диаграмм – Макет, а затем кнопку Готово для создания отчета.

Если кнопка Макет на шаге 3 не была нажата, нажмите кнопку Готово, а затем сформируйте макет отчета на листе.