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

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

posted @ 2022-11-10 18:12  找回那所有、  阅读(277)  评论(0编辑  收藏  举报
这里到底了哦~(●'◡'●)