Консолидация с использованием формул

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

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

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

Консолидация по расположению

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

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

Консолидация по категории

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

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

Другие способы объединения данных

Можно создать отчет сводной таблицы из нескольких диапазонов консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий.

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

АГРЕГИРОВАНИЕ ДАННЫХ

Агрегирование или агрегация (лат. aggregatio «присоединение») – процесс объединения элементов в одну систему (пример: восемь блондинок в розовом идут в музей ночью в Таганроге есть объединение по каким-либо однородным показателям с целью получения общих итогов).

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

СПИСОК - набор строк (записей) на рабочем листе с однотипными данными, которые взаимосвязаны и имеют постоянный формат.

ПОЛЯ - категории, по которым упорядочена информации в списке (отдельный столбец - отдельная категория). Поля имеют метки – заголовки полей.

Более подробно об агрегации данных с помощью можно посмотреть здесь: http://sapr.ictis.sfedu.ru/files/16

 

 

Задания:

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

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

3. Обосновать наиболее эффективную форму представления и отобразить полученные в п.2 данные с помощью средств деловой графики.

 

Дополнительные задания:

1. Используя справочную систему MS Excel (или иные доступные источники):

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

­ познакомьтесь с возможностями сортировки данных в таблице.

­ познакомьтесь с возможностями агрегирования данных на основе промежуточных итогов.

­ познакомьтесь с возможностями агрегирования данных на основе консолидации данных.

2. Измените внешний вид деловой графики (п. 3 основного задания), используя доступные инструменты (2-3 различных варианта).

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

 

Содержание отчета

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

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

3. Краткие выводы по проделанной работе.