Шпаргалка формул Excel: Посібник середнього рівня

Отже, вам вдалося освоїти – хвала вам! Тепер ви вирішили підняти речі на вищий рівень і зануритися в більш просунуті речі, чи не так? Що ж, вам пощастило, тому що ми зібрали для вас зручну шпаргалку Excel середнього рівня!

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

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

Тепер не забудьте прочитати, але також роздрукуйте та збережіть шпаргалку нижче!

Давайте розпочнемо!

Зміст

• Математичні формули середнього рівня
  • Умовна логіка
• Проміжні статистичні формули
  • 1. MINA і MAXA
  • 2. COUNTA і COUNTIF
• Формули Excel для фінансового аналізу
  • 1. Функція PRODUCT
  • 2. Функції QUOTIENT і LOG
  • Функції для інвестиційних сценаріїв
• Проміжні формули дати та часу Excel
  • 1. Функції проміжного місяця
  • 2. Проміжні функції тижня
  • 3. Проміжні функції часу
• Функції Excel для умовної та логічної алгебри
  • 1. Функція IF
  • 2. Функція І
  • 3. Функція АБО
  • 4. Функція НЕ
• Посилання на клітинки
  • 1. Функція INDIRECT
  • 2. Функція INDEX
  • 3. Функція OFFSET
• Проміжні текстові формули
  • 1. Функція TEXTJOIN
  • 2. Функція REPLACE
  • 3. Функція SUBSTITUTE
• Проміжні формули пошуку
• Формули обробки помилок Microsoft Excel
  • 1. Функція IFERROR
  • 2. Функція IFNA
• Ярлики формул і функцій
• Проміжне форматування комірки
• Заключні думки

Математичні формули середнього рівня

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

• ABS() повертає абсолютне значення числа.

• SQRT() повертає квадратний корінь із числа

• RAND() надає випадкове число від 0 до 1

Умовна логіка

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

• SUMIFS(діапазон_суми, діапазон_критерію1, критерій1, [….])

• COUNTIFS(діапазон_критерію1; критерій1; [діапазон_критерію2; критерій2], …)

• AVERAGEIFS(діапазон_критерію1; критерій1; [діапазон_критерію2; критерій2], …)

Функції оцінюють надані критерії та створюють логічне значення TRUE або FALSE.

Припустімо, у стовпцях A, B і C є такі дані про продажі:

Щоб обчислити суму продажів червоних футболок, скористайтеся формулою SUMIFS:

=SUMIFS(C2:C6; A2:A6; «Футболка»; B2:B6; «Червоний»)

Щоб підрахувати кількість рядків із червоними футболками, скористайтеся формулою СЧІСЛИВО:

=COUNTIFS(C2:C6; A2:A6; «Футболка»; B2:B6; «Червоний»)

Щоб обчислити середні продажі червоних футболок, використовуйте формулу AVERAGEIFS:

=AVERAGEIFS(C2:C6; A2:A6; «Футболка»; B2:B6; «Червоний»)

Проміжні статистичні формули

Окрім основних функцій, таких як MIN і MAX, Excel має ширший набір статистичних формул для користувачів середнього рівня. Ось деякі з найкорисніших:

• МІНА

• МАКСА

• COUNTA

• COUNTIF

1. MINA і MAXA

Більш часто використовувані функції MIN і MAX ігнорують текстові значення.

Альтернативні функції MINA та MAXA враховують як текст, так і числа, коли шукають найвищі чи найнижчі значення. Текстові значення оцінюються як нульові.

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

2. COUNTA і COUNTIF

Функція COUNTA використовується в діапазоні.

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

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

Формули Excel для фінансового аналізу

Є кілька формул, які ви захочете використовувати під час виконання фінансового аналізу, наприклад прогнозування інвестицій:

• ПРОДУКТ(число1; [число2…])

• QUOTIENT(чисельник, знаменник)

• LOG(число; [основа])

1. Функція PRODUCT

Припустімо, ви хочете обчислити майбутню вартість початкових інвестицій у розмірі 1000 доларів США (комірка B1) за річною процентною ставкою 4% (комірка B2) через п’ять років (комірка B3).

Використовуйте функцію PRODUCT, щоб обчислити майбутню вартість за цією формулою:

=B1 * PRODUCT(1 + B2)^B3

Ця формула поверне майбутню вартість ваших інвестицій.

2. Функції QUOTIENT і LOG

Наступним кроком є ​​обчислення, скільки років знадобиться, щоб інвестиція подвоїлася за даною відсотковою ставкою.

