In order to understand how to use this program, you need to consider EXCEL formulas with examples.
If you place the mouse cursor on any cell and click on the “select function” item, the function wizard appears.
With its help, you can find the necessary formula as quickly as possible. To do this, you can enter its name, use the category.
Excel is very convenient and easy to use. All functions are divided into categories. If the category of the required function is known, then its selection is carried out according to it.
If the function is unknown to the user, then he can set the category "full alphabetical list".
For example, given the task, find the function SUMIFS. To do this, go to the category of mathematical functions and find the one you need there.
VLOOKUP function
Using the VLOOKUP function, you can extract the necessary information from the tables. The essence of vertical lookup is to look for the value in the leftmost column of the given range.
After that, the total value is returned from the cell, which is located at the intersection of the selected row and column.
The calculation of the VLOOKUP can be seen in an example that shows a list of surnames. The task is to find the last name by the given number.
Applying the VLOOKUP function
The formula shows that the first argument of the function is cell C1.
The second argument A1:B10 is the range to search.
The third argument is the index number of the column from which to return the result.
Calculating a given last name using the VLOOKUP function
In addition, you can search for the last name even if some sequence numbers are missing.
If you try to find the last name from a non-existent number, the formula will not give an error, but will give the correct result.
Search for a last name with missing numbers
This phenomenon is explained by the fact that the VLOOKUP function has a fourth argument, with which you can set the interval view.
It has only two values - "false" or "true". If no argument is given, it defaults to true.
Rounding Numbers with Functions
The functions of the program allow you to accurately round any fractional number up or down.
And the resulting value can be used in calculations in other formulas.
The number is rounded using the ROUNDUP formula. To do this, you need to fill in the cell.
The first argument is 76.375 and the second is 0.
Rounding a number with a formula
In this case, the number has been rounded up. To round a value down, select the ROUNDDOWN function.
Rounding occurs to an integer. In our case, up to 77 or 76.
Excel helps to simplify any calculations. With the help of a spreadsheet, you can complete tasks in higher mathematics.
The program is most actively used by designers, entrepreneurs, as well as students.
The whole truth about Microsoft Excel 2007 formulas
EXCEL formulas with examples - Instructions for use
Formulas in Excel are its main essence, for the sake of which this program was created by Microsoft. Formulas allow you to calculate cell values based on the data of other cells, and if the source data changes, then the result of the calculations in the cell where the formula is located will be recalculated automatically!
Create formulas in Excel
Consider the work of formulas on 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 appear in the third cell.
The sum of 2 and 3 is, of course, 5, but you don’t need to enter the five manually in the next cell, otherwise the meaning of calculations in Excel is lost. In the cell with the total, you must enter the sum formula 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, a formula is simply indispensable.
Formulas in Excel can contain arithmetic operations (addition +, subtraction -, multiplication *, division /), coordinates of source data cells (both individually and in a range) and calculation functions.
Consider the formula for the sum of numbers in the example above:
SUM(A2;B2)
Every formula starts 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 some data, and already in the 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, like so:
SUM(A4;B4;C4)
When you need to add a large number of cells, it will take a very long time to specify each of them in the formula, so instead of a simple enumeration, 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 cell values from cell B2 to cell B7.
Functions in formulas can be connected and combined as you need to get 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 of 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, and the third is the action if the condition is false. We remind you that the arguments are separated by a semicolon.
IF(condition; true; false)
As a condition, it is indicated 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 of the range is, 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 100, then the action in the third argument of the IF function will be performed, 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, and some you will need only occasionally, and it is very difficult to remember their name, and even more so the form of recording.
But in Excel there is a standard way to insert functions with their full list. If you want to add some function to a cell, then click on the cell and select insert function from the main menu. The program will display a list of functions and you can choose 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 the keyboard.
In Excel 2003, a function is inserted via 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 located, and the "Function Wizard" window will be displayed on top of the sheet.
Function in Excel divided into categories. If you know to which category your proposed function may belong, then select the selection for it. Otherwise, select "Full alphabetical listing". The program will display all available functions in the list of functions.
Scroll through the list and select the name of the function you are interested in with the mouse. Just below the list, its form will appear, 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.
The arguments window contains 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 the data. You can fill in manually, but it is much more convenient to click on the table icon at the end of the field in order to specify the source cell or range.
The arguments window will take on 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 field "Number 1" 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 already preview the result of the calculation of the resulting formula. To make it appear in a cell on the sheet, click the OK button. In the considered example, the product of the numbers in cells B2 and C2 is placed in cell D2.
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 |
Excel is necessary in cases where you need to organize, process and save a lot of information. It will help automate calculations, make 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, you should understand a few basic principles.
- Each starts with an "=" sign.
- Values from cells and functions can participate in calculations.
- Operators are used as the mathematical signs of operations that are familiar to us.
- When you insert an entry, the default cell reflects the result of the calculation.
- You can see the design in the row above the table.
Each cell in Excel is an indivisible unit with its own identifier (address), which is denoted 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 insert a formula in Excel? Proceed according to the following algorithm:
Designation Meaning
Addition
- Subtraction
/ division
* Multiplication
If you need to specify a number, not a cell address, enter it from the keyboard. To enter a negative sign in an Excel formula, press "-". 
How to enter and copy formulas in Excel
They are always entered after pressing "=". But what if there are many similar calculations? In this case, you can specify one, and then just copy it. To do this, enter the formula, and then "stretch" it in the right direction to multiply.
Set the pointer to the copied cell and move the mouse pointer to the lower right corner (on the square). It should take the form of a simple cross with equal sides. 
Press the left button and drag. 
Release when you want to stop copying. At this point, the calculation results will appear. 
You can also stretch to the right.
Move the pointer to the next cell. You will see the same entry, but with different addresses. 
When copying in this way, the line numbers increase if the shift is down, or the column numbers increase if to the right. This is called relative addressing.
Let's enter the value of VAT into the table and calculate the price with tax. 
The price with VAT is calculated as the price*(1+VAT). Enter the sequence in the first cell. 
Let's try to copy the record. 
The result is strange. 
Let's check the content in the second cell. 
As you can see, when copying, not only the price shifted, but also VAT. And we need this cell to remain fixed. Fix it with an absolute link. To do this, move the pointer to the first cell and click on the address B2 in the formula bar. 
Press F4. The address will be diluted with a "$" sign. This is the sign of an absolutely cell. 
Now after copying the address B2 will remain unchanged.
If you accidentally entered data in the wrong cell, just transfer 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. In the right place, just release the manipulator.
Using Functions for Calculations
Excel offers a large number of functions that are categorized. You can view the full list by clicking on the Fx button next to the formula bar or by opening the "Formulas" section on the toolbar. 
Let's talk about some of the features.
How to Set "If" Formulas in Excel
This function allows you to set a condition and perform a calculation depending on whether it is true or false. For example, if the quantity sold is more than 4 packs, more should be purchased.
To insert the result depending on the condition, let's add one more column to the table. 
In the first cell under the heading of this column, set the pointer and click the "Logical" item on the toolbar. Let's select the "If" function. 
As with inserting any function, a window will open to fill in the arguments. 
Let's specify a 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 inscription will appear for those products 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. 
So that the cell does not display "FALSE", open the function again and fix 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 quotation marks. 
Then OK and copy again. 
Now we see which product should be purchased.
formula text in excel
This feature allows you to apply a format to the contents of a cell. In this case, any data type is converted to text, and therefore 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, specify a link to the cell of the total amount and set the format to "#RUB". 
Click OK and copy. 
If we try to use this amount in calculations, we will get an error message. 
"VALUE" means that calculations cannot be made.
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 build a date from three numbers. This is useful 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 a large amount of data, Excel provides the SUM function. For example, let's calculate the amount for sold goods.
Put the pointer in cell F12. It will calculate the total. 
Go to the Formulas panel and click AutoSum. 
Excel will automatically highlight the nearest numeric range. 
You can select a different range. In this example, Excel did everything right. Click OK. Pay attention to the contents of the cell. The SUM function was substituted automatically. 
When inserting a range, specify the address of the first cell, a colon, and the address of the last cell. ":" 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 with formulas in Excel: an example
We told you how to make a formula in Excel. This is the kind of 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 tables to be on the same table. To do this, simply click on the "+" at the bottom of the window. 
To rename a sheet, double-click on it and enter a name. 
The table can be made even more detailed.
Excel is a very useful program, and calculations in it give almost unlimited possibilities.
Have a great day!
Over the past decade, the computer in accounting has become simply an indispensable tool. At the same time, its application is diverse. First of all, this, of course, is the use of an accounting program. To date, quite a lot of software tools have been developed, both specialized (“1C”, “Info-Accountant”, “BEST”, etc.), and universal ones, like Microsoft Office. At work, and in everyday life, you often have to do a lot of different calculations, keep multi-line tables with numerical and textual information, do all sorts of calculations with the data, and print options. To solve a number of economic and financial problems, it is advisable to use the numerous possibilities of spreadsheets. Consider in this connection the computational functions of MS Excel.
Source: Journal "Accountant and Computer" No. 4, 2004.
Like any other spreadsheet, MS Excel is designed primarily to automate calculations that are usually made on a piece of paper or using a calculator. In practice, in professional activities there are quite complex calculations. That's why we'll take a closer look at how Excel helps us automate their execution.
Operators are used in formulas to denote an action, such as addition, subtraction, etc.
All operators are divided into several groups (see table).
| OPERATOR | MEANING | EXAMPLE |
|
||
| + (plus sign) | Addition | =A1+B2 |
| - (minus sign) | Subtraction Unary minus | =A1-B2 =-B2 |
| /(slash) | Division | =A1/B2 |
| *(star) | Multiplication | = A1*B2 |
| % (percent sign) | Percent | =20% |
| ^ (cap) | Exponentiation | = 5^3 (5 to the 3rd power) |
|
||
| = | Equals | =IF(A1=B2,"Yes","No") |
| > | More | =IF(A1>B2,A1,B2) |
| < | Less | =IF(AKV2,B2,A1) |
| >= <= | Greater than or equal Less than or equal | =IF(A1>=B2,A1,B2) =IF(AK=B2,B2,A1) |
| <> | Not equal | =IF(A1<>B2; "Not Equal") |
|
||
| &(ampersand) | Combining character sequences into a single character sequence | = "The value of cell B2 is: "&B2 |
|
||
| Range(colon) | Referencing all cells between the bounds of a range, inclusive | =SUM(A1:B2) |
| Concatenation (semicolon) | Link to merge range cells | =SUM(A1:B2,SZ,D4:E5) |
| Intersection(space) | Link to shared range cells | =CUMM(A1:B2C3D4:E5) |
Arithmetic operators are used to denote basic mathematical operations on numbers. The result of an arithmetic operation is always a number. Comparison operators are used to denote the operations of comparing two numbers. The result of the comparison operation is the logical value TRUE or FALSE.
Formulas are used to perform calculations in Excel. With the help of formulas, you can, for example, add, multiply, and compare table data, i.e., formulas should be used when you need to enter a calculated value into a sheet cell (automatically calculate). Entering a formula begins with the symbol “=” (equal sign). It is this sign that distinguishes the input of formulas from the input of text or a simple numeric value.
When entering formulas, you can use regular numeric and text values. Recall that numeric values can only contain numbers from 0 to 9 and special characters: (plus, minus, slash, parentheses, dot, comma, percent and dollar signs). Text values can contain any characters. It should be noted that text expressions used in formulas must be enclosed in double quotes, for example “constant1”. In addition, in formulas, you can use cell references (including in the form of names) and numerous functions that are interconnected by operators.
References are cell addresses or ranges of cells included in a formula. Cell references are set in the usual way, that is, in the form A1, B1, C1. For example, in order to get the sum of cells A1 and A2 in cell A3, it is enough to enter the formula =A1+A2 into it (Fig. 1).
When entering a formula, cell references can be typed character by character directly from the keyboard in Latin, but more often it is much easier to specify them with the mouse. For example, to enter the formula =A1+B2, you would do the following:
Select the cell in which you want to enter the formula;
Start entering the formula by pressing the “=” (equals) key;
Click on cell A1;
Enter the “+” symbol;
Click on cell B2;
Finish entering the formula by pressing the Enter key.
A cell range is a certain rectangular area of the worksheet and is uniquely defined by cell addresses located in opposite corners of the range. Separated by “:” (colon), these two coordinates make up the address of the range. For example, to get the sum of cell values in the range C3:D7, use the formula =SUM(C3:D7).
In the special case when a range consists entirely of several columns, for example from B to D, its address is written as B:D. Similarly, if the entire range consists of lines 6 through 15, then it has the address 6:15. In addition, when writing formulas, you can use the union of several ranges or cells, separating them with the symbol “;” (semicolon), for example C3:D7; E5;F3:G7.
Editing an already entered formula can be done in several ways:
Double-clicking the left mouse button on a cell to correct the formula directly in that cell;
Select a cell and press the F2 key (Fig. 2);
Select a cell by moving the cursor to the formula bar, click the left mouse button.
As a result, the program will enter the editing mode, during which you can make the necessary changes to the formula.
When filling out a table, it is customary to set calculation formulas only for the “first” (initial) row or “first” (initial) column, and fill the rest of the table with formulas using copy or fill modes. An excellent result is the use of autocopying formulas using an autofiller.
Recall how to correctly implement the copy mode. There may be various options (and problems too).
It must be borne in mind that when copying, the addresses are transposed. When you copy a formula from one cell to another, Excel reacts differently to formulas with relative and absolute references. For relative terms, Excel by default performs the transposition of addresses, depending on the position of the cell into which the formula is copied.
For example, you need to add row by row the values of columns A and B (Fig. 8) and place the result in column C. If you copy the formula \u003d A2 + B2 from cell C2 to cell C3 * (and further down C), then Excel itself will convert formula addresses respectively as =A3+B3 (and so on). But if you need to put a formula, say, from C2 into cell D4, then the formula will already look like =B4+C4 (instead of the desired one =A4+B4), and accordingly the calculation result will be wrong! In other words, pay special attention to the copying process and manually adjust the formulas if necessary. By the way, copying itself from C2 to C3 is done as follows:
1) select cell C2 from which you want to copy the formula;
2) press the “Copy” button on the toolbar, or the Ctrl+C keys, or select “Edit ® Copy” from the menu;
3) select cell C3, into which we will copy the formula;
4) press the “Paste” button on the toolbar, or the Ctrl + V keys, or through the menu “Edit ® Paste” with pressing Enter.
Consider the autocomplete mode. If you need to move (copy) the formula into several cells (for example, in C3: C5) down the column, then it is more convenient and easier to do this: repeat the previous sequence of actions until step 3 of selecting cell C3, then move the mouse cursor to the starting cell of the range ( C3), press the left mouse button and, without releasing it, drag it down to the required last cell of the range. In our case, this is cell C5. Then release the left mouse button, move the cursor to the “Insert” button on the toolbar and press it, and then Enter. Excel itself converts the addresses of the formulas in the range we have selected to the corresponding row addresses.
Sometimes it becomes necessary to copy only the numeric value of a cell (range of cells). To do this, do the following:
1) select a cell (range) from which you want to copy data;
2) click the “Copy” button on the toolbar or select “Edit ® Copy” from the menu;
3) select a cell (upper left of the new range) to which the data will be copied;
4) select “Edit ® Paste Special” from the menu and press Enter.
When copying formulas, the computer immediately makes calculations on them, thus giving a quick and visual result.
:: Functions in Excel
Functions in Excel greatly facilitate calculations and interaction with spreadsheets. The most commonly used function is the summation of cell values. Recall that it has the name SUM, and the ranges of summed numbers serve as arguments.
In a spreadsheet, you often want to calculate a total for a column or row. To do this, Excel offers an automatic sum function, performed by clicking the button (“AutoSum”) on the toolbar.
If we enter a series of numbers, place the cursor under them and double-click on the auto-sum icon, then the numbers will be added (Fig. 3).
In the latest version of the program, there is a list button to the right of the autosum icon that allows you to perform a number of frequently used operations instead of summing (Fig. 4).
:: Automatic calculations
Some calculations can be done without entering formulas at all. Let's make a small lyrical digression, which may be useful for many users. As you know, a spreadsheet, due to its user-friendly interface and computing capabilities, can completely replace calculations using a calculator. However, practice shows that a significant part of people who actively use Excel in their activities keep a calculator on their desktop to perform intermediate calculations.
Indeed, in order to perform the operation of summing two or more cells in Excel to obtain a temporary result, it is necessary to perform at least two extra operations - find the place in the current table where the total will be located, and activate the autosum operation. And only after that you can select those cells whose values need to be summed.
That's why, starting with Excel 7.0, the auto-calculate feature has been built into the spreadsheet. Excel spreadsheets now have the ability to quickly perform some mathematical operations in automatic mode.
To see the result of intermediate summation, it is enough just to select the necessary cells. This result is also reflected in the status bar at the bottom of the screen. If the amount does not appear there, move the cursor to the status bar at the bottom of the frame, right-click and in the drop-down menu next to the Amount line, press the left mouse button. Moreover, in this menu on the status bar, you can select various options for the calculated results: the sum, the arithmetic mean, the number of elements, or the minimum value in the selected range.
For example, let's use this function to calculate the sum of values for the range B3:B9. Select the numbers in the cell range B3:B9. Please note that in the status bar located at the bottom of the working window, the inscription Sum = X appeared, where X is a number equal to the sum of the selected numbers in the range (Fig. 5).
As you can see, the results of the usual calculation using the formula in cell B10 and the automatic calculation are the same.
:: Function Wizard
In addition to the summation function, Excel allows you to process data using other functions. Any of them can be entered directly in the formula bar using the keyboard, however, to simplify entry and reduce the number of errors in Excel, there is a “Function Wizard” (Fig. 6).
You can call the “Wizards” dialog box using the “Insert ® Function” command, the Shift+F3 key combination, or the button on the standard toolbar.
The first dialog of the "Function Wizard" is organized thematically. Having selected a category, in the lower window we will see a list of the names of the functions contained in this group. For example, you can find the SUM () function in the “Math” group, and in the “Date and Time” group there are functions NUMBER (), MONTH (), YEAR (), TODAY ().
In addition, to speed up the selection of functions, Excel "remembers" the names of 10 recently used functions in the corresponding group. Please note that in the lower part of the window a brief help about the purpose of the function and its arguments is displayed. If you click the "Help" button at the bottom of the dialog box, Excel will open the corresponding section of the help system.
Suppose you need to calculate the depreciation of property. In this case, enter the word “depreciation” in the function search area. The program will select all functions for depreciation (Fig. 7).
After filling in the appropriate fields of the function, the depreciation of the property will be calculated.
Often you need to add numbers that satisfy some condition. In this case, you should use the SUMIF function. Let's consider a specific example. Suppose it is necessary to calculate the amount of commission if the value of the property exceeds 75,000 rubles. To do this, we use the data of the table of dependence of commissions on the value of property (Fig. 8).
Our actions in this case are as follows. We set the cursor in cell B6, use the button to launch the “Function Wizard”, in the category “Mathematical” select the SUMIF function, set the parameters, as in Fig. 9.
Please note that we choose the interval of cells A2:A6 (property value) as the range for checking the condition, and B2:B6 (commission) as the summation range, while the condition looks like (> 75000). The result of our calculation will be 27,000 rubles.
:: Let's name the cell
For the convenience of working in Excel, it is possible to assign names to individual cells or ranges, which can then be used in formulas along with regular addresses. To quickly name a cell, select it, position the pointer over the name field on the left side of the formula bar, click the mouse button, and enter a name.
When assigning names, you must remember that they can consist of letters (including the Russian alphabet), numbers, dots, and underscores. The first character in the name must be a letter or an underscore. Names cannot have the same form as cell references, such as Z$100 or R1C1. The name can contain more than one word, but spaces are not allowed. Underscores and periods can be used as word separators, such as Sales_Tax or First.Quarter. The name can contain up to 255 characters. In this case, uppercase and lowercase letters are perceived equally.
To insert a name into a formula, you can use the command “Insert ® Name ® Insert” by selecting the desired name in the list of names.
It is useful to remember that names in Excel are used as absolute references, that is, they are a kind of absolute addressing, which is convenient when copying formulas.
Names in Excel can be defined not only for individual cells, but also for ranges (including non-contiguous ones). To assign a name, simply select the range, and then enter a name in the name field. In addition, to set the names of ranges containing headers, it is convenient to use the special command “New” in the menu “Insert ® Name”.
To delete a name, select it from the list and click the "Delete" button.
When you create a formula that references data from a worksheet, you can use the row and column headings to specify the data. For example, if you assign the column values the name of the column name (Fig. 10),
then to calculate the total amount for the “Commission” column, the formula =SUM(Commission) is used (Fig. 11).
:: More Excel Features - Templates
MS Excel includes a set of templates - Excel tables, which are designed to analyze the economic activity of an enterprise, draw up an invoice, order, and even to take into account a personal budget. They can be used to automate the solution of frequently occurring tasks. So, you can create documents based on the templates “Advance report”, “Invoice”, “Order”, which contain forms of documents used in business activities. These forms do not differ from the standard ones in their appearance and when printed, and the only thing that needs to be done to receive the document is to fill in its fields.
To create a document based on a template, execute the “New” command from the “File” menu, then select the required template on the “Solutions” tab (Fig. 12).
Templates are copied to disk during a typical installation of Excel. If the templates do not appear in the New Document dialog box, run the Excel installer and install the templates. For detailed information about installing templates, see "Installing Microsoft Office components" in Excel Help.
For example, to create a number of financial documents, select the “Financial Templates” template (Fig. 13).
This group of templates contains forms for the following documents:
travel certificate;
. advance report;
. payment order;
. invoice;
. invoice;
. power of attorney;
. incoming and outgoing orders;
. telephone and electricity bills.
Select the required form to fill out, and then enter all the necessary details into it and print it out. If desired, the document can be saved as a regular Excel spreadsheet.
Excel allows the user to create document templates himself, as well as edit existing ones.
However, document forms may change over time, and then the existing template will become unusable. In addition, in the templates that are included in the delivery of Excel, it would be nice to enter in advance such permanent information as data about your organization, manager. Finally, it may be necessary to create your own template: for example, the planning department will most likely need templates for preparing estimates and calculations, and the accounting department will need an invoice form with your organization's logo.
For such cases, Excel, as well as many other programs that work with electronic documents, provides the ability to create and edit templates for frequently used documents. An Excel template is a special workbook that can be used as a reference when creating other workbooks of the same type. Unlike a regular Excel workbook that has the *.xls extension, the template file has the *.xlt extension.
When creating a document based on a template, Excel automatically creates a working copy of it with the *.xls extension, adding a serial number to the end of the document name. The original template remains intact and can be reused later.
To automatically enter the date, you can use the following method: enter the TODAY function in the date cell, after which it will display the current day of the month, month and year, respectively.
Of course, you can use all the considered actions on templates when working with regular Excel workbooks.
Often there is a need to create tables in which you need to perform mathematical operations with the available data. For example, consider a fragment of a table for accounting for goods in a warehouse (Fig. 6.1). In this table, numbers in bold type are numbers that require mathematical calculations to obtain. You can calculate the amounts on a calculator and manually enter ready-made values, but by learning the basics of working in Excel, you can get the results automatically, which saves time and eliminates possible errors.
Rice. 6.1. Example table with calculations
Every spreadsheet is made up of cells, which form rows and columns.
Strings- these are horizontal rows of cells, which are numbered with numbers (1, 2, 3). columns are vertical rows of cells and are designated by one or two Latin letters (A, B, C).
One spreadsheet is called worksheet or simply sheet. The maximum number of columns in an Excel 2007 worksheet is 16,384, and the maximum number of rows is more than 1 million, which is significantly more than in previous versions of the program. Spreadsheet files usually consist of several sheets and are named books.
The place of each cell in the table is determined link. It is formed from the designations of the column and row at the intersection of which the cell is located. For example, the price of a CD-RW is entered in cell C2 and the total is entered in cell D7 (see Figure 6.1). Instead of a term cell reference terms may also be used cell name or cell address.
You can enter text, numbers, and dates into spreadsheet cells, and formulas. Formula is a mathematical expression that is used to calculate the table. For example, in cell D2 (see Figure 6.1), the following formula was entered: =B2*C2. Using this formula, Excel will automatically multiply the contents of cells B2 and C2 and display the result in cell D2. Similar formulas must be entered in the remaining cells of column D. The formula always begins with the sign =, after which references to cells and signs of mathematical operations are entered. In Excel, there are tools for automating the input of formulas, which will be discussed in detail in the following lessons.
NOTE
You have already encountered documents in the form of tables when studying Word, which has powerful editing and formatting tools, but the possibilities for automatic calculations are limited. If your spreadsheet does not have calculations, then it is preferable to use Word.
Introduction to the Excel window
Like any other program, Excel can be launched using the Start menu search. You can start Excel in another way, for example, create a shortcut to desktop or at Quick Launch Bars. The appearance of the Excel program window is shown in Fig. 6.2.
After learning Word, it is much easier to deal with the main elements of the Excel window, since all programs of the Microsoft Office family have a single interface. At the top of Excel 2007 is a ribbon with commands and buttons you already know, so you can apply previously learned techniques for working with documents, snippets, and formatting text.
Rice. 6.2. Microsoft Office Excel 2007 window
You should remember the main features of working with the tape:
? the ribbon consists of tabs, each of which contains several groups of commands; to find a specific command, you need to open the tab by clicking on its title, then find the required group of commands, and in it - a button or other control element;
? names of buttons without captions can be found in tooltips;
? the ribbon can be collapsed by double-clicking on the title of any tab; to return the original position of the ribbon, you need to double-click on the title of any tab again;
? when you select certain document objects, such as pictures or charts, additional tabs automatically appear on the ribbon to work with them;
? commands for working with files ( Create, Open, Save, Print etc.) are in the menu, which is opened with Office buttons, located in the upper left corner of the program window;
? frequently used buttons and commands can be placed on the quick access panel, for which you should right-click on the desired button and execute the command Add to Quick Access Toolbar.
Using tab commands View you can turn on or off interface elements such as the formula bar, grid, headings, and switch between document views.
A distinctive feature of the Excel interface is the formula bar, which is usually located below the ribbon. The name of the current cell is displayed on its left side, and its contents are displayed on the right side (Fig. 6.3, left). When entering data, buttons appear in the formula bar that allow you to complete or cancel the input, as well as insert a function (Fig. 6.3, on right).
Rice. 6.3. Formula bar: name and contents of the current cell (left); end and cancel buttons
input (on right)
Immediately after starting Excel, a new spreadsheet file will be created called book1, consisting of three sheets. To move from one sheet to another, you need to click on the label of the desired sheet in the lower part of the window (see Fig. 6.2).
Sheet navigation
Data is always entered in active cell, marked with a bold frame (see Fig. 6.2). There are two ways to change the active cell.
? Mouse click on the desired cell.
? Arrow keys () (they will make the adjacent cell active in the selected direction).
To move around the sheet, you can also use the key combinations that are shown in Table. 6.1.
Table 6.1. Keyboard shortcuts for moving around the sheet
Sometimes it becomes necessary to scroll the contents of the sheet without changing the active cell. To do this, use the scroll bars or the mouse wheel. Another way is to turn on the Scroll Lock mode with the same key, after which using any keys to move around the sheet will scroll it without changing the active cell.
Entering data into cells
To fill a table cell with data, do the following.
1. Select the desired cell.
2. Enter text, number or formula.
3. Press any of the sheet navigation keys, such as an arrow key or Enter, to move to the next cell.
There are a few things to keep in mind when entering data:
? if the text does not fit in a cell, it will take an empty adjacent cell; if there is data in an adjacent cell, the text will be cut off at the cell border, but will be displayed in its entirety in the formula bar;
?
? a comma is used to separate the integer and fractional parts of a number; it is convenient to type numbers on the additional numeric keypad;
? numbers entered with a dot, hyphen, or / are treated as dates; to enter time values, numbers must be separated by a colon.
As you enter data, buttons appear in the formula bar that allow you to complete or cancel your entry. It should be noted that you can enter and edit data directly in the formula bar, which is especially useful when entering complex formulas.
Excel also has tools for automating data entry, which will be covered in the following lessons.
data editing
When working with a table, it often becomes necessary to change previously entered data. The main methods of data editing are given in Table. 6.2.
Table 6.2. Techniques for editing data in a spreadsheet
Practice 42: Creating a spreadsheet, entering data and formulas
Exercise. Create a spreadsheet, populate it with data, perform simple calculations, and save the result.
1. Launch the Excel program using the search bar in the menu Start. This will automatically create a new book called Book1.
2. Move around the sheet in various ways - by clicking on the cells with the mouse, using the arrow keys or key combinations (see Table 6.1).
3. Enter free text in several cells. Delete the entered text by pressing the Delete key.
4. Fill in the table with data as shown in fig. 6.4. First, enter text in the cells of column A, completing the entry by pressing the Enter key.
Rice. 6.4. Sample table for input
Note that the text in some cells of the column A won't fit in the width and will take up part of column B. To avoid this, hover over the column border in the column headings area and move the border slightly to the right. Fill in the rest of the cells with data (see Figure 6.4).
5. Make cell D2 active and enter the following formula into it: =B2*C2 (Fig. 6.5), then press the Enter key. Do not forget to put the = sign at the beginning of the formula and switch to the English input language. If you did everything correctly, then the result of the calculation should be displayed in the cell, otherwise enter the formula again.
Rice. 6.5. Process (left) and result (right) of entering a formula
6. In cell D3, the formula should look like this: =B3*C3. Enter this formula in another way:
1) make cell D3 active and press the = key;
2) click on the cell C3- the cell address should automatically appear in the formula;
3) press the * (multiply) key;
4) click on cell B3 - the formula will look like: =C3*B3;
5) to complete the input, press the Enter key, after which the result of the calculation should appear in the cell.
As you have already noticed, to enter the address of a cell into the formula, just click on the desired cell with the mouse button.
7. Enter the formula yourself in cell D4. It will be like this: \u003d B4 * C4.
8. Change a few numbers in columns B and C. Please note that all formulas will be recalculated automatically.
9. Use the shortcuts at the bottom of the window to navigate to Sheet2, and then return to Sheet1.
10. Save the created table. To do this, click the button Save on the quick access panel, in the window that appears, select the folder to save and enter the name of the book - Products(as you remember, a separate folder was created to save the work Lessons on Excel).
11. Close the Excel program.
1. Launch the Excel program.
2. Create a spreadsheet for calculating electricity according to the sample (Fig. 6.6).
3. In cell B6, create a formula to calculate the flow rate (the difference between the last and previous meter readings).
4. In cell B7, enter the formula to calculate the payment amount. Change the meter readings and check that the formulas work correctly.
5. Adjust the width of the columns and change the size and style of the font for some cells using the tab buttons Home.
6. Save the table in the study folder under the name Electricity.
Rice. 6.6. An example of calculating the electricity fee
Summarizing
Questions to check
? What is Excel for?
? What do the basic terms of spreadsheets mean: book, sheet, cell, row, column, link?
? What are the similarities and differences between Excel and Word windows?
? How to start excel and navigate through the sheet?
? How to enter and edit data?
? How to enter a simple formula for calculations?
In the next lesson, you will learn about powerful tools for simplifying data entry and editing, and in Lesson 6.3, you will perform calculations using various formulas and functions.
Lesson 6.2. Working with cells, ranges, and sheets
Cell selection
When performing an operation, it becomes necessary to select several cells or their range. The most commonly used selection of a rectangular area of cells with the mouse button. This is done in the following way.
1. Move the mouse pointer to the upper left cell of the required range. The pointer should look like in Fig. 6.7, left(great advantage").
2. While holding down the mouse button, move the pointer to the lower right cell of the range, then release the mouse button - the selected range will be filled with a different color (Fig. 6.7, on right).
Rice. 6.7. Single cell selection (left) and rectangular range (right)
To select an entire line, just click on its number in the line header area (Fig. 6.8, left). By dragging the mouse while holding down its button, you can select several lines in a row. Similarly, you can select columns using their headers (Fig. 6.8, in the center). To select the entire table, press the button Select all at the intersection of row and column headings (Fig. 6.8, on right).
Rice. 6.8. Select row (left), column (center), table (right)
Cell ranges can be used in formulas and expressions. A range reference consists of top left and bottom right cell references separated by a colon. For example, a link to a range that was highlighted in the figure above (see Figure 6.7, on right) will be written as B2:C4.
It is possible to select several rectangular ranges or individual cells at once (Fig. 6.9). To do this, you need to sequentially select the desired ranges or cells with the mouse button while holding down the Ctrl key. To cancel the selection, just click on any cell.
Rice. 6.9. Selecting a complex range
Operations on the contents of cells
Various operations can be performed on individual cells or ranges: moving, copying, deleting, adding, linking, etc. As noted earlier, Microsoft Office applications provide the user with several ways to perform certain actions: buttons on the ribbon, context menu commands, or keyboard shortcuts . The most common ways to perform actions with cells and ranges will be discussed next.
Cell cleaning
In the previous lesson, it was already mentioned that to clear a cell, it is enough to make it active and press the Delete key. In the same way, you can clear several cells at once, after selecting the desired range.
Cleaning deletes only the data or formulas present in the cell, and information about cell formatting parameters (font, color, data type, borders, etc.) remains unchanged. In Group Editing tabs home there is a button clear, which allows you to choose what exactly to clear in the selected cells - All, Formats, Content or Notes.
You may need to clean up the format if a decimal number was entered by mistake with a dot instead of a comma. In this case, Excel will treat the entered information as a date, and re-entering the correct number will not correct the error. You can get rid of the date by running the command Clear > All.
Deleting cells
When cells are deleted, adjacent cells are shifted to fill in the resulting empty space. Delete cells like this.
1. Select the cells to be deleted and press the right mouse button.
2. In the context menu that appears, execute the command Delete.
3. In the window Deleting cells(Fig. 6.10) set the switch to the desired position and press the button OK.
Rice. 6.10. Deleting cells: executing a context menu command (left), choosing a deletion method (right)
ADVICE
You can quickly delete a row by right-clicking on the desired row in the row header area and executing the Delete command from the context menu. In this case, deletion is performed without the window shown in Fig. 6.10, left. You can delete columns in the same way.
Adding Cells
Sometimes it is necessary to add cells to an already completed table. To insert new cells, you need to expand existing ones. Deleting moves existing cells up or to the left, while inserting moves them down or right. The sequence of inserting cells can be as follows.
1. Select the cells where you want to insert new ones.
2. In a group cells tabs home click the arrow next to the button Insert and run the command Paste cells.
3. In the window that appears (Fig. 6.11), specify the method for inserting cells and click the OK button.
Rice. 6.11. Add Cells Dialog Box
To insert rows or columns, you can use menu commands - buttons Insert– Insert Rows to Sheet or Insert Columns to Sheet.
Moving and copying cells with the mouse button
To move one or more cells to a different location on a sheet using the mouse button, follow these steps:
1. Select the desired cell or select a range.
2. Move the mouse pointer to the frame of the cell or range so that it takes the form shown in fig. 6.12, left.
3. Holding down the mouse button, drag the selected data to the desired location (Fig. 6.12, on right), then release the mouse button.
Rice. 6.12. Moving cells by dragging: pointer view (left), selected data moved (right)
While dragging, a warning may appear (Figure 6.13) if there is already some data in the destination cells.
Rice. 6.13. Warning when moving cells
If you want to copy cells, drag them with the mouse button while holding down the Ctrl key. In this case, a small plus sign will appear next to the pointer.
The operation of dragging cells with the right mouse button has a wide range of possibilities. In this case, when you release the mouse button, you will see a context menu (Fig. 6.14) in which you can select one of the available actions for moving cells.
Rice. 6.14. Dragging cells with the right mouse button
When executing commands Move or Copy the contents of the end cells will be replaced without further warning. If you want to expand cells before moving or copying, choose one of the following commands: Move down and copy, Move right and copy, Slide down and move or Move right and move.
Team Copy only values allows you to keep the original formatting of the end cells, and the command Copy formats only, on the contrary, will copy the design of the source cells without changing the data in the destination cells.
Team To tie inserts a formula with a reference to the source cell, and any changes to the data in the source cell will automatically be reflected in the destination.
Using the clipboard
The sequence of copying cells using the clipboard is as follows.
1. Make the desired cell active or select their range.
2. Click the button Copy, which is in the group Clipboard Home tabs . The selected cells will be surrounded by a running dotted frame (Fig. 6.15).
3. Make active the cell where you want to paste. If you copied several cells, you should specify the upper left cell of the paste range.
4. Press the Enter key to copy or move. Note that in this case there is no need to run the command Insert.
Rice. 6.15. Copying cells using the Copy button
One or more cells are moved in the same way, only instead of a button Copy need to press the button To cut. The described method allows you to copy or move cells once.
There are many other options for inserting data in Excel, their number is even redundant for most users. For example, you have selected the required cells, run the command Copy and chose the insertion point, that is, completed steps 1-3 of the previous method. After that, you can choose one of the following options.
? The paste command can be executed in various ways: using the Paste button , context menu or the Ctrl+V key combination. In any of these cases, the insertion can be repeated several times.
After pasting, you can specify exactly how to perform it using the Options button. inserts(Fig. 6.16).
? You can select different insertion options by opening the button menu Paste with using the arrow at the bottom of the button (Fig. 6.17).
Rice. 6.16. Selecting the paste method using the Paste Options button
? Using the context menu command Paste copied cells you can perform an insert with a cell shift.
? Team Special insert(see Fig. 6.17) allows you not only to select the insertion option, but also to perform an operation with existing and inserted data (Fig. 6.18).
Rice. 6.17. Selecting the paste method using the Paste button menu
Rice. 6.18. Selecting Paste Options in the Paste Special Window
Autocomplete
Autocomplete allows you to automatically re-enter text. For example, you are filling in the table shown in Fig. 6.19. When retyping in the fifth line of the word speakers Excel will automatically substitute the missing part of the word, which will be highlighted in black, with which it is enough to agree by pressing the Enter key. If in the next line you need to enter another word for "Ko", for example Frame, then it should be entered further, ignoring the auto-completion that appears.
Autocomplete
Autocomplete is used to automatically fill several cells with sequences - numbers, dates, text or formulas. For example, to populate cells with dates, do the following:
Rice. 6.19. AutoComplete Example
1. Enter the start date in the first cell.
2. Make the cell with the start date active and move the mouse pointer over the fill handle, which is located in the lower right corner of the active cell. The mouse pointer should take the form of a thin "plus" (Fig. 6.20, left).
3. While holding down the mouse button, drag the mouse down. A tooltip with the value of the current cell will appear near the mouse pointer (Fig. 6.20, in the center).
4. After dragging the pointer to the desired number of cells, release the mouse button - the range will be filled with a sequence of values (Fig. 6.20, on right).
When autofilling, the date is automatically incremented by one depending on the format. For example, if you enter the start date as 01.2005, the filling will be done by month. Similarly, autocomplete with time values occurs.
To fill the cells with numbers, you must enter not one, but two initial values, so the sequence of actions will be different.
1. Enter the first and second value of the numeric sequence in adjacent cells.
2. Select both filled cells.
3. Place the pointer on the fill handle and move the mouse to the desired number of cells while holding down its button. Excel will fill in the cells based on the difference between the first and second number.
Similarly, the filling with date and time values occurs if the step differs from one.
There are other filling options, which are shown in Fig. 6.21. If Excel cannot determine how to fill in the cells, then it will simply copy them.
Rice. 6.20. Autocomplete workflow: pointer changed to a thin plus (left), tooltip (center), range filled with a sequence of values (right)
Rice. 6.21. Autocomplete Options
When autofilling, using the right mouse button, you can select one of the available filling methods in the context menu (Fig. 6.22). By executing the command Progression, you will see a window for setting advanced options.
You can create your own fill list in the following way.
1. Click on "Office" button then press the button Excel Options.
2. In the window that appears, click the Change button lists in Group Basic options for working with Excel.
Rice. 6.22. Autocomplete with the right mouse button
3. In the next window (Fig. 6.23) in the field Lists select NEW LIST, enter its elements in the field List Items and press the button Add.
4. To test the operation of the new list, close all windows using the button OK, enter the first element of the list in any cell, and perform the autocomplete operation.
Rice. 6.23. Autocomplete list settings window
Worksheet Management
A new book usually contains three sheets called Sheet1, Sheet2 and Sheet3. To switch to another sheet, click on its label at the bottom of the window. With a large number of sheets, labels can be scrolled using the buttons to the left of them. By right-clicking on the same buttons, you can select the desired sheet from the context menu.
With sheets, you can perform various operations, which are described in Table. 6.3.
Table 6.3. Worksheet Actions
Practical work 43. Operations with cells, ranges and sheets
Exercise. Learn to perform various operations with cells and ranges.
Execution sequence
1. Open a book Products saved in the previous lesson.
2. Select a range, row, column as shown in lesson 6.2 (see Fig. 6.7–6.9).
3. Delete the range of cells A3:D3 (see Fig. 6.10).
4. Select the range A1:D1 and add cells with a shift down.
5. With button Cancel cancel first the insertion and then the deletion of cells so that the table takes on its original form.
6. Without closing the book Products, open the book Electricity.
7. Select the range A4:B4 and drag it to the second line (see Fig. 6.12).
8. Return the moved cells back by dragging with the right mouse button.
9. Copy the table from the book Electricity on the second page of the book Products. For this:
1) in the book Electricity highlight the range A1:B7 and press the button Copy;
2) go to book Products via Taskbars windows;
3) go to the second sheet of the book (click on the label Sheet2 at the bottom of the window);
4) make cell A1 active and press the Enter key.
10. Rename sheets - Sheet1 v Products, a Sheet2 v Electricity.
11. Go to ListZ and perform various options for autocomplete cells (see Figure 6.21).
1. Add to the book created in the previous task a new sheet with the name The calendar.
2. To cell A1 new sheet, enter the word January, into a cell A2– Monday, and in cells VZ and AT 4- numbers 1 and 2.
3. Using only autofill, move, copy, and delete cells, create the calendar shown in fig. 6.24.
4. Save your changes.
Rice. 6.24. The calendar must be created using autofill, move and copy cells
Summarizing
Questions to check
? What are cell ranges and how are they designated?
? How to select multiple adjacent cells? How to select multiple nonadjacent cells?
? What is the difference between clearing and deleting cells?
? How many ways to copy cells do you know? Describe the sequence of copying with one of them.
? What's the difference between dragging cells with the left and right mouse buttons?
? What actions can be performed with worksheets?
? How does autocomplete work? In what cases is it enough to select one cell to start filling, and when it is necessary to select two?
In the next lesson, you'll learn the basic ways to enter and edit formulas, and learn how to use the built-in functions. In Lesson 6.4 you will perform more advanced calculations.
Lesson 6.3. Simple calculations in Excel
Using different number formats
Before solving mathematical, economic and other problems, you should understand how Excel stores, processes and displays numerical data.
All numbers in Excel are stored with an accuracy of 15 decimal places, and are displayed in accordance with the selected cell format. For all cells, Excel defaults to a common format that automatically detects the type of data you enter and then applies the appropriate formatting.
With the established general format, the following data entry rules apply:
? text is automatically aligned to the left, and numbers to the right;
? a comma is used to separate the integer and fractional parts of a number;
? when entering numbers separated by a dot, dash, or sign /, they are treated as dates (if possible);
? when entering numbers separated by a colon, they are treated as time values;
? if you add a % sign to a number, it will be presented in percentage format;
? numbers in the form of an ordinary fraction, for example 2/3, should be entered like this: 0 2/3 (you must specify the integer part of the number, even if it is equal to zero, and also put a space between the integer and fractional parts).
NOTE
The separator between integer and fractional parts of the number, currency designation and some other parameters depend on the regional settings in the Windows Control Panel. To check these settings, you need to open the Control Panel from the Start menu, select the Clock, language and region section, then the Change date time and number formats link.
The advantage of the general format is that it is enough to enter the data, and Excel will format it on its own. If the default formatting is not to your liking, you can change it in the following way.
1. Select the cell or range where you want to change the format.
2. Tab home in Group Number open the list Numeric format and select the desired formatting option (Fig. 6.25).
Rice. 6.25. Choice of number formatting
NOTE
When converting a date to a number format, Excel will display the number corresponding to the number of days that have passed since 12/31/1899.
In Group Number tabs home there are also several buttons for quickly changing the presentation of numerical data. These buttons have the following purpose.
Money format. The value of the main currency is automatically added to the number, a fixed number of decimal places is set, and every three digits of the number are separated by spaces. By clicking on the arrow next to this button, you can select other currencies.
percentage format. The number will be multiplied by 100 and the % symbol will be added to it.
Delimiter format. Similar to the currency format, but without the currency symbol.
Increase bit depth. Increases the number of decimal places in the fractional part of a number.
Reduce bit depth. Reduces the number of decimal places in the fractional part of a number.
ATTENTION
When calculating, Excel always uses the actual value of the number, and not the one that is displayed in the cell, taking into account the set number of digits. To force rounding of a number, you need to use the function ROUND, which will be discussed next.
If the standard formatting options are not enough, select the desired cells on the sheet and click on the dialog box call button in the Number group (Fig. 6.26). You can choose one of the available formats, as well as further customize it. For example, for the currency format, you can specify the currency symbol, the number of decimal places, and the format for negative numbers.
Rice. 6.26. Number tab of the Format Cells dialog box
Rules for entering formulas
As noted above, the main purpose of the Excel program is to perform calculations, for which formulas must be entered into table cells. Formula - this is the expression that Excel calculates against and displays the result. When creating formulas, you should be guided by the rules:
? the formula always starts with the = sign;
? the following signs of arithmetic operations are used in formulas:
+ - addition;
– - subtraction;
* - multiplication;
/ - division;
% - percent;
^ – exponentiation;
? formulas can use numbers, cell and range references, and built-in functions;
? the formula is always written on one line; to write complex formulas, parentheses are used, which establish the order in which calculations are performed; for the convenience of working with long formulas, enter not into a cell, but into the formula bar; very long formulas will automatically break into multiple lines in the formula bar.
Formulas can be entered both manually and using various automation tools. Entering formulas manually is time-consuming and unreliable, because in this case it is easy to make a mistake in the name of the cell or the name of the function. Despite these shortcomings, this method is useful when entering simple formulas or making small changes to already entered formulas.
To make it easier to enter formulas, you can use the following tricks:
? to quickly enter the cell name into the formula, just click on it with the mouse button;
? to correctly enter the name of the function and its parameters, use the button Insert function(fx) in the formula bar; in the Function Wizard window that appears, select the name of the function from the list and specify its arguments;
? To quickly enter similar formulas in multiple adjacent cells, enter the formula in the first cell, and then use AutoComplete.
You will find a detailed description of the process of entering formulas in the instructions for practical work 44.
Using the AutoSum button
To quickly sum up a range of cells, use the button autosum, which automatically generates the desired formula. The sequence of actions for summing several numbers will be as follows.
1. Make active the cell in which you want to place the summation result.
2. Press the button AutoSum
which is in the group Editing the Home tab. Excel will create a formula and try to automatically determine the range to sum, which will be highlighted by a dotted rectangle (Figure 6.27).
Rice. 6.27. Create a formula to sum a range using the AutoSum button
3. If the summation range is defined correctly, you can press the key Enter to complete the operation. Otherwise, you must first select the desired range using the mouse button, and then press the key Enter.
In addition to summing, you can perform other operations from the menu, which is opened by clicking on the arrow next to the button. AutoSum(Fig. 6.28).
Rice. 6.28. The AutoSum button has a menu for selecting the desired function
Assigning Button Menu Commands AutoSum is given in table. 6.4.
Table 6.4. Assigning menu commands to the AutoSum button
You can quickly get the results of calculations in the status bar: select several cells with numbers, and their number, sum and average value will be displayed in the status bar. To select a different set of functions for calculations, right-click on the summation results and check the boxes of the required functions.
Next, an example of solving a practical calculation problem using Excel will be given. Read it carefully, as it will cover the basic working methods that are used in solving most computational problems.
Practice 44: Creating a simple spreadsheet with formulas
Exercise. Create a payroll table for employees of the department, having the following initial data: the names of employees, their hourly rates and the number of hours worked.
In the table, you need to calculate: the amount of accrued salary for each employee, the amount of taxes withheld (for simplicity, we will take a single tax rate of 15%), the amount that needs to be paid on hand. In addition, you need to calculate the total amount of hours worked, wages and taxes for the entire department, as well as determine the average values of these indicators.
Execution sequence
1. Create a new book.
2. In the first line, enter the title of the table, and in the second - its "header". If necessary, increase the width of the columns by moving their borders in the column header area. Fill in the table with initial data, as shown in fig. 6.29.
Rice. 6.29. Input data for payroll calculation
3. To calculate the accrued salary, multiply the rate by the number of hours. To enter a formula in cell D3, do the following:
2) press the key = ;
3) click on the cell C3- the cell address must appear in the formula;
4) press the key * (multiplication);
5) click on cell B3; the formula should take the form =C3*B3;
6) to complete the input, press the Enter key, after which the result of the calculation should appear in the cell.
If the formula does not give the correct result, try entering it again.
4. To enter formulas in the remaining cells of column D, use autocomplete:
1) make cell D3 active;
2) move the mouse pointer over the autocomplete marker in the lower right corner of the cell (the mouse pointer should take the form of a thin “plus” (see Fig. 6.20, left);
3) with the button pressed, drag the mouse down to the required number of cells, then release the mouse button.
If you made a mistake when performing autocomplete, click the button Cancel on quick access bar to return to the original state and try the operation again.
5. Enter the formula in cell E3. According to the task, the tax rate is 15% of the accrued salary, so the formula should look like: =D3*15%. The sequence for entering this formula is as follows:
1) make cell E3 active and press the key = ;
2) click on cell D3 - the cell address should appear in the formula;
3) press the key * (multiply) and enter 15%;
4) press the Enter key and check the calculation result.
6. Use AutoComplete to enter formulas in the rest of the cells in column E.
7. The formula in cell F3 will look like: =D3-E3. Try to enter it yourself and fill in the other cells of column F.
8. To calculate the total number of hours worked, make cell C7 active, press the button AutoSum, verify that Excel has correctly defined the sum range, and then press Enter.
9. To calculate the remaining amounts, make cell C7 active and autofill to the right.
10. To calculate the average number of hours per employee, do the following:
1) make cell C8 active;
2) click the arrow next to the button AutoSum and run the command The average;
3) Excel will automatically enter the correct formula, but the range may not be defined correctly because you do not need to include cell C7 in it;
to correct it, use the mouse button to select the range C3:C6, which will be automatically inserted into the formula;
4) press the Enter key and check the calculation result.
11. Format the table:
1) to change the font in a cell, select it and use the group buttons Font tabs home;
2) for all cells with monetary values, set Delimited format with using the corresponding button in the group Number; if ##### symbols appear in some cells instead of numbers, you should increase the width of the corresponding columns;
3) to speed up the work, you can change the formatting of several cells at once, having previously selected them; The result of formatting the table may be as shown in Fig. 6.30.
12. Save the workbook with a name Salary.
Rice. 6.30. Payroll result
ATTENTION
Try to always check the correctness of the formulas. This will avoid errors in calculations.
Task for self-fulfillment
Three teams participate in the KVN game, the game itself consists of two competitions: a greeting and a musical one, each of which is evaluated by three judges. It is necessary to ensure a quick calculation of the results of the game. To do this, do the following.
1. Create a table as shown in fig. 6.31. To speed up data entry, copy repeating fragments.
2. In the highlighted cells of column F, enter the formulas for calculating average scores and totals. Check how the formulas work for different rating values.
Rice. 6.31. Table for calculating the results of KVN
3. At the last moment, the organizers of the game decided to add a "Homework" contest and include one more judge in the jury. Based on this, make the necessary additions to the settlement sheet.
4. Save the workbook with a name KVN.
Summarizing
Questions to check
? How are numbers represented and processed in Excel?
? What are the features of data entry when using a common format?
? How to change the representation of numbers in cells?
? What is a formula? What are the rules for creating formulas?
? How to enter cell names in a formula using mouse clicks?
? When should you use Formula AutoComplete? What is the sequence of this operation?
? How to create different formulas with a button Autosum?
? How to perform calculations using the status bar?
Make sure you fully understand the material in this lesson, as Lesson 6.4 will cover more complex calculations using various functions. In lesson 6.5, you will learn how to format ready-made calculation tables.
Lesson 6.4. Calculations Using Functions and Cell Names
Link types. Cell names
Cell and range references can be relative and absolute. Until now, formulas have used relative references, in which the cell designation consists of a letter and a number. Relative references have a useful feature: when autofilling or copying formulas to neighboring cells, the links in the formula will automatically change. This allows you to create formulas for only one line, and use autocomplete for the rest, which is what you did in the previous lesson when calculating payroll.
To make the reference absolute, you must add a dollar sign ($) to the row and column designations. For example, reference A2 is relative and reference $A$2 is absolute. An absolute reference does not change when copying or filling formulas. A special option is mixed references where only the column or row is absolute, such as $A2 or A$2.
It is better to use cell names instead of absolute references. Any cell or range can be given a unique name, which can then be used in formulas. To assign a name, select the desired cell, enter a new name instead of the address on the left side of the formula bar, and be sure to press the Enter key. Names make the formula more readable and have the same effect as using absolute references.
Sometimes it may be necessary to remove or change the assigned name. To do this, on the tab Formulas press the button Name Manager, in the window that appears, select the required name from the list and click the button Delete or Change.
Practice 45. Calculations using named cells
Exercise. There is a price list (Fig. 6.32), in which the price of each product is indicated in dollars; it is necessary to convert prices into rubles at the current exchange rate.
If you enter a formula for calculating the price in rubles in the first cell, you will get the correct result, but if you try to autofill the rest of the cells with this formula, the results will be erroneous. To avoid this, use an absolute cell reference IN 2 or give it a name.
Execution sequence
1. Create a table with initial data (see Figure 6.32).
2. Name cell B2. For this:
1) make it active;
2) click the mouse button in the address field on the left side of the formula bar;
3) enter a new name, for example Course (Fig. 6.33);
4) press the Enter key.
3. Create a formula for calculating the ruble price in cell C5. The formula should look like = B5*Course. Names, like links, do not have to be typed on the keyboard, it is enough to click on the desired cell when entering the formula.
4. Autocomplete the rest of the cells in the column WITH and check if the results are correct.
5. Save the workbook with a name Price.
Rice. 6.32. Price list example
Task for self-fulfillment
Create another payroll table that is calculated like this:
? for each employee, a monthly rate is set, which he will receive if he works the set number of working days;
? If an employee has worked less days than expected, then his salary is calculated as follows:
salary=rate*worked_days/working_days_in_month;
? take the amount of taxes equal to 13%;
? the amount payable for each employee must be converted to dollars at the current exchange rate.
An example of a table is shown in fig. 6.34. In the cells of the range D5:G9, enter the appropriate formulas (in the figure, this range is highlighted).
Rice. 6.34. Table for payroll using named cells
In order not to type the entire table, you can copy part of the data from the book created earlier Salary, and when saving the table, give it a name Salary2.
Clue. When compiling this table, you should use cell names that contain the number of working days and the current dollar exchange rate.
Function Wizard
Excel has many built-in functions that allow you to perform mathematical, economic, scientific and other calculations using predefined formulas. When using a function in a calculation, write its name followed by the arguments in parentheses. Arguments - these are the values that are used in this function. For example, consider using the function ROUND, which rounds a number to a given number of decimal places. Let's say you need to multiply cell values A2 and IN 2, and then round the result to two decimal places. The formula will look like this:
=ROUND(A2*B2,2).
The parentheses after the function name contain its arguments, which are separated by semicolons. In this case, the expression A2*B2 is the first argument, which is the rounding expression, and the number 2 is the second argument, which specifies the number of decimal places after rounding.
A formula with functions can be typed manually, but it is more convenient to use a special Function Wizard for this. It starts with a button. Insert function
which is in the formula bar. The work of the Function Wizard includes two stages: at the first stage, you should select the desired function from the list, and at the second stage, specify the arguments of the selected function. Working with the Function Wizard will be covered in Practice 46.
Practice 46. Performing calculations using the Function Wizard
Exercise. Use the function ROUND to round prices in the price list from the previous practical work (see Figure 6.32). Rounding is necessary because the conversion may result in several decimal places, and such prices are not always convenient.
Execution sequence
1. Open the price list that has been saved under the name Price.
2. Remove all formulas from column C.
3. Make active the cell in which you want to enter the formula. For this price list, this is the cell C5.
4. Press the button Insert function (fx) in the formula bar. The first window of the Function Wizard will appear (Fig. 6.35). If you are starting from the Function Wizard, then the = sign will be entered automatically.
5. Find the desired function in the list and press the OK button. By default, Excel offers a list of 10 recently used functions; if the desired function is not in it, there are the following search methods:
all functions in Excel are divided into several categories; after selecting the desired category, a list of the functions available in it will be displayed, for example, the function ROUND belongs to the category Mathematical;
if you don't know in which category to look for the desired feature, try typing a short description of the feature in the field Function search, and then press the button Find, e.g. search by word round up will display several functions that perform this operation.
Rice. 6.35. The First Step of the Function Wizard - Selecting a Function
6. After selecting the function, the second window of the Wizard will appear with fields for entering arguments (Fig. 6.36). For selected function ROUND two arguments must be entered - Number and Number_digits. In field Number enter the expression to be rounded. In this example, it will look like B6*Course. To enter cell references into a formula, just click on the cell; to see the desired cell, you may need to move the Wizard window to another part of the screen.
Rice. 6.36. Step 2 of the Function Wizard - Entering Function Arguments
7. To enter the second argument, click in the field Number_digits. Notice the changed tooltip at the bottom of the Wizard window. To round the price to tens of kopecks, set the number of digits to 1.
8. After entering all the arguments, click OK and check that the formula works correctly. To enter a similar formula in other cells in the column, use autocomplete.
9. Before closing Excel, save your changes.
Task for self-fulfillment.
Create a table to calculate the hypotenuse of a right triangle from two known legs. If we designate the legs with letters a and b, and the hypotenuse c, then the formula for calculating the hypotenuse will look like:
Use the math function to calculate the root ROOT.
Tips for Using the Function Wizard
If you have completed the tasks above, you should be convinced that the Function Wizard is an effective tool for creating payslips. A few tips for using it.
? You can insert a function in the middle or end of any formula. To do this, when entering or editing a formula, place the cursor in the desired place and press the button Function insert.
? To change the arguments of a previously introduced function, do the following:
1) make active the cell in which you want to edit the formula;
2) click on the name of the required function in the formula bar;
3) press the button Inserting a function- a window will appear with the arguments of the selected function, and you can edit them.
? When using unfamiliar functions, pay attention to the hints that appear at the bottom of the Function Wizard. Using the Help link for this function you will be taken to the Excel help system, where you will find a detailed description of the selected function and examples of its use.
? You can use one function as an argument to another (so-called nested functions). An example of the use of nested functions will be given later in the discussion of logical functions.
? For easy search and input of the desired function, use the group Function Library tab Formulas(Fig. 6.37). By clicking on any button, select the desired function from the list - you will go to the window for entering function arguments.
Rice. 6.37. Function library group
Errors in formulas
When creating formulas, errors can be made, as a result of which the formula will not give results or the results will be incorrect. Do not be upset: everyone makes mistakes, and the user's qualifications are largely determined by the ability to correct them.
If you make a syntax error in a formula, such as using extra parentheses, missing a required argument, or missing a punctuation mark, you will see a warning similar to the one shown in Figure 2 when you try to complete the formula. 6.38. In this case, you should analyze the text of the formula and correct the error or re-enter the formula. If Excel can pinpoint the exact location of the error, instead of warning you will be prompted to fix the error automatically. It is better not to enter functions manually, but to use the Function Wizard - this will significantly reduce the likelihood of errors.
Rice. 6.38. Formula error message
If Excel encounters an unsolvable problem while calculating a formula, an error message will appear in the cell instead of the result. Standard error messages and ways to fix them are shown in Table. 6.5. For detailed help about the error, select the cell with the error, click the exclamation mark button that appears next to the cell, and run the command Help for this error.
Table 6.5. Error messages
If no error messages appear after entering formulas, this does not mean that the result is correct. You can make a mistake in a cell reference or specify an incorrect arithmetic operation. Finding such errors is a difficult task. To check the correctness of the calculations, enter the control initial data and check the result. It is desirable to perform a check with several values of the initial data.
To check the correctness of the formula, make the desired cell active and click the mouse button in the formula bar. After that, all cells and ranges used in the formula will be highlighted with colored frames, and you can visually assess the correctness of their use, as well as analyze the text of the formula.
Visual analysis of the formula text is usually enough to find most errors, and in especially confusing cases, you can use the buttons in the Dependencies group formulas tab Formulas. By successively pressing the button Influencing cells, you can see all the cells involved in the calculation of the value in the active cell. Pushing a button Dependent Cells, you can see all the cells that use the value of the active cell. Using the Calculate button formula you can start the process of step-by-step calculation of the formula with the observation of intermediate results.
Boolean functions in Excel
When calculating, it is often necessary to choose a formula depending on specific conditions. For example, when calculating wages, different allowances can be applied depending on the length of service, qualifications or specific working conditions, which are calculated using different formulas. Creating such a calculation table can be a difficult task.
In such cases, logical functions can help, with the help of which Excel chooses one of several actions depending on specific conditions. Most important logic function IF is written like this:
IF(logical_expression, value_if_true, value_if_false).
In a boolean expression of a function IF numbers, dates, cell references, and > (greater than) signs can be used,< (меньше), = (равно), >= (no less),<= (не больше), <>(not equal). Function IF is performed as follows.
1. A logical expression is calculated, which can have one of two values: TRUE or LYING.
2. Depending on the result of evaluating a logical expression, the function returns one of two possible results that are written in the arguments value_if_true and value_if_false. Consider an example. Let the enterprise pay a salary bonus for seniority in the amount of 20% for employees with 10 or more years of experience. An example of a calculation table is shown in fig. 6.39, which shows the process of entering a formula in cell D3 using the formula bar. This formula uses the boolean expression C3>=10 to determine the employee's eligibility for the allowance. If the boolean expression will have a value TRUE, then the amount of the allowance is calculated by the formula B3*20%, otherwise, the result of the function will be zero.
Rice. 6.39. An example of entering a logical function IF
To combine several conditions into one, you can use the logical function AND, which returns a value TRUE if all input conditions are true TRUE. For example, the condition "the value of cell A1 must be greater than 5 and less than 10" is written as follows: AND(A1>5;A1<10). Boolean function OR returns meaning TRUE if at least one input condition evaluates to TRUE.
Another way to build complex boolean expressions is to use function nesting IF. This method will be discussed in the next practical work.
NOTE
The number of nestings of the IF function can be up to 64, which allows you to set complex conditions. Readers with a background in programming should note that the IF function is analogous to the conditional statement (if...then...else).
Practical work 47. Calculations using logical functions
To solve the problem, you need to formulate a verbal version of the solution. It might sound something like this: IF less than three years of experience then result: 0, else IF experience less than 10 years, then result: salary * 10%, otherwise result: salary * 20%. Bold font indicates keywords that define function arguments. You need to use two IF functions, the second of which will be nested inside the first.
Execution sequence
1. Create a table with initial data (see Figure 6.39).
2. Make the desired cell active (in this example, D3).
3. Run the command Formulas > Function Library > Boolean and select a function from the list IF.
4. Enter the function arguments. Following the verbal formulation of the solution, in the field Log _expression enter condition C3<3, and in the field value_if_true is the number 0.
5. In the field value_if_false create a nested function IF. To do this, place the cursor in the indicated field and select the function IF from the drop-down list in the formula bar (Fig. 6.40).
Rice. 6.40. Inserting a nested function
6. Specify the arguments of the second function. According to the verbal formulation, they will be as follows: Logic_expression– C3<10; Значение_если_истина – B3*10%; value_if_false– B3*20%.
7. Press the button OK and check the result of the formula with different input data. If everything was done correctly, the formula in cell D3 should be: = IF(SZ<3;0;ЕСЛИ(СЗ<10;ВЗ*10%;ВЗ*20%)). This formula is provided for verification purposes only, and it is strongly discouraged to enter it manually - this must be done in the manner described above using the Function Wizard.
8. Autocomplete the rest of the cells in column D with the generated formula and calculate the values for the column E. Check the correctness of the created formulas and save the table under the name Seniority allowance.
Summarizing
Questions to check
? What is the difference between absolute and relative cell references?
? What are the benefits of using names for cells and ranges?
? How to create a name and how to delete it?
? What is a function, function arguments?
? How to enter functions using the Function Wizard?
? What are logical functions; How does the IF function work? ?
? What errors can occur in calculations and how to correct them?
You have learned how to perform calculations in tables. Next, we will describe how best to format the results of calculations. In the next lesson, you will learn various techniques for formatting cells, sheets, and workbooks, after which you will learn how to create charts.
Lesson 6.5. Table Formatting
You can format the created table in various ways:
? apply inline styles to cells or the table as a whole;
? set formatting options for individual cells and ranges manually;
? set up conditional formatting for cells;
? create new documents based on ready-made templates with preconfigured formatting.
Each of these methods has its own characteristics, which will be described in this lesson.
Formatting with inline styles
Excel, like Word, has a set of built-in styles that allow you to quickly give a table the proper look. Inline styles can be applied to individual cells (ranges) or the table as a whole.
To format an entire table, follow these steps:
1. Select a range of cells to format as a table.
2. Tab home in Group Styles press the button Format as a table.
3. In the window that appears (Fig. 6.41), select the desired style.
Rice. 6.41. Choosing a table style
For tables formatted with styles, an additional tab appears on the ribbon Working with tables: Constructor(Fig. 6.42), where you can customize the table format using the checkboxes in the group Table style options.
Rice. 6.42. Table Tools tab: Design view
To apply one of the built-in styles to individual cells, follow these steps:
1. Select the desired cell or range.
2. Tab home in Group Styles press the button Cell styles.
3. In the window that appears (Fig. 6.43) select the desired style.
Rice. 6.43. Cell style selection
If the existing styles in Excel 2007 do not suit you, you can create your own. To create a new cell style, click the button Cell styles and run the Create command cell style. In the window that appears, enter a name for the new style, use the checkboxes to select the required components, click the button Format and specify cell formatting options. After saving the style, you can select it using the button Cell styles.
Creating a table style is done in the same way: run the command Format as Table > Create Table Style, enter a name for the new style, then sequentially select table elements from the list and set formatting options for each of them using the button Format.
ADVICE
To create a new style based on an existing one, open the table or cell style selection window, right-click on the style name and select Duplicate.
Cell Formatting
We've seen examples of quick formatting with styles above, but you can also format cells and ranges manually. To do this, select the desired cells and use the buttons in the Font groups , Alignment and Number tab Home. Working with a group of commands Number was discussed in detail in lesson 6.3, so let's focus on using the capabilities of the group Font and Alignment.
Working with teams in a group Font almost the same as using similar commands in the Word program (see lesson 5.3): in the group Font you can set the type and size of the font, as well as various font effects. These options usually apply to the entire cell or selected range, but in cell edit mode, you can specify these options for individual characters. This group also has buttons for setting the cell fill color.
and the type of its border
Consider the purpose of the buttons in the group Alignment.
Text alignment. Excel allows you to set both horizontal and vertical text alignment in cells. Vertical alignment makes sense for high-height lines, where text can be positioned at the top, bottom, and middle (Figure 6.44).
Rice. 6.44. Examples of different ways to align in a cell
Orientation. In Excel cells, text can be placed at any angle. The main ways of positioning text in a cell will be available in the Orientation button menu ; to select an arbitrary rotation angle, execute the Format command cell alignment this menu.
Text wrap. By default, text in cells is displayed on one line, but with a button Text wrap you can turn on the mode of breaking text into several lines with automatic increase in line height (as it is done in Word). Pressing the button again turns off stakeout mode.
Merge and center. To merge several cells into one, select them and click this button. Pressing it again will return the state of the cells to their original state.
Increase indent, Decrease indent. Changing the indent value in a cell. Left alignment changes the indentation on the left, right alignment changes the indentation on the right.
Additional options for formatting cells can be found in the dialog box. Cell Format, which can be opened by right-clicking on a cell or range and executing the command in the context menu Cell Format. This window consists of several tabs; to open the desired one, you can use the button to call the dialog box in groups Font, alignment(Fig. 6.45) or Number.
Rice. 6.45. Alignment tab of the Format Cells dialog box
Most items on a tab alignment window Cell Format duplicate the above group commands alignment, but if necessary, you can use some additional features. For example, you can enable autowidth, and the font size will automatically be reduced to a value that will fit all the text on one line. You can also set an arbitrary angle of rotation of the text in the cell using the elements of the group Orientation.
Assigning other window tabs Cell Format following:
? Number. Serves to set number formats (see lesson 6.3);
? Font. Almost completely duplicated by buttons in the group Font; may be needed to set effects strikethrough, superscript or subscript;
? The border. You can set cell borders and choose the type and color of lines;
? Fill. Allows you to select the fill for the cell, but this is easier done using the button Fill color in Group Font;
? Protection. You can enable or disable cell content protection and formula display; these settings will only take effect after sheet protection is enabled.
Row and column formatting
To change the width of a column, move its border in the area of the column headings. Similarly, you can change the height of the rows. To fine-tune the column width and row height, use the button menu that appears after executing the command Home > Cells and button press Format(Fig. 6.46).
After executing the command Column Width you can enter the desired column width manually. Similarly, you can adjust the line height using the command Line height. AutoFit Column allows you to set the minimum possible column width, at which all data in the cells are displayed in full. Similar commands exist for changing line heights.
Rice. 6.46. Commands for Formatting Rows and Columns
You can also hide individual rows or columns. For example, to hide a column, select it and click the button Format and run the command Hide or Show > Hide Columns. To display a hidden column, select two columns between which the hidden one is located, and by pressing the button Format, run command Hide or Show > Show Columns. You can hide and show rows in a similar way.
A template is a blank or form for frequently used documents. You can use a ready-made template, download a template from the Internet, or use any previously saved workbook as a template.
To create a new document based on a template, click Office button and run the command Create, to open a window Book creation. The following operations are available here:
? double click on the icon to create a new book A new book in the area of Empty and last;
? to create a document based on one of the installed templates, select Installed Templates(Fig. 6.47), then double-click on the icon of the desired template;
Rice. 6.47. Template selection window
? if you have an Internet connection, you can search for the desired template in one of the categories of the Microsoft Office Online group; after double-clicking on the selected template, it will be downloaded to the computer, then a new document will be created based on it;
? to use a previously saved workbook as a template, run the command From an existing document in Group Templates and specify the path to the document file.
After creating a new document based on the template, fill it with the necessary data, after which you can save or print it like any other Microsoft Excel workbook.
Conditional Formatting
You can significantly increase the visibility of paysheets using conditional formatting, which highlights cells with certain values. Consider the use of conditional formatting on the example of the table of results of the KVN competition (see Fig. 6.31), which was created in lesson 6.3. After applying conditional formatting, it might look like the one shown in Fig. 6.48.
Rice. 6.48. Example of a table with conditional formatting
To apply conditional formatting, you need to select the required range of cells, run the command Home > Styles > Conditional Formatting and select the desired option. For example, to display icons next to judges' scores (see Fig. 6.48), select the range C4:E10, in the button menu Conditional Formatting choose an option Icon sets and specify the desired type of icons (Fig. 6.49). To create columns in a field Average score select range F2:F14 and choose formatting option Bar graph.
Rice. 6.49. Choosing a Conditional Formatting Option
Sometimes in tables it is necessary to highlight cells with values that are greater than, less than, or equal to a certain number. To do this, the menu Conditional Formatting select item Cell selection rules, then - a condition and specify in the window that appears the number with which you want to compare cell values, and the desired formatting style.
If necessary, several conditions for formatting can be successively applied to one range. To view all set rules for the selected cell or range, click the button Conditional Formatting and run the command Rule Management. In the window that appears (Fig. 6.50), you can view and edit the established rules, add new ones and delete existing ones.
Rice. 6.50. Conditional Formatting Rules Manager
Any of the conditional formatting rules can be customized by double-clicking on it in the dispatcher window. For example, for any set of icons, you can manually specify the values at which each of the icons in the set will be displayed (Fig. 6.51).
Rice. 6.51. Window for changing formatting rules
Graphics in Excel
Excel allows you to use various types of graphic objects on sheets - autoshapes, WordArt objects, pictures, charts, drawings from files, etc. To insert graphic objects, there are tab options Insert. Working with graphics in Excel is almost the same as using graphics in Word, so for detailed instructions, refer to the lessons in the previous part of the book.
Preparing Sheets for Printing
A worksheet in Excel usually appears as a single, uninterrupted worksheet, although it may be split across multiple pages when printed, so it is often necessary to review and adjust the layout of the worksheet. To do this, you can use the Page layout, which is selected on the tab View. In this mode, the sheet will be displayed as it will look when printed (Fig. 6.52).
Rice. 6.52. Page Layout Mode
NOTE
Compared to previous versions, Excel 2007 has significantly changed and expanded the Page Layout View, but if necessary, on the View tab, you can select Page View, which is exactly the same as the Page Layout view in previous versions of Excel.
To prepare sheets for printing, it is convenient to use the features of the tab Page layout(see Figure 6.52). With it, you can perform the following actions:
? apply one of the built-in themes for the page, as was done in Word; you can also change individual elements of the theme - colors, fonts or effects; all these actions are performed using the buttons in the group Themes;
? adjust margins, page orientation, paper size, and other settings using the group buttons Page settings;
? you can print not the whole sheet, but a separate range of cells by selecting it by pressing the button Print area and running the command Ask; to cancel the printing of the range, press the button Print area and run the command Put away;
? to print headers on each sheet, press the button print headers, place the cursor in the field through lines and highlight the header lines on the sheet; similarly, you can use the field Through columns;
? to adjust the size of the pages you need to use the buttons in the group Enter; by changing the scale, you can print the sheet in an enlarged or reduced form; if necessary, adjust the scale for printing on a given number of pages, you can use the drop-down lists Width and Height;
? in Group Sheet Options you can turn on or off the display of the grid and titles on the screen using the checkboxes of the area View; the checkboxes in the Print area are used to enable or disable the printing of these items.
To create headers and footers, click in the header or footer area - a tab for working with headers and footers will appear on the ribbon (Fig. 6.53). Headers and footers in Excel are automatically divided into left, middle and right parts. You can insert ready-made elements into each of them using the group buttons Header and footer elements or type the text manually. To finish working with headers and footers, click on any cell of the sheet.
Rice. 6.53. Working with headers and footers in Excel
Click to preview and print Office button and select Print , where the following commands will be available.
? Seal. A print options window will appear, which is almost the same as the standard print dialog of most programs; the only feature is the ability to select a selected range, sheet, book or list for printing.
? Fast printing. The sheet will be printed without any dialog boxes appearing.
? Preview allows you to see the layout of information on sheets of paper in the preview window.
Practice 48. Formatting tables
Exercise. Format the table as shown.
Execution sequence
1. In a new workbook, create the table shown in fig. 6.54.
Rice. 6.54. Source table to perform formatting
2. Using cell formatters, bring the table to the following form (see Fig. 6.44) and save it under the name Formatting.
3. Open the previously created workbook named KVN(lesson 6.3) and using conditional formatting bring it to the following form (see Fig. 6.48).
4. Open a book Salary(lesson 6.3) and bring it to this form (Fig. 6.55), using the following techniques:
cell and table styles;
insert lines, pictures, WordArt objects, headers and footers, etc.;
using conditional formatting, highlight the values of those cells in column D in which the number of hours worked is greater than 150.
5. Check your page settings and preview before printing.
6. Print your document.
7. Save the table with a name Payroll sheet.
Rice. 6.55. Payroll Formatting Example
Summarizing
Questions to check
? What types of inline styles do you know?
? How to create a new style?
? What cell formatting options are available in Excel?
? How to open a window Cell format?
? Compare table formatting options in Word and Excel.
? How to automatically highlight certain numbers in Excel, for example, in bold?
? How to create a new document based on a template?
? What are the graphics capabilities of Excel?
In the next lesson, you will build charts and graphs that will present data in a visual and attractive way.
Lesson 6.6. Charts and Graphs
Understanding charts
Diagrams are used to present the data available in the table in a graphical form, which makes it possible to increase the visibility of the data, to show the ratio of various parameters or the dynamics of their change. Excel allows you to build charts of various types; simple examples of the most common diagrams are shown in fig. 6.56.
Rice. 6.56. Basic chart types
These charts have the following purpose:
? bar graph, or a bar chart, serves to quantitatively compare various indicators, that is, it clearly answers the question of what is more and what is less; you can choose other types of charts that are similar to histograms, differ only in the shape and position of the figures, for example, various options for bar charts;
? pie chart should be used to display parts of a whole; the circle is taken as 100%, and the values on which the chart is built are displayed as sectors of various sizes; a kind of pie chart is a donut chart, which is constructed in a similar way and differs only in appearance;
? charts are usually used to show the dynamics of changes in parameters over time, less often they show the change in one parameter relative to another.
With Excel, you can create other types of charts that are variations on the main types listed above. Information about the features of various types of charts and recommendations for their use can be found in the Excel help system.
Plotting and charting
Before building a chart, you need to create a table with the data that will be used in it. The structure of the table should be thought out in such a way that the data needed for the chart is in one or more columns. A chart based on a data table is built as follows.
1. Select the data for the chart on the sheet. You can include row and column headings in the selection area, in which case Excel will automatically create data and axis labels.
2. Select the type and type of diagram using the buttons on the tab Insert groups Diagrams – the diagram will be created automatically.
3. Format the diagram using the tools on the additional tabs for working with diagrams (Fig. 6.57).
Rice. 6.57. Histogram type selection
Chart Formatting
If the chart you've built doesn't meet your requirements, you can refine it using the simple and powerful tools in Excel 2007. To format your charts, there's a tab on the ribbon Working with charts, consisting of three tabs: Constructor, Layout and Format.
tab Constructor(Fig. 6.58) is designed for quick editing and formatting of diagrams and contains the following groups of commands.
Rice. 6.58. Working with charts, Design tab
? A type. If you unsuccessfully selected the type of chart, you can change it using the button Change chart type. Button Save as Template allows you to save the chart formatting and layout for later use.
? Data. Sometimes, for clarity in the chart, it is advisable to change the data in rows and columns - this is the button Row column. Button Select data opens the manual data setting window.
? Diagram layouts. Here you can select one of the built-in chart layouts. Built-in layouts differ in the presence or absence of individual chart elements, their shape and location.
? Chart styles. Designed to select one of the built-in styles. Excel 2007 offers three visual styles: black and white, multi-colored, and solid colors.
? Location. Allows you to move the chart to another sheet or create a new one for it.
With built-in styles and layouts, you can quickly format your chart; if features tab Constructor is not enough, you can format the chart manually.
For example, to manually customize the chart layout, open the tab Layout, where you can choose formatting options for titles, captions, axes, grids, walls, and other chart elements. To do this, use the menu buttons in the Captions groups, axes and Background(Fig. 6.59).
Rice. 6.59. Working with charts, Layout tab
Any chart consists of individual objects– titles, axes, grid lines, series, data labels, legend, etc. Some objects may in turn consist of several simpler objects. Each chart object has a dialog box for setting formatting options. To open this window, click on the desired element, then click the Format button selected fragment in Group current fragment. For example, in fig. 6.60 window shown Axis Format, in which you can customize the value scale, change the color and type of lines, fill, and other options. Similar windows are available for other chart elements; you can also select the desired object using the drop-down list in the group current fragment.
Rice. 6.60. Axis Format Window
Using the tab Format(Fig. 6.61), you can apply one of the built-in styles for individual chart objects, set the type of fill and outlines, and apply various effects to shapes. Elements for performing these actions are located in the group Shape styles. With the help of a group WordArt Styles you can set text effects for chart objects. Before executing the considered commands in the diagram, you should select the desired object.
Rice. 6.61. Working with charts, Format tab
You can also perform other actions on chart objects, such as moving them with the mouse button, resizing them by dragging frame handles, or deleting them from the chart with the Delete key. For elements containing text or numbers, you can change the font settings using the group buttons Font tab Home.
Printing charts
A chart located on the same sheet as a table can be printed as part of a worksheet or separately. You can print a chart separately from the rest of the sheet by selecting it and then running the command Office Button > Print > Print Preview for viewing before printing, and if the result of the viewing is satisfactory, you can use the Print button in the viewing window .
To print the chart along with the rest of the sheet, you need to set up the relative position of the chart and data in the display mode Page layout, as well as other page parameters (see lesson 6.5). Before printing, you need to make sure that none of the charts on the sheet is selected (for this, you can select any cell with data).
Practice 49. Building and formatting charts
Exercise 1. Create a table with data, build a histogram from the table and format it as shown in fig. 6.62.
Rice. 6.62. Histogram Example
Execution sequence
1. Create the required data table (see Figure 6.62).
2. Select the data you want for the chart. In this example, this is the range A2:D7.
3. Tab Insert press the button bar graph and select the required chart option (see Fig. 6.57). In this case, a volumetric version of the histogram with grouping is selected; to get information about the available types of histograms, move the mouse pointer over it and wait for the tooltip.
4. If you correctly selected the range with data, then after choosing the type of chart, it will be created automatically on the current sheet. You can move the chart to the right side of the screen and reduce its size so that the data cells are not covered.
5. Tab Constructor press the button Row column. Notice how the chart's appearance changes. Press the button again Row column- the diagram should return to its original form.
6. Try different layouts and styles for your chart by selecting them from the tab Constructor.
7. To change the name of the chart, select it with the mouse, then click on the text so that the text cursor appears and edit the text. Similarly, you can change the names of the axes.
8. Change the font size and type for the chart text elements. To do this, right-click on the desired element and use the buttons on the panel that appears.
9. Change the chart type with the corresponding tab button Constructor, for example, choose conical or pyramidal.
10. Apply other formatting effects to various elements yourself. If the applied effect makes the chart look worse, use the button Cancel on the Quick Access Toolbar. One version of the formatted histogram is shown above (see Figure 6.62).
Task 2. Build and format a pie chart to display the results of parliamentary elections, as shown in Figure 1. 6.63.
Rice. 6.63. Pie chart
Execution sequence
1. Go to the second sheet of the book and create a table with data (see Figure 6.63).
2. Select the data ranges for the chart. In this example, this is the AZ range :AT 8.
ATTENTION
When constructing pie charts, the range selected for the chart cannot include a row with total data - in this case, the chart will be built incorrectly.
3. Tab Insert press the button circular, select chart type Volumetric circular– the diagram will be built automatically.
4. Tab Constructor apply one of the built-in styles to the chart.
5. Run the command Working with Charts > Layout; use the buttons in the Labels group to turn off the display of the legend and try different options for displaying data labels.
6. To obtain signatures as in fig. 6.63 execute in the button menu Data Signatures command Additional data label options. In the window that appears, check the following boxes: category names, shares, callout lines and set the switch to position At the top outside.
7. Open a tab on the ribbon Format and apply WordArt effects to the chart title.
8. For each data sector, apply a color corresponding to the batch name. To select one sector of a circle, first select the entire circle by clicking the mouse button, and then click on the desired sector. After that, on the tab Format choose a new shape style or change its fill.
Task 3. Build a graph of changes in exchange rates during the year (Fig. 6.64).
Rice. 6.64. Chart example
1. The sequence of plotting is similar to the previous examples: you should create a table, select the desired range, select the type of chart and place it in a convenient place on the sheet.
2. In the graph (see Figure 6.64) for column values Dollar an auxiliary vertical axis is used. To achieve this effect, open the tab on the ribbon Layout, in Group current fragment select from the Row drop-down list "Dollar", then press the button Selection Format. In the window that appears, set the switch to the position along the minor axis and press the button close.
3. Using the buttons in the group Signatures tabs Layout adjust the position of the axis labels and the legend as shown in the figure (see Fig. 6.64).
4. Click the tab Format and format the various chart elements as you see fit.
5. Save your work in a book under the name Diagrams.
Task for self-fulfillment
1. Open the payroll table (file Salary) and complete it with a diagram. Choose the appropriate chart type yourself, as well as determine the range of source data. Format the built chart.
2. Similarly, build a diagram based on the results of the performance of teams in KVN (see lesson 6.3).
Summarizing
Questions to check
? What is a chart, what are the types of charts?
? In what cases is it advisable to use histograms, pie charts, graphs?
? Describe the general sequence of actions when building a diagram.
? What diagram elements do you know?
? How can I change the chart settings after it has been created?
? How to change the format of individual chart elements?
? How can you print a chart?
In the final lesson, you will learn about special techniques for working with large amounts of ordered data or databases.
Lesson 6.7. Databases in Excel
The concept of databases
Excel is often used to process large amounts of information that are presented in an organized way. data lists. An example of such information is the table shown above (see Figure 6.62). This list is also called database and apply the following terms and rules to it.
? The top row of the list is called the column headings and defines the overall structure of the list.
? All other lines in the list are called records. Each entry (row) must refer to one list object. In the example shown in fig. 6.65, each entry contains information about one product.
? The list column is called field; Each column must contain data of a certain type, according to the field header.
? All records must have the same number of fields, some fields can be left blank.
Rice. 6.65. Data List Example
When creating a database in Excel, you must follow the rules above and do not leave completely blank rows or columns. You can use only one or more of the top rows to create a list header. Most of the examples from the previous lessons are lists of data with a title on the top row.
NOTE
To work effectively with databases, there are specialized programs, such as Microsoft Access, which is included in the Microsoft Office 2007 package. A database in Access usually consists of several interrelated tables; In this lesson, we will assume that the database consists of one table.
Sorting data lists
Often there is a need to sort the data in the list, that is, to sort the records by the value of a particular field. Excel 2007 has commands for sorting data on two ribbon tabs:
? in Group Editing tabs home there is a button Sort and filter, after pressing which a menu appears with commands for sorting and filtering (Fig. 6.66, left);
? tab Data there is a group Sort and filter, buttons of which (Fig. 6.66, on right) correspond to the above menu commands of the Sort button and filter.
Rice. 6.66. Menu button Sort and Filter on the Home tab (left) and the Sorting and Filter command group on the Data tab (right)
There are two ways to sort in Excel: quick and custom. Quick sort is performed as follows.
1. Make active any cell with data in the column where you want to sort.
2. Click the button Sort and filter and run one of the commands Sort from A to Z and Sort from Z to A(see fig. 6.66, left). You can also use the buttons in the command group Sort and filter(see fig. 6.66, on right).
3. After executing the command, Excel will automatically determine the sort range and sort the rows by the selected column. If the list has the correct structure, then all existing entries will be included in the sort range.
Sometimes it becomes necessary to sort not the entire list, but a separate group of rows. In this case, the desired range is selected first, after which the sort command is executed.
When selecting individual columns for sorting, a dialog box may appear with the following choices:
? expand the range for sorting;
? sort only within selection;
? cancel sorting and try to correctly allocate the range.
ATTENTION
When sorting individual columns of data, there is a risk of distorting the information in the list by mixing up the values of the columns. For the list to be sorted correctly, the range must include all available columns.
ADVICE
If sorting was not performed as expected, run the command Cancel for return the table to its original state, and then try sorting in a different way.
Quicksort commands allow you to sort data by only one field. To sort data by multiple fields, use custom sorting. To do this, follow these steps.
1. Make any cell in the list active or select the entire list for sorting.
2. Run the following command: Sort & Filter > Custom Sorting(see fig. 6.66, left) or click the button Sorting in Group Sort and filter(see fig. 6.66, on right).
3. In the window that appears (Fig. 6.67), specify which column to sort by and specify its order. If Excel didn't automatically detect the column headers, check the box
Rice. 6.67. Sort Range Dialog Box
4. To sort by multiple fields, click the button Add level and enter the options for the second sort column. Similarly, you can add one or more levels. Sorting will be done after pressing the OK button.
Practice 50: Sorting a list of data
Exercise 1. Sort the records in the table (see Fig. 6.65) in descending order of the price of the goods (from higher to lower).
Execution sequence
1. Create a new workbook and populate the table with data (see Figure 6.65). Save it with a name Goods in stock.
2. Make active any cell with data in the column Price. Do not select multiple cells or an entire column.
3. Run the command Sort from Z to A, after which the table will take the form shown in Fig. 6.68.
4. To return the table to its original form, use the button Cancel on the Quick Access Toolbar.
Rice. 6.68. List of data after sorting by product price
Task 2. In the list of goods (see Fig. 6.65), sort by product categories, and within each category, in descending order of price.
Execution sequence
1. Make active any cell with data and click the button Sort in group Sort and filter tabs Data.
2. Listed Sort by select item Name. If you see column names instead of list headers in Excel, check the box My data contains headers.
3. Click the button Add level and in the list Then by select Price ,$.
4. Set to item Name order from A to I, and for the paragraph Price, $– Descending.
5. Click OK and check if the sorting is correct.
Using filters
You may encounter the task of selecting records from a large list of data, which takes a long time. To do this, the easiest way is to use the built-in data filtering capabilities. Excel 2007 has simple and convenient means of setting filters, which will be discussed in the next practical work.
Practice 51 - Selecting records from a list using a filter
Exercise 1. Select in the price list (see Fig. 6.65) only goods that are in stock.
Execution sequence
1. Open the list of products that is saved under the name Goods in stock(See Figure 6.65).
2. In a column Availability select any cell with value There is.
3. Right-click and in the context menu select Filter > Filter by selected cell value(Fig. 6.69). The autofilter mode will be enabled, and only entries that match the condition will remain in the list.
4. To cancel the filter, press the button Filter tab Data(see fig. 6.66, b).
Rice. 6.69. Setting a filter by cell value
Task 2. Select from the list all printers that are in stock and that can be ordered.
Execution sequence
1. To enable the filtering mode, select any cell with data and press the button Filter tab Data. Arrow buttons should appear on the right side of the list column headers.
2. First select all printers. This can be done using the context menu, as in the previous task, but there is another way:
1) click on the arrow button next to the title Name;
2) uncheck Select all, then check the box a printer and press the OK button (Fig. 6.70).
3. To select products that are in stock or can be ordered, click on the arrow button next to the heading Availability, uncheck Not and click OK. When multiple filters are applied in succession, they are superimposed on each other, so only printers that are in stock or that can be ordered will remain in the list.
Task 3. Select only monitors priced under $300 from the list.
Execution sequence
1. Cancel the previous filter and select all monitors using one of the methods described above.
Rice. 6.70. Filter installation
2. Click the arrow button next to the heading Price and run the command Numeric filters > less than.
3. In the corresponding field, enter the value 300 (Fig. 6.71) and click the button OK to apply the filter.
Rice. 6.71. Filter setting window by condition
Summary Calculations in Tables
Excel has tools for calculating summary data in tables and analyzing them. For example, using the command Subtotals you can automatically divide the table into groups and calculate totals for each of them.
A powerful tool for data analysis is the pivot table, which can be used to get multiple totals or group by multiple fields. Data from a list in a PivotTable can be presented as a two-dimensional table, which is often easier to analyze.
The use of these possibilities will be considered in the next practical work.
Practice 52. Final calculations in tables
Exercise 1. For the table shown in fig. 6.72, you need to calculate totals for individual dates or products using the tool Subtotals.
Rice. 6.72. Sales ledger (to be used in examples of calculating subtotals and building a pivot table)
Execution sequence
1. Create a table (see Figure 6.72) and save it as Sales accounting.
2. Sort the table by the field you want to group by (in this example, sort by date).
3. Make active any cell in the data list and press the button Interim results in group Structure tabs Data.
4. Run the command Data > Totals– the entire list will be highlighted and a window will appear Subtotals.
5. In this window, you need to specify the field, when changing which the totals should be calculated, which final operation to perform and for which fields the totals should be calculated. To solve the problem, you should set the values of the fields, as shown in Fig. 6.73.
Rice. 6.73. Dialog Subtotals
6. Press the OK button - Excel will calculate the subtotals (Fig. 6.74). Pay attention to the buttons that appear on the left side of the Excel window. With their help, you can change the view of the list by hiding certain groups of data.
Rice. 6.74. Subtotal calculation result
7. You can remove subtotals from the sheet by pressing the button again Subtotals and in the window that appears (see Fig. 6.73) by pressing the button Remove all.
Task 2. Build a pivot table for the sales ledger (see Figure 6.72), from which you can find out the total values of the number of goods and the amount of sales in the context of both dates and each product item.
Execution sequence
1. Open a book saved with a name Sales Accounting, and make any cell in the data list active, or select the entire list.
2. Tab Insert press the button pivot table.
3. In the pivot table creation window (Fig. 6.75), check the correctness of the data range definition and click the OK button.
4. In the field Pivot Table Field List check the boxes date, Name of product, Quantity and Sum- the pivot table will be created automatically (Fig. 6.76).
Rice. 6.75. PivotTable Creation Window
Rice. 6.76. Pivot Table Example
5. Change the structure of the pivot table by dragging fields between the row, column, or value label areas. To get the table shown in Fig. 6.77, drag box date from the region Row names to the Name area columns and turn off the field display Quantity by clearing the corresponding checkbox.
Rice. 6.77. Pivot table after restructuring
6. Explore the tools for changing the structure and format of a pivot table on your own using the contextual tabs that appear on the ribbon when you select a table:
tab Parameters you can change the parameters of the table as a whole, individual fields, etc.; for example, to change the final operation, make any cell in the value area active, click the button Field options in group active field and select the desired operation;
tab Constructor you can change table display options and apply inline styles for automatic formatting.
Summarizing
Questions to check
? Define the following terms: database, field, record.
? What rules should be followed when creating lists of data?
? How to sort data?
? How to install a filter and how to remove it?
? How to filter records that meet several criteria?
? What analysis capabilities do subtotals and pivot tables have?
? How to create a pivot table and change its properties?
This was the last Excel lesson. You have completed all the tasks set at the beginning of this part of the book. As in the study of Word, the main features of Excel were considered here, allowing you to solve most practical problems. For self-education and improving your professional level, you can give the following tips:
? try to make calculation tables as often as possible;
? a difficult task is not always possible to solve the first time - even professionals often find a solution only after several attempts;
? the number of built-in functions in Excel is several hundred, and among them you can find the one that will solve the problem in the best way; The Feature Wizard and Help system will help you understand the new features.
The next chapter of the book is devoted to creating and using databases in Microsoft Access. You've already learned the basics about databases and know that you can create them in Excel, but Access has more features that you'll need to learn about.
