Установлення інтервалу критеріїв. Критерії бувають двох типів.

1. Критерії обчислення – це критерії, що є результатом обчислення формули. Наприклад, інтервал критеріїв =F7>СРЗНАЧ($F$7:$F$21 ) виводить на екран рядки, що мають у стовпці F значення більше, ніж середнє значення розмірів у вічках F7:F21 . Формула повинна повертати логічне значення ЛОЖЬ абоИСТИНА .При фільтрації будуть доступні тільки ті рядки, значення яких будуть додавати формулі значення ИСТИНА .

2. Критерії порівняння – це набір умов для пошуку, використовуваний для витягу даних при запитах за прикладом. Критерій порівняння може бути послідовністю символів (константою) або вираженням (наприклад, Ціна > 700 ).

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

­ виділити клітинку в таблиці;

­ у меню Данные вибрати команду Форма ;

­ натиснути кнопку Критерии ;

­ у полях редагування увести критерії для пошуку даних;

­ для виводу на екран першого запису, що відповідає критерію, натиснути кнопку Далее ;

­ для виводу на екран попереднього запису, що відповідає критерію, натиснути кнопку Назад ;

­ для пошуку записів у переліку по іншому критерії натиснути кнопку Критерии і увести новий критерій;

­ по закінченні натиснути кнопку Закрыть .

Щоб знову одержати доступ до усіх записів переліку необхідно натиснути кнопку Критерии , а потім натиснути кнопку Правка.

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

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

Рис.5.2.4.

Якщо у прихованому переліку вибрати пункт Условие … , то з’явиться вікно Пользовательский автофильтр (мал.). У верхньому правому переліку слід вибрати один з операторів ( равно , больше , меньше та ін.), а у полі праворуч вибрати одне зі значень. У нижньому правому переліку можна вибрати іншій оператор, і у полі ліворуч – значення. Коли увімкнений перемикач И , то будуть виводитися тільки записи, які задовольняють обидві умови. При увімкненому перемикачу ИЛИ будуть виводитися записи, які задовольняють одну з умов. Наприклад, у вікні на мал.36 введені умови для виведення записів по виробам з ціною більше 99грн і менше 187грн .

Рис. 5.2.5.

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

Щоб вивести усі дані переліку, необхідно викликати команду Отобразить все або скасувати команду Автофильтр меню Данные , підміню Фильтр.

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

Фільтрувати список на місці – перемикач, що приховує рядки, які не задовольняють зазначеному критерію;

Скопіювати результат в друге місце – копіює відфільтровані дані на інший робочий аркуш, або на інше місце на тому ж робочому аркуші;

Исходный диапазон – поле, що визначає інтервал, якій містить перелік, що підлягає фільтрації;

Диапазон условий – поле, що визначає інтервал вічок на робочому аркуші, якій містить необхідні умови;

Поместить результат в диапазон – поле, що визначає інтервал вічок, в який копіюються рядки, що задовольняють визначеним умовам; це поле активно тільки в тому випадку, якщо обраний перемикач скопировать результат в другое место;

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

Рис.5.2.6.

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

1. уставити декілька рядків у верхній частині робочого аркушу;

2. в одному із уставлених порожніх рядків увести імена стовпців, по яких слід відфільтрувати перелік;

3. при використанні критеріїв порівняння, імена критеріїв повинні бути ідентичні іменам стовпців, що перевіряються;

4. у рядках, розташованих під рядком із іменами стовпців, що перевіряються, увести критерії, яким повинні відповідати вічка стовпців, що перевіряються;

5. вибрати в меню Данные підміню Фильтр ,а потім команду Расширенный фильтр ,і в діалоговому вікні увести умови фільтрації.

Для об'єднання критеріїв за допомогою умовного оператораИ потрібно зазначити критерії в одному і тому ж рядку, а для об'єднання критеріїв за допомогою умовного оператора ИЛИ слід подати критерії в різних рядках. Наприклад, інтервал критеріїв на мал. виводить на екран усі записи, що мають у стовпці Цена значення більше 50 і менше 100.

