エクセルの学校：エクセル質問ボード[[20240423170849]]

エクセル	の学校
8.一覧	9.HOME
1.Top	2.Last

[[20240423170849]]

[ 初めての方へ | 一覧(最新更新順) |

| | 全文検索 | 過去ログ | エクセルの学校HOME ]

『エクセル自動集計』（ギャップ）

エクセルの集計についての質問です。

例のシートから以下の項目を別シートにシートの更新から集計まで自動で行いたいと思っています。 ※前提条件として... ・更新時は毎回CSVでダウンロードしたものを使う・ファイル名はダウンロード毎に変わるため更新時はファイル選択は手動で行う・シートのフォーマットは変わらず項目の内容はダウンロード毎に随時更新される (入荷⇒検品⇒出荷、新商品の入荷など) ・管理番号は商品別につけられており被らない・集計対象のカテゴリは果物のみとする

【集計項目】 ?@管理番号　移動先(入荷のみ)　予定売価(10000以上のみ) ?A管理番号　入荷日からの経過日数(検品のみ、古いものから順番)※今回は例として茨城を集計理想としては販売先ごとにまとまって集計できればベストです

?@は入荷数を把握するため朝一で更新を行い更新日の前日の入荷※今回は更新日を4/16として入荷日4/15を集計 ?Aは?@と同じタイミングで更新を行い更新ごとに最新の状態を集計する

かなり分かりにくい例になってしまっていると思いますがお力添えお願いいたします。

管理番号カテゴリ商品名予定売価　販売先　移動先入荷日 1 果物リンゴ 12000 　　茨城入荷　 2024/4/15 5 果物みかん 3000 　　栃木検品　2024/4/15 2 果物ライチ 5000　　　　茨城　　　入荷　　 2024/4/1 7 果物ブドウ 7000 　　静岡出荷　2024/3/22 4 魚まぐろ 8000 　　岩手入荷　2024/4/10 8 果物イチゴ 11000 　　福岡入荷　2024/4/15 3 魚鮭 13000 　　静岡出荷　2024/3/28 9 果物パイン 4000 　　秋田検品　2024/4/1

＜使用 Excel：Microsoft365、使用 OS：Windows10 ＞

例のシート　シート名　不明　表の開始セル不明。。。なので、相違点が有りましたら更なるご説明を。。。^^;

整理のお手伝いだけでも。(*^^*)

    |[A]     |[B]     |[C]   |[D]     |[E]   |[F]   |[G]      
 [1]|管理番号|カテゴリ|商品名|予定売価|販売先|移動先|入荷日   
 [2]|       1|果物    |リンゴ|   12000|茨城  |入荷  |2024/4/15
 [3]|       5|果物    |みかん|    3000|栃木  |検品  |2024/4/15
 [4]|       2|果物    |ライチ|    5000|茨城  |入荷  |2024/4/1 
 [5]|       7|果物    |ブドウ|    7000|静岡  |出荷  |2024/3/22
 [6]|       4|魚      |まぐろ|    8000|岩手  |入荷  |2024/4/10
 [7]|       8|果物    |イチゴ|   11000|福岡  |入荷  |2024/4/15
 [8]|       3|魚      |鮭    |   13000|静岡  |出荷  |2024/3/28
 [9]|       9|果物    |パイン|    4000|秋田  |検品  |2024/4/1

追記集計は2通り別々の表で作成なのでせうか。どうなればよいのか。。。結果も合わせて、各シート名、セル番地が解るような内容で更なるご説明を賜れば、たくさんアドバイスがあるかもしれません。。。←　多分＾＾； m(__)m

（隠居Z） 2024/04/23(火) 18:58:49

ご丁寧に整理までありがとうございます。

更新時に読み込んだファイルを添付するシートを【理論在庫】集計結果は以下のシート名で別々のシートへ反映 ※訂正経過日数は検品⇒入荷へ変更

 【入荷】

　　|[A] |[B] |[C]

 [1]|管理番号|移動先|予定売価  
 [2]|       1|入荷  |12000
 [3]|       8|入荷  |11000

 【経過日】

　 |[A] |[B] |[C]

 [1]|管理番号|経過日|予定売価  
 [2]|       2|    15|12000
 [3]|       1|     1|11000

