課程章節
培訓 · 第 14 單元:試算表 · 第 14.2 章,共 5 章
14.2 格式與版面配置 3:16
以格式代碼設定數字格式、依數值醒目提示儲存格、合併標題,並凍結標題列。
展示 Holarch 2.35。
您將學到
- 合併置中標題,並將標題設為粗體
- 讓欄寬符合其內容
- 套用百分比與自訂數字格式代碼
- 以條件式格式設定規則醒目提示數值
- 凍結標題列
設定 EO-1 質量預算的格式:在各欄上方合併置中標題、將標題設為粗體、讓欄寬符合內容、以百分比顯示預備量、為質量指定加上單位的自訂格式代碼、以條件式格式醒目提示最重的子系統,並凍結標題與欄標題使其保持顯示。
練習
在 Holarch 中於 CubeSat EO-1 上親自操作。
- 開啟本章的起始檔案,或第 14.1 章的活頁簿,切換到 預算 工作表。
- 在 A1 輸入
EO-1 Mass Budget。選取 A1:E1 並按一下合併置中。在名稱方塊中選取 A1:E2,然後按一下粗體。 - 按兩下 B、D 與 E 欄標題的右邊緣,讓欄寬符合內容。
- 選取 D3:D8 並按一下百分比。選取 C3:C8,開啟設定儲存格格式 → 自訂,並輸入
0.00" kg"。對 E3:E10 做同樣的設定,並為 E11 指定格式代碼0.0%。 - 選取 E3:E8,開啟條件式格式設定 → 醒目提示儲存格規則 → 大於,然後輸入
2。 - 選取 A3 並選擇凍結窗格。向下捲動:第 1 與第 2 列會保持顯示。
當質量以 kg 顯示、餘裕以百分比顯示、超過 2 kg 的子系統被醒目提示,且捲動時標題保持不動時,即完成。
自我檢查
- 格式代碼 0.00" kg" 會對數值 2.5 產生什麼效果?
- 為什麼在凍結窗格前選取 A3,會讓第 1 與第 2 列保持顯示?
- 條件式格式設定規則會變更儲存格的數值嗎?
顯示答案
格式代碼 0.00" kg" 會對數值 2.5 產生什麼效果?
它會顯示 2.50 kg。數值仍是數字 2.5,因此公式仍以它計算。
為什麼在凍結窗格前選取 A3,會讓第 1 與第 2 列保持顯示?
凍結窗格會凍結使用中儲存格上方的列與左方的欄。
條件式格式設定規則會變更儲存格的數值嗎?
不會。它只在條件成立時變更儲存格的外觀。
延伸閱讀
文字稿
顯示文字稿 (旁白與畫面內容)
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.