Опрацювання даних у Excel, сортування даних,

фільтрація даних у списку

 

5.1 Стислі теоретичні відомості

Використання засобів сортування, фільтрація і формування підсумків потребує уявлення електронної таблиці у вигляді бази даних або списку .

5.1.1 Уявлення ЕТ у виді списку

Список – це один із засобів організації даних на робочому листі. Список створюється як позначена область, що складається з рядківтаблиці зі зв’язаними даними. Перший рядок містить назви стовпців, що визначають структуру таблиці. Дані в кожному стовпчику мусять бути однотипними. Наприклад, перелік співробітників деякого відділу, у якому стовпчики мають відповідно такі імена:№ ,Прізвище,ім’я по батькові ,Оклад ,Кількість дітей, профс. ,пенсійний ,на прибуток ,Сума до видачі – це є список даних . Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД) . При цьому рядки таблиці – цезаписи бази даних , а стовпчики – поля БД .

Щоб перетворити таблицю Ехсel у список, необхідно надати стовпчикам імена, які будуть використовуватися як імена полів бази даних. Варто мати на увазі, що імена стовпчиків можуть складатися здекількох рядків заголовків, розміщених в одному рядку таблиці Ехcel, як це зроблено на рис. 5.1.

Створюючи список на робочому листі Ехсеl необхідно дотримуватись певних правил:

а) на одному робочому листі не варто поміщати більш одного списку, оскільки деякі операції, наприклад, фільтрація, не працюють водночас з кількома списками;

б) варто відокремлювати список від інших даних робочого листа хоча б одним незаповненим стовпчиком або одним незаповненим рядком. Це допоможе Ехсеl автоматично виділити список при виконанні фільтрації або при сортуванні даних;

в) список може займати весь робочий лист: 65536 рядків і 256 стовпчиків;

г) імена стовпчиків повинні розташовуватися в першому рядку списку. Ехсеl використовує ці імена при створенні звітів, для пошуку і сортування даних;

д) для імен стовпчиків варто використовувати шрифт, тип даних, вирівнювання, формат, рамку або стиль букв, відмінні від тих, що використовувалися для даних списку;.

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

Звертаємо увагу на зміни у структурі списку „Відомість нарахувань” з лабораторної роботи №!: заголовки таблиці розміщені не у два рядка, а в один, текст „податки” відокремлений від таблиці додатковим рядком 9 , як показано на рис. 5.1 .

 

 

 

Рисунок 5.1 - Приклад списку

 

 

5.1.2 Сортування даних

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

Команда Сортировка здійснюється також і через діалогове вікно. У вікні Сортировка в трьох полях введенняможна задати ключі, за якими буде виконане сортування. У першому полі (у списку) необхідно вибрати стовпчик, за яким Ехсеl повинен відсортувати дані.

Наприклад, дані зведеної відомості можна відсортувати по стовпчикуКількість дітей .В другому полі діалогового вікна Сортировка можна задати інший ключ сортування, наприклад,Прізвище, ім’я по батькові . Сортування для третього ключа виконується аналогічно.

У діалоговому вікні Сортировка існує режим Параметры .Він дозволяє встановити порядок сортування за першим ключем – звичайний або вказаний користувачем, задати врахування великих та малих літер(урахування регістру символів), а також напрямок сортування – за зростанням або за спаданням.

На панелі інструментівСтандартная знаходяться дві кнопки для швидкого сортування: „Сортувати по зростанню” та „Сортувати по спаданню” . Ключем сортування в цьому випадку є стовпчик із активною клітиною.

 

5.1.3 Фільтрація даних у списку

За допомогою фільтрів можна виводити та переглядати тільки ті дані, що задовольняють визначеним умовам. Ехсel дозволяє швидко і зручно переглядати необхідні дані зі списку за допомогою простого засобу – Автофільтру . Складніші запити до бази даних можна реалізувати за допомогою команди Расширенный фильтр .

 

 

5.1.4 Автофільтр

Щоб використовувати Автофільтр , треба спочатку виділити для пошуку область списку із заголовками полів. Потім виконатикоманду Автофильтр в меню Данные . Після вибору пунктуАвтофильтр Ехсel розташовує списки, що розкриваються, безпосередньо до відповідних імен стовпчиків списку. Клацнувши по стрілці, можна вивести на екран список всіх унікальних елементів відповідного стовпчика. Якщо виділити деякий елемент стовпчика, то будуть сховані всі рядки, крім тих, що містять виділене значення. Наприклад, якщо вибрати значення поля Кількість дітей рівним 1 , то будуть обрані тільки ті співробітники, що мають одного утриманця.

