上一节针对公司某一项产品单独分析了销售收入与成本，对于一个企业来说，除了按产品单独核算外，还需要对所有产品的收入、成本、费用及税金进行分析。本节将对公司本年度整体销售成本、销售费用、销售税金与销售收入的相关性进行分析。
创建表格并设置公式计算
创建一个用于统计本年度销售收入、成本、费用和税金的表格，并根据已知数据计算出各月的销售收入、成本、费用和税金，然后计算出销售成本率、销售费用销售税金率、销售税金率，操作步骤如下所示。
步骤01：将工作表标签Sheet2更改为“本年成本、费用、税金与收入相关分析”，在工作表中创建如图2-112所示的分析表。
图2-112 创建表格
步骤02：在单元格B4中输入公式“=数据表!C4+数据表!C11+数据表!C18”，按下Enter键后，向右复制公式至单元格M4，如图2-113所示。
图2-113 设置公式计算销售收入
步骤03：向下拖动单元格M4右下角的填充柄至单元格M7，求出销售成本、费用和税金，如图2-114所示。
步骤04：在单元格B8中输入公式“=B5/B4”，计算销售成本率，按下Enter键后，复制至N8，如图2-115所示。
步骤05：在单元格B9中输入公式“=B6/B4”，计算销售费用率，按下Enter键后，复制公式至N9，得到如图2-116所示的数据。
步骤06：在单元格B10中输入公式“=B7/B4”，计算销售税金率，按下“Enter”键后，复制公式至N10，得到如图2-117所示的数据。
步骤07：在单元格N4中输入公式“=SUM（B4:M4）”，计算合计按下Enter键后，复制公式至单元格N7，得到表格最终的数据如图2-118所示。
图2-114 复制公式
图2-115 计算销售成本率
图2-116 计算销售费用率
图2-117 计算销售税金率
图2-118 计算合计
销售收入与销售成本相关分析
销售收入与销售成本的分析是企业发展计算所得利润的最初阶段，正确地利用Excel对其进行分析对一个企业来讲至关重要，操作步骤如下。
步骤01：在工作表中选择单元格区域A3:M4，在“插入”选项卡的“图表”组中单击“折线图”下三角按钮，从展开的下拉列表中单击“折线图”子类型，如图2-119所示。
步骤02：Excel会根据用户选择的数据区域，以默认的样式创建所选择的图表类型，如图2-120所示。
步骤03：打开“选择数据源”对话框，选择“月份”系列，单击“删除”按钮，如图2-121所示。
步骤04：在图表中添加标题“收入、成本比较图表”，得到的图表最终效果如图2-122所示。
步骤05：复制“收入、成本比较图表”，然后将副本图表的标题更改为“销售成本率变化趋势”，如图2-123所示。
步骤06：打开“选择数据源”对话框，单击“图表数据区域”右侧的单元格引用按钮，选择单元格区域A8:M8，然后单击“确定”按钮，如图2-124所示。更改图表数据源后的图表效果如图2-125所示。
图2-119 选择折线图类型
图2-120 默认的图表类型
图2-121 删除系列
图2-122 图表最终效果
图2-123 复制图表并修改标题
图2-124 更改图表数据源
图2-125 更改后图表效果
步骤07：选择单元格区域B31:C31，输入公式“=LINEST（B4:M4,B5:M5）”，按下Ctrl+Shift+Enter组合键，生成数组公式，返回结果如图2-126所示。
图2-126 设置LINEST函数进行最佳直线拟合
步骤08：在合并单元格B32中输入公式
“=CONCATENATE（"Y="，TEXT（B31，"0.0000"），"X+"，TEXT（C31，"0.0000"））”，按下Enter键后，返回回归函数表达式，如图2-127所示。
步骤09：在单元格B33中输入公式“=CONCATENATE（"r=",TEXT（CORREL（B4:M4,B5:M5）,"0.0000"））”，按下Enter键后，单元格中显示相关系数“r=0.3744”，如图2-128所示。
步骤10：在单元格C33中输入公式“=IF（CORREL（B4:M4,B5:M5）<0.5,"异常",""）”，判定相关性是否正常，按下Enter键后，单元格中显示“异常”，如图2-129所示。
图2-127 返回回归函数表达式
图2-128 计算相关系数
图2-129 判定相关性是否正常
销售收入与销售费用相关分析
上面分析了销售收入与销售成本之间的相关性，接下来分析销售收入与销售费用之间的相关性，操作步骤如下所示。
步骤01：复制上一节中创建的“收入、成本比较图表”，并将副本图表的标题更改为“收入、费用比较图表”，如图2-130所示。
图2-130 复制图表并修改标题
步骤02：打开“选择数据源”对话框，选择“销售成本”数据系列，单击“删除”按钮，如图2-131所示。
图2-131 删除数据系列
步骤03：此时“选择数据源”对话框中只有“销售收入”一个数据系列。单击“添加”按钮，如图2-132所示。
步骤04：打开“编辑数据系列”对话框，单击“系列名称”框右侧的单元格引用按钮，选择“销售费用”文本所在单元格A6，单击“系列值”框右侧的单元格引用按钮，选择单元格B6:M6，然后单击“确定”按钮，如图2-133所示。
步骤05：返回“选择数据源”对话框，单击“确定”按钮，如图2-134所示。
步骤06：返回工作表中，得到的销售收入与费用比较图表，如图2-135所示。
步骤07：复制“销售成本率变化趋势”图表，然后打开“选择数据源”对话框，将副本图表的数据区域更改为A9:M9，单击“确定”按钮，如图2-136所示。销售成本率变化趋势图最终效果如图2-137所示。
图2-132 单击“添加”按钮
图2-133 “编辑数据系列”对话框
图2-134 单击“确定”按钮
图2-135 收入、费用比较图表效果
步骤08：选择单元格区域B54:C54，输入数组公式“=LINEST（B4:M4,B6:M6）”，得到如图2-138所示的计算结果。
图2-136 更改图表数据源
图2-137 销售成本率变化趋势图表
图2-138 设置公式返回函数参数
步骤09：在单元格B55中输入公式“=CONCATENATE（"Y=",TEXT（B54,"0.0000"）,"X+",TEXT（C54,"0.0000"））”，返回销售收入与销售费用的函数表达式，如图2-139所示。
图2-139 设置公式返回函数表达式
步骤10：在单元格B56中输入公式“=CONCATENATE（"r=",TEXT（CORREL（B4:M4,B6:M6）,"0.0000"））”，按下Enter键后，公式计算相关系数为“r=0.9284”，如图2-140所示。
图2-140 设置公式计算相关系数
步骤11：在单元格C56中输入公式“=IF（CORREL（B4:M4,B6:M6）<0.5,"异常",""）”，按下Enter键后，公式结果返回为空，说明该相关系数在正常范围内，如图2-141所示。
我们已经介绍了销售收入与销售成本相关分析、销售收入与销售费用相关分析的具体步骤，我们不难从中发现一些有趣的计算规律，我们可以利用这些规律，对表格中任意两个变量之间的相关关系进行分析。
图2-141 设置公式判定相关系数
