Excel实际运用全攻略.docx

Excel实际运用全攻略.docx

《Excel实际运用全攻略.docx》由会员分享，可在线阅读，更多相关《Excel实际运用全攻略.docx（126页珍藏版）》请在冰豆网上搜索。

Excel实际运用全攻略

Excel全攻略

“MicrosoftOffice工资表”全攻略1

Excel2000斜线表头轻松做1

ExcelXP函数实用教程2

ExcelXP函数运用实例四则10

ExcelXP技巧10011

ExcelXP数据分析应用指南22

ExcelXP中的复制技巧30

Excel单元格内文本换行三法31

Excel名称框使用技巧31

Excel文字输入技巧31

EXCEL小技巧32

Excel应用技巧七则32

Excel在会计中的应用33

EXCEL中错误提示信息的含义及解决办法34

Excel中妙用双击功能35

Excel中填充柄的几种用法35

Office中Enter键的妙用37

Office中常见问题的解决37

Word与Excel联手制作工资审批表38

对《巧用Excel函数分辨人员身份》一文的补充38

更胜一筹──OfficeXP的新功能39

利用Excel2000管理学生成绩40

巧打印拼接图片42

巧算现金支付的面额搭配42

巧用Excel格式工具43

巧用Excel函数分辨出人员身份43

巧用Excel解方程44

巧用替换命令让单元格变色44

轻松打造你的Excel图表45

请评委亮分46

让Excel与IE携手──在Web上发布你的Excel数据47

让表格自动填色49

让电子表格发出动听的声音50

让数据透视表帮你轻松汇总数据──做一个可查询，能自动计算的汇总表50

数据透视表应用拓展──让表更智能和更有个性51

体验MicrosoftofficeXP52

统计成绩之高效奇技53

系统中的ffastun.ffa文件54

已激活的OfficeXP保存方法54

用Excel2000建立简单、实用的进销存系统55

用Excel2000建立商品月销售明细表56

用Excel2000建立项目概算汇总表57

用ExcelXP做个语音累加器58

用Excel的宏管理仓库58

用Excel函数计算年龄几法59

用Excel合并会计报表60

用Excel进行票面计算61

用EXCEL进行医学统计61

用Excel巧编英语学习趣味练习62

用Excel巧解智力趣题63

用Excel实现进销存自动统计63

用Excel实现招标评分自动化65

用Excel制定产品最优组合决策66

用Excel制作工程计量支付证书66

用Excel自动计息68

用MSOffice提高工作效率的三个途径69

用Office2000搭建邮购管理系统70

员工生日巧排序72

在EXCEL2000中快速输入特定文本72

在Excel中打印工资条73

在Excel中轻松移动或复制工作表74

在Excel中相同数据的统计74

在Excel中巧用IF函数计算工资74

自定义OfficeXP中的定位按钮75

“MicrosoftOffice工资表”全攻略　　 

   如果你单位有得心应手的工资软件，或者你甘心埋头苦干不惜汗水，那就省省吧，用不着往下看了。

   小型企事业单位如果没有专门的工资表软件，每月编制工资表都是一件费时费力的重复劳动。

不过只要有MicrosoftOffice2000/XP

   软件，可就轻松多了。

   用Excel制作工资表

   步骤一：

创建工资表的基本格式。

   步骤二：

建立和复制计算公式。

   在E3单元格内写入公式“=B3+C3+D3”，然后选定该单元格，并按着CTRL键，鼠标单元格右下变成复制符号时，拖至E5单元格，此时应发额计算完毕。

   个人所得税的计算是个比较棘手的问题，MicrosoftExcel却提供了简捷的方法。

   如果贵单位职工的最高月收入低于20800，就只须在F3单元格内键入“=IF

   （（E3-800）>5000,（3-800）*0.2-375,IF（（E3-800）>2000,（E3-800）*0.15-125,IF（（E3-800）>500,（E3-800）*0.1-25,IF（（E3-800）>0,（E3-800）*0.05,0））））”就OK，月收入如果更高，根椐以上提供的公式再增加嵌套。

但是请注意，公式中最多可以包含七级嵌套函数。

最后将写好的公式复制到F4、F5······。

   实发额和合计栏的计算就简单多了，用公式、函数都行。

在B6单元格输入“=B3+B4+B5”，并将这一公司复制到C6、D6、···G6。

在H3单元格输入“=E3-F3-G3”，并将这一公式复制到H4、H5、H6。

   经过以上的工作，工资表的计算关系就确定了。

以后只须修改工资项目，计算工作就可交给PC做了。

   另外，对于工资表上基本不变的项目（比如：

姓名、基本工资等项）可采用锁定的方法（菜单：

格式-单元格-保护）防止无意中修改（除此之外，还可通过工作表的合并汇总统计季工资、年工资）。

   用Word邮件合并工具制作工资条

   步骤一：

建立数据源文件。

以上制作的工资表删去标题行就可作为数据源。

   步骤二：

创建工资条模板文档，打开一个空白文档，设计一个工资条格式。

   选工具菜单中的邮件合并，在主文档/创建选项中选择套用信函，在数据源/获取数据/打开数据源选项中选定已删去标题行的工资表文件作为数据源。

   步骤三：

在工资条模板中插入合并域，合并域的字体、字号等属性可随意设置，但切勿删除“《　》”符号。

   步骤四：

合并数据。

点击合并选项/合并即可得到工资条文档供你打印。

下月工资变动后也只经此合并数据即可得新工资条，全不用手工修改。

   最终得到每个职工的工资条。

Excel2000斜线表头轻松做　　 

   为了符合中国人的制表习惯，Word

   2000中文版中加入了“绘制斜线表头”的功能，使得我们在制表时可以方便地在表格中插入斜线表头。

但遗憾的是在Excel  2000中却没有这项功能，以致在Excel2000表格中插入表头时不得不用直线工具来绘制，这样做一来很麻烦，二来文字的位置也不易调整。

其实我们完全可以利用Word

   2000中的绘制斜线表头功能在Excel2000中轻松插入斜线表头。

具体做法如下：

   1.在Excel2000中制作好一张表格并留出放置斜线表头的位置。

   2.在Word2000中绘制斜线表头

   创建一个新文档，并使用“插入表格”工具插入一个二行二列的表格。

选中表格中左上角的单元格，单击“表格”菜单，在弹出的下拉菜单中单击“绘制斜线表头”命令，弹出“绘制斜线表头”对话框，该对话框共提供了五种斜线表头的样式，根据需要选择一种样式，并设置适当的字号，然后在行标题栏中分别填入各栏的标题，完成后单击“确定”按钮，则“插入斜线表头”对话被关闭，在被选中的单元格中插入了一个斜线表头。

   其实在Word   2000中制作的斜线表头是预先制作好的一个斜线表头框架，用鼠标点击后会在四周出现控点。

用鼠标选中这个斜线表头框架，将它复制到“剪贴板”上。

   3.关闭Word2000回到Excel2000中，将斜线表头框架粘贴到工作表中并移动到要放置斜线表头的位置。

   4.调整斜线表头框架及它要覆盖的单元格的大小，选择所有被斜线表头覆盖的单元格，单击工具栏上的“合并单元格”按钮，将它们合并起来，则一个带有斜线表头的Excel工作表制作完毕。

   提示：

如果调整效果始终不能满意，则可选择斜线表头框架，右击鼠标选择“取消组合”命令，将斜线表头框架打散重新调整各部分的位置，然后再将它们组合起来即可。

ExcelXP函数实用教程　　

   Excel是集表格、计算和数据库为一身的优秀软件，其数据处理的核心是函数和数据库。

本文介绍Excel

   XP函数的新增功能、常用函数和数据库的使用方法，以及ExcelXP的网络集成特性。

   一、ExcelXP函数新增功能

   1．公式错误检查

   公式是Excel处理数据的主要工具，由于它的运算符和参数比较复杂，用户构造公式时极易发生错误。

为了加快错误的排除速度，Excel   XP提供了公式错误检查功能。

当单元格中的公式出现错误时，其左侧会出现智能标记按钮。

单击该按钮可以打开一个智能标记菜单，其上端显示出错误的名称（如“无效名称错误”），能帮助用户迅速发现错误原因。

   2．函数工具提示

   用户直接输入公式的函数名和参数时，不能看到函数格式和参数的提示，这是公式发生错误的一个重要原因。

