What is microsoft excel and its functions. Microsoft Excel program. Brief description of the features. Creating and opening books

Microsoft Excel(Mac OS X)

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 Microsoft began taking steps to eliminate them starting with version 10.

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 Windows version 95 came with Word 7. All Office programs 95 and Office 4.x used OLE 2.0 (automatic data transfer between various 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. In addition, most Microsoft versions 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.

New Excel 2007 formats
Format Extension Notes
Excel workbook .xlsx Standard Excel 2007 workbook format. It is actually a compressed ZIP archive of a catalog of XML documents. It is a replacement for the former .xls binary format, although it does not support macros for security reasons.
Excel workbook with macros .xlsm The same Excel workbook, but with macro support.
Binary Excel workbook .xlsb The same Excel workbook with macros, but storing information in binary format, opening documents faster than XML. It is especially often used for very large documents with tens of thousands of rows and/or hundreds of columns.
Excel template with macros .xltm Template created as a basis for workbooks, macro support included. Replaces the old .xlt format.
Excel add-in .xlam Excel add-in aimed at adding additional functionality and tools.

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

Valuable Excel capability is the ability to write code based on 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 the document stores a hash of set password. 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 already converted into a 40-bit key, which on modern equipment can also be cracked very quickly. 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 began to be used for encryption, and to obtain the key, a 50,000-fold use of 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: on this moment strong protection is provided only by documents saved in Office format 2007\2010 with resistant 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 say in response that these errors only appear in special conditions specially selected source data that affects a relatively small number of users and is unlikely to be encountered 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 Excel versions up to and including 2007, 1900 was erroneously 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 appeared 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)

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 only need basic skills to work.

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 package office programs(including Microsoft Excel) is not installed on your computer.

“Microsoft Excel (also sometimes called Microsoft Office Excel) is a spreadsheet program created by Microsoft 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 Application). Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular applications in the world" Source: Wikipedia.org

Microsoft Excel capabilities?

The areas and possibilities for using Excel are varied:

As can be seen from all of the above, the main focus of Excel is working with financial and analytical data, so ownership of this application is in demand in many types of activities related to calculations, reports, forecasting, etc. And an employee with in-depth knowledge is a gift

That's all for now! In the next lesson we will get acquainted with the MS Excel 2003 interface.

For those who want to delve into the history of the development of MS Excel, you can take a look at.

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 10 best features, which can be useful for various purposes, and we will 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 diagrams.

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 the 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 various 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 field "Set in cell" you must provide a reference to the cell that contains the formula you want. In field "Meaning" the end result you want must be specified. In 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.

The fact that the ability to work on a computer today is necessary for everyone is beyond doubt even among skeptics.

The book you are holding in your hands will be a true friend and assistant for those who want to independently and quickly master the wisdom of working on personal computer. Written in simple and understandable language, it is accessible and easy even for beginners. A large number of specific examples and visual illustrations contribute to the quick and easy assimilation of the proposed material.

Its sequential presentation, as well as detailed step by step description key operations and procedures turn the study of this book into an exciting process, the result of which will be the ability to communicate on a first-name basis with any modern computer.

The description is based on an example Windows systems XP Professional.

Book:

6.2. Terms and concepts used in Excel

The Excel spreadsheet editor is a more complex software product than text editor Word. To a certain extent, this is due to the fact that Excel provides calculation functions, moreover, it is designed specifically for this. Accordingly, there is a certain terminology that every user of the program should be familiar with. In this section we will provide an interpretation of the basic terms and concepts necessary when studying and subsequently working with Excel.

Autoshape– a ready-to-use figure of a given shape that can be added to a worksheet or diagram. Excel has a built-in set of AutoShapes.

Diagram– graphic method representation of numeric values. Excel program supports working with different charts: histograms, pie charts, bubble charts, graphs, etc.

Name– an identifier that provides the ability to refer to an object (cell, range, formula, etc.).

Constant– constant (non-calculated) value. A constant can be, for example, a fixed number or some text.

Context menu– a menu containing a list of commands that are designed to work with a specific object. To call the context menu, you need to right-click on the object or press a key combination Shift+F10.

Macro– a program that is written in Excel’s built-in language Visual programming Basic for Application (VBA) and designed to expand its functionality. Macros are accessed using the command Service? Macro.

Array– a set of cells or values ​​used as a whole. In other words, an array is a group of elements of the same type, united under one name.

Settings– changing the current parameters of the program regular means. Basic operations for setting up Excel are performed in a window that opens using the main menu command Service? Options.

Operand– calculation element (constant, function or reference).

Operator– a sign or symbol that determines the type of calculation in a formula over operands. The types of operators that Excel uses are mathematical operators, text operators, comparison operators, and reference operators.

Toolbar– a panel consisting of buttons and other controls used to activate commands and go to different modes work. Enabling/disabling toolbars is done using the corresponding submenu items View? Toolbars.

Seal– output the contents of the workbook (in whole or in part) to paper using a printer. The following objects can be printed: workbook, multiple workbooks, worksheet, multiple worksheets, range of cells in a worksheet, range of cells in multiple worksheets, graphic objects, diagrams.

User interface– a means of user interaction with the program. The interface includes menus, toolbars, etc. Excel uses the standard user interface used in MS Office applications.

Note– auxiliary information of an arbitrary nature, related to a specific cell and stored regardless of the contents of this cell. To add a note to a cell, you need to select it with the cursor and execute the context menu command Add a note, then enter the required text from the keyboard.

Workbook– a file that is created, edited and saved using Excel. The main structural unit of the workbook is worksheet(see below).

Worksheet– the main element of a workbook, intended for entering, editing and storing data, as well as for performing calculations. By default, a workbook includes three worksheets. The basic structural unit of a worksheet is cell(see below). The worksheet consists of 16777216 cells, which form a grid of 65536 rows and 256 columns.

Formatting– changing the design of the cell (its “ appearance» – borders, fill, etc.) or changing the presentation of the data contained in the cell. A cell's formatting options are independent of its contents, and vice versa. But keep in mind that after changing the formatting, the displayed value in a cell may not match its actual value (the most common example is rounding: the cell stores the value 0,33 , but due to formatting options, the value may appear on screen 0,3 ).

Formula– a special Excel tool designed for calculations, calculations and data analysis. A formula can include a constant, an operator, a reference, a cell (range) name, and function. There are three types of operators:

Arithmetic operator– an operator designed to perform arithmetic operations and output as a result numeric value;

Comparison operator– this operator used to compare data and produces a boolean value as a result TRUE or LIE;

Text operator– operator used to combine data.