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

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

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

1) Активізувати будь-яку комірку у списку і виконати команду Данные – Сортировка. Відкриється діалогове вікно Сортировка диапазона.

2) У розділі Сортировать по потрібно вибрати параметр, за яким треба відсортувати. За замовчуванням Excel сортує дані в порядку зростання.

3) Щоб задати параметри вторинного сортування (при співпаданні значень у першому полі), потрібно вказати поле і порядок сортування (за зростанням чи спаданням), повторивши дії, описані раніше.

4) Зазвичай у розділі Идентифицировать поля по встановлений режим подписям (первая строка диапазона). У такому випадку рядок, що містить заголовки стовпців, не підлягає сортуванню. Якщо в списку рядок заголовків відсутній, і перший рядок має сортуватися нарівні з іншими, треба встановити режим обозначениям столбцов листа.

5) Щоб швидко впорядкувати список за зростанням (спаданням), достатньо виділити комірку в стовпці, по якому повинно здійснюватися сортування, і натиснути на кнопку Сортировка по возрастанию (убыванию) на панелі інструментів. Цей спосіб дозволяє сортувати тільки по одному стовпцю.

7. Проміжні підсумки

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

1) Відсортувати список по стовпцю, на основі даних якого слід створити групи.

2) Виділити будь-яку комірку в списку і виконати команду Данные – Итоги. З’явиться діалогове вікно Промежуточные итоги.

3) Зі списку При каждом изменении в вибрати стовпець, за даними якого слід групувати список (той, значення якого були відсортовані у п. 1).

4) Зі списку Операция, що розкривається, вибрати потрібну для обчислень функцію (наприклад, Сумма, Количество, Среднее і т.д.).

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

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

На робочому листі автоматично з’явиться структура документа. Символи структури ( - та +) і номери рівнів можна використовувати для того, щоб приховувати і відтворювати дані різних рівнів структури.

8. Використання функцій баз даних

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

Кожна функція має три аргументи: база_данных, поле і критерий.

Щоб скористатися функціями баз даних, потрібно виконати такі дії:

1) Визначити на робочому листі область умов (критерій).

2) Підготувати комірки, в яких будуть міститися результати.

3) Викликати Мастер функций.

4) У списку Категория вибрати Работа с базой данных. У списку Функция вибрати ім’я потрібної функції.

5) Для першого аргументу вибрати комірки листа, що містять БД.

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

7) Перейти до аргументу Критерииі вибрати в листі відповідні комірки області умов.

8) Натиснути ОК. У рядку формул відтвориться функція, а на робочому листі з’явиться результат обчислення цієї функції.

Якщо в області умов вказано два критерії в одному рядку, вони пов’язані логічним оператором И. Щоб зв’язати критерії співвідношенням ИЛИ, потрібно використати в області умов другий рядок (див. рис. 6.1). Перший критерій варто помістити в перший рядок, а другий – у другий. При наявності порожнього рядка при розрахунках будуть використовуватися всі записи списку.

Рис. 6.1. Область умов з використанням логічних операторів И та ИЛИ

9. Використання розширеного фільтру

Розширені фільтри дозволяють знаходити дані, що задовольняють складним критеріям із використанням операторів умов И/ИЛИ, а також накладати умови за допомогою розрахункових критеріїв.

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

1) Створити область умов (критерій).

2) Виділити будь-яку комірку в списку та виконати команду Данные– Фильтр – Расширенный фильтр.

3) У полі Исходный диапазон Excel автоматично вкаже діапазон комірок списку.

4) У полі Диапазон условий мишкою виділити область умов.

5) Якщо встановлений перемикач скопировать результат в другое место, стає доступним третє поле введення – Поместить результат в диапазон. Слід вибрати на робочому листі діапазон комірок, в якому повинні розміститися відфільтровані дані.

6) Якщо у списку є записи, що повторюються, потрібно встановити прапорець Только уникальные записи – і, у випадку виявлення однакових записів, на екрані буде відображатися тільки перший з них.

7) Натиснути на кнопку ОК.

 

ІІ. ПРАКТИЧНА ЧАСТИНА

Хід роботи

1. Опрацювати теоретичні відомості.

2. Відкрити робочу книгу Прізвище_Excel,яка містить таблицюАналіз ваги і зросту юнаків групи.

3. Додати новий робочий лист у книгу. Перейменувати його на Лаб6.

4. Скопіювати таблицю з листа Лаб3-4 разом із формулами на лист Лаб6.

5. Змінити "шапку" таблиці відповідно до базових правил створення списків (див. п.2). Для цього потрібно зняти об’єднання комірок у таблиці, додати між заголовком і самою таблицею один вільний рядок, видалити непотрібні підписи стовпців та коди стовпців. Крім того, модифікуємо структуру таблиці, додавши (видаливши) стовпці та вписавши додаткові дані про студентів (див. рис. 6.2). Усі інші дані з листа видалені.

Рис. 6.2. Список юнаків – студентів факультету

6. На підставі даних зі списку (рис. 6.2) виконати наступні завдання.

6.1. Внести дані за допомогою форми:

Ввести в створену раніше робочу таблицю (список) декілька нових (своїх) записів про студентів за допомогою команди Данные – Форма.

6.2. Провести сортування даних у таблиці різними способами:

1) Відсортувати список прізвищ за алфавітом.

2) Провести сортування списку за роком народження юнаків, встановивши вторинне сортування по групам, а у третю чергу – по прізвищам.

6.3. Провести фільтрацію даних у таблиці за допомогою автофільтру:

1) Відібрати студентів 1991 року народження.

2) Знайти записи про студентів, прізвище яких починається на букву "П".

3) Відібрати студентів, які мають відхилення від належної ваги понад 10 кг.

4) Відібрати студентів, для яких Індекс Бреле становить норму (від 20 до 25).

5) Знайти трьох студентів, найвищих за зростом.

Результати фільтрації по кожному пункту скопіювати на окремий лист робочої книги Лаб6-результати та підписати відповідно до завдання (див. приклад на рис. 6.3).

Рис. 6.3. Результати застосування автофільтру

6.4. Провести фільтрацію даних за допомогою розширеного фільтру:

1) Знайти студентів групи ОАП-21, рік народження – 1991.

2) Знайти записи про студентів, які страждають ожирінням (Індекс Бреле >30) або мають недостатню вагу (Індекс Бреле < 20).

3) Знайти записи про студентів, для яких обидва індекси становлять норму. А саме, 350 ≤ Індекс Кетле ≤ 400 та 20 ≤ Індекс Бреле ≤ 25.

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

Рис. 6.4. Область критеріїв пошуку за допомогою розширеного фільтру

Для третього випадку критерій запишеться таким чином (рис. 6.5):

Рис. 6.5. Область критеріїв пошуку за допомогою розширеного фільтру

Для запису критерію у другому випадку потрібно врахувати, що умови пов’язані логічним оператором ИЛИ і мають записуватись у різних рядках (рис. 6.6).

Рис. 6.6. Запис умов пов’язаних логічним оператором ИЛИ

Після формування критеріїв слід виділити будь-яку комірку всередині списку та виконати команду Данные – Фильтр – Расширенный фильтр. Примітка! Область умов (критерій) виділяється разом із назвами полів (прямокутна область).

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

Рис. 6.7. Результати застосування розширеного фільтру

6.5. Застосувати функції баз даних для розрахунку:

1) Кількості студентів групи ОАП-21 1991 року народження, скориставшись сформованим раніше критерієм та функцією БСЧЕТА.

2) Середнього зросту студентів групи ЕК-21, сформувавши новий критерій, та скориставшись функцією ДСРЗНАЧ.

Область результатів зручно розташувати справа від вхідної таблиці, як показано на рис. 6.8.

Рис. 6.8. Виконання розрахунків за допомогою функції БСЧЕТА

6.6. Розрахувати проміжні підсумки, які обчислюють середній зріст та середню вагу студентів по групах:

1) Скопіювати список студентів на новий лист книги Лаб6-підсумки.

2) Відсортувати дані таблиці по полю Група за зростанням чи спаданням.

3) Виконати команду Данные – Итоги.

4) Встановити параметри у вікні Промежуточные итоги, як на рис. 6.9.

5) Переглянути отримані результати.

Рис. 6.9. Встановлення проміжних підсумків по групах

7. Підготувати звіт про виконання лабораторної роботи.

8. Ознайомитися з питаннями для самоконтролю та тестами.

9. Захистити лабораторну роботу викладачу.

 

ІІІ. ПИТАННЯ ДЛЯ САМОКОНТРОЛЮ

1. Що таке база даних в Excel?

2. Що таке поле таблиці?

3. Що називається записом бази даних?

4. Перерахуйте основні правила створення списків у Excel.

5. Що Ви розумієте під сортуванням даних таблиці?

6. Якими способами можна здійснити сортування записів у таблиці?

7. За значеннями скількох полів одночасно можна здійснювати сортування таблиці?

8. Що таке фільтр?

9. Які типи фільтрів існують в Excel?

10. Які умови можуть застосовуватись для відбору даних?

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

12. Як відмінити фільтрацію даних таблиці?

13. Опишіть алгоритм застосування розширеного фільтру?

14. Що таке область умов?

15. Які вбудовані функції Excel використовуються для роботи з базою даних?

16. Поясніть загальний систаксис функцій для роботи з базами даних.

17. Які засоби для роботи з базою даних вимагають формування області умов?

18. Для чого призначена Форма данных?

19. Як за допомогою Формы данных здійснити пошук потрібних записів?

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

ІV. ПЕРЕВІРОЧНІ ТЕСТИ

