従来のすべての検索および型置換関数 VPR （VLOOKUP）, GPR (HLOOKUP), もっと露出 （マッチ） それらには重要な機能が XNUMX つあります。最初から最後まで、つまりソース データを左から右または上から下に検索します。 一致する最初の一致が見つかるとすぐに検索が停止し、必要な要素の最初の出現のみが見つかります。
最初の出現ではなく、最後の出現を見つける必要がある場合はどうすればよいでしょうか? たとえば、クライアントの最後の取引、最後の支払い、最新の注文など?
方法 1: 配列数式で最後の行を見つける
元のテーブルに日付または行のシリアル番号 (注文、支払いなど) を含む列がない場合、実際には、指定された条件を満たす最後の行を見つけることが私たちのタスクです。 これは、次の配列数式で実行できます。
ここに：
- 演算 IF （IF） 列内のすべてのセルを XNUMX つずつチェックします 顧客 必要な名前が含まれている場合は行番号を表示します。 シート上の行番号は関数によって与えられます LINE （行）ですが、テーブルの行番号が必要なため、テーブルにヘッダーがあるため、さらに 1 を引く必要があります。
- 次に、関数 MAX （最大） 行番号の形成されたセットから最大値、つまりクライアントの最新の行の番号を選択します。
- 演算 INDEX （索引） 他の必要なテーブル列から見つかった最後の番号を持つセルの内容を返します (注文コード).
これはすべて次のように入力する必要があります 配列数式、すなわち：
- 最新の更新プログラムがインストールされ、動的配列がサポートされている Office 365 では、単に押すことができます 入力します.
- 他のすべてのバージョンでは、数式を入力した後、キーボード ショートカットを押す必要があります。 Ctrlキー+シフト+入力します、数式バーに中括弧が自動的に追加されます。
方法 2: 新しい LOOKUP 関数による逆引き
私はすでに新機能に関するビデオを含む長い記事を書いています VIEW (XLOOKUP)、古い VLOOKUP を置き換えるために Office の最新バージョンに登場しました （VLOOKUP）. BROWSE の助けを借りて、私たちのタスクは非常に基本的に解決されます。 この関数では (VLOOKUP とは異なり)、検索方向を明示的に設定できます: トップダウンまたはボトムアップ – その最後の引数 (-1) がこれを担当します:
方法 3. 最新の日付で文字列を検索する
ソース データにシリアル番号または同様の役割を果たす日付の列がある場合、タスクは変更されます。一致する最後の (最下位の) 行ではなく、最新の (最大）日付。
従来の関数を使用してこれを行う方法についてはすでに詳しく説明しました。次に、新しい動的配列関数の機能を使用してみましょう。 美しさと利便性を高めるために、キーボード ショートカットを使用して元のテーブルを「スマート」テーブルに変換します。 Ctrlキー+T またはコマンド ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット).
彼らの助けを借りて、この「キラー カップル」は私たちの問題を非常に優雅に解決します。
ここに：
- 機能第一 フィルタ （フィルター） 列内のテーブルから行のみを選択します 顧客 – 必要な名前。
- 次に、関数 GRADE （選別） 選択した行を日付で降順に並べ替え、最新の取引を一番上に表示します。
- 演算 INDEX （索引） 最初の行を抽出します。つまり、必要な最後の取引を返します。
- そして最後に、外部の FILTER 関数が余分な 1 列目と 3 列目を結果から削除します (注文コード и 顧客) 日付と金額のみを残します。 このために、定数の配列が使用されます。 {0;1;0;1}、表示する列 (1) または表示しない列 (0) を定義します。
方法 4: Power Query で最後の一致を見つける
完全を期すために、Power Query アドインを使用して逆検索の問題を解決する方法を見てみましょう。 彼女の助けを借りて、すべてが非常に迅速かつ美しく解決されます。
1. キーボード ショートカットを使用して、元のテーブルを「スマート」テーブルに変換しましょう Ctrlキー+T またはコマンド ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット).
2. ボタンを使用して Power Query に読み込みます テーブル/範囲から タブ 且つ (データ — テーブル/範囲から).
3. (ヘッダーのフィルターのドロップダウン リストを使用して) テーブルを日付の降順で並べ替え、最新のトランザクションが一番上になるようにします。
4… タブ内 変換 チームを選ぶ グループ化する (変換 — グループ化) 顧客ごとのグループ化を設定し、集計関数としてオプションを選択します すべての行 (すべての行). 新しい列には好きな名前を付けることができます – たとえば 詳細.
グループ化後、クライアントの一意の名前のリストを列に取得します 詳細 – それぞれのすべてのトランザクションを含むテーブル。最初の行が最新のトランザクションになります。これが必要です。
5. ボタンで新しい計算列を追加します カスタム列 タブ 列を追加 (列を追加 — カスタム列を追加)次の式を入力します。
ここに 詳細 – これは、顧客別のテーブルを取得する列です。 0 {} 抽出する行の番号です (Power Query の行番号はゼロから始まります)。 レコードを含む列を取得します (USBレコーディング)、ここで、各エントリは各テーブルの最初の行です。
列ヘッダーに二重矢印のあるボタンを使用して、すべてのレコードの内容を展開する必要があります 最後の取引 目的の列の選択:
…そして、不要になった列を削除します 詳細 そのタイトルを右クリックして – 列を削除する （列を削除）.
結果をシートにアップロードした後 ホーム — 閉じてロード — 閉じてロードイン (ホーム — クローズ & ロード — クローズ & ロード…) 必要に応じて、最近のトランザクションのリストを含む素敵なテーブルを取得します。
ソース データを変更するときは、結果を右クリックして結果を更新することを忘れないでください – コマンド 更新して保存 （更新） またはキーボードショートカット Ctrlキー+他の+F5.
- LOOKUP 関数は VLOOKUP の子孫です
- 新しい動的配列関数 SORT、FILTER、および UNIC の使用方法
- LOOKUP 関数を使用して、行または列の空白でない最後のセルを検索する