最全Excel VBA 办公自动化

在日常办公中，Excel 经常被用来处理数据、创建报表和执行各种计算。但是，当我们需要重复执行一些任务时，手动操作可能会变得繁琐乏味。这时候，Excel VBA (Visual Basic for Applications) 就派上了用场。本文将介绍如何使用 Excel VBA 进行办公自动化，包括提示词编写技巧、打开 VBA 界面以及具体的 VBA 提示模板。

一、提示词编写技巧

1. 指定角色： 在编写 VBA 代码时，首先要确定代码的角色或功能。比如，你的代码是用来自动化数据导入还是生成报告？

2. 交代背景： 在代码注释中简要说明代码的背景和目的。这有助于其他人理解你的代码，并且在以后需要修改或维护代码时也会非常有用。

3. 说明问题： 在代码注释中清晰地说明你要解决的问题或实现的功能。这样可以确保你的代码逻辑清晰，易于理解。

4. 给出示例： 最好在注释中提供一些示例，展示代码的预期行为或输出。这有助于其他人快速了解代码的作用。

二、打开编写 VBA 界面

在 Excel 中打开 VBA 编辑器很简单：

1. 打开 Excel 文件。
2. 按下 Alt + F11 快捷键，或者在菜单栏中选择 开发人员 -> Visual Basic。

这样就可以打开 VBA 编辑器，开始编写 VBA 代码了！

        3.具体操作实现示例如下：

 

三、具体 VBA 提示模板

1，单条件查询(Find/字典)

你是Excel VBA专家。第1个工作表中N:N为给定数据，A-C列分别为“商品”、"数量”和“金额”数据，第1行为变量名称。用Range对象的End方法，在F列从上面第2个单元格开始，向下获取F列的未行。将F列第2行到未行各单元格的值放到SN数组。注意，SN数组中放的是单元格的值，不是单元格本身。遍历SN数组中每个值，假设当前为第i个值，在A1:C5范围内查询它对应的数量和金额数据。在工作表的第i+1行，第G列和第H列分别输出查询得到的数量和金额。比如，假设商品名称“冰箱”，在G(i+1)和H(+1)单元格输出冰箱对应的数量和金额。用Find/字典方法查询。给代码添加注释。

2，多条件查询（字典）

你是Excel VBA专家。第1个工作表中N:N为给定数据，A-D列分别为“销售人员”、“商品”、“数量”和“总额”数据，第1行为变量名称。用Range对象的End方法，在F列从上面第2个单元格开始，向下获取F列的末行。将E列第2行到末行各单元格的值放到SN数组。将G列第2    行到末行各单元格的值放到SP数组。注意，SN和SP数组中放的是单元格的值，不是单元格本身。对SN数组和SP使用For循环，将SN数组中的第i个值作为第1查询条件，将SP数组中的第i个值作为第2查询条件，实现在A1:D11范围内多条件查询对应的数量和总额数据。在工作表的第 i+1行，第H列和第1列分别输出查询得到的数量和总额。比如，假设销售人员为张三，商品为彩电，在H(i+1)和l(i+1)单元格输出对应的数量和总额。用字典实现，注意创建字典时使用的是 A1:D11范围内的数据，对于每行数据前两列的值作为键，后两列的值作为值。给代码添加注释。

3，单条件汇总（字典）

你是Excel VBA专家。第1个工作表中N:N为给定数据，A-D列分别为"时间”、"商品"、"数量”和“金额”数据,第1行为变量名称。用字典对每种商品的数量和金额分别进行汇总，将汇总结果输出在第F-H列，第1行为变量名,分别为“商品”、“数量”和“金额”。给代码添加注释。注意，For Each循环中循环变量的名称不要跟程序中之前表明过的变量名重复

4，多条件汇总（字典）

你是Excel VBA专家。第1个工作表中N:N为给定数据，A-D列分别为“日期”、“销售人员”、“商品”和“数量”数据，第1行为变量名称。用字典实现多条件汇总，销售人员为第1条件,商品为第2条件。对每个销售人员每种商品的数量进行汇总，将汇总结果输出在第FH列，其中第1行为变量名，分别为“销售人员”、“商品”和“数量”。给代码添加注释。

5，行汇总

你是Excel VBA专家。第1个工作表中N:N为给定数据。从第2行开始，每一行为某同学的语文、数学、英语、政治和历史等5门学科的考试成绩，第1列为学生姓名。计算每位同学的成绩总和，显示在最后一列。给代码添加注释。

