Excel 空白を含む列範囲からn番目のセル

質問

Excel2010を使用しています。

皆様のお知恵をお貸しください。

下の図のようにA2からA10までの表があり、空白も混じっています。
C3セルに2と入力したら、A2からA10までの表から空白を除いた2番目の数値
つまり、7がC5セルに表示されるようにしたいのです。

ご教示のほど、宜しくお願い致します。

回答No.1

bunjii · ベストアンサー

次の数式で良いと思います。
=INDEX(A$1:A$10,SUMPRODUCT(SMALL((A$2:A$10<>"")*ROW(A$2:A$10)+(A$2:A$10="")*100,C3)),1)
SUMPURODUCT関数を使わなくてもSMALL関数でn番目の行番号を抽出すればINDEX関数で値を取り出せます。
但し、計算途中で配列値を扱うので数式の確定時にCtrl+Shift+Enterの操作が必要です。
SUMPURODUCT関数を使うと通常のEnterキーで確定できます。
SMALL関数の範囲は値が入力されているセルに対して行番号を適用し、未入力に対しては定数の100を適用しています。
実情に合わせて検索範囲と定数を変更してください。

お礼

とても華麗な数式をありがとうございました。

自分なりにSUMPRODUCT関数を学んではいるものの、
SMALL関数と組み合わせた上で
>値が入力されているセルに対して行番号を適用し、
>未入力に対しては定数の100を適用

このような発想に至るにはまだまだ及びません。

非常に勉強になりました。
ベストアンサーとさせていただきました。

回答No.3

tom04

こんにちは！

画像通りに配置として、表示したいセルに
=IFERROR(INDEX(A2:A1000,SMALL(IF(A2:A1000<>"",ROW(A2:A1000)-1),C3)),"")
としてCtrl＋Shift＋Enterで確定！

この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　表示したいセルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。m(_ _)m

お礼

ご教示ありがとうございました。
配列数式の確定まで丁寧に説明くださり恐れ入ります。
SMALL関数をもっと勉強しようと思います。

回答No.2

aokii

簡単にするため、A列に一列、列を追加して、A2に以下の式を入れて下にドラッグコピーする。
=IF(B2="",A1,A1+1)

次に、D5に以下の式を入れて、下にドラッグコピーでいかがでしょう。
=VLOOKUP(D3,A:B,2,FALSE)

お礼

ご回答ありがとうございました。
期待していた結果とはちょっと違いましたが
勉強になりました。