Елемент стовпчика, який виділений у списку, що розкривається, називається критерієм фільтру . Можна продовжити фільтрацію списку за допомогою критерію з іншого стовпчика. Наприклад, якщо після виділення елемента «1» у списку для поля Кількість дітей вибрати у списку Сума до видачізначення «2005,19 грн» , то на екран буде виведений тільки один рядок для співробітника Іванов А.Ф.

Щоб видалити критерії фільтра для окремого стовпчика, треба вибрати параметр Все в списку, що розкривається. Щоб показати всі сховані в списку рядки, треба вибрати в меню Данные команду Фильтр , а потім – команду Отобразить все .

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

 

5.1.5 Складна фільтрація

Для фільтрації списку або бази даних за складним критерієм, що буде визначений нижче, а також для одержання частини наданого списку з декількома потрібними стовпцями, в Ехсel використовується командаРасширенный фильтр меню Данные . Відмінність цієї команди від командиАвтофильтр полягає в тому, що, крім перелічених вище можливостей, відфільтровані записи можна винести в інше місце робочого листа Ехсel, не зіпсувавши наданий список.

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

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

Завдання критеріїв пошуку у видіконстант потребуєточної копії імен тих стовпчиків списку, що задають умови фільтрації. Наприклад, для фільтрації списку „Відомість нарахувань” з метою відбору записів співробітників, у яких „Сума до видачі” більше 400, треба скласти таку таблицю критеріїв:

 

 

Якщо необхідно одержати список співробітників, у яких „Сума до видачі”знаходиться в діапазоні від 2500 до 3500 , то в таблиці критеріїв кожна умова повинна бути задана окремо , але в одному рядку , тому що вони пов’язані оператором И . Таким чином, таблиця критеріїв матиме вигляд:

 

 

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

Наприклад, для таблиці „Відомість нарахувань”необхідно одержати список співробітників, у яких Сума до видачі знаходиться в заданому вище діапазоні. Тоді заголовок вихідного документу повинен мати такий вигляд:

 

Прізвище, ім'я по батькові

Сума до видачі

 

Кількість рядків у вихідному документі Ехсel визначить самостійно.

Таким чином, для виконання командиРасширенный фильтр треба виконати три дії:

а) сформувати у вільному місці робочого листа таблицю критеріїв;

б) сформувати шапку вихідного документу;

в) виділити область пошуку в первісному списку.

 

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

Тепер можна запускати командуРасширенный фильтр, яка виведе на екран діалогове вікно. У діалоговому вікні треба задати Исходный диапазон , тобто область знаходження тієї частини списку, яку треба відфільтрувати. Якщо перед запуском командиРасширенный фильтр область пошуку була виділена, то в діалоговому вікні Исходный диапазон буде заданий (рис. 5.2).

 

 

Рисунок 5.2 – Заповнене діалогове вікно Расширенный фильтр

Далі треба визначити Диапазон условий , задавши область знаходження таблиці критеріїв, до якої обов’язково входять імена стовпчиків таблиці. В області діалогового вікнаОбработка треба зазначити, буде фільтрація виконуватися на місці або результат буде записаний в іншій області робочого листа. Якщо був обраний режим – „фильтровать список на месте” , то Ехсеl сховає всі рядки початкового списку, які не задовольняють заданим критеріям. Якщо встановлений перемикач „только уникальные записи”, то однакові рядки списку не будуть показані в області вихідних даних. Якщо умови пошуку задані в таблиці критеріїв в одному рядку, то ці умови зв’язані оператором И , якщо ж умови пошуку задані в різних рядках, то вони пов’язані оператором ИЛИ .

Виконайте фільтрацію й перевірте правильність відбору даних.

 

5.1.6 Використання критерію, що обчислюється

 

Використання критеріїв пошуку, що обчислюються, в таблиці критеріїв потребує виконання таких правил щодо формул:

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

б) формула повинна посилатися хоча б на один стовпчик у списку;

в) формула, використана в критерії, повинна посилатися або на заголовок стовпця, або на відповідне поле першого запису списку(в наведеному нижче прикладі це може бути або „Сума до видачі” , або H11 , або H12).

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

Формула для критерію обчислюється за допомогою функції СРЗНАЧ , а саме:

=H11>СРЗНАЧ($H$11:$H$20)

 

Результат наведений на рис. 5.3.

 

Рисунок 5.3 – Результат фільтрації по критерію, що обчислюється

 

Використання критерію, що обчислюється, накладає обмеження на таблицю критеріїв . У цьому випадку ім’я стовпчика в таблиці критеріїв, який містить значення критерію, що обчислюється, повинно відрізнятися від імені подібного стовпчика у початкових даних. Тому, в наведеному нижче прикладі, ім’я поля„Сума до видачі”в таблиці критеріїв одержало ім’я „До видачі” .

