Урок на Excel: Как да получите n-ти стойности с SMALL и LARGE

Как да получите n-ти стойности с SMALL и LARGE

В това видео ще разгледаме как да изчислите n-тата най-малка или най-голяма стойност в диапазон, като използвате функциите SMALL и LARGE. Това би било например 1-ва, 2-ра и 3-та най-малка или най-голяма стойност.

В този първи лист имаме списък с ученици с 5 резултата от теста. Нека използваме функцията LARGE, за да извлечем най-добрите 3 резултата за всеки ученик.

За да получите най-високи стойности, използвайте LARGE. Функцията LARGE се нуждае от масив и нещо, наречено "k", което можете да помислите за "n-то".

Така че, в този случай просто ще посоча 5-те резултата за масив и ще въведа 1 за К. Това ни дава най-добрия резултат за първия ученик.

Сега, за да копирам формулата, трябва първо да заключа препратките към колоните за диапазона, за да не се променят.

След това мога да копирам формулата и да се върна, за да променя k, ако е необходимо.

Когато копирам всичките 3 формули надолу, получаваме най-добрия резултат за всеки ученик в списъка.

Така че, позволете ми да ви покажа няколко трика, които се справят с формулата k в тази ситуация …

Тъй като вече имаме число в заглавието на колоната, бих могъл просто да го използвам във формулата … заключващ ред 5.

Това ми позволява да копирам една и съща формула навсякъде.

Също така бих могъл да използвам функцията COLUMN за динамично изчисляване на k. КОЛОНА ни показва, че сме в колони 9,10 и 11.

И така, като извадим 1, можем да генерираме числата 1, 2 и 3.

Тогава мога да използвам тази конструкция директно във формулата навсякъде.

Сега да разгледаме функцията SMALL.

Тук имаме списък с резултатите от състезанието и искам да извлека най-добрите 3 пъти като цяло. Най-добрите времена са най-кратките, така че това е работа за функцията МАЛКИ.

Вече имам ранг в колона Е, използвайки функцията за ранг, който ще оставя за справка

Също така съм посочил диапазони за "имена" и "времена", за да улесня четенето на формули.

Малките работят точно като LARGE, така че ще използвам "пъти" за масив, след което просто вдигам k от колоната вляво.

Когато копирам формулата надолу, получаваме 3-те най-кратки или най-добри времена.

И така, може би се чудите как можем да получим име за всеки от тези времена?

За това, ако няма дубликати, можем да използваме INDEX и MATCH.

MATCH ще ни даде позиция изключена всеки път в списъка.

Тогава мога просто да взема тази формула и да я използвам директно в INDEX, за да извлека името.