Educational and practical guide in excel. EXCEL formulas with examples - Instructions for use. Konrad Carlberg “Business analysis using Excel”

If you've never used a spreadsheet to create documents before, we recommend reading our guide to Excel for Dummies.

You'll then be able to create your first spreadsheet with tables, graphs, math formulas, and formatting.

Detailed information about the basic functions and capabilities of the table processor.

Description of the main elements of the document and instructions for working with them in our material.

By the way, to work more efficiently with Excel tables, you can familiarize yourself with our material.

Working with cells. Filling and formatting

Before taking specific actions, you need to understand the basic element of any .

An Excel file consists of one or several sheets divided into small cells.

A cell is a basic component of any Excel report, table or graph. Each cell contains one block of information. This could be a number, date, monetary amount, unit of measurement, or other data format.

To fill out a cell, simply click on it with the pointer and enter the required information.

To edit a previously filled cell, double-click on it.

Rice. 1 – example of filling cells

Each cell on the sheet has its own unique address. Thus, you can carry out calculations or other operations with it.

When you click on a cell, a field will appear at the top of the window with its address, name and formula (if the cell is involved in any calculations).

Select the cell “Share of shares”. Its location address is A3. This information is indicated in the properties panel that opens. We can also see the content.

This cell has no formulas, so they are not shown.

More cell properties and functions that can be applied to it are available in context menu.

Click on the cell with the right mouse button.

A menu will open with which you can format the cell, analyze the contents, assign a different value, and other actions.

Rice. 2 – context menu of the cell and its main properties

Sorting data

Often users are faced with the task of sorting data on a sheet in Excel. This feature helps you quickly select and view only the data you need from the entire table.

It’s already in front of you (we’ll figure out how to create it further in the article). Imagine that you need to sort data for January in ascending order.

How would you do it? Simply retyping a table is extra work, and if it is large, no one will do it.

There is a special function for sorting in Excel. The user is only required to:

• Select a table or block of information;
• Open the “Data” tab;
• Click on the “Sorting” icon;

Rice. 3 – “Data” tab

• In the window that opens, select the table column on which we will carry out actions (January).
• Next is the sorting type (we group by value) and, finally, the order - ascending.
• Confirm the action by clicking on "OK".

Rice. 4 – setting sorting parameters

The data will be sorted automatically:

Rice. 5 – the result of sorting the numbers in the “January” column

Similarly, you can sort by color, font and other parameters.

Mathematical calculations

The main advantage of Excel is the ability to automatically carry out calculations while filling out the table. For example, we have two cells with values ​​2 and 17. How can we enter their result into the third cell without doing the calculations ourselves?

To do this, you need to click on the third cell in which you will enter final result calculations.

Then click on the function icon f(x) as shown in the image below.

In the window that opens, select the action you want to apply. SUM is the sum, AVERAGE is the average, and so on.

A complete list of functions and their names in the Excel editor can be found on the official Microsoft website.

We need to find the sum of two cells, so click on “SUM”.

Rice. 6 – select the “SUM” function

There are two fields in the function arguments window: “Number 1” and “Number 2”. Select the first field and click on the cell with the number “2”.

Its address will be written into the argument line.

Click on “Number 2” and click on the cell with the number “17”. Then confirm the action and close the window.

If you need to perform mathematical operations with three or more cells, simply continue entering the argument values ​​in the Number 3, Number 4, and so on fields.

If the value of the summed cells changes in the future, their sum will be updated automatically.

Rice. 7 – result of calculations

Creating tables

You can store any data in Excel tables.

Using the function quick setup and formatting, it is very easy to organize a personal budget control system, a list of expenses, digital data for reporting, etc. in the editor.

They have an advantage over a similar option in other office programs.

Here you have the opportunity to create a table of any size. The data is easy to fill out. There is a function panel for editing content.

In addition, the finished table can be integrated into docx file using the usual copy-paste function.

To create a table, follow the instructions:

• Open the Insert tab. On the left side of the options panel, select Table. If you need to consolidate any data, select the “Pivot Table” item;
• Using the mouse, select the space on the sheet that will be allocated for the table. And also you can enter the data location in the element creation window;
• Click OK to confirm the action.

