У меня есть два набора данных в Excel, набор 1 — это необработанные данные, а набор 2 — таблица моста. Желаемый результат также добавляется. Как я должен подготовиться к этой формуле.
набор 1:
набор 2:
ожидаемый результат:
Вы можете попробовать СУММЕСЛИМН с подстановочным знаком для каждой строки. Например, для первого столбца поместите следующую формулу и перетащите ее вниз.
=SUMIFS($B2:$F2,$B$1:$F$1,"=A*")
Затем сделайте то же самое для других столбцов, например. для столбца Б:
@ Гарри, в следующий раз, пожалуйста, уточните это в своем вопросе заранее, чтобы мы могли сделать предположение, основанное на вашей реальной проблеме.
Здесь решение, которое предполагает переменное количество заголовков и отсутствие определенного шаблона в именах столбцов. Предполагается отсутствие ограничений версии Excel в соответствии с тегами, перечисленными в вопросе. В ячейку H1
поместите следующую формулу, которая выводит весь результат сразу:
=LET(in, A1:F5, lk, A8:B12, header, DROP(TAKE(in,1),,1), A, TAKE(lk,,1),
B, DROP(lk,,1), data, DROP(in,1,1), REDUCE(TAKE(in,,1), UNIQUE(B),
LAMBDA(ac,bb, LET(f, FILTER(A, B=bb),values, CHOOSECOLS(data,XMATCH(f, header)),
sum, MMULT(values, SEQUENCE(ROWS(f),,1,0)), HSTACK(ac, VSTACK(bb, sum))))))
Вот это вывод:
Мы используем функцию LET
только с двумя входными диапазонами: in
, lk
, поэтому остальные определенные имена зависят от таких имен диапазонов. Это упрощает обслуживание формулы и ее адаптацию к вашему реальному сценарию.
С помощью DROP
и TAKE
извлекаем каждую часть входных диапазонов: header
, data
, A
, B
(столбцы из второй таблицы). Мы используем шаблон REDUCE/HSTACK
для объединения столбца результата на каждой итерации. Проверьте мой ответ на вопрос: как преобразовать таблицу в Excel из вертикальной в горизонтальную, но с разной длиной для получения дополнительной информации.
Мы перебираем уникальные значения B
и для каждого значения (bb
) выбираем значения столбца A
(f
). Мы используем XMATCH
для выбора соответствующих столбцов индекса из header
(он не включает столбец даты). Мы используем CHOOSECOOLS
для выбора соответствующих столбцов из data
(values
). Теперь нам нужно суммировать по столбцам, и мы используем для этого MMULT
. Результат в sum
имени. Наконец, мы используем HSTACK
для объединения выбранных столбцов по одному на каждой итерации, включая в качестве заголовка уникальные значения из B
.
Примечание. Вместо функции MMULT
вы можете использовать следующую функцию массива, это вопрос личных предпочтений:
BYROW(values, LAMBDA(x, sum(x)))
Большое спасибо за эту помощь. Это выходит за рамки моих знаний, но это работает. В моем тесте кажется, что если в таблице моста есть нулевые значения или неиспользуемые строки, будут возвращены ошибки. Это правильно? Еще одна вещь заключается в том, что эта работа заключается в автоматизации процесса от набора 1 до вывода. Если последовательность столбцов в выводе была зафиксирована как «Дата, A, C, B», как я могу обновить эту формулу.
@ Гарри, я рад, что это работает. Чтобы предотвратить появление пустых строк в таблице мостов, вы можете обернуть ввод TOCOL
следующим образом: =LET(in, A1:F5, lk, A8:B12, header, DROP(TAKE(in,1),,1), A, TOCOL(TAKE(lk,,1),1),B, TOCOL(DROP(lk,,1),1), data, DROP(in,1,1), REDUCE(TAKE(in,,1), UNIQUE(B), LAMBDA(ac,bb,LET(f, FILTER(A, B=bb), values, CHOOSECOLS(data,XMATCH(f, header)),sum, MMULT(values, SEQUENCE(ROWS(f),,1,0)), HSTACK(ac, VSTACK(bb, sum))))))
, попробуйте и дайте мне знать. О втором вопросе, пожалуйста, подробнее.
@Harry о вашем втором вопросе, порядок столбцов в выводе зависит от того, как они представлены в таблице мостов. Вы можете обернуть UNIQUE(B)
с SORT
или SORTBY
, например, чтобы указать определенный порядок или просто отсортировать входные данные таблицы мостов. Без более подробной информации трудно представить ваш реальный сценарий, но я думаю, что использование SORT(UNIQUE(B))
гарантирует, A, B, C
порядок, например.
спасибо за Ваш ответ. Заголовки здесь просто примеры. Фактические заголовки намного сложнее. И колонок много. Этот метод не может решить мою проблему.