Освоение способа обобщения данных, используя команду консолидация

агентов и количество сделок, которые они совершили в течение месяца.

3.  Построить сводку за первый квартал. Установить связи с исходными данными.

Таблица 14

Таблица 15

Таблица 16

Методические рекомендации по выполнению задания 1

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

2.  Внести заголовков одновременно в несколько листов. Выделить все листы с названиями месяцев: для этого перейти на лист «Январь», нажатье клавишу Shift и, не отпуская её, кликнуть по ярлычку листа «Март». Будут выделены все листы рабочей книги, при этом активным листом останется «Январь». Если нужно выделить только некоторые листы, то нужно удерживать нажатой клавишу Ctrl. Далее ввести в ячейки названия граф таблиц. Кликнуть по ярлычку листа «Февраль» выделение с нескольких листов будет снято. Убедитесь, что в ранее выделенные листы внесен один и тот же текст в ячейки. Для иллюстраций целей на листе «Февраль» поменяйте местами заголовки «Сделки», «Объём».

Чтобы снять выделение всех листов, необходимо дважды кликнуть по ярлычку любого листа. Возможно, использовать контекстное меню: ВЫДЕЛИТЬ ВСЕ ЛИСТЫ, РАЗГРУППИРОВАТЬ ЛИСТЫ.

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

4.  Консолидация. Нужно выделить ячейку, которая будет служить верхней левой ячейкой для блока с результатами консолидации. Для этого перейдем на лист «1 квартал» и выделить ячейку, которая будет служить верхней левой ячейкой для блока с результатами консолидации.

Выбрать ДАННЫЕ / РАБОТА С ДАННЫМИ / КОНСОЛИДАЦИЯ. В диалоговом окне:

−  в списке ФУНКЦИЯ выставлено СУММА, т.е. выбранные данные будут суммироваться. Можно выбрать и другую итоговую функцию;

−  поле ССЫЛКА. Выполнить последовательность действий: по очереди выделять диапазоны для консолидации; когда в этом поле появится очередной диапазон, кликнуть кнопку Добавить – адрес диапазона переместится в окно Список диапазонов;

−  блок ИСПОЛЬЗОВАТЬ В КАЧЕСТВЕ ИМЕН из двух флажков «подписи верхней строки» и «значения левого столбца». Установить оба флажка. Эти флажки нужно установить, потому что информация в таблице будет идентифицироваться по названиям строк и столбцов. Если бы таблицы по месяцам имели одинаковую структуру, но разные названия столбцов, например, на одном листе столбец называется «Сделки», а на другом – «Количество сделок», но их расположение в таблице одинаково, тогда следовало снять флажок «подписи верхней строки»;

−  флажок «создавать связи с исходными данными» пока не устанавливать.

5.  После выполнения консолидации заголовок «Фамилии» отсутствует. Строку «Фамилия И.О.» можно ввести в ячейку. Было бы целесообразно при вводе заголовков в рабочие листы с месяцами выделить и лист «1 квартал».

6.  Если внести изменения в один из диапазонов консолидации, например, увеличим количество сделок, записанных за Ивановым, то таблица на листе «1 квартал» не изменится, так как не был установлен флажок «создавать связи с исходными данными». Но если выполнить команду ДАННЫЕ / КОНСОЛИДАЦИЯ, в диалоговом окне ничего не менять, только кликнуть ОК, то произойдет обновление таблицы.

7.  Установление связей. Выделите на листе «1 квартал» ячейку. В диалоговом окне КОНСОЛИДАЦИЯ и установить флажок «создавать связи с исходными данными». Таблица измениться. Слева появятся символы структуры. Раскрыв уровень структуры, появятся имена текущей рабочей книги (можно консолидировать данные и из разных рабочих  книг). Если теперь проводить изменения на каком-либо листе, то итоговые данные будут обновлены автоматически.

Задание 2.

1.  Создать новые рабочие книги «Январь», «Февраль», «Март», «1 квартал», в которых создать соответствующие таблицы.

2.  Консолидировать данные из трёх разных рабочих книг.

Задание 3.

Подвести итоги работы агентов за первый квартал, построив сводную таблицу.

Методические рекомендации по выполнению задания 3

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