EXCEL 笔记

该记录包含：

　　Excel 便捷操作、排序、匹配、条件、规划求解、模拟分析、统计分析七个模块。

一、便捷操作

合并计算：

数据 - 合并计算 - 求和 / 计数 / 平均值 - 添加区域（仅含表头&数据）- 首行&最左列

多重合并计算：

ALT+D+P，后续创建数据透视表

点击表格的 “项” 单元格，数据透视表分析 - 字段、项目和集 - 计算项

分类汇总：

数据 - 分类汇总

先对列先排序！让相同的类在一起~

区域：仅含表头&所有数据，或单击表中某个数据

分类字段：按班级分类

汇总方式：平均值

选定汇总项：每门课成绩（语数英）

高级筛选：

数据 - 高级筛选

多条件：复制表头，相同行表示”且“，不同行表示”或“

复合饼图：

饼图右键-设置数据系列格式-第二绘图区的值为最后x个字段

饼图右键-设置数据标签格式-值、百分比、类别名称

调节坐标刻度：

右键坐标轴-设置坐标轴格式

复制&粘贴：

需要的话，设为绝对引用~

---------------------------------------------

二、排序

rank() 参数：

1.排序的某个单元格，比如某人成绩

2.排序的范围，比如成绩列，设为绝对引用！

3.0 -- 降序，1 -- 升序

多条件排序：

数据 - 排序 - 添加条件 - 主要关键字、次要关键字

---------------------------------------------

三、匹配

lookup() 参数：

功能类似xlookup~

1. 特定查找值，比如88分成绩

2. 查找范围，比如成绩列

3. 返回范围，比如姓名列

近似匹配，先从小到大排序！返回比指定值小的最大值~

vlookup() 参数：

1. 查找值

2. 范围（含查找&返回范围）

3. 返回列序号

4. 1 -- 近似匹配，0 -- 精确匹配，默认近似匹配~

按列查找！

hlookup() 参数：

1. 查找值，比如 “Axles”

2. 范围（含查找&返回范围）

3. 返回列序号

4. 1 -- 近似匹配，0 -- 精确匹配，默认近似匹配~

按行查找！

---------------------------------------------

四、条件：

countif() 参数：

1. 范围，比如成绩列

2. 条件，比如 “>=90” ，打引号！

countifs() 参数：

1. 范围1

2. 条件1

3. 范围2，可与范围1相同

4. 条件2

if() 嵌套：

1. 条件1，比如 A2>=85

2. 成立返回值，比如 “优秀”

3. 不成立返回值，比如 if (A2>=85, “优秀”, if (...))

---------------------------------------------

五、规划求解：

数据 - 规划求解

1. 目标单元格（到最值/目标值）

2. 可变单元格

3. 添加约束（非负/整数约束）

可多变量求解（求方程的根），无约束条件！

贷款问题：同时运用 pmt() 函数，注意目标值贷款额为负值~

---------------------------------------------

六、模拟分析：

1.单变量求解：利用一个变量对另一个关联变量的影响

数据-模拟分析-单变量求解（求方程的根）

目标单元格、目标值、可变单元格

---------------------------------------------

七、统计分析

描述统计：

数据 - 数据分析 - 描述统计

方差：var()

标准差：stdev()

平均数：median()

众数：mode()

四分位数：quartile(array, quart)，求总体前25%的收入值

quart=0：最小值，quart=1：第一个四分位数

quart=2：中位数，quart=3：第三个四分位数

quart=4：最大值 （最好先排序）

偏度：=SKEW (y列)

峰度：=KURT (y列)

截距：intercept (y列,x列)

斜率：slope (y列,x列)

判定系数：rsq (y列,x列)

算术平均数：average()

调和平均数：harmean()，一般用于调和不同价格/速度

几何平均数：geomean()，一般用于求增长率

统计量选择：

1. 单样本总体均值的检验：

大样本：均为z检验

小样本：σ已知 - z检验，σ未知 - t检验

2. 单样本总体方差的检验：

f检验：双样本需先用该检验，判断两总体方差是否相同，再进行总体均值检验（同方差 t 检验、异方差 t 检验）

单样本手动计算，双样本点击数据 - 数据分析 - f检验自动求解

3. 双样本：

双样本（非配对）：同方差t检验、异方差t检验（小样本）、平均差z检验（大样本，方差已知）

双样本（同一样本，处理前后）：平均值成对t检验

统计量计算：

1. 根据 z 计算 p：

双侧 2*(1-normsdist( z的绝对值 ))，单侧 (1-normsdist( z的绝对值 ))，若小于0.05则拒绝原假设

2. 根据 α 计算 z：

normsinv(α) 和 normsinv(1-α)，是标准，若z值不在标准内则拒绝原假设

3. 根据 α 计算 t：

tinv(α,n-1) 和 tinv(1-α,n-1) -- 双尾

t.inv(α,n-1) 和 t.inv(1-α,n-1) -- 左尾

原假设单双侧判断：

降低：左侧检验

提高：右侧检验

变化：双侧检验

方差分析：

单因素方差分析：单个因素的影响

双因素方差分析：多个因素的影响

无重复双因素方差分析：无重复观测

可重复双因素方差分析：有重复观测，比如在同一路口高峰期和非高峰期均观测

回归方程假设：

H0：β0=β1=...=βp=0线性关系不显著

H1：β0，β1，...，βp至少有一个不等于0

回归系数假设：

H0：β1=0

H0：β1≠0

注：数据列必须竖着摆放~