エクセル関数式により、列ごと最後尾と最後尾からの２行目の値を抜き出す

　エクセル関数式により、列ごと最後尾と最後尾からの２行目の値を抜き出す。

　初めまして、よろしくお願いします。
　　　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ
１　　　　赤　　青　　黄　　緑　　黒
２　
３　　　
４　　　　１　　３　　２　　０　　９
５　　　　９　　１　　３　　４　　３
・　　　　・　　・　　・　　・　　・
・　　　　・　　・　　・　　・　　・　　
１１２　　４　　６　　１　　９　　６
１１３　　７　　７　　５　　８　　１
１１４　　６　　９　　１　　３　　７　　　
１１５　　７　　　　　２　　５　　０
１１６　　８　　　　　０　　　　　０

　という表があります。
各列の空白、もしくは０の入力されている後の値（A列では８、B列では９、C列では２、D列では５、E列では７）と最後尾の数字から２行目の値を（A列では６、B列では６、C列では５、D列では８、E列では６）を抜き出すことのできる関数式の方法がありましたら、教えていただきたく。よろしくお願いします。

No.1 回答者： mt2008 回答日時： 2010/06/05 10:22

A列の最後尾と最後尾から１つ前のは以下の様な式で取り出します。

ただし、0または空白しかない列ではエラーになります。

=OFFSET(A1,SUMPRODUCT(MAX(ROW(A5:A116)*(A5:A116<>0)))-1,0)
=OFFSET(A1,SUMPRODUCT(MAX(ROW(A5:A116)*(A5:A116<>0)))-2,0)

この回答へのお礼

　回答ありがとうございます。うまくできました。大変助かりました。ありがとうございます。

お礼日時：2010/06/05 13:34

No.2 ベストアンサー 回答者： grumpy_the_dwarf 回答日時： 2010/06/05 10:37

私のやり方だと、「ゼロより大きい」という論理値と行番号をかけ

算した配列を生成し、その最大値および三番目に大きい値が目的の
行である。という手口を使います。こちらの仕様ではE112が0で
あったときにE111が採用されます。
最後尾
=index(A1:A200,large(index(row(A4:A200)*(A4:A200>0),0),1))
最後尾から2つ上、ただし0は除外
=index(A1:A200,large(index(row(A4:A200)*(A4:A200>0),0),3))

この回答へのお礼

　回答ありがとうございます。このやり方ですと０でもエラーが出ずに大変きれいです。ありがとうございました。

お礼日時：2010/06/05 13:36

No.3 回答者： KURUMITO 回答日時： 2010/06/05 12:04

0が含まれる場合には面倒になりますね。

最後の0が3行目であっても良い場合でしたらＡ2セルに次の式を入力してＥ2セルまでオートフィルドラッグします。

=IF(INDEX(A4:A1000,COUNT(A4:A1000))>0,INDEX(A4:A1000,COUNT(A4:A1000)-2),IF(AND(INDEX(A4:A1000,COUNT(A4:A1000))=0,INDEX(A4:A1000,COUNTA(A4:A1000)-1)>0),INDEX(A4:A1000,COUNT(A4:A1000)-3),IF(AND(INDEX(A4:A1000,COUNT(A4:A1000)-1)=0,INDEX(A4:A1000,COUNTA(A4:A1000)-2)>0),INDEX(A4:A1000,COUNT(A4:A1000)-4),IF(AND(INDEX(A4:A1000,COUNT(A4:A1000)-2)=0,INDEX(A4:A1000,COUNTA(A4:A1000)-3)>0),INDEX(A4:A1000,COUNT(A4:A1000)-4),IF(AND(INDEX(A4:A1000,COUNT(A4:A1000)-3)=0,INDEX(A4:A1000,COUNTA(A4:A1000)-4)>0),INDEX(A4:A1000,COUNT(A4:A1000)-5),"")))))

この回答へのお礼

　回答ありがとうございます。大変お手数をおかけしました。参考にさせていただきたいと思います。

お礼日時：2010/06/05 13:32

No.4 回答者： hallo-2007 回答日時： 2010/06/05 12:33

配列関数で作成してみました

A2セル　最後の行の値（０を除く）　=VLOOKUP(10^6,IF(B4:B100=0,"",B4:B100),1)
の式をコピィして　Ctrl+Shift+Enter　で決定させます。
式が　{}で囲まれると思います。
結果が表示されたら、右へコピィ
A3セル　最後の行から2行上　=INDEX(B4:B100,MATCH(10^6,IF(B4:B100=0,"",B4:B100))-2)
同様に　Ctrl+Shift+Enter　右へコピィしてみてください。

No.5 回答者： hallo-2007 回答日時： 2010/06/05 13:07

No4です。

範囲の指定が間違っていましたね。
=VLOOKUP(10^6,IF(A4:A200=0,"",A4:A200),1)　　Ctrl+Shift+Enterで
={VLOOKUP(10^6,IF(A4:A200=0,"",A4:A200),1)}
こんな感じになると思いますが、状況に合わせて編集してください。

この回答へのお礼

　回答ありがとうございます。訂正文をいただき、大変参考になりました。ありがとうございました。

お礼日時：2010/06/05 13:39

No.6 回答者： tom04 回答日時： 2010/06/05 13:10

こんにちは！

一例です。

↓の画像で説明します。
H2セルに
=INDEX(A$1:A$116,LARGE(IF(A$1:A$116>0,ROW($A$1:$A$116)),1))

H3セルに
=INDEX(A$1:A$116,LARGE(IF(A$1:A$116>0,ROW($A$1:$A$116)),3))

どちらの数式も配列数式になってしまいますので、
この画面からコピー＆ペーストしただけではエラーになると思います。
セルに貼り付け後、F2キーを押す、又は貼り付けセルでダブルクリック、又は数式バー内で一度クリックします。

編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定してください。
数式の前後に｛　｝マークが入り配列数式になります。

最後にH2・H3セルを範囲指定し、H3セルのフィルハンドルで列方向にコピーすると
画像のような感じになります。

以上、参考になれば幸いです。m(__)m

この回答へのお礼

　回答ありがとうございます。うまくできました。大変助かりました。ありがとうございました。

お礼日時：2010/06/05 13:44