50348 (Консолидация данных в Excel), страница 3

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

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

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

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

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

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

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

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

Большое количество связей может замедлить процесс открытия целевой рабочей книги. Кроме того, размеры рабочей книги с большим количеством внешних связей могут быть очень большими, так как 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 и завершить ввод формулы, нажав клавишу .

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример 16.

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

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

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

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

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

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

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

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

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

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

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

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

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

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