Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
有給休暇の繰越残の計算式について
投稿日時: 21/06/02 13:25:51
投稿者: たんしお

有給休暇の繰越残の計算式について
 
付与基準日で月別に4月から3月まで(シート別)の1年間で有給休暇管理表を作成しております。
その時の前年繰越残の計算式がわからず教えていただければと思います。
表は下記の通りです。
 
A1 付与処理日(給与締め基準) 末締
 
2行目以降
  A   B   C     D    E    F   G    H    I    J
社員番号 氏名 付与基準日 前年繰越 当年付与 当年使用 消滅日数 残日数 前年繰越残 当年付与残
 
D 手入力(前回ファイルのHを参照)
E 数式(勤続年数と出勤日数で計算)
F 数式(別シートの取得表から集計)
G 手入力(前回ファイルの前年繰越残が当年使用より残っている場合に消滅)
H IF(A2<>"",SUM(D2:E2)-SUM(F2:G2),"")
I IFERROR(IF(F2>D2,0,D2-F2),"")
 
このファイルを作成する月と4月の付与の方は、この表で問題なかったのですが、
5月以降は付与を処理する際に、消滅する前々年度付与が残っていると
Iの数式だとうまく計算されないことに気づきました。
うまく計算する方法があれば教えていただければと思います。
 
 
 
 
 

回答
投稿日時: 21/06/02 14:52:47
投稿者: Suzu

減数 に、消滅日数 の 項目を加えれば良いのでは

投稿日時: 21/06/02 17:02:31
投稿者: たんしお

Suzu 様
 
消滅日数の数式を入れたらよかったんですね。ありがとうございます。
 
追加での質問で申し訳ないのですが、
もし、6月付与予定の方で、4月のファイル作成時点の付与前に前々年度付与残を持っている場合
Dの前年繰越の日数(昨年の残日数)が15日(前々年度付与が4日 前年付与が11日)
今の数式だとIの前年繰越残が15日となってしまうんですが、
うまく回避する方法はありますか
 
 
 
 

回答
投稿日時: 21/06/02 23:19:49
投稿者: 半平太

 有給休暇の繰越残計算表をどう作成・運用しているのかよく分からないです。
  
 懸案のケースについて 4,5,6月の推移がどうなればいいのか
 具体的な日付および日数を入れてアップしてください。
 

回答
投稿日時: 21/06/03 09:46:09
投稿者: QooApp

当期に社員が使用した有給 <  全前年度付与残有給 = 消滅する有給あり (消滅日数警告)
当期に社員が使用した有給 >= 全前年度付与残有給 = 消滅する有給なし
 
ってことで解決しませんか?具体例が想像できてないので検討違いなこと言ってたらごめんなさい。

回答
投稿日時: 21/06/03 10:29:06
投稿者: Suzu

引用:
もし、6月付与予定の方で、4月のファイル作成時点の付与前に前々年度付与残を持っている場合
Dの前年繰越の日数(昨年の残日数)が15日(前々年度付与が4日 前年付与が11日)
今の数式だとIの前年繰越残が15日となってしまうんですが、
うまく回避する方法はありますか

 
その何が問題なのか理解できかねます。
 
6月付与なのですから、
4月の段階では、前々年度 4 + 前年付与 11 であれば、前年繰越 15 で良いのでは?
6月の段階では、消滅日数に 手入力で、4 が入って 11 となるのでしょうけど。
 
前々年度 が、云々は、今回のご質問内の表中で管理できる項目はありませんよね。
 
どの様な管理方法を採っているか判りませんが、
【消滅日数】を手入力を行っている事から、前々年度を含め考慮し【消滅日数】を手入力しているのだと
推測しました。
 
 
現状と理想を含め提示して貰えれば、解決案を出せるとは思いますが、
今出ている情報では、案の出しようもありません。
 
 
どんな管理を行いたいかにも拠りますが、私が行うなら
   A      B    C    D    E    F    G    H    I    J    K    L    M    N    O
 1  2021年度  有給休暇取得状況
 2
 3                 4月  5月  6月  7月  8月  9月 10月 11月 12月  1月  2月  3月
 4 氏名    鈴木太郎  繰越数  25   23   23   23   23   23   23   38   38   38   38   38
 5 基準月    10月   取得数                                20
 6 取得数     20    使用数   2    0
 7 前々年繰越    5   消滅数                                 5
 8 前年繰越    20    残数  23   23   23   23   23   23   38   38   38   38   38   38
 9
10 氏名    佐藤次郎  繰越数  14   26   24   24   24   24   24   24   24   24   24   24
11 基準月     4月   取得数  14
12 取得数     14   使用数  1    2
13 前々年繰越    2   消滅数  1
14 前年繰越    12    残数  26   24   24   24   24   24   24   24   24   24   24   24

 
こんな感じにするでしょうか。。
 
