C2 =LET(D, A2:A13, N, B2:B13,
fX, LAMBDA(A, LET(X, TEXTSPLIT(A,,"+"), PN, REGEXEXTRACT(X,"[\d]+")*1,
    PR, SUMPRODUCT(VLOOKUP(REGEXEXTRACT(X,"[^\d]+"),H:I,2,0),PN),PR)),
BYROW(D, fX)*N)

J2=LET(D, A2:A13, N, B2:B13, P, H2:H5, S, SEQUENCE(ROWS(D)),
fM, LAMBDA(x, REGEXEXTRACT(x,"[^\d\+]+",1)),
fN, LAMBDA(x,y, REGEXEXTRACT(x,"[\d]+",1)*y),
fX, LAMBDA(x,y, WRAPROWS(TOROW(VSTACK(fM(x),fN(x,y)),,1),2)),
DN, DROP(REDUCE("",S,LAMBDA(t,r, VSTACK(t,fX(INDEX(D,r),INDEX(N,r))))),1),
BYROW(P, LAMBDA(r,SUMPRODUCT((INDEX(DN,,1)=r)*INDEX(DN,,2)))))

​

힌트 : 쿼리병합, 그룹화-합계

아래 이미지로 대체합니다.

C2=SUMPRODUCT($I$2:$I$5,IFERROR(VALUE(SUBSTITUTE(MID(A2,FIND($H$2:$H$5,A2)+LEN($H$2:$H$5),2),"+","")),0))*B2

J2=SUMPRODUCT($B$2:$B$13,VALUE(IFERROR(SUBSTITUTE(MID($A$2:$A$13,FIND(H2,$A$2:$A$13)+LEN(H2),2),"+",""),0)))

C2=LET(F, LAMBDA(x, LET(Data,WRAPROWS(REGEXEXTRACT(x,"[^\d\+]+|\d+",1),2),
SUM(VLOOKUP(INDEX(Data,,1),$H$2:$I$5,2,0) * INDEX(Data,,2)))),
MAP($A$2:$A$13, F)*$B$2:$B$13)

​

J2=LET(Data,DROP(REDUCE("",A2:A13,
LAMBDA(a,b,VSTACK(a,REGEXEXTRACT(b,"[^\d\+]+|\d+",1)))),1),
wd, WRAPROWS(TOCOL(IFERROR(Data*$B$2:$B$13,Data),3),2),
VLOOKUP(H2:H5,GROUPBY(INDEX(wd,,1), INDEX(wd,,2), SUM,0,0),2,0))

​

저는 이렇게 해봤네요^^