EXCEL技巧提高研究

EXCEL技巧提高研究目录数据输入与工作表处理数据分析与管理函数与公式高级图表目录数据输入与工作表处理数据输入技巧定义名称引用数据有效性自定义格式工作表处理数据输入与工作表处理数据输入技巧录入文本格式数字（身份证号码、等）输入当前日期、时间：Ctrl+;和Ctrl+Shift+;格式化文本数据导入不连续区域的填充：定位、Ctrl+Enter快速输入重复出现列表快速复制、移动单元格单元格中输入多行数据（自动与强制）填充柄录入（序列填充、双击鼠标左键）自定义数据序列数据输入与工作表处理引用

公式所在单元格与公式所引用单元格的位置关系称为单元格引用的相对性。A1样式特征$A$1绝对引用，向右向下复制公式不改变引用关系A$1行绝对列相对混合引用，向下复制公式不改变引用关系$A1行相对列绝对混合引用，向右复制公式不改变引用关系A1相对引用，向右向下复制公式均改变引用关系有钱能使鬼推磨，给“$”的就不动了神奇的“F4”数据输入与工作表处理定义名称1、“公式”-“定义名称”2、使用名称框命名3、名称管理器数据输入与工作表处理数据有效性利用数据菜单中的有效性功能可以控制一个范围内的数据类型、范围等，还可以快速、准确地输入一些数据。比如录入身份证号码、手机号这些数据长，数量多的数据，操作过程中容易出错，数据有效性可以帮助防止、避免错误的发生。数据输入与工作表处理数据有效性数据有效性还可以应用于时间、日期、整数、小数等的检查控制；

数据有效性的出错信息：数据有效性的出错信息包括“停止”，“警告”，“信息”。数据输入与工作表处理数据有效性通过数据有效性还可以设置下拉菜单a.选定要设定下拉菜单的列。b.选择菜单栏里的“数据”->“有效性“，打开数据有效性对话框，设置有效性条件为“序列”，在“来源”里输入要限制输入的值，逗号输入时要切换到英文输入模式。c.思考：复杂序列如何制作？数据输入与工作表处理自定义格式输入身份证的前几位“身份证号码："输入固定位数数字，不足位数前补足数字“0”自定义日期格式1、直接输入数字格式2、通过数值格式窗设定格式3、通过数字选项设置数据输入与工作表处理工作表处理冻结（窗格）加密、备份（文件）数据输入与工作表处理工作表处理保护（单元格）隐藏（公式、行&列、窗口、工作表）数据输入与工作表处理数据输入技巧定义名称引用数据有效性自定义格式工作表处理？小结1目录数据输入与工作表处理数据分析与管理函数与公式高级图表应用目录数据分析与管理数据排序与筛选图形化数据分析合并计算分类汇总数据透视表数据分析与管理数据排序排序时尽量不要选择数据范围，以免破坏数据的结构对多列进行排列，使用排序对话框进行相应操作数据分析与管理数据筛选自动筛选（数据、颜色）高级筛选

条件区域的设置原则：

1、工作表中指定一个空白区域作条件区域

2、条件区域第一行是条件名（字段名）行，第二行是输入的条件

3、条件区域中不能包含有空行或空列

4、字符字段的条件可以使用通配符*和?注意：在具体写条件时，要分析条件之间是“与”还是“或”关系，若

