kengo700のナレッジベース

誰かの役に立つと思う情報を発信するブログ

Excelで抽選ソフト(研究室の進捗報告の発表順をランダムに)

この記事では、Excelで作った抽選ソフトを公開します。これは過去に、研究室の進捗報告会の緊張感を保つために発表順をランダムにしたときに、必要になって作ったものです。

Lot_kengo700.xlsm(kengo700のGoogleドライブ)

素人が作ったものなので、ご利用は自己責任でお願いします。

外観と機能

f:id:kengo700:20160113200939g:plain

「抽選」ボタンを押すと、左の候補者のリストから一人がランダムに選ばれ、結果欄に表示されます。チェックボックスをクリックすることで、手動で抽選済みにする(候補から外す)ことができます。「リセット」ボタンを押すと抽選状態がリセットされます。

簡単な経緯

私が参加している研究プロジェクトでは、週に1回進捗報告会を行なっています。毎週ほぼ同じメンバーでの発表なのでマンネリにならないように、下記の2点をルール化してみました。

  • 事前に発表資料を1つのパソコンにまとめる。
  • 発表順をランダムにする。

ここで発表順をランダムに決めるために抽選ソフトを探しましたが、ちょうどいいものが見つからなかったので自作することにしました。そのときVBA (Visual Basic for Applications) に興味があったので、Excelで作ってみました。

抽選ソフトの説明

抽選ボタンのプログラム

抽選を行うボタンを押したときに実行されるプログラムは下記の通りです。参加者の人数と抽選済みの人数を数え、全員が抽選済みでなければ、乱数計算の関数で抽選を行います。

[抽選ボタンのプログラム(クリックで表示)]

Sub drawLots_Click()
    
    '変数定義
    Dim MaxCount      As Long  '参加者の人数
    Dim ElectedCount  As Long  '抽選済み人数
    Dim WinningNumber As Long  '当選番号
    Dim Counter       As Long  'ループ用カウンタ
    
    '参加者リストの先頭のセル
    Dim NameRow As Integer: NameRow = 4
    Dim NameCol As Integer: NameCol = 2
    '結果欄のセル
    Dim ResultRow As Integer: ResultRow = 7
    Dim ResultCol As Integer: ResultCol = 5

    '全チェックボックスを、チェックボックスがあるセルとリンク
    Dim Chk As CheckBox
    For Each Chk In ActiveSheet.CheckBoxes
        With Chk
            .LinkedCell = _
                .TopLeftCell.Offset(0, 0).Address
        End With
    Next Chk

    '参加者の人数をカウント
    MaxCount = 0
    Do While Cells(NameRow + MaxCount, NameCol) <> "" 'セルが空欄でなければ
        MaxCount = MaxCount + 1
    Loop

    '抽選済み人数をカウント
    Counter = 0
    ElectedCount = 0
    Do While Counter < MaxCount
        If Cells(NameRow + Counter, NameCol + 1) = True Then
            ElectedCount = ElectedCount + 1
        End If
        Counter = Counter + 1
    Loop

    '全員が抽選済みなら終了
    If ElectedCount = MaxCount Then
        Exit Sub
    End If

    '抽選作業
    Randomize '乱数のシード値を変更
    Do '出るまで無限ループ
        WinningNumber = Int((MaxCount - 1 - 0 + 1) * Rnd + 0) '0から(参加者人数-1)までの整数の乱数を計算
        If Cells(NameRow + WinningNumber, NameCol + 1) = False Then '選んだ人がまだ抽選済でない場合
            Cells(NameRow + WinningNumber, NameCol + 1) = True '抽選済フラグをTrueに
            Cells(ResultRow, ResultCol) = Cells(NameRow + WinningNumber, NameCol) '結果欄に当選者名を表示
            Exit Do 'ループを抜ける
        End If
    Loop

End Sub

リセットボタンのプログラム

リセットボタンのプログラムでは、抽選状態を保存しているセルをリセットし、結果を表示する欄を空欄にしています。

[リセットボタンのプログラム(クリックで表示)]

