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

質問

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

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

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

回答No.2

grumpy_the_dwarf · ベストアンサー

私のやり方だと、「ゼロより大きい」という論理値と行番号をかけ
算した配列を生成し、その最大値および三番目に大きい値が目的の
行である。という手口を使います。こちらの仕様では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))

お礼

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

回答No.6

tom04

こんにちは！
一例です。

↓の画像で説明します。
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

お礼

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

回答No.5

hallo-2007

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

お礼

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

回答No.4

hallo-2007

配列関数で作成してみました
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.3

KURUMITO

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),"")))))

お礼

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

回答No.1

mt2008

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)

お礼

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