为此，Excel

   XP增加了函数工具提示功能。

以公式“=SUMIF（A1:

A8）”为例，当你输入“=SUMIF（）”后，公式下方会显示黄色的函数工具提示条，提供了有关函数和参数的语法信息。

你只要单击其中的函数或参数名称，就可以打开帮助获得更多信息。

   3．用自然语言搜索函数

   Excel拥有数百个函数，寻找适用的函数是初级用户面临的难题。

为此，Excel

   XP在“插入函数”对话框中增加了“搜索函数”功能。

假如你要完成数据排序任务，可以单击工具栏中的“插入函数”按钮打开对话框，在其中的“搜索函数”框内输入“排序”，然后单击“转到”按钮，对话框下面的“选择函数”框中显示“RANK”等排序函数。

   4．监视窗口

   如果一个工作表的不同单元格或者多个工作表设置了公式，要想查看其内容和计算结果，必须在不同单元格或工作表中转换。

为此，Excel

   XP增加了一个名为监视窗口的工具。

其使用方法是：

选中含有公式的待监视单元格，再用鼠标右键单击工具栏选择“监视窗口”。

然后单击“监视窗口”中的“添加监视”按钮，被监视的公式及其计算结果就会显示在监视窗口中。

   5．公式审核   Excel

   XP新增了一个“公式审核”工具栏，它提供了几个新的数据审查工具，例如“错误检查”、“追踪引用单元格”和“公式求值”。

“错误检查”与语法检查程序类似，它用特定的规则检查公式中存在的问题，可以查找并发现常见错误，你可以在“选项”对话框的“错误检查”选项卡中启用或关闭这些规则。

“追踪引用单元格”可以用蓝色箭头等标出公式引用的所有单元格，追踪结束后可以使用“移去单元格追踪箭头”按钮将标记去掉。

“公式求值”可以打开一个对话框，用逐步执行方式查看公式计算顺序和结果，能够清楚了解复杂公式的计算过程。

   二、ExcelXP函数快速上手

   1．求和函数SUM

   语法：

SUM（number1，number2，．．．）。

   参数：

number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。

   注意：

参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。

如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。

   应用实例一：

跨表求和

   使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：

选中Excel  XP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。

切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。

如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。

上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。

“确定”后公式所在单元格将显示计算结果。

   应用实例二：

SUM函数中的加减混合运算

   财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。

按照规定，工作表中的这些项目没有输入负号。

这时可以构造“=SUM（B2:

B6，C2:

C9，-D2，-E2）”这样的公式。

其中B2:

B6，C2:

C9引用是收入，而D2、E2为支出。

由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。

即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:

B6，C2:

C9，-D2，-D3，D4）”这样的形式。

   应用实例三：

及格人数统计

   假如B1:

B50区域存放学生性别，C1:

C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。

可以使用公式“=SUM（IF（B1:

B50=″女″，IF（C1:

C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。

公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF（B1:

B50=″女″，IF（C1:

C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。

   2.平均值函数AVERAGE

   语法：

AVERAGE（number1，number2，．．．）。

   参数：

number1、number2．．．是需要计算平均值的1～30个参数。

   注意：

参数可以是数字、包含数字的名称、数组或引用。

数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。

如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。

   应用实例一：

跨表计算平均值

   标签名为“一班”、“二班”和“三班”的工作表存放各班学生的成绩，则它们的总平均分计算公式为“=AVERAGE（一班!

C1:

C36，三班!

C1:

C32，三班!

C1:

C45）”。

式中的引用输入方法与SUM跨表求和时相同。

   应用实例二：

忽略零的平均值计算公式

   假设A1:

A200随机存放包括零在内的48个数值，在AVERAGE参数中去掉零引用很麻烦，这种情况可以使用公式“=AVERAGE（IF（A1:

A200<>0，

   A1:

A200，″″）”。

公式输入结束后按住Ctrl+Shift回车，即可对A1:

A200中的数值（不包括零）计算平均值。

   3．逻辑函数IF

   语法：

IF（logical_test，value_if_true，value_if_false）。

   参数：

