エクセル関数【月別シートの内容を表示させる】

質問

エクセルで各月で分かれたシートに、情報が入っていて、そちらをひとつにまとめることは可能でしょうか？
情報が入っている部分は固定とします。

例：入力場所、各シートA1～A10まで。

1シート
A1　天気がいい
A2　天気がわるい
A3　雨だ
A4　晴れだ

2シート
A1　体調がわるい
A2　体調がいい

3シート
なし

4シート
A1　元気がいい

・・・12シート

合計シート
A1　天気がいい
A2　天気がわるい
A3　雨だ
A4　晴れだ
A5　体調がわるい
A6　体調がいい
A7　元気がいい
・・・

といった感じなのですが、やはりVBAでないと実装は難しいでしょうか？

回答No.6

kagakusuki · ベストアンサー

シート名に法則性が無い場合でも有効な方法です。
　今仮に、データを一つに纏めるためのシートが「統合」というシート名であるものとします。

　まず、適当な使用していないシート（ここでは仮に「補助」というシート名のシートとします）のA列に、

A1セルに　　Sheet1
A2セルに　　Sheet2
A3セルに　　Sheet3
A4セルに　　Sheet4
A5セルに　　Sheet5
A6セルに　　Sheet6
A7セルに　　Sheet7
A8セルに　　Sheet8
A9セルに　　Sheet9
A10セルに　　Sheet10
A11セルに　　Sheet11
A12セルに　　Sheet12

という具合に、1行目のセルから下に向かって順番に、纏めたいデータがあるシートのシート名を入力して下さい。
　次に、補助シートのB1セルに次の数式を入力して下さい。

=MAX(IF(ISNUMBER(MATCH(MAX(INDIRECT("'"&$A1&"'!A:A"))+1,INDIRECT("'"&$A1&"'!A:A"))),MATCH(MAX(INDIRECT("'"&$A1&"'!A:A"))+1,INDIRECT("'"&$A1&"'!A:A")),0),IF(ISNUMBER(MATCH("゛",INDIRECT("'"&$A1&"'!A:A"),-1)),MATCH("゛",INDIRECT("'"&$A1&"'!A:A"),-1),0))

　次に、補助シートのC1セルに

1

と入力して下さい。

　次に、補助シートのC2セルに次の数式を入力して下さい。

=IF($B2=0,"",SUM($B$1:$B1)+1)

　次に、補助シートのB1セルをコピーして、補助シートのB2セルに貼り付けて下さい。
　次に、補助シートのB2～C2の範囲をコピーして、補助シートのB3～C12の範囲に貼り付けて下さい。

　次に、統合シートのA1セルに次の数式を入力して下さい。

=IF(ROWS(補助!$F$1:$F1)>SUM(補助!$B:$B),"",IF(INDIRECT("'"&INDEX(補助!$A:$A,MATCH(ROWS(補助!$F$1:$F1),補助!$C:$C))&"'!R"&ROWS(補助!$F$1:$F1)-VLOOKUP(ROWS(補助!$F$1:$F1),補助!$C:$C,1)+1&"C"&COLUMN(Sheet1!$A$1),FALSE)="","",INDIRECT("'"&INDEX(補助!$A:$A,MATCH(ROWS(補助!$F$1:$F1),補助!$C:$C))&"'!R"&ROWS(補助!$F$1:$F1)-VLOOKUP(ROWS(補助!$F$1:$F1),補助!$C:$C,1)+1&"C"&COLUMN(Sheet1!$A$1),FALSE)))

　次に、統合シートのA1セルをコピーして、統合シートのA2以下に貼り付けて下さい。

　以上です。

　尚、この方法であれば、各元データのシートの中で、A列の最下段の行までにしか、他の列においてもデータが存在しない場合には、統合シートのA列をコピーして、統合シートの他の列に貼り付けるだけで、A列以外の列のデータも纏める事が出来ます。

お礼

ご回答ありがとう御座います！！
ばっちりできました！ひとつひとつの関数の意味はわかるのですが、多くなってくると理解が。。。
やっていけばなれるものでしょうか。。
取り急ぎ解決できましたので、ありがとう御座いました！！

回答No.7

KURUMITO

関数で行うには結構面倒ですね。
それぞれのシートには作業列を作って対応します。
仮にシートがSheet1からSheet12まであるとしまします。
Sheet1のB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",COUNTA(A$1:A1))

次にシート見出しでSheet2を選択してから、Shiftキーを押しながらSheet12をクリックしてSheet2からSheet12までを同じ作業グループにします。その上で、Sheet2のB1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(AND(A1="",ROW(A1)>1),"",MAX(INDIRECT("Sheet"&SUBSTITUTE(TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,10)),"Sheet","")-1&"!B:B"))+COUNTA(A$1:A1))

これでSheet2からSheet12までのB列には同じ式が入力されることになります。
そこでお望みのまとめのシートを別に用意してA1セルからK1セルにはSheet1からSheet12までの文字を入力します。
A2セルには次の式を入力してK2セルまで横にオートフィルドラッグします。

=IF(ISERROR(INDIRECT(A1&"!A1")),"",COUNTA(INDIRECT(A1&"!A:A")))

A3セルには次の式を入力してK3セルまでオートフィルドラッグします。

=IF(A2="","",SUM($A2:A2))

その上でお求めの表をA5セルから下方に表示させるとしたらA5セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>MAX($3:$3),"",INDEX(INDIRECT(INDEX($1:$1,IF(ROW(A1)<=SMALL($3:$3,1),MATCH(SMALL($3:$3,1),$3:$3,0),IF(COUNTIF($3:$3,ROW(A1)),MATCH(ROW(A1),$3:$3,0),MATCH(ROW(A1),$3:$3,1)+1)))&"!A:A"),MATCH(ROW(A1),INDIRECT(INDEX($1:$1,IF(ROW(A1)<=SMALL($3:$3,1),MATCH(SMALL($3:$3,1),$3:$3,0),IF(COUNTIF($3:$3,ROW(A1)),MATCH(ROW(A1),$3:$3,0),MATCH(ROW(A1),$3:$3,1)+1)))&"!B:B"),0)))

お礼

ご回答ありがとうございます！！
内容的にはデータがバラバラのときは合計の数と差分で入力するのでしょうか。。抽象的な質問ですみません。最後の関数のところで、入力した数式は正しくありませんとでました。
解決できそうですので、がんばってみます。
ありがとうございました！

回答No.5

hallo-2007

No2です。
＞私も同感です！ただ、すでに何年も続けられているファイルでフォーマットの変更が中々難しそうでして。。
実は、私の会社の中でも同じような事例は沢山あります。
エクセルに限らず、過去のやり方で改良を進めても限界にぶつかった時は、根本からやり直せです。
大きな壁にぶつかりますが、大きな結果と将来性が見えてきます。
頑張ってみてください。
ちなみに、これも私の感想ですが、過去に
ロータスとかエクセルの出始めなどスプレッドシートの時代に作成されたファイルを今まで引きづって
来ているケースも多いですね。
その時代に、花子とかアクセスなどデータベースからエクセルに発展してきた場合のケースがレアなのですが
拡張性があって良い結果を出しています。
例えば、月ごととかでシートやファイルが分かれると
月単位のグラフを作ってくれと頼まれたときは手作業だらけで大変ですよね。
せっかく入力したデータですので、長年の歴史をグラフにしたり、傾向や分散など解析まで使えるという所まで
利用するという認識まで深めて活用するとエクセルの利点が発揮できるのです。
今回の質問への直接の回答ではありませんが、エクセルも既に単なる表計算ソフトではない所まで進化している
ということを職場の皆さんで考えてほしいと思います。

お礼

いろいろとアドバイスありがとうございます。
こういった意見を聞くと自分もがんばってやらなくてはと思わされます！！
私にできる範囲でがんばってみます！で、やれる内容をもっと多くしていければと思っております。
貴重なご意見ありがとう御座いました！

回答No.4

keithin

それで？

＞ただ、入力されている内容はA1～A10までの中に記載されてあり、各シートによって、A1～A3までのシートもあれば、未入力のシートもあったりとバラバラです。
＞そのバラバラの情報をコピペではなく、自動的に別シートにだせればと思っておりました。

それはご質問に，最初からちゃんと書いてあることです。
それに対して，回答では「統合」を使う方法と，そのまま使えるマクロと２種類のやり方をお話ししました。