Виконайте зазначену фільтрацію і порівняйте отримані результати.

 

5.1.7 Робота з функціями бази даних

Microsoft Excel пропонує 14 функцій для роботи зі списками. Кожна з цих функцій повертає інформацію про елементи списку, які задовольняють деяким умовам.

Функція СЧЕТЕСЛИ(діапазон;критерій) підраховує кількість елементів у діапазоні, які задовольняють критерію.

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

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

=СЧЕТЕСЛИ(D11:D20;”>1”) .

Функція СУММЕСЛИ(діапазон;критерій;діапазон_підсумовування)

підсумовує значення в зазначеному діапазоні, з огляду тільки на ті записи, що задовольняють критерію.

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

=СУММЕСЛИ(D11:D20;”>1”;H11^H20) .

Інші функції, орієнтовані на роботу зі списками, мають узагальнену назву Бдфункції . На відміну від двох наведених вище функцій, вони потребують створення блока критеріїв , як при роботі з розширеним фільтром.

Функція ДСРЗНАЧ(діапазон_списку;поле;блок_критеріїв) обчислює середнє значення у вказаному полі серед тих записів, що задовольняють умові, записаній в блоці критеріїв.

Наприклад, щоб обчислити середню зарплату співробітників, які мають більш одного утриманця, створимо блок критеріїв в клітинах G3:G4 , записавши в G3 – Кількість утриманців, а в G4 – >1. Відповідь дасть формула:

=ДСРЗНАЧ(A10:H20;”Всего начислено”;G3:G4) .

Ознайомтесь з іншими функціями обробки бази даних самостійно.

Ви можете використовувати команду Данные/Проверка для того, щоб значення, що вводяться в список, задовольняли деяким умовам. У вікні діалогу Проверка вводимых значений на вкладці Параметры зазначтетип та інтервал значень, що дозволяється вводити. Вкладка Сообщение для ввода дозволяє скласти підказування користувачу про те, які дані можна вводити. На вкладці Сообщение об ошибке можна ввести повідомлення, яке буде з’являтися на екрані, якщо введено некоректні дані. Задайте перевірку для даних із списку і перевірте як вона працює.

5.2 Контрольні питання

5.2.1 Яка частина таблиці може бути списком? Чим список(база даних) відрізняється від електронної таблиці?

5.2.2 Поясніть різницю в можливостях сортування за допомогою кнопок панелі інструментів і за допомогою пункту меню Данные/Сортировка . Які обмеження має сортування в Excel?

5.2.3 Які можливості надає користувачу опція Автофильтр ?

5.2.4 Перелічіть правила завдання блока критеріїв для розширеного фільтра. Які особливості завдання критерію, що обчислюється?

5.2.5 Які функції для роботи зі списком Ви знаєте? Їхнє призначення і можливості.

5.2.6 Як задати перевірку значень, що вводяться, і які можливості у Вас при цьому є?

5.2.7 Назвіть головні можливості та вимоги до опції Данные/Итоги в списку.

 

5.3 Завдання для самостійної роботи

 

Для списку, що Ви створили в лабораторній роботі №1, виконайте наступні дії.

5.3.1 Зробіть сортування списку по декількох полях усіма можливими засобами.

5.3.2 За допомогою команди Данные/Фильтр/Автофильтр відберіть дані, що задовольняють декільком умовам (визначте умови самостійно).

5.3.3 За допомогою команди Данные/Фильтр/Расширенный фильтр організуйте відбір даних у вільне місце таблиці, що задовольняють більш складним критеріям, а також критеріям, що обчислюються. Для цього задайте декілька блоків критеріїв і блоків виведення.

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

5.3.5 Організуйте для декількох полів перевірку значень, що вводяться.

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

 

6 РЕКОМЕНДОВАНА ЛІТЕРАТУРА

 

6.1 Джонс Эдвард, Саттон Дерек. Библия пользователя Office 97.: Пер. с англ.- К.:Диалектика,1997.-848с.

6.2 Евдокимов В.В. и др. Экономическая информатика.Учебник для вузов. – СПб.:Питер, 1997.-592с.

6.3 Использование Microsoft Excel 97: Пер. с англ. – Изд.-бестселлер.-К.;М.;СПб: Изд. Дом «Вильямс», 1998.-800с.

6.4 О.Ефимова, М.Моисеева, Ю.Шафрин. Практикум по компьютерной технологии, Упражнения, примеры и задачи. – Москва, ABF,1997.

6.5 А.Левин. Самоучитель работы на компьютере. – Москва: Издат. «Нолидж»,1998.-624с.

6.6 Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах.-СПб.:БХВ – Санкт-Петербург, 1999. – 336 с., ил.