（ギャップ） 2024/04/23(火) 20:16:16

※訂正【経過日シート】の予定売価は必要ないので削除（ギャップ） 2024/04/23(火) 20:18:54

入荷シートのＡ２に

=FILTER(FILTER(A2:G9,(B2:B9="果物")*(F2:F9="入荷")*(D2:D9>=10000)),{1,0,0,1,0,1,0})

とかでは。。。どうなりますでせう。。。^^;

　　↑　ダメですよね。。。シート名を指定してくださいませ。すみません。m(__)m

経過日は数式は私の手には負えない様なので他の回答者様のお出ましをお待ちくださいませ。 m(__)m （隠居Z） 2024/04/23(火) 22:17:58

 仕様が今一つ理解できないなぁ・・

 特にここ
 ＞※今回は例として茨城を集計
 ＞理想としては販売先ごとにまとまって集計できればベストです

 理想形を考えると、販売先が出力対象にないとおかしいと思うのだが・・

（半平太） 2024/04/24(水) 10:53:54

経過日はどうやって算出したのですか。（IT） 2024/04/24(水) 11:05:52

（半平太）申し訳ございません。訂正するのを忘れてしまいました。算出例の結果に出力できる方法をご質問させて頂きました。（ギャップ） 2024/04/24(水) 14:10:21

（IT）経過日は更新日と入荷日から算出できれば良いかと思っております。（ギャップ） 2024/04/24(水) 14:13:02

更新日は何処に登録してあるのですか。（IT） 2024/04/24(水) 16:24:06

こんばんわ～＾＾　こんな感じなのでせうかね。半平太さんご指摘の販売先を塊に。。。は事前にソートして戴くとして^^; 以下コードでは無視しています。項目名も書いてませんです(￣▽￣) m(__)m

 Option Explicit
Sub OneInstanceMain_B()
    Dim i As Long
    Dim v, tx, r
    With Worksheets("入荷")
        .UsedRange.ClearContents
        tx = "=FILTER(FILTER(理論在庫!A2:G9,(理論在庫!B2:B9=""果物"")*"
        tx = tx & "(理論在庫!F2:F9=""入荷"")*(理論在庫!D2:D9>=10000)),{1,0,0,1,0,1,0})"
        With .Cells(2, 1)
            .Formula2 = tx
            .CurrentRegion.Copy
            .PasteSpecial (xlValues)
        End With
        Application.CutCopyMode = False
    End With
    With Worksheets("経過日")
        .UsedRange.ClearContents
        tx = "=FILTER(FILTER(理論在庫!A2:G9,(理論在庫!B2:B9=""果物"")*"
        tx = tx & "(理論在庫!F2:F9=""検品"")),{1,0,0,0,0,0,1})"
        With .Cells(2, 1)
            .Formula2 = tx
            .CurrentRegion.Copy
            .PasteSpecial (xlValues)
            Application.CutCopyMode = False
            Set r = .CurrentRegion
        End With
        For i = 1 To r.Rows.Count
            v = r(i, 2)
            v = DateSerial(2024, 4, 16) - v
            With r(i, 2)
                .Value = Format(v, "0 日")
            End With
        Next
    End With
End Sub

（隠居Z） 2024/04/24(水) 17:44:54

   |[A]     |[B]     |[C]   |[D]     |[E]   |[F]   |[G] 
 [1]|2024/4/16
 [2]|管理番号|カテゴリ|商品名|予定売価|販売先|移動先|入荷日 
 [4]|       1|果物    |リンゴ|   12000|茨城  |入荷  |2024/4/15
 [5]|       5|果物    |みかん|    3000|栃木  |検品  |2024/4/15
 [6]|       2|果物    |ライチ|    5000|茨城  |入荷  |2024/4/1 
 [7]|       7|果物    |ブドウ|    7000|静岡  |出荷  |2024/3/22
 [8]|       4|魚      |まぐろ|    8000|岩手  |入荷  |2024/4/10
 [9]|       8|果物    |イチゴ|   11000|福岡  |入荷  |2024/4/15
 [10]|      3|魚      |鮭    |   13000|静岡  |出荷  |2024/3/28
 [11]|      9|果物    |パイン|    4000|秋田  |検品  |2024/4/1

