电脑知识：EXCEL公式汇总

Excel在我们生活工作当中常平，智通培训学校教大家一些公式技巧，希望能帮助大家
1 公式的组成要素:等号=、运算符、常量、单元格引用、函数、名称等
注： 1）公式以等号（=）引导
2）函数参数特性：参数以逗号隔开，且每个参数位置固定
3）函数嵌套，即函数作为另一函数的参数
4）函数不能做删除单元格或对其他单元格赋值操作

2 公式运算符：算术运算符、比较运算符、文本运算符、引用运算符
1）算术运算符：+、-、*、/、%、^
2）比较运算符：=、>、>=、<、<=、<>
3）文本运算符：&
4）区域运算符：冒号：，如：=SUM（A1：C5）
5）交叉运算符：单个空格，如：=SUM（A1:E5 B1:F5)，相当于SUM(B1:E5)
6）联合运算符：逗号,如：RANK(A1,(A1:A20.C1:C20))

3 公式运算符的顺序
1）冒号、单个空格、逗号（ :  , )
2）负号( - )
3）百分比( % )
4）乘幂( ^ )
5）乘除( * / )
6）加减( + - )
7）连接文本( & )
8）比较( = > >= < <= <> )

4 通配符(*?和~)
1）*表示任何字符，？表示任何单个字符，~表示解除字符通配性
例1,在A列中查找以张三开头,在B中对应的值,=Vlookup("张三*",A:B,2,0)
例2,统计商品型号为:5C*6的个数,=countif(A1:A10,"5C~*6")
2）当通配符直接用于比较运算时，则不具有通配性，如：=SUMPRODUCT(N(A1:A10="10M*5M))
例1,假设A1="ABCD",A2="AB?D",公式=A1=A2,返回FALSE,比较运算,通配符无通配性
例2,上例中,如果输入=Search(A1,A2),返回1,通配符具有通配性
3)支持通配符的函数有：Vlookup,Hlookup,Match,Sumif,Countif,Search,SearchB
4)不支持通配符的函数有：Find,FindB,Substitute

5 数据类型
1）主要类型有:文本、数值、日期和时间、逻辑值、错误值
2）公式中用一对半角双引号（""）所包含的内容表示文本
3）日期和时间，每一天用数值1表示，1小时=1/24，1分钟=1/24/60，1秒钟=1/24/60/60
4）逻辑值为两个值：TRUE和FALSE
5）错误值8种：NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,###

6 数据排序规则
1）升序：负数<0<正数<文本<FALSE<TRUE
2）错误值不参与排序
3）此规则仅用于排序，不比较其大小

7 文本型数字转换为数值
1）利用查错工具批量处理，
选择文本区>点击文本左则感叹号>弹出错误指示器>选择"转换为数字"
2）通过文本函数（如：Left、Mid,text）运算、文本合并符&合并计算、以及前置单引号输入数字后，结果都为
文本型
3）文本型数字参与运算的特性
文本数字运算特性（注：A1=1（常规），A2=2（文本型））
1、四则运算，+-*/ 数值 =A1+A2 返回3
2、数组文本 =COUNT({1,"2"}),返回1 ；COUNTA({1,"2"}),返回2
3、单元格引用文本 =SUM(A1:A2),返回1
4、直接作参数数值 =COUNT(1,"2"),返回2 ；SUM(1,"2"),返回3

4）文本型数字转换为数值的公式
序号公式说明
1 =A1*1 四则运算--乘*
2 =A1/1 四则运算--除/
3 =A1+0 四则运算--加+
4 =A1-0 四则运算--减-
5 =--A1 减负，负负得正较为gao效
6 =VALUE(A1) 函数转换

8 逻辑值与数值互换准则
一、在四则运算中，TRUE=1，FALSE=0
二、在逻辑判断中，0=FALSE，所有非0数值=TRUE
三、在比较运算中，数值<文本<FALSE<TRUE
例1：=TRUE>1，返回TRUE(准则3）；=TRUE-1>0,返回FALSE（准则1)
例2：求A1:A10中大于5的个数，公式：{=SUM(--(A1:A10>5))},也可以用{=SUM(N(A1:A10>5))}

9 用数值直接参与条件判断
在C1中输入=B1/A1，当A1=0时则会返回错误值#DIV/0！，为此可在C1中输入以下公式屏蔽错误值：=IF(A1=0,"",B1/A1)
根据逻辑值与数字互换准则2，0=FALSE,所以公式可简化为：=IF(A1,B1/A1,"")


10 运算符巧替逻辑函数
例1 给某产品检验，检验值大于50且小于等于80，则贴上正品标志，否则重新返修
公式：=IF(AND(A1>50,A1<=80),"正品"，返修")
根据准则1，TRUE*TRUE=1，FALSE*TRUE=0，及准则2，以上公式可改为：=if((A1>50)*(A1<=80),"正品","返修")
例2 根据性别判断退休年龄，男性60岁，女性55岁
公式：=IF(A1="男",60,55)，此公式可简化为：=(A1="男")*5+55

11 正确区分空文本与空单元格
1)空文本,指在单元格中输入:="",成对半角双引号,表示什么也没有,字符长度为0
2)空单元格,指未在单元格中输入任何数据或公式,或单元格被清空
A1为空单元格,B1为空文本
公式返回结果说明
=A1="" TRUE
=B1="" TRUE
=A1=0 TRUE
=B1=0 FALSE
=ISBLANK(A1) TRUE
=ISBLANK(B1) FALSE
注:当空文本作复制,选择性数值粘贴到其他单元格后,其结果值具有空文本特性

