=LARGE(INDEX((($A$1:$A$10)<>"")/ROW($A$1:$A$10),),ROW(A1)) とは

質問

エクセル2000です。
A列（A1～A10）に飛び飛びに文字列が表示されているとき、
仮にC1に
=LARGE(INDEX((($A$1:$A$10)<>"")/ROW($A$1:$A$10),),ROW(A1))　と入れてフィルドラッグすると、C列にA列のうち文字が表示されたセルの行番号の逆数を取得できるようです。
これはどのような理屈によるものでしょうか？
LARGEやINDEXなど組み合わされた関数それぞれの意味は理解しているはずなのに、先ほどから式とにらめっこしていますが何故こうなるのかわかりません。
ご教示いただければ幸いに存知ます。

回答No.8

myRange · ベストアンサー

今日はとてもとても暇なので。。。(^^;;;

＞（１）=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW(A1))
　　　　をCSEしてみましたが、配列にならず
＞（２）=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW($A$1:$A$10))
　　　　で成功しました。

どちらでも上手くいくのですがちょと気をつけないといけないところがあります。

例えばこれらの式を、Ｅ１～Ｅ１０にセットするものとして説明します。

（１）ではLargeの第２引数がＡ１と相対参照、かつ、ひとつのセルだけ設定されていることに気づくと思います。
ということは、この式は、Ｅ１～Ｅ１０を範囲選択してから入力するのではなくて、
Ｅ１だけを選択し、配列数式にして入力後、Ｅ１０までフィルドラッグしなければいけないことになります。

（２）では、A1:A10と10個のセルを指定していますので
Ｅ１～Ｅ１０を選択後、配列数式で入力することになります。

●それから（２）の入力方法であれば、全てが相対参照であってもＯＫです。

Ｅ１～Ｅ１０を選択後、

{=LARGE((A1:A10<>"")/ROW(A1:A10),ROW(A1:A10))}

---------------------------------------------------
どうもスーパーエクスパートさんは
「配列」という言葉に苦手意識が働くようですねぃ。(^^;;;
以上です。

お礼

ありがとうございました。
とても勉強になりました。
ほんと配列は難しいです。
苦手なのは配列だけではないですけど・・・・。

長々とお付き合いいただき、深謝いたします。

回答No.7

myRange

またまた登場、myRangeです。

＞=(($A$1:$A$10)<>"")/ROW($A$1:$A$10)　でCSEしたら逆数の配列が現れましたが
＞これをそのままLARGEで読めず、一旦INDEX関数に入れなければならないのはどうしてでしょうか？

なぜ配列が現れたかというと「配列数式」にしたからです。

●=LARGE(INDEX((($A$1:$A$10)<>"")/ROW($A$1:$A$10),),ROW(A1))

この式は配列数式ではありませんよね。
ということは、INDEXを外して

●=LARGE((($A$1:$A$10)<>"")/ROW($A$1:$A$10)),ROW(A1))

としてもこれは配列数式ではないので

(($A$1:$A$10)<>"")/ROW($A$1:$A$10))

この結果は（正しい値かどうかは置いといて）、
１０個の配列ではなくて１個なので上手くいかないのです。
で、上記式の結果を配列として取得するために、INDEX関数の登場となるのです。

またこのことから、
INDEX関数を省いた式にするときは、
「配列数式」にすればいいことに気づかれると思います。　

【INDEXを省いて配列数式にする】

{=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW(A1))}

この説明で理解できたらええのですが。。。(^^;;;
以上です。

お礼

ほんとに何度もありがとうございます。
完全には理解できていませんが、上記式の結果を配列として取得するために、INDEX関数が必要なのだと覚えておくことにします。

>【INDEXを省いて配列数式にする】
> {=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW(A1))}

=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW(A1))
をCSEしてみましたが、配列にならず
=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW($A$1:$A$10))
で成功しました。
これであっていますか？

回答No.6

myRange

＞ただ、新たな疑問が・・・・。
＞=($A$1:$A$10)<>"" は、CSEしなくともただのフィルドラッグで配列が現れるのは何ででしょう

この疑問は生じると思ってました。
先に書いとけばよかったですねぃ。

が、これは配列が現れるわけではありませぬよ。
配列数式でなく式を単純にドラッグした場合

１行目　=($A$1:$A$10)<>""　■=A1<>""　▲False
２行目　=($A$1:$A$10)<>""　■=A2<>""　▲TRUE
３行目　=($A$1:$A$10)<>""　■=A3<>""　▲False
４行目　・・・・・
５行目　・・・・・

このようにこの式が、
式が参照している行と同じ行にセットされていると
その行のみを参照するように解釈されているわけです。
これは、この式を式が参照している範囲と違った行
例えば、Ｄ１２あたりからセットするとエラーが出ることからも分かります。

