Сумма сумм на основе критериев уникальных комбинаций полей в таблице Excel
У меня есть таблица в Excel 365 под названием MyLongData, содержащая несколько строк. Я хотел бы придумать формулу для отслеживания претензий, где заявка определяется как уникальная комбинация полей [Accident Date], [Claimant Name], [Location].
Я хочу добавить поле [Amount] для претензий, где [Amount]>10000,
кроме того, я хочу ограничить его полем «where» [Year] = 2023.
Подводя итог, я хотел бы сложить [Amount] с учетом [Amount]>10000, сгруппированных по комбинациям [Accident Date], [Claimant Name], [Location] в таблице MyLongData.
Простой пример:
Я хочу вернуть 10050 (сумму записей 2 и 3, которые представляют собой одну заявку, превышающую порог в 10 000).
Мне удалось выяснить формулу СЧИСЛЕНИЙ, которая, я считаю, верна:
=IFERROR(ROWS(UNIQUE(FILTER(
MyLongData[Accident Date]&MyLongData[Location]&MyLongData[Claimant Name],
(MyLongData[Year]=2023)*(SUMIFS(MyLongData[Amount],
MyLongData[Accident Date],MyLongData[Accident Date],
MyLongData[Location],MyLongData[Location],
MyLongData[Claimant Name],
MyLongData[Claimant Name])>10000)))),0)
Но я не могу получить формулу для сложения сумм.
Я бы предпочел единую (хотя и сложную) формулу, но я также могу прибегнуть к подходу сводной таблицы, если это единственный способ.
Ответы 2
Вполне уверен, что это может быть короче, просто чтобы получить представление:
=LET(a,A1:D4,b,INDEX(a,,1),c,INDEX(a,,2),d,INDEX(a,,3),e,INDEX(a,,4),f,UNIQUE(FILTER(a,e=2023)),g,E1:E4,h,HSTACK(f,BYROW(f,LAMBDA(x,SUM(FILTER(g,(b=INDEX(x,,1))*(c=INDEX(x,,2))*(d=INDEX(x,,3))))))),FILTER(h,INDEX(h,,5)>10000))
Моя ошибка: я построил формулу, не используя ссылки на таблицы. В любом случае, у вас уже есть очень хороший ответ!
Вот один из способов достижения желаемого результата:
=LET(
_SumByYear, SUMIFS(Amount,Accident_Date,Accident_Date,
Claimant_Name,Claimant_Name,
Location,Location,Year,2023),
UNIQUE(FILTER(HSTACK(DROP(MyLongData,,-1),_SumByYear),
_SumByYear>10000,0)))
Или лучше использовать GROUPBY(), если применимо:
=LET(
_Group, GROUPBY(MyLongData[[Accident Date]:[Year]],Amount,SUM,,0,,Year=2023),
FILTER(_Group, TAKE(_Group,,-1)>10000,0))
NOTE: Firstly, using Structured References aka Tables, secondly to make easier to read have defined those references with their respective column heads. Please ensure to change the references or cell ranges etc as per your suit. GROUPBY() function works with MS365 Office Insiders Version Exclusively.
С SUMIFS так просто!
В предложенном решении есть небольшой недостаток. Функция UNIQUE выбрасывает две одинаковые записи и сохраняет только одну, но вместо этого их следует суммировать. Я считаю, что исправление состоит в том, чтобы добавить индекс строки в уникальный микс, например: =LET( _SumByYear, SUMIFS(Amount,Accident_Date,Accident_Date, Claimant_Name,Claimant_Name, Location,Location,Year,2023), UNIQUE(FILTER(HSTACK(DROP(MyLongData,,-1),_SumByYear,ROW(MyLongData)), _SumByYear>10000,0)))
Спасибо! Это сработало при тестировании, но мои реальные данные содержат столбцы в разных местах (между всеми столбцами, которые я указал, есть дополнительные столбцы), и мне было трудно выстроить числа ИНДЕКС. Кажется, я перепутал ссылки ИНДЕКС внутри функции LAMBDA, но не смог понять, как их исправить; Я не думаю, что они совпадают с индексом исходных данных. Я получал результаты, но при ручной проверке они не совпадали.