Holarch
課程章節

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

14.4 圖表與模型資料 2:47

為範圍建立圖表並設定座標軸格式、插入即時模型表格,並以 SUMIFS 與 HOLARCH.VALUE 讀取模型數值。

展示 Holarch 2.35。

您將學到

  • 為範圍建立圖表,並將圖表移到資料旁
  • 在「設定座標軸格式」中設定座標軸界限與單位
  • 插入即時模型資料的模型表格
  • 以 SUMIFS 加總模型數值,並以 HOLARCH.VALUE 讀取單一數值
  • 看到模型變更流入工作表與圖表

為 EO-1 質量預算建立圖表並連接模型:插入子系統質量的直條圖,並在「設定座標軸格式」中設定其數值座標軸;插入列出每個資產及其編號與質量的唯讀即時模型表格;以對該表格的 SUMIFS 取代手動輸入的估計值;以 HOLARCH.VALUE 從模型讀取質量分配;然後在模型中變更一個質量,看預算隨之更新。

專案檔案

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

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

練習

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

  1. 開啟本章的起始檔案,或您在第 14.3 章的活頁簿。
  2. 選取 B2:C8,開啟圖表並選擇直條圖。將圖表拖曳到預算的右側。
  3. 在圖表上按一下右鍵,選擇設定縱座標軸格式…,將最大值設為 5、主要單位設為 1。
  4. 新增名為 Model 的工作表。在 A1 按一下模型表格,選擇類別 Asset,勾選編號與 Mass,將它命名為 Parts,然後按一下插入。
  5. 在 預算 上,於 C3 輸入 =SUMIFS(Parts[Mass],Parts[Number],A3&"*") 並向下填滿到 C8。在 Assumptions 上,於 B1 輸入 =HOLARCH.VALUE("Spacecraft Mass","Threshold Value","kg")。
  6. 在資料庫中,將 Payload Camera 的質量從 2.4 改為 2.9 kg,然後回到試算表。

當質量來自模型、分配值從模型讀出 12,且總計、餘裕與圖表都跟著 Payload Camera 的變更更新時,即完成。

自我檢查

  1. 試算表可以變更模型嗎?
  2. SUMIFS 準則中的 A3&"*" 會比對什麼?
  3. 要在哪裡設定圖表座標軸的最大值與主要單位?
顯示答案
  1. 試算表可以變更模型嗎?

    不行。模型表格與 HOLARCH 函數只讀取模型;試算表永遠不會寫入模型。

  2. SUMIFS 準則中的 A3&"*" 會比對什麼?

    每個以該子系統編號開頭的零件編號,例如 1.1 的 1.1.1 與 1.1.2。

  3. 要在哪裡設定圖表座標軸的最大值與主要單位?

    在「設定座標軸格式」中,從圖表的右鍵選單開啟;欄位留空則保持自動數值。

延伸閱讀

文字稿

顯示文字稿 (旁白與畫面內容)

Introduction

Chapter four: charts and model data. A chart shows the budget at a glance. And a spreadsheet can read the model, so the budget stays current without retyping.

In this chapter you chart the budget, then feed it from the model.

Charts

Select the subsystems and their masses, B2 to C8. Open Chart, and choose Column.

On screen: Name Box B2:C8 → Chart ▾ → Column.

The chart floats over the sheet. Drag it to the right of the budget, so the totals stay in view.

On screen: Drag the chart right of column E.

The chart follows its cells. Right-click it for its menu, and choose Format Vertical Axis.

On screen: Right-click the chart → Format Vertical Axis….

Format Axis sets the bounds and units. Set the maximum to five and the major unit to one. Leave a box empty to keep it automatic. Close the panel.

On screen: Maximum 5, Major 1 → close.

Model data

The masses so far are typed estimates. The model holds the current mass of every component, so read them from there. Add a sheet, and name it Model.

On screen: + → Model.

Select A1, and click Model Table. Choose the class Asset, tick Number and Mass, name the table Parts, and click Insert.

On screen: A1 → Model Table… → Class Asset; Number, Mass; name Parts → Insert.

The table lists every asset with its number and mass, live and read-only. A spreadsheet reads the model, and never writes to it.

On screen: The Parts table.

Back in the budget, replace the estimate in C3. The SUMIFS function adds the masses of the parts whose number starts with the subsystem's number.

On screen: Budget → C3: =SUMIFS(Parts[Mass],Parts[Number],A3&"*") ↵.

Fill it down to C8. Every subsystem now adds up its components from the model.

On screen: Name Box C3:C8 → Fill ▾ → Fill Down.

HOLARCH.VALUE reads one value of one item, in the unit you ask for. On the Assumptions sheet, the allocation becomes the threshold of the spacecraft mass measure in the model.

On screen: Assumptions → B1: =HOLARCH.VALUE("Spacecraft Mass","Threshold Value","kg") ↵.

The sheet follows the model

Now change the model. In the Database, open the payload camera's mass, and change it from two point four to two point nine kilograms.

On screen: Database → Payload Camera → Mass 2.4 → 2.9.

Back in the spreadsheet, with its editor tab. The payload camera's mass, the totals, the margin and the chart have all followed, with no edit to the sheet.

On screen: Editor tab → Budget: 2.90 kg, totals, margin, chart.

Recap

To recap: chart a range and format its axis, insert a Model Table for live model data, sum it with SUMIFS, and read single values with HOLARCH.VALUE.

In the last chapter, you import an Excel workbook and get AI help with formulas.