課程章節
培訓 · 第 14 單元:試算表 · 第 14.3 章,共 5 章
14.3 表格、尋找與檢視 2:17
將範圍格式化為表格、尋找與取代文字,並在不變更儲存格的情況下儲存具名檢視。
展示 Holarch 2.35。
您將學到
- 將範圍格式化為具名表格,並在公式中使用它
- 一次在整個活頁簿中尋找與取代文字
- 開啟篩選按鈕並排序一欄
- 儲存具名檢視,並在檢視之間切換
整理 EO-1 質量預算:將子系統各列格式化為具有名稱、帶狀列與篩選按鈕的表格,以「尋找和取代」在每個地方重新命名一個子系統,然後在與專案共用的具名檢視中依質量排序表格,再切換回「預設」檢視,它仍依儲存的樣子顯示儲存格。
練習
在 Holarch 中於 CubeSat EO-1 上親自操作。
- 開啟本章的起始檔案,或您在第 14.2 章的活頁簿。
- 選取 A2:E8,按一下表格,將它命名為
Subsystems,然後按一下確定。 - 開啟尋找和取代…,尋找
Comms,開啟取代欄位,輸入Communications,按一下全部尋找,然後按全部取代。 - 在表格內按一下,開啟排序與篩選並開啟篩選按鈕。在 With contingency 上選擇從 Z 到 A 排序。
- 按一下儲存檢視,將它命名為
Heaviest first,選擇所有具存取權的人並儲存。在預設與 Heaviest first 之間切換。
當表格名為 Subsystems、Comms 變成 Communications,且 Heaviest first 檢視會排序各列,而「預設」依原順序顯示時,即完成。
自我檢查
- 公式如何參照表格 Subsystems 的 Mass 欄?
- 在檢視中排序會變更儲存格嗎?
- 除了排序與篩選之外,檢視還會保留什麼?
顯示答案
公式如何參照表格 Subsystems 的 Mass 欄?
寫成 Subsystems[Mass]:表格名稱後面加上以方括號括住的欄名。
在檢視中排序會變更儲存格嗎?
不會。檢視只變更您看工作表的方式;切換回「預設」會依原順序顯示各列。
除了排序與篩選之外,檢視還會保留什麼?
隱藏的欄、欄寬與凍結的窗格。
延伸閱讀
文字稿
顯示文字稿 (旁白與畫面內容)
Introduction
Chapter three: tables, find and views. A table gives a block of rows a name and a style. Find and Replace changes text in one step. And views show the same cells in different ways.
In this chapter you organize the budget.
Tables
Select the headings and the six subsystems, A2 to E8, and click Table.
On screen: Name Box A2:E8 → Table.
The dialog shows the range, and that the first row holds the headers. Name the table Subsystems, and click OK.
On screen: Range A2:E8, headers; name Subsystems → OK.
The table has a styled header row and banded rows, and grows when you add a row below it. Its name works in formulas: Subsystems, with a column name in square brackets.
On screen: The table: header style, banded rows.
Find and Replace
One subsystem is abbreviated. Open Find and Replace. Find Comms, open the replace field, and type Communications.
On screen: Find ▾ → Comms; ⇄ → Communications.
Find All lists each match with its sheet and cell. Click Replace All: the name is changed everywhere it appears, in one undo step. Close the bar.
On screen: Find All → the results; Replace All → close.
Named views
Now a view. Views change how you see the sheet, never the cells. Click in the table, open Sort and Filter, and turn on Filter Buttons.
On screen: B3 → Sort & Filter ▾ → Filter Buttons.
Click the filter button on With contingency, and choose Sort Z to A: the heaviest subsystem comes first.
On screen: With contingency ▾ → Sort Z to A.
The views bar shows the unsaved change. Click Save View, name it Heaviest first, choose Everyone with access, and save it.
On screen: Save View → Heaviest first, Everyone with access → Save View.
A view keeps sort, filters, hidden columns, widths and frozen panes. Click Default: the rows are back in their order, because the cells never changed. Everyone with access can switch views without affecting anyone else.
On screen: Default → rows in order; Heaviest first.
Recap
To recap: format rows as a named table, use Find and Replace for changes in many cells, and save sorts and filters as views.
In the next chapter, you chart the budget and feed it from the model.