Есть такие задания, и они никак не принимаются. Притом ошибки только по этим заданиям, в какой бы последовательности я их не заполнял. Как их заполнять??? Инструкций в заданиях нет. Там через запятые, подряд как число, через пробел, или надо текст весь писать через запятую или точку? Задание: Пронумеруйте шаги Создание имени путем выделения ячеек на листе: |
Консолидация данных
МЕТОДИЧЕСКИЕ УКАЗАНИЯ
Общие сведения
Консолидация – это агрегирование (объединение) данных, представленных в исходных областях-источниках – таблицах, списках, блоках ячеек и др.
Консолидация данных (рис. 8.1 рис. 8.1) выполняется в соответствии с выбранной функцией обработки. Результат консолидации находятся в области- назначения. Таблица консолидации создается путем применения функции обработки к исходным значениям. Области-источники могут находиться на различных листах или рабочих книгах их может быть до 28= 256.
В Excel возможны следующие варианты консолидации данных:
- с помощью формул, где используются ссылки;
- по расположению данных для одинаково организованных областей-источников (фиксированное расположение);
- по категориям для различных по структуре области данных;
- с помощью сводной таблицы;
- консолидация внешних данных.
При консолидации данных с помощью формул используемые в них ссылки могут иметь разное представление в зависимости от взаимного расположения областей-источника и области-назначения:
- все области на одном листе – в ссылках указывается адрес блока ячеек (например, D1:C8);
- области на разных листах – в ссылках указывается название листа и диапазон (например, лист1 !D1:лист2!С8, т.е. с ячейки D1 листа 1по ячейку С8 листа 2);
- области в разных книгах, на разных листах – в ссылках указывается название книги, название листа, диапазон, например [книга1]лист1! D1:[книга2]лист2!С8.
Консолидация данных по расположению
При консолидации по расположению данных все источники имеют одинаковое расположение данных источников (имена категорий данных в выделяемые области-источники не включаются). Данные имеют одинаковую структуру, фиксированное расположение ячеек и могут быть консолидированы с определенной функцией обработки (среднее значение, максимальное, минимальное и т.п.) по их расположению. Для консолидации данных курсор устанавливается в область места назначения. Выполняется команда Данные > Работа с данными > Консолидация, выбирается вариант и задаются условия консолидации.
Пример 1. На разных листах рабочей книги по каждому товару хранятся сведения о показателях реализации товаров за конкретный период (рис. 8.2 рис. 8.2). Консолидируемая область выделена цветом.
При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации данных по категориям используются имена строк и/или столбцов (имена включаются в выделенные области-источники).
по расположению
Выполняется команда Данные > Консолидация, выбирается вариант и задаются условия консолидации.
Пример 2. На рабочих листах представлена информация областей-источников в виде структуры на рисунке 8.3 рис. 8.3. Число строк и столбцов – переменное, состав показателей и виды товаров могут различаться или совпадать, при консолидации по категориям они собираются вместе. Цветом показана консолидируемая область источников.
Условия консолидации задаются в диалоговом окне Консолидация (рис. 8.4 рис. 8.4 ). В поле Функция выбирается функция консолидации данных. Для каждой области-источника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область-источника для выделения блока ячеек и нажимается кнопка Добавить.
Ссылка может иметь любую из следующих форм:
Ссылки на ячейки | Источники и назначение на одном листе |
Ссылки на лист и ячейки | Источники и назначение на разных листах |
Ссылки на книгу, лист и ячейки | Источники и назначение в разных книгах |
Полный путь и все ссылки | Источники и назначение в различных местах диска |
Имя поименованной области | Область-источник поименована |
Консолидация данных по категориям
При консолидации по категориям область имен входит в выделение, устанавливаются флажки подписи верхней строки или значения левого столбца (рис. 8.4 рис. 8.4). Excel автоматически переносит эти имена в область назначения.
При консолидации внешних данных в диалоговом окне Консолидация следует нажать кнопку 0бзор, в диалоговом окне Обзор выбрать файл, содержащий области-источники для добавления к списку, а затем добавить ссылку на ячейку или указать имя блока ячеек.
Переключатель Создавать связи с исходными данными включается при консолидации связи области назначения к областям-источникам.
При изменениях в области назначения результаты консолидации автоматически обновляются.
Примечание. Нельзя корректировать ссылки на области-источники (добавлять или удалять новые области-источники) при наличии флажка переключателя Создавать связи с исходными данными.
В окне Список диапазонов для текущего рабочего листа итогов консолидации перечислены ссылки на все области-источники. Ссылки можно модифицировать: добавить новые области-источника, удалить существующие области-источника либо изменить его конфигурацию, если только до этого не был выбран переключатель Создавать связь с исходными данными.
Для одного листа итогов консолидации набор ссылок на области-источники постоянен, на нем можно построить несколько видов консолидации с помощью различных функций. Курсор переставляется в новое место, выполняется команда Данные > Консолидация, выбирается другая функция для получения сводной информации.
ЗАДАНИЕ
- Предварительно подготовить 2–е таблицы для консолидации по областям, например продажа товаров по кварталам в 1 и 2, по аналогии с примером (рис. 8.5 рис. 8.5). Построить итоговую таблицу консолидированную по областям.
- Предварительно подготовить 2 таблицы для консолидации по категориям (см. рис. 8.3 рис. 8.3) – дополнить 2-ую таблицу (копию) новым столбцом и новой строкой. Построить итоговую таблицу, консолидированную по категориям.
Пояснения к выполнению
Консолидация по областям
1. Создать таблицу своего варианта ("например, Показатели в 1 квартале") на новом листе, переименовать лист в "Консолидация" (дважды щелкнуть мышью по имени и ввести новое имя).
2. Сделать копию таблицы на том же листе, изменить в ней данные. Эта таблица будет отражать, например, показатели во 2 квартале (рис. 8.5 рис. 8.5).
3. Выполнить консолидацию данных по расположению:
- установить курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A10;
- выполнить команду Данные > Консолидация;
- в диалоговом окне Консолидация выбрать из списка функцию Сумма и установить флажки подписи верхней строки и значения левого столбца;
- установить курсор в окне Ссылка и выделить блок ячеек А2:D7 (показатели в 1 квартале);
- нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
- установить курсор в окне Ссылка, удалить прежнюю запись и выделить блок ячеек F2:I7 (показатели во 2 квартале);
- нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
- нажать кнопку ОК и сравнить полученные результаты с рисунка8.5.
Консолидация по категориям
1. Добавить строку с товаром TV-тюнер с соответствующими числами в первую таблицу "Показатели в 1 квартале" (рис. 8.6 рис. 8.6).
2. Вставить новый столбец с именем % реализации и заполнить формулой Продано*100/ Получено. Чтобы выводилось 2 знака после запятой, в меню Формат > Ячейки, вкладка Число выбрать в поле Числовые форматы строку Числовой и установить Число десятичных знаков – 2;
3. Сделать консолидацию данных по категориям:
- установить курсор в первую ячейку области, где будет располагаться консолидиpoванная таблица, например в ячейку A11;
- выполнить команду Данные > Консолидация;
- в диалоговом окне Консолидация выбрать из списка функцию Сумма и установить флажки подписи верхней строки и значения левого столбцов;
- установить курсор в окне Ссылка и выделить блок ячеек A2:Е8 (показатели в 1 квартале);
- нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
- установить курсор в окне Ссылка, удалить предыдущую запись и выделить блок ячеек G2:J7 (показатели во 2 квартале);
- нажать кнопку Добавить, в окне Список диапазонов появится ссылка на выделенный диапазон;
- нажать кнопку <ОК> и сравнить результаты с рис. 8.6 рис. 8.6.
КОНТРОЛЬНЫЕ ВОПРОСЫ
- Что называется консолидацией?
- Где располагается результат консолидации?
- Что такое области-источники и где они могут располагаться?
- Какие существуют варианты консолидации?
- Из чего состоит область консолидации при объединении данных по расположению?
- Из чего состоит область консолидации при объединении данных по категориям?