Сравнить содержимое списка в Excel

Question

Итак, вот моя ситуация: я создаю лист, где у меня есть 9 групп по 3 списка, в которых все выбранные элементы содержатся в списках, созданных с помощью проверки данных. Что мне нужно сделать, так это добавить оценку к каждой из этих групп по 3, если 3 варианта, выбранные из списка проверки данных, уникальны по сравнению с вариантами, выбранными в предыдущих группах по 3 (обратите внимание, что группы из 3 никогда не будут один и тот же выбор, они всегда будут уникальны друг для друга).

В основном, скажем, в группе 1 у вас есть синий, зеленый и фиолетовый цвета, что дает значение 1, поскольку выбор уникален. Во 2-й группе у вас есть оранжевый, желтый и розовый, что также дает значение 1, поскольку выбор уникален. Так и так далее, однако, скажем, в группе 9 у вас есть пурпурный, красный и фиолетовый цвета - эта группа получит 0, поскольку она не является полностью уникальной (фиолетовый был выбран в группе 1). Обратите внимание, что выбор делается в разное время и оценивается через эти отдельные интервалы - поэтому, когда группа 2 сравнивается только с группой 1, группа 3 сравнивается как с группой 1 и 2, и так далее.

Я попробовал формулу, похожую на:

=IF(NOT(B1:B3=A1:A3),1,0)

Тем не менее, в ячейке отображается оценка # #VALUE! вместо 1 или 0. Мне интересно, нужно ли мне делать каждую ячейку по отдельности, поэтому, если содержимое ячейки B1 не равно содержимому в A1, A2 или A3, проверьте содержимое ячейки B2 и не совпадают ли они с содержимым ячейки A1, A2 или A3 затем проверяют содержимое ячейки B3 и, если они не равны содержимому A1:A3, тогда значение этого списка равно 1. Если мне нужно сделать это индивидуально, есть идеи, как бы я сформулировал формулу? Будет ли это что-то вроде:

=IF(NOT(A1:A3=B1,NOT(A1:A3=B2,NOT(A1:A3=B3),1,0)))

Можно ли вообще сделать это в Excel?

Я использую самую последнюю версию Excel (Office 365) с Windows 10. Спасибо заранее за любую помощь.

Answer 1

Шаг за шагом (для поддержки любого уровня будущих читателей)

Используйте функцию COUNTIF() чтобы узнать, содержит ли диапазон значение. IOW, чтобы проверить, находится ли значение в B1 в диапазоне A1:A3 , вы можете написать

=COUNTIF(A1:A3,B1)

который вы можете обратиться к логическому результату, сравнивая с 0 (то есть B1 не в A1:A3)

=COUNTIF(A1:A3,B1)=0

Но вам также нужно проверить, что B2 и B3 не встречаются в A1:A3 . Вы можете использовать функцию AND() для этого:

=AND(COUNTIF(A1:A3,B1)=0,COUNTIF(A1:A3,B2)=0,COUNTIF(A1:A3,B3)=0)

который вернет True, если ни один из B1 , B2 и B3 встречается в A1:A3 . Чтобы мы могли скопировать эту функцию по строке и настроить диапазон, который будет проверяться, мы изменим ссылку на первую ячейку на фиксированную ссылку на столбец, A1:A3 -> $A1:A3 . Когда мы скопируем формулу справа, диапазон увеличится до $A1:B3 , $A1:C3 и так далее до $A1:I3 для девятого столбца.

Приведенная выше формула возвращает логический результат, True или False . Вы указали, что вам нужно 1 или 0, что легко сделать, добавив функцию IF() вокруг предыдущей формулы.

Теперь мы можем написать формулу для ячейки B4 как

=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0),1,0)

затем скопируйте в ячейки C4:I4

Поскольку, по вашему определению, каждая группа из 3 уникальна для каждой другой, в A4 формула не требуется, вы можете просто установить ее в 1.

Обновить

Заметка! Пустые ячейки в диапазоне, подобном C1:C3 приводят к 1. Вы не указали его, но вы можете захотеть, чтобы группа с отсутствующими значениями возвращала 0. Это можно сделать, добавив четвертый член в функцию AND() : COUNTBLANK((B1:B3)=0 . Запись в B4 будет

=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0,COUNTBLANK(B1:B3)=0),1,0)

перед копированием в ячейки C4:I4