一键整理数据秘籍 EXCEL VBA

最新推荐文章于 2022-10-07 10:08:56 发布

tyfifx

最新推荐文章于 2022-10-07 10:08:56 发布

阅读量799

点赞数 1

文章标签： excel

本文链接：https://blog.csdn.net/tyfifx/article/details/104339319

版权

估计很多销售和我一样，一到月底都会烦恼一次。原因就是要整理很多数据，做销售报告。客户越多，产品型号越多，工作就越多。曾经有好多次，想聘请一个临时工帮我整理数据。这个烦恼困扰了我很多年，终于在这个漫长的假期中，通过闭关修炼，练成了一键整理数据大法。

问题说明：

每月月底，我需要从公司内网下载销售数据，按照产品的类型和销售的名字统计销售金额。下载的数据库里面包括很多我不需要的信息，而且有时我需要的信息却没有。比如下面表格中，美国和德国客户的数据我不需要，在每一行的末尾，我需要增加三列，分别是季度总销售额，产品类型和销售经理姓名。

客户	产品型号	数据类型	1月	2月	3月
中国客户A	A10001	预测	1000	1000	1000
中国客户A	A10001	实际销售额	1000	1000	1000
中国客户B	A10001	预测	1000	1000	1000
中国客户B	A10001	实际销售额	1000	1000	1000
中国客户C	A10001	预测	1000	1000	1000
中国客户C	A10001	实际销售额	1000	1000	1000
美国客户C	A10001	预测	1000	1000	1000
美国客户C	A10001	实际销售额	1000	1000	1000
德国客户C	A10001	预测	1000	1000	1000
德国客户C	A10001	实际销售额	1000	1000	1000

去除不需要的数据：

去除某些不需要的数据，可以在Data中采用Filter，或者数据视图来进行过滤。因为需要过滤的数据很多，每次都需要勾选很多次。而且容易漏选。

增加其他数据

季度总销售额：要在每行末尾，手工输入求和公式，计算出每一个产品型号每个季度的销售。
产品类型：首先通过数据过滤功能，把某一类型的产品筛选出来，统一在每行末尾填加产品类型。然后依次对其他类型的产品进行重复操作。
销售经理姓名：首先通过数据过滤功能，把某销售经理的客户筛选出来，统一在每行末尾，填加销售经理的姓名。然后依次对其他销售经理进行相同的操作。

每次去除数据和增加数据，一般需要30分钟左右的时间。既费眼费力费时又容易出错。

要解决上面这些问题，有几个解决方案：

要求公司的IT部门，按照你的要求，提供相关选项，下载时，可以按照选项进行下载。这个方案直接就被IT无情地拒绝了。可能是我的颜值不够高吧。没办法，只能想其他办法。
用VBA来编写一个脚本，自动进行数据处理。

下面来介绍一下如何用VBA自动处理这些数据的步骤：

打开Excel文件
在Excel的File菜单中，选择Option, 在Option的Cusomize Ribbon中最右边的界面中选中Developer。
点击Ok按钮，然后菜单上会出现Developer，当鼠标移到Developer菜单上，会显示下面子菜单。

在这里插入图片描述点击Insert菜单，弹出控件按钮选择，选择左上角第一个按钮，在Excel表格的空白处，可以画出一个按键，同时弹出下面窗口，在Macro name中把宏的名字改为DataProcessing，然后点击New按钮。

在这里插入图片描述 Excel会弹出下面脚本编写窗口。在这个窗口中，已经自动生成了一个空的函数，名字叫DataProcessing。接下来我们需要在这个空的函数中加入代码。

在这里插入图片描述按ALT+F11键可以在Excel表格和程序界面窗口中来回切换，或者在程序界面中点击File菜单下方的第一个excel标记的图标，也可以切换到excel窗口，在excel窗口中，在Developer下面的子菜单中，点击View Code也可以切换到程序窗口。
好的，我们按ALT+F11键，切换到Excel窗口，发现窗口中已经出现了一个按键。

