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

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

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

１．数式

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

(1) スタートの行番号を直接指定した場合

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

(2) スタートの行番号をROW関数で算出した場合

A B B列セルの内容
1 あ1 　 A3セルから5行飛びにB2セル以降に取り出した場合
2 あ2 あ3 =INDIRECT("sheet1!A"&(ROW(B2)-ROW(B$2)+1)*5-5+ROW(sheet1!A$3))
3 あ3 あ8 =INDIRECT("sheet1!A"&(ROW(B3)-ROW(B$2)+1)*5-5+ROW(sheet1!A$3))
4 あ4 あ13 =INDIRECT("sheet1!A"&(ROW(B4)-ROW(B$2)+1)*5-5+ROW(sheet1!A$3))
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 =INDIRECT("A3") 2. A3セルの参照をINDIRECT関数を使って間接的に表現します。
「“A3” という文字列をセル番号として使います」という意味になります。
　 あ3 =INDIRECT("A"&"3") 3. 列"A"と行"3"を分けて表現します。
　文字列としての“A3”なので、分離して表現しても動作は変わらない。
　 あ3 =INDIRECT("A"&ROW(A3)) 4. 行をROW関数を使って置き換えます。
　式をコピペで展開するためにセル番号とすることが必要なのです。
　 5 =ROW(B1)*5 5. 5行飛びを作ります。
とりあえず、1行目から式を入れることにして、B1セルの行番号から5飛びを計算する式を作ります。
　 あ5 =INDIRECT("A"&ROW(B1)*5) 6. 行番号をB列側の変化で算出するため、4.式のROW(A3)をROW(B1)*5で置き換えます。
　 あ3 =INDIRECT("A"&ROW(B1)*5-5+3) 7. 3行目から拾うことにするために、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. 7.式の“ROW(B1)”を8.式で置き換えると式が完成します。
　 あ3 =INDIRECT("sheet1!A"&(ROW(B2)-2+1)*5-5+3) 10. データのシート(Sheet1）とそれを読み出す式が置かれるシートが違っている場合は、Aの前にシート名（Sheet1!）を追加します（シート名とセル番号の区切りを表す“!”を忘れずに）。
　 あ3 =INDIRECT("sheet1!A"&(ROW(B2)-ROW(B$2)+1)*5-5+ROW(sheet1!A$3)) 11. “2”と“3”はどちらもスタートの行番号です。9.式はこのスタート行番号を直接式中に埋め込んでいますが、編集作業によりスタート行番号が変化する可能性がある場合は、この2つをROW関数を使って算出するようにすると式の修正忘れを防げます。

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

← BACK
↑ PAGE TOP

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

2020/11/25
[3] Excel2019を追加。
2017/11/29
[2] HTML5,UTF-8対応。Excel version 2016を追加。数式の(2)を追加。[コピー]ボタンを設置。
2012/08/21
[1]
2007/01/19
新規Tipsとして公開
← BACK
↑ PAGE TOP
Today is