数据分析（二）Excel

Excel，可视化
办公软件
发布日期:   2022-04-25
文章字数:   2k
阅读时长:   7 分
阅读次数:  

数据分析（二）Excel

一、数据录入

录入方式

  1. 手动输入
    Tab横向移动单元格，Enter纵向移动单元格。
    Enter + Tab移动到下一行第一列，Shift + Tab/Enter回到上一单元格。

  2. 网络获取
    只能爬取简单结构化数据：本身为表格形式。
    数据→自网站→输入网址→选中区域→加载（全部刷新/连接属性下可选实时刷新）。

单元格格式

1. 原有格式操作

  1. 清除格式：选中单元格→开始→编辑→清除→清除格式。
  2. 跨列居中：代替合并居中，保持原单元格，尽量避免合并居中。
  3. 自动换行：完整显示内容。
  4. 调整列宽：开始→格式→自动调整列宽，或者鼠标移动到列首右边线成十字左右箭头双击鼠标左键。
  5. 复制粘贴后显示内容不一样→调整格式，输入分数：0+Space+1/2。
  6. 单元格实际内容为上方编辑栏内容，可自定义格式，加单位格式后仍可计算。

2. 自定义格式

占位符
  1. #，0：数字占位符。
  2. @：文本占位符。
自定义方法
  1. 数字格式

    • 示例：0.00显示小数点后两位。

    • 示例：[红色][>=20]0;[蓝色][<20]0;0
      [红色]为颜色代码，<=>为条件运算符（可做数据筛选）

    • 示例：”正”0;”负”0;”零”0;@ 或者 ↑0;下0;空;@
      数字格式4个区域，0;0;0;@ 依次为 正数;负数;零;文本

  2. 文本格式
    • 示例：@”元”，单元格文本内容后附加显示元
      @为文本占位符
  3. 时间格式
    • 示例：m 只显示月
      y-m-d 改变ymd顺序和数量改变时间显示方式

3. 默认格式

  1. 文本默认左对齐，数字默认右对齐，状态值默认居中（true/false）。
  2. 日期格式：2022-01-01或者2022/01/01（但别用日期格式存储日期）。

二、数据处理

数据验证

  1. 规范数据输入内容
    • 示例：选中单元格→数据→数据工具→数据验证→验证条件→序列（序列内容）
      可实现填写数据时出现下拉列表。
  2. 数据验证后可圈选不合格数据。

条件格式

  1. 开始→样式→条件格式
    突出显示符合条件的单元格。

查找/定位

  1. 开始→查找和选择→转到/定位条件
    • 查找只能确定具体单元格，定位可寻找符合条件的所有单元格再进行操作。
    • 可以先选中区域再进行查找（Ctrl+F），不影响其他区域。
    • 打开查找选项，有更详细的查找功能，可查到全部并替换全部。
    • 查找/替换可结合通配符使用。

排序/筛选

1. 排序

  1. 数字排序：点选需要排序列的任一单元格，点击排序可实现快速排序。
  2. 文本排序：默认首字母排序，可选笔划和自定义排序。

2. 筛选

  1. 文本筛选：可连续筛选，第二次筛选将“添加到筛选器”框选。
  2. 数字筛选：可点击筛选，也可以使用RANK()函数。

拆分

位置：数据→数据工具→分列

  1. 选中区域分列，单元格内容为中文时可先转文本再分列，功能在分列向导下。
  2. 利用函数拆分
    • 示例：将数据”1234人”文本和数字拆分，只要数字，数据单元格为E2
      =LEFT(E2,LEN(E2)*2-LENB(E2))
      函数中嵌套LEN()和LENB()函数，LEN统计为5，LENB为6（文本占两个字符）

公式/函数

位置：开始→公式（所有公式和函数）

1. 公式

  1. 求和
    • 示例：=A1+K1 第一行求和
      快捷键：拉选区域→Alt+=→快速求和
  2. 求积
    • 示例：=A1*2
  3. 合并单元格
    • 示例：=A2 & B$1 & B2
      合并多个单元格的内容，B$1为B列的列名

