Загальна формула
=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A$1:A1,A1)=1,"x","xx"),"")
Резюме
Щоб позначити перший дублікат у списку, ви можете використовувати формулу на основі функції COUNTIF. За бажанням, ви можете позначити наступні дублікати іншим маркером. У наведеному прикладі формула в комірці С4 має вигляд:
=IF(COUNTIF($B$4:$B$11,B4)>1,IF(COUNTIF($B$4:B4,B4)=1,"x","xx"),"")
Ця формула скопійована в стовпець з C4 до C11.
Пояснення
По суті, ця формула складається з двох наборів функції COUNTIF, загорнутих у функцію IF. Зовнішній IF + COUNTIF спочатку перевіряє, чи не відображається значення (B4) більше одного разу у списку:
=IF(COUNTIF($B$4:$B$11,B4)>1
Якщо ні, зовнішня функція IF повертає порожній рядок ("") як кінцевий результат. Якщо значення відображається більше одного разу, ми запускаємо ще одне комбіноване IF + COUNTIF. Цей виконує роботу з позначення дублікатів:
IF(COUNTIF($B$4:B4,B4)=1,"x","xx")
У цій частині формули використовується посилання на розширення ($ B $ 4: B4), яке розширюється під час копіювання формули в стовпець. (Перший B4 у діапазоні є абсолютним (заблокованим), другий - відносним, тому він змінюється під час копіювання формули у списку).
Пам'ятайте, що ця частина формули виконується лише в тому випадку, якщо перший COUNTIF повернув число, більше 1. Таким чином, у кожному рядку формула перевіряє кількість усередині діапазону до поточного рядка. Якщо підрахунок дорівнює 1, ми позначаємо дублікат знаком "x", оскільки це перший, який ми бачили. Якщо це не 1, ми знаємо, що це повинен бути наступний дублікат, і ми позначаємо "xx"
Основна формула
Щоб позначити перший дублікат у списку лише 0 або 1, ви можете скористатися цією скороченою формулою, яка використовує розширюваний діапазон і функцію COUNTIFS.
=(COUNTIFS($B$5:B5,B5)=2)+0
Ця формула поверне 1 лише тоді, коли значення зустрічалося двічі - перше входження поверне нуль:
Для позначення другого та всіх наступних випадків формула у F5 вище:
=(COUNTIFS($E$5:E5,E5)>=2)+0
Примітка: В обох прикладах додавання нуля - це просто простий спосіб примусити значення TRUE і FALSE до 1 і 0.
Крім того, використання COUNTIFS замість COUNTIF дає змогу оцінити значення в інших стовпцях як частину тесту на дублікати. Кожен додатковий стовпець також потрібно вводити як розширюваний діапазон.
