1 what is microsoft excel used for. Microsoft Office Excel - what is it? Calculation in Excel

Excel is a widely used computer program. It is needed to carry out calculations, compile tables and diagrams, and calculate simple and complex functions. It is part of the Microsoft Office suite.

This is a set of programs for office work. The most popular applications in it are Word and Excel.

Excel is something like a calculator with many functions and capabilities. In this program you can create reports, make calculations of any complexity, and create diagrams. It is needed, first of all, by accountants and economists.

It is a large table into which you can enter data, that is, print words and numbers. Also, using the functions of this program, you can perform various manipulations with numbers: add, subtract, multiply, divide and much more.

Many people think that Excel is only about tables. They are convinced that all tables on the computer are compiled in this program. But that's not true. This program is needed primarily for calculations.

If you need to not only draw a table with words and numbers, but also perform any actions with the numbers (add, multiply, calculate percentage, etc.), then you need to work in Microsoft Excel. But if you need to create a table without calculations, that is, enter ready-made data, then it is faster and more convenient to do this in Microsoft Word.

Excel, compared to Word, is, of course, more complicated. And it’s better to start working in this program after you master Word. It will take a lot of time to learn Excel thoroughly. But, as practice shows, most people only need basic skills to get the job done.

How to open Excel

Click on the "Start" button in the lower left corner of the screen.

A list will open. Click on All Programs (Programs).

A new list will appear. Find "Microsoft Office" and click on it. If you do not see such an inscription, then most likely the office software package (including Microsoft Excel) is not installed on your computer.

Microsoft Excel (also sometimes called Microsoft Office Excel) is a spreadsheet program created by Microsoft Corporation for Microsoft Windows, Windows NT, and Mac OS. It provides economic-statistical capabilities, graphical tools, and, with the exception of Excel 2008 on Mac OS X, the macro programming language VBA (Visual Basic for Applications). Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular programs in the world.

A valuable feature of Excel is the ability to write code using Visual Basic for Applications (VBA). This code is written using an editor separate from the tables. The spreadsheet is managed using an object-oriented code and data model. Using this code, the data from the input tables will be instantly processed and displayed in tables and charts (graphs). The table becomes the interface of the code, making it easy to work with, change it, and guide calculations.

Using Excel, you can analyze large amounts of data. In Excel, you can use more than 400 mathematical, statistical, financial and other specialized functions, link different tables to each other, choose arbitrary data presentation formats, and create hierarchical structures. Truly limitless methods for graphically representing data: in addition to several dozen built-in chart types, you can create your own, which can be customized to help visually display the subject of the chart. Those who are just learning to work with Excel will appreciate the help of "wizards" - auxiliary programs that help when creating charts. They, like good wizards, asking leading questions about the expected next steps and showing, depending on the planned answer, the result, will guide the user “by the hand” through all the stages of constructing a diagram in the shortest way.

Working with a table is not limited to simply entering data into it and drawing diagrams. It is difficult to imagine an area where analysis of this data would not be required. Excel includes a powerful analysis tool - the Pivot Table. With its help, you can analyze wide-format tables containing a large amount of unsystematized data, and with just a few clicks of the mouse button you can bring them into a convenient and readable form. Mastering this tool is simplified by the presence of an appropriate wizard program.

There are two main types of objects in Microsoft Excel: book and letter.

Book in Microsoft Excel is a file that is used to process and store data. Each book can consist of several sheets, so you can put a variety of information in one file and establish the necessary connections between them.

Letters serve to organize and analyze data that can be entered and edited simultaneously on multiple sheets, and also perform calculations based on data from multiple sheets. Once created, charts can be placed on an email with the relevant data or on a separate email email.

Sheet names appear on tabs at the bottom of the workbook window. To switch from one letter to another, you need to specify the appropriate label. The name of the active sheet is in bold.

Microsoft Excel has a lot of different functions, for example:

1. Financial, among many special functions that calculate interest on a deposit or loan, depreciation charges, rate of return and a variety of inverse and related quantities.

