使用Excel 的一些小巧门Word格式文档下载.docx

使用Excel 的一些小巧门Word格式文档下载.docx

《使用Excel 的一些小巧门Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《使用Excel 的一些小巧门Word格式文档下载.docx（18页珍藏版）》请在冰点文库上搜索。

功能可轻松地实现这一点，具体方法是先选定一块区域，然后选择"

格式"

，在打开的窗口中输入条件，然后单击"

按钮，对满足条件的数据的显示格式（字体、边框和图案）进行设置，若要增加条件，还可按"

添加"

按钮添加下一个条件。

这样在选定的区域内满足条件的数据都将以您设定的格式进行显示。

5.利用"

条件求和向导"

对数据按条件进行汇总

----在一个表格中经常需要将表中的数据按一定的条件进行求和计算。

如一个人事职称档案表，需要统计"

助理工程师"

、"

工程师"

会计师"

等职称的人数各为多少。

对类似问题，利用"

便可很好地解决。

工具"

加载宏"

，在打开的窗口中选中"

，然后按"

按钮，调用加载宏;

选择"

向导"

条件求和"

，进入求和向导;

在求和向导中首先单击"

输入"

框右边的小按钮，然后在表格中直接选定包含数据的区域，再单击"

下一步"

，在"

求和列"

中选定求和数据所在列，在"

条件"

框中添加求和的条件，根据向导的步骤指定存放汇总结果的单元格，即将光标置于输入框中，直接在表格中选中相应单元格即可，最后单击"

完成"

按钮。

在汇总结果单元格中将出现预期的汇总结果，且一旦设定，汇总结果单元格会随着数据变化自动汇总，一劳永逸。

要取消汇总结果只需点击汇总结果单元格，将公式去掉即可。

当然，熟悉函数公的用户，可直接定义单元格的公式，效果是一样的。

6.Excel中数组公式

Excel中数组公式非常有用，尤其在不能使用工作表函数直接得到结果时，数组公式显得特别重要，它可建立产生多值或对一组值而不是单个值进行操作的公式。

输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按Ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上花括号“{}”。

注意：

不要自己键入花括号，否则，Excel认为输入的是一个正文标签。

选取数组公式所占有的全部区域先选中区域中任意一个单元格，然后按下Ctrl＋/键即可。

编辑或删除数组公式编辑数组公式时，须选取数组区域并且激活编辑栏，公式两边的花括号将消失，然后编辑公式，最后按Ctrl＋Shift＋Enter键。

选取数组公式所占有的全部区域后，按Delete键即可删除数组公式。

数组常量的使用数组公式中还可使用数组常量，但必须自己键入花括号“{}”将数组常量括起来，并且用“，”和“；

”分离元素。

其中“，”分离不同列的值，“；

”分离不同行的值。

使用不在工作表中的数组有时，公式仅占用一个单元格时也需要以数组的方式输入。

其具体原则是：

一个公式使用了数组，并且这个数组不在工作表上，就应该使用数组的方式输入这个公式。

下面介绍两个使用数组公式的例子。

1有如图所示的工作表，需分别计算各商品的销售额，可利用数组公式来实现。

单元格F2中的公式为：

{=SUM（IF（A2:

A11=″商品1″，B2:

B11＊C2:

C11,0））}。

这个数组公式创建了一个条件求和，若在A2:

A11中出现值“商品1”，则数组公式将B2:

B11和C2:

C11中与其相对应的值相乘并累加，若是其他值则加零。

同时，虽然数组B2:

C11均在工作表中，但其相乘的数组B2:

C11不在工作表中，因此必须使用数组公式。

2假设要将A1:

A50区域中的所有数值舍入到2位小数位，然后对舍入的数值求和。

很自然地就会想到使用公式：

=ROUND（A1,2）＋ROUND（A2,2）＋…＋ROUND（A50,2）。

有没有更简捷的算法呢？

有。

