Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»


Скачати 261.67 Kb.
НазваЛекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Сторінка4/5
Дата25.03.2013
Розмір261.67 Kb.
ТипЛекція
bibl.com.ua > Інформатика > Лекція
1   2   3   4   5

Фільтрація даних



Фільтри, вбудовані в Excel, спрощують процес уведення та видалення записів зі списку, а також процес пошуку інформації. Фільтрація даних у списку дозволяє відібрати та відобразити тільки ті записи, що задовольняють заданим критеріям (умовам). Excel надає дві команди для фільтрації: «Автофильтр» — для простих критеріїв, та «Расширенный фильтр» — для більш складних критеріїв, із можливістю копіювання відібраних даних в інше місце робочої книги.

Автофільтр



Можливостями автофільтра можна скористатися, помістивши покажчик клітинки усередині списку та виконавши команду «Данные\Фильтр\Автофильтр». Справа від кожного заголовка стовпчика з’явиться кнопка зі стрілкою униз, натиснувши на яку, можна відкрити список значень даного поля. Після вибору зі списку потрібного елемента на екрані будуть відображені тільки ті записи, що відповідають заданому фільтру. При цьому у рядку стану з’явиться повідомлення про те, скільки записів, що відповідають заданому критерію, знайдено у списку: .

Запропоновані для вибору списки значень полів можуть бути достатньо довгими. Для швидкого переходу до певного елемента списку введіть початкову літеру. Елемент «(Все)» дозволяє зняти обмеження по даному полю. За допомогою елемента «(Первые 10…)» користувач може відібрати певну кількість найбільших або найменших значень розглянутого списку. При виборі вказаного елемента відкривається наступне вікно діалогу «Наложение условия по списку».

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

Можна задавати і більш складні критерії, ніж просто порівняння на рівність. Застосовуючи автофільтр користувача, можна з’єднати за допомогою логічних операторів окремі умови відбору записів по одному полю (з будь-якими знаками порівняння). Для визначення вказаного автофільтра виберіть у списку, що розкривається, елемент «(Условие…)». У вікні діалогу «Пользовательский автофильтр» зазначте окремі критерії для одного поля та з’єднайте їх за допомогою логічного оператора.

При визначенні критеріїв по текстових полях можна використовувати не тільки оператори порівняння, але і символи шаблона: «*» — для представлення будь-якої послідовності символів та «?» — для представлення будь-якого окремого символу.

Відбір записів можна продовжити, задаючи критерії в інших стовпчиках (по іншим полям). Кожний доданий критерій зв’язується з попереднім логічним оператором «И».

Якщо перед виконанням команди «Данные\Фильтр\Автофильтр» виділити частину списку, то кнопки зі списками, що розкриваються, будуть додані тільки по відповідних полях (включених до області виділення).

Щоб знову відобразити на екрані всі записи списку, виконайте команду «Данные\Фильтр\Отобразить все». Для деактивації автофільтра повторно скористайтеся командою «Данные\Фильтр\Автофильтр».

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

Команди «Копировать» та «Вставить» із меню «Правка» або їхні аналоги у контекстному меню, а також відповідні кнопки панелі інструментів «Стандартная» дають можливість копіювати відфільтровані записи так само, як будь-які інші дані, на нове місце поточного або іншого аркуша робочої книги. Більш розвинені можливості відбору та копіювання даних надає команда «Расширенный фильтр».

Розширений фільтр



При використанні розширеного фільтра критерії відбору задаються безпосередньо у робочому аркуші (діапазон критеріїв). Користувач завжди може контролювати та швидко змінювати критерії, що застосовуються. Але головне, можна задавати як завгодно складні критерії, що зв’язують елементарні умови по одному полю або різноманітним полям логічними операторами «И» та «ИЛИ». При копіюванні можна не тільки враховувати складні критерії, але і відбирати окремі потрібні поля з відфільтрованих записів.

Діапазон критеріїв (інтервал критеріїв) можна оформити у будь-якому вільному місці робочого аркуша. Обираючи місце під діапазон, виходять із того, щоб він не заважав, наприклад, поповненню списку.

