excel2010 行ごとにデータがある最後のセルの１行目の値を取得したい

歯抜けのデータがあります。１行ごとに最後の値が入っている列の１行目の値を取得しZの列（最終）にセットしたいのですが、できればvbaではなく関数でできないものでしょうか。
毎回列数も行数も若干フォーマットも違うのでセルにいれる関数のほうがありがたいです。
　　　A　B　C　D ・・・ Z
１　　あ　い　う　え　　　最終　
２　　10　5　7　9　　　　え
３　　8　　　2　　　　　　う　　
４　　　　6　　　　　　　　い
５　　3　　　　　　　　　　あ

質問者からの補足コメント

• みなさんありがとうございます。せっかく回答いただいたのに、うまく加工ができないでおります。
  ＞enunokokoroさん
  実際には取得したいデータは（今回は）M列からFU列なので、
  AをM　YをFUに変えたら　ずれた結果になったり（たとえばCO列なのにDA列を取得）場所によっては＃REF！となってしまいました。今回の範囲だったらどのように加工すればよいか教えてください。
  =INDEX($M$1:$FU$1,MAX(INDEX(($M2:$FU5<>"")*COLUMN($M2:$FU5),0)))

    補足日時：2017/02/08 11:22

• ＞tom04さん
  enunokokoroさんと同じ結果になりました。ずれた結果になったり（たとえばCO列なのにDA列を取得）場所によっては＃REF！となってしまいました。今回の範囲だったらどのように加工すればよいか教えてください。
  ｛=IF(COUNTA(M2:FU2),INDEX(M$1:FU$1,,MAX(IF(M2:FU2<>"",COLUMN(M1:FU1)))),"")｝

    補足日時：2017/02/08 11:23

• No3さんの分も含めてご丁寧に説明いただきありがとうございます。こんなにするすると解けたら気持ちがいいだろうにと思います。本来じっくり一つ一つの関数を調べるべきだと思いますが、これを取得した後の作業がメインでして、ほかの作業にもおわれてまして、今の状態をクリアしなければならず、初歩的な質問ばかりですがご容赦ください。
  
  ①列の値がM列からL列分を引いたA列になるように
  というのが理解できていないかもしれません。MからLを引いたAとはどういうことなのでしょうか。AからL「まで」を引くという覚え方でよいでしょうか。毎回取得範囲が異なるので・・・。取得したい列－１つ前の列を「-COLUMN($？2)」？の場所に当てはめればよいでしょうか。

  No.6の回答に寄せられた補足コメントです。 補足日時：2017/02/08 18:34

• ②COLUMN($M2:$FU2)-COLUMN($L2)
  のようにとあるのですが、その下の式は
  =INDEX($M1:$FU1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN(L1)),0)))
  とあります。下の式は「-COLUMN($L2)」に直せばよいでしょうか。
  
  ③この式をオートフィルでコピーしたら＃VALUEになってしまったので見てみると、
  =INDEX($M2:$FU2,MAX(INDEX(($M3:$FU3<>"")*(COLUMN($M3:$FU3)-COLUMN(L3)),0)))
  に代わっていました。「INDEX($M1:$FU1」の部分はヘッダーだと思ったのでINDEX($M$1:$FU$1)にしてみたんですけどあってますでしょうか。

    補足日時：2017/02/08 18:35

• enunokokoroさん　tom04さん
  丁寧に教えていただき、ありがとうございます。すみません。最後にもう少し教えてください。これでもネットで調べてみたんですがやっぱりわからなくて。
  ①配列というものを理解していなかったので、enunokokoroさんの説明に「配列」とあったのですが式はそのままセルに貼り付けておりCtrl＋Shift＋Enterで｛｝で囲っていません。囲うと１件もデータがない行は#VALUE!と出ていたところが、M1の情報「あ」がセットされてしまいました。同じ配列でも｛｝で囲うのと囲わないのがあるのはなぜでしょうか。

    補足日時：2017/02/09 12:01

• ②(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN(L1)),0)　の最後の0につまづいています。配列で行番号や列番号に0を指定すると行全体・列全体の情報が一気に取得できるという説明を見つけましたが、この例でいくと行を指していますか？「行全体を取得」というのはこの式では具体的には何を意味しているのかが今一つぴんとこないのです。教えてください。

    補足日時：2017/02/09 12:02

