Мне позвонил друг и спросил, есть ли способ иметь серийные номера таким образом, чтобы они не дублировались в серийных номерах в Excel.
Что-то вроде того, что показано ниже:
Он хотел, чтобы серийный номер Индии был равен 1 везде, где он встречается. Точно так же США являются второй страной и всегда должны иметь 2 в качестве серийного номера.
Это заставило меня задуматься.
И вот два способа, которые я мог бы придумать, чтобы избежать дублирования серийных номеров в Excel.
Метод №1 - Использование функции ВПР
Первый способ - использовать нашу любимую функцию ВПР.
Для этого нам сначала нужно получить уникальный список стран. Вот шаги для этого:
- Создайте копию списка стран (скопируйте и вставьте ее на тот же или другой рабочий лист).
- Выделите скопированные данные и перейдите в «Данные» -> «Удалить дубликаты». Откроется диалоговое окно удаления дубликатов.
- Убедитесь, что опция - Мои данные содержат заголовки отмечена (если у ваших данных есть заголовок. В противном случае снимите флажок).
- Выберите столбец, из которого вы хотите удалить дубликаты.
- Щелкните ОК.
- Вот и все. У вас будет список уникальных названий стран.
См. Также: Полное руководство по поиску и удалению дубликатов в Excel.
Теперь присвойте серийные номера каждой стране. Убедитесь, что эти числа введены справа от уникального списка стран, поскольку функция VLOOKUP не может получать данные слева от значения поиска.
В ячейке, где вам нужны серийные номера (B3: B15), используйте приведенную ниже формулу ВПР:
= ВПР (C3; $ F $ 3: $ G $ 8,2,0)
Эта формула ВПР принимает название страны в качестве значения поиска, проверяет его в данных в F3: G8 и возвращает ее серийный номер.
Метод # 2 - Динамическая формула
Хотя метод ВПР - отличный способ сделать это, он не является динамическим.
Поэтому, если я добавлю новую страну или изменю существующую, этот метод не будет работать, и вам придется повторить весь процесс метода №1 снова.
Вот формула, которая делает его динамичным:
= ЕСЛИ (СЧЁТЕСЛИ ($ C $ 3: $ C4, $ C4) = 1, MAX ($ B $ 3: $ B3) + 1, ИНДЕКС ($ B $ 3: $ C $ 18, ПОИСКПОЗ ($ C4, $ C $ 3: $ С4,0), 1))
Чтобы использовать эту формулу, вам необходимо вручную ввести 1 в первую ячейку и указанную выше формулу во всех остальных ячейках.
Как это устроено:
Он использует функцию ЕСЛИ, которая проверяет, сколько раз страна встречалась перед этой строкой. Если название страны встречается впервые, счетчик равен 1, а условие - ИСТИНА, а если название страны также встречается раньше, счет больше 1, а условие - ЛОЖЬ.
- Когда условие ИСТИННО:
= МАКС ($ B $ 3: $ B3) +1
Если значение TRUE, что означает, что название страны появляется впервые, оно определяет максимальное значение серийного номера до этого момента и добавляет к нему 1, чтобы получить следующее значение серийного номера.
- Когда Значение, если ЛОЖЬ:
= ИНДЕКС ($ B $ 3: $ C $ 18; ПОИСКПОЗ ($ C4; $ C $ 3: $ C4,0); 1)
Если страна уже встречалась ранее, эта формула переходит в ячейку, где она появляется первой, и возвращает порядковый номер первого появления этой страны.
Загрузите файл примера
Вам также могут понравиться следующие руководства по Excel:
- Как использовать флэш-заливку в Excel.
- Автоматическая сортировка данных в алфавитном порядке с помощью формул.
- Как быстро заполнять числа в ячейках без перетаскивания.
- Как использовать Fill Handle в Excel.
