EXCEL学习全技巧.ppt

,EXCEL学习小技巧,Excel简介,公式函数的应用,图表和图形的应用,Excel的基本操作, Excel简介,一 什么是Excel,相信我们很多人知道什么是Excel ，也知道怎么使用Excel ，在这里很多用户都会存在一个问题：Excel能够帮助我们做什么？ Excel并不是什么神秘之物，从本质上看，它就是个计算工具，与我们所熟知的算盘，电子计算器没什么区别，只不过功能更加强大而已。,二 Excel起源与历史,机械式计算器,二 Excel起源与历史,2.电子表格软件的产生与演变 2.1 1979年，美国人丹.布里克林（D.Briclin）和鲍勃.弗兰克斯顿（B.Frankston）在苹果型电脑上开发了一款名为“VisiCalc”（即“可视计算”）的商用应用软件，打破了现在计算工具因为缺乏合适软件而无法发挥硬件优势的这个牢笼。 2.2 微软从1982年特开始了电子表格软件的研发工作，经过数年的而改进，终于在1987年凭借着与windows 2.0捆绑的excel 2.0 后来居上。经过多个版本的升级，奠定了Excel在电子表格软件领域的霸主地位。,三 Excel的基本功能,E,商业图表制作, Excel的基本操作,一 工作窗口的基本操作,1.窗口的冻结与拆分 07版的冻结窗格与03版相比有些改动：首先选择要冻结行或列的下一行/列，再选择“视图”“窗口”“冻结拆分窗格”。,一 工作窗口的基本操作,2.设置下拉菜单 设置下拉菜单：首先选择工具栏中的数据项，“数据”“数据有效性”“设置”“序列（允许）”“来源”（例：差,（注英文状态下的逗号）及格,中,良好,优秀）确定。,一 工作窗口的基本操作,3.Excel下拉公式数据未变化 这是由于公式的设置出现问题，解决方法有：office按钮“Excel选项”“公式”“自动重算”确定。,一 工作窗口的基本操作,4.条件格式的设置 4.1 选定目标单元格或者区域。 4.2 单击“开始”“条件格式”“新建格式规则”，弹出“新建格式规则”对话框，,一 工作窗口的基本操作,4.条件格式的设置 4.3 在“新建格式规则”对话框中选择“规则类型”。 4.4 在“规则类型”的对话框中，加入具体的条件，条件设置也会有所不同，条件可以是常量、单元格引用或者公式。 4.5 设置完成后，确定。 4.6 例：,一 工作窗口的基本操作,4.6 方法：,二 单元格的操作,1 单元格输入格式 在单元格中可以输入和保存的数据主要包括四种类型：数值、日期、文本和公式，此外还有“逻辑值”、“错误值”等一些特殊的数据类型。 1.1 数值输入介绍 数值是Excel表格中用得最广泛的数据，其基本的操作相信大家都也熟悉，下面主要介绍几个不常用的输入方式： 1.1.1 保存和输入15位数字以上的整数数字 可以在单元格中先输入“”单引号再加上输入的数字，在单元格中数字就可以完全的显示出来。例如输入身份证号422326198710124953，就可以输入422326198710124953。,二 单元格的操作,1.1.2 实现输入常见的股票类型的数字，如01234 选中单元格“设置单元格样式” （ctrl+1）“数字”“自定义”，在自定义中输入与你需要输入数字相同位数的0，对于01234，就需要输入00000，确定后再于单元格中输入01234，按enter键即可。 1.1.3 实现分数的输入，如3/5 13/5等 在单元格输入分数的时候需要在其前面加上0，如0 3/5，得到的显示方式就是3/5,另外，Excel默认自动会除以最大公约数，如输入0 2/24，显示结果是1/12. 1.1.4 在单元格中输入不代表日期的时间如3-4天， 可以通过简单操作34天即可。,二 单元格的操作,1.2 日期和时间输入介绍 Excel中，日期和时间是以一种特殊的数值形式存储的，这种数值形式被称为“序列值”，下面简单介绍下一些日期和时间输入的技巧： 1.2.1 单元格允许输入最大的时间为：9999：59：59.9999. 1.2.2 Excel的日期系统存在一个小错误，1990年不是闰年，但微软为了实现Excel与其他软件的兼容性，所以保留这个错误。 1.2.3 介绍一个简单的显示当时时间的方法：=now（）。,二 单元格的操作,1.3 文本输入介绍 Excel中，文本通常是指一些非数值性的文字、符号等，例如企业的部门名称、学生的考试科目、个人的姓名性别等。下面简单介绍下文本输入的技巧： 1.3.1 文本自定义格式 条件格式：条件值1；条件值2；同时不满足条件值1，2的值；文本。例10.00,90G/通用格式;绿色5G/通用格式;=。,二 单元格的操作,1.4 公式输入介绍 公式是Excel中一种非常重要的数据，Excel作为一种电子数据表格，他许多强大的功能都是由公式来实现的。公式的强大功能将于第三章详细介绍，下面表格效果就是由函数公式实现的。,二 单元格的操作,2、单元格的处理 Excel强大的主要就体现在对单元格以及单元格区域的处理上，下面主要介绍几种操作技巧： 2.1 使用查找与替换功能（ctrl+H） * 代表任意数目的字符，可以使一个也可以是多个字符 ？ 代表着任何的单个字符 2.2 斜线表头的制作方法 2.2.1 单线表头 如果表头中只需要一条斜线，可以利用Excel的边框设置来画斜线，然后填入文字内容。在单元格内画斜线的方法如下。,二 单元格的操作,选定单元格，按组合键调出“单元格格式”对话框。在“单元格格式”对话框的“边框”选项卡中，在“边框”区中单击斜线按钮。画好斜线后在单元格内填充表头内容的方法有三种。 （1）使用文本框 单击菜单“插入”“文本框”“横排”（视情况也可以选择“竖排”），在单元格内连续插入两个文本框。 分别编辑两个文本框中的相关文字，并调整文本框大小和文字字号以相互适应。 选定文本框，单击菜单“格式”“文本框”，在“设置文本框格式”对话框的“颜色与线条”选项卡中，分别将“填充颜色”与“线条颜色”设置为“无”。设置文本框格式：用鼠标把两个文本框分别移动到画有斜线的单元格的合适位置。,二 单元格的操作,（2） 使用上下标 在画有斜线的单元格中输入文字。把不同部分的内容分别设置为上标或下标格式。根据需要，还可在不同部分之间插入适量的空格进行美化。 （3）使用空格进行中分 如果表头的项目文字都非常少，也可以直接输入文字，然后在不同部分之间插入足够的空格,二 单元格的操作,2.2.2 多斜线表头的制作方法 （1）选定单元格，设置足够的长度和高度； （2）单击工具栏“插入”“形状”“直线”工具，绘制两条或者多条直线到单元格中； （3）使用插入文本框的方式逐个添加表头。,二 单元格的操作,2.3 填充与序列 使用自动填充连续输入“甲”，“乙”，“丙”。“癸”: 2.3.1 在单元格中输入“甲”。 2.3.2 将鼠标移至填充柄处，当鼠标指针显示为黑色加号时，双击鼠标左键。,二 单元格的操作,2.4 自动填充不相邻单元格。,拆分,二 单元格的操作,2.4.1 首先拆分单元格，使各个省份单元格分开。 2.4.2 选中所有拆分的单元格，“查找与选择”“定位条件”“空值”确定 2.4.3 选中一格有文本的单元格如北京“ctrl+enter”,即可实现填充这些不相邻单元格功能。, 公式函数的应用,一 函数和公式简介,1 什么是函数和公式？ 公式就是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式，函数也是一种特殊的公式。Excel之所以在日常的工作中起到强大的作用，主要就是对函数公式的应用。 本章主要介绍公式与函数的基础知识，并深入讲解一些常用的工作表函数，主要包括信息与逻辑、文本、日期与时间、数学、统计、查找与引用等函数。,一 函数和公式简介,2.公式中的运算符,一 函数和公式简介,3.Excel使用公式时常出现的问题,二 函数和公式应用介绍,1.简单的公式输入 1.1 实现九九乘法表的制作 1.1.1 先在a列和第二行中通过下拉实现输入1-9数字 1.1.2 在单元格b2内输入：=b$1&”*”&$a$2&”=”&b$1*$a2，再分别下拉，即可实现以下效果。,二 函数和公式应用介绍,1.2.实现大小写字母表的生成 1.2.1 大写字母A,B,C,DZ,只需在单元格中输入=char（column（）+64）； 1.2.2 小写字母a,b,c,dz,由于a=A+32，即=char（column（）+96）；,二 函数和公式应用介绍,2.常用公式函数的介绍 2.1 IF函数 语法：IF(logical_test,value_if_true,value_if_false) 以下语句可以实现以下功能：=IF(K950,F,IF(K960,E,IF(K970,D,IF(K980,C,IF(K990,B,A),二 函数和公式应用介绍,2.2 常用提取字符函数 2.2.1 left（text，num_chars）从字符串中左端提取指定数量字符。 2.2.2 mid（text，start_num,num_chars）从字符串中间提取指定字符。 2.2.3 right（text，num_chars）从字符串中右端提取指定数量字符。 以congratulations为例，在a1单元格： =left（a1，4）得到cong； =mid（a1，4，4）得到grat； =mid（a1，3，len（a1）-3+1）得到ngratulations；,二 函数和公式应用介绍,2.3 取舍函数,二 函数和公式应用介绍,2.3 取舍函数（具体例子介绍） 2.3.1 int和trunc函数 =int（15.38）=15 =int（-8.72）=-9； =trunc（15.38）=15 =trunc（-8.72）=-8； =trunc（18.742，2）=18.74； 2 .3.2 roundup和rounddown函数 =roundup（123.456，2）=123.46 =rounddown（123.456，2）=123.45； 2.3.3 ceiling和floor函数 =ceiling（-123.456，0.2）=#num =ceiling（-123.456，-0.2）=-123.46；,二 函数和公式应用介绍,2.3 取舍函数（接上） =floor（-123.456，0.2）=#num =floor（-123.456，-0.2）=-123.4； 2.4 实现随机的排名（实现以下效果） 2.4.1 在l2单元格中输入=rand（）， 下拉至L13得到0-1之间不重复的数字； 2.4.2 在M2中输入=rank（l2，$l$2 :$l$13）,下拉即可得到不重复的随机排 名。,二 函数和公式应用介绍,2.5 Vlookup函数 语法：=vlookup（lookup_value,table_array,col_index_num,range_lookup）；,二 函数和公式应用介绍,2.5 Vlookup函数,二 函数和公式应用介绍,2.5 MOD函数 2.5.1 利用mod函数计算余数 =mod（19，5）=4； 2.5.2 利用mod函数提取身份证性别 =if（or（len（b2）=15，18),if(mod(mid(b2,17,1),2),“男”,”女”)，“ ”）； 2.6 统计带条件的数据 例如统计c列数据大于5，小于10的数据个数 2.6.1 =countif（c：c，“5”）-countif(c:c,”10”) 2.6.2 =sum(countif(c:c),“5”,”10”*(1,-1),二 函数和公式应用介绍,2.7 时间日期函数介绍 2.7.1 基本的日期，时间函数： today和now函数：用于生成当前日起和时间；=today（）=2010-11-24，=now（）=2010-11-24 13：59 date函数：根据指定的年份，月份和日期数返回具体的日期值； year、month、和day函数：勇于从日期数据中提取年份、月份和日期数；=month（today（）=12,2.7.1 基本的日期，时间函数： yearfrac函数：勇于计算两个日期序列相差的年数，结果以实数显示； edate函数：根据指定月份数返回指定日期之前或者之后的日期值； emonth函数：从任意一个日期返回指定月份数之前或者之后的月末日期。,二 函数和公式应用介绍, 图表和图形的应用,一 图表和图形简介,1.图表图形的作用 在Excel中，图表具有直观形象的优点，可以方便用户查看数据的差异，比如我们工作中经常会用到的数据透视表，图形能增强工作表或图表的视觉效果，创建出引人注目的报表。 结合Excel的的函数公式、名称、窗体控件等功能，还可以创建实时变化的动态图表。 Excel提供了丰富的图表类型，以及流程图和组织图等常用图示。,一 图表和图形简介,图表最大的特点就是直观形象，世人一目了然的看清数据的大小、差异和变化趋势。,Excel图表包括14种标准图表类型和20种内置自定义图表类型，其中每一种标准图表类型还包括不少子图表类型。,双向联动是指图表会自动随数据变化而变化，反之数据也可随图标的变化而变化。,Excel图表虽然提供了一些三维图表类型，但从实际角度来看，其实质还是二维平面坐标系下建立的图表。,二 图表和图形应用介绍,1.数据透视表的操作 1.1 先选定需要透视的数据区域,二 图表和图形应用介绍,1.2 “插入”“数据透视表”“数据透视表（T）”选择需要透视的区域和透视的位置。,二 图表和图形应用介绍,1.3 根据要求选择不同的字段，透视出自己需要的结果。,二 图表和图形应用介绍,1.4 下图就是根据省份，月份透视出来的各省份在各月份的物流费用和物流费率。,二 图表和图形应用介绍,1.5 再根据自己的要求，将透视表美化,二 图表和图形应用介绍,2.图形的基本操作 2.1 先选定需要画图的数据区域,二 图表和图形应用介绍,2.2 “插入”“图表”“柱形图”二维柱形图”，即可得到以下效果。,