るんるんモノマネEXCEL VBA 本〜仕事で覚えたEXCEL＆VBA小技 BOOK〜 キモの関数、そしてIEを自在に操るVBA。初めはモノマネが一番いい！

highschoolgirl

　自分のレベルにあった市販のEXCEL本。出会ったらいいね。でもそんな本はなかなか見つかりません。ワタシも幾つか買いましたが、仕事の効率化に役立つ、これぞという本に巡り会うことはできませんでした。それは、求めれば求めるほど離れていく恋人のようなものかもしれません。もっといいj本があるはずだ。明日こそ、見つかるかも。いや、こうなったら、自分で作るしかないなと。
　てなわけで、EXCELの必須関数や日常業務の自動化を実現するための「EXCEL VBA の始めかた」を書いてみました。
　最初の１歩の踏み出し方さえわかれば、あとは自転車と同じで、スイスイと、いや「どうにかこうにか」できるようになります。
　「買うより始めろ。習うより慣れろ。」
　仕事で苦しみもがき、何度も心が折れそうにながらも、必死で覚えた「１０年間のノウハウ」をすべて詰め込みました。
　仕事で覚えたEXCEL、渾身のEXCELノウハウ集（作品）です。
 　特に、「IEブラウザを自在に操るVBAテクニック」をマネしてください。動けばよいのです。動いたら、「勝ち」です。
　すべて、るんるんでモノマネに始まり、モノマネで終わってもいい。

はじめに　イチローのように・・・
第１章　キモの関数
１．１　SUMとSUBTOTALを使い分ける
１．２　ピボット（PIVOT）でクロス集計する
１．３　ブイルックアップ（VLOOKUP）する
１．４　VLOOKUPでは＄（ダラ）が必須だら 

１．５　SUMIFSとCOUNTIFSで集計する（プログラムレス）

第２章　IEを自在に操るVBA
２．０　VBAのはじめかた
２．１　VBAでIEを自在に操る
２．１．１　Webシステムへログインする
２．１．２　行きたいページへジャンプする
２．１．３　欲しい情報をゲットする
２．１．４　ラジオボタンを操作する
２．２　家計簿をつくる
２．２．１　 モノまねVBAで家計簿〜まずは、キホン設計！
２．２．２　 モノまねVBAで家計簿を作る〜その２ ユーザフォームでデータ入力する　
２．２．３　 ものマネVBAで家計簿～その３ ピボットで収支管理　これでバッチグー！

第３章　文字列を自在に加工する
３．１　複数のセルで特定の長さの文字列を生成する 
３．２　YYYYMMという文字列からQを抽出
３．３　文字列除去 
３．４　フィルタで〜以上が使えないときには

第４章　とても重宝する「小技（コワザ）」
４．１　見えてるセル（可視セル）のみへコピペする
４．２　いっぱい行をdeleteしたらexcelがコケたならば並べ替えて消せ

４．３　フィルター繰り返しつつ、特定の作業を行う（ autofilter とcurrentReagion ）

第５章　トラブルシューテング
５．１　視点を変えてみる
５．２　デバッグ  
５．３　トラブル原因は社内のセキュリティ強化ではないかも
５．４   そして、まずは、Windowsパッチを疑え   

付録１　VBAの出力メッセージをコピペする 
付録２　最終月曜日を求める
付録３　金額がプラスなら青字にマイナスなら赤字に（正規表現でちゃちゃと）

番外編　習得のコツは隣の師匠

　ここでちょっとひといきのコーナー。

　年を取り、引退（定年退職）すると、「教育」と「教養」が必要なのだそうです。そう聞くと、すごくハードルが高そうですが、
つまり、
・きょういく（今日、行く）ところがある。
・きょうよう（今日、用）がある。
というのが、よろしいようです。

仕事で覚えたEXCEL+VBA〜金額がプラスなら青字に、マイナスなら赤字に設定する

　EXCELで「プラス金額なら青字で、マイナス金額なら赤字で」わかりやく、色を付けたいことがある。
　そこで、EXCELで「選択」しているセル（複数かつ、点在していてもOK）について、
＋５百万であれば、青字で＋５百万円、−５百万円ならば、赤字で−５百万円と表示するVBAマクロは、以下のような感じ。

excergirl

Sub minusakaandplusaoHK()
Dim strPat As String
Dim strTest As String
'  -digits red
For Each r In Selection
  strTest =r.Value
  Set RE =CreateObject("VBScript:RegExp")
  Set Matches = RE.Execute (strTest)
  strPat = "(¥-|−)([0-9]|[０−９])+(.|．)? ([0-9]|[０−９]) *百万円"   --- pattern for search
  With RE
    .Pattern = StrPat
    .IgnoreCase =False
    .Global = True
  End With
  Set Matches = RE.Exceutes(strTest)
  If Matches.Count <> 0 then
    For Each Match In Matches
      r.Characters(Start:=Match.FirstIndex + 1,Length:=Match.Length).Font.ColorINdex = 3
  Next
  Endif
 Next r
