ExcelのOFFSET関数の使い方がわからない方もいるかもしれません。

Microsoft Excel の OFFSET 関数は、セル参照だけでなく、他の多くのことに役立つことをご存知ですか? 以下は、あなたが知らないかもしれない Excel での OFFSET のユニークな使用方法です。

Microsoft Excel の OFFSET 関数は、柔軟で強力なツールです。残念ながら、その複雑さと同様の関数の数のため、この Excel 関数は十分に活用されていません。

この比較的単純な機能は、ユーザーの時間と労力を節約するのに役立ちます。これにより、適切に分類されたマスター データセットから正確なデータ ポイントを簡単に取得できます。また、詳細なレポート、インタラクティブな視覚化、複雑なダッシュボードを簡単に作成することもできます。

カスタムサイズのデータ​​セットを作成する

Excel の OFFSET 関数の最も一般的な使用法の 1 つは、サイズ変更可能なデータ セットを収集することです。データセットが大きい場合、一度にデータのごく一部だけを考慮したい場合があります。

たとえば、従業員の売上合計の自動レポートを提供するには、特定の週の合計を取得する必要があります。

合計ごとに異なる SUM 関数を作成するのは非常に簡単です。ただし、OFFSET 関数を使用した方が使いやすく、再利用も早くなります。

まず、セルB6に開始したい日付を入力します。次に、レポートを実行する日数を B7 に入力します。次に、単純な SUM 関数と AVERAGE 関数を使用して、選択した期間における各従業員のより多くのデータを取得します。

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

開始日時の調整はセルを編集するのと同じくらい簡単ですが、データのグループをドラッグしてサイズ変更する方がはるかに簡単です。

カスタムレポートの作成を許可します

上記と同じ方法を使用して、変数を非常に簡単にカスタマイズして完全なレポートを作成します。OFFSET 関数の最初の引数に別のシートを参照させることができます。これにより、データ入力とレポートを分離できます。

上記のアクションにより、選択したデータをあるワークシートから別のワークシートにドラッグすることもできます。さらに、現在のレポートに関連する情報のみを表示することもできます。以前と同じデータを使用して毎日の合計を示すレポートを設定するのは非常に簡単です。これは、無関係な日付のデータを取得せずに行うことができます。

セル B5 に配置された次のオフセット関数は、B2 で指定された日数を取得します。B1 で選択した週から始まり、関連する期間のデータのみが表示されます。

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

データ検証を使用して、B1 と B2 のエントリを制限し、使用可能な週を含むドロップダウン リストを Excel で作成することができます。

スプレッドシートのページネーション

OFFSET 関数を使用すると、単純なスプレッドシートのページネーションも可能になります。これは、Excel の [開発] タブの使用に関連することが多いタスクです。これにより、大規模なデータ グループの処理がより簡単かつ効率的になります。

最大 30,000 行のデータを含むスプレッドシートを操作する場合、すべてのデータを一度に調べるのは難しい場合があります。1 ～ 300 の数値範囲を受け入れるドロップダウン メニューを作成すると、1 ページあたり 100 件の結果を含むデータをページごとにすばやく分けることができます。

OFFSET 関数のアンカー ポイントは常に先頭のデータ セルに配置されます。ただし、2 番目の引数は垂直オフセットです。この値は、選択したページ番号から 100 を引いたものと等しくなります。オフセットは水平方向で、3 番目の引数は 0 に設定できます。

4 番目の引数は 100 に設定され、5 番目の引数はデータセット内の列の数です。この方法により、データテーブルの分割と管理が非常に簡単になります。

以下はページ分割されたデータの例です。1 枚のシートにはセンサーから記録された 500 件の情報が含まれ、もう 1 枚のシートにはページ分割されたレポートが含まれます。

レポートでは、セル B1 のデータ検証を使用して、内部にページが含まれるドロップダウン メニューを作成します。セル A3 の次の式は、レポートの本文を作成します。

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

上記は、あまり知られていないExcel の offset 関数の使用方法ですが、特に大量のデータを処理する必要がある場合に非常に便利です。

この記事がお役に立てば幸いです。