空白でないセルの値を返す方法について その2

いつもお世話になっております。

http://oshiete.goo.ne.jp/qa/6290200.html

で、以前質問させていただきました内容についてまた投稿します。

エクセルにて下記のような表を作成しております。


A　　　B　　　C　　　D　　　E　　　F　　　G　・・・・・・　Q

名前　住所　 あ　 　　　　　　 　 あ


A～Cは入力は固定です。
D～Qは可変でこの範囲どこか1箇所に入力されます。
変更があればD～Qの中で移動します。

前回の質問では、「D～Gの入力してあるセルを検索してCに反映させてたい」旨を投稿させていただきました。
↓回答いただいた数式で解決できました。
=IF(COUNTA(D2:G2)=0,"",INDEX(D2:G2,,SUMPRODUCT((D2:G2<>"")*COLUMN($A$1:$D$1))))

今回の場合でも、同様の数式で当てはめて数式を入れてみたものの、N/Aになってしまいます。
（対象セルはD2:G2→D2：Q2へ変更しています。）
COLUMN関数をCOLUMNSへ変えてみても変化なしです。

どのようにしたら上手く行くでしょうか？
お詳しい方、ご教授下さい。

No.5 回答者： mu2011 回答日時： 2010/11/23 09:41

NO1です。

INDEX関数のベースを誤っていましたので訂正します。
=IF(COUNTA(D2:Q2)=0,"",INDEX(2:2,,SUMPRODUCT((D2:Q2<>"")*COLUMN(D2:Q2))))

一例です。
仮にD2:Q2範囲が文字列（数値以外）が前提で、D2:Q2範囲で一番右端のデータを表示します。
=IF(COUNTA(D2:Q2)=0,"",INDEX(D2:Q2,MATCH("",D2:Q2,-1)))

No.4 回答者： tom04 回答日時： 2010/11/22 19:48

こんばんは！

前回、回答したものです！

余計なお世話かもしれませんが、
すでにokormazdさんが回答してくださっている

=IF(COUNTA(D2:Q2)=0,"",INDEX(D2:Q2,,SUMPRODUCTCOLUMN($A$1:$N$1))))
という数式にすれば大丈夫だと思います。
列方向の検索になりますので、INDEX関数で範囲指定した列数分だけ
必ず1列目（A列）から掛ける必要があります。

数式の意味はINDEX関数で範囲指定した中で何列目か？を求めることになります。
前提として、行に関しては1つのセルにしかデータが入らないということですので
SUMPRODUCT関数の前半部分の(D2:Q2<>"")は空白セルはFALSE（0）となりますので
いくら列番号をかけても「0」です。
データが入っている列だけがTRUE（1）となり、範囲指定した列すべてに1から14までその列番号を順に掛けるので、仮にF列にデータが入っている場合は
範囲指定した3番目の列の「3」という値が返り、INDEX関数で範囲指定した3列目を表示しなさい！
という数式になります。

以上、長々と書きましたがごめんなさいね。m(__)m

No.3 回答者： MackyNo1 回答日時： 2010/11/22 19:28

INDEX関数の検索範囲を調整するのが面倒な式になっています。

汎用性のあるわかりやすい数式にするなら以下のように行全体を指定してください。

=IF(COUNTA(D2:Q2)=0,"",INDEX(2:2,,SUMPRODUCT((D2:Q2<>"")*COLUMN(D2:Q2))))

ちなみに「あ」の文字が英数字やひらがなだけのような場合は以下のような関数で簡単に表示できます。

=PHONETIC(D2:Q2)

ひらがなの場合は、データ入力範囲を選択して「書式」「ふりがな」で「ひらがな」にチェックを入れてください。

No.2 回答者： okormazd 回答日時： 2010/11/22 18:18

=IF(COUNTA(D2:Q2)=0,"",INDEX(D2:Q2,,SUMPRODUCT((D2:Q2<>"")*COLUMN($A$1:$N$1))))

にでもしてみたらどうでしょうね。

No.1 回答者： mu2011 回答日時： 2010/11/22 18:10

COLUMN関数の範囲が合っていないからだと思います。

=IF(COUNTA(D2:Q2)=0,"",INDEX(D2:Q2,,SUMPRODUCT((D2:Q2<>"")*COLUMN(D2:Q2))))