更新日はA1にTODAYで記載しようかと思っております。（ギャップ） 2024/04/24(水) 17:52:16

（隠居Z）理想の結果出ました！大変助かりましたありがとうございます！

ずうずうしくて申し訳ございませんが入荷シートの出力結果の上に4/15入荷数の個数とその中で10000円以上は何点あるのかの個数を別々で表示させることは可能でしょうか？

【入荷シート】

入荷数　　　　　3 10000円以上　　 2

1 12000 入荷 8　11000 入荷

（ギャップ） 2024/04/24(水) 18:30:48

 Option Explicit
Sub OneInstanceMain_A()
    Dim i&, v, tx, r, rZws, dCnt&, oMCnt&, w()
    Set rZws = Worksheets("理論在庫")
    w = rZws.Cells(1).CurrentRegion.Value
    For i = 1 To UBound(w, 1)
        If w(i, 2) = "果物" And w(i, 6) = "入荷" Then
            dCnt = dCnt + 1
            If w(i, 4) >= 10000 Then oMCnt = oMCnt + 1
        End If
    Next
    With Worksheets("入荷")
        .UsedRange.ClearContents
        .Cells(2, 1).Resize(, 2) = Array("入荷数", dCnt)
        .Cells(3, 1).Resize(, 2) = Array("10000円以上", oMCnt)
        tx = "=FILTER(FILTER(理論在庫!A2:G9,(理論在庫!B2:B9=""果物"")*"
        tx = tx & "(理論在庫!F2:F9=""入荷"")*(理論在庫!D2:D9>=10000)),{1,0,0,1,0,1,0})"
        With .Cells(6, 1)
            .Formula2 = tx
            With .CurrentRegion
                .Value = .Value
            End With
        End With
        .UsedRange.Columns.AutoFit
    End With
    With Worksheets("経過日")
        .UsedRange.ClearContents
        tx = "=FILTER(FILTER(理論在庫!A2:G9,(理論在庫!B2:B9=""果物"")*"
        tx = tx & "(理論在庫!F2:F9=""検品"")),{1,0,0,0,0,0,1})"
        With .Cells(2, 1)
            .Formula2 = tx
            With .CurrentRegion
                .Value = .Value
            End With
            Set r = .CurrentRegion
        End With
        For i = 1 To r.Rows.Count
            v = r(i, 2)
            v = DateSerial(2024, 4, 16) - v
            With r(i, 2)
                .Value = Format(v, "0 日")
            End With
        Next
    End With
    Erase w
End Sub
数式使う理由がだんだん薄らいできたような感じがしないでもありませんが^^;
足らずは適当に足してやって下さい。m(__)m
（隠居Z） 2024/04/24(水) 22:02:35

（隠居Z）ありがとうございます！今回の例だとA2：G9の範囲でフィルターをかけて絞り込んでいますが仮に行数がランダムの時に対応する方法はありますでしょうか？（ギャップ） 2024/04/25(木) 07:28:09

おはようございます。結論から申し上げますと方法はいくらでも有ると思います。一案ですが１．最終行を求め↑アドレス部分の9を最終行と差し換える[変数を使えば動的　　に変化させることが可能だと思います] ２．範囲を取得してフイルター詳細に置き換える３．同じく範囲を取得してひたすらループと分岐で処理する＾＾；

とか、今すぐ考え付くのはこれくらいです。(*^^*)。。。１．が手間いらずかも^^; でわ m(__)m （隠居Z） 2024/04/25(木) 08:10:28

r＝最終行※コードわからないので仮

A2：r

↑こんな感じでしょうか？無知ゆえ見当違いでしたら申し訳ございません（ギャップ） 2024/04/25(木) 08:45:26

最初の変数宣言箇所に

 dim lR& ←　追加

 w = rZws.Cells(1).CurrentRegion.Value　←　以降に

 lR = UBound(w, 1)

 tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",(理論在庫!B2:B" & lR & "=""果物"")*"
 tx = tx & "(理論在庫!F2:F" & lR & "=""入荷"")*(理論在庫!D2:D" & lR & ">=10000)),{1,0,0,1,0,1,0})"

 tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",(理論在庫!B2:B9=""果物"")* "
 tx = tx & "(理論在庫!F2:F" & lR & "=""検品"")),{1,0,0,0,0,0,1})"