是“与”条件要写到同一行中，若是“或”条件要写到不同行中。数据分析与管理图形化数据分析试一试“迷你图”数据分析与管理合并计算数据分析与管理分类汇总数据分析与管理数据透视表小结2数据分析与管理数据排序与筛选图形化数据分析合并计算分类汇总数据透视表？目录数据输入与工作表处理数据分析与管理函数与公式高级图表应用目录函数与公式函数与公式解读常见错误值及含义文本处理函数数字处理函数日期与时间函数常用函数序号公式说明1=26*7+87*9包含常量运算的公式2=A1*3+A2*7包含单元格引用的公式3=单价*数量包含名称的公式4=SUM(A1*3,A2*7)包含函数的公式函数与公式什么是函数？什么是公式？函数是按照特定的算法执行计算的产生一个或一组结果的预定义的特殊公式。公式是以“=”为引导，通过运算符按照一定的顺序组合进行数据运算处理的等式。=IF(A1>0,SUM(B1:G1),””)函数的结构嵌套函数函数名称以逗号分隔的3个参数函数与公式函数参数解读错误值类型含义#####当列宽不够显示数字，或者使用了负的日期或负的时间时，出现错误#VALUE!当使用的参数或操作数类型错误时，出现错误#DIV/0!当数字被零（0）除时，出现错误#NAME?当Excel未识别公式中的文本时，如未加载宏或定义名称，出现错误#N/A当数值对函数或公式不可用时，出现错误#REF!当单元格引用无效时，出现错误#NUM!公式或函数中使用无效数字值时，出现错误#NULL!当用空格表示两个引用单元格之间的相交运算符，但指定并不相交的两个区域的交点时，出现错误。函数与公式常见的错误值及含义A列B列C列ABX500120-E=A1&B1ABX500120-EABX500120-EA列B列ABX500120=A1&"-E"ABX500120ABX500120-E思考：如何把零件号后面的“-E”去掉？函数与公式文本处理函数1、连接符“&”，用途：连接“&”前后的字符、字段函数与公式文本处理函数2、提取字符Left、Right、Mid注意：MID函数有3个参数，而LEFT、RIGHT只有2个参数身份证号码地区出生日期性别函数=left(a1,6)=mid(a1,7,6)=right(a1,1)3201027810014923201027810012员工代码社保号姓名函数=left(a1,10)=mid(a1,11,3)0002337095李明0002337095李明0000656246李小明0000656246李小明为什么是3而不是2？为什么不用Right函数？函数与公式文本处理函数3、转码函数Char、Code生成A-ZChar(65)=AChar(66)=BChar(67)=C……Char(97)=aChar(98)=bChar(99)=cCode(“A”)=65思考：如何快速填充A-Z序号所有大写字母转为小写字母所有小写字母转为大写字母字符串首字母转为大写字母=LOWER(“ILOVEEXCEL”)=“iloveexcel”=UPPER(“iloveexcel”)=“ILOVEEXCEL”=Proper(“I爱excel”)=“I爱Excel”函数与公式文本处理函数4、大小写字母转换Lower、Upper、Proper函数与公式日期与时间函数1、四个基础日期函数特殊函数：Today()，读取计算机设置的今日日期，无参数日期年月日函数=Year(a1)=Month(a1)=Day(a1)2014-9-9201499年月日日期=DATE(A1,B1,C1)2014992014-9-9函数与公式日期与时间函数2、Datedif函数，用途：计算两日期之间的天、月或年数unit代码函数返回值"y"时间段中的整年数"m"时间段中的整月数"d"时间段中的天数"md"忽略日期中的年和月，计算天数差"ym"忽略日期中的年和日，计算月数差"yd"忽略日期中的年，计算天数差注：此函数在Excel中无帮助文件，结构为：Datedif(start_date,end_date,unit)函数与公式日期与时间函数2、Datedif函数示例开始日期结束日期公式显示结果2000-6-152009-6-16=datedif(A2,B2,"y")92000-6-172009-6-16=datedif(A3,B3,"y")82008-6-152009-6-16=datedif(A4,B4,"m")122008-6-172009-6-16=datedif(A5,B5,"m")112008-6-152009-6-16=datedif(A6,B6,"d")3662008-6-172009-6-16=datedif(A7,B7,"d")3642008-3-152009-6-16=datedif(A8,B8,"md")12008-3-172009-6-16=datedif(A9,B9,"md")302008-3-152009-6-16=datedif(A10,B10,"ym")32008-6-172009-6-16=datedif(A11,B11,"ym")112008-6-152009-6-16=datedif(A12,B12,"yd")12008-6-172009-6-16=datedif(A13,B13,"yd")364函数与公式数字处理函数1、计算余数、长度Mod、LenMod(3,2)=1Mod(15,4)=3Mod(21,7)=0……Len(“学号123456”)=8Lenb(“学号123456”)=10应用：身份证号判断性别？如何判断数字的奇偶性？思考：除2的余数有多少个？除3呢？函数与公式数字处理函数2、四舍五入函数Round数值公式显示结果123.456=round(A2,0)123123.456=round(A3,2)123.461234.56=round(A4,-2)1200函数与公式数字处理函数3、向上取整Roundup、向下取整Rounddown数值RounddownRoundup公式显示结果公式显示结果123.456=rounddown(A3,0)123=roundup(A3,0)124123.456=rounddown(A3,2)123.45=roundup(A3,2)123.461234.56=rounddown(A3,-2)1200=roundup(A3,-2)1300提示区单击函数与公式看懂公式帮助，境界提升函数与公式看懂公式帮助，境界提升其实看帮助没有我们想象的那么难，还有实例呢！函数与公式常用函数1、Vlookup\hlookup：不会vlookup,不要说自己精通excel进行两个数的数据匹配，将一个表的信息对应到另一个表中2、If:让你更加有逻辑