'リセットボタン
Sub reset_Click()

    '変数定義
    Dim MaxCount As Long  '参加者の人数
    Dim Counter  As Long  'ループ用カウンタ
    
    '参加者リストの先頭のセル
    Dim NameRow As Integer: NameRow = 4
    Dim NameCol As Integer: NameCol = 2
    '結果欄のセル
    Dim ResultRow As Integer: ResultRow = 7
    Dim ResultCol As Integer: ResultCol = 5

    '全チェックボックスを、チェックボックスがあるセルとリンク
    Dim Chk As CheckBox
    For Each Chk In ActiveSheet.CheckBoxes
        With Chk
            .LinkedCell = _
                .TopLeftCell.Offset(0, 0).Address
        End With
    Next Chk

    '参加者の人数をカウント
    MaxCount = 0
    Do While Cells(NameRow + MaxCount, NameCol) <> "" 'セルが空欄でなければ
        MaxCount = MaxCount + 1
    Loop

    '抽選済フラグをすべてFalseに
    Counter = 0
    Do While Counter < MaxCount
        Cells(NameRow + Counter, NameCol + 1) = False
        Counter = Counter + 1
    Loop

    '結果欄を空欄に
    Cells(ResultRow, ResultCol) = ""

End Sub

乱数の計算

抽選する部分では、乱数を計算する関数Rndを用いています。Rnd関数は0以上1未満の乱数を返します。これを用いて最小値から最大値の間の整数の乱数を得るには以下の式を用います。

Int((最大値 - 最小値 + 1)*Rnd + 最小値)

ただし、乱数を計算する前に下記関数を実行しておかないと、Excelファイルを開くごとに同じ乱数が出力されてしまいます。

Randomize

抽選状態を切り替えるチェックボックス

チェックボックスを利用して抽選状態をマウスクリックによって切り替えることができるようにしています。

チェックボックスの状態は、リンクされているセルに「TRUE」「FALSE」として出力されます。リンクの設定は右クリックメニューの「プロパティ」から行うこともできますが、この方法ではチェックボックスをコピペやオートフィル(下図)をしたあとも同じセルをリンクしてしまいます。

f:id:kengo700:20160113201030g:plain

そこで「抽選」ボタンと「リセット」ボタンのプログラムの中で、チェックボックスとセルをリンクさせています。

    '全チェックボックスを、チェックボックスがあるセルとリンク
    Dim Chk As CheckBox
    For Each Chk In ActiveSheet.CheckBoxes
        With Chk
            .LinkedCell = _
                .TopLeftCell.Offset(0, 0).Address
        End With
    Next Chk

これには、下記ページを参考にさせていただきました。

セルに「TRUE」「FALSE」が表示されないように、セルの書式設定に「;;;」を設定しています。

抽選状態の表示(セルの強調)

抽選済みの人の名前を灰色に変更することによって、抽選状態をわかりやすくしています。 これは、条件付き書式の設定で、抽選状態を保存しているセルがTrueとなったときに、名前を灰色にすることで実現しています。

f:id:kengo700:20160113202730j:plain

条件付き書式の設定は、「ホーム」タブの「条件つき書式」から行うことができます。

ExcelのVBA (Visual Basic for Applications) の説明

フォームコントロールの追加方法

ボタンなどの「フォームコントロール」をExcelのシートに挿入するためには「開発」タブを表示させる必要があります。

  1. 「ファイル」メニューから「オプション」を選択
  2. 「リボンのユーザー設定」を選択し、右の欄の「開発」にチェックを入れる
  3. 「OK」ボタンをクリック

フォームコントロールを挿入するには「開発」タブの「挿入」から任意のフォームコントロールを選択し、適当なセルをクリックします。

挿入したフォームコントロールは右クリックで選択状態になり、場所を移動したり表示される文字を変更することができます。

プログラムの追加方法

ExcelでVBAのプログラムを追加するには、下記の手順で行います。

  1. 「開発」タブの「Visual Basic」ボタンをクリックし、VBAのエディタを出現させる
  2. エディタの「挿入」タブの「標準モジュール」をクリックする
  3. 「Module1」というファイルが生成されるので、ここにコードを入力する

ボタンなどを押したときにプログラムが実行されるようにするには、右クリックメニューの「マクロの登録」から作成したプログラムを登録します。

基本的な文法

プログラムのコメントは「'」によって入力します。すなわち「'」以下の文字は無視されます。

'これはコメントです

変数の定義は以下のように行います.

Dim 変数名 As データ型

任意の位置のセルへのアクセスはCellsプロパティを用います.

Cells(行番号, 列番号)

forループは以下のように行います.

For カウンタ変数 = 初期値 To 繰り返し回数
 処理
Next カウンタ変数

Do Whileループは以下のように行います.

Do While 条件式
 処理
Loop

参考資料

おわりに

私がVBAを使ったのは、この抽選ソフトを作ったときが最初で最後でした。