[[20110909104302]]　『データを別のBOOKに振り分ける』（eri）　ページの最後に飛ぶ

『データを別のBOOKに振り分ける』（eri）Win7 ｴｸｾﾙ2010

 いつもこちらのサイトにはお世話になっています。

 下記のシートは12か月分の入力シートを一つのシートにまとめたものです。

 現在は、関数で対応していますが処理時間がかかるのです。VBAなら少しでも処理が早く
 なるのではと思い、色々本や過去の質問事項を見てみましたがVBA初心者の私には
 難しく参考例のコードをコピーするしかありません。大変長い質問文になっていますが

 ご教授よろしくお願い致します。

 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
	A	B	C	D	E	F	G	E	F	G	H	Q
1	日付	工番	品名	仕入先	数量	単価	材料費	労務費	外注費	旅費	雑費	伝NO
2	6/1	1-05	AAA	N社	2	1000	2000					10
3	6/10	1-06	BBB					10000				
4	・											
:	・											
:	・											
30	7/1	1-10	CCC	M社	1	3000	3000					
:												
:												
4200												
 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

 12か月分の入力シートを一つのシートに下記のコードでまとめることが出来ましたが・・・（参考例からのコピーです。）
 　
Sub sh_check()
　Dim newSh As String
　Dim Sh As Worksheet, myFlag As Boolean
　　newSh = "全データ"
　　myFlag = False
　　For Each Sh In ThisWorkbook.Worksheets
　　　　If Sh.Name = newSh Then
　　　　　　myFlag = True
 　
　　　　　　'---全データシートのデータをクリアし、先頭へ移動します
　　　　　　Worksheets(newSh).Cells.ClearContents
　　　　　　Worksheets(newSh).Move before:=Sheets(1)
　　　　　　Exit For
　　　　End If
　　Next Sh
 　
　　'---全データシートを先頭へ追加します
　　If myFlag = False Then
　　　　ActiveWorkbook.Worksheets.Add(before:=Worksheets(1)).Name = newSh
　　End If
End Sub
 　
Sub matome()
　Dim i As Integer
　Dim lRow As Long, lCol As Long, lRow2 As Long
　　Application.ScreenUpdating = False
 　
　 　'---全データシートの有無をチェックします
　　sh_check
 　
　　'---列見出しをコピーします
　　Worksheets(2).Range("1:1").Copy Worksheets(1).Range("A1")
　　For i = 2 To Worksheets.Count
　　　　With Worksheets(i)
　　　　　　lRow = .Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　lCol = .Cells(1, Columns.Count).End(xlToLeft).Column
 　
　　　　　　'---シートのデータが2行以上の場合にコピーします
　　　　　　If lRow >= 2 Then
　　　　　　　　lRow2 = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
　　　　　　　　.Activate
　　　　　　　　.Range(Cells(2, 1), Cells(lRow, lCol)).Copy Worksheets(1).Cells(lRow2, 1)
　　　　　　End If
　　　　End With
　　Next i
 　
　　Worksheets(1).Activate
　　Range("A1").Select
　　Application.ScreenUpdating = True
End Sub

 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
 別のBOOKに工番毎のシートでデータを振り分けたいのです。工番はかなりの数になります
 ので、一つのBOOKに20シートでBOOK数も10個以上になります。

 振り分ける先の工番シートは以下のような書式です。

 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
	A	B	C	D	E	F	G	H	I	J	K	L
1												
:												
:												
7	No.	日付	工番	品名	仕入先	数量	単価	材料費	労務費	外注費	旅費	雑費
8	1	6/1	1-005	AAA	N社	2	1000	2000				
9	2	6/2	1-005	BBB	C社	1	5000	5000				
10	3	6/10	1-005							20000		
:												
:												
500												
 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
　
 まとめたシートも振り分け先のシートもデータが更新された際、コマンドボタンで更新できるように
 したいのですが合わせてご教授よろしくお願いいたします。

 ======================================================================
 [未]マークが無かったせいか（？）レスがついてなかったのであげてみます。
 2011/09/09(金)17:06 　の登録でした。
 (HANA)　2011/09/11(日)16:35

 (HANA)さんへ

 いわゆる、若い人が言う　age ってやつですね。
 ところで、あがるのはわかるんですが、「未」は、どうやってつくんですか？

 (eri)さんへ

 ・まず、元のシート->12ヶ月分のまとめシート　これはこれで、この12ヶ月分のまとめシートから分割？
