excel公式技巧大全，excel公式法怎么使用

、IF多条件判断返回值

公式：C2

=IF(AND(A2500,B2=未到期),补款,)

说明：两个条件同时成立用AND,任一个成立用OR函数。



三、统计公式

1、统计两个表格重复的主要内容

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：假设返回值大于0说明在另一个表中存在，0则不存在。



2、统计不重复的总人员数量

公式：C2

=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))

说明:用COUNTIF统计出每人的产生次数，用1除的方法把产生次数变成分母，然后相加。



四、求和公式

1、隔列求和

公式：H3

=SUMIF($A$2:$G$2,H$2,A3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

说明：假设标题行没有规则用第2个公式



2、单条件求和

公式：F2

=SUMIF(A:A,E2,C:C)

说明：SUMIF函数的基本用法



3、单条件模糊求和

公式：详细内容查看下图

说明：假设需进行模糊求和，还要掌握并熟悉通配符的使用，这当中星号是表示任意多个字符，如*A*就表示a前和后有任意多个字符，即包含A。



4、多条件模糊求和

公式：C11

=SUMIFS(C2:C7,A2:A7,A11*,B2:B7,B11)

说明：在sumifs中可以使用通配符*

5、多表一样位置求和

公式：b2

=SUM(Sheet1:Sheet19!B2)

说明：在表中间删除或添加表后，公式结果会自动更新。



6、按日期和产品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)

说明：SUMPRODUCT可以完成多条件求和



五、查找与引用公式

1、单条件查找公式

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

说明：查找是VLOOKUP擅长的，基本用法



2、双向查找公式

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值



3、查找后一条满足条件的记录。

公式：详细内容查看下图

说明：0/(条件)可以把不满足条件的变成错误值，而lookup可以忽视错误值



4、多条件查找

公式:详细内容查看下图

说明:公式原理同上一个公式

5、指定区域后一个非空值查找

公式；详细内容查看下图

说明：略



6、按数字区域间取对应的值

公式：详细内容查看下图

公式说明：VLOOKUP和LOOKUP函数都可以按区间取值，一定要注意，销售量列的数字一定要升序排列。



六、字符串处理公式

1、多单元格字符串合并

公式：c2

=PHONETIC(A2:A7)

说明：Phonetic函数只可以对字符型内容合并，数字不可以。

2、截取除后3位之外的部分

公式：

=LEFT(D1,LEN(D1)-3)

说明：LEN计算出总长度,LEFT从左边截总长度-3个

3、截取-前的部分

公式:B2

=Left(A1,FIND(-,A1)-1)

说明：用FIND函数查找位置，用LEFT截取。

4、截取字符串中任一段的公式

公式:B1

=TRIM(MID(SUBSTITUTE($A1, ,REPT( ,20)),20,20))

说明:公式是利用强插N个空字符的方法进行截取

5、字符串查找

公式：B2

=IF(COUNT(FIND(河南,A2))=0,否,是)

说明: FIND查找成功，返回字符的位置，不然返回错误值，而COUNT可以统计出数字的个数，这里可以用来判断查找是不是成功。

6、字符串查找一对多

公式：B2

=IF(COUNT(FIND({辽宁,黑龙江,吉林},A2))=0,其他,东北)

说明：设置FIND第一个参数为常量数组，用COUNT函数统计FIND查找结果

七、日期计算公式

1、两日期相隔的年、月、天数计算

A1是启动日期（2023-12-1），B1是结束的后一天期(2023-6-10)。计算：

相隔多少天？=datedif(A1,B1,d) 结果：557

相隔多少月? =datedif(A1,B1,m) 结果：18

相隔多少年? =datedif(A1,B1,Y) 结果：1

不考虑年相隔多少月？=datedif(A1,B1,Ym) 结果：6

不考虑年相隔多少天？=datedif(A1,B1,YD) 结果：192

不考虑年月相隔多少天？=datedif(A1,B1,MD) 结果：9

datedif函数第3个参数说明：

Y 时间段中的整年数。

M 时间段中的整月数。

D 时间段中的天数。

MD 天数的差。忽视日期中的月和年。

YM 月数的差。忽视日期中的日和年。

YD 天数的差。忽视日期中的年。

2、扣除星期六和星期天天数的工作日天数

公式：C2

=NETWORKDAYS.INTL(IF(B2DATE(2023,1,1),DATE(2023,1,1),B2),DATE(2023,1,31),11)

说明：返回两个日期当中的全部工作日数，使用参数指示什么天是星期六和星期天，还有有多少天是星期六和星期天。星期六和星期天和任何指定为假期的日期不被默认为工作日

以算某中学成绩满分作为例子。

1、在Excel中计算公式一定是先用“=”号开头，既然如此那，先找到满分的第一格位置。

2、满分等于六课之和，既然如此那，在满分的第一格位置打出第一栏各个考试科目成绩所在位置的坐标=c7+d7+e7+f7+g7+h7。

3、按回车键，就可以得到我们第一栏的满分了。

4、当我们算出第一个结果后面，把鼠标放在第一个单元格的右下角。

5、使用自动填充的功能往下拉，完全就能够算出下面的满分。

在excel文档中，需使用excel循环引用公式，如何操作呢？下面给各位考生介绍详细的使用excel循环引用公式的方式

1、如图所示作为例子：在单元格在引用自己时，产生循环引用警告提示

2、点击左上角office功能按钮，然后弹出的界面点击右下角excel选项

3、弹出的框图点击“公式”，然后点击右边启用迭代计算，如图所示，对迭代进行设置，设置100作为例子

4、如图所示，设置完后，单元格进行了100次迭代

如何在excel使用公式中迅速选中区域？

方式/步骤

1

点击excel，然后打开选中的表格就可以。

2

选中一个空白单元格，然后输入等于号。

3

在公式当中迅速选中表格，一种方法是为了让用鼠标向下拖拽，这样方法针对哪些表格的比较适用。

4

将鼠标拖拽选中就可以。

5

第二种方法可以输入冒号，然后选择通过鼠标来进行输入就可以。

6

第三种方法是选中这当中一个单元格，然后键盘点击ctrl+shift+↓就可以，然后再次选中另一个结尾的单元格就可以将这两个单元格当中的表格选中。