Excel でのオフセット関数の使用 [オフセット - マッチ コンボ、ダイナミック レンジ]

今日は Excel の OFFSET 関数を紹介したいと思います 3 つの実際の例を示します。

最初に、式の構文について説明し、次に、OFFSET 関数を使用して実際の問題を解決する方法について説明します。

はじめに

OFFSET 関数は、別のセル (参照セル) または範囲 (参照範囲) から指定された行数と列数だけ離れたセル (ターゲット セルと呼びましょう) または範囲 (ターゲット範囲) への参照を返すことができます。

下の図は、OFFSET 関数を使用してセル (左側) または範囲 (右側) への参照を返す方法を示しています。

これにより、何がターゲット セルで何が参照セルであるかが直感的にわかります。

緑色で強調表示されたセルはターゲット セルであり、黄色で強調表示されたセルはターゲット範囲で構成されます。

青色で強調表示されたセルは参照セルです。

図 1

Excel での OFFSET の意味 (構文)?

オフセット関数の構文は次のとおりです:OFFSET (参照、行、列、[高さ]、[幅])

リファレンス	必須。 参照は、オフセットが始まるセルまたはセル範囲です。セルの範囲を指定する場合、セルは互いに隣接している必要があることに注意してください。
行	必須 .行数、上または下、参照セルまたは参照範囲の左上セル。 行 正または負のいずれかになります。図 1 の左側を見てください。関数を OFFSET (C3, -1, -1) に変更すると、ターゲット セルは B2 になります。 B2 は C3 の 1 行上にあります。
列	必須。 参照セルまたは参照範囲の左上のセルの左または右の列数。 行と同様 引数、Cols の値 プラスにもマイナスにもなり得ます。 B4 を参照セル、C3 をターゲット セルに設定した場合、OFFSET 関数をどのように記述できますか?答えは OFFSET (B4, -1, 1) です。ここでは、Cols が正で、C3 が B4 の 1 列右にあることがわかります。
高さ	任意。 ターゲットが範囲の場合のみ、Height 引数を使用します。ターゲット範囲に含まれる行数を示します。高さは正の数でなければなりません。図 1 の右側から、ターゲット範囲に 2 つの行があることがわかります。したがって、その場合は Height を 2 に設定します。
幅	任意。 ターゲットが範囲の場合にのみ幅引数を使用します (図 1 の右側を参照)。ターゲット範囲に含まれる列の数を示します。幅は正の数でなければなりません。

それでは、実際の問題を解決するために OFFSET 関数を使用する方法をお見せしましょう.

ケース 1:OFFSET 関数と MATCH 関数を組み合わせた右から左へのルックアップ

VLOOKUP 関数では左から右へのルックアップしか実行できないことはよく知られています。

検索する値は、テーブル配列の最初の列に配置する必要があります。

新しいルックアップ値を追加する場合は、テーブル範囲全体を 1 列右にシフトする必要があります。別の列をルックアップ値として使用する場合は、データ構造を変更する必要があります。

ただし、OFFSET を Match 関数と組み合わせることで、VLOOKUP 関数の制限を取り除くことができます。

MATCH 関数とは何ですか? また、どのように OFFSET 関数を Match 関数と組み合わせてルックアップを行うことができますか?

Match 関数は、セルの範囲内で指定された項目を検索し、範囲内のその項目の相対位置を返します。

例として、図 2.1 (さまざまな国のさまざまな国の収益を示しています) から範囲 B3:B8 を取り上げてみましょう。

式「=MATCH (“USA”, B3:B8, 0)」は 1 を返します USA が範囲内の最初の項目であるため (セル B10 と C10 を参照)。

別の範囲 C2:F2 の場合、式「=MATCH (2015, C2:F2, 0)」は 3 を返します。 2015 は範囲内の 3 番目の項目であるため (セル B11 と C11 を参照)。

OFFSET 関数に戻ります。

セル B2 を参照セルとして設定し、セル E3 をターゲット セルとして設定すると、どのように OFFSET 式を記述できますか?

E3 は 1 です B2 と 3 の下の行 B2 の右側の列。

したがって、数式は「=OFFSET(B2, 1」のように記述できます。 、3 ）」。赤い数字をよく見てください。一致していることがわかりますか?

これが質問に対する答えです – OFFSET 関数と Match 関数を組み合わせる方法 – Match 関数を適用して、OFFSET 関数の 2 番目または 3 番目の引数として機能させることができます (セル C13 を参照)。

セル C14 は、VLOOKUP 関数を使用して同じデータを取得する方法を示しています。

2015 年の収益は第 4 に記録されていることを知っておく必要があります。 VLOOKUP 関数を記述する前に、テーブル配列 B2:F8 の列。

つまり、VLOOKUP 関数を使用するときは、データ構造について十分に理解する必要があります。

これは、VLOOKUP のもう 1 つの制限です。しかし、MATCH 関数を OFFSET 関数の引数として使用することで、列のインデックスを知る必要がなくなります。

これは、多数の列がある場合に非常に便利です。

図 2.1

次に、より複雑な例を見てみましょう。

さまざまな会社の会社名、連絡先名、および電子メール アドレスを含むテーブルがあるとします。

そして、既知の連絡先名から会社名を取得したり、既知の電子メール アドレスから連絡先名を取得したりします。何ができる？

図 2.2 を参照してください。範囲 B5:E8 には会社情報が含まれています。セル C2 とセル B3 に入力を入れることで、赤い四角の数式を使用して、連絡先の名前がわかっている場合に会社名を取得できます。

範囲 D2:E4 は、既知の電子メール アドレスで連絡先名を取得する方法を示しています。

要約すると、これら 2 つの例は、右から左へのルックアップを実行でき、検索値を右端の列に配置する必要がないことを示しています。 table 配列内の任意の列に検索値を含めることができます。

図 2.2

ケース 2:OFFSET 関数と COUNT 関数を組み合わせて計算を自動化する

列に新しい数値を追加するたびに計算を自動化する方法を紹介する前に、最初に列の最後の数値を自動的に返す方法から始めましょう。

人事部からのエントリを示す下の図を見てください。列 B の最後の数字を取得したい場合、式は「=OFFSET (C2, 9 , 0)" OFFSET 関数を適用した場合。

式から、9 であることがわかります はキー番号です。

この番号を自動的に返すことができる限り、列の最後の番号を自動的に見つけることができます。

9 列 C に数値を含むセルの数です。

COUNT 関数に精通している場合は、COUNT 関数が範囲内の数値を含むセルの数をカウントできることがわかります。

たとえば、数式「=COUNT (C3:C11)」は、セル C3 から C11 までの数値を含むセルの数をカウントします。

この場合、列全体にいくつの数字があるかを知りたいので、列 C のすべての行を含む C:C のような参照を使用する必要があります。

セル G4 と H4 を見てください。「=COUNT(C:C)」によって返される数値は正確に 9 です。 .

したがって、上記の OFFSET 関数で 9 を COUNT(C:C) に置き換えると、新しい数式「=OFFSET (C2, COUNT(C:C)] が得られます。 , 0)」(セル H5)。

