エクセルで1番シンプルで右端・最下部・最終列・行のデータを抽出・求める方法

今回は対象範囲での1番右端・下にあるデータの抽出方法についてまとめています。
データを右方向・下方向に入力している場合など最新データの抽出するのに役立ちます。

例えば累積して入力する仕様であれば「MAX関数」で対応できますが
単体で数値を入力している場合は「MAX・MIN関数」を使用しても求めることができません。

そこで今回は関数を活用して右端・下のデータを抽出する方法についてまとめています。

それでは見ていきましょう。

■数値の最終列・行を求める

・関数を使用して最終列・行を求める

今回紹介する方法はLOOKUP関数を活用する方法です。
様々な方法がありますが、この方法が1番シンプルで簡単です。

では見本を見てみましょう。

4月から3月までの売り上げを3行目に入力しているシートになります。
今回は8月まで入力されているので「B7」の「最新の売り上げ」に8月のデータが出力できればゴールです。

ではLOOKUP関数の仕組みを見てみましょう。

=LOOKUP(検索値,検索範囲,[対応範囲])

通常のLOOKUP関数は検索範囲に検索値が当てはまる場合、対応範囲の結果を出力する関数になっています。
しかしLOOKUP関数にはあまり知られていない機能が2つあります。

マイクロソフト公式の記事を引用してみてみましょう。

①LOOKUP では、検査値が見つからない場合、検査範囲内で検査値以下の最大値が、一致する値として使用されます。
②検査値 が検査範囲に含まれる最小値よりも小さい場合は、LOOKUP では、エラー値#N/A が返されます。

ここで注目してほしいのは①の「検査値が見つからない場合、検査範囲内で検査値以下の最大値が、一致する値として使用」の部分です。

つまり②のエラーを回避するため、想定される数値以上の数値を検索値としてLOOKUP関数を使用すれば、一致する検索結果は見つからないため、①の条件を満たし、右端・下のデータを出力するといった仕組みです。

では関数に当てはめてみましょう。
今回は「9999」を超える売り上げは存在しないとし、検索値を「9999」にします。

範囲は入力する「C3からN3」つまり「C3:N3」です。

では打ち込んでみましょう。結果はこのようになります。

C3からN3で一番右端の「2315」を出力することができました。
最終行のデータを求める際も同じ考え方です。

では見本で検索値を「2000」にした例を見てみましょう。

下から順に検索していきますが検索値は「2000」なので
8月の「2315」、7月の「7254」をスルーして「2000」以下に当てはまる
「1547」が出力されました。これはこれで活用法がありますね。

・よくみる検索値「10＾10」の意味と活用法について

ある記事では検索値を「10＾10」にすればいいという内容を結構見かけました。
こちらの方法は「10乗を10回繰り返す」といった意味であり、「10000000000」を意味しています。

打ち込む文字も5文字で「10000000000」以下を網羅できるのでとても便利です。
しかし注意点として「10000000000」以上の数値には対応できません。

そこでLOOKUPを活用して右端・下の数値を抽出する一番美しい式はこのようになります。

再度、関数の仕組みを見ましょう。

=LOOKUP(検索値,検索範囲)
置き換えると
=LOOKUP(MAX(C3:N3)+1,C3:N3)

説明として検索値をMAX関数で最大値を求め、「+1」することにより
検査値が検索値を上回ってしまい、スルーするといった不具合がなくなります。

もちろん最大値の目途がついている場合はその数字を検査値にしてもいいですし
「99＾99」と検索値を膨大な数値にしても構いません。

■文字の最終列・行を求める

今度は文字の最終列・行を求めます。
先ほどは「=LOOKUP(検索値,検索範囲)」に対して
「=LOOKUP(考えられる最大値,検索範囲)」と合わせました。

しかし文字では数値の最大値を打ち込んでも検知しません。

では先ほどの見本に文字を打ってみましょう。

二万、四万などは文字のため最大値を検知できずエラーになっていしまいました。
では考えられる最大値には何を当てはめればよいのでしょうか。

実は文字にも大きさの順があります。
その文字は「伸ばし棒のー」です。半角ではないので注意してください。

では関数に当てはめてみましょう。
「=LOOKUP(“ー”,C3:N3)」

では打ち込んでみたいと思います。

結果はこのようになります。

最終列のデータである「八万」を出力することができました。

しかしこの関数式では注意点があります。
「－」は文字の最大値でありますが、先頭に「－」が続いた場合は最大値を上回ってしまうため検知できなくなります。

その場合は「－」を2つ並べることでーが先頭についた最大値を上回ることができます。
「=LOOKUP(“ーー”,C3:N3)」

いたちごっこになりますが
「ー」が先頭に2つ並んだ場合は検知できなくなりますが「ー」を3つ並べれば大丈夫です。
あまりないとは思いますが、、、

補足として行方向の最終行の検出も可能です。

■まとめ

いかがだったでしょうか。
フォーマットがあった状態で最新データを出力したい場合にとても便利な方法です。
是非参考にしてみてください。

それでは次回の記事でお会いしましょう。