СУММПРОИЗВ с формулой ТЕКСТРАПЛИТ и ПОИСКПОЗ, возвращающей #ЗНАЧЕНИЕ! ошибка при суммировании нескольких пересечений в Excel
Я пытаюсь суммировать значения из таблицы, в которой ссылки на строки и столбцы динамически генерируются из объединенных строк в отдельных ячейках. В частности, мне нужно:
- Разделите и сопоставьте значения в одной ячейке (BO28), содержащей ссылки на объединенные строки (например, «Модель проверки преобразования данных 1, проверка плана переключения»), со строками в таблице.
- Разделите и сопоставьте значения в другой ячейке (BP28), которая содержит объединенные заголовки столбцов (например, «Джейн Смит, Эмили Форс»), с заголовками столбцов в таблице.
- Суммируйте пересекающиеся значения каждой комбинации совпадений строк и столбцов.
В моей формуле я хочу суммировать следующие пересечения:
«Макет проверки преобразования данных 1» с «Джейн Смит» «Макет проверки преобразования данных 1» с «Эмили Форс» «Проверка плана переключения» с «Джейн Смит» «Проверка плана переключения» с «Эмили Форс»
Итак, ожидаемый результат должен быть 80 + 40 + 80 + 40 = 240. Однако я получаю #VALUE! ошибка, когда я пытаюсь применить эту формулу:
=SUMPRODUCT(
ISNUMBER(MATCH(BN2:BN10, TEXTSPLIT(BN25, ", "), 0)) *
ISNUMBER(MATCH(BN1:BU1, TEXTSPLIT(BN26, ", "), 0)) *
BN2:BU10
)
Я использовал TEXTSPLIT, чтобы разбить объединенные строки в BO28 и BP28 на отдельные значения. Я применил ПОИСКПОЗ, чтобы найти соответствующие строки и столбцы, и использовал ИНДЕКС, чтобы вернуть пересекающиеся значения. Обернули все это в СУММПРОИЗВ для обработки логики массива.
Несмотря на этот подход, я продолжаю получать #VALUE! ошибка, и я не знаю, в чем проблема. (Сообщите мне, если нужно предоставить файл Excel). Спасибо.
Ответы 4
Вам необходимо сравнить как вертикальные, так и горизонтальные заголовки данных. Затем используйте SUMPRODUCT(), чтобы суммировать соответствующие значения, соответствующие заголовкам столбцов и заголовкам строк. Пытаться-
=SUMPRODUCT(MAP(BO2:BU10,LAMBDA(x,
ISNUMBER(SEARCH(INDEX(BN2:BN10,ROW(x)-ROW(BO1)),BN25))*
(ISNUMBER(SEARCH(INDEX(BO1:BU1,1,COLUMN(x)-COLUMN(BN2)),BN26)))))*
(BO2:BU10))
Вот немного другой подход:
=SUM(
CHOOSECOLS(
CHOOSEROWS(BO2:BQ5,XMATCH(TEXTSPLIT(BN10,", ");BN2:BN5)),
XMATCH(TEXTSPLIT(BN9,", "),BO1:BQ1))
)
Другой способ
=XMATCH(TRIM(TEXTSPLIT(A12,",")),$A$2:$A$6) чтобы найти названия курсов.=XMATCH(TRIM(TEXTSPLIT(A13,",")),$B$1:$E$1) чтобы найти имена пользователей.
Я добавил TRIM на тот случай, если после , есть пробел.
=INDEX($B$2:$E$6,<course names formula>,<user names formula>) вернуть пересечения.
Соедините все это вместе и оберните формулой СУММ:
=SUM(INDEX($B$2:$E$6,XMATCH(TRIM(TEXTSPLIT(A12,",")),$A$2:$A$6),XMATCH(TRIM(TEXTSPLIT(A13,",")),$B$1:$E$1)))
С INDEX и MATCH не нужно иметь дело с заголовками. TRIM удаляет ненужные пробелы, которые сбивают с толку функции.
=SUMPRODUCT(INDEX(A1:G8,MATCH(TRANSPOSE(TRIM(TEXTSPLIT(A12,","))),A1:A8,0),MATCH(VALUE(TRIM(TEXTSPLIT(A13,","))),A1:G1,0)))
Textsplit(A12,,", ") будет транспонирован и обрезан (разделен по строкам, а не по столбцам и по разделителям, включая символ пробела)
Можете ли вы редактировать в образце данных, используя Table Markdown, чтобы можно было легко скопировать данные и фактически использовать их.