logical_test是结果为true（真）或false（假）的数值或表达式；value_if_true是logical_test为true时函数的返回值，如果logical_test为ture并且省略了value_if_true，则返回true。

而且value_if_true可以是一个表达式；value_if_false是logical_test为false时函数的返回值。

如果logical_test为false并且省略value_if_false，则返回false。

value_if_false也可以是一个表达式。

   应用实例一：

个人收入调节税计算

   假设个人收入调节税的稽征办法是：

工资在1000元以下的免征调节税，工资1000元以上至1500元的超过部分按5%的税率征收，1500元以上至2000元的超过部分按8%的税率征收，高于2000元的超过部分按30%的税率征收。

   假如工作表的B列存放职工姓名，C列存放工资，选中D列的空白单元格输入公式“=IF（C2<=1000，″″，IF（（C2-1000）<=1500，（C2-1000）*0.05，IF（C2-E2<=1500，（C2-1500）*0.08，IF（C2>2000，（C2-2000）*0.3））））”，回车后即可计算出C2工资应缴纳的收入调节税。

   公式中的IF语句是逐次计算的，如果第一个逻辑判断C2<=1000成立，则公式所在单元格被填入空格；如果第一个逻辑判断式不成立，则计算第二个IF语句；直至计算结束。

如果税率征收标准发生了变化，只须改变逻辑和计算式中的值，如1000、1500和2000等即可。

   应用实例二：

消除无意义的零

   用SUM函数计算工资总额等问题时，若引用的全部参数均为空白单元格，公式仍然会计算出一个“0”。

这样打印出来的报表不仅很不美观。

为此可将计算公式设计为“=IF（SUM（A1:

B1，D2:

E8）<>0，SUM（A1:

B1，D2:

E8），″″）”，即是当SUM（A1:

B1，D2:

E8）计算结果不等于零时，公式所在单元格显示SUM（A1:

B1，D2:

E8）的计算结果，否则显示一个空格。

   上面这个问题在财务计算中依然存在，如公式“=A1-A6-A9”有可能为零或显示负数。

为了避免打印零或负数，可以设计公式“=IF（A2-A6-A9=0，″″，IF（A2-A6-A9<0，RMB（A2-A6-A9），A2-A6-A9））”。

当A2-A6-A9=0时在单元格中填写零，否则进行下一个逻辑判断。

如果A2-A6-A9<0则在结果前添加一个“￥”符号，否则进行正常运算直至结束。

   应用实例三：

多条件求和

   假设C1:

C460区域内存放着职工的职称，D1:

D460区域存放着职工的性别。

如果要统计具有高级职称的男性职工总数，可以使用公式“=SUM（IF（C1:

C460=″高级″，IF（D1:

D460=″男″，1，0）））”。

这是一个数组公式，输入结束后按住Ctrl+Shift回车即可计算出结果。

   4．快捷方式或链接创建函数HYPERLINK

   语法：

HYPERLINK（link_location，friendly_name）。

   参数：

link_location是文件的路径和文件名，它可以指向文档中的某个具体位置。

如Excel工作表中的特定单元格或区域，或某个Word文档中的书签，也可以指向硬盘中的文件或是Internet或Intranet的URL。

Friendly_name为单元格中显示的链接文字或数字，它用蓝色显示并带有下划线。

如果省略了friendly_name，单元格就将link_location显示为链接。

   应用实例：

管理文档或网站地址

   如果你拥有大量文档或收集了许多网站地址，管理起来一定有困难。

如果能够将其中的文档名、URL等与文档等对象链接起来，不仅管理方便，还可以直接打开文档或访问站点。

具体操作方法是：

首先根据文档类型建立管理工作表，其中的文件名或网站名必须使用以下方法输入：

选中一个空白单元格，单击Excel

   XP工具栏中的“粘贴函数”按钮，打开“粘贴函数”对话框，在“函数分类”框下选中“查找与引用”，然后在“函数名”框内找到HYPERLINK函数。

单击对话框中的“确定”按钮，弹出“HYPERLINK”函数向导。

在“link_location”框中输入文件的完整路径和名称（包括扩展名），如“C:

