содржина
Ако треба да ја подредите листата, тогаш има многу начини на располагање, од кои најлесниот се копчињата за сортирање на јазичето или во менито податоци (Податоци - Подреди). Сепак, постојат ситуации кога сортирањето на листата треба да се направи автоматски, односно формули. Ова може да биде потребно, на пример, кога се генерираат податоци за паѓачка листа, кога се пресметуваат податоци за графикони итн. Како да се сортира список со формула во лет?
Метод 1. Нумерички податоци
Ако списокот содржи само нумерички информации, тогаш сортирањето може лесно да се направи со помош на функциите ЛИСТ (МАЛО) и ЛИНИЈА (ROW):
функција ЛИСТ (МАЛО) го извлекува од низата (колона А) n-тиот најмал елемент по ред. Оние. SMALL(A:A;1) е најмалиот број во колоната, SMALL(A:A;2) е вториот најмал и така натаму.
функција ЛИНИЈА (ROW) го враќа бројот на редот за наведената ќелија, т.е. 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)
Првиот член е функција за броење на бројот на ќелии кои се помали од сегашната. Вториот е заштитна мрежа во случај некое име да се појави повеќе од еднаш. Тогаш нема да ги имаат истите, туку последователно зголемени бројки.
Сега примените броеви мора да се подредат последователно во растечки редослед. За ова можете да ја користите функцијата ЛИСТ (МАЛО) од првиот начин:
Па, конечно, останува само да се извлечат имињата од списокот по нивниот број. За да го направите ова, можете да ја користите следнава формула:
функција ПОИЗЛОЖЕНИ (натпревар) бара во колоната Б саканиот сериски број (1, 2, 3, итн.) и, всушност, го враќа бројот на линијата каде што се наоѓа овој број. Функција Индекс (ИНДЕКС) го вади од колоната А името на овој број на линија.
Метод 3: Формула на низа
Овој метод е, всушност, истиот алгоритам за поставување како во Метод-2, но имплементиран со формула за низа. За да се поедностави формулата, опсегот на ќелии C1:C10 го доби името листа (изберете ќелии, притиснете Ctrl + F3 и копче Креирај):
Во ќелијата Е1, копирајте ја нашата формула:
=INDEX(Список; MATCH(SMALL(COUNTIF(Список; „<"&Список); ROW(1:1)); COUNTIF(Список; "<"&Список); 0))
Или во англиската верзија:
=INDEX(Список, MATCH(SMALL(COUNTIF(Список, «<"&Список), ROW(1:1)), COUNTIF(Список, "<"&Список), 0))
и притисни Ctrl + Shift + Enterза да го внесете како формула за низа. Потоа добиената формула може да се копира по целата должина на списокот.
Ако сакате формулата да не земе предвид фиксен опсег, туку да може да се прилагоди кога додавате нови елементи на списокот, тогаш ќе треба малку да ја промените стратегијата.
Прво, опсегот Список ќе треба динамички да се постави. За да го направите ова, при креирањето, треба да наведете не фиксен опсег C3:C10, туку посебна формула која ќе се однесува на сите достапни вредности, без оглед на нивниот број. Кликнете Alt + F3 или отворете го јазичето Формули – Менаџер со имиња (Формули - Менаџер со име), креирајте ново име и во полето линк (Референца) внесете ја следната формула (претпоставувам дека опсегот на податоци што треба да се подредат започнува од ќелијата C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Второ, горенаведената формула за низа ќе треба да се протега надолу со маржа - со очекување за внесување дополнителни податоци во иднина. Во овој случај, формулата на низата ќе почне да дава грешка #NUMBER на ќелиите што сè уште не се пополнети. За да го пресретнете, можете да ја користите функцијата ИФЕРОР, што треба да се додаде „околу“ нашата формула за низа:
=IFERROR(INDEX(Список; MATCH(SMALL(COUNTIF(Список; „<“&Список); ROW(1:1)); COUNTIF(Список; „<“&Список; 0));»»)
=IFERROR(NDEX(Список, MATCH(SMALL(COUNTIF(Список, «<"&Список), ROW(1:1)), COUNTIF(Список, "<"&Список), 0));"")
Ја фаќа грешката #NUMBER и наместо тоа, излегува празнина (празни наводници).
:
- Подреди опсег по боја
- Што се формули на низи и зошто се потребни
- SORT сортирање и динамички низи во новиот Office 365