Заняття 10 з 12
Заняття 10 Робота з даними та основи макрокоманд
Основи роботи з даними в Excel: представлення даних таблицею, імпорт зовнішніх даних, фільтрація, сортування та базові макрокоманди.
Презентація
Налаштування даних у форматі таблиці, імпорт зовнішніх даних, робота з базами даних, гарантування цілісності даних, сортування й фільтрація даних, групування, підведення підсумків.
Навички MOS 77-420
| Назва навички | Номер навички |
|---|---|
| Імпорт файлів | 1.1.3 |
| Відкриття сторонніх файлів у Excel | 1.1.4 |
| Встановлення перевірки даних | 1.3.8 |
| Керування безпекою макросів | 1.4.5 |
| Запис простого макросу | 1.4.7 |
| Прив'язка поєднань клавіш | 1.4.12 |
| Додавання даних на робочі аркуші | 2.1.1 |
| Створення структури | 2.3.5 |
| Згортання груп даних у структурі | 2.3.6 |
| Додавання підсумків | 2.3.7 |
| Перетворення діапазону на таблицю і навпаки | 3.1.1 |
| Додавання та видалення клітинок у таблиці | 3.1.2 |
| Визначення імен | 3.1.3 |
| Застосування стилів до таблиці | 3.2.1 |
| Область рядків і стовпців | 3.2.2 |
| Додавання рядка підсумків | 3.2.3 |
| Видалення стилів таблиці | 3.2.4 |
| Накладання фільтра на записи | 3.3.1 |
| Сортування даних кількох стовпців | 3.3.2 |
| Зміна порядку сортування | 3.3.3 |
| Видалення дублюючих записів | 3.3.4 |
Теорія:
1. Налаштування даних у форматі таблиці
Відеоверсія
Текстова версія
Коли ми створювали певні діапазони даних, додавали заголовки до діапазонів, ми часто називали такі діапазони таблицями. Насправді в Excel це не зовсім правильно, оскільки для того, щоб діапазон став таблицею, його потрібно відповідним чином відформатувати командою "Форматувати як таблицю" з групи "Стилі" вкладки "Основне".

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

До речі, при першому перетворенні з'явиться діалогове вікно, у якому потрібно підтвердити правильність вибору діапазону для таблиці, а також зазначити, чи є там заголовки. Після подальшої зміни оформлення це вікно більше не з'являтиметься.
Зовнішнє оформлення
Отже, перше, на що звертають увагу при форматуванні діапазону як РТ — це зовнішнє оформлення. Візуальна складова відіграє важливу роль у сприйнятті інформації, але якщо ви хочете, щоб РТ зовні нічим не відрізнялася від звичайного тексту на аркуші Excel, то очистити оформлення можна за допомогою команди "Очистити" у випадаючому меню вибору зовнішнього стилю РТ на додатковій вкладці "Конструктор".
Зверніть увагу: незважаючи на схожість випадаючого меню на вкладці "Конструктор" та меню‑команди "Форматувати як таблицю" вкладки "Основне", команда "Очистити" доступна лише в першому випадку.

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

Правда, вимкнувши рядок заголовків, вони не просто не будуть прикріплюватися до заголовків стовпців — вони зникнуть з аркуша. Кнопка фільтра додає автофільтр до РТ, рядок підсумків дозволяє швидко додати деякі підсумкові дані. Опції "Перший" та "Останній стовпець" виділять відповідні стовпці напівжирним.