＼my

   documents＼IT网站集锦.doc”，然后在“friendly_name”框中输入文件名（如“IT网站集锦”）。

确认输入无误单击“确定”按钮，所选单元格即出现带下划线的紫色文件名“IT网站集锦”。

   此后你就可以从工作表中打开文档，方法是：

打开工作表，在其中找到需要打开的文件。

箭头光标指向文件名会变成手形，若停留片刻则会显示该文件的完整路径和名称，单击则会调用关联程序将文件打开。

   如果你收集了许多网站的URL，还可以用此法建立一个大型“收藏夹”。

既可以用Excel的强大功能进行管理，又可以从工作表中直接访问Web站点。

   5．计数函数COUNT

   语法：

COUNT（value1，value2，．．．）。

   参数：

value1，value2．．．是包含或引用各类数据的1～30个参数。

   注意：

COUNT函数计数时数字、日期或文本表示的数字会参与计数，错误值或其他无法转换成数字的文字被忽略。

如果参数是一个数组或引用，那么只有数组或引用中的数字参与计数；其中的空白单元格、逻辑值、文字或错误值均被忽略。

   应用实例：

及格率统计

   假如C1:

G42存放着42名学生的考试成绩，在一个空白单元格内输入公式“=COUNTIF（C1:

C42，″>=60″）/COUNTA（C1:

C42）”回车，即可计算出该列成绩的及格率（即分数为60及以上的人数占总人数的百分比）。

   6．最大值函数MAX、最小值函数MIN

   语法：

MAX（number1，number2，．．．），MIN（number1，number2，．．．）。

   参数：

number1，number2．．．是需要找出最大值（最小值）的1至30个数值、数组或引用。

   注意：

函数中的参数可以是数字、空白单元格、逻辑值或数字的文本形式，如果参数是不能转换为数字的内容将导致错误。

如果参数为数组或引用，则只有数组或引用中的数字参与计算，空白单元格、逻辑值或文本则被忽略。

   应用实例：

查询最高分（最低分）

   假如C1：

G42存放着42名学生的考试成绩，则选中一个空白单元格，在编辑栏输入公式“=MAX（C1:

C42）”，回车后即可计算出其中的最高分是多少。

   如果将上述公式中的函数名改为MIN，其他不变，就可以计算出C1：

G42区域中的最低分。

   7．条件求和函数SUMIF

   语法：

SUMIF（range，criteria，sum_range）。

   参数：

range是用于条件判断的单元格区域，criteria是由数字、逻辑表达式等组成的判定条件，sum_range为需要求和的单元格、区域或引用。

   应用实例：

及格平均分统计

   假如A1:

A36单元格存放某班学生的考试成绩，若要计算及格学生的平均分，可以使用公式“=SUMIF（A1:

A36，″>=60″，A1:

A36）/COUNTIF（A1:

A36，″>=60″）。

公式中的“=SUMIF（A1:

A36，″>=60″，A1:

A36）”计算及格学生的总分，式中的“A1:

A36”为提供逻辑判断依据的单元格引用，“>=60”为判断条件，不符合条件的数据不参与求和，A1:

A36则是逻辑判断和求和的对象。

公式中的COUNTIF（A1:

A36，″>=60″）用来统计及格学生的人数。

   8．贷款偿还计算函数PMT

   语法：

PMT（rate，nper，pv，fv，type）.

   参数：

如今贷款买房子或车子的人越来越多，计算某一贷款的月偿还金额是考虑贷款的重要依据，Excel

   XP提供的PMT函数是完成这一任务的好工具。

语法中的rate是贷款利率；nper为贷款偿还期限；pv是贷款本金；fv为最后一次付款后剩余的贷款金额，如果省略fv，则认为它的值为零；type为0或1，用来指定付款时间是在月初还是月末。

如果省略type，则假设其值为零。

   应用实例：

购房还款金额

   假如你为购房贷款十万元，如果年利率为7%，每月末还款。

采用十年还清方式时，月还款额计算公式为“=PMT（7%/12，120，-100000）”。

其结果为￥-1，161.08，就是你每月须偿还贷款1161.08元。

   9．样本的标准偏差函数STDEV

   语法：

STDEV（n