Holarch
课程章节

培训 · 课程 14. 电子表格 · 第 14.4 章,共 5 章

14.4 图表和模型数据 2:47

为区域创建图表并设置其坐标轴格式,插入实时模型表,并用 SUMIFS 和 HOLARCH.VALUE 读取模型值。

演示 Holarch 2.35。

您将学到

  • 为区域创建图表,并将图表移到数据旁边
  • 在“设置坐标轴格式”中设置坐标轴边界和单位
  • 插入包含实时模型数据的模型表
  • 用 SUMIFS 对模型值求和,并用 HOLARCH.VALUE 读取单个值
  • 查看模型的变化如何流入工作表和图表

为 EO-1 质量预算创建图表并连接模型:插入各子系统质量的柱形图,并在“设置坐标轴格式”中设置其数值轴;插入包含每个 Asset 的编号和质量的实时只读模型表;用针对该表的 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,勾选 编号 和 质量,命名为 Parts,然后单击 插入。
  5. 在 Budget 上,在 C3 中输入 =SUMIFS(Parts[Mass],Parts[Number],A3&"*") 并向下填充到 C8。在 Assumptions 上,在 B1 中输入 =HOLARCH.VALUE("Spacecraft Mass","Threshold Value","kg")。
  6. 在 数据库 中,将 Payload Camera 的质量从 2.4 kg 改为 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.