XLSXWriter和Excel"= FILTER()"功能? [英] XLSXWriter and Excel "=FILTER()" Function?

问题描述

我已经使用XLSXWriter成功创建了许多Excel工作簿.现在，我试图将Excel的新功能(截至2019年)中的FILTER函数之一放入单元格中:

I'm successfully creating many Excel workbooks using XLSXWriter. Now I'm trying to place one of Excel's new (as of 2019) FILTER functions into a cell:

=FILTER(A19:B90,B19:B90=E19)

当我打开工作簿时，Excel会给我这个错误对话框:

When I open the workbook, Excel gives me this error dialog:

工作簿打开，但是单元格中的"0"而不是FILTER函数.

The workbook opens, but a "0" is in the cell instead of the FILTER function.

但是，如果我将完全相同的过滤器功能手动粘贴到同一单元格中，它将起作用！

But if I paste the exact same filter function into the same cell manually, it works!

我正在创建的所有其他公式均按预期工作，并且如果我使用XLSXWriter将通用函数放置到我希望过滤器通过的同一个单元格中，例如=100 *5，它也可以工作.

All the other formulas I'm creating work as expected, and if I use XLSXWriter to place a generic function into the same cell where I want the filter to go, e.g. =100 *5, it also works.

在使用=FILTER()函数时，XLSXWriter是否存在错误?

Does XLSXWriter have a bug when it comes to using =FILTER() functions?

推荐答案

这有点奇怪.来自在Excel 2010中添加了公式及以后:

Excel 2010及更高版本添加了原始文件规范中未定义的功能. Microsoft将这些功能称为将来的功能.这些功能的示例是ACOT，CHISQ.DIST.RT，CONFIDENCE.NORM，STDEV.P，STDEV.S和WORKDAY.INTL.

使用write_formula()编写时，这些功能需要使用_xlfn.(或其他)前缀完全限定，如下面的列表所示.例如:

When written using write_formula() these functions need to be fully qualified with a _xlfn. (or other) prefix as they are shown the list below. For example:

worksheet.write_formula('A1', '=_xlfn.STDEV.S(B1:B10)')

它们将在Excel中不带前缀出现

They will appear without the prefix in Excel

但是，此公式具有_xlfn._xlws.前缀，并且它也是一个数组公式，因此您必须执行以下操作:

However, this formula has a _xlfn._xlws. prefix and is also an array formula so you would have to do this:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

worksheet.write_array_formula('C1:D72', 
                              '=_xlfn._xlws.FILTER(A19:B90,B19:B90=E19)')

workbook.close()

输出:

XlsxWriter输出和Excel的输出之间的一个区别是，这将数组公式显示为{FILTER(...)}，并带有大括号，这是数组公式的典型代表，但Excel却没有.但是，我认为该公式符合预期.您可以在更复杂的示例中进行尝试进行验证.

One difference between the XlsxWriter output and Excel's is that this shows the array formula as {FILTER(...)}, with braces that are typical of array formulas, but Excel doesn't. However, I think the formula works as intended. You can try it in a more complex example to verify.

这篇关于XLSXWriter和Excel"= FILTER()"功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！