Структурирование таблиц

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

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

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

В результате создается структура таблицы со следующими элементами слева или сверху на служебном поле:

• линии уровней структуры, показывающие соответствующие группы иерархического уровня;

• кнопка «плюс» - для раскрытия групп структурированной таблицы;

• кнопка «минус» - для скрытия групп структурированной таблицы;

• кнопки «номера уровней 1, 2, 3 и т.д.» - для открытия или скрытия соответствующего уровня.

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

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

Таблица 1.5 – Функции при подведении автоматических итогов по группам

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

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

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

Чтобы создать сводную таблицу, необходимо перейти на вкладку Вставка и с панели Таблицы выполнить команду Вставить сводную таблицу. Будет открыто диалоговое окно мастера создания сводных таблиц и диаграмм (рис. 1.8).

Рисунок 1.8 – Мастер построения сводных таблиц

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

[имя_книги]имя_листа!диапазон ячеек

Затем происходит построение макета сводной таблицы. Структура сводной таблицы состоит из следующих областей, определяемых в макете:

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

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

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

данные – поля, по которым подводят итоги согласно выбранной функции; область определять обязательно.

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

Таблица 1.6 – Виды дополнительных функций над полем в области данных

Функция	Результат
Отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент
Доля	Значения ячеек области данных отображаются в процентах к заданному элементу, указанному в списках поле и элемент
Приведенное отличие	Значения ячеек области данных отображаются в виде разности с заданным элементом, указанным в списках поле и элемент, нормированной к значению этого элемента
С нарастающим итогом в поле	Значения ячеек области данных отображаются в виде нарастающего итога для последовательных элементов. Следует выбрать поле, элементы которого будут отображаться в нарастающем итоге
Доля от суммы по строке	Значения ячеек области данных отображаются в процентах от итога строки
Доля от суммы по столбцу	Значения ячеек области данных отображаются в процентах от итога столбца
Доля от общей суммы	Значения ячеек области данных отображаются в процентах от общего итога сводной таблицы
Индекс	При определении значений ячеек области данных используется следующий алгоритм: ((Значение в ячейке) * (Общий итог)) / ((Итог строки) * (Итог столбца))