当中值计算为零时崩溃（程序没有响应）（VBA）
当我使用 VBA 计算数组的中位数（在中位数函数的计算结果为零的情况下）时，我遇到了 Excel 2010 崩溃的问题（Windows XP 表示 Excel“没有响应”）。不幸的是，该问题仅在特定代码集的上下文中才能重现（当我尝试编写简化版本以进行调试时，该问题不会发生）。尽管如此，我认为有人可能会因为以下原因发现这个问题很有趣。
崩溃发生在 application.worksheetfunction.median 被调用时。 VBA 无法通过该调用。不存在随后归零的问题。
即使数组非常小（例如 8），也会发生崩溃。
我发现一个有效的解决方法是向每个数组项添加一个小常量，然后计算中位数，然后立即从每个数组值中减去该常量。这似乎产生了正确的答案。但令我担心的是 Excel 需要这种解决方法。
另一个涉及排序的工作表函数也会出现此问题：百分位函数。
I have a problem with Excel 2010 crashing (Windows XP says Excel is "not responding") when I use VBA to calculate the median of an array, in cases where the median function evaluates to zero. Unfortunately the problem is reproducible only in the context of a particular set of code (when I try to write simplified version, for debugging, the problem does not occur). Nevertheless, I think someone ma find this problem interesting for the following reasons.
The crash happens while application.worksheetfunction.median is being called. VBA cannot get past that call. It is not a problem of subsequently diving by zero.
The crash happens even if the array is very small (e.g., 8).
I've found that an effective workaround is for me to add a small constant to each array item, then calculate the median, then immediately subtract the constant from each array value. This appears to produce a correct answer. But it worries me that Excel requires this workaround.
The problem also occurs with another worksheet function that involves sorting: the percentile function.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
谢谢。我现在可以复制这个问题。对我来说，这像是 Excel 2010 中的一个错误。缩小范围，它看起来像是与中位数中极小的数字的浮点问题相关的错误：例如，您可以通过在调用 MEDIAN 之前添加一个零来绕过该问题
。
如果您允许，我将使用您的测试文件将此作为错误报告给 Excel 团队。
Thank you. I can now duplicate the problem. Looks like a bug in Excel 2010 to me. Narrowing it down it looks like a bug related to floating point problems with extremely small numbers in Median: for instance you can bypass the problem by addin a zero to
to make
before calling MEDIAN.
If you will allow it I will report this as a bug to the Excel team using your test file.
我在类似的条件下遇到了同样的问题 - 除了在 Excel 2003 中，并且仅在计算 Double 或 Single 类型的数组时。也就是说，当计算的“答案”为零时，Excel 在计算数组的中位数时只是挂起（没有响应）。
计算数组的百分位数或修剪均值函数时相同。
如果我将数组写入一个范围，然后使用公式（“=MEDIAN(myRng)”等）进行计算，Excel 不会冻结。仅当计算 Double 或 Single 类型的数组时。 （Excel 似乎不介意我使用 Long；还没有尝试过 Integer。）
我使用了上面建议的解决方法：
{我的计算在这里，然后：}
似乎有效，作为一个拼凑。 （但表明我需要迁移 Excel 来进行任何严肃的统计工作。我想，是时候最终学习 C++ 了，否则就哭吧。）
I have been getting the same problem under similar conditions -- except in Excel 2003, and only when calculating an array of type Double or Single. That is, Excel just hangs (no response) when calculating the median of an array, when the "answer" to the calculation is zero.
Same when calculating the percentile or trimmean functions of an array.
If I write the array to a range, then use a formula ("=MEDIAN(myRng)" etc.) for the calculation, Excel doesn't freeze. Only when calculating an array of type Double or Single. (Excel doesn't seem to mind if I use Long; haven't tried Integer.)
I've used a workaround as suggested above:
{my calculations here, then:}
Seems to work, as a kludge. (But shows that I need to migrate off Excel for any serious statistical work. Time to finally learn C++, I guess, or cry tryin'.)