Spreadsheets (or spreadsheet processors) are application programs designed to perform spreadsheet calculations.

In spreadsheets, all processed information is located in the cells of a rectangular table. The difference between a spreadsheet and a simple one is that it has "fields" (table columns), the values ​​of which are calculated through the values ​​of other "fields" where the original data is located. This happens automatically when the original data changes. The fields of the table, in which the source data are located, are usually called independent fields. The fields that record the results of calculations are called dependent or calculated fields. Each cell of a spreadsheet has its own address, which is formed from the name of the column and the line number where it is located. Rows are numbered and columns are designated by letters of the Latin alphabet.

Spreadsheets are large. For example, the most commonly used Excel spreadsheet on IBM-compatible computers has 256 columns and 16,384 rows. It is clear that a table of this size cannot all fit on the screen. Therefore, the screen is only a window through which only part of the table can be seen. But this window is movable, and you can use it to look anywhere in the table.

Consider how a table might look like for calculating the expenses of schoolchildren who are going to go on an excursion to another city.

In total, 6 schoolchildren are going on the excursion, 4 of them are going to go to the museum, and 5 to the circus. Train tickets cost 60 rubles, but you can also go by bus, paying 48 rubles each. Then it becomes possible to either increase the cost of lunch, or buy tickets to the circus at a higher price, but for the best seats. There are tons of other options for budget allocation for a tour, all of which can be easily calculated using a spreadsheet.

A spreadsheet has several modes of operation: table formation (entering data into cells), editing (changing data values), calculating by formulas, storing information in memory, building graphs and charts, statistical data processing, sorting by attribute.

Formulas by which the values ​​of dependent fields are calculated include numbers, addresses of table cells, and operation signs. For example, the formula by which the value of the dependent field in the third row is calculated is: ВЗ * СЗ - the number in cell ВЗ multiplied by the number in cell СЗ, place the result in cell D3.

When working with spreadsheets, the user can also use the so-called built-in formulas (there are about 400 of them in Excel), that is, they are prepared in advance for certain calculations and entered into the computer's memory.

Most table processors allow ordering (sorting) of the table by some criterion, for example, in descending order. At the same time, in our table, the first place (in the second line) will be the expense for the purchase of tickets (the maximum value is 360 rubles), then (in the third line) there will be the expense for visiting the circus (100 rubles), then the cost for lunch (60 r.) and finally in the last line - the cost of visiting the museum (minimum value - 8 rubles).

Spreadsheets also provide a graphical mode of operation, which makes it possible to graphically represent (in the form of graphs, diagrams) the numerical information contained in the table.

Spreadsheets are easy to use, quickly mastered by non-professional computer users and greatly simplify and speed up the work of accountants, economists, scientists, designers and people of a number of other professions whose activities are related to calculations.

Practice has shown that solving many problems of an economic nature in high-level languages ​​using the entire arsenal of techniques and methods of professional programming is a complex and cumbersome business. A fundamentally different approach was required, and it was found and implemented in the form of spreadsheets - a tool available to non-professionals. The main field of application of spreadsheets is those areas of human activity where information is provided in the form of rectangular tables (planning and financial and accounting documents, accounting of material assets, etc.), which require mathematical calculations during processing, from which, apparently, arose the term "table processor". Note that relational databases, which are also represented using tables, are usually not suitable for calculations.

Currently, many variants of spreadsheets are known: ABAK, Varitab-86. Superplan, Multiplan, SuperCalk, QuattroPro, Excel, Lotus 1-2-3, etc. Basically, they all represent a spreadsheet processor and differ only in interface and service capabilities.

A spreadsheet (ET) is a rectangular matrix consisting of cells, each of which has its own number, Fig. one.

Rice. one.

The cell number is determined in the usual coordinate way, for example, cell OT, etc.

A group of cells (range) is specified with a colon, for example, B3: D4 (or B3..D4) and forms a rectangle that includes cells ВЗ, СЗ, D3, B4, C4, D4.

Each of the cells can be entered number, formula(arithmetic expression) or text. If a formula is written in the ET cell, then in the initial state the value of this formula is displayed on the screen, and not it itself. The operands of the formula can be mathematical functions, constants, cell numbers (the contents of the cell with the specified number). The ET cell has a complex "multilayer" structure, it can contain a reference to another cell, the value of which is the result of calculations using a different formula, etc.

