行を飛び飛びに参照する数式(2)
HOME > PCワンポイント活用術 > エクセル(Excel)の活用術 > コピペで使える数式【行を飛び飛びに参照する数式2】
Excel Version: Excel2013 Excel2010 Excel2007 Excel2003 Excel2002 Excel2000 Excel97
 この数式は、連続した行に入力されたデータ行を飛び飛びに参照、取り出す数式(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

↑ページトップへ

● 数式を組み立てられるようになりたい人のための解説
  A列3行目から5行ごとのセルからデータをB列の2行目以降に取出す式を例に説明しましょう

B となりのセル(B列)の内容 解説
1      
2 あ3 =A3 1.基本の式はこれ
  あ3 =OFFSET(A3,0,0,1,1) 2.A3セルの参照をOFFSET関数を使って表現する
  あ3 =OFFSET(A3,(ROW(B2)-ROW(B2)),0,1,1) 3.ROW関数を使って抽出開始セルと数式があるセルの行数の差を計算する式に変える
  あ3 =OFFSET(A3,(ROW(B2)-ROW(B2))*5,0,1,1) 4.行数の差に飛び間隔(5)をかけて行数のとび量にする
  あ3 =OFFSET(A$3,(ROW(B2)-ROW(B$2))*5,0,1,1) 5.OFFSET計算の基準になるA3セルの行番号及びB3セルの基準側行番号に「$」を付けて固定(コピペしたときにズレない)にして完成
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)」

↑ページトップへ




↑ページトップへ