Task 6. Data processing and presentation in the form of histograms

Practical work

Microsoft Office Excel 2007. Graphing and charting

Theoretical part

With Microsoft EXCEL, you can create complex charts for worksheet data. EXCEL presents 9 types of flat charts and 6 three-dimensional chart types. A chart can be on a worksheet along with the source data, or on a separate chart sheet that is part of a workbook. A chart that is on a worksheet is called an embedded chart. Before we start plotting the diagram, let’s look at two important definitions.

A data series is a set of values to be displayed on a chart. In an assignment, for example , these are test scores.

Categories define the position of specific values in a data series. For example , in the task, these are the names of the students being tested.

So, a data series is a set of values that is plotted on a chart, and categories are, as it were, “titles” for them.

Practical part

Task 1. Building a histogram

Build an embedded histogram according to the table “Test results”, (Fig. 1), File Test results.xlsx

Rice. one

To build a histogram based on the data in the “Test Results” table, do the following:

1. Select the range containing the source data (in this case, B1:H11 ) and click the button, select the Insert menu on the Diagram panel Histogram and select an arbitrary type of histogram.

2. Increase the size of the resulting histogram

3. Transfer the histogram to another sheet of the book and rename it “Histogram”

Task 2. Create a diagram

Create a pie chart for test averages on a separate sheet

1. Highlight the data ranges containing averages.

2. Click Insert and in the Charts panel select Pie

3. Transfer the resulting diagram to the free space of the window

4. Click on it with the right mouse button and select Select data , then change the labels of the horizontal axis to the names of students – range B2-B11 (press Shift to select ranges)

5. Click OK.

6. Transfer the chart to another sheet of the book (not the one where the histogram is) and rename it to “Chart”

Task 3: Create a donut chart

1. Create your own donut chart ( Other charts ) based on test results for one student from the group. Customize it to your liking

2. Transfer the chart to another sheet of the book and rename it “Doughnut”

Task 4. Building a graph

Build a graph that reflects the dynamics of the test results of the first three students in the group

1. Select an area for plotting a chart without capturing the average test scores. (In our case, this is the range B1:G4 ).

2. Click Insert and in the Charts panel select Graph

3. Transfer the graph to another sheet of the book and rename it to “Chart”

Task 5. Volumetric version of the graph

1. Build a graph on your own that reflects the test results of the first three students from the group, using the view of the Volumetric version of the graph

2. Transfer the graph to another sheet of the book and rename it to “Volume Graph”

Show your work to the teacher

Task 6. Data processing and presentation in the form of histograms

Create a new sheet, name Graphs , create a list with the names of the students in the group in one column and the current grades in the other. Name the first rows of the columns, respectively, Last Name First Name and Grade.

Use the sorting function (on the Ribbon tab Home / Editing / Sort and filter) so that you get a list of last names, names of all students and their grades, sorted in descending order (from top to bottom: first students with a grade of 5, then with a grade of 4, then 3, then 2).

In the column of marks under the names, indent one empty line and in the following lines write accordingly: Quantity, 5, 4, 3, 2 (all inscriptions in one column under each other). Count the number of appropriate ratings and put them in the second column (for example, 5-7, 4-9, etc.).

Based on the data obtained, build a histogram: along the x-axis – grades (and sign the x-axis – Grades ), along the y-axis – Number of students (sign the axis accordingly)

Add a title for the chart – Student Grades . To change the names of the axes and charts, with the chart selected, go to the Layout tab on the Ribbon and there Captions / Chart Title and Axis Titles.

Add a polynomial trendline to the histogram: Right button on one of the histogram rectangles/Add trendline/Polynomial/Close. Alternate implementation: Layout/Analysis/Trendline/Advanced Trendline Options/Polynomial/Close.

Show the completed task to the teacher and get a grade.

Be First to Comment

Leave a Reply

Your email address will not be published.