わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

基本情報技術者試験マクロ「相対」の意味を、ExcelのOFFSET関数で理解しよう

基本情報技術者試験の表計算(選択問題13)で出題されるマクロには「相対」という関数が使われます(正確にはセル変数の相対表現)。Excel関数ではOFFSET()、VBAでは「.Offset()」です。この相対は、Excelの相対参照とは違います(参照:【Excel数式】セルの「参照」が分からなければ相対参照は理解できない)。

マクロの相対表現とは、ある特定の場所を基準として位置を表す、いわゆる「相対パス」または相対アドレスのことです。基準となるセルを固定して、そこから上下左右にどれだけ移動するかを数値で表すセル指定の方法です。

さて、この相対はどのような場合に使うのでしょうか?今回は、相対の意味をOFFSET関数で理解する練習問題を出題します。

 

 

目次

1.基本情報技術者試験の表計算について

基本情報技術者試験の問題に使用される関数の説明についての著作権はIPA情報処理推進機構にあります(IPA公式サイト:https://www.jitec.ipa.go.jp/)。また、基本情報技術者試験(表計算)の詳細は、市販のテキストをご覧ください。

 

2.オフセットとは何か

問題

「相対(A1,3,2)」「相対(E7,-3,-2)」は何を表すか述べなさい。また、Excelで求めなさい。

解説

IPA情報処理推進機構の仕様説明によると、相対には引数が3個あるものとされています。

相対(セル変数,行の位置,列の位置)
セル変数で指定したセルを基準のセルとする。そのセルの行番号と列番号の位置を0とし、下または右方向を正として数え、行の位置と列の位置で指定した数と一致する場所にあるセルを表す変数である。

相対(セル,x,y)はセルの行番号にx、列番号にyを加算したセルを表します。

f:id:waenavi:20191002223753j:plain

 

「相対(A1,3,2)」はセルA1の行番号に3を加算し、列番号に2を加算します。セルA1から見て、下に3つ、右に2つ進んだセルはC4です。セルC4を表します。

f:id:waenavi:20191002231532j:plain

 

ExcelのOFFSET関数は引数が5個ありますが、そのうちの第4、第5引数を省略したものと考えればよいです。「=OFFSET(A1,3,2)」と入力します。

f:id:waenavi:20191002231638j:plain

 

答えは0です。セルC4に何も入力していないからです。

f:id:waenavi:20191002231716j:plain

 

セルC4に数値(文字列でもよい)を入力すると、数式の結果も同じ値になります。

f:id:waenavi:20191002231752j:plain

 

セルC4を参照したければ単に「=C4」と入力すればよいものを、わざわざ関数を使ってややこしい書き方をするのには意味があるのです(後述)。

f:id:waenavi:20191002231824j:plain

 

減算する場合はマイナスにします。相対(E7,-3,-2)は、セルE7の行番号を3減らし、列番号を2減らします。セルC4となります。このように行列の番号を加算したり減算したりして移動することを「オフセット」といいます。

f:id:waenavi:20191002232043j:plain

 

Excelでは、「=OFFSET(E7,-3,-2)」と入力します。セルC4と同じデータが求められます。

f:id:waenavi:20191002232139j:plain

 

つまり、「相対(A1,3,2)」と「相対(E7,-3,-2)」は、記述の仕方が異なりますが同じセルを指しています。

f:id:waenavi:20191002232216j:plain

 

3.相対で指定するメリット

問題

セルB4またはセルD4を基準とするとき、セルD10をそれぞれ相対で表しなさい。また、相対を使うメリットを述べなさい。

f:id:waenavi:20191002232347j:plain

 

解説

(1)表の中の場所を指定する

「相対」は、必ず基準となるセルがあります。基準となるセルがあって、そこから転記したり、探したりするときに相対を使います。

セルD10はExcelの10行目にあるセルですが、表の中では10行目ではありません。表の中では6件目のデータです。そのなかで左端の列を除けば2番目の項目にあたります。
この値を参照するときに「=D10」としてもよいですが、「D」「10」が何を表すのかが分かりにくいという欠点があります。

f:id:waenavi:20191002232912j:plain

 

そこで、表の左上を基準として「相対(B4,6,2)」とすれば、6件目のデータの2番目の項目であることが分かります(以下、(6,2)という)。これによってマクロでループが使いやすくなります。

f:id:waenavi:20191002233029j:plain

 

(2)2つの表があったときに判別しやすい

相対の基準となるセルは、表の左上端にすることが多いです。「相対(B4,x,y)」は、基準となるセルがB4であるというだけでなく、セルB4から始まる表から探索していることを意味しています。

f:id:waenavi:20191002233253j:plain

 

同じシートまたは異なるシートに2つ以上の表がある場合、基準を明示することによって、どの表からデータを見つけようとしているのかを明確にすることができます。

例えば、「相対(B4,6,2)」は、セルB4から始まる表の(6,2)のデータであり、「相対(G4,6,2)」は、セルG4から始まる表の(6,2)のデータを指します。セル番地で「D10」「I10」とするよりも、基準となるセルが明示されているほうが分かりやすくなります。

f:id:waenavi:20191002233527j:plain

 

(3)特定の列を検索する

基準となるセルが左上でない場合があります。例えば、「相対(D4,6,0)」とすると、基準となるセルD4から下向きにデータを探し、6件目のデータのデータを返すことを表します。これによって、特定の列を探索していることを表します。(6,2)、(6,0)のように2次元の座標の形にすることによって、マクロが記述しやすくなるという利点もあります(後述)。

f:id:waenavi:20191002234227j:plain


*補足*二次元の座標は表計算の基本です。セルを二次元の座標で表すことに慣れてください。また、VBAでは二次元配列を使うので座標の表現が分からなかったらマクロを理解することは不可能です。

 

4.行番号と列番号を考える

(1)リストの項目名を基準として縦に検索する

問題

セルD2を基準とするとき、D列のセルを相対で表しなさい。

f:id:waenavi:20191002234811j:plain

 

解説

項目名を基準とするとき、その列のデータを取り出すときは、行番号を1ずつ増やします。列番号は0です。相対で表すと相対(D2,1,0)、相対(D2,2,0)、相対(D2,3,0)、・・・となります。

f:id:waenavi:20191002234851j:plain

 

この行番号は、レコードの件数と一致します。

f:id:waenavi:20191002234928j:plain

 

上から数えて何件目のレコードかを変数「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
■

 

(2)リストの左上が基準としてレコードを取得する

問題

セルB2を基準とするとき、3件目、6件目、9件目のレコードを相対で表しなさい。

f:id:waenavi:20191003000206j:plain

 

また、セルB3が基準の場合はどうか。

f:id:waenavi:20191003000120j:plain

 

解説

左上端の項目名を基準とするとき、3件目のレコードの行番号は3です。列番号は0~3です。相対で表すと相対(B2,3,0)、相対(B2,3,1)、相対(B2,3,2)、相対(B2,3,3)となります。

f:id:waenavi:20191003000557j:plain

 

マクロでは列番号を変数にしてループで表現します。これによって1行分(1件分)のレコードを取得することができます。

■ J=0,J<=3,1
|・相対(B2,3,J)を使った処理
■

 

行番号を3の倍数にするなら2重ループとなります。

f:id:waenavi:20191003000707j:plain

■ I=1,I<=10,1
|▲ 剰余(I,3)=0
||■ J:0,J<=3,1
|||・相対(B2,I,J)を使った処理
||■
|▼
■

 

基準のセルを1件目の左端のセルとする場合、行番号が1つずれます。

f:id:waenavi:20191003000828j:plain

 

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)を使った処理
||■
|▼
■

 

