如何在Excel Data Model中使用动态普通数据范围（非表格格式）

数据源位于工作簿的普通数据区域（非表格格式），每月数据范围会变化，需要在Data Model中用变量定义动态范围，同时关注代码中引号与&符号的使用是否正确，以及其他引入数据区域地址的方式。另外，尝试借助Data Model获取数据集的唯一计数信息，但参数部分的动态范围定义无法写出合适代码，想了解能否用相对/绝对引用或其他变量来定义数据范围。

我尝试的代码 #

Dim myrange As String
myrange = Sheets("EKPO").Range("A1").CurrentRegion.Address

Workbooks("Book1").Connections.Add2 "WorksheetConnection_EKPO!" & myrange,
, "", "WORKSHEET;\[Book1\]EKPO!" & myrange, "EKPO!" & myrange, 7, True, False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=
ActiveWorkbook.Connections("WorksheetConnection_EKPO!" & myrange), Version
:=8).CreatePivotTable TableDestination:="Data model!R1C1", TableName:=
"PivotTable2", DefaultVersion:=8

1. 动态范围的正确定义 #

用CurrentRegion.Address获取动态范围的思路是可行的，但要注意Address默认返回带$的绝对引用，这在连接定义中是合法的；如果需要更灵活的引用形式，可以指定参数：

' 返回不带$的相对引用（连接通常需要绝对引用，按需选择）
myrange = Sheets("EKPO").Range("A1").CurrentRegion.Address(RowAbsolute:=False, ColumnAbsolute:=False)

另外，CurrentRegion会从A1扩展到连续非空单元格区域，若数据中间有空行/空列，范围可能不准确，这种情况可以基于最后一行/列自定义动态范围：

Dim lastRow As Long, lastCol As Long
lastRow = Sheets("EKPO").Cells(Sheets("EKPO").Rows.Count, "A").End(xlUp).Row
lastCol = Sheets("EKPO").Cells(1, Sheets("EKPO").Columns.Count).End(xlToLeft).Column
myrange = Sheets("EKPO").Range(Sheets("EKPO").Cells(1, 1), Sheets("EKPO").Cells(lastRow, lastCol)).Address

2. 引号与&符号的使用修正 #

你的代码中引号和&拼接逻辑是正确的，但有两个细节需要调整：

• 动态生成的连接名称（如WorksheetConnection_EKPO!$A$1:$D$100）过长且不固定，建议改用固定名称，避免后续调用出错：

Dim connName As String
connName = "EKPO_Data_Connection"
Workbooks("Book1").Connections.Add2 connName, , "", "WORKSHEET;\[Book1\]EKPO!" & myrange, "EKPO!" & myrange, 7, True, False
' 后续直接通过固定名称调用连接
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:=ActiveWorkbook.Connections(connName), Version:=8)...

• VBA代码换行必须用下划线_，原代码缺失下划线会报错，修正后如下：

Workbooks("Book1").Connections.Add2 "WorksheetConnection_EKPO!" & myrange, _
, "", "WORKSHEET;\[Book1\]EKPO!" & myrange, "EKPO!" & myrange, 7, True, False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, SourceData:= _
ActiveWorkbook.Connections("WorksheetConnection_EKPO!" & myrange), Version _
:=8).CreatePivotTable TableDestination:="Data model!R1C1", TableName:= _
"PivotTable2", DefaultVersion:=8

3. 其他引入数据区域地址的方式 #

• Excel定义名称：在Excel界面给动态范围定义名称（如EKPO_Dynamic_Range），引用公式用=OFFSET(EKPO!$A$1,0,0,COUNTA(EKPO!$A:$A),COUNTA(EKPO!$1:$1))，然后在VBA中直接引用：

myrange = ThisWorkbook.Names("EKPO_Dynamic_Range").RefersToRange.Address

这种方式更直观，方便在Excel中检查范围是否正确。

• 直接传递Range对象：先获取Range对象再转成地址，代码更易读：

Dim dataRange As Range
Set dataRange = Sheets("EKPO").Range("A1").CurrentRegion
myrange = dataRange.Address

4. Data Model中实现唯一计数 #

无需复杂代码，创建透视表后通过界面操作即可：

1. 将需要计数的字段拖到值区域
2. 右键点击值区域字段，选择值字段设置
3. 在窗口中选择非重复计数

如果用VBA实现，可在创建透视表后添加字段并设置汇总方式：

Dim pt As PivotTable
Set pt = ActiveWorkbook.PivotTables("PivotTable2")
' 假设要计数的字段是"订单号"，设置为非重复计数
pt.AddDataField pt.PivotFields("订单号"), "唯一订单数", xlCountNums
pt.DataFields("唯一订单数").Function = xlCountUnique

内容的提问来源于stack exchange，提问作者Radoslav Tóth