☰

尝试制作一个方便用户的 Excel 使用图表

tag-icon • tag-icon microsoft-excel charts resource-usage
尝试制作一个方便用户的 Excel 使用图表

我是一家大型商业设施的固定工程师。最近，我们的公用事业账单突然激增，我的老板希望我改进我们的用电跟踪，以免再次出现意外。

我们有大约五十年前的夜间仪表读数（不，我没有深入研究那些非常古老的读数！）但没有人将它们放入电子表格或其他格式中，以便于日常跟踪。我坐下来，将最近一个月的数据输入到我创建的模板中，希望逐月复制它并持续跟踪使用情况。到目前为止一切顺利。

当某些读数被遗漏时，问题就出现了。这是一个很大的设施，午夜值班人员还有很多其他事情要做。因此，如果水读数被遗漏，我一天会显示四亿加仑的负消耗量，而第二天会显示四亿一万加仑的正峰值。我可以使用 MAX(0, C12-C11) 函数来处理前者，但我还没有找到处理后者的好方法。

是的，我可以手动编辑这样的内容，但目标是将其交给一些不太具备计算机知识的人使用。如果可能的话，我想做的是找到一种方法来对缺失的数据点进行直线平均，并通过不显示点标记来在图表中反映这一点。我仍然可以通过手动编辑来处理诸如仪表更换或翻转之类的事情，但缺失数据是一个足够常见的问题，我希望能够自动处理它。

我还应该提到，我对 Excel 不太熟悉；我花了很多时间使用 LibreOffice Calc 和 Quattro Pro。我似乎无法弄清楚请求者如何用折线图准确地完成我想要的操作。除了日常使用外，我还想在同一张图表上显示室外最高和最低温度以及建筑物占用率。如果能提供关于在哪里可以找到教程的建议，我将不胜感激。

我想发布当前的电子表格，但我找不到将文件附加到该问题的方法……因此也欢迎一些初步建议。

当前电子表格的图片：
选择显示数据表和数据透视表的电子表格

答案1

最大测试错过多个错误路径

最大限度忽略每个单元格中值的有效性，而是依赖于值之间的差异小于零的有限错误路径。

当您使用“MAX”测试 C12-C11 是否 > 0 时，结果为“0”永远，并且仅当'C12 NOT GREATER THAN C11'，无论 'C12' 和 'C11' 的值是正数还是负数。由于范围狭窄，它不适用于您的第二个用例以及其他用例：

----------------------------
Tests using 'MAX(0,C12-C11)'
----------------------------
C11=160,C12=189  
   • MAX(0,19)        # 19  (Succeeds)

C11=189,C12=0  
   • MAX(0,-189)      #  0  (Succeeds)

C11=0,C12=0      
   • MAX(0,0)         #  0  (Succeeds)

C11=0,C12=189
   • MAX(0,189)       #  189 (Fails, '0' wanted) 

C11=(-189),C12=181
   • MAX(0,370)       #  370 (Fails, '0' wanted, see note 1) 

