Лекція 7. Аналіз даних в Google Sheets

Логічні функції

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

Нижче перераховано деякі з основних логічних функцій:

1. IF():

• IF(logical_expression, value_if_true, value_if_false) - Умовний оператор, який повертає одне значення, якщо вираз істинний, і інше, якщо вираз хибний

=IF(A1 > 10, "Більше 10", "Менше або рівно 10")

2. AND():

• AND(logical_expression1, logical_expression2, ...) - Повертає TRUE, якщо всі передані вирази є істинними.

=AND(A1 > 10, B1 < 20)

3. OR():

• OR(logical_expression1, logical_expression2, ...) - Повертає TRUE, якщо хоча б один з переданих виразів є істинним.

=OR(A1 > 10, B1 < 20)

4. NOT():

• NOT(logical_expression) - Повертає TRUE, якщо переданий вираз хибний, і FALSE, якщо вираз істинний.

=NOT(A1 > 10)

5. IFS():

• IFS(logical_expression1, value_if_true1, logical_expression2, value_if_true2, ...) - Повертає значення першого істинного виразу, або значення за замовчуванням.

=IFS(A1 > 10, "Більше 10", A1 > 5, "Більше 5", TRUE, "Інше")

6. SWITCH():

• SWITCH(expression, case1, value1, case2, value2, ...) - Повертає значення, пов'язане із першим відповідним виразом.

=SWITCH(A1, "один", 1, "два", 2, "три", 3, "Інше")

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

Функції AND, OR, IF, IFS

Формула if

Одна з найпопулярніших в Excel функція IF дає змогу виконувати логічне порівняння значення й очікуваного результату.

Отже, інструкція IF може мати два результати. Перший отримаємо, якщо умова виявиться істинною, другий – якщо вона буде хибна.

Поширені проблеми

Формула sumifs

Функція SUMIFS, яка належить до математичних і тригонометричних функцій, додає всі аргументи, які відповідають кільком критеріям. 

Повертає суму діапазону на основі кількох критеріїв =sumifs(C$5:C$29;B$5:B$29;F5; D$5:D$29;"Сплачено")

Поширені проблеми

Формула IFERROR

Формула SORT

Функція SORT сортує вміст діапазону або масиву за значеннями одного чи кількох стовпців 

Функція SORT повертає відсортований масив елементів. Повернутий масив матиме однаковий формат із наданим аргументом. 

=SORT(масив;[індекс_сортування];[порядок_сортування];[за_стовпцем])

Примітки: 

• Якщо аргумент "індекс_сортування" не вказано, дані сортуються за рядком 1 або за стовпцем 1. Якщо не вказано порядок, дані сортуються за зростанням. За замовчуванням Excel сортує дані за рядком. Сортування за стовпцем застосовується, лише якщо аргумент "за_стовпцем" має значення TRUE. Якщо аргумент "за_стовпцем" має значення FALSE або відсутній, Excel сортує дані за рядком.

• Функція SORT дає змогу сортувати дані в масиві. Якщо потрібно відсортувати дані в сітці, радимо використовувати гнучкішу функцію SORTBY. Функція SORTBY посилається на діапазон, тому враховує додані та видалені стовпці, а функція SORT посилається на номер індексу стовпця.

• Масив можна розглядати як рядок або стовпець значень чи поєднання рядків і стовпців значень. У наведеному вище прикладі діапазон A5:D20 – це вихідний масив формули SORT.

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

• Excel має обмежену підтримку перенесення динамічних масивів між книгами, і цей сценарій підтримується, лише якщо відкрито обидві книги. Якщо закрити вихідну книгу, формули пов’язаного динамічного масиву повертатимуть помилку #REF! під час оновлення.

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

Сортування даних – невід’ємна частина аналізу даних. Воно дає можливість відсортувати список імен в алфавітному порядку, скласти список продуктів за рівнем запасів (від найбільшого до найменшого) або впорядкувати рядки за кольорами чи піктограмами. Сортування даних дає змогу швидко переглядати дані й покращувати їх сприйняття, упорядковувати, знаходити потрібні дані та використовувати їх максимально ефективно.

