【VBA実践】別のシートでコピーしたデータを1つのシートに集約させて、蓄積させていく。

私がVBAをマスターしたいと思った技術の1つです。
誰しもがブック内にあるたくさんのシートにあるデータを
1つのシートにまとめたうえで管理したいと考えたことがあると思います。

もちろん、シートだけでなく他のブックからデータを引っ張ってくることも出来ます。

VBAの勉強を始めようとした私が最初にぶち当たった壁なんですが
これは、マクロの記録だけでは対処のしようがないんです。

マクロの記録とVBAは同じようで同じではないです。

マクロは処理の自動化で
Excel内で作業をした内容を記録させて
同じ動きをさせることが出来る機能なんです。
厳密にいうと、さらに意味は全く違いますが。

VBAはそんな自動化処理をするための文章を
直接書き込む事なんです。

似て非なるもの。
【白い恋人】と【面白い恋人】くらい差があるかもしれません。

そして、VBAには出来て、マクロの記録には出来ないことがあります。

条件により作業内容を変える!ということ。

マクロはやったことを記録して、その通り繰り返すだけ。
VBAは条件に応じてその先の処理内容を変えていくことが出来ます。


すみません、横道にそれましたが
今回のテーマもデータを蓄積させるさせるためには
データが入っている一番下の次のセルに別のデータを張るという作業の繰り返しになります。


ここで必要な大きな作業内容を説明しますと…


・蓄積させたいデータコピーする。
・貼り付けたいシートを選択し、現段階でシート内に
　データが何行目まで入っているかを確認する。
・そのデータの1つ下のセルにデータを貼り付ける。
・蓄積させたいデータが入っているシートの数だけ
　その作業をループする。


大きく分けてその4つ分のコードを書けば、希望の処理を行うことが出来ます。
まず、答えは以下の通りです。
※ブック内一番左側にAll_dataという名前のシートがあり、
以降そのシートにデータを蓄積させる元データのシートがあることを前提としてます。
-------------------------------------------------
Sub test()

Dim scount, row　As Long

For scount = 2 To Sheets.Count

    Sheets(scount).Select
    Range("A1").Copy

    Sheets("All_data").Select
    row = Cells(Rows.Count, 1).End(xlUp).row + 1
    Cells(row, 1).Select
    ActiveSheet.Paste

Next scount

End Sub

-------------------------------------------------

※変数は赤文字で表示してます。
この中に上の4つの条件が書かれています。


順序としては以下の通りです。
・蓄積させたいデータが入っているシートの数だけ
　その作業をループする。
For scount = 2 To Sheets.Count
～～処理内容～～
Next scount

ここでFor文というのを使います。
ここでの使い方はシート2シート目から
最後のシートまでの枚数を割り出し、順に処理をしていくという
ループ処理指示になります。

なお、For文に関しては
後日別の記事で詳しくご案内しますね。


・蓄積させたいデータコピーする。
For文で選択されたシートのコピーしたいセルを指示して
コピーします。

Sheets(scount).Select
Range("A1").Copy
この2行です。
なお、RangeプロパティでもCellsプロパティでも、
Rangeプロパティで広い範囲を指定してコピーしてもOKですよ。


・貼り付けたいシートを選択し、現段階でシート内に
　データが何行目まで入っているかを確認する。

Sheets("All_data").Select
row = Cells(Rows.Count, 1).End(xlUp).row + 1
Cells(row, 1).Select

貼り付けるシート、つまりAll_dataというシートを指示してあげます。(1行目)
ここからが魔法の言葉なんですが
Cells(Rows.Count, 1).End(xlUp).rowという記述が
選択しているシートで何行目までデータが入っているかというのを
調べる記述です。
Cells(Rows.Count, 1)の1はA列を示してるので
B列の末端を調べるときはここを2に変えてください。
それを変数rowに代入して、Cellsプロパティに当て込んであげればいいわけです。
しかし、End(xlUp).rowの後には必ず【+1】を付け加えてあげてください。
そうじゃないと、ずっと同じ場所にデータが毎回貼り付けられてしまい
蓄積もクソもない、鬼畜の所業になってしまいます。
もっちょいかみ砕いていうと、
データが入っている最終行を割り出したその下にデータを貼り付けたいので
最終行の1つ下、つまり【+1】を付与して1つしたにずらしてあげるわけです。


・そのデータの1つ下のセルにデータを貼り付ける。

ActiveSheet.Paste

あとは選択した箇所にペーストしてあげればOKです。



こんな時、特にCellsプロパティの便利さが実感できます。