Rice. 8 – creating a standard table

To format appearance the resulting sign, open the contents of the designer and in the “Style” field, click on the template you like.

If desired, you can create your own view with a different color scheme and cell highlighting.

Rice. 9 – table formatting

Result of filling the table with data:

Rice. 10 – completed table

For each table cell, you can also configure the data type, formatting, and information display mode. The designer window contains all the necessary options for further configuration of the sign, based on your requirements.

Working with Excel: tutorial. Excel (Excel) is one of the basic programs of the package Microsoft Office. This indispensable assistant when working with invoices, reports, tables.
Excel allows you to:
program, store huge amounts of information
Build graphs and analyze results
Make calculations quickly
This program is an excellent choice for office work.
Getting started with Excel (Excel)
1. Double-click on the sheet name to enter editing mode. In this panel, you can add a new sheet to the book or delete an unnecessary one. This is easy to do - you need to right-click and select the “Delete” line.

2. It’s easy to create another book - select the “Create” line in the “File” menu. A new book will be located on top of the old one, and an additional tab will appear on the taskbar.

Thanks to the tabular form of data presentation, tables automatically turn into a database. It is customary to format tables; to do this, select the cells and give them individual properties and format.

A simple F=ma is the formula, force equals mass times acceleration.

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 required formula as quickly as possible. To do this, you can enter its name and use the category.

Excel is very convenient and easy to use. All functions are divided into categories. If category required function is known, then its selection is carried out according to it.

If the function is unknown to the user, he can set the category “full alphabetical list”.

For example, the task is given to find the SUMIFS function. To do this you need to go to the category mathematical functions and find the one you need there.

VLOOKUP function

Using the VLOOKUP function, you can extract the necessary information from tables. The essence of vertical scanning is to find the value in the leftmost column of a given range.

After which 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 traced using an example that provides a list of surnames. The task is to find the last name using the given number.

Using the VLOOKUP function

The formula shows that the first argument of the function is cell C1.

The second argument A1:B10 is the range in which the search is carried out.

The third argument is the ordinal number of the column from which the result should be returned.

Calculate a given last name using the VLOOKUP function

In addition, you can search for a last name even if some serial numbers missed.

If you try to find a last name from a non-existent number, the formula will not give an error, but will give the correct result.

Searching 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 interval viewing.

It has only two meanings - “false” or “true”. If the argument is not specified, it is set to true by default.

Rounding numbers using functions

The program's functions allow you to accurately round any fractional number up or down.

And the resulting value can be used in calculations in other formulas.

Numbers are 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.

Round a number using a formula

In this case, the number was rounded up. To round the value down, select the ROUND DOWN function.

Rounding occurs to the nearest whole number. In our case, up to 77 or 76.

Excel helps simplify any calculations. Using a spreadsheet you can complete higher mathematics assignments.

The program is most actively used by designers, entrepreneurs, and students.

The whole truth about Microsoft Excel 2007 formulas

EXCEL Formulas with examples - Instructions for use

If new data appears on the sheet for the constructed chart that needs to be added, then you can simply select the range with new information, copy it (Ctrl + C) and then paste it directly into the diagram (Ctrl + V).

Suppose you have a list of full names (Ivanov Ivan Ivanovich), which you need to turn into abbreviated ones (Ivanov I. I.). To do this, you just need to start writing the desired text in the adjacent column manually. On the second or third Excel line will try to predict our actions and perform further processing automatically. All you have to do is press the Enter key to confirm, and all names will be converted instantly. In a similar way, you can extract names from email, merge full names from fragments, and so on.

You most likely know about the magic autofill marker. This is a thin black cross in the lower right corner of a cell, by pulling it you can copy the contents of the cell or a formula to several cells at once. However, there is one unpleasant nuance: such copying often violates the design of the table, since not only the formula is copied, but also the cell format. This can be avoided. Immediately after pulling the black cross, click on the smart tag - a special icon that appears in the lower right corner of the copied area.

If you select the “Copy values ​​only” option (Fill Without Formatting), Excel will copy your formula without formatting and will not spoil the design.

