- •4.2. Сортировка данных в таблице
- •4.3. Фильтрация данных
- •4.4. Автоматическое вычисление общих и промежуточных итогов
- •4.5. Анализ и обобщение данных с помощью сводных таблиц и сводных диаграмм
- •4.6. Консолидация данных
- •Задания
- •Задание 4.1. Сортировка, фильтрация, подведение итогов, создание сводной таблицы
- •Задание 4.2. Консолидация данных
- •Контрольные вопросы
- •Библиографический список
4.6. Консолидация данных
Консолидация — это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:
консолидация данных с помощью формул со ссылками;
консолидация данных по расположению;
консолидация данных по категориям.
Первый способ позволяет объединить данные консолидируемых областей формулами. Для этого надо на итоговом листе создать (или скопировать) надписи для данных консолидации и в соответствующие ячейки ввести формулы, содержащие ссылки на консолидируемые исходные области листов, которые будут участвовать в консолидации. Например, =СУММ(Лист1!В3;Лист2!С3;Лист3!С5).
Консолидация данных по расположению используется, если консолидируемые данные находятся в одном и том же месте разных листов и размещены в одном и том же порядке. Технология консолидации такова: указать левую верхнюю ячейку области размещения консолидируемых данных, вызвать команду: Данные группа Работа с данными Консолидация, в диалоговом окне Консолидация выбрать в списке Функция итоговую функцию для обработки данных, в поле Ссылка ввести исходную область для консолидации данных (диапазон ячеек), нажать кнопку Добавить и повторить эти действия для всех диапазонов, данные из которых участвуют в консолидации.
Консолидация данных по категориям используется, если данные исходных областей не упорядочены, но имеют одни и те же заголовки. Технология этой консолидации совпадает с технологией консолидации данных по расположению, но в диалоговом окне Консолидация в группе Использовать в качестве имен следует установить параметры подписи верхней строки и/или значения левого столбца для указания расположения заголовков в исходных областях.
Установка параметра Создавать связи с исходными данными в диалогом окне Консолидация означает, что между исходными данными и результатами консолидации устанавливается динамическая связь, обеспечивающая автоматическое обновление данных. Автоматическое обновление данных происходит, если исходные данные находятся в пределах одной книги. Если исходные данные расположены в других рабочих книгах, то их обновление будет выполняться командой: Данные группа Подключение Изменить связи. После установки связей нельзя корректировать ссылки на области-источники (добавлять или удалять области-источники). Связи нельзя использовать, если исходная и итоговая области находятся на одном листе.
П ример 4.6. На рабочих листах с именами Январь, Февраль, Март приведены фамилии торговых агентов и количество сделок, которые они совершили в течение месяца (рис. 4.10). Подготовить сводку за первый квартал.
Лист Январь: Лист Февраль:
|
А |
В |
С |
|
|
А |
В |
С |
1 |
Фамилия И.О. |
Сделки |
Объем |
|
1 |
Фамилия И.О. |
Объем |
Сделки |
2 |
Иванов И.И. |
6 |
250 |
|
2 |
Сидоров И.Н. |
200 |
5 |
3 |
Антонов А.В. |
12 |
430 |
|
3 |
Иванов И.И. |
220 |
8 |
4 |
Медведев К.Л. |
7 |
180 |
|
4 |
|
|
|
Лист Март:
|
А |
В |
С |
1 |
Фамилия И.О. |
Сделки |
Объем |
2 |
Иванов И.И. |
12 |
200 |
3 |
Сидоров И.Н. |
10 |
300 |
4 |
Антонов А.В. |
8 |
150 |
5 |
Медведев К.Л. |
6 |
220 |
Р ис. 4.10
В приведенном примере области-источники содержат однотипные данные, но в различных областях эти данные организованы по-разному, поэтому надо применить консолидацию по категориям.
Вставим новый лист, присвоим ему имя Квартал_1 и выделим на нем ячейку, которая будет служить левой верхней ячейкой для диапазона с результатами консолидации, например А2. Выберем команду: Данные группа Работа с данными Консолидация, в появившемся диалоговом окне Консолидация в поле Функция укажем Сумма, так как выбранные данные должны суммироваться, а в поле Ссылка укажем поочередно диапазоны для консолидации. Это можно выполнить следующим образом: щелкнем мышью в поле Ссылка, щелкнем по ярлычку листа Январь (в поле ввода появится Январь!), выделим диапазон А1:С4 (в поле ввода — Январь!$A$1:$C$4), щелкнем кнопку Добавить — адрес диапазона окажется в поле Список диапазонов. Аналогично добавим диапазоны Февраль!$A$1:$C$3 и Март!$A$1:$C$5. Список диапазонов консолидации сформирован.
В области Использовать в качестве имен установим флажки подписи верхней строки и значения левого столбца, потому что информация в таблице будет идентифицироваться по названиям строк и столбцов. Если бы таблицы по месяцам имели одинаковую структуру, но разные названия столбцов, например, на одном листе столбец назывался бы Сделки, а на другом — Количество сделок, но их расположение в таблице было бы одинаковым, тогда следовало снять флажок подписи верхней строки. Флажок Создавать связи с исходными данными устанавливать пока не будем. После щелчка по кнопке ОК на рабочем листе Квартал_1 появится консолидированная таблица (рис. 4.11).
|
А |
В |
С |
1 |
|
|
|
2 |
|
Сделки |
Объем |
3 |
Иванов И.И. |
26 |
670 |
4 |
Сидоров И.Н. |
15 |
500 |
5 |
Антонов А.В. |
20 |
580 |
6 |
Медведев К.Л. |
13 |
400 |
Рис. 4.11
Заголовок 1-го столбца Фамилия И.О. отсутствует, его надо ввести в ячейку А2, а в 1-ю строку можно ввести заголовок к консолидированной таблице.
При внесении изменений в один из диапазонов консолидации, например, в марте увеличить количество сделок, записанных за Ивановым, таблица на листе Квартал_1 не изменится, так как флажок Создавать связи с исходными данными не был установлен. При установке этого флажка в диалоговом окне Консолидация таблица изменится: столбец В будет пустым, столбцы Сделки и Объем переместятся в столбцы С и D, а слева появятся символы структуры. При раскрытии 2-го уровня структуры в столбце В появится имя текущей рабочей книги (можно консолидировать данные и из разных рабочих книг), а в столбцах С и D будет указано из каких исходных данных сложились итоговые данные. Если теперь изменить количество сделок на листе Март, то итоговые данные будут обновлены автоматически.