複数のExcelシートからデータを集めるマクロ。試行錯誤と思考過程。

複数のExcelシートから、データを集める方法の1つは、マクロです。
基本のパターンとその考え方を押さえておきましょう。

データを転記する事例

今回の事例はこういったものです。
人ごとにシートがあり、それぞれのシートにデータがあります。
有給休暇のデータです。
このままだと、一覧でチェックできず、不便なので、

このように、1つのシートにまとめます。
（名前、データはランダムにつくりました）

これを1つずつやっていたら大変です。
・コピペすればいい
・年に1回だから。月に1回だから。
・しかたない

と思わないようにしましょう。

最もいいのは、こういった転記をしなくてすむようにExcelファイルをつくることではありますが。

Excelマクロは、まず1つつくる

Excelマクロ、プログラミングの基本は、「まず1つつくる」です。
（私の考えですが）
「全部のシートからデータを集める」ということをいきなりやろうとすると、それなりに大変です。
慣れてくればそれでもいいのですが、最初のうちは、「まず1つつくる」を確実にやりましょう。
というより、慣れてきても、この方法で私はやっています。
今回のようなマクロは、シートの数だけ処理を繰り返すわけで、間違ったものを繰り返しても意味がありません。
（プログラミングに限らず、あらゆることで言えますが）
1つを完璧につくってから、繰り返す部分をつくりましょう。

データを集めるシートは、「data」と名づけ、このように、見出しを入れておきます。

ひとまず森さんのシート「森 一明」から、セルB2にある「森 一明」というデータを

シート「data」のセルA2に転記することを考えましょう。

ExcelでAlt＋F11（AltとF11の同時押し）で、VBE（マクロを書くソフト）を開き、Alt→I→M（Alt、I、Mと1つずつ順番に押す）で、標準モジュールを開き、次のように入力します。
（セルを示すA2は、a2でもA2でもかまいません）

SubはExcelマクロのはじまりで、End Subが終わりで、data_collectが、マクロの名前です。
マクロ名は、任意で、私が勝手につけたものですので、原則として自由につけられます。

入力は次のようにやりましょう。

sub data_collect

と小文字で入れて、Enterキーを押せば、
Sub data_collect()
End Sub
と、SubとEndの頭文字が大文字になり、（）が入ります。

SubとEndの間にマクロを書いていきます。

シート「data」のセルA2に、シート「森 一明」のセルB1を入れたいところです。

マクロでは、

〇＝△

で、〇に△を入れるという意味になります。

この場合は、〇が、シート「data」のセルA2、△がシート「森 一明」のセルB1ですので、

シート「data」のセルA2 = シート「森 一明」のセルB1

と書くわけです。
ただ、このまま書いては、マクロが動きません。
それぞれのプログラムに合わせて、適切な言葉に翻訳しなければいけないのがプログラミングです。

シートは、Worksheets、セルはRangeと書き、セルの値という意味で、Valueも使います。

シート「data」のセルA2 = シート「森 一明」のセルB1
は、

と書くわけです。

シート、セル、値は、.（ピリオド）でつなぎます。
これを実行（VBEでF5）すれば、こうなるはずです。

その他、4つの項目を同じように転記するので、今書いたマクロをコピーしておきましょう。

転記先のシート「data」のA2からE2までにデータを入れたいので、

まずは、左側（〇の部分）を次のように変えます。
緑色の文字は、コメントです。
頭に’（シングルコーテーション）をつけると、プログラムとみなされません。
つけないと、「これ、何？」とエラーがでます。

その後、転記元のシートのセルAA2からAA5、AA8を指定するように、

右側（△の部分）を変えていきましょう。
このセルの指定を間違えると正しく処理できません。

実行してみると、このように正しく転記されていることがわかります。

間違っていれば、マクロを修正しましょう。
間違ったら直せばいいやくらいがちょうどいいです。

Excelマクロで繰り返しの部分をつくる

1つめはうまくできたので、繰り返していきます。
すべてのシートで繰り返すというマクロがありますので、それを使いましょう。
まずは、この3行を書いてしまいます。
W_sというのは任意です。好きなものでかまいません。

For Each～
と
Next
の間にあるものを繰り返しますので、さきほどつくった部分をまとめて入れます。
Ctrl＋Xで切り取りましょう。

インデントをつけて（Tab）、このようにしておくとみやすくなります。

ただ、このままだと、いつまでたっても「森 一明」のシートからしか転記しません。
すべてのシートという意味で、せっかくFor Eachを使っているので、それを活用します。
W_s（変数。場合によっていろんなものが入る箱）を使えば、シートを次から次へと処理することができるのです。

Worksheets（”森 一明”)
を
W_s
へ変えていきましょう。

コピペで貼り付けてもいいのですが、Ctrl+Hで置換することもできます。

このように変われば、すべてのシートを1つずつ処理してくれるはずです。

