またＩＮＤＥＸについてです

質問

また失礼します、
ＩＮＤＥＸです。
なんか変です。

Ｅｘｃｅｌ２００３でINDEX(範囲,行番号,列番号,領域番号)
の構文を使おうと思ったのですが、

=INDEX(('Sheet1:Sheet3'!C7:D8),0,0,0)
は、おろか
=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),0,0,ROW(A1:A3))
に、始まらず
=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),0,0,1)
どころか
=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),1,1,1)
すらも駄目で
数式バー内でＰＤ９をしても#VALUE!になります。
勿論通常のセル表示上でも#VALUE!です。

ＨＥＬＰ上では
=INDEX((A1:C6,A8:C11),2,2,2)
と、云う記載が挙げられているので

=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),1,1,1)
位は行けて欲しいところなのですが…

領域が　多シートにまたがっては駄目なのでしょうか？　ね～…

２００７かも知れませんが、
昔多シート参照
克服した覚えがあるのですが、…

困りました。

ご教示をお願いします。

因みに
=INDIRECT("Sheet1:Sheet3!$C$7",TRUE)は#REF!
=OFFSET(Sheet1:Sheet3!C7,1,1,1,1)は#VALUE!
になりました
(※注：数式バー内でのＰＦ９でも　です)

回答No.6

cj_mover · ベストアンサー

＃３、５、ｃｊです。
お礼欄へのレスです。

> 例えばVBA的に云うならば
> With ThisWorkBook
> 　　Set r = .Sheets(Array(1 to 3)).Range("C5:E7")
> End With
> 又は、
> 　　Set r = Array( _
> 　　　　　　　　　Sheets(1).Range("C5:E7"), _
> 　　　　　　　　　Sheets(2).Range("C5:E7"), _
> 　　　　　　　　　Sheets(3).Range("C5:E7"), _
> 　　　　　　　　 )
> なのです☆が…

こういう書き方だと、如何にも成立している、動く、コードのように見えますから、
実際に試してしまう人もいると思いますが、
あり得ないですから、ご注意ください。
少し批判しておきますね。
貴方も回答者としてスレに参加することもあるのですから、
誤解を招く言動にはご注意を。
でもまぁ、言いたいこと、求めていることは理解できます。
VBAでも、Rangeオブジェクトにシートを跨ぐ範囲をSetすることはできません。
やるなら、Range型の配列変数に要素毎にSetするとか、
Collectionオブジェクトでシート毎にセル範囲をAddするとか、
ですけど、それでも
纏めて参照したり、関数やメソッドの引数やオブジェクトに指定したりできる訳ではありません。
ないものはない、と諦めるしかないです。
可能性あるとすれば、UDFやサブクラスということになりますが、
これも具体的なニーズに照らしてみないと何とも言えません。

> =CHOOSE(ROW(A1:A2),データ上期!C11:C16,データ下期!B11:B16)
> =index(CHOOSE(ROW(A1:A2),データ上期!C11:C16,データ下期!B11:B16),,)
> =CHOOSE(INDEX(ROW(A1:A2),,),データ上期!C11:C16,データ下期!B11:B16)
> =CHOOSE(INDEX(ROW(A1:A2),,),INDEX(データ上期!C11:C16,,),INDEX(データ下期!B11:B16,,))
> はNG…
そりゃあ、
ひとつひとつのニーズに
ひとつひとつの各論として応えることは出来るでしょうけれど、
この件については、レスは控えます。

> 因みに今回は
> QNo.8072926のla-life様回答向け配列数式用です。
それ、因み、じゃなくて、大前提、ですよ。
スレッドをＬｅａｄするのは質問者さんの役割ですから、
後から、そういうの出すのは心証を損ないかねませんので、
次からは最初に書くようにしましょう。

お礼

有り難うございます。

やはり駄目なのですね。

回答No.5

cj_mover

＃２、ｃｊです。

一応、補足しておきますね。
例えば、A1:C1に以下の数式を貼ります。
=SUM(INDEX(CHOOSE(COLUMN(),Sheet1!$C$7:$D$8,Sheet2!$C$7:$D$8,Sheet3!$C$7:$D$8),0,0))
これは喩えであって、現実にはINDEX関数を使う必要がありませんけれども、
INDEX関数が配列を返していることは確認できると思います。

