Шпаргалка формул Excel: Розширений посібник

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

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

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

Завантажте та роздрукуйте шпаргалку та тримайте її під рукою.

Гаразд, почнемо.

По-перше, давайте розберемося з формулами масиву.

Зміст

• Формули масивів
  • 1. УНІКАЛЬНА функція
  • 2. Функція SORT
  • 3. Функція FILTER
• Рандомізація функцій Excel
  • 1. Функція RANDBETWEEN
  • 2. Функція RANDARRAY
• Розширені формули прогнозування в Microsoft Excel
  • 1. Функція FORECAST.ETS
  • 2. Функція TREND
• Розширені статистичні формули
  • 1. Функція ПРОЦЕНТИЛЬ
  • 2. Функція КВАРТИЛЬ
• Розширений аналіз даних і формули маніпулювання
  • 1. Функція TRANSPOSE
  • 2. Функція FREQUENCY
  • 3. Функція GETPIVOTDATA
• Розширена обробка помилок
• Розширені формули пошуку
  • 1. Функція XLOOKUP
  • 2. Функція XMATCH
• Заключні думки

Формули масивів

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

Формули масиву дозволяють виконувати обчислення в кількох клітинках одночасно. Це три ключові функції:

1. УНІКАЛЬНИЙ

2. СОРТ

3. ФІЛЬТР

Деякі з цих функцій доступні лише в найновіших версіях Microsoft Excel.

1. УНІКАЛЬНА функція

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

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

=УНІКАЛЬНИЙ(A2:A6)

2. Функція SORT

Функція SORT сортує вміст діапазону. Синтаксис:

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

• масив : діапазон значень для сортування.

• sort_index : стовпець для сортування (1 за замовчуванням)

• sort_order : 1 для зростання (за замовчуванням) або 2 для спадання).

• by_col : TRUE для сортування за стовпцем (за замовчуванням) або FALSE для сортування за рядком.

Останні три аргументи є необов’язковими, і за замовчуванням зазвичай є те, що ви хочете.

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

=СОРТ(A2:C6)

3. Функція FILTER

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

=ФІЛЬТР(масив, включити, [якщо_порожній])

• масив : діапазон для фільтрації.

• include : умова, яка визначає, які значення фільтрувати.

• if_empty : визначає, що повертати, якщо жодне значення не відповідає критеріям фільтрації (за замовчуванням «»).

Припустімо, ви хочете відфільтрувати рядки у вибірці даних, щоб показати лише ті, де вартість продажів перевищує 15 доларів США. Використовуйте цю формулу:

=ФІЛЬТР(A2:C6; C2:C6>15)

Рандомізація функцій Excel

Наш проміжний код показує, як використовувати функцію RAND, яка створює випадкове число від 0 до 1.

Досвідчені користувачі Excel знають, як використовувати функції рандомізації для швидкого створення зразків даних.

1. Функція RANDBETWEEN

Функція RANDBETWEEN більш гнучка, ніж RAND, оскільки ви можете вказати нижнє та верхнє числа як щось інше, ніж 0 і 1.

Щоб створити дані з числами від 1 до 100, введіть цю формулу в клітинку A1:

=RANDBETWEEN(1; 100)

Потім скопіюйте клітинку в скільки завгодно рядків і стовпців. Для створення сітки рандомізованих чисел потрібні секунди:

2. Функція RANDARRAY

Можливо, ви думаєте, що було б непогано уникнути ручного копіювання функції RANDBETWEEN. Щоб отримати наддосконалий рівень, ви можете скористатися новою функцією RANDARRAY в останній версії Microsoft Excel.

Синтаксис:

RANDARRAY([рядки], [стовпці], [хв], [макс], [ціле число])

• rows : кількість рядків

• columns : кількість стовпців

• min : найменше число

• max : найбільше число

• ціле число : за замовчуванням значення TRUE, інакше використовуються десяткові числа.

Щоб згенерувати випадкові числа від 1 до 100 у шести рядках і дев’яти стовпцях, використовуйте цю формулу:

=ВИПАДКОВИЙ МИС (6; 9; 1; 100; ІСТИНА)

