Собака вивчає Excel! Фото Kyle Hanson на Unsplash
Ви починаючий аналітик даних? Якщо так, то вам варто вивчити Excel, як собаці на картинці вище!
Як аналітик даних, я використовую Excel для багатьох завдань: додавання чисел, обчислення медіани та середнього значення, знаходження мінімумів та максимумів тощо. Чим більше функцій Excel я вивчаю, тим ефективнішим стає мій аналіз.
Однак я продовжую повертатися до одних і тих же функцій. Якщо ви хочете бути на крок попереду в аналізі Excel, читайте далі і вивчайте мої найчастіше використовувані функції!
Нагадую, що перед запуском наведених нижче функцій потрібно вводити знак =. «=» сигналізує Excel про те, що ви збираєтеся почати вводити функцію.
Крім того, після застосування будь-якої функції перевірте результат, щоб переконатися в його точності!
Мої найкращі функції Excel (з прикладами)
MAX() / МАКС()
MAX() обчислює максимальне значення в діапазоні.
Щоб обчислити максимальне значення у стовпчику A, я набираю
=MAX(A2:A6)
=МАКС(A2:A6)
- «MAX» знаходить максимальне значення.
- «A2:A6» це діапазон комірок, які я хочу дослідити.
Це корисно для швидкого пошуку найбільшого значення у великому наборі даних.
MIN() / МІН()
MIN() обчислює мінімальне значення.
Щоб обчислити мінімальне значення у стовпчику A, я набираю
=MIN(A2:A6)
- «MIN» знаходить мінімальне значення.
- «A2:A6» це діапазон комірок, які я хочу дослідити.
SUMIF() / СУММЕСЛИ()
SUMIF() додає значення на основі одного або декількох критеріїв.
У наведеному нижче прикладі я маю список транзакцій продажу з двома стовпчиками: «товар (стовпець A)» і “сума продажу (стовпець B)”. Я хочу підрахувати загальну суму продажів для кожного товару: футболок, джинсів і піджаків.
Щоб обчислити загальну суму продажів для футболок, я вводжу
=SUMIF(A2:A6, «Футболка», B2:B6)
- «SUMIF» додає значення на основі певних критеріїв.
- «A2:A6» це діапазон, який містить критерії.
- «T-Shirt» це критерій, якому я хочу відповідати. Мене цікавить лише сума продажів футболок.
- «B2:B6» це діапазон зі значеннями, які я хочу додати (суми продажів).
Далі я хочу використати SUMIF() для підсумовування сум продажів джинсів. Для цього я включу «Джинси» як критерій:
=SUMIF(A2:A6, «Джинси», B2:B6)
- «SUMIF» додає значення на основі певних критеріїв.
- «A2:A6» це діапазон, який містить критерії.
- «Jeans» це критерій, якому я хочу відповідати. Мене цікавить лише сума продажів джинсів.
- «B2:B6» це діапазон зі значеннями, які я хочу додати (суми продажів).
AVERAGE() / СЕРЗНАЧ()
AVERAGE() обчислює середнє значення набору чисел.
У наведеному нижче прикладі я обчислив середнє значення для діапазону клітинок від A2 до A126:
=AVERAGE(A2:A126)
- «AVERAGE» знаходить середнє значення.
- «A2:A126» це розташування чисел.
MEDIAN() / МЕДИАНА()
MEDIAN() знаходить медіану набору чисел.
У наведеному нижче прикладі я обчислив медіану для діапазону клітинок від A101 до A113:
=MEDIAN(A101:A113)
- «MEDIAN» говорить, що я хочу знайти медіану.
- «A101:A113» це розташування номерів.
CONCAT() для об’єднання імен / ЗЧЕПИТИ()
CONCAT() об’єднує текст з декількох клітинок в одну.
Я постійно використовую функцію CONCAT(), найчастіше для об’єднання імен та прізвищ у стовпчику «повне ім’я». Це особливо корисно, якщо ви аналізуєте безладні дані опитування.
Щоб об’єднати стовпчики імен та прізвищ нижче, я набираю
=CONCAT(A2, « », B2)
- «CONCAT» об’єднує текст з різних клітинок.
- «A2» це перший текст, який показує (ім’я.)
- Я додав лапки з пробілом, щоб між іменем та прізвищем був пробіл. Якби я цього не зробила, повне ім’я виглядало б як «MarySmith».
- «B2» це останній текст, який показує (прізвище.)
Далі я перетягую формулу вниз по стовпчику і перевіряю результат на точність. Я бачу, що «Mary» і «Smith» правильно об’єдналися в «Mary Smith», «John» і «Reed» об’єдналися в «John Reed» і так далі.
CONCAT() для створення адрес електронної пошти
Ви можете використовувати CONCAT для створення адрес електронної пошти, якщо ви знаєте ідентифікатор електронної пошти та ім’я хоста. Наприклад, якщо у вас є список ідентифікаторів електронної пошти і ви знаєте ім’я хоста «@texas.edu».
Нижче я використовую цю формулу, щоб об’єднати ідентифікатор електронної пошти з іменем хоста і отримати повну адресу електронної пошти:
=CONCAT(A2, «@texas.edu»)
- «CONCAT» об’єднує текст з різних клітинок.
- «A2» це перша комірка, яку я хочу, щоб Excel переглянув. У цьому випадку це ідентифікатор електронної пошти mnh.
- «@texas.edu» додає це ім’я хоста в кінець тексту в A2.
Остаточний результат – [email protected], що є правильним!
Перетягніть цю формулу вниз, щоб застосувати її до решти стовпчика. Як завжди, перевірте результати на точність.
COUNT() / РАХУНОК()
COUNT() підраховує клітинки, що містять числові значення (включаючи дати і час, які зберігаються як числові значення). Вона ігнорує клітинки, що містять текст, порожні клітинки, логічні значення (TRUE/FALSE) і значення помилок (наприклад, #VALUE!, #DIV/0! і т.д.).
У наведеному нижче прикладі я підраховую кількість клітинок у стовпчику B, які містять числові значення (бали).
Я використовую цю формулу:
=COUNT(B2:B11)
- «COUNT» підраховує комірки з числовими значеннями.
- «B2:B11» вказує діапазон комірок для перевірки.
Він підраховує числові значення у стовпчику B і видає мені результат 7, який є правильним.
COUNTA() / РАХУНОКЗ()
COUNTA() рахує ВСІ непорожні клітинки, незалежно від їх типу даних.
Як це відрізняється від COUNT()?
COUNT() рахує тільки ті клітинки, які містять числові значення, тоді як COUNTA() рахує всі непорожні клітинки, незалежно від типу.
Якщо комірка містить значення «НЕЗАПОВНЕНА», функція COUNTA() включила б його до підрахунку, а функція COUNT() – ні.
Нижче наведено той самий список студентів і оцінок, що й у прикладі для COUNT(). Функція COUNT() дала мені результат 7, тому що вона НЕ порахувала «НЕЗАПОВНЕНІ» клітинки.
Функція COUNTA() видає результат 10, тому що вона включає «НЕЗАПОВНЕНІ» клітинки.
Давайте розглянемо формулу COUNTA більш детально.
Нижче я використовую формулу =COUNTA(B2:B11)
- «COUNTA» означає, що я хочу порахувати всі непорожні клітинки, незалежно від типу даних.
- «B2:B11» вказує комірки, які я хочу переглянути: числа в стовпчику B.
COUNTIF() / РАХУНОКЯКЩО()
COUNTIF() підраховує клітинки на основі одного або декількох критеріїв.
Наприклад, у мене є список імен і країн. Я хочу побачити, скільки учасників з кожної країни відвідали мою подію.
Для цього я створюю окрему таблицю і перераховую країни: Сполучені Штати, Іспанія, Бельгія та Австралія.
Я використовую функцію COUNTIF() для підрахунку кількості учасників зі Сполучених Штатів:
=COUNTIF(B2:B9, «United States»)
- «COUNTIF» підраховує клітинки на основі певних критеріїв.
- «B2:B9» це діапазон комірок, які я хочу дослідити.
- «Сполучені Штати» це той критерій, який я хочу шукати.
Це дає мені результат 3, який є правильним. 3 учасники приїхали зі Сполучених Штатів.
Потім я повторно вводжу формулу, використовуючи інші критерії, які я хочу шукати.
Для Іспанії я вводжу =COUNTIF(B2:B9, «Spain»)
Для Бельгії я вводжу =COUNTIF(B2:B9, «Belgium»)
Для Австралії я вводжу =COUNTIF(B2:B9, «Australia»)
IF() / ЯКЩО()
IF() дозволяє використовувати умовну логіку. Функція IF перевіряє, чи виконується умова, і повертає одне значення, якщо вона істинна, і інше, якщо хибна.
У наведеному нижче прикладі я маю список оцінок учнів у класі, і я хочу виставити оцінку на основі оцінки кожного учня. Я використовую формулу ЯКЩО для автоматизації цього процесу.
У колонці A – імена учнів, у колонці B – їхні бали.
У колонці С я виставляю оцінки на основі наступних критеріїв:
- 90+ = “A”
- 80–89: “B”
- 70–79: “C”
- Below 70: “D”
Щоб призначити ці оцінки, зробіть наступне:
- Створіть стовпчик C, куди будуть потрапляти оцінки.
- У клітинці C2 (якщо C2 – це місце, де ви хочете відобразити оцінку для першого студента) введіть таку формулу: =IF(B2 >= 90, «A», IF(B2 >= 80, «B», IF(B2 >= 70, «C», «D»)))
- Потім перетягніть цю формулу вниз уздовж списку оцінок учнів, щоб застосувати логіку оцінювання до всіх учнів.
- Обов’язково перевірте, як застосована формула і чи точні результати.
Розкладемо цю формулу на частини: =IF(B2 >= 90, «A», IF(B2 >= 80, «B», IF(B2 >= 70, «C», «D»)))
- IF(B2 >= 90, «A»,…) перевіряє, чи оцінка в комірці B2 більша або дорівнює 90. Якщо так, то присвоюється оцінка «A».
- IF(B2 > =80, «B»,…) Якщо результат не дорівнює 90 або вище, перевіряється, чи він більший або дорівнює 80. Якщо так, то виставляється оцінка «B».
- IF(B2 >= 70, «C», …) Якщо оцінка не дорівнює 80 або вище, перевіряється, чи вона більша або дорівнює 70. Якщо так, то виставляється оцінка «C».
- Якщо жодна з вищезазначених умов не виконана (тобто оцінка менше 70 балів), виставляється оцінка «D».
Як завжди, перевірте, що результат відповідає вашим очікуванням і що ви посилаєтесь на правильні комірки!
Фінальні думки
Швидше за все, якщо ви аналітик даних, ви будете використовувати Excel у своїй повсякденній роботі. Я часто використовую наведені вище функції, зокрема SUMIF(), MEDIAN(), СUМ() і AVERAGE(). Ці функції можуть зробити ваше життя набагато простішим, навіть якщо ви не аналітик даних!
Якщо ви хочете покращити свої навички роботи з Excel, перегляньте цю статтю:
- Занурення в Excel Maven Analytics
- Курс навчання Maven Analytics Excel Specialist
- Моя стаття на Medium, Топ-5 способів, як я використовую ChatGPT, щоб стати Excel ніндзя.
- Моя стаття на Medium, Топ-5 навичок очищення даних в Excel, які я використовую щодня.
Зміст зовнішніх матеріалів не обов’язково відображає погляди або роботу Maven Analytics або будь-кого з членів її команди.
Ми віримо у сприяння навчанню впродовж усього життя, і наш намір полягає в тому, щоб забезпечити платформу для спільноти аналітиків, щоб вони могли ділитися своєю роботою та отримувати зворотній зв’язок від сім’ї аналітиків Maven Analytics.
Надішліть свій власний текст тут, якщо ви хочете стати учасником.
Вдалого навчання!
-Команда Maven
ОРИГІНАЛ СТАТТІ:My Top Excel Functions for Data Analysis (with examples!)
АВТОР СТАТІ:MargaretEfron
🚀Долучайтесь до нашої спільноти Telegram:
🚀Долучайтесь до нашої спільноти FaceBook:
🚀Долучайтесь до нашої спільноти Twiter X:
