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

質問

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日まで入力した分です。

回答No.7

某HN クロメート(Chromate)（@CoalTar）

回答欄の添付図から そのセルは何を指しているかを読み取れないのでしょうか？
まずは、回答者と同じように作成してできる・できないを判断し、
意図するものなのか違うかを検証してもらいたいです。

＞K3～AH35をアクティブにする→
K5:AH35が対象範囲ということなら、K5セルがアクティブセルとしてK5:AH35セル範囲を選択します
ちなみにアクティブセルは1つしか存在しないです。

＞条件付き書式→新しいルール→
＞数式を使用して→入力欄に数式を入力
＞($AJ3+7=K$1)→
＞書式の塗りつぶしの色を選択→OK
バージョンは2007か2010なのでしょう。応用ができないなら、先に提示してもらいたかったです。
($AJ3+7=K$1) ではなく
↓
=$AJ3+7=K$1 さらに、日付は3行目から、「出」は5行目から書かれているということなので
↓
=$AJ5+7=K$3
>>最終出勤日+7が日と同じだったら色を塗る
説明が分からなかったのでしょうか？
「日」は 1,2,3と(シリアル値ではなく)1～31までの整数と考えています

回答No.6

某HN クロメート(Chromate)（@CoalTar）

No.5より
>最後にD3～AH35をアクティブにし、
>=$AJ3+7=K$1を条件付き書式に登録しました

当方の説明
>>この表では最初の7日間は塗りつぶしがないので
>>K3:AH35セル範囲をK3セルから選択して、条件付き書式
>>数式が =$AJ3+7=K$1
>>最終出勤日+7が日と同じだったら色を塗る

設定するセル範囲が違います。

D3:AH35セル範囲に設定したいなら、
D3:AH35セル範囲をD3セルから選択して、条件付き書式
数式が =$AJ3+7=D$1

お礼

お礼を欄お借りしてもう一つ細くです。

> =IF(E36<>"","勤",IF(COUNTIF(D5:D35,"出")<>ROWS(D5:D35),"勤",""))
D36に入力し、AH36までオートフィル

> =COUNTIF($D$36:$AH$36,"勤")
AJ36に入力

> =MATCH("ー",$D5:INDEX($D5:$AH5,,AJ$36))
AJ5に入力し、AJ35までオートフィル

> =$AJ5+7=K$1
条件付き書式に入力

このような状況です。

補足

おはようございます。

ごめんなさい、間違いました。
K3～AH35を選択範囲にしていましたが、
それでも塗りつぶされませんでした。
条件付き書式の設定の流れの確認ですが、

K3～AH35をアクティブにする→
条件付き書式→新しいルール→
数式を使用して→入力欄に数式を入力
($AJ3+7=K$1)→
書式の塗りつぶしの色を選択→OK

で間違いないでしょうか？

ちなみにこちらのファイルでは「出」を打つ範囲が
D5～AH35なのでそれに応じて数式を全て

=IF(E36<>"","勤",IF(COUNTIF(D5:D35,"出")<>ROWS(D5:D35),"勤",""))

=COUNTIF($D$36:$AH$36,"勤")

=MATCH("ー",$D5:INDEX($D5:$AH5,,AJ$36))

=$AJ5+7=K$1

に変更していたのですが、これも間違いないでしょうか？
使ったのはこの数式４つなのですが足りない数式はありますか？

回答No.5

某HN クロメート(Chromate)（@CoalTar）

No.2、No.3です
>B5の作業員の最終出勤が1日だった場合k5の8日が塗りつぶされる。
>B6の作業員の最終出勤が3日だった場合m6の10日が塗りつぶされるというものです。
そのためには、
Ａ. チェックした日(空白セルのある日の最後)
Ｂ. Ａ.から導き出した範囲内におけるその人の最終出勤日
がわかる必要があります。
よって、回答No.２では Ａ.を求めるために
D36セルに
=IF(E36<>"","勤",IF(COUNTIF(D3:D35,"出")<>ROWS(D3:D35),"勤",""))
右へオートフィル としてみました。

しかし、文字通り、空白セルのある日を探すなら
=IF(E36<>"","勤",IF(COUNTBLANK(D3:D35),"勤",""))
右へオートフィルです。
小の月の31日などに「出」を入れない仕様なら
=IF(D1="","",IF(E36<>"","勤",IF(COUNTBLANK(D3:D35),"勤","")))
としておくとよいと思います。
AI36セルは 空白セルのままにしておいてください。

