回覧板の見ましたよ的なやつ
長く生きてると町内で役に当たるなんて事もしばしば。基本めんどくさがり屋なのでこういうものは出来るだけ避けたいところではありますが、当たればそれはそれで大人としてしょうがないと腹をくくりますpapypapioです。
今回はエクセルを使って回覧板の「見ましたよ的なやつ」を作ってみたのでご紹介したいと思います。興味ある方は参考にしてみたりしてみなかったりしてください。
町内会はいくつかの自治会が集まって構成されており、さまざまな連絡事項は各自治会ごとに管理されている「回覧板」を使って各世帯に伝えるというのが一般的だと思います。
回覧板を回す順番は家の並び方などで大体決まっていて、役名は地域によってそれぞれ違うと思いますが、毎年決められた組長さんや班長さんといった役の方(以降説明は組長さんでいきます)が管理して回覧板を回しています。
回覧板がまわってきたらまず内容に目を通して、確認ができたら自分の名前の横などにチェック、あるいは確認した日付などを書いて次の人にまわします。今回はこの”回覧板の内容、しかと確認させていただきました”という、見ましたよ的なやつをエクセルで作りたいと思います。
↑こんなやつです。まず回覧板は組長さんからスタートして、皆々様のところを回り、最終的にはまた組長さんのところに帰ってくるというシステムになっていると思います。という事は、回覧板の名簿の先頭はその年の組長さんの「お隣さん」ということになります。
また上の図のように世帯数が多い自治会の場合、組長さんのところまで回覧板が帰ってくるのに時間がかかってしまう為、ルートA・ルートBといったように、ルートを二つに分ける場合もあるかと思います。なので今回はルートが二つある場合で説明していきますが、そんなの必要ねぇって方はスルーしていただいて全然結構です。
そんな面倒くさいことしなくても、手書きでちょちょいっと書けばいいんじゃねぇの?って思うかもしれませんが、ここで問題なのは組長さんが交代になると順番がまた変わってしまうということです。ちょっとした事ですが、その都度いちいち名前を書き換えたりするのは面倒です。面倒極まりないです。
この面倒な作業をエクセルを使って「たった一つのセル」を入力するだけで、その年の組長さん、そしてお隣さんをはじめとする「決められた順番」の名前が一発で入るようになったら楽じゃないでしょうか。後はプリントアウトするだけ。楽ですよね?いや、楽なはず。うん、きっと楽。
「パソコンなんか持ってないし、そもそもエクセルなんか使ったことも見たこともないっす。」
もしそんな方が次の組長さんならば、設定してからプリントアウトしてあげましょう。あとはそれをコピーして使えばいいんですから。 世の中助け合う気持ちが大事です。 ちなみに設定さえしてしまえば、あとはたった一つのセルを入力するだけなので、エクセルを使った事が無い人でも簡単に覚えられると思います。
ではさっそく説明していきたいと思います。最初の設定は少し面倒ですが、一度作ってしまえば超楽になるはずです。後は役の引き継ぎをする際にこのエクセルで作成したデータも引き継げば、町内皆ハッピーなこと間違いなしです。 それでは次のページから作成していきます。
設定シートを作る
先程の図の「やんちゃ自治会」を例にして作成していきたいと思います。 僕のエクセルのバージョンが古いので、少し見てくれが違うかもしれませんが、だいたいは似通っていると思うのでお許しください。それではファイル→新規作成→新しいブックを選び、まずは「設定シート」を作りたいと思います。別にシート名は何でもいいですが、いちおうここでは設定としておきます。
入力する順番はどの家からスタートしても構いませんが、先程の図のように回覧板を回す「順番通り」に1番から順番に番号と氏名を入力していきます。例のやんちゃ自治会の今年の組長さんは「西島さん」です。
やんちゃ自治会の総世帯数は20世帯なので、西島さんを1番として、順に20番まで入力していきます。二つ目のルートを設定する場合は、後で上のセルにもデータを入力するので、世帯数以上空けるようにして、下の方から入力します。
そして次に世帯数の倍の数まで、20世帯の場合は40番までさらに下へ番号を入力していきます。二つ目のルートがある場合は、上の方にも数を減らしながら0から−19番までを入力していきます。二つ目のルートが必要無い場合はこの上方向への入力は不要です。
連番を入力するにはセルを選択して、Ctrlキーを押しながらセルの右下の+マークを下へドラッグすると数が増える連番を入力することができます。同じように上へドラッグすると数が減る連番が入力できます。
番号が入力できたら、1番から20番までの氏名欄を右クリックメニューから「コピー」を選択して、それぞれ「21番」、二つ目のルートを設定する場合は「-19番」の右隣のセルにペーストします。Ctrlキーを使って、【Ctrlキー+C】でコピー、【Ctrlキー+V】でペーストでもできます。
そしてさらに「E2~F3」「G2~I3」「E4~F6」「G4~I6」のセルを、それぞれ「セルを結合して中央揃え」でセルを結合させて下図のようにします。 結合したセルの上の段左側には「番号」、その右側に「今年度組長」と入力します。組長なのか班長なのか、お住まいの地域で使われている役名があると思うのでここは自由に設定してください。(図はこの後の説明がしやすいように、その下のセルをそれぞれ黄色と水色で塗りつぶしています。)
以上で設定シートの外枠は完成です。まだ関数を適用しなければいけませんが、それはまた後でやりますのでひとまずこれでOKです。↓見た目はこんな感じになります。
テンプレートを作成する
それではいよいよ「見ましたよ的なやつ」のテンプレートを作っていきたいと思います。設定シートとは別に、新しいシートで作成していきます。シート名は「テンプレート」でも「回覧」でも、わかりやすい名前をつけて下さい。
回覧ルートを二つ設定する場合は、もう一つ別にシートを作らなければいけませんが、それは今から作成するものを複製して作りますので今は不要です。
町内会によって文面や形式など、昔から決まっているものがあると思うので、ある程度それに沿って自由に作成してくださって結構です。ここで必要なのは「氏名欄」(黄色)と「組長欄」(水色)、そして氏名欄左の「番号欄」(緑色)です。ちなみにこの番号欄は関数で使用するだけのものなので、プリントアウトする時には写らないようにします
いちおう上の図は、回覧板に目を通した日付を記入する、「閲覧日スタイル」で作成しています。そんなスタイル名があるのかどうかは知りませんが、各々自由なスタイルで作ってみてください笑
設定シートに関数を適用する
それではあらかた出来上がったので、いよいよ「関数」を当てはめていきます。まずは設定シートからいきます。今年度組長下の水色セルを選択します。そして上の数式タブから検索/行列→VLOOKUPを選びます。
VLOOKUP関数とは検索する値に関連する値を、指定した範囲の中から抽出したい時に使う関数です。小難しいことはとりあえず後にして、まずはやってみます。
関数の引数が開きますので、それぞれの項目を入力していきます。まず「検索値」右端のボタンをクリックして、黄色のセルを指定します。同じように「範囲」には番号と名前すべての範囲を指定します。「列番号」には 2 、「検索方法」には FALSE と入力します。FALSEは完全一致という意味です。
これを数式で表すと =VLOOKUP(E4,B4:C63,2,FALSE) となります。
これは【黄色のセルに入力された番号(検索値)をもとに、検索範囲(範囲)の2列目(列番号)から黄色セルの番号と完全に一致(検索方法)する値を抽出する】という意味になります。
こんなことがサラッとできちゃうんですから、ホントすごいですよねエクセルって。さてさてうまくできたかな…??えっ…あれれ?って思いました?思いましたよね?
そうなんです、おそらく今、二つルートを設定している場合には名前が、ルートを一つで設定している場合は #N/A みたいなエラーが水色セルの場所に出てしまってますよね。
何故こんなことになるのかというと、黄色セルに何も入力されていない場合、黄色セルの値は「0」になります。なので0番に設定している人の名前が出るのです。そしてルートを一つに設定している場合は、検索範囲に0番がそもそも無いので#N/A(該当なし)というエラーが出るのです。
別にこのままでも使う分には全然差し支えはないんですが、なんとなくスマートじゃないので嫌ですよね。僕は嫌です。…うん、ぜってぇヤダ。
だからこの現象を解消する為にもう一つ関数を足します。別にこのままでも気にならないよーという人はこのままスルーしてくださって全然OKです。気にならなければ全然。…本当に。
IF関数とVLOOKUP関数を組み合わせる
この現象を解決してくれるのが「IF関数」です。IF関数とは、もしもこの値がこうだったらこう、違ったらこう!っていう関数です。
説明が雑ですいません笑 百聞は一見にしかず、とりあえずやってみます。まず数式バーの先程入力したVLOOKUPの数式をグイっとドラッグして全部選択したら、右クリックメニューで切り取り、もしくはCtrlキーを押しながらXでカットします。
そして今度は数式タブから論理→IF関数を選びます。
IF関数の設定項目を入力します。まず「論理式」には黄色セルを指定して、その後ろに =”” と入力します。”” は空白という意味です。入力を半角英数字にして、Shiftキー+キーボード上の 2 を二回押すと入ります。図の設定だと E4=”” となります。次に「真の場合」の欄にはまた “” とだけ入力します。そして「偽の場合」には先程カットしておいたVLOOKUPの数式を貼り付けます。Ctrlキー+Vです。
注!)この時にVLOOKUPの前にある「=」を消しておいて下さい。これを消しておかないと数式がおかしくなるので必ず消して下さい。
数式で表すと =IF(E4=””,””,VLOOKUP(E4,B4:C63,2,FALSE)) となります。
これはもしも黄色セルが空白だったら(論理式)空白に(真の場合)、空白じゃ無い場合(偽の場合)はVLOOKUP関数を適用する。という意味になります。
このようにIF関数とVLOOKUP関数を組み合わせることによって、セルに何も入力されていない場合、変な値やエラーを出さないようにできるのです。これは便利ですね!あぁスッキリ。
テンプレートにも関数を適用する
さあさあそれではテンプレートにも関数を適用させていきたいと思います。まずは氏名欄(黄色セル)一番上のセルを選択します。そして数式タブの論理から先程も紹介しました「 IF関数」を選択してください。
ここもスッキリさせたいので、まずはじめに 「論理式」に設定シートの黄色セルを指定します。そしてそのまま「F4」キーを1回だけ押してください。これで設定シートの黄色セルが「絶対参照」になり、$E$4 というように「$」マークがつきます。
絶対参照とは「指定されたこのセルだけを参照しますからっ!」って感じです。それに対して相対参照とは「結果を表示するセルが下にずれたら参照するセルも下にずれるし、右にいけば右にずれて参照するからっ!」って感じです。雑ですいません。
この場面で言うと、今はテンプレートの氏名欄一番上の黄色セルに数式を入力しています。そして数式が出来たらいちいち一つ一つのセルに数式を当てはめるのは面倒極まりないので、下にドラッグして他の黄色セルにもこの数式を一気にコピーします。
すると相対参照になっている場合、下にドラッグしてコピーした時に、参照するE4のセルも相対的に変化します。すぐ下のセルはE5を、その下のセルはE6を参照するようになってしまいます。結果数式が成り立たないので変な答えが表示されてしまいます。
相対参照は便利ですが、今回の場合はそれだと困るので、必ず絶対参照にしてください。
次に「真の場合」には先程と同じように””をポンポンッと入力して下さい。
そして「偽の場合」ですが、先程の流れでいくと、最初にVLOOKUPを設定しておいてカットしてからここでコピペしましたよね?実はこんな方法でも出来るんです。
まず数式バー左側の矢印をクリックします。ここには最近使用した関数がズラッと出てきます。その中に先程使用したVLOOKUPがあると思うのでそれを選択します。
「検索値」には設定シートの黄色セルを選択して、これもF4キーを1回押して絶対参照にします。そしてさらにテンキーの「+」を押します。テンキーが無ければShiftキーを押しながら L の右隣り、ひらがなの「れ」のところです。さらにさらにテンプレートの番号欄(緑色)の番号が 1 と入力されているセルを選択します。これは絶対参照にしなくて結構です。検索値の中は $E$4+A5 となります。
続いて「範囲」には設定シートの番号と名前のセル範囲(図中B4~C63)を選択します。そしてここもF4キーを一回押して絶対参照にしておきます。
あとは「列番号」には 2 、「検索方法」にはFALSEと入力します。数式はこうなります。
=IF(設定!$E$4=””,””,VLOOKUP(設定!$E$4+回覧A!A5,設定!$B$4:$C$63,2,FALSE))
上記のように数式が設定できたら、あとは右下の+マークから下へドラッグしてコピーです。
さあいよいよ大詰めです。下の方の「組長」と「戻り」の間にある水色セルを選択します。そして数式バーに「=」を入力して、設定シートの水色セルを選択します。数式バーは =設定!G4 となります。これは単純に設定シートの水色セルに表示されているものが、テンプレートのここにも反映されるよーということです。
これでテンプレートも完成ですが、ルートを二つ設定する場合はあともう少しだけお付き合い下さい。
テンプレートを作成したシート名の上で右クリック、「移動またはコピー」を選び、(末尾へ移動)を選択して「コピーを作成する」にチェックを入れてOKをクリックして下さい。
シート名は回覧A・Bや1・2など好きな名前をつけて下さい。そして今コピーした方のテンプレートの黄色セル一番上を選択します。実はこのままだとまったく同じルートが出来ただけなので、これから反対まわりのルートを設定します。
長ったらしい説明で申し訳ないですが、すぐに終わります。数式バーの中の「+」を「-」に変えるだけです。
後は先程と同じように下へドラッグして数式を黄色セル一番下までコピーしたら終わりです。以上で回覧板の見ましたよ的なやつは完成となります。長いことお付き合いいただき本当にありがとうございます。お疲れ様でした。
それでは設定シートの黄色セルに今年度組長の西島さんの「1」を入力してみます。
水色セルには今年度組長の名前、テンプレートの黄色セルにはお隣さんから順番通りに名前が出ました。
ちなみに回覧ルートAの黄色セル一番下が空欄なのは、この場合世帯数が組長を除くと19世帯となる為、AとB両方に登場する人物が現れます。今回の例でいくと、竹村さんがダブります。なのでルートAの一番下の数式だけをDeleteキーで消して9世帯にしてあるのです。
さあいよいよプリントアウトするのみです。と、その前に緑色のセルの番号…。数式に使用しているだけの数字ですから意味不明なので印刷したくないです。それでは最後に印刷範囲を設定して終わりたいと思います。
緑色のセルを除く、印刷したい範囲のセルをグイっとドラッグして選択します。そして「ページレイアウト」タブの印刷範囲→印刷範囲の設定をクリック。これで今選択した範囲外のところは印刷されません。あとは余白や色などお好みで設定してください。
本当に長々とお付き合いいただきありがとうございました。しかし次からは設定シートの黄色セルに今年度組長の番号を入力してプリントアウトするだけです。超楽ちんです。もう楽ちん極まりないです。ありがとうございました笑