posts - 297,  comments - 1618,  trackbacks - 0

日期:2014/5/31

文:阿蜜果

         将使用Excel常用功能的使用技巧记录一下,以备大家和我自己的不时之需。感觉Office套件有几个产品还是非常优秀的产品,简单易用、功能强大,例如ExcelWordPPTVisio。本文内容将不断补充。

    【说明】笔者使用Excel版本为Excel 2007。

1、某列设置下拉框

   选择某列的某个单元格(例如B1单元格)后,点击菜单栏的“数据”->“数据有效性”,如下图所示:       
       

         在弹出的窗口中在“允许”的下拉框下选择“序列”,在“来源”中输入选项,多个选项以英文逗号(“,”)隔开,例如本实例的下拉框有“幼儿园”、“小学”、“初中”、“高中”四个选项。如下图所示:
      

         设置完成后点击“确定”按钮,完成设置。效果如下图所示:
      

         若想让该列的下面若干单元格都延续该下拉框,可在选中该单元格后,在左下角出现实心十字图形时,往下拉到想要的行即可。

2、隐藏和显示一列或多列

         当表格列数比较多时,可选择性的显示一些重点列,而将某些不重要的列隐藏。例如隐藏如下表格的“所属县区”列,选择该列的头部(E所在地方)后,点击右键,选择“隐藏”后,该列将被隐藏。如下图所示:
     

         隐藏E列将只显示DF列,若要将隐藏的列E显示出来,可选中其相邻列DE后,点击右键选择“取消隐藏”即可。

3、删除重复行,留下不重复的行

         有时候需要找出某列存在重复的有问题的行,以便之后进行删除,留下不重复的行。为了实现该目标,可使用ExcelCOUNTIF函数。

         COUNTIF函数的作用,是统计在某个范围内,满足既定条件的单元格的个数。

   基本语法:=countif(范围,条件)

   如:=countif(A1:A10,1) 即在A1A10的范围内,单元格内容是1个数。还可以设置条件,如 =countif(A1:A10,">=5") 统计大于等于5的单元格个数,如果是字符或条件,必须加"" ,单独数字不用加。如:=countif(B2:B21,B2) 即在B2B21的范围内,单元格内容是B2的个数。如:=countif(B:B,B2) 即在B列范围内,单元格内容是B2的个数。

  若想找出B2列(学校名称)某个学校名称的行数量(=1表示不存在重复行,>1表示存在该行重复,数字表示同样学校名称的数量)。可在“学校名称”后加上一列“行数量”,双击C2单元格,输入公式:==countif(B:B,B2),即在B列范围内寻找值为B2的行的数量。而后选中该单元格后将鼠标移动到左下角,出现实心小十字时往下拉,让下面的单元格也使用该公式进行计算。结果如下图所示:
      

         若想将重复的行删除,留下不重复的行,可对该列进行过滤,选出C行大于1的数据删除即可。

4、寻找重复的行

         在上一节的实例中,不重复的行比较容易过滤,但若想找出存在重复的行,往往重复多少次会过滤出多少行,若想寻找重复的行,而且重复的行只留下一行,依然可以使用countif行数,但需要做出少许的变化。

         添加一列“前面行重复数量”,将鼠标定位到C3处,输入公式:=COUNTIF(B$1:B2,B3)。该公式的作用是在B3前面的B列单元格中寻找与B3单元格的值相同的行的个数。设置完成后,将C3单元格的公式下拉,让每个BX单元格都于其前的所有B列单元格比较,若找到重复行,则C3的值为在前面重复的个数。得出的结果如下图所示:
       

         若想找出重复行,而且只是留下一行,可过滤C列留下值为1的数据即可。

5、从另一个区域找出某个值对应的值

         若想在Excel中实现从某一列(例如“区域名称”列)的值从另一个表中找出该列对应的值(例如“区域编码”的值),可使用VLOOKUP函数。

         VLOOKUP查找目标查找范围返回值的列数精确OR模糊查找)

         例如想在上面学校表中添加一列“区域编码”,步骤如下:

    1)通过“所属县区”精确去另一个区域表中通过区域名称找到区域编码,可添加“区域编码”列(E列)。

         2)在准备区域名称和区域编码对应表,在J1K6范围内准备好区域名称和区域编码对应关系。

    3)将鼠标移到E2单元格,双击后输入公式:=VLOOKUP(F2,$J$2:$K$6,2,FALSE)。该公式表示使用F2单元格在J2 -> K6矩形区域内精确查找(最后一个参数为FALSE表示J列的值要跟F2精确匹配,若改为TRUE表示J列的某个值只要跟F2模糊匹配即可),若找到对应的值,则将对应行的第二列的值(即K列的该行的单元格)返回。

    4)选择E2单元格,将鼠标移动到左下角,待变成实心小十字时向下拉,结果如下图:
      

6、生成三维饼图

         若想得到学校区域分布图,可使用Excel生成饼图,例如学校数量分布:高新区(80)、锦江区(90)、青羊区(30)、金牛区(10)、武侯区(35)。

         Excel生成三维饼图的参考步骤参考如下:

   1) A1B6矩形区域设置“区域名称”和“学校数量”的表格。

   2) 选中A1B6的矩形区域后,点击“插入”->“饼图”,选择“三维饼图”,如下图所示

         初始的三维饼图如下图所示:
     

        3)若想在饼图上显示具体的数字,点击饼图区域,点击右键,选择“添加数据标签”,若想改变饼图的名称为“成都市学校区域分布图”,可选择“学校数量”,修改即可。修改后如下图所示:
      

        4)可在饼图区域点击右键,选择“设置数据标签格式”,例如可以勾选“类别名称”,在饼图显示区域名称,如下图所示:   
        
      实例下载地址:http://www.blogjava.net/Files/amigoxie/Excel常用使用技巧案例.rar

posted on 2014-05-31 11:04 阿蜜果 阅读(4659) 评论(1)  编辑  收藏 所属分类: 项目管理


FeedBack:
# re: Excel常用功能使用技巧备忘
2014-05-31 16:58 | 腌渍府
文章写的很不错!  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航:
 
<2014年5月>
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

      生活将我们磨圆,是为了让我们滚得更远——“圆”来如此。
      我的作品:
      玩转Axure RP  (2015年12月出版)
      

      Power Designer系统分析与建模实战  (2015年7月出版)
      
     Struts2+Hibernate3+Spring2   (2010年5月出版)
     

留言簿(255)

随笔分类

随笔档案

文章分类

相册

关注blog

积分与排名

  • 积分 - 2170379
  • 排名 - 3

最新评论

阅读排行榜

评论排行榜