2. Date and time functions– most of the functions in this category are responsible for converting date and time into different formats. Two special functions TODAY and TDATE insert the current date (the first) and date and time (the second) into the closet, updating them every time the file is called or when any changes are made to the table.

5. Link and arrays. This category contains functions that allow you to access a data array (by column, row, rectangular interval) and obtain a variety of information from it: the numbers of columns and rows it contains, their number, the contents of the array element you need; you can find which compartment of this array contains the required number or text, etc.

6. Text– There are about two dozen teams in this group. With their help, you can count the number of characters in the collar, including spaces (DLST), find out the symbol code (CODE), find out which character is the first (LEFT) and last (RIGHT) in a line of text, place a certain number of characters from another into the active box collars (PSTR), place in the active box all the text from another box in capital letters (UPPER CAPITAL) or capital letters (LOWER), check whether two text boxes match (COICH), find some text (SEARCH, FIND) and replace it with another (REPLACE ).

7. Checking Properties and Values– here are commands with which you can get information about the type of data in the collar (a number is there, text or some other information), about the format, about the current operating environment, about typical errors that have arisen in the formula, etc. p..

8. Working with the database– here you can find commands for statistical accounting (BDDISP - variance for a sample from the database, BDDISP - variance for the general population, DSTANDOTKL - standard deviation for the sample), operations with columns and rows of the base, the number of non-empty (BSCOUNT) or (BSCOUNT) cells, etc. .d.

9. Chart Wizard– built-in EXCEL program, which simplifies working with the main features of the program.

Purpose of MS Excel.

MS Excel is one of the most popular spreadsheet programs today. It is used by scientists, accountants, journalists, etc., with its help they maintain various tables, lists and catalogs, compile financial and statistical reports, calculate the state of a trading enterprise, process the results of a scientific experiment, keep records, and prepare presentation materials. Excel's capabilities are very high. Text processing, database management - the program is so powerful that in many cases it is superior to specialized editor programs or database programs. Such a variety of functions may at first confuse you, rather than force you to use it in practice. But as you gain experience, you begin to appreciate the fact that the limits of Excel's capabilities are difficult to reach.

Over the long history of tabular calculations using personal computers, user requirements for such programs have changed significantly. At first, the main emphasis in a program such as VisiCalc was on counting functions. Today, along with engineering and accounting calculations, the organization and graphical representation of data is becoming increasingly important. In addition, the variety of functions offered by such a calculation and graphic program should not complicate the user's work. Windows programs provide the ideal prerequisites for this. Recently, many have just switched to using Windows as their user environment. As a result, many software companies began to offer a large number of programs for Windows.

Excel provides both ease of handling data and its safety. Excel allows you to quickly complete work that does not require a lot of paper and time, and also involves the involvement of professional accountants and financiers.

This program will be able to calculate the amounts in the rows and columns of tables, calculate the arithmetic mean, bank interest or dispersion; here, in general, you can use many standard functions: financial, mathematical, logical, statistical.

Excel has many more advantages. This is a very flexible system that “grows” with the user’s needs, changes its appearance and adapts to you. The basis of Excel is the field of cells and the menu at the top of the screen. In addition, up to 10 toolbars with buttons and other controls can be located on the screen. It is possible not only to use standard toolbars, but also to create your own.

Conclusion.

In order to know Excel you need to work in it. Using this program, you will certainly discover more and more new features and properties. Explore and experiment. If you don't like the results, try again. Almost everything you do in Excel can be undone, so you won't lose anything more than a few minutes of your time by experimenting.

It can greatly facilitate the user’s work with tables and numerical expressions by automating it. This can be achieved using the application's tools and its various functions. Let's look at the most useful of them.

Due to the wide functional purpose of the program, the user is not always aware of the capabilities that simplify interaction with many Excel tools. Later in the article we will talk about the 10 best functions that can be useful for various purposes, and also provide links to detailed lessons on working with each of them.

VLOOKUP function

One of the most requested features in Microsoft Excel is "VLOOKUP". By using it, you can drag and drop values ​​from one or more tables into another. In this case, the search is performed only in the first column of the table, thus, when the data in the source table is changed, data is automatically generated in the derived table, in which individual calculations can be performed. For example, information from a table containing price lists for goods can be used to calculate indicators in the table about the volume of purchases in monetary terms.

