Консолидация данных в ms excel реферат

Обновлено: 08.07.2024

Функция "чтения" служит для ознакомления с работой. Разметка, таблицы и картинки документа могут отображаться неверно или не в полном объёме!

Консолидация данных в EXCEL СодержаниеРис.13 Вид окна Специальная вставка 17Рис.17 Вид окна Шаг 3 Мастера сводных таблиц 21Рис.18 Сводная таблица примера 11 22 1. Консолидация данных в EXCEL В Excel данные, используемые для обработки и анализа, могут размещаться в разных ячейках и диапазонах ячеек одного листа, на нескольких листах одной книги и даже в различных книгах. При этом с помощью консолидации и связывания объектов процесс получения и отображения результатов обработки данных можно существенно упростить.

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

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

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

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

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

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

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

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

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

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

Проблемам связывания объектов посвящен раздел 2. 1.1 Общее описание процесса консолидации Процесс консолидации предполагает обязательное указание

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

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

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

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

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

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

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

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

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

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

Консолидируется ровно столько категорий, сколько поместится в выделенном

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

Методы консолидации данных

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

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

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

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

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

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

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

1.
На листе консолидации скопируйте или задайте надписи для данных консолидации.

2. Укажите ячейку, в которую следует поместить данные консолидации.

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

4. Повторите шаги 2 и 3 для каждой ячейки, в которой требуется вывести результаты обработки данных.
Использование трехмерных ссылок для объединения данных
Если исходные листы имеют различные шаблоны и подписи или если требуется создать собственный шаблон, или применить собственные формулы объединения данных, при консолидации следует использовать трехмерные ссылки. Трехмерные ссылки обновляются автоматически при изменении исходных данных.
Добавление данных для объединения
Если все исходные листы имеют одинаковый шаблон, в трехмерных формулах можно использовать диапазон имен листов. Чтобы ввести в объединение новый лист, скопируйте его в диапазон, на который ссылается формула.
Совет.

Консолидация данных по расположению

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

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

3. Выберите из раскрывающегося списка "Функция" функцию, которую следует использовать для обработки данных.

4. Введите в поле "Ссылка" исходную область консолидируемых данных, а после чего нажмите кнопку "Добавить".

5. Повторите шаг 4 для всех консолидируемых исходных областей.

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

Консолидация данных по категориям

1. Выберите из раскрывающегося списка "Функция" функцию, которую следует использовать для обработки данных.

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

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

Задание исходных областей консолидируемых данных

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

¨ Если исходные области и область назначения находятся на одном листе, используйте имена или ссылки на диапазоны.

Изменение итоговой таблицы консолидации данных

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

Добавление области данных в итоговую таблицу

Изменение области данных в итоговой таблице

Создание связей итоговой таблицы с исходными данными

Фильтры

Фильтры являются средством быстрого выделения из списка определенного набора данных, содержащих указанное значение. Чтобы применить фильтр к списку, щелкните любую ячейку в списке, выберите команду Фильтр в меню Данные, а затем выберите команду Автофильтр. Справа от заголовков столбцов появятся стрелки. Чтобы выбрать нужное значение из списка, щелкните стрелку, а затем выберите в списке нужное значение.
После применения фильтра в списке остаются только строки, содержащие указанное значение. В приведенном примере из списка отобраны все строки, относящиеся к продавцу Петрову. В Microsoft Excel фильтрованные объекты выделяются специальным образом. Номера отфильтрованных строк становятся голубыми. Стрелки автофильтра в столбцах, по значению которых отбирались строки, также выделяются голубым.

Отображение строк списка с использованием фильтра

1. Укажите ячейки в фильтруемом списке.

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

4. Выберите значение в списке.

Условия отбора автофильтра

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

Отображение строк списка с использованием условий отбора

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

Виды условий отбора

Последовательности символов

Знаки подстановки

Значения сравнения

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

Фильтрация списка с помощью расширенного фильтра

2. Вставьте скопированные заголовки столбцов в первой пустой строке диапазона условий отбора.

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

4. Укажите ячейку в списке.

Удаление фильтра из списка

Контрольные вопросы и контрольное задание

1. Что такое консолидация данных, для чего она применяется на практике?
2. Каким образом можно создать трехмерную ссылку для консолидации данных?
3. Что такое фильтры и как добавить различные фильтры в таблицу?
4.
Необходимо создать для книги
Excel
, область в которой будет произведена консолидация данных произвольным образом, а потом, полученные данные должны быть отфильтрованы автофильтром по любому признаку.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: