新型コロナウイルス感染症対策の観点から一気に広がったオンライン学習。教育におけるICT化はものすごく速い勢いで進んでいる、と言えればいいのだが、正直なことを言えば、「文房具としてのICT」以前の問題として「教具としてのICT」や「業務ツールとしてのICT」も、その浸透が不十分とは言えない教育現場。比較的PCの操作が得意である私は、よく「先生、このエクセルなんですけど」という声を同僚にかけられることが多い。

そんななか、業務上「不便だなぁ」と感じるもののなかで、繰り返し処理や自動処理ができるものを見かけると、私はどうしても、前職時代に培った、ExcelベースのVBAツールの開発をしたくなる衝動にかられる。そして今日、年に１回しか発生しない業務ながら、やろうとするとすごく時間がかかる作業を行うための、汎用ツールを開発したので、使い方・開発の意図・裏の処理・ソースコードも含めて記事化しておこうと思う。

フォルダ階層メーカー_v1.xlsm
※リンク先の画面で「ダウンロード」マークを押して、DLして使って下さい。

まずはスクリーンショット

画面自体は極めてシンプル。フォルダの階層構造を記述していくエリアは緑色で塗られた部分。階層構造を記述したら、「処理スタート」のボタンを押すだけ。そうすると、この「フォルダ階層メーカー_v1.xlsm」が置かれているフォルダ（ディレクトリ）と同じ階層に、第１階層のフォルダが生成され、またその中に第２階層以下のフォルダが生成される。

メインとなる機能

やっていることも極めてシンプル。階層構造で記載をしたリストの通りに、階層構造のフォルダを生成する。いちおう、処理においては、

すでに生成されているフォルダが存在したら新たに生成しないし上書きもしない
A3セルは第１階層として書かれているべきなので、ここに何も書いていない場合は処理を開始しない

といったことをしてくれる仕様になっている。

上述したとおり、使い方としてはこのツールを設置したフォルダの中に、記述した階層構造のフォルダを生成していく。たとえば、このツールを「令和３年度」というフォルダの中に置いて、スクショのように階層構造を記述して処理をすると、こうなる。

＜処理前＞
[Folder] 令和３年度
└[Excel] フォルダ階層メーカー_v1.xlsm

＜処理後＞
[Folder] 令和３年度
├[Excel] フォルダ階層メーカー_v1.xlsm
└[Folder] 学年教師フォルダ
　├[Folder] 中学１年
　│├[Folder] A先生
　│└[Folder] B先生
　└[Folder] 中学２年
　　├[Folder] A先生
　　└[Folder] B先生

開発の背景

私が赴任した現任校は小中一貫校で、同じ建物の中に小学校と中学校が入っている。職員室も同じ部屋の中なので、互いの垣根をそんなに感じないのがいいところだと思っている。とはいっても、法的な位置付けで言えば小学校と中学校がそれぞれの組織体系として成り立っている状態なので、とうぜん校務も小中が別れているのが基本だ。小中一貫校としての共同の取り組みも存在するが、日々の業務の大半は校種別となる。

さて、私が勤務し始めた2019年度当時にはすでに、業務用データはすべて市の教育委員会が用意した学校用ネットワーク内にあるファイルサーバーに格納することになっていた。昔は、学校内でイントラネットを張って、校内PCに直挿ししたハードディスクを共用していたようだが、きちんと校務用PCとネットワークを配備したのは素晴らしい。で、2019年度当時の、中学校のフォルダ整理ポリシーは以下の通りだった。

業務別フォルダ > 年度別フォルダ > 各種データ

大きく校務分掌ごとにフォルダ分けがなされ、その業務内に年度別のフォルダが生成され、各種データを格納していく、というスタイルだった。しかし、ここには一つ問題があった。「業務別フォルダ」といっても、ある業務においては

教務事務 > 生徒名簿 > 平成30年度

となっているのに対して、別の業務では

