= SUMPRODUCT (--(A1=things))>0Резюме
Если вы хотите проверить ячейку, чтобы узнать, соответствует ли она одному из нескольких значений, вы можете сделать это с помощью формулы, в которой используется функция СУММПРОИЗВ.
самая частая ошибка при вводе формулы - ссылка на неправильную ячейку в формуле
Контекст
Допустим, у вас есть список текстовых строк в диапазоне B5: B11, и вы хотите проверить каждую ячейку на соответствие другому списку элементов в диапазоне E5: E9. Другими словами, для каждой ячейки в B5: B11 вы хотите знать: совпадает ли эта ячейка с чем-либо из элементов в E5: E9?
Вы можете начать строить большую формулу на основе вложенные операторы IF , но формула массива на основе SUMPRODUCT - более простой и понятный подход.
Решение
Решение состоит в том, чтобы создать формулу, которая будет проверять несколько значений и возвращать список значений ИСТИНА / ЛОЖЬ. Когда у нас есть это, мы можем обработать этот список (фактически, массив) с помощью SUMPRODUCT.
Формула, которую мы используем, выглядит так:
построить круговую диаграмму в Excel
Объяснение= SUMPRODUCT (--(B5=things))>0
Ключ в этом фрагменте:
--(B5=things)
который просто сравнивает значение в B5 с каждым значением в именованном диапазоне «вещи». Поскольку мы сравниваем B5 с массивом (то есть именованным диапазоном 'things', E5: E11), результатом будет массив значений ИСТИНА / ЛОЖЬ, подобный этому:
{TRUEFALSEFALSEFALSEFALSE}
Если у нас есть хотя бы одно ИСТИНА в массиве, мы знаем, что B5 равно по крайней мере одному элементу в списке, поэтому, чтобы принудительно установить значения ИСТИНА / ЛОЖЬ до 1 и 0, мы используем двойной отрицательный (-, также называемый двойным унарный). После этого принуждения мы имеем следующее:
{10000}
как преобразовать в юлианскую дату
Теперь мы обрабатываем результат с помощью SUMPRODUCT, который складывает элементы в массив. Если мы получаем какой-либо результат, отличный от нуля, у нас есть по крайней мере одно совпадение, поэтому мы используем> 1, чтобы получить окончательный результат ИСТИНА или ЛОЖЬ.
С жестко запрограммированным списком
Нет требований, чтобы вы использовали диапазон для своего списка вещей. Если вы ищете лишь небольшое количество вещей, вы можете использовать список в формате массива, который называется константой массива. Например, если вы просто ищете красный, синий и зеленый цвета, вы можете использовать {'красный', 'синий', 'зеленый'} следующим образом:
--(B5={'red','blue','green'})
Работа с лишним пространством
Если в тестируемых ячейках есть лишнее пространство, они не будут соответствовать друг другу. Чтобы удалить все лишнее пространство, вы можете изменить формулу для использования функции TRIM следующим образом:
Автор Дэйв Брунс= SUMPRODUCT (--( TRIM (A1)=things))>0