电子表格函数公式使用集锦Word格式文档下载.docx
- 文档编号:14681222
- 上传时间:2022-10-23
- 格式:DOCX
- 页数:8
- 大小:104.85KB
电子表格函数公式使用集锦Word格式文档下载.docx
《电子表格函数公式使用集锦Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《电子表格函数公式使用集锦Word格式文档下载.docx(8页珍藏版)》请在冰豆网上搜索。
表示相加
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”。
"
、"
为直接写入的数。
=IF(LEN(A1)=15,"
MID(A1,7,4),MID(A1,7,6))转换出生年月取如“198606”
=IF(LEN(A1)=15,CONCATENATE("
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)计算年龄
=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:
=80"
)-COUNTIF(B1:
)算80到90分人数
=MAX(C1:
C25)算最高分
=MIN(C1:
C25)算最低分
=AVERAGE(C1:
C25)算平均分
=COUNTIF(C1:
C25,"
)/COUNT(C1:
C25)90分以上占百分比
=MEDIAN(B1:
B25)算中位数
=MODE(B1:
B25)算众数
=STDEVP(B1:
B25)算标准差
四、文本格式转换成数值格式
放着学生的物理成绩,而B1单元格已经输入了“物理”字样,则求物理平均分的公式可以写成“=AVERAGE(物理)”。
十、几个常用函数
=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函数
1AVERAGE——求算术平均数
2COUNT——计算参数列表中的数字项的个数
3COUNTA——计算单元格区域或数组中包含数据的单元格个数。
4MAX——求最大值
5MIN——求最小值
6PRODUCT——单元格内的乘积
7STDEV——估算样本的标准偏差,反映相对于平均值的离散程度
8STDEVP——整个样本总体的标准偏差
9SUM——求和
10VAR——计算基于给定样本的方差
11VARP——计算基于整个样本总体的方差
例:
“=SUBTOTAL(9,A2:
A5)对A2至A5列使用SUM函数计算出的分类汇总(303)”,“=SUBTOTAL(1,A2:
A5)对A2至A5列使用AVERAGE函数计算出的分类汇总(75.75)”
3、SUMIF——根据指定条件对若干单元格求和
=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
$B$11=$F2)*($C$2:
$C$11=G$1),$D$2:
$D$11)计算男、女分别是中一或中二的总课时数(在15节单元格中输入公式)
课时
15
16
14
13
18
17
SUMSQ函数:
计算多个数值的平方和。
如SUMSQ(B2,C2)=B2的平方+C2的平方。
ROUND函数:
如ROUND(B2,2)就是对B2进行四舍五入保留2位小数。
INT(将数字向下舍入到最接近的取整函数)
IF和AND嵌套使用:
=IF(AND(A1>
60,B1>
60,C1>
60),"
及格"
不及格"
),当A1,B1,C1都大于60时返回“及格”
=IF(A1<
60,"
IF(AND(A1>
60,A1<
70),"
70,A1<
85),"
良好"
IF(A1>
85,"
优秀"
)))),当A1<
60时返回“不及格”,当60<
A1<
70时返回“及格”,当70<
85时返回“良好”,当A1>
85时返回“优秀”
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(C17<
50000,10%,15%)*C17如果订单总额小于50000则奖金为10%;
如果订单总额大于等于50000,则奖金为15%
十二、字母大小写转换
LOWER(A1)将A1文字串中的所有字母转换为小写字母。
UPPER(A1)将A1文本转换成大写形式。
PROPER(A1)将A1文字串的首字母及任何非字母字符之后的首字母转换成大写。
将其余的字母转换成小写。
十三、取出字符串中的部分字符
LEFT("
Thisisanapple"
4)=This从前面取
RIGHT("
5)=apple从后面取
MID("
6,2)=is从中间取
十四、取出当前系统时间/日期信息
NOW()取当前系统“年月日时分”
TODAY()取当前系统“年月日”
YEAR(E5)=2001取单元格的“年”
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()
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 电子表格 函数 公式 使用 集锦