Excel公式大全：从入门到高阶应用.pdfVIP

Excel公式大全：从入门到高阶应用

一、公式基础与运算符

1.公式组成

Excel公式以等号=开头，包含常量、单元格引用、函数和运算符。例如：

=(A1+B1)/2。

1.运算符类型：

算术运算符：（加）、（减）、（乘）、（除）

+-*/

比较运算符：（等于）、（大于）、（小于）

=

文本运算符：（连接文本，如）

A1元

引用运算符：（区域引用，如）。

:A1:C3

1.单元格引用方式

o相对引用：A1（随公式位置变化）

o绝对引用：$A$1（固定引用）

o混合引用：A$1或$A1（部分固定）。

二、常用函数公式分类详解

1.数学计算

绝对值：=ABS(数字)

取整：=INT(数字)

四舍五入：=ROUND(数字,小数位数)

乘积求和：=SUMPRODUCT(数组1,数组2)（支持多条件计算）。

2.条件判断与统计

单条件求和：=SUMIF(条件区域,条件,求和区域)

示例：=SUMIF(A:A,销售部,C:C)统计销售部业绩。

多条件求和：=SUMIFS(求和区域,条件区域1,条件1,条件区域2,条件2)

示例：=SUMIFS(C:C,B:B,500,D:D,未到期)。

中国式排名：

=SUMPRODUCT(($D$4:$D$9=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))。

3.查找与引用

垂直查找：=VLOOKUP(查找值,表格区域,列号,FALSE)（精确匹配）。

双向查找：=INDEX(数据区域,MATCH(行条件,行区域,0),MATCH(列条件,列区域,0))。

动态引用：=OFFSET(起点,行偏移量,列偏移量,高度,宽度)（适用于动态范围）。

4.文本处理

提取出生日期（18位身份证）：

=CONCATENATE(MID(E2,7,4),/,MID(E2,11,2),/,MID(E2,13,2))。

金额转大写：

=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),[dbnum2]G/通用格式元

)TEXT(RIGHT(RMB(A2),2),[dbnum2]0角0分)。

5.日期与时间

计算年龄：=TRUNC((DAYS360(出生日期,TODAY()))/360,0)。

按月份汇总：

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

三、高阶技巧与实用场景

1.多表协同计算

多表相同位置求和：=SUM(Sheet1:Sheet19!B2)（自动更新表增减）。

跨工作簿引用：=[工作簿名.xlsx]Sheet1!A1（需确保文件路径正确）。

2.数据验证与清洗

标识重复值：=IF(COUNTIF(A:A,A2)1,重复,)。

限制输入长度：

数据验证→自定义公式：=LEN(A1)=10（限制A1为10字符内）。

3.统计与分析

频率分布：=FREQUENCY(数据区域,分段点数组)

示例：=FREQUENCY(D2:D8,{30,40})统计30-40岁人数。

不重复计数：=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))。

四、