C11=("oops"),C12=181
   • MAX(0,#VALUE!)   #  #VALUE! (Fails, '0' wanted, see note 2)

笔记：

  1. 除非差异 < 0，否则对于 <> 0 但超出合理范围的实数，您的测试将无法返回“0”。
  2. 当前测试将对非数字条目（例如数据集中的意外文本条目）报错 #VALUE!。这可能是文本条目、存储为文本“1849”的数字，或看似空白的单元格，其中隐藏了单引号 (') 等格式。在减法或任何其他计算中使用它时，您将生成错误，而不是什么都没有。

测试两个值的有效性

如果我们根据您的用例假设您只想在两个值都大于零的情况下执行计算“C12-C11”，那么测试将是‘AND(C11>0,C12>0)=true’在你的公式之前：

-----------------------------
Tests using 'AND(C11>0,C12>0'
-----------------------------

C11=160,C12=189  
   • AND(true,true)   # true

C11=189,C12=0  
   • AND(true,false)  # false

C11=0,C12=0      
   • AND(false,false) # false

C11=0,C12=189
   • AND(false,true)  # false

C11=(-189),C12=181
   • AND(false,true)  # false

文本值问题。

Excel 在比较不同数据类型时有自己的特点，这些特点会因所使用的公式而异。这会导致文本值漏掉并触发与 MAX 方法相同的错误（上文注释 2）。

针对此用例：

    # In Excel, '[text] > [number]' is 'true'
      • oops > 1,000,000 is true
      • ' > 0 is also 'true' 

    C11=("oops"),C12=181  
       • AND(true*,true)   # true (*C11 should be false)

   #  I suggest 2 ways to resolve this below (IFERROR vs ISNUMBER).

调整公式以包含测试

错误处理
因为：

[number (or text)] - [text] = #VALUE! error
and
[text] - [number (or text)] = #VALUE! error

我们可以通过对 C11 和 C12 分别进行 ISNUMBER 测试来解决这个问题，以确认它们是数字：

AND(ISNUMBER(C11),ISNUMBER(C12))

或者我们可以跳过另外两个测试，并使用 IFERROR 函数处理触发的错误：

IFERROR([formula],[value_if_error])

您做出的选择（以及其他可用的方法）将取决于您如何根据您的需要调整公式以及您使用的其他计算。

此外，如果 C12 和 C11 本身引用工作簿中其他地方的数据，您可以选择在流程早期执行数字检查/错误检查。

-----------------------------------------
Formulae based on test 'AND(C11>0,C12>0)'*
-----------------------------------------   

* Includes IFERROR handling or ISNUMBER check

If both C11 and C12 must be positive numbers: 
   • =IFERROR(IF(AND(C11>0,C12>0),C12-C11,0),0)
       or 
   • =IF(AND(C11>0,ISNUMBER(C11),C12>0,ISNUMBER(C12)),C12-C11,0)
       or 
   • =IF(AND(ISNUMBER(C11),ISNUMBER(C12)),
         IF(AND(C11>0,C12>0,
            C12-C11,
            0),
         "custom error msg")  

added C12 must always be greater than C11:
   • =IFERROR(IF(AND(C11>0,C12>0,C12>C11),C12-C11,0),0)
       or 
   • =IF(AND(C11>0,ISNUMBER(C11),C12>0,ISNUMBER(C12)),C12>C11),C12-C11,0)

added C12 must always be at least 10% bigger than C11:
   • =IFERROR(IF(AND(C11>0,C12>0,C12>C11/.9),C12-C11,0),0)
       or 
   • =IF(AND(C11>0,ISNUMBER(C11),C12>0,ISNUMBER(C12),C12>C11/.9),C12-C11,0)

and so on...

希望有帮助:-)

。
。
。
。

这也可能会引起人们的兴趣

当人们想要传递一个需要（或预期）是实数的值时，他们有时会故意触发错误处理。

如果不是实数，则触发错误

1/(1/[我的值]) （实数中不包括‘0’）

# value=(Number<>0)
   • 1/(1/[myValue]) = [myValue]  

# value=0 (or equivalent)
   • 1/(1/[myValue]) = #DIV/0!   (error)
       
# value=Non-Number (e.g. text)
   • 1/(1/[myValue]) = #VALUE!    (error)

[我的值]*1 （包括‘0’）

# value = (Number < > = 0)
   • [myValue] * 1 = [myValue]  
    
# value=Non-Number (e.g. text)
   • [myValue] * 1 = #VALUE!        (error)

这些测试可以与 IFERROR 结合以传递 [myValue] 或 [alternateValue]，并且可以嵌套在其他公式中：

=IFERROR(1/(1/[myValue]),[alternateValue]) 
=IFERROR([myValue]*1,[alternateValue])

相关内容