一.数据预处理
1.日期整理:假定A列为年数据,B列为月数据,C列为日数据,转换成一列日期的公式:=DATE(A1,B1,C1)填充
2.将一列数据分为多列:选中数据(可多行不可多列)“数据”-分列
3.对于按照日期统计的数据,有些日期的数据因为暂时没有统计发生缺失,需要按日期顺序将数据补全,如下图中,B列中数据不是每天都有,不利于数据统计分析,所以创建D列将日期按顺序排列,如果日期是B中有的,则将C列中对应的数据填入,如果没有则填入缺失,此时需要借助函数,在E2处写函数“=IF(ISERR(VLOOKUP(D2,B:C,2,)),"0",VLOOKUP(D2,B:C,2,))”,回车,得到结果;然后下拉列表,得到全部完整日期。(但似乎VLOOKUP函数只能用于相邻两列数据的查找)
4.EXCEL数列中包含缺失值(#N/A)的求和:
=SUMIF(J2:J17,"<>#N/A") <>表示“非”,即对J2:J17中不为空值的数求和
二.EXCEL模拟数据分析
1.模拟分析
(1)单变量求解,计算公式一般都是已知的
例如已知单价、成本,问销售多少才能获得相应的利润,利润所在地方就是目标单元格(公式在目标单元格),所设定的值就是目标值,销售量就是可变单元格(特点就是只有一个可变量)
(2)模拟运算:分为单变量模拟运算(比单变量求解运算量大一些)与双变量模拟运算
单变量模拟运算:首先选中待运算的数据区域(包含所有变量),选择模拟运算表,最终的区域是单元格,一般是列(A2的输出是$A$2)
双变量模拟运算:步骤类似,引入行的单元格与引入列的单元格表示两个不同的变量。(左上角必须是计算公式),
(3)方案管理:针对多个变量
使用公式之前首先需要命名(可以使公式更加直观),“公式”栏中有名称管理器.
使用时,添加方案,方案名可以随便取,可变单元格是自变量的几个单元格,因变量不包含其中;添加可变单元格后“确定”,进入方案变量值,就可以按照方案显示结果,选择“摘要”可以显示所有方案的结果
三.预测工作表
选中数据(横坐标及对应数据)、预测工作表、选择参数(开始日期、结束日期、置信区间、缺失点补充方式、聚合重复项选择方式)、点击创建生成对应数据报表