excel部分字段相同模糊匹配_你-早该这么玩 Excel
一本工具书让你从“表哥”菜鸟变成一个有“表妹”的“表哥”。
之前谈Excel色变,公式、统计图、数据透视表等等,让我是抓耳挠腮,看到表格我就怕。
一次领导的推介,让我们抽时间读读《你早该这么玩Excel》这本书,可能改善一下。
该书提到了“懒蚂蚁效应”,大部分蚂蚁都很勤快地寻找、搬运食物、少数蚂蚁却整日无所事事、东张西望,在企业中也存在此现象:一些人平时看上去好像无所事事,但他们花很多时间去思考和提炼,所以往往平时不显山不露水,但关键时刻往往能挑大梁。当你只有20个数据时,觉得Excel并不重要,但当你要处理2000个甚至上百万个数据时,就应该学会用Excel“偷懒”。最近在整理一份数据表,一开始特别轻松,逐渐发现数据越来越多,需要处理的东西也越来越多,比如需要汇总数据,批量添加行列,不能允许格式错乱。一大堆的问题涌现了出来。经过读了这本好书后,它给我带来了很多技巧,很多思路,从手忙脚乱变的从容对应。
1. 源数据表
- 一维表格、首行首列默认是标题行,及数据的字段名称,标题可写在工作簿或工作表。
- 字段顺序(及数据录入顺序)要与工作顺序保持一致。
- 数据要有连续性,不能人为割裂开,因为Excel根据行列的连续性判断数据的关联性
- 只对数据进行录入,不进行统计
Excel工作步骤:1.数据录入;2.数据处理;3.数据分析。
对应的操作步骤:1.输入数据;2.整理数据(函数等技巧);3.数据分类汇总
对应的工作表:1.源数据表;2.源数据表或其他新建工作表;3.分类汇总表 - 同一种属性数据记录一列,如果明细数据有一个属性不同,就应该录入为两条数据tips: 批量录入数据,所用快捷键“Ctrl + Enter”,首先按住“Ctrl”键选中多个单元格,在最后一个单元格录入数据,录入完毕,按组合键,完成
- 不要用“合并居中”,会打乱Excel的内在逻辑
修正方法很有意思,技巧性很强首先全部选中,点击“合并居中”按钮,拆分合并的单元格,在按“F5”键,打开定位功能,空位条件为空值,在单元格输入公式“=上一个单元格”(如光标所在单元格为B4,就在这个单元格录入=B3),按下“Ctrl + Enter”键,这样录入的数据为公式,复制选择性粘贴为数值,完成。 - 数据不能有缺失,不能有空格,没有数据可以用“0”表示;而且设计表格的时候要仔细分析所需的字段,属性,不能有缺失。tips: 数字变换为中文大写形式,选中数字单元格,设置单元格格式(Ctrl + 1),数字标签下的“特殊”,“中午大写数字”,完成,但是单元格的实质还是阿拉伯数字。
- 属性要单一,不要记录复合属性tips: "数据-分列",可以将一列数据分成几列;合并数据用 “&” 运算符,对多个单元格进行合并。
2.“天下第一表”tips: 选中连续区域的快捷键 “Ctrl + Shift + 向下方向键”,
- 保证表格的安全
单元格数据录入安全:单元格数据有效性设置,快捷键“Alt+D”再按“L”,可保证单元格数据录入的正确性。 - 利用参数对数据进行匹配
主要是VLOOKUP的使用,该函数规定,匹配信息的主键必须位于第一列。
该函数有四个参数:
用什么找————设置匹配信息的主键,相对引用
去哪里找————‘工作表123’!$A:$D,表示去工作表123中A到D列中去匹配。$表示相对引用。
返回第几个值————2表示返回第二个值
精确还是模糊找————False或0为精确查找,True或1为模糊查找
书中例子:
=IF(LEN($E2)=0,“”,VLOOKUP($E2,'参数表(供应商信息)'!$A:$D,COLUMN(B1),0))
翻译:当E2单元格无空时,返回空,否则返回VLOOKUP函数结果。注释:$为相对引用,COLUMN为单元格所在列号 - 表格美化
文本对齐——垂直方向居中对齐,同列数据水平方向对齐方式一致。
文本大小——对于数据明细,10号字比12号字更精致。
字体——中文宋体,英文Arial或Times New Roman。
单元格边框——同类数据采用相同边框,禁用大面积粗边框或虚线边框。
色彩——不宜超过三种。
简化数据区域——待录入数据区域最好不填充颜色。
数据区域——手工录入、复制粘贴、公式不同区域用不同颜色区分。
字体大小——录入和经常查看的单元格字体稍大,公式生产的明细数据字体稍小区分关注重点。
边框——用虚线边框弱化明细数据和非重点数据,用粗线边框分隔录入方式不同的数据区域。
工作表——以不同的工作表标签颜色区分汇总表、源数据表、参数表。 - 输入长文本技巧
利用“自动更正选项”,将长文本的代号更正成文本本身。代号最好用三个英文字母组合,可以将常用长文本设置好的替换列表打印出来,随时查看。
设置好以后,word和ppt也可以使用。tips: 定位功能(F5),可以定位表格中设置定位条件的所有单元格,如选中所有空格。
3.图表
- 观点:Excel中的图表用来说明数据的意义,不用过多的美化,只要最基本的就行,直观、准确。PPT中说明观点的图表才需要考虑过多的美化问题。
- 分析数据是用来指导行动的,如果只是单纯的整理数据,那么数据是没有用处的,以指导行动为目标来对数据进行必要的分析。
- 图表类型
作者推荐三种图表:
饼状图——对比份额,分布
柱状图——比较数值
带点折线图——表示趋势
图形都推荐使用二维最简单直观的,慎用3D或其他复杂图形。 - 图表美化
参考标准:干净、原配(默认图表类型)、大小(图大字小)、繁简(简洁为王)
如需美化只需,双击需要美化的地方,设置选项就会出现。
tips: Excel图表粘贴到Word和PPT中,粘贴时可选择“选择性粘贴,如果勾选“粘贴链接”,Excel表格更新时,Word的图表也会更新。但是需要注意,直接“Ctrl+V”粘贴到word中时很安全,只是张图片,但是粘贴到PPT中时将会把所有的源数据粘贴到PPT中,无意中泄露了很多商业秘密,(粘贴为链接时,不在本机,无法打开源数据;粘贴为图表对象,就会附带源数据,即便不在本机也可以打开)
4. 职场经验
- 赢得同事认可
人的形象时逐渐积累的过程,要认真对待每一份工作,珍惜每一次沟通机会。
善于总结,并乐于分享,这也是展现自己工作的机会。
保存好报表,分类,备份。
真诚 - 赢得老板的认可
比吩咐的多做一点;
汇报进度并按时完成;
要主动汇报情况,明确完成时间时,如有异议,要当场提出。未提出,就要克服困难,尽力按时完成。邮件的重要性:
不要有错别字,
如果有附件,可以先添加附件,以免最后忘了添加。
如果忘了发附件,要补发邮件,内容或标题区别于第一封,并电话确认。
如果附件比较重要,可以在邮件正文中对邮件进行总结描述。
汇报时,让老板做多选题,提出多个解决方案,并给出建议。