En vän ringde mig och frågade om det finns ett sätt att ha serienummer på ett sådant sätt att de inte är dubblering av serienummer i Excel.
Något som visas nedan:
Han ville att serienumret för Indien ska vara 1 var det än förekommer. På samma sätt är USA det andra landet och bör alltid ha 2 som serienummer.
Detta fick mig att tänka.
Och här är de två sätten jag kan komma på för att undvika dubbelarbete i serienummer i Excel.
Metod #1 - Använda VLOOKUP -funktionen
Det första sättet är att använda vår älskade VLOOKUP -funktion.
För att göra detta måste vi först få en unik lista över länder. Här är stegen för att göra det:
- Skapa en kopia av listan över länder (kopiera och klistra in den i samma kalkylblad eller ett annat kalkylblad).
- Välj de kopierade data och gå till Data -> Ta bort dubbletter. Det öppnar dialogrutan Ta bort dubblett.
- Se till att alternativet - Min data har rubriker är markerat (om din data har rubriken. Annars avmarkera den).
- Välj den kolumn från vilken du vill ta bort dubbletterna.
- Klicka på OK.
- Det är allt. Du kommer att ha en lista med unika landnamn.
Se även: Den ultimata guiden för att hitta och ta bort dubbletter i Excel.
Tilldela nu varje land serienumren. Se till att dessa siffror anges till höger om den unika landslistan, eftersom VLOOKUP inte kan hämta data från vänster om uppslagsvärdet.
I cellen där du vill ha serienumren (B3: B15) använder du nedanstående VLOOKUP -formel:
= VLOOKUP (C3, $ F $ 3: $ G $ 8,2,0)
Denna VLOOKUP -formel tar landnamnet som uppslagsvärde, söker efter det i data i F3: G8 och returnerar sitt serienummer.
Metod #2 - En dynamisk formel
Även om VLOOKUP -metoden är ett perfekt sätt att göra detta, är den inte dynamisk.
Så om jag lägger till ett nytt land eller ändrar ett befintligt land skulle den här metoden inte fungera och du måste upprepa hela processen med metod #1 igen.
Här är en formel som gör den dynamisk:
= IF (COUNTIF ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3)+1, INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1))
För att använda denna formel måste du manuellt ange 1 i den första cellen och ovanstående formel i alla andra återstående celler.
Hur det fungerar:
Den använder en IF -funktion som kontrollerar antalet gånger ett land har inträffat före den raden. Om landnamnet förekommer för första gången är räkningen 1 och villkoret är SANT, och om landets namn har inträffat tidigare är räkningen mer än 1 och villkoret är FALSKT.
- När villkoret är SANT:
= MAX ($ B $ 3: $ B3) +1
Om värdet är SANT, vilket betyder att landnamnet visas för första gången, identifierar det det maximala värdet på serienumret tills dess och lägger till 1 till det för att ge nästa serienummervärde.
- När värde om FALSE:
= INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1)
Om landet redan har inträffat tidigare går denna formel till cellen där den visas först och returnerar serienumret för det första förekomsten av det landet.
Ladda ner exempelfilen
Du kanske också gillar följande Excel -självstudier:
- Så här använder du Flash Fyll i Excel.
- Sortera data automatiskt i alfabetisk ordning med hjälp av formel.
- Hur man snabbt fyller nummer i celler utan att dra.
- Så här använder du Fill Handle i Excel.
