Консолидация по расположению

1. Организуйте консолидируемые данные на каждом отдельном листе.

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

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

§ Убедитесь, что макеты всех диапазонов совпадают.

§ Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена нажмите кнопку Имя диапазона и введите имя диапазона в поле Имя.

2. Щелкните на основном листе левый верхний угол области, в которой требуется разместить консолидированные данные. Убедитесь, что справа и снизу этой ячейки достаточно свободных ячеек для данных консолидации. Команда Консолидация заполнит столько ячеек, сколько потребуется.

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

4. Выберите в раскрывающемся списке Функция итоговую функцию (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.), которую требуется использовать для консолидации данных.

5. Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем — кнопку ОК, чтобы закрыть диалоговое окно Обзор.

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

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

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

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

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

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

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

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

§ Организуйте консолидируемые данные на каждом отдельном листе.

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

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

§ Убедитесь, что подписи столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв. Например, заголовки «Ср. за год» и «Средний за год» различаются и не будут объединены в таблице консолидации.

§ Присвойте каждому диапазону имя: выделите диапазон ячеек и на вкладке Формулы в группе Определенные имена щелкните стрелку, расположенную рядом с полем Присвоить имя и введите имя диапазона в поле Имя.

§ Щелкните левый верхний угол области основного листа, в которой требуется разместить консолидированные данные.

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

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

§ Выберите в раскрывающемся списке Функция итоговую функцию (Итоговая функция. Тип вычисления при объединении данных в отчете сводной таблицы, в таблице консолидации или при вычислении итогов в списках или базах данных. Примерами итоговых функций могут служить функции СУММ, СЧЕТ и СРЕДНЕЕ.), которую требуется использовать для консолидации данных.

§ Если лист находится в другой книге, нажмите кнопку Обзор, чтобы найти файл, а затем — кнопку ОК, чтобы закрыть диалоговое окно Обзор.

§ Путь к файлу будет введен в поле Ссылка, он будет завершаться восклицательным знаком.

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

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

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

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

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

§ Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: либо подписи верхней строки, либо Значения левого столбца, либо оба флажка одновременно.

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

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

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

Содержание лабораторной работы

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