5.OFFSET関数の基本練習

(1)縦方向の転記

問題

セル範囲D4:D13の数値を、A列の数値を用いてセルB6以降に転記しなさい。

f:id:waenavi:20191003001557j:plain

 

解説

もちろん、セルB6に「=D4」と入力してオートフィルをすれば終わりであることは言うまでもありませんが、OFFSETの練習にならないので、あえてA列の数値を使って参照してみましょう。

f:id:waenavi:20191003003214j:plain

 

転記するデータは縦に並んでいます。つまり、行番号が1ずつ増えています。列番号の増減はありません。基準のセルをD4とすると、その次のD5は行番号を1足したものです。その次のD6は行番号を2足したものです。

f:id:waenavi:20191003003317j:plain

 

A列に入力されている数値は、行番号の増分です。セルD4を基準として、下向きに0~9番目の数値を取得すればよいです。したがって、「=OFFSET($D$4,A6,0)」となります。セルD4は絶対参照です。基本情報技術者試験の書き方であれば「=相対($D$4,A6,0)」となります(試験ではドルマークを付けないのですが、詳しくは後述)。

f:id:waenavi:20191003003409j:plain

 

(2)縦方向の検索

問題

D4:D13の数値のうち、上から3番目、6番目、9番目の数値を求めなさい。

f:id:waenavi:20191003003500j:plain

 

解説

D4:D13の数値のうち、上から3番目の数値は425です。

f:id:waenavi:20191003003540j:plain

 

これはセルD4を基準とすると2つ下のセルです。したがって、「=OFFSET($D$4,A6-1,0)」です。