Розширені формули прогнозування в Microsoft Excel

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

1. Функція FORECAST.ETS

Старішу функцію ПРОГНОЗ було замінено набором нових функцій у Excel 2016.

Ви вибираєте функцію на основі конкретної моделі прогнозування, яка вам потрібна. Наприклад, функція FORECAST.ETS використовує алгоритм експоненціального згладжування .

Синтаксис:

FORECAST.ETS(цільова_дата, значення, шкала часу)

• target_date : дата, для якої потрібно отримати обчислене значення.

• значення : історичні дані.

• шкала часу : діапазон дат

Припустімо, у стовпці A є дати з 1 по 5 січня, а в стовпці B – обсяги продажів. Ця формула передбачить наступну суму продажів:

=FORECAST.ETS(“6 січня 2023”, B2:B6, A2:A6)

2. Функція TREND

Функція TREND проектує набір значень на основі методу найменших квадратів. Він повертає масив. Синтаксис:

ТРЕНД(відомий_y, [відомий_x], [новий_x], [const])

• відомий_y : діапазон значень y

• відомий_x : діапазон значень x

• new_x : діапазон обчислених значень

Часто відомі_y — це точки даних, а відомі_x — дати.

Використовуючи ті самі дані, що й у попередньому прикладі, ви можете ввести наведену нижче формулу в клітинку C2. Буде згенеровано набір значень.

=ТРЕНД(B2:B6; A2:A6)

Розширені статистичні формули

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

1. Функція ПРОЦЕНТИЛЬ

Ця функція обчислює відсоток точок даних, які падають нижче певного значення. Синтаксис:

ПРОЦЕНТИЛЬ.INC(масив, k)

• масив : діапазон клітинок

• k : процентиль від 0 до 1

Припустімо, ви хочете обчислити 70-й процентиль даних у стовпці B. Скористайтеся цією формулою:

=PERCENTILE.INC(B2:B6; 0,7)

2. Функція КВАРТИЛЬ

Ця функція є різновидом функції процентиля, але використовує чверті для поділу даних. Ось синтаксис:

QUARTILE.INC(масив, кварт)

• масив : діапазон даних

• кварта : 1 для 25-го процентиля, 2 для 50-го, 3 для 75-го і 4 для максимуму.

Формула нижче обчислить перший квартиль даних у стовпці A.

=QUARTILE.INC(A2:A5; 1)

Розширений аналіз даних і формули маніпулювання

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

1. ТРАНСПОНУВАТИ

2. ЧАСТОТА

3. ОТРИМАТИ ДАНІ

1. Функція TRANSPOSE

Іноді потрібно перемістити дані з рядків у стовпці і навпаки. Ви можете зробити це вручну або скористатися функцією TRANSPOSE.

Припустімо, у клітинках A2, A3 і A4 є елементи «Футболка», «Толстовка» та «Джинси». Ви хочете зробити це заголовками стовпців. Ця функція повертає значення в одному рядку:

=ТРАНСПОНУВАТИ (A2:A4)

2. Функція FREQUENCY

Ця функція обчислює частотний розподіл значень у наборі даних. Ось синтаксис:

FREQUENCY(масив_даних, масив_бінів)

• масив_даних : діапазон значень.

• bins_array : інтервали для використання.

Припустімо, у вас є дані про продажі в стовпці B, і ви хочете проаналізувати частотний розподіл значень на основі кількості сум:

• нижче 20.

• від 20 до 80.

• вище 80.

Це представляє три бункери і може бути обчислено за цією формулою:

=ЧАСТОТА(A2:A6; {20;80})

Щоб дізнатися більше про частотні розподіли в Excel, перегляньте це відео:

3. Функція GETPIVOTDATA

Ця функція дозволяє отримувати зведену інформацію зі зведених таблиць. Ось синтаксис:

GETPIVOTDATA(поле_даних, зведена_таблиця, [поле1, елемент1], [поле2, елемент2], …)

• data_field : поле даних або значення, яке потрібно отримати зі зведеної таблиці.

• pivot_table : посилання на зведену таблицю.

• field1, item1 тощо : пари поле/елемент для фільтрування.

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

