财务管理Excel应用实训教案Word格式文档下载.docx
《财务管理Excel应用实训教案Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《财务管理Excel应用实训教案Word格式文档下载.docx（21页珍藏版）》请在冰豆网上搜索。
《Excel在财务管理中的应用》，李江霞编，北京邮电大学出版社，2011年12月第1版；
《Excel在财务中的应用》，黄新荣编，人民邮电出版社。
时间安排
教学过程设计
2课时
4课时
引入：
在会计实务工作中，对数据进行处理经常需要用到函数，本项目讲叙会计实务工作中常用的函数的功能和使用方法。
会计务实中常用的函数有SUM求和函数、AVERAGE求数学平均数函数、IF条件函数、MAX和MIN求最大值最小值函数、SUMIF条件求和函数、VLOOKUP函数、LEFT函数等。
实训一基本常用函数实训
【实训内容】
张三在实习期间应辅导员邀请统计全班成绩，为此他想到了会计实务中常用的函数，于是尝试用Excel来统计本班成绩。
【实训分析】
学生成绩表除包括学号、姓名、性别、出生年月等基本信息外，还有各门课的成绩。
学生成绩表录入的原始数据如表2-1所示。
表2-1学生成绩表单位：
分
学号
姓名
性别
出生年月
是否党员
语文
数学
英语
001
张帆
女
1992年5月12日
FALSE
67
84
002
吴飞
男
1991年9月18日
75
54
003
花文静
1992年3月29日
TRUE
73
45
66
004
李红
1992年10月25日
85
81
005
范自立
1990年12月20日
58
74
006
张菲
1992年3月21日
90
007
李弘强
1992年6月16日
48
78
68
008
夏天
1992年8月27日
86
65
009
王晓红
1992年2月15日
77
010
高自强
1990年11月2日
72
011
李彤彤
1992年3月25日
63
57
012
王蒙蒙
1990年2月5日
53
87
013
蔡建雯
1991年12月2日
46
014
俞涛
1991年11月12日
62
015
甘林
1990年9月2日
利用求和函数SUM计算各位同学的总分，利用平均数函数AVERAGE计算各位同学的平均分，利用IF条件函数计算总评成绩，求单科最高分和最低分可以用最大值函数MAX和最小值函数MIN。
【相关知识】
（1）SUM函数是求和函数。
函数类型：
SUM函数是数学函数。
函数格式：
SUM（number1,number2,number3……）。
函数功能：
SUM函数可以对相应的区域或者数字求和。
（2）AVERAGE函数是求数学平均数函数。
AVERAGE函数是统计函数。
AVERAGE（number1,number2,number3……）。
AVERAGE函数可以对相应的区域或者数字求平均数。
（3）IF函数用于进行真假值的判断，根据逻辑计算的真假值返回不同的结果。
IF函数是逻辑函数。
IF（Logical_test，Value_if_true，Value_if_false）。
IF函数是执行真假值判断，根据逻辑计算的真假值返回不同的结果。
（4）MAX函数是求最大值函数。
MAX函数是统计函数。
MAX（number1,number2,number3……）。
MAX函数可以返回一组数据中的最大值，忽略文本和逻辑值。
（5）MIN函数是求最小值函数。
MIN函数是统计函数。
MIN（number1,number2,number3……）。
MIN函数可以返回一组数据中的最小值，忽略文本和逻辑值。
【操作步骤】
（1）启动MicrosoftOfficeExcel2010，建立一个工作簿文件，命名为“成绩统计表”。
（2）把sheet1更名为“学生成绩表”，在A1单元格中输入“学生成绩表”。
在第2行中依次输入“学号”“姓名”“性别”“出生年月”“是否党员”“语文”“数学”“英语”等作为标题行，如图2-1所示。
图2-1学生成绩表
（3）选定“学号”所在的A列，在“开始”选项卡中的“单元格”功能组中选择“格式”菜单中的“设置单元格格式”命令，弹出“设置单元格格式”对话框。
在“数字”选项卡中的“分类”列表框中选择“文本”，单击“确定”按钮，如图2-2所示。
在A3单元格中输入001，在A4单元格中输入002，在A5单元格中输入003，选中A3:
A5单元格区域，将鼠标指针移到选中区域右下角的填充控制点上，拖动填充柄至A17单元格。
图2-2“设置单元格格式”对话框
（4）按图2-3所示分别输入学生姓名、性别、出生年月、是否党员及各科成绩，并调整好相应的列宽。
图2-3学生成绩表基础数据
（5）设置出生年月的格式。
选择D3:
D17区域，在“开始”选项卡中的“单元格”功能组中选择“格式”菜单中的“设置单元格格式”命令，弹出“设置单元格格式”对话框，如图2-2所示。
在“数字”选项卡中的“分类”列表框中选择“日期”分类。
（6）在I2、J2、K2中分别输入“总分”“平均分”“总评”标题。
求总分可以用公式，也可以用SUM函数。
选择I3，在“公式”选项卡中的“函数库”功能组中选择“数学和三角函数”菜单中的SUM命令，弹出“函数参数”对话框，如图2-4所示，默认的求和区域为F3:
H3，单击“确定”按钮即可。
该区域也可以自己拖选或者直接输入，并自动填充到I17单元格中完成其他学生的成绩求和。
图2-4设置SUM函数参数
（7）在J3单元格中利用AVERAGE函数求该学生的平均成绩。
选择J3单元格，在“公式”选项卡中的“函数库”功能组中的“其他函数”菜单中执行“统计”→“AVERAGE”命令，弹出如图2-5所示的“函数参数”对话框。
默认的求平均数的区域为F3:
I3，这里需要将其改为F3:
H3，单击“确定”按钮，完成该学生平均分的计算。
其他学生可以使用自动填充的方法完成函数的填充以求得平均分。
图2-5设置AVERAGE函数参数
（8）总评成绩是根据平均分是否超过60分来确定是否通过，小于60分为“NO”，否则为“PASS”。
这里就要用到逻辑函数IF函数。
选择K3单元格，在“公式”选项卡中的“函数库”功能组中选择“逻辑”菜单中的IF命令，弹出如图2-6所示的“函数参数”对话框。
图2-6设置IF函数参数
在“Logical_test”文本框中输入“J3<
60”，在“Value_if_true”文本框中输入“NO”（当条件为真时返回的值，即平均分低于60分时候返回NO，系统会自动加上双引号），在“Value_if_false”文本框中输入“PASS”（当条件为假时返回的值，即平均分高于或等于60分时返回PASS，系统会自动加上双引号）。
其他学生的成绩可以用自动填充的方式完成函数的填充。
（9）选择A18:
E18合并并居中，在其中输入最高分；
选择A19:
E19区域合并并居中，在其中输入“最低分”。
求最高分和最低分，需要用到MAX函数和MIN函数。
单击E18单元格，在“公式”选项卡中的“函数库”功能组中的“其他函数”菜单中选择“统计”→“MAX”命令，弹出“函数参数”对话框，如图2-7所示。
“Number1”文本框中的默认区域为F3:
F17，也可以手动输入或者拖选，完成后单击“确定”按钮，用自动填充的方式填充到J18单元格。
图2-7设置MAX函数参数
单击E19单元格，在“公式”选项卡中的“函数库”功能组中的“其他函数”菜单中选择“统计”→“MIN”命令，弹出如图2-8所示的“函数参数”对话框，“Number1”文本框中默认的区域为F3:
F18，将其改为修改为F3:
F17，因为实际的求最低分的单元格区域为F3:
F17，再单击“确定”按钮，用自动填充的方式填充到J19单元格。
图2-8设置MIN函数参数
（10）选择A1:
K1单元格区域，在“开始”选项卡中的“对齐方式”功能组中选择“合并后居中”命令，然后在“字体”功能组中选择“加粗”和“倾斜”命令，将“字号”设为20磅；
选择A2:
K2单元格区域，将标题行加粗。
右击第1行，在弹出的快捷菜单中选择“行高”命令，弹出“行高”对话框，设置行高为30磅。
拖选第2行到第17行，设置行高为15磅。
K19单元格区域，在“开始”菜单中“单元格”功能组的“格式”菜单中选择“设置单元格格式”命令，在弹出的“设置单元格格式”对话框中打开“边框”选项卡，将A2:
K19单元格区域添加黑色细实线边框。
然后，切换至“边框”选项卡，在“线条”选项区域中的“样式”列表框中选择黑色单实线，“颜色”默认为“自动”，在“预置”选项区域中依次单击“外边框”和“内部”，如图2-9所示。
图2-9“设置单元格格式”对话框
“成绩统计表”工作簿中的学生成绩表的统计工作最终完成，如图2-10所示。
图2-10“成绩统计表”效果图
实训二高级常用函数实训
张三大学毕业后进入安徽中鼎集团公司财务部实习，公司要求他用Excel来建账、设置账户并且建立总账。
所谓建账，就是用Excel建立一个工作簿，并建立若干张工作表，用以分别存放会计科目及其期初余额、记账凭证，以及根据记账凭证自动生成的总账和明细账等。
设置账户即建立一个会计科目及余额表，会计科目是会计记账的核心，它主要有三个功能：
一是会计分录的对象；
二是记账的标准；
三是制表的纲目。
在日常的会计核算中，会计科目一般分为一级科目、二级科目及明细科目，其中一级科目是国家财政部统一规定的。
总分类账简称总账，是根据总分类科目（一级科目）开设账户，用来登记全部经济业务，进行总分类核算，提供总括核算资料的分类账簿。
试算平衡表是列有总分类账中所有账户及余额的简单表格。
本实训通过建账、设置账户、输入凭证和建立总账来熟悉高级基本函数的使用。
（1）SUMIF函数是条件求和函数。
SUMIF函数是逻辑函数。
SUMIF（搜索范围、搜索条件、求和范围）。
SUMIF函数可以对在搜索范围中满足条件的单元格对应的求和范围区域求和。
（2）VLOOKUP函数用于表格数组的首列查找值，并由此返回表格数组当前行的对应列的值。
VLOOKUP函数是查找与引用函数。
VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）。
VLOOKUP函数在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
（3）LEFT函数用于基于所指定的子字符数返回其母字符串中从左边数起的第1个或前几个字符。
LEFT函数是文本函数。
LEFT（text，num_chars）。
LEFT函数基于所指定的字符数返回文本字符串中的第1个或前几个字符。
（1）根据安徽中鼎集团公司2014年10月的结余情况，建立11月的期初余额表。
启动Excel，建立一个工作簿文件，命名为“1411”。
（2）在“1411”工作簿中创建“封面”工作表，如图2-11所示。
图2-111311总账封面
（3）在“封面”工作表后面插入一张新的工作表，并命名为“1411会计科目及余额表”，并将第一行锁定，如图2-12至图2-14所示。
图2-12会计科目及余额表
（1）
图2-13会计科目及余额表
（2）
图2-14会计科目及余额表（3）
期初余额表中A76单元格中的“合计”是将一级科目的相关金额求和，可以使用SUMIF函授。
选中C76单元格，在“公式”选项卡中“函数库”功能组中的“数学和三角函数”菜单中选择“SUMIF”命令，弹出如图2-15所示的“函数参数”对话框。
图2-15设置SUMIF函数参数
在“Range”文本框中输入A:
A，在“Criteria”文本框中输入“"
?
"
”，在“Sum_range”文本框中输入“C:
C”。
其中，A:
A是搜索范围，代表A列，表示所有科目。
“"
”是搜索条件，表示编码为4位的所有科目，即一级科目，这里需要加上英文半角的双引号，并且问号要是英文半角状态下输入。
C:
C表示C列，是求和范围。
（4）在“1411”工作簿中插入一张新的工作表，命名为“凭证模板”，如图2-16所示。
图2-16凭证模板
设置“类别编码”、“凭证日期”、“附件”、“摘要”、“科目编码”等列数据的有效性。
凭证模板中“类别编码”所在列设为文本型。
“凭证日期”的有效性设置为日期，介于2014-09-01到2014-12-31之间，日期列的提示信息为“请输入日期，格式为YYYY-MM-DD”。
“附件”列只允许整数，范围在1～1000之间，提示信息为“请输入0～1000的整数”。
“摘要”列只允许输入文本，范围在1～50个字，提示信息为“请输入50字以内的摘要”。
“凭证日期”的设置步骤如下：
在“数据”选项卡中的“数据工具”功能组中的“数据有效性”菜单中选择“数据有效性”命令，弹出如图2-17所示的“数据有效性”对话框。
在“设置”选项卡中的“允许”下拉列表框中选择“日期”，“数据”下拉列表框中选择“介于”，开始日期中输入“2014-09-01”，结束日期中输入“2014-12-31”，选中“忽略空值”复选框。
“附件”“摘要”等数据有效性的设置参照“凭证日期”的数据有效性。
图2-17“数据有效性”对话框
科目编码的数据有效性的设置区别于上面数据的有效性的设置，具体操作如下：
在“公式”选项卡中的“定义的名称”功能组中的“名称管理器”菜单中选择“定义名称”命令，弹出如图2-18所示“新建名称”对话框。
在“名称”文本框中输入“科目编码”，在“范围”下拉列表框中选择“工作簿”，在“引用位置”文本框中输入“='
1411会计科目及余额表'
!
$A:
$A”，这样设置的原因是1411会计科目及余额表的A列存放的数据就是预设的会计科目，单击“确定”按钮。
单击“数据”选项卡，选择“数据工具”功能组中的“数据有效性”命令，弹出如图2-19所示的“数据有效性”对话框。
在“设置”选项卡中，“有效性条件”的“允许”下拉列表框中选择“序列”，并且勾选“忽略空值”和“提供下拉箭头”，在“来源”文本框中输入“=科目编码”。
“输入信息”选项卡中的提示信息输入“输入一级科目左对齐，下级科目右对齐！
”，这样可以在输入会计科目时清晰地区分一级科目和下级科目。
设置好后，当输入凭证时，只需单击右侧的下拉按钮就可以轻松选择会计科目编码，同时也可以直接输入会计科目的编码，这对不熟悉科目编码的用户来说，确实方便了很多。
图2-18“新建名称”对话框
图2-19“数据有效性”对话框
为了简化凭证输入时的汉字录入工作，可以设置“总账科目”和“明细科目”列的取值公式，只要输入会计科目编码，系统即可自动填入相应的总账科目和明细科目的名称。
取值公式要用到VLOOKUP函数和LEFT函数。
“总账科目”所在列的数据有效性的设置步骤如下。
①单击“凭证模板”工作表的F2单元格，打开“公式”选项卡，在“函数库”功能组的“查找与引用”菜单中选择“VLOOKUP”命令，弹出如图2-20所示的“函数参数”对话框。
图2-20设置VLOOKUP函数参数
②在“lookup_value”文本框中输入“LEFT（E2,4）”，表示搜索的值为E2单元格左边的四位，即一级科目；
在“Table_array”文本框中输入“'
A:
B”，表示搜索的区域为“1411会计科目及余额表”工作表的A列和B列；
在“Col_index_num”文本框中输入2，表示满足条件的单元格所在的列，这里是科目名称所在的列在搜索区域中的位置，1为首列；
在“Range_lookup”文本框中输入0，表示大致匹配。
单击“确定”按钮，其他单元格可以用填充方法填充该函数。
“明细科目”的数据有效性可以参照“总账科目”数据有效性进行设置。
（5）根据公司11月份的业务往来在凭证模板的基础上制作公司1411凭证，如图2-21至图2-23所示。
图2-211411凭证
（1）
图2-221411凭证
（2）
图2-231411凭证（3）
（6）在“1411”工作簿中新建一个工作表，并命名为“1411总账及试算平衡表”，将“1411会计科目及余额表”全部复制过来，但要注意用选择性复制粘贴数值，然后将子科目全部删除，留下总账科目，如图2-24所示。
图2-24总账及试算平衡表
（7）利用SUMIF函数计算出本期借方和贷方发生额。
本期借方和贷方发生额均来自于2014年11月的业务往来，即记账凭证“1411凭证”。
E2=SUMIF（'
1411凭证'
F:
F,B2,'
H:
H）
F2=SUMIF（'
I:
I）
（8）利用IF函数计算出期末借方余额和期末贷方余额。
G2=IF（（C2-D2）+（E2-F2）>
=0,（C2-D2）+（E2-F2）,0）
H2=IF（（C2-D2）+（E2-F2）<
0,ABS（（C2-D2）+（E2-F2））,0）
（9）选定E2:
H2单元格区域，将鼠标指针移动到该区域右下角，待鼠标指针变为黑色十字时向下拖动到第47行，完成函数和公式的自动填充。
（10）单击E48单元格，找到“公式”选项卡，选择“函数库”功能组的“数学和三角函数”菜单中的SUM命令，弹出如图2-25所示的“函数参数”对话框，“Number1”文本框中默认E2:
E47单元格区域，单击“确定”按钮，然后将SUM函数填充到F48、G48、H48中。
图2-25设置SUM函数参数
（11）选定A1:
H48单元格区域，在“开始”选项卡的“单元格”功能组中选择“格式”菜单中的“设置单元格格式”命令，弹出“设置单元格格式”对话框，选择“边框”选项卡，给总账及试算平衡表添加单实线边框，效果如图2-26所示。
图2-26总账及试算平衡表
作业
熟悉Excel常用函数
完成课后实训
教学反馈
升级会员 