【AI办公】数据处理

把需求描述给大模型，让它帮忙写VBA代码

DataWhale 『AI办公 x 财务』第一期 workshop学习说明

参考prompt

假设你是最懂 Excel 的专家，take the breath，let's think step by step。帮我写一个VBA脚本来执行以下两个任务：

1.汇总：脚本应该遍历当前文件夹中的所有Excel文件，并将它们的内容汇总到一个新的Excel文件中。第一行需要在汇总的表格中，每行数据需要添加一个新列“表格名”，以标识该数据来自哪个原始表格。完成后，请将这个新的Excel文件保存在当前目录下，并命名为“汇总”。
2.统计：在“汇总”文件中，除了上述汇总内容，还请添加一个新的工作表。在这个新工作表中，我需要统计湖南地区办公用品类别的总销售额、总数量和总利润。为您参考，我上传了一个示例表格，其中包含了类似的数据格式和内容。

当前文件夹中其中一个表格数据如下所示：
行 ID        订单 ID        订单日期        发货日期        邮寄方式        客户 ID        客户名称        细分        城市        省/自治区        国家/地区        地区        产品 ID        类别        子类别        产品名称        销售额        数量        折扣        利润
11        CN-2018-4195213        2018/12/22        2018/12/24        二级        谢雯-21700        谢雯        小型企业        榆林        陕西        中国        西北        技术-设备-10000001        技术        设备        爱普生 计算器, 耐用        434.28        2        0        4.2
44        CN-2018-2932548        2018/5/17        2018/5/22        二级        唐婉-21385        唐婉        小型企业        南昌        江西        中国        华东        办公用-收纳-10004212        办公用品        收纳具        Fellowes 锁柜, 蓝色        1935.08        2        0        0

请注意，该脚本需要能够在Excel环境中顺利运行。

生成可执行VB代码

Sub SummarizeAndStatistics()
    Dim summaryWorkbook As Workbook
    Dim dataWorkbook As Workbook
    Dim dataWorksheet As Worksheet
    Dim summaryWorksheet As Worksheet
    Dim dataFile As String
    Dim folderPath As String
    Dim lastRow As Long
    
    ' 获取当前文件夹路径
    folderPath = ThisWorkbook.Path & "\"
    
    ' 创建一个新的工作簿作为汇总文件
    Set summaryWorkbook = Workbooks.Add
    
    ' 遍历当前文件夹中的所有Excel文件
    dataFile = Dir(folderPath & "*.xlsx")
    Do While dataFile <> ""
        ' 打开每个Excel文件
        Set dataWorkbook = Workbooks.Open(folderPath & dataFile)
        ' 复制数据表到汇总文件
        Set dataWorksheet = dataWorkbook.Worksheets(1)
        dataWorksheet.Copy After:=summaryWorkbook.Sheets(summaryWorkbook.Sheets.Count)
        Set summaryWorksheet = summaryWorkbook.Sheets(summaryWorkbook.Sheets.Count)
        ' 在汇总数据表中添加“表格名”列并填入原始表格名称
        summaryWorksheet.Cells(1, dataWorksheet.UsedRange.Columns.Count + 1).Value = "表格名"
        summaryWorksheet.Cells(2, dataWorksheet.UsedRange.Columns.Count + 1).Resize(dataWorksheet.UsedRange.Rows.Count - 1).Value = dataFile
        ' 关闭原始文件
        dataWorkbook.Close False
        ' 继续处理下一个文件
        dataFile = Dir
    Loop
    
    ' 保存汇总文件
    summaryWorkbook.SaveAs folderPath & "汇总.xlsx"
    
    ' 添加新的工作表进行统计
    Set summaryWorksheet = summaryWorkbook.Sheets.Add
    summaryWorksheet.Name = "统计"
    
    ' 在新工作表中进行统计
    lastRow = summaryWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
    summaryWorksheet.Cells(1, 1).Value = "类别"
    summaryWorksheet.Cells(1, 2).Value = "总销售额"
    summaryWorksheet.Cells(1, 3).Value = "总数量"
    summaryWorksheet.Cells(1, 4).Value = "总利润"
    
    ' 这里需要根据实际数据格式进行统计计算
    ' 示例代码：
    summaryWorksheet.Cells(2, 1).Value = "办公用品"
    summaryWorksheet.Cells(2, 2).Formula = "=SUMIFS('Sheet1'!$C:$C,'Sheet1'!$B:$B,""=""&summaryWorksheet.Cells(2, 1))"
    summaryWorksheet.Cells(2, 3).Formula = "=SUMIFS('Sheet1'!$D:$D,'Sheet1'!$B:$B,""=""&summaryWorksheet.Cells(2, 1))"
    summaryWorksheet.Cells(2, 4).Formula = "=SUMIFS('Sheet1'!$E:$E,'Sheet1'!$B:$B,""=""&summaryWorksheet.Cells(2, 1))"
    
    ' 保存汇总文件
    summaryWorkbook.Save
    
    ' 关闭汇总文件
    summaryWorkbook.Close
End Sub

操作步骤

1.创建一个目录

将需要处理的execel文件移动到该目录，创建一个数据处理的execel文件并启动宏。将vb代码贴进去，然后另存为启动宏的工作表。代码编译通过后，再执行F5运行，最后数据保存再汇总文件里。