• ＞enunokokoroさん　＞tom04さん
  何度もありがとうございます。お二人の回答を合わせてみているので、まとめての質問になってしまいますがお願いします。
  データがあるときだけ１行目の値がほしいので、「あ」がセットされてしまうと困るのです。本当に「あ」の列にデータがあった場合と区別がつかなくなってしまうからです。
  「参考程度に」とのことですがせっかく教えてくださったので試してみたところ、#valueのままでした。
  データがなければなにも表示しない「""」か「"なし"」をいれたいです。実は１行目の実際のデータは日付なのでこの関数をいれるセルも日付型にしているため「0」をセットすると1900/1/0になってしまうからnullか文字列をいれるしかないのかなと思っています。if関数だから加工してみたのですが#valueのままで、＞tom04さんのは「あ」を取得してしまいます。

    補足日時：2017/02/10 09:58

• 

  ＞enunokokoroさん　＞tom04さん　ほかにも回答くださったみなさま　本当にありがとうございました。特にこのお二人ともベストアンサーに選びたかったのですが一人なので、回答くださった回数で＞enunokokoroさんにさせていただきました。

    補足日時：2017/02/14 11:15

No.11 ベストアンサー 回答者： enunokokoro 回答日時： 2017/02/10 12:37

No.10の式の一部の再度の修正

私がNo.9で提示した式
　=IF(COUNTA($M2:$FU2),INDEX($M$1:$FU$1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN($L1)),0))),"")

COUNTAの範囲がA列からになっていました。
添付画像の数式バーのものも同じ間違いのままでした。
検証するときに修正するのを忘れていました。

この回答へのお礼

enunokokoroさん
何度も丁寧に回答してくださり、ありがとうございました。おかげで今後、作業がとても効率よく進めることができます。

お礼日時：2017/02/14 11:09

No.12 回答者： tom04 回答日時： 2017/02/10 14:35

続けてお邪魔します。

enunokokoroさんの回答で解決済みだと思っていましたが・・・
なかなか思い通りにならないみたいですね？

とりあえずこの画面から↓の数式をドラッグ＆コピー　→　表示したいセルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定　→　フィルハンドルで下へコピーしてみてください。

=IF(COUNTA(M2:FU2),INDEX(M$1:FU$1,MAX(IF(M2:FU2<>"",COLUMN(M2:FU2)-12))),"")

尚、
＞実は１行目の実際のデータは日付なので・・・

というコトですので、シリアル値で昇順（右側列が新しい日付）になっている！という前提であれば
↓の数式でも大丈夫だと思います。

=IF(COUNTA(M2:FU2),MAX(IF(M2:FU2<>"",M$1:FU$1)),"")

この数式も配列数式ですので、Ctrl＋Shift＋Enterで確定します。

これでもまだお望みどおりにならないでしょうか？m(_ _)m

この回答へのお礼

tom04さん
何度もご回答ありがとうございました。おかげで目的が達成できました。

お礼日時：2017/02/14 11:12

No.10 回答者： enunokokoro 回答日時： 2017/02/10 12:33

私がNo.9で提示した式

　=IF(COUNTA($A2:$FU2),INDEX($M$1:$FU$1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN($L1)),0))),"")

No.3さんが提示した配列数式
　{=IF(COUNTA(M2:FU2),INDEX(M$1:FU$1,,MAX(IF(M2:FU2<>"",COLUMN(M1:FU1)-COLUMN(L1)
))),"")}

上記の式を使って検証しているのですか？
私の式にてエラー「#VALUE!」が出るのは、IF(COUNTA(行の範囲)の
式を追加していなかったときの式のときだけだと思います。
上記式に変更すればエラーは出ないと思いますよ。

