Ако треба да сортирате листу, онда вам је на располагању много начина, од којих је најлакши дугмад за сортирање на картици или у менију Датум (Подаци — сортирање). Међутим, постоје ситуације када је сортирање листе потребно извршити аутоматски, односно формула. Ово може бити потребно, на пример, када се генеришу подаци за падајућу листу, када се рачунају подаци за графиконе, итд. Како сортирати листу са формулом у ходу?
Метод 1. Нумерички подаци
Ако листа садржи само нумеричке информације, сортирање се може лако извршити помоћу функција ЛЕАСТ (МАЛА) и ЛИНЕ (РЕД):
функција ЛЕАСТ (МАЛА) извлачи из низа (колона А) н-ти најмањи елемент у низу. Оне. СМАЛЛ(А:А;1) је најмањи број у колони, СМАЛЛ(А:А;2) је други најмањи, и тако даље.
функција ЛИНЕ (РЕД) враћа број реда за наведену ћелију, тј. РОВ(А1)=1, РОВ(А2)=2 итд. У овом случају, користи се једноставно као генератор низа бројева н=1,2,3… за нашу сортирану листу. Са истим успехом било је могуће направити додатну колону, ручно је попунити нумеричким низом 1,2,3 … и упутити на њу уместо функције РОВ.
Метод 2. Листа текста и регуларне формуле
Ако листа не садржи бројеве, већ текст, онда функција СМАЛЛ више неће радити, тако да морате ићи другом, мало дужом, путањом.
Прво, додајмо сервисну колону са формулом у којој ће серијски број сваког имена у будућој сортираној листи израчунати помоћу функције ЦОУНТИФ (ЦОУНТИФ):
У енглеској верзији биће:
=ЦОУНТИФ(А:А,»<"&А1)+ЦОУНТИФ($А$1:А1,"="&А1)
Први појам је функција за бројање броја ћелија које су мање од тренутног. Друга је сигурносна мрежа у случају да се неко име појави више пута. Тада неће имати исти, већ сукцесивно све већи број.
Сада примљени бројеви морају бити поређани узастопно у растућем редоследу. За ово можете користити функцију ЛЕАСТ (МАЛА) од првог начина:
Па, коначно, остаје само да извучемо имена са листе по броју. Да бисте то урадили, можете користити следећу формулу:
функција ИЗЛОЖЕНИЈИ (УТАКМИЦА) тражи у колони Б жељени серијски број (1, 2, 3, итд.) и, заправо, враћа број реда у којем се овај број налази. Функција ИНДЕКС (ИНДЕКС) извлачи из колоне А име на овом броју реда.
Метод 3: Формула низа
Овај метод је, у ствари, исти алгоритам постављања као у Методу-2, али имплементиран помоћу формуле низа. Да би се формула поједноставила, опсег ћелија Ц1:Ц10 је добио име Листа (изаберите ћелије, притисните Цтрл + ФКСНУМКС и дугме Створити):
У ћелију Е1 копирајте нашу формулу:
=ИНДЕКС(Списак; ПОДРЖАВАЊЕ(МАЛО(БРОЈ(Листа; „<"&Листа); РЕД(1:1)); БРОЈ(Листа; "<"&Листа); 0))
Или у енглеској верзији:
=ИНДЕКС(Листа, ПОДРЖАВАЊЕ(МАЛО(БРОЈ(Листа, «<"&Листа), РЕД(1:1)), БРОЈ(Листа, "<"&Листа), 0))
и гурати Цтрл + Схифт + Ентерда га унесете као формулу низа. Затим се резултујућа формула може копирати низ целу дужину листе.
Ако желите да формула не узима у обзир фиксни опсег, већ да буде у могућности да се прилагоди када додајете нове елементе на листу, онда ћете морати мало да промените стратегију.
Прво, опсег листе ће морати да се подеси динамички. Да бисте то урадили, приликом креирања, потребно је да наведете не фиксни опсег Ц3:Ц10, већ посебну формулу која ће се односити на све доступне вредности, без обзира на њихов број. Кликните Алт + ФКСНУМКС или отворите картицу Формуле – Менаџер имена (Формуле — Менаџер имена), креирајте ново име и у пољу линк (Референца) унесите следећу формулу (претпостављам да опсег података за сортирање почиње од ћелије Ц1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=ОФФСЕТ(Ц1,0,0,СЦХОТЗ(Ц1:Ц1000),1)
Друго, горња формула низа ће морати да буде растегнута са маргином – уз очекивање додатних података који се уносе у будућности. У овом случају, формула низа ће почети да даје грешку #БРОЈ на ћелијама које још нису попуњене. Да бисте га пресрели, можете користити функцију ИФЕРРОР, који треба додати „око“ наше формуле низа:
=АКО ГРЕШКА(ИНДЕКС(Листа; ПОДРЖАВАЊЕ(МАЛО(БРОЈ(Листа; „<"&Листа); РЕД(1:1)); БРОЈ(Листа; "<"&Листа); 0));»»)
=ИФГРЕШКА(НДЕКС(Листа, МАТЦХ(МАЛЕ(БРОЈ(Листа, «<"&Листа), РОВ(1:1)), ЦОУНТИФ(Листа, "<"&Листа), 0));"")
Хвата грешку #НУМБЕР и уместо тога исписује празнину (празне наводнике).
:
- Сортирај опсег по боји
- Шта су формуле низа и зашто су потребне
- СОРТ сортирање и динамички низови у новом Оффице 365