1.予定変更への対応 今の学校では,毎週来週の学習予定とめあてを書いた週案簿を提出することになっています。来たばかりの時は何も分からず,毎回手書きをしていました。2年目になって慣れてきてから,年間の週案簿の教科名だけは印刷して,めあてだけを手書きしていました。その次は,週案簿と各教科の1年間のめあてを書いたシートをリンクさせて,1年分の週案簿を4月に作りました。これで1年間週案簿作成からは解放されると安心していたところ,年度途中に専科の都合で大幅に学習予定の変更をすることになりました。週案簿の教科に合わせてめあてのシートから1つずつリンクさせていたので,学習予定が変わるとまたリンクも張り直さなければなりませんでした。 そこで,1年間のカレンダーに教科を入れるだけで,それが週案簿に反映され,めあてもその教科を判断して自動的にめあてを書いたシートからリンクしていくようなソフトがほしいなと思いました。これなら,途中で教科の変更があっても,カレンダーの予定を打ち変えるだけで,勝手にめあても反映されて変更されるはずです。当然,毎年使用できるよう年度変更もできるものでなければなりません。 こんな時はインターネットで検索するに限ります。そこで,週案簿のソフトを検索してみると,カレンダーから教科は週案簿に反映できても,めあては1つずつ貼り付けていくものしか見当たりませんでした。同僚に,マクロでプログラミングされた,めあても反映される週案簿作成ソフトを持った方がおられましたが,それは,年度変更のために毎月の日にちを打ち込む必要があったり,土日に対応していなかったり,XPでは作動しなかったりで,使用する気になりませんでした。 そこで,一から自分でソフトを作ってみることにしました。マクロなどはとても無理なので,カレンダーに教科を入れた瞬間,「入力」等のスイッチをクリックすることなく,数値を入れると平均がすぐに計算されるのと同じ感じで,週案簿に教科とめあてがパッパッと入っていくようなものになるはずだと思いました。 2.週案簿とカレンダーの相違 今回の一番の課題は,週案簿に表示された各教科に合わせて,めあてが第1時から順に自動的に入っていくという仕組みです。全くその方法が思いつかないまま,その前に取り敢えず,カレンダーに入れた教科が週案簿にリンクされるシートを作ろうと思いました。 ある年だけ使用するものを作るのなら,カレンダーと週案簿の同じ日にリンクを張っていけば済みます。しかし,それでは年度変更すると,日にちと曜日がずれるので,リンクを張り直さなければならなくなります。 そこで気づいたのは,このずれはカレンダーと週案簿の表示の違いによって起こるということでした。カレンダーは毎月1日から始まり,週案簿は月曜日から始まります。日にちに曜日を合わせるカレンダーと,曜日に日にちを合わせる週案簿をリンクさせようと思うから難しかったのです。 そこで,カレンダーを週案簿のように,月曜日から始まる形式にしました。すると,週案簿とぴったり合うようになり,年度変更してもリンクを張り直す必要がなくなりました。 3.年度変更 指定した年のカレンダーを自動作成する方法は,エクセルの基本的な技のようで,パソコン雑誌等によく載っています。私もこれは雑誌を参考にして作成することができました。基本的にはDATE関数,WEEKDAY関数,MATCH関数の3つを使用するとできますが,それに祝日名の表示や祝日以外のエラー表示を消したり,祝日の日付を赤色にしようとするとVLOOKUP関数,ISERROR関数,ISTEXT関数を使うことになります。 4.一列 時間割を入れるカレンダーと週案簿の教科欄とのリンクは,どちらも曜日を固定したために,年度変更に対応できるようになりました。次がいよいよ一番の課題である,めあての流し込みです。 ただ単に週案簿に表示された教科に合わせて,1つずつめあてを貼り付けていったり,リンクを張ってしまうと,時間割の変更があった場合,また1つずつ貼り付け直していくことになります。そうならないようにするには,週案簿に表示されるある日の国語は,最初の国語から数えて何番目かを判断して,その何番目に合うめあてを選んで持ってくるような仕組みにしなければなりません。 そこで,ふと思いついたのは,「最初から数えて何回目の国語か」を判断するには,時間割の教科を4月から3月まで一列に並べればいいのではないかということです。つまり,例えば4月6日の1時間目から3月25日の6時間目までの時間割を一列につなげて並べるのです。 5.エクセルの果て セル1つに教科名を1つ入れて,4月から3月まで土日も入れて横一列に並べていくと,1年で(8月を抜いて)2058個のセルを使うようになります。今回この作業をしていて初めてエクセルの横の果てが256までしかないことが分かり,教科名の列を縦に9列作らなければならなくなりました。エクセルは縦の方が長く,6万以上セルがあることが後で分かり,本当は縦に並べていけばよかったと思っています。 6.カウント この時点ではまだ,めあてを教科に合わせて順番に週案簿に反映させていく方法は分かっていませんでした。 しかし,そのために必ず必要になるのは,それぞれの教科の実施回数を示す通し番号の表示だと思いました。そのために1年間の授業のコマを一列に並べたのでした。この表の中の国語なら国語だけを最初から1,2,3と数えていくことができれば,3番目の国語なら,そこに教科のめあて一覧表の中から国語の枠の3番目のめあてを持って行くようにすればいいのです。 もし国語だったら数える・・・,もし,国語だったら,数える・・・。と,それならカウント,イフではないですか。そこで,例えば,B7のセルから教科枠が始まっているとすると,「=COUNTIF($B$7:B$7,"国")」と数式を入れ,後はこの数式をコピーしていけば,それぞれのセルまでの国語の合計を数えられることが分かりました。そこで,教科の枠の下に国語の通し番号を表示する枠を作り,ここにカウントする数式を入れました。 このようにして,それぞれの教科等のカウント枠を作りました。 7.シート めあてが反映できる週案簿ができるかどうか分からないまま,ここまでに4種類のシートを作ってきました。 シート1は時間割を入れる年度変更可能カレンダーです。シート1とシート4の週案簿の年度変更をする場合はこのシートの年度の欄に年を入力します。学年の欄はシート2のめあて一覧表の表題の学年欄とリンクしています。各教科の時数確認はこのシートで行います。下の学期毎の時間割に教科を入れると,カレンダー全体に反映されます。右側にある祝日表示用の枠に各年の日付を入れると,シート4の週案簿の行事欄に祝日名が表示され,日付の文字色が赤になります。右端にある表は,エクセルが各年のカレンダーを作成する時に使用する枠です。年度入力すると,この表を使ってエクセルが第1土曜日が何日かを調べ,その日を規準に日付を表示してくれる仕組みで,普段はこの表があるセルは非表示にしておきます。 シート2は各教科の1年間のめあてを書き込む一覧表です。ここに表示しているのは,2009年度,5年生のめあての一部分です。 シート3は1年間の時間割を横一列に並べて,各教科の実施回数を表示できるようにしたものです。このシートはシート1とリンクさせていて,シート1のカレンダーに教科が入ると,それがシート3に反映され,教科名がずらっと横一列に並びます。 そしてシート4が年度変更可能週案簿です。2週間分をA3サイズで印刷し,袋とじにしてA4ファイルに綴じます。土日の枠は非表示にしており,必要な時だけ表示して印刷します。 8.プロジェクト 「独自の製品、サービス、所産を創造するために実施される有期性の業務」をプロジェクトと呼ぶ(プロジェクトマネジメント協会)そうです。 エクセルの操作の中で,関数と言えば平均と合計ぐらいしか利用していなかった私にとって,今回「週案簿に教科とめあてを連動させて表示させる」ソフトを作るという,独自に創造することを来年度が始まるまでに達成するという有期性をもつ作業は,充分「プロジェクト」と呼ぶに相応しい仕事だと勝手に思っていました。 この週案簿プロジェクトの最初の課題は年間教科カレンダーと週案簿の絶対的リンクでした。絶対的とは,年度変更してもリンクの場所を変えなくてもすむものということです。これは,教科カレンダーを週案簿と同じ「月曜から始まる曜日主体型」にすることで解決できました。 次にカレンダーの年度変更をするという課題は,パソコン雑誌に方法が載っていたので,関数の勉強をしながら設定できました。 そして次が,週案簿の教科に合わせてめあてが自動的に貼りついていく仕組みを作るという大きな課題です。このためには年間の全ての教科に通し番号が必要になるので,1年間の時間割を一列に並べたシートを別に作ればいいのではないかと考えました。そして,実際一列に並べたことで,各教科に通し番号を付けることが「COUNTIF」という関数を使うことで解決できました。後は,この一列に並んだ教科を何の教科か判断するにはIF関数が使えるのではないだろうか,次にその判断した教科のめあてを持ってくる関数を探せばいいのではないか,確かパソコン雑誌に何とかルックアップ関数というのが「データを取り出す」と書いてあったが,これが使えるのではないかと考えました。 最初は全く手探り状態だったものが,ここまできたあたりから,何だか本当にプロジェクトXっぽい「そのとき,ふと気が付いた」的なナレーションが聞こえてくるような気がするくらい盛り上がってきて,もうすぐできそうだという期待感でわくわくしてきました。 しかし,実はこの最後の山だと思っていた課題の解決後に,更なる課題が待ち構えていたのでした。プロジェクトX・・・。 9.IF関数 週案簿に直接各教科のめあてが入るようにするのは難しそうなので,まず,1年間の時間割を一列に並べたシート3の各教科の下にそれぞれのその日のめあてを表示する方法を考えました。このためには,「もし,国語だったら,めあてを書いたシート(シート2)の中からその国語の通し番号に合うめあてを探してきて表示する。」という関数を考えなければなりません。これは,「もし」ですからIF関数を使うことになるはずです。 そこで,まずIF関数の仕組みをエクセルの「この関数のヘルプ」で見てみました。すると,「=IF(A2>89,"A",IF(A2>79,"B", IF(A2>69,"C",IF(A2>59,"D","F"))))」という例が表示されていました。これは「もしA2セルの数が89点より高かったら,Aを表示し・・・それ以外だったらFを表示する」という意味のようでした。 これを利用して,=IF(A2="国",国語のめあての中から○番目のめあてを表示する,IF(A2="算",算数のめあての中から○番目のめあてを表示する,・・・・・,"")))))・・・)という意味の式を作ればいいのだろうと思いました。 ここで問題は「国語のめあての中から○番目のめあてを表示する」という指示はどの関数でできるのかということです。どうもこれは最初思っていた「ブイルックアップ関数」ではできないようでした。 10.INDEX関数 めあての一覧表の中から必要なめあてを取り出して表示できる関数はないかなと,パソコン雑誌を見ていると,「該当する位置からINDEXで取り出す」という見出しが目に入りました。それは「商品一覧表から自動で品番や単価を取り出し,表示する請求書のひな型作り」の記事の中にありました。それによると,INDEX関数を使うと,「範囲の中から行と列を指定して,データを取り出すことができる」ということでした。例えば表の範囲がB8からE12まであり,その中の上から2行目,左から3列目のデータを取り出すには,=INDEX(B8:E12,行番号2が表示されているセル名,列番号3が表示されているセル名)とすればいいのです。 これをIF関数の中に入れたらできそうな気がしてきました。IF関数の中にINDEX関数。素人には,このひらめきが今回のプロジェクトのクライマックスだったように思います。 11.「7」の壁 IF関数の中にINDEX関数。これで今回のプロジェクトを終えることができる,と安堵したと言う訳ではありませんでした。実は見て見ぬ振りをしていた気になる一文があったのです。それは,「この関数のヘルプ」で見た「IF関数」についての説明でした。そこには「最大 7 つまでの IF 関数を真の場合、偽の場合としてネストすることにより、より複雑なテストを行うことができます。」と書いてありました。この中の「7つまで」が気になっていました。1年間の時間割の中には7つどころか,19種類の教科等が現れてくるのです。よく分からない私は,とりあえず「国語」「算数」「理科」「社会」・・・と7つ以上,8個か9個のIF関数を設定してみました。すると,案の定,うまく動いてくれませんでした。そこで,19個の教科等を3分割してそれぞれに何の教科か判断する計算式を作ることにしました。 例えば上の「国語・算数・理科・社会・図工・体育」の欄には次のような式を設定しました。 「=IF(B36="国",INDEX(めあて入力!$C$3:めあて入力!$C$300,Bypass!B42,Bypass!$B$3),IF(B36="算",INDEX(めあて入力!$L$3:めあて入力!$L$300,Bypass!B45,$B$3),IF(B36="理",INDEX(めあて入力!$O$3:めあて入力!$O$300,Bypass!B46,$B$3),IF(B36="社",INDEX(めあて入力!$R$3:めあて入力!$R$300,Bypass!B47,$B$3),IF(B36="図",INDEX(めあて入力!$U$3:めあて入力!$U$300,Bypass!B48,$B$3),IF(B36="体",INDEX(めあて入力!$AA$3:めあて入力!$AA$300,Bypass!B51,$B$3),""))))))」 ここで,「Bypass」というのは,シート3に,「めあて入力」というのはシート2につけた名前です。 12.3種類の週案簿 「IF関数は7つまでしか判断できない」ということから,教科等を3分割しなければなりませんでした。すると,例えば「国・算・理・社・図・体」だけのめあてが表示された週案簿と「道・毛・硬・保・総・英」のめあてが表示された週案簿と・・・というように3種類の週案簿をまず作って,後で1つに編集・・・というすごく煩雑なものになりそうな予感がしてきました。 13.テトリス めあてが表示されるセルが3行になるため,それぞれのセルとリンクを張るには,3種類の週案簿が必要になります。これではとても使い物になりそうにありません。もうほとんどできたと思ったところで,またまた新たな課題が生まれてしまい,もう気分は十分プロジェクトXでした。どうにかこの3行にわたって表示されるめあてを1行にまとめることはできないだろうか,と考えて思いついたのは「テトリス」でした。 14.CONCATENATE関数 3行のどこかのセルに表示されるめあてを1行にまとめることはできないだろうか,と考えて思いついたのは,テトリスのように上から4行目のセルに全部落としていけばいいのではないかということでした。そこでIF関数を使って,まず上2つのセルの内容を1つにまとめ(もし上のセルが空白ならば,下のセルの内容を表示する。そうでなければ上のセルの内容を表示する,という式。)そのまとめたセルと3行目のセルの内容を再度同じようにまとめることにしました。これで1つのセルに19種類の教科等のめあて全てを表示することができるようになりました。 しかし,複数セルの内容を1つのセルにまとめるにはCONCATENATE関数を使えばすぐできることでした。ただそれが分かったのは,このテトリス式の設定を全て済ませた後でした。 15.リンク 結局テトリス方式のままで,めざしていた「教科に合わせてめあてが表示されていく」週案簿の仕組みが一応出来上がりました。ここまでは,1月の3連休を全部使うことでできました。しかし,実はここから約2週間かかった作業があります。それはめあてが教科に合わせて表示されるようにしたシートのセルから,実際の週案簿のシートのセル1つひとつにリンクを貼り付ける作業です。土日のセルにもリンクを貼りますから,8月以外の1年分で約2000以上のセルにリンクの貼り付けを1つずつしなければなりませんでした。 更に一応全部のリンクを貼り終えた後,リンクミスの点検を行うのにも時間がかかりました。 16.ヘッダー シート4の週案簿のヘッダーには右上に「&[ファイル名]」,左上に「No,&[ページ番号]」を表示するように設定しました。そして,このソフトのファイル名を「週案簿( 年 組)」としました。これで,このソフトを使う先生がファイル名に自分のクラス名を入れると,週案簿には全て右上に「週案簿(○年○組)」と印刷され,左上には通し番号(4月から3月までをShiftキーを押しながらシート見出しタブを一度に選択した場合)が印刷されることになり,丁合がしやすくなります。 17.合理性 毎週,毎時間の教科に合わせて指導書を取っ替え引っ替えしながらめあてを書いていくのは,時間のかかる作業になります。それより1つの教科のめあてを表に一気に記述していく方が合理的です。そして表に書いためあてが,週案簿の教科に合わせて自動的に反映されていったらすごく助かるなぁと思っていました。そのソフトをどうにか今回作ることができたと思っています。 このソフトを使って,同学年の3クラス分のそれぞれ時間割が違う週案簿1年分を作り,ファイルに綴じ表紙をつけ先生方に渡し,チェックしながら利用してもらっていますが,今のところミスも見つからず,大変喜んでもらっています。 以上エクセル奮戦記「週案簿の巻」でした。(もし,素人が作ったこの週案簿ソフトを自己責任で試してみようという勇気のある方はご連絡下さい。ソフトを添付して返信します。) |