成績評価 > 平成30年度 > 評定

となっており、どの階層で年度分けがされるのかが定かになっていなかった。

また、そもそも2019年度当時のフォルダ分けは運用から数年経っていることもあって、フォルダ構造がどうなっているのかが定かでなく、的確な業務データを参照することが難しい場合があった。さらには、小学校と中学校でフォルダ構成ポリシーがまるで違っていた。実は「小中一貫校」として１つの共用サーバーエリアが供用されており、実態として小学校の教員が中学校のフォルダを、またその逆を参照することもあるのだが、その場合のデータ参照がめんどくさいことになっていた。

その状況をみかねた教務主任が、フォルダを校務分掌や学校運営の業務種別に合わせて整理し直すことを考え、技術の先生（元校長で再任用）に依頼をしてフォルダ整理案を作っていただいた。そのフォルダ整理ポリシーが

年度 > 校種 > 業務 > 各種データ

という順序である。そしてこの「業務」に当たる部分は、学校運営に一通り必要な業務を、校務分掌表等に基づいてあらかじめ階層構造にしておく、という形にした。

そして令和２年度を迎えたタイミング。このタイミングで、教育委員会によるネットワークおよび校務PCの管理体制が変更になり、サーバーの移管が図られることになったため、2019年度までのフォルダ構成のうち、2019年度（令和元年度）の単年度で作成されたデータは、新たなフォルダ整理ポリシーにしたがって階層構造を生成した「令和１年度」というフォルダに格納し直し、それ以前のデータは外付けHDDに退避させて書き込み禁止にした。その上で「令和２年度」のフォルダ構成を、一つ一つ手作業で生成して運用を開始した。

この時点で「これ、来年どうするんだよ」と思いつつ「まぁVBAでそのうちツールでも作るか」と思っていた。思っていたら、そのまま１年がすぎ、フォルダ生成をしなければいけな時期がやってきた。教務主任が「どうしようね」と言い出し、情報担当（私ではない）に「令和２年度をコピーして不要データを消していくか、あるいは一からフォルダ構造を作って」と依頼をかけようとしていたので「ちょっとまってください。もっと手軽にできる方法を考えます」と言って半日時間をもらった。

そこで私が行ったのは以下のことだ。

手軽にフォルダを階層構造にする方法はないか、ググった。同じようなことを考えている人がいるもんだ。
調べた結果は、コマンドプロンプトとエクセルの合わせ技だった。つまり階層構造をエクセルで生成してコマンドプロンプトのコードを作成し、コピペで流し込む、というものだった。
そのためには現状のフォルダの階層構造を確認する必要があったので、コマンドプロンプトのtreeコマンドで階層構造を確認した。

そして、この情報をもとに、コマンドプロンプトに流し込むためのフォルダ階層リストを作った。そしてそこまできて「あれ、もうここまで作ってしまえば、VBAでコード書くことは造作もないんじゃないか」と思った。そして調べた結果、フォルダ生成に必要なコードはとても単純なものだった。

MkDir

なんだ。コマンドプロンプトのmkdirコマンドとほぼ一緒じゃねぇか。そんなこんなで、２年ぶりくらいとなるVBAのコーディングをし始めたのだが、調べたり細かなデバッグをしたりしながらで、結局２時間とかからずツール作成まで至ってしまった。実際、フォルダ生成の階層を現したリストは390行になったので、それを１つ１つ手作業で作成していくことを考えると恐ろしい。それが、ボタン一発で済んだ。自動化万歳。

再掲：ダウンロードはこちらから

フォルダ階層メーカー_v1.xlsm
※リンク先の画面で「ダウンロード」マークを押して、DLして使って下さい。

裏の処理のこと

ただツールとしてはもっとスマートにできなかったものかと悔いてもいる。なぜなら、以下のような制約があるからだ。

