Inhoud
Als u de lijst moet sorteren, dan zijn er veel manieren om u van dienst te zijn, waarvan de gemakkelijkste de sorteerknoppen op het tabblad of in het menu zijn Data (Gegevens — Sorteren). Er zijn echter situaties waarin het sorteren van de lijst automatisch moet gebeuren, dwz formules. Dit kan bijvoorbeeld nodig zijn bij het genereren van gegevens voor een vervolgkeuzelijst, bij het berekenen van gegevens voor grafieken, enz. Hoe sorteer ik een lijst met een formule in een oogwenk?
Methode 1. Numerieke gegevens
Als de lijst alleen numerieke informatie bevat, kan het sorteren eenvoudig worden gedaan met behulp van de functies MINST (KLEIN) и LINE (RIJ):
Functie MINST (KLEIN) haalt uit de array (kolom A) het n-de kleinste element in een rij. Die. SMALL(A:A;1) is het kleinste getal in de kolom, SMALL(A:A;2) is het op één na kleinste, enzovoort.
Functie LINE (RIJ) geeft het rijnummer voor de opgegeven cel, dwz RIJ(A1)=1, RIJ(A2)=2 enz. In dit geval wordt het eenvoudig gebruikt als generator van een reeks getallen n=1,2,3... voor onze gesorteerde lijst. Met hetzelfde succes was het mogelijk om een extra kolom te maken, deze handmatig te vullen met de numerieke reeks 1,2,3 … en ernaar te verwijzen in plaats van de RIJ-functie.
Methode 2. Tekstlijst en reguliere formules
Als de lijst geen cijfers maar tekst bevat, dan werkt de SMALL-functie niet meer, dus moet je een ander, iets langer pad inslaan.
Laten we eerst een servicekolom toevoegen met een formule waarin het serienummer van elke naam in de toekomstige gesorteerde lijst wordt berekend met behulp van de functie COUNTIF (AANTAL.ALS):
In de Engelse versie wordt het:
=AANTAL.ALS(A:A,»<"&A1)+AANTAL.ALS($A$1:A1,"="&A1)
De eerste term is een functie voor het tellen van het aantal cellen dat kleiner is dan de huidige. De tweede is een vangnet voor het geval een naam meer dan eens voorkomt. Dan hebben ze niet dezelfde, maar opeenvolgend toenemende aantallen.
Nu moeten de ontvangen nummers in oplopende volgorde worden gerangschikt. Hiervoor kunt u de functie MINST (KLEIN) vanaf de eerste manier:
Nou, tot slot, het blijft gewoon om de namen uit de lijst te halen met hun nummers. Om dit te doen, kunt u de volgende formule gebruiken:
Functie MEER BLOOTGESTELD (WEDSTRIJD) zoekt in kolom B naar het gewenste serienummer (1, 2, 3, etc.) en geeft in feite het nummer terug van de regel waar dit nummer zich bevindt. Functie INDEX (INHOUDSOPGAVE) haalt uit kolom A de naam op dit regelnummer.
Methode 3: Matrixformule
Deze methode is in feite hetzelfde plaatsingsalgoritme als in methode-2, maar geïmplementeerd door een matrixformule. Om de formule te vereenvoudigen, kreeg het cellenbereik C1:C10 de naam Lijst (selecteer cellen, druk op Ctrl + F3 en knop creëren):
Kopieer in cel E1 onze formule:
=INDEX(Lijst; VERGELIJKEN(KLEIN(AANTAL.ALS(Lijst; “<"&Lijst); RIJ(1:1)); AANTAL.ALS(Lijst; "<"&Lijst); 0))
Of in de Engelse versie:
=INDEX(Lijst, VERGELIJKEN(KLEIN(AANTAL.ALS(Lijst, «<"&Lijst), RIJ(1:1)), AANTAL.ALS(Lijst, "<"&Lijst), 0))
en duw Ctrl + Shift + Enterom het in te voeren als een matrixformule. Vervolgens kan de resulterende formule over de hele lengte van de lijst worden gekopieerd.
Als u wilt dat de formule geen vast bereik in acht neemt, maar zich kan aanpassen bij het toevoegen van nieuwe elementen aan de lijst, dan moet u de strategie enigszins wijzigen.
Eerst moet het lijstbereik dynamisch worden ingesteld. Om dit te doen, moet u bij het maken geen vast bereik C3:C10 opgeven, maar een speciale formule die verwijst naar alle beschikbare waarden, ongeacht hun aantal. Klik Alt + F3 of open het tabblad Formules – Naammanager (Formules — Naam Manager), maak een nieuwe naam en in het veld Link (Referentie) voer de volgende formule in (ik neem aan dat het bereik van de te sorteren gegevens begint bij cel C1):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
Ten tweede moet de bovenstaande matrixformule worden uitgerekt met een marge - met de verwachting dat er in de toekomst aanvullende gegevens worden ingevoerd. In dit geval begint de matrixformule een fout #NUMBER te geven op cellen die nog niet zijn gevuld. Om het te onderscheppen, kunt u de functie gebruiken: IFFOUT, die moet worden toegevoegd "rond" onze matrixformule:
=INFOR(INDEX(Lijst; VERGELIJKEN(KLEIN(AANTAL.ALS(Lijst; “<"&Lijst); RIJ(1:1)); AANTAL.ALS(Lijst; "<"&Lijst); 0));»»)
=ALS.FOUT(NDEX(Lijst, VERGELIJKEN(KLEIN(AANTAL.ALS(Lijst, «<"&Lijst), RIJ(1:1)), AANTAL.ALS(Lijst, "<"&Lijst), 0));"")
Het vangt de #NUMBER-fout op en voert in plaats daarvan een leegte (lege aanhalingstekens) uit.
:
- Sorteer bereik op kleur
- Wat zijn matrixformules en waarom zijn ze nodig?
- SORTEREN sorteren en dynamische arrays in het nieuwe Office 365