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

Содержание

1. Консолидация данных в EXCEL

1.1 Общее описание процесса консолидации

1.1.2 Консолидация данных по физическому расположению

1.1.3 Консолидация по заголовкам строк и столбцов

1.1.4 Консолидация данных с использованием ссылок

1.1.5 Ручная консолидация рабочих листов

1.1.6 Создание сводной таблицы на основе данных, находящихся в нескольких диапазонах консолидации

1.2 Связывание объектов

1.2.1 Связывание с ячейками и диапазонами ячеек рабочей книги

1.2.2 Связывание ячеек с помощью команд Копировать и Вставить ссылку

1.2.3 Связывание с изображениями ячеек рабочего листа

1.2.4 Обновление связанных изображений ячеек

1.2.5 Изменение связи с изображением ячеек

1.3. Основные приемы работы со связанными книгами

1.3.1 Открытие связанных рабочих книг

1.3.2 Изменение и обновление ссылок

1.3.3 Сохранение рабочих книг со связями

Список использованных источников

1. Консолидация данных в EXCEL

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

Консолидация объектов – это процесс объединения данных из разных источников в виде итоговых значений, размещаемых в диапазоне назначения.

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

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

Возможны следующие виды консолидации:

- по физическому расположению (п.1.2);

- по заголовкам строк и столбцов (п.1.3);

- с использованием ссылок (п.1.4);

- ручная консолидация (п.1.5).

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

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

Проблемам связывания объектов посвящен раздел 2.

1.1 Общее описание процесса консолидации

Процесс консолидации предполагает обязательное указание

- диапазона назначения;

- источников данных;

- способа консолидации;

- наличия связи между объектами консолидации;

- типа (функции) консолидации.

Последние четыре действия выполняются с помощью диалогового окна Консолидация, которое вызывается по команде Данные – Консолидация (рис.1).

Рис.1. Вид окна Консолидация

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

- отдельной ячейки;

- строки ячеек;

- столбца ячеек;

- диапазона ячеек, куда необходимо поместить обобщенные данные.

Заполнение его осуществляется по правилам, представленным в табл.1.

Таблица 1. Заполнение диапазона назначения

Выделение

Результат

Ячейка

Заполняются все ячейки, необходимые для всех консолидируемых категорий (элементов) исходных данных

Строка ячеек

Заполняются ячейки вниз от выделения. Ширина области назначения в точности совпадает с шириной выделения

Столбец ячеек

Заполняются ячейки вправо от выделения. Высота области назначения в точности совпадает с высотой выделения

Диапазон ячеек

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

Примечания:

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

- Текст и формулы не переносятся с исходных листов в область назначения. Их необходимо создать вручную перед консолидацией.

Источники данных представляют собой диапазоны ячеек. Число диапазонов может достигать 255. Источники данных не обязаны быть открыты во время консолидации. Исходные области обрабатываемых данных задаются либо трехмерными формулами непосредственно в ячейках диапазона назначения, либо в поле Ссылка диалогового окна Консолидация (рис.1). Источники данных могут находиться на том же листе, что и таблица консолидации, на других листах той же книги, в других книгах или в файлах Lotus 1-2-3.

Для указания источников данных могут быть использованы два способа:

- выделение исходного диапазона с помощью мыши;

- ввод ссылки на диапазон с клавиатуры.

Выделение исходного диапазона с помощью мыши осуществляется стандартными приемами.

Ввод ссылок на диапазон с клавиатуры. Существует два вида ссылок: внутренние и внешние.

Внутренняя ссылка – это ссылка на диапазоны ячеек рабочей книги. Она является частным случаем внешней ссылки.

Синтаксис внутренней ссылки:

='Имя_листа'!Адрес_диапазона

Пример 1.

Необходимо записать в ячейку В2 первого листа(Лист1) значение ячейки D4 следующего листа (Лист2).

Для этого в ячейку В2 введите формулу:

='Лист2'!$D$4

Внешняя ссылка –это ссылка на диапазоны ячеек, расположенных в других книгах.

Синтаксис внешней ссылки:

='Путь\[Имя_рабочей_книги]Имя_листа'!Адрес_диапазона

Пример 2.

Необходимо в ячейку В2 первого листа (Лист1) записать значение ячейки А1 из листа 2кв1996 файла КВАРТАЛ2.xls, находящегося на диске Е: в папке USERS.

Для этого в ячейку В2 следует ввести следующую формулу:

='E:\USERS\[КВАРТАЛ2.xls]2кв1996'!$A$1

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

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

Пример 3. Исходные области и область назначения находятся на одном листе. Например, включить диапазон Бюджет (диапазону ячеек предварительно присвоено имя Бюджет).

Для этого вводим ссылку

=Бюджет

Пример 4. Исходные области и область назначения находятся на разных листах. В этом случае удобно использовать имя листа и имя или ссылку на диапазон. Например, чтобы включить диапазон с заголовком «Бюджет», находящийся в рабочей книге на листе «Бухгалтерия», необходимо ввести ссылку

=Бухгалтерия!Бюджет

Пример 5. Исходные области и область назначения находятся в разных книгах. Используйте имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Продажи» из листа «Дальний Восток» в книге «1996», находящейся в этой же папке, введите:

='[1996.xls]Дальний Восток'!Продажи

Пример 6. Исходные области и область назначения находятся в разных книгах разных каталогах диска. Используйте полный путь к файлу книги, имя книги, имя листа, а затем — имя или ссылку на диапазон. Например, чтобы включить диапазон «Оборот» листа «Февраль» в книге «Отдел продаж», которая находится в папке «Бюджет» на диске С:, введите:

='[C:\Бюджет\Отдел продаж.xls]Февраль'!Оборот

Если диапазонам назначены уникальные, не присвоенные автоматически имена, то в ссылке можно не указывать имена листов. Например '[1996.xls]'!Продажи или '[C:\Бюджет\Отдел продаж.xls]'!Оборот в примерах 5 и 6.

Удаление и редактирование ссылок

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

Чтобы удалить ссылку из диапазонов консолидации, выделите ее в списке диапазонов и нажмите кнопку Удалить .

Чтобы отредактировать ссылку, выделите ее в диалоговом окне Консолидация в списке диапазонов. Она появится в поле Ссылка, где ее можно изменить. После внесения всех исправлений нажмите кнопку Добавить. Затем удалите старый вариант измененной ссылки.

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

Наличие связи между объектами. При наличии связи результаты будут обновляться при изменении данных, а в области назначения будет создана структура. Т.е. в диапазон консолидации между итоговых строк будет вставлена детальная информация, связанная внешними ссылками с исходными диапазонами. Причем, диапазон назначения будет структурирован. Верхним уровнем структуры будут итоговые данные, нижним — исходные (см. пример _8.).

Тип ( функция) консолидации. Обобщение исходных данных может быть осуществлено с использованием следующих функций:

- Сумма

- Количество значений

- Среднее значение

- Смещенное отклонение

- Максимум

- Несмещенное отклонение

- Минимум

- Смещенная дисперсия

- Произведение

- Несмещенная дисперсия

- Количество чисел

По умолчанию используется функция Сумма.

1.1.2 Консолидация данных по физическому расположению

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

Пример 7.

Необходимо просуммировать данные об обороте товаров за первый и второй квартал 1996 года. Данные соответственно находятся на листе 1кв1996 ячейках D2:D5 файла Квартал1.xls и на листе 2кв1996 ячейках B3:B6 файла Квартал2.xls. В указанных диапазонах нет заголовков граф.

Чтобы осуществить консолидацию этого типа необходимо выполнить следующие действия:

1. Выделить диапазон назначения, руководствуясь табл. 1.

Учитывайте только область данных. Текстовые заголовки в консолидации не участвуют. Для нашего примера это диапазон A2:A5 листа назначения (рис.3). Поскольку указан диапазон ячеек, то консолидируется только четыре значения по количеству ячеек в диапазоне (см.табл.1).