階層構造を５段階までしか設定できない。もっと深くしたい場合など、階層の深度を自由にしても対応するようなコードにはできていない。
ツールのF列からK列には、処理用の関数が入っている。厳密に言うと関数はF3:K3の範囲にしか書かれていないが、うっかりこれが消えてしまったらツールが回らない。関数を入れずにコード部分だけでどうにか処理してもよかったかもしれない。

ではそのF列からK列の関数はなにをしているのか、という話だ。最終的には以下のスクショの通り、K列で、作成するフォルダのパスを生成している。

そのためにまず、F / G / H / I / J の５列には、A / B / C / D / E の５列にそれぞれ対応するように、以下の関数を入れている。

[F3] =IF(ISBLANK(A3),F2,A3) 
[G3] =IF(ISBLANK(B3),IF(F2=F3,G2,""),B3) 
[H3] =IF(ISBLANK(C3),IF(G2=G3,H2,""),C3) 
[I3] =IF(ISBLANK(D3),IF(H2=H3,I2,""),D3)
 [J3] =IF(ISBLANK(E3),IF(I2=I3,J2,""),E3)

スクショにあるように、例えば第１階層「学年教師フォルダ」の子階層に当たる第２階層「中学１年」のフォルダを記載するとき、「中学１年」という文言は、親階層の「学年教師フォルダ」が書かれているA3セルから見て、１つ下の行・１つ右の列にあたる、B4セルに書かれている。このとき、A4セルは空白である。この場合、「中学１年」のフォルダは、厳密には 学年教師フォルダ¥中学１年 という構造になっている必要がある。そこで、当該セルが空白の場合でも上のセルの内容を参照する、といった処理が必要になる。

ただし、この「当該セルが空白の場合でも上のセルの内容を参照する」をそのままやってしまうと、スクショでいうところの６行目と７行目でおかしなことが起きる。H3セルの関数のままだと、

[line6] 学年教師フォルダ¥中学１年¥B先生 
[line7] 学年教師フォルダ¥中学２年¥B先生

となってしまう。なぜなら、B7セルでは記載内容が切り替わっているのに、C7セルが空白なので、そのままC6セルの内容をコピーしてきてしまうからだ。そこで、手前側のセル（第３階層に関するセルの「手前」は、第２階層に関するセル）の内容が切り替わっているかどうかを判定する処理を噛ませている。

その上でK列ではフォルダパスを生成する関数を以下のように設定している。

=IF(F3="","",F3)
 &IF(G3="","","¥"&G3) 
&IF(H3="","","¥"&H3) 
&IF(I3="","","¥"&I3)
 &IF(J3="","","¥"&J3)

単純な話だ。空白だったら飛ばす。空白じゃなかったら、第２階層以下はフォルダの区切り文字である”¥”を入れる。

で、実際にはこれらの関数を、F3からK3の範囲にのみ書いておいて、VBAのコードにおいて必要な部分までコピーする、という方法をとっている。

ソースコード

で、今回のソースコードがこちら。

