Консолидация данных в соответствии с расположением

Практическая работа. Консолидация данных

В Excel имеется много технологий по обобщению данных и получения сводок. Простейшей из них является Консолидация данных.

Консолидация позволяет объединять данные, расположенные в различных местах, и выводить их в область назначения. При консолидации данных рабочих листов происходит обобщение однородных данных.

Возможны следующие способы консолидации ячеек:

- в соответствии с расположением данных в диапазоне ячеек (данные рас­положены на нескольких листах в одних и тех же местах),

- в соответствии с заголовками строк и столбцов (данные расположены на нескольких листах, имеющих различную структуру, но одинаковые названия столбцов или строк),

- с применением трехмерных формул (ссылка 3-D).

При консолидации данных могут использоваться следующие операции с данными:

- сумму или произведение значений,

- среднее значение,

- определение максимального или минимального значения,

- подсчет количества значений или пустых строк,

- среднеквадратичное отклонение выборки из генеральной совокупности,

- дисперсия выборки из генеральной совокупности.

Консолидация данных в соответствии с расположением

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

Требуется вычислить суммарную зарплату за квартал и среднемесячную зарплату.

 

 

 

 

Переименуем рабочие листы в соответствии с названиями месяцев, а лист, на котором будут размещены консолидированные данные, назовем Квартал. На этом листе поместим копию списка сотрудников фирмы.

Выделим диапазон ячеек С3:С12.

 

 

В меню Данные выберем команду Консолидация.

 

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

Диалоговое окно свернется в строку, следует щелкнуть по ярлычку листа Янв и выделить диапазон с данными.

 

 

Обратите внимание, что при выделении диапазона его адрес появляется в строке Консолидация-Ссылка. Следует щелкнуть по кнопке с красной стрелочкой для возврата в диалоговое окно Консолидация.

Теперь надо щелкнуть по кнопке Добавить.

 

Адрес первого диапазона появился в списке диапазонов. Надо снова щелкнуть по кнопке с красной стрелочкой в поле Ссылка. Затем щелкнуть по ярлычку листа Фев. Выделить диапазон ячеек с данными. Убедиться в правильности выделения и щелкнуть по красной стрелочке возврата.

Щелчком по кнопке Добавить адрес следующего диапазона добавляется в список диапазонов.

Повторить указанные действия для выделения диапазона на листе Мар и добавления адреса в список диапазонов.

 

Щелкнуть по кнопке ОК и посмотреть результат.

 

 

 

 

Выделить диапазон ячеек D3:D12, в меню Данные выбрать Консолидация, раскрыть список в верхнем поле и выбрать Среднее.

 

 

 

 

Умный Excel в списке диапазонов предлагает указанные ранее адреса.

 

Следует убедиться в правильности адресов и щелкнуть по кнопке ОК.

Следует иметь в виду, что при изменении данных на листах Янв, Фев или Мар консолидированные данные на листе Квартал не изменятся.

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

 

Вставим еще один лист, переименуем его, скопируем список с фамилиями сотрудников.

 

Выделим диапазон ячеек С3:С12, в меню Данные выберем Консолидация, установим флажок Создавать связи с исходными данными.

Повторим действия, отмеченные выше, для добавления адресов в список диапазонов.

Щелкнем по кнопке ОК.

 

 

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

 

Если в поле группировки щелкнуть по кнопке со знаком +, то скрытые при группировке строке отобразятся.

При изменении исходных данных на листах Янв, Фев или Мар консолидированные данные автоматически изменятся. Так же следует обратить внимание на то, что порядок отображения данных соответствует порядку в списке диапазонов.