Video lessons Microsoft Excel for Beginners from Andrey Sukhovoy. How to work in Excel (program): tips for beginners How to work with calculations in Excel

Start creating formulas and using built-in functions to perform calculations and solve problems.

Important: The calculated formula results and some Excel worksheet functions may differ slightly on computers running Windows with x86 or x86-64 architecture and computers running Windows RT with ARM architecture.

Create a formula that references values ​​in other cells

View formula

Entering a formula containing a built-in function

Downloading the book "Textbook on formulas"

We have prepared for you a book Getting Started with Formulas, which is available for download. If you're new to Excel or even have some experience with Excel, this tutorial will help you become familiar with the most common formulas. Thanks to clear examples, you will be able to calculate the sum, quantity, average value and substitute data just as well as professionals.

Formula Details

To learn more about specific formula elements, review the relevant sections below.

Parts of an Excel formula

A formula can also contain one or more elements such as functions, links, operators And constants.

additional information

You can always ask a question from the Excel Tech Community, ask for help in the Answers community, or suggest a new feature or improvement to the website

- 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 a closing parenthesis, Excel displays the last pair of parentheses in bold (or a different color), which is very useful if you have a large number of parentheses in your 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 the Del and Backspace keys, if necessary. Changes are committed by pressing the Enter key.

Using text in formulas

You can perform mathematical operations on text values ​​if the 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 you don’t know exactly the function that needs to be applied at the moment, 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 in 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.

You cannot cut, clear, or edit part of an array, but you can assign different formats to 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" button

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.

To quickly copy formats from selected cells to 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 affects only the appearance, not 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.

Because this format affects numeric values ​​in the same way as text, the number may not look exactly as intended. 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.

The Height vertical alignment format does essentially the same thing as its Width counterpart, except that it aligns the cell's value to its top and bottom edges rather than 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

Cell borders and 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 corresponding button next to that area. If you have multiple cells selected in a worksheet, the Border tab makes the Internal button available so you can add borders between the selected cells. Additionally, additional markers appear in the viewing area on the sides of the selection, indicating 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 of different types, such as a single top border and a double bottom border. 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 a 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 is red, font is 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 choose Conditional Formatting from the Format menu. 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, use the scroll bar to find "Merge Cells" and drag this icon (using the left mouse button) to the "Format" 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 selected range is non-blank, 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;

Excel merged cells can be copied, cut and pasted, deleted, and dragged, 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 delete 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 into another place.

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 cell A1 was made active; 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 to 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 the equivalent of 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 ​​as arguments. 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 a function has a variable number of arguments, this dialog box expands when additional arguments are supplied. 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 OKRVDOWN, 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 OKRVDOWN, 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 different signs, 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, the following function will return the value 1, which is the remainder obtained when 19 is divided 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:

NUMBERCOMB(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 into a text string with a 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 to 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 can 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 PREPCHYMB 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 PRINTCHARACTERS 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:

COINCIDENT(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.

UPPER, LOWER, and PROP 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 a text function in empty cells on the same row and copy the result to the clipboard. To replace the original values ​​with the modified ones, 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

Functions RIGHT and LEFT

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 identically 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 is greater than the number of characters in the search text, or the start_position is greater than the position of the 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 repetition_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","Failed 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 conjunction 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 reverse 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 neither 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"

Formulas in Excel are one of the most important advantages of this editor. Thanks to them, your capabilities when working with tables increase several times and are limited only by your existing knowledge. You can do anything. At the same time, Excel will help you at every step - there are special tips in almost any window.

To create a simple formula, just follow the following instructions:

1. Make any cell active. Click on the formula input line. Put an equal sign.

1. Enter any expression. Can be used as numbers

In this case, the affected cells are always highlighted. This is done so that you do not make a mistake with your choice. It is easier to see the error visually than in text form.

What does the formula consist of?

Let's take the following expression as an example.

It consists of:

• symbol “=” – any formula begins with it;
• "SUM" function;
• function argument "A1:C1" (in this case it is an array of cells from "A1" to "C1");
• operator “+” (addition);
• references to cell "C1";
• operator “^” (exponentiation);
• constant "2".

Using Operators

Operators in the Excel editor indicate which operations need to be performed on specified formula elements. The calculation always follows the same order:

• brackets;
• exhibitors;
• multiplication and division (depending on the sequence);
• addition and subtraction (also depending on the sequence).

Arithmetic

These include:

• addition – “+” (plus);

• negation or subtraction – “-” (minus);

If you put a “minus” in front of a number, it will take on a negative value, but in absolute value it will remain exactly the same.

• multiplication - "*";

• division "/";

• percent "%";

• exponentiation – “^”.

Comparison Operators

These operators are used to compare values. The operation returns TRUE or FALSE. These include:

• “equals” sign – “=”;

• “greater than” sign – “>”;

• "less than" sign - "<»;