Excel常用公式总结(大一)

Excel常用公式总结一、基本操作数字作为文本输入：’12345678=”12345678”开始-数字-打开单元格格式设置-数字作为文本处理负数输入：()里数字作为负数分式输入：0 1/2 (0-空格-分数)####表示单元格宽度不够，但数字仍存在日期：整数时间：小数2017-9-4 2017/9/4填充序列自定义：文件-选项-高级-常规-编辑自定义列表（填充序列：开始-编辑-填充）（一）COUNTIF 11.含义：条件计数2.语法：COUNTIF(计数范围,条件)二、单元格引用1、绝对引用和相对引用混合引用$D$6（二）IF 2 52 61.含义：逻辑判断函数，如果某事件的x条件成立有A结果，不成立有B结果；2.语法：IF(x条件,条件成立得到的结果A,条件不成立得到的结果B)3.如果一件事有超过两种情况，那么需要嵌套函数，即当事件的第一个条件不成立时，在B 处嵌入一个IF再次进行判断，如某事件有ABC三种情况，A成立有100元，B成立200元，C 成立300元，那么判断函数为：IF(A成立条件,”100元”,IF(B成立条件,”200元”,”300元”)) 因为用到了两次IF函数，所以有两个括号。

三、公式基本应用开始-数字-数值-小数位数常量（数值、字符：””括起来的、逻辑TRUE、FALSE）变量公式移动：相当绝对引用，不变/复制：Ctrl+鼠标拖动、黏贴、填充柄移动：相对引用会变化）运算符：①算数运算符②文本运算符&（字符串若是数字，可省略双引号）运算优先级：引用、%、^ 、*/、+-、&、比较运算符（三）RANK（早期、不常用） 41.含义：按一定规则进行排序2.语法：RANK(被排序的单元格,排序范围,服从规则)3.服从规则有两个值：0—降序，1—升序4.注意：在排序时要对范围进行锁定（引用范围添加混合引用）RANK.EQ相同数值返回最佳排名（用这个）RANK.AVG相同数值返回平均排名六、选择性粘贴：（跨表引用防止表格删除引起出错） 加法：先复制，选中要复制到的单元格，右键选择性粘贴-加法合并计算：数据-数据工具-合并计算（四）LEFT 61.含义：从左侧截取单元格的文本2.语法：LEFT(被截取单元格,截取位数)，如A1单元格里是“张小瑞”，我要在另一个单元格里得到她的姓，此时可用左侧截取，公式为：=LEFT(A1,1)，即从左侧截取A1单元格的一个字（也可用mid）（五）RIGHT1.含义：从右侧进行截取2.语法：类left，自己套用；七、数据有效性设置（也叫数据验证）选定要填充的单元格-数据-数据工具-数据有效性/数据验证-选定可输入范围单元格八、数据筛选排序和分类汇总（简单排序、复合排序、特殊排序）数据-排序和筛选（排序：升序降序：数字大小、拼音大小）筛选：点击数据工具中筛选按钮，高级筛选：条件区域：第一行列标题，下边：对应关系分类汇总：（数据-分级显示）先按分类字段进行排序、再进行分类汇总九、数据工具-删除重复项（六）SUMIF 91.含义：对指定单元格区域中符合某一条件的单元格求和；2.语法：SUMIF(条件判断区域,条件,[求和范围])，当判断条件的区域和求和区域为同一区域时，求和范围不用写，如果条件判断和求和是两个区域，则需要写，如=SUMIF(B2:B10,”>5”,C2:C10)其含义为在B中找到“>5”的单元格，然后将对应的C求和；十、数据透视表（单元格要有列标题）（需要全面对数据做全面分析时使用，结合分类汇总和合并计算的优点，可以方便地调整分类汇总的依据，很灵活）（建立、应用，在excel指导下使用）选一个单元格-插入-数据透视表-在新工作表生成（右边有可视化工作栏，可将标题向下拖动）注：调整分析步长：日期会只能整合，可以右键取消智能整合或者按季度等整合添加/删除字段、显示/隐藏数据、调整显示方向、改变计算函数改变显示方式：进行对比：右键：数据显示方式，如差异百分比不能直接在数据透视表（只读性）更改，更改原始记录！注：①公式必须要有”=”，”函数名”，”英文半角的括号()”，”函数参数”;②用“[]”括起来的参数是非必需参数；③用英文半角的引号引起来的东西，系统会按照文本格式保存；④“$”的意义：锁定，绝对引用，放在行标和列标前面，表示绝对引用；⑤“&”的意义：文本连接符，放在文本与文本之间；⑥函数中，文本条件或包含逻辑符号的条件，都要用引号引起来；○7将单元格变成文本形式：英文半角符’，或右键设置单元格形式⑧跨表引用的时候使用! 如Sheet1!A1:D1⑨@是字符占位符（七）SUM 11.含义：求和，一般用来算总成绩、总销售额等；2.语法：SUM(数1,数2,...)或SUM(求和范围如A1:C4)3.自动求和：开始-编辑-自动求和（八）MAX1.含义：求一定区域内的最大值，如求最高分就可用此函数；2.语法：MAX(数1,数2,...)或MAX(求和范围如A1:C4)（九）MIN （52）1.含义：求一定区域内的最小值，如求最高分就可用此函数；2.语法：MIN(数1,数2,...)或MIN(求和范围如A1:C4)（十）INT 521.含义：向数轴的反方向取整（把小数砍掉）2.语法：INT(某数)（十一）ROUND1.含义：四舍五入2.语法：ROUND(要进行运算的数本身，保留小数位数（1：一位小数，0：取整，-1:10）) 例子：四舍五入到100的整数倍（若要舍掉不足100的，用int（数/100）*100）（十二）AVERAGE 51.含义：求平均数2.语法：AVERAGE(数1,数2,...)或AVERAGE(求和范围如A1:C4)例子：分数去掉最高和最低求平均，先全加起来（sum），再减掉两个补充：trimmean函数（修建平均值，不要求，没讲）（十三）AVERAGEIF1.含义：条件平均2.语法：与SUMIF相似，自己套用；（十四）COUNT 21.含义：数某一区域内数据类型为数字的单元格个数2.语法：COUNT(数或区域)（十五）NOW1.含义：返回当前系统的日期和时间2.语法：NOW()（十六）TODAY 521.含义：返回当前日期2.语法：TODAY()例子：工龄补贴，先计算工作天数，再除以365.25计算（十七）YEAR()1.含义：返回某一日期的年份；2.语法：YEAR(“日期”)3.注意：日期要用引号引起来，否则，系统会根据数学运算法则进行计算；（十八）MONTH1.含义：返回某一日期的月份2.语法：MONTH(“日期”)（十九）DATEDIF（office的隐藏函数）1.含义：计算两个日期之间的天数、月数或年数2.语法：DATEDIF(开始日期,结束日期,年Y/月M/天D)（二十）MID 51.含义：从文本中间截取2.语法：MID(被截取单元格,起始位置,截取个数)，如我要“张小瑞”中的“小”字，那么公式为：=MID(A1,2,1)，即从第二位开始截，截取一位例子：从身份证号中截取生日（二十一）LEN1.含义：数一个单元格里文本字符串的字符个数2.语法：LEN(单元格)，如我要知道上面的A1里有几个字，此时可用LEN，公式为：=LEN(A1)即可（二十二）ROW1.含义：返回指定单元格的行标数。

