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

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

Пример структурированных данных в Microsoft Excel показан на рис. 14.13.

Рис. 14.13 Пример структурированной таблицы

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

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

Для автоматического создания структуры следует выделить диапазон ячеек (в противном случае будет структурирован весь лист) и воспользоваться командой Создание структуры из выпадающего меню кнопки « Группировать» в группе Структура вкладки Данные.

После создания структуры слева от номеров строки и выше названий столбцов отображаются области структуры. Когда сгруппированные столбцы отображаются на экране, они помечены черной точкой (), объединены черной линией-скобой (), на одном из концов которой, над итоговым столбцом, имеется кнопка . Нажатие на эту кнопку сворачивает все столбцы группы, оставляя лишь итоговый столбец, а сама кнопка превращается в кнопку , которая, в свою очередь, служит для разворачивания столбцов. Сгруппированные строки ведут себя аналогично, только жирная линия-скоба будет вертикальной. В левой верхней части области структуры также находятся кнопки с цифрами (), нажатие которых заставляет Excel свернуть строки или столбцы, уровень которых ниже, чем цифра на кнопке. В частности, кнопка с цифрой «1» сворачивает все столбцы или строки, которые можно свернуть, а кнопка с самой большой цифрой позволяет отобразить все столбцы или строки.

Также быстро скрыть или отобразить строки и столбцы в группе, в которой находится курсор, можно кнопками « Скрыть детали» и « Отобразить детали» в группе Структура вкладки Данные.

Создание структуры вручную заключается в последовательном выделении строк или столбцов, образующих группы, и нажатии на кнопку « Группировать» в группе Структура вкладки Данные. При этом по умолчанию строкой итога считается строка ниже последней строки выделенного диапазона, а столбцом итога – столбец правее последнего столбца выделенного диапазона. Если выделить диапазон ячеек (а не строк или столбцов), то будет отображено окно с переключателем, указывающим, что именно группировать: строки или столбцы.

Кнопка « Разгруппировать» позволяет удалить группирование выделенных строк или столбцов. Для быстрого разгруппирования всех строк и столбцов таблицы (и, как следствие, удаления ее структуры) служит команда Удалить структуру из выпадающего меню кнопки « Разгруппировать».

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

При создании структуры (автоматически или вручную) Microsoft Excel может автоматически применить к итоговым строкам и столбцам стили «Уровеньстрок_1», «Уровеньстрок_2», «Уровеньстолб_1» и т.д. За эту функцию отвечает флажок «Автоматические стили» в окне рис. 14.14. Кнопка «Создать» запускает процесс автоматического создания структуры (аналог команды Создать структуру), кнопка «Применить стили» позволяет применить отмеченные выше стили после ручного группирования.

Подведение итогов в таблицах

Функция вставки промежуточных итогов удобна тем, что она одновременно и добавляет в таблицу с данными строки с формулами для расчета промежуточных итогов (к примеру, сумм по каждому месяцу и году), и структурирует список, разбивая его на группы. При этом вставленные строки с формулами автоматически становятся строками итогов групп.

Для добавления итогов к данным необходимо, чтобы они были оформлены как таблица или база данных (в противном случае команда отобразит окно с ошибкой). Подведение итогов по группам записей осуществляется через диалоговое окно «Промежуточные итоги» (рис. 14.15), которое вызывается кнопкой « Промежуточный итог» в группе Структура вкладки Данные.

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

Флажок «Конец страницы между группами» заставляет Microsoft Excel принудительно вставлять перед началом каждой следующей группы разрыв страницы, что удобно, если таблица большая и в дальнейшем предполагается ее распечатывать.

При необходимости можно создавать так называемые вложенные промежуточные итоги (в результате будет произведена группировка ранее созданных групп друг с другом в группы более высокого уровня). Для этого кнопку « Промежуточный итог» следует использовать несколько раз, выбирая каждый раз новое поле в списке «При каждом изменении в». Флажок «Заменить текущие итоги» при этом должен быть снят, иначе создаваемые итоги и группировка заменят предыдущие, а не будут добавлены к ним в виде нового уровня.

Если промежуточные итоги были вставлены неверно или больше не требуются, можно, опять же, открыть окно и нажать кнопку «Убрать все». Вместе с промежуточными итогами будет уничтожена и структура таблицы, независимо от того, была ли она создана при добавлении итогов или вручную.

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

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

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

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

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

