The workshop is intended to conduct practical classes And tests by spreadsheet processor Excel versions 2007 and others. Can be used for self-study and homework. Throughout the workshop, one cross-cutting example is considered. The tasks are designed according to the principle “What you see is what you do.” At the end, materials for tests are given. It is assumed that basic skills in working with computer equipment, operating Windows system, students have an Excel processor.

Groups of sheets.
You can select several worksheets at once, and adjacent sheets can be selected by clicking on the tabs of the first and last worksheets while pressing Shift key. Multiple non-adjacent sheets can be selected by pressing and holding the Ctrl key. Click on the tabs for the required worksheets.

By selecting multiple worksheets, you can enter the same data into each one at the same time. To do this, select a group of worksheets and enter data into one of them. The data appears in the corresponding cells of each of the selected worksheets.

CONTENT
1. Launch the program
2. Psychological preparation
2.1. Screen elements. Russian language. What is it called
2.2. Dante file name
2.3. How to use the mouse
2.4. Navigation and highlighting
2.5. Feel the hardness of your hand
2.6. Settings
3. Data entry and editing
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 sequences of data into cells
4.3. Not quick input
5. Actions with workbooks
5.1. How to navigate workbook
5.2. How to set sheet names
5.3. Adding Sheets
5.4. Moving sheets
5.5. Sheet groups
5.6. Removing sheets
6. Drawing up formulas
6.1. Automatically sum 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. Rearrange cell contents
7.1. Move a line
7.2. Inserting a Column
7.3. Move a Column
7.4. Copy data to another worksheet
7.5. How to copy only formula values
7.6. Sort by alphabet
8. Data formatting
8.1. Express Table Styles
8.2. Copy a format to another cell
8.3. Formatting with the Ribbon Home
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. Step forward: “Styles”
9. Diagramming
9.1. How to create embedded charts
9.2. The chart notices changes in the table
9.3. How to add and remove data series
9.4. How to increase a data series
9.5. Changing the chart
9.6. Graphing
9.7. How to create a chart on a separate sheet
10. Answers
11. Materials for tests
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
You can buy this book below best price at a discount with delivery throughout Russia.

Task execution technology:

1. Run the program Microsoft Excel. Take a close look at the program window.
One of the cells is highlighted (framed with a black frame). How to select another cell? Just click on it with the mouse, and the mouse pointer at this time should look like a light cross. Try highlighting different table cells. 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 much needed text in the Word processor). text cursor) "write".

Enter today's day of the week into any selected (active) cell, for example: Wednesday.
Select the entire table row that contains the name of the day of the week.
Click on the row title (its number).
Select the entire table column that contains the name of the day of the week.
Click on the column header (its name).

3.The main difference between the operation of spreadsheets and word processor is that after entering data into a cell, it must be recorded, i.e. let the program know that you have finished entering information in that particular cell.

You can record data in one of the following ways:

