Excel関数学習：動的統計の王様OFFSET()について話しましょう

この記事では、「動的統計の王様」とも呼ばれる OFFSET 関数について説明します。 OFFSET 関数は非常に実用的な関数であり、ドロップダウン メニュー、動的チャート、動的参照などの操作において、かけがえのない役割を果たします。 Excel テーブルの効率のかなりの部分は OFFSET によってもたらされると言っても過言ではありません。

【はじめに】

OFFSET関数はExcel関数の上級者かどうかを判断する重要な関数の一つです。実際の業務では、仕事でデータファイルを体系的かつ自動的にモデル化する必要がある場合、必然的にこの機能を使用することになります。

[関数と構文]

OFFSET 関数の機能は、指定された参照を参照系として使用し、指定されたオフセットを通じて新しい参照を返すことです。

文法: OFFSET(reference, rows,cols, [height], [width])

• ##reference が本来の基本点です

• rows はオフセットされる行数です。正の数は減少し、負の数は増加し、ゼロは変化しません。

• cols はオフセットされる列の数です。正の数は右に、負の数は左に移動し、ゼロは変更されません。

• [高さ] は、基点をオフセットした後の数行の垂直方向の拡張です。正の数は下に拡張し、負の数は上に拡張します。

• [幅]は、基点をオフセットした後の数列の水平方向の拡張です。正の数は右に拡張し、負の数は左に拡張します。

4 番目と 5 番目のパラメータを使用しない場合 (ただし、ゼロにすることはできません)、新しく参照される領域は元の基本点と同じサイズになります。

元の基本点はセルまたは領域になります。

OFFSET関数に触れたばかりの学生さんにとっては、上記のパラメータが分かりにくいかもしれないので、図を使って説明しましょう。

誰もがこの写真を見て多くの時間を費やしたと思います。まず、上の図のガイドラインに従って OFFSET 関数にデータを入力し、それが新しいエリアのアドレスと一致するかどうかを実際に確認します。

まず最初の例をテストして、正の数値がパラメーターである場合の実行結果を見てみましょう:

黄色の「新しい領域」については、検証に合格します。値は合計され、256 と等しくなります。これはセル C15 の値と一致しており、結果は正しいです。学生がこのデータをシミュレートしたい場合は、セル C15 を選択し、ツールバーの「数式 - 数式のレビュー - 数式の評価」機能を使用して、OFFSET の戻り値をより直観的に確認することもできます。 (関数内で F9 を使用することもできます。数式の OFFSET 関数部分を選択して F9 キーを押します。ここでは詳細は説明しません。)

それでは、もう一度テストしてください。 2 番目の例では、負の数値がパラメーターである場合の実行結果を確認します。

「式の評価」メソッドを使用して自分でテストし、結果がどのように変化するかを確認できます。 OFFSET関数領域が動作し、戻り値が返されます。

OFFSET の基本的な動作原理を理解すると、実際の作業で多くの操作や計算を実行するのに役立ち、この関数の参加により、Excel で多くの自動化された効果を実現できます。実際の運用におけるOFFSET関数の強力な役割を見てみましょう。

1. 基本的な一般的な使用法

他の関数の地域リファレンスとして、これは OFFSET 関数の最も基本的な使用法です。 OFFSET 関数はセル範囲を移動しませんが、オフセット拡張された範囲アドレスを返します。したがって、参照領域をパラメータとして受け取るすべての関数は、上記の例 Sum (OFFSET ()) や次の例など、OFFSET 関数の戻り値を使用できます。

#関数の原理は上記の使い方と同じなので詳細は省略しますが、OFFSET 関数で返された領域を MAX 関数のパラメータとして使用します。

2. 高度な一般的な使い方

特技①: 転置のシミュレーション TRANSPOSE 関数

TRANSPOSE 関数を使用する前に、対応するサイズの転置領域を選択する必要があります。また、Ctrl Shift Enter を使用して数式を終了する必要がありますが、これは非常に面倒です。

ここでは、上の図に示すように、OFFSET 関数を使用してこの移調の効果をシミュレートできます。

A11セル関数:

=OFFSET($A$1,COLUMN()-1,ROW()-11)

関数解析 ：

データの転置というのは、実際には「行から列」「列から行」という処理で、具体的には行番号と列番号を入れ替えるということです。元のデータの最初の列である「名前」列が、転置後の新しい領域の最初の行になります。同様に、「名前」列の各行の行番号が転置された列番号になります。 OFFSET を使用する原理は、オフセット値を取得するときに行番号と列番号の引用範囲を変更することです。

