写入结果电子表格时，AGGREGATE公式不会自动计算 [英] AGGREGATE formula not automatically calculating when written to results spreadsheet

问题描述

我有一个使用OPENPYXL(v2.5.10)库开发的python 3.7脚本，用于从许多excel工作簿中获取数据，处理该数据，然后将其写入单独的excel工作簿中.结果工作簿包含大约100个命名范围和众多公式，所有这些公式均按预期工作，包括自动计算在excel中打开工作簿的时间.

I have a python 3.7 script that has been developed using the OPENPYXL (v2.5.10) library to take data from a number of excel workbooks, to process that data and then to write to a separate excel workbook. The results workbook contains around 100 named ranges and numerous formula which all work as expected including automatically calculating when the workbook is opened in excel.

我遇到一个包含AGGREGATE函数的特定公式组.在这种情况下，公式将以正确的形式写到结果工作簿，正确的单元格中.虽然其他公式会在打开工作簿时显示结果值，但此公式序列仅在以下情况下显示结果:选择单元格，将光标置于公式栏中(就像您正在编辑公式一样)，然后按Enter/返回钥匙.不会对公式进行任何更改或编辑.完成此操作后，公式将按预期工作.

I am having trouble with one particular formula group which includes the AGGREGATE function. In this case the formula writes to the results workbook, to the correct cell and in the correct form. While other formulas show the resulting value on opening the workbook, this sequence of formulas only show a result if you select the cell, place the cursor in the formula bar (as if you are editing the formula) and then you push the enter/return key. No change or edit is made to the formula. Once you have done this the formula works as expected.

我已经在最新的macOS和Windows版本的excel上对此进行了测试，并且得到了相同的行为.我应该补充一点，尝试立即计算"，CtrlShiftAltF9和计算工作表"选项不会产生任何影响.公式仅计算一次使用回车键.

I have tested this on both the latest macOS and windows versions of excel and I get the same behaviour. I should add that trying the 'calculate now', CtrlShiftAltF9, and 'calculate sheet' options do not have any impact. The only time the formula calculates is if you use the enter/return key.

写公式的代码是:

activeSheet.cell(row，col).value = f"= IFERROR(INDEX({rngData}，AGGREGATE(15,3，({rng} = {cellRef}))/({rng} = {cellRef}) * ROW({rng})，{nth})，{colIndex})，\"\")"

activeSheet.cell(row, col).value = f"=IFERROR(INDEX({rngData}, AGGREGATE(15,3,({rng}={cellRef})/({rng}={cellRef})*ROW({rng}),{nth}),{colIndex}),\"\")"

例如，在excel工作簿单元格中给出的正确结果如下:

which gives, for example, a correct result in the excel workbook cell as:

= IFERROR(INDEX(_INDEX(_monthAgedDebt_Data，AGGREGATE(15,3，(_ monthAgedDebt_ProjectNumbers = $ L $ 4))/(_ monthAgedDebt_ProjectNumbers = $ L $ 4)* ROW(_monthAgedDebt_ProjectNumbers)，1)，6)，")

=IFERROR(INDEX(_monthAgedDebt_Data, AGGREGATE(15,3,(_monthAgedDebt_ProjectNumbers=$L$4)/(_monthAgedDebt_ProjectNumbers=$L$4)*ROW(_monthAgedDebt_ProjectNumbers),1),6),"")

总而言之:

• 代码可以正常工作，因为它将正确的公式以正确的形式写入正确的单元格
• 在excel中，公式不会自动计算，仅在您在单元格中编辑公式，不进行任何更改并按Enter/返回以退出编辑时才有效

AGGREGATE产生数组结果是否有问题?我之所以选择这种形式的公式，主要是因为您不需要CTL-SHIFT-ENTER即可起作用.如果直接将其输入到excel的单元格中，则可以将其作为常规公式输入.

Is it an issue with AGGREGATE producing an array result? I chose this form of formula principally because you do not need a CTL-SHIFT-ENTER to make it work. If you enter it directly into a cell in excel you can enter it as a normal formula.

除了这个

I haven't been able to find help on stack overflow other than this one. However, the solution proposed here doesn't work either.

此问题提出了类似的问题，但没有相关的答复

This question poses a similar issue but has no relevant responses.

此问题可能有一个线索，但我不知道似乎也无法做到这一点.

This question may hold a clue but I don't seem to be able to make that work as well.

任何有关如何解决此问题的想法都值得赞赏.我不确定这是openpyxl问题还是excel问题，也不确定要测试什么.

Any thoughts on how to fix this issue appreciated. I am not sure if it is an openpyxl issue or an excel issue and am not sure what else to test.

推荐答案

全部-完整性的最终答案.

All - the final answer for completeness.

事实证明，解决此问题的关键在于OPENPYXL，查理·克拉克(Charlie Clark)对此的回答提供了指导

It turns out that the key to solving this issue lay with OPENPYXL and that the guidance provided in the answer by Charlie Clark to this

问题

是正确的.我最初错误地应用了该解决方案.

was correct. I had initially applied the solution incorrectly.

我将公式更改为:

activeSheet.cell(row，col).value = \ f"= IFERROR(INDEX({EXR({rngData}，_xlfn.AGGREGATE(15,3，({RNG} = {cellRef})/({rng} = {cellRef})* ROW({rng})，{nth}) ，{colIndex})，\"\")"

activeSheet.cell(row, col).value = \ f"=IFERROR(INDEX({rngData}, _xlfn.AGGREGATE(15,3,({rng}={cellRef})/({rng}={cellRef})*ROW({rng}),{nth}),{colIndex}),\"\")"

添加"_xlfn".放在AGGREGATE函数语句的前面.

by adding in the '_xlfn.' to the front of the AGGREGATE function statement.

Excel电子表格现在可以按预期工作，而无需编辑包含公式的单元格.

The excel spreadsheet now works as expected without the need to edit the cell containing the formula.

这篇关于写入结果电子表格时，AGGREGATE公式不会自动计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！