1. • press the (Enter) key;
   • click on another cell;
   • use the cursor buttons on the keyboard (move 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 column has its own heading (A, B, C...), and all rows are numbered (1, 2, 3...). In order to select an entire column, just click on its header; to select an entire row, you need to click on its header.

Select the entire table column in which the name of the day of the week you entered is located.
What is the title 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 the table has and what the last column name is.
Attention!!! To quickly reach the end of the table horizontally or vertically, you need to press the key combinations: Ctrl+→ - end of columns or Ctrl+↓ - end of rows. Quick return to the beginning of the table - Ctrl+Home.
Select the entire table.
Use the empty button.

5.Select the table cell that is located in column C and row 4.
Please note that the address of the selected cell C4 has appeared in the Name field located above the header of column A. Select another cell and you will see that the address in the Name field has changed.

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 3 sheet shortcut and right-click on it, a context menu will open, select the Add option and select Insert Sheet in the window. Added Sheet 4. Add Sheet 5 in the same way. Attention! Pay attention to the names of the new sheets and where they are placed.
Change the order of the sheets in the book. Click Sheet 4 and hold left button, move the sheet to the desired location.

8.Set the default number of worksheets in a new workbook to 3. To do this, run the command Tools–Options–General.

Report:

1. In a cell A3 Specify the address of the last column of the table.
2. How many rows are there in the table? Please enter your address last line in a cell B3.
3. Enter your name in cell N35, center it in the cell, and make it bold.
4. Enter the current year in cell C5.
5. Rename Sheet 1

Practical work inMS E xcel

Laboratory workshop created for practical learning section, calculations in “SpreadsheetsMSExcel- 2007" within the discipline " Information Technology V professional activity» second-year students of various specialties of the State Budgetary Educational Institution of Secondary Professional Education Polytechnic College No. 42, Moscow.

The workshop consists of four practical works on the main topics of applicationMS E xcelin calculations, is aimed mainly at students studying in the specialties “Economics and Accounting (by Industry)”, “Operational activities in logistics" and "Installation and technical operation industrial equipment (by industry)". Some topics of practical work can be used in training by students of other specialties.

Each practical work contains theoretical information on the topic of work, detailed analysis test example and 3 variants of tasks on the topic.

1. Practical work

Subject:"Organization of settlements inMSExcel»

Purpose given practical work is to master the technology of organizing tables inMSExcelnamely copying, formatting cells, creating borders, presenting data, and organizing simple formulas calculations. Figure 1 shows a table in whichcolumn A organized by copying cell contentsA4 (date 04/01/13) down to the required cell,columns B And C filled with source data, also using copying and subsequent editing of values,column D , created by organizing a formula into a cellD4 (in the formula line, the type of formula is shown) and then copying it down.

Fig.1

The table in Fig. 2 is similar in creation to the previous table, with the addition of generating totals by column and row. The formula can be written differently.

Fig.2

Task optionson this topic« Organization of settlements inMSExcel»

Exercise 1 . Create a table according to task 1. ColumnMonth organize by copying cells the next three columns with the original data, fill and format the data in these columns. ColumnSupplement amount , create via formula.

Task 2 . Create a table according to task 2. Organize the column by copying cells.

Task 3 . Create a table according to task 3. Organize the column as follows, from the beginning fill in the value 1.0 in 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.

1. Practical work

Subject:"Statistical functions"

Purpose This practical work is to become familiar with the built-in statistical functions.

When processing statistical data quite often there is a need 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 )

maximum value from the set of arguments (x 1 ,…,x n )

MIN(x 1,…,x n)

the minimum value from the set of arguments (x 1 ,…,x n )

COUNT(x 1,…,x n)

number of numbers in argument list

COUNTA(x 1 ,…,x n )

number of values ​​in the argument list and non-empty cells

An example of completing a task using

statistical functions

Figure 4 shows a table of product sales in a store.

Fig.4

Note . Empty cell in the "Number of Sales" column means that the item was not sold.

Guidelines to complete the task:

Calculate:

• revenue from sales of each product;

  total, average, maximum, minimum revenue from sales of all goods;

  determine the total number of types of goods in the store,

  how many types of goods were sold.

An example of completing 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) - number of types of goods

(counting the number of non-blank values)

in D10 = COUNT(C2:C4) - the number of types of goods sold (counting the number numerical values)

Task optionson this topic"Statistical functions"

Exercise 1 . Organize the table “Rivers of Eurasia”.

Fig.5

Task 2 . The ages of ten people applying for vacancies in the company are known. Determine the maximum, minimum, average age applicants?

Task 3 . The table contains information about the company's employees: last name, length of service. Determine the average, maximum, minimum length of service. How many employees are there in total?

1. Practical work

Subject:"Logical functionIF…»

Purpose This practical work is to become familiar with the most common function in logical expressions.

In practice, Boolean expressions are used to developbranching algorithm :

Algorithmic language

If condition ( logical expression)

action 1

otherwise

action 2

all-if;

condition

action 1

action 2

Block diagram

To build a branch inMSExcel has a logical IF function, its structure is as follows:

IF value of logical expressionTRUE ,

THAT statement 1 is executed ,

OTHERWISE statement 2 is executed .

Rice. 5 .

Example of specifying arguments to the IF function

(finding maximum value of two numbers)

To call a functionIF , you have to press the buttonf x "Insert Function" located in the formula bar. will appearFunction Wizard in a cell Category you need to select a linebrain teaser and then select the functionIF , fill in three cells:

