 |
Упражнение 43. Консолидация данных без создания связей с исходными данными. Абсолютные ссылки
|
|
|
|
1. Откройте рабочий лист Итог-кражи. Выделите область назначения - диапазон ячеек В4:С11.
2. Выполните команды меню Д анные/ К онсолидация…. На экране появится диалоговое окно Консолидация (рисунок 3.10).
| Кнопка свертывания окна |
| Открывает список итоговых функций |
Рисунок 3.10. Диалоговое окно консолидации данных
3. Щелкните на кнопке [▼] в поле Ф ункция. Откроется список итоговых функций. Выберите функцию Сумма.
4. Для определения ссылок на исходные диапазоны сначала сверните окно консолидации, щелкнув на кнопке свертывания окна.
5. Затем перейдите на лист Кражи-2001 и выделите диапазон ячеек В4:С11.
6. Откройте окно Консолидация, щелкнув на кнопке развертывания окна.
7. В окне Консолидация нажмите кнопку Доб а вить. В поле С писок диапазонов сформируется ссылка ‘Кражи-2001’!$В$4:$С$11. Здесь ‘Кражи-2001’ – имя исходного диапазона, а $В$4 и $С$11 – абсолютные ссылки. Восклицательный знак (!) разделяетимена диапазона и ссылок.
Внимание: Абсолютные ссылки в отличие от относительных ссылок (см. 3.3.6) не изменяются при выполнении операций копирования или перемещения. Признаком абсолютной ссылки является знак доллара ($), стоящий перед именем столбца и номером строки ссылки. Например, в абсолютной ссылке $В$4 передименем столбца В и передномером строки 4 напечатан знак доллара ($). В ссылке $А7 знак доллара стоит только перед именем столбца, а в ссылке G$91 знак доллара стоит только перед номером строки.
7. Сверните окно Консолидация. Перейдите на лист Кражи-2002. Второй исходный диапазон ячеек В4:С11 будет автоматически выделен при консолидации по расположению. Откройте окно Консолидация и нажмите кнопку Доб а вить. В поле С писок диапазонов сформируется вторая ссылка ‘Кражи-2001’!$В$4:$С$11.
|
|
|
8. Проверьте, снят ли флажок Создавать связи с ис х одными данными. Повторите все действия пункта 8 для третьего листа Кражи-2003, кроме последнего: не нажимайте кнопку Доб а вить, а нажмите кнопку ОК в окне Консолидация. На экране появится лист Итог-кражи (рисунок 3.11)с результатами консолидации в диапазоне ячеек В4:С11.
Рисунок 3.11. Результат консолидации данных трех таблиц
Замечание: Если по какой-то причине ссылка на исходный диапазон определена неверно, то необходимо удалить ошибочную ссылку и ввести новую, правильную ссылку. Для удаления ошибочной ссылки выделите ее и нажмите клавишу У далить в окне Консолидация.
Консолидация с установлением связей с исходными данными
Основные понятия
Если в исходных таблицах изменить данные, то при консолидации без создания связей данные в итоговой таблице не изменятся, и консолидацию с измененными данными необходимо повторить. Способ консолидации, предлагаемый ниже, устанавливает динамические связи с исходными данными, и тогда при внесении изменений в исходные таблицы Excel автоматически пересчитывает данные в итоговой таблице. При этом формируется структурированная итоговая таблица, в которой достаточно просто можно скрывать или отображать данные. Благодаря созданию структуры в итоговой таблице значительно улучшается контроль данных. Такую таблицу удобно использовать при создании итоговых отчетов.
§ Структурированная таблица – это таблица, состоящая из отдельных взаимосвязанных групп данных.
§ Группы данных в структурированной таблице располагаются на разных уровнях.
§ Уровень структуры определяет место той или иной группы данных в таблице.
§ Уровни структурированных данных могут быть скрыты или отображены на экране все одновременно, по отдельности или группами с помощью символов структуры.
|
|
|
§ Символы структуры – это специальные символы, с помощью которых осуществляется работа по скрытию и отображению данных структурированной таблицы.
§ К символам структуры относятся кнопки с цифрами, обозначающими номера уровней структуры, и знаки плюс (+) и минус (-), с помощью которых скрываются или отображаются группы данных.
§ Структура может содержать до восьми вертикальных и восьми горизонтальных уровней.
В следующих упражнениях сформируем структурированную таблицу и научимся управлять отображением структурированных данных с помощью символов структуры, убирать отображение символов структуры с экрана, удалять структуру документа. Но сначала создадим таблицу на новом рабочем листе для определения в ней области назначения консолидации.
Упражнение 44. Консолидация данных с установлением связей с исходными данными
1. Добавьте еще один лист в Книгу2 и переименуйте его в Связи.
2. Скопируйте таблицу с листа Итог-кражи на лист Связи в диапазон ячеек А2:С11.
3. Измените с помощью редактирования ячейки заголовок таблицы на следующий: Таблица 5. Итоги со связями: 2001-2003г.
4. Очистите содержимое области назначения (диапазон ячеек В4:С11) для консолидации данных с установлением связей. В результате действий 1-4
на листе Связи сформирована таблица для выполнения консолидации.
5. Выполните команды меню Д анные/ К онсолидация…. На экране появится диалоговое окно Консолидация (рисунок 3.10).
6. Установите флажок Создавать связи с исходными данными.
7. Выполните консолидацию точно так же, как и в упражнении 43. На рабочем листе Связи сформируется структурированная итоговая таблица, в которой вверху и слева от рабочего листа отображаются символы структуры – кнопки с цифрами 1 и 2 и знаки + (плюс) и – (минус) (см. 3.4.5.).
Совет: При консолидации данных по расположению необязательно выделять каждый исходный диапазон – исходный диапазон выделяется автоматически на всех открываемых листах. Кроме того, необязательно каждый раз сворачивать окно Консолидация для выделения исходных областей. Достаточно (при правильной подготовке данных) один раз открыть окно Консолидация ивыполнить команду Доб а вить нужное число раз, в конце процедуры – нажать ОК.
|
|
|
