КОНСОЛІДАЦІЯ ДАНИХ І ФІЛЬТРИ У MicroSoft 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 для кожного осередку, в якій потрібно вивести результати обробки даних.

Використання тривимірних посилань для об'єднання даних

Якщо вихідні листи мають різні шаблони і підписи або якщо потрібностворити власний шаблон, або застосувати власні формули об'єднанняданих, при консолідації слід використовувати тривимірні посилання. Тривимірніпосилання оновлюються автоматично при зміні вихідних даних.

Додавання даних для об'єднання

Якщо всі вихідні листи мають однаковий шаблон, в тривимірних формулахможна використовувати діапазон імен аркушів. Щоб ввести в об'єднання новийлист, скопіюйте його в діапазон, на який посилається формула.

Рада.
Щоб ввести посилання, не використовуючи клавіші на клавіатурі, введіть формулу дотого місця, де потрібно вставити посилання, а потім вкажіть на аркуші потрібнуклітинку. Якщо осередок знаходиться на іншому аркуші, перейдіть на цей лист івкажіть потрібну комірку.

Консолідація даних за розташуванням

Для консолідації даних по їх розташуванню необхідно виконати наступнікроки:

1. Вкажіть верхню ліву комірку області призначення консолідованих даних.
2. Виберіть команду "Консолідація" в меню "Дані".
3. Виберіть зі списку, що розкривається "Функція" функцію, яку слід використовувати для обробки даних.
4. Введіть у поле "Посилання" вихідну область консолідованих даних, а після чого натисніть кнопку "Додати".
5. Повторіть крок 4 для всіх консолідованих вихідних областей.
6. Щоб автоматично оновлювати підсумкову таблицю при зміні джерел даних, встановіть прапорець "Створювати зв'язку з вихідними даними".

Зв'язки не можна використовувати, якщо вихідна область і область призначеннязнаходяться на одному аркуші. Після встановлення зв'язків не можна додавати новівихідні області і змінювати вихідні області, вже беруть участь уконсолідації.

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

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

Для консолідації даних по категоріях необхідно для початку вказати верхнюліву комірку кінцевої області консолідованих даних а після цього вибратикоманду "Консолідація" в меню "Дані" після чого необхідно дотримуватисянаступних пунктів:
1. Виберіть зі списку, що розкривається "Функція" функцію, яку слід використовувати для обробки даних.
2. Введіть вихідну область консолідованих даних у полі "Посилання".

Переконайтеся, що вихідна область має заголовок. Після цього потрібно натиснути кнопку "Додати".

3. У наборі прапорців "Використовувати як імена" встановіть прапорці, що відповідають розташуванню у вихідній області заголовків: у верхній частині, в лівому стовпчику або у верхній частині і в лівому стовпчику одночасно.
4. Щоб автоматично оновлювати підсумкову таблицю при зміні джерел даних, встановіть прапорець "Створювати зв'язку з вихідними даними".

Зв'язки не можна використовувати, якщо вихідна область і область призначеннязнаходяться на одному аркуші. Після встановлення зв'язків не можна додавати новівихідні області і змінювати вихідні області, які вже входять в консолідацію.

Примітка:
Якщо мітки в одній з вихідних областей не збігаються з мітками в іншихвихідних областях, то при консолідації даних для них будуть створеніокремі рядки або стовпці.

Завдання вихідних областей консолідованих даних

Вихідні області оброблюваних даних задаються або тривимірними формулами,або в полі Посилання діалоговому вікні Консолідація. Джерела даних можутьперебувати на тому ж листі, що і таблиця консолідації, на інших сторінкахтієї ж книги, в інших книгах або у файлах Lotus 1-2-3.
При описі вихідних областей дотримуйтесь наступних рекомендацій.

o Для полегшення роботи з вихідними областями задайте ім'я кожного діапазону і використовуйте в полі Посилання тільки імена. o Якщо вихідні області і область призначення знаходяться на одному аркуші, використовуйте імена або посилання на діапазони. o Якщо вихідні області і область призначення знаходяться на різних сторінках, використовуйте ім'я листа і ім'я або посилання на діапазон. Наприклад, щоб включити діапазон із заголовком «Бюджет», що знаходиться в книзі на аркуші «o Бухгалтерія», введіть Бухгалтерія! Бюджет.

o Якщо вихідні області і область призначення знаходяться в різних книгах, використовуйте ім'я книги, ім'я аркуша, а потім - ім'я або посилання на діапазон.

Наприклад, щоб включити діапазон «Продаж» з листа «Далекий Схід» в книзі «1996», що знаходиться у цій же папці, введіть:

o '[1996.xls] Далекий Схід'! Продажі