因为数组ROUND（A1:

A50,2）并不在工作表中，因此要使用数组的方式输入公式，即：

{=SUM（ROUND（A1:

A50,2））}。

7、Excel文件的加密与隐藏

　　如果你不愿意自己的Excel文件被别人查看，那么你可以给它设置密码保护，采用在保存文件时用加密的方法就可以实现保护目的，在这里要特别注意的是，自己设定的密码一定要记住，否则自己也将被视为非法入侵者而遭拒绝进入。

给文件加密的具体方法为：

A、单击“文件”菜单栏中的“保存或者（另存为）”命令后，在弹出的“保存或者（另存为）”的对话框中输入文件名；

B、再单击这个对话框中“工具”栏下的“常规选项”按钮，在弹出的“保存选项”的对话框中输入自己的密码；

这里要注意，它提供了两层保护，如果你也设置了修改权限密码的话，那么即使文件被打开也还需要输入修改权限的密码才能修改。

C、单击“确定”按钮，在弹出的密码确认窗口中重新输入一遍密码，再点击“确认”，最后点“保存”完成文件的加密工作。

当重新打开文件时就要求输入密码，如果密码不正确，文件将不能打开。

8、对单元格进行读写保护

　　单元格是Excel执行其强大的计算功能最基本的元素，对单元格的读写保护是Excel对数据进行安全管理的基础。

对单元格的保护分为写保护和读保护两类，所谓写保护就是对单元格中输入信息加以限制，读保护是对单元格中已经存有信息的浏览和查看加以限制。

　　对单元格的写保护有两种方法：

　　A、对单元格的输入信息进行有效性检测。

首先选定要进行有效性检测的单元格或单元格集合，然后从数据菜单中选择“有效数据”选项，通过设定有效条件、显示信息和错误警告，控制输入单元格的信息要符合给定的条件。

　　B、设定单元格的锁定属性，以保护存入单元格的内容不能被改写。

可分为以下步骤：

（1）选定需要锁定的单元格或单元格集合；

（2）从格式菜单中选择“单元格”选项；

　　（3）在设置单元格格式的弹出菜单中选择“保护”标签，在弹出的窗口中，选中“锁定”；

　　（4）从工具菜单中选择“保护”选项，设置保护密码，即完成了对单元格的锁定设置。

　　对单元格的读保护有三种方法：

　　A、通过对单元格颜色的设置进行读保护。

例如：

将选定单元格或单元格集合的背景颜色与字体颜色同时设为白色，这样，从表面看起来单元格中好像是没有输入任何内容，用户无法直接读出单元格中所存储的信息。

　　B、用其他画面覆盖在需要保护的单元格之上，遮住单元格的本来面目，以达到读保护目的。

使用绘图工具，画一不透明矩形覆盖在单元格之上，从格式菜单中选定矩形的“锁定”选项，然后保护工作表，以保证矩形不能被随意移动。

这样，用户所看到的只是矩形，而看不到单元格中所存储的内容。

　　C、通过设置单元格的行高和列宽，隐藏选定的单元格，然后保护工作表，使用户不能直接访问被隐藏的单元格，从而起到读保护的作用。

9、快速填充相同数据

　　如果你希望在不同的单元格中输入大量相同的数据信息，那么你不必逐个单元格一个一个地输入，那样需要花费好长时间，而且还比较容易出错。

我们可以通过下面的操作方法在多个相邻或不相邻的单元格中快速填充同一个数据，具体方法为：

　　A、同时选中需要填充数据的单元格。

若某些单元格不相邻，可在按住Ctrl键的同时，点击鼠标左键，逐个选中；

　　B、输入要填充的某个数据。

按住Ctrl键的同时，按回车键，则刚才选中的所有单元格同时填入该数据。

10、连续两次选定单元格

　　我们有时需要在某个单元格内连续输入多个数值，以查看引用此单元格的其他单元格的效果。