=GETPIVOTDATA(“Продажі”, A1, “Колір”, “Червоний”)

Розширена обробка помилок

Навіть найпростіші формули Excel можуть давати помилки. Користувачі середнього рівня повинні знати, як використовувати ISERROR для обробки помилок. Досвідчені користувачі також повинні бути знайомі з функцією ERROR.TYPE для визначення помилок.

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

Він повертає числове значення, що відповідає різним типам помилок, наприклад #N/A, #VALUE!, #REF! тощо.

Припустімо, у комірці A1 є помилка, і ви хочете визначити її тип. Наступна формула поверне число, яке відповідає певній помилці:

=ПОМИЛКА.ТИП(A1)

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

1. #НУЛЬ! (у діапазоні не знайдено спільної комірки)

2. #DIV/0! ( ділення на нуль або порожню клітинку)

3. #VALUE! (невідповідний тип даних або аргумент у формулі)

4. #REF! (комірку, на яку посилається, видалено або є )

5. #ІМ'Я? (Excel не розпізнає функцію чи діапазон)

6. #NUM! (недійсне числове значення)

7. #N/A (значення не знайдено)

Припустімо, ви хочете обробити три конкретні типи помилок. Використовуйте цю формулу, щоб відобразити конкретне повідомлення про помилку на основі типу:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, «Помилка поділу на нуль», IF(ERROR.TYPE(C2)=3, «Помилка недійсного значення», IF(ERROR.TYPE(C2) )=7, «Помилка значення не знайдено», «Інша помилка»), C2)

Розширені формули пошуку

Наші шпаргалки для початківців і середнього рівня охоплювали . Ось кілька додаткових параметрів:

1. XLOOKUP

2. XMATCH

1. Функція XLOOKUP

Ця функція пошуку дозволяє шукати значення в діапазоні та повертати відповідне значення з іншого стовпця чи діапазону.

Він пропонує більшу універсальність, ніж простіші функції пошуку, такі як . Ось синтаксис:

XLOOKUP(пошукове_значення, пошуковий_масив, повернутий_масив, [режим_збігу], [режим_пошуку], [якщо_не_знайдено])

• lookup_value : значення, яке ви хочете знайти.

• lookup_array : діапазон для пошуку.

• return_array : діапазон, який показуватиме відповідне значення.

• match_mode : точна відповідність (0), наступна менша (1), наступна більша (-1) або відповідність символу підстановки (2).

• search_mode : -1 для пошуку зверху вниз, 1 для пошуку знизу вгору або 2 для бінарного пошуку.

• if_not_found : встановлює значення, яке повертається, якщо збігів не знайдено.

Припустімо, що ви хочете здійснити пошук у діапазоні даних для першого появи предмета одягу та повернути суми продажів. Ця формула шукатиме текст «Толстовка» та повертатиме значення в сусідній клітинці, якщо знайдено:

=XLOOKUP(“Толстовка”; A2:A6; B2:B6)

2. Функція XMATCH

Ця функція дозволяє знайти позицію вказаного значення в діапазоні або масиві. Ось синтаксис:

XMATCH(пошукове_значення, пошуковий_масив, [тип_збігу], [режим_пошуку])

• lookup_value : значення, яке ви хочете знайти.

• lookup_array : Діапазон, який потрібно шукати.

• match_type : точна відповідність (0), наступний найменший (-1), наступний найбільший (1).

• search_mode : бінарний пошук (1) або лінійний пошук (2).

Припустімо, ви хочете знайти перше входження жовтого елемента в діапазоні в стовпці B. Скористайтеся цією формулою:

=XMATCH(“Жовтий”, B2:B6, 0)

Заключні думки

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

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

Коли ви починаєте включати їх у свої завдання Excel, ви на шляху до покращення своїх навичок Excel до просунутого рівня.

Але пам’ятайте, що ця шпаргалка – лише верхівка айсберга. Справді дивовижні речі відбуваються, коли ви підходите до творчості, комбінуєте та поєднуєте ці формули та адаптуєте їх для вирішення ваших унікальних завдань. Excel схожий на полотно, а ці формули — ваша палітра — тож, малюйте свій шедевр!