Excelによる授業進行表(条件付き書式とVBA）

毎日の授業においてクラス毎の進行を管理するのはかなり大変です。

・次の回はどのクラスが最初になるのか。
・各クラスの残り時間は何時間なのか。
・試験の前にはどのくらいの時間があるのか。

通常ならカレンダーを使って管理していると思いますが、日々刻々と動いていくものであり、さらにある時点での未来の状態やどのように変化していくのかを直感的に把握するのは難しいですね。そこで、Excelでの進行表を作ってみました。

本校ではＡ組〜Ｈ組まで8クラスあります。年間行事予定表とにらめっこしながら、いつが授業なのかを一つずつ入力します。これは手作業です。

画像のL2セルは設定日となっています。このセルに適当な日付（例えば来週の今日など）を入れます。

各日付の入ったセルに条件付き書式を設定します。というか、先に設定をして（コピーして）おいたほうがいいです。


D3セルでの条件は、

条件（１）=D3=$L$2
書式（背景パターン）：うすい水色

条件（２）=D3＜$L$2
書式（背景パターン）：灰色50%

条件（３）=D3=""
書式：設定無し

です（上の条件２の不等号は全角になっています）。

これによって、設定日以前は暗く（灰色５０％）、設定日当日はうすい水色、設定日後はデフォルトの背景になります。これだと日付を入力していないと灰色になってしまうのがうっとうしいですが、まぁそのままにしています。

続いてマクロを設定します。

まずその前に準備として、M2セルに「=today()」と入れて常に今日の日付が入るようにしておきます。

「ツール」−「マクロ」−「Visual Basic Editor」を起動して、VBE上で「挿入」−「標準モジュール」としてソースを書けるようにします。
ここに、５つのマクロを入力します（セルの配置が全く同じなら以下をコピー＆ペーストすればいいと思います）。ここで Cells(2,12) というのは L2セル、つまり「設定日」セルです。

Sub 今日ボタンで今日にする()

　　今日 = Cells(2, 13)
　　Cells(2, 12) = 今日

End Sub

Sub 次の日にする()

　　設定日 = Cells(2, 12)
　　Cells(2, 12) = 設定日 + 1

End Sub

Sub 前の日にする()

　　設定日 = Cells(2, 12)
　　Cells(2, 12) = 設定日 - 1

End Sub

Sub 翌週にする()

　　設定日 = Cells(2, 12)
　　Cells(2, 12) = 設定日 + 7

End Sub

Sub 前週にする()

　　設定日 = Cells(2, 12)
　　Cells(2, 12) = 設定日 - 7

End Sub

「今日ボタンで今日にする」マクロ、
「次の日にする」マクロ、
「前の日にする」マクロ、
「翌週にする」マクロ、
「前週にする」マクロ
の5つですね。

一応2行にしてありますが、一行でも済みますね。

解説するまでも無いと思いますが、
「今日ボタンで今日にする」マクロは、=today() が入っているセルを読み込んで設定日セルに書き込む。
「前の日にする」マクロは、設定日を読み込んで「−１」して返す。
「次の日にする」マクロは、〃　　「＋１」して返す。
…
…

というだけです。それぞれの Sub の中にカーソルを置いて、VBE上部の実行ボタン又はメニューの「実行」−「Sub/ユーザーフォームの実行」で実行するとその通りになることを確認します。


後は、Excelシート内の適当な位置にボタンを配置してマクロを登録します。
Excel上で「表示」−「ツールバー」−「フォーム」でフォームのツールバーを表示します。

右側の上から二番目、「ボタン」をクリックし、シートの適当なところでドラッグしてボタンを作ります。離した瞬間に「マクロの登録」ダイアログボックスが表示されます。


ここで「今日ボタンで今日にする」などが並んでいるので適切なマクロをクリックして選択し、「OK」をクリックすると登録されます。ボタンが選択されている状態なら軽くワンクリックすると「ボタン 1」などとなっている表示を変更できます。選択されていない状態だと、クリックした瞬間にマクロが実行されてしまうので、右クリックで「テキストの編集」を選んで名前を変えます。

これを繰り返してマクロボタンを５個作り登録します。画面上では「今日」「-1d」「+1d」「-1w」「+1w」の５つです。

「今日」を押せば今日が水色、昨日までは灰色、明日以降が白の背景になりますでしょうか。「+1d」をクリックするとどのように授業が進んでいくのかが次々と色が変わりますでしょうか。

以上で、動的な授業進行表ができました。全体を見渡しながら、進んでいく様子が分かります。

条件付き書式を工夫すれば、横一列で一番早い日を黄色にするなどができると思います。試してみてください。

掲示板のツリー表示と再帰プログラミング

関東大会での発表の直前、相方より、「掲示板の生データからツリー表示を作れないか」との相談を受けました。

「ちょっとムリ」とその場は断りました。大会発表までには自分の能力では余りに時間が足りないと思ったからです。

発表も終わり少し余裕が出てきたので、取り組んでみました。ツリー構造には再帰プログラミングだなぁということはうっすらと考えていたのですが、これがなかなか難しいものでした。


続きを読む

Excelでセルを変更した時に色を表示させる

例えば、B2セルに「=A2+10」と入れて横にコピーするとA2に10と入れるだけで

10,20,30,40,50,……

と規則的に並びます。

途中一部だけ5増やして、続きは10ずつのままにしたくて、30のところに「25」と数値を直接入れると

10,20,25,35,45,……

となります。このとき、変更したことを分かり易くしたいと思い立ちました。
ググってみると

内容を変更したセルに色をつけたい（OKWave）

等という記事があり、VBA が紹介されています。標準モジュールではなく、Sheet1のコードとして、

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula Then
Target.Interior.ColorIndex = 28
End If
End Sub

などとやっておくと、式が別の式に変わったことをイベントとして検出して色を付けるとのこと。しかしこれでは上記のように式ではなく数値にした場合はダメですし、いろいろメンドウな感じです。

こんな時は「条件付き書式」ですね。

上記のパターンではB2セルに、条件付き書式を設定。
「数式が」
「=B2＜＞A2+10」（＜や＞は半角に直してください）
「書式→背景色を設定」
のようにしておきます。これを式ごと横にコピーします。


つまり本来表示されている数値ではなかった場合に背景色を塗る、ということです。もっとも本来表示されるはずの数値と同じものを値として入力したり、別な式だけど同じ結果になる場合は色が付かないので厳密には「変更したとき」とは違いますが、望みのものにはなりました。

ExcelVBA Rangeプロパティの不思議な挙動

例えば次のようにセルに数値が入力されていて、セル範囲が選択されているとします。



ある決まった範囲を指定する方法としてさまざまなパターンを、以下のようなマクロで試してみます。

Sub setのテスト()

MsgBox Worksheets(1).Range("A2").Value

Set ws = Worksheets("Sheet1").Range("A2")

MsgBox ws.Value

MsgBox Selection.Range("A2").Value

MsgBox Range("A2").Value


End Sub

すると挙動はどうなるかというと、順番に、4,4,8,4 となります。問題はこの「８」です。



Selection.Range("A2")は選択された範囲に於いて、相対的に A2 すなわち左上を原点（１，１）として横に１（つまりそのまま）、縦に２（つまり一つ下）のセルを指し示し、B3 を意味することになります。この辺を間違えるととんでもない結果になるので要注意です。

（参考）
Range プロパティ (Application オブジェクト、Range オブジェクト、Worksheet オブジェクト)（個人サイト）

Excel で Visual Basic プロシージャを使用して、セルおよびセル範囲を選択する方法(Microsoft)

ExcelVBA でワークシートの値を配列に取り込む

Ruby や Perl のように、VBA でも配列の要素数を Dim で宣言しなくてもばっさりと代入することはできないのかなぁと思っていました。今日、Excel VBA では、Variant型の変数にセル範囲を代入すれば、範囲の値がそのまま配列になってくれることを知りました。

たとえば「Sheet1」に次のような値が入っているとします。



そこで、以下のようなマクロを実行します。

Sub 配列のテスト()

Dim myRng As Range
Dim myData As Variant

Set myRng = Worksheets(1).Range("B2:D5")

myData = myRng

Worksheets(2).Range("A1:C4") = myData

MsgBox myData(4, 3)

End Sub

B2からD5までの四角い範囲をそのまま「myData」というVariant型の変数に代入します。それをそのまま「Sheet2」に貼り付けます。そして、要素(4,3)をメッセージボックスで表示させています。その結果は次の通りです。



わざと「Sheet2」ではずらしたのですが、ちゃんと貼り付いています。

また、メッセージボックスには「12」が表示されています。ここから配列は(y,x)の順番、添字は１から始まる値になっていることが分かります。

結構使えそうです。

web ファイルの全角文字

Web ページを作る上で、全角ファイルは厳禁ということになっている。apache 等が対応していないからでしょう。

日本語ドメインに関しては聞いたことがあるブラウザの中では唯一 IE6 が対応していませんでしたが、IE7 でその辺は解消されるでしょう。

さて、校内に webサーバを立てて、Web ページの実習をしていると、この辺が問題になる場合があります。自分のデスクトップ上で確かめてリンクも問題なく表示されていても、ftp で送信するとうまくいかない理由に、「絶対アドレスと相対アドレス」の問題とこの全角文字の問題があります。

t_fukuhara さんから、Excel VBA でサブフォルダまで検索して（つまり生徒の個人フォルダをすべて調べて）ファイル名に全角を使っているものを調べることができないか、という宿題を頂きました。

なんとか、それらしいものができました。続きを読む