个人中心
gpt4 book ai didi

Excel根据先进先出计算销售价格

转载 作者：行者123 更新时间：2023-12-02 22:29:33 27 4
gpt4 key购买 nike

我有多个 Material 编号，但现在仅给出一个 Material 编号的示例。有一个类型列告诉我们是否在特定日期购买或出售了 Material 。我已经根据 Material 编号和日期对数据进行了排序。现在我想实现先进先出逻辑来计算已售 Material 的总价和单价。我现在已经在 Excel 中手动计算了。

MaterialNo  Type    Date    Qty   Price   Balance-Quantity **TotalPrice**   **UnitPrice**
XXXXXX     Buy      03-2017 1250    79.99998042 1250    99999.97552     79.99998042
XXXXXX     Sell     03-2017   20                1230    -1599.999608    -79.99998042
XXXXXX     Buy      04-2017 2200    79.99999667 3430    175999.9927      79.99999667
XXXXXX     Sell     04-2017 2375                1055    -189999.9721    -79.99998825
XXXXXX     Buy      05-2017  997    79.9999625  2052    79759.96261      79.9999625
XXXXXX     Sell     05-2017 2055                  -3    -164399.9229    -79.9999625
XXXXXX     Buy      06-2017 4749    79.99996153 4746    379919.8173      79.99996153
XXXXXX     Sell     06-2017 3550                1196    -283999.8669    -79.9999625
XXXXXX     Buy      07-2017  894    80.00001111 2090    71520.00993      80.00001111
XXXXXX     Sell     07-2017 2100                 -10    -167999.9213    -79.9999625
XXXXXX     Buy      08-2017 2495    137.210024  2485    342339.0098      137.210024
XXXXXX     Sell     08-2017 1900                 585    -260699.0455    -137.210024
XXXXXX     Buy      09-2017 2000    156.28      2585    312560           156.28
XXXXXX     Sell     09-2017 2652                 -67    -414454.56      -156.28
XXXXXX     Buy      10-2017  596    156.2805875  529    93143.23015      156.2805875
XXXXXX     Sell     10-2017  476                  53    -74389.55965    -156.2805875
XXXXXX     Buy      11-2017 5600    156.2800066 5653    875168.0371      156.2800066
XXXXXX     Sell     11-2017 1111                4542    -173627.0874    -156.2800066
XXXXXX     Buy      12-2017 1118    156.2800863 5660    174721.1365      156.2800863
XXXXXX     Sell     12-2017 1222                4438    -190974.2654    -156.2800863
XXXXXX     Buy      01-2018 2162    156.28      6600    337877.36        156.28
XXXXXX     Sell     01-2018 4188                2412    -654500.64      -156.28
XXXXXX     Buy      02-2018  792    156.2796819 3204    123773.5081      156.2796819
XXXXXX     Sell     02-2018 2602                 602    -406639.7323    -156.2796819
XXXXXX     Buy      03-2018  400    156.28      1002    62512            156.28
XXXXXX     Sell     03-2018  560                 442    -87516.8        -156.28

另一种没有负余额数量的场景

MaterialNo  Type    Date    Qty Price   Balance Quantity    TotalPrice  Unit Price
SSSSSSSS    Buy 06-2017 30  140.8   30  4224    140.8
SSSSSSSS    Sell    06-2017 30      0   -4224   -140.8
SSSSSSSS    Buy 07-2017 150 140.8   150 21120   140.8
SSSSSSSS    Sell    07-2017 16      134 -2252.8 -140.8
SSSSSSSS    Sell    08-2017 22      112 -3097.6 -140.8
SSSSSSSS    Buy 09-2017 150 207.4399333 262 31115.99    207.4399333
SSSSSSSS    Sell    09-2017 112     150 -15769.6    -140.8
SSSSSSSS    Buy 10-2017 250 207.4399    400 51859.975   207.4399
SSSSSSSS    Sell    10-2017 63      337 -13068.7158 -207.4399333
SSSSSSSS    Buy 11-2017 3260    207.4399991 3597    676254.3969 207.4399991
SSSSSSSS    Sell    11-2017 2655        942 -550753.167 -207.4399876
SSSSSSSS    Buy 12-2017 300 207.44  1242    62232   207.44
SSSSSSSS    Sell    12-2017 556     686 -115336.6395    -207.4399991
SSSSSSSS    Buy 01-2018 428 207.44002   1114    88784.32856 207.44002
SSSSSSSS    Sell    01-2018 448     666 -92933.11964    -207.4399992
SSSSSSSS    Buy 02-2018 -9  207.44  657 -1866.96    207.44
SSSSSSSS    Sell    02-2018 332     325 -68870.08188    -207.4400057
SSSSSSSS    Buy 03-2018 699 207.43997   1024    145000.539  207.43997
SSSSSSSS    Sell    03-2018 341     683 -70737.0462 -207.4400182

