Menu
homeFiles and folders

Microsoft Excel spreadsheet processor. MS Excel spreadsheet processor MS Excel spreadsheet processor creating a table

EXCEL TABLE PROCESSOR AND

PROGRAMMING LANGUAGE

VISUAL BASIC

Guidelines

For part-time students

Part 1

Compiled by: ass. L.Yu. Koshkina

Assoc. Ponkratova S.A.

Art. Rev. Smirnova M.A.

Excel spreadsheet and Visual Basic programming language: Guidelines for correspondence students. Part 1 / Kazan. state technol. University; Kazan, 2003, p. 56

The basic techniques for working in the Excel environment are briefly described. The theoretical material presented is accompanied by a large number of illustrations and practical tasks to consolidate it.

The issues of creating programs in the Visual Basic programming language built into an Excel spreadsheet, as well as creating macros and dialogs, are considered.

Designed for students of technological specialties studying the discipline “Informatics”.

Prepared at the Department of Chemical Cybernetics.

Published by decision of the Expert Council on Informatization.

Reviewers: prof. Ziyatdinov N.N.,

Assoc. Kharitonov E.A.


Working with Microsoft Excel spreadsheet processor.

Table - a form of organizing data into columns and rows.

Spreadsheet– the computer equivalent of a regular table.

Table processor– a set of programs designed for creating and processing spreadsheets.

The spreadsheet is the most common and powerful technology for professional data management. Data of various types can be written in the cells (cells) of the table: text, dates, numbers, formulas, functions, etc.

The main advantage of a spreadsheet is the ability to instantly automatically recalculate all data related by formula dependencies when the value of any table component changes.



The Excel spreadsheet allows you to:

1. Solve mathematical problems: perform spreadsheet calculations (including like a regular calculator), calculate values ​​and explore functions, graph functions (for example, sin, cos, tg, etc.), solve equations, work with matrices and complex numbers and etc.

2. Carry out mathematical modeling and numerical experimentation. (What happens if? How to make it happen?).

3. Conduct statistical analysis, forecasting (decision support) and optimization.

4. Implement database functions - input, search, sorting, filtering (selection) and data analysis.

5. Enter passwords or set protection for some (or all) table cells, hide (hide) table fragments or the entire table.

6. Visually present data in the form of charts and graphs.

7. Enter and edit texts, as in a word processor, create drawings using the Microsoft Office graphic editor.

8. Import-export, exchange data with other programs, for example, insert text, pictures, tables prepared in other applications, etc.

9. Implement multi-table connections (for example, combine reports from company branches).

10. Prepare speeches, reports and presentations thanks to the built-in presentation mode.

Launch Excel carried out through the Windows Main Menu using the command Start – Programs – Microsoft Excel. Excel program file – excel.exe – usually found in folders Program Files – Microsoft Office – Office.

Microsoft Excel Basics

Document, i.e. Excel processing object is a file with an arbitrary name and *.xls extension. Each *.xls file can contain 1 book, and the book contains from 1 to 255 worksheets (spreadsheets). By default, each workbook contains 3 worksheets. The worksheet has a tabular structure and can consist of any number of pages.

Worksheets can be deleted ( Edit - Delete Sheet), add new ( Insert – Sheet), rename (click on the sheet shortcut first with the left mouse button, then with the right one, in the context menu that appears, select the command Rename).

The Excel spreadsheet consists of 65536 rows and 256 columns. Rows are numbered with numbers (from 1 to 65536), and columns are usually designated by letters of the Latin alphabet A, B, C, …, Z. After column Z columns follow AA, AB, AC, BA, BB…IV.

Column header serves not only to designate a column, but also to select the entire column and change its width. Row header performs similar functions for strings.

Cell– the area of ​​a spreadsheet located at the intersection of a column and a row; this is the smallest structural unit on a worksheet. Current (active) cell – the cell in which the cursor is currently located. Each specific cell has an address. The cell address is made up of the column designations and line number. The address and contents of the current cell are displayed in the formula bar of the spreadsheet. Input and editing operations are carried out only in the active cell.

The cell can contain the following data: number, text, formulas, and can also remain empty. Data is entered directly into the current cell or into the formula bar. To complete the entry, press . Cancel changes made using the - key. Deleting the contents of the current cell - .

Cell addresses can be relative(A1, B2:C8 - change when copying, moving) and absolute($A$2, $B$4:$C$9 – remain unchanged when copying, moving), mixed(A$2, $B4 – in which only one of the components is constant).

Block (range) of cells– a group of consecutive cells.

A block of cells can be selected:

· Direct typing from the keyboard of the starting and ending addresses of the cells that form the range;

· Selecting a block using the mouse (non-adjacent blocks while pressing the key) or navigation keys.

The following actions are performed for a cell and a block of cells:

· Formatting

· Copying

·Insert

· Removal

· Moving

· Filling

· Cleaning formats, contents, values

Table cell format

The worksheet cells have a specified format, which is set by the command Format - Cells or context menu command Cell Format. These commands have multiple tabs : Number, Alignment, Font, Border, View, Protection.

· Number tab – sets the formats for presenting data in a cell.

· Alignment tab – determines the method of aligning data in a cell horizontally or vertically, and also determines whether text in a cell can be moved by words, allows or prohibits merging cells, and sets automatic selection of cell width.

· Font tab – changes the font, style, size, color, underline and text effects in selected cells.

· Border tab – creates a frame (frame) around the selected block of cells.

· View tab – allows you to set the cell shading (color and pattern).

· Protection tab – controls hiding formulas and locking cells (prohibiting editing of cell data).

Autocomplete with numbers

To fill cells with numbers: Edit – Fill – Progression. This procedure is convenient to use when tabulating a function.

Example: Fill column A with numbers from 1 to 10 in increments of 0.5.


Calculations in a Microsoft Excel spreadsheet

Formulas

Entering formulas begins with the “=” sign.

A formula can contain numeric constants, cell references, and functions connected by mathematical operation symbols. If a cell contains a formula, the worksheet displays the result of the formula's calculations. If you make a cell current, the formula appears in the formula bar.

If the formula bar is missing, you can enable it using View – Formula bar.

