エクセルで作成したカレンダーに「当番の名前」を自動的に入力する方法をお

エクセルで作成したカレンダーに「当番の名前」を自動的に入力する方法をおしえてください。


毎月エクセルで朝礼当番表を作っています。
土、日、祝がお休みです。
たとえば、1日に最初の人の名前を入力すると休みの日はぬかして、
順番に当番が入力されるという関数があれば教えてください。

1行目に「日にち」
2行目に「曜日」
3行目に「当番者名」

と簡単な表です。

リストからコピペしたら間違えてしまいました。

オートフィルで入力しようかと思ったのですが、休みの日を抜かすのが面倒で。


よろしくお願いします。

No.1 回答者： kmetu 回答日時： 2010/09/19 17:10

> オートフィルで入力しようかと思ったのですが、休みの日を抜かすのが面倒で。

どんな関数を使う(一番の候補はWORKDAY）にせよ祝祭日は自分で設定しないといけませんから、それが面倒というのですから無理です。

No.2 回答者： hallo-2007 回答日時： 2010/09/19 18:50

どれだけ手間が省けるかわかりませんが、一例です

http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/ …
に祝日の一覧がありますので　URLに従って　祝日　という名前で定義しておきます。
カレンダーのシートが
　　　A　　　　　B　　　C・・・
１　人数　　　　5
２　最初の人　　1
３　日付　　　　10/1　10/2
４　曜日　　　　金曜　　土曜・・・
５　作業列　　　０　　　１・・・
６　作業列　　　１　　　０・・・
と準備して
5行目（B5セル）に　=COUNTIF(祝日,B3)+(WEEKDAY(B3)=1)+(WEEKDAY(B3)=7)
と入れて右へコピィしえおきます。休みの日が　１　それ以外が　０　になるはずです。
更に
6行目（B6セル）に　=IF(B6=0,MOD($B2-2+DAY(B3)-SUM($B5:B5),$B1)+1,0)
と入れて右へコピィすれば　休み以外で　連続した番号が繰り返されると思いますので
後は、別のシートに当番の名前の一覧表を準備します。
　番号　　氏名
　　０　　休日
　　１　　Ａさん
　　２　　Ｂさん
・・・
7行目に　vLOOKUP関数を使って名前が表示できます。
毎月　3行目の日付と最初の人の番号を変えると後は自動ですむはずです。
がんばって式を考えれば作業列を省くことも出来ますが、とりあえずは式の意味を理解してください。
作業列が邪魔であれば非表示にしておきます。

この回答への補足

今日、会社でやってみました。
５の作業列に０、１までうまくいったのですが、
６の作業列がどうしてもうまくいきません。
２行目の最初の人→１というのは、１から始まるのなら「１」だけ入力すればいいのでしょうか？
あと、大変申し訳ないのですが、お時間がありましたら6行目の式の意味を教えていただけますでしょうか？
そうしたら、自分で治せるかもしれないので。
よろしくお願いいたします。

補足日時：2010/09/21 22:17

この回答へのお礼

どうもありがとうございます。
なるほど、番号を振るのですね。
めちゃくちゃ初級者なので、式の意味を理解できるか微妙ですが、
明日会社でやってみます。

お礼日時：2010/09/20 22:58

No.3 回答者： tom04 回答日時： 2010/09/19 22:00

こんばんは！

外していたらごめんなさい。

こういうことで良いのですかね？
↓の画像でC4セルにその月の1日の担当者を入力すると、F・G列に作成している表の順に土日・祝日を除いた日に
順番にC4セルに入力した人から繰り返して表示するようにしてみました。
尚、C5セルに入力しないのは、1日が土日・祝日の可能性があるので敢えて別セルに入力するようにしています。

準備段階として、↓の画像では右側がSheet2でそこに祝日データを作っておきます。
そして、日付に関してはシリアル値の方が都合がよいのでシリアル値にし、表示形式だけを変えています。

Sheet1の1行目に年・月度を入力するセルを設けています。

A5セル（セルの表示形式はユーザー定義から　d　としておきます）は
=IF(MONTH(DATE($A$1,$C$1,ROW(A1)))=$C$1,DATE($A$1,$C$1,ROW(A1)),"")

B5セル（セルの表示形式はユーザー定義から aaa　としておきます）は
=IF(A5="","",A5)

C5セルに
=IF(D5="","",INDEX($G$5:$G$14,IF(MOD(D5,COUNTA($G$5:$G$14))=0,COUNTA($G$5:$G$14),MOD(D5,COUNTA($G$5:$G$14)))))

作業列D４セルに
=IF(C4="","",INDEX(F5:F8,MATCH(C4,G5:G8,0)))

D5セルに
=IF(OR($C$4="",A5="",WEEKDAY(A5)=1,WEEKDAY(A5)=7,COUNTIF(祝日,A5)),"",$D$4+COUNT($D$4:D4)-1)

として最後にA5～D5セルを範囲指定し、D5セルのフィルハンドルで31日の35行目までコピーすると
画像のような感じになります。

これでF・G列の担当者の人数に変更があっても対応できると思います。
（B列のセルの色は条件付書式で設定しています。）

以上、長々と失礼しました。
参考になればよいのですが
的外れならごめんなさいね。m(__)m

この回答へのお礼

画像までつけてくださってどうもありがとうございます。
祝日は手入力で対応しようと思っていたので、
そこまで考えてくださってありがとうございます。
全然まとはずれじゃないです。
明日会社で試してみます。

お礼日時：2010/09/20 23:10

No.4 回答者： tom04 回答日時： 2010/09/19 22:49

No.3です！

たびたびごめんなさい。

前回のD5セルの数式内に
"祝日" というものが出ていますが、これは条件付書式を設定する場合は別Sheetを直接参照できませんので、
範囲を名前定義していた関係です。

この際ですので、前回のB列に色を付ける方法も投稿しておきます。

当方使用のExcel2003の場合です。
前回のSheet2のB2～C23(今年と来年分のシリアル値の祝日データです）を範囲指定　→　メニュー　→　挿入　→　名前　→　祝日　と名前定義しています。
（B2～C23を範囲指定し、直接名前ボックスに　祝日　と入力してもかまいません）

Sheet1のB5セルには前回の数式を入れて、条件付書式を設定します。
メニュー　→　書式　→　条件付書式　→　「数式が」を選択　→　数式欄に
=OR(WEEKDAY(A5=1,WEEKDAY(A5)=7,COUNTIF(祝日,A5)　として　書式　→　パターン　で「赤」を選択し、
オートフィルで下へコピーすると土日・祝日のセルが赤くなります。

結局数式を入れる前にこの名前定義を行っていたので、Sheet2のB2～C23を範囲指定すると

=IF(OR($C$4="",A5="",WEEKDAY(A5)=1,WEEKDAY(A5)=7,COUNTIF(祝日,A5)),"",$D$4+COUNT($D$4:D4)-1)

となってしまいます。
名前定義していなければD5セルは
=IF(OR($C$4="",A5="",WEEKDAY(A5)=1,WEEKDAY(A5)=7,COUNTIF(Sheet2!$B$2:$C$23,A5)),"",$D$4+COUNT($D$4:D4)-1)
という数式になります。

何度も失礼しました。m(__)m

この回答へのお礼

たびたびご丁寧にありがとうございます。

お礼日時：2010/09/20 23:20

No.5 回答者： kagakusuki 回答日時： 2010/09/20 03:30

　今仮に、Sheet1のA1セルに、その年の西暦年数、C1セルに月、F1セルに第1日目の当番者名を入力すると、A4セルから下に向かって日にちが順番に自動表示され、各日にちと同じ行のB列にはその日の曜日、C列にはその日の当番者名が自動表示されるものとします。

　又、自動表示を行う際の判定のためには、当番者のリストと、土日以外の休日のリストが必要になりますから、それらのリストをSheet2に設けるものとします。

　まず、Sheet2のB1セルに項目名として「当番者名」と入力して下さい。
　同様に項目名として、
E5セルに「休日の日にち」、
F1セルとF5セルに「夏休み」、
G1セルとG5セルに「冬休み」、
H1セルとH5セルに「春休み」、
E2セルに「開始日」
E3セルに「最終日」
と入力して下さい。
　次に、F6セルに次の数式を入力して下さい。

=F$2

　次に、F7セルに次の数式を入力して下さい。

=IF(OR(F$2="",F$3="",F6=""),"",IF(F6+1>F$3,"",F6+1))

　次に、F6～F7の範囲をコピーして、G6～H7の範囲に貼り付けて下さい。

　次に、F7～G7の範囲をコピーして、同じ列の8行目以下に、最も長い休みの日数を上回るのに充分な回数だけ貼り付けて下さい。
　次に、B2セルから下方へ向かって、各当番者の名前を、当番を行う順番に入力して下さい。
　次にE6セルから下方へ向かって、年間の祝祭日等の、土日以外の休日を

2010/1/1
2010/1/2
2010/1/3
2010/1/11
2010/2/11
　　・
　　・
　　・
　　・
　　・
2010/12/23
2010/12/31

という具合に入力して下さい。（順不同も可）
　次に、F2セルに夏休みの開始日を、
F3セルに夏休みの最終日を、
G2セルに冬休みの開始日を、
G3セルに冬休みの最終日を、
H2セルに春休みの開始日を、
H3セルに春休みの最終日を
入力して下さい。

　次にSheet1を開いて、A1セル、C1セル、F1セルを枠線で囲んで下さい。
　次に項目名として、
B1セルに「年」、
D1セルに「月」、
E1セルに「最初の当番者名：」、
A3セルに「日にち」、
B3セルに「曜日」、
C3セルに「当番者名」、
と入力して下さい。

　次に、A4セルのセルの書式設定の表示形式を、ユーザー定義の

d"日"

とし、同じくB4セルのセルの書式設定を

aaa

として下さい。
　次に、A4セルに次の数式を入力して下さい。

=IF(ROWS($3:3)>DAY(DATE($A$1,$C$1+1,)),"",DATE($A$1,$C$1,ROWS($3:3)))

　次に、B4セルに次の数式を入力して下さい。

=A4

　次に、C4セルに次の数式を入力して下さい。

=IF($A4="","",IF(OR(WEEKDAY($A4,2)>5,COUNTIF(Sheet2!$E:$H,$A4)>0),"【休日】",INDEX(Sheet2!$B:$B,MOD(MATCH($F$1,Sheet2!$B:$B,0)+COUNTIF(C$3:C3,"<>【休日】")-2,COUNTIF(Sheet2!$B:$B,"<>")-1)+2)))

　次に、A4～C4の範囲をコピーして、同じ列の5行目～34行目の範囲に貼り付けて下さい。

　そして、必ずしも必要ではありませんが、F1セルに最初の当番者名を入力する際に便利になる様に、F1セルの入力規則を「リスト」に設定して、「元の値」欄の中に入力する数式を以下の様にして下さい。

=INDIRECT("Sheet2!B2:B"&COUNTIF(INDIRECT("Sheet2!B:B"),"<>")-1)

　以上で準備は完了で、後は
Sheet1のA1セルに、その年の西暦年数、C1セルに月、F1セルに第1日目の当番者名を入力すれば、当番表が自動表示されます。
　尚、Sheet2の休日のリストは、年が変わる度に更新して下さい。

この回答へのお礼

夏休み、冬休み・・・全然考えておりませんでした。
全部自動ででてくるなんてすごいです。
どうもありがとうございます。
明日会社で試してみます。

お礼日時：2010/09/20 23:22

No.6 回答者： KURUMITO 回答日時： 2010/09/20 07:08

最初の表の作り方ですが、通常は１行目に項目名を横に並べ、それぞれのデータは下方に入力するまたは表示させることでしょう。

ここでは通常の方法で述べます。
また、表示したい月の日付は自動的に月末までを表示させるようにし、朝礼の当番については月の初めで常に当番表の１番が来るとは限りませんのでそれらを考慮した表を作ることにします。
シート１にお求めの表を作るとしてシート２にはシート１を完成するために必要なデータベースを入力することにします。
シート２のＡ列には祝祭日を入力します。
Ａ１セルに祝祭日と入力して下方にはその年の日付を例えば2010/1/11 2010/2/11 などと行を変えて入力します。
Ｃ１セルには当番者名などと入力し下方には氏名を入力します。
次にシート１ですが、Ａ１セルには例えば今年の９月のカレンダーを作成するのでしたら2010/9/1と入力します。その後にセルを右クリックして「セルの書式設定」から「表示形式」のタブで「ユーザー定義」を選び種類の窓には　m"月" と入力します。これでセルの表示は９月と表示されます。
次にＡ２セルには「日」、Ｂ２セルには「曜日」、Ｃ２セルには「当番者名」と入力します。
Ａ３セルには次の式を入力して下方にオートフィルドラッグします。

=IF(MONTH(A$1+ROW(A1)-1)<>MONTH(A$1),"",A$1+ROW(A1)-1)

その後にそれらのセル範囲を選択してから上記と同様に「表示形式」の「ユーザー定義」で種類の窓には　d と入力します。数値だけが表示されます。
なお、この式では月が変わってもその最終日までが自動的に表示されます。
次にＢ３セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A3="","",TEXT(A3,"aaa"))

これでＢ列には曜日が表示されます。
次にＣ１セルにはシート２で当番者名が並んでいますが上から何番目の方が今月初めの当番になるかを数値で入力します。１番目の方から当番を開始するのであれば１と入力します。Ｄ１セルには「番から」とでも入力します。
Ｃ２セルには当番者名と入力します。
Ｃ３セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(A3="",WEEKDAY(A3,2)>=6,COUNTIF(Sheet2!A:A,A3)>0),"",INDEX(Sheet2!C$2:C$100,MOD(COUNTIF(C$2:C2,"?*")+C$1-2,COUNTA(Sheet2!C$2:C$100))+1))

一度是非試験してみてください。

この回答への補足

今日会社でやってみました。
が、ごめんなさい。説明不足でした。

　　　　　　　　　Ａ、Ｂ、Ｃ・・・
1行目→日にちは横へ１，２，３，４、
2行目→曜日、
3行目→担当者

なのです。
うまくいかなかったので、ＲＯＷ関数をしらべてみたら行の値を返すとかいてあったので、
ＣＯＵＮＴＩ？？？というのに変えてみましたが、だめでした。
そこで力つきました・・・・

補足日時：2010/09/21 22:22

この回答へのお礼

どうもありがとうございます。
初心者なもので、そういった関数でもないかと思い、かる～く質問させていただいたのですが、
いろいろ組み合わせないとならないのですね。
みなさんの回答を理解するまで時間がかかりそうですが、
いろいろ試してみます。
ありがとうございました。

お礼日時：2010/09/20 23:25

No.7 回答者： KURUMITO 回答日時： 2010/09/22 07:29

例えば今年の9月のカレンダーを作るのならシート1のＡ1セルに2010/9/1と入力します。

その後にセルを右クリックして「セルの書式設定」から「表示形式」のタブで分類の「ユーザー定義」を選択し、種類の窓には　m"月" と入力してＯＫします。これでＡ1セルには9月と表示されます。
次にＡ2セルには日付と入力し、Ｂ2セルには次の式を入力してＡＦ2セルまでオートフィルドラッグします。

=IF(MONTH($A$1+COLUMN(A1)-1)<>MONTH($A$1),"",$A$1+COLUMN(A1)-1)

これで数値が表示されますがＢ2セルからＡＦ2セルを選択して右クリックし、先と同様にして「表示形式」のタブの「ユーザー定義」で　d"日" と入力してＯＫします。それによってその月の最終日までが日が付いて表示されます。
次に曜日ですがＡ3セルには曜日と入力し、Ｂ3セルには次の式を入力してＡＦ3セルまでオートフィルドラッグします。

=IF(B2="","",TEXT(B2,"aaa"))

4行目には当番者名を表示させるわけですが、その前に祝祭日や当番者名の表を準備することが必要です。
シート2にそれらのデータを準備することにします。
シート2のＡ1セルには祝祭日と入力し、Ａ２セルから下方にはその年の祝祭日を例えば2010年の1月1日でしたら2010/1/1のように入力します。9月でしたら2010/9/20や2010/9/23なども含まれますね。
また、Ｃ1セルには当番者名とでも入力しＣ2セルから下方に当番者の名前を入力します。
以上でシート2での準備作業は終了です。

なお、シート1では月の初めに必ずシート2の当番者名の最初の人（Ｃ2セル）の名前が来るとは限りませんので月の初めに最初に当番になる人の名前をシート2のＣ2セルから下方に何番目に有るかを調べ、シート1のＣ1セルに入力することにします。最初の人から始まるのでしたら1と入力します。Ｄ1セルには番目からとでも入力します。
その後にＡ4セルには当番者名と入力し、Ｂ4セルには次の式を入力してＡＦ4セルまでオートフィルドラッグします。

=IF(OR($C$1="",B2=""),"",IF(OR(WEEKDAY(B2,2)>=6,COUNTIF(Sheet2!$A:$A,B2)>0),"",INDEX(Sheet2!$C$2:$C$100,MOD(COUNTIF($A4:A4,"?*")+$C$1-2,COUNTA(Sheet2!$C$2:$C$100))+1)))

なお、この式では必ずＡ4セルには当番者名などの文字列が,また、Ｃ1セルには数値が入力されていることが必要です。

丁寧に説明したつもりです。成功するよう願っています。

この回答へのお礼

何度もどうもありがとうございました。
今回は時間がなかったので、他の方の方法を使わせていただきましたが、
他にも似たような表が必要なのでそちらに応用させていただきます。
どうもありがとうございました。

お礼日時：2010/09/28 23:39

No.8 回答者： tom04 回答日時： 2010/09/22 20:05

No.3・4です！

No.6さんの補足を読ませていただいて・・・
前回は大きな勘違いをしていました。列方向に日付があるわけですよね？
もう一度画像をアップしてみます。
Sheet2に祝日（B2～C23セル）に表を作っています。会社独自の休日があればこのデータに付け加えておいても構いません。
このB2～C23セルを範囲指定し、「祝日」と名前定義しています。

そして前回同様、その月の1日目が土日・祝日の場合は1日目のセルに入力できないので、Sheet1のB1～B4セルを予備の列としています。

B3セルは
=IF(A8="","",A8)

B4セルは
=IF(B3="","",INDEX(A11:A20,MATCH(B3,B11:B20)))

C1セル（セルの書式設定から表示形式はユーザー定義で　d　としておきます。）
=IF(MONTH(DATE($A$6,$A$7,COLUMN(A1)))=$A$7,DATE($A$6,$A$7,COLUMN(A1)),"")

C2セル（表示形式はユーザー定義から　aaa　として、
条件付書式から　数式が　を選択、数式欄に=OR(WEEKDAY(C1)=1,WEEKDAY(C1)=7,COUNTIF(祝日,C1))　として書式からパターンで「赤」を選択
数式は
=IF(C1="","",C1)

C4セルの数式は
=IF(C1="","",IF(OR($B$4="",WEEKDAY(C1)=1,WEEKDAY(C1)=7,COUNTIF(祝日,C1)),"",$B$4+COUNT($B$4:B4)-1))

最後にC1～C4セルを範囲指定し、列（右）方向に31日までのAG列までオートフィルでコピーします。

これでSheet1のA6～A8に年・月・その月の最初の担当者を入力すれば
日付・曜日・担当者が順番に表示されると思います。
尚、このカレンダーは年と月を入れ替えるたびに何度でも利用できます。
担当者は10人まで対応できるようにしていますが、担当者の人数によって範囲指定の領域はアレンジしてみてください。
（10人以下ならこのままの数式でも構いません）

以上、長々と失礼しました。m(__)m

No.9 回答者： tom04 回答日時： 2010/09/23 09:58

No.8です！

たびたびお邪魔します。

前回の投稿でC3セルの数式を載せていませんでした。

表は前回そのままでC3セルの数式を

=IF(C4="","",INDEX($B$11:$B$20,IF(MOD(C4,COUNTA($B$11:$B$20))=0,COUNTA($B$11:$B$20),MOD(C4,COUNTA($B$11:$B$20)))))

としてください。

何度もごめんなさいね。m(__)m

この回答へのお礼

何度もどうもありがとうございました。
今回は時間がなかったので、他の方の方法を使わせていただきましたが、
他にも似たような表が必要なのでそちらに応用させていただきます。
どうもありがとうございました。

お礼日時：2010/09/28 23:38

No.10 ベストアンサー 回答者： hallo-2007 回答日時： 2010/09/23 14:52

>6行目（B6セル）に　=IF(B6=0,MOD($B2-2+DAY(B3)-SUM($B5:B5),$B1)+1,0)

式を分解してみるとわかりやすいです。
更に
7行目に作業列　=$B2-2+DAY(B3)
　　　　日にちごとに　日にち事に連続した番号になります。
8行目に作業列　=MOD($B2-2+DAY(B3),$B1)
　　　　その番号を　人数で割ったあまりがでます。
9行目に作業列　=SUM($B5:B5)
　　　　休みの数の合計がでます。

と入れて右へコピィしてみてください。
式のセルを指定する　$B2　とか$マークが付く場合と付かない場合がありますよね。
絶対参照と呼びますが、意味は右へコピィしてもセルの位置を変動させないということです。
例えば
9行目の=SUM($B5:B5)の式を右へコピィした場合
=SUM($B5:B5)
=SUM($B5:C5)
=SUM($B5:D5)
・・・
と合計する範囲が広くなっていくように設定してあります。

別件ですが
カレンダーの日付をコピィの作業をしなくても良いように関数をいれておくことも出来ます。
　　　A　　　　　B　　　C　　　D・・・
１　人数　　　　5　　　年　　2010
２　最初の人　　1　　　月　　　1
３　日付　　　　10/1　10/2
４　曜日　　　　金曜　　土曜・・・
とD1セルに　年　の数値　D2セルに　月　を数値で入れます。
日付のB2セルには　=DATE($D1,$D2,COLUMN(A1))　と入れて右へコピィしておきます
ここも　年と月を決める　D1とD2のセルを指定するときは右へコピィしても変動しないように
＄マークをつけておきます。
COLUMN(A1)はA1セルの列の番号です。右へコピィした場合に
COLUMN(B1)
COLUMN(C1)　と変動する様に　＄マークは付けません。
＊COLUMN(A1)は　COLUMN(A2)でもACOLUMN(3)でもかまいません。
曜日のB3セルには　=B2 と入れます。書式＝＞セル　で表示形式のタブ　ユーザ定義　で　aaa
と入れると　その日の表示が曜日になります。
B3セルも右へコピィします。
毎月、月の部分を変更するだけで　その月のカレンダーになります。
表示形式については
http://www.excel.studio-kazu.jp/lib/e3g/e3g.html
などを参考にしてください。

この回答へのお礼

補足説明ありがとうございました。
今日会社でやってみたところ成功しました。
どうしてもうまくいかなかったので、
=IF(B6=0,MOD($B2-2+DAY(B3)-SUM($B5:B5),$B1)+1,0)
の最初のＢ6＝0を、Ｂ5＝0とやったらできました。
あっていたのか？ちょっと不安ですが、おかげさまで表は完成いたしました。
どうもありがとうございました。
他の皆さんの方法を試す時間がなかった（というか私のレベルだと理解するのに時間がかかるので）
他の表に活用させていただきます。
どうもありがとうございました。

お礼日時：2010/09/28 23:37