在这里插入图片描述鼠标挪到这个按键上方，点击鼠标右键，在Edit菜单中，可以修改按钮上的文字，我们把它改成Data Processing。
再次点击鼠标右键，可以修改这个按钮的属性。比如在Font中，可以修改按键上文字的字体和大小，颜色。在Alignment中可以修改对齐方式，在Size中可以修改按键大小。

在这里插入图片描述在Format窗口的Properties中，选择第三项：按键不会随着Excel中单元格的变化而变化。
在Excel表格中，我们需要按照步骤处理数据：

删除掉客户名称为NewYork和Paris的这一行的数据。
删除掉Type中Forecast的这一行的数据。
在数据表格的最后一列，增加4列数据，分别是Q1, Q2, Q3, Q4四个季度的每个客户每个产品的总的销售额。
在数据表格的最后一列中，增加1列数据，显示每行产品名称所属的产品线。
在数据表格的最后一列中，增加1列数据，显示负责该行客户的销售人员姓名。
在数据表格最后一列后面，按照产品线，列出每个季度的销售额。
在数据表格最后一列后面，按照销售人员姓名，列出每个季度的销售额。

根据上面的要求，我们切换到程序设计窗口。编写代码

删除掉客户名称为NewYork和Paris的这一行的数据，以及删除掉Type中Forecast的这一行的数据：
首先定义了两个变量；
接下来把这个表格中有数据的最大行数和最大列数存在这个变量中；
创建一个循环结构，从最大行数开始查找每行中客户名称是否有NewYork和Paris，还有Type中是否有Forecast，如果存在的话，删除该行。

	Dim RowNumber As Integer
    Dim ColNumber As Integer
    Dim PL_Revenue(3) As Long
    Dim Sales_Revenue(3) As Long
    
    
    RowNumber = Sheet1.UsedRange.Rows.Count     'Get the rows of the active sheet
    ColNumber = Sheet1.UsedRange.Columns.Count  'Get the columns of the active sheet
           
    For Row = RowNumber To 2 Step -1 'Must delete from the big to small!!!
    '1. Delete the rows which the customer name in Column 1 is "NewYork" or "Paris" or the type in Colume 3 is "Forecast"
        If InStr(Cells(Row, 1), "NewYork") + InStr(Cells(Row, 1), "Paris") + InStr(Cells(Row, 3), "Forecast") Then
            Rows(Row).Delete
        End If
    Next Row

编写完代码以后，然后我们再切换到Excel界面中，点击按键，发现数据已经删除成功。如下图所示:
在这里插入图片描述 2. 在数据表格的最后一列，增加4列数据，分别是Q1, Q2, Q3, Q4四个季度的每个客户每个产品的总的销售额。
在上面代码后面，加入下面代码：

	Cells(1, ColNumber + 1) = "Q1" 'Add one new column after the last column.
    Cells(1, ColNumber + 2) = "Q2" 'Add one new column after the last column.
    Cells(1, ColNumber + 3) = "Q3" 'Add one new column after the last column.
    Cells(1, ColNumber + 4) = "Q4" 'Add one new column after the last column.
    
    RowNumber = Sheet1.UsedRange.Rows.Count     'Get the rows of the active sheet
        
    For Row = RowNumber To 2 Step -1
        ' calculate Q1 total revenue
        Cells(Row, ColNumber + 1).Value = Cells(Row, 4).Value + Cells(Row, 5).Value + Cells(Row, 6).Value
        ' calculate Q2 total revenue
        Cells(Row, ColNumber + 2).Value = Cells(Row, 7).Value + Cells(Row, 8).Value + Cells(Row, 9).Value
        ' calculate Q3 total revenue
        Cells(Row, ColNumber + 3).Value = Cells(Row, 10).Value + Cells(Row, 11).Value + Cells(Row, 12).Value
        ' calculate Q4 total revenue
        Cells(Row, ColNumber + 4).Value = Cells(Row, 13).Value + Cells(Row, 14).Value + Cells(Row, 15).Value
    Next Row