F5で実行してみましょう。
う、うまくいったようで、そうでもありません。
1人分しかデータがないのです。

シートをよくよく見ると最後のシートのデータだけが入っています。

こういったときには、VBEでF8を押してみましょう。
F8を押すたびに1つずつプログラムを実行します。

シート「森 一明」を入れ終わり、

F8でさらに実行していくと、次のシート「小林 郁一」のデータが入りました。

2行目の次は3行目に入れてくれればいいのに……と思っても当然です。
そうプログラミングしていないのですから。
（これ、対人にも言えて、言われたとおりにやったのに、怒られることってあるかと。人に仕事を依頼する人はすべからくプログラミングやったほうがいいと思っています）

マクロを見てみると、a2、b2、c2、d2、e2とあります。
これが、繰り返すときには、

a2、b2、c2、d2、e2
の次に

a3、b3、c3、d3、e3

そして、

a4、b4、c4、d4、e4

となってほしいわけです。

こういった場合、2、3、4と変更する部分を、変数にします。
方程式のxのようなもので、箱のようなものです。

iという変数を使って（任意です）、
”a2”

を
“ai”

としてしまうと、aiという指定になってしまいます。
セルには、aiというものはないのでエラーが出てしまうのです。

マクロでは、
・文字は、””（ダブルコーテーション）で囲む
・それ以外（変数も含む）は囲まない

というルールがあります。

“ai”
ではなく、

“a”i

になるのですが、これでもエラーになります。
文字と変数をくっつけるのは、＆が必要です。
結果、
”a”& i
となります。

こう書き換えてF5で実行してみましょう。
このようなエラーが出てしまいます。
変数であることを伝えていないからです。
「例のあれ、どうなった？」といって、「あれ」がわからないのと同じことになります。

ここで、
Dim i
と、iは変数ですよと書きます。
（Dim i As Longとするのがベターですが、ここでは省略しています）

F5で実行すると、またエラーが。

F8で実行して黄色い部分にカーソルをあてると、iの中身をみることができます。
i＝Empty、つまり空です。
空のものをaとくっつけようとしているからエラーが出ます。

iに何か入れましょう。

最初は、a2と指定したいので、iに2を入れます。

i =2 としておきましょう。

F5で実行すると、こうなります。

マクロを見てみると、i＝2としているので、ずっと2のまま、a2、b2、c2…‥と指定している状態です。

「i=2を1つずつ足して」というマクロを入れましょう。

i = i+1
というのは、「〇＝△」で「〇に△を入れる」と考えるので、「iにi+1を入れる」という意味になります。
i＝ 2+1、だから、i = 3 という考え方です。

F5で実行するとこうなります。
できた！と思いきや、セルA2には、「前年繰越」が。

こういうときも、F8で1つずつ実行すると、原因がわかりやすいです。
「すべてのシートで」というマクロなので、シート「data」のセルB1も集めてしまっています。

すべてなんだけど、シート「data」は除いて と伝えなければいけません。
（神龍に、「ブウに殺された人を生き返らせて。ただし悪人は除いて」といったような）

条件を指定するのは、IFです。
If ○ Then
End If

で、もし〇だったら、IFとEndの間の処理をします。

この場合は、それぞれのシートの名前（W_s.Name）が、”data”じゃなかったら（＜＞）という条件を入れました。

For Eachと同様に、IfとEndの間に、これまで書いた部分を入れます。

F5で実行すると、データが無事集まりました。

おさらいで解説しておくと、繰り返しの部分の1周目は、i＝2

データを転記した後に、i＝3になります。

2週目は、i＝3、つまり3行目に転記し、最後にiが4になり、シートの数だけ繰り返してくれるのです。

こういったマクロをつくっておけば、シートがいくら増えても手間は変わりません。
転記の基本パターンの1つですので、ぜひ、書いてみていただければ。
こうやって試行錯誤しながら考えていくのがプログラミングです。
私もそうしています。
達人ならかんたんに書けるのかもしれませんが。

プログラミングは、他の仕事への波及効果もありますし、その思考過程は、効率化につながります。

サンプルはこちらです。
シートを増やしてみてもおもしろいかと。

EX-ITサンプル 複数シートからデータ集計マクロ.xlsm

なお、今回のサンプルをつくるときにもマクロを使いました。
こういったサンプルつくるの手でやったら大変ですし。
別途記事にするかもです。

新刊にもマクロの基本は載せています。

 

 

---

■編集後記

昨日は、個別コンサルティングで、PCの設定。
新PCを設定して使えるようにしていくのはワクワクします。

■昨日の「1日1新」

俺の割烹

■昨日の娘日記

クリスマスプレゼントは、アンパンマンのミニドレッサー。
ちょっと早いかもですが、ドレッサーやくしに興味を持っていたので。
ドライヤーやアクセサリーもついています。