12 防止空单元误统计(空值与0值的区分)
在某些统计计算中,常常要将空值不统计在内但要统计0值,很多函数在统计时往往将空值视为0来计算
如:=SUMPRODUCT((A1:A20="红色")*(B1:B20))
当B1:B20中包含空值时也会一并统计进来了,修改公式如下:=SUMPRODUCT((A1:A20="红色")*(B1:B20<>"")*(B1:B20))

13 空单元格与空文本的合并妙用
当公式结果返回的是一个空单元格的引用时，EXCEL会返回数值0
用空单元格与空文本进行合并返回空文本的特性,屏蔽0值
在公式后加一个“&""”


14 引用样式
有两种样式:A1和R1C1
EXCEL2007由1048576行*16384列组成,即2^20行*2^14列
EXCEL2003由65536行*256列组成

15 切换单元格引用相对性
F4

16 工作表名与跨表引用的关系
当工作表名称中包括以下字符，则在引用时工作表名将被一对半角单引号包含：
1）数字开头
2）空格
3）以下非字母字符：$,%,,~,!,@,#,^,&,+,-,=,|,",;,{}

17   跨工作表、跨工作簿引用
1）跨工作表 =Sheet1!A1
2）跨工作簿（开启） =[Book1.xlsx]Sheet1!$A$1
3）跨工作簿（关闭） ='D:\桌面\[Book1.xlsx]Sheet1'!$A$1

18 如果引用工作表名不存在时，将返回错误值#REF！，即引用错误

19 工作表的命名规则：
1）字符串长度不得超过31
2）不得包含半角冒号：，斜杠/，问号？，通配符？*，方括号[]，反斜杠\，单引号‘

20 引用其他工作表相同单元格（通配符*的应用）
汇总1月至12月A1
方法1 ：=SUM('1月:12月'!A1)
方法2 ：=SUM('*'!A1) ,回车后公式自动转换为：=SUM('1月:12月'!A1)
以上两种方法的区别：
方法1表示求首表为1月，尾表为12月之间所有A1单元格数值之和
方法2表示，求除本身外，其他所有本工作簿表A1单元格之和
当汇总表处在6月和7月之间时，方法2的汇总公式将转化为：=SUM('1月:6月'!A1,'7月:12月'!A1)

21 引用相同字符数的工作表的单元格（通配符？应用）
分别汇总1-9月和10-12月的A1单元格
汇总1-9月A1公式：=SUM('??'!A1)，公式转换为：=SUM('1月:9月'!A1)
汇总10-12月A1公式：=SUM('??？'!A1)，公式转换为：=SUM('10月:12月'!A1)

注：EXCEL2003中，通配符？有单字节和双字节的区分；EXCEL2007则只表示单个字符数，不区分单字节和双字节

22 数字计算精度限制
1）允许键入的较大数值为：9.9999 9999 9999 99 E+307
2)计算精度 15位。例如身份证号码为18位：123456789012345678，识别为：123456789012345000

23    复制公式的5种方法
1）拖曳填充柄
2）双击填充柄
3）键填充，向右Ctrl+R, 向下Ctrl+D
4)选择性粘贴（公式）
5）多单元格同时输入（Ctrl+回车）

24 函数嵌套层数限制
EXCEL2007公式较多可以64层嵌套函数
EXCEL2003公式较多可以 7层嵌套函数

25    函数易失性
具有易失性的函数有:RAND、RandBetween、Today、Now、Cell、Info、Offset、Indirect等
大量地使用易失性函数，将因需要频繁重新计算而占用大量的系统资源，从而影响运行的速度，因此要尽量避免。
例：SUMIF第三个参数如果写成简写形式，就会表现出易失性，引发工作表重算。

26 函数的参数限制
在EXCEL2007中参数较多为255个
在EXCEL2003中参数较多为 30个
如：SUM、COUNT、COUNTA、AVERAGE、CHOOSE等