编写完代码以后，然后我们再切换到Excel界面中，点击按键，发现每个季度的数据已经计算完成，并填写在最后四列中。
3. 在数据表格的最后一列中，增加1列数据，显示每行产品名称所属的产品线。
产品名称为A1xxxx的属于MCU，产品名称为A2xxxx的属于Power Supply，产品名称为A3xxxx的属于Mosfet。代码如下:

    Cells(1, ColNumber + 5) = "Product Line" 'Add one new column after the last column.
     
    For Row = RowNumber To 2 Step -1
        If InStr(Cells(Row, 2), "A1") Then Cells(Row, ColNumber + 5).Value = "MCU" 'If Part ID begins with A1, it belongs MCU production line
        If InStr(Cells(Row, 2), "A2") Then Cells(Row, ColNumber + 5).Value = "Power Supply" 'If Part ID begins with A2, it belongs power supply
        If InStr(Cells(Row, 2), "A3") Then Cells(Row, ColNumber + 5).Value = "Mosfet" 'If Part ID begins with A3, it belongs Mosfet
    Next Row

在数据表格的最后一列中，增加1列数据，显示负责该行客户的销售人员姓名。
客户名为Jiading的，销售人员为：Andy。客户名为Minhang的，销售人员为：Angela，客户名为Putuo和Changning的，销售人员为： Amanda。代码如下：

    Cells(1, ColNumber + 6) = "Sales Name" 'Add one new column after the last column.
    
    For Row = RowNumber To 2 Step -1
        If InStr(Cells(Row, 1), "Jiading") Then Cells(Row, ColNumber + 6).Value = "Andy" 'If customer is Jiading, Sales is Andy.
        If InStr(Cells(Row, 1), "Minhang") Then Cells(Row, ColNumber + 6).Value = "Angela" 'If customer is Minhang, Sales is Angela.
        If InStr(Cells(Row, 1), "Putuo") + InStr(Cells(Row, 1), "Changning") Then Cells(Row, ColNumber + 6).Value = "Amanda" 'If customer is Putuo or Changning, Sales is Amanda.
    Next Row

在数据表格最后一列后面空一列之后，按照产品线，列出每个季度的销售额。

    Cells(1, ColNumber + 8) = "Production Line" 'Add one new column for product line
    Cells(1, ColNumber + 9) = "Revenue" 'Add one new column for revenue
    
    For i = 1 To 3 Step 1  'PL_Revenue are initialized to 0.
        PL_Revenue(i) = 0
        Sales_Revenue(i) = 0
    Next i
    
    For Row = RowNumber To 2 Step -1
        If InStr(Cells(Row, ColNumber + 5), "MCU") Then   'If PL is MCU, accumulate the revenue to PL_Revenue(1)
            PL_Revenue(1) = PL_Revenue(1) + Cells(Row, ColNumber + 1).Value + _
                                            Cells(Row, ColNumber + 2).Value + _
                                            Cells(Row, ColNumber + 3).Value + _
                                            Cells(Row, ColNumber + 4).Value
        End If
        If InStr(Cells(Row, ColNumber + 5), "Power Supply") Then   'If PL is MCU, accumulate the revenue to PL_Revenue(2)
            PL_Revenue(2) = PL_Revenue(2) + Cells(Row, ColNumber + 1).Value + _
                                            Cells(Row, ColNumber + 2).Value + _
                                            Cells(Row, ColNumber + 3).Value + _
                                            Cells(Row, ColNumber + 4).Value
        End If
        If InStr(Cells(Row, ColNumber + 5), "Mosfet") Then   'If PL is MCU, accumulate the revenue to PL_Revenue(3)
            PL_Revenue(3) = PL_Revenue(3) + Cells(Row, ColNumber + 1).Value + _
                                            Cells(Row, ColNumber + 2).Value + _
                                            Cells(Row, ColNumber + 3).Value + _
                                            Cells(Row, ColNumber + 4).Value
        End If
                
    Next Row
    Cells(2, ColNumber + 8) = "MCU"
    Cells(2, ColNumber + 9) = PL_Revenue(1)
    
    Cells(3, ColNumber + 8) = "Power Supply"
    Cells(3, ColNumber + 9) = PL_Revenue(2)
    
    Cells(4, ColNumber + 8) = "Mosfet"
    Cells(4, ColNumber + 9) = PL_Revenue(3)