2. Выбрать команду Данные, Консолидация. Появится диалоговое окно Консолидация (рис. 1).

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

=[Квартал1.xls]1кв1996!$D$2:$D$5

Нажать кнопку Добавить, ссылка будет занесена в Список диапазонов (рис. 2).

4. Повторите шаги 3 и 4 для всех исходных диапазонов. Если исходные рабочие листы имеют однотипные имена, достаточно будет слегка редактировать строку в поле Ссылка. Для нашего примера это еще 1 диапазон:

=[Квартал2.xls]2кв1996!$В$3:$В$6

Рис.3. Результат консолидации примера 7

Рис. 2. Окно Консолидация для примера 7

5. В списке Функция выбрать тип консолидации. По умолчанию используется функция сумма.

6. Сбросить оба флажка в группе Использовать в качестве имен, поскольку ячейки привязаны к своим позициям, а не к заголовкам.

7. Установить при необходимости флажок в опции Создать связи с исходными данными. Тогда результаты будут обновляться при изменении данных, а в области назначения будет создана структура.

8. Нажать кнопку ОК.

Результат консолидации представлен на рис.3.

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

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

1.1.3 Консолидация по заголовкам строк и столбцов

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

Таким образом, достигается большая гибкость.

Пример 8.

Необходимо составить сводную ведомость расходов двух различных филиалов компании (книги Филиал1.xls и Филиал2.xls). Названия статей и их порядок следования могут отличаться. Исходные диапазоны представлены на рис.5. Заголовки столбцов совпадают, заголовки строк нет.

Рис.5. Исходные данные для примера 8

Чтобы осуществить консолидацию по заголовкам строк и столбцов необходимо:

1. Выделить диапазон назначения. Если необходимо, чтобы поля были расположены в определенном порядке, следует включить в диапазон заголовки полей или строк. Заголовки должны быть написаны в точности так, как и на исходных листах. Если заголовки не вводить, то Excel создаст их автоматически (рис.6).

Рис.6. Диапазон назначения для примера 8

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

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

='[Филиал1.xls]Лист1'!$А$1:$С$6

4. Нажать кнопку Добавить, ссылка будет занесена в Список диапазонов.

5. Повторить шаги 3 и 4 для всех исходных диапазонов. Если исходные рабочие листы имеют однотипные имена, достаточно будет немного отредактировать строку в поле Ссылка.

='[Филиал2.xls]Лист1'!$А$1:$С$6

6. В списке Функция выбрать тип консолидации. Для рассматриваемого примера – функция Сумма.

7. Установить флажки в группе Использовать в качестве имен. Можно установить как один флажок, так и оба Подписи верхней строки, Значения левого столбца.(рис. 7).

Рис.7.Окно Консолидация для примера 8

8. Установить при необходимости флажок Создать связи с исходными данными. Тогда результаты будут обновляться при изменении данных, а в области назначения будет создана структура.

9. Нажать кнопку ОК.

Результат представлен на рис.8.

Рис.8.Результат консолидации данных примера 8

1.1.4 Консолидация данных с использованием ссылок

Пример 9. Подготовить отчет по результатам деятельности филиалов предприятия за 5 лет с 1995 года включительно. Данные за каждый год представлены в отдельной книге. Структура годовых отчетов однотипна. В качестве примера на рис.11 приведен отчет работы предприятия за 1995 год.

Для консолидации данных по ссылке необходимо:

1.Скопировать или задать надписи для данных консолидации (рис.12).

2.Указать ячейку, в которую следует поместить данные консолидации. Выделить ячейку В2 на Лист1 книги "Отчет за 5 лет".

Рис.11. Исходные данные для примера 9

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

='[Год1995.xls]Лист1'!$B$2:$D$2

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

Рис.12.Результат консолидации данных примера 9

1.1.5 Ручная консолидация рабочих листов

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

Пример 10. Дополнить отчет, полученный в примере 9 данными за 2000 год. Структура годового отчета не изменилась.

