[[20130830131421]]　『INDIRECT関数』（木葉）　ページの最後に飛ぶ

『INDIRECT関数』（木葉）

こんにちは。毎度お世話になっております。

今回INDIRECT関数について、お教えいただきたいのですが、

●例えば、
=SUM(A1:A2)
をINDIRECT関数を使うと、
=SUM(INDIRECT("A1:A2"))
と入力したら、同じ意味の式になりますよね？

●ということは、
=SUM(Sheet1:Sheet2!A1)
の場合、
=SUM(INDIRECT("Sheet1:Sheet2!A1"))
と入力したらいいのかと思ったのですが、

「＃ＲＥＦ！」のエラーが出ます。

実際に使う式は
=SUM(INDIRECT("Sheet1:Sheet2!A1"))
をベースにした式なのですが、

エラーの原因を探ってみたところ、上の他には見当たらないのです。
エラーの出ない式にするには、どう入力したらよいのか、
もしよろしければ、なぜエラーになるのか教えていただきたいです

よろしくお願いします。

 =SUM(N(INDIRECT("Sheet"&{1,2}&"!A1")))

 ※INDIRECT関数は串刺し処理はできない。
   （単一セル参照と連続セル範囲参照（飛び飛びでなく）しかできない）

 対応
 1 各シートのA1セルを個別にINDIRECTで拾う（配列処理）。
 2 セル参照の配列は処理できないので数値データ配列に置換（N関数）
 3 最後に合計

 (GobGob)

 あと、補足。

 =SUM(Sheet1:Sheet2!A1) は処理できるけど
 =Sheet1:Sheet2!A1 は #REF!エラー。

 INDIRECTは「セル参照」の数式処理をしてるだけであって
 けっしてSUMの引数を処理しているわけではない。

 ってことでない？

 串刺し処理は関数側でできる。できないがあるんだろうね。

 (GobGob)

GobGob様

ご回答ありがとうございます！
なるほど・・・

 >>=SUM(N(INDIRECT("Sheet"&{1,2}&"!A1")))

せっかく書いていただいた上式を利用しようと思ったのですが、ちょっと問題点が・・・

シートの量が結構多く、{}内の数字、変動する場合があるのです。
変動というのはシート1では1から2でも、シート2では1から30だったり、シート３では5から15だったり、、シート4では102から200だったり・・・という意味です。
ただ、飛び飛びで5と、15と16。というのはないです。

言語力なくてごめんなさい・・・
わからない箇所があったら遠慮なくどうぞ・・

（木葉）

GobGob様

　>>補足
ふむ・・・なるほどです・・・

勉強になります！
関数でなにかしようとするたび引っかかってしまうのは便利になるための代償なのか何なのか・・・

おっと、ごめんなさい

（木葉）

 たとえば、Sheet1～Sheet10として

 =SUMPRODUCT(N(INDIRECT("Sheet"&ROW(1:10)&"!A1")))

 ※ 5～15 なら =ROW(5:15) とか。
    ROW関数の引数を変更

 (GobGob)

GobGob様

 >>ROW関数の引数を変更

ありがとうございます！今一度組み立てなおしてみます！
ちょっと複雑（+私の低能さも原因）なので、時間がかかってしまいますが・・・

また分からなくなったら、お付き合いいただけたら嬉しいです！

本当に、ありがとうございました＾＾

（木葉）

問題発生です・・・。

ROW(1:10)
の部分なのですが、
Row関数の中には関数は入れることができませんよね･･・

このシート「1」や、「2」っていうのは、
シート番号ではなくて、中の内容の日付と関係していまして、

8月3日だったらシート3、8月23日だったらシート23･･･
という構成なのです。

そこで、ROW内の数字は、日付（E2に入力されています）を参照させたいのですが、
代わりになる関数もいまいち分からず･･･

私の説明不足が発揮されてしまい･･

何度もごめんなさい

再度教えていただけないでしょうか？

（木葉）

 1～E2セル。
 ってこと？

 ROW(A1:INDEX(A:A,E2))

 (GobGob)

GobGob様

返信遅くなり申し訳ないです

なるほど、ＩＮＤＥＸを使えばいいのですね！
完成しました！ありがとうございます！

（木葉）

 もういっちょ補足ね。

 =SUM(Sheet1:Sheet2!A1) は SUM関数の機能として串刺し合計ができる。
 ※ MAX関数とかもそうだね。

 今回の場合は

 =SUM(INDIRECT("Sheet1:Sheet2!A1"))でエラーとなる。

 これは関数のネストの問題で、まず INDIRECTを処理してからSUMを処理するんだけど
 INDIRECTの結果が串刺し参照しようとしてエラーとなる。
 (先に例示したように串刺しセル参照はエラーとなるから）

 んで、SUM関数を処理するんだけど、引数が先に「エラー」を返しているんで結果は当然エラーとなる。

 処理の順 … =関数２(関数１) → 関数２（エラー） → エラー。

 SUMとかMAXとかAVERAGEとかは特別に引数に串刺し参照を指定できるんだろね。
 けど、その引数をINDIRECTで処理しちゃうと串刺し指定できない関数なので
 SUM処理する前に先にエラーとなっちゃうんでNGになってるんだと思う。

 ※あくまで憶測だけど。

 (GobGob)

 補足
 3D参照(串刺し参照)に対応している関数はヘルプによると以下の通り。

 SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, VARPA

 これは2007でも追加はないようだ。
 2010以降で追加があるかどうかは未確認。

 (ねむねむ)

GobGob様

ねむねむ様

解説ありがとうございます！

なるほど･･･関数も奥が深いというかなんというか･･･
多量のシートを使うのは初めてだったので、度々問題に衝突しましたが、
なんとか完成できて良かったです！

串刺し参照のことも初めて知ったので、また一つ賢くなったような気分です

なぜ串刺し参照は全ての関数に対応していないのだろう･･･？というのが新たな疑問ですね･･･

お二方とも、ありがとうございます＾＾

（木葉）

sheet1 sheet2 sheet3 sheet4 sheet5
sum(sheet1:sheet5!A2)
sheet1～sheet5の合計

sheet1 sheet2 sheet3 sheet5 sheet4

 sum(sheet1:sheet5!A2) 
sheet1	  sheet2	  sheet3	 sheet5 の合計

sheet1:sheet5に囲まれたシート対称
シート名に数値がなくても指定したシートの間で計算されます

シート名をずらすことで簡単に範囲をかえられますよ

sheet1 sheet2 sheet3 sheet4 あさひ　 sheet5
シート増設でも簡単に

！！！

コメント返信：

[ 一覧(最新更新順) ]

[[20130830131421]] 『INDIRECT関数』（木葉） ページの最後に飛ぶ

GobGob様

ねむねむ様

[[20130830131421]]　『INDIRECT関数』（木葉）　ページの最後に飛ぶ