複数のシートにある帳票。新たな集計シートにおいて帳票シートの月末の数値を参照、合計したい。

Windows7、エクセル2002です。

エクセルには串刺し計算機能、というのがあります。
ま、機能、というよりも合計関数をシートを超越して使用し、異なるシートの同じ行、列の
セルの合計値を求める関数です。
これは知っていますが、今回はこれを使っても計算できないのでお知恵を求めます。

あるエクセルファイルに複数のシートがあり、銀行通帳の内容が入力されています。

たとえば以下のような感じです。
シート名＝みずほ
日付列 摘要列 払出列 預り列　残高列
2018/01/01 仕入 10000 90000
2018/02/01 仕入 10000 80000
2018/03/01 仕入 10000 70000


シート名＝UFJ
日付列 摘要列 払出列 預り列　残高列
2018/01/21 売上 50000 50000
2018/02/21 売上 50000 100000
2018/03/21 売上 30000 130000

シート名＝SMBC
日付列 摘要列 払出列 預り列　残高列
2018/01/25 給与 1000000 9000000
2018/02/25 給与 1000000 8000000
2018/03/25 給与 1000000 7000000
2018/03/30 借入 3000000 10000000


ただし、上記のものは一例です。取引内容によって一日のうちに明細行が何行も発生する
場合もあれば、1ヶ月、まったく金の移動がない場合もあえます。


さて、このエクセルファイルに集計用のシートを追加するとします。
（とりあえずシート名を「集計」とします）
このシートに、各シート（みずほ、UFJ、SMBC）の月末時点の残高を合計して算出、表示したいと思います。
以下のような感じです

シート名＝集計
日付列 銀行1列 残高1列 銀行2列 残高2列 銀行3列 残高3列 合計列
2018/01/31 みずほ 90000 UFJ 50000 SMBC 9000000 9140000
2018/02/28 みずほ 80000 UFJ 100000 SMBC 8000000 8101000
2018/03/31 みずほ 70000 UFJ 130000 SMBC 10000000 10200000

前述のとおり、串刺し計算は使えません。
なぜなら「明細行が何行あるか？」は銀行ごとにことなるので、毎月の末日が同じ行番号に
なるとは限りません。
　また「月末には必ず取引がある」とは限りませんので「末日の日付で検索する」ということもできません。
（同様の理由で、「月初の日付で検索する」こともできません）

さて、このようなことをしたい場合、シート「集計」にどのようにして各シートのデータを
参照してくればいいのでしょうか？

もしも
「銀行シートの記載例の体裁、レイアウトでは希望通りの方法は無理だ。
しかし銀行シートの体裁、レイアウトを少し変更するだけで希望通りになるよ」
という場合はそのやり方を教えてください。
ただし、レイアウトの変更は最小限にしてください。
”明細のもっとも多い銀行シートにあわせて、他の銀行シートに無用な空白行を作る”
など、入力やシートの体裁変更のために手間が多数かかる方式、ではちょっと困ります。


詳しい方、よろしくお願いします。

No.4 回答者： ママチャリ 回答日時： 2018/08/05 08:47

「試してみます」とのことですが、もう解決しましたか？

とりあえず、ヒントになればと思い書きましたが、解決しているようであれば無視してください（大した内容ではないので・・・）
日付って、日時のシリアル値で時刻は0:00ですよね？また、当然、昇順に並んでいるのですよね？であれば、添付画像のような式で月末の残高が取得できると思います。ポイントは、取得の基準となる月末日に時刻（0:00より大きい数字）を設定することです。

No.3 回答者： tatsu99 回答日時： 2018/07/31 10:48

No2です。

私は「マクロでよいならマクロ提供が可能です」の意味で書きました。
従って、あなたからの回答は、
１）マクロは不要です。
２）マクロで構いません。以下、補足します。・・・・・・
のどちらかを想定していました。

あなたからの回答の「試してみます」とは、１）２）のどちらの意味でしょうか。

この回答へのお礼

ご回答ありがとうございます。

私は質問文章に
>入力やシートの体裁変更のために手間が多数かかる方式、ではちょっと困ります。

と記載しました。
ましてやマクロ使用は範疇外です。

よって回答の主旨は
1)です。

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

お礼日時：2018/07/31 11:04

No.2 回答者： tatsu99 回答日時： 2018/07/30 16:46

マクロでなら可能です。

マクロでOKなら以下の補足要求の補足をお願いします。（マクロ不可なら補足不要です）
①各シートは、全て同じフォーマットですか。
②各シートの１行目は見出し行ですか。
③各シートの日付順にデータは記載されていますか。
以下のように日付が逆転することはないと考えて良いですか。
シート名＝SMBC
日付列 摘要列 払出列 預り列　残高列
2018/02/25 給与 1000000 8000000
2018/01/25 給与 1000000 9000000
④各シートの残高はE列ですか。
⑤集計シートに各シートを並べるとき、例では、みずほ、UFJ、SMBCとなっていますが、
この順番は、シートの左側から順に並べた名前にしますが、それでよいですか。
⑥来年になったら、2019年の集計をしたいと考えるはずですが、その場合も、このマクロを使えることを
あなたは望んでいると思われます。もし、そうであれば、
集計シートのB1に集計対象の年を記入し、その年を集計すれば良いと考えますがいかがでしょうか。
（添付図参照　黄色の部分）
B1で指定された年のみ集計します。
但し、指定された年以前のデータがあれば、それを引き継ぎます。
例
2017/12/21 の残高　5000
2018/3/1 の残高　3000
上記のデータがあった場合、
2018/1/31 の残高=5000
2018/2/28 の残高=5000
となります。
⑦集計シートは、一番右側に置くことにしたいのですが宜しいでしょうか。
添付図では、一番右側に置いた例です。
もし、ブック内に、集計シート、各銀行のシートの他になにか別の用途のシートが追加されるなら
銀行シート１、銀行シート２、・・・、銀行シート最後、集計シート、別シート１、別シート２
のような配置になります。（別シート１、２は、なくても問題ありません）
この案の場合、集計シートの左側を全て集計対象とします。
集計シートの右側のシートは集計対象としません。

この回答へのお礼

ご回答ありがとうございます。
試してみます。

お礼日時：2018/07/30 19:51

No.1 回答者： mike_g 回答日時： 2018/07/30 15:54

》 …ではちょっと困ります。

何と贅沢なッ！(^_^)

添付図参照
集計!B2: =INDEX(INDIRECT(B$1&"!E$1:E$1000"),MATCH($A2,INDIRECT(B$1&"!A$1:A$1000")))
集計!E2: =SUM(B2:D2)

この回答へのお礼

ご回答ありがとうございます。
試してみます。

お礼日時：2018/07/30 19:51