Для решения задачи:

1) Выделите диапазон с данными за 2000 год и скопируйте его с помощью команды Правка, Копировать.

2) Выделите диапазон назначения. Для рассматриваемого примера это диапазон ячеек G2-G5, расположенный на Лист1 книги "Отчет за 5 лет".

3) Активизируйте команду Правка-Специальная вставка. В появившемся диалоговом окне Специальная вставка (рис.13) выберите в группе Вставить переключатель Значения, а в группе Операция — Сложить.

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

Рис.13 Вид окна Специальная вставка

1.1.6 Создание сводной таблицы на основе данных, находящихся в нескольких диапазонах консолидации

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

Рис.14.Вид окна Шаг1 из 4 Мастера сводных таблиц

Поскольку консолидированные данные как правило бывают получены на базе исходных данных, имеющих разную структуру, то и структура области СТРАНИЦА сводной таблицы в этом случае несколько меняется.

Для отображения каждого диапазона исходных данных в консолидации используются дополнительные поля страниц. Элементы полей страниц представляют один или несколько исходных диапазонов. Например, если имеется консолидация бюджетных данных по нескольким отделам («Маркетинг», «Сбыт» и «Производство»), в поле страницы могут находиться элементы, представляющие данные по каждому отделу или их комбинации.

Выбрать тип поля страницы можно в диалоговом окне Мастера сводных таблиц - шаг 2а из 4 (рис. 15.).

Если необходимо отобразить одну страницу для каждого исходного диапазона или страницу, объединяющую все диапазоны исходных данных, установите переключатель в положение Создать одно поле страницы в диалоговом окне Мастер сводных таблиц - шаг 2a из 4.

Если необходимо создать несколько (можно создать до четырех) полей страниц и назначить каждому элементу исходного диапазона имя, а также создать структуру, не имеющую полей страниц, то установите переключатель в положение Создать поля страницы в диалоговом окне Мастер сводных таблиц - шаг 2a из 4. Этот переключатель используется для сравнения частей исходных данных или отображения всех данных.

Рис.15.Вид окна Шаг 2а из 4 Мастера сводных таблиц

Пример 11.

Проанализировать данные о продаже различных видов продукции филиалами компании за 3 месяца 1995 года.

Для этого выполните следующие действия:

1. Запустите Мастер сводных таблиц.

2. Установите переключатель на шаге 1 в положение В нескольких диапазонах консолидации(рис.14)

3. На шаге 2а установите переключатель в положение Создать одно поле страницы(рис.15)

4.Укажите диапазоны для консолидации на шаге 2б (рис.16)

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

Рис.17 Вид окна Шаг 3 Мастера сводных таблиц

6. Укажите место, куда будет помещена сводная таблица, на четвертом шаге.

Результат построения сводной таблицы представлен на рис.18

Рис.18 Сводная таблица примера 11

Если щелкнуть по раскрывающемуся списку Страница1, то появятся общие имена: Объект1,Объект2,Объект3 ,которые Excel по умолчанию присвоил каждому из диапазонов консолидации. Для того, чтобы задать имена диапазонов консолидации необходимо:

1. На шаге 2а (рис.15) установить переключатель в положение Создать поля страницы.

2. На шаге 2б необходимо указать количество страничных полей сводной таблицы и для каждого диапазона консолидации указать его имя или метку (редактируется Первое поле). (рис.19)

Рис.19. Создание страничных полей.

Остальные шаги в алгоритме построения сводной таблицы рассмотрены в Примере 11.

1.2 Связывание объектов

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

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

Независимо от того, открыта ли исходная рабочая книга или закрыта, целевая рабочая книга получит необходимые данные через связь. Если во время открытия целевой рабочей книги исходная рабочая книга открыта, то связи обновляются автоматически. Если исходная рабочая книга закрыта, то при открытии целевой пользователю будет задан вопрос, хочет ли он работать с данными, оставшимися после последнего сохранения или их следует обновить из исходной рабочей книги (см.п."Открытие связанных рабочих книг").

