EXCEL ３Ｄ集計のシート名を参照したい

質問

シート名　４月　５月　６月・・・・
４月　には　４月のみの累計
５月　には　４～５月の累計
６月　には　４～６月の累計　をとる際、串刺し集計をすると出来ます。
シートを増やすたびに式のシート名を変更しなければいけません。
５月のシート　　SUM('４月:５月'!A2)　
６月のシート　　SUM('４月:６月'!A2)　
７月のシート　　SUM('４月:７月'!A2)　
この式のシート名を参照すれば良いと思うのですが・・・
CELL関数でシート名を取得する関数を
上記の式に入れるとエラーになってしまいます。

何か良い方法はありませんか？

ご回答お待ちしております。

回答No.5

Wendy02 · ベストアンサー

こんにちは。#3 です。

>GET.WORKBOOK(1)　　　　　　←シートのすべてを配列で出力
>GET.DOCUMENT(87)+NOW()*0　　←現在のシート番号を出力

Ver.4 マクロ関数と呼ばれているものです。
それぞれの使い方については、もう過去のものになってしまい、もう、英文にしか存在していません。

回答者の中には、いろいろな意見の持ち主もいるようですが、#4 さんのご意見は、私も、最もかなと、思いましたが、かと言って、毎月に付け足していく方法もないとは言えませんよね。

たとえば、シートの半分の

左側が、当月売上/利益で、右側の方には、累計の売上/利益
を入れるような場合があります。

そういう場合は、前月の累計から取ればよいのですが、前のシートを指定するというようなワザが必要になります。

それで、こういうものは、営業会議なんかで使われるもので、単純なマクロだと思っても、よほど自信がなければ、そう簡単にはできないですね。VBAは使いたくないというのは、もっともな心情だと思います。私なんかは、仮にマクロを使わないでも、表計算で計算したものは、もう一度、すべて電卓で検算していましたから。

>１２ケ月分作るにしても式を変更しなければいけません。

私個人の経験ですが、各月の合計の欄が、動いてしまうのですよね。だから、単純に、そのまま、年の集計シートにセルの番地を数式には写せない、ということのように思います。それで、私は、名前にして登録するという方法を取っていました。total_4, total_5, total_6..... という方法です。

それを集計シートには、

=total_4
=total_5
　・
　・
　・

というようにしていました。
面倒というよりも、間違いは絶対にいけないという前提ですから、何よりもまして表を複雑にさせないことが一番ですね。

お礼

ありがとうございました。
大変勉強になりました。
ＶＢＡを使わなくても色々出来るのですね。
まさしく、記載されているとおりの営業報告書の資料を作成するためのエクセル表です。

回答No.4

noname#79209

シートを増やしていこうとするから、混乱するのであって、
最初から12ヶ月分のシートと1年分のサマリを計算するシートを作った方が早いのでは？
サマリシート上に
　　　売上高　　　　累計
４月　
５月
６月
７月
　・
　・
　・
などとして、累計の部分にこのシート上での累計計算の式を入れれば良いのでは？

お礼

返信が遅くなりまて申し訳ございません。
早速のご回答ありがとうございます。
ごもっとも！です。
私もそう思います。
あるフォーマット上で使う式なのですが、（このフォーマットは変更したくない）１２ケ月分作るにしても式を変更しなければいけません。
それが、面倒らしくて・・・。
フォーマット例
（４月のシート）
当月分　　累積計
10 10
（５月のシート）
当月分　　累積計
5 15
（６月のシート）
当月分　　累積計
20 35
・
　　・
　　・
となるように　累積計の値に入れる式を（ＶＢＡは使わずにできれば関数で）教えていただきたいのです。
よろしくお願いします。

回答No.3

Wendy02

こんにちは。#1です。

シートの月の数では、厳密には取れないことを指摘されてしまいましたので、その代わりの式を書いておきます。

「前のシート」を参照する方法は、
（もちろん、手作業でも良いとは思いますが）
----------------------------------------------------
メニュー－[挿入]－[名前]－[定義]

名前(W)
「SHT」

参照範囲(R)
=GET.WORKBOOK(1)

名前(W)
「inx」

参照範囲(R)
=GET.DOCUMENT(87)+NOW()*0

// =INDIRECT(SUBSTITUTE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,3),"月","")-1&"月!A30")+A2 //

の代わりに

=INDIRECT(INDEX(sht,inx-1)&"!A30")+A2

で、前のシートのセルが取れます。
ただし、Ver. 2002以上で、マクロ－セキュリティ　が「高」にしてある場合は、働きません。なお、これらは、あくまでも、実験的なものです。Excel2003では稼動しています。

最初に、年間分のシートを一気に作るなら、手作業で作ってもよいと思います。それから、なお、VBAで処理するなら、シートインデックスを使う方法もありますね。

お礼

返信が遅くなり申し訳ございません。
さっそく試してみました。
期待どおりの答えができました。
ありがとうございます。
しかし、勉強不足で意味がよくわかりません。
GET.WORKBOOK(1)
GET.DOCUMENT(87)+NOW()*0
よろしければ、簡単に上記は何を意味するのか
教えていただけませんでしょうか。
よろしくお願いします。

回答No.2

nishi6

個人的な考えですが、このような問い合わせがあると、複雑な式を作るより、単純な式を１２個（１年分）作るように指示します。

月別の処理の場合、当月累計＝前月累計＋当月単月分　で計算できるように思いますが、４月から３月までの事業年度の場合、４月が最初ということで、４月は特別な算式になります。
また、１月累計の場合、前月は１２月となり、単純に『当月－１』月で該当シートを特定できません。

そのような理由で、単純な式を12個作るようにさせていますが、面倒な場合は、ユーザ定義関数を作っています。
このような場合にこそＶＢＡでしょうか。

A2セルを累計する場合は、=Ruikei(A2) とします。どのシートも同じ書き方です。

Function Ruikei(rg As Range)
　　Application.Volatile
　　Ruikei = Evaluate("=Sum(４月:" & rg.Parent.Name & "!" & rg.Address & ")")
End Function

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。出てきたコードウインドウに上記マクロをコピーして貼り付けます。
Application.Volatile で自動再計算関数にしています。CellsやActiveSheet.Nameを使ったりすると、うまく更新できなかったりするので、Parentを使い、Volatileで再計算させています。
なお、シートは４月、５月、６月・・・２月、３月と順に並んでいる前提です。（Ecxel2000です）

お礼

返信が遅くなりまして申し訳ございません。
さっそく試してみました。
感激！しました。
期待どおりの答えが返ってきました。
ありがとうございます。
・・・・・と喜んでいたのですが、
ＶＢＡは使いたくないという要望が出ました。
（ＶＢＡを使えない人が多い、引継ぎができない等の
理由から）
関数を使って（簡単に？）行う方法はありますでしょうか？

回答No.1

Wendy02

こんにちは。

串刺しのその集計の解決というのは、ワークシート関数にはないと思います。その解決というよりも、その作り方の問題で、古典的な方法があります。

たとえば、最初のシート（4月）の　A30 に、
=A2

と入れます。

次の
シートに

=INDIRECT(SUBSTITUTE(MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,3),"月","")-1&"月!A30")+A2

と入れます。

そして、残りのシートを選択して、作業グループにして、一括で、その式をF2 を押して、Enter を入れれば、残りのシートに同じ式が入ります。

必ず、前の累計に、アクティブなシートのA2を足すようにすればよいわけです。