注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

孔唯妍

用我们的智慧开创出属于我们的时代。

 
 
 

日志

 
 

Excel不起眼技巧也有大作用  

2015-07-26 19:55:09|  分类: 电脑常识 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
技巧一:巧妙解决Excel的日期格式问题

  要想在A列输入类似于“20140518”这样的数字时,Excel能够自动转换为“2014-05-18”的日期格式,但即使打开“设置单元格格式”对话框,选择“自定义”,将类型设置为“yyyy-mm-dd”,但仍然无法得到正确的日期格式,而是显示为类似于“########”的格式,这是怎么回事呢?

  究其原因,如果不是Excel所规定的日期范围内的数字,那么不会被Excel识别为日期,因此只有首先将相关数值格式化为日期格式,然后才能转换为Excel能够识别的日期。如果你不想使用辅助列通过公式进行转换,那么可以添加“####-##-##”的自定义类型(见图1),以后输入“20140518”就可以得到“2014-05-18”的日期格式。


Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 
  技巧二:按需抓取单元格中的多行内容

  同学传过来一份表格,这里的单元格都包含了多行内容,各个单元格的第一行是姓名,最后一行的逗号后面是国家,现在她希望将姓名、国家这些信息提取出来,除了手工操作的方法外,有没有简单一些的方法呢?

  相信大部分朋友首先想到的是使用公式,但公式显然相对比较麻烦,其实如果你使用的是Excel 2013,那么可以选择更为简单的快速填充:首先在B1单元格手工输入A1单元格的姓名信息,切换到“数据”选项卡,选择B1:B5单元格区域,单击“快速填充”按钮,即可快速提取相关信息;继续在C1单元格手工输入A1单元格的国家信息,选择C1:C5单元格区域,单击“快速填充”按钮,很快就可以批量提取国家信息,最终效果如图2所示。

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧三:从不规则数据中提取指定数据

  同学前来求助,她希望从一份不规则的数据中提取指定数据,希望将“税额:”后面的数字从每个单元格中提取出来,手工提取固然可行,但工作量相当大,有没有简单一些的方法呢?

  在B2单元格输入公式“=--MID(A2,FIND(“税额”,A2)+3,FIND(“认证”,A2)-FIND(“税额”,A2)-3)”,MID函数表示从文本字符串指定的起始位置返回指定长度的字符,使用时涉及3个参数,“A2”表示需要切割的字符串,“FIND(“税额”,A2)+3”表示从“税额”后面第3位开始截取,第3个参数“FIND(“认证”,A2)-FIND(“税额”,A2)-3”表示截取几位,“--”表示修改数据格式,此时会得到“10427.31”的结果,双击填充柄即可获得相应效果。

  使用公式虽然可行,但上述公式对于初级用户来说,可能还是过于麻烦了一些。如果你使用的是Excel 2013,那么可以利用快速填充实现上述需求:

  经过分析,发现这里的数据还是有一些规律,例如需要提取的数据都是在“税额:”的后面,最终数据的后面都有空格字符,因此我们可以直接提取:在B2单元格手工输入“10427.31”,选择B2:B15单元格区域,切换到“数据”选项卡,单击“数据工具”功能组的“快速填充”按钮,很快就可以获得图3所示的效果,是不是更为简单?当然,如果最终数据的前后是其他的规律,也可以同时提取一些其他的字符以作区别,最后替换即可。

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧四:快速找出前10名的最小值

  同事前来求助,需要统计出一列数据中最大的10个数据中的最小值,常规的方法是排序之后进行手工筛选,操作相对麻烦,有没有简单一些的方法呢?这里以统计D列数据为例进行说明:

  如果是Excel 2013,则可以利用条件格式解决这一问题。切换到“文件”选项卡,在“样式”功能组选择“条件格式”功能,在下拉菜单依次选择“项目选取规则→前10项”,如图4所示,此时可以直接“查看”其中的最小值,不过如果该列数据比较多,手工“查看”可能会比较麻烦。

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧五:一键完成行列的同时冻结

  很多时候,我们需要对Excel工作表的某些行和列进行同时冻结,常规的操作是切换到“视图”选项卡,接下来点击“窗口”功能组的“拆分”按钮完成拆分,选择需要冻结的行和列,点击“冻结窗格→冻结拆分窗格”,操作比较麻烦,而且还容易出错。其实,我们有更为简单的方法,这里以冻结三行、两列进行说明:

  事先不需要作任何操作,切换到“视图”选项卡,选择C4单元格,点击“冻结窗格”按钮下面的倒三角按钮,如图5所示,在这里选择“冻结拆分窗格”,即可对三行、两列的数据进行冻结。如果需要对其他的行或列进行冻结,可以按照类似的步骤进行操作。

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧六:巧妙实现空值对应单元格的颜色填充

  最近遇到一个Excel方面的问题,要求将B列单元格显示为空值的A列单元格自动填充相应的颜色以示区别,手工操作显然是相当麻烦,因此考虑另觅他法:

  选择A1:A7单元格区域,单击“样式”功能组的“条件格式”按钮,从弹出菜单选择“新建规则”,打开“新建格式规则”对话框,规则类型选择“使用公式确定要设置格式的单元格”,公式使用“=B1=""”,单击“格式”按钮,打开“设置单元格格式”对话框,切换到“填充”选项卡,在这里设置填充颜色,返回“编辑格式规则”对话框,检查无误之后关闭对话框,随后就可以看到填充效果。

  如果对公式不太熟悉,也可以利用筛选、定位的方法实现,在“编辑”功能组的“排序和筛选”菜单下选择“筛选”,单击B列的倒三角箭头,如图6所示,在这里勾选“(空白)”复选框,现在就可以将A列填充颜色,最后只要将筛选条件设置为“全部”即可。

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧七:利用定位条件快速转移同一列的中文内容

  手头有一份Excel文档,其中都是中英文参照的术语,但由于是在同一列显示,看起来很不顺眼,现在希望将英文对应的翻译对应显示在“中文”一列,如果采取纯手工的方法,操作起来显然相当繁琐。有没有简单一些的方法呢?

  选中B2单元格,输入公式“=A3”,同时选中B2:B3单元格区域,双击右下角的填充柄,快速复制公式,此时可以获得图7所示的效果;保持B列的选中状态,按下“Ctrl+C”组合键进行复制,原位粘贴时请选择“值和数字格式”;继续保持B列的选中状态,按下F5功能键,打开“定位条件”对话框,将定位条件设置为“空值”,确认之后可以选中B列所有的空白单元格,右击任意一空值单元格选择“删除”,此时会弹出“删除”提示框,请选择“整行”删除即可。

  完成上述操作之后,可以发现英文、中文已经分别显示在不同的列,是不是很方便?

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧八:利用“合并计算”完成跨工作表的求和计算

  同事拿过来一份Excel工作簿,要求在“汇总表”对1月、2月、3月、4月的相关产品的产量进行统计,逐表进行手工求和的方法虽然可取,但工作量实在太大,而且也容易出错,当然也可以采取引用的方式进行求和,但操作同样繁琐。有没有简单一些的方法呢?

  其实,我们可以通过“合并计算”的方法获得各产品产量的汇总求和:

  选中B5:C11单元格区域,切换到“数据”选项卡,单击“数据工具”功能组的“合并计算”按钮,打开“合并计算”对话框,函数自然是选择“求和”,点击“引用位置”右侧的选择按钮,分别在1月、2月、3月、4月工作表选择引用位置,需要指出的是,选择时必须包括“产品”、“产量”两个标题列,如图8所示,最后不要忘记勾选“首行”和“最左列”两个复选框。

  检查无误之后,点击“确定”按钮,很快就可以获得最终的求和结果,是不是很方便?当然,除了完成求和计算之外,我们还可以完成计数、平均值、最大值、最小值等其他的计算,此时并不需要重新开始,只要再次点击“合并计算”按钮,打开“合并计算”对话框,在这里直接重置相应的函数就可以了。

Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 

  技巧九:利用函数实现字母编号的自动填充

  我们知道,在Excel可以实现数字编号的自动填充,但遗憾的是Excel并未提供字母的自动填充功能,如果纯粹采取手工填充的方法,既麻烦而且也不现实。其实,我们可以利用两个函数解决这一问题:

  首先请选中需要填充位置的空白单元格,例如A1,在公式编辑栏填入计算公式“=CHAR(65+COLUMN()-1)”,“CHAR”函数的作用是根据本机中的字符集返回由代码数字指定的字符,“65”表示字母A的ASCII代码,使用CHAR函数读取A1单元格时,生成的字母为A,如果字母编号不是从A开始,那么请更换为其他的ASCII代码,“COLUMN”函数是用来返回引用的列号,然后向右拖拽填充柄至指定位置,即可自动填充相应的字母,效果如图9所示。

  如果需要实现竖向填充,只要将公式更改为“=CHAR(65+ROW()-1)”即可。如果需要填充小写的字母,只要将ASCII代码更改为97就可以了。

  补充:如果不清楚ASCII代码,可以从Word或Excel的“符号”对话框查询。
Excel不起眼技巧也有大作用 - 12期 孔唯妍  - 孔唯妍 廊坊师范学院信息技术提高班十二期
 
  评论这张
 
阅读(1845)| 评论(119)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017