会社で旅費精算書を作成しております。その中で関数について教えてください。(初心者なもので、よく分からない)宿泊の場合、在勤事業所又は出張先の地を午前6時前に出発した場合、750 円
/回を加算支給する。(1回のみ)在勤事業所又は出張先の地に午後10時を超えて到着した場
合、1,150 円/回を加算支給する。(1回のみ)
添付させて頂きましたExcelのセルW20に教えて頂いた関数を入力しました。
=(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
K18は日付の入力。今回宿泊だったので日にちまたぎです。しかし22時越えはしていないのでW20には反映されるはずがないのですが、何故か金額が反映されてしまっており、どうしたらいいか分かりません。どうか、ご教示お願い致します。
No.4ベストアンサー
- 回答日時:
前回のご質問に回答した者です。
前回回答でご説明したとおり、>仕方ないので、当初出発日より後の日付けの到着日があれば日超えしたとみて、加算支給するという数式
を回答いたしました。つまり、仕方なく作成したものを、そのまま実務に適用するのは無理があります。数式の内容を理解したうえで、ご自身で調整されることが前提です。
>22時越えはしていないのでW20には反映されるはずがないのですが、・・・
>今回宿泊だったので日にちまたぎです
冒頭の説明のとおり、22時越えしているかどうかに拘らず、当初出発日より後の日付の到着日があれば加算支給されるという数式になっているので、「反映されるはずがない」つもりでも「反映される」ということになります。
また、今回の説明で前回のご質問には登場しない条件「宿泊の場合」という条件がついています。
EXCELはAIではないので、出発、到着の時刻だけでは宿泊したか否かの判断はできません。
日にちまたぎが発生したとして「出発日に帰着する予定だったが、遅くなって日にちまたぎになってしまった」と「到着予定時刻が初めから日またぎになる予定で、到着後宿泊した」との違いを時刻だけからどのように判断するのでしょうか?
そこで、
>宿泊の場合、在勤事業所又は出張先の地を午前6時前に出発した場合、750 円/回を加算支給する。
という条件のうち「宿泊の場合」は【無視する】ことにします。仮に、別セルに「宿泊の有無」というセルがあって、これに「有り・無し」が記録されているなら、それで判定する数式を作成することは可能ですが、何しろご質問者が掲出した画像が全く見えないので判断のしようがありません。
またまた「仕方がないので」、少しでもご質問者のニーズに近づける数式にするため、日またぎが発生した場合、6:00より前の時刻で到着していれば、前日からの出張の帰着が翌日になったものと判断することにしてみました。
いずれにしても以下の数式は、
>旅費精算書を作成しております
という旅費精算書ごとに、早朝出発1回のみ、深夜到着(日越え含む)1回のみの加算支給となります。このため日またぎで連日の出張を1枚の旅費精算書で作成する場合と、1日ごとに複数の旅費請求書を作成する場合の合計金額とが異なる場合があります。
数式は以下のとおりです。
=((SUMPRODUCT(LARGE(($J$5:$J$18*100+$K$5:$K$18-($J$5*100+$K$5))*($I$5:$I$18="着")*($L$5:$L$18*60+$M$5:$M$18<"6:00"*1440),1))+SUMPRODUCT((($L$5:$L$18*60+$M$5:$M$18)>"22:00"*1440)*($I$5:$I$18="着")))>0)*1150+(SUMPRODUCT((($L$5:$L$18*60+$M$5:$M$18)<"6:00"*1440)*($I$5:$I$18="発")*($J$5:$J$18<>""))>0)*750
※今回の回答は「宿泊の場合」を無視したので、全くの余談ですが、旅費規則の解釈が難しいです。「宿泊の場合、在勤事業所・・・場合、750円・・・」という表現を、条件の併記と捉えれば、「宿泊の場合または在勤事業所・・・場合、750円/回を・・・」という解釈になります。しかし、条件の追加と捉えれば「「宿泊の場合かつ在勤事業所・・・場合、750円/回を・・・」という解釈になりどちらともとれます。このような旅費規則を数式にするのは厳密な意味を理解しないと不可能です。
goomaniaさん。いつも有難うございます。添付画像が見づらくて申し訳ございません。とても助かりました。全く無知で申し訳ございません。関数を勉強したいと思います。goomaniaさんは凄いですね。
No.3
- 回答日時:
>教えて頂いた関数を入力しました
教えてもらった関数の意味・内容を理解して、使用していますか?
理解していればご自身で試行錯誤できると思いますが。
他人の提示したものを、使用するときは、ちゃんと理解して上で使ってください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(プログラミング・Web制作) 【フローチャートの書き方について】 勤務年数と勤務場所を入力し,給与額を表示するフローチャートを作成 2 2022/10/04 12:11
- その他(Microsoft Office) Excelで時間計算(負) 8 2023/02/26 05:47
- Excel(エクセル) エクセル 条件に合う日付に入力された時間数の合計したい 4 2022/06/17 22:18
- 会社・職場 夜勤中の眠気について 3 2022/09/02 07:49
- Excel(エクセル) 出退勤管理の遅刻・早退時間について 3 2023/08/10 15:33
- 労働相談 有給の計算について 3 2023/05/20 13:11
- 所得・給料・お小遣い 数ヶ月前から夫にお小遣いとは別で 会社から支給される交通費を全額渡しています そして現場が変わり交通 2 2022/10/04 11:54
- 会社・職場 大学勤務の彼氏が出張費(研究費)を2回ほど不正にもらっていました。(金額にすると56000円ほど) 4 2022/05/15 21:30
- その他(悩み相談・人生相談) 大学勤務の彼氏が出張費(研究費)を2回ほど不正にもらっていました。 宿泊費をホテルにして、実際は実家 4 2022/05/15 15:52
- その他(悩み相談・人生相談) 大学勤務の彼氏(29)の出張費について。 彼は出張の際に宿泊先をホテルにして、実際は実家に泊まってい 6 2022/05/16 11:14
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelはなんで先頭の0を消すん...
-
Excel元に戻す方法を教えてくだ...
-
【Microsoft Office Excel Comp...
-
Excelが固まってしまった。
-
西暦や和暦の表示をyyyymmdd表...
-
Excel 2019 のピボットテーブル...
-
【関数】スペースがいくつ入っ...
-
【Excel】セル内の時間帯が特定...
-
excelの不要な行の削除ができな...
-
Excelのオートフィル
-
別シートからの文字を変更
-
Excelのセルを飛ばして入力する
-
Excel初心者です。 詳しい方、...
-
エクセルの行の抽出について質...
-
Excel初心者です。 詳しい方、...
-
【マクロ】エクセルにかいてあ...
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシート クエリ関数 1...
-
エクセルで指定した日付、店舗...
-
Excelのグラフ軸について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報
やはり、そうだったのですね。添付のものが見れませんm(__)mもう一度添付頂けないでしょうか。