Итак, вот моя ситуация: я создаю таблицу, в которой у меня есть 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