需要注意的一件事 - 当余额数量为负时，它应该根据上次购买的价格计算已售 Material 的总价格。如有任何问题请告诉我。

这是我尝试过的功能。

Dim prc As Double
Function fifoval(q As Range, Optional details As String) As Variant
Application.Volatile (True)
    Dim i As Integer
    Dim qstr As String
    Dim pstr As String
    Dim cqty As Integer
    Dim qty As Integer
    Dim ctr As Integer
    Dim dstr As String
    Dim amt As Double
    'Stop
    For i = 2 To q.Row - 1
        If Cells(i, 1) = Cells(q.Row, 1) And IsNumeric(Cells(i, 7)) Then
            Select Case Cells(i, 2)
                Case "Buy"
                    qstr = qstr & Cells(i, 4) & ","
                    pstr = pstr & Cells(i, 5) & ","
                Case "Sell"
                    qty = Cells(i, 4)
                    Do While qty > 0

                        cqty = Val(qstr)
                        If (cqty = 0) Then
                            fifoval = "Not enough balance"
                            Exit Function
                        End If
                        Select Case True
                            Case cqty = qty
                                qstr = Replace(qstr, cqty & ",", "", , 1)
                                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                                qty = qty - cqty
                            Case cqty > qty
                                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                                qty = 0
                            Case cqty < qty
                                qstr = Replace(qstr, cqty & ",", "", , 1)
                                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                                qty = qty - cqty
                            Case cqty = 0
                                fifoval = "Not enough balance"
                                Exit Function
                        End Select
                        ctr = ctr + 1
                        If ctr > 1000 Then End: Stop
                    Loop
            End Select
        End If
    Next i
    qty = Cells(q.Row, 4)
    Do While qty > 0

        cqty = Val(qstr)
        If (cqty = 0) Then
            fifoval = "Not enough balance"
            Exit Function
        End If
        prc = Val(pstr)
        Select Case True
            Case cqty = qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty & ",", "", , 1)
                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                qty = qty - cqty
            Case cqty > qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                qty = 0
            Case cqty < qty
                dstr = dstr & IIf(dstr = "", "", " + ") & cqty & " * " & prc
                amt = amt + cqty * prc
                qstr = Replace(qstr, cqty & ",", "", , 1)
                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                qty = qty - cqty
        End Select
        ctr = ctr + 1
        If ctr > 1000 Then End: Stop
    Loop
    If details = "" Then
        fifoval = amt
    Else
        fifoval = dstr
    End If
End Function

最佳答案

我需要做同样的事情，并且没有 VBA，我想出了一个需要创建一些额外列的解决方案。

在我的示例中，第 367 行的列如下。

数据:

  • D 列:日期。
  • E 列:产品名称。
  • 第一栏:数量。如果是销售，则数量为负数，因此如果您想使用此公式提供负数销售数量，则需要创建一个新列。
  • J 列:价格。
  • 第 N 列:汇率(我想您不需要它)。

公式:

  • AA 列:该行和操作日期中的当前产品数量:

    =+IF(I367="","",MAX(0,MIN(I367,SUMIF(E:E,E367,I:I)-
    SUMIFS(I:I,D:D,">"&D367,E:E,E367,I:I,">0"))))

  • AB 列:操作日期所在行当前产品数量的成本价:

    =IF(AA367="","",ROUND(AA367*N367*J367,2))

  • AC 栏:操作日期已售出产品数量的成本价:

    =IF(I367="","",IF(I367>0,ROUND((I367-AA367)*N367*J367,2),0))

  • AD栏:操作当日已售出产品数量的售价: =+IF(I367<0,ROUND(-I367*J367*N367,2),"")

获得这些列后，您只需按列进行总计:

  • AB 列总计:您存储的所有 Material 的成本。
  • AC 列总计:购买您已售出的 Material 所花费的所有资金。
  • AD 列总计:您已售出的所有产品的收入。

您需要重新制定公式以匹配您的列，但除此之外它应该可以工作。

关于Excel根据先进先出计算销售价格，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/50113568/

27 4 0
文章推荐： gcc - 连接 SDL2 和 Clion
文章推荐： javascript - 在谷歌浏览器扩展中加载外部javascript
文章推荐： VBA:嵌套字典是一个很好的解决方案吗？
文章推荐： javascript - 如何从异步调用返回响应？
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com