Sub makeDir()
    
    Dim ts As Worksheet
    ts = ThisWorkbook.Sheets(1)

    'A3セルに第１階層を指定していないと動かない
    If ts.Cells(3, 1) = "" Then
        MsgBox "３行目はかならず第１階層から指定してください"
        Exit Sub
    End If

    '階層リストの下限を確認する
    x1 = ts.Cells(1048576, 1).End(xlUp).Row
    x2 = ts.Cells(1048576, 2).End(xlUp).Row
    x3 = ts.Cells(1048576, 3).End(xlUp).Row
    x4 = ts.Cells(1048576, 4).End(xlUp).Row
    x5 = ts.Cells(1048576, 5).End(xlUp).Row
    
    x = x1
    If x < x2 Then x = x2
    If x < x3 Then x = x3
    If x < x4 Then x = x4
    If x < x5 Then x = x5
        
    'F3:K3の関数をコピーしていく
    ts.Range(ts.Cells(3, 6), ts.Cells(3, 11)).Copy
    ts.Range(ts.Cells(4, 6), ts.Cells(x, 11)).PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
    ts.Cells(3, 1).Select
    
    '動作の確認
    pathMainDir = ThisWorkbook.Path
    Dim chkDo As Integer
    chkDo = MsgBox(pathMainDir & Chr(13) & "にフォルダを作成します。", vbOKCancel)
    If chkDo = vbCancel Then
        ts.Range(ts.Cells(4, 6), ts.Cells(x, 11)).ClearContents
        ts.Cells(3, 1).Select
        Exit Sub
    End If
    
    'フォルダの生成（既存の場合は生成しない）
    For i = 3 To x
        pathCreateDir = pathMainDir & "\" & ts.Cells(i, 11)
        Dim chkDir
        chkDir = Dir(pathCreateDir, vbDirectory)
        If chkDir = "" Then
            MkDir pathCreateDir
        End If
    Next
    ts.Range(ts.Cells(4, 6), ts.Cells(x, 11)).ClearContents
    ts.Cells(3, 1).Select
    MsgBox "完了しました"

End Sub

実際のフォルダ生成部分はMkDirだけだから、そこに至るまでが長いというところはあるが、誤りのないように動作させることを考えると、これくらい書いた方がいいな、とも思いながらコーディングした。

再掲：ダウンロードはこちらから

フォルダ階層メーカー_v1.xlsm
※リンク先の画面で「ダウンロード」マークを押して、DLして使って下さい。

おわりに

書いたコード自体は大したことはない。プロのエンジニアからは「こんなことくらいで」と思われることだろう。しかし忘れないでほしい。私は、経歴こそ「ふつう」ではないし、前職時代に多少だがコーディング経験があったくらいのものではあるとはいえ、学校教員である。コーディングできなくたって主たる仕事は成り立つ類の人間だ。

だから、全教員がここまでコーディングができる必要性なんて本来は無いし、まして「学校に一人必要」というのもこれまた違う世界観である。ただ、私は好きでこうやって書いてしまうだけである。

だがしかし、自分で作るにせよ、すでに世の中にあるツールに頼るにせよ、ちょっと立ち止まって考えたり調べたりして、「これってもっと【便利に手早く間違いなく】できないかな」と試してみる、ということはすべきだと思う。そしてだいたい、その【便利に手早く間違いなく】行う手立ては、誰かが用意していたりする。

前職時代、エクセルの研修をしていた時によく言っていた言葉がある。

あなたの困っていることは
他の人も困っていて
だいたい誰かが解決している

だから、ググったほうが早い、と。実際、今回のコーディングにおいても、「あれ、これって」と思った時にはすぐにググった。私と同じことに困っている人や、その解決を図った人もすでにいて、ご丁寧にWebにまとめられていたりもする。なので私も、作成したツールを公開することで、次なる「困り人」への貢献をしている。

もう一つ。今回このツールは「汎用ツール」として公開した。私の場合は校内の業務フォルダ生成のために用いたが、このツールは私だけ・勤務校だけで使えるものではないはずだ。このように、ツールを開発するうえでも、その現場の文脈だけに依存するものはあまり好ましくないというのが私の考えである。

いずれにせよ、これで誰かが便利になったとしたら、それはそれで非常に嬉しいし、このようにして学校現場での仕事の大変さを軽減する人が、現場の中にちょっとずつでも増えていけばいいな、と思う次第である。

Comments

comments

enshino Archive

生きる想いを残したい　−　遠藤忍のenshino.biz

オンライン学習のよもやま：⑨「校務改善ツール：フォルダ階層メーカー」

まずはスクリーンショット

メインとなる機能

開発の背景

裏の処理のこと

ソースコード

おわりに

Comments

「オンライン学習のよもやま：⑨「校務改善ツール：フォルダ階層メーカー」」への1件のフィードバック