シート名を自動取得し、自動的に合計値を求めたい

質問

シート名を自動的に取得し、
合計値を求めるにはどうすればいいでしょうか。

【現状関数】
=SUMIF(支出の部!C:C,B26,支出の部!J:J)

【仕様】
・「B26」はシート名を示しています。（変動的な値）
・「D26」は算出した支出の合計値が入っています。
・「C:C」は、項目Aを探し出し、「J：J」で項目Aの合計値を求めています。

【対応したいこと】
・現在、関数で固定（例；支出の部）されており、B26（支出の部）が変動した場合、
　値が取得できなくなってしまいます。

　例：「支出の部」　→　「テスト」に変更した場合、参照ができなくなる。

　これらを自動的に、B26からシート名を取得し、D26にその取得した値を
　入れるようにしたいです。

・取得対象となるシート名がない場合、「D26」セルに、「Error」と出力する。

よろしくお願いします。

回答No.9

bunjii · ベストアンサー

回答No.8の追加です。
月別シートのG3セルへ支出の部シートから2016年9月の旅費交通費の合計を求める数式を設定することにします。
月別シートの要件
A1に"支出の部"と入力されていること
F3に"旅費交通費"と入力されていること
設定シートの要件
月別シートのG2:R2の値を設定シートのAC2:AC13へ行列を入れ替えてコピペしてあること
AC列の集計月に対応する行のAE列に月の開始日を、AF列に終了日を入力すること
支出の部シートの要件
F列に出費の日付、G列に科目、J列に金額額が入力されていること
上記の条件で月別シートのG3セルへ次の数式を設定します。
=SUMIFS(INDIRECT($A$1&"!J:J"),INDIRECT($A$1&"!G:G"),$F3,INDIRECT($A$1&"!F:F"),">="&VLOOKUP(G$2,設定!$AC$2:$AF$13,3,0),INDIRECT($A$1&"!F:F"),"<="&VLOOKUP(G$2,設定!$AC$2:$AF$13,4,0))
G3セルを右へR3セルまでコピーすれば目的に合う集計になるでしょう。
SUMIFS関数の各引数の算出論理で理解できない点がありましたら解説の要請をしてください。

補足

ご教示頂きました内容で、思ったような動作をさせることができました。
ありがとうございました。
また何かありましたら、よろしくお願いします。

回答No.8

bunjii

>文章だと説明しづらいので、スクリーンショットを取りました。
今までに提示していただいた全ての数式とスクリーンショットのデータ配列は整合性が取れていないようです。
従って、数式は全面的に見直す必要があり、検証に時間が必要です。

回答No.7

bunjii

>設定sheetの関連性と、データシートの関連性が縦と横になっているので、正しく計算ができなかったため絶対関数にしていました。
設定シート、月別シート、集計シートがどのような構成になっているかを提示して頂けないので的確な判断ができない状態です。
当方で分かっていることは「支出の部」と言う名前のシートと別の名前のシートが有り、集計する元データになっていることです。
当初の質問では集計対象のシート名を集計結果のシートのB26に入力して集計対象のを元になるデータシートの選択が数式内で可変にする方法でした。
その後、回答への補足では「集計する期間を含めるようにしたい」との要望が出されましたが集計する対象の元データは提示してされていません。
従って、情報不足のため解決策を模索しても検証できずに憶測の回答にはなっています。
憶測で回答するのも限界がありますので元データ、設定データ、集計データの表がどのように構成され、模擬データでどのような値が配置されているかを提示してください。

>先にも記していますが、下記となています。
集計開始日と集計開始終了日の間に区切り文字がないのでExcelシートへコピペして検証に使うには不適切なものになっています。
2016/9/12016/09/30
2016/10/12016/10/31
2016/11/12016/11/30
　　　↓　空白（半角または全角）で区切る
2016/9/1 2016/09/30
2016/10/1 2016/10/31
2016/11/1 2016/11/30

>【データ取得sheet（月別sheet）】（横方向）
>G列3行目から9月のデータから
>R列3行目迄の8月のデータがあります。
>（9月～8月のデータ値）
模擬的な値を具体的に提示してください。

