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

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

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

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

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

Процедура консолидации представляет собой диалог, выполняемый в окне "Консолидация". Оно вызывается командой Данные - Консолидация.

Пример 1.1.. На рабочих листах с именами "январь", "февраль", "март" находится информация о продажах товаров по месяцам. Подсчитать общие показатели за первый квартал.

Решение. Чтобы освоить некоторые полезные приемы, нач­нем решать задачу "с нуля".

1) Создать новую рабочую книгу. Переименовать листы: задать им названия "Январь", "Февраль", "Март",

"1 квартал".

2) Ввести заголовки одновременно в несколько листов.

Для этого вы­делить все листы с названиями месяцев:

· перейти на лист "Январь", нажать клавишу Shift и, не отпуская ее, щёлк­нуть по ярлычку листа "Март". Будут выделены все листы рабо­чей книги, при этом активным листом останется "Январь".

GПримечание. Если нужно выделить только некоторые листы, следует удерживать нажатой клавишу Ctrl.

· Ввести в ячейку А1 "Наименование товара.", в ячейку В1 - "Количество продаж, шт.", в С1 - "Объем продаж, т.руб".

· Щёлкнуть по ярлычку лис­та "Февраль", выделение с нескольких листов будет снято. Убедиться, что в ранее выделенные листы внесен один и тот же текст в ячейки А1, В1, С1.

GДля иллюстративных целей поме­нять на листе "Февраль" содержимое ячеек: в В1 "Объем продаж, т.руб", а в С1 " Количество продаж, шт. ".

3) Ввод информации в листы.

Ввести в листы с названиями месяцев информацию в соответствии с таблицами (рис. 1)

G Примечания.

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

2. Для демонстрации произвольного выбора данных названия товаров в листах идут в полном беспорядке, заголовки столбцов тоже перепутаны (но названия товаров всегда в первом столбце!).

Январь:

Февраль:

Март:

Рис. 1. Данные для примера 1.1

4) Консолидация.

· Выделить ячейку, кото­рая будет служить верхней левой ячейкой для блока с результа­тами консолидации. Для этого перейти на лист " 1 квартал" и выделить ячейку А1.

· Выбрать в меню " Данные/ Консолидация ".

· Внимательно рассмотреть диало­говое окно "Консолидация" (рис. 2).

· В выпадающем списке " Функция:" выставлено "Сумма", т.е. выбранные данные будут суммиро­ваться.

G Примечание. Можно выбрать и другую итоговую функцию. Самостоятельно по­смотрите список.

Рис. 2. Диало­говое окно "Консолидация".

· Следующее поле: " Ссылка ".

Порядок действий при задании диапазонов консолидации:

- установить курсор для ввода в поле " Ссылка ",

- выделить диапазон для консолидации;

- когда в этом поле поя­вится адрес диапазона, щёлкнуть кнопку " Добавить " — адрес диапазона переместится в окно "Список диапазонов";

- повторить эти действия для всех объединяемых диапазонов.

В данном примере действия таковы:

- щёлкнуть мышью в поле "Ссылка".

- щёлкнуть по ярлычку листа "Январь" (в поле ввода появится "Январь!" — фор­мируется адрес). Выделить блок А1:С4 (в поле ввода "Январь!$А$1:$С$4") — вокруг блока бегущая пунктирная рамка.

- Щёлкнуть кнопку "Добавить" — адрес диапазона окажется в по­ле "Список диапазонов:".

- Аналогично добавить диапазоны "Февраль!$А$1:$С$3" и "Март!$А$1:$С$5". Список диапазо­нов консолидации сформирован.

· Установить флажки в блоке " Использовать в качестве имен ". Эти флажки ("подписи верхней строки" и "значения левого столбца") нужно ус­тановить, потому что информация в таблице будет определяться по названиям строк и столбцов.

G Примечания.

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

2) Флажок "создавать связи с исходными данными" устанавли­вать пока не будем.

· Щелчок по кнопке "ОК" - на рабочем листе появится таблица (рис. 3):

Рис. 3. Результат

консолидации данных

(пример 1.1).

G Внимание! Заголовок "Наименование товара" отсутствует. Верхняя строка дает подписи столбцов, в левом столбце — названия строк.

Самостоятельно!

1) Строку " Наименование товара." ввести в ячейку А1.

2) Проверить, целесообразно ли при вводе заголовков в рабо­чие листы с месяцами выделить и лист "1 квартал"?

На рис. 3 приведен результат консолидации данных без установления связи. Если мы внесем изменения в один из диапазонов консолидации, например, увеличим количество сделок, записанных за Ивановым, то таблица на листе "1 квартал " не изменится.

Чтобы изменить консолидированные данные в этом случае, нужно ещё раз выполнить команду "Данные/ Консолида­ция", в диалоговом окне ничего не менять, только щелкнуть "ОК". Тогда произойдет обновление таблицы.

Дата добавления: 2015-07-12