☰

행의 각 값에 대한 vlookup의 합계

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
행의 각 값에 대한 vlookup의 합계

이렇게 할 수 있는 방법이 있어야 하는데 아직 어려움을 겪고 있습니다...

==데이터==

시트1

        A           B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod           | $9.99 |
| Trout         | $8.99 |
| Haddock       | $6.99 |
| Kippers       | $3.99 |
+---------------+-------+

시트2

       A            B         C       D        E
+------------+-------------+-----+--------+---------+
|            | Total Spend | Cod | Trout  | Haddock |
+------------+-------------+-----+--------+---------+
| Customer 1 | ?           |   1 |        |       3 |
| Customer 2 | ?           |     |      2 |         |
| Customer 3 | ?           |   2 |      2 |       2 |
+------------+-------------+-----+--------+---------+

==도전==

각 고객에 대해 계산해야 합니다.총 지출Sheet2의 '구매'에 Sheet1의 '제품/가격'을 곱합니다. 예를 들어, 다음과 같이 계산할 수 있습니다.총 지출다음 라인을 따라 고객 1의 경우: =(C2*vlookup(c1,'Sheet1!A:B,2,0))+(D2*vlookup(D1,'Sheet1!A:B,2,0))+(E2*vlookup(E1,'Sheet1!A:B,2,0))등.

하지만Sheet1에는 항상 새로운 제품이 추가됩니다.

"kippers"가 F열에 추가되자마자 수동으로 루프백하여 모든 수식을 업데이트해야 합니다. (새 제품이 Sheet1에 추가될 때마다 사용되는 수식으로 인해 Sheet2에 자동으로 새 열로 나타납니다.) 개인적인 게으름은 제쳐두고, 업데이트가 필요한 시기를 파악하지 못하고 잘못된 합계로 인해 작업을 수행할 수 있다는 우려가 있습니다.

계산할 수 있는 방법이 있나요총 지출새 항목/열이 추가될 때마다 수식을 수동으로 업데이트하지 않고도 가능합니까?

이 문제에 대해 도움을 주실 수 있는 모든 분들께 미리 감사드립니다.

추신: 저는 Google Sheets에서 작업 중이라는 점을 덧붙이고 싶습니다.

답변1

SUMPRODUCT()계산을 수행하는 데 사용하십시오 .

여기에 이미지 설명을 입력하세요

공식 B8:

=SUMPRODUCT((A$2:A$5=C$7:E$7)*(B$2:B$5*C8:E8))

아래로 드래그하세요. 하지만 제품이 추가될 때 자동으로 업데이트되지는 않습니다.

명확히 하자면 제품의 순서는 중요하지 않습니다.

여기에 이미지 설명을 입력하세요

같은 결과입니다.

답변2

데이터 방향을 약간 수정하면 이 문제를 해결할 수 있습니다.

:경고:

표시된 방법은 Google 시트뿐만 아니라 Excel에서도 작동합니다.

여기에 이미지 설명을 입력하세요

  • 셀 sheet2 H19의 수식 및 작성:

     =IFERROR(SUMPRODUCT(VLOOKUP(Sheet1!G13,$G$19:$K$22,{3,4,5},FALSE))*Sheet1!H13,"")

주의

SUMPRODUCT에 단일 인수가 있는 경우 SUM과 매우 유사하게 동작합니다. 그러나 SUMPRODUCT에 인수가 여러 개 있는 경우 인수 곱의 합계를 반환합니다. 다음은 단일 인수로 작업하는 것입니다.

{3,4,5}는 데이터 범위 $G$19:$K$22의 열 I, J, K이며 조정 가능합니다.

필요에 따라 수식의 셀 참조를 조정합니다.

답변3

모든 항목을 수용할 수 있도록 VLookup 테이블에 대한 큰 영역을 선택합니다. 5000개의 행을 가져간다고 가정해 보세요. 이제 새 항목이 추가될 때마다 항목이 포함된 A 열과 B 열을 다시 정렬하세요. SORT하는 순간 올바르게 작동하므로 문제가 없습니다.

감사해요.

관련 정보