行を飛び飛びに参照する数式(2)
  • Excel Version：
  • 2019
  • 2016
  • 2013
  • 2010
  • 2007
  • 2003
  • 2002
  • 2000
  • 97
  • ([2]2020-11-25)

Excel アイコン行を飛び飛びに参照、取り出す数式の解説です。

　データの行を一定の間隔で飛び飛びに取り出して使いたい場合があります。
データ量が少ない場合には、[ctrl]キーを押しながら選択してコピペすればいいのですが、データが大量だったり、定期的あるいは頻繁にその作業が発生する場合には、数式で対応したほうが効率的です。
この数式は、OFFSET関数を使って行を飛び飛びに参照する数式です。

１．数式

　下表中の式、“=OFFSET(sheet1!A$3,(ROW(B2)-ROW(B$2))*5,0,1,1)”をコピペしてお使いください。
　下記の表は、A列3行目から5行ごとのセルからデータをB列の2行目以降に取り出しています。
　“sheet1!”は元データのシート名（同一シート内での場合は無くても良い）、
　“A$3”は元データの取り出し開始セル番号、
　“B2”及び“B$2”は式の入っている最初のセル番号、
　“5”は読み出す行の飛び量（行数）です。

A B B列セルの内容
1 あ1 　 A3セルから5行飛びにB2セル以降に取り出した場合
2 あ2 あ3 =OFFSET(sheet1!A$3,(ROW(B2)-ROW(B$2))*5,0,1,1)
3 あ3 あ8 =OFFSET(sheet1!A$3,(ROW(B3)-ROW(B$2))*5,0,1,1)
4 あ4 あ13 =OFFSET(sheet1!A$3,(ROW(B4)-ROW(B$2))*5,0,1,1)
5 あ5 あ18
6 あ6 あ23
7 あ7 あ28
8 あ8 あ33
← BACK
↑ PAGE TOP

２．数式を組み立てられるようになりたい人のための解説

　A列3行目から5行ごとのセルからデータをB列の2行目以降に取り出す式を例に説明します。

　 B B列セルの内容(式) 解説
1 　 　 　
2 あ3 =A3 1. 基本の式はこれです
　 あ3 =OFFSET(A3,0,0,1,1) 2. A3セルの参照をOFFSET関数を使って表現します。
　A3セルを基準にして、1つ目の0が行のずれ量、2つ目の0が列のずれ量に該当します。
　 あ3 =OFFSET(A3,(ROW(B2)-ROW(B2)),0,1,1) 3. 行のずれ量は取り出し側のB列の行番号を使って計算することにします。
　ROW関数を使って抽出開始セル(B2)と数式があるセル(B2)の行数の差を計算する式に変えます。
　 あ3 =OFFSET(A3,(ROW(B2)-ROW(B2))*5,0,1,1) 4. 行数の差に飛び間隔(5)を掛けて行数の飛び量にします。
　B列1行に対してA列の行は5倍変化します。
　 あ3 =OFFSET(A$3,(ROW(B2)-ROW(B$2))*5,0,1,1) 5. 4.の式をコピペするとA・B列とも行が一緒に変化してしまうので永遠に5飛びにならないので、OFFSET計算のスタートになるA3セルの行番号及びB2セルのスタート側行番号に「$」を付けて固定（コピペしたときにズレない）にして完成です。
3 あ8 =OFFSET(A$3,(ROW(B3)-ROW(B$2))*5,0,1,1) 6. 必要な数だけコピペする
4 あ13 =OFFSET(sheet1!A$3,(ROW(B4)-ROW(B$2))*5,0,1,1) 7. データのシート(Sheet1）とそれを読み出す式が置かれるシートが違っている場合は、Aの前にシート名「Sheet1!」を追加します（シート名とセル番号の区切りを表す“!”を忘れずに）。

◆INDIRECT関数を使った飛び飛びの参照式はこちら⇒「行を飛び飛びに参照する数式(1)」

← BACK
↑ PAGE TOP

Excel Tips『行を飛び飛びに参照する数式(2)』更新記録

2020/11/25
[2] Excel2019を追加。
2017/11/28
[1] HTML5,UTF-8対応。Excel version 2016を追加。[コピー]ボタンを設置。
2011/09/10
新規Tipsとして公開
← BACK
↑ PAGE TOP
Today is