In Excel you can quickly display interactive map your geodata, for example sales by city. To do this, you need to go to the “App Store” (Office Store) on the “Insert” tab and install the “Bing Maps” plugin from there. This can also be done from the site by clicking the Get It Now button.

After adding a module, you can select it from the My Apps drop-down list on the Insert tab and place it on your worksheet. All you have to do is select your data cells and click on the Show Locations button in the map module to see our data on it. If desired, in the plugin settings you can select the type of chart and colors to display.

If the number of worksheets in a file exceeds 10, then it becomes difficult to navigate through them. Right-click on any of the sheet tab scroll buttons in the lower left corner of the screen. A table of contents will appear, and you can go to any desired sheet instantly.

If you've ever had to manually move cells from rows to columns, you'll appreciate the following trick:

1. Select a range.
2. Copy it (Ctrl + C) or by right-clicking and select “Copy”.
3. Right-click on the cell where you want to paste data and select one of the options from the context menu special insert- “Transpose” icon. Older versions of Excel do not have this icon, but you can solve the problem by using Paste Special (Ctrl + Alt + V) and selecting the Transpose option.

If in any cell you are supposed to enter strictly defined values ​​from the allowed set (for example, only “yes” and “no” or only from a list of company departments, and so on), then this can be easily organized using a drop-down list.

1. Select the cell (or range of cells) that should contain such a restriction.
2. Click the “Data Validation” button on the “Data” tab (Data → Validation).
3. In the “Type” drop-down list, select the “List” option.
4. In the “Source” field, specify a range containing reference variants of elements that will subsequently appear as you enter.

If you select a range with data and on the “Home” tab click “Format as Table” (Home → Format as Table), then our list will be converted into a smart table that can do a lot of useful things:

1. Automatically expands when new rows or columns are added to it.
2. The entered formulas will be automatically copied to the entire column.
3. The header of such a table is automatically fixed when scrolling, and it includes filter buttons for selection and sorting.
4. On the “Design” tab that appears, you can add a total line with automatic calculation to such a table.

Sparklines are miniature diagrams drawn directly in cells that visually display the dynamics of our data. To create them, click the Line or Columns button in the Sparklines group on the Insert tab. In the window that opens, specify the range with the original numerical data and the cells where you want to display sparklines.

After clicking the "OK" button Microsoft Excel will create them in the specified cells. On the “Design” tab that appears, you can further configure their color, type, enable the display of minimum and maximum values and so on.

Imagine: you close the report you've been fiddling with for the last half of the day, and the "Save changes to file?" dialog box appears. suddenly for some reason you press “No”. The office is filled with your heart-rending scream, but it’s too late: the last few hours of work have gone down the drain.

In fact, there is a chance to improve the situation. If you have Excel 2010, then click on “File” → “Recent” (File → Recent) and find the “Recover Unsaved Workbooks” button in the lower right corner of the screen.

In Excel 2013, the path is slightly different: “File” → “Information” → “Version Control” → “Recover Unsaved Workbooks” (File - Properties - Recover Unsaved Workbooks).

In later versions of Excel, open File → Details → Manage Workbook.

A special folder will open from the depths of Microsoft Office, where temporary copies of all created or modified, but unsaved books are saved in such cases.

Sometimes when working in Excel, you need to compare two lists and quickly find the elements that are the same or different. Here is the fastest and most visual way to do this:

1. Select both columns to compare (hold down the Ctrl key).
2. Select on the Home tab → Conditional Formatting → Highlight Cell Rules → Duplicate Values.
3. Select the Unique option from the drop-down list.

Have you ever tweaked the input values ​​in your Excel calculation to get the output you want? At such moments, you feel like a seasoned artilleryman: just a couple of dozen iterations of “undershooting - overshooting” - and here it is, the long-awaited hit!

Microsoft Excel can do this adjustment for you, faster and more accurately. To do this, click the “What If Analysis” button on the “Data” tab and select the “Parameter Selection” command (Insert → What If Analysis → Goal Seek). In the window that appears, specify the cell where you want to select the desired value, the desired result and the input cell that should change. After clicking “OK,” Excel will perform up to 100 “shots” to find the total you require with an accuracy of 0.001.

