Суммирует строки на основе заголовка

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

В таблице A1:D21 я хотел создать под ней сводку (A23:D28), показывающую общую сумму входящих и исходящих сообщений каждого руководителя группы, а также общий итог.

Обычно я добавляю вспомогательный столбец, добавляя имена руководителей групп рядом с этими агентами, чтобы просто выполнять суммирование. Можно ли это сделать без помощи вспомогательной колонки?

Обновление добавлено еще один образец, близкий к тому, над чем я сейчас работаю

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

Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Руководитель группы А Руководитель группы А Руководитель группы А Агент 1 100 50 50% Агент 1 100 50 50% Агент 1 100 50 50% Агент 2 100 60 60% Агент 2 100 60 60% Агент 2 100 60 60% Агент 3 100 70 70% Агент 3 100 70 70% Агент 3 100 70 70% Агент 4 100 80 80% Агент 4 100 80 80% Агент 4 100 80 80% Агент 5 100 90 90% Агент 5 100 90 90% Агент 5 100 90 90% Итоги 500 350 70% Итоги 500 350 70% Итоги 500 350 70% Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Руководитель группы Б Руководитель группы Б Руководитель группы Б Агент 6 100 50 50% Агент 6 100 50 50% Агент 6 100 50 50% Агент 7 100 60 60% Агент 7 100 60 60% Агент 7 100 60 60% Агент 8 100 70 70% Агент 8 100 70 70% Агент 8 100 70 70% Агент 9 100 80 80% Агент 9 100 80 80% Агент 9 100 80 80% Агент 10 100 90 90% Агент 10 100 90 90% Агент 10 100 90 90% Итоги 500 350 70% Итоги 500 350 70% Итоги 500 350 70% Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Руководитель группы С Руководитель группы С Руководитель группы С Агент 11 100 50 50% Агент 11 100 50 50% Агент 11 100 50 50% Агент 12 100 60 60% Агент 12 100 60 60% Агент 12 100 60 60% Агент 13 100 70 70% Агент 13 100 70 70% Агент 13 100 70 70% Агент 14 100 80 80% Агент 14 100 80 80% Агент 14 100 80 80% Агент 15 100 90 90% Агент 15 100 90 90% Агент 15 100 90 90% Итоги 500 350 70% Итоги 500 350 70% Итоги 500 350 70% Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Имя Входящие вызовы Принятые звонки Принимать % Руководитель группы Д. Руководитель группы Д. Руководитель группы Д. Агент 16 100 50 50% Агент 16 100 50 50% Агент 16 100 50 50% Агент 17 100 60 60% Агент 17 100 60 60% Агент 17 100 60 60% Агент 18 100 70 70% Агент 18 100 70 70% Агент 18 100 70 70% Агент 19 100 80 80% Агент 19 100 80 80% Агент 19 100 80 80% Агент 20 100 90 90% Агент 20 100 90 90% Агент 20 100 90 90% Итоги 500 350 70% Итоги 500 350 70% Итоги 500 350 70% Входящие вызовы Принятые звонки Принимать % Входящие вызовы Принятые звонки Принимать % Входящие вызовы Принятые звонки Принимать % Руководитель группы А 500 350 70% Руководитель группы А 500 350 70% Руководитель группы А 500 350 70% Руководитель группы Б 500 350 70% Руководитель группы Б 500 350 70% Руководитель группы Б 500 350 70% Руководитель группы С 500 350 70% Руководитель группы С 500 350 70% Руководитель группы С 500 350 70% Руководитель группы Д. 500 350 70% Руководитель группы Д. 500 350 70% Руководитель группы Д. 500 350 70% Итоги 2000 г. 1400 70% Итоги 2000 г. 1400 70% Итоги 2000 г. 1400 70%

excel excel-formula sumifs

01.05.2024 20:16

Преобразование HTML-таблицы в профессиональный документ Excel

Это самый простой способ создания Excel из HTML-таблицы.

Импорт excel в laravel в базу данных

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

