Theme:Business graphics inExcel.
The purpose of the lesson: to acquaint with the possibility of practical application of information technologies for the visual display of statistical data.
Lesson Objectives:
1. Educational:
ü expand and clarify knowledge about spreadsheets Excel;
ü get an idea of the business schedule; introduce the concept of a diagram, get an idea of the main types of diagrams;
ü teach to use the Chart Wizard when building graphs, histograms, diagrams; get acquainted with the main stages of charting;
ü teach how to use the knowledge gained in a familiar situation (building a diagram using a ready-made algorithm); to form the ability to apply the acquired knowledge in an unfamiliar situation (solving problems without describing the sequence of actions).
2. Educational:
ü listen to the opinion of your classmates (do not interrupt);
ü to identify their own level of knowledge (by choosing a suitable task, the presence of differentiable homework);
ü Ability to independently group material according to the indicated characteristics (building correct tables in the proposed tasks);
ü contribute to the formation of a materialistic worldview, emphasizing the connection of informatics with economics, geography, biology and other sciences.
3. Developing:
ü develop a monologue speech of students when expressing their own judgments about the possibilities of using business graphics in various fields of human activity;
ü development of students' creative abilities (building interesting species diagrams for your own task);
ü develop the ability to compare and build analogies between different types of diagrams;
ü Ability to highlight the main thing from the information received (oral assignment when summing up); development of students' abilities to apply the acquired knowledge in further practical activities.
4. Health-preserving aspect:
ü Compliance of the cabinet equipment with hygienic requirements for video display terminals, personal electronic computers;
ü airing the office before and after classes;
ü continuous duration of work of 11th grade students on a PC does not exceed 30 minutes; at the end of work at the computer, a physical education minute, a set of exercises for the eyes is carried out.
Lesson type: combined
Equipment: computers, software - Microsoft Excel spreadsheet editor, didactic material: posters depicting the main types of diagrams, a table with the condition of the problem, posters with step-by-step building diagrams in the Diagram Wizard, handouts - algorithms for solving some problems, a list of tasks for independent solution, a crossword puzzle , a set of exercises for the eyes.
During the classes:
I.Org. moment - 2 minutes.
Checking those present at the lesson, checking the readiness of students for the lesson. Announcement of lesson goals to students.
II.Repetition - 3 min.
Students are encouraged to find an error in the statements:
1. MS Word- a program for creating and editing spreadsheets.
2. Text data is aligned by default right edge.
5. The formula always starts with a sign "a plus".
III.Explanation of the new material - 10 min.
They are creation of minds,
Everywhere will find application:
Draw, translate,
Read, output,
Graphs draw, create diagrams
Universal Applied ..... …… (Programs)
Today we will try new form explanations of the material - lecture. Only you do not have to write down notes, because the basic concepts and algorithms are on your tables.
When solving planning and management problems, the form of displaying the results is very important. The clearer this display is, the easier it is for humans to perceive the results. Pictures, graphs and diagrams are the most visual form of information presentation. This gave rise to a whole direction in computer science called computer graphics.
Computer graphics are divided into several types: illustrative, business, engineering and scientific.
1. In the computer science room, expensive, complex equipment is installed. Therefore, handle this technique with care, calmly, without hurrying, without pushing, take the place allotted to you.
2. It is strictly forbidden to touch the connectors of the connecting cables; turn on and off the equipment without the permission of the teacher; put books, CDs, notebooks on the keyboard and monitor.
3. Before starting work, check that there is no visible damage to the workplace.
4. Make sure that the equipment is in good working order and stop working immediately if an unusual sound appears or the equipment turns off spontaneously. Report this to your teacher immediately.
Students receive two practice assignments.
1. Construct a multiple diagram using the existing algorithm.
2. Multilevel practical task.
Students seeking a grade of "3" build diagrams from the descriptions they have. It is necessary to build the three remaining graphs.
Those who wish to receive the mark "4", solve the problems from those proposed for independent solution (three problems must be solved).
To get the mark "5" you need to come up with and solve your problem, or solve all 5 problems for an independent solution.
V.Physical education - 1 min.
At the end of the work at the computer, students do a set of eye exercises and a set of exercises to relieve fatigue.
Vi.Summing up - 8 min .
The correct answers are posted on the board. Students independently compare their graphs with the correct answers and evaluate their work.
Oral assignment. As a summary of the lesson, you need to name the most important thing that you learned and did today. The teacher summarizes and draws conclusions.
Briefly about the main thing
Business graphics are a visualization tool, that is, representations in a visual form, arrays of numerical data.
The data for the chart is selected from the selected table block. You can select data by columns and rows.
Column charts are called histograms and show a discrete distribution of values. Both single and multiple histograms can be plotted.
The graph is considered as a kind of diagram. The graph reflects the continuous process of changing the value.
A pie chart is used to represent the contribution of several quantities to a whole.
Vii.Homework - 1 min.
The first item of homework is completed by all students. Task 2 is performed for a good grade. The last task is for those wishing to get an additional grade.
1. Write down in a notebook the basic definitions, the way of constructing diagrams, the main steps of the Diagram Wizard. According to I. Semakin's textbook, paragraphs 2.4, 2.5
2. Solve the spreadsheet crossword suggested by the teacher.
3. Come up with your own crossword puzzle and design it.
Additional task:
Find the error:
6. MS Word is a program for creating and editing spreadsheets.
7. Text data is right-aligned by default.
10. A formula always starts with a plus sign.
TP Excel contains an advanced business graphics tool - diagram wizard... Let's get acquainted with its capabilities in more detail than it was described above.
Chart - this means for graphic display of quantitative information, intended for comparing several values or several values of one value, tracking changes in their values, and the like.
Most charts are plotted in a rectangular coordinate system. Horizontal axis NS the values of the independent variable (argument) are plotted, and the vertical axis Y- the value of the dependent variable (function). Several diagrams can be displayed simultaneously on one figure.
In graphic processing numerical information using TP Excel follows:
1) indicate the data area (block of cells), according to which the diagram will be built;
2) determine the sequence of data selection (by rows or by columns) from the selected block of cells.
When selecting by columns NS-coordinates are taken from the leftmost column of the selected block of cells. The rest of the columns contain Y-coordinates of diagrams. The number of charts being built is determined by the number of columns. When choosing by row, the most top line the selected block of cells is a string NS-coordinates, the rest of the lines contain Y-coordinates of diagrams.
We will consider the types of charts available in the TP Excel and the methods of their construction using a specific example.
Example. Three stores selling complicated household appliances during the week sold the number of pieces of equipment shown in table 2.8.
Table 2.8
| A | B | C | D | E | F | G | H | |
| Mon | W | Wed | NS | Fri | Sat | Sun | ||
| Shop 1 | ||||||||
| Shop 2 | ||||||||
| Shop 3 |
bar graph(bar chart) is used to reflect the discrete change in one or more quantities (Y) at different points (at different X). In fig. 2.24 is a histogram showing the trading results of store # 1 for each day of the week.
Rice. 2.24. Bar chart showing the weekly sales of store # 1
The chart in Figure 2.25 is a multiple histogram. It makes it possible to visually compare three quantities: the results of the trade of three stores.
Rice. 2.25. Bar chart showing the comparative performance of three stores
Figure 2.26 shows a tiered chart. Another name is stacked histogram. Here, each column is the sum of three values. The chart gives an idea of how each value contributes to the total.
Rice. 2.26. Stacked bar chart showing the comparative performance of three stores
Figure 2.27 graphically displays the same information as shown in Figure 2.24 as a histogram. Similarly, Figure 2.28 displays the same information as Figure 2.25-2.26.
Pie chart serves to compare several values at one point. The use of a pie chart is especially visual if the quantities add up to something whole (100%).
Rice. 2.29. Pie chart showing the contribution of different stores to total sales
We will briefly describe the technique of working with wizard of diagrams. It is called by executing the command Insert /Diagram .
Consider the algorithms for obtaining the diagrams presented in the previous paragraph, built on the basis of the data from Table 2.8.
Histograms(bar charts). The procedure for obtaining the diagram shown in Figure 2.24 is as follows:
1. Select in the table a block of cells B1: H2
2. Insert / Chart
3. On the tab Standard choose the type bar graph, view - Regular Histogram / Next
4. We indicate the range of data selection - B1: H2 and the order of selection - in rows; (automatically installed) / Further
5. Let's design the diagram. On a bookmark Headings the title of the diagram, labels for the axes are indicated; on the bookmark Legend indicate the location of the legend (in this example, you can not use it) / Further
6. Place the diagram; there are two options for this: place on the same sheet as the table, or on separate sheet(choose the same sheet)
7. Ready
The construction of the multiple diagram shown in Figure 2.25 differs from the first in the following way: block A1: H4 is indicated as the data area.
The legend (frame with names) is filled according to the contents of the first column. It notes the ways of shading the columns related to each of the stores (marking with color or in other ways is possible).
When plotting the tiered plot shown in Figure 6.6, block A1: H4 was specified as the data area. At the 1st step, the type “Histogram” was indicated, the type was “Histogram with accumulation”.
Schedule. In the plotting algorithm (Fig. 2.27, 2.28) at the 1st step, the type "Graph" is indicated, the view "Graph with markers marking data points".
Pie chart. When building the diagram in Fig. 2.29 at the 1st step, the type "Pie chart" is indicated. As a data block, it was indicated: A2: B4.
TP Excel contains a number of other types and types of charts, which should be familiar with experimentally.
Note that any of the chart objects has its own properties, such as color, font, etc. If it becomes necessary to change these properties, you need to:
1) right-click on the object;
2) select the item from the context menu Format;
3) change the properties of the object and click ok.
In addition to illustrating business texts shown in the previous example, graphics capabilities Excel is also used for solving mathematical problems. In the following example, we will show how with using Excel plot the functions sin ( x) and sin ( x+a).
The task can be divided into two tasks: 1) build a table of function values; 2) build graphs of functions.
Algorithm for creating tables and graphs
1. In cells B1, C1, A3, B3, C3 enter headings. Make them bold and center.
2. Enter the step value in cell B2 - p / 6. To do this: making the cell current, click on the button Function wizard... Select from the list of categories Mathematical and further from the list of functions - PI click on ok... After inserting the function, you need to edit the contents of cell B2. To do this: making the cell current, click in the formula bar and add the sign «-» before the function and "/ 6" after function PI ().
3. The value of the parameter a is PI () / 2... It can be entered without resorting to the function wizard. Similarly, in cell A4, enter the initial value of the argument - PI ().
4. In cell A5, enter the formula for calculating the next value of the argument - = A4 + $ B $ 2. Box A4 referenced relative, since when copying a formula down a column it is necessary that the row number change - each time a step must be added to the previous value of the argument. Cell B2 is referenced absolute, since the argument changes to a constant number stored in cell B2, and when copying, the reference to B2 should not change.
5. Copy the formula from cell A5 to the range A6: A16. To do this, after making cell A5 current, click on the button Copy to clipboard... Then select the range A6: A16 and click on the button Paste from clipboard.
6. Let's calculate the value of the function from the first value of the argument. To do this, put the cursor in cell B4 and click on the button Function wizard... Select sin from the list of functions and click on ok. Next, you need to enter a number that is an argument to the function. The dialog box can be moved away and clicked on cell A4 (Excel will automatically substitute the address in the formula bar) and click on ok.
7. The finished formula from B4 must be copied into cells B5: B16. The result is a table of function arguments and values y= sin ( x).
8. Introduce in C4 the formula = SIN (A4 + $ C $ 2)... Its difference from the formula contained in B4 consists in adding to the argument a constant number located in cell C2. When copying the formula further, the argument must change, but the number a must not, so an absolute reference is made to C2.
9. Multiply the formula in C4 by the range C5: C16 (we'll use autocomplete). The first task is completed.
| A | V | WITH | |
| Step | a | ||
| 0,523598776 | 1,570796327 | ||
| Argument | f-i sin (x) | f-i sin (x + a) | |
| -3,14159 | -1.22515E-16 | -1 | |
| -2,61799 | -0,5 | -0,866025404 | |
| -2,0944 | -0,866025404 | -0,5 | |
| -1,5708 | -1 | -4.44089E-16 | |
| -1,0472 | -0,866025404 | 0,5 | |
| -0,5236 | -0,5 | 0,866025404 | |
| -6.7E-16 | -6.66134E-16 | ||
| 0,523599 | 0,5 | 0,866025404 | |
| 1,047198 | 0,866025404 | 0,5 | |
| 1,570796 | 1.01069E-15 | ||
| 2,094395 | 0,866025404 | -0,5 | |
| 2,617994 | 0,5 | -0,866025404 | |
| 3,141593 | 1.45478E-15 | -1 |
10. Let's build graphs of functions. Select the range A3: C16, click on the button Chart Wizard and then select the chart type - schedule.
Rice. 2.30. The graph obtained as a result of the actions described in the text
Finally of this section note the following. In addition to the fact that there is a large built-in library for building graphical images: graphs, charts, bar charts, Excel contains a powerful built-in graphic editor. Also, much attention in Excel is paid to the design of the book, its sheets. For these purposes, they use the format of texts and numbers, colors and fills, style, fonts, etc. In fact, a graphics editor is interfaced with a word processor, similar in capabilities to publishing systems such as Word. Excel not only "friendly" with text and graphic systems, but also supports the basic actions typical of database management systems (DBMS). In this sense, modern spreadsheets (Excel, QuattroPro, Lotus) are integrated software systems... Moreover, they have a developed apparatus for importing and exporting data from other software systems.
These and many other pleasant service opportunities, sometimes unexpected, can be discovered when working with Excel.
Control questions and tasks
1.What types of data do spreadsheets work with?
2. Create ET "Scholarship Sheet".
3. Make an estimate of expenses for organizing a trip, if the total cost is known. Build different types of charts and prepare a report.
4. Make a computer model of the solar system in spreadsheets. Assuming that the planets move around the Sun in circles at a constant speed, determine the speed of movement in orbit for each planet. For example, for Mars V = 2 · 3.14 · 0.387 / 0.24 = 10.1 (km / h). Estimate the extent to which the distance from Earth to Mars may vary. At what arrangement of the planets the lowest and highest values are achieved.
5. Let the interval of movement of the bus be 10 minutes. The average waiting time for a bus can be estimated by N experiments by playing out a random number in the interval. Find the average for a series of 10, 50, 100 experiments. For two routes, find the average waiting time when the first bus is 10 minutes apart and the second 60 minutes apart. Build a computational table for three routes.
6. Plot function graphs
7. Complete table 2.9 and do the following:
Table 2.9
| Name | Firm | Opt. price | Retail price | Date of sale |
| Television | Sony | 2,000 RUB | 2 500 RUB | 12 Aug |
| Television | Samsung | 1 700 RUB | 2 100 p. | 17 Aug |
| Television | Panasonic | 2 200 p. | 2 650 RUB | 12 sep |
| Television | Panasonic | 2 600 RUB | 2 800 RUB | 10 oct |
| Video | Samsung | 1 200 p. | 1 400 RUB | 12 oct |
| Video | Sony | 1 650 RUB | 1 800 RUB | 09 Nov |
| Video | Panasonic | 1,370 p. | 1 600 RUB | 08 Dec |
| Electr. plate | Ariston | 2 500 RUB | 2 700 RUB | 12 oct |
| Electr. plate | Ariston | 2 600 RUB | 2 700 RUB | 09 Nov |
| Electr. plate | Indesit | 2 100 p. | 2 300 RUB | 08 Dec |
| Steer. a car | Ariston | 2 600 RUB | 2 800 RUB | 12 oct |
| Steer. a car | Indesit | 2 800 RUB | 3,000 RUB | 11 Dec |
| Steer. a car | Bosh | RUB 3,700 | 4,000 RUB | Dec 17 |
· Sort the data in descending order of the date of sale;
· Sort the data so that the products are in alphabetical order, and the same products are also sorted by the wholesale price in descending order;
Only report data on Panasonic TVs using autofilter;
· Issue on the sheet only those goods, the retail price of which is less than or equal to 2500 rubles.
Construction of diagrams and graphs A remarkable property of ET is the ability to graphically represent numerical information. Charts and graphs are used to visualize table data. Diagrams allow you to quickly comprehend and interpret large volumes information contained in ET. Excel allows you to create charts on separate worksheets and embedded charts located on the same worksheet with the table. In both cases, charts change when the spreadsheet data is refreshed.
Building charts and graphs Excel supports Various types diagrams, each of which has several types. The choice of the type and type of charts depends on its purpose and should provide the best presentation of the data. The main chart types are: bar, bar, graph, pie, scatter, area, donut, radar, surface, bubble, stock, cone. A histogram is a set of vertical bars whose height is determined by the data values. A histogram is used to display one or more series of data; it is used to compare data or change it over time.
Building charts and graphs A bar chart is a bar chart that has bars horizontally instead of vertically. A graph is a smoothed curve or broken line that connects points that correspond to data values. A pie chart is used to represent one series of data. It demonstrates the relationship between the whole and its parts. Each value is represented as a sector of a circle, the angle of which is proportional to the fraction of the value represented in the total of all values. To make it easier to work with small fractions, they can be combined in the main chart into one element, and then presented side by side in the form of a separate pie chart.
Building charts and graphs A scatter plot displays the relationship between numeric values in multiple series in XY coordinates. When preparing data, the values of the argument are placed in the first row, and the values of the functions in the subsequent ones. This chart displays odd intervals - or clusters - of data and is often used in scientific research. An area chart shows the magnitude of the change in a particular factor over a period of a certain period time. It also displays the contribution of individual values to the total.
Petal Chart In a Radar Chart, each category has its own coordinate axis, originating from the origin. Lines connect all values from a specific series. It allows you to compare common values from multiple datasets.
Surface chart In a surface chart, like on a topographic map, areas with the same range of values are highlighted with the same color.
A bubble chart is a type of scatter chart in which the size of the marker indicates the value of the third variable.
Other Charts A donut chart shows the contribution of each item to the total, but unlike a pie chart, it can contain multiple series of data. Conical (cylindrical, pyramidal) are a kind of volumetric histograms that use data markers in the form of a cone, cylinder, pyramid. The built-in custom chart types are combinations of the standard chart types. Excel allows you to create and use your own chart types. Excel allows you to use geographic Maps to display ET data. Maps are OLE objects... They must first be activated.
Formulas in EXCEL Formula Is a combination of constant values, addresses, names, functions and operators that defines the rules for calculating the result in a cell. EXCEL uses the following operation symbols: +, -, *, /,%, ^, &, =, =, and the address operation symbols are colon, semicolon, and space. The colon is the range, the semicolon is the union of the ranges, and the space is the intersection. In descending order of priority, operations are arranged in the following order: range - ":", intersection - "", union - "; ", Negation -" - ", percentage -"% ", exponentiation -" ^ ", multiplication and division -" * "," / ", addition and subtraction -" + "," - ", text concatenation -" & ", Comparison operations - =, =,.
Formulas in EXCEL When EXCEL cannot calculate a value by a formula, an error message is displayed: ##### - the cell width is not enough; # DIV / 0! - division by 0; # N / A - undefined data; #NAME? - the name is not recognized; # EMPTY! - the presence of two disjoint areas; #Number! - problems with the number; #REFERENCE! - Invalid cell reference; #VALUE! - the value is of the wrong type.
The project plan is a key part of any project manager's toolbox. Of course, you want to start working on tasks as soon as possible, but if you find the time and outline a strategy for implementing the project, you can save money and resources. The parts of your project will constantly shift, and you will need a project plan template to keep track of these changes.
How to choose a suitable template from a huge number of existing ones? We looked at them all and selected the most best templates project plans in Excel. In this article, we'll walk you through the different types of templates and also explain when to use each one. You can also download an Excel project plan template for free. We'll walk you through what a project plan is and how to create one in Excel, and how to create one in Smartsheet, a tool that simplifies management and offers additional features for collaboration.
Smartsheet
A work plan template helps you break down large project goals into smaller, easy-to-follow steps. This type of template shows what tasks need to be completed, who is responsible for completing them, and also contains the due date for tasks. A timeline is included in the template to help define expectations and improve the transparency of task completion, allowing everyone on the project to stay on top of deliverables and deadlines. The work plan template is best suited for large teams working on large projects with big amount tasks and deadlines.
Gantt chart work plan template
Download project plan template
The project plan is most commonly used in project management, where Gantt charts are needed to plan and report project progress. The Gantt View is a dynamic horizontal chart used to visualize the project timeline and is an important tool for organizing stakeholder access to project status changes. This template is perfect for project managers who work on large projects with a lot of contributors.
Action plan template
Download action plan template
An action plan lists all the steps required to achieve a specific goal. It includes all information about the actions to be taken, the persons responsible for their implementation, due dates, priorities and status. An action plan is similar to a work plan, but the action plan focuses on goals, while the work plan template displays the milestones for completing tasks, which are usually displayed in chronological order. The action plan template is suitable for individual use or for small teams.
Business plan template
Download business plan template
A business plan template displays business goals and the actions required to achieve them. A business plan focuses on the future development of your business and shows how you can achieve your goals. Business plans are more strategic than work or action plans and often include a concept definition, business profile, economic assessment, etc. A business plan is suitable for owners of an already running business, start-up entrepreneurs or entrepreneurs who need outside help.
- Go to the site site and enter your account(or try the free 30-day version).
- On the "Home" tab, click "New" and select the "View Templates" option.
- Enter the words "Gantt chart" in the "Search templates" field and click on the magnifying glass icon.
- You will see the existing templates, but for our example we will choose the Simple Project with Gantt Chart template. Click on the blue "Use Template" button in the upper right corner of the search screen.
- Give your template a name, choose where you want to save it, and then click OK.
2. Indicate all your design data
An inline template opens containing sample content for reference, as well as sections, subtasks, and sub-subtasks that are already formatted. In Smartsheet, you can easily add or remove columns based on the size of your project.
Just right-click on a column and select "Insert Column Right" or "Insert Column Left" to add a column, or "Remove Column" to remove a column. To delete the yellow highlighted text at the top of the table, right-click on it and select "Delete row".
- Add your tasks to the Task Name column. You will notice that the hierarchy has already been formatted for you. Enter your details in the Section 1, Section 2, and Section 3 fields (called “parent rows.” For more information on what hierarchies are, see).
- Enter your Tasks and Subtasks Rows below the parent.
- To the left of each line, you can add attachments directly to the issue (ideal for nesting stakeholder lists, budgets, etc.) or start a discussion about an issue.
3. Adding a start date and due date
- Add start dates and due dates for each task. If you click and drag either end of a task bar in your Gantt Chart, Smartsheet will automatically update the dates in your sheet.
- Click on a cell in any of the date columns.
- Click on the calendar icon and select a date. You can also manually enter the date into the cell.
4. Enter data on% of completion and performers
Columns "Completed (%)" and "Responsible person" contain Additional information about your project and improve its transparency, allowing team members to see who is responsible for tasks and at what stage the implementation process is.
In a Gantt chart, the thin bars within the task bars represent the percentage of work completed for each task.
- In the "Completed (%)" column, enter the percentage of work completed for each task. Enter an integer and Smartsheet will automatically add a percent sign.
- In the Person Responsible column, select the name of the contractor from your contact drop-down list, or enter the name manually.