回答No.3ではB.を求めています
AJ36セルに
=COUNTIF($D$36:$AH$36,"勤")
AJ3セルに
=MATCH("ー",$D3:INDEX($D3:$AH3,,AJ$36))
下へオートフィル としています。

$D3:INDEX($D3:$AH3,,AJ$36)
はセル範囲を表し、仮に「勤」の数が2だったら、D3:E3セル範囲となります
MATCH関数でその範囲内での何かが書いてある日を最終にしています。

「出」以外の文字が入るなら「出」が入っているセルの最終日(最大値)を探す必要があります
=MAX(($D3:INDEX($D3:$AH3,,AJ$36)="出")*($D$1:INDEX($D$1:$AH$1,,AJ$36)))
Ａ.の範囲までの各人の配列と日を掛けて、「出」の入っている最終日を出しています。
セル上で配列を認識させるために[Ctrl]+[Shft] ＋[Enter] で確定します。
この数式を直に条件付き書式に組み込めば、[Ctrl]+[Shft] ＋[Enter]は不要ですが。

これで、必要なデータA、B.がそろいました。
あとは、塗りつぶしです。この表では最初の7日間は塗りつぶしがないので
K3:AH35セル範囲をK3セルから選択して、条件付き書式
数式が =$AJ3+7=K$1
最終出勤日+7が日と同じだったら色を塗る

コピペだけで終わりにせず、ヘルプなどで各関数の意味を把握してください。
ヘルプ以上の高度な使い方もしていますが、、、

補足

返信遅くなってしまい申し訳ございません。
出勤して朝から説明を見て試してみたのですが、
印を付ける事ができません。
何か抜けているのか
どこが間違っているのか教えて欲しいです。

> D36セルに
> =IF(E36<>"","勤",IF(COUNTIF(D3:D35,"出")<>ROWS(D3:D35),"勤",""))
> 右へオートフィル

完了し、チェックした日まで勤を表示することができました。

> AJ36セルに
> =COUNTIF($D$36:$AH$36,"勤")

完了し、AJ36にチェックした日付を表示できました。

> =MATCH("ー",$D3:INDEX($D3:$AH3,,AJ$36))
> 下へオートフィル

完了し、個人の最終出勤日を表示できました。

「出」以外の文字は入力しないので

>「出」以外の文字が入るなら「出」が入っているセルの最終日(最大値)を探す必要があります
> =MAX(($D3:INDEX($D3:$AH3,,AJ$36)="出")*($D$1:INDEX($D$1:$AH$1,,AJ$36)))
> Ａ.の範囲までの各人の配列と日を掛けて、「出」の入っている最終日を出しています。
> セル上で配列を認識させるために[Ctrl]+[Shft] ＋[Enter] で確定します。
> この数式を直に条件付き書式に組み込めば、[Ctrl]+[Shft] ＋[Enter]は不要ですが。

は飛ばしました。

最後にD3～AH35をアクティブにし、
=$AJ3+7=K$1を条件付き書式に登録しました。

回答No.4

kagakusuki

>ルール上全員が出勤する事はないので、空白セルのある列が最後の出勤日とする、

　その様なルールとなっているのですね。それでしたら、「一番最後に出勤した日」を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月乗務割表」と表示されます）

　以上で準備は完了で、後は「出」を消していくだけで、各従業員の行の「一番最後に出勤した日」や、日付の行の「一番最後に出勤の有無をチェックした日」のセルが設定した色で塗りつぶされます。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

No2です
＞数式は条件付き書式→新しいルール→数式を使用して
＞の所に入力し、書式の塗りつぶしをして印にしていました。
条件付き書式を設定したときのアクティブセルがわかりません
度のセルに設定したのでしょうか？

＞ここから「勤」のついた列の「出」（無い場合は直近の出）
＞から１週間後に印をつけるにはどうしたら良いのでしょうか？
1週間後は7足せばよいってことかな
作業列(仮でAJ列)
AJ36セルに
=COUNTIF($D$36:$AH$36,"勤")

直近の「出」は 入力が「出」しかないなら
AJ3セルに
=MATCH("ー",$D3:INDEX($D3:$AH3,,AJ$36))
下へオートフィル

直近の「出」を探すなら 配列数式で
=MAX(($D3:INDEX($D3:$AH3,,AJ$36)="出")*($D$1:INDEX($D$1:$AH$1,,AJ$36)))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる

お礼

