VBA如何编写Vlookup公式整理财产目录
网志图文版：
http://www.b88104069.com/archives/4234
Vlookup是《会计人的Excel小教室》主打的两大招式之一，对于会计工作有很大帮助，在
整理和补充报表时，往往会用到Vlookup函数公式。但，由于它是“比对特定条件查找并
传回储存格内容”，听起来就比较消耗计算资源，有经验的读者应该遇到过，在笔数量巨
多、查找范围寛广的情况，Excel一定当场卡关给你看，然后CPU使用率瞬间飇升50%以上
……。我自己工作便有几个例行性档案，资料少说几万笔，而且系统产生出来的报表有所
不足，必须以Vlookup函数补好补满，每次把公式往下拉到底，恶梦于焉开始，我的工作
，跟着我的电脑一起被Excel绑架了。在此分享个人在Excel及VBA如何减轻计算负担的小
技巧（简单版和复杂版）：
一、极简版本的资产目录，只有6笔资料，大企业绝对是这个的好几N倍。
二、“资产代码”、“资产类别”、“会计科目”，这些是原报表没有、但很想要有的资
料，必须以Vlookup带入。
三、储存格“G4”的公式：“=LEFT(A4,2)”，储存格“H4”的公式：“=VLOOKUP(G4,二
!A:C,2,0)”，储存格“I4”的公式：“=VLOOKUP(G4,二!A:C,3,0)”，有修过《会计人的
Excel小教室》学分，应该都会写像这样的函数公式。
四、如同文章一开始所述，如果资料量大，好几万个Vlookup同时运作，结果可想而知。
解决办法之一：上方功能区移到“公式”页签、“计算”区块，将“计算选项”下拉，有
“自动”、“除运算列表外，自动重算”、“手动”三个选项。“自动”是只要储存格有
任何变动，包括自动储存时，都会再跑一次几万个Vlookup（其实没必要）。“除运算列
表外，自动重算”主要是搭配Excel一个“模拟分析”的应用，有机会再作分享。“手动
”则是这篇文章推荐的方法之一，设置后Excel不会主动重算，除非操作者按下这个功能
区块里的“立即重算”指令。
五、手动重算纵然有其妙用、而且操作简单，但往往工作中会同时开好几个Excel档案好
几个工作表，手动重算只能在“都不重算”和“一起重算”两者之间控制，在灵活度上等
于是没有。最好的解决方案应当是需要时再重算这几万笔Vlookup，就这几万Vlookup，其
余不受影响。想达到这样的效果，只有编写VBA程式码了。首先在这个步骤重点介绍VBA的
“UsedRange.Rows.Count”和“For…Next”。
10：建立一个宏程序，取名为“UsedRange_Left”。
30：很实用的标准程式码，计算目前工作表的资料行数，设定其值为“R”，如同步骤一
的图片所示，这里的R值为10。
40：在储存格“G1”输入“R”值。
60：设置一个从“4”到“R-1”的循环，变量为“i”，于此范例即为4到9。
80：利用“Left”函数取储存格文字串的左边两个字符，并且设定其值为“C”。例如当i
＝4时，“Left(Cells(4, 1), 2)”即为“OF”。
90：在储存格“(i,7)”输入“C”值，例如当i＝4时，亦即于“G4”输入“OF”。
110：完成一次循环，跳回到“For…”执行下一个“i”。
130：结果此程式。
六、执行结果，如图所示。“G1”为“10”，表示资料含标题共有10行。“G4”在资料编
辑列为“OF”，即使把它的引用对象储存格“A4”清除，“OF”仍然在，这代表Left函数
是在VBA执行时计算，把结果值输入于Excel储存格中，在Excel并没有任何函数公式，因
此也不会有公式计算的问题。
七、在先前VBA的基础上进一步编写程式码：
90：在循环“For i = 4 To R - 1”中再套一个循环“For j = 2 To 4”，第一个“i”
循环类似于Excel公式往下拉、好几个Vlookup的效果，第二个“J”循环类似于一个个的
Vlookup，相当于模拟一行一行往下查找的效果。
110~130：白话解释程式码，如果“C”(Left(Cells(i, 1), 2))等同于Sheets("二
").Cells(j, 1).Value，那么将“Sheets("二").Cells(j, 2).Value”输入在“Sheets("
八").Cells(i, 7).Value”。用心琢磨这一段程式码，应当能理解其执行效果，便是函数
公式“VLOOKUP(G4,二!A:C,2,0)”。
150~170：另外一组Vlookup，前面查找的是资产类别，这里查找的是会计科目。严格比较
VBA程式码和Excel函数公式，只有在如果有两个查找条件同时成立，VBA程式码取的是成
立的最后一行内容，Excel的Vlookup函数会取成立的第一行内容，这部份文字说明较为抽
象，日后有机会再以适当范例作介绍，总之于实务工作中，很少会遇到因此所产生的困难
。
八、一如预期，以VBA宏实现Vlookup计算结果，除非再执行一次宏，否则Excel再怎
么自动计算或自动储存，都不会再作这方面的处理，所以当然也不会影响电脑速度。
这节范例用到了一般应用程式中很常见的循环语句。如果肯花时间阅读理解，它其实相当
于高中数学方程式的概念，应该不难理解，它的概念。这里的VBA程式码就是单纯依照
Vlookup函数的运作逻辑，把工作表上的每一个储存格当做对象来做计算处理。扩大而言
，在现有的函数公式里面，只要有涉及到范围，从哪个储存格到哪个储存格的，都可以写
成VBA的一个循环，从这个角度来思考VBA程式码，会是一个蛮有趣的过程、也会比较有成
就感。
延伸阅读：
VBA如何自动建立资料夹（下）
http://www.b88104069.com/archives/4193
VBA如何合并报表资料
http://www.b88104069.com/archives/4220
VBA如何保持档案干净
http://www.b88104069.com/archives/4230