To display formulas on a worksheet: Tools – Options – General – Enable formulas. The formula may contain links, i.e. addresses of cells whose contents are used in calculations. You can set a cell reference in the following ways:

1. Type cell addresses in the formula bar (=B1+B2);

2. Left-click on the desired cell;

3. Using the name field, where the desired name is selected.

To edit a formula, double-click on the cell containing the formula, or press , or edit in the formula bar, selecting the required cell.

Building charts

Diagram is a graphical representation of numerical data. Each cell with data is displayed on the diagram - in the form of points, rectangles, lines and other graphic objects ( those. data point ).

To build a diagram you need:

§ Select data in the table;

§ Press button Chart Wizard on the toolbar Standard(or Insert – Diagram);

§ In the Chart Wizard window that opens (step 1 of 4): chart type, select the desired type, chart type, click the button Further;

§ In the Chart Wizard window (step 2 of 4): the source of the chart data, indicate the source data (data range (address of the table data block) and their location (in rows or columns)); press the button Further;

§ In the Chart Wizard window (step 3 of 4): chart parameters fill in the chart parameters. The Chart Wizard provides 6 tabs that allow you to set or edit the desired chart parameters.

ü In Headlines You can set the chart title and axis titles.

ü In Axes You can show or hide the X and Y axes.

ü Grid lines- an analogue of graph paper - help to more accurately determine the value of the data.

ü Legend– decoding of the lines of the diagram, corresponds to the names of the series.

ü Data signatures or data labels– allow you to display numerical data of chart points.

ü Data table– you can add a table of source data to the chart.

§ In the Chart Wizard (step 4 of 4): Chart Placement, specify the location of the chart. The diagram can be placed on the current sheet (embedded), or on a separate one.

§ Finish creating the diagram using the button Ready.

To change or format chart elements, select them by single-clicking the right mouse button and select the corresponding context menu item (or go to the menu Diagram).

To resize a chart:

1. Click on the diagram to make anchors appear.

2. Tow one of the tie-downs, thereby changing the size of the chart.

Removing a chart:

1. Select it by left-clicking on it.

2. Press the Delete key.

Trends

Trend is a function of a given type, with the help of which you can approximate a graph constructed from table data. Excel provides several standard trend types: linear, logarithmic, power law, exponential, polynomial, moving average.

A trend can be built for charts like:

§ Bar graph;

§ Bar chart;

§ Area chart;

§ XY-point.

Trend construction is carried out using the following technology:

§ Construct a chart for one series of data;

§ Select this diagram by left-clicking on it. Markers should appear on the diagram;

§ Call the context menu and execute the Add trend line command;

§ In the Trend Line dialog box, on the Type tab, select the trend type, and on the Parameters tab, set the parameters: Number of forecast periods, Show the equation on the chart, Place the approximation reliability value on the chart;

§ Click OK.

By selecting a trend line and calling the Format trend line command in the context menu, you can format or delete trend lines - the Clear command.

Example: Building a trend.

For this:

1. Create a table in Excel, shown in Fig. 1.

2. Calculate the average score in computer science and higher mathematics.

3. Format the table.

4. Construct a histogram of the distribution of computer science grades by group.

5. Construct a linear trend for the histogram.

6. Construct a polynomial trend for the histogram.

7. Design the chart and trend lines as shown in Figure 2.

8. Save your work in your folder.


Rice. 1. Table Average score for charting and trending

Solution:

1. Create the table shown in Fig. 1.

2. Using Function Master, In cell B7, enter the formula =AVERAGE(B3:B6). Copy this formula into cell C7.

· In the center;

· Merge cells – check the box.

5. Construct a distribution diagram for groups of grades obtained in computer science. To do this, call Chart Wizard by clicking the corresponding button on the toolbar. On the Standard tab, select the normal histogram type and click Next. Place the cursor in the Range line and select the block of cells A2:B7. Design the headings and axis names as shown in Fig. 2. Finish constructing the diagrams.

6. Construct a linear trend for the histogram. To do this, place the mouse pointer on one of the histogram bars and click the left mouse button so that black marks appear on all bars. For a selected histogram, call the context menu by right-clicking. Run the command Add a trend line. In the Trendline dialog box, on the Type tab, select the Linear box. On the Options tab, set the following options:

· Forecast: forward for 1 period;

· Show equation on diagram: check the box;

· Place the approximation confidence value on the diagram: check the box.

Click OK. A trend line and an equation describing it will appear on the chart.

7. Construct a polynomial trend for the histogram using the technology in step 5.

8. Design the chart and trend lines as shown in Figure 2.

9. Save the file in your folder under your name.

Rice. 2. Histogram and trends

Procedure Parameter selection

Parameter selection is an Excel tool for analyzing a table, in which the values ​​of a parameter cell are changed so that the number in the target cell becomes equal to the specified one. Using this tool, it is possible, for example, to search for a solution to an equation with one unknown.

The problem is solved through the command Parameter selection menu Service. In the dialog box, the target cell, its value, and the address of the parameter cell are set.

Procedure Parameter selection only available if checked in the dialog box Add-ons, which opens with Service – Add-ons.

Example: Solve the equation using the Parameter Selection procedure.


Rice. 3. Solving an equation using the Parameter Selection service

Solution:

1. In cell A1 we enter the text “Parameter value”, in A2 - “Equation”.

2. Give cell B1 the name x ( Insert – Name – Assign) and enter into it the initial value x, equal to, for example, 1.

3. In cell B2, enter the formula =(x-2)^2*2^x.

4. Run the command Service – Parameter selection.

5. Set the address of the target cell $B$2, the value of the target cell is 1, changing the value of cell $B$1 (Fig. 3).

So the procedure Parameter selection will automatically change the value in cell B1, that is, x, until the value of the objective function in cell B2 becomes equal to the specified value.

Answer: x=1.3801 (with y=0.9999).

Matrix operations

A system of linear algebraic equations is given

Find:

1. determinant;

2. x 1, x 2, x 3;

3. multiply the matrix of coefficients a i, j by the number 5.

Solution:

Inverse matrix method is as follows: let a system of linear algebraic equations of the form A*X=B be given, where A is the matrix of coefficients for the unknowns; X – vector of unknowns; B is the vector of free members. Then X=A -1 *B, where A -1 is the inverse matrix of matrix A.

Rice. 4. Matrix operations

1. In cells B1:D3 enter the coefficient matrix for the unknowns. To cells F1:F3 enter the vector of free terms (Fig. 4).

2. To find the determinant of the matrix, click on the cell B5. Next in the Function Wizard dialog box, which was called using the menu command Insert – Function, choose the function =MOPRED( B1:D3) from the Mathematical category. Using the mouse, select the matrix A located in the cells B1:D3. Press .

3. We find the unknowns x 1 , x 2 , x 3 using the inverse matrix method . To do this in cells B7:D9 Let's calculate the inverse matrix. Select a block of cells B7:D9. Further Insert – Function, category – Mathematical, function =MOBR( B1:D3). .

4. The result of multiplying the original matrix and the inverse matrix will be an identity matrix, the elements of which are equal to zero, except for the elements of the main diagonal, they are equal to 1. Select a block of cells B11:D13. Further Insert – Function,B1:D3; B7:D9). Press . Next, click on the formula bar. Press key combination .

5. Select a block of cells I1:I3, in which we calculate the unknowns x 1, x 2, x 3. To do this, let's do Insert – Function, category – Mathematical, function =MULTIPLE( B7:D9; F1:F3). Press . Next, click on the formula bar. Press key combination .

6. The result of multiplying a matrix by a number will be a matrix. Select the block B15:D17. Let's go to the formula bar, where, starting with the equal sign, we enter = B1:D3*5. Press .

7. Enter comments.

Using add-ons

Add-ins are special tools that extend the capabilities of spreadsheets. You can enable or disable add-ons using Service – Add-ons.

Most commonly used add-ons:

§ Analysis package;

§ Search for a solution (optimization);

§ Autosave;

§ Template Wizard.

Finding a solution

Linear programming problems (LPP) include optimization problems in which non-negative values ​​of factors minimizing or maximizing the values ​​of a linear objective function are sought, in the presence of restrictions specified in the form of linear equalities or inequalities.

The solution of the ZLP in Excel is carried out using the program Finding a solution.

Launching the program Search for a solution using the command Service – Search for a solution. This program is available if the dialog box Add-ons marked Finding a solution.

In the dialog box Finding a solution There are several input windows:

§ Set target cell– the address of the target cell in which the target function is located is set, in the form of a formula previously entered.

§ Equal to: either the maximum or minimum value(switch turns on) , or the value(indicated in the box).

§ Changing cells - The cells involved in the calculation of the objective function are indicated.

§ Restrictions - after clicking the add button, the Adding a constraint window appears, in which a cell is indicated in the left input window, then a sign is selected, and a constraint is indicated in the right input window.

Task: Draw up a staffing schedule for a self-supporting research laboratory, i.e. determine how many employees, in what positions and with what salary should be hired. The total monthly wage fund is 60,000 rubles. When deciding, take into account that for normal operation of the laboratory, 8-12 laboratory assistants, 2-4 units of technical personnel, 4-8 engineers, 1 supply manager, 2 department heads, 1 laboratory manager are needed.

The salary of a laboratory assistant is taken as the basis, and all others are calculated based on it: =A*X+B, where X is the salary of a laboratory assistant, A and B are coefficients that for each position are determined by a decision of the labor council. The laboratory assistant's salary must be at least 1,500 rubles.

Solution: Let's fill the table with the data shown in Fig. 5.

Rice. 5. Laboratory staffing table

1. Go to cell D3. Salaries are calculated using the formula: =В3*$В$11+С3(i.e. =A*X+B). Next, copy this formula to cells D4:D8.

2. Calculate the total wages in cells F3:F8.

3. The final salary value, located in cell F9

=SUM(F3:F8)

4. Let's use the command Service – Search for a solution to solve the optimization problem, having previously placed the cursor in cell F9, intended for the objective function. The Search for a Solution dialog box appears, in which you need to set the values ​​in the following input windows:


Restrictions– click the Add button (Fig. 6) and enter restrictions on the number of employees in the dialog box. For example, the number of laboratory assistants must be more than 8, but less than 12, and also integer. The laboratory assistant's salary must be >= 1500. All other restrictions are filled in similarly. After entering the last restriction, press the button OK.

Rice. 7. Add Constraint Dialog Box

A window will appear Finding a solution, press the button Execute. The Solution Search Results window will appear, click the button OK and analyze the results.

Macros

Macro is a recorded sequence of commands and actions, stored under a unique name, that Excel can execute.

Excel Visual Basic is a macro language, i.e. you can write a program in this language in the traditional way, or you can record a macro using the command Service – Macro and use the macro text along with the rest of your program text.

Microsoft Excel has a built-in tool for recording macro definitions - a macro recorder. It records a sequence of keystrokes and mouse movements as Visual Basic statements. In the future, the macro can be edited like a regular text file.

Texts of programs in Visual Basic are stored in special sheets of the workbook. These sheets are called modules .

Executing a Macro

To run a macro, select the menu command Service – Macro – Macros.

A dialog box will open with a list of macros. Having selected the desired macro, click on the button Execute.

Buttons

To make it easier to call a macro for execution, you can assign buttons to the macro:

§ Toolbar button . View – Toolbars – Settings. Among the Commands, select Macros – Custom Button and drag it using the mouse to any toolbar. To work with this button, click the right mouse button (Assign macro). Editing a button is only possible in setup mode.

§ Command button . View – Toolbars – Forms. In the Forms toolbar you need to select the button – Button. Use the mouse to draw a button and right-click Assign a macro.

§ Graphic object. Insert – Drawing – Pictures. Right click Assign a macro.

Objects

An object- this is what you manage using the Excel Visual Basic program:

§ Cell (cell),

§ Range

§ Chart (diagram),

§ WorkSheet (worksheet),

§ WorkBook (workbook).

The concept of an object includes the parameters of the object (properties) and software tools for working with it (methods). In a Visual Basic program, you must identify an object before you can apply methods to it or change its property values.

