excel - XLSXWriter FILTER() : Reference to Other Worksheet?

Question

我有一个名为 Sheet1 的选项卡。它从名为 Data_Summary 的选项卡中过滤数据.

如果我在 Sheet1 的单元格 A2 中输入此 FILTER() 函数，然后按回车键:

=FILTER(Data_Summary!A20:B91,Data_Summary!B20:B91="Assigned")

...一切都按预期工作:



但如果我通过 XLSXWriter 输入:

worksheet.write_array_formula("A2:B73", "=_xlfn._xlws.FILTER(Data_Summary!A20:B91,Data_Summary!B20:B91=\"Assigned\")")

...过滤器仍然有效，但在被过滤的行之后的行中，我得到很多包含 #N/A 的行.



我该如何纠正？

Answer 1

你得到了很多 N/A 值，因为你定义了一个比返回结果更大的数组。如果您手动输入 FILTER() 函数，它会根据返回的结果数自动完成。但是使用 xlsxwriter 除了使用 write_array_formula 方法和定义特定范围之外，我找不到编写公式的方法。这不方便，因为我想大多数时候你不知道你会得到多少结果。

我通过计算您在您的范围内出现的字符串“已分配”的出现次数找到了一个解决方案，然后将此数字用作公式数组的结束行。但这需要您通过 pandas 导入数据框。据我所知，无法使用 xlsxwriter 读取单元格的值并稍后在公式中使用它，也许约翰可以对此进行更多说明。

以下是基于您提供给我们的信息的工作示例:

import pandas as pd
import numpy as np

# Cretae a test df
df = pd.DataFrame({'Track Number': ['Track #1','Track #2','Track #3','Track #4','Track #5',
                                    'Track #6','Track #7','Track #8','Track #9','Track #10'],
                   'Status': ['Assigned',np.nan,'Assigned',np.nan,np.nan,
                              'Assigned','Assigned',np.nan,np.nan,np.nan]})

# Start the xlsxwriter
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')
workbook  = writer.book
worksheet = workbook.add_worksheet()

# You could count them using excel's native formula but you will not be able to store it
# into a variable as the formulas are calculated when the file opens
#worksheet.write_formula('D1', '=COUNTIF(Data_Summary!B:B,"Assigned")')

# Using pandas is possible though
# Use loc or iloc for specific range, otherwise pass in the whole column df['Status']
count = df.loc[0:10,'Status'].str.count('Assigned').sum()
worksheet.write_array_formula(f'A1:B{count}', '=_xlfn._xlws.FILTER(Data_Summary!A1:B11,Data_Summary!B1:B11="Assigned")')

# Pass the main df to a second sheet named Data_Summary
df.to_excel(writer, sheet_name='Data_Summary', index=False)

writer.save()

输出 :

主要df:



结果:


如果我定义的范围比返回的结果更大，例如:

worksheet.write_array_formula('A1:B8', '=_xlfn._xlws.FILTER(Data_Summary!A1:B11,Data_Summary!B1:B11="Assigned")')

我回来了#N/As: