15日締めと末締めのお弁当個数の集計

質問

初心者です。
SHEET1に末締めの弁当個数表（例3/1～31）
SHEET2に15日当月締の弁当個数表（例2/16～3/15）
SHEET3に15日翌月締の弁当個数表（例3/16～4/15）
を作ってます。
SHEET2の3/1～3/15までの弁当の個数とSHEET3の3/16～3/31までの弁当の個数をSHEET1へリンクして表を作成したいのですが、毎月このファイルをコピーして使用したいと考えている為、分らなくなっています。
(1)SHEET2とSHEET3の月末日が変わってくるため、SHEET1へ連動できなくて困っています。（月が変わる度に計算式を設定するようになります。）
(2)SHEET2とSHEET3の弁当数を入力するのに同じ行数にする為、月末日と月初の日付の位置が合わせられないのです。
何かいい方法があれば教えてください。御願いします。

回答No.3

tom04 · ベストアンサー

こんばんは！
外していたらごめんなさい。
画像が小さいのでこちらで勝手に↓のように表を作ってみました。

Sheet2・Sheet3ともに作業用の列を使わせてもらっています。
両Sheetとも数式は同じです。
作業列E2セルに
=IF(MONTH(A2)=Sheet1!$A$1,ROW(A1),"")
としてオートフィルで下へずぃ～～～！っとコピーします。

Sheet1には表示したい月を入力するセルを設けます。
今回はA1セルに月を入力するとその月のデータが表示されるようにしています。
尚、日付セルはシリアル値が入力してあるものとします。

Sheet1のA3セルに
=IF(COUNT(Sheet2!$E$2:$E$100)>=ROW(A1),INDEX(Sheet2!A$2:A$100,SMALL(Sheet2!$E$2:$E$100,ROW(A1))),IF(COUNT(Sheet2!$E$2:$E$100,Sheet3!$E$2:$E$100)>=ROW(A1),INDEX(Sheet3!A$2:A$100,SMALL(Sheet3!$E$2:$E$100,ROW(A1)-COUNT(Sheet2!$E$2:$E$100))),""))
としれ列方向と行方向にオートフィルでコピーします。

B列以降はセルの書式設定から表示形式を「標準」にします。
数式を入れたセルが日付の表示形式になると思いますので、
そのままコピーすると全てのセルの表示形式が日付になってしまいます。

これで画像のような感じになります。
尚、数式はSheet2・3ともに100行目まで対応できるようにしていますが
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になればよいのですが
的外れなら読み流してくださいね。m(__)m

お礼

お返事遅くなりました。<(_ _)>
エクセルの知識はあまりないものですから、教えて頂いた計算式の意味を理解するのに時間がかかりました。
INDEX関数を使って、row関数の行カウントで日付を持ってくるということで理解できてますでしょうか?無事に作成完了です。

翌月に繰り越した時に前月分から当月分のデータを移し変える何かスムーズなデータの移行方法はご存知でしょうか?もしもいい案がありましたら教えていただければ幸いです。

ありがとうございました。

回答No.4

tom04

No．3です！
たびたびお邪魔します。

＞翌月に繰り越した時に前月分から当月分のデータを移し変える何かスムーズなデータの移行方法はご存知でしょうか?

とありましたので、再び顔を出しました。
確かに月が変わった段階で少し面倒になりますね！

そこで一つの案ですが、もう一つSheetをつくり入力用Sheetとして、
月日に関係なくデータを次々入力していくSheetを利用してはどうでしょうか？
そのSheetから1～3のSheetへ振り分ける方法です。
もう一度画像をアップさせてもらいます。少し小さくて見づらいかもしれませんが
方法だけ理解してもらえれば良いかな！って思います。
画像の右下の表が入力用のSheetになり、
左上がSheet1・右上がSheet2・左下がSheet3になります。
そして、Sheet1に年・月を入力するセル（A1・C1）を設け、そこに年と月を入力すればSheet1～Sheet3に振り分け表示が出来るようにしています。
「入力Sheet」に3列作業用の列を設けています。
作業列E3セルに
=IF(MONTH(A3)=Sheet1!$C$1,ROW(A1),"")
F3セルに
=IF(AND(A3>=DATE(Sheet1!$A$1,Sheet1!$C$1-1,16),A3<=DATE(Sheet1!$A$1,Sheet1!$C$1,15)),ROW(A1),"")
G3セルに
=IF(AND(A3>=DATE(Sheet1!$A$1,Sheet1!$C$1,16),A3<=DATE(Sheet1!$A$1,Sheet1!$C$1+1,15)),ROW(A1),"")
として、E3～G3セルを範囲指定しG3セルのフィルハンドルでずぃ～～～！っと下へコピーします。
当方使用のExcel2003の場合は最終行が　65536行目になりますのでそこまでコピーしても構いません。一気にというのは大変でしょうから
ある程度のところまでコピーしておいて、その後数式が入っている最終行からコピーを始めてもOKです。

