Excel查找公式在OpenOffice Calc中不起作用 [英] Excel lookup formula not working in OpenOffice Calc

问题描述

我最近使用名为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.我想返回特定范围内的 last 值(并使其行与另一个范围匹配)，该值大于或等于S3中的值乘以LOOKUP FACTOR x的值，例如如果为LOOKUP FACTOR x2，则将S3中的值乘以2.

我发现 OpenOffice Calc 从另一个工作表访问范围的方式与 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将返回Apple的最后一次出现在Column A的行.

值得重申的是，除了LOOKUP以外， ALL 公式均适用，这意味着上述单元格引用 B3和M3返回正确的索引位置(或排).还值得一提的是，该公式确实适用于Excel.

有人知道为什么LOOKUP公式在OpenOffice中不起作用吗?

如果没有LOOKUP，可以这样做吗?

解决方案

不知道它如何在Excel中工作，但是在Calc中，如果左侧的值大于或等于on的值，则>=只是返回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)))

故障:

• INDIRECT(-从该单元格中获取值
• 导入.F"& -此行加上我们要查找的地址
• MAX(-获得最高的行号，因为我们希望最后一个值在该范围内
• IF(-如果该值大于或等于，则返回行号，否则返回零
• INDIRECT(CONCATENATE("$ Import.$ F $"; Q3;:$ F $"; M3))> = S3 * VALUE(SUBSTITUTE($ S $ 1;"LOOKUP FACTOR x";"))-如果该值大于或等于，则返回true，否则返回false
• ; ROW(INDIRECT(CONCATENATE("$ Import.$ F $"; Q3;:$ F $"; M3))-获取我们当前正在查看的行.通过实现

  This formula is quite long to some extent, but to keep it short I have 2 sheets, one called Import and the other Lookup. I want to return the last value in a specific range (and match its row in another range) that is greater than or equal to the value in S3 multiplied by the LOOKUP FACTOR x, e.g. if is LOOKUP FACTOR x2, the value in S3 is multiplied by 2.

  I found that the way OpenOffice Calc accesses a range from another sheet is different to how Excel does, hence I rewrote the formula to:

  =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)))
  

  Hereby changing the sheetname from Import!$F$ to $Import.$F$. The same for column $B$.

  When I run this formula, OpenOffice returns the error #DIV/0!.

  So, if I chunk up the formula into each of its parts:

  • =INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))
  • =INDIRECT(CONCATENATE("$Import.$B$";Q3;":$B$";M3))
  • =(S3)*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";""))

  Whenever I execute them in separate cells, I get the same results in both spreadsheet programs, which means they do WORK individually. This makes me believe the issue is with the LOOKUP.

  In the above case, the reason I'm using INDIRECT is because both cells Q3 and M3 reference to the beginning and the end of a range:

  Q3 contains:

  {=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 contains about the same but using MAX:

  {=MAX(...)}

  These will return the index position where an array of array starts and ends. Say that A3 equals to Apple, then Q3 will return the row of Apple's first occurrence and Q3 will return the row of Apple's last occurrence in Column A.

  It's worth reiterating that ALL formulas work except the LOOKUP, meaning that the above cell references B3 and M3 return the correct index position (or row). It's also worth mentioning that the formula does work for Excel.

  Does anyone know why the LOOKUP formula does not work in OpenOffice?

  Can this be done elsewise without LOOKUP?

  解决方案

  No idea how it works in Excel, but in Calc, >= simply returns true if the value on the left is greater than or equal to the value on the right. So it sounds like we need to add an IF statement to do what you are asking.

  The following array formula finds the highest row where the condition returns true. Then it grabs the value from that cell.

  =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)))
  

  Breakdown:

  • INDIRECT( - grab the value from that cell
  • "Import.F"& - this plus the row makes the address we're looking for
  • MAX( - get the highest row number that works, because we want the last value in the range
  • IF( - if the value is greater or equal, then return the row number, otherwise return zero
  • INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3))>=S3*VALUE(SUBSTITUTE($S$1;"LOOKUP FACTOR x";"")) - returns true if the value is greater or equal, otherwise false
  • ;ROW(INDIRECT(CONCATENATE("$Import.$F$";Q3;":$F$";M3)) - get the row we are currently looking at. This could be shortened to CREATEARRAY(Q3;M3) by implementing a user-defined function that returns an array of row numbers.
  • ;0 - return row number as zero if not greater or equal

  这篇关于Excel查找公式在OpenOffice Calc中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！