Generieke formule
=RANK(A1,values)+COUNTIF(exp_rng,A1)-1
Samenvatting
Als u gegevens die alleen numerieke waarden bevatten, dynamisch wilt sorteren, kunt u een hulpkolom en een formule gebruiken die zijn gemaakt met de functies RANK en AANTAL.ALS. In het getoonde voorbeeld is de formule in D5:
=RANK(C5,sales)+COUNTIF($C$5:C5,C5)-1
waarbij "verkoop" het benoemde bereik C5: C11 is.
Uitleg
Opmerking: deze formule is de opzet voor een formule die gegevens kan extraheren en weergeven met behulp van een vooraf gedefinieerde sorteervolgorde in een hulpkolom. Een voorbeeld hier.
De kern van deze formule is de RANK-functie, die wordt gebruikt om een rangorde van verkoopwaarden te genereren, waarbij het hoogste nummer op # 1 staat:
=RANK(C5,sales)
Hier gebruikt RANK voor het gemak het genoemde bereik "verkoop" (C5: C11). RANK wijst standaard 1 toe aan de hoogste waarde, 2 aan de op een na hoogste waarde, enzovoort. Dit werkt perfect zolang numerieke waarden uniek zijn. Om numerieke waarden te verwerken die duplicaten bevatten, moeten we echter de AANTAL.ALS-functie gebruiken om banden te verbreken. Dit wordt gedaan door het resultaat van dit fragment toe te voegen aan de waarde die wordt geretourneerd door RANK:
COUNTIF($C$5:C5,C5)-1
Merk op dat het bereik wordt ingevoerd als een gemengde verwijzing die wordt uitgebreid naarmate de formule door de tabel wordt gekopieerd. Zoals geschreven, zal deze verwijzing de huidige rij bevatten, dus trekken we 1 af om de eerste keer dat het voorkomt te "nul uit". Dit betekent dat de uitdrukking nul retourneert voor elke numerieke waarde totdat een duplicaat wordt aangetroffen. Bij de tweede instantie retourneert de uitdrukking 1, bij de derde instantie 2, enzovoort. Dit verbreekt effectief de banden en stelt de formule in staat om een opeenvolgende lijst met getallen te genereren zonder hiaten.
Zodra de formule op zijn plaats is, kunnen de gegevens worden gesorteerd op de hulpkolom. Het kan ook worden opgehaald met INDEX met behulp van de waarden in de hulpkolom.
Opmerking: deze formule is aangepast van een voorbeeld in het uitstekende boek Control + Shift + Enter, door Mike Girvin.
