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

696

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

image

データを転記する事例

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

image

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

image

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

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

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

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

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

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

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

image

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

image

ExcelでAlt+F11(AltとF11の同時押し)で、VBE(マクロを書くソフト)を開き、Alt→I→M(Alt、I、Mと1つずつ順番に押す)で、標準モジュールを開き、次のように入力します。
(セルを示すA2は、a2でもA2でもかまいません)
image
SubはExcelマクロのはじまりで、End Subが終わりで、data_collectが、マクロの名前です。
マクロ名は、任意で、私が勝手につけたものですので、原則として自由につけられます。

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

sub data_collect

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

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

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

マクロでは、

〇=△

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

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

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

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

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

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

image

と書くわけです。

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

image

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

image

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

image

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

image

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

image

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

image

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

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

image

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

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

image

For Each~

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

image

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

image

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

Worksheets(”森 一明”)

W_s
へ変えていきましょう。

image

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

image

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

image

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

image

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

image

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

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

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

image

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

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

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

a3、b3、c3、d3、e3

そして、

a4、b4、c4、d4、e4

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

image

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

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


“ai”

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

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

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

“ai”
ではなく、

“a”i

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

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

image

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

image

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

image

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

image

iに何か入れましょう。

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

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

image

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

image

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

image

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

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

image

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

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

image

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

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

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

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

image

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

image

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

image

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

image

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

image

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

image

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

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

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

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

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

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

 

 


■編集後記

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

■昨日の「1日1新」

俺の割烹

■昨日の娘日記

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

Spread the love