Підручник Excel: Як отримати n-ті значення за допомогою SMALL і LARGE

Як отримати n-ті значення за допомогою SMALL і LARGE

У цьому відео ми розглянемо, як обчислити n-те найменше або найбільше значення в діапазоні за допомогою функцій SMALL і LARGE. Це можуть бути, наприклад, 1-е, 2-е і 3-те найменші чи найбільші значення.

У цьому першому аркуші ми маємо список студентів з 5 балами. Давайте використовуватимемо функцію LARGE для вилучення найкращих 3 балів для кожного учня.

Щоб отримати найвищі значення, використовуйте LARGE. Функції LARGE потрібен масив і щось під назвою "k", що можна сприймати як "n-ту".

Отже, у цьому випадку я просто вкажу на 5 балів для масиву та вводжу 1 для K. Це дає нам найкращий бал для першого учня.

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

Тоді я можу скопіювати формулу та повернутися, щоб змінити k за необхідності.

Коли я копіюю всі 3 формули вниз, ми отримуємо найкращий бал для кожного студента у списку.

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

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

Це дозволяє мені копіювати ту саму формулу скрізь.

Крім того, я міг би використовувати функцію COLUMN для динамічного обчислення k. Стовпець показує нам, що ми знаходимось у стовпцях 9,10 та 11.

Отже, віднімаючи 1, ми можемо генерувати числа 1, 2 і 3.

Тоді я можу використовувати цю конструкцію прямо у формулі скрізь.

А тепер давайте розглянемо функцію SMALL.

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

Я вже отримав рейтинг у стовпці E за допомогою функції рангу, який я залишу для довідки

Я також назвав діапазони для "імен" та "часів", щоб полегшити читання формул.

Невеликий працює так само, як LARGE, тому я буду використовувати "times" для масиву, а потім просто підберу k з стовпця ліворуч.

Коли я копіюю формулу, ми отримуємо 3 найкоротші чи найкращі часи.

Отже, ви можете задатися питанням, як ми можемо отримати назву для кожного з цих часів?

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

MATCH дасть нам позицію відключеної кожного разу у списку.

Тоді я можу просто взяти цю формулу і використати її безпосередньо всередині INDEX для отримання імені.