Every modern PC user who has ever come across the Microsoft Office software package, which is one of the most common today, knows that MS Excel is a mandatory application. Now we will consider the basic concept of spreadsheets. Along the way, brief information about the main elements of the structure of tabular elements and data and some of their capabilities will be given.

What is an Excel spreadsheet?

The first mention of the Excel program refers to 1987, when Microsoft released its famous office suite of programs and applications, united by the common name MS Office.

In fact, the Excel spreadsheet is a universal tool for processing mathematical data of all varieties and levels of complexity. This includes mathematics, and algebra, and geometry, and trigonometry, and working with matrices, and solving complex systems of equations, and much more. Almost everything related to this plan is presented in the program itself. The functions of spreadsheets are such that many users are not only unaware of them, they are not even aware of how powerful this software product is. But first things first.

Getting started with spreadsheets

As soon as the user opens the Excel program, he sees in front of him a table created from the so-called default template. The main work area consists of columns and rows, the intersection of which forms cells. In the understanding of working with everyone, the main element of a spreadsheet is a cell, since it is in it that they are entered.

Each cell has a numbering consisting of a column and row ordinal designation, however, it may differ in different versions of the application. So, for example, in the 2003 version, the very first cell located at the intersection of column "A" and row "1" is designated as "A1". In the 2010 version, this approach has been changed. Here, the designation is in the form of ordinal digits and numbers, but in the description line, columns are designated as "C" and rows as "R". Many do not understand what this is connected with. But everything is simple here: “R” is the first letter of the English word row (line, row), and “C” is the first letter of the word column (column).

Data used

Speaking about the fact that the main element of a spreadsheet is a cell, you need to understand that this only applies to input data (values, text, formulas, functional dependencies, etc.). The data in cells can be in different formats. The function of changing the format of a cell can be called from when you right-click on it (right-click).

Data can be presented in text, numeric, percentage, exponential, money (currency), fractional, time formats, as well as a date. In addition, you can set additional options for the appearance or decimal places when using numbers.

Main window of the program: structure

If you look closely at the main window of the application, you will notice that in the standard version, the table can contain 256 columns and 65536 rows. The sheet tabs are located at the bottom. There are three of them in the new file, but more can be specified. If we consider that the main element of the spreadsheet is a sheet, this concept should be attributed to the content on different sheets of different data that can be used crosswise when setting the corresponding formulas and functions.

Such operations are most applicable when creating pivot tables, reports, or complex calculation systems. Separately, it should be said that if there are interconnected data on different sheets, the result when changing dependent cells and sheets is calculated automatically without re-introducing a formula or function that expresses one or another dependence of variables and constants.

Above is a standard panel with several main menus, and just below the formula bar. Speaking about the data contained in the cells, we can say that this element is also the main element of the spreadsheet, since the line itself displays either text or numeric data entered in the cells, or formulas and In the sense of displaying information, the formula bar and the cell are one and also. True, formatting or specifying a data type is not applicable for a string. It is purely a viewer and input tool.

Formulas in Excel Spreadsheets

As for the formulas, there are a lot of them in the program. Some of them are generally unknown to many users. Of course, to deal with all of them, you can carefully read the same reference guide, but the program also provides a special ability to automate the process.

By clicking on the button on the left fx ” (or the sign “ = ”), a list will be displayed from which you can select the necessary action, which will save the user from entering the formula manually.

There is another commonly used tool. This is the autosum function, presented as a button on Here, too, you do not have to enter a formula. It is enough to activate the cell in the row and column after the selected range where the calculation is supposed to be made, and click on the button. And this is not the only example.

Related data, sheets, files, and cross-references

As for interrelated links, data from another place in this document, a third-party file, data from an Internet resource, or an executable script can be attached to any cell and sheet. This even allows you to save disk space and reduce the original size of the document itself. Naturally, how to create such relationships will have to be carefully dealt with. But, as they say, there would be a desire.

Add-ons, charts and graphs

In terms of additional tools and capabilities, MS Excel spreadsheets provide the user with a wide choice. Without talking about specific add-ons or executable Java or Visual Basic scripts, let's focus on creating graphical visual tools for viewing analysis results.

It is clear that not everyone wants to draw a chart or build a graph based on a huge amount of dependent data manually, and then insert such a graphic object into a table as a separate file or an attached image.

That is why their automatic creation is used with a preliminary selection of the type and type. It is clear that they are built on the basis of a certain data area. Just when building graphs and charts, it can be argued that the main element of the spreadsheet is a selected area or several areas on different sheets or in different attached files, from which the values ​​​​of variables and dependent calculation results will be taken.

Applying filters

In some cases, it may be necessary to apply special filters that are set on one or more columns. At its simplest, this helps you search for data, text, or value in an entire column. If there is a match, all found results will be shown separately.

