VCE Applied Computing - U1O1 - Data analysis Hangman

The 82 key terms

1spreadsheet

A grid of columns (letters, running down) and rows (numbers, running across). A cell is where a column and row meet, like B3. Each worksheet holds one grid.

3Click a cell, type, press Enter

To store data, click a cell, type your value, then press Enter to save it. Tab saves it and moves right. Text lines up left and numbers line up right.

4Raw_Data

A short, clear worksheet tab name that describes what the sheet holds. Use one consistent style with no spaces, and keep raw data on a separate tab from analysis, such as Summary.

5header label

A label in the first row or column that names the data below or beside it. Use one per column, add units like Height_cm, and keep capitalisation consistent.

8fill handle

The small square at a cell's corner. Drag it down to copy a formula to the cells below. The cell references change row by row, so B2 becomes B3.

10=SUM(B2:B10)

A formula that adds up all the numbers in a range of cells. The colon means 'from B2 to B10'. The AutoSum button can type it for you.

11=AVERAGE(B2:B10)

A spreadsheet function that adds up the numbers in cells B2 to B10 and divides by how many there are, to find the mean (the typical value). It is not the total.

12=MIN(range) and =MAX(range)

MIN finds the smallest (lowest) value in a range of cells and MAX finds the largest (highest) value. For example, =MAX(B2:B10) shows the top score in cells B2 to B10.

13=COUNT(B2:B10)

A spreadsheet formula that counts how many cells in the range B2 to B10 contain numbers. It ignores cells with text or empty cells.

14=COUNTIF(range, criteria)

Counts how many cells in a range match a value or condition, such as "Yes" or ">10". Example: =COUNTIF(C2:C30,"Yes") counts every Yes in C2 to C30.

15=SUMIF(range, criteria, sum_range)

Adds only the numbers in sum_range whose matching cell in range meets the criteria, such as the total of all sales for one category.

16=IF(test, value_if_true, value_if_false)

Checks a test, like B2>=50. Shows the second value if the test is true and the third if it is false. Text results go in quotes, e.g. =IF(B2>=50,"Pass","Fail").

17XLOOKUP

A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.

18=MEDIAN(B2:B30)

Returns the middle value of the numbers in B2:B30 once ordered. With an even count it averages the middle two. You do not need to sort the cells first.

19mean vs median

An outlier drags the mean towards it but barely moves the median. If the two differ a lot, the data is skewed, so the median is usually the fairer typical value to report.

20=COUNTIF(range,A2)

Used in a frequency table: list each category in column A, then put this beside it to count how many times that category appears in the data range. Copy it down for every category.

21=COUNTIFS(B2:B30,">=10",B2:B30,"<20")

Counts the numbers in a range that are at least the lower limit and less than the upper limit, giving the frequency of one bin. Equal-width bins must not leave gaps or overlap.

22=STDEV.S(B2:B30)

Returns the sample standard deviation of the numbers in a range, showing how spread out they are. Use STDEV.P if the range is the whole population.

23standard deviation

A measure of how spread out values are around the mean. A small value means the data is clustered close to the mean. A large value means it is widely spread. It uses the same units as the data.

24pattern, trend, outlier

Look for patterns and trends in data, like highest, lowest, going up or down. An outlier is a value that stands out from the rest. Compare groups to see differences.

25Sort

Sorting puts the rows of a table in order using the values in one column, like A to Z or smallest to largest, and keeps each row together.

26filter

A filter shows only the rows that match a value you choose and hides the other rows. The hidden rows are not deleted. Clear the filter to see them all again.

27clean data

Finding and fixing mistakes in data, like typos, spelling variations, duplicate rows, missing values and impossible values, so the results are correct.

28=TRIM(A2)

A text function that returns a tidy copy of the text in A2 with extra spaces removed. UPPER, LOWER and PROPER work the same way to fix capitals, e.g. =PROPER(A2) gives Mia Chen.

29validation

An automatic check that data is reasonable and allowed when it is entered, such as being in range or the right type, so typos and impossible values are caught.

30Data > Data validation > Whole number between

A rule that only lets a cell accept a number between a minimum and maximum, like age 11 to 14. Numbers outside the range are rejected. Use Decimal for numbers with parts.