じゃ、ROW($A$1:$A$10)はなぜそうならないの？
との疑問が生じると思いますが、
これらは、その式の特徴であるとしかいいようがありません。
要するに、この関数などを作成したマイクロソフトに訊くしかないということです。

何れにしろ、
=($A$1:$A$10)<>""　や　ROW($A$1:$A$10) のようなふつうでは配列を扱えないものは
当然ながら配列数式にしないと意味がない、正しい結果が出ないということです。

文章での説明は難しいなぁ。。。ブツブツブツ。。(^^;;;

----------------------------------------
merlionXXさんの上達には目を見張るものがありまする。
よって、これからは、スーパーエキスパートさんとお呼びすることにしませう。
以上です。

お礼

何度もありがとうございます。
「これらは、その式の特徴であるとしかいいようがありません。」ということは、そのように覚えなければいけないということですね、わかりました。

甘えついでに最後にもう一点だけご教示いただけませんか？
=(($A$1:$A$10)<>"")/ROW($A$1:$A$10)　でCSEしたら逆数の配列が現れましたが、これをそのままLARGEで読めず、一旦INDEX関数に入れなければならないのはどうしてでしょうか？

今回しつこく追及しているのは、実はこれによって
=IF(ROW(A1)>COUNTIF($A$1:$A$10,"?*"),"",INDEX($A$1:$A$10,1/LARGE(INDEX((($A$1:$A$10)<>"")/ROW($A$1:$A$10),),ROW(A1))))
という完成形（作業列や、CSE動作が不要な、空白行を飛ばして上から表示させる関数）を理解したいからです。INDEX以外の部分は大師様のお導きにより何とか理解しました。

回答No.5

onlyrom

ばればれでしたねぃ。。(^^;;;

＞=A1<>"" でなくてですか？

それです。当方のタイプミスなり。お許しあれ。

で、本題。
騙されたと思って次のように入力願います。

E1～E10を選択しておいて、

=$A$1:$A$10<>""　Ctrl+Shift+Enter

F1～F10を選択しておいて

=ROW($A$1:$A$10)　　Ctrl+Shift+Enter

最近のエキスパートさんのＶＢＡの回答を見ていると
当方は既に追い越されたような感じを受けておりまする。
ゆえに、エクスパートさんの質問を見かけても回答を遠慮している次第です。
以上ここまで。

お礼

大師様おはようございます。
昨夜はいただき物の「野うさぎの走り」のせいで眠ってしまいました。
わたくしなど大師様には遠く及びません。

> 騙されたと思って次のように入力願います。

うひゃあ！おどろきました。
今、試しました。範囲を選択して
=ROW($A$1:$A$10)　でCtrl+Shft+Enterしたところ見事に配列が現れました！
おなじく
=(($A$1:$A$10)<>"")/ROW($A$1:$A$10)　でCSEしたら逆数の配列が現れました！
この逆数の配列をINDEX関数に取り込み、LARGEで大きい方から表示したってわけですね。やっと実感できました。

ただ、新たな疑問が・・・・。
=($A$1:$A$10)<>"" は、CSEしなくともただのフィルドラッグで配列が現れるのは何ででしょう？

回答No.4

onlyrom

回答２、myrangeです。

＞=($A$1:$A$10)<>""として１行目から10行目にフィルドラッグすると
＞そのように返るのは確認いたしましたので理解できます。

この理解には、？マークがふたつみつ付くかも。。(^^;;;

試しに、
=($A$1:$A$10)<>""
この式を、Ｄ１２辺りからドラッグしてみてください。
エラーになるはずです。

要するに、

=($A$1:$A$10)<>""

この式は配列数式でなくてはいけないということです。
もし配列数式を使わないで確かめるなら

　=A1　としてドラッグです。

---------------------------------------------

同じく、

>ROW($A$1:$A$10)

これも配列数式でないと正しい結果はでません
配列数式を使わないで確かめるなら

=ROW(A1)　をドラッグです。

--------------------------------------------

このことは、INDEXは配列の中から値を取得する関数ということを踏まえると理解できるのではないでしょうか。

以上ここまで。

(注）
最近自宅のＰＣからokwebに入れず、
okwebのサポートもその理由は分からないということなので
これはgooから入ってます。

お礼

おやまあ、お大師様。

> この式を、Ｄ１２辺りからドラッグしてみてください。
> エラーになるはずです。

はい、VALUEエラーでした。

> =($A$1:$A$10)<>""
> この式は配列数式でなくてはいけないということです。

CSE関数として、{=($A$1:$A$10)<>""}　としたところすべてTRUEになってしまいました？

> =A1　としてドラッグです。

=A1<>"" でなくてですか？

=ROW($A$1:$A$10)もCSE関数として、{=ROW($A$1:$A$10)}　としたところすべて1のままでした。
出来の悪い生徒で申し訳ないことでございます。

回答No.3

kazuhisa01

(1)　(($A$1:$A$10)<>"")/ROW($A$1:$A$10)
⇒TrueかFalseの値を行番号で割っている。
Trueの場合1、Falseの場合0となるため成立する。

(2)　INDEX((1),)
第二引数以降を入力しないことで配列全体を返却する。

(3)　LARGE((2),ROW(A1))
返却された配列の中で数値的に大きい方から見て行番号と同じデータを返している。

{0,0.5,0,0.25,0,0.166…}
と配列で返ってきているので、1行目のセルには0.5が、2行目のセルには0.25が入ります。

お礼

ありがとうございます。
みなさまのご教示で大分わかってきましたが、
=($A$1:$A$10)<>""として１行目から10行目にフィルドラッグするとFALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE　の順で返りますが、=ROW($A$1:$A$10)を１行目から10行目にフィルドラッグしても1しか返りません。
それなのに配列は1;2;3;4;5;6;7;8;9;10になるというところがまだわからないでいます。

回答No.2

myRange

（$A$1:$A$10<>""）
Ｆ、Ｔ，Ｔ，Ｔ、Ｆ、Ｔ、Ｆ、Ｔ、Ｆ、Ｔ
（Ｔ=True,　Ｆ=False）

　　ROW($A$1:$A$10)　　　　　　　１
１、２、３、４、５、６、７、８、９、１０

　($A$1:$A$10)<>"")/ROW($A$1:$A$10)
0/1,1/2,1/3,1/4,0/5,1/6,0/7,1/8,0/9,1/10

で、
INDEX((($A$1:$A$10)<>"")/ROW($A$1:$A$10),),
は、
0/1,1/2,1/3,1/4,0/5,1/6,0/7,1/8,0/9,1/10
この配列が返るので

LARGE((0/1,1/2,1/3,1/4,0/5,1/6,0/7,1/8,0/9,1/10),ROW(A1))

このようになり、、、、、
後はお分かりですね。。
以上ここまで。

お礼

>　（$A$1:$A$10<>""）
> Ｆ、Ｔ，Ｔ，Ｔ、Ｆ、Ｔ、Ｆ、Ｔ、Ｆ、Ｔ
> （Ｔ=True,　Ｆ=False）

=($A$1:$A$10)<>""として１行目から10行目にフィルドラッグすると、そのように返るのは確認いたしましたので理解できます。

>　　ROW($A$1:$A$10)　　　　　　　１
> １、２、３、４、５、６、７、８、９、１０

=ROW($A$1:$A$10) を１行目から10行目にフィルドラッグするとそれぞれ1しか返りません。
それでも配列は1;2;3;4;5;6;7;8;9;10になるのでしょうか？

>　($A$1:$A$10)<>"")/ROW($A$1:$A$10)
> 0/1,1/2,1/3,1/4,0/5,1/6,0/7,1/8,0/9,1/10

上記で、配列は1;2;3;4;5;6;7;8;9;10になるのであれば理解できます。

> LARGE((0/1,1/2,1/3,1/4,0/5,1/6,0/7,1/8,0/9,1/10),ROW(A1))

はい、よくわかります。
ありがとうございました。

回答No.1

grumpy_the_dwarf

基本はlarge(うじゃうじゃ,自分の行番号)で、1行目からドラッグす
れば一番大きな値から順番に返しますね。うじゃうじゃの部分は、
index(
　　　(($A$1:$A$10)<>"")/
　　　ROW($A$1:$A$10),
　　　ゼロを省略)
ですから、「A列が空じゃない論理値の配列/行番号の配列」という
配列を生成しているわけです。例によってTRUEは1、FALSEは0として
四則演算にぶち込まれます。例では「A列が空じゃない論理値の配
列」は{FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
で、「行番号の配列」は常に{1;2;3;4;5;6;7;8;9}ですから、index
関数の返り値は{0;1/2;1/3;1/4;0;1/6;0;1/8;0;1/10}ですね。

するとlarge関数で一番大きな値から順番に取り出していけば、例の
ようになると思いませんか。

お礼

さっそくありがとうございました。

> 基本はlarge(うじゃうじゃ,自分の行番号)で、1行目からドラッグす
れば一番大きな値から順番に返しますね。

はい、ここはもちろんわかります。

> index((($A$1:$A$10)<>"")/ROW($A$1:$A$10),ゼロを省略)

ここがよくわからないのです。
INDEX関数は、範囲と行や列を指定しますよね？
範囲が、($A$1:$A$10)<>"")/ROW($A$1:$A$10)の計算結果の配列として、行を指定するかわりに0として配列全体が指定されたということでしょうか？
また、もう一点よくわからないのは、
=($A$1:$A$10)<>""として１行目から10行目にフィルドラッグすると、
FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE　の順で返りますが、
=ROW($A$1:$A$10)を１行目から10行目にフィルドラッグするとそれぞれ1しか返りません。
それでも配列は1;2;3;4;5;6;7;8;9;10になるのでしょうか？