Zavolal mi priateľ a spýtal sa, či existuje spôsob, ako mať sériové čísla tak, aby v programe Excel neboli duplikáciou sériových čísel.
Niečo, ako je uvedené nižšie:
Chcel, aby sériové číslo pre Indiu bolo 1, kdekoľvek sa vyskytne. Podobne sú USA druhou krajinou a vždy by ich sériové číslo malo mať 2.
To ma prinútilo zamyslieť sa.
A tu sú dva spôsoby, ako by som mohol prísť na to, aby som sa v programe Excel vyhol duplikácii sériových čísel.
Metóda č. 1 - Použitie funkcie VLOOKUP
Prvým spôsobom je použiť našu milovanú funkciu VLOOKUP.
Na to musíme najskôr získať jedinečný zoznam krajín. Tu sú kroky, ako to urobiť:
- Vytvorte kópiu zoznamu krajín (skopírujte ju a vložte ju do rovnakého alebo iného pracovného hárka).
- Vyberte skopírované údaje a prejdite na položku Údaje -> Odstrániť duplikáty. Otvorí sa dialógové okno na odstránenie duplikátov.
- Uistite sa, že je začiarknutá možnosť - Moje údaje majú hlavičky (v prípade, že vaše údaje majú hlavičku. V opačnom prípade zrušte začiarknutie).
- Vyberte stĺpec, z ktorého chcete odstrániť duplikáty.
- Kliknite na tlačidlo OK.
- To je všetko. Budete mať zoznam jedinečných názvov krajín.
Pozrite si tiež: Ultimate Guide to Find and remove Duplicates in Excel.
Teraz priraďte sériové čísla ku každej krajine. Uistite sa, že sú tieto čísla zadané napravo od jedinečného zoznamu krajín, pretože VLOOKUP nemôže načítať údaje zľava od vyhľadávanej hodnoty.
V bunke, kde chcete zistiť sériové čísla (B3: B15), použite nasledujúci vzorec VLOOKUP:
= VLOOKUP (C3, $ F $ 3: $ G $ 8,2,0)
Tento vzorec VLOOKUP berie ako vyhľadávaciu hodnotu názov krajiny, kontroluje ho v údajoch v F3: G8 a vracia jeho sériové číslo.
Metóda č. 2 - Dynamický vzorec
Aj keď je metóda VLOOKUP úplne dobrým spôsobom, nie je dynamická.
Ak teda pridám novú krajinu alebo zmením existujúcu krajinu, táto metóda nebude fungovať a budete musieť celý postup metódy č. 1 znova zopakovať.
Tu je vzorec, ktorý ho robí dynamickým:
= IF (COUNTIF ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3)+1, INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1))
Ak chcete použiť tento vzorec, musíte ručne zadať 1 do prvej bunky a vyššie uvedený vzorec do všetkých ostatných zostávajúcich buniek.
Ako to funguje:
Používa funkciu IF, ktorá kontroluje, koľkokrát sa krajina vyskytla pred týmto riadkom. Ak sa názov krajiny vyskytuje prvýkrát, počet je 1 a podmienka je PRAVDA a ak sa názov krajiny vyskytol aj skôr, počet je viac ako 1 a podmienka je NEPRAVDA.
- Keď je podmienka PRAVDA:
= MAX ($ B $ 3: $ B3) +1
Ak je hodnota PRAVDA, čo znamená, že názov krajiny sa zobrazuje prvýkrát, identifikuje dovtedy maximálnu hodnotu sériového čísla a pridá k nemu 1, čím poskytne ďalšiu hodnotu sériového čísla.
- Kedy Hodnota, ak je NEPRAVDA:
= INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1)
Ak sa krajina už vyskytla skôr, tento vzorec prejde do bunky, kde sa zobrazí ako prvý a vráti poradové číslo prvého výskytu tejto krajiny.
Stiahnite si ukážkový súbor
Tiež by sa vám mohli páčiť nasledujúce návody na Excel:
- Ako používať Flash Fill v Exceli.
- Automaticky zoradiť údaje v abecednom poradí pomocou vzorca.
- Ako rýchlo vyplniť čísla v bunkách bez ťahania.
- Ako používať rukoväť výplne v programe Excel.
