Excel在财务管理中的应用课件：Excel的数据和图表操作

Excel的数据和图表操作2.1数据输入、修改、填充及有效性验证2.1.1数据输入单击要输入数据的单元格,利用键盘输入相应的内容,即可完成Excel的数据输入。系统默认的情况下,输入数据后,按Enter键,会确认输入内容并切换到下方单元格;按Tab键,会确认输入内容并切换到右方单元格;单击任意其他单元格,会确认输入内容并切换到指定单元格。2.1.2数据修改

(1)直接替换单元格内的数据。单击要修改的单元格,输入新的内容,以直接替换该单元格的原始内容。(2)修改单元格中的部分内容。双击要修改的单元格,当光标变为“|”状态时,单元格为录入状态,“|”所在位置即为文字插入的位置。此时,可以对单元格中的部分内容进行增加或删减。2.1.3数据填充

在Excel中,对于一些比较有规律性的数据,系统提供了自动填充功能,可以帮助使用者快速输入此类数据,避免手动输入的时间耗费,降低手动输入的错误率。使用填充柄能够快速填充数据,首先单击写有数字的单元格,将光标置于单元格右下角,此时光标变为十字形,单击十字形光标并向下拖动,即可完成向下填充;单击十字形光标向右拖动,即可完成向右填充。此时单元格右下角会出现“自动填充选项”,单击“自动填充选项”即可选择填充方式。2.1.3数据填充

填充方式包括以下几种。(1)复制单元格,即复制上一单元格并填充至下一单元格。如图2-1所示,A1单元格原始数据为1,使用填充柄拖动鼠标至A10单元格,此时A2至A10单元格均复制A1单元格数据。图2-12.1.3数据填充

【例2-1】如图2-2所示,按住鼠标左键选中A1至A2单元格,将光标移至A2单元格右下角,待光标变为十字形,按住鼠标左键下拉至A10单元格,此时在“自动填充选项”中选择“复制单元格”,得到右侧结果。图2-22.1.3数据填充

(2)填充序列,即按照等差数列进行填充。【例2-2】如图2-3所示,A1单元格原始数据为1,使用填充柄拖动鼠标至A10单元格,在“自动填充选项”中选择“填充序列”,就得到从1开始的等差数列。图2-32.1.3数据填充

(3)仅填充格式。【例2-3】如图2-4所示,A1单元格背景颜色为黄色,字体格式为宋体11号加粗。使用填充柄拖动鼠标至A10单元格,在“自动填充选项”中选择“仅填充格式”,可以看到A2至A10单元格格式与A1单元格相同,但并未填充内容,如图2-5左侧图所示。此时,在A4单元格输入“填充”二字,可以看到字体格式与A1单元中的字体格式相同,如图2-5右侧图所示。图2-4图2-52.1.4数据有效性验证

1.设置数据验证数据有效性验证是对单元格或单元格区域输入的数据从内容到数量上的限制。从规则列表中进行选择以限制可以在单元格中输入的数据类型,便于统一标准及统计分析,避免手工录入错误数据。数据验证功能可以通过“数据”选项卡-“数据工具”-“数据验证”选项进行设置,如图2-6所示。图2-62.1.4数据有效性验证

【例2-4】图2-7为差旅费报销单的工作表界面,利用“数据验证”功能限制“月”列下C7单元格中输入的内容为1~12之间的整数。图2-72.1.4数据有效性验证

【操作方法】第一步,选中C7单元格;第二步,在“数据”选项卡下,单击“数据验证”选项;第三步,在“数据验证”对话框中设置数据有效性的内容,在本例中,设置C7单元格为1~12之间的整数,操作过程如图2-8所示。图2-82.1.4数据有效性验证

设置数据有效性验证后,相应单元格只有在通过数据验证后才能录入。在本例中,C7单元格的数据有效性限制在1~12的整数中,即只有输入的内容为1~12的整数才允许录入。此时,若在C7单元格中输入“13”,系统将自动报错,无法录入,如图2-9所示。图2-92.1.4数据有效性验证

