Efnisyfirlit
Ef þú þarft að flokka listann, þá eru margar leiðir til þjónustu þinnar, sú auðveldasta er flokkunarhnapparnir á flipanum eða í valmyndinni Gögn (Gögn — Raða). Hins vegar eru aðstæður þar sem flokkun listans þarf að fara fram sjálfkrafa, þ.e. formúlur. Þetta gæti verið nauðsynlegt, til dæmis þegar búið er til gögn fyrir fellilista, þegar gögn eru reiknuð fyrir töflur o.s.frv. Hvernig á að raða lista með formúlu á flugi?
Aðferð 1. Töluleg gögn
Ef listinn inniheldur aðeins tölulegar upplýsingar, þá er auðvelt að flokka það með aðgerðunum LESTA (LÍTILL) и LINE (ROW):
virka LESTA (LÍTILL) dregur út úr fylkinu (dálki A) n-ta minnsta þáttinn í röðinni. Þeir. SMALL(A:A;1) er minnsta talan í dálknum, SMALL(A:A;2) er næstminnsta, og svo framvegis.
virka LINE (ROW) skilar línunúmerinu fyrir tilgreinda reitinn, þ.e. ROW(A1)=1, ROW(A2)=2 o.s.frv. Í þessu tilviki er það einfaldlega notað sem rafall fyrir talnaröð n=1,2,3… fyrir flokkaður listi okkar. Með sama árangri var hægt að búa til viðbótardálk, fylla hann handvirkt með töluröðinni 1,2,3 … og vísa í hana í stað ROW fallsins.
Aðferð 2. Textalisti og venjulegar formúlur
Ef listinn inniheldur ekki tölur, heldur texta, þá mun SMALL aðgerðin ekki lengur virka, svo þú verður að fara aðra, aðeins lengri, leið.
Fyrst skulum við bæta við þjónustudálki með formúlu þar sem raðnúmer hvers nafns í framtíðarflokkuðum lista verður reiknað út með því að nota fallið COUNTIF (COUNTIF):
Í ensku útgáfunni verður það:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Fyrsta liðurinn er fall til að telja fjölda frumna sem eru færri en núverandi. Annað er öryggisnet ef eitthvað nafn kemur fyrir oftar en einu sinni. Þá munu þeir ekki hafa það sama, heldur vaxandi fjölda.
Nú þarf að raða mótteknum númerum í röð í hækkandi röð. Til þess geturðu notað aðgerðina LESTA (LÍTILL) frá fyrstu leið:
Jæja, loksins er bara eftir að draga nöfnin út af listanum eftir númerum þeirra. Til að gera þetta geturðu notað eftirfarandi formúlu:
virka FYRIR MEIRA (MATCH) leitar í dálki B að æskilegu raðnúmeri (1, 2, 3 o.s.frv.) og skilar í raun númeri línunnar þar sem þetta númer er staðsett. Virka INDEX (VÍSITALA) dregur út úr dálki A nafnið á þessu línunúmeri.
Aðferð 3: Fylkisformúla
Þessi aðferð er í raun sama staðsetningaralgrím og í Method-2, en útfærð með fylkisformúlu. Til að einfalda formúluna var svið frumna C1:C10 gefið nafnið Listi (veldu frumur, ýttu á Ctrl + F3 og hnappur Búa til):
Afritaðu formúluna okkar í reit E1:
=INDEX(listi; MATCH(SMALL(COUNTIF(listi; “<"&listi); ROW(1:1)); COUNTIF(listi; "<"&listi; 0))
Eða í enskri útgáfu:
=INDEX(List, MATCH(SMALL(COUNTIF(listi, «<"&listi), ROW(1:1)), COUNTIF(listi, "<"&listi), 0))
og ýta Ctrl + Shift + Sláðu inntil að slá það inn sem fylkisformúlu. Þá er hægt að afrita formúluna sem myndast niður alla lengd listans.
Ef þú vilt að formúlan taki ekki tillit til fasts bils, heldur geti stillt sig þegar nýjum þáttum er bætt við listann, þá þarftu að breyta aðeins stefnunni.
Í fyrsta lagi þarf að stilla listasviðið á virkan hátt. Til að gera þetta, þegar þú býrð til, þarftu ekki að tilgreina fast svið C3:C10, heldur sérstaka formúlu sem vísar til allra tiltækra gilda, óháð fjölda þeirra. Smellur Alt + F3 eða opnaðu flipann Formúlur – Nafnastjóri (Formúlur - Nafnastjóri), búa til nýtt nafn og í reitinn Link (Tilvísun) sláðu inn eftirfarandi formúlu (ég geri ráð fyrir að gagnasviðið sem á að flokka byrji frá reit C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX);XNUMX)
Í öðru lagi þarf að teygja ofangreinda fylkisformúlu niður með spássíu – með von um viðbótargögn sem færð eru inn í framtíðinni. Í þessu tilviki mun fylkisformúlan byrja að gefa villu #NUMBER á hólfum sem eru ekki enn fylltar. Til að stöðva það geturðu notað aðgerðina IFERROR, sem þarf að bæta við „í kringum“ fylkisformúluna okkar:
=IFERROR(INDEX(List; MATCH(SMALL(COUNTIF(List; “<"&List); ROW(1:1)); COUNTIF(listi; "<"&listi; 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
Það grípur #NUMBER villuna og gefur út tóm (tómar gæsalappir) í staðinn.
:
- Raða svið eftir lit
- Hvað eru fylkisformúlur og hvers vegna er þörf á þeim
- RÁÐA flokkun og kraftmikla fylki í nýja Office 365