痔になりやすい生活習慣とは?

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

「=LARGE(INDEX((($A$1:」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (8件)

今日はとてもとても暇なので。

。。(^^;;;

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

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

例えばこれらの式を、E1~E10にセットするものとして説明します。

(1)ではLargeの第2引数がA1と相対参照、かつ、ひとつのセルだけ設定されていることに気づくと思います。
ということは、この式は、E1~E10を範囲選択してから入力するのではなくて、
E1だけを選択し、配列数式にして入力後、E10までフィルドラッグしなければいけないことになります。

(2)では、A1:A10と10個のセルを指定していますので
E1~E10を選択後、配列数式で入力することになります。

●それから(2)の入力方法であれば、全てが相対参照であってもOKです。

E1~E10を選択後、

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

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

 
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2009/07/16 16:25

またまた登場、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))

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

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

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

{=LARGE(($A$1:$A$10<>"")/ROW($A$1:$A$10),ROW(A1))}
 
この説明で理解できたらええのですが。。。(^^;;;
以上です。
 
    • good
    • 0
この回答へのお礼

ほんとに何度もありがとうございます。
完全には理解できていませんが、上記式の結果を配列として取得するために、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))
で成功しました。
これであっていますか?

お礼日時:2009/07/16 14:04

>ただ、新たな疑問が・・・・。


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

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

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

1行目 =($A$1:$A$10)<>"" ■=A1<>"" ▲False
2行目 =($A$1:$A$10)<>"" ■=A2<>"" ▲TRUE
3行目 =($A$1:$A$10)<>"" ■=A3<>"" ▲False
4行目 ・・・・・
5行目 ・・・・・

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

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

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

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

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

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

甘えついでに最後にもう一点だけご教示いただけませんか?
=(($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以外の部分は大師様のお導きにより何とか理解しました。

お礼日時:2009/07/16 10:55

ばればれでしたねぃ。

。(^^;;;

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

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

で、本題。
騙されたと思って次のように入力願います。
 
E1~E10を選択しておいて、

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

F1~F10を選択しておいて

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


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

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

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

うひゃあ!おどろきました。
今、試しました。範囲を選択して
=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しなくともただのフィルドラッグで配列が現れるのは何ででしょう?

お礼日時:2009/07/16 09:39

回答2、myrangeです。



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

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

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

要するに、

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

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

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

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

同じく、

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

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

=ROW(A1) をドラッグです。
 
--------------------------------------------
 
このことは、INDEXは配列の中から値を取得する関数ということを踏まえると理解できるのではないでしょうか。

以上ここまで。
 
(注)
最近自宅のPCからokwebに入れず、
okwebのサポートもその理由は分からないということなので
これはgooから入ってます。
 
    • good
    • 0
この回答へのお礼

おやまあ、お大師様。

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

はい、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のままでした。
出来の悪い生徒で申し訳ないことでございます。

お礼日時:2009/07/15 22:37

(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が入ります。
    • good
    • 0
この回答へのお礼

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

お礼日時:2009/07/15 18:18

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


F、T,T,T、F、T、F、T、F、T
(T=True, F=False)

  ROW($A$1:$A$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

で、
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))

このようになり、、、、、
後はお分かりですね。。
以上ここまで。
 
    • good
    • 0
この回答へのお礼

> ($A$1:$A$10<>"")
> F、T,T,T、F、T、F、T、F、T
> (T=True, F=False)

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

>  ROW($A$1:$A$10)       1
> 1、2、3、4、5、6、7、8、9、10

=ROW($A$1:$A$10) を1行目から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))

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

お礼日時:2009/07/15 18:10

基本は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関数で一番大きな値から順番に取り出していけば、例の
ようになると思いませんか。
    • good
    • 0
この回答へのお礼

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

> 基本は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)<>""として1行目から10行目にフィルドラッグすると、
FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE の順で返りますが、
=ROW($A$1:$A$10)を1行目から10行目にフィルドラッグするとそれぞれ1しか返りません。
それでも配列は1;2;3;4;5;6;7;8;9;10になるのでしょうか?

お礼日時:2009/07/15 17:53

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aと関連する良く見られている質問

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい


人気Q&Aランキング