excel表格公式无效、不生效的解决方案及常见问题、常用函数

转载自  https://blog.youkuaiyun.com/cngkqy/article/details/96927198

1、表格公式无效、不生效

使用公式时碰到了一个问题，那就是公式明明已经编辑好了，但是在单元格里不生效，直接把公式显示出来了，网上资料说有4种原因，但是我4种都不是，是第5种原因，如下图：

这种情况是由于单元格格式不是常规导致的，首先右键单击单元格-->设置单元格格式，如下图：

然后把单元格格式修改为常规。然后双击该单元格，变成光标闪烁状态，敲回车，该公式就会生效了。

2、公式出错时如何排查：

首先单击要排查的公式的Cell，选择公式选项卡，点击公式求值

然后会弹出一个公式求值框，点击【求值】按钮，它会一步一步做转化运算，你可以观察值的变化，从而得知是那一步出了问题。对于复杂的容易出错的层层嵌套公式，这种排查方式非常有效，可快速定位错误

3、批量复制引用的公式，引用位置会自动修改，在这种情况下如果复制时不想让他自动变化，把相对位置修改为绝对位置就可以了，如下图：

在引用的表格地址前增加$符号就可以了

 

“相对引用”，指的是在单元格公式中，用列标和行号直接表示所引用单元格的地址。当把一个含有单元格地址的公式复制到新的单元格时，原单元格内公式引用的地址，在新的单元格内发生变化，但其引用的单元格地址之间的相对位置间距保持不变。“绝对引用”，就是单元格中的绝对单元格引用总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。 公式中使用“绝对引用”，在引用的单元格行号和列标前加上“$”符号 (注意，可分别在行号或列标前加$符号，达到固定某一列或行的功能) ，表示公式中所引用单元格的精确地址，与包含公式的单元格所在位置无关，加上“$”符号的单元格无论在那个单元格进行运算，引用的都是“$”符号后的单元格数据。 “相对引用”和“绝对引用”可以混合使用，即“混合引用”，单元格运算所引用的单元格数值随“相对引用”的行号或列标的改变而改变。 混合引用具有绝对列和相对行，或是绝对行和相对列，绝对引用列在所引用单元格地址的列标前加上$符号。绝对引用行在所引用单元格地址的行号前加上$符号。

 

4、日期加减操作，参考链接

加减天数：直接引用表格值，再加减数字即可，示例：

B1+2 当前日期+2天

 

加减月份：使用EDATE 函数，专门用于日期月份相加、相减，参考链接

EDATE(B1,2) 当前月份加2个月

EDATE(B1,-2) 当前月份减2个月

 

加减年份：使用YEAR、 MONTH、DAY函数

DATE(YEAR(B1)+1,MONTH(B1),DAY(B1)) 当前年份加1年

 

5、计算两个日期间的差，示例：参考链接

DATEDIF(B4,C4,"y")  相差的年数

DATEDIF(B4,C4,"m") 相差的月数

DATEDIF(B4,C4,"d") 相差的天数

 

日期函数有很多，以上列出一些常用的，链接里是总 日期和时间函数参考表

 

6、字符替换

使用SUBSTITUTE函数

SUBSTITUTE(E6,"哈哈","嘿嘿")  把E6的哈哈替换成嘿嘿

 

7、条件判断

IF(B6=0,B5,C5) 条件成立就取B5的值，否则就取C5的值

 

8、TEXT 函数用于格式各种数字、日期、时间等文本信息，参考链接