A detailed account of databases in Excel can be found here.
This exercise involves the use of a questionnaire to investigate the behaviour and interests of members of the class. The data collected will be used as the basis of analysis of the topic under consideration.
Possible topics for questionnaires include:
Holidays
Food and Drink
TV Programmes
Hobbies
Personal Details
Cars
Careers
Music
Books
The sort of questions you might ask include:
Name (first name only so individuals cannot be identified outside school)
Age
Quantity (e.g. number of holidays/year, number of cars in household, number of meals per day)
Current or Last or Favourite item (e.g. car, holiday destination, type of music)
Detail of item (e.g. age of car, holiday destination, artist)
Detail of item (e.g. style of car, length of holiday)
Detail of item (e.g. engine size, who went on holiday)
Detail of item (e.g. number of seats, type of accommodation)
Detail of item (e.g. number in holiday group)
Score features 1-5 e.g. food, facilities, enjoyment, comfort, economy)
Ideal holiday/car/book/career
Devise a questionnaire directly in Excel so that members of the class can fill in the answers. Keep the column headings as brief as possible. Provide a list of possible answers where appropriate, for example, style of car. You should then move around the class and complete everyone else's questionnaire.
When your questionnaire returns are complete you should analyse the results and write a report on your findings. The techniques you can use include charts, database and pivot table. These are described below.
You will probably not be able to create charts directly from the data in your spreadsheet, instead you will have to add up the number of occurrences of each item and use these totals as the basis of your charts. You will find it easier to make charts from pivot table data so leave this part of the analysis until later.
To convert the questionnaire data into a database choose Data/Filter/Autofilter.
You can now use the drop down lists on each column heading to display only the data value you choose. For example, to display only holidays taken in Greece click on the arrow next to Destination and choose Greece from the list. To return the selection to all destinations click again on the arrow next to Destination and choose 'All'.
With a destination chosen such as Greece selected you can also select another field on which to filter the remaining data, for example Duration might be set to one week in order to show all holidays taken in Greece of one week's duration. This is known as an AND operation as both filters are applied to the data. Further filters can be applied to the data but the number of rows of data remaining may be too small to tell you much. Advice: apply one or two filters at most to your data.
To show all rows of data after applying a filter choose Data/Filter/Show All.
This is a more advanced tool that can be used quite effectively with some questionnaire data. Further details here.
This shows how to use Access to create a better interface for your questionnaire. Details here.
You should write a report on the findings of your questionnaire. This should use the headings of the PLUS model:
Purpose: framing questions (see below). What did you want to find out?
Location: finding raw data and information (in this case from your questionnaire)
Uses: using the information to produce your conclusions
Self-Evaluation: what have you learned? Could you repeat the exercise without guidance and supervision?
Section 3 might include answers to questions like:
(These questions are relevant for the holidays questionnaire, for others you would have to devise alternatives.)
Your report might look something like this:
Purpose: The aim of my report is to investigate what people think... (insert subject here).
Location (Data): To investigate this topic I devised a questionnaire and surveyed 20 people in my class/year group/school/etc. The questions and results are shown on the attached sheet.
Uses (Findings - find at least 10): My questionnaire shows the following (a list of numbered points showing the findings from each question)
Out of 20 people surveyed 12 have/prefer/went to xxx and 6 have/prefer/went to yyy.
Of the 12 people who have/prefer/went to xxx 5 like/stayed for zzz.
Out of 20 people surveyed 6 prefer aaa
etc.
Self-Evaluation (Conclusions): The questionnaire was useful in finding out people's behaviour/opinions on (subject). This is a useful way to find out useful information and I could use it in future. The database and pivot table techniques were very useful for showing the patterns in the responses.
Click Back to return to the previous page