求助excel达人，数据统计

  • a
    a68032613
    遇到一个要求，搞的头大，求救表格达人指导一二

    有表1的明细，统计各设备的年租金到表B，注意：设备租赁60个月后，不再计算租金；部分设备是未来租赁的。表1：
    设备种类起租日期租期(月)月租费
    A2015/1/160500
    A2016/6/160500
    B2019/7/260500
    C2018/4/160500
    …60500
    Z2021/3/160500




    表2：
    租金2019年2020年2021年2022年2023年
    A
    B
    C
    …
    Z
  • 2
    2Bpencil
    回复1#a68032613
    没看懂楼主的需求是什么
  • a
    a68032613
    回复2#2Bpencil


    表1是设备统计明细表，要整理出来各设备每年的租赁费是多少
  • s
    shadowmage
    不知道楼主具体想要统计什么。
    但是提醒下楼主，excel里面有edate（）跟month（）两个函数。前一个可能符合你要求。
  • a
    a68032613
    回复4#shadowmage


    根据表1的信息，统计出来各类型设备未来5年，每年的租赁费是多少。比如统计2019年A设备的时候，需要把2020年开始租赁的A设备去除；统计2024年租赁费的时候，需要把已到期的设备去除，
  • x
    xuyn2003
    租金按天计算还是取整到月？
  • a
    a68032613
    回复6#xuyn2003


    取整到月即可
  • e
    eistein
    放个excel文件上来啊。
  • s
    shadowmage
    回复5#a68032613


    表1增加一列到期日，内容为：=edate(B2,C2)，下面依次拉一下；表2增加个每年的月份数据，填个if函数，判断下表1的到期日，小于就返回租金，大于就返回0。最后汇总下。

    到月份的话，可能“到期”的内容是=edate（month（B2），C2）
  • a
    a68032613
    工作簿1.xlsx(11.71 KB)

    上传附件
  • x
    xin.h
    一个思路，MIN(DATEDIF(起租时间,计算当年底,"m"),12）*月租金
    再加一个判断是否超过60个月，和当年租期未开始的条件就可以了吧

    哦，还不行，你是按设备类型统计的，还要再做个汇总
  • C
    Ceasser
    找你们公司会计问最简单。 iOS fly ~
  • x
    xhxdbxz
    我好象做过类似的东西，让我找找
  • b
    bladeknight
    sumifs啊
  • d
    dby864
    excel中有一系列专门的日期的函数，但是不是很熟悉，于是考虑笨办法：
    第一步，把起租日期序列话，比如2015年1月是第1个月，2016年1月是第13个月……
    序列号=（year（起租日）-2015）*12+month（起租日），假设为t月，租期为n个月，t+n月租期结束。
    具体年份，比如2019年，起止期间为第49月至第60月，其他年份类推，笼统分别用a、b表时起止期。
    第二步，算min（b-t，t+n-t，b-a，t+n-a）这四个数中最小的数，算2019的租金，总共有六种情况，图示如下
    min（b-t，t+n-t，b-a，t+n-a）>=0，租金为(min（b-t，t+n-t，b-a，t+n-a）+1)*月租金

    min（b-t，t+n-t，b-a，t+n-a）<0，租金为0


    第三步，修改参数a、b，分别算2010、2011……的租金
    第四步，数据透视表，出结果。
  • e
    eistein
    回复10#a68032613


    1）汇总表里的租金是按种类来的对吧？2）有没有设备没租出去的情况？比如B1，第一个租期只有20个月，下一次租到2021年3月就没租出去？
  • a
    a68032613
    回复17#eistein

    为方便，我填入实物信息，需要统计各设备未来5年每年的租金，租期都是连续的，到期后就部计算租金了。
    设备SN号设备种类起租日期租期(月)月租费
    D1LDK24A1手机2015/1/160500
    023D102WE手机2016/6/160500
    EJ21L0321E电脑2019/7/260500
    E291210EJE打印机2018/4/160500
    …60500
    E2144109E投影仪2021/3/160500
  • x
    xhxdbxz
    工作簿1.xlsx(15.87 KB)


    做了一下，供参考

    逻辑：租入月计算租金，到期月不计算租金。如：2015年1月1日租入，2015年1月算租金；2020年1月1日到期，2020年1月不计算租金。如果是相反的逻辑，简单修改公式即可。
  • w
    wailing
    还有个函数叫DATEDIF，也可以用
  • e
    eistein
    回复18#a68032613


    我还以为租期不连续，宏写了一半。。
  • 7
    78588371
    回复14#Tonyli0532

    这可不一定，我司财务连基本加减法公式都不会用，用计算器哦
  • a
    a68032613
    回复19#xhxdbxz


    太棒了，就是整个效果。
  • 花
    花菊
    mark iOS fly ~
  • y
    yaohoo
    数据透视 iOS fly ~
  • a
    alzeng
    1. Sub getSums()
    2. Dim Arr, Ary, k%, i%
    3. Dim Dic As Object, Str$

    5. Arr = Sheets("明细").Range("A1").CurrentRegion
    6. Set Dic = CreateObject("Scripting.Dictionary")

    8. For k = 2 To UBound(Arr)
    9. For i = 1 To Arr(k, 4)
    10. Str = Arr(k, 2) & Format(CDate(Application.EDate(Arr(k, 3), i - 1)), "yyyy年")
    11. Dic(Str) = Dic(Str) + Arr(k, 5)
    12. Next
    13. Next

    15. Arr = Sheets("汇总").Range("A1").CurrentRegion
    16. For k = 2 To UBound(Arr)
    17. For i = 2 To UBound(Arr, 2)
    18. Str = Arr(k, 1) & Arr(1, i)
    19. Arr(k, i) = Dic(Str)
    20. Next
    21. Next
    22. Sheets("汇总").Range("A1").CurrentRegion = Arr

    24. Set Dic = Nothing
    25. End Sub
    复制代码
  • a
    alzeng
    运行结果：
    2019-07-18_143628.jpg
  • e
    eistein
    回复26#alzeng


    高手。
  • a
    a68032613
    回复27#alzeng
    高手小尾巴~