No.3さんが提示した配列数式も値がない場合にM列の値が出るのは、
IF(COUNTA(行の範囲)　の式を入れないでINDEXからの式のみにした
場合の結果だと思います。
COUNTA関数での範囲に空白しかない場合にはFalseになるので、""で
指定した結果が出るはずなので。

添付画像の一部に間違った書き方があるので修正です。
M6列 → M6セル
M7列からFU7列まで → M7セルからFU7セルまで

No.9 回答者： enunokokoro 回答日時： 2017/02/09 16:34

No.4の回答者です。

Excelについての回答をするようになったのが最近なので、回答内容が
不足気味でしたね。

私がした回答内容では、行ごとのデータにおいて全くデータがない行を
考慮していませんでした。
No.3の回答者さんのように、考慮した回答にて数式を提示していれば、
質問者さんを混乱させないようにできたかもしれませんね。
　=IF(COUNTA(行の範囲),条件の数式,"")
を使えば、空白行がある場合に""にしてエラーを出さないようにできる
ので、これを組み合わせることで対応できるますね。
No.3の回答者さんの数式がスマートだと思いますから、私が提示をした
数式は参考程度と思ってくださいね。

さて、追加補足の疑問について、私なりに追加説明しますね。

私の回答にある「配列数式」は、[ Ctrl＋Shift＋Enter ]キーの配列数式
の意味ではなく、「配列のある数式」という意味で使ってしまいました。
混乱させる回答で御免なさい。

No.6でのNo.3さんの修正をした数式と私の数式の違いについてですが、
IF関数を使わなければ結果は同じものになります。
　｛=INDEX(M$1:FU$1,,MAX(IF(M2:FU2<>"",COLUMN(M1:FU1)-COLUMN(L1)
)))｝
のように配列数式にすれば、結果はM1の情報「あ」になります。
エラー「#VALUE!」が出るか出ないかの違いは配列数式にするかどうか
によるものだと思います（違っていたら御免なさい）。

> (INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN(L1)),0)
における最後の0の意味は、INDEX関数の引数を省略できないので、
特定の列を指定しないという意味で0を使うようです。
(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN(L1)),)　の
ようにカンマ後ろの0を記載しないでも、範囲における指定行で特定列を
指定しないようになるので、結果的に（範囲内での）行全体という結果を
得ることができるのだと思います。


以下はデータのない行を考慮した私の最終的な修正の数式です。

　=IF(COUNTA($A2:$FU2),INDEX($M$1:$FU$1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN($L1)),0))),"")

参考程度に利用してみてくださいね。

No.8 回答者： enunokokoro 回答日時： 2017/02/08 20:53

No.4の回答者です。

修正すべき数式の一部が間違っていましたね
> ヘッダーだと思ったのでINDEX($M$1:$FU$1)にして
あっていますよ。私のほうが修正間違いをしていました。

　=INDEX($M$1:$FU$1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN(L1)),0)))

が正しいです。
No.7の回答者のアドバイスで基本的なことが書かれていますね、
内容が重複しますが、私なりのアドバイスをしますね。

数式を修正するコツですが、COLUMN関数のことを理解することが重要
です。
　=COLUMN(A1)　→1
　=COLUMN(M1)　→13
　=COLUMN(FU1)　→177
になることはすでにアドバイスされているので理解できますね。
これがCOLUMN関数で得られる列番号です。
https://dekiru.net/article/4452/

COLUMN($M2:$FU2)-COLUMN(L1)
は配列数式として利用したもので、
　=COLUMN($M2)-COLUMN(L1)　～　=COLUMN($FU2)-COLUMN(L1)
を求めるための式になります。値のみにした場合
　=13-12　～　=177-12
までの値を出して、範囲指定した列内の何番目なのかを取得します。

($M2:$FU2<>"")はTRUEまたはFALSEを出すもので、範囲内のセルが
空白かどうかを判定して、空白だった場合はCOLUMN関数で得た値と
乗算した結果がFALSEなので0になります。
MAX関数において最大値を得るときに空白セルは最小となります。
最大はFU2セルに値があった場合の
　=177-12=165となり、M列から165番目が最大になります。
FU2セルが空白の場合は、その前のセルが最大になることは理解する
ことができると思います。

INDEX関数では、
　=INDEX(範囲,行番号,列番号)
を指定するものですが、行が1行分しかない範囲の場合には行番号を
省略できるので、
　=INDEX(範囲,列番号)
で範囲内の列番号を指定すれば、その列の値を取得できます。
この場合の列番号は何番目なのかを指定するものなので、
　=INDEX(範囲,何番目なのかを指定)
ということになります。

このことから、
　=INDEX(1行目の範囲の値,MAX(INDEX((指定行の範囲<>"")*(COLUMN(指定行の範囲)-開始列の前の列番号),0)))
のようにすることで、指定行の指定範囲での空白以外の最大の列番号
（何番目）を取得したものから、INDEX関数で1行目の指定範囲の最大
の列番号の値を取得しているのです。

なので、
①については何番目なのかを得るための計算式の方法ですね。
②はM列から開始した場合は12を引かないとならないために
　=COLUMN(L1)で得られる列番号の値のみが必要で、オートフィルを
した場合でも修正する必要はありません。（常に12を得ればよいだけ）
③はヘッダー部分なので絶対参照にする必要があります。私の書いた
ものが修正途中で消してしまったことによる間違いでした。
No.6の添付画像での数式バーでは正しい数式になっていますよね。