Methods

Method is a program that performs certain actions on an object.

Properties

Properties is a set of characteristics and attributes that describe an object.

Syntax: An object. Property = expression
Example: ActiveWindow. Heigt = HSize

Variables and Constants

It is important to distinguish the following concepts:

§ A memory cell is an element of RAM that stores data in the form of bytes.

§ Variable – a named memory area reserved for temporary storage of data that can change during program execution.

§ The value of a variable is the contents of those memory cells in which the variable is stored.

§ An identifier is just a variable designation adopted in the program.

§ Constant – a named element that maintains a constant value during program execution.

Data type

The type specifies what the variable can store: integer, string, date, etc.

To use memory efficiently, you need to choose the right type of variable (Table 1).

Table 1. Data types

Type Stored information Memory occupied Value intervals
Integer types
Byte Whole numbers 1 byte From 0 to 255
Boolean Boolean values 2 bytes True or false
Integer Whole numbers 2 bytes From –32768 to 32767
Long Integer Long Integers 4 bytes +/-2.1E9
Floating Point Types
Single Single-precision floating-point real numbers 4 bytes From –3.402823E38 to –1.401298E-45 for negative numbers and from 1.401298E-45 to 3.442823E38 for positive numbers
Double Double precision floating point real numbers 8 bytes From –1.7976313486232E308 to –4.94065645841247E-324 for negative numbers and from 4.94065645841247U-324 to 1.7976313486232E308 for positive numbers
String types
String (fixed length string) 1 byte per character From 1 to 65400
String (variable length string) Text information (string) 10 bytes + 1 byte per character From 0 to two billion characters
Object types
Object Drawing or reference to any other object 4 bytes Object reference
Variant types
Variant Values ​​of any of the listed data types 16 bytes for numbers, 22 bytes + 1 byte per character for strings Any numeric or string value
Other types
Currency Numbers with up to 15 digits before the decimal point and 4 digits after it (currency units) 8 bytes From –922337203685477.5808 to 922337203685477.5808
Date Date and time information 8 bytes From January 1, 100 to December 31, 9999
Decimal Decimal number 14 bytes Integer – 29 digits Real – 27 decimal places

Declaring Variables

Declaration of variables can be explicit or implicit. The first method is preferable.

With the second method of implicit declaration, a variable is declared simply by indicating its name in the program text.

Operation priority

Basic arithmetic operations include: addition (+), subtraction (-), multiplication (*), division (/).

Each arithmetic operation has its own priority. Higher priority operations (multiplication and division) will be executed before lower priority operations (addition and subtraction). You can change the order in which an expression is evaluated using parentheses.

Brackets can be nested within each other an arbitrary number of times. The use of square brackets or curly brackets is not allowed.

Data input

Entering data in the program

Data entry in the program is carried out using the assignment operator. The assignment operator is determined by the assignment sign. (=), to the left of which is the variable identifier, and to the right - some expression.

Example: x=3.


With Element_1

Element_2

Element_3

End With

Example 2:

Output of results

Elementary functions

Table 2. Functions in Visual Basic and Excel Spreadsheet

Function value Functions in an Excel table
sin x sin(x) sin(x)
cos x cos(x) cos(x)
tg x tan(x) tan(x)
ctg x 1/tan(x) 1/tan(x)
arcsin x asin(x) asin(x)
arccos x acos(x) acos(x)
arctan x atn(x) atan(x)
sh x sinh(x) =(exp(x)-exp(-x))/2 sinh(x)
Function value Visual Basic Built-in Functions Functions in an Excel table
ch x cosh(x) =(exp(x)+exp(-x))/2 cosh(x)
|x| abs(x) abs(x)
sqr(x) root(x)
e x exp(x) exp(x)
ln x log(x) Ln(x)
lg x log(x)/log(10) Log10(x)
log a x log(x)/log(a) log(x;a)

Example 1

where y=2/3, a=27.

Solution in Excel spreadsheet:

1. Rename Sheet1 to Linear_processes,

2. In cells A1:A3 enter comments for Example 1,

3. Rename cell B1 to a, B2 to y,

4. Enter the number 27 in B1, the formula =2/3 in B2,

5. Enter a formula into cell B3, using the Function Wizard to insert functions; it is better to insert arguments into the formula using the mouse,

6. When you finish entering the formula, press Enter.

Example 2

,

where x=ln2, y=0.28*10 2.

Solution in Excel spreadsheet:

Creating a Dialog

Information entry is often done using a keyboard and mouse. To organize the interaction between the user and the program, a variety of screen forms are used. A striking example of such forms are dialogs in programs for the Windows environment.

To create a dialogue (form), you need to insert a sheet with a template for a new dialogue into the workbook. The insertion is carried out

The first step when working with a screen form is to set the values ​​of its properties (Fig. 8). Property Name – the name of the form that is used in the program to refer to the display form. Property Caption– the text that appears in the title bar.


Rice. 8. Properties window

All controls in the dialog box are created using the buttons on the toolbar Forms(Fig. 10).

Rice. 10. Forms Toolbar

Example:

Create z calculation dialog

where y=2/3, a=27.

Solution:

1. Insert a sheet with a new dialogue template into the workbook. Tools – Macro – Visual Basic Editor, Insert – User Form (Insert – Dialog).

2. Call properties window View – Properties Window And Project window (View – Project Explorer).

A spreadsheet (or spreadsheet) is an application program that is designed to store and process data in tabular form. The table processor also makes it possible to work with large tables of numbers. Another feature is the use of formulas to describe the relationship between different cells. Among such programs we can note, which is part of the Windows operating system.

Microsoft Excel Spreadsheet

Excel is part of an office suite called Microsoft Office. The document that is created by this program is called a workbook. A workbook is a set of sheets that have a tabular structure and can contain various information. When opening such a document, only the sheet with which the user is working will be displayed. A list of such sheets is at the bottom of the screen. Using these shortcuts (each of which has its own name) you can switch to other worksheets.

