会计财务报表Excel讲义

1、会计必备知识之Excel操作宋惠亮 整理前言 2第一章 基础篇 2第二章各种输入型态说明3第三章实例篇7第四章绘图篇8第五章资料篇10第六章作业16学习愉快2009年 前言本软件有三大重点：一、资料表：建立、编辑、修改（增加、删除）、搬移、复制、贴上、打印二、图表：一图胜过千言万语，此为最好的诠释。三、数据库：进入方式n 开始à程序集àMicroSoft Officeà Exceln 点选桌面上快捷方式画面介绍 一一说明如下：窗口标题列：主菜单：工具列：目前储存格地址：编辑列：储存格(cell) ：工作标签：工作标签卷动钮（最前/前一个/后一个/最后）工作标签：状

2、态列：滚动条（水平、垂直）壹、基础篇有关画面功能逐步操作、说明如下：一、在开启Microsoft Excel之后,画面出现一空白的活页簿窗口,该活页簿被命名为Book1(相当于书名;而底下的sheet1则像是各章之名称)二、 ，在储存格输入任意字,按´或esc键表示放弃储存格之内容,而Ö或enter表确认,fx为函数储存格中的文字靠左、数字靠右排列（自订值）三、画面控制键说明如下：1.ctrl+¬将游标移至当行之A栏(如游标停在X30移至A30)2.ctrl+® 将游标移至当行之IV栏(如游标停在x30移至IV30)3.ctrl+home将游标快速移至A

3、14.ctrl+将游标快速移至IV655364.5.ctrl+end将游标快速移至右下角（行列交集）非空白区6.page down/page up 将游标往后/前移一页7.f5将光标移至，键入地址（如x30）8.f2进入编辑模式四、改变工作环境1.改变储存格的网格线：工具(T)®选项®检视®网格线练习取消网格线与改变网格线之颜色之别再练习将显示之数据编辑列等选项，勾勾取消®确定2.改变储存格的工作表个数、字型、大小、预设档案位置：工具(T)®选项®检视®一般®确定练习改变上项内容分别为5、标楷体、12、a:五、工作

4、标签之应用1.欲将sheet1重新命名：将光标移至sheet1,按鼠标右键出现对话框,点重新命名,输入新的档名，如exercise2.欲将sheet1复制,将光标移至sheet1,左手按住ctrl，右手拖曳鼠标至其它卷标位置上，则变成sheet(1)，可再重新命名3.欲将sheet1及sheet删除(1)连续:将游标移至sheet1,按住shift1,点sheet4，此时sheet1及sheet2、3、4出现反白,按鼠标右键出现对话框,点删除,按确定则sheet1及sheet2、3、4消失(2)不连续:将游标移至sheet1,按住ctrl分别点sheet2, sheet5此时sheet1及sh

5、eet2、5出现反白,按鼠标右键出现对话框,点删除,按确定则sheet1及sheet2、5消失贰、各种输入型态说明如下：一、文字：1.功能与word相同。2.为跨栏置中,储存格内的字较多，欲跨越多个储存格时选用，如选A1至A3,排列方式即改变。说明如下：于a1储存格打入ntpu。为要比较文字的对齐方式，须复制相同内容，方式：1.利用右下角之填满控制点（黑十字），拖曳至a5先选取a1a5，编辑®填满®填满向下2.依序练习之功能3.于a6储存格打入国立台北大学，选取a6b6，点4.于a7储存格打入国立台北大学，格式®储存格®对齐练习水平（分散对齐）、垂直、文

6、字控制、方向之各项功能5.于a10打入国立台北大学，按alt+enter再打入信息中心，则二列内容打在同一储存格。二、数值：上图最右边代表小数字数递减; 右2代表小数字数递增1.于c1储存格依序打入85.2、-152、3500、6、0.9，选取c1:c5试点选工具列之小数字数对齐及三位一撇，是否美化？再试格式à储存格à数字à数值（货币、会计专用）à确定2.于c6键入+c1+c2+c3+c4+c53.于c7利用函数sum计算其总和有两种方式：一为点工具列S，范围为c1:c5二为插入à函数à在右侧点一下，打入要找的函数第一个字母（如sum

