[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VBA データ取得』（楓）
いつもお世話になります。
【1】ファイルのデータから【2】ファイルに書き込むＶＢＡを作成しています。
【1】URI_KANRI_GEPOU20160401.csv（参照するファイル）
|[C] |[D]|[E] |[F] |[G]|[H]|[I]|[J]|[K] |[L]|[M]
[1] |店舗CD| |営業日 |曜日| | | | |純売上高| |総売上高
[2] | 201| |2016/4/1 |金 | | | | | ******| | ******
[3] | 201| |2016/4/2 |土 | | | | | ******| | ******
[4] | 201| |2016/4/3 |日 | | | | | ******| | ******
[5] | 201| |2016/4/4 |月 | | | | | ******| | ******
省略
[30] | 201| |2016/4/29|金 | | | | | | |
[31] | 201| |2016/4/30|土 | | | | | | |
[32] | 1401| |2016/4/1 |金 | | | | | ******| | ******
[33] | 1401| |2016/4/2 |土 | | | | | ******| | ******
[34] | 1401| |2016/4/3 |日 | | | | | ******| | ******
[35] | 1401| |2016/4/4 |月 | | | | | ******| | ******
省略
[60] | 1401| |2016/4/29|金 | | | | | | |
[61] | 1401| |2016/4/30|土 | | | | | | |
省略
[542]| 6900| |2016/4/1 |金 | | | | | ******| | ******
[543]| 6900| |2016/4/2 |土 | | | | | ******| | ******
[544]| 6900| |2016/4/3 |日 | | | | | ******| | ******
[545]| 6900| |2016/4/4 |月 | | | | | ******| | ******
省略
[570]| 6900| |2016/4/29|金 | | | | | | |
[571]| 6900| |2016/4/30|土 | | | | | | |
空白列は別のデータが入っています。
現在571行目が最終データですが、「19（店舗数）×日数+1」行と考えて頂ければと思います。
店舗数も変わる可能性有りです。
【2】月報データ（書き込むファイル）
|[B] |[C] |[D] |[E] |[F]|[G]|[H]|[I]|[J]|[K]|[L]|[M]|[N]|[O]|[P] |[Q] |[R] |[S] |[T]|[U]|[V]|[W]
[1] | 1401| | | | | | | | | | | | | | | | | | | | |
省略
[39] |2015年1月| | | | | | | | | | | | | |2015年2月| | | | | | |
[40] |営業日 |曜日|純売上高 |総売上高 | | | | | | | | | | |営業日 |曜日|純売上高 |総売上高 | | | |
[41] |2015/1/1 |木 | ***,***| ***,***| | | | | | | | | | |2015/2/1 |日 | ***,***| ***,***| | | |
[42] |2015/1/2 |金 | ***,***| ***,***| | | | | | | | | | |2015/2/2 |月 | ***,***| ***,***| | | |
[43] |2015/1/3 |土 | ***,***| ***,***| | | | | | | | | | |2015/2/3 |火 | ***,***| ***,***| | | |
省略
[68] |2015/1/28|水 | ***,***| ***,***| | | | | | | | | | |2015/2/28|土 | ***,***| ***,***| | | |
[69] |2015/1/29|木 | ***,***| ***,***| | | | | | | | | | | | | | | | | |
[70] |2015/1/30|金 | ***,***| ***,***| | | | | | | | | | | | | | | | | |
[71] |2015/1/31|土 | ***,***| ***,***| | | | | | | | | | | | | | | | | |
[72] |合計 | |*,***,***|*,***,***| | | | | | | | | | |合計 | |*,***,***|*,***,***| | | |
[73] | | | | | | | | | | | | | | | | | | | | | |
[74] |2016年1月| | | | | | | | | | | | | |2016年2月| | | | | | |
[75] |営業日 |曜日|純売上高 |総売上高 | | | | | | | | | | |営業日 |曜日|純売上高 |総売上高 | | | |
[76] |2016/1/1 |金 | ***,***| ***,***| | | | | | | | | | |2016/2/1 |月 | ***,***| ***,***| | | |
[77] |2016/1/2 |土 | ***,***| ***,***| | | | | | | | | | |2016/2/2 |火 | ***,***| ***,***| | | |
[78] |2016/1/3 |日 | ***,***| ***,***| | | | | | | | | | |2016/2/3 |水 | ***,***| ***,***| | | |
省略
[103]|2016/1/28|木 | ***,***| ***,***| | | | | | | | | | |2016/2/28|日 | ***,***| ***,***| | | |
[104]|2016/1/29|金 | ***,***| ***,***| | | | | | | | | | |2016/2/29|月 | ***,***| ***,***| | | |
[105]|2016/1/30|土 | ***,***| ***,***| | | | | | | | | | | | | | | | | |
[106]|2016/1/31|日 | ***,***| ***,***| | | | | | | | | | | | | | | | | |
[107]|合計 | |*,***,***|*,***,***| | | | | | | | | | |合計 | |*,***,***|*,***,***| | | |
空白列は別のデータが入っています。
表の範囲は全て一緒で右に12月までデータが入っている形になります。
営業日も含めて取得していない年月は空欄になっています。
B1セルに店舗CDを入力しています。
対象年月は【1】のE2もしくはファイル名で取得
上記参照データは2016年4月なので、AR76〜AU106に今回は記載
数式だけであれば
=INDEX(URI_KANRI_GEPOU20160401.csv!$M:$M,SUMPRODUCT((URI_KANRI_GEPOU20160401.csv!$C:$C=$B$1)*(URI_KANRI_GEPOU20160401.csv!$E:$E=$BF97)*ROW(URI_KANRI_GEPOU20160401.csv!$C:$C)))
というのに辿りつくことはできたのですが、
どうしても重いデータになるので、ＶＢＡで取得して値だけを入力したいです。
いつも分かりずらく申し訳ありませんが宜しくお願い致します。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
こんばんわ。
まず、URI_KANRI_GEPOU20160401.csv と言う名前のブックがあって、その中の店舗CD毎に 営業日に対応する純売上高・総売上高だけを抜き出したいと言う事で合ってますか？
月報データと言うのは、店舗ごとに違うブックなのでしょうか？ それとも、1つのブックの中に店舗ごとのシートがあるのでしょうか？
後、この数式で表示出来てるんですか？ =INDEX(URI_KANRI_GEPOU20160401.csv!$M:$M,SUMPRODUCT((URI_KANRI_GEPOU20160401.csv!$C:$C=$B$1)*(URI_KANRI_GEPOU20160401.csv!$E:$E=$BF97)*ROW(URI_KANRI_GEPOU20160401.csv!$C:$C)))
URI_KANRI_GEPOU20160401.csv!$M:$M これってシートを参照する式ですよね。 参照するファイルって書いてあるけど、実は同じブック内のURI_KANRI_GEPOU20160401.csvと言うシートで、 書き込むファイルと言うのも、同じブック内で単にシートで別れてるだけですか？
同じブックなのかどうかで、式やコードも大きく変わるので、その辺はっきりと提示して下さい。
（sy） 2016/04/26(火) 22:37
＞まず、URI_KANRI_GEPOU20160401.csv と言う名前のブックがあって、その中の店舗CD毎に
営業日に対応する純売上高・総売上高だけを抜き出したいと言う事で合ってますか？ 店舗CD毎に営業日・曜日・純売上高・総売上高の４点を抜き出したいです。
＞月報データと言うのは、店舗ごとに違うブックなのでしょうか？
それとも、1つのブックの中に店舗ごとのシートがあるのでしょうか？ [月報データ.xlsx]ブックの中に店舗毎にシートがあります。
＞後、この数式で表示出来てるんですか？
「URI_KANRI_GEPOU20160401.csv」ファイルを開いている場合のみ表示されます。
開いていない場合は#REF!になりました。
こちらのファイルは別ブックになります。
[URI_KANRI_GEPOU20160401.csv]ブックには「URI_KANRI_GEPOU20160401」シート
[月報データ.xlsx]ブックには19店舗分のシート
があります。
[URI_KANRI_GEPOU20160401.csv]のURI_KANRI_GEPOU20160401シートから
[月報データ.xlsx]の各店舗シートに書き込みます。
宜しくお願い致します。
（楓） 2016/04/27(水) 09:30
月報データにつき 2点、質問です。
１．たとえば Ｂ７４ に 2016年1月 と記載されていますが、これは あらかじめ 記載されているのですか？ （つまり、2016年の場所はここだということが検索すればわかる） それとも、csvから転記する際に、それまでのデータ最終行の次に追加していくのですか？ ２．上記次第ですが、合計欄には、あらかじめ数式が入っていますか？ それとも合計もマクロでセット？
（β） 2016/04/27(水) 11:34
１．年月についてはあらかじめ記載しておく予定です。
２.合計欄は数式が入っています。
宜しくお願い致します。
（楓） 2016/04/27(水) 11:46
一例です。
各月の1日から最終日までの日付と曜日は、あらかじめセット済みとの前提。 最初に２つのダイアログを表示し、ＣＳＶファイルと報告書ブックを選択します。
Sub Test()
Dim csv As String
Dim xlsx As String
Dim shT As Worksheet
Dim shF As Worksheet
Dim dateF As Date
Dim rowT As Long
Dim colT As Long
Dim d(1 To 31, 1 To 2)
Dim i As Long
Dim c As Range
csv = GetBook("CSVファイルを選んでください", "csv")
If csv = "" Then Exit Sub
xlsx = GetBook("月報データを選んでください", "xlsx")
If xlsx = "" Then Exit Sub
Application.ScreenUpdating = False
Set shF = Workbooks.Open(csv).Sheets(1)
Set shT = Workbooks.Open(xlsx).Sheets(1)
dateF = shF.Range("E2").Value
dateF = DateSerial(Year(dateF), Month(dateF), 1)
colT = 2 + (Month(dateF) - 1) * 14
For i = 41 To shT.Cells(shT.Rows.Count, colT).End(xlUp).Row Step 35
If shT.Cells(i, colT).Value = dateF Then
rowT = i
Exit For
End If
Next
If rowT = 0 Then
MsgBox dateF & " が " & shT.Parent.Name & " に記載されていません"
Else
For Each c In shF.Range("C2", shF.Range("C" & Rows.Count).End(xlUp))
If c.Value = shT.Range("B1").Value Then
i = Day(c.Offset(, 2))
d(i, 1) = d(i, 1) + c.EntireRow.Range("K1").Value
d(i, 2) = d(i, 2) + c.EntireRow.Range("M1").Value
End If
Next
shT.Cells(rowT, colT + 2).Resize(UBound(d, 1), UBound(d, 2)).Value = d
End If
shF.Parent.Close False 'Csvファイルを保存なしで閉じる
shT.Parent.Close True '月報データを保存して閉じる
' shT.Parent.Save もし、月報データを閉じたくなければこちらで
End Sub
Private Function GetBook(tttt As String, ext As String) As String
Dim ffff As Variant
ffff = Application.GetOpenFilename("ExcelBook,*." & ext, , tttt)
If ffff = False Then
GetBook = ""
Else
GetBook = ffff
End If
End Function
（β） 2016/04/27(水) 18:42
おはようございます。
もう1つ気になった事が、式中の URI_KANRI_GEPOU20160401.csv これってcsvファイルは月毎に１つづつ存在するって事ですか？
後、今朝はアップ間に合わなかったけど、 2007ならsumifsが使えるので、関数で表示させても たいして重くならないと思います。 重いのは、上記の配列数式にしてるのと、 その他の関数が原因じゃ無いですか？ 5年分12ヶ月表示させて、19シートでファイルサイズは1.7メガでした。 (リンク先は１つにしたので実際はもう少し重くなると思いますが)
（sy） 2016/04/28(木) 08:04
βさん
ありがとうございます。
確認させていただきます。
syさん
ありがとうございます。
csvファイルは月毎に1つずつ存在します。
なので、2016年5月はURI_KANRI_GEPOU20160501.csvとなります。
sumifsがうまく使用しきれなくて、いつも断念してしまいます・・
また、自分以外の人が主に使うため、SUM等の数式はいいのですが
参照数式が表に出ないのが理想です。
最初は置き換えマクロを作成して作ろうと思ったのですが
上記等の理由から今回質問させていただきました。
（楓） 2016/04/28(木) 09:04
確認しながら、実行させていただきました。
月報データファイルの1つのシートを目的に動作させるのは問題ありませんでしたが
全シートを対象にすると、2つ目のシート以降がどんどん前の情報を足してしまい
最終的には19店舗の合計が一番最後のシートに表示されていました。
最後の
shT.Cells(rowT, colT + 2).Resize(UBound(d, 1), UBound(d, 2)).Value = d
の部分をシート変更する際に元に戻す宣言をしないといけないとは思うんですが
どうしたらいいのか分かりませんでした。
ご教授いただけると助かります。
宜しくお願い致します。
（楓） 2016/04/28(木) 15:46
アップしたコードは 単一のシートのみを相手にしているんですが、これを元に複数シートに店舗ごとのデータを 繰り返し反映させるように手直しをされたんですね？
コードが見えないのですが、シート毎にループさせる、その先頭に Erase d を入れてみてください。 (あるいは、shT.Cells(rowT, colT + 2).Resize(UBound(d, 1), UBound(d, 2)).Value = d の下でもＯＫ）
（β） 2016/04/28(木) 16:27
ありがとうございます！
無事希望のデータ取得を行うことができました！
（楓） 2016/04/28(木) 17:27
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.