普通高等教育经管类专业"十三五"规划教材·会计信息化系列

财务与会计数据处理——以Excel为工具丛书主编欧阳电平主编陈潇怡副主编李超夏雨北京内容简介本书以财务与会计业务基础知识为铺垫,以业务流程和业务场景为主线,讲解了运用Excel进行财务与会计业务的数据处理过程.
全书共分10章.
第一章介绍了Excel应用基础,按照Excel是什么、有哪些功能、能够帮助管理者做什么、如何做的思路阐述了什么是Excel模型、如何利用Excel工具建模、输入数据、处理数据、获取结果.
这一章是读者应用Excel的入门知识,介绍了工作簿、工作表、单元格、公式、函数等基础概念及其使用,同时介绍了Excel数据表分析、图表分析方法,使读者对应用Excel的全过程有一个全面的了解.
第二章到第五章介绍了Excel在会计数据处理中的应用,主要包括Excel在会计账务处理、工资核算、固定资产核算、成本核算与管理中的应用.
第六章到第九章介绍了Excel在财务管理中的应用,主要包括Excel在筹资管理、投资管理、收入分配管理、财务分析中的应用.
这八章以业务知识和业务流程为主线,介绍如何应用Excel中的工具进行业务数据处理.
第十章介绍了Excel的深入应用,包括从文本文件、数据库、网站等获取外部数据,美化图表,宏与VBA,Excel与商务智能等内容.
本书可用作高等院校财会专业和经济管理类专业的教材或教学参考书,也可用作从事财务、会计、审计和经济管理工作人员的培训用书和自学教材.
本书封面贴有清华大学出版社防伪标签,无标签者不得销售.
版权所有,侵权必究.
侵权举报电话:010-6278298913701121933图书在版编目(CIP)数据财务与会计数据处理——以Excel为工具/欧阳电平丛书主编;陈潇怡主编.
—北京:清华大学出版社,2017(普通高等教育经管类专业"十三五"规划教材·会计信息化系列)ISBN978-7-302-48490-5Ⅰ.
①财…Ⅱ.
①欧…②陈…Ⅲ.
①表处理软件-应用-会计-高等学校-教材②表处理软件-应用-财务管理-高等学校-教材Ⅳ.
①F232②F275-39中国版本图书馆CIP数据核字(2017)第227638号责任编辑:刘金喜蔡娟封面设计:尹梦涵版式设计:思创景点责任校对:成凤进责任印制:出版发行:清华大学出版社网址: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课件下载:http://www.
tup.
com.
cn,010-62794504印刷者:装订者:经销:全国新华书店开本:185mm*260mm印张:19.
75字数:481千字版次:2017年10月第1版印次:2017年10月第1次印刷印数:1~2500定价:39.
00元产品编号:丛书序经济全球化和"大数据""云计算""移动互联""人工智能"等新一轮信息技术的飞速发展,加速了我国企业信息化的进程,会计环境也发生了重大变革.
依托于信息技术创新的财务管理模式(如财务共享服务),以及管理会计信息化的深入推进,不仅提高了会计工作效率,更加提升了会计管理、控制和决策的能力.
我国财政部发布的《关于全面推进管理会计体系建设的指导意见》(财会2014〔17〕号)文件中也明确指出"加快会计职能从重核算到重管理决策的拓展",我国会计信息化事业进入一个新的发展阶段.
信息化事业的发展对财会人员或经管类专业学生的知识结构和能力提出了更高的要求.
财会人员或经管类专业的学生如果不掌握一定的信息技术知识,不具备较熟练的计算机应用能力和必要的分析问题、解决问题的能力,以及自我学习的能力,将很难适应未来专业工作的需要.
如何培养适应时代发展的财会专业人才以及企业信息化人才作为一名在中国会计信息化领域从事教学和研究近三十年的老教师,我一直在思考这个问题.
会计信息化需要的是具有多学科交叉的复合型知识结构的人才.
我国高校要培养这样的人才首先要解决专业教育理念的转变、培养目标的正确定位,以及会计信息化师资等问题;在此基础上要制定适应信息化发展的人才培养方案,以及编写适应时代发展的合适的教材.
为此,我们经过充分的调研和精心的准备,推出了这套"会计信息化"系列丛书.
本系列教材首先出版的是《会计信息化基础》和《财务与会计数据处理——以Excel为工具》两本,随后将陆续推出《ERP系统原理与应用》《企业经营决策模拟实训——以财务决策为中心》《会计综合实训——从手工会计到业财一体化》《管理会计信息化》等.
本系列教材具有以下特点.
(1)学历教育、职业教育、岗位对接一体化.
系列教材的读者对象主要为我国普通高校财会专业以及经管类专业的本科生、大专生和在职的财会人员.
对于学历教育要求将基本概念、基本原理和知识架构论述清楚;对于职业教育要求将业务流程和数据之间的传递关系要阐述清楚;对于岗位对接则要求将岗位职责和岗位操作流程表达清楚.
教材的编写自始至终贯穿这个原则,使理论学习与实践有机结合、课程教学与岗位学习有机结合.
(2)教材内容不仅注重信息化实践操作能力的培养,也更注重构建相关学科信息化的完整理论体系.
我们根据长期从事信息化教学的经验体会到:任何应用软件仅仅是从事专业工作的工具,只有对业务工作熟悉了才能使用好工具;因此,教材重点是对业务流程、业务场景阐述清楚,要有基础理论铺垫,使读者不仅知其然,还要知其所以然.
为便于教学,每本教材都配有软件的操作实训(如金蝶K/3系统的操作),但又防止写成软件的操作手册,这样才能做到触类旁通.
(3)教材的创新性.
系列教材由浅入深,内容丰富,满足各个层次的会计信息化教学和读者群的要求.
其中,《会计综合实训——从手工会计到业财一体化》《管理会计信息化》《企业经营决策模拟实训——以财务决策为中心》(暂定)是目前市面上少有的教材,我们的编写思财务与会计数据处理——以Excel为工具II路和结构应该是创新性的.
系列教材基本覆盖了目前高校财会专业以及经管类专业开设的会计信息化相关的课程教学,同时又充分考虑了企业开展会计信息化培训的不同需求,按照从易到难的原则设计各教材的知识体系.
每本教材除了讲授相关课程的信息化理论和实务外,还提供了相应的案例、丰富的习题、上机实训题等,便于教学使用.
(4)充分利用团队的力量,力保教材的质量.
本系列教材由欧阳电平策划、总编和主审,确定每本教材的大纲、编写的思路和原则.
其他作者大部分来自于湖北省会计学会会计信息化专业委员会的高校教师,他们都具有多年信息化方面的教学和实践经验;另外,湖北省会计信息化专业委员会除了有高校委员外,还有浪潮集团湖北分公司等企业委员,他们丰富的实战经验和案例等资源为系列教材提供了素材.
我们利用会计信息化专业委员会这个平台组织教材编写团队,充分调研和讨论大纲,相互交叉审阅书稿,力保教材质量.
在本系列教材的编著过程中,尽管我们进行了多次的调研和讨论,力求做到推陈出新,希望能够做到尽可能完美,但仍然难免存在疏漏和错误,恳请读者多提宝贵意见.
本系列教材在编著过程中,参考和吸收了国内外不少专家学者的相关研究成果并引用了大量的实例,在此一并表示感谢.
欧阳电平2017年夏于珞珈山前言Excel电子表格处理软件以其灵活、方便、友好的操作界面,丰富的各类函数等电子表格数据处理功能深受办公人员的青睐,在财务、会计、审计等管理工作中有着广泛的应用,是财务、会计、审计人员不可或缺的数据处理工具.
高等院校的财会专业与经管类专业也将Excel应用作为基础课之一.
将《财务与会计数据处理——以Excel为工具》作为会计信息化系列丛书的基础教材,体现了Excel作为会计信息化的一种工具和手段的重要性.
Excel的版本经过了多次更新,目前我国用得较多的是Excel2003、Excel2007、Excel2010和Excel2013版本.
本书以Excel2010版作为建模环境和工具.
本书共分10章.
第一章介绍了Excel应用基础,按照Excel是什么、有哪些功能、能够帮助管理者做什么、如何做的思路阐述了什么是Excel模型、如何利用Excel工具建模、输入数据、处理数据、获取结果.
这一章是读者应用Excel的入门知识,介绍了工作簿、工作表、单元格、公式、函数等基础概念及其使用,同时介绍了Excel数据表分析、图表分析方法,使读者对应用Excel的全过程有一个全面的了解.
第二章到第五章介绍了Excel在会计数据处理中的应用,主要包括Excel在会计账务处理、工资核算、固定资产核算、成本核算与管理中的应用.
第六章到第九章介绍了Excel在财务管理中的应用,主要包括Excel在筹资管理、投资管理、收入分配管理、财务分析中的应用.
这八章都是以业务知识和业务流程为主线,介绍如何应用Excel的工具进行业务数据处理.
第十章介绍了Excel的深入应用,包括从文本文件、数据库、网站等获取外部数据,美化图表,宏与VBA,Excel与商务智能等内容.
本书的主要特点如下.
(1)以业务处理的基本原理、基本概念为引导和铺垫.
要利用Excel对相关业务进行数据处理,首先对业务的基础理论知识要掌握,这样才能理解为什么要这样建模,做到理论联系实际.
因此本书开头就用一个简单的案例介绍了Excel建模的基本思路、依据和过程,使读者理解应用Excel的重点是业务基础;第二章到第九章介绍的会计和财务管理的基础理论知识也是运用Excel完成相关业务必备的.
(2)以业务流程和业务场景为主线介绍如何运用Excel进行数据处理.
本书写作的指导思想是:Excel仅仅是一种电子表格的数据处理工具,重点在于财会人员如何利用工具更好地完成会计业务和财务管理业务的数据处理,而不是软件操作本身;因此,以业务场景和业务流程为主线,讲解运用Excel工具完成相关会计和财务管理业务的建模和处理过程具备更有效的指导作用,便于理解和应用.
(3)突出重点与兼顾系统性相结合.
本书力求系统介绍Excel在会计和财务管理中的应用,但在业务选取上不追求面面俱到,而是突出重点.
首先,在章节内容的选择上我们突出了经常性的会计和财务管理业务;鉴于会计的业务处理和财务管理的处理有差异性,尤其是会计业务处理的系统性和缜密性,我们重点讲解了相关会计业务的核算方法和核算流程;而财务管理以常用方法为主力求突出且描述清晰,便于读者能够快速掌握利用Excel完成各种财务与会计数据处理——以Excel为工具IV会计和财务管理业务的技能,同时又不至于陷入Excel繁杂多样的功能之中.
(4)从过程控制的角度介绍了会计账务业务处理的方法和技巧.
由于Excel是一个通用的电子数据表处理工具,不适合于对会计账务业务处理进行过程控制,所以本书利用Excel提供的数据处理方法和技巧,介绍了基于计算机的会计账务业务处理过程及其控制,以提高读者应用Excel处理会计业务数据的技能.
(5)便于学习和上机实践.
本书图文并茂,采用实际操作界面替代繁杂的文字叙述,便于读者学习、模仿、理解.
同时每章以财务和会计工作为主线结合会计、财务管理的业务案例讲解Excel的应用,并在每章附有关键名词、思考题、技能训练和应用实训题,便于读者巩固所学知识.
本书提供案例源文件和PPT教学课件,可通过http://www.
tupwk.
com.
cn/downpage免费下载.
本书由丛书主编欧阳电平教授策划,确定编写思路和原则,组织讨论总体框架以及详细的大纲,最后对全书统一审核、修改、定稿.
陈潇怡担任本书主编,负责拟定章节详细大纲,组织编写与审阅稿件.
本书第一、二、三、十章由陈潇怡编写;第四~七章由李超编写;第八、九章以及附录由夏雨编写.
本书是由欧阳电平教授担任丛书主编的会计信息化系列教材之一,在此对丛书其他编者在本书编写中所提出的宝贵意见表示感谢.
另外,本书的编写还参考和吸收了国内不少学者的相关研究成果,在此一并致谢.
由于我们水平有限,不妥和错误之处敬请各位专家和读者指正.
作者2017年6月于武汉目录第一章Excel应用基础·1第一节Excel基础知识·1一、Excel是什么1二、运用Excel进行业务处理的过程1三、Excel主窗口界面·4四、Excel工作簿5五、Excel工作表8六、Excel单元格和区域·10第二节Excel公式14一、公式的含义与组成·14二、公式的输入、编辑与删除·14三、公式的复制与填充·14四、单元格引用与名称·15五、公式中的运算符·17六、Excel的数据类型·18七、公式使用中的常见错误20第三节Excel函数20一、函数的含义与组成20二、函数的分类·21三、函数的输入和编辑22第四节Excel数据处理与分析工具25一、数据有效性管理·25二、数据排序26三、数据筛选28四、数据分类汇总·30五、数据透视表31第五节Excel图表处理·33一、图表的类型33二、图表的组成35三、创建图表37本章小结·39思考题·39本章实训·40第二章Excel与会计账务处理·41第一节会计账务处理概述41一、会计核算方法·42二、会计账务处理系统的目标与任务·43三、账务处理业务流程与Excel建模·44四、会计账务处理主要使用的Excel函数·46第二节会计账务初始数据的建立48一、创建会计科目表·48二、输入会计科目期初余额·52三、对会计科目期初余额工作表设置保护·53第三节记账凭证的处理·53一、通用记账凭证·54二、记账凭证存储表·56三、输入审核记账凭证57四、记账凭证的查询与输出·60第四节会计账簿数据处理61一、科目汇总表的处理62二、日记账的处理·65三、总账的处理·67第五节报表编制71一、资产负债表·71二、利润表73三、现金流量表·74本章小结·75思考题·76本章实训·76财务与会计数据处理——以Excel为工具VI第三章Excel与工资核算79第一节工资核算系统概述·79一、工资核算系统的目标、任务与核算方法·79二、工资核算业务流程与Excel建模80第二节工资核算初始数据的建立82一、建立职工工资结算明细表·82二、输入工资结算明细表基础数据84第三节工资计算与发放·85一、加班费的计算·86二、缺勤扣款的计算·87三、"三险一金"项目的计算88四、"应发合计"项目的计算88五、"个人所得税"项目的计算88六、计算扣款合计和实发工资·90七、职工工资条设置与打印91第四节工资分类汇总与查询92一、按部门分类汇总与查询92二、工资数据的查询·95本章小结·96思考题·96本章实训·96第四章Excel与固定资产核算·99第一节固定资产核算概述·99一、固定资产的分类·99二、固定资产核算系统的目标与任务100三、固定资产核算业务流程及Excel建模100第二节固定资产核算初始数据的建立101一、建立固定资产基础信息表········101二、设置固定资产卡片模板102三、输入固定资产卡片信息·104四、编制固定资产清单104五、固定资产增减变动处理·105第三节固定资产折旧核算处理105一、固定资产折旧·105二、计提折旧方法及Excel折旧函数模型106第四节固定资产的查询与分类汇总111一、查询新增的固定资产112二、固定资产折旧的分类汇总·112本章小结·114思考题·115本章实训·115第五章Excel与成本管理·117第一节成本管理概述·117一、成本的相关概念与分类·117二、成本核算的方法·118第二节Excel与产品成本核算模型119一、品种法模型·119二、分步法模型·127三、分批法模型·130第三节Excel与产品成本分析模型135一、全部产品成本分析模型·135二、可比产品成本分析模型·136三、产品单位成本分析模型·138第四节运用Excel制作成本费用汇总表139一、汇总同一工作簿的成本数据139二、汇总不同工作簿的成本数据141本章小结·143思考题·143目录VII本章实训·143第六章Excel与筹资管理146第一节筹资管理及资金需求量预测分析146一、筹资的相关概念与分类146二、资金需求量的预测分析148第二节Excel与资金时间价值分析模型151一、资金时间价值概念与计算公式151二、资金时间价值函数及模型153第三节Excel与筹资管理分析模型158一、资本成本分析模型·158二、杠杆分析模型·166三、资本结构模型·170本章小结·174思考题·175本章实训·175第七章Excel与投资管理177第一节投资管理概述·177一、投资的相关概念与分类177二、项目投资决策评价指标178第二节Excel与投资风险管理模型180一、投资风险分析方法·180二、投资风险分析与Excel模型182第三节Excel与投资决策分析模型185一、投资回收期法分析模型185二、净现值法模型·188三、现值指数法模型·191四、内含报酬率法模型·193五、固定资产更新决策模型200本章小结·205思考题·205本章实训·205第八章Excel与收入和利润分配管理207第一节收入和利润分配管理概述207一、收入管理概述·207二、收入管理的主要内容207三、利润分配管理概述208第二节Excel与收入管理的主要分析模型211一、基于Excel的销售预测分析模型211二、基于Excel的产品价格定价模型217第三节Excel与利润分配政策分析模型·222一、剩余股利分析模型222二、固定股利分析模型223三、稳定增长股利分析模型·225四、固定股利支付率分析模型226五、低正常股利加额外股利分析模型227本章小结·228思考题·228本章实训·229第九章Excel与财务报表分析·231第一节财务报表分析概述231一、财务报表分析的主体及分析目的231二、财务报表分析的主要内容和主要方法·232第二节基于Excel的财务报表的结构与趋势分析·232一、结构分析模型·233二、趋势分析模型·240第三节基于Excel的财务比率分析246财务与会计数据处理——以Excel为工具VIII一、企业偿债能力分析模型247二、企业营运能力分析模型248三、企业盈利能力分析模型250四、企业发展能力分析模型251第四节基于Excel的上市公司财务能力分析·252一、每股收益分析模型·252二、市盈率分析模型·253三、市净率分析模型·254第五节基于Excel的企业综合绩效评价分析·255一、沃尔综合评分分析模型255二、杜邦财务分析体系模型256本章小结·258思考题·259本章实训·259第十章Excel应用进阶·261第一节Excel与外部数据获取261一、从文本文件中获取数据261二、从Access数据库中获取数据264三、从网站中获取数据264四、从MicrosoftQuery获取数据265第二节Excel与数据可视化266一、动态图267二、迷你图269第三节宏与VBA271一、宏的录制和使用·271二、VBA编程基础274第四节Excel与商务智能·277一、商务智能(BI)277二、Excel工具——PowerPivot·····279本章小结·281思考题·281附录综合案例282综合案例一投资分析·282综合案例二全面预算·295参考文献·304第一章Excel应用基础【学习目标】通过本章的学习,读者要知悉:Excel是什么有哪些功能能帮助管理者做什么如何做掌握应用Excel处理业务(数据)的过程以及建模的概念;熟悉Excel的操作方法;掌握Excel公式和函数的概念和使用;掌握应用Excel进行简单的数据处理(排序、分类汇总等)和数据分析的方法;掌握应用Excel对数据进行图形分析的方法.
第一节Excel基础知识一、Excel是什么Excel是微软公司(MicrosoftCorporation)开发的用于数据表处理和图表处理的应用软件,是一种办公用的工具软件,是MicrosoftOffice的套件之一.
Excel自推出以来,至今已发布了2003、2007、2010、2013多个版本,本书以Excel2010为基础来介绍相关的内容.
Excel以其强大的电子表格数据处理功能、方便友好的操作界面,得到广大用户的普遍欢迎.
其不仅提供了日常工作所需的表格处理功能,还提供了丰富的函数、卓越的图表功能、数据分析功能、辅助决策功能和通过Web实现协作和信息共享等功能.
Excel在财务、会计等管理工作中的应用主要有两种方式:数据表方式和图表方式.
数据表方式主要以表格的形式通过设计数据模型、采集数据、对模型求解形成数据报告、分析评价等过程完成业务处理;图表方式是以图形、图表形式把数据表示出来的方法.
两种方式相互结合就可以在完成数据处理、分析的同时以直观、清晰的形式把处理的结果表示出来.
Excel为各行各业的管理者提高工作效率并提高数据处理和数据管理能力,以及应用信息的能力提供了技术支持.
Excel是当前财务、会计、审计等管理人员应掌握的必不可少的工具之一.
与大型财务软件系统相比,Excel更适合小型、灵活的数据处理和办公环境,尤其是中小企业的财务管理和会计审计数据处理.
二、运用Excel进行业务处理的过程(一)运用Excel进行业务处理的步骤运用Excel进行业务(或业务数据)处理要按照一定的步骤进行.
财务与会计数据处理——以Excel为工具2(1)明确要处理的业务和达到的目标,即首先要明确运用Excel最终要解决业务处理中的哪些问题及实现的目标要求.
(2)依据业务相关的理论确定解决该问题应该采用的方法(计算公式或模型),以及该方法如何在Excel中实现,即明确模型的具体形式.
(3)利用Excel工具建立起已经确定的业务处理模型,这是应用能否成功的关键.
一般要建立的模型包括原始数据、业务处理的数学公式、模型的约束条件等几个部分.
其中,原始数据是指需要进行分析和处理的数据,这些数据可以是用户手工输入的数据,也可以是利用数据获取方法从外部数据库获得的数据;这些数据可以存放在同一个工作簿的同一个工作表内,也可以存放在同一个工作簿的不同工作表内,还可以存放在不同工作簿的工作表中.
数学公式是用数学语言表示的对问题进行定量分析的公式,模型中必须用Excel的工具把数学公式表示出来.
模型的约束条件是指保证数学公式在业务中有效的一些前提要求.
(4)利用Excel工具自动完成模型求解.
因为通过数学公式已经建立了数据之间的动态链接,所以只要原始数据发生改变,系统就可以自动或在用户的控制下按公式进行计算,并更新计算结果.
(5)把模型的计算结果用适当的形式表示出来,对计算结果进行分析、评价,给出业务处理结果和建议,并将业务处理的结果和建议发布出来.
(二)运用Excel进行业务处理举例【例1-1】某公司准备发行面值为500万元的5年期债券,票面利率为9%,按年付息,到期一次还本,发行费用率为4%,公司的所得税税率为25%.
分别计算平价、溢价(发行价格为600万元)发行该债券的资本成本.
根据处理步骤,首先要弄清业务处理的问题和目标,以及该业务本身的计算公式或计量模型.
该业务属于企业长期债券筹资决策内容,筹资的资本成本是重要考虑的因素.
根据债券筹资相关理论,发行债券的成本主要指债券利息和筹资费用.
按照一次还本、分期付息的方式,长期债券资本成本(不考虑资金的时间价值)的计算公式为:(1)(1)bbbITKBF=式中:Kb—债券资本成本;Ib—债券票面利息;T—所得税税率;B—债券筹资总额;Fb—债券筹资费用率.
以上长期债券资本成本计算公式比较简单,可以利用在Excel单元格中输入计算公式的方式建立Excel模型,如图1-1所示.
运用Excel工具建立以上债券筹资业务处理模型的步骤如下.
(1)建立Excel工作表,并在表中输入债券筹资的相关数据,如图1-1所示,分别在单元格C2、C3、C4、C5、C6中输入债券价格、债券面值、债券票面利率、债券筹资费用率和所得税税率(即模型的原始数据).
(2)在计算债券成本数据的单元格中输入公式,可直接在C7单元格中输入公式"=C3*C4*(1-C6)/(C2*(1-C5))",也可在公式输入栏输入,如在D7中输入"=D3*D4*(1-D6)/(D2*(1-D5))",如图1-1所示,即可计算出该项债券筹资的平价、溢价发行的资本成本分别为7.
03%和5.
86%.
第一章Excel应用基础3图1-1长期债券资本成本的Excel模型Excel作为一种电子表格数据处理工具,还提供了非常丰富的函数(如统计函数、财务函数等)将一些常规业务的数学公式、数据处理模型进行了预先定义,可以帮助管理者简化业务建模和数据处理过程.
【例1-2】某女士2016年年末存入银行5万元,年利率为3.
75%,假设年利率保持不变,每年年末将利息转存本金(复利计算),求:第5年年末该女士可以从银行取回多少钱复利计算公式如下:(1)nfpVVI=+式中:Vf—n年后的终值;Vp—现值或期初数;I—利率,一般为年利率;n—时间周期数,一般为年数.
对例1-2用Excel单元格中输入计算公式的方式建立Excel模型(C5单元格),以及用Excel函数调用方式建立Excel模型(C6单元格)1,如图1-2所示,计算的结果一致.
图1-2用计算公式或函数建模通过以上应用Excel处理长期债券筹资业务以及计算本利和业务的过程可以看出:建立Excel业务处理模型,是在弄清业务目标、求解问题、计算模型、处理步骤的基础上,利用Excel工具将业务计算模型中的常量、自变量和因变量,以及约束条件(如果有)在Excel工作表中用单元格之间的数据勾稽关系和运算公式表达清楚.
由于不同业务的求解问题、处理过程和复杂度、难易度不同,一张Excel工作表、一个计算公式不足以表达一个复杂的业务模1.
FV是一个财务函数,用于根据固定利率计算投资的未来值.
语法:FV(rate,nper,pmt,[pv],[type]).
参数含义如下:rate,必需,各期利率;nper,必需,年金的付款总期数;pmt,必需,各期所应支付的金额,在整个年金期间保持不变,通常pmt包括本金和利息,但不包括其他费用或税款,如果省略pmt,则必须包括pv参数;pv,可选,现值或一系列未来付款的当前值的累积和,如果省略pv,则假定其值为0,并且必须包括pmt参数;type可选,数字0或1,用以指定各期的付款时间是在期初还是期末,如果省略type,则假定其值为0.
关于函数的详细介绍可参见本章函数一节.
财务与会计数据处理——以Excel为工具4型,这样就需要多张Excel工作表和多个运算公式、多个步骤才能完成建模.
显然,要利用Excel工具建立业务处理模型,前提是管理者对业务本身要解决的问题、处理方法、计算模型、处理流程等很清楚,这样才能更好地利用Excel完成业务建模和操作处理.
另外,为方便使用,Excel提供了丰富的各类函数,帮助用户完成建模,这些都有待后续进行深入的学习.
三、Excel主窗口界面在了解了Excel建模的基本思路后,接下来对Excel的基本操作界面和功能做一个总体的介绍.
Excel2010的主窗口界面如图1-3所示.
(二)功能区(三)编辑栏(一)快速访问工具栏(四)工作表编辑区活动单元格(五)工作表标签行号(六)名称框列标状态栏视图按钮滚动条(七)单元格标题栏图1-3Excel2010主窗口界面(一)快速访问工具栏该工具栏位于界面的左上角,包含一组用户使用频率较高的工具,如"保存""撤销"和"恢复".
用户可单击"快速访问工具栏"右侧的倒三角按钮,在展开的列表中选择其中显示或隐藏的工具按钮.
快速访问工具栏右侧是标题栏,显示工作簿的名字,工作簿是用户使用Excel进行操作的主要对象和载体.
(二)功能区功能区位于标题栏的下方,是一个由若干个选项卡组成的功能区,又称功能选项卡区域.
Excel2010将用于处理数据的所有命令组织在不同的选项卡中,单击不同的选项卡标签,可切换功能区中显示的工具命令.
在每一个选项卡中,命令又被分类放置在不同的组中.
组的右下角通常都会有一个对话框启动器按钮,用于打开与该组命令相关的对话框,以便用户对要进行的操作做出进一步的设置.
(三)编辑栏编辑栏位于功能区下方,主要用于输入和修改活动单元格中的数据或公式.
当在工作表的某个单元格中输入数据时,编辑栏会同步显示输入的内容.
第一章Excel应用基础5(四)工作表编辑区工作表编辑区用于显示或编辑工作表中的数据.
此区域由单元格或者若干个单元格构成的区域组成.
(五)工作表标签窗口左下角是工作簿所包含的工作表名的标签,默认名称为Sheet1、Sheet2、Sheet3、…,单击不同的工作表标签可在工作表间进行切换.
(六)名称框名称框显示单元格或单元格区域的名称,标明目前活动单元格的地址,默认情况下由活动单元格的列标和行号构成.
也可以通过名称管理器对活动区域的名称进行修改,方便数据的识别和管理.
名称管理器的使用在第二节单元格和区域的介绍部分还会进一步说明.
(七)单元格单元格是Excel工作簿的最小组成单位,所有的数据都存储在单元格中.
工作表编辑区中每一个长方形的小格就是一个单元格,每一个单元格都可用其所在的行号和列标进行标识,如A1单元格表示位于第A列第1行的单元格.
Excel工作簿就像是我们日常生活中的账本,而账本中的每一页账表就是工作表,账表中的一格就是单元格.
一个工作簿由若干工作表组成,一个工作表又由若干单元格组成.
工作簿、工作表和单元格的相关概念和操作是学习Excel首先要熟悉的内容.
四、Excel工作簿在Excel中用来储存并处理数据的文件叫作工作簿,其文件名后缀与版本和类型有关.
Excel2010默认的文件名后缀为.
xlsx.
每一个工作簿可以拥有许多的工作表.
工作簿、工作表是Excel的主要操作对象.
下面介绍对工作簿的创建、保存、恢复等基本操作.
(一)创建工作簿首次应用Excel进行业务处理时必须新建一个工作簿,给工作簿命一个名字,然后在工作簿中设计需要的工作表.
编辑或应用某个已经存在的工作表时,必须先打开相应的工作簿.
创建工作簿可分为如下两种方式.
1.
自动创建当启动Excel时,系统会默认创建一个Excel工作簿,自动命名为Book1-MicrosoftExcel.
2.
手工创建也可以通过单击Office按钮,打开按钮菜单;选择其中的"新建"命令,打开"新建工作簿"对话框,如图1-4所示;在该对话框的"模板"列表中选择是否选用模板以及选用哪一种模板.
例如,可以选择"空白文档和最近使用的文档""已安装的模板""我的模板""根据现有内容新建"以及"MicrosoftOfficeOnline"栏下的系列模板.
再单击该对话框右下角的"创建"按钮,则按某某模板创建一个新的工作簿.
如果选择"空工作簿"新建工作簿,就是采用默认的模板创建一个空的工作簿(或是直接财务与会计数据处理——以Excel为工具6采用默认选项),则该工作簿默认包含了3张工作表,即Sheet1、Sheet2、Sheet3,这时就可以在新的工作簿中进行工作了.
图1-4"新建工作簿"对话框(二)保存工作簿对新建或已有的工作簿进行编辑设置后,需要及时保存,否则所做的工作和数据不能保存下来.
保存工作簿的方法有如下几种.
1.
手工操作保存工作簿可以通过以下几种手工操作方法保存工作簿.
(1)在功能区中依次单击"文件"→"保存"或"另存为"按钮.
(2)单击"快速启动工具栏上"的"保存"按钮.
(3)在键盘上按Ctrl+S组合键.
(4)在键盘上按Shift+F12组合键.
此外,在退出Excel时系统会自动弹出警告信息,提示用户是否要保存,单击"保存"按钮就可以保存此工作簿.
工作簿有多种类型.
当保存一个新的工作簿时,可以在"另存为"对话框的"保存类型"下拉菜单中选择所需保存的Excel文件类型,如图1-5所示.
在Excel2010中,.
xlsx为普通Excel工作簿;.
xlsm为启用宏的工作簿(当工作簿中包含宏代码时,选择这种类型);.
xlsb为二进制工作簿;.
xls为Excel97-2003工作簿,无论工作簿中是否包含宏代码都可以保存为这种Excel2003兼容的文件格式.
2.
自动保存工作簿由于电源系统不稳定、Excel程序本身故障、用户操作不当等原因,Excel程序可能会在用户保存文档之前就意外关闭,使用自动保存功能可以减少这些意外情况所造成的损失,尤其对于财务和会计等关键信息的保存有非常重要的作用.
Excel2010版本中,自动保存功能得到进一步增强,不仅会自动生成备份文档,而且会根据间隔时间需求生成多种文件版本.
当Excel程序意外崩溃而退出或者用户没有保存文档就第一章Excel应用基础7关闭,工作簿就可以选择其中的某一个版本进行恢复.
图1-5Excel工作簿可保存的文件类型设置自动保存的方法如下.
(1)单击Excel菜单栏中的"文件"选项卡,选择"Excel选项"对话框中的"保存"选项卡,如图1-6所示.
图1-6Excel自动保存设置(2)勾选"保存工作簿"中的"保存自动恢复信息时间间隔"复选框(默认为勾选,即所谓的自动保存),在右侧的微调框内设置自动保存的时间间隔,默认为10分钟.
(3)单击右下角的"确定"按钮,保存设置并退出"Excel选项"对话框.
财务与会计数据处理——以Excel为工具8设置开启了自动保存功能之后,在工作簿文档的修改编辑过程中Excel会根据保存间隔时间的设置自动生成备份副本.
在Excel功能区中单击"文件"→"信息"就可以查看到这些通过自动保存生成的副本信息,如图1-7所示.
图1-7自动保存文件信息查询(三)恢复工作簿文件恢复工作簿文档的方式,根据Excel程序关闭的情况不同分为两种.
第一种情况是用户手动关闭Excel程序之前没有保存文档.
这种情况通常是由于误操作造成的,要恢复之前所编辑的状态,可以在重新打开目标工作簿文档后,在功能区上依次单击"文件"→"信息",右侧会显示此工作簿最近一次自动保存的文档副本.
单击此文件版本即可打开此副本文件,并在编辑栏上显示提示信息,单击"还原"按钮即可将工作簿文档恢复到此版本.
第二种情况是Excel程序发生断电、程序崩溃等情况而意外退出.
在Excel工作窗口非正常关闭的情况下,重新启动Excel时会自动出现文档恢复任务窗口.
在该任务窗口中,用户可以选择打开Excel自动保存文件版本或者选择打开原始文件版本及用户最后一次手动保存时的文件状态.
虽然自动保存功能有很大的改进,但并不能完全代替用户的手动保存操作.
在使用Excel进行财务数据管理的过程中养成良好的保存和备份习惯才是避免重大损失的有效途径.
五、Excel工作表工作表是数据输入、处理以及制作图表的基本操作界面,每个工作簿中包含多个工作表,最多不超过255个.
在Excel2010中工作表的最大行号为1048576,最大列号为16384.
一般来说,用户对工作表的基本操作包括插入新工作表、重命名工作表、删除工作表、移动和复制工作表等.
下面对这些基本操作进行介绍.
第一章Excel应用基础9(一)插入新工作表工作表的创建通常分为两种情况:一种是随着工作簿的创建而创建;另一种是从现有工作簿中创建新的工作表.
用户在使用过程中,如果工作簿比较复杂,内容比较多,可能默认的工作表还不够用,这个时候就需要向工作簿中插入新的工作表.
在Excel中,插入一个新工作表的方法如下:右击工作表标签,打开快捷菜单;选择"插入"命令,打开"插入"对话框;在"常用"选项卡中选择"工作表",然后单击"确定"按钮即可插入一个新的工作表.
或者按Shift+F11组合键,可直接添加一个新的工作表.
(二)重命名工作表工作表的名称是按照"Sheet+序号"的方式自动命名的,但是这种命名方式不能体现工作表的内容,也不便于查找.
这时可以根据需要将工作表重命名.
其操作方法是:右击要更改名称的工作表标签,在弹出的快捷菜单中选择"重命名"命令,或者直接双击工作表标签,此时工作表的名称呈高亮状态,在其中直接输入新的名称,然后按Enter键即可完成工作表的重命名.
(三)删除工作表当不再需要一个工作表时,可将其删除.
其操作方法为:右击要删除的工作表的标签,在打开的快捷菜单中选择"删除"命令,工作表即被删除.
如果要一次删除多个工作表,可以按住Shift键单击最后一个工作表标签,这个标签和活动工作表标签之间的所有工作表都将被删除.
(四)移动和复制工作表有时候需要把工作簿中的工作表重新调整顺序,此时只要单击要移动的工作表标签,并按住鼠标左键,使其出现一个向下的箭头,然后直接将工作表拖动到相应的位置即可.
复制工作表与移动工作表的操作相似.
单击需要复制的工作表标签之后,按住Ctrl键,在要复制的工作表标签上按住鼠标左键并拖动,就可以复制工作表到指定位置,之后松开鼠标键即可.
如果希望将工作表复制到另外一个工作簿内,则要求两个工作簿同时打开.
或者在工作表标签上右击,在打开的快捷菜单中选择"移动或复制工作表"命令即可.
下面举例说明工作簿和工作表的建立.
【例1-3】假设A公司要运用Excel进行工资核算,其工资结算明细表项目如表1-1所示.
建立"工资核算"工作簿和工资结算工作表.
表1-1A公司工资结算明细表员工编号姓名性别部门级别基本工资岗位工资加班费应发合计三险住房公积金缺勤扣款所得税扣款合计实发合计财务与会计数据处理——以Excel为工具10操作步骤如下.
(1)新建Excel工作簿,并为工作簿命名"工资核算".
(2)给工作簿中的工作表标签"Sheet1"重新命名为"A公司工资结算明细表".
(3)选择B1至P1单元格,单击"开始"选项卡中的"合并后居中"按钮;输入表头"A公司工资结算明细表".
(4)选中B2至P18单元格区域,单击"开始"选项卡中的"下框线"按钮,给表格加上表格线并可以按表格设计需求,使用下拉菜单调整表格线条粗细和线形.
(5)在B2至P2单元格手工输入表头项目名称,并在"开始"选项卡的"对齐方式"和"字体"区域调整对齐方式和字体等.
(6)使用快捷键Ctrl+S保存创建好的工作簿"工资核算.
xlsx",如图1-8所示.
下框线更改工作表"sheet1"名称图1-8新建工资核算工作簿和A公司工资结算明细表六、Excel单元格和区域Excel单元格和区域是工作表最基础的构成元素和操作对象.
行和列相互交叉所形成的一个一个格子被称为单元格;而同一工作表中若干个相邻单元格连接成的矩形称为区域.
单元格是工作表最基础的组成元素.
用户可以在单元格内输入和编辑数据,单元格中可以保存的数据包括数字、文本和公式等内容.
除此以外用户还可以为单元格添加批注以及设置多种格式.
(一)单元格或区域的选择要对单元格或区域进行操作就必须先选中单元格.
在Excel中可以选择单个单元格,也可以一次选择多个单元格(即区域).
如果要选择单个单元格,只要单击这个单元格即可.
如果要连续选择多个单元格,除了可以用拖曳鼠标选择外,还可以先选中第一个单元格,然后按住Shift键选择最后一个单元格,这样中间区域的单元格就都被选中了.
如果要选择多个不连续的单元格,可以按住Ctrl键依次单击这几个单元格.
在任一工作表中,选中任意单元格,在键盘上按Ctrl+↓组合键,就可以迅速定位到选定第一章Excel应用基础11单元格所在列上,向下连续非空的最后一行,如单元格所在列下方均为空,则定位到当前列的1048576行.
为了方便工作表内容的复制,还可以通过单击工作表编辑区左上角,行号1和列标A交叉点的符号(向右下的三角形),来快速选中整个工作表编辑区.
(二)在单元格或区域中输入数据1.
输入文本(字符型)数据向单元格输入的数据可以是文本(字符型)或数值型.
文本可以是汉字、英文字母,也可以是文本性质的数字,每个单元格最多可以输入32767个字符.
双击单元格即可输入数据;或者在编辑栏中输入相应的内容,再单击左侧的(勾)按钮完成输入.
在默认情况下,输入文本内容会与单元格左侧对齐.
以例1-3为例,在B3单元格中输入0001,Excel会把输入自动默认为数值格式而显示为1.
为了在单元格中输入文本性质的数字,可在数字前输入一个单引号,Excel就将该数字作为文本处理,同时单元格左上角出现绿色三角标志.
向选中区域内输入数据的方法与向单元格内输入数据的方法相同,如图1-9所示.
选中区域反显,而区域内正在输入数据的单元格正常显示,对区域内单元格输入完成后,单击Enter键,活动单元格自动跳转到同列的下一行.
当选中区域内的一列输入完成后,单击Enter键,活动单元格跳转到选中区域下一列的首行.
当区域内最后一个单元格输入完成后,再次单击Enter键,活动单元格位置跳转到选中区域的首行首列.
单击此处可全选整张工作表选中区域被反显B3单元格输入'0001图1-9向区域内输入数据2.
输入数值型数据Excel中的数值型数据是指可用于计算的数据,常见的有整数、小数、逻辑值等.
与文本数据相区别,默认情况下输入的数值会自动右对齐.
如果输入的数字超过11位,将自动变成科学计数法形式,如果单元格的宽度不足以容纳输入的数字,将以"####"表示,如图1-10所示.
出现"####"表示时,将光标移至列标上方,直至变为时,选中需要调整宽度的列,再将光标移至列标之间,直至变为双箭头时,双击,则Excel会自动依据单元格内容调整单元格宽度.
数值过长宽度不足以显示图1-10快速调整单元格列宽财务与会计数据处理——以Excel为工具123.
填充相同的数据继续以例1-3为例,在"A公司工资结算明细表"中,选中输入起始数据的单元格,输入数据,然后把光标放在该单元格的右下角,这时光标会变成"+"标记.
再向下或者向右拖动鼠标,这样所经过的单元格就以等差数列方式填充单元格中的内容.
例如从C1到C20都输入0001,先在C1单元格中输入0001,然后将光标放置在单元格右下角,变成"+"后向下拖动鼠标至C20单元格,如图1-11所示.
如果同时按住Ctrl键下拉,则在经过的单元格中填入相同的数据,效果如图1-12所示.
图1-11直接下拉填充效果图1-12按住Ctrl键下拉填充效果4.
填充等差数列要想使用自动填充功能输入等差数列的数据,除了上例中按住Ctrl键下拉填充外,还可用如下步骤填充.
(1)在输入起始数据时,至少输入前两个数据.
例如在A1和A2单元格分别输入1和3.
(2)选中这两个单元格,并把光标移到单元格的右下角,当光标变成"+"标记时,按住鼠标左键向下拖动,填充完所选区域后松开鼠标即可,效果如图1-13所示.
当然,还可以使用自动填充按钮的对话框来对单元格进行快速填充.
或者使用"数据"第一章Excel应用基础13选项卡中的"快速填充"按钮进行填充.
图1-13数据的快速填充(三)单元格或区域的插入与删除有时需要在工作表中插入或删除单元格或多个单元格(区域).
首先选中某个单元格,然后打开功能区的"开始"选项卡,在"单元格"组中单击"插入"→"插入单元格",如图1-14所示,打开"插入"对话框;选择其中的"活动单元格下移"单选按钮,单击"确定"按钮,即可在这几个单元格的上方各插入一个单元格.
或者选中单元格后单击鼠标右键,在弹出的快捷菜单中选择"插入"命令,同样会弹出"插入"对话框.
当要一次插入多个单元格(即区域)时,所选择的单元格数目和即将要插入的单元格数目要一致.
图1-14插入单元格删除单元格时,选择要删除的单元格区域并单击鼠标右键,在弹出的快捷菜单中选择"删除"命令,在弹出的"删除"对话框中选择要删除的方式,单击"确定"按钮即可完成.
财务与会计数据处理——以Excel为工具14第二节Excel公式一、公式的含义与组成Excel的公式是以"="号为引导,通过运算符按照一定的顺序组合进行数据运算处理的等式.
简单的公式有加减乘除等计算,复杂的公式则可能会包含函数以及各种引用等内容.
使用公式是为了有目的地计算结果,或依据计算结果改变其所作用单元格的条件格式、设置规划模型等.
要熟练地使用公式,就要对Excel中公式的基本结构进行了解.
Excel公式的组成要素有等号、运算符号、常量、单元格引用、函数和名称等.
以等号开头输入Excel单元格中的以下内容,都可看作为公式.
"=5+2"是包含常量运算的公式;"=A3*$B$5"是包含单元格引用的公式;"=(语文成绩+数学成绩)÷2"是包含名称的公式;"=SUM(D:D)"是包含函数的公式.
二、公式的输入、编辑与删除单元格的数据类型被事先设置为"文本",当以"="号作为开始在单元格中输入时,Excel将自动变为输入公式状态,以"+""-"号作为开始输入时,系统会自动在其前面加上等号变为输入公式状态.
在输入公式状态下,鼠标选中其他单元格区域时,被选区域将作为引用自动输入公式中.
按下Enter键或者Ctrl+Shift+Enter组合键,可以结束普通公式和数组公式f(x)输入或编辑状态.
如果需要对已有公式进行修改,可以通过以下3种方式进入单元格编辑状态.
第一种方式,选中公式所在单元格,并按下F2键.
第二种方式,双击公式所在单元格(可能光标位置不会处于公式起始位置).
第三种方式,选中公式所在单元格,单击列标上方的编辑栏.
选中公式所在单元格,按Delete键即可清除单元格中的全部内容,或者进入单元格编辑状态后,将光标放置在某个位置并使用Delete键或Backspace键删除光标后面或前面的公式部分内容.
当需要删除多单元格数组公式时,必须选中其所在的全部单元格再按Delete键.
三、公式的复制与填充当需要使用相同的计算方法时,可以像一般单元格内容一样,通过"复制"(快捷键:Ctrl+C)和"粘贴"(快捷键:Ctrl+V)的方法进行操作,而不必逐个单元格编辑公式.
此外,可以根据表格的具体情况使用不同的操作方法复制与填充公式,提高效率.
包含单元格引用的公式进行复制与填充时,由于单元格引用方式的不同,公式的运算内容和返回结果可能会发生很大变化.
因此,在了解公式复制与填充的操作方法后,还需要对单元格引用的方式有充分的理解和认识,才能利用Excel工具,更好地为财务会计数据处理服务.
第一章Excel应用基础15四、单元格引用与名称单元格是工作表的最小组成元素,以左上角第一个单元格为原点,向下、向右分别为行、列坐标的正方向,由此构成单元格在工作表上所处位置的坐标集合.
在公式中使用坐标方式表示单元格在工作表中的"地址"实现对存储于单元格中的数据的调用,这种方法称为单元格引用.
(一)单元格引用在公式中的引用具有以下关系:如果单元格A1包含公式B1,那么B1就是A1的引用单元格,A1就是B1的从属单元格.
从属单元格与引用单元格之间的位置关系称为单元格引用的相对性,可分为3种不同的引用方式,即相对引用、绝对引用和混合引用.
1.
相对引用当复制公式到其他单元格时,Excel保持从属单元格与引用单元格的相对位置不变,称为相对引用.
例如使用A1引用样式时,在B2单元格输入公式"=A1".
当向右复制公式时,将依次变为:"=B1""=C1""=D1"等,当向下复制公式时,将依次变为"=A2""=A3""=A4",始终保持引用公式所在单元格的左侧1列、上方1行的位置.
2.
绝对引用当复制公式到其他单元格时,Excel保持公式所引用的单元格绝对位置不变,称为绝对引用,例如在A1引用样式中,在B2单元格输入公式"=$A$6".
则无论公式向右还是向下复制,都始终保持为"=$A$6"不变,固定在A行第6列.
当选中公式中的单元格地址时,单击功能键F4,则地址可以自动在绝对引用和相对引用间切换.
【例1-4】如图1-15所示对A列与B列求和.
在C3单元格中输入计算公式"=$A$2+$B$2",在D3单元格中输入计算公式"=$A$2+B2",在E3单元格中输入计算公式"=A2+B2";把C3至E3单元格的公式内容向下复制到同列其他单元格中,计算结果会如何变化图1-15绝对引用、相对引用计算结果比较财务与会计数据处理——以Excel为工具163.
混合引用当复制公式到其他单元格时,Excel仅保持所引用单元格的行或列方向之一的绝对位置不变,而另一方向位置发生变化,这种引用方式称为混合引用,可分为行绝对列相对引用和行相对列绝对引用.
例如,在A1引用样式中,若在C3单元格中输入公式"=$A5",则公式向右复制时始终保持为"=$A5"不变,向下复制时行号将发生变化,即是行相对列绝对引用.
4.
其他工作表区域的引用若希望在公式中引用其他工作表的单元格区域,则可在公式编辑状态下,通过单击相应的工作表标签,然后选取相应的单元格区域.
跨表引用的表示方式为:"工作表名+感叹号+引用区域"(使用的符号均为英文状态下的半角符号),如公式"=科目编码与名称!
$B$3∶$C$84"为引用"科目编码与名称"工作表中B3到C84区域的内容.
5.
其他工作簿中的工作表区域的引用当引用的单元格与公式所在单元格不在同一工作簿中时,其表示方式为:"[工作簿名称]工作表!
单元格引用".
当被引用单元格所在工作簿关闭时,公式中将在工作簿名称前自动加上文件的路径.
当路径或工作簿名称、工作表名称之一包含空格或相关特殊符号时,感叹号之前的部分需要使用一对半角单引号包含.
(二)名称的使用在Excel中,名称是一种较为特殊的公式,多数由用户自行定义,也有部分名称可以随创建列表、设置打印区域等操作自动产生.
作为一种特殊的公式,名称也是以"="号开始,可以由常量数据、常量数组、单元格引用、函数与公式等元素组成,并且每个名称都具有一个唯一的标识,可以方便在其他名称或公式中调用.
与一般的公式所不同的是,普通公式存在于单元格中,名称保存在工作簿中,并在程序运行时存在于Excel的内存中,且通过其唯一标识(即名称的命名)进行调用.
合理地使用名称,可以方便编写公式,有增强公式的可读性、方便于公式的统一修改、可代替需重复使用公式以简化公式、可代替单元格区域存储常量数据等优点.
例如,将存放在B3∶B12单元格区域的"商品价格"数据定义为"价格",使用"=AVERAGE(价格)"和"=AVERAGE(B3∶B12)"两个公式都可以求平均价格,但显然前者比后者更易于理解其意图.
再例如,在工资表中有多个公式都使用1200作为基本工资以乘以不同奖金系数进行计算,当基本工资额发生改变时,要逐个修改相关公式将较为繁琐.
如果定义一个"基本工资"的名称并带入公式中,则只需修改基本工资名称代表的内容即可.
1.
新建名称Excel主要提供以下两种方式来新建名称.
一种是通过选中区域,以直接命名的方法新建名称;另一种是通过打开"新建名称"对话框,来实现对名称的新建.
Excel又提供了以下三种打开新建名称对话框的方法:①单击"公式"选项卡中的"定义名称"按钮;②单击"公式"选项卡中的"名称管理器"按钮,在"名称管理器"对话框中单击"新建"按钮;③使用Ctrl+F3快捷键打开"名称管理器"对话框,单击"新建"按钮,如图1-16所示.
第一章Excel应用基础171.
对选中区域进行命名2.
使用名称管理器新建图1-16使用名称管理器新建名称【例1-5】如图1-17所示,使用名称管理器命名数据区域A列和B列,在F列填入公式"=A列+B列"对A、B列进行求和,结果会发现此时的数组随着行与列的变化而变化.
图1-17使用名称2.
删除与修改名称名称的修改与删除方法与新建名称方法类似,单击"公式"选项卡中的"名称管理器"按钮,在"名称管理器"中选中需要编辑或删除的名称,再单击对话框中的"编辑"或"删除"按钮即可.
五、公式中的运算符公式中需要使用运算符号,Excel运算符号主要包括四种:算术运算符、比较运算符、文本运算符和引用运算符.
算术运算符:主要用于加、减、乘、除、百分比以及乘幂等常规的算术运算.
财务与会计数据处理——以Excel为工具18比较运算符:用于比较数据的大小,包括对文本或数值的比较,返回值为"TRUE"或"FALSE".
文本运算符:主要用于将文本字符或字符串进行连接和合并.
引用运算符:这是Excel特有的运算符,主要用于在工作表中进行单元格的引用.
通常情况下,Excel按照从左向右的顺序进行公式运算,当公式中使用多个运算符时,Excel将根据各个运算符的优先级进行运算,对于同一级次的运算符,则按从左向右的顺序运算.
具体的优先顺序如表1-2所示.
表1-2公式中运算符与优先级列表类别运算符运算功能优先级引用:区域运算符,用于引用单元格区域1引用,联合运算符,将多个引用合并2引用空格交叉运算符,用于引用两个单元格区域的重叠部分3算数()括号4算数-负号5算数%百分号6算数^乘方7算数*和/乘、除8算数+和-加、减9文本&文本链接,将两个文本连接起来合并成一个文本10逻辑等于、小于、大于、小于等于、大于等于、不等于11数学计算式中使用小括号()、中括号[]和大括号{}以改变运算的优先级别,在Excel中均使用小括号代替,而且括号的优先级将高于上表中的所有运算符.
如果在公式中使用多组括号进行嵌套,其计算顺序是由最内层的括号逐级向外进行运算.
在公式中使用的括号必须成对出现,虽然Excel在结束公式编辑时会做出判断自动补充、修正,但修正结果并不一定是用户所期望的.
六、Excel的数据类型在介绍Excel公式后,还需要对Excel的数据类型做一个简单的了解.
在单元格中可以输入和保存的数据包括4种基本类型:数值、日期、文本和公式.
除此以外,还有逻辑值、错误值等一些特殊的数值类型.
(一)数值数值是指所有代表数量的数字形式,例如企业的产值和利润、商品的价格、员工的工资等.
数值可以是正数,也可以是负数,但是都可以用于进行数值计算,例如加、减、求和、求平均值等.
除了普通的数字以外,还有一些带有特殊符号的数字也被Excel理解为数值,例如百分号(%)、货币符号(如)、千分间隔符(,)以及科学计数符号(E)等.
在自然界中,数字的大小是无穷无尽的,但是在Excel中,由于软件系统自身的限制,对于所使用的数值也存在着一些规范和限制.
第一章Excel应用基础19Excel可以表示和存储的数字最大可精确到15位有效数字.
对于超过15位的整数数字,Excel会自动将15位以后的数字变为零,例如,123456789123456789(18位),会自动变为123456789123456000.
对于大于15位有效数字的小数,则会将超出的部分截去.
因此,对于超出15位有效数字的数值,Excel无法进行精确的计算或处理,例如无法比较两个相差无几的20位数字的大小,无法用数值形式存储18位的身份证号码,等等.
用户可以通过使用文本形式来保存位数过多的数字,来处理和避免上面这些情况,例如在单元格中输入18位身份证号码的首位之前加上单引号"'",或者先将单元格设置为文本类型后,再输入身份证号码.
对于一些很大或者很小的数值,Excel会自动以科学记数法来表示(用户也可以通过设置将所有数值以科学计数法表示),例如,123456789123456会以科学计数法表示为1.
23457E+14,即为123457*1014之意,其中代表10的乘方大写字母"E"不可以省掉.
进行财务会计数据处理时经常会遇到较大金额的数据,同时对数据的精度也有要求,因此需要特别留意Excel能处理数值类型数据的精度.
(二)日期和时间在Excel中,日期和时间是以一种特殊的数值形式存储的,这种数值形式被称为"序列值".
在Windows系统上所使用的Excel版本中,日期系统默认为"1900年日期系统",即以1900年1月1日作为序列值的基准日,当日的序列值计为1,这之后的日期均以距基准日期的天数作为其序列值,例如1900年1月15日的序列值为15,2007年5月1日的序列值为39203.
在Excel中可表示的最后两个日期是9999年12月31日,当日的序列值为2958465.
由于日期存储为数值的形式,因此它继承着数值的所有运算功能,例如日期数据可以参与加、减等数值运算.
日期运算的实质就是序列值的数值运算,例如要计算两个日期之间相距的天数,可以直接在单元格中输入两个日期,再用减法运算的公式来求得.
日期系统的序列值是一个整数数值,一天的数值单位就是1,那么1小时就可以表示为1/24天,1分钟就可以表示为1/(24*60)天,等等.
一天中的每一个时刻都可以由小数形式的序列值来表示,例如,正午12:00:00的序列值为0.
5(一天的一半),12:01:00的序列值近似为0.
500694.
如果输入的时间值超过24小时,则Excel会自动以天为单位进行整数进位处理,例如26:13:12,转换为序列值1.
0925,即为l+0.
0925(1天+2小时13分12秒).
Excel2010中允许输入的最大时间为9999:59:59.
9999.
(三)文本文本是字符型数据,通常是指一些非数值型的文字、符号等,例如企业的部门名称、会计科目名称、个人的姓名等.
除此以外,许多不代表数量的、不需要进行数值计算的数字也可以保存为文本形式,例如电话号码、身份证号码、股票代码等.
所以,文本并没有严格意义上的概念.
事实上,Excel将许多不能理解为数值(包括日期时间)和公式的数据都视为文本.
文本不能用于数值计算,但可以比较大小.
在Excel2010中,单元格中最大可显示2041个字符,而在编辑栏中最多可以显示32767个字符.
财务与会计数据处理——以Excel为工具20(四)逻辑值逻辑值是比较特殊的一类参数,它只有TRUE(真或1)和FALSE(假或0)两种类型.
例如,在公式"=IF(A3=0,"0",A2/A3)"中,"A3=0"就是一个可以返回TRUE(真)或FALSE(假)两种结果的参数.
当"A3=0"为TRUE时,在公式中返回结果为"0",否则返回"A2/A3"的计算结果.
需要注意的是,TRUE不是1,FALSE也不是0,不是数值而是逻辑值,只不过有些时候可以把它"当成"1和0来用.
但是,逻辑值和数值有着本质的不同.
七、公式使用中的常见错误在使用公式计算的过程中,常常会出现一些无法显示正确值的情况,依据错误情况的不同,Excel返回的错误信息也不相同.
表1-3所示为Excel函数与公式使用中常见的错误值及其说明.
表1-3Excel函数与公式使用中常见的错误值及其说明常见错误值说明#####输入单元格的数值太长,在单元格中显示不下,可以通过调整单元格大小来修正#VALUE!
使用了错误的参数和运算对象类型#DIV/0!
公式被0除时#NAME公式中产生不能识别的文本而产生的错误值#N/A函数或公式中没有可用的数值而产生的错误值#REF!
单元格引用无效#NUM!
公式或函数中的某个数字有问题#NULL!
试图为两个并不相交的区域指定交叉点时产生的错误值第三节Excel函数一、函数的含义与组成为了方便用户使用,Excel提供了大量的函数.
Excel函数是由Excel内部预先定义并按照特定的顺序、结构来执行计算、分析等数据处理任务的功能模块.
Excel函数的概念与数学中函数的概念类似,它是一些预定义的公式,这些公式使用一些称为参数的特定数值按特定的顺序或结构进行计算,因此,Excel函数也常被人们称为"特殊公式".
与公式一样,Excel函数的最终返回结果为值.
以下为一个Excel函数的基本语法结构:函数名(参数1,参数2,参数3,…,参数N)函数只有唯一的函数名且不区分大小写,函数名表达函数的含义,由一个字符串来表示.
每个函数都有特定的功能和用途.
第一章Excel应用基础21函数名后面是用圆括号括起来的参数,多个参数之间要用半角的逗号分隔,函数参数具有固定的位置.
函数的参数可分为可选参数与必需参数.
在Excel函数语法中,可选参数一般用一对方括号"[]"包含起来.
当函数有多个可选参数时,可从右向左依次省略参数.
例如,OFFSET函数语法为:OFFSET(reference,rows,cols,[height],[width]),其中height、width参数都为可选参数,如果OFFSET函数仅使用4个参数,则第4个参数会识别为height而不是width参数.
函数的参数可以由数值、日期和文本等元素组成,也可以使用常量、数组、单元格引用或使用其他函数.
当使用函数作为另一个函数的参数时,称为函数的嵌套.
在使用函数时,通常由表示公式开始的"="号、函数名称、左括号、以半角逗号相间隔的参数和右括号构成.
例如,求一列数B3到B10之间所有数据的总和,可以使用函数:=SUM(B3:B10).
另外,有的函数可以仅使用其部分参数,例如SUM函数可支持255个参数(Excel2003版为30个),其中第1个参数为必需参数,不能省略,而第2至第255个参数都可以省略.
也有一些函数没有参数或可不需要参数,例如,Now函数、RAND函数、PI函数等没有参数;ROW函数、COLUMN函数如果参数省略则返回公式所在的单元格行号、列标数.
二、函数的分类在数据处理过程中,某些简单的计算可以通过自行设计公式来完成,但如果要对多个单元格进行复杂操作,则需要使用Excel函数,以使数据的处理过程高效、简单,降低出错的概率.
另外,类似RAND函数产生大于等于0小于1的随机值的功能也是自编公式无法完成的.
在Excel函数中,依据来源的不同,函数可分为以下四个种类.
(一)自定义函数使用VBA代码编制的实现特定功能并存放于"模块"中的函数.
(二)宏表函数该类函数需要通过定义名称或在宏表中使用,其中多数函数功能已逐步被内置函数和VBA功能所替代.
自Excel2007版开始,需将包含有自定义函数或宏表函数的文件保存为"启用宏的工作簿(.
xlsm)"或"二进制工作簿(.
xlsb)",并在首次打开文件后单击"宏已被禁用"安全警告对话框中的"启用内容"按钮.
(三)扩展函数必须通过加载后才能正常使用,例如EUROCONVERT函数必须在单击"开发工具"→"加载项",在"加载宏"对话框中勾选"欧元工具"复选框后,才能正常使用,否则将返回#NAME错误.
(四)内置函数内置函数是只要启动了Excel就可以使用的函数.
通过单击功能区中"公式"选项卡中的"插入函数"按钮,就可弹出"插入函数"对话框,如图1-18所示.
根据函数的功能和应用领域,内置函数可分为以下12个类别.
财务与会计数据处理——以Excel为工具22文本函数信息函数逻辑函数查找和引用函数日期和时间函数统计函数数学和三角函数数据库函数财务函数工程函数多维数据集函数兼容性函数图1-18"插入函数"对话框其中,兼容性函数是在Excel2010版中提供的比2003版等早期版本具有更高精确度,或名称能更好地反映其用法等新的替代函数而仍保留的函数.
虽然这些函数仍可向后兼容,但用户应该考虑从现在开始使用新函数,因为Excel的将来版本中可能不再可用.
内置函数是用户经常使用的函数,如果这些函数还不能满足用户的特殊需要,用户可以自定义函数.
三、函数的输入和编辑如果对要使用的函数非常熟悉,可以在单元格中直接输入函数公式,然后单击编辑栏中的"="按钮,系统将根据输入的函数公式自动进行计算,并把计算结果显示到该单元格中.
除了直接输入函数公式外,还可以使用Excel提供的"插入函数"的工具完成函数的输入和使用.
(一)使用"自动求和"按钮插入函数相信很多财务人员接触Excel函数都是从"自动求和"功能开始的.
在功能区"公式"选项卡中有一个显示∑字样的"自动求和"按钮("开始"选项卡"编辑"组中也有此按钮),第一章Excel应用基础23其中包括求和、平均值、计数、最大值、最小值和其他函数6个备选项,默认情况下单击该按钮或者按Alt+=组合键插入"求和"函数.
(1)单击"其他函数"按钮时,将打开"插入函数"对话框.
(2)单击其他5个按钮时,Excel将智能地根据所选取单元格区域和数据情况,自动选择公式统计的单元格范围,以实现快捷输入.
(3)当要计算的表格区域处于筛选状态时,单击该按钮将应用SUBTOTAL函数的相关功能,以便在筛选状态下进行求和、求均值、计算、取最小值等统计计算.
(二)使用函数库插入已知类别的函数在"公式"选项卡的"函数库"中提供了文本、日期和时间、查找与引用、数学和三角函数、其他函数等多个下拉按钮,在"其他函数"下拉按钮中提供了统计、工程、多维数据集、信息、兼容性函数等扩展菜单.
(三)使用"插入函数"向导搜索函数如果对函数所归属的类别不太熟悉,还可以使用"插入函数"向导选择或搜索所需函数.
"插入函数"对话框效果如图1-19所示.
以下4种方法均可以打开.
单击"公式"选项卡上的"插入函数"按钮.
在"公式"选项卡的"函数库"组中的各个下拉按钮的扩展菜单中,单击"插入函数"按钮;或单击"自动求和"下拉按钮,在扩展菜单中单击"其他函数"按钮.
单击"编辑栏"左侧的"插入函数"按钮.
按Shift+F3组合键.
1234图1-19关键词查找函数如图1-19所示,在"搜索函数"编辑框中输入"折旧",单击"转到"按钮,对话框中将显示"推荐"的函数列表,选择具体函数(如VDB函数)后,单击"确定"按钮,即可插入该函数并切换到"函数参数"对话框,如图1-20所示.
财务与会计数据处理——以Excel为工具24图1-20"函数参数"对话框(四)使用"公式记忆式键入"手工输入函数自Excel2007开始新增了一项"公式记忆式键入"功能,可以在用户输入公式时出现备选的函数和已定义的名称列表,帮助用户自动完成函数公式的输入.
如果知道所需函数的全部或开头部分字母的正确拼写方法,则可直接在单元格或编辑栏中手工输入函数.
在公式编辑模式下,按Alt+↓组合键可以切换是否启用"公式记忆式键入"功能,也可以单击"文件"→"选项",在"Excel选项"对话框的"公式"选项卡中勾选"使用公式"区域的"公式记忆式键入"复选框,然后单击"确定"按钮关闭对话框.
当用户在编辑或输入公式时,就会自动显示以输入的字符开头的函数或已定义名称、"表"名称以及"表"的相关字段名下拉列表.
例如,在单元格中输入"=SU"后,Excel将自动显示所有以"SU"开头的函数、名称或"表"的扩展下拉菜单.
通过在扩展下拉菜单中移动上、下方向键或光标选择不同的函数,其右侧将显示此函数功能简介,双击鼠标或者按Tab键可将此函数添加到当前的编辑位置,既提高了输入效率,又确保了输入函数名称的准确性.
随着进一步的输入,扩展下拉菜单将逐步缩小范围,如图1-21所示.
图1-21公式记忆式键入(五)灵活使用"函数屏幕提示"工具如图1-22所示,单击"文件"→"选项"选项卡,在"Excel选项"对话框"高级"选项卡的"显示"区域中,勾选"显示函数屏幕提示"复选框.
第一章Excel应用基础25在单元格或编辑栏中编辑公式的时候,当输入函数名称及紧跟其后的左括号时,在编辑位置附近会自动出现悬浮的"函数屏幕提示"工具条,帮助用户了解函数语法中的参数名称、可选参数或必需参数等,如图1-22所示.
提示信息中包含了当前输入的函数名称及完成此函数所需要的参数,如图1-22所示,输入的DATE函数包括了3个参数,分别为"year""month"和"day",当前光标所在位置的参数(如图中所显示的"year"参数)以加粗字体显示.
如果公式中已经填入了函数参数,则单击"函数屏幕提示"工具条某个参数名称时,将选中该参数的所在部分(包括使用嵌套函数作为参数的情况)并以黑色背景突显.
单击"函数屏幕提示"工具条上的函数名称,将打开"Excel帮助"窗口,快速获取该函数的帮助信息.
因为函数是一种特殊的公式,因此函数中常见的错误和参数的数据类型等相关问题可参见本章的公式一节.
图1-22启用函数屏幕提示功能第四节Excel数据处理与分析工具一、数据有效性管理数据有效性工具通常用来限制单元格中输入数据的类型和范围,防止用户输入无效数据,保证输入数据的正确性是数据处理与分析的前提要求.
(一)数据有效性的设置与使用要对某个单元格或单元格区域设置数据有效性,可以按以下步骤操作.
(1)选中要设置数据有效性的单元格或单元格区域,如图1-23所示的"性别"列.
(2)单击功能区中"数据"选项卡"数据工具"组中的"数据有效性"按钮,打开"数财务与会计数据处理——以Excel为工具26据有效性"对话框,如图1-23所示.
图1-23设置数据有效性(3)在"设置"选项卡中的"验证条件"组的"允许"下拉菜单中选择"序列","来源"输入"男,女",表示允许选择区域中输入文本男或女,用半角逗号分隔.
(4)单击确定后,有效性设置完成,效果如图1-23右侧所示.
在"数据有效性"对话框中,用户可以进行数据有效性的其他相关设置.
(二)数据有效性的基本规则在"数据有效性"对话框的"设置"选项卡中,内置了8种数据有效性允许的条件可以对数据录入进行有效的管理和控制.
这8种数据有效性允许的条件为:任何值、整数、小数、序列、日期、时间、文本长度和自定义,如图1-24所示.
其中,"任何值"为默认选项,即允许在单元格中输入任何数据而不受限制.
其他条件则只允许输入相应数据类型的值.
较为不易理解的是"序列""文本长度"和"自定义"这三种条件.
"序列"条件要求在单元格区域中必须输入某一特定序列中的一个内容项.
序列的内容可以是单元格引用、公式,也可以手动输入.
"文本长度"条件主要用于限制输入数据的字符个数.
例如要求输入某会计一级科目编码必须为4位.
"自定义"条件则主要是指通过函数与公式来实现较为复杂的条件.
需要注意的是,数据有效性可以限制错误数据录入,但不能阻止错误数据被复制粘贴.
对于错误数据的检查,可以采用条件格式的方法将错误数据查找出来.
二、数据排序在日常的数据处理工作中,经常需要对大量的数据按某种要求进行排序,如按应发工资数额从小到大排序.
我们可以利用Excel提供的排序工具进行处理.
排序是对数据的顺序进行重新排列,其中决定数据顺序关系的数据列被称为关键字.
在Excel中,可以按字母、数图1-24数据有效性允许的条件第一章Excel应用基础27字或日期等顺序来进行数据排序.
排序关键字是Excel对数据进行排序的依据,在排序之后,主要关键字所在的数据列是有顺序的,而其余数据列不一定有序.
(一)排序的规则排序的方式有升序和降序两种.
按升序排序时,Excel按如下次序:数字→字母→逻辑值→错误值→空格;在按降序排序时,除了空格总是在最后面外,其他的排序次序与升序相反.
各种类型数据排序规则如下.
数值按数值大小.
字母按字母先后顺序.
日期按日期的先后.
汉字按汉语拼音的顺序或按笔画顺序.
逻辑值升序时FALSE排在TRUE前面,降序时相反.
空格总是排在最后.
(二)排序的操作步骤【例1-6】以图1-25所示的"A公司工资结算明细表"为例,按"实发合计"的降序对员工进行排序.
图1-25A公司工资结算明细表按"实发合计"降序排序财务与会计数据处理——以Excel为工具28对数据进行排序的具体步骤如下.
(1)依次打开"工资核算"工作簿和"A公司工资结算明细表",单击数据区域中的任意一个单元格.
(2)选择"数据"→"排序"命令,即可弹出"排序"对话框,如图1-25所示,从"主要关键字"下拉列表框中选择关键字,这里选择"实发合计".
(3)选择"排序依据"为"数值",选择"次序"为"降序"后,单击"确定"按钮,即可完成排序,如图1-25所示,"实发合计"数据按降序排列.
如果要按升序排列,只需要在"排序"对话框中的"次序"下拉列表框中选择"升序"即可.
当只选择对数据清单按一个关键字进行排序时,可以直接单击工具栏中的"升序"按钮或"降序"按钮.
当数据表中作为关键字的数据列中存在重复数据时,就需要使数据能够在具有相同关键字的记录当中再次按另一个关键字进行排列,这就是多字段排序.
进行多字段排序时,只需要在"排序"对话框中添加新的关键字即可.
在"排序"对话框中单击"添加条件"按钮,这时出现"次要关键字"下拉列表框,根据需要依次进行选择,单击"确定"按钮,完成多字段排序.
三、数据筛选财务数据往往是复杂繁多的,工作人员常常需要从海量的数据当中找出一些符合条件的数据,这时就需要利用Excel提供的筛选功能来实现.
筛选功能可以把数据表或数据库中所有不满足条件的数据隐藏起来,只显示满足条件的数据记录.
常用的数据筛选方法有自动筛选和高级筛选.
(一)自动筛选利用自动筛选功能可以通过简单的操作快速检索数据表或数据库,筛选出所需要的数据.
下面以图1-25中"A公司工资结算明细表"为例介绍筛选的步骤如下.
(1)单击数据清单中任一非空单元格,选择"数据"选项卡的"筛选"命令,即可在数据清单的列项右侧自动添加一个下拉列表标志,如图1-26所示.
(2)单击需要筛选的下拉列表,Excel将显示出可用的筛选条件,从中选择需要的条件,如单击"部门"右侧的下拉列表,则会显示如图1-26所示的快捷菜单.
(3)在"文本筛选"列表框中选择"财务部"复选框,单击"确定"按钮,即可显示出只有"财务部"员工的工资数据,如图1-26所示.
还可通过自动筛选进行数值比较,选出工资金额符合条件的员工记录.
值得注意的是,Excel的筛选只是把原始数据表中不符合条件的数据行隐藏起来了,并没有修改原表中的任何数据,要把筛选后的数据表恢复成原样,只需要再次选择Excel中的"数据"→"筛选"命令即可.
第一章Excel应用基础29图1-26筛选出"财务部"员工的工资明细表(二)高级筛选高级筛选一般用于条件较复杂的筛选操作,其筛选结果可显示在源数据表中,不符合条件的记录被隐藏起来,也可以在新的位置显示筛选结果,不符合条件的记录同时保留在数据表中而不会被隐藏起来,这样就更加便于进行数据的比较.
高级筛选的操作步骤如下.
(1)选择如图1-27所示的"A公司工资结算明细表",在其空白处建立各数据列的筛选条件,如"基本工资>6000并且实发合计>7000"的员工,在空白单元格处输入条件.
图1-27输入筛选条件(基本工资>6000并且实发合计>7000)财务与会计数据处理——以Excel为工具30(2)在"数据"选项卡的"排序和筛选"组中单击"高级"按钮.
在"高级筛选"对话框中有3个区域,分别是列表区、条件区、复制到区域(结果显示区域),在其中确定原始数据区、确定筛选条件区、确定筛选数据保存区.
(3)单击"确定"按钮,显示数据表的筛选结果,如图1-28所示.
图1-28高级筛选条件设置及筛选结果如果在"高级筛选"对话框中选择了"将筛选结果复制到其他位置",并在"复制到"文本框中指定筛选结果的显示区域,那么筛选结果将在指定区域中显示出来,原始数据表不变.
再次选择"筛选"命令,将会取消筛选,重新显示出原始的全部数据.
四、数据分类汇总在对数据进行分析时,常常需要将相同类型的数据统计出来,这就是数据的分类与汇总.
分类汇总是一种在数据表中快捷地汇总数据的方法,通过分级显示和分类汇总,可以从大量的数据中提取有用的信息.
使用Excel工具对数据进行分类汇总时,必须确定以下内容:首先,要分类汇总的数据区域必须是一个数据表或数据库,且数据表各列必须有列标题;其次,必须在数据表中对要进行分类汇总的列进行排序,这个排序的列标就是分类关键字,在进行分类汇总时,只能指定排序后的列标为汇总关键字.
具体操作步骤如下.
(1)打开要进行分类汇总的工作表,例如"A公司工资结算明细表",单击表内任意非空单元格后选择"数据"选项卡,从中选择"分级显示"组中的"分类汇总"命令,即可弹出"分类汇总"对话框,如图1-29所示.
(2)从"分类字段"下拉列表框中选择要进行分类的字段,该下拉列表框中汇集了数据表中的所有列标,分类字段必须是已经排序的字段.
如果选择没有排序的列标作为分类字段,最后的分类结果将不准确.
(3)在"汇总方式"下拉列表框中列出了可以选择的汇总方式和所需汇总的列表.
(4)选择汇总数据的保存方式有3种:①替换当前分类汇总,选择该种方式时,新的汇总将取代旧的分类汇总;②每组数据分页,选择该种方式时,各种不同的分类数据将被分第一章Excel应用基础31页保存;③汇总结果显示在数据下方,选择该方式时,原数据的下方将显示汇总计算的结果.
(5)单击"确定"按钮,完成汇总.
图1-29在"分类汇总"对话框中设置条件进行自动分类汇总后,如果不再需要分类汇总的结果,可在"分类汇总"对话框中单击"全部删除"按钮,即可撤销分类汇总.
五、数据透视表数据透视表是用来从Excel数据列表、关系数据库文件或OLAP(联机分析处理)多维数据集中的特殊字段中总结信息的分析工具.
它是一种交互式报表,可以快速分类汇总、比较大量的数据,并可以随时选择其中页、行和列中的不同元素,以达到快速查看源数据的不同统计结果,同时还可以随意显示和打印出用户所感兴趣区域的明细数据.
数据透视表有机地综合了数据排序、筛选、分类汇总等数据分析的优点,可方便地调整分类汇总的方式,灵活地以多种不同方式展示数据的特征.
一张数据透视表仅靠鼠标移动字段位置,即可变换出各种类型的报表.
同时,数据透视表也是解决函数公式速度瓶颈的手段之一.
因此,该工具是最常用、功能最全的Excel数据分析工具之一.
数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式动态表格,能帮助用户分析、组织数据.
例如,计算平均数或标准差、建立列联表、计算百分比、建立新的数据子集等.
建好数据透视表后,可以对数据透视表重新安排,以便从不同的角度查看数据.
数据透视表的名字来源于它具有"透视"表格的能力,从大量看似无关的数据中寻找背后的联系,从而将纷繁复杂的大量数据转化为有价值的信息,以供研究和决策所用.
总之,合理运用数据透视表进行计算与分析,能使许多复杂的问题简单化并且极大地提高工作效率.
【例1-7】表1-4为各城市程序员专业类型和薪资待遇的列表,请对各城市不同专业类型的薪资待遇分类汇总并进行比较分析,用数据透视表来完成.
假设表1-4的工作表已经建立,数据已经输入.
财务与会计数据处理——以Excel为工具32表1-4各城市程序员专业类型与薪资待遇城市专业类型薪资待遇深圳平面设计7200深圳UI设计师8600深圳Java程序员9000深圳Android程序员10800深圳PHP程序员10800广州平面设计7100广州UI设计师7000广州Java程序员7400广州Android程序员9200广州PHP程序员8600北京平面设计8200北京UI设计师7800北京Java程序员12600北京Android程序员15000北京PHP程序员10200南宁平面设计3700南宁UI设计师5300南宁Java程序员5900南宁Android程序员7300南宁PHP程序员5500柳州平面设计3900柳州UI设计师3500柳州Java程序员4200柳州Android程序员4500柳州PHP程序员4000北海平面设计2800北海UI设计师2700北海Android程序员北海Java程序员3500北海PHP程序员3500(一)数据透视表的创建步骤(1)打开"各城市程序员专业类型与薪资"工作表,单击数据列表区域中任意一个单元格,在"插入"选项卡中单击"数据透视表"图标,弹出"创建数据透视表"对话框,如图1-30所示.
(2)保持"创建数据透视表"对话框内默认的选项不变,单击"确定"按钮后即可创建一张空的数据透视表.
(3)在"数据透视表字段"列表对话框中按照需要分别勾选"行""列"和"值"所需的字段,并按住鼠标左键将其拖动至表格所需位置即可,如图1-30所示.
第一章Excel应用基础33图1-30数据透视表的创建(二)数据透视表的区域从结构上看,数据透视表至少包含3个部分,如图1-31所示.
行区域:此标志区域中按钮将作为数据透视表的行字段.
列区域:此标志区域中按钮将作为数据透视表的列字段.
数值区域:此标志区城中按钮将作为数据透视表的显示汇总的数据.
图1-31数据透视表区域第五节Excel图表处理一、图表的类型Excel提供了数据表分析和图表分析两种分析方式.
数据表分析方式中数据处理的结果是用数据的形式呈现,这种形式虽然精确,但很难有直观和全面的效果.
图表分析方式可以把数据在各类图表上描述出来,使用户可以直观、形象地看到数据的变化规律、发展趋势、变化周期、变化速度和变化幅度等.
Excel提供了丰富的图表类型,以方便用户创建各种需要的图表,使图表形式成为数据表格的一个很好的补充.
Excel共提供了11种标准的图表类型,而每种类型还有多种不同的具体形式——子图表类型可以选择.
另外,用户还可以自定义图表类型.
下面介绍几种常用的图表类型.
(一)柱形图柱形图也称直方图,是Excel默认的图表类型,也是我们经常使用的一种图表类型.
柱