おそらく質問者さんはVBAのEvaluateメソッドで扱う配列について調べているのだと思います。
いわゆる普通の配列数式は、一つのセルにひとつの値を返すもの、だとすると、
今問題になっているINDEX関数で配列を返す、というのは、
複数のセルを選択した状態で数式をCtrl+Shift+Enterで確定するタイプの
つまり配列を返す配列数式の話であろうとの認識で回答しています。

ワークシート関数の引数に、セル範囲を渡した時に、
その引数を、セル範囲として受けるか、配列として受けるか、
は、受け取る側の関数が決めること、ということだけ、念を押しておきます。
SUM関数のように至れり尽くせりで超Variantな関数こそレアな存在で、
多くの関数は、セル範囲専門、または、配列専門、ということなのです。
もしここに書いてあることの意味が解らないようでしたら、
SUM関数の仕様を調べるとか、SUM関数をUDFで自作してみるとか、
SUM関数をUDFで再現した人の話を聞く（読む）とか、してみるといいと思います。

もう一つ念を押しておきますが、
(A1:C6,A8:C11)のような書き方は
ひとつのセル参照です。
ひとつのセル参照の中に複数の領域が含まれています。
paramArrayで複数の引数を列挙しているのとは全くの別物です。
"A1:C6,A8:C11"で、ひとつのセル参照、ひとつのセル範囲、
なのであって、ふたつの範囲をくっつけている訳ではありません。

ワークシート関数についても、
オブジェクトを扱っているのか、非オブジェクトを扱っているのか
区別しないと、今回のような誤解が生じてしまうということなのかも。

以上です。

お礼

有り難うございます。
VBAを知ってまた伸びれた気がします。

にしても
ParamArray
これも面白いですね、
初めて見ました。
感謝します。

因みに今回は
QNo.8072926のla-life様回答向け配列数式用です。

多シート間にまたがる範囲の選択的取得
これがテーマです。

回答No.4

MackyNo1

＞=INDEX(('Sheet1:Sheet3'!C7:D8),0,0,0)
は、おろか

SUM、AVERAGE、COUNT関数などの一部の関数を除けば、ＩＮＤＥＸ関数を含めて多くの関数の参照範囲に串刺し集計の範囲を指定することはできません。

=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),0,0,ROW(A1:A3))
に、始まらず

そもそもＩＮＤＥＸ関数やＯＦＦＳＥＴ関数は（どちらかというと珍しい方のケースですが）、第二引数以下の部分を複数選択しても配列数式と認識されません(配列を返しません）。

＞=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),0,0,1)
どころか
=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),1,1,1)
すらも駄目で
数式バー内でＰＤ９をしても#VALUE!になります。
勿論通常のセル表示上でも#VALUE!です。

そもそも「（」と「）」で複数の範囲をくくって1つの参照範囲のようにできる関数は限定されています。
例えばＳＵＭ関数で複数の範囲をカンマでつないでカッコでくくると1つの範囲として認識しますので、2003までのバージョンでは引数の制限以上の範囲を指定することができます。
また、例外的にＲＡＮK関数ではカッコでくくった複数の範囲を1つの範囲のように取り扱うことができますが一般的な使い方ではありません。

＞ＨＥＬＰ上では
=INDEX((A1:C6,A8:C11),2,2,2)
と、云う記載が挙げられているので
=INDEX((Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),1,1,1)
位は行けて欲しいところなのですが…

上記のカッコでくくって複数の範囲をカンマでつなぐという特殊なケースの場合（ＳＵＭ関数やＲＡＮＫ関数の場合でも）、同じシート以外の範囲は1つの参照範囲として認識できない仕様になっていますので、ご質問のような状況が発生します。

いずれにしろ、配列数式で対応する場合でも関数によって配列が利用できるものもあれば、配列として認識されないものもあるわけです。
さらに参照範囲についてもセル範囲同士を文字列結合した配列を数式の「参照範囲」にできる関数（例：ＭＡＴＣＨ関数）もあれば、できない関数（ＣＯUNTIF関数など多くの関数）もありますので、基本的には試行錯誤してご自分で利用できる範囲を調べて、対応するしかありません。