'+digits blue
For Each r In Selection
  strTest =r.Value
  Set RE =CreateObject("VBScript:RegExp")
  Set Matches = RE.Execute (strTest)
  strPat = "(¥+|＋)[0-9]|[０−９])+(.|．)? ([0-9]|[０−９]) *百万円"   --- pattern for search
  With RE
    .Pattern = StrPat
    .IgnoreCase =False
    .Global = True
  End With
  Set Matches = RE.Exceutes(strTest)
  If Matches.Count <> 0 then
    For Each Match In Matches
      r.Characters(Start:=Match.FirstIndex + 1,Length:=Match.Length).Font.ColorINdex = 5
  Next
  Endif
 Next r
End Sub

ポイントとしては、「選択」しているセルを取り出すFor Each r In Selectionと「＋ｎ百万円」を正規表現RegExpで見つけているところ。

ちょっと一息のコーナー。
　トマス ・Ｈ・クックの「鹿の死んだ夜」（染田屋茂　訳、文春文庫）は、月曜日に「自宅の窓ごしに眺めると、リアダンには街が、でたらめな音と方向性のない動きの巨大なパッチワークに見えた。」という描写で静かに始まる。
　もう、随分前にこの本と出会い、思い浮かべるたびにその哀しい余韻が印象に残っている。原書のタイトルは、ブラッド・イノセント（blood innocent）。何年も絶版になった原書を探していたところ、電子書籍の時代となり、kindle本を購入することができた。
　原書では、MONDAY Watching it from his window, Reardon saw the city only as an immense patchwork of random sound and direction-less movement. で始まる。
　immenseは、巨大ななんだー。kindle本は単語の意味がでるので、便利ですね。
　タイトルが「ブラッド・イノセント」ではなく、「鹿の死んだ夜」になっていることのも「いいね」ですが、原書を読み返すたびに、染田屋訳の凄さ、絶妙さを感じています。トマス・クックもこれが処女作というのも凄い。

日々の仕事スタートでは、まず、技（道具）磨く～イチローのように・・・

  日々の仕事は、本当に地味で、辛気臭い作業が多い。だからの、少しでも楽するために様々な道具（EXCEL+VBA,ACCESS,IE+VBA,rubyやperlなど）を駆使して可能な限り、効率化したい。

ストレスなく、毎日、楽しく仕事できる（と思える）ようにするため、仕事のスタートでは「道具を磨く」かのように「スキルを磨く」。「ルーティン」として体に覚えさせていく。スポーツ選手が準備運動でいつもの定型メニューをこなしながら、体をつくるように、効率化のネタを探し、スキルを身につける。「昨日よりも、今日。今日よりも明日の進化」を目指して。
　イチローが試合前にまずは、決まったメニューで体を作るように・・・。昼は炊きたてご飯にカレーというように・・・・。

仕事で覚えたEXCELVBAの初めかた～まずは、マクロの記録で始めたよ

 いきなり、EXCEL　VBAをコーディングするのは、大変ですね。
ワタシは、まず、EXCELの「ツール」タブにあるマクロの記録で、EXCELの操作をVBAへ記録し、それを編集するという形で、始めました。
 最初は、モノまねから入ることをお勧めします。難しく考えずにやってみることが大切ですね！？

「ツール」タブのマクロの記録の画面は、こんな感じです。

 例えば、家計簿の費目ごとの合計を出すピボットテーブルを更新するVBAをマクロの記録で作成します。









そして、ピボットテーブルを更新する操作を「マクロ」として記録します。


記録したVBAコードは、ピボットテーブルを作り、集計するというコードで、こんな感じ。

 Sub Macro1()
' Macro1 Macro
' マクロ記録日 : 2010/3/15  ユーザー名 : Jaga Taro
'
     Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").RefreshTable
 End Sub

これに対して、コードを多少手直しする。
手直しのポイントは、以下の３つ。
・シートの選択
・最終行の算出
・ピボットテーブルの範囲（Sourcedata）の設定


こんな感じで、行が増えても、ピボットテーブルを更新できるように変更します。マクロ名もわかり易い名前、例えば、Updatae_Pivot_area（）にしておきます。

