Posobie_1_chast2_Excel

Користувальницький автофільтр дозволяє відфільтрувати список одночасно по двох умовах для кожного зі стовпчиків.

Щоб створити користувальницький автофільтр, треба:

•натиснути будь-яку комірку списку;

•дати команду Даные/Фильтр/Автофильтр;

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

•натиснути елемент Условие – з'явиться діалогове вікно Пользовательский автофильтр;

•задати умова:

9 вибрати з лівого списку, що розкривається, оператор порівняння: «дорівнює», «більше», «менше», «більше або дорівнює», «менше або дорівнює», «не дорівнює» або ін.;

9 вибрати з правого списку, що розкривається, необхідне значення порівняння;

•натиснути перемикач И (AND) або ИЛИ (OR) і вибрати зі списків, що розкриваються, у полях другої умови оператор і значення порівняння;

•натиснути ОК.

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

32. Створіть користувальницький автофільтр на основі наступних критеріїв:

fМісяць – жовтень, листопад (підказка: оператор порівняння - «дорівнює», перемикач ИЛИ (OR));

fНайменування товару – Вексель, Казначейське зобов'язання (підказка: оператор порівняння - «дорівнює », перемикач ИЛИ (OR));

fЗакупівля (шт.) – більше 6, але менше або дорівнює 12 (підказка: перемикач И (AND)).

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

Даные/Фильтр/Автофильтр

33. Скасуйте фільтрацію списку.

60

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

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

•уставити кілька порожніх рядків над рядком заголовків стовпчиків;

•у першу комірку першого порожнього рядка скопіювати заголовок фільтруємого стовпця;

•ввести в нижні рядки умови добору;

•вибрати команду Данные/Фильтр/Расширенный фільтр– з'явиться діалогове вікно Расширенный фільтр;

•у групі Обработка встановити перемикач Фильтровать список на месте –

61

– щоб скопіювати відфільтрований список в інший діапазон;

•указати діапазон фільтруємого списку в поле Выходной діапазон, виділивши його мишею;

•ввести в поле Диапазон критериев посилання на діапазон умов відбору, включаючи заголовки;

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

натиснути поле Поместить результат в диапазон, а потім натиснути ліву верхню комірку області вставки; натиснути ОК.

34.Визначите, яка продукція закуповувалася в травні, червні і липні. Для цього:

fуставте перед списком п'ять порожніх рядків;

fсформуйте діапазон умов (у комірку А1 скопіювати заголовок Місяць (комірка В7), у комірки А2:А4 уведіть назви зазначених місяців);

fстворіть розширений фільтр, указавши вихідний діапазон і діапазон умов, а також задавши копіювання відфільтрованого списку в діапазон, що починається з комірки М1.

35.Видаліть відфільтрований список і рядки, що містять діапазон критеріїв.

Підведення підсумків

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

62

Щоб підвести підсумки, треба:

•відсортувати список по стовпцях, для яких потрібно підвести підсумки (наприклад, щоб підвести підсумки по обсязі закупівель по кожнім виді товару, необхідно відсортувати список по полю «Найменування товару»);

•натиснути будь-яку ячейку списку;

•дати команду Данные/Итоги – з'явиться діалогове вікно Промежуточные итоги;

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

•у списку Операция вибрати підсумкову функцію (підсумовування або ін.);

•у поле Добавить итоги по ввімкнути прапорець тому стовпцеві, за значеннями якого треба підвести підсумки;

•активувати прапорці Заменить текущие итоги – щоб замінити всі проміжні підсумки знову створеними, а також Итоги под даннями – щоб вставити рядки, що містять загальні і проміжні підсумки під детальними даними;

•натиснути ОК.

36.Підведіть підсумки по валовому обсязі закупівель кожного виду цінних паперів. Для цього:

f попередньо відсортуйте по зростанню поле Найменування цінного

папера,

f призначте підведення сумарних підсумків по полю Валовий обсяг

закупівель.

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

37.Клацніть кнопку «1» першого рівня структури – ознайомтеся з загальним підсумком закупівель за рік.

38.Клацніть кнопку «2» другі рівні структури – проаналізуйте обсяг закупівель по кожнім виді товару.

Щоб видалити підсумки, треба дати команду Данные/Итоги (Data/Subtotals) і в діалоговому вікні Промежуточные итоги натиснути кнопку

Убрать все.

39. Видаліть підсумки.

63

40. Самостійно виконайте завдання.

Перейдіть до листа Виробництво.

Створіть автофільтр.

Відфільтруйте список так, щоб на екран виводилися дані про кількість зроблених цехом №1 деталей типу Гвинт М14 і Гвинт М10, з умовою, що кількість шлюбу менше або дорівнює 230 шт. на партію. (Підказка: для стовпчиків Деталь і Брак створіть користувальницький автофільтр).

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

Видаліть автофільтр.