A VLOOKUP is started by inserting a statement "VPR" from "Function Masters" in the cell where the data should be displayed.

In the window that appears, after running this function, you need to specify the address of the cell or range of cells from which the data will be pulled.

Pivot tables

Another important function of Excel is the creation of pivot tables, which allows you to group data from other tables according to various criteria, perform various calculations with them (summation, multiplication, division, etc.), and the results are displayed in a separate table. At the same time, there are very wide possibilities for customizing the fields of the pivot table.

It is created on the tab "Insert" by pressing the button, which is called - "Pivot table".

Creating Charts

To visually display data placed in a table, it is convenient to use charts. They are often used for creating presentations, writing scientific papers, for research purposes, etc. Excel provides a wide range of tools for creating various types of charts.

To create a chart, you need to select a set of cells containing the data that you want to visually display. Then, while on the tab "Insert", select on the ribbon the type of diagram that you consider most suitable for achieving your goals.

More precise setup of charts, including setting its name and axes names, is done in a group of tabs "Working with diagrams".

One type of chart is graphs. The principle of their construction is the same as that of other types of diagrams.

Formulas in Excel

To work with numerical data, the program allows you to use special formulas. With their help, you can perform various arithmetic operations with data in tables: addition, subtraction, multiplication, division, exponentiation, root extraction, etc. To apply the formula, you need to put the sign in the cell where you plan to display the result «=» . After this, the formula itself is entered, which can consist of mathematical symbols, numbers and cell addresses. To indicate the address of the cell from which the data for calculation is taken, just click on it with the mouse, and its coordinates will appear in the cell for displaying the result.

Excel is also convenient to use as a regular calculator. To do this, in the formula bar or in any cell, simply enter mathematical expressions after the sign «=» .

"IF" function

One of the most popular functions used in Excel is "IF". It makes it possible to specify in a cell the output of one result if a specific condition is met and another result if it is not met. Its syntax is as follows: IF(boolean expression; [result if true]; [result if false]) .

Operators "AND", "OR" and nested function "IF" matches several conditions or one of several conditions.

Macros

Using macros, the program records the execution of certain actions, and then they are played back automatically. This significantly saves time on performing a large amount of the same type of work. Macros are recorded by enabling the capture of your actions in the program through the corresponding button on the ribbon.

Macros can also be recorded using the Visual Basic markup language in a special editor.

Conditional Formatting

To highlight specific data, a table uses conditional formatting to set rules for highlighting cells. The conditional formatting itself can be done in the form of a histogram, color scale, or set of icons. You can access it through the tab "Home" highlighting the range of cells you are going to format. Next in the tools group "Styles" click the button named "Conditional Formatting". After this, all you have to do is select the formatting option that you consider most suitable.

The formatting will be completed.

Smart table

Not all users know that a table simply drawn with a pencil or borders is perceived by Excel as a simple area of ​​cells. You can force the program to see this data set as a table through reformatting. This is done simply: first, select the desired range with data, and then, being on the tab "Home", click on the button "Format as table". A list will appear with different design style options, where you can select the one that suits you.

The table is also created by clicking the button "Table", which is located on the tab "Insert", having previously selected a certain area of ​​the sheet with data.

The editor will treat the selected set of cells as a table. As a result, for example, if you enter some data into the cells located at the borders of the table, they will be automatically included in it. In addition to this, when scrolling down, the header will be constantly within the field of view.

Parameter selection

Using the parameter selection function, you can select the initial data, guided by the result you want. Go to the tab "Data" and press the button "What If Analysis" located in the toolbox "Working with data". In the list that appears, select the item “Selection of parameter...”.

The parameter selection window will open. In the field "Set in cell" you must provide a reference to the cell that contains the formula you want. In the field "Meaning" the end result you want must be specified. In the field "Changing cell values" insert the coordinates of the cell with the value being adjusted.

INDEX function

