PHPSpreadsheet生成错误"INDEX()函数的参数数量错误:给定5个，期望值在1到4之间". [英] PHPSpreadsheet generates an error "Wrong number of arguments for INDEX() function: 5 given, between 1 and 4 expected"

问题描述

我试图直接在excel公式栏中输入以下代码 =INDEX($E$4:$E$132,AGGREGATE(15,6,ROW($1:$30) / ($J$4:$J$132=M4), COUNTIF($M$4:M4, M4)))并可以正常工作(下图的左列).

I tried to enter the following code in my excel formula bar directly =INDEX($E$4:$E$132,AGGREGATE(15,6,ROW($1:$30) / ($J$4:$J$132=M4), COUNTIF($M$4:M4, M4))) and works perfectly fine (the left column on the pic below).

但是，如果我使用Web应用程序来生成excel报告文件(PHP，使用CodeIgniter和Laravel).它显示错误"'INDEX()函数的参数数量错误:给定5个，预期值介于1到4之间'"

But if I'm using my web application to generate an excel report file (PHP, using CodeIgniter and Laravel). It displays an error "'Wrong number of arguments for INDEX() function: 5 given, between 1 and 4 expected'"

这是我的示例代码片段:

Here's my sample code snippet:

$code = "=INDEX(\$E\$4:\$E\$$occurance, AGGREGATE(15,6,ROW(\$1:\$$occurance) / (\$J\$4:\$J\$$occurance=M$top_cell), COUNTIF(\$M\$4:M$top_cell, M$top_cell)))";
$ews2->setCellValue("L$top_cell", $code);

我也尝试使用 setValueExplicit 方法，但是导致excel文件未预先计算代码，而是将代码读取为 string

I also have tried to use the setValueExplicit method but causes the excel file to NOT precalculate the code, it reads the code as a string

$ews2->setCellValueExplicit("L$top_cell", $code, DataType::TYPE_STRING);

注意之所以提供TYPE_STRING，是因为如果还使用TYPE_FORMULA，则会出现顶部提到的相同输出

NOTE TYPE_STRING is provided because if TYPE_FORMULA is also used, the same output mentioned at the top occurs

这是使用 setCellValueExplicit

我可以为此找到合适的解决方案或快速解决方案吗?提前非常感谢您！

May I know the right solution or quick fix for this? Thank you very much in advance!

推荐答案

我发现PHP的PHPSpreadsheet库尚未允许使用AGGREGATE()和复杂的公式/函数，因此我找到了另一种方法

I have found out that the PHPSpreadsheet library for PHP is yet to allow the usage of the AGGREGATE() and complicated formulas/functions, so I had found another way around

通过使用此excel代码

By using this excel code

=INDEX(E$2:E$38,IF(M4=M3,MATCH(L3,E$2:E$38,0),0)+MATCH(M4,OFFSET(J$2,IF(M4=M3,MATCH(L3,E$2:E$38,0),0),0,COUNT(J$2:J$38)-IF(M4=M3,MATCH(L3,E$2:E$38,0),0),1),0))

我能够遍历整个范围，并确保不会出现重复的出版物名称

I was able to traverse through my entire range and make sure that no duplicate publication names would appear

这与我的其他问题有关->

This is in relation with the my other question -> Excel - Getting the 2nd or nth matched string from your corresponding data

这篇关于PHPSpreadsheet生成错误"INDEX()函数的参数数量错误:给定5个，期望值在1到4之间".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！