たかだか260文字程度を入力するだけで
たくさんのシートにあるデータをかき集めることが出来るのです。

しかも一瞬で。


もちろん、これが全世界の正解ではありません。
VBAはたくさんのやり方が存在します。

これはそんな方法の1つだと思っていただければ思います。

【Excel】オーフィルと親父の小言は後で効く…

オートフィル。

オートフィルタと似てますが、一文字違いで全く別のものです。


オートフィル

Excelで連続的にデータがあり
その隣のセルにそのデータに対して関数を設定し
上から下までその関数を同じ条件で入れるときに
関数を入れて、そのセルの右下のあたりに
ポインタを持っていったら、白い太めの十字から
黒い細めの十字に変わります。

その瞬間に、マウスをダブルクリックすると
隣のデータが入っている下のセルまで
ザイーン！！って関数が引き継がれる、なんとも便利なExcelの機能です。

さて、勘のいい人はここでピンと来るはずです。


隣のセルのデータが入っているところ。


そうです。



つまり、A1からA10000までデータが入っていて
そのとなりのB1からB10000まで関数をザイーン！ってさせたい時。

縦10000行まで結構な長さがあります。

これ、私みたいなめんどくさがり屋さんは
ろくに下の10000行までデータが入っていることを確認せず
次の作業に進めようとします。

だって、オートフィルじゃん！
下まで行ってんじゃん！



でも。





もし。










A6859行目にデータが何も入ってなくて
A6860行目から、続きのデータが入っていたら…











はぅあ～！！




オートフィルはA6859で止まってしまっているのです。




はい～、処理ミス発生!




慣れてきたころ、このオートフィルの機能を軽視して
ろくに最後まで確認せず次の作業に移行しようとする輩が
多くなるものです。私のように。




今日、このオートフィルの機能を軽視した私は
爆死しました。
本当にすみません。










私が無意識でも調子に乗り始めようものなら
オートフィル先輩は
調子に乗るなよ!!

と、常に戒めてくれました。


私の長所でもあり短所でもある、
すぐ調子に乗ること。


過去どんだけあるんだって話ですが。



人間としてまだまだなのであります。




VBAのオーフィルはいつかご紹介させて頂きますね。

【VBAリファレンス】セルの値を指定する方法は主に2種類あるのです。

Excelに値を書き込んだり、読み込んだりする時
そのセルを指定した上で行います。

セルの一番左上のセルがA1という番地？になってます。

そのセルに文字を入力するときは
マウスで十字マークをぬゅ～んって持って行ってカチってやったら
指定できますよね。

VBAではぬゅ～んって持って行ってカチってのを文字で記載する必要があります。



下の図で黄色い部分はA1でオレンジの部分はB3となります。

VBAで指定するとき、
メジャーな方法では主に2通りの方法があります。

CellsプロパティとRangeプロパティです。

ざっくり説明すると
Cellsプロパティは縦と横の番地をそれぞれ指定するやり方で
Rangeプロパティは直接セルの番地を指定するやり方です。

多分7割くらいの方がはっ？ってなっているかもしれません。

ちょっとコード書いてみますね。

--------------------------------------------
Sub test()

’Cellsプロパティ
Cells(1, 1).Select　’A1を選択
Cells(3, 2).Select　’B3を選択

’Rangeプロパティ
Range("A1").Select　’A1を選択
Range("B3").Select　’B3を選択

End Sub

--------------------------------------------

なんとなく違いが分かりますでしょうか。

Cellsプロパティで選択をする場合、列と行の並びが逆になります。
→Cells(3, 2).Selectは上から3番目左から2番目の場所って書き方になります。
最初は混乱しますが、記述し続けると慣れてきます。
Cellsプロパティのメリットですが
カッコ内、数字を記載してますが、この数字の代わりに
変数を埋め込むことが出来ます。
そのメリットも変数を使い慣れてきた時、
また、For文やIf文使うようになった時に実感できるようになるともいます。
最初は縦横逆だし、ただ煩わしいだけなんですよね。

Rangeプロパティはそのままセルの値を記述します。
その時はダブルクォーテーションで囲ってください。
Rangeプロパティのメリットはコロンを間に挟むことで
広い範囲を簡単に選択することが出来ます。
こんな感じです→Range("A1：C10").Select
Cellsプロパティでも出来ないことはないんですが、いささか面倒なんです。


さて、ここまでは選択するという記述Selectですが
その部分から先を変えると値を入れることが出来ます。
--------------------------------------------
Sub test()

Cells(1, 1).Value = "こんにちわ" 'A1にこんにちはと入力
Cells(3, 2).Font.Color = vbRed  'B3の文字の色が赤くなる

