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

РЕФЕРАТ

ТЕМА: "КОНСОЛИДАЦИЯ ДАННЫХ И ФИЛЬТРЫ В MicroSoft Excel "

 

                                                Выполнила: Студентка 2-го курса

                                                                Группы НЭК-99

                                                                Ландик Е.

                                               

                                                Проверил: ____________________

 

 

Краснодар 2001

ОГЛАВЛЕНИЕ

ОГЛАВЛЕНИЕ------------------------------------------------------------------------------------- 2

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

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

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

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

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

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

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

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

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

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

Фильтры------------------------------------------------------------------------------------------- 9

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

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

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

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

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

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

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

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

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

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

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

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

 

В табличном редакторе Microsoft Excel предусмотрено несколько способов консолидации:

 

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

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

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

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

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

 

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

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

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

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

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

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

 

Использование трехмерных ссылок для объединения данных

 

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

 

 

Добавление данных для объединения

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

 

Совет.  

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

 

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

 

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

При описании исходных областей следуйте следующим рекомендациям.

 

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

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

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

¨ Бухгалтерия», введите Бухгалтерия!Бюджет.

 

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

 

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

 

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

 

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

 

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

 

Совет.  

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

 

 

Конечная область

 

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

 

Примечание.

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

 

Фильтры

 

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

 

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

 

 

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

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

 

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

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

2. Выберите пункт Фильтр в меню «Данные», а затем - команду «Автофильтр».

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

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

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

 

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

 

Примечания.

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

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

 

Список

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

 

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

 

Все строки списка	Все
Заданное число строк с максимальными или минимальными значениями ячеек текущего столбца (например, можно отобразить 10% строк, имеющих максимальные значения суммы покупки	Первые 10
Строки, удовлетворяющие двум условиям или одному условию с оператором сравнения, отличным от И (оператор по умолчанию)	Условие...
Все строки, имеющие пустые ячейки в текущем столбце	Пустые
Все строки, имеющие непустые ячейки в текущем столбце	Непустые

Примечание:

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

 

 

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

 

При настройке автофильтра или вводе условий отбора в диапазоне условий расширенного фильтра можно использовать перечисленные ниже элементы условий.

 

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

 

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

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

 

=''=текст''

 

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

 

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

 

        

Чтобы найти	Используйте знак	Пример
Любой символ в той же позиции, что и знак вопроса	«?» (знак вопроса)	«д?м» задает поиск "дым" и "дом"
Любую последовательность символов в той же позиции, что и звездочка	«*» (звездочка)	«*ино» задает поиск "Люблино" и "Выхино"
Знак вопроса, звездочка или тильда	~ (тильда), за которой следует «?», «*» или «~»	«Что~?» Задает поиск "Что?"

 

 

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

 

Чтобы отобрать строки с ячейками, имеющими значения в заданных границах, следует использовать оператор сравнения. Условие отбора с оператором сравнения следует ввести в ячейку ниже заголовка столбца в диапазоне условий. Например, чтобы отобрать строки, имеющие значения ячеек большие или равные 1000, введите условие отбора >=1000 ниже заголовка «Количество».

 

Примечание.

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

 

Совет.

 Если на листе существует диапазон с именем «Критерии», то в поле «Диапазон условий» автоматически появится ссылка на этот диапазон.

 

 

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

 

· Чтобы удалить фильтр для одного столбца списка, нажмите на кнопку со стрелкой, а затем выберите из развернувшегося списка «Все».

· Чтобы удалить фильтры для всех столбцов списка, выберите пункт «Фильтр» в меню «Данные», а затем - команду «Показать все».

· Чтобы удалить автофильтр из списка, выберите пункт «Фильтр» в меню «Данные», а затем - команду Автофильтр.

 

 

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

 

1. Что такое консолидация данных, для чего она применяется на практике?

 

2. Каким образом можно создать трехмерную ссылку для консолидации данных?

 

3. Что такое фильтры  и как добавить различные фильтры в таблицу?

 

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

РЕФЕРАТ

ТЕМА: "КОНСОЛИДАЦИЯ ДАННЫХ И ФИЛЬТРЫ В MicroSoft Excel "

 

                                                Выполнила: Студентка 2-го курса

                                                                Группы НЭК-99

                                                                Ландик Е.

                                               

                                                Проверил: ____________________

 

 

Краснодар 2001

ОГЛАВЛЕНИЕ

ОГЛАВЛЕНИЕ------------------------------------------------------------------------------------- 2

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

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

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

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

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

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

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

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

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

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

Фильтры------------------------------------------------------------------------------------------- 9

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

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

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

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

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

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

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

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

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

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

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

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

 

В табличном редакторе Microsoft Excel предусмотрено несколько способов консолидации:

 

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

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

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

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

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

 

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

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

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

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

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

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