[[20220120145531]] 『データベースから重複無しのデータ抽出集計』(バンバンジー!!) >>BOT

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『データベースから重複無しのデータ抽出集計』(バンバンジー!!)

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

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


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.