Можно установить связь исходных данных

1) с ячейками и диапазонами ячеек

2) с изображениями ячеек рабочего листа.

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

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

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

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

- Небольшие рабочие книги пересчитываются намного быстрее

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

- Систему, состоящую из отдельных компонентов, проще обслуживать и отлаживать

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

Большое количество связей может замедлить процесс открытия целевой рабочей книги. Кроме того, размеры рабочей книги с большим количеством внешних связей могут быть очень большими, так как Excel хранит в рабочей книге последнюю копию использованных в работе внешних данных, чтобы иметь возможность работать с рабочей книгой не обновляя ее связей. Если исходная рабочая книга всегда открывается вместе с целевой, или если вы всегда обновляете связи при открытии, или же если вы просто хотите сэкономить дисковое пространство, отключите сохранение копии внешних данных вместе с рабочей книгой. Для этого выберите команду Сервис, Параметры , в появившемся диалоговом окне раскройте вкладку Вычисления, снимите флажок Сохранять значения внешних связей и нажмите кнопку ОК (рис.20).

Рис.20.Вид окна Параметры команды Сервис

1.2.1 Связывание с ячейками и диапазонами ячеек рабочей книги

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

- ячейка с ячейкой;

- ячейка связывается с диапазоном ячеек;

- диапазон ячеек с диапазоном ячеек.

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

Связь ячейка-ячейка

Пример 12. На рис. 21 изображены рабочие книги, связанные с помощью внешней ссылки. Рабочая книга Квартал 1 является исходной для рабочей книги Годовые отчеты. В строке формул находится формула внешней ссылки, использованная в рабочей книге Годовые отчеты. Эта формула выглядит следующим образом:

Рис.21. Иллюстрация примера 12

='[Квартал l.xls]1 kb 1996'!$Е$2

и означает, что ячейка В2, в которой она помещена, связана с ячейкой Е2 рабочего листа 1кв1996 рабочей книги Квартал1. При изменении содержимого исходной ячейки, конечная ячейка также изменится.

В этой формуле Квартал1 — имя исходной рабочей книги, 1кв1996 — имя рабочего листа в этой книге, а $Е$2 — адрес ячейки, в которой хранятся связанные данные. Восклицательный знак отделяет адрес ячейки от имени рабочей книги и рабочего листа.

Связь ячейка – диапазон ячеек.

Пример 13. В ячейке В6 примера 12 может располагаться такая формула:

=СУММА(‘[Кварталl.xls]1кв1996’!$Е$2:$Е$5)

Данную формулу можно с помощью клавиатуры, однако легче это сделать с помощью мыши. Для этого необходимо щелкнуть по ячейке В6 в книге Годовые отчеты.xls, набрать знак равно (=) и функцию СУММ(), затем перейти в книгу Квартал1.xls на лист 1кв1996, выделить диапазон Е2:Е5 и завершить ввод формулы, нажав клавишу <Enter>.

Связь диапазона ячеек с диапазоном ячеек.

Можно связать диапазон ячеек с другим диапазоном только такого же размера. Такая связь использует формулу массива.

Пример 14. Например формула, связывающая ячейки В2:В5 рабочей книги Годовые отчеты с ячейками Е2:Е5 рабочей книги Квартал 1, может выглядеть следующим образом:

{=‘[Квартал l.xls]1 kb 1996’!$Е$2:$Е$5}

Фигурные скобки в этой формуле являются показателем того, что это формула массива.. Для ввода данной формулы необходимо выделить диапазон ячеек для результата (в данном примере это В1:В4). В ячейку В1 ввести формулу: =‘[Кварталl.xls]1kb1996’!$Е$2:$Е$5 , после чего нажать клавиши <Shift>+<Ctrl>+<Enter>. Результат представлен на рис.22.

Рис.22. Связь диапазон ячеек-диапазон ячеек

1.2.2 Связывание ячеек с помощью команд Копировать и Вставить ссылку

