10 основних функцій Microsoft Excel для аналізу даних
Різне / / November 29, 2021
1. КОНКАТЕНАТИ
=CONCATENATE є однією з найважливіших функцій для аналізу даних, оскільки вона дозволяє комбінувати текст, числа, дати тощо. з кількох клітинок в одну. Ця функція особливо корисна для об’єднання даних з різних комірок в одну. Наприклад, він зручний для створення параметрів відстеження для маркетингових кампаній, створення запитів API, додавання тексту до числового формату та багатьох інших речей.
![Аналіз даних основних функцій Excel 1](/f/ef4844c9256f6aeeb7180f57b791172d.jpg)
У наведеному вище прикладі я хотів об’єднати місяць і продажі в одному стовпці. Для цього я використав формулу =CONCATENATE(A2, B2) у комірці C2, щоб отримати 700 січ.
Формула: =КОНКАТЕНАТИ (клітини, які потрібно об’єднати)
2. LEN
=LEN — це ще одна зручна функція для аналізу даних, яка по суті виводить кількість символів у будь-якій комірці. Ця функція переважно використовується під час створення тегів заголовків або описів, які мають обмеження на кількість символів. Це також може бути корисно, коли ви намагаєтеся з’ясувати відмінності між різними унікальними ідентифікаторами, які часто є досить довгими та не в правильному порядку.
![Аналіз даних основних функцій Excel 2](/f/d7cadade746a26cd545ab58daa0e9ad7.jpg)
У наведеному вище прикладі я хотів підрахувати кількість переглядів щомісяця. Для цього я використав формулу =LEN(C2) у клітинці D2, щоб отримати 5 в результаті.
Формула: =LEN(клітинка)
3. ВПР
=VLOOKUP, мабуть, є однією з найбільш впізнаваних функцій для тих, хто знайомий з аналізом даних. Ви можете використовувати його, щоб зіставити дані з таблиці з вхідним значенням. Функція пропонує два режими відповідності — точний і наближений, який контролюється діапазоном пошуку. Якщо встановити для діапазону значення FALSE, він шукатиме точну відповідність, але якщо встановити значення TRUE, він шукатиме приблизну відповідність.
![Аналіз даних основних функцій Excel 3](/f/6e053d7c4c477481f07d505cdf6a876f.jpg)
У наведеному вище прикладі я хотів дізнатися кількість переглядів за певний місяць. Для цього я використав формулу =VLOOKUP("Jun", A2:C13, 3) у комірці G4, і в результаті отримав 74992. Тут "Jun" - це значення пошуку, A2:C13 - це масив таблиці, в якій я шукаю "Jun", а 3 - це номер стовпця, в якому формула знайде відповідні перегляди для червня.
Єдиним недоліком використання цієї функції є те, що вона працює лише з даними, які були впорядковані в стовпці, звідси й назва — вертикальний пошук. Отже, якщо ви розташували свої дані в рядки, вам спочатку потрібно це зробити транспонувати рядки в стовпці.
Формула: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
4. ІНДЕКС/СПІВНИК
Подібно до функції VLOOKUP, функції INDEX і MATCH корисні для пошуку конкретних даних на основі вхідного значення. INDEX і MATCH, якщо використовуються разом, можуть подолати обмеження VLOOKUP щодо надання неправильних результатів (якщо ви не будете обережні). Тому, коли ви об’єднуєте ці дві функції, вони можуть точно визначити посилання на дані та шукати значення в масиві з одним виміром. Це повертає координати даних у вигляді числа.
![Аналіз даних основних функцій Excel 4](/f/b13227460bc7cb0fa84f9d41f380d98d.jpg)
У наведеному вище прикладі я хотів дізнатися кількість переглядів у січні. Для цього я використав формулу =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Тут A2:C13 - це стовпець даних, який я хочу, щоб формула повернула, "Jan" - це значення, яке я хочу знайти, A2:A13 - це стовпець, у якому формула знайде "Jan", а 0 означає, що я хочу, щоб формула знайшла точну відповідність для значення.
Якщо ви хочете знайти приблизний збіг, вам доведеться замінити 0 на 1 або -1. Таким чином, 1 знайде найбільше значення, менше або рівне значенню пошуку, а -1 знайде найменше значення, менше або дорівнює значенню пошуку. Зауважте, що якщо ви не використовуєте 0, 1 або -1, формула використовуватиме 1, by.
![Аналіз даних основних функцій Excel 5](/f/47d28f577f724d05e8f8ee098e91c6b7.jpg)
Тепер, якщо ви не хочете жорстко кодувати назву місяця, ви можете замінити його номером клітинки. Таким чином, ми можемо замінити "Jan" у формулі, згаданій вище, на F3 або A2, щоб отримати той самий результат.
Формула: =ІНДЕКС(стовпець даних, які ви хочете повернути, MATCH(загальна точка даних, яку ви намагаєтеся знайти, стовпець іншого джерела даних, що має загальну точку даних, 0))
5. MINIFS/MAXIFS
=MINIFS і =MAXIFS дуже схожі на функції =MIN і =MAX, за винятком того факту, що вони дозволяють брати мінімальний/максимальний набір значень і відповідати їм за певними критеріями. Таким чином, по суті, функція шукає мінімальні/максимальні значення та узгоджує їх із критеріями введення.
![Аналіз даних основних функцій Excel 7](/f/fae987f5603a9782d0f1136c8a385c01.jpg)
У наведеному вище прикладі я хотів знайти мінімальні бали на основі статі учня. Для цього я використав формулу =MINIFS (C2:C10, B2:B10, «M») і отримав результат 27. Тут C2:C10 – це стовпець, у якому формула шукатиме бали, B2:B10 – це стовпець, у якому формула шукатиме критерії (стать), а «M» – критерії.
![Аналіз даних основних функцій Excel 6](/f/e122c3fc7d8eafccda0a531139c4dea9.jpg)
Аналогічно, для отримання максимальної кількості балів я використав формулу =MAXIFS(C2:C10, B2:B10, "M") і отримав результат 100.
Формула для MINIFS: =МІНІФС(мінімальний_діапазон, критерій_діапазон1, критерій1,...)
Формула для MAXIFS: =MAXIFS(максимальний_діапазон, критерій_діапазон1, критерій1,...)
6. СЕРЕДНІ
Функція =AVERAGEIFS дозволяє знайти середнє значення для певного набору даних на основі одного або кількох критеріїв. Використовуючи цю функцію, слід пам’ятати, що кожен критерій і середній діапазон можуть відрізнятися. Однак у функції =AVERAGEIF і діапазон критеріїв, і діапазон сум повинні мати однаковий діапазон розмірів. Помітили різницю між цими функціями в однині та множині? Ну, ось тут треба бути обережним.
![Аналіз даних основних функцій Excel 8](/f/a2eb06f67342cde2eb6ee86ea6a8d351.jpg)
У цьому прикладі я хотів знайти середній бал на основі статі учнів. Для цього я використав формулу =AVERAGEIFS(C2:C10, B2:B10, "M") і отримав 56,8 в результаті. Тут C2:C10 – це діапазон, у якому формула буде шукати середнє значення, B2:B10 – діапазон критеріїв, а «M» – критерії.
Формула: =СРЕДНІЙКИ (середній_діапазон, критерій_діапазон1, критерій1,...)
7. COUNTIFS
Тепер, якщо ви хочете підрахувати кількість випадків, коли набір даних відповідає певним критеріям, вам потрібно буде використовувати функцію =COUNTIFS. Ця функція дозволяє додавати необмежені критерії до вашого запиту, і, таким чином, робить його найпростішим способом знайти кількість на основі критеріїв введення.
![Аналіз даних основних функцій Excel 9](/f/39635ab1eeffded1042cfd7c6249a7f2.jpg)
У цьому прикладі я хотів знайти кількість студентів, які отримали прохідні оцінки (тобто >=40). Для цього я використав формулу =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Тут B2:B10 – це діапазон, у якому формула шукатиме перші критерії (стать), «M» – це перший критерій, C2:C10 – це діапазон, у якому формула шукатиме другий критерій (мітки), а «>=40» – другий критерії.
Формула: =COUNTIFS(критерій_діапазон1, критерій1,...)
8. ПІДСУМНИЙ ПРОДУКТ
Функція =SUMPRODUCT допомагає вам помножити діапазони або масиви разом, а потім повертає суму добутків. Це досить універсальна функція, яку можна використовувати для підрахунку та підсумовування масивів, таких як COUNTIFS або SUMIFS, але з додатковою гнучкістю. Ви також можете використовувати інші функції в SUMPRODUCT, щоб ще більше розширити його функціональність.
![Аналіз даних основних функцій Excel 10](/f/f4d91eb407af82ac40d18cb36e6bbd13.jpg)
У цьому прикладі я хотів знайти загальну суму всіх проданих продуктів. Для цього я використав формулу =SUMPRODUCT(B2:B8, C2:C8). Тут B2:B8 – це перший масив (кількість проданих товарів), а C2:C8 – другий масив (ціна кожного товару). Формула потім множить кількість кожного проданого продукту з його ціною, а потім додає все це, щоб забезпечити загальний обсяг продажів.
Формула: =SUMPRODUCT(масив1, [масив2], [масив3],...)
9. TRIM
Функція =TRIM особливо корисна, коли ви працюєте з набором даних, який містить кілька пробілів або небажаних символів. Ця функція дозволяє з легкістю видалити ці пробіли або символи з даних, дозволяючи отримувати точні результати під час використання інших функцій.
![Аналіз даних основних функцій Excel 11](/f/04381c96e973bee237ac85395449540a.jpg)
У цьому прикладі я хотів видалити всі зайві пробіли між словами «Миша» та «падовик» у форматі A7. Для цього я використав формулу =TRIM(A7).
![Аналіз даних основних функцій Excel 12](/f/6d8e3d59bdbc3535366f7e4eeeedb7de.jpg)
Формула просто видалила зайві пробіли і доставила результат килимка для миші з одним пробілом.
Формула: =ОБРИМ(текст)
10. ЗНАЙТИ/ШУКАТИ
Заокругленням є функції FIND/SEARCH, які допоможуть вам виділити певний текст у наборі даних. Обидві функції дуже схожі за своєю діяльністю, за винятком однієї істотної відмінності — функція =FIND повертає лише збіги, чутливі до регістру. Між тим, функція =SEARCH не має таких обмежень. Ці функції особливо корисні під час пошуку аномалій або унікальних ідентифікаторів.
![Аналіз даних основних функцій Excel 13](/f/94b07268b0748d7e1ecddc2c94ec57ee.jpg)
У цьому прикладі я хотів знайти, скільки разів «Gui» з’являвся в Guiding Tech, для чого я використовував формулу =FIND(A2, B2), що дало результат 1. Тепер, якщо я хотів би знайти, скільки разів «gui» з’являвся в Guiding Tech замість цього, мені довелося б використовувати формулу =SEARCH, оскільки вона не чутлива до регістру.
Формула для пошуку: =ЗНАЙТИ(знайти_текст, всередині_тексту, [початкова_нумерація])
Формула пошуку: =ШУКАТИ(знайти_текст, всередині_тексту, [початкова_нумерація])