У випадаючому списку потрібно вибрати тип проміжного підсумку (сума, середнє, кількість тощо), і Excel автоматично заповнить клітинку формулою для підрахунку підсумку на базі функції ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL). Так, ми вже її розглядали, і ще будемо розглядати при створенні структури.
Інші налаштування в групі "Параметри стилів таблиці" відповідають за зовнішнє стилістичне оформлення (чергування рядків або стовпців, перший і останній стовпець тощо); звісно, ці параметри матимуть сенс тільки при застосованому стилі оформлення таблиці.
Ім'я таблиці
Однією з корисних "фішок" РТ є те, що вони мають імена. У групі "Властивості" вкладки "Конструктор" можна змінити ім'я, а також самі розміри таблиці (перевизначивши початковий діапазон). Ім'я таблиці дуже схоже на іменування клітинок і діапазонів, які ми розглядали у третьому питанні четвертого заняття, але все ж дещо відрізняється.
Завдяки тому, що розумним таблицям можна давати імена, звертатися до певних елементів таблиці можна, використовуючи конструкцію:
Имя_таблицы[Имя поля]
Також у таблиці є зарезервовані імена для таких елементів, як: Вся таблиця (#Все або #All в англійській версії), Заголовки (#Заголовки або #Header), Дані (#Дані або #Data), Підсумки (#Итоги або #Totals), певний рядок (@).

Схематично адресацію до РТ можна представити таким рисунком.

І кілька прикладів звернення до елементів таблиці.

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

Крім того, у формулах можна використовувати стандартні посилання на клітинки так само, як якщо б це був діапазон. Єдина відмінність: якщо використовувати посилання на діапазон таблиці (всю таблицю або певний стовпець), їх не потрібно фіксувати як абсолютні.
Група "Інструменти"
На вкладці "Конструктор", яка стає доступною при переміщенні курсору в область РТ, є група "Інструменти". За допомогою команд цієї групи розумну таблицю можна перетворити назад у діапазон, зробити з неї зведену таблицю, видалити дублюючі значення в одному зі стовпців або додати зрізи — автоматичні фільтри для вибраних стовпців.

Зрізи — популярний інструмент Excel, який дозволяє прикрасити процес фільтрації й сортування значень за вибраними стовпцями; їх також можна зустріти при налаштуванні діаграм або зведених таблиць.
Ще корисні можливості перетворення діапазонів у таблиці
Перетворивши діапазони на таблиці, у кожної з них можна встановити власний автофільтр; для звичайних діапазонів допускається лише один фільтр на сторінці. Також корисно знати, що клацання по заголовку стовпця виділить тільки дані цього стовпця в таблиці, а не весь стовпець до кінця аркуша — це важливо при застосуванні форматування, наприклад, заливки.
І нарешті, що не менш важливо: при додаванні нових рядків унизу таблиці вони автоматично включатимуться до таблиці (за замовчуванням, це можна вимкнути). І це не просто застосування кольорової заливки — це автоматичний перерахунок формул, які використовували дані таблиці; наприклад, для підрахунку суми не потрібно змінювати розміри діапазону, новий рядок буде включений у формулу автоматично.
Коли таблиці не корисні
Перетворення діапазону на таблицю всім зручне, проте Excel дещо інакше обробляє таблиці, тому якщо виникне потреба використати дані для експорту, краще спочатку перетворити їх назад у діапазон; також перетворення в діапазон знадобиться, якщо потрібно додати імпортовані дані з зовнішнього джерела.
2. Імпорт даних
Відеоверсія
Текстова версія
Коли ви працюєте з великими обсягами інформації, як правило, первинні дані не вводяться вручну на аркуш, а імпортуються з інших джерел. При імпорті слід врахувати, що не завжди дані можуть виявитися у вигляді, придатному для роботи в Excel: неправильні роздільники, відсутня розбивка по стовпцях тощо. В Excel є різні інструменти для обробки початкових даних, а також способи додавання цих даних у книгу Excel.
Відкриття «нерідних» для Excel файлів прямо в Excel
Двома основними «рідними» форматами табличного процесора Excel є файли з розширенням ".xls" і ".xlsx". Перший — застарілий, який використовувався в версіях Excel до 2007 року, другий — сучасний, що дозволяє не лише користуватися всім функціоналом останніх версій Excel, але й, завдяки відкритим специфікаціям формату, більше сервісів можуть надавати дані в рідному для Excel форматі.
Водночас говорити про масове використання зовнішніми сервісами формату .xlsx не доводиться: частіше дані надають у базовому текстовому форматі XML або у CSV. У цьому випадку не можна гарантувати збереження початкового форматування даних (стовпці можуть бути не вирівняні, заголовки таблиці не на своїх місцях). В Інтернеті найпопулярнішим з погляду сумісності форматом для табличних даних вважається .CSV — це простий текст із комою як роздільником (Comma Separated Value — значення, розділені комами). Часто як роздільник використовується інший символ; хоча в такому випадку формат технічно є .DSV (delimiter-separated values), його все одно часто називають .CSV, що не зовсім правильно.
Імпорт зовнішніх даних
Для роботи з «нерідними» для Excel файлами передбачена вкладка "Дані"

Тут можна здійснити первинне отримання даних, зробити запит на отримання лише частини даних, а також виконати базові операції над самими даними: сортування, фільтрацію, видалення дублікатів.
Розглянемо детальніше імпорт зовнішніх даних на прикладі. У ролі даних буде вступний тест перевірки базових знань з текстового процесора MS Word. З моменту перенесення його на платформу Google Форми вже назбиралося 60 відповідей і завантажується файл у форматі *.csv. Excel може відкривати такі файли напряму, наче це файл Excel, але такий «швидкий» результат однозначно залишає бажати кращого.

Як видно, Excel абсолютно неправильно визначив кодування файлу і з такими даними працювати неможливо.
Водночас, якщо скористатися командою: "Отримання зовнішніх даних/з тексту" у групі "Отримання зовнішніх даних" вкладки "Дані" і пройти 3 простих кроки:
- Формат вихідних даних: з роздільником, формат файлу Юнікод (UTF-8) мої дані містять заголовки

Майстер імпорту крок 1 - Символ-роздільник: кома

Майстер імпорту крок 2 - На останньому етапі можна вказати формати для колонок, або залишити це на розсуд Excel, обравши для всіх колонок формат "Загальний"

Майстер імпорту крок 3
Наприкінці Excel запропонує вибрати місце для розміщення імпортованих даних. Зазвичай дані імпортують не на порожній аркуш, а як доповнення до вже наявних даних на аркуші. У такому випадку слід вказати крайню ліву клітинку для нових даних.

Натиснувши на "Властивості…" ми перейдемо в діалогове вікно з додатковими налаштуваннями, найважливішим серед яких є можливість автоматичного оновлення створеного запиту.

Після створення запиту натискаємо "ОК" і отримуємо результат, який кардинально відрізняється: ми отримали не просто читаємі дані, ці дані вже можна аналізувати.

Імпорт з баз даних
Часто додавати дані на аркуш доводиться не з текстового файлу, а з бази даних (БД), наприклад, СУБД Access. Процес імпорту даних з файлу або бази даних з точки зору користувача не відрізняється, але технічно між ними велика різниця.
БД — це не просто набір файлів із даними, це складна система даних, які організовані в таблиці й пов'язані між собою відношеннями. Тому для роботи з БД використовується спеціальне програмне забезпечення — системи керування базами даних (СУБД); одним із таких рішень є Access, який входить до складу пакета Microsoft Office.
Звісно, Excel чудово працює з Access; більш того, при імпорті даних користувач може імпортувати як самі таблиці, так і запити, тобто зведені вибірки, отримані в результаті обчислень, виконаних у самому Access.
Отже, щоб імпортувати дані з БД Access потрібно скористатися командою "З Access" у групі "Отримання зовнішніх даних" вкладки "Дані".
Після вибору файлу Access з'явиться діалогове вікно зі списком таблиць і запитів, які можна імпортувати в Excel.

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

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

Другим кроком імпортуємо запит з БД і додамо його на існуючий аркуш під наявною таблицею.

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

3. Контроль правильності даних
Відеоверсія
Текстова версія
При додаванні даних на аркуш користувач може помилитися: неправильно записав назву міста чи прізвище, випадково змінив клітинку з константою тощо. В Excel передбачені інструменти для контролю правильності введення даних, які стануть у пригоді в тих чи інших випадках.
Заборона на введення певних даних
Досить часто виникають ситуації, коли від користувача вимагають ввести певний тип даних, наприклад, лише 3 цифри коду оператора без коду країни, або ввести назву міста тощо. Звісно, можуть бути помилки вже на етапі вводу: хтось додасть код країни, хтось захоче записати знак "+" разом із кодом; із назвами міст ситуація не краща.
У таких випадках в Excel можна встановити перевірку вже на етапі вводу — не лише за типом даних, а й за самим вмістом.
Команда "Перевірка даних" знаходиться в групі "Робота з даними" вкладки "Дані".

У діалоговому вікні "Перевірка введених значень" можна налаштувати різні умови перевірки: наприклад, встановити обмеження на тип даних (текст, число, дата або лише перелік значень), а також на самі значення, наприклад, лише невід'ємні цілі числа тощо.

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

Створення випадавого меню
Досить поширеним запитом користувачів є створення випадавого меню на аркуші Excel. Це робиться якраз за допомогою діалогового вікна "Перевірка введених значень".
Спочатку потрібно створити список значень для вибору — краще на окремому аркуші, але можна й на поточному. У випадаючому меню "Тип даних" обрати "Список", а в полі значення вказати діапазон, в якому занесені значення для списку.

При цьому, якщо діапазон оформити «розумною таблицею» («Основне/ Форматувати як таблицю»), то додавання нових значень автоматично розширюватиме випадаючий список.

Видалення дублікатів
Операція видалення повторюваних значень доволі затребувана; команду «Видалити дублікат» можна зустріти на вкладці «Дані», якщо працюють з діапазоном даних, на додатковій вкладці «Конструктор», якщо діапазон перетворено на «розумну таблицю», або навіть у редакторі запитів інструмента «Завантажити й перетворити» (Power Query ми розглянемо пізніше).
Видалимо повторювані значення зі списку-джерела прізвищ, виконавши команду «Видалити дублікат» зі вкладки «Дані». До речі, оскільки наш список відформатовано як «розумна таблиця», це можна було зробити й з вкладки «Конструктор».

У діалоговому вікні «Видалити дублікат» можна вказати, чи має діапазон заголовок чи ні; до речі, видаляти дублікати можна відразу для кількох стовпців.
4. Сортування даних
Відеоверсія
Текстова версія
Потреба сортувати дані трапляється постійно: впорядкувати список прізвищ, відсортувати товари за кількістю продажів, регіони за прибутковістю тощо.
Досить часту й затребувану операцію сортування можна виконати кількома способами, з різними налаштуваннями та з різних місць інтерфейсу табличного процесора.
Сортування важливе не лише для подання даних, але й для роботи деяких функцій, наприклад, для функції ВПР (VLOOKUP), яку ми раніше розглядали, якщо обрано режим приблизного співпадіння шуканого значення.
Способи сортування
Сортування, як правило, можна виконати кількома способами:
- Вкладка «Основне», група «Редагування», команда «Сортування й фільтр».
- Вкладка «Дані», група «Сортування й фільтр», кілька команд для сортування.
- Контекстне меню/ Сортування.
- Сортування можна виконати з Редактора запитів (Power Query).

Сортування за одним критерієм
Найпростіший вид сортування — це сортування за одним критерієм; яскравий приклад — сортування ПІБ співробітників. Достатньо виділити діапазон і за допомогою однієї з перелічених команд вибрати напрям сортування (А до Я — за зростанням або Я до А — за спаданням).
Якщо сортувані дані є складовою частиною діапазону, Excel запропонує розширити діапазон сортування на іншу частину даних, тоді записи рядків не зіб'ються; однак можна відмовитись і відсортувати ТІЛЬКИ виділену частину, тоді не дивуйтеся, що, наприклад, номер телефону Сидорова опинився у Іванової. Якщо сортування відбувається в таблиці (тобто в «розумній таблиці»), там записи рядків завжди підлаштовуються під відсортований стовпець.

Сортування за кількома критеріями (налаштовуване сортування)
Більш цікавий випадок — сортування даних за кількома критеріями. Наприклад, є забіги атлетів у трьох раундах: потрібно розташувати їх від кращого результату до гіршого за третім раундом, але деякі атлети можуть мати однаковий час, тому друге сортування виконати за другим раундом і, якщо й тут збіг, то відсортувати за першим раундом.
Команда сортування за кількома критеріями знаходиться там же, де й за одним — потрібно вибрати опцію «Налаштовуване сортування».

Сортування даних з урахуванням атрибутів клітинок
Окрім самих значень — як числових, так і текстових, Excel може виконувати сортування залежно від таких атрибутів клітинки, як колір заливки, шрифт, значок клітинки; ці атрибути можна автоматично призначати за допомогою умовного форматування.

Умiло комбінуючи налаштовуване сортування з сортуванням за атрибутами, можна досягти цікавих результатів, наприклад, в одній таблиці окремо відсортувати чоловіків і жінок тощо.
5. Фільтрація даних
Відеоверсія
Текстова версія
Фільтрація даних — це відображення не всіх даних, а лише тих, що задовольняють певну умову, наприклад, показати тільки результати третього раунду забігів, використати для звітів лише дані поточного року, показати працівників, які не виконали план продажів тощо.
Фільтрація, як і сортування, дуже затребувана; більше того, ці команди в Excel розташовані разом — на вкладці «Основне» команда навіть називається «Сортування й фільтр».
Автофільтр
Найпростіший і найшвидший спосіб відфільтрувати дані. Після натискання кнопки фільтра біля заголовків діапазонів з'являються трикутники — «кнопки фільтрів», за допомогою яких можна швидко приховати ті чи інші значення з діапазону або таблиці, або відсортувати їх. Якщо діапазон відформатовано як таблицю, то кнопки фільтра там розміщені за замовчуванням; правда це можна відключити (вкладка «Конструктор») або прибрати сам фільтр (команди сортування й фільтра розглядалися раніше), у цьому випадку кнопка відображення фільтра на вкладці «Конструктор» буде неактивна.
На робочому аркуші автофільтр можна застосувати одночасно лише до одного діапазону даних; якщо потрібно кілька діапазонів, тоді діапазони слід перетворити на таблиці.
Слід зазначити, що автофільтр підлаштовується під вміст: наприклад, дати зручно згруповані за роками і місяцями.

Excel дозволяє не просто відзначити потрібні значення/групи значень для фільтрації, а налаштувати більш-менш складні фільтри, наприклад, відобразити лише числа більше за середнє або діапазон дат; для тексту це може бути пошук працівників за іменем, наприклад, щоб привітати з Днем Тетяни тощо. Останнім пунктом у додаткових опціях автофільтра йде «Користувацький фільтр».

За допомогою користувацького фільтра можна налаштувати два умови через логічне «І» або «АБО».
Для прикладу відобразимо в таблиці всі результати бігу на 100 м зі значеннями менше 11 с. або більше 12,5 с.

Автофільтр, як і сортування, можна виконувати не лише за даними в клітинках, а й за атрибутами клітинок, наприклад, відобразити лише клітинки зеленого кольору тощо; також фільтр чудово поєднується зі сортуванням, тому можна відфільтрувати значення, а потім їх відсортувати, або навпаки.
Розширений фільтр
Якщо можливостей автофільтра недостатньо (наприклад, у тому ж користувацькому фільтрі можна налаштувати лише два умови для одного стовпця), можна застосувати розширений фільтр: у ньому немає обмеження на кількість умов і їх поєднань. З іншого боку, використання розширеного фільтра дещо складніше, оскільки його застосування не таке очевидне.
Перед використанням розширеного фільтра потрібно підготувати область, рівну за кількістю стовпців вихідній таблиці — з тими самими заголовками — і за кількістю рядків, рівною кількості умов з логічним «АБО». Наприклад, щоб скласти таку умову: «потрібно знайти всіх, хто підтягнувся більше 20 разів, або пробіг 100 метрів швидше за 11 секунд, або пробіг 3000 м швидше за 12:00», нам знадобиться 3 рядки, не рахуючи рядка заголовків; водночас, якби всі умови були пов'язані логічним «І», то потрібен був би лише один рядок для цих умов.


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

Очищення фільтра відбувається так само, як і з автофільтром. Серед додаткових опцій можна відзначити можливість розміщувати відфільтрований діапазон в іншому місці — іноді це зручно. Також слід звернути увагу на нюанс, пов'язаний із місцем для самих умов: у нашому прикладі вони розміщені під таблицею; якби таблицю потрібно було поповнювати записами, це спричинило б певні незручності. Водночас, якщо розмістити умови поруч з вихідним діапазоном, то в процесі фільтрації можуть ховатися рядки з умовами, якщо вони перебуватимуть у тих самих рядках, що й відфільтровані рядки вихідного діапазону. На правильність фільтрації це не вплине, але візуально може дезорієнтувати. Щоб цього уникнути, слід знати, що умови можна розміщувати на іншому аркуші, або ж фільтрувати значення не на місці, а поміщати їх у нове місце.
6. Групування і підведення підсумків
Відеоверсія
Текстова версія
Групування зустрічається в житті скрізь, найпростіший приклад – школа, коли учнів згруповано за класами та роками навчання. За допомогою групування аналізують не окремі елементи, а групи елементів: середня успішність за класами, за роками навчання в школі, дохід за місяцями, кварталами, роками тощо.
В Excel можна згрупувати як стовпці, так і рядки; при цьому сама групування може бути виконана вручну, самостійно відібравши потрібну кількість рядків/стовпців, або автоматично. Оскільки більшість табличних даних має вертикальне представлення, то групування за рядками зустрічається частіше, ніж за стовпцями.
В автоматичному режимі вихідні дані повинні бути підготовлені, щоб Excel знав, де закінчується поточна група і починається наступна.
Групуємо дані вручну
Інструменти для групування в Excel знаходяться на вкладці "Дані" у групі "Структура".

У ручному режимі виділяється довільна кількість записів і натискається команда "Групувати"; при цьому слід врахувати, що дві групи не можуть бути поруч, бо в цьому випадку застосування команди групування для наступної групи просто додасть дані до першої групи.

Застосувавши команду групування для наступного номера, отримаємо структуровані дані. Числа у верхньому лівому куті показують кількість рівнів у структурі, кліки по цим числам дозволяють згортати/розгортати рівні; те саме можна зробити за допомогою спеціальних кнопок у групі "Структура".

Створення структури в автоматичному режимі
Як правило, групування вручну ніхто не робить, бо по-перше, воно не підходить для великих обсягів, а невеликі обсяги інформації особливого сенсу групувати немає; по-друге, такі групи зазвичай містять певні підсумки (сума, середнє, кількість тощо), і в цьому випадку дані можна згрупувати автоматично в кілька кліків.
Видалимо структуру командою "Розгрупувати/ Видалити структуру", а на місцях створених пустих рядків розрахуємо середній час пробігу по раундах; також цю рядок можна візуально виділити, наприклад, оформивши її стилем клітинки.
На цьому підготовчі заходи можна вважати завершеними; виконання команди: "Групувати/ Створити структуру" одразу створить дві групи, правильно об'єднавши результати по раундах.

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

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

Для операції підведення проміжних підсумків у підсумкових рядках було додано звичайну функцію ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTAL), роботу з якою ми розглядали в восьмому занятті; тому просто змінюємо аргумент для обчислюваної функції з 1 на 3 і отримуємо операцію підрахунку клітинок із значеннями, а не середнє, яке для текстових значень обчислити неможливо.
7. Основи роботи з макрокомандами
Відеоверсія
Текстова версія
Макрокоманда або макрос – це послідовність дій, заздалегідь визначена користувачем і записана мовою програмування Visual Basic for Application. В Excel існує два типи макросів: процедура та функція-процедура.
Процедура виконує послідовність дій із різними об'єктами Excel (клітинками, аркушами, книгами), а процедура-функція фактично є користувацькою функцією; іноді їх називають UDF-функціями (User Defined Functions).
Запис макроса за допомогою макрорекордера
Команди роботи з макросами знаходяться на вкладці "Розробник", за замовчуванням вона не ввімкнена, тому увімкніть її в параметрах стрічки.