6，列汇总

你是Excel VBA专家。第1个工作表中N:N为给定数据，A-D列分别为“商品”、“一月”、“三月"和"六月”数据，第1行为变量名称。计算不同月份的总销售额，即计算各月份所在列的数据的和，结果输出在第N行。给代码添加注释。

7，次数汇总

你是Excel VBA专家。第1个工作表中N:N为给定数据，是一些有重复的人员名字。请遍历N:N范围内的每个单元格，用字典统计每个名字出现的次数，名字为键，名字出现的次数为值。在第1列输出名字，在第K列输出名字出现的次数。比如:在J2单元格输出“张三”，在K2单元格输出3。给代码添加注释。忽略缺失值。

8，提取首/末值（字典）

你是Excel VBA专家。第1个工作表中N:N为给定数据，A-D列分别为“时间”、“商
品”、“数量”和“金额”数据，第1行为变量名称。使用两个字典提取每种商品第一次售出的行数据。字典1以商品名称为键，以时间为值;字典2以商品名称为键，以对应的整行数据为值。遍历第2行到末行数据，根据字典1的值与当前行A列单元格中时间的先后，如果时间更早/晚，用单元格中的时问替换字典1的值，用整行数据替换字典2的值。从第E列至第1列，第1行输出“时间”、“商品”、“数量”和“金额”，从第2行开始，输出字典2的值对应的行数据。给代码添加注释。

9，单/多条件排序

你是Excel VBA专家，第1个工作表中N:N为给定数据，A-E列分别为“工号”、“部
订、姓名、身份证号"和“性别”数据，行为变量名称。从第2行开始，遍历各行数据,根据工号大小对行数据进行序排列（首先根据性别进行升序排序，然后对于相同性别用工号进行升序排序）。排序后的结果输出在名为“Sheet2”的工作表中，第一行为受量名称，跟第1个工作表中的相同。Sheet2工作表已经存在，不需要新建。给代码添加注释。

10，单/多条件筛选

你是Excel VBA专家，第1个工作表中A1:G10为给定数A-G列分别为“编号”、“性別”、“年
选出工资大于4000元（以及性别为女）的行数据，结果输出在第13行下面。给代码添加注释。

11，中国式排名

你是Excel VBA专家，第1个工作表中A1:B8为给定数据，A-B列分别为“姓名"和"短跑成绩(秒)”数据，第1行为变量名称。遍历第2行到未行，首先按照短跑成绩对各行数据进行升序排列，得到各行对应的序号。排序后的姓名和短跑成绩数据放在第E列和第F列，变量名为“姓名”和“短跑成绩(秒)”，数据从第2行开始往下放。排序后行数据处于第几行序号就是几。比如，假设排序后短跑成绩(10 1111 12 13 13 1315)对应的序号为(12 34 5678)，对于短跑成绩相同的情况，比如成绩都为11的有两个，它们对应的序号为2和3，采用中国式排名，现在序号都取2，取最小值;成绩都为13的有三个，它们对应的序号为5.6和7，都取最小值5。处理完后，最终序号添加在第G列，变量名为“排名”。给代码添加注释。

12，工作表合并

你是Excel VBA专家，前3个工作表中，第1到3个工作表的第1列为学生姓名，第2列分别为学生的语文、数学和英语考试成绩。要求新建一个汇总工作表，将这些同学的三门课的考试成结进行汇总。首先将第1个工作表中的数据复制到汇总表前两列,然后将第2个工作表的第2列整列复制到汇总表第3列第1行的位置,将第3个工作表的第2列整列复制到汇总表第4列第1行的位置。给代码添加注释。

13，工作表拆分

你是ExcelVBA专家，第1个工作表sht中A-D列为“姓名”、“语文”、“数学”和“英语”数据。要求新建3个工作表，它们的名称分别为“语文”、“数学”和“英语”。将sht的“姓名”列整列复制到3个新工作表的第1列第1行。将sht的“语文”列整列复制到新建的第1个工作表中第2列第1行;将sht的“数学”列整列复制到新建的第2个工作表中第2列第1行;将sht的“英语”列整列复制到新建的第3个工作表中第2列第1行。给代码添加注释。

14，根据某列值拆分工作表