27 “表”在公式应用中的特点
此处的“表”在EXCEL2003中称为“列表”
创建：开始》套用表格格式》自定议》套用表格式》（输入“表数据的来源：”）》确定
结构化引用：
1）表名称例：=SUM（表1[金额]）中的“表1”
2）列标题例：=SUM（表1[金额]）中的“[金额]”
3）表字段，共有4项，即[#全部]、[#数据]、[#标题]、[#汇总]

28 公式的查错与监视
常见错误类型：NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,###
查错工具查找错误：
1）错误指示器选项；
2）公式审核工具

29 数组（array）概念及分类
概念：由一个或者多个元素按照行列排列方式组成的集合，
这些元素可以是文本、数值、逻辑值、日期、错误值等。
主要形式：
1）常量数组，如：{1,2;3,5;3,6}
2）区域数组，如：=SUMPRODUCT(A1:A10*B1:B10)
3）内存数组，如：=SMALL(A1:A10,{1,2,3})

30 多项计算原理
--将两组或多组数组参数的各项值分别进行计算的过程
统计大于0的单元格数据之和，=SUM((A1:A10>0)*A1:A10)

31   数组的维和尺寸
一维数组
二维数组
单元素数组


32 多单元格数组公式
--在多单元格中使用同一公式,并按照数组公式接下CSE结束的输入方式形成的公式.
特性:公式所在任何一个单元格都不能单独编辑,否则出现警告对话框("不能更改数组的某一部分")
要修改只能整体修改或全部清除
创建步骤:
1)选择输入公式区域
2)输入和编辑公式
3)CSE键
范围溢出显示#N/A错误

33 逻辑函数不能替代多重*、+计算
函数AND、OR仅返回单个TRUE、FALSE值，因此在数组公式中（*、+），不能用函数AND、OR替代。
例1：=SUM(AND(A1:A10>=60,A1:A10<=100)*1),CES键，返回不正确结果
例2：=SUM((A1:A10>=60)*(A1:A10<=100)),CES键，返回正确结果

34 多项*、+计算不能完全替代逻辑函数
在多项*、+计算过程中，逻辑值都将转换为数值
在一些区分0与FALSE的函数中，不能用多项*、+计算代替IF函数执行逻辑判断。如：MAX、MIN、SMALL、AVETAGE等
例1：求区间正数之和：
公式1：=SUM(IF(A1:A10>0,A1:A10)  CSE
公式2：=SUM(A1:A10>0)*A1:A10) CSE
这两个公式可互换
例2：求较小正数
公式1：=MIN(IF(A1:A10>0.A1:A10))  CSE,返回正确结果
公式2：=MIN((A1:A10>0)*A1:A10)  CSE，返回错误结果

35 数组之间直接运算的规则
1）单值或单元素数组与数组之间的直接运算
单值或单元素数组可以与另一个数组自由运算，返回与另一个数组相同尺坟的结果
序号公式结果说明
1 =3+{1;2;3;4} {4;5;6;7} 尺寸与{1;2;3;4}同
2 =COLUMN(A:A)*{1,2,3,4} {2,4,6,8} 尺寸与{1,2,3,4}同
3 =ROW(1:1)*{1,2;3,4} {2,4;6,8} 尺寸与{1,2;3,4}同
2）同方向一维数组之间的直接运算
两个同方向一维数组的运算进行相同位置元素一一对应的运算，因此要求两个数组具有相同的尺寸，否则
返回与较多元素数组相同尺寸的结果，但多出较少元素数组部分为#N/A错误。
例1：={1;2;3;4}>{2;1} CSE,返回：FALSE;TRUE;#N/A;#N/A
例2：={"*","日本","法国")&{"北京","东京"} CSE,返回：{*北京，日本东京，#N/A}
例3 凡代码为1，2，3和06开头的订单免配送费，其征收5%的运费，公式：
=IF(OR(LEFT(A1,{1,1,1,2})={"1","2","3","06"}),0,5%)
3）不同方向一维数组之间的直接运算
两个不同方向一维数组即M行垂直数组与N列水平数组进行运算时，数组中的每一个元素分别与另一个数组的
每一个元素进行运算，返回M*N二维数组。
公式:={1;2;3;4}*{1,2,3}
结果:{1,2,3;2,4,6;3,6,9;4,8,12}
4）一维数组与二维数组之间的直接运算
当一维数组与二维数组具有同向相同尺寸特征时,即M行数组或者N列数组与M行N列数组,进行该方向的一一
对应运算并返回M*N二维数组,否则在一维数组方向上差异部分整行或整列返回#N/A错误.
例1 公式:={1;2;3;4}*{1,2;1,2;1,2}
结果:{1,2;2,4;3,6;#N/A,#N/A}
例2 公式:={1,2,3}*{1,2;1,2;1,2}
结果:{1,4,#N/A;1,4,#N/A;1,4,#N/A},第三列出错
例3 公式:{1;2;3}*{1,2;1,2;1,2}
结果:{1,2;2,4;3,6},行对应相乘,无错误值
5）二维数组之间的直接运算
两个二维数组直接运算,可以将其视为同一个原点的两个单元格区域进行叠加,其重叠部分的元素进行一一
对应运算,非交叉区域则全部返回#N/A错误值.即:M*N
公式:={1,2;3,4;5,6;7,8}*{1,3,5,7;2,4,6,8}
结果:{1,6,#N/A,#N/A;6,16,#N/A,#N/A;#N/A,#N/A,#N/A,#N/A;#N/A,#N/A,#N/A,#N/A}
1 2 1 3 5 7 1 6 #N/A #N/A
3 4 2 4 6 8 6 16 #N/A #N/A
5 6 #N/A #N/A #N/A #N/A

7 8 #N/A #N/A #N/A #N/A