Prijatelj me je poklical in vprašal, ali obstaja način, da imamo serijske številke tako, da v Excelu ne podvajajo serijskih številk.
Nekaj, kot je prikazano spodaj:
Želel je, da bi bila serijska številka Indije 1, kjer koli se pojavi. Podobno so ZDA druga država in za serijsko številko bi morale vedno imeti 2.
To mi je dalo misliti.
Tu sta dva načina, s katerimi bi se lahko izognil podvajanju serijskih številk v Excelu.
Metoda #1 - Uporaba funkcije VLOOKUP
Prvi način je uporaba naše ljubljene funkcije VLOOKUP.
Za to moramo najprej dobiti enoličen seznam držav. Tukaj so koraki za to:
- Ustvarite kopijo seznama držav (kopirajte in prilepite na isti delovni list ali drug delovni list).
- Izberite kopirane podatke in pojdite na Podatki -> Odstrani podvojene podatke. Odprlo se bo pogovorno okno za odstranitev podvojenega.
- Prepričajte se, da je izbrana možnost - Moji podatki imajo glave (v primeru, da imajo vaši podatki glavo. Sicer pa počistite polje).
- Izberite stolpec, iz katerega želite odstraniti dvojnike.
- Kliknite V redu.
- To je to. Imeli boste seznam edinstvenih imen držav.
Glejte tudi: Končni vodnik za iskanje in odstranjevanje podvojenih datotek v Excelu.
Zdaj dodelite serijske številke vsaki državi. Prepričajte se, da so te številke vnesene desno od seznama edinstvenih držav, saj VLOOKUP ne more pridobiti podatkov z leve strani vrednosti iskanja.
V celici, kjer želite serijske številke (B3: B15), uporabite spodnjo formulo VLOOKUP:
= VLOOKUP (C3, $ F $ 3: $ G $ 8,2,0)
Ta formula VLOOKUP vzame ime države kot iskalno vrednost, jo preveri v podatkih v F3: G8 in vrne serijsko številko.
Metoda #2 - Dinamična formula
Čeprav je metoda VLOOKUP popolnoma dober način za to, ni dinamična.
Če torej dodam novo državo ali spremenim obstoječo državo, ta metoda ne bi delovala in boste morali ponoviti celoten postopek metode #1 znova.
Tukaj je formula, zaradi katere je dinamična:
= ČE (COUNTIF ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3)+1, INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1))
Če želite uporabiti to formulo, morate ročno vnesti 1 v prvo celico, zgornjo formulo pa v vse ostale preostale celice.
Kako deluje:
Uporablja funkcijo IF, ki preveri, kolikokrat se je država pojavila pred to vrstico. Če se ime države pojavi prvič, je število 1 in pogoj je TRUE, če pa se je ime države pojavilo tudi prej, je štetje več kot 1 in pogoj je FALSE.
- Ko je pogoj res:
= MAX ($ B $ 3: $ B3) +1
Če je vrednost TRUE, kar pomeni, da se ime države prvič prikaže, označi največjo vrednost serijske številke do takrat in ji doda 1, da poda naslednjo vrednost serijske številke.
- Ko je vrednost, če je FALSE:
= INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1)
Če se je država že pojavila prej, gre ta formula v celico, kjer je prva, in vrne zaporedno številko prvega pojavljanja te države.
Prenesite datoteko z vzorcem
Morda vam bodo všeč tudi naslednji vaje za Excel:
- Kako uporabljati Flash Fill v Excelu.
- Samodejno razvrstite podatke po abecednem vrstnem redu s formulo.
- Kako hitro vnesti številke v celice brez vlečenja.
- Kako uporabljati ročico za izpolnjevanje v Excelu.
