[[20201124154002]]　『平日、祝祭日で単価を変更する』（こま）　ページの最後に飛ぶ

『平日、祝祭日で単価を変更する』（こま）

Excel初心者です。

現在、作業報告書と請求内訳が合算されたものを使用しているます。(以下報告書)
その中の請求内訳の中に平日、土日祝、年末年始で単価が変わるものがあり、いままで手入力だったのですがミスが多くなり必ず入る請求なので、
平日、土日祝、年末年始によって単価を変える方法はないかと探しています。

単価は
平日：\9,000
土日祝：\12,000
年末年始：\18,000
時間外：×1.2
深夜：×1.5
となっています。(小数点切り上げ)

平日作業が入った場合そこに\9,000と入って時間外、深夜になった場合はそれぞれ時間数に応じて計算するようにしたいです。

どのようにしたらいいのかご教授お願いします。

シート1に報告書
シート2にカレンダー
シート3に一覧表
(会社の休み、祝日の一覧表があります。)

シート1
　 A　　　B　　　　　C D
1実施日yyyy年m月d日*曜日　実施時間0:00~0：00　休憩時間0:00~0:00
2標準**時間　　時間外**時間　　　　深夜**時間
3作業者名
4作業内容
5
6請求内訳　yyyy年m月d日
7品名　　数量　　　単価　　金額
8○○　　　　　　　　　　　　1人　　　　　　【9,000】

シート2

	A	B	C
1年月yyyy年m月　
2日付		曜日	祝日
3 1　		月　　　(祝日、社内休みの場合「祝日」年末年始の場合「年末年始」表記)
4 2　		火

シート3

	A	B
1日付		祝祭日
21/1		年末年始
31/13　		祝日

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

 返信つかないですねぇ
 私も答えようがないので静観してました
 いくつか教えて下さい
 また、作文てはなく、具体的な例をパターン別に教えて下さい

 >その中の請求内訳の中に平日、土日祝、年末年始で単価が変わるものがあり
 単価が変わるものって表中のどこですか？ 計算式を入れたい場所？
 日付がいくつか出てきますが、どこを見て判断すればいいですか？
 日付をまたぐ場合はありませんか？
 2行目の標準、時間外、深夜は既に計算式が入っているのですか？
 単価の単位は時間ですか？一日ですか？
 カレンダーシートは何を目的としていますか？

（稲葉） 2020/11/25(水) 00:16

例

シート2

　　　A　　　　　　B　　　　　　C　　　　　　D　　　　　　E
1　日付　　　　　曜日　　　　単価
2　19891011　　　日　　　　　9000
3　19891012　　　月　　　　12000　

これをずっと作る

　報告書の単価部分にVLOOKUPで単価を拾う

検索値＝実施日
範囲＝シート2のA1：C3
列番号2

　　　　　　
（ごっき） 2020/11/25(水) 00:22

こま = ごっき同一の人?
ニックネームをどうして変更した?

＞その中の請求内訳の中に平日、土日祝、年末年始で単価が変わるものがあり
そのことについて稲葉さんは質問しています。
何故返信しないのですか。
質問を無視しているのですか。

上記の表だったら自分で解決できると思うけど。
ましてや 19891011 は日付とは言えないです。ただの数値か文字列ですよ。
（ppm） 2020/11/25(水) 15:03

ごっきは私であって、質問主のこまさんとは違う人物です。
稲葉さんの後に、解決策を提示しているだけです。

あと、無視ではなく仕事とかでまだ掲示板を見れてないだけの様な気もするので
無視と決めつけるのは時期尚早かと。
（ごっき） 2020/11/25(水) 17:31

ごっきさん
「例」の単語見落としていました。
失礼しました。発言取り消します。
（ppm） 2020/11/25(水) 20:57

稲葉様

ご返信できず申し訳ありません。
単価が変わるものはC8の【】の中になります。
その中に計算式を入れたいです。

日付をまたぐこともあります
例えば12/1～12/2で行ったとき日付は12/1のままですが時間のところは48時間形式で入ります。

２行目の標準、時間外、深夜はすでに計算式入ってます。

単価の単位は時になります。

カレンダーは実施日のところに反映させるために作ってました。

