【转帖】execel操作技巧

【转帖】execel操作技巧

世界是由懒人创造的。比如人们懒得扛东西，就发明了轮子，懒得走千里路，就发明了汽车、电话、电视机，所以，任何慢的方法，我们都要改进，任何重复性很强的工作，我们都要交给电脑去做，我们天生就不是那种脚踏实地、慢工出细活的人，所以，我们只有欺负一下电脑了，毕竟人家电脑"吃"的是"电"，我们吃的是"米饭"，电脑做这些重复性的工作还是很在行的。

所以，请明确：
　　电脑是用来提升工作效率的，不能让电脑束缚我们。
　　电脑是要用两只手来操作的，如果你的一只手拿着鼠标，请用另一只手操作键盘。

工作就是学习，学习就是工作，所以请明确：
　　使用电脑时，如果你不知道如何操作是，请按F1，微软的帮助文件做得很好，如果看不懂，这再看一遍，还不懂，那就还再看一遍。

一、格式刷的妙用：
　　双击格式刷后，可以一直对不同的单元格来刷格式，直到按一下格式刷按钮或是按一下Esc键，这个用法在word里也实用。

二、单元格的快速填充：
　　比如在单元格A1:A10都有数据，你在B1填上一个数据，不能为空，我们假使输入的为一，我想大家都会把B1:B10全部填为1，但是我要讲

的是一个快速的方法，当光标变为十字型的时间，不要往下拖，只要双击一下，B1:B10就全部填充了1了，如果很多，拖起来就很不方便，用此

法就很好。此法对公式也有效。

三、最常用的快捷键，提升工作效率的法宝：
　　我介绍的快捷键是使用频率很高的，如果记住了，可以提高效率。为了实现这个目标，就要求大家能做到一只手操作鼠标，一只手操作键

盘。
　　请注意，Ctrl和Shift键通常是和其它键或鼠标左键是同时被按下的。
　　 1、Ctrl+c,Ctrl+x,Ctrl+v,这就不用介绍了，这三个要用熟，一定要结合鼠标，否则这意义不大。
　　 2、Ctrl+a选中工作表中所有的内容，在office2003中，将会选择表格中的所有数据。
　　 3、Ctrl+f ,Ctrl+h，前一个是查找，后一个是查找替换。
　　 4、Ctrl+g,快速定位，这个快速定位的作用是能快速选中满足你的条件的单元格或行或列，比如，先选择要定位的区域，假设是A1:H30,

选中后，按下快捷键，在对话框中选择"定位条件..."再选择公式，将数字，文本，逻辑值的勾去掉，只留下错误，按下确定后，在区域A1:H30

中，所有出现错误的公式就被选中了，按下Delete键，就可以将这些害群之马删掉，否则，它们会影响对此区域的求和或其它操作。
　　 5、Ctrl,Shift键与鼠标的结合使用,这样，你就可以看到鼠标与键盘结合的威力是多么大.
　　　　1)Shift + 鼠标左键，可以选择一个区域。传统的做法是用鼠标拖动，但是，当要选择的区域很大，比如，A1

1000,如查用传统方法

，你会发现，很不顺手，就像是再捉泥鳅，很不听使唤，所以，你可以先选中单元格A1,再用鼠标拖动右边的滚动条，直到出现D1000为止，此

时，先按住Shift不放，再用鼠标单击D1000,这样，A1

1000就会被选中了。
　　　　对于此法，还有一个更快的方法，也是先选中A1，再在名称框（在左上角，当你选中A1时，上面会显示"A1"）中输入D1000,此时，按

住Shift后按下回车就可以了。
　　　　你事先可能不知道是1000行，或许是很多，就可以用另一中方法，先选中A1

1(用第一种方法呀)，按住Shift,再按一下键盘上的向下

键（↓）。如果是按下shift + →←↑↓中和任何一种，效果类似。
　　　　2）选择不规则区域，如果你要同时选中A1

34,f3:h12,等等，你可以先按住Ctrl,再用鼠标分别拖动选择这几个区域。
　　　　3）Ctrl + →←↑↓可快速移动到数据区域的边缘，比如A列中有1000行数据，如是你选中的是A1,按住Ctrl键不放，按一下键盘上的

