Есть ли способ подсчитать, когда переменная появляется с (и без) другой в Excel?

#excel #excel-formula

Вопрос:

Я пытаюсь подсчитать в Excel, сколько раз «A» появляется с (а затем без) «C» по горизонтали, когда они не находятся в одном и том же месте каждый раз. Кто-нибудь знает формулу Excel, которая будет работать? Вот пример моих данных.

1	2	3	4	5
A	B	C	D	E
A	B	C	D	F
E	G	F	H	L
E	B	C	H	F
A	Я	J	K	H

Спасибо!

1. Каковы ожидаемые результаты?

2. Просто посмотрите, сколько раз A появляется с C, когда они находятся на одной горизонтальной линии. В этом примере мы должны видеть 2 как правильный ответ, даже если A и C здесь по 3 раза каждый.

3. Хорошо, я записал ответ. Тем не менее, вы нашли рабочий ответ от ScottCraner. Убедитесь, что приняли ее как таковую, щелкнув галочку слева от нее.

Ответ №1:

Чтобы получить количество строк, в которых есть оба A и C , предполагая, что каждая может быть в строке только один раз каждая:

 =SUM(--(MMULT((A1:E5="A") (A1:E5="C"),TRANSPOSE(COLUMN(A1:E5)^0))>1))

Это может потребоваться подтвердить с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования в более старых версиях.

Чтобы получить общее количество без, мы добавляем COUNTIF(A1:E5,"A") и сокращаем приведенную выше формулу:

 =COUNTIF(A1:E5,"A")-SUM(--(MMULT((A1:E5="A") (A1:E5="C"),TRANSPOSE(COLUMN(A1:E5)^0))>1))

То же самое предостережение о Ctrl-Shift-Enter.

Если значения могут присутствовать несколько раз в строке, нам нужно сделать:

 =SUM(--((MMULT(--(A1:E5="A"),TRANSPOSE(COLUMN(A1:E5)^0))>0) (MMULT(--(A1:E5="C"),TRANSPOSE(COLUMN(A1:E5)^0))>0)>1))

Опять же, более старые версии должны использовать Ctrl-Shift-Enter.

Ответ №2:

По общему признанию, скорее всего, у вас еще не будет доступа к следующим функциям. Но это может быть хорошим вариантом использования для BYROW() :

Формула I1 для подсчета строк с ‘A’ и ‘C’:

 =SUM(BYROW(A1:E5,LAMBDA(x,--(COUNTIF(x,"A")*COUNTIF(x,"C")))))

Или:

 =SUM(--BYROW(A1:E5,LAMBDA(x,SUM(--(UNIQUE(x)={"A","C"}))=2)))

Формула I2 для подсчета строк с ‘A’, но без ‘C’:

 =SUM(BYROW(A1:E5,LAMBDA(x,COUNTIF(x,"A")*(COUNTIF(x,"C")=0))))