Microsoft Excelで数式をオフセット
VLOOKUP、HLOOKUP、およびINDEX-MATCHは、Excelテーブルの値を検索するための有名で一般的な方法です。ただし、Excelで値を検索する方法はこれらだけではありません。この記事では、ExcelでOFFSET関数をMATCH関数と一緒に使用する方法を学習します。はい、正しくお読みください。Excelの悪名高いOFFSET関数を使用して、Excelテーブルの特定の値を検索します。
一般式
|
= |
これを注意深く読んでください：
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ボタンを押します。
エンターボタンを押すと、すぐに結果が得られます。 6月に行われたID1004の販売は177です。
どのように機能しますか？
`link：/ counting-the-offset-function-in-excel [OFFSET function]`の作業は、指定された開始セルから指定された行と列に移動し、そのセルから値を返すことです。たとえば、OFFSET（B1,1,1）と書くと、OFFSET関数はセルC2（1セル下、1セル右）に移動して値を返します。
ここに、式があります
|
OFFSET(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、つまりI7のM2（ ‘Jun’）のインデックスを返します。これで、式はOFFSET（B1,4,7）になります。これで、OFFSET関数は、4つのセルをセルB1に移動し、セルB5に移動します。次に、OFFSET関数は7左に移動してB5に移動し、I5に移動します。それだ。 OFFSET関数は、177のセルI5から値を返します。
このルックアップ手法の利点は？
これは速いです。この方法の唯一の利点は、他の方法よりも高速であるということです。 INDEX-MATCH関数またはVLOOKUP関数を使用して同じことを行うことができます。しかし、いくつかの選択肢を知っておくのは良いことです。
いつか重宝するかもしれません。
Excelでのオフセット式の使用に関する注意事項を次に示します。
メモ：
。 OFFSETは参照のみを返し、セルは移動されません。
。行と列の両方を負の数として指定して、通常のオフセット方向を逆にすることができます。負の列は左にオフセットされ、負の行は上にオフセットされます。
。 OFFSETは「揮発性関数」であり、ワークシートが変更されるたびに再計算されます。揮発性関数を使用すると、より大きく複雑なブックの実行が遅くなる可能性があります。
。 OFFSETは#REFを表示します！オフセットがワークシートの端の外側にある場合のエラー値。
。高さまたは幅を省略すると、参照の高さと幅が使用されます。
。 OFFSETは、参照を受け取ることを期待する他の関数と一緒に使用できます。
。 Excelのドキュメントには、高さと幅を負にすることはできないと記載されていますが、負の値は機能します。
MicrosoftExcelでオフセット数式を使用する方法に関するこの記事が説明的であることを願っています。ルックアップ値と関連するExcelの数式に関するその他の記事はこちらからご覧ください。私たちのブログが気に入ったら、Facebookで友達と共有してください。また、TwitterやFacebookでフォローすることもできます。私たちはあなたから話を聞くのが大好きです、私たちがどのように私たちの仕事を改善、補完、または革新し、あなたのためにそれをより良くすることができるかを私たちに知らせてください。 [email protected]までご連絡ください。
関連記事：
link：/ lookup-formulas-use-index-and-match-to-lookup-value [INDEXと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機能の基本は次のとおりです。
人気の記事：
link：/ keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity [生産性を向上させるための50のExcelショートカット]：タスクをより速くします。これらの50のショートカットにより、Excelでの作業がさらに高速になります。
link：/ expression-and-functions-introduction-of-vlookup-function [ExcelのVLOOKUP関数の使用方法]：これは、さまざまな範囲やシートから値を検索するために使用される、Excelで最もよく使用されている関数の1つです。 。
link：/ Tips-countif-in-microsoft-excel [使用方法]
link：/ forms-and-functions-introduction-of-vlookup-function [Excel]
link：/ Tips-countif-in-microsoft-excel [COUNTIF Function]：この驚くべき関数を使用して条件付きの値をカウントします。
特定の値をカウントするためにデータをフィルタリングする必要はありません。ダッシュボードを準備するには、COUNTIF関数が不可欠です。
link：/ excel-formula-and-function-excel-sumif-function [ExcelでSUMIF関数を使用する方法]：これはもう1つのダッシュボードの必須関数です。これは、特定の条件で値を合計するのに役立ちます。