搜索
基础篇：第1到第5章应用篇：第6到第10章分析篇：第11到第16章MicrosoftExcel从入门到精通第六章创建和使用公式第一节公式的基本概念公式是指对工作表数据进行运算的算式，利用公式可以进行数学运算，可以比较工作表数据并返回布尔值等公式通常由以下五种元素组成：运算符、单元格引用、文字（数字、字符串等）、工作表函数、括号公式以等号=开始，等号后面是要进行计算的操作数、运算符、函数等公式按照运算符特定顺序从左至右进行运算，括号（）可改变运算顺序，先运算括号内，再运算括号外应用篇第二节公式中使用的运算符算术运算符+加、-减、*乘、/除、%百分号、^乘方比较操作符=、、、=大于等于、=小于等于、不等于文本串联符&文字串联符，把两个字符串首尾连接引用操作符：冒号--引用两个地址间的所有单元格，如B5：C6，包含B5、B6、C5、C6四个单元格，逗号--引用逗号前后的两个单元格或单元格区域，如A1：B2，B4：C5，包含A1、A2、B1、B2和B4、B5、C4、C5公式中符号运算优先次序:、,-%^*、/+、-&=、、、=、=、即：引用负号百分号乘方乘、除加、减文本连接符比较运算符第三节输入公式直接在单元格中输入公式单击要输入公式单元格输入等号=输入公式内容，回车确认使用“公式选项板”输入公式单击编辑栏上“编辑公式”按钮，直接输入公式，或者再单击所需函数，出现“公式选项板”公式中单元格的输入选重要输入公式单元格，输入等号=用鼠标单击要引用单元格，输入单元格地址输入运算符继续上述操作，若输入完毕，按回车键或单击编辑栏上“确认”按钮结束，也可按Esc或编辑栏上“取消”按钮取消输入第四节编辑公式编辑公式就向编辑其他单元格一样双击单元格可在单元格内部直接编辑公式选择要编辑的单元格，再单击“编辑栏”编辑公式单击“编辑栏”中的“编辑公式”按钮，再使用“公式选项板”编辑公式第五节单元格和区域引用一个引用地址代表工作表上的一个或一组单元格引用地址告诉Excel在哪些单元格中查找公式中要用的数值通过使用引用地址，可以在一个公式中使用工作表不同部分的数据，也可以在几个公式公式用同一单元格数值；同时还可以引用同一工作簿不同工作表的数据，或者引用其他工作簿的数据相对引用在输入和创建公式的过程中，若非特别指明，通常采用单元格的相对位置相对地址是当公式复制到新位置或用一个公式增加一个新的范围时公式中单元格地址会改变在复制包含相对引得公式时，Excel会自动调整复制公式中的引用，以引用相对于当前公式位置的其他单元格（如：A1、B5、A2：C3等）绝对引用一般情况下，复制单元格地址时使用使用相对地址，但我们如果不希望复制公式时系统自动调整引用，就要使用绝对地址引用绝对地址引用是指在复制公式或把公式填入到新位置时，公式的固定单元格地址不变Excel中通过在行号列号前增加$符号表示绝对引用（如：$A$4、$B$7等）混合地址引用相对地址和绝对地址可以混合使用如：$A1、A$8等三维地址引用所谓三维地址引用是指：在同一工作簿中引用不同工作表中单元格或单元格区域中的数据三维地址引用的一般格式为：工作表名称！单元格地址如：Sheet3！B3+C4即Sheet3工作表中B3单元格的值和当前工作表中C4单元格的值相加注意避免循环引用如果公式引用自己所在的单元格，不论是直接还是间接，都称为循环引用如：A1单元格的公式为=A1+A2+A3当系统计算此类公式时，必须使用前一次的结果来进行下一次的计算，一般来说Excel将在100次计算后或相邻两次计算值变化小于0.001时，停止迭代计算过程（可在菜单“工具/选项”--“重新计算”中修改）第六节移动和复制公式移动和复制单元格的操作同移动复制单元格操作移动复制公式会产生单元格地址的变化可使用“剪切”“复制”“粘贴”，也可使用鼠标拖拽，还可使用填充柄移动复制公式对相对引用的影响Excel会自动调整位于新粘贴单元格区域内的所有的相对地址引用和混合引用的相关部分移动复制单元格对绝对引用的影响移动复制公式对绝对引用和混合引用中的相关部分没有影响，引用地址保持不变第七节在工作表中使用名称可用行列标题引用该行或该列数据可用“列标题行标题”引用该列该行单元格数据可使用已定义的名称引用相应的数据第八节公式与常见错误错误类型产生原因####单元格所含数字、日期时间的宽度大于列宽或单元格日期时间公式产生负值#VALUE!使用错误的参数或运算对象类型#DIV/0!被除数为0#NAME？公式中使用Excel不能识别的文本#N/A函数或公式中没有可用数值#REF！单元格引用无效#NUM！公式或函数中某数字有问题#NULL！使用了不正确的区域运算符或不正确的单元格引用第七章工作表函数的使用函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。如：SUM函数用来求和，AVERAGE函数用来求平均第一节了解工作表函数在Excel中使用函数，必须先将它们输入到公式中若函数的参数也是函数，称为函数的嵌套一个公式最多可以嵌套7层函数的参数的开始和结束用（）括起，（）必须成对出现，（）前后不能有空格函数的结构以函数名称开始，然后是左圆括号（、以逗号分开的参数和右圆括号）应用篇函数结构如：例如：求平均=AVERAGE(A1：A5)例如：求最大值=MAX(A1：B10)函数语法有的函数带有必选参数和可选参数如：CELL(info_type，reference)，加粗的是必选参数，常规是可选参数必选参数一定要有，可选参数可省略正确使用：CELL(“format”,B12)，CELL(“format”)，但CELL()是错误的，因为没有必选参数有的函数参数后跟省略号……如：SUM(number1,number2……)等号，如果函数应用于公式中函数名称函数参数，用逗号分隔参数用（）括起带省略号表示用户可使用该数据类型的多个参数正确的使用:SUM(26,31)、SUM(A1:B4)，A1,B2)等有的函数不带参数如：NOW()虽然这些函数没有参数，但使用时一定要带（）常用参数类型number：数字text：文本reference：引用value：任意单值结果logical：逻辑值函数中的逗号函数中使用逗号分隔不同参数，不可多键入逗号如果用逗号预留了一个参数位置而未输入该参数，系统将用默认值代替该参数，但必选参数除外例如：求平均AVERAGE(1,2,3,4,5),结果为3;若输入为AVERAGE(,,1,2,3,4,5),则结果为(0+0+1+2+3+4+5)/7=2.14,因为用逗号预留的参数用缺省值0补上参与了计算第二节在公式中输入函数两种函数输入方法手工输入使用粘贴函数手工输入函数选定单元格在编辑栏单击“编辑公式”按钮单击“函数”下拉列表框，选择函数，如果函数不在下拉列表中，单击列表中“其他函数”项在粘贴函数对话框中选择函数并输入相应参数粘贴函数选定单元格菜单“插入/函数”或单击工具栏上“粘贴函数”按钮，出现对话框选择函数的种类和具体函数后“确定”填入函数所需的参数（利用“范围”按钮可以方便快捷输入引用区域）第三节数学和三角函数SUM函数格式：SUM(number1,number2……)参数为1~30个例:=SUM(4,6)值为10例:=SUM(“3”,6,FALSE)值为9,因为文本“3”被自动转换为数字3,逻辑值FALSE被转换为数字0例:若F10单元格内容为文本“3”,则=SUM(F10,6)的值为6,因为对非数值型的值的引用不能被转换成数值参与计算如果参数为引用,只有其中的数字被计算,引用中的文本、逻辑值、错误值将被忽略如果参数错误或不能被转换成数字的文本，将导致错误SUMIF函数格式：SUMIF(RANGE,criteria,sum_RANGE)RANGE:用于条件判断的单元格区域criteria:为确定哪些单元格将被求和的条件sum_RANGE:为需要求和的实际单元格。只有当RANGE中相应单元格满足条件，才对sum_RANGE中的单元格求和。若省略sum_RANGE,则直接对RANGE中的单元格求和如:=SUM(A1:A5,“1000”,B1:B5)INT函数格式:INT(number)对number取整如:=INT(12.4)的值是12如:=INT(-12.4)的值是-13RAND函数格式:RAND()返回大于等于0小于1的随机数如:=RAND()*100,生成1个0~100的数如:=INT(RAND()*100),生成1个0~100的整数ROUND函数格式:ROUND(number,num_digits)根据number_digits指定的位数为number四舍五入如:=ROUND(3.52,1)值是3.5如:=ROUND(-4.276,2)值是-4.28如:=ROUND(46.5,-1)值是50PI函数格式:PI()返回圆周率3.1415926…,精确到小数点后15位如:=SIN(PI()/2)值是1SIN函数格式:SIN(number)返回给定弧度的正弦值SQRT函数格式:SQRT(number)返回number的平方根ABS函数格式:ABS(number)返回number的绝对值TRUNC函数格式:TRUNC(number,num_digits)根据num_digits指定的位数对number截取小数部分num_digits默认值为0如:=TRUNC(-9.3)值为-9LOG函数格式:LOG(number,base)返回以base为底number的对数,缺省的base位10如:=LOG(16,2)值是4COUNTIF函数格式:COUNTIF(RANGE,criteria)计算RANGE指定区域内满足criteria条件的单元格数目如:A3:A6内容分别为“apples”,“oranges”,“peaches”,“apples”,则=COUNTIF(A3:A6,“apples”)值为2如:B3:B6内容分别为32,54,75,86,则=COUNTIF(B3:B6,“55”)值为2第四节文本函数CHAR函数格式:CHAR(number)返回一个ASCII码为number(取值1~255)的字符如:CHAR(65)值为“A”LEFT函数和LEFTB函数格式:LEFT(text,num_chars)格式:LEFTB(text,num_bytes)主要应用于中文字符返回text文本串中前chars个字符,缺省的num_chars为1,若其大于text总长度,返回整个字符串如:=LEFT(“Sale_Price”,4)值是“Sale”如:A1单元格内容为“Soft”,则=LEFT(A1)值是“S”如:=LEFT(“计算机”,2)值是“计算”如:=LEFTB(“计算机”,4)值是“计算”LEN函数和LENB函数格式:LEN(text)格式:LENB(text),主要应用于中文字符返回text字符串的长度,空格也作为字符参与计算如:=LEN(“”)值是0如:=LEN(“”)值是1如:=LEN(“计算机”)值是3如:=LENB(“计算机”)值是6MID函数和MIDB函数格式:MID(text,start_num,)格式:MIDB(text,start_num,num_bytes)返回指定字符串text中从start_num开始长度为num_chars或num_bytes的子字符串如果start_num),返回“”空文本如果start_numLEN(text),但start_num+num_charsL