Back forward

Attention! The slide preview is used for informational purposes only and may not represent all the presentation options. If you are interested this workplease download the full version.

The purpose of the lesson:to develop the skills of creating, editing, formatting and performing the simplest calculations in spreadsheets

Lesson Objectives:

• Educational:
• repetition during the lesson of the basic terms and concepts of the topic "Spreadsheet";
• developing skills in working with a spreadsheet;
• formulation of the concept of settlement operations in a spreadsheet, the purpose and possibilities of using formulas in ET.
• Developing:
• development of skills of individual practical work;
• development of the ability to reason logically, draw conclusions.
• Educational:
• development of cognitive interest, education of information culture.

DURING THE CLASSES

I. Organizational moment

• greeting students;
• mark absent;
• setting goals and objectives of the lesson (actualization and motivation).

II. Theoretical part

(In the course of presenting new material, the teacher uses the presentation, the students take notes). Spreadsheets are reviewed based on OpenOffice.org Calc.

Slide 1. Definitions

Electronic Sheets (ET) Is a program for mathematical, statistical and graphic processing of text and numerical data in rectangular tables. They allow you to automate the execution of the same type of calculations and recalculation with changing source data, as well as to process numerical information in an array of databases, analyze finance, income, taxes, and so on.
A large number of spreadsheets are currently developed: Microsoft Excel, Lotus 1-2-3, Corel, Spread32, Calc, Works, QuattroPro, Superplan other.
Among them, a commercial product is distinguished - MicrosoftExcel and open source - OpenOffice.org Calc .

Slide 2. Structure

The structure of the spreadsheet is discussed.

Slide 3. Columns, rows, cells

On the 1st click, the column is selected, on the 2nd - the row, on the 3rd, the cell is selected.

Slide 4. Range of cells

By clicking, an image appears with a selected range of cells, students need to name the range, and then click on the correct answer.

Slide 5. Types and formats of data

The basic types and format of data, methods of setting and changing them, as well as an example of writing a formula are considered.

A definition is given and an example is considered first of a relative and then an absolute reference.

A definition is given and examples of mixed links are considered.

III. Exercise for the eyes.

Complex number 1.

1) Blink quickly, close your eyes and sit quietly, slowly counting to 5. Repeat 4-5 times.
2) Close your eyes tightly (count to 3), open your eyes and look into the distance (count to 5). Repeat 4-5 times.
3) Stretch your right arm forward. Follow with your eyes, without turning your head, for slow movements of the index finger of an outstretched hand to the left and right, up and down. Repeat 4-5 times.
4) Look at the index finger of an outstretched hand at 1-4, then look into the distance at 1-6. Repeat 4-5 times.
5) At an average pace, do 3-4 circular movements with your eyes to the right side, the same amount to the left. Relaxing the eye muscles, look into the distance at the expense of 1.
6) Repeat 1-2 times.

IV. Practical part

Slide 8. Pinning

Use a hyperlink to open the simulator ( Appendix 1 ). Made in Notebook software for the SmartBoard interactive whiteboard.
We consolidate the knowledge gained through a frontal survey, completing tasks.

1. Fill in the blank blocks by choosing the correct answer

Answer options: Window Control Buttons, Title Bar, Scroll Bars,
Menu bar, Function wizard, Cell address, Active cell, Input line, Sheet shortcuts, Status bar, Toolbars, Cell

2. Fill in the blank blocks by choosing the correct answer

Answer options:

C4: F4, B2: D2, A2: A11, C2: E2, C4: F11, B2: B6

3. What will be the value of cell B5 if you enter the formula \u003d SUM (B1: B4) / 2 into it. (Move the answer to an empty block)

Answer options:120, 220 , 110, 200

4. What formula will be obtained when you copy the formula from cell C2 to cell C3. (Move the answer to an empty block)

Answer options:

\u003d $ A $ 1 * A2 + B2,
\u003d $ A $ 1 * $ A $ 2 + $ B $ 2,
\u003d $ A $ 2 * A3 + B3,
\u003d $ A $ 1 + A3 * B3

5. What values \u200b\u200bwill appear in cells D2 and E3 if you copy cell C1 to the cell data? (Move the answer to an empty block)