Examples of functions:

sum (A2: A8) - the sum of the values ​​of all cells from A2 to A8;

sin (D5) - sine of a number from cell D5;

cos (F3) - cosine of the number from cell F3.

Example formula:

2.7. * A6 + cos (sum (D5: F7))

The above formula means that we want to get the result of the following calculations: add the product of the number from cell A6 to 2.7 with the cosine of the angle, which is the sum of the numbers from cells D5, E5, F5, D6, E6, F6, D7, E7, F7.

Data included in tables can be automatically presented in the form of graphs, charts, bar charts, etc.

The user works in a dialogue with a special program that allows him to fill the cells with the content he needs (texts, numbers or formulas for calculations); clear them, copy and delete, sort (i.e. arrange cells, as well as rows and columns from them, in a certain order); perform calculations on the entire table or part of it, save the table on disk and print part or all of it on paper, etc.

Let's give an example to illustrate the capabilities of ET.

Formation of the payroll.

This is how the initial data for filling the spreadsheet looks like:

Here we are dealing with three types of cell content: text, number, formula. Input of initial data occurs on the command line. After filling in the table, we will see on the screen:

Usually, an accounting employee, having corrected one of the numbers, was forced to correct the entire set of interrelated documents, which explicitly or implicitly included the corrected parameter. With the help of ET, such a change can be taken into account instantly and everywhere.

Applying this manual at the stage of consolidation and formation of practical skills, the student has the opportunity to update theoretical knowledge, test himself on test questions, and practice skills on practical tasks.

1. Structure of spreadsheets

A spreadsheet is an interactive numeric data processing program that stores and processes data in rectangular tables. A spreadsheet is made up of columns and rows. Column headings are designated by letters or letter combinations (A, G, AB, etc.), row headings - by numbers (1, 16, 278, etc.). Cell is the intersection of a column and a row. Each cell in the table has its own address. A spreadsheet cell address is composed of a column header and a row header, for example A1, F123, R7. The cell with which some actions are performed is highlighted with a frame and is called active. An Excel spreadsheet has 256 columns and 16384 rows. In Excel, tables are called worksheets. Worksheet (spreadsheet) is the main document type used in Excel to store and process data. By default, sheets are numbered “Sheet 1”, “Sheet 2”, etc. You can change the names and order of the sheets. The selection of another sheet of the workbook is carried out by clicking on the tab of this sheet. The selected sheet becomes active.

Control questions

1. What is the structure of spreadsheets?
2. How is a cell name set in spreadsheets?
3. What contains Sheet Excel spreadsheets?
4. What contains Workbook Excel spreadsheets?

Practical task.

1. Rename and arrange sheets in spreadsheets.

2. Entering data into a table cell

To enter data in a specific cell, you must select it with a mouse click and then click on the formula bar. The data you enter in the formula bar is reproduced in the current cell. When entering data on the left side of the formula bar, there are three buttons designed to control the input process: with a red cross, a green check mark and an icon. If you click on the button with a cross, the input will stop and the data that entered the cell in the current input session is deleted. Clicking on the button with the icon will bring up the Function Wizard dialog box. You can introduce a function.

After completing the data entry, you must fix them in the cell in any of three ways: By pressing the (Enter) key; By clicking on the checkmark button; By clicking on another cell.

You can also double-click on the selected cell to enter data, and the text cursor appears directly in the cell. Fragments of the contents of cells can be formatted in the usual way using the [Format-Cells ... / Font] tab or using the “Formatting” toolbar. You can experiment with the font yourself.

Practical tasks

1. Create a multiplication table with EXCEL (or another one according to the example).

Formulas use references to cell addresses. There are two main types of links: relative and absolute. Differences between relative and absolute references appear when you copy a formula from an active cell to another cell. Relative link in a formula is used to specify the cell address, calculated relative to the cell in which the formula resides. When you move or copy a formula from the active cell, relative references are automatically updated based on the new position of the formula. Relative links are of the form: A1, B3. By default, Excel uses relative references when typing formulas. Absolute link in a formula is used to specify a fixed cell address. Absolute references do not change when you move or copy a formula. In absolute references, a dollar sign (for example, $ A $ 1) is placed in front of the immutable cell address value.

