entrancePractical work on MS Excel. Workshop on Excel Topic: "Boolean function if ..."
Practical work inMS E xcel
The laboratory workshop is intended for the practical study of the section, calculations in "SpreadsheetsMSExcel- 2007 "within the discipline" Information Technology in professional activity "by second-year students of various specialties GBOU SPO Polytechnic College №42, Moscow.
The workshop consists of four practical works on the main application topicsMS E xcelin calculations, focused mainly on students studying in the specialties "Economics and Accounting (by industry)", "Operations in logistics"And" Installation and technical operation of industrial equipment (by industry) ". Some of the topics of practical work can be used in teaching and students of other specialties.
Each practical work contains theoretical information on the topic of work, detailed analysis test case and 3 options for tasks on the topic.
Practical work
Subject:"Organization of settlements inMSExcel»
The purpose this practical work is the development of the technology of organizing tables inMSExcel, namely, copying, formatting cells, forming borders, presenting data and organizing simple calculation formulas. Figure 1 shows a table in whichColumn A organized by copying the contents of a cellA4 (date 01.04.13) down to the required cell,columns B and C filled with original data, also using copying and subsequent editing of values,column D created by organizing a formula into a cellD4 (in the formula line, the form of the formula is shown) and then copying it down.
Fig. 1
The table in Fig. 2 is similar to the creation of the previous table, with the addition of the formation of totals by column and row. The formula can be written differently.
Fig. 2
Job optionson this topic« Organization of settlements inMSExcel»
Exercise 1 ... Create a table by task 1. ColumnMonth organize through copying cells, the next three columns with the original data, fill in and format the data in these columns. ColumnThe amount of the surcharge , create through a formula.
Assignment 2 ... Create a table by task 2. Organize the column by copying cells.
Assignment 3 ... Create a table according to task 3. Organize the column as follows from the beginning to fill the value 1.0 into the cellI 4 and 1.1 per cell I5, then select a range of cells consisting of cellsI 4, I5 and copy the selected range down.
Practical work
Subject:"Statistical functions"
The purpose This practical work is an introduction to the built-in statistical functions.
When processing statistical data quite often it becomes necessary to determine various statistical characteristics. For such calculations inMSExcela number of statistical functions are built in, for example:
AVERAGE (x 1, ..., x n)
arithmetic mean (x 1 + ... + x n) / n.
MAX (x 1, ..., x n)
the maximum value of the set of arguments (x 1, ..., x n)
MIN (x 1, ..., x n)
the minimum value of the set of arguments (x 1, ..., x n)
COUNT (x 1, ..., x n)
the number of numbers in the argument list
COUNT (x 1, ..., x n)
the number of values in the argument list and not empty cells
An example of performing an assignment using
statistical functions
Figure 4. Shows a table of product sales in the store.
Fig. 4
Note ... An empty cell in the "Number of Sales" column means that this product has not been sold.
Methodical instructions for completing the assignment:
Calculate:
proceeds from sales of each product;
total, average, maximum, minimum proceeds from the sale of all goods;
determine the total number of types of goods in the store,
how many types of goods are sold.
An example of performing an assignment on the topic "Statistical functions"
enter in cell D2(in the first cell of the "Sales revenue" column) formula: = B2 * C2 ("Sales revenue" = "Price" * "Number of sales");
copy the formula to the entire column;
enter formulas:
in D5 = SUM (D2: D4) - total revenue
in D6 = AVERAGE (D2: D4) - average revenue
in D7 = MAX (D2: D4) - maximum revenue
in D8 = MIN (D2: D4) - minimum revenue
in D9 = COUNT (A2: A4) - the number of types of goods
(counting the number of non-empty values)
in D10 = ACCOUNT (C2: C4) - the number of types of goods sold (counting the number of numerical values)
Job optionson this topic"Statistical functions"
Exercise 1 ... Organize the table "Rivers of Eurasia".
Fig. 5
Assignment 2 ... The age of ten people applying for vacancies in the firm is known. Determine the maximum, minimum, average age of applicants?
Assignment 3 ... The table contains information about the employees of the company: surname, work experience. Determine the average, maximum, minimum experience. How many employees are there?
Practical work
Subject:"Logic functionIF A…»
The purpose This practical work is an acquaintance with the most common function in logical expressions.
In practice boolean expressions are used for developmentbranching algorithm :
Algorithmic language
If a condition (boolean expression)
action 1
otherwise
action 2
all if;
condition
action 1
action 2
Block diagram
To build a branch inMSExcel there is a logical function IF, its structure is as follows:
IF A boolean valueTRUE ,
THAT statement 1 is executed ,
OTHERWISE statement 2 is executed .
Fig. five .
An example of setting the arguments of an IF function
(finding the maximum value of two numbers)
To call the functionIF A , you need to press the buttonf x Insert Function, found in the formula bar. Will appearFunction wizard in the cell Category you need to select a linebrain teaser and then select the functionIF A , fill in three cells:
Log_Expression
Value_if_true
Value_if_false
Figure 7. Shows an example of using the functionIF A Fig 7.
Job optionson this topic« Logical function IF ...»
Exercise 1... In a cell D8 put the value 800, that is, make Plan = Fact for V.V. Serov. Explain why the result has not changed?
Assignment 2 ... Column A is an arbitrary number with a value of about 1000, column B is 2% of the number, column C (result), logical function IF, provided if the number is greater than or equal to 1000, then the result will be = number + 2%, otherwise = number - 2%. Figure 8 shows the table.
Fig 8_1.
Assignment 3 ... Column E - first number, columnF- second number, columnG(result) is formed as follows, if number1 is greater than number2, then the result will be their sum, otherwise the result will be their difference. Figure 8_2 shows the original table with the result.
Fig 8_2.
Practical work
Subject:"Histograms, graphs"
The purpose this practical work is to master the technology of presenting data in the form of diagrams inMSExcel... For the formation of histograms, the availability of initial data is required, then, depending on the versionMSOffice, select the Insert menu and the desired type of histogram (graph). It is recommended to be in any cell of the source table with data before inserting the chart. Fig 9_1.
The following figure is Fig 9_2. formed a diagram - a graph of functions
y= sin(x), y= cos(x), y= x 2 (parabola). To generate graphs, a column of values byX... The values are formed from -6, 28 to 6.28 with a step of 0.1 Columns to formsin(x), cos(x) are selected through function insertion. The column for a parabola is organized by a formula. Fig 9_2.
Job optionson this topic« Histograms, graphs "
Exercise 1 ... Arrange the pie chart according to Fig 9_1.
Assignment 2 ... Organize a function scheduley= x^ 3 (cubic parabola).
Fig 9_3
Assignment 2 ... Arrange changes in the dollar exchange rate in relation to the ruble.
The workshop is intended for practical training and control works by table processor Excel 2007 and others. Can be used for self-study and homework. One cross-cutting example is considered throughout the workshop. Tasks are compiled according to the principle "What you saw, do it." At the end, materials for control works are given. It is assumed that basic skills in working with computer hardware, operating system Windows, the students have an Excel processor.
Groups of sheets.
You can select several worksheets at once, and adjacent worksheets can be selected by clicking on the tabs of the first and last worksheets while holding down Shift key... Multiple non-contiguous sheets can be selected by pressing and holding the Ctrl key. click on the tabs of the required worksheets.
By selecting multiple worksheets, you can enter the same data at the same time in each of them. To do this, select a group of worksheets and enter data into one of them. The data will appear in the corresponding cells of each of the highlighted worksheets.
CONTENT
1. Launching the program
2. Psychological preparation
2.1. Screen elements. Russian language. What is called
2.2. Give a name to the file
2.3. How to work with the mouse
2.4. Navigation and highlighting
2.5. Feel the firmness of your hand
2.6. Customization
3. Entering and editing data
3.1. Data input
3.2. Data types
3.3. How to edit data
4. Fast data entry
4.1. AutoCorrect
4.2. How to enter data sequences into cells
4.3. Not fast input
5. Actions with workbooks
5.1. How to navigate a workbook
5.2. How to set sheet names
5.3. Adding Sheets
5.4. Moving sheets
5.5. Sheet groups
5.6. Deleting Sheets
6. Drawing up formulas
6.1. Automatic summation of rows and columns
6.2. Drawing up elementary formulas
6.3. Composing Functions Using the Function Wizard
6.4. Formulas with relative and absolute addresses
7. Reordering the contents of cells
7.1. Move line
7.2. Column insertion
7.3. Moving a column
7.4. Copy data to another worksheet
7.5. How to copy only formula values
7.6. Alphabetical order
8. Formatting data
8.1. Quick Table Styles
8.2. Copy format to another cell
8.3. Formatting with the Home Ribbon
8.4. Formatting with the Format Cells command
8.5. How to change row height and column width
8.6. How to add frames and change colors
8.7. Conditional formatting
8.8. One step ahead: "Styles"
9. Building diagrams
9.1. How to create embedded charts
9.2. The chart notices changes in the table
9.3. How to add and remove a series of data
9.4. How to increase a data series
9.5. Changing the chart
9.6. Plotting
9.7. How to build a chart on a separate sheet
10. Answers
11. Materials for control works
Bibliography.
Free download e-book in a convenient format, watch and read:
Download the book Practical work in Excel 2007, Kiselev V.G., 2009 - fileskachat.com, fast and free download.
Download pdf
Below you can buy this book on best price with a discount with delivery throughout Russia.
Sections: Computer science
Purpose of work: formation of practical skills in working with spreadsheets MS Excel 2010
- Educational: generalization and consolidation of knowledge and practical skills in the creation and design of tables, diagrams, organization of calculations.
- Developing: development of attention, independence when working with a software product.
- Cognitive: development of interest in solving problems and intersubject connections, strengthening of cognitive motivations.
Equipment: computer class, software- MS Excel 2010.
Practical work No. 1
"Creating and Formatting a Table"
Purpose of work: To learn how to format a table using the Format Cell command.
- Create new file... Give the first sheet a name land and make a table according to the sample (font Arial, size 14):
2. Set the data format. Select cell C2 (place the cursor in it) with the right mouse button and in the context menu select Cell format…. In the tab Number select format Numerical , the number of decimal places is 0. Click OK. In cell C2, type 149600000.
Similarly, select cell C3 and set the format Numerical , the number of decimal places is 0. In cell C3, type 384400.
Select cell C4 and set the format Time ... In cell C4, type 23:56:04.
Select cell C5 and set the format Numerical , the number of decimal places is 3. In cell C5, type 365.256.
Select cell C6 and set the format Numerical , the number of decimal places is 1. In cell C6, type 29.8.
3. Format the table.
Combining cells. Combine the range of cells A1: C1 (cells A1, B1, C1). To do this, select the indicated cells with the left mouse button and select Cell format ... tab Alignment ... Check the box in the line merge cells .
Cell alignment. Select in line horizontally in the dropdown - horizontally value centered .
In line vertically in the dropdown - centered .
Change the width and height of cell A1 with the left mouse button.
Writing in multiple lines. Select cells B2: B6 with the left mouse button, in the context menu select Cell format…. tab Alignment ... Check the box in the line word wrap ... Nothing has changed in the table, since all information fits in the width of the cell. Decrease the width of column B so that the text appears as in the swatch below. (If the text in the cell is not all visible, it means that it is outside the border of the cell - you need to increase the height of the cell with the left mouse button.)
The table will take the form
Setting cell borders... Select cells A2: C6. In the context menu, select Cell format tab Boundaries ... Set external and internal boundaries.
The finished table will take the form.
4. Save the table in your folder under the name tables .
Practical work No. 2
"Building diagrams"
Purpose of work: Learn to create and format charts
Exercise 1.
1. Open the file tables ... Give the second sheet the Fur name and create a table based on the example shown.
2. Select the data range A2: Bll, including the row and column headers
3. Select the Insert tab, the Chart command group, select the chart type - Histogram - Volume histogram - Clustered histogram.
4. Change the name of the diagram.
After selecting the chart, the Work with Charts command bar will be activated. Select in the Layout tab - Chart Title - Above Chart. Click on the diagram name on the diagram, erase the old name and type a new one Wearing the best quality furs... The diagram will take the form
5. Formatting the chart.
Right-click on an empty space in the chart area and select the command The format of the chart area. Set the design options you like. Approximate result
Task 2.
1. Go to the next sheet and give it a name Caves. Create a table using the following example:
2. Build a diagram. Approximate diagram view
Task 3.
1. Go to the next sheet and name it Graph
2. Build a diagram like Schedule and format the chart (use the Chart Tools - Layout tab to create axis labels and data labels). Approximate diagram view
Practical work No. 3
"Autocomplete the table"
Purpose of work: learn how to fill table cells using the Autocomplete function.
The Autocomplete function allows you to automatically continue a series of cells, if the filling of the latter obeys a certain principle (arithmetic progression, days of the week, months). MS Excel searches for the filling rule of the entered data in order to determine the values of empty cells. If one initial value of the filling pattern is entered, then one cell is selected, if the list is with an interval of data change, then it is necessary to select two cells filled with the corresponding data.
Exercise 1.
1. Open the file tables autocomplete.
2.Autocomplete with numbers... In cell A2, type the number 1, and in cell A3 - the number 2. Select cells A2 and A3. Drag the fill handle with the left mouse button to cell A7.
3.Fill in the cells with the days of the week... In cell B1, print Monday. Drag the fill handle with the left mouse button to cell F1.
4. Fill in the rest of the cells and format the table using the sample.
Task 2.
1. Open the file tables... Go to new leaf and give it a name temperature.
2. Using the function Autocomplete, create a table based on the sample.
3. Save the file.
Practical work No. 4
on this topic"Calculations inMSExcel 2010 "
Purpose of work: to form the skills of creating, editing, formatting and performing the simplest calculations in spreadsheets.
Calculations in MS Excel tables are carried out using formulas. The formula always starts with an = sign. A formula can contain numbers, cell addresses, mathematical signs, and built-in functions. The parentheses allow you to change the standard order of actions. If the cell contains a formula, the worksheet displays the current calculation result for that formula. If you make a cell current, then the formula itself is displayed in the formula bar.
The rule of using formulas in MS Excel is that if the value of a cell really depends on other cells in the table, you should always use a formula, even if the operation can be easily performed “mentally”. This ensures that subsequent editing of the table does not compromise its integrity and the correctness of the calculations performed in it.
Exercise 1.
1. Open the file tables... Create a new sheet and give it a name area.
2. Draw up a sheet for calculating the area of a rectangle using a sample
3. Set the number format for cells B2, B3, B4 (one decimal place).
4. In cell B2, enter the number 6, in cell B3, enter the number 7.
5. The area of the rectangle is calculated in cell B4. Place the cursor in it. In order to calculate the area of a square, the length of the first side of the rectangle must be multiplied by the value of the second side of the rectangle, i.e. multiply the value of cell B2 by the value of cell B3. Enter the formula in cell B4. For this
- type = sign;
- left-click on cell B2;
- print the multiplication sign *;
- left-click on cell B3;
- press the Enter key.
The cell will display the result of the calculation by the formula = B2 * B3, the number 42.0.
6. Change the value in cell B2, see what has changed. Change the value in cell B3, see what has changed.
Task 2.
the perimeter of the square.
2. Draw up a sheet for calculating the perimeter of a square using a sample
3. Enter any number in cell B2
4. Enter the formula for calculating the perimeter in cell B3.
5. Look at the result.
Task 3.
1. Create a new sheet and give it a name amount of information.
2. The amount of information in bytes is known. Draw up a sheet for calculating the amount of information in the remaining units of information.
Task 4.
1. Create a new sheet and give it a name geography.
2. Fill in the sheet for calculation by the sample and fill in the empty cells of the table.
Literature.
- Vasiliev A.V. Work in spreadsheets: workshop / A.V. Vasiliev, O. Bogomolova. - M.: BINOM. Knowledge Laboratory, 2007 .-- 160 p.
- Zlatopolsky D.M. 1,700 tasks Microsoft Excel/ - SPb .: BHV-Petersburg, 2003 - 544 p.
Task execution technology:
1.
Run Microsoft program Excel. Take a close look at the program window.
One of the cells is highlighted (surrounded by a black frame). How do I select another cell? It is enough to click on it with the mouse, and the mouse pointer at this time should look like a light cross. Try highlighting different cells in the table.
Use the scroll bars to move around the table.
2.In order to enter text into one of the table cells, you need to select it and immediately (without waiting for the appearance of the so necessary for us in the processor Word text cursor) "write".
Enter in any highlighted (active) cell today's day of the week, for example: Wednesday.
Select the entire row of the table in which the name of the day of the week is located.
Click on the row heading (line number).
Select the entire column of the table in which the name of the day of the week is located.
Click on the column heading (name).
3. The main difference between the work of spreadsheets and a word processor is that after entering data into a cell, they must be fixed, i.e. make it clear to the program that you have finished entering information in that particular cell.
You can fix the data in one of the following ways:
- press the (Enter) key;
- click on another cell;
- use the cursor buttons on the keyboard (go to another cell).
Record the information you entered. Select the table cell containing the day of the week and use the paragraph alignment buttons.
4. You have already noticed that the table consists of columns and rows, and each of the columns has its own heading (A, B, C ...), and all rows are numbered (1, 2, 3 ...). In order to select the entire column, just click on its heading; to select the entire row, you need to click on its heading.
Select the entire column of the table in which the name of the day of the week you entered is located.
What is the heading of this column?
Select the entire row of the table in which the name of the day of the week you entered is located.
What title does this line have?
Determine how many rows and columns there are in the table?
Use the scroll bars to determine how many rows a table has and what the name of the last column is.
Attention!!! To quickly reach the end of the table horizontally or vertically, you must press the key combinations: Ctrl + → - end of columns or Ctrl + ↓ - end of lines. Quick return to the beginning of the table - Ctrl + Home.
Select the entire table.
Use the empty button.
5.Select the cell in the table that is in column C and row 4.
Notice that the address of the highlighted cell C4 appears in the Name field above the column heading A. Highlight another cell and you will see that the address has changed in the Name field.
6.Select cell D5; F2; A16
.
What is the address of the cell containing the day of the week?
7.Determine the number of sheets in Book1.
Paste through context menu Add – Sheet two additional sheets.
To do this, go to the sheet shortcut Sheet 3 and right-click on it, a context menu will open, select the Add option and select in the Insert Sheet window. Added Sheet 4. Add Sheet 5 in the same way. Attention! Pay attention to the names of the new sheets and their placement.
Change the order of the sheets in the workbook.
Click on Sheet 4 and while holding left button, move the sheet to the desired location.
8.Set the default number of worksheets in the new workbook to 3. To do this, run the command Service-Options-General.
Report:
- In a cell A3 Specify the address of the last column of the table.
- How many rows are there in the table? Specify the address of the last line in the cell B3.
- Enter your name in cell N35, center it in the cell, and apply boldface style.
- Enter the current year in cell C5.
- Rename Sheet 1