Answer options:

0.2 and 0.5; 0.4 and 1; 2 and 5; 1.25 and 2.5

After we proceed to the test, the base with questions is attached ( Appendix 2 ). Made in the iTest program.

1. A spreadsheet is ...

• code table application applied
• program for processing table-structured data
• a PC device that manages its resources in the process of processing data in tabular form
• system program that manages PC resources when processing tables

2. The spreadsheet is intended for:

• processing predominantly numerical data structured using tables
• orderly storage and processing of significant amounts of data
• visualization of structural relationships between data presented in tables
• editing graphical representations of large amounts of information

3. The spreadsheet is ...

• a set of numbered lines and columns named in the Latin alphabet
• collection of numbered lines
• a set of columns named by letters of the Latin alphabet
• a collection of rows and columns, arbitrarily named by the user

4. Rows of a spreadsheet

• denoted by letters of the Latin alphabet
• are numbered

5. In general, the columns of a spreadsheet

• are numbered
• randomly named by users
• denoted by letters of the Latin alphabet
• denoted by letters of the Russian alphabet

6. For the user, the cell of the spreadsheet is identified

• by sequentially specifying the column name and row number at the intersection of which the cell is located
• special code word
• the address of the machine word of RAM allocated for the cell

7. Calculation formulas in the cells of the spreadsheet are written:

• in ordinary mathematical notation
• in a special way using built-in functions and according to the rules adopted for writing expressions in programming languages
• according to the rules adopted exclusively for spreadsheets
• according to the rules adopted exclusively for databases

8. Expression 5 (A2 + C3): 3 (2B2-3D3) in the spreadsheet is

• 5 (A2 + C3) / 3 (2B2-3D3)
• 5 * (A2 + C3) / 3 * (2 * B2-3 * D3)
• 5 * (A2 + C3) / (3 * (2 * B2-3 * D3))

9. Select the correct formula entry for the spreadsheet

• C3 + 4 * D4
• C3 \u003d C1 + 2 * C2
• \u003d A2 * A3-A4
• A5B5 + 23

10. What will be the value of cell A8 if you enter the formula \u003d SUM (A1: A7) / 2 into it:

11. What is the address of the active cell?

12. Cell B4 of the spreadsheet contains the formula \u003d $ C3 * 2. How does the formula look after cell B4 is copied to cell B6?

• \u003d $ C5 * 4
• \u003d $ C5 * 2
• \u003d $ C3 * 4
• \u003d $ C1 * 2

13. What formula will be obtained when copying to cell C3, formulas from cell C2

• \u003d A1 * A2 + B2
• \u003d $ A $ 1 * $ A $ 2 + $ B $ 2
• \u003d $ A $ 1 * A3 + B3
• \u003d $ A $ 2 * A3 + B3

14. What ranges of cells are highlighted in the image?

• B2: B12
• D2: G2
• D2: F10
• D5: F10

15. What values \u200b\u200bwill appear in cells D2 and E3 if you copy cell C1 to the cell data?

• 0.4 and 1.2
• 8 and 12
• 0.8 and 1.2
• 4 and 12

Vi. Homework

Slide 9. Homework

p. 1.5.2 p. 96-99
Practical work 1.14 p. 99

Vii. Lesson summary

Slide 10. Reflection

The guys in a circle express themselves in one sentence, choosing the beginning of the phrase from the reflective screen. Reflective screen

• i found out…
• it was interesting…
• it was difficult…
• i performed tasks ...
• i realized that ...
• now I can…
• i felt that ...
• i bought ...
• i learned…
• i managed …
• i could ...
• i'll try…
• surprised me ...
• classes gave me something for life ...
• i wanted…

Grading a lesson.

A spreadsheet is a computer program that allows you to perform calculations with data presented in the form of two-dimensional arrays that simulate paper tables. Some programs organize data into “sheets,” thus offering a third dimension. Spreadsheets (ETs) are a handy tool for automating calculations. Many calculations, in particular in the area accounting, are performed in tabular form: balances, payroll, cost estimates, etc. In addition, it is convenient to solve a number of mathematical problems by numerical methods exactly in tabular form. Using mathematical formulas in spreadsheets allows you to represent the relationship between various parameters of some real system. Solutions to many computational problems that previously could only be accomplished through programming have become possible through mathematical modeling in a spreadsheet.

