2022年电子表格函数公式使用集锦 .pdf

1 电子表格函数公式使用集锦怎么让 excel 表格公式只能看不能被改：选定不需要保护的的单元格，单击右键设置单元格格式取消锁定选定需要保护的的单元格，单击右键设置单元格格式锁定工具保护保护工作表输入密码确定。一、电子表格中由身份证号自动导出年月日的公式=IF(LEN(E1)=15,19&MID(E1,7,2)&-&MID(E1,9,2)&-&MID(E1,11,2),MID(E1,7,4)&-&MID(E1,11,2)&-&MID(E1,13,2)转换出生年月如1986-05-23 说明：E：列数 E1890：第 E列第 1890行。输入身份证号码LEN （text ）: 返回本字符串的个数。 LEN （“123”）=3 LEN(E1)=15表示如果身份证号码为15 个数字&:表示相加MID （字符串， M ，N）：从该字符串第M位开始，取 N位字符。MID （A1，3，4）=3456，从“ A1”单元格中的第“ 3”位起截取“ 4”个数IF（条件表达式，语句1，语句 2）：如果条件成立，那么就执行语句1，否则执行语句 2 LEFT （A1,14）截取 A1单元格前 14位数RIGHT （A1,14）截取 A1单元格后 14 位数身份证号码有两种，如“352124860213541 ”或“ 352124198602135411 ”如果 E1是15 个, 为 19 加上从第 7 个开始取 2 个“86” 加上-加上从第 9 个开始取 2 个“02” 加上- 加上从第 11 个开始取 2 个“13”合起来为“ 1986-02-13 ”，否则为从第7 个开始取 4个 “1986” 加上-加上从第 11个开始取 2 个 “02” 加上- 加上从第 13 个开始取 2 个“13”合起来为“ 1986-02-13 ”。 19 、- 为直接写入的数。=IF(LEN(A1)=15,19&MID(A1,7,4),MID(A1,7,6)转换出生年月取如“ 198606”=IF(LEN(A1)=15,CONCATENATE(19,MID(A1,7,2),.,MID(A1,9,2),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,4),.,MID(A1,11,2),身份证错 ) 转换出生年月取如1986.05 =2010-MID(B1,1,4)-IF(MID(B1,5,2)-0)8,1,0)计算年龄名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 9 页 - - - - - - - - - 2 =IF(LEN(A1)=15,YEAR(NOW()-1900-VALUE(MID(A1,7,2),IF(LEN(A1)=18,YEAR(NOW()-VALUE(MID(A1,7,4),身份证错 ) 计算年龄，月数全部不算如24 岁 2个月和 24岁 11 个月都是 24 岁=IF(LEN(A1)=15,IF(MOD(VALUE(RIGHT(A1,3),2)=0,女, 男),IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,15,3),2)=0,女, 男),身份证错 ) 转换性别二、成绩在年级里的排名菜RANK （）函数的使用=RANK(N2,$N$2:$N$1501,0) N2为所要排名的单元格， $N$2:$N$1501为从 N2列到 N1501列，0 表示为按照降序排列的列表，不为零为按照升序排列的列表=RANK(C1,$C$1:$C$10) 为 10 个学生中的第一个的排名三、利用函数统计考试成绩=COUNTA(A1:A25) 算有数值的单元格个数应考人数=COUNT(B1:B25) 和上面的一样用处算出考试人数=COUNTBLANK(B1:B25)算出缺考人数=COUNTIF(B1:B25,=90) 算 90 分以上人数 =COUNTIF(B1:B25,=80)- COUNTIF(B1:B25,=90) 算 80 到 90 分人数=MAX(C1:C25) 算最高分=MIN(C1:C25)算最低分=AVERAGE(C1:C25)算平均分=COUNTIF(C1:C25,=90)/COUNT(C1:C25)90分以上占百分比=MEDIAN(B1:B25) 算中位数=MODE(B1:B25) 算众数=STDEVP(B1:B25) 算标准差四、文本格式转换成数值格式在原单元格上转换： 在任一空白单元格输入1复制 1选定所有需要改变的单元格右键选择性粘贴选乘，用 0 加计算也行名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 9 页 - - - - - - - - - 3 在新单元格上转换：选定新单元格， “=所要转换的文本单元格 -0 ”在新单元格上转换：复制选择性粘贴数值点击下拉转换成数值五、&的应用=B2&C2 表示把两个单元格的数值合为一个如“45” 、 “67”合为“ 4567”=$F$17&C1 表示在一个数值前加一个数， 在任意单元格输入123 设为绝对值， 再加所要添加的单元格六、IF 的应用（满足其中一个条件的判断）=IF(A289, 优+,IF(A279,优,IF(A269,良+,IF(A259,及格, 不及格) =IF(A2B2, 超预算 , 预算内 ) 是对预算执行结果的判断=IF(A2=100,SUM(B5:B15),) 如果 A2数字为 100，则计算单元格区域B5:B15，否则返回空文本 () =IF(AND(A10,A110,A1=100,A160,IF(AND(C190),优秀, 合格), 不合格 ) 如果单元格 C1的值大于 60，则执行第二个参数， 在这里为嵌套函数， 继续判断单元格 C1的值是否大于 90 （为了让大家体会一下 AND函数的应用，写成 AND(C190) ，实际上可以仅写C190 ） ，如果满足在单元格 C2中显示优秀字样，不满足显示合格字样，如果C1的值以上条件都不满足，则执行第三个参数即在单元格C2中显示不合格字样。=IF(A1=1,A,IF(A1=2,B,IF(A1=3,C,IF(A1=4,D,IF(A1=5,E,IF(A1=6,F,IF(A1=7,G,IF(A1=8,G,)&IF(A1=9,Q,IF(A1=10,l,IF(A1=11,k,IF(A1=12,y,IF(A1=13,x,IF(A1=14,n,IF(A1=15,m,IF(A1=16,o,)&, 为多层嵌套七、AND 函数（同时满足条件判断）名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 9 页 - - - - - - - - - 4 AND （条件一，条件二 . 条件三, ) ，条件一二三 , 都成立时返回TURE ，否则返回 FALSE =IF(AND(A160,B160,C160), 及格, 不及格 ) ，当 AND （A160 ，B160，C160 ）为 TURE 时返回 “及格” ，为 FALSE时返回“不及格”=IF(A160,A170,A185,优秀 )可以和 IF 函数合起来使用八、CONCATENATE函数（将几个文本字符串合并为一个文本字符串）=CONCATENATE(A1,B1,C1,D1) 也可以用 & （和号）运算符代替函数 CONCATENATE 实现文本项的合并。九、名称和标志为了更加直观地标识单元格或单元格区域，我们可以给它们赋予一个名称，从而在公式或函数中直接引用。例如“B2:B46”区域存放着学生的物理成绩，求解平均分的公式一般是“ =AVERAGE(B2:B46) ” 。在给 B2:B46 区域命名为“物理分数”以后，该公式就可以变为“=AVERAGE(物理分数 )” ，从而使公式变得更加直观。给一个单元格或区域命名的方法是: 选中要命名的单元格或单元格区域，鼠标单击编辑栏顶端的“名称框”，在其中输入名称后回车。也可以选中要命名的单元格或单元格区域，单击“插入名称定义”菜单命令，在打开的“定义名称”对话框中输入名称后确定即可。如果你要删除已经命名的区域，可以按相同方法打开“定义名称”对话框，选中你要删除的名称删除即可。由于 Excel 工作表多数带有“列标志”。 例如一张成绩统计表的首行通常带有 “序号”、“姓名” 、 “数学” 、 “物理”等“列标志” ( 也可以称为字段 ) ，如果单击“工具选项”菜单命令，在打开的对话框中单击“重新计算”选项卡，选中“工作簿选项”选项组中的“接受公式标志”选项，公式就可以直接引用“列标志”了。例如“B2:B46”区域存放着学生的物理成绩，而B1单元格已经输入了“物理”字样，则求物理平均分的公式可以写成 “=AVERAGE(物理)” 。十、几个常用函数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 9 页 - - - - - - - - - 5 =ABS(A2)，则在 A2单元格中无论输入正数（如100）还是负数（如 -100） ，B2中均显示出正数（如100）=AND(A5=60,B5=60) ，确认。如果 C5中返回 TRUE ，说明 A5和 B5中的数值均大于等于 60，如果返回 FALSE ，说明 A5和 B5中的数值至少有一个小于60 =COLUMN(B11)，确认后显示为“ 2” （即 B列）十一、与求和有关的函数1、=SUM(H3:H12) 求 H3至 H12的和2、SUBTOTAL(function_num,ref1,ref2,) 分类汇总Function_num 为 1 到 11 之间的数字，指定使用何种函数在数据清单中进行分类汇总计算。Function_Num 函数1 AVERAGE 求算术平均数2 COUNT 计算参数列表中的数字项的个数3 COUNTA 计算单元格区域或数组中包含数据的单元格个数。4 MAX 求最大值5 MIN 求最小值6 PRODUCT 单元格内的乘积7 STDEV 估算样本的标准偏差，反映相对于平均值的离散程度8 STDEVP 整个样本总体的标准偏差9 SUM 求和10 VAR 计算基于给定样本的方差11 VARP 计算基于整个样本总体的方差例：“=SUBTOTAL(9,A2:A5) 对 A2至 A5列使用 SUM 函数计算出的分类汇总 (303) ” ，“=SUBTOTAL(1,A2:A5) 对 A2至 A5列使用 AVERAGE 函数计算出的分类汇总 (75.75)”3、SUMIF 根据指定条件对若干单元格求和名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 9 页 - - - - - - - - - 6 例：=SUMIF($C$3:$C$12,销售部 ,$F$3:$F$12) ，“$C$3:$C$12 ”指部门名称单元格， 销售部 指计算其中的“销售部”部门，“$F$3:$F$12”指部门名称相应的数值单元格。4、SUMPRODUCT在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和=SUMPRODUCT(A15:A16,B15:B16)表示“ A15*B15+A16*B16”=SUMPRODUCT(B2:C4*D2:E4)表示“ B2*D2+C2*E2+ ,+ B4*D4+C4*E4”=SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 计算符合2 个及以上条件的数据个数（4 人的单元格输入公式）姓名性别职称性别中一中二A 男中一男4 B 女中二女C 女中一D 男中一E 女中一F 男中二G 女中二H 男中一I 男中一J 女中一=SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)计算男、女分别是中一或中二的总课时数（在15 节单元格中输入公式）姓名性别职称课时性别中二中一A 男中一15 男15 B 女中二16 女C 女中一14 D 男中一13 E 女中一18 F 男中二15 G 女中二16 H 男中一14 I 男中一17 J 女中一18 SUMSQ 函数：计算多个数值的平方和。如SUMSQ (B2,C2)=B2 的平方+C2的平方。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 9 页 - - - - - - - - - 7 ROUND 函数：如 ROUND(B2,2) 就是对 B2进行四舍五入保留2 位小数。INT（将数字向下舍入到最接近的取整函数）IF 和 AND 嵌套使用： =IF(AND(A160,B160,C160),及格, 不及格 ) ，当 A1，B1,C1 都大于 60时 返回“及格”=IF(A160,A170,A185,优秀 )，当 A160 时返回“不及格”，当 60A170 时返回“及格”，当70A185时返回“优秀”COUNTIF 函数：计算其中满足条件的单元格数目，如COUNTIF(B4:B10,90) ，计算B4到 B10这个范围各科成绩中有多少个数值大于90 的单元格。如COUNTIF($C$2:$C$13,A17) ，计算$C$2:$C$13这个范围有多少个A17 （A17存放的是姓名）SUMIF($C$2:$C$13,A17,$B$2:$B$13)计算其中（A17）的销售奖金， $C$2:$C$13是销售人员的姓名， A17是其中的一个姓名， $B$2:$B$13是销售金额区域，IF(C1750000,10%,15%)*C17 如果订单总额小于 50000 则奖金为 10%；如果订单总额大于等于 50000，则奖金为 15% 十二、字母大小写转换LOWER (A1) 将 A1文字串中的所有字母转换为小写字母。UPPER (A1) 将 A1文本转换成大写形式。PROPER (A1) 将 A1文字串的首字母及任何非字母字符之后的首字母转换成大写。将其余的字母转换成小写。十三、取出字符串中的部分字符LEFT(This is an apple,4)=This从前面取RIGHT(This is an apple,5)=apple从后面取MID(This is an apple,6,2)=is从中间取十四、取出当前系统时间/ 日期信息NOW() 取当前系统“年月日时分”TODAY() 取当前系统“年月日”YEAR(E5)=2001取单元格的“年”名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 9 页 - - - - - - - - - 8 MONTH(E5)=5 取单元格的“月”DAY(E5)=30取单元格的“日”HOUR(E5)=12 取单元格的“时”DATEDIF ：计算两个日期之间的天数、月数或年数：其中计算年数为DATEDIF(A24,TODAY(),y) ，Y 时间段中的整年数， M 时间段中的整月数， D 时间段中的天数， MD为日期中天数的差，忽略日期中的月和年（直接天数相减，不够减要向上月借一）， YM为日期中月数的差，忽略日期中的日和年（直接月数相减，不够减要向上月借一）， YD 为日期中天数的差。忽略日期中的年（月日合计相减，不够减要向上月借一）。VALUE ：将代表数字的文字串转换成数字，语法形式为：VALUE(text) IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),女, 男) ：INT：返回实数舍入后的整数值，当VALUE(RIGHT(E4,3)/2 与取整时的 INT(VALUE(RIGHT(E4,3)/2相等时说明为偶数。 创建日期： &TEXT(TODAY(),dd-mm-yyyy) ，为提取系统的时间格式也可改为“YYYY-MM-DD”十五、引用函数ADDRESS 函数： ADDRESS(2,3) 绝对引用 ($C$2) 。ADDRESS(2,3,2) 绝对行号， 相对列标(C$2)。 ADDRESS(2,3,2,FALSE) 在 R1C1引用样式中的绝对行号， 相对列标 (R2C3) 。ADDRESS(2,3,1,FALSE,Book1Sheet1) 对其他工作表的绝对引用(Book1Sheet1!R2C3) 。ADDRESS(2,3,1,FALSE,ETSHEET) 对其他工作表的绝对引用(ETSHEET!R2C3) COLUMN用于返回给定引用的列标：如COLUMN(D3), 即：查看第 3 行 D列这个单元格所在第几列，因此结果为4 ROW 用于返回给定引用的行号：ROW(E12) 结果为 12 行AREAS 用于返回引用中包含的区域个数。COLUMNS用于返回数组或引用的列数。ROWS用于返回引用或数组的行数名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 9 页 - - - - - - - - - 9 INDEX用于返回表格或区域中的数值：“=INDEX(A1:A3 ，1，1) ”两个 1 为行号和列号名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 9 页 - - - - - - - - -