Download eksemplet på projektmappe
Denne vejledning viser, hvordan du dynamisk sorterer data efter en tilpasset sorteringsrækkefølge i Excel og Google Sheets.
SORTBY -funktion i Excel 365
SORTBY -funktionen producerer et dynamisk array af sorterede data.
I dette eksempel bruger vi SORTBY -funktionen til at tage en datatabel og producere et dynamisk array, der sorterer det efter Hold efter a Brugerdefineret sorteringsliste:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Bemærk: SORTBY -funktionen er kun skrevet i celle H3. Resten af tabellen udfyldes automatisk.
Den sorterede tabel er dynamisk, hvilket betyder, at hvis nogen af Hold eller Spiller navn værdier ændres eller registreres eller tilføjes / slettes, så vil outputtabellen automatisk ændre størrelse og sortere igen.
Bemærk: SORTBY -funktionen er ikke tilgængelig i Excel 2022 eller tidligere. Læs nedenfor for sorteringsmuligheder til rådighed for tidligere versioner af Excel.
For at forklare, hvordan denne formel fungerer, lad os opdele den i trin:
Vores sidste formel er:
| 1 | = SORTBY (B3: D8, MATCH (B3: B8, F3: F5,0)) |
Først definerer vi datatabellen, der skal sorteres. Dette vil være B3: D8.
Dernæst skal vi definere sorteringsrækkefølgen. Vi vil bruge MATCH -funktionen til at finde den rækkefølge, hvor hver Hold navn vises i Brugerdefineret sorteringsliste. Denne del af formlen er:
| 1 | = MATCH (B3: B8, F3: F5,0) |
Ved at tilføje værdierne fra celleområderne får vi:
| 1 | = MATCH ({"Blå"; "Rød"; "Gul"; "Rød"; "Blå"; "Gul"}, {"Gul"; "Blå"; "Rød"}, 0) |
MATCH -funktionen erstatter alle Hold navne med den rækkefølge, de vises i Brugerdefineret sorteringsliste, ved hjælp af et nøjagtigt tekstmatch:
| 1 | ={2; 3; 1; 3; 2; 1} |
Denne matrix af værdier bruges derefter som sorteringsrækkefølge af hoved SORTBY -funktionen:
| 1 | = SORTBY (B3: D8, {2; 3; 1; 3; 2; 1}) |
Det sorterede bord fremstilles derefter
Tilpasset sorteringsliste - Pre -Excel 365
Da SORTBY -funktionen ikke er tilgængelig for Excel -brugere før Excel 365 -versionen, viser vi nu, hvordan man udfører den samme opgave ved hjælp af en mere manuel proces.
Det indbyggede Excel-sorteringsværktøj kan bruges til at sortere efter en Brugerdefineret sorteringsliste ved at vælge 'Brugerdefineret liste …' fra sorteringsindstillingerne i sorteringsværktøjet:
En ny Brugerdefineret liste skal tilføjes som en kommasepareret liste. I dette eksempel tilføjer vi: 'Gul, blå, rød ’
Det Brugerdefineret liste vises derefter i valg af sorteringsrækkefølge
Datatabellen sorteres derefter efter Brugerdefineret liste værdier
Bemærk, at denne tilpassede sorteringsrækkefølge er gemt i projektmappen og kan bruges igen. Hvis du ikke ønsker at tilføje en Brugerdefineret liste til Excel, så kan en metode ved hjælp af en hjælperkolonne i stedet prøves.
Tilpasset sorteringsliste med hjælpekolonne - Pre -Excel 365
I dette næste eksempel bruger vi MATCH -funktionen i en hjælperkolonne til at definere a Brugerdefineret sorteringsrækkefølge ved at henvise til a Brugerdefineret sorteringsliste:
| 1 | = MATCH (B3, $ G $ 3: $ G $ 5,0) |
Bemærk brugen af $ -symbolet, når der refereres til Brugerdefineret sorteringsliste. Læs vores artikel om låsning af cellereferencer for at lære mere om dette emne.
En hjælperkolonne er blevet tilføjet, en manuel sorteringsproces kan køres ved hjælp af Sorteringsguide:
Dette producerer derefter tabellen sorteret efter Tilpasset sorteringsrækkefølge:
Hvis der foretages ændringer i Hold eller Brugerdefineret sorteringsliste værdier, skal sorteringsguiden genkøres for at opdatere datatabellens sorteringsrækkefølge.
Tilpasset sorteringsliste med formel i Google Sheets
SORTBY -funktionen er ikke tilgængelig i Google Sheets, men dens SORT -funktion kan i stedet bruges og er mere kraftfuld end SORT -funktionen i Excel 365. Det giver os mulighed for at bruge tilpassede sorteringslister til dynamisk at sortere dataområder.
I dette eksempel bruger vi Google Sheets SORT -funktionen til at tage en datatabel og producere et dynamisk array, der sorterer Hold værdier ved a Brugerdefineret sorteringsliste:
| 1 | = SORT (B3: D8, MATCH (B3: B8, F3: F5,0), SAND) |
Google Sheets SORT -funktion sorterer data efter de angivne kolonnetal eller tilpassede sorteringsordrer i inputdataene og giver os mulighed for at angive, om sorteringsrækkefølgen er stigende eller ej