Each sheet consists of rows and columns. Columns are named with capital Latin letters, and rows are named with numbers. The maximum possible number of columns is 256, rows - 65536. At the intersection of rows and columns there are table cells (they are also called table fields). This is the main element for storing data. Each cell has its own unique name, which is made up of the column and row names (for example, A1). This name is called the cell address. It is the address of the fields that is used to write formulas.

The cell that is active is highlighted with a black frame. Any operations of entering or editing information are performed in the active cell. You can select another cell using the mouse or keyboard.

In addition, there is another concept - cell range. It is formed at the intersection of sequential lines and columns. A range of fields also has its own unique name: in the name, separated by a colon, 2 cells are indicated, which are located in opposite corners of the range (for example, A1:B4). To select a range of cells, you need to hold down the left mouse button and drag the cursor to the desired mark.

Can contain 3 types of data: text, numeric and formulas, and can also remain empty. The program can automatically determine the data type as you enter it. For example, a formula always begins with an equal sign. Data is entered either directly into the field or into the formula bar, which is located just below the toolbar (it is labeled fx). Cell data is displayed simultaneously both in the formula bar and in the field itself. Typically, text information in Excel is aligned to the left edge of the field, and numeric information is aligned to the right. Editing information is done by double-clicking on the desired cell or by single-clicking in the formula bar. To save information in a cell, just press “Enter”.

Excel operating mode

The Excel spreadsheet processor can operate in several modes. The most important of them:


In Ready mode, the user can select a field or range of fields to enter information. As soon as he starts entering data in a field, the ready mode will automatically change to the data entry mode. In the field itself, the input cursor will begin to blink, indicating that you can add information to the cell. After saving the information in the cell, the ready mode is turned on again, the next range of fields is selected - again data entry mode, and so on until the user finishes working in Excel.

Over time, the user will need to perform some actions with the table, for example, sort, print, etc. Since in this case it is necessary to work not with one cell, but with their totality or with the entire table, the user will need to switch to command mode. This mode allows you to execute a specific command or item from the menu bar (for example, “Data - Sort”, “Insert - Function”). After all commands have been completed, the ready mode is turned on again.

Editing mode in Excel allows you to make changes to the contents of a field. Typically, when you re-enter information in a field, all of its contents are deleted. But using this mode, you can quickly edit data without deleting it (for example, a typo in a word). This mode is very convenient in situations where you need to make small changes.

Formulas in Excel

The Excel spreadsheet processor is not only a program for working with tables and huge databases, but also a “smart” calculator. One of the main capabilities of Excel is its ability to carry out various operations with numerical data, ranging from ordinary addition or multiplication to, and reports based on this data.

All calculations in Excel are called formulas. Any formula always begins with an equal sign. Otherwise, the table processor simply will not understand what the user wants from it. For example, to add 2+2, you need to write this: =2+2. Excel will immediately produce the result - 4. If you simply write 2+2, then the spreadsheet processor will simply write the same thing - 2+2.

In addition to standard arithmetic operators (addition, subtraction, multiplication and division), Excel also has:

  • exponentiation (^ sign)
  • percentage (% sign).

For example, if you need to raise the number 4 to the second power, then you need to write it like this: =4^2. If you put a % sign after any number, then it will be divisible by 100. That is, 3% will be 0.03.

Now you can check how the table processor calculates. To do this, you need to select any cell, write, for example, “=145+55” (without quotes) and press Enter. You can change the contents of a cell by going into edit mode. That is, you need to select this field, and then change the data in the formula bar or in the cell itself. For example, write “=140*55”.

Of course, you can write specific numbers into the formula, but this will not be entirely correct. The fact is that the spreadsheet processor can automatically recalculate formulas if the data in the cells has been changed. Therefore, the user has no need to waste extra time manually entering and then editing some data. To do this, you just need to write the formula correctly, and the program will do the rest.

So, let's say cells A1 and B1 contain the numbers 2 and 3, respectively. The user's task is to multiply them. To do this, you need to select any empty field, write an equal sign, then click on cell A1, put the multiplication sign and click on cell B1. After pressing the Enter key, the program will display the result. If you select this cell and look at the formula bar, you will see the following: =A1*B1. If you change the number 2 in field A1 to the number 5, the formula will be automatically recalculated - now the result is 15. This is exactly the principle on which formulas work in Excel.

In general, Microsoft Excel can solve many different problems, and to list all the capabilities of this program, you can compose an entire essay (or even a book). But this information should be enough to get acquainted with the program.

MS table processor EXCEL belongs to the class of application programs. Since 1994, it has been the most popular spreadsheet processor in the world.

The purpose of the program is to automate calculations, create business graphics, create spreadsheet documents, and maintain databases.

MS EXCEL spreadsheet processor is a program that is used by office managers, economists, financiers, and statisticians in their professional activities.

Program features

  • Data entry and editing.
  • Formatting table cells, rows, and columns
  • Entering formulas (calculation automation)
  • Application of a wide range of diverse functions
  • Building, editing and printing diagrams.
  • Preview and print the table
  • Creation and maintenance of databases

Question 28. Basic concepts of spreadsheets. Excel window structure.

Tables are used to present data in a convenient form. The computer allows you to present them in electronic form, and this makes it possible not only to display, but also to process the data. The class of programs used for this purpose is called spreadsheets.

A special feature of spreadsheets is the ability to use formulas to describe the relationship between the values ​​of different cells. Calculation using specified formulas is performed automatically. Changing the contents of a cell leads to the recalculation of the values ​​of all cells that are connected to it by formula relations and, thereby, to updating the entire table in accordance with the changed data.

The use of spreadsheets simplifies working with data and allows you to obtain results without manual calculations or special programming. Spreadsheets are most widely used in economic and accounting calculations, but spreadsheets can also be used effectively in scientific and technical tasks.

Spreadsheet Basics

The Excel document is called workbook. 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 only displays current worksheet with which the work is carried out. Each worksheet has Name, which is displayed on sheet label, displayed at the bottom. Using shortcuts, you can switch to other worksheets included in the same workbook. To rename a worksheet, double-click on its tab.

The worksheet consists of lines And columns. The columns are headed with capital Latin letters and, further, with two-letter combinations. In total, the worksheet can contain up to 256 columns, numbered A through IV. Lines are numbered sequentially, from 1 to 65,536 (the maximum allowed line number).

