Excel在财务中的应用 第3版 课件 项目一 Excel制作财务会计表格

项目一Excel制作财务会计表格项目一

任务一创建并保存工作表任务二保护及打印工作表任务三单元格操作任务四提取职工出生年月和计算工龄任务五制定薪金等级o1创建并保存工作表1.创建工作表（1）通过双击桌面快捷菜单

（2）

右击桌面空白处，选择“新建”中的“MicrosoftExcel工作表”命令（3）

选择“开始”→“所有程序”→“Microsoftoffice”→“MicrosoftExcel”任务一创建并保存工作表

另存为D盘中的新建的“万隆灯具有限公司财务表格”文件夹里，更名为“万隆灯具有限公司职工信息表”。2.保存工作表任务一创建并保存工作表o2保护及打印工作表1.保护工作表

单击“审阅”选项卡里的“保护工作表”按钮；设置密码后，就只有知道密码的人才能打开Excel文件。而【保护工作表】加密以后，虽然能够看到表格中的数据，却不能修改。任务二保护及打印工作表1.保护工作表保护工作表只对保护的某一张工作表起效；保护工作簿与保护工作表类似，即不能再改变工作簿的结构，如不能再增加工作表等。要分发出去的固定结构表格，为了防止别人篡改，又要给别人填写其中的信息，怎么办？步骤一：取消锁定1.保护工作表①选中黄色的区域③取消【锁定单元格】②在【开始】选项卡下单击【格式】按钮21.保护工作表步骤二：设置允许编辑区域在【审阅】选项卡下单击【允许用户编辑区域】按钮。单击【新建】按钮，打开创建窗口，为此区域命名，然后单击【确定】按钮，返回允许用户编辑区域对话框步骤三：保护工作表④取消勾选第一项【选定锁定单元格】单击【保护工作表】按钮，打开保护工作表对话框③为黄色区域以外默认锁定保护区域设置编辑密码1.保护工作表有些工作表不能删，但是仅仅作为参数表，并不需要频繁查看，又或者不想让别人看到，那就可以把它们隐藏起来。隐藏工作表1.保护工作表

当一张工作表中含有成百上千条数据时，那么在移动滚动条时，表头会随着滚动条的下移或右移就看不见了，这时就可以利用“视图”工具栏中“冻结首行”选项，设置冻结窗格将首行或首列冻结住，方便查看最后的合计栏。2.冻结工作表任务二保护及打印工作表冻结窗格，可以在滚动工作表时让锁定的行、列始终显示在原来的位置。2.冻结工作表有时要自定义冻结的行数或列数，就要用到【冻结拆分窗格】。2.冻结工作表2.冻结工作表冻结首行2.冻结工作表冻结首列2.冻结工作表冻结窗格选中区域后，右键设置打印区域，可局部打印选中区域。任务二保护及打印工作表3.打印工作表(1)选择需要打印的区域

打印预览后发现整个文档右边和下边显得比较空，这时再单击“页面设置”，通过调整边距使文档居中。3.打印工作表（2）小表大打或大表小打

如果出现需要打成一页的话，可以通过设置中的最下面选项中的下拉按钮，将“无缩放”改成“将工作表调整为一页”。3.打印工作表（2）小表大打或大表小打调整列宽还无法塞进一页时，就要考虑整体缩放。在页面设置中，精确修改缩放比例。3.打印工作表大表格打印时，往往需要重复打印标题行作为表头，这样每一页的数据列都有标题，方便查看。在【页面布局】选项卡下，单击【打印标题】按钮并选择需要重复到每一页的区域即可。3.打印工作表（3）每页重复打印标题行3.打印工作表o3单元格操作

单元格是工作表中最小的构成单位，对工作表的操作必须要先从单元格开始，单元格可使用的数据类型有：数值、文本、公式。数值可以为数字，也可以为日期或时间。

对于一般的数据只能逐个输入，但对于有序、相似或者相同的数据的输入，则可以采用填充功能。任务三单元格操作1.等差序列

在A2单元格输入1，将鼠标移到A2单元格的右下角时，会出现一个黑色的“+”标志，点击并用鼠标左键向下拖动，点击即可。知识技能一：数据填充2.等比序列知识技能一：数据填充

