It consists. It's time to try the program in action, you will learn how to work in Excel. We will create a new document, enter data into it, perform simple calculations and build a time diagram. After mastering this lesson, you will be able to work in Excel and perform simple tasks. This will be the first confident step towards professional use of Excel.

Initial data: monthly sales volumes of goods in 2015, unit cost of goods, revenue for 2014 on a monthly basis. Let's start with the calculations.

How to create an Excel document

If Excel is not yet running, click Start - All Programs - Microsoft Office , and in the folder that opens – Excel. The program will be launched. In the window that opens, select Blank workbook.

Create a new Excel document

If the program is already running, press the key combination Ctrl+N to create a new workbook.

Filling the Excel sheet with data

As a result, you have a new empty document with an active sheet. "Sheet 1". Let's fill it with initial data. Let's start with the header, i.e. Let's decide on the structure of the table. Let the first column be the months of the year.

• Left-click on cell A1, it will be activated for filling. Write: "Month"
• Similarly, in column B1 we will write “Revenue 2015”
• C1 – “Sales”
• D1 – “Price”
• E1 – “Revenue 2014”

The hat is ready.

Now let’s write the names of the months in column A. Place the cursor in cell A2, write “January” and press Enter. The table cursor will move down one line. To fill in the subsequent months, we will use the Excel autofill function. Return the cursor to cell A2 and hover the mouse over the square in the lower right corner of the active cell. At the same time, the cursor will turn into a black cross. Hold down the left mouse button and drag the cursor down, as if stretching the inscription “January” down. Excel will automatically write new months in each subsequent line.

Until you release the mouse, the program shows a hint about what month will be in the corresponding cell. Look, in the picture I extended the list of months until May. If you move the cursor down 11 lines, the tooltip will display “December”. Let go of the mouse and “voila”, with one wave of your hand we have written down a whole 12 months!

Autofill cells in MS Excel

Now let's fill in the quantities and prices. Place the cursor in cell C2, write down the quantity and press Enter on the keyboard. The selection will move to C3, and we will also write down the quantity there. So, we fill all sales until December (C13). Let's do the same with prices in column D. In our example, prices contain kopecks. In the Russified version of Excel, they are separated from the whole part by a comma. In the original version - a dot. We will also fill in the 2014 revenue. This is what the completed data table will look like:

The table is filled with initial data

Calculations in Excel

Important! All formulas in Excel begin with a sign «=» . If the first character in the cell is this sign, the program understands that a calculated formula will follow.

Let's activate cell B2 and calculate revenue in January 2015. Let's write «=» and left-click on cell C2, setting the link to January sales. Next we put the multiplication sign «*» and click on D2 – link to the cost of the product. Press Enter and see that the result of the calculations is displayed in the selected cell, and the formula we entered is displayed in the formula bar for this cell. Excel will extract the value from C2 and multiply it by the value in D2. If you change any of them, the formula will be immediately recalculated. I wrote more information about the calculation rules in the articles and. All this is described in more detail there.

Formula and calculation result

Let's extend this formula to all rows of the table, as we did with filling in the months. Click on the square in the lower right corner of the cell with the formula. Result - the program calculated revenue for all months. Please note that when you drag, the addresses of the links in the formula change. For example, in cell B12 we have the formula: =C12*D12, not =C2*D2. This is because we have used .

Now let's calculate the difference between revenue in 2014 and 2015. Let's call column F – “Difference”. The method of writing formulas does not change, we just calculate =B2-E2 and extend the formula for all months. We see, for example, that in January 2015 our revenue increased by 7.5 units. less than in 2014.

Excel Charts

The last task remains - to build a time diagram and visually assess the dynamics of sales (I discussed the issue of diagrams in more detail in).

To build a graph, you need to select a range (read) of data on the basis of which the curve will be built. Hover your mouse cursor over cell A2. Hold down the left button and drag the cursor to cell B13. After this, release the mouse button. If you did everything correctly, the range A2:B13 will be highlighted in dark color.

We build a graph based on the selected data. To do this, on the ribbon, go to the Insert tab, and there - Charts - Insert graph - Graph with markers. A chart of changes in revenue by month of 2015 will appear on the screen. The program used the first selected column for labels along the horizontal axis, and the second for values ​​along the vertical axis.

Calculation result

Saving calculations

To save our document, press the key combination Ctrl+S, in the window that opens, click Review. A standard saving window opens, where you need to specify the location of the file and set its name. At the touch of a button Save , confirm the creation of a new file.

