Spreadsheet data manipulation and analysis

邊玩邊學

回答這些題目賺取能量，接著就能釣魚、探索。不需要帳號。

給教育者：為 Spreadsheet data manipulation and analysis（Information and Communication Technology、Information Processing）準備好可直接使用的課程投影片, 複習筆記——用於你的課程，或把這個主題當成互動班級活動，讓學習者以即時遊戲的方式進行。

課程筆記

Cells, formulas and functions 儲存格、公式與函數

A spreadsheet is a grid of cells, each named by its column letter and row number, e.g. B3. A group of cells is a range, e.g. \texttt{B2:B31}.
試算表由儲存格組成，每個儲存格以欄字母和列號命名，例如 B3。一組儲存格稱為範圍，例如 \texttt{B2:B31}。
A formula starts with an equals sign and calculates a value, e.g. \texttt{=B2+C2}. When the data changes, the result updates automatically.
公式以等號開始並計算數值，例如 \texttt{=B2+C2}。數據改變時，結果會自動更新。
A function is a built-in formula: \texttt{=SUM(B2:B31)}, \texttt{=AVERAGE(B2:B31)}, \texttt{=MAX(B2:B31)}, \texttt{=MIN(B2:B31)}, \texttt{=COUNT(B2:B31)}.
函數是內置的公式：\texttt{=SUM(B2:B31)}、\texttt{=AVERAGE(B2:B31)}、\texttt{=MAX(B2:B31)}、\texttt{=MIN(B2:B31)}、\texttt{=COUNT(B2:B31)}。
Other useful functions: \texttt{ROUND} (round to n decimal places), \texttt{INT} (whole-number part), \texttt{RANK}, \texttt{COUNTIF}, \texttt{SUMIF}, \texttt{VLOOKUP}.
其他常用函數：\texttt{ROUND}（四捨五入至 n 個小數位）、\texttt{INT}（取整數部分）、\texttt{RANK}、\texttt{COUNTIF}、\texttt{SUMIF}、\texttt{VLOOKUP}。

Relative, absolute and mixed references 相對、絕對及混合參照

A relative reference (e.g. \texttt{B2}) changes when the formula is copied: \texttt{=B2×C2} in D2 becomes \texttt{=B3×C3} in D3.
相對參照（例如 \texttt{B2}）在複製公式時會改變：D2 的 \texttt{=B2×C2} 複製到 D3 會變成 \texttt{=B3×C3}。
An absolute reference (e.g. \texttt{\$E\$1}) stays the same when copied. Use it for a fixed value such as an exchange rate or discount rate.
絕對參照（例如 \texttt{\$E\$1}）在複製時保持不變。適用於固定數值，例如匯率或折扣率。
A mixed reference fixes only the column (\texttt{\$A2}) or only the row (\texttt{A\$2}).
混合參照只固定欄（\texttt{\$A2}）或只固定列（\texttt{A\$2}）。
Example: price in HK$ in column B, exchange rate in E1. In C2 enter \texttt{=B2×\$E\$1}, then copy it down the column.
例子：B 欄是港元價錢，E1 是匯率。在 C2 輸入 \texttt{=B2×\$E\$1}，然後向下複製。

Operators 運算符

Arithmetic: \texttt{+} \texttt{-} \texttt{×} \texttt{/} \texttt{\textasciicircum{}}. Order: brackets, then \texttt{\textasciicircum{}}, then \texttt{×} and \texttt{/}, then \texttt{+} and \texttt{-}. So \texttt{=2+3×4\textasciicircum{}2} gives 50.
算術運算符：\texttt{+} \texttt{-} \texttt{×} \texttt{/} \texttt{\textasciicircum{}}。運算次序：括號，然後 \texttt{\textasciicircum{}}，然後 \texttt{×} 和 \texttt{/}，最後 \texttt{+} 和 \texttt{-}。所以 \texttt{=2+3×4\textasciicircum{}2} 得 50。
Relational (comparison): \texttt{=} \texttt{<>} \texttt{>} \texttt{<} \texttt{≥} \texttt{≤} — each gives TRUE or FALSE.
關係運算符（比較）：\texttt{=} \texttt{<>} \texttt{>} \texttt{<} \texttt{≥} \texttt{≤}——每個都會得出 TRUE 或 FALSE。
Logical functions: \texttt{AND} (all conditions true), \texttt{OR} (at least one true), \texttt{NOT} (reverses TRUE/FALSE).
邏輯函數：\texttt{AND}（所有條件為真）、\texttt{OR}（最少一個為真）、\texttt{NOT}（把 TRUE／FALSE 反轉）。
\texttt{IF} chooses between two results: \texttt{=IF(B2≥50,"Pass","Fail")}. Combine: \texttt{=IF(AND(B2≥50,C2≥50),"Pass","Fail")}.
\texttt{IF} 在兩個結果中選一個：\texttt{=IF(B2≥50,"Pass","Fail")}。可組合使用：\texttt{=IF(AND(B2≥50,C2≥50),"Pass","Fail")}。

