Консолидация данных по категории

Вы также можете консолидировать данные по категориям, используя в качестве основы для объединения заголовки столбцов или строк. Такой способ консолидации более гибкий. Например, если данные по Товарам на разных листах находятся в разных строках таблицы, то при консолидации по категориям такие данные будут объединены (Рисунок 7.24).

Рисунок 7.24. Разная структура диапазонов, которые используются для консолидации

1. На каждом листе, который требуется консолидировать, подготовьте данные, как описано ниже.

a. Необходимо, чтобы все диапазоны данных были представлены в формате списка.

b. Разместите каждый диапазон на отдельном листе. Не помещайте диапазоны на лист, на котором должна выполняться консолидация.

2. На основном листе выделите ячейку, начиная с которой будут располагаться консолидированные данные.

! Убедитесь, что справа и снизу этой ячейки достаточно свободных ячеек, чтобы не перезаписать существующие данные консолидированными данными.

3. На вкладке Данные в группе Работа с данными выберите команду Консолидация.

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

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

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

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

6. Если данные для консолидации находятся в другой книге, выполните одно из следующих действий:

Ø Если книга открыта – в поле Ссылка нажмите кнопку закрытия диалогового окна, перейдите в нужную книгу и выделите консолидируемый диапазон.

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

7. Если лист находится в той же книге, выполните указанные ниже действия.

c. Щелкните кнопку справа от поля Ссылка, чтобы открыть диалоговое окно Консолидация — Ссылка.

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

8. В диалоговом окне Консолидация нажмите кнопку Добавить и повторите действия 6 и 7, чтобы добавить все нужные диапазоны.

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

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

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

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

Ø Если верхняя строка каждого из выделенных диапазонов содержит подписи столбцов – установите флажок подписи верхней строки.

Ø Если левый столбец каждого из выделенных диапазонов содержит подписи строк - установите флажок значения левого столбца.

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

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

11. Нажмите кнопку ОК.

Рисунок 7.25. Результат консолидации диапазонов по категории