《21天Excel新手到高手》技巧篇：快速数据选择、输入数据的秘诀

《21天Excel新手到高手》技巧篇：快速数据选择、输入数据的秘诀 ，提升你的excel处理能力。感兴趣的小伙伴可以收藏起来。

21天Excel新手到高手 技巧篇：快速数据选择、输入数据的秘诀

• 1.掌握4种方法，选择数据快的不是一点点
  • 1）鼠标拖拽法
    • 连续单元格
      • 方法一：鼠标放在第一个单元格，然后按住左键不放，将鼠标拖拽到最后一个单元格
      • 方法二：鼠标单击起始单元格，然后按住Shift键，将鼠标放到最后单元格
    • 非连续单元格
      • 加上Ctrl键
  • 2）快捷操作法
    • 连续单元格
      • 方法一：Ctrl加方向键，即可跳转到相应边缘。
      • 方法二：鼠标移动到单元格边框，出现十字光标时，双击即可跳转到边缘
      • 同时按住shift即可选择连续单元格区域
    • • 在单元格任何位置按住Ctrl+Home（End），即可返回到A1单元格（该区域的最后单元格）
      • 在单元格任何位置按住ctrl+A即可全选整个单元区域
    • 职场细节
      • 需要外发给别人查看的Excel文档，在完成内容后，随手按下Ctrl+Home键，回到A1单元格，方便阅读者从头查看
  • 3）定义名称法
    • 非连续单元格
      • 选择单元格区域，在名称框中输入定义名称，按回车确定。之后只需在名称栏中找到或输入对应名称即可选中命名区域。
    • 注意
      • 在单元格命名中，不能出现A100,B35这种名称 (原因是因为在Excel中每一个单元格区域，都是由字母和数字两个位置共同完成的)
  • 4）条件定位法
    • Ctrl+G打开定位面板，选择定位条件。
• 2.学会3种操作，高效查看数据
  • 1）冻结拆分窗格
    • 冻结首行（首列）：点击视图，冻结窗格—冻结首行（首列）
    • 冻结多行（多列）：在需要同时显示的单元格下方任意单元格位置，点击然后冻结窗格
    • 冻结行和列：选中行和列方向交叉位置单元格，点击冻结窗格
    • 技巧：在excel中按ALT，此时excel菜单栏出现快捷键字母按钮，按下对应的快捷键即可快速进入相应的功能（如冻结窗口：ALT+W+F+F\R\C（即冻结内容））
  • 2）数据内容查找
    • 在开始菜单栏中找到查找和选择，点击查找，调出查找对话框（或ctrl+F）
    • 如果想对所查找的内容进行标注的话，点击其中一个，按ctrl+A全选，关闭对话框，进行加粗或颜色调整
  • 3）数据内容筛选
    • 筛选条件调出：选中表格需要筛选的标题单元格，点击排序和筛选中的筛选（或ctrl+shift+L）
    • 大于小于等筛选：点击筛选小三角，找到数据筛选，即可实现一定范围内的数字筛选
    • 快速将表格回复原状：菜单栏找到排序和筛选下面的，点击清除选项
• 3.图片PDF中的表格，如何快速录入Excel
  • 微信小程序：微软AI识图
  • 识别后的内容复制后发送到文件助手，电脑端复制到Word，选择数据部分，点击插入表格，文本转换成表格，设置行列，点击确定，然后复制内容，回到excel中，简单调整一下，对照数据，美化一下即可。（标题加粗，绿色底纹，字体反白处理，正文字体微软雅黑）（亦可以给列数据添加条件格式，让数据看起来增长分明）
  • 其他程序推荐：扫描全能王，白描APP，Acrobat，泰比(ABBYY)