Cells and their addressing. At the intersection of columns and rows, table cells. They are the minimum elements for storing data. The designation of an individual cell combines the column and row numbers (in that 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 define the relationship between values ​​located in different cells.

One of the cells is always active and stands out frame of the active cell. This frame plays the role of a cursor in Excel. Input and editing operations are always performed in the active cell. You can move the frame of the active cell using the cursor keys or the mouse pointer.

Range of cells. Data located in adjacent cells can be referenced in formulas as a single unit. Such a group of cells is called range. Most rectangular ranges are often used, formed at the intersection of a group of sequential rows and a group of sequential columns. A range of cells is indicated by indicating, separated by a colon, the numbers of cells located in opposite corners of the rectangle, for example: A1:C15.

If you want to select a rectangular range of cells, you can do this by dragging the pointer from one corner cell to the opposite one diagonally. The frame of the current cell expands to cover the entire selected range. To select an entire column or row, click on the column (row) header. By dragging the pointer over the headings, you can select multiple consecutive columns or rows.

Excel window structure.

1. Main window title field.

2. Menu bar.

3. Control buttons

4. Toolbars.

5. Name field.

6. Formula line.

7. Status bar

8. Workbook window title field (Excel child window).

9. Active sheet of the workbook.

10. Button for selecting the entire sheet.

11. Active (current) cell.

12. Column name.

13. String name.

Question 29. Cell structure. Contents of the cell. Editing cell contents. Cell value. Formatting a cell.

Excel cell structure:

1st level contains an image visible on the screen (i.e. formatted text) or the result of a formula calculation).

2nd level contains cell formats (number format, fonts, switch (switch) sign to show or not the cell, frame type, cell protection).

3rd level contains a formula that can consist of text, numbers, or built-in functions.

4th level contains the name of the cell; this name can be used in formulas of other cells, while providing absolute addressing of this cell.

Level 5 contains the notes for this cell (free text). If a cell contains a note, a red square (dot) appears in the upper right corner

Each cell has an address - indicated by a column name and a row name. For example A2.

Cell Contents
1) Lyrics
2) Numbers
3) Dates
4) Computable expressions

Purpose. Using MS Excel, a document called a spreadsheet is created. The spreadsheet is generated in the computer's RAM. In the future, you can view it, change it, write it to a magnetic disk for storage, print it on printer.

Spreadsheet files have the extension xls. A single file can store a multi-table workbook containing several worksheet-tables, as well as worksheet-tables.

Wednesday. The MS Excel environment is shown in Fig. 2.4. It has a traditional window view for MS Windows applications. Let us describe its elements.

The title line contains the name of the program, the name of the workbook, and window control buttons. The main menu consists of basic spreadsheet management commands. Commands that cause the opening of a submenu can be called modes. Executable commands initiate specific actions on spreadsheet.


Fig.2.4 MS Excel environment

The toolbar contains icons for calling up the most frequently used commands. Among them there are those that are typical for many Windows applications, and there are also those that are specific to MS Excel.

The form line is intended to reflect the data entered into the current cell. The address of the current cell is displayed on the left side of the line (separate window). In this line you can view and edit the formula stored in the cell; In the cell itself, the user sees the result of the calculation using the formula.

The workspace contains the main part of the spreadsheet - the cells. The table cursor (rectangular frame) selects the current cell. In Fig. 2.4 the current cell is B2. IN MS Excel the maximum row number is b5536 and the last column is named IV (256 columns in total).

Sheet shortcuts allow you to move from one sheet of a workbook to another.

The hint line reflects the current operating mode of the table processor. Messages are also displayed here to the user about possible actions for a given table state.

Vertical and horizontal scroll bars are used to move the window around the spreadsheet.

Basic operating modes.

Ready mode ( "Ready"). In this mode, the current cell is selected or a block of cells is selected.

Data input mode ( "Enter"). Character-by-character data entry occurs with keyboards to the current cell.

Edit mode ( "Edit"). Used when it is necessary to edit the contents of a cell without completely replacing it.

Modes main menu. Each main menu element provides the user with the ability to select commands and submodes from a hierarchical menu system. After the command is executed, the system returns to ready mode.

In addition to the listed main operating modes of the TP, we can talk about table display modes.

Table display modes. Cells that store formulas can display the results of formula calculations or the formulas themselves. The first mode is called the value display mode, the second - the formula display mode. The working state of the table is the value display mode. The formula display mode can be used when creating and debugging a table.

Changing the display mode is done by algorithm: executing the command =>Tools => Options, selecting the View tab, setting/resetting the “Formulas” switch. The check mark in the switch frame indicates the formula display mode; If there is no checkmark, the main mode works - the mode of displaying values.

Command system.

MS Excel commands are organized in a hierarchical system, the top level of which is the main menu. In addition, command execution can be initiated through the toolbar, context menu, and hotkeys.

Table editing commands (menu Edit, Insert) allow you to manipulate table fragments: delete, copy, move, paste. Insertion and deletion of columns or rows causes other rows or columns in the table to shift. In this case, the relative addressing operating in the table automatically modifies the formulas in accordance with their changed addresses. The copying technique allows you to quickly build large tables containing similar elements.

Formatting commands (Format menu) allow you to change the appearance of the table and its design. Format elements include:

Ways to align data relative to cell boundaries;

Row height and column width;

Font type, style and size;

Number representation format (regular, exponential, bit depth);

Type of table layout;

Background color and so on.

A spreadsheet has a set of standard default format options.
Commands for working with files (File menu) - a standard set of commands that allow you to open and save files, organize printing of the resulting document.

We will not discuss commands for working with a table as a database here (Data menu). You will become familiar with some of the service capabilities of MS Excel (Service menu) in the following paragraphs. The View menu allows you to manage the spreadsheet environment.

Data in table cells.

Data for table processors is the information contained in table cells. The content of a spreadsheet cell can be a number, formula, or text. A special case of a formula is a variable (cell name). More general - an arithmetic or logical expression.

