Holarch
课程章节

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

14.2 格式和布局 3:16

用格式代码设置数字格式,按数值突出显示单元格,合并标题并冻结标题行。

演示 Holarch 2.35。

您将学到

  • 合并并居中标题,将标题行设为粗体
  • 使列宽适合内容
  • 应用百分比和自定义数字格式代码
  • 用条件格式规则突出显示数值
  • 冻结标题行

设置 EO-1 质量预算的格式:将标题合并并居中于各列之上,将标题行设为粗体,使列宽适合内容,将裕度显示为百分比,用添加单位的自定义格式代码设置质量,用条件格式突出显示最重的子系统,并冻结标题和标题行使其保持可见。

项目文件

  • 起始项目 (.holarch.json;管理项目 → 项目文件 ▾ → 导入项目 (.json)…)
  • 完成项目 (.holarch.json;管理项目 → 项目文件 ▾ → 导入项目 (.json)…)

视频从 holarch.app 播放;参见隐私政策。

练习

在 Holarch 中基于 CubeSat EO-1 亲自完成此操作。

  1. 打开本章的起始文件,或第 14.1 章中的工作簿,并切换到 Budget 工作表。
  2. 在 A1 中输入 EO-1 Mass Budget。选择 A1:E1,单击 合并后居中。在名称框中选择 A1:E2,单击 加粗。
  3. 双击 B、D 和 E 列标题的右边缘,使列宽适合内容。
  4. 选择 D3:D8,单击 百分比。选择 C3:C8,打开 设置单元格格式 → 自定义,输入 0.00" kg"。对 E3:E10 做同样的设置,并将 E11 的代码设为 0.0%。
  5. 选择 E3:E8,打开 条件格式 → 突出显示单元格规则 → 大于,输入 2。
  6. 选择 A3,然后选择 冻结窗格。向下滚动:第 1 行和第 2 行保持可见。

当质量以 kg 显示、余量以百分比显示、超过 2 kg 的子系统被突出显示且滚动时标题行保持不动时,即完成练习。

自测

  1. 格式代码 0.00" kg" 对值 2.5 有什么作用?
  2. 为什么在冻结窗格前选择 A3 能让第 1 行和第 2 行保持可见?
  3. 条件格式规则会改变单元格的值吗?
显示答案
  1. 格式代码 0.00" kg" 对值 2.5 有什么作用?

    显示为 2.50 kg。值仍是数字 2.5,因此公式仍可用它计算。

  2. 为什么在冻结窗格前选择 A3 能让第 1 行和第 2 行保持可见?

    “冻结窗格”冻结活动单元格上方的行和左侧的列。

  3. 条件格式规则会改变单元格的值吗?

    不会。它只在条件成立时改变单元格的外观。

延伸阅读

文字稿

显示文字稿 (旁白和屏幕内容)

Introduction

Chapter two: formats and layout. A format changes how a value looks, never the value itself, so formulas keep calculating with the full number.

In this chapter you make the budget easy to read.

Title and headings

Give the sheet a title. In A1, type EO-1 Mass Budget.

On screen: A1: EO-1 Mass Budget.

Select A1 to E1 in the Name Box. Open Merge, and choose Merge and Center: the five cells become one, with the title in the middle.

On screen: Name Box A1:E1 → Merge ▾ → Merge & Center.

Make the title and the headings bold: select them, and click Bold, or press Command B.

On screen: Name Box A1:E2 → Bold.

The subsystem names are wider than their column. Double-click the right edge of column B's heading, and the column fits its longest entry. Do the same for D and E.

On screen: Double-click the header borders of B, D and E: the columns fit.

Number formats

Now the numbers. Select the contingencies, D3 to D8, and click Percent. Zero point one shows as ten percent.

On screen: Name Box D3:D8 → %.

Select the masses, C3 to C8, and open Number Format. The menu lists the common formats, with a sample of each. Choose More Number Formats.

On screen: Name Box C3:C8 → Number Format ▾ → More Number Formats….

Format Cells shows the format as a code. Choose Custom, and type zero point zero zero, then a space and k g, in quotes. Text in quotes is shown as it is, so each mass reads in kilograms. The sample shows the result. Click OK.

On screen: Category: Custom; code 0.00 " kg" → sample → OK.

The masses with contingency get the same code. Select E3 to E10, open More Number Formats again, choose Custom, and type the code.

On screen: Name Box E3:E10 → More Number Formats… → Custom → 0.00 " kg" → OK.

The margin is a fraction. Select E11, and give it the code zero point zero percent: a percentage with one decimal.

On screen: E11 → More Number Formats… → Custom → 0.0% → OK.

Conditional formatting

Conditional formatting colors cells by their values. Select E3 to E8, open Conditional Formatting, then Highlight Cells Rules, and choose Greater Than.

On screen: Name Box E3:E8 → Conditional Formatting ▾ → Highlight Cells Rules ▸ → Greater Than….

Type two, keep the red fill, and click OK. The two heaviest subsystems stand out, and the color follows the values when they change.

On screen: Value 2 → OK: Payload Camera and Structure and Thermal turn red.

Freeze panes

Last, freeze panes keep the title and headings in view while you scroll. Select A3, open Freeze, and choose Freeze Panes: everything above the active cell stays put.

On screen: A3 → Freeze ▾ → Freeze Panes.

Scroll the sheet down. The rows move under the frozen title and headings.

On screen: Scroll down, then back up.

Recap

To recap: merge and center a title, fit columns by double-clicking their edge, choose formats from the menu or write a format code, color cells with a rule, and freeze the headings.

In the next chapter, you turn the budget into a table, find and replace text, and save views.