Един приятел ми се обади и попита дали има начин да има серийни номера по такъв начин, че да не се дублират в серийни номера в Excel.
Нещо, както е показано по -долу:
Той искаше серийният номер за Индия да бъде 1, където и да се появи. По същия начин САЩ са втората държава и винаги трябва да имат 2 като сериен номер.
Това ме накара да се замисля.
И ето двата начина, които бих могъл да измисля, за да избегна дублиране на серийни номера в Excel.
Метод #1 - Използване на функцията VLOOKUP
Първият начин е да използваме любимата ни функция VLOOKUP.
За да направим това, първо трябва да получим уникален списък с държави. Ето стъпките за това:
- Създайте копие от списъка с държави (копирайте го поставете в същия или друг работен лист).
- Изберете копираните данни и отидете на Данни -> Премахване на дубликати. Той ще отвори диалоговия прозорец за премахване на дублиращи се.
- Уверете се, че опцията - Моите данни имат заглавки е проверена (в случай, че вашите данни имат заглавката. Иначе я премахнете).
- Изберете колоната, от която искате да премахнете дубликатите.
- Щракнете върху OK.
- Това е. Ще имате списък с уникални имена на държави.
Вижте също: Най -доброто ръководство за намиране и премахване на дубликати в Excel.
Сега задайте серийните номера на всяка държава. Уверете се, че тези числа са въведени вдясно от уникалния списък на държави, тъй като VLOOKUP не може да извлече данни отляво на стойността за търсене.
В клетката, където искате серийните номера (B3: B15), използвайте формулата VLOOKUP по -долу:
= VLOOKUP (C3, $ F $ 3: $ G $ 8,2,0)
Тази формула VLOOKUP приема името на държавата като стойност за търсене, проверява го в данните във F3: G8 и връща серийния му номер.
Метод #2 - Динамична формула
Въпреки че методът VLOOKUP е идеален начин да направите това, той не е динамичен.
Така че, ако добавя нова държава или променя съществуваща държава, този метод няма да работи и ще трябва да повторите целия процес на метод #1 отново.
Ето формула, която я прави динамична:
= АКО (COUNTIF ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3)+1, INDEX ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1))
За да използвате тази формула, трябва ръчно да въведете 1 в първата клетка, а горната формула във всички останали останали клетки.
Как работи:
Той използва функция IF, която проверява колко пъти е възникнала държава преди този ред. Ако името на държавата се появи за първи път, броят е 1 и условието е TRUE, а ако името на страната се е появило и по -рано, броят е повече от 1 и условието е FALSE.
- Когато условието е ИСТИНА:
= MAX ($ B $ 3: $ B3) +1
Ако стойността е TRUE, което означава, че името на страната се показва за първи път, тя идентифицира максималната стойност на серийния номер до тогава и добавя 1 към нея, за да даде следващата стойност на серийния номер.
- Когато Value ако FALSE:
= ИНДЕКС ($ B $ 3: $ C $ 18, MATCH ($ C4, $ C $ 3: $ C4,0), 1)
Ако държавата вече е настъпила по -рано, тази формула отива в клетката, където се появява първа, и връща серийния номер на първото появяване на тази държава.
Изтеглете примерния файл
Може да харесате и следните уроци по Excel:
- Как да използвате Flash Fill в Excel.
- Автоматично сортиране на данни по азбучен ред с помощта на формула.
- Как бързо да попълните числа в клетки, без да влачите.
- Как да използвате Fill Handle в Excel.