Worksheets and books... When working on a computer, the spreadsheet exists in the form worksheetwhich has a name (e.g. Sheet 1). Worksheets are combined into booksand the user can insert, copy, delete and rename worksheets. When you create, open or save a document in spreadsheets, it is actually about creating, opening or saving a workbook.

When working with spreadsheets, you can enter and modify data on multiple worksheets at the same time, and perform calculations based on data from multiple sheets.

Range of cells... In the process of working with spreadsheets, it is quite often necessary to select several cells - a range of cells. The range is specified by the cell addresses of the upper and lower range limits, separated by colons. You can select several cells in a column (range A2: A4), several cells in a row (range C1: E1) or a rectangular range (range SZ: E4) (Table 1.2).

Appearance tables... You can change the appearance of a table, selected ranges of cells, or individual cells. For cell borders, you can set different types lines (single, dotted, double, etc.), their thickness and color. The cells themselves can be painted in any color by choosing a color from the color palette.

Editing sheets... You can delete columns, rows, ranges of cells, and individual cells from a table. When deleting ranges of cells and individual cells, you need to specify in which direction (left or up) the cells will be shifted.

You can insert columns, rows, and cells into a table. When inserting ranges of cells and individual cells, you need to specify in which direction (to the right or down) the cells will be shifted.

· carrying out calculations of the same type over large data sets;

· automation of final calculations;

· solving problems by selecting parameter values, tabulating formulas;

· processing the results of experiments;

· searching for the optimal values \u200b\u200bof the parameters;

· preparation of tabular documents;

· building charts and graphs from available data.

The Excel document is called working book.The workbook is a set worksheets,each of which has a tabular structure and can contain one or more tables. The document window in Excel displays only currentworksheet with which work is carried out. Each worksheet has name,which is displayed on sheet tab,displayed at the bottom. Shortcuts can be used to switch to other worksheets in the same workbook. To rename a worksheet, double-click on its shortcut.

Cells and their addressing... At the intersection of columns and rows, table cells. They are the minimum elements for storing data. The designation of a single cell combines the column and row numbers (in this order) at the intersection of which it is located, for example: A1 or DE234. The cell designation (its number) serves as its address. Cell addresses are used when writing formulas that determine the relationship between values \u200b\u200blocated in different cells.

Range of cells.Data located in adjacent cells can be referenced in formulas as a whole. This group of cells is called range. Most often use rectangular ranges, formed at the intersection of a group of consecutive rows and a group of consecutive columns. A range of cells is denoted by specifying a colon separated by the cell numbers located in opposite corners of the rectangle, for example: A1 :C15 .

A single cell can contain data of one of three types: text, number or formula ,– and also stay empty. ProgramExcel when saving the workbook, it writes to the file only the rectangular area of \u200b\u200bthe worksheets adjacent to the upper left corner (cell A1 ) and containing all filled cells.

The type of data placed in the cell is determined automatically as you type. If this data can be interpreted as a number, the Exc programel it does so. Otherwise, the data is treated as text. Formula input always starts with“=” (equal sign).

Enter text and numbers.Data is entered directly into the current cell or into formula bar , located in the upper part of the program window directly below the toolbars. The insertion point is marked with a text cursor. If you start typing by pressing the alphanumeric keys, the data from the current cell is replaced by the text you enter. If you click on the formula bar or double-click on the current cell, the old cell content is not deleted and you can edit it. The entered data is displayed anyway: both in the cell and in the formula bar.

To complete the entry, saving the entered data, use the buttonEnter in the formula bar or keyEnter ... To undo the changes made and restore the previous cell value, use the buttonCancellation in the formula bar or keyEsc ... The easiest way to clear the current cell or selected range is to use the keyDelete.

