【职场办公】Excel大神上分攻略

 【职场办公】Excel大神上分攻略

一、框架

数据思维：抓取数据，梳理数据，分析数据，驱动决策

搭建-》处理-》分析-》呈现

表格搭建:整体、一维表、规范

表格搭建【整体，一维表，规范】【五规范：字段设置应合理，空行空格莫乱加，数据格式合规范，合并格子要不得，拆分多表太多余】，

数据处理【检查，过滤，清洗】

数据分析【目的：分析现状，原因，进行预测】【工具：分类汇总，数据透视表……】

数据呈现【数据可视化：常规图标，迷你图，条件格式，power bi】

技巧：快速学到 沉淀【练习，时间积累，慢慢养成】——》技能

二、快速填充

填充1-10000

填充基本操作【填充柄拖拉，填充柄双击，选择-ctrl+r/d，填充-序列】

批量填充数字/日期【填充-序列，】

快速填充【提取，合并，特殊格式：通过前几个值学习矫正：ctrl+e】

内容重排【将一个格子的内容和多个格子的内容合并或分发：填充-内容重排】

自定义快速访问工具栏【增加，删除，顺序，导入/导出自定义文件】

三、行列操作技巧

快速调整行高列宽【行列字段间快速双击】

行列移动【选中-放到两列中间-出现黑色十字时-shift+拖动/ctrl+shift+拖动（拖动+增加）】

复制【ctr+cv】

增删【右键插入；ctr+shift+=||ctr+-；F4重复上一个操作；一次性加三列：选中三列+ctr+shift+=】

快速选择表格区域的四种方法【快捷键[选中单元格-ctr+shift+方向键];ctr+a]、点选[选中+shift+选中]、定义名称[选择整片区域-左上角框输入名称（不要数字和英文）]】

四、邮件合并【邮件-点击选择收件人-使用现有列表-exel文档-点击插入合并阈-完成并合并】

五、文本数据格式

规律：1、输入超过11位时，自动变为科学计数法；超过15位，15位后面的数据会用0替代。2、区分：文本默认左对齐，数字默认右对齐（包括日期）。3、数字可以计数计算（右下角），文本只能计数。

填表格前一定要设置数据文本格式！公司导入的很多都是文本格式

数据格式1900-1-1；1900/1/1 ctr+H 查找替换

如何设置数据格式【日期：右键-设置单元格格式-日期；ctr+1-日期；菜单栏下拉按钮。注：“50双”可能是数字格式，但用了单元格格式设置自定义】【文本：选中单元格-先设置文本格式再填；英文状态带引号+输入】【导出的钉子户文本数据转化：数据-分列-下一步-下一步（常规or文本or日期）-确定（这个也可修改错误的时间格式）；选择性粘贴法：选择空单元格-ctr+c-选中文本-右键-选择性粘贴-运算加-确定】

数据格式常见类型

时间快捷键【日期快捷键：ctr+；时间快捷键：ctr+shift+；=today（）；自定义日期格式：ctr+1】

自定义数据格式

0是文本占位符，它可以显示有意义的数字，然后将不足尾数用0补齐

加单位：选中列，在格式后加"双"。【任何中文字符都要用英文“”引起来】

手机号分段：000 0000 0000

前后缀：选中-"前缀"@"后缀"【@表示单元格所有字符】

隐藏数据：选中-;;;【三个英文状态下的分号】

快速输入性别：[=0]'男'；[=1]'女'

中文数字转换：国产wps ctrl+1 -特殊

六、定位&查找&替换

定位-F5/ctrl+G

定位两列不同：选中两列-ctrl+\【以先选中的一列作为参照物；仅限于每行数据一一对应】

定位简介：常量【输入值】、公式【有=】、空值、对象【表格中插入的图片、形状】、可见单元格【相对隐藏单元格，即肉眼可见的单元格。不复制隐藏列，只复制隐藏单元格：F5-可见单元格-ctrl+c；选中表格-alter+;-ctrl+c】