初めてなので、至らない点ばかりで申し訳ありません。
（こま） 2020/12/07(月) 11:07

ごっき様

遅くなってしまい申し訳ありません。

VLOOKUPで拾う手もありました。
全然気づきませんでした。

ありがとうございます
参考にさせていただきます。
（こま） 2020/12/07(月) 11:15

ppm様

少々立て込んでしまいご返信できませんでした。
無視をしたわけでないです。

申し訳ありません。
（こま） 2020/12/07(月) 11:17

 >単価の単位は時になります。
 だとしたら
 >7品名 　 　数量 　　　単価　　 金額
 において単位が抜けているのでは?
 品名 　 　数量 　　単価　　単位(時) 　 金額

（ppm） 2020/12/07(月) 12:26

ppm様

単位の欄は設けず
品名　数量　単価/時　金額

と表記しています。
（こま） 2020/12/07(月) 13:44

 時間外：×1.2 
 深夜：×1.5　　ってどこにかかる係数なんですかね？

 稲葉さんもおっしゃっているように、具体例を記載してください。
 ・どのセルに
 ・何の値が入っていたら
 ・この結果が出てほしい
 これがないと現在提示の情報を読み取るのが難しいです
（ななし） 2020/12/07(月) 14:16

 > 2標準**時間　 　時間外**時間　　　　深夜**時間
 時間外**時間と深夜**時間は別々ですか。
 要は両方入力があり得るかということです。
 >単価/時
 では平日で時間外2時間としたら表記はどのようになるのですか。
 そして金額はいくらになるのですか。
 また C8の【】はどうなるのですか。

（ppm） 2020/12/07(月) 14:35

ななし様

至らないところばかりで申し訳ありません

・どのセルに
⇒シート1のC ８行目の【9,000】と入ってるところに計算式を入れたいです

・何の値が入っていたら
⇒実施日が平日ならば9,000、土日祝ならば12,000、年末年始ならば18,000となり、それぞれその単価に標準、時間外、深夜の時間分併せて計算したいです
　例　標準2時間のみの場合　9,000×2時間＝\18,000

・この結果が出てほしい
⇒最終としてはそれぞれ平日、土日祝、年末年始の単価に時間をかけた結果を出したいです。
　計算の際小数点はすべて切り上げで出したいです

例
2020/12/7　月曜日　実施時間16:00~21:00
標準：2時間　時間外：3時間　深夜：0時間
の場合

平日9,000×2時間×時間外1.25×3時間
＝\72,000
この計算した単価の答え\72,000を単価/時C 8行目に出したいです。
（こま） 2020/12/07(月) 15:36

 ひとまず、値段のテーブルと、係数のテーブルを分けて作成してください。
 そして、sheet1にはsheet2か3から祝日かどうかの情報を持ってこれるようにしましょう。

 ＞平日9,000×2時間×時間外1.25×3時間
 平日9,000×2時間＋時間外1.25×9,000×3時間ではなく上記の式で良いのですか？
 どちらにせよ上記式だと\67,500になると思うのですが…
（ななし） 2020/12/07(月) 16:35

 >品名　数量　単価/時　金額
              　↑
 >　　答え\72,000を単価/時
 ということですか。考えがおかしいですね。
 では金額の項目は何のためにあるのでしょう。
 >要は両方入力があり得るかということです。
 この件についてはどうなんですか

（big） 2020/12/07(月) 17:15

 ＞平日9,000×2時間×時間外1.25×3時間
 かぶりますけどこの計算方式明らかに誤りです。

（猿人） 2020/12/07(月) 21:29

平日 9200円/1時間とはどんな会社なんだろうと想像します。
仮に1日8時間、1ヶ月の内20間働いたとして計算すると
なんと1ヶ月 \1,472,000 になった。手当分を考慮すると
もっと金額が増えそう。
9200円/1時間は時間ではなく1日ではなかろうか。
1150円/1時間なら話は分かりますけど。

「実施時間16:00~21:00」の内訳は「標準：2時間時間外：3時間」
になるのだろうか。
だとしたら平日の勤務は2時間勤務とも受け取れますよね。