但每次输入一个值后按回车键，活动单元格均默认下移一个单元格，非常不便。

解决此问题的一般做法是通过选择“工具”\“选项”\“编辑”，取消“按回车键后移动”选项的选定来实现在同一单元格内输入许多数值，但以后你还得将此选项选定，显得比较麻烦。

其实，采用连续两次选定单元格方法就显得灵活、方便：

　　单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格。

此时，单元格周围将出现实线框。

11、在工作表之间使用超级连接

　　首先须要在被引用的其他工作表中相应的部分插入书签，然后在引用工作表中插入超级链接，注意在插入超级链接时，可以先在“插入超级链接”对话框的“链接到文件或URL”设置栏中输入目标工作表的路径和名称，再在“文件中有名称的位置”设置栏中输入相应的书签名，也可以通过“浏览”方式选择。

完成上述操作之后，一旦使用鼠标左键单击工作表中带有下划线的文本的任意位置，即可实现中文Excel2000在自动打开目标工作表并转到相应的位置处，这一点与WORD的使用很相似。

　　12、快速清除单元格的内容

　　首先用鼠标选定该单元格,然后按住键盘上的Delete键，此时你会发现你只是删除了单元格内容,它的格式和批注还保留着。

那么如何才能彻底清除单元格呢,请看下面的两种操作步骤：

　　A、选定想要清除的单元格或单元格范围；

　　B、选择“编辑”菜单中的“清除”命令,这时显示“清除”菜单；

　　C、选择要清除的命令,可以选择“全部”、“格式”、“内容”或“批注”中的任一个。

　　13、快速修改单元格次序

　　在实际操作的过程中，我们有时需要快速修改单元格内容的次序。

在拖放选定的一个或多个单元格至新的位置的同时，按住Shift键可以快速修改单元格内容的次序。

具体方法如下：

　　A、首先用鼠标选定单元格，同时按下键盘上的Shift键；

　　B、接着移动鼠标指针到单元格边缘，直至出现拖放指针箭头，然后进行拖放操作。

上下拖拉时鼠标在单元格间边界处会变成一个水平“工”状标志，左右拖拉时会变成垂直“工”状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。

14、在Excel中利用公式来设置加权平均

　　加权平均在财务核算和统计工作中经常用到，并不是一项很复杂的计算，关键是要理解加权平均值其实就是总量值（如金额）除以总数量得出的单位平均值，而不是简单的将各个单位值（如单价）平均后得到的那个单位值。

在Excel中可设置公式解决（其实就是一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，它的结果就是这些量值的加权平均值。

　　15、用Excel绘制函数图像

　　函数图像能直观地反映函数的性质，用手工方法来绘制函数图像效果不太好，而用Excel绘制函数图像非常简便，所作图像非常标准、漂亮，具体方法如下：

　　A、首先打开电子表格的操作窗口，然后用鼠标选择菜单栏中的“新建”命令，这时屏幕上会出现一个空白的电子表格；

　　B、然后在A列的A1格输入“X=”，表明这是自变量，再在A列的A2及以后的格内逐次从小到大输入自变量的各个值；

实际输入的时候，通常应用等差数列输入法，先输入前三个值，定出自变量中数与数的距离，然后点击A2格，按住鼠标拖到A4格选中这三项，使这三项变成一个黑色矩形，再用鼠标指向这黑色矩形的右下角的小方块，当光标变成“＋”后，按住鼠标拖动光标到适当的位置，就完成自变量的输入；

　　C、接着在B列的B1格输入函数式y=f（x）的一般函数表达式，如y=1/x；

也可在点击工具栏上的函数图标“fx”后，在出现的不同函数列表的选项中选择需要的一种；

输入结束后，点击函数输入对话框旁的勾号，B2格内马上得出了计算的结果。

这时，再选中B2格，让光标指向B2矩形右下角的方框，当光标变成“＋”时按住光标沿B列拖动到适当的位置即完成函数值的计算。

