Hong's External Brain

Appearance

概览 ​

大致掌握四个方向：

  • 数据格式，单元格格式
  • 函数
  • 可视化
  • 数据透视表

常用的板块大致介绍一下：

  • 开始
    • 搞数据格式，单元格格式
  • 插入
    • 搞可视化
  • 公式
    • 汇总统计
    • 文本处理
    • 时间计算
    • 表的查找
    • 单元格匹配
  • 数据
    • power query

基础操作 ​

快捷键操作备注
option + enter单元格内换行
shift + 上下左右键选中单元格
开始->格式-> 自动调整行高/列宽自动调整行高，列宽

数据格式 / 单元格格式 ​

单元格格式 ​

image-20240606133634118

条件格式 ​

样式里面设置单元格样式，条件格式很好用

image-20240606125701062

清除单元格格式 ​

  • 开始->编辑-> 里的粉色小橡皮，如上图

数据格式 ​

数据自定义格式 ​

方式一： ​

  • 正数格式；负数格式；零格式；文本格式
    • 用分号隔开
    • 要跳过某一个部分，要为跳过的代码部分添加分号；
image-20241221220125754

方式二 ​

  • 特定条件，用分号隔开
    • 大于条件值；小于条件值；等于条件值；文本
image-20240606124832947

方式三 ​

  • 特定条件
    • 条件值1；条件值2；同时不满足条件值1，2；文本

各种通配符 ​

符号代表啥备注
G/通用格式默认的就当没有
#数字
0数字，不足位就补齐格式：0000，原数字68，变为0068
“ ”双引号里面的内容为字符串格式：##.00”元”，原数字25.639，变25.64元
.?将无意义的0干掉格式：??.???，原数字011.250 ，变为11.25
.如果外加#则为字符，不加就是小数点
%百分比格式：#%，原数字 0.23,变23%
//+文本，和“”一样都是文本，输入后自动转为“”格式：/ABC，变为“ABC”
*****重复下一次字符，知道充满列宽格式：#*m，数字123变123—mmmmm
_下划线，留一个和下一个字符同等宽度的空格
[颜色]用制定的颜色显示字符[绿色]；[红色];[黑色];[蓝色]，显示为正数绿色，负数红色，零黑色，文本蓝色。
[条件]将单元格内容按条件设置[>0]正数；[<0]负数；零；文本，如果>0显示“正数”，等等
！显示单引号“”格式：”#！“ ， ”10“，变为“10”“

时间设置 ​

image-20240606133342483image-20240606133508628

数据 ​

定位条件，筛选单元格 ​

处理空值 ​

  • 开始 -> 查找和选择 -> 定位条件 -> 空值
  • 输入填充的值，command + enter

错误单元格 ​

  • 开始 -> 查找和选择 -> 定位条件 -> 公式 -> 错误
  • 输入填充的值，command + enter

只复制分类汇总的结果 ​

分类汇总计算方式 ​

  • command + a 选中表格区域
  • 数据 -> 分类汇总

复制分类汇总结果 ​

  • 折叠分类汇总后的表格，只显示汇总结果，如下图所示

  • 开始 -> 查找和选择 -> 定位条件 -> 可见单元格

  • 复制，粘贴即可

  • image-20240609132328649

填充柄 ​

  • 普通的填充柄右键点住右下角下拉，选择序列设置想要的格式即可，如等差之类的

  • 但想要自定义序列的话，比如孙大鸿，孙中鸿，孙小鸿之类的，需要自定义序列

    • 偏好设置 -> 自定义序列
    image-20240609101930558

多个不连续单元格输入相同数据 ​

image-20240609102457796

  • 按住command 选择多个单元格
  • 输入要填充数据
  • 按command + enter 填充多个单元格

文本记忆输入 ​

点单元格，右键从下拉列表中选择

image-20240609102739854

数据验证 ​

  • 规定单元格输入数据的格式，不满足格式输入不进去
    • 先选择要验证的单元格范围
    • 数据 -> 数据验证
    • 定义数据验证格式
image-20240609103137567

  • 要给指定的选项

    • 选择单元格范围
    • 在允许里选择序列
    • 提前在空白单元格处，写好所有选项
    • 来源处，选择写好选项的单元格范围即可
    image-20240609103749597