Найважливішою опцією, на яку слід звернути увагу при простому записі макросів, є перемикач "Відносні посилання". Різницю найпростіше зрозуміти на прикладі перенесення значення клітинки. Якщо записати макрос, який просто переносить значення з клітинки A1 у клітинку E5, то застосування цього макроса для будь-якої клітинки листа буде переносити значення в клітинку E4. При увімкненому перемикачі перенос відбуватиметься на 4 клітинки вниз і на 4 ліворуч, тобто клітинка A2 буде перенесена в клітинку E6.

Під час запису макросів слід розуміти, що Excel записує кроки, які впливають на вміст аркуша, а не самі команди, які призводять до виконання цих кроків. Наприклад, якщо ви зробите текст напівжирним, скасуєте дію, потім курсивом, скасуєте дію і, нарешті, підкреслите, Excel запише лише "Зробити текст підкресленим" — інші ваші кроки не потраплять до запису. Тому, якщо під час запису макроса ви зробили помилковий крок, сміливо натискайте скасувати — це в запис не потрапить.
Запис макроса виконується за допомогою однойменної команди у групі "Код"; при цьому з'явиться діалогове вікно, у якому можна задати ім'я макроса, призначити гаряче поєднання клавіш для нього, місце збереження самого коду макроса та опис.