This article shows how to do math calculations in Excel and change the order of operations in your calculation formulas.

How to do calculations in Excel

Produce calculations in Excel easily. Here's how:

• Type an equal symbol (=) into the cell. This tells Excel that you are entering a formula and not just numbers.
• Enter the equation you want to calculate. For example, to calculate the sum of 5 and 7, enter =5+7
• Press Enter to complete the calculation. Ready!

Instead of entering the numbers directly into the calculation formula, you can put them in separate cells and then refer to those cells in your formula, for example, =A1+A2+A3

The following table shows how to perform basic arithmetic calculations in Excel .

Operation	Operator	Example	Description
Addition			Sums the numbers in cells A1 and A2
Subtraction			Subtracts the number A2 from the number in A1.
Multiplication			Multiplies numbers from A1 and A2.
			Divides the number A1 by the number A2.
			Finds 10% of the number A1.
Exponentiation			Returns the number A2 to the 3rd power.
Square root	SQRT function	SQRT(A1)	Finds the square root of A1.
Nth root	^(1/n), where n is the root to be found		Finds the cube root of A1.

The results above calculation formulas in Excel might look something like this:

Calculations in Excel - Calculation formulas in Excel

Additionally, you can combine values ​​from two or more cells in one cell using (&) like this:

A2&" "&B2&" "&C2

The space character (" ") is used to separate words:

Excel Calculations - Combining Values ​​from Multiple Cells

You can also compare cells using , such as greater than (>), less than (<), «больше или равно» (>=) and “less than or equal to” (<=). Результатом сравнения являются логические значения ИСТИНА и ЛОЖЬ:

Excel Calculations - Comparing Cells Using Boolean Operators

The procedure for performing calculations in Excel

When you perform two or more calculations in one formula, Microsoft Excel calculates the formula from left to right according to the order of operations shown in this table:

Since the calculation order affects the final result, you need to know how to change it.

How to change the calculation order in Excel

Just like in math, you can change calculation procedure in Excel , enclosing the part in parentheses.

For example, the calculation =2*4+7 tells Excel to multiply 2 by 4 and then add 7 to the value. The result of this calculation is 15. By enclosing the addition operation in parentheses =2*(4+7), you instruct Excel to first sum 4 and 7, and then multiply the sum by 2. And the result of this calculation is the value 22.

As you can see in the image below, the same calculation with and without parentheses produces different results:

Excel is essential when you need to organize, process, and save a lot of information. It will help automate calculations, making them easier and more reliable. Formulas in Excel allow you to carry out arbitrarily complex calculations and get results instantly.

How to write a formula in Excel

Before learning this, there are a few basic principles to understand.

1. Each one begins with an “=” sign.
2. Cell values ​​and functions can participate in calculations.
3. Operators are used as familiar mathematical symbols for operations.
4. When you insert a record, the cell displays the result of the calculation by default.
5. You can view the design in the line above the table.

Each cell in Excel is an indivisible unit with its own identifier (address), which is indicated by a letter (column number) and a number (row number). The address is displayed in the field above the table.

So, how to create and paste a formula in Excel? Proceed according to the following algorithm:

Designation Meaning

Addition
- Subtraction
/ Division
* Multiplication

If you need to specify a number rather than a cell address, enter it from the keyboard. To specify a negative sign in an Excel formula, click "-".

How to enter and copy formulas in Excel

They are always entered after clicking on “=”. But what to do if there are many similar calculations? In this case, you can specify one and then simply copy it. To do this, enter the formula and then “stretch” it in the desired direction to multiply it.
Position the pointer over the cell to be copied and move the mouse pointer to the lower right corner (over the square). It should take the form of a simple cross with equal sides.


Press the left button and drag.


Release when you need to stop copying. At this moment, the calculation results will appear.


You can also stretch it to the right.
Move the pointer to an adjacent cell. You will see the same entry, but with different addresses.


When copying in this way, the row numbers increase if the shift occurs downward, or the column numbers increase if it is shifted to the right. This is called relative addressing.
Let's enter the VAT value into the table and calculate the price including tax.


The price including VAT is calculated as price*(1+VAT). Let's enter the sequence into the first cell.


Let's try to copy the entry.


The result was strange.


Let's check the contents in the second cell.


As you can see, when copying, not only the price, but also the VAT shifted. And we need this cell to remain fixed. Let's anchor it with an absolute link. To do this, move the pointer to the first cell and click on address B2 in the formula bar.


