☰

Ошибки формулы СУММПРОИЗВ

tag-icon • tag-icon microsoft-excel
Ошибки формулы СУММПРОИЗВ

Я хочу, чтобы ячейка автоматически отображала текст (T1, T2, T3, T4) в зависимости от того, найдены ли в таблице входные данные в другой ячейке.

=IF(SUMPRODUCT(--(C3=standards!I3:I16))>0,"T1")

будет выведен список только для одной категории, но я не могу заставить формулу принимать несколько массивов И определенные входные данные, когда значение находится в этой категории.

=IF(SUMPRODUCT(--(C3=standards!I3:I16))>0,"T1")*(--(C3=standards!J3:j16))>0,"T2")

и т. д. Что я делаю не так?

решение1

Используйте функцию AGGREGATE, она выполнит поиск по диапазону и вернет число на основе столбца, в котором было найдено значение функции CHOOSE():

=CHOOSE(AGGREGATE(15,6,(COLUMN($I$2:$L$16)-COLUMN($I$2)+1)/($I$2:$L$16=C3),1),"T1","T2","T3","T4")

решение2

введите описание изображения здесь

Вы можете использовать эту формулу:

=IF(SUMPRODUCT(--(B253=A254:A257))>0,"T1",IF(SUMPRODUCT(--(B253=B254:B257))>0,"T2",IF(SUMPRODUCT(--(B253=C254:C257))>0,"T3",IF(SUMPRODUCT(--(B253=D254:D257))>0,"T4"))))

Примечания:

  1. При необходимости скорректируйте диапазон данных и ссылку на ячейку критериев.
  2. Для любого значения ячейки критерия от 0 до 15 формула возвращает T1–T4 в зависимости от столбца, в котором находится значение.
  3. Если значение в ячейке критерия больше 15, AGGREGATE() формула вернет #Num!ошибку, но эта формула даст FALSE.

решение3

Эта SUMPRODUCT()формула возвращает номер столбца (вашей таблицы данных), в котором находится значение из ячейки C3:

=SUMPRODUCT(1*(C3=standards!I3:L16)*COLUMN(A:D))

и вы можете использовать это в CHOOSE()формуле, как в ответе Скотта, или INDEX()если ваши тексты длинные или их необходимо изменить.

=CHOOSE(SUMPRODUCT(1*(C3=standards!I3:L16)*COLUMN(A:D)),"T1","T2","T3","T4")

=INDEX(Text_Table,SUMPRODUCT(1*(C3=standards!I3:L16)*COLUMN(A:D)))

Обратите внимание, что SUMPRODUCT()формула даст неправильный ответ, если в вашей таблице данных есть несколько вхождений значения C3. Существуют способы обойти это, поэтому, пожалуйста, прокомментируйте, если может быть несколько значений.

В других ответах будет указано первое вхождение, но в них будет указан правильный текст для столбца, в котором оно находится.

Связанный контент