行を飛び飛びに参照する数式(1)
HOME > PCワンポイント活用術 > エクセル(Excel)の活用術 > コピペで使える数式【行を飛び飛びに参照する数式1】
Excel Version: Excel2013 Excel2010 Excel2007 Excel2003 Excel2002 Excel2000 Excel97
Update:2012-8-21[1]
 この数式は、連続した行に入力されたデータ行を飛び飛びに参照、取り出す数式(indirect関数を使った数式)です。

● 数式
 下表中の式、“=INDIRECT("Sheet1!A"&(ROW(……”をコピペしてお使いください。
 下記の表は、A列3行目から5行ごとのセルからデータをB列の2行目以降に取り出しています。
 “sheet1!”は元データのシート名(同一シート内での場合は無くても良い)、
 “A”は元データの列番号、
 “B2”は式の入っているセル番号、
 “2”は式の入っている行番号、
 “5”は読み出す行の飛び(2ヵ所)、
 “3”は元データの読み出す最初のセルの行番号です。

A B となりのセル(B列)の内容
1 あ1   A3セルから5行飛びにB2セル以降に取り出した場合
2 あ2 あ3 =INDIRECT("sheet1!A"&(ROW(B2)-2+1)*5-5+3)
3 あ3 あ8 =INDIRECT("sheet1!A"&(ROW(B3)-2+1)*5-5+3)
4 あ4 あ13 =INDIRECT("sheet1!A"&(ROW(B4)-2+1)*5-5+3)
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 =INDIRECT("A3") 2.A3セルをINDIRECT関数を使って間接的に表現する。
  あ3 =INDIRECT("A"&"3") 3.列(A)と行(3)を分けて表現
  あ3 =INDIRECT("A"&ROW(A3)) 4.行をROW関数を使って置き換える
  5 =ROW(B1)*5 5.1行目から式を入れることにして、B1セルの行番号から5飛びを計算する式を作る。
  あ5 =INDIRECT("A"&ROW(B1)*5) 6.ROW(A3)をROW(B1)*5で置き換える
  あ3 =INDIRECT("A"&ROW(B1)*5-5+3) 7.3行目から拾うことにするためには、行き過ぎている2を引くことになるが、B1セルの行番号に飛びの5を掛けた時点で5行目がスタートになっているので、その分を引いて0に戻し、拾い始めの3を足すことにする。
  1 =ROW(B2)-2+1 8.7.の式は5.で1行目から式を入れることにしているので、2行目以降を式の開始にすると成り立たない。
そこで、2行目(B2セル)を式の開始行とすると、5飛びを発生させるための“ROW(B1)”を1から順に発生するようにする。
  あ3 =INDIRECT("A"&(ROW(B2)-2+1)*5-5+3) 9.8.の式で7.の式の“ROW(B1)”を置き換えると式が完成する。
データのシート(Sheet1)とそれを読み出す式が置かれるシートが違っている場合は、Aの前にシート名(Sheet1!)を追加する(シート名とセル番号の区切りを表す“!”を忘れずに)。

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

↑ページトップへ




↑ページトップへ