7、函数的第一个字母为s），会快速的出现相同字母的函数，找起来会快些。4.于c3增加一数值0.1，应如何处理? 鼠标移至c3，按右键出现便捷功能键à增加储存格à储存格下移，如此多出一储存格，打入0.1，看看两个总和有何变化？5.于c8 求算此6个数值的平均： =average(c1:c5)6.于c9 求算此6个数值的最大值：=max(c1:c5)7.于c10求算此6个数值的最小值： =min(c1:c5)8.于c11求算此6个数值的笔数： =count(c1:c5)9.于a12,a13分别打入 85,78210.于a16求算此15个储存格的笔数： =counta(a1:a15)

8、11.于a17求算此15个储存格数值数据的笔数： =count(a1:a15)12.于c12打入分数 1 1/413.定义所键入的值为正，改变其格式为红色且出现”赚”，若为负，改变其格式为蓝色且出现”赔”，若为零，不改变。格式à储存格à数字à自定à类型（可点类似格式修改）红色”赚”#,#0; 蓝色”赔”#,#0;0三、日期：1.于d1储存格依序向下打入2000/2/1，=date(1899,4,15), =today(),=now()，3位好友或家人的生日 ps：=today()则自动出现系统之日期（即今日之日期）=now()则自动出现系统之日期及时间（

