Консолидация данных по физическому расположению
Консолидация данных с использованием ссылок
На третьем шаге Мастера сводных таблиц нажмите кнопку Далее, чтобы подтвердить положение установленных по умолчанию полей (рис.17)
Связывание с ячейками и диапазонами ячеек рабочей книги
Обновление связанных изображений ячеек
Навигация
Связывание с ячейками и диапазонами ячеек рабочей книги
Консолидация данных в Excel
37627
знаков
12
таблиц
27
изображений
1.2.1 Связывание с ячейками и диапазонами ячеек рабочей книги
С точки зрения консолидации данных возможны следующие варианты связывания ячеек и диапазонов ячеек:
- ячейка с ячейкой;
- ячейка связывается с диапазоном ячеек;
- диапазон ячеек с диапазоном ячеек.
Для связывания этих объектов используются формулы, которые вводятся в ячейках назначения, а отображаются в строке формул. Синтаксис формулы связи аналогичен синтаксису внешней ссылки при консолидации данных.
Связь ячейка-ячейка
Пример 12. На рис. 21 изображены рабочие книги, связанные с помощью внешней ссылки. Рабочая книга Квартал 1 является исходной для рабочей книги Годовые отчеты. В строке формул находится формула внешней ссылки, использованная в рабочей книге Годовые отчеты. Эта формула выглядит следующим образом:
|
='[Квартал 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.
|
1.2.2 Связывание ячеек с помощью команд Копировать и Вставить ссылку
Чтобы связать ячейку или диапазон ячеек, можно воспользоваться командой Правка, Специальная вставка, нажав кнопку Вставить ссылку (рис.12).
Пример 15. В примере диапазон В2:В5 рабочей книги Годовые отчеты связан с диапазоном Е2:Е5 рабочей книги Квартал1 (рис. 23).
|
Чтобы установить эту связь выполните следующие действия:
1. Откройте рабочие книги, между которыми вы хотите установить связь.
2. Активизируйте исходную рабочую книгу.
3. Выделите диапазон ячеек, с которым вы хотите установить связь.
4. Выберите команду Правка, Копировать
5. Активизируйте конечную рабочую книгу, в которой нужно установить связь с данными. Выделите левый верхний угол диапазона ячеек, в который вы хотите поместить связь.
В данном примере необходимо выделить ячейку В5 рабочего листа 1996 рабочей книги Годовые отчеты. Не выделяйте весь диапазон, в который нужно поместить связь, т. к. при этом очень просто ошибиться в размерах и получить сообщение об ошибке Область копирования и область вставки имеют различную форму. Достаточно всего-навсего выделить один верхний левый угол диапазона.
6. Выберите команду Правка, Специальная вставка или, щелкнув правой кнопкой мыши по выделенной ячейке, выберите команду Специальная вставка из контекстного меню. На экране появится соответствующее диалоговое окно.
7. Установите переключатель. Вставить в положение Все, a переключатель Операция — в положение Нет (рис.13.)
8. Нажмите кнопку Вставить связь. В ячейках появятся ссылки.
1.2.3 Связывание с изображениями ячеек рабочего листа
Чтобы на одном листе собрать данные из разных документов, можно использовать связь изображения области рабочего листа с другим листом. Это является прекрасным способом для создания легко обновляемых отчетов, выводимых как на экран, так на печать. Связанные изображения ячеек рабочего листа имеют следующие преимущества перед связанными ячейками и диапазонами:
- Связанные изображения ячеек могут быть быстро открыты и обновлены. При двойном щелчке по изображению открывается лист-источник и выделяется соответствующий диапазон. Если файл рабочей книги, содержащий необходимый лист закрыт, то он откроется автоматически. Это позволяет легко вносить изменения в данные и, следовательно, в рисунок
- Связанные изображения ячеек можно отформатировать большинством способов, применяемых к текстовым полям, что обеспечивает высокое качество печатных документов
- Связанные объекты, в отличии от самих ячеек листа, могут быть свободно перемещены, увеличены или уменьшены. Такая гибкость позволяет добиваться высококачественного оформления страниц, объединяющих различные данные
- Связанные изображения ячеек могут быть выведены на печать вместе с зависящими от них диаграммами
- Связанные изображения ячеек могут быть ассоциированы с макросами, выполняющимися в момент выбора объекта
Однако у связанных изображений ячеек рабочего листа есть и недостатки:
- Нельзя вводить данные непосредственно в связанные изображения ячеек
- В вычислениях связанные изображения ячеек использовать нельзя. Для этой цели следует пользоваться связью с самими ячейками и диапазонами рабочего листа.
Процесс связывания изображения ячеек состоит из двух шагов: копирование рисунка ячейки и последующая вставка в нужное место.
Для копирования можно воспользоваться кнопкой Камера , которая находится среди кнопок в категории Дополнительные средства. Чтобы получить доступ к этой кнопке, сделайте правый щелчок по любой существующей панели инструментов, выберите в контекстном меню команду Настройка, затем выделите категорию Дополнительные средства и перетащите кнопку Камера в любое место рабочего листа или на любую открытую панель инструментов.
Чтобы связать изображение ячеек рабочего листа с помощью кнопки Камера, выполните следующие действия:
- Выделите исходный диапазон, рисунок которого вы хотите создать.
- Нажмите кнопку Камера. Указатель мыши примет вид маленького крестика.
- Перейдите на рабочий лист, на котором вы хотите поместить рисунок.
- Щелчком мыши укажите то место, в котором хотите видеть левый верхний угол рисунка ячеек. На рабочем листе появится изображение ячеек, выделенное черными маркерами.
Чтобы сделать то же самое с помощью команд меню и клавиатуры, выполните следующие действия:
- Выделите исходный диапазон, рисунок которого вы хотите создать.
- Выполните команду Правка, Копировать или нажмите комбинацию клавиш <Ctrl>+<0>.
- Перейдите на рабочий лист, на котором вы хотите разместить рисунок.
- Выделите ячейку, которая должна находиться в верхнем левом углу рисунка.
Нажав и удерживая клавишу <Shift>, выполните команду Правка, Вставить связь с рисунком.
Пример 16.
На новом рабочем листе сформировать рисунок для построенной в Примере 11 сводной таблицы.
Для этого необходимо:
- выделить исходный диапазон А30:Е42,
- щелкнуть по кнопке Копировать в буфер, перейти к ячейке А1 нового листа,
-удерживая клавишу <Shift>, выполните команду Правка, Вставить связь с рисунком. Результат представлен на рис.24.
|
Похожие работы
нного фильтра------------------------------- 13 Удаление фильтра из списка------------------------------------------------------------------- 14 Контрольные вопросы и контрольное задание---------------- 15 Консолидация данных Методы консолидации данных В табличном редакторе Microsoft Excel предусмотрено несколько способов консолидации: · С помощью трехмерных ссылок, что ...
... рис. 18 ☺ Самостоятельно поработайте с диалоговым окном Формат ячеек и изучите возможности каждой из имеющихся закладок. Средства автоматического обобщения и анализа данных электронной таблицы Excel предоставляет разнообразные способы для автоматического обобщения и анализа данных: 1. Автоматические вычисления; 2. Средства для работы с базами данных и со списками, позволяющие ...
... сводной таблицы установить переключатель Обновлять внешние данные при выборе каждого элемента. Выделение элементов сводной таблицы При работе со сводными таблицами в Microsoft Excel 97 мы можем использовать новый режим выделения , который называется структурным выделением. Если этот режим включен , то при выделении элементов в ...
... элемента и вычисляемого поля. Использование общих и промежуточных итогов в сводной таблице Сводную таблицу можно настроить на выполнение вычислений с помощью итоговой функции (итоговая функция - это тип вычисления, производимого Microsoft Excel при объединении данных в сводной таблице или при вычислении итогов в списках и базах данных. Примерами итоговых функций являются «Сумма», «Кол-во ...
0 комментариев