Лабораторная работа № 4
Агрегирование данных в Microsoft Excel. Структурирование таблиц. Консолидация данных.
Цель работы: Изучение основных приемов создания структурированных таблиц и консолидации данных в электронной таблице Excel.
1. ОСНОВНЫЕ ПОЛОЖЕНИЯ.
Агрегирование данных списков состоит в формирование промежуточных итогов, создании сводных и консолидированных таблиц. Агрегирование информации выполняется для списков Microsoft Excel, в записях которых имеются поля с повторяющимися значениями
Структурирование таблиц
Структурирование таблиц, содержащих значительное число строк и столбцов, состоит в выделении уровней иерархии для представления информации. Каждому уровню иерархии на листе рабочей книги Microsoft Excel соответствует своя специальная кнопка, с помощью которой можно скрыть или отобразить содержимое уровня. Максимальный уровень вложенности в структурной таблице - 8.
Для создания структуры предварительно выделяется группа строк или столбцов, выполняется команда меню Данные→Группа и структуры→Группировать. Если в таблице находятся формулы, использующие в качестве операндов ссылки на ячейки текущей строки или столбца, то команда меню Данные→Группа и структуры→Создать структуру создает структурную таблицу автоматически.
Команда меню Данные→Группа и структуры →Настройки позволяет выполнить настройки структуры:
- выбрать место вставки кнопок структуры; использовать автоматически стили структуры; применить другие стили.
Для удаления структуры следует выполнить повторное выделение строк/столбцов и выбрать команду Данные→Группа и структуры→Разгруппировать. Для автоматически созданных структурных таблиц обратное преобразование выполняется командой Данные →Группа и структуры →Удалить структуру.
Консолидация данных
Консолидация – особый способ вычисления итогов для диапазона ячеек. Консолидируемые данные могут находиться на одном и том же или нескольких листах одной рабочей книги, а так же различных рабочих книг. При консолидации доступны все функции статистических итогов (сумма, максимум, минимум, среднее и т. д.). результат консолидации записывается на лист рабочей книги. На одном листе может быть представлено несколько результатов консолидации для одних и тех же диапазонов ячеек с различными функциями итогов. Если диапазоны ячеек различаются, результата консолидации должен быть представлен на разных листах рабочей книги.
Различают два вида консолидации:
- консолидация по расположению ячеек – состав и порядок следования консолидируемых данных во всех диапазонах постоянны; консолидация по категориям – на основании одинаковых подписей строк и столбцов для диапазонов ячеек.
Консолидация может осуществляться с поддержкой связей с источниками. В структурной таблицу при изменении исходных данных автоматически вычисляются новые итоги.
При выполнении команды меню Данные→Консолидация выводится диалоговое окно Консолидация, в котором выбирается функция итога, указываются диапазоны консолидируемых ячеек. Для добавления очередного диапазона ячеек нужно нажать «красную» кнопку. При этом возможен переход на другие листы книги, а с помощью команды Окно – в другие открытые рабочие книги для выделения ячеек.
2. Практическая часть
Задание 1. Подготовить исходную таблицу (рисунок 1), на ее основе создать структурную таблицу.
Рисунок 1. Отчет о движении денежных средств.
Последовательность действий:
Создать новый лист. Переименовать его, присвоив имя Таблица1. Начиная с ячейки А1, разместить таблицу (рисунок 1). Ввести в ячейки таблицы расчетные формулы:Строка 020=030+040+050+060+070+0890+090+110
Строка 120=130+140+150+160+170+180+190+200+210+220+230+250
Строка 260=010+020-120
Ввести в ячейки формулы суммирования:С4: =СУММ(С6:C13) D4: =СУММ(D6:D13)
E4: =СУММ(Е6:Е13) F4: =СУММ(F6:F13)
С15: =СУММ(С17:С28) D15: =СУММ(D17:D28)
Е15: =СУММ(Е17:Е28) F15 =СУММ(F17:F28)
С30: =С3+С4-С15
Для контроля правильности вычислений создаются вспомогательные столбцы: Сумма1 и Контроль. Формулы проверки данных:G4: =СУММ(D4:F4) H4: =ЕСЛИ(G4=C4;""; "Ошибка")
G15: =СУММ(D15:F15) H15: = ЕСЛИ(G15=C15;""; "Ошибка")
Выполнить команду меню Данные→Группа и структуры →Создание структуры. Сохранить изменения в файле.Задание 2. Подготовить консолидированный отчет о прибылях и убытках за ряд периодов.
Последовательность действий:
1. На новом листе рабочей книги, начиная с ячейки А1 находится таблица (рисунок 2), которая содержит формулы:
С6: =С2-С3-С4-С5 D6: =D2-D3-D4-D5
C12: =C6+C7-C8+C9+C18-C11 D12: =D6+D7-D8+D9+D10-D11
C15: =C12+C13-C14 D15: =D12+D13-D14
C16: =0,23*C15 D16: =0,23*D15
C18: =C15-C16-C17 D18: =D15-D16-D17
2. Выбрать лист Таблица1.
Рисунок 2. Отчет о прибылях и убытках.
3. Создать копию листа Таблица1 в новой книге с помощью команды меню Правка→Переместить/Скопировать лист.
4. Выполнить команду меню Окно для перехода в новую книгу.
5. Заполнить данные в копии таблицы.
6. Установить курсор на новый лист в новой книге.
7. Выполнить команду меню Данные→Консолидация:
- выбрать функцию итога – среднее; указать в поле Ссылка диапазонов ячеек таблиц Таблица1 для каждой рабочей книги; использовать в качестве имен подписи верхней строки и значения левого столбца; создавать связи с исходными данными; нажать ОК.
8. Сохранить изменения в файле.
3. Содержание отчета:
1. Название работы.
2. Цель работы.
3. Законспектировать теоретическую часть (Основные положения) лабораторной работы.
