Excel: Как сделать подсчет количества ячеек, содержащих "X", и ячейка выше содержит " Y"

Я предлагаю решение, для которого требуется один вспомогательный столбец. Предполагая, что ваши данные расположены в ячейках A3: J8, используйте столбец K в качестве помощника и поместите в него следующую формулу массива

{=SUM(IF($A4:$J4="1a",IF($A3:$J3="Math",1,0)))}

поместите формулу без фигурных скобок, а затем из строки формул нажмите CTRL+SHIFT+ENTER, чтобы сделать ее формулой массива. Это дает граф '1А' с математикой слово, что. Начните с K4 и перетащите его вниз. Для альтернативных строк возвращается 0, но это не имеет значения. Теперь просто подведем итоги Вспомогательный Столбец. Для этого можно использовать простую функцию SUM. Возможно, есть более разумные способы достичь этого без вспомогательного столбца или даже использовать макросы VBA.

вы можете использовать формулу COUNTIFS. Это позволяет указать несколько критериев. Используя пример pat2015, если данные находятся в диапазоне A3:J8, формула должна быть

=COUNTIFS(A3:J7;"Math";A4:J8;"1a")

(обратите внимание на смещение на одну строку между критериями)

Если вы переформатировать данные, чтобы иметь математику и 1a в той же ячейке:

=G5&" "&G6

тогда вы можете использовать countif:

=COUNTIF($F:$K,N15)

вы можете использовать:

=SUMPRODUCT(--(F3:K12&F2:K11="1a"&"Math"))

вы должны использовать начальный массив (F3: K12) в первой ссылке и после & ваша ссылка меньше одной строки (добавьте одну строку вверх и меньше одной строки вниз) (F2:K11)

В таком случае (F3:K12&F2:K11="1a"&"Math") вернутся True Если строки в F3: K12 = "1a"и F2:K11="Math"

The -- перед условием вернет 1, Если True

Суммирование будет суммироваться (1,1,0...) Какой граф

=COUNTIFS(F3:K11, "Math", F4:K12, "1a")

, который уже был представлен fitch496, это хорошее начало.  (Примечание: некоторые локали используют ; для разделения параметров функции; другим использовать ,.)  Есть одна возможная проблема: он будет считать пары ячеек, где "математика" находится в четной строке, и "1a" под ним в нечетной строке.  (Хотя вопрос не говорит об этой проблеме, на рисунке показано, что такие пары не должны учитываться.)

чтобы решить эту проблему, я начнем с перевода приведенной выше формулы в тот, который дает тот же результат, но с помощью SUMPRODUCT:

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"))

здесь мы используем стандартные приемы, предшествующие логическому выражению -- чтобы превратить TRUE в 1, а FALSE в 0, и используя SUMPRODUCT неявное умножение как логическое и.  Затем я расширил это до

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"), --(MOD(ROW(F3:K11),2)=1))

чтобы проверить, что" математика " появилась в нечетной строке (MOD(ROW(…),2)=1).  Это не, потому что Excel вроде лечит ROW(F3:K11) так же, как и ROW(3:11), создание линейного массива из 9 значений а не прямоугольный массив из 54 значений.  (IMNSHO, это ошибка в Excel.)  Я обнаружил, что могу это исправить заставляя его учитывать столбцы, а затем игнорируя их:

=SUMPRODUCT(--(F3:K11="Math"), --(F4:K12="1a"), --(MOD(ROW(F3:K11)+0*COLUMN(F3:K11),2)=1))

которая вычисляет номер столбца каждой ячейки (COLUMN(…)) а затем умножает его на 0.  Эта работа.  Иллюстрация ниже такая же, как данные OP в вопросе, но с добавлением "Math" и " 1a " в ячейках J8 и J9.  fitch496 COUNTIFS формула (которую я включаю в верхней части моего ответа) а мой простой SUMPRODUCT формула оба считают эту смещенную "математику 1a" и дают в общей сложности 3; мой последний SUMPRODUCT формула игнорирует смещение " Math 1a" и дает в общей сложности 2.

        ^{(см. источник этого поста для копирования и вставки текста выше.)}

моя формула можно немного упростить:

• --(MOD(row number,2)=1) принимает номер строки по модулю 2 и проверяет, равен ли он 1.  Если это так, сравнительный тест дает TRUE, который преобразуется в 1 --.  Это не 1, это должно быть 0; сравнение с 1 дает FALSE, который преобразуется в 0 на --.  Мы можем обойтись без логических операций (тестирование на качество, а затем применение -- к логическому результату) и просто используйте MOD(row number,2).
• --(Boolean) просто -(-(Boolean)).  Самое сокровенное - преобразует TRUE в 1 и FALSE в 0, но тогда получается 1 в -1.  Тогда внешняя - преобразует -1 обратно в 1.  Так как мы берем произведение двух -- факторы, внешняя - s отменить друг друга, так что мы можем опустить их.

Итак, окончательная, упрощенная формула

=SUMPRODUCT(-(F3:K11="Math"), -(F4:K12="1a"), MOD(ROW(F3:K11)+0*COLUMN(F3:K11),2))