f:id:waenavi:20191003003610j:plain

 

別解

セルD3を基準とすれば、3つ下のセルになります。したがって、「=OFFSET($D$3,A6,0)」です。

f:id:waenavi:20191003003748j:plain

 

(3)縦方向に検索して横方向に転記する

問題

B4:D13の数値のうち上から3番目、6番目、9番目のものを、セルG2以降に転記しなさい。

f:id:waenavi:20191003003825j:plain

 

解説

さきほどの問題から、セルB3を基準としたほうが良いことが分かります。セルB3を基準とすると行番号は+3、+6、+9です。

f:id:waenavi:20191003004235j:plain

 

したがって、「=OFFSET($B$4,F2,0)」です。これでG列を求めることはできます。

f:id:waenavi:20191003010112j:plain

 

次に、横方向については列番号を考えます。セルB3を基準とすると列番号は0、+1、+2です。したがって、1行目の数値を用いて「=OFFSET($B$3,$F2,G$1)」となります。複合参照です。

f:id:waenavi:20191003010203j:plain

 

6.行番号、列番号で検索する

問題

1000点満点の試験を3科目行い、生徒10人分の得点をセルA5以降に入力した。セルB1に生徒の番号、セルB2に科目の番号を入力した。次のセルを基準とする場合、該当する得点をセルD2に求めなさい。

(1)セルA5を基準とする場合
(2)セルB6を基準とする場合

f:id:waenavi:20191003010254j:plain

 

解説

このような問題は通常、表引き(ExcelではINDEX関数)で求めますが、範囲選択をする必要が無いという点で、相対(OFFSET)を使うこともあります。相対を使う場合、入力する番号と、基準となるセルの位置関係によって、適宜、行番号や列番号を調整しなければなりません。特に、0から始まるのか、1から始まるのかに注意します。

生徒番号を2、科目番号を3と指定すると、答えは「386」になるはずです。

f:id:waenavi:20191003010325j:plain

 

セルA5を基準とする場合、行番号は+1、+2、+3、・・・であり、列番号も+1、+2、+3です。B1:B2に入力する番号と、加算する数値が一致しています。したがって、「=OFFSET(A5,B1,B2)」となります。

f:id:waenavi:20191003010611j:plain

 

セルB6を基準とする場合、行番号は0、+1、+2、・・・であり、列番号も0、+1、+2です。加算する数値は、B1:B2に入力する番号より1小さいです。したがって、「=OFFSET(B6,B1-1,B2-1)」となります。

f:id:waenavi:20191003010744j:plain

 

7.大きいリストの中から抽出する練習

(1)OFFSETの入れ子

問題

基準をセルB1とする。

f:id:waenavi:20191003011038j:plain

 

2件目、5件目、7件目のレコードをセルG9:J11に転記しなさい。なお、G2:G4、F9:F11、G8:J8を参照してもよい。

f:id:waenavi:20191003011106j:plain

 

解説

セルB1を基準とすると、行番号はG2、列番号はG8です。したがって、「=OFFSET($B$1,$G2,G$8)」です。複合参照です。

f:id:waenavi:20191003011214j:plain

 

ちなみに、セルG1を基準とすると、G2:G4は、OFFSET(G1,1,0)~OFFSET(G1,3,0)となります。この列番号はF9:F11の数値と同じです。

f:id:waenavi:20191003011902j:plain

 

したがって、「=OFFSET($B$1,OFFSET($G$1,$F9,0),G$8)」と書くこともできます。このように2つの基準(2つの表)からデータを求めることもできます。

f:id:waenavi:20191003012046j:plain

 

(2)MATCHで行番号を求める

問題

A列に1~3の番号を入力した。

f:id:waenavi:20191003013130j:plain

 

番号を入力した行について、B列~E列のデータをセルH9:K11に転記しなさい。

f:id:waenavi:20191003012331j:plain

 

解説

まず、MATCH関数(照合一致)を用いて、番号を検索して行番号を求めます。「=MATCH(G9,$A$2:$A$11,0)」となります。

f:id:waenavi:20191003012159j:plain

 

基準をセルA1とすると、この行番号がそのまま使えます。列番号は+1~+4なので、セルH8~K8に1~4と入力します。

f:id:waenavi:20191003012431j:plain

 

複合参照で「=OFFSET($A$1,MATCH($G9,$A$2:$A$11,0),H$8)」となります。

f:id:waenavi:20191003012649j:plain

 

別解

基準をセルB1とすると、列番号は0~3です。複合参照で「=OFFSET($B$1,MATCH($G9,$A$2:$A$11,0),H$8)」となります。

