[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『曜日で時間が変わる交番表の作成』（素人）
平日（月・金）、土日祝日、平日（火～金）の３パターンの計画時間がありますが 今は、平日の時間で下のようになっています。 P2に年 T2に月 P7に日(=マスタ!B2) P8に曜日(=P7) P9には=VLOOKUP(P7,マスタ!$B$6:$C$41,2FALSE) P10には=IF(ISNA(P9),"",P9) P13に交番 P15に計画時間には =IF(P13=","VLOOKUP(P13,マスタ!$F25:$L$66,2,0))
マスタのシート
B2に =DATE(計画入力!P2,計画入力!T2-1,16) B4に =YEAR(B2) B6～B41にDATE C6～C41に祝日の名前
F25～F66までに交番 G25～G66に計画時間
と入っています。カレンダーは万年で祝日も出てきます。
他に（F25～F66までに交番 G25～G66に計画時間）と同じように３パターンの
計画時間を作り曜日で時間が変わる様にしたいのですが
たとえば、平日、月①は７時間
祝日、 ①は６時間
平日、水①は８時間
説明が上手くできませんが曜日で計画時間が変わるようにしたいのでお願いします。
よ～～～～く読んだら、何となく意味がわかりました。(^_^A;
計画時間を入れるセルは、
=LOOKUP(WEEKDAY(P8,2)+10*N(P10<>""),{1;2;3;4;5;6;7;8},{7;8;8;8;7;6;6;6})
こんな感じで3つに分けることが出来ます。
ただ、多分おやりになりたいことはこんなことではなく、 各パターンごとに計画時間のリストを変更したいってことだと思いますので、 (1)マスタのF25～L66の具体的な内容 をお書きになってみてください。
おそらくOFFSET関数または、INDEX関数などの組み合わせで可能だと思います。 ''で、上でお書きになった例題で、 ＞平日（月・金）、土日祝日、平日（火～金） これは、 平日（月・金）、土日祝日、平日（火～木） ~~ の間違いだと判断して式を考えてます。
（川野鮎太郎）
すいません間違いでした。 平日（月・金）、土日祝日、平日（火～木）です。
マスタのF25～L66の内容ですが
F G H I J K L
24 交番 計画H 始業H 休憩H 終業H
25 ① 7 5 30 1 0 13:30
F24には項目なのでF25からが入力になります。
L25には
=IF(G25="","",TIME(H25,I25,0)+TIME(J25,K25,0)+TIME(G25,(G25-INT(G25))*60,0))
の式が入っています。
F26～F66は交番の番号です。
G25～G66が計画時間になります。ここが曜日で変わります。
よろしくお願いします。 (素人）
1行だけではデータの規則性がつかめません。 ですから、 ＞(1)マスタのF25～L66の具体的な内容 と、申し上げました。
長くなってもかまいませんので、全て列記してください。
（川野鮎太郎）
F G H I J K L
24 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ
25 ① 7 5 30 1 0 13:30
26 ② 11 5 30 1 0 17:30
27 ③ 11.5 5 30 1 0 18:00
28 ④ 11 5 30 1 0 17:30
29 ⑤ 10 9 0 1 0 20:00
30 ⑥ 11.5 5 30 1 0 18:00
31 ⑦ 8.6 9 0 1 0 18:36
32 ⑧ 9.5 5 30 1 0 16:00
33 ⑨ 11 5 30 1 0 17:30
34 ⑩ 11 5 30 1 0 17:30
35 ⑪ 10.8 5 30 1 0 17:18
36 ⑫ 11.5 5 30 1 0 18:00
37 ⑬ 9 5 30 1 0 15:30
38 ● 公休
平日は、こんな感じになります。土・日などは交番以外の計画Hが変わり
それにあわせて終業Hが変わります。
(素人）
>土・日などは交番以外の計画Hが変わり > それにあわせて終業Hが変わります。
何で >長くなってもかまいませんので、全て列記してください。 と書いてくださってるのにすべて記載しないのだろう？ (とおりすがり)
F39からL66、に入る所は交番に追加があった時の予備で広くとってあります。 交番の所には数字、記号など何でもかまいません、交番の番号に成りますので、 それにあわせて計画Hが設定され始業H、 休憩Hが決まり終業Hが出てきます。(素人）
なんだか謎の多い相談。 ある特定の一日のことだけ表示すればよいものなら、 「曜日」を元に数値を変化させるような数式を G25～G66 の範囲に入力すればよいのですが、 もし、このシートが一ヶ月間表示されるようなものなら、この方法はとれません。 ＞カレンダーは万年で祝日も出てきます。 ということですが、ご提示の部分とどういう関係があるのか、説明がないので、判断が付きません。 A列から始まって、P列が16列目ですから、おそらくは、 シフト表の一部分だけのご提示なのだとは推測できます。が、あくまで推測。 この辺は説明してもらわないとわかりませんね。 マスタ!B2の「=DATE(計画入力!P2,計画入力!T2-1,16)」。 「 P2に年 T2に月」としたものの前月の16日のシリアル値を求めている。 例えば、 P2に2008、 T2に 11 と入力すると、 P7、P8にはそれぞれ マスタ!B2 の数式から2008/10/16の日付が返ります。 （表示形式"d"や"aaa"を使っていると推測しますが・・・） T2に入力した月の前月の曜日を表示するのが正しいのかどうか。 意図してそのようにしているのならばよいのですけれども。 ＞土・日などは交番以外の計画Hが変わり 回答者が教えてほしいのは（エクセルで計算するのに必要なのは） 「曜日や祝日によって計画Hが変わる」ということではなくて、 「曜日や祝日によって計画Hがどのように変わるのか」 です。 ある曜日の分がリスト化できているのであれば、それ以外もリスト化 してみてはいかがですか。 (みやほりん)(-_∂)b
説明がうまく出来なくてすみません。
平日（月・金）労働H 土日祝日 労働H 平日（火～木) 労働H ① 7 ① 6 ① 8 ② 11 ② 10 ② 11 ③ 11.5 ③ 11 ③ 11.5 ④ 11 ④ 9.2 ④ 9.5 ⑤ 10 ⑤ 7.5 ⑤ 9 ⑥ 11.5 ⑥ 11.5 ⑥ 11.5 ⑦ 8.6 ⑦ 8.5 ⑦ 8.8 ⑧ 9.5 ⑧ 7.5 ⑧ 9.5 ⑨ 11 ⑨ 11 ⑨ 11 ⑩ 11 ⑩ 11 ⑩ 11 ⑫ 10.8 ⑫ 10 ⑫ 10.5 ⑪ 11.5 ⑪ 11.5 ⑪ 11.5 ⑬ 9 ⑬ 8 ⑬ 9.5 ～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～ ● ● ●
３パターンの数字を適当に入れてみました。でも、この数字は変わる数字なので
あまり関係ないと思います。
F G H I J K L
24 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ
25 ① 7 5 30 1 0 13:30
26 ② 11 5 30 1 0 17:30
27 ③ 11.5 5 30 1 0 18:00
28 ④ 11 5 30 1 0 17:30
29 ⑤ 10 9 0 1 0 20:00
30 ⑥ 11.5 5 30 1 0 18:00
31 ⑦ 8.6 9 0 1 0 18:36
32 ⑧ 9.5 5 30 1 0 16:00
33 ⑨ 11 5 30 1 0 17:30
34 ⑩ 11 5 30 1 0 17:30
35 ⑪ 10.8 5 30 1 0 17:18
36 ⑫ 11.5 5 30 1 0 18:00
37 ⑬ 9 5 30 1 0 15:30
38 ● 公休
～～～～～～～～～～～～～～～～～～～～
66
F24～L66の範囲と同じものを他のセルに２つ作成して
平日（月・金）労働H
土日祝日 労働
平日（火～木) 労働H
の３パターンから曜日を判定して時間を出したいのです。
16から始めているのは16日～15日が1ヶ月なのでそのようにしています。 2008 年 12 月度交番表
2008/11/16 ～2008/12/15
16 17 18 19 20 21 22～ 15 日 月 火 水 木 金 土～ 月 （素人）
やっと判りました。
やはり最初に思ったように、曜日（3パターン）別にリスト（範囲）を変更したいってことのようですね。
以下のような配置だとして、3つのパターンで名前の定義を使ったほうが、
式の組立が簡単だと思います。
[F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]
[23] 平日1 平日2 休日
[24] 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ
[25] ① 7 5 30 1 0 13:30 ① 7 5 30 1 0 13:30 ① 7 5 30 1 0 13:30
[26] ② 11 5 30 1 0 17:30 ② 11 5 30 1 0 17:30 ② 11 5 30 1 0 17:30
[27] ③ 11.5 5 30 1 0 18:00 ③ 11.5 5 30 1 0 18:00 ③ 11.5 5 30 1 0 18:00
[28] ④ 11 5 30 1 0 17:30 ④ 11 5 30 1 0 17:30 ④ 11 5 30 1 0 17:30
[29] ⑤ 10 9 0 1 0 20:00 ⑤ 10 9 0 1 0 20:00 ⑤ 13 9 0 1 0 20:00
[30] ⑥ 11.5 5 30 1 0 18:00 ⑥ 11.5 5 30 1 0 18:00 ⑥ 11.5 5 30 1 0 18:00
[31] ⑦ 8.6 9 0 1 0 18:36 ⑦ 8.6 9 0 1 0 18:36 ⑦ 8.6 9 0 1 0 18:36
[32] ⑧ 9.5 5 30 1 0 16:00 ⑧ 9.5 5 30 1 0 16:00 ⑧ 9.5 5 30 1 0 16:00
[33] ⑨ 11 5 30 1 0 17:30 ⑨ 11 5 30 1 0 17:30 ⑨ 11 5 30 1 0 17:30
[34] ⑩ 11 5 30 1 0 17:30 ⑩ 11 5 30 1 0 17:30 ⑩ 11 5 30 1 0 17:30
[35] ⑪ 10.8 5 30 1 0 17:18 ⑪ 10.8 5 30 1 0 17:18 ⑪ 10.8 5 30 1 0 17:18
[36] ⑫ 11.5 5 30 1 0 18:00 ⑫ 11.5 5 30 1 0 18:00 ⑫ 11.5 5 30 1 0 18:00
[37] ⑬ 9 5 30 1 0 15:30 ⑬ 9 5 30 1 0 15:30 ⑬ 9 5 30 1 0 15:30
[38] ● 公休 ● 公休 ● 公休
F24～L66を選択して、名前の定義を 平日1
M24～S66を選択して、名前の定義を 平日2
T24～Z66を選択して、名前の定義を 休日 として、
P15セル（時間）に =IF(P13="","",VLOOKUP(P13,INDIRECT(LOOKUP(WEEKDAY(P8,2)+10*N(P10<>""),{1;2;5;6},{"平日1";"平日2";"平日1";"休日"})),2,0))
（川野鮎太郎）
川野鮎太郎様
大変ありがとうございました、出来ました。
もう少し教えて頂けますか。
{1;2;5;6},{"平日1";"平日2";"平日1";"休日"}の意味を教えて下さい。
あと、これに追加のリストが出来た場合はどのようにすればよいでしょうか。
日・月・祝日・祝日の次の日の場合を教えて頂けますか。（素人）
説明はすご～～～～～～～く苦手なので、わかりにくいかも＿/￣|○ il||li
LOOKUP(WEEKDAY(P8,2)+10*N(P10<>""),{1;2;5;6},{"平日1";"平日2";"平日1";"休日"})
LOOKUP関数の引数として使用しているもので、
WEEKDAY(P8,2)+10*N(P10<>"") は、月日のパターンを算出してます。
まず、WEEKDAY(P8,2)は、以下のようになります。
仮に2008/10/1から数えた場合
月日 曜日 祝祭日 WEEKDAY WEEKDAY(P8,2)+10*N(P10<>"")
10月1日 水 3 3
10月2日 木 4 4
10月3日 金 5 5
10月4日 土 6 6
10月5日 日 7 7
10月6日 月 1 1
10月7日 火 2 2
10月8日 水 3 3
10月9日 木 4 4
10月10日 金 5 5
10月11日 土 6 6
10月12日 日 7 7
10月13日 月 体育の日 1 11 P10が祝日の判定セルになっているので、空白じゃなかったら10足すことになる。
10月14日 火 2 2
ただし、上記の場合で、10/13は祝日なので、10*N(P10<>"")の数値がプラスされます。
そして、LOOKUP関数は、範囲に対する値を返してくれるので、以下のようになります。
月 1 平日1
火 2 平日2
水 3 平日2
木 4 平日2
金 5 平日1
土 6 休日
日 7 休日
祝祭日 11 休日
ただし、範囲の数値は検索値の以下の最大値で返ってくるので、3,4,7,11が省略できます。
なので、以下のような配列で結果が求められるわけです。
月 1 平日1
火水木 2 平日2
金 5 平日1
土日休 6 休日
これを数式に直したものが、{1;2;5;6},{"平日1";"平日2";"平日1";"休日"} です。
追加のリストが出来た場合は、追加の要素が判らないと今回の式（ロジック）が全く使えないかもしれません。
使えるとしたら、10*N(P10<>"")+10*（別の要素） などとして、LOOKUPの引数に
{1;2;5;6;19},{"平日1";"平日2";"平日1";"休日";"別日"}などと出来るかもしれません。
（川野鮎太郎）
ありがとうございます。 凄く難しいです。
(日・月・祝日・祝日の次の日) 2008/11/16～2008/12/15 16 17 18 19 20 21 22 23 24 25 日 月 火 水 木 金 土 日 月 火 の場合23 24 25 （日・祝日・祝日の次の日） 2008/12/16～2009/1/15 17 18 19 20 21 22 23 24 25 水 木 金 土 日 月 火 水 木 の21 22 23 24 （日・月・祝日・祝日の次の日）
このような時の出し方が出来ればお願いします。
(素人）
以下の場合、それぞれに対応するパターンはどうなりますか。 11月23日 日 11月24日 月 祝日 11月25日 火 12月21日 日 12月22日 月 12月23日 火 祝日 12月24日 水
（川野鮎太郎）
11月23日 日 11月24日 月 祝日 11月25日 火 12月21日 日 12月22日 月 12月23日 火 祝日 12月24日 水 は同じリストです （日・月・祝日・祝日の次の日） 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 7 5 00 1 0 13:00 F1 9 5 00 1 0 15:00 F2 7 5 30 1 0 13:30 こんな感じです。
上と下で一週間になります。
(火～土） 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 10 2 00 1 0 13:00 F1 10 2 00 1 0 13:00 F2 10 2 00 1 0 13:00 よろしくお願いします（素人）
"o(-_-;*) ウゥム… また判らなくなりました。 何で火曜から土曜日が同じなのでしょうか。
今後は2パターンしかなくなったってことですか？
（川野鮎太郎）
説明が上手く出来なくてすみません。 始めにあった３パターンと後から追加した２パターンを足して５パターンです。 宜しくお願いします。 (素人)
はっきり言って意味不明のため無理です。 ＞始めにあった３パターンと後から追加した２パターンを足して５パターン どう判断すればその5パターンがどうなるのか・・・。 火曜日はどれ？水曜日は？木曜日は？金曜日は？土曜日は？
（川野鮎太郎）
一応書いてみます。
「最初の３パターンと 後から追加した２パターンは それぞれ別のグループのパターン」 と言う事ではないのですかね。
でしたら、どちらのグループのパターンを使いたいのか エクセルに意思表示をする必要がありますが どこで判断すれば良いのでしょう。
それとももっと単純に 「このシート（或いはブック）は全て最初の３パターンを使用 （或いは追加の２パターンを使用）」 とか言う事なんですかね。
(HANA)
説明不足と説明が下手でご迷惑をお掛けしてすいません。 「最初の３パターンと 後から追加した２パターンは それぞれ別のグループのパターン」 はい、そうです。後からの分が修正で３パターンに変更でした。 ①～⑬の数字で書いてあるグループが最所の３パターンになります。 F～F3の英数字で書いてあるグループが後からの３パターンになります。 同じブックで２つのグループの交番を入力するにはパターンの数が増えてしまうのです が無理でしょうか。
F～F3の英数字で書いてあるグループのパターン 平日（火～土） 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 10 2 0 1 0 13:00 F1 10 2 0 1 0 13:00 F2 10 2 0 1 0 13:00 F3 10 2 0 1 0 13:00 （日・祝日・月） 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 7 5 0 1 0 13:00 F1 7 5 0 1 0 13:00 F2 7 5 0 1 0 13:00 F3 7 5 0 1 0 13:00
（祝日の後日） 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 7 5 0 1 0 13:00 F1 7 5 0 1 0 13:00 F2 7 5 0 1 0 13:00 F3 7 5 0 1 0 13:00
宜しくお願いします。（素人）
P列が祝日か？は、P10セルを見れば分かります。 P列の一日前の日が祝日か？ （P列が祝日の後日か？）というのは どこのセルを見て判断することにしますか？
O列が空いていてO10セルで確認出来るのが簡単でしょうけど どこか空いた行を使って式を作成してもらっても構いません。
現在そちらで完成している部分の式まで変更するつもりは有りませんので 「前日が祝日だったのか」が分かるような作業セルを そちらで作成した後、P列に対応するセル番地を教えて下さい。
(HANA)
ありがとうございます。 カレンダーの部分に入っている式を書きます。 2008/11/16～2008/12/15の時
P Q R S T U V W X Y
7 16 17 18 19 20 21 22 23 24 25
=ﾏｽﾀ!B2
8 日 月 火 水 木 金 土 日 月 火
=P7
9 #N/A #N/A #N/A #N/A #N/A #N/A #N/A 勤労感謝の日 振替休日
=VLOOKUP(P7,ﾏｽﾀ!$B$6:$C$41,2,FALSE)
10
=IF(ISNA(P9),"",P9) 勤労感謝の日 振替休日
マスタにある11/23.24
B
29 2008/11/23 勤労感謝の日 11月23日
=DATE($B$4,11,23)
30 2008/11/24 振替休日 =IF(WEEKDAY(B29)=1,B29+1,"")
分かりずらいかもしれません。（素人）
えっと・・・私が欲しいのは ＞「前日が祝日だったのか」が分かるような作業セル です。 Q列以降は、前の列の10行目で確認出来ますが P列に関しては始まりの日なので「前の列の」 と言う訳にはいきません。
作業セルは、そちらの空いていて使用出来るセルを使って下さい。 こちらで勝手に決めても「そこには他の物が入っている」と思いますので。
他の列が「前の列の」が使えるので簡単なのは、O列を作業列にして P列に関しても「前の列の」を使うことです。 ですが、O列は何か他の物が入っていますよね。
見たところ、9行目と10行目は簡単に一つにまとめる事が出来そうなので この二つの行を10行目にまとめて 9行目を作業セルに使いますか？
(HANA)
空いてるセルは Oの列はO9.O10.O11です。11行目は空けられます。 （素人）
でしたら、O10に15日が祝日かどうかの判定を出して下さい。 祝日だったら祝日の名前、そうでない場合は「""」です。
それから・・・後は夜にレスします。 それまでに川野鮎太郎さんはじめ 他の方からの書き込みが無ければ。。。ですが。
(HANA)
15日が祝日かどうかの判定をどおしてよいか分かりません。16～15なので カレンダーを前の月に合わせれば見れますが (素人）
ん？難しく考えなくて大丈夫と思いますよ。 =VLOOKUP(P7,ﾏｽﾀ!$B$6:$C$41,2,FALSE) この式で、16日が祝日かどうか分かります。 なぜならP7セルには16日の日付が入っているからです。
調べたいのは 15日です。 15日は16日の1日前ですから P7-1 日ですよね。
(HANA)
新しい３つのパターンはそれぞれ PTR1 平日（火～土） PTR2 （日・祝日・月） PTR3 （祝日の後日） と名前を付けておいて下さい。
P15に入れる式は、
=IF(P13="","",VLOOKUP(P13,INDIRECT(LOOKUP(WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*(LEFT(P13,1)="F"),
{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"})),2,0))
↑２行で一つの式です。
思い違いがなければこれで良いと思うのですが・・・。 検証していませんので、色々な月で確認してください。
(HANA)
ありがとうございます。 コピーして貼り付けると 全部同じ時間になるのはなぜでしょう。 あと、式の意味を教えて頂けますか。 (素人）
何故でしょうかね。 11/16～11/25の範囲（↑で説明して下さっている範囲） のデータではテストしているので、 全て同じになることは無いと思いますが。
試しに、全てのパターンの最初の計画Ｈ（各パターン範囲の左上セル）の値を A,B,C等の文字列を宛ててみて、P13に「①」を入れた場合と「F」を入れた場合で 実際にどの表の値が参照されているか確認して下さい。
式の意味は、鮎太郎さんのご説明に準じます。 どこまで分かりますか。 分からないところを再度お聞き下さい。
(HANA)
式を入力した所は問題ないのですがコピーして張り付けた場所はだめです。
+(20*(O10<>"")+100)*(LEFT(P13,1)="F"),この辺が？ もし、（祝日の後日）を外したい時は、どのようにすればよろしいですか。 聞いてばかりで申し訳ありません。（素人）
う～ん、鮎太郎さんの式では正しい結果が返されたのですよね？ それとも、前の段階でも上手く行っていなかったのですかね。
P13セルに式を貼付した後、【P13セルをコピー】してQ14セル以降に貼付 と言う手順でやってもらっていますか？
また、実際にはどの表の値が参照されていましたか？ まずは式問題点を見つけましょう。
(HANA)
行をコピーではなく、セルをコピーしてやったら出来ました。 （祝日の後日）を外したい時は PTR3";" +(20*(O10<>"")+100) ;131を抜けばよろしいのですか？（素人）
セルをコピーで上手く行きましたか、良かったです。
式の変更については運用方法を教えて下さい。 （祝日の後日）を外したい時と外さない時は どの様に使い分けるのですか？
どこかのセルの値によって変えるのなら それも加味した式にするのがよいと思います。 今後運用中に「やっぱり２パターンで行こう」と言われた場合を 想定して居られるのなら、もう一つ式を作っておけば良いと思いますが。
(HANA)
ありがとうございます。 今後の事も考えて見たいのと、いろんなパターンが出来た事も考えてのことです。 （素人）
それでは説明してみます。 式中の +(20*(O10<>"")+100)*(LEFT(P13,1)="F") の部分ですが、分解して書くと +20*(O10<>"")*(LEFT(P13,1)="F") +100*(LEFT(P13,1)="F") の二つに分けることが出来ます。
上側は P13セルがFと言う文字で始まっていて、O10<>の時 +20 下側は P13セルがFと言う文字で始まっている時 +100 します。
WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*(LEFT(P13,1)="F") の部分は以下の様なパターンの値になります。 平日 祝日 GR2 GR2 GR2 GR2 平日 祝日 前祝 祝日+前祝 +100 +100 +100 +100 ←Fで始まったら +100*(LEFT(P13,1)="F") +10 +10 +10 ←祝日なら+10 +10*(P10<>"") +20 +20 ←前祝なら+20 +(20*(O10<>"")+100)*(LEFT(P13,1)="F") 月 1 11 101 111 121 131 火 2 12 102 112 122 132 水 3 13 103 113 123 133 木 4 14 104 114 124 134 金 5 15 105 115 125 135 土 6 16 106 116 126 136 日 7 17 107 117 127 137
≪注≫「前祝」とは「前日が祝日」＝祝日の次の日 の事です。
この時、どの範囲を参照したいか と言うと 平日 祝日 GR2 GR2 GR2 GR2 平日 祝日 前祝 祝日+前祝 月 平日1 休日 PTR2 PTR2 PTR3 PTR2 火 平日2 休日 PTR1 PTR2 PTR3 PTR2 水 平日2 休日 PTR1 PTR2 PTR3 PTR2 木 平日2 休日 PTR1 PTR2 PTR3 PTR2 金 平日1 休日 PTR1 PTR2 PTR3 PTR2 土 休日 休日 PTR1 PTR2 PTR3 PTR2 日 休日 休日 PTR2 PTR2 PTR3 PTR2
上の表で1の時 平日1 上の行で2～4の時 平日2 ・・・とこの辺のまとめ方は上で鮎太郎さんが書いて居られるので （列挙すると縦長に成ってしまうので）省略しますが まとめると
1 平日1 2 平日2 5 平日1 6 休日 101 PTR2 102 PTR1 107 PTR2 121 PTR3 131 PTR2 に成ります。
これが
{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"}
の部分に対応します。
PTR3が不要の場合は、まず +20*(O10<>"")*(LEFT(P13,1)="F") が不要になります。 +100*(LEFT(P13,1)="F") は必要ですよ。
ですから戻り値と範囲の対応表は 平日 祝日 GR2 GR2 平日 祝日 GR2 GR2 平日 祝日 平日 祝日 +100 +100 ←+100*(LEFT(P13,1)="F") +10 +10 ←+10*(P10<>"") 月 1 11 101 111 月 平日1 休日 PTR2 PTR2 火 2 12 102 112 火 平日2 休日 PTR1 PTR2 水 3 13 103 113 水 平日2 休日 PTR1 PTR2 木 4 14 104 114 木 平日2 休日 PTR1 PTR2 金 5 15 105 115 金 平日1 休日 PTR1 PTR2 土 6 16 106 116 土 休日 休日 PTR1 PTR2 日 7 17 107 117 日 休日 休日 PTR2 PTR2
まとめると 1 平日1 2 平日2 5 平日1 6 休日 101 PTR2 102 PTR1 107 PTR2
ですね。 121以降の二つが不要になります。
あまり詳しく説明していませんが ◆20*(O10<>"")*(LEFT(P13,1)="F") これで、前の日が祝日だったら+20される と言うのは分かりますかね。
その他、不明点は再度ご質問下さい。
(HANA)
丁寧にありがとうございます。 よく見てみます、
HANA様、 川野鮎太郎様 大変ありがとうございました。また何かあればよろしくお願いします。
(素人）
HANA様 また質問です。 *(LEFT(P13,1)="F") この部分ですが、同じグループでFから始まる時はいいのですが、もし、違う文字、数字などで 判定したいときはどのようにすればよろしいですか？ （素人）
どの様な文字や数字で判定したいのか例をあげて下さい。 「どんな文字や数字か分からない」のなら グループにできませんよね。
(HANA)
（日・祝日・月）と（火～金）と（祝日後日）のグループは別のブックで作成しても 文字や数字が分からないと無理でしょうか？その都度、文字を考えて入力したりは出来 ないでしょうか？(素人）
済みません、どの様な事をなさりたいと仰って居られるのか イメージが分かりません。
グループが沢山有る と言う事？ グループは二つしか無いが、同じグループの中で開始文字が色々有る （左端がFで始まらないが同じグループの物がある）と言う事？ グループはいくつか有るが、そのうち二つのグループを使い 一つは○数字だが、もう一つがFで始まるとは限らない。（その都度変わる）と言う事？ 時間も変わる？それとも番号だけが変わる？
言葉のご説明だけでは、どの様な事を言っておられるのか分かりません。 このスレの最初の方でも、鮎太郎さんが 「具体的な内容をお書きになってみてください。」 と書いておられますよね。 そして私は直上でも「例をあげて下さい。」と書いています。
(HANA)
何度も申し訳ありません。
グループは二つしか無いのですが、（Fで始まるグループ）は左端がFで始まらない物 が あり同じグループの中で開始文字が色々有ります。 (グループ２）パターン １ 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 10 2 0 1 0 13:00 F1 10 2 0 1 0 13:00 F2 10 2 0 1 0 13:00 F3 10 2 0 1 0 13:00 送迎 10 2 0 1 0 13:00 郡 10 2 0 1 0 13:00 会２ 10 2 0 1 0 13:00 福 10 2 0 1 0 13:00 郡３ 10 2 0 1 0 13:00 郡２ 10 2 0 1 0 13:00 福１ 10 2 0 1 0 13:00 会１ 10 2 0 1 0 13:00 岩 10 2 0 1 0 13:00 宮 10 2 0 1 0 13:00 ● 公休 など、交番に入る文字などはその都度考えて入れる感じになります。 パターン２．３は少し時間が変わる グループ１とグループ２に入る文字・英・数字はだぶらないように考えています。
（素人）
＞グループ１とグループ２に入る文字・英・数字はだぶらないように考えています。 と言う事は、グループ１の方の例として １～の丸数字が使用されていますが、 「グループ１に関しても、実際はいろいろな交番名がある」 と言う事ですかね。
でしたら、交番名からグループを割り出すのではなく どこか別の列にどちらのグループなのか 入力する列を作るのが良いと思います。
グループ１とグループ２で 気づかずに同じ名前を使うと 参照先が意図しない物になってしまいますし・・・・・。
グループ名をどうしても指定したくない （新たに列を設けて入力したくない）のなら COUNTIF関数等でどちらのグループなのか決定しても良いかもしれませんが。
よく使われる簡単な例としては 交番～就業H迄の範囲を PTR1 として名前定義済み 交番 計画Ｈ 始業Ｈ 休憩H 終業Ｈ F 10 2 0 1 0 13:00 F1 10 2 0 1 0 13:00 F2 10 2 0 1 0 13:00 F3 10 2 0 1 0 13:00 送迎 10 2 0 1 0 13:00 郡 10 2 0 1 0 13:00 会２ 10 2 0 1 0 13:00 福 10 2 0 1 0 13:00 郡３ 10 2 0 1 0 13:00 郡２ 10 2 0 1 0 13:00 福１ 10 2 0 1 0 13:00 会１ 10 2 0 1 0 13:00 岩 10 2 0 1 0 13:00 宮 10 2 0 1 0 13:00 ↑新たに交番の範囲のみ 「GRP2」として名前を定義 「COUNTIF(GRP2,P13)」の戻り値が 0 ならグループ１ 1なら、グループ２と判断できます。
(HANA)
説明が上手く出来なくすみません。 例えばグループ1という部屋に３バターンあります。(平日1、平日２、休日) グループ２の部屋に３パターン(PTR1、PTR2、PTR3)それぞれの交番の名前はいろいろありグループごとにだぶらないように設定します。それで、その曜日に入った交番を元に時間を割り出すことが可能ですか？ (素人)
？？？ 同じグループでもパターンが違うと、交番名が違う ってことですか？
(HANA)
グループ内の交番名は一緒です。ただ、名前が英数字や囲み文字等々。違うグループ内の交番名とはだぶらないように設定します。●は一緒です。(素人)
えっとですね、再度書きますが
【１】 でしたら、交番名からグループを割り出すのではなく どこか別の列にどちらのグループなのか 入力する列を作るのが良いと思います。
【２】 グループ名をどうしても指定したくない （新たに列を設けて入力したくない）のなら COUNTIF関数等でどちらのグループなのか決定しても良いかもしれませんが。
どちらの方法をとるにしても、(LEFT(P13,1)="F")の部分を 「グループ２の時に1 、グループ１の時に0」と成るような 数式と置き換える必要があります。
現在は「Fで始まったらグループ２」とみなす式に成っていますので。
(HANA)
何度もすみません。 (2)のやり方で作るにはどのようにすればよいですか。お願いします。(素人)
えっと・・・再度詳しくは書きませんので もう一度該当部分は読み直して下さいね。 （６個上の書き込みの部分の事です。）
数式内の「(LEFT(P13,1)="F")」の部分がありますよね それを「COUNTIF(GRP2,P13)」の式と変更するのですが 事前に該当範囲に『GRP2』の名前を定義しておいて下さい。
上手く行かない場合は、どこかの空いた行（11行目？）に 「COUNTIF(GRP2,P13)」の式をいれて、戻り値が ＞「グループ２の時に1 、グループ１の時に0」 と成るのか確認して下さい。
上手く行くと良いのですが・・・。
(HANA)
ありがとうございました出来ました。（素人）
何度もすみません。
=IF(P13="","",VLOOKUP(P13,INDIRECT(LOOKUP(WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13),{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"})),2,0))
何故か、16日にはいる時間が祝日の後日を表示してしまいます。（素人）
O10セルの値はどの様になっていますか？
上でそれぞれの日がどの様な値をとるのか対応表を載せていますが、 16日の該当の日が何処と一致すべきなのか捜してください。 その後、数式内のそれを導く所の式の戻り値を一つずつ確認し、 何処に数式の間違いが有るのか確認していくのが 早いように思います。
「数式内のそれを導く所の式の戻り値を一つずつ確認」と言うのは WEEKDAY(P8,2) 10*(P10<>"") 20*(O10<>"") COUNTIF(GRP2,P13) の戻り値をそれぞれ確認する と言う事です。
「16日」と言う日付が何年何月の16日の事なのか。 O10セルの値はどの様になっているか。 （O9:O10セルには正しく式が入力出来ていますか？） 数式内のそれぞれの式の戻り値を一つずつ 教えて下さい。
(HANA)
このブックを見てもらえれば何処が間違っているかがわかるのですが無理なので O10は=P7-1です 11行目に=COUNTIF(GRP2,P13)でよいのですか？Ｆを16日～15日に入れると全て１です。 ① を入れると0です。 =WEEKDAY(P8,2)は2008/12/16から712345671234567とつずきます。 =10*(P10<>"")は2008/12/16から0 0 0 0 0 0 0 10 10 0 =20*(O10<>"")は2008/12/16から20 0 0 0 0 0 0 0 20 20 0です、16日が何故20?
16日は2008年12月の表示です。交番はそのままで、月を替えると16日だけが祝日後日を 指ます。 (素人）
●O10は=P7-1です と言う事ですが、O10には ＞でしたら、O10に15日が祝日かどうかの判定を出して下さい。 ＞祝日だったら祝日の名前、そうでない場合は「""」です。
=P7-1 は、P7セルの一日前の日付が返されていますよね。
●16日が何故20? ＞あまり詳しく説明していませんが ＞◆20*(O10<>"")*(LEFT(P13,1)="F") ＞これで、前の日が祝日だったら+20される ＞と言うのは分かりますかね。 この部分に「分かりません」と言うお言葉がなかったので 分かって居られるのかと思っていましたが・・・。
取りあえず、O7セルに「=P7-1」を入れてください。 その後、O9:10セルには P9:P10セルをコピーして貼り付けてください。
(HANA)
取りあえず、O7セルに「=P7-1」を入れてました。 O9 =VLOOKUP(O7,ﾏｽﾀ!$B$6:$C$41,2,FALSE) O10 =IF(ISNA(O9),"",O9) と入れました。 何故、11/15が祝日でないのに20なのですか？ (素人）
＞ と入れました。 それでも、=20*(O10<>"")は20のままですか？?
(HANA)
=20*(O10<>"")は0になりましたが、時間は祝日後日を指ます。（素人）
そうですね・・・ブックを見ることができないのなら あなたのご説明だけが頼りです。
ですから、もう一度確認してください。 ＞=WEEKDAY(P8,2)は2008/12/16から712345671234567とつずきます。 ということですが、P8セルに入っているのが 2008/12/16 であれば この日は火曜日ですから、WEEKDAY関数の戻り値は「2」でないとおかしいですね。
7で始まるということは、そのセルに入っている日付の曜日は 日曜日のはずです。
＞=20*(O10<>"")は0になりましたが、時間は祝日後日を指ます。 でしたら ＞WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13) の戻り値は何になっていますか？ 時間が「祝日後日」の時間を表示するためには 121～127の値になっている必要がありますが そのようになっていますか？
また、その時の「COUNTIF(GRP2,P13)」の戻り値も確認してください。
(HANA)
=WEEKDAY(P8,2)は2008/11/16から712345671234567 =10*(P10<>"")は2008/11/16から0 0 0 0 0 0 0 10 10 0 =20*(O10<>"")は2008/11/16から20 0 0 0 0 0 0 0 20 20 0 でした。 でも、16日はだめです。 「COUNTIF(GRP2,P13)」は0 WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13)は107 =10*(P10<>"") 0 =WEEKDAY(P8,2) 0 =WEEKDAY(P8,2) 7 （素人）
ご説明がおかしいことに気づかれませんか？
WEEKDAY(P8,2) が 7
10*(P10<>"") が 0
(20*(O10<>"") が 0
COUNTIF(GRP2,P13) が 0
なら、これらを足し算や掛け算して得られる
WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13)は
7 + 0 +( 0 +100)* 0 ＝ 7
となって、107にはなりません。
もしも、 ＞「COUNTIF(GRP2,P13)」は0 というご説明が間違っているのなら 107 になりますが。
本当は、どうなっているのですかね。
＞WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13)は107
が正しいのなら
=LOOKUP(WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13),
{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"})
の戻り値を確認してください。
(HANA)
10*(P10<>"") 0 20*(O10<>"") 0 COUNTIF(GRP2,P13) 1 WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13) 107 =WEEKDAY(P8,2) 7 でした。（素人）
＞COUNTIF(GRP2,P13) が「1」でしたか。
それで
=LOOKUP(WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13),
{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"})
の戻り値はどうなりますかね。
(HANA)
=LOOKUP(WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13),
{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"})
の戻り値は 7.0です。どうにか出来ました。
いろいろと有難う御座いました。（素人）
7.0？ PTR2とかPTR3とかではなく？ 数値の名前は使えないと思いますが・・・・。
どういう事だったのですか？ こちらでは、何がどうなってどうだったのか 全く分かりませんので、事の顛末を教えて下さい。
(HANA)
何故かいじっているうち出来たみたいなのですが、O10に何か入ると16日が祝日後日を指すみたいです。
=LOOKUP(WEEKDAY(P8,2)+10*(P10<>"")+(20*(O10<>"")+100)*COUNTIF(GRP2,P13),
{1;2;5;6;101;102;107;121;131},{"平日1";"平日2";"平日1";"休日";"PTR2";"PTR1";"PTR2";"PTR3";"PTR2"})
の戻り値は ?あれ、出し方がわかりません。また、質問になってしまいました。（素人）
もう一度最初から読み直して式を理解して下さい。 でないと、同じ事の繰り返しです。
O10に何か入ると、16日が祝日後日を指すのなら そのような式になっていますので不思議な事では有りません。 ですから、15日が祝日でないのにO10に何か入るのなら そちらの方（O10に入れた式や関連の式）に問題が有るのでしょう。
＞の戻り値は ?あれ、出し方がわかりません。 に関しては、この式をセルにコピペしてもらえば良いのですが 「出し方が分からない」と言うのは、どう言うことでしょうか。 コピペの方法が分からない。どのセルに入れれば良いのか分からない。 エラーになって入力出来ない。結果が何も表示されない。 そちらでは、何がおきているのでしょうかね。
(HANA)
どのセルに入れれば良いのか分からないのですが。（素人）
どこのセルでも良いのですけどね。 その式の戻り値が必要なだけですので。
ただ、今までうまくいった状態と変わっていないのなら この式自体に問題があるとは思えません。
＞O10に何か入ると16日が祝日後日を指すみたいです。 ここに何か疑問がありますか？
(HANA)
計算が出来ているので大丈夫だと思います。 丁寧に有難うございました。（素人）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.