エクセル関数について

X18にPM22：00以降の帰着の場合1150円/回加算支給させたいのですが
関数が分からないです。教えてください。よろしくお願い致します。

質問者からの補足コメント

• 

  有難うございます。帰着時間に25：32（仮の時間）と入れて関数=(MOD(帰着時間,1)*24=>22)OR(MOD(帰着時間,1)*24<6)*1150と入力したのですが0円表示になります。どうしたらいいでしょうか。

  No.1の回答に寄せられた補足コメントです。 補足日時：2023/05/11 13:28

• 

  丁寧に説明頂き有難うございます。旅費規則では出張先の地を午前６時前に出発した場合、
  750 円／回を加算支給する。在勤事業所又は出張先の地に午後１０時を超えて到着した場合、
  1,150 円／回を加算支給する。となっております。このような場合ですと、どちらの数式を当てはめればよろしいでしょうか。関数が難しくて悩んでおります。宜しくお願い致します。

  No.2の回答に寄せられた補足コメントです。 補足日時：2023/05/12 08:35

No.4 ベストアンサー 回答者： goomania 回答日時： 2023/05/12 17:30

No.2,3です。

>旅費規則では出張先の地を午前６時前に出発した場合、750 円／回を
>加算支給する。在勤事業所又は出張先の地に午後１０時を超えて到着
>した場合、1,150 円／回を加算支給する。

という条件だとご説明がありましたが、1日のうちに複数回の適用があるかどうかの説明がありません。例えば以下のようなケースはどうなるのでしょうか?(添付画像に入力した内容と同じです)

5/12 4:30 出張先Aを出発･･･①
5/12 5:30 出張先Bに到着･･･②
5/12 5:55 出張先Bを出発･･･③
5/12 7:00 出張先Cに到着･･･④
5/12 21:00 出張先Cを出発･･･⑤
5/12 22:05 出張先Dに到着･･･⑥
5/12 22:30 出張先Dを出発･･･⑦
5/12 23:00 事業所に帰着････⑧

上記の例は、①③が出張先の地を6時前に出発するケース、⑥⑧が在勤事業所又は出張先の地に午後１０時を超えて到着するケースです。出発時刻、帰着時刻とも複数回加算支給があるということなのか、出発時刻、帰着時刻、各々1日1回の適用なのかが判りません。従って正確な数式をお示しできません。

旅費規則の文面だけ見れば、1日に複数回の支給も認められるように思います。しかし、このような特殊な状況を想定していないとも考えられます。
また、午後１０時を超えて到着というのは、翌日になることもあるわけですが、翌日何時までなら支給されるのか判りません。

仕方ないので、当初出発日より後の日付けの到着日があれば日超えしたとみて、加算支給するという数式を考えてみました。但し、日付は年を跨がない(12/31出発、翌年1/1到着というようなケースはない)ものとします。添付画像のような入力シートがある場合、取り敢えず各1日1回だとして加算支給額をX18セルに表示する数式は、

=(SUMPRODUCT((((J5:J18*100+K5:K18-($J$5*100+$K$5)>0)*1440+L5:L18*60+M5:M18)>"22:00"*1440)*(I5:I18="着"))>0)*1150+(SUMPRODUCT(((L5:L18*60+M5:M18)<"6:00"*1440)*(I5:I18="発")*(J5:J18<>""))>0)*750

となります。これが、複数回の支給が認められる場合は、

=SUMPRODUCT((((J5:J18*100+K5:K18-($J$5*100+$K$5)>0)*1440+L5:L18*60+M5:M18)>"22:00"*1440)*(I5:I18="着"))*1150+SUMPRODUCT(((L5:L18*60+M5:M18)<"6:00"*1440)*(I5:I18="発")*(J5:J18<>""))*750

という数式になります。
EXCEL内部では小数点は2進数に変換されて計算が行われます。時間表示は1日24時間として、少数として2進数に変換されるため、無限小数となるケースがあり、丸めによる計算誤差が発生する可能性があります。このため、時間標記は0:00から何分経過したか計算し、1440を掛けて全て整数化して計算しています。

