1、会计必备知识之 Excel 操作宋惠亮 整理前言 2第一章 基础篇 2第二章各种输入型态说明3第三章实例篇7第四章绘图篇8第五章资料篇10第六章作业162009 前言本软件有三大重点：一、资料表：建立、编辑、修改（增加、删除） 、搬移、复制、贴上、打印二、图表：一图胜过千言万语 ，此为最好的诠释。三、数据库：进入方式 开始程序集 MicroSoft Office Excel 点选桌面上快捷方式画面介绍 学习愉快- 2 -一一说明如下：窗口标题列：主菜单：工具列：目前储存格地址：编辑列：储存格(cell) ：工作标签：工作标签卷动钮（最前/前一个/后一个/最后）工作标签：状态列：滚动条（水平、垂
2、直）壹、基础篇有关画面功能逐步操作、说明如下：一、在开启 Microsoft Excel 之后,画面出现一空白的活页簿窗口,该活页簿被命名为Book1(相当于书名;而底下的sheet1 则像是各章之名称)二、 ，在储存格输入任意字,按或 esc 键表示放弃储存格之内容,而或 enter表确认,f x为函数储存格中的文字靠左、数字靠右排列（自订值）三、画面控制键说明如下：1.ctrl+将游标移至当行之A 栏(如游标停在 X30 移至 A30)2.ctrl+ 将游标移至当行之IV 栏(如游标停在 x30 移至 IV30)3.ctrl+home 将游标快速移至 A14.ctrl+将游标快速移至 IV
3、655364.5.ctrl+end 将游标快速移至右下角（行列交集）非空白区6.page down/page up 将游标往后/前移一页7.f5 将光标移至，键入地址（如 x30）8.f2 进入编辑模式四、改变工作环境1.改变储存格的网格线：工具(T)选项检视网格线练习取消网格线与改变网格线之颜色之别- 3 -再练习将显示之数据编辑列等选项，勾勾取消确定2.改变储存格的工作表个数、字型、大小、预设档案位置：工具(T)选项检视一般确定练习改变上项内容分别为 5、标楷体、12、a:五、工作标签之应用1.欲将 sheet1 重新命名：将光标移至 sheet1,按鼠标右键出现对话框,点重新命名,输入新
4、的档名，如exercise2.欲将 sheet1 复制,将光标移至 sheet1,左手按住 ctrl，右手拖曳鼠标至其它卷标位置上，则变成 sheet(1)，可再重新命名3.欲将 sheet1 及 sheet2.3.4删除(1)连续:将游标移至 sheet1,按住 shift1,点 sheet4，此时 sheet1 及sheet2、3、4出现反白,按鼠标右键出现对话框,点删除,按确定则 sheet1 及sheet2、3、4消失(2)不连续:将游标移至 sheet1,按住 ctrl 分别点 sheet2, sheet5此时 sheet1及 sheet2、5出现反白,按鼠标右键出现对话框,点删除,
5、按确定则 sheet1 及sheet2、5 消失贰、各种输入型态说明如下：一、文字：1. 功能与 word 相同。2. 为跨栏置中,储存格内的字较多，欲跨越多个储存格时选用，如选 A1 至 A3,排列方式即改变。说明如下：于 a1 储存格打入 ntpu。为要比较文字的对齐方式，须复制相同内容，方式：1.利用右下角之填满控制点（黑十字），拖曳至 a5先选取 a1a5，编辑填满填满向下2.依序练习 之功能3.于 a6 储存格打入国立台北大学，选取 a6b6，点4.于 a7 储存格打入国立台北大学，格式储存格对齐练习水平（分散对齐）、垂直、文字控制、方向之各项功能5.于 a10 打入国立台北大学，按
6、 alt+enter 再打入信息中心，则二列内容打在同一储存格。二、数值：上图最右边代表小数字数递减; 右 2 代表小数字数递增- 4 -1.于 c1 储存格依序打入 85.2、-152、3500、6、0.9，选取 c1:c5试点选工具列之小数字数对齐及三位一撇，是否美化？再试格式储存格 数字 数值（货币、会计专用） 确定2.于 c6 键入+c1+c2+c3+c4+c53.于 c7 利用函数 sum 计算其总和有两种方式：一为点工具列，范围为 c1:c5二为插入函数 在右侧点一下，打入要找的函数第一个字母（如 sum 函数的第一个字母为 s），会快速的出现相同字母的函数，找起来会快些。4.于
7、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)11.于 a17 求算此 15 个储存格数值数据的
8、笔数： =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()则自动出现系统之日期及时间（即现在之日期时间）2.选取 d1:d7，格式储存格日期以符合中国人习惯3.于 e
9、1，计算各人年龄：=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()四、自订清单：- 5 -1.于 i1 储存格打入周六, 再以黑十字向下填满，或向右填满了解其功能后，再删除右侧储存格内容2.系统提
10、供之清单不足时，有二方式解决：一为工具选项 自订清单新增，依序打入你想加入的数据（如 小明,小玉,小花）确定于 j1，打入刚刚你加入的数据中任一人名，再以黑十字向下填满二为于 k1 先将你想加入的数据依序打入（如喜爱的五种动物），工具选项自订清单汇入，范围 k1:k5确定于 L1，打入刚刚你加入的任一动物，再以黑十字向下填满五、等差级数：1.于 m1、m2，输入 30,35 后,将其反白使光标在储存格下方出现黑十字,往下或往右拉则储存格出现 30,35,40,45 (小数 点、负数亦可 )2.显示周期性日期数据（如每周六）：a.于 n1 打入某一周六之日期（如 2003/8/15）b.选取 n
11、1: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 赵一 女 25 孙山 男（每列打一资料，共 9 列）2.选取 s1:u3,打入=transpose(r1:r9) 按 ctrl+shift+enter 即可七、if 条件定义
12、：于 r1 打入 1，r2 则出现 one，若打入 2，则出现 two，其余一律出现 reenter于 r2 打入 if(r1=1,”one”,if(r1=2,”two”,”reenter”)八、移动及复制储存格的作法移动：先将储存格反白,鼠标指着记号区，待出现白色箭头，按鼠标左键，拖曳至所要的储存格上复制：前述方法，按 ctrl，拖曳至新的储存格上- 6 -参、实例篇：一、针对学生成绩资料练习资料来源：http:/www.ntpu.edu.tw/hlsong 档案区 excel score.xls，按鼠标右键，另存目标 d:/score.xls二、计算成绩的作法：1.于 g1 打入 tota
13、l，于 g2，打入=d2*.3+e2*.3+f2*.4，再以黑十字向下填满2.于 g22，打入=sum(g2:g21) -此为分母 3.于 h1，打入%，于 h2，打入=g2/$g$22可先打 g22 按两次 f4，次点工具列之%，或格式储存格 数值 %，指定小数位数，满意后，再以黑十字向下填满ps：绝对地址及相对地址，说明如下：当打入=G3/G23,鼠标向下拉,分子及分母之地址会跟着改变,称为相对地址之移动,然分母应固定不动,须改为绝对地址,因此在分母 G23 点一下,按 F4 则变成为绝对地址(固定不动);(若多次按 F4,则从 G22$G$22G$22$G22G22 变化)三、插入标题列
14、的作法：1.将游标停在 A1 列任一格,插入(I)列,即增加一列。2.于 c1 打入国立台北大学 92 学年度（按 alter + enter）再打入数据处理成绩，可选数储存格，再点工具列之跨栏置中四、美化储存格：按键盘的 F5,输入范围(如 a1:g22);格式(O)储存格，进行数字、对齐、字型、外框、图样等编排之练习五、外框的选取的方式先选取范围(如 a1:a22)，格式(O)储存格外框,先选样式再点框线(如外框或上下左右),再选样式六、次数分配(要统计各分数间之人数)先有一原始资料(如 G3:G23),则在 A30 输入数据分配间距(ex:10、20、30.),先选取B3:b39 输入=
15、frequency($G$2:$G$22,A30:A39)按 ctrl+shift+enter 即出现所有结果七、防止工作表中之公式或重要内容被篡改1.选定整个工作表(栏列交叉处)格式储存格保护 取消锁定之勾勾 确定选定要保护之储存格（如 g2:g22），储存格保护勾锁定确定工具保护保护工作表 确定2.解除保护工具保护解除保护工作表 确定八、打印1.定义纸张方向及边界：档案版面设定 页面，定义纸张方向、比例 确定- 7 -档案版面设定 边界，定义上下左右边界、置中方式确定2.定义页首/页尾：档案版面设定 页首/页尾，定义字型、页码、总页数、打印日期、文件名称确定3.定义换页标题重复：先检查标题
16、位置，如 a1:h1档案版面设定 工作表，于标题列，打入 a1:h1确定4.预览打印上页、下页、缩放、设定、边界、分页预览等功能5.打印定义打印之范围、页数、指定工作表、份数肆、绘图篇一、针对预算数据练习：资料来源：http:/www.ntpu.edu.tw/hlsong 档案区 excel budget.xls，按鼠标右键，另存目标 d:/budget.xls二、完成表达式：1.于 b4 打入=b3-b2，以黑十字向右填满，至 e42.于 b6 打入=b5-b4，以黑十字向右填满，至 e63.于 f2 打入=sum(b2:e2)，以黑十字向下填满，至 f6ps：为避免比例差别太大，绘画数据范
17、围以 a1:e6 为主三、绘制柱形图：1.选取 a1:e62.插入图表（或点工具列之图表）柱形图 下一步，确认数据范围、数列数据取自是否正确下一步，一一打入标题之图表标题、类别 x 轴、数值 y 轴，数据卷标之显示数值下一步，点新工作表，打入柱形图完成3.美化图表a.针对某一数列图（如第一季），按鼠标右键点数据数列格式图样、区域颜色可改变，或点填满效果选取喜欢之类型确定确定b.再练习，按鼠标右键点数据数列格式数列顺序、选项、y 误差线之意义四、改变柱形图为立体图：1.复制（ ctrl+拖曳）柱形图工作卷标，命名（按鼠标右键）为立体图2.对着中间的绘图区，按鼠标右键，点图表类型，柱形图右侧之第
18、7 个确定3.鼠标可在立体图各边角出现黑十字时，旋转其角度。亦可以 图表立体检视，改变上下仰角、旋转、远近景深、高度套用五、绘制立体圆型图：可以某月份各科目金额为例，亦可以某科目各月份金额为例绘制立体圆型，现以各会计科目年度金额为例。- 8 -1.选取 a1:a6，按 ctrl 不放，再选取 f1:f62.插入图表（或点工具列之图表）圆型图 立体圆型图（第一列第二栏） 下一步下一步，标题，打入年度合计、数据卷标，点选显示卷标及百分比下一步，点新工作表，打入立体圆型图完成3.练习将鼠标停在圆心，往外拖曳，有何变化？又往内拖曳，是否恢复原先状态？4.练习将鼠标在某一区块点一下（出现六个编辑点），往
19、外拖曳，有何变化？又往内拖曳，是否恢复原先状态？5.点选绘图工具列之快取图案中任一图片说明，打入此区块特殊之处（如天灾），并美化（填满色彩）以强调之六、绘制曲面图：1.分别在 b3:c3,a4:a5 打入-3.14,-2.51 ，向右、下填满2.在 b4 打入=sin(b$32+$a42)/ (b$32+$a42+0.01)，向右、下填满3.选取所有数据范围，插入图表（或点工具列之图表）曲面图下一步下一步，点新工作表，打入曲面图完成确定七、绘制股票图：至 http:/www.ntpu.edu.tw/hlsong 档案区 /excel 下载股票数据文件.xls，在股票图 data 工作标签1.选
20、取 a1:f152.插入图表（或点工具列之图表）股票图，点第二列第二栏之图下一步下一步打入标题 下一步 点新工作表，打入股票图 完成八、绘制折线图、xy 散布图：以股票资料文件.xls，在 xy 图 data 工作标签1.选取 a1:b152.插入图表（或点工具列之图表）折线图，点任一图下一步下一步 打入标题 下一步 点新工作表，打入折线图 完成3.选取图表区，按右键，图表类型xy 散布图伍、资料篇以 score.xls 为例，工作卷标命名为原始数据一、数据排序三要素：档案、key(关键值)、升/降序排序先将 2 至 3 人成绩复制为一致选取所有资料（含标题,不包含最后之统计值）1.按学号升幂
21、排列：资料排序、主要键 学号、升幂排列、点有标题列 确定2.按总分降序，总分相同，按学号升幂排列：- 9 -资料(D)排序、主要键 总分、降序、次要键学号、升幂排列、点有标题列确定3.按班别升幂，班别相同，按学号升幂排列：资料(D)排序、主要键 班别、升幂、次要键 学号、降序排列、点有标题列 确定4.自定清单之排序：会计科目有一定的顺序排列，若随机输入，最后要照会计科目上之顺序排列将 budget.xls 会计科目复制后贴上于 d30（共 5 个）选取 d30:d34，工具选项自订清单 汇入，范围 d30:d34确定于 e30 随机输入此些会计科目，数据(D)排序选项主要排序方式选取该会计科目
22、确定、主要键会计科目、升幂确定二、排名：1.只排名次：将数据依成绩降序排序后，可在班别的左边插入一栏，在 A2 打入标题为名次，在A3 打入 1，A4 打入 2,将 1、2 选取,出现黑十字往下拉,则产生名次2.同分数者名次相同：将数据依成绩降序排序后，可在名次的右边插入一栏，在 b2 键入标题为排名,先检查 total 在那一栏（如 j3）在 b3 打入=rank(j3,$j$3:$j$23)，以黑十字向下填满三、筛选：先复制此工作表，命名为进阶筛选1.自动筛选：将符合所设定准则条件之记录，使其数据内容显示在画面上，而不符合条件之记录则隐藏起来。a.先将数据选取含标题（不包含最后总计值），数
23、据(D)筛选自动筛选b.每一栏标题内会有一向下按钮，压此按钮将出现下拉式选单，该选单包含所在字段的数据，以及全部、自订、空格等项目，其中自订，出现自订对话框,以便设定准则条件2.进阶筛选：以下栏名最好复制，以免造成错误a.定义数据区（含标题列）：如 a3:j23b.定义准则区（含标题列）：如 l3:u3c.定义输出区（含标题列）：如 l10:u30d.于准则区班别下一列打入 A，选取数据范围 a3:j23，资料筛选进阶筛选点将结果复制到其它地方、核对数据范围、准则范围点一下，选取准则范围（含栏名如 l3:l4）、复制到，选取输出范围（含栏名，空间须足够，如l10:u30）确定，即可看到筛选结果
24、e.试练习各种型态数据及 And, or 条件句f.数据库函数：- 10 -Dcount(数据清单范围,“所欲计算的字段(可用字段名称指定)”,筛选准则范围)g.试练习 dmax,dmin,daverage,dsum 等函数四、窗体：1.在数据表最后增加一空白列，选取数据范围（如 a3:j24）2.资料窗体，练习新增、删除、准则、找上一笔、找下一笔关闭五、资料(D)小计：此为一维变量之统计应用，需依关键值(key)sort复制此工作表，命名为小计1.选取数据（含标题、不包含最后总计值），小计可计算每一种类别的统计资料,包括计数 count、总和 sum、平均值 average、最大值 max、
25、最小值 min、乘积product、次数 frequency 等函数2.在分组小计字段班别;使用函数项目个数;新增小计位置学期分数等3.同时统计各班 total 之最高与平均分数六、数据验证：下拉数据项之应用，减少按键之错误适用于少量固定性之数据资料来源：http:/www.ntpu.edu.tw/hlsong 档案区 excel price.xls，按鼠标右键，另存目标 d:/price.xls于 f3，资料验证 设定 储存格内允许，点清单 来源，点 price.xls 之a3:a10确定七、垂直查表：1.打入代号，要于 g1 出现对应的品名、单价于 g3 打入=vlookup(f3,$a$
26、3:$c$10,2)于 g4 打入=vlookup(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，并于姓名右侧增加一性
27、别栏，同时打入数据- 11 -b.选取数据（含标题、不包含最后总计值，如 a3:j23）资料数据透视表及图报表点 excel 清单及数据库、点数据透视表下一步，核对数据范围下一步，点版面配置，拖曳班别至列，拖曳性别至栏，拖曳total 至数据确定 完成c.鼠标停在数据透视表内任一储存格，在数据透视表工具列练习各功能2.自行建立群组资料来源：http:/www.ntpu.edu.tw/hlsong 档案区excel程序统计.xls（略）九、资料剖析：1.在任一工作标签，按右键，插入新增工作卷标，重新命名为数据剖析2.方式一：各资料间以逗点区隔a.在 a1 依序往下打（全在 a1,a2,a3 储存
28、格打）e120,王小梅,90.1,100,80e451,欧阳美女,5,49,82e045,范姜俊男,100,78,59b.选取 a1:a3，资料资料剖析点分隔设定 下一步，预览数据分隔的对不对，若不正确，重新修正 a1:a3 之内容下一步，若有日期性数据，格式是否正确完成3.方式二：各数据以固定长度储存a.在 a5 依序往下打e120 王小梅 090.1100.0080.0e451 欧阳美女 005.0049.0082.0e045 范姜俊男 100.0078.0059.0b.选取 a5:a7，资料资料剖析点固定长度 下一步，预览数据分隔得对不对，若不正确，重新修正 a5:a7 之内容下一步，若
29、有日期性数据，格式是否正确完成等于自己逐一储存格打入，可进行计算十、资料(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)运算列表二-九九表1.先在储存格 g2 输入 1，2(可用黑十字向下拉,拉至 9;横的也一样在 h1 开始输入),格式(O)栏 栏宽,将
30、栏宽改为 4,使储存格看起来较密集美观。- 12 -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 年，利率变动为例，做一简单示范：a.在 q1、q2 输入 0.085、0.09 用黑十字向下拉至 0.11，在 r1 打=PMT(q1/12,120,-100000)，
31、就是 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 表期数(即有几年)，将 q9w15 反白，资料
32、(D)运算列表(T)列变量储存格点 r8，栏变量储存格点 q8确定，分期付款表即完成 十三、多档连结：欲整合数个不同的档案内容，达同步更新之效果1.先建立 10 名学生数据处理之成绩（含表达式）2.次建立 10 名学生会计学、统计学之成绩3.再建立此 10 名学生各成绩之汇总表（只须学号）4.在汇总表之第一位学生数据处理下之储存格（如 b2）打入=鼠标点数据处理工作卷标第一位学生之总成绩（如=数据处理!e2），再用黑十字向下拉，出现该科各人总成绩5.依此方式汇总会计学、统计学之总成绩6.更改某名学生之数据处理平时成绩，汇总表是否同步更改十四、以健保、公保为例，练习 index、match 指令
33、： index(array,row_num,column_num)根据所设定之比对方式找出欲搜寻之值在搜寻范围的位置match(lookup_value,lookup_array,match_type)match_type: 1 或省略 找出小于或等于搜寻值(先递增排序)的最大值-1 找出大于或等于搜寻值(先递减排序)的最小值0 找出与搜寻值(不须排序)相等的值- 13 -十五、问卷调查： 工具加载宏，勾选分析工具箱确定，出现工具 数据分析之功能1.在任一工作标签，按右键，插入新增工作标签，重新命名为问卷调查2.在 d1 打入旅游地点代号（一定以数字表示）及旅游地点3.在 a,b 二栏打入约
34、20 份问题内容，如年龄、旅游地点代号4.工具资料分析 直方图确定，打入输入范围（如 a1:c21）、组界范围（如d1:d5），点输出范围（如 f1:j15），勾选柏拉图、累积百分率确定十六、目标搜寻：以 score.xls 为例，求算学号 143 此人，final 拟 pass 时，期末考应考几分？1.鼠标停在此人之 final（如 g3）2. 工具目标搜寻，目标储存格打入 g3，目标值打入 60，变量储存格打入 f3确定，立即出现 64.6十七、宏：新建立：工具宏 录制新宏使用：1. 工具宏宏，选取宏名称2. 工具列窗体 按纽，连结宏名称试做二宏以 score.xls 为例，制作成绩降序排列，改变字型再以学号升幂排列，改变字型陆、作业 自订一至二主题 以一主画面连结各卷标(label)，其功能须有 表达式 排序 进阶筛选，至少两例 数据库统计函数 垂直查表，数据验证 运算列表 小计，数据透视表 其它 图表不能独自一个标签
