基本編に続き、３つのポイントを駆使して別のパターンのデータ操作をやってみましょう。基本編では、行の変数だけ使用しましたが、応用編では列も変数で操作します。ここまで習得すると、二次元の操作ができることになります。そして、二次元の操作ができると、ほぼできない事はないくらい、エクセルの自動化が実現できます。慣れるまで頭が混乱する場面もあるかもしれませんが、使用するのは３つのポイントだけです。より付加価値の高い仕事に時間を使えるよう、頑張って習得しましょう。
応用編
クロス集計
エクセルのクロス集計作業については、ピボットテーブルを駆使されていることと思います。前年との比較、予算との比較をする際に、行列のズレに苦慮された経験はありませんか？前の年はあったサイズがなくなったり、新しいカテゴリーが増えたり…年度を切り替えるたびに表の行列がズレてしまうと、計算に注意が必要です。
ここでは、表の行列を固定して定形の集計を行います。予めフォームを固定することで、データ上に項目（エリアや商品）の改廃があっても比較できることと、主要な項目に絞って傾向を確認することができます。基本編で作成した”加工 data”シートを元に、変数を操って「Do Until 〜Loop」、「If (A) then (B) else (C) End if 」でプログラムを作成しましょう。
次のPDFを印刷してください。
集計フォームの作成
表の作成にあたっては、一旦ピボットテーブルでクロス集計して用途にあったフォームを作ることになります。今回は、すでにフォーム作成済みの状態で進めます。
sample_02を保存して開いてください。
基本編で作成した”加工 data”シートが含まれています。このシートを元に、”全社”/“東京”/“大阪”/“愛知”のシートにあるフォームを埋めていきます。
ちなみに、”加工 data”シートの”cate No.”は1〜12まで存在します。主要な1〜9までを表頭に用意し、10以上は”other cate”に纏めます。”size No.”についても、主要な1〜9までを表側に用意し、10以上は”other size”に纏めます。
それでは、手始めに”全社”シートへの集計を行います。
表に数字を埋める
“加工data”シートを元に”全社”シートの表を埋める作業です。手順を確認します。
- “加工data”シートの２行目からデータが無くなるまで、”全社”シートへ集計する作業を繰り返す。
- “加工data”シート２行目のE列(cate名)の値が、”全社”シートのどの列の”cate名”と一致するか決定する。
- “加工data”シート２行目のG列(size名)の値が、”全社”シートのどの行の”size名”と一致するか決定する。
- “加工data”シート２行目のA列から、”前期”、”当期”を判断・決定する。（エリア別の場合は、エリアコードも判断に加える。）
- 前段で決定した条件で”全社”シートへ「ケース数」、「売上高」、「粗利」を落とし込む。
- “加工data”シートの次の行に移る。
以上の内容をプログラムします。
まず、準備編を参照して、新たにmodule2を作成してください。
module2にサブプロシージャ「全社_matrix」を作成します。
変数は３つ用意します。“加工data”シートを縦に移動する変数が１つと、クロス集計のために縦軸と横軸を移動する変数が１つずつ、合計３つという訳です。
フォームのデータをリセットするために、「全社」シートの実績値の部分をクリアします。「ケース数」、「売上高」、「粗利」を「前期」、「当期」に分けて集計するので、範囲は６区画あります。下図の範囲を参照して、マクロの記録で作成しましょう。
以上で、準備完了。ここから、手順に示した作業を行います。
“加工data”シートの縦軸の変数を”a”とします。“加工data”シートのデータは２行目から始まるので
a=2
手順１の「“加工data”シートの２行目からデータが無くなるまで、”全社”シートへ集計する作業を繰り返す。」とは、「“加工data”シートのデータが空白になるまで」として
Do Until Sheets(“加工data”).Cells(a,1)=“” 〜 Loop
と書きます。
手順2〜手順6は、Do Until Sheets(“加工data”).Cells(a,1)=“” と Loop の間に作成します。
手順2の「“加工data”シート２行目のE列(cate名)の値が、”全社”シートのどの列の”cate名”と一致するか決定する。」では、“加工data”シート２行目のE列(cate名)の値が”全社”シート４行目のどの列の値と一致するか？ということです。”全社”シートの横軸の変数をbとして検索(b=b+1)させると、cate①〜cate⑨までは”全社”シートの2列目〜10列目までの値と一致します。cate⑩以上は11列目に収めたいので、b>10つまりb=11になったらLoopを抜けるよう命令するとcate⑩以上は11列目に決定される訳です。
ちなみにシングルクォーテーション以降の緑色の部分は、基本編で説明した通り、作業の内容を表示しています。プログラムとしては、機能しないので何を命令しているか書いておくとプログラムを理解しやすくなります。
手順3の「“加工data”シート２行目のG列(size名)の値が、”全社”シートのどの行の”size名”と一致するか決定する。」では、“加工data”シート２行目のG列(size名)の値が”全社”シートA列目のどの行の値と一致するか？ということです。”全社”シートの縦軸の変数をcとして検索(c=c+1)させると、size①〜size⑨までは”全社”シートの5行目〜13行目までの値と一致します。size⑩以上は14行目に収めたいので、c>13つまりc=14になったらLoopを抜けるよう命令するとsize⑩以上は14行目に決定される訳です。
手順4の「“加工data”シート２行目のA列から、”前期”、”当期”を判断・決定する。」は、二者択一なので、ここでは何も手を加えず、手順5で命令をすることにします。
手順5「前段で決定した条件で”全社”シートへ「ケース数」、「売上高」、「粗利」を落とし込む。」では、手順2で決定した列の値(b)/手順3で決定した行の値(c)を元に、前年か？当年か？でif構文を使って”全社”シートの表を作成します。
上記の命令を加えます。
プログラムの最初の3行を読むと、「”加工data”シートの”a”行１列が”前期”だったら、”全社”シートのc行b列に”加工data”シートの”a”行８列の値（ケース数）を持ってくる。」となっています。注意点は、
sheets(”全社”).Cells(c,b)=sheets(”加工data”).Cells(a,8)…①
だと、同じセルに入るデータがあった場合に、常に最新のデータに書き換えられる点です。
sheets(”全社”).Cells(c,b)=sheets(”全社”).Cells(c,b)+sheets(”加工data”).Cells(a,8)…②
とすることで、同じセルに入るデータがあっても加えながら集計できます。累積して集計する場合は、必ず②のようにプログラムしてください。
プログラムの４行目から９行目では、売上高は決定した行に59行加えて、粗利は118行加えることで表を完成させています。エクセルの行数を確認していただくと、それぞれが同じカテゴリー/サイズに相当していることが解るでしょう。
10行目以降は、当年だったら…という条件で、同様のことを行っています。
最後に、a=a+1を加えることで”加工data”シート次の行に移行し、Do Until Sheets(“加工data”).Cells(a,1)=“”とLoopの間を繰り返します。Do Until Sheets(“加工data”).Cells(a,1)=“”となった時点でLoopを抜けてプログラム終了です。
完成したマクロを確認しましょう。
module2「全社」
基本編に比べると長くなりましたが、これだけで「ケース数」、「売上高」、「粗利」を前年と当年に分けて集計できました。
このマクロは、元データのデータ件数が増えてもきちんと集計されます。ピボットテーブルだと範囲の再指定をしないと正しく集計できません。再指定の手間だけでなく、ミスを防ぐ点においてもメリットは大きいでしょう。
次は、「東京」、「大阪」、「愛知」について同様の集計をしますが、集計のフォームは「全国」の集計と全く同じです。シート名を変更し、一文加えるだけでマクロが出来上がります。やってみましょう。
新しいmodule3を作成し、そこに”全社_matrix”を貼り付けます。”全国”→”東京”に置き換えます。(メニューから編集→置換。）
置き換えたら、下の赤線の部分を書き加えるだけです。(”=1”としたのは、東京のarea No.が1だからです。)
「大阪」「愛知」も同様に、文字を置き換えて、大阪なら「And Sheets(“加工data”).Cells(a,2)=2」
愛知なら「And Sheets(“加工data”).Cells(a,2)=2」
を赤線部分に加えるだけです。
それぞれ、module4、module5に作成してみましょう。
module4「大阪」
module5「愛知」
以上で必要なマクロは完成ですが、あと一つ。
実行ボタンを作る
ワンクリックで作業を完了するために、マクロボタンを作ります。基本編で作成したように、一度に実行するマクロを作成します。module6に以下のサブプロージャを作成します。
「全社」シートに「四角形」を描き、テキストを「マクロ実行」とする。
このボタンにマクロの登録（main2を指定）をすれば完成です。
いかがでしょう。慣れないうちは、脳みそに疲れを感じるかもしれません。実際には、慣れた人でも一発でプログラムを書き上げることはないそうです。書いて、動かしてみて、エラーを確認して、修正を加える…。確認を繰り返しながら完成させていくそうです。はっきり言って、その時だけの作業であれば関数を駆使する方が早いです。しかし、最初に申し上げた通り、毎日、毎週、毎月など、繰り返し作業する場合は、ちょっと脳みそに汗をかいて作り込みをしておくことを勧めます。その年にアウトプットする資料のイメージができているなら、最初の１ヶ月残業してでも、プログラム化しておいてください。２ヶ月目からワンクリックで作業が終わります。後に生み出される時間を、新たな仕事に振り向けられたなら…。どんどん、自分の業務を改善していってください。
（完）