↓，你会发现A1000立即就被选中了，如果从A1到A1000中间的单元格有空格的话，空格单元格之前的单元格将会被选中，如果重复按下Ctrl+↓

,最终，你可能会跳到表格的最下一行，也就是A65536单元格，不用担心，你再按一下Ctrl + ↑就会又回到A1001。
　　　　4)大家可以试试Ctrl + shift + →←↑↓,看看会出现什么效果。
　　 6、Ctrl+S,保存，这个很重要吧，所以过一段时间这按一下吧，否则，死机后再捶胸顿足就来不及了。

四、冻结窗格的技巧
　　　如果有一个表，A2

1000,其中表头行是A2

2,通常，大家可能会选择第3列,再冻结窗格，这样，表头就固定不动了，但是，如果我们回

到第三条，第5小条，要是要选择A2

1000，这个区域，我们可产把表内容的第一条可给冻结，也就是先选择第4列，而不是第3列，再冻结窗格

，这样，在我们操作的过程中，如果当前是在456行，如果要选择A3

456,这时就很方便了，我们可产直接选择A3,按住shift,再选择D456,这样

是不是很方便。同时，你会发现，对于其它类似的操作，就会很方便。当然，对于横向的操作也适用。 Excel高级应用

一、常用公式

  术语：
参数：指输入给公式的值。
返回值：公式的计算结果。

  1、vlookup，hlookup,查找。
比如我们想达到这样一个目的，在单元格A1中输入产品编号，在单元格B1中自动显示产品名称。当然，电脑是不知道根据编号来做出判

断的，如果人的记性好，是可以做到的，但是还得输入，是不是很烦，还记得我在Excel操作技巧中说的吗，我们就是天生的懒人。幸好有了比

尔大哥。
当然，我们可以做到让电脑知道，产品编号与产品名称的对应关系，所以我们要事先输入一个产品编号与产品名称的对应关系，也就是

一个表，电脑是不是很厉害，很快它就记住了呢。不过我们不能怕麻烦，当你将此表建好后，你还可以用到其它方面。
建好表(与Excel中的表不同，这里表示一组数据)后，假使这个表的区域为G1:H1000,G列为编码，H列为名称。所以，在B1中，我们输入
=VLOOKUP(A1,G1:H1000,2,TRUE)
这个公式是什么意思，A1,表示我们要查找A1，在哪里查找呢，在G1:H1000中查找，2，表示我们从哪一列得到结果，就是表G1:H1000中

的第2列，所以，意思是说，在G1:H1000中的G列中查找A1的值，如果找到，将得到本表中的第2列对应的值，如果G101与A1的值相同，将得到H1

01的值。

另：本公式有四个参数，除第二个参数为一个区域外，其它参数还可以是其它的公式，也叫公式的套嵌，这再其它公式中也一样。第四

个参数为TRUE,或是不输入表示精确匹配，为FALSE表示大致匹配，上面的公式也可以写成
=VLOOKUP(A1,G1:H1000,2,)
第四个参数没有输入，也就是什么都没有（什么都没有与空格，0，等是有很大的区别的）。
大致匹配：例如，a 与 abc或是bac,是大致匹配，"金帝"与"中粮金帝食品（深圳）有限公司"是大致匹配。
如果编码名称对照表大于两列，比如是G1:J1000,如是名称是在I列，那么公式就要写成
   =VLOOKUP(A1,G1:J1000,3,) 或是 =VLOOKUP(A1,G1:I1000,3,)
当然，这是被动获取数据，还可能用于主动获取，即已经有了编码，来查询名称。

  2、公式参数的锁定（可能我对这个功能的叫法不规范）

比如上面的vlookup公式，如果我们要输入很多产品编号，都要得到产品名称，我们可以把所有的产品编吗输入，比如在A1:A10中输入了

编号，将B1中的填充柄双击，B1:B10就全都填充上公式了（也可以复制过去），还记得在Excel操作技巧中介绍过吗？但是我们会发现一个问题

