How to enter formulas in Excel. How to write a formula in Excel? Education. The most necessary formulas
Formulas in Excel are its main essence, that is why this program was created by Microsoft. Formulas allow you to calculate cell values based on data from other cells, and if the source data changes, the result of the calculations in the cell where the formula is written will be recalculated automatically!
Creating Formulas in Excel
Let's look at how the formulas work using the simplest example - the sum of two numbers. Let the number 2 be entered in one Excel cell, and 3 in another. It is necessary that the sum of these numbers appears in the third cell.
The sum of 2 and 3 is, of course, 5, but you don’t need to enter the 5 manually into the next cell, otherwise the meaning of the calculations in Excel is lost. You must enter the sum formula in the cell with the total and then the result will be calculated automatically by the program.
In the example, the calculation looks simple, but when the numbers are large or fractional, you simply cannot do without a formula.
Formulas in Excel can contain arithmetic operations (addition +, subtraction -, multiplication *, division /), coordinates of source data cells (both individually and range) and calculation functions.
Consider the formula for the sum of numbers in the example above:
SUM(A2;B2)
Every formula begins with an equal sign. If you want to add a formula to a cell by writing it manually, then this character should be written first.
Next in the example is the SUM function, which means that it is necessary to sum up some data, and already in parentheses of the function, separated by a semicolon, some arguments are indicated, in this case the coordinates of the cells (A2 and B2), the values of which must be added and the result placed in the cell where the formula is written. If you needed to add three cells, you could write three arguments to the SUM function, separating them with a semicolon, for example:
SUM(A4;B4;C4)
When you need to add a large number of cells, specifying each of them in the formula will take a lot of time, so instead of simply listing, you can use specifying a range of cells:
SUM(B2:B7)
A range of cells in Excel is specified using the coordinates of the first and last cells, separated by a colon. This example adds the values of cells starting from cell B2 to cell B7.
Functions in formulas can be connected and combined as needed to obtain the desired result. For example, the task is to add three numbers and, depending on whether the result is less than 100 or more, multiply the sum by a factor of 1.2 or 1.3. The following formula will help solve the problem:
IF(SUM(A2:C2)
Let's look at the solution to the problem in more detail. Two functions IF and SUM were used. The IF function always has three arguments: the first is the condition, the second is the action if the condition is true, the third is the action if the condition is false. We remind you that arguments are separated by semicolons.
IF(condition; true; false)
The condition indicates that the sum of the range of cells A2:C2 is less than 100. If, during the calculation, the condition is met and the sum of the cells in the range is equal to, for example, 98, then Excel will perform the action specified in the second argument of the IF function, i.e. SUM(A2:C2)*1.2. If the sum exceeds the number 100, then the action in the third argument of the IF function will be executed, i.e. SUM(A2:C2)*1.3.
Built-in functions in Excel
There are a huge number of functions in Excel and it is simply impossible to know everything. You can remember some frequently used ones, but you will need some only occasionally and it is very difficult to remember their name and especially the form of recording.
But Excel has a standard way of inserting functions with their complete list. If you want to add a function to a cell, click on the cell and select insert function in the main menu. The program will display a list of functions and you can select the one that is necessary to solve the problem.
To insert a function in Excel 2007, select the “Formulas” item in the main menu and click on the “Insert Function” icon, or press the Shift+F3 key combination on your keyboard.
In Excel 2003, a function is inserted through the "Insert" -> "Function" menu. The key combination Shift+F3 works the same way.
An equal sign will appear in the cell on which the cursor was placed, and the “Function Wizard” window will appear on top of the sheet.
Functions in Excel are divided into categories. If you know which category your intended function may belong to, then select selection according to it. Otherwise, select Full Alphabetical Listing. The program will display all available functions in the function list.
Scroll through the list and use the mouse to highlight the name of the function that interests you. Just below the list will appear its recording form, the required arguments and a brief description that will explain to you the purpose of the function. When you find what you need, click on the “OK” button to proceed to specifying the arguments.
In the arguments window there are fields named “Number 1”, “Number 2”, etc. They must be filled with the coordinates of the cells (or ranges) in which you want to take data. You can fill it in manually, but it is much more convenient to click on the table icon at the end of the field in order to indicate the source cell or range.
The arguments window will take a simplified form. Now you need to click on the first source cell with data, and then again on the table icon in the arguments window.
The “Number 1” field will be filled with the coordinates of the selected cell. The same procedure should be done for the “Number 2” field and for the following fields if you have more than two function arguments.
Having filled in all the arguments, you can preview the result of calculating the resulting formula. To make it appear in a cell on the worksheet, click the “OK” button. In the example considered, cell D2 contains the product of the numbers in cells B2 and C2.
The considered method of inserting a function is universal and allows you to add any function from the general list of standard Excel functions.
| Like |
- 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 in 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"
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 and not the stored content 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 the 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 your 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 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 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;
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. The 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, Boolean, 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 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 include 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 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. The RANDBETWEEN function can be used to specify the range 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:
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 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 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 PRINT 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 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 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 the 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)
RIGHT and LEFT functions
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 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"
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 commonly used in everyday life. That is, the formulas themselves have a slightly different appearance 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 quite deeply. But even preliminary knowledge will give any user an understanding of the basic principles of using formulas for different cases.
How to write initial concepts
So, entering formula values in a program is somewhat different from standard operations, the symbols used and the operators used. When solving the problem of how to write a formula in Excel, you need to start from basic concepts that are used in almost all computer systems.
The fact is that the machine does not understand entering a combination like “2 x 2” or putting the common component out of brackets (“2+2)5”). 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, to use additional operators, the sum 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 can not 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 previously 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 a general sense, such calculations are like a simple filter applied to columns when you want to filter out only the exact values rather than the 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 it is not possible to install any specialized software products.
Conclusion
It remains to be said that not all formulas that can be used in the Excel spreadsheet editor have been described here. 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, with 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.
A fairly important function in Excel are formulas, which perform arithmetic and logical operations on data in a table. With their help, you can easily solve mathematical and engineering problems and carry out various calculations, so an advanced user of the program should know how to set a formula in Excel.
All formulas begin with an equal sign (=), this lets the program know that it is a formula that is being entered into the cell. Simple formulas include expressions that calculate sum, difference, multiplication, etc. Arithmetic operators are used to write such expressions:
— “*” - product (=4*3)
— “/” - division (=4/3)
— “+” - addition (=4-3)
— “-” - subtraction (=4+3)
— “^” - exponentiation (=4^3)
- “%” - finding a percentage (to find 4% of 156, use the following expression “=156*4%”, that is, when adding “%” to a number, it is divided by 100 and “4%” is converted to “0.04” ).
Example. Amount formula in Excel:
For cell A1, the expression “=12+3” was entered. The program automatically performed the calculation and indicated in the cell not the entered formula, but the solution to the example. The implementation of other arithmetic operations is done in the same way.
Several operators can be used simultaneously in the formula: “=56 + 56*4%” - this expression adds four percent to the number 56.
To work correctly with operators, you should know the rules of priority:
1) expressions in brackets are counted;
2) after product and division, addition and subtraction are considered;
3) expressions are executed from left to right if they have the same priority.
The formulas “=5*4+8” and “=5*(4+8)” have different meanings, because in the first case the multiplication “5*4” is initially performed, and in the other the expression in brackets “4+8” is calculated "
Excel formula for average value
A useful formula that allows you to find the average of several entered numbers and is denoted “=AVERAGE()”. The average value of the numbers: 5, 10, 8 and 1 is the result of dividing their sum by the quantity, that is, 24 by 4. This function is implemented in Excel in this way:
Using links
Work in the program is not limited to constant values (constants), so formulas can contain not only numbers, but also cell numbers - links. This function will allow you to calculate formulas even when the data in the specified cell changes and is indicated by its letter and number.
Cells A1, A2 contain the numbers 12, 15 relative. When you add the formula “=A1+A2” to cell A3, the value of the sum of the numbers in cells A1 and A2 will appear in it.
If you change the number of any of the cells A1 or A2, the formula will calculate the amount based on the new data and cell A3 will change its value.
Cell range
In Excel, you can operate on a specific range of cells, which makes filling out formulas easier. For example, to calculate the sum of cells from A1 to A6, it is not necessary to enter the sequence “=A1+A2+A3...”, but it will be enough to enter the addition operator “SUM()” and hold down the left mouse button and drag from A1 to A6.
The result of the work will be the formula “=SUM(A1:A6)”.
Text in formulas
To use text in formulas, it must be enclosed in double quotes - “text”. To combine 2 text values, the ampersand operator “&” is used, which connects them into one cell with the assignment of the “text value” type.
To insert a space between words, you need to write it like this: “=A1&” “&A2”.
This operator can combine text and numeric values, so for example, you can quickly fill out a table like this by connecting the numbers 10, 15, 17, 45 and 90 with the text “pcs.”
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.
An example of using the multiplication and equal signsSolution:
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 on different computers, you may have to face the fact that the Excel files needed for your work do not calculate using formulas.
Incorrect cell format or incorrect cell range settings
In Excel, various hashtag (#) errors occur, such as #VALUE!, #REF!, #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 displays the formula in text format (in version 2016).
Examples of errors in formulas In this example, you can see that the contents of cells with different data types are multiplied =C4*D4.
Bug fix: indicating the 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.
An example of fixing errors in Excel Formula display mode enabled
Since in normal mode the calculated values are displayed in the cells, in order to directly see the calculated formulas in Excel there is a mode for displaying all formulas on the sheet. You can enable or disable this mode using the command Show formulas from tab Formulas In chapter Formula dependencies.
Automatic calculation using formulas is disabled
This is possible in files with a large amount of calculations. To prevent a weak computer from slowing 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.
Example of calculating a sum in Excel 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).
Example of using the AutoSum function 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 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.
Decreasing bit depth does not round the number To round a number according to mathematical rules, you must use the built-in function =ROUND(number,number_of_digits).
Mathematical rounding of a number using the built-in function You can write it manually or use the function wizard on the tab Formulas in Group Mathematical(see picture).
Excel Function Wizard 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)
Calculating the cost of a product taking into account the discount 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, a record of the form 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 products
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.