No.7 回答者： tom04 回答日時： 2017/02/08 19:53

No.3です。

No.6さんがすでに数式の修正をしていただいているので・・・
数式の説明を少しだけ！

基本はINDEX関数です。
INDEX関数で範囲指定した「何番目」を返すか？というコトになります。

↓の画像で説明します。
FU列まで画像をアップすると小さくて判りにくいと思いますので、適当な範囲でやめています。

INDEX関数の範囲はM1～T1の範囲となり、M1が1番目・N1が2番目・・・となります。
2行目、M2セルに
=COLUMN()
という数式を入れ右へコピーすると単純に列番号が表示されます。

5行目、M5セルには
=M4<>""
という数式を入れこれも右へコピーすると、空白セル以外は「TRUE」となり、空白セルは「FALSE」（＝０）となります。

この「TRUE」の列の最大の列番号を返したいのですが
単純に列番号を掛け算してしまうと
画像の場合は「19」というとんでもない数値が返ってきます。
INDEX関数の範囲には「19番目」がないので当然エラーになってしまいます。
そこでU4セルには
=INDEX(M$1:T$1,,MAX(IF(M4:T4<>"",COLUMN(M4:T4)-COLUMN(L4)))
（配列数式です）
としています。
＞COLUMN(M4:T4)-COLUMN(L4)
のようにM列が「1番目」になるように列合わせが必要になってくるわけです。
INDEX関数の範囲指定の最初の列がM列と判っているのであれば
＞COLUMN(M4:T4)-12
としても同じ結果になります。

列数がいくら増えても同じ理屈です。

以上長々と書きましたが
この程度でご理解いただけたでしょうか？m(_ _)m

No.6 回答者： enunokokoro 回答日時： 2017/02/08 12:36

No.4の回答者です。

COLUMN関数は列番号を取得するものなので、参照する範囲の指定が
A列からでないと取得した値が違ってきます。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/kansu …
補足の例だとM列から開始しているので、
　COLUMN($M2:$FU2)-COLUMN($L2)
のように、列の値がM列からL列分を引いたA列になるようにしておいて
計算するようにします。
（　COLUMN($M2:$FU2)-COLUMN($M2)+1でも同じ　）
それと、MAX関数の中にある参照範囲は行単位で指定してください。

なので、以下のように数式を修正します

　=INDEX($M1:$FU1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN(L1)),0)))

これで2行目の最終列の1行目の文字列が取得できます。
3行目以降は、その数式をオートフィルでドラッグすれば、参照する行が
ずれてくれます。
http://www.eurus.dti.ne.jp/yoneyama/Excel/input_ …

No.3の数式も同じで参照範囲の指定が間違っていますので修正。

　｛=IF(COUNTA(M2:FU2),INDEX(M$1:FU$1,,MAX(IF(M2:FU2<>"",COLUMN(M1:FU1)-COLUMN(L1)
))),"")｝

COLUMN関数では列番号の値を取得するだけなので、以下の数式でも
結果は同じです。

　=INDEX($M1:$FU1,MAX(INDEX((M2:FU2<>"")*(COLUMN(M1:FU1)-COLUMN(L1)),0)))

No.5 回答者： yuji3690 回答日時： 2017/02/08 12:03

№2です。

=VLOOKUP($M$1,$M1:$FU1,SUMPRODUCT(((M2:FU2)>0)*1,(COUNTBLANK(INDIRECT(ADDRESS(ROW(),COLUMN(M2:FU2),4)&":FU"&ROW()))=COLUMN(FU2)-COLUMN(M2:FU2))*1,COLUMN(M2:FU2)-COLUMN(N2)))
でどうでしょうか？

No.4 回答者： enunokokoro 回答日時： 2017/02/06 18:41

A列からY列までなら、こちら↓のような式でも1行目の値が出ます。

=INDEX($A$1:$Y$1,MAX(INDEX(($A2:$Y2<>"")*COLUMN($A2:$Y2),0)))

添付画像ではA列からO列までで1行目の値を出しています。

No.3 回答者： tom04 回答日時： 2017/02/06 18:21

こんばんは！

Z2セルに
=IF(COUNTA(A2:Y2),INDEX(A$1:Y$1,,MAX(IF(A2:Y2<>"",COLUMN(A1:Y1)))),"")

配列数式ですので、Ctrl＋Shift＋Enterで確定！
Z2セルのフィルハンドルで下へコピーしてみてください。m(_ _)m