o Якщо вихідні області і область призначення знаходяться в різних книгах різних каталогів диска, використовуйте повний шлях до файлу книги, назва книги, ім'я листа, а потім - ім'я або посилання на діапазон. Наприклад, щоб включити діапазон «Оборот» листа «Лютий» в книзі «Відділ продажу», яка знаходиться в папці «Бюджет», введіть:

'[C: БюджетОтдел продаж.xls] лютий'! Оборот

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

Рада.
Щоб задати опис джерела даних, не натискаючи клавіші клавіатури,вкажіть полі Посилання, а потім виділіть вихідну область. Щоб визначитивихідну область в іншій книзі, натисніть кнопку Огляд. Щоб прибратидіалогове вікно Консолідація на час вибору вихідної області, натиснітькнопку згортання діалогового вікна

Зміна підсумковій таблиці консолідації даних

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

кінцева область

Обраний для зберігання консолідованих даних діапазон комірок. Кінцевийдіапазон може перебувати на тому ж листі, що і вихідні дані, а такожна іншому аркуші. Лист може містити тільки один кінцевий діапазонконсолідації.

Примітка.
Якщо консолідація даних проводилася за допомогою тривимірних посилань, тозміна підсумкової таблиці здійснюється за допомогою зміни формул
(наприклад, для долучення вихідних областей) або видалення листа зі спискулистів, які використовуються у формулах.

Додавання області даних в підсумкову таблицю

Якщо при консолідації не створювалися зв'язку з вихідними даними, можнадодати нові вихідні області і перерахувати дані в області призначення.
Якщо були створені зв'язку з вихідними областями, видаліть підсумкову таблицю, апотім видаліть структуру, якщо вона існує на аркуші. Щоб додатиобласті даних в підсумкову таблицю вкажіть верхню ліву комірку існуючоїпідсумковій таблиці. Далі виберіть команду «Консолідація» в меню «Дані атак само вкажіть полі «Посилання». Якщо лист, який містить нове джерело даних,є поточною, виділіть на ньому вихідну область.
Після всього цього натисніть кнопку «Додати». А щоб перерахувати підсумковутаблицю, натисніть кнопку "OK".
Щоб зберегти новий набір вихідних областей без перерахунку підсумковоїтаблиці, натисніть кнопку «Закрити».

Зміна області даних у підсумковій таблиці

Якщо при консолідації не створювалися зв'язку з вихідними даними, можназмінити вихідні області і перерахувати дані в області призначення. Якщозв'язку з вихідними областями були створені, видаліть підсумкову таблицю, а потімвидаліть структуру.

1. Вкажіть ліву верхню клітинку існуючої підсумковій таблиці.
2. Виберіть команду «Консолідація» в меню «Дані».
3. У списку «Список» діапазонів вкажіть вихідну область, яку слід змінити.
4. Внесіть зміни у вибрану область в полі «Посилання».
5. Натисніть кнопку Додати.
6. Якщо стара посилання не потрібна, вкажіть її в списку Список діапазонів, а потім натисніть кнопку «Видалити».
7. Щоб перерахувати підсумкову таблицю, натисніть кнопку "OK".

Щоб зберегти новий набір вихідних областей без перерахунку підсумковоїтаблиці, натисніть кнопку «Закрити».

Створення зв'язків підсумковій таблиці з вихідними даними

Якщо на аркуші, що містить область призначення, вже існує структура абоцей лист використовувався раніше для консолідації даних зі зв'язками, видалітьструктуру перед створенням нових зв'язків. Після цього вкажіть верхню лівукомірку існуючої підсумковій таблиці. Потім виберіть команду «Консолідація»в меню «Дані». У вікні встановіть прапорець «Створювати зв'язку звихідними даними ».

Фільтри

Фільтри є засобом швидкого виділення зі списку певногонабору даних, що містять вказане значення. Щоб застосувати фільтр досписку, клацніть будь-яку клітинку у списку, виберіть команду Фільтр в меню
Дані, а потім виберіть команду Автофільтр. Праворуч від заголовків стовпцівз'являться стрілки. Щоб вибрати потрібне значення зі списку, клацнітьстрілку, а потім виберіть у списку потрібне значення.

Після застосування фільтра в списку залишаються лише рядки, що містятьвказане значення. У наведеному прикладі зі списку відібрані всі рядки,що відносяться до продавця Петрову. У Microsoft Excel Фільтровані об'єктивиділяються спеціальним чином. Номери відфільтрованих строк стаютьблакитними. Стрілки автофільтру у стовпцях, за значенням яких відбиралисярядки, також виділяються блакитним.

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

Відображення рядків у списку з використанням фільтра

