2014年3月9日日曜日

実績報告(ココナラを通じて500円でサービスの提供 Excel VBA開発)

以前、「ココナラを通じて500円でサービスの提供(簿記解説動画・Excel VBA作成)」というタイトルでお知らせしましたが、簿記の解説とExcel VBAの開発を有料で承っています。簿記の解説については全く依頼がありません(^^; 素人に500円払ってまで「教えてもらおう」って人はいませんよねー(汗) 対して、Excel VBAの開発についてはいくつか依頼がありました。3つほど開発し、来週さらに1件開発依頼を受ける予定になっています。以下、詳細はぼかしますが、どんなものを作ったのか簡単にご説明します。

1. Webサイトから任意のコメントを抽出し、リスト化する機能
対象となるWebサイトのURLを上から順番に並べて、そこに書かれた「何番目」かのコメントを抽出するという機能を作成しました。コメントが無かった場合の動きとかに工夫を要しました。ExcelでWebの情報を得る際、Internet Explorer を起動するのが一般的なやり方です。(他のブラウザでも出来るっぽいですが、そっちはどうやるのか勉強してません^^;) そしてページのソース(HTML)を拾い出して、その中の規則性を確認します。HTMLの作りはサイトの独自性が出てくるので、なかなか一朝一夕に行きません。どうやって抽出すれば良いかを考えるところが、頭の使いどころ。

2.過去のリストと現在のリストを比較し、増加分と減少分をリスト化する機能
いわゆる「差分」を取る機能です。たとえばCSVのDiffを取るフリーツールなんか沢山ありそうですが、Excel上で簡単に管理したいのであればVBAが適していると思います。HTMLを読み込む場合と違い、Excel内部で完結している機能は割と簡単に作れてしまいます。これは作業時間2時間程度で済みました。

3. 買い物サイトから名前、価格(送料込)、重さ等の情報を抽出し、リスト化する機能
これはけっこう苦労しました。何が大変かと言うと、HTMLのバラエティの豊富さがです。あるページを見本に機能を作成しても、別のページでは思うようにいかない。なのである程度のパターンを想定しつつ、抽出機能の場合分けを行わなくてはなりません。細かい話ですが、文字列を数値化したりといった手間も多く発生しました。

こんな感じで色々と作っています。

人に何かを説明するのは難しい

第08回簿記勉強会では「ころがし計算法」をテーマにしました。ころがし計算法は簿記1級や全経上級でもあまり出題されないテーマで重要性は低いのですが、きちんと理解するのはなかなか難しいところです。なので、受験者によっては最初から切り捨ててしまっている人もいるんじゃないでしょうか。僕自身、ころがし計算法は他のテーマに比べても難しいと感じていました。

今回勉強会で取り扱うことにしたため、1週間ほどずっと「ころがし計算法」について考えていました。一つのテーマに集中して取り組むことで、ある程度の理解は得られたと感じています。教科書を読み込んで勉強するわけですが、今となると教科書の説明は下手くそなんじゃないかという気がします。初見で教科書の説明を理解出来る人は1%もいないんじゃないでしょうか。

じゃあ、自分なら教科書よりも分かりやすく説明出来るのか、と聞かれたら答えは「YES」です。ころがし計算法の視覚的な解き方を動画で説明しました。しかしそれを上手に出来ているかと聞かれたら答えは「NO」です。 論理の構築が難しく、まだまだ改善の余地があります……。勉強会の様子をYoutubeにあげておきました。TACの厳選問題集日商簿記1級第4版を利用していますので、これを参照しながら見ていただくと分かりやすいと思います。(逆に言うと、問題集無しで見てもよく分からないかも)

2014年3月1日土曜日

【備忘録】Excel VBA小技集

以下、自分の備忘録&外出先での確認用に集めたものです。気が向いたら更新します。

<<VBA実行時の更新画面表示 or 非表示>>
Application.ScreenUpdating = False '更新を表示しない
Application.ScreenUpdating = True '更新を表示する

<<アラートを無視する or しない>>
Application.DisplayAlerts = False 'アラートを無視する
Application.DisplayAlerts = True 'アラートを無視しない

<<最終行を取得する>>
Dim MaxRow As Long
MaxRow = Cells(Rows.Count, 1).End(xlUp).Row


<<最終列を取得する>>Dim MaxCol As Long
MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column

<<配列>>
Dim xxx() As String
ReDim xxx(number)

<<集約>>
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row '先頭行から最終行まで
    For j = Cells(Rows.Count, 1).End(xlUp).Row To i + 1 Step -1 '最終行から対象の行まで
        If (Cells(i, 1).Value = Cells(j, 1).Value) Then '同じ値かどうかを確認
                    ActiveSheet.Rows(j).Delete '下の方の行を削除
            End If
    Next j
Next i

<<整列>>
Worksheets(1).Range(Cells(2, 1), Cells(MaxRow, MaxColumn)).Sort Key1:=Worksheets(1).Cells(1, 1), order1:=xlAscending

<<HTMLの情報を取得する為の主なメソッド・プロパティ>>
'GetAttribute("alt")         代替テキストを取得する場合に使用
'GetAttribute("checked")       チェックボックスやラジオボタンの選択状況を取得
'GetAttribute("href")        リンク先のURL等を取得する場合
'GetAttribute("id")         要素を識別するために固有の ID名を取得する場合
'GetAttribute("name")        要素に付けられたname属性を取得する場合
'GetAttribute("selected")      オプションボタン等の選択状況を取得する場合
'GetAttribute("selectedIndex")    どのオプションボタン等が選択されているかを取得する場合
'GetAttribute("src")         画像ファイル等の保存先の URI を取得する場合
'GetAttribute("title")        要素内に記入されている title 属性を取得する場合
'GetAttribute("type")        要素に付けられたtype属性を取得する場合
'GetAttribute("value")        その要素に記入されている値を取得する場合
'GetElementById("ID名")       要素の ID 属性を検索キーとして使用して HtmlElement を取得する場合
'GetElementsByName("属性名")     対応する名前を指定して要素のコレクションを取得
'GetElementsByTagName("タグ名")   指定した HTML タグを持つ要素のコレクションを取得する場合
'InnerHtml              HTML マークアップを取得または設定する場合
'InnerText              テキストを取得または設定する場合
'OuterHtml              現在の要素の HTML コードを取得または設定する場合
'OuterText              現在の要素のテキストを取得または設定する場合

<<HTMLの情報を設定する為の主なメソッド・プロパティ>>
'InnerHtml              HTML マークアップを取得または設定する場合
'InnerText              テキストを取得または設定する場合
'InvokeMember("Click")        ボタン・リンク等をクリックする場合
'InvokeMember("focus")        フォーカスを設定したい場合(画面の見える位置までスクロール)
'InvokeMember("onChange")      Change イベントを発生させたい場合
'InvokeMember("submit")       送信ボタンをクリックしてFORM action を実行させたい場合
'InvokeScript("関数名")       HTML ページで定義されたアクティブ スクリプト関数を実行
'OuterHtml              現在の要素の HTML コードを取得または設定する場合
'OuterText              現在の要素のテキストを取得または設定する場合
'RaiseEvent("onChange")       Change イベントを発生させたい場合
'SetAttribute("Checked", "")     オプションボタン・チェックボックスの選択のチェックを外す場合
'SetAttribute("Checked", "true")   オプションボタン・チェックボックスを選択する場合
'SetAttribute("Selected", "True")  リストボックスのアイテムを選択する場合
'SetAttribute("selectedIndex", "2") リストボックス等の指定のアイテムを選択する場合
'SetAttribute("value", "文字列")   テキストボックス等に書き込む場合

<<HTMLのタグ読み込み>>
For Each objectA In objectIE.document.Body.all.tags("td") 'TDタグのみ読み込む
Next

For Each objectA In objectIE.document.Body.all '全てのタグを読み込む
Next

<<HTMLタグ名取得>>
objectA.tagname

<<IE開いて閉じる>>
Dim a As Object
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True
html = "http://minkabu.jp/stock/list/" & i
ie.Navigate html
waitNavigation ie 'ちゃんとIEが開くまで待つ
ie.Quit

<<IE待ち>>
Sub waitNavigation(ie As Object)
    Do While ie.Busy Or ie.ReadyState < 4
        DoEvents
    Loop
End Sub

参考サイト
VBレスキュー花ちゃん
ダメ株主 Z