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

Работа с группой листов

MS Excel имеет инструмент, позволяющий работать сразу с группой листов. Это позволяет редактировать один лист, но при этом все изменения появляются на листах группы, как если бы Вы писали «под копирку». Для объединения листов в группы необходимо при нажатой клавише Ctrl левой клавишей мыши нажать на ярлыки листов, которые необходимо объединить в группу. При этом ярлыки листов группы будут отображаться более тусклыми цветами. Для снятия группировки необходимо нажать правой клавишей на один из ярлыков листов группы и выбрать команду Разгруппировать листы.

Задание:

1. Создайте новую пустую книгу Microsoft Excell.

2. Добавьте новый лист и переименуйте листы в Квартал1, Квартал2, Квартал3, Квартал4.

3. Сгруппируйте листы: Квартал1, Квартал2, Квартал3, Квартал4.

4. Создайте таблицу как показано на рисунке на всех листах созданной группы.

5. Разгруппируйте листы и создайте на листе Квартал4 ячейку F1 с Итого за год. Для диапазона F2-F7 посчитайте суммарный расход для каждого города.

6. Скопируйте Лист Квартал2 и обратите внимание, что Итого за год в Квартал4 учел, что в диапазоне Квартал1-Квартал3 появился новый лист.

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

В зависимости от способа организации данные для консолидации можно задавать тремя способами:

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

Второй и третий методы задания исходных данных для консолидации - по расположению ячеек и по заголовкам строк или столбцов – реализуются в диалоговом окне Consolidate (Консолидация), которое вызывается с вкладки Данные (Date)из набора команд Работа с данными (Data Tolls), щелкнув по кнопке Консолидация (Consolidate).

Для консолидации данных из нескольких рабочих листов выполните перечисленные ниже операции:

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

· Выделите ячейку, в которую следует помесить данные консолидации.

· На вкладке Данные из набора команд Работа с данными, щелкните по кнопке Консолидация.

· Щелкните ОК, чтобы закрыть диалоговое окно и получить объединенные данные.

· Результаты появятся на итоговом рабочем листе в формате с иерархической структурой.

Задание:

1. Откройте файл Консолидация.xlsx. Откройте Лист3.

2. Перейдите к ячейке А1. Добавьте консолидацию данных по листам Офис1 и Офис2.

1. Перейдите к командной вкладке Данные.

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

3. Щелкните в поле Ссылка.

4. Перейдите на лист Офис 1.

5. Выделите диапазон ячеек А2:Е9.

6. Щелкните по кнопке Добавить.

7. Перейдите на лист Офис 2.

8. Выделите диапазон ячеек А2:Е9.

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

4. Нажмите ОК.

Дата добавления: 2017-01-14