Excel inbyggd datasortering är fantastisk, men den är inte dynamisk. Om du sorterar data och sedan lägger till data i den måste du sortera den igen.
Sortera data i alfabetisk ordning
I det här inlägget kommer jag att visa dig olika sätt att sortera data i alfabetisk ordning med hjälp av formler. Det betyder att du kan lägga till data, och det kommer automatiskt att sortera det åt dig.
När data är all text utan dubbletter
Anta att du har en data enligt nedan:
I det här exemplet är all data i textformat (inga siffror, ämnen eller dubbletter). För att sortera detta kommer jag att använda en hjälparkolumn. Använd följande COUNTIF -formel i kolumnen bredvid data:
= RÄKTA ($ A $ 2: $ A $ 9, "<=" & A2)
Denna formel jämför ett textvärde med alla andra textvärden och returnerar dess relativa rang. Till exempel, i cell B2, returnerar den 8, eftersom det finns 8 textvärden som är lägre än eller lika med texten ‘US’ (alfabetisk ordning).
För att sortera värdena, använd följande kombination av INDEX, MATCH och ROWS -funktioner:
= INDEX ($ A $ 2: $ A $ 9, MATCH (RADER ($ B $ 2: B2), $ B $ 2: $ B $ 9,0))
Denna formel extraherar helt enkelt namnen i alfabetisk ordning. I den första cellen (C2) söker den efter det landnamn som har det lägsta antalet (Australien har 1). I den andra cellen returnerar den Kanada (som har siffran 2) och så vidare …
Allergisk mot hjälpkolumner ??
Här är en formel som gör samma sak utan hjälparkolumnen.
= INDEX ($ A $ 2: $ A $ 9, MATCH (RADER ($ A $ 2: A2), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), 0))
Detta är en matrisformel, så använd Ctrl + Skift + Enter istället för Enter.
Jag låter dig avkoda.
Prova själv … Ladda ner exempelfil
Denna formel fungerar bra om du har text eller alfanumeriska värden.
Men det misslyckas oerhört om:
- Du har dubbletter i data (försök sätta USA två gånger).
- Det finns tomma ämnen i data.
- Du har en blandning av siffror och text (försök sätta 123 i en av cellerna).
När data är en blandning av siffror, text, dubbletter och ämnen
Nu är den här lite knepig. Jag kommer att använda 4 hjälparkolumner för att visa dig hur det fungerar (och sedan ge dig en enorm formel som gör det utan hjälpkolumnerna). Anta att du har en data enligt nedan:
Du kan se att det finns dubblettvärden, blank och siffror. Så jag kommer att använda hjälparkolumner för att ta itu med alla dessa frågor.
Hjälpkolumn 1
Ange följande COUNTIF -formel i hjälpkolumn 1
= COUNTIF ($ A $ 2: $ A $ 9, "<=" & A2)
Denna formel gör följande:
- Det returnerar 0 för ämnen.
- När det gäller dubbletter returnerar det samma nummer.
- Text och siffror bearbetas parallellt och denna formel returnerar samma nummer för text och nummer (till exempel 123 och Indien får båda 1).
Hjälpkolumn 2
Ange följande IS -funktion i hjälpkolumn 2:
=-ISNUMBER (A2)
Hjälpkolumn 3
Ange följande formel i hjälpkolumn 3:
=-ISBLANK (A2)
Hjälpkolumn 4
Ange följande formel i Hjälpkolumn 4
= IF (ISNUMBER (A2), B2, IF (ISBLANK (A2), B2, B2+$ C $ 10))+$ D $ 10
Tanken med denna formel är att separera ämnen, siffror och textvärden.
- Om cellen är tom returnerar den värdet i cell B2 (som alltid skulle vara 0) och lägger till värdet i cell D10. I ett nötskal kommer det att returnera det totala antalet tomma celler i data
- Om cellen är ett numeriskt värde returnerar den jämförande rankningen och lägger till det totala antalet ämnen. Till exempel, för 123 returnerar den 2 (1 är rankningen 123 i data och det finns en tom cell)
- Om det är text returnerar den jämförande rankningen och lägger till det totala antalet numeriska värden och ämnen. Till exempel för Indien lägger den till textens jämförande rang i text (som är 1) och lägger till antalet tomma celler och antalet numeriska värden.
Slutresultat - Sorterade data
Nu kommer vi att använda dessa hjälparkolumner för att få den sorterade listan. Här är formeln:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMÅ ($ E $ 2: $ E $ 9, RADER ($ F $ 2: F2)+$ D $ 10), $ E $ 2: $ E $ 9,0)) , "")
Denna sorteringsmetod blir nu idiotsäker. Jag har visat dig metoden för 8 objekt, men du kan utöka den till så många objekt som du vill.
Prova själv … Ladda ner exempelfil
En formel för att sortera allt (utan hjälpkolumner)
Om du kan hantera extrema formler, här är en allt-i-ett-formel som kommer att sortera data i alfabetisk ordning (utan någon hjälpkolumn).
Här är formeln:
= IFERROR (INDEX ($ A $ 2: $ A $ 9, MATCH (SMALL (NOT ($ A $ 2: $ A $ 9 = "")*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUMMA (-ISNUMBER ($ A $ 2: $ A $ 9)))), ROWS ($ A $ 2: A2)+SUM (-ISBLANK ($ A $ 2: $ A $ 9)))), INTE ($ A $ 2: $ A $ 9 = "")*IF (ISNUMBER ($ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9), COUNTIF ($ A $ 2: $ A $ 9, "<=" & $ A $ 2: $ A $ 9)+SUMMA (-ISNUMBER ($ A $ 2: $ A $ 9))), 0)), "")
Ange denna formel i en cell och dra den ner för att få den sorterade listan. Eftersom det här är en matrisformel, använd också Ctrl + Skift + Enter istället för Enter.
Denna formel har verklig nytta. Vad tror du? Jag skulle älska att lära av dig. Lämna dina fotspår i kommentarsfältet!
