无趋势的季节性 | 回归 | 季节性指数 | 预测 | 虚拟变量

1. 图示
   观察依据

   工作表被重新组织为三列，列标题分别为“年份”“季度”和“销售额”。

2. 声音
   观察依据

   讲解者提示：在第一列输入“年份”，第二列输入“季度”，并将时间序列数据命名为“销售额”。

Year, Quarter, Sales

长格式时间序列表格的列标题：年份索引、该年内的季度、观测到的销售额数值。

年份 = 1,2,3,4；季度 = 1,2,3,4；销售额 = 数值型季度观测值。

1. 公式
   观察依据

   公式栏显示 =SEQUENCE(4)。

2. 声音
   观察依据

   讲解者指出，该公式返回一个包含数字一至四、共四行的数组。

=SEQUENCE(4)

Excel 函数调用，生成一个垂直数组，其中包含 1, 2, 3, 4。

此处用于填充一年内“季度”列的数据。

1. 公式
   观察依据

   屏幕文字定义：$Q1 = 1$（若为第1季度），否则为 0；$Q2 = 1$（若为第2季度），否则为 0；$Q3 = 1$（若为第3季度），否则为 0。

2. 声音
   观察依据

   讲解者提示：定义指示变量 Q1、Q2 和 Q3。

Q1, Q2, Q3

季度指示（虚拟）变量，用作第1、2、3季度回归预测的自变量。

每个变量取值于集合 {0,1}。

1. 公式
   观察依据

   屏幕文字显示 Q4 -> $Q1 = Q2 = Q3 = 0$。

2. 声音
   观察依据

   讲解者将季度4称为参照类别或省略类别，此时 Q1、Q2、Q3均为零。

Q4

季节性回归中的参照类别或省略类别；通过隐式方式表示为 $Q1 = Q2 = Q3 = 0$，而非为其单独设立一个虚拟变量列。

当观测值属于季度4时适用。

1. 公式
   观察依据

   输入的公式为 =IF($B3=D$1,1,0)。

2. 声音
   观察依据

   讲解者解释道：若B列中的季度编号与第1行中的列编号一致，则返回1；否则返回0。

=IF($B3=D$1,1,0)

Excel 条件公式，通过将当前行B列中的季度编号与第1行中的季度标签进行比较，来生成虚拟变量。

$B3 fixes column B while allowing the row to change; D$1固定第1行，同时允许列号变化。

1. 公式
   观察依据

   回归对话框中显示“Y值输入区域”：$C$2:$C$18。

2. 声音
   观察依据

   讲解者提示选择时间序列数值作为Y值区域。

$C$2:$C$18

所选因变量区域，包含用于回归分析的销售观测值。

C列的第2行至第18行，即共17个单元格，包括表头单元格 C2。

1. 公式
   观察依据

   回归对话框显示输入 X 区域：$D$2:$F$18。

2. 声音
   观察依据

   讲解者提示选择 X 区域的指示变量值。

$D$2:$F$18

所选预测变量区域包含三个季度虚拟变量列 Q1、Q2、Q3。

D 列至 F 列的第2行至第18行，即一个包含标题行的17行×3列数据块。

1. 公式
   观察依据

   屏幕上的方程显示为$\hat{y}=107.75-37.75Q_1-26.25Q_2+8.75Q_3$。音频说明回归方程如所示。

$\hat{y}$

由季节性虚拟变量回归得到的拟合值或预测销售值。

一年中各季度的销售值。

1. 公式
   观察依据

   回归输出列出截距系数107.75。

2. 公式
   观察依据

   所显示的方程以107.75作为常数项。

107.75

拟合回归模型的截距；当所有季度虚拟变量均为零时，它即为基准预测值。

实数

1. 图示
   观察依据

   电子表格的D、E、F三列分别标记为Q1、Q2和Q3，其单元格内容为二进制数值。

2. 公式
   观察依据

   方程中包含项$-37.75Q_1$。

3. 声音
   观察依据

   对于第一季度，仅有 Q1 will be one, Q2 和 Q3 will be zero.

$Q_1$

哑变量，当1 为第1季度时取值为0 otherwise.

二元指示符{0,1}

1. 图示
   观察依据

   电子表格的E列标记为Q2，其条目为二进制值。

2. 公式
   观察依据

   方程中包含项$-26.25Q_2$。

3. 声音
   观察依据

   第二季度时，Q2为1，而Q1和Q3均为0。

$Q_2$

哑变量，当1 为第2季度时取值为0 otherwise.

二元指示器{0,1}

1. 图示
   观察依据

   电子表格的F列标记为Q3，其条目为二进制值。

2. 公式
   观察依据

   方程中包含项$+8.75Q_3$。

3. 声音
   观察依据

   第三季度时，Q3为1，而Q1和Q2均为0。

$Q_3$

哑变量，等于 1 第 3 季度，且 0 其他情况。

二元指示器{0,1}

1. 声音
   观察依据

   讲解者将本主题介绍为：在 Excel 中对具有季节性但无趋势的时间序列数据进行预测。

2. 图示
   观察依据

   绿色横幅显示“季节性但无趋势”，随后叠加在图表上的文字为“季节性模式，无趋势”以及“季节性但无趋势”。

本片段描述了一个预测问题：季度观测值在各年度间重复呈现季节性模式，但未表现出随时间持续上升或下降的趋势。因此，所选用的模型类别是基于指示变量的季节性回归，而非趋势加季节性模型。

1. 这些数据是季度时间序列观测值。

2. 该序列呈现出重复的季节性行为。

3. 该序列在时间上未表现出明显的上升或下降趋势。

1. 声音
   观察依据

   讲解者复制该表格，以转置方式粘贴，使年份位于列中，然后重新构建一张包含“年份”、“季度”和“销售额”的表格。

2. 图示
   观察依据

   原始矩阵的行对应1年到4年，列对应1季度到4季度，它被转换为一个长表格，其中年份和季度标签重复出现，并设有一列“销售额”。

本视频演示如何将宽格式的季度表格转换为长格式数据集，即每个时间段对应一行。之所以需要这种重塑，是因为回归工具要求因变量为单独一列，预测变量为各自独立的列，而非以“年×季度”构成的矩阵形式。

1. 从一个宽表格出发，其行表示年份，列表示季度。

2. 为每一年度与季度的组合创建一条记录。

3. 将观测值保留在单一的“销售额”列中。

1. 预测设定：季节性但无趋势

1. 声音
   观察依据

   讲解者说道：“要进行回归分析，我们首先定义指示变量。”

2. 公式
   观察依据

   屏幕上的定义为匹配的季度分配1，其余情况分配0，适用于Q1、Q2和Q3。

指示变量（亦称虚拟变量）以数值方式编码季度这一分类变量的归属关系。在本例中，Q1、Q2和Q3各自仅在其对应季度取值为1，其余情况下均为0，从而使得线性回归能够相对于某一基准季度，分别估计这些季度的季节性水平。

1. 该分类变量为“一年中的季度”。

2. 其中一个类别被指定为参照类别。

3. 其余类别获得明确的$0/1$指示符。

1. 预测设定：季节性但无趋势

1. 声音
   观察依据

   讲解者指出，季度4将作为参照类别或被省略的类别，此时Q1、Q2、Q3均为零。

2. 公式
   观察依据

   屏幕上显示$Q4 \to Q1 = Q2 = Q3 = 0$。

在使用虚拟变量编码分类变量时，需省略其中一个类别，作为基准类别。此处季度4即为被省略的类别，因此属于季度4的观测值由向量(Q1, Q2, Q3) = (0, 0, 0)表示。此时截距项对应季度4的均值水平，而各虚拟变量的系数则衡量相对于该基准类别的差异。

1. 共有四个互斥的季度类别。

2. 在显式虚拟变量集合中，恰好有一个类别被省略。

3. 被省略的类别被标识为参照组。

1. 用于刻画季度季节性的指示变量

1. 公式
   观察依据

   输入公式=IF($B3=D$1,1,0)，并在虚拟变量区域中横向填充。

2. 声音
   观察依据

   讲解者说明，美元符号将引用限定于列B和第1行，从而保证同一公式可沿行与列拖拽复制。

该视频通过将B列中每一行的季度标签与第$0/1$行中每一列的季度编号进行比较，来构建1行，从而构建1指示矩阵。混合绝对引用使季度来源在B列中保持固定，同时向下遍历各行；并使比较标题在第

1. B列包含每个观测值对应的季度编号。

2. 虚拟变量区块上方的1行包含季度编号1、2、3。

3. 该公式向下并向右复制，以填充Q1-Q3矩阵。

1. 将季度数据重塑为适用于回归分析的长格式
2. 用于刻画季度季节性的指示变量

1. 声音
   观察依据

   讲解者指示：单击“数据”→“数据分析”，选择“回归”，为Y指定时间序列数值，为X指定指示变量数值，并勾选“标志”。

2. 公式
   观察依据

   回归对话框中显示：输入Y区域：$C$2:$C$18，输入X区域：$D$2:$F$18。

构建完虚拟变量后，视频片段在Excel中设置了一个普通最小二乘回归：因变量为“销售”列，自变量为三个季度指示变量列，且启用了“标志”选项，因为所选区域包含标题单元格。

1. 使用Excel数据分析中的回归工具。

2. 将输入Y区域设为“销售”列。

3. 将输入X区域设为Q1-Q3虚拟变量列。

4. 当所选区域包含标题时，请勾选“标志”。

1. 在Excel中使用IF函数与混合引用生成虚拟变量
2. 虚拟变量回归中的参照类别或被省略的类别

1. 图示
   观察依据

   Excel 数据表包含年份、季度、销售额以及虚拟变量列 Q1、Q2、Q3。

2. 公式
   观察依据

   回归输出及显示的方程采用截距项加上 Q1、Q2、Q3的系数。

3. 声音
   观察依据

   讲解者提及该回归方程，并使用这些系数进行预测。

本视频使用带有季度虚拟变量的多元回归模型来估计季节性，模型中未显式包含趋势项。拟合方程直接根据Excel回归输出结果写出。

1. 季度数据由三个二元指示变量Q1、Q2和Q3表示。

2. 第4季度为被省略的参照类别，因此对应$Q1=Q2=Q3=0$。

3. 本片段展示的模型中没有单独的趋势变量。

1. 公式
   观察依据

   叠加图示展示了将(1,0,0)、(0,1,0)、(0,0,1)和(0,0,0)代入回归方程的过程。

2. 图示
   观察依据

   预测单元格显示70、81.5、116.5和107.75。

3. 声音
   观察依据

   讲话者指出，由于模型中无趋势项，这些值即为任意年份的预测结果。

要预测某一季度，需将对应的虚拟变量取值模式代入回归方程：第1季度使用$Q1=1$，第2季度使用$Q2=1$，第3季度使用$Q3=1$，而第4季度因属基准类别，故全部虚拟变量取零。

1. 使用屏幕上所显示回归方程中的相同拟合系数。

2. 由于模型未包含趋势项，因此相同的季度预测适用于任意未来年份。

1. 季节性虚拟变量回归模型

1. 公式
   观察依据

   可见比值公式=H11/$H$15与季节性指数公式=$I11*4$。

2. 声音
   观察依据

   主讲人指出：先将各期预测值相加求和，再用每个预测值除以总和得到比率，最后将这些比率乘以四，即可得到季节性指数。

3. 图示
   观察依据

   比例数值与季节性指数（S.Index）值并排显示。

该片段将季节性指数在操作上定义为：某季度预测值占全年预测值比重的四倍。这一处理对季度占比进行了重新标度，使得四个季节性指数之和等于4。

1. 每年分为四个季节。

2. 此处所用的季度预测值分别为70、81.5、116.5和107.75。

3. 所得出的季节性指数之和应为4。

1. 通过虚拟变量代入进行季度预测

1. 声音
   观察依据

   主讲人说，为便于理解，需从每个季节性指数中减去一，并将其转换为百分数。

2. 公式
   观察依据

   公式栏显示为=J11-1。

3. 图示
   观察依据

   列标题为“from ave.”，对应数值格式化为-25%、-13%、24%和15%。

在计算出季节性指数后，视频通过减去1，将每个指数转换为相对于平均水平的百分比偏差。结果为负，表示该季度低于平均水平；结果为正，表示该季度高于平均水平。

1. 季节性指数已经标准化，使其平均值为1。

2. 结果被解释为相对于季度平均需求的带符号百分比。

1. 由预测值占比得出的季节性指数

1. 声音
   观察依据

   讲解者指出，图表显示出季节性模式，但无趋势，因为数值并未随时间推移而持续上升或下降。

2. 图示
   观察依据

   散点图采用直线加标记的形式，在横轴方向上重复呈现波浪状的季度模式，并叠加显示“有季节性、无趋势”的结论。

对于该数据集，所绘制的季度序列呈现出重复的季节性模式，但未表现出随时间推移而上升或下降的趋势。

1. 所绘制的序列是按时间顺序排列的“销售额”列。

2. 该图表采用带标记的折线图形式，适用于观察时间序列的形态特征。

该论断仅针对本工作表中所展示的样本序列，而非所有季度数据。

1. 声音
   观察依据

   讲解者得出结论：适合该序列的模型是具有季节性但不含趋势的模型。

2. 图示
   观察依据

   ‘有季节性、无趋势’的叠加标注直接出现在图表观察之后。

由于该序列表现出季节性但无趋势，本片段中适用的回归模型是仅含季节性的模型，由季度指示变量构建而成。

1. 该时间序列具有重复出现的季度效应。

2. 该时间序列缺乏系统性的上升或下降趋势成分。

该结论适用于视频中所示数据集的模型选择决策。

1. 声音
   观察依据

   发言人表示：由于不存在趋势，这将是任意年份的预测值。

2. 公式
   观察依据

   所显示的回归方程仅包含截距项和季度虚拟变量，不含时间变量或趋势变量。

如果拟合的季节性模型仅包含季度虚拟变量而无趋势项，则相同的季度预测适用于任何未来年份。

1. 该模型即为所显示的方程$\hat{y}=107.75-37.75Q_1-26.25Q_2+8.75Q_3$。

2. 模型中未额外包含趋势变量。

对于任意未来年份及每个季度，预测值仅取决于该季度的虚拟变量模式。

1. 声音
   观察依据

   发言人称这些值之和应为四。

2. 图示
   观察依据

   所示的四个季节性指数（S.Index）分别为0.745176、0.867598、1.240186和1.147039。

当各季度预测值与全年预测值总和的比值乘以4后，所得的四个季节性指数之和为4。

1. 一年恰好分为四个季节。

2. 每个比值定义为该季度预测值除以四个季度预测值的总和。

对于四个季度 $q=1$,2,3,4，$\sum_{q=1}^{4}\text{S.Index}_q=4$。

1. 声音
   观察依据

   讲解者称，这些预测值也可通过直接计算每个季度对应数值的平均值来获得，并随后指出两种方法结果相同。

