Excel数据分析

2024/11/06 program 共 1652 字,约 5 分钟

基础功能

  • 去重:数据 - 删除重复值。
  • 百分比转小数:选择表格后,鼠标悬停,前面会出现一个黄色的提示三角:此单元格中的数字为文本格式,或者其前面有撇号。点击,然后点:转换为数字
  • 乱序:新建一列,输入公式:=RAND(),复制公式,然后排序。

常用公式

Excel 是一个功能强大的电子表格软件,它提供了许多内置公式来帮助用户进行数据分析和处理。以下是一些常用的Excel公式:

  1. 求和(Sum)
    • 用法:=SUM(数值1, [数值2], ...)
    • 说明:计算所有数值的总和。
  2. 平均值(Average)
    • 用法:=AVERAGE(数值1, [数值2], ...)
    • 说明:计算所有数值的平均值。
  3. 最大值(Max)
    • 用法:=MAX(数值1, [数值2], ...)
    • 说明:找出给定数值中的最大值。
  4. 最小值(Min)
    • 用法:=MIN(数值1, [数值2], ...)
    • 说明:找出给定数值中的最小值。
  5. 计数(Count)
    • 用法:=COUNT(数值1, [数值2], ...)
    • 说明:计算范围内包含数字的单元格数量。
  6. 计数非空单元格(CountA)
    • 用法:=COUNTA(值1, [值2], ...)
    • 说明:计算范围内非空单元格的数量。
  7. 查找匹配项(VLOOKUP)
    • 用法:=VLOOKUP(查找值, 表, 列索引, [是否精确匹配])
    • 说明:在一列中查找特定值,并返回同一行中另一个列的值。
  8. 横向查找匹配项(HLOOKUP)
    • 用法:=HLOOKUP(查找值, 表, 行索引, [是否精确匹配])
    • 说明:在一行中查找特定值,并返回同一列中另一个行的值。
  9. 随机数(Rand)
  10. 条件求和(SumIf)
    • 用法:=SUMIF(范围, 条件, [求和范围])
    • 说明:对满足条件的单元格进行求和。
  11. 条件计数(CountIf)
    • 用法:=COUNTIF(范围, 条件)
    • 说明:对满足条件的单元格进行计数。
  12. 条件平均值(AverageIf)
    • 用法:=AVERAGEIF(范围, 条件, [平均值范围])
    • 说明:计算满足条件的单元格的平均值。
  13. 日期差(DATEDIF)
    • 用法:=DATEDIF(开始日期, 结束日期, 单位)
    • 说明:计算两个日期之间的差异。
  14. 文本连接(Concatenate)
    • 用法:=CONCATENATE(文本1, [文本2], ...)
    • 说明:将多个文本字符串连接成一个。
  15. 查找字符位置(FIND)
    • 用法:=FIND(要查找的文本, 在哪个文本中查找, [起始位置])
    • 说明:查找一个文本字符串在另一个文本字符串中的位置。
  16. 替换文本(SUBSTITUTE)
    • 用法:=SUBSTITUTE(文本, 旧文本, 新文本, [替换第几个])
    • 说明:替换文本中的旧字符串为新字符串。
  17. 四舍五入(Round)
    • 用法:=ROUND(数字, 小数位数)
    • 说明:将数字四舍五入到指定的小数位数。
  18. 绝对引用($)
    • 说明:在复制公式时,保持单元格引用不变。
  19. 逻辑判断(IF)
    • 用法:=IF(条件, 条件为真时的结果, 条件为假时的结果)
    • 说明:根据条件返回两个结果中的一个。

数据透视表

Excel表格- 插入 - 数据透视表。

  1. 统计某维度的占比。勾选:回款,渠道。再把回款拖到列值里去,选择数据显示方式,总计的百分比。
  2. 各个维度的统计及线索占比
    1. 选择字段:产品,回款,来源。
    2. 再拖放一个 回款里面,值显示方式选择:父行汇总的百分比
  3. 按照年度+季度的维度统计分析各个团队的回款情况:
    1. 把时间维度也拖进来,右键表格选择:创建组。
    2. 然后多选:季度和年。配置后应该是这样的,行:年,回款时间,子产品,团队。值:求和项回款金额/元。
    3. 显示效果是:年份季度团队,求和项回款金额。

碰撞检测/交集

例如A列为IP地址列表,B列为待检测的IP地址列表,求B在A中出现的IP地址。步骤:

  1. 去重。先对A列和B列内容去重,可以在另外一个sheet里操作,如果有多张表,可以先合并后去重。
  2. 编辑公式。增加C列,填上公式:=VLOOKUP(A1, B:B, 1, FALSE)
  3. 复用公式。选中A列,然后CTRL + ↓ 滚动到最后一行,在C列最后一行复制公式,然后选中改行后按下shift,鼠标拖动滚动条到顶部全部选择,复制上公式即可。
  4. 排序。C列按照升序即可。

文档信息

Search

    Table of Contents