设置数据有效性验证后,相应单元格只有在通过数据验证后才能录入。在本例中,C7单元格的数据有效性限制在1~12的整数中,即只有输入的内容为1~12的整数才允许录入。此时,若在C7单元格中输入“13”,系统将自动报错,无法录入,如图2-9所示。图2-9设置数据验证后,还可以通过单元格格式粘贴功能将C7单元格的格式粘贴至C8至C12单元格当中。2.1.4数据有效性验证

2.插入或删除批注表格制作者在对表格进行编辑的过程中,可以对表格中的内容插入批注,以方便其他使用者了解表格内容。可以通过单击鼠标右键,选择“插入批注”进行插入,也可以在“审阅”选项卡下选择“新建批注”进行插入。【例2-5】图2-7为差旅费报销单的工作表界面,已对C7单元格设置了数据验证,输入内容是1~12之间的整数。为方便其他使用者了解C7单元格的格式限制,请对C7单元格添加批注。2.1.4数据有效性验证

【操作方法】方法1：第一步,选中C7单元格,并单击鼠标右键,选择“插入批注”选项;第二步,输入批注内容。方法2:第一步,选中C7单元格,并在“审阅”选项卡下单击“新建批注”选项;第二步,输入批注内容。如图2-10、图2-11所示。

图2-10

图2-112.1.4数据有效性验证

插入批注后,可以重新编辑或删除批注。操作方法为:单击已插入批注的单元格,在“审阅”选项卡下,选择“编辑批注”或“删除批注”即可,如图2-12所示。

图2-122.2数据排序2.2.1排序的规则排序,可以让表格中的数据按照某种规则排列,数据间的顺序包括升序和降序。在财务工作中,对数据列表进行排序能够让人更直观地观察数据的规律,便捷地找到某一特殊数据。Excel2019系统按照如下规则对数据进行排序:(1)针对数值型数据,系统按照数值的大小进行排序;(2)针对日期型数据,系统按照日期的先后进行排序;(3)针对时间型数据,系统按照时间的先后进行排序;(4)针对逻辑型数据,系统按照TRUE>FLASE的规则进行排序;(5)针对文本型数据,系统对文本中每个字符按从左到右的顺序进行比较,并按照字符的ASCII(美国信息交换标准代码)顺序进行排序,即所有汉字(按拼音或笔画顺序)>所有字母(按字母顺序,如区分大小写,则小写>大写)>所有数字(按数值大小)>空格。2.2.1排序的规则其中,汉字按拼音顺序还是笔画顺序排序、字母排序是否区分大小写,均可在“排序选项”中选择。操作方法是:第一步,单击“数据”选项卡;第二步,在“排序和筛选”组中单击“排序”选项;第三步,单击“选项”按钮;第四步,选择是否区分大小写;第五步,选择排序方向;第六步,选择文字的排序方法;第七步,单击“确定”选项,如图2-13所示。

