どのようにExcelのOFFSET-MATCH関数を使用してルックアップ値に

投稿日: 2020年10月22日 2020年11月12日
タグ: lookup-formulas

VLOOKUP、HLOOKUP、およびINDEX-MATCHは、Excelテーブルの値を検索するための有名で一般的な方法です。ただし、Excelで値を検索する方法はこれらだけではありません。この記事では、ExcelでOFFSET関数をMATCH関数と一緒に使用する方法を学習します。はい、正しくお読みください。Excelの悪名高いOFFSET関数を使用して、Excelテーブルの特定の値を検索します。

一般式、

=OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0))

これを注意深く読んでください：

StartCell：*これはルックアップテーブルの開始セルです。 A2：A10の範囲でルックアップする場合、StartCellはA1になります。

RowLookupValue：これは、StartCellの下の行で検索するルックアップ値です。 RowLookupRange：これは、RowLookupValueを検索する範囲です。 StartCell（A2：A10）より下の範囲です。 ColLookupValue：*これは、列（ヘッダー）で検索するルックアップ値です。

ColLookupRange：*これは、ColLookupValueを検索する範囲です。これは、StartCellの右側の範囲です（B1：D1など）。

だから、理論は十分です。例から始めましょう。

例：ExcelテーブルからOFFSETおよびMATCH関数を使用した売上のルックアップここに、さまざまな月にさまざまな従業員が行った売上のサンプルテーブルがあります。

今、私たちの上司は、6月にId1004によって行われた販売を行うように私たちに求めています。これを行うには多くの方法がありますが、OFFSET-MATCH式について学習しているので、それらを使用して目的の値を検索します。

上記の一般式はすでにあります。このテーブルでこれらの変数を特定する必要があります。

ここではStartCellはB1です。 RowLookupValueはM1です。 RowLookupRangeはB2：B1o（スタートセルの下の範囲）です。

ColLookupValueはM2セルにあります。 ColLookupRangeはC1：I1（StartCellの右側のヘッダー範囲）です。

すべての変数を特定しました。それらをExcelの数式に入れましょう。

この数式をセルM3に記述し、Enterボタンを押します。

=OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0))

エンターボタンを押すと、すぐに結果が得られます。 6月に行われたID1004の販売は177です。

どのように機能しますか？

`link：/ counting-the-offset-function-in-excel [OFFSET function]`の作業は、指定された開始セルから指定された行と列に移動し、そのセルから値を返すことです。たとえば、OFFSET（B1,1,1）と書くと、OFFSET関数はセルC2（1セル下、1セル右）に移動して値を返します。

ここでは、formulaOFFSET（B1、MATCH（M1、B2：B10,0）、MATCH（M2、C1：I1,0））があります。最初の `link：/ lookup-formulas-excel-match-function [MATCH function]`は、範囲B2：B10のM1（1004）のインデックス（4）を返します。

これで、式は、OFFSET（B1,4、MATCH（M2、C1：I1,0））になります。次のMATCH関数は、範囲C1：I1のM2（ ‘Jun’）のインデックスを返します。これはi 7です。式はOFFSET（B1,4,7）です。これで、OFFSET関数は、4つのセルをセルB1に移動し、セルB5に移動します。次に、OFFSET関数は7左に移動してB5に移動し、I5に移動します。それだ。 OFFSET関数は、177のセルI5から値を返します。

このルックアップ手法の利点は？

これは速いです。この方法の唯一の利点は、他の方法よりも高速であるということです。 INDEX-MATCH関数またはVLOOKUP関数を使用して同じことを行うことができます。しかし、いくつかの選択肢を知っておくのは良いことです。

いつか重宝するかもしれません。

そうですね、これが、OFFSET関数とMATCH関数を使用してExcelテーブルの値をルックアップする方法です。それが十分に説明的だったと思います。この記事またはその他のExcel / VBA関連のトピックについて疑問がある場合は、以下のコメントセクションで質問してください。

link：/ lookup-formulas-use-index-and-match-to-lookup-value [インデックスとMATCHを使用して値を検索する]：* INDEX-MATCH式は、指定されたテーブルの値を動的かつ正確に検索するために使用されます。これはVLOOKUP関数の代替であり、VLOOKUP関数の欠点を克服します。

link：/ Tips-sum-by-offset-groups-in-rows-and-columns [行と列のOFFSETグループによる合計]：OFFSET関数を使用して、セルのグループを動的に合計できます。これらのグループは、シートのどこにあってもかまいません。

link：/ lookup-formulas-retrieveing-every-nth-value-in-a-range [Excelで範囲内のN番目ごとの値を取得する方法]：ExcelのOFFSET関数を使用して、交互の行から値を取得できます。列。この式は、ROW関数を使用して行を交互に表示し、COLUMN関数を使用して列を交互に表示します。

link：/ counting-the-offset-function-in-excel [ExcelでOFFSET関数を使用する方法]：OFFSET関数は、多くのユーザーから過小評価されている強力なExcel関数です。しかし、専門家は、OFFSET関数の能力と、この関数を使用してExcelの数式でいくつかの魔法のタスクを実行する方法を知っています。 OFFSET機能の基本は次のとおりです。