在弹出的对话框中，序列产生在”列”，

类型选择“等比序列”，步长值输入数字2，终止值输入数字32。若要完成首项为2、步长为2、终止值为32的等比序列。知识技能一：数据填充3.自定义序列Excel中还有一个很方便的功能，就是可通过拖动单元格右下角填充柄自动生成相应序列，如在两个相邻的单元格内有“甲”“乙”两个汉字，选中这两个单元格拖动右下角填充柄，可以向下或向后顺次生成丙、丁、戊……。需要说明的是，但这些序列必须是Excel中已经定义的。知识技能一：数据填充3.自定义序列

若要添加自定义序列，需要通过“文件”选项卡中的“选项——高级——编辑自定义列表”。拖曳法选中单元格后，拖动右下角的填充柄批量生成总结双击法表格数据成百上千，用拖曳法向下填充有时很麻烦。此时，可以采用双击法，只要紧挨着的一列有连续的数据，双击填充柄就能将当前单元格的数据填充到最后一行。批量生成总结指定条件自动生成序列批量生成总结知识技能二：数据验证

数据验证是对单元格或单元格区域输入的数据从内容到数量上的限制。比如，对于性别的统计，可以限制单元格内容只能输入“男”或“女”。

在“数据”选项卡中，单击“数据验证”下拉按钮。知识技能二：数据验证在弹出的对话框中逐项填写。

首先，在“设置”选项卡下的“允许”中选择“序列”，在“来源”中输入“男,女”。“来源”中区别各选项需要用英文状态下的逗号隔开，请特别注意标点。知识技能二：数据验证如果想要完善内容，可以切换到第二张“输入信息”选项卡和第三张“出错警告”选项卡。知识技能二：数据验证如果想要完善内容，可以切换到第二张“输入信息”选项卡和第三张“出错警告”选项卡。知识技能三：单元格格式设置输入长数字

单元格输入的默认格式都是常规型，但可以通过右击单元格，选择“设置单元格格式”命令，从而改变单元格的类型。当输入长度超过11位，就会自动以科学计数法来显示。如果数字长度超过15位，不仅不能正常显示，而且超过15位后的尾数会自动都变成数字0。方法1先将单元格式设置成文本属性，再输入长数字；方在输入前先键入单引号（英文状态下），键入的数字符被看成是字符串，但显示时不会将单引号显示出来。关于日期格式的说明由于Excel具有强大的自我纠正能力，左图中的几种输入形式，都是可以接受的。而右图的输入形式，分隔符、日的写法出错了，这些就是需要后期整理的错误日期。o4提取职工出生年月和计算工龄任务四提取职工出生年月和计算工龄

在输入公司职员的信息后，还需要提取职员的出生年月，这时需要用到两类函数。文本函数，用于处理文字串。例如，可以改变大小写或提取文字串。日期函数，用于分析和处理日期值和时间值。含义：

MID函数可以从一个字符串中截取出指定数量的字符。1.MID函数函数语法：MID(text,start_num,num_chars)其中，text表示准备提取字符串的文本字符串；start_num是准备提取的第一个字符串的位置，

num_chars是指定所要提取的字符串长度。知识技能五：文本函数1.MID函数在MID函数参数的对话框中，分别输入I2、7、8，表示从I2单元格的第7位开始截取8位字符，会在对话框看到出现“19820312”的结果。知识技能五：文本函数含义：LEFT函数是从一个文本字符串的第一个字符开始返回指定个数的字符。函数语法：LEFT(text,num_start)其中,text表示准备提取字符串的文本字符串；

num_start是提取的字符串长度。2.LEFT函数2.LEFT函数

选中J2单元格，输入“=LEFT（I2,14）”，代表的意思是在I2单元格中从左边第一个字符串开始取14位字符，结果如图J2单元格所示。函数语法：RIGHT(text,num_start)其中，text表示准备提取字符串的文本字符串；

