У меня есть два списка в таблице. Оба списка состоят из двух столбцов. Один столбец — это серийные номера компьютеров, а другой столбец — это лицо, которому назначен компьютер из первого столбца.
Списки были созданы двумя разными людьми, и мне нужно сравнить их и найти несоответствия. Вот пример этой проблемы: 
.
В этом примере мне нужно убедиться, что Person A назначен CPU1 как в наборе данных A, так и в наборе данных B. Если есть расхождения, то приоритет должен иметь набор данных B. Однако если в наборе данных A есть человек, которого нет в наборе данных B, этот человек должен остаться в списке.
решение1
Поэтому вам нужно сопоставить список A со списком B, внося изменения для соответствия списку B, где это необходимо, и используя данные списка A, где это не так. И, вероятно, вы хотели бы знать о проблемах, которые возникают.
Что касается первой части, вам нужен новый столбец в списке A, лучше всего вставить его между двумя существующими столбцами, чтобы он немедленно стал частью любого диапазона или другой ссылки в любом месте электронной таблицы, затем столбец, который он сдвинул вправо, вырезать и вставить перед ним, а не после него, чтобы вернуться к исходному материалу с новым столбцом справа от него, при этом новый столбец является частью любой ссылки на материал списка A.
Для второго есть по крайней мере два события, которые, как можно предположить, вас заинтересуют: 1) Список A содержит данные, которые не соответствуют списку B. Результат в столбце — «правильный» список B, но есть несоответствие, и 2) После проверки показанный результат является дубликатом других показанных результатов. Другими словами, вы показываете по крайней мере двух Persons, назначенных на определенный CPU, и если List B не содержит дубликатов, это происходит потому, что List B говорит об одном person, а List A показывает одного или нескольких других, имеющих этот CPU. Только результат списка B Person может быть правильным, поэтому остальные представляют проблемы.
Чтобы выполнить проверку, вставьте новый столбец, затем верните столбец, который был сдвинут, на место. Я скажу, что ваши данные в настоящее время: Список A — это столбцы A и B, черный столбец — это столбец C, а Список B — это столбцы D и E. В итоге у вас будет новый столбец C, сдвигающий все остальные на один столбец вправо, так что Список B теперь — это столбцы E и F. Строка заголовка — это строка 1 в электронной таблице, и я сделаю вид, что данные — это строки 2:11.
Поскольку список B переопределяет любые различия со списком A, формула будет искать каждого человека из списка A в списке B и возвращать то, что есть в списке B, если в нем есть запись для этого человека, и возвращать то, что есть в списке A, если записи в списке B нет:
XLOOKUP($B2,$F$2:$F$5,$E$2:$E$5,$A2,0,1)
Использовался хороший, новый, XLOOKUP()но если у вас его нет, вам понадобится Index/Matchрешение, так как вам придется «смотреть налево» в списке B.
Затем проблемы. У вас есть по крайней мере два варианта, как с ними справиться. Вероятно, другие, например, вы могли бы написать макрос, чтобы сделать это, но я просто подумаю о формулах. Один из способов — усложнить это XLOOKUP()с помощью тестов для проблемы. Большинство людей, похоже, громко заржали и ушли в этом направлении. Но есть гораздо более простой способ, который позволяет выполнить массу тонких настроек, если это желательно, хотя вам здесь нужны только основы. Он заключается в использовании условного форматирования («УФ») для выполнения тестирования, а не в превращении этой формулы выше в монстра из 20 строк, которого вы на самом деле не понимаете, даже когда только что закончили.
С CF вам нужно только выбрать порядок перечисления правил, чтобы они проверялись в том порядке, который работает правильно. (Большинство людей сначала продумывают тесты. Если вы сначала выбираете порядок, то затем вам нужно разработать тесты, которые будут соответствовать этому порядку. Обычно гораздо проще пойти другим путем, что люди и делают.)
Итак, если есть более одного Persons, перечисляющего конкретный CPU, это называется "дубликаты", и в CF есть встроенное правило для этого. Сначала его легко проверить, поэтому настройте его. Я перейду к формату для применения через минуту, потому что с ним есть "трюк". Затем создайте второе правило, которое использует эту формулу для своего теста:
=XLOOKUP($B2,$F$2:$F$5,$E$2:$E$5,$A2,0,1)<>$A2
Обратите внимание, что это новое правило вставляет себя перед правилом дубликатов. Это все хорошо, они не будут работать, если вы переместите это правило вниз, чтобы оно стало вторым проверенным правилом. ОТМЕТЬТЕ флажок «Остановить, если True».
Теперь CF проверит, соответствует ли результат формулы исходным данным Списка А для Персоны. Если соответствует, то применит формат здесь и остановится. Если нет, то перейдет ко второму правилу.
Второе правило проверяет наличие дублирования и применяет соответствующий формат, если оно есть.
Итак, «трюк»… вы будете использовать довольно редкую возможность форматирования, чтобы поместить сообщения об ошибках в новые результаты списка А (вместо того, чтобы мучиться с их подгонкой под формулу ячейки, мучиться с работой и мучиться с попытками снова во всем этом разобраться, чтобы обновить или исправить).
Обычное числовое форматирование позволяет вам задать форматирование для четырех ВИДОВ данных в ячейке: положительных чисел, отрицательных чисел, нулей и текста. В любом из них вы можете поместить текстовые строки (множественное число) в формат. Фактически, формат может быть ничем иным, КРОМЕ текстовой строки. Для обоих этих правил CF вы будете использовать этот факт. Для первого правила используйте следующее, именно так, как вы его видите:
;;;"Список А несоответствие"
а для второго правила используйте следующее:
;;;">1 назначено"
Чтобы они лучше выделялись (они длиннее текста «CPU», но...), вы можете отформатировать текст другим цветом, по крайней мере, может быть, приятным красным.
А если вам нужен просто список без каких-либо отмеченных проблем, просто напишите формулу в самом начале и запомните эту штуку с CF на другой день, когда вам понадобится простая, понятная формула в ваших ячейках и все проверки на наличие ошибок и выдача соответствующих сообщений в простых правилах в CF, вместо того чтобы превращать формулы ячеек в кошмар.