，在B2中公式就不对了，变成了=VLOOKUP(A2,G2:H1001,2,),这显然不行，因为我们的编码对照表是G1:H1000,这样，对照表中的第一条记录就

不能被查询到。在B3中就变成了=VLOOKUP(A3,G3:H1002,2,) ... B10 中的公式就变成了 = VLOOKUP(A10,G10:H1009,2,)，有一个方法可以解决

，将B1中的公式写成=VLOOKUP(A2,$G$2:$H$1001,2,),再填充一次，是不是没问题了？如果你将B1写成=VLOOKUP($A2,$G$2:$H$1001,2,)也行，

因为公式在复制（或自己动填充）时，是在同一列中进行的，所以A不管有没有锁定，都是不变的，如果你写成了=VLOOKUP(A$2,$G$2:$H$1001,

2,)或是=VLOOKUP($A$2,$G$2:$H$1001,2,),就会发现，填充或复制后，B1:B10中，所有的公式都是一样的，因为在A2这个参数中，你在2前面加

了一下$符号，所以复制时，A2不会对应的变为A3,A4....

  2、ISERROR, 公式的结果是否出错
还是用第一个来举例，如果你输入的编码在表中用vlookup查不到，就会显示#N/A(或其它错误结果),这是我们就可以把公式写成：

=IF(ISERROR(VLOOKUP($A2,$G$2:$H$1001,2,)),,VLOOKUP($A2,$G$2:$H$1001,2,))
其中，ISERROR(VLOOKUP($A2,$G$2:$H$1001,2,))是IF公式的第一个参数，表示一个条件，对于VLOOKUP($A2,$G$2:$H$1001,2,),如果查

找的结果不存在，那么VLOOKUP最终会得到

#N/A，所以再计算ISERROR(#N/A)，很显然#N/A是一个错误，这句话是对的，所以，ISERROR(#N/A)=TRUE,所以最终公式就变为了IF(TRUE,,VLOO

KUP($A2,$G$2:$H$1001,2,)) =

,结果是"什么都没有"、"空"（不同于空格或是0，可能有时会显示为"0"），同样，如果VLOOKUP($A2,$G$2:$H$1001,2,)找到了，没有出错，就

变成了IF(FALSE,,VLOOKUP($A2,$G$2:$H$1001,2,)) = VLOOKUP($A2,$G$2:$H$1001,2,),这样就达到了我们的要求了。

  3、sumif,条件求和

有一个销售表，如果我们要求出某个产品（已知编码）的总销售量，表如下：

      A    B       C          D
   ┌────┬──────┬──────┬──────┐
1 │分店代码│ 产品代码 │ 产品名称 │ 产品销量 │
   ├────┼──────┼──────┼──────┤
2 │ A001 │ C001    │ 产品1 │ 10 │
   ├────┼──────┼──────┼──────┤
3 │ A001 │ C002    │ 产品2 │ 11 │
   ├────┼──────┼──────┼──────┤
4 │ A001 │ C003    │ 产品3 │ 12 │
   ├────┼──────┼──────┼──────┤
5 │ A002 │ C001    │ 产品1 │ 10 │
   ├────┼──────┼──────┼──────┤
6 │ A001 │ C003    │ 产品3 │ 12 │
   ├────┼──────┼──────┼──────┤
7 │ A003 │ C002    │ 产品2 │ 11 │
   ├────┼──────┼──────┼──────┤
8 │ A002 │ C002    │ 产品2 │ 11 │
   ├────┼──────┼──────┼──────┤
9 │ A003 │ C003    │ 产品3 │ 12 │
   ├────┼──────┼──────┼──────┤
10 │ A002 │ C003    │ 产品3 │ 12 │
   ├────┼──────┼──────┼──────┤
11 │ A003 │ C001    │ 产品1 │ 10 │
   ├────┼──────┼──────┼──────┤
12 │ A002 │ C003    │ 产品3 │ 12 │
   ├────┼──────┼──────┼──────┤
13 │ A003 │ C002    │ 产品2 │ 11 │
   ├────┼──────┼──────┼──────┤