Sub Update_Pivot_area()
'
' Macro1 Macro
' マクロ記録日 : 2010/3/15  ユーザー名 : Jaga Taro
'
    Sheets("家計簿").Select

    最終行 = Range("A3").End(xlDown).Row

    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").SourceData = "家計簿!R2C1:R" & 最終行 & "C7"

    Worksheets("ピボット").PivotTables("ﾋﾟﾎﾞｯﾄﾃｰﾌﾞﾙ").RefreshTable

End Sub


　このコードを、ピボットテーブル更新という「ボタン（長方形）」に割り当て、このボタンをクリックすると、ピボットテーブルが更新されるようになります。

ここでちょっと一息のコーナー。

　静岡の登呂遺跡の隣に芹沢けい介美術館があります。先日、ミニ仮面作り体験（教えて頂いたお姉さん、ありがとうございました）につられて、出かけました。 芹沢けい介は、日本の染色の草分け、独自の文様感覚が光っており、ググーッと引き込まれました。芹沢先生は。世界の工芸品コレクター でもあり、石ノ森章太郎の「がんばれ！ロボコン」みたいな木彫りの人形、アフリカの仮面（マスク）、これまた、丁寧で、しっかりとした手作りの暖かさがある、魅力的な世界に浸ることができました。
　ついでに美術館の隣にある、登呂遺跡で弥生時代にタイムトリップすることもできました。

 There's Serizawa Keisuke museum next to TORO park in Shizuoka,Japan. Serizawa Keisuke had been a artist in dyeing with pattern and also collected all sorts of artifacts. His abilities to design and colletions of handwork were so splendid that I felt like being in another world. It was pretty amazing.
I have experienced making a mini-mask thanks to a staff of the museum. 
 Go to there if you visited to Shizuoka city.

それじゃ。Bye Bye

EXCEL＋VBA＋IE＋Webで仕事を効率化

　日々の仕事で、会社の業務システムへログインし、仕事に必要なデータを取ってくることが 度々あります。
 I have to login onto a company system and then get data many times everyday.  
　
　まずは、ブラウザでログイン画面を出し、ユーザIDを入れて、パスワード。そして、メニュー画面が出てくるのを待つ。

　メニュー画面が出たら、必要な画面を求めて、幾つかのボタンをクリック、数画面を経て、目指したページへやっと辿り着く。
　それから、特定のキー（例えば、オーダ番号）となる情報を入れて、必要なデータ（例えば、オーダ番号に対する内容ー商品名、注文個数など）をようやく得ることができる。
　オーダ番号が幾つかあれば、その分、番号を入れて、必要な情報を得る。そういうことで、日々の仕事をこなしていく。

　というような日々の操作を、イチイチ、手でやるでもイイじゃないかーーーッと思ってやっていたのですが、EXCEL＋VBA＋IE（= マイクロソフトのInternet Explorer、日本の会社は何故かIE好きだから、イヤ、マイクロソフト好きか・・・）＋Webで、自動操作できるようにしてみた。

fatman's coming

EXCELのVBAコードにより、IEで会社のWebシステムへ自動でログインし、あらかじめ指定しておいた通りにページ移動し、あらかじめ指定したオーダ番号一覧を舐めて、それぞれに対応したオーダ情報を別シートにコピーして、これまた、自動的に終了する。
　EXCELVBA Codes can make some irritating manual operations pretty easy.

　すると、 あることに気が付いた。
  I noticed one thing.

　物凄く、当たり前なのですが、「あっという間にできている。実際に手で動かしているときよりも少し早い、いや、だいぶ、早い。画面のコピペも手でやるよりも早いし、なにより間違った画面をコピーすることがなく、正確にできちゃうよー」ということに。ホントですよー。ノーミスで。
  Pretty fast. Really fast!

　ここで一息のコーナーです。

 ニュージーランド北島のギズボン（Gisborn)にあるワールドワインセンター（World Wine Centre)は、ポバティベイ（Poverty Bay、貧乏な湾）の小さな港にあります。
　「フライト(Flight）」というシャレた名前で、５銘柄ワインのテイスティングを２コースから楽しむことができます。チーズとパンのプレートもあるので、それをつまみながら、港を眺めつつ、ワインを優雅に楽しむことができます。イケメンのお兄さんが案内してくれますよ。上のリンクで、動画に登場されております。
　ちなみに、この貧乏な湾、ポバティベイの由来ですが、かの探検家キャプテン・クックが旅の途中で食料調達に立ち寄ったものの、何もなかったので、貧乏な湾と名づけたようです。失礼な話ですね。

　Let's take a break:

  World wine centre is located in a small port at Poverty Bay, Gisborn in North New zealand. There  are two tastings called "flight" in which you can taste five wines. There's also platter of cheese and bread. Enjoy wine and the platter and talk to a nice guy! 

  The name of Poverty Bay comes from a tale that Captain Cook couldn't get food there. How rude isn't　it!