>F列1行目 testデータ1
>F列2行名 testデータ2
意味不明です。

補足

文章だと説明しづらいので、スクリーンショットを取りました。
画像の載せ方がわからなかったので、下記に格納しました。認証コードは12341234。zipパスワードは、YYYYMMDDの日付です。http://ux.getuploader.com/yagokoroomoikan/download/55/%E8%B3%AA%E5%95%8F.zip

回答No.6

bunjii

>これらを今度は、月別で取得できないかと対応をしているのですが、ある部分だけ取得ができずにいます。
集計対象のデータが提示されていないので数式の添削は無理です。

>【G１列の関数】
　=SUMIFS(INDIRECT($A$1&"!C:C"),INDIRECT($A$1&"!H:H"),$F3,INDIRECT($A$1&"!A:A"),">="&設定!$AE$2,INDIRECT($A$1&"!A:A"),"<="&設定!$AF$2)
>※9月、11月、2月、4月、6月、8月は取得ができない
>※そのほかの月は取得ができている。
設定シートのAE列とAF列は集計開始日と集計終了日でしょうか？
9月、11月、2月、4月、6月の集計終了日に誤りがあると思います。
8月が集計できない理由が分かりません。
集計終了日
9月31日はありません。
11月31日、2月31、4月31日、6月31日も同様です。
月末の日付を正しく入力しないと論理演算で正しい結果を得られません。
8月は31日がありますので集計対象の範囲にデータが有れば算出できるはずです。
尚、「G１列の関数」と言う表現は誤りかと思います。
「G1セルの数式」または「G列の数式」と表現しないと誤解を招きます。
また、G1セルとは集計結果のシート（設定シート、月別シートとは別シート）に有るのでしょうか？
設定シートのAE列（月初）とFA列（月末）の各月を参照するには数式を一部変更する必要がありそうです。
INDIRECT($A$1&"!A:A"),">="&設定!$AE$2
　　　　　　↓
INDIRECT($A$1&"!A:A"),">="&設定!$AE2

INDIRECT($A$1&"!A:A"),"<="&設定!$AF$2
　　　　　　↓
INDIRECT($A$1&"!A:A"),"<="&設定!$AF2
変更前の数式では数式を下へコピーしたとき$AE$2がそのまま変化せずに$AE$2のままになります。
変更すると数式を下へコピーすると$AE2が$AE3に変化します。
この機能を使わないのでしょうか？

補足

＞設定シートのAE列とAF列は集計開始日と集計終了日でしょうか？
＞9月、11月、2月、4月、6月の集計終了日に誤りがあると思います。
＞8月が集計できない理由が分かりません。
→日付の開始日と末日となります。
　末日がないため、正しく計算されていなかったようです。
　（数字内に入っていれば、取得ができるができるかと思っていました。）

現在、設定値では、固定値となっていますが、変更すると数式を下へコピーすると$AE2が$AE3に変化します。

＞尚、「G１列の関数」と言う表現は誤りかと思います。
＞ 「G1セルの数式」または「G列の数式」と表現しないと誤解を招きます。
→あまり正しくない表現でした。すみません。。

＞また、G1セルとは集計結果のシート（設定シート、月別シートとは別シート）に有るのでしょうか？
→上記のシートは、それぞれ別々のシートになります。

＞INDIRECT($A$1&"!A:A"),">="&設定!$AE$2
＞　　　　　　↓
＞ INDIRECT($A$1&"!A:A"),">="&設定!$AE2
＞
＞ INDIRECT($A$1&"!A:A"),"<="&設定!$AF$2
＞　　　　　　↓
＞ INDIRECT($A$1&"!A:A"),"<="&設定!$AF2
＞変更前の数式では数式を下へコピーしたとき$AE$2がそのまま変化せずに$AE$2のままになります。
＞ 変更すると数式を下へコピーすると$AE2が$AE3に変化します。
＞この機能を使わないのでしょうか？
→上記の設定をすることで、月初と月末の間の集計ができました。

