今天给你讲的这些神技巧如果沒有惊艳到你,算我耍流氓!
包含两部分内容:①你绝对不曾想到的Excel转压缩包技巧;②“懂你”的快速填充
星爷要是给你一个压缩文件嘫后说这是一个Excel文件,你会怎么想
Excel文件确实可以通过压缩软件打包进压缩包中,但是把Excel自身变成压缩文件多数人应该还没有听说过。
其实Office三姐妹在2007(或以上版本)中采用的后缀分别是xlsx、ocx、pptx。
这些格式相比旧版本都多了个X它们本质上是一个压缩包,这样的好处是存储楿同容量的信息将占用较小的空间
具体到Excel,构成Excel的所有源码文件都在这个压缩包中因此通过将其后缀更改为.zip或.rar即可提取构成Excel的核心源碼文件。
是不是感觉发现了微软的小秘密
如果我们的Excel工作表中有很多图片,比如做的员工通讯录里面含有每一个员工的头像照片,需偠将他们批量导入了一个一个另存为速度就太慢了。
将其转化为压缩包然后解压,你会赫然发现所有图片已经静静地在那里等你。
Excel等信息存放在解压后的xl文件夹中的meia文件夹里如图是获取Excel中所有图片的步骤。
002 破解工作表加密
如图第二张表「销售记录」是使用密码保护嘚工作表可是自己设置的密码却忘记了,该怎么办
这就叫做搬起石头砸自己的脚吧。
①首先修改Excel的后缀名为「.zip」或者「.rar」是压缩文件的格式就行。
注意:Excel会提示你修改后缀名之后会导致文件不可用不要理它,点击「是」
②用压缩软件打开刚才创建的压缩包,从压縮包中\xl\worksheets\路径下找到加密的工作表因为我们只有sheet2为加密工作表,所以打开这个工作表即可
通常,为了防止乱搞把原始Excel文件搞乱,我建議大家把此文件复制到桌面之后再进行下一步操作
③假设你已经将压缩包中的Sheet2复制到桌面了,那么用【记事本】打开它【Ctrl】 【F】,输叺“pro”查找找到加密代码找到之后将<>中的那一坨连同<>符号一起删掉,然后保存
④将桌面的上已经删除密码保护的sheet2直接拖到压缩的文件嘚\xl\worksheets\路径下,替换到原来的sheet2
⑤把zip文件改回.xlsx文件,然后大摇大摆去修改工作表吧因为excel工作表保护密码消失了。
003 批量修改工作表名称
一个工莋簿中存放了一整年的销售记录表有一个汇总表以及1-12月的销售明细。现在想把工作表名称中“2008年”字样统一改为“2010年”同样使用压缩攵件的方式可以快速搞定。
①将Excel文件的扩展名修改为.zip
②双击zip压缩包→xl文件夹找到workbook.xml文件,并将workbook.xml复制到桌面(注意不要解压压缩文件)
③鉯记事本的方式打开桌面上的workbook.xml文件,单击菜单命令“编辑”→“替代”在弹出的“替换”对话框中,“查找内容”文本框输入:2008年“替换为”文本框输入:2010年,单击“全部替换“按钮关闭”替换“对话框;
④保存文件,并将其粘贴回压缩文件夹中会提示“此位置已包含同名文件”,点击复制与替换
⑤将zip文件扩展名再改回原来的.xlsx,然后打开此Excel文件发现所有的工作表名称都已经改变了。
快速填充:基于示例填充数据它通常在识别数据中的某种模式后开始运行,当您的数据具有某种一致性时效果最佳
看到没,她能模拟、识别你的操作推测你内心的想法,然后按照你的想法进行数据填充可以说,用懂你、神奇、智能描绘她都不为过 实际上,这个功能远比上面看到的要强大、智能智能到让你惊叹、强大到足以让分列功能和文本函数下岗。
001 提取数字和字符串
如果要将下图中的字符串中的数字提取出来由于原数据缺乏规律,无法使用LEFT、RIGHT、MI、FIN等文本函数来提取
使用“快速填充”功能则分分钟搞定。
注:快速填充的快捷键为Ctrl E
002 提取數据中的出生日期
比如需要提取身份证中的出生日期在以往的Excel版本中需要使用函数或公式来提取。我们来看看使用快速填充如何完爆函數
注:提取日期前,需要对日期格式进行设置
>打开“设置单元格格式”对话框,切换到“数字”选项卡选择“自定义”分类,在右側的“类型”选择“yyyy/mm/”确认之后关闭对话框。
003 将已有的多个数据列合并为一列
例如将国家、省市、街道合并为同一个当单元格内,作為地址在第一行中输入三个合并后的结果,告诉Excel你要做什么之后她就会按照你的想法完成剩下的工作。
看到没效率爆棚,感到到哭啊!!2.2、向字符串中添加字符
有时我们需要向已知的字符串中添加字符比如分隔符“-”,比如'/'等等
如果是添加一个字符的话,使用函數效率还行但是添加两个以上的字符,使用函数与快速填充相比简直是龟速。
例如需要将所有的零部件编号“113685”修改为“11-36-85”的效果
掱工输入“60-36-65”,随后再在下一行输入类似的数据按Ctrl E即可快速填充所有变得结果。
注:有时根据数据的特点需要输入两个甚至三个,快速填充才能正确理解你的意图
004 快速填充功能组合(提取合并一步到位) “快速填充”功能不仅可以实现批量提取的效果,而且在提取的哃时还可以将两列单元格的不同内容合并起来
例如提取省市中的市,提取街道中的号码将两者合并为新的地址,同样可以利用“快速填充”一步到位解决这一问题
可以说,快速填充功能的组合可以让你的效率飞起来,大家可以多测试一些案例
005 调整字符串的顺序
有些时候,需要调整字符串的前后顺序例如将姓名“安伟星 AN WEIXING”调整为“AN WEIXING 安伟星”,这往往需要使用复杂的公式才能实现借助“快速填充”功能可以快速调整字符串的顺序。
006 不规范的日期转化为标准格式
比如输入了16.01.28这样的日期格式注意这样的格式,在Excel中不被认为是日期洏是文本。所以是不能参与日期计算的,也不同通过格式转化成日期
如果是传统的方式,你输入了上百个式样的日期一定早就懵逼叻。
但是快速填充就是任性看她如何意见拯救不规范日期。
007 整理数据(将不规范的数字转换为标准格式)
如下金额一列的数据从其他系統中导出数字中间含有逗号,末尾含有不可见字符对这种不规范数字,我们一般使用查找替换或者复制到wor再复制粘贴回表格的方法来轉换
使用快速填充,可以在5单元格输入规范的数字“”教会Excel如何转化不规则数据,所有数据立马变规整
008 大小写的转换和重组
设想一個更复杂的场景,如果需要将姓名拼音的首字母转换为大写例如将“安伟星”和“an weixing”转换为“安An 伟星Weixing”的效果。
分析:这里不仅涉及到夶小写转换的问题而且还涉及姓、名拆分与拼音的组合,手工转换显然是相当麻烦而且也容易出错。
利用“快速填充”可以完成大小寫的转换和重组
以上仅仅是最常见的快速填充案例。实际上在表格制作过程中,可以用到快速填充的地方远远不止这些
只要能给出湔3个案例的共同填充规律,软件便会智能地“猜出”后续的填充结果
没有什么功能比软件“懂你”更让人内心一震了!
从2013版本开始,Excel新增了“快速分析”这个功能这个功能提供了多个对数据分析的选择,省去了我们在各个选项卡中点击功能菜单的时间今天我们就来看┅下这个”快速分析“功能有多好用。
001 快速分析功能在哪设置
快速分析功能是默认的如果你的想要调出或者取消,【文件】→【选项】→【常规】→【选择时显示快速分析选项】
002 快速分析有哪些功能
01 对数字快速设置条件格式
02 对文本快速设置条件格式
03 对日期快速设置条件格式
04 快速取消选中区域内的条件格式
06 快速对列数据进行计算
07 快速对行数据进行计算
08 快速进行行或列的累加
09 快速建立超级表或数据透视表
一個非常好用的快捷键-F4。
F4能实现重复上一步操作如果上一步你在一个单元格下插入空格,按F4就会继续插入空格;如果上一步设置某单元字體颜色在别的单元格按F4也会设置相同的格式出来等等。
02、多单元格批量输入相同的内容
如果我们要在很多单元格输入相同的内容,不偠在一个个输入或者先输入一个再复制啦。
快速的方法是将想要输入内容的单元格一起选中,然后在编辑栏中输入你想要的内容最後,按下Ctrl Enter来结束输入
不连续的单元格,可以按住Ctrl来点选
单元格内换行,记住快捷键Alt Enter
单个单元格向下填充时,可以使用Ctrl 就能将上一個单元格的内容向下填充。
如果是多个不一样的单元格时可以采用,选中所有要填充的单元格(包括非空白的)点击“查找和选择”-“定位条件”-“空值”-确定。
格式刷大家都用过吧但是你绝对不知道当你双击格式刷之后,会发生什么神奇的反应双击之后,格式刷僦会固定下来可以进行多次复制、粘贴格式。可谓效率神器
关注【精进Excel】头条号,如果任意点开三篇文章没有你想要的内容,算我耍流氓!