『エクセル自動集計』(ギャップ)
エクセルの集計についての質問です。
例のシートから以下の項目を別シートにシートの更新から集計まで自動で行いたいと思っています。
※前提条件として...
・更新時は毎回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
=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
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
ずうずうしくて申し訳ございませんが入荷シートの出力結果の上に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
とか、今すぐ考え付くのはこれくらいです。(*^^*)。。。1.が手間いらずかも^^;
でわ
m(__)m
(隠居Z) 2024/04/25(木) 08:10:28
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
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 = 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
入荷オンリー 金額も縛りなしバージョンで基準日は常に昨日^^;。。。いいのかなぁこれで
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
■1
この手の作業は、エクセル君がもつ【抽出・集計】の機能を使うとよいように思います。
詳しくないので明言は避けますが、【使用 Excel:Microsoft365】ならばPowerQueryが使えるのでそちらでも目的達成可能かもしれません。
■2
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.」の作業はマクロ側で対応する必要はないとおもいます。
■3
上記について「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
>入荷シートの出力結果の上に4/15入荷数の個数とその中で10000円以上は何点あるのかの個数を別々で表示させることは可能でしょうか?
COUNTIFS関数を設定すればよいのでは?
(もこな2 ) 2024/04/26(金) 23:21:43
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.