У меня есть следующий набор данных, который здесь обрезан. Он имеет около 3 тыс. строк, в которых один и тот же раздел повторяется снова и снова, но значения немного отличаются. Цвета повторяются и никогда не меняются, но значения B:G могут меняться для каждого цвета.
Я пытаюсь подсчитать количество строк, в которых появляется значение, например Оскар, для каждого цвета. В приведенном здесь примере набора данных Оскар получил бы значение 1 для красного цвета, поскольку в двух строках, где красный является цветом, он появляется только в одной из этих строк. Аналогично, он получит 2 за фиолетовый цвет, потому что он появляется в двух рядах фиолетового цвета.
Трудность, с которой я сталкиваюсь, заключается в том, что я продолжаю подсчитывать количество раз, когда название цвета появляется. Самое близкое, что у меня есть, это ниже, где K22:K30 — список цветов, а Q21:30 — названия.
=COUNT(IF(FILTER($A:$G,$K22=$A:$A)=Q$21,1,""))
Любая помощь приветствуется, спасибо.
Обновлено: похоже, что вы ищете следующие значения, если я не ошибаюсь: (функция FILTER()
избыточна и не нужна, я следовал формуле в OP, поэтому понял позже)
• Способ 1: использование MMULT()
=SUM(--(MMULT(($I3=$B$1:$G$18)*(J$2=$A$1:$A$18),SEQUENCE(COLUMNS($B$1:$G$1)))>0))
• Способ 2: использование BYROW()
с конструкцией LAMBDA()
.
=SUM(N(BYROW(($I3=$B$1:$G$18)*(J$2=$A$1:$A$18),LAMBDA(x,OR(x)))))
• Способ 3: использование BYROW()
с конструкцией ETA LAMBDA
.
=SUM(N(BYROW(($I3=$B$1:$G$18)*(J$2=$A$1:$A$18),OR)))
Вы также можете использовать MAKEARRAY()
для возврата одной формулы динамического массива:
=LET(
_Data, A1:G18,
_Colors, TAKE(_Data,,1),
_Names, DROP(_Data,,1),
_UniqC, TOROW(UNIQUE(_Colors)),
_UniqN, UNIQUE(TOCOL(_Names)),
_Counts, MAKEARRAY(ROWS(_UniqN),COLUMNS(_UniqC), LAMBDA(r,c,
SUM(--(MMULT((INDEX(_UniqN,r)=_Names)*(INDEX(_UniqC,c)=_Colors),SEQUENCE(COLUMNS(_Data)-1))>0)))),
HSTACK(VSTACK("",_UniqN), VSTACK(_UniqC, _Counts)))