=LET(
rng,B3:C12,
num,LAMBDA(x,
LET(
a,IFERROR(REGEXEXTRACT(x,"[-+]?\d+(?:\.\d+)?",1),0),
SUM(--a)
)
),
s,BYROW(TAKE(rng,,-1),LAMBDA(r,num(r))),
VSTACK(
{"종류","값","합계"},
SORTBY(HSTACK(rng,s),s,-1)
)
)

=LET(
rng,B3:C12,
db,DROP(REDUCE({"","",""},
SEQUENCE(ROWS(rng)),
LAMBDA(a,i,
LET(
tp,INDEX(rng,i,1),
x,INDEX(rng,i,2),
m,TOCOL(REGEXEXTRACT(x,"[가-힣A-Za-z]+ *\([-+]?\d+(?:\.\d+)?\)",1),1),
ct,TRIM(REGEXEXTRACT(m,"^[^(]+")),
no,--REGEXEXTRACT(m,"[-+]?\d+(?:\.\d+)?"),
VSTACK(a,HSTACK(ct,tp&"("&no&")",no))
)
)
),1),
city,CHOOSECOLS(db,1),
item,CHOOSECOLS(db,2),
amt,CHOOSECOLS(db,3),
u,UNIQUE(city),
s,MAP(u,LAMBDA(x,SUM(FILTER(amt,city=x)))),
txt,MAP(u,LAMBDA(x,TEXTJOIN("+",,FILTER(item,city=x)))),
VSTACK(
{"지역","내역","합계"},
SORTBY(HSTACK(u,txt,s),s,-1)
)
)

​

파워쿼리로 만들어 봅니다.