実際にご自分の手を動かして，回答で寄せられた色々な方法をやってみたのですか？
やってみて結果はどうだったのでしょうか？
今の様子じゃ「統合」の方はまず上手く行かなかったと思いますが，調べもせずましてや手を動かしてもいないでお返事だけ書かれても，フォローできませんよ？

お礼

いつもご回答ありがとうございます！
気分を害されたようで失礼いたしました。ご指摘ありました内容ですが、統合では難しかったですが、マクロについては、何も動きはなかってです。
私ごとですが、質問する際は一度調べてから、投稿しております。調べ方が足りないのかもしれません。。また、ご回答いただいて見ただけで理解できないものに関しては実践しております。

今後ともご回答頂けますと幸いです！
もっと自分で解決する力を身につけないとだめですね。。

回答No.3

MackyNo1

関数で対応するなら以下のような方法が簡単です。

まとめシートに「＝Sheet1!A1」と入力して10個オートフィルコピーし、その下のセルに同様に「＝Sheet2!A1」と入力し10個分のデータをコピーし、12枚のシート全部をリンク貼り付けします。
ちなみに、シート名が「1月」「2月」・・・のように規則性があれば、INDIRECT関数を使った1つの数式で表示することができます。

このように表示しておいたデータシートで、「データ」「フィルタ」「オートフィルタ」で▼をクリックして「オプション」から「0」「と等しくない」にすれば該当データだけが表示されています。

必要に応じてこのセル範囲を新規シートにコピーすればご希望のデータのシートが作成できます。

お礼

ご回答ありがとう御座います！
データを関数でひっぱてきてフィルタをかけるって事ですね。
INDIRECT参考になります！

回答No.2

hallo-2007

>情報が入っている部分は固定とします。
であるのであれば、根気強く

合計シート
A1　シート１！A1
A2　シート１！A2
A3　シート１！A3
A4　シート１！A4
A5　シート２！A1
A6　シート２！A2
A7　シート４！A1
と式を入れていきましょう。

基本は、最初からデータが日付と一緒に、一枚のシートにひたすら縦方向に入力していきます。
データのシート
　A　　B
日付　天気
・・・・
といった具合です。
別のシートに　年と月を入力したら、その月のデータの一覧表が出来る（月単位でデータ抽出）
の方がエクセルは得意です。
シートは　データと　抽出のシートの２枚でほとんどの場合、何年も続けられます。
月ごとにシートが増える。年ごとにファイルが増える。といった方法はやめた方が良いです。

補足

ご回答ありがとうございます！
私も同感です！ただ、すでに何年も続けられているファイルでフォーマットの変更が中々難しそうでして。。
非常に効率が悪いので、少しでもと思い質問させて頂きました。

回答No.1

keithin

＞合計シート
　：
＞といったカンジ

一体どういうカンジで「合計シート」になるのかさっぱり不明ですし，またご利用のエクセルのバージョンも不明ですが，もしかするとデータメニュー(データタブ)の統合などを使ってみると楽に出来ることかもしれません？

でもま，ご質問に書かれてるだけのことなら，マクロとか使っちゃった方が勿論簡単は簡単です。

やりかた：
シート１から１２まで用意する
ALT+F11を押す
現れた画面の挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
　dim w as worksheet
　dim i as long
　worksheets.add before:=worksheets(1)
　set w = activesheet

　for i = 2 to worksheets.count
　　worksheets(i).range("A1:A10").copy destination:=w.range("A65536").end(xlup).offset(1)
　next i
　w.range("A1") = "合計"
’または
’w.range("1:1").delete shift:=xlshiftup
end sub

ファイルメニューから終了してエクセルに戻る
ALT+F8を押してマクロを実行する。

＃ご質問に明記されていない前提条件：
各シートのA1から生データを記入してあるか，または何も記入していない。

補足

ご回答ありがとうございます！説明不足で失礼しました。
エクセルは2003です。
シート1～シート12までは生データになります。
ただ、各シートによって入力されている数が変わってくるので、どうしようかと思ってました。
ただ、入力されている内容はA1～A10までの中に記載されてあり、各シートによって、A1～A3までのシートもあれば、未入力のシートもあったりとバラバラです。
そのバラバラの情報をコピペではなく、自動的に別シートにだせればと思っておりました。