excel总结遗留问题解决

标签：总结格子单元格 excel 遗留问题格式数据粘贴

excel遗留问题解决

power query
这是power bi中的一部分，excel 2016以后集成了power query ，用于做数据清洗。
一般过程是数据导入 power query ，经过power query清洗，然后上载到excel的表，数据透视表等以共使用。
插入之定义列，然后使用公式生成新的列数据？
函数配合条件选择

使用条件格式命中目标，然后使用定位--条件格式--当前值选中目标，然后使用编组功能把他们分成一组，然后再函数中用组名当做数组使用
excel的格式分成三种
- 通用格式(字体字号字色等，边框填充等，对方式排版方向等，条件格式数据验证批注等)
- 数字格式(内容格式)，用于描述单元格内容
- 主题格式(全局格式)粘贴的时候叫做源格式(受到全剧主题的影响，这个和普通格式有大量重叠但是可以全剧替换)
各种粘贴的效果
- 粘贴选项
  - 粘贴选项对比
  - 保留源格式需要粘贴到新的工作簿(并且主题不一样)才能看出区别
- 选择性粘贴
  - 选择性粘贴里面可以做到部分粘贴，这部分功能和粘贴选项里面是大分部重合的有些细致的功能粘贴选项里面没有，红色框里面的粘贴选项都做不到
  - 粘贴的时候可以跳过空值，也就是有数据的格子才会粘贴过去，不用用空值覆盖旧的值
  - 对旧值的区域加上100，可以在任意单元格输入100，选中这个单元格，然后再原始数据区域选择性粘贴--运算--加
填充的几种方式 OK
- 填充类型
  - 复制填充，复制内容
  - 序列填充，对应的坐标+1
  - 快速填充(智能填充)，ctrl+E
  - 原位填充(所有选择格子填充当前选中格子的值)，ctrl+enter
- 触发方式
  - 拖动填充
  - 选中格子，鼠标移动到右下角，然后双击黑色+的填充柄
  - 不同场景的快捷间填充，比如ctrl+E ，ctrl+enter之类的
数据验证 excel 自定义验证 OK
条件格式和数据验证里面的自定义公式 OK
设置在每页打印表格标题行，打印->页面设置->工作表->打印标题？不可选？ OK
打印页面设置里面不能选，但是页面布局--工作表选项--工作表页面设置里面就可以选
查找里面的单元格匹配和查询范围的关系 OK
- 查找范围里面的三个值
  - 公式，指的是编辑框里面的内容，
  - 值，指的是excel的显示结果
  - 批注，查询批注项
- 单元格匹配选中的时候是全量匹配，没有选中的时候是类似包含匹配
- 没有勾选单元格匹配 的时候使用，公式是可以查询出公式内容和结果值，值时候只能查询出结果值，并且这时候的查询是包含匹配
- 勾选单元格匹配 的时候使用，公式查询的是公式的内容，值查询的是输出的结果。并且是全量匹配
定位里面的数据校验全部和相同选项的区别 OK
- 全部是查询到所有数据验证或者条件格式的格子
- 相同是查询当前选中的格子具有同样条件格式或者数据验证的格子
---------------------------------------------------分割线-------------------------------------------------
当前数组？？？？？？？？？？？？？？？？
好像已经无效了？现在在当前数组中任意格子默认就会选中当前数组
据说类似=VLOOKUP(D59,$C$59:$I$71,{1,2,3,4,5,6,7},FALSE)输出是多个格子，在里面任意格子定位到当前数组，会选总所有输出的格子。
数据--分类汇总

原始数据这样子

下面的汇总是自动生成的
行高大概是列宽的6倍才能大约是正方形的格子，这是为什么？我们需要单位是什么？
- excel的列宽单位是默认字体(等线字体)默认字号(11磅)下面一个字符的大小，宽度默认是8.38，默认可以放下8字符，9 哥字符就会挤出去一些。
- excel的行高单位磅，默认值是14.25，对应字体11磅多一点。
- 4号字体对应14磅，小4对应12磅，5号是10.5磅，excel默认的11磅是比5号字体略大。
vlookup和 hlookup 函数的区别？
- vlookup，v是Vertical的意思，表示垂直方向的查找
- hlookup ，h是Horizontal的意思，表示水平方向的查找
- 他们的用法和参数都是一样的，第一个是查找的值，第二个是查找的区域，第三个是取第几行/列的数据，第四个参数表示是否范围查询。
拒绝宋体？的原因是啥？

宋体横细竖粗，开始结尾处细节修饰比较多，不利于在屏幕上显示，尤其是字幕描边等情况。
数字格式
- @*_ 的结果是插入下划线
  - *表示*后面的文本重复N次，直到填满单元格
