excel常用公式大全

EXCEL公式和函数公式的使用和公式的调整常用函数工作表的格式化公式•Excel表格中不仅可以输入数据，也可以使用公式对工作表中的数据进行各种数值计算和统计分析等•Excel的公式是由运算符号，和参与计算的运算数组成的表达式公式中的运算符算术运算符：+-*/^%字符运算符：&连接符比较运算符：===逻辑运算符：andornot以函数形式出现优先级顺序：算术运算符→字符运算符→比较运算符→逻辑函数符(使用括号可确定运算顺序)公式中的数据类型输入公式要注意公式中的运算对象可以包括：数值和字符、单元格地址、区域、函数等,如：=A2+B2+C2不要随意包含空格公式中的字符要用半角引号括起来公式中运算符两边的数据类型要相同如：=ab+25出错#VALUE公式的输入公式的基本特性：•公式的输入是以“=”开始•公式计算结果显示在单元格中•公式本身显示在编辑栏中公式示例•=1+2+6（数值计算）•=A1+B2（引用单元格地址）•=SUM(A1:C4)（公式计算）•=IF(and(A1A2,B1B2),100*A1/A2,100*B1/B2)（函数计算）•=ABC&XYZ(字符计算，结果:ABCXYZ)公式中常用单元格的引用来代替单元格的具体数据，当公式中被引用单元格的数据变化时，公式的计算结果会随之变化公式的复制和移动公式的复制：–使用填充柄–菜单[编辑]→[复制]/[粘贴]复制公式时，公式会作相对调整相对引用和绝对引用•相对引用是针对当前单元格的引用，是随当前单元格变化而变化的•绝对引用是针对起始单元格的引用，是不随当前单元格的变化而变化的公式的调整相对地址引用:在公式复制时将自动调整调整原则是：新行地址=原来行地址+行偏移量•新列地址=原来列地址+列偏移量例如：将D2单元中的公式=A2+B2+C2复制到D4后，D4中的公式=A4+B4+C4公式的调整绝对地址引用在公式复制时不变。例如：C3单元的公式=$B$2+$B$3+$B$4复制到D5中，D5单元的公式=$B$2+$B$3+$B$4混合地址在公式复制时绝对地址不变，相对地址按规则调整。公式的调整公式移动相对和绝对地址都不调整C3单元格的公式=B2+B3+B4移动到D5中，D3单元格的公式=B2+B3+B4被公式引用的单元格移动，公式跟踪单元格，值不变选择性粘贴菜单[编辑]→[复制]菜单[编辑]→[选择性粘贴]公式、数值、格式的粘贴运算、转置的使用Sumif函数•根据指定条件对若干单元格求和。•SUMIF(range,criteria,sum_range)•Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、“32”、“32”或“apples”。•Sum_range为需要求和的实际单元格。只有当Range中的相应单元格满足条件时，才对sum_range中的单元格求和。如果省略sum_range。则直接对Range中的单元格求和•例如，假设A1:A4单元格中的内容分别为四种产品的属性值：$100,000，$200,000，$300,000，$400,000。B1:B4的内容为每个属性值所对应的销售佣金；$7,000，$14,000，$21,000，$28,000，那么就可以运行公式：SUMIF(A1:A4,160,000,B1:B4)最后的求和结果将是$63,000。函数•COUNT•用途：它可以统计数组或单元格区域中含有数字的单元格个数。•语法：COUNT(value1，value2，...)。•参数：value1，value2，...是包含或引用各种类型数据的参数(1～30个)，其中只有数字类型的数据才能被统计。•实例：如果A1=90、A2=人数、A3=〞〞、A4=54、A5=36，则公式“=COUNT(A1:A5)”返回3•COUNTA•用途：返回参数组中非空值的数目。语法：COUNTA(value1，value2，...)•说明:参数可以是任何类型，它们包括空格但不包括空白单元格。如果不需要统计逻辑值、文字或错误值，则应该使用COUNT函数。•实例：如果A1=6.28、A2=3.74，A3=“我们”其余单元格为空，则公式“=COUNTA(A1:A7)”的计算结果等于3。•COUNTBLANK•用途：计算某个单元格区域中空白单元格的数目。•语法：COUNTBLANK(range)•参数：Range为需要计算其中空白单元格数目的区域。•实例：如果A1=88、A2=55、A3=、A4=72、A5=，则公式“=COUNTBLANK(A1:A5)”返回2。•COUNTIF•用途：计算区域中满足给定条件的单元格的个数。•语法：COUNTIF(range，criteria)•参数：Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本If函数•1．IF函数的语法结构IF函数的语法结构：IF(条件,结果1,结果2)。•2．IF函数的功能•对满足条件的数据进行处理，条件满足则输出结果1，不满足则输出结果2。可以省略结果1或结果2，但不能同时省略。•3．条件表达式•把两个表达式用关系运算符(主要有=，，，，=，=等6个关系运算符)连接起来就构成条件表达式，例如，在IF(a1+b1+50b1+c1-50,1,2)函数式中，条件表达式是a1+b1+50b1+c1-50。•4．执行过程•下面以IF(a1+b1+50b1+c1-50,1,1)函数式为例来说明IF函数的执行过程。先计算条件表达式a1+b1+50b1+c1-50，如果表达式成立，值为TRUE，并在函数所在单元格中显示“1”；如果表达式不成立，值为FALSE，并在函数所在单元格中显示“2”。IF(E2=85,“优”,IF(E2=75,“良”，IF(E2=60,及格，不及格)))•函数从左向右执行。首先计算E2=85，如果该表达式成立，则显示“优”，如果不成立就继续计算E2=75，如果该表达式成立，则显示“良”，否则继续计算E2=60，如果该表达式成立，则显示“及格”，否则显示“不及格”。数据排序•使用工具栏上的排序按钮进行排序•单击数据清单需要排序的列中任一单元格；•单击“常用工具栏”上的升序或降序按钮.•使用“数据”菜单中的“排序”命令进行排序•1、排序•单击数据清单任一单元格；•单击[数据]-[排序]•选择主要关键字、次要关键字；•选择升序还是降序，确定。数据筛选•任选一个单元格•数据-----筛选------自动筛选分类汇总•分类汇总:就是把某一个关键字段的相同数据汇总在一起。•使用分类汇总命令之前，首先要对关键字段进行排序，使关键字段相同的数据记录可以连续访问。•操作步骤：•●先对分类字段进行排序•●打开“数据”下拉菜单，再单击“分类汇总”命令•●在“分类汇总”对话框中选择分类的字段（与排序字段相同），汇总的方式，汇总的项（统计的数值字段）清除分类汇总•“编辑“里的“撤消分类汇总”命令（若已按了1、2或3分级显示，则需多次按“撤消显示分级层次”至到出现“撤消分类汇总”为止），•方法二：在“分类汇总”对话框中按“全部删除”按钮。多工作表或工作簿的操作同一工作簿中的某一个公式中引用不同工作表中的数据，要引用的单元格地址前加表名字和感叹号！如：=sheet3!E3+sheet3!F3一个公式中引用不同工作簿中的数据，要在引用的单元格地址前加工作簿的名字，并用方括号括起来如：=[cjb.xls]sheet3!E3+[cjb.xls]sheet3!F3工作表的格式化单元格格式的设置:选定单元格或区域选择菜单[格式]→[单元格]设置数字格式：两位小数、千位分隔字体、对齐方式、边框底纹等，EXCEL表格要加边框线，打印的时候才有边线合并及居中选择菜单[格式]→[条件格式]设置带条件的格式，如：小于60分的成绩显示红色粗体，背景为兰色使用[格式刷]或[选择性粘贴]复制格式工作表的格式化调整行高和列宽–鼠标拖拽操作–菜单操作：[格式]→[行]/[列]–隐藏行或列：[格式]→[行]/列]→[隐藏]工作表的格式化工作表的整体显示–工作表的背景设置菜单[格式]→[工作表]→[背景]–工作表的整体设置菜单[工具]→[选项]的[视图]，工作表的网格线的设置自动套用格式菜单[格式]→[自动套用格式]样式（格式的组合）菜单[格式]→[样式]添加、修改、删除、应用样式工作表的格式化拆分和冻结工作表•拆分工作表窗口–工作表拆分成多个窗格，在拆分窗口中查看工作表的不同部分内容–选择菜单[窗口]→[拆分]•冻结工作表窗口–冻结工作表中某几行或几列单元格的显示–选择菜单[窗口]→[拆分]–=vicent&id=55&commentid=48–