[[20220120145531]]　『データベースから重複無しのデータ抽出集計』（バンバンジー！！）　ページの最後に飛ぶ

『データベースから重複無しのデータ抽出集計』（バンバンジー！！）

今週悩み続けましたが、わからないので教えて下さい！！

sheets("データベース")に入力されているデータを
sheets("集計")に抽出して集計したいです。

・抽出元sheets("データベース")

B列　…　客先名
AG列　…　金額

抽出先sheets("集計")

A3　…　客先名
B3　…　金額

sheets("データベース")B列に入力される客先名は重複するため、
重複しない客先名をsheets("集計").range("A3")へ
各客先名の金額の合計をsheets("集計").range("B3")へ抽出したいです。

更に抽出したデータは
sheets("集計").range("F7:F12")にグループごとに金額がまとめます。
(こちらは数式で集計している)

そのグループごとに分けられた値を、

sheets("集計").range("F4")　…　転記先のシート名指定
sheets("集計").range("F5")　…　転記先の月(Month)指定

上記セルで指示した場所に入力したいです。

　　　 B　　 C D E F G H I
4 　 4月　5月 6月 7月 8月 9月 10月
9 グループA 50 0 100
10 グループB 20 0 80
11 グループC 40 50 20

sheets("集計").range("F4")で指示したシート、
かつsheets("集計").range("F5")で指示した月で
sheets("集計").range("F7:F12")の値を転記したいです。

例えば、sheets("集計").range("F5")で"7月"とした場合、
sheets("集計").range("F4")で指示したシートを選択し、
sheets("集計").range("F5")で指示した月に入力ということです。

上記の図？でいうと、range("E4")と一致したので、
range("E9:E14")に転記します。

説明下手で申し訳ありません。
何かヒントでもよろしいのでコメント頂けるとありがたいです。

＜使用 Excel：Office365、使用 OS：unknown ＞

 Office 365なら、殆ど数式主体のマクロで出来そう。

 Sub Macro1()
     Dim cnt As Long
     Dim TgtWsh, TgtMon, TgtCol

     With Sheets("集計")
         .Range("A4", .Cells(.Rows.Count, "A").End(xlUp)).Resize(, 2).ClearContents

         .Range("A4").Formula2R1C1 = _
         "=LET(db,データベース!C[1],FILTER(db,(db<>""客先名"")*(db<>"""")))"

         .Range("B4").Formula2R1C1 = "=SUMIF(データベース!C,RC[-1]#,データベース!C[31])"

         cnt = .Evaluate("COUNT(B4#)") '結果セルの数

         .Range("B4").Resize(cnt) = .Range("B4").Resize(cnt).Value
         .Range("A4").Resize(cnt) = .Range("A4").Resize(cnt).Value

         TgtWsh = .Range("F4")
         TgtMon = .Range("F5")

         TgtCol = Application.Match(TgtMon, Worksheets(TgtWsh).Rows(4), 0)
         .Range("F7:F12").Copy
     End With

     Worksheets(TgtWsh).Cells(9, TgtCol).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
                                     Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 End Sub

（半平太） 2022/01/20(木) 19:28

ありがとうございます！！

上記コードを実行したところ、

    .Range("A3").Formula2R1C1 = _
    "=LET(db,データベース!C[1],FILTER(db,(db<>""部署"")*(db<>"""")))"

重複している客先名がそのまま抽出され、
重複した客先の合計金額が抽出されました。

Sheets("データベース")
客先A　800
客先A　800
客先B　600
客先B　600
客先B　600

Sheets("集計")

客先A　1600
客先A　1600
客先B　1800
客先B　1800
客先B　1800

金額は一致した客先名の合計で抽出しています。
が、客先名がそのままでした。

重複削除のコードを追加しようとしましたが
上手く動作しません…

お手数ですが、再度教えて下さい。

（バンバンジー！！） 2022/01/21(金) 15:22

 済みませーん。UNIQUEにするのを忘れました。

 ＞        .Range("A4").Formula2R1C1 = _
 ＞        "=LET(db,データベース!C[1],FILTER(db,(db<>""客先名"")*(db<>"""")))"

 変更後
         .Range("A4").Formula2R1C1 = _
         "=LET(db,データベース!C[1],UNIQUE(FILTER(db,(db<>""客先名"")*(db<>""""))))"

（半平太） 2022/01/21(金) 16:44

半平太様、お礼が遅くなり申し訳ありません。
無事完成しました！！
ありがとうございました！！

ご質問なのですが、

"=LET(db,データベース!C[1],UNIQUE(FILTER(db,(db<>""客先名"")*(db<>""""))))"

のコードについて
調べたのですがいまいちわかりません。

こちらのコードで重複しないリストを抽出していると思うのですが、
意味を理解できていないため今後に生かせそうにありません。。。
よろしければ教えて下さい。
（バンバンジー！！） 2022/01/28(金) 14:20

  ＞ With Sheets("集計")
  ＞        :
  ＞   .Range("A4").Formula2R1C1 = _
  ＞    "=LET(db,データベース!C[1],UNIQUE(FILTER(db,(db<>""客先名"")*(db<>""""))))"

  そこは、集計シートのA4セルに下の数式を入れています。

  =LET(db,データベース!B:B, UNIQUE(FILTER(db,(db<>"客先名")*(db<>""))))
  ~~~~~~↑~~~~~~~~~~~~~~~~~ ~~↑~~ ~~~~~~~~~~~~~↑~~~~~~~~~~~~~~~~~~~~~
        (1)                  (3)                (2)

  数式の意味
  (1)データベースシートのB列をdbと言う変数に入れます(変数名前は適当でいいす）。
  (2)FILTER関数で「dbが客先名でもなく、空白でもないデータだけ」をdbから抽出します。
  (3)上で得られた結果を重複排除してリストにします。

（半平太） 2022/01/28(金) 22:58

半平太様
なるほど！！！！！
納得しました！！！！
ご丁寧に説明して頂きありがとうございます！！！！

（バンバンジー！！） 2022/01/31(月) 14:35

コメント返信：

[ 一覧(最新更新順) ]

[[20220120145531]] 『データベースから重複無しのデータ抽出集計』（バンバンジー！！） ページの最後に飛ぶ

[[20220120145531]]　『データベースから重複無しのデータ抽出集計』（バンバンジー！！）　ページの最後に飛ぶ