被Excel公式“虐哭”？7个技巧让你秒变高手！

今天咱们分享几个常用公式。

一、计算日期所在季度

如下图所示，需要根据A列日期计算出该日期所在季度。
B2单元格输入以下公式，下拉：
=LEN(2^MONTH(A2))

这个公式里，MONTH函数是计算月份的，LEN函数是计算字符长度的。
用不同月份作为2的乘幂，然后用LEN函数计算出乘幂的字符长度，正好与所在季度对应。

 

二、判断今年是不是闰年

A2单元格输入以下公式：
=IF(COUNT(“2-29″),”是”,”否”)

“2-29″，得到到系统当前年份的2月29日（如果有这个日期），否则得到一个文本。
最后使用COUNT函数计算数值个数，如果COUNT函数的统计结果是1，说明是闰年，否则就不是闰年了。

 

三、判断差异变化

如下图所示，要根据B~C列两年的数据，判断变化差异。
D2单元格输入以下公式：
=TEXT(C2-B2,”比上年多0元;比上年少0元;与上年相同”)

TEXT函数是一个文本函数，作用是将数字变成指定格式的文本。
本例中的格式代码为”比上年多0元;比上年少0元;与上年相同”，
格式代码中的0表示第一参数本身。
如果C2-B2的结果大于0，就显示“比上年多n元”。
如果C2-B2的结果小于0，就显示“比上年少n元”。
如果C2-B2的结果等于0，就显示“与上年相同”。

 

四、快速提取字符

如下图所示，要根据A列的号码，提取减号前的区号。

C2单元格输入以下公式，向下复制：
=TEXTBEFORE(B2,”-“)
TEXTBEFORE函数用于返回指定间隔符号前的字符。
如需提取不包含区号的号码，可以使用以下公式：
=TEXTAFTER(B2,”-“)
TEXTAFTER函数用于返回指定间隔符号后的字符。

 

五、提取姓名

如下图所示，使用以下公式可以提取出A列混合内容中的姓名。
=LEFT(B2,LENB(B2)-LEN(B2))

LEN函数计算出B2单元格的字符数，将每个字符计算为1。

LENB函数计算出B2单元格的字节数，将字符串中的双字节字符（如中文汉字）计算为2，单字节字符（如数字、半角字母）计算为1。

用LENB计算结果减去LEN计算结果，就是字符串中的双字节字符个数。

最后用LEFT函数从B2单元格左侧按指定位数取值。

 

六、填充内容

如下图所示，要根据B列的户主关系，在C列填充该户的户主姓名。

C2输入以下公式：
=IF(B2=”户主”,A2,C1)

公式的意思是：如果B2等于“户主”，就返回A列的姓名，否则返回公式所在单元格的上一个单元格里的内容。当公式下拉时，前面的公式结果会被后面的公式再次使用。

 

七、查询产品类别

如下图所示，A列是产品名称，D列是对照表。

如果产品名称中包含对照表中的关键字，就显示对照表中的内容。

B2单元格输入以下公式，向下复制。
=LOOKUP(1,-FIND(D$2:D$7,A2),D$2:D$7)

公式中的“FIND(D$2:D$7,A2)”部分：

首先用FIND函数，以D$2:D$7单元格中的类别关键字作为查询，在A2单元格中分别查询这些字符出现的位置，得到一个由错误值和数值组成的内存数组。

加上负号后，内存数组中的数值变成负数，错误值部分的结果不变。

接下来使用1作为查询值，在内存数组中进行查找，由于找不到具体的查找值，同时LOOKUP认为数组中最后一个数值一定是所有数值中最大的，因此以最后一个负数与之匹配，并返回第三参数中同一位置的元素。