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

और सवाल

9.
What value does \texttt{=COUNTIF(B2:C5,"≥50")} give? \texttt{=COUNTIF(B2:C5,"≥50")} 得出甚麼數值？
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
Medium
- A2
- B3
- C4
- D5
10.
D3 contains \texttt{=IF(OR(B3≥50,C3≥50),"Pass","Fail")}. What is displayed? D3 的公式是 \texttt{=IF(OR(B3≥50,C3≥50),"Pass","Fail")}，會顯示甚麼？
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
Medium
- APass
- BFail
- CTRUE
- DFALSE
11.
What value does \texttt{=MAX(C2:C5)-MIN(C2:C5)} give? \texttt{=MAX(C2:C5)-MIN(C2:C5)} 得出甚麼數值？
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
Medium
12.
The data is sorted by Test 1 in descending order. Who is in row 2? 數據按測驗一遞減排序後，誰在第 2 列？
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
- AAmy
- BChris
- CBen
- DDora
13.Which function counts the number of students in column C whose mark is below 40? 哪個函數可計算 C 欄中分數低於 40 的學生人數？
Medium
- A\texttt{=COUNT(C2:C31)}
- B\texttt{=SUMIF(C2:C31,"<40")}
- C\texttt{=COUNTIF(C2:C31,"<40")}
- D\texttt{=IF(C2<40,1,0)}
14.What does \texttt{=ROUND(3.14159,2)} return? \texttt{=ROUND(3.14159,2)} 傳回甚麼？
Easy
- A3
- B3.1
- C3.14
- D3.142
15.What does \texttt{=INT(7.8)} return? \texttt{=INT(7.8)} 傳回甚麼？
Easy
- A7
- B7.8
- C8
- D0.8
16.A worksheet named Prices has product codes in column A and prices in column C. Which formula finds the price of the code in cell A2 on another sheet? 名為 Prices 的工作表 A 欄是貨品編號、C 欄是價錢。以下哪條公式可在另一工作表找出 A2 編號的價錢？
Medium
- A\texttt{=SUM(Prices!C2:C50)}
- B\texttt{=VLOOKUP(Prices!A2:C50,A2,3,FALSE)}
- C\texttt{=VLOOKUP(A2,Prices!A2:C50,1,FALSE)}
- D\texttt{=VLOOKUP(A2,Prices!A2:C50,3,FALSE)}
17.A cell shows \texttt{\#DIV/0!}. What is the MOST likely cause? 某儲存格顯示 \texttt{\#DIV/0!}，最可能的原因是甚麼？
Medium
- AThe formula divides by zero or by an empty cell 公式除以零或空白儲存格
- BThe function name is misspelt 函數名稱拼錯
- CA referenced cell was deleted 參照的儲存格已被刪除
- DThe column is too narrow 欄寬太窄
18.Marks for Term 1 and Term 2 are on worksheets named Term1 and Term2. Which formula adds a student's totals from cell D2 on both sheets? 第一及第二學期分數分別在名為 Term1 和 Term2 的工作表。哪條公式可把兩個工作表 D2 的總分相加？
Medium
- A\texttt{=Term1:D2+Term2:D2}
- B\texttt{=Term1!D2+Term2!D2}
- C\texttt{=D2+D2}
- D\texttt{=SUM(Term1,Term2)}
19.A teacher wants a list sorted by Class (4A, 4B, 4C…) and, within each class, by Mark from highest to lowest. How should the sort be set up? 老師想把名單按班別（4A、4B、4C…）排序，並在每班內按分數由高至低排列。應如何設定排序？
Medium
- ASort by Mark descending only 只按分數遞減排序
- BFirst key Mark descending, second key Class ascending 第一排序鍵為分數遞減，第二排序鍵為班別遞增
- CFirst key Class ascending, second key Mark descending 第一排序鍵為班別遞增，第二排序鍵為分數遞減
- DFilter by Class and delete the other rows 按班別篩選並刪除其他列
20.When a filter is applied, the rows that do not meet the criteria are deleted from the worksheet. 套用篩選時，不符合條件的列會從工作表中刪除。
Easy
True or false?
21.A filter uses the criteria: Class is 4A AND Mark is greater than 80. Which students are shown? 篩選條件是：班別是 4A 並且分數大於 80。會顯示哪些學生？
Medium
- AAll students in 4A 4A 班所有學生
- BAll students with more than 80 marks 所有分數高於 80 的學生
- C4A students and all students with more than 80 marks 4A 學生及所有分數高於 80 的學生
- DOnly 4A students with more than 80 marks 只有分數高於 80 的 4A 學生
22.A sales table has 5 000 rows with columns Date, Region, Product and Amount. The manager wants the total Amount for each Region in each month. Which feature is MOST suitable? 一個銷售表有 5 000 列，欄位為日期、地區、產品及金額。經理想知道每個地區每月的總金額。哪個功能最合適？
Medium
- APivot table 樞紐分析表
- BGoal Seek 目標搜尋
- CFind and Replace 尋找及取代
- DFormat check 格式檢查
23.A pivot chart updates when the pivot table it is linked to is changed. 樞紐分析圖會在其連結的樞紐分析表改變時更新。
Easy
True or false?
24.Match each purpose with the MOST suitable chart. 把每個目的配對最合適的圖表。
Medium
- Temperature every day for a month 一個月內每天的氣溫
- Share of students choosing each elective 選修各科目的學生比例
- Number of books borrowed by each class 各班借書數量
- Hours of study against exam mark 溫習時數與考試分數
- Line chart 折線圖
- Pie chart 圓形圖
- Column chart 棒形圖
- Scatter chart 散佈圖
25.A student club wants to compare its profit under three different ticket prices and three different attendance numbers, then keep each set for the committee. Which tool is MOST suitable? 學生會想比較三種票價及三種出席人數下的利潤，並把每組數據保存給委員會。哪個工具最合適？
Medium
- ASort 排序
- BScenario Manager 分析藍本管理員
- CFilter 篩選
- DVLOOKUP
26.Which of the following BEST describes Goal Seek? 以下哪項最能描述目標搜尋？
Easy
- AIt hides rows that do not meet criteria 隱藏不符合條件的列
- BIt sorts data by several keys 按多個排序鍵排序數據
- CIt finds the input value needed to produce a target result 找出產生目標結果所需的輸入值
- DIt checks the spelling of function names 檢查函數名稱的拼寫
27.Which of the following can a spreadsheet use to make predictions? (1) A trendline on a line chart. (2) What-if analysis. (3) Filtering the data. 試算表可利用以下哪些方法作出預測？(1) 折線圖上的趨勢線。(2) 假設分析。(3) 篩選數據。
Medium
- A(1) only 只有 (1)
- B(1) and (2) only 只有 (1) 和 (2)
- C(2) and (3) only 只有 (2) 和 (3)
- D(1), (2) and (3) (1)、(2) 和 (3)
28.Which of the following are logical functions? (Select all that apply) 以下哪些是邏輯函數？（選出所有正確答案）
Easy
- A\texttt{AND}
- B\texttt{OR}
- C\texttt{SUM}
- D\texttt{NOT}
- E\texttt{ROUND}
29.Which of these formulas give TRUE when A1 contains 10? (Select all that apply) 當 A1 是 10 時，以下哪些公式得出 TRUE？（選出所有正確答案）
Medium
- A\texttt{=A1≥10}
- B\texttt{=A1<>10}
- C\texttt{=NOT(A1<5)}
- D\texttt{=AND(A1>5,A1<8)}
- E\texttt{=OR(A1=10,A1=20)}
30.In a class list, column C holds each student's house (Red, Blue, Green) and column D holds points. Which formula gives the total points of Red house? 班名單 C 欄是社別（Red、Blue、Green），D 欄是積分。哪條公式可得出 Red 社的總積分？
Medium
- A\texttt{=SUM(D2:D40)}
- B\texttt{=COUNTIF(C2:C40,"Red")}
- C\texttt{=SUMIF(D2:D40,"Red",C2:C40)}
- D\texttt{=SUMIF(C2:C40,"Red",D2:D40)}
31.Put the order of operations used by a spreadsheet in order, first to last. 把試算表的運算次序由先至後排列。
Easy
- Brackets 括號
- Powers \texttt{\textasciicircum{}} 乘冪
- Multiplication and division 乘和除
- Addition and subtraction 加和減
32.Name the type of reference. 說出參照的類型。
Easy
A reference like $E$1 that does not change when a formula is copied is called an ____ reference.
33.Name the spreadsheet function that returns the largest value in a range. 說出傳回範圍內最大值的試算表函數名稱。
Easy
34.A cell shows \texttt{\#NAME?} after the formula \texttt{=AVRAGE(B2:B9)} is entered. Why? 輸入公式 \texttt{=AVRAGE(B2:B9)} 後，儲存格顯示 \texttt{\#NAME?}。原因是甚麼？
Easy
- AThe function name is misspelt 函數名稱拼錯了
- BThe range is empty 範圍是空的
- CThe formula divides by zero 公式除以零
- DThe cells contain text 儲存格包含文字

रिवीज़न नोट्स

## 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