14 │ A003 │ C003    │ 产品3 │ 12 │
   └────┴──────┴──────┴──────┘
   此表中，比如要查C001的销量总计

      X       Y       Z
   ┌────┬──────┬──────────────────┐
1 │产品代码│ 产品名称 │销量合计                │
   ├────┼──────┼──────────────────┤
2 │ C001 │ 产品1 │=sumif($A$2:$A$10,$X2,$D$2:$D$10) │
   └────┴──────┴──────────────────┘
同样，加上$符号，是为了向下复制公式。公式的含义是指，在A2:A10区域中查找与X2匹配的所有的项，再把这些项对应的在D2

10区域内的所

有对应的数据求和，所以最终结果就是：30.

4、数组公式
   预备知识：数组，比如{10,12,7,45,36,18,73}这是一个一维的数组，共有7个元素，第3个元素的值7。
   对于有些公式（并不是所有的），可以接收数组作为参数，比如sum,像sum(1,2,3,4),它的参数就是一个数组：{1,2,3,4},
   对于有些公式，不但可以接收数组作为参数，它的计算结果（我们通常叫返回值）也可以是数组，例如if。
   所以，我们可以通过将sum和if两个函数组合一起，来得到我们想要的结果。

   问题：在sumif公式中，可以按条件求和，但是，如果有多个条件呢,比如，我们要求A1分店的C001产品的销量汇总，用sumif就不行

了，但我们可以这样
   =SUM(IF($A$2:$A$15="A001",IF($B$2:$B$15="C001",$D$2:$D$15,0),0))
或 =SUM(IF(($A$2:$A$15="A001"

*($B$2:$B$15="C001"

,$D$2:$D$15,0))
对于第二种形式，是什么意呢，原来，在excle中，TRUE(真)的值是1,FALSE(假)的值是0,所是大于等于1的数，也可以表示为TRUE,小于

等于0的数可以表示为FALSE，而且

+，-,*,/这些运算符号，也可能对数组进行运算，所以，分店代码为A001,且产品代码为C001这个可以用($A$2:$A$15="A001"

*($B$2:$B$15="C

001"

来表示，因为1*1=1,0*0=0,1*0=0,0*1=0,所以，这个乘号就也可以进行Bool值（TRUE,FALSE）运算了，即TRUE*TRUE=TRUE,FALSE*FALSE =

FALSE,TRUE*FALSE = FLASE,如果条件不是分店代码为A001,且产品代码为C001,而是分店代码为A001,或产品代码为C001，这个条件就可以表示

为：($A$2:$A$15="A001"

+($B$2:$B$15="C001"

,因为TRUE + TRUE = 2 为TRUE,TRUE + FLASE = 1,为TRUE,FLASE + FALSE = 0,为FALSE,所以

就达到，* 可以作为逻辑运算符的AND,+可以作为OR。

      X       Y       Z
   ┌────┬──────┬────────────────────────────┐
1 │分店代码│ 产品代码 │销量合计                               │
   ├────┼──────┼────────────────────────────┤
2 │ A002 │ C001 │=SUM(IF(($A$2:$A$15=$X2)*($B$2:$B$15=$Y2),$D$2:$D$15,0))│
   └────┴──────┴────────────────────────────┘
   ¤¤¤很关键的一点，当此公式输完后，不能直接按回车，要同时按住Ctrl+Shift,再按回车，这样，数组公式才生效果，对所有的

数组公式是一样的。

二、条件格式

有时候，我们可能要对一些数据突出显示，但这些数据可能是经常变化的，我们不可能每次都去设置一次格式，很烦人，因为我们是

懒人嘛。
比如，在上面的销售表中，要把没有小于12的所有单元格都涂成红色，可能D列是由公式生成的，别的地方变化就会影响此列的变化。
首先，我们选择D2

14（还记不记得在Excel操作技巧中讲的）,点击菜单："格式"->"条件格式"，在对话框中选择条件，第一个下拉

框中选择"单元格数值"，第二个选小于，在输入文本框中输入12，就是说单元格数值小于12,设好后，点对话框中的"格式"按钮，这样，设置任

何格式都可以，这里我们选"图案"，选中"红色"，按确定，表中小于12的单元格都变红了。
其实这个很难，有时可能要用到相对引用（就是类似于$B2:B$15这样的），就会很复杂，这里就不讲了，修行还得靠个人。

三、数据有效性

  此功能主要用于两方面，1、限制用户的输入，2、提高用户的输入速度

  1、有时再收集数据时，可能对输入数据的范围作一个限制，比如，人的年龄不能是负数，也不能太大（比如999），还有，比如身份证的

号码位数也是固定的，等等，这样，用数据有效性这个功能就很有用了。
  1)打开菜单数据、有效性，就可以看到了。当然，你要事先选择你要限定的区域，假设A1:A10。
  2)我们现在就拿年龄打个比方，在 "设置" 、"有效性条件"中选择充许条件为"整数" (年龄很少说小数的)
  3)"数据"中选择 "介于"(大家也可以看看有还其它的)
  4)在"最小值"中输入"1"，在最大值中输入"500"(我想除了神话人物，没人能活500岁吧，张三丰都没这么猛)，如果一定要用户输入，则

把"忽略空值"前的小勾去掉
  5)设置好后，这时实际上A1：A10已经被限制只能输入1--500之前的数了。但是我们可以把事情做得更漂亮点，毕竟EXCEL自带的提示让人

很不好懂。
所以现在我们选中"出错警告"，"输入无效数据时显示出错警告"肯定是要打勾的。注意样式，有三个可选：停止、警告、信息，我们选

反停止吧（其它的大家可以自己试试）。"标题"输入："出错啦！"，"错误信息"输入："输入的年龄不对"。
"输入信息"的设置类似，有何作用大家一试便知。
  6)好了，按下确定，这时，如果你在A1:A10的任何一个单元格中输入一个值（汉字也可，当然它会提示出错了）,如果输入不满足刚才设