確かにこの方法だと、下にペーストすれば、
集計はできます。

設定sheetの関連性と、データシートの関連性が
縦と横になっているので、正しく計算ができなかったため
絶対関数にしていました。

設定sheetの値では、縦方向に
先にも記していますが、下記となています。

【設定sheet】（縦方向）
AE　　　　　　AF
（1行目から）
2016/9/1 2016/09/30
2016/10/1 2016/10/31
2016/11/1 2016/11/30
2016/12/1 2016/12/31
2017/1/1 2017/01/31
2017/2/1 2017/02/28
2017/3/1 2017/03/31
2017/4/1 2017/04/30
2017/5/1 2017/05/31
2017/6/1 2017/06/30
2017/7/1 2017/07/31
2017/8/1 2017/08/31

【データ取得sheet（月別sheet）】（横方向）
G列3行目から9月のデータから
R列3行目迄の8月のデータがあります。
（9月～8月のデータ値）
F列1行目 testデータ1
F列2行名 testデータ2

月が変わった時にずれてしまいます。

他に方法はないでしょうか。

回答No.5

bunjii

>下記のように設定したのですが、取得されませんでした。
提示の数式からシート名「設定」と「支出の部」を作成して次のようにもぎでーたを入力すると正しい集計ができます。
シート名：設定
A4="開始"
A5="終了"
B4=2016/9/1
B5=2017/8/31
A15="支出の部"
B25=”交通費”
シート名：支出の部
A列=[科目]　・・・・B25で集計する科目名の実例が必要です。
B列=[金額]
D列=[日付]

>どこか間違っていますでしょうか。
条件1の INDIRECT($A$15&"!A:A"),$B25 に誤りがあります。
科目と$B25の関係を見直してみることをお薦めします。

補足

回答ありがとうございます。
ご教示頂きました通り、試行錯誤したら、取得ができました。

これらを今度は、月別で取得できないかと対応をしているのですが、
ある部分だけ取得ができずにいます。

設定sheet
AE AF
1 2016/9/1 2016/9/31
2 2016/10/1 2016/10/31
3 2016/11/1 2016/11/31
4 2016/12/1 2016/12/31
5 2017/1/1 2017/1/31
6 2017/2/1 2017/2/31
7 2017/3/1 2017/3/31
8 2017/4/1 2017/4/31
9 2017/5/1 2017/5/31
10 2017/6/1 2017/6/31
11 2017/7/1 2017/7/31
12 2017/8/1 2017/8/31

月別sheet
G～R列
9月～8月

A列
2016/09/20
2016/10/28
2016/11/28
2016/12/28
2017/01/28
2017/02/28
2017/03/28

【G１列の関数】
=SUMIFS(INDIRECT($A$1&"!C:C"),INDIRECT($A$1&"!H:H"),$F3,INDIRECT($A$1&"!A:A"),">="&設定!$AE$2,INDIRECT($A$1&"!A:A"),"<="&設定!$AF$2)

※9月、11月、2月、4月、6月、8月は取得ができない
※そのほかの月は取得ができている。

この結果から、関数から取得で来ていると思うので、
ある部分だけ取得できないのがいまいちわかりません。

原因は何でしょうか。

回答No.4

bunjii

>本関数で可変値から検索をかけ、ある期間内に存在する値のみ取り出し、算出するにはどうすればいいでしょうか。
SUMIFS関数を使えば複数条件に合致したセルを対象に集計できます。
A列に日付が入力されているものとすれば次のようになります。
=SUMIF(INDIRECT(B26&"!C:C"),B26,INDIRECT(B26&"!J:J"))
　　　　　↓
=SUMIFS(INDIRECT(B26&"!J:J"),INDIRECT(B26&"!C:C"),B26,INDIRECT(B26&"!A:A"),">="&B4,INDIRECT(B26&"!A:A"),"<="&B5)

補足

下記のように設定したのですが、取得されませんでした。
どこか間違っていますでしょうか。