Let's look at the action of absolute and relative links using an example. Suppose we need to calculate the cost of components for a computer in rubles, if their prices in dollars and the ruble exchange rate are known.

Copy formulas containing relative and absolute references.

1. Enter the device names in cells A5, A6 and A7, and their prices in dollars in cells B5, B6, B7. Enter the ruble exchange rate in cell C2.
2. Enter in cell C5 the formula = B5 * $ C $ 2, where B5 is a relative reference and $ C $ 2 is an absolute one. Copy formulas to cells C6 and C7; the absolute reference to cell $ C $ 2 will remain unchanged a, relative B5 is changed by the offset from the active cell.

If the dollar sign is in front of a letter (for example, $ A1), then the column coordinate is absolute and the rows are relative. If the dollar sign is in front of a number (for example, A $ 1), then, conversely, the column coordinate is relative, and the rows are absolute. Such links are called mixed.

Control questions

1. What is the difference between absolute and relative links?
2. Which links are called mixed links? Give an example.
3. In cell F13, entered the formula = F12 / $ B $ 4. Then this formula was copied to cell F16. What is the formula in cell F16?
4. In cell B7, entered the formula = (A6 + A7) * $ D $ 4. Then this formula was copied to cell F7. What is the formula in cell F7?
5. In cell D5, they entered the formula = $ A5 + B $ 5. Then this formula was copied to cell D2. What formula is in cell D2?

Practical tasks

1. Check in practice the correctness of the tasks 3, 4 and 5.
2. Open the sheet containing the table with the name of the devices and the price in USD. For the convenience of calculations, translate prices into rubles at the exchange rate, the value of which you can store in any cell convenient for you.

4. Practical task. "Price of computer devices"

In order to acquire the most efficient computer, estimate the shares that make up the prices of the devices included in it from the total price of the computer. Present the result in the form of a diagram.

Computer device price

1. Start Microsoft Excel. In your workbook, create a new worksheet for this practice activity.
2. Enter the names of the devices included in the computer and their prices in conventional units. For the convenience of calculations, we will translate prices into rubles at the rate, the value of which will be stored in cell B1 (or in another cell convenient for you).
3. Let's enter into cell C4 the formula for converting the price into rubles = $ B $ 1 * B4. Let's use the absolute reference to cell B1 to correctly copy the formula to cells C5-C15 using the [Edit - Fill - Down] command. Let's calculate the total price of the computer in cell C16 using the SUM function and determine the percentage of the price of each device.
4. Let's enter in cell D4 the formula for determining the share of the price of the device in the total price of the computer = B4 / $ B $ 16. Let's use the absolute reference to cell B16 to copy the formula correctly into cells D5-D15.
5. To represent the numbers in column D as percentages, enter the [Format-Cell ...] command. On the panel that appears Cell attributes in the window Category select Percent... The data in the table can be sorted in ascending or descending order of their values.
6. To sort devices alphabetically in ascending order, select the columns and rows of the table and click on the A-Z button. (Create copies of the table: in alphabetical order of device names, in descending order of device prices, in ascending order of device prices.). In the process of work (for example, when entering formulas), it is sometimes convenient to view the contents of cells not in the form of numbers, but in the form of formulas.
7. To view values ​​in the form of formulas, enter the [Service - Options ...] command. On the left side of the panel that appears, select Spreadsheet Document-Content. On the right side of the panel, check the Formulas box. For a visual display of the obtained numerical data, we will build a diagram.
8. Select the data area and enter the [Insert-Diagram] command. On the first panel that appears Chart auto-format (1-4) you can change the data area.
9. On the second panel, you must select the type of chart (in this case Circular volumetric) and the location of the data series (in this case the columns). The View window shows the view of the selected chart.
10. In the third and fourth panels, you can refine the details of the appearance of the chart and legend.
    As a result, we got a pie chart that gives a visual representation of the “contribution” of each device to the price of a computer.
    In spreadsheets, you can search for data (strings) in accordance with specified conditions. Such conditions are called filter... The search will find those strings that match the specified filter. Before starting the search, you must select at least one cell with data. For example, let's find all lines that contain information about devices worth more than $ 100.
