Spreadsheets: collecting and analysing data

邊玩邊學

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

給教育者：為 Spreadsheets: collecting and analysing data（KS3 Computing、Digital Literacy）準備好可直接使用的課程投影片, 複習筆記——用於你的課程，或把這個主題當成互動班級活動，讓學習者以即時遊戲的方式進行。

課程筆記

What is a Spreadsheet?

A spreadsheet is a computer application for computation, organization, analysis, and storage of data in tabular form.
It was developed as a computerized analog of paper accounting worksheets.
Data is entered into cells arranged in rows and columns.
Each cell can contain numeric data, text data, or the results of formulas.
A workbook is a file containing one or more worksheets (or sheets).
Worksheets are shown as tabs that let you switch between pages.

Cell References and Ranges

Columns are labelled with letters (A, B, C, …) and rows with numbers (1, 2, 3, …).
A cell is referred to by its column letter and row number, e.g., C10.
This system is called A1 notation.
A range is a group of contiguous cells, written like A1:A10 (cells A1 to A10).
In a multi-sheet workbook, cell references include the sheet name, e.g., Sheet1!C10.

Entering Data and Formulas

You can type data directly into a cell, such as text, numbers, or dates.
A formula starts with an equals sign (=), e.g., =5×3.
The cell displays the result of the formula, not the formula itself.
Formulas can refer to other cells, e.g., =5×C10 multiplies the value in C10 by 5.
Chaining formulas together gives spreadsheets their power.

Common Functions

Functions are pre-built formulas for common calculations.
SUM adds up all numbers in a range, e.g., =SUM(A1:A10).
AVERAGE calculates the mean of a range, e.g., =AVERAGE(B1:B10).
IF performs a conditional test, e.g., =IF(C10>5,"High","Low").
Functions can be combined with cell references and ranges.

Sorting and Filtering

Sorting arranges data in a specific order (e.g., alphabetical or numerical).
Filtering shows only rows that meet certain criteria, hiding the rest.
These tools help you analyse large sets of data quickly.
Sorting and filtering do not change the underlying data permanently unless you save.

Charts and Graphs

Spreadsheets can display data graphically as charts or graphs.
Common chart types include bar charts, line graphs, and pie charts.
Charts make it easier to spot trends and patterns in data.
You can usually create a chart by selecting data and choosing a chart type.

What-If Analysis

What-if analysis lets you change values and see how they affect results.
Because formulas update automatically, you can test many scenarios quickly.
For example, change a budget amount and see the new total instantly.
This is useful for planning and decision-making.

Spreadsheets vs. Databases

A spreadsheet is essentially one table of data.
A database is a collection of many tables with relationships between them.
Spreadsheets lack the relational structure of databases.
However, spreadsheets and databases are interoperable: you can import/export data between them.

History and Examples

LANPAR (1969) was the first electronic spreadsheet on mainframe computers.
VisiCalc (1979) was the first spreadsheet for microcomputers and helped popularise the Apple II.
Lotus 1-2-3 was the leading spreadsheet during the DOS era.
Microsoft Excel now has the largest market share.
Google Sheets is a popular web-based spreadsheet application.

投影片

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

免費註冊登入

練習題

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

1.What is a spreadsheet?
Easy
- AA computer application for computation, organization, analysis and storage of data in tabular form
- BA type of database used only for financial calculations
- CA word processing program for writing documents
- DA presentation tool for making slides
2.In a spreadsheet, columns are typically labelled with:
Easy
- Anumbers (1, 2, 3...)
- Bletters (A, B, C...)
- Csymbols (#, $, %...)
- Ddates (Jan, Feb, Mar...)
3.A single cell in a spreadsheet is referred to by its column letter and row number, for example C10.
Easy
True or false?
4.In a spreadsheet, a formula is usually created by preceding the text with which symbol?
Easy
- A=
- B+
- C-
- D#
5.A spreadsheet is essentially the same as a database.
Easy
True or false?
6.Match each spreadsheet term with its correct description.
Easy
- Cell
- Range
- Formula
- A group of contiguous cells
- A single intersection of a row and column
- An expression that calculates a value
7.In the cell reference 'Sheet1!C10', what does 'Sheet1' indicate?
Easy
- AThe workbook name
- BThe worksheet name
- CThe column name
- DThe row name
8.Changing a value in a spreadsheet can automatically update the results of formulas that depend on it.
Easy
True or false?

複習筆記

## Spreadsheets: Collecting and Analysing Data ### What is a Spreadsheet? - A **spreadsheet** is a computer application for computation, organization, analysis, and storage of data in tabular form. - It consists of a table of **cells** arranged into **rows** (numbered 1, 2, 3...) and **columns** (lettered A, B, C...). - Each cell can contain: - **Text** (e.g., "hello world") - **Numbers** (e.g., 5) - **Dates** (e.g., 10-Sep-97) - **Formulas** (e.g., =5*3) which calculate and display a value based on other cells. ### Cell References and Ranges - A cell is referred to by its column letter and row number, e.g., **C10**. - A **range** is a group of contiguous cells, e.g., **A1:A10** refers to the first ten cells in column A. - In multi-sheet workbooks, include the sheet name: **Sheet1!C10**. ### Formulas and Functions - Formulas start with an equals sign (`=`). Example: `=5*C10` multiplies the value in C10 by 5. - The power of spreadsheets comes from **chaining formulas** – a formula can refer to other cells, which may themselves contain formulas. - **Functions** are pre-built formulas for common tasks: - `SUM(range)` – adds up all numbers in a range. - `AVERAGE(range)` – calculates the mean of numbers in a range. - `IF(condition, value_if_true, value_if_false)` – returns different values based on a condition. ### Sorting and Filtering - **Sorting** arranges data in a specific order (e.g., alphabetical, numerical). - **Filtering** shows only rows that meet certain criteria, hiding others. ### Charts - Spreadsheets can display data graphically as **charts** (e.g., bar, line, pie) to make patterns easier to see. ### What-If Analysis - Because you can change any stored value and instantly see the effect on calculated values, spreadsheets are ideal for **what-if analysis** – exploring many scenarios without manual recalculation. ### Workbooks and Worksheets - A **workbook** is a file containing one or more **worksheets** (or sheets). - Worksheets are shown as tabs; each sheet contains its own grid of cells. ### Spreadsheets vs. Databases - A spreadsheet is essentially one table, while a database is a collection of many tables with relationships. - Spreadsheets and databases can interoperate: sheets can be imported into databases, and query results can be exported to spreadsheets. ### Key Points to Remember - Always start a formula with `=`. - Use cell references (e.g., `B2`) instead of typing numbers directly to make formulas dynamic. - Use functions to save time and reduce errors. - Sort and filter to organise data. - Use charts to visualise data. - Use what-if analysis to test different scenarios.

Spreadsheet: A computer application for computation, organization, analysis and storage of data in tabular form.
Cell: A single box in a spreadsheet where data or formulas can be entered, identified by its column and row (e.g., C10).
Range: A group of contiguous cells, e.g., A1:A10.
Formula: An expression that calculates a value, starting with an equals sign (=), e.g., =5*C10.
Function: A pre-built formula for common operations, e.g., SUM, AVERAGE, IF.
SUM: A function that adds up all numbers within a range.
AVERAGE: A function that calculates the mean of numbers in a range.
IF: A conditional function that returns one value if a condition is true and another if false.
Sorting: Arranging data in a specific order, such as alphabetical or numerical.
Filtering: Displaying only rows that meet certain criteria, hiding others.
Chart: A graphical representation of data, such as a bar chart or line graph.
What-if analysis: Changing stored values to observe effects on calculated values, allowing rapid investigation of many scenarios.
Workbook: A file containing one or more worksheets.
Worksheet: A single sheet within a workbook, containing a grid of cells.
Cell reference: The address of a cell, using column letter and row number, e.g., C10.
A1 notation: A system of cell references using column letters and row numbers, introduced in VisiCalc.

字卡

What is a spreadsheet?

A computer application for computation, organization, analysis and storage of data in tabular form.

What is a cell in a spreadsheet?

A cell is a single box in a spreadsheet table where data or formulas can be entered.

What is a cell reference?

A cell reference identifies a specific cell by its column letter and row number, e.g., C10.

What is a range in a spreadsheet?

A range is a group of cells, usually contiguous, referenced by the first and last cell, e.g., A1:A10.

How do you create a formula in a spreadsheet?

You start with an equals sign (=), then write the formula, e.g., =5×3.

What does the formula =5×C10 do?

It multiplies the value in cell C10 by 5 and displays the result.

What is the SUM function used for?

It adds up all the numbers within a range, e.g., =SUM(A1:A10).

What is the AVERAGE function used for?

It calculates the mean of numbers in a range, e.g., =AVERAGE(B1:B10).

What is the IF function used for?

It returns one value if a condition is true and another if false, e.g., =IF(C10>5,"High","Low").

What is sorting in a spreadsheet?

Sorting arranges data in a specific order, such as alphabetical or numerical, ascending or descending.

What is filtering in a spreadsheet?

Filtering displays only rows that meet certain criteria, hiding the rest.

What are charts in spreadsheets used for?

Charts display data graphically, making patterns and trends easier to see.

What is 'what-if' analysis in spreadsheets?

It involves changing values in cells to see how they affect calculated results, helping explore different scenarios.

What is a workbook in modern spreadsheet applications?

A workbook is a file that contains one or more worksheets (sheets), each with its own cells and data.

How are cells referenced in a multi-sheet workbook?

By adding the sheet name, e.g., Sheet1!C10.

What was the first electronic spreadsheet on a microcomputer?

VisiCalc, released in 1979, which helped make the Apple II popular.

Name a popular web-based spreadsheet application launched in 2006.

Google Sheets (initially launched as a β web application).

What is the difference between a spreadsheet and a database?

A spreadsheet is essentially one table, while a database is a collection of many tables with machine-readable relationships.

Can spreadsheets and databases work together?

Yes, they are interoperable: sheets can be imported into databases as tables, and database queries can be exported to spreadsheets.

Unlock all 61 questions, flashcards & more

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

免費註冊登入

歷屆試題

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

即將推出