お礼

有り難うございます。

回答No.3

cj_mover

こんにちは。お邪魔します。
＃１さんのご回答で答えになっていると私は思っていますが、
納得されていないようなので、、、。

> 領域が　多シートにまたがっては駄目なのでしょうか？
はい、駄目なのです。

> =INDEX(('Sheet1:Sheet3'!C7:D8),0,0,0)
この構文の第一引数は、セル範囲または二次元の値配列ですが、
ひとつのセル範囲またはひとつの二次元の値配列です。
SUM関数の串刺し合計のように二段階配列は指定できません。
'Sheet1:Sheet3'!C7:D8
これはセル範囲への参照ではなくて、
二段階配列（要素が二次元の値配列）として引数に渡されていますが
関数の側で受け付けません。

おそらく、セル範囲への参照として渡しているつもりなのでしょうが
セル範囲はシートのChildですからシートをまたげる訳ないですよね。

複数領域を指定するセル範囲への参照、といった場合、
その参照は、(A1:C6,A8:C11)のように
ひとつのセル参照の中に複数の領域が含まれている
という考え方が妥当です。

ワークシート関数でのセル参照（値配列ではない！）は
そのまま、参照文字列としてVBAでのRangeの引数に使えるものですが
Set r = Range("'Sheet1:Sheet3'!C7:D8")
という風に書いてみれば、これが矛盾していることは解ると思います。
RangeのParentはSheetですから、
省略している親オブジェクトは、この世のものではないですよね。

で、
今回の課題は
=INDEX(CHOOSE(2,Sheet1!C7:D8,Sheet2!C7:D8,Sheet3!C7:D8),2,2)
のように列挙して扱うことになるかと思います。

# VBAの方、補足読みました。少し時間ください。

お礼

何時もお世話になります、有り難うございます。

例えばVBA的に云うならば
With ThisWorkBook
　　Set r = .Sheets(Array(1 to 3)).Range("C5:E7")
End With
又は、
　　Set r = Array( _
　　　　　　　　　Sheets(1).Range("C5:E7"), _
　　　　　　　　　Sheets(2).Range("C5:E7"), _
　　　　　　　　　Sheets(3).Range("C5:E7"), _
　　　　　　　　 )
なのです☆が…

ところで、
=CHOOSE({1,2},シート2!C11:C16,シート1!B11:B16)
これ、面白いですね
有り難うございます。
また、学ばせて頂きました。

でも
=CHOOSE({1,2},データ上期!C11:C16,データ下期!B11:B16)
はOKですが、

=CHOOSE(ROW(A1:A2),データ上期!C11:C16,データ下期!B11:B16)
=index(CHOOSE(ROW(A1:A2),データ上期!C11:C16,データ下期!B11:B16),,)
=CHOOSE(INDEX(ROW(A1:A2),,),データ上期!C11:C16,データ下期!B11:B16)
=CHOOSE(INDEX(ROW(A1:A2),,),INDEX(データ上期!C11:C16,,),INDEX(データ下期!B11:B16,,))
はNG…

困ったものです、とほほ

回答No.2

MarcoRossiItaly

＞串刺し合計ですね
　有り難うございます。
　しかしながら
　これは配列数式に使えないので…
　済みません。

そういう問題ではなくて、エラーと書いてあるところの数式をよく見てくださいということです。あなたは、「INDEX とは無関係に不可能な」記述をしようとしているのではないですか、と言っているのです。

お礼

なるほど！ そういう意味でしたか、
失礼しました。

これをINDEXに展開すると、どういうことになるのでしょうか？

済みません、宜しくお願いします。

回答No.1

MarcoRossiItaly

エラー #VALUE!

=sum((sheet1!a1:c3,sheet2!b2:d4))

成功

=sum((sheet1!a1:c3,sheet1!b2:d4))
=sum(sheet1!a1:c3,sheet2!b2:d4)
=sum(sheet1!a1:c3)+sum(sheet2!b2:d4)

お礼

串刺し合計ですね
有り難うございます。

しかしながら
これは配列数式に使えないので…
済みません。