2. 公式
   观察依据

   公式栏显示 =AVERAGE(C3,C7,C11,C15）表示第1。

3. 图示
   观察依据

   两种方法均显示70、81.5、116.5和107.75。

在此例中，基于虚拟变量回归得到的季度预测值，与各季度历史销售数据的算术平均值完全相同。

1. 该回归模型仅使用季度虚拟变量。

2. 历史数据按季度分组，涵盖四年。

对每个季度 q，回归预测值等于该季度观测销售数据的样本均值。

1. 公式
   观察依据

   公式 =IF($B3=D$1,1,0) 仅输入一次，然后向右填充至 Q1-Q3 区域。

2. 图示
   观察依据

   填充完成后，虚拟变量列包含如下行：(1,0,0)、(0,1,0)、(0,0,1) 和 (0,0,0)，分别对应季度 1、2、3 和 4。

1. 公式

   $$\text{Quarter in column } B \quad \text{与之对比} \quad \text{quarter label in row } 1$$
   解释

   该设置将每个观测值的季度编号与各虚拟变量列上方的标题编号进行比较。

   步骤依据

   叙述者在介绍 IF 函数时明确说明。

   视频直接表达
2. 公式=IF($B3=D$1,1,0)
   解释

   若该行对应的季度等于该列的季度标签，则输出 1；否则输出 0。

   步骤依据

   这是公式栏中显示的确切 Excel 公式。

   视频直接表达
3. 公式

   $$(Q1,Q2,Q3)=(1,0,0),(0,1,0),(0,0,1),(0,0,0)$$
   解释

   填充该公式后，可为季度 1、2 和 3 生成独热编码行，而季度 4 对应全零行。

   步骤依据

   在公式拖拽填充至全部单元格后，完整的虚拟变量区域中可见此结果。

   视频直接表达

当前工作表已包含一组有效的季度指示变量，其中季度 4 作为隐含的参照类别。

1. 声音
   观察依据

   叙述者提示：选择 Y 的时间序列数值和 X 的指示变量数值。

2. 公式
   观察依据

   回归对话框中，输入 Y 范围显示为$C$2:$C$18，输入 X 范围显示为$D$2:$F$18。

1. 公式

   $$\text{Open Data Analysis} \rightarrow \text{Regression}$$
   解释

   该分析通过 Excel 的数据分析工具包启动。

   步骤依据

   通过菜单导航和对话框打开过程展示。

   视频直接表达
2. 解释

   因变量范围是“销售额”列。

   步骤依据

   在回归对话框中可见，并由讲解者明确指出。

   视频直接表达
3. 解释

   预测变量范围是 Q1、Q2 和 Q3 这三列虚拟变量构成的区域。

   步骤依据

   在回归对话框中可见，并由讲解者明确指出。

   视频直接表达
4. 公式

   $$\text{Labels checked}$$
   解释

   标题单元格被视作标签，而非数据。

   步骤依据

   讲解者明确说明应勾选“标志”选项，因为已选中了标题行。

   视频直接表达

准备好的长格式表格与虚拟变量已正确映射至 Excel 回归设定中，用于季节性预测。

1. 公式
   观察依据

   叠加图明确写出 Q1 至 Q4 的四个代入表达式。

2. 图示
   观察依据

   预测单元格显示数值结果 70、81.5、116.5 和 107.75。

1. 公式

   $$\hat{y}=107.75-37.75Q_1-26.25Q_2+8.75Q_3$$
   解释

   从屏幕上显示的拟合回归方程出发。

   步骤依据

   视频中呈现的公式。

   视频直接表达
2. 公式

   $$Q1:\ \hat{y}=107.75-37.75(1)-26.25(0)+8.75(0)$$
   解释

   对于第1季度，令 $Q1=1$，且 $Q2=Q3=0$。

   步骤依据

   音频解释仅当季度为1一季度时，Q值才为一。

   视频直接表达
3. 公式

   $$=70$$
   解释

   计算第1季度的表达式，得到预测值 70。

   步骤依据

   对屏幕上显示的表达式进行算术计算。

   依据视频推导
4. 公式

   $$Q2:\ \hat{y}=107.75-37.75(0)-26.25(1)+8.75(0)=81.5$$
   解释

   对于第2季度，令 $Q2=1$，其余虚拟变量均设为 0，然后计算。

   步骤依据

   屏幕上显示的叠加图及对应单元格的数值。

   视频直接表达
5. 公式

   $$Q3:\ \hat{y}=107.75-37.75(0)-26.25(0)+8.75(1)=116.5$$
   解释

   对于第3季度，令 $Q3=1$，其余虚拟变量均设为 0，然后计算。

   步骤依据

   显示的叠加图层及对应单元格的值。

   视频直接表达
6. 公式

   $$Q4:\ \hat{y}=107.75-37.75(0)-26.25(0)+8.75(0)=107.75$$
   解释

   对于第4季度，所有虚拟变量均为0，因此预测值等于截距项。

   步骤依据

   显示的叠加图层及对应单元格的值。

   视频直接表达

四个季度的预测值分别为70、81.5、116.5和107.75。

1. 公式
   观察依据

   公式栏显示为=SUM(H11:H14)、=H11/$H$15以及=$I11*4$。

2. 图示
   观察依据

   显示的数值包括总计375.75、比率0.186294、0.2169、0.310047、0.28676，以及指数0.745176、0.867598、1.240186、1.147039。

1. 公式

   $$\sum_{q=1}^{4}F_q=70+81.5+116.5+107.75=375.75$$
   解释

   将四个季度预测值相加，得到年度预测总量。

   步骤依据

   可见的SUM公式及显示的合计值。

   视频直接表达
2. 公式

   $$\text{Ratio}_q=\frac{F_q}{375.75}$$
   解释

   将每个季度预测值除以年度总量，得到其占比。

   步骤依据

   可见公式=H11/$H$15并配有语音讲解。

   视频直接表达
3. 公式

   $$(0.186294,\ 0.2169,\ 0.310047,\ 0.28676)$$
   解释

   这些是第1至第4季度所显示的比率。

   步骤依据

   比率列中显示的数值。

   视频直接表达
4. 公式

   $$\text{S.Index}_q=4\times \text{Ratio}_q$$
   解释

   将每个比率乘以4，因为一年有四个季度。

   步骤依据

   语音讲解此规则，公式栏显示为 =$I11*4$。

   视频直接表达
5. 公式

   $$(0.745176,\ 0.867598,\ 1.240186,\ 1.147039)$$
   解释

   这些是得到的季节性指数。

   步骤依据

   季节性指数（S.Index）列中显示的数值。

   视频直接表达

季节性指数分别为0.745176、0.867598、1.240186和1.147039，它们的和为4。

1. 声音
   观察依据

   讲解者指出，也可直接对每个季度的数值取平均来获得预测值。

2. 公式
   观察依据

   公式栏显示为 =AVERAGE(C3,C7,C11,C15)。

3. 图示
   观察依据

   第二个预测区块重复列出70、81.5、116.5和107.75。

1. 公式

   $$\bar{y}_{Q1}=\frac{72+69+70+69}{4}$$
   解释

   对第1季度在四年间的实际销售额取平均。

   步骤依据

   可见的 AVERAGE 公式选中了第1季度对应的行。

   视频直接表达
2. 公式

   $$=70$$
   解释

   季度1平均值等于70。

   步骤依据

   显示结果与基于回归的预测一致。

   依据视频推导
3. 公式

   $$\bar{y}_{Q2}=81.5,\quad \bar{y}_{Q3}=116.5,\quad \bar{y}_{Q4}=107.75$$
   解释

   将平均方法拖拽至其余各季度。

   步骤依据

   语音提示‘向下拖拽至其余部分’，且显示数值与先前的预测一致。

   视频直接表达

本例中，简单季度平均法给出的预测结果与虚拟变量回归法相同。

1. 图示
   观察依据

   工作表起始于一个4行×4列的季度销售额表格，涵盖从第1年到第4年的数据。

2. 声音
   观察依据

   讲解者逐步演示了对该同一数据集进行复制、转置、重建、制图、创建虚拟变量以及启动回归分析的操作。

1. 最终拟合的回归结果未在本120秒片段中显示。

给定一个涵盖四年时间的季度销售小表格，请在 Excel 中整理该数据，并构建一个不含趋势项的季节性回归模型。

1. 初始表格的行标题依次为：年份1、年份2、年份3、年份4。

2. 初始表格的列标题依次为：季度1、季度2、季度3、季度4。

3. 可见数值包括：年份1：72、83、116、113；年份2：69、74、112、106；年份3：70、89、120、108；年份4：69、80、118、104。

将数据转换为适合回归分析的格式，识别仅含季节性的模式，构建季度指示变量，并配置 Excel 回归工具。

1. 公式

   $$\text{Copy original table \text{且} paste transposed}$$
   解释

   首先将这种按年-季排列的宽矩阵重新整理，以便在重建过程中将年份作为列来处理。

   步骤依据

   由讲解者口述，并在工作表操作中可见。

   视频直接表达
2. 公式

   $$\text{Create columns Year, Quarter, Sales}$$
   解释

   数据被重写为长格式，每个时间点占一行。

   步骤依据

   在新表格结构中可见，并在音频中加以说明。

   视频直接表达
3. 公式

   $$=SEQUENCE(4)$$
   解释

   季度标签1至4为第一年区块生成，随后复制到后续年份。

   步骤依据

   公式显示在公式栏中，并由讲解员进行说明。

   视频直接表达
4. 公式

   $$\text{Insert scatter chart with straight line \text{且} markers}$$
   解释

   绘制销售序列以检验其时间结构。

   步骤依据

   屏幕上展示图表插入路径及最终生成的图表。

   视频直接表达
5. 公式

   $$Q1 = 1 \text{ if quarter 1; } 0 \text{ otherwise},\; Q2 = 1 \text{ if quarter 2; } 0 \text{ otherwise},\; Q3 = 1 \text{ if quarter 3; } 0 \text{ otherwise}$$
   解释

   为季度1至3明确定义了三个虚拟变量。

   步骤依据

   以屏幕文字形式显示，并由讲解员同步口述。

   视频直接表达
6. 公式=IF($B3=D$1,1,0)
   解释

   虚拟变量矩阵通过将每一行的季度与每一列标题进行比对而自动生成。

   步骤依据

   公式输入与填充操作清晰可见。

   视频直接表达
7. 解释

   配置回归工具时，将Sales设为因变量Y，将三个虚拟变量设为自变量X。

   步骤依据

   在回归对话框中展示，并由讲解员逐步口述说明。

   视频直接表达

本片段结束时，数据集已完成重塑；通过可视化诊断确认其具有季节性但无趋势；已添加Q1至Q3指示变量；并已载入Excel回归对话框，准备进行估计。

本片段内的验证方式为视觉化与流程化：图表支持“无趋势但具季节性”的判断；填充完成的虚拟变量矩阵呈现出预期的一热编码模式；回归对话框中显示的Y与X数据范围正确，且已启用“标签”选项。

1. 公式
   观察依据

   叠加图显示：季度1预测值 = $107.75 - 37.75(1) - 26.25(0) + 8.75(0)$。

2. 图示
   观察依据

   季度1的预测单元格显示70。

3. 声音
   观察依据

   讲解者说明：仅在第一季度时，Q1为1，其余均为0。

使用拟合的回归方程预测季度1的销售额。

1. 对应的式子为：$\hat{y}=107.75-37.75Q_1-26.25Q_2+8.75Q_3$

2. 对于季度1、$Q_1=1$、$Q_2=0$、$Q_3=0$。

计算第1季度的预测值。

1. 公式

   $$\hat{y}=107.75-37.75(1)-26.25(0)+8.75(0)$$
   解释

   将第1季度的虚拟变量模式代入回归方程。

   步骤依据

   直接根据所显示的模型进行代入。

   视频直接表达
2. 公式

   $$\hat{y}=107.75-37.75=70$$
   解释

   计算该表达式的值。

   步骤依据

   算术化简。

   依据视频推导

对应的式子为：70

电子表格中第1季度的预测单元格显示为70。

1. 公式
   观察依据

   可见公式用于计算总375.75、比率、季节性指数、与平均值的偏差，以及使用=$I11*420$进行分配。

2. 图示
   观察依据

   显示的输出包括-25%、-13%、24%、15%以及分配值78.24351、91.0978、130.2196、129.4391。

3. 声音
   观察依据

   讲解人说明：将比率乘以四，再减去一以便解释，并利用季节性比率对预计年度销售额420进行分配。

已知四个季度的预测值及预计年度总量420，计算季节性指数，将其解释为相对于平均值的百分比偏差，并按季度分配该年度总量。

1. 季度预测：70、81.5、116.5、107.75

2. 年度预测合计：375.75

3. 预计年度销售额：420

计算季节性指数、相对于平均值的百分比偏差，以及420的季度分配。

1. 公式

   $$\text{Ratio}_q=\frac{F_q}{375.75}$$
   解释

   将每个季度预测值除以年度合计值。

   步骤依据

   可见公式为 =H11/$H$15，并配以口头解释。

   视频直接表达
2. 公式

   $$\text{S.Index}_q=4\times \text{Ratio}_q$$
   解释

   将每个比值乘以4，得到季节性指数。

   步骤依据

   可见公式为 =$I11*4$，并配以适用于四个季节的口头规则。

   视频直接表达
3. 公式

   $$\text{from ave.}_q=\text{S.Index}_q-1$$
   解释

   从每个季节性指数中减去1，并将结果格式化为百分比。

   步骤依据

   可见公式为 =J11-1，并配以口头解读步骤。

   视频直接表达
4. 公式

   $$\text{Allocation}_q=\text{Ratio}_q\times 420$$
   解释

   利用季度比值分配预计年度合计值。

   步骤依据

   可见公式为 =$I11*420$，并配以口头指令：将季节性比值乘以420。

   视频直接表达

季节性指数：0.745176、0.867598、1.240186、1.147039。与平均值的偏差：-25%、-13%、24%、15%。总额420的分配结果：78.24351、91.0978、130.2196、129.4391。

屏幕上显示的分配数值之和为420，与口述验证中‘它们应加总为420’一致。

1. 图示
   观察依据

   绿色下部横幅显示“季节性但无趋势”，而“预测”“回归”和“季节性指数”等文字出现在工作表上方。

1. 绿色横幅

2. 工作表背景

3. 文字标签：“预测”“回归”“季节性指数”

1. 文字标签依次出现在电子表格上方。

2. 横幅在导入环节中始终固定于底部。

1. 底层的季度销售表格在叠加图层后仍保持可见。

画面明确表明：本课内容是关于仅含季节性效应、不含趋势的预测，所用工具为回归分析与季节性指数。

1. 图示
   观察依据

   插入一幅带直线和标记点的散点图，随后叠加标注“季节性模式”“无趋势”及“季节性但无趋势”。

2. 声音
   观察依据

   讲解员将重复出现的波形解释为季节性，将缺乏持续上升或下降的趋势解释为无趋势。

1. 折线与标记图

2. 横轴时间索引

3. 纵轴销售额刻度

4. 文字标注指出存在季节性模式而无趋势

1. 原始表格被可视化为一条绘制的序列。

2. 叠加注释依次出现，用于标识所观察到的模式。

1. 所表示的仍是相同的销售额数据，仅以图形形式呈现。

2. 季度重复性在连续多年间依然清晰可见。

该图表为选择仅含季节性的回归模型而非趋势模型提供了经验依据。

1. 动画
   观察依据

   输入公式 =IF($B3=D$1,1,0) 后，将该公式向下拖拽以填充 Q1-Q3 区域中的其余单元格。

2. 图示
   观察依据

   完成后的矩阵显示：各行呈现 $1/0$ 模式，分别对应季度 1、2、3；而对应季度 4 的行则全为零。

1. 公式栏

2. 单元格 D3:F18

3. B 列中的季度标签

4. 季度编号1、2、3位于第1

1. 一个单一公式展开为一个完整的矩形虚拟矩阵。

2. 每一行均根据其所处的季度进行编码。

1. 每个已填充单元格的比较逻辑保持一致。

2. 季度4继续对应全零模式。

动画演示了混合绝对引用如何使一个公式生成一整套分类编码方案。

1. 图示
   观察依据

   数据分析对话框打开，选择‘回归’，回归对话框中显示输入 Y 区域$C$2:$C$18和输入 X 区域$D$2:$F$18。

2. 声音
   观察依据

   讲解员指导用户为 Y 选择时间序列值，为 X 选择指示器值，并勾选‘标志’。

1. 数据分析对话框

2. 回归分析对话框

3. 因变量输入区域字段

4. 自变量输入区域字段

5. 标志复选框

1. 工作流程从数据准备阶段进入模型估计设置阶段。

2. 所选区域被填入回归分析的相应字段中。

1. 销售额仍为因变量。

2. Q1–Q3仍为解释变量。

这一可视化步骤确认：已准备好的虚拟变量设计矩阵正被用于普通最小二乘回归框架中。

1. 图示
   观察依据

   Excel 回归对话框已打开，其中‘Y 值输入区域’为$C$2:$C$18，‘X 值输入区域’为$D$2:$F$18。

2. 图示
   观察依据

   输出选项中显示‘输出区域’为$K$2，此时尚未点击‘确定’。

1. Excel 工作表包含‘年份’、‘季度’、‘销售额’、‘Q1’、‘Q2’、‘Q3’列。

2. 回归对话框

3. 输出区域字段

1. 输出区域指定为$K$2。

2. 点击‘确定’以生成回归分析结果。

1. 因变量仍为 C 列的‘销售额’。

2. 自变量仍为虚拟变量列‘Q1’、‘Q2’、‘Q3’。

可视化序列展示了如何在 Excel 中执行季节性虚拟变量回归。

1. 图示
   观察依据

   回归摘要显示在K列和L列中，截距为107.75, Q1—37.75, Q2—26.25, Q3 8.75。

2. 图示
   观察依据

   系数块被复制到 I 列和 J 列，其余较大输出则被删除。

1. 摘要输出表

2. 系数表

3. 剪贴板粘贴区域

1. 仅保留系数块以供后续使用。

2. 完整的回归摘要从工作表中移除。

1. 在复制过程中，系数本身的数值不会发生变化。

该工作流程提取出构建预测方程所需的数值参数。

1. 公式
   观察依据

   大型覆盖层显示$\hat{y}=107.75-37.75Q_1-26.25Q_2+8.75Q_3$。

2. 公式
   观察依据

   逐层叠加显示四条分别对应各季度的代入直线。

3. 图示
   观察依据

   预测列依次填入70、81.5、116.5和107.75。

1. 回归方程叠加图示

2. 预测表格

3. 系数表格

1. 依次将每个季度的虚拟变量模式代入。

2. 预测值按行依次显示，从第一季度1至第一季度4。

1. 系数107.75、-37.75、-26.25和8.75在整个过程中保持不变。

动画清晰展示了虚拟变量编码如何转化为逐季度的预测结果。

1. 公式
   观察依据

   公式栏显示=AVERAGE(C3,C7,C11,C15)。

2. 图示
   观察依据

   在第一个预测区块下方创建第二个预测区块，生成相同的四个数值。

3. 图示
   观察依据

   红色方框突出显示匹配的预测值。

1. 原始销售数据列 C

2. 第二个预测区块

3. 高亮显示的方框

1. 第1季度的销售数据被非连续选中并求平均值。

2. 该公式向下拖拽至其他季度。

1. 基于回归的原始预测值保持不变，以供比较。

直观对比表明，在此无趋势虚拟变量模型中，回归预测值等于简单的季度均值。

1. 图示
   观察依据

   新增了“比率”“季节性指数（相对于均值）”和“总计 = 420”三列。

2. 公式
   观察依据

   公式栏中显示的公式分别为 =SUM(H11:H14)、=H11/$H$15、=$I11*4$、=J11-1 和 =$I11*420$。

3. 图示
   观察依据

   标注文字说明“比均值低25%”和“比均值高15%”。

1. 预测第H列

2. 比率第I列

3. 季节性指数第J列

4. 来自平均值第K列

5. 合计 = 420 第L列

1. 各项预测值之和为375.75。

2. 通过乘以4，将比率转换为季节性指数。

3. 将指数转换为相对于平均值的百分比偏差。

4. 使用比率，按季度分配预计年度销售额420。

1. 基础季度预测值保持为70、81.5、116.5和107.75。

该过程完整展示了从拟合的季度预测值到可解释的季节性指数，再到实际年度分配的全部转换步骤。

1. 声音
   观察依据

   讲解者特别指出，季度4为参照类别（即被省略的类别），此时Q1、Q2、Q3均为零。

2. 公式
   观察依据

   仅显式创建了Q1、Q2和Q3三列。

一个常见错误是用四个虚拟变量列 Q1、Q2、Q3、Q4来编码全部四个季度。

在此回归设定中，必须省略一个类别。季度4被有意隐去，由（0,0,0）共同表示，从而避免虚拟变量列与截距项之间出现完全共线性。

1. 声音
   观察依据

   讲解者将重复出现的季节性模式与随时间推移既不递增也不递减的情形区分开来。

2. 图示
   观察依据

   该图表分别标注了‘存在季节性模式’和‘无趋势’。

观众可能将一年内规律性的起伏误认为长期趋势。

本视频将季节性定义为按季度重复出现的形态，而将趋势定义为随时间持续发生的单向变动。本数据集被呈现为仅具前者、不具后者。

1. 公式
   观察依据

   所展示的模型仅使用 Q1、Q2 和 Q3，而季度4的预测则通过将全部三个虚拟变量设为0来实现。

2. 声音
   观察依据

   主讲人明确将季度4描述为 Q1、Q2 和 Q3 全部为零的情形。

人们可能认为回归模型中每个季度都需要一个独立的虚拟变量。

对于四个季节性类别，仅需使用三个虚拟变量；被省略的季度作为参照类别，因此季度4对应于 $Q1=Q2=Q3=0$。

1. 公式
   观察依据

   季节性指数（S.Index）值约为1，而平均值调整后的数值则通过减去1得到。

2. 声音
   观察依据

   主讲人区分了季节性指数与减去一之后更易理解的百分比形式。

像0.745176这样的季节性指数可能被误认为是相对于平均值的直接百分比偏差。

该指数本身是围绕1。为表示与平均值的偏差，需减去1，然后将结果读作百分比，得到-25%，对应第1季度。

1. 声音
   观察依据

   在重建表格后，讲解者直接进入回归所用指示变量的定义环节。

2. 图示
   观察依据

   长格式的“季度”列成为 IF 公式生成 Q1–Q3所依据的源数据。

长格式表格提供了构建虚拟变量列所需的、每个观测值对应的季度标签。

1. 公式
   观察依据

   在定义 Q1、Q2、Q3之后，紧接着给出语句：$Q4 \to Q1 = Q2 = Q3 = 0$。

虚拟变量构造包含一条附加规则：被省略的第四季度用全零向量表示。

1. 公式
   观察依据

   Q1、Q2、Q3的抽象定义，通过=IF($B3=D$1,1,0)在实际中具体实现。

Excel 的 IF 方法是在工作表内对指示变量定义进行的计算实现。

1. 图示
   观察依据

   回归对话框使用$D$2:$F$18，这恰好是包含 Q1–Q3的单元格区域。

2. 声音
   观察依据

   讲解者提示选择 X 变量范围所对应的指示变量取值。

回归设定依赖于已事先选定第4季度作为被省略的基准期；否则 X 变量范围的设计将与所陈述的模型不一致。

1. 声音
   观察依据

   讲解者从图表推断出，恰当的模型是‘有季节性但无趋势’。

2. 图示
   观察依据

   图表标注的‘季节性模式’和‘无趋势’引出了叠加图层‘有季节性但无趋势’。

认识到该序列具有季节性但无趋势，促使后续采用季度指示变量，而非时间趋势回归变量。

1. 公式
   观察依据

   该方程紧接着列出了按季度分别代入的具体行。

2. 声音
   观察依据

   主讲人从陈述回归方程过渡到解释如何分别预测各季度。

将拟合的虚拟变量回归方程应用于每个季度，代入对应的虚拟变量模式，从而得到预测值。

1. 公式
   观察依据

   比率（Ratio）和季节性指数（S.Index）公式直接使用预测列中的数值。

2. 声音
   观察依据

   讲解者指出，应先对预测值求和，再计算各季度占比（比率），最后乘以四，得到季节性指数。

季度预测值通过先计算其占全年总和的比例，再乘以4，转化为季节性指数。

1. 公式
   观察依据

   ‘平均值差’列（ave.列）的计算公式为：=J11-1，其中减数来自S.Index列。

2. 声音
   观察依据

   讲解者指出，为便于解释，需对每个季节性指数减去一。

与平均水平的百分比偏差可直接由季节性指数减去1得到。

1. 声音
   观察依据

   讲解者指出，相同的预测值也可通过对每个季度的对应历史值取平均来获得。

2. 图示
   观察依据

   两个独立的预测结果区块显示完全一致的结果。

该片段对比了回归法与更简单的直接平均法，并说明在此无趋势情形下，两种方法得出的季度预测值完全相同。

1. 公式
   观察依据

   所展示的方程仅包含截距项和季度虚拟变量。

2. 声音
   观察依据

   讲解者指出，由于不存在趋势，这些预测值适用于任意年份。

断言相同预测适用于任意年份，依赖于模型中不含趋势项，这一点在所显示的方程中可见。

1. 声音
   观察依据

   讲解者明确将该主题命名为‘具有季节性但无趋势的时间序列数据’。

1. 预测设定：季节性但无趋势

1. 图示
   观察依据

   宽表在绘图和回归前被转换为‘年份’、‘季度’、‘销售额’三列。

1. 将季度数据重塑为适用于回归分析的长格式

1. 公式
   观察依据

   公式 =IF($B3=D$1,1,0) 被展示并横向填充至虚拟变量区域。

1. 在Excel中使用IF函数与混合引用生成虚拟变量
2. 用于刻画季度季节性的指示变量

1. 声音
   观察依据

   讲解者将第4季度称作参照类别或被省略的类别。

1. 虚拟变量回归中的参照类别或被省略的类别

1. 公式
   观察依据

   回归对话框中显示：因变量输入区域为$C$2:$C$18，自变量输入区域为$D$2:$F$18。

1. 在Excel数据分析中运行季节性回归

1. 图示
   观察依据

   图表上标注了季节性模式，且无趋势。

1. 预测设定：季节性但无趋势

1. 公式
   观察依据

   屏幕上显示一个较大的方程。

1. 季节性虚拟变量回归模型

1. 公式
   观察依据

   Q1至Q4的代入标注可见。

1. 通过虚拟变量代入进行季度预测

1. 声音
   观察依据

   说话人称不存在趋势。

1. 当模型中未纳入趋势时，预测值会在各年之间重复出现。

1. 公式
   观察依据

   公式栏中可见比率（Ratio）和季节性指数（S.Index）公式。

1. 由预测值占比得出的季节性指数

1. 公式
   观察依据

   ‘ave.’列通过从S.Index中减去1得到。

2. 图示
   观察依据

   标注显示25%低于平均水平、15%高于平均水平。

1. 相对于平均季节性的百分比偏差

1. 公式
   观察依据

   公式栏显示 =$I11*420$。

2. 声音
   观察依据

   讲解者指出，需将季节性比率乘以420。

1. 由预测值占比得出的季节性指数