У діапазоні критеріїв можна вводити та сполучати два типи критеріїв:

  • Порівняльні критерії — порівнюють вміст полів за заданою умовою (аналогічно застосуванню автофільтра).

  • Обчислювальні критерії — дозволяють записувати формули, що містять бібліотечні функції, та перевіряти складні умови. Наприклад, використовуючи обчислювальні критерії, легко можна виділити у списку тільки тих робітників, у яких зарплата не менше ніж на 25% перевищує середню зарплату.

Діапазон критеріїв повинен складатися не менше ніж із двох рядків. У першому рядку для порівняльних критеріїв необхідно зазначити імена полів. Причому можна ввести тільки ті імена полів, для яких вказуються обмеження. Враховуючи необхідність точного збігу імен полів, їх простіше усього одержати шляхом копіювання. У рядку (рядках), розташованих безпосередньо під рядком з іменами полів, потрібно зазначити обмеження, що мають традиційний вигляд (наприклад: >=5000, або <=30) та інтерпретуються наступним чином:

  • Обмеження розташовані в одному рядку зв’язуються логічним оператором «И».

  • Обмеження у різних рядках зв’язуються логічним оператором «ИЛИ».

  • Ім’я поля може повторюватися у рядку з іменами полів.

Для застосування створеного діапазону критеріїв до списку:

  • Розташуйте покажчик клітинки усередині списку.

  • Виконайте команду «Данные\Фильтр\Расширенный фильтр…».

  • У вікні діалогу «Расширенный фильтр» розташуйте курсор уведення у полі «Диапазон условий» та виділіть відповідний діапазон у робочому аркуші (у разі потреби перемістіть вікно діалогу).

  • Переконайтеся, що встановлений перемикач «фильтровать список на месте» та натисніть кнопку «OK».

Якщо у результаті відбору не повинні бути відображені однакові записи, у вікні діалогу «Расширенный фильтр» потрібно встановити прапорець параметра «Только уникальные записи».

Щоб після фільтрації задати відображення у робочому аркуші усіх записів списку, виконайте команду «Данные\Фильтр\Отобразить все».

При створенні діапазону критеріїв у вікні діалогу «Расширенный фильтр» йому призначається ім’я «Критерии», яке може використовуватися для швидкого переходу по робочому аркушу. Якщо при опрацюванні списку регулярно використовується декілька діапазонів критеріїв, то сформуйте їх в окремих областях, призначте їм імена та підставляйте їх імена замість адрес у вікні «Расширенный фильтр». Це дозволить спростити переключення з одного фільтра на інший. При виконанні команди «Расширенный фильтр» Excel завжди переглядає увесь список, а не тільки його відфільтровану частину. Тому при зміні критеріїв фільтра не потрібно виконувати команду «Данные\Фильтр\Отобразить все».

Інтерпретація обмежень по текстовим полям має деякі особливості. Обробка таких критеріїв виконується в Excel за такими правилами:

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

  • За умовами >, >=, <, <= будуть знайдені значення, що розташовуються за абеткою від заданого значення, відповідно, після, або починаючи з, або до, або до, включаючи зазначене.

  • По обмеженню =”=текст” відбираються значення, точно збіжні з заданим виразом «текст». Наприклад, щоб знайти записи з прізвищем Мороз, варто задати =”=Мороз”. Якщо задати не формулу, а просто — Мороз, то можливо будуть виділені записи з прізвищами Мороз, Морозов, Морозовський і т. д.

  • Символи шаблона (* та ?) опрацьовуються так само, як в автофільтрі.

  • Щоб символ шаблона «?» або «*» включити у критерій фільтра як звичайний символ, поставте перед ним тільду «~».

Обчислювальні критерії дозволяють виконувати більш складні операції, ніж просте порівняння значення поля з зазначеною константою. Наступні правила допоможуть уникнути помилок:

  • Заголовок стовпчика над обчислювальним критерієм (перший рядок діапазону критеріїв) не повинний збігатися з іменем якогось поля в аналізованому списку. Він може бути порожнім або містити будь-який інший текст.

  • Формула повинна повертати значення логічного типу.

  • Задавати посилання на поле списку можна шляхом указівки відносної адреси першої клітинки поля.

  • Посилання на клітинки поза списком повинні бути абсолютними.

  • Посилання на клітинки усередині списку повинні бути відносними.

  • Посилання на постійний діапазон клітинок усередині списку потрібно задавати в абсолютному вигляді.

Наприклад, обчислюємий критерій може мати вигляд:

  • =J3/H3<10;

  • =C8–D8<18*365 — таким чином, маючи інформацію про дату прийому на навчання та дату народження, можна відібрати тих, хто поступив у віці до 18 років;

  • =F3>=СРЗНАЧ($F$3:$F$325)*1,25 — таким чином, маючи інформацію про зарплату, можна відібрати тих, чия зарплата не менш ніж на 25% перевищує середню.

У наведеному вище вікні діалогу «Расширенный фильтр» можна задати режим копіювання відфільтрованих записів в інше місце робочого аркуша. Для цього потрібно встановити перемикач у полі «Обработка» у положення «скопировать результат в другое место» та у полі уведення «Поместить результат в диапазон» зазначити потрібний діапазон (цільову область). Простіше за все задати діапазон для копіювання, клацнувши мишею початкову клітинку цього діапазону. Переконайтеся, що справа та нижче від обраної клітинки є достатньо вільного місця. Заголовки стовпчиків і всі записи списку, що задовольняють критерію, будуть поміщені у зазначений діапазон. Якщо діапазон для копіювання задати повністю, відфільтровані записи будуть копіюватися до заповнення усіх клітинок діапазону, після чого буде виведене вікно запиту з відповідною вказівкою.

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

Як діапазон розташування результату, можна задати діапазон тільки поточного робочого аркуша. Задати посилання на діапазон іншого робочого аркуша у полі уведення «Поместить результат в диапазон» не можна. Проте поточний робочий аркуш не обов’язково повинний збігатися з аркушом розташування списку, що дозволяє одержати копію відібраних даних на іншому аркуші, якщо почати виконання команди «Расширенный фильтр» з “цільового” аркуша.

Діапазону, що задається у полі «Поместить результат в диапазон», призначається ім’я «Извлечь», яке можна використовувати для переходів у аркуші.

У процесі роботи з відфільтрованими списками потрібно враховувати ряд особливостей:

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

  • При сортуванні враховуються тільки відображені записи.

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

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

Koнcoлiдaцiя даних. Консолідація виконується в тому випадку, коли потрібно підсумувати дані, які розташовані в різних діапазонах таблиці. Діапазони можуть знаходитися на одному робочому аркуші або на різних аркуша i в різних книгах. Наприклад, якщо в різних книгах міститься певна інформація, то для створення підсумкового аркуша можна використати консолідацію. Цю команду можна використовувати декількома способами. Можна консолідувати дані з початковими даними, так щоб зміни в початкових аркушах приводили до змін в підсумковому аркуші. Або можна консолідувати дані без зв’язків. Консолідувати дані можна по розташуванню (by position) або по категорії (by category). Якщо консолідуються дані по розташуванню, то збирається інформація з однаково розташованих комірок всіх початкових аркушів. При консолідації даних по категорії EXCEL використовує за основу об’єднання аркушів заголовки стовпців або рядків. Консолідація по категорії надає більшу свободу для організації даних.

Консолідація за розташуванням. Нехай маємо на аркушах 1 семестр i 2семестр список студентів з оцінками по відповідних предметах.

3 допомогою консолідації за розташуванням хочемо отримати середнє арифметичне кожного з студента за два семестри. Спочатку підготуємо місце для отримання результата. Скопіюємо стовпець А з аркуша 1семестр або 2семестр на новий аркуш Середнє в комірки стовпця А. Потім скопіюємо комірки В1:D1 у відповідні комірки аркуша Середнє. Позначаємо діапазон комірок B2:D7 на аркуші Середнє i виконуємо команду DATA (дані) => CONSOLIDATE (консолідація). 3’являється діалогове вікно. У рядку FUNCTION (функції) вибираємо тип функції. Для нашого прикладу AVERAGE (середнє). У рядку REFERENCE (адреса) вибираємо діапазони комірок або набираємо з клавіатури, або шляхом позначення діапазонів (вікно CONSOLIDATE можна зменшити або перенести). Наприклад, переходимо на аркуш 1семестр i позначаємо діапазон $B$2:$D$7, i клацаємо на клавіші ADD (додати). Переходимо на аркуш 2семестр i позначаємо такий самий діапазон i знову клацаємо на клавіші ADD (додати). У полі ALL REFERENCES (список діапазонів) висвітлюється вci позначені діапазони. Якщо діапазони комірок знаходяться в інших книгах, то клацаємо на клавіші BROWSE (переглянути), знаходимо файл, відкриваємо його i позначаємо потрібні комірки. Якщо певні комірки непотрібні, позначаємо їx в полі ALL REFERENCES (список діапазонів) i клацаємо на клавіші DELETE (витерти). Після цього клацаємо на OK i з’являється таблиця виду, де обчислюється середнє арифметичне за два семестри.

