ああ、、、非正規社員にとって恐怖の、、、、、、あの大型連休が、、、、、
ゴールデンウィークがついにやってきてしまいますね。
全米は泣かないけど非正規社員は全員泣いてるはず（苦笑）
とりあえず！今のうちに、やれるだけガンガン仕事して、収入を蓄えておかないと、、、、連休あとの給料日が怖い！！（笑）
ってことで、、、ただいま休みも睡眠時間もゴリゴリ削って対策中っす！！
こんな状況なのに、なんでこんなもの？！書いているのか？といいますと、
実は、前回書いた、「動的にシートを指定する」記事に説明不足があることにふと気が付きまして、、、
直接過去記事に補足説明を追加しようかなと、思っていたのですが、いや、どうせ書くなら、実務で使えるやつを作りたいっ！！
それでも、さすがにゼロから新しいものを作る余裕は無いので、過去記事の「関数だけで「２４時間対応」の勤務時間表をつくってみる」がちょうどいい題材なので、この記事で書いた表を使って、前回記事の「セルの値によって参照するシートを変化させる方法」の補足説明をしたいなと思います。
では、早速。。。。
以前つくった24時間対応の勤務時間表を下のように準備します。
このように、２名分、２ヶ月ずつの勤務実績ダミーデータを入れたシートを準備しまして、１枚だけデータ無しで新しい年月のシートを準備しました。
もし、試しに同じように作ってみたいという方は、連動して参照先シートが変わった時に、変わったことがすぐわかるような、ダミーデータにしておくとデバッグが楽でいいと思います。
そうしましたら、勤務実績データの入っていない、新しい年月のシートを使って次のような動きをさせます。
使うメインの関数はINDEX＋MATCHの縦横検索です。この関数の組み合わせの中で、セル、配列の参照する場所がありますが、そこでINDIRECTを使ってシートを動的に指定します。
ここのセルD6に入れる関数式はこうなります。見やすいようにインデントつけてみます。このインデントがついたまま、Excelのセルにコピペしても動きます。
=INDEX(
INDIRECT(TEXT(DATE(YEAR($A$1),MONTH($A$1),0),"'yyyy年m月") & " " & $F$1 & "!A:F"),
MATCH($B6,INDIRECT(TEXT(DATE(YEAR($A$1),MONTH($A$1),0),"'yyyy年m月") & " " & $F$1 & "!B:B1"),0),
COLUMN()
)過去記事で使った関数しか使ってませんので、改めてそれぞれの関数の説明はしませんが、前回の記事で説明不足だった点がこの中にあります。
前回、INDIRECT関数を使って、シート名を指定するとき、'（シングルクォーテーション）で括ってませんでしたが、今回は括っています。 ADDRESS関数を使ってシート名を指定する場合は、勝手に'が入るので意識しませんが、自分で文字列をつなげてシート名を作成する場合で、「半角スペース」がシート名に入る場合は、'でシート名を括らないといけません。
「半角スペース」が入らないなら括らなくてもいいですし、「全角スペース」の場合も括らなくてもいいです。
なんで半角スペーズはダメで、全角スペースはいいの？？って言われると困りますが（笑）
えー、これは、私なりの解釈ですが、、、全角は人間界で使う言語、半角はPC界で使う言語・・・・。
みたいな感じです（笑）。
PCさんにとって、全角文字は別の意味を持たない「絵」みたいなものなのですが、半角文字は別の意味として理解できちゃうものがあるんですね。
なので、「半角スペース」として名前に使いたいときは、PCさんに「これは半角スペースです！」って説明しないといけないわけです。
PCさんは、寂しかりやなので、自分にわかるメッセージがあると、うれしくなって勝手に自分用に解釈しちゃうんです。
なので、PCさんによく話しかけてあげていると、括らなくてもいいらしい。。。。（←ウソ）
あとは、入力したセルから第１週目の実績入力欄に書式なしフィルすれば反映されるようになります。
こんな感じですね。
先月の日付が出る可能性があるのは第１週目だけですから、関数をいれておくのはここだけでいいです。
そして、参照先の先月シートには、今月の日付はありませんので、今月の日付になっているところから、エラー表示が出ています。
エラーを消す処理をしてもいいのですが、フォーマット用のシートとして、運用するならこのままの方が分かりやすいと思います。
なぜかというと、今月の実績を入力するためにシートを準備するのはおそらく、先月末から今月初めの間の月１回だけですね。
その準備は、フォーマットシートをコピーして、年月と名前を入力して、シート名を変えて、、、というように準備していくと思います。
そして、準備が整い、実際にデータを入力しようとする前に、不要な関数式を消しておきたいですが、、、月１回しか使わない上に、エラー表示も出ないだと、、、、多分、消し忘れちゃいます（笑）
さらに、こういった実績入力系は、自分だけが使う状況というのは少ないと思うので、不要な関数式を消し忘れてしまうと、
「これ消していいんですか？」とか、
「自動で実績が入らないですが・・・」とか
「関数が間違ってますよ？」とか、
いろいろ面倒なやりとりをせねばならなくなります（笑）
それは避けたいところですよね。
まあ、好みではありますので、エラーを消したい！と思われる方は、関数の大外にエラー回避の条件分岐をつけて使ってください。
さて、これで終わり・・・
とはいかなくて（笑）
このまま上の関数式を法定休日欄にフィルしてみますと・・・・
とまあ、よく見る「あるある」です（笑）
なので回避方法も、よく見る「あるある」でしょうね。
そして、いくつか方法があります。
まず、関数式でなんとかする方法。
めちゃくちゃ式が長くなるので、この方法は避けたい、、、、と、思うかもしれませんが、IF文の中に、同じ式を２ついれるだけなので、コピペで簡単につくれます。式もここだけなので、私はアリだと思います。
そして、次の方法は本当によく見るやつ。
このように「見えなくする」と「受け側の式を工夫する」の組み合わせです。
どれが一番いいですか？？ってことはなくて、どれも一緒です。
自分が理解しやすくて、使いやすいのが一番かなと思います。
最後に、おまけです。
機能として、あっても、なくてもいいものですが、もし、気に入ってもらえて、使えるPC環境なら使ってみてください。
今回はシート名にルールをつけましたが、フォルダ名やファイル名、人の名前の入力方法なんかでも、こういった名前などをつけるルールを社内や部署内で決めたとします。
でも、、、、結局、「ルールは破るためにある！」って状況になりませんか？（笑）
特に全角、半角の数字やスペースね。
半角数字と全角数字が混在しているケースもよくみたな。見つけて直すのに結構大変だったっけ（笑）
あと、氏名の間にスペースを入れるのか、入れないのか？全角なのか半角なのか？！問題もよくありますね（笑）
このようなことは、人間の目だと意味もわかるし、区別もつくので、大きな問題にならないことが多いですが、PCさんから見た場合、全角と半角は全くの別物です。
特に、今回のような、セルに入力された値から一致するシート名を探すというのは、値が一致するという前提があって、はじめて成り立つ関数管理方法です。このような管理方法にとって、入力ルールが破られることは、、、、かなりの致命傷になります。
なので、そういったことを少しでも回避できるようにするため、アラートを表示させる方法のご紹介です。
使う関数はCELL関数。
これは、セルの情報を表示する関数です。
先に使い方を書いておきます。
=CELL("情報の種類",参照先)
まず、参照先ですね。これは情報を得たいセルを指定するところです。省略もできますが、省略するとアクティブになっているセルの情報になるので、セルの選択を変えるたびにうるさく値が変化し、動作も遅くなります。なので、省略の使い方は、、、、よく考えた方がよいかと（笑）
次に、情報の種類ですが。これはたくさんありまして、書式だったり、列数、行数、色、などなど、、、、なんかVBAでオブジェクトから情報を引き出すみたいな動きで、とても便利に使えそうなのですが、、、ほとんどが他の関数でできるので、CELL関数の使い道はほぼないかと思います（笑）。残念！
そんな残念なCELL関数ですが、情報の種類の中のfilenameだけは、「シート名を得る」と検索したら絶対に出てくる、シート名を得るために生まれた救世主！唯一無二の関数なのです！（笑）
まあ、誇張しすぎて、言い過ぎに聞こえますが、、、実際、シート名を得るにはこれしかないです。シート名だけを得る方法もほぼ１つに確立されていますので、検索してみてはいかがでしょうか？
「えっ？ここで説明はしないの？？？」
ははは、、、、今回、シート名は参照しますが、抜き出しません！
では、どのように使うのかといいますと、こんな感じで使います。
そして、それぞれのセルに入っている関数式ですが、
まず、年月の下、セルA3です。
=IFERROPR(IF(FIND(TEXT($A$1,"yyyy年m月"),CELL("filename",$A$1))>0,"","想定外の結果です。管理者に連絡を！"),"注！シート名と年月が違います")そして、名前の下、セルF3です。
=IFERROPR(IF(FIND($F$1,CELL("filename",$F$1))>0,"","想定外の結果です。管理者に連絡を！"),"注！シート名と名前が違います")となっています。CELL("filename",$A$1)をするとフルパスのファイル名＋シート名が得られます。その文字列の中から年月、名前と同じ文字列があるかどうかをFIND関数で探し、ヒットすれば文字の位置が得られますので、結果０より大きくなり、異常なしで何も表示させないようにします。
そして、同じ文字列が見つからなかった場合、FIND関数は０ではなく、エラーになります。なので数式の大外にエラー判定を置いておいて、エラーなら文字列がなかったことになり、コメントをセルに表示します。
さらに、０にはならないはずなのに０になったら、、、、もう想定外です。これ出るなら是非見てみたいので管理者を呼んでもらいましょう（笑）
このようにコメントは管理者の言葉として伝えたいことを書けばよいです。フォントや色も設定すればよりよいかと思います。
このようにシート名を確認し、それによってアラートを出し、ユーザー自身に確認してもらう方法をとっています。
シート名かセルの値のどちらかがルール通りならば、ガードの意味は出ますが、どちらも同じ間違いをした場合は、ガードをすり抜けます。さすがにどっちかはルールを守っててほしいもんですけどね（笑）
さて、なぜ、このように抜け道がある不完全なアラートを出す方法にしているのかといいますと、
シート名とセルの値を一致させるだけが目的であれば、シート名からそれぞれ年月と名前を抜き出し、セルの値としてやれば簡単です。その場合は、先ほど検索してみては？と案内した方法を使って実現すればよいと思います。絶対にセルの値とシート名は一致しますからね。ですが、この方法はシート名同士の入力ルールの一致はわかりませんね。
今回一番実現したいことは、動的に別シートを参照することです。
ということは、個別のシートごとにシート名とセルの値が一致していることが大事なわけではなく、すべてのシート名がルールに沿っていることが大事なわけです。
なので、手入力するたびにアラートを出すガードを設定しておいて、ルールにあってますか？と気づいてもらう機会を増やす方法をとっているわけです。
不完全なガードなので、結局は管理者がしっかり確認するしかないのですが、管理者の代わりに微力ではありますがアラートの文面が一役かっているという感じです。なので、文面は管理する人が考えましょう。
また、このアラートと一緒に、入力ガードをつけておくのも手です。もうこれ以上長く書くとバイトがシンドくなるのでサラッといかせてもらいますが、例えば、名前のセル側に全角、半角ガードをつけるのも手ですね。こんな感じ。
=IF($F$1=ASC($F$1),"","おい！全角スペース入れただろ！")これで全角スペースが入ったらアラートでます。
ちなみに、このASCをJISにすれば全角チェックになります。
そして、年月の場合、そもそもこのシートは日付としてセルに入力してもらっているので、データが日付かどうかで判断すればいいっすね
=IFERROPR(IF(VALUE($A$1)>0,"","なんで0いれた？"),"日付で指定しろって言ったやん！")なんてガードもかけれますね（笑）
さて、今回はこれで終わりです。
何か役に立つものがあったでしょうか？あればいいのですが（笑）
それでは、ここまで読んでいただきありがとうございます。
誤字脱字はまた後日確認いたします。。。。ご勘弁を。
