Aベストアンサー
条件が複雑で大分苦労しましたが関数で処理する方法です。
関数で処理することでシートの数が多くなっても即座に対応できますし（ここでの例では１０シートまで）、データがそれぞれのシートに新たに追加された場合でも即座にお求めの表が自動的に変更されますので、常に最新のデータがお求めの表に表示されていることになります。マクロでは操作を行わなければ最新のデータの表示とはなりませんね。また、お求めの表では２カ月間の表示とは限らずに、例ですが、６カ月間までを表示させることもできるようになっています。
なお、日付の入力は6/1や６月１日のように入力しても数式バー上では2013/6/1のように日付として認識されていることが必要です。また、まとめの表では6月のデータとするときには6/1や2013/6/1のように入力してからセルの表示形式のユーザー定義で m月 のようにして6月と表示させるようにします。
これから示す例ではまとめの表をシート幾つに作成してもよいのですがシート３にまとめの表を表示させるとします。
初めに各会社ごとのシート、シート１、シート２、シート４、シート５・・・について作業列をＧ列に設けます。各シートのＧ2セルには次の式を入力して下方にドラッグコピーします。作業に当たっては例えば6月と7月の表を作るとしたらシート3のA3セルに2013/6/1、A4セルには2013/7/1 のように入力しておくのがよいでしょう。入力が無い場合には空白のままのセルとなります。
=IF(D2="","",IF(COUNTIF(Sheet3!A$3:A$8,DATE(YEAR(D2),MONTH(D2),1))>0,DATE(YEAR(D2),MONTH(D2),1)*1000+COUNTIF(D$2:D2,">="&DATE(YEAR(D2),MONTH(D2),1))-COUNTIF(D$2:D2,">="&DATE(YEAR(D2),MONTH(D2)+1,1)),""))
そこでまとめのシートですが例えばシート3に作るとして次のようにします。
Ｂ1セルから横方向には会社名をＡ社、Ｂ社、Ｃ社・・・のようにＫ1セルまでに入力します。
Ｂ2セルから横のセルには対応するシート名を入力します。例えばB2セルにSheet1、Ｃ2セルにSheet2、Ｄ2セルにSheet4などと半角英数文字で実際のシート名に合う文字で入力します。
A3セルからA8セルまでには表示させたい月を6月と7月の表を作りたい場合にはA3セルには2013/6/1、A4セルには2013/7/1のように必ず月の初めの日付で入力します。その後にA3セルからA8セルを選択してセルの表示形式の「ユーザー定義」で m月 のようにして6月、7月のように表示させます。
B3セルには次の式を入力して右横方向のK3セルまでドラッグコピーしたのちに下方の8行目までドラッグコピーします。
=IF(OR($A3="",B$2=""),"",IF(ROW(A1)=1,IF(COLUMN(A1)=1,0,OFFSET(B3,0,-1)),IF(ROW(A1)>1,IF(COLUMN(A1)=1,MAX($B2:$K2),OFFSET(B3,0,-1))))+COUNTIF(INDIRECT(B$2&"!G:G"),">="&$A3*1000)-IF($A4="",0,COUNTIF(INDIRECT(B$2&"!G:G"),">="&$A4*1000)))
お求めの表は10行目から下方に表示させることにします。
A10セルには発注一覧表と入力します。
B11セルには得意先、C11セルには発注No、D11セルには商品名、E11セルには納品日とそれぞれ文字列を入力します。
A12セルには次の式を入力して下方にドラッグコピーします。
=IF(ROW(A1)=1,A3,IF(COUNTIF(INDEX(B$3:K$8,1,COUNT(B$3:K$3)):INDEX(B$3:K$8,6,COUNT(B$3:K$3)),ROW(A1)-1)=0,"",IF(INDEX(A$3:A$8,MATCH(ROW(A1)-1,INDEX(B$3:K$8,1,COUNT(B$3:K$3)):INDEX(B$3:K$8,6,COUNT(B$3:K$3)),0)+1)=0,"",INDEX(A$3:A$8,MATCH(ROW(A1)-1,INDEX(B$3:K$8,1,COUNT(B$3:K$3)):INDEX(B$3:K$8,6,COUNT(B$3:K$3)),0)+1))))
数値が表示されますがシリアル値が表示されますので、それらのセル範囲についてはセルの表示形式から日付を選んで好みの日付表示にします。
次のB12セルには次の式を入力して下方jにドラッグコピーします。
=IF(ROW(A1)>MAX($B$3:$K$8),"",IF(MIN(INDEX($B$3:$K$8,MATCH(LOOKUP(10^10,$A$12:$A12),$A$3:$A$8,0),1):INDEX($B$3:$K$8,MATCH(LOOKUP(10^10,$A$12:$A12),$A$3:$A$8,0),10))>=ROW(A1), $B$1,INDEX($B$1:$K$1,MATCH(ROW(A1)-0.1,INDEX($B$3:$K$8,MATCH(LOOKUP(10^10,$A$12:$A12),$A$3:$A$8,0),1):INDEX($B$3:$K$8,MATCH(LOOKUP(10^10,$A$12:$A12),$A$3:$A$8,0),10),1)+1)))
会社名が表示されます。
C12セルには次の式を入力してE12セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。
=IF($B12="","",INDEX(INDIRECT(INDEX($B$2:$K$2,MATCH($B12,$B$1:$K$1,0))&"!A:E"),MATCH(LOOKUP(10^10,$A$12:$A12)*1000+COUNTIF(INDEX($B$12:$B12,MATCH(10^10,$A$12:$A12)):$B12,$B12),INDIRECT(INDEX($B$2:$K$2,MATCH($B12,$B$1:$K$1,0))&"!G:G"),0),IF(COLUMN(A1)<=2,COLUMN(A1),IF(COLUMN(A1)=3,5))))
納品日の列にはシリアル値が表示されますのでそれらの範囲を選択してからセルの表示形式で日付から好みの日付表示に変更します。
以上で作業は終了です。式が複雑で理解するのが容易ではないと思いますが、お示しした通りでここに示した式をそのままコピーして一度試験してみてください。
条件が複雑で大分苦労しましたが関数で処理する方法です。
関数で処理することでシートの数が多くなっても即座に対応できますし（ここでの例では１０シートまで）、データがそれぞれのシートに新たに追加された場合でも即座にお求めの表が自動的に変更されますので、常に最新のデータがお求めの表に表示されていることになります。マクロでは操作を行わなければ最新のデータの表示とはなりませんね。また、お求めの表では２カ月間の表示とは限らずに、例ですが、６カ月間までを表示させることもできるようになって...続きを読む
ウォッチ
