Сортирање података уграђених у Екцел је невероватно, али није динамично. Ако сортирате податке, а затим им додате податке, мораћете да их сортирате поново.
Сортирајте податке по абецедном реду
У овом посту ћу вам показати различите начине сортирања података по абецедном реду помоћу формула. То значи да можете додати податке и он ће их аутоматски сортирати уместо вас.
Када су сви подаци Текст без дупликата
Претпоставимо да имате податке као што је приказано испод:
У овом примеру сви подаци су у текстуалном формату (без бројева, празнина или дупликата). Да бих ово сортирао, користићу помоћну колону. У колони поред података користите следећу формулу ЦОУНТИФ:
= ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & А2)
Ова формула упоређује текстуалну вредност са свим осталим текстуалним вредностима и враћа њен релативни ранг. На пример, у ћелији Б2 враћа 8, јер има 8 текстуалних вредности које су мање или једнаке тексту „САД“ (по абецедном реду).
Сада да сортирате вредности, користите следећу комбинацију функција ИНДЕКС, МАТЦХ и РОВС:
= ИНДЕКС ($ А $ 2: $ А $ 9, МАТЦХ (РОВС ($ Б $ 2: Б2), $ Б $ 2: $ Б $ 9,0))
Ова формула једноставно издваја имена по абецедном реду. У првој ћелији (Ц2) тражи се назив земље који има најмањи број (Аустралија има 1). У другој ћелији се враћа Канада (која има број 2) и тако даље …
Алергични на помоћне колоне ??
Ево формуле која ће учинити исто без помоћне колоне.
= ИНДЕКС ($ А $ 2: $ А $ 9, МАТЦХ (РОВС ($ А $ 2: А2), ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & $ А $ 2: $ А $ 9), 0))
Ово је формула низа, па користите Цонтрол + Схифт + Ентер уместо Ентер.
Оставићу вам да декодирате.
Пробајте сами … Преузмите датотеку за пример
Ова формула добро функционише ако имате текстуалне или алфанумеричке вредности.
Али јадно не успева ако:
- Имате дупликате у подацима (покушајте да двапут ставите УС).
- У подацима постоје празнине.
- Имате комбинацију бројева и текста (покушајте да ставите 123 у једну од ћелија).
Када су подаци мешавина бројева, текста, дупликата и празнина
Сада је ово мало зезнуто. Користићу 4 помоћне колоне да вам покажем како то функционише (а затим ћу вам дати огромну формулу која ће то учинити без помоћних колона). Претпоставимо да имате податке као што је приказано испод:
Можете видети да постоје дуплиране вредности, празно и бројеви. Зато ћу користити помоћне колоне за решавање сваког од ових проблема.
Колона помоћника 1
Унесите следећу формулу ЦОУНТИФ у колону 1 за помоћ
= ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & А2)
Ова формула чини следеће:
- Враћа 0 за празна поља.
- У случају дупликата, враћа исти број.
- Текст и бројеви се обрађују паралелно и ова формула враћа исти број за текст и број (на пример 123 и Индија добијају 1).
Колона помоћника 2
Унесите следећу ИС функцију у помоћну колону 2:
=-ИСНУМБЕР (А2)
Колона помоћника 3
Унесите следећу формулу у помоћну колону 3:
=-ИСБЛАНК (А2)
Колона помоћника 4
Унесите следећу формулу у помоћну колону 4
= ИФ (ИСНУМБЕР (А2), Б2, ИФ (ИСБЛАНК (А2), Б2, Б2+$ Ц $ 10))+$ Д $ 10
Идеја ове формуле је одвајање празнина, бројева и текстуалних вредности.
- Ако је ћелија празна, враћа вредност у ћелију Б2 (која би увек била 0) и додаје вредност у ћелију Д10. Укратко, вратиће укупан број празних ћелија у подацима
- Ако је ћелија нумеричка вредност, вратиће упоредни ранг и додати укупан број празнина. На пример, за 123 враћа 2 (1 је ранг 123 у подацима, а постоји 1 празна ћелија)
- Ако је то текст, враћа упоредни ранг и додаје укупан број нумеричких вредности и празнина. На пример, за Индију, додаје упоредни ранг текста у тексту (који је 1) и додаје број празних ћелија и број нумеричких вредности.
Коначни резултат - сортирани подаци
Сада ћемо користити ове помоћне колоне да бисмо добили сортирану листу. Ево формуле:
= ИФЕРРОР (ИНДЕКС ($ А $ 2: $ А $ 9, МАТЦХ (СМАЛЛ ($ Е $ 2: $ Е $ 9, РОВС ($ Ф $ 2: Ф2)+$ Д $ 10), $ Е $ 2: $ Е $ 9,0)) , "")
Овај метод сортирања сада постаје сигуран од будала. Показао сам вам методу за 8 ставки, али можете је проширити на онолико ставки колико желите.
Пробајте сами … Преузмите датотеку за пример
Једна формула за све сортирање (без помоћних колона)
Ако можете да рукујете екстремним формулама, ево свеобухватне формуле која ће сортирати податке по абецедном реду (без помоћне колоне).
Ево формуле:
= ИФЕРРОР (ИНДЕКС ($ А $ 2: $ А $ 9, МАТЦХ (СМАЛЛ (НОТ ($ А $ 2: $ А $ 9 = ""))*ИФ (ИСНУМБЕР ($ А $ 2: $ А $ 9), ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & $ А $ 2: $ А $ 9), ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & $ А $ 2: $ А $ 9)+СУМ (-ИСНУМБЕР ($ А $ 2: $ А $ 9))), РОВС ($ А $ 2: А2)+СУМ (-ИСБЛАНК ($ А $ 2: $ А $ 9))), НЕ ($ А $ 2: $ А $ 9 = "")*АКО (ИСНУМБЕР ($ А $ 2: $ А $ 9), ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & $ А $ 2: $ А $ 9), ЦОУНТИФ ($ А $ 2: $ А $ 9, "<=" & $ А $ 2: $ А $ 9)+СУМ (-ИСНУМБЕР ($ А $ 2: $ А $ 9))), 0)), "")
Унесите ову формулу у ћелију и превуците је надоле да бисте добили сортирану листу. Такође, пошто је ово формула низа, користите Цонтрол + Схифт + Ентер уместо Ентер.
Ова формула има корисност у стварном свету. Шта мислиш? Волео бих да учим од вас. Оставите своје трагове у одељку за коментаре!