注意一定要把该函数中自变量x的位置输入前面A列自变量的绝对位置A2格，这样下面计算的时候才会对不同的自变量进行计算；

　　D、最后点击工具栏上的“图表向导”图标，在出现的各种图表格式图标中选择“X，Y散点图”，然后在出现的“X，Y散点图”类型中选择“无数据点平滑线散点图”；

这时，可按住鼠标察看即将绘好的函数图像。

另外，对于自变量仅为正值的函数，也可用“图表向导”中的“折线图”格式绘制。

16、给工作表命名应注意的问题

　　有时为了直观，往往要给工作表重命名（Excel默认的荼表名是sheet1、sheet2.....），在重命名时应注意最好不要用已存在的函数名来作荼表名，否则在下述情况下将产征收岂义。

我们知道，在工作薄中复制工作表的方法是，按住Ctrl健并沿着标签行拖动选中的工作表到达新的位置，复制成的工作表以“源工作表的名字+

（2）”形式命名。

例如，源表为ZM，则其“克隆”表为ZM

（2）。

在公式中Excel会把ZM

（2）作为函数来处理，从而出错。

因而应给ZM

（2）工作表重起个名字。

17、如何拆分或取消拆分窗口？

　　当我们给一个工作表输入数据时，在向下滚动过程中，尤其是当标题行消失后，有时会记错各列标题的相对位置。

这时可以将窗口拆分为几部分，然后将标题部分保留在屏幕上不动，只滚动数据部分。

其方法是在主菜单上单击“窗口”＼“拆分窗口”。

取消拆分窗口时除了使用“窗口”＼“撒消拆分窗口”命令外，有捷径：

将鼠标指针置于水平拆分或垂直拆分线或双拆分钱交点上，双击鼠标即可取消已拆分的窗口。

18、如何给工作簿扩容？

　　选取“工具”＼“选项”命令，选择“常规”项，在“新工作薄内的工作表数”对话栏用上下箭头改变打开新工作表数。

一个工作薄最多可以有255张工作表，系统默认值为6。

19、如何减少重复劳动？

　　我们在实际应用Excel时，经常遇到有些操作重复应用（如定义上下标等）。

为了减少重复劳动，我们可以把一些常用到的操作定义成宏。

其方法是：

选取“工具”菜单中的“宏”命令，执行“记录新宏”，记录好后按“停止”按钮即可。

也可以用VBA编程定义宏。

20、如何快速地批量修改数据？

　　假如有一份Excel工作簿，里面有所有职工工资表。

现在想将所有职工的补贴增加50（元），当然你可以用公式进行计算，但除此之外还有更简单的批量修改的方法，即使用“选择性粘贴”功能：

　　首先在某个空白单元格中输入50，选定此单元格，选择“编辑”＼“复制”。

选取想修改的单元格区域，例如从E2到E150。

然后选择“编辑”＼“选择性粘贴”，在“选择性粘贴”对话框“运算”栏中选中“加”运算，按“确定”健即可。

最后，要删除开始时在某个空白单元格中输入的50。

21、如何快速删除特定的数据？

　　假如有一份Excel工作薄，其中有大量的产品单价、数量和金额。

如果想将所有数量为0的行删除，首先选定区域（包括标题行），然后选择“数据”＼“筛选”＼“自动筛选”。

在“数量”列下拉列表中选择“0”，那么将列出所有数量为0的行。

此时在所有行都被选中的情况下，选择“编辑”＼“删除行”，然后按“确定”即可删除所有数量为0的行。

最后，取消自动筛选

22、如何快速删除工作表中的空行？

　　以下几种方法可以快速删除空行：

　　方法一：

如果行的顺序无关紧要，则可以根据某一列排序，然后可以方便地删掉空行。

　　方法二：

如果行的顺序不可改变，你可以先选择“插入”＼“列”，插入新的一列入在A列中顺序填入整数。

