楼主
kevinchengcw |
本例为从一个复杂的数据区域（含合并单元格，缩写编码及区间设定等项目）中查询并计算出数据结果的实例，其中数据区和结果区的抓图如下图：
其中左侧表格为要查询的数据源表，右侧表格为结果存放表格，需根据右侧表格前三列的数据在左侧表格中查询并计算出第四列的值，其中各表的红色区域是对应的邮编（左侧表是缩写形式），蓝色区域的对应的国家区域，黄色区域是对应的重量值（左侧表是区间上下限的两列形式）。
本例涉及到的难点：
1.从合并单元格区域查询出国家，并找到对应的列区间用于查询邮编号区间;
2.从查询对应的邮编号缩写区域中查询对应的结果表中邮编号于查询表中所处的列号;
3.从查询表的重量区间计算出结果并写入结果表。
下面来看一下代码：- Sub test()
- Dim M, N, I As Long
- Dim Sh1, Sh2 As Worksheet
- Set Sh1 = Worksheets("sheet1") '定义两个工作表变量，分别对应查询表(sheet1)和结果表(sheet2)，这样的好处是如果更改了表名需要修改的地方仅此两处
- Set Sh2 = Worksheets("sheet2")
- Application.ScreenUpdating = False '关闭屏幕刷新，提高速度
- Sh1.Activate '因为一会我们要用到selection属性处理合并单元格，所以激活对应的工作表以免出错
- For M = 2 To Sh2.Cells(Rows.Count, 1).End(3).Row '设定结果表的行数区间进行循环
- Sh1.Cells.Find(Sh2.Cells(M, 3).Value).Select '在查询表中先查找结果表中当前行的C列的值（即国家名），并选中该单元格，这样我们可以用selection.columns.count来判断合并单元格的列数
- For N = Sh1.Cells.Find(Sh2.Cells(M, 3).Value).Column To Sh1.Cells.Find(Sh2.Cells(M, 3).Value).Column + Selection.Columns.Count - 1 '设定变量N从合并单元格的第一列向最后一列循环，注意第一列和最后一列的表达方式
- If Sh1.Cells(3, N) = "所有邮编" Then '因为查询表的邮编都存储在第三行，所以进行判断，如果对应国家的当前循环列的内容为“所有邮编”，那么我们进行下一步的操作
- If Sh2.Cells(M, 2).Value > 100 Then '如果结果表当前行的B列的值大于100，则
- Sh2.Cells(M, 4) = Sh1.Cells(203, N) + IIf((Sh2.Cells(M, 2).Value * 10) Mod 5 = 0, (Sh2.Cells(M, 2).Value - 100) * 2, Int((Sh2.Cells(M, 2).Value - 100) * 2) + 1) * Sh1.Cells(206, N).Value '直接去对应100的位置取值，并相加上每超0.5所对应的加价
- Else '如果不大于100，则在表中查找对应的数值
- For I = 4 To Sh1.Cells.SpecialCells(xlCellTypeLastCell).Row '设定循环取值为从查询表的第四行到最后一行
- If Sh1.Cells(I, 1) = "" Then Exit For '因为查询表的特点是一遇到空行即是查询表中区间查询的结尾，所以退出循环
- If Sh2.Cells(M, 2).Value > Sh1.Cells(I, 1).Value And Sh2.Cells(M, 2).Value <= Sh1.Cells(I, 2).Value Then '以查询表的A、B列的值作为区间的两极判断结果表中当前行的B列值（即重量）是否在该区间，如果符合
- Sh2.Cells(M, 4) = Sh1.Cells(I, N) '将查询表的当前国家所在列范围的邮编对应列的值写入结果表的D列当前行
- Exit For '并退出邮编列的循环
- End If
- Next I '如果没有符合条件的项，则进入查询表的下一行查询
- End If
- Exit For '当邮编列的内容是“所有邮编”时该国家不会再有其他的列，所以同样要在完成任务后退出循环
- ElseIf (Sh2.Cells(M, 1).Value >= Trim(Split(Sh1.Cells(3, N), "till")(0)) And Sh2.Cells(M, 1).Value <= Trim(Split(Sh1.Cells(3, N), "till")(1))) Then '另一个进行查询的情况是当邮编位于该邮编列的区间范围中，注意，区间范围由单元格内容依特定字符"till"进行分割，前一个值为下限，后一个值为上限，注意用trim去掉两端空格，防止出错
- If Sh2.Cells(M, 2).Value > 100 Then '以下判断同上面相同
- Sh2.Cells(M, 4) = Sh1.Cells(203, N) + IIf((Sh2.Cells(M, 2).Value * 10) Mod 5 = 0, (Sh2.Cells(M, 2).Value - 100) * 2, Int((Sh2.Cells(M, 2).Value - 100) * 2) + 1) * Sh1.Cells(206, N).Value
- Else
- For I = 4 To Sh1.Cells.SpecialCells(xlCellTypeLastCell).Row
- If Sh1.Cells(I, 1) = "" Then Exit For
- If Sh2.Cells(M, 2).Value > Sh1.Cells(I, 1).Value And Sh2.Cells(M, 2).Value <= Sh1.Cells(I, 2).Value Then
- Sh2.Cells(M, 4) = Sh1.Cells(I, N)
- Exit For '当找到结果后退出循环，可以节省一定的时间
- End If
- Next I
- End If
- Exit For '当找到结果后退出循环，可以节省一定的时间
- End If
- Next N
- Next M '循环到结果表的下一行取值
- Sh2.Activate '激活结果表
- Application.ScreenUpdating = True '打开屏幕刷新
- MsgBox "查询完成", vbOKOnly, "" '显示提示框
- End Sub
附示例文件
 Book1.rar
|