31List validation

A rule that lets a cell accept only items from a set list, shown as a drop-down. Items can be typed in or taken from a range of cells, which keeps spelling consistent.

32Error alert

A message that pops up when someone enters data that breaks a validation rule. It can Stop the entry, Warn about it, or give Information, and a good message explains how to fix it.

33expected result

The answer you work out by hand using a few rows of data. Compare it with the formula's actual result. If they match, the formula works. If not, check the formula and its range.

34Conditional formatting

A spreadsheet tool that changes a cell's colour or icon automatically when its value meets a rule, so highs, lows and outliers stand out.

35pivot table

A tool that summarises a big table by grouping rows under row and column fields, then working out a sum, count or average for each group. Refresh it when the data changes.

36relational database

A database that stores data in separate tables of fields (columns) and records (rows), with tables linked by shared values such as keys.

37database table

A database table stores data in columns called fields and rows called records. A single-table database keeps all its data in this one table.

38data type

The kind of value a database field accepts, such as text, number, date or yes/no. It stops the wrong kind of data being entered, like words in a number field.

39PRIMARY KEY

A field whose value is unique for every record in a table, such as a student ID number, so each record can be identified exactly. Names make poor keys because they can repeat.

40FOREIGN KEY

A field in one table that holds the primary key value of a record in another table, linking the two tables together so related data is stored once and not repeated.

41naming convention

A consistent rule for naming a database and its tables, such as a singular noun in lower_case with no spaces or special characters, e.g. student, applied in the same style for every table.

42student_id

A key column name in lowercase with an _id suffix. A primary key uses it in its own table, and a foreign key reuses the exact same name in a related table.

43table design

Planning a table by listing a clear field name and data type for each piece of data, with one fact per field, such as FirstName and LastName as two fields.

44CREATE TABLE

SQL command that builds a new table by naming its fields and their data types, such as a PRIMARY KEY. INSERT INTO ... VALUES then adds one record to that table.

45Import CSV

Fills a new database table from a CSV file. The first row becomes the field names, and you choose a data type for each field, such as text or number, and check them after the import.

46validation rule

A rule set on a database field that checks each entry, such as a number range, a maximum text length or a drop-down list, and rejects any record that breaks the rule.

47CHECK (age BETWEEN 0 AND 120)

A constraint is a rule on a column. This CHECK rejects any record with an age outside 0 to 120. NOT NULL (must have a value) and UNIQUE (no repeats) are constraints too.

48query

A saved question asked of a table. It shows only the fields and records that answer it, as a new view, and leaves the original table unchanged.

49SELECT name, age FROM students;

SELECT lists the fields to show and FROM names the table. This returns the name and age columns for every record in students. SELECT * shows all columns. End with a semicolon.

50WHERE

An SQL clause that keeps only the rows meeting a condition, such as WHERE age >= 15 or WHERE town = 'Perth'. Text goes in quotes, and AND or OR can combine conditions.

51AND / OR

AND needs both conditions to be true, like Year = 7 AND Score > 80. OR needs at least one to be true, like Pet = "Cat" OR Pet = "Dog". They let a query match several conditions at once.

52Like "S*"

A query criterion where the * wildcard stands for any characters, so it finds text that starts with S. Like "*s" ends with s and Like "*at*" contains at.

53ORDER BY

Sorts the rows a SELECT query returns by a chosen field. It sorts A to Z or lowest to highest by default (ASC), or the reverse if you add DESC after the field name.

54SELECT AVG(score) FROM results;

An aggregate function works out one value from many rows. COUNT, SUM, AVG, MIN and MAX give the count, total, mean, lowest and highest. This query returns the mean score.

55GROUP BY

An SQL clause that sorts rows into groups sharing the same field value, then applies an aggregate like AVG or COUNT to each group, giving one result row per group (e.g. average score per class).

56INNER JOIN t2 ON t1.key = t2.key

Combines rows from two tables where a foreign key matches a primary key, so related data shows in one result. Use table.field names to say which table each field belongs to.

57UPDATE table SET field = value WHERE condition;

