Excel

Cell означает одно из многих

Cell Equals One Many Things

Формула Excel: ячейка означает одно из многихУниверсальная формула
= 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
Автор Дэйв Брунс


^