返回目录：excel表格制作
近日网上看到一个案例，关于分类汇总的VBA文章。我模拟了该文中的一个数据，用来写此文章，数据本身不具备真实性，咱讲的主要是方法。
该文首先提出了一个问题，如下：该题主最近在做一个小定单，需要把其他多个工作表的数据按照城市汇总到一个工作表中。以其中一个工作表为例，需要汇总的数据如下图所示，共有4个数据表存在与同一个工作表中，每个表都含有"城市"、"排名"和"指数名称"3列，有100多行，各表都间隔1列，所在的工作表名称为"数字产业10大细分行业城市100强"。需要注意的是：数据表中的城市名称是简写，不含有"市"、"盟"、"地区"、和"特别行政区"等后缀。
汇总的Excel表格模板如下图所示，其所在的工作表名称为"大数据指数一致性评价（城市级）"。A列城市名称为完整名称，含有"市"、"盟"、"地区"、和"特别行政区"等后缀。"指数名称"行中，指数名称的顺序与要汇总的数据表的排列顺序一致。汇总模板与需要汇总的数据表位于同一个工作簿中。现在就市要把上述的数据汇总到该汇总模板中。
待汇总模板如下：
题主此时给出了一个解决思路：使用VBA两层嵌套循环来解决。具体表述为：外城循环从汇总模板的A列第第一个城市循环到最后一个城市，内层循环从需要汇总的数据表的第一个城市往下循环，并与汇总模板中的城市进行一一比对，如果相同，则把指数数据填写到汇总模板相应的位置中，然后退出内曾循环，外层循环到下一个城市，再次进入内容循环进行新一轮的比对，如此进行，直到外层循坏的最后一个城市，完成一个数据表的汇总。然后手动更改条件进行下一个数据表的汇总。
题主经过不断的程序修改，最终完成的代码如下：
Sub 一个分类汇总情形的案例分析之如何优化解决方法()Dim wk1 As Worksheet, wk2 As Worksheet '先定义了2个表的变量Dim i As Integer, j As Integer, k As Integer, l As Integer '又定义了一些循环变量'给2个变量赋值到2个表：Set wk1 = Worksheets("原始表"): i = 3Set wk2 = Worksheets("大数据指数一致性评价（城市级）"): j = 4'给变量初始值：k = 1l = 2n = 0'外层循环：Do While wk2.Cells(j, 1) <> ""i = 3 '内层循环变量重设初始值固定为3If TypeName(wk2.Cells(j, 1).Value) = "String" ThenDo While wk2.Cells(i, k) <> ""'城市名称一致的判断：If wk2.Cells(j, 1) = wk1.Cells(i, k) & "市" Or _wk2.Cells(j, 1) = wk1.Cells(i, k) & "盟" Or _wk2.Cells(j, 1) = wk1.Cells(i, k) & "地区" Or _wk2.Cells(j, 1) = wk1.Cells(i, k) & "特别行政区" Or _(Right(wk2.Cells(j, 1), 3) = "自治州" And Left(wk2.Cells(j, 1), 2) = Left(wk1.Cells(i, k), 2)) Thenwk2.Cells(j, l) = wk1.Cells(i, k + 2)wk1.Cells(i, k + 2).Interior.Color = vbGreen '把正确数据标为绿色n = n + 1Exit DoEnd Ifi = i + 1LoopElseMsgBox "存在非字符串数据"End Ifj = j + 1LoopMsgBox n & "个数据输入成功"End Sub
经过本人验证，此代码有效，经过运行，效果如下：
以上，就是题主的VBA解决办法。
问题似乎部分解决了，只不过就是需要一列一列的改代码，仍需要手动操作才能把剩下其他列的数据统计到汇总模板中，所以这段代码仍旧是一个不完整的代码。那么有什么更好的办法呢？实际上我们解决问题应掌握一个原则，就是简洁高效。有很多方法都能解决这种问题。下面介绍一种不用写VBA的方式。
其实很简单就能完成的工作任务。我们来看看函数是否也能够完成任务。首先，我们来使用vlookup函数，
我们直接在表"大数据指数一致性评价（城市级）"中的B3单元格输入：=VLOOKUP(A3,原始表!$A:$C,3,1)。然后鼠标移动到单元格右下角，鼠标变成实心十字时双击鼠标，完成了第一列的计算。同理，后面3列操作时一样的。经过函数计算。现在初步结果算完了，如下：
我们可以看到大多数结果都是错的，那么原因就是查询的城市名称中后缀不带"市"字。那么怎么办？一般人可能想到如下思路：
修改原始表中的城市名称，都加上"市"字，有省的就不要加任何字。具体方法很多。比如我现在可以给出一个方法，先筛选出来所有的市，然后批量加上"市"字，就可以了。操作步骤就是：同时按下Ctrl+Shift+L，在城市列下拉-文本筛选-自定义筛选-不包含"省"与不包含"自治区"。
然后我们选中B列，右键插入2列空列：
然后我们在插入的B列输入"市"，在插入的C列将A3与B3连接，如下：
确定，我们就得到了结果，然后自动填充B列和C列后，在复制C列，选择性粘贴数值到C列，就完成了"市"字的填充。然后同样筛选的方法，选出省、自治区等，将等号直接将A列结果赋值到C列，然后复制C列结果，选择性粘贴数值到C列。然后去掉筛选功能。删除A列和B列，将C列写好题目"城市"。一气呵成，搞定名称。
然后我们返回看到结果，第一列的结果就正确了。那么后面３列仍按上述操作即可。
这是手动解决的上述问题。那么有没有不这么麻烦的手动操作方法呢。我们来继续探讨。可以用if函数结合到Vlookup中，先来个判断，然后再做查询。
那么构造如下函数，在B３单元格输入下面的函数，然后自动填充公式，一步搞定
=VLOOKUP(IF(RIGHT(A3,1)="市",LEFT(A3,LEN(A3)-1),A3),原始表!A:C,3,FALSE)
结果如下：
综上，题主提出的此问题，VBA也可以解决，手动也可以解决，函数也可以解决。但是最优解暂时来看，还是构造函数。直接在单元格中输入自己构造的一个函数：
=VLOOKUP(IF(RIGHT(A3,1)="市",LEFT(A3,LEN(A3)-1),A3),原始表!A:C,3,FALSE)
就搞定了这些数据。完美……
你，学会了吗？