な、感じ。。。(*^^*) m(_ _)m （隠居Z） 2024/04/25(木) 09:49:18

大変助かりました！本当にありがとうございます（ギャップ） 2024/04/25(木) 21:48:53

 tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",(理論在庫!B2:B9=""果物"")* "

↓

 tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",(理論在庫!B2:B" & lR & "=""果物"")*"

にしてくださいませ。。。変更し忘れです。もうすでにお気づきで、更新済みでしたら無視して下さいね。^^; 済みませんでした。m(__)mm(__)mm(__)m でわ。＾＾

（隠居Z） 2024/04/25(木) 21:58:53

tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",(理論在庫!B2:B" & lR & "=""果物"")*"

 tx = tx & "(理論在庫!F2:F" & lR & "=""入荷"")*(理論在庫!D2:D" & lR & ">=10000)),{1,0,0,1,0,1,0})"
 tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",(理論在庫!B2:B9=""果物"")* "
 tx = tx & "(理論在庫!F2:F" & lR & "=""検品"")),{1,0,0,0,0,0,1})"

入荷、経過日シートの検索対象を入荷のみにする場合はどう書き換えれば良いのでしょうか？※果物も対象外

あと、今回の例ですと4/16を基準になっていますが更新日を基準に1日前に変更など可能でしょうか？（ギャップ） 2024/04/26(金) 07:57:26

１．検索対象選択問題、基準日問題とも　　いろいろと方法は有ると思いますが一案で、　　Ａ．検索対象[複数可能]を入力する　　　　この辺は組み合わせが多いと汎用性を持たせるにはとて～も大変かも^^; 　　　　固定の２～３パターンならその個数分コード書いとけばOKかも　　Ｂ．お気に入りの基準日を入力する　　Ｃ．入力方法は　　　　ユーザーフォーム　　　　シート　　　　インプットボックス　　　　等々が考えられます。シート使うのが一番かんたんかもぉ＾＾；とかおもいまぁす。(*^^*)ｖおおぉ。。。だんだん本格的に。。。(￣▽￣) でわ m(_ _)m　　　　（隠居Z） 2024/04/26(金) 08:20:34

おはようございます　＾＾とりあえず、自分だけがこそこそっと書き換えて使う仕様ですと。。。＾＾私の場合ほとんどこれ。。。(*^▽^*)

入荷オンリー　金額も縛りなしバージョンで基準日は常に昨日^^;。。。いいのかなぁこれで

 Option Explicit
Sub OneInstanceMain_D()
    Dim i&, v, tx, r, rZws, dCnt&, oMCnt&, w(), lR&
    Set rZws = Worksheets("理論在庫")
    w = rZws.Cells(1).CurrentRegion.Value
    lR = UBound(w, 1)
    For i = 1 To UBound(w, 1)
        If w(i, 6) = "入荷" Then
            dCnt = dCnt + 1
            If w(i, 4) >= 10000 Then oMCnt = oMCnt + 1
        End If
    Next
    With Worksheets("入荷")
        .UsedRange.ClearContents
        .Cells(2, 1).Resize(, 2) = Array("入荷数", dCnt)
        .Cells(3, 1).Resize(, 2) = Array("10000円以上", oMCnt)
        tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",理論在庫!F2:F" & lR & "=""入荷""),{1,0,0,1,0,1,0})"
        With .Cells(6, 1)
            .Formula2 = tx
            With .CurrentRegion
                .Value = .Value
            End With
        End With
        .UsedRange.Columns.AutoFit
    End With
    With Worksheets("経過日")
        .UsedRange.ClearContents
        tx = "=FILTER(FILTER(理論在庫!A2:G" & lR & ",理論在庫!F2:F" & lR & "=""入荷""),{1,0,0,0,0,0,1})"
        With .Cells(2, 1)
            .Formula2 = tx
            With .CurrentRegion
                .Value = .Value
            End With
            Set r = .CurrentRegion
        End With
        For i = 1 To r.Rows.Count
            v = r(i, 2)
            Rem v = DateSerial(2024, 4, 16) - v
            v = (Date - 1) - v
            With r(i, 2)
                .Value = Format(v, "0 日")
            End With
        Next
    End With
    Erase w