MINISTRY OF EDUCATION AND SCIENCE OF THE RUSSIAN FEDERATION

STATE EDUCATIONAL INSTITUTION OF HIGHER PROFESSIONAL EDUCATION

ROSTOV STATE CIVIL UNIVERSITY

Approved at a meeting of the ISS department

Guidelines

and test assignments in the discipline “Informatics” Parts 1 and 2

for bachelors of distance learning

Rostov-on-Don

Advanced features of Microsoft Excel 2007. Solving practical problems.

annotation

Given tutorial describes the advanced capabilities of Microsoft Office Excel 2007, a program included in the software Microsoft package Office 2007 (Russian version)

General information

Sheet sizes

IN earlier versions of Excel each sheet has 65,536 rows and 256 columns.

Excel 2007 has more available space - the worksheet contains:

Increase sheet size book created in the format Excel 97–2003:

1. open the file in Excel 2007,

2. choose Office - Convert, save in Excel format 2007.

When saving large tables(more than 65,536 rows) in the format of earlier releases of Excel, it is important to remember that data exceeding the worksheet size of 65,536 rows and 265 columns will be lost.

To use Excel 2000–2003 to open and save files created in Excel 2007 format, you must install compatibility pack(this module can be downloaded from the Microsoft website and installed)

Excel Limitations

Memory – buffer for formulas and pivot tables increased to 2GB (1GB in Excel 2003) Sorting – sort levels increased to 64 (3 in Excel 2003)

AutoFilter – drop-down list of up to 10,000 items (1,000 in Excel 2003)

Maximum formula length– up to 8,000 characters (1,000 in Excel 2003) Function nesting– up to 64 (7 in Excel 2003)

Conditional Formatting– an infinite number of criteria (3 in Excel 2003) Limit number of formats cells in a workbook – up to 64,000 (4,000 in Excel 2003) Number of colors– up to 4.3 million (56 in Excel 2003)

Number of characters in cell – up to 32,000 (1,024 in Excel 2003) Number of kickbacks (Undo) – up to 100 (16 in Excel 2003)

Program interface

All tools are grouped on the Ribbon according to the principle of general action. Accessing toolboxes - Tabs.

In addition to permanent tabs, tabs associated with the selected object (these are called contextual tabs) appear dynamically.

On each tab, tools are also grouped into sets of related commands - groups. If a group of tools has a corresponding dialog box, then to call it you need to click the button in the lower right part of the group. The feed can be hidden/showed double click on the active tab.

The Office button is a new representation of the File menu, containing new features along with the previous File menu commands.

In the Office menu, the Excel Options button opens the program settings window, which includes three familiar previous versions windows: Tools – Options,

Add ins, Customize.

Adding buttons to the Quick Access panel:

1. Select Office - Excel Options(Excel Options)

2. Go to category Customize

3. In the top list, select a group of commands,

4. Select a tool;

5. Click Add, then OK.

New opportunities

Manual input

When entering a formula manually, a hint appears listing the names of cells and functions. To select, double click on the name. When you select a function, appears short description arguments.

Hotkeys

If you press ALT, prompts will appear on the screen for selecting commands without using the mouse. Keys need to be pressed sequentially.

CTRL + N – Create,

CTRL + O – Open,

View mode Page Layout View

In this mode, the table will be shown as it will be printed. Fields, headers, footers, rulers, pagination are displayed, allowing the user to work with a table oriented for further printing.

To switch to Page Layout mode you need to:

1. Select the View tab;

2. In a group Book viewing modes(Workbook Views), select Page layout(Page Layout).

Another way to enter this mode is to select the button Page layout(Page Layout) on the right side of the status bar of the program window.

Status bar

As before, it is located at the bottom of the program window. Easy to customize – tooltip options can be configured by right-clicking on the status bar. A button and a zoom slider appear here

Live Preview

When designing a table, the function "live" viewing makes it possible to see the final result before the command is applied. This saves time that was previously spent on undoing an unsuccessful action.

For use preview options, you need to stop the cursor on the desired option. The highlighted area will change to show your selection. To apply the appropriate option, you need to click on it. This function can be disabled in the Popular section of the Excel Options window.

