1.熟练使用Excel、SQL、Python（1）

原创于 2025-07-17 23:33:07 发布 · 626 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#excel

Excel

1.基础操作与数据规范

基础是高效分析的前提，确保数据 “可处理、可分析”

单元格操作：
- 熟练掌握单元格的选中（单个、区域、整行 / 列）、插入 / 删除、合并 / 拆分、隐藏 / 显示。
- 理解单元格引用方式：相对引用（A1）、绝对引用（$A）、混合引用（A1或A$1），这是函数计算的基础。
数据录入与规范：
- 数据类型识别：文本、数字、日期、时间、逻辑值（TRUE/FALSE）的正确录入（避免 “文本型数字” 导致计算错误）。
- 数据验证（数据有效性）：限制录入范围（如日期必须在 2023 年内、数值必须为正数）、创建下拉菜单（规范选项，减少录入错误）。

2、数据清洗（核心能力）

数据分析中 80% 的时间用于清洗数据，Excel 提供了丰富的工具：

基础清洗功能：
- 查找与替换（Ctrl+H）：批量替换特殊字符（如空格、换行符）、统一格式（如将 “张三”“zhangsan” 统一为 “张三”）。
- 去除重复值：快速定位并删除重复记录（“数据” 选项卡→“删除重复值”），保留唯一值。
- 文本处理函数：
  - TRIM()：去除多余空格（如用户录入的 “张三”→“张三”）。
  - LEFT()/RIGHT()/MID()：提取文本片段（如从 “2023-10-01” 中提取年份 “2023”）。
  - PROPER()/UPPER()/LOWER()：统一大小写（如将 “excel”→“Excel”）。
  - TEXT()：将数字 / 日期转换为指定格式（如TEXT(TODAY(),"yyyy年mm月dd日")）。
- 日期与时间处理：
  - 识别 Excel 日期本质（1900 年 1 月 1 日为 “1”，后续日期为累加值），避免 “文本型日期”（如 “2023/10/01” vs “2023-10-01” 的兼容处理）。
  - 函数：TODAY()（当前日期）、NOW()（当前日期时间）、DATEDIF()（计算两个日期差，如年龄、天数）。
- 分列功能：将不规则数据拆分为规范列（如 “张三 - 2023-10-01 - 北京” 拆分为 “姓名”“日期”“城市” 三列）。

3、数据计算与函数（核心工具）

函数是 Excel 的 “灵魂”，数据分析师需掌握高频函数及组合用法：

基础计算函数：
- 求和（SUM()）、平均值（AVERAGE()）、计数（COUNT()/COUNTA()/COUNTBLANK()）、最大值（MAX()）、最小值（MIN()）。
条件计算函数：
- SUMIF()/SUMIFS()：按条件求和（如 “计算北京地区销售额总和”“计算 2023 年 10 月且销售额 > 1000 的订单总和”）。
- COUNTIF()/COUNTIFS()：按条件计数（如 “统计逾期未付款的订单数”）。
- AVERAGEIF()/AVERAGEIFS()：按条件求平均（如 “计算上海地区用户的平均消费金额”）。
逻辑函数：
- IF()：单条件判断（如IF(A1>100,"达标","未达标")），支持多层嵌套（最多 64 层，处理复杂逻辑）。
- AND()/OR()：组合条件（如IF(AND(A1>100,B1="北京"),"重点客户","普通客户")）。
- IFERROR()：捕获错误值（如IFERROR(VLOOKUP(...),"未找到")，避免 #N/A 影响报表）。
查找与引用函数：
- VLOOKUP()：垂直查找（如 “根据订单号查找客户姓名”），注意第三参数（返回列序号）和第四参数（精确匹配 0/FALSE）。
- HLOOKUP()：水平查找（适用于表头在首行的场景）。
- INDEX()+MATCH()：替代 VLOOKUP 的更灵活组合（支持反向查找、多条件查找），如INDEX(结果列,MATCH(条件1&条件2,条件列1&条件列2,0))。
- OFFSET()：动态返回指定偏移量的单元格区域（常用于动态图表）。
其他高频函数：
- CONCAT()/TEXTJOIN()：合并文本（如TEXTJOIN(";",TRUE,A1:C1)，忽略空白）。
- RANK()/RANK.EQ()：排名（如 “计算各产品销售额的排名”）。

4、数据透视表（高效分析利器）

数据透视表是快速汇总、分析大规模数据的核心工具，必须精通：