1) За допомогою якого засобу Excel не можна здійснити автоматичний пошук та вибір даних?

a) Автофільтру

b) Розширеного фільтру

c) Сортування

d) Форми

 

2) Який з видів фільтрів Excel дозволяє здійснювати фільтрацію даних за кількома критеріями одночасно?

a) Автофільтр

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

c) Обидва фільтри дозволяють це робити

d) Жоден з фільтрів не дозволяє це робити

 

3) Який з видів фільтрів Excel дозволяє здійснювати фільтрацію даних "на місці", скриваючи непотрібні записи?

a) Автофільтр

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

c) Обидва фільтри дозволяють це робити

d) Жоден з фільтрів не дозволяє це робити

 

4) Який з видів фільтрів Excel дозволяє відразу копіювати результат пошуку в інше місце, не змінюючи списку?

a) Автофільтр

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

c) Обидва фільтри дозволяють це робити

d) Жоден з фільтрів не дозволяє цього робити

 

5) Яка з наведених функцій не належить до категорії по роботі з базою даних?

a) ПРОМЕЖУТОЧНЫЕ.ИТОГИ

b) БДСУММ

c) БСЧЕТ

d) ДСРЗНАЧ

 

6) Яка з наведених функцій повертає проміжні підсумки в список чи базу даних?

a) СУМЕСЛИ

b) ВПР

c) ПРОСМОТР

d) ПРОМЕЖУТОЧНЫЕ.ИТОГИ

7) Скільки аргументів містить функція ПРОМЕЖУТОЧНЫЕ.ИТОГИ?

a) 1

b) 2

c) 3

d) 4

e) 9

 

8) Яким чином обчислюються проміжні підсумки у списках?

a) Данные – Итоги

b) Данные – Группа и структура

c) Вставка – Функция – Сумма

d) Данные – Консолидация

9) Яка обов’язкова дія має бути виконана перед підведенням проміжних підсумків у таблиці?

a) Сортування даних таблиці по полю, при зміні значень якого мають встановлюватись підсумки

b) Сортування даних по першому стовпцю таблиці

c) Сортування даних таблиці по полю, значення якого будуть додаватись (обчислюватись)

 

10)Яку назву має вікно, що викликається за допомогою команди Данные –Форма?

a) Ім’я активного листа

b) Текст з першого рядка виділеної області

c) Ім’я діапазону виділених комірок

d) Форма даних

 

11)Які дані розміщені в діалоговому вікні, що з’явилось після виконання команди Данные – Форма, ліворуч біля полів введення?

a) Дані з поточного рядка області виділення

b) Назви стовпців

c) Назви рядків

d) Імена перших в стовпцях комірок виділеного діапазону

 

12)Які дані розміщені в діалоговому вікні, що з'явилось після виконання команди Данные – Форма, праворуч від назв стовпців?

a) Дані з поточного рядка області виділення

b) Назви полів

c) Назви рядків

d) Імена перших в стовпцях комірок виділеного діапазону

 

13)Яка клавіша здійснює перехід до наступного поля у формі введення даних?

a) Enter або стрілка вниз

b) Tab

c) End

d) Shift+Tab

 

14)Яка клавіша здійснює перехід до наступного запису у формі введення даних?

a) Enter або стрілка вниз

b) Tab

c) End

d) Shift+Tab

15)Вкажіть неправильний синтаксис функції БДСУММ:

a) =БДСУММ(A2:F12;D2;A14:F15)

b) =БДСУММ(A2:F12;"Вага, кг";A14:F15)

c) =БДСУММ(Товари;"Вага, кг";A14:F15)

d) =БДСУММ(A2:F12;D2;D2>20)

 

16)Яка з наведених функцій не належить до категорії функцій для роботи з базою даних?

a) СЧЕТЕСЛИ

b) БДСУММ

c) БСЧЕТА

d) ДСРЗНАЧ

17)Яка з наведених функцій призначена для роботи з базою даних?

a) СУМЕСЛИ

b) СРЗНАЧ

c) БСЧЕТ

d) ИНДЕКС

 

18)Скільки аргументів містить будь-яка функція для роботи з базою даних?

a) 1

b) 2

c) 3

d) 4

 

19)Вкажіть правильний запис умови для пошуку інформації про студентів, прізвище яких починається на букву Б?

a) Б?

b) Б*

c) Б

d) Б#

 

20)Як записуються умови відбору для розширеного фільтру, які мають виконуватись одночасно?

a) В одному рядку

b) У різних рядках

c) В одному стовпці

d) В одній комірці

 

21)Вкажіть правильний запис умови при встановленні фільтру користувача для пошуку інформації про співробітників, номери телефонів яких починаються на 235 та 555?

a) начинается с 235 И начинается с555

b) начинается с 235ИЛИ начинается с555

c) начинается с235 И 555

d) 235 ИЛИ 555