Formulas in Excel: examples of writing and use. Creating simple formulas in Microsoft Excel

Since its appearance in the composition Office package Excel has become a permanent working tool for those involved in data analysis and structuring. Over the years, thanks to the constant development and improvements of this product, everyone has been able to use it - from the “advanced” to the most ordinary people. Microsoft's commitment to simplifying work with data is expressed in the constant simplification of working methods while maintaining functionality, so it is understandable that Excel becomes a powerful tool in the hands of the user.

Ready-made functions are used to carry out calculations

However, many functions cannot yet be simplified. This does not mean that they will require you to have programming skills or knowledge of higher mathematics. Although writing macros may present you with such conditions, they, in principle, have a high barrier to entry. You can use most of the functionality of Office with mouse clicks and short commands from the keyboard.

Formulas are used to process values ​​and their ranges - from a banal sum to methods of mathematical statistics such as median or mode. This is a very flexible and yet quite simple tool that is widely used along with conditional formatting when creating tables with data. In the simplest case, to write a formula in a cell, use the usual text input. To carry out, for example, adding values ​​from several fields, you can write “=SUM()” directly in the cell and indicate the addresses of the values ​​in parentheses, separated by semicolons. Or, if they are located next to each other, indicate the starting and ending characters separated by a colon. And to fix a cell in this formula, for example, so that a column or row does not change when copying the formula to another, put a “$” sign in front of the desired symbol. Then Excel will not change this escaped character while changing others.

In principle, you can enter any formula this way. Since Excel supports text input, you just need to know in advance required values, which you will substitute there. But if for simple actions This is acceptable, but for more or less complex ones this is already difficult, since you have to remember too many parameters. Therefore, it is easier to use the formula builder built into the program. In order to call it, you need to click on the button at the top of the page, to the left of the field for entering a value in a cell.

A dialog box will appear in which the user can create a data processing rule. On the left side of the window there is a list of formulas organized by category. There are mathematical, statistical, text groups, etc. Having selected the one you need, click on it with the left mouse button. A form for filling in values ​​will be displayed on the right side of the dialog box. In it you can specify data ranges or specific single addresses. To do this, click the highlight button on the right side of the input field, the window will collapse, and you will have access to the working part Excel sheet. Since in this mode you can select both single values ​​and their ranges, entering the desired values ​​is not difficult. After that, click the same button again and the window will restore its previous size. If the formula requires more than one set of data, then repeat the described procedure the required number of times.

In this field, by the way, the screening rules also work. Therefore, if you later distribute the contents of this cell to others, and at the same time want to save the data values, do not forget to put in the right places dollar sign.

When you are finished entering the constructor, click OK. At this stage, Excel checks the entered values ​​for type consistency and other errors, so it may throw out the error text. From it you can understand what exactly needs to be changed in order for the formula to work the way you expect it to. If there are no errors, the window will close and the value will appear in the cell. Sometimes it happens that one of the specified ranges is not yet filled, but the content type assigned to the cell corresponds to the desired one. Then there will be no errors at the verification stage, but the value will be calculated without it, so be careful.

As you know, the spreadsheet office editor Excel was originally designed to perform mathematical, algebraic and other calculations, for which you use the input of special formulas. However, in light of how to write a formula in Excel, it is necessary to take into account that most input operations are fundamentally different from everything that is usually used in ordinary life. That is, the formulas themselves have a slightly different form and use completely different operators from those used when writing standard calculations.

Let's look at the question of how to write a formula in Excel using a few simple examples, without touching on complex operations, to understand which you need to study the program in depth. But even preliminary knowledge will give any user an understanding basic principles using formulas for different cases.

How to write initial concepts

So, entering formula values ​​in a program is somewhat different from standard operations, symbols used and operators used. When solving the problem of how to write a formula in Excel, you need to start from basic concepts, which are used in almost all computer systems.

The fact is that the machine enters a combination like “2 x 2” or takes out common component beyond brackets (“2+2)5”) does not understand. To do this, it is possible to use several types of symbols, which are presented in the table below, not counting logical operators.

In this case, the priority of operations starts from the degree and ends with addition and subtraction. Additionally, although Excel can be used like a regular calculator, you typically need to specify cell numbers or cell ranges for calculations. It goes without saying that the data format in any such cell must be set to the appropriate one (at least numeric).

Sum and difference

How to write a sum or difference formula in Excel? So, let's start with the simplest thing, when you need to calculate the amount. In the formula bar (and for all operations), an equal sign is first entered, after which the desired formula is entered. In the case of a regular calculator, you can specify “=2+2” for the installed cell.

If the summation is performed for values ​​entered directly in other cells (for example, A1 and A2), the formula takes the form “=A1+A2”. Often for use additional operators the amount may be enclosed in parentheses. For the difference - the same thing, only with a minus instead of a plus.

When you need to specify cell numbers or their range, a special sum command can be used (in Russian "SUMM", in English - SUM). When specifying several cells, it looks like this: “=SUM(A1;A2)”, for a range - “SUM(A1:A10)”, provided that you need to calculate the sum of all numbers located in cells from the first to the tenth. In principle, if you set the active cell, which is located immediately after the last one in the column with the original values, you don’t have to enter the formula, but simply click on the automatic summation button.

Multiplication, division and exponentiation

Now let's see how to write a multiplication or division formula in Excel. The procedure is the same as when entering a sum or difference, only the operators differ.

For the product the form “=A1*A2” is used, for the quotient - “A1/A2”. By the way, these are exactly the same commands that can be found when using a standard Windows calculator.

For exponentiation, use the symbol “^”. For a value in cell A1 that, for example, needs to be squared, the formula “=A1^2” is used.

Percentage calculations

With percentages, if you do not touch upon complex calculations, everything is also simple. How to write a formula with percentages in Excel?

It is enough to enter a formula of the form “=A1*5%”, after which you will receive the same five percent of the value in the corresponding cell.

Using cell selection-based formula entry

But all this related to manual assignment or the so-called direct input of formulas (direct or direct input). In fact, sometimes it is useful to use the mouse and the Ctrl key.

While holding down the mouse key, you can simply select the desired cells, having first entered the required calculation in the formula bar. Cells will be added directly to the formula bar. But, depending on the type of formula, sometimes you will have to add parentheses manually.

Absolute, relative and mixed cell types

It is also worth noting that a program can use several types of cells, not to mention the data they contain.

An absolute cell is immutable and is denoted as $A$1, a relative cell is a reference to a common location (A1), a mixed cell is a combination of references to both an absolute and a relative cell ($A1 or A$1). Typically, such formats are used when creating formulas that involve data located on different sheets of a book or even in different files.

VLOOKUP formulas

Finally, let's see how to write a VLOOKUP formula in Excel. This technique allows you to insert data from one range into another. In this case, the method is somewhat similar to that used when solving the problem of how to write a “Condition” formula in Excel, which uses the symbols given in the table above.

IN in a general sense such calculations are something like simple filter applied to columns when you want to filter only exact values ​​rather than approximate values.

In this option, first, through the “Function Wizard”, the range of values ​​of the original (first) table is used, in the “Table” field the second range is indicated with the contents fixed (F4), then the column number is indicated, and in the interval viewing field the value is set to “FALSE” if indeed, when filtering, you really only need to get exact, not approximate values. As a rule, such formulas are used more in warehouse or accounting, when some specialized software products not possible.

Conclusion

It remains to be said that not all formulas that can be used in a tabular table have been described here. Excel editor. This is, so to speak, just the basics. In fact, if you dig deeper into trigonometry or calculating logarithms, matrices, or even tensor equations, everything looks much more complicated. But in order to study all this, you need to thoroughly study the manual for the editor itself. And this is not about the fact that in Excel, based on changing data, you can create even the simplest logic games. As an example, we can cite the same “snake”, which initially had nothing to do with the spreadsheet editor, but was reproduced by enthusiasts of their craft in Excel.

Otherwise, you should clearly understand that, having studied primitive formulas or operations with data, you can then easily master more complex calculations, say, creating cross-references, using various kinds of scripts or VB scripts, etc. All this takes time, so if you want to study the program and all its capabilities to the maximum, you will have to work hard on the theoretical part.

IN Microsoft Excel calculations performed on the entered data are performed using formulas. Formulas can be simple, for example adding two prime numbers, or incredibly complex, such as calculating differential equation second order.

Calculations that are performed using formulas use data stored in cells other than those that display the result of the formula in the worksheet. When creating complex worksheets, you can create formulas that query data from other formulas. In such spreadsheets, changing one cell can cause a global recalculation of the entire worksheet.

