Holarch
課程章節

培訓 · 第 14 單元:試算表 · 第 14.1 章,共 5 章

14.1 工作表、資料與公式 4:16

建立試算表、為工作表命名並設定顏色、輸入資料,並使用公式列、名稱方塊與自動完成撰寫公式。

展示 Holarch 2.35。

您將學到

  • 在專案中建立試算表,並找到工具列、名稱方塊、公式列與工作表分頁
  • 重新命名工作表、設定顏色並新增工作表
  • 輸入數值,並在公式列中撰寫公式
  • 以名稱方塊選取範圍,並以「向下填滿」複製公式
  • 以自動完成補齊函數名稱,並參照另一個工作表

在新的試算表中開始 CubeSat EO-1 示範專案的質量預算:按兩下分頁重新命名工作表並設定顏色、新增第二個工作表、在儲存格中輸入標題與數值、在公式列中撰寫公式、在名稱方塊中選取範圍並向下填滿公式、讓公式自動完成補齊 SUM,並參照另一個工作表上的儲存格。

專案檔案

  • 完成的專案 (.holarch.json;管理專案 → 專案檔案 ▾ → 匯入專案 (.json)…)

影片從 holarch.app 播放;請參閱隱私權政策。

練習

在 Holarch 中於 CubeSat EO-1 上親自操作。

  1. 開啟專案檔案 → 新增示範專案,然後選擇 CubeSat。開啟計畫 → 試算表,按一下新增試算表,並將它命名為 EO-1 Mass Budget。
  2. 按兩下 Sheet1 分頁,輸入 Budget,然後按 輸入。在分頁上按一下右鍵並選擇顏色。
  3. 按一下分頁旁的 +,將新工作表命名為 Assumptions,在 A1 輸入 Mass allocation (kg),在 B1 輸入 12。
  4. 在 預算 上,於 A2:E2 輸入標題 No.、Subsystem、Mass、Contingency、With contingency,並在第 3 至 8 列輸入六個子系統及其質量與預備量。
  5. 在 E3 的公式列中輸入 =C3*(1+D3)。在名稱方塊中輸入 E3:E8,按 輸入,然後選擇填滿 → 向下填滿。
  6. 在 E9 輸入 =SU,從清單中挑選 SUM,然後完成 E3:E8)。在 E10 輸入 =Assumptions!B1,在 E11 輸入 =(E10-E9)/E10。

當 E9 顯示含預備量的總計、E10 顯示來自 Assumptions 工作表的 12,且 E11 顯示餘裕時,即完成。

自我檢查

  1. 對於顯示結果的儲存格,要在哪裡看到它的公式?
  2. 如何將 E3 中的公式複製到 E4:E8,並讓參照自動調整?
  3. 公式如何讀取另一個工作表上的儲存格?
顯示答案
  1. 對於顯示結果的儲存格,要在哪裡看到它的公式?

    選取該儲存格時,在格線上方的公式列中。

  2. 如何將 E3 中的公式複製到 E4:E8,並讓參照自動調整?

    選取 E3:E8(例如在名稱方塊中輸入),然後選擇「填滿 → 向下填滿」。

  3. 公式如何讀取另一個工作表上的儲存格?

    以工作表名稱、驚嘆號與儲存格表示,例如 =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.