2.语法：ROW(单元格坐标)（二十三）COLUMN1.含义：返回所选择的某一个单元格的列数。

语法与row相似CONCATENATE 比较合并单元格例如：=CONCATENATE（B2,”的”,C2,”的籍贯是”,K2）=B2的C2的籍贯是K2MOD 求余函数5=MOD（A1，2），用A1中的数除以2的余数例子：身份证号提取性别信息（倒数第二位是性别位）DATE 日期函数5=DATE（A1,B1,C1）=1999年01月15日逻辑函数=and() 4所有参数为TURE时，返回TURE 否则为FALSE [所有参数满足条件]例子一等奖学金中，也可以内层用min，最小的大于等于85=or() 4返回逻辑值ture , false [其中一个参数满足条件]例子：单科奖学金，也可用max函数=NOT()查找函数vlookup(vertical查阅)（水平查阅HLOOKUP）=VLOOKUP（要查的数（查表函数），在哪儿查（全部数据区域），要查的值所在行的要查的列的列的序号，TRUE近似查找（比要查数小的最近似的数）/FALSE精确查找）类似找三角函数表：函数功能：在range的第一列查要查的数值（函数中第一项），确定行号后，返回该行要查找的列（函数中第三项），以精确或近似方式查找，给出所查数值相应行对应列号的空格中所填数值FALSE 精确查询精确找到对应参数TURE 模糊查询查询前面最近小于或等于的值例子：【近似查找典型】岗位津贴计算：建立岗位津贴标准表，以要计算的表格中的岗位为第一项，range为标准表，返回津贴数值列，精确查找）注：range要注意引用方式绝对引用税率算法：（全工资-起征点）*【（全工资-起征点）相应等级税率（使用vlookup，应用记得加$）】-速算扣除数=LOOKUP（关键词，参数的范围【只含一行/列，必须是升序排列】，查找结果的范围）应用数据库函数=DSUM(database，field, criteria)Database：要进行的全部范围（整张表带着标题选上）Field指定函数所使用的数据列。

（进行求和的数据区域）Criteria条件：列标志、列标志下的具体内容，用于在列中筛选=DCOUNT=DAverage=pmt(rate, nper, pv, [fv], [type])月初1，月末0。