
Мне нужно условно отформатировать ячейки с разными значениями в одних и тех же столбцах,еслиих значение для конкретного столбца одинаково.
Сценарий
Моя электронная таблица представляет собой базу данных контактов, где каждая строка представляет собой запись контакта, а каждый столбец содержит тип данных (фамилия, улица 1, улица 2, город, штат и т. д.) для каждой записи контакта.
Электронная таблица представляет собой объединение контактной информации, хранящейся в разных офисах нашей компании. Мы объединили всю контактную информацию в один источник, чтобы иметь возможность согласовывать любые расхождения и в дальнейшем хранить все наши контакты в одном месте.
У некоторых контактов есть только одна запись — например, когда только один из наших офисов имел данные для этого контакта, поэтому они не могут не совпадать с данными, которые у нас есть из любого другого офиса; у других контактов есть несколько записей (например, может быть только одна строка для Фреда Джонсона, но четыре строки для Джейн Смит). Каждый контакт должен иметь ту же самую точную контактную информацию, прежде чем мы сможем загрузить ее в облачную базу данных, иначе будут созданы дубликаты.
Мы потратили много времени, пытаясь согласовать данные в записях об одном и том же человеке, и теперь нам нужно выяснить, где еще существуют расхождения.
Я объединил поля LastName и FirstName, чтобы можно было сравнивать друг с другом строки с одинаковым значением в этом поле. Цель состоит в том, чтобы выделить ячейки с разными данными в одном столбце при сравнении с другими записями с таким же значением в столбце LastFirst (если таковые имеются).
Как я могу это сделать?
Пока я знаю, что функция Find Special может выделять ячейки при сравнении с указанной строкой или столбцом, но мне нужно, чтобы сравнения выполнялись со строками, имеющими одинаковое значение для определенного столбца.
У меня есть доступ к Excel 2010 и 2013 для выполнения этой функции.
Спасибо!
решение1
Возможно, есть более изящные решения, но вам не нужно объединять имя и фамилию, чтобы создать столбец «FullName».
Предварительный просмотр нашего продукта
(белые ячейки справа показывают, какой вывод будет выдавать наша формула для управления условным форматированием):
Вот наша формула
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
-COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
Не забудьте скорректировать данные в соответствии с количеством строк в вашем наборе данных, а также положением столбцов «Имя» и «Фамилия», если они не находятся в столбцах A и B.
Я также предположил, что ваши первые неименные данные начинаются в ячейке C2 - т.е. это самый верхний левый бит данных, который вы хотите проверить на несоответствия. Отредактируйте "C" в этой формуле, если нужно.
Применение условного форматирования
Скопируйте формулу выше (внеся все необходимые изменения), затем щелкните по самой верхней левой ячейке, которую вы хотите проверить на несоответствия (далее я буду называть ее «C2»). Теперь выберите кнопку на Conditional Formatting
ленте и выберите New Rule
.
Выберите Use a formula to determine which cells to format
, затем вставьте формулу в текстовое поле (убедитесь, что нет пробелов — если вы скопировали сверху, вам придется убрать несколько). Теперь вы можете щелкнуть, Format
чтобы задать, как вы хотите выделить несоответствия. Я выбрал (яркую!) красную заливку. Щелкните, OK
пока не вернетесь к таблице.
Если у вас нет несоответствий в ячейке C2, то может показаться, что ничего не произошло, но это потому, что нам все еще нужно применить правило ко всему набору данных. Сохраняя C2 выделенным, щелкните Manage Rules
в Conditional Formatting
меню ленты.
Теперь вы можете выбрать весь диапазон, который вы хотите проверить на несоответствия. В Applies to
поле щелкните и перетащите, чтобы выбрать все, что вы хотите сравнить (или, если у вас много строк, для скорости просто введите ссылку на ячейку =$C$2:$Z$999
)
Нажмите «ОК» и всё!
Как это работает
Эта формула используется COUNTIFS()
для подсчета количества строк для данного человека:
=COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2)
Затем мы вычисляем, сколько строк содержат имя этого человека И значение в проверяемом вами столбце. Если все строки идентичны, то это число должно точно совпадать с первым COUNTIFS()
.
COUNTIFS($A$1:$A$100,$A2,$B$1:$B$100,$B2,C$1:C$100,IF(C2="","",C2))
Если мы вычтем последнее из первого, и все строки совпадут, то формула выведет 0 и не выполнит условное форматирование. Однако, если что-то отличается, то вывод будет 1 или выше, что вызовет условное форматирование.
Примечание
Мне пришлось оформить окончательные COUNTIFS()
критерии в виде IF()
утверждения, чтобы иметь дело с пробелами, — COUNTIFS
пробелы ему не очень нравятся (кажется, он не уверен, считать ли их 0 или "". Странно).
Загрузка файла
Этот пример документа такжедоступно для скачивания.