多种常用的Excel函数公式

EXCEL的函数公式用得熟练，在实际工作中直接套用就能够得心应手了。

1、多条件判断

公式：=if(and（条件1，条件2，条件3……），满足所有条件返回值，不满足条件返回值）---------这是判断是否满足所有条件的判断。

     =if(or(条件1，条件2，条件3……），满足任一条件返回值，一个都不满足返回值）------------这是判断是否只满足任一条件的判断。

以下是满足多条件的判断：

这是满足任一条件后的判断：

2、多条件求和

公式：SUMPRODUCT（条件1*条件2*条件3*……实际求和区域）

这里也可以用sumifs函数来求和：=SUMIFS(G3:G22,D3:D22,'>=90',E3:E22,'>=90')

3、多条件查找

公式：=LOOKUP(1,0/((C:C='女')*(B:B='小琴')),G:G)

4、提取任意字符串里的数字

公式：=LOOKUP(9^9,MID(H24,MATCH(1,MID(H24,ROW($1:99),1)^0,0),ROW($1:9))*1)，这里，如果字符超过99，则把此处99改成更大的数就可以了。

实际使用时，请把H24换成你实际的带数字的字符串单元格名字就可以。

最后记得要三键同时按下：Ctrl Shift Enter。

5、分离提取字符

从左边提取用函数left，从右边提取用函数：right.

这是提取左边汉字：=LEFT(H31,LENB(H31)-LEN(H31))

这是提取右边汉字：=RIGHT(H32,LENB(H32)-LEN(H32))

这是提取右边字母：=RIGHT(H31,(LENB(H31)-(LENB(H31)-LEN(H31))*2))

这是提取左边字母：=LEFT(H32,(LEN(H32)-(LENB(H32)-LEN(H32))))

len：-返回字符串中字符的字符个数，每个文本就是一个字符个数。

lenb：返回文本字符中包含的字符数，每个汉字是双音节字，算2个字符。

6、多表同一位置的时候求和计算

求和的数据在不同的表格中的相同位置。如下图，1到11表格内的B31都是我们需要汇总的数据，现在要把每个表中的B31汇总求和在汇总表里。

公式：=（工作表1：工作表N！单元格地址）

    如下：=SUM(Sheet9:Sheet11!B31)

7、计算不重复值的个数

公式：=SUMPRODUCT(1/COUNTIF(区域,区域))

=SUMPRODUCT(1/COUNTIF(B3:B22,B3:B22))

是不是很好记！

8、给总分前三名者以高亮显示：如图示，选中A2:F15，点击开始---条件格式，选择新建规则----使用公式，在公式栏内输入：=$F2>=large($F$2:$F$15,3)，其中large($F$2:$F$15,3)是在F2:F15中第3个最大值。>=第3个最大值 就是前3名。这里F2必须在F前面加美元符号锁定。效果图如下：

9、给前三名者标示上红旗：如图示，在G2栏内输入“=F2”并下拉 复制得到与F2完全一样的一组数字。

然后选中G2:G15，点击开始---条件格式---新建规则---基于各自值选择所有单元格的格式。在下面“格式样式”里选择“图标”，在图标选项卡里的“当值是”选择“红旗”图标，下面两个选择“无单元格图标”。见下图：

继续以上操作：在“类型”选项卡第一个值里 选择“公式”，第二个值里选择“数字”。然后在“值”下面第一个空里 输入公式：=large($G$2:vG$15,3)，第二个空里输入“0”，并在“仅显示图标”左边的框里打上勾。如下图：

确定后返回，此时，前三名的所在列G就标上红旗啦，下图示：

10、给数字在所在单元格标示出数据条，有时候比如财会造工资表中工资多少通过数据条很直观。方法很简单：选中数据，选择开始---条件格式---数据条，选择自己喜欢的颜色即可。也可以选中数据后，在右下角的“快速分析”选择格式化--数据条或者直接按“Ctrl Q'弹出”快速分析选择。

11、给员工当月生日的高亮显示以提示生日临近：如下图示，选中生日所在行E2:E15，点击开始——条件格式——新建规则——使用公式----,在公式栏里输入：=text(today(),'m')=text($E2,'m')，在格式--填充里选择黄色，确定返回即可。（这里也可以用month函数，请自行去试试）today()表示现在日期与E2都表示日期，text是从这个日期中提取年月日，此处“M'表示提取月份。month是提取日期中月份的函数。

如果用高亮显示最近生日的三个员工，可先在F列建一辅助列，用公式计算出下一次生日距今天数，然后选中A2：F16，利用条件格式---新建规则----使用公式----公式栏输入：=$F2<=SMALL($F$2:$F$16,3)（这里F2前面一定加上美元符号锁定），点击格式选择填充为黄色，这里，small是取第K个最小值（升序排列），SMALL($F$2:$F$16,3)表示取第3个最小值，'<='即小于等于的意思，小于等于第3个最小值就是最小的三个数，确定后即可。朋友们可以自行试试这个。

利用条件格式可以用这样的设定条件：最近的生日用红色，第二近的用黄色，第三近的用紫色来表示。