excel使用总结

  1. 填充的几种方式

    • 填充类型
      • 复制填充，复制内容
      • 序列填充，对应的坐标+1
      • 快速填充(智能填充)，ctrl+E
      • 原位填充(所有选择格子填充当前选中格子的值)，ctrl+enter
    • 触发方式
      • 拖动填充
      • 选中格子，鼠标移动到右下角，然后双击黑色+的填充柄
      • 不同场景的快捷间填充，比如ctrl+E ，ctrl+enter之类的

  2. 快捷键

    • tab 定位到右边的格子
    • shift+tab 定位到左边的格子
    • enter 定位到下一行第一个格子，如果输入过就是下一行的当前位置？
    • shift+enter 定位到上一行的当前格子
    • 上下左右箭头移动当前格子向上下左右移动一格
    • shift 可以点选，ctrl+箭头可以定位到表格的边缘，ctr+shift+箭头可以快熟选中一行一列或者任意连续行列或者整个表格
    • ctrl+N新建工作薄
    • shift+F11新建工作表

  3. 对齐的那边半圆的用法

    用来调整旋转角度
    image-20240801140041803

  4. 日期格式只能是-或者/线分隔

    • 快速输入当前日期 ctrl+；
    • 快速输入当前时间 ctrl+shift+；
    • 如果要输入完整的时间 直接输入 =now() 回车

  5. 数字的输入

    • 百分比:20%

    • 分数:0 1/2 ,直接输入1/2会变成日期

    • excel 的数字类型的限制
      注意数值类型是15位，超过丢失精度，后面变成0
      image-20240802095212123
      常规类型超过11位变科学计数法，这时候精度还在，依旧是超过15位后面才会变成0丢失精度

      image-20240802095507777

    • 输入英文逗号 ' 123 数字当前格子会变成文本，这个绿色的小三角叫做错误指示器
      image-20240726170756158

  6. ctrl+enter 原位填充，把当前格子填充到所有选中的格子

    • 先用ctrl+鼠标点选需要填充的格子(拖选或者别的选中方式也可以)

    • 然后再最后一个为本格子里面输入输入内容，然后ctrl+enter

    • 如果最后那个格子已经有需要填充的内容，直接F2进入格子的编辑状态，然后ctrl+enter也行

      选中多个格子，然后在最后一个格子输入内容
      image-20240726170448800
      输入ctrl+enter以后
      图片1

  7. excel的格式分成三种

    • 通用格式(字体字号字色等，边框填充等，对方式排版方向等，条件格式数据验证批注等)
    • 数字格式(内容格式)，用于描述单元格内容
    • 主题格式(全局格式)粘贴的时候叫做源格式(受到全剧主题的影响，这个和普通格式有大量重叠但是可以全剧替换)

  8. 多人协作场景的数据收集可以优先考虑在线的excel工具，国内网络原因，可以考虑使用简道云代替

  9. 数据合并，合并以后不变成了两个sheet

    • 数据-->获取数据-->从文件-->从文件夹，然后可以把多个excel的数据合并到当前excel
    • 前提条件，需要每个excel都有相同的表头
    • 老版本位置,数据-->新建查询-->从文件-->从文件夹

  10. 获取外部网站数据

  • 数据-->获取数据-->自网站，需要网站使用的是 <table>标签才能识别

  • 这样获取的数据可以通过刷新来获取最新的数据，也可以自动刷新

  • 自动刷新在 数据->全部刷新->连接属性 ,然后可以设置自动刷新时间

  • 需要注意数据刷新会覆盖原表格上的编辑

  • 使用，查询->编辑里面 ，可以对获取到的数据表格展示做一些编辑和清洗，并且这样的编辑在刷新的时候不会丢失

  • 只有超级表才能使用power query功能？

    输入地址
    image-20240726181143372

    选择网页中的表格，然后加载到指定区域

    image-20240726181538709

    效果

    image-20240
    设置自动刷新
    image-20240802103327133

    这里的修改不会被刷新覆盖
    image-20240802104337736

  1. 普通表转换成超级表，选中表格数据区域，ctrl+T，或者插入->插入表格

  • 数据区域变成超级表以后，选中任意单元格，功能区会多出 表设计选卡

  • 表设计->工具(组)->转换成区域，可以把超级表还原成普通表

    image-20240729153139408

  1. 数据-->分列，可以把一个列拆分成多个列
    拆分的时候可以选择按照字符长度拆分或者按照固定分隔符拆分，拆分的时候可以指定单独指定单元格数据格式

    image-20240729145629861

  2. 筛选框，选中列 数据->排序和筛选(组)-->筛选 就会出现筛选框

    image-20240729153549705

  3. 使用countif公式+排序找出重复
    image-20240729161021556

  4. 跨列居中和合并居中的区别
    合并居中是改变了单元格，格子变少了，跨列居中格子没有改变是跨列了

    第一个是合并居中,第二个是跨列居中
    image-20240729170356805

  5. excel打印得时候默认不会打印网格线，网格线可以关闭显示
    image-20240729170457395
    或者在页面布局中设置
    image-20240802105838276

  6. excel 单元格格式 主要三种格式

    • 文本 默认做对其
    • 布尔 默认居中对其
    • 数字 日期，货币金额都是数字。默认有对右。
    • 其他单元格格式用途总结
      image-20240729170953813

  7. 单元格左上的小三角叫做错误指示器

    可以在选项公式里面修改错误指示器是否显示，和显示的颜色
    image-20240730095231590

  8. 自定义单元格格式

    1. 单元格内容格式代码

      • 数字类型，下面的000.000,是占位符代码，可以有任意多个
        • 设置位置
          image-20240802111030978
        • 000.000 ，不足的位用0补充，小数点前面超过的位数如实显示，小数点后面超过的位数隐藏
          image-20240729174448021
        • ???.??? 不足的的位用空格占，小数点前面超过的位数如实显示，小数点后面超过的位数隐藏
          image-20240729174406112

        • .### 不足的位用不显示，小数点前面超过的位数如实显示，小数点后面超过的位数隐藏

          image-20240802110825169
      • 文本类型
        • @ 表示输入原文，多个@表示重复多次，同样可以加入别的固定文字
          image-20240729174044383

    2. 格式代码的4个区域，每个区域都可以用0?#@
      4区域格式分别表示，正数;负数;零;文本 , 0;-0;0;@表示都显示

      如下图，4区域的格式分别是

      正数区域代码:00.00，正数用0占位

      负数位区域代码:-??:??，负数前面有-号，并且用空格占位

      零区域：-0 ，表示在0的前面加-号，0前面加负号正常没什么意义，只是演示可以这样用

      如果一个区域是空那么就是隐藏

      文本区域：@@ ，文本重复2次
      image-20240729175429914

    3. 单元格颜色代码，直接写在单元格格式代码的最前面用[颜色]表示
      格式如下：[红色]00.00;[绿色]-??.??;;[黄色]@@
      image-20240729180214880

    4. 条件代码，条件代码编写格式和颜色代码类似，[条件]
      只能用3段或者小于3段，用4段会和4区域混淆

      [绿色][>=80]0;[红色][<60]0;0 大于80显示绿色，小于60红色，其他原色
      image-20240729180906670

    5. 时间格式

      • yyyy 2024
      • yy 24
      • mmm 月份的英文缩写
      • mm 两位月份
      • m 一位月份，如果是1位显示不下还是会是2位
      • dd 两位天
      • d 一位的天
      • aaaa 三位汉字星期几
      • aa 不显示星期，只显示几
      • dddd 星期几的英文全称
      • 上午/下午 上午
      • AP/AM AM
      • a/p a
      • h:m:s 一位是分秒，显示不下依旧会显示2位
      • hh:mm:ss 2位的是分秒
        image-20240729181428711

  9. 数据工具--数据验证的使用
    数据->数据验证，可以限制单元可以输入的内容，以及给出提示

    • 数据验证-输入提示设置

      • 数据-->数据验证-->输入信息，可以给单元格输入提示
        image-20240726170402662

    • 数据验证-数据下拉列表

      • 数据-->数据验证-->设置，设置允许条件为序列，来源输入值，用逗号隔开多项
        image-20240726171244098

        下拉框效果

        im

      • 这里还有别的很多验证，对小数和整数的范围验证

      • 对文本长度验证

      • 对时间日期的区间验证

      • 还有自定义公式验证

    • 数据验证-出错警告
      image-20240726173214081

      警告的三个级别

      • 停止 弹出提示信息，不能确认保存异常的输入
      • 警告 弹出提示信息，让选择确认或者撤回
      • 信息 弹出提示信息，然后确认输入

    • 数据验证-圈释验证不通过的数据

      image-20240726174108338

  10. 数据验证--自定义

    需要表达式返回的是一个true或者false

    下面图中的表达式要求返回 10到20之间的一个数或者100.
    image-20240802165249946
    全都返回不通过

    需要注意数据验证当前格子可以取到最新值，这时候别的格子里面的公式取到的是老的值
    image-20240802165801428

  11. 数据--数据工具--删除重复值
    删除前
    image-20240730000500243

    删除后
    image-20240730000436709
    如果选中的是一列，并且这列周围有数据，需要选择是否级联周围的数据

    第一个选项是级联周围的数据，第二个是不级联，注意这是是选的是要级联删除的行数据，不是判断重复的列，这里选定以后才是选择判断重复的列。
    image-20240730100543964

    选择判断重复依据的列(这里选择的F，也就是判断重复的依据只是F)
    image-20240730100923804

  12. 选择性粘贴，可以选择性的粘贴内容，格式等

    • 粘贴后有个提示
      image-20240729182438462
    • 粘贴前右键有 粘贴选项和选择性粘贴
      image-20240729182536975
    • 选择性粘贴
      image-20240802113204037

  13. 各种粘贴的效果

    • 粘贴选项

      • 粘贴选项对比
        image-20240803203759629
      • 保留源格式需要粘贴到新的工作簿(并且主题不一样)才能看出区别
        image-20240803201936218

    • 选择性粘贴

      • 选择性粘贴里面可以做到部分粘贴，这部分功能和粘贴选项里面是大分部重合的有些细致的功能粘贴选项里面没有，红色框里面的粘贴选项都做不到
        image-20240803205657247

      • 粘贴的时候可以跳过空值，也就是有数据的格子才会粘贴过去，不用用空值覆盖旧的值
        image-20240803204256774

      • 对旧值的区域加上100，可以在任意单元格输入100，选中这个单元格，然后再原始数据区域选择性粘贴--运算--加
        image-20240803204427438

  14. 条件格式的使用
    根据选中条件或者值的取值情况改变单元格的填充色等格式，或者在格子里面显示一些图示

    • 条件格式分成两大类

      • 一类是根据单元格的值所占整体的分布情况给出图形图示(数据条，色阶，图标)，作用是表现当前数据处于整体中的位置。
      • 另一种是根据和指定指标值匹配，然后确定显示的格式。

    • 对不同条件数值的格子设置格式
      image-20240729222102294

    • 数据条
      image-20240729222359884

      数据条的使用，设置数据条的长度最大值和最小值，设置数据条渐变
      image-20240729223725942

    • 图标集合
      image-20240729222528916

    • 色阶的使用

      image-20240730103741926

    • 当有多条规则作用于统一区域的时候，上面的优先高
      如果勾选了如果为真停止可以让后面的规则失效

      如果没有勾选就是合并格式，如果格式有重复的，保留高优先级的格式。
      image-20240730103935793

    • 条件格式+使用筛选的带唯一值
      image-20240730104753222

    • 条件格式--使用自定义公式

      下图定义的公式命中的是能被2整除的数字
      image-20240802171124095

      =image-20240802171022104

  15. 迷你图--在单元格内部的图
    image-20240729230833776

    • 迷你图有三种，折现图，柱状图，盈亏图(不适合表示变动趋势，只适合表示正负盈亏)
    • 迷你图是在一个单元格内输出的图，所以输出位置是1*n或者n*1
    • 使用迷你图确定行列数据统计方向问题，默认情况下会自动选择，选择依据是输区域是n的那边和数据区域的行列相等情况。如果n等于数据区域的行数就是纵向趋势，如果输出区域的n等于列数就是横向区域，如果输出区域行列数相等，那么可以通过编辑数据--切换行/列来切换
      image-20240730110535951
    • 可以设置突出显示一些高低点，会显得更加直观
      image-20240730110711682

  16. 在查找替换中使用通配符 ？ * 和 转义字符~

    • ?单个字符

    • *多个字符

    • ~ 转义字符,~?匹配问号，~*匹配星号

    • 需要注意的是excel的查找不是全量匹配，所以阿?可以查到阿达

      image-20240730231002578

  17. excel 查询替换默认是对当前sheet替换，如果选中指定区域然后替换就是区域内替换

  18. 查找功能的高级选项可以查询格式，可以指定查询范围，可以查询全部，然后全部选中，然后再给他们编组

    全选以后再左上名称框里面输入组名回车就行了，这样的编组在公式中可以使用
    image-20240731180947607

  19. 按行查找和按列查找的区别
    一个是行遍历优先，有个是列方向上遍历优先

  20. 查找里面的 单元格匹配 和 查询范围的 选项说明

    • 查找范围里面的三个值
      • 公式，指的是编辑框里面的内容，
      • 值，指的是excel的显示结果
      • 批注，查询批注项
    • 单元格匹配 选中的时候是全量匹配，没有选中的时候是类似包含匹配
    • 没有勾选单元格匹配 的时候使用，公式是可以查询出公式内容和结果值，值时候只能查询出结果值，并且这时候的查询是包含匹配
    • 勾选单元格匹配 的时候使用，公式查询的是公式的内容，值查询的是输出的结果。并且是全量匹配

  21. 选中以后(或者查询选中以后)可以分组

    编组的数据可以作为数组进行函数计算
    image-20240731165001958
    函数可以使用组
    image-20240731165022668

  22. 定位的使用

    • 使用定位查找图片，公式格子，空白格子，可见格子等

      image-20240730115200016

    • 行/列内容差异单元格
      查询出横向或者纵向和当前选中单元格有差异的个体，下面的a2单元格格式是文本。
      image-20240730115901846

    • 在名称框和引用框输入坐标都能直接选中

      选择一个区域格式：左上坐标:右下坐标

      选择多个区域格式：区域1,区域2,区域3
      image-20240730135748736

    • 定位里面的数据校验 全部和相同 选项的区别
      全部是查询到所有数据验证或者条件格式的格子
      相同是查询当前选中的格子具有同样条件格式或者数据验证的格子

    • 当前数组

      好像已经无效了(office 2021)？现在在当前数组中任意格子默认就会选中当前数组
      据说类似=VLOOKUP(D59,$C$59:$I$71,{1,2,3,4,5,6,7},FALSE)输出是多个格子，在里面任意格子定位到当前数组，会选中所有输出的格子。

  23. 函数 配合 条件格式 选中数据

    1. 使用条件格式命中目标
    2. 然后使用定位--条件格式--当前值选中目标
    3. 然后使用编组功能把选中目标分成一组
    4. 然后再函数中用组名当做数组使用

  24. 创建其他模版的工作薄

    一般我们用的空白模版，有些时候可以考虑直接用已经有的模版
    image-20240730142042215

  25. 使用视图并排工作簿，使用新建影子并排工作表

    视图--全部重排--垂直重排 并列两个excel文件
    image-20240730142543205

    视图--新建窗口，是给当前文件多开一个编辑窗口，他们内容是同步的，并且可以重排对比不同sheet
    image-20240730142940773

  26. 工作表组，的使用和退出
    在一个工作表组里面输入，所有的表都会写入同样的内容，ctrl+点选工作表下面的tabs就可以同时选中多个sheet
    同时选中多个表，然后编辑是会同步到别的sheet的对应位置的
    image-20240730144352128

    编辑完成以后查看别的表
    image-20240730144441050

    取消组
    image-20240730144732666

  27. 移动或者复制工作表

    可以把工作表移动到别的工作薄(需要别的工作薄已经打开)，勾选了建立副本就是复制，否者会删除原表
    image-20240730145134006
    选择目标
    image-20240730145545573

  28. 工作表的显示和隐藏
    在表标题上右键可以选择隐藏和显示
    image-20240730145703268

  29. 工作列行的显示和隐藏

    在行列上右键，可以显示隐藏，取消隐藏后，调整列行列宽度才会显示，如果是直接选中然后指定区域取消隐藏会直接显示

  30. 冻结窗格可以让某些行列不随着滚轮移动

    冻结首行和首列的时候不需要选中，如果是要同时冻结行列，需要选中数据区域的最右上数据格子再冻结。冻结区域是根据当前选中位置算的。
    image-20240730162818047

  31. excel 打印的时候可以设置打印选中区域，打印活动工作表，打印整个工作薄
    image-20240730163404847

  32. 打印页面可以选择将列调整为一页，这样会把因为列太多分成多页的数据放到一页，对象数据列宽变小

    image-20240730163649104

  33. 打印->页面设置->页边距->居中方式,可以设置表格居中
    image-20240730163745392

  34. 打印->页面设置->工作表->打印->行和列标题，可以设置打印行列标题(行列编号)

    image-20240730164126578

  35. 设置在每页打印表格标题行，打印->页面设置->工作表->打印标题，不可选问题

    从页面布局里面是可以选的，但是从打印那边进去不能选。

    个人觉得打印那边是工作簿级别的设置，打印标题和打印区域应该是sheet级别的设计，所以只能在页面布局修改。

    image-20240802172758733

  36. 函数和函数参数面板的使用

    • 输入函数的时候ctrl+a(需要已经用tab选择函数以后才有效)可以直接打开函数参数面板,和右上的fx按钮效果一样
      image-20240730165043088

  37. 公式和函数的区别

    • 自定义的结算逻辑是公式
      • 输入=号然后选引用的格子
        image-20240730181204667
    • 使用系统定义计算逻辑的是函数
      • 输入=然后输入函数的前几个字符然后可以选择函数，如果是在第一个直接tab键选择
        image-20240730181217690
    • 其实在查找里面查找公式上面的两种都能会被命中，他们的区别主要在默认值和报错处理的区别。

  38. 使用$可以让excel的引用变成绝对引用，excel默认是相对引用

    • 相对引用在拖动填充的格子里面会坐标会递增，横向拖动填充增加横坐标，纵向拖动填充增加纵坐标。

    • B2，在向右拖动填充的时候，B会递增，向下拖动填充的时候2会递增

    • $B2,向右填充的结果都是B2格子的内容，$B$2向任意方向填充的结果都是B2

    • F4可以快捷的切换锁定绝对引用和相对引用

    • 使用函数和使用公式的区别

      • 使用函数会自动过滤一些错误数据，比如count之类的会过滤文本空值等，公式会直接报错，函数会自动跳过一些异常值
      • 公式的计算会对数据类型做自动转换，所以文本也可以相加，不会跳过，如果不能自动转换会报错，公式会做自动类型转换
      • 函数对行列添加删除之类的变化会自动添加或者过滤，公式会直接报错 找不到的格式显示为#REFI，函数在行列增减的时候可以自动适配

  39. 排序和筛选的位置 数据->排序筛选，或者开始->编辑组->排序和筛选

    • 选定排序列，然后选择扩展选定区域，附近的数据列跟随当前数据列的顺序变动
      image-20240801100547466

    • 选定排序列，然后选择以当前选定区域排序的效果是只有当前列的顺序变动

    • 如果只选中排序列的一个格子然后排序等价于默认是扩展选定区域

      5062

  40. 高级筛选的使用类似数据库的where查询，高级筛选最大的特点在于可以把筛序结果输出到指定区域，简单筛选只能在原有数据上筛选

    第一个参数是数据区域，第二个是查询条件，第三个输出位置(当选择把筛选结果输出到其他区域的时候才有效)

    如果勾选选择不重复的记录，会对结果去重
    image-20240801151137147

  41. 函数count,counta,countblanck的区别

    • count 只能求数值类型的非空格子数量
      image-20240731095142981

    • counta可以求字符和数值类型的非空格子数量
      image-20240731095157459

    • countblanck 可以求空格子的数量
      image-20240731095213387

    • 求所有格子的和可以用，countblank+counta
      image-20240731095400715

    • countif ，对指定区域求数量，但是要满足第二个参数的判定条件
      image-20240731095555099

    • countifs ，可以有多组取数区域和判定，相当于多个countif的结果求与的关系
      第一个参数是数据取值区域，第二个是判定是否要计数的条件

      im

    • dcount(取值的表区域,统计的字段,等值条件)，这个类似数据的where查询

      第一个参数是数据表区域(包含表头)，第二个求count的字段，第三个是where条件区域(包含表头)
      image-20240731103129016

    • dcounta，和dcount的区别在于这个是多非空白格子，dcount只能统计数字格子

  42. 求和平均

    • 接受的参数可以是多个参数的，也可以是数组或者二维数组
    • 自动求和，选中区域然后 alt+=
    • average函数对于空白和文本格子不会计数，对于0的格子会计数

  43. sumif，sumifs，averageif，averageif，sumproduct()对多个数组的对应下边的元素求和

    • sum(求值区域) 对指定区域数值类型求和
    • sumif(判定区域，判定条件，求值区域)
      image-20240731104714346
    • sumifs，第一个参数是求和区域，后面成对出现的判定区域和判定条件
      image-20240731105130689
    • dsum d开头的函数都类似数据库的where查询，第一个参数书带有表头的数据区域，第二个是统计字段，第三个是where条件
      image-20240731110135797
    • sumproduct(区域1,区域2,[区域3]) 是区域1和区域2的乘积然后再求和，最常见的用法就是算总价
      image-20240731110755210
    • sumsq 求平方和，
    • sumx2my2 求两数组平方差的和
    • sumx2py2 求两数组平方和的和
    • sumxmy2 求两数组差的平和
    • average，averageif，averageifs ，daverage和 sum之类的函数类似
    • averagea可以会把字符类型当做0，false当做0，true当做1

  44. 函数配合条件选择？

  45. rank(排序字段，排序区域，升序降序)，现在推荐用rank.eq方法
    image-20240801154511313

  46. vlookup函数

    • 在指定区域的第一列查找指定值，找到以后返回指定列，并且可以精确查找或者模糊(范围查询)查找
      image-20240801162844766
    • 模糊查找实际是范围查找(第4个参数是true），是查找第一列，查询值大于等于当记录，小于下一个条记录，所以择数区域第一列必须是升序排列的
      image-20240801171151438

  47. vlookup函数配合if函数实现反向查找(choose函数也可以，尤其是在字段大于2个的时候)

    vloopup查询的匹配字段在第一列，查询的字段在右边，方向查询是指能查询到左的字段。
    image-20240801175720651

  48. if函数
    普通用法
    image-20240801175945320

    多层嵌套

    image-20240801180507792
    if的特殊用法，返回多列(如果第一个参数的数组是纵向也可以是多行)

    判定条件是数组的时候输出也是数组，这里是横向数组，所以输出两列
    image-20240801180706553

    if输出是一个二维表
    image-20240801180855481

  49. choose和if类似，但是更加适合选项多的时候，choose的第一个参数是后面选项的下标，if的第一个参数是true或者false，代表选择后面的第一个还是第二个值。

    choose常见用法
    image-20240801181058496

    第一个参数依旧可以输入数组

    image-20240801181202896

  50. excel的数组

    • 横向一位数组：
    • 纵向一位数组:
    • 二维数组：
    • excel 的范围区域 如 A1:C3 会被翻译成一个类似{1,2,3;1,2,3;1,2,3} 数组里面的值取自真实的格子数据。

  51. index

    index有两种模式

    • 模式1只有三个参数，第三个参数可以不填
    • 模式2，有4个参数，第一个参数是多个取数区域，第2,3参数和之前一样，第4个参数是指定第一个参数多个区域的下标

    只有一列数据，只指定行号,取出第几行的内容
    image-20240801182325827

    多列数据，需要指定行号和列号

    image-20240801202054965
    index的数据源区域可以是多个, 多个区域用逗号隔开小括号包裹如 (A1:A8,B1:B8)
    image-20240801203151715
    image-20240801203207979

  52. match 函数

    和vlookup函数很像，vlookup有4个参数，可以指定返回第几列的数据，match直接返回命中数据是第几行，配合index函数，可以实现vlookup函数和vlookup反向查询

    第一个参数是查询的值，第二个是查询区域，第三个是是否模糊查询(范围查询)，返回值是命中数据位于第几行

    image-20240801203755764
    match函数配合index函数使用

    这里最后一个参数是1，表示的是第一列,这里的数据区域只有一列，不写其实也可以
    image-20240801204309605

posted on 2024-07-17 18:25  zhangyukun  阅读(8)  评论(0)  编辑  收藏  举报

导航