Підведіть підсумкові значення загальної кількості випущених деталей по кожному типі гвинта. (Підказка: відсортуйте стовпець Деталь по зростанню).

За допомогою структури покажіть загальну кількість випущених за два роки деталей.

Додайте до підсумкових даних підрахунок сумарної кількості якісних деталей. (Підказка: у поле Добавить итоги по: діалогового вікна

64

Промежуточные итоги установите ще один прапорець – для поля Якісні).

Проаналізуйте отриманий результат на 1-м і 2-м рівнях структури.

Видаліть підсумки.

Примітка: У всі листки файлів вставте верхній колонтитул:

РС№

Прізвище студента

Шифр групи

41.Збережіть файл D:\Мои документы\Шифр групи\ Equipment_Прізвище.

42.Закрийте усі відкриті вікна.

43.Вимкніть комп’ютер.

65

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

1.Що в списку називається записом, полем і полем запису?

2.З якою метою можна використовувати форму введення даних?

3.Які способи прискореного введення даних у список Ви знаєте?

4.Якщо сортувати елементи АА-123 і АА-99 по зростанню, то який з них виявиться в списку раніш?

5.Як задати сортування по трьох стовпцях списку?

6.Подумайте, чи можна відсортувати список по чотирьох стовпцях? Якщо можна, то як?

7.Чим відрізняється використання звичайного Автофильтра від

користувальницького Автофильтра?

8.Яку обов'язкову процедуру потрібно виконати, перед тим, як підводити підсумки в списку?

Література: [1], [4], [5], [6], [7], [8], [11], [12], [13], [16], [19], [21], [22], [23], [25], [26].

Практичне заняття № 6 – тестування

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

66

Практичне заняття № 7

Тема дисципліни: Microsoft Office. Табличний процесор EXCEL (рівень спеціаліста).

Тема заняття: Табличний процесор EXCEL.

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

Хід виконання роботи

1.Увімкніть комп’ютер, увійдіть в систему з ім’ям USER.

2.Завантажте табличний процесор Microsoft Excel.

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

Створення зведеної таблиці

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

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

Мастера сводных таблиц и диаграмм на основі списку. Для цього треба:

•натиснути будь-яку комірку списку і дати команду Данные/Сводная таблица;

•у діалоговому вікні Майстер сводных таблиц и диаграмм – шаг 1 з 3 поставити перемикач у групі Создать таблицу на основе данных,

находящихся у потрібне положення;

•у полі Вид создаваемого отчета установити перемикач сводная таблица;

•натиснути Далее;

67

•у діалоговому вікні Мастер сводных таблиц и диаграмм – шаг 2 з 3 указати діапазон вихідних даних, виділивши його мишею;

•натиснути Далее;

•у діалоговому Мастер сводных таблиц и диаграмм – шаг 3 з 3 вибрати положення перемикача в групі Поместить таблицу в:

9 новий аркуш, щоб помістити зведену таблицю на новому листі, 9 існуючий аркуш, якщо зведену таблицю треба розташувати на активному

листі; потім варто натиснути ліву верхню комірку області вставки зведеної таблиці на листі;

•натиснувши на кнопку Параметры, можна установити різні параметри зведеної таблиці, як, наприклад, висновок загального підсумку по стовпцях і рядкам;

•кнопка Макет призначена для розміщення полів у зведеній таблиці;

•натиснути Готово, щоб закінчити створення зведеної таблиці.

На робочому листі з'явиться розмітка для майбутньої таблиці і панель інструментів Сводные таблицы.

68

3.Перейдіть на аркуш Список книги Equipment_Прізвище.xls.

4.Створіть зведену таблицю для списку, що знаходиться на цьому листі: f у діалоговому вікні Мастер сводных таблиц и диаграмм – шаг1 з 3

у полі Создать таблицу на основе даннях, находящихся установіть перемикач в положення в списке или базе данных Excel;

у полі Вид создаваемого отчета установіть перемикач сводная таблица;

f у діалоговому вікні Мастер сводных таблиц и диаграмм – шаг 2 з 3

укажіть, що вихідні дані знаходяться в діапазоні А2:K91;

f за допомогою діалогового вікна Мастер сводных таблиц – шаг 3 з 3 помістіть зведену таблицю на новий аркуш і клацніть Готово.

5.Перетаскуючи мишею кнопки полів у відповідну область розмітки, сформуйте зведену таблицю з наступною структурою:

в області Страница розташуєте дані поля Одержувач,

в області Столбец – дані поля Місяць,

в області Строка – дані поля Найменування цінного папера,

в області Данные буде проводитися підсумовування значень полів

Валовий обсяг закупівель.

6.Назвіть створений аркуш Зведена таблиця.

7.Розглянете таблицю. Тепер у ній представлені зведення про щомісячні обсяги закупівель товарів і підсумкові зведення про закупівлі за рік.

8.Покажіть зведення про закупівлі для Підприємства1, Підприємства2.

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

69