Spreadsheets: collecting and analysing data

Apprends en jouant

Réponds à ces questions pour gagner de l'énergie, puis pêche et explore. Sans compte.

Pour les enseignants : diapos de leçon, notes de révision prêts à l'emploi pour Spreadsheets: collecting and analysing data (KS3 Computing, Digital Literacy) — utilise-les en cours, ou lance le thème en activité de classe interactive que tes apprenants jouent en direct.

Notes de leçon

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.

Diapos

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

S'INSCRIRE — GRATUIT CONNEXION

Questions d'entraînement

Aperçu gratuit — 8 sur 61 questions. Inscris-toi pour toutes les voir.

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?

Plus de questions

9.Which of the following is a built-in function in spreadsheets that adds up all the numbers within a range?
Medium
- ASUM
- BAVERAGE
- CIF
- DCOUNT
10.Match each spreadsheet component with its correct description.
Easy
- Cell
- Range
- Worksheet
- Workbook
- A group of contiguous cells
- A single box in a table
- A single sheet within a file
- A file containing multiple sheets
11.Arrange the following spreadsheet software in the order they were introduced, from earliest to latest.
Medium
- Microsoft Excel
- VisiCalc
- LANPAR
- Google Sheets
12.Which of the following are true about formulas in spreadsheets? (Select all that apply)
Medium
- AFormulas always begin with an equals sign.
- BFormulas can refer to other cells.
- CFormulas can only contain numbers, not cell references.
- DFormulas can use built-in functions like SUM.
- EThe result of a formula is always displayed in the cell.
13.A spreadsheet can be used for 'what-if' analysis because changing a value can automatically update the results of formulas that depend on it.
Easy
True or false?
14.In the cell reference 'Sheet2!B5', what does 'Sheet2' indicate?
Medium
- AThe row number
- BThe column letter
- CThe worksheet name
- DThe workbook name
15.A spreadsheet and a database are essentially the same thing.
Medium
True or false?
16.Which of the following are examples of built-in functions that modern spreadsheet software can provide? (Select all that apply)
Hard
- ASUM
- BAVERAGE
- CIF
- DPRINT
- ESORT
17.A spreadsheet contains the following data: A1=10, B1=5, C1=2. What is the result of the formula =A1/B1×C1?
Medium
- A4
- B1
- C25
- D10
18.A spreadsheet contains sales data for each day of the week in cells B2:B8. Which of the following formulas would correctly calculate the total sales for the week? (Select all that apply)
Medium
- A=SUM(B2:B8)
- B=SUM(B2,B8)
- C=B2+B3+B4+B5+B6+B7+B8
- D=SUM(B2:B8)+0
19.In a spreadsheet, the formula =IF(A1>10, "Pass", "Fail") will display "Pass" if the value in cell A1 is greater than 10.
Easy
True or false?
20.A spreadsheet contains the following data: A1=5, B1=10, C1=15. What will be displayed in cell D1 if it contains the formula =AVERAGE(A1:C1)?
Medium
- A10
- B5
- C15
- D30
21.Match each spreadsheet function with its purpose.
Medium
- SUM
- AVERAGE
- IF
- Adds up a range of numbers
- Calculates the mean of a range
- Returns one value if a condition is true and another if false
22.Arrange the following steps in the correct order to use a spreadsheet to analyse a dataset.
Medium
- Enter the data into cells
- Select the data range
- Insert a chart to visualise the data
- Use a formula or function to calculate a summary statistic
23.A spreadsheet contains the following data: A1=5, B1=3, C1=2. What is the result of the formula =A1+B1×C1?
Medium
- A16
- B11
- C13
- D10
24.Which of the following are true about using spreadsheets for data analysis? (Select all that apply)
Hard
- AFormulas can refer to other cells, so changing a value updates dependent results automatically.
- BSorting data in a spreadsheet permanently deletes the original order.
- CA spreadsheet can display data in graphical form, such as charts, to help identify patterns.
- DFiltering data hides rows that do not meet the criteria, but the data is not deleted.
- ESpreadsheets can only handle numeric data, not text.
25.In a spreadsheet, the formula =IF(A1>10, "Pass", "Fail") will display "Pass" if the value in cell A1 is greater than 10, otherwise it will display "Fail".
Easy
True or false?
26.A student wants to find out if there is a relationship between the number of hours spent revising and test scores. They collect data from 20 students. Which spreadsheet feature is most useful to quickly see if a pattern exists?
Hard
- ASorting the data by revision hours
- BUsing the IF function to categorise scores
- CCreating a scatter chart with revision hours on one axis and test scores on the other
- DUsing the SUM function to total the scores
27.In a spreadsheet, what are the vertical divisions called?
Easy
- ARows
- BColumns
- CCells
- DTabs
28.A spreadsheet is a computer application for computation, organization, analysis and storage of data in tabular form.
Easy
True or false?
29.In the cell reference 'Sheet1!C10', what does 'C10' refer to?
Easy
- AThe sheet name
- BThe column and row of the cell
- CThe workbook name
- DThe range of cells
30.In a spreadsheet, a formula is normally created by preceding the text with an equals sign (=).
Easy
True or false?
31.Which of the following is a built-in function that adds up all the numbers within a range?
Easy
- AAVERAGE
- BSUM
- CCOUNT
- DIF
32.Which of the following are examples of data types that can be entered into a spreadsheet cell? (Select all that apply)
Easy
- AText
- BNumbers
- CFormulas
- DImages
- ECharts
33.Which of the following was the first electronic spreadsheet on a microcomputer?
Medium
- ALANPAR
- BVisiCalc
- CLotus 1-2-3
- DMicrosoft Excel
34.The ability for a formula to refer to the contents of other cells gives spreadsheets their power.
Medium
True or false?
35.Which of the following is the correct formula to calculate the average of the values in cells B2 to B10?
Medium
- A=AVERAGE(B2:B10)
- B=AVG(B2:B10)
- C=MEAN(B2:B10)
- D=AVERAGE(B2,B10)
36.Which of the following are true about sorting and filtering data in a spreadsheet? (Select all that apply)
Hard
- ASorting rearranges rows based on a column's values
- BFiltering hides rows that do not meet criteria
- CSorting can be done on both text and numbers
- DFiltering removes data permanently
- ESorting changes the underlying data
37.Which of the following is the first electronic spreadsheet on a microcomputer?
Easy
- ALANPAR
- BVisiCalc
- CLotus 1-2-3
- DMicrosoft Excel
38.Which of the following are valid ways to refer to a group of cells in a spreadsheet? (Select all that apply)
Medium
- AA1:A10
- BA1, B2, C3
- CB1:B5
- DC10
- EA1..A5
39.In a spreadsheet, the formula =5×C10 will display the result of multiplying 5 by the value in cell C10.
Easy
True or false?
40.Which of the following is the correct way to reference a cell that is on a different worksheet within the same workbook?
Medium
- ASheet1!C10
- BSheet1.C10
- CSheet1:C10
- DC10!Sheet1
41.What is the term for a group of contiguous cells in a spreadsheet, such as A1:A10?
Easy
42.Fill in the blanks with the correct terms.
Easy
In a spreadsheet, the intersection of a row and a column is called a ____. A group of contiguous cells is called a ____. The first electronic spreadsheet on a microcomputer was ____.
43.If cell A1 contains the number 5 and cell B1 contains the number 3, what is the result of the formula =A1×B1?
Medium
44.What is the keyboard symbol used to start a formula in a spreadsheet?
Medium
45.If cell A1 contains 10 and cell B1 contains 4, what is the result of the formula =A1/B1?
Medium
46.What is the range reference for all cells in the first column from row 1 to row 10?
Medium
47.Fill in the blank with the correct term.
Medium
A formula such as =5×C10 produces the result of multiplying the value in cell C10 by the number 5. This is an example of a formula that ____ to the contents of other cells.
48.A student records the heights (in cm) of 5 plants: 12, 15, 10, 18, 20. What is the mean height?
Medium
49.Complete the sentence: The formula =SUM(A1:A5) adds up the values in the range from ___ to ___.
Medium
The formula =SUM(A1:A5) adds up the values in the range from ____ to ____.
50.In a spreadsheet, cell A1 contains 5, B1 contains 10, and C1 contains 2. What is the result of the formula =A1+B1×C1?
Easy
51.A spreadsheet contains the values 20, 30, 40, and 50 in cells A1 to A4. What is the result of the formula =AVERAGE(A1:A4)?
Medium
52.A spreadsheet contains the values 10, 20, 30, and 40 in cells A1 to A4. What is the result of the formula =SUM(A1:A4)/COUNT(A1:A4)?
Medium
53.Explain one advantage of using a spreadsheet rather than a paper-based table to analyse data.
Hard
54.Describe how a spreadsheet can be used to model a 'what-if' scenario, and give an example.
Hard
55.Explain how a spreadsheet can be used to model a 'what-if' scenario, and give an example.
Hard
56.If cell A1 contains the number 5 and cell B1 contains the number 3, what is the result of the formula =A1×B1?
Medium
- A15
- B17
- C16
- D14
57.If cell A1 contains 10 and cell B1 contains 4, what is the result of the formula =A1/B1?
Medium
- A2.5
- B4.5
- C3.5
- D1.5
58.A student records the heights (in cm) of 5 plants: 12, 15, 10, 18, 20. What is the mean height?
Medium
- A15 cm
- B14 cm
- C16 cm
- D17 cm
59.In a spreadsheet, cell A1 contains 5, B1 contains 10, and C1 contains 2. What is the result of the formula =A1+B1×C1?
Easy
- A25
- B22
- C28
- D31
60.A spreadsheet contains the values 20, 30, 40, and 50 in cells A1 to A4. What is the result of the formula =AVERAGE(A1:A4)?
Medium
- A43
- B35
- C39
- D31
61.A spreadsheet contains the values 10, 20, 30, and 40 in cells A1 to A4. What is the result of the formula =SUM(A1:A4)/COUNT(A1:A4)?
Medium
- A31
- B25
- C28
- D22

Notes de révision

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

Cartes mémo

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

Crée un compte gratuit pour voir toutes les questions, les diapos, les cartes mémo et les notes de révision de ce thème.

S'INSCRIRE — GRATUIT CONNEXION

Annales

Les annales d'entraînement pour ce thème arrivent bientôt.

Bientôt disponible