What is Excel and how to use it correctly. Practical application of MS Excel functions What is Microsoft Office Excel

The Purpose of Microsoft Excel Spreadsheets

Microsoft Excel spreadsheets are designed to provide a user-friendly experience with data tables that contain predominantly numeric values.

Using spreadsheets, you can get accurate results without doing manual calculations, and built-in functions allow you to quickly solve complex problems.

Rectangular tables often store ordered data and are used for visual representation of both numbers and textual information in various branches of human professional activity. Tables can store both initial (primary) data and the results of performing various kinds of operations on them. Before the advent of computers, tables were used that looked like graphed sheets or file cabinets.

Computers have made it much easier to represent and process data. Data processing programs are called spreadsheets or spreadsheets because of their resemblance to a regular table drawn on paper.

Remark 1

Today, a huge number of programs are known that provide storage and processing of tabular data, including Lotus 1-2-3, Quattro Pro, Calc, and others. The most widely used spreadsheet for personal computers is Microsoft Excel.

MS Excel is used to solve planning, economic, financial, technical, economic and engineering tasks, to perform accounting and banking operations, in statistical processing of information, data analysis and project forecasting, to fill in tax returns, etc.

Also, the Excel spreadsheet toolkit allows you to process statistical information and present data in the form of graphs and charts, which can also be used in everyday life for personal accounting and analysis of spending your own money.

Thus, the main purpose of spreadsheets is:

• data entry and editing;
• table formatting;
• automation of calculations;
• presentation of results in the form of diagrams and graphs;
• modeling the processes of influence of some parameters on others, etc.

Spreadsheets can be easily integrated into a document created, for example, in the word processor Word or another application that is included in the Microsoft Office suite. The tight integration of the applications that are included in the package increases the efficiency of the user's work, makes it possible to create professionally designed documents and use the capabilities of the local and global information networks (World Wide Web) for collective work and data publication.

The main feature of MS Excel is the ability to apply formulas to create relationships between the values ​​of different cells, and the calculation of the formulas occurs automatically. When you change the value of any cell that is used in a formula, the cell with the formula is automatically recalculated.

The main features of spreadsheets include:

• automation of all final calculations;
• performing similar calculations on large data sets;
• the ability to solve problems by selecting values ​​with different parameters;
• the possibility of processing the results of the experiment;
• the ability to tabulate functions and formulas;
• preparation of spreadsheets;
• search for optimal values ​​for the selected parameters;
• the ability to build graphs and charts according to the entered data.

Advanced features of MS Excel 2013

Remark 2

When starting a spreadsheet, the user sees a full-fledged web resource in front of him, and not a regular program. The start page contains various templates that can be downloaded from the Internet. This version of the program is also convenient to use on smartphones and tablets.

Let's take a closer look at the changes made:

• Saving to the cloud data server is done using a Live ID entry. Saving documents on a cloud service makes it possible to get all the necessary files using any equipment that has Internet access. After saving the document in the cloud service, the user can invite other users to edit or view it. In parallel, the user can create a special web link that will direct other users to the data document and set the set of actions allowed to be performed. Moreover, the big advantage of using a cloud service is that it does not take into account the version of Excel installed on the user's computer.
• With such a direct connection of some documents with the Internet, the issue of information security is especially important, which the developers have completely solved. In addition, in Excel 2013, the Check Out function is responsible for creating new versions of the document when it is edited.
• The ability to set rights and restrictions for other users who have access to a particular file. For example, you can prevent the page from being shown or edited.
• The ability to search for pictures on the Internet directly from the program and directly insert them directly into the table. The search is carried out on free collections of pictures, on Flickr and SkyDrive.
• A wider list of data sources, which allows you to increase efficiency when working with databases; the ability to import data.

Applications of spreadsheets

The Excel spreadsheet is widely used for compiling a variety of forms, keeping records of orders, processing statements, planning production, accounting for personnel and production turnover. Excel also contains powerful mathematical and engineering functions that allow you to solve many problems in the field of natural and technical sciences.

Spreadsheets are widely used to automate calculations. In tabular form, it is convenient to perform a number of tasks, such as accounting, mathematical problems, etc. The ability to use mathematical formulas in spreadsheets makes it possible to represent the relationship between different parameters. With the help of spreadsheets, it became possible to solve many computational problems that were previously solved exclusively with the help of programming.

Thus, the areas of application of spreadsheets include:

• accounting and banking;
• planning and allocation of resources;
• design estimates;
• engineering and technical calculations;
• processing of large amounts of information;
• study of dynamic processes;
• sphere of business and entrepreneurship.

