締切済み

Excelの関数・マクロについて教えて下さい。

2013/02/01 10:38

Excelの関数・マクロについて教えて下さい。公休一覧表を作り、加えたい機能があったので前回質問させて頂き、解決することができました。しかし公休一覧表ではなく乗務割表に変更して欲しいとの指示を受けたため作りなおしました。そのため教えていただいた関数ではできなくなってしまいまた質問を立てさせて頂きました。現在のファイルはD3～AH3までが1～31までの日付、 B5～B35までが従業員名、D5～AH35内の全てのセルに出勤の「出」の字を打ち、休んだ都度「出」を消していき、シートの右には「出」の数、公休(空白セル)の数を表示するというシステムになっています。そこで教えて頂きたい加えたい機能なのですが、一番最後に出勤した日(最初から全てのセルに「出」を入力しているので一番右の出ではなく、空白セルの左右どちらかの「出」が最後の出勤日になります)から 1週間後のセル(D5～AH35内)に自動で印（塗りつぶしなど）を付けるという機能です。 14日が最後の出勤日であれば21日に印がつき、 15日が最後の出勤日になれば前日の印が消え 22日に印を付けたいのですが可能でしょうか？前回はD5～AH35内の空白セルに休む都度公休の「公」の字を打つという物だったので単純に一番右の「公」から７列右に印を付けていけば良かったのですが、今回は最初からD5～AH35内の全てのセルに「出」を入力しているので同じようにはできません。前回教えて頂いた関数↓ 　=AND(COLUMN(A1)-7=MATCH("ー",$D5:D5),OFFSET(D5,0,-7)="公") 関数やマクロその他も含め、何か手があれば御教授願います。小さくて見えにくいかと思いますが添付画像は14日まで入力した分です。

musi69
お礼率36% (4/11)

オフィス系ソフト
回答数7
ありがとう数4

kagakusukiの回答

kagakusuki
ベストアンサー率51% (2610/5101)

2013/02/02 06:18 回答No.4

>ルール上全員が出勤する事はないので、空白セルのある列が最後の出勤日とする、　その様なルールとなっているのですね。それでしたら、「一番最後に出勤した日」をExcelに自動判定させる事も可能です。　只、私が検討しました処、結構複雑な問題になる事が判りました。　何故なら、31日が無い2月、4月、6月、9月、11月には、AH列に「出」と入れたりはしないと思われますから、「AH列までの間に空欄が無い期間」を「未だチェックしていない期間」とする訳には行きませんし、「未だチェックしていない期間」が決まらなければ、「最後の出勤日」を求める事は出来ないからです。　他にも、配置換えや退社で、月の途中で乗務しなくなる従業員（下の添付画像の例ではCさん）がいる場合には、右隣の列が全て「出」となっているか否かで、「チェック済みの日」であるか否かを、Excelに自動で判断させる事も簡単ではなくなります。　そこで、適当なセルに、その乗務割表が何年何月の月用の表であるのかを示す、年月のデータを入力しておき、小の月の場合には、関数を使って存在しない日を（3～4行目には）表示しない様にする事で、月末の日付の列が何列であるのかを判定し易くし、「月の途中で乗務しなくなる従業員」の行のセルに関しては、業務から外れた以降の日には「出」ではなく、「-」（半角ハイフォン）を入力する事で、「出勤日」、「休んだ日」、「公休日」の何れでもない事を表す様にする事にします。　尚、ここでは「その乗務割表が何年何月の月用の表であるのかを示す、年月のデータ」をB1セルに入力する事とします。　まず、B1セルの書式設定の表示形式を[ユーザー定義]の yyyy"年"m"月乗務割表" として下さい。　尚、おそらくはB1セルだけでは、セルの横幅が不足して、内容を全て表示させる事は出来ないと思いますので、B1セルと、その右側にある数個のセル（例えばC1セル～J1セル等）を結合して、（B1～J1のセル範囲を）まとめて1個のセルにして下さい。　次に、A列において、B列に従業員の名前がある行のA列のセルにのみ連番を振って下さい。　この連番が入力されているセルの個数によって、従業員の人数をExcelに判断させますので、従業員の名前が無い行のA列のセルには数値や日時を入力しない様にして下さい。（文字列は入力しても構いません）　次に、以下の操作を行って、D3セルに条件付き書式を設定して下さい。【Excel2007よりも前のバージョンの場合】 D3セルを選択　　↓ メニューの[書式]ボタンをクリック　　↓ 現れた選択肢の中にある[条件付き書式]をクリック　　↓ 現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック　　↓ 現れた選択肢の中にある「数式が」をクリック　　↓ 「条件付き書式の設定」ダイアログボックスの左から2番目の欄に =COLUMNS($D:D)=MATCH(9E+99,INDIRECT("R"&MATCH("出勤者数",$A:$A,0)&"C"&COLUMN($D$3)&":C"&COLUMN($AH$3),FALSE)) と入力　　↓ 「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック　　↓ 現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック　　↓ 現れた色のサンプルの中にあるセルを塗りつぶしたい色（下の添付画像の例では黄色）の四角形をクリック　　↓ 「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック　　↓ 「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック【Excel2007以降のバージョンの場合】 Excelウィンドウの[ホーム]タブをクリック　　↓ D3セルを選択　　↓ 選択されているセル範囲を変えないまま、「スタイル」グループの中にある[条件付き書式]ボタンをクリック　　↓ 現れた選択肢の中にある[新しいルール]をクリック　　↓ 現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック　　↓ 現れた「次の数式を満たす場合に値を書式設定」欄の中に =COLUMNS($D:D)=MATCH(9E+99,INDIRECT("R"&MATCH("出勤者数",$A:$A,0)&"C"&COLUMN($D$3)&":C"&COLUMN($AH$3),FALSE)) と入力　　↓ 「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック　　↓ 現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック　　↓ 現れた色のサンプルの中にあるセルを塗りつぶしたい色（下の添付画像の例では黄色）の四角形をクリック　　↓ 「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック　　↓ 「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック　次に、D3セルをコピーして、D12セルに貼り付けて下さい。　次に、D3セルに、次の関数を入力して下さい。 =IF(ISNUMBER((TEXT(SUBSTITUTE($B$1,"乗務割表",)+0,"yyyy/m")&"/"&COLUMNS($D:D))/DAY($B$1)),TEXT(SUBSTITUTE($B$1,"乗務割表",)+0,"yyyy/m")+COLUMNS($D:D)-1,"-") 　次に、D3セルをコピーして、D4セルに貼り付けて下さい。　次に、D3セルの書式設定の表示形式を[ユーザー定義]の d として下さい。　次に、D4セルの書式設定の表示形式を[ユーザー定義]の aaa として下さい。　次に、D3～D4の範囲をコピーして、E3～AH4の範囲に貼り付けて下さい。　次に、従業員の人数が例えば6名だった場合には、A12セルに出勤者数と入力して下さい。（何故A12セルかと言いますと、従業員の名前がB5セルからB10セルに入力されているので、1行あけた12行目に入力する）　次に、D12セルに、次の関数を入力して下さい。 =IF(AND(ISNUMBER(D$3),SUMPRODUCT(COUNTIF(INDEX($5:$5,COLUMN()):INDEX($D:$AH,ROW()-1,COLUMNS($D:D)),{"出","-","公"})*1)<COUNT($A:$A)),COUNTIF(INDEX($5:$5,COLUMN()):INDEX($D:$AH,ROW()-1,COLUMNS($D:D)),"出"),"") 　次に、D12セルをコピーして、E12～AH12の範囲に貼り付けて下さい。　次に、AJ5セルに、次の関数を入力して下さい。 =IF(INDEX($B:$B,ROW())="","",COUNTIF(INDEX($D:$D,ROW()):INDEX($D:$AH,ROW(),MATCH(9E+99,INDEX($D:$D,MATCH("出勤者数",$A:$A,0)):INDEX(AH:AH,MATCH("出勤者数",$A:$A,0)))),"出")) 　次に、AJ12セルに、次の関数を入力して下さい。 =SUM($AJ$5:INDEX($AJ:$AJ,ROW()-1)) 　次に、D5セルに出と入力して下さい。　次に、D3に条件付き書式を設定したのと同様の操作をD5セルに対して行って、D5セルに条件付き書式を設定して下さい。　但し、条件付き書式の条件として設定する数式は =AND(D5="出",COUNTIF(INDIRECT("RC:RC["&MATCH(9E+99,$D$12:$AH$12)-COLUMNS($D:D)&"]",FALSE),"出")=1,COLUMNS($D:D)<=MATCH(9E+99,$D$12:$AH$12)) として下さい。（出勤者数欄が12行目の場合）　次に、D5セルをコピーして、D5～AH5の範囲をコピーして、D列～AH列の「B列に従業員の名前が入力されている全ての行」に貼り付けて下さい。　最後に、「その乗務割表が何年何月の月用の表であるのかを示す、年月のデータ」をB1セルに入力して下さい。　例えば、2013年2月の表とする場合には、B1セルに 2013/2 などと入力して下さい。（それだけで「2013年2月乗務割表」と表示されます）　以上で準備は完了で、後は「出」を消していくだけで、各従業員の行の「一番最後に出勤した日」や、日付の行の「一番最後に出勤の有無をチェックした日」のセルが設定した色で塗りつぶされます。