如何在EXCEL里进行不同数据表之间运算_百度文库
赠送免券下载特权
10W篇文档免费专享
部分付费文档8折起
每天抽奖多种福利
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
如何在EXCEL里进行不同数据表之间运算
阅读已结束，下载本文需要
想免费下载本文？
定制HR最喜欢的简历
你可能喜欢BeforeDoubleClick， ：在本工作表鼠标双击 之前 ，将触发 代码程序 代码： sheet1.cells（1,3）就是指第一行第三列的这个单元格；Sheet1.Cells(1, 1)就是指第一行第一列的这个单元格；S...
EXCEL交流QQ群
友情链接：求教，如何用excel表格做统计数据预测？111人已关注
山东财经大学
I am i！我以一个简单的例子来帮你“入门”如何做数据预测：【例】如下图所示A为产品数量，B列是对应的单个产品成本。要求计算：当生产50个产品时，相对应的成本是多少？方法1：使用TREND函数。公式：=TREND(OFFSET(B1,MATCH(D2,A:A)-1,,2),OFFSET(A1,MATCH(D2,A:A)-1,,2),D2)公式说明：Trend函数是做线性预测的函数，但本例中的A列和B列并非线性关系(y=ax+b)。所以需要分段插值。即在A列查找到相邻的小值和大值。如50为13~68。MATCH(D2,A:A)-1：利用match函数的模糊查找功能，找到比样本小且最接近的值。如比50小的是13OFFSET(B1,match()-1,,2)：用offset返回小值和大值的所在B列区域。如50对应B列的是B5:B6，同理A列的区域A5:A6D2：是样本值。本例是50Trand函数预测的结果是：76.64方法2：利用LINEST函数公式：=D2*INDEX(LINEST($B$2:$B$8,$A$2:$A$8),1)+INDEX(LINEST($B$2:$B$8,$A$2:$A$8),2)LINEST():如果我们知道A列和B列对应的线型关系式(y=ax+b)，那么我们可以直接把X值代入求值。而LINEST函数可以根据两组数据，直接取得a和b的值。如本例：LINEST($B$2:$B$8,$A$2:$A$8)可以返回{-0.05,85.97},其中-0.05是a,85.9是b。那么关系式出来: y=-0.05x+85.9INDEX(linest（）,1)可以取值第一个值，即a的值。同理当为2时可以取出b的值。方法2预测的结果是：83.39（由于预测原理不同，结果和方法不相同是正常的）我以一个简单的例子来帮你“入门”如何做数据预测：【例】如下图所示A为产品数量，B列是对应的单个产品成本。要求计算：当生产50个产品时，相对应的成本是多少？方法1：使用TREND函数。公式：=TREND(OFFSET(B1,MATCH(D2,A:A)-1,,2),OFFSET(A1,MATCH(D2,A:A)-1,,2),D2)公式说明：Trend函数是做线性预测的函数，但本例中的A列和B列并非线性关系(y=ax+b)。所以需要分段插值。即在A列查找到相邻的小值和大值。如50为13~68。MATCH(D2,A:A)-1：利用match函数的模糊查找功能，找到比样本小且最接近的值。如比50小的是13OFFSET(B1,match()-1,,2)：用offset返回小值和大值的所在B列区域。如50对应B列的是B5:B6，同理A列的区域A5:A6D2：是样本值。本例是50Trand函数预测的结果是：76.64方法2：利用LINEST函数公式：=D2*INDEX(LINEST($B$2:$B$8,$A$2:$A$8),1)+INDEX(LINEST($B$2:$B$8,$A$2:$A$8),2)LINEST():如果我们知道A列和B列对应的线型关系式(y=ax+b)，那么我们可以直接把X值代入求值。而LINEST函数可以根据两组数据，直接取得a和b的值。如本例：LINEST($B$2:$B$8,$A$2:$A$8)可以返回{-0.05,85.97},其中-0.05是a,85.9是b。那么关系式出来: y=-0.05x+85.9INDEX(linest（）,1)可以取值第一个值，即a的值。同理当为2时可以取出b的值。方法2预测的结果是：83.39（由于预测原理不同，结果和方法不相同是正常的）
惊破霓裳羽衣曲我简单说一下我用excel表格做统计数据预测的几种一般步骤，供大家学习交流：一、绘制预测值如果只想了解预测趋势,可延伸前面创建的趋势线。选择图表，如果其中包含多个数据系列的图形，则单击要处理的数据系列。单击“布局&趋势线&其他趋势线选项”打开“设置趋势线格式”对话框在“趋势线选项”选项卡中单击“线性”。选中复选框“显示公式”。选中“显示R平方值”复选框。使用文本框“前推”指定要将趋势线向未来延伸多少个周期（例如，要显示下一年的季度销量数据，可将“前推”设置为4，从而将趋势线延伸4个季度&。单击“确定”按钮，Excel将插入趋势线并将其延伸到未来。二、使用填充柄延伸线性趋势线如果要预测中看到数据点，可使用填充柄将最佳拟合线延伸到未来，其步骤如下：选择工作表中的历史数据。单击并拖曳填充柄来扩大选取，Excel将根据现有的数据计算最佳拟合线，将该线延伸到未来，并计算未来值。使用“系列”命令延伸线性趋势也可使用“系列”命令来延伸最佳拟合线，其步骤如下。选择包含历史数据和用于存储预测数据的单元格的区域（预测数据单元格必须为空)。单击“开始&填充&系列”，Excel将打开“序列”对话框。选中单选框“自动填充”。单击“确定”按钮，Excel将使用最佳拟合预测值填充空单元格。三、使用回归方程进行预测“系列”命令也可用于生成定义整个最佳拟合线的数据，让用户能够查看实际趋势值，其步骤如下：将历史数据复制到相邻的行或列中。选择包含历史数据和用于存储预测数据的单元格的区域（同样，预测数据单元格'必须为空)。单击“开始&填充&系列”，Excel将打开“序列”对话框。选中复选框“预测趋势”。选中单选框“等差填充”。单击“确定”，Excel将把复制的数据替换为最佳拟合值，并将趋势延伸到空单元格中。在图16.10中，“系列”命令创建的趋势值存储在区域E2:E13中，且在图表中绘制了最佳拟合线。添加趋势线时显示的回归方程提供了出和!)的值，因此要确定新的y值，只需插入新的x值进行计算即可。例如，在季度销量模型中，Excel计算出如下回归方程：y=9800要确定第13季度的趋势值，可用13代替x:y=+259800结果为278099，这就是第13季度（2008年第1季度）的预测销量。四、使用TREND()函数进行预测TREND()函数也可用于预测新值。要延伸趋势并生成新值，需要指定TREND()函数的参数new_x’s,下面是在工作表中完成这项工作的基本步骤：在工作表中添加新的x值。例如，要将季度销量趋势延伸到下一财年，可在Period列中添加数字13~16。选择一个足以容纳所有新y值的区域。例如，如果要添加4个新值，则选择位于同一行或同一列（这取决于数据的结构）的4个单元格。将TREND()函数作为数组公式输入，并将包含新；c值的区域作为参数new_xs。下面是季度销量示例使用的公式：{=TREND(02：D13,C2：C13,C14:C17)}五、使用LINEST()函数进行预测前面介绍过，LINEST()函数返回趋势线的斜率和&&截距。知道这些数据后，预测新值，也可使用FORECAST()函数计算预测值：FORECAST(x,known_y,s,known_x,s)其中，X是要计算其火值的a：值，cnovwj_y’s和cnown_y*s与TREND()函数中相同（但参数是cnown_y’s不是可选的）.下面是一个例子：=F0RECAST(13,D2:DI3,C2:C13)就只需将它们和新的X值插入线性回归方程进行计算即可。例如，如果斜率存储在单元格H2中，截距位于12中，新的AT值位于C13中，则下面的公式返回预测值。我简单说一下我用excel表格做统计数据预测的几种一般步骤，供大家学习交流：一、绘制预测值如果只想了解预测趋势,可延伸前面创建的趋势线。选择图表，如果其中包含多个数据系列的图形，则单击要处理的数据系列。单击“布局&趋势线&其他趋势线选项”打开“设置趋势线格式”对话框在“趋势线选项”选项卡中单击“线性”。选中复选框“显示公式”。选中“显示R平方值”复选框。使用文本框“前推”指定要将趋势线向未来延伸多少个周期（例如，要显示下一年的季度销量数据，可将“前推”设置为4，从而将趋势线延伸4个季度&。单击“确定”按钮，Excel将插入趋势线并将其延伸到未来。二、使用填充柄延伸线性趋势线如果要预测中看到数据点，可使用填充柄将最佳拟合线延伸到未来，其步骤如下：选择工作表中的历史数据。单击并拖曳填充柄来扩大选取，Excel将根据现有的数据计算最佳拟合线，将该线延伸到未来，并计算未来值。使用“系列”命令延伸线性趋势也可使用“系列”命令来延伸最佳拟合线，其步骤如下。选择包含历史数据和用于存储预测数据的单元格的区域（预测数据单元格必须为空)。单击“开始&填充&系列”，Excel将打开“序列”对话框。选中单选框“自动填充”。单击“确定”按钮，Excel将使用最佳拟合预测值填充空单元格。三、使用回归方程进行预测“系列”命令也可用于生成定义整个最佳拟合线的数据，让用户能够查看实际趋势值，其步骤如下：将历史数据复制到相邻的行或列中。选择包含历史数据和用于存储预测数据的单元格的区域（同样，预测数据单元格'必须为空)。单击“开始&填充&系列”，Excel将打开“序列”对话框。选中复选框“预测趋势”。选中单选框“等差填充”。单击“确定”，Excel将把复制的数据替换为最佳拟合值，并将趋势延伸到空单元格中。在图16.10中，“系列”命令创建的趋势值存储在区域E2:E13中，且在图表中绘制了最佳拟合线。添加趋势线时显示的回归方程提供了出和!)的值，因此要确定新的y值，只需插入新的x值进行计算即可。例如，在季度销量模型中，Excel计算出如下回归方程：y=9800要确定第13季度的趋势值，可用13代替x:y=+259800结果为278099，这就是第13季度（2008年第1季度）的预测销量。四、使用TREND()函数进行预测TREND()函数也可用于预测新值。要延伸趋势并生成新值，需要指定TREND()函数的参数new_x’s,下面是在工作表中完成这项工作的基本步骤：在工作表中添加新的x值。例如，要将季度销量趋势延伸到下一财年，可在Period列中添加数字13~16。选择一个足以容纳所有新y值的区域。例如，如果要添加4个新值，则选择位于同一行或同一列（这取决于数据的结构）的4个单元格。将TREND()函数作为数组公式输入，并将包含新；c值的区域作为参数new_xs。下面是季度销量示例使用的公式：{=TREND(02：D13,C2：C13,C14:C17)}五、使用LINEST()函数进行预测前面介绍过，LINEST()函数返回趋势线的斜率和&&截距。知道这些数据后，预测新值，也可使用FORECAST()函数计算预测值：FORECAST(x,known_y,s,known_x,s)其中，X是要计算其火值的a：值，cnovwj_y’s和cnown_y*s与TREND()函数中相同（但参数是cnown_y’s不是可选的）.下面是一个例子：=F0RECAST(13,D2:DI3,C2:C13)就只需将它们和新的X值插入线性回归方程进行计算即可。例如，如果斜率存储在单元格H2中，截距位于12中，新的AT值位于C13中，则下面的公式返回预测值。
迷途漫漫，终有一归。我来给大家分享一种基于时间序列分析的excel应用的“草根”短期预测方法：【短期预测“模型”原理:在历史中寻找规律】首先，我们来看一下2008年至2016年乘联会批发量的时间序列图。就酱紫看过去，数据貌似是无规律地在跳动，但认真地看下汽车批发量呈现整体上涨趋势的这个规律是存在的，因此我们可以借助12个月的移动平均线和线性趋势线（excel内选中曲线右键就可以轻松添加啦）进一步发掘其规律。利用excel绘制出这两条线后我们再次观察数据，发现批发量移动平均线和线性趋势线是有规律地呈现季节波动（两者也十分吻合）。并且季节波动因子呈现一定的时间段性，就是近几年的走势会比较接近（2010年前的季节波动和2012年后的波动规律就有较大差异），这也决定了此方法只能应用于短期预测。因此我们可以将批发量季节因子进行分离，得出预测的原理：【批发量预测具体步骤】我们采用三年期预测数据来说明，各计算步骤可直接运用excel公式完成：1.求移动平均数Tt：第一期移动平均数T1=（Y1+Y2+Y3+…+Y12)/12,T1是移动平均，需要与历史数据的中间项对齐，T1对齐Y7，T2对齐T8，T2=（Y2+Y3+Y4+…+Y13)/12，同理类推；2.求对正移动平均Mt：由于N是偶数，12个月，中间是介于6月和7月之间，因此要对移动平均数两期求平均，形成新的对正序列Mt。M1=（T1+T2）/2，M2=（T2+T3）/2，同时M1和T1对齐，M2和T2对齐，同理类推；3.求季节指数ft：将各期实际批发量（观测值）除以对正移动平均，就是季节比率。ft=Yt/Mt，也就是说f1=Y1/M1，f2=Y2/M2，同理类推；4.求调整季节指数Fi：将各年同期的季节指数求平均值，就得到调整季节指数Fi；&5.确定斜率b：根据线性函数模型参数，b=（Mt末尾项-Mt首项）/Mt项数；6.确定截距a：在估计a时，实际观察值∑Yt=∑（a + bt）=∑a + b∑t=n*a+ b∑t求解：a=（∑Yt-b∑t）/n7.求趋势值X’t:按照公式X’t=a+bt根据b=（Mt末尾项-Mt首项）/Mt项数=（1,787,745-1,545,476）/19=12751；a=（51*496）/31=1435289；根据公式计算：X’1=1,448,040; X’2=1,460,7918.得出预测值Y’t:根据公式Y’t=X’t*Fi完成！我们的预测结果这就出来啦！我来给大家分享一种基于时间序列分析的excel应用的“草根”短期预测方法：【短期预测“模型”原理:在历史中寻找规律】首先，我们来看一下2008年至2016年乘联会批发量的时间序列图。就酱紫看过去，数据貌似是无规律地在跳动，但认真地看下汽车批发量呈现整体上涨趋势的这个规律是存在的，因此我们可以借助12个月的移动平均线和线性趋势线（excel内选中曲线右键就可以轻松添加啦）进一步发掘其规律。利用excel绘制出这两条线后我们再次观察数据，发现批发量移动平均线和线性趋势线是有规律地呈现季节波动（两者也十分吻合）。并且季节波动因子呈现一定的时间段性，就是近几年的走势会比较接近（2010年前的季节波动和2012年后的波动规律就有较大差异），这也决定了此方法只能应用于短期预测。因此我们可以将批发量季节因子进行分离，得出预测的原理：【批发量预测具体步骤】我们采用三年期预测数据来说明，各计算步骤可直接运用excel公式完成：1.求移动平均数Tt：第一期移动平均数T1=（Y1+Y2+Y3+…+Y12)/12,T1是移动平均，需要与历史数据的中间项对齐，T1对齐Y7，T2对齐T8，T2=（Y2+Y3+Y4+…+Y13)/12，同理类推；2.求对正移动平均Mt：由于N是偶数，12个月，中间是介于6月和7月之间，因此要对移动平均数两期求平均，形成新的对正序列Mt。M1=（T1+T2）/2，M2=（T2+T3）/2，同时M1和T1对齐，M2和T2对齐，同理类推；3.求季节指数ft：将各期实际批发量（观测值）除以对正移动平均，就是季节比率。ft=Yt/Mt，也就是说f1=Y1/M1，f2=Y2/M2，同理类推；4.求调整季节指数Fi：将各年同期的季节指数求平均值，就得到调整季节指数Fi；&5.确定斜率b：根据线性函数模型参数，b=（Mt末尾项-Mt首项）/Mt项数；6.确定截距a：在估计a时，实际观察值∑Yt=∑（a + bt）=∑a + b∑t=n*a+ b∑t求解：a=（∑Yt-b∑t）/n7.求趋势值X’t:按照公式X’t=a+bt根据b=（Mt末尾项-Mt首项）/Mt项数=（1,787,745-1,545,476）/19=12751；a=（51*496）/31=1435289；根据公式计算：X’1=1,448,040; X’2=1,460,7918.得出预测值Y’t:根据公式Y’t=X’t*Fi完成！我们的预测结果这就出来啦！
夜来风雨声，花落到底有多少？举个例子来说吧：问：我有3年的每个季度的某耳机的销售数据，有一个周期性的变化。 2007年：200
800 2008年： 240 350 600 980 2009年： 300 420 700 1150 我该如何做才能得出2010年第一季度的销售量呢？答：销售量是呈年度增长趋势，并随季度周期性变化。
1. 预测2010年总销售量
08 70 2010 年销售量
=forecast(预测年份, 销量区域，年份区域）=forecast(,a2:a4)=2950 2. （1）计算年度周期内变化： 第一季度 第二季度 第三季度 第四季度 2007 200 300 500 800 2008 240 350 600 980 2009 300 420 700 1150
（2）季度占权重 （每季度销量/全年销量）
11.11% 16.67% 27.78% 44.44%
11.06% 16.13% 27.65% 45.16%
11.67% 16.34% 27.24% 44.75%
（3）三年平均季度权重：
11.28% 16.38% 27.55%
44.78% 3. 求出 2010 年各季度销量预测：年度销量*平均季度权重 第一季度 第二季度 第三季度 第四季度 333 483 813 1321举个例子来说吧：问：我有3年的每个季度的某耳机的销售数据，有一个周期性的变化。 2007年：200
800 2008年： 240 350 600 980 2009年： 300 420 700 1150 我该如何做才能得出2010年第一季度的销售量呢？答：销售量是呈年度增长趋势，并随季度周期性变化。
1. 预测2010年总销售量
08 70 2010 年销售量
=forecast(预测年份, 销量区域，年份区域）=forecast(,a2:a4)=2950 2. （1）计算年度周期内变化： 第一季度 第二季度 第三季度 第四季度 2007 200 300 500 800 2008 240 350 600 980 2009 300 420 700 1150
（2）季度占权重 （每季度销量/全年销量）
11.11% 16.67% 27.78% 44.44%
11.06% 16.13% 27.65% 45.16%
11.67% 16.34% 27.24% 44.75%
（3）三年平均季度权重：
11.28% 16.38% 27.55%
44.78% 3. 求出 2010 年各季度销量预测：年度销量*平均季度权重 第一季度 第二季度 第三季度 第四季度 333 483 813 1321
以下是相关步骤，要是有看不懂的地方，请私聊我：1.工具-加载宏把分析工具库前面打勾；2.工具-数据分析-指数平滑；3.输入区域选择你的销售收入的数据行或列；4.阻尼系数选择0-1之间，建议分别选择0.2 0.6 0.9 3个；5.标准误差打勾，图表输出也打勾，直观一些；6.选择误差最小的那个阻尼系数的；7.在输出的最后一个数的单元格拖动填充柄，得出的数就是下期的预测数。8.补充：第一列是预测值，n-1个数，拖的就是这一列；第二列是标准误差，根据这个来判断哪个阻尼系数比较合适，标准误差越小越好。以下是相关步骤，要是有看不懂的地方，请私聊我：1.工具-加载宏把分析工具库前面打勾；2.工具-数据分析-指数平滑；3.输入区域选择你的销售收入的数据行或列；4.阻尼系数选择0-1之间，建议分别选择0.2 0.6 0.9 3个；5.标准误差打勾，图表输出也打勾，直观一些；6.选择误差最小的那个阻尼系数的；7.在输出的最后一个数的单元格拖动填充柄，得出的数就是下期的预测数。8.补充：第一列是预测值，n-1个数，拖的就是这一列；第二列是标准误差，根据这个来判断哪个阻尼系数比较合适，标准误差越小越好。
相关标签：
关注我们咨询服务合作法律法规京ICP备号
下载申请方APP
即刻拥有你的学业规划助手