会社で旅費精算書を作成しております。その中で関数について教えてください。(初心者なもので、よく分からない)宿泊の場合、在勤事業所又は出張先の地を午前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の関数について教えてくだ...
-
エクセルファイルがファイルの...
-
一つのセルに(例えばA1)入力...
-
エクセル vbaについて教えてく...
-
Excel 複数列 A列B列C列一致 D...
-
【マクロ】名前を保存する際に...
-
Excelについて教えてください
-
エクセル マクロ チェックボックス
-
エクセルであるセルに数字を入...
-
【Excel】数字を3倍にし、なお...
-
エクセル共有したが、アクセス...
-
【マクロ】顧客番号にて一致さ...
-
Excel分析ツールでのポアソン回...
-
エクセルで年休を管理する方法...
-
エクセルのデーターが2か月前の...
-
LOOKUP関数を使えばいいのでし...
-
エクセル②
-
エクセルを使っていて2024/5/15...
-
Excelでセルの値が同じか...
-
エクセルでの作業計算方法について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報
やはり、そうだったのですね。添付のものが見れませんm(__)mもう一度添付頂けないでしょうか。