• 4.如何多人协同完成Excel表格数据的快速收集
  • 1）识别两种类型表格的特征
    • 信息收集型数据
      • 特点
        • 侧重于标准化字段的数据收集
      • 例如
        • 个人信息统计，调查问卷等
    • 内容创作型数据
      • 特点
        • 侧重于团队之间内容的协作，共享，修改等
      • 例如
        • 多部门多地区的计划提交，报告汇总
  • 2）信息收集型数据的获取
    • 工具：问卷系统（问卷星，金数据，麦克CRM，腾讯问卷(wj.qq.com)等）
  • 3内容创作型数据的获取
    • 工具：问卷系统（腾讯文档（docs.qq.com））
• 5.外部数据文件，如何批量录入Excel
  • 1）出现的问题
    • （1）复制粘贴之后堆成一团，处理起来太麻烦
    • （2）用excel打开格式不对，每月都要更新
    • （3）每次都要打开网站，还要处理各种格式
  • 2）操作分类
    • （1）TXT数据导入excel及精加工
      • 找到数据选项卡，单击获取外部数据中的自文本选项卡，找到文件并导入，选择分割符号分割，选择其他（/），点击下一步，点击完成。出现导入数据面板，数据的放置位置选择一个单元格，点击确定。出现短横线，按CTRL+H查找替换，短横线替换为（不填），点击全部替换。在开始选项卡中点击套用表样式，选择合适的即可。
      • 注意：有时候我们在导入文本文件会出现中文乱码，一般是由于文本文件的编码格式不是ANSI所导致，这个时候只需要使用TXT文档，记事本文件打开后，点击文件另存为，在另存为处将编码改成ANSI格式即可
    • （2）CSV数据导入excel及精加工
      • 找到数据-新建查询-从文件-从csv，找到已经下载好的csv文件，点击导入，弹出窗口，点击数据转换选项，进入到Power Query中，对数据进行编辑和查看，对照数据，如果日期是数字格式，选中列，修改为日期格式等
    • （3）网页数据导入excel及自动更新
      • 查询往日天气，使用天气后报网站，复制网址信息，回到excel中，点击数据-新建查询-其他来源-自网站，在弹出的对话框中粘贴网址信息，点击确定。在导航器中出现导航器选项，选择需要信息，点击加载（设置为实时更新，即可获得最新天气信息。首先点击天气表格信息，会发现在菜单栏中多出表设计和查询两个下选项，在表设计中有一个刷新选项，点击刷新下方小箭头，点击链接属性，选择允许后台刷新，打开文件时刷新数据）
• 6.善用填充工具善用填充工具，录入数据从此告别手工敲击
  • 1）数据序号三种填充方式
    • 方法一：单元格右下十字光标出现，下拉至所需位置，此时填充的均为相同的，之后在十字光标处选择序列填充即可改变
    • 方法二：先填前两个数据，之后选中两个单元格，十字光标，下拉，excel自动按等差数列进行处理（或直接双击十字光标（需此列至少一边边均有数据，否则系统无法识别，无法填充））
    • 方法三：在单元格填入序号后，点击单元格，单击开始-填充-序列填充，在弹出的序列面板中填写所需即可（此方法可以在两边无数据进行）
  • 2）指定条件日期列表生成
    • 在日期序列输入日期信息，打开填充-序列面板-列方向，选中日期后，点到工作日，终止值设置为月末（数据要采用日期的形式）
  • 3）自定义序列的数据生成
    • 先人肉输入一遍，找到文件-选项-高级-编辑自定义列表（有些没有，可跳过）-自定义序列，选择刚输好的数据单元格，单击导入，点击添加即可
  • 4）自带光环的智能填充使用
    • 提取混排的文本和汉字：复制第一个文本，填充到单元格，选则下一个单元格，按智能填充（CTRL+E）即可
    • 实例：从身份中提取出生年月：1.在出生日期位置根据身份证信息，输入第一个人员的出生日期信息。2.鼠标移动到下一个单元格区域，按ctrl+E键。3.抽取些进行验证正确性
    • excel的智能填充不仅可以提取身份证中的出生日期外，还可以提取混排的文本和汉字