In this tutorial we will look at "How to make a formula in Excel".

Creating a formula in Excel.

The formula consists of three main parts:

• All equals sign (=);
• One or more links;
• Signs of mathematical operations (addition, multiplication, etc.).

A cell reference is a unique entry that uniquely identifies a single cell, such as A4 or D9. The basic mathematical operations used in formulas include addition (+), subtraction (-), multiplication (*) and division (/). The table shows all the mathematical operators that can be used in formulas.

A typical formula basically uses one mathematical operator and references two cells.

This formula contains three parts.

• Sign =. Shows that the written text is a formula. If you write in cell A4+C7, then M.C. Excel will treat this entry as a normal test.

• Links to two cells. In our random example these are A4 and C
• Mathematical addition operator (+).

To create a formula in an Excel cell, follow a series of steps.

1. Click on the cell in which you want to create the formula.

The arrow keys on the keyboard are also often used to select a cell.

M.C. Excel will highlight the cell you need.

1. Enter an equal sign (=).

1. Enter a formula that contains several cell references with the required data, for example A4 or E8

If you need to sum the numbers written in cells A4 and E8, then write =A4+E8.

1. Click " Enter».

Writing cell references can be a bit of a hassle because it requires keeping track of the row and column names of the cells used in the formula. As another option, you can suggest clicking on the cells with the desired data to add links to these cells in the formulas. This semi-automatic type of writing formulas is very well known in M.C. Excel.

To use the mouse to write cell references when creating a formula, follow a series of steps.

1. Click on the cell in which the formula will be located. (Remember that you can also use the keyboard arrow keys to select cells.)

M.C. Excel will highlight the cell you need.

1. Write an equal sign (=)

1. Click on the cells with the required data (for example, A4 or E8) and write the necessary mathematical operations.

To make the formula =A4+E8 in Excel, follow the steps below.

a) Enter =.

Thus you show M.C. Excel that you are going to create a formula.

b) Click cell A4.

Microsoft Excel will automatically insert a link to cell A4 into the formula.

c) Write +.

d) Click cell E8.

Microsoft Excel will automatically insert a link to cell E8 into the formula.

1. Press the " Enter».

Once we've finished creating a formula, we can change the data in the cells pointed to by the formula to achieve the effect of calculations.

Now you know how to make a formula in Excel. We wish you good luck.

If you have read the previous ones, you should be aware that the formula begins with the “Equals” sign. When it becomes necessary to write this sign in a cell without a formula, the program persistently continues to consider such an entry as the beginning of the formula. When you click on another cell, the cell address is written after the sign. In this case, there are several ways to outsmart Excel.

Solution:
Place a space or an apostrophe before writing an equal sign, plus (addition), minus (subtraction), slash (division) or asterisk (multiplication).

Why doesn't the formula calculate in Excel?

If you have to work for different computers, then you may have to deal with the fact that necessary in the work Excel files They do not calculate using formulas.

Incorrect cell format or incorrect cell range settings

In Excel there are various errors with a hashtag (#), such as #VALUE!, #LINK!, #NUMBER!, #N/A, #DIV/0!, #NAME? and #EMPTY!. They indicate that something in the formula is not working correctly. There may be several reasons.

Instead of the result it is given #VALUE!(in version 2010) or the formula is displayed in text format(in version 2016).

IN in this example it can be seen that the contents of the cells are multiplied with different types data =C4*D4.

Bug fix: indication correct address =C4*E4 and copying the formula for the entire range.

• Error #LINK! Occurs when a formula references cells that have been deleted or replaced with other data.
• Error #NUMBER! Occurs when a formula or function contains an invalid numeric value.
• Error #N/A usually means that the formula does not find the requested value.
• Error #DIV/0! occurs when a number is divisible by zero (0).
• Error #NAME? occurs due to a typo in the formula name, that is, the formula contains a reference to a name that is not defined in Excel.
• Error #EMPTY! Occurs when an intersection is specified between two areas that do not actually intersect, or an incorrect separator is used between references when specifying a range.

Note:#### does not indicate a formula error, but rather that the column is not wide enough to display the contents of the cells. Simply drag the column border to expand it, or use the option Home - Format - Automatic column width selection.

Errors in formulas

Green triangles in the corner of a cell may indicate an error: numbers are written as text. Numbers stored as text may produce unexpected results.

Correction: Select a cell or range of cells. Click the "Error" sign (see picture) and select the desired action.

Formula display mode enabled

Since in normal mode Calculated values ​​are displayed in the cells. To see the calculation formulas directly, Excel provides a mode for displaying all formulas on the sheet. Enable and disable this mode can be called with the command Show formulas from tab Formulas in section Formula dependencies.

Automatic calculation using formulas is disabled

This is possible in files with a large amount of calculations. In order to weak computer did not slow down, the author of the file can disable automatic calculation in the file properties.

Correction: after changing the data, press the F9 button to update the results or enable automatic calculation. File – Options – Formulas – Calculation options – Calculations in the workbook: automatically.

Addition formula in Excel

Adding in spreadsheets is fairly easy. You need to write a formula that lists all the cells containing the data to be added. Of course, we put a plus between the cell addresses. For example, =C6+C7+C8+C9+C10+C11.

But if there are too many cells, then it is better to use the built-in function Autosum. To do this, click the cell in which the result will be displayed, and then click the button Autosum on the tab Formulas(highlighted with a red frame).

The range of cells to be summed will be highlighted. If the range was selected incorrectly, for example, vertical cells are selected, but horizontal ones are needed, then select a new range. To do this, left-click on the outermost cell of the new range and, without releasing the button, drag the pointer through all cells of the range to the final one. Complete the formula by pressing the key Enter on the keyboard.

Formula for rounding to a whole number in Excel

Beginner users use formatting that some try to round a number with. However, this does not affect the contents of the cell in any way, as indicated in the tooltip. When you click on the button (see figure), the format of the number will change, that is, its visible part will change, but the contents of the cell will remain unchanged. This can be seen in the formula bar.

To round a number according to mathematical rules, you must use the built-in function =ROUND(number,number_of_digits).

You can write it manually or use the function wizard on the tab Formulas in the group Mathematical(see picture).

This function can round not only the fractional part of a number, but also whole numbers to the desired digit. To do this, when writing the formula, indicate the number of digits with a minus sign.

How to calculate percentages of a number

To calculate percentages in a spreadsheet, select the cell to enter the calculation formula. Place the equal sign, then write the address of the cell (use the English layout) that contains the number from which you will calculate the percentage. You can simply click on this cell and the address will be inserted automatically. Next, put the multiplication sign and enter the number of percentages that need to be calculated. Look at an example of calculating a discount when purchasing a product.
Formula =C4*(1-D4)

IN C4 the price of the vacuum cleaner is written down, and in D4– discount in %. It is necessary to calculate the cost of the product minus the discount; for this, our formula uses the construction (1-D4). Here the percentage value by which the price of the product is multiplied is calculated. For Excel entry kind 15% means the number 0.15, so it is subtracted from one. As a result, we get a residual value of the goods of 85% of the original.

In this simple way, using spreadsheets, you can quickly calculate percentages of any number.

Excel formula cheat sheet

The cheat sheet is made in the form of a PDF file. It includes the most popular formulas from the following categories: mathematical, text, logical, statistical. To get the cheat sheet, click the link below.

PS: Interesting facts about the real cost of popular goods

Dear reader! You have watched the article to the end.
Have you received an answer to your question? Write a few words in the comments.
If you haven't found the answer, indicate what you were looking for.

- Formula entry order

- Relative, absolute and mixed references

- Using text in formulas

Now let's move on to the most interesting part - creating formulas. In fact, this is what spreadsheets were developed for.

Formula entry order

You must enter the formula starting with the equal sign. This is necessary so that Excel understands that it is a formula and not data that is being entered into the cell.

Select an arbitrary cell, for example A1. In the formula bar we enter =2+3 and press Enter. The result (5) appears in the cell. And the formula itself will remain in the formula bar.

Experiment with different arithmetic operators: addition (+), subtraction (-), multiplication (*), division (/). To use them correctly, you need to clearly understand their priority.

The expressions inside the parentheses are executed first.

Multiplication and division have higher priority than addition and subtraction.

Operators with the same precedence are executed from left to right.

My advice to you is to USE BRACKETS. In this case, you will protect yourself from accidental errors in calculations, on the one hand, and on the other hand, brackets make reading and analyzing formulas much easier. If the number of closing and opening parentheses in a formula does not match, Excel will display an error message and offer an option to correct it. Immediately after you enter the closing parenthesis, Excel displays in bold(or in a different color) the last pair of brackets, which is very convenient if there are a large number of brackets in the formula.

Now let's let's try to work using references to other cells in formulas.

Enter the number 10 in cell A1, and the number 15 in cell A2. In cell A3, enter the formula =A1+A2. In cell A3 the sum of cells A1 and A2 will appear - 25. Change the values ​​of cells A1 and A2 (but not A3!). After changing the values ​​in cells A1 and A2, the value of cell A3 is automatically recalculated (according to the formula).

To avoid mistakes when entering cell addresses, you can use the mouse when entering links. In our case, we need to do the following:

Select cell A3 and enter an equal sign in the formula bar.

Click cell A1 and enter the plus sign.

Click cell A2 and press Enter.

The result will be similar.

Relative, absolute and mixed references

To better understand the differences between links, let's experiment.

A1 - 20 B1 - 200

A2 - 30 B2 - 300

In cell A3, enter the formula =A1+A2 and press Enter.

Now place the cursor on the lower right corner of cell A3, press the right mouse button and drag over cell B3 and release the mouse button. A context menu will appear in which you need to select “Copy Cells”.

After this, the formula value from cell A3 will be copied to cell B3. Activate cell B3 and see what formula you get - B1+B2. Why did this happen? When we wrote the formula A1+A2 in cell A3, Excel interpreted this entry as follows: “Take the values ​​​​from the cell located two rows higher in the current column and add the value of the cell located one row higher in the current column.” Those. by copying the formula from cell A3, for example, to cell C43, we get - C41 + C42. This is the beauty of relative links; the formula itself seems to adapt to our tasks.

Enter the following values ​​in the cells:

A1 - 20 B1 - 200

A2 - 30 B2 - 300

Enter the number 5 in cell C1.

In cell A3, enter the following formula =A1+A2+$C$1. Similarly, copy the formula from A3 to B3. Look what happened. Relative links “adjusted” to the new values, but the absolute link remained unchanged.

Now try experimenting with mixed links yourself and see how they work. You can reference other sheets in the same workbook in the same way that you can reference cells in the current sheet. You can even refer to sheets from other books. In this case, the link will be called an external link.

For example, to write a link to cell A5 (Sheet2) in cell A1 (Sheet 1), you need to do the following:

Select cell A1 and enter an equal sign;

Click on the "Sheet 2" shortcut;

Click cell A5 and press enter;

After this, Sheet 1 will be activated again and the following formula will appear in cell A1 = Sheet2! A5.

Editing formulas is similar to editing text values ​​in cells. Those. you need to activate the cell with the formula by highlighting or double-clicking the mouse, and then edit it using, if necessary, the Del, Backspace keys. Changes are committed by pressing the Enter key.

Using text in formulas

WITH text values You can perform mathematical operations if text values ​​contain only the following characters:

Numbers from 0 to 9, + - e E /

You can also use five numeric formatting characters:

$%() space

In this case, the text must be enclosed in double quotes.

Wrong: =$55+$33

Correct: ="$55"+$"33"

When Excel performs calculations, it converts numeric text into numeric values, so the result of the above formula is 88.

To combine text values, use the text operator & (ampersand). For example, if cell A1 contains the text value "Ivan", and cell A2 contains the text value "Petrov", then entering the following formula =A1&A2 into cell A3, we get "IvanPetrov".

To insert a space between the first and last name, write this: =A1&" "&A2.

The ampersand can be used to combine cells with different data types. So, if in cell A1 there is the number 10, and in cell A2 there is the text “bags”, then as a result of the formula =A1&A2, we will get "10 bags". Moreover, the result of such a union will be a text value.

Excel functions - introduction

Functions

Autosum

Using headings in formulas

Functions

FunctionExcel is a predefined formula that operates on one or more values ​​and returns a result.

The most common Excel functions are shortcuts to frequently used formulas.

For example function =SUM(A1:A4) similar to recording =A1+A2+A3+A4.

And some functions perform very complex calculations.

Each function consists of name And argument.

In the previous case SUM- This Name functions, and A1:A4-argument. The argument is enclosed in parentheses.

Autosum

Because Since the sum function is used most often, the “AutoSum” button has been added to the “Standard” toolbar.

Enter arbitrary numbers in cells A1, A2, A3. Activate cell A4 and click the AutoSum button. The result is shown below.

Press enter. The formula for the sum of cells A1..A3 will be inserted into cell A4. The AutoSum button has a drop-down list from which you can select a different formula for the cell.

To select a function, use the "Insert Function" button in the formula bar. When you click it, the following window appears.

If the exact function that needs to be applied is unknown at the moment, then you can search in the “Search for Function” dialog box.

If the formula is very cumbersome, you can include spaces or line breaks in the formula text. This does not affect the calculation results in any way. To break a line, press the key combination Alt+Enter.

Using headings in formulas

You can use table headers in formulas instead of references to table cells. Construct the following example.

By default, Microsoft Excel does not recognize headings in formulas. To use headings in formulas, select Options on the Tools menu. On the Calculations tab, in the Workbook Options group, select the Allow range names check box.

If written normally, the formula in cell B6 would look like this: =SUM(B2:B4).

When using headings, the formula will look like this: =SUM(Q 1).

You need to know the following:

If a formula contains the header of the column/row it is in, then Excel thinks you want to use the range of cells located below the table column header (or to the right of the row header);

If a formula contains a column/row heading other than the one it is in, Excel assumes that you want to use the cell at the intersection of the column/row with that heading and the row/column where the formula is located.

When using headers, you can specify any table cell using - range intersection. For example, to reference cell C3 in our example, you can use the formula =Row2 Q2. Notice the space between the row and column headings.

Formulas containing headings can be copied and pasted, and Excel automatically adjusts them to the correct columns and rows. If an attempt is made to copy a formula to an inappropriate place, Excel will report this and display the value NAME? in the cell. When changing the heading names, similar changes occur in the formulas.

“Data entry in Excel || Excel || Excel cell names"

Cell and range names inExcel

- Names in formulas

- Assigning names in the name field

- Rules for naming cells and ranges

You can name Excel cells and cell ranges and then use them in formulas. While formulas that contain headings can only be applied in the same worksheet as the table, you can use range names to refer to table cells anywhere in any workbook.

Names in formulas

The cell or range name can be used in a formula. Let us write the formula A1+A2 in cell A3. If you name cell A1 "Bases" and cell A2 "Add-in", then the entry Basis+Add-in will return the same value as the previous formula.

Assigning names to the name field

To assign a name to a cell (range of cells), you must select the corresponding element, and then enter the name in the name field; spaces cannot be used.

If the selected cell or range has been given a name, then that name is displayed in the name field, and not a link to the cell. If a name is defined for a range of cells, it will appear in the name field only when the entire range is selected.

If you want to navigate to a named cell or range, click the arrow next to the name field and select the cell or range name from the drop-down list.

More flexible options for assigning names to cells and their ranges, as well as headings, are provided by the "Name" command from the "Insert" menu.

Rules for naming cells and ranges

The name must begin with a letter, a backslash (\), or an underscore (_).

You can only use letters, numbers, backslashes, and underscores in your name.

You cannot use names that can be interpreted as references to cells (A1, C4).

Single letters can be used as names, with the exception of the letters R, C.

Spaces must be replaced with an underscore.

"Excel Functions|| Excel || Excel Arrays"

ArraysExcel

- Using arrays

- Two-dimensional arrays

- Rules for array formulas

Arrays in Excel are used to create formulas that return a set of results or operate on a set of values.

Using Arrays

Let's look at a few examples to better understand arrays.

Let's calculate, using arrays, the sum of the values ​​in the rows for each column. To do this, do the following:

Enter numeric values ​​in the range A1:D2.

Select the range A3:D3.

In the formula bar, enter =A1:D1+A2:D2.

Press the key combination Ctrl+Shift+Enter.

Cells A3:D3 form an array range, and the array formula is stored in each cell of that range. The argument array is references to the ranges A1:D1 and A2:D2

Two-dimensional arrays

In the previous example, the array formulas were placed in a horizontal one-dimensional array. You can create arrays that contain multiple rows and columns. Such arrays are called two-dimensional.

Rules for Array Formulas

Before entering an array formula, you must select the cell or range of cells that will contain the results. If your formula returns multiple values, you must select a range that is the same size and shape as the range containing the source data.

Press the Ctrl+Shift+Enter keys to fix the entry of the array formula. This causes Excel to enclose the formula in curly braces in the formula bar. DO NOT ENTER CURLY BRACES BY MANUAL!

Within a range, you cannot edit, clear, or move individual cells, or insert or delete cells. All cells in an array range must be treated as a single unit and edited all at once.

To change or clear an array, you need to select the entire array and activate the formula bar. After changing the formula, you need to press the key combination Ctrl+Shift+Enter.

To move the contents of an array range, you need to select the entire array and select the "Cut" command from the "Edit" menu. Then select the new range and choose Paste from the Edit menu.

It is not allowed to cut, clear or edit part of the array, but you can assign different formats individual cells in the array.

“Excel Cells and Ranges|| Excel || Formatting in Excel"

Assigning and deleting formats inExcel

- Purpose of the format

- Removing a format

- Formatting using toolbars

- Formatting individual characters

- Application of autoformat

Formatting in Excel is used to make data easier to understand, which plays an important role in productivity.

Purpose of the format

Select the command "Format" - "Cells" (Ctrl+1).

In the dialog box that appears (the window will be discussed in detail later), enter the desired formatting parameters.

Click "OK"

A formatted cell retains its format until a new format is applied to it or an old one is deleted. When you enter a value into a cell, the format already used in the cell is applied to it.

Removing a format

Select a cell (range of cells).

Select the command "Edit" - "Clear" - "Formats".

To delete values ​​in cells, select the “All” command from the “Clear” submenu.

Please note that when copying a cell, along with its contents, the cell format is also copied. Therefore, you can save time by formatting the source cell before using the copy and paste commands.

Formatting using toolbars

The most frequently used formatting commands are located on the Formatting toolbar. To apply a format using a toolbar button, select a cell or range of cells and then click the button. To delete the format, press the button again.

For quick copying To format selected cells into other cells, you can use the Format Painter button in the Formatting panel.

Formatting individual characters

Formatting can be applied to individual characters of a text value in a cell as well as to the entire cell. To do this, select the desired characters and then select the “Cells” command from the “Format” menu. Set the required attributes and click OK. Press the Enter key to see the results of your work.

Using AutoFormat

Excel's automatic formats are predefined combinations of number format, font, alignment, borders, pattern, column width, and row height.

To use autoformat, you need to do the following:

Enter the required data into the table.

Select the range of cells you want to format.

From the Format menu, select AutoFormat. This will open a dialogue window.

In the AutoFormat dialog box, click the Options button to display the Edit area.

Select the appropriate auto format and click "OK".

Select a cell outside the table to deselect the current block, and you will see the formatting results.

"Excel Arrays|| Excel || Formatting numbers in Excel"

Formatting numbers and text in Excel

-General format

-Number formats

-Currency formats

-Financial formats

-Percentage formats

-Fractional formats

-Exponential formats

-Text format

-Additional formats

-Creation of new formats

The Format Cells dialog box (Ctrl+1) allows you to control the display of numeric values ​​and change the text output.

Before opening the dialog box, select the cell containing the number you want to format. In this case, the result will always be visible in the "Sample" field. Keep in mind the difference between stored and displayed values. The formats do not affect stored numeric or text values ​​in cells.

General format

Any text or numeric value entered is displayed in General format by default. In this case, it is displayed exactly as it was entered into the cell, with the exception of three cases:

Long numeric values ​​are displayed in scientific notation or rounded.

The format does not display leading zeros (456.00 = 456).

A decimal entered without the number to the left of the decimal point is output with a zero (.23 = 0.23).

Number formats

This format allows you to display numeric values ​​as integers or fixed-point numbers, and to highlight negative numbers using color.

Currency formats

These formats are similar to number formats, except that instead of a digit separator, they control the display of a currency symbol that you can select from the Symbol list.

Financial formats

The financial format basically follows the currency formats - you can output a number with or without a currency unit and a specified number of decimal places. The main difference is that the financial format outputs the currency unit aligned to the left, while the number itself is aligned to the right edge of the cell. As a result, both the currency and the numbers are vertically aligned in the column.

Percentage formats

This format displays numbers as percentages. The decimal point in the formatted number is shifted two places to the right, and the percent sign appears at the end of the number.

Fractional formats

This format displays fractional values ​​as fractions rather than decimals. These formats are especially useful when dealing with exchange prices or measurements.

Exponential formats

Scientific formats display numbers in scientific notation. This format is very convenient to use for displaying and outputting very small or very large numbers.

Text format

Applying a text format to a cell means that the value in that cell should be treated as text, as indicated by left alignment of the cell.

It doesn't matter if the numeric value is formatted as text, because... Excel is capable of recognizing numeric values. An error will occur if there is a formula in a cell that has a text format. In this case, the formula is treated as plain text, so errors are possible.

Additional formats

Creation of new formats

To create a format based on an existing format, do the following:

Select the cells you want to format.

Press the key combination Ctrl+1 and on the “Number” tab of the dialog window that opens, select the “All formats” category.

In the "Type" list, select the format that you want to change and edit the contents of the field. In this case, the original format will remain unchanged, and the new format will be added to the “Type” list.

“Formatting in Excel || Excel ||

Aligning the contents of Excel cells

-Left, center and right alignment

-Filling cells

-Word wrap and alignment

-Vertical alignment and text orientation

-Automatic character size selection

The Alignment tab of the Format Cells dialog box controls the placement of text and numbers in cells. This tab can also be used to create multi-line text boxes, repeat a series of characters in one or more cells, and change the orientation of text.

Left, center, and right alignment

When you select Left, Center, or Right, the contents of the selected cells are aligned to the left, center, or right edge of the cell, respectively.

When aligning to the left, you can change the amount of indentation, which is set to zero by default. Increasing the indent by one unit moves the value in the cell one character width to the right, which is approximately the width of a capital X in the Normal style.

Filling cells

The Fill format repeats the value entered in the cell to fill the entire width of the column. For example, in the worksheet shown in the image above, cell A7 repeats the word "Fill". Although the range of cells A7-A8 appears to contain many words "Fill", the formula bar suggests that there is actually only one word. Like all other formats, the Filled format only affects appearance, and not on the stored contents of the cell. Excel repeats characters along the entire range without spaces between cells.

It may seem that repeating characters are just as easy to enter using the keyboard as using fill. However, the Filled format offers two important advantages. First, if you adjust the column width, Excel increases or decreases the number of characters in the cell as appropriate. Secondly, you can repeat a character or characters in several adjacent cells at once.

Since this format affects numeric values ​​in the same way as text, the number may look completely different from what you want. For example, if you apply this format to a 10-character wide cell that contains the number 8, that cell will display 8888888888.

Word wrap and justification

If you enter a text box that is too long for the active cell, Excel extends the text box beyond the cell provided that adjacent cells are empty. If you then select the Word Wrap check box on the Alignment tab, Excel will display this text entirely within one cell. To do this, the program will increase the height of the line the cell is in and then place the text on additional lines inside the cell.

When you apply the Justify horizontal alignment format, text in the active cell is word-wrapped onto additional lines within the cell and aligned to the left and right edges, with line height automatically adjusted.

If you create a multiline text box and subsequently clear the Word Wrap option or apply a different horizontal alignment format, Excel restores the original row height.

Format vertical alignment Fit to Height does essentially the same thing as Fit to Width, except that it aligns the cell's value to its top and bottom edges rather than to its sides.

Vertical alignment and text orientation

Excel provides four formats for vertical text alignment: top, center, bottom, and height.

The Orientation area allows you to position cell content vertically from top to bottom or slanted up to 90 degrees clockwise or counterclockwise. Excel automatically adjusts the row height in portrait orientation unless you previously or subsequently set the row height manually.

Automatic character sizing

The Auto-Fit Width checkbox reduces the size of the characters in the selected cell so that its contents fit entirely within the column. This can be useful when working with a worksheet in which adjusting the column width to a long value has an undesirable effect on the rest of the data, or in the event. When using vertical or italic text, word wrap is not an acceptable solution. In the figure below, the same text is entered into cells A1 and A2, but the “Auto-fit width” checkbox is selected for cell A2. When changing the column width, the size of the characters in cell A2 will decrease or increase accordingly. However, this maintains the font size assigned to the cell, and if you increase the column width after reaching a certain value, the character size will not be adjusted.

It should be said that although this format is a good way to solve some problems, it must be borne in mind that the size of the characters can be as small as desired. If the column is narrow and the value is long enough, the contents of the cell may become unreadable after applying this format.

"Custom Format || Excel || Font in Excel"

Using cell borders and shadingExcel

-Use of boundaries

-Application of color and patterns

-Using fill

Using Borders

Borders and cell shading can be a good way to decorate different areas of a worksheet or draw attention to important cells.

To select a line type, click on any of thirteen boundary line types, including four solid lines of varying thicknesses, a double line, and eight types of dotted lines.

By default, the border line color is black when the Color box is set to Auto on the View tab of the Options dialog box. To select a color other than black, click the arrow to the right of the Color box. The current 56-color palette will open, in which you can use one of the existing colors or define a new one. Note that you must use the Color list on the Border tab to select a border color. If you try to do this using the formatting toolbar, you will change the text color in the cell, not the border color.

After selecting the line type and color, you need to specify the position of the border. Clicking the Outer button in the All area places a border around the perimeter of the current selection, whether it's a single cell or a block of cells. To remove all boundaries present in the selection, click the No button. The viewing area allows you to control the placement of borders. When you first open the dialog box for a single selected cell, this area contains only small markers indicating the corners of the cell. To place a border, click on the viewport where you want the border to be, or click the appropriate button next to that area. If you have multiple cells selected in a worksheet, the Border tab makes the Inner button available so you can add borders between the selected cells. Additionally, additional handles appear on the sides of the selection in the viewing area to indicate where the inner borders will go.

To remove a placed border, simply click on it in the viewing area. If you need to change the border format, select a different linetype or color and click that border in the viewing area. If you want to start placing boundaries again, click the No button in the All area.

You can apply multiple types of borders to selected cells at the same time.

You can apply border combinations using the Borders button on the Formatting toolbar. When you click the small arrow next to this button, Excel will display a border palette from which you can choose the type of border.

The palette consists of 12 border options, including combinations various types, such as single top and double bottom. The first option in the palette removes all border formats in the selected cell or range. Other options show a miniature view of the location of a border or combination of borders.

As a practice, try the small example below. To break a line, press the Enter key while holding down Alt.

Applying color and patterns

Use the View tab of the Format Cells dialog box to apply colors and patterns to selected cells. This tab contains the current palette and a drop-down pattern palette.

The Color palette on the View tab allows you to set a background for selected cells. If you select a color in the Color panel without selecting a pattern, the specified background color appears in the selected cells. If you select a color from the Color panel and then a pattern from the Pattern drop-down panel, the pattern is overlaid with the background color. The colors in the Pattern drop-down palette control the color of the pattern itself.

Using Fill

The various cell shading options provided by the View tab can be used to visually design your worksheet. For example, shading can be used to highlight summary data or to draw attention to worksheet cells where data is entered. To make it easier to view numerical data by row, you can use the so-called “stripe fill”, when rows of different colors alternate.

The cell background should be a color that makes the text and numeric values ​​displayed in the default black font easy to read.

Excel allows you to add a background image to your worksheet. To do this, select the “Sheet” - “Background” command from the “Format” menu. A dialog box will appear allowing you to open graphic file, stored on disk. This graphic is then used as the background of the current worksheet, much like watermarks on a piece of paper. The graphic image is repeated, if necessary, until the entire worksheet is filled out. You can disable the display of grid lines in a worksheet by selecting the "Options" command from the "Tools" menu and on the "View" tab and unchecking the "Grid" checkbox. Cells that are assigned a color or pattern display only the color or pattern, not the background graphic.

"Excel font|| Excel || Merging cells"

Conditional formatting and merging cells

- Conditional formatting

- Merging cells

- Conditional formatting

Conditional formatting allows you to apply formats to specific cells that remain "sleeping" until the values ​​in those cells reach some reference value.

Select the cells to be formatted, then select the "Conditional Formatting" command from the "Format" menu. The dialog box shown below will appear in front of you.

The first combo box in the Conditional Formatting dialog box allows you to choose whether the condition should be applied to the value or the formula itself. Typically, you select the Value option, which causes the format to be applied based on the values ​​of the selected cells. The "Formula" parameter is used in cases where you need to set a condition that uses data from unselected cells, or you need to create a complex condition that includes several criteria. In this case, you should enter a logical formula that accepts the value TRUE or FALSE in the second combo box. The second combo box is used to select the comparison operator used to set the formatting condition. The third field is used to specify the value to compare. If the "Between" or "Outside" operator is selected, an additional fourth field appears in the dialog box. In this case, you must specify the lower and upper values ​​in the third and fourth fields.

After setting the condition, click the "Format" button. The Format Cells dialog box opens, allowing you to select the font, borders, and other format attributes that should be applied when the specified condition is met.

In the example below, the format is set to: font color - red, font - bold. Condition: if the value in the cell exceeds "100".

Sometimes it is difficult to determine where conditional formatting has been applied. To select all cells in the current worksheet that have conditional formatting, choose Go from the Edit menu, click the Select button, then select the Conditional Formats radio button.

To remove a formatting condition, select the cell or range, and then from the Format menu, choose Conditional Formatting. Specify the conditions you want to remove and click OK.

Merging cells

The grid is a very important design element of a spreadsheet. Sometimes it is necessary to format the grid in a special way to achieve the desired effect. Excel allows you to merge cells, which gives the grid new capabilities that you can use to create clearer forms and reports.

When cells are merged, a single cell is formed whose dimensions match the dimensions of the original selection. The merged cell receives the address of the top left cell of the original range. The remaining original cells practically cease to exist. If a formula contains a reference to such a cell, it is treated as empty, and depending on the type of formula, the reference may return null or an error value.

To merge cells, do the following:

Select source cells;

In the "Format" menu, select the "Cells" command;

On the "Alignment" tab of the "Format Cells" dialog box, select the "Merge Cells" checkbox;

Click "OK".

If you have to use this command quite often, then it is much more convenient to “pull” it onto the toolbar. To do this, select the "Tools" - "Settings..." menu, in the window that appears, go to the "Commands" tab and select the "Formatting" category in the right window. In the left "Commands" window, using the scroll bar, find "Merge Cells" and drag this icon (using left button mouse) to the Formatting toolbar.

Merging cells has a number of consequences, and the most obvious is breaking the grid, one of the main attributes of spreadsheets. Some nuances should be taken into account:

If only one cell in the selection range is non-empty, merging repositions its contents in the merged cell. So, for example, when merging cells in the range A1:B5, where cell A2 is non-empty, this cell will be moved to the merged cell A1;

If multiple cells in the selected range contain values ​​or formulas, merging only retains the contents of the top-left cell and repositions them in the merged cell. The contents of the remaining cells are deleted. If you need to save data in these cells, then before merging you should add them to the upper left cell or move them to another location outside the selection;

If a merge range contains a formula that is repositioned in a merged cell, the relative references in the merged cell are adjusted automatically;

United Excel cells can be copied, cut and pasted, deleted, and dragged around just like regular cells. After you copy or move a merged cell, it occupies the same number of cells in the new location. In place of the cut or deleted merged cell, the standard cell structure is restored;

When merging cells, all borders are removed except the outer border of the entire selection, as well as the border that is applied to any edge of the entire selection.

"Borders and Shading || Excel || Editing"

Cutting and pasting cells intoExcel

Cut and paste

Cut and paste rules

Inserting cut cells

Cut and paste

You can use the Edit menu's Cut and Paste commands to move values ​​and formats from one location to another. Unlike the Delete and Clear commands, which remove cells or their contents, the Cut command places a movable dotted frame around the selected cells and places a copy of the selection on the clipboard, which saves the data so it can be pasted elsewhere.

After selecting the range into which you want to move the cut cells, the Paste command places them in a new location, clears the contents of the cells inside the moving frame, and deletes the moving frame.

When you use the Cut and Paste commands to move a range of cells, Excel clears the contents and formats in the cut range and moves them into the paste range.

This causes Excel to adjust all formulas outside the cut area that reference those cells.

Cut and paste rules

The selected cut area must be a single rectangular block of cells;

When you use the Cut command, you paste only once. To paste selected data into several places, you must use a combination of the “Copy” - “Clear” commands;

It is not necessary to select the entire paste range before using the Paste command. When you select a single cell as the paste range, Excel expands the paste area to match the size and shape of the cut area. The selected cell is considered to be the top left corner of the insertion area. If you select the entire paste area, you need to make sure that the selected range is the same size as the cut area;

When you use the Paste command, Excel replaces the contents and formats in all existing cells in the paste range. If you don't want to lose the contents of existing cells, make sure that there are enough empty cells in the worksheet below and to the right of the selected cell, which will end up in the upper left corner of the screen area, to accommodate the entire clipped area.

Inserting cut cells

When you use the Paste command, Excel pastes the cut cells into the selected area of ​​the worksheet. If the selected area already contains data, it is replaced with the inserted values.

In some cases, you can paste the contents of the clipboard between cells instead of placing it in existing cells. To do this, use the "Cut Cells" command of the "Insert" menu instead of the "Paste" command of the "Edit" menu.

The "Cut Cells" command replaces the "Cells" command and appears only after data has been deleted to the clipboard.

For example, in the example below, cells A5:A7 were initially cut (the "Cut" command of the "Edit" menu); then it was done active cell A1; then the "Cut Cells" command was executed from the "Insert" menu.

“Filling the Rows || Excel || Excel Functions"

Functions. Function syntaxExcel

Function syntax

Using Arguments

Argument types

In lesson No. 4 we already made our first acquaintance with Excel functions. Now it's time to take a closer look at this powerful spreadsheet tool.

Excel functions are special, pre-created formulas that allow you to perform complex calculations quickly and easily. They can be compared with special keys on calculators designed to calculate square roots, logarithms, etc.

Excel has several hundred built-in functions that perform a wide range of different calculations. Some functions are equivalent to long mathematical formulas that you can do yourself. And some functions cannot be implemented in the form of formulas.

Function syntax

Functions consist of two parts: the function name and one or more arguments. A function name, such as SUM, describes the operation that the function performs. Arguments specify the values ​​or cells used by the function. In the formula below: SUM is the name of the function; B1:B5 - argument. This formula sums the numbers in cells B1, B2, B3, B4, B5.

SUM(B1:B5)

An equal sign at the beginning of a formula means that it is the formula that has been entered, not the text. If the equal sign is missing, Excel will treat the input simply as text.

The function argument is enclosed in parentheses. The opening parenthesis marks the beginning of the argument and is placed immediately after the function name. If you enter a space or other character between the name and the opening parenthesis, the cell will display the erroneous value #NAME? Some functions have no arguments. Even then, the function must contain parentheses:

Using Arguments

When multiple arguments are used in a function, they are separated from each other by a semicolon. For example, the following formula indicates that you need to multiply the numbers in cells A1, A3, A6:

PRODUCT(A1,A3,A6)

You can use up to 30 arguments in a function, as long as the total length of the formula does not exceed 1024 characters. However, any argument can be a range containing any number of worksheet cells. For example:

Argument types

In the previous examples, all arguments were cell or range references. But you can also use numeric, text and boolean values, range names, arrays, and error values. Some functions return values ​​of these types, which can later be used as arguments in other functions.

Numeric values

Function arguments can be numeric. For example, the SUM function in the following formula adds the numbers 24, 987, 49:

SUM(24;987;49)

Text values

Text values ​​can be used as function arguments. For example:

TEXT(TDATE();"D MMM YYYY")

In this formula, the second argument to the TEXT function is text and specifies a pattern for converting the decimal date value returned by the TDATE(NOW) function into a character string. The text argument can be a character string enclosed in double quotes, or a reference to a cell that contains text.

Boolean values

Arguments to some functions can only accept the logical values ​​TRUE or FALSE. A Boolean expression returns TRUE or FALSE to the cell or formula that contains the expression. For example:

IF(A1=TRUE;"Increase";"Decrease")&"price"

You can specify the name of the range as an argument to the function. For example, if the cell range A1:A5 is named "Debit" (Insert-Name-Assign), then you can use the formula to calculate the sum of the numbers in cells A1 through A5

SUM(Debit)

Using Different Argument Types

You can use arguments of different types in one function. For example:

AVERAGE(Debit;C5;2*8)

"Inserting Cells || Excel || Entering Excel Functions"

Entering Functions in a WorksheetExcel

You can enter functions in a worksheet directly from the keyboard or by using the Function command on the Insert menu. When entering a function from the keyboard, it is better to use lowercase letters. When you've finished entering a function, Excel will change the letters in the function name to uppercase if it was entered correctly. If the letters do not change, then the function name was entered incorrectly.

If you select a cell and choose Function from the Insert menu, Excel displays the Function Wizard dialog box. You can achieve this a little faster by pressing the key with the function icon in the formula bar.

You can also open this window using the "Insert Function" button on the standard toolbar.

In this window, first select a category from the "Category" list and then select the desired function from the "Function" alphabetical list.

Excel will enter an equal sign, the name of the function, and a pair of parentheses. Excel will then open a second Function Wizard dialog box.

The second window of the Function Wizard dialog contains one field for each argument of the selected function. If the function has variable number arguments, this dialog box expands when adding additional arguments. A description of the argument whose field contains the insertion point is displayed at the bottom of the dialog box.

To the right of each argument field is its current value. This is very useful when you use links or names. The current value of the function is displayed at the bottom of the dialog window.

Click "OK" and the created function will appear in the formula bar.

"Function Syntax || Excel || Mathematical functions"

Mathematical functionsExcel

Here are the most commonly used Excel mathematical functions (quick reference). More information about functions can be found in the Function Wizard dialog box and in the Excel help system. In addition, many mathematical functions are included in the Analysis Package add-on.

SUM function

Functions EVEN and ODD

Functions OKRVBOTTOM, OKRVUP

INTEGER and SELECT functions

RAND and RANDBETWEEN functions

PRODUCT function

REST function

SQRT function

NUMBERCOMB function

ISNUMBER function

LOG function

LN function

EXP function

PI function

RADIANS and DEGREES function

SIN function

COS function

TAN function

SUM function

The SUM function adds up a set of numbers. This function has the following syntax:

SUM(numbers)

The number argument can have up to 30 elements, each of which can be a number, a formula, a range, or a reference to a cell that contains or returns a numeric value. The SUM function ignores arguments that refer to empty cells, text values, or Boolean values. The arguments do not have to form contiguous ranges of cells. For example, to get the sum of the numbers in cells A2, B10, and cells C5 through K12, enter each reference as a separate argument:

SUM(A2;B10;C5:K12)

Functions ROUND, ROUNDDOWN, ROUNDUP

The ROUND function rounds the number specified by its argument to the specified number of decimal places and has the following syntax:

ROUND(number,number_digits)

number can be a number, a reference to a cell that contains a number, or a formula that returns a numeric value. The number_digits argument, which can be any positive or negative integer, specifies how many digits will be rounded. Setting number_digits to a negative argument rounds to the specified number of places to the left of the decimal point, and setting number_digits to 0 rounds to the nearest integer. Excel numbers that are less than 5 are deficient (down), and numbers that are greater than or equal to 5 are excess (up).

The ROUNDDOWN and ROUNDUP functions have the same syntax as the ROUND function. They round values ​​down (under) or up (over).

Functions EVEN and ODD

You can use the EVEN and ODD functions to perform rounding operations. The EVEN function rounds a number up to the nearest even integer. The ODD function rounds a number up to the nearest odd integer. Negative numbers are rounded down rather than up. The functions have the following syntax:

EVEN(number)

ODD(number)

Functions OKRVBOTTOM, OKRVUP

The FLOOR and CEILING functions can also be used to perform rounding operations. The OKROWN function rounds a number down to the nearest multiple of a given factor, and the OKRUP function rounds up a number to the nearest multiple for a given factor. These functions have the following syntax:

OKRVDOWN(number,multiplier)

OVERTOP(number,multiplier)

The number and factor values ​​must be numeric and have the same sign. If they have various signs, then an error will be generated.

INTEGER and SELECT functions

The INT function rounds a number down to the nearest integer and has the following syntax:

INTEGER(number)

The number argument is the number for which you want to find the next smallest integer.

Consider the formula:

INTEGER(10.0001)

This formula will return 10, just like the following:

INTEGER(10,999)

The TRUNC function discards all digits to the right of the decimal point, regardless of the sign of the number. The optional number_digits argument specifies the position after which the truncation occurs. The function has the following syntax:

SELECT(number,number_digits)

If the second argument is omitted, it is assumed to be zero. The following formula returns the value 25:

OTBR(25,490)

The ROUND, INTEGER, and SELECT functions remove unnecessary decimal places, but they work differently. The ROUND function rounds up or down to a specified number of decimal places. The INTEGER function rounds down to the nearest integer, and the RUN function discards decimal places without rounding. The main difference between the INT and TRAN functions is how they handle negative values. If you use the value -10.900009 in the INTEGER function, the result is -11, but if you use the same value in the INTEGER function, the result is -10.

RAND and RANDBETWEEN functions

The RAND function generates random numbers evenly distributed between 0 and 1, and has the following syntax:

The RAND function is one of the EXCEL functions that has no arguments. As with all functions that take no arguments, you must enter parentheses after the function name.

The value of the RAND function changes each time the worksheet is recalculated. If you set calculations to update automatically, the value of the RAND function changes each time you enter data into that worksheet.

The RANDBETWEEN function, which is available if the Analysis Package add-in is installed, provides more functionality than RAND. For the RANDBETWEEN function, you can specify the interval of random integer values ​​to be generated.

Function syntax:

RANDBETWEEN(start,end)

The start argument specifies the smallest number that can return any integer from 111 to 529 (including both):

RANDBETWEEN(111,529)

PRODUCT function

The PRODUCT function multiplies all numbers specified by its arguments and has the following syntax:

PRODUCT(number1,number2...)

This function can have up to 30 arguments. Excel ignores any empty cells, text, or Boolean values.

REST function

The ROD (MOD) function returns the remainder of a division and has the following syntax:

REMAINDER(number,divisor)

The value of the REMAIN function is the remainder obtained when the argument number is divided by the divisor. For example, next function will return the value 1, that is, the remainder obtained when dividing 19 by 14:

REST(19;14)

If the number is less than the divisor, then the value of the function is equal to the number argument. For example, the following function will return the number 25:

REST(25,40)

If the number is exactly divisible by the divisor, the function returns 0. If the divisor is 0, the MOD function returns an error value.

SQRT function

The SQRT function returns the positive square root of a number and has the following syntax:

SQRT(number)

number must be a positive number. For example, the following function returns the value 4:

ROOT(16)

If the number is negative, SQRT returns an erroneous value.

NUMBERCOMB function

The COMBIN function determines the number of possible combinations or groups for a given number of elements. This function has the following syntax:

NUMBER(number, number_selected)

number is the total number of elements, and number_selected is the number of elements in each combination. For example, to determine the number of 5-player teams that can be formed from 10 players, the formula is:

NUMBER(10;5)

The result will be 252. That is, 252 teams can be formed.

ISNUMBER function

The ISNUMBER function determines whether a value is a number and has the following syntax:

ISNUMBER(value)

Let's say you want to know whether the value in cell A1 is a number. The following formula returns TRUE if cell A1 contains a number or a formula that returns a number; otherwise it returns FALSE:

ENUMBER(A1)

LOG function

The LOG function returns the logarithm of a positive number to a given base. Syntax:

LOG(number;base)

If the base argument is not specified, Excel will assume it is 10.

LN function

The LN function returns the natural logarithm of a positive number given as an argument. This function has the following syntax:

EXP function

The EXP function calculates the value of a constant raised to a given power. This function has the following syntax:

The EXP function is the inverse of LN. For example, let cell A2 contain the formula:

Then the following formula returns the value 10:

PI function

The PI function returns the value of the pi constant to 14 decimal places. Syntax:

RADIANS and DEGREES function

Trigonometric functions use angles expressed in radians rather than degrees. The measurement of angles in radians is based on the constant pi and 180 degrees is equal to pi radians. Excel provides two functions, RADIANS and DEGREES, to make working with trigonometric functions easier.

You can convert radians to degrees using the DEGREES function. Syntax:

DEGREES(angle)

Here - angle is a number representing an angle measured in radians. To convert degrees to radians, use the RADIANS function, which has the following syntax:

RADIANS(angle)

Here - angle is a number representing an angle measured in degrees. For example, the following formula returns the value 180:

DEGREES(3.14159)

However, the following formula returns the value 3.14159:

RADIANS(180)

SIN function

The SIN function returns the sine of an angle and has the following syntax:

SIN(number)

COS function

The COS function returns the cosine of an angle and has the following syntax:

COS(number)

Here the number is the angle in radians.

TAN function

The TAN function returns the tangent of an angle and has the following syntax:

TAN(number)

Here the number is the angle in radians.

"Inputting functions || Excel || Text functions"

Text functionsExcel

Here are the most commonly used Excel text functions (quick reference). More information about functions can be found in the Function Wizard dialog box and in the Excel help system.

TEXT function

RUBLE function

LENGTH function

CHARACTER and CHARACTER CODE function

Functions SPACEBEL and PECHSIMV

COINCIDENT function

Functions ITEXT and ENETEXT

Text functions convert numeric text values ​​to numbers and numeric values ​​to character strings (text strings), and also allow you to perform various operations on character strings.

TEXT function

The TEXT function converts a number to a text string with specified format. Syntax:

TEXT(value, format)

The value argument can be any number, formula, or cell reference. The format argument determines how the returned string is displayed. You can use any of the formatting characters except the asterisk to set the required format. The use of the General format is not allowed. For example, the following formula returns the text string 25,25:

TEXT(101/4,"0.00")

RUBLE function

The DOLLAR function converts a number into a string. However, RUBLE returns a string in currency format with the specified number of decimal places. Syntax:

RUBLE(number, number_characters)

Excel will round the number if necessary. If the number_characters argument is omitted, Excel uses two decimal places, and if this argument is negative, the returned value is rounded to the left of the decimal point.

LENGTH function

The LEN function returns the number of characters in a text string and has the following syntax:

LENGTH(text)

The text argument must be a character string enclosed in double quotes or a cell reference. For example, the following formula returns the value 6:

DLstr("head")

The LENGTH function returns the length of the displayed text or value, not the cell's stored value. In addition, it ignores leading zeros.

CHARACTER and CHARACTER CODE function

Any computer uses numeric codes to represent characters. The most common character encoding system is ASCII. In this system, numbers, letters and other symbols are represented by numbers from 0 to 127 (255). The CHAR and CODE functions deal specifically with ASCII codes. The CHAR function returns the character that matches the given numeric ASCII code, and the CHAR CODE function returns the ASCII code for the first character of its argument. Function syntax:

CHAR(number)

CODE(text)

If you enter a character as a text argument, be sure to enclose it in double quotes; otherwise, Excel will return an incorrect value.

Functions SPACEBEL and PECHSIMV

Often leading and trailing spaces prevent values ​​from being sorted correctly in a worksheet or database. If you use text functions to work with worksheet text, extra spaces may prevent formulas from working correctly. The TRIM function removes leading and trailing spaces from a string, leaving only one space between words. Syntax:

SPACE(text)

The CLEAN function is similar to the SPACE function except that it removes all non-printing characters. The PRINTCHARM function is especially useful when importing data from other programs because some imported values ​​may contain non-printing characters. These symbols may appear on worksheets as small squares or vertical bars. The PRINT CHARACTER function allows you to remove non-printing characters from such data. Syntax:

PECHSIMV(text)

COINCIDENT function

The EXACT function compares two strings of text for complete identity, taking into account the case of letters. Differences in formatting are ignored. Syntax:

COICH(text1,text2)

If the arguments text1 and text2 are case-sensitive, the function returns TRUE; otherwise, FALSE. The arguments text1 and text2 must be character strings enclosed in double quotes, or references to cells that contain text.

CAPITAL, LOWER LOWER, and PROPIT functions

Excel has three functions that allow you to change the case of letters in text strings: UPPER, LOWER, and PROPER. The CAPITAL function converts all letters in a text string to uppercase and the LOWER function converts all letters to lowercase. The PROPER function capitalizes the first letter of each word and all letters immediately following non-letter characters; all other letters are converted to lowercase. These functions have the following syntax:

CAPITAL(text)

LOW(text)

PROPNACH(text)

When working with existing data, quite often a situation arises when you need to modify the original values ​​themselves to which text functions are applied. You can enter the function in the same cells where these values ​​are located, since the entered formulas will replace them. But you can create temporary formulas with text function in the free cells in the same row and copy the result to the clipboard. To replace the original values ​​with modified values, select the original text cells, select Paste Special from the Edit menu, select the Values ​​radio button, and click OK. You can then delete the temporary formulas.

Functions ITEXT and ENETEXT

The ISTEXT and ISNOTEXT functions check whether a value is text. Syntax:

ETEXT(value)

NETTEXT(value)

Let's say we need to determine whether the value in cell A1 is text. If cell A1 contains text or a formula that returns text, you can use the formula:

ETEXT(A1)

In this case, Excel returns the Boolean value TRUE. Likewise, if you use the formula:

ENETEXT(A1)

Excel returns the Boolean value FALSE.

"Mathematical functions || Excel || String functions"

FunctionsExcelfor working with row elements

FIND and SEARCH functions

RIGHT and LEFT functions

PSTR function

REPLACE and SUBSTITUTE functions

REPEAT function

CONNECT function

The following functions find and return parts of text strings or construct large strings from small ones: FIND, SEARCH, RIGHT, LEFT, MID, SUBSTITUTE, REPEAT, REPLACE, CONCATENATE.

FIND and SEARCH functions

The FIND and SEARCH functions are used to determine the position of one text string within another. Both functions return the number of the character from which the first occurrence of the search string begins. The two functions work the same except that the FIND function is case-sensitive and the SEARCH function allows wildcard characters. The functions have the following syntax:

FIND(search_text, viewed_text, start_position)

SEARCH(search_text, viewed_text, start_position)

The search_text argument specifies the text string to be found, and the search_text argument specifies the text to be searched. Any of these arguments can be a character string enclosed in double quotes or a cell reference. The optional argument start_position specifies the position in the text being viewed at which the search begins. The start_position argument should be used when lookup_text contains multiple occurrences of the searched text. If this argument is omitted, Excel returns the position of the first occurrence.

These functions return an error value when the search_text is not contained in the searched text, or the start_position is less than or equal to zero, or the start_position exceeds the number of characters in the search text, or the start_position more position last occurrence of the search text.

For example, to determine the position of the letter "g" in the line "Garage Door", you need to use the formula:

FIND("w","Garage door")

This formula returns 5.

If you don't know the exact character sequence of the text you're looking for, you can use the SEARCH function and include the wildcard characters: question mark (?) and asterisk (*) in the search_text string. A question mark matches one randomly typed character, and an asterisk replaces any sequence of characters at a specified position. For example, to find the position of the names Anatoly, Alexey, Akakiy in the text located in cell A1, you need to use the formula:

SEARCH("A*y";A1)

Functions RIGHT and LEFT

The RIGHT function returns the rightmost characters of the argument string, while the LEFT function returns the first (left) characters. Syntax:

RIGHT(text, number_characters)

LEFT(text, number_of_characters)

The number_of_characters argument specifies the number of characters to be extracted from the text argument. These functions respect spaces, so if the text argument contains spaces at the beginning or end of the line, you should use the SPACE function in the function arguments.

The character_count argument must be greater than or equal to zero. If this argument is omitted, Excel treats it as 1. If number_characters is greater than the number of characters in the text argument, then the entire argument is returned.

PSTR function

The MID function returns a specified number of characters from a string of text, starting at a specified position. This function has the following syntax:

PSTR(text, start_position, number of characters)

text is a text string containing the characters to be extracted, start_position is the position of the first character to be extracted from the text (relative to the start of the string), and char_count is the number of characters to extract.

REPLACE and SUBSTITUTE functions

These two functions replace characters in text. The REPLACE function replaces part of a text string with another text string and has the syntax:

REPLACE(old_text, start_position, number of characters, new_text)

The argument old_text is a text string, and the characters must be replaced. The next two arguments specify the characters to be replaced (relative to the beginning of the line). The new_text argument specifies the text string to be inserted.

For example, cell A2 contains the text "Vasya Ivanov". To place the same text in cell A3, replacing the name, you need to insert the following function into cell A3:

REPLACE(A2;1;5;"Petya")

In the SUBSTITUTE function, the starting position and number of characters to be replaced are not specified, but the text to be replaced is explicitly specified. The SUBSTITUTE function has the following syntax:

SUBSTITUTE (text, old_text, new_text, occurrence_number)

The occurrence_number argument is optional. It instructs Excel to replace only the specified occurrence of the string old_text.

For example, cell A1 contains the text "Zero less than eight." We need to replace the word "zero" with "zero".

SUBSTITUTE(A1,"o","y";1)

The number 1 in this formula indicates that only the first "o" in the row of cell A1 needs to be changed. If occurrence_number is omitted, Excel replaces all occurrences of the string old_text with the string new_text.

REPEAT function

The REPEAT function allows you to fill a cell with a string of characters repeated a specified number of times. Syntax:

REPEAT(text,number_repetitions)

The text argument is a multiplied character string enclosed in quotation marks. The repeat_number argument specifies the number of times the text should be repeated. If repeat_count is 0, the REPEAT function leaves the cell empty, and if it is not an integer, the function discards decimal places.

CONNECT function

The CONCATENATE function is the equivalent of the text operator & and is used to concatenate strings. Syntax:

CONCATENATE(text1,text2,...)

You can use up to 30 arguments in a function.

For example, cell A5 contains the text "first half of the year", the following formula returns the text "Total for the first half of the year":

CONCATENATE("Total for ";A5)

"Text functions || Excel || Logical functions"

Logic functionsExcel

IF function

Functions AND, OR, NOT

Nested IF functions

Functions TRUE and FALSE

EMPTY function

Boolean expressions are used to write conditions that compare numbers, functions, formulas, text, or Boolean values. Any logical expression must contain at least one comparison operator, which defines the relationship between the elements of the logical expression. Below is a list of Excel comparison operators

> More

< Меньше

>= Greater than or equal to

<= Меньше или равно

<>Not equal

The result of a logical expression is the logical value TRUE (1) or the logical value FALSE (0).

IF function

The IF function has the following syntax:

IF(logical_expression, value_if_true, value_if_false)

The following formula returns 10 if the value in cell A1 is greater than 3, and 20 otherwise:

IF(A1>3,10,20)

You can use other functions as arguments to the IF function. The IF function can use text arguments. For example:

IF(A1>=4; “Passed the test”; “Did not pass the test”)

You can use text arguments in the IF function so that if the condition is not met, it will return an empty string instead of 0.

For example:

IF(SUM(A1:A3)=30,A10,"")

The boolean_expression argument of the IF function can contain a text value. For example:

IF(A1="Dynamo";10;290)

This formula returns 10 if cell A1 contains the string "Dynamo" and 290 if it contains any other value. The match between the text values ​​being compared must be exact, but not case-sensitive. AND, OR, NOT functions

Functions AND (AND), OR (OR), NOT (NOT) - allow you to create complex logical expressions. These functions work in combination with simple comparison operators. The AND and OR functions can have up to 30 Boolean arguments and have the syntax:

AND(boolean_value1;boolean_value2...)

OR(boolean_value1,boolean_value2...)

The NOT function has only one argument and the following syntax:

NOT(boolean_value)

Arguments to the AND, OR, and NOT functions cannot be Boolean expressions, arrays, or cell references containing Boolean values.

Let's give an example. Let Excel return the text "Passed" if the student has a GPA greater than 4 (cell A2) and a class absence rate of less than 3 (cell A3). The formula will look like:

IF(AND(A2>4,A3<3);"Прошел";"Не прошел")

Even though the OR function has the same arguments as the AND function, the results are completely different. So, if in the previous formula we replace the AND function with OR, then the student will pass if at least one of the conditions is met (average score more than 4 or absenteeism less than 3). Thus, the OR function returns the logical value TRUE if at least one of the logical expressions is true, and the AND function returns the logical value TRUE only if all the logical expressions are true.

The function does NOT change the value of its argument to the opposite boolean value and is usually used in combination with other functions. This function returns the logical value TRUE if the argument is FALSE and the logical value FALSE if the argument is TRUE.

Nested IF functions

Sometimes it can be very difficult to solve a logic problem using only comparison operators and AND, OR, NOT functions. In these cases, you can use nested IF functions. For example, the following formula uses three IF functions:

IF(A1=100;"Always";IF(AND(A1>=80;A1<100);"Обычно";ЕСЛИ(И(А1>=60;A1<80);"Иногда";"Никогда")))

If the value in cell A1 is an integer, the formula reads: "If the value in cell A1 is 100, return the string "Always." Otherwise, if the value in cell A1 is between 80 and 100, return "Usually." otherwise, if the value in cell A1 is between 60 and 80, return the row "Sometimes". AND, if none of these conditions are true, return the row "Never". A total of 7 levels of nesting of IF functions are allowed.

Functions TRUE and FALSE

The TRUE and FALSE functions provide an alternative way to write the Boolean values ​​TRUE and FALSE. These functions have no arguments and look like this:

For example, cell A1 contains a Boolean expression. Then the following function will return the value "Pass" if the expression in cell A1 evaluates to TRUE:

IF(A1=TRUE();"Pass";"Stop")

Otherwise, the formula will return "Stop".

EMPTY function

If you need to determine whether a cell is empty, you can use the ISBLANK function, which has the following syntax:

EMPTY(value)

"String functions || Excel || Excel 2007"