9、即现在之日期时间）2.选取d1:d7，格式à储存格à日期以符合中国人习惯3.于e1，计算各人年龄：=year(today()-year(d1)+1，再以黑十字向下填满4.于f1，列出5月寿星：=if(month(d1)=5,”yes”,”) ，再以黑十字向下填满5.于g1，计算各人今年生日：=date(year(today(),month(d1),day(d1)6.于h1，计算各人还有几天生日：=if(g1>=today(),g1-today(),date(year(today()+1,month(d1),day(d1)-today()四、自订清单：1.于i1储存格打

10、入周六, 再以黑十字向下填满，或向右填满 了解其功能后，再删除右侧储存格内容2.系统提供之清单不足时，有二方式解决：一为工具à选项à自订清单à新增，依序打入你想加入的数据（如小明,小玉,小花.）à确定 于j1，打入刚刚你加入的数据中任一人名，再以黑十字向下填满二为于k1先将你想加入的数据依序打入（如喜爱的五种动物），工具à选项à自订清单à汇入，范围k1:k5à确定 于L1，打入刚刚你加入的任一动物，再以黑十字向下填满五、等差级数：1.于m1、m2，输入30,35后,将其反白使光标在储存格下方出现黑十字,往下或往右拉

11、则储存格出现30,35,40,45 (小数 点、负数亦可)2.显示周期性日期数据（如每周六）： a.于n1打入某一周六之日期（如2003/8/15）b.选取n1:n10c.编辑à填满à数列à类型，点日期、日期单位，点日间距值打7à确定六、转置矩阵：例一：欲将n1:p4矩阵转至n6:q81.于n1至p4依序打入 no name sex35 张三 男12 赵一 女25 孙山 男2.选取n6:q8,打入=transpose(n1:p4) 按ctrl+shift+enter即可例二：欲将r1:r9内容转为3*3矩阵1.于r1依序往下打入35 张三 男12 赵一

12、女25 孙山 男（每列打一资料，共9列）2.选取s1:u3,打入=transpose(r1:r9) 按ctrl+shift+enter即可七、if条件定义：于r1 打入1，r2则出现one，若打入2，则出现two，其余一律出现reenter于r2打入if(r1=1,”one”,if(r1=2,”two”,”reenter”)八、移动及复制储存格的作法移动：先将储存格反白,鼠标指着记号区，待出现白色箭头，按鼠标左键，拖曳至所要的储存格上复制：前述方法，按ctrl，拖曳至新的储存格上参、实例篇：一、针对学生成绩资料练习资料来源：.tw/hlsong 档案区 ex

13、cel score.xls，按鼠标右键，另存目标 d:/score.xls二、计算成绩的作法：1.于g1打入total，于g2，打入=d2*.3+e2*.3+f2*.4，再以黑十字向下填满2.于g22，打入=sum(g2:g21) -此为分母 3.于h1，打入%，于h2，打入=g2/$g$22可先打g22按两次f4，次点工具列之%，或格式à储存格à数值à%，指定小数位数，满意后，再以黑十字向下填满ps：绝对地址及相对地址，说明如下：当打入=G3/G23,鼠标向下拉,分子及分母之地址会跟着改变,称为相对地址之移动,然分母应固定不动,须改为绝对地址,因此在分母G23点

14、一下,按F4则变成为绝对地址(固定不动);(若多次按F4,则从G22$G$22G$22$G22G22变化)三、插入标题列的作法： 1.将游标停在A1列任一格,插入(I)à列,即增加一列。 2.于c1打入国立台北大学92学年度（按alter + enter）再打入数据处理成绩，可选数储存格，再点工具列之跨栏置中四、美化储存格：按键盘的F5,输入范围(如a1:g22);格式(O)à储存格，进行数字、对齐、字型、外框、图样等编排之练习五、外框的选取的方式先选取范围(如a1:a22)，格式(O)à储存格à外框,先选样式再点框线(如外框或上下左右),再选样式六、次

15、数分配(要统计各分数间之人数)先有一原始资料(如G3:G23),则在A30输入数据分配间距(ex:10、20、30.),先选取B3:b39输入=frequency($G$2:$G$22,A30:A39)按ctrl+shift+enter即出现所有结果七、防止工作表中之公式或重要内容被篡改1.选定整个工作表(栏列交叉处)格式à储存格à保护à取消锁定之勾勾à确定选定要保护之储存格（如g2:g22），储存格à保护à勾锁定à确定工具à保护à保护工作表à确定2.解除保护工具à保护à解除

16、保护工作表à确定八、打印 1.定义纸张方向及边界：档案à版面设定à页面，定义纸张方向、比例à确定档案à版面设定à边界，定义上下左右边界、置中方式à确定 2.定义页首/页尾：档案à版面设定à页首/页尾，定义字型、页码、总页数、打印日期、文件名称à确定3.定义换页标题重复： 先检查标题位置，如a1:h1档案à版面设定à工作表，于标题列，打入a1:h1à确定 4.预览打印 上页、下页、缩放、设定、边界、分页预览等功能 5.打印 定义打印之范围、页数、指定工作表、份数肆、绘

17、图篇一、针对预算数据练习：资料来源：.tw/hlsong 档案区 excel budget.xls，按鼠标右键，另存目标 d:/budget.xls二、完成表达式：1.于b4打入=b3-b2，以黑十字向右填满，至e42.于b6打入=b5-b4，以黑十字向右填满，至e63.于f2打入=sum(b2:e2)，以黑十字向下填满，至f6ps：为避免比例差别太大，绘画数据范围以a1:e6为主三、绘制柱形图：1.选取a1:e62.插入à图表（或点工具列之图表）à柱形图à下一步，确认数据范围、数列数据取自是否正确à下一步，一一打入标

18、题之图表标题、类别x轴、数值y轴，数据卷标之显示数值à下一步，点新工作表，打入柱形图à完成3.美化图表 a.针对某一数列图（如第一季），按鼠标右键点数据数列格式à图样、区域颜色可改变，或点填满效果选取喜欢之类型à确定à确定 b.再练习，按鼠标右键点数据数列格式à数列顺序、选项、y误差线之意义四、改变柱形图为立体图：1.复制（ctrl+拖曳）柱形图工作卷标，命名（按鼠标右键）为立体图2.对着中间的绘图区，按鼠标右键，点图表类型，柱形图右侧之第7个à确定3.鼠标可在立体图各边角出现黑十字时，旋转其角度。亦可以 图表à立

19、体检视，改变上下仰角、旋转、远近景深、高度à套用五、绘制立体圆型图： 可以某月份各科目金额为例，亦可以某科目各月份金额为例绘制立体圆型，现以各会计科目年度金额为例。1.选取a1:a6，按ctrl不放，再选取f1:f62.插入à图表（或点工具列之图表）à圆型图à立体圆型图（第一列第二栏）à下一步à下一步，标题，打入年度合计、数据卷标，点选显示卷标及百分比à下一步，点新工作表，打入立体圆型图à完成3.练习将鼠标停在圆心，往外拖曳，有何变化？又往内拖曳，是否恢复原先状态？4.练习将鼠标在某一区块点一下（出现六个编辑点），

20、往外拖曳，有何变化？又往内拖曳，是否恢复原先状态？5.点选绘图工具列之快取图案中任一图片说明，打入此区块特殊之处（如天灾），并美化（填满色彩）以强调之六、绘制曲面图：1.分别在b3:c3,a4:a5打入-3.14,-2.51 ，向右、下填满2.在b4打入=sin(b$32+$a42)/ (b$32+$a42+0.01)，向右、下填满3.选取所有数据范围，插入à图表（或点工具列之图表）à曲面图à下一步à下一步，点新工作表，打入曲面图à完成à确定七、绘制股票图：至.tw/hlsong 档案区 /exce

21、l下载股票数据文件.xls，在股票图data工作标签1.选取a1:f152.插入à图表（或点工具列之图表）à股票图，点第二列第二栏之图à下一步à下一步à打入标题à下一步à点新工作表，打入股票图à完成八、绘制折线图、xy散布图：以股票资料文件.xls，在xy图data工作标签1.选取a1:b152.插入à图表（或点工具列之图表）à折线图，点任一图à下一步à下一步à打入标题. à下一步à点新工作表，打入折线图à完成3.选取图表区，按右键，图

22、表类型àxy散布图伍、资料篇 以score.xls为例，工作卷标命名为原始数据一、数据排序三要素：档案、key(关键值)、升/降序排序先将2至3人成绩复制为一致选取所有资料（含标题,不包含最后之统计值）1.按学号升幂排列：资料à排序、主要键à学号、升幂排列、点有标题列à确定2.按总分降序，总分相同，按学号升幂排列：资料(D)à排序、主要键à总分、降序、次要键®学号、升幂排列、点有标题列à确定3.按班别升幂，班别相同，按学号升幂排列：资料(D)à排序、主要键à班别、升幂、次要键à学号、降

23、序排列、点有标题列à确定4.自定清单之排序：会计科目有一定的顺序排列，若随机输入，最后要照会计科目上之顺序排列将budget.xls会计科目复制后贴上于d30（共5个）选取d30:d34，工具à选项à自订清单à汇入，范围d30:d34à确定于e30随机输入此些会计科目，数据(D)à排序à选项à主要排序方式à选取该会计科目à确定、主要键®会计科目、升幂à确定二、排名：1.只排名次：将数据依成绩降序排序后，可在班别的左边插入一栏，在A2打入标题为名次，在A3打入1，A4打入2,将

24、1、2选取,出现黑十字往下拉,则产生名次2.同分数者名次相同：将数据依成绩降序排序后，可在名次的右边插入一栏，在b2键入标题为排名,先检查total在那一栏（如j3）在b3打入=rank(j3,$j$3:$j$23)，以黑十字向下填满三、筛选： 先复制此工作表，命名为进阶筛选1.自动筛选：将符合所设定准则条件之记录，使其数据内容显示在画面上，而不符合条件之记录则隐藏起来。a.先将数据选取含标题（不包含最后总计值），数据(D)à筛选à自动筛选b.每一栏标题内会有一向下按钮，压此按钮将出现下拉式选单，该选单包含所在字段的数据，以及全部、自订、空格等项目，其中自订，出现自订对话框

25、,以便设定准则条件2.进阶筛选： 以下栏名最好复制，以免造成错误a.定义数据区（含标题列）：如a3:j23b.定义准则区（含标题列）：如l3:u3c.定义输出区（含标题列）：如l10:u30d.于准则区班别下一列打入A，选取数据范围a3:j23，资料à筛选à进阶筛选à点将结果复制到其它地方、核对数据范围、准则范围点一下，选取准则范围（含栏名如l3:l4）、复制到，选取输出范围（含栏名，空间须足够，如l10:u30）à确定，即可看到筛选结果e.试练习各种型态数据及And, or 条件句f.数据库函数： Dcount(数据清单范围,“所欲计算的字段(可用字段

26、名称指定)”,筛选准则范围)g.试练习dmax,dmin,daverage,dsum等函数四、窗体：1.在数据表最后增加一空白列，选取数据范围（如a3:j24）2.资料à窗体，练习新增、删除、准则、找上一笔、找下一笔à关闭五、资料(D)à小计：此为一维变量之统计应用，需依关键值(key)sort复制此工作表，命名为小计1.选取数据（含标题、不包含最后总计值），小计可计算每一种类别的统计资料,包括计数count、总和sum、平均值average、最大值max、最小值min、乘积product、次数frequency等函数2.在分组小计字段à班别;使用函数&

27、#224;项目个数;新增小计位置à学期分数等3.同时统计各班total之最高与平均分数六、数据验证：下拉数据项之应用，减少按键之错误适用于少量固定性之数据资料来源：.tw/hlsong 档案区 excel price.xls，按鼠标右键，另存目标 d:/price.xls于f3，资料à验证à设定à储存格内允许，点清单à来源，点price.xls之a3:a10à确定七、垂直查表： 1.打入代号，要于g1出现对应的品名、单价于g3 打入=vlookup(f3,$a$3:$c$10,2)于g4 打入=vl

28、ookup(f3,$a$3:$c$10,3) 垂直查表指令 查表值 原查表范围 第?项数2.若查询值找不到时,显示err之讯息于f10，任意打入一编号，g10 打入 =if(isna(vlookup(f3,$a$3:$c$10,2,0),"err", vlookup(f3,$a$3:$c$10,2,0)其语法为 if(isna(查询指令),”err”,(查询指令)isna表查不到之函数八、数据透视表：此为二维变量之统计应用1.依照班别与性别做max,min,sum,count之练习a.复制此工作表，命名为数据透视表data，并于姓名右侧增加一性别栏，同时打入数据b.选取数据

29、（含标题、不包含最后总计值，如a3:j23）资料à数据透视表及图报表à点excel清单及数据库、点数据透视表à下一步，核对数据范围à下一步，点版面配置，拖曳班别至列，拖曳性别至栏，拖曳total至数据à确定à完成c.鼠标停在数据透视表内任一储存格，在数据透视表工具列练习各功能2.自行建立群组资料来源：.tw/hlsong档案区->excelà程序统计.xls（略）九、资料剖析：1.在任一工作标签，按右键，插入à新增à工作卷标，重新命名为数据剖析2.方式一：各资料间

30、以逗点区隔 a.在a1依序往下打（全在a1,a2,a3储存格打） e120,王小梅,90.1,100,80 e451,欧阳美女,5,49,82 e045,范姜俊男,100,78,59 b.选取a1:a3，资料à资料剖析à点分隔设定à下一步，预览数据分隔的对不对，若不正确，重新修正a1:a3之内容à下一步，若有日期性数据，格式是否正确à完成3.方式二：各数据以固定长度储存 a.在a5依序往下打 e120王小梅 e451欧阳美女 e045范姜俊男 b.选取a5:a7，资料à资料剖析à点固定长度à下一步，预览数据分隔得对

31、不对，若不正确，重新修正a5:a7之内容à下一步，若有日期性数据，格式是否正确à完成 等于自己逐一储存格打入，可进行计算十、资料(D)à运算列表一1.在任一工作标签，按右键，插入à新增à工作标签，重新命名为运算列表2.在a2:a11，有一串数据110，拟在各栏计算各值之10倍、平方、立方、平方根值 a.储存格a1为暂存区，不打入任一值 b.于b1打入+a1*10，c1打入+a12，d1打入+a13，e1打入+sqrt(a1) c.选取a1:e11，资料à运算列表à在栏变量储存格打入a1à确定十一、资料(D)

32、4;运算列表二-九九表1.先在储存格g2输入1，2.(可用黑十字向下拉,拉至9;横的也一样在h1开始输入),格式(O)à栏à栏宽,将栏宽改为4,使储存格看起来较密集美观。2.设g1为公式之所在，f1为被乘数的共享区；f2为乘数的共享区，在g1输入=f1*f2,数据(D)à运算列表(T)à列变量储存格点f1，栏变量储存格点f2à确定，九九表即完成十二、资料(D)à运算列表三-计算分期付款表题目:贷款100,000元,年利率分别为8.5、9、9.5、10，分5年、10年、15年摊还,各期(月)应摊还多少?1.先以固定期数10年，利率变动为

33、例，做一简单示范：a.在q1、q2输入0.085、0.09用黑十字向下拉至0.11，在r1打=PMT(q1/12,120,-100000)，就是10年不付利息每个月付$833元b.将q1r7反白,资料(D)à运算列表(T)à栏变量储存格点q1à确定，则r2出现$1,240、r3出现$1,267、r4出现.c.若要知道十年来共缴了多少利息?在s2输入=r2*120-100000，储存格s2出现$4,872,再用黑十字向下拉，出现各不同利率下运算的结果2.若利率及期数均变动时:a.在q10将0.085、0.090等利率值贴上，r9打5、s9打10，往右黑字拉至30b.在q9输入=PMT(q8/12,r8*12,-100000)，q8表年利率，r8表期数(即有几年)，将