Features the function provides "INDEX", somewhat close to the capabilities of the function "VPR". It also allows you to search for data in an array of values ​​and return it to a specified cell. The syntax is as follows: INDEX(cell_range, row_number, column_number) .

This is not a complete list of all the functions that are available in Microsoft Excel. We focused only on the most popular and most important of them.

Microsoft Excel 2008
Type
Developer
operating system
Latest version
License
Website

Microsoft Excel(also sometimes called Microsoft Office Excel listen)) is a spreadsheet program created by Microsoft Corporation for Microsoft Windows, Windows NT and Mac OS. It provides economic-statistical capabilities, graphical tools, and, with the exception of Excel 2008 on Mac OS X, the VBA macro programming language ( Visual Basic for Application). Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular applications in the world.

Story

VBA functionality made Excel an easy target for macro viruses. And this was a serious problem until antivirus products learned to detect them. Microsoft, belatedly taking measures to reduce the risk, added the ability to select a security mode:

• completely disable macros
• enable macros when opening document
• trust all macros signed using trusted certificates.

Excel versions 5.0 through 9.0 contain various Easter eggs, although starting with version 10 Microsoft began taking steps to eliminate them.

Versions

Windows and Windows NT versions

Excel 97 (8.0) on Windows XP

Microsoft Office Excel 2003

• 1988 - Excel 2.0 for Windows
• 1990 - Excel 3.0
• 1992 - Excel 4.0
• 1993 - Excel 5.0 (Office 4.2 and 4.3, there is also a 32-bit version for Windows NT only)
• 1995 - Excel 7 for Windows 95 (included in Microsoft Office 95)
• 1997 - Excel 97 (included in Microsoft Office 97)
• 1999 - Excel 2000 (9) - Microsoft Office 2000
• 2001 - Excel 2002 (10) - Microsoft Office XP
• 2003 - Excel 2003 (11) - Microsoft Office 2003
• 2007 - Excel 2007 (12) - Microsoft Office 2007
• 2010 - Excel 2010 (14) - Microsoft Office 2010
• Comment: Version 1.0 was not used to avoid confusion with Apple products
• Comment: Excel 6.0 does not exist because the Windows 95 version shipped with Word 7. All Office 95 and Office 4.x programs used OLE 2.0 (automatic transfer of data between different programs), and Excel 7 should have shown that it complies with Word 7.
• Comment: Version 13.0 has been skipped. Excel 2010 has internal version 14.0.

Macintosh versions

