この関数の解説をお願いします。

質問

お世話になります。下記関数の解説をお願い致します。

{=IF(ROW(F1)-1<COUNTIF($F$1:$F$3000,$U$2),INDEX($G$1:$G$3000,SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999,ROW(F1))),"")}

U2に「ヨーロッパ」と入力するとF列のデータベースから「スペイン34、フランス33、イギリス44」など表示されるようになっています。

上記関数の解説をお願いします。
また｛｝を先頭につけている意味も教えて下さい。数式をいじろうとするとエラーがでます。
他人にいじらせないようにしてる気もします。

数式はコピペが出来なかったので写し間違えがあるかもしれません。
恐れ入りますがその際はながして頂ければと思います。
宜しくお願い致します。

回答No.3

bunjii

>下記関数の解説をお願い致します。
>{=IF(ROW(F1)-1<COUNTIF($F$1:$F$3000,$U$2),INDEX($G$1:$G$3000,SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999,ROW(F1))),"")}
転記ミスがあるようです。

=IF(ROW()-1<COUNTIF($F$1:$F$3000,$U$2),INDEX($G$1:$G$3000,SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999),ROW())),"")

ROW(F1)をROW()に省略できますので勝手に省略しました。（貼付画像の例）
この式は入れ子構造になっていますので内側から評価しながら解説します。
IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999),ROW()) の論理式 $F$1:$F$3000=$U$2 はF1セルからF3000セルまでをU2セルと同じか否かを判断します。
TRUE（正）のとき行番号を、FALSE（誤）のときは9999を返します。
この時、検査する配列と結果の配列は同じ形となるようにしますので式の確定時にCtrl+Shift+Enterの打鍵が必要になります。
このCtrl+Shift+Enterで確定することで { } で括られた数式が数式バーに表示されます。
一部を修正したときは必ずCtrl+Shift+Enterで確定しなければ目的通りの結果を得られません。
SMALL(配列,ROW()) は前項のIF関数の返り値（配列）の小さい順にROW()番目の行番号を取り出します。
ROW()はアクティブセルの行番号で式の設定するセルの行番号です。
省略形を使っていますので1行目から下へ式をコピーすることを想定しました。
INDEX($G$1:$G#3000,行番号) はG列の1行目から3000行目までの配列からSMALL関数で取り出した行番号の値を取り出します。
一番外側のIF関数はF列の1行目から3000行までの値が$U$2の値と同じである個数と行の番号から1を減じた値を比較し、$U$2と同じセルの数の方が大きいときINDEX関数で目的の値を取り出し、反対のときは空欄とします。

回答No.2

mshr1962

＞=SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999,ROW(F1))
＞をもう少し詳しく解説お願いします。
＞たぶんROWが分かっていません。

ROW()はカッコ内のセルの行番号を表示する関数です。

{=IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999)}
は
=IF($F$1=$U$2,ROW($F$1),9999)
=IF($F$2=$U$2,ROW($F$2),9999)
・
・
=IF($F$2999=$U$2,ROW($F$2999),9999)
=IF($F$3000=$U$2,ROW($F$3000),9999)
をまとめたものになります。

=IF($F$1=$U$2,ROW($F$1),9999)　=>　=IF($F$1=$U$2,1,9999)
でF1とU2が同じ値なら1、異なる場合は9999が返ります。

=SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999,ROW(F1))
は
=SMALL({9999,9999,3,4,9999・・・・・,2999,9999},1)
となり{}内の数値で1番低い値で上記の例だと3が返ります。

回答No.1

mshr1962

まず数式を囲う{　}ですが、これは配列数式を表します。
数式を入力後に、CtrlキーとShiftキーを押しながらEnterキーを押すことで設定できます。

数式の解説
=IF(ROW(F1)-1<COUNTIF($F$1:$F$3000,$U$2),(1),"")

ROW(F1)-1はF1の行数である1が返ります。これとF1:F3000の間にあるU2セルと同じ内容のセル数を比較して
同じか多い場合に(1)の数式を実行、少ない場合は空白を返します。

(1)=INDEX($G$1:$G$3000,(2))

G1:G3000の範囲の(2)結果の位置の値を表示します。

(2)=SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999,ROW(F1))

この部分が配列数式の対象ですね。
F1:F3000の値がU2の値と一致する行を最小値から順に抽出するようになってます。

結果としてF列の値がU2と一致する行のG列の値を、存在するだけ表示する数式になっています。

お礼

早速のご回答ありがとうございます。

すみません・・・

=SMALL(IF($F$1:$F$3000=$U$2,ROW($F$1:$F$3000),9999,ROW(F1))

をもう少し詳しく解説お願いします。
たぶんROWが分かっていません。