定的条件，就会弹出一个对话框，大家快试试吧。

  2、有时，可能有很多数据要重复录入，比如某某分公司，是不是，很烦是不是，所以假设我们要在A列中输入不同的分公司，分公司包括

：深圳分公司、福州办事处、上海分公司、成都分公司。

1)事先要输入分公司的列表，在H1:H4中输入四个分公司的列表。
2)选择A列，打开菜单数据、有效性,在设置中，充许"序列"，来源中输入："H1:H4",当然，你可以直接输入："深圳分公司,福州办事处

,上海分公司,成都分公司",请注意逗号为半角，也就是在英文状态下的逗号。按确定就可以了.
3)现在看看A列，选中A列中的任何一个单元格，就可以看见在它的旁边有一个下拉箭头，按一下它，看到结果了吧，是不是很方便？
  其实，还有很多其它的用法，大家可以研究一下。

四、分列

  此功能主要是对数据进行分列，比如，Baan系统中，有时导出的文本文件用Excel打开后，数据全部是一列的。此功能就很有效
  1、固定宽度分列
1)我们就用上例中的分公司来举例吧，比如我们要把H1：H4中的深圳、福州、上海、成都给分离出为，单独一列；
2)选择H列，打开菜单中的"数据"、"分列",选择"固定宽度"，点下一步
3)在数据预览中，点击你要从何处分列，我们选择从"深圳"处分列，这是就出现了一个分界线，点"完成"就可以了
  2、分隔符分列
1)如果是打开文本文件，可以考虑用这种方法。所以开始时就要选择"分隔符号"。
2)点下一步后，分隔的符号有"Tab键"，"分号"，"逗号"，"空格",如是这三个都不是你想要的，你可以选择"其它"，然后在它的后面输

入你的符号，这里可以输入任何值，甚至是汉字。"Tab键"，"分号"，"逗号"，"空格"，"其它"可以同时选。
3)按下完成，就可以看到效果了。

真的很棒。我才知道那个双击填充柄的妙处。填充柄实际就下右下角的那个黑点。

我一般用的是word

不过妈咪经常用``

so顺便贴过来``````

以前上课有学过，不过都快还给老师了，都没实践`````

欢迎光临空网论坛 (http://bbs.kongweb.net/)

作者: 好想飞 时间: 2005-5-22 00:12