Excel基础介绍分析

EXCEL基础知识介绍如何用好EXCEL？学习各种函数？掌握各种功能技巧？要有数学天赋？2Excel用户水平的5个层次3刚刚开始接触基本掌握常用功能熟练使用常用功能+数据透视表+公式…+数组公式+VBA简单编程Excel技能+专业知识+行业经验新手初级用户中级用户高级用户专家课程目的了解Excel的基本知识熟悉Excel的常用技巧熟悉Excel常用图表的做法熟悉Excel的常用函数数据透视表初探4课程安排Excel基本知识介绍Excel的常用技巧介绍Excel常用图表的做法Excel的常用函数介绍数据透视表初探5Excel基本知识介绍EXCEL基本知识介绍6大小一共有256列及65536行。列用字母序列表示，从A到IV，行用阿拉伯数字表示，从1到65536。如果我们在公式中输入的行或者列大于或者小于上述范围，系统将报错为“#NAME?”（比如=A88888）或者“#REF!”（比如=INDIRECT(a-10)）。【应用说明】：在进行范围引用的时候，应注意Excel的大小。勿摆乌龙！EXCEL基本知识介绍7单元格表示方法A1式：系统默认的方式，即列行式。在书写的时候，列用字母表示，大小写都行，行用数字表示。R1C1式：即，先引用行，再引用列。在这种表示方法下，行和列均用数字表示。EXCEL基本知识介绍8函数概念：函数指Excel自带的公式或者参数。结构：函数名(参数……)使用方法：函数必须运用于公式，比如，=SUM(1,2)，或者=SUM(1,MAX(1,2,3,4))函数大小写均可，但必须用英文半角状态输入。任何函数，都有完整的双括弧EXCEL基本知识介绍9公式概念：为达成某一结果，利用运算符号将表达式、函数或（及）数值连接起来并进行运算的表达式。结构：=表达式特点：公式常用到函数公式代替人工使得数据处理效率更好。特别说明：三键公式：指同时按下Ctrl、Shift和Enter的公式，为数组公式，使得表达式更精简。比如，我们将AB两列的前四行分别相乘后求和，一般公式写法为=SUM(A1*B1,A2*B2,A3*B3,A4*B4)，然后回车。三键公式为=SUM(A1:A4*B1:B4)，三键。EXCEL基本知识介绍10数据类型数值型：概念：可以用于算术计算的数据。包括常说的“数字”、日期型、时间性和逻辑值，类型为1，默认为右对齐.限制：Excel只能精确15位有效数字，超过部分以0表示，如果是小数则直接去掉数值过大或者过小，Excel会用科学计数法表示（字母E）单元格可以输入的最值：a最大的数值：9.99999999999999E+307b最大的正数：1.79769313486231E+308c最小的正数：2.229E-308d最大的负数：-2.250738585073E-308e最小的负数：-1.79769313486231E+308【小提示】：用type函数可以知道一个值的数值类型。EXCEL基本知识介绍11数据类型日期和时间型：概念：日期和时间以一种特殊的数值形式存储，实际上，被叫做序列值。该值大于等于0，小于等于2958466。所以，实际上，日期和时间的函数类型为1。限制：不能输入小于系统默认值的日期：1111年11月11日，这样将不会被识别为日期。允许的最大的时间为9999:59:59.9999说明：Excel对于时间的计算基本单位为天。将1小时算成1/24天。输入时间的方法建议为：2011/12/1314:15:16EXCEL基本知识介绍12数据类型文本型：概念：常说的文本和文本型的数值。函数类型为2。默认为左对齐。限制：不能参与计算，但是可以比较大小。说明：身份证号码，应该设置为文本型。EXCEL基本知识介绍13数据类型公式型：概念：即各种公式。函数类型为16。限制：公式最大长度为1024个字符。课程安排Excel基本知识介绍Excel的常用操作介绍Excel常用图表的做法Excel的常用函数介绍数据透视表初探14Excel的常用技巧介绍遇到的问题如何制作多选/单选按钮？如何限制单元格的字数？如何制作下拉菜单？输入数字后，如何自动产生固定的单位？Excel与Word(邮件合并功能）15工作实例16文化中心的需求——字数限制在信息录入的运用——提高人员信息录入准确率表单制作的需求——选择框、下拉框的运用常用技巧——自定义格式的运用17格式示例显示结果(0##)########-####2.08608E+13(020)86078888-8232手机号[phone]手机号[phone]y年m月2011-11-1111年11月y年m月d日2011-11-1111年11月11日ss秒-mm分-h小时2011-11-914:1842秒-18分-14时描述性质正数;负数;零;文本1正数密码式样******2541454****************************自定义格式电话号码日期和时间单元格设置基本方法，单击菜单栏中的“格式”，选择“单元格”。示例身份证号码限制要求做法无重复=COUNTIF(C:C,C3)2只能为数字=TYPE(C3)=1长度为18位=LEN(C3)=18生日不能大于当前=DATE(--MID(C3,7,4),--MID(C3,11,2),--MID(C3,13,2))=TODAY()年份为1950以后=AND(--MID(C3,7,4)1949,--MID(C3,7,4)year(today())+1)月份输入无误=AND(--MID(C3,11,2)0,--MID(C3,11,2)13)18单元格输入限制很多时候，我们需要对单元格输入进行限制，典型的如员工身份证不能重复、姓名栏只能为文本、手机号码必须为11位方法：打开“数据”→“有效性”，在里面进行设置即可根据实际情况进行设定，有利于提高数据质量工作操作——为区域命名19方法：方法一：选中需要命名的区域，在名称栏中直接书写方法二：单击“插入”→“名称”，然后设置。好处：便于写公式工作表操作——定位20方法：直接按F5用处：想要快速选择某一单元格的时候想要找到某些单元格的时候例子：快速生成“111222333444”这样的序列快速清除表格中的数字，但保留公式。工作表操作21隐藏工作表隐藏：方法一：选择工作表，然后单击“格式”→“工作表”，选额“隐藏”方法二：打开VB编辑器，写入以下代码：PrivateSubWorkbook_Open()Sheet2.Visible=xlSheetHiddenEndSub行或者列隐藏：选择需要隐藏的行或者列，右键，“隐藏”工作表操作22Q&A课程安排Excel基本知识介绍Excel的常用技巧介绍Excel常用图表的做法Excel常用图表介绍数据透视表初探23Excel的常用函数介绍应用实例文本函数在档案管理的运用津贴计算的综合运用24EXCEL常用函数介绍25文本函数查找与引用函数日期函数数学与三角函数信息函数文本函数查找与引用函数日期函数数学与三角函数信息函数EXCEL常用函数介绍26文本函数函数名：Mid作用：从指定位置开始获取指定长度的字符串函数结果：结果为文本型。函数结构及解释：字符串，即数据源。起始位置。截取长度实例：=MID(“课程名：Excel123”,5,5)的计算结果为Excel=MID(“123456”,1,1)的计算结果为1EXCEL常用函数介绍27文本函数函数名：Left作用：从文本的左边第一位开始提取指定长度的字符串函数结果：结果为文本型。函数结构及解释：字符串，即数据源。截取长度实例：=LEFT(“课程名：Excel123”,3)的计算结果为课程名=LEFT(“123456”,3)的计算结果为123Right函数写法相同，取值位置为从右往左取~EXCEL常用函数介绍28文本函数函数名：Find作用：在母字符串中，从指定位置开始，找到某字符串首先出现的位置。函数结果：结果为文本型。函数结构及解释：FIND(find_text,within_text,start_num)需要查找的字符串母字符串实例：=FIND(“加薪”,“我要加薪，我要加薪！”,3)结果为3=FIND(“加薪”,“我要加薪，我要加薪！”,4)结果为8Find本身没有什么实际用处，主要是用于嵌套函数中进行字段截取或者实现其他功能。从哪个位置开始找？EXCEL常用函数介绍29文本函数函数名：Substitute作用：从文本的左边第一位开始提取指定长度的字符串函数结果：结果为文本型。函数结构及解释：SUBSTITUTE(text,old_text,new_text,instance_num)母字符串需要替换的字符串需要替换为的字符串替换第几个？原文本公式结果我要+薪+薪+薪+薪=SUBSTITUTE(A2,+,加,3)我要+薪+薪加薪+薪我要+薪+薪+薪+薪=SUBSTITUTE(A3,+,加,1)我要加薪+薪+薪+薪我要+薪+薪+薪+薪=SUBSTITUTE(A3,+,加)我要加薪加薪加薪加薪最后一个参数省略表示全部替换。SUBSTITUE函数妙用：计算指定字符串在某字符串中出现的次数30通用公式：=(LEN(母字符串)-LEN(SUBSTITUTE(母字符串,“需要计算的字符串,)))/LEN(需要计算的字符串)单字符公式：=LEN(母字符串)-LEN(SUBSTITUTE(母字符串,“需要计算的字符串,))案例：文本函数小结练习311.请提取出你身份证号中的生日，并将其替换为“我的生日”~2.计算你身份证号码中，0-9中哪个数字出现次数最多？EXCEL常用函数介绍32文本函数查找与引用函数日期函数数学与三角函数信息函数数据库函数EXCEL常用函数介绍33查找与引用函数VLOOKUPHLOOKUPINDEX+MATCHINDIRECTEXCEL常用函数介绍34日期函数YEARMONTHDAYDATEDIFTODAYNOWWEEKDAYEXCEL常用函数介绍35数学与三角函数SUMIFSUMPRODUCTCOUNTCOUNTIFCOUNTAPRODUCTSUMSQCOUNTBLANK课程安排Excel基本知识介绍Excel的常用操作介绍Excel常用函数介绍Excel常用图表的做法数据透视表初探Excel的常用函数介绍36Excel常用图表的做法常见图表做法姓名5月6月7月Terry24.15万24.54万65.23万Nina64.55万39.49万70.64万Lee29.97万65.01万26.91万Joy35.20万45.55万26.39万37基础数据：直方图二季度部门人员销售对比图28.0966.3950.9634.5863.0835.1221.5531.1420.7253.8251.4541.390万20万40万60万80万100万120万140万160万180万TerryNinaLeeJoy5月6月7月38堆积柱状图好处：可以看到构成总数的每一部分的明细。但对比不如饼图直观。饼图5月销售构成Joy,43.00万,25%Terry,70.34万,40%Lee,24.34万,14%Nina,36.84万,21%39分离性饼图好处：可以看到构成总数的每一部分的明细和对比。折线图Terry日销售折线图0500100015002000250030003500400045005月1日5月8日5月15日5月22日5月29日6月5日6月12日6月19日6月26日7月3日7月10日7月17日7月24日7月31日40好处：清楚地看到每个数据在时间序列的变化折线图是用于做控制图的常用方法折线图口门高低检测结果2828.52929.53030.53131.532135791113151719212325272931333537394143测量值上限下限41利用折线图制作控制图好处：可以清楚地看到超过控制限的数据，以观测过