本ページはプロモーションが含まれています
EXCELでN行ごと/N列ごとにデータを参照したい！
データが少なければ「=A1」「=A4」・・・と入力してもいいけど
大量のデータでは大変ですね。
簡単にデータリンクする方法を紹介します。
Ｎ行ごと/N列ごとのデータを参照する
サンプル
参照される元データはこんな感じ。
同じことの繰り返しで12月まで用意しました。
参照する側、「まとめ」の表はこちら。
こちらも12月まで作りました。
２つのシートは別々のブックで作成しています。
これ、大事なポイントなんです。
データリンクにはOFFSET関数を使用
N行ごと、N列ごとの参照にはOFFSET関数を使います。
OFFSET関数の書式はこちら。
OFFSET(基準のセル、上下方向の移動量、左右方向の移動量、取得したい行数、取得したい列数）
これに加えてROW関数、COLUMN関数も。
| ROW | 行番号を返します |
| COLUMN | 列番号を返します |
使い方はかんたん。
例えば A3のセルに
=ROW()
と入力すると「３」が表示されます。
データ参照をやってみる
E4のセルに入力したのは
=OFFSET(元データ.xlsx!$A$5,0,(COLUMN()-5)*4,1,1)
| 基準セル | 元データ.xlsx!$A$5 |
| 上下の移動量 | 0 |
| 左右の移動量 | (COLUMN()-5)*4 |
| 取得したい行数 | 1 |
| 取得したい列数 | 1 |
基準セルの「元データ.xlsx!$A$5」
A5のセルがデータリンクしたい1つ目のデータ。
これは必ず絶対参照で指定します。
上下の移動量は「０」。
説明では列方向のデータ参照ですので
上下には移動はありません。
左右の移動量「(COLUMN()-5)*4」
ここがポイントです。
E列に入力しているため、
COLUMN()で返される列番号は５。
5 – 5 = 0
0 * 4 = 0
となってE5のセルでの左右の移動量は0になります。
このセルを右方向にオートフィルでコピーすると、
E6のセルではCOLUMN()で返される列番号は6。
6 – 5 = 1
1 * 4 = 4
となって左右の移動量は4。
基準セルから４つ右方向に移動したセルが参照されます。
つまりこういうことです。
(COLUMN()-参照するシートの列番号)*参照されるシートで飛ばしたい列数
対象にしているのが１つのセルだけなので
取得したい行数、列数は１。
あとはオートフィルで右方向にコピーすればOKです♪
Excelってどうやって勉強するの？OFFSET関数の弱点はここ
サンプルデータの紹介で
２つのシートは別々のブックで作成しています
と書きました。
その理由はOFFSET関数の弱点を紹介したかったから。
参照される元データのブックを開いていないとエラーになります。
これの対処方法は参照される元データの値を
参照するまとめのブックにリンクさせたシートを作る事です。
EXCELのバージョンアップで対応してくれるとうれしいんですけどね。
まとめ
EXCELでN行ごと/N列ごとのデータを参照する方法を紹介しました。
N列ごとの参照方法で紹介しました。
N行ごとの参照の場合はこちらです。
=OFFSET(基準セル,(ROW()-参照するシートの行番号)*参照されるシートで飛ばしたい列数,1,1)
ほかにもいろいろ書いてます
↓ 下記からご覧ください
サイトマップ
ブックマークして頂けると嬉しいです
よろしくお願いいたします
コメント