Чтобы связать ячейку или диапазон ячеек, можно воспользоваться командой Правка, Специальная вставка, нажав кнопку Вставить ссылку (рис.12).

Пример 15. В примере диапазон В2:В5 рабочей книги Годовые отчеты связан с диапазоном Е2:Е5 рабочей книги Квартал1 (рис. 23).

Рис.23. Иллюстрация примера 15

Чтобы установить эту связь выполните следующие действия:

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

2. Активизируйте исходную рабочую книгу.

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

4. Выберите команду Правка, Копировать

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

В данном примере необходимо выделить ячейку В5 рабочего листа 1996 рабочей книги Годовые отчеты. Не выделяйте весь диапазон, в который нужно поместить связь, т. к. при этом очень просто ошибиться в размерах и получить сообщение об ошибке Область копирования и область вставки имеют различную форму. Достаточно всего-навсего выделить один верхний левый угол диапазона.

6. Выберите команду Правка, Специальная вставка или, щелкнув правой кнопкой мыши по выделенной ячейке, выберите команду Специальная вставка из контекстного меню. На экране появится соответствующее диалоговое окно.

7. Установите переключатель. Вставить в положение Все, a переключатель Операция — в положение Нет (рис.13.)

8. Нажмите кнопку Вставить связь. В ячейках появятся ссылки.

1.2.3 Связывание с изображениями ячеек рабочего листа

Чтобы на одном листе собрать данные из разных документов, можно использовать связь изображения области рабочего листа с другим листом. Это является прекрасным способом для создания легко обновляемых отчетов, выводимых как на экран, так на печать. Связанные изображения ячеек рабочего листа имеют следующие преимущества перед связанными ячейками и диапазонами:

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

- Связанные изображения ячеек можно отформатировать большинством способов, применяемых к текстовым полям, что обеспечивает высокое качество печатных документов

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

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

- Связанные изображения ячеек могут быть ассоциированы с макросами, выполняющимися в момент выбора объекта

Однако у связанных изображений ячеек рабочего листа есть и недостатки:

- Нельзя вводить данные непосредственно в связанные изображения ячеек

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

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

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

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

- Выделите исходный диапазон, рисунок которого вы хотите создать.

- Нажмите кнопку Камера. Указатель мыши примет вид маленького крестика.

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

- Щелчком мыши укажите то место, в котором хотите видеть левый верхний угол рисунка ячеек. На рабочем листе появится изображение ячеек, выделенное черными маркерами.

Чтобы сделать то же самое с помощью команд меню и клавиатуры, выполните следующие действия:

- Выделите исходный диапазон, рисунок которого вы хотите создать.

- Выполните команду Правка, Копировать или нажмите комбинацию клавиш +<0>.

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

- Выделите ячейку, которая должна находиться в верхнем левом углу рисунка.

Нажав и удерживая клавишу , выполните команду Правка, Вставить связь с рисунком.

Пример 16.

На новом рабочем листе сформировать рисунок для построенной в Примере 11 сводной таблицы.

Для этого необходимо:

- выделить исходный диапазон А30:Е42,

- щелкнуть по кнопке Копировать в буфер, перейти к ячейке А1 нового листа,

-удерживая клавишу , выполните команду Правка, Вставить связь с рисунком. Результат представлен на рис.24.

Рис.24. Иллюстрация примера 16

1.2.4 Обновление связанных изображений ячеек

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

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

После двойного щелчка по изображению ячеек, на экране появляется рабочий лист с выделенным исходным диапазоном. Быстро вернуться на рабочий лист с изображением можно с помощью клавиши перехода . Обратите внимание на то, что в списке Перейти к диалогового окна Переход находится адрес ячейки рабочего листа, содержащего связанное изображение ячеек. Если вы, не сделав никаких изменений в диалоговом окне, просто нажмете кнопку ОК, то немедленно вернетесь туда, откуда был вызван этот рабочий лист. Чтобы обновить связи или открыть исходный файл с помощью клавиатуры:

Откройте рабочий лист, содержащий связанные рисунки.

