Zavolal mi přítel a zeptal se, jestli existuje způsob, jak mít sériová čísla takovým způsobem, aby v duplikátech sériových čísel v Excelu nebyla žádná duplikace.
Něco, jak je uvedeno níže:
Chtěl, aby pořadové číslo pro Indii bylo 1, ať se objeví kdekoli. Podobně jsou USA 2. zemí a vždy by jako sériové číslo měly mít 2.
To mě přimělo přemýšlet.
A zde jsou dva způsoby, které bych mohl vymyslet, abych se v aplikaci Excel vyhnul duplicitě v sériových číslech.
Metoda č. 1 - Použití funkce VLOOKUP
Prvním způsobem je použít naši milovanou funkci VLOOKUP.
K tomu musíme nejprve získat jedinečný seznam zemí. Zde jsou kroky, jak toho dosáhnout:
- Vytvořte kopii seznamu zemí (zkopírujte ji do stejného listu nebo jiného listu).
- Vyberte zkopírovaná data a přejděte na Data -> Odebrat duplikáty. Otevře se dialogové okno pro odstranění duplicit.
- Ujistěte se, že je zaškrtnuta možnost - Moje data mají záhlaví (v případě, že vaše data mají záhlaví. Jinak jej zrušte).
- Vyberte sloupec, ze kterého chcete odebrat duplikáty.
- Klikněte na OK.
- A je to. Budete mít seznam jedinečných názvů zemí.
Viz také: The Ultimate Guide to Find and remove Duplicates in Excel.
Nyní přiřaďte sériová čísla každé zemi. Zajistěte, aby tato čísla byla uvedena vpravo od jedinečného seznamu zemí, protože VLOOKUP nemůže načítat data zleva od vyhledávací hodnoty.
V buňce, kde chcete sériová čísla (B3: B15), použijte níže uvedený vzorec VLOOKUP:
= VLOOKUP (C3, $ F $ 3: $ G $ 8,2,0)
Tento vzorec VLOOKUP bere jako vyhledávací hodnotu název země, zkontroluje jej v datech v F3: G8 a vrátí jeho sériové číslo.
Metoda č. 2 - Dynamický vzorec
Přestože je metoda VLOOKUP naprosto skvělým způsobem, není dynamická.
Pokud tedy přidám novou zemi nebo změním stávající zemi, tato metoda by nefungovala a budete muset celý postup metody č. 1 znovu opakovat.
Zde je vzorec, který je dynamický:
= IF (COUNTIF ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3)+1, INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1))
Chcete -li použít tento vzorec, musíte ručně zadat 1 do první buňky a výše uvedený vzorec do všech ostatních zbývajících buněk.
Jak to funguje:
Používá funkci IF, která kontroluje, kolikrát se země vyskytla před daným řádkem. Pokud se název země objeví poprvé, počet je 1 a podmínka je PRAVDA, a pokud se název země objevil také dříve, počet je více než 1 a podmínka je NEPRAVDA.
- Když je podmínka PRAVDA:
= MAX ($ B $ 3: $ B3) +1
Pokud je hodnota PRAVDA, což znamená, že se název země objevuje poprvé, identifikuje do té doby maximální hodnotu sériového čísla a přidá k němu 1, čímž poskytne další hodnotu sériového čísla.
- When Value if FALSE:
= INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1)
Pokud se země již objevila dříve, tento vzorec přejde do buňky, kde se zobrazí jako první, a vrátí pořadové číslo prvního výskytu této země.
Stáhněte si ukázkový soubor
Také by se vám mohly líbit následující výukové programy pro Excel:
- Jak používat Flash Fill v Excelu.
- Automaticky třídit data v abecedním pořadí pomocí vzorce.
- Jak rychle vyplnit čísla v buňkách bez přetahování.
- Jak používat Fill Handle v Excelu.