图2-132.2.2排序的操作方法1.简单排序(单个条件排序)第一步,选定工作表数据区域中的某个单元格或者部分单元格区域;第二步,单击“数据”选项卡;第三步,单击“排序和筛选”组中的“升序”,即“”选项,或“降序”,即“”选项。2.2.2排序的操作方法2.复杂排序(多重条件排序)第一步,选定工作表数据区域中的某个单元格或者部分单元格区域;第二步,单击“数据”选项卡;第三步,单击“排列和筛选”组中的“排序”,即“”选项;第四步,在“排序”对话框中对排序条件进行设置。排序条件主要包括:根据哪一列数据进行排序,排序依据,排序次序,同时还可以添加条件、删除条件、复制条件等,如图2-14所示。图2-142.2.3排序的操作实例【例2-6】图2-15为一组数据,要求:按照A列数据进行升序排列,并使B列、C列与A列一起移动。图2-152.2.3排序的操作实例【操作方法】第一步,在工作表数据区域中单击任意单元格;第二步,在“数据”选项卡中单击“排序”选项;第三步,取消勾选“数据包含标题”选项;第四步,在排序条件中,“列”选择“列A”,“排序依据”选择“单元格值”,“次序”选择“升序”;第五步,单击“确定”选项。操作步骤如图2-16所示。图2-162.2.3排序的操作实例【操作方法】第一步,在工作表数据区域中单击任意单元格;第二步,在“数据”选项卡中单击“排序”选项;第三步,取消勾选“数据包含标题”选项;第四步,在排序条件中,“列”选择“列A”,“排序依据”选择“单元格值”,“次序”选择“升序”;第五步,单击“确定”选项。操作步骤如图2-16所示。由操作结果可以看出,当仅选定工作表数据区域中的某一个单元格并对其进行排序时,意味着对整个表格数据进行排序,整个数据表会随着排序关键字所在列的数据变化而变化。图2-162.2.3排序的操作实例【例2-7】原始数据如图2-17所示。要求:对A1至B2单元格区域内的数据按照A列数值进行降序排列。图2-172.2.3排序的操作实例【操作方法】第一步,拖动鼠标选择A1至B2单元格区域;第二步,在“数据”选项卡中单击“排序”选项;第三步,取消勾选“数据包含标题”选项;第四步,在排序条件中,“列”选择“列A”,“排序依据”选择“单元格值”,“次序”选择“降序”;第五步,单击“确定”选项。操作结果如图2-18所示。由操作结果可以看出,当选定的工作表数据区域包含多列数据时,意味着仅对选中区域进行排序。图2-182.2.3排序的操作实例【例2-8】假设某高校三个食堂外包,承包方列出了食堂销售收入明细,如图2-19所示。要求:按收入类别进行升序排列,同时,对于同一收入类别,按照收入金额降序排列。图2-192.2.3排序的操作实例【操作方法】如图2-20所示,第一步,拖动鼠标,选中工作表内数据区域;第二步,单击“数据”选项卡下“排序”选项;第三步,选择主要关键字为“收入类别”;第四步,选择次序为“升序”;第五步,单击“添加条件”;第六步,选择次要关键字为“收入金额”;第七步,选择次序为降序;第八步,单击“确定”选项。操作结果如图2-21所示。图2-20图2-212.3数据筛选2.3.1自动筛选Excel具有强大的数据筛选功能,该功能可以根据筛选条件显示符合条件的数据,同时将不符合条件的数据暂时隐藏。注意,进行筛选操作时,数据列表中必须含有列标签。1.自动筛选数据的基本步骤第一步,选定工作表数据区域中的某个单元格或者部分单元格;第二步,单击“数据”选项卡下“排序和筛选”组中的“筛选”选项;第三步,单击下拉箭头,打开筛选清单,选择相应的筛选条件。通过筛选的数据将被显示,而不符合筛选条件的数据将被暂时隐藏。当需要恢复原有全部数据时,只需在筛选清单中勾选“全选”选项。当需要取消自动筛选时,只需再次单击“筛选”选项。2.3.1自动筛选【例2-9】如图2-22,任意选定某一单元格(如A8单元格),点击“数据”选项卡下“筛选”选项,此时可以看到,工作表数据区域中每一列的第一行单元格都出现了筛选的下拉箭头。通过单击下拉箭头能够对下方数据进行筛选。图2-222.3.1自动筛选【例2-10】如图2-23,任意选定某一数据区域(如B8至D10单元格区域),点击“数据”选项卡下“筛选”选项,此时可以看到,选定区域的第一行成为字段名(如图2-23所示),系统能够对选定区域第一行以下的同列所有数据区域进行筛选(如图2-24所示)。图2-23图2-242.3.1自动筛选【例2-11】基于上例中的数据,要求:通过筛选使工作表显示二食堂的相关数据。【操作方法】第一步,选定数据区域内任意单元格,或选中所有数据区域,单击“数据”选项卡下“筛选”选项;第二步,单击“单位”的下拉箭头;第三步,在筛选清单中,选择“二食堂”;第四步,单击“确定”选项,如图2-25所示。图2-252.3.1自动筛选操作结果如图2-26所示,由操作结果可以看到,工作表底部状态栏显示“在18条记录中找到6个”,即对筛选结果做了计数统计。图2-262.3.1自动筛选注意,此时如果选择“收入金额”下的数据,即D2至D15单元格区域,在状态栏中能够看到二食堂收入金额的平均值、最小值、最大值和求和。(如图2-27所示。)将Excel的自定义状态栏功能和筛选功能相结合,能够很快得到简单的统计和计算结果,这对财务人员快速筛选数据非常实用。图2-272.3.1自动筛选2.对数据区域按颜色筛选【例2-12】数据区域见图2-28,要求:筛选出“收入类别”下字体为红色,且“收入金额”下单元格填充为黄色的数据。图2-282.3.1自动筛选【操作方法】第一步,单击“收入类别”的下拉箭头。第二步,单击“按颜色筛选”。第三步,选择红色(如图2-29所示)。图2-292.3.1自动筛选【操作方法】第四步,单击“收入金额”的下拉箭头。第五步,单击“按颜色筛选”。第六步,单击需要筛选的单元格颜色(如图2-30所示)。图2-302.3.1自动筛选3.对数据区域按数字筛选对于财务人员而言,对数字、金额等进行筛选是比较常用的功能。数字筛选的方式非常多,如图2-31所示,使用者可以根据实际情况选择筛选方式。图2-312.3.1自动筛选【例2-13】数据如图2-32所示,要求:筛选出“收入金额”在人民币500000~1000000元之间的数据。图2-322.3.1自动筛选【操作方法】第一步,单击“收入金额”的下拉箭头。第二步,在筛选清单中单击“数字筛选”选项。第三步,单击“介于”选项。如图2-33所示。第四步,输入筛选条件,即大于或等于500000;小于或等于1000000(如图2-34所示)。最终筛选结果如图2-35所示。图2-33图2-34图2-352.3.2高级筛选进行高级筛选时,首先,在工作表数据区域外建立筛选条件区域。【例2-14】如图2-36,复制第一行表头至F1至I1单元格,并在F2和F3单元格分别输入“一食堂”和“二食堂”,此时,F2至F3单元格区域为筛选条件区域。图2-362.3.2高级筛选注意,筛选条件区域中,若条件输入在同一行,则该行条件是必须同时满足的条件;若条件输入在不同行,则不同行条件只要满足其一即可。筛选条件可以是文本或数值条件,也可以是计算条件。其次,选定工作表数据区域中的任意单元格,单击“数据”选项卡下“排序和筛选”组内的“高级”选项,打开“高级筛选”对话框,按照对话框内指示进行相应操作即可进行高级筛选。高级筛选可在原有区域显示筛选结果,也可以将筛选结果复制到其他位置。若选择复制到其他位置,则在“复制到”选项处输入或选择复制区域的左上角单元格地址。操作过程如图2-37所示。图2-372.3.2高级筛选注意,筛选条件区域中,若条件输入在同一行,则该行条件是必须同时满足的条件;若条件输入在不同行,则不同行条件只要满足其一即可。筛选条件可以是文本或数值条件,也可以是计算条件。其次,选定工作表数据区域中的任意单元格,单击“数据”选项卡下“排序和筛选”组内的“高级”选项,打开“高级筛选”对话框,按照对话框内指示进行相应操作即可进行高级筛选。高级筛选可在原有区域显示筛选结果,也可以将筛选结果复制到其他位置。若选择复制到其他位置,则在“复制到”选项处输入或选择复制区域的左上角单元格地址。操作过程如图2-37所示。图2-372.3.2高级筛选筛选结果如图2-38所示,Excel根据筛选条件区域的筛选条件,将“单位”为“一食堂”和“二食堂”的数据筛选出来,并复制到起始单元格为F5单元格的区域内。高级筛选还提供了“选择不重复的记录”选项,此操作可实现筛选结果删除重复项的功能。图2-382.4图表操作2.4.1图表的建立针对工作表中的数据区域,Excel可以生成图表。操作步骤(见图2-39)为:第一步,选中需要生成图表的数据区域;第二步,在“插入”选项卡下“图表”组中单击“查看所有图表”,即“”;第三步,选择需要生成的图表类型;第四步,选择需要生成的图表样式;第五步,单击“确定”选项,生成图表。图2-392.4.1图表的建立Excel为使用者提供了17种图表类型,包括柱形图、折线图、饼图、条形图、面积图、XY散点图、地图、股价图、曲面图、雷达图、树状图、旭日图、直方图、箱形图、瀑布图、漏斗图、组合图,基本满足日常工作中对数据的分析要求。此外,根据所选数据的特征,Excel能够给使用者推荐图表,可通过“插入”选项卡下“图表”组中的“推荐的图表”选项实现。注意,Excel建立的图表具有随数据源的改变而自动更新的功能,即所选数据区域的数据有所改变时,图表能够进行相应的更新。图2-392.4.2图表的编辑插入图表后,Excel功能区将自动生成“图表工具”,其中包括“设计”和“格式”选项卡。使用者可以应用这两个选项卡对生成的图表进行编辑。“设计”选项卡下的功能如图2-40所示,主要包括图表布局、图表样式、数据、类型、位置。图2-402.4.2图表的编辑“格式”选项卡下的功能如图2-41所示,主要包括当前所选内容、插入形状、形状样式、艺术字样式、辅助功能、排列及大小。图2-412.4.2图表的编辑1.添加图表元素在对图表进行编辑时,最常用的功能是“添加图表元素”,通过此功能可以添加或删除图表的显示元素。“添加图表元素”功能位于“设计”选项卡下“图表布局”组中,内含11个图表元素:坐标轴、坐标轴标题、图表标题、数据标签(将具体数据标签显示在图表中)、数据表(设置原数据表是否显示在图表中)、误差线、网格线、图例、线条、趋势线、涨/跌柱线,如图2-42所示,且每个元素都有具体选项,使用者可根据实际操作要求进行选择。此外,生成图表后,单击图表右上角的十字标志,也可以选择图表元素。图2-422.4.2图表的编辑2.切换行/列在“设计”选项卡下,“数据”组中有“切换行/列”功能。切换行/列,即交换坐标轴上的数据。表2-1为某单位2016—2019年营业收入、营业成本对比表。将表2-1输入到Excel工作表中,选择数据区域并生成“簇状柱形图”,如图2-43所示。图2-432.4.2图表的编辑选中图表,单击“设计”选项卡下“数据”组中的“切换行/列”,此时图表结果如图2-44所示。图2-442.4.2图表的编辑3.选择数据使用“设计”选项卡下“数据”组中的“选择数据”功能,能够更改图表中包含的数据区域。【例2-15】将表2-1输到Excel工作表中,选择数据区域并生成“簇状柱形图”。若将工作表中“营业成本”相关数据隐藏,即隐藏工作表中第五行,显示结果如图2-45所示。图表仅包含“营业收入”的数据,而不显示“营业成本”的数据。图2-452.4.2图表的编辑单击“设计”选项卡下“数据”组中的“选择数据”选项,系统会弹出“选择数据源”对话框,如图2-46所示。在“选择数据源”对话框中可以设置图表数据区域、切换行/列,还可以对图例项和水平(分类)轴标签进行选择。图2-462.4.2图表的编辑使用者可以通过单击左下角的“隐藏的单元格和空单元格”选项,选择图表中是否显示隐藏行/列的数据,如图2-47所示。图2-472.4.2图表的编辑若勾选“显示隐藏行列中的数据”,并单击“确定”选项,则结果如图2-48所示。此时,尽管“营业成本”的相关数据被隐藏,但图表中依然包含“营业成本”的相关数据。图2-482.4.3图表的复制、移动和删除图表复制与单元格复制的方法相同,选定图表区域后,单击鼠标右键,选择菜单中的“复制”选项,或者使用快捷键Ctrl+C进行复制,再选定要复制图表的位置,进行“粘贴”操作即可。图表的移动包括在同一工作表内移动,以及移动至其他工作表中。在同一工作表内,选定图表区域后,当光标变为“”时,即可在同一工作表内轻松移动图表。要将图表移动至其他工作表,在“设计”选项卡下“位置”组中单击“移动图表”,此时系统弹出“移动图表”对话框,如图2-49所示。若勾选“新工作表:Chart1”,即新建工作表,命名为“Chart1”,并将图表移至Chart1工作表中,其中,新工作表名称“Chart1”可自行更改;若勾选“对象位于”,即将图表移动至工作簿中已有的工作表当中。此外,直接使用剪切+粘贴的方法,可以将图表剪切后复制到其他工作表中。删除图表时,只需选中需要删除的图表,按下退格键(Backspace键)或删除键(Delete键),即可删除图表。图2-492.5图表操作数据透视表与数据透视图2.5.1创建数据透视表

