そうだ！当番表をつくってみよう（後編）

そうだ！当番表をつくってみよう（前編）別窓開
そうだ！当番表をつくってみよう（中編）別窓開

の続きです。

　ランキング参加中☆読んだよ～の足跡代わりに　
　それぞれ１ポチしてもらえると嬉しいです（＾ー＾）　
　　

同居人が、無知な私を若干置き去りにしつつ作ってくれた計算式
↓↓↓
=IF(F6="当番",INDEX(ローテ表!$B$2:$B$23,MOD(NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165)-1,ローテ表!$E$2)+1,1,1)," ")

ローテ表シート
土日長期休暇シート
を参照して既にカレンダーシートにつくられたＢＯＸカレンダーには休日表示と当番が入力される予定のセルの上に「当番」と表示されるように目印がつけられている状態です。

と文字表示してもピンときませんよね（＾＾；）
私自身、大してエクセルに詳しい訳ではないので、検索してあちこちのサイトさんを参考にさせてもらったり、関数辞典で関数そのものの使い方を調べたりしながら作成したので、画像がないとどのくらい解りにくいかというのは承知しています。
はたして私が説明して合ってるのかどうか非常に不安ですが、手持ちの資料は山ほどあるので、画像を交えて説明してみようと思います。







まずは、どんなカレンダー作ったんだよ、君！！ってのがこちら
↓↓↓

ウィンドウを大きめにしてもらえれば適度なサイズで表示されると思いますが、どうでしょ？
それぞれ閲覧環境があるので、まあ見える範囲でウィンドウ広げてみてください。

見てのとおりエクセルの画面です。
カレンダーが表示されています。私が作ったカレンダーです。普通のよくあるヤツ（＾＾；）

スタンダードではないのは、カレンダーの１週目です。
１日～４日が「春休み」と記載されています、これが自動入力されてる学校専用の休暇表示です。

このカレンダー、Ｂ１セルに年号を入れＣ１セルに希望の月を英数半角で入力すると横７マス×縦５マスの指定セルに自動で日にちを表示してくれる、万年カレンダーです。検索かけると作り方がを紹介しているサイトがたくさんＨＩＴするので、どなたでも簡単に作れると思います。
私が改良したのは
●参照されている「土日長期休暇」シートに予め作っておいた一覧を参照して独自の休日表示をする
●カレンダーの前後に出てしまう前月・次月の日付を基準セルから算出し、英字表示させる
という所です。


単純に日数だけだとこの４月のように日曜始まりの月は良いのですが、土曜始まりだと前月の末がズラーっと表示されてしまいます。カレンダーとしてややこしいのでそれを消すのに前回説明した条件付き書式設定で白色表示にしようとしたんですが、失敗したんですよ（＞＜。）
消すってのはちょっとできないので、白色（背景色に同化させる）で見えなくしたいんですが、他の方法だと白色指定ができませんでした。グレーで薄く表示ってしてもいいかな？と思ったら色指定に限界があり中々上手く機能しない（のは私の腕だと思うけど）
そこで、もう出ちゃうものは仕方ない！と割り切って、ではこの無用な日付が無用だと見た方が解るようにするしかないかな？って事でちょうど拾いやすい月表示を追加することを思いつきました。
でも、日にちが数字なのに更にその下に「４月」とか数字が重なると余計見難いので、英語表記ならいいかもって単純な発想です。簡単作業で簡潔に表示できた気がします・・・・私の腕としてはって事ですが（＾＾；）
関数がそれぞれ入っているので、このカレンダーを作るのは最初の通り、年と月を数字入力するだけです。
後はローテーションを組んでおいて、手打ちで当番を入力するだけなので、そんなに時間がかかるものでもありません。

例年班長さんが苦労していたのは、このカレンダーを作る事だったんじゃないかと。

皆さん、ワードを使って作られてるようでしたから
　　１）枠を組む
　　２）レイアウトを整える
　　３）日付を書き込む
　　４）学校の日程表から休みを拾う
　　５）ローテーションを打ち込む
みたいに作業されるでしょう。

