Excelで一つ下のセルを関数で、かつ可視セルで

質問

Excelの関数についての質問です。

一つ下のセルを関数で参照したいのですが、
その場合、フィルターで非表示となったセルも参照されてしまいます。
※OFFSET関数だとそうなります。

そこで質問です。
可視セル限定で一つ下のセルを参照する方法はありませんでしょうか。

よろしくお願いします。

回答No.2

MackyNo1

＞実際は一つ上のセルを参照し、そのセルの文字列と左のセルの文字列を結合したいです。

今回の質問と補足説明には全く関連性が無いように思うのですが・・・
ご質問は「１つ下のセル」ではなく、フィルタしている同じデータの他の列のセルの値ということでしょうか？

項目名が１行目で、表示されているB列の下の行のセルの値はC2セルに以下のような関数を入力し、下方向にオートフィルすれば表示することができます。

=INDIRECT("B"&SMALL(IFERROR(1/SUBTOTAL(3,INDIRECT("A"&ROW($2:$100))),1000)*ROW(($2:$100)),SUBTOTAL(3,$B$2:B2)+1))&""

ただし、上記の式は配列数式ですので、入力後Ctrl+Shift+Enterで確定してください。

＃実際にどのようなデータをどのように結合したいかによって、適用する数式などがが変わりますので、もう少し具体的に目的を含めて、実データ（に近い形）で補足説明されるとより的確な回答ができると思います。

お礼

ご回答ありがとうございました。
また、補足説明がいたらなかったようで失礼しました。

ご教授頂きました数式ですが、1つ下の可視セルを表示まで、ということでよろしいでしょうか。
うまくいったのですが、非可視セルが100を超えるとエラーとなってしまいました。

SUBTOTALと非可視セルの行数、そのMAX値（またはMIN値）などを利用して
もう少し頑張ってみます。

ありがとうございました。

回答No.1

MackyNo1

どのような目的で可視セルの１つ下のセルを参照したいのでしょうか？
目的によって、合理的な数式が変わりますが、例えば１行目が項目名の場合、A列の可視セルの１つ下のセルの内容は以下のような数式になります。

=INDIRECT("A"&MAX(INDEX(SUBTOTAL(3,INDIRECT("A"&ROW(1:100)))*ROW((1:100)),))+1)

お礼

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

一つ訂正致します。
実際は一つ上のセルを参照し、そのセルの文字列と左のセルの文字列を結合したいです。

月　1日　1日
火　2日　1日＋2日
水　3日　1日＋2日＋3日
木　4日　1日＋2日＋3日＋4日
金　5日　1日＋2日＋3日＋4日＋5日
土　6日　1日＋2日＋3日＋4日＋5日＋6日
日　7日　1日＋2日＋3日＋4日＋5日＋6日＋7日
祝　8日　1日＋2日＋3日＋4日＋5日＋6日＋7日＋8日

この状態から、曜日をフィルタすると、指定曜日の日付データだけを結合した文字が取得できるかとやっています。
いかがでしょうか？