定位批量填充：【选中空格后-输入-ctrl+enter】【对合并单元格：取消合并-居中-定位【ctrl+G】-空值-编辑栏输入=[该区域第一个有值的单元格]-ctrl+回车】

批量删空行：选中整张表格-定位【ctrl+G/F5】-空值-空值上右键-删除-整行

查找数据不同【定位法：ctrl+\；条件格式发：选中两列-点击条件格式-点击突出显示单元格规则-重复值-确定；函数法：=countif（对比列，对比值）-回车-下拉填充；选择性粘贴法：（对两个较大的数据表格；要备份）选中一个表格的数值区-ctrl+c-选择另一个表格的第一个表格-右上角粘贴-选择性粘贴“减”】

查找和定位的区别：定位使用本身属性，比如常量；查找更灵活，可以设置某些关键词【内容符号格式】，

带颜色或者特殊符号得到单元格要靠查找ctrl+H【查找替换】

删除空行空格：alter+1+0【换行符的快捷键】/空格键 ，替换：不输入任何值

精确查找：ctr+F【底下有查找对话框，shift选中所有】

模糊查找：通配符，*代替任意多个字符；？【英文半角】代表一个字符。查*？在前面加小波浪号【shift+1前面的键】

格式查找

七、排序&筛选

排序

常规排序【排序内设置：单条件、多条件、笔划】

字符数排【辅助列：len（单元格），右下角黑色十字双击】

自定义排序【排序-次序-自定义序列】

门牌号排序【两列辅助列：复制门牌号到新列-数据-分列……】

制作工资条

排序删空行【排序后空行集中在表后，可以一起删掉；counta（单元格区域）：计算非空格数目，双击填充，注意：由于空行把表格隔开，双击不能一次性完成填充，可以使用点选法，再ctr+D，此时空行辅助列1值为0。再对辅助列2编号，方便后续恢复数据顺序。对辅助列1降序，删除后面集中的空行，再对辅助列2排序，恢复原本的数据顺序。】

筛选

常规筛选【文本、数字、日期】

精确筛选【搜索框,用英文双引号将跑鞋引起来】、模糊筛选【搜索框直接搜；通配符：*？（数据也可以筛选）】

高级筛选

筛选|隐藏重复值【开始-条件格式-突出显示单元格规则-重复值-数据-高级筛选-勾上不重复的记录 】

筛选删空行【选中所有筛选单元格-打开筛选-选上空白行删去-取消筛选】

八、数据验证功能【格式：限制日期、长度[=or(len($)=15,len($)=18)]、空格[=len(C2)=len(substitute(C2," ",""))]、重复值[数据验证-自定义-=countif（a列，A2)=1]；内容：下拉菜单[数据验证-允许（序列）-来源（英文逗号隔开；选中表格）[来源如果要新增：选中来源的任意单元格-ctrl+T-选中来源表格 -直接在下面加]】

二级下拉菜单：选中数据源表格-ctr+G(定位)-常量-公式-根据所选内容创建-首行【可以通过名称管理器看他们的意义】-选中空单元格-数据-数据验证-序列-来源（框选）-填上一个内容-选中另一个空单元格-数据验证-序列-来源（=indirect（$A2))

九、打印【ctral+p；页面布局】

设置缩放打印

选项卡，命令启动器【右下角小箭头】，命令组【一组菜单】，一页纸打印，设置打印缩放比例

跨页打印标题【页面布局-……-打印标题（鼠标选完之后要按回车）】

打印选定区域

打印前/后n行【框选区域-页面布局-打印区域-设置打印区域-ctrl+p；ctr+p-设置】【如果不要中间的打印成一张表，就隐藏：选完中间的后在最左边右键-隐藏-页面布局-打印区域-设置打印区域-ctrl+p，打印成两张表就不需要隐藏-只需要ctr选择后直接打印选择区域（但是没标题，记得加标题）】

