17. Консолидация.

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

18. Сводная таблица.

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

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

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

1 Шаг. Определение типа источника данных.

2 ШАГ. Определение адреса источника данных. По умолчанию, если это возможно, Excel сам определяет исходный блок и обводит его бегущей пунктирной линией. Для этого курсор должен находиться в нужной области. В случае, если исходные данные находятся в других файлах, следует открыть с помощью кнопки Обзор.

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

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

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

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

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

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

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

Прежде всего здесь можно задать собственное имя сводной таблицы (по умолчанию она получает имя Сводная таблица1).

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

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

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

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

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

Флаг сохранять форматирования устанавливает режим сохранения введенного пользователем индивидуального форматирования элементов сводной таблицы при ее обновлении. Такое форматирование клеток будет иметь приоритет перед автоформатированием (если оно есть). Эта установка имеет значение только в режиме Разрешить выделение из контекстного меню Выделить.

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

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

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

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

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