お礼欄をお借りして補足します。
念のため私が目的としている機能が伝わっているのか確認です。
B5の作業員の最終出勤が1日だった場合k5の8日が塗りつぶされる。
B6の作業員の最終出勤が3日だった場合m6の10日が塗りつぶされるというものです。

補足

> 条件付き書式を設定したときのアクティブセルがわかりません
> 度のセルに設定したのでしょうか？

すみません少し数式が間違っていたかもしれませんが
条件付き書式の入力欄に記入してしまってるため
確認しようとすると数式がぐちゃっとなってしまい
確認できませんでした。
アクティブセルはD5～AH35の範囲でした。

これで従業員31人分の印が動いていました。

> 作業列(仮でAJ列)
> AJ36セルに
> =COUNTIF($D$36:$AH$36,"勤")

> 直近の「出」は 入力が「出」しかないなら
> AJ3セルに
> =MATCH("ー",$D3:INDEX($D3:$AH3,,AJ$36))
> 下へオートフィル

> 直近の「出」を探すなら 配列数式で
> =MAX(($D3:INDEX($D3:$AH3,,AJ$36)="出")*($D$1:INDEX($D$1:$AH$1,,AJ$36)))
> [Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる

試してみたのですがどのように利用すれば良いのか
Excel初心者なため理解できませんでした。
条件付き書式に１つの数式をいれて従業員31人分に
印を付ける事は可能ですか？

回答No.2

某HN クロメート(Chromate)（@CoalTar）

＞前回教えて頂いた関数↓
＞　=AND(COLUMN(A1)-7=MATCH("ー",$D5:D5),OFFSET(D5,0,-7)="公")
「前回」が探せませんでした。どこに上記の数式(関数ではない)を入力するのでしょうか？

D36セルに
=IF(E36<>"","勤",IF(COUNTIF(D3:D35,"出")<>ROWS(D3:D35),"勤",""))
AH36まで右へオートフィル としておけば、出勤する必要のあった日が分かるではないでしょうか？

お礼

お礼欄をお借りして補足します。
念のため私が目的としている機能が伝わっているのか確認です。
B5の作業員の最終出勤が1日だった場合k5の8日が塗りつぶされる。
B6の作業員の最終出勤が3日だった場合m6の10日が塗りつぶされるというものです。

補足

関数ではなく数式というのですね。ありがとうございます。
数式は条件付き書式→新しいルール→数式を使用して
の所に入力し、書式の塗りつぶしをして印にしていました。

> D36セルに
> =IF(E36<>"","勤",IF(COUNTIF(D5:D35,"出")<>ROWS(D5:D35),"勤",""))
> AH36まで右へオートフィル

これで最終出勤日が何日なのかわかるという事ですね！
ありがとうございます。
ここから「勤」のついた列の「出」（無い場合は直近の出）
から１週間後に印をつけるにはどうしたら良いのでしょうか？

回答No.1

kagakusuki

確認したいのですが、

>D5～AH35内の全てのセルに出勤の「出」の字を打ち、休んだ都度「出」を消していき、シートの右には「出」の数、公休(空白セル)の数を表示するというシステムになっています。

>一番最後に出勤した日(最初から全てのセルに「出」を入力しているので一番右の出ではなく、空白セルの左右どちらかの「出」が最後の出勤日になります)

との事ですが、「一番最後に出勤した日」に、全員が出勤していた場合には、「空白セルがある最後の列」と、「『空白セルがある最後の列』の右隣の列」のどちらの列が、「一番最後に出勤した日」であるのかを、一体どの様にして区別すれば良いと考えておられるのでしょうか？
　何処かのセルに、何日までチェックしたのかを明記しているのでもなければ、その様な区別をする事は不可能だと思われますので、

>D5～AH35内の全てのセルに出勤の「出」の字を打ち、休んだ都度「出」を消していき

という方式を改められる事を御勧め致します。

お礼

もう一つ補足したいことがあったのでお礼の記入欄をお借りして
補足させて頂きます。
空白セルのある列を最後の出勤日とし、
その日に出勤してない場合は
その列から一番近い左の「出」を最後の出勤日にしたいと
思っていますが、このようなことは可能なのでしょうか？

補足

失礼致しました、どうして良いのかわからず投げやりになってしまいました。

ルール上全員が出勤する事はないので、空白セルのある列が最後の出勤日とする、
もしくは何日までチェックしたのかを明記することにより
目的としている機能は実現可能なのでしょうか？

最初に全てのセルに「出」を入力するのは、
出勤日数の調整のため、できればこのシステムでやりたいと思っていました。