在数据表格最后一列后面，按照销售人员姓名，列出每个季度的销售额.

    Cells(1, ColNumber + 11) = "Sales" 'Add one new column for product line
    Cells(1, ColNumber + 12) = "Revenue" 'Add one new column for revenue
    
    For i = 1 To 3 Step 1  'Sales_Revenue are initialized to 0.
        Sales_Revenue(i) = 0
    Next i
    
    For Row = RowNumber To 2 Step -1
        If InStr(Cells(Row, ColNumber + 6), "Andy") Then   'If PL is MCU, accumulate the revenue to PL_Revenue(1)
            Sales_Revenue(1) = Sales_Revenue(1) + Cells(Row, ColNumber + 1).Value + _
                                            Cells(Row, ColNumber + 2).Value + _
                                            Cells(Row, ColNumber + 3).Value + _
                                            Cells(Row, ColNumber + 4).Value
        End If
        If InStr(Cells(Row, ColNumber + 6), "Angela") Then   'If PL is MCU, accumulate the revenue to PL_Revenue(2)
            Sales_Revenue(2) = Sales_Revenue(2) + Cells(Row, ColNumber + 1).Value + _
                                            Cells(Row, ColNumber + 2).Value + _
                                            Cells(Row, ColNumber + 3).Value + _
                                            Cells(Row, ColNumber + 4).Value
        End If
        If InStr(Cells(Row, ColNumber + 6), "Amanda") Then   'If PL is MCU, accumulate the revenue to PL_Revenue(3)
            Sales_Revenue(3) = Sales_Revenue(3) + Cells(Row, ColNumber + 1).Value + _
                                            Cells(Row, ColNumber + 2).Value + _
                                            Cells(Row, ColNumber + 3).Value + _
                                            Cells(Row, ColNumber + 4).Value
        End If
                
    Next Row
    Cells(2, ColNumber + 11) = "Andy"
    Cells(2, ColNumber + 12) = Sales_Revenue(1)
    
    Cells(3, ColNumber + 11) = "Angela"
    Cells(3, ColNumber + 12) = Sales_Revenue(2)
    
    Cells(4, ColNumber + 11) = "Amanda"
    Cells(4, ColNumber + 12) = Sales_Revenue(3)

编写完代码以后，然后我们再切换到Excel界面中，点击按键，所有的数据都已经按照要求计算并显示出来了。编写这样的代码也许需要半天的时间，但是当你轻轻按下那个按键时，你也许感觉到整个世界都在你手中一样，一切尽在掌控中。
在这里插入图片描述这种方法适合于重复性比较多的任务，如果每个表格要处理的数据都不一样，那就只能自己手动处理了。有机会的话，可以结合自己的实际工作需求尝试一下，相信一定帮助你提高工作效率的！

tyfifx

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
一键整理数据秘籍 EXCEL VBA

估计很多销售和我一样，一到月底都会烦恼一次。原因就是要整理很多数据，做销售报告。客户越多，产品型号越多，工作就越多。曾经有好多次，想聘请一个临时工帮我整理数据。这个烦恼困扰了我很多年，终于在这个漫长的假期中，通过闭关修炼，练成了一键整理数据大法。问题说明：每月月底，我需要从公司内网下载销售数据，按照产品的类型和销售的名字统计销售金额。下载的数据库里面包括很多我不需要的信息，而且有时我需要的信息...
复制链接

扫一扫