AI 提示词：Excel宏生成

以下是一个能实现将多个财务表格合并，并生成季度损益表的Excel VBA宏代码：

Sub 合并财务表格并生成季度损益表()

    Dim ws合并 As Worksheet
    Dim ws结果 As Worksheet
    Dim 当前工作表 As Worksheet
    Dim 最后行 As Long
    Dim 最后列 As Long
    Dim 合并行 As Long
    Dim 起始行 As Long
    Dim 文件夹路径 As String
    Dim 文件名 As String
    Dim 工作簿 As Workbook
    Dim 源范围 As Range
    
    ' 新建一个“合并”工作表，用于存储所有合并数据
    On Error Resume Next
    Set ws合并 = ThisWorkbook.Sheets("合并")
    If ws合并 Is Nothing Then
        Set ws合并 = ThisWorkbook.Sheets.Add
        ws合并.Name = "合并"
    Else
        ws合并.Cells.Clear
    End If
    On Error GoTo 0

    ' 起始行号设置
    合并行 = 1

    ' 设置该工作簿内的文件夹路径及导入数据
    文件夹路径 = Application.InputBox("请输入存储财务表格的文件夹路径：", Type:=2)
    If 文件夹路径 = "" Then Exit Sub

    If Right(文件夹路径, 1) <> "\" Then 文件夹路径 = 文件夹路径 & "\"

    文件名 = Dir(文件夹路径 & "*.xls*")
    
    Do While 文件名 <> ""
        Set 工作簿 = Workbooks.Open(文件夹路径 & 文件名)
        
        ' 假设每个源工作表的数据在第一个工作表中
        Set 当前工作表 = 工作簿.Sheets(1)
        最后行 = 当前工作表.Cells(Rows.Count, 1).End(xlUp).Row
        最后列 = 当前工作表.Cells(1, Columns.Count).End(xlToLeft).Column

        ' 将数据复制到合并表中
        Set 源范围 = 当前工作表.Range(Cells(1, 1), Cells(最后行, 最后列))
        源范围.Copy Destination:=ws合并.Cells(合并行, 1)

        ' 更新下一行起始行号
        合并行 = ws合并.Cells(Rows.Count, 1).End(xlUp).Row + 1
        
        ' 关闭工作簿，避免占用内存
        工作簿.Close SaveChanges:=False
        文件名 = Dir
    Loop

    ' 新建“季度损益表”工作表
    On Error Resume Next
    Set ws结果 = ThisWorkbook.Sheets("季度损益表")
    If ws结果 Is Nothing Then
        Set ws结果 = ThisWorkbook.Sheets.Add
        ws结果.Name = "季度损益表"
    Else
        ws结果.Cells.Clear
    End If
    On Error GoTo 0
    
    ' 分析损益表数据并处理逻辑（假设合并表中有“日期”、“收入”、“支出”列）
    Dim 数据行 As Long
    合并行 = ws合并.Cells(Rows.Count, 1).End(xlUp).Row
    ws结果.Cells(1, 1).Value = "季度"
    ws结果.Cells(1, 2).Value = "总收入"
    ws结果.Cells(1, 3).Value = "总支出"
    ws结果.Cells(1, 4).Value = "净利润"

    Dim 当前季度 As String
    Dim 总收入 As Double
    Dim 总支出 As Double
    Dim i As Long
    Dim 日期列 As Long, 收入列 As Long, 支出列 As Long

    ' 找出对应列
    日期列 = Application.Match("日期", ws合并.Rows(1), 0)
    收入列 = Application.Match("收入", ws合并.Rows(1), 0)
    支出列 = Application.Match("支出", ws合并.Rows(1), 0)

    If IsError(日期列) Or IsError(收入列) Or IsError(支出列) Then
        MsgBox "数据表中缺少必要的列（日期、收入、支出），无法生成季度损益表。"
        Exit Sub
    End If

    数据行 = 2
    For i = 2 To 合并行
        当前季度 = "Q" & WorksheetFunction.RoundUp(Month(ws合并.Cells(i, 日期列)) / 3, 0) & " " & Year(ws合并.Cells(i, 日期列))
        
        If ws结果.Cells(数据行, 1).Value <> 当前季度 Then
            If ws结果.Cells(数据行, 1).Value <> "" Then
                ' 填写净利润
                ws结果.Cells(数据行, 4).Value = ws结果.Cells(数据行, 2).Value - ws结果.Cells(数据行, 3).Value
                数据行 = 数据行 + 1
            End If
            ws结果.Cells(数据行, 1).Value = 当前季度
            ws结果.Cells(数据行, 2).Value = 0
            ws结果.Cells(数据行, 3).Value = 0
        End If
        
        ' 累加收入和支出
        ws结果.Cells(数据行, 2).Value = ws结果.Cells(数据行, 2).Value + ws合并.Cells(i, 收入列).Value
        ws结果.Cells(数据行, 3).Value = ws结果.Cells(数据行, 3).Value + ws合并.Cells(i, 支出列).Value
    Next i

    ' 填写最后一个季度的净利润
    ws结果.Cells(数据行, 4).Value = ws结果.Cells(数据行, 2).Value - ws结果.Cells(数据行, 3).Value

    MsgBox "财务表格合并完成，季度损益表已生成！"

