Excelで条件付きで列の最後の値を返す方法 - TechRepublic

誰もが面倒な手続きを踏んで仕事を終わらせたいとは思いませんが、時にはシンプルな解決策が見つからないこともあります。解決策が迅速かつ簡単に見つからない場合、他に方法がないと思い込み、複雑な配列関数やコードに頼ってしまうことがよくあります。良い例としては、条件付きで列の最後の値を返そうとする場合が挙げられます。なぜなら、一致する行が必ずしも最後の行であるとは限らないからです。このような条件を追加すると、処理がはるかに難しくなるように感じます。この記事では、実際に機能するシンプルな方法をいくつか紹介しますが、それらは優れた解決策とは言えません。その後、少し手間がかかるだけで実現できる、より動的な解決策を紹介します。

私はWindows 10 64ビット版のソリューションでMicrosoft 365を使用していますが、以前のバージョンも使用できます。ご自身のデータを使用することも、デモ用の.xlsxファイルと.xlsファイルをダウンロードすることもできます。ただし、メニュー版では、動的セクションに必要なTableオブジェクトがサポートされていません。この記事は、フィルタリングや関数の入力といった基本的なExcelスキルがあることを前提としています。ブラウザ版では、この記事で説明するすべての機能がサポートされています。

参照: Excel パワーユーザーガイド(TechRepublic Premium)

最後に用語の定義

「最後」という言葉の意味は分かっていますが、データの文脈の中でそれを定義することが重要です。データセット内で最後に一致するレコードのことでしょうか？最新の日付のことでしょうか？それとも最小値のことでしょうか？このシンプルなデータセットを見てみると、これら3つの質問への答えはすべて「はい」である可能性があることがわかります（図 A）。

図A

実際には、最小値と日付の値は除外できます。なぜなら、最小値を求めることになるからです。これは最後の値とは異なります。つまり、条件に一致する最後のレコードが残ります。

シンプルだがダイナミックではない

Acme社とABC社という2つの仕入先があり、それぞれのデータセットの最終レコードの日付と請求金額を取得したいとします。VLOOKUP ()関数を思い浮かべるかもしれませんが、この関数は最初に一致する値を返します。関数に最後に一致する値を強制的に見つけさせる方法はありません。

データを制御できるなら、簡単な解決策があります。レコードの順序を逆にしてVLOOKUP()を使うと、逆順に並べられたデータセットから最後の（最初の）レコードが見つかります。レコードの順序を逆にする最も簡単な方法は、補助列に連続した数値のセットを入力することです（図B）。

図B

次に、日付と請求書を返すVLOOKUP()関数を追加しましょう（図B参照）。ルックアップ関数は2つありますが、違いは返される列のみです。

G3: =VLOOKUP($F3,$B$3:$D$18,3,FALSE)

H3: =VLOOKUP($F3,$B$3:$D$18,2,FALSE)

3行目の関数を4行目にコピーします。現時点では、これらの関数は両社の最初の一致レコードを返します。最後の一致レコードを取得するには、追加した補助列で降順ソートを実行し、データセットを反転します。図Cが結果です。VOOKUP() 関数は「最後の」一致レコードの値を返すようになりました。これはシンプルな解決策ですが、動的ではありません。ソートを忘れないようにする必要があり、理想的ではありません。

図C

必要なのがそれだけであれば、シンプルなフィルターを使えばすぐに視覚的な情報が得られます（図D）。そのためには、データセット内の任意の場所をクリックし、「データ」タブをクリックします。「並べ替えとフィルター」グループで「フィルター」をクリックします。次に、「ベンダー」列のドロップダウンリストを使って、一度に1社だけ選択します。

図D

動的

VLOOKUP() とフィルターを使ったソリューションは、部分的な解決策に過ぎません。並べ替えを覚えたり、視覚的なヒントを得たりするだけでは不十分です。これらの部分的な解決策を組み合わせることで、データセットをフィルタリングする以外に追加の操作を必要としない、動的な優れたソリューションを作成できます。このソリューションでは、両方のデータセットを同時に取得することはできませんが、上記のソリューションよりも使いやすいかもしれません。

データセットの上にフィルター行を追加し、データセットを合計行を持つテーブルオブジェクトに変換しました（図E）。テーブルを作成するには、データ範囲内の任意のセルをクリックし、Ctrl+Tキーを押して「OK」をクリックします。合計行を追加するには、コンテキストメニューの「テーブルデザイン」リボンをクリックし、「テーブルスタイルのオプション」グループの「合計行」オプションをオンにします。最後に、「順序列」の合計セル（サンプルシートではA20）をクリックし、ドロップダウンから「最大」を選択します。これにより、その列の最大値が返されます。メニューバージョンではテーブルオブジェクトがサポートされていないことに注意してください。

図E

フィルタリング行を作成するには、次の関数を追加します。

A1: =Table2[[合計数],[順序]]

B1: =VLOOKUP($A$1,テーブル2,2,FALSE)

C1: =VLOOKUP($A$1,テーブル2,3,FALSE)

D1: =VLOOKUP($A$1,テーブル2,4,FALSE)

これまでと同様に、VLOOKUP()関数はそれぞれの列で最初に一致する値を検索します。主な違いは、参照テーブルへの参照がTable2（作成したテーブル）への参照である点です。A1の式は、Order列の合計行の値を参照しています。この値は、フィルターされたセットの最大値を返す特別なSUBTOTAL()関数の結果です。

つまり、VLOOKUP()関数の参照値はA20の値、つまりフィルタリングされたセット内の最大値です。したがって、関数はその行から対応する値を返します。

フィルターを適用していない場合、フィルター行は常に最後の行の値を返します。では、企業名でフィルターを適用するとどうなるか見てみましょう。そのためには、「ベンダー列」ドロップダウンから一度に1つの企業のみを選択します。図Fに示すように、Acmeのフィルター適用後のデータセットは、フィルターを適用していないデータセットと同じ値を返します。これは、データセットの最後の行がAcmeのレコードであるためです。

図F