Press F4. The address will be appended with a “$” sign. This is the sign of an absolute cell.


Now after copying the B2 address will remain unchanged.
If you accidentally entered data into the wrong cell, simply move it. To do this, move the mouse pointer over any border, wait until the mouse looks like a cross with arrows, press the left button and drag. At the desired location, simply release the manipulator.

Using functions for calculations

Excel offers large number functions that are divided into categories. You can view the full list by clicking on the Fx button next to the formula line or opening the “Formulas” section on the toolbar.


Let's talk about some functions.

How to Set "If" Formulas in Excel

This function allows you to set a condition and perform a calculation depending on its truth or falsity. For example, if the quantity of goods sold is more than 4 packs, you should purchase more.
To insert the result depending on the condition, we will add another column to the table.


In the first cell under the heading of this column, set the pointer and click the “Boolean” item on the toolbar. Let's select the "If" function.


As with inserting any function, a window will open for you to fill in the arguments.


Let's indicate the condition. To do this, click on the first row and select the first cell “Sold”. Next, put the “>” sign and indicate the number 4.


In the second line we will write “Purchase”. This message will appear for those items that have been sold out. The last line can be left blank since we have no action if the condition is false.


Click OK and copy the entry for the entire column.


To prevent “FALSE” from being displayed in the cell, let’s open the function again and correct it. Place the pointer on the first cell and press Fx next to the formula bar. Insert the cursor on the third line and put a space between the quotes.


Then OK and copy again.


Now we see which product should be purchased.

Formula text in Excel

This function allows you to apply a format to the contents of a cell. In this case, any type of data is converted to text, which means it cannot be used for further calculations. Let's add a column to format the total.


In the first cell, enter a function (the “Text” button in the “Formulas” section).


In the arguments window, we will indicate a link to the total amount cell and set the format to “#rub.”


Click OK and copy.


If we try to use this amount in calculations, we will receive an error message.

"VALUE" indicates that the calculation cannot be performed.
You can see examples of formats in the screenshot.

Date formula in Excel

Excel provides many options for working with dates. One of them, DATE, allows you to construct a date from three numbers. This is convenient if you have three different columns - day, month, year.

Place the pointer on the first cell of the fourth column and select a function from the Date and Time list.

Arrange the cell addresses accordingly and click OK.


Copy the entry.

AutoSum in Excel

In case you need to add up a large number of data, Excel provides the SUM function. For example, let's calculate the amount for goods sold.
Place the pointer in cell F12. It will calculate the total.


Go to the Formulas panel and click AutoSum.


Excel will automatically highlight the closest number range.


You can select a different range. In this Excel example I did everything right. Click OK. Pay attention to the contents of the cell. The SUM function was substituted automatically.


When inserting a range, the first cell address, a colon, and the last cell address are specified. ":" means "Take all cells between the first and last. If you need to list multiple cells, separate their addresses with a semicolon:
SUM (F5;F8;F11)

Working in Excel with formulas: example

We told you how to make a formula in Excel. This is knowledge that can be useful even in everyday life. You can manage your personal budget and control expenses.


The screenshot shows the formulas that are entered to calculate the amounts of income and expenses, as well as the calculation of the balance at the end of the month. Add sheets to the workbook for each month if you don't want all the sheets on one. To do this, simply click on the “+” at the bottom of the window.

To rename a sheet, double-click it and enter a name.

The table can be made even more detailed.
Excel is very useful program, and calculations in it provide almost unlimited possibilities.

Have a great day!

Most often among available groups Excel users turn to mathematical functions. They can be used to perform various arithmetic and algebraic operations. They are often used in planning and scientific calculations. Let's find out what this group of operators is like as a whole, and take a closer look at the most popular of them.

Using mathematical functions, you can perform various calculations. They will be useful to students and schoolchildren, engineers, scientists, accountants, and planners. This group includes about 80 operators. We will dwell in detail on the ten most popular of them.

There are several ways to open the list of mathematical formulas. The easiest way to launch the Function Wizard is to click on the button "Insert Function", which is located to the left of the formula bar. In this case, you must first select the cell where the result of data processing will be displayed. This method is good because it can be implemented from any tab.

You can also launch the Function Wizard by going to the tab "Formulas". There you need to press a button "Insert Function", located at the very left edge of the ribbon in the toolbox "Function Library".

There is a third way to activate the Function Wizard. This is done by pressing a combination of keys on the keyboard Shift+F3.

