向高手们请教一个Excel问题

goooo

向excel高手们请教一个问题。

如图所示，cell H7 = 年薪 / 12 x 本财年工作的月份数，2022年也是。从2023财年开始，需要在上一财年基础上加上2.5%的工资涨幅，以后各年也是。我费了老大力气，还是不能用一个统一的公式，自动计算出每个人每一年的工资。但是我老板坚决认为这是可以用统一公式解决的。请注意，表格的格式不能变。需要计算的是highlighted fields

请问谁有高招吗？多谢了

老婆是CPA

上拖拖下拖拖不行嘛 啥是 统一公式

鱼羊鲜

if （年》=2023 ，+2.5%，正常算法）
这么说你明白吗

both

J7=B7*1.025
然后鼠标选择J7向右拖到L7
鼠标选择J7K7L7，向下拖到第十行

kaixin123

所以B列是年薪吗？
如果是的话同意楼上的
J7=$B7*1.025
然后向右向下拖J7的单元格就好了

goooo

计算需要参照start date and end date of each staff, for example:

cell H7 - 第一个员工 - FY2021：
1）首先要计算出第一年工作了几个月，我想到的公式是 DATEDIF(C7,H4,"M")+1=5
2）然后再乘以每月工资，就是=(DATEDIF(C7,H4,"M")+1)*B7/12=25000

cell I7 - 第一个员工 - FY2022：

1) 计算出第2年工作了几个月: 这时候就发现问题的复杂了，因为每个员工开始和结束工作的年份不一样，如果只能按照起始时间计算，我想到的公式是：（后面简称公式一）
=IFS((YEAR(I4)-YEAR($D$7))>1,"",DATEDIF($C$7,I4,"M")<12,DATEDIF($C$7,I4,"M"),DATEDIF($C$7,I4,"M")>12,12)

2）然后再带入工资的部分：就变成了： =公式一*B7/12

cell J7 - 第一个员工 - FY2023：
1）计算月份：=公式一
2）工资的部分要加上CPI：=公式一*B7/12*（1+B）

cell K7 - 第一个员工 - FY2024：
1）计算月份：=公式一
2）工资的部分要叠加CPI：=公式一*B7/12*（1+B2）*（1+B）

cell L7 - 第一个员工 - FY2025：
到了这一步，发现还要考虑结束日期的问题，就开始头大了，发现思路可能不对

所以才过来向高人请教了.

goooo

如果只是点击第一的单元格然后拖动复制公式，当然容易，可是关键是老板要一个能根据条件变化而自动计算的公式，例如如果员工工作的开始日期从1 FEB 2021 变成了 1 JUNE 2023，那么计算结果也要跟着变的那种公式。同理，工作结束日期也有可能变。

刮胡刀

你想太多了，这种highlevel forecast的东西就参考楼上的拉拉格子好了
tricky的地方就在于第一年和第二年的工作月数，搞不来的话做个辅助列隐藏起来好了。
这帐只能简单算个大概，超过20个字符的方程就不是会计该做的事

刮胡刀

H7=IF(C7>"30/06/2021",0,ROUND(("30/06/2021"-C7),0)*(B7/12))
I7=IF(H7>0,B7,IF(AND(H7=0,C7>"30/06/2022"),0,ROUND(("30/06/2022"-C7),0)*(B7/12)))
J,K,L就直接1.025*I7，不带提前一年找工作的

goooo

刮胡刀 发表于 2022-4-6 21:41
H7=IF(C7>"30/06/2021",0,ROUND(("30/06/2021"-C7),0)*(B7/12))
I7=IF(H7>0,B7,IF(AND(H7=0,C7>"30/06/2022 ...

非常感谢

goooo

刮胡刀 发表于 2022-4-6 21:22
你想太多了，这种highlevel forecast的东西就参考楼上的拉拉格子好了
tricky的地方就在于第一年和第二年的 ...

谁说不是呢。我向老板解释了，研究公式费的力气太大，没必要。但是老板坚持要用公式，虽然他自己不会，但是坚持认为有这么一个公式，可以又简单又高效的解决这种计算。

seaskydl

就是顶多半个小时的事，重要的是要先搞清楚逻辑是什么，不要管excel

freshoffboat

H7填入，其他表格直接拖动即可
=IFERROR(IF(H$4<=DATEVALUE("2023/6/30"),IF(DATEDIF($C7,H$4,"m")<12,DATEDIF($C7,H$4,"M")*($B7/12),DATEDIF(IF(MONTH(H$4)>6,YEAR(H$4)&"-6-30",(YEAR(H$4)-1)&"-6-30"),H$4,"M")*($B7/12)),IF(DATEDIF($C7,H$4,"m")<12,DATEDIF($C7,H$4,"M")*($B7/12),DATEDIF(IF(MONTH(H$4)>6,YEAR(H$4)&"-6-30",(YEAR(H$4)-1)&"-6-30"),H$4,"M")*($B7/12))*POWER(1+$B$2,ROUNDUP(DATEDIF("2023/6/30",H$4,"m")/12,0))),"")
自己验证下哈

goooo

freshoffboat 发表于 2022-5-27 10:48
H7填入，其他表格直接拖动即可
=IFERROR(IF(H$4

太感谢了

ozkids2011

靠，拿多少钱办多少事，这样的SB老板都被你们给宠坏了