Как суммировать столбцы, используя заголовки столбцов и сводные таблицы в excel

У меня есть два набора данных в Excel, набор 1 — это необработанные данные, а набор 2 — таблица моста. Желаемый результат также добавляется. Как я должен подготовиться к этой формуле.

набор 1:

набор 2:

ожидаемый результат:

Преобразование HTML-таблицы в профессиональный документ Excel
Преобразование HTML-таблицы в профессиональный документ Excel
Это самый простой способ создания Excel из HTML-таблицы.
Импорт excel в laravel в базу данных
Импорт excel в laravel в базу данных
Здравствуйте, дорогой читатель, в этой статье я расскажу практическим и быстрым способом, как импортировать файл Excel в вашу базу данных с помощью...
0
0
79
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Вы можете попробовать СУММЕСЛИМН с подстановочным знаком для каждой строки. Например, для первого столбца поместите следующую формулу и перетащите ее вниз.

=SUMIFS($B2:$F2,$B$1:$F$1,"=A*")

Затем сделайте то же самое для других столбцов, например. для столбца Б:

спасибо за Ваш ответ. Заголовки здесь просто примеры. Фактические заголовки намного сложнее. И колонок много. Этот метод не может решить мою проблему.

Harry 11.02.2023 01:08

@ Гарри, в следующий раз, пожалуйста, уточните это в своем вопросе заранее, чтобы мы могли сделать предположение, основанное на вашей реальной проблеме.

David Leal 11.02.2023 01:52
Ответ принят как подходящий

Здесь решение, которое предполагает переменное количество заголовков и отсутствие определенного шаблона в именах столбцов. Предполагается отсутствие ограничений версии 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», как я могу обновить эту формулу.

Harry 11.02.2023 02:36

@ Гарри, я рад, что это работает. Чтобы предотвратить появление пустых строк в таблице мостов, вы можете обернуть ввод 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)))))), попробуйте и дайте мне знать. О втором вопросе, пожалуйста, подробнее.

David Leal 11.02.2023 02:54

@Harry о вашем втором вопросе, порядок столбцов в выводе зависит от того, как они представлены в таблице мостов. Вы можете обернуть UNIQUE(B) с SORT или SORTBY, например, чтобы указать определенный порядок или просто отсортировать входные данные таблицы мостов. Без более подробной информации трудно представить ваш реальный сценарий, но я думаю, что использование SORT(UNIQUE(B)) гарантирует, A, B, C порядок, например.

David Leal 11.02.2023 03:14

Другие вопросы по теме