使用JXL公式（如SUM（）AVG（））时出现问题STDEV（）返回#VALUE！当它从另一张表中引用值时 [英] Problem when using JXL Formula like SUM() AVG() STDEV() returns #VALUE! when it refers values from another sheet

问题描述

我想在表格中填充一些值，然后使用jxl公式从该表中获取值并将其写入另一个工作表...

当我尝试运行此示例代码

  
 String filename =C：\\input.xls; 
 WorkbookSettings ws = new WorkbookSettings（）; 
 ws.setLocale（new Locale（en，EN））; 
 WritableWorkbook工作簿= 
 Workbook.createWorkbook（新文件（filename），ws）; 
 WritableSheet s = workbook.createSheet（Input，0）; 
 WritableSheet s1 = workbook.createSheet（Output，1）; 
 s1.addCell（new Number（3，0，5））; 
 s1.addCell（new Number（3，1，6））; 
 s1.addCell（new Number（3，2，1））; 
 s1.addCell（new Number（3，3，6））; 
 s1.addCell（new Number（3，4，1））; 
公式公式=新公式（3,5，AVERAGE（Output！D1：Output！D5））; 
 s.addCell（formula）; 
 
  

我得到AVG列表中的最后一个值有输出

= AVERAGE（Output！D5）

JAR已使用：jxl 1.0.jar ...

解决方案尝试：

1）而不是给它一个公式我给它有标签

标签标签=新标签
（3,5，AVERAGE（输出！ ！D5））;

我得到了单元格中的整个文本，并在单元格之前给出了'='一个魅力但是我希望这样做可以用JXL API完成

2）将JAR更改为jxl 2.6.jar

现在，当我尝试运行相同的时候，我正在获得 #VALUE！。单元格内容是

= AVERAGE（输出！D1：输出！D5），但是我仍然得到#VALUE！ b
$ b

JAR已使用：jxl 2.6.jar

此错误仅在我去该单元格，按Tab键或F2键。

请提供一些解决方案

注意

NSBalaji

解决方案

看起来excel是以奇怪的方式评估公式，因此您为什么看到 #VALUE！。要了解为什么公式失败，请单击公式单元格，然后转到工具>公式审核>评估公式。您将看到以下步骤：

  AVERAGE（输出！D1：输出！D5）
 = AVERAGE ：输出！D5）
 = AVERAGE（5：1）
 = AVERAGE（#VALUE！）
 =＃VALUE！ 
  

但是当您执行 F2 + Enter 该单元格，您将看到Excel更改其执行计划并获得正确的答案。

我恐怕唯一可以想到的解决方法是使用一个逗号分隔的列表，而不是范围：

 公式公式=新公式（3,5，AVERAGE（输出！ !!! D1，D2输出，输出D3，D4输出，输出D5））！; 
  

I want to populate some values in a sheet and then use jxl Formula to get the values from that sheet and write it to another sheet...

When I try to run this sample code

  
String filename = "C:\\input.xls";
      WorkbookSettings ws = new WorkbookSettings();
      ws.setLocale(new Locale("en", "EN"));
      WritableWorkbook workbook = 
      Workbook.createWorkbook(new File(filename), ws);
      WritableSheet s = workbook.createSheet("Input", 0);
      WritableSheet s1 = workbook.createSheet("Output", 1);
      s1.addCell(new Number(3, 0, 5));
      s1.addCell(new Number(3, 1, 6));
      s1.addCell(new Number(3, 2, 1));
      s1.addCell(new Number(3, 3, 6));
      s1.addCell(new Number(3, 4, 1));
      Formula formula = new Formula(3,5,"AVERAGE(Output!D1:Output!D5)");
      s.addCell(formula);

 

I am getting the last value in the AVG list has the output

=AVERAGE(Output!D5)

JAR Used : jxl 1.0.jar....

Solution tried :

1) Instead of giving it has a formula I gave it has label

Label label = new Label (3,5,"AVERAGE(Output!D1:Output!D5)");

I got the entire text in the cell and gave '=' before the cell.. It worked like a charm. But I want this to be done with JXL API

2) Changed the JAR to jxl 2.6.jar

Now I am getting #VALUE! when I try to run the same. The cell content is

=AVERAGE(Output!D1:Output!D5) but still I get #VALUE!.

JAR Used : jxl 2.6.jar

This error is getting solved only when I go to that cell and press tab key or F2 key.

Please provide some solution

Regards

N.S.Balaji

解决方案

It looks like excel is evaluating the formula in a strange way, hence why you are seeing #VALUE!. To see why the formula is failing, click on the formula cell and then go to Tools > Formula Auditing > Evaluate Formula. You will see that the steps are:

AVERAGE(Output!D1:Output!D5)
=AVERAGE(5:Output!D5)
=AVERAGE(5:1)
=AVERAGE(#VALUE!)
=#VALUE!

But when you execute F2+Enter on the cell, you will see that Excel changes its execution plan and gets the right answer.

I'm afraid the only thing I can think of to fix this is to use a comma-separated list, instead of a range:

Formula formula = new Formula(3,5, "AVERAGE(Output!D1,Output!D2,Output!D3,Output!D4,Output!D5)");

这篇关于使用JXL公式（如SUM（）AVG（））时出现问题STDEV（）返回#VALUE！当它从另一张表中引用值时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！