Calculations using formulas in Excel. Working with formulas in Excel detailed analysis. Algorithm for performing calculations using the fx button

• "less than or equal" sign - "<=»;

• "not equal" sign - "<>».

Text concatenation operator

The special character “&” (ampersand) is used for this purpose. Using it, you can connect different fragments into one whole - the same principle as with the “CONNECT” function. Here are some examples:

1. If you want to merge text in cells, then you need to use the following code.

1. In order to insert any symbol or letter between them, you need to use the following construction.

1. You can merge not only cells, but also ordinary symbols.

Any text other than links must be quoted. Otherwise the formula will generate an error.

Please note that the quotes used are exactly the same as in the screenshot.

The following operators can be used to define links:

• in order to create a simple link to the desired range of cells, just indicate the first and last cell of this area, and between them the symbol “:”;
• to combine links the sign “;” is used;
• if it is necessary to determine cells that are at the intersection of several ranges, then a “space” is placed between the links. In this case, the value of cell “C7” will be displayed.

Because only it falls under the definition of “intersection of sets.” This is the name given to this operator (space).

Using links

While working in the Excel editor, you can use various types of links. However, most novice users know how to use only the simplest of them. We will teach you how to correctly enter links of all formats.

Simple links A1

As a rule, this type is used most often, since they are much more convenient to compose than others.

• columns – from A to XFD (no more than 16384);
• lines – from 1 to 1048576.

Here are some examples:

• the cell at the intersection of row 5 and column B is “B5”;
• the range of cells in column B starting from line 5 to line 25 is “B5:B25”;
• the range of cells in row 5 starting from column B to F is “B5:F5”;
• all cells in row 10 are “10:10”;
• all cells in rows 10 to 15 are “10:15”;
• all cells in column B are “B:B”;
• all cells in columns B to K are “B:K”;
• The range of cells B2 to F5 is “B2-F5”.

Sometimes formulas use information from other sheets. It works as follows.

The second sheet contains the following information.

If there is a space in the name of the sheet, then it must be indicated in the formula in single quotes (apostrophes).

Absolute and relative links

Excel editor works with three types of links:

• absolute;
• relative;
• mixed.

Let's take a closer look at them.

All the previously mentioned examples refer to relative cell addresses. This type is the most popular. The main practical advantage is that the editor will change the references to a different value during migration. In accordance with where exactly you copied this formula. For the calculation, the number of cells between the old and new positions will be taken into account.

Imagine that you need to stretch this formula across an entire column or row. You will not manually change letters and numbers in cell addresses. It works as follows.

1. Let's enter a formula to calculate the sum of the first column.

1. Press the hotkeys Ctrl + C. In order to transfer the formula to an adjacent cell, you need to go there and press Ctrl + V.

If the table is very large, it is better to click on the lower right corner and, without releasing your finger, drag the pointer to the end. If there is little data, then copying using hot keys is much faster.

1. Now look at the new formulas. The column index changed automatically.

If you want all links to be preserved when transferring formulas (that is, so that they do not change automatically), you need to use absolute addresses. They are indicated as "$B$2".

As a result, we see that no changes have occurred. All columns display the same number.

This type of address is used when it is necessary to fix only a column or row, and not all at the same time. The following constructions can be used:

• $D1, $F5, $G3 – for fixing columns;
• D$1, F$5, G$3 – for fixing rows.

Work with such formulas only when necessary. For example, if you need to work with one constant row of data, but only change the columns. And most importantly, if you are going to calculate the result in different cells that are not located along the same line.

The fact is that when you copy the formula to another line, the numbers in the links will automatically change to the number of cells from the original value. If you use mixed addresses, then everything will remain in place. This is done as follows.

1. Let's use the following expression as an example.

1. Let's move this formula to another cell. Preferably not on the next or on another line. Now you see that the new expression contains the same line (4), but a different letter, since it was the only one that was relative.

3D links

The concept of “three-dimensional” includes those addresses in which a range of sheets is indicated. An example formula looks like this.

In this case, the result will correspond to the sum of all cells “A5” on all sheets, starting from 1 to 4. When composing such expressions, you must adhere to the following conditions:

• such references cannot be used in arrays;
• three-dimensional expressions are prohibited from being used where there is an intersection of cells (for example, the “space” operator);
• When creating formulas with 3D addresses, you can use the following functions: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNTA, COUNT, MIN, MAX, MINA, MAX, VARVE, PRODUCT, VARIANCE, VAR. and DISPA.

If you break these rules, you will see some kind of error.

R1C1 format links

This type of link differs from “A1” in that the number is assigned not only to rows, but also to columns. The developers decided to replace the regular view with this option for convenience in macros, but they can be used anywhere. Here are some examples of such addresses:

• R10C10 – absolute reference to the cell, which is located on the tenth line of the tenth column;
• R – absolute link to the current (in which the formula is indicated) link;
• R[-2] – a relative link to a line that is located two positions above this one;
• R[-3]C is a relative reference to a cell that is located three positions higher in the current column (where you decided to write the formula);
• RC is a relative reference to a cell that is located five cells to the right and five lines below the current one.

Use of names

Excel allows you to create your own unique names for naming ranges of cells, single cells, tables (regular and pivot), constants, and expressions. At the same time, for the editor there is no difference when working with formulas - he understands everything.

You can use names for multiplication, division, addition, subtraction, calculation of interest, coefficients, deviation, rounding, VAT, mortgage, loan, estimate, timesheets, various forms, discounts, salaries, length of service, annuity payment, working with VPR formulas , “VSD”, “INTERMEDIATE.RESULTS” and so on. That is, you can do whatever you want.

There is only one main condition - you must define this name in advance. Otherwise Excel will not know anything about it. This is done as follows.

1. Select a column.
2. Call the context menu.
3. Select "Assign a name".

1. Specify the desired name for this object. In this case, you must adhere to the following rules.

1. To save, click on the “OK” button.

In the same way, you can assign a name to a cell, text or number.

You can use the information in the table both using names and using regular links. This is what the standard version looks like.

And if you try to insert our name instead of the address “D4:D9”, you will see a hint. Just write a few characters and you will see what fits (from the name database) the most.

In our case, everything is simple - “column_3”. Imagine that you will have a large number of such names. You won't be able to remember everything by heart.

Using Functions

There are several ways to insert a function in Excel:

• manually;
• using the toolbar;
• using the Insert Function window.

Let's take a closer look at each method.

In this case, everything is simple - you use your hands, your own knowledge and skills to enter formulas in a special line or directly in a cell.

If you do not have working experience in this area, then it is better to use easier methods at first.

In this case it is necessary:

1. Go to the "Formulas" tab.
2. Click on any library.
3. Select the desired function.

1. Immediately after this, the Arguments and Functions window will appear with the function already selected. All you have to do is enter the arguments and save the formula using the “OK” button.

Substitution Wizard

You can apply it as follows:

1. Make any cell active.
2. Click on the “Fx” icon or use the keyboard shortcut SHIFT + F3.

1. Immediately after this, the “Insert Function” window will open.
2. Here you will see a large list of different features sorted by category. In addition, you can use the search if you cannot find the item you need.

All you have to do is type in some word that can describe what you want to do, and the editor will try to display all the suitable options.

1. Select a function from the list provided.
2. To continue, you need to click on the “OK” button.

1. You will then be asked to specify "Arguments and Functions". You can do this manually or simply select the desired range of cells.
2. In order to apply all the settings, you need to click on the “OK” button.

1. As a result of this, we will see the number 6, although this was already clear, since the preliminary result is displayed in the “Arguments and Functions” window. The data is recalculated instantly when any of the arguments changes.

Using Nested Functions

As an example, we will use formulas with logical conditions. To do this, we will need to add some kind of table.

Then follow the following instructions:

1. Click on the first cell. Call up the “Insert Function” window. Select the "If" function. To insert, click on “OK”.

1. Then you will need to create some kind of logical expression. It must be written in the first field. For example, you can add the values ​​of three cells in one row and check whether the sum is greater than 10. If “true”, indicate the text “Greater than 10”. For a false result – “Less than 10”. Then click “OK” to return to the workspace.

1. As a result, we see the following - the editor showed that the sum of the cells in the third line is less than 10. And this is correct. This means our code works.

1. Now you need to configure the following cells. In this case, our formula simply extends further. To do this, you first need to hover the cursor over the lower right corner of the cell. After the cursor changes, you need to left click and copy it to the very bottom.

1. As a result, the editor recalculates our expression for each line.

As you can see, the copying was quite successful because we used the relative links we talked about earlier. If you need to assign addresses to function arguments, then use absolute values.

You can do this in several ways: use the formula bar or a special wizard. In the first case, everything is simple - click in a special field and manually enter the necessary changes. But writing there is not entirely convenient.

The only thing you can do is make the input field larger. To do this, just click on the indicated icon or press the key combination Ctrl + Shift + U.

It's worth noting that this is the only way if you don't use functions in your formula.

If you use functions, everything becomes much simpler. To edit you must follow the following instructions:

1. Make the cell with the formula active. Click on the "Fx" icon.

1. After this, a window will appear in which you can change the function arguments you need in a very convenient way. In addition, here you can find out exactly what the result of recalculating the new expression will be.

1. To save the changes you have made, use the “OK” button.

To remove an expression, just do the following:

1. Click on any cell.

1. Click on the Delete or Backspace button. As a result, the cell will be empty.

You can achieve exactly the same result using the “Clear All” tool.

Possible errors when creating formulas in the Excel editor

Listed below are the most popular mistakes made by users:

• The expression uses a huge number of nestings. There should be no more than 64 of them;
• formulas indicate paths to external books without the full path;
• Opening and closing brackets are placed incorrectly. This is why in the editor, in the formula bar, all brackets are highlighted in a different color;

• the names of books and sheets are not placed in quotation marks;
• numbers are used in the wrong format. For example, if you need to enter $2000, you need to simply enter 2000 and select the appropriate cell format, since the $ symbol is used by the program for absolute references;

• Required function arguments are not specified. Note that optional arguments are enclosed in square brackets. Everything without them is necessary for the formula to work properly;

• The cell ranges are specified incorrectly. To do this, you must use the “:” (colon) operator.

Error codes when working with formulas

When working with a formula, you may see the following error options:

• #VALUE! – this error indicates that you are using the wrong data type. For example, you are trying to use text instead of a numeric value. Of course, Excel will not be able to calculate the sum between two phrases;
• #NAME? – such an error means that you made a typo in the spelling of the function name. Or are you trying to enter something that doesn’t exist. You can't do that. Besides this, the problem could be something else. If you are sure of the function name, then try looking at the formula more closely. Perhaps you forgot a parenthesis. In addition, you need to take into account that text fragments are indicated in quotation marks. If all else fails, try composing the expression again;
• #NUMBER! – displaying a message like this means that you have some problem with the arguments or the result of the formula. For example, the number turned out to be too huge or, on the contrary, small;
• #DIV/0! – this error means that you are trying to write an expression in which division by zero occurs. Excel can't override the rules of math. Therefore, such actions are also prohibited here;
• #N/A! – the editor can show this message if some value is not available. For example, if you use the SEARCH, SEARCH, MATCH functions, and Excel does not find the fragment you are looking for. Or there is no data at all and the formula has nothing to work with;
• If you are trying to calculate something and Excel writes the word #REF!, then the function argument is using the wrong range of cells;
• #EMPTY! – this error appears if you have an inconsistent formula with overlapping ranges. More precisely, if in reality there are no such cells (which happen to be at the intersection of two ranges). Quite often this error occurs by accident. It is enough to leave one space in the argument, and the editor will perceive it as a special operator (we talked about it earlier).

When you edit the formula (the cells are highlighted), you will see that they do not actually intersect.

Sometimes you can see a lot of # characters that completely fill the width of the cell. In fact, there is no error here. This means that you are working with numbers that do not fit in a given cell.

To see the value contained there, just resize the column.

In addition, you can use cell formatting. To do this you need to follow a few simple steps:

1. Call the context menu. Select Format Cells.

1. Specify the type as "General". To continue, use the “OK” button.

Thanks to this, the Excel editor will be able to convert this number into another format that fits in this column.

Examples of using formulas

The Microsoft Excel editor allows you to process information in any way convenient for you. There are all the necessary conditions and opportunities for this. Let's look at a few examples of formulas by category. This will make it easier for you to understand.

In order to evaluate the mathematical capabilities of Excel, you need to perform the following steps.

1. Create a table with some conditional data.

1. To calculate the amount, enter the following formula. If you want to add just one value, you can use the addition operator (“+”).

1. Oddly enough, in the Excel editor you cannot take away using functions. For subtraction, the usual “-” operator is used. In this case, the code will be as follows.

1. In order to determine how much the first number is from the second as a percentage, you need to use this simple construction. If you want to subtract several values, you will have to enter a “minus” for each cell.

Note that the percent symbol is placed at the end, not at the beginning. In addition, when working with percentages, you do not need to additionally multiply by 100. This happens automatically.

1. Use the following formula to determine the average.

1. As a result of the expressions described above, you will see the following result.

1. To do this, let's increase our table.

1. For example, let's add those cells that have a value greater than three.

1. Excel can add based on several conditions at once. You can calculate the sum of cells in the first column whose value is greater than 2 and less than 6. And the same formula can be set for the second column.

1. You can also count the number of elements that satisfy some condition. For example, let Excel count how many numbers we have greater than 3.

1. The result of all formulas will be as follows.

Mathematical functions and graphs

Using Excel you can calculate various functions and build graphs based on them, and then conduct graphical analysis. As a rule, such techniques are used in presentations.

As an example, let's try to build graphs for an exponent and some equation. The instructions will be as follows:

1. Let's create a table. In the first column we will have the initial number “X”, in the second - the “EXP” function, in the third - the specified ratio. It would be possible to make a quadratic expression, but then the resulting value would practically disappear against the background of the exponential on the graph.

1. In order to convert the value of "X", you need to specify the following formulas.

1. We duplicate these expressions until the very end. As a result, we get the following result.

1. Select the entire table. Go to the “Insert” tab. Click on the “Recommended Charts” tool.

1. Select the “Line” type. To continue, click on “OK”.

1. The result turned out quite beautiful and neat.

As we said earlier, the growth of the exponent occurs much faster than that of the ordinary cubic equation.

Any function or mathematical expression can be represented graphically in this way.

Everything described above is suitable for modern programs of 2007, 2010, 2013 and 2016. The old Excel editor is significantly inferior in terms of capabilities, number of functions and tools. If you open the official help from Microsoft, you will see that they additionally indicate in which version of the program this function appeared.

In all other respects, everything looks almost exactly the same. As an example, let's calculate the sum of several cells. To do this you need:

1. Provide some data for calculation. Click on any cell. Click on the "Fx" icon.

1. Select the “Mathematical” category. Find the “SUM” function and click on “OK”.

1. We indicate the data in the required range. In order to display the result, you need to click on “OK”.

1. You can try to recalculate in any other editor. The process will happen exactly the same.

Conclusion

In this tutorial, we talked about everything related to formulas in the Excel editor, from the simplest to the very complex. Each section was accompanied by detailed examples and explanations. This is done to ensure that the information is accessible even to complete dummies.

If something doesn’t work out for you, it means you’re making a mistake somewhere. You may have misspelled expressions or incorrect cell references. The main thing is to understand that everything needs to be driven in very carefully and carefully. Moreover, all functions are not in English, but in Russian.

In addition, it is important to remember that formulas must begin with the “=” (equals) symbol. Many novice users forget about this.

Examples file

To make it easier for you to understand the previously described formulas, we have prepared a special demo file in which all the above examples were compiled. You can do it from our website completely free of charge. If during training you use a ready-made table with formulas based on the completed data, you will achieve results much faster.

Video instruction

If our description did not help you, try watching the video attached below, which explains the main points in more detail. You may be doing everything right, but you're missing something. With the help of this video you should understand all the problems. We hope that lessons like this have helped you. Check us out more often.

Over the past decade, a computer has become an indispensable tool in accounting. At the same time, its application is diverse. First of all, this is, of course, the use of an accounting program. To date, quite a lot has been developed software, both specialized (“1C”, “Info-Accountant”, “BEST”, etc.) and universal, like Microsoft Office. At work, and in everyday life, you often have to do a lot of different calculations, maintain multi-line tables with numerical and text information, performing all sorts of calculations with the data, printing options. To solve a number of economic and financial problems, it is advisable to use the numerous capabilities of spreadsheets. In this regard, let us consider the computational functions of MS Excel.

Source: Magazine "Accountant and Computer" No. 4 2004

Like any other spreadsheet, MS Excel is designed primarily to automate calculations that are usually made on a piece of paper or using a calculator. In practice in professional activity There are quite complex calculations. That's why we'll talk more about how Excel helps us automate their execution.

To indicate any action, such as addition, subtraction, etc., operators are used in formulas.

All operators are divided into several groups (see table).

Arithmetic operators are used to represent basic mathematical operations on numbers. The result of execution arithmetic operation is always a number. Comparison operators are used to indicate operations that compare two numbers. The result of the comparison operation is boolean value TRUE or FALSE.

Excel uses formulas to perform calculations. Using formulas, you can, for example, add, multiply and compare table data, i.e. formulas should be used when you need to enter (automatically calculate) a calculated value into a sheet cell. Entering a formula begins with the symbol “=” (equal sign). It is this sign that distinguishes entering formulas from entering text or a simple numeric value.

When entering formulas, you can use regular numeric and text values. As a reminder, numeric values ​​can only contain the numbers 0 through 9 and special characters: (plus, minus, slash, parentheses, period, comma, percent and dollar signs). Text values ​​can contain any characters. It should be noted that the text expressions used in the formulas must be in double quotes, for example “constant1”. In addition, in formulas you can use cell references (including in the form of names) and numerous functions that are connected to each other by operators.

References are cell addresses or cell ranges included in a formula. Cell references are specified in the usual way, i.e. in the form A1, B1, C1. For example, in order to get the sum of cells A1 and A2 in cell A3, it is enough to enter the formula =A1+A2 (Fig. 1).

When entering a formula, cell references can be typed character by character directly from the Latin keyboard, but it is often much easier to specify them using the mouse. For example, to enter the formula =A1+B2, you need to do the following:

Select the cell in which you want to enter the formula;

Start entering the formula by pressing the “=” (equals) key;

Click on cell A1;

Enter the symbol “+”;

Click on cell B2;

Finish entering the formula by pressing the Enter key.

A range of cells represents a certain rectangular area of ​​the worksheet and is uniquely determined by the addresses of cells located in opposite corners of the range. Separated by a “:” (colon), these two coordinates constitute the range address. For example, to get the sum of the cells in the range C3:D7, use the formula =SUM(C3:D7).

In the special case when the range consists entirely of several columns, for example from B to D, its address is written in the form B: D. Similarly, if the range consists entirely of lines 6 to 15, then it has the address 6:15. In addition, when writing formulas, you can use the union of several ranges or cells, separating them with the “;” symbol. (semicolon), for example C3:D7; E5;F3:G7.

Editing an already entered formula can be done in several ways:

Double-click the left mouse button on a cell to adjust the formula directly in that cell;

Select a cell and press the F2 key (Fig. 2);

Select a cell by moving the cursor to the formula bar and clicking the left mouse button.

As a result, the program will switch to editing mode, during which you can make the necessary changes to the formula.

When filling out a table, it is customary to set calculation formulas only for the “first” (initial) row or “first” (initial) column, and fill in the rest of the table with formulas using the copy or fill modes. An excellent result is obtained by using autocopying formulas using an autofill.

Let us remind you how to correctly implement the copy mode. There may be various options (and problems too).

It is necessary to keep in mind that when copying, addresses are transposed. When you copy a formula from one cell to another, Excel responds differently to formulas with relative and absolute references. For relative ones, Excel transposes addresses by default, depending on the position of the cell into which the formula is copied.

For example, you need to add row by row the values ​​of columns A and B (Fig. 8) and place the result in column C. If you copy the formula =A2+B2 from cell C2 to cell C3* (and further down C), then Excel itself will convert formula addresses respectively as =A3+B3 (etc.). But if you need to place the formula, say, from C2 in cell D4, then the formula will already look like =B4+C4 (instead of the required =A4+B4), and accordingly the result of the calculations will be incorrect! In other words, pay special attention to the copying process and, if necessary, manually adjust the formulas. By the way, the copying itself from C2 to C3 is done as follows:

1) select cell C2 from which you need to copy the formula;

2) click the “Copy” button on the toolbar, or the Ctrl+C keys, or select “Edit ® Copy” from the menu;

3) select cell C3 into which we will copy the formula;

4) press the “Paste” button on the toolbar, or the Ctrl+V keys, or through the “Edit ® Paste” menu and press Enter.

Let's look at the autocomplete mode. If you need to move (copy) a formula to several cells (for example, in C3:C5) down a column, then it is more convenient and easier to do this: repeat the previous sequence of actions up to step 3 of selecting cell C3, then move the mouse cursor to the starting cell of the range ( C3), press the left mouse button and, without releasing it, drag it down to the required last cell of the range. In our case, this is cell C5. Then release the left mouse button, move the cursor to the “Insert” button on the toolbar and press it, and then Enter. Excel itself converts the formula addresses in the range we have selected to the corresponding row addresses.

Sometimes there is a need to copy only the numeric value of a cell (range of cells). To do this you need to do the following:

1) select the cell (range) from which you want to copy data;

2) click the “Copy” button on the toolbar or select “Edit ® Copy” from the menu;

3) select the cell (top left of the new range) into which the data will be copied;

4) select “Edit ®” from the menu Special insert” and press Enter.

When copying formulas, the computer immediately performs calculations on them, thus producing a quick and clear result.

:: Functions in Excel

Functions in Excel greatly facilitate calculations and interaction with spreadsheets. The most commonly used function is to sum cell values. Let us recall that it is called SUM, and the arguments are ranges of numbers to be summed.

In a table, you often want to calculate the total for a column or row. To do this, Excel offers an automatic sum function, performed by clicking the (“AutoSum”) button on the toolbar.

If we enter a series of numbers, place the cursor under them and execute double click mouse over the auto-sum icon, the numbers will be added (Fig. 3).

IN latest version The program has a list button to the right of the auto-sum icon, which allows you to perform a number of frequently used operations instead of summation (Fig. 4).

:: Automatic calculations

Some calculations can be done without entering formulas at all. Let's make a small lyrical digression, which may be useful for many users. As you know, a spreadsheet, thanks to its convenient interface and computing capabilities, can completely replace calculations using a calculator. However, practice shows that a significant portion of people who actively use Excel in their activities keep a calculator on their desktop to perform intermediate calculations.

Indeed, in order to perform the operation of summing two or more cells in Excel to obtain a temporary result, you must perform at least two extra operations - find the place in the current table where the total will be located, and activate the auto-sum operation. And only after that you can select those cells whose values ​​need to be summed.

That is why, starting with Excel 7.0, an auto-calculation function was built into the spreadsheet. Now Excel spreadsheets have the ability to quickly perform some mathematical operations automatically.

To see the result of the intermediate summation, simply select the required cells. This result is also reflected in the status bar at the bottom of the screen. If the amount does not appear there, move the cursor to the status bar at the bottom of the frame, click right click mouse and in the drop-down menu next to the Amount line, click the left mouse button. Moreover, in this menu on the status bar you can select different options for the calculated results: sum, arithmetic average, number of elements or the minimum value in the selected range.

For example, let's use this function to calculate the sum of values ​​for the range B3:B9. Select the numbers in the range of cells B3:B9. Please note that in the status bar located at the bottom of the working window, the inscription Sum=X has appeared, where X is a number equal to the sum of the selected numbers in the range (Fig. 5).

As you can see, the results of the usual calculation using the formula in cell B10 and the automatic calculation are the same.

:: Function Wizard

In addition to the sum function, Excel allows you to process data using other functions. Any of them can be entered directly in the formula bar using the keyboard, however, to simplify entry and reduce the number of errors, Excel has a “Function Wizard” (Fig. 6).

You can call the “Wizards” dialog box using the “Insert ® Function” command, the Shift+F3 key combination, or the button on the standard toolbar.

The first dialog of the “Function Wizard” is organized thematically. Having selected a category, in the lower window we will see a list of function names contained in this group. For example, you can find the SUM() function in the “Mathematical” group, and in the “Date and Time” group there are the functions DAY(), MONTH(), YEAR(), TODAY().

In addition, to speed up the selection Excel functions“remembers” the names of 10 recently used functions in the corresponding group. Please note that at the bottom of the window a brief reference about the purpose of the function and its arguments is displayed. If you click the Help button at the bottom of the dialog box, Excel opens the Help section.

Let's assume that it is necessary to calculate the depreciation of property. In this case, you should enter the word “depreciation” in the function search area. The program will select all functions for depreciation (Fig. 7).

After filling in the appropriate fields of the function, the property depreciation will be calculated.

Often you need to add numbers that satisfy some condition. In this case, you should use the SUMIF function. Let's consider specific example. Let’s say you need to calculate the amount of commission if the value of the property exceeds 75,000 rubles. To do this, we use the data from the table of the dependence of commissions on the value of the property (Fig. 8).

Our actions in this case are as follows. Place the cursor in cell B6, use the button to launch the “Function Wizard”, in the “Mathematical” category select the SUMIF function, set the parameters as in Fig. 9.

Please note that we select the range of cells A2:A6 (property value) as the range for checking the condition, and B2:B6 (commissions) as the summation range, and the condition looks like (>75000). The result of our calculation will be 27,000 rubles.

:: Let's give the cell a name

For ease of use, Excel has the ability to assign names to individual cells or ranges, which can then be used in formulas just like regular addresses. To quickly name a cell, select it, position the pointer in the name box on the left side of the formula bar, click and enter a name.

When assigning names, you must remember that they can consist of letters (including the Russian alphabet), numbers, dots and underscores. The first character in the name must be a letter or an underscore. Names cannot have the same appearance as cell references, such as Z$100 or R1C1. A name can contain more than one word, but spaces are not allowed. Underscores and periods can be used as word separators, for example Sales_Tax or First.Quarter. The name can contain up to 255 characters. In this case, uppercase and lowercase letters are perceived equally.

To insert a name into a formula, you can use the command “Insert ® Name ® Insert” by selecting desired name in the list of names.

It's useful to remember that names in Excel are used as absolute references, that is, they are a type of absolute addressing, which is convenient when copying formulas.

Names in Excel can be defined not only for individual cells, but also for ranges (including non-adjacent ones). To assign a name, simply highlight the range and then enter the name in the name field. In addition, to specify the names of ranges containing headers, it is convenient to use special team“New” in the “Insert ® Name” menu.

To delete a name, select it in the list and click the “Delete” button.

When you create a formula that references data from a worksheet, you can use row and column headings to identify the data. For example, if you assign the column values ​​the name of the column name (Fig. 10),

then to calculate the total amount for the “Commission” column, use the formula =SUM(Commission) (Fig. 11).

:: Additional features Excel - Templates

MS Excel includes a set of templates - Excel tables, which are intended for analyzing the economic activities of an enterprise, drawing up an invoice, work order, and even for accounting for a personal budget. They can be used to automate solutions to frequently encountered problems. Thus, you can create documents based on the templates “Advance report”, “Invoice”, “Order”, which contain forms of documents used in business activities. These forms are in their own way appearance and when printed do not differ from the standard ones, and the only thing you need to do to receive the document is to fill out its fields.

To create a document based on a template, execute the “Create” command from the “File” menu, then select the required template on the “Solutions” tab (Fig. 12).

Templates are copied to disk during a normal Excel installation. If templates are not displayed in the “Create Document” dialog box, run the program Excel installation and install the templates. For detailed information about installing templates, see the “Installing Microsoft Office components” topic in Excel Help.

For example, to create a number of financial documents, select the “Financial Templates” template (Fig. 13).

This group of templates contains forms for the following documents:

Travel certificate;
. advance report;
. payment order;
. invoice;
. invoice;
. power of attorney;
. income and expenditure orders;
. payments for telephone and electricity.

Select the form you need to fill out, then enter all the necessary details and print it. If desired, the document can be saved as a regular Excel table.

Excel allows the user to create document templates themselves, as well as edit existing ones.

However, document forms may change over time, and then the existing template will become unusable. In addition, in the templates that come with Excel, it would be a good idea to enter in advance such permanent information as information about your organization and manager. Finally, you may need to create your own template: for example, the planning department will most likely need templates for preparing estimates and calculations, and the accounting department will most likely need an invoice form with your organization’s logo.

For such cases in Excel, as in many other programs that work with electronic documents, it is possible to create and edit templates for frequently used documents. An Excel template is a special workbook that you can use as a template to create other workbooks of the same type. Unlike the usual Excel workbooks, having the extension *.xls, the template file has the extension *.xlt.

When creating a document based on a template, Excel automatically creates a working copy of it with the *.xls extension, adding a serial number to the end of the document name. The original template remains intact and can be subsequently reused.

To automatically enter the date, you can use the following method: enter the TODAY function in the date cell, after which it will display the current day of the month, month and year, respectively.

Of course, you can use all the considered actions on templates when working with regular Excel workbooks.

Good afternoon.

Once upon a time, writing a formula in Excel on my own was something incredible for me. And even despite the fact that I often had to work in this program, I didn’t type anything except text...

As it turned out, most of the formulas are not complicated and can be easily worked with, even by a novice computer user. In this article, I would like to reveal the most necessary formulas with which you most often have to work...

So, let's begin…

1. Basic operations and basics. Excel basics training.

All actions in the article will be shown in Excel versions 2007

After launch Excel programs- a window appears with many cells - our table. main feature The program is that it can calculate (like a calculator) your formulas that you write. By the way, you can add a formula to each cell!

The formula must begin with the “=” sign. This required condition. Next, you write what you need to calculate: for example, “=2+3” (without quotes) and press the Enter key - as a result, you will see that the result “5” appears in the cell. See screenshot below.

Important! Despite the fact that the number “5” is written in cell A1, it is calculated using the formula (“=2+3”). If you simply write “5” in the next cell, then when you hover the cursor over this cell, in the formula editor (the line at the top, Fx) - you will see the prime number “5”.

Now imagine that in a cell you can write not just the value 2+3, but the numbers of the cells whose values ​​​​need to be added. Let’s say “=B2+C2”.

Naturally, there must be some numbers in B2 and C2, otherwise Excel will show us a result equal to 0 in cell A1.

And one more important note...

When you copy a cell that has a formula in it, for example A1 - and paste it into another cell - it is not the value “5” that is copied, but the formula itself!

Moreover, the formula will change in direct proportion: i.e. if A1 is copied to A2, then the formula in cell A2 will be “=B3+C3”. Excel automatically changes your formula: if A1=B2+C2, then it is logical that A2=B3+C3 (all numbers increased by 1).

The result, by the way, is in A2=0, because Cells B3 and C3 are not specified, which means they are equal to 0.

This way, you can write the formula once, and then copy it to all the cells of the desired column - and Excel itself will perform the calculation in each row of your table!

If you don't want B2 and C2 to change when copying and have always been linked to these cells, then simply add a “$” sign to them. Example below.

This way, no matter where you copy cell A1, it will always refer to the linked cells.

2. Adding values ​​in rows (SUM and SUMIFS formula)

You can, of course, add each cell, making the formula A1+A2+A3, etc. But to avoid this hassle, there is a special formula in Excel that will add up all the values ​​in the cells that you select!

Let's take a simple example. There are several items of goods in stock, and we know how many of each product separately in kg. In stock. Let's try to calculate how much is in kg. cargo in warehouse.

To do this, go to the cell in which the result will be shown and write the formula: “=SUM(C2:C5)”. See screenshot below.

As a result, all cells in the selected range will be summed, and you will see the result.

2.1. Addition with condition (with conditions)

Now let’s imagine that we have certain conditions, i.e. You don’t need to add up all the values ​​in the cells (Kg, in stock), but only certain ones, say, with a price (1 kg) less than 100.

There is a wonderful formula for this “ SUMIFS". Immediately an example, and then an explanation of each symbol in the formula.

=SUMIFS(C2:C5,B2:B5,“<100» ) , Where:

C2:C5 - the column (those cells) that will be summed up;

B2:B5- column against which the condition will be checked (i.e. price, for example, less than 100);

«<100» - the condition itself, please note that the condition is written in quotes.

There is nothing complicated in this formula, the main thing is to maintain proportionality: C2:C5;B2:B5 - correct; C2:C6;B2:B5 - incorrect. Those. The sum range and the condition range must be proportional, otherwise the formula will return an error.

Important! There can be many conditions for the amount, i.e. You can check not by the 1st column, but by 10 at once, setting many conditions.

3. Counting the number of rows that satisfy the conditions (COUNTIFS formula)

A fairly common task: to count not the sum of values ​​in cells, but the number of such cells that satisfy certain conditions. Sometimes there are a lot of conditions.

So, let's begin.

To count goods in the required cell, write the following formula (see above):

=COUNTIFS(B2:B5,“>90”), Where:

B2:B5- the range by which they will check, according to the condition we set;

">90"- the condition itself is enclosed in quotation marks.

Now let’s try to complicate our example a little and add an invoice for one more condition: with a price greater than 90 + quantity in stock less than 20 kg.

The formula takes the form:

=COUNTIFS(B2:B6,”>90″,C2:C6 ;“<20» )

Here everything remains the same, except for one more condition ( C2:C6;"<20″ ). By the way, there can be a lot of such conditions!

It is clear that no one will write such formulas for such a small table, but for a table of several hundred rows this is a completely different matter. As an example, this table is more than clear.

4. Search and substitution of values ​​from one table to another (VLOOKUP formula)

Let's imagine that we received a new table with new price tags for the goods. It’s good if there are 10-20 items, you can “refill” them all manually. What if there are hundreds of such names? It would be much faster if Excel independently found matching names from one table to another, and then copied the new price tags into our old table.

For such a problem, the formula is used VLOOKUP. At one time I was playing tricks with logical formulas “IF” until I came across this wonderful thing!

So, let's begin…

Here is our example + a new table with price tags. Now we need to automatically substitute new price tags from the new table into the old one (new price tags are red).

Place the cursor in cell B2 - i.e. to the first cell where we need to change the price tag automatically. Next, we write the formula as in the screenshot below (after the screenshot there will be a detailed explanation of it).

=VLOOKUP(A2 ;$D$2:$E$5 ;2 ), Where

A2- the value that we will look for in order to take a new price tag. In our case, we are looking for the word “apples” in the new table.

$D$2:$E$5- select our new table completely (D2:E5, selection goes from the upper left corner to the lower right diagonally), i.e. where the search will take place. The “$” sign in this formula is necessary so that when copying this formula to other cells - D2:E5 does not change!

Important! The word "apples" will be searched only in the first column of your selected table; in this example, "apples" will be searched in column D.

2 - When the word “apples” is found, the function must know from which column of the selected table (D2:E5) to copy the desired value. In our example, copy from column 2 (E), because in the first column (D) we performed the search. If your selected table for search consists of 10 columns, then the first column is searched, and from columns 2 to 10 you can select a number to copy.

To formula =VLOOKUP(A2,$D$2:$E$5,2) substituted new values ​​for other product names - just copy it to other cells of the column with product price tags (in our example, copy to cells B3:B5). The formula will automatically search and copy the value from the column you need in the new table.

5. Conclusion

In this article we looked at the basics of working with Excel, starting with how to start writing formulas. They gave examples of the most common formulas that most people who work in Excel often have to work with.

I hope that the analyzed examples will be useful to someone and will help speed up their work. Happy experimenting!

What formulas do you use? Is it possible to somehow simplify the formulas given in the article? For example, on weak computers, when changing some values ​​in large tables where calculations are made automatically, the computer freezes for a couple of seconds, recalculating and showing new results...

A formula is a mathematical expression that is created to calculate a result and that can depend on the contents of other cells. A formula in a cell can contain data, links to other cells, and also an indication of the actions that need to be performed.

Using cell references allows formula results to be recalculated when the contents of the cells included in the formulas change.

In Excel, formulas begin with an = sign. Parentheses () can be used to define the order of mathematical operations.

Excel supports the following operators:

• Arithmetic operations:
  • addition (+);
  • multiplication (*);
  • finding percent (%);
  • subtraction(-);
  • division(/);
  • exponent (^).
• Comparison operators:
  • = equal;
  • < меньше;
  • > more;
  • <= меньше или равно;
  • >= greater than or equal to;
  • <>not equal.
• Telecom operators:
  • : range;
  • ; Union;
  • & operator for joining texts.

Table 22. Examples of formulas

Exercise

Insert formula -25-A1+AZ

Pre-enter any numbers in cells A1 and A3.

1. Select the required cell, for example B1.
2. Start entering the formula with the = sign.
3. Enter the number 25, then the operator (- sign).
4. Enter a reference to the first operand, for example by clicking on the desired cell A1.
5. Enter the following operator (+ sign).
6. Click in the cell that is the second operand in the formula.
7. Complete the formula by pressing the key Enter. In cell B1 you will get the result.

Autosummation

Button AutoSum- ∑ can be used to automatically create a formula that sums the area of ​​immediately adjacent cells left in this line and directly higher in this column.

1. Select the cell in which you want to place the summation result.
2. Click the AutoSum - ∑ button or press the key combination Alt+=. Excel will decide which area to include in the sum range and will highlight it with a dotted moving frame called a border.
3. Click Enter to accept the area that Excel has selected, or use your mouse to select a new area and then press Enter.

The AutoSum function automatically transforms when cells are added or deleted within an area.

Exercise

Creating a table and calculating using formulas

1. Enter numeric data in the cells as shown in the table. 23.

Table 23. Original data table

1. Select cell B7 in which the vertical sum will be calculated.
2. Click the AutoSum - ∑ button or click Alt+=.
3. Repeat steps 2 and 3 for cells C7 and D7.

Calculate the number of employees without higher education (using formula B7-VZ).

1. Select cell B8 and type the (=) sign.
2. Click cell B7, which is the first operand in the formula.
3. Enter the (-) sign on your keyboard and click in the cell V3, which is the second operand in the formula (the formula will be entered).
4. Click Enter(the result will be calculated in cell B8).
5. Repeat steps 5-8 to calculate using the appropriate formulas in cells C8 and 08.
6. Save the file with the name Education_employees.x1s.

Table 24.Calculation result

Duplicate formulas using a fill marker

The cell area (cell) can be multiplied by using fill marker. As shown in the previous section, the fill handle is a control point in the lower right corner of the selected cell.

It is often necessary to reproduce not only data, but also formulas containing address links. The process of replicating formulas using a fill handle allows you to copy the formula while simultaneously changing the address references in the formula.

1. Select the cell containing the formula to replicate.
2. Drag fill marker in the right direction. The formula will be replicated in all cells.

This process is typically used when copying formulas within rows or columns containing the same type of data. When replicating formulas using the fill marker, the so-called relative addresses of the cells in the formula change (relative and absolute links will be described in detail below).

Exercise

Replication of formulas

1.Open the file Employee_Education.x1s.

1. Enter the formula for auto-summing cells =SUM(VZ:03) in cell E3.
2. Copy the formula by dragging the fill handle into cells E4:E8.
3. Look at how the relative addresses of cells change in the resulting formulas (Table 25) and save the file.

Table 25. Changing cell addresses when replicating formulas

Relative and absolute references

Formulas that implement calculations in tables use so-called references to address cells. The cell reference can be relative or absolute.

Using relative references is similar to indicating the direction of travel on a street - "go three blocks north, then two blocks west." Following these instructions from different starting places will lead to different destinations.

For example, a formula that sums the numbers in a column or row is then often copied for other row or column numbers. Such formulas use relative references (see the previous example in Table 25).

An absolute reference to a cell.or area of ​​cells will always refer to the same row and column address. When compared with street directions, it will be something like this: “Go to the intersection of Arbat and the Boulevard Ring.” Regardless of where you start, it will lead to the same place. If the formula requires that the cell address remain unchanged when copied, then an absolute reference must be used (record format $A$1). For example, when a formula calculates fractions of a total amount, the reference to the cell containing the total amount should not change when copied.

A dollar sign ($) will appear before both a column reference and a row reference (for example, $C$2). Pressing F4 successively will add or remove a sign before the column or row number in the reference (C$2 or $C2 - the so-called mixed links).

1. Create a table similar to the one below.

Table 26. Salary calculation

1. In cell СЗ enter the formula for calculating Ivanov’s salary =В1*ВЗ.

When replicating the formula of this example with relative references, an error message (#VALUE!) appears in cell C4, since the relative address of cell B1 will change, and the formula =B2*B4 will be copied to cell C4;

1. Set an absolute reference to cell B1 by placing the cursor in the formula bar on B1 and pressing the F4 key. The formula in cell C3 will look like =$B$1*BZ.
2. Copy the formula into cells C4 and C5.
3. Save the file (Table 27) under the name Salary.xls.

Table 27. Salary calculation results

Names in formulas

Names in formulas are easier to remember than cell addresses, so you can use named scopes (one or more cells) instead of absolute references. The following rules must be followed when creating names:

• names can contain no more than 255 characters;
• names must begin with a letter and can contain any character except a space;
• names should not be similar to references, such as VZ, C4;
• names should not use Excel functions such as SUM IF and so on.

On the menu Insert, Name There are two different commands for creating named areas: Create and Assign.

Team Create allows you to specify (enter) the required name ( only one), Assign command uses labels placed on the worksheet as area names (allows you to create several names at once).

Creating a name

1. Select cell B1 (Table 26).
2. Select from menu Insert, Name (Insert, Name) command Assign (Define).
3. Enter your name Hourly rate and click OK.
4. Select cell B1 and make sure that the name field says Hourly rate.

Creating Multiple Names

1. Select cells VZ:C5 (Table 27).
2. Select from menu Insert, Name (Insert, Name) command Create (Create), a dialog box will appear Create names(Fig. 88).
3. Make sure the radio button in the left column is checked and click OK.
4. Select cells VZ:NZ and make sure that the name field says Ivanov.

Rice. 88. Dialog box Create names

You can insert a name into the formula instead of an absolute link.

1. In the formula bar, place the cursor where you want to add the name.
2. Select from menu Insert, Name (Insert, Name) command Paste (Paste), The Insert Names dialog box appears.

1. Select the desired name from the list and click OK.

Errors in formulas

If an error is made when entering formulas or data, an error message appears in the resulting cell. The first character of all error values ​​is the # character. The error values ​​depend on the type of error made.

Excel can not recognize all errors, but those that are detected must be able to be corrected.

Error # # # # appears when the entered number does not fit in the cell. In this case, you should increase the column width.

Error #DIV/0! appears when a formula attempts to divide by zero. This most often happens when the divisor is a cell reference that contains a null or empty value.

Error #N/A! is an abbreviation for the term "undefined data". This error indicates that a formula is using a blank cell reference.

Error #NAME? appears when a name used in a formula has been removed or was not previously defined. To correct, determine or correct the data area name, function name, etc.

Error #EMPTY! appears when there is an intersection between two regions that do not actually have common cells. Most often, the error indicates that an error was made when entering references to cell ranges.

Error #NUMBER! appears when a function with a numeric argument uses an incorrect argument format or value.

Error #VALUE! appears when a formula uses an invalid argument or operand type. For example, text was entered instead of a numeric or logical value for an operator or function.

In addition to the listed errors, a circular link may appear when entering formulas.

A circular reference occurs when a formula directly or indirectly includes references to its own cell. A circular reference can cause distortions in worksheet calculations and is therefore considered an error in most applications. When you enter a circular reference, a warning message appears (Figure 89).

To correct the error, delete the cell that caused the circular reference, edit or re-enter the formula.

Functions in Excel

More complex calculations in Excel tables are carried out using special functions (Fig. 90). A list of function categories is available when you select a command Function in the Insert menu (Insert, Function).

Financial functions carry out such calculations as calculating the amount of payment on a loan, the amount of payment of profit on investments, etc.

The Date and Time functions let you work with date and time values ​​in formulas. For example, you can use the current date in a formula using the function TODAY.

Rice. 90. Function Wizard

Mathematical functions perform simple and complex mathematical calculations, such as calculating the sum of a range of cells, the absolute value of a number, rounding numbers, etc.

Statistical functions allow you to perform statistical data analysis. For example, you can determine the mean and variance of a sample and much more.

Database functions can be used to perform calculations and to select records based on conditions.

Text functions provide the user with the ability to process text. For example, you can concatenate multiple strings using the function CONNECT.

Logical functions are designed to test one or more conditions. For example, the IF function allows you to determine whether a specified condition is true and returns one value if the condition is true and another if it is false.

Functions Checking Properties and Values are intended to determine the data stored in a cell. These functions check the values ​​in a cell according to a condition and return values ​​depending on the result TRUE or FALSE.

To perform table calculations using built-in functions, we recommend using the Function Wizard. The Function Wizard dialog is available when you select the command Function in the Insert menu or pressing a button , on the standard toolbar. During the dialogue with the wizard, you need to specify the arguments of the selected function; to do this, you need to fill in the fields in the dialog box with the corresponding values ​​or addresses of table cells.

Exercise

Calculate the average value for each line in the Education.xls file.

1. Select cell F3 and click on the Function Wizard button.
2. In the first window of the Function Wizard dialog, from the Statistical category, select the function AVERAGE, click on the button Further.
3. The second dialog box of the Function Wizard must provide arguments. The input cursor is in the input field of the first argument. In this field as an argument number! enter the range address B3:D3 (Fig. 91).
4. Click OK.
5. Copy the resulting formula into cells F4:F6 and save the file (Table 28).

Rice. 91. Entering an Argument in the Function Wizard

Table 28. Table of calculation results using the function wizard

To enter a range of cells into the Function Wizard window, you can use your mouse to circle this range on the table worksheet (in the example, B3:D3). If the Function Wizard window covers the desired cells, you can move the dialog box. After selecting a range of cells (B3:D3), a running dotted frame will appear around it, and the address of the selected range of cells will automatically appear in the argument field.

The Microsoft Excel spreadsheet editor is designed for processing numerical and text data. With its help, it is convenient to calculate various indicators: sum of values, product, percentage, arithmetic mean and many others.

How to make simple calculations

If the action you need is not indicated in the list of formulas, for example, you need to divide one number by another, the calculation algorithm will be as follows:

1. select the cell in which you want to see the calculation results;
2. place the cursor in the formula bar, enter the “=” sign;
3. click on the cell containing the value of the dividend;
4. enter the division sign “/”;
5. click on the cell containing the divisor value;
6. press Enter.

In the case when you need to find the difference between two values ​​in Excel, following step 4, enter the difference sign “-” using the number keys on the right side of the keyboard.

Algorithm for performing calculations using the fx button

1. Fill the table cells with the necessary data. Enter them manually using the keyboard.
2. Place the cursor in the cell in which you want to see the calculation results.
3. On the toolbar, click on the "Functions" icon (symbolized by fx), a dialog box called "Function Wizard" will appear.
4. Select the desired action (function), confirm your choice by clicking “OK”. A new Function Arguments window will appear.
5. Select the cells that contain the data for calculations, click OK.

How to calculate the sum of several values

To calculate the sum of two numbers, according to the algorithm, enter them into cells, for example, A1 and A2. Click once on cell A3 or select it using the left, right, down, or up arrows on your keyboard. Click on the fx button, the proposed list shows 10 recently used (or default) formulas. Select the "SUM" function, it will immediately appear in the formula bar, and the "Function Arguments" window will appear.

The next step will be to enter the designations of the cells containing the numbers that need to be added. This can be done manually by entering “A1” and “A2” from the keyboard (change the keyboard layout language to English), but it is easier and more correct to click sequentially on one cell, then on another. After clicking the “OK” button, the sum of the specified values ​​will be reflected in cell A3. Now, if you change the data in cells A1 and A2, the amount will be recalculated automatically.

One more thing: if you need to calculate the sum of the values ​​of a large number of cells, select the first one, then left-click and drag the cursor to the last one. The Function Arguments window will display the range you selected.

Having mastered these actions in the Excel editor, you will receive a very convenient tool for processing and systematizing a large amount of digital data.