你是Excel VBA专家，第1个工作表A1:G10中为不同科室员工的个人信息数据。要求遍历第1个工作表中第2行到未行，对于当前行，D列为科室名称，如“科室1”，如果工作簿中不存在名为该科室名称的工作表，则新建一个，工作表的名称为科室名称，工作表的第一行与第1个工作表的第一行相同，将当前行数据复制到新建工作表的第2行;如果工作簿中已经存在名为该科室名称的工作表，直接将当前行数据添加到该工作表的最后一行。给代码添加注释。

15，数据预处理—去重（字典键唯一）

你是Excel VBA专家。第一个工作表中存在重复数据，请根据工号列数据用字典去除重复行数据。给代码添加注释

16，数据预处理—处理缺失值

你是Excel VBA专家。第一个工作表前N行前N列中的数据中存在缺失值，请删除至少包含一个缺失值的行数据。添加代码注释

17，数据预处理-处理异常（均值，标准差，四分位）

你是Excel VBA专家，请找出第一个工作表N列第N行及以下数据中存在的异常值。使用内四分极值判断异常值。将找到的异常值在第三列进行标示。添加代码注释

18，数据预处理-数据变换（中心化/中心位平移，标准化/标准正态分布，归一化/0-1）

你是Excel VBA专家，对第1个工作表N列N行及以下数据进行标准化处理，标准化结果显示在第三列。添加代码注释。

19，描述性统计

集中趋势：均值，中值，众数，几何均值，调和均值，截尾均值

离中趋势：极差，方差，标准差，均值绝对差，内四分极差

你是Excel VBA专家。对第1个工作表C列第2行及以下的数据进行描述性统计。统计量包括均值、中值、极差、方差、标准差和内四分极差。结果输出在E列和F列，例如:E2中输出文本“均值”，F2中输出计算得到的均值。添加代码注释。

20，设置excel工作表格式-设置边框

你是Excel VBA专家。给第1个工作表中B3:116单元格区域添加边框，内部边框为黑色的细线，最外部边框为红色的粗线。添加代码注释。

21，设置excel工作表格式-设置单元格背景色

你是Excel VBA专家。第1个工作表中，在B-D列2-11行中将值大于等于95的单元格的背景色设置为粉红色，将值小于60的单元格的背景色设置为淡绿色。添加代码注释。

22，设置excel工作表格式-设置字体

你是Excel VBA专家。设置第1个工作表中B3单元格中文本的字体名称为“黑体”，字体大小为20，加粗，字体颜色为红色;D4单元格中文本的字体名称为“宋体”,字体大小为30，字体颜色为蓝色,倾斜。添加代码注释。

23，设置excel工作表格式-设置对齐方式

你是Excel VBA专家。设置第1个工作表B3单元格中文本水平居中对齐;D5单元格中的文本水
平左对齐;B8单元格中的文本水平右对齐，垂直方向顶对齐。添加代码注释。

24，设置excel工作表格式-设置单元格合并和取消合并

你是ExcelVBA专家，在第1个工作表中合并单元格区域B3:C4，将单元格B8取消合并。添加
代码注释。

25，数据可视化（条形图，线形图，饼图）

你是Excel VBA专家。用第一个工作表A1:D8范围内的数据生成嵌入式条形图。添加代码注释

四，心得

最后分享下我使用，调试，运行VBA的一些心得体会吧，其实我算有点代码基础，知道大致怎么做，但是可能无法知道那种专有名词，无法知道更简单的方法，如字典的键值对的特点就比单纯的find或者for循环简单很多，字数也少很多，代码也相对简单，所以我对这个excel vba提示词的理解，我个人觉得可以类比中文的成语，如果我们不够专业，可能需要很多词才能解释出来这个成语，虽然意思大概的是有了，而专业的人可以描述的更简洁，实现的更高效。

其实如果数据不是很多，VBA略麻烦，可以直接描述操作部分，写出Excel函数公式来完成哦。

Excel函数公式提示词示例：

E3单元格为出货数量，B3单元格为出货地址，请写出Excel函数公式判断出货量小于100000大于30000，并且是上海出货的显示为是，否则的话显示为否

以上就是具体的 VBA 提示模板，你可以根据实际需求修改和扩展这个模板。希望这篇文章能帮助你 Excel VBA 办公自动化，提高工作效率！

（用更优质更全更新的资源学习，简直yyds啊！！！感兴趣加好友咨询，请注明来意)