WorksheetFunction数组大小限制 [英] WorksheetFunction array size limit

问题描述

我正在使用百分位数函数计算VBA中数组（arr1）中100000个数据集的99.5％百分位数，如下所示：

  Pctile = Application.WorksheetFunction.Percentile（arr1，0.995）
 Pctile = Application.WorksheetFunction.Percentile_Inc（arr1，0.995）
  pre> 
 
 
无论如何，我不断得到类型不匹配（13）。 
 
 
 
如果我将数组大小限制为最大值65536，代码运行正常。据我所知由可用内存限制的计算，因为Excel 2007 数组大小。
 
 
 
我在高性能服务器上使用Excel 2010。有人可以确认这个问题存在吗？假设这样，我认为我的选择是建立一个vba函数来计算手动的百分位数或输出到工作表，计算它并读回来。有没有任何替代方案，什么是最快的？
解决方案
 这是一个经典的VBA示例，模拟Excel百分位数函数。 
 
 
 
  百分位数和置信度（Excel-VBA）  
 
 
  Straight Insertion 。我已经通过以下内容编辑了这个答案：
 
 
 
我读到QuickSelect似乎超大的记录，效率非常高。
 
 
 
参考文献：
 
 
  Wikipedia.org：快速选择 
 
 
 AC＃实现可以在@ 用于计算百分位数以消除离群值的快速算法，这应该很容易转换为VB。
 
 
 
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?
解决方案
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:

Wikipedia.org: Quick Select 
A C# implementation can be found @ Fast Algorithm for computing percentiles to remove outliers which should be easily converted to VB.


                        
这篇关于WorksheetFunction数组大小限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！