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

Question

Я обычный пользователь Excel, и с этой проблемой я сталкивался раньше и в конце концов нашел другой способ сделать это, но в этом случае я думаю, что это мой единственный вариант.

Вот пример изображения моего листа:

Я хочу посчитать, сколько раз ячейка в диапазоне содержит "1a" И ячейка над ячейкой с 1a содержит "Math". Очевидно, этот пример тривиален, но я решил, что логика решения этой проблемы будет такой же, как если бы это был более крупный лист с моими данными, полностью его заполняющими.

В этом примере ответ должен быть дважды. Как мне построить формулу для условного подсчета этого типа?

Answer 1

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

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

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

Answer 2

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

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

Поместите формулу без фигурных скобок, а затем в строке формул нажмите CTRL+SHIFT+ENTER, чтобы создать формулу массива. Это дает счет «1a» со словом «Math» над этим. Начните в К4, скажем, и перетащите его вниз. Для альтернативных строк он вернет 0, но это не должно иметь значения. Теперь просто СУММА столбца помощников. Для этого вы можете использовать простую функцию SUM. Там могут быть более разумные способы достичь этого без столбца Helper или можно даже использовать макросы VBA.

Answer 3

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

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

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

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

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

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

чтобы проверить, что «Math» появилось в нечетном ряду (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.  Это работает.  Иллюстрация ниже аналогична данным ОП в вопросе, но с добавлением «Математика» и «1а» в ячейках J8 и J9 .  Формула COUNTIFS fitch496 (которую я включаю в начало моего ответа) и моя простая формула SUMPRODUCT оба считают этот смещенный «Математический 1a» и дают всего 3; моя последняя формула SUMPRODUCT игнорирует неверно выровненную «Математическую 1a» и дает в итоге 2.

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

Моя формула может быть немного упрощена:

• --(MOD(row number,2)=1) берет номер строки по модулю 2 и проверяет, равен ли он 1.  Если это так, тест сравнения дает значение ИСТИНА, которое преобразуется в 1 с помощью -- .  Это не 1, это должно быть 0; сравнение с 1 дает ЛОЖЬ, которая преобразуется в 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))

Answer 4

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

=G5&" "&G6

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

=COUNTIF($F$15:$K$18,N15)

Answer 5

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

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

Вы должны использовать начальный массив (F3:K12) в первой ссылке, а после & Ваша ссылка меньше одной строки (добавить одну строку вверх и меньше одной строки вниз) (F2:K11)
В этом случае (F3:K12&F2:K11="1a"&"Math") вернет True если строки в F3:K12 = "1a" и F2:K11 = "Math"
-- перед условием вернет 1, если оно True
Суммарный продукт составит (1,1,0 ...) Какой счет

Answer 6

Если вы хотите получить итоговое значение, то, сколько раз обе Math & 1a записываются по одной формуле, это должно быть,

= COUNTIF (B86; E91, "Math") + Countif (B86:E91, "1a")

Если хотите посчитать отдельно, то один и тот же счет, формула дважды.