II.4. Объединение и связывание электронных таблиц

В Excel

Microsoft Excel предоставляет возможность объединять и связывать рабочие листы или файлы электронных таблиц таким образом, чтобы в результате ввода значения в одну таблицу изменялось содержимое другой таблицы, а выполнение вычислений в одном файле приводило бы, например, к выдаче сообщения в дру­гом.

При объединении и связывании таблиц используется абсолютная адресация. При­знаком абсолютного адреса является $ (знак доллара). В зависимости от позиции этого знака в адресе ячейки соответствующая область данных не модифицируется. Например:$А$1, $А1, А$1. Сделать адрес абсолютным можно несколькими спосо­бами:

· ввести знак $ с клавиатуры;

· выделить ячейку, затем дважды щелкнуть мышью в строке формул на адресе ячейки, чтобы выделить его, а затем нажать клавишу <F4>.

Объединять листы рабочей книги можно:

· суммированием содержимого ячеек соответствующих рабочих листов с ис­пользованием функции“Автосуммиро-вание”;

· консолидацией электронных таблиц;

· созданием сводных таблиц с помощью “Мастера сводных таблиц”, вызов которого производится с помощью команды “Данные/Сводная таблица”(далее необходимо следовать указаниям “Мастера”).

II.5.Консолидация данных в электронных таблицах

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

Консолидируем данные из столбцов G и Н из нескольких (например, трех) таблиц. Откроем все таблицы, данные из которых необходимо консолидировать. Пусть это будут таблицы со следующими именами “Книга1.xls”, “Книга2.xls”, “Книга3.xls”. Для перехода их одной таблицы в другую можно воспользоваться командой “Окно/Книга1.xls” - откроется первая таблица, “Окно/Книга2.xls” - вторая таблица, “Окно/Книга3.xls” - третья.

Для решения задачи консолидации выделим об­ласть назначенияв таблице итоговых результатов (пусть имя этой таблицы - “Книга4.xls”), например, ячейкиВ5:С11, далее

Рис.11.Диалоговое окно “Консолидация”

выберем команду“Данные/Консолидация” и в появившемся диалоговом окне “Консолидация”(рис.11) в поле “Функ-ция”установим функцию “Сумма”.

Затем в поле “Ссылка” определяем области-источники, которые нужно консолидировать. Для этого перейдем на лист с первой таблицей “Книга1.xls” с помощью команды “Окно/Книга1.xls” и выделим ячейки G5:H11. В окне “Консолидация” нажмем кнопку “Добавить”. В результате в поле “Список диапазона”сформировалась ссылка на выделенную область: [Книга1.xls]Лист1!$G$5:$Н$14 (рис.12). Excel использует здесь абсолютные ссылки на ячейки.

Рис.12.Диалоговое окно “Консолидация”с заполненной первой ссылкой.

Затем перейдем на лист “Книга2.xls” (с помощью команды “Окно/Книга2.xls”), выделим ячейки G5:H11 и в окне “Консоли­дация” нажмем кнопку “Добавить”. Далее перейдем на лист “Книга3.xls” (аналогично), выделим ячейки G5:H11и также в окне “Консоли­дация” нажмем кнопку “Добавить”.В итоге окно “Консоли­дация”примет вид (рис.13).

Рис.13.Диалоговое окно “Консоли­дация”после заполнения ссылок (списка диапазона).

Далее устанавливаем флажок “Создавать связи с ис-ходными данными” и нажимаем кнопку <ОК>. Cводная табли-ца готова. При консолидации без установления связей изменение данных в таблицах-источ­никах не приведет к обновлению данных в итоговой таблице, поэтому при внесе­нии изменений в таблицы-источники консолидацию необходимо повторить. Если флажок “Создавать связи с исходными данными”установлен, то область назначения в итоговой таблице будет автоматически обновляться при внесении измене­ний в областях-источниках.

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

II.6.Работа со структурированной таблицей

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

Обратимся к полученной итоговой таблице (“Кни-га4.xls”) и познакомимся с символами структуры (рис.14). Их два типа: кнопки с номерами уровней (в таблице это кнопки <1>, <2> и<3> находя­щиеся в левом верхнем углу рабочей области экрана) и знаки <+> (плюс) и/или <—> (минус), позво­ляющие соответственно раскрывать или скрывать детали структурирован­ного документа.

Рис.14.Символы структуры итоговой таблицы.

Щелкнем, например, по кнопке <2>. Итоговая таблица «распахнулась», предоставив воз­можность просмотреть консолидируемые данные. Щелкнув по кнопке <1>, можно скрыть исходные данные из таблиц-источников. Щелкнем по любому из значков <+> (плюс). Результатом будет открытие одной из составляющих итоговой таблицы. Щелкнем по знач­ку <-> (минус), в результате скроются исходные данные из таблиц-источников.