
Итак, вот моя ситуация: я создаю таблицу, в которой у меня есть 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. Заранее благодарю за любую помощь.
решение1
Шаг за шагом (для поддержки любого уровня будущих читателей)
Используйте функцию COUNTIF()
, чтобы узнать, содержит ли диапазон значение. Например, чтобы проверить, 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