数据透视表是一种交互表格,是能够计算、汇总和分析数据的强大工具,可以帮助使用者了解数据的对比情况和规律。对于财务人员来说,使用数据透视表能够直观、快速地查看和分析大类项目的数据。【例2-16】创建数据透视表的步骤(如图2-50所示)如下:第一步,选择要创建数据透视表的单元格。注意,数据不应有任何空行或列,必须只有一个行标题。第二步,选择“插入”选项卡下“数据透视表”选项。第三步,在“请选择要分析的数据”下,选择“选择一个表或区域”选项;在“选择放置数据透视表的位置”下,可以选择“新工作表”,此时生成的透视表将放到一个新的工作表中,或者选择“现有工作表”并选择要显示数据透视表的位置,此时生成的透视表将放到当前工作表中。第四步,单击“确定”选项。图2-492.5.1创建数据透视表按上述步骤进行操作,将得到图2-51的结果,可以看到Excel界面右侧出现“数据透视表字段”窗口。图2-512.5.1创建数据透视表按上述步骤进行操作,将得到图2-51的结果,可以看到Excel界面右侧出现“数据透视表字段”窗口。此时,使用者可以在数据透视表中添加字段,从而构成数据透视表。注意,所选字段将添加至默认区域,即非数字字段添加到“行”,日期和时间层次结构将添加到“列”,数值字段将添加到“值”。若要将字段从一个区域移到另一个区域,可以选中该字段,按住鼠标左键并拖动至目标区域。图2-512.5.1创建数据透视表【例2-17】在图2-51的基础上,选中“单位”和“收入金额”字段。根据系统默认设置,“单位”属于非数字字段,则自动添加至“行”区域;“收入金额”为数值字段,则自动添加至“值”区域。操作结果如图2-52所示。由结果可以看到,通过数据透视表能够迅速且直观地“透视”各个单位的收入金额。图2-522.5.1创建数据透视表【例2-18】在图2-52的基础上,选中“单位”“时间”“收入金额”字段。根据系统默认设置,“单位”属于非数字字段,则自动添加至“行”区域;“时间”属于非数字字段,则自动添加至“行”区域;“收入金额”属于数值字段,则自动添加至“值”区域。操作结果如图2-53所示。图2-532.5.1创建数据透视表根据结果可以看到不同单位在不同时间的收入金额总和。接下来,尝试将“时间”字段从“行”区域拖动至“列”区域。操作结果如图2-54所示。图2-542.5.1创建数据透视表根据结果可以看到不同单位在不同时间的收入金额总和。接下来,尝试将“时间”字段从“行”区域拖动至“列”区域。操作结果如图2-54所示。此外,数据透视表还有筛选功能,每个标签都有下拉箭头,单击下拉箭头能够对字段进行筛选,方便使用者操作。图2-542.5.1创建数据透视表根据结果可以看到不同单位在不同时间的收入金额总和。接下来,尝试将“时间”字段从“行”区域拖动至“列”区域。操作结果如图2-54所示。此外,数据透视表还有筛选功能,每个标签都有下拉箭头,单击下拉箭头能够对字段进行筛选,方便使用者操作。图2-542.5.2创建数据透视图数据透视图是通过对数据透视表中的汇总数据添加可视化效果得到的图表,是对数据透视表的补充,以便使用者对数据透视表中的数据进行更直观的查看和比较。对于财务工作人员来说,应用数据透视表和数据透视图,能够更便捷地得到企业的关键财务数据并做出决策。数据透视图与标准图表相同,能够显示数据、数据类别、数据标记、坐标轴等,也能够更改图表类型和其他选项,如标题、图例的位