返される数値は 87000 で、これは列 C の最後の数値です。

それでは、自動計算に移りましょう。列 C のすべての数値の合計が必要だとします。

数式は「=SUM (OFFSET (C2, 1, 0, 9) , 1))」は、SUM を OFFSET と一緒に使用する場合です。

9 範囲 C3:C11 の行の総数であり、セルの総数には列 C の数値も含まれます。

したがって、「=SUM (OFFSET (C2,1, 0, COUNT (C:C), 1))」のような新しい方法で式を書くことができます。

セル G10 と H10 を見てください。これら 9 人の従業員の合計給与額は $521,700 です。

セル C12 に $34,000 のような数値を入力すると、セル G5 と G10 の両方の数値がそれぞれ $34,000 と $555,700 に変更されます。

セル G5 または G10 の数式を更新する必要がないため、これを自動化と呼んでいます。

COUNT 関数は数値を含むセルの数のみを返すため、COUNT 関数を使用する場合は注意が必要です。

たとえば、「=COUNT (B:B)」は、列 B に数値を含むセルがないため、9 ではなく 0 を返します (セル G3 と H3 を参照)。

列 D には数値を含む 10 個のセルが含まれており、「COUNT (D:D)」によって返される数値も 10 です。

しかし、列 C で行ったように列 D の最後の数値を取得したい場合は、数値 0 を取得します (セル G8 と H8 を参照)。

明らかに、0 は私たちが望むものではありません。どうしたの？セル D13 はセル D2 から 10 行ではなく 11 行離れています。

これは、「=OFFSET (D2, COUNT (D:D) + 1 , 0)」をセル G7 に挿入します。

要約すると、計算の自動化を可能にするために COUNT 関数を OFFSET 関数と一緒に使用する場合、数値は互いに隣接している必要があります。

図 3

ケース 3:OFFSET 関数を使用してダイナミック レンジを作成する

会社の月間販売台数をグラフ化したい場合、図 4.1 は現在のデータと現在のデータに基づいて作成されたグラフを示しています。

毎月、C 列の最後の数値の下に、直近の月の販売台数が追加されます。

グラフを自動的に更新する簡単な方法はありますか?

チャートを更新するための鍵は、OFFSET 関数を使用して、Units Sold 列のダイナミック レンジ名を作成することです。

新しいデータが入力されると、ユニットの販売の動的範囲にはすべての販売データが自動的に含まれます。

図 4.1

ダイナミック レンジを作成するには、[数式] タブをクリックし、Name Manager を選択します または 名前を定義 .

新しい名前の下 [名前の定義] をクリックするとダイアログ ボックスが表示されます .

ネーム マネージャーを選択した場合 、New もクリックする必要があります 以下の新しい名前を作成します ダイアログ ボックスが表示されます。

図 4.2

「名前:」 」入力ボックスに、ダイナミック レンジ名を入力する必要があります。そして、「Refers to:」 入力ボックスに、販売台数値に基づいて値のダイナミック レンジを生成する OFFSET 数式「=OFFSET (Figure4!$C$2, 1, 0, COUNT (!$C:$C), 1)」を入力する必要があります。列 C に入力します。

デフォルトでは、名前はワークブック全体に適用され、ワー​​クブック内で一意である必要があります。

ただし、範囲を特定のシートに限定したいと考えています。

したがって、ここでは「Scope:」で図 4 を選択します。 」入力ボックス。 [OK] をクリックした後 、ダイナミック レンジが作成されます。

新しいデータが入力されると、すべての販売データが自動的に含まれます。

チャート内の任意のポイントを右クリックし、[データの選択] を選択します。

図 4.3

プロンプトでデータを選択 ソース、Series1 を選択 そして編集。

図 4.4

次に、図 4.5 に示すように「=Figure4!Units」と入力します。

図 4.5

最後に、セル C13 に 11 を入力してみましょう。グラフが変更され、値 11 が含まれていることがわかります。

新しいデータが追加されると、グラフは自動的に変更されます。

図 4.6

続きを読む…

• Excel の Offset(…) 関数と例

作業ファイルをダウンロード

以下のリンクから作業ファイルをダウンロードしてください。

Excel-Offset-Function.rar