Вчера вечером на радио-шоу Крейга Кроссмана «Компьютерная Америка» Джо из Бостона задал вопрос:
У меня есть столбец с номерами счетов. Как я могу использовать Excel для отметки дубликатов?
Я предложил использовать условные форматы и формулу СЧЁТЕСЛИ. Вот подробности о том, как заставить это работать.
Мы хотим настроить условное форматирование для всего диапазона, но проще установить условный формат для первой ячейки в диапазоне, а затем скопировать этот условный формат. В нашем случае ячейка A1 имеет заголовок с номером счета, поэтому я выберу ячейку A2 и в меню выберите «Формат»> «Условное форматирование». Диалоговое окно «Условное форматирование» начинается с начального раскрывающегося списка «Значение ячейки». Если вы коснетесь стрелки рядом с этим, вы можете выбрать «Formula Is».
После выбора «Формула Is» диалоговое окно меняет внешний вид. Вместо полей для «Между x и y» теперь есть одно поле формулы. Эта коробка с формулами невероятно мощная. Вы можете ввести любую формулу, которую только можете придумать, если она будет иметь значение ИСТИНА или ЛОЖЬ.
В нашем случае нам нужно использовать формулу СЧЁТЕСЛИ. Формула для ввода в поле:
=COUNTIF(A:A,A2)>1
По-английски это говорит: «просмотрите весь диапазон столбца A. Подсчитайте, сколько ячеек в этом диапазоне имеют то же значение, что и в ячейке A2. (Очень важно, чтобы« A2 »в формуле указывал на текущая ячейка - ячейка, в которой вы устанавливаете условное форматирование. Итак, если ваши данные находятся в столбце E, и вы устанавливаете первое условное форматирование в E5, формула будет иметь вид =COUNTIF(E:E,E5)>0). Затем мы сравниваем, чтобы увидеть, учитывается ли это is> 1. В идеале, без дубликатов, счет всегда будет 1 - поскольку ячейка A2 находится в диапазоне, мы должны найти ровно одну ячейку в столбце A, которая содержит то же значение, что и A2.
Нажмите кнопку Форматировать…
Теперь пора выбрать неприятный формат. В верхней части диалогового окна «Формат ячеек» есть три вкладки. Вкладка «Шрифт» обычно первая, так что вы можете выбрать жирный красный шрифт, но мне нравятся более неприятные. Обычно я щелкаю вкладку «Узоры» и выбираю ярко-красный или ярко-желтый. Выберите цвет, затем нажмите «ОК», чтобы закрыть диалоговое окно «Формат ячеек».
Вы увидите выбранный формат в поле «Предварительный просмотр формата для использования». Нажмите ОК, чтобы закрыть диалоговое окно «Условное форматирование».
… И ничего не происходит. Ух ты. Если вы впервые настраиваете условное форматирование, было бы неплохо получить здесь отзыв о том, что это сработало. Но, если вам не повезло, что 1098 в ячейке A2 является дубликатом какой-то другой ячейки, условие неверно, и похоже, что ничего не произошло.
Вам нужно скопировать условное форматирование из A2 в другие ячейки вашего диапазона. С подоконником курсора в A2, выполните Edit> Copy. Нажмите Ctrl + пробел, чтобы выделить весь столбец. Сделайте Edit> Paste Special. В диалоговом окне «Специальная вставка» нажмите «Форматы». Щелкните ОК.
Это скопирует условное форматирование во все ячейки столбца. Теперь - наконец - вы видите несколько ячеек с красным форматированием, что указывает на то, что у вас есть дубликат.
Информативно перейти к ячейке A3 и посмотреть условный формат после копии. Выберите A3, нажмите od, чтобы вызвать условное форматирование. Формула в поле «Формула Is» изменена, чтобы подсчитать, сколько раз A3 появляется в столбце A: A.
Заметки
На вопрос Джо, у него было всего 1700 счетов в диапазоне. Я установил 65536 ячеек с условным форматированием, и каждая ячейка сравнивает текущую ячейку с 65536 другими ячейками. В Excel 2005 - с большим количеством строк - проблема будет еще хуже. Технически формула на первом этапе могла быть такой:=COUNTIF($A$2:$A$1751,A2)>1
Кроме того, при копировании условного формата во весь столбец вы могли бы вместо этого выбрать только строки с данными перед выполнением вставки специальных форматов.
Больше
Другая проблема, которую я описал после вопроса, заключается в том, что вы действительно не можете отсортировать столбец на основе условного формата. Если вам нужно отсортировать эти данные так, чтобы дубликаты находились в одной области, выполните следующие действия. Сначала добавьте заголовок к B1 под названием «Дублировать?». Введите эту формулу в B2: =COUNTIF(A:A,A2)>1.
Когда указатель ячейки находится в положении B2, щелкните маркер автозаполнения (маленький квадрат в правом нижнем углу ячейки), чтобы скопировать формулу до конца диапазона.
Теперь вы можете сортировать по столбцу B по убыванию и по столбцу A по возрастанию, чтобы проблемные счета-фактуры находились в верхней части диапазона.
Это решение предполагает, что вы хотите выделить ОБЕИХ дубликатов счетов-фактур, чтобы вы могли вручную решить, какие из них удалить или исправить. Если вы не хотите , чтобы отметить первое вхождение дубликата, вы можете настроить формулу быть: =COUNTIF($A$2:$A2,A2)>1. Важно вводить знаки доллара точно так, как показано. Это будет смотреть на все ячейки, начиная с текущей, только вверх, ища повторяющиеся записи.
Спасибо Джо из Бостона за вопрос!