　　それとも、元のシート->直接分割　？

 ・分割は工番ごとというのはわかるけど、それを複数ブックの複数シート？
　であればブックの分割基準は？月別? そして１ブックのシート数が２０というのは？　シートをわける基準は？

 追加）最初の例、G列の次にまたE列がきてる。E-F-G-H-Q この意味は？

 (ぶらっと)

 １．マクロブックに"全データ"というシートがなければ作った上で、それを一番左のシートにして内容をクリア。
 ２．それ以外のシートでタイトル行以外にデータがあるシートからデータを"全データ"シートに上詰めでコピペ。
（ここまでがアップされたコード）

 ３．続けて、この"全データ"シートから、「月別」に別ブックを生成。
 ４．その「月別の別ブック」は、「工番」ごとにシートを分ける。
 ５．その後、データ追加修正があるので、必要な時に、いつでも「再実行」して、「月別ブック」をアップデートしたい。
　（実際には、かわったところをメンテするのではなく、１２ヶ月分のブックを削除した上で再作成することになると思うけど）

 こんなことかな？

 (ぶらっと)

 To,eriさん

 この掲示板は、文頭に半角スペースを入れると
 空行を入れなくても、改行がそのまま表示される様に成ります。
 最初の投稿は、同時に編集しました。
_←ここに半角スペースですが、位置が良く分からなければ
 プレビュー画面で確認してもらうと良いかもしれません。

 (HANA)

HANAさん、ぶらっとさん、有難うございます。

 質問の仕方とか内容の表示が上手く出来ずごめんなさい。
 まず、最初の表の列おかしくなってますね。アルファベット順です。
 まとめシート（全データ）からの振分けです。

 HANAさんのおっしゃる通りです。
 事前に振分け先のブックとシートは出来ていて、A1に工番を入力すると（シート名と同じ工番）
 ”全データ”シートから同じ工番のデータを表示させる。まで関数でしてます。
 なので処理に時間がかかります。
 工番は連番になってますので、ブック1No.1～20 ブック2No.21～40・・・と名前を付けてずらーと並びます。
 確かに、探すのも少し大変ですし効率が悪いと思います。良い方法をご教授下さい。（eri)

 すこ～しイメージがクリアになってきた。
HANAさんがレスされてけされたところにもあったように、【実際に運用】をしていくうちに
不都合というか、困るところも出てくるとは思うけど、まず、もう少しクリアに理解したい。

 ・既にできているブックは【単一ブック】？　それとも【複数のブック】？