Remark 3

One of the important functional extensions of the MS Excel spreadsheet processor is the built-in Visual Basic for Applications (VBA) programming environment, which is designed to solve applied problems in MS Office. With VBA, Microsoft not only expanded the capabilities of the Excel macro language, but also introduced a new level of application programming, since VBA allows you to create full-fledged application packages that go far beyond spreadsheet processing in their functions.

Hi all. This is the first article in a series about Microsoft Excel. Today you will learn:

• What is Microsoft Excel
• What is it for
• What does his workspace look like?

In the next article, we will discuss the working environment of the program a little more. But we will already be in the third post of this cycle, so the most interesting thing is very close! In the future, we will focus on practice, since it is of interest to everyone.

To master this and subsequent articles in the series, you will need a computer with Microsoft Office, including Excel. If you don't have this program yet, you can buy it, for example, here.

What is Excel, why use it

Microsoft Excel is a program with a tabular structure that allows you to organize tables of data, systematize, process them, build graphs and charts, perform analytical tasks, and much more. Of course, this is not the whole list of possibilities, which you will soon see by studying the course materials. The program is able to do many useful operations for you, which is why it has become a worldwide hit in its industry.

Excel workspace

The Excel workspace is called a workbook, which consists of worksheets. That is, one or more tables, called Sheets, can be located in one file-book. Each sheet consists of many cells that form a data table. Rows are numbered in order from 1 to 1,048,576. Columns are named from A to XFD. Cells and coordinates in Excel In fact, these cells can store a huge amount of information, much more than your computer can process. Each cell has its own coordinates. For example, a cell not at the intersection of the 3rd row and 2nd column has coordinates B3 (see figure). The cell coordinates are always highlighted on the sheet with color, look at the picture how the number of the third line and the letter of the second column look - they are darkened.

By the way, you can place the data in any order on the sheet, the program does not limit your freedom of action. This means that you can easily create various reports, forms, layouts, and choose the best place for.

Now let's take a look at the Excel window as a whole and understand the purpose of some of its elements:

Page Title displays the title of the current working document

View selection- switching between

Ribbon– an interface element with buttons for commands and settings. The tape is divided into logical blocks tabs. For example, the "View" tab helps to customize the appearance of the working document, "Formulas" - tools for performing calculations, etc.

Display scale- the name speaks for itself. We choose the ratio between the actual size of the sheet and its representation on the screen.

Quick Access Toolbar– zone for placing elements that are used most often and are not on the tape

Name field displays the coordinates of the selected cell or the name of the selected element

Scroll bars- allow you to scroll the sheet horizontally and vertically

Status bar displays some intermediate calculations, informs about the inclusion of "Num Lock", "Caps Lock", "Scroll Lock"

Formula bar is used to enter and display the formula in the active cell. If there is a formula in this line, in the cell itself you will see the result of the calculation or a message about .

Table Cursor - displays the cell that is currently active to change content

Row numbers and column names- the scale by which the cell address is determined. In the diagram, you can see that the cell is active L17, 17 scale string and element L highlighted in dark. You can see the same coordinates in the Name field.

Sheet Tabs help to switch between all sheets of the workbook (and there can be a lot of them, by the way)

Excel Workspace This concludes our first lesson. We examined the purpose of the Excel program and the main (not all) elements of its worksheet. In the next lesson, we will look at . Thank you for reading this article to the end, keep it up! If you have any questions - write in the comments, I will try to answer everything. 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 for Microsoft Windows, Windows NT, and Mac OS. It provides economic and statistical calculation capabilities, graphical tools, and, with the exception of Excel 2008 under 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

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

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

Versions of Excel from 5.0 to 9.0 contain various Easter eggs, although starting from version 10 Microsoft began to take steps to eliminate them.

Versions

Versions for Windows and Windows NT

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 (automated data transfer between different programs), and Excel 7 should have shown that it matches Word 7.
• Comment: Version 13.0 has been skipped. Excel 2010 has internal version 14.0.

Versions for Macintosh

• 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)

Versions for OS/2

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

Versions of Excel prior to 7.0 were limited in data storage capacity, holding up to 16,384 (214) 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 the new XML formats as core, it is still compatible with the traditional binary formats. In addition, 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.

New formats in Excel 2007

Format	Extension	Notes
Excel workbook	.xlsx	The 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. Especially often used for very large documents with tens of thousands of lines and/or hundreds of columns.
Macro Excel Template	.xltm	Template created as the basis for workbooks, macro support included. Replaces the old .xlt format.
Excel add-in	.xlam	An 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 in web pages using ActiveX or plugins such as Adobe Flash Player. The Apache POI project introduces Java libraries for reading and writing Excel spreadsheets. There have also been attempts to copy Excel spreadsheets to web applications using comma separated values ​​(CSV).

