☰

드롭다운 목록과 함께 sumifs 함수를 사용하여 한 시트에서 다른 시트로 데이터를 채우고 싶습니다.

tag-icon • tag-icon microsoft-excel dropdownlist
드롭다운 목록과 함께 sumifs 함수를 사용하여 한 시트에서 다른 시트로 데이터를 채우고 싶습니다.

드롭다운 목록을 사용하여 이미지 2에 표시하려는 데이터가 이미지 1에 있습니다. 드롭다운 목록에서 선택한 상점에 ​​따라 결과가 변경됩니다. sumif를 시도했지만 매장 2가 아닌 매장 1에 대한 데이터만 표시됩니다. 참조 및 수식 수정을 위해 이미지를 확인하세요.

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

데이터가 포함된 이미지 1

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

수식이 포함된 이미지 2

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

이미지 3 shop2 데이터가 표시되지 않습니다

답변1

이를 수행하는 한 가지 방법은 다음과 같습니다.

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

• 셀에 사용되는 수식I4

=VLOOKUP($H4,INDIRECT($I$1),MATCH(I$3,HEADERS,0),0)
  • 드롭다운과 관련하여 수식이 적절하게 작동하도록 일부 범위를 정의합니다.
  • =$B$4:$F$7다음과 같이 정의된 범위SHOP1
  • =$B$11:$F$14다음과 같이 정의된 범위SHOP2
  • 이후 헤더는 각각 동일하게 유지되고 다음 SHOPS중 하나에 대해 정의됩니다 .SHOPSHEADERS=$B$3:$F$3
  • 다음으로, VLOOKUP()위에 표시된 수식을 사용하여 채우고 오른쪽으로 채우세요!

그러나 사용하려는 경우 SUMIFS()사용을 제안 SUMPRODUCT()하거나 SUM()귀하의 기준에 따라Excel Version

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

• 셀에 사용되는 수식I4

=SUMPRODUCT(INDIRECT($I$1)*(Criteria_Range=$H4)*(I$3=HEADERS))
  • 정의된 이름을 일부 변경합니다. 다음과 같이 :

  1. =$C$4:$F$7다음과 같이 정의된 범위SHOP1

  2. =$C$11:$F$14다음과 같이 정의된 범위SHOP2

  3. =$C$3:$F$3다음과 같이 정의된 범위HEADERS

  4. =$B$4:$B$7다음과 같이 정의된 범위Criteria_Range

  • 이제 위의 수식을 이용하여 아래를 채우고 올바르게 채우세요!

아마도 SUMIFS()정의된 명명된 범위를 사용하는 것은 다음을 사용하는 첫 번째 방법과 동일하게 유지됩니다.VLOOKUP()

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

=SUMIFS(INDEX(INDIRECT($I$1),,MATCH(I$3,HEADERS,0)),INDEX(INDIRECT($I$1),,1),$H4)

• 액세스 권한이 있는 경우 MS365다음을 사용하면 전체 배열에 적용되는 단일 수식입니다.

=LET(
     _Data, VSTACK(EXPAND(SHOP1,,6,"SHOP1"),EXPAND(SHOP2,,6,"SHOP2")),
     _Filtered, FILTER(_Data, TAKE(_Data,,-1)=I1),
     MAKEARRAY(ROWS(H22:H24), COLUMNS(I21:L21), LAMBDA(r,c,
     VLOOKUP(INDEX(H22:H24,r),_Filtered,XMATCH(INDEX(I21:L21,c),HEADERS),0))))

경고:함수를 사용하는 INDIRECT()것은 암시적이지 않습니다. Volatile본질적으로 함수를 사용하면 작업 기능이 느려지고 Excel열려 있는 통합 문서에 변경 사항이 있을 때마다 셀을 계속 다시 계산하게 됩니다. 휘발성 함수를 사용하지 않고 작업을 쉽게 하려면 데이터 구조를 일부 변경해야 합니다.

이는 Windows 및 MAC 이후 버전 POWER QUERY에서 사용할 수 있는 기능을 사용하여 달성할 수도 있습니다 .Excel 2010+

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

  • 먼저 소스 범위를 테이블로 변환하고 그에 따라 이름을 지정합니다. 이 예에서는 for SHOP1, SHOP_1for SHOP2as SHOP_2및 for Channelas 로 이름을 지정했습니다.Channeltbl
  • Data다음으로 Tab --> Get & Transform Data--> Get Data--> From Other Sources--> 에서 빈 쿼리를 엽니다.Blank Query
  • 위의 내용을 Power Query사용하면 이제 Home탭 --> --> 보이는 내용을 제거하여 Advanced Editor다음을 붙여넣은 후 창을 열 수 있습니다.M-CodeDone

• 출력의 경우:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "SHOP")),
    #"Expanded Content" = Table.ExpandTableColumn(#"Filtered Rows", "Content", {"Expense Type", "January", "February", "March", "April"}, {"Expense Type", "January", "February", "March", "April"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Content", each ([Name] = CHANNEL)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Name"}),
    #"Filtered Rows2" = Table.SelectRows(#"Removed Columns", each [Expense Type] <> "Total Budget")
in
    #"Filtered Rows2"

• 채널의 경우:

let
    Source = Excel.CurrentWorkbook(){[Name="Channeltbl"]}[Content],
    CHANNEL = Source{0}[CHANNEL]
in
    CHANNEL
  • 위의 내용을 두 개의 서로 다른 빈 쿼리에 붙여넣어야 한다는 점을 기억하세요. 또한 CHANNEL쿼리는 연결이고 다른 쿼리는 시트에 입력해야 합니다.
  • 마지막으로 다시 가져오려면 Excel--> 클릭 Close & Load또는 Close & Load To--> 클릭한 첫 번째 항목은 New Sheet필요한 출력을 생성하고 후자는 결과를 어디에 배치할지 묻는 창을 표시합니다.

관련 정보