Spreadsheets (AS)：練習問題

When working with a very large data set in a spreadsheet, which feature should be used to ensure that the top row containing column headings remains visible on the screen even when the user scrolls down to the bottom of the worksheet?

A spreadsheet contains product codes in Column A with the format 'CAT-10255-XP'. A manager needs to extract the 5-digit numeric part (10255) into a separate cell. Which of the following formulas correctly achieves this?

If cell A1 contains the date 2024-03-15 and the current date is 2024-09-01, which formula will correctly calculate the number of complete calendar months that have passed since the date in A1?

Which spreadsheet function should be used to determine the number of cells in a range that contain any type of data, including text, numbers, or error messages, but excluding empty cells?

A user is creating a formula to calculate a discount. If the quantity (Cell A2) is greater than 100 AND the customer type (Cell B2) is "Wholesale", a \( 20\% \) discount is applied; otherwise, a \( 5\% \) discount is applied. Which formula is correct?

State the numerical result of the formula \(=INT(-4.7)\) and explain why this specific value is returned by the software.

A date is stored in cell A1. Write a formula using the WEEKDAY function that would return a value of 1 if the date is a Sunday, assuming the default return type is used.

Explain the benefit of using a calculated field in a Pivot Table rather than adding a new formula column to the source data range.

A school administrator is setting up a spreadsheet to track student house points. The administrator needs to ensure the sheet is easy to read and formatted correctly for a weekly printed report.

(a) State the purpose of the 'fit to page' and 'margins' settings when preparing a spreadsheet for printing.

(b) Describe how conditional formatting could be used to highlight students who have earned more than 50 points in a single week.

(c) Explain why the administrator would 'lock' certain cells containing formulas while leaving the 'points earned' column 'unlocked' before protecting the worksheet.

A large logistics firm, GlobalFreight, is designing a spreadsheet system for tracking delivery dates. The data entry operator must input the following fields into a worksheet: Order ID (must be exactly 6 alphanumeric characters), Shipping Zone (must be 'North', 'South', 'East', or 'West'), and Delivery Date (must be a date within the current year and cannot be blank).

(a) State and describe two distinct validation techniques you would use on the Shipping Zone column to ensure data integrity and prevent errors.

(b) Describe how you would implement a validation rule (specifying the type of check) on the Delivery Date column to meet the requirement that the date must be within the current year.

(c) Explain the steps required to protect the cell containing the formula that calculates the Days Overdue based on the Delivery Date, ensuring that this cell cannot be accidentally modified by the user, while still allowing data input in the other columns.

(d) Write a formula using an appropriate counting function that would be used in a summary cell to count how many Delivery Date fields are left empty, providing management with a measure of data incompleteness.