エクセルの配列数式について

お世話になります。
よろしくお願いいたします。

次のHPで紹介されている方法（方法３）を用いて空白セルを詰めて表示したいと
考えていますが、配列数式が初めてなもので、なかなか理解が進みません。
http://office-qa.com/Excel/ex257.htm

とくに次の数式の内、IFの中では何をしているのかよく分かりません。
{=INDEX(A:A,SMALL(IF(A:A<>"",ROW(A:A)),ROW()))}

ご教示頂ければ幸いです。
よろしくお願いいたします。

No.5 ベストアンサー 回答者： tom04 回答日時： 2016/04/17 09:24

No.3です。

＞ROW(A:A)の処理がよくわかりません。
とありますが、
これはINDEX関数で範囲指定した行数分だけを数式に入れます。
「TRUE」の行の行番号を取得するためです。

必ず1行目から指定します。相対参照にしてしまうとフィル＆コピーするたびに
行番号がずれてしまうため、絶対参照にする必要があります。
数式を入れるセルが何行目であっても必ず
＞ROW(A$1:A$1000)
のように1行目からの指定です。
そうしないとINDEX関数で範囲指定した何行目を返すか？という意味の数式ですので
結果がずれるのを防ぐためです。

※　文章で色々説明しても一気に理解できないかもしれませんが
ご自身で試行錯誤してみてください。m(_ _)m

この回答へのお礼

>「TRUE」の行の行番号を取得するためです。

なんとなくわかってきました。これでSMALLの中で配列を作ってるんですね。
配列も基礎から勉強してみようと思います。ありがとうございました。

お礼日時：2016/04/17 15:56

No.4 回答者： 銀鱗 回答日時： 2016/04/15 19:12

まず各々の関数の使い方は理解できているだろうか。

IF関数とROW関数について理解できていれば問題なく読み取れるはずなのだが…。

ROW関数は引数のセルの行番号を返す関数だ。
　ROW(A1）ならば１
　ROW(C12）ならば12
　が値として返ってくる。
そして引数が無い場合は、そのROW関数が入力されているセルの行番号を返す。
　B２セルにROW()とあれば2
　F24セルにROW()とあれば24
　が値として返ってくる。

IF関数は条件式と条件が成立した時の挙動、条件が成立しない時の挙動を引数に記述するが、挙動を省略した場合は
　成立時を省略すると"TRUE"
　不成立時を省略すると"FALSE”
　というちょっと特殊な値が返ってくる。

もう1つ、A:Aの意味。
これはA列を上から下まで全部という意味。
とくに配列数式で示された場合は上から順番という意味になる。（A1:A1048576 と同じ）

これらを念頭に入れてもう一度読み直して見よう。
範囲を決めて配列数式を使わずに同じ事をさせるなら
（※面倒なので範囲を３行として示す）
1行目　=INDEX(A1:A3,SMALL(IF(A1<>"",ROW(A1)),ROW()))
2行目　=INDEX(A1:A3,SMALL(IF(A2<>"",ROW(A2)),ROW()))
3行目　=INDEX(A1:A3,SMALL(IF(A3<>"",ROW(A3)),ROW()))
こんな表記になる。

この回答へのお礼

ROW(A:A)の動作が理解できなかったのですが、各行に応じた番号を返すということなのでしょうか。
ためしに=ROW(A:A)として入力しても、どの行でも返す値は1になるのですが。。。
また、配列数式を使わなかった場合の式をご提示いただきましたが、

> 1行目　=INDEX(A1:A3,SMALL(IF(A1<>"",ROW(A1)),ROW()))
> 2行目　=INDEX(A1:A3,SMALL(IF(A2<>"",ROW(A2)),ROW()))
> 3行目　=INDEX(A1:A3,SMALL(IF(A3<>"",ROW(A3)),ROW()))

1行目から入れてみると、
2行目以降は＃NUM!となります。

お礼日時：2016/04/17 08:38

No.3 回答者： tom04 回答日時： 2016/04/15 16:53

こんにちは！

お示しのサイトの場合、1行目に数式を入れていますので
その数式で大丈夫だと思いますが、1行目はタイトル行になっている場合が多いはずですので、
自分としては
=IFERROR(INDEX(A$1:A$1000,SMALL(IF(A$1:A$1000<>"",ROW(A$1:A$1000)),ROW(A1))),"")
という数式にします。
※　配列数式はPCにかなりの負担を掛けますので、
列全体を範囲指定してしまうと計算速度が極端に落ちる場合があります。
なので、1000行程度の範囲でやめています。
（3000～5000行くらいなら問題ないと思います）

＞IFの中では何をしているのかよく分かりません
文章で説明するより↓の画像で説明した方が判りやすいかもしれません。

①　B2セルに
=A2<>""
として下へコピーしています。
これで空白行は「FALSE」（0）、何らかのデータが入っている行は「TRUE」（1）になります。
②　C2セルに
=ROW()
という数式を入れ下へコピー
これで行番号が表示されます。
③　D2セルに
=B2*C2
として下へコピー！
これで「TRUE」の行の行番号が表示されます（「FALSE」の行は「0」になります）

配列数式の結果として
INDEX関数の範囲内のA列が空白以外の行番号の小さい順に表示！
というコトになります。

この程度でお判りになるでしょうか？m(_ _)m

この回答へのお礼

ありがとうございます。
ご説明いただいた内容は理解できましたが、
振り返って当てはめてみると、
No.4の方へのお礼にも書いたのですが、ROW(A:A)の処理がよくわかりません。
各行においてどのような値を返しているのでしょうか。

お礼日時：2016/04/17 08:52

No.2 回答者： y504 回答日時： 2016/04/15 16:49

リンク先の

緑色になっている部分が１．の説明、
赤色になっている部分が２．の説明、
青色になっている部分が３．の説明になっています

No.1 回答者： CoalTar 回答日時： 2016/04/15 16:37

リンク先の説明では、難しいでしょうか？

当方、XL2003なので配列の列指定ができないので
=INDEX(A:A,SMALL(IF($A$1:$A$30<>"",ROW($A$1:$A$30)),ROW(A1)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
となります。
で、
“達人”芳坂和行氏に学ぶ、エクセル「配列数式」講座
http://pc.nikkeibp.co.jp/pc21/special/hr/
(まだ残ってた)はどうでしょうか？

一応説明はしてみますね
作業列が、配列の考え方にあたる部分。
C1セルに、
=IF(A1<>"",ROW(A1))
下へオートフィル

E2セルに =ROW()
下へオートフィル

F2セルに
=INDEX(A:A,SMALL($C$1:$C$8,$E2))
右へ下へオートフィル エラー処理なし。

基本的にこのような(オートフィルするような)配列数式は計算が遅くなるので、作業列使ったほうが良いです。
C11セル 0
C12セル =SUM(A12<>"",C11)
下へオートフィル

E12セルから連番

F12セル
=INDEX(A$11:A$18,MATCH($E12-1,$C$11:$C$18)+1)
右へ下へオートフィル
(右へオートフィルしている時点で冗長さは残っていますが)
計算速度を早くする「二分探索木」なんて方法になります。

この回答へのお礼

ありがとうございました。
教えて頂いた配列数式講座に一通り目を通してみようと思います

お礼日時：2016/04/17 15:57