- , 千位分割分隔符，表示当前位置是千分位？，放在前面是千分位，后面是表示除1千
  - 0,000 表示每三位添加千位分隔符
  - 0.0, 表示除以1000,并且最后保留1位小数
- 0!.0,"万""元"，能显示万元
  - ! 强制显示某些有特定意义的字符，!.是强制显示小数点，!" 强制显示引号
  - !.0表示在右边第二位强制显示小数点，也就是缩小了10倍，加上最后的千位分隔符就是1W倍的缩小。
函数
- mid函数字符串截，大概是middle的缩写，取字符串中间的几个字符
  - MID("原文"，开始位置，取几个字符)
- date(年，月，日)函数
  - =DATE(2024,8,5) ，返回 2024/8/5
- days 间隔日期
  - =DAYS("2024/8/5","2024/8/3")
- and 和 or 配合 if使用
  - 例子： if( or(and( 条件1，条件2 ), 条件3 ) ，"true的结果"，"false的结果" )
- xlookup，第一个参数是查询的值，第二个参数是查询的区域，第三个是返回的区域，第4个事默认值，第5个事
- dget 类似数据库的where查询过程
- NUMBERSTRING(数字,TYPE) 可以把数字转换成汉字
  - =NumberString(1234567890,1) 返回结果：一十二亿三千四百五十六万七千八百九十
  - =NumberString(1234567890,2) 返回结果：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
  - =NumberString(1234567890,3) 返回结果：一二三四五六七八九〇
excel 插件
- 方方格子
- excel 催化剂
- 应用宝
- 搜索工具箱
- excel 工具箱
- 慧办公
power query 的使用
excel的异常
- N/A 当在函数或公式中没有可用数值时，将产生错误值#N/A。？
- NAME 公式名字错误
- VALUE 公式的值错误
- ###### 单元格宽度不够，不能显示
- DIV/0！零除
- REF 引入数据错误
- NUM 数字格式错误？
- NULL 使用了不正确的运算符？
R1C1引用样式，在选项--公式里面可以开启
名称管理器的使用(其实就是定义的一变量)
- 批量创建名称管理器
- 可以不是格子引用，还可以指向常量文本，公式等
excel 格子中显示图片，使用vlookup函数查找指定行的图片，使用命名的变量指向图片
excel365 (2023年下半年才有的功能？)才有的新功能，可以把图片插入到excel的格子里面，office 2021 还没有更新，只能浮动预单元格上面
excel的格子里面可以插入图片吗？不然的换怎么查找引用
excel365 (2023年下半年才有的功能？)才有的新功能，office 2021 还没有更新
视图拆分是用来干啥的

点击拆分以后，会一鼠标选中格子作为分隔符，然后上下左右分成4块，选中不同的半块区域，滚动只有当前区域会滚动。
excel超级表
- 默认或冻结首行，并且可以通过ctrl+↑回到首行，冻结首行不能回去
- 默认开启筛选
- 默认使用了表格样式(中等色-蓝色样式2)
  - 开启了隔行变色
  - 开启了标题行
  - 可以开启汇总行
- 支持切片器
- 会自动扩展表的数据区域(这意味着添加行列，哪些选用全行列的公式不用再修改)
切片器,可以对超级表和数据透视图的数据进行动态的过滤
数据透视图表，和数据透视图
数据透视表是excel对数据分析的工具，数据透视图能得到的结果类似如下sql
- select 聚合函数(xxx) from 数据表 where 筛选条件 group by 行字段，[列字段]
数据透视图是数据透视表的可视化图形，跟随透视图数据变化
通过下拉别列表图形控件的数据区域，以此实现动态效果
录制宏，使用excel的做一个类似表单的录入数据
- 怎么做一个按钮？用圆角矩形代替
- 怎么给按钮绑定绑定宏？右键点击-按钮-指定宏，指定到已经定义的宏
- 录制脚本过程
  1. 复制输入框数据的时候使用绝对定位，找到数据页第一行(大标题行)以后开始使用相对定位。
  2. 查找输入页最后一行，使用相对定位，然后使用ctrl+下箭头找到区域的最后一行，然后再用下箭头找到新的插入位置,开始粘贴数据。
  3. 如果一次没有录入完，那么要切换会绝对定位，然后重复复制输入表单的数据，重复录入的时候查找相对格子的时候用ctrl+下箭头，然后用右箭头调整插入的相对位置。
  4. 录入完后切换会绝对定位，清楚输入框内容，把光标定位到第一个输入框。
录入页

数据页