まず、Sheet1のA3セルに
=IF(COUNT(入力Sheet!$E$3:$E$65536)<ROW(A1),"",INDEX(入力Sheet!A$3:A$65536,SMALL(入力Sheet!$E$3:$E$65536,ROW(A1))))
として、列方向と行方向にコピー
Sheet2のA3セルに
=IF(COUNT(入力Sheet!$F$3:$F$65536)<ROW(A1),"",INDEX(入力Sheet!A$3:A$65536,SMALL(入力Sheet!$F$3:$F$65536,ROW(A1))))
としてコピー
最後にSheet3のA3セルに
=IF(COUNT(入力Sheet!$G$3:$G$65536)<ROW(A1),"",INDEX(入力Sheet!A$3:A$65536,SMALL(入力Sheet!$G$3:$G$65536,ROW(A1))))
としてコピー

以上で画像のような感じになります。
尚、数式は最終行まで対応できるようにしています。

以上、あくまで一案ですので
的外れなら無視してくださいね。m(__)m

お礼

ついにできました!

今までは15日締めの表と末締めの表をそれぞれ別ファイルで持っておりまして、末締めの表へ15日締めのデータをリンクさせている状況だったのですが、これなら１つのファイルですべて管理できます。

本当にありがとうございました。<(_ _)>
エクセルってなんでもできるんですね。いろいろとやってみたいと思います。
また、機会がありましたら教えて下さい。本当にありがとうございました。

回答No.2

KURUMITO

図が小さくてわかりませんので次の例を参考にして作業を進めてください。
例えばシート2ではＡ2セルに日付とでも入力して3行目から下方にデータがあるとします。また、Ｂ3から下方には弁当の数が入力されるとします。
この形は他のシートでも同じとします。
そこでシート2のＡ3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(DATE(YEAR(TODAY()),MONTH(TODAY())-1,15+ROW(A1))>DATE(YEAR(TODAY()),MONTH(TODAY()),15),"",DATE(YEAR(TODAY()),MONTH(TODAY())-1,15+ROW(A1)))

同様にシート3ではＡ3セルに次の式を入力して下方にオートフィルドラッグします。

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),15+ROW(A1))>DATE(YEAR(TODAY()),MONTH(TODAY())+1,15),"",DATE(YEAR(TODAY()),MONTH(TODAY()),15+ROW(A1)))

シート1ではＡ3セルに次の式を入力して下方にオートフィルドラッグします。

=IF(DATE(YEAR(TODAY()),MONTH(TODAY()),ROW(A1))>EOMONTH(TODAY(),0),"",DATE(YEAR(TODAY()),MONTH(TODAY()),ROW(A1)))

シート1のＢ3セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A3="","",IF(ROW(A1)<=15,VLOOKUP(A3,Sheet2!A:B,2,0),VLOOKUP(A3,Sheet3!A:B,2,0)))

いずれのシートでもＡ列の書式表示は日付にします。なお、これらの日付は月が変われば自動的に変わりますので月が変わるたびに式を変更するなどの操作は必要ありません。

回答No.1

MackyNo1

添付画像が小さくて質問内容が把握できません。

＞(1)SHEET2とSHEET3の月末日が変わってくるため、SHEET1へ連動できなくて困っています。（月が変わる度に計算式を設定するようになります。）

現在どのような数式を入力していて、どうして数式を変更する必要があるのか、もう少し具体的に質問内容を例示してください。

＞(2)SHEET2とSHEET3の弁当数を入力するのに同じ行数にする為、月末日と月初の日付の位置が合わせられないのです。

この意味もよくわかりません。
最終的にどのようなレイアウトにしたいのかについて、これまでの問題点を含めて質問内容をもう少し具体的に提示してください。