Modern technologies processing of information often leads to the fact that there is a need to present data in the form of tables. In programming languages, this representation is two-dimensional arrays. Tabular calculations are characterized by relatively simple formulas, on which calculations are made, and large amounts of initial data. Such calculations are usually classified as routine work; a computer should be used to perform them. For these purposes, created spreadsheets (spreadsheet processors) is a general-purpose application software designed to process various data presented in tabular form.

Spreadsheet (THIS) allows you to store in tabular form a large number of initial data, results, as well as connections(algebraic or logical relations) between them. When the initial data changes, all results are automatically recalculated and entered into the table. Spreadsheets not only automate calculations, but are also effective tool modeling various options and situations. By changing the values ​​of the initial data, one can monitor the change in the results obtained and choose the most appropriate one from the many options for solving the problem.

Spreadsheet Functions varied:

creation and editing of spreadsheets;

Creation of multi-table documents;

design and printing of spreadsheets;

construction of diagrams, their modification and solution economic tasks graphic methods;

Creation of multi-table documents, united by formulas;

· work with spreadsheets as with databases: sorting tables, data selection by queries;

creation of summary and summary tables;

use of information from external databases when building tables;

Creation of a slide show

solution of optimization problems;

solving economic problems such as "what - if" by selecting parameters;

· development of macros, customization of the environment according to the needs of the user, etc.

The working field of the spreadsheet processor is the display screen, on which the spreadsheet is represented as a rectangle divided into rows and columns. Lines are numbered from top to bottom. Columns are labeled from left to right. Not the entire document is visible on the screen, but only part of it. The document is stored in its entirety in random access memory, and the screen can be considered a window through which the user has the ability to view the table. To work with the table, a table cursor is used - a selected rectangle that can be placed in one or another cell. The minimum element of a spreadsheet, on which certain operations can be performed, is such a cell, which is often called cell. Each cell has a unique name(identifier), which is made up of the numbers of the column and row at the intersection of which the cell is located. The numbering of columns is usually carried out using Latin letters (since there are only 26 of them, and there are much more columns, then this numbering follows - AA, AB, ..., AZ, BA, BB, BC, ...), and rows - with using decimal numbers starting from one. Thus, names (or addresses) cells B2, C265, AD11, etc.

The next object in the table − cell range. It can be selected from consecutive cells in a row, column, or rectangle. When specifying a range, its start and end cells are indicated, in a rectangular range - the cells of the upper left and lower right corners. The largest range is the entire table, the smallest is a cell. Range examples - A1:A100; B12:AZ12; B2:K40.

If the range contains numerical values, then they can be summed up, the average value calculated, the minimum or maximum value found, etc.

Sometimes a spreadsheet can be integral part sheet, the sheets, in turn, are combined into book(such an organization is used in Microsoft Excel).

Cells in spreadsheets can contain numbers(integer and real), symbolic And string values, boolean values, formulas(algebraic, logical, containing a condition).

In formulas, when referring to cells, two addressing methods are used - absolute And relative addressing. When using relative addressing, copying, moving a formula, inserting or deleting a row (column) with a change in the location of the formula leads to the rebuilding of the formula relative to its new location. Because of this, the correctness of calculations is preserved for any of the above actions on cells with formulas. In some cases, it is necessary that when you change the location of the formula, the address of the cell (or cells) used in the formula does not change. In such cases, absolute addressing is used. In the above address examples

cells and ranges of cells addressing is relative. Absolute addressing examples (in Microsoft Excel): $A$10; $B$5:$D$12; $M10; K$12 (in the penultimate example, only the column is fixed, and the row can change; in the last example, the row is fixed, the column can change).

The operation of the spreadsheet is controlled through the command menu.

Functions are a powerful tool used in spreadsheets. Functions are predefined formulas that perform calculations on given values, called arguments, and in a specified order.

For example, the SUM function sums the values ​​in a range of cells, and the PMT function calculates the payment amount for one annuity period based on constant payments and a constant interest rate. The function enters the formula as an operand.

