Ако трябва да сортирате списъка, тогава има много начини на ваше разположение, най-лесният от които е бутоните за сортиране в раздела или в менюто Дата (Данни — Сортиране). Има обаче ситуации, когато сортирането на списъка трябва да се извърши автоматично, т.е. формули. Това може да е необходимо, например, когато генерирате данни за падащ списък, когато изчислявате данни за диаграми и т.н. Как да сортирате списък с формула в движение?
Метод 1. Числени данни
Ако списъкът съдържа само цифрова информация, тогава сортирането му може лесно да се извърши с помощта на функциите ПОНЕ (МАЛЪК) и LINE (РЕД):
функция ПОНЕ (МАЛЪК) изважда от масива (колона A) n-тия най-малък елемент в ред. Тези. SMALL(A:A;1) е най-малкото число в колоната, SMALL(A:A;2) е второто най-малко и т.н.
функция LINE (РЕД) връща номера на реда за посочената клетка, т.е. ROW(A1)=1, ROW(A2)=2 и т.н. В този случай той се използва просто като генератор на поредица от числа n=1,2,3... за нашия сортиран списък. Със същия успех беше възможно да се направи допълнителна колона, да се попълни ръчно с числовата последователност 1,2,3 ... и да се обърне към нея вместо функцията ROW.
Метод 2. Текстов списък и редовни формули
Ако списъкът не съдържа числа, а текст, тогава функцията SMALL вече няма да работи, така че трябва да отидете по различен, малко по-дълъг път.
Първо, нека добавим служебна колона с формула, където серийният номер на всяко име в бъдещия сортиран списък ще бъде изчислен с помощта на функцията COUNTIF (COUNTIF):
В английската версия ще бъде:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Първият член е функция за преброяване на броя клетки, които са по-малки от текущата. Второто е предпазна мрежа в случай, че някое име се среща повече от веднъж. Тогава те няма да имат еднакви, а последователно нарастващи числа.
Сега получените числа трябва да бъдат подредени последователно във възходящ ред. За целта можете да използвате функцията ПОНЕ (МАЛЪК) от първия начин:
Е, накрая остава само да извадим имената от списъка по техните номера. За да направите това, можете да използвате следната формула:
функция ПО-ИЗЛОЖЕН (СЪВПАДА) търси в колона B желания сериен номер (1, 2, 3 и т.н.) и всъщност връща номера на реда, където се намира този номер. функция INDEX (ИНДЕКС) изважда от колона A името на този номер на ред.
Метод 3: Формула за масив
Този метод всъщност е същият алгоритъм за поставяне като в метод 2, но реализиран чрез формула за масив. За да се опрости формулата, диапазонът от клетки C1:C10 получи името списък (изберете клетки, натиснете Ctrl + F3 и бутон Създаване на):
В клетка E1 копирайте нашата формула:
=ИНДЕКС(Списък; МАЧ(МАЛЪК(БРОЕАЛИ(Списък; “<"&Списък); РЕД(1:1)); БРОЕАЛИ(Списък; "<"&Списък); 0))
Или в английската версия:
=ИНДЕКС(списък, MATCH(SMALL(COUNTIF(списък, «<"&списък), РЕД(1:1)), COUNTIF(списък, "<"&списък), 0))
и натиснете Ctrl + Shift + Enterза да го въведете като формула за масив. След това получената формула може да бъде копирана по цялата дължина на списъка.
Ако искате формулата да вземе предвид не фиксиран диапазон, но да можете да коригирате, когато добавяте нови елементи към списъка, тогава ще трябва леко да промените стратегията.
Първо, обхватът на списъка ще трябва да бъде зададен динамично. За да направите това, когато създавате, трябва да посочите не фиксиран диапазон C3:C10, а специална формула, която ще се отнася до всички налични стойности, независимо от техния брой. Кликнете Alt + F3 или отворете раздела Формули – Мениджър на имена (Формули — Мениджър на имена), създайте ново име и в полето връзка (Референтен) въведете следната формула (предполагам, че диапазонът от данни за сортиране започва от клетка C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=ОТМЕСТ(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
Второ, горната формула за масив ще трябва да бъде разтегната надолу с марж – с очакване за въвеждане на допълнителни данни в бъдеще. В този случай формулата на масива ще започне да дава грешка #NUMBER в клетки, които все още не са попълнени. За да го прихванете, можете да използвате функцията АКО ГРЕШКА, който трябва да се добави „около“ нашата формула за масив:
=IFERROR(ИНДЕКС(списък; MATCH(SMALL(COUNTIF(списък; “<"&списък); РЕД(1:1)); COUNTIF(списък; "<"&списък); 0));»»)
=IFERROR(NDEX(списък, MATCH(SMALL(COUNTIF(списък, «<"&списък), РЕД(1:1)), COUNTIF(списък, "<"&списък), 0));"")
Той улавя грешката #NUMBER и вместо това извежда void (празни кавички).
:
- Сортирайте гамата по цвят
- Какво представляват формулите за масиви и защо са необходими
- SORT сортиране и динамични масиви в новия Office 365