Выберите команду Правка, Связи (рис. 25.).

Рис.25. Вид окна Связи команды Правка

- Выделите исходный рабочий лист в списке Исходный файл. Для того чтобы выделить несколько рабочих листов, воспользуйтесь клавишей . Несколько расположенных рядом рабочих листов можно выделить с помощью клавиши и клавиш управления курсором.

- Нажмите кнопку Обновить, затем нажмите кнопку Закрыть или клавишу .

Чтобы открыть выделенный исходный рабочий лист, нажмите кнопку Открыть.

1.2.5 Изменение связи с изображением ячеек

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

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

Чтобы удалить связанный рисунок ячеек, выделите его и нажмите клавишу или .

1.3 Основные приемы работы со связанными книгами

1.3.1 Открытие связанных рабочих книг

При открытии рабочей книги с внешними ссылками, связи могут обновляться по-разному. Если исходная рабочая книга в этот момент открыта, то связи обновляются автоматически. Если исходная рабочая книга закрыта, на экране появляется диалоговое окно с вопросом об обновляемых связях (рис. 26).

Рис.26.Вид окна с запросом об обновлении связей

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

Если вы уже открыли целевую рабочую книгу и теперь хотите открыть исходные рабочие книги, выполните следующие действия:

1. Активизируйте рабочую страницу, содержащую внешние связи.

2. Выберите команду Правка, Связи. На экране появится диалоговое окно Связи (рис. 27).

Рис.27. Вид окна Связи команды Правка

3. Выделите файлы, которые вы хотите открыть. Для закрытых в данный момент файлов указан полный путь.

4. Нажмите кнопку. Открыть.

Примечание

При выборе команды Правка, Связи должна быть активна целевая книга. Если активна книга, не содержащая внешних ссылок, команда будет недоступна.

1.3.2 Изменение и обновление ссылок

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

Чтобы переустановить потерянные связи или установить связь с другой рабочей книгой:

1. Откройте целевую рабочую книгу.

2. Выберите команду Правка, Связи. Откроется диалоговое окно Связи.

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

4. Нажмите кнопку Изменить. Откроется диалоговое окно Изменить связи (рис. 28).

Рис.28. Вид окна Изменить связи команды Правка

5. При необходимости перейдите на другой диск и в другую папку. Выберите файл исходной рабочей книги.

6. Нажмите кнопку ОК.

7. Если на шаге 3 было выделено несколько файлов, повторите шаги 5 и 6.

Чтобы обновить связи с закрытыми исходными книгами, выберите команду Правка, Связи. В диалоговом окне Связи выделите необходимые файлы и нажмите кнопку Обновить.

Примечание. В некоторых ситуациях исходные данные могут не достигать всех рабочих книг, в которых они используются. Это возможно, если какие-либо книги со связями не открываются продолжительное время и связи не обновляются. Например, книга А является исходной для В, а В — исходной для С, причем так, что данные в книге С зависят от данных в А. Если изменить исходную информацию в А, но связи между А и В не обновить, то информация в книге С останется устаревшей несмотря на ручное или автоматическое обновление данных.

1.3.3 Сохранение рабочих книг со связями

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

По той же причине при изменении имени книги-источника лучше держать открытой и книгу получатель. Если в этот момент целевая книга будет закрыта, придется переустанавливать связи, как показано выше (команда Правка, Связи).

Список использованных источников

1. Рон Персон. Excel 7.0 для WINDOWS 95: пер. с англ.-СПб.:BHV-Санкт-Петербург,1996.

2. Водополова Н.В. Практическое пособие: “Получение данных в EXCEL из внешних данных” / Водополова Н.В., Косинов Г.П., Шибеко В.Н. – Гомель, 2002.

3. Винтер Р., Винтер П. Microsoft Office 97. Т. 2. - СПб.: «BHV - Санкт-Петербург», 1997.

4. Уокербах Джон. Библия пользователя Exsel 97.Пер. с англ. К.- Диалектика, 1997.