Для початку необхідно зазначити, що фільтри можуть бути використані тількидля одного списку на листі. Що ж до відображення рядків у списку звикористанням фільтру, то для цього необхідно виконати наступне:
1. Вкажіть клітинки в фільтрованої списку.
2. Виберіть пункт Фільтр у меню «Дані», а потім - команду «Автофільтр».
3. Щоб відфільтрувати рядки, що містять певне значення, натисніть кнопку зі стрілкою в стовпці, в якому містяться шукані дані.
4. Виберіть значення у списку.
5. Повторіть кроки 3 і 4, щоб ввести додаткові обмеження для значень в інших стовпцях.

Щоб відфільтрувати список по двох значень в одному стовпці або застосувативідмінні від рівності оператори порівняння, натисніть кнопку зі стрілкою, апотім виберіть пункт «Умова».

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

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

Умови відбору автофільтру

Примітка:
Умови Порожні і непусті можна використовувати, тільки якщо в стовпцімістяться порожні клітинки.

Відображення рядків у списку з використанням умов відбору

Щоб відібрати рядки із списку з використанням одного або двох умоввідбору для одного стовпця, натисніть кнопку зі стрілкою в стовпці, в якомузнаходяться порівнювані дані, а потім виберіть пункт Умова.

o Щоб відобразити рядки, що задовольняють одному умовою відбору, виберіть необхідний оператор порівняння в першому полі під написом Показати тільки ті рядки, значення яких і значення порівняння праворуч від нього. o Щоб відобразити рядки, що задовольняють одночасно двом умовам відбору, введіть оператор і значення порівняння в першій групі полів, натисніть кнопку «І», а потім введіть другу оператор і значення порівняння в другій групі полів.

o Щоб відобразити рядка, що задовольняють одному з двох умов відбору, введіть оператор і значення порівняння в першій групі полів, натисніть кнопку «Або», а потім введіть другу оператор і значення порівняння в другій групі полів.

Види умов відбору

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

Послідовності символів

Щоб відібрати рядки, що містять комірки з заданим значенням, введітьнеобхідні число, дату, текстову чи логічну константу в клітинку нижчезаголовка стовпця діапазону умов. Наприклад, щоб відібрати рядки, вяких індекс відділення зв'язку дорівнює 115522, введіть у діапазоні умовчисло 115522 нижче заголовка «Індекс відділення зв'язку».
При використанні текстової константи в якості умови відбору будутьвідібрані всі рядки з комірками, що містять текст, що починається із заданоюпослідовності символів. Наприклад,при введенні умови «Анг» будутьвідібрані рядки з комірками, що містять слова Ангара, Англія і Ангола.
Щоб отримати точну відповідність відібраних значень заданому зразку,наприклад текст, слід ввести умову:

=''= текст''

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

Щоб відібрати рядки з комірками, що містять послідовність символів,в деяких позиціях якої можуть стояти довільні символи, слідвикористовувати знаки підстановки. Знак підстановки еквівалентний одномусимволу або довільній послідовності символів.

Значення порівняння

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

Примітка.
Малі та великі літери при фільтрації даних не розрізняються.

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

Щоб відфільтрувати список за допомогою розширеного фільтра, стовпці спискуповинні мати заголовки. На аркуші також повинне бути не менш трьох порожніхрядків вище списку. Ці рядки будуть використані як діапазонумов відбору. Після цього:

1. Скопіюйте зі списку заголовки фільтровану стовпців.
2. Вставте скопійовані заголовки стовпців у першу порожній рядок діапазону умов відбору.
3. Введіть у рядки під заголовками умов необхідні критерії відбору.

Переконайтеся, що між значеннями умов і списком знаходиться як мінімум один порожній рядок.
4. Вкажіть комірку у списку.
5. Виберіть пункт «Фільтр» у меню «Дані», а потім - команду «Розширений фільтр».
6. Щоб показати результат фільтрації, сховавши непотрібні рядки, встановіть перемикач «Обробка» в положення «Фільтрувати список на місці».
7. Введіть у полі «Діапазон критеріїв» посилання на діапазон умов відбору, що включає заголовки стовпців.

Щоб скопіювати відфільтровані рядки в іншу область листа, встановітьперемикач «Обробка» в положення «Скопіювати результати в іншемісце », перейдіть у полі" Помістити результат в діапазон », а потім вкажітьверхню ліву комірку області вставки.

Щоб прибрати діалогове вікно Розширений фільтр на час виділеннядіапазону умов відбору, натисніть кнопку згортання діалогового вікна:

Рада.
Якщо на листі існує діапазон з ім'ям «Критерії», то в полі «Діапазонумов »автоматично з'явиться посилання на цей діапазон.

Видалення фільтра зі списку

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

«Дані», а потім - команду Автофільтр.

Контрольні питання і контрольне завдання

< br>1. Що таке консолідація даних, для чого вона застосовується на практиці?

2. Яким чином можна створити тривимірну посилання для консолідації даних?

3. Що таке фільтри і як додати різні фільтри в таблицю?

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

-----------------------< br>