导入数据 ​

  • 数据 -> 获取数据 -> 选择文件格式

  • csv的分隔符问题：

    • 如果能分开就在导入时分割，

    • 分割不开，先导入

    • 选中整列数据，在数据 -> 分列 ，选择分隔符，他会自动识别分隔符，然后分隔

      image-20240609105021841

数据类型 ​

  • 常见报错值

    image-20240609105721773

  • 数值型数据，后加字符串单位

    • 选中单元格范围
    • 右键设置单元格格式 -> 自定义
    • ”#“ 或 0 代表数字，后面跟“单位”
    • 举个例子：如下图，金额保留两位小数，并加单位元
    • 自定义具体如何设置，详见上面的，数据格式/单元格格式。
    • image-20240609110041560

文本转数值 ​

  • 数字位数如果过多，就会以科学记数法展示

  • 不想以科学记数法展示，可以转换为文本类型

  • 但是计算时记得转换回数值型

    • 可以乘数字1
    • 或使用value（）函数

  • bool类型转数值型，也是乘1即可

日期标准化 ​

  • 除了点点点的方式
  • 可以用TEXT（）+ 自定义格式的方式，后面讲，之后再来补笔记

格式刷批量删除单引号 ​

  • 选中空白单元格

  • 双击开始里左上角的格式刷

  • 选中要清理格式的区域

  • 出现小刷子符号后单击，即统一清空了单元格的乱七八糟的格式

    image-20240609112240658

快速求和运算（command + Shift + T） ​

  • 选中要运算的单元格的下方单元格
  • 按（command + Shift + T）自动运算单元格上方的求和

冻结单元格 ​

当列或行行太多时，鼠标滑轮滚动，但列名或行名那一行或列不动，方便查看数据

  • 选中交叉点的位置，如下图，aaa的位置就是交叉点

  • 点击视图 -> 冻结窗格

    image-20240609113219885

选择性粘贴 ​

  • 如果新的一列是使用公式根据某一列计算的
  • 此时如果将原先的列删掉，新的列会报错
  • 但又不想显示原先的列
  • 此时需将根据公式计算后的结果，选择性粘贴，然后删除原列
  • 举个例子，如下图所示，删除金额列，增加比例（25%）的列会报错
image-20240609132712744

解决上述问题，两种方法

方法一： ​

  • 选中计算后的列，复制，选择性粘贴 -> 值

方法二： ​

  • 空白单元格输入1.25，command+c复制
  • 选中金额列，command + shift + 下箭头，右键选择性粘贴 -> 选择性粘贴
  • 选择乘
image-20240609134058274image-20240609134144781

将两列数据合并为一列 ​

使用选择性粘贴解决，想达到的效果如下图

image-20240609144718617
  • 先隔行插入空行
  • 再复制，选择性粘贴 -> 选择性粘贴 -> 跳过空单元

查找特定单元格 ​

比如查找全部绿色单元格

  • command + a 选中表格范围

  • 开始 -> 查找和选择 -> 查找 -> 格式 -> 从单元格选择格式

替换 ​

记得选择单元格范围

辅助列 ​

每隔一行插入一个空行

  • 插入一个辅助列，给行号的值，和比没行行号略大一点的值
  • 选中辅助列，排序
  • 选择拓展选定区域

圈释无效数据 ​

  • 先使用数据验证设置数据验证条件
  • 点数据 -> 数据验证 -> 圈释无效条件
image-20240609145703497

快速填充 ​

只要文本有固定的格式，都可以使用快速填充，提取信息

  • 先填入沈阳
  • 鼠标右键按住下拉，点快速填充
  • 或者，选中沈阳下面的空白单元格，按 crtl + e ，快速填充
image-20240609150303124

  • 效果如下

    image-20240609150426108

删除重复项 ​

  • 选中指定区域
  • 数据 -> 删除重复项

合并计算 ​

两张表的计算，比如通过上半年销量，和下半年销量，计算全年销量

  • 选择一个空白位置，点击数据 -> 合并计算

  • 选择计算区域，和计算方法，即可

    image-20240609152735504

合并重组 ​

image-20240610112431914

  • 一样的，先手打一个张经理
  • 然后右键下拉，选择快速填充