Range("A2").Value = 1 + 2  'A2に1+2の結果つまり3が入る
Range("B4").ClearContents  'B4に文字が入っていたら消去される

End Sub
--------------------------------------------

その他の指定方法でR1C1ってやり方もあるんですが
これがかなりややこしいので、今回の説明は見送ります。

まずは、いろいろ入力して遊んでみて
是非セルの値の操作に慣れてみてください。

【VBAリファレンス】変数は宣言してください。お願いします。

記念すべき最初の記事は

変数　です。

プログラミングの世界では

この変数というのを必ず使用します。

VBAのプログラムの冒頭にこういうのを見たことないですか。

(青い字の部分)

変数の宣言を宣言するといいます。

---------------------------------

Sub test()

Dim a as Long

a = 10

MsgBox a

End Sub

---------------------------------

青い部分をざっくり説明すると

入れ物(箱みたいな)を作るということを

常にイメージしてみてください。

ここではaという箱の名前です。

これが変数名といいます。

そして、宣言するだけだと

ただの箱を作っただけで、中身は空っぽなので

その中に"値"を入れます。

それを代入いいます。

例えば、りんご箱、中身がなければただのダンボール箱です。

そこに

1【赤いりんご】を入れる

2りんごを【6】個入れる

3【2016年5月31日】に収穫したりんごを入れる。

など、入れ方はさまざまあります。

それが変数を宣言するときに合わせて使う

変数の型というものです。

1は【赤いりんご】というテキストなのでString

2はりんごの個数、整数なのでLong

3は【2016年5月31日】で日付なのでDate

など、タイプに合わせてその型がたくさんあります。

さて、型は覚えないでください。

後で勝手に覚えるようになります。

じゃあ、変数名の後、どうやって記述したらいいのん？となりますが

Dim a　だけ書く

それだけの記述でいいです。

省略出来るのです。

その場合、変数の型はVariant型(どんなものでも入れられる万能入れ物)という扱いになります。

慣れるまでは、Variant型のままでいいです。

逆になれてきたら、それぞれの型で変数を宣言するようにしてください。

なんか、Excelの初期設定で

変数を宣言しなくても使用できる仕様になっているのでここでは

それも直しておきましょう。

意味が分からなくてもいいです。
やってない方は最初のうちにやっておいて下さい。

この設定は変数を使うときは絶対に変数を宣言しないといけないって設定です。
これやらないと後悔します。
OfficeTANAKAの田中さんも口酸っぱく言ってます。

やっとかないと後々後悔します。
詳しい理由は田中さんのWebサイト見てみてください。


第一歩は変数に慣れてみてください。

ブログを開設します。

はじめまして。

匡純(まさずみ)といいます。

この度ブログを開設しました。

私は仕事柄、大量のデータを扱いことが多く
そのほとんどがExcel(あとCSVとか)なのです。

約1年前くらいまでは某大手通信業界で業務委託の仕事をしており
その時はExcelの関数を得意としてました。

特技はExcelですって言えるくらい、
大体どんなことでもできてました。
やっていたつもりでいたのです。

なので、VBAの勉強もしたかったのですが
業務上のWebの閲覧を許されないことや、
元々怠け者体質も手伝って、関数ができることまでで満足してました。

井の中の蛙です。
世界は広かったのです。
そして、誰もいなくなりました。

やがて仕事が変わって、扱う内容も大きく変わり、
関数やマクロの記録だけでは対応できなくなってきた時、
自分の愚かさを呪いつつ、本気でVBAを勉強し始めたのです。

VBAってプログラムなんです。

JAVAとかC言語とかRubyとかPythonとか
あれのあれなんです。

でも、エンジニアの方からしたら
VBAなんてプログラムに入らないっていう方もいるのです。
おそらく、フリーザ様からみた地球に送られるカカロットなのでしょう。


だからどうした。


最初から役に立たないと投げ出すより
Excelと電気代だけ払えれば、身に付けられるスキルです。
少なくとも、変数やFor文の概念はこのVBAでしっかりと身に付けることが出来ました。


ここでは、自分で身に付けたことを書いたり
驚いたことの備忘録や、
シェンロンにお願いするよりも処理が早くなる方法を
ブログで公開したいと思います。

いかんせん、独学でやっているので
おかしな記述方法や、わけわかめな解釈をブチ込んでくる恐れもあります。
自信があります。
でも、どうか優しく見守ってください。

そして、時々どうしようもない奴だと笑いながら、
交流をしていただければとてもうれしく思います。


どうぞ、よろしくお願いします。