当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。表計算(選択問題13)で出題されるマクロには「相対」という関数が使われます(正確にはセル変数の相対表現)。Excel関数ではOFFSET()、VBAでは「.Offset()」です。この相対は、Excelの相対参照とは違います(参照:【Excel数式】セルの「参照」が分からなければ相対参照は理解できない)。マクロの相対表現とは、ある特定の場所を基準として位置を表す、いわゆる「相対パス」または相対アドレスのことです。基準となるセルを固定して、そこから上下左右にどれだけ移動するかを数値で表すセル指定の方法です。問題に使用される関数の説明についての著作権はIPA情報処理推進機構にあります(IPA公式サイト:https://www.jitec.ipa.go.jp/)。「相対(A1,3,2)」「相対(E7,-3,-2)」は何を表すか述べなさい。Excelで求めなさい。解説IPA情報処理推進機構の仕様説明によると、相対には引数が3個あるものとされています。相対(セル変数,行の位置,列の位置)セル変数で指定したセルを基準のセルとする。そのセルの行番号と列番号の位置を0とし、下または右方向を正として数え、行の位置と列の位置で指定した数と一致する場所にあるセルを表す変数である。相対(セル,x,y)はセルの行番号にx、列番号にyを加算したセルを表します。「相対(A1,3,2)」はセルA1の行番号に3を加算し、列番号に2を加算します。セルA1から見て、下に3つ、右に2つ進んだセルはC4です。ExcelのOFFSET関数は引数が5個ありますが、そのうちの第4、第5引数を省略したものと考えればよいです。「=OFFSET(A1,3,2)」と入力します。セルC4に何も入力していないからです。セルC4に数値(文字列でもよい)を入力すると、数式の結果も同じ値になります。セルC4を参照したければ単に「=C4」と入力すればよいものを、わざわざ関数を使ってややこしい書き方をするのには意味があるのです(後述)。減算する場合はマイナスにします。相対(E7,-3,-2)は、セルE7の行番号を3減らし、列番号を2減らします。このように行列の番号を加算したり減算したりして移動することを「オフセット」といいます。Excelでは、「=OFFSET(E7,-3,-2)」と入力します。セルC4と同じデータが求められます。「相対(A1,3,2)」と「相対(E7,-3,-2)」は、記述の仕方が異なりますが同じセルを指しています。3.相対で指定するメリット問題セルB4またはセルD4を基準とするとき、セルD10をそれぞれ相対で表しなさい。相対を使うメリットを述べなさい。解説(1)表の中の場所を指定する「相対」は、必ず基準となるセルがあります。基準となるセルがあって、そこから転記したり、探したりするときに相対を使います。セルD10はExcelの10行目にあるセルですが、表の中では10行目ではありません。表の中では6件目のデータです。そのなかで左端の列を除けば2番目の項目にあたります。この値を参照するときに「=D10」としてもよいですが、「D」「10」が何を表すのかが分かりにくいという欠点があります。そこで、表の左上を基準として「相対(B4,6,2)」とすれば、6件目のデータの2番目の項目であることが分かります(以下、(6,2)という)。これによってマクロでループが使いやすくなります。(2)2つの表があったときに判別しやすい相対の基準となるセルは、表の左上端にすることが多いです。「相対(B4,x,y)」は、基準となるセルがB4であるというだけでなく、セルB4から始まる表から探索していることを意味しています。同じシートまたは異なるシートに2つ以上の表がある場合、基準を明示することによって、どの表からデータを見つけようとしているのかを明確にすることができます。「相対(B4,6,2)」は、セルB4から始まる表の(6,2)のデータであり、「相対(G4,6,2)」は、セルG4から始まる表の(6,2)のデータを指します。セル番地で「D10」「I10」とするよりも、基準となるセルが明示されているほうが分かりやすくなります。(3)特定の列を検索する基準となるセルが左上でない場合があります。「相対(D4,6,0)」とすると、基準となるセルD4から下向きにデータを探し、6件目のデータのデータを返すことを表します。これによって、特定の列を探索していることを表します。(6,2)、(6,0)のように2次元の座標の形にすることによって、マクロが記述しやすくなるという利点もあります(後述)。*補足*二次元の座標は表計算の基本です。セルを二次元の座標で表すことに慣れてください。VBAでは二次元配列を使うので座標の表現が分からなかったらマクロを理解することは不可能です。4.行番号と列番号を考える(1)リストの項目名を基準として縦に検索する問題セルD2を基準とするとき、D列のセルを相対で表しなさい。項目名を基準とするとき、その列のデータを取り出すときは、行番号を1ずつ増やします。相対で表すと相対(D2,1,0)、相対(D2,2,0)、相対(D2,3,0)、・・・となります。この行番号は、レコードの件数と一致します。上から数えて何件目のレコードかを変数「I」で表すものとすると、相対(D2,I,0)となるので、マクロではFor文で表現することができます。■1列目のデータが空白であることを条件としてループを止める場合はWhile文を使います(試験ではこちらのパターンが多く出題されています)。cnt+1■(2)リストの左上が基準としてレコードを取得する問題セルB2を基準とするとき、3件目、6件目、9件目のレコードを相対で表しなさい。セルB3が基準の場合はどうか。左上端の項目名を基準とするとき、3件目のレコードの行番号は3です。相対で表すと相対(B2,3,0)、相対(B2,3,1)、相対(B2,3,2)、相対(B2,3,3)となります。マクロでは列番号を変数にしてループで表現します。これによって1行分(1件分)のレコードを取得することができます。行番号を3の倍数にするなら2重ループとなります。基準のセルを1件目の左端のセルとする場合、行番号が1つずれます。3件目、6件目、9件目をループで表すときは2通りの表現の仕方があります。問題セル範囲D4:D13の数値を、A列の数値を用いてセルB6以降に転記しなさい。解説もちろん、セルB6に「=D4」と入力してオートフィルをすれば終わりであることは言うまでもありませんが、OFFSETの練習にならないので、あえてA列の数値を使って参照してみましょう。転記するデータは縦に並んでいます。行番号が1ずつ増えています。列番号の増減はありません。基準のセルをD4とすると、その次のD5は行番号を1足したものです。その次のD6は行番号を2足したものです。A列に入力されている数値は、行番号の増分です。セルD4を基準として、下向きに0~9番目の数値を取得すればよいです。したがって、「=OFFSET($D$4,A6,0)」となります。書き方であれば「=相対($D$4,A6,0)」となります(試験ではドルマークを付けないのですが、詳しくは後述)。問題D4:D13の数値のうち、上から3番目、6番目、9番目の数値を求めなさい。解説D4:D13の数値のうち、上から3番目の数値は425です。これはセルD4を基準とすると2つ下のセルです。したがって、「=OFFSET($D$4,A6-1,0)」です。別解セルD3を基準とすれば、3つ下のセルになります。したがって、「=OFFSET($D$3,A6,0)」です。(3)縦方向に検索して横方向に転記する問題B4:D13の数値のうち上から3番目、6番目、9番目のものを、セルG2以降に転記しなさい。解説さきほどの問題から、セルB3を基準としたほうが良いことが分かります。セルB3を基準とすると行番号は+3、+6、+9です。したがって、「=OFFSET($B$4,F2,0)」です。これでG列を求めることはできます。次に、横方向については列番号を考えます。セルB3を基準とすると列番号は0、+1、+2です。したがって、1行目の数値を用いて「=OFFSET($B$3,$F2,G$1)」となります。6.行番号、列番号で検索する問題1000点満点の試験を3科目行い、生徒10人分の得点をセルA5以降に入力した。セルB1に生徒の番号、セルB2に科目の番号を入力した。次のセルを基準とする場合、該当する得点をセルD2に求めなさい。解説このような問題は通常、表引き(ExcelではINDEX関数)で求めますが、範囲選択をする必要が無いという点で、相対(OFFSET)を使うこともあります。相対を使う場合、入力する番号と、基準となるセルの位置関係によって、適宜、行番号や列番号を調整しなければなりません。0から始まるのか、1から始まるのかに注意します。生徒番号を2、科目番号を3と指定すると、答えは「386」になるはずです。セルA5を基準とする場合、行番号は+1、+2、+3、・・・であり、列番号も+1、+2、+3です。B1:B2に入力する番号と、加算する数値が一致しています。したがって、「=OFFSET(A5,B1,B2)」となります。セルB6を基準とする場合、行番号は0、+1、+2、・・・であり、列番号も0、+1、+2です。加算する数値は、B1:B2に入力する番号より1小さいです。したがって、「=OFFSET(B6,B1-1,B2-1)」となります。基準をセル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つの基準(2つの表)からデータを求めることもできます。(2)MATCHで行番号を求める問題A列に1~3の番号を入力した。番号を入力した行について、B列~E列のデータをセルH9:K11に転記しなさい。解説まず、MATCH関数(照合一致)を用いて、番号を検索して行番号を求めます。「=MATCH(G9,$A$2:$A$11,0)」となります。基準をセルA1とすると、この行番号がそのまま使えます。列番号は+1~+4なので、セルH8~K8に1~4と入力します。複合参照で「=OFFSET($A$1,MATCH($G9,$A$2:$A$11,0),H$8)」となります。基準をセルB1とすると、列番号は0~3です。複合参照で「=OFFSET($B$1,MATCH($G9,$A$2:$A$11,0),H$8)」となります。問題セルA1を基準とする。G列の行番号のレコードについて、氏名をH2:H5に転記しなさい。科目1~3の合計が2000以上であれば合格、そうでなければ不合格としなさい。解説セルA1を基準とすると氏名の列番号は+1なので「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列に1~4を入力します。これはセルA1を基準としたときの列番号です。氏名は「=OFFSET($A$1,$H$1,F3)」です。科目の判定は「=IF(OFFSET($A$1,$H$1,F4)>=700,"合格","不合格")」となります。8.マクロでは転記先も相対を使う問題セルA3とG3を基準として、次の処理を疑似言語で記述しなさい。2つの表は別のシートに入力すべきですが、解説の便宜上、1つのシートに入力しています。(G列)A列のうち3の倍数をセルG4以降に転記する。セルA4から下向きに探して空白セルが見つかった時点で処理を終了する。(H~K列)A3から始まる表のうち上から3件目、6件目、9件目、・・・(3の倍数)のレコードをセルH4以降に転記する。(L列)さらに、3科目の合計が1000以上の場合に合格、そうでなければ不合格とする。解説マクロの場合は、転記元と転記先のそれぞれに基準となるセルを設定して、相対で表します。転記元は通常、左上のセルを基準として、レコード件数を数える変数(cntなど)を用意します。セルA3を基準とするとき1件目のレコードの行番号は1です。1この変数は1ずつ増やしていきます。1列目が空白のときに処理を終了するのであれば、Whileによるループになります。行番号が3の倍数かどうかの判定をします。転記先も基準があります。セルG3を基準とすると、G列の行番号はrow_cnt/3、列番号は0です。氏名は、転記元「相対(A3,row_cnt,1)」、転記先「相対(G3,row_cnt/3,1)」です。■3科目の得点は、相対(A3,row_cnt,2)~相対(A3,row_cnt,4)ですが、行番号や氏名も含めてすべてループで転記したほうがすっきりします。合計を求めるのもループを使います。試験問題はこのようにして作られるのですが、自分で、疑似言語やVBAを書いてみたら出題者の意図が理解できるのではないかと思います。疑似言語は平成29年秋の試験問題の一部を変えたものである(改題)。どのような処理をしていると考えられるか。解説まず、最初と最後の2行だけ取り出すと、「ログ!A1」を基準とする表があることが分かります。列番号+2の列(C列)を探索していることが分かります。log_lineが1から始まることから、ログ!C2から下向きに空白セルが見つかるまで処理をしています。これが転記元の表であると考えられます。この表の行番号は「log_line」です。このC列のデータは「log_url」にいったん格納されています。2)さらに、アクティブシートのセルA1を基準とする表があることが分かります。列番号0の列を探索していることが分かります。count_lineが1から始まることから、A2から下向きに処理をしていると考えられます。これが転記先の表であると考えられます。この表の行番号は「count_line」です。A列のデータは「count_url」にいったん格納されています。その後、「log_url」を転記しています。log_url|▼これで、なんとなく流れが分かったと思いますが、この問題は「ログ」のなかから重複をなくして転記する処理をしています。転記先のシートに存在しなければ転記して、存在すれば転記する必要はありません。count_url<>log_url)」となります。試験対策として、試験問題で使われている相対の意味をOFFSET関数で解説したものです。ExcelのOFFSET関数やVBAのOffsetの使い方を説明したものではありません。わえなびチャンネルhttp://www.youtube.com/c/waenavijpチャンネル登録をすると最新の動画をチェックできます。初心者向けの動画も大量にあります。平成三十年八月]スターをつけました引用をストックしましたストック一覧を見る閉じる引用するにはまずログインしてくださいログイン閉じる引用をストックできませんでした。再度お試しください閉じる限定公開記事のため引用できません。