　後者だとしたら、それは、年月別？(ブック_201106.xls とか）　あるいは年別？(ブック_2011.xls とか)
　あるいは　工番別？(No1.xls とか)
 ・で、単一であれ複数であれ、そのブックのシートは何別？そのシート名の名前の基準は？

 (ぶらっと)

 「内容の確認が出来れば良い」と言うことなら
 私なら取り敢えず、外部データの取り込み＆入力規則＆VLOOKUP関数　をやってみるかな。。。

 全データが入っているシートと、工番情報が一覧に成っているシートと、確認用のシートを
 それぞれ一枚ずつ用意。

 確認用のシートの例えば、A2セルに入力規則で、工番情報一覧から　工番部分をリスト設定。
http://www.excel.studio-kazu.jp/lib/e2k/e2k.html
 ↑ライブラリ『簡易リストボックス』

 7行目より上の部分は、VLOOKUP関数で関連情報を表示。

 7行目以降は、外部データの取り込みで　A2セルで選んだ工番のデータを表示。
http://www11.plala.or.jp/koma_Excel/contents6/mame6041/mame604101.html
 ↑『Excel豆知識41エクセル外部データの取り込み（パラメータークエリの利用）条件によるデータの抽出』

 7行目より上の部分をこのシートで変更する必要が有る場合は
 マクロも組み合わせる事に成りそうですが。

 こんな感じで、一枚のシートを使い回して良いなら
 フィルタオプションの設定を基本に　コードにして
 「A2セルに変化が有ったら自動実行」と言う形を取っても
 そう面倒な事には成らないのではないかと思います。

 (HANA)

 HANAさんとは別の観点で。
もしかしたら、複数のブックというのが、どんな期間はわからないけど、とにかく、ある期間のデータが入っているブックがあって
そのブックそれぞれに、【同じマクロ】があって（つまり、データブック=マクロブック）そのブック内で
月別のシートを全データシートにまとめ、その全データシートから、指定工番を抜き出したものを、
【そのブック内】の当該工番別シートに振り分けるという、そんなイメージ？

 (ぶらっと)

 フォルダー（52期工事）の中に、"月別ﾃﾞｰﾀ入力"名のﾌﾞｯｸ [ｼｰﾄ数14: 繰越・6月～翌年5月・全ﾃﾞｰﾀ ]
 "原価計算簿(繰越)"名のﾌﾞｯｸ　"原価計算簿(52-1～20)"・・・・・と複数ﾌﾞｯｸがあります。

 "全ﾃﾞｰﾀ"ｼｰﾄには列A～Lまで以下の数式（6月～12月分まで）
 =IF(INDIRECT(ADDRESS(IF(MOD(ROW(),350),MOD(ROW(),350),350),COLUMN(),,,INT(ROW()/350)+IF(MOD(ROW  (),350),1,0)+5&"月"))="","",INDIRECT(ADDRESS(IF(MOD(ROW(),350),MOD(ROW(),350),350),COLUMN(),,,INT(ROW()/350)+IF(MOD(ROW(),350),1,0)+5&"月")))

 (1月～5月分は）
 =IF(INDIRECT(ADDRESS(IF(MOD(ROW(),350),MOD(ROW(),350),350),COLUMN(),,,CHOOSE(INT(ROW()/350)+IF(MOD(ROW(),350),1,0),6,7,8,9,10,11,12,1,2,3,4,5)&"月"))="","",INDIRECT(ADDRESS(IF(MOD(ROW(),350),MOD(ROW(),350),350),COLUMN(),,,CHOOSE(INT(ROW()/350)+IF(MOD(ROW(),350),1,0),6,7,8,9,10,11,12,1,2,3,4,5)&"月")))

 "原価計算簿"の各ｼｰﾄには、列B～Lまで以下の数式
 =IF(COUNTIF([月別ﾃﾞｰﾀ入力.xlsx]全ﾃﾞｰﾀ!$B$2:$B$4200,"*"&$B$1&"*")<$A8,"",INDEX([月別ﾃﾞｰﾀ入力.xlsx]全ﾃﾞｰﾀ!A$1:A$4200,SMALL(INDEX(ISERROR(FIND($B$1,[月別ﾃﾞｰﾀ入力.xlsx]全ﾃﾞｰﾀ!$B$2:$B$4200))*10^5+ROW($B$2:$B$4200),),$A8)))

 "原価計算簿"の各ｼｰﾄ、A7からNo.を=MAX(A$7:A7)+1でふっています。
 月末毎に集計をするので集計行のNo.をDeleteしてｴﾗｰ表示がでますが、またDeleteしてSUM計算してます。

 現在は、これで処理してますので本当に時間がかかっています。
 HANAさん、ぶらっとさん、長い説明になっていますが効率よく出来るでしょうか？（eri）

 表示するのが目的なのか、集計するのが目的なのか。。。？
 月末には、集計と共に印刷も必要なのか？
 計算だけなら、エラーが出るのに「集計行のNo.をDelete」なんてしなくても良さそうですし。。。
 　勿論、Deleteしなくても他の値が混ざらない様な式に成っている場合　ですが。。。
 　これが成っていないのかな？
 　それとも、やはり計算だけではないのかな？

 「データの確認のために工番毎に振り分けたシートを用意している」
 　　例えば、何が入荷済みで、何が未入荷なのかの確認　等
 って感じではなさそうですね？

 月末に集計もするけど、月末まででデータを確認したりもする？？

 そもそも、工番毎にシートを分けるのはどうしてなのでしょう？
 それが分かれば、方針も決まってきそうに思います。

 (HANA)

 他の方々と違い、当方、関数はさっぱりなので、アップされた式を読んで、どこの何をどうしているのかを理解して・・
というのは、お手上げ。

 HANAさんにフォローしてもらえるとは思うけど、ＶＢＡ処理でアドバイスを得ようとする場合は
最初にアップされたようなイメージで、
・このフオルダに、こんな名前のブックがある。
・Ａブックには、こういう名前でこういうレイアウトのシートがこれだけある。
・Ｂブックには・・・・・
・で、こういう入力をここにすると、Ａブックのシートのこの条件のもの、Ｂブックのこの条件のものを
　Ｘブックのこのシートに、このように転記したい。

 こんなふうに説明があればありがたいとは思う。

 (ぶらっと)

 説明下手ですみません。HANAさんの疑問に思っている事すべて必要なのです。
 工番は工事件名毎に社内処理し易いようにふってます。その工事の請負金額に対して
 原価がどれくらいかかっているか把握したり、確認する為にシートを分けています。
 月末集計して印刷します。

 ぶらっとさん、VBA処理でアドバイス頂けるよう、イメージを整理してアップしますので
 少しお時間ください。（eri）

 それにしても、全てのシートを並べて見比べる事は無いと思うので
 全てのシートを用意しておく必要もなさそうに思いますけどね。

 シートタブを切り換える様に、指定したセルの値を切り換える。
 すると、現在　ブックやシートが大量に有りますが、それが無くなるので
 探す手間も省けるんじゃないかと思いますが。。。

 ＞工番は連番になってますので、ブック1No.1～20 ブック2No.21～40・・・と名前を付けてずらーと並びます。
 ＞確かに、探すのも少し大変ですし効率が悪いと思います。
 効率が悪いと思うのは、ブックやシートがたくさん有るからなのでは。。。？

 と思いますが。。。現状のまま数式部分をVBA化するおつもりでしたら
 既に運用実績も有る様ですし、これ以上関わらない事にします。
 この書き込みへのレスも不要です。

 (HANA)

 To:ぶらっとさん
「未」の表示についてだけ顔出しです。
判断方法は文字列「----」が本文中に登場するか、です。
「未回答マーク」のキーワードで全文検索すると、導入に纏わる経緯がヒットすると思います。
（ご近所PG）

 ご近所PGさん

 ご教示ありがとうございます。
実は、本件、ずっと上の方でHANAさんが、いったん説明をアップしてくださって、それで、なるほどなと。
なぜか、その部分を、後で削除しておられますが。
（しかも、その削除が「差分」でみても、みあたらない。さては、まだまだ隠し技があるのかな？）

 (ぶらっと)

 なるほどですね。
最初から経緯を見守ってた訳じゃないので、あれスルーされてる？んじゃ一応書いとくかーとしゃしゃり出ました。
Wikipediaとかでは過去履歴がずーっと残ってたりしますが、
おそらくココでは校長先生が改造して、差分は直前の物との比較だけを参照出来るような形に手を加えられているのだろう、
と思います。全部もってると重くなるだろうし。
　
もしより以前のも参照できるなら便利ですけどね。
（ご近所PG）

コメント返信：

[ 一覧(最新更新順) ]

[[20110909104302]] 『データを別のBOOKに振り分ける』（eri） ページの最後に飛ぶ

[[20110909104302]]　『データを別のBOOKに振り分ける』（eri）　ページの最後に飛ぶ