Как мне создать эту формулу, которая будет применена к столбцу C (возвращая логическое значение)?
Если B2 содержит «яблоко», проверьте, существует ли число в A2 в других ячейках A, строка которыхне делаетсодержат «яблоко» в столбце B. (Предполагается, что другие строки также содержат «яблоко» в столбце B и могут иметь или не иметь повторяющееся число в столбце A).
Вот пример данных и желаемый результат в столбце C:
решение1
Используйте ЕСЛИ и СЧЁТЕСЛИМН:
=IF(B2="apple",COUNTIFS(A:A,A2,B:B,"<>"&B2)>0,"")
Сначала мы проверяем, является ли B2 «яблоком»
Во-вторых, мы проверяем, есть ли какие-либо элементы, соответствующие яблоне, а затем смотрим, есть ли элементы, соответствующие ячейке A2, но не содержащие слово «яблоко» в столбце B.
решение2
Быстрый ответ:
Вы можете сделать это с помощью формул массива. В вашей ячейке C2напишите следующее:
=IF(B2="apple",SUM(ISNUMBER(A:A)*(A:A=A2)*(B:B<>B2))>0,"")
затем используйте Ctrl+Shift+Enter, чтобы зарегистрировать его как формулу массива. Скопируйте его в остальную часть столбца.
Объяснение:
Функция IF— это обычная сортировка: если в столбце B мы видим «яблоко», мы делаем что-то, в противном случае просто пишем пустую строку. Интересная часть — это второй аргумент — тот, который дает нам результат в случае «яблоко».
Теперь сначала рассмотрим, что Excel будет делать с более простой формулой: SUM(A:A=A2). Внутри SUMExcel видит равенство в форме «диапазон = значение». Если бы обе стороны были простыми значениями, это было бы оценено как логическое значение, но здесь, поскольку вы использовали Ctrl+Shift+Enter для включения формул массива, Excelприменяет операцию к каждому элементу с левой стороны по отдельности, и сохраняет их в памяти во временном массиве, который SUMс радостью принимает. Результатом является количество ячеек в столбце, Aкоторые равны A2...
Ну, почти. Это было бы так, если бы значения SUMобрабатывались TRUEкак 1, а FALSEзначения как 0. В противном случае нам пришлось бы преобразовывать булевы значения в числа, одним из способов является двойное отрицание, например: SUM(--(A:A=A2)). *Оператор в фактической формуле тоже позаботится об этом.
Возвращаясь к фактической сумме, у нас есть дополнительный член ISNUMBER(A:A). Принцип аналогичен: это снова будет работать поэлементно над столбцом, Aпоскольку обычно не знает, что делать с диапазоном. Затем *оператор «умножает» два временных диапазона, содержащих булевы значения, поэлементно — в основном применяя ANDк ним операцию — и давая нам новый диапазон булевых значений. (Это здесь просто для того, чтобы гарантировать, что пустые ячейки не будут считаться равными 0, и NOT(ISBLANK(A:A))будет работать аналогичным образом.)
Наконец, мы делаем то же самое с дополнительным диапазоном логических значений, которые описывают, Bявляется ли каждое отдельное значение в столбценетравно B2. В конце концов, SUMзатем действует на полученный диапазон.
В заключение, мы посчитали строки с числами в первом столбце, где текущая строка совпадает в столбце A, но не в столбце B. Ваше условие просто говорит, что число этих чисел положительно.
Замечание 1:
Мы используем *оператор вместо функции, ANDпоскольку последняя по умолчанию принимает диапазоны в качестве аргумента, поэтому она просто съест временные диапазоны вместо активации «режима массива» и выполнения поэлементной операции.
Замечание 2:
Вы можете свободно сделать столбцы абсолютными для копирования формулы в другие столбцы, добавить (абсолютные) номера строк к диапазонам A:Aи B:Bигнорировать другие данные, потенциально расположенные в том же столбце (а также производительность) — или даже создать именованные диапазоны из двух столбцов, например IDsи Fruits, а затем подставить эти имена в формулу вместо диапазонов.