Logical expression

Value_if_true

Value_if_false

Figure 7 shows an example of using the functionIF Fig 7.

Task optionson this topic« Logical function IF...»

Exercise 1. In a cell D8 set the value to 800, that is, make Plan = Fact for V.V. Serov. Explain why the result has not changed?

Task 2 . Column A arbitrary number with a value of about 1000, column B is 2% of the number, column C (result), a logical IF function, provided that 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.

Task 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.

1. Practical work

Subject:"Histograms, graphs"

Purpose This practical work is to master the technology of presenting data in the form of diagrams inMSExcel. To generate histograms, initial data is required, then depending on the versionMSOffice, select the Insert menu and the right type histograms (graphics). Before inserting a chart, it is recommended to be in any cell of the source table with data. Fig 9_1.

The following figure is Fig 9_2. a diagram has been generated - a graph of functions

y= sin(x), y= cos(x), y= x 2 (parabola). To generate graphs, a column of values ​​is requiredX. Values ​​are formed from -6.28 to 6.28 in increments of 0.1 Columns for formationsin(x), cos(x) are selected via function insertion. The column for the parabola is organized by formula. Fig 9_2.

Task optionson this topic« Histograms, graphs"

Exercise 1 . Organize a pie chart according to the data in Fig. 9_1.

Task 2 . Organize the graph of a functiony= x^3 (cubic parabola).

Fig 9_3

Task 2 . Organize changes in the dollar exchange rate against the ruble.

=INTEGER((TODAY()-E4)/365.25)

Let's comment on it. The student's date of birth is subtracted from today's date. Thus, we obtain the total number of days that have passed since the birth of the student. Dividing this number by 365.25 (the real number of days in a year, 0.25 days for a normal year is compensated by a leap year), we get the total number of years of the student; finally, highlighting the whole part - the age of the student.

Whether a girl is an excellent student is determined by the formula (using cell H4 as an example):

=IF(AND(D4=5,F4="w");1,0)

Let's proceed to the basic calculations. First of all, you need to determine the boys' average score. According to the definition, it is necessary to divide the total score of the boys by their number. For these purposes, you can use the corresponding functions of the table processor.

=SUMIF(F4:F15,"m";D4:D15)/COUNTIF(F4:F15,"m")

The SUMIF function allows you to sum the values ​​only in those cells of the range that meet a given criterion (in our case, the child is a boy). The COUNTIF function counts the number of values ​​that meet a specified criterion. Thus we get what we need. To calculate the share of excellent students among all girls, we will take the number of excellent girls to the total number of girls (here we will use a set of values ​​​​from one of the auxiliary columns):

=SUM(H4:H15)/COUNTIF(F4:F15,"w")

Finally, we will determine the difference in the average scores of children of different ages (we will use the auxiliary column in the calculations Age ):

=ABS(SUMIF(G4:G15,15,D4:D15)/COUNTIF(G4:G15,15)- SUMIF(G4:G15,16,D4:D15)/COUNTIF(G4:G15,16))

Please note that the data format in cells G18:G20 is numeric, two decimal places. Thus, the problem is completely solved. The figure shows the solution results for a given data set.

Practical work 7

