首先,我要把你的意见与你的产出完全分开。 这是基于经验的,因为几年前,我为会计人员制定了相当复杂的会计电子表格,作为总分类账和P&L。

控制信息载于一份表格,《全球升温潜能值编码》是另一个表格,交易是另一个报表,宏观基本上通过,在另外四个表上编制资产负债表和收入/支出报表。

最初的尝试试图在投入方面操纵信息,但后来变成了梦.。 一旦投入和产出分开,就变得更容易管理。

换言之,有以下表格:

• People.
• Items.
• Transaction.
• Output.

头三个只是投入。 交易是一份清单,列出哪些项目是提供给人们的(许多对人的关系)。 然后,我会有一个如下宏观。

第一,完全清楚第四页(产出)。 接着,在《人民报》中,每名活跃人员都通过交易单,并为与其相关的任何交易创造出一个产出条目。

顺便说一句,因为你可能想保持历史,为已经离开的人保存记录。 这在《人民报》中将是某种旗帜。

作为这一进程的一部分,你或许需要研究这些物品和价格。

You can report on errors as part of the macro as well, such as Transaction entries that have no valid People or Items.

你们还可能想考虑某些人/项目可能具有相同的名称(甚至一个相同的项目可能定期改变价格)。 为此,最好给每个人提供独一无二的身份证和物品,以确保不存在错误识别的可能性。 这些独特的身份证将存放在交易单上。

Since the macro I discussed weighs in at 37K, I can t post the lot here. But this is the main processing bit which processes the transactions sheet and updates the accounts page with balances:

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Option Explicit

Public Const TxnSheet = "Txns"
Public Const TxnColId = "a"
Public Const TxnColDate = "b"
Public Const TxnColAcct = "c"
Public Const TxnColAmt = "d"
Public Const TxnColDesc = "e"
Public Const TxnColNotes = "f"
Public Const TxnRowStart = "2"

Public Const AcctSheet = "Accts"
Public Const AcctColReport = "a"
Public Const AcctColType = "b"
Public Const AcctColBold = "c"
Public Const AcctColItalic = "d"
Public Const AcctColFontPlus1 = "e"
Public Const AcctColOther2 = "f"
Public Const AcctColOther3 = "g"
Public Const AcctColOther4 = "h"
Public Const AcctColOther5 = "i"
Public Const AcctColLevel = "j"
Public Const AcctColSign = "k"
Public Const AcctColAcct = "l"
Public Const AcctColVal = "m"
Public Const AcctColNotes = "n"
Public Const AcctRowStart = "2"

  Process all transactions.

Sub ProcessTransactions()
    Dim TxnId As Integer
    Dim Balance As Double
    Dim WsTxn As Worksheet
    Dim WsAcct As Worksheet

    Dim RowTxn As String
    Dim RowAcct As String

    Dim RowTxn2 As String
    Dim RowTxn3 As String

    Dim StartDate As Date
    Dim EndDate As Date
    Dim CutoffDate As Date
    Dim PastCutoff As Boolean

      Get user-configurable stuff

    StartDate = GetConfig("start_date")
    EndDate = GetConfig("end_date")
    CutoffDate = GetConfig("cutoff_date")
    PastCutoff = False

      For filling in transaction IDs.

    TxnId = 1

    Set WsTxn = Worksheets(TxnSheet)
    Set WsAcct = Worksheets(AcctSheet)
    RowTxn = TxnRowStart


      Select the worksheet and cell so we can see what s happening.

    WsTxn.Select
    Range(TxnColAcct + RowTxn).Select
    Range(TxnColAcct + RowTxn).Show

      Process all transaction lines.

    Do While Range(TxnColAcct + RowTxn).Value <> ""
          Check for start of transaction (non-blank date).

        If Range(TxnColDate + RowTxn).Value <> "" Then
              Check date within range.

            If Range(TxnColDate + RowTxn).Value < StartDate Or Range(TxnColDate + RowTxn).Value > EndDate Then
                Range(TxnColDate + RowTxn).Select
                MsgBox "ERROR: ProcessTransactions: Date out of range"
                End
            End If

            If Range(TxnColDate + RowTxn).Value > CutoffDate Then
                PastCutoff = True
            End If

              Start of transaction, fill in transaction ID and increment.

            Range(TxnColId + RowTxn).Value = TxnId
            TxnId = TxnId + 1

              Check that transaction is balanced.

            RowTxn2 = FindNextTxn(RowTxn)
            RowTxn3 = PrevRow(RowTxn2)

            Balance = 0
            Do While RowTxn2 <> RowTxn
                RowTxn2 = PrevRow(RowTxn2)
                Balance = Balance + Range(TxnColAmt + RowTxn2).Value
            Loop
            If Balance > 0.001 Or Balance < -0.001 Then
                Range(TxnColAmt + RowTxn + ":" + TxnColAmt + RowTxn3).Select
                MsgBox "ERROR: ProcessTransactions: Unbalanced transaction"
                End
            End If
        Else
              Not transaction start, clear transaction ID column.

            Range(TxnColDate + RowTxn).Clear
        End If

          Get account line, error if account not in accounts worksheet.

        RowAcct = FindAccount(Range(TxnColAcct + RowTxn).Value)
        If RowAcct = "" Then
            MsgBox "ERROR: ProcessTransactions: Invalid account  " & Range(TxnColAcct + RowTxn).Value & " "
            End
        End If

          Update accounts value.

        If Not PastCutoff Then
            WsAcct.Range(AcctColVal + RowAcct) = WsAcct.Range(AcctColVal + RowAcct) + Range(TxnColAmt + RowTxn).Value
        End If

          Move to next transaction.

         Sleep 50
        RowTxn = NextRow(RowTxn)
        Range(TxnColAcct + RowTxn).Select
        Range(TxnColAcct + RowTxn).Show
    Loop

    Range(TxnColDate + RowTxn).Select
    Range(TxnColDate + RowTxn).Show
End Sub

It may not be that useful without knowing the sheet layout but it s the best I can do without being able to send you the whole workbook.