Formatting the contents of cells.Text data is by default aligned to the left of the cell, and numbers– on the right. To change the display format of data in the current cell or selected range, use the commandFormat\u003e Cells... The tabs of this dialog box allow you to select the data recording format (number of decimal places, indication of the currency, date writing method, etc.), set the text direction and alignment method, define the font and style of symbols, control the display and appearance of frames, set the background color.

Formulas.Calculations in program tablesExcel carried out by formulas.The formula can contain numeric constants, linkson cells and functions Excelconnected by signs of mathematical operations. 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 a programExcel is that if the cell value really depends on other cells in the table, alwaysa formula should be used, even if the operation can be easily done in the mind. This ensures that subsequent editing of the table will not compromise its integrity and the correctness of the calculations performed in it.

To edit a formula, double-click on the corresponding cell. In this case, the cells (ranges) on which the value of the formula depends are highlighted on the worksheet with colored frames, and the links themselves are displayed in the cell and in the formula bar in the same color. This makes it easier to edit and validate formulas..

For example, in the cell IN 2 there is a cell reference AZ... In relative terms, we can say that the link points to a cell that is one column to the left and one row below the given one. If the formula is copied to another cell, this relative reference will be preserved. For example, when copying a formula to a cell EA27 the link will continue to point to the cell to the left and below, in this case the cellDZ28.

When absolute addressingthe addresses of the links do not change when copied, so the cell that the link points to is treated as non-tabular. To change the addressing method when editing a formula, select the cell reference and press the keyF4. Cell number elements using absolute addressing are preceded by $. For example, with successive presses keys F4cell number A1 will be written as A1, $ A $ 1, A $ 1 and $ A 1In the last two cases, one of the components of the cell number is considered as absolute, and the other– as relative.

Copying and moving cells in the programExcel can be done by drag and drop or via the clipboard. When working with a small number of cells, it is convenient to use the first method, when working with large ranges- the second.

Clipboard application.Transferring information through the clipboard has in the programExcel certain features associated with the complexity of control over this operation. First, you need to select the range to be copied (cut) and give the command to place it on the clipboard:Edit\u003e Copy or Edit > To cut... Pasting data into a worksheet is possible only immediately after it is placed on the clipboard. Any attempt to perform any other operation will result in canceling the started copy or move process. However, no data is lost because the "cut" data is only removed from its original location at the time of the paste.

Search for a solution.This add-in is used to solve optimization problems. Cells for which optimal values \u200b\u200bare selected and constraints are set are selected in the dialog boxFinding a solution , which is opened with the commandService\u003e Finding a solution.

Template wizard for collecting data.This add-in is designed to create templates that serve as forms for entering records into the database. When a workbook is created from a template, the data entered into it is automatically copied to the database associated with the template. The wizard is launched by the commandData\u003e Template Wizard.

Web Forms Wizard.Add-in Designed to create a form to be hosted on a Web site. The form is organized in such a way that the data entered by visitors is automatically added to the database associated with the form. The Excel data collection form must be created on the worksheet in advance. Setting up the data collection system is organized using a wizard, which is launched by the commandService\u003e Master\u003e Web Form.

In a programme Excel term diagramused to refer to all kinds of graphical representations of numerical data. The construction of a graphic image is based on a number of data.This is the name for a group of cells with data within a single row or column. Multiple data series can be displayed in one chart.

To build a diagram, usually useChart Wizard button clickedChart Wizard on the standard toolbar. It is often convenient to pre-select the area containing the data that will be displayed in the chart, but you can specify this information during the wizard.

Chart type... At the first stage, the wizards choose the shape of the diagram. The available forms are listedType on the Standard tab ... For the selected chart type, several options for presenting data are indicated on the right (the paletteView ), from which you should choose the most suitable one. In the tabNon-standard a set of fully formed chart types with ready formatting is displayed. After defining the shape of the diagram, click on the buttonFurther .

Data selection.The second stage of the wizard is used to select the data on which the diagram will be built. If the data range has been previously selected, a rough preview of the future chart appears in the preview area at the top of the wizard. If the data forms a single rectangular range, then it is convenient to select them using the tab

Data range... If the data does not form a single group, then the information for drawing individual data series is set on the tabRow ... The chart preview is automatically refreshed when the set of displayed data changes.