num_start是提取的字符串长度。3.RIGHT函数含义：RIGHT函数是从字符串右边取出的指定数量的字符。知识技能五：文本函数3.RIGHT函数显然，光用LEFT函数无法获取出生日期，此时还需要RIGHT函数帮忙。将J2单元格原函数补充为“=RIGHT(LEFT(I2,14),8)”，意味着将原结果“41131619820312”从右边取8位数，得到最终想要的结果。含义：TEXT函数是将数值转换为按指定数字格式表示的文本。函数语法：TEXT(value,format_text)其中，value

数值、计算结果为数字值的公式，或对包含数字值的单元格的引用；

format_text是文本形式的数字格式。4.TEXT函数知识技能五：文本函数4.TEXT函数

虽然用LEFT、RIGHT、MID函数能把出生日期提取出来，但并不符合真正的日期格式。

选中J2单元格，在原先已有文本函数前面输入TEXT，会在下方出现TEXT函数。双击蓝色的“TEXT”，然后点击“插入函数”按钮，就会弹出TEXT的函数对话框。4.TEXT函数

第一个value参数就是MID（I2,7,8），意思就是通过MID函数取出的19820312这串数字，第二个format_text参数需要输入日期格式。

正确的日期格式只有两种表示方式：用斜杠“/”区分：年/月/日用横线“-”区分：年-月-日

但是在格式设置中的类型只有用横线表示的，也就是说用format_text的语言只有“0000-00-00”这样的格式可以输入，输入后可以看到对话框中出现“1982-03-12”这样的显示结果。

特别需要注意的是，输入格式类型时一定要加上英文状态下的双引号，这样才能将原先的数字格式转换为日期格式。知识技能六：日期函数1.DATE函数含义：DATE函数用于返回日期格式，使用这个函数之后，即使单元格格式为“常规”，则结果也将设为日期格式。函数语法：

DATE(year,month,day)。其中，year、month、day分别对应年、月、日。

以身份证为的员工为例，用函数提取出该员工的年月日。含义：

DATEDIF函数，用于返回两个日期之间的年、月、日间隔数。可用于计算工龄。函数语法：DATEDIF(start_date,end_date,unit)。其中，start_date代表时间段内的第一个日期；

end_date代表结束日期；

unit为所需信息的返回类型，具体分为六类。2.DATEDIF函数知识技能六：日期函数2.DATEDIF函数Unit通常有Y、M、D、MD、YM、YD六种类型。知识技能六：日期函数使用DATEDIF函数来计算职工的工龄。

因为DATEDIF函数是隐藏函数，无法使用函数对话框，所以只能直接在单元格中直接输入。o5制定薪资等级

职工信息表的最后一块内容是制定薪金等级。公司规定的薪酬标准：总经理12000元/月，研究生学历及以上且职称为高级或者是部门经理都是9000元/月，其他职工6000元/月。这时需要用到逻辑函数来解决了。任务五制定薪金等级逻辑函数，可以进行真假值判断，或者进行复合检验；常见的有AND函数、OR函数和IF函数。含义：AND函数指的是所有参数的逻辑值为真时，返回TRUE；只要有一个参数的逻辑值为假，即返回FALSE。函数语法：

AND(logical1,logical2,…)其中，“logical1,logical2,…”表示待检测的1到255个条件值。知识技能七：逻辑函数1.AND函数含义：OR函数指的是在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE；所有参数的逻辑值为FALSE，才返回FALSE。函数语法：

OR(logical1,logical2,…)其中，“logical1,logical2,…”表示待检测的1到255个条件值。2.OR函数知识技能七：逻辑函数研究生学历及以上且职称为高级AND（D2=“研究生”，E2=“高级”）1.AND函数2.OR函数研究生学历及以上且职称为高级或者是部门经理OR(AND(D2=“研究生”,E2=“高级”)，G2=“部门经理”)含义：IF函数是根据指定的条件来判断TRUE或FALSE，并根据真假值返回相应的内容。函数语法：

IF(logical_test,value_if_true,value_if_false)。其中，logical_test是逻辑判断，如果真，返回value_if_true的内容；如果假，返回value_if_false的内容。3.IF函数知识技能七：逻辑函数

现有一些学生的成绩，如果高于60分（含），就是及格，不然就是不及格。总经理12000