In order for the spreadsheet processor MS Excel to correctly recognize a function and perform calculations in accordance with its algorithm, it is necessary to strictly observe the syntax - a set of rules for writing this function. A function entry begins with its name, followed by an opening parenthesis, arguments separated by semicolons, and then a closing parenthesis. If a formula begins with a function, the function name is preceded by an equal sign (=).

Function arguments are values, the type and order of which, when written, must strictly correspond to the syntax of the function. As arguments, the function can contain numbers, text, booleans (such as TRUE or FALSE), arrays, error values ​​(such as #N/A), or references. In addition, arguments can be both constants and formulas. These formulas, in turn, can contain other functions.

In total, more than three hundred functions are included in the MS Excel spreadsheet processor, which are combined into 11 categories for convenience (for Excel 2003).

1. Functions for working with databases. Used to analyze data in lists. In this case, an interval of cells that form a list or database is considered as a database. The MS Excel TP assumes that the database is a list of related data, where data rows are records and columns are fields. The top line of the list contains the names of all columns. The names of many database functions often start with a "D".

2. Date and time functions. Used to analyze and work with date and time values ​​in formulas.

3. Engineering functions are used to perform engineering analysis. Three groups of functions can be distinguished in this category: a) functions for working with complex numbers; b) functions for converting numbers from one number system to another (decimal, hexadecimal, octal and binary); c) functions for converting quantities from one system of measures and weights to another.

4. Financial functions allow you to perform typical financial calculations and are used to plan and analyze the financial and economic activities of the enterprise, as well as in solving problems related to investing funds.

5. Information functions are designed to determine the type of data stored in a cell, check the fulfillment of some condition and return TRUE or FALSE depending on the result.

6. Logic functions allow you to check the fulfillment of one or more conditions, which makes it possible to implement selection or loop algorithms.

7. View functions are used to find the desired data in lists or tables. Using these functions, you can get text or numeric values ​​that are impractical or impossible to calculate, but can be selected from a table on a worksheet.

For example, by the name of a product or service, you can determine their cost according to the price list, etc.

8. Mathematical functions allow you to perform simple and complex calculations, such as calculating the sum of a range of cells, calculating the sum of cells in a range that satisfy a specified condition, rounding numbers, and so on. These include arithmetic, logarithmic, and trigonometric functions.

9. Statistical functions are required to perform statistical analysis data ranges. For example, you can use the aggregate function to draw a line over a group of values, calculate the angle of inclination and the point of intersection with the Y axis, and so on.

10. Text functions are used to perform actions on lines of text (for example, changing the case or determining the length of a line, combining several lines into one, etc.).

11. In TP, in addition to the named functions, external functions can be used. They function as add-ons - auxiliary programs, serving to add to Microsoft Office special commands or opportunities. Examples of such functions are:

EUROCONVERT - converts an amount into euros, converts from euros to the national currency of a country using the euro, or converts from one national currency to another using the euro as an intermediate;

SQL.REQUEST - provides connection to an external data source and execution of a query from a sheet. The result is returned as an array. This does not require any additional programming.

In some cases, it is necessary to use a function as one of the arguments to another function. For example, formula (6.13) uses the nested function AVERAGE and compares the result with the value 50. However, it contains another nested function CYMM(G2:G5)

ECJIH(CP3HA4(F2:F5)>50,CyMM(G2:G5),0) (6.13)

The nested function used as an argument must evaluate to the corresponding data type for that argument. For example, if the argument must be a boolean, i.e., have the value of either TRUE or FALSE, then the nested function must also evaluate to the Boolean value, either TRUE or FALSE. Otherwise, the error message "#VALUE!" will appear.

You can use up to seven levels of function nesting in formulas. If function B is an argument of function A, then it has a second nesting level. For example, in formula (6.13), the AVERAGE and SUM functions are second-level functions because both of them are arguments to the IF function. A function that might be nested as an argument in an AVERAGE or SUM function would be a third-level function, and so on.

To avoid syntax errors when entering formulas, it is advisable to use the Function Wizard - step by step instructions which makes this task easier. In this case, the input of the function is carried out in two stages. By command Insert/Function... the Function Wizard dialog box appears (Fig. 6.10), in which the user must select the function he needs to perform calculations. At the same time, in

Rice. 6.10. Function Wizard Dialog Box (Step 1)