Programming

A valuable feature of Excel is the ability to write code based on Visual Basic for Applications (VBA). This code is written using a separate editor from tables. The spreadsheet is managed through an object-oriented code and data model. With this code, the data of 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 modify it and manage calculations.

Password protection MS Excel

Microsoft Excel protection provides several types of different passwords:

All passwords, except for the password to open the document, can be removed instantly, regardless of the version of MS Excel in which the document was created. These types of passwords primarily serve to collaborate on a document. When using them, the document is not encrypted, and the hash of the set password is stored in the document. Although using a password to protect a workbook encrypts the document with a known password "VelvetSweatshop", it 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 can also be cracked very quickly on modern equipment. In addition, password cracking programs can sort out hundreds of thousands of passwords per second, which allows not only to decrypt a document, but also to find the original password. In MS Excel 2003/XP the situation has improved a bit - the user can choose practically any encryption algorithm available in the system (CryptoServiceProvider). But by default, the same protection of MS Excel 97/2000 remained. For users who are accustomed to trusting the behavior of programs by default, this means that there is no 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 50,000 times the use of the SHA1 hash function was used to obtain the key, which reduced the brute force to hundreds of passwords per second. In MS Excel, the default protection strength has been increased by a factor of 2, thanks to the use of already 100,000 times SHA1 password-to-key conversion. Conclusion: at the moment, only documents saved in the Office 2007 \ 2010 format with persistent protection installed on them are provided with strong protection. "password to open".

Criticism

The general criticism of spreadsheets applies to Excel as well. Specific areas of criticism in Excel are precision, date issues, and display errors in Excel 2007.

Accuracy

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

Date issues

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

Excel display errors

Microsoft Excel 2007 screenshot showing display error 65535

On September 22, 2007, it was reported that Excel 2007 would show incorrect results in certain situations. In particular, for those pairs of numbers whose product is 65535 (for example, 850 and 77.1), Excel will display 100000 as the total. This happens with approximately 14.5% of such pairs. Also, if you add one to the result, Excel will display the total as 100001. However, if you subtract one from the total, the display will show the correct result, 65534. (Also, if the total is multiplied or divided by 2, it will display 131070 and 32767.5, respectively.)

Microsoft reported in the Microsoft Excel blog that a problem 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 calculation that results in one of the twelve values ​​will not display correctly. The actual data stored and passed 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 to fix the problem. It was also included in Service Pack 1 fixes.

Notes

Links

• Working with Excel Files with the .NET Framework
• List of MVP Sites

Microsoft Office
Client Applications	Accounting Access Lync Entourage excel Groove InfoPath InterConnect MapPoint OneNote Outlook PowerPoint Project Publisher SharePoint Designer Visio Word
Server applications	Communications Forms Groove PerformancePoint Project Project Portfolio SharePoint
Legacy Applications	Binder · Data Analyzer · front page · liquid motion · Mail · office assistants · PhotoDraw · photo editor · Schedule+ · Vizact · Web Components
Online Services	Office Online Office 365 Office Live Meeting SkyDrive
Versions for Windows	3.0 4.0 4.3 95 97 2000 XP 2003 ·
Versions for Mac OS	98 2001 X 2004 2008 2011
Technologies	Object Linking and Embedding Office Open XML Visual Studio Tools for Office Visual Basic for Applications
Category

Microsoft Excel is a program for performing calculations and managing so-called spreadsheets.

A spreadsheet is the main tool used for processing and analyzing digital information by means of computer technology. Although spreadsheets are primarily related to numerical or financial transactions, they can also be used for a variety of data analysis tasks, providing the user with great power to automate data processing.

Excel allows you to perform complex calculations that can use data located in different areas of a spreadsheet and related to each other by a certain relationship. To perform such calculations in Excel, it is possible to enter various formulas in the table cells. Excel performs the calculation and displays the result in the formula cell. Available formulas range from simple addition and subtraction to financial and statistical calculations.

An important feature of using a spreadsheet is the automatic recalculation of results when changing cell values. For example, you can use Excel to perform financial calculations, keep track of your organization's workforce, and so on. Excel can also build and update graphs based on the numbers you enter.

The file that Excel works with is called book . The book, as a rule, consists of several worksheets, which may contain tables, texts, diagrams, figures.