Рис.5.2.7.

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

Щоб знову вивести усі записи слід у меню Данные вибрати пункт Фильтр і потім пункт Отобразить все .

Зведені таблиці. Зведеною називається спеціальна таблиця, яка підсумовує інформацію з окремих полів списку або бази даних. При створенні зведеної таблиці за допомогою майстра можна задати потрібні поля, структуру таблиці (її макет), тип обчислень, що виконуються. У разі потреби після побудови таблиці можна приховувати або відображати її структурні рівні. Розглянемо основні етапи роботи із зведеною таблицею.
• Створення зведеної таблиці.
Вибравши будь-яку комірку бази даних, виконайте команду Зведена таблиця з пункту меню Дані.
Майстер зведених таблиць виводить перше з трьох вікон діалогу. У ньому потрібно зазначити, на основі яких даних створюється таблиця. Як правило, це список Excel, тому залиште перемикач в списку або в базі даних Microsoft Excel встановленим.
Натисніть кнопку Дальше. Відкривається друге вікно майстра, в якому пропонується ввести діапазон, що містить вихідні дані.
Якщо вікно майстра правильно задає вихідні дані, натисніть кнопку Дальше, інакше введіть правильний діапазон.
У третьому вікні діалогу майстра зведених таблиць сформуйте макет нової зведеної таблиці. Для цього натисніть кнопку Макет І перемістіть кнопки з назвами полів у потрібну область рядків, стовпців або даних чи сторінок. Зони Стрічки, Стовпець, Далі повинні мати відповідне поле. Інформація в зоні Дані, як правило, обробляється математично. Тому в ній розміщують числові поля. При переміщенні кнопки поля в область Далі програма Excel вказує тип операції, яку здійснить з даними. За замовчуванням обчислюється сума. Для зміни типу операції слід перемістити кнопку поля в область Далі і двічі клацнути лівою кнопкою миші на ній відкриється вікно, в якому можна вибрати потрібну функцію обробки даних
Заповнювати область Сторінка не обов'язково. Натисніть кнопку (Ж для закриття вікна діалогу і повернення у третє вікно майстра зведених таблиць,
Виберіть місце розташування зведеної таблиці - - наявний або новий робочий аркуш Натисніть кнопку Параметри і введіть назву зведеної таблиці. Натисніть кнопку (Ж.
Після натискання кнопки Готово майстер створить таблицю, Відкриється також панель інструментів Зведені таблиці. За допомогою цієї панелі можна змінювати вигляд і параметри вже побудованої таблиці.
До готової таблиці можна застосувати будь-який автоформат Excel. Для нього потрібно виконати команду Автоформат з меню Формат \ вибрати відповідний формат таблиці. Якщо вибраний автоформат не забезпечує потрібного числового форматування, можна змінити числовий формат даних зведеної таблиці. Для цього слід виділити будь-яку комірку таблиці, на панелі інструментів Зведені таблиці натиснути кнопку Зведена таблица, вибрати команду Параметри поля, у вікні діалогу натиснути кнопку Формат, вибрати потрібний формат і двічі натиснути кнопку ОК.
• Зміна розміщення полів.
Вигляд таблиці можна змінити, перегустивши кнопки полів в інші зони, а також змінивши порядок відображення поля в області стовпців або рядків зведеної таблиці. Для видалення поля із зведеної таблиці кнопку поля треба перемістити за межі макету.
• Оновлення зведеної таблиці.
Зведена таблиця динамічно пов'язана з базою даних, яку було використано при її створенні. Якщо значення в базі даних змінилися, треба виконати команду Обновити дані з пункту меню Далі або натиснути кнопку Обновити дані на панелі інструментів Зведені таблиці Проте цей спосіб не підходить, якщо в базі даних з'являються нові рядки або стовпці. У цьому разі необхідно повернутися до майстра зведених таблиць і зазначити новий діапазон записів, що додаються до таблиці.
• Перейменування полів і елементів.
Імена полів і елементів, що призначені майстром зведених таблиць, можна змінити, відредагувавши їх заголовки у зведеній таблиці.
• Сортування елементів.
Майстер зведених таблиць впорядковує елементи за їх збільшенням. Якщо треба впорядкувати елементи за їх зменшенням або за значеннями поля даних, необхідно скористатись командою Сортування з пункту меню Дані.
• Відображення або приховування елементів поля рядка чи стовпця зведеної таблиці.
Можна приховати певні елементи полів у зведеній таблиці. Для цього слід розгорнути список поля і встановити (зняти) прапорці для елементів, які потрібно відобразити (відповідно приховати).
• Відображення або приховування елементів внутрішніх полів.
Зведена таблиця для кожного елемента зовнішнього поля повторює елементи внутрішнього поля. Щоб приховати елементи внутрішнього поля, потрібно двічі клацнути на відповідному елементі зовнішнього поля. Щоб вивести на екран елементи внутрішнього поля, знову слід двічі клацнути на заголовку зовнішнього поля.
• Відображення або приховування детальних даних.
Можна побачити детальну інформацію, що використовується для обчислення значення поля даних, якщо двічі клацнути на цьому значенні. При цьому здійснюється копіювання детальних даних на новий аркуш книги.
• Групування елементів внутрішнього поля.
Майстер зведених таблиць автоматично групує елементи внутрішнього поля для кожного заголовка зовнішнього поля і в разі потреби створює проміжні підсумки для кожної групи елементів внутрішнього поля. Проте іноді зручніше групувати елементи в інший спосіб. Щоб створити групу, слід виділити її елементи і виконати команду Дані/Структура /Згрупувати.
• Створення діаграми на основі зведеної таблиці.
Діаграму на основі зведеної таблиці можна побудувати так само, як і для будь-яких Інших типів даних робочого аркуша, скориставшись кнопкою Майстер діаграм на панелі інструментів або командою Діаграма з меню Вставка. Щоб досягти найкращих результатів при створенні діаграми на основі зведеної таблиці, дотримуйтесь таких рекомендацій: видаліть з таблиці проміжні та загальні підсумки; прослідкуйте, щоб таблиця мала не більше двох полів у зоні стовпців і рядків, приховайте всі зайві елементи.

Функції Excel. Excel має велику кількість вмонтованих функцій робочих таблиць, які користувачі можуть застосовувати в своїх формулах. До них належать як досить поширені функції, такі як СУММ (SUM), СРЗНАЧ (AVERAGE) і КОРЕНЬ (SQRT), так і функції, призначені для виконання спеціальних обчислень, наприклад, статистичні функції або прикладні функції. Функції можуть спростити формули і зробити їх зрозумілішими, крім того, вони здебільшого дають змогу робити обчислення, які без них виконати дуже важко.

В Excel входить більше як 300 вмонтованих функцій.

Функції - це вмонтовані інструменти, що використовуються у формулах. Вони дають змогу:

спрощувати формули;

виконувати за формулами такі обчислення, які без них зробити неможливо;

прискорювати виконання деяких завдань редагування.

Крім того, вони дозволяють "умовне" виконання формул, завдяки чому користувач може реалізовувати алгоритми прийняття рішень.

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

без аргументів;

з одним аргументом;

з невизначеною кількістю аргументів;

з необов'язковим аргументом.

Прикладом функції, що не використовує аргумент, є функція СЛЧИС (RAND), яка генерує випадкове число в діапазоні від 0 до 1. Синтаксис цієї функції такий (хоч і немає аргументу, дужки обов'язкові):

=CЛЧИС() або =RAND()

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

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