駅メモのフレガチャ統計ツールをExcelで作る
ここでフレンドガチャの出現率統計をなんとなく取ってみてるわけですが、
それに使っている手作りツール。
見た目こんなの。
「+」のところをクリックすると、対応した年月・アイテムのところが1増えます。
(もちろんそれに応じて割合と合計も計算される)
「ExcelVBAで作るちょっとしたプログラム」の作例としては使いやすいかな、と思ったので置いておく。
「Excelのシート内の構成ざっくり組み立て」「数字を+1してくれるプロシージャ」と「その2つを繋ぐ部分の作り込み」の3つに分けて考えます。
【まず、VBAの前にExcel側をざっくり作る】
①現在の年月
正確には、「+」を押した時に集計対象にしたい年月。
現在年月の1日(※)を「2016/6/1」のような形式で入力します。
(↑月が変わった時に手動で書き変える前提ですが、お好みで自動で切り替わるようにするのも良い)
で、お好みで書式を「yyyy年mm月分」みたいにすればOK。
※…
1日としているのは、VBAでの「+を押したときにどの行の数字を増やすか」の判定時、次項の年月と比較しやすいようにするためです。
VBA側で年月だけを見るように作り込むなら何日でもOK。
②集計結果用の年月
これも①と同じく、各年月の1日を「2016/5/1」「2016/6/1」…のように入力して、
書式をお好みのものに変えればOK。
③各月の合計、④アイテムごとの合計、⑤総合計
普通にワークシート関数のSUMを用いて設定。
⑥割合
「(総合計÷そのアイテムの合計)×100」と設定。
小数点以下の桁数とか単位を付ける付けないについては、書式設定でお好みに。
その他
どうでもいいので後でもいい。
- デザインを調整したり
- 不要な枠線を消したり(上部メニューの「表示」>「枠線」のチェックを外す)
- ウィンドウ枠の固定をしたり(上部メニューの「表示」>「ウィンドウ枠の固定」)
【プロシージャの作成】
メイン処理。
詳しくは説明しませんが、簡単に書くと
「今選択しているセルの列」と「集計対象年月の行」の交点のセルの数字を「+1」するプログラムです。
標準モジュール「friend_gacha」を作成し、その中に書きます。
Option Explicit Const SHEET_NAME As String = "フレガチャ統計" '※実際のシート名 Const RANGE_NOW_YEAR_MONTH As String = "A2" '前述「①現在の年月」のセル Const RANGE_START_YEAR_MONTH As String = "A6" '上記における「②集計結果用の年月」の最初の年月のセル Public Sub addGachaCount() Dim wsSheet As Worksheet Dim dtNow As Date Dim rngTmpYearMonth As Range Dim lColumn As Long Dim lRow As Long Dim i As Long 'シート定義 Set wsSheet = ThisWorkbook.Sheets(SHEET_NAME) '集計対象年月 dtNow = wsSheet.Range(RANGE_NOW_YEAR_MONTH).Value 'インクリメント対象列 lColumn = ActiveCell.Column 'インクリメント対象行を求める i = 0 Do While True ' 対象行チェック用セル(年月列を用いる) Set rngTmpYearMonth = wsSheet.Range(RANGE_START_YEAR_MONTH).Offset(i, 0) ' 対象がなければ終了 If rngTmpYearMonth.Value = "" Then Exit Sub End If ' 対象行があればループ終了 If rngTmpYearMonth.Value = dtNow Then lRow = rngTmpYearMonth.Row Exit Do End If ' 次の行へ i = i + 1 Loop ' インクリメント wsSheet.Cells(lRow, lColumn).Value = wsSheet.Cells(lRow, lColumn).Value + 1 End Sub
【+が押された時にプロシージャを呼び出す処理】
では、あとはプロシージャを呼び出す方法を考えます。
いくつか方法がありそうですが、今回は
「+の文字にハイパーリンクを張り、クリックしたときの処理としてプロシージャを呼び出す」
という方法にしてみました。
参考:http://www.excel-wing.com/study/jitumu/941
とりあえずハイパーリンクを張る
まず、さっき作ったワークシートの「+」にハイパーリンクを張ります。
セルをコピペしてもセル参照先が変わらないので、面倒ですが1つずつ設定してやります。
これで、「クリックするとそのセル自身に飛ぶ(実質は何も動かない)ハイパーリンク」ができます。
「ハイパーリンクがクリックされたらプロシージャを呼び出す」処理を用意する
- VBAのエディタを開く
- 以下のコードを記述する
- もちろん、元々何か入ってればいい感じにマージしてください
- どんなハイパーリンクでもクリックされたら実行されてしまうので、必要に応じた処理を加えてください
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) Call friend_gacha.addGachaCount End Sub
以上です。
細かいエラーチェックとかガバガバですが、個人的に使うプログラムならこういう感じでいいかなって。