The book is a good organizational tool. For example, you can collect all documents in one book ( worksheets ) related to a specific project (task), or all documents maintained by one executor. The basis of the worksheet (Fig. 1) is a grid of rows and columns. Cell formed by the intersection of a row and a column. The cell(s) selected with the mouse is called active (in Fig. 1, the active cell is marked with a frame).

A line in a worksheet is identified by a name (number) that appears on the left side of the worksheet.

A column in a worksheet is also identified by a name (in Latin letters) that appears at the top of the worksheet.

An Excel worksheet can contain up to 65,536 rows and 256 columns. A cell - the main element of a table - has its own unique address, consisting of a column number and a row, for example E4 .

Each cell contains one piece of information, be it a numeric value, text, or a formula.

When you open a previously created file in the Excel window, a book appears with the entered data.

Workbook in Excel, it is a file that stores and analyzes data. A workbook file consists of several worksheets, which may contain tables, texts, charts, or pictures. Each worksheet is identified by a name that appears on the sheet's tab.

Consider the specific elements of the Excel window (Fig. 2).

Rice. 2. Excel window

Status bar contains information about the active document, the selected menu command, keyboard mode indicators. In it, the user receives messages on how to execute the started command to the end and view the intermediate results of some calculations.

Formula bar shows the formula (if present in the cell) or the data contained in the active cell. In the formula bar, you can enter and edit text, numbers, and formulas.

Excel is an indispensable universal analytical tool for dynamically solving computational problems. Let's look at concrete examples of how Excel helps to improve the quality of our modern life and makes us more successful.

7 examples why you need Excel

What is Excel for? Let's give real life examples of using formulas, functions and graphs in solving important problems. These 7 useful examples of using formulas are provided as solutions for specific problems.

ROI analysis

The function =BS() will help to calculate the value of the future value of an investment object.

Task 1: Calculate the amount accumulated in the account after 5 years when investing in an investment object. Assuming $6,000 is paid at the beginning of each year and the annual interest rate is 5.6%. Given that at the beginning of the investment, the amount on the balance of assets has already accumulated $ 5,000.

Solving problem 1 in Excel using the BS function:

Considering loan repayment options

This task is to test the loan repayment options with the proposed monthly payments by the bank. Moreover, it is possible to visually trace whether it is realistic to close a loan at all under such conditions of lending and repayment.

Task 2: The Can Be Redeemed column is a possible calculation of redemption using IF, which should consider the type of value returned by the NPER function. In the Monthly Installment Check column, use the PMT function to calculate the payment amount for the number of payments returned by the NPER function, rounded to the nearest integer. In this way, we can check or get the correct analysis for other input parameters of the credit conditions. For example, when we already know in advance the terms of repayment of the loan and the amount, as well as the number of regular payments for closing.

Solving problem 2 in Excel using the NPER and PMT functions:

Note! If the bank prohibits early repayment of the loan without penalty fees, then under the conditions of lending in the first line it is impossible to repay such a loan! Be careful and learn Excel.



Car depreciation maintenance cost

The declining balance method allows us to analyze a type of expense such as the depreciation of a car, tractor, equipment, or other expensive device to operate. Having solved this problem, you understand your motives and rationality in acquiring such an expensive product. To begin with, let's correctly set the task, which will be as close as possible to other similar tasks in the life of a modern person.

Problem 3: The cost of purchasing a trucking vehicle is $20,000 after 10 years of operation, the cost is only $40,000. Determine the depreciation charge and the current residual value of the vehicle for each year throughout the life of the vehicle.

Solving problem 3 in Excel using formulas, a graph, and the function \u003d FPU () :

Predicting Future Progress Indicators

Now we will analyze time series with progression with exponential growth of indicators.

Task 4: Predict the firm's future performance. A table containing all important series values ​​for all time points is automatically populated with indicators using a single GROWTH prediction function (working directly with data tables).

In the second argument of the GROWTH function "Known_values ​​x" - contains a range of cells in which points outside the time series are placed, for which the values ​​of the tested parameter will be extrapolated. These are the predicted values ​​(projected into the future) of the series.

Constant - a logical constant that determines the point of intersection of the trend line with the axis of the independent variable. This is usually a coordinate point (0, b) and is TRUE by default. Use the default value.

Solving problem 4 in Excel using the growth forecasting function - GROWTH and graphics.

Smart filtering of a large amount of data

The advanced AutoFilter filter in Excel allows you to quickly process large amounts of data and immediately return the result according to the filter criteria.

Task 5: Display fish with prices no higher than 20 and prices for spices no less than 20 with non-zero stock in the warehouse. Also, show drinks with prices between 10 and 20 with non-zero stock.

Solving problem 5 in Excel using advanced autofilter.