EXCEL中脚本的学习和应用

jxlzy · 发表于 2012-5-10 20:26

最近开始接触excel中的脚本应用。发现脚本的确可以解决很多以前比较难解决或者必须用复杂的VBA来解决的问题。遂记录下学习之心得，与广大朋友共同学习。

1、初识Microsoft 脚本编辑器

首先，必须确保你安装的是excel2003版本（2007版本取消了脚本编辑器），并且你的EXCEL安装的是完整版，如果是绿色版或者精简版，建议先找到安装光盘或者在网上先down一个完整版。

安装好之后，就可以在【工具】——【宏】——Microsoft 脚本编辑器中打开脚本编辑器。或者直接按Shift+Alt+F11打开。

2、一个简单的例子

先来看一个简单的例子。

要将多行多列的数变成1列。这个问题通常是用函数来解决的。我们先来看看函数的解法。

E1 =INDEX($A$1

C$11,INT((ROW()-1)/3)+1,MOD(ROW()-1,3)+1)（这是我写的一个函数，写得复杂大家多指教）

尽管这个函数也不算复杂，但是要用到index,mod等函数，还要计算行列以确定除数和被除数。着实有些复杂。

再来看看脚本编辑器中是如何处理此类问题的。

游客，如果您要查看本帖隐藏内容请回复

1、打开脚本编辑器——按Ctrl+H，调出查找/替换对话框（或是按【编辑】-【查找和替换】）.然后查找td,替换成tr,全部替换。

2、再回到excel界面，界面上弹出一个刷新工具栏，点击”刷新”,看到了什么，多行多列已经转化为了1列。（这个1列实际上是3列合并单元格后形成的，可点击工具栏的【合并居中按钮】来取消。）

看，用脚本编辑器就是这么简单，只需要简单的替换和刷新两步。

仍然是替换和刷新两步。是不是很简单呢？

这个方法更加优越的一点在于，用拼写检查只能替换英文，但是用脚本编辑器则不受该限制。

大家可以试试，再将上例中的”excel精英培训”替换为”excelpx”，如果用拼写检查则无法实现，用脚本编辑器则可以转换自如。

不仅是对于批注，其他窗体控件如命令按钮、复选框等无法在excel中直接进行替换的也可以在脚本编辑器中进行替换。

游客，如果您要查看本帖隐藏内容请回复

下面是稍微要有些html语言基础的，不过没有也没关系，咱又不摆弄网页，只需要理解一些基本的规则和代码就可以。

首先得知道，html语言中,用<table>来定义表格， <TR>定义表格的行，<TD>定义表格的列。脚本编译器是将工作表内容按照先行后列的顺序翻译的，也就是说，如果你的sheet1有10行8列数据，那么将有10个<TR>标签，同时会有10*8=80个<TD>标签。

这从上一讲的图中可以很清楚的看出来，脚本编辑器文档大纲视图中，在<table>标签中，包含了很多<TR>标签（代表每个表格含有很多行），每个<TR>标签又包含了很多<TD>标签（代表每一行有很多列）。

Html语言的写法是将代码写在每个封闭的标签中，所谓封闭，是指有开始标签和结束标签，结束标签一般是在开始标签前面加上一个”/”,例如<table></table>就是一对标签。（html语法允许不封闭，但是建议大家写代码的时候还是按照规定来写）

自己写代码

下面来自己写一些代码。我所谓的自己写代码是直接在excel中写，然后通过剪贴板来转换。这是一种常用的方法

具体操作如下：

1、CTRL+C+C打开剪贴板，或者菜单——编辑——office剪贴板，调出剪贴板。

2、将要复制的代码或内容复制到剪贴板

3、单击剪贴板粘贴

4、鼠标右键——选择性粘贴，弹出对话框，粘贴方式选择unicode文本。

首先来写以下这段代码：

然后用上面的方法，粘贴为unicode文本之后，看到了什么？

晦涩难懂的代码变成了

姓名年龄

张三 25

这是怎么回事呢？让我们分析上面的代码：

最外层的<table>和</table>标签是定义表格的

紧接着是两个<th></th>标签，这是用来定义表头用的，也可称之为标题，在数据库中叫做字段。