（頭の悪い人） 2020/12/07(月) 23:00

ななし様

すみません。
慌ててしまい計算式を間違えて書き込んでおりました。

計算式の方
\9,000*2時間+\9,000*1.25*3時間です。

申し訳ありません。
（こま） 2020/12/08(火) 09:34

ppm様

 > 2標準**時間　 　時間外**時間　　　　深夜**時間
 時間外**時間と深夜**時間は別々ですか。
 要は両方入力があり得るかということです。
⇒両方入ることもあります。

 >単価/時
 では平日で時間外2時間としたら表記はどのようになるのですか。
 そして金額はいくらになるのですか。
 また C8の【】はどうなるのですか。
⇒平日時間外のみの作業の場合C8【22,500】(\9,000*1.25*2時間)になります

（こま） 2020/12/08(火) 09:38

big様

品名　数量　単価/時　金額

              　↑
 >　　答え\72,000を単価/時
 ということですか。考えがおかしいですね。
 では金額の項目は何のためにあるのでしょう。
⇒計算の答えに関しては私のミスです。
申し訳ありません。

金額に関しては数量*単価の合計が入ります

 >要は両方入力があり得るかということです。
 この件についてはどうなんですか
⇒あくまでも単価の場所だけです。
平日、土日祝、年末年始で単価を変えてそれに応じて時間数を掛けたものを単価の部分に出したいです。
金額の欄はあくまでも単価と数量を掛けた合計を出す欄なので両方入ることはないです
（こま） 2020/12/08(火) 09:48

頭の悪い人様

平日 9200円/1時間とはどんな会社なんだろうと想像します。
仮に1日8時間、1ヶ月の内20間働いたとして計算すると
なんと1ヶ月 \1,472,000 になった。手当分を考慮すると
もっと金額が増えそう。
9200円/1時間は時間ではなく1日ではなかろうか。
1150円/1時間なら話は分かりますけど。
「実施時間16:00~21:00」の内訳は「標準：2時間時間外：3時間」
になるのだろうか。
だとしたら平日の勤務は2時間勤務とも受け取れますよね。

⇒計算式に関してはこちらのミスです。申し訳ありません。
こちらに関しては依頼を受け作業した内容を出す作業報告書であり1日の勤務を報告するものではないです。
なので平日2時間しか勤務してないということではないです。
（こま） 2020/12/08(火) 09:54

猿人様

申し訳ありません。
こちらの入力ミスです
正しくは
平日\9,000*2時間+時間外1.25*\9,000*3時間です。
（こま） 2020/12/08(火) 09:57

 よく読んでませんけど
 とりあえず ↓ の単価が出ればいいのなら･･･
 ＞平日：\9,000
 ＞土日祝：\12,000 
 ＞年末年始：\18,000

 日をまたぐ勤務もあるようですけど
 曜日とか祝日とかは、B1セルの「実施日」で判定すればいいんですよね？

 C8 =IF(COUNTIFS(Sheet3!A:A,B1,Sheet3!B:B,"年末年始"),18000,IF(WORKDAY(B1-1,1,Sheet3!A2:A100)>B1,12000,9000))

 あとはわかりません。
 ↓ がどんな式なのかもわかりませんし･･･
 ＞２行目の標準、時間外、深夜はすでに計算式入ってます。

 単価さえわかれば、あとは自分でできるんですよね？

 以上