然后根据其他任何一列将表中的行排序，使所有空行都集中到表的底部，删去所有空行。

最后以A列重新排序，再删去A列，恢复工作表各行原来的顺序。

　　方法三：

使用上例“如何快速删除特定的数据”的方法，只不过在所有列的下拉列表中都选择“空白”。

23、使用数组公式

　　Excel中数组公式非常有用，它可建立产生多值或对一组值而不是单个值进行操作的公式。

要输入数组公式，首先必须选择用来存放结果的单元格区域，在编辑栏输入公式，然后按ctrl＋Shift＋Enter组合键锁定数组公式，Excel将在公式两边自动加上括号“{}”。

要编辑或清除数组公式．需选择数组区域并且激活编辑栏，公式两边的括号将消失，然后编辑或清除公式，最后按Ctrl＋shift＋Enter键。

24、如何不使显示或打印出来的表格中包含有0值？

　　通常情况下，我们不希望显示或打印出来的表格中包含有0值，而是将其内容置为空。

例如，图1合计列中如果使用“=b2+c2+d2”公式，将有可能出现0值的情况，如何让0值不显示？

　　方法一；

使用加上If函数判断值是否为0的公式，即：

　　=if（b2＋c2＋d2＝0，“”，b2＋c2＋d2）

选择“工具”＼“选项”＼“视窗”，在“窗口选项”中去掉“零值”选项。

使用自定义格式。

　　选中E2：

E5区域，选择“格式”＼“单元格”＼“数字”，从“分类”列表框中选择“自定义”，在“格式”框中输入“G/通用格式；

G/通用格式；

；

”，按“确定”按钮即可。

25、在Excel中用Average函数计算单元格的平均值的，值为0的单元格也包含在内。

有没有办法在计算平均值时排除值为0的单元格？

如果单元格中的值为0，可用上例“0值不显示的方法”将其内容置为空，此时空单元格处理成文本，这样就可以直接用Average函数计算了。

巧用Countif函数

　　例如，下面的公式可计算出b2:

B10区域中非0单元格的平均值：

　　=sum（b2:

b10）/countif（b2:

b1o，"

<

>

0"

）

26、如何在Excel中实现“自动更正”功能？

　　Word用户都知道，利用Word的“自动更正”功能可以实现数据的快速输入．但在Excel中却没有类似“自动更正”功能的菜单命令。

其实，使用VloopuP函数可以巧妙地解决这一问题。

27、如何对工作簿进行安全保护？

　　如果你不想别人打开或修改你的工作簿，那么想法加个密码吧。

打开工作薄，选择“文件”菜单中的“另存为”命令，选取“选项”，根据用户的需要分别输入“打开文件口令”或“修改文件D令”，按“确定”退出。

　　工作簿（表）被保护之后，还可对工作表中某些单元格区域的重要数据进行保护，起到双重保护的功能，此时你可以这样做：

首先，选定需保护的单元格区域，选取“格式”菜单中的“单元格”命令，选取“保护”，从对话框中选取“锁定”，单由“确定”按钮退出。

然后选取“工具”菜单中的“保护”命令，选取“保护工作表”，根据提示两次输入口令后退出。

　　注意：

不要忘记你设置有“口令”。

28、如何使单元格中的颜色和底纹不打印出来？

　　对那些加了保护的单元格，还可以设置颜色和底纹，以便让用户一目了然，从颜色上看出那些单元格加了保护不能修改，从而可增加数据输入时的直观感觉。

但却带来了问题，即在黑白打印时如果连颜色和底纹都打出来，表格的可视性就大打折扣。

解决办法是：

选择“文件”＼“页面设置”＼“工作表”，在“打印”栏内选择“单元格单色打印”选项。

之后，打印出来的表格就面目如初了。

29、工作表保护的口令忘记了怎么办？

　　如果你想使用一个保护了的工作表，但口令又忘记了，有办法吗？