Chart design.The third stage of the wizard (after clicking on the buttonFurther ) consists in choosing the design of the diagram. On the tabs of the wizard window, you can set:

· chart title, axis labels (tabHeadings);

· display and labeling of coordinate axes (tabAxles);

· displaying a grid of lines parallel to the coordinate axes (theGrid lines);

· description of the plotted graphs (tabLegend);

· displaying labels corresponding to individual data items on the chart (tabData signatures);

· presentation of the data used in the construction of the graph in the form of a table (the tabData table).

Placement of the chart.At the last stage of the wizard (after clicking on the buttonFurther ) indicates whether to use a new worksheet or one of the existing worksheets to place the diagram. Usually, this choice is only important for later printing a document containing a diagram. After clicking the buttonDone the diagram is built automatically and inserted into the specified worksheet.

Editing the diagram.The finished diagram can be modified. It consists of a set of individual elements, such as the graphs themselves (data series), coordinate axes, chart title, plot area, and so on. When you click on an element of the diagram, it is highlighted with markers, and when you hover over it, it is described by a tooltip. You can open a dialog box for formatting a chart element through the menuFormat (for the selected item) or through the context menu (commandFormat ). The various tabs of the dialog box that opens allow you to change the display parameters of the selected data item.

To delete a chart, you can delete the worksheet on which it is located (Edit\u003e Delete Sheet), or select a chart embedded in the data worksheet and press the keyDelete.

ELECTRONIC TABLES

TEST

1. A spreadsheet is:

an application program designed to process data structured in the form of a table;

application program for processing code tables;

a PC device that manages its resources in the process of processing data in tabular form;

a system program that manages PC resources when processing tables.

2. The spreadsheet is intended for:

processing predominantly numerical data structured using tables;

orderly storage and processing of significant amounts of data;

visualization of structural relationships between data presented in tables;

editing graphical representations of large amounts of information.

3. The spreadsheet is:

a set of numbered lines and columns named by letters of the Latin alphabet;

a set of rows and numbered columns named by letters of the Latin alphabet;

a collection of numbered rows and columns;

a set of rows and columns, named by the user in an arbitrary way.

4. Rows of the spreadsheet:

are numbered.

5. In general, the columns of a spreadsheet are:

denoted by letters of the Latin alphabet;

are numbered;

designated by letters of the Russian alphabet;

are named by users in an arbitrary way;

6. For the user, the cell of the spreadsheet is identified by:

by sequentially specifying the name of the column and the row number at the intersection of which the cell is located;

the address of the machine word of the RAM allocated for the cell;

special code word;

a user-definable name.

7. Calculation formulas in the cells of the spreadsheet are written:

in the usual mathematical notation;

in a special way using built-in functions and according to the rules adopted for writing expressions in programming languages;

according to the rules adopted exclusively for spreadsheets;

according to the rules adopted exclusively for databases.

8. Expression 5 (A2 + C3): 3 (2B2-3D3) in the spreadsheet looks like:

5 (A2 + C3) / 3 (2B2-3D3);

5 * (A2 + C3) / 3 * (2 * B2-3 * D3);

5 * (A2 + C3) / (3 * (2 * B2-3 * D3));

5 (A2 + C3) / (3 (2B2-3D3)).

9. Select the correct formula entry for the spreadsheet:

10. When moving or copying in a spreadsheet, absolute references:

do not change;

are transformed depending on the new position of the formula;

are converted depending on the rules specified in the formula.

11. When moving or copying in a spreadsheet, relative links:

are transformed regardless of the new provision of the formula;

converted depending on the length of the formula;

do not change;

are converted depending on the new position of the formula.

12. Range is:

a set of cells that form a rectangular region in the table;

all cells in one row;

all cells in one column;

set of valid values.

13. The active cell is the cell:

to record commands;

in which commands are entered.

14. What formula will be obtained when copying to cell C3, the formula from cell C2:

\u003d $ A $ 1 * $ A $ 2 + $ B $ 2;

15. What will be the value of cell C1, if you enter the formula \u003d A1 + B1 into it:

16. What will be the value of cell C1 if you enter the formula \u003d SUM (A1: A7) / 2 into it:

KEY