Вчора ввечері в радіо-шоу Крейга Кроссмена "Комп'ютерна Америка" у Джо з Бостона виникло запитання:
У мене є графа номерів рахунків-фактур. Як я можу використовувати Excel для позначення дублікатів?
Я запропонував використовувати умовні формати та формулу COUNTIF. Ось деталі про те, як змусити це працювати.
Ми хочемо встановити умовне форматування для всього діапазону, але простіше встановити умовний формат для першої комірки в діапазоні, а потім скопіювати цей умовний формат. У нашому випадку комірка А1 має заголовок номера рахунка-фактури, тому я виберу комірку А2 і з меню виберіть Формат> Умовне форматування. Діалогове вікно умовного форматування починається з початкового випадаючого списку, який говорить "Значення комірки є". Якщо торкнутися стрілки поруч із цим, ви можете вибрати "Формула є".
Після вибору «Формула є», діалогове вікно змінює вигляд. Замість полів для "Між х та у", тепер є одне поле формули. Цей блок формул неймовірно потужний. Ви можете ввести будь-яку формулу, яку ви можете придумати, поки ця формула буде мати значення TRUE або FALSE.
У нашому випадку нам потрібно використовувати формулу COUNTIF. Формула, яку потрібно ввести у поле, є
=COUNTIF(A:A,A2)>1
По-англійськи, це говорить, "перегляньте весь діапазон стовпця А. Підрахуйте, скільки комірок у цьому діапазоні мають таке саме значення, що і в А2. (Насправді важливо, щоб" А2 "у формулі вказувало поточна комірка - осередок , що ви налаштовуєте умовне форматування в Так. - якщо ваші дані в стовпці E і ви встановлюєте перший умовне форматування в E5, то формула буде =COUNTIF(E:E,E5)>0). Потім ми порівняємо , щоб побачити , якщо цей рахунок є> 1. В ідеалі, без дублікатів, підрахунок завжди буде 1 - оскільки комірка А2 знаходиться в діапазоні - ми повинні знайти точно одну клітинку в стовпці А, яка містить таке саме значення, як А2.
Натисніть кнопку Формат …
Тепер настав час вибрати неприємний формат. У верхній частині цього діалогового вікна Форматування комірок є три вкладки. Вкладка «Шрифт» зазвичай перша, тому ви можете вибрати жирний червоний шрифт, але мені подобається щось більш неприємне. Зазвичай я переходжу на вкладку Візерунки і вибираю яскраво-червоний або яскраво-жовтий. Виберіть колір, а потім натисніть кнопку OK, щоб закрити діалогове вікно Форматування клітинок.
Ви побачите вибраний формат у полі "Попередній перегляд формату, який буде використано". Натисніть OK, щоб закрити діалогове вікно умовного форматування …
… і нічого не відбувається. Ого. Якщо ви вперше налаштовуєте умовне форматування, було б дуже приємно отримати тут відгук про те, що це спрацювало. Але, якщо вам не пощастить, що 1098 у комірці А2 є дублікатом якоїсь іншої комірки, умова не відповідає дійсності, і схоже, нічого не сталося.
Вам потрібно скопіювати умовне форматування з A2 до інших комірок вашого діапазону. З підвіконням курсора в А2 виконайте «Правка»> «Копіювати» Натисніть Ctrl + Пробіл, щоб виділити весь стовпець. Виконайте редагування> Спеціальна вставка. У діалоговому вікні Вставити спеціальне натисніть Формати. Клацніть OK.
Це призведе до копіювання умовного форматування до всіх комірок стовпця. Тепер - нарешті - ви бачите кілька комірок із червоним форматуванням, що вказує на наявність дубліката.
Інформативно перейти до комірки A3 і переглянути умовний формат після копії. Виберіть A3, натисніть клавішу od, щоб викликати умовне форматування. Формула у полі Формула змінилася, щоб підрахувати, скільки разів A3 відображається у стовпці A: A.
Примітки
У запитанні Джо, у нього було лише 1700 рахунків-фактур. Я налаштував 65536 комірок із умовним форматуванням, і кожна комірка порівнює поточну комірку з 65536 іншими клітинками. У Excel 2005 - з більшою кількістю рядків - проблема буде ще гіршою. Технічно формула на першому кроці могла бути такою:=COUNTIF($A$2:$A$1751,A2)>1
Крім того, під час копіювання умовного формату до цілого стовпця, ви могли б замість цього вибрати лише рядки з даними перед тим, як вставляти спеціальні формати.
Більше
Інша проблема, яку я описав після запитання, полягає в тому, що ви дійсно не можете сортувати стовпець на основі умовного формату. Якщо вам потрібно відсортувати ці дані так, щоб дублікати були в одній області, виконайте такі дії. Спочатку додайте заголовок до B1, який називається "Дублікат?". Введіть цю формулу в B2: =COUNTIF(A:A,A2)>1.
За допомогою вказівника комірки в B2 клацніть маркер автозаповнення (маленький квадрат у нижньому правому куті комірки), щоб скопіювати формулу до кінця діапазону.
Тепер ви можете сортувати за стовпчиком B за спаданням та за зростанням, щоб фактури із проблемами знаходилися вгорі діапазону.
Це рішення передбачає, що ви хочете виділити І ДВЕ дублікати рахунків-фактур, щоб ви могли вручну з’ясувати, які видалити чи виправити. Якщо ви не хочете , щоб відзначити перше входження дубліката, ви можете налаштувати формулу бути: =COUNTIF($A$2:$A2,A2)>1. Важливо вводити знаки долара точно так, як показано. Це буде переглядати всі комірки лише з поточної комірки вгору, шукаючи повторювані записи.
Дякую Джо з Бостона за запитання!