Дані можна сортувати за текстом (від "А" до "Я" або від "Я" до "А"), числами (від найменшого до найбільшого та від найбільшого до найменшого) і датою й часом (від найстаршого до наймолодшого, від наймолодшого до найстаршого) в одному або кількох стовпцях. Їх можна також сортувати за самостійно створеним настроюваним списком (наприклад, "Великий", "Середній", "Маленький") або за форматом, зокрема за кольором клітинки чи шрифту або набором піктограм. 

Формула UNIQUE

Функція UNIQUE повертає унікальні рядки з вихідного діапазону, пропускаючи ті, що дублюються Google Таблиці Порядок рядків у новому діапазоні відповідає порядку їх розташування у вихідному діапазоні.

=UNIQUE(array,[by_col],[exactly_once])

Функція UNIQUE містить такі аргументи:

Формула QUERY

Формула QUERY виконує запит даних, написаний мовою запитів Google Visualization API Query Language

Power Query — це технологія підключення та підготовки даних, яка дозволяє кінцевим користувачам легко імпортувати та змінювати дані з широкого спектру продуктів Microsoft, зокрема Excel, Power BI, Analysis Services, Dataverse тощо.

Формула QUERY для суми з умовою групування. Підсумовує дані стовпця та групує їх по іншому стовпцю

Формула FILTER

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

Функція FILTER фільтрує значення масиву на основі масиву логічних (істина або хибність) значень.

=FILTER(масив;включити;[якщо_пустий])

Примітки.: 

• Масив можна розглядати як рядок або стовпець значень чи поєднання рядків і стовпців значень. У наведеному вище прикладі діапазон A5:D20 – це вихідний масив формули FILTER.

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

• Якщо існує ймовірність того, що за вашим набором даних функція поверне пусте значення, додайте третій аргумент ([якщо_пустий]). Інакше ви отримаєте помилку #CALC!, оскільки Excel поки не підтримує пусті масиви.

• Якщо будь-яке значення аргументу include є помилкою (#N/A, #VALUE тощо) або не може бути перетворено на логічне значення, функція FILTER поверне помилку.  

• Excel має обмежену підтримку перенесення динамічних масивів між книгами, і цей сценарій підтримується, лише якщо відкрито обидві книги. Якщо закрити вихідну книгу, формули пов’язаного динамічного масиву повертатимуть помилку #REF! під час оновлення.

Фільтрування даних в Google таблиці

Фільтрація даних забезпечує: 

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

Зведена таблиця

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

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

Виберіть Вставити > Зведена таблиця.

Поради та підказки щодо форматування даних

• Використовуйте чисті табличні дані, щоб отримати найкращі результати.

• Упорядковуйте дані в стовпцях, а не в рядках.

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

• Відформатуйте дані як таблицю Excel (виберіть будь-де в даних, а потім натисніть кнопку Вставити > таблиця на стрічці).

• Якщо у вас складні або вкладені дані, скористайтеся Power Query, щоб перетворити їх (наприклад, щоб відкріпити дані), щоб об'єднати їх у стовпці з одним рядком заголовка

Фільтрування даних

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

Візуалізована фільтрація — ЗРІЗИ

Групування або розгрупування даних у зведеній таблиці

Формула IMPORTRANGE

Формула IMPORTRANGE в Google Таблицях використовується для імпортування даних з одного аркуша (таблиці) в інший. Ця функція дозволяє вам звертатися до конкретного діапазону даних в іншому аркуші та автоматично оновлювати дані при зміні оригінального аркуша.

Синтаксис функції IMPORTRANGE виглядає так:

=IMPORTRANGE("ключ_аркуша", "діапазон")

• "ключ_аркуша": Це унікальний ідентифікатор аркуша, з якого ви хочете імпортувати дані. Ви можете знайти цей ключ у веб-адресі аркуша після "/d/" та перед "/edit".

• "діапазон": Це конкретний діапазон даних (наприклад, "Лист1!A1:B10") з аркуша, який ви хочете імпортувати.

Наприклад:

=IMPORTRANGE("ключ_аркуша", "Лист1!A1:B10")

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

Важливо, щоб аркуш, з якого ви хочете імпортувати дані, був відкритий для перегляду або доступу для конкретного користувача.