なお、旅費規則の午前6時前は「6時以前」ではなく「6時より前」と解釈しました。これば到着時刻が「午後10時を超えて」となっており、「記載時刻を含まない」に統一するためです。
つまり、数式中の時刻との比較では「=」は付かず、不等号のみになります。

この回答へのお礼

有難うございます！！すごいです！！

お礼日時：2023/05/15 12:29

No.3 回答者： goomania 回答日時： 2023/05/12 02:42

No.2です。

連投失礼します。前回回答の文中に、

･･･2つの帰着時間(1つしかない場合は両方同じ時間)を入れても加算金額を正しく計算できません。･･･

という一文がありますが、誤解を招く表現でした。

(1つしかない場合は両方同じ時間ですが、これを)2つの帰着時間に分けて入れても加算金額を正しく計算できません。

という意味です。

1つしかない場合は同じ時間になることから数式③は数式①と同じになり、正しく計算できます。

冒頭の表現だと、1つしかない場合両方同じ時間をいれても正しく計算できないという誤解を与える懸念があります。
お詫びして、訂正いたします。

No.2 回答者： goomania 回答日時： 2023/05/12 02:26

No.1さんの回答にある数式はOR関数と不等号の使い方に誤りがあると思います。

EXCELの標準関数でORを用いる場合は

OR(条件1,条件2,条件3･･･)

という書き方になります。No.1さんの記述方法はVBAで用いられる書き方です。また、不等号は等号の前に記述しなければいけません。つまり、22時以降であれば「=>22」ではなく「>=22」となります。

従って、数式を

＝OR(MOD(帰着時間,1)*24>=22,MOD(帰着時間,1)*24<6)*加算金額･･･①

に修正する必要があります。あるいはNo.1さんの回答の数式に出来るだけ寄せたものにすると、

＝((MOD(帰着時間,1)*24>=22)+(MOD(帰着時間,1)*24<6))*加算金額･･･②

となります。

ただ、ご質問者の掲出画像が判別不能なので、良く判らないのですが、「帰着時間による加算金が1日に2回発生することはない」という前提でよいのでしょうか?
例えば、以下のようなケースです。

5月12日9:00に出発して帰着時間が5月13日の午前1:00となり、5月13日は9:00に出発して帰着時間が23:30になった。

上記の場合、出発日基準で計算するなら、5月13日の午前1:00の帰着時間は5月12日分として計算することになり、「帰着時間による加算金が1日に2回発生することはない」わけですが、これを帰着日基準で計算すると、5月13日は帰着時間による加算金が2回発生することになります。

仮に帰着日基準で計算するというのであれば、帰着時間が複数存在することもあるため、①の数式を、

＝OR(MOD(帰着時間大,1)*24>=22,MOD(帰着時間小,1)*24<6)*加算金額･･･③

として、2つの帰着時間(1つしかない場合は両方同じ時間)を入れても加算金額を正しく計算できません。一方②の数式を

＝((MOD(帰着時間大,1)*24>=22)+(MOD(帰着時間小,1)*24<6))*加算金額･･･④

とした場合、加算金額を正しく計算することができるように見えます。

但し、上記の例は、あくまでも出発時間が6時より前でなく、22時以降後でないという例を示しただけなので、出発時間が6時より前だったり、22時以降だった場合どうなるのか、ご質問者にお示しいただかない限り何とも言えないということになります。

No.1 回答者： angkor_h 回答日時： 2023/05/11 12:44

＝(MOD(帰着時間,1)*24=>22)*加算金額

これを追加すればよいです。

なお、これでは0時を過ぎると加算が無いので、
例えば、翌日の6時前までとする場合は、

＝(
(MOD(帰着時間,1)*24=>22)
OR
(MOD(帰着時間,1)*24<6)
)*加算金額