11. Enter the command [Data-Filter-AutoFilter]. Drop-down lists containing standard search terms appear in the table column names. Expand the list in the Price column and select Condition… to enter custom conditions.
12. On the dialog panel Custom autofilter in the fields, enter the operator of the search term greater than and the value 100.
13. As a result, two rows will be found that match the specified filter. (Look in the table for devices that contain the word "Disk drive".)
14. Save the table with data and the diagram on the device price sheet.

5. Tabulation and graphing of functions in ET EXCEL.

The function Y (x) = x 2 -3x + 2 is given. Required:

Calculate the values ​​of the function Y for the values ​​of the argument x = -5; -4.5; -4;… 7; 7.5; 8;

Plot the function Y (x) using EXCEL and determine (approximately) at which values ​​of x the value of the function Y (x) is 0.

Problem solving technology

GENERAL INFORMATION ON EXCEL

Excel software belongs to a class of numerical information processing systems called SPREADSHEET. The literal translation of the term “spreadsheet” from English into Russian means “SPREADED SHEET (of paper)”. Meanwhile, in the computer world, this term means a completely specific class of software, which we call "ELECTRONIC TABLES" or systems for processing numerical information.

BRIEF HISTORICAL FLASHBACK

The first VisiCalc spreadsheet was released by Visi Corporation in 1981, and it is from this moment that it is customary to count the history of spreadsheets as an independent type of software.

The idea of ​​separating tables into a special class of documents and creating a specialized program that performs all kinds of operations with tabular data turned out to be very successful and was taken up by many companies. The popularity of spreadsheets has grown exponentially.

In 1983, Lotus Development Corporation released the 1-2-3 spreadsheet, which has become the de facto standard in its field for many years.

In 1985, the first version for the Macintosh platform, the most widely used Microsoft Excel today, appeared. A year later, this sector of desktop applications was replenished with the Quattro package, created by Borland. International Corporation. In 1989 it comes out under the name Quattro Pro.

Spreadsheets today occupy one of the leading positions in the sales structure of business software. The new generation of ET is characterized by a new level of functionality. In addition to traditional tools (such as calculations using standard functions, auto-recalculation, combining worksheets), modern ET packages are focused on working in the Internet environment, supplemented by teamwork tools, and significantly expanded functions for creating business graphics. The main competitors among the manufacturers of this software in 1998 were Corel (Quattro Pro 7), Microsoft (Excel 97, 7.0), Lotus Development (Lotus 1-2-3).

APPLICATIONS FOR ELECTRONIC TABLES

¨ ¨ accounting and banking;

¨ ¨ planning resource allocation;

¨ ¨ design and estimate works;

¨ ¨ engineering calculations;

¨ ¨ processing of large amounts of information;

¨ ¨ study of dynamic processes.

KEY FEATURES OF ELECTRONIC TABLES

· · Analysis and modeling based on the performance of calculations and data processing;

· · Design of tables, reports;

· · Formatting of the data contained in the table;

· · Construction of diagrams of the required type;

· · Creation and maintenance of databases with the ability to select records by a given criterion and sort by any parameter;

· Transfer (insertion) into the table of information from documents created in other applications running in the Windows environment;

· · Printing of the final document in whole or in part;

· · Organization of interaction in the working group (collective use, that is, distribution and viewing of spreadsheets by all members of the working group;

· · Work on the Internet (data retrieval and publication of information) using the ET toolkit.

ADVANTAGES OF USING THIS IN SOLVING PROBLEMS

1. 1. Solving problems using spreadsheets frees you from compiling an algorithm and debugging a program. You only need in a certain way, write in the table the initial data and mathematical relations included in the model.

2. 2.When using formulas of the same type, there is no need to enter them repeatedly, you can copy formula into the desired cell. This will automatically recalculate the relative addresses found in the formula. If it is necessary that the reference to a cell does not change when copying a formula, then it is possible to set an absolute (unchangeable) cell address (automatic setting of links).

COMPOSITION OF ELECTRONIC TABLE

The table uses columns(256) and strings (16384).

Lines are numbered from 1 to 16384, columns are marked with Latin letters from A to Z, and combinations of letters AA, AB, ..., IV,

The element located at the intersection of a column and a row will be called - cell(cage).

The rectangular area of ​​the table is called block(range, interval) cells. It is specified by the addresses of the upper-left and lower-right cells of the block, separated by colons.

CELL MODELS IN EXCEL

Each cell in the table has the following characteristics:

· · the address

· Contents

· · image

· Format

· · name

· Note (comment)

name content image

Cell address- column and row number. Used in formulas (as a relative, absolute or mixed reference), as well as to quickly navigate a table.

Excel allows 2 link styles: A1 style and R1C1 style.

FOR EXAMPLE . Suppose that in cell D3 you need to get the product of the numbers in cells A2 (second row, first column) and B1 (first row, second column). It can be written in one of the following ways:

Content cells can be:

· · number(signed or unsigned integer (-345), fixed-point fractional (253.62), or floating-point (2.5362e + 2));

· · text;

· · formula.

Formula- always starts with the "=" sign and can contain: numeric constants, absolute or relative references to cell addresses, built-in functions.

Function arguments are always enclosed in parentheses. Standard functions can either be entered from the keyboard, or you can use the INSERT / FUNCTION menu or the

Image- what the user sees on the monitor screen.

If the cell content is a formula, then the image will be its value.

The text placed in a cell can be "visible" in its entirety, or (if the adjacent cell is not empty) as many characters as the width of the cell.

The display of the number depends on the selected format. The same number in different formats (date, percentage, currency, etc.) will have a different image.

Cell format- number format, font, character color, frame type, background color, alignment on cell borders, cell protection.

Name- used as a replacement for the absolute cell address for use in formulas.

FOR EXAMPLE , by assigning a name to cell C3 “Product "in cell D3, you can put the formula: = Product / 3 (instead of the formula = C3 / 3). In this case, when you copy the formula, the cell address will not change.

Note- accompanying text to the contents of the cell. You can enter a note in a cell using the INSERT / NOTE menu. A cell that has a note is marked in the worksheet with a dot in the upper right corner.

So the model

cells can be represented

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 analyzing digital information by means of computer technology. Although spreadsheets are mainly related to numerical or financial operations, they can also be used for various data analysis tasks, providing the user with great opportunities to automate data processing.

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

An important feature of using a spreadsheet is the automatic recalculation of results when cell values ​​change. For example, you can use Excel to perform financial calculations, accounting and control of the organization's personnel, and so on. Excel can also build and update graphs based on the numbers entered.

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

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 performer. The basis of the worksheet (Figure 79) is a grid of rows and columns. A cell is formed by the intersection of a row and a column. The cell (s) highlighted by the mouse is called active (in Fig. 77 the active cell is highlighted by a frame).

Rice. 79. Table elements

A row 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 (Latin letters) that appears at the top of the worksheet.

An Excel workbook 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 number and a row, for example E4.

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

The commands for launching Office applications are described in the previous chapter. When you enter the program, an Excel window opens and a blank workbook named Book 1 is displayed.

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

A workbook in Excel is a file that stores and analyzes data. A workbook file consists of multiple worksheets, which can contain tables, text, diagrams, or pictures. Each worksheet is identified by a name that appears on the sheet tab.

The common elements of the Office application window are described in the 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 complete 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 data contained in the active cell. In the formula bar, you can enter and edit text, numbers, and formulas.

In the 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 shortcuts and to navigate between sheets in a workbook containing a large number of sheets.

Create a new workbook

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

By clicking the 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. Select a team Create (New) from the File menu,

1. Select the desired bookmark to define the category of the template for the workbook to be created.
2. Then select a template or wizard to base your new workbook on.

Templates can be created in advance and can contain elements of forms for reports, including invoices, promissory notes, financial reports, etc., adopted in a particular organization (Fig. 81).

Rice. 81. Template Payment forms (invoice sheet)

Moving inside a workbook

The workbook window only shows a portion of the sheet of an open spreadsheet. To view different parts of a 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, data can be located outside the visible window of the screen. To move the active cell while scrolling the window, use the keys and keyboard shortcuts.

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

You can use a cell reference to navigate to any cell in a workbook (Figure 82).

Rice. 82. Dialog Box Go

1. Select 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 tens of rows or columns.

You can navigate between sheets in a workbook using sheet labels and label scroll buttons. To move to another sheet, you need to click on the shortcut for that sheet.