• 7.活用快捷键与公式，数据输入效率倍增
  • 1）快捷键实现数据的批量输入
    • 按CTRL+G调出定位面板，定位需填写内容的区域（如：定位条件为空值，即可选出未填写区域），在编辑栏中输入内容，按CTRL+ENTER实现批量输入填充
    • 快速搞定合并单元格的拆分操作：首先拆分合并的单元格，接着使用定位功能，选择空值，然后在第一个高亮单元格区域输入“等号”等于上一单元格，接着按CTRL+enter实现批量填充，即可实现将每个合并的数据分别粘贴到相应的拆分的区域
  • 2）自定义格式让录入省时省力
    • 方法一：如要填入01，02等数据，将单元格格式改为文本，然后用填充操作即可
    • 方法二：选择序号列，右键设置单元格格式，点击自定义，输入00（每个0代表一位），点击确定，这样前面的1，2，3就会变成01，02，03（如果人员增加到三位数，只需要输入000即可）（针对前缀也是一样，输入“SCP-“000即可（注意中文需要带双引号））
    • 优点：除此之外还可快速改变数据的大小写，同一数据格式
    • 单元格格式快捷键（CTRL+1）
  • 3）活用公式快速生成序号值
    • 方法
      • 应用if函数，判断姓名单元格是否为空
      • ROW（行）函数的作用是返回一个引用的行号，参数是求取于行号的单元格区域，例如ROW（A1）返回值为1
      • 实例：=if(b2<≥"",row(a1),"")
    • 在一个单元格输入后，可进行填充操作
• 8.用好复制粘贴用好复制粘贴，轻松搞定数据调整
  • 1）调出面板：选择需要复制的区域后，点击开始-粘贴下拉-选择性粘贴（或快捷键CTRL+ALT+V）
  • 2）功能
    • （1）选择性粘贴实现公式转化为数据
      • 在粘贴方式中选择数值，即可将公式产生的数据固定下来
      • 原因：用公式进行数据运算所得到的结果，不会受筛选等操作的影响，即公式是固定在各个单元格中，只有将其变化为数据，才能脱离单元格公式的束缚，才可进行筛选等操作
      • 同样前面由于粘贴导致的格式混乱等问题，也可用选择性粘贴将所有改为数值解决
    • （2）选择性粘贴实现单元格行列转置
      • 转置
        • 选择转置，即可将列数据复制到行
      • 跳过空白单元格
        • 选择跳过空单元格，即可实现多行数据数据合并
        • 此功能可以当复制的源数据区域中包含有空单元格时粘贴时空单元格不会替换粘贴区域对应的单元格
    • （3）选择性粘贴实现批量修改数值及数据核对
      • 在空白单元格输入要增减乘的数目，进行复制，选择需要运算的单元格，在选择性粘贴对话框选择运算篮中加（减，乘）按钮，即可实现数据的批量改变
      • 扩展：如果想知道两列数据有那些不同，将两列数据相减，查看不为0的数据即可
• 9.用好条件格式，为Excel找一个智能助理
  • 1）概述
    • 什么是条件格式？：条件+格式=规则
      • 让符合条件的单元格，显示为预设的格式，根据条件使用数据条，色阶，图标集，以突出显示相关单元格，强调异常数值，以及实现数据可视化的操作。
    • excel的条件格式分类
      • 突出显示单元格
      • 最前/最后
      • 数据预警可视化范畴
        • 数据条
        • 色阶
        • 图标集
      • 自定义
    • 使用：选中需要设置条件格式的区域，点击开始-条件格式-选择对应的条件模块即可
  • 2）功能
    • （1）条件格式标记重复值及特定条件值
      • 选中姓名列后，突出显示单元格规则-重复数据，在弹出的对话框中设置重复值显示方式即可
      • 突出显示单元格-内容输入（或文本包含）-输入需标记的内容，即可实现批量的标记（如果选取整个表格，则不仅目前，以后输入的也一样会显示）
    • （2）条件格式高亮显示数据最高最低值
      • 突出前和后几位：条件格式-项目选取规则-最前/最后-前十项-将十改为应填数据借口（后几位同样操作）
      • 用高于平均值衡量：最前/最后-高于（低于）平均值
      • 将前（后）10%表示：最前/最后-高于（低于）10%
    • （3）借助公式实现临期数据自动提醒
      • 选中整个单元格，开始-条件格式-新建规则-使用公式确定要设置格式的单元格，在公式对话框中输入=today()-日期单元格（记得去除列锁定，这样才能使用列填充）=0，设置格式，完成 （亦可以在产品临期提醒，员工生日提醒等中使用）
      • 调出具体年月日日期的方法
        • today()是当日完整日期，year（today()）是当日年日期，month(today())是当日月日期，day(today())是当日日日期，以此类推，可得当日或表中日期单元格的年月日单项
        • 例如：用出生日期设置临期提醒的公式为=year(today())-year(出生日期单元格)=年龄所在单元格，或=if(month(today())-month(出生年月单元格)=0,if(day())-day(日期单元格)=0,0,1),1)=0