“Creating charts using MS Excel” By completing the tasks in this topic, you will learn:  Perform operations to create charts based on the data entered into the table;  Edit chart data, its type and design. What is a diagram? A chart is designed to represent data graphically. Lines, bars, columns, sectors, and other visual elements are used to display numeric data entered into table cells. The appearance of the diagram depends on its type. All charts, with the exception of the pie chart, have two axes: a horizontal one – the category axis and a vertical one – the value axis. When creating 3-D charts, a third axis is added – the series axis. Often a chart will contain elements such as a grid, titles, and a legend. Grid lines are an extension of the tick marks found on the axes, headings are used for explanation individual elements chart and the nature of the data presented on it, the legend helps to identify the data series presented in the chart. There are two ways to add charts: embed them in the current worksheet or add a separate chart sheet. If the diagram itself is of interest, it is placed on a separate sheet. If you need to simultaneously view the diagram and the data on which it was built, then an embedded diagram is created. The diagram is saved and printed along with the workbook. Once the diagram is generated, changes can be made to it. Before performing any actions on the diagram elements, select them by left-clicking on them. After this, call the context menu using the right mouse button or use the corresponding buttons Chart toolbar. Task: By using spreadsheet plot the function Y=3.5x–5. Where X takes values ​​from –6 to 6 in increments of 1. Working technology: 1. Launch the Excel spreadsheet processor. 2. In cell A1 enter “X”, in cell B1 enter “Y”. 3. Select the range of cells A1:B1 and align the text in the cells to the center. 4. In cell A2, enter the number –6, and in cell A3, enter –5. Use the AutoFill marker to fill in the cells below up to option 6. 5. In cell B2, enter the formula: =3.5*A2–5. Use the autocomplete marker to extend this formula to the end of the data parameters. 6. Select the entire table you created and give it external and internal borders. 7. Select the table header and apply an inner fill . 8. Select the remaining cells of the table and fill the inner area with a different color. 9. Select the entire table. Select Insert from the menu bar - Diagram, Type: point, View: Point with smooth curves. 10. Move the chart below the table. Independent work: Plot the function y=sin(x)/x on the segment [-10;10] with a step of 0.5. Display the graph of the function: a) y=x; b) y=x 3; c) y=-x on the segment [-15;15] with step 1. Open the "Cities" file (go to Network Neighborhood - Boss Documents - Grade 9 - Cities). Calculate the cost of a call without a discount (column D) and the cost of a call taking into account the discount (column F). For a clearer representation, construct two pie charts. (1-diagram of the cost of a call without a discount; 2-diagram of the cost of a call with a discount).

Practical work 8

"MS Excel. Filtering (selecting) data from the list"

By completing the tasks in this topic, you will learn:

 Perform data filtering operations based on a specific condition;

 Distinguish between sorting and filtering operations.

Filtering (sampling) of data in a table allows you to display only those rows whose cell contents meet a specified condition or several conditions. Unlike sorting, filtering does not reorder data, but only hides those records that do not meet the specified selection criteria.

Data filtering can be done in two ways: using AutoFilter or Advanced Filter.

To use the autofilter you need:

o place the cursor inside the table;

o select a team Data - Filter - AutoFilter;

o expand the list of the column by which the selection will be made;

o select a value or condition and set the selection criterion in the dialog box Custom auto filter.

To restore all rows of the source table, you need to select the row all in the filter drop-down list or select the command

To cancel the filtering mode, you need to place the cursor inside the table and select the menu command again Data - Filter - Autofilter (uncheck the box).

The advanced filter allows you to create multiple selection criteria and perform more complex filtering of spreadsheet data by specifying a set of selection conditions across several columns. Filtering records using an advanced filter is done using the menu command Data - Filter - Advanced filter.

Exercise.

Create a table in accordance with the example shown in the figure. Save it as Sort.xls.

Task execution technology:

1. Open the Sort.xls document

2. Place the frame cursor inside the data table.

3. Execute the menu command Data - Sorting.

4. Select the first sort key "Ascending" (All departments in the table will be arranged alphabetically).

Let us remember that every day we need to print a list of goods remaining in the store (having a non-zero balance), but for this we first need to obtain such a list, i.e. filter the data.

5. Place the frame cursor inside the data table.

6. Execute the menu command Data - Filter

7. Deselect the tables.

8. Each table header cell now has a "Down Arrow" button; it is not printed; it allows you to set filter criteria. We want to leave all records with a non-zero remainder.

9. Click on the arrow button that appears in the column Remaining quantity. A list will open from which the selection will be made. Select line Condition. Set the condition: > 0. Click OK. The data in the table will be filtered.

10. Instead of a complete list of products, we will receive a list of products sold to date.

11. The filter can be strengthened. If you additionally select a department, you can get a list of undelivered goods by department.

12. In order to again see the list of all unsold goods for all departments, you need to select the “All” criterion in the “Department” list.

13. To avoid confusion in your reports, insert a date that will automatically change according to your computer's system time Formulas - Insert Function - Date and Time - Today.

14. Restore the original version of the table and cancel the filtering mode. To do this, click on the arrow button and select the All line in the drop-down list, or run the command Data - Filter - Display all.