Серед основних обмежень імені макроса слід зазначити, що воно не може починатися з цифри і містити пробілів, які краще замінити символом підкреслення.
Гаряче поєднання клавіш варто використовувати лише якщо макрос часто застосовується. Що стосується місця збереження макросів, то це може бути лише поточна книга — у цьому разі макрос буде доступний лише в поточній книзі. При виборі опції "Нова книга" макрос буде доступний у будь-якій новій книзі, яка створюватиметься на поточному комп'ютері. "Особиста книга макросів" – це спеціальна книга Excel, що використовується для збереження макросів. Опис — опціональна характеристика макроса.
Якщо потрібно швидко записати макрос, не обов'язково переходити на вкладку Розробник: кнопка запису макроса доступна на панелі стану.

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

Управління безпекою
Макроси — це, по суті, мініпрограми, які виконуються у середовищі програм Microsoft Office. Незважаючи на це, вони можуть виконувати дії не лише в межах книги, а й здійснювати певні операції в системі. Існує цілий клас шкідливих програм, що називаються макровірусами — це «шкідливі» макроси.
Для розмежування простих книг і книг із підтримкою макросів останні мають інше розширення *.xlsm.

До файлів із підтримкою макросів, отриманих з Інтернету, слід ставитись особливо обережно. Водночас у параметрах Excel є можливість керувати тим, як буде запускатися активний вміст у книгах.
Діалогове вікно "Центр управління безпекою" можна відкрити командою "Безпека макросів" на вкладці "Розробник" або через Файл/ Параметри Excel/ Центр управління безпекою/ Параметри центру управління безпекою.

