excel公式大全教学文案.docx
- 文档编号:11841336
- 上传时间:2023-04-05
- 格式:DOCX
- 页数:8
- 大小:57.62KB
excel公式大全教学文案.docx
《excel公式大全教学文案.docx》由会员分享,可在线阅读,更多相关《excel公式大全教学文案.docx(8页珍藏版)》请在冰豆网上搜索。
excel公式大全教学文案
excel公式大全
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)——对K2K56这一区域求平均数;
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中三个小窍门:
①连续输入三个“~”可得一条波浪线。
②连续输入三个“-”可得一条直线。
连续输入三个“=”可得一条双直线。
一、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色比如:
A1〉1时,C1显示红色
0 A1<0时,C1显示黄色 方法如下: 1、单元击C1单元格,点“格式”>“条件格式”,条件1设为: 公式=A1=1 2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。 条件2设为: 公式=AND(A1>0,A1<1) 3、点“格式”->“字体”->“颜色”,点击绿色后点“确定”。 条件3设为: 公式=A1<0 点“格式”->“字体”->“颜色”,点击黄色后点“确定”。 4、三个条件设定好后,点“确定”即出。 二、EXCEL中如何控制每列数据的长度并避免重复录入 1、用数据有效性定义数据长度。 用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置","有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。 还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"。 2、用条件格式避免重复。 选定A列,点"格式"->"条件格式",将条件设成“公式=COUNTIF($A: $A,$A1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"。 这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。 三、在EXCEL中如何把B列与A列不同之处标识出来? (一)、如果是要求A、B两列的同一行数据相比较: 假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: “单元格数值”“不等于”=B2 点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 (二)、如果是A列与B列整体比较(即相同数据不在同一行): 假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为: “公式”=COUNTIF($B: $B,$A2)=0 点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。 用格式刷将A2单元格的条件格式向下复制。 B列可参照此方法设置。 按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。 四、EXCEL中怎样批量地处理按行排序 假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作? 由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。 所以,这一问题不能用排序来解决。 解决方法如下: 1、假定你的数据在A至E列,请在F1单元格输入公式: =LARGE($A1: $E1,COLUMN(A1)) 用填充柄将公式向右向下复制到相应范围。 你原有数据将按行从大到小排序出现在F至J列。 如有需要可用“选择性粘贴/数值”复制到其他地方。 注: 第1步的公式可根据你的实际情况(数据范围)作相应的修改。 如果要从小到大排序,公式改为: =SMALL($A1: $E1,COLUMN(A1)) 五、巧用函数组合进行多条件的计数统计 例: 第一行为表头,A列是“姓名”,B列是“班级”,C列是“语文成绩”,D列是“录取结果”,现在要统计“班级”为“二”,“语文成绩”大于等于104,“录取结果”为“重本”的人数。 统计结果存放在本工作表的其他列。 公式如下: =SUM(IF((B2: B9999="二")*(C2: C9999>=104)*(D2: D9999="重本"),1,0)) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。 六、如何判断单元格里是否包含指定文本? 假定对A1单元格进行判断有无"指定文本",以下任一公式均可: =IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无") =IF(ISERROR(FIND("指定文本",A1,1)),"无","有") 求某一区域内不重复的数据个数 例如求A1: A100范围内不重复数据的个数,某个数重复多次出现只算一个。 有两种计算方法: 一是利用数组公式: =SUM(1/COUNTIF(A1: A100,A1: A100)) 输入完公式后按Ctrl+Shift+Enter键,让它自动加上数组公式符号"{}"。 二是利用乘积求和函数: =SUMPRODUCT(1/COUNTIF(A1: A100,A1: A100)) 七、一个工作薄中有许多工作表如何快速整理出一个目录工作表 1、用宏3.0取出各工作表的名称,方法: Ctrl+F3出现自定义名称对话框,取名为X,在“引用位置”框中输入: =MID(GET.WORKBOOK (1),FIND("]",GET.WORKBOOK (1))+1,100) 确定 2、用HYPERLINK函数批量插入连接,方法: 在目录工作表(一般为第一个sheet)的A2单元格输入公式: =HYPERLINK("#'"&INDEX(X,ROW())&"'! A1",INDEX(X,ROW())) 将公式向下填充,直到出错为止,目录就生成了。 Excel攻略 一排版篇 给他人发送excel前,请尽量将光标定位在需要他人首先阅览的位置。 有必要的时候请冻结首行;没必要但可追究的内容,可以隐藏处理。 行标题、列标题加粗,适当处理文字颜色、填充颜色,利人利己。 占用空间比较小的表格,可以放置在左上角,但留空A列和1行,并给表格加上合适的框线,同类型数据的行高、列宽、字体、字号,求你尽量一致,定义好比较标准的格式,例如百分比预留几位小数,手机号的列宽设置足够,时间显示尽量本土化,不要设置其他电脑没有的字体,除非这个表格就在这一台电脑使用。 如果这样做,你的老板会记得你的。 二操作篇 Alt+Enter在表格内换行。 Ctrl+Shift+上/下,选择该列所有数据,当然加上左右可选择多列 Ctrl+上/下,跳至表格最下方 Ctrl+D/R,复制上行数据/左列数据 Ctrl+F/H的查找、替换,点击“选项”,可以替换某种格式等等! F4重复上一步操作,按F4轮流切换绝对引用,例如A1,$A$1,$A1,A$1 三公式篇 if、countif、sumif、countifs、sumifs,这几个一起学,用于条件计数、条件求和 max、min、large,这几个一起,用于简单的数据分析 rand、randbetween,这俩一起,用于生成随机数,也可以用于生成随机密码(用rand配合char可生成中英文大小写随机的) 定位类型的函数: MID、SEARCH、LEN、LEFT、RIGHT一起学吧,简单但异常实用 四舍五入个人偏好用round函数,举个简单例子,一列数据,2.04、2.03并求和,显示保留1位小数,你会在界面上看到2.0、2.0,求和却是4.1,表格打印出来会比较让人难理解 subtotal: 用于对过滤后的数据进行汇总分析 sumproduct: 返回一个区域的乘积之和,不用A1*B1之后再下拉再求和 Vlookup函数,这个不多说了,神器;另外推荐lookup函数: LOOKUP(1,0/(条件),查找数组或区域) offset函数,常用于配合其他函数使用,例如想将10*20的表中的每行复制成3行按原顺序变成30行: =OFFSET($A$1,INT((ROW(A1)-1)/3),COLUMN(A1)-1,1,1)下拉,由于不用到列,所以等同于=OFFSET($A$1,INT((ROW(A1)-1)/3),0),我当初是这么做笔记的....: =(A1,向下偏移(向下取整(行数-1)/3),向右偏移0) text,例如19880110text(A1,"0-00-00"),转为1988-01-10,用法很多 weekday,让你做时间计划表什么的时候,把日期转为“星期X” column(目标单元格),返回目标单元格所在列数,有时候真的很好用...还有columnstranspose(目标区域),神奇的转置,把行变成列,把列变成行... &,可在目标单元格后面增加某些字符 数组,虽然复杂,但是有的公式配上数组简直爽爆 多XX,例如曾经碰到一个难题,把X分X秒,转为X秒,例如172分52秒,XX半天得到的公式: =IF(IFERROR(FIND("分",$E2),0)>0,LEFT($E2,FIND("分",$E2)-1)*60+IFERROR(MID($E2,FIND("分",$E2)+1,FIND("秒",$E2)-FIND("分",$E2)-1),0),LEFT($E2,FIND("秒",$E2)-1)*1)度娘很厉害的 四图表篇 不同的场景请用不同的图 图表设计——布局,灵活运用好多类数据时的“次坐标轴” 选择数据——右键——更改图标类型,灵活在一张表上结合起来柱状图和折线图 五技巧篇 数据——分列,将列内的数据拆分成多列,比如“XXX省XXX市”,拆成省、市两列,“XX小时XX分钟”拆成时、分两列,可以按照宽度、文本、标点等作为界定进行拆分,非常多的场景会使用到,请优先学会... 如果你不是靠excel吃饭,请不用那么geek,而是学会excel的逻辑——配合简单的公式、排序、替换、if等全局操作能得出的结果,不一定非要用一个长公式然后下拉,举例: 如何将无规律的一列上下翻转? ——创建一列,标上1、2、3……,下拉,以该列为主排序,改升序为降序,扩展目标列,得到结果,之后可以删掉创建的辅助排序列 如何将目标区域的每一行数据下面插入一条空行? ——创建一列,标上1、2、3……,下拉,下面空白行标上1.5、2.5、3.5……下拉,同理排序 条件格式——突出显示单元格规则,里面的“重复值”,在实时录入和检查标记时很实用 在条件允许的情况下,升级到office2013吧,excel2013比2010好到爆啊! 比如新增的sumifs、averageifs等多条件if,比如选择一个区域,右下角小标“快速分析”自动生成数据条、色阶、柱形图、汇总图、透视表、折线图等等 六插件篇 PowerMap: 在线地图+在线演示+制作视频,随便来个中国壕热力图: 当然,也有柱状图: PowerView: 带可视化交互效果的图表,很适合演示 看完这篇Excel攻略,你会感觉这么多年的excel都白学了! 默认配色就很不错,而且演示的时候点击时会直接按你点击的类型帮你显示对应的数据(例如上面的堆积柱状图) PowerQuery: 这个用法很多,我主要用于以下两点: 在线Web抓取: 不需要学会某个编程语言也能爬虫+分析一些简单数据,随便举个例子输入新浪股票的网址,它自动帮忙抓取到N个表,我随便打开一个: 连接数据库: 不需要学会SQL语法也能查询+分析数据库内的数据,这个就不方便截图了... 打比方说,常见的select*from...wherexxx=xxxandxxx>xxxgroupbyxxx这种sql语法查询的内容,可以在PowerQuery中直接通过点击、筛选等操作就列出来 各种excel工具箱,这个不多介绍了,不常用,也就不打广告了,但是挺适合部分长期使用excel的职场人士使用 SmartArt也是一大神器,我终于不用在Ai或者PPT上作图再粘过来了... 七其它篇 不会写宏没关系,要懂得怎么使用别人的宏(自行XX“excel宏大全”吧~),怎么保存xlsm,怎么录制宏。 当你把机械化的一套操作通过录制宏实现,并用xlsm配合auto_open自动操作,眼看表格自动化操作,在两秒内给你返回原来每天固定要做十几分钟的数据分析结果时,那个鸡皮疙瘩……excel满足不了你,又懂编程,想秀逼格的,请右转XXSPSS
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 公式 大全 教学 文案