高职高专会计专业"十三五"项目化规划教材

Excel数据处理与分析赵萍主编刘玉梅副主编北京内容简介本书为适应软件类课程特点和数据分析项目化教学改革需要,以Excel2016为分析工具,按照多岗位的多条工作任务为主线,将统计分析理论与数据分析技巧融入具体的实践过程,注重实用,由浅入深,循序渐进.
本书内容包括Excel数据分析基础、销售数据分析、调查问卷分析、人事数据处理与分析以及财务信息分析等五个项目,涵盖销售状况分析、销售业绩分析、销售预测;职工档案管理、考核管理、薪资管理;消费者构成分析、消费态度分析、消费行为分析以及财务报表分析、筹资分析、投资分析等多种典型工作任务和真实案例与技能训练.
本书方法多样,理实一体,步骤清晰,总结全面,既可作为各大院校财务、管理、统计等经济管理类专业的教材,又可作为社会各界人士入职必备的岗前培训手册.
本书提供丰富的配套资源(课件、教学案例源数据、习题样表和答案),可扫描前言中的二维码下载使用.
本书封面贴有清华大学出版社防伪标签,无标签者不得销售.
版权所有,侵权必究.
侵权举报电话:010-6278298913701121933图书在版编目(CIP)数据Excel数据处理与分析/赵萍主编.
—北京:清华大学出版社,2018(高职高专会计专业"十三五"项目化规划教材)ISBN978-7-302-49447-8Ⅰ.
①E…Ⅱ.
①赵…Ⅲ.
①表处理软件-信息处理系统-高等职业教育-教材Ⅳ.
①TP391.
13中国版本图书馆CIP数据核字(2018)第020934号责任编辑:崔伟马遥遥封面设计:周晓亮版式设计:思创景点责任校对:曹阳责任印制:王静怡出版发行:清华大学出版社网址:http://www.
tup.
com.
cn,http://www.
wqbook.
com地址:北京清华大学学研大厦A座邮编:100084社总机:010-62770175邮购:010-62786544投稿与读者服务:010-62776969,c-service@tup.
tsinghua.
edu.
cn质量反馈:010-62772015,zhiliang@tup.
tsinghua.
edu.
cn印装者:三河市少明印务有限公司经销:全国新华书店开本:185mm*260mm印张:16.
25字数:416千字版次:2018年3月第1版印次:2018年3月第1次印刷印数:1~2500定价:39.
00元产品编号:076116-01辽宁省职业教育改革发展示范校建设成果系列教材编审委员会主任:王冠白广申副主任:安刚孙迎春商学来委员:(以姓氏笔画为序)于舒白玉刚孙德才杨古今李晓红时武略张红玲张述平张嘉惠郑袆华赵杰前言大数据时代,数据铺天盖地,数据分析也渐渐成为一种基本技能.
随着大数据概念的普及,越来越多的人们意识到数据分析对社会经济发展的重要意义,各行各业都在数据中查找问题,发现机遇.
在此背景下,我们推出了这样一本数据分析的"技术书":涵盖多个岗位实际数据分析案例和数据处理方法,由易到难,将Excel的数据分析技术理论与应用实践紧密结合起来的工具书、岗位指导书——《Excel数据处理与分析》.
本书特点如下.
(1)创新性.
本书打破多年来Excel教材按操作方法分类构建章节的模式,以工作任务为主线,通过项目教学法将理论与实践、课堂教学与职业岗位实际结合起来,实现"做中学"教学模式改革.
(2)实用性.
突出数据分析在实际工作中的工具性、管理性与实用性,引入大量有代表性的工作实例,让读者在掌握Excel2016基本操作的同时,形成运用多种分析方法独立分析多种来源的数据的能力,提升Excel应用技巧,积累经验,提高办公效率.
(3)实践性.
以全真企业背景、业务、账表、要求等为蓝本,按照岗位工作流程介绍市场调查、行政人事、销售、财务分析等岗位必备的数据分析处理能力,同时覆盖理论、经验和技巧,具有强大的实践指导性.
本书以常用的办公软件Excel2016为工具,由浅入深,既适合新手学习数据分析技术,也适合专业人员进一步夯实理论,增加熟练度和操作技巧;理实一体,既可适应学生在校学习的需要,也兼顾未来就业对数据分析知识、理论、技巧的要求;既适合高职高专统计、会计、工商管理、人力资源管理等相关专业学生的学习,也适合广大社会工作人员职业技能提升、Excel办公效率提升的需要.
本书除了列举大量范例之外,还提供了丰富的教学资源,主要包括:①教学课件PPT;②课后习题资源包,方便读者进行课后训练;③教学案例资源包,为教师演示任务执行过程提供必备的数据资料.
说明:本书后续还将提供教学小视频,读者请关注我们教学资源包的更新.
教师服务邮箱为cuiwei80@163.
com.
本书由赵萍主编,刘玉梅为副主编,张红玲、张家鹤参编.
赵萍负责拟定全书大纲,并负责全部书稿的修改、统稿和最后定稿.
本书在编写过程中得到了多家企业的支持和帮助,特别得到了清华大学出版社编辑的大力支持与帮助,在此一并表示感谢!
尽管我们为编写此书付出了很多努力,但由于理论水平和实践经验有限,加之时间仓促,书中难免存在错漏之处,殷切希望广大读者提出宝贵意见和建议.
编者2017年12月课件、教学案例源数据、习题样表和答案目录项目一Excel数据分析基础·1学习情境一Excel的基本操作·2任务一认识Excel2016·2任务二数据的输入与编辑·12学习情境二公式与函数的应用·25任务一公式与函数基础·25任务二单元格引用·34项目二销售数据分析40学习情境一产品销售状况分析·41任务一销售量基本统计与分析·41任务二销售量综合统计与分析·48学习情境二销售人员业绩分析·54任务一销售业绩统计与评比·54任务二销售提成计算·61学习情境三销售预测分析·65任务一移动平均法预测·65任务二指数平滑法预测·70任务三回归分析法预测·75项目三人事数据处理与分析83学习情境一职工基本信息处理·84任务一职工档案建立、修改与查询84任务二人才构成分析·91学习情境二职工考核管理·97任务一职工出勤统计·98任务二职工期末考核·102学习情境三职工薪资管理·107任务一基本薪资数据的统计·108任务二创建工资结算单·114任务三企业人工费用统计·120项目四调查问卷分析·125学习情境一问卷数据库建立·126任务一问卷的回收与初审·126任务二问卷编码·129任务三问卷数据录入及二审·132任务四编码替换与数据安全·142学习情境二消费者构成分析·147任务一消费者性别与学历构成分析148任务二消费者年龄构成分析·153学习情境三消费态度分析·159任务一手机消费观念分析·159任务二手机消费偏好分析·168学习情境四消费行为分析·173任务一手机消费行为的均值分析173任务二手机消费行为的方差分析183任务三手机消费行为的独立性检验192任务四消费者对宏发公司消费印象分析197项目五财务信息处理与分析·209学习情境一财务报表分析·210VI任务一报表结构分析·210任务二报表比率分析·216学习情境二筹资决策分析·225任务一长期借款筹资模拟计算······226任务二最优资本结构设置·230学习情境三投资决策分析·233任务一一般投资业务分析·233任务二股票投资决策分析·239任务三债券投资决策分析·242任务四投资风险分析·244参考文献·249项目一Excel数据分析基础(1)能根据需要自定义快速访问工具栏、功能区.
(2)能熟练进行工作簿、工作表与单元格的相关操作.
(3)掌握公式与函数的构成,会定义名称,能正确运用相对引用、绝对引用、混合引用、三维引用以及名称来书写创建公式和函数.
(1)认识Excel的基本结构,掌握自定义快速访问工具栏、功能区的方法.
(2)理解并掌握数据录入与编辑的多项方法和技巧.
(3)了解名称的定义,掌握名称定义及应用方法.
(4)了解公式与函数的构成,掌握其构建方法,理解并掌握相对引用、绝对引用、混合引用、三维引用的含义及应用方法.
善于观察数据的结构和形式,提升数据录入的速度和技巧,形成规范数据、美化工作表格的意识,逐步养成运用Excel进行数据分析的习惯.
Excel是使用最广泛的办公软件之一,具有强大的表格数据处理和分析功能,而且向下兼容性较好,是日常管理和数据分析工作普遍应用的软件.
Excel2016的整体布局和Excel2013基本相同,但增加了一些新特性,使用起来更加便捷.
学习数据分析,应先从认识Excel工具开始.
关键词:基本操作公式与函数名称引用2学习情境一Excel的基本操作宏发公司的管理和数据分析工作主要应用Office软件.
2017年年初,公司将全部个人计算机的办公系统升级到Office2016.
为了更好地提升数据处理与分析的效率,数据分析相关岗位的员工掀起了学习Excel2016的热潮.
任务一认识Excel2016一、任务描述Excel2016是一款功能非常强大的数据分析软件,认识Excel的操作界面和基本功能,可以根据需要自定义快速访问工具栏和功能区,为数据分析工作打好基础.
二、入职知识准备Excel是一个电子表格程序,是数据处理、数据分析、数据维护的常用工具.
它通常由工作簿、工作表和单元格三大元素构成.
(一)工作簿、工作表与单元格通常所说的Excel文件,就是一个工作簿文件.
工作簿是用来存储并处理工作数据的文件,其扩展名为.
xlsx.
工作簿由工作表构成,无论数据还是图表都是以工作表的形式存储在工作簿中的.
一个工作簿最多可容纳255个工作表.
打开工作簿后,工作表显示在工作簿窗口,Excel2016默认显示一张工作表.
单元格是工作表中行与列交汇处的区域,是存储数据的基本单位,可以处理数字、文字、逻辑值、数组等不同类型的数据,也可以与其他不同工作簿、不同工作表间的单元格进行运算.
一张工作表由1048576(行)*16384(列)个单元格构成.
(二)操作界面相比以前的版本,Excel2016的操作界面更加直观、清晰.
其操作窗口由标题栏、选项卡、快速访问工具栏、功能区、编辑栏、工作区、状态栏等几部分构成.
1.
标题栏标题栏(见图1-1)位于Excel顶部,主要包括快速访问工具栏、文件名和窗口控制按钮.
图1-1标题栏32.
快速访问工具栏快速访问工具栏(见图1-2)位于标题栏左侧.
默认的快速访问工具栏中包含"保存""撤销""恢复"命令.
单击快速访问工具栏右边的下拉箭头,在弹出的菜单中可以自定义快速访问工具栏的命令.
图1-2快速访问工具栏3.
功能区功能区(见图1-3)位于标题栏下方,使用Ribbon风格,是Excel窗口的重要组成部分.
功能区由"文件""开始""插入""页面布局""公式""数据""审阅"和"视图"八个默认选项卡以及包含在选项卡中的组和各种命令按钮组成,利用它可以轻松地查找以前隐藏在菜单中的命令和功能.
在数据分析过程中,可根据需要对功能区的各选项卡、组及命令进行自定义设置和导出.
图1-3功能区4.
编辑栏、工作区和状态栏编辑栏(见图1-4)是位于功能区与工作区中间的区域,用于显示和编辑当前活动单元格的名称、数据和公式.
编辑栏由名称框、公式框和公式编辑按钮构成.
图1-4编辑栏工作区(见图1-5)是操作界面的主要区域,由单元格构成,用于数据编辑和处理.
状态栏(见图1-6)位于操作界面底部,显示当前数据的编辑状态和显示比例.
状态栏也可根据研究需要自定义显示项目.
图1-5工作区4图1-6状态栏三、任务内容(一)创建工作簿和工作表新建工作簿"数据分析基础",保存到"数据分析"文件夹,然后在工作簿中添加工作表"数据录入"和"乘法表",修改工作表标签颜色为绿色.
在"数据录入"工作表前插入"库存资料"工作表,标签颜色为红色,保存工作簿.
(二)自定义快速访问工具栏自定义快速访问工具栏,要求其显示新建、打开、保存、另存为、撤销、恢复等功能按钮,并且工具栏显示在功能区的下方.
(三)自定义功能区(1)添加"开发工具"选项卡,加载宏,并添加数据分析工具.
(2)自定义"常用功能"选项卡,下设保存功能、数据录入选项组,分别添加"Excel97-2003工作簿"和记录单功能按钮到各选项组.
四、任务执行(一)创建工作簿和工作表(1)打开"数据分析"文件夹,如图1-7所示,在空白区域单击鼠标右键,从弹出的快捷菜单中选择"新建"-"MicrosoftExcel工作表"命令,系统新建一个空白Excel工作簿,修改其名称为"数据分析基础",并打开.
图1-7创建工作簿及命名5(2)工作簿中有一张名为sheet1的工作表,如图1-8所示,右击sheet1标签,从弹出的快捷菜单中选择"重命名"命令,修改工作表标签名称为"数据录入",按Enter键,工作表完成重命名.
单击工作表标签右侧的,添加新的工作sheet1,按同样方法命名为"乘法表".
(3)选择"数据录入"工作表,如图1-9所示,按住Shift键,再单击"乘法表",选中两个工作表.
单击鼠标右键,从弹出的快捷菜单中选择"工作表标签颜色"命令,在标准色中选择绿色色块,完成标签颜色设定.
图1-8增加工作表及重命名图1-9设置工作表标签颜色(4)选择"数据录入"工作表,单击鼠标右键,从弹出的快捷菜单中选择"插入"-"工作表"命令,系统在"数据录入"工作表前增加一个新的工作表,修改其名称为"库存资料",并设置工作表标签为红色.
(二)自定义快速访问工具栏单击快速访问工具栏中的下拉按钮,在弹出的"自定义快速访问工具栏"菜单中选择要显示的按钮:新建、打开、保存、撤销、恢复,即可将其添加至快速访问工具栏,如图1-10所示.
6图1-10自定义快速访问工具栏"另存为"按钮的添加,需在"自定义快速访问工具栏"菜单中选择"其他命令(M)…",打开"Excel选项"对话框,在左侧"常用命令"列表中选择"另存为"按钮,单击"添加"-"确定"按钮.
这时"另存为"按钮已经加入了快速访问工具栏,如图1-11所示.
图1-11添加"另存为"到快速访问工具栏7另外,自定义快速访问工具栏菜单中选择"显示在功能区的下方"或"显示在功能区的上方"选项,系统将调整快速访问工具栏放置的位置在功能区的下方或放置在标题栏的左侧.
(三)自定义功能区1.
加载宏(1)在功能区的空白处右击,在弹出的快捷菜单中选择"自定义功能区"命令,或者选择"文件"-"选项"-"自定义功能区"选项,如图1-12所示.
图1-12加载宏(一)(2)打开"Excel选项"对话框,如图1-13所示,自动选择"自定义功能区"选项,在此对话框右侧"主选项卡"列表中勾选"开发工具"选项卡及其"加载项"名称前的复选框,单击"确定"按钮,系统中增加了"开发工具"选项卡,如图1-14所示.
图1-13加载宏(二)8图1-14加载宏(三)(3)选择"开发工具"-"加载项"-"Excel加载项",在"加载项"对话框中选中"分析数据库""分析数据库-VBA""规划求解加载项"复选框,单击"确定"按钮,系统将在"数据"选项卡下增加"分析"功能区,里面有"数据分析"和"规划求解"功能按钮,这些都是进行数据分析必备的工具,如图1-15和图1-16所示.
图1-15加载宏(四)图1-16加载宏(五)2.
自定义"常用功能"选项卡(1)选择"文件"-"选项"-"自定义功能区"选项,打开"Excel选项"对话框,如图1-17所示,在右侧"主选项卡"区域选择"开始"主选项卡,单击"新建选项卡(W)"按钮,系统自动在"开始"选项卡下面增加一个新的选项卡.
单击"重命名(M)"按钮,修改名称为"常用功能",单击"确定"按钮.
(2)选择"新建组(自定义)",单击"新建组(N)"按钮系统在其下面增加一个新的"新建组(自定义)",依次选中二者,执行"重命名"操作,修改名称分别为保存功能和数据录入,单击"确定"按钮,如图1-18所示.
9图1-17自定义常用功能(一)图1-18自定义常用功能(二)10(3)选中"保存功能",在左侧"全部命令"列表中选择"Excel97-2003工作簿",单击"添加"按钮;同理在"数据录入"选项卡中添加"记录单"功能.
系统完成选项卡的自定义与功能添加,如图1-19和图1-20所示.
图1-19自定义常用功能(三)图1-20自定义常用功能(四)操作技巧(1)选择要改变位置的选项卡或选项组,单击后面的"上移"或"下移"按钮,或拖曳选项卡名称,调整排序.
(2)添加命令后如果不需要该命令,则可以选择要删除的命令,单击"删除"按钮.
(3)用户在"Excel选项"对话框中,单击"重置"按钮,可删除功能区和快速访问工具栏的自定义内容,恢复到软件默认界面.
(4)定义好自定义快速访问工具栏或功能区后,单击"Excel选项"对话框右下角的11"导入/导出"按钮,可将自定义快速访问工具栏或功能区保存到指定位置.
以后当设定的工具栏或功能区发生变化时,可再次单击该按钮,导入之前的自定义设置文件,恢复快速访问工具栏和功能区的自定义设置.
1.
认识Excel2016的操作界面,能根据分析需要自定义快速访问工具栏和功能区.
2.
掌握新建、插入、重命名等基本的工作簿、工作表操作方法和技巧.
1.
自定义快速访问工具栏,增加"升序排序"和"降序排序"功能.
2.
自定义功能区,取消显示"视图"主选项卡,再恢复.
Excel究竟可以用来干什么Excel应用范围之广和普及人群之多不用多说,那么它究竟可以用来干什么Excel是一个功能强大的电子表格制作和数据处理与分析的软件,可以输入、输出、存储、整理和分析数据并以表格和图表等方式显示分析结果,甚至可以看作一个数据处理与分析的小型加工厂.
在实际工作中,绝大多数人如何应用Excel有一部分人仅以Excel作为计算器使用,在其中做些加加减减的简单运算;另一部分人把Excel当成一个表格制作与输出的工具.
Excel以表格形式出现,使人认为使用其创建表格更加方便.
当然也可以为表格设置一些格式,做一些简单的公式运算;还有一部分人使用Excel制作的数据报表能够很快让人抓住重点,并且赏心悦目,看着就是一种享受.
1.
只将Excel作为计算器使用当需要计算的数据较多时,使用Excel作为计算器的确比使用计算器或者Windows系统自带的计算器更为方便灵活.
于是经常会看到一些人在Excel中输入需要计算的长串数字,在另一个单元格中输入运算公式(他们甚至不知道使用函数),如果使用Excel仅限于此,则Excel是大材小用了.
2.
将Excel作为制作表格的简单工具由于熟悉Excel的功能,所以一些人会用其来录入表格内容并设置一定的美化格式.
而且还会使用简单的公式计算表格中的一些数据,如求和及求平均值等.
设置表格外观未脱离Excel内置的单元格或表样式的限制.
这些用户也许是当前使用Excel的主流人群,也是最12忠实的Excel迷.
但他们制作的表格容易让阅读者产生视觉疲劳,没有自己的特色和个性.
3.
使用Excel分析专业数据数据分析师熟知Excel的每一项功能,却不会被其牵着鼻子走.
他们制作的表格美观且专业、为阅读者提供更多有价值的信息.
不仅数据计算科学、简便、高效,表格的形式也更加清晰、美观与专业.
Excel可以帮助用户设计出符合各种需求的"高大上"的图表.
资料来源:启赋书坊.
分析力!
专业Excel的制作与分析实用法则[M].
北京:电子工业出版社,2011.
任务二数据的输入与编辑一、任务描述数据录入是数据分析的基础工作,掌握数据录入与编辑的方法和技巧,提升数据录入速度,快速规范地组织好数据以备分析.
二、入职知识准备(一)数值、文本、日期与时间数据录入1.
数值数据除了0~9及其构成的数值外,还可以包括+e等.
利用这些符号,可以在单元格中输入指定形式的数据,如$23000、8.
6%,以及科学记数法表示的数值等.
分数形式的数据输入较为特殊,需要以带分数的形式录入,整数部分与分数值中间以空格隔开,没有整数部分的,先录入0,再录入空格和后面的分数值.
默认情况下,数据将在单元格中右对齐.
这条标准可用来判断数据输入是否正确.
在Excel中输入数值应遵循如下规则:(1)数字和文字组合的数据,都视为文本来处理.
这样的单元格无法直接参与运算.
(2)输入纯数值时,会将数据显示成整数或小数,当数值长度超出单元格宽度时,以科学记数法表示.
数值长度超过11位数,Excel会自动以科学记数法表示.
(3)如果要将数字表示为文本,如身份证号、学号等,可在单元格先输入一个单引号,或设置单元格格式为文本后,再输入数字.
2.
文本数据文本包括汉字、英文字母、数字和符号等.
每个单元格最多可包含32767个字符.
默认情况下,文本数据在单元格中左对齐.
若文字长度大于字段宽度,会显示在右边的单元格中.
但若右边的单元格中已经录入数据,此时超出字段宽度部分的内容不予显示.
可通过"开始"-"数据"选项组-"对齐"选项卡中勾选"自动换行"复选框来显示所有数据.
133.
日期与时间数据在工作表中输入日期或时间时,需要特定的格式定义.
Excel中内置了一些日期与时间的格式.
先通过"开始"-"数据"-"日期"/"时间"设置单元格中日期或时间的显示格式,再以左斜线或短横线分隔日期的年、月、日,以冒号分隔时间的时、分、秒,可以获得格式规范的日期与时间数据.
日期与时间数据在单元格中靠右对齐.
操作技巧(1)输入今天的日期可按Ctrl+;组合键,输入当前的时间可按Ctrl+Shift+;组合键,快捷录入.
(2)Excel在计算时,若是两个日期格式数据相减,得到的格式会以"日期"格式作为默认格式,若是要以天数来显示,则必须设置单元格的数字格式为"常规"选项.
(二)快速填充除了常规的数据输入方法外,Excel还提供了一些批量录入数据的功能.
1.
在多个连续单元格中输入相同数据在多个连续单元格中录入相同数据,可利用Excel填充柄进行.
选定单元格区域的第一个单元格,并录入数据,再在初始单元格右下角的填充柄上按住左键向右、向下拖动鼠标,完成目标区域数据填充;如果单元格区域不连续,则需要先按住Ctrl键,单击目标单元格,选中所需区域,再在最后选择的单元格里录入数据,按Ctrl+Enter键,这样,所选定的区域即完成了相同数据的填充.
2.
序列填充序列数据是具有某一规则的数据集合.
例如,等差数列(1,3,5…),等比数列(2,4,8…)以及日期数列(星期一、星期二、星期三……)等.
序列填充包括自动填充和自定义序列并填充两种形式.
(1)自动填充Excel具有智能型自动填充功能.
使用自动填充功能来产生特定的序列时,要先输入初始值再选取要填入的范围后,在序列对话框中按需求设置序列类型、步长等信息,可快速完成数值型等差数列、等比数列以及日期数列等的填充.
自动填充功能也可以拖动填充柄来完成.
除了一般日期和数字外,Excel中已经存在的默认序列和自定义序列都可以通过此种形式完成填充.
(2)自定义序列并填充默认状态下,Excel中只包含一些通用的数据序列.
不同的组织在数据分析过程中,往往需要特异性的数据序列,那么就需要先将序列加入Excel系统,再进行序列填充.
自定义序列的方法有两种,一是从工作表中既存的数据进行转换;二是直接在"自定义序列"对话框中输入.
具体操作过程见"任务执行".
3.
快速填充功能参照填充快速填充(flashfill)是Excel2013版本中新增、在2016版本中延续的一项功能.
它能让14一些不太复杂的字符串处理工作变得更简单.
除了实现一般的复制填充功能外,快速填充还能实现日期、字符串等的拆分、提取、分列和合并等以前需要借助公式或"分列"才能实现的功能.
快速填充必须是在数据区域的相邻列内才能使用,在横向填充当中不起作用.
使用快速填充有很多途径,至少有以下三种方式可以实现.
方法一:选中填充起始单元格以及需要填充的目标区域,然后在"数据"选项卡上单击新增的"快速填充"按钮.
方法二:选中填充起始单元格以及需要填充的目标区域,按快捷键Ctrl+E.
方法三:选中填充起始单元格,使用双击或拖曳填充柄(鼠标移至单元格右下角,出现黑色十字形图标)的方式填充至目标区域,在填充完成后会在右下角显示"填充选项"按钮,单击按钮出现下拉菜单,在其中选择"快速填充"选项.
操作技巧用方法一、二生成快速填充之后,填充区域右侧还会显示"快速填充选项"按钮(flashfilloptions,图标上有一个闪电的图案),此时可以在这个选项中选择是否接受Excel的自动处理,也可以直接在填充区域中更改单元格内容立刻生成新的填充.
(三)外部数据导入在企业环境的数据处理系统中,有一些大型或微型计算机上的文件或一些特定的应用软件,如特定格式的会计系统,本身并没有提供"导出"功能,也不包含在Excel所支持的文件格式中,无法直接将数据导入Excel中,或对于某些分析的数据以文本文件格式保存.
这时需要以文本文件的形式导入Excel系统中.
文本数据导入主要分为3个步骤.
(1)判断数据的分隔方式是"分隔符号"还是"固定宽度".
Excel会自行判断应以何种方式导入文本数据,也可以自行选择.
其中,"分隔符号"表示每一类由特定字符,如逗号、分号、定位符号或空格分隔,甚至自动的符号,而在同一列的数据宽度可能不一样,"固定宽度"则表示每一列的数据宽度一致.
(2)决定"分隔符号"方式的符号或"固定宽度"的每列宽度.
如果选择采用"分隔符号"形式导入数据,则需进一步选定哪种分隔符号;如果选择"固定宽度"导入,则需进一步拖动分列线来调整列宽位置.
(3)更改字段的数据类型,包括数字、文字、日期,也可以在"列数据格式"选项组中决定是否要导入该字段.
具体导入文本数据的操作过程见"任务执行".
(四)数据编辑与规范1.
数据编辑当数据输入错误时,单击需要修改数据的单元格,输入准确的数据即可.
也可以选中错15误数据所在单元格,从右击弹出的快捷菜单中选择"清除内容"命令,清除数据之后,再重新输入正确的数据.
选中单元格后,运用键盘上的Backspace键或Delete键也可以清除数据.
移动单元格数据,可在选中欲移动的数据区域后,将鼠标放在选中区域的边框处,待鼠标变成十字箭头形状,按住鼠标左键移动该区域至目标位置,放开鼠标.
此时数据已经移动过来.
按Ctrl+X组合键将要移动的单元格或单元格区域剪切到剪贴板中,然后通过粘贴(运用鼠标右键的"粘贴"选项或按Ctrl+V组合键)的方式也可以移动目标单元格区域.
复制单元格内容,可使用Ctrl+C组合键或鼠标右键的"复制"功能,用法与使用组合键剪切相似.
此外,Excel中还提供了撤销、恢复等快速工具,随时撤销或恢复前面一步或多步操作;提供了查找和替换功能,可以快速定位、查找、替换错误数据.
2.
单元格数据格式在Excel单元格中输入数值时,是没有格式的,如果想要输入日期和时间的数值、货币型数据等,需要对单元格进行数字格式的设置.
Excel2016中提供了12种数字格式,如表1-1所示.
表1-1Excel2016的12种数字格式说明类型作用与方法常规Excel的默认数字格式,一般情况下,常规格式的数字即以输入的实际值显示,如果单元格的宽度不够显示整个数字,将对带有小数点的数据字进行四舍五入;如果是较大的数字(12位或更多)将使用科学记数的形式显示数值用于数字的一般表示.
可以设置使用的小数位数、是否使用千位分隔符以及如何显示负数货币用于一般货币值并显示带有数字的默认货币符号.
可以设置使用的小数位数、千位分隔符以及负数会计专用也用于货币值,但会在一列中对齐货币符号和数字的小数点日期将日期和时间序列号显示为日期值,用户可以选择多种日期显示方式时间将日期和时间序列号显示为时间值,用户可以选择多种时间显示方式百分比将单元格值乘以100,并将结果与百分号(%)一同显示.
用户可以指定要使用的小数位数分数根据所指定的分数类型以分数形式显示数字科学记数以指数计数法显示数字,将其中一部分数字用E+n代替.
其中,E(代表指数)指将前面的数字乘以10的n次幂.
例如,两位小数的科学记数格式将12345678901显示为1.
23E+10,即用1.
23乘以10的10次幂文本将单元格的内容作为文本处理,包括数字特殊将数字显示为邮政编码、电话号码或社会保险号码自定义允许用户自定义修改现有数字格式,使用此格式可以创建自定义数字格式并将其添加到数字格式代码的列表中如果要对单元格的数字格式进行设置,可采用以下四种方法.
16第一,使用功能区命令.
在命令组下方,设置了5个较为常用的数字格式按钮,分别为"会计专用格式""百分比格式""千位分隔样式""增加小数位数"和"减少小数位数",选择包含数值的单元格或单元格区域,单击数字格式按钮,即可应用该数字格式.
单击数字格式下拉列表,包含了11种数字格式选项和1个其他数字格式选项,单击"其他数字格式"选项,可打开"设置单元格格式"对话框.
第二,使用键盘快捷键.
用户可以使用键盘快捷键,设置单元格或单元格区域的数字格式,使用的数字格式设置快捷键如表1-2所示.
表1-2数字格式设置快捷键快捷键作用Ctrl+Shift+~常规数字格式,即为设置格式的值Ctrl+Shift+$货币格式,含两位小数Ctrl+Shift+%百分比格式,没有小数位Ctrl+Shift+^科学计数法格式,含两位小数Ctrl+Shift+#日期格式,包含年、月、日Ctrl+Shift+@时间格式,包含小时和分钟Ctrl+Shift+!
千位分隔符格式,不含小数第三,使用"设置单元格格式"对话框.
如图1-21所示,选择一个单元格并右击,在弹出的快捷菜单中选择"设置单元格格式"命令,弹出"设置单元格格式"对话框,选择"数字"选项卡,左侧列表中列出了12种数字格式.
除了"常规"和"文本"外,每种数字格式类型中都包含了多种样式可供用户选择,在对话框里预测显示相应的设置,并在"示例"区域显示预览效果.
另外,单击"数字"选项组中的"数字格式"按钮,或者按Ctrl+1组合键都可以打开"设置单元格格式"对话框.
图1-21单元格格式设置17第四,使用包含数字格式的单元格样式.
在Excel中提供了多种单元格样式,其中包括了含所有数字格式的样式,单击"开始"选项卡下"样式"选项组中的"单元格样式"按钮,弹出下拉列表.
在下拉列表中选择数字格式类型,完成设置,如图1-22所示.
图1-22设置单元格样式三、任务内容(一)库存数据录入将企业库存资料的信息录入工作表"数据录入",具体要求如下.
(1)表格名称为"宏发公司库存表",居中位于首行,字体为黑体,字号为14号;首行行高23.
25.
(2)将字段名"商品代号""商品品牌""进货单价(元)""库存数量(台)"分别录入单元格A2~D2中,底色为黄色行高26.
25,字体为宋体,加粗,字号11.
(3)表格主体行高为14.
25,无底色,字体为宋体,不加粗,字号11.
(4)准确录入商品代号,禁止重复;商品品牌要求设置数据有效性,从下列列表中选择录入;进货单价保留一位小数,不添加货币符号;库存数量为整数.
(5)添加表格的边框,要求字段名称所在行及表格的最下一行外层边缘线用粗实线(样式第二列倒数第三种),表体用细实线(样式第一列最下面一种).
列宽统一为15.
25,字符在单元格内部水平方向和竖直方向均居中,可以自动换行.
(二)文本数据导入将文本文件"问卷数据库(分隔符号)"和"问卷数据库(固定宽度)"①导入Excel,工作表名称不变,简单加以处理,保证数据分列清晰,适于分析.
①可用手机扫描本书前言所列二维码,从配套教学案例资源包中获取.
18四、任务执行(一)库存数据录入1.
设置表格名称选中单元格A1~A4,选择"开始"-"对齐方式"-"合并后居中"命令,输入"宏发公司库存表",按Enter键.
选中该区域,在"开始"-"字体"选项卡下,打开字体右侧下拉列表,选择"黑体",调整字号为14号.
2.
设计字段名称依次在单元格A2~D2中录入字段名"商品代号""商品品牌""进货单价(元)"以及"库存数量(台)".
选中这四个单元格,选择字体为宋体,字号为11,单击加粗按钮.
同时,单击颜色填充按钮,在下拉列表中选择"黄色",做好单元格底色填充.
3.
输入数据在单元格A3~A21区域依次录入各商品代号(可参见教学案例资源包中"1.
数据分析基础"工作簿).
选中单元格B3~B21区域,单击"数据"-"数据工具"-"数据验证",在下拉列表中选择"数据验证",打开"数据验证"对话框.
在"设置"选项卡的"允许"下拉列表中选择"序列",在"来源"文本框中输入"苹果,小米,vivo,华为,OPPO,金立,三星",各品牌之间用英文状态下的逗号隔开,单击"确定"按钮.
此时,单击B3~B21区域的每个单元格都出现一个下拉列表,分别从每个下拉列表中选择相应的品牌,完成"商品品牌"字段的填充.
操作过程如图1-23所示.
图1-23数据有效性的设置依次在单元格C3~C21区域中录入进货单价,在单元格D3~D21区域录入库存数量,完成表体记录单的录入工作.
选中单元格A3~D21区域,统一设置字体为宋体11号,不加粗.
4.
设置行高列宽选中第1行,右击选择"行高",设置数值为23.
25;同理,选中第2行,设置行高数值为23.
25;选中第3~第21行区域,设置行高为14.
25.
选中A、B、C、D四列,右击选择"列宽",设置数值为15.
25.
195.
设置边框选中表格中字段名称所在的单元格区域,单击"开始"-"字体"-"边框"-"其他边框",打开"设置单元格格式"对话框.
在"边框"选项卡下,选择"样式"第二列的倒数第三种线条,并单击右侧上边框和下边框,单击"确定"按钮,完成字段名称所在区域粗边框设置,如图1-24所示.
图1-24边框的设置同理,选择表体所在单元格区域,设置边框线条为"样式"第一列最后一种,单击右侧全部边框,单击"确定"按钮.
6.
设置对齐方式选中全部表格区域,单击"开始"-"对齐方式",在"设置单元格格式"对话框中选择水平对齐方式为居中,垂直对齐方式也为居中,勾选"自动换行"复选框,其他默认,单击"确定"按钮.
至此,完成全部表格的设计.
结果如图1-25所示.
商品代号商品品牌进货单价(元)库存数量(台)100025苹果350055100026苹果260042100027苹果490056101520小米265024101521小米1500110101522小米210052420017vivo200021420018vivo230028101533华为300080101534华为420066101535华为200030101536华为380024452356oppo160058452357oppo2400113452358oppo350078103452金立130052103453金立240087101158三星270040101159三星330036宏发公司库存表图1-25宏发公司库存表20(二)文本数据导入1.
分隔符号(1)导入数据打开"数据分析基础"工作簿,在"乘法表"之前插入一张工作表,重命名为"文本导入1".
选定单元格A1,单击"数据"-"获取外部数据"-"自文本",打开"导入文本文件"对话框,在指定盘符找到文件"问卷数据库(分隔符号)",单击"导入"按钮.
在"文本导入向导-第1步,共3步"对话框中,选择"分隔符号"单选按钮,其他默认,单击"下一步"按钮,打开"文本导入向导-第2步,共3步"对话框,默认选项不做修改,单击"下一步"按钮,选择"常规"列数据格式,其他默认,单击"完成"按钮,系统返回"导入数据"对话框,选择现有工作表中的A1单元格为数据放置位置,单击"确定"按钮,完成数据导入初步工作.
操作过程如图1-26所示.
图1-26分隔符号法导入外部数据(一)由图1-27可见,经过上述操作后,编号与性别两列没有被区分开,需要进一步利用分列功能进行处理.
21编号性别年龄学历是否喝啤酒何种品牌哪里购买1男30大专(学)是青岛啤酒便利商店2男22高中是青岛啤酒便利商店3女20大专(学)是北京啤酒便利商店4男42大专(学)以上是青岛啤酒量贩店5男38高中是北京啤酒便利商店6女34大专(学)是青岛啤酒便利商店7男25初中是青岛啤酒便利商店8女24大专(学)是青岛啤酒便利商店图1-27分隔符号法导入外部数据(二)(2)分列由于编号和性别都在A列,因此需要将二者区分开来.
选择B列,右击选择"插入"命令,系统在A列后新插入一空白列;选择数据表A列所在区域,选择"数据"-"数据工具"-"分列",打开"文本分列向导-第1步,共3步"对话框.
选择"固定宽度"单选按钮,单击"下一步"按钮.
在"文本分列向导-第2步,共3步"对话框中,单击"编号"与"性别"之间,系统在二者之间画出一道分隔线,单击"下一步"按钮,默认设置,并单击"完成"按钮.
此时,"性别"已经从A列剥离出来.
操作过程和结果如图1-28与图1-29所示.
图1-28外部数据导入-分列(一)22编号性别年龄学历是否喝啤酒何种品牌哪里购买1男30大专(学)是青岛啤酒便利商店2男22高中是青岛啤酒便利商店3女20大专(学)是北京啤酒便利商店4男42大专(学)以上是青岛啤酒量贩店5男38高中是北京啤酒便利商店6女34大专(学)是青岛啤酒便利商店7男25初中是青岛啤酒便利商店8女24大专(学)是青岛啤酒便利商店图1-29外部数据导入-分列(二)重复上述操作,进一步将编号与性别分开.
在分列的第二步操作对话框中,在数字与男女之间划分隔线,其他同前述操作,系统最终完成分列工作.
检查并修改其他记录的错漏,适当调整表格格式,保证数据清晰易用.
最终结果如图1-30所示.
编号性别年龄学历是否喝啤酒何种品牌哪里购买1男30大专(学)是青岛啤酒便利商店2男22高中是青岛啤酒便利商店3女20大专(学)是北京啤酒便利商店4男42大专(学)以上是青岛啤酒量贩店5男38高中是北京啤酒便利商店6女34大专(学)是青岛啤酒便利商店7男25初中是青岛啤酒便利商店8女24大专(学)是青岛啤酒便利商店图1-30外部数据导入-分列(三)2.
固定宽度采用"固定宽度"方式导入文本数据,操作过程与"分隔符号"形式一致,都是分为三个步骤,与"分列"活动也有异曲同工之妙.
具体过程如下.
(1)打开"数据分析基础"工作簿,在"乘法表"之前插入一张工作表,重命名为"文本导入2".
选定单元格A1,单击"数据"-"获取外部数据"-"自文本",打开"导入文本文件"对话框,在指定盘符找到文件"问卷数据库(固定宽度)",单击"导入"按钮.
(2)在"文本导入向导-第1步,共3步"对话框中,选择"固定宽度"单选按钮,其他默认,单击"下一步"按钮,打开"文本导入向导-第2步,共3步"对话框,系统智能区别不同字段划线(对于不想要的划线可双击去除),单击"下一步"按钮.
选择"常规"列数据格式,其他默认,单击"完成"按钮,系统返回"导入数据"对话框,选择现有工作表中的A1单元格为数据放置位置,单击"确定"按钮,完成数据导入工作.
操作过程如图1-31所示,操作结果如图1-32所示.
区别于"分隔符号"形式的导入,固定宽度形式导入之后的数据很规范,不需分列等其他操作便符合分析需要.
23图1-31固定宽度法导入外部数据(一)编号性别年龄学历是否喝啤酒何种品牌哪里购买1男30大专(学)是青岛啤酒便利商店2男22高中是青岛啤酒便利商店3女20大专(学)是北京啤酒便利商店4男42大专(学)以上是青岛啤酒量贩店5男38高中是北京啤酒便利商店6女34大专(学)是青岛啤酒便利商店7男25初中是青岛啤酒便利商店8女24大专(学)是青岛啤酒便利商店图1-32固定宽度法导入外部数据(二)1.
掌握一般数据的录入方法和快捷方法.
2.
掌握单元格格式设置方法.
3.
掌握外部数据导入方法及数据修改与规范方法.
24打开"课后习题"工作簿①中"1-1基础练习"工作表,依下列方式输入数据.
1.
将A2单元格中的文字设置自动换行的功能,使文字显示于A2单元格中,而不显示于B2单元格中.
2.
利用一般数据输入的方式,在C5~C9五个单元格输入1500、245、200、720与110等数据.
3.
在A10与F4单元格中输入"总和"与"合计"等文字数据.
4.
利用一般数据输入方式,在B3单元格中填入日期.
5.
在B12单元格中输入2015年的营业额为7650元.
6.
以阿拉伯数字在B13、B14等单元格中填入13、114等数据.
7.
在B15单元格中利用Ctrl+;组合键输入今天的日期,并在B16单元格中计算B15与B3单元格相差的天数.
如果计算出来的是日期格式,改为常规格式以显示天数.
8.
利用智能型自动填充功能将B4单元格中的数据以鼠标拖动的方法在C4、D4、E4单元格中产生第二季、第三季、第四季等数据.
Excel中常用的鼠标拖曳技巧1.
快速移动/复制表格选中表格,直接拖动,即可快速移动到另一个位置;如果按住Ctrl键拖动表格,则可以快速复制出一个表格.
2.
快速移动、缩放、复制、对齐图形在插入图形、移动图形时,也有很多鼠标拖动的技巧.
常见的6种提高效率的拖曳操作如表1-3所示.
表1-3常见的6种提高效率的拖曳操作操作效果按住Shift键拖动鼠标插入正圆、正方形按住Shift键拖动图形对角保持长宽比例缩放按住Ctrl键拖动图形对角以图形中心点缩放按住Alt键拖动图形对角图形与单元格边框对齐按住Shift键移动图形图形将水平或垂直移动按住Ctrl键移动图形可以复制一个新图形①"课后习题"工作簿可通过手机扫描前言中列出的二维码获取,以下不再单独提示.
253.
两列或两行互换注意是互换,不是替换.
其实说互换也不是特别准确,相当于移动并插入行或列.
操作手法:默念移动,左手按Shift键不松,右手按鼠标左键不松拖动列或者行边线,可以快速让列或者行换位.
注意:拖曳时,鼠标一定要选在单元格边框上.
4.
快速插入、删除行左手按Shift键不松,当光标显示下面分开形状时拖动(请注意看鼠标,会看到分成两行的时候).
往下拖动,可以快速插入行;往上拖动,可以快速删除行.
当然,这个操作对列也是适用的.
5.
把表格转移到另一个工作表中按Alt键不松,选中表格进行移动,可以将表格移动到另一个Sheet中.
6.
巧妙公式转数值(1)选取公式所在的列,按右键不松拖动到一边,别松键再拖回来.
(2)单击仅复制数值.
这个操作的本质是利用了右键呼出菜单,将动作连贯在了一起.
熟练运用,有一种浑然天成的感觉.
学习情境二公式与函数的应用公式和函数是数据分析的重要工具,掌握其应用方法和技巧,可以大幅度提升工作效率,轻松办公.
函数是特殊的公式,Excel2016提供了门类繁多的内置函数700余种,包括商业常用的统计、财务、日期与时间、数据库、逻辑、查找、引用等不同类型的函数,还新增了TEXTJOIN等数据分析用户期盼已久的函数,功能十分强大.
任务一公式与函数基础一、任务描述认识公式和函数的构成、运算规则和创建方法,会定义名称并在公式和函数中加以运用,能熟练运用SUM()、MAX()、MIN()等常用函数分析企业的基本运行数据.
二、入职知识准备(一)公式概述Excel最主要的目的之一是用来计算,建立公式与函数两种反映问题模式的运算方式.
26简单地说,单纯的公式是直接由运算符号、地址(名称)组合而成,函数则是由Excel内置的程序赋予所需的参数以求得所要的信息.
在应用上,可以将函数视为公式的一个运算对象,而形成广义的公式定义.
不论公式或函数都是以等号(=)开头.
要计算的对象(称为参数)可为地址、名称或常数等.
设置函数时,可直接使用插入函数或自行输入来完成.
1.
运算符简易的公式是地址配合许多运算符号所构成,而广义的公式则是由运算符号、单元格地址与函数所构成.
在Excel中作为运算的运算符号与一般算数中的运算符号一样,针对不同的数据进行运算,可将运算符号分成四大类来介绍.
(1)数学运算符数学运算符主要有加、减、乘、除以及乘方和百分比等.
数学运算符的说明如表1-4所示.
表1-4数学运算符运算符名称含义运算符名称含义+(加号)加法*(星号)乘法-(减号)减法%(百分号)百分比/(斜杠)除法^(脱字符)乘幂(2)比较运算符比较运算符号主要用于比较两个数值,得到的运算结果为逻辑值TRUE或FALSE,其相对的数值是1与0.
比较运算符如表1-5所示.
表1-5比较运算符运算符名称含义运算符名称含义=(等号)等于>=(大于等于号)大于等于>(大于号)大于(不等号)不等于(3)引用运算符引用运算符主要包括比号、逗号和空格.
具体说明如表1-6所示.
表1-6引用运算符运算符名称含义:(比号)区域运算符,对两个引用之间包括这两个引用在内的所有单元格进行引用,(逗号)联合运算符,将多个引用合并为一个引用(空格)交叉运算符,产生同时属于两个引用的单元格区域的引用27(4)文本运算符文本运算符只有一个文本串连字符"&",用于将两个或多个字符串连接起来.
数值类型的数据可用四则运算符,但文本数据则只可用文字运算符来连接.
等号(=)后不管是单元格地址还是函数,只要是与文字连接,不论是放在前面还是后面,都用"&"符号来连接.
在数据编辑行中输入右括号时,Excel会以粗体强调相对的左括号.
如此可用以审核括号个数是否正确.
当使用键盘输入或将插入点移动经过一个括号时,会暂时将一组相对应的括号用粗体显示.
当有多个括号时,也会以颜色区分相对的括号.
若是遗漏了右括号,Excel具有"公式自动校正"功能,会自动补上右括号.
当公式建好时,在单元格中显示的是运算后的数值,公式则显示在编辑栏中.
有时在处理大型模型时,若想直接由工作表中查看公式的设置或以多重窗口同时查看公式与值的比较,则应进行公式与运算结果的切换.
选择菜单栏中的"公式"选项卡-"显示公式功能"按钮,可以实现这一功能.
2.
运算符优先级如果一个公式中包含多种类型的运算符号,Excel则按表中的先后顺序进行运算.
如果想改变公式中的运算优先级,则可以使用括号实现.
运算符优先级说明如表1-7所示.
表1-7运算符优先级优先顺序运算符说明优先顺序运算符说明1:比号域运算符6^脱字符乘幂2,逗号联合运算符7*和/乘和除3空格交叉运算符8+和-加和减4-负号减法9&文本运算符5%百分号百分比10比较运算符(二)函数概述所谓函数,就是Excel预先写好的特殊公式,可让用户得以设置参数后迅速而简易地完成复杂运算.
简单来说,函数的功能就是将一个或多个参数进行运算,然后将处理的结果返回.
在进行数据分析时,除了使用Excel内置功能来完成外,也会大量使用函数来建立分析模式.
使用狭义的公式来处理大量单元格的运算时,有时候会相当复杂.
相对的,函数最大的功能就是简化复杂的公式输入.
1.
函数构成函数基本上由两部分组成,分别为函数名称和参数.
函数名称代表此函数的意义.
例如,求和的SUM、计算平均数的AVERAGE、最大值MAX、求净现值的NPV等.
而参数则告诉Excel要执行的目标单元格、名称或数值.
例如求和公式=SUM(A2:A5),其中,SUM为函数的名称,而A2:A5则为函数的参数,用来计算A2:A5四个单元格内数值的和.
28参数是Excel函数据以产生结果的基本信息,必须置于函数名称后面的括号中.
在同一个函数中的参数个数与总长度是有限制的.
使用参数时,要注意其数据类型,若类型不符,Excel会返回一个错误值.
参数可由数字、地址、名称、文字、逻辑值、数组、错误值或其他公式与函数所组成.
如果函数的参数就是另一个函数,这种情形称为嵌套函数.
2.
函数的设置准则每一个函数至少包含一组括号,指出Excel函数参数开始和结束的位置.
在括号前后都不可以有空格.
括号中主要设置参数,但如TODAY()函数则只有括号不需要参数.
所有的参数都要以正确的顺序和数据类型输入.
若要省略参数,仍需输入逗号作为预留位置.
在必须有参数的函数中,一定要指定参数.
部分函数接收选择性的参数,表示非必要的参数.
自行输入函数时,若函数名称无误,在输入左括号后,会自动出现参数提示标签,提示有哪些参数是必要的,这些参数的类型是什么,哪些是选择性的以及可以连接到该函数的说明主题.
以插入函数来建立函数时,选定函数后,会打开函数参数的对话框,Excel将各参数分项显示,并对参数进行说明.
通过此对话框,除了可以指导参数的顺序、数据类型等外,还可以了解每一个参数的意义.
3.
函数的参数类型掌握了参数的概念之后,还必须了解函数参数的类型.
(1)数值参数类似SUM()函数、VAR()函数等,会使用数值型参数来进行计算,这些数值参数可包含正、负符号并可有小数.
如公式=VAR(13,20.
5,-10,5),Excel会返回四个数的方差值170.
3958.
在一般的处理中,通常会以区域地址或名称来取代数值.
(2)文本参数针对文本类型的参数,需以双引号标出,如果不使用双引号,系统会将文本参数作为名称处理,如果事先未定义该名称,则会出现错误值"#NAME".
例如,在计算字符串长度时使用的LEN()函数,公式应为=LEN("LOVE"),而不是=LEN(LOVE).
(3)逻辑值参数逻辑值本身只有真(TRUE)和假(FALSE)两种.
使用逻辑值参数时,可直接输入"TRUE"或"FALSE",或者也可用表达式来取代其中的参数.
例如,使用AND()函数判断多个叙述是否全为真,公式为=AND(5+8=12,2*3>5,TRUE).
(4)错误值参数在Excel中共有七个错误值:#DIV/O!
、#NAME、#N/A、#REF!
、#NUM!
、#NULL!
、#VALUE!
.
以错误值来做参数,可以直接输入七个错误值之一,但这么做通常没有意义.
以错误值来做参数值的函数,一般是判断某一单元格是否有错,如ISERROR()函数.
针对错误值,可以用某一个单元格地址来取代.
29(5)地址和名称参数可用地址来表示,如B3、F3:G10等,都是合法地址.
参数也可以用名称来代替,事先取过名称的地址,可以直接用名称来代替.
(6)其他函数或公式使用由其他函数或公式的返回值作为函数参数,而不论其是哪种类型.
(7)数组有些函数参数必须使用数组(array)参数类型.
例如,计算回归的TREND()函数,计算频数分布的FREQUENCY()函数等.
(8)混合类型有一种参数属于混合类型,可包括上述任何一种Excel可接受的参数类型.
(9)不需参数Excel中还有一些函数并不需要参数,如时间和日期函数NOW()、TODAY()等.
4.
函数的建立使用函数时,可自行输入或使用"插入函数"对话框来进行.
自行输入函数较为便捷,但需要用户熟悉函数的用法,针对常用函数用得较多,用户可以根据Excel提供的帮助系统,学习运用.
初学者或不常用函数的用户建议使用"插入函数"对话框来设置函数.
(三)名称的定义与应用名称是工作簿中某些项目的标识符.
在Excel中,可以为单元格、常量、图表、公式或工作表等项目定义一个名称.
如果某个项目被定义了一个名称,就可以在公式或函数中通过该名称来引用它.
以名称来取代单元格地址时,不仅简化了函数参数的设置,也可使所建立的公式较具有实际的意义.
名称的定义有以下三种方法.
1.
定义名称按钮选择需要命名的单元格区域,单击"公式"选项卡-"定义的名称"选项组-"定义名称"按钮,在弹出的"新建名称"对话框的"名称"文本框中输入姓名,在"范围"下拉列表框中选择"工作簿"选项,单击"确定"按钮,即可完成命名操作.
2.
在名称框中命名所谓直接定义名称方式,即是利用"名称框"文本框来完成.
选定要命名的区域后,直接在"名称框"中填入所要的名称,按Enter键即可.
3.
以选定区域命名在一般的应用上,针对表格数据来说,数据的项目名称都置于该数据区域的顶端行或最左列,而此名称实际上亦适合取代为该区域(行或列)的名称.
选定需要命名的单元格区域,单击"公式"选项卡-"定义的名称"选项组-"根据所选内容创建"按钮,在弹出的"以选定区域创建名称"对话框中选中"首行"和"最左列"两个复选框,单击"确定"按钮,完成命名操作.
为单元格、单元格区域、常量或公式定义好名称后,就可以在工作表中使用了.
名称可以用来取代公式中的地址,在设置公式计算、设计数据有效性等方面被广泛使用.
30三、任务内容(一)函数应用2017年1月31日宏发公司库存资料如图1-25所示,请设计公式计算存货数量合计以及最大库存量和最小库存量.
(二)名称应用以库存数据为例,将苹果手机的库存数量区域命名为"苹果库存",将华为手机的库存数量区域命名为"华为库存",将三星手机的库存数量区域命名为"三星库存",利用名称设计公式计算三种商品的平均库存量.
四、任务执行(一)函数应用1.
数据准备选择"数据录入"工作表,单击鼠标右键,选择"移动或复制"命令,打开"移动或复制工作表"对话框,将选定工作表移至"数据分析基础"工作簿中"乘法表"工作表之前,勾选"建立副本"复选框,系统新增工作表"数据录入(2)".
选择"重命名"操作,修改该工作表为"公式与名称".
具体操作如图1-33所示.
图1-33复制工作表并重命名在"公式与名称"工作表中,选中A22、B22、C22三个单元格,单击"开始"-"对齐方式"-"合并后居中",将三个单元格合并起来,并输入"合计"二字;采用同样方法合并A23、B23、C23三个单元格,修改内容为"最大库存";合并A24、B24、C24三个单元格,修改内容为"最小库存";合并A25、B25、C25三个单元格,修改内容为"苹果、华为、三星手机平均库存量".
2.
求和求和有两种方式,一种是书写公式,用"+"连接全部加数所在单元格;另外一种是应用SUM()函数,后一种方法较为常用.
选择单元格D22,输入=SUM(D3:D21),计算全部商31品的库存总量.
其中,参数"D3:D21"表示全部商品库存数量所在区域.
3.
求最值求最大值可应用函数MAX(),求最小值应用函数MIN().
这两个函数的使用与SUM()函数相似.
采用"插入函数"对话框形式,选择单元格D23,单击公式编辑按钮,打开"插入函数"对话框,如图1-34所示.
在"选择函数(N)"下拉列表中选择MAX函数,单击"确定"按钮,系统返回"函数参数"对话框,在Number1的位置输入或选择地址D3:D21,单击"确定"按钮完成公式设置.
系统自动计算库存量的最大值为113台.
图1-34插入函数同理,计算存货量最小值,采用MIN()函数,可以重复上述"插入函数"的方法,也可以直接输入公式=MIN(D3:D21),算出最小存货量21台.
全部公式设置情况如图1-35所示.
商品代号商品品牌进货单价(元)库存数量(台)100025苹果350055100026苹果260042100027苹果490056101520小米265024101521小米1500110101522小米210052420017vivo200021420018vivo230028101533华为300080101534华为420066101535华为200030101536华为380024452356oppo160058452357oppo2400113452358oppo350078103452金立130052103453金立240087101158三星270040101159三星330036105211321宏发公司库存表合计最大库存最小库存图1-35全部公式设置(二)名称应用1.
定义名称选择苹果手机对应的库存数量区域D3:D5,在左上角的名称框中输入"苹果库存",按Enter键,系统做好名称定义,采用同样方法,定义"华为库存""三星库存".
结果如图1-36所示.
32图1-36定义名称2.
应用名称计算平均数如图1-37所示,计算平均数用函数AVERAGE().
在单元格D25中输入=AVERAGE(苹果库存,华为库存,三星库存),按Enter键,系统自动计算出三个单元格区域数值的平均数为47.
66666667台.
图1-37名称应用1.
掌握公式中各种运算符的计算规则和优先级,能正确设置公式解决实际问题.
2.
掌握函数的构成,了解各种类型参数的使用方法,会插入函数.
3.
能为单元格、单元格区域等项目定义名称,并在公式、函数中应用名称,提高效率.
33根据"课后习题"工作簿中"1-2公式与函数练习"工作表,进行以下操作:1.
以"自动填充"功能完成C3:E3单元格区域的月份数据.
2.
在A2单元格完成"现在时间是:**月**日(星期)(上午/下午)**时**分**秒"(提示:使用文字连接符号&、TEXT()与NOW()函数来完成).
3.
求F4:G10单元格的总和与平均值.
Excel公式中的这些特殊数字,你都了解吗在使用函数公式过程中,有一些经常用到的有着特殊含义的数字,这些数字你知道几个呢1.
9E+3079E+307是科学计数法表示的一个数字,可以简单理解成是Excel支持的一个很大的数字.
2.
1和0这两个数字用法实在是太多了.
例如,0可以在判断的时候当FALSE用,可以用某些文本数字+0变成数值,用-(0&mid函数提取出的空)可以把空值转化成0避免出现错误值……1可以在判断的时候当TRUE,可以当作1天24小时来计算时间,也可以是比0大的数字被用在Lookup(1,0/条件判断,数据)这样的组合里……还是碰到相关函数公式再单独研究其用法吧!
3.
1/17或5^19或5/19等这几个数字有一个特点,就是运算返回的值里面包括0~9所有的10个数字.
例如,1/17=0.
0588235294117647,5^19=19073486328125,5/19=0.
263157894736842.
这个一般用于FIND函数在单元格中查找数字时避免出现错误值.
4.
9999是一般用在文本函数中的,也充当一个大数字的角色.
用法示例1:=MID(A2,3,99).
这个MID函数返回A2单元格中第3个字符后的所有字符.
因为不确定A2单元格字符一共多少个,所以就用99来代替了.
也就是只要第3个字符后面的字符不超过99个,就都能正确提取出来.
5.
1和24在Excel里,时间和日期都是数字,可以显示成不同的样式.
1代表1天,代表24个小时,代表86400秒等.
如图1-38所示,时间计算示例:A2是上班时间,B2是下班时间,34C2输入B2-A2得到的0.
38代表0.
38天.
6.
1900/4/3或类似1900年开头的日期数据这种返回值一般是两个日期减差或者一个不大的数字显示成了日期格式.
用法示例如图1-39所示.
图1-38时间计算示例图1-39日期计算示例两个日期相减期望得到相隔的天数,但是由于C2单元格也是日期格式,所以就显示成了1900/4/3这种,其实结果是94天.
Excel从1900年1月1日起算1,1900年1月2日算2,依此类推.
因为单元格返回数值是94,显示成日期格式就成了1900年4月3日.
任务二单元格引用一、任务描述在应用公式和函数的过程中经常要引用单元格.
单元格的引用方式有相对引用、绝对引用等多种,选择正确的引用形式可以大大增加公式或函数的适应性,提高数据分析效率.
二、入职知识准备单元格引用通常是由该单元格所在的行号和列标组合所得到的,即该单元格在工作表中的地址,如A2、B6等.
在Excel中根据样式划分,引用可以分为A1引用样式和R1C1样式;根据地址划分,单元格的引用方式有相对引用、绝对引用、混合引用和三维引用4种形式.
(一)相对引用公式中的相对引用单元格是基于包含公式和单元格引用的单元格的相对位置.
如果公式所在的单元格位置改变,则引用也随之改变.
例如,在E5单元格中输入公式为=E2+E3+E4,拖动到E6单元格就会变成=E3+E4+E5;拖动到F5则会变成=F2+F3+F4.
(二)绝对引用绝对引用单元格指工作表中固定位置的单元格,它所在位置与引用公式的单元格无关.
在Excel中,通过对单元格引用的冻结来达到此目的,即在单元格的行号和列标前添加"$"符号,如$C$2,表示绝对引用单元格C2.
如在E5单元格中输入公式为=$E$2+$E$3+$E$4,无论向哪个方向拖动,公式都不会改变.
F4键可以快速实现绝对引用.
35(三)混合引用混合引用是指在一个单元格地址引用中,既有相对地址引用,也有绝对地址引用,如C$2和$C2.
在E5单元格中输入公式=E$2+$E3,拖动到E6公式会变成=E$2+$E4;拖动到F5公式会变成=F$2+$E3.
(四)三维引用三维引用表示要引用同一工作簿不同工作表或不同工作簿之间的单元格或单元格区域.
引用同一工作簿不同工作表中的单元格,表达方式为"工作表!
单元格地址";如果要引用同一工作簿中多个工作表中的单元格,其表达方式为"工作表名称:工作表名称!
单元格地址";除了引用同一工作簿中不同工作表中的单元格外,还可以引用不同工作簿中的单元格,这种引用方式分为以下两种情况.
第一,在Excel中未打开被引用的工作簿,其表达方式为"'工作簿存储地址[工作簿名称]工作表名称'!
单元格地址".
第二,如果已经在Excel中打开了被引用的工作簿,那么输入"[工作簿名称]工作表名称!
单元格地址",就可以引用.
三、任务内容(1)承接任务一中库存数据,完成库存总价的计算;假定每个编号的商品都必须满足最低库存量20台,设计公式计算各商品尚可销售的数量.
(2)设置公式自制乘法表,格式如图1-40所示.
123456789123456789乘法表图1-40乘法表格式要求利用"混合引用"在空白区域的左上角单元格设置一个公式,向下、向右拖曳该公式即可完成全部数字填充.
四、任务执行(一)库存数据处理1.
数据准备复制"数据录入"工作表于"乘法表"之前,重命名为"相对与绝对引用".
在"宏发36公司库存表"的右侧E1单元格内输入"最低库存量:",选中F1单元格,单击"开始"-"数字",打开"设置单元格格式"对话框,如图1-41所示,在"数字"选项卡下选择"自定义"分类,在右侧"类型"中选择"0"并在其后输入"台"字.
单击"确定"按钮,此时,虽在单元格F1内输入数字20,却显示为20台.
图1-41自定义"库存数量"字段格式在E2和F2单元格内增加两个字段名"库存总价(元)"和"尚可销售量(台)",格式与其他字段相同,设置边框和填充颜色.
结果如图1-42所示.
最低库存量:20台商品代号商品品牌进货单价(元)库存数量(台)库存总价(元)尚可销售量(台)100025苹果350055100026苹果260042100027苹果490056101520小米265024101521小米1500110101522小米210052420017vivo200021420018vivo230028101533华为300080101534华为420066101535华为200030101536华为380024452356oppo160058452357oppo2400113452358oppo350078103452金立130052103453金立240087101158三星270040101159三星330036宏发公司库存表图1-42库存数据准备372.
相对引用在单元格E3中设置公式=C3*D3计算编号为100025的苹果手机的库存总价,为了保证向下拖曳十字光标填充时,公式能自动根据所在行选择"进货单价"和"库存数量"来计算库存总价,在单元格E3中应采用相对引用设置公式.
具体形式为"=C3*D3",此时当向下填充时,公式会自动变成"=C4*D4""=C5*D5"等,直至计算完成全部存货的库存总价.
3.
绝对引用在计算商品的"尚可销售量"字段时,由于每种商品的最低库存量固定为20台,因此设置公式时要保证对其的引用固定不变,不会随着光标的拖曳而发生移动.
在单元格F3中设置公式=D3-$F$1,双击右下角十字光标即可完成准确计算.
相对引用与绝对引用的计算公式及计算结果分别如图1-43和图1-44所示.
图1-43相对引用与绝对引用公式设置最低库存量:20台商品代号商品品牌进货单价(元)库存数量(台)库存总价(元)尚可销售量(台)100025苹果35005519250035台100026苹果26004210920022台100027苹果49005627440036台101520小米265024636004台101521小米150011016500090台101522小米21005210920032台420017vivo200021420001台420018vivo230028644008台101533华为30008024000060台101534华为42006627720046台101535华为2000306000010台101536华为380024912004台452356oppo1600589280038台452357oppo240011327120093台452358oppo35007827300058台103452金立1300526760032台103453金立24008720880067台101158三星27004010800020台101159三星33003611880016台宏发公司库存表图1-44相对引用与绝对引用计算结果38需要特别说明的是,单元格F1中的内容,并非文本"20台",而是数字20,其显示形式"20台".
如果直接录入"20台",按上述方法设置公式会出错.
(二)乘法表制作1.
框架制作在"乘法表"的A3~A11单元格中填充数字1~9,同理在单元格B2~J2中也填充数字1~9,添加边框线并填充颜色,做好乘法表的雏形.
2.
设置公式在单元格B3中设置公式=$A3*B$2,为了保证单元格向右填充时A列不变,需要在"A3"的"A"前加"$",同时,为了保证单元格向下填充时第2行不变,需要在"B2"的"2"前加"$",这种混合引用的情形,实现了填充的通用性.
3.
复制粘贴由于单元格B3中输入的公式具有通用性,适用所有乘法表的空白单元格,因此选择"乘法表"中B3:J11单元格区域,右击选择"粘贴"命令,即可完成整个表单的制作.
结果如图1-45所示.
图1-45乘法表掌握函数书写中单元格的引用方法:相对引用、绝对引用、混合引用及三维引用,能根据实际分析需要准确选用.
根据"课后习题"工作簿中"1-2公式与函数练习"工作表,进行以下操作:设计公式运用相对引用计算全年总收入,运用绝对引用计算各月收入占全年总收入的比重.
39Excel中的小技巧,你知道吗1.
如何让表头部分分页打印后都有通过"页面布局"-"打印标题"-"顶端标题行"设置,选取行区域.
2.
批量清除Excel中数字的绿三角全选含绿色三角的区域,打开绿三角,选择"忽略错误"或"转换为数值"(列表中的选项一个不行再试另一个,生成的原因不同,选择的选项也不同).
3.
Excel返回当天日期的函数=TODAY()返回当天的日期;=NOW()返回现在的时间和日期.
4.
如何选中相同的内容如果只是单列,则可以用筛选的方法;如果是多列,则可以查找后按Ctrl+A组合键全选.
5.
如何恢复保存以前的Excel数据一般情况下无法恢复,所以一定要注意重要文件的备份.
特殊情况下有的计算机会生成备份文件(通过"文件"-"Excel选项"-"保存"-"自动恢复保存位置"找到),可以一试.
6.
禁止自动生成超级链接"文件"-"选项"-"高级"-"请求更新链接"的勾去掉.
7.
怎样进行模糊筛选自Excel2010起提供了筛选框,输入值可以实现模糊筛选.
8.
出现循环引用的警告怎么处理循环引用是自己直接或间接引用自己,通过"公式"-"公式审核"-"错误检查"-"循环引用"找到循环引用的单元格,然后修改公式即可.