生協の注文履歴をエクセルで利用額を把握できるようにしてみた

おいらはほぼ、生協に依存しているw

依存している生協は『生協の宅配パルシステム(インターネット注文はこちら) | 生協(コープ/COOP)のインターネット注文サービス、商品のご案内など』ですね。

加入してもう何年になるのか覚えていないが、加入したばかりの当時はOCR(マークシートのことですね)注文書を配達日に通い箱に入れて引き取ってもらっていたものです。
たまに忘れてたりもしましたがね

昨今は、インターネットの利便性に力を入れているようで、もう何年も前からインターネットで注文が出来るようになっています。
ああ、つい最近はケータイ電話からも出来るようになったらしいのですが、それは使ってない(^_^;

いつも、毎月の支払額を見ながら注文しているが、元来横着者のおいらが配達明細なんぞ整理しているわけがないw
でも、来月の支払いがいくらになるのかをにらみながら注文していかないと餓死する(大げさ)か破産するか(ちょっと誇張)のに二者択一を迫られてしまいます。
なんか、少しでも楽な方法はないのか…?
と、考えて、楽なのかどうかは相当疑問が残るがw
エクセルで管理してやろうじゃないかっ!
と、なぜか考えてしまい、ちょっとした仕組みを構築してみたのでした。

用意するもの。

  • Firefox→知らない人はまさか居ないと思いますが、ホームページを見るためのブラウザです。
  • Table2Clipboard→Firefoxのアドオン。web(ホーム)ページ上のテーブルをクリップボードにコピーしてしまいます。
    エクセルに貼り付けるとテーブルの構造そのままで再現してくれます。
  • Microsoft EXCEL→天下に名高い表計算ソフトですね。おいらは表計算で使ってませんが…何か?
①インターネット注文書②ご注文履歴③履歴確認ページ

まず、Firefoxで、パルシステムのインターネット注文にログイン(①)します。

履歴のページ『ご注文履歴』(②)をクリックして履歴ページ(③)を表示します。

④テーブル要素の上で右クリック

エクセルに取り込む注文回の履歴を表示し、テーブルの範囲内にカーソルを置いて(④)右クリックします。コンテキストメニューに『Table2Clipboard』というメニューが表示されています(Table2ClipboardというFirefoxのアドインがインストールされていて、有効になっている場合に表示されます)。カーソルを置くとさらに4つ選択肢が表示されますので、『表全体』を選択します。

⑤ワークシートに貼り付ける⑥VBAエディタでモジュールを追加⑦Module1を選択

次に、エクセルを立ち上げます。初めての場合はデフォルトの『Sheet2』を選んで『A1』セルを選んでペーストしてください。履歴ページのイメージそのまま…とは行きませんが、かなり近い状態で貼り付けされたと思います(⑤)。きちんと貼り付けられたのを確認してワークシートの名前を変更しておいてください。おすすめは『11月3回』などの注文回数です。このワークシートの名前は後ほど利用しますので、変更しておくことをおすすめします。
で、なぜ、Sheet1を使わなかったのか?といいますと、Sheet1には集計をするためのシートとして残してあるのです。

では、次はちょっとした仕掛けを施します。
キーボードの『Alt』キーを押しながら『F11』キーを押して、『Microsoft Visual Basic for Application』を立ち上げます。
そして、メニューから『挿入(I)』 →『標準モジュール(M)』をクリックしていきます(⑥)。左側の『プロジェクト』に『標準モジュール』という項目が表示され、その下に『Module1』というのが表示され、そのモジュールが選択されている状態になっていると思います(⑦)。

次に、以下のマクロコードをそこにペーストします。

Option Explicit

Function pickup(wsn)
    Dim zx, zy, zz, cnt, tmps
    For cnt = 1 To Worksheets.Count
        If Worksheets(cnt).Name = wsn Then
            zx = fcols(wsn.Text, "商品名")
            zy = frows(wsn.Text, "合計金額(増資分含む)", zx)
            zz = fcols(wsn.Text, "金額")
            tmps = Worksheets(cnt).Cells(zy, zz).Value
            pickup = CCur(Left(tmps, Len(tmps) - 1))
            Exit Function
        End If
    Next
End Function
Function fcols(wsn, keys, Optional rows)
    'wsn=ワークシート名
    'keys=検索するキーワード
    'rows=検索する行(省略可能、省略時は1行目)
    Dim zx, cnt
    If IsMissing(rows) = True Then
        rows = 1
    End If
    With Worksheets(wsn)
        For cnt = 1 To .Cells(rows, 1).End(xlToRight).Column
            If .Cells(rows, cnt).Value = keys Then
                fcols = cnt
                Exit Function
            End If
        Next
        fcols = "見つかりませんでした"
    End With
End Function
Function frows(wsn, keys, Optional cols)
    'wsn=ワークシート名
    'keys=検索するキーワード
    'cols=検索する列(省略可能、省略時は1列目)
    Dim zy, cnt
    If IsMissing(cols) = True Then
        cols = 1
    End If
    With Worksheets(wsn)
        For cnt = 1 To .Cells(1048576, cols).End(xlUp).Row
            If .Cells(cnt, cols).Value = keys Then
                frows = cnt
                Exit Function
            End If
        Next
        frows = "見つかりませんでした"
    End With
End Function

とりあえず、『Microsoft Visual Basic for Application』は閉じてしまっても大丈夫です。

では、いよいよ集計のシートです。
まあ、このシートは適当に見やすいように作っていただいて全然かまわないです。

⑧ワークシートに関数を

このシートで作る仕掛けというのは、先ほどの履歴のテーブルを貼り付けたシートの『名前』をセルに入力して、その右隣のセルに『=pickup(A1)』という、呪文を記述します(⑧)。
エクセルで関数を使ったことのある人なら説明は要らないとは思いますが、これは、このセルに関数を使った計算結果を表示するように定義しているということです。
先ほど、『Microsoft Visual Basic for Application』で作った『標準モジュール』の正体は、実は『ユーザー定義関数』というものです。
『=』は関数を呼び出すための合図で、その後の()の前までが関数の名前になります。()の中は何かというと『関数に渡す情報』ということになります。つまり、この場合は『pickup』というユーザー定義関数に『A1』という情報を元に計算した結果をセルに表示しなさい…という動作をセルに定義していることになります。
『A1』というのはもちろん、『A1』セルのことです。
例示したケースの場合は『A1』セルの内容『11月1回』を『pickup関数』に渡してその結果を『B1』セルに表示する…となります。
最初に、シートの名前を記入したセルの隣…と言いましたが、実際には隣である必要はありません。ですので、見やすいように自由に適当に作ってかまわないわけです。
A列には注文回数を列記して、その隣にはすべてpickup関数を呼び出す記述がしてあります。
最下行の『B6』セルは『=SUM(B1:B4)』と記述してあります。これはよく使うエクセルの関数ですからご説明の必要はないですね(笑)

せっかくですから、先ほど作ったユーザー定義関数について簡単に説明しておきましょう。
1行目の『Option Explicit』はマクロの中で使う『変数』というものの『宣言を強要する』という命令です。
エクセルのマクロで採用されている『Visual Basic for Application』というものはMicrosoftが開発しているプログラミング言語『Visual Basic』をエクセルにあわせて調整したものです。
で、その『Visual Basic』は変数というものは『宣言』をしなくてもマクロの中で最初に出てきたときに『自動的に』作ってしまいます。
いちいち宣言をしなくてもいいので楽ちんなのですが、これには落とし穴があって、スペルミス(文字の記述ミス)があっても別なものとして変数を勝手に作ってしまうと言う弊害があります。簡単に言えば『abcd』と変数のつもりで『abcc』と書いてしまったとしたら、『abcd』という変数と『abcc』という変数が作られてしまうと言うことで、それぞれ『別な変数』として存在することになってしまうわけです。
マクロを書いている本人は『同じ変数』のつもりで記述していっても、このたった1カ所の間違いに気づかずに書き進めてしまいます。
なので、この記述ミスをなくすために『変数の宣言を強要』するわけです。
これを記述しておけば変数は『Dim abcd』のように、宣言を行わないとエラーを発生させるようになりますので、記述ミスが減らせるわけです。
この動作はオプションを設定することで、自動的に付けることが出来るようにもなります。面倒ですが、これからマクロに挑戦していこうと思った人は一番最初に設定してください。
設定の場所は『Microsoft Visual Basic for Application』エディターのメニュー『ツール(T)』→『オプション(O)』の中の『編集』タブの中にある『変数の宣言を強要する(R)』項目です。

そして、関数は行の最初の『Function』という言葉で始まり、『関数の名前()』という記述をしてある行から、最初に見つかる『End Function』と書かれている行までが1つの関数として定義されます。
今回のマクロでは3つ、関数ブロックが存在します。
最初のブロックが今回のメインになる部分です。
セルに記述した『pickup』というのは、この関数の名前のことです。
最初の関数ブロックの中で、2種類、2つめ、3つめの関数を呼び出しているところがあります。
2つめと3つめの関数は似たような動作をする関数です。
2つめの関数はワークシートの1行目から、キーワードを探して、見つかった列番号を返す関数です。ただし、返すのは『A』とか『B』ではなく、A列からの順番を数字で返します。
なぜ『A』列などではないかというと、実は、マクロの世界では数字の方が扱いが非常に簡単だからです。この辺はマクロをいじり倒すようになると判ります(^_^;

3つめの関数はワークシートの1列目から、キーワードを探して、見つかった行番号を返す関数です。
どちらの関数も()の中に実行するための情報を与えてあげなければなりません。
2つめの関数の場合は『対象のワークシート名,検索するキーワード』が必須情報です。その後に『検索する行』を指定することが出来ます。
3つの関数もにていますが、最後の『検索する行』が『検索する列』になります。
今回のマクロでは3つめの関数にエクセルのバージョンに依存している箇所があります。
43行目の

        For cnt = 1 To .Cells(1048576, cols).End(xlUp).Row

の『1048576』という数字の部分です。
実はエクセルは『EXCEL 2007』で仕様変更があり、扱える最大の行数と列数が拡大されています。今回のマクロは『EXCEL 2007』以降のバージョン用です(とは言っても『EXCEL2010』しか持っていないので2007でも問題なく動くかどうかは確認していません(^_^;)
それ以前のバージョンで使う場合はここの数字を『65936』(注意:うろ覚えです。違っている可能性もありますので、白紙のワークシートで『Ctrl』キーを押しながら『↓』を押してカーソルが移動した行数が扱える最大の行数ですので、それを記述してください。↓の代わりに『End』キーでも最大の行数を確認することは出来ます。)に置き換えてください。
バージョンを吸収する仕掛けを入れればいいのでしょうけど、めんどくさいのと所詮確認をとることが出来ないのでいたずらに入れない方が賢明だと思ったからです。
まあ、なので、2つめ、3つめの関数は他でも使える関数なので、気に入ったら使ってあげてください(ヲイ)。

で、最初の関数の説明に戻りますがw
動作的にはブックに存在するすべてのワークシート名と、wsnで渡されたワークシート名とを比較して、同じものが見つかったら、そのワークシートを対象にして、1行目にある『商品名』と記入されたセル、『金額』と記入されたセルの列数をそれぞれ探しておきます。
次に、その情報を元に『合計金額(増資分含む)』と記述さているセルを『商品名』と記入された列の中から探して、その行数を調べます。
最後に、『金額』と記入されたセルの『合計金額(増資分含む)』と記入された行にあるセルの記述を返すのですが、そのままだと『9,999円』のような形で返してしまうので、数字だけを取り出すように処理を加えてから関数の結果として返して終了します。
まあ、多少無駄を省くための処理が入っていますが、マクロは基本的に上から下に向かって1行ずつ順番に実行されていきますので、『End Function』まできたところで終了します。

そうそう。
おいらが今回経験したところで、エクセル2010はマクロに対するセキュリティが過剰なくらい強化されています。
昔のバージョンならマクロを含んだブックを開くときには『警告』を表示した上で『マクロを実行するか』という選択肢を投げてきてたのですが、2010ではそれすらないorz
要するに、マクロは『一切使わない』か『あんたの責任で危険を冒せ』という極論しかあり得ない仕組みに変わっていました。
元はといえば、安易な機能を提供したツケが今廻ってきているだけだろ?と思うのだが、既に回避不能な状況にまで追いやられてユーザーに投げたMSのどうしようもなさが垣間見れて、あきれています。

そんなわけで、皆さんも注意してくださいね。
あ、ちなみに、今回のマクロ、悪さをするような仕組みは入っていません。
というか、おいら自身いやですし、そんな仕組みを作るような脳みそは持っていないし、何より面倒です
でも、踏み台にする可能性は否定できないので、このマクロを使おうと思っている人はくれぐれも『自己責任』で使ってください。
ではでは。

Comments are closed.