搜索
1公式的组成要素:等号=、运算符、常量、单元格引用、函数、名称等注：1）公式以等号（=）引导2）函数参数：参数以逗号隔开，且每个参数位置固定3）函数嵌套，即函数作为另一函数的参数4）函数不能做删除单元格或对其他单元格赋值操作2公式运算符：算术运算符、比较运算符、文本运算符、引用运算符1）算术运算符：+、-、*、/、%、^如：A1+B12）比较运算符：=、、=、、=、如：A1=B13）文本运算符：&如：A1&B14）区域运算符：冒号：如：=SUM（A1：C5）5）交叉运算符：单个空格，如：=SUM（A1:E5B1:F5)，相当于SUM(B1:E5)6）联合运算符：逗号,如：RANK(A1,(A1:A20,C1:C20))4公式运算符的顺序1）冒号、单个空格、逗号（:,)2）负号(-)3）百分比(%)4）乘幂(^)5）乘除(*/)6）加减(+-)7）连接文本(&)8）比较(===)5通配符(*?和~)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,SearchB4)不支持通配符的函数有：Find,FindB,Substitute6数据类型1）主要类型有:文本、数值、日期和时间、逻辑值、错误值2）公式中用一对半角双引号（）所包含的内容表示文本一、EXCEL公式与函数基础第1页，共46页3）日期和时间，每一天用数值1表示，1小时=1/24，1分钟=1/24/60，1秒钟=1/24/60/604）逻辑值为两个值：TRUE和FALSE5）错误值8种：NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,###7数据排序规则1）升序：负数0正数文本FALSETRUE2）错误值不参与排序3）此规则仅用于排序，不比较其大小8文本型数字转换为数值1）利用查错工具批量处理，选择文本区点击文本左则感叹号弹出错误指示器选择转换为数字100110021003100410051006100710081009101010011002100310041005100610071008100910102）通过文本函数（如：Left、Mid,text）运算、文本合并符&合并计算、以及前置单引号输入数字后，结果都为文本型3）文本型数字参与运算的特性文本数字运算特性（注：A1=1（常规），A2=2（文本型））1、四则运算，+-*/数值=A1+A2返回32、数组文本=COUNT({1,2}),返回1；COUNTA({1,2}),返回23、单元格引用文本=SUM(A1:A2),返回14、直接作参数数值=COUNT(1,2),返回2；SUM(1,2),返回34）文本型数字转换为数值的公式序号1=A1*1四则运算--乘*2=A1/1四则运算--除/3=A1+0四则运算--加+4=A1-0四则运算--减-5=--A1减负，负负得正较为高效6=VALUE(A1)函数转换9逻辑值与数值互换准则一、在四则运算中，TRUE=1，FALSE=0二、在逻辑判断中，0=FALSE，所有非0数值=TRUE三、在比较运算中，数值文本FALSETRUE例1：=TRUE1，返回TRUE(准则3）；=TRUE-10,返回FALSE（准则1)例2：求A1:A10中大于5的个数，公式：{=SUM(--(A1:A105))},也可以用{=SUM(N(A1:A105))}公式说明第2页，共46页10用数值直接参与条件判断在C1中输入=B1/A1，当A1=0时则会返回错误值#DIV/0！，为此可在C1中输入以下公式屏蔽错误值：=IF(A1=0,,B1/A1)根据逻辑值与数字互换准则2，0=FALSE,所以公式可简化为：=IF(A1,B1/A1,)11运算符巧替逻辑函数例1给某产品检验，检验值大于50且小于等于80，则贴上正品标志，否则重新返修公式：=IF(AND(A150,A1=80),正品，返修)根据准则1，TRUE*TRUE=1，FALSE*TRUE=0，及准则2，以上公式可改为：=if((A150)*(A1=80),正品,返修)例2根据性别判断退休年龄，男性60岁，女性55岁公式：=IF(A1=男,60,55)，此公式可简化为：=(A1=男)*5+5512正确区分空文本与空单元格1)空文本,指在单元格中输入:=,成对半角双引号,表示什么也没有,字符长度为02)空单元格,指未在单元格中输入任何数据或公式,或单元格被清空A1为空单元格,B1为空文本=A1=TRUE=B1=TRUE=A1=0TRUE=B1=0FALSE=ISBLANK(A1)TRUE=ISBLANK(B1)FALSE注:当空文本作复制,选择性数值粘贴到其他单元格后,其结果值具有空文本特性13防止空单元误统计(空值与0值的区分)在某些统计计算中,常常要将空值不统计在内但要统计0值,很多函数在统计时往往将空值视为0来计算如:=SUMPRODUCT((A1:A20=红色)*(B1:B20))当B1:B20中包含空值时也会一并统计进来了,修改公式如下:=SUMPRODUCT((A1:A20=红色)*(B1:B20)*(B1:B20))14空单元格与空文本的合并妙用当公式结果返回的是一个空单元格的引用时，EXCEL会返回数值0用空单元格与空文本进行合并返回空文本的特性,屏蔽0值在公式后加一个“&”15利用选项设置来优化公式和多项功能实现返回结果公式说明第3页，共46页16引用样式有两种样式:A1和R1C1EXCEL2007由1048576行*16384列组成,即2^20行*2^14列EXCEL2003由65536行*256列组成17快速切换单元格引用相对性18跨工作表、跨工作簿引用1）跨工作表=Sheet1!A12）跨工作簿（开启）=[Book1.xlsx]Sheet1!$A$13）跨工作簿（关闭）='D:\桌面\[Book1.xlsx]Sheet1'!$A$119工作表名与跨表引用的关系当工作表名称中包括以下字符，则在引用时工作表名将被一对半角单引号包含：1）数字开头2）空格3）以下非字母字符：$,%,,~,!,@,#,^,&,+,-,=,|,,;,{}20如果引用工作表名不存在时，将返回错误值#REF！，即引用错误21工作表的命名规则：1）字符串长度不得超过312）不得包含半角冒号：，斜杠/，问号？，通配符？*，方括号[]，反斜杠\，单引号‘22快速引用其他工作表相同单元格（通配符*的应用）F4$A$1A$1$A1A1F4R1C1R1C[-1]R[-2]C1R[-2]C[-1]第4页，共46页汇总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)23快速引用相同字符数的工作表的单元格（通配符？应用）分别汇总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则只表示单个字符数，不区分单字节和双字节24快速复制公式的5种方法1）拖曳填充柄2）双击填充柄3）快速键填充，向右Ctrl+R,向下Ctrl+D4)选择性粘贴（公式）5）多单元格同时输入（Ctrl+回车）25数字计算精度限制1）允许键入的最大数值为：9.99999999999999E+3072)计算精度15位。例如身份证号码为18位：123456789012345678，识别为：12345678901234500026函数嵌套层数限制EXCEL2007公式最多可以64层嵌套函数EXCEL2003公式最多可以7层嵌套函数27函数的参数限制在EXCEL2007中参数最多为255个在EXCEL2003中参数最多为30个如：SUM、COUNT、COUNTA、AVERAGE、CHOOSE等28函数易失性具有易失性的函数有:RAND、RandBetween、Today、Now、Cell、Info、Offset、Indirect等大量地使用易失性函数，将因需要频繁重新计算而占用大量的系统资源，从而影响运行的速度，因此要尽量避免。第5页，共46页例：SUMIF第三个参数如果写成简写形式，就会表现出易失性，引发工作表重算。29“表”在公式应用中的特点此处的“表”在EXCEL2003中称为“列表”创建：开始》套用表格格式》自定议》套用表格式》（输入“表数据的来源：”）》确定结构化引用：1）表名称例：=SUM（表1[金额]）中的“表1”2）列标题例：=SUM（表1[金额]）中的“[金额]”3）表字段，共有4项，即[#全部]、[#数据]、[#标题]、[#汇总]30公式的查错与监视常见错误类型：NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,###查错工具快速查找错误：1）错误指示器选项；2）公式审核工具31数组（array）概念及分类概念：由一个或者多个元素按照行列排列方式组成的集合，这些元素可以是文本、数值、逻辑值、日期、错误值等。主要形式：1）常量数组，如：{1,2;3,5;3,6}2）区域数组，如：=SUMPRODUCT(A1:A10*B1:B10)3）内存数组，如：=SMALL(A1:A10,{1,2,3})32数组的维和尺寸一维数组二维数组单元素数组33多项计算原理--将两组或多组数组参数的各项值分别进行计算的过程统计大于0的单元格数据之和，=SUM((A1:A100)*A1:A10)34多单元格数组公式--在多单元格中使用同一公式,并按照数组公式接下CSE结束的输入方式形成的公式.特性:公式所在任何一个单元格都不能单独编辑,否则出现警告对话框(不能更改数组的某一部分)要修改只能整体修改或全部清除第6页，共46页创建步骤:1)选择输入公式区域2)输入和编辑公式3)CSE键范围溢出显示#N/A错误35逻辑函数不能替代多重*、+计算函数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键，返回正确结果36多项*、+计算不能完全替代逻辑函数在多项*、+计算过程中，逻辑值都将转换为数值在一些区分0与FALSE的函数中，不能用多项*、+计算代替IF函数执行逻辑判断。如：MAX、MIN、SMALL、AVETAGE等例1：求区间正数之和：公式1：=SUM(IF(A1:A100,A1:A10)CSE公式2：=SUM(A1:A100)*A1:A10)CSE这两个公式可互换例2：求最小正数公式1：=MIN(IF(A1:A100.A1:A10))CSE,返回正确结果公式2：=