Використовуйте функцію QUOTIENT у поєднанні з функцією LOG, щоб обчислити це таким чином:

=QUOTIENT(LOG(2) / LOG(1 + B2), 1)

Ця формула поверне 17 із зразковими даними, що вказує на те, що знадобиться 17 років, щоб інвестиція подвоїлася у вартості за річної відсоткової ставки 4%.

Ця картина :

Функції для інвестиційних сценаріїв

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

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

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

• INTRATE – функція INTRATE обчислює процентну ставку для повністю інвестованого цінного паперу.

• PMT – функція PMT обчислює загальну суму платежу за борговим цінним папером.

• IRR – функція IRR визначає внутрішню норму прибутку.

• YIELD – функція YIELD визначає прибутковість цінних паперів на основі процентної ставки, номінальної вартості та терміну погашення.

Проміжні формули дати та часу Excel

Основні функції дати й часу в Excel включають функції ЗАРАЗ і СЬОГОДНІ для поточної дати. Користувачі середнього рівня також повинні знати, як отримати компоненти з даної дати за допомогою:

• ДЕНЬ(дата)

• МІСЯЦЬ(дата)

• YEAR(дата)

Формула =MONTH(“23 квітня 2023″) поверне результат 4 для 4-го місяця. Так само функції DAY і YEAR повертатимуть 23 і 2023 відповідно.

1. Функції проміжного місяця

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

• EDATE(дата_початку, кількість_місяців)

• EOMONTH(дата_початку; кількість_місяців)

Наприклад, формула =EDATE("23 квітня 2023", 2) обчислить дату на два місяці пізніше.

Формула =EOMONTH(23 квітня 2023”, 2) обчислює кінець місяця через два місяці.

Результат – «30 червня 2023 року», який враховує, що в червні лише тридцять днів. Якщо вказати 3 місяці, результатом буде «31 липня 2023».

2. Проміжні функції тижня

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

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

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

=SUM(–(WEEKDAY(B1:B40,2)>5))

3. Проміжні функції часу

Функція YEARFRAC обчислює частку року між двома датами.

Функція TIMEVALUE використовується для перетворення часу, представленого у вигляді тексту, у десяткове число, яке представляє частку 24-годинного дня.

Результатом є порядковий номер Excel, де 1 означає повний 24-годинний день, 0,5 — 12 годин, 0,25 — 6 годин і так далі.

Припустімо, у вас є час у текстовому форматі «17:45» у клітинці A1, і ви хочете перетворити його на десяткове число. Це формула:

=TIMEVALUE(A1)

Значення 0,74 представляє частку 24-годинного дня, що минула за п'ятнадцять хвилин до шостої години.

Перейдіть до , щоб змінити формат комірки часу з формату часу на текстовий. Ви можете відформатувати це у відсотках, щоб було зрозуміліше.

Функції Excel для умовної та логічної алгебри

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

1. ЯКЩО(умова, значення_якщо_істина, значення_якщо_хибність):

2. І(умова1, умова2, …)

3. АБО(умова1, умова2, …)

4. NOT(умова1, умова2, …)

Функція IF оцінює умову та повертає різні значення залежно від того, істинна чи хибна умова.

Функції AND, OR і NOT перевіряють, які умови відповідають дійсності, і дозволяють приймати відповідні рішення.

Ось кілька прикладів на основі даних про продажі, які ми використовували раніше.

1. Функція IF

Припустімо, ви хочете відобразити текст «Низькі продажі», якщо продажі для товарів менше 15. Якщо продажі вищі, ви хочете відобразити «Високі продажі» в новому стовпці.

Скористайтеся цією формулою та скопіюйте її в інші рядки:

=IF(C2<15, «Низькі продажі», «Високі продажі»)

2. Функція І

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

=І(A3=”Футболка”, C3>=15)

3. Функція АБО

Припустімо, що ви хочете перевірити кілька умов і повернути TRUE, якщо будь-яка з умов виконується, і FALSE в іншому випадку, тоді ви скористаєтеся функцією АБО.

Наприклад, якщо ми маємо бали тесту в клітинках A1 і B1, і ми хочемо знати, чи один із цих балів перевищує 80, ми можемо використати: =OR( A1>80, B1>80) . Це поверне TRUE, якщо один із балів (або обидва) перевищує 80, і FALSE, якщо обидва бали 80 або менше.

4. Функція НЕ

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

=NOT(A2="Толстовка")

Посилання на клітинки

Початківець швидко ознайомиться з використанням абсолютних і відносних посилань, таких як $A$1 або B2. Користувачі середнього рівня повинні ознайомитися з використанням непрямих посилань, індексів і зсувів:

1. INDIRECT(текст_посилання)

2. INDEX(діапазон; номер_рядка; номер_стовпця)

3. OFFSET(посилальна_комірка, рядки, стовпці):

1. Функція INDIRECT

Функція INDIRECT повертає значення вказаного посилання на клітинку, введене як текст. Щоб посилатися на клітинку B3, використовуйте цю формулу:

=INDIRECT(“B3”)

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

2. Функція INDEX

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

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

=ІНДЕКС(B1:B6; 3)

3. Функція OFFSET

Функція ЗМІЩЕННЯ повертає клітинку або діапазон, який знаходиться на певній кількості рядків і стовпців від контрольної клітинки.

Якщо ви хочете відобразити значення клітинки, що складається з двох клітинок поперек і однієї клітинки вниз від A1, скористайтеся цією формулою:

=ЗМІЩЕННЯ(A1; 2; 1)

Ось формули в дії:

Проміжні текстові формули

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

1. TEXTJOIN(роздільник, ігнор_порожній, текст1, [текст2, …])

2. REPLACE(старий_текст, початковий_номер, кількість_символів, новий_текст)

3. SUBSTITUTE(текст, старий_текст, новий_текст, [номер_примірника])

1. Функція TEXTJOIN

Функція TEXTJOIN об’єднує клітинки з роздільником, який ви надаєте. Ви також можете вказати, де ігнорувати порожні клітинки.

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

=TEXTJOIN(“, “, TRUE, A2:A4)

2. Функція REPLACE

Функція заміни дозволяє вказати початкову позицію та довжину рядка, який потрібно замінити на цільовий.

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

=TEXTJOIN(A2; 1; 2; “XX”)

3. Функція SUBSTITUTE

Функція заміни замінює вказані входження текстового рядка в інший текстовий рядок новим текстовим рядком.

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

=ЗАМІНА(A3, «Джинси», «Легінси»)

Ось формули в дії:

Проміжні формули пошуку

Поширені функції пошуку, такі як VLOOKUP і MATCH, описані в шпаргалці для початківців.

Користувачі середнього рівня повинні ознайомитися з функцією пошуку CHOOSE:

• ВИБРАТИ(номер_індексу; значення1; [значення2; …])

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

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

=ВИБРАТИ(A1; «Малий», «Середній», «Великий»)

Формули обробки помилок Microsoft Excel

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

1. IFERROR(значення, якщо_помилка)

2. IFNA(значення, значення_якщо_на)

1. Функція IFERROR

Помилки ділення на нуль є звичайними, але Excel за замовчуванням відображає #DIV/0! не буде зрозуміло кожному користувачеві.

Скористайтеся функцією IFERROR, щоб замінити стандартну помилку на «Неможливо поділити на нуль» за цією формулою:

=ЯКЩОПОМИЛКА(A1/B1, «Неможливо поділити на нуль»)

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

2. Функція IFNA

Функція IFNA в Excel використовується для виявлення та обробки помилок #N/A.

Інший розділ цієї шпаргалки показував функцію ВИБРАТИ. Ця функція поверне помилку N/A, якщо номер індексу не є цілим числом.

Припустімо, що розмір футболки було введено як 1,5, і користувач помилково подумав, що це покаже категорію десь між малим і середнім. Натомість буде показано помилку #N/A .

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

=IFNA(ВИБРАТИ(A2; «Низький», «Середній», «Високий»), «Недійсний індекс»)

Ярлики формул і функцій

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

• F2 : відредагуйте активну комірку та розмістіть точку вставки в кінці вмісту комірки.

• F9 : обчислення та відображення результату вибраної частини формули.

• Ctrl + Shift + Enter : введіть формулу масиву.

• Shift + F3 : відкрити діалогове вікно «Вставити функцію».

• Esc : скасувати введення формули та повернутися до вихідного вмісту клітинки.

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

Проміжне форматування комірки

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

1. Виберіть клітинки, які потрібно відформатувати.

2. Клацніть правою кнопкою миші на вибраних клітинках і виберіть «Формат клітинок».

3. Виберіть відповідну категорію (наприклад, число, валюта, дата) і застосуйте потрібний формат.

Деякі поширені типи форматування, які слід враховувати у фінансах, включають:

• Валюта : $1,234.56

• Відсоток : 12,34%

• Бухгалтерський облік : ($1234,00)

• Дата : 10 травня 2023 р

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

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

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

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

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

Відчуваєте, що хочете підняти речі на новий рівень? Перевірте наш