117

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вот один из способов сделать это:

=LET(
     _Filler, SCAN(,IF(D1:D21 = "Total",A1:A21,""),LAMBDA(x,y,IF(y = "",x,y))),
     _Output, FILTER(HSTACK(_Filler,B1:D21),TEXTAFTER(_Filler&"|"&A1:A21,"|") = "Totals"),
     VSTACK({"","Inbound","Outbound","Total"},_Output, HSTACK("",MMULT({1,1,1},DROP(_Output,,1)))))

Другой метод без использования вспомогательных функций LAMBDA(), следующее решение может работать, а может и не работать с фактическими данными, но вот что я предположил в соответствии с данными в ОП: если все агенты одинаковы для каждого руководителя группы, тогда мы можем стереть их, используя UNIQUE(), чтобы захватить только отдельные, да, следует еще раз отметить, что я намеренно делаю это на основе заданных данных, чтобы агенты не были разными, а затем с помощью манипуляций с функциями DROP(), TAKE() и FILTER() мы можем добиться желаемого результата. Все это зависит, опять же, это полностью основано на приведенных примерных данных и шаблонах.:

=LET(
     _Data, A1:D21,
     _Teams, UNIQUE(TAKE(_Data,,1),,1),
     _Vals, DROP(FILTER(_Data, TAKE(_Data,,1) = "Totals"),,1),
     _Combined, HSTACK(VSTACK(_Teams,""), VSTACK(_Vals, MMULT({1,1,1}, _Vals))),
     VSTACK(HSTACK("",DROP(TAKE(_Data,1),,1)), _Combined))

Обновлено: обновленное решение, основанное на редактировании с новыми образцами данных, опубликованных OP в 2024-05-01 21:14:40Z.

=LET(
     _Data, A1:D32,
     _TeamLeaders, TAKE(FILTER(_Data, INDEX(_Data,,2) = ""),,1),
     _Vals, DROP(FILTER(_Data, TAKE(_Data,,1) = "Totals"),,1),
     _Totals, MMULT({1,1,1,1}, TAKE(_Vals,,2)),
     _Bottom, HSTACK("Totals",_Totals, TAKE(_Totals,,-1)/TAKE(_Totals,,1)),
     _Middle, HSTACK(_TeamLeaders,_Vals),
     _Top, HSTACK("",DROP(TAKE(_Data,1),,1)),
     VSTACK(_Top, _Middle, _Bottom))

Предложение: поместите следующую формулу в диспетчер имен и определите ее как SUMMARY

=LAMBDA(array,
   LET(
       _Data, array,
       _TeamLeaders, TAKE(FILTER(_Data, INDEX(_Data,,2) = ""),,1),
       _Vals, DROP(FILTER(_Data, TAKE(_Data,,1) = "Totals"),,1),
       _Totals, MMULT({1,1,1,1}, TAKE(_Vals,,2)),
       _Bottom, HSTACK("Totals",_Totals, TAKE(_Totals,,-1)/TAKE(_Totals,,1)),
       _Middle, HSTACK(_TeamLeaders,_Vals),
       _Top, HSTACK("",DROP(TAKE(_Data,1),,1)),
       VSTACK(_Top, _Middle, _Bottom)))

А теперь используйте следующую формулу под каждой таблицей:

=SUMMARY(A1:D32)

=SUMMARY(F1:I32)

=SUMMARY(K1:N32)

Это работает отлично! Поскольку я действительно не знаком с Lambda, и это новые функции для меня. Моя следующая проблема заключается в том, как сделать это гибким, поскольку я собираюсь использовать его для нескольких столбцов. Но это здорово, спасибо!

— 01.05.2024 20:50

Я попробовал переместить вашу формулу под таблицу и просто продублировать все столбцы, включая созданную вами формулу, и все работает нормально. Я могу просто сделать это, если мне нужно добавить больше столбцов для продвижения вперед, и формула будет извлекать любые данные над ней.