## たとえば、セル A11、COLUMN()=1、1-1=0 では、OFFSET の 2 番目のパラメータは 0 で、元の基本点の行数がオフセットされていないことを示します ( OFFSET の 2 番目のパラメータは行オフセットを示します。詳しくない場合は、前の内容を読んでください。) ROW()=11、11-11=0、OFFSET の 3 番目のパラメータは 0 で、列数がオフセットされていないことを示しているため、元の基点 A1 セルの値が引用されます。

##★ 関数を右に引いてセル B11、COLUMN()=2、2-1=1 を入力すると、OFFSET の 2 番目のパラメータが 1 になり、元の行数が 1 であることを示します。基点は下です 位置を 1 つ移動します。 ROW()=11、11-11=0、OFFSET の 3 番目のパラメータは 0 で、列数がオフセットされていないことを示します。そのため、セル B11 は、基点 A1 を下にシフトした後のセル A2 の値を参照します。

セル A11 の関数をプルダウンして入力します セル A12 では、COLUMN()=1、1-1=0 となり、行数はオフセットされません。 ROW()=12, 12-11=1、OFFSET の 3 番目のパラメータは 1 で、列番号が基点 A1 から 1 つ右にオフセットされていることを示し、セル B1 の値を参照します (その理由)数式の A1 は、すべてのセルが A1 に基づいているため、絶対参照を使用します。

同様に、マウスを使用して下にプルして右にプルして数式を入力するとき、COLUMN 関数と ROW 関数を使用して各セルのオフセットを特定し、転置効果を実現します。

スタント②：Vlookup関数の逆引きクエリ関数をシミュレートする

VLOOKUP関数の逆引きクエリは以下でほぼ完成します。配列の助けを借りますが、配列のせいで大量のデータがある場合に関数がスタックする可能性があるため、多くの学生は代わりに INDEX 関数を使用することもあります。そこで今日は、このような問題に対処するために OFFSET 関数を使用して、皆さんの知識を豊かにしましょう。

C12 セル関数:

=OFFSET($A$1,MATCH("D2568",$B$2:$B$7,0),)##関数分析:

セル A1 を元の基点として使用し、返される必要がある値は元の基点と同じ列にあるため、考慮する必要があるのは行オフセットのみです。 OFFSET 関数の場合、列オフセットを考慮する必要はありません。従業員番号は通常一意の値であるため、MATCH 関数を使用して領域 B2:B7 の番号「D2568」のシリアル番号を取得します。戻り値 4 は OFFSET 関数の行オフセットとして使用され、 OFFSET 関数 =OFFSET($A$1,4,)。列オフセットを省略した場合のデフォルトは 0、拡張幅と拡張高さを省略した場合のデフォルトは 1 (つまり 1 つのセル) A5 セルではないでしょうか。

スタント③: データリセットアップグレード版 - データ構造の再配置

F2:H2エリアに数式を入力後、Pull下に向かってデータを入力すると、右側に 1 次元のデータ テーブルが表示されます。この種のデータ再配置の問題は、実際の業務では珍しいことではありません。では、生徒はそれを解決するためにどのような方法を選択するでしょうか?逆に、著者はOFFSET関数の考え方の方が簡潔で明確であると感じています。

関数分析:

ステップ 1: 連続した名前の取得

F2 セル関数:

=OFFSET($A$1 ,INT(( ROW(F1)-1)/3) 1,)

件名は合計 3 つあるため、同じ名前が 3 回出現する必要があると判断でき、セルをドロップダウンするとF2 関数を埋めるときは、OFFSET 関数の行オフセットの 3 つのセルごとのパラメーター値が同じであることを確認する必要があります。ここでは、「除数の四捨五入」について数学的な考え方が必要です。説明を助けるために写真を挙げてみましょう:

この写真から、一連のシリアル番号がわかります。 through INT((通し番号-1)/3) 1を変換すると、右の数列が得られます（被験者が4人の場合は、3を4に変更するなど）。このシリアル番号を行オフセットの基準として OFFSET 関数の第 2 パラメータに入れると、名前列の効果を得ることができます。

ステップ 2: 同じ人に異なるアカウントを割り当てる

G2 セル関数:

=OFFSET($A$1,,MOD(ROW(G1 )- 1,3) 1)

列 F の各名前は 3 回出現するため、中国語、数学、英語の 3 つの科目を順番に周期的にリストする必要があることがわかります。最初のステップの考え方と同様に、「除数と剰余」の数学的思考を使用して効果を実現します。

上図に示すように、シリアル番号は MOD 関数によって変換され、連続した循環リストのシリアル番号が取得されます。このシリアル番号を OFFSET 関数の 3 番目のパラメーター列オフセットとして使用すると、元のデータのアカウントの内容を順次かつ循環的に取得できます。

3 番目のステップ: 名前と件名を通じて INDEX 関数をシミュレートし、元のデータから結果を導き出します