The word processor must "know" what type of data is stored in a particular table cell in order to correctly interpret its contents. So, for example, a sign of text data is the symbol “"” (apostrophe). A data type is a set of values ​​accepted by a value and a set of operations applicable to values ​​of this type. From here, for example, it follows that arithmetic operations cannot be applied to the contents of cells tables in which text information is stored.

Text- any sequence of characters entered after an apostrophe (single quote). In addition, any character sequence that cannot be perceived as a number or formula is perceived as text. In other words, if you made a mistake when entering a number or missed the “” sign when entering a formula , then the entered information will be perceived as text.

Numbers are divided into integers and reals. Real numbers can be written in two ways: in fixed point form and in exponential form (floating point form).

Fixed-point notation of a numeric constant assumes that the number contains an integer and a fractional part, separated by a decimal point. For example, the number 3.1415 is written in Excel. When writing a number in exponential form, first write the mantissa, then the Latin letter E (uppercase or lowercase), followed by the order.

The mantissa can be written as an integer or fixed-point constant, but the exponent can only be written as an integer two-digit constant. A numerical constant in exponential form is interpreted as the mantissa multiplied by 10 to a power equal to the order.

For example, the numbers 0.0001 and 1 OOO OOO can be written as follows: 1E-4 or 0.1e-3 and 1e6 or 1E+6.
In MS Excel, when a dot is used in a number, the number is interpreted as a date. For example, 05/3/99 is perceived as May 3, 1999.

Formulas(expressions). Spreadsheets use two types of formulas: arithmetic and logical. The result of a calculation using an arithmetic formula is a number, and using a logical formula - a logical value (true or false). There are certain rules for writing formulas. These rules are similar to those used in programming languages.

Arithmetic formulas are composed of constants, variables, operation signs, functions, and parentheses. Entering formulas in MS Excel begins with a sign = ».

Examples of formulas:

2.5*(G5+G2) =ROOT(В4~2-4*ВЗ*В5)
=SUM(C10:C20

Here the symbol “*” is a multiplication sign; "^" - exponentiation. In Russified versions of Excel, ROOT is a square root function, SUM is a summation function. The order in which expressions are evaluated follows the traditional priorities of arithmetic operations and the placement of parentheses. MS Excel has a very large library of functions. Inserting functions into formulas is done using the =>Insert =>Function command or using the corresponding button on the toolbar.

Logical expressions are constructed using relational operations (<, >, = , <=, >=, <>) and logical operations (AND, OR, NOT). The result of evaluating a logical expression is the logical values ​​“true” and “false”. You have already encountered logical expressions when working with databases. The peculiarity of logical expressions for spreadsheets is that logical operations are used as functions: first the name of the logical operation is written: AND, OR, NOT (AND, OR, NOT), and then the logical operands are listed in parentheses. For example, the logical expression I(A1>0, A1<1)
corresponds to the system of inequalities 0< А1< 1

Using logical expressions, in particular, conditions are specified, which are checked by a conditional function.

The conditional function has the following structure:

IF(condition, action1, action2)

If the condition is true, then action 1 is performed, otherwise action 2 is performed.

Example: let the sum of points he received be stored in cell E2 of a spreadsheet with information about the passing of entrance exams by an applicant. The following rule applies: if the total score is not less than 13, then he is admitted to the university. Then the formula is entered in cell F2:

IF(E2>=13, "accepted", "not accepted")

In the display mode, this cell will contain the word “accepted” or “not accepted”, depending on the exam result.

A conditional function can have a nested structure. Let the following rule apply in the same university: if an applicant scores 12 points, then he is accepted as a candidate (sometimes such a category also occurs). Then in cell F2 you need to enter the formula:

IF(E2>=13, "accepted", IF(E2=12, "accepted by the candidate", "not accepted"))

You will learn about some other features of MS Excel in the following paragraphs.


Semakin I.G., Henner E.K., Computer Science and ICT, 11

Submitted by readers from Internet sites

Lesson content lesson notes supporting frame lesson presentation acceleration methods interactive technologies Practice tasks and exercises self-test workshops, trainings, cases, quests homework discussion questions rhetorical questions from students Illustrations audio, video clips and multimedia photographs, pictures, graphics, tables, diagrams, humor, anecdotes, jokes, comics, parables, sayings, crosswords, quotes Add-ons abstracts articles tricks for the curious cribs textbooks basic and additional dictionary of terms other Improving textbooks and lessonscorrecting errors in the textbook updating a fragment in a textbook, elements of innovation in the lesson, replacing outdated knowledge with new ones Only for teachers perfect lessons calendar plan for the year; methodological recommendations; discussion programs Integrated Lessons Plan

3. Purpose of MS Excel

1. The concept of a table processor

The table processor provides work with large tables of numbers. When working with a spreadsheet processor, a rectangular table is displayed on the screen, the cells of which may contain numbers, explanatory texts and formulas for calculating the values ​​in the cell using the available data.

Spreadsheet processor is a software tool for designing spreadsheets. They allow you not only to create tables, but also to automate the processing of tabular data. Using spreadsheets, you can perform various economic, accounting and engineering calculations, as well as build various types of diagrams, conduct complex economic analysis, simulate and optimize solutions to various business situations, etc.

The functions of table processors are very diverse:

creating and editing spreadsheets;

creating multi-table documents;

design and printing of spreadsheets;

constructing diagrams, their modification and solving economic problems using graphical methods;

creating multi-table documents combined with formulas;

working with spreadsheets as databases: sorting tables, retrieving data based on queries;

creating summary and summary tables;

use of information from external databases when constructing tables;

creating a slide show;

solving optimization problems;

solving economic problems of the “what-if” type by selecting parameters;

development of macro commands, setting up the environment to suit the user’s needs, etc.

The most popular spreadsheets for personal computers are Microsoft Excel, Lotus 1-2-3, Quattro Pro and SuperCalc. And if, after its appearance in 1982, Lotus 1-2-3 was actually the standard for spreadsheet developers, now it has lost its leading position. Test results demonstrated a clear advantage of Excel in many respects .

2. Brief characteristics of MS Excel

Microsoft Excel (also sometimes called Microsoft Office Excel) is a spreadsheet program created by Microsoft Corporation for Microsoft Windows, Windows NT, and Mac OS. It provides economics-statistical capabilities, graphical tools, and, with the exception of Excel 2008 on Mac OS X, the VBA (Visual Basic for Applications) macro programming language. Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular programs in the world.

A valuable feature of Excel is the ability to write code using Visual Basic for Applications (VBA). This code is written using an editor separate from the tables. The spreadsheet is managed using an object-oriented code and data model. Using this code, the data from the input tables will be instantly processed and displayed in tables and charts (graphs). The table becomes the interface of the code, allowing you to easily change it and guide calculations.

Using Excel, you can analyze large amounts of data. In Excel, you can use more than 400 mathematical, statistical, financial and other specialized functions, link different tables to each other, choose arbitrary data presentation formats, and create hierarchical structures. Truly limitless methods for graphically presenting data: in addition to several dozen built-in chart types, you can create your own, which can be customized to help visually display the subject of the chart. Those who are just learning to work with Excel will appreciate the help of "wizards" - auxiliary programs that help when creating charts. They, like good wizards, asking leading questions about the foreseen next steps and showing, depending on the planned answer, the result, will guide the user “by the hand” through all the stages of constructing a diagram in the shortest way.

Working with a table is not limited to simply entering data into it and drawing diagrams. It is difficult to imagine an area where analysis of this data would not be required. Excel includes a powerful analysis tool - the Pivot Table. With its help, you can analyze wide-format tables that contain a large amount of unsystematized data, and with just a few clicks of the mouse button you can bring them into a convenient and readable form. Mastering this tool is simplified by the presence of a corresponding wizard program .

There are two main types of objects in Microsoft Excel: book and letter.

Book in Microsoft Excel is a file that is used to process and store data. Each book can consist of several sheets, so you can put a variety of information in one file and establish the necessary connections between them.

Letters serve to organize and analyze data that can be entered and edited simultaneously on multiple sheets, as well as perform calculations based on data from multiple sheets. Once created, charts can be placed on an email with the relevant data or on a separate email email.

Sheet names appear on tabs at the bottom of the workbook window. To switch from one letter to another, you need to specify the appropriate label. The name of the active sheet is in bold.

Microsoft Excel has a lot of different functions, including the following:

    Financial , among many special functions that calculate interest on a deposit or loan, depreciation charges, rate of return and a variety of inverse and related quantities.

    Date and time functions – most of the functions in this category are responsible for converting date and time into different formats. Two special functions TODAY and TDATE insert the current date (the first) and the date and time (the second) into the closet, updating them every time the file is called or when any changes are made to the table.

    Logic functions – this category includes 6 logical commands, including IF, AND, OR, NOT, TRUE, FALSE.

    Mathematical – this category is one of the most densely populated in Excel (more than 100 functions). There are no special tricks in it. But there are quite a few functions for all occasions.

    Link and arrays. This category contains functions that allow you to access a data array (by column, row, rectangular interval) and obtain a variety of information from it: the numbers of columns and rows it contains, their number, the contents of the array element you need; you can find which compartment of this array contains the required number or text, etc.

    Text – There are about two dozen teams in this group. With their help, you can count the number of characters in the collar, including spaces (DLST), find out the symbol code (CODE), find out which character is first (LEFT) and last (RIGHT) in a line of text, place a certain number of characters from another into the active box collars (PSTR), place in the active box all the text from another box in capital letters (UPPER CAPITAL) or capital letters (LOWER), check whether two text boxes match (COICH), find some text (SEARCH, FIND) and replace it with another (REPLACE ).

    Checking Properties and Values – here are commands with which you can get information about the type of data in the collar (a number is there, text or some other information), about the format, about the current operating environment, about typical errors that have arisen in the formula, etc. P..

    Working with the database – here you can find commands for statistical accounting (BDDISP - variance for a sample from the database, BDDISP - variance for the general population, DSTANDOTKL - standard deviation for the sample), operations with columns and rows of the base, the number of non-empty (BSCOUNT) or (BSCOUNT) cells, etc. .d.

    Chart Wizard – built-in EXCEL program, which simplifies working with the main features of the program.

3. Purpose of MS Excel

spreadsheet formula text

MS Excel is one of the most popular spreadsheet programs today. It is used by scientists and business people, accountants and journalists, with its help they maintain various tables, lists and catalogs, compile financial and statistical reports, calculate the state of a trading enterprise, process the results of a scientific experiment, keep records, and prepare presentation materials. Excel's capabilities are very high. Text processing, database management - the program is so powerful that in many cases it is superior to specialized editor programs or database programs. Such a variety of functions may at first confuse you, rather than force you to use it in practice. But as you gain experience, you begin to appreciate the fact that the limits of Excel's capabilities are difficult to reach.

Over the long history of tabular calculations using personal computers, user requirements for such programs have changed significantly. At first, the main emphasis in a program such as VisiCalc was on counting functions. Today, along with engineering and accounting calculations, the organization and graphical representation of data is becoming increasingly important. In addition, the variety of functions offered by such a calculation and graphic program should not complicate the user's work. Windows programs provide the ideal prerequisites for this. Recently, many have just switched to using Windows as their user environment. As a result, many software companies began to offer a large number of programs for Windows.

Excel provides both ease of handling data and its safety. Excel allows you to quickly complete work that does not require a lot of paper and time, and also involves the involvement of professional accountants and financiers.

This program will be able to calculate the amounts in the rows and columns of tables, calculate the arithmetic mean, bank interest or dispersion; here, in general, you can use many standard functions: financial, mathematical, logical, statistical.

Excel has many more advantages. This is a very flexible system that “grows” with the user’s needs, changes its appearance and adapts to you. The basis of Excel is the field of cells and the menu at the top of the screen. In addition, up to 10 toolbars with buttons and other controls can be located on the screen. It is possible not only to use standard toolbars, but also to create your own.

List of used literature

    Electronic resource: http://www.sch35-k.h14.ru/informatika/teacher01/excel/les01.html

    Electronic resource:

    Electronic resource: http://www.kolomna-school7-ict.narod.ru/st40201.htm