选定工作表，选择“编辑”＼“复制”、“粘贴”，将其拷贝到一个新的工作薄中（注意：

一定要是新工作簿），即可超越工作表保护。

当然，提醒你最好不用这种方法盗用他人的工作表。

30、“$”的功用

　　Excel一般使用相对地址来引用单元格的位置，当把一个含有单元格地址的公式拷贝到一个新的位置，公式中的单元格地址会随着改变。

你可以在列号或行号前添加符号“$”来冻结单元格地址，使之在拷贝时保持固定不变。

31、如何用汉字名称代替单元格地址？

　　如果你不想使用单元格地址，可以将其定义成一个名字。

定义名字的方法有两种：

一种是选定单元格区域后在“名字框”直接输入名字，另一种是选定想要命名的单元格区域，再选择“插入”＼“名字”＼“定义”，在“当前工作簿中名字”对话框内键人名字即可。

使用名字的公式比使用单元格地址引用的公式更易于记忆和阅读，比如公式“=SUM（实发工资）”显然比用单元格地址简单直观，而且不易出错。

32、如何在公式中快速输入不连续的单元格地址？

　　在SUM函数中输入比较长的单元格区域字符串很麻烦，尤其是当区域为许多不连续单元格区域组成时。

这时可按住Ctrl键，进行不连续区域的选取。

区域选定后选择“插入”＼“名字”＼“定义”，将此区域命名，如Group1，然后在公式中使用这个区域名，如“＝SUM（Group1）”。

33、如何定义局部名字？

　　在默认情况下，工作薄中的所有名字都是全局的。

其实，可以定义局部名字，使之只对某个工作表有效，方法是将名字命名为“工作表名！

名字”的形式即可。

34、如何命名常数？

　　有时，为常数指定一个名字可以节省在整个工作簿中修改替换此常数的时间。

例如，在某个工作表中经常需用利率4.9％来计算利息，可以选择“插入”＼“名字”＼“定义”，在“当前工作薄的名字”框内输入“利率”，在“引用位置”框中输入“＝0.04.9”，按“确定”按钮。

35、工作表名称中能含有空格吗？

　　能。

例如，你可以将某工作表命名为“ZhuMeng”。

有一点结注意的是，当你在其他工作表中调用该工作表中的数据时，不能使用类似“＝ZhUMeng！

A2”的公式，否则Excel将提示错误信息“找不到文件Meng”。

解决的方法是，将调用公式改为“＝'

ZhuMg'

!

A2”就行了。

当然，输入公式时，你最好养成这样的习惯，即在输入“＝”号以后，用鼠标单由ZhuMeng工作表，再输入余下的内容。

36、如何为表格添加斜线？

　　一般我们习惯表格上有斜线，而工作表本身并没有提供该功能。

其实，我们可以使用绘图工具来实现：

　　单击“绘图”按钮，选取“直线”，鼠标变成十字型．将其移至要添加斜线的开始位置，按住鼠标左键拖动至终止位置，释放鼠标，斜线就画出来了。

另外，使用“文字框”按钮可以方便地在斜线上下方添加文字，但文字周围有边框，要想取消它，可选中文字框，调出快显菜单，选择“对象格式”＼“图案”，选择“无边框”项即可。

37、如何快速地将数字作为文本输入？

　　在输入数字前加一个单引号“”’，可以强制地将数字作为文本输入。

38、如何快速输入数据序列？

　　如果你需要输入诸如表格中的项目序号、日期序列等一些特殊的数据系列，千万别逐条输入，为何不让Excel自动填充呢？

在第一个单元格内输入起始数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按Excel内部规定的序列进行填充。

如果能将自己经常要用到的某些有规律的数据（如办公室人员名单），定义成序列，以备日后自动填充，岂不一劳永逸！

选择“工具”菜单中的“选项”命令，再选择“自定义序列”标签，在输入框