Znajomy zadzwonił do mnie i zapytał, czy istnieje sposób na posiadanie numerów seryjnych w taki sposób, aby nie były duplikowane w numerach seryjnych w Excelu.
Coś, jak pokazano poniżej:
Chciał, aby numer seryjny dla Indii miał wartość 1, gdziekolwiek występuje. Podobnie Stany Zjednoczone są drugim krajem i zawsze powinny mieć 2 jako numer seryjny.
To dało mi do myślenia.
A oto dwa sposoby, które mogę wymyślić, aby uniknąć powielania numerów seryjnych w Excelu.
Metoda nr 1 - Korzystanie z funkcji WYSZUKAJ.PIONOWO
Pierwszym sposobem jest użycie naszej ukochanej funkcji WYSZUKAJ.PIONOWO.
Aby to zrobić, najpierw musimy uzyskać unikalną listę krajów. Oto kroki, aby to zrobić:
- Utwórz kopię listy krajów (skopiuj i wklej ją w tym samym arkuszu lub innym arkuszu).
- Wybierz skopiowane dane i przejdź do Dane -> Usuń duplikaty. Otworzy się okno dialogowe usuwania duplikatów.
- Upewnij się, że opcja Moje dane ma nagłówki jest zaznaczona (w przypadku, gdy Twoje dane mają nagłówek. W przeciwnym razie odznacz ją).
- Wybierz kolumnę, z której chcesz usunąć duplikaty.
- Kliknij OK.
- Otóż to. Będziesz mieć listę unikalnych nazw krajów.
Zobacz też: Kompletny przewodnik po znajdowaniu i usuwaniu duplikatów w programie Excel.
Teraz przypisz numery seryjne do każdego kraju. Upewnij się, że te liczby zostały wprowadzone po prawej stronie listy unikalnych krajów, ponieważ WYSZUKAJ.PIONOWO nie może pobrać danych z lewej strony wartości wyszukiwania.
W komórce, w której chcesz uzyskać numery seryjne (B3:B15), użyj poniższej formuły WYSZUKAJ.PIONOWO:
= WYSZUKAJ.PIONOWO(C3,$F3:$G8,2,0)
Ta formuła WYSZUKAJ.PIONOWO przyjmuje nazwę kraju jako wartość wyszukiwania, sprawdza ją w danych w F3:G8 i zwraca jej numer seryjny.
Metoda nr 2 - Formuła dynamiczna
Chociaż metoda WYSZUKAJ.PIONOWO jest doskonałym sposobem na zrobienie tego, nie jest dynamiczna.
Więc jeśli dodam nowy kraj lub zmienię istniejący kraj, ta metoda nie zadziała i będziesz musiał powtórzyć cały proces metody nr 1 ponownie.
Oto formuła, która sprawia, że jest dynamiczny:
=JEŻELI(LICZ.JEŻELI($C$3:$C4,$C4)=1,MAX($B$3:$B3)+1,INDEKS($B$3:$C$18;DOPASOWANIE($C4,$C$3:$) C4,0),1))
Aby użyć tej formuły, musisz ręcznie wprowadzić 1 w pierwszej komórce, a powyższą formułę we wszystkich pozostałych komórkach.
Jak to działa:
Używa funkcji JEŻELI, która sprawdza, ile razy dany kraj wystąpił przed tym wierszem. Jeśli nazwa kraju występuje po raz pierwszy, liczba wynosi 1, a warunek to PRAWDA, a jeśli nazwa kraju występuje również wcześniej, liczba jest większa niż 1, a warunek to FAŁSZ.
- Gdy warunek jest PRAWDZIWY:
=MAKS($B3$:$B3)+1
Jeśli wartość to TRUE, co oznacza, że nazwa kraju pojawia się po raz pierwszy, identyfikuje maksymalną wartość numeru seryjnego do tej pory i dodaje do niej 1, aby otrzymać kolejną wartość numeru seryjnego.
- Gdy wartość, jeśli FAŁSZ:
=INDEKS($B$3:$C$18;MATCH($C4,$C$3:$C4,0);1)
Jeśli kraj wystąpił już wcześniej, ta formuła przechodzi do komórki, w której występuje jako pierwsza i zwraca numer seryjny pierwszego wystąpienia tego kraju.
Pobierz przykładowy plik
Możesz również polubić następujące samouczki programu Excel:
- Jak korzystać z wypełniania Flash w programie Excel.
- Automatycznie sortuj dane w kolejności alfabetycznej za pomocą formuły.
- Jak szybko wypełnić liczby w komórkach bez przeciągania.
- Jak korzystać z uchwytu wypełnienia w programie Excel.
