今天学到了两个Excel技巧：让图表忽略不想要的数值，让错误值保留但不显示出来。
或者，用Excel的语言来说，是这样两个操作目的：
1.使以时间轴为基础的图表中不绘画指定数值的数据点图形； 2.通过条件格式功能赋予公式计算结果为错误值的单元格特定格式。 或许有人会马上说：“不想要的值，不就删掉了就行了么？”“不显示不就是将其文字变成和背景同色就行了么？”这些回答也没错，但是那是手工操作的笨办法，如果总是要处理这样的情况，处理的表格也很多很繁的话，这样的答案就没有价值了。 或者更多人未能理解这是什么目的，不如我就用以下的例子说明吧。  如上图，我有“苹果”和“雪梨”两种水果的销量表和价钱表，将两者对应乘起来，得到第三个表就是两种水果的销售额月度分布表，底下就是对销售额月度走势图。 因为“雪梨”4月和5月没有销售，所以销售额为“0”，看起来没问题啊，但是如果把曲线平滑的话：  看出来了吧？“雪梨”的销售额居然在这两个月变成负的了！这怎么可能啊！ 因为Excel对公式的计算法则是，“空值”被合法计算之后，得出的结果就是“零”，于是若表格中某些时间点没数据的话，相应的计算结果就会显示为“零”。当我们想让图形看起来连续，或者说表现出数据的走势，但又为了保证时间轴的连续而不能去掉出现这种“零”的时间点时，应该怎么办才好呢？ “不想要的值，不就删掉了就行了么？”——不行，因为这是公式计算出来的，如果代入新的数据又要重新填写公式，这不折腾么？再者，如果很多个表都这样，手工处理不是太麻烦了么？ 当时我就查啊查，终于查到高人说的一句“用NA()就可以了”。高人就是高人，根本不用解释怎么用为什么用；我于是只能试着用，结果发现将公式改成这样就可以了： 【=if(原公式=0,NA(),原公式)】 意思就是如果公式算出来是“零”，就输出错误值（存在就是有用的对NA()这个函数来说就是其存在的理由），否则就输出公式的值。对例子中第三个表处理出来后：  是不是一下子豁然开朗？曲线平滑了，3月的数据点之后直接跳过了4、5月，到6月的数据点，不但不会变负，还清晰显示了数据缺失的4、5月应有的走势。 目的1，达成！ ｛扩展｝：对公式【=if(原公式=0,NA(),原公式)】中的“0”按需要进行更改，就可以使图表忽略各种不想出现在图表中的值，例如改成“<5”就将所有数值小于5（可以理解为低得可以忽略）的点都忽略掉，例如改成“>AVERAGE($B$14:$M$14)*3”就将所有比均值3倍还大的“超标”点忽略掉。 好，图表没问题了，但是怎么那数据表，也就是第三个表看起来这么不爽呢？说对了，就是中间有让人很不舒服的“#N/A”字样啊。按公式的值的话图表会乖乖的出来个“0”的点，使用其他值也一样；而使用错误值的话没点了但数据表又不好看，怎么办呢？ “不显示不就是将其文字变成和背景同色就行了么？”手动改啊？可电脑我们都用上了，居然还在电脑上进行劳力密集型操作，这样对得起发明电脑的众多远见卓识的高人吗？？ 要“看不见”但不影响“它在那”的话，自然要更改其格式；要避免变成劳力密集型操作那自然就要祭出“条件格式”这样的魔幻技术了（说过头了……）！ 按最基本的思路，那应该是这样的：  结果当然是没有效果的！！！ 怎么办？看见前面“单元格数值”的下拉列表框了不？下拉，改成“公式”，然后在右边输入：【=ISERROR(B13)】（“B13”为第一个单元格的坐标），确定（别忘了改“条件格式”的格式设置哦~），搞掂！  目的2，达成！ ｛扩展｝：“条件格式”中的“公式”功能可以说是十分强大的。例如输入公式【=ABS(B13)>2】就是讲单元格中绝对值大于2的赋予设定格式，【=MOD(ROW(B13),2)】跟【=MOD(ROW(B13),2)-1】轻松地实现了隔行设定格式，前者偶数行，后者奇数行。更多创意应用有待大家发掘哦！ |
|
|
来自： behindtdark > 《Excel表格》
