Microsoft Office/创建大学费用分析工作簿
#REF! - 一条错误消息，表明公式存在错误，如果公式中包含已删除的单元格，则可能出现此错误消息。
格式符号 - 与数字一起输入的符号，例如 $, %，或逗号。在数字中输入这些符号时，Excel 会自动假设相应的格式。
冻结标题 - 固定一行或一列，以便无论向下或向右滚动多远，它们始终在屏幕上显示。
日期戳 - 显示文件创建日期。这在商业财务报表中非常重要。
NOW 函数 - 此函数始终显示今天的日期，或计算机保存的系统日期。日期实际上存储为一个数字，表示从 1900 年 1 月 1 日开始的天数。
绝对单元格引用 - 在复制单元格时保持单元格不变。通过添加 $ 符号来实现。$C$15 是一个绝对单元格引用，当您复制包含它的公式（例如，=$C$15 +C16）向下移动一列时，$C$15 会保持不变，而 C16 会随行号变化。
相对单元格引用 - 当公式被复制时，单元格引用会发生变化。在公式 =$C$15 + C16 中，C16 是相对单元格引用。
混合单元格引用 - 可以告诉 Excel 只保留单元格引用的一部分不变，只需使用一个 $ 符号即可。C$15 和 $C15 都是混合单元格引用。在 C$15 中，行号将保持不变，但列字母可能会发生变化。在 $C15 中，列字母将保持不变，但行号可能会发生变化。
IF 函数 - 一种条件，允许根据测试结果将值分配给单元格。=IF(B7>$B$2, $C$2, 0) 这意味着如果单元格 B7 中的值大于单元格 B2 中的值，则保留单元格 C2 中的值。如果它等于或小于 B2 中的值，则保留值 0。
逻辑测试 - IF 语句中的测试，例如 B7>$B$2。
如果为真，则返回值 - 如果测试的条件为真，则显示的值。=IF(B7>$B$2, $C$2, 0) 在此示例中，它是 $C$2。
如果为假，则返回值 - 如果测试的条件为假，则显示的值。=IF(B7>$B$2, $C$2, 0) 在此示例中，它是 0。
比较运算符 - 可以执行六种测试
- < 第一个值小于第二个值
- > 第一个值大于第二个值
- >= 第一个值大于或等于第二个值
- <= 第一个值小于或等于第二个值
- = 两个值相等
- <> 两个值不相等
嵌套 IF 函数 - 可以将一个 IF 语句嵌套在另一个 IF 语句中。第二个 IF 语句将位于如果为真或如果为假的区域中。
图表工作表 - Excel 中只包含图表的 工作表。
饼图 - 对整体部分的图形表示。仅当您要显示某事物的百分比组成时使用饼图。每个饼图切片表示整体的百分比。
爆炸饼图 - 一个或多个切片从整体中拉出的饼图。
偏移 - 饼图中拉出的部分称为偏移。有时您只想拉出一个切片来显示您正在讨论的区域。
假设分析 - 这被称为敏感性分析，这只是意味着对于您更改数据的任何单元格，Excel 将重新计算所有公式并重新绘制所有图表。
目标求解 - 如果您想找到在单元格中获得所需结果需要发生什么情况，您可以使用此功能来确定公式所依赖的单元格的值。
EX3 - 费用分析
将以下表格输入 Excel
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Pizza R Us | |||||||
| 2 | 半年预计毛利、费用和营业利润 | |||||||
| 3 | 一月 | 总计 | ||||||
| 4 | 销售额 | $2,212,105.98 | $4,845,562.56 | $6,721,100.32 | $3,025,430.35 | $3,001,272.68 | $5,987,384.14 | |
| 5 | 销货成本 | |||||||
| 6 | 毛利 | |||||||
| 7 | ||||||||
| 8 | 费用 | |||||||
| 9 | 奖金 | |||||||
| 10 | 佣金 | |||||||
| 11 | 营销 | |||||||
| 12 | 研发 | |||||||
| 13 | 支持、一般和行政管理 | |||||||
| 14 | 总费用 | |||||||
| 15 | ||||||||
| 16 | 营业利润 | |||||||
| 17 | ||||||||
| 18 | 假设数据 | |||||||
单击 Office 按钮以保存工作簿
单击 Office 按钮 - 准备为您的姓名和其他相关信息添加文档属性
为工作表选择一个主题
旋转月份名称，使其呈 45° 角
单击行标题 3 以选择整行。
单击“主页”选项卡以使其处于活动状态
单击“对齐”组对话框启动器
将方向更改为 45°
单击“确定”
单击单元格 B3，即月份 JANUARY
单击并拖动填充柄到右侧，到单元格 G3
注意，Excel 会自动将月份更新到系列中的下一个月份。
还要注意新出现的选项按钮 - 自动填充选项菜单
- 复制单元格
- 填充系列
- 仅填充格式
- 不带格式填充
- 填充月份
在 Excel 中，有许多不同的复制和粘贴方法。
- “主页”选项卡“剪贴板”组中的复制和粘贴按钮
- 右键单击并复制和粘贴
- CTRL+C 复制，CTRL+V 粘贴
- 另一种方法是剪切和粘贴，如果您想移动数据。
使用其中一种方法将范围 A9:A13 复制到 A19:A23。
与填充选项菜单一样，还有一个粘贴选项菜单，提供相同的选项。
您可以插入单个单元格、单元格区域或整行和整列。不同的方法是
- 右键单击行标题，然后单击“插入”以插入一行
- 右键单击列标题，然后单击“插入”以插入一列
- 单击行标题，然后单击“开始”选项卡中的“单元格”组中的“插入”。
- 单击列标题，然后单击“开始”选项卡中的“单元格”组中的“插入”。
- 您也可以对单个单元格执行相同的操作。
您还可以删除单元格、单元格区域或整行和整列。当您删除单元格时，系统会询问您是否要将单元格向上或向右移动。
插入单元格 A21 并将文本“利润率”放在新单元格 A21 中。
插入单元格 A24 并将文本“奖金收入”放在新单元格 A24 中。
注意：如果删除了在公式中使用的单元格，则公式结果将显示为“#REF!”，您需要修复公式。
输入“假设数据”
[edit | edit source]B19 = 150,000.00
B20 = 4.5%
B21 = 58%
B22 = 8%
B23 = 6.25%
B24 = 5,250,000.00
B25 = 16.5%
冻结单元格
[edit | edit source]有时，当您处理大型电子表格时，您可能希望看到列和行标题。Excel 中有一个选项允许您在屏幕上冻结行或列。这样一来，您仍然可以查看标题，并且可以滚动查看其余数据。
要冻结单元格，请执行以下操作：
- 单击要冻结区域的边框单元格。在本例中，该单元格为 B4。
- 单击“视图”选项卡。
- 单击“冻结窗格”按钮。
- 单击“冻结窗格”，这将冻结列 A 和行 1-3。
显示系统日期
[edit | edit source]处理数据时，了解数据的最新程度非常重要。为此，我们将日期放入我们的表格中。
- 转到单元格 H2。
- 单击公式栏中的“插入函数”框。
- 选择“日期和时间”。
- 在“选择函数”框中单击“NOW”。
- 单击“确定”
- 右键单击单元格 H2。
- 单击“设置单元格格式”。
- 单击“数字”选项卡。
- 单击“日期”。
- 单击您喜欢的格式（我将选择 10/27/09）。
- 单击“确定”
此日期实际上存储为自 1899 年 12 月 31 日以来的天数。
绝对引用和相对引用单元格
[edit | edit source]在以下公式中，请确保在使用填充柄之前确定哪些引用是绝对引用，哪些引用是相对引用。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 单元格 | 行标题 | 公式 | 注释 |
| 2 | B5 | 销售成本 | =B4*(1-B21) | 这是销售的所有商品的成本。 |
| 3 | B6 | 毛利 | =B4-B5 | 销售利润（不包括任何其他费用）。 |
| 4 | B9 | 奖金 | =IF(B4>=B24, B19, 0) | 如果您的销售额（B4）大于或等于奖金收入（B24）金额，则发放奖金（B19），否则不发放任何奖金。 |
| 5 | B10 | 佣金 | =B4*B20 | 计算销售额（B4）的佣金百分比（B20）。 |
| 6 | B11 | 营销 | =B4*B22 | 计算销售额（B4）的营销支出百分比（B22）。 |
| 7 | B12 | 研发 | =B4*B23 | 计算销售额（B4）的研发支出百分比（B23）。 |
| 8 | B13 | 支持、一般和管理 | =B4*B25 | 计算销售额（B4）的支持、一般和管理支出百分比（B25）。 |
| 9 | B24 | 总费用 | =SUM(B9:B13) | 1 月份所有支出的总额 |
| 10 | B16 | 营业利润 | =B6-B14 | 这是您在扣除所有费用后的实际利润。 |
当您输入公式时，您有三种选择，可以决定公式将如何与您引用的单元格交互
- 相对单元格引用 - 这是您一直使用的方法。当您使用填充柄时，单元格引用会发生变化。如果在包含公式“=A3+A5”的单元格上使用填充柄并向右拖动，则新的公式将为“=B3+B5”。如果使用填充柄并向下拖动，则新的公式将为“=A4+A6”。根据拖动方向的不同，行号或列字母会发生变化。
- 绝对单元格引用 - 有时您可能希望公式中引用的单元格在拖动填充柄时不发生变化。（就像上面的公式一样。）如果以单元格 B5 的公式“=B4*(1-B21)”为例。B4 是该月的销售额。当您向右拖动时，您希望它发生变化，以便每列都引用其所在月份的销售额。但是，单元格 B21 旁边没有任何内容。因此，您希望该单元格为绝对引用。为此，在 B 和 21 前面放置一个“$”，因此它将变为“$B$21”，或者公式将变为“=B4*(1-$B$21)”
- 混合单元格引用 - 如果您不需要阻止单元格在列和行方向上都更改引用，就像在本例中一样，您只需将“$”放在您不希望更改的部分。$B21，因此公式将变为“=B4*(1-$B21)”然后引用将始终为 B 列，并且当您向右拖动时，21 不会发生变化。
创建条件 IF 函数语句
[edit | edit source]IF 语句的格式
= if (B4>=$B$24,$B$19,0)
这样解释：如果 B4 的内容大于或等于 B24 的内容，则使用 B19 中的值，否则使用 0。
或者
IF(logical_test, Value_if_True, Value_if_False)
您可以测试以下条件：
- = 等于
- < 小于
- <= 小于或等于
- > 大于
- >= 大于或等于
- <> 不等于
对非相邻单元格求和
[edit | edit source]选择 H 列中应该求和的单元格。如果单元格不相邻，则使用 CTRL 键。
单击“自动求和”按钮以获取总计。
使用格式刷
[edit | edit source]格式刷的工作原理与在 Microsoft Word 中相同。单击要复制格式的单元格，然后单击格式刷（画笔工具），然后单击要设置相同格式的单元格。
创建饼图
[edit | edit source]选择 B3:G3 和 B16:G16。
插入选项卡。
饼图 - 三维饼图
单击“图表工具” - “设计”选项卡中的“移动图表”按钮。
单击“新建工作表”单选按钮。
键入“半年期财务预测器”。
单击“确定”
注意窗口底部的新的图表选项卡。
添加图表标题 - “半年期财务预测器”。
下划线。
关闭图例 - 从“布局”选项卡中单击“图例”，然后单击“无”。
添加数据标签 - 从“布局”选项卡中单击“数据标签” - 选中“类别名称”和“百分比”。
旋转图表 - 从“布局”选项卡中单击三维旋转按钮 - 选择角度。
更改图表的格式 - 右键单击饼图，然后转到“设置数据系列格式” - 探索并确保您访问了“三维格式”以添加轮廓、表面或材质。
尝试从“设置数据系列格式”窗口中手动更改饼图扇区的颜色。
右键单击工作表选项卡，然后单击“重命名”，将名称从“SHEET 1”更改为“半年财务预测”。
右键单击工作表选项卡，然后单击“选项卡颜色”，更改选项卡的颜色，切换活动选项卡以查看颜色变化。
单击并拖动工作表选项卡到右侧或左侧，以更改它们在底部列出的顺序。
在打印前始终进行打印预览！
Office 按钮 - 打印 - 打印预览
我使用“页面设置”按钮进行格式化，然后从预览中打印，以确保我得到想要的结果。
在“页面”选项卡中，您可以从横向更改为纵向，并将文档调整为一页。
在“页边距”选项卡中，您可以更改页面的页边距，以及水平和垂直居中。
在“页眉/页脚”选项卡中，您可以在页面中添加页眉或页脚。
在“工作表”选项卡中，您可以设置打印区域，设置要在每张纸张的顶部或侧面打印的行或列，启用用于打印的网格线，仅以黑白打印，或以较低的质量打印。
如果您有多个工作表的工作簿，可以一次打印整个工作簿。选择包含数据或图表的所有工作表选项卡，然后进行打印预览和正常打印。在打印预览中，您需要使用滚动条查看其他页面。
由于所有数据都与“假设分析”部分相关联，您可以更改假设，并在整个工作簿（包括图表）中观察变化。
缩放工作表，以便您可以看到整个数据表。
在单元格 B19 中输入 72,000 - 这会造成什么变化？为什么？这意味着什么？
在单元格 B20 中输入 3.0 - 这会造成什么变化？为什么？这意味着什么？
在单元格 B25 中输入 15.25 - 这会造成什么变化？为什么？这意味着什么？
单元格 H16 中发生了什么？为什么？
如果您知道想要的结果，但不知道如何获得，这将非常有用。Excel 的此功能允许您输入最终结果，它将帮助您计算因变量。
单击“垂直拆分”框，并在列 E 后拆分屏幕。
调整右侧以显示列 H。
单击单元格 H16。
单击“数据”选项卡。
单击“假设分析”按钮。
单击“目标求解”。
按如下方式填写框：
- 设置单元格 - H16
- 目标值 - 11,000,000
- 通过更改单元格 - $B$25
单击“确定”
What happened?
玩弄这些值。
完成后，单击“取消”。
大学费用预测 在当今经济条件下上大学很困难。提前规划是缓解寻找这笔巨额费用所需资金的压力的关键。创建以下表格
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 科罗拉多州立大学北部 | |||||
| 2 | 费用预测 | |||||
| 3 | 费用 | 大一 | 大二 | 大三 | 大四 | 总计 |
| 4 | 学费 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 5 | 一般费用 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 6 | 医疗 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 7 | 书籍津贴 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 8 | 食宿 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 9 | 个人/杂项 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 10 | 交通 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 11 | 总费用 | |||||
| 12 | ||||||
| 13 | 资源 | 大一 | 大二 | 大三 | 大四 | 总计 |
| 14 | 储蓄 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 15 | 父母 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 16 | 工作 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 17 | 贷款 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 18 | 奖学金 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 19 | 总资源 | |||||
| 20 | ||||||
| 21 | 假设 | |||||
| 22 | 储蓄 | |||||
| 23 | 父母 | |||||
| 24 | 工作 | |||||
| 25 | 贷款 | |||||
| 26 | 奖学金 | |||||
| 27 | 年度增长率 | 5.3% | ||||
公式 #1 = 上一年费用 * (1 + 年度增长率)
公式 #2 = 年总费用 * 相应的假设
这些假设是你对从每个类别中需要多少百分比来支付大学费用的最佳猜测。
创建总费用的饼图。
创建总资源的饼图。
额外学分 - 使用 if 语句，如果总储蓄高于你已知的储蓄额，则通知你。
| 标准 | 1 分 | 2 分 | 3 分 | 4 分 | 5 分 |
|---|---|---|---|---|---|
| 准确创建大学费用表 | 缺少 4 个项目 | 缺少 3 个项目 | 缺少 2 个项目 | 缺少 1 个项目 | 100% 准确 - 没有任何遗漏 |
| 使用逻辑猜测填写假设 | 缺少 4 个项目 | 缺少 3 个项目 | 缺少 2 个项目 | 缺少 1 个项目 | 100% 准确 - 没有任何遗漏 |
| 准确使用公式 #1 | 4 个错误 | 3 个错误 | 2 个错误 | 1 个错误 | 没有错误 |
| 准确使用公式 #2 | 4 个错误 | 3 个错误 | 2 个错误 | 1 个错误 | 没有错误 |
| 准确填写总计 | 4 个错误 | 3 个错误 | 2 个错误 | 1 个错误 | 没有错误 |
| 创建总费用的饼图 | 4 个错误 | 3 个错误 | 2 个错误 | 1 个错误 | 没有错误 - 包括数据标签和标题 |
| 创建总资源的饼图 | 4 个错误 | 3 个错误 | 2 个错误 | 1 个错误 | 没有错误 - 包括数据标签和标题 |