В Microsoft Excel консолидацию можно осуществлять разными способами: при помощи формул со ссылками (вручную – используется возможность указывать в ссылках названия книг Excel и имена листов) и автоматически, через диалоговое окно.

Консолидация при помощи формул заключается в том, что на лист консолидации (или на свободное место существующего листа) копируются заголовки строк и столбцов из диапазонов консолидации, затем в ячейки вводятся формулы. В формулах используются функции (например, СУММ или СРЗНАЧ), аргументами которых являются ссылки на соответствующие ячейки в диапазонах консолидации. Способ достаточно трудоемок, однако позволяет консолидировать данные, структура таблиц которых сильно различается.

Автоматическая консолидация осуществляется через диалоговое окно (рис. 14.16), для вызова которого служит кнопка « Консолидация» в группе Работа с данными вкладки Данные ленты инструментов. Предварительно должна быть активирована ячейка, в которую будет вставлена итоговая таблица.

В поле «Ссылка» диалогового окна вводится ссылка на диапазон консолидации. Кнопка «Обзор» позволяет вставить в поле «Ссылка» полное имя любого другого файла Microsoft Excel, если диапазон консолидации находится в нем. При этом файл не обязательно должен быть открыт (хотя лучше все-таки открыть его, иначе не будет возможности выделить диапазон консолидации мышью, а придется набирать соответствующую ссылку вручную). Далее кнопкой «Добавить» следует добавить данную ссылку в список диапазонов. Другие диапазоны добавляются в список диапазонов аналогичным образом. В дальнейшем, выделив любой диапазон в списке, его можно отредактировать в поле «Ссылка» либо удалить при помощи кнопки «Удалить».

Рис. 14.16 Диалоговое окно консолидации данных

Еще более удобным добавление диапазонов может сделать предварительное присваивание каждому из них уникального имени (см. Лабораторную работу №11). В этом случае в поле «Ссылка» можно непосредственно набирать имена диапазонов.

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

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

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

Консолидация по категориям является наиболее удобным способом. Диапазоны консолидации должны быть представлены в виде оформленных таблиц или баз данных с заголовками строк и столбцов, либо столбцам и строкам должны быть присвоены имена (если заголовков нет, а есть имена, соответствующие флажки в окне необходимо снять). При добавлении диапазонов консолидации в список ячейки с заголовками также должны попасть в диапазоны. Заголовки столбцов и строк в разных диапазонах консолидации должны быть идентичными (регистр, знаки препинания, пробелы – все должно совпадать). При консолидации ячейки из диапазонов отбираются на основании названий записей и полей, поэтому, если форма или размер диапазонов различны, Microsoft Excel корректно учтет эти различия.

Если перед осуществлением консолидации по категориям на листе консолидации создать строку с именами полей или столбец с именами записей (заготовку итогового диапазона), то при консолидации будут отработаны только те ячейки, имена полей или записей которых были введены. Это позволяет выбирать из исходных диапазонов только необходимые данные. Например, если в диапазонах консолидации содержатся наименования товаров, цена, количество проданных товаров и сумма выручки от продажи, очевидно, что нужно консолидировать, т.е. суммировать, объемы продаж и выручку. Следовательно, имя поля с ценой в этом случае создавать не надо.

Пример такой консолидации показан на рис. 14.17. Сначала на одном листе были созданы диапазоны консолидации (рис. 14.17, а), затем была создана заготовка итогового диапазона (рис. 14.17, б), которая была выделена перед открытием диалогового окна рис. 14.16. Результат консолидации показан на рис. 14.17, в.

Флажок «Создавать связи с исходными данными» в окне рис. 14.16 вставляет данные на лист консолидации в виде ссылок. При изменении исходных данных в диапазонах лист консолидации будет автоматически обновляться (если флажок снят, таблица консолидации будет содержать не формулы, а значения, и будет статичной). В этом режиме таблица консолидации создается в структурированном виде на основании заголовков строк (для каждого уникального заголовка строки создается группа, в которую вносится строка с таким заголовком из каждого отдельного диапазона консолидации). Для каждой группы вычисляется функция, заданная в диалоговом окне консолидации. Такая консолидация на одном и том же листе с исходными диапазонами невозможна – Microsoft Excel отобразит ошибку.

Рис. 14.17 Пример консолидации данных:
а) исходные диапазоны консолидации; б) заготовка итогового диапазона; в) сформированный итоговый диапазон