Консолідація по категорії. Тепер розглянемо більш складний приклад. Нехай у другому семестрі у rpyпi з’явилися ще два студенти, яких не було у першому семестрі. Розташуємо курсор у комірці А1 на аркуші категорія. Дані будемо брати з аркушів 1семестр i 2семестр. Але у аркуші 2семестр додається інформація що про двох студентів. Для консолідації по категорії виконаємо команду DATA (дані) => CONSOLIDATE (консолідація). 3’являється діалогове вікно. У рядку FUNCTION (функції) вибираємо AVERAGE (середнє). У рядку REFERENCE (адреса) вибираємо відповідні діапазони комірок. 3 аркуша 1семестр вибираємо $A$1:$D$7 i клацаємо на ADD (додати), з аркуша 2семестр вибираємо $A$1:$D$9 i клацаємо на ADD (додати). Для консолідації по рядках активізуємо команду LEFT COLUMN (значення лівогo стовпця) i клацаємо на ОК. Отримаємо результати виду:

Стовпець А містить прізвища студентів i заголовок. Інші стовпці без заголовків. Але заголовки можна скопіювати з відповідної таблиці i використати команду автоформат для кращого вигляду таблиці. Якщо початкові значення змінюються, то консолідуючу таблицю потрібно поновлювати. Для цього потрібно ввімкнути опцію CREATE LINK TO SOURCE DATA (створити зв’язок з початковими даними). Встановлюється динамічний зв’язок i автоматично забезпечується оновлення консолідуючої таблиці.
1   2   3   4   5

Схожі:

Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Колесников А. Excel 2000 (русифицированная версия). – К.: Изд группа ВНУ, 1999. – 496 с
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Колесников А. Excel 2000 (русифицированная версия). – К.: Изд группа ВНУ, 1999. – 496 с
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Лекція Робота з фінансовими функціями. Створення, редагування і форматування графіків і діаграм (2 год.)
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Вейскорн Джен. Excel 2000. Базовый курс (русифицированная версия). – К.: М., Спб.: Век+Энтроп, Корона, 2000. 464 с
Лекція з курсу «Прикладні програми (Електронні таблиці Excel)»
Вейскорн Джен. Excel 2000. Базовый курс (русифицированная версия). – К.: М., Спб.: Век+Энтроп, Корона, 2000. 464 с
Тема: Електронні таблиці. Програма ”Microsoft EXCEL”
Мета: навчити учнів розуміти призначення програм для опрацювання табличної інформації; запускати програму EXCEL; вводити інформацію...
УРОКУ   Тема: Загальні відомості про електронні таблиці
Мета: познайомити учнів з поняттям електронних таблиць та функціями програми Excel
Тема: Ознайомлення з вікном програми MS Excel
Щоб запустити Excel, виконаєте команду Пуск / Програми / Microsoft Office / Microsoft Excel
Лекція: Робота з таблицями: версія для друку і PDA Лекція присвячена...
Показані можливості сортування даних в таблиці. Дано уявлення про можливості обчислень в таблицях документів Microsoft Word 2007....
Тема: Електронні таблиці
Нарахувати стипендію учням за результатами сесії за умовою: якщо середній бал становить
Додайте кнопку на своєму сайті:
Портал навчання


При копіюванні матеріалу обов'язкове зазначення активного посилання © 2013
звернутися до адміністрації
bibl.com.ua
Головна сторінка