繰越数 D4 は手入力、E4 : =D8 →
取得数、使用数、消滅数 手入力
残数 D8 : =SUM(D4:D5)-SUM(D6:D7) →
 
基準月と、前々年度繰越数、前年度繰越数 を表内に持っておけば、
取得、消滅 の手入力時に 間違いにくいでしょうから。
 
月ごとに、シートを分けるのは 個人的には使いにくいですね。

投稿日時: 21/06/04 14:46:11
投稿者: たんしお

[quote="たんしお"]有給休暇の繰越残の計算式について
 
皆様 ご回答いただきありがとうございます。
現状の説明が不足しており、申し訳ありません。
現状の表に数字を入れました。
ご指摘いただきましたとおり、この表だとDの前年繰越に前々年度 4日が含まれていても、その内訳を管理する欄を設けてなかったため、今の数式だとIが15日になります。
前々年度付与4日と前年付与11日の欄を設定した場合、下記のような結果が出るようにするには、理想が多いので、手入力がいいでしょうか
 
4月ファイル作成時点(現状)
 
A1 付与処理日(給与締め基準) 末締
 
2行目以降
  A   B  C    D    E    F    G   H(=I+J) I   J
社員番号 氏名 基準日 前年繰越 当年付与 当年使用 消滅日数 残日数 前年繰越残 当年付与残
   1   ああ  6月    15    0     0     0   15    15    0
 
4月ファイル作成時点(理想)
 A   B   C    D    E    F    G   H(=I+J) I    J
社員番号 氏名 基準日 前年繰越 当年付与 当年使用 消滅日数 残日数 前年繰越残 当年付与残
  1   ああ   6月    15    0     0     0   15    4     11
 
6月付与処理時点(理想)
 A   B   C    D    E    F    G   H(=I+J) I    J
社員番号 氏名 基準日 前年繰越 当年付与 当年使用 消滅日数 残日数 前年繰越残 当年付与残
   1   ああ  6月    15    12     2    2    23    11    12
               
 
D 手入力(前回ファイルのHを参照)
E 数式(勤続年数と出勤日数で計算)
F 数式(別シートの取得表から集計)
G 手入力(前回ファイルの前年繰越残が当年使用より残っている場合に消滅)
H IF(A2<>"",SUM(D2:E2)-SUM(F2:G2),"")
I IFERROR(IF(F2>D2,0,D2-F2),"")
J IFERROR(H2-I2,"")

回答
投稿日時: 21/06/04 16:58:04
投稿者: Suzu

6月の 処理後 にて、前年が前々年、当年が前年 へ シフト するのでしょうか?
 
そうであれば、
4月の作成の段階の数値で、当年付与残 11 前年繰越が 4 であり、
その前年繰越の「4」が6月 の 当年使用 2、消滅 2 に現れるのではないのでしょうか?
 
数字の相関性について考慮し、入力する値 を決め、数式を決めましょう。

回答
投稿日時: 21/06/04 17:16:29
投稿者: 半平太

付与年別に管理するので、下記の様なレイアウトにする。

【4月】
行 ____D____ ___E___ ___F___ ____G____ ____H____ __I__ ___J___ ___K___ ___L___ ___M___
 1 前々年残  前年残  繰越計  当年付与  当年使用  消滅  残日数  前々残  前年残  当年残 
 2       4      11      15         0         0     0      15       4      11       0

【6月】                                                                                    
 1           前年残  繰越計  当年付与  当年使用  消滅  残日数  前々残  前年残  当年残 
 2       4      11      15        12         2     2      23       0      11      12

(1) D2セル 手入力。前月と同値。ただし、新年度では前月の前年付与残(L列)をコピーしてくる
(2) E2セル 手入力。前月と同値。ただし、新年度では前月の当年付与残(M列)をコピーしてくる
(3) F2セル =SUM(D2:E2)
(4) G2セル 数式(勤続年数と出勤日数で計算)
(5) H2セル 数式(別シートの取得表から集計)
(6) I2セル 手入力。前月と同値。ただし、新年度では0とする。
(7) J2セル =F2+G2-H2-I2
(8) K2セル =IF(G2,0,MAX(0,D2-H2))
(9) L2セル =MAX(0,E2-MAX(0,(H2-(D2-I2))))
(10) M2セル =J2-K2-L2

投稿日時: 21/06/07 15:53:52
投稿者: たんしお

半平太 様 Suzu 様
回答いただきました皆様
ありがとうございました。
理想通りに表示できました。ありがとうございます。