Data input

With the content of cells

IN Each sheet cell can contain:

1. TEXT (default alignment - left edge of cell)

2. NUMBER (default alignment - right edge of cell)

a. Normal (in the Russian language standard the separator of the integer and fractional parts is a comma, and in English it is a dot)

Without changing the system locale, you can configure Excel program on the other is a separator for the integer and fractional parts. For

This should be turned off in the Advanced section of the Excel Options window. Use system delimiters

(Use system separator), enter the separator character.

To represent numbers in Excel uses 15 digits. If you enter a number that is more than 15 digits long (both before and after the decimal point), Excel will store it to the nearest 15 digits, replacing the remaining digits with zeros.

Very large numbers are automatically represented in scientific notation: 1.2 E+19 = 1.2*1019 or 1.2 E19 = 1.2*1019. The largest positive number is 9.9 E+307; the smallest positive number is 1 E307 (numbers greater than 9.9 E+307 become text, less than 1 E307 become zero).

b. Date (start date – 01/01/1900, end date – 12/31/9999).

The separator in date entries is a slash day/month/year or a period.

01.01.1900=1, 02.01.1900=2, … 31.12.9999=2 958 465

You can change the first date to 01/02/1904 by including Use 1904 date system(Use 1904 date system) in the Additional section

(Advanced) Excel Options window.

c. Time (from 00:00:00 to 23:59:59) – fractional part of the day: 12:00=0.5.

If the number of hours is greater than 23, the entered time is converted to datetime format. For example, 24:12:15 = 01/01/1900 0:12:15

You can change the system locale settings in the Control Panel (Start - Settings - Control Panel) in the Regional and Language Options group. These settings affect the operation of programs and cover, in addition to numbers, the monetary unit, the form of recording date and time.

3. FORMULA. Formulas begin with the = (equal) sign and may contain parentheses (precedence of operations), cell references, cell and range names, operation signs, function calls, etc.

Cell range: A1:A25 (everything from A1 to A25, symbol: colon)

Cell range: A1:C25 (all A1 to A25, B1 to B25, C1 to C25)

Group of cells: A1;A25 (2 cells A1 and A25, symbol ; semicolon)

By default, the cell with the formula displays the answer (number or text),

V Formula bar – formula. This mode can be changed by enabling

Show formulas, not their values (Show formulas in cell instead...)

in the Advanced section of the Excel Options window (or “Ctrl”+“`”)

Switch between types of links - F4 key on the keyboard:

W50 F4 $W$50 F4 W$50 F4 $W50 F4 W50

Linking sheets of one book

When entering a formula necessary:

1. Go to the desired sheet (click on the sheet tab);

3. Enter next operation sign

Linking workbooks

First you need to open the linked books and When entering a formula:

1. Go to the necessary book, sheet(where does the link for the formula come from);

2. Select a cell or range of cells;

3. Enter next operation sign or Enter (to complete the formula).

On the Data tab, in the Connections group, the button

Edit Links will allow you to update, change, break links . When the connection is broken, formulas and external links are irrevocably replaced by their values ​​(text or number at the time of the break).

Using names in formulas

Creating names

1 way. Formula bar

2. In the Name field (left side of the formula bar), enter a new name;

3. Press Enter

Name length up to 255 characters. 1st character: letter, _ (underline) or\ (rev. slash), followed by letters, numbers, dots and underscores. Without spaces ! Names in the form of cell references are prohibited (for example, Z$100 or R1C1).

Method 2. From table row and column headers

1. Select a range, including headings rows and columns;

2. On the Formulas tab in the group Specific names(Defined Names) select command Create from selection(Create from Selection);

3. Specify header location(row above/below, column left/right).

The Names obtained in this way coincide with the names of the first or last row (column) of the range, but refer only to the cells located below (to the right), above (to the left) of these row (column) headings.

3 way. Name Manager Dialog Box

1. Select a cell (range);

2. Formulas tab, select Name Manager;

3. Click the New button in the Create Name window that appears:

a. Enter Name,

b. Define Scope

(within a sheet/book),

c. Enter