H2 セル関数:

=OFFSET($A$1,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1 , 0))

MATCH 関数をそれぞれ使用して、関連する領域のデータに対応するシーケンス番号を取得します。これは、OFFSET のオフセットとして使用され、それぞれ 2 番目と 3 番目のパラメーターに入れられます。参照点 A1 セルからのセル オフセットが、必要な勾配値です。

上記の内容から、OFFSET 関数は MATCH 関数と組み合わせて使用​​されることが多いことが簡単にわかります。 Match 関数は配列内のキーワードのシーケンス番号を見つけることができるため、OFFSET 関数のオフセットを決定するためにこの関数をよく使用します。

3. ハイエンド アプリケーションのアイデア

(動的レポート テンプレートのプロトタイプ)

高速化のために Excel を使用しますデータを地理統計的に分析し、必要なものを迅速に抽出します。次の 2 つのシナリオを想定します:

シナリオ 1:

リーダーは、特定の四半期の売上データをカウントする作業を手配しました。すぐにアクションを実行し、関数を使用します。 ;

シナリオ 2:

リーダーが作業を手配しました。売上データは四半期ごとに収集する必要があるため、事前にテンプレートを作成しました. いつ配布されるかについてはレポート次第です。あなたが非常に生産性が高いことを「中間リーダー」に知らせないでください。

2 つのシナリオがあります。どちらのアプローチを選択しますか?著者はそれが2番目であることを望んでいます。

アイデアによって表のレイアウトが決まります。これは単純なケースです。データ ソースが変更されると、対応する四半期データが自動的に調整されます。複雑なテンプレートのすべての位置で OFFSET 関数が使用されるわけではありませんが、データ領域を動的に参照する必要があるため、それを処理するために OFFSET 関数を使用するのは絶対に正しいことです。

#4. 一般的な使用例

スタント 4: 動的なドロップダウン メニューの作成

データ内 モデリング プロセスでは、ドロップダウン メニュー (またはコンボ ボックス コントロール) をよく使用します。ドロップダウン コンテンツの一意性を確保するために、INDEX SMALL IF ROW の「Taiwan Balm」関数を使用して配列から重複データを抽出します。前回の記事で説明した INDEX 関数を OFFSET 関数に置き換えた例をまだ覚えていますか?したがって、Index関数の代わりにOFFSET関数を利用できれば、OFFSET関数でも「万能」な処理が実現できます。複雑な「ドロップダウン メニュー」の作成プロセスを見てみましょう。

ステップ 1: OFFSET 関数を使用して、「台湾バーム」式の一意の値を抽出します

この式は比較的長く、次のとおりです。 :

D2 セル関数:

=IFERROR(OFFSET($A$1,SMALL(IF(ROW($A$2:$A$27)-1=MATCH($A) $2:$A $27,$A$2:$A$27,0),ROW($1:$20),9^9),ROW(D1)),),"")<br># タイガーバームフォーミュラ 今日話したいテーマではないので、ここでは触れません。重要なことは、OFFSET 関数でもこのような複製効果を実現できることを全員に知らせることです。

ステップ 2: ネーム マネージャーで OFFSET 関数を使用してデータ ソースを確立します。

Ctrl F3 キーの組み合わせを使用してネーム マネージャー ウィンドウを開き、新しい名前を作成して設定します。次の図に示すように、名前を「Area」、参照位置は「D2:D15」とします。

次に、セル G1 を選択し、Alt D L キーの組み合わせを押します。データ検証設定ボックスを開き、下図に示すように、「許可」で「シーケンス」を選択し、「ソース」に「=Area」と入力します。

OK ボタンをクリックし、そうすれば、G1 セルのドロップダウン メニューが正常に作成されます。しかし、問題も発生し、必要なものではない空のオプションがたくさんあることがわかります。

一部の生徒は、名前マネージャーで D2:D5 を選択するだけだと言うでしょう。はい、ただし、列 A の領域に新しいデータが表示されると、ドロップダウン メニューのデータが少なくなります。そのため、現時点ではこの問題に対処するために OFFSET 関数を使用します。

ネームマネージャーの「エリア」の参照位置を変更します:

=OFFSET(動的ドロップダウンメニュー!$D$1, 1, ,COUNTA(動的ドロップダウン メニュー!$D$2:$D$15)-COUNTBLANK(動的ドロップダウン メニュー!$D$2:$D$15),1)