IF(C2>100%，“达成”，“未达成”），如果单元格C2大于100%，则显示“达成”3、Find/Search/Match:无论你在哪，我都能找到你的位置查找某个数据在区域中的相对位置4、rank:电脑排名，错不了

计算一个数值在一组数值中的排名，可以正着排，也可以反着排函数与公式常用函数5、Sum\sumif\sumifs\sumproduct：变着花样求和

sumif单条件求和：sumifs象英文里面的复数，那就是多条件求和6、count\countif\countifs：sum的兄弟函数，变着花样计数7、value\text:单元格格式随你设

例：A1=2.3%，则公式=TEXT(A1，“00.00%”)返回02.30%8、Row\column：留给大家自己去核实吧小结3函数与公式函数与公式解读常见错误值及含义文本处理函数数字处理函数日期与时间函数常用函数？目录数据输入与工作表处理数据分析与管理函数与公式高级图表应用目录高级图表应用认识图表图表类型专业图表制作常用图表类型高级图表应用认识图表图表是用于数据比较的。用图表传输信息关键在于选择正确的图表类型常用比较包括：项目之间比较例如：比较某公司的每个销售地区的销售额按时间比较数据例如：用显示月销售额并指出按时间变化趋势。作相对比较：例如：饼图扇区说明相对值。比较数据关系：例如：用XY散点图显示销售成本和销售额之间关系频率比较例如：直方图用于显示某一特殊分数范围的学生数发现“特殊”情况例如：数千个数据点，图表有助于识别没有代表性的数据。高级图表应用认识图表-8项要素1、常见的6项要素图表标题坐标轴Y坐标轴X图例绘图区图表区数据系列高级图表应用认识图表-8项要素2、数据表要素数据表高级图表应用认识图表-8项要素3、三维背景要素（三维背景由基座和背景墙组成）三维背景高级图表应用图表类型柱形图－－－－－－（19个）折线图－－－－－－（7个）饼图－－－－－－（6个）条形图－－－－－－（15个）面积图－－－－－－（6个）XY散点图－－－－－（5个）股价图－－－－－－（4个）曲面图－－－－－－（4个）圆环图－－－－－－（2个）气泡图－－－－－－（2个）雷达图－－－－－－（3个）Excel2010共有73个子类型的图表类型。高级图表应用专业图表制作常见的专业图表高级图表应用专业图表制作专业图表的特点给我10页纸的报告，必须有9页是数据和图表分析，还有1页是封面;《财经》杂志招聘图表编辑的要求：精通Illustrator、Freehand、Coreldraw、Photoshop、3Dmax等图形、图像处理软件，能制作专业水平的商业图表。1.专业的外观他们的图表都制作精良，显得专业协调。我们或许不知道它专业在哪里，但有一点可以确定，那就是在他们的图表中很少能看到Office软件中默认的颜色、字体和布局。而这些都正是构成专业外观的重要方面。高级图表应用专业图表制作专业图表的特点2.简洁的类型他们的图表都只使用一些最基本的图表类型，绝不复杂。不需要多余的解释，任何人都能看懂图表的意思，真正起到了图表的沟通作用。3.明确的观点他们在图表的标题中明白无误地直陈观点，不需要读者再去猜测制图者的意思，确保信息传递的高效率，不会出现偏差。高级图表应用专业图表制作专业图表的特点4.完美的细节请注意他们对每一个图表元素的处理，几乎达到完美的程度。一丝不苟之中透露出百分百的严谨，好像这不是一份图表，而是一件艺术品。在很大程度上，正是这些无微不至的细节处理，才体现出图表的专业性。而这往往是我们普通图表不会注意到的地方。高级图表应用专业图表制作如何制作专业图表高级图表应用专业图表制作如何制作专业图表1.使用专业品质的配色一幅图表给人的印象，很大程度上是由其配色决定的。专业的图表往往是因为它具有专业品质的配色，通过配色透露出一种专业精神。普通用户制作图表，往往会受到Excel默认颜色的限制。而默认颜色要么糟糕不已，要么因长期使用而审美疲劳。推荐拾色软件：ColorPix系统