我最近使用名为gnumeric
的命令行实用程序将一个名为template.xlsx
的文件转换为template.ods
。除以下公式外,所有公式都能正确转换:
=LOOKUP(2,1/(INDIRECT(CONCATENATE("Import!$F$",Q3,":$F$",M3))>=(S3)*VALUE(SUBSTITUTE($S$1,"LOOKUP FACTOR x",""))),INDIRECT(CONCATENATE("Import!$B$",Q3,":$B$",M3)))
这个公式在某种程度上是很长的,但为了简短起见,我有两张纸,一张叫Import
,另一张是Lookup
。我想返回特定范围中的最后一个值(并匹配另一个范围中的行),该值大于或等于S3
中的值乘以LOOKUP FACTOR x
,例如,如果是LOOKUP FACTOR x2
,则S3
中的值将乘以2。
我发现OpenOfficeCalc从另一个工作表访问范围的方式与Excel的方式不同,因此我将公式改写为:
=LOOKUP(2;1/(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))>=(S3)*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";"")));INDIRECT(CONCATENATE("$Import.$B$";Q3;":$B$";M3)))
因此,将表单名称从Import!$F$
更改为$Import.$F$
。列$B$
也是如此。
当我运行此公式时,OpenOffice返回错误#DIV/0!
。
所以,如果我把这个公式分成各个部分:
=INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))
=INDIRECT(CONCATENATE("$Import.$B$";Q3;":$B$";M3))
=(S3)*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";""))
每当我在不同的单元格中执行它们时,我在两个电子表格程序中都会得到相同的结果,这意味着它们分别执行WORK。这让我相信问题出在LOOKUP
上。
在上面的情况下,我使用INDIRECT
的原因是因为Q3
和M3
都引用了范围的开始和结束:
Q3
包含:
{=MIN(IF($Import.$A$1:$A$1048576=A3;ROW($Import.$A$1:$A$1048576)-ROW(INDEX($Import.$A$1:$A$1048576;1;1))+1))}
M3
包含大致相同但使用MAX
:
{=MAX(...)}
这些将返回数组的起始和结束位置的索引。假设A3
等于Apple
,则Q3
将返回Apple
第一次出现的行,Q3
将返回Column A
中Apple
最后一次出现的列。
值得重申的是,ALL公式除LOOKUP
外都有效,这意味着上述单元格引用B3
和M3
返回正确的索引位置(或行)。同样值得一提的是,该公式确实适用于Excel。
有人知道为什么LOOKUP
公式在OpenOffice中不起作用吗?
这可以在没有LOOKUP
的情况下在其他地方完成吗?
不知道它在Excel中是如何工作的,但在Calc中,如果左侧的值大于或等于右侧的值,>=
只会返回true。因此,听起来我们需要添加一个IF
语句来满足您的要求。
以下数组公式查找条件返回true的最高行。然后它从该单元格中获取值。
=INDIRECT("Import.F"&MAX(IF(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))>=S3*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";""));ROW(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3)));0)))
细分:
- 间接(-从该单元格获取值
- "Import.F"&-这一行加上我们要查找的地址
- MAX(-获得有效的最高行号,因为我们想要该范围中的最后一个值
- IF(-如果值大于或等于,则返回行号,否则返回零
- 间接(CONCATNATE("$Import.$F$";Q3;":$F$";M3))>=S3*VALUE(SUBSTITE($S$1;"LOOKUP FACTOR x";"))-如果值大于或等于,则返回true,否则返回false
- ;ROW(INDIRECT(CONCATNATE("$Import.$F$";Q3;":$F$";M3))-获取当前正在查看的行。通过实现返回行号数组的用户定义函数,可以将其缩短为
CREATEARRAY(Q3;M3)
- ;0-如果不大于或等于,则将行号返回为零