質問: 表に日付と担当者を入力すると、カレンダーに色が付くようにしたいのですが... 海外で働いているものです。 周りにエクセル上級者がいない為、質問できずに困っています。 添付の図のような表を作り、タスクの情報を表とカレンダーで管理したいと考えています。 下記に具体的な内容を記します。 1.上の表にcodeと、担当者、開始日と終了日を入力する ↓↓↓ 2.カレンダーの担当者の列の該当日に色が付き、開始日にcodeが表示される 以上になります。 尚、エクセルのバージョンは2010になります。 どうぞ宜しくお願い致します。
回答: ここで説明して伝えるのは、難しく、困難だと思っているのですが、少しチャレンジしてみます。もともと、自分でも関数だけで実現出来るとは思ってなかったのですが、関数を使いまわして、それっぽいのが出来たので、自分の覚えのつもりでもあります。 1)日付の入力は整数とし、startからfinishは一ヶ月は超えず、startの日付は当月、finishの日付は(startの日付と同日~月末日の日付なら当月中)(startの日付より若い日付なら翌月の日付)とみなす。 2)同じcode番号では、担当者は1名~5名まで指定可能。6名以上をアサインする場合はcode番号に添え字をつけて対応する。(例 ana-004とana-004-2のように) 3)一度あるcode番号で担当をアポイントしたあとで、キャンセルに変更した場合は、当月中はそのcode番号と担当の指定状況は画面上残すが、翌月はリストから削除する。当月もカレンダには集計の対象としても扱わないし、担当者別の当月就業記録にも(なかったもの)として扱う。一度キャンセルにしたcode番号のcancellの文字を削除した場合は、そのcode番号の担当を割り当ては復活する。 4)結果の表(シート名result)には、担当者全員の就業と休業の記録がカレンダー上の日付で識別表示され、就業日が同じcode番号で連続する場合は、最初の日付にのみcode番号を表示する。ある就業日に複数のcode番号で就業した場合は、その件数と1つのcode番号を表示する。また結果の表(シート名result)には、当月の暦日日数、就業した当月の日数、休業登録した当月の日数を表記する。 5)当月の入力や修正の結果で、翌月に引き継ぐべきcode番号とその担当の指名状況はシート名nextmに反映させ、次月に値のコピー貼り付けの元データに出来るようにしておく。ただし、当月のスタート日付は継続させず、初日(ついたち)からスタートするような表記にする。 6)担当の名前は、別のシート(staff)にシストとして入れておく。 7)上記の処理を関数だけで行うために、入力や修正のために使うシート(ctrlpanel、staff)に作業領域を設ける他、作業用シート(presentm、nextm、pic、dayoff)を使用する。 8)関数の可読性を高めるため、名前定義をつかう。 9)対象行を抽出させるために、ctrlpanelのu~ac列に、対象行として認定するところに1,その1の個数集計(順位番号)、その行のrow位置を入れておく。そして、例えば、index(rawdata,vlookup(row()-3,nextposi,2,0),1)とか、index(nextposi,vlookup(row()-3,nextposi,2,0),11)などの関数を使用して、必要なデータを選択させている。 10)指定したstartとfinishの日付は、カレンダーのcolumnの番号と大小比較して、該当日を勤務日あるいは休暇申請日として扱う。例えば、picシートでは、pic1~pic5を縦に並べ、各pic毎にcode番号とそのpicでの担当名が入っていて(その自動反映の方法は別途記載する)、例えば、次の式 if(and(u$9>=$h905,u$9<=$i905),$e905,"") この場合9行目に月初を1としての日数が入っていて、h905にstart、i905にfinishがあれば、e905の担当者名を入れるようにしている。 11)picシートでは、10)のようにpic1~5で、code番号×日付の交差セルに担当者を入れたものを、担当者別×日付の交差セルに、例えば、=if($e15="","",if(countifs(u$100:u$2090,$e15)=0,"",if(countifs(u$100:u$2090,$e15)>1,countifs(u$100:u$2090,$e15)&":"&index($a$100:$a$2090,match($e15,u$100:u$2090,0)),index($a$100:$a$2090,match($e15,u$100:u$2090,0))))) のような式で、担当にとってその日付で就業するcode番号index($a$100:$a$2090,match($e15,u$100:u$2090,0))を表記させる。その場合、同じ日付で複数のcode番号があれば、その件数countifs(u$100:u$2090,$e15)を先頭に付して表記させる。 12)結果の表(カレンダー表示)では、条件付き書式で、セルの背景を色分けをする際、ヒダに位置するセルと同じ文字列になっているセルの文字はセルの背景色と同じにすることで、見た目のわずらわしさを回避している。また結果の表(カレンダー表示)では、翌月の日付に該当する列の背景色も変更している。当月の個人別稼働日数や休暇申請日数、なんの指定でもない日数は、countifs(offset($g5,0,0,1,$a$2),$a5)のように、$a$2の当月の月末日付=day(eomonth(a1,0))とoffset関数で集計対象を可変にして集計している。