（笑） 2020/12/08(火) 18:36

 >平日、土日祝、年末年始によって単価を変える方法はないかと探しています。
 ◎A1に日付を入力すると判定して切り替えてくれる。
 ◎計算結果も転記してくれる。
 >２行目の標準、時間外、深夜はすでに計算式入ってます。
 ※表示形態が分からないので行を追加しています。
 >(小数点切り上げ) 
 自分で対処願います。

     |[A]       |[B]   |[C]    |[D] 
 [1] |2020/11/23|月曜日|       |    
 [2] |標準      |時間外|深夜   |金額
 [3] |         2|     0|      0|    
 [4] |作業者名  |      |       |    
 [5] |作業内容  |      |       |    
 [6] |          |      |       |    
 [7] |請求内訳  |      |       |    
 [8] |品名      |数量  |単価/時|金額
 [9] |          |      |  24000|    
 [10]|          |      |       |    
 [11]|平日      |FALSE |       |    
 [12]|土日      |FALSE |       |    
 [13]|祝日      |TRUE  |  24000|    
 [14]|年末年始  |FALSE |       |    
 [15]|          |      |       |    
 [16]|検索値    |TRUE  |       |    
 [17]|          |      |       |    
 [18]|年末年始  |      |       |    
 [19]|2020/12/30|開始日|       |    
 [20]|2020/1/2  |終了日|       |    
 [21]|          |      |       |    
 [22]|祝日設定  |      |       |    
 [23]|2020/11/23|      |       |

 ■数式の内容
  ・抽出用
    C9=VLOOKUP($B$16,$B$11:$C$14,2,FALSE)
  ・検索値　TRUE　VLOOKUPの検索値として使用する。
  ・日付け判定用
    平日  B11=IF(AND($B$12=FALSE,$B$13=FALSE,$B$14=FALSE),TRUE,FALSE)
    土日  B12=OR($B$1="土曜日",$B$1="日曜日")
    祝日  B13=COUNTIF($A$23:$A$25,$A$1)>0
    年末年始  B14=OR($A$1>=$A$19,$A$1<=$A$20)
  ・計算表示
    平日　C11=IF(AND($B$12=FALSE,$B$13=FALSE,$B$14=FALSE),9000*$B$4+9000*1.2*$C$4+9000*1.5*$D$4,"")
    土日  C12=IF(OR($B$1="土曜日",$B$1="日曜日"),12000*$B$4+12000*1.2*$C$4+12000*1.5*$D$4,"")
    祝日  C13=IF(COUNTIF($A$23:$A$25,$A$1)>0,12000*$B$4+12000*1.2*$C$4+12000*1.5*$D$4,"")
    年末年始  C14=IF(OR($A$1>=$A$19,$A$1<=$A$20),18000*$B$4+18000*1.2*$C$4+18000*1.5*$D$4,"")
 ■日付け判定用に祝日と年末年始に分けました。
 ■日付け判定用、計算表示、休日設定はフォントを白にして隠しましょう。

（ppm） 2020/12/08(火) 23:09

 Sheet3 はこの処理のために作った表で、変更可能なら
 A列：祝日の日付を入力（年末年始を除く）
 B列：年末年始の日付を入力
 祝日と年末年始の列を分ける。

 単価
 C8 =IF(COUNTIF(Sheet3!B:B,B1),18000,IF(WORKDAY(B1-1,1,Sheet3!A2:A100)>B1,12000,9000))

 これでできます。

 参考まで
（笑） 2020/12/09(水) 00:19

 Sheet3 はどんな表なんですかね？

 2020/12/08(火) 18:36 の回答は
 A列には年末年始と祝日の日付がすべて入力してあり
 年末年始についてはB列に「年末年始」と入力してある、ということを前提にしています。

 なので、年末年始が 2021/1/3までなら、曜日に関係なく2021/1/1～1/3の日付はすべて入力しておく必要があります。

 2020/12/09(水) 00:19 の回答も同様で、B列には年末年始の日付をすべて入力。
 ついでに言っておくと、1行目は見出し、2行目から日付だとしての式です。

 以上 
（笑） 2020/12/09(水) 09:53

 （ppm） 2020/12/08(火) 23:09の回答で１行目の列がずれていました。
 既存の表で試されるときは参照セル位置を変更してください。

（ppm） 2020/12/09(水) 12:34

＞笑様

詳しく教えていただきありがとうございます。
シート3は会社の休みと祝祭日が記載された表があります
（こま） 2020/12/11(金) 10:00

＞ppm様

計算式など見やすく詳細に教えていただきありがとうございます。

この計算式をもとに挑戦してみます。
ありがとうございます。
（こま） 2020/12/11(金) 10:02

コメント返信：

[ 一覧(最新更新順) ]

[[20201124154002]] 『平日、祝祭日で単価を変更する』（こま） ページの最後に飛ぶ

[[20201124154002]]　『平日、祝祭日で単価を変更する』（こま）　ページの最後に飛ぶ