tartalom
Ha rendezni kell a listát, akkor számos mód áll az Ön rendelkezésére, ezek közül a legegyszerűbb a lapon vagy a menüben található rendezés gombok dátum (Adatok – Rendezés). Vannak azonban olyan helyzetek, amikor a lista rendezését automatikusan, azaz képletekkel kell elvégezni. Erre szükség lehet például egy legördülő lista adatainak generálásakor, diagramok adatainak kiszámításakor stb. Hogyan lehet menet közben egy listát rendezni képletekkel?
1. módszer. Numerikus adatok
Ha a lista csak számadatokat tartalmaz, akkor a rendezés egyszerűen elvégezhető a függvényekkel LEGKEVÉSBÉ (KICSI) и LINE (SOR):
Funkció LEGKEVÉSBÉ (KICSI) kihúzza a tömbből (A oszlop) a sorban az n-edik legkisebb elemet. Azok. SMALL(A:A;1) a legkisebb szám az oszlopban, SMALL(A:A;2) a második legkisebb, és így tovább.
Funkció LINE (SOR) visszaadja a megadott cella sorszámát, azaz ROW(A1)=1, ROW(A2)=2 stb. Ebben az esetben egyszerűen n=1,2,3… számsorozat generátoraként használható. rendezett listánkat. Ugyanilyen sikerrel sikerült egy további oszlopot készíteni, azt manuálisan kitölteni az 1,2,3 … számsorral, és erre hivatkozni a ROW függvény helyett.
2. módszer: Szöveges lista és szabályos képletek
Ha a lista nem számokat, hanem szöveget tartalmaz, akkor a SMALL funkció már nem fog működni, így egy másik, kicsit hosszabb úton kell menni.
Először adjunk hozzá egy szolgáltatás oszlopot egy képlettel, ahol a jövőbeni rendezett listában lévő egyes nevek sorszáma a függvény segítségével lesz kiszámítva COUNTIF (COUNTIF):
Az angol változatban ez lesz:
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
Az első tag az aktuálisnál kisebb cellák számának számlálására szolgáló függvény. A második egy biztonsági háló arra az esetre, ha bármelyik név többször előfordulna. Akkor nem ugyanazok, hanem folyamatosan növekvő számok lesznek.
Most a kapott számokat növekvő sorrendbe kell rendezni. Ehhez használhatja a funkciót LEGKEVÉSBÉ (KICSI) az első útból:
Nos, végül csak a neveket számuk alapján kell kiemelni a listáról. Ehhez a következő képletet használhatja:
Funkció KIÉPÍTETTebb (MÉRKŐZÉS) megkeresi a B oszlopban a kívánt sorozatszámot (1, 2, 3 stb.), és valójában visszaadja annak a sornak a számát, ahol ez a szám található. Funkció INDEX (INDEX) kihúzza az A oszlopból a nevet ennél a sorszámnál.
3. módszer: Tömbképlet
Ez a módszer valójában ugyanaz az elhelyezési algoritmus, mint a 2. módszerben, de egy tömbképlettel valósítja meg. A képlet egyszerűsítése érdekében a C1:C10 cellatartomány nevet kapta Lista (jelölje ki a cellákat, nyomja meg Ctrl + F3 és gomb Teremt):
Az E1 cellába másolja be a képletünket:
=INDEX(Lista; MATCH(SMALL(COUNTIF(Lista; "<"&Lista); ROW(1:1)); COUNTIF(Lista; "<"&Lista); 0))
Vagy az angol változatban:
=INDEX(Lista, EGYEZÉS(KICS(COUNTIF(lista, «<"&lista), ROW(1:1)), COUNTIF(lista, "<"&lista), 0))
és nyomja Ctrl + Shift + Enterhogy tömbképletként adja meg. Ezután a kapott képlet lemásolható a lista teljes hosszában.
Ha azt szeretné, hogy a képlet ne egy fix tartományt vegyen figyelembe, hanem tudjon igazodni, amikor új elemeket ad hozzá a listához, akkor kissé módosítania kell a stratégián.
Először a Lista tartományt kell dinamikusan beállítani. Ehhez a létrehozáskor nem egy rögzített C3:C10 tartományt kell megadni, hanem egy speciális képletet, amely az összes elérhető értékre hivatkozik, függetlenül azok számától. Kattintson Alt + F3 vagy nyissa meg a lapot Képletek – Névkezelő (Képletek — Névkezelő), hozzon létre egy új nevet és a mezőben Link (Referencia) írja be a következő képletet (feltételezem, hogy a rendezendő adatok tartománya a C1 cellából kezdődik):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=ELTOLÁS(C1,0,0;1;1000;SCHÖTZ(C1:CXNUMX),XNUMX)
Másodszor, a fenti tömbképletet margóval le kell feszíteni – a jövőben további adatok megadására számítva. Ebben az esetben a tömbképlet #SZÁM hibát kezd kiadni azokon a cellákon, amelyek még nincsenek kitöltve. Elfogásához használhatja a funkciót IFROROR, amelyet hozzá kell adni a tömbképletünkhöz:
=IFERROR(INDEX(Lista; MATCH(SMALL(COUNTIF(Lista; "<"&Lista); ROW(1:1)); COUNTIF(Lista; "<"&Lista); 0));»»)
=IFERROR(NDEX(lista, MATCH(SMALL(COUNTIF(lista, «<"&lista), ROW(1:1)), COUNTIF(lista, "<"&lista), 0));"")
Elkapja a #NUMBER hibát, és helyette üres (üres idézőjelek) szöveget ad ki.
:
- A tartomány rendezése szín szerint
- Mik azok a tömbképletek és miért van szükség rájuk
- A rendezés és a dinamikus tömbök RENDEZÉSE az új Office 365-ben