打印多个表格区域

只打印图表【选中图表-ctr+p】【不打印图片：选中一张图片-ctr+a（同时选中同一类型对象即图片）-在任意一张图片上右键-设置图片格式-属性-打印对象的√去掉-ctr+p】

设置分页打印【分页符打印：视图-分页预览-鼠标定位框外-页面布局-分隔符-插入分页符（可拖动）-ctr+p-视图-普通（退出预览模式）；分类汇总打印：先鼠标定位到表格里的任意单元格-数据-分类汇总功能-设置其他的后-勾选每组数据分页（记得加标题）】

打印页眉页脚【设置logo、文件名、公司名：命令启动器-页眉页尾-自定义页眉；设置页脚的奇偶页不同：命令启动器-页眉页尾-自定义页脚（左右都要设置：奇数页在右下角，偶数页在左下角）-“代码”第&[页码]页/总&[总页数]页“效果”第n页/总m页】

同时打印多表：sheet处：ctrl键点选；shift键连续选

对照打印【可以直接分开装订，页码123123123而不是111222333】

打印批注【首先显示批注[开始-查找和选择-选择窗格-全部显示]-】

不打印填充色【页面布局-命令启动器-工作表-批注和注释（如工作表上所示（仅注释））-单色打印-确定】

打印不变形【另存为-文件格式PDF格式】

十、函数

三种引用方式

相对引用：公式随着单元格的递进而改变它的公式参数

绝对引用：加了$锁定，选定单元格-F4（行列都锁定了，第二下不锁行）

函数（F1：帮助；F2：函数引用了哪些区域）

一键求和：选中要填的单元格-alt+=

合并计算：（不用一一对应）数据-合并计算-添加计算区域

条件求和：sumif（条件区域，条件，求和区域）【条件要加上双引号或者用单元格、区域绝对引用、可用通配符查找】

sumifs

sumproduct（区域1,区域2,区域3）求乘积【区域大小必须相等】

sumproduct（条件1*条件2*……*求和区域）【区域不能整列引用】

subtotal（参数，计算区域）筛选后求和

时间函数

博学网课程目录：

理念篇:

正本清源学表格

做表效率低下，因为方法不对

掌握数据思维，告别加班返工

技术篇:抓住关键提效率

掌握这些操作，快的不是一点点

极限提升效率，从此新手变高手

你不可不知的数据格式

建造数据大楼，从打好地基开始

精准搜索的技术:定位和查找

批量操作让你的效率进入2.0时代

数据有条不紊:排序和筛选技巧

告别凌乱的表格，数据随心挑着看

数据在你掌控:巧用数据验证

规范数据格式，让你省时省力省心

完美打印没烦恼:不要输在最后一步

摆脱打印恐惧症，这五招让你很能“打”

函数篇

处理数据不费力

它是个纸老虎:十分钟摸透函数

懂了这个符号，函数就会了一半

花式求和玩的溜:SUM还有PLUS版本

数据求和汇总一站式搞定

时间计算不糊涂:让你做时间的主人

快速计时，老板对你刮目相看

逻辑推理不犯晕:IF你一定要学

学完这节，再长的函数你都不怕了

查找匹配来牵线:VLOOKUP一定要学

十秒搞定一万行数据匹配

高效分析见本质

数据透视表篇8

13

数据处理核武器:数据透视表

学会拖拖拉拉，透过数据查看本质

数据透视表实例:发现问题是价值所在

动动手指分析数据，Excel的核心功能，你值得掌握

图表篇:

数据可视来呈现

满满的干货

抽丝剥茧:图表元素面面观

不止是赏心悦目，让你的图表显得比别人更加专业

出奇制胜:几种花式图表的制作

分分钟让你颜值爆“表”

升职加薪自然而然少不了

美观实用:让你的数据好看又智能

讲真的，Excel除了不能生孩子，其他的事情都能干