2. 函数

  1. 输入函数名→Ctrl+A 调出详细函数参数。
  2. 点击单元格→编辑栏显示行列→A1/B2→行列前加$可固定行/列 相对引用/绝对引用。
    • 示例：=$A1*B$2 固定第A列，第2行 练习方法：九九乘法表
统计函数
  1. COUNT:计算区域中包含数字的单元格的个数。
    COUNTA:计算区域中包含非空单元格的个数。
    COUNTBLANK:计算区域中空单元格的数目。
    COUNTIFS:多条件判断。
    COUNTIF:计算区域中符合条件单元格的数目。
    • 示例：=COUNTIF(A1:A15,”张*”) 统计区域中姓张人数
      统计文本内容时，用到通配符*,?,~,三个分别为任意多个字符，任意一个字符，转义字符
求和函数
  1. SUM:求和。
    SUMIF:附加条件的求和,(Range,Criteria,Sum_range)参数分别为判断区域，判断条件，求和范围。
    SUMPRODUCT:数组相乘求和。
    • 示例：=SUMPRODUCT(A2:A10,SUMIF(B2:B20),判断条件/列，C2:C20)
      函数嵌套后结果行数与主函数函数相等，数组分别相乘求和
  2. AVERAGE:求平均数。
    AVERAGEIF:附加条件的求平均值。
  3. SUBTOTAL:11个可选求值参数，原数据筛选后返回值实时更新。
其它函数
  1. IF：判断函数。
    • 示例：IF:(A1>0,”大于0”,”小于0”) 可嵌套使用。
  2. MATCH:定位单元格。
  3. INDEX:返回指定单元格值。

图表可视化

1. 插入图表

  1. 点选表中任一单元格→插入数据透视表→将字段拖入视图（右下角）→在透视表插入所需图表。
  2. 插入切片器→选择字段：进一步筛选并更新图表，切片器全局有效。

三、打印

  1. 打印选中区域
    文件→打印→设置→打印选中区域
  2. 打印完整页面
    文件→打印→设置→缩放或者调整页边距实现打印完整页面
  3. 居中打印
    文件→打印→设置→页面设置→页边距→居中方式→水平+垂直
  4. 分页打印标题
    页面布局→页面设置→启动器（右下三角）→工作表→拉选顶端标题行

四、快捷键

实用快捷键

Ctrl + 方向键：快速定位/选择

  1. 选中第一个单元格，Shift+第二个单元格，快速选中对角区域。
    shift选中首尾可实现连选。
  2. Ctrl+方向键，快速跳转到最*端。
  3. Ctrl+Shift+方向键，选中整行/列。

Ctrl + E：快速填充

填写第一行后，Ctrl+E快速填充,可实现功能：提取，合并，拆分，重组。简单规律数据填写一个便可填充，稍复杂的可多手动填写几个数据。

鼠标移动到单元格右下角成十字下拉填充，也可实现快速填充，Ctrl + 下拉可实现序列填充，单独下拉可以继承公式。F4锁定行列，公式和函数中使用，可按三次 $，下拉填充函数出错时，考虑相对引用/绝对引用。

  • 提取：

  • 合并：

  • 拆分：

  • 重组：

Ctrl + G：定位

  • 定位空行

    实现对空行批量操作，删除，填充，求和等。

  • 定位行、列内容差异单元格

Alt + =：快速求和

可先拉选区域→Alt+=→快速求和，也可先快速求和再选择区域。

Ctrl + q：快速分析

格式化、图标、汇总、表格、迷你图，具体功能可以参考弹出界面。

Ctrl + 1：设置单元格格式

Ctrl + Enter：原位填充

所有选中的单元格执行一样的操作。

Ctrl + ~：显示表中的公式

按一次显示表中所有公式，再按一次恢复（可定位）。

Ctrl + F/H：查找/替换

Ctrl + Shift + L：筛选

按一次进入筛选界面，再按一次退出。

Ctrl + PgUp/PgDn

切换工作表。

Ctrl + ; 和 Ctrl + Shift + ;

显示日期和时间。

Ctrl + p：打印

通用打印快捷键。

Excel，可视化
  目录