Excel数据分析基础操作_小白入门笔记

---

前言

  Excel小技巧记录

一、快捷键

1.添加行

  按住shift鼠标移到左侧单元格下缘，变成双横线箭头后往下随便拖动几行就是添加几行。

2.快速筛选

  cntr+shift+L

3.快速定位选中

  到a1单元格：cntr+⬆️⬅️
  连续选中：cntr+shift+⬇️➡️

二、引用

1.转换公式中的单元格及区域的引用方式

• 选中整个公式或单元格按下F4键，单元格中间和前面均有美元符号，表示对横、纵行单元格均进行绝对引用。
• 第二次按下F4键，仅单元格字母数字之间有美元符号，表示对横行进行绝对引用，列相对引用。
• 第三次按下F4键，字母前美元符号，表示对列绝对引用。
• 第四次按下F4键时，回到初始状态。

2.同一Excel文件中引用另一个工作表

• 感叹号(！)将工作表名称与单元格地址分隔开
  如：= 数学分析!B3

• 如果工作表名称包含空格，则必须在引用中用单引号将该名称引起来。
  如：=‘Mathematic Analysis’!B3

3.查找

• VLOOKUP(查找值，查找区域，对应的数据区域内的列号，[匹配查找值和首列类型,精确匹配输入0])
  注：查找值必须在查找区域的首列
• MATCH(查找值，区域，[0])
  给出有个查找值，返回在指定范围内的相对行号或列号
• INDEX()
  与MATCH相反，根据索引号返回指定值
• OFFSET()
  偏引
  注：区域：ctrl+shift+enter

三、公式函数

1.逻辑

1. IF(logical_test,[value_if_true],[value_if_false])
   logical_test:逻辑表达式，比如，判断A2的值是否大于A1的值，本参数可以使用任何比较运算符。也可以使用函数的返回值，比如用and函数的返回值作为第一个参数。
2. AND(logical1,logical2,…，Logical1, logical2,…表示待检测的 1到 N 个条件表达式。参数说明:所有参数的逻辑值为真时，返回TRUE
3. OR(logical1,logical2,…) 一真都真

2. MOD

MOD(number,divisor)。Number必需，要计算余数的被除数。Divisor必需，为除数。
注：当除数和被除数的符号不一样时，即:一个为正一个为负。
余数=除数*(整商+1)-被除数。

四、数据透视

1.基础操作

1. 生成：（来源）选中数据，插入～数据透视表，（位置）可选中定位某个表的某单元格；
2. 修改操作：要定位到透视表任意单元格，上出现数据透视表工具；
3. 移动或删除：选中整个数据透视表；
4. 基础排序等等：选择某单元格，右击；

2.切片器和报表连接

1. 生成：选中数据透视表的某单元格，分析～切片器
2. 建立连接：右击切片器～选透视表

3.动态透视表

offset（目标单元格，行偏移，列偏移，[高：COUNTA(某一列)即：COUNTA(源数据!$A: $A)]，COUNTA(源数据！$1:$1)）

五、图表

1.基础图表

1. 比较类：柱形图、条形图、折线图、雷达图
2. 构成类：饼图（类别少）、树状图、旭日图（层级关系）
3. 分布、联系：散点图、气泡图（三个维度）
4. 时间序列图类：（折线图若变成俩图例俩线，如：销量、季度，轴标签仅年，可选择数据中删除季度图例，再编辑水平标签同时选中源数据的年和季度）
5. 统计类：直方图（频数）、箱型图（百分位数）
6. 增维分析图：如，先画一张两个维度的基础折线图，再画其中每个节点维度的扇形图，以图片形式粘贴到节点上。

2.公式动态图表

1. 文件～选项～自定义功能区～开发工具
2. 开发工具～插入～列表框～单元格内画控件
3. 右键选中可拖拽或者设置控件格式，如选中列
4. 复制源数据的行到空白处，作为动态表，如起始于G1
5. 在复制的表头行下面选中与源数据相同大小的区域，=OFFSET（A1,G1(获取动态偏移值)，0，动态表数据高度（如1），数据宽度）区域计算用ctrl+shift+enter
6. 选中动态数据表，插入图表
7. 选中控件～格式～上移一层，让控件悬浮于图表上
8. 右键选中控件～单机图表～右键组合