Excelで別シートのデータを一定間隔で取得したい場合、単純なセル参照ではコピーしたときに思った位置へ反映されないことがあります。例えば、日経データ側のB列へWEB貼付シートのC列データを1列ずつ取得し、C列へはF列のデータを3列間隔で取得するといった処理では、INDEX関数やCOLUMN関数を組み合わせることでフィルハンドルによる連続コピーが可能になります。この記事では、別シートから規則的な間隔でデータを取得するExcel数式の作り方を解説します。
Excelで一定間隔のデータを取得するときの考え方
別シートからデータを取得する場合、通常は「=WEB貼付!C10」のようなセル参照を使います。しかし、この方法ではコピーすると参照先が1行ずつずれるだけで、3列ごとなど特殊な間隔で取得することはできません。
今回のように、WEB貼付シートのC10、F10、I10のように3列間隔でデータを取得したい場合は、参照する列番号を数式で計算する必要があります。
ExcelではINDEX関数を使うことで、指定した範囲の中から任意の位置のデータを取り出すことができます。さらにCOLUMN関数やROW関数を組み合わせることで、コピーしても規則的に参照先を変更できます。
B6からWEB貼付C10を1列ずつ取得する数式
日経データシートのB6へWEB貼付シートのC10の値を表示したい場合、単純な参照であれば以下のようになります。
=WEB貼付!C10
しかし、下方向へコピーして別の行のデータを取得したい場合は、INDEX関数を利用すると管理しやすくなります。
例えばWEB貼付シートのC10:C70から順番に取得する場合は、以下の数式をB6へ入力します。
=INDEX(WEB貼付!$C$10:$C$70,ROW(A1))
この数式を下方向へコピーすると、B7ではC11、B8ではC12というように自動的に参照する行が変化します。
C6へWEB貼付F10を3列ごとに取得する数式
WEB貼付シートのF10、I10、L10のように3列ずつ間隔を空けて取得する場合は、INDEX関数で列番号を計算します。
C6セルへ以下の数式を入力します。
=INDEX(WEB貼付!$F$10:$BZ$10,1,(COLUMN(A1)-1)*3+1)
この数式を右方向へコピーすると、参照先が以下のように変化します。
| コピー位置 | 参照先 |
|---|---|
| C6 | WEB貼付 F10 |
| D6 | WEB貼付 I10 |
| E6 | WEB貼付 L10 |
COLUMN(A1)はコピーするごとに1、2、3と増えるため、それを3倍することで3列間隔の参照が可能になります。
行方向に60件程度のデータを取得する場合
1年間分など60行程度のデータを処理する場合でも、基本的な考え方は同じです。INDEX関数で取得範囲を固定し、ROW関数やCOLUMN関数で参照位置だけを変化させます。
例えば、WEB貼付シートのC10:C69まで60件のデータがある場合、以下のように設定できます。
=INDEX(WEB貼付!$C$10:$C$69,ROW(A1))
この数式を60行分コピーすれば、自動的に順番にデータを取得できます。
大量のデータを扱う場合は、元データの範囲を絶対参照（$記号）で固定しておくことが重要です。固定しない場合、コピー時に参照範囲がずれてしまいます。
数式をコピーするときによくある失敗例
別シート参照の数式を作成するときによくある失敗は、参照範囲を固定していないことです。
例えば、=INDEX(WEB貼付!C10:C70,ROW(A1))のように$記号を付けずにコピーすると、数式を移動した際に参照範囲もずれてしまいます。
そのため、データ範囲は$C$10:$C$70のように絶対参照にしておくと、フィルハンドルで連続コピーしても安定して動作します。
まとめ｜INDEX関数を使えば規則的なデータ取得を自動化できる
Excelで別シートから1列ごと、または3列ごとにデータを取得したい場合は、INDEX関数とROW関数・COLUMN関数を組み合わせることで簡単に自動化できます。
B6でWEB貼付のC列データを順番に取得する場合はINDEX関数とROW関数、C6以降でF列から3列間隔で取得する場合はINDEX関数とCOLUMN関数を利用すると、フィルハンドルで連続コピーできます。
データ量が60行程度あっても、一度正しい数式を作成すれば手作業で入力する必要はありません。Excelの参照機能を活用して、定期的なデータ更新作業を効率化しましょう。
コメント