End Sub
m(__)m
（隠居Z） 2024/04/26(金) 09:54:47

混乱するとよろしくないので、以下は（隠居Z）さんとのやりとりがすべて終わってからお読みください。

■１この手の作業は、エクセル君がもつ【抽出・集計】の機能を使うとよいように思います。

 詳しくないので明言は避けますが、【使用 Excel：Microsoft365】ならばPowerQueryが使えるのでそちらでも目的達成可能かもしれません。

■２ PowerQueryを使わないとして、仰ることは以下で出来るように思います。

 0.【抽出条件】シートを作成して以下のようにしておく
       ___A____   ____B___   ___C__
    1  カテゴリ   予定売価   移動先
    2  果物       >=10000    入荷
    3
    4
    5
    6  カテゴリ   販売先
    7  果物       茨城

 1. ダイアログを出してユーザーに取り込みたいテキスト（CSV）ファイルを指定してもらう

 2.【理論在庫】シートをいったんクリアする

 3.「1.」で取得したテキストファイルを「QueryTables.Addメソッド」を使い【理論在庫】シートにインポートする

 4.【理論在庫】シートのH1セルに"経過日"と書き込む

 5.【理論在庫】シートのH3～H列かつ【理論在庫】シートの最終行までに数式を書き込み、経過日を算出する

 6.【入荷】シートのA1～C1セルに"管理番号","移動先","予定売価"と書き込む

 7.【経過日】シートのA1～C1セルに"管理番号","経過日","予定売価"と書き込む

 8.【フィルタオプション】を使い、【理論在庫】シートの情報を、【抽出条件】シートの条件に基づいて、【入荷】シートに抽出する

 9.【フィルタオプション】を使い、【理論在庫】シートの情報を、【抽出条件】シートの条件に基づいて、【経過日】シートに抽出する

 ※実際には「6.」「7.」の作業はマクロ側で対応する必要はないとおもいます。

■３上記について「4.」より前は、過去ログなんかを見てもらうとして、それ以降をコード化すると↓みたいなことになろうかと思います。

    Sub 研究用()
        Dim 最終行 As Long

        With Worksheets("理論在庫")
            .Range("H2").Value = "経過日"
            最終行 = .Cells(.Rows.Count, "A").End(xlUp).Row
            If 最終行 > 2 Then
                .Range("H3:H" & 最終行).Formula = "=TODAY()-G3" '数式は適宜修正のこと
                .Range("H3:H" & 最終行).NumberFormatLocal = "G/標準"

                Worksheets("入荷").Range("A1:C1").Value = Split("管理番号,予定売価,移動先", ",")
                Worksheets("経過日").Range("A1:C1").Value = Array("管理番号", "経過日", "予定売価")

                .Range("A2:H10000").AdvancedFilter _
                    Action:=xlFilterCopy, Unique:=False, _
                    CriteriaRange:=Worksheets("抽出条件").Range("A1").CurrentRegion, _
                    CopyToRange:=Worksheets("入荷").Range("A1:C1")

                .Range("A2:H10000").AdvancedFilter _
                    Action:=xlFilterCopy, Unique:=False, _
                    CriteriaRange:=Worksheets("抽出条件").Range("A6").CurrentRegion, _
                    CopyToRange:=Worksheets("経過日").Range("A1:C1")
            End If
        End With
    End Sub

 ※上記は説明のための提示であり、完成品プレゼントの意図はありません。
 ※採用される場合は、仕組み（理屈）がわかってから、必要な部分のみご自身のコードに組み込んでください。

このように、エクセル君のもつ機能を活用することでコーディングの手間もだいぶ省けるのではないかと思いました。

■４＞入荷シートの出力結果の上に4/15入荷数の個数とその中で10000円以上は何点あるのかの個数を別々で表示させることは可能でしょうか？ COUNTIFS関数を設定すればよいのでは？

（もこな２） 2024/04/26(金) 23:21:43

[ 一覧(最新更新順) |

]

キーボードヒント：[Home]または[Fn+Home]キーで一番上へ戻ります