Counting, looking up and multiple worksheets 計數、查找及多個工作表

\texttt{=COUNTIF(B2:B31,"≥50")} counts cells meeting one condition. \texttt{=SUMIF(A2:A31,"4A",B2:B31)} adds values whose row meets a condition.
\texttt{=COUNTIF(B2:B31,"≥50")} 計算符合一個條件的儲存格數目。\texttt{=SUMIF(A2:A31,"4A",B2:B31)} 把符合條件的列的數值相加。
\texttt{=VLOOKUP(A2,Prices!A2:C50,3,FALSE)} searches the first column of a table for A2 and returns the value in column 3 of that row. FALSE means an exact match.
\texttt{=VLOOKUP(A2,Prices!A2:C50,3,FALSE)} 在表格第一欄尋找 A2，並傳回該列第 3 欄的數值。FALSE 表示完全相符。
A workbook can hold many worksheets. Refer to another sheet with its name and !, e.g. \texttt{=Term1!D2+Term2!D2}.
一個活頁簿可包含多個工作表。以工作表名稱加 ! 參照其他工作表，例如 \texttt{=Term1!D2+Term2!D2}。
Common errors: \texttt{\#DIV/0!} (dividing by zero or an empty cell), \texttt{\#REF!} (a referenced cell was deleted), \texttt{\#VALUE!} (wrong data type), \texttt{\#NAME?} (misspelt function).
常見錯誤：\texttt{\#DIV/0!}（除以零或空白儲存格）、\texttt{\#REF!}（參照的儲存格已被刪除）、\texttt{\#VALUE!}（數據類型錯誤）、\texttt{\#NAME?}（函數名稱拼錯）。

Sorting, filtering and searching 排序、篩選及搜尋

Sorting rearranges rows in ascending or descending order. With multiple keys, e.g. sort by Class (A→Z) and then by Mark (largest first) within each class.
排序把各列按遞增或遞減次序重新排列。使用多個排序鍵時，例如先按班別（A→Z），再在每班內按分數（由大至小）排序。
Filtering shows only the rows meeting criteria and hides the rest — the data is not deleted.
篩選只顯示符合條件的列並隱藏其餘各列——數據不會被刪除。
Multiple criteria: AND (Class is 4A and Mark is above 80) gives fewer rows; OR (Class is 4A or 4B) gives more rows.
多個條件：AND（班別是 4A 並且分數高於 80）會得到較少列；OR（班別是 4A 或 4B）會得到較多列。
Searching (Find / Find and Replace) locates cells containing a value across a sheet or the whole workbook.
搜尋（尋找／尋找及取代）可在工作表或整個活頁簿中找出包含某個值的儲存格。

Pivot tables and charts 樞紐分析表及圖表

A pivot table summarises a large table by grouping it, e.g. total sales by region (rows) and month (columns), without writing formulas.
樞紐分析表把大型表格分組並作摘要，例如按地區（列）及月份（欄）計算總銷售額，而無須編寫公式。
Fields can be dragged to rows, columns, values and filters to look at the data from different angles.
可把欄位拖放到列、欄、值及篩選區，從不同角度檢視數據。
A pivot chart is a chart linked to a pivot table; it changes when the pivot table changes.
樞紐分析圖是連結到樞紐分析表的圖表；樞紐分析表改變時它亦會改變。
Choose charts by purpose: line for trends over time, column/bar for comparing groups, pie for parts of a whole, scatter for relationships between two variables.
按目的選擇圖表：折線圖顯示隨時間的趨勢，棒形圖比較不同組別，圓形圖顯示部分佔整體的比例，散佈圖顯示兩個變數之間的關係。

What-if analysis and predictions 假設分析及預測

What-if analysis changes input values to see how the results change, helping people make informed decisions.
假設分析改變輸入值以觀察結果如何變化，幫助人們作出明智的決定。
Goal Seek: finds the input needed to reach a target output, e.g. what exam mark is needed for an overall grade of 70.
目標搜尋：找出達到目標輸出所需的輸入，例如要考取多少分才能令總成績達 70 分。
Scenario Manager: saves and compares several sets of inputs, e.g. best case, normal case, worst case for a school fair budget.
分析藍本管理員：儲存並比較多組輸入值，例如學校攤位預算的最佳、一般及最差情況。
Data table: shows results for a range of input values at once. Trendlines on a chart can predict future values, e.g. next year's sales.
資料表：一次過顯示一系列輸入值的結果。圖表上的趨勢線可預測未來數值，例如明年的銷售額。

投影片

Step through every slide for this topic — plus flashcards and revision notes — with a free account.

免費註冊登入

練習題

免費預覽——34 題中的 8 題。註冊即可查看全部。

1.Which of the following is a valid spreadsheet formula to add the values in cells A1 to A5? 以下哪條是把 A1 至 A5 的數值相加的有效試算表公式？
Easy
- A\texttt{=SUM(A1:A5)}
- B\texttt{SUM A1 to A5}
- C\texttt{=ADD(A1-A5)}
- D\texttt{=A1:A5}
2.What is the main advantage of using formulas instead of typing the results? 使用公式而不直接輸入結果的主要優點是甚麼？
Easy
- AThe file becomes smaller 檔案變得較小
- BResults update automatically when the data changes 數據改變時結果會自動更新
- CFormulas cannot contain errors 公式不可能出錯
- DThe data is encrypted 數據會被加密
3.Cell C2 contains \texttt{=A2×B2}. It is copied to D3. What formula appears in D3? C2 的公式是 \texttt{=A2×B2}，複製到 D3 後，D3 會出現甚麼公式？
Medium
- A\texttt{=A2×B2}
- B\texttt{=A3×B3}
- C\texttt{=B3×C3}
- D\texttt{=B2×C2}
4.An exchange rate is stored in cell F1. Which formula in C2 can be copied down the column to convert every price in column B correctly? 匯率存於 F1。C2 應輸入哪條公式，才能向下複製並正確轉換 B 欄的每個價錢？
Medium
- A\texttt{=B2×F1}
- B\texttt{=\$B\$2×\$F\$1}
- C\texttt{=\$B\$2×F1}
- D\texttt{=B2×\$F\$1}
5.The reference \texttt{A\$2} keeps the row fixed but lets the column change when copied. 參照 \texttt{A\$2} 在複製時固定列，但欄可以改變。
Medium
True or false?
6.What value does \texttt{=(2+3)×4\textasciicircum{}2} give? \texttt{=(2+3)×4\textasciicircum{}2} 得出甚麼數值？
Medium
- A50
- B80
- C400
- D1 600
7.Match each operator with its type. 把每個運算符配對其類型。
Medium
- \texttt{\textasciicircum{}}
- \texttt{<>}
- \texttt{AND}
- \texttt{≥}
- Arithmetic 算術
- Relational: not equal to 關係：不等於
- Logical 邏輯
- Relational: greater than or equal to 關係：大於或等於
8.
What value does \texttt{=SUM(B2:B5)} give? \texttt{=SUM(B2:B5)} 得出甚麼數值？
A B C
1 Name Test 1 Test 2
2 Amy 72 80
3 Ben 45 58
4 Chris 90 86
5 Dora 38 49
Easy
- A207
- B245
- C273
- D518

複習筆記

## Formulas 公式 - Start with an equals sign; update automatically. 以等號開始；會自動更新。 - Functions: SUM, AVERAGE, MAX, MIN, COUNT, COUNTIF, SUMIF, ROUND, INT, IF, VLOOKUP. 常用函數。 ## References 參照 - Relative B2 changes when copied; absolute $B$2 does not; mixed $B2 or B$2. 相對參照會變；絕對參照不變；混合參照只固定欄或列。 ## Operators 運算符 - Arithmetic with order: brackets, powers, multiply and divide, add and subtract. 算術次序：括號、乘冪、乘除、加減。 - Relational: =, <>, >, <, >=, <=. Logical: AND, OR, NOT. 關係及邏輯運算符。 ## Sort, filter, search 排序、篩選、搜尋 - Multiple sort keys; filters hide rows; AND narrows, OR widens. 多個排序鍵；篩選隱藏列；AND 收窄，OR 擴闊。 ## Analysis 分析 - Pivot tables and pivot charts summarise large data. 樞紐分析表及圖摘要大量數據。 - Line for trends, column to compare, pie for proportions, scatter for relationships. 折線看趨勢、棒形作比較、圓形看比例、散佈看關係。 - Goal Seek, Scenario Manager, data tables, trendlines for predictions. 目標搜尋、分析藍本管理員、資料表、趨勢線作預測。

Formula 公式: An expression starting with an equals sign that calculates a value. 以等號開始、計算數值的算式。
Function 函數: A built-in formula such as SUM or AVERAGE. 內置公式，例如 SUM 或 AVERAGE。
Relative reference 相對參照: A cell reference that changes when the formula is copied. 複製公式時會改變的儲存格參照。
Absolute reference 絕對參照: A cell reference with $ signs that does not change when copied. 帶 $ 符號、複製時不變的儲存格參照。
Relational operator 關係運算符: An operator that compares two values and gives TRUE or FALSE. 比較兩個數值並得出 TRUE 或 FALSE 的運算符。
Filter 篩選: Showing only rows that meet criteria, hiding the rest. 只顯示符合條件的列，並隱藏其餘各列。
Pivot table 樞紐分析表: A table that summarises data by grouping it. 把數據分組並作摘要的表格。
Goal Seek 目標搜尋: Finds the input needed to reach a target output. 找出達到目標輸出所需的輸入。
Scenario Manager 分析藍本管理員: Stores and compares several sets of input values. 儲存並比較多組輸入值。
Trendline 趨勢線: A line on a chart showing the general pattern, used to predict. 圖表上顯示整體趨勢、用作預測的線。

字卡

How does every formula start? 公式以甚麼開始？

With an equals sign (=). 以等號（=）開始。

Relative vs absolute reference? 相對與絕對參照？

Relative (B2) changes when copied; absolute ($B$2) stays fixed. 相對參照（B2）複製時會變；絕對參照（$B$2）保持不變。

Order of arithmetic operations? 算術運算次序？

Brackets, powers, multiply/divide, add/subtract. 括號、乘冪、乘除、加減。

What does <> mean? <> 是甚麼意思？

Not equal to. 不等於。

COUNTIF vs SUMIF? COUNTIF 與 SUMIF？

COUNTIF counts cells meeting a condition; SUMIF adds values meeting a condition. COUNTIF 計算符合條件的儲存格數目；SUMIF 把符合條件的數值相加。

What does FALSE mean in VLOOKUP? VLOOKUP 中的 FALSE 代表甚麼？

Exact match only. 只接受完全相符。

What causes #DIV/0!? #DIV/0! 的成因？

Dividing by zero or an empty cell. 除以零或空白儲存格。

Does filtering delete data? 篩選會刪除數據嗎？

No — it only hides rows. 不會——只是隱藏列。

When use a pivot table? 何時使用樞紐分析表？

To summarise a large table by groups, e.g. sales by region and month. 按組別摘要大型表格，例如按地區及月份的銷售額。

Goal Seek vs Scenario Manager? 目標搜尋與分析藍本管理員？

Goal Seek finds the input for a target; Scenario Manager compares several input sets. 目標搜尋找出達標所需輸入；分析藍本管理員比較多組輸入。

Best chart for a trend over time? 顯示隨時間趨勢的最佳圖表？

Line chart. 折線圖。

Unlock all 34 questions, flashcards & more

建立免費帳號，即可查看這個主題的所有題目、投影片、字卡與複習筆記。

免費註冊登入

歷屆試題

這個主題的歷屆試題練習即將推出。

即將推出

	A	B	C
1	Name	Test 1	Test 2
2	Amy	72	80
3	Ben	45	58
4	Chris	90	86
5	Dora	38	49

	A	B	C
1	Name	Test 1	Test 2
2	Amy	72	80
3	Ben	45	58
4	Chris	90	86
5	Dora	38	49

	A	B	C
1	Name	Test 1	Test 2
2	Amy	72	80
3	Ben	45	58
4	Chris	90	86
5	Dora	38	49

	A	B	C
1	Name	Test 1	Test 2
2	Amy	72	80
3	Ben	45	58
4	Chris	90	86
5	Dora	38	49