☰

ドロップダウンリストでsumifs関数を使用して、あるシートから別のシートにデータを移入したい

tag-icon • tag-icon microsoft-excel dropdownlist
ドロップダウンリストでsumifs関数を使用して、あるシートから別のシートにデータを移入したい

画像 1 にはデータがあり、ドロップダウン リストを使用して、画像 2 に表示したいのですが、ドロップダウン リストから選択されたショップに応じて結果が変わります。SUMIFS を試しましたが、ショップ 1 のデータのみが表示され、ショップ 2 のデータが表示されません。参考と数式の修正については、画像を確認してください。

ここに画像の説明を入力してください

データ付き画像1

ここに画像の説明を入力してください

式付き画像2

ここに画像の説明を入力してください

画像3 shop2のデータは表示されません

答え1

その方法の 1 つを以下に示します。

ここに画像の説明を入力してください

• セルで使用されている数式I4

=VLOOKUP($H4,INDIRECT($I$1),MATCH(I$3,HEADERS,0),0)
  • ドロップダウンに応じて数式が適切に機能するように、いくつかの範囲を定義します。
  • 範囲=$B$4:$F$7は次のように定義されますSHOP1
  • 範囲=$B$11:$F$14は次のように定義されますSHOP2
  • ヘッダーはそれぞれ同じままなのでSHOPS、1つを次のSHOPSように定義しますHEADERS。=$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()推奨されません。揮発性関数の使用を避けて作業を容易にするために、データ構造にいくつかの変更を加える必要があります。VolatileExcel

これは、Windows および MACPOWER QUERY以降で利用可能なを使用して実現することもできます。Excel 2010+

ここに画像の説明を入力してください

  • まずソース範囲をテーブルに変換し、それに応じて名前を付けます。この例では、 forSHOP1をSHOP_1、 for をSHOP2、SHOP_2for を とChannel名付けました。Channeltbl
  • Data次に、タブ --> Get & Transform Data--> Get Data--> From Other Sources-->から空のクエリを開きます。Blank Query
  • 上記のようにPower Queryウィンドウが開きます。TabからHome--> Advanced Editor-->そして、表示されているものを削除して次の内容を貼り付けM-Code、を押します。Done

• 出力の場合:

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
  • 覚えておいてください、上記を 2 つの異なる空のクエリに貼り付ける必要があります。また、CHANNELクエリは接続になり、もう 1 つはシートに入力する必要があります。
  • 最後に、それを再度インポートするには、Excel-->Close & LoadまたはをクリックしますClose & Load To--> 最初にクリックすると、New Sheet必要な出力を含む が作成され、後者をクリックすると、結果を配置する場所を尋ねるウィンドウが表示されます。

関連情報