<p>列 D の唯一の値は数式を使用して取得されるため、内部の「空のセル」は名目上「空」ではなく、数式によって取得された空であるため、COUNTIF(D2:D15, " を直接渡すことはできません) ") を使用して、値を持つセルの数を取得します。したがって、最初に COUNTBLANK 関数 (空白セルのカウント) を使用して空白セルの数を数え、次に COUNTA 関数を使用して空白以外のセルの数を数え、最後にその 2 つを減算して値が入っているセルの数を取得しました。 。得られた結果をOFFSET関数の第4引数(新規領域の展開行数)として使用することで、有効なデータを動的に参照する効果が得られます。次の図に示すように: </p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/image/563/808/587/168112286113269Excel関数学習：動的統計の王様OFFSET()について話しましょう" class="lazy" title="168112286113269Excel関数学習：動的統計の王様OFFSET()について話しましょう" alt="Excel関数学習：動的統計の王様OFFSET()について話しましょう"></p> <p>#新しい地域名が列 A に追加されると、新しいオプションも G1 のドロップダウン メニューに追加されます。効果を見てみましょう。これが必要なものだと思います。 </p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/87ef22336de5ba47e613ed48b10afa42-7.gif" class="lazy" style="max-width:90%" style="max-width:90%" alt="Excel関数学習：動的統計の王様OFFSET()について話しましょう" ></p> <p>スタント 5: グラフでの OFFSET 関数の使用</p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/9bde3eb6974a2deeb97813a3aebd809d-8.png" class="lazy" style="max-width:90%" style="max-width:90%" alt="Excel関数学習：動的統計の王様OFFSET()について話しましょう" ></p> <p>上のグラフは誰もがよく知っていると思います。 。社会人学生はグラフ作成の経験があるので、上の図の A1:B10 の領域を選択し、ツールバー - [挿入] - 縦棒グラフに移動して、凡例の内容を完成させます。 </p> <p>データ行を削除すると、縦棒グラフの系列凡例が 1 つ減ります。ただし、データ行を追加した場合は、グラフ データ ソースの範囲を変更して表示する必要があります。正しいチャート。しかし、毎回変更することはできません。そうしないと、Excel を効率的かつ迅速に使用するという本来の目的が失われてしまいます。 </p> <p>現時点でも、問題を解決するために OFFSET 関数から学ぶことができます: </p> <p>ステップ 1: OFFSET 関数を使用して、「日付列」と「数量列」のカスタム名を作成します。 「それぞれ</p> <p>名前マネージャー、それについては上で紹介したので、これ以上は言いません。 「日付列」を選択し、次のように設定します。 </p> <p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/024/9bde3eb6974a2deeb97813a3aebd809d-9.png" class="lazy" style="max-width:90%" style="max-width:90%" alt="Excel関数学習：動的統計の王様OFFSET()について話しましょう" ></p> <p> 基準位置関数: </p> <p><code>=OFFSET(Chart Series!$A$1,1 ,0 ,COUNTA(chart series!$A$2:$A$1000),1)

元のデータには数式で得られた空のセルがないため、ここでは Countblank 関数を使用します。CountA 関数は空ではないセルの数をカウントし、OFFSET 関数の 4 番目のパラメーター (新しい領域の行数) として使用できます。ここでの A2:A1000 は絶対に大きな領域を表しており、新しく入力されたコンテンツがこの範囲内に収まることが保証されます。

「数量列」を選択し、次のように同じ方法で数量のカスタム名を作成します。

ステップ 2: 名前を使用します

チャート領域の

これは OFFSET ダイナミック チャートのキーであり、名前を追加する場所は非常に重要です。

作図領域で任意の列を選択すると、編集バーにアイコンの関数記述が表示されます (チャートにも関数があることを初めて知りました)。ここで参照の範囲を変更してみましょう。

#エリアを変更するだけです。

• チャート シリーズ!$A$2:$A$10

• チャート シリーズ!$B$2:$B$10

これら 2 つの赤い部分をカスタム名に置き換えるだけです。「Chart Series!$A$2:$A$10」全体を置き換えないでください。

置換後、Enter キーを押すと、上に示すように関数が表示されます。OFFSET.xlsx はワークブックの名前です。

#他のチャートタイプも用意されているので、操作してみて印象を深めてみてはいかがでしょうか。

【編集者注】

OFFSET 関数の 5 つのパラメーターは、意味を理解していれば覚えるのは難しくありません。その戻り値は他の関数への参照として使用でき、同様に、「戻り値が数値形式」である他の関数も OFFSET 関数のパラメーターとして使用できるため、データを単独で移動できます。

この関数は Excel 関数において不可欠な役割を果たします。特に Excel モデリングを使用する必要がある場合、この関数は動的領域を参照し、データを自動的に処理するためによく使用されます。より多くの時間をかけて学ぶことは、将来の時計製造プロセスに大きな利益をもたらすでしょう。

関連する学習の推奨事項:

Excel チュートリアル

以上がExcel関数学習：動的統計の王様OFFSET()について話しましょうの詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。