f:id:waenavi:20191003013008j:plain

 

(3)判定

問題

セルA1を基準とする。

f:id:waenavi:20191003013102j:plain

 

G列の行番号のレコードについて、氏名をH2:H5に転記しなさい。また、科目1~3の合計が2000以上であれば合格、そうでなければ不合格としなさい。

f:id:waenavi:20191003013334j:plain

 

解説

セルA1を基準とすると氏名の列番号は+1なので「OFFSET($A$1,行番号,1)」となります。したがって、「=OFFSET($A$1,G2,1)」となります。

f:id:waenavi:20191003013553j:plain

 

各科目の得点はそれぞれ、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,"合格","不合格")

となります。

f:id:waenavi:20191003013645j:plain

 

別解

Excelでは、SUMを使って「=IF(SUM(OFFSET($A$1,G2,2,1,3))>=2000,"合格","不合格")」とまとめることができますが、試験問題の仕様外なので詳しい解説は省略します。

f:id:waenavi:20191003013729j:plain

 

(4)縦横逆

問題

セルA1を基準とする。

f:id:waenavi:20191003013102j:plain

 

セルH1に行番号を入力したときに、セルH1の行番号のレコードについて、氏名をセルH3に求めなさい。また、科目1~3の得点が700以上であれば合格、そうでなければ不合格としなさい。

f:id:waenavi:20191003014327j:plain

 

解説

VLOOKUP関数(垂直照合)を用いて求めることもできますが、あえて相対で求めてみましょう。

f:id:waenavi:20191003014401j:plain

 

F列に1~4を入力します。これはセルA1を基準としたときの列番号です。

f:id:waenavi:20191003014440j:plain

 

氏名は「=OFFSET($A$1,$H$1,F3)」です。

f:id:waenavi:20191003014530j:plain

 

科目の判定は「=IF(OFFSET($A$1,$H$1,F4)>=700,"合格","不合格")」となります。

f:id:waenavi:20191003014636j:plain

 

8.マクロでは転記先も相対を使う

問題

セルA3とG3を基準として、次の処理を疑似言語で記述しなさい。なお、2つの表は別のシートに入力すべきですが、解説の便宜上、1つのシートに入力しています。

  • (G列)A列のうち3の倍数をセルG4以降に転記する。ただし、セルA4から下向きに探して空白セルが見つかった時点で処理を終了する。
  • (H~K列)A3から始まる表のうち上から3件目、6件目、9件目、・・・(3の倍数)のレコードをセルH4以降に転記する。
  • (L列)さらに、3科目の合計が1000以上の場合に合格、そうでなければ不合格とする。

f:id:waenavi:20191003015047j:plain

 

解説

マクロの場合は、転記元と転記先のそれぞれに基準となるセルを設定して、相対で表します。転記元は通常、左上のセルを基準として、レコード件数を数える変数(cntなど)を用意します。セルA3を基準とするとき1件目のレコードの行番号は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
■

 

行番号が3の倍数かどうかの判定をします。

・row_cnt ← 1
■ 相対(A3,row_cnt,0)<>null
|▲ 剰余(row_cnt,3)=0
||
||・row_cnt ← row_cnt + 1
|▼
■

 

転記先も基準があります。セルG3を基準とすると、G列の行番号はrow_cnt/3、列番号は0です。

・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

 

9.過去問研究

問題

次の疑似言語は平成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
■

f:id:waenavi:20191003015843j:plain

 

列番号+2の列(C列)を探索していることが分かります。log_lineが1から始まることから、ログ!C2から下向きに空白セルが見つかるまで処理をしています。これが転記元の表であると考えられます。

f:id:waenavi:20191003015938j:plain

 

この表の行番号は「log_line」です。

f:id:waenavi:20191003020032j:plain

 

このC列のデータは「log_url」にいったん格納されています。

|・log_url ← 相対(ログ!A1, log_line, 2)

f:id:waenavi:20191003020144j:plain

 

さらに、アクティブシートのセルA1を基準とする表があることが分かります。

|・count_line ← 1
|・count_url ← 相対(A1, count_line, 0)
|■ [ 解答欄d ]
||・count_line ← count_line + 1
||・count_url ← 相対(A1, count_line, 0)
|■

f:id:waenavi:20191003020700j:plain

 

列番号0の列を探索していることが分かります。count_lineが1から始まることから、A2から下向きに処理をしていると考えられます。これが転記先の表であると考えられます。

f:id:waenavi:20191003020229j:plain 

 

この表の行番号は「count_line」です。A列のデータは「count_url」にいったん格納されています。

f:id:waenavi:20191003020832j:plain

 

その後、「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の使い方を説明したものではありません。

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]