基础操作：
- 插入数据透视表：选择数据源，指定放置位置（新工作表 / 现有工作表）。
- 字段布局：将字段拖入 “行”“列”“值”“筛选器” 区域，快速生成汇总表（如 “按地区 + 月份汇总销售额”）。
- 计算方式调整：值字段设置（求和、平均值、计数、占比等，如 “计算各产品销售额占比”）。
进阶技巧：
- 分组功能：对日期（按年 / 季 / 月分组）、数值（按区间分组，如 “0-1000”“1000-2000”）分组分析。
- 切片器与日程表：插入切片器（点击 “插入切片器”），通过点击快速筛选数据（替代传统筛选，更直观），日程表专门用于日期筛选。
- 计算字段与计算项：在数据透视表中添加自定义计算（如 “毛利率 =（销售额 - 成本）/ 销售额”）。
- 处理空白与错误值：通过 “数据透视表选项” 隐藏空白或错误，优化报表美观度。

5、数据可视化（结果呈现）

将分析结果转化为直观图表，需掌握：

图表类型选择：
- 比较数据：柱状图（分类比较）、折线图（趋势变化）、雷达图（多维度对比）。
- 占比数据：饼图（单组占比）、环形图、堆叠柱状图（多组占比）。
- 相关性：散点图（变量关系）、气泡图（三维数据）。
图表美化与优化：
- 基础设置：标题、坐标轴标签、图例、数据标签（显示具体数值）。
- 细节调整：删除冗余网格线、调整颜色（用品牌色或对比色）、隐藏无意义 “0 值”。
- 动态图表：结合 “数据验证”（下拉菜单）+OFFSET()函数（动态数据源），或切片器，实现 “选择条件自动更新图表”。
条件格式：
- 用颜色、图标直观展示数据分布：数据条（长度代表数值大小）、色阶（颜色深浅代表数值高低）、图标集（箭头 / 星级表示趋势）。
- 示例：为销售额列添加 “绿色数据条”，快速识别高值；为库存列添加 “红色箭头↓” 标记低于安全库存的产品。

6、进阶功能（提升效率与处理复杂场景）

函数组合技巧：
- 文本函数 + 逻辑函数 + 查找函数：处理非结构化数据（如从 “订单号_20231001_张三” 中提取日期和姓名，并匹配客户等级）。
- 动态数组函数（Excel 365/2021 支持）：
  - FILTER()：按条件筛选数据（如FILTER(数据源, 条件1*条件2)，返回符合条件的所有行）。
  - SORT()/SORTBY()：排序数据（如SORT(销售额数据, 销售额列, -1)按销售额降序）。
  - UNIQUE()：提取唯一值（如UNIQUE(客户地区列)获取所有地区列表）。
  - SEQUENCE()：生成序列（如SEQUENCE(12,1,2023,1)生成 2023-2034 年）。
Power Query（数据清洗引擎）：
- 用于处理多来源、不规则数据（如多个 Excel 文件、CSV、网页数据），支持：
  - 合并查询（纵向追加 / 横向合并）、拆分列（按分隔符 / 长度）、替换值、填充空值。
  - 添加自定义列（用公式计算新字段）、分组依据（按字段汇总）。
  - 优点：步骤可复用，数据源更新后 “刷新” 即可自动执行清洗，避免重复操作。
Power Pivot（大数据分析）：
- 处理超 100 万行的数据（突破 Excel 行限制），建立数据模型（表关系），用 DAX 函数计算（如 “同比增长率”“累计销售额”）。
- 适合多表关联分析（如 “订单表”“客户表”“产品表” 关联后，分析 “客户所在地区的产品偏好”）。

7、自动化与效率工具

快捷键：大幅提升操作速度，必背高频快捷键：
- 单元格操作：Ctrl+C（复制）、Ctrl+V（粘贴）、Ctrl+X（剪切）、Ctrl+Z（撤销）。
- 函数与数据：Ctrl+F（查找）、Ctrl+H（替换）、Alt+=（自动求和）、Ctrl+Shift+Enter（数组公式，旧版本）。
- 数据透视表：Alt+N+V（插入数据透视表）、Ctrl+Shift+L（筛选）。
宏与 VBA：
- 录制宏：将重复操作（如每月报表格式调整）录制成宏，一键执行。
- 简单 VBA 代码：修改宏代码实现更灵活的自动化（如批量重命名工作表、按条件删除行）。
模板：制作标准化模板（含固定格式、函数、数据透视表），避免重复劳动（如周报 / 月报模板）。

8、数据管理与安全

外部数据连接：导入 CSV、TXT、数据库（SQL Server/Access）数据，设置 “刷新全部” 更新数据。
数据保护：
- 工作表 / 工作簿加密（“审阅”→“保护工作表”，设置密码限制编辑）。
- 隐藏敏感数据（如将身份证号列设置为 “;;;” 格式隐藏，或通过 VBA 加密）。
数据表（模拟分析）：
- 单变量求解：已知结果反推输入值（如 “要达到月利润 10 万，销售额需多少”）。
- 双变量分析：通过数据透视表的 “模拟运算表”，展示两个变量对结果的影响（如 “不同价格和销量下的利润表”）。