ワードの表作成ってレイアウトの調整がちょっと面倒なんですよね（＾＾；）
１回に作るカレンダーは１か月分の時もあれば２～３か月分の時もあります。
他に配布する書類の情報とか日程の都合だけでなく、転出入があった時などは差し替えを作ることもあるんで、その時々によるんです。
欲しい月があっという間に表にされるだけで凄い作業が早くなると思います。
学校からは年度初めに年間予定表が発行されるのですが、これがＡ４サイズで１年分が一覧になってる！とまあ小さい事、原稿はもう少し大きいのだと思いますが、全家庭に配布される書類は規定サイズにそろえられる関係上、そのサイズになってるようです。
そこには、学校行事やらＰＴＡの会議やら色々と細かい記載がたくさんされているので、月ごとに毎回拾うのは結構ストレスになるはず。

エクセルだと土日は自動で拾ってくれる関数があるので、最初に４月１日を作ってその曜日を返すように設定して翌年の３月３１日までドラッグするだけで３５６日分の曜日が全て簡単に拾えます。日本の祝日＆振替休日表示は、ネット上で年別に一覧公開していくださってる親切なサイトさんがいくつもあるので、そこを参照して記入（コピーやＤＬでもいい）します。学校の長期休暇は初日だけを拾ってズズーっと終日までコピーするだけなので、特に時間がかかる事もないです。この一覧については、大抵の作業がコピーでできちゃうので、エクセルの知識はあまりいりません。
そんな手順で作られたのが、画像下部に名前だけ載ってる「土日長期休暇」シートです。
私の使った関数だとこのシートからは登校日は消す必要がありますが、大抵月～金を消すだけなので１ブロックずつ行削除するのでそんなに手間じゃないです。ただ、学校って年に何度か「土曜登校」みたいな事があるので、そこだけは注意して休暇から削除しないとダメですが、ほんの数日なので難しい事はないと思います。ちなみに、今回は運動会と文化祭の２回だけでした。というか例年そうみたいです。
しかも学校って春・夏・冬にまとめて長期休暇があります、そこはゴッソリ残す事になるので、間違いにくくて助かります（＾＾ｖ

もうこの時点で諦めてもいい気がしました。
お当番さんの入力セルは用意されてるので、手打ちでも全然時間かからないんです。
でも、折角久々にエクセルに触れる機会ができたし、少し学びたい気もするし・・・・でちょっと複雑な関数に心魅かれちゃってたのかもしれません（嫁のトキメキのせいで貴重なお家タイムを削がれる同居人って不幸　＞ｍ＜）

この専用カレンダーを更に便利にしたいので、ローテーション表を読み込ませたくなってしまいました。

=IF(F6="当番",INDEX(ローテ表!$B$2:$B$23,MOD(NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165)-1,ローテ表!$E$2)+1,1,1)," ")
で、この関数登場です。

これはどのセルに記載されているかというと

４月５日（木）の担当者「パンダ」が表示されたセルです！
ローテーションを読み込ませる為に、最初のカレンダーの関数を少し書き換えたので、ちょっと表示が変わっています。そして印刷用にレイアウトを整えた為、最上部の行（１行目）を空白にしたのでちょっとずれていますが、他は同じカレンダーです。

参照させたローテーション表はこんな感じ↓↓↓

こちらの担当一覧を自動で表示させてます。
記入の仕方も最初と同じで年月を数字入力するだけです（＾＾ｖ

便利です！凄く早いです！
というか、数字　２０１８と４と入力するとカレンダーがローテーション込で完成します！
翌月なら４を５と入力し直すだけです。



スゲー激速！

でも、問題は私がこの関数を書いたのではないんだよ～って事（ダメな人）
だから私なりにこの関数を理解してやろうじゃないの！と思いました。もしかすると次はこの位自分で書けちゃったりするかもよ？（ないだろう）






はいはい、話を戻します！
あの、複雑な関数の話です。
再三表示します（＾＾）





=IF(F6="当番",INDEX(ローテ表!$B$2:$B$23,MOD(NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165)-1,ローテ表!$E$2)+1,1,1)," ")

長いです（＞Д＜）複雑です（？▽？）解るようなわからないような～ははははははは
こういう関数は分解して考える事にしてます・・・・それでも難しいのは一緒だけど（＝。＝；）
まずは一番中心の関数を取り出す

NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165) NETWORKDAYS.INTLって、指定期間の稼働日を返す関数で 最初の日＝土日長期休暇シートのＡ２セル 最終日＝カレンダーシートＦ４セル 週末は日曜だけ指定（＝１１）、 その他の休日は土日長期休暇シートＡ２～Ａ１６５を参照する と指示している カレンダーのＦ４セルは、指定した休日を除外した期間内の何日目かを求めろ つまり・・・・ Ｆ４セルは平日の何日目になるか だと思う。

次はその外側ね

MOD(NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165)-1,ローテ表!$E$2) ＭＯＤ関数は余りをもとめる関数で、意味は・・・・ （NETWORKDAYS.INTLの条件ではじき出した値－１）÷　ローテ表Ｅ２セル を計算したら余りはいくつか 何故かココで「－１」登場！ 合計人数で割り切れる数が出てしまう事があるが、その場合余り０で返す数がなくなり関数が機能しなくなる。それを補う為に最初に＋１をしているのだが、これだとずっと１つ多い数を返し続けるので、どうやらココでマイナスして帳尻を合わせているらしい。 この帳尻合わせが実は私にもよくわからない・・・・理屈はわかるが、何故ここなのか？不明だけれども、今回の関数ではココがいいらしいので仕方ない。恐らく、一旦プラスで安定した値を返させてその直後に帳尻合わせするのがスタンダードな方法なんだと思う（思ってるだけ） この場合、分母はローテ表にある合計を表示したセルを指してるから 平日の★日目÷ローテーションをする人の合計人数＝余り◎（求めるのは余りだけ） だと思う。

更に外側に移動します

INDEX(ローテ表!$B$2:$B$23,MOD(NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165)-1,ローテ表!$E$2)+1,1,1) INDEX関数は範囲内の指定位置にあるセルの値を調べる関数 INDEX(参照範囲,行番号,列番号,領域番号)だから ローテ表シートＢ２～Ｂ２３の範囲で行がMOD(　)内の計算でだされた「◎＋１」だけ「縦位置移動」した値を求める（列と領域は１表示されているけど、これは念のため記載してるだけ） つまり・・・ ローテーション表の上から「◎＋１」番目の人は誰か だと思う。

で、最後に全部がこうなる↓

=IF(F6="当番",INDEX(ローテ表!$B$2:$B$23,MOD(NETWORKDAYS.INTL(土日長期休暇!$A$2,カレンダー!F4,11,土日長期休暇!$A$2:$A$165)-1,ローテ表!$E$2)+1,1,1)," ") もしもＦ６セルに「当番」と表示されていたら、指定日が期間内の何日目かを求め、担当合計人数で割った値＋１だけローテ表を下へ移動したセルが表示する担当者を返せ。もしも「当番」と表示されていなかったら「　　」と表示しろ だと思う。

ＭＯＤ関数の（　）内が複雑すぎて同居人の手助けなしでは私には無理だったと今も思います。

最初は「当番」と表示をせず「　　」空白表示にしていたんですけど、エクセルさんはどうも空白を読みにくくなることがあるみたいで参照ができなかったので改良後は当番と出すことにしました、いや別の文字や記号でも空白でさえなければいいですけど・・・・・まあ何となく。
本来これも条件付き書式設定で白表示に設定すれば消えるんですが、我が家では使えない状況らしいので、無理せずこんな形に仕上げました。

この関数の解釈は、今後の自分のために整理してみましたが、正解かどうかは自分でも解りません、多分こういってるんじゃないかな？程度で書いてるだけなので

あなた、全然あってませんよ！

とか苦情はお断りします。誰かに教えようだなんて大それた気持ちは全くありません！
覚え書きというかメモだと思ってください。覚え書き全世界に公開するのもなんですけどどこかに書いておけば、間違いに気づいたら訂正できるし、なくさず残しておけます（＾＾）
それに、関数そのものは合っていてちゃんと稼働しているんで、もしかして全く同じＢＯＯＫを作った方がいたら、コピペとかで使える可能性もあるので、ここに記載しました。

という訳で、無意味に悪戦苦闘して頑張ったんですけど、結局同居人の手を借りて完成しましたとさ。
これを機にすこし関数に親しんで自分でももう少し高度な関数を使えるようになれたらいいなと些細な目標をもった私です（＾＾）