Связывание рабочих листов таблицы

 В Excel существует возможность связывания рабочих листов. С ее помощью можно, например, свести воедино значения разных таблиц на одном рабочем листе.

1. Прямое связывание

Для ссылки из таблицы, расположенной на Листе 1, на данные таблицы, расположенной на Листе 2, надо в ячейке таблицы Листа 1 указать абсолютный адрес ячеек с данными Листа 2, в виде:

=Лист 2!$A$1.

Если нужно сослаться на данные, расположенные в незагруженном файле (другой рабочей книге), то нужно задать полный путь местонахождения файла с названием листа в одинарных кавычках, при этом имя рабочей книги указывается в квадратных скобках:

=d:\ excel\[primer]лист 2'!$n$20 

Допустим, мы имеем электронный журнал. Для простоты ограничимся 3 предметами: литература, алгебра и геометрия.

Отдельный лист рабочей книги отводится для каждого предмета. Он содержит список класса (ограничимся 5 учениками), текущие оценки.

Необходимо выставить итоговую оценку за I четверть по каждому предмету, используя функцию СРЗНАЧ - среднее значение.

Как работать со встроенными функциями можно познакомиться на странице  http://kursach.com/!inforactehnolog/4.4.8.htm

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

Сравните свои результаты, с результатами на рисунке

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

Данные, расположенные в различных областях одного рабочего листа, на различных рабочих листах или в различных рабочих книгах, могут быть сведены вместе путем их консолидации. В этом случае рекомендуется:

1.     создать таблицы одинаковой структуры на разных рабочих листах,

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

3.     объединить данные в итоговой таблице путем консолидации.

В Microsoft Excel существует два метода консолидации данных:

•  По расположению, если сводимые области расположены идентично;

•  По категориям, если сводимые области отличаются по расположению.

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

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

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

• Установить курсор в верхнюю левую ячейку области консолидации.

• Задать команду консолидация из меню данные.

• В параметре функция указать сумма

• В параметре ссылка поочередно определять область-источник (включающую лишь подлежащие суммированию данные без меток) и нажимать кнопку добавить.

• Включить параметр создавать связи с исходными данными и нажать ок.

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

Исходные данные располагаются на листах Январь, Февраль и Март.  Необходимо произвести расчеты на этих листах: количество рабочих дней, количество дней по временной нетрудоспособности, количество дней отпуска и количество дней в командировке. Для расчета использовать функцию СЧЁТЕСЛИ

Подробнее об этой функции смотрите на странице

http://www.pro-excel.ru/37.html

На листе с именем I квартал необходимо свести данные вместе путем их консолидации по расположению.

Сравните свои результаты,
с результатами на рисунке

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

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

• Ввести в область консолидации (т.е. в результирующую таблицу) категории (заголовки шапки или боковик).

• Выделить эту область консолидации, включая категории и будущие суммарные данные.

• Задать команду консолидация из меню данные.

• В параметре функция указать сумма

• В параметре ссылка поочередно определять область-источник (включающую категории и данные) и нажимать кнопку добавить.

• Если категориями являются заголовки шапки, то включить параметр в верхней строке. Если категориями являются названия в боковике, то включить параметр в левом столбце.

• Включить параметр создавать связи с исходными данными и нажать ок.

В таблице, полученной по команде консолидация, можно работать как со структурой: нажимать кнопку 2 и раскрывать все внутренние данные, входящие в сумму. Нажатие кнопки 1 восстанавливает вариант таблицы с итоговыми данными. Нажатие кнопки + (плюс) приводит к раскрытию промежуточных данных для соответствующей метки (категории). Нажатие кнопки - (минус) убирает промежуточные данные для данной категории.

На листах книги 1квартал, 2квартал, 3квартал, 4квартал ведены исходные данные о закупке канцтоваров в течение года. Необходимо рассчитать стоимость закупки канцтоваров за каждый квартал и стоимость товаров по группам.

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

Сравните свои результаты,
с результатами на рисунке