— 01.05.2024 20:52

@Харви Я не думаю, что возникнут какие-либо проблемы, если у вас несколько столбцов, потому что, если я предполагаю, что вы добавляете столбцы для чисел дополнений, то это должно быть перед последним столбцом «Итоги», и если это какие-то другие текстовые столбцы, связанные с руководителем группы, тогда вы должен обновить свой пост. в остальном я не вижу никаких проблем с формулой.

— 01.05.2024 21:06

@Харви, ок, не мог бы ты дать мне знать, что ты ищешь? также в ваших сообщениях ни разу не упоминалось, что у вас будет несколько столбцов.

— 01.05.2024 21:36

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

— 01.05.2024 22:33

конечно, подожди минутку, и я поделюсь с тобой файлом Excel в качестве образца.

— 01.05.2024 22:48

@Харви, видишь, я опубликовал еще один метод и сделал несколько примечаний, чтобы он помог тебе понять.

— 01.05.2024 22:48

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

— 01.05.2024 23:15

@Харви, это последний вариант, надеюсь, изменений больше не будет?

— 01.05.2024 23:17

@Харви, я обновил свой ответ на основе твоих новых образцов данных, он также LAMBDA() не основан и не прост для понимания, надеюсь, ты сможешь выполнить эту тренировку.

— 02.05.2024 00:07

ВАУ, это выглядит потрясающе! Завтра я попробую это снова! Спокойной ночи, сэр, еще раз спасибо!

— 02.05.2024 01:50

@Харви, ты еще не принял мои ответы на свои старые сообщения. Этот один

— 02.05.2024 01:56

ух ты, это было так давно! Должно быть, я забыл это. Я принял ваш ответ. Спасибо!

— 03.05.2024 00:15

01.05.2024 20:36

Рассматривали ли вы использование таблиц вместо просто диапазонов?

Вот один из способов использования таблиц и сводных таблиц. Использование таблиц облегчит использование Power Query при необходимости в дальнейшем.

Агент

Имя Лидер группы Агент 1 Руководитель группы А Агент 2 Руководитель группы А

Руководитель группы (необязательно – может помочь с вводом данных)

Имя Руководитель группы А Руководитель группы Б

Звонки

Имя Входящие вызовы Принятые звонки Принимать % Лидер группы Агент 1 79 18 =[@[Accepted Calls]]/[@[Entered Calls]]=XLOOKUP([@Name],Agent[Name],Agent[Team Leader]) Агент 2 53 18 34% Руководитель группы А

Сводные таблицы

заголовки строк Сумма принятых вызовов Сумма входящих звонков Сумма общего количества вызовов Руководитель группы А 189 334 523 Руководитель группы Б 202 367 569 Руководитель группы С 169 360 529 Руководитель группы Д. 161 457 618 Общий итог 721 1518 2239

заголовки строк Сумма входящих звонков Сумма принятых вызовов Руководитель группы А Агент 1 73 47 Агент 2 51 35 Агент 3 63 20 Агент 4 51 7 Агент 5 96 80 Руководитель группы А Тотал 334 189

05.05.2024 16:06

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

Суммируйте все, игнорируя первый экземпляр значения

Почему `UNIQUE(TEXTSPLIT(TEXTJOIN(...)))` не возвращает список уникальных значений?

Как применить формулу только к определенным функциям

Excel: автоматическое преобразование ячейки на основе ввода в другую

Excel – суммирование стоимости нескольких идентификаторов материалов из одной ячейки

Как создать функцию VSTACK, пропускающую определенные строки (повторяющиеся строки)

Рассчитать средний рейтинг портфеля акций

Формула массива возвращает данные из ВТОРОЙ соответствующей строки, не может получить первую строку

Ищете альтернативу COUNTIFS(), которая будет работать с динамическим диапазоном (например: вывод массива функции FILTER())

Как выделить уникальное значение из нескольких ячеек

Суммирует строки на основе заголовка

Ответы 2

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

Похожие вопросы