Excel表格公式大全,太 强大了!
1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和: =SUM(K2:K56) ——对K2到K56这一区域进行求和;
2、平均数: =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数;
3、排名: =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;
4、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;
6、最高分: =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;
7、最低分: =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;
8、分数段人数统计:
(1) =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格;
(2) =COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;
(3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;
(4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;
(5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;
(6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;
(7) =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;
说明:COUNTIF函数也可计算某一区域男、女生人数。
如:=COUNTIF(C2:C351,"男") ——求C2到C351区域(共350人)男性人数;
9、优秀率: =SUM(K57:K60)/55*100
10、及格率: =SUM(K57:K62)/55*100
11、标准差: =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化);
12、条件求和: =SUMIF(B2:B56,"男",K2:K56) ——假设B列存放学生的性别,K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和;
13、 多条件求和: {=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))} ——假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求 一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。“{}”不能手工输入,只能用组合键产生。
14、根据出生日期自动计算周岁:=TRUNC((DAYS360(D3,NOW( )))/360,0)
———假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。
15、在Word中三个小窍门:
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
来源/知乎
1、查找重复内容公式:=IF(COUNTIF(A:A,A2)>1,"重复","")。
2、用出生年月来计算年龄公式:=TRUNC((DAYS360(H6,"2009/8/30",FALSE))/360,0)。
3、从输入的18位身份证号的出生年月计算公式:=CONCATENATE(MID(E2,7,4),"/",MID(E2,11,2),"/",MID(E2,13,2))。
4、从输入的身份证号码内让系统自动提取性别,可以输入以下公式:
=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,"男","女"),IF(MOD(MID(C2,17,1),2)=1,"男","女"))公式内的“C2”代表的是输入身份证号码的单元格。
1、求和: =SUM(K2:K56) ——对K2到K56这一区域进行求和;
2、平均数: =AVERAGE(K2:K56) ——对K2 K56这一区域求平均数;
3、排名: =RANK(K2,K$2:K$56) ——对55名学生的成绩进行排名;
4、等级: =IF(K2>=85,"优",IF(K2>=74,"良",IF(K2>=60,"及格","不及格")))
5、学期总评: =K2*0.3+M2*0.3+N2*0.4 ——假设K列、M列和N列分别存放着学生的“平时总评”、“期中”、“期末”三项成绩;
6、最高分: =MAX(K2:K56) ——求K2到K56区域(55名学生)的最高分;
7、最低分: =MIN(K2:K56) ——求K2到K56区域(55名学生)的最低分;
8、分数段人数统计:
(1) =COUNTIF(K2:K56,"100") ——求K2到K56区域100分的人数;假设把结果存放于K57单元格;
(2) =COUNTIF(K2:K56,">=95")-K57 ——求K2到K56区域95~99.5分的人数;假设把结果存放于K58单元格;
(3)=COUNTIF(K2:K56,">=90")-SUM(K57:K58) ——求K2到K56区域90~94.5分的人数;假设把结果存放于K59单元格;
(4)=COUNTIF(K2:K56,">=85")-SUM(K57:K59) ——求K2到K56区域85~89.5分的人数;假设把结果存放于K60单元格;
(5)=COUNTIF(K2:K56,">=70")-SUM(K57:K60) ——求K2到K56区域70~84.5分的人数;假设把结果存放于K61单元格;
(6)=COUNTIF(K2:K56,">=60")-SUM(K57:K61) ——求K2到K56区域60~69.5分的人数;假设把结果存放于K62单元格;
(7) =COUNTIF(K2:K56,"<60") ——求K2到K56区域60分以下的人数;假设把结果存放于K63单元格;
说明:COUNTIF函数也可计算某一区域男、女生人数。
如:=COUNTIF(C2:C351,"男") ——求C2到C351区域(共350人)男性人数;
9、优秀率: =SUM(K57:K60)/55*100
10、及格率: =SUM(K57:K62)/55*100
11、标准差: =STDEV(K2:K56) ——求K2到K56区域(55人)的成绩波动情况(数值越小,说明该班学生间的成绩差异较小,反之,说明该班存在两极分化);
12、条件求和: =SUMIF(B2:B56,"男",K2:K56) ——假设B列存放学生的性别,K列存放学生的分数,则此函数返回的结果表示求该班男生的成绩之和;
13、 多条件求和: {=SUM(IF(C3:C322="男",IF(G3:G322=1,1,0)))} ——假设C列(C3:C322区域)存放学生的性别,G列(G3:G322区域)存放学生所在班级代码(1、2、3、4、5),则此函数返回的结果表示求 一班的男生人数;这是一个数组函数,输完后要按Ctrl+Shift+Enter组合键(产生“{……}”)。“{}”不能手工输入,只能用组合键产生。
14、根据出生日期自动计算周岁:=TRUNC((DAYS360(D3,NOW( )))/360,0)
———假设D列存放学生的出生日期,E列输入该函数后则产生该生的周岁。
15、在Word中三个小窍门:
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
来源/知乎
pal and i were having the wedding convo for the 18369363515th time during her 7 year relationship two in the morning and somehow it dfited onto some seance ish game involving a ring and a strand of hair that’ll tell you the sex of your future children (i was VERY skeptical until she told me of an example of how the ring wouldnt move for a gay man who doesnt plan on having kids) and she explained that the game was like ouija board and you wouldnt know what kind of consequences calling onto the unknown force would have on you personally (e.g. being possessed) and now all i can think about IS ouija board (and chutes n ladders, weirdly)
今天的绘本是我和女儿都非常喜欢的 《don't let the pigeon drive the bus》。作者Mo Willoms 是我很喜欢的美国作家,他的作品想象丰富但很生活化,互动性也很强。这本《别让鸽子开巴士》就是他的代表作。#跟着GPT看绘本##chatgpt#
Oh, look at this! We have a bright and cheeky pigeon who seems to be making a very unusual request! The pigeon is asking if he can drive a bus, which is very silly, isn't it? Birds don't drive buses, people do! The pigeon looks very eager and hopeful, with big wide eyes and a friendly wave. But the title of the book, "Don't Let the Pigeon Drive the Bus!" is a big hint that maybe letting pigeons drive isn't the best idea. I wonder what kind of funny adventures the pigeon will have in this story!
Here is how you say the difficult words and their Chinese definitions:
Pigeon (ˈpɪdʒ.ɪn): A type of bird that is often seen in cities. In Chinese, pigeon is 鸽子 (gē zi).
"Cheeky" (ˈtʃiː.ki) 这个词形容一个人或动物表现得非常自信,有时候可能有点不敬或顽皮,但通常是以一种可爱和有趣的方式。它可以用来形容那种敢于做出一些大胆行为或说出大胆话语的行为,让人忍俊不禁的那种调皮样。
在中文里,"cheeky" 可以翻译为调皮 (tiáo pí) 或者顽皮 (wán pí)。
用一句话来说,就是:
A cheeky child might make a funny face when they're told to eat their vegetables.
一个调皮的孩子可能在被告知吃蔬菜时会做个鬼脸。
Now, let's think about some questions:
Why do you think it's not a good idea to let the pigeon drive the bus?
你为什么认为让鸽子开公共汽车不是一个好主意?
What are some things that you are really good at, that the pigeon might not be?
你擅长做些什么,而鸽子可能不擅长呢?
If you could talk to the pigeon, what advice would you give him?
如果你可以和鸽子说话,你会给他什么建议?
Can you imagine a funny story about what might happen if the pigeon did drive the bus?
你能想象如果鸽子开了公共汽车,可能会发生什么有趣的事情吗?
Oh, look at this! We have a bright and cheeky pigeon who seems to be making a very unusual request! The pigeon is asking if he can drive a bus, which is very silly, isn't it? Birds don't drive buses, people do! The pigeon looks very eager and hopeful, with big wide eyes and a friendly wave. But the title of the book, "Don't Let the Pigeon Drive the Bus!" is a big hint that maybe letting pigeons drive isn't the best idea. I wonder what kind of funny adventures the pigeon will have in this story!
Here is how you say the difficult words and their Chinese definitions:
Pigeon (ˈpɪdʒ.ɪn): A type of bird that is often seen in cities. In Chinese, pigeon is 鸽子 (gē zi).
"Cheeky" (ˈtʃiː.ki) 这个词形容一个人或动物表现得非常自信,有时候可能有点不敬或顽皮,但通常是以一种可爱和有趣的方式。它可以用来形容那种敢于做出一些大胆行为或说出大胆话语的行为,让人忍俊不禁的那种调皮样。
在中文里,"cheeky" 可以翻译为调皮 (tiáo pí) 或者顽皮 (wán pí)。
用一句话来说,就是:
A cheeky child might make a funny face when they're told to eat their vegetables.
一个调皮的孩子可能在被告知吃蔬菜时会做个鬼脸。
Now, let's think about some questions:
Why do you think it's not a good idea to let the pigeon drive the bus?
你为什么认为让鸽子开公共汽车不是一个好主意?
What are some things that you are really good at, that the pigeon might not be?
你擅长做些什么,而鸽子可能不擅长呢?
If you could talk to the pigeon, what advice would you give him?
如果你可以和鸽子说话,你会给他什么建议?
Can you imagine a funny story about what might happen if the pigeon did drive the bus?
你能想象如果鸽子开了公共汽车,可能会发生什么有趣的事情吗?
✋热门推荐