If there are dependencies, the filtered data in one column will show the rest of the values ​​in other columns and rows. But this is the simplest example, because each custom filter has its own submenu with certain search criteria or a specific setting.

Conclusion

Of course, it is simply impossible to consider all the possibilities of the MS Excel software package in one article. At least here you can understand which elements are the main ones in the tables, based on each specific case or situation, as well as at least a little understanding, so to speak, with the basics of working in this unique program. But for its full development will have to work hard. Often, the developers themselves do not always know what their brainchild is capable of.

Cell- an indivisible element of the table that contains data.

The structure of a cell in an Excel spreadsheet has several levels:

1. The top level is the image of the contents of the cell that is displayed on the screen. In it, we see formatted values, such as text, numbers, or the results of calculating formulas.

2. The second level contains hidden formatting. It contains service information about the contents of the cell, which is not displayed in any way, but is used in Excel when working.

3. The third level contains the formula. Depending on which options are set, you can see either the value calculated by the formula or the formula itself.

4. The fourth level contains the name of the cell.

5. The fifth level contains a commentary.

cell address- is determined by the column name and row number that form the intersection (for example, A1).

Cell block- a group of consecutive cells. A block of cells may consist of a single cell, row, column, sequence of row and column parts (for example, B2:C5).

Worksheet- contains a separate spreadsheet.

Workbook- set of worksheets.

2. Data types

Character (text) data are descriptive. May include letters, numbers, punctuation, etc. To explicitly indicate character data, put an apostrophe in front " (for example, entry "132 - the spreadsheet will be treated as text).

If the entered text is longer than the column size, it will be shown in the following columns, if they are empty. Otherwise, the text will be cut off by the border of the next column.

Numeric data contain only numerical values. If the input number is larger than the cell size, then it not displayed in a neighboring empty cell, and it is automatically converted into an exponential form (1.6E + 5) or the cell is clogged with signs - # .

The decimal separator is a comma (12.1999).

Formula contains a record that may include arithmetic, logical and other operations performed with the data of other cells (=A1+B1). The formula entry always starts with a sign equals =.

Function- a small subroutine that calculates data using several formulas (for example, the AVERAGE(B2:D5) function first calculates the sum of the data in cells B2:D5, and then divides the resulting sum by the total number of cells). A function entry always starts with a character @ .

Dates- numerical data representing days, months, years (for example, 12.1999 - December 1999).

3. Cell Format

To format cells, you need to run the command Format - cells. The result of your actions will be the appearance of a dialog box:

Rice. 1. Dialog box Cell Format.

Bookmark Number allows you to select the following formats:

General- used to display text and numeric values ​​of an arbitrary type.

Numerical- displays numbers in normal form with the given precision (ie the number of digits after the decimal point is set).

Monetary- displays numbers in monetary form, i.e. indicating the monetary unit (125.56 rubles).

Financial- used to align monetary values ​​by the separator of the integer and fractional parts.

date of- Displays numeric values ​​in date format. Those. if you enter the number 366, then in date format it will be 12/30/1900. date can be displayed

in numerical form (12.04.99),

indicating the month and year (Apr 99),

· indicating the day, month, year and time (12.04.99 14:40).

Time- Displays numeric values ​​in time format. The abbreviation can be used when indicating the time. PM, which means that the time is after lunch (5:00 PM - 17:00).

Percentage- Displays numbers as a percentage. When entering percentages, keep in mind that percentages are defined as a fraction of a unit. Those. in order to enter 85%, you must enter the number 0.85.

Fractional- Displays non-integer numbers as fractions. Those. if you enter the number 0.25, it will be displayed as 1/4.

Exponential- displays numbers in exponential form. Those. all numbers are displayed in the format:

where M is a number with an integer part in the form of one digit;

E - decimal exponent pointer,

P is the order of the exponent.

For example, the number 245 would be displayed as 2.45E+02

Text- displays numbers in text format. Actions cannot be performed on such numbers.

Additional- designed to work with addresses, phone numbers, etc.

4. Formulas and functions

4.1 Using formulas

Distinguish between arithmetic and logical formulas. IN arithmetic formulas use the following operators:

+ - addition;

- - subtraction;

* - multiplication;

/ - division;

^ - exponentiation

Comparison operators are used in logical formulas:

= - equals;

<> - not equal;

< - less;

> - more;

<= - less than or equal;

>= - more or equal.

4.2 Using functions

The spreadsheet contains the following kinds of functions:

· mathematical- perform complex mathematical operations (calculation of algorithms, trigonometric functions, etc.);

· statistical- perform operations to calculate the parameters of random variables or their distributions;

· text- perform operations on text strings, for example, calculate the length of a string, perform text replacement operations;

