EXCEL实训案例与操作步骤.docx
- 文档编号:7963118
- 上传时间:2023-01-27
- 格式:DOCX
- 页数:46
- 大小:2.28MB
EXCEL实训案例与操作步骤.docx
《EXCEL实训案例与操作步骤.docx》由会员分享,可在线阅读,更多相关《EXCEL实训案例与操作步骤.docx(46页珍藏版)》请在冰豆网上搜索。
EXCEL实训案例与操作步骤
Excel实训案例与操作步骤
一、函数
要紧介绍如下函数:
maxminsumifsumifsumproductvlookupcountif
1.sum函数
功能:
计算单元格区域中所有数值的和
语法:
=sum(number1,number2,……)
“number1,number2,……”为需要求和的参数。
参数能够是数值、文本、逻辑值和单元格引用。
单元格引用假设是是空单元格,那么该单元格引用将被忽略。
2.max函数
功能:
返回一组值中的最大值
语法:
=max(number1,number2,……)
number1,number2,……number1是必需的,后续数值是可选的。
3.min函数
功能:
返回一组值中的最小值
语法:
=min(number1,number2,……)
number1,number2,……number1是必需的,后续数值是可选的。
例财务工作中常常利用函数:
化工集团含假设干分工厂,2021年各月利润如表所示,需求出各工厂年度利润合计、各月最大利润数与各月最小利润数。
那么需
在N3单元格中输入=SUM(B3:
M3)
在O3单元格中输入=MAX(B3:
M3)
在P3单元格中输入=MIN(B3:
M3)
再将N3至P3单元格选中,下拉填充柄,即将公式填充至下一行
可得出下表结果:
4.if函数
功能:
判定一个条件是不是知足,假设是知足返回一个值,假设是不知足那么返回另一个值。
语法:
=if(logical_test,value_if_true,value_if_false)
其中第一个参数logical_test为任何一个可判定为true或false的数值或表达式。
第二个参数value_if_true为logical_test为true时函数的返回值,能够是某一个公式。
假设是value_if_true省略,当logical_test为真时,函数返回TRUE值。
第三个参数为logical_test为假时的返回值,当该参数省略同时logical_test为假时,函数返回的值为false。
If函数能够嵌套,最多能够嵌套7层。
例企业在应收账款治理进程中,需及时对到期的应收账款进行归还提示,故需挑选出当前日期已到期的应收客户及应收金额。
如下表所示
为及时挑选出到期客户清单,需在F列显示出其到期状态,可通过IF函数来实现。
在F4单元格输入函数:
=IF(E4 表示的意思是,当E4单元格的到期日小于当前日期,那么F4单元格显示“到期”,不然,那么显示“未到期”。 进一步,假设将函数中的B2代表的当前日期固定,也即,将公式改成 =IF(E4<$B$2,"到期","未到期")那么下拉填充柄,可轻松实现公式的自动复制。 5.sumif函数 功能: 对知足条件的单元格求和。 语法: =sumif(range,criteria,sum_range) range表示要进行计算的单元格区域,criteria表示用数字、表达式或文本形式概念的条件;sum_range表示用于求和计算的实际单元格。 假设是省略,将利用区域中的单元格。 例: 依照表1-工资表得出表2-各部门工资统计 在B12单元格输入=SUMIF(C3: C8,A12,D3: D8) 即可得出管理部6月实发工资合计。 6.sumproduct函数 功能: 用于计算几组数组间对应元素乘积之和 语法: =SUMPRODUCT(array1,array2,array3,……) =SUMPRODUCT(数组1,数组2,数组3,……) 例: 关于如下左图所示数据,要计算所有产品的销售总额,一样的方式是先计算每一个产品的销售额(单价乘以销售量),然后将每一个产品的销售额加总在一路,取得销售总额 ,如下右图。 但利用sumproduct函数能够完全省略中间的计算进程,计算公式为: 在B9单元格输入=sumproduct(B2: B7,C2: C7)即可直接得出答案 7.vlookup函数 功能: 依照数据区域的第一列数据,向右边查找某列的数据 语法: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) =VLOOKUP(查找依据,查找区域,指定取数的列位置,是不是精准查询的逻辑值) 0表示精准匹配,找一模一样的数据;1表示模糊匹配,找接近的数据。 注意,单独利用函数VLOOKUP无法查找指定数据区域的重复数据。 例: 某公司共生产12种产品,产品资料如下左表,2020年1月销售记录如下右表,销售记录中仅包括所销售产品编码及数量,要求要继续完成空白单元格。 销售记录表: 单元格D3: =VLOOKUP($B3,产品资料! $B$3: $E$14,2,0) 单元格E3: =VLOOKUP($B3,产品资料! $B$3: $E$14,3,0) 单元格F3: =VLOOKUP($B3,产品资料! $B$3: $E$14,4,0) 单元格G3: =C3*E3 例: 利用VLOOKUP函数制作工资条 下表1为工资表,要想轻松高效完成每位员工工资条的制作,形成表2 操作步骤: 新建表格“工资条”,按规定格式输入第一行列标签 B2单元格: =VLOOKUP($A2,工资表! $A$2: $O$21,2,0) 即,以工资条中A2编号为查找依据,查找区域为工资表A2至O21,指定取数的列位置为第2列,也即假设是碰着工资表中第一列编号也为01的情形,那么B2单元格取值为被查找匹配单元格向右数第2列单元格中的数值,0表示精准查找。 可是鉴于工资条的格式与工资表的格式相同,可将2以COLUMN()代替(COLUMN()表示本单元格所在的列数),以方便公式的拖沓,而不需手动修改公式。 故,进一步将B2单元格改成: =VLOOKUP($A2,工资表! $A$2: $O$21,COLUMN(),0),向右拖沓填充柄,即可完成第一名员工工资表的制作。 完成后,选中A1至O3单元格,向下拖动右下角的填充柄,即可实现所有员工工资条的制作。 8.countif函数 功能: 用来求知足区域内指定条件的计数函数 语法: countif(range,criteria) range表示要计算其中非空单元格数量的区域 criteria表示统计条件 例: 依照工资表,需统计出实发工资各区间段的员工人数。 操作步骤: 在R6单元格中输入公式: =COUNTIF($O$2: $O$21,">=6000") 在R7单元格中输入公式: =COUNTIF($O$2: $O$21,">=4000")-COUNTIF($O$2: $O$21,">=6000") 在R8单元格中输入公式: =COUNTIF($O$2: $O$21,">=2000")-COUNTIF($O$2: $O$21,">=4000") 在R9单元格中输入公式: =COUNTIF($O$2: $O$21,"<2000") 取得如下结果: 其他常见小函数: todayrowcolumntextmidleftright 二、大体操作与数据处置 (一)数据有效性 数据有效性是对单元格设置的一个规那么,只有知足那个规那么的数据才能输入到单元格。 一、输入序列数据 在很多情形下,常常要输入一些重复的数据,比如要在员工信息表的某列输入该员工所属部门名称,而这些部门名称老是那么几个,此刻,利用数据有效性,不仅能够实现部门名称的快速输入,也能够幸免输入错误的部门名称。 例,如下左表,一共有三个部门,服装部、家电部、食物部,当单击B2单元格时,显现下拉箭头,就能够够够选择输入该序列的某个项目。 操作步骤: 选中B2至B7单元格区域,单击“数据”选项卡中的“数据有效性”——数据有效性, 2、输入指定区间的数据 例 : 要求输入员工年龄时,年龄区间范围为20-60之间。 一旦输入非区间内数值,将提示报错。 操作步骤: 选中E2至E7单元格,单击“数据”选项卡中的“数据有效性”——数据有效性,设置有效性条件为许诺整数,介于最小值20与最大值60之间,同时犯错警告输入错误提示。 当E2单元格输入66时,那么会弹出对话框如下: (二)条件格式 “开始”选项卡—“条件格式” 1、挑选重复数据 例使重复的名字突出显示 操作步骤: 选中A2至A19单元格区域,依次点击条件格式——突出显示单元格规那么——重复值 二、突出显示最大值与最小值 例以上工资表为例,要求突出显示“工资”列中最大工资与最小工资,以红色填充最大工资,以绿色填充最小工资。 操作步骤: (1)选中F2至F19单元格区域,依次点击条件格式——项目选取规那么——值最大的10项。 将左边的10改为1,右边的设置中点击自定义格式,选择填充,红色,即可。 (2)选中F2至F19单元格区域,依次点击条件格式——项目选取规那么——值最小的10项。 方式同理。 3、图标集与数据条的利用 (1)图标集 例如下表,给下表中工资数据加上图标集,其中大于7000,5000至7000之间,小于5000别离标记上不同的图标。 操作步骤: 1)选中F2-F19,条件格式——图标集——标记(第一行第二项) 2)选中F2-F19,条件格式——治理规那么——编辑规那么,将类型从“百分比”改成“数字”,值依次输入7000,5000,确信即可。 (2)数据条 假设是不想用图标集,想更直观的看到每位员工的工资差距,可尝试利用数据条。 操作步骤: 选中F2-F19单元格,点击条件格式——数据条——选择任意一种颜色即可。 (三)挑选 挑选是EXCEL的一个最常常利用的数据分析功能,很多人都会利用数据挑选功能。 不仅能够挑选多个条件,还可对日期、时刻数据进行特殊的挑选,和依照颜色来进行挑选。 一、多条件高级挑选 例,关于下表数据,要把知足2021年7月,华北地域,销售额大于500的数据挑选出: 操作步骤: Ø选中数据清单的区域,单击: 开始——排序和挑选——挑选 Ø依次选择订购日期的“2021年7月”,销售地域的“华北”,销售额“数字挑选”——大于——输入数字500 2、利用列表(表)实现高效挑选 尽管自动挑选超级有效,可是无法自动扩展挑选区域,假设是数据区域右边增加几列数据,这几列数据是不能已经成立的挑选区域中的,假设是要把这几列数据也成立挑选,需要先取消挑选,然后再成立自动挑选。 如此表,如在表右边加一列“运货商”,那么需取消挑选再从头选择新区域再进行挑选。 但通过创建表的形式自动扩展挑选区域。 操作步骤: Ø将光标确信于数据区域内任意单元格 Ø点击“插入”——“表格” Ø在“销售额”右边增加一列“运货商”,那么此列自动进入挑选区域 三、数据透视表 (一)制作大体的数据透视表 第一保证数据源是一个数据清单 单击数据清单中的任一非空单元格,单击“插入”选项卡,再单击功能区最左侧“数据透视表”——“数据透视表” 在默许情形下,系统自动将选取整个数据清单作为数据源,假设是数据源区域需要修改,那么可直接在“选择一个表或区域”输入栏中从头输入数据区域。 确信数据源后,单击“确信”按钮,EXCEL将自动新建新工作表,并在此工作表上创建空白的数据透视表。 “报表挑选” 用于添加报表挑选字段,能够用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内,创建挑选字段。 “列标签”区域,用于添加列字段,能够用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内,创建列字段。 “行标签”用于添加行字段,能够用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内,创建行字段。 “数值”用于添加汇共计算的字段,能够用鼠标把字段列表区域窗格内的某个字段或其他小窗格内的字段拖放到此窗格内,创建值字段。 例: 依照左表的销售记录数据,需统计出在不同销售渠道下,各地域不同类别商品的销售额合计,也即右表所示。 操作步骤: Ø选中原数据清单,插入数据透视表 Ø将“渠道”字段拖至“报表挑选”区域,将“城市”字段拖至“行标签”区域,将“类别”字段拖至“列标签”区域,将需要汇共计算的字段“销售额”拖至“数值”区域。 注: 1.如假设不需要数据透视表最右列“共计”字段,可将单元格定位在“共计”,右键点击“删除共计”,后如欲恢复,可在鼠标定位在数据透视表任意单元格,右键“数据透视表选项”,选择“汇总和挑选”,从头勾选上“显示行共计” 2.本例中,数值汇总方式为求和,但也能够通过左键点击“数值”区域的“销售额”字段,选择“值字段设置”,可修改成以平均值方式或计数方式显示。 例: 以例1中原数据为数据源,需统计出在各时刻段,各地域不同类别商品的销售额合计,也即右表所示。 操作步骤: Ø选中原数据清单,插入数据透视表 Ø将“渠道”字段拖至“报表挑选”区域,将“日期”字段拖至“行标签”区域,将“类别”字段拖至“列标签”区域,将需要汇共计算的字段“销售额”拖至“数值”区域。 Ø鼠标单击行标签区域内任一单元格,右键选择“创建组”,选中“月”和“年” Ø鼠标定位在数据透视表表格区域,“设计”选项卡,“报表布局”——“以表格形式显示”,进一步点击“报表布局”——“重复所有项目标签”可得。 例: 应收账款账龄分析: 要求依照提供的应收账款清单,分析富华食物、尚展食物、天宝食物、裕庆食物这几家客户各不同超期天数段中,应收账款超期的金额合计。 操作步骤: Ø选中A3至K43单元格区域,插入“数据透视表”,设置行标签为“公司名称”字段,列标签为“超期天数”字段,求和项为“未收金额”字段,取得如下数据透视表。 Ø但因如此的统计仍然不够直观清楚,故需进一步将超期天数进行分类。 鼠标定位在工作表第四行数据透视表内上除A4之外,比如“46”、“49”、“54”……中任意一单元格,右键选择“创建组”,使得超期天数以起始于“1”,终止于“270”,步长也即距离为“60”的形式显示。 Ø进一步修转业标签和列标签名称可取得: (二)利用多重数据区域制作数据透视表 例: 某公司有三个公司,三个公司2021年各税种缴纳情形如下表: (单位: 万元), 操作步骤: Ø依次按下ALT与D,同时松开后再按键盘上的P字母,显现如下对话框,调出数据透视表向导,选择“多重归并计算数据区域”,下一步,选择“自概念页字段”, Ø选定区域,点击图示红色圆圈内按钮,选择“广州分公司”数据清单区域,设页字段数量为1,命名为“广州分公司”,后点击“添加” Ø Ø继续重复同一动作,将上海分公司和北京分公司别离添加进区域,并别离设页字段数量为1,别离命名为“上海分公司”和“北京分公司”。 Ø单击“下一步”,继续点击“完成”。 Ø新工作表中即形成汇总后的数据透视表,而且能够通过页1字段的选择,别离显示广州分公司、上海分公司和北京分公司的数据。 可将“页1”直接编辑为“分公司” Ø通过对字段在“报表挑选”区域、“列标签”区域、“行标签”区域的拖动,可实现各类形式表格的变换,如以下图所示: (三)运用数据透视表进行表格数据对照分析 例: 运用数据透视表进行银行对账 如下左表为企业内部的银行存款日记账,右表为银行对账单记录。 此刻要进行银行对账,找出两个工作表中不一致的数据。 操作步骤: Ø新建工作表,将两个工作表进行归并(手工归并,将银行对账单的记录依照对应的字段粘贴于企业银行存款日记账记录下方),归并的时候注意,银行对账单记录中的贷方金额应粘贴在新工作表借方,银行对账单记录中的借方金额应粘贴在新工作表贷方,另外,在最右方加一列“单位”字段,注明是企业记录与银行记录以便区分。 Ø依照新工作表创建数据透视表,选择“贷方”为行标签,“单位”为列标签,求和项为“贷方”。 Ø持续选中数据透视表中B5至C16单元格,设置条件格式,使得左右不一致的记录填充为红色,具体操作为选择“开始选项卡”——条件格式——新建规那么——利用公式确信要设置格式的单元格,并在空白方框中输入公式: =$B5<>$C5。 设置格式为填充为红色,点击“确信”。 Ø经上一步骤后,数据透视表便将企业银行存款日记账与银行对账单中贷方不一致的记录突出显示出来,如以下图所示 Ø用一样的方式,亦可将借方记录中企业银行存款日记账与银行对账单中不一致的记录突出显示。 (操作方式为可将上一步骤中的数据透视表选中,复制到A22单元格,将行标签改成“借方”,求和项改成“借方”) (四)动态数据透视表制作 例: 依照原始数据(下方左图),运用数据透视表得出白米、白奶酪、饼干三种类别产品在各月的销售额合计(下方右图)。 操作步骤参考“ (一)制作大体的数据透视表”(注: 可通过列标签的挑选功能只选择白米、白奶酪、饼干这三种产品) 此刻,如假设在原数据表格底下添加4月5月6月的销售数据,数据透视表又需从头选择数据区域,从头制作才能更新数据透视表中的数据,此刻能够通过“创建表”的方式制作动态,不需要从头选择数据源即能够更新的数据透视表。 操作步骤: Ø单击数据源表格中任一单元格,点击“插入”选项卡中“表格” Ø那么原数据表格呈如下样式,此刻再依照此表新建数据透视表。 Ø在原数据表格下方添加2021年4月、5月、6月的数据。 Ø在第二步中生成的数据透视表中,定位任意单元格,右键“刷新”,即可更新数据透视表中的数据 (五)通过自概念计算字段进行统计分析 当EXCEL提供的已有的汇共计算和自概念显示方式不能知足需要时,EXCEL还许诺向数据透视表添加自概念计算字段,也确实是为数据透视表添加新的计算指标。 计算字段是通过对表中现有的字段执行计算后取得的新的字段,一旦创建了自概念字段或概念项,EXCEL就许诺在报表中利用他们,就像他们是数据源的一部份那样。 例: 原数据为采购部、人事部各月份预算费用额与实际费用额,需要对各费用类别进行归集统计分析实际发生额与预算额的不同及不同率。 形成右图所示数据透视表 操作步骤: Ø依照数据透视表制作的大体步骤,制作出如下表格: Ø将光标定位在“求和项-实际发生额”单元格,点击“选项”选项卡,选择“域、项目和集”——“计算字段”。 Ø在弹出对话框中名称中输入“不同额”,公式=实际发生额-预算额(实际发生额与预算额是通过别离双击字段列表中的名称得来的),点击确信。 Ø通过一样的方式与步骤,继续添加“不同率”字段。 (不同率=不同额/预算额) Ø通过“条件格式”中的“图标集”设置不同额和不同率两列中,大于0的数字前显示向上箭头,小于0的数字前显示向下箭头。 四、图表制作与美化 应用EXCEL图表不仅能够清楚地显示数据间的不同,而且能够找到数据内的逻辑关系,找出数据的转变趋势,据以做出合理的预测。 设计完美的图表与枯燥的数据清单相较,更能迅速有力地传递数据。 常常利用图表类型及用途: 柱形图: 用于显示一段时期内数据的转变或各项之间的比较关系 折线图: 用于显示数据之间的转变趋势 饼图: 用于显示数据系列中各项占整体的比例关系,注意饼图只显示一个数据系列 (一)柱形图 1.大体柱形图 例某公司2021-2021年四个季度的净利润数据如表所示,请据此表绘制柱形图 操作步骤: Ø选取数据范围。 利用鼠标持续选中工作表中该数据所在的区域。 选中A2至E4区域。 Ø选择图表类型。 选择“插入”选项卡,“柱形图” Ø Ø柱形图的子类别别离有二维柱形图,三维柱形图等,本例选择二维柱形图中的第一个。 Ø在原电子表格右下角生成了该数据的柱形图表格 Ø运用“图表工具”。 选中表格,显示图表工具,别离是“设计”、“布局”、“格式”三个选项卡。 通过“设计”选项卡,切换行/列数据,改变图表样式,从头选择数据等 通过“布局”选项卡,能够设置图表题目、图例、数据标签、坐标轴、网格线等。 Ø增加例中图表题目、调整图例位置,及不显示网络线,可得以下图: 2.动态柱形图 当用户需要查看多个地域不同月份销售额时,假设是采纳柱形图,那么需绘制多个柱形图,不便查看,通过创建下拉菜单式的动态图表,可显示不同地域的销售数量。 操作步骤(例): Ø因为动态图表中涉及到下拉组合框,因此需在EXCEL中添加“开发工具”选项卡,具体做法为: 文件——选项——自概念功能区,将开发工具勾选上以后,即可 Ø在A11单元格中输入数值“1”,在B11单元格中输入=INDEX(B4: B9,$A$11),按回车键后向右复制公式至G11单元格中,取得上海地域各月的销量。 如下图 Ø选择需创建图表的区域,那个地址选择B11至G11单元格区域,在“插入”选项卡下单击“柱形图”第一种类型。 Ø显现柱形图后,选中右键点击“选择数据”,单击水平分类轴标签,编辑,选择轴标签区域,用鼠标选中B3至G3单元格,那么柱形图水平轴即变成月份。 Ø Ø删除图例和网络线,添加图表题目,并修改成“各部门销售业绩表”。 Ø在“开发工具”选项卡被选择“插入”,选择表单控件中的第二项“组合框”,并在柱形图空白位置画出。 Ø Ø右键点击组合框,选择“设置控件格式” ,在数据源区域被选择A4至A9单元格区域,单元格链接选择A11单元格(注,这些操作不需要通过键盘输入,而是通过方框右边的选项按钮与鼠标的配合完成),勾选三维阴影,即可完成。 Ø为柱形图添加数据标签,可任意选择组合框中的各地域选项,即可显现对应地域上半年销售数量柱形图。 (二)折线图 EXCEL电子表格能够对图表中的图表项进行修改。 例如,调整各图表项字体的大小和背景色等,用户还能够将某一系列的数据由柱形图改成折线图。 将例中已完成的柱线图选中,右键选择“更改图表类型”,选择“折线图”,第四种类型。 别离选中两条折线,添加数据标签并手动调整标签位置后,图表变成如下所示: 注: 在某些情形下,能够对折线图添加“趋势线”,方式: 图表工具——布局——趋势线。 并可依照趋势线对以后进行预测。 Ø选择“趋势线”——“线性趋势线”,为2021年净利润添加趋势线 Ø选中趋势线,右键点击“设置趋势线格式”,调整趋势线颜色为红色,并前推一周期。 (三)饼图 1.大体饼形图 例: A公司的股本组成情形如下: 中国大洋食物开发集团占总股本49%,长岛食物研究所占总股本21%,企业高管层人员赵正华占总股本15%,钱小红占总股本49%,孙伟占总股本5%,请绘制饼形图,描述该公司的股本结构。 操作步骤: Ø持续选中B2至G3单元格区域,选择“插入”选项卡,图表组,单击“饼图”按钮,弹出“饼图”的子菜单,从被选择“三维饼图”的第二个,即“分离型饼图” ,如下图。 Ø修改图表题目,并添加数据标签 2.复合饼形图 例: 复合饼图制作——企业资金组成。 要求依照下表数据制作复合饼图 操作步骤: Ø选中A2至C5单元格,“插入”选项卡,选择二维饼图,第三种“复合饼图”
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 案例 操作 步骤