• 10.Power Query处理多工作表数据汇总
  • 概述
    • 何为Power Query？
      • 正在连接和转换excel中的数据：1.连接，2.转换，3.组合，4.共享
  • 认识Power Query组件及使用入口
    • 选择数据-获取数据-选择对应的文件数据
  • Power Query实现多工作表数据汇总
    • 导入数据后弹出导航器对话框，其左侧显示了所有工作表的表明，既可以单击选择，也可以多项选择，单击转换数据，将数据加载到power query编辑器，右侧应用步骤只保留源区域，按CTRL选择name和data，然后单击删除列选项下的删除其他列，然后点击data下的按钮，在弹出的对话框中选择使用原始名作为前缀勾线掉，选择将第一行作为标题，删除右侧更改的类型，同时吧这个月份的一月修改为月份，删除不需要的表格区域，对编号做一个降序排列，将名称等删除，之后做升序排列，使其回复原装，将数据加载到excel中
    • 当原表数据发生改变时，只需刷新一下即可获取最新数据
• 11.Power Query动态处理多文件汇总
  • 使用Power Query 加载多文件数据
    • 在空白工作簿，在数据选项卡中选择获取数据，来自文件夹，浏览选择目标文件夹（或）
  • 在Power Query 数据编辑器中进行数据整理
  • 处理沉余数据信息后上载数据至excel
• 12.活用页面设置做好Excel文档输出打印
  • 常见问题
    • 1.打印时表格列不能显示在同一页怎么办？
    • 2.怎么设置才能只打印需要进行打印的区域
    • 3.打印表格前，需要对页面进行设置吗？
    • 4.在打印时如何保证每一页都显示表头内容？
    • 5.如何快速实现报表的自动分页打印效果？
  • 1）打印页面的基本属性设置
    • 打印预览界面（打印快捷键Ctrl+P）
  • 2）选择设置打印区域打印
    • 选中打印区域，单击文件-打印-找到打印区域-选定打印选定区域
  • 3）打印每页带表头的数据表格
    • 当工作表过长时，把打印的工作表设置为将所有的列调整为一页，这样这张工作表就可以分布在多张纸上了，
    • 在打印的每页都带上标题
      • 页面布局-页面设置(有些没有，直接点打印标题即可)-打印标题-顶端标题位置选择我们需要打印的标题内容-单击确定
  • 4）借助分类汇总实现自动分页打印
    • 实例：将每个销售员的销售产品，金额进行汇总，并分页打印个人销售员的情况
    • 步骤一：选中需要进行拆分的销售员这一列数据，单击排序选项（目的，在分类汇总之前，将汇总的数据进行排列，防止汇总数据不准确）
    • 步骤二：选中需要汇总数据区域任意单元格，依次单击数据分类汇总，在弹出的对话框中，分类字段选择销售员，汇总方式选择求和，选定汇总项选择销售金额，同时因为要求分页打印，所以要勾选下方的每组数据分页，单击确定
    • 步骤三（可选）：页面布局-页面设置-也秒打印-设置为顶端标题