Завантажте зразок робочої книги
Цей підручник продемонструє, як обчислити "велике якщо" або "маленьке якщо", отримавши n-е найбільше (або найменше) значення на основі критеріїв.
ВЕЛИКІ І МАЛІ функції
Функція LARGE використовується для обчислення n -го найбільшого значення (k) у масиві, тоді як функція SMALL повертає найменше n -е значення.
Для створення “Великого Якщо” ми будемо використовувати функцію LARGE разом з функцією IF у формулі масиву.
ВЕЛИКИЙ ЯКЩО
Комбінуючи LARGE (або SMALL) та IF у формулі масиву, ми можемо по суті створити функцію “LARGE IF”, яка працює подібно до того, як працює вбудована формула SUMIF. Пройдемося по прикладу.
У нас є перелік оцінок, досягнутих учнями з двох різних предметів:
Припустимо, нас попросять знайти три найкращі оцінки з кожного предмета так:
Для цього ми можемо вкласти функцію IF за допомогою предмет оскільки наші критерії всередині функції LARGE виглядають так:
= ВЕЛИКИЙ (ЯКЩО (=,),)
= ВЕЛИКИЙ (ЯКЩО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
Під час використання Excel 2022 та раніших версій потрібно ввести формулу, натиснувши CTRL + SHIFT + ENTER щоб обвести фігурні дужки навколо формули.
Як працює формула?
Формула працює, оцінюючи кожну клітинку в нашому діапазоні критеріїв як TRUE або FALSE.
Знаходження найвищої оцінки (k = 1) у математиці:
= ВЕЛИКИЙ (ЯКЩО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
= ВЕЛИКИЙ (IF ({TRUE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,81; 0,8; 0,93; 0,42; 0,87; 0,63; 0,71; 0,58; 0,73}), 1)
Далі, функція IF замінює кожне значення на FALSE, якщо її умова не виконується.
= ВЕЛИКИЙ ({0,81; FALSE; FALSE; 0,42; FALSE; 0,63; FALSE; 0,58; FALSE}, 1)
Тепер функція LARGE пропускає значення FALSE і обчислює найбільше (k = 1) з решти значень (0,81 - найбільше значення між 0,42 і 0,81).
МАЛИЙ ЯКЩО
Цю ж техніку можна замість цього застосувати і з функцією SMALL.
= МАЛЕНЬКИЙ (ЯКЩО ($ C $ 2: $ C $ 10 = $ F3, $ D $ 2: $ D $ 10), G $ 2)
ВЕЛИКИЙ, якщо з кількома критеріями
Щоб використовувати LARGE IF з кількома критеріями (подібно до того, як працює вбудована формула SUMIFS), просто вкладіть більше функцій IF у функцію LARGE так:
= ВЕЛИКИЙ (IF (=, IF (=,)),)
= ВЕЛИКИЙ (ЯКЩО ($ D $ 2: $ D $ 18 = $ H3, IF ($ B $ 2: $ B $ 18 = $ G3, $ E $ 2: $ E $ 18)), I $ 2)
Інший спосіб включити кілька критеріїв - помножити критерії разом, як показано в цій статті
Поради та хитрощі:
- По можливості, завжди посилайтеся на позицію (k) з допоміжної комірки та посилання на блокування (F4), оскільки це полегшить автоматичне заповнення формул.
- Якщо ви використовуєте Excel 2022 або новішу версію, ви можете ввести формулу без Ctrl + Shift + Enter.
- Щоб отримати імена студентів, які досягли найкращих оцінок, поєднайте це з ІНДЕКСНИМ МАТЧОМ
