课程章节
培训 · 课程 14. 电子表格 · 第 14.1 章,共 5 章
14.1 工作表、数据和公式 4:16
创建电子表格,命名并为工作表设置颜色,输入数据,并使用编辑栏、名称框和自动完成编写公式。
演示 Holarch 2.35。
您将学到
- 在项目中创建电子表格,并找到工具栏、名称框、编辑栏和工作表标签
- 重命名、着色和添加工作表
- 在编辑栏中输入值和编写公式
- 用名称框选择区域,并用“向下填充”复制公式
- 用自动完成补全函数名,并引用另一个工作表
在新电子表格中开始 CubeSat EO-1 演示项目的质量预算:双击标签重命名工作表并设置颜色,添加第二个工作表,在单元格中输入标题和值,在编辑栏中编写公式,在名称框中选择区域并向下填充公式,用公式自动完成补全 SUM,并引用另一个工作表中的单元格。
练习
在 Holarch 中基于 CubeSat EO-1 亲自完成此操作。
- 打开 项目文件 → 新建演示项目 并选择 CubeSat。打开 计划 → 电子表格,单击 新建电子表格,命名为
EO-1 Mass Budget。 - 双击 Sheet1 标签,输入
Budget并按 Enter。右键单击该标签,选择一种颜色。 - 单击标签旁的 +,将新工作表命名为
Assumptions,在 A1 中输入Mass allocation (kg),在 B1 中输入12。 - 在 Budget 上,在 A2:E2 中输入标题
No.、Subsystem、Mass、Contingency、With contingency,并在第 3 至 8 行输入六个子系统及其质量和裕度。 - 在 E3 中,在编辑栏中输入
=C3*(1+D3)。在名称框中输入E3:E8,按 Enter,然后选择 填充 → 向下填充。 - 在 E9 中输入
=SU,从列表中选择 SUM,并补全E3:E8)。在 E10 中输入=Assumptions!B1,在 E11 中输入=(E10-E9)/E10。
当 E9 显示含裕度的合计、E10 显示来自 Assumptions 工作表的 12、E11 显示余量时,即完成练习。
自测
- 在哪里查看显示结果的单元格的公式?
- 如何将 E3 中的公式复制到 E4:E8 并调整其引用?
- 公式如何读取另一个工作表中的单元格?
显示答案
在哪里查看显示结果的单元格的公式?
选中单元格时,在网格上方的编辑栏中。
如何将 E3 中的公式复制到 E4:E8 并调整其引用?
选择 E3:E8(例如在名称框中输入),然后选择“填充 → 向下填充”。
公式如何读取另一个工作表中的单元格?
使用工作表名称、感叹号和单元格,例如 =Assumptions!B1。
延伸阅读
文字稿
显示文字稿 (旁白和屏幕内容)
Introduction
Welcome to the spreadsheet track. A Holarch spreadsheet is an Excel-style workbook inside a project, calculated in Excel's formula language, right next to the model. Across five chapters you build one workbook: the mass budget of the CubeSat EO-1.
In this chapter you create the workbook, set up its sheets, enter the budget and write its first formulas.
A new spreadsheet
We work in the CubeSat EO-1 demo project. To make your own copy, open Project Files, then New Demo Project, and choose the CubeSat. Its model already holds the mass of every component.
On screen: The CubeSat EO-1 demo project: Home.
Open Plan, then Spreadsheets, and click New Spreadsheet. Name it EO-1 Mass Budget.
On screen: Plan → Spreadsheets → + New Spreadsheet → EO-1 Mass Budget.
The editor opens. The toolbar is at the top. Below it are the Name Box, which shows the active cell, and the formula bar, which shows what the cell holds. The sheet tabs are at the bottom.
On screen: The editor: toolbar, Name Box, formula bar, grid, sheet tabs.
Sheets and tabs
Double-click the tab of Sheet1 to rename it. Type Budget, and press Enter.
On screen: Double-click Sheet1 → Budget ↵.
Right-click the tab for its menu: insert, rename, move, hide or delete a sheet, and choose a tab color. Choose Blue.
On screen: Right-click Budget → Tab Color: Blue.
The plus beside the tabs adds a sheet. Add one, and name it Assumptions. It will hold the inputs the budget uses.
On screen: + → double-click Sheet2 → Assumptions ↵.
Entering data
Type the first input. Double-click A1, type Mass allocation in kilograms, and press Enter. In B1, type twelve.
On screen: A1: Mass allocation (kg); B1: 12.
Back on the Budget sheet, type the column headings in row two: number, subsystem, mass, contingency, and mass with contingency. Enter confirms each entry.
On screen: Budget: A2 No., B2 Subsystem, C2 Mass, D2 Contingency, E2 With contingency.
Then the first subsystem: one point one, Payload Camera, an estimate of two point five kilograms, and a contingency of ten percent, typed as zero point one.
On screen: Row 3: 1.1, Payload Camera, 2.5, 0.1.
The other five subsystems are entered the same way.
On screen: Card while rows 4 to 8 are filled.
Formulas
Now a formula. Select E3, click the formula bar, and type equals C3 times, in brackets, one plus D3. Press Enter. The cell shows the result, and the formula bar keeps the formula.
On screen: E3 → formula bar: =C3*(1+D3) ↵.
The Name Box also selects. Type E3 colon E8, and press Enter. Then open Fill, and choose Fill Down: the formula is copied to every row, with its references adjusted.
On screen: Name Box E3:E8 ↵ → Fill ▾ → Fill Down.
In row nine, the total. Type Total in B9. In E9, start typing equals S U. Autocomplete lists the functions that match, with what each one does.
On screen: B9 Total; E9 =SU: the function list.
Click SUM. Holarch writes the name and the bracket, and the hint shows the arguments. Type E3 colon E8, close the bracket, and press Enter.
On screen: SUM → E3:E8) ↵.
Formulas can use other sheets. Type Allocation in B10, and in E10, equals Assumptions, exclamation mark, B1.
On screen: B10 Allocation; E10 =Assumptions!B1 ↵.
Last, the margin: what is left of the allocation, as a fraction of it. Type Margin in B11, and in E11, equals, in brackets, E10 minus E9, divided by E10.
On screen: B11 Margin; E11 =(E10-E9)/E10 ↵.
Every edit is one undo step, and the spreadsheet is saved with the project, as an item with its own history.
On screen: The budget so far.
Recap
To recap: rename tabs by double-clicking, color them from their menu, type in cells, write formulas in the formula bar, select ranges in the Name Box, and let autocomplete finish function names.
In the next chapter, you format the budget and lay it out.