电子表格知识大全.docx
《电子表格知识大全.docx》由会员分享，可在线阅读，更多相关《电子表格知识大全.docx（32页珍藏版）》请在冰豆网上搜索。
电子表格知识大全
电子表格知识大全
摘自：
原创作者：
邹凯华
EXCEL取单元格颜色、字体、格式等等函数2007-02-2813:
50函数定义:
GET.CELL（类型号,单元格（或范围））
比如现在我们要取得A1单元格里的数据格式?
结果显示在B1步骤如下:
1.任选一个单元格-->插入--->名称-->定义
2.在名称称输入AA（任意名称）
3.在引用位置上写入=GET.CELL（7,Sheet1!
$A$1）
4.点添加
5.在B1单元格里输入=AA
看到了,显示的结果是什么?
?
?
如果没修改过的话应该是G/通用格式吧!
以下是类型号及代表的意思
1参照储存格的绝对地址
2参照储存格的列号
3参照储存格的栏号
4类似TYPE函数
5参照地址的内容
6文字显示参照位址的公式
7参照位址的格式，文字显示
8文字显示参照位址的格式
9传回储存格外框左方样式，数字显示
10传回储存格外框右方样式，数字显示
11传回储存格外框方上样式，数字显示
12传回储存格外框方下样式，数字显示
13传回内部图样，数字显示
14如果储存格被设定locked传回True
15如果公式处于隐藏状态传回True
16传回储存格宽度
17以点为单位传回储存格高度
18字型名称
19以点为单位元传回字号
20如果储存格所有或第一个字符为加粗传回True
21如果储存格所有或第一个字符为斜体传回True
22如果储存格所有或第一个字符为单底线传回True
23如果储存格所有或第一个字符字型中间加了一条水平线传回True
24传回储存格第一个字符色彩数字，1至56。
如果设定为自动，传回0
25MSExcel不支持大纲格式
26MSExcel不支持阴影格式
27数字显示手动插入的分页线设定
28大纲的列层次
29大纲的栏层次
30如果范围为大纲的摘要列则为True
31如果范围为大纲的摘要栏则为True
32显示活页簿和工作表名称
33如果储存格格式为多行文字则为True
34传回储存格外框左方色彩，数字显示。
如果设定为自动，传回0
35传回储存格外框右方色彩，数字显示。
如果设定为自动，传回0
36传回储存格外框上方色彩，数字显示。
如果设定为自动，传回0
37传回储存格外框下方色彩，数字显示。
如果设定为自动，传回0
38传回储存格前景阴影色彩，数字显示。
如果设定为自动，传回0
39传回储存格背影阴影色彩，数字显示。
如果设定为自动，传回0
40文字显示储存格样式
41传回参照地址的原始公式
42以点为单位传回使用中窗口左方至储存格左方水平距离
43以点为单位传回使用中窗口上方至储存格上方垂直距离
44以点为单位传回使用中窗口左方至储存格右方水平距离
45以点为单位传回使用中窗口上方至储存格下方垂直距离
46如果储存格有插入批注传回True
47如果储存格有插入声音提示传回True
48如果储存格有插入公式传回True
49如果储存格是数组公式的范围传回True
50传回储存格垂直对齐，数字显示
51传回储存格垂直方向，数字显示
52传回储存格前缀字符
53文字显示传回储存格显示内容
54传回储存格数据透视表名称
55传回储存格在数据透视表的位置
56枢纽分析
57如果储存格所有或第一个字符为上标传回True
58文字显示传回储存格所有或第一个字符字型样式
59传回储存格底线样式，数字显示
60如果储存格所有或第一个字符为下标传回True
61枢纽分析
62显示活页簿和工作表名称
63传回储存格的填满色彩
64传回图样前景色彩
65枢纽分析
66显示活页簿名称
excel颜色函数问题，高手领分！
~
悬赏分：
100-解决时间：
2007-5-2117:
12
举例：
A1为数据0、1、2、3、4、5、6、7、8、9
B1为随意数据
我想让当A1为0或5时C1添0，当A1为其他数值时C1=B1+1
刚才解决的问题！
C1=IF（OR（A1=0，A1=5）,"0",SUM（B1+1））
------------------------------------------------
继续延伸我想让A1为0或5时C1添0，字体颜色自动变为白色，所在C1格自动填充为红色，当A1为其他数值时C1=B1+1
这个EXCEL可以办到吗？
如用函数做不好可以调用别的方法，或者用宏做吗？
问题补充：
谢谢！
lb2000但是你说的也不对！
选中C1单元格，点“格式”，“条件格式”，第一个选“单元格值”，后面选“等于”后面填=IF（OR（A1=5,A1=0），“0”）这样才可以不过谢谢你的提醒！
多
对最佳答案的评论共2条
还有另一种也可以，就是选等于，填"0",格式选字白图案红，再添加一个条件，填"5"，格式同上。
评论者：
lb2000-高级魔法师六级
我这里这两种方法都可以。
我用的是OFFICE2003，不知道是不是版本的问题？
评论者：
lb2000-高级魔法师六级
其他回答共1条
选中C1“格式”，“条件格式”条件1
（1）“单元格数值”右边选“等于”右边输入0后点“格式”设置字体颜色为白色后确定，点“添加”条件2
（2）“单元格数值”右边选“不等于”右边输入0后点“格式”设置填充色为红色后确定两次，有问题发消息吧
Excel使用技巧
蔡琳 收集
在工作当中用电子表格来处理数据将会更加迅速、方便，而在各种电子表格处理软件中，Excel以其功能强大、操作方便著称，赢得了广大用户的青睐。
虽然Excel使用很简单，不过真正能用好Excel的用户并不多，很多人一直停留在录入数据的水平，本文将向你介绍一些非常使用的技巧，掌握这些技巧将大大提高你的工作效率。
⒈快速定义工作簿格式
首先选定需要定义格式的工作簿范围，单击“格式”菜单的“样式”命令，打开“样式”对话框；然后从“样式名”列表框中选择合适的“样式”种类，从“样式包括”列表框中选择是否使用该种样式的数字、字体、对齐、边框、图案、保护等格式内容；单击“确定”按钮，关闭“样式”对话框，Excel工作簿的格式就会按照用户指定的样式发生变化，从而满足了用户快速、大批定义格式的要求。
&返回&
⒉快速复制公式
复制是将公式应用于其它单元格的操作，最常用的有以下几种方法：
一是拖动制复制。
操作方法是：
选中存放公式的单元格，移动空心十字光标至单元格右下角。
待光标变成小实心十字时，按住鼠标左键沿列（对行计算时）或行（对列计算时）拖动，至数据结尾完成公式的复制和计算。
公式复制的快慢可由小实心十字光标距虚框的远近来调节：
小实心十字光标距虚框越远，复制越快；反之，复制越慢。
也可以输入复制。
此法是在公式输入结束后立即完成公式的复制。
操作方法：
选中需要使用该公式的所有单元格，用上面介绍的方法输入公式，完成后按住Ctrl键并按回车键，该公式就被复制到已选中的所有单元格。
还可以选择性粘贴。
操作方法是：
选中存放公式的单元格，单击Excel工具栏中的“复制”按钮。
然后选中需要使用该公式的单元格，在选中区域内单击鼠标右键，选择快捷选单中的“选择性粘贴”命令。
打开“选择性粘贴”对话框后选中“粘贴”命令，单击“确定”，公式就被复制到已选中的单元格。
&返回&
⒊快速显示单元格中的公式
如果工作表中的数据多数是由公式生成的，如果想要快速知道每个单元格中的公式形式，可以这样做：
用鼠标左键单击“工具”菜单，选取“选项”命令，出现“选项”对话框，单击“视图”选项卡，接着设置“窗口选项”栏下的“公式”项有效，单击“确定”按钮。
这时每个单元格中的公式就显示出来了。
如果想恢复公式计算结果的显示，再设置“窗口选项”栏下的“公式”项失效即可。
&返回&
⒋快速删除空行
有时为了删除Excel工作簿中的空行，你可能会将空行一一找出然后删除，这样做非常不方便。
你可以利用“自动筛选”功能来简单实现。
先在表中插入新的一行（全空），然后选择表中所有的行，选择“数据”菜单中的“筛选”，再选择“自动筛选”命令。
在每一列的项部，从下拉列表中选择“空白”。
在所有数据都被选中的情况下，选择“编辑”菜单中的“删除行”，然后按“确定”即可。
所有的空行将被删去。
插入一个空行是为了避免删除第一行数据。
&返回&
⒌自动切换输入法
当你使用Excel2000编辑文件时，在一张工作表中通常是既有汉字，又有字母和数字，于是对于不同的单元格，需要不断地切换中英文输入方式，这不仅降低了编辑效率，而且让人不胜其烦。
在此，笔者介绍一种方法，让你在Excel2000中对不同类型的单元格，实现输入法的自动切换。
新建或打开需要输入汉字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输入法模式”选项卡，在“模式”下拉列表框中选择“打开”，单击“确定”按钮。
选择需要输入字母或数字的单元格区域，单击“数据”菜单中的“有效性”，再选择“输入法模式”选项卡，在“模式”下拉列表框中选择“关闭（英文模式）”，单击“确定”按钮。
之后，当插入点处于不同的单元格时，Excel2000能够根据我们进行的设置，自动在中英文输入法间进行切换。
就是说，当插入点处于刚才我们设置为输入汉字的单元格时，系统自动切换到中文输入状态，当插入点处于刚才我们设置为输入数字或字母单元格时，系统又能自动关闭中文输入法。
&返回&
⒍自动调整小数点
如果你有一大批小于1的数字要录入到Excel工作表中，如果录入前先进行下面的设置，将会使你的输入速度成倍提高。
单击“工具”菜单中的“选项”，然后单击“编辑”选项卡，选中“自动设置小数点”复选框，在“位数”微调编辑框中键入需要显示在小数点右面的位数。
在此，我们键入“2”单击“确定”按钮。
完成之后，如果在工作表的某单元格中键入“4”，则在你按了回车键之后，该单元格的数字自动变为“0.04”。
方便多了吧！
此时如果你在单元格中键入的是“8888”，则在你结束输入之后，该单元格的数字自动变为“88.88”。
&返回&
⒎用“记忆式输入”
有时我们需要在一个工作表中的某一列输入相同数值，这时如果采用“记忆式输入”会帮你很大的忙。
如在职称统计表中要多次输入“助理工程师”，当第一次输入后，第二次又要输入这些文字时，只需要编辑框中输入“助”字，Excel2000会用“助”字与这一列所有的内容相匹配，若“助”字与该列已有的录入项相符，则Excel2000会将剩下的“助理工程师”四字自动填入。
按下列方法设置“记忆式输入”：
选择“工具”中的“选项”命令，然后选择“选项”对话框中的“编辑”选项卡，选中其中的“记忆式键入”即可。
&返回&
⒏用“自动更正”方式实现快速输入
使用该功能不仅可以更正输入中偶然的笔误，也可能把一段经常使用的文字定义为一条短语，当输入该条短语时，“自动更正”便会将它更换成所定义的文字。
你也可以定义自己的“自动更正”项目：
首先，选择“工具”中的“自动更正”命令；然后，在弹出的“自动更正”对话框中的“替换”框中键入短语“爱好者”，在“替换为”框中键入要替换的内容“电脑爱好者的读者”；最后，单击“确定”退出。
以后只要输入“爱好者”，则整个名称就会输到表格中。
&返回&
⒐用下拉列表快速输入数据
如果你希望减少手工录入的工作量，可以用下拉表来实现。
创建下拉列表方法为：
首先，选中需要显示下拉列表的单元格或单元格区域；接着，选择菜单“数据”菜单中的“有效性”命令，从有效数据对话框中选择“序列”，单击“来源”栏右侧的小图标，将打开一个新的“有效数据”小对话框；接着，在该对话框中输入下拉列表中所需要的数据，项目和项目之间用逗号隔开，比如输入“工程师，助工工程师，技术员”，然后回车。
注意在对话框中选择“提供下拉箭头”复选框；最后单击“确定”即可。
&返回&
10..两次选定单元格
有时，我们需要在某个单元格内连续输入多个测试值，以查看引用此单元格的其他单元格的效果。
但每次输入一个值后按Enter键，活动单元格均默认下移一个单元格，非常不便。
此时，你肯定会通过选择“工具”\“选项"\“编辑"，取消“按Enter键移动活动单元格标识框"选项的选定来实现在同一单元格内输入许多测试值，但以后你还得将此选项选定，显得比较麻烦。
其实，采用两次选定单元格方法就显得灵活、方便：
单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格（此时，单元格周围将出现实线框）。
&返回&
11.“Shift＋拖放"的妙用
在拖放选定的一个或多个单元格至新的位置时，同时按住Shift键可以快速修改单元格内容的次序。
具体方法为：
选定单元格，按下Shift键，移动鼠标指针至单元格边缘，直至出现拖放指针箭头“?
"，然后进行拖放操作。
上下拖拉时鼠标在单元格间边界处会变为一个水平“工"状标志，左右拖拉时会变为垂直“工"状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。
这种简单的方法节省了几个剪切和粘贴或拖放操作，非常方便。
&返回&
12.超越工作表保护的诀窍
如果你想使用一个保护了的工作表，但又不知道其口令，有办法吗？
有。
选定工作表，选择“编辑"\“复制"、“粘贴"，将其拷贝到一个新的工作簿中（注意：
一定要新工作簿），即可超越工作表保护。
&返回&
13.巧用IF函数
（1）.设有一工作表，C1单元格的计算公式为：
=A1／B1，当A1、B1单元格没有输入数据时，C1单元格会出现“＃DIV／0！
”的错误信息。
这不仅破坏了屏幕显示的美观，特别是在报表打印时出现“＃DIV／0！
”的信息更不是用户所希望的。
此时，可用IF函数将C1单元格的计算公式更改为：
=IF（B1=0，″″，A1／B1）。
这样，只有当B1单元格的值是非零时，C1单元格的值才按A1／B1进行计算更新，从而有效地避免了上述情况的出现。
（2）.设有C2单元格的计算公式为：
=A2＋B2，当A2、B2没有输入数值时，C2出现的结果是“0”，同样，利用IF函数把C2单元格的计算公式更改如下：
=IF（AND（A2=″″，B2=″″），″″，A2＋B2）。
这样，如果A2与B2单元格均没有输入数值时，C2单元格就不进行A2＋B2的计算更新，也就不会出现“0”值的提示。
（3）.设C3单元格存放学生成绩的数据，D3单元格根据C3（学员成绩）情况给出相应的“及格”、“不及格”的信息。
可用IF条件函数实现D3单元格的自动填充，D3的计算公式为：
=IF（C3＜60，″不及格″，″及格″＝。
&返回&
14.累加小技巧
我们在工作中常常需要在已有数值的单元格中再增加或减去另一个数。
一般是在计算器中计算后再覆盖原有的数据。
这样操作起来很不方便。
这里有一个小技巧，可以有效地简化老式的工作过程。
（1）.创建一个宏：
选择Excel选单下的“工具→宏→录制新宏”选项；
宏名为：
MyMacro；
快捷键为：
Ctrl＋Shift＋J（只要不和Excel本身的快捷键重名就行）；
保存在：
个人宏工作簿（可以在所有Excel工作簿中使用）。
（2）.用鼠标选择“停止录入”工具栏中的方块，停止录入宏。
（3）.选择Excel选单下的“工具→宏→VisualBasic编辑器”选项。
（4）.在“VisualBasic编辑器”左上角的VBAProject中用鼠标双击VBAProject（Personal.xls）打开“模块→Module1”。
注意：
你的模块可能不是Module1,也许是Module2、Module3。
（5）.在右侧的代码窗口中将Personal.xls－Module1（Code）中的代码更改为：
SubMyMacro（）
OldValue=Val（ActiveCell.Value）
InputValue=InputBox（“输入数值，负数前输入减号”,“小小计算器”）
ActiveCell.Value=Val（OldValue＋InputValue）
EndSub
（6）.关闭VisualBasic编辑器。
编辑完毕，你可以试试刚刚编辑的宏，按下Shift＋Ctrl＋J键，输入数值并按下“确定”键。
（这段代码只提供了加减运算，借以抛砖引玉。
）
&返回&
15.怎样保护表格中的数据
假设要实现在合计项和小计项不能输入数据，由公式自动计算。
首先，输入文字及数字，在合计项F4至F7单元格中依次输入公式：
=SUM（B4∶E4）、=SUM（B5∶E5）、=SUM（B6∶E6）、=SUM（B7∶E7），在小计项B8至F8单元格中依次输入公式：
=SUM（B4∶B7）、=SUM（C4∶C7）、=SUM（D4∶D7）、=SUM（E4∶E7）、=SUM（F4∶F7）。
在默认情况下，整个表格的单元格都是锁定的，但是，由于工作表没有被保护，因此锁定不起作用。
选取单元格A1∶F8，点击“格式→单元格”选单，选择“保护”选项，消除锁定复选框前的对勾，单击确定。
然后，再选取单元格F4∶F7和B8∶F8，点击“格式→单元格”选单，选择“保护”选项，使锁定复选框选中，单击确定，这样，就把这些单元格锁定了。
接着，点击“工具→保护→保护工作表”选单，这时，会要求你输入密码，输入两次相同的密码后，点击确定，工作表就被保护起来了，单元格的锁定也就生效了。
今后，可以放心地输入数据而不必担心破坏公式。
如果要修改公式，则点击“工具→保护→撤消保护工作表”选单，这时，会要求你输入密码，输入正确的密码后，就可任意修改公式了。
&返回&
16．如何避免Excel中的错误信息
在Excel中输入或编辑公式后，有可能不能正确计算出结果，Excel将显示一个错误信息，引起错误的原因并不都是由公式本身有错误产生的。
下面我们将介绍五种在Excel中常出现的错误信息，以及如何纠正这些错误。
错误信息1—＃＃＃＃
输入到单元格中的数据太长或单元格公式所产生的结果太大，在单元格中显示不下时，将在单元格中显示＃＃＃＃。
可以通过调整列标之间的边界来修改列的宽度。
如果对日期和时间做减法，请确认格式是否正确。
Excel中的日期和时间必须为正值。
如果日期或时间产生了负值，将在整个单元格中显示＃＃＃＃。
如果仍要显示这个数值，请单击“格式”菜单中的“单元格”命令，再单击“数字”选项卡，然后选定一个不是日期或时间的格式。
错误信息2—＃DIV/0!
输入的公式中包含明显的除数0，例如－120/0，则会产生错误信息DIV/0!
。
或在公式中除数使用了空单元格（当运算对象是空白单元格，Excel将此空值解释为零值）或包含零值单元格的单元格引用。
解决办法是修改单元格引用，或者在用作除数的单元格中输入不为零的值。
错误信息3—＃VALUE!
当使用不正确的参数或运算符时，或者当执行自动更正公式功能时不能更正公式，都将产生错误信息＃VALUE!
。
在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。
这时应确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。
例如，单元格B3中有一个数字，而单元格B4包含文本，则公式=B3＋B4将返回错误信息＃VALUE!
。
错误信息4—＃NAME?
在公式中使用了Excel所不能识别的文本时将产生错误信息＃NAME?
。
可以从以下几方面进行检查纠正错误：
（1）如果是使用了不存在的名称而产生这类错误，应确认使用的名称确实存在。
在“插入”菜单中指向“名称”，再单击“定义”命令，如果所需名称没有被列出，请使用“定义”命令添加相应的名称。
（2）如果是名称，函数名拼写错误应修改拼写错误。
（3）确认公式中使用的所有区域引用都使用了冒号（：
）。
例如：
SUM（A1：
C10）。
注意将公式中的文本括在双引号中。
错误信息5—＃NUM!
当公式或函数中使用了不正确的数字时将产生错误信息＃NUM!
。
要解决问题首先要确认函数中使用的参数类型正确。
还有一种可能是由公式产生的数字太大或太小，Excel不能表示，如果是这种情况就要修改公式，使其结果在－1×10307和1×10307之间。
&返回&
17.不用编程--Excel公式也能计算个人所得税
个人所得税的计算看起来比较复杂，似乎不用VBA宏编程而只用公式来计算是一件不可能的事。
其实，Excel提供的函数公式不但可以计算个人所得税，而且还有很大的灵活：
可以随意改变不扣税基数，随意改变各扣税分段界限值及其扣税税率（说不定以后调整个人所得税时就可以用到。
）
不管是编程还是使用公式，都得将个人所得税的方法转化为数学公式，并且最好将这个公式化简，为以后工作减少困难。
以X代表你的应缴税（减去免税基数）的工薪收入（这里的个人所得税仅以工薪为例），Tax代表应缴所得税，那么：
当500＜X≤2000则TAX=（X-500）*10+500*5=>TAX=X*10-25
当2000＜X≤5000则TAX=（X-2000）*15+2000*10=>TAX=X*15-125
．．．．．．
依此类推，通用公式为：
个人所得税=应缴税工薪收入*该范围税率-扣除数
在此,扣除数=应缴税工薪收入上一范围上限*该范围税率-上一范围扣除数
其实只有四个公式，即绿色背景处。
黄色背景处则为计算时输入数据的地方。
各处公式设置即说明如下：
E3：
=C3*D3-C3*D2+E2
E4-E10：
根据E3填充得到，或者拷贝E3粘贴得到
C15：
=IF（B15>$B$12,B15-$B$12,0）如果所得工薪大于不扣税基数，则应纳税工薪为工薪减去为零不扣税基数，否则，应纳税工薪零。
D15：
=VLOOKUP（C15,$C$2:
$C$10,1）查阅应纳税工薪属于哪个扣税范围。
E15：
=C15*VLOOKUP（D15,$C$2:
$E$10,2）-VLOOKUP（D15,$C$2:
$E$10,3）查阅该扣税范围扣税税率和应减的扣除数。
这里主要用到VLOOKUP函数，可查阅帮助获取更多信息。
C15，D15的公式可以合并到E15中，那样可读性会差很多，但表格会清晰一些。
合并后公式：
=IF（B15>$B$12,B15-$B$12,0）*VLOOKUP（VLOOKUP（IF（B15>$B$12,B15-$B$12,0）,$C$2:
$C$10,1）,$C$2:
$E$10,2）-VLOOKUP（VLOOKUP（IF（B15>$B$12,B15-$B$12,0）,$C$2:
$C$10,1）,$C$2:
$E$10,3）实际上是将公式中出现的C15，D15用其公式替代即可。
&返回&
18.用EXCEL轻松处理学生成绩
期末考试结束后，主任要求班主任自已统计本班成绩，尽快上报教导处。
流程包括录入各科成绩→计算总分、平均分并排定名次→统计各科分数段人数、及格率、优秀率及综合指数→打印各种统计报表→制作各科统计分析图表等。
有了EXCEL，我们可用不着躬着身、驼着背、拿着计算器一个一个算着学生的成绩了！
我迅速地打开电脑，启动EXCEL2000，录入学生的考试成绩，如图1所示。
然后在J2单元格处输入公式"=sum（c2:
i2）",然后拖动填充柄向下填充，便得到了每人的总分。
接着在k2单元格处输入公式"=average（c2:
i2）"，然后拖动填充柄向下填充，便得到了
升级会员 