接下来红色部分<tr></tr>定义了一行，中间有两个<td></td>标签，表示有两列，第一列的数据是第一个<td></td>包含的“张三”，第二列数据是第二个<td></td>包含的25.

当然，上面的例子只是为了说明代码的作用，实际中很少有用这样的代码来编写表格的。写这样一长串代码比直接在工作表中输入数据不知道要费时多少倍。但是，通过这样一个实例，我们或许能找到一些解决特殊问题的方法。

问题一：

有一列数据，我们要将每个单元格的第一个字符变成粗体（或是变成楷体，颜色变成红色等等）。应该如何解决呢

用格式刷或者选择性粘贴——格式当然是不行的，我们可以试着用V，但是代码同样复杂。

这样，倒不如用脚本来试试。

C1="<table><B>"&LEFT(A1)&"</B>"&MID(A1,2,99)，下拉

复制，粘贴，然后选择性粘贴为unicode文本。试试看，是不是很快的就达成效果了。

这里<B></B>是用来加粗字体用的，其他字体类的标签有<I></I>斜体，<U></U>下划线 <font color=”red”></font>用来定义字体颜色为红色，<font face=”隶书”></font>用来定义字体为隶书等等。大家可以找相关的html语言教程来看看。

大家可以引申一下，如何将文本与数字相连的单元格中的文本用粗体，蓝色表示，数字用斜体红色表示。

昨天好似看到一贴要求将一个工作簿中一百个工作表进行排序，象这样的问题一般都会想到必须要用VBA，可实际上不用VBA，只用技巧一样可以解决。下面是我录制的一段动画，时间较长，大家可能会看的云里雾里，其实道理很简单，就是在“脚本编辑器”里把<x:excelworksheet>与</x:excelworksheet>之间的NAME标签和它下面那行进行重新排序，排好序后再复制回“脚本编辑器”中。
可能有人未完整安装EXCEL，也不要紧，把工作簿另存为网页文件，用IE打开网页文件，查看－源文件，一样可以看到同样的代码并进行调整，将调整好代码的网页文件保存后用EXCEL打开，工作表已经被排好序了，再另存为普通工作簿即可。
因为“脚本编辑器”可以解决很多EXCEL底层问题，因此2007，2010版取消“脚本编辑器”实在是一大损失！！！

其实法老师的解法很不错，尽管过程稍复杂了一些。

我这里将它精简了一下，以利更多人理解。

其实主要思想就是利用book中保存的工作表的信息，复制到excel之后进行排序。

1、打开脚本编辑器，选中book1，现实book1代码，查找到worksheet部分。将<x:ExcelWorksheets>与</x:ExcelWorksheets>标签卡中的代码全部复制到excel中

2、上面复制的就是工作表信息的代码了。对其每4行进行组合，然后对组合后的单元格进行排序

3、最后复制排序后的单元格，再替换掉原来的代码即可

几点注意：粘贴至excel的时候，要粘贴为unicode文本，否则会出错

粘贴回脚本编辑器的时候，要右键，粘贴为html，这样才不会出现乱码。

通过上例的学习，我们也可以得到批量更改工作表名的方法，例如我们要将上面工作表的sheet1更改为1，sheet2更改为2……,我们只需在脚本编辑器中查找替换，然后刷新excel就可以了。但是要注意的是，查找的时候不能直接查找sheet,否则替换很多无关内容，我们可以查找<x:Name>Sheet，替换为<x:Name>。

下面还有几个零星的用脚本解决的问题，大家有兴趣的可以去看看。

用函数批量合并单元格
批量合并单元格一般都是用VBA或者用数据透视表来解决的，这里提供一种利用函数和脚本的方法。

B2=IF(A2=A1,"","<table><tr><td rowspan="&COUNTIF(A:A,A2)&">"&A2&"</td></tr></table>")
下拉，然后复制至剪贴板，单击剪贴板粘贴，再右键——选择性粘贴——粘贴为unicode文本

excel批量分行

这里提供一种方法，具体是将一列数据中的文本和数字分开，并且1行分成2行。
（还是用脚本解决，最近喜欢上了脚本）
函数公式如下：="<table><tr><td>"&LEFT(A1,MATCH(1,1/ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1)&"<br>"&MID(A1,MATCH(1,1/ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),99)&"</td></tr></table>"

excel中的邮件合并