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

Excel2010を使用しています。


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

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

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

No.1 ベストアンサー 回答者： bunjii 回答日時： 2014/06/30 13:00

次の数式で良いと思います。

=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を適用

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

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

お礼日時：2014/06/30 22:33

No.3 回答者： tom04 回答日時： 2014/06/30 13:27

こんにちは！

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

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

この回答へのお礼

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

お礼日時：2014/06/30 22:39

No.2 回答者： aokii 回答日時： 2014/06/30 13:04

簡単にするため、A列に一列、列を追加して、A2に以下の式を入れて下にドラッグコピーする。

=IF(B2="",A1,A1+1)

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

この回答へのお礼

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

お礼日時：2014/06/30 22:40