• 1985 - Excel 1.0
• 1988 - Excel 1.5
• 1989 - Excel 2.2
• 1990 - Excel 3.0
• 1992 - Excel 4.0
• 1993 - Excel 5.0
• 1998 - Excel 8.0 (Office "98)
• 2000 - Excel 9.0 (Office 2001)
• 2001 - Excel 10.0 (Office v. X)
• 2004 - Excel 11.0 (Office 2004)
• 2008 - Excel 12.0 (Office 2008)
• 2011 - Excel 14.0 (Office 2011)

OS/2 versions

• 1989 - Excel 2.2
• 1990 - Excel 2.3
• 1991 - Excel 3.0

Excel versions prior to 7.0 were limited in data storage capabilities, holding up to 16,384 (2 14) rows. Versions 8.0-11.0 could handle 65,536 (2 16) rows and 256 columns (2 8). Versions 12.0 and 14.0 handle up to 1,048,576 (2 20) rows and 16,384 (2 14) columns.

File formats

Microsoft Excel, up to and including version 2003, used its own binary file format (BIFF) as the main one. Excel 2007 uses Microsoft Office Open XML as its primary format.

Although Excel 2007 supports and aims to use newer XML formats as its primary format, it is still compatible with traditional binary formats. Additionally, most versions of Microsoft Excel can read CSV, DBF, SYLK, DIF, and other formats.

Office Open XML

Microsoft Excel 2007, along with other Microsoft Office 2007 products, introduces many new file formats. They are part of the Office Open XML (OOXML) specification.

Exporting and moving tables

The API allows you to open Excel spreadsheets in a number of other applications. This includes opening Excel documents on web pages using ActiveX or plugins such as Adobe Flash Player. The Apache POI project provides Java libraries for reading and writing Excel spreadsheets. There have also been attempts to copy Excel tables into web applications using comma separated values ​​(CSV).

Programming

A valuable feature of Excel is the ability to write code using Visual Basic for Applications (VBA). This code is written using an editor separate from the tables. The spreadsheet is managed through an object-oriented code and data model. With this code, the data from the input tables will be instantly processed and displayed in tables and charts (graphs). The table becomes the interface of the code, making it easy to change it and manage calculations.

MS Excel password protection

Microsoft Excel security provides several types of different passwords:

All passwords, except the password for opening a document, can be deleted instantly, regardless of the version of MS Excel in which the document was created. These types of passwords are primarily used for collaborative work on a document. When using them, the document is not encrypted, and a hash of the set password is stored in the document. Although when using a password to protect a workbook, the document is encrypted with a known password "VelvetSweatshop", this does not add additional security to the document. The only type of password that can prevent an attacker from gaining access to a document is "password to open", but the cryptographic strength of such protection strongly depends on the version of MS Excel in which the document was created.

In MS Excel version 95 and earlier, "password to open" is converted into a 16-bit key, which is cracked instantly. In MS Excel 97/2000, the password was converted into a 40-bit key, which can also be cracked very quickly on modern equipment. In addition, password cracking programs can try hundreds of thousands of passwords per second, which allows you not only to decrypt a document, but also to find the original password. In MS Excel 2003 / XP the situation has improved a little - the user can select almost any encryption algorithm available in the system (CryptoServiceProvider). But by default, the same MS Excel 97/2000 protection remains. For users who are accustomed to trusting the default behavior of programs, this means a lack of strong protection for their documents.

The situation changed dramatically in MS Excel 2007 - the modern AES algorithm with a 128-bit key was used for encryption, and to obtain the key, 50,000 times the SHA1 hash function was used, which reduced the search speed to hundreds of passwords per second. In MS Excel, the default security strength was increased by another 2 times, thanks to the use of a 100,000-fold SHA1 password-to-key conversion. Conclusion: at the moment, strong protection is provided only by documents saved in the Office 2007\2010 format with strong protection installed on them. "password to open".

Criticism

Common criticisms of spreadsheet processors apply to Excel as well. Specific areas of criticism in Excel are accuracy, date issues, and Excel 2007 display errors.

Accuracy

Because Excel operates based on floating point calculations, Excel's statistical accuracy has been criticized. Excel proponents argue in response that these errors only appear under special conditions of specially selected source data, which affect a relatively small number of users, and are unlikely to occur in practice. For versions 97, 2000, 2002, errors were recorded when executing the MOD (division with remainder) function with certain arguments, where the function returns the #NUM! error instead of the result.

Date problems

In versions of Excel up to and including 2007, 1900 was incorrectly considered a leap year. The bug originated in Lotus 1-2-3 and was intentionally introduced into Excel for compatibility and maintained for backward compatibility.

Excel Display Errors

Microsoft Excel 2007 screenshot showing display error 65535

On September 22, 2007, it was reported that Excel 2007 would produce incorrect results in certain situations. Specifically, for those pairs of numbers whose product is 65535 (for example, 850 and 77.1), Excel will display 100000 as the total. This happens for about 14.5% of these pairs. Additionally, if you add one to the result, Excel will output a total of 100001. However, if you subtract one from the total, the display will show the correct result of 65534. (Also, if you multiply or divide the total by 2, it will display 131070 and 32767.5, respectively.)

Microsoft reported on the Microsoft Excel Blog that an issue exists in the display of six specific floating point values ​​between 65534.99999999995 and 65535 and six values ​​between 65535.99999999995 and 65536 (not including bounds). Any calculations that result in one of the twelve values ​​will not display correctly. The actual data stored and transmitted to other cells is correct, only the display of the value is incorrect. The error was introduced in Excel 2007 and does not exist in previous versions. On October 9, 2007, Microsoft released a patch that corrected the problem. It was also included in the Service Pack 1 fixes.

Notes

Links

• Working with Excel Files Using the .NET Framework
• List of MVP sites (English)

Microsoft Excel is convenient for creating tables and making calculations. A workspace is a set of cells that can be filled with data. Subsequently – format, use for building graphs, charts, summary reports.

Working with Excel tables for novice users may seem difficult at first glance. It differs significantly from the principles of creating tables in Word. But we'll start small: by creating and formatting a table. And at the end of the article, you will already understand that you cannot imagine a better tool for creating tables than Excel.

How to Create a Table in Excel for Dummies

Working with tables in Excel for dummies is not rushed. You can create a table in different ways, and for specific purposes, each method has its own advantages. Therefore, first let’s visually assess the situation.

Take a close look at the spreadsheet worksheet:

This is a set of cells in columns and rows. Essentially a table. Columns are indicated in Latin letters. Lines are numbers. If we print this sheet, we will get a blank page. Without any boundaries.

First let's learn how to work with cells, rows and columns.

How to select a column and row

To select the entire column, click on its name (Latin letter) with the left mouse button.

To select a line, use the line name (by number).

To select several columns or rows, left-click on the name, hold and drag.

To select a column using hot keys, place the cursor in any cell of the desired column - press Ctrl + spacebar. To select a line – Shift + spacebar.

How to change cell borders

If the information does not fit when filling out the table, you need to change the cell borders:

To change the width of columns and height of rows at once in a certain range, select an area, increase 1 column/row (move manually) - the size of all selected columns and rows will automatically change.

Note. To return to the previous size, you can click the “Cancel” button or the hotkey combination CTRL+Z. But it works when you do it right away. Later it won't help.

To return the lines to their original boundaries, open the tool menu: “Home” - “Format” and select “Auto-fit line height”

This method is not relevant for columns. Click “Format” - “Default Width”. Let's remember this number. Select any cell in the column whose borders need to be “returned”. Again “Format” - “Column Width” - enter the indicator specified by the program (usually 8.43 - the number of characters in the Calibri font with a size of 11 points). OK.

How to insert a column or row

Select the column/row to the right/below the place where you want to insert the new range. That is, the column will appear to the left of the selected cell. And the line is higher.

Right-click and select “Insert” from the drop-down menu (or press the hotkey combination CTRL+SHIFT+"=").

Mark the “column” and click OK.

Advice. To quickly insert a column, select the column in the desired location and press CTRL+SHIFT+"=".

All these skills will come in handy when creating a table in Excel. We will have to expand the boundaries, add rows/columns as we work.

Step-by-step creation of a table with formulas

Column and row borders will now be visible when printing.

Using the Font menu, you can format Excel table data as you would in Word.

Change, for example, the font size, make the header “bold”. You can center the text, assign hyphens, etc.

How to create a table in Excel: step-by-step instructions

The simplest way to create tables is already known. But Excel has a more convenient option (in terms of subsequent formatting and working with data).

Let's make a “smart” (dynamic) table:

Note. You can take a different path - first select a range of cells, and then click the “Table” button.

Now enter the necessary data into the finished frame. If you need an additional column, place the cursor in the cell designated for the name. Enter the name and press ENTER. The range will automatically expand.

If you need to increase the number of lines, hook it in the lower right corner to the autofill marker and drag it down.

How to work with a table in Excel

With the release of new versions of the program, working with tables in Excel has become more interesting and dynamic. When a smart table is formed on a sheet, the “Working with Tables” - “Design” tool becomes available.

Here we can give the table a name and change its size.

Various styles are available, the ability to convert the table into a regular range or a summary report.

Features of dynamic MS Excel spreadsheets huge. Let's start with basic data entry and autofill skills:

If we click on the arrow to the right of each header subheading, we will get access to additional tools for working with table data.

Sometimes the user has to work with huge tables. To see the results, you need to scroll through more than one thousand lines. Deleting rows is not an option (the data will be needed later). But you can hide it. For this purpose, use numerical filters (picture above). Uncheck the boxes next to the values ​​that should be hidden.