End Sub

此宏代码将以下任务自动化：

1. 从指定文件夹读取多个Excel文件。
2. 自动合并各表格中的数据至“合并”工作表。
3. 根据合并的数据生成“季度损益表”，列出季度收入、支出和净利润。

在使用此代码时，请确保各个财务表格的表头一致，且包含“日期”、“收入”和“支出”列以用于损益分析。如果某些列或格式没有按照预期，需要调整列名或逻辑。

Here's the VBA macro code that can automate the process of populating financial data templates based on input CSV files:

Sub PopulateFinancialTemplate()
    Dim wsTemplate As Worksheet
    Dim wsData As Worksheet
    Dim csvFilePath As String
    Dim lastRow As Long
    Dim lastColumn As Long
    Dim destinationRow As Long
    Dim destinationColumn As Long
    Dim dataRange As Range
    Dim cell As Range
    
    ' Select your financial template (Assumes template is in the active workbook)
    Set wsTemplate = ThisWorkbook.Sheets("Template")
    
    ' Prompt user to select CSV file
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "CSV Files", "*.csv"
        .Title = "Select a CSV file to populate the template"
        If .Show <> -1 Then
            MsgBox "No file selected. Macro will exit.", vbExclamation
            Exit Sub
        End If
        csvFilePath = .SelectedItems(1)
    End With
    
    ' Open the CSV file
    Workbooks.Open Filename:=csvFilePath
    Set wsData = ActiveWorkbook.Sheets(1)
    
    ' Find the last row and column in the data sheet
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    lastColumn = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
    
    ' Define the range containing the data
    Set dataRange = wsData.Range(wsData.Cells(1, 1), wsData.Cells(lastRow, lastColumn))
    
    ' Clear existing data on the template sheet (Optional)
    wsTemplate.UsedRange.ClearContents
    
    ' Populate the template with data from the CSV
    destinationRow = 1
    destinationColumn = 1
    
    For Each cell In dataRange
        wsTemplate.Cells(destinationRow, destinationColumn).Value = cell.Value
        ' Move to the next column in the template
        destinationColumn = destinationColumn + 1
        ' If it reaches the end of the row, move to the next row and reset the column
        If destinationColumn > lastColumn Then
            destinationColumn = 1
            destinationRow = destinationRow + 1
        End If
    Next cell
    
    ' Close the CSV file without saving
    wsData.Parent.Close False
    
    MsgBox "Template populated successfully!", vbInformation
End Sub

How to Use the Macro:

1. Open Excel and go to the VBA editor (Alt + F11).
2. Insert a new module (Insert > Module) and paste the above macro into the module window.
3. Modify the Template sheet name in the Set wsTemplate = ThisWorkbook.Sheets("Template") line to match your financial template sheet's name.
4. Run the macro (F5 or Run).
5. Select the CSV file when prompted.
6. The financial template will be populated with data from the CSV file.

This macro ensures user-friendly automation while allowing flexibility for various CSV file structures. Adjust the macro as needed based on your specific template's layout.