=SUMIFS(INDIRECT($A$15&"!B:B"),INDIRECT($A$15&"!A:A"),$B25,INDIRECT($A$15&"!D:D"),">="&設定!$B$4,INDIRECT($A$15&"!D:D"),"<="&設定!$B$5)

※設定個所
$A$15&　＝　支出の部
!A:A　　＝科目
!B:B　　＝金額
!D:D　　＝日付
&設定!$B$4　＝開始日付
&設定!$B$5　＝終了日付

回答No.3

bunjii

「支出の部!C:C」と「支出の部!J:J」をINDIRECT関数で可変にすれば良いでしょう。
B26="支出の部"であるとのことなので次のように修正してみることをお薦めします。
=SUMIF(支出の部!C:C,B26,支出の部!J:J)
　　　　　↓
=SUMIF(INDIRECT(B26&"!C:C"),B26,INDIRECT(B26&"!J:J"))

補足

bunjii さん
ご教示ありがとうございます。
早速、確認しましたところ、上記の関数で思うような動作をさせることができました。
ありがとうございます。

また追加で教えてほしいのですが、
本関数で可変値から検索をかけ、ある期間内に存在する値のみ取り出し、
算出するにはどうすればいいでしょうか。

■やりたいこと
・下記の期間内に存在するデータのみ計算したい。

シート名：設定
A4 開始
B4 2016.9.1
A5 終了
B5 2017.8.31

よろしくお願いします。

回答No.2

Nouble

未だ、少し
質問させて、ください

先程と、同様
テスト用に
シートを、二枚
作り

片方に
他方へ、参照させる
=SUMIF(●●!C:C,B26,●●!J:J)
(※注：●●は、テスト用の　参照先Sheet名)
と、入れ
参照先Sheet名を、変える

Excelを
一度、終了させ
新たな、ブックを
開き
新の、シートを
二枚、用意する

片方に
他方へ、参照させる
=SUMIF(●●!C:C,B26,●●!J:J)
(※注：●●は、テスト用の　参照先Sheet名)
と、入れ
参照先Sheet名を、変える

此等、でも
追随性が、あるか
確認、頂ければ
有り難い

お願い、できますか？

因みに
セルが、存在する
Sheetの
Sheet名が、ない
のは、
割と、有り得ない
かも、知れません

ただ、
cell関数を、使用時に
ブックが
未保存の、場合は
エラーが、出ます
ので
此への、対応は
可能です

また、
Sheet名が、変わった
其の、場合に
エラー
と、出させる事も
可能です
(※注：此は、ベット　ご用命、ください)

恐らく、Sheet名は
=RIGHT(CELL("FILENAME",Sheet2!A1),LEN(CELL("FILENAME",Sheet2!A1))-FIND("]",CELL("FILENAME",Sheet2!A1)))
こんな、感じで
取得して、いる
もの
と、思われます

此の、式が
エラーを
示した、時に
ご指定、文言を
表す
の、ですから
上式を、IFERRORで
包んで、やれば
良い
=IFERROR(RIGHT(CELL("FILENAME",Sheet2!A1),LEN(CELL("FILENAME",Sheet2!A1))-FIND("]",CELL("FILENAME",Sheet2!A1))),"エラー")
ですね

回答No.1

Nouble

普通
ユーザー関数や、名前定義、
等、では
無い、場合

文字列、指定
されて、いない
Sheet名は
変更に、追随します

其処で
お伺い、します

一、
Excelの、バージョンは
幾つ、ですか？

二、
業務用途以外
と、して
其の、環境で
シートを
二枚、作り
其の、片方から
他方に、対する
参照式を、入れ
参照される、側の
Sheet名を、変える

参照が、壊れるか
其れとも
Sheet名が、追随
するか
見て、頂けますか？

あと、
先にも、述べましたが
名前管理や、ユーザー関数、
等は、

Sheet名、変更に
追随、しない
かも、知れません

補足

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

(1)使用しているバージョンは、Excel2013です。

(2)テスト用にシートを作成し、参照先のシート名を変更すると、
　参照元の関数は追随して、関数中のシート名が変わりました。

　テストA　→　テストB
　に変化。

よろしくお願いします。