The lower part of the window displays the function name, its description, and arguments.

In addition, from this window, using a hyperlink, you can call up help on this function, which, in addition to the description, provides examples of calculations. After pressing the OK button of this dialog box, the next window of the Function Wizard appears (Fig. 6.11), where you can see the description of each argument, the current result of the function and the entire formula. When you perform this step, the help for the function also remains available.

stupid user. Entering the function is completed by pressing the OK button.

When using references as arguments, the user must take into account the possibility of further copying the created formula. To do this, write them in the dialog box in absolute or relative form.

In cases where the user does not know exactly which function to use to solve a problem, the Function Wizard can also be useful. In the first step, it is enough to enter in the "Search for a function" field short description action to be performed and click the Find button. The list of functions found according to the provided description is displayed in the Select a function field.

After studying Chapter 6, the student should:

know

• the purpose of spreadsheet programs in the processing of arrays of numerical data;
• spreadsheet interface, document (book) structure, cell addressing and interconnection;
• types of data in a cell (number, text, formula, function, format);
• the difference between the content, value, and format of a cell;
• categories, names, and rules for writing basic spreadsheet functions;
• distinction between categories and data series in a table;
• types of charts, their use for visual display of large amounts of tabular data;

be able to

• create, edit and format data tables;
• make formulas, apply the functions of processing numerical and text arrays of information;
• choose the type of charts that most adequately represents tabular numerical data, build them, present them in reports and presentations;

own

• skills in building spreadsheets;
• spreadsheet skills;
• skills in the design of graphs, diagrams and symbols;
• skills of extracting data from text and numeric arrays-lists by criteria.

Basic concepts and method of organization

Spreadsheet - a program for mathematical, statistical and graphical processing of arrays of textual and numerical data organized in tables. Spreadsheet programs are also known as spreadsheet programs.

processors. A spreadsheet distributes and processes data (text, numbers, formulas) in cells of rows and columns, displays their values, automates calculations, and serves as an effective tool for modeling options and situations when data changes. Formulas and functions built into a spreadsheet determine how cells relate to one another. When data changes, the values ​​in the cells are instantly recalculated, showing what consequences this will lead to.

The information structure of the table is described lines with numbers 1, 2, 3, ... and columns with letters A, B, FROM,... therefore, the cells (or cells) of the table are denoted by the coordinates A2, C3, D4(Fig. 6.1). cell - the minimum table element at the intersection of a column and a row, having an address (name), by which its contents are determined and processed, a value is drawn up and displayed. Cell name(address) is defined by its column and row. Address C15 means: column cell FROM and line 15.

You can enter data into the table cells, set the format for their presentation (percentages, rubles), set formulas for calculating dependent values. For example, in the cells of the columns "Price per piece", "Quantity, pcs." enter data, and in the cells of the column "Total cost" - formulas for multiplying the price per piece by the number of pieces. Cells with formulas show values the result of the calculation, and if the data in the cells of the columns IN And FROM change, column cell values D will be recalculated. This allows you to explore "what if" scenarios as well as simulate various data change situations. Tabular calculations are characterized by relatively simple formulas and large amounts of initial data.

The spreadsheet processor distinguishes between the types of data stored in table cells in order to correctly interpret

Rice. 6.1.

Extract content and apply some operation to it. From the keyboard, you can enter text, numbers (a mixture of text and numbers is regarded as text), dates (a kind of numeric data), formulas, functions.

A spreadsheet processor can process extensive numerical information in an array of databases, analyze finances, incomes, taxes, conduct research in economic and legal statistics, sociology, give results not only in the form of numbers, but also diagrams, graphs, organizational charts. Evaluation of data, comparison of the results of calculations performed in a spreadsheet, speeds up decision making in management and business activities.

The calculations and functions performed in the table are determined by the commands available in its asset, which are represented by the menu bar. To make it easier to work with commands in spreadsheet programs, toolbars are provided.

One of the most productive ideas in the field of computer information technologies was the idea of ​​a spreadsheet. Many development firms software for PC created their own versions of spreadsheets - application programs designed to work with spreadsheets. Of these, Lotus 1-2-3 by Lotus Development, Supercalc by Computer Associates, Multiplan, and Excel by Microsoft are the most well-known. Domestic school computers are also equipped with simplified (educational) versions of spreadsheet processors.

Table processors (TP) - handy tool for economists, accountants, engineers, scientists - all those who have to work with large amounts of numerical information. These programs allow you to create tables that (unlike relational databases data) are dynamic, i.e. contain so-called calculated fields, the values ​​of which are automatically recalculated according to given formulas when changing the values ​​of the initial data contained in other fields. When working with spreadsheet processors, documents are created - spreadsheets (ET). A spreadsheet (document) is created in the computer's memory. In the future, it can be viewed, modified, recorded on magnetic disk for storage, print on the printer.

Spreadsheet environment

The working field of the spreadsheet processor is the display screen, on which the spreadsheet is presented in the form of a matrix. ET, like a chessboard, is divided into cells, which are commonly called table cells. The rows and columns of the table are marked. Most often, rows are numbered, and columns are alphabetic (letters of the Latin alphabet) designations. As on a chessboard, each cell has its own name (address), consisting of the column name and row number, for example: A1, C13, F24, etc.

But if there are only 8x8=64 cells on the chessboard, then there are much more cells in the spreadsheet. For example, an Excel spreadsheet has a table maximum size contains 256 columns and 16384 rows. Since there are only 26 letters in the Latin alphabet, starting from the 27th column, two-letter designations are used, also in alphabetical order: AA, AB, AC, ..., AZ, BA, BB, BC, ..., BZ, CA ... Last, 256- The th column is named IY. This means that there are cells with such, for example, names: DL67, HZ10234, etc.

Excel spreadsheets. Basic information.

The presentation of data in the form of tables greatly simplifies the analysis of information. To solve problems that can be represented in the form of tables, special software packages have been developed, called spreadsheets or spreadsheet processors. They are focused primarily on solving economic problems, but they can be used to solve mathematical, physical and engineering problems, for example, perform calculations using formulas, build graphs and diagrams.

Excel Program included in the office suite Microsoft programs Office and is designed to prepare and process spreadsheets under the operating system. Windows shells. Versions of Excel 4.0 and Excel 5.0 are for Windows 3.1, and Excel 7.0 and 97 are for Windows-95/98. There is Excel-2000 included in the Office-2000 software package running on Windows-2000. The older the version number of Excel, the more perfect it is.

Excel is one of the main office computer technology numerical data processing.

An Excel document is a file with an arbitrary name and an XLS extension. Such an *.xls file is called a workbook. Each *.xls file can contain from 1 to 255 spreadsheets, each of which is called a worksheet (Sheet). One spreadsheet consists of 16384 rows (row) and 256 columns (column) located in the computer's memory. Rows are numbered with integers from 1 to 16384, and columns are marked with Latin letters A, B, C,…, Z, AA, AB, AC,…, IY.

At the intersection of a column and a row, the main element of the table is located - a cell (cell). In any cell, you can enter the initial data - a number, text, as well as a formula for calculating derived information. The width of a column or row can be changed with the mouse. When entering data into a cell, this happens automatically, i.e. spreadsheets are "rubber". To specify a specific cell, an address is used, which is made up of the column designation and row number at the intersection of which the cell is located, for example: A1, B2, F8, C24, AA2, etc.

To make a cell active, point to it with the mouse and press the left mouse button. The cell will be highlighted with a rectangular frame. When entering a formula, you must first enter the = sign, since the = sign is a sign of a formula. A rectangular group of cells, defined by the first and last cell separated by a colon, is called an interval. Example: C5:D10. Selecting a group of cells is done with the mouse.

Excel spreadsheets can be used to create databases. Excel is a multi-window program. The windows are working excel sheets. To sort the data, you must specify the mouse

Microsoft Excel (hereinafter simply Excel) is a program for performing calculations and managing so-called spreadsheets.

Spreadsheet is the main tool used for processing and analysis digital information means computer science. Although spreadsheets are mostly related to numbers or financial transactions, they can also be used for various tasks data analysis, providing the user with great opportunities for automating data processing.

Excel allows you to complex calculations, which can use data located in different areas of the spreadsheet and related to each other by a certain relationship. To perform such calculations in Excel, it is possible to enter various formulas in the table cells. Excel performs the calculation and displays the result in the formula cell. Available formulas range from simple addition and subtraction to financial and statistical calculations.

An important feature of using a spreadsheet is the automatic recalculation of results when changing cell values. For example, you can use Excel to perform financial calculations, keep track of your organization's workforce, and so on. Excel can also build and update graphs based on the numbers you enter.

The file that Excel works with is called a workbook. The book, as a rule, consists of several worksheets, which may contain tables, texts, diagrams, figures.

The book is a good organizational tool. For example, you can collect in one book all documents (worksheets) related to a specific project (task), or all documents that are maintained by one executor. The basis of the worksheet (Fig. 79) is a grid of rows and columns. A cell is formed by the intersection of a row and a column. The cell (cells) selected with the mouse is called active (in Fig. 77, the active cell is highlighted with a frame).

Rice. 79. Table elements

A line in a worksheet is identified by a name (number) that appears on the left side of the worksheet.

A column in a worksheet is also identified by a name (in Latin letters) that appears at the top of the worksheet.

An Excel worksheet can contain up to 65,536 rows and 256 columns.

The cell - the main element of the table - has its own unique address, consisting of a column and row number, for example, E4.

Each cell contains one piece of information, be it a numeric value, text, or a formula.

Launch commands Office applications described in the previous chapter. When you enter the program, an Excel window opens and a blank workbook named Workbook 1 is displayed.

When you open a previously created file, a workbook appears in the Excel window with the entered data.

A workbook in Excel is a file that stores and analyzes data. A workbook file consists of several worksheets, which may contain tables, texts, charts, or pictures. Each worksheet is identified by a name that appears on the sheet's tab.

Common elements of the Office application window are described in previous chapters. Let's take a closer look at the specific elements of the Excel window (Fig. 80).

Rice. 80. Excel window

The status bar contains information about the active document, the selected menu command, and keyboard mode indicators. In it, the user receives messages on how to execute the started command to the end and view the intermediate results of some calculations.

The formula bar shows the formula (if present in the cell) or the data contained in the active cell. In the formula bar, you can enter and edit text, numbers, and formulas.

In an active cell, you can enter and edit data directly in the cell or in the formula bar.

Labels are used to identify a sheet in a workbook. By default, sheets are numbered Sheet 1, Sheet 2, etc.

The scroll buttons at the bottom left of the window are used to view sheet labels and to move between sheets in a workbook containing a large number of sheets.

Create a new workbook

Just like in Word, every workbook created by Excel is based on some model called a template. By default, Excel bases a new workbook on a template named Book. The template saves information about the formatting of cells and worksheets, as well as the toolbars used.

Clicking a button Create (New) on the standard toolbar, you can create a new, blank workbook based on the default template.

Create a book based on the selected template

1. Choose a team Create (New) from the File menu,

1. Select the desired bookmark to define the template category of the workbook you are creating.
2. Then choose a template or wizard to base the new workbook on.

Templates can be pre-created and can contain form elements for reports, including invoices, debt obligations, financial reports, etc., adopted in a particular organization (Fig. 81).

Rice. 81. Template Payment forms (sheet Invoice)

Moving inside a workbook

The workbook window shows only part of the sheet in the open spreadsheet. To view different parts of the sheet, use the vertical or horizontal sliders to scroll the sheet vertically and horizontally, respectively. Slider Position Shows the relative position of the screen window within the worksheet. Using sliders changes the viewport, but does not move the active cell.

In large tables, the data may be located outside the visible window of the screen. For moving active cell while scrolling the window, keys and keyboard shortcuts are used.

Table 16. Keys and keyboard shortcuts for moving the active cell

To jump to any cell in the workbook, you can use the cell reference (Figure 82).

Rice. 82. Transition Dialog Box

1. Choose a team Go from the Edit menu (Edit, Go To) or press the key on the keyboard F5.
2. Enter a cell reference (for example, cell C2) and click OK.

This operation is useful for jumping to a cell with a known address in large tables containing several dozen rows or columns.

To navigate between sheets in a workbook, use the sheet tabs and tab scroll buttons. To move to another sheet, you need to click on the label of this sheet.