エクセルOFFSET関数とその使用方法は何ですか

投稿日: 2020年10月22日 2020年11月12日
タグ: tips

オフセット機能とは何ですか？

オフセット機能は情報検索機能です。特定のデータを含む巨大なテーブルがあり、そこからデータを取得する場合、そのための最良の方法は、そのワークシートまたは同じワークブックの別のシートにテーブルのオフセット式を挿入することです。したがって、オフセット取得テーブルを作成したら、「月」などのデータを入力するだけで、そのテーブルからその月の「売上」または「収入」を取得できます。

テーブルを調べることも別のオプションですが、シートとデータのシートがすべて1000列のデータテーブルを含む場合は、このオプションを検討しますか？これは、オフセットが他の機能と組み合わされる場所です。

ピボットテーブルの概念はオフセットから来ています。ピボットテーブルは一般的なテーブルであり、必要に応じて、データとグラフの特定の組み合わせが取得されます。動的テーブルも同じ方法で作成できます。これらのテーブルでは、Excelオフセット式を作成する必要があります

オフセットの構文

=OFFSET(reference,rows, columns, [height], [width])

行–情報を取得するために行数を移動するようにExcelに要求します。この場合、テーブル全体を移動する必要があるため、コンマ（、）で示される空白のままにします。それ以外の場合、列を前方に移動するには値を1として指定し、前の列を移動するには値-1を指定します。

列–これは関数を列の数に駆動するためのものです。値システムは行のシステムと同じです。

高さ–テーブルの高さ（この場合はA11）。

幅–テーブルの幅（この場合はD11）。

オフセット機能を設定した後、エラーを取り除くためにトライアルを実行することが重要です。

名前、電子メールID、生年月日、年齢を含むテーブルを見てみましょう。

テーブルは、「名前」の見出しを含むA1セルから始まります。データは、たとえばA11に対して実行されます。そして、行はD1まで実行されます。テーブル全体がA1：D11を実行します。電子メールIDを入力すると名前を取得する検索システム、または名前とその他の詳細を入力すると電子メールIDを取得する検索システムが必要です。 5つの引数を満たすことで数式を作成します。

参照点は、ルックアップを開始するセルです。この場合、A2である必要があります。それが一般的な基準です。

例：

たとえば、B4とオフセット行0の参照を指定し、オフセット列1を指定すると、C4からの値が返されます。紛らわしい？さらにいくつかの例を見てみましょう。 OFFSET関数の最後の例は、動的にSUMする方法を示しています。

セルの左右から値を取得するOFFSET関数このテーブルがあります。

ここで、2セルからB2（つまり、D2）までの値を取得したい場合。このOFFSET式を書きます：

=OFFSET(B2,0,2)

OFFSET関数は、2セルをセルB4の右側に移動し、その値を返します。下の画像を参照してください。1セル左からB2（つまり、A2）までの値を取得したい場合。このOFFSET式を書きます：

=OFFSET(B2,0,-1)

マイナス（-）記号は、逆方向に移動するオフセットを示します。

セルの下と上から値を取得するOFFSET関数したがって、上記の表でも、2セルの最愛からB3（つまりB5）に値を取得したい場合。このOFFSET式を書きます：

=OFFSET(B2,2,0)

残りの1セルからB2（つまりA2）に値を取得したい場合。このOFFSET式を書きます：

=OFFSET(B2,-2,0)

上級者向けのヒント：* OFFSETをグラフポインタとして扱います。ここで、参照は原点であり、行と列はx軸とy軸です。

範囲を動的に合計するOFFSET関数この例を見てみましょう。

上記の表では表の最後の合計行。時間の経過とともに、このテーブルに行を追加します。次に、数式の合計範囲を変更する必要があります。

ここでは、OFFSET関数を使用して動的に合計することができます。現在、セルC11には= SUM（C2：C10）があります。これを以下の式に置き換えてください。

=SUM(C2:OFFSET(C11,-1,0))

この数式は、最初のデータ行を取得してから、合計行より上の範囲を合計します。

配列を取得するためのオフセットOFFSET関数には、[高さ]と[幅]の2つのオプションの引数があります。それらを当然のことと思ってはいけません。 OFFSET関数に[height]引数と[width]引数を指定すると、2次元配列が返されます。それらの1つだけが引数として渡される場合、値の次元配列を返します。

いずれかのセルにこの数式を入力した場合：

=SUM(OFFSET(C1,1,0,9,3))

C2から下の9行、右の3列までの範囲の値を合計します。

OFFSET（C1,1,0,9,3）：これは\ {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8に変換されます、3,9; 8,9,2; 3,5,4; 6,6,5}。

したがって、結論として、オフセットは、多くの絞め殺されたスレッドを解決するのに役立つExcelの非常に便利な機能です。数式でOFFSET関数をどのように使用し、どこで最も役立ったかを教えてください。

ルックアップ関数はオフセットと同じですが、Match、Counta、IFなどの関数を使用して動的テーブルを操作します。

Excelでオフセットを使用するには、Excelの機能とそれらがどのように連携するかについてのスキルと十分な知識が必要です。エラーなしでそれをやってのけることが最大のハードルです。

これは、式の使用に関するすべての注意事項です。

注：

。この数式は、テキストと数字の両方で機能します。

。エラーのない情報を取得するために満たす必要があるオフセットの5つの引数があります。間違った式を書くと、Refエラーが発生します。

Excelオフセット関数とは何か、Excelでの使用方法を理解していただければ幸いです。こちらの数式を使用して、Excelのルックアップと一致値に関するその他の記事をご覧ください。私たちのブログが気に入ったら、Facebookで友達と共有してください。また、TwitterやFacebookでフォローすることもできます。皆様からのご意見をお待ちしております。私たちの仕事を改善、補完、革新し、より良いものにする方法をお知らせください。 [email protected]までご連絡ください。

link：/ lookup-formulas-vlookup-function-to-calculate-grade-in-excel [Excelで成績を計算するVLOOKUP関数]：成績を計算するにはIFとIFSだけが使用できる関数ではありません。 VLOOKUPは、このような条件付き計算に対してより効率的で動的です。VLOOKUPを使用して成績を計算するには、この式を使用できます。

link：/ lookup-formulas-17-things-about-excel-vlookup [17 Things About Excel VLOOKUP]：VLOOKUPは、一致する値を取得するために最も一般的に使用されますが、VLOOKUPはこれ以上のことを実行できます。 VLOOKUPについて、効果的に使用するために知っておくべき17のことを次に示します。

link：/ lookup-formulas-lookup-the-first-text-from-a-list [LOOKUP the First Text from a List in Excel]：VLOOKUP関数はワイルドカード文字で正常に機能します。これを使用して、Excelの特定のリストから最初のテキスト値を抽出できます。これが一般的な式です。

link：/ lookup-formulas-lookup-date-with-last-value-in-list [リスト内の最後の値を持つLOOKUP日付]：最後の値を含む日付を取得するには、LOOKUP関数を使用します。この関数は、ベクトルの最後の値を含むセルをチェックし、その参照を使用して日付を返します。

link：/ lookup-formulas-how-to-retrieve-latest-price-in-excel [Excelで最新の価格を取得する方法]：あらゆるビジネスで価格を更新し、あらゆる購入に最新の価格を使用するのが一般的です。販売は必須です。 Excelのリストから最新の価格を取得するには、LOOKUP関数を使用します。 LOOKUP関数は最新の価格を取得します。