8. Створення користувацької функції
Відеоверсія
Текстова версія
У попередньому питанні ми згадували, що є процедури, а є процедури-функції. Якщо відкрити код макроса в редакторі VBA, це можна зробити за командою "Visual Basic" на вкладці "Розробник", а можна через діалогове вікно "Макрос", натиснувши "Увійти" і зайшовши в один із макросів.
Незалежно від локалізації Microsoft Office редактор макросів англійською мовою

У вікні редактора макросів кожний окремий макрос відокремлюється службовими словами "Sub" і "End Sub"; усе, що знаходиться між цією конструкцією, належить до макроса і називається процедурою.
Процедури-функції (або просто функції)
Процедури виконують певні дії на аркуші, а процедури-функції, як уже згадували, по суті є користувацькими функціями.
Відмінність процедур-функцій у тому, що конструкція для них починається службовим словом "Function" і закінчується "End Function", крім того функція ще повертає значення як аргумент.
Для створення нової функції виконаємо команду Вставлення/Процедура. У діалоговому вікні налаштуємо параметри: ім'я, виберемо тип Function, а також задамо видимість (залишимо за замовчуванням Public).

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

Вся користувацька функція Cube готова, вона буде повертати куб числа.

Ось так, відносно просто можна створити власну функцію. Єдине, що потрібно пам'ятати — місце, куди ви зберігаєте свою функцію, яка є підвидом макроса. Наприклад, якщо обрана поточна книга, то створивши нову порожню книгу ваша функція буде недоступна.
Флешкартки
Так звані картки пам'яті: дивитесь на картку й намагаєтеся відповісти; при кліку на картку відобразиться правильна відповідь. Картки пам'яті хороші для запам'ятовування ключових позицій заняття. Усі заняття цього курсу забезпечені картками пам'яті.
Практика
У практичній частині ви знайдете завдання за пройденим заняттям. Після їх виконання у вас є можливість порівняти свій варіант із підготовленою викладачем відповіддю. Настійно рекомендовано дивитися розв'язок лише після того, як ви виконали завдання самостійно. Для деяких завдань є невеликі підказки.
Завдання 1. Робота з розумними таблицями
- Відкрити книгу "Заняття 10 старт" на аркуші "Практика 10.1".
- Початковий діапазон відформатувати як таблицю.
- Дати ім'я таблиці "Olympics".
- За допомогою стандартного інструментарію розумної таблиці і зрізів визначити:
- середній час забігу;
- середній час забігу в першому раунді;
- середній час забігу в другому раунді;
- середній час забігу країни USA в другому раунді;
- загальний час забігу для USA в першому раунді.
Підказка
Для швидкого знаходження показників слід використовувати рядок підсумків і зрізи
- Зберегти книгу під іменем "Заняття 10.1 фініш."
Переглянути розв'язок
Завдання 2. Імпорт даних на робочий аркуш
- Імпортувати результати тесту, що зберігаються в csv файлі на робочий аркуш.
- Відформатувати дані таблицею і відповісти на питання: "Скільки користувачів відповіли на перше питання тесту 'Відмінностей немає'?"
- Зберегти книгу під іменем "Заняття 10.2 фініш."
Переглянути розв'язок
Завдання 3. Контроль правильності даних
- Відкрити книгу з попереднього завдання "Заняття 10.2 фініш."
- На новий аркуш імпортувати таблицю "Замір 1" з БД Access.
- Створити перелік ПІБ із наявних співробітників для подальшого заповнення їх зі випадаючого меню.
- Оформити джерело ПІБ як таблицю і додати до неї два нові записи (довільні ПІБ, яких немає в списку).
- Для стовпця з підтягуваннями встановити правило, що кількість підтягувань не може бути від'ємною.
- Додати двох нових учасників з використанням випадаючого меню.
- За допомогою перевірки знайти неправильні (від'ємні) значення в стовпці з підтягуваннями.
- Зберегти книгу під іменем Задання 10.3 фініш.
Переглянути розв'язок
Завдання 4. Фільтрація даних і сортування
- Відкрити книгу з попереднього завдання Задання 10.3 фініш.
- Відсортувати учасників за кількістю підтягувань (більше підтягувань зверху списку); якщо два учасники мають однакову кількість підтягувань, то подальше сортування виконати за бігом на 100 метрів (менше значення зверху списку).
- За допомогою розширеного фільтра знайти всіх, хто підтягнувся 20 разів і більше або пробіг 100 метрів менш ніж за 11 секунд.
- Зберегти книгу під іменем Задання 10.4 фініш.