· brain teaser- are used to build logical expressions;

· financial- are used in complex financial calculations, for example, to determine the amount of monthly payments to repay a loan;

· date and time- are used for operations with dates and times, for example, find the number of days between two dates;

· working with the database- apply if the table is used as a database;

· checking properties and values- are used to check the data entered in the cells.

4.3 Relative and absolute addressing

Relative addressing is set for cells in formulas and functions by default and is a normal cell address, for example, B2. If relative cell addresses are used in a formula or function, then when copying such a formula to other cells, the cell addresses will automatically change:

This addressing is convenient if you need to perform the same type of operation for several consecutive cells. However, relative addressing can be inconvenient if the values ​​are located only in specific cells. Then, when copying, it is not required that the cell addresses change automatically. To do this, formulas use absolute cell addressing.

Absolute address cells - this is an unchanging, in the process of copying or transferring the formula, the address of the cell. To specify an absolute address, enter the symbol $ . There are two types of absolute addressing: full and partial:

· Full absolute addressing is indicated, if during copying or moving, the address of the cell should not change completely. To do this, the $ symbol is placed before the column name and row number $A$1+$B$1.

Partial absolute addressing is specified if only the row number or column name A$1+B$1 does not change during copying and moving

Note. If produced copying formulas and cell addresses are relative, then the addresses will automatically change. If produced transfer formulas and cell addresses are relative, then the automatic change of addresses does not occur. However, if not only the formula is transferred, but also the data for the calculation, then the addresses are automatically changed.

5. General principles of operation

5.1 Entering and editing data

For input data, you must place the cursor on the desired cell and enter data from the keyboard. Data entry ends with a keystroke .

For editing the contents of the cell, you must place the cursor on the required cell and press the key or double-click. Data editing is also ended by pressing the key .

5.2 Charting

In an Excel spreadsheet, you can build the following main types of charts:

· Pie chart- used for graphical interpretation one variable. The values ​​of this variable are represented in the diagram by circle sectors. It can be located on the plane and in space.

· Schedule- depicts each variable as a broken line or plane (in space).

· bar graph- Displays each variable as a column. It can be located on the plane and in space.

· Ruled- similar to the histogram, but located vertically. It can be located on the plane and in space.

· with areas- depicts each variable as an area.

· Petal- Uses as many axes for plotting as there are data values.

For charting you need to do the following:

1. Select data. If the data is not in adjacent columns, then you can use the key to select .

2. Run command Insert - Diagram.

3. Select the type of chart and the type of graph.

4. If necessary, change the range of cells with data.

5. Enter the chart attributes (name of the axes and chart itself, legend, etc.).

6. Specify the placement of the diagram (on the same sheet or on a new one).

7. Press the button Ready.

5.3 Working with macros

Macro- a recorded sequence of actions or commands, stored under a specific name for repeated use. Macros are an effective means of automating time-consuming, repetitive tasks.

A macro can be created manually using Visual Basic programming language commands, or recorded using a macro recorder that converts a sequence of user actions into programming language code.

To create a macro, follow these steps:

1. Run command Tools - Macro - Start Recording.

2. Specify the name of the macro and set the keyboard shortcut that calls the macro.

3. Perform the actions that the macro should perform.

4. To end the recording, press the button Stop Recording on the toolbar.

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 source data changes. The fields of the table, in which the source data are located, are usually called independent fields. The fields where the results of calculations are recorded are called dependent or calculated fields. Each cell of the spreadsheet has its own address, which is formed from the column name and row number where it is located. Rows are numbered, and columns are denoted by letters of the Latin alphabet.

Spreadsheets are large. For example, the Excel spreadsheet most commonly used 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 moves, and with its help you can look at any place in the table.

Consider how a table could 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 an excursion, 4 of them are going 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 either to increase the cost of lunch, or to buy tickets to the circus more expensive, but for better seats. There are many other options for the distribution of the budget allotted for the tour, and all of them can be easily calculated using a spreadsheet.

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

The formulas used to calculate the values ​​of dependent fields include numbers, table cell addresses, and operation signs. For example, the formula that calculates the value of the dependent field in the third row looks like this: ВЗ*СЗ - multiply the number in cell ВЗ 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, pre-prepared for certain calculations and entered into the computer's memory.

Most spreadsheet processors allow you to sort (sort) the table according to some criteria, such as descending. At the same time, in our table, the first place (in the second line) will remain the cost of buying tickets (the maximum value is 360 rubles), then (in the third line) there will be the cost of visiting the circus (100 rubles), then the cost of lunch (60 p.) and finally in the last line - the cost of visiting the museum (the minimum value is 8 p.).

Spreadsheets also provide a graphical mode of operation, which allows the graphical representation (in the form of graphs, charts) of 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.