WorksheetFunction 数组大小限制
我试图使用百分位数函数计算 VBA 中数组 (arr1) 中包含 100000 个值的数据集的 99.5% 百分位数，如下所示：
Pctile = Application.WorksheetFunction.Percentile(arr1, 0.995)
Pctile = Application.WorksheetFunction.Percentile_Inc(arr1, 0.995)
两者都不起作用，而且我不断收到类型不匹配的信息 (13)。
如果我将数组大小限制为最大 65536，则代码可以正常运行。据我所知 计算受可用内存限制 自 Excel 2007 自 Excel 2000 起，传递给宏时的数组大小受可用内存限制 。
我在高性能服务器上使用 Excel 2010 谁能确认这个问题是否存在？假设是这样，我认为我的选择是构建一个 vba 函数来“手动”计算百分位数或输出到工作表，在那里计算并读回。有其他选择吗？什么是最快的？
I'm trying to calculate the 99.5% percentile for a data set of 100000 values in an array (arr1) within VBA using the percentile function as follows:
Pctile = Application.WorksheetFunction.Percentile(arr1, 0.995)
Pctile = Application.WorksheetFunction.Percentile_Inc(arr1, 0.995)
Neither works and I keep getting a type mismatch (13).
The code runs fine if I limit the array size up to a maximum of 65536. As far as I was aware calculation limited by available memory since Excel 2007 array sizes when passing to macro limited by available memory since Excel 2000.
I'm using Excel 2010 on a high performance server. Can anyone confirm this problem exists? Assuming so, I figure that my options are to build a vba function to calculate the percentile 'manually' or output to a worksheet, calculate it there and read it back. Are there any alternatives and what would be quickest?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果 arr1 是一维且具有大于 65536 个元素，则会发生错误（请参阅 数组大小限制在 VBA 中传递数组参数）。维度 arr1 作为具有单列的二维数组：
这适用于 Excel 2013。根据 Charles 的回答，它似乎适用于 Excel 2010。
The error would occur if arr1 is 1-dimensional and has greater than 65536 elements (see Charles' answer in Array size limits passing array arguments in VBA). Dimension arr1 as a two-dimensional array with a single column:
This works in Excel 2013. Based on Charles' answer, it appears that it will work with Excel 2010.
这是一个模仿 Excel Percentile 函数的经典 VBA 示例。百分位数和置信水平(Excel-VBA)鉴于 Jean 曝光的 直接插入方法效率低下。我用以下内容编辑了这个答案：
我读到 QuickSelect 似乎擅长处理大型记录，并且这样做非常有效。
参考资料：
Here is a Classic VBA example that mimics the Excel Percentile function.Percentile and Confidence Level (Excel-VBA)In light of Jean's exposure of the Straight Insertion method being inefficient. I've edited this answer with the following:
I read that QuickSelect seems to excel with large records and is quite efficient doing so.
References: