基本情報技術者試験の表計算（選択問題13）で出題されるマクロには「相対」という関数が使われます（正確にはセル変数の相対表現）。Excel関数ではOFFSET()、VBAでは「.Offset()」です。この相対は、Excelの相対参照とは違います（参照：【Excel数式】セルの「参照」が分からなければ相対参照は理解できない）。
マクロの相対表現とは、ある特定の場所を基準として位置を表す、いわゆる「相対パス」または相対アドレスのことです。基準となるセルを固定して、そこから上下左右にどれだけ移動するかを数値で表すセル指定の方法です。
さて、この相対はどのような場合に使うのでしょうか？今回は、相対の意味をOFFSET関数で理解する練習問題を出題します。
目次
- １．基本情報技術者試験の表計算について
- ２．オフセットとは何か
- ３．相対で指定するメリット
- ４．行番号と列番号を考える
- ５．OFFSET関数の基本練習
- ６．行番号、列番号で検索する
- ７．大きいリストの中から抽出する練習
- ８．マクロでは転記先も相対を使う
- ９．過去問研究
- 10．さいごに
１．基本情報技術者試験の表計算について
基本情報技術者試験の問題に使用される関数の説明についての著作権はIPA情報処理推進機構にあります（IPA公式サイト：https://www.jitec.ipa.go.jp/）。また、基本情報技術者試験（表計算）の詳細は、市販のテキストをご覧ください。
２．オフセットとは何か
問題
「相対(A1,3,2)」「相対(E7,-3,-2)」は何を表すか述べなさい。また、Excelで求めなさい。
解説
IPA情報処理推進機構の仕様説明によると、相対には引数が3個あるものとされています。
相対（セル変数,行の位置,列の位置）
セル変数で指定したセルを基準のセルとする。そのセルの行番号と列番号の位置を０とし、下または右方向を正として数え、行の位置と列の位置で指定した数と一致する場所にあるセルを表す変数である。
相対（セル,x,y）はセルの行番号にx、列番号にyを加算したセルを表します。
「相対(A1,3,2)」はセルA1の行番号に３を加算し、列番号に２を加算します。セルA1から見て、下に3つ、右に2つ進んだセルはC4です。セルC4を表します。
ExcelのOFFSET関数は引数が5個ありますが、そのうちの第4、第5引数を省略したものと考えればよいです。「=OFFSET(A1,3,2)」と入力します。
答えは０です。セルC4に何も入力していないからです。
セルC4に数値（文字列でもよい）を入力すると、数式の結果も同じ値になります。
セルC4を参照したければ単に「=C4」と入力すればよいものを、わざわざ関数を使ってややこしい書き方をするのには意味があるのです（後述）。
減算する場合はマイナスにします。相対(E7,-3,-2)は、セルE7の行番号を3減らし、列番号を2減らします。セルC4となります。このように行列の番号を加算したり減算したりして移動することを「オフセット」といいます。
Excelでは、「=OFFSET(E7,-3,-2)」と入力します。セルC4と同じデータが求められます。
つまり、「相対(A1,3,2)」と「相対(E7,-3,-2)」は、記述の仕方が異なりますが同じセルを指しています。
３．相対で指定するメリット
問題
セルB4またはセルD4を基準とするとき、セルD10をそれぞれ相対で表しなさい。また、相対を使うメリットを述べなさい。
解説
（１）表の中の場所を指定する
「相対」は、必ず基準となるセルがあります。基準となるセルがあって、そこから転記したり、探したりするときに相対を使います。
セルD10はExcelの10行目にあるセルですが、表の中では10行目ではありません。表の中では6件目のデータです。そのなかで左端の列を除けば2番目の項目にあたります。
この値を参照するときに「=D10」としてもよいですが、「D」「10」が何を表すのかが分かりにくいという欠点があります。
そこで、表の左上を基準として「相対(B4,6,2)」とすれば、6件目のデータの2番目の項目であることが分かります（以下、(6,2)という）。これによってマクロでループが使いやすくなります。
（２）2つの表があったときに判別しやすい
相対の基準となるセルは、表の左上端にすることが多いです。「相対(B4,x,y)」は、基準となるセルがB4であるというだけでなく、セルB4から始まる表から探索していることを意味しています。
同じシートまたは異なるシートに２つ以上の表がある場合、基準を明示することによって、どの表からデータを見つけようとしているのかを明確にすることができます。
例えば、「相対(B4,6,2)」は、セルB4から始まる表の(6,2)のデータであり、「相対(G4,6,2)」は、セルG4から始まる表の(6,2)のデータを指します。セル番地で「D10」「I10」とするよりも、基準となるセルが明示されているほうが分かりやすくなります。
（３）特定の列を検索する
基準となるセルが左上でない場合があります。例えば、「相対(D4,6,0)」とすると、基準となるセルD4から下向きにデータを探し、6件目のデータのデータを返すことを表します。これによって、特定の列を探索していることを表します。(6,2)、(6,0)のように2次元の座標の形にすることによって、マクロが記述しやすくなるという利点もあります（後述）。
＊補足＊二次元の座標は表計算の基本です。セルを二次元の座標で表すことに慣れてください。また、VBAでは二次元配列を使うので座標の表現が分からなかったらマクロを理解することは不可能です。
４．行番号と列番号を考える
（１）リストの項目名を基準として縦に検索する
問題
セルD2を基準とするとき、D列のセルを相対で表しなさい。
解説
項目名を基準とするとき、その列のデータを取り出すときは、行番号を1ずつ増やします。列番号は０です。相対で表すと相対(D2,1,0)、相対(D2,2,0)、相対(D2,3,0)、・・・となります。
この行番号は、レコードの件数と一致します。
上から数えて何件目のレコードかを変数「I」で表すものとすると、相対(D2,I,0)となるので、マクロではFor文で表現することができます。
■ I=1,I<=10,1
｜・相対(D2,I,0)を使った処理
■
1列目のデータが空白であることを条件としてループを止める場合はWhile文を使います（試験ではこちらのパターンが多く出題されています）。
cnt ← 1 ■ 相対(B2,cnt,0) <> null ｜・相対(D2,cnt,0)を使った処理 ｜・cnt ← cnt+1 ■
（２）リストの左上が基準としてレコードを取得する
問題
セルB2を基準とするとき、3件目、6件目、9件目のレコードを相対で表しなさい。
また、セルB3が基準の場合はどうか。
解説
左上端の項目名を基準とするとき、3件目のレコードの行番号は３です。列番号は０～３です。相対で表すと相対(B2,3,0)、相対(B2,3,1)、相対(B2,3,2)、相対(B2,3,3)となります。
マクロでは列番号を変数にしてループで表現します。これによって1行分（1件分）のレコードを取得することができます。
■ J=0,J<=3,1
｜・相対(B2,3,J)を使った処理
■
行番号を3の倍数にするなら2重ループとなります。
■ I=1,I<=10,1
｜▲ 剰余(I,3)=0
｜｜■ J：0,J<=3,1
｜｜｜・相対(B2,I,J)を使った処理
｜｜■
｜▼
■
基準のセルを1件目の左端のセルとする場合、行番号が1つずれます。
3件目、6件目、9件目をループで表すときは2通りの表現の仕方があります。
■ I=0,I<=9,1 ｜▲ 剰余(I+1,3)=0 ｜｜■ J：0,J<=3,1 ｜｜｜・相対(B3,I,J)を使った処理 ｜｜■ ｜▼ ■
■ I=1,I<=10,1 ｜▲ 剰余(I,3)=0 ｜｜■ J：0,J<=3,1 ｜｜｜・相対(B3,I-1,J)を使った処理 ｜｜■ ｜▼ ■
５．OFFSET関数の基本練習
（１）縦方向の転記
問題
セル範囲D4:D13の数値を、A列の数値を用いてセルB6以降に転記しなさい。
解説
もちろん、セルB6に「=D4」と入力してオートフィルをすれば終わりであることは言うまでもありませんが、OFFSETの練習にならないので、あえてA列の数値を使って参照してみましょう。
転記するデータは縦に並んでいます。つまり、行番号が1ずつ増えています。列番号の増減はありません。基準のセルをD4とすると、その次のD5は行番号を１足したものです。その次のD6は行番号を２足したものです。
A列に入力されている数値は、行番号の増分です。セルD4を基準として、下向きに０～９番目の数値を取得すればよいです。したがって、「=OFFSET($D$4,A6,0)」となります。セルD4は絶対参照です。基本情報技術者試験の書き方であれば「=相対($D$4,A6,0)」となります（試験ではドルマークを付けないのですが、詳しくは後述）。
（２）縦方向の検索
問題
D4:D13の数値のうち、上から3番目、6番目、9番目の数値を求めなさい。
解説
D4:D13の数値のうち、上から3番目の数値は425です。
これはセルD4を基準とすると2つ下のセルです。したがって、「=OFFSET($D$4,A6-1,0)」です。
別解
セルD3を基準とすれば、３つ下のセルになります。したがって、「=OFFSET($D$3,A6,0)」です。
（３）縦方向に検索して横方向に転記する
問題
B4:D13の数値のうち上から3番目、6番目、9番目のものを、セルG2以降に転記しなさい。
解説
さきほどの問題から、セルB3を基準としたほうが良いことが分かります。セルB3を基準とすると行番号は+3、+6、+9です。
したがって、「=OFFSET($B$4,F2,0)」です。これでG列を求めることはできます。
次に、横方向については列番号を考えます。セルB3を基準とすると列番号は０、＋１、＋２です。したがって、1行目の数値を用いて「=OFFSET($B$3,$F2,G$1)」となります。複合参照です。
６．行番号、列番号で検索する
問題
1000点満点の試験を3科目行い、生徒10人分の得点をセルA5以降に入力した。セルB1に生徒の番号、セルB2に科目の番号を入力した。次のセルを基準とする場合、該当する得点をセルD2に求めなさい。
（１）セルA5を基準とする場合
（２）セルB6を基準とする場合
解説
このような問題は通常、表引き（ExcelではINDEX関数）で求めますが、範囲選択をする必要が無いという点で、相対（OFFSET）を使うこともあります。相対を使う場合、入力する番号と、基準となるセルの位置関係によって、適宜、行番号や列番号を調整しなければなりません。特に、０から始まるのか、１から始まるのかに注意します。
生徒番号を２、科目番号を３と指定すると、答えは「386」になるはずです。
セルA5を基準とする場合、行番号は+1、+2、+3、・・・であり、列番号も+1、+2、+3です。B1:B2に入力する番号と、加算する数値が一致しています。したがって、「=OFFSET(A5,B1,B2)」となります。
セルB6を基準とする場合、行番号は0、+1、+2、・・・であり、列番号も0、+1、+2です。加算する数値は、B1:B2に入力する番号より１小さいです。したがって、「=OFFSET(B6,B1-1,B2-1)」となります。
７．大きいリストの中から抽出する練習
（１）OFFSETの入れ子
問題
基準をセルB1とする。
2件目、5件目、7件目のレコードをセルG9:J11に転記しなさい。なお、G2:G4、F9:F11、G8:J8を参照してもよい。
解説
セルB1を基準とすると、行番号はG2、列番号はG8です。したがって、「=OFFSET($B$1,$G2,G$8)」です。複合参照です。
ちなみに、セルG1を基準とすると、G2:G4は、OFFSET(G1,1,0)～OFFSET(G1,3,0)となります。この列番号はF9:F11の数値と同じです。
したがって、「=OFFSET($B$1,OFFSET($G$1,$F9,0),G$8)」と書くこともできます。このように2つの基準（２つの表）からデータを求めることもできます。
（２）MATCHで行番号を求める
問題
A列に１～３の番号を入力した。
番号を入力した行について、B列～E列のデータをセルH9:K11に転記しなさい。
解説
まず、MATCH関数（照合一致）を用いて、番号を検索して行番号を求めます。「=MATCH(G9,$A$2:$A$11,0)」となります。
基準をセルA1とすると、この行番号がそのまま使えます。列番号は+1～+4なので、セルH8～K8に１～４と入力します。
複合参照で「=OFFSET($A$1,MATCH($G9,$A$2:$A$11,0),H$8)」となります。
別解
基準をセルB1とすると、列番号は０～３です。複合参照で「=OFFSET($B$1,MATCH($G9,$A$2:$A$11,0),H$8)」となります。
（３）判定
問題
セルA1を基準とする。
G列の行番号のレコードについて、氏名をH2:H5に転記しなさい。また、科目1～3の合計が2000以上であれば合格、そうでなければ不合格としなさい。
解説
セルA1を基準とすると氏名の列番号は＋１なので「OFFSET($A$1,行番号,1)」となります。したがって、「=OFFSET($A$1,G2,1)」となります。
各科目の得点はそれぞれ、OFFSET($A$1,G2,2)、OFFSET($A$1,G2,3)、OFFSET($A$1,G2,4)なので、
- =IF(OFFSET($A$1,G2,2)+OFFSET($A$1,G2,3)+OFFSET($A$1,G2,4)>=2000,"合格","不合格")
となります。
別解
Excelでは、SUMを使って「=IF(SUM(OFFSET($A$1,G2,2,1,3))>=2000,"合格","不合格")」とまとめることができますが、試験問題の仕様外なので詳しい解説は省略します。
（４）縦横逆
問題
セルA1を基準とする。
セルH1に行番号を入力したときに、セルH1の行番号のレコードについて、氏名をセルH3に求めなさい。また、科目1～3の得点が700以上であれば合格、そうでなければ不合格としなさい。
解説
VLOOKUP関数（垂直照合）を用いて求めることもできますが、あえて相対で求めてみましょう。
F列に１～４を入力します。これはセルA1を基準としたときの列番号です。
氏名は「=OFFSET($A$1,$H$1,F3)」です。
科目の判定は「=IF(OFFSET($A$1,$H$1,F4)>=700,"合格","不合格")」となります。
８．マクロでは転記先も相対を使う
問題
セルA3とG3を基準として、次の処理を疑似言語で記述しなさい。なお、2つの表は別のシートに入力すべきですが、解説の便宜上、1つのシートに入力しています。
- （G列）A列のうち3の倍数をセルG4以降に転記する。ただし、セルA4から下向きに探して空白セルが見つかった時点で処理を終了する。
- （H～K列）A3から始まる表のうち上から3件目、6件目、9件目、・・・（3の倍数）のレコードをセルH4以降に転記する。
- （L列）さらに、3科目の合計が1000以上の場合に合格、そうでなければ不合格とする。
解説
マクロの場合は、転記元と転記先のそれぞれに基準となるセルを設定して、相対で表します。転記元は通常、左上のセルを基準として、レコード件数を数える変数（cntなど）を用意します。セルA3を基準とするとき1件目のレコードの行番号は１です。
・row_cnt ← 1
この変数は1ずつ増やしていきます。セルA3を基準とするとA列のデータは「相対(A3,row_cnt,0) 」となります。1列目が空白のときに処理を終了するのであれば、Whileによるループになります。
このパターンは絶対暗記です！！！100回書いて覚えてください！！！
・row_cnt ← 1 ■ 相対(A3,row_cnt,0)<>null ｜ ｜・row_cnt ← row_cnt + 1 ■
行番号が３の倍数かどうかの判定をします。
・row_cnt ← 1
■ 相対(A3,row_cnt,0)<>null
｜▲ 剰余(row_cnt,3)=0
｜｜
｜｜・row_cnt ← row_cnt + 1
｜▼
■
転記先も基準があります。セルG3を基準とすると、G列の行番号はrow_cnt/3、列番号は０です。
・row_cnt ← 1
■ 相対(A3,row_cnt,0)<>null
｜▲ 剰余(row_cnt,3)=0
｜｜・相対(G3,row_cnt/3,0) ← row_cnt
｜｜
｜｜・row_cnt ← row_cnt + 1
｜▼
■
氏名は、転記元「相対(A3,row_cnt,1)」、転記先「相対(G3,row_cnt/3,1)」です。
・row_cnt ← 1
■ 相対(A3,row_cnt,0)<>null
｜▲ 剰余(row_cnt,3)=0
｜｜・相対(G3,row_cnt/3,0) ← row_cnt
｜｜・相対(G3,row_cnt/3,1) ← 相対(A3,row_cnt,1)
｜｜
｜｜・row_cnt ← row_cnt + 1
｜▼
■
3科目の得点は、相対(A3,row_cnt,2)～相対(A3,row_cnt,4)ですが、行番号や氏名も含めてすべてループで転記したほうがすっきりします。
・row_cnt ← 1 ■ 相対(A3,row_cnt,0)<>null ｜▲ 剰余(row_cnt,3)=0 ｜｜■ J：0,J<=4,1 ｜｜｜・相対(G3,row_cnt/3,J) ← 相対(A3,row_cnt,J) ｜｜■ ｜｜ ｜｜・row_cnt ← row_cnt + 1 ｜▼ ■
合計を求めるのもループを使います。
・row_cnt ← 1 ■ 相対(A3,row_cnt,0)<>null ｜▲ 剰余(row_cnt,3)=0 ｜｜■ J：0,J<=4,1 ｜｜｜・相対(G3,row_cnt/3,J) ← 相対(A3,row_cnt,J) ｜｜■ ｜｜・gokei ← 0 ｜｜■ J：2,J<=4,1 ｜｜｜・gokei ← gokei + 相対(A3,row_cnt,J) ｜｜■ ｜｜▲ gokei>=1000 ｜｜｜・相対(G3,row_cnt/3,5) ← '合格' ｜｜+－ ｜｜｜・相対(G3,row_cnt/3,5) ← '不合格' ｜｜▼ ｜｜・row_cnt ← row_cnt + 1 ｜▼ ■
これで完成です。試験問題はこのようにして作られるのですが、自分で、疑似言語やVBAを書いてみたら出題者の意図が理解できるのではないかと思います。
Sub test()
row_cnt = 1
Do While Range("A3").Offset(row_cnt, 0) <> ""
If row_cnt Mod 3 = 0 Then
For J = 0 To 4
Range("G3").Offset(row_cnt / 3, J) = Range("A3").Offset(row_cnt, J)
Next
gokei = 0
For J = 2 To 4
gokei = gokei + Range("A3").Offset(row_cnt, J)
Next
If gokei >= 1000 Then
Range("G3").Offset(row_cnt / 3, 5) = "合格"
Else
Range("G3").Offset(row_cnt / 3, 5) = "不合格"
End If
row_cnt = row_cnt + 1
End If
Loop
End Sub
９．過去問研究
問題
次の疑似言語は平成29年秋の試験問題の一部を変えたものである（改題）。どのような処理をしていると考えられるか。
・log_line ← 1 ■ 相対(ログ!A1, log_line, 2) <> null ｜・count_line ← 1 ｜・log_url ← 相対(ログ!A1, log_line, 2) ｜・count_url ← 相対(A1, count_line, 0) ｜■ [ 解答欄d ] ｜｜・count_line ← count_line + 1 ｜｜・count_url ← 相対(A1, count_line, 0) ｜■ ｜▲ [ 解答欄e ] ｜｜・相対(A1, count_line, 0) ← log_url ｜▼ ｜・log_line ← log_line + 1 ■
解説
まず、最初と最後の2行だけ取り出すと、「ログ!A1」を基準とする表があることが分かります。
・log_line ← 1 ■ 相対(ログ!A1, log_line, 2) <> null ｜・log_line ← log_line + 1 ■
列番号＋２の列（C列）を探索していることが分かります。log_lineが1から始まることから、ログ!C2から下向きに空白セルが見つかるまで処理をしています。これが転記元の表であると考えられます。
この表の行番号は「log_line」です。
このC列のデータは「log_url」にいったん格納されています。
｜・log_url ← 相対(ログ!A1, log_line, 2)
さらに、アクティブシートのセルA1を基準とする表があることが分かります。
｜・count_line ← 1 ｜・count_url ← 相対(A1, count_line, 0) ｜■ [ 解答欄d ] ｜｜・count_line ← count_line + 1 ｜｜・count_url ← 相対(A1, count_line, 0) ｜■
列番号０の列を探索していることが分かります。count_lineが1から始まることから、A2から下向きに処理をしていると考えられます。これが転記先の表であると考えられます。
この表の行番号は「count_line」です。A列のデータは「count_url」にいったん格納されています。
その後、「log_url」を転記しています。
｜▲ [ 解答欄e ] ｜｜・相対(A1, count_line, 0) ← log_url ｜▼
これで、なんとなく流れが分かったと思いますが、この問題は「ログ」のなかから重複をなくして転記する処理をしています。転記先のシートに存在しなければ転記して、存在すれば転記する必要はありません。
したがって、[ 解答欄d ]のループは、空白が見つかるかまたは同じものが見つかった場合に終了すればよいので、ループ継続条件は「論理積(count_url<>null, count_url<>log_url)」となります。[ 解答欄e ]は転記をする条件なのでcount_url = nullとなります。
10．さいごに
ここで説明しているのは、基本情報技術者試験の試験対策として、試験問題で使われている相対の意味をOFFSET関数で解説したものです。ExcelのOFFSET関数やVBAのOffsetの使い方を説明したものではありません。
解説は以上です。
