Калі вам неабходна адсартаваць спіс, то да вашых паслуг мноства спосабаў, самы просты з якіх - гэта кнопкі сартавання на ўкладцы або ў меню Дата (Дадзеныя — Сартаванне). Аднак бываюць сітуацыі, калі сартаванне спісу трэба рабіць аўтаматычна, гэта значыць па формулах. Гэта можа спатрэбіцца, напрыклад, пры генерацыі даных для выпадальнага спісу, пры разліку даных для дыяграм і г. д. Як сартаваць спіс з формулай на хаду?
Спосаб 1. Лікавыя дадзеныя
Калі спіс утрымлівае толькі лікавую інфармацыю, то адсартаваць яго можна лёгка з дапамогай функцый МЕНШ (МАЛЫ) и ЛІНІЯ (РАДОК):
функцыя МЕНШ (МАЛЫ) выцягвае з масіва (слупок A) n-ы найменшы элемент у радку. Тыя. SMALL(A:A;1) - найменшы лік у слупку, SMALL(A:A;2) - другі па меншасці, і гэтак далей.
функцыя ЛІНІЯ (РАДОК) вяртае нумар радка для вызначанай ячэйкі, г.зн. ROW(A1)=1, ROW(A2)=2 і г.д. У гэтым выпадку ён выкарыстоўваецца проста як генератар паслядоўнасці лікаў n=1,2,3… для наш адсартаваны спіс. З такім жа поспехам можна было зрабіць дадатковы слупок, запоўніць яго ўручную лікавай паслядоўнасцю 1,2,3 … і звярнуцца да яго замест функцыі ROW.
Спосаб 2. Тэкставы спіс і звычайныя формулы
Калі ў спісе ёсць не лічбы, а тэкст, то функцыя МАЛЫ больш не будзе працаваць, таму трэба ісці іншым, крыху даўжэйшым, шляхам.
Спачатку дадамо службовы слупок з формулай, дзе парадкавы нумар кожнага імя ў будучым адсартаваным спісе будзе вылічвацца з дапамогай функцыі COUNTIF (COUNTIF):
У англійскай версіі гэта будзе:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Першы член - гэта функцыя для падліку колькасці вочак, меншых за бягучую. Другое - гэта ахоўная сетка на выпадак, калі якое-небудзь імя сустракаецца больш за адзін раз. Тады іх будзе не аднолькавая, а паслядоўна расце колькасць.
Цяпер атрыманыя лічбы неабходна паслядоўна размясціць па ўзрастанні. Для гэтага вы можаце выкарыстоўваць функцыю МЕНШ (МАЛЫ) з першага спосабу:
Ну і, нарэшце, засталося проста выцягнуць імёны са спісу па іх нумарах. Для гэтага можна выкарыстоўваць наступную формулу:
функцыя БОЛЬШ ВЫКЛЮЧАНЫ (МАТЧ) шукае ў слупку B патрэбны парадкавы нумар (1, 2, 3 і г.д.) і, уласна, вяртае нумар радка, дзе гэты нумар знаходзіцца. Функцыя індэкс (ІНДЭКС) выцягвае са слупка А імя ў гэтым нумары радка.
Спосаб 3: формула масіва
Гэты метад, па сутнасці, уяўляе сабой той жа алгарытм размяшчэння, што і ў метадзе-2, але рэалізаваны з дапамогай формулы масіва. Для спрашчэння формулы дыяпазон вочак C1:C10 атрымаў назву спіс (вылучыць клеткі, націснуць Ctrl + F3 і кнопка Ствараць):
У ячэйку E1 скапіруйце нашу формулу:
=ІНДЭКС(Спіс; СУПАВЕДЗЕННЕ(МАЛЫ(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)
=ЗРУШЭННЕ(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
Па-другое, прыведзеную вышэй формулу масіва трэба будзе расцягнуць з запасам - з разлікам на ўвядзенне дадатковых даных у будучыні. У гэтым выпадку формула масіва пачне выдаваць памылку #НУМЕР у незапоўненых вочках. Каб перахапіць яго, вы можаце выкарыстоўваць функцыю КАЛІПАМЫЛКА, які трэба дадаць «вакол» нашай формулы масіва:
=КАЛІПАМЫЛКА(INDEX(Спіс; MATCH(SMALL(COUNTIF(Спіс; “<"&Спіс); ROW(1:1)); COUNTIF(Спіс; "<"&Спіс); 0));»»)
=КАЛІПАМЫЛКА(NDEX(Спіс, СУПАДЗЕННЕ(МАЛЫ(COUNTIF(Спіс, «<"&Спіс), ROW(1:1)), COUNTIF(Спіс, "<"&Спіс), 0));"")
Ён выяўляе памылку #NUMBER і замест гэтага выдае пустыя двукоссі.
:
- Сартаваць дыяпазон па колеры
- Што такое формулы масіваў і для чаго яны патрэбныя
- Сартаванне SORT і дынамічныя масівы ў новым Office 365