Мені подзвонив друг і запитав, чи є спосіб мати серійні номери таким чином, щоб вони не дублювали серійні номери в 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 ще раз.
Ось формула, яка робить її динамічною:
= IF (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, а умова - ІСТИНА, а якщо назва країни трапилася і раніше, то кількість більше 1, а умова - НЕВІРНО.
- Коли умова істинна:
= MAX ($ B $ 3: $ B3) +1
Якщо значення TRUE, це означає, що назва країни з'являється вперше, воно визначає максимальне значення серійного номера до цього часу і додає до нього 1, щоб дати наступне значення серійного номера.
- Коли значення, якщо FALSE:
= ІНДЕКС ($ B $ 3: $ C $ 18, МАТЧ ($ C4, $ C $ 3: $ C4,0), 1)
Якщо країна вже відбулася раніше, ця формула переходить до осередку, де вона з’являється першою, і повертає серійний номер першого входження цієї країни.
Завантажте файл прикладу
Вам також можуть сподобатися такі підручники Excel:
- Як використовувати програму Flash Fill в Excel.
- Автоматично сортувати дані в алфавітному порядку за допомогою формули.
- Як швидко заповнити числа в клітинках без перетягування.
- Як використовувати маркер заповнення в Excel.