Përmbajtje
Nëse keni nevojë të renditni listën, atëherë ka shumë mënyra në shërbimin tuaj, më e thjeshta prej të cilave janë butonat e renditjes në skedë ose në meny Data (Të dhënat - Rendit). Megjithatë, ka situata kur renditja e listës duhet të bëhet automatikisht, pra formula. Kjo mund të kërkohet, për shembull, kur krijoni të dhëna për një listë rënëse, kur llogaritni të dhëna për grafikët, etj. Si të renditni një listë me një formulë në fluturim?
Metoda 1. Të dhëna numerike
Nëse lista përmban vetëm informacion numerik, atëherë renditja e tij mund të bëhet lehtësisht duke përdorur funksionet pAKTËN (E VOGËL) и LINE (ROW):
funksion pAKTËN (E VOGËL) nxjerr nga grupi (kolona A) elementin e n-të më të vogël me radhë. Ato. SMALL(A:A;1) është numri më i vogël në kolonë, SMALL(A:A;2) është numri i dytë më i vogël, e kështu me radhë.
funksion LINE (ROW) kthen numrin e rreshtit për qelizën e specifikuar, p.sh. ROW(A1)=1, ROW(A2)=2 etj. Në këtë rast, ai përdoret thjesht si gjenerues i një sekuence numrash n=1,2,3… për lista jonë e renditur. Me të njëjtin sukses, u bë e mundur të krijohej një kolonë shtesë, të plotësohej manualisht me sekuencën numerike 1,2,3 … dhe t'i referohej asaj në vend të funksionit ROW.
Metoda 2. Lista e teksteve dhe formulat e rregullta
Nëse lista nuk përmban numra, por tekst, atëherë funksioni SMALL nuk do të funksionojë më, kështu që duhet të shkoni në një rrugë tjetër, pak më të gjatë.
Së pari, le të shtojmë një kolonë shërbimi me një formulë ku numri serial i secilit emër në listën e ardhshme të renditur do të llogaritet duke përdorur funksionin countif (COUNTIF):
Në versionin anglisht do të jetë:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Termi i parë është një funksion për numërimin e numrit të qelizave që janë më pak se ai aktual. E dyta është një rrjet sigurie në rast se ndonjë emër shfaqet më shumë se një herë. Atëherë ata nuk do të kenë numra të njëjtë, por në rritje të njëpasnjëshme.
Tani numrat e marrë duhet të renditen në mënyrë sekuenciale në rend rritës. Për këtë ju mund të përdorni funksionin pAKTËN (E VOGËL) nga mënyra e parë:
Epo, më në fund, mbetet vetëm të nxjerrim emrat nga lista sipas numrave të tyre. Për ta bërë këtë, mund të përdorni formulën e mëposhtme:
funksion ME TE EKSPOZUAR (ndeshje) kërkon në kolonën B numrin serial të dëshiruar (1, 2, 3, etj.) dhe, në fakt, kthen numrin e rreshtit ku ndodhet ky numër. Funksioni INDEKSI (INDEX) nxjerr nga kolona A emrin në këtë numër rreshti.
Metoda 3: Formula e vargjeve
Kjo metodë është, në fakt, i njëjti algoritëm vendosjeje si në Metodën-2, por zbatohet nga një formulë grupi. Për të thjeshtuar formulën, gamës së qelizave C1:C10 iu dha emri Listë (zgjidhni qelizat, shtypni Ctrl + F3 dhe butonin Krijo):
Në qelizën E1, kopjoni formulën tonë:
=INDEX(Lista; MATCH(VOGLA (COUNTIF(Lista; “<"&Lista); RRESHT(1:1)); COUNTIF(Lista; "<"&Lista); 0))
Ose në versionin anglisht:
=INDEX(Lista, MATCH(VOGLA (COUNTIF(Lista, «<"&Lista), RRESHT(1:1)), COUNTIF(Lista, "<"&Lista), 0))
dhe shtyj Ctrl + Shift + Enterpër ta futur atë si një formulë grupi. Pastaj formula që rezulton mund të kopjohet në të gjithë gjatësinë e listës.
Nëse dëshironi që formula të mos marrë parasysh një gamë fikse, por të jetë në gjendje të përshtatet kur shtoni elementë të rinj në listë, atëherë do t'ju duhet të ndryshoni pak strategjinë.
Së pari, diapazoni i Listës do të duhet të vendoset në mënyrë dinamike. Për ta bërë këtë, kur krijoni, nuk duhet të specifikoni një gamë fikse C3:C10, por një formulë të veçantë që do t'i referohet të gjitha vlerave të disponueshme, pavarësisht nga numri i tyre. Klikoni Alt + F3 ose hapni skedën Formulat – Menaxheri i emrit (Formulat - Menaxheri i emrit), krijoni një emër të ri dhe në fushë lidhje (Referenca) shkruani formulën e mëposhtme (Unë supozoj se diapazoni i të dhënave që do të renditen fillon nga qeliza C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Së dyti, formula e mësipërme e grupit do të duhet të shtrihet me një diferencë - me pritjen e të dhënave shtesë të futura në të ardhmen. Në këtë rast, formula e grupit do të fillojë të japë një gabim #NUMBER në qelizat që nuk janë mbushur ende. Për ta kapur atë, mund të përdorni funksionin IFERROR, e cila duhet të shtohet "rreth" formulës sonë të grupit:
=IFERROR(INDEX(Lista; MATCH(VOGLA (COUNTIF(Lista; "<"&Lista); RRESHT(1:1)); COUNTIF(Lista; "<"&Lista; 0));»»)
=IFERROR(NDEX(Lista, MATCH(SALL(COUNTIF(Lista, «<"&Lista), RRESHT(1:1)), COUNTIF(Lista, "<"&Lista), 0));"")
Ai kap gabimin #NUMBER dhe në vend të tij nxjerr një boshllëk (thonjëza boshe).
:
- Rendit gamën sipas ngjyrës
- Cilat janë formulat e grupeve dhe pse janë të nevojshme
- SORT renditjen dhe grupet dinamike në Office 365 të ri