After the user has performed any of the above actions, the Function Wizard opens. Click on the window in the field "Category".

A drop-down list opens. Select a position in it "Mathematical".

After this, a list of all mathematical functions in Excel appears in the window. To proceed to entering arguments, select a specific one and click on the button "OK".

There is also a way to select a specific mathematical operator without opening the main Function Wizard window. To do this, go to the tab that is already familiar to us "Formulas" and press the button "Mathematical", located on the ribbon in the tool group "Function Library". A list opens from which you need to select the required formula to solve a specific problem, after which the window of its arguments will open.

However, it should be noted that not all formulas of the mathematical group are presented in this list, although most of them are. If you do not find the operator you need, you should click on the item "Insert function..." at the very bottom of the list, after which the already familiar Function Wizard will open.

SUM

Most commonly used function SUM. This operator is designed to add data in multiple cells. Although it can also be used for ordinary summation of numbers. The syntax that can be used when entering manually is as follows:

SUM(number1;number2;…)

In the arguments window, you should enter references to cells with data or to ranges in the fields. The operator adds the contents and displays the total in a separate cell.

SUMIF

Operator SUMIF also calculates the total sum of numbers in cells. But, unlike the previous function, in this operator you can set a condition that will determine which values ​​are included in the calculation and which are not. When specifying a condition, you can use the signs “>” (“greater than”), “<» («меньше»), «< >"("not equal"). That is, a number that does not meet the specified condition is not taken into account in the second argument when calculating the amount. Additionally, there is an additional argument "Summarization range", but it is not required. This operation has the following syntax:

SUMIF(Range,Criteria,Sum_Range)

ROUND

As you can understand from the name of the function ROUND, it is used to round numbers. The first argument of this operator is a number or a reference to a cell that contains a number element. Unlike most other functions, this range cannot be used as a value. The second argument is the number of decimal places to round to. Rounding is carried out according to general mathematical rules, that is, to the nearest absolute number. The syntax for this formula is:

ROUND(number, number_digits)

In addition, Excel has functions such as ROUNDUP And ROUND BOTTOM, which accordingly round numbers to the nearest greater and lesser modulo.

PRODUCT

Operator's task PRIZVED is the multiplication of individual numbers or those located in the cells of the sheet. The arguments to this function are references to the cells that contain the data to be multiplied. A total of up to 255 such links can be used. The result of the multiplication is displayed in a separate cell. The syntax of this operator looks like this:

PRODUCT(number,number,…)

ABS

Using a mathematical formula ABS The number is calculated modulo. This operator has one argument - "Number", that is, a reference to a cell containing numeric data. Range cannot act as an argument. The syntax is as follows:

ABS(number)

DEGREE

From the name it is clear that the operator’s task is DEGREE is the raising of a number to a given power. This function has two arguments: "Number" And "Degree". The first of these can be specified as a reference to a cell containing a numeric value. The second argument specifies the degree of erection. From the above it follows that the syntax of this operator is as follows:

DEGREE(number,degree)

ROOT

The task of the function ROOT is to extract the square root. This operator has only one argument - "Number". Its role can be a link to a cell containing data. The syntax takes the following form:

SQRT(number)

CASE BETWEEN

The formula has a rather specific task CASE BETWEEN. It consists of outputting any random number, located between two given numbers. From the description of the functionality of this operator it is clear that its arguments are the upper and lower boundaries of the interval. Its syntax is:

RANDBETWEEN(Lower_border;Upper_border)

PRIVATE

Operator PRIVATE used to divide numbers. But in the division results it only outputs an even number, rounded down modulo. The arguments to this formula are references to cells containing the dividend and divisor. The syntax is as follows:

QUANTIATE(Numerator;Denominator)

ROMAN

This function allows you to convert Arabic numbers, which Excel operates by default, into Roman numbers. This operator has two arguments: a cell reference with the number to be converted and a form. The second argument is optional. The syntax is as follows:

ROMAN(Number;Form)

Only the most popular Excel mathematical functions were described above. They help greatly simplify various calculations in a given program. Using these formulas, you can perform both simple arithmetic operations and more complex calculations. They are especially helpful in cases where you need to make mass calculations.

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 consider how the formulas actually work simple 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 analyze 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. Some frequently used ones can be remembered, but some you will need only occasionally and it is very difficult to remember their name and especially the form of recording.

But in Excel there is standard way inserting functions with their full 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, its entry form, required arguments and brief description, which 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 standard features Excel.

Like

Review