英文:
Is there a way to trigger a dynamic "spilled" array in Excel to resize?
问题
以下是翻译的内容：
背景简要说明：
1）我正在创建一个包括一个名为“Data”的工作表用于原始数据（将以编程方式插入）以及使用公式从原始数据派生其他工作表的“模板”xlsx文件。
2）我正在使用EPPlus将原始数据插入“Data”工作表。
3）我还有另一个工作表，它使用UNIQUE()函数从“Data”工作表的一列中创建一个唯一值的动态数组：
=UNIQUE(Data!A2:A10000,FALSE,FALSE)
问题在于，使用EPPlus以编程方式插入原始数据后，UNIQUE()公式不会自动“溢出”。它会更新第一行的值，但不会“扩展”以显示完整的行数。
（注：我也尝试了使用FILTER()等产生动态数组的其他公式，结果相同。）
根据这篇文章：https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
当您按Enter键确认您的公式时，Excel会为您动态调整输出范围的大小，并将结果放入该范围内的每个单元格。
我的问题是，是否有一种方法可以触发调整输出范围的大小，而无需按Enter键？如果可能，我希望在打开Excel文件时自动调整大小，而无需使用脚本。生成的Excel文件面向最终用户，我不希望让他们专注于公式并按Enter键以查看计算结果。
英文:
A little background on my problem:
- I am creating a "template" xlsx file that includes one "Data" worksheet for raw data (that will be inserted programmatically) and other worksheets that use formulas to derive from the raw data.
- I am using EPPlus to insert the raw data into the "Data" worksheet.
- I have another worksheet that uses the UNIQUE() function to create a dynamic array of unique values from one of the columns in the "Data" worksheet:
=UNIQUE(Data!A2:A10000,FALSE,FALSE)
The problem is that after programmatically inserting the raw data using EPPlus, the UNIQUE() formula does not automatically "spill". It does update the value in the first row, but it doesn't "expand" to show the full number of rows.
(Note: I've also tried other formulas that produce a dynamic array, such FILTER(), with the same result.)
According to this article: https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
> When you press Enter to confirm your formula, Excel will dynamically size the output range for you, and place the results into each cell within that range.
My question is, is there a way to trigger sizing the output range without having to press Enter? If possible, I would like it to resize when opening the Excel file, without any scripting. The generated Excel file is intended for the end user and I don't want to make them focus the formula and press Enter in order to see the calculated results.
答案1
得分: 1
我发现GrapeCity的GcExcel组件支持动态数组。使用GcExcel而不是EPPlus解决了这个问题。要触发GcExcel更新溢出范围：
var f = cell.Formula2;
cell.Formula2 = null;
cell.Formula2 = f;
我将把这个回答视为目前为止最佳答案。然而，根据EPPlus的GitHub问题（https://github.com/EPPlusSoftware/EPPlus/issues/768）：
是的，我们正在进行一项重大的依赖链和表达式处理的重新设计，在那里我们将实现动态数组公式。
英文:
I found that GrapeCity's GcExcel component does support dynamic arrays. Using GcExcel instead of EPPlus solved the problem. To trigger GcExcel to update the spill range:
var f = cell.Formula2;
cell.Formula2 = null;
cell.Formula2 = f;
I'm going to accept this as the best answer so far. However, according to this github issue on EPPlus: https://github.com/EPPlusSoftware/EPPlus/issues/768
> Yes, we are working on a major redesign of the dependency chain and
> expression handling where we will implement dynamic array formulas
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论