UPDATE changes field values and DELETE FROM removes records. Always add WHERE so only the chosen records change. Without it, every record in the table is changed or deleted.

58Test a query

Predict the rows a query should return from a small sample, run it, then compare the actual result with your prediction. Check row counts, boundary values and JOIN matches.

59Choosing the right chart

Pick the chart that matches your question: a bar chart to compare categories, a line chart for change over time, and a pie chart for parts of a whole.

60bar chart

A chart that uses bars to compare amounts in different categories. Select the labels and values, then choose Insert chart. Column charts have upright bars; bar charts lie sideways.

61line chart

A chart that joins points with a line to show how a value changes over time. Time goes along the bottom axis, so you can see if the trend goes up or down.

62pie chart

A circle chart split into slices. Each slice shows how big one part is compared with the whole, often as a percentage. All the slices add up to 100%.

63scatter graph

A chart that plots each record as a point using two number columns, one on the x axis and one on the y axis, to show whether two things are related.

64histogram

A chart with touching columns showing how many values fall in each numeric bin. Bins go on the x axis, frequency on the y axis. It shows the shape of the data, unlike a bar chart of categories.

65Title, axis labels and legend

The parts that help a viewer read a chart: the title says the topic, axis labels name the data and units, the legend explains colours.

66Trendline

A straight or curved line fitted to the points on a chart. It shows the overall direction of the data, such as going up or down. Add it with Add Chart Element > Trendline.

67correlation is not causation

Two things can rise or fall together without one causing the other. A trend shows a relationship, not the reason, so a hidden third factor may explain both.

68misleading chart

A chart whose design, like a cut-off axis, 3D effects or a cherry-picked range, makes the data look different from what it truly shows.

69conclusion

A statement that answers a question using evidence from the data, such as numbers. It should only say what the data shows and stay within the data's limits.

70educating visualisation

A visualisation that helps an audience learn how something works or understand a topic step by step, such as a labelled diagram or a process explained in stages.

71Informing visualisation

A visualisation that shows facts and findings clearly and neutrally, with key numbers up front, so the audience can see what the data says and decide for themselves.

72entertaining visualisation

A visualisation that uses humour, bold design or surprising facts to hold attention so the audience enjoys it, for example through playful graphics and fun facts.

73persuading visualisation

A visualisation that selects and emphasises data to move an audience towards a viewpoint or action, using a call to action and design emphasis, while using data ethically.

74purpose and audience

The reason a visualisation is made and who will view it. Together they decide the content, level of detail, tone and style, so the same data can look different for different audiences.

75long-form infographic

A tall, single-page visualisation that tells one story from start to finish as you scroll down through headed sections of text, graphics and charts.

76poster series

A set of separate infographic posters that share one template and colours, each with one message, linked by a common theme.

77dashboard

A single screen that combines charts, tables and key figures so a viewer can monitor data at a glance, such as sales or attendance.

78slicer

A set of buttons linked to a table, pivot table or chart. Click a button to filter the data, so a viewer can explore it interactively. Clear the filter to show everything again.

79Text and graphics

Titles, headings, captions and key figures (text) plus shapes, pictures and icons (graphics) that frame and explain the data so viewers know what it shows and what matters.

80table

A visualisation component that shows exact values in rows and columns, used when the audience needs the precise numbers. Clear headers and only the rows that matter keep it readable.

81chart

A visualisation component that turns data into shapes such as bars, lines or slices so patterns can be seen quickly. Use one chart per message and place it near its explanation.

82colour palette

A small, consistent set of colours where each colour has one meaning. Use the same colour for the same category, and never rely on colour alone, so colour-blind viewers can still read it.

83contrast

How different the text colour is from the background colour. High contrast, like black text on white, with large enough text, makes words easy to read.

84font hierarchy

Using only two or three fonts, with size and weight showing importance from title to heading to body to caption, so a visualisation is easy to read.

85icon

A small simple picture that makes a topic quick to recognise. Keep icons relevant and in one style, use them only where they help the data, and credit or get permission for any you borrow.

86consistent design

Using the same colours, fonts, alignment and spacing on every part of a dashboard or poster series so it looks unified and is easy to read.