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 and statistical computing capabilities, graphical tools and, with the exception of Excel 2008 for Mac OS X, the VBA (Visual Basic for Applications) macro programming language. Microsoft Excel is part of Microsoft Office and today Excel is one of the most popular programs in the world.

A valuable Excel feature is the ability to write code based on Visual Basic for Applications (VBA). This code is written using a separate editor from the tables. The spreadsheet is managed using an object-oriented code and data model. With this code, the data from the input tables will be instantly manipulated and displayed in tables and charts (graphs). The table becomes the interface of the code, allowing it to be easily manipulated, modified, and guided by calculations.

With 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 of graphical presentation of data: in addition to several dozen built-in chart types, you can create your own, which are customizable types that help to visually display the subject of the chart. Those who are just learning to work with Excel will appreciate the help of the "wizards" - auxiliary programs that help with the creation of charts. They, like good magicians, asking leading questions about the foreseen further steps and showing, depending on the planned answer, the result, will lead the user "by the hand" through all the stages of building a diagram in the shortest way.

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

There are two main types of objects in Microsoft Excel: workbook 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 links between them.

Letters serve to organize and analyze data that can be entered and edited simultaneously on multiple sheets, as well as perform calculations based on data from multiple sheets. Once the diagram is created, you can place the diagram on a letter with the corresponding data or on a separate letter.

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

There are a lot of different functions in Microsoft Excel, for example:

1. Financial, among the many special functions that calculate interest on a deposit or loan, depreciation, rate of return and a variety of reciprocal and related values.

2. Date and time functions- most of the functions in this category deal with converting dates and times to different formats. Two special functions TODAY and TDATE insert the current date (first) and date and time (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 an array of data (by column, row, rectangular interval) and get a variety of information from it: the numbers of columns and rows, it includes, their number, the contents of the array element you need; you can find in which closet of this array the desired 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 (DLSTR), find out the character code (CODE), find out which character is the first (LEFT) and last (RIGHT) in a line of text, put a certain number of characters from another in the active closet collars (MID), place in the active closet all the text from another closet in large (UPPER) or lowercase letters (LOWER), check, or two text boxes match (MATCH), find some text (SEARCH, FIND) and replace it with another (REPLACE ).

7. Checking properties and values- here are the commands with which you can get information about the type of data in the collar (the number is there, text or some other information), about the format, about the current operating environment, about typical errors that occurred in the formula, etc. NS..

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

9. Chart Wizard- built-in EXCEL program, which simplifies the work with the basic features of the program.

Appointment 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 keep various tables, lists and catalogs, prepare financial and statistical reports, calculate the state of a trading enterprise, process the results of a scientific experiment, keep records, prepare presentation materials. Excel's capabilities are very high. Word processing, database management - the program is so powerful that in many cases it outperforms specialized editor programs or database programs. Such a variety of functions can be confusing at first than force to apply in practice. But as you gain experience, you begin to appreciate the fact that Excel's limits are hard to reach.

Over the long history of tabular calculations using personal computers, user requirements for such programs have changed significantly. In the beginning, the main emphasis in a program like VisiCalc was on counting functions. Today, along with engineering and accounting calculations, organizing and graphing 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. V recent times many have just switched to using Windows as their user environment. As a result, many software firms began offering a large number of Windows programs.

Excel provides both ease of handling data and their safety. Excel allows you to quickly complete work for which you do not need to spend a lot of paper and time, as well as involve professional accountants and financiers.

This program will be able to calculate the sums by rows and columns of tables, calculate the arithmetic mean, bank interest or variance, here you can generally use many standard functions: financial, mathematical, logical, statistical.

Excel has many other benefits. This very flexible system "grows" along with the needs of the user, changes its appearance and adapts to you. Excel is based on a box of cells and a 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 surely discover more and more new possibilities and properties. Explore and experiment. If you don't like the results, try again. Almost everything in Excel can be undone, so by experimenting, you won't lose anything but a few minutes of your time.

Purpose of Microsoft Excel Spreadsheets

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

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

Rectangular tables often store ordered data and are used to visualize both numbers and text information in various fields of human professional activity. The tables can store both the 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 graded sheets or filing cabinets.

With the help of computers, it has become much easier to represent and process data. Data processing programs are called spreadsheets or spreadsheets because of their similarity to a regular spreadsheet 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 processor for personal computers is Microsoft Excel.

MS Excel is used to solve economic, financial, technical and economic and engineering problems, to perform accounting and banking operations, in statistical information processing, data analysis and project forecasting, to fill out 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 the spending of your own funds.

Thus, the main purpose of spreadsheets is:

• data entry and editing;
• formatting tables;
• 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 a word processor Word or another application that is included in the Microsoft Office suite. Tight integration of the applications that are included in the package improves user efficiency, makes it possible to create professional-looking documents and use the capabilities of local and global information networks (World Wide Web) for teamwork and data publishing.

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

The main features of spreadsheets include:

• automation of all final calculations;
• performing calculations of the same type on large data sets;
• the ability to solve problems by selecting values ​​with different parameters;
• the ability to process the results of the experiment;
• the ability to tabulate functions and formulas;
• preparation of tabular documents;
• performing a search for optimal values ​​for the selected parameters;
• the ability to build graphs and diagrams based on the entered data.

Advanced features of MS Excel 2013

Remark 2

When the spreadsheet processor starts up, the user sees a full-fledged web resource in front of him, and not an ordinary 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:

• The data is stored on the cloud server using a Live ID entry. Saving documents on the cloud service makes it possible, using any equipment that has access to the Internet, to get all the necessary files. After saving the document to the cloud, the user can invite other users to edit or view it. In parallel, a user can create a special web link that will direct other users to a document with data, and set a set of actions that are 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 protection 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 a document when it is edited.
• The ability to establish rights and restrictions for other users who have access to a specific 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. Search is carried out on free collections of images, 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 electronic spreadsheet processor Excel is widely used for drawing up a variety of forms, keeping track of orders, processing lists, production planning, personnel accounting 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. It is convenient to perform a number of tasks in tabular form, for example, accounting, math 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 has become possible solution many computational problems that were previously solved exclusively by programming.

Thus, the areas of application for spreadsheets include:

• accounting and banking;
• planning and resource allocation;
• design and estimate work;
• engineering calculations;
• processing of large amounts of information;
• research of dynamic processes;
• the 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. Thanks to VBA, Microsoft managed not only to expand the capabilities of the Excel macro language, but also to introduce a new level of application programming, since VBA allows you to create full-fledged application packages that go far beyond processing spreadsheets in their functions.

The program for processing spreadsheets Microsoft Excel (hereinafter referred to as Excel or MS Excel for brevity), like the text editor MS Word, is included in the packages of the Microsoft Office family. Currently used mainly versions of MS Excel 7.0, MS Excel 97, MS Excel 2000, which are included in the packages MS Office 95, MS Office 97 and MS Office 2000, respectively. The manual discusses general issues of working with the program for processing spreadsheets, which are presented in one form or another in all the versions mentioned. Therefore, the manual does not specify the version of the program anywhere. The examples given in the tutorial were obtained in MS Excel 97 editor.

Purpose of MS Excel

The MS Excel program is a powerful and fairly easy-to-use spreadsheet processing package designed to solve a wide range of economic planning, accounting, statistical, scientific and technical and other tasks in which numerical, text or graphic information with some regular, repeating structure is presented in a tabular form. Spreadsheet just like a regular table, it consists of rows and columns, at the intersection of which are located cell... However, unlike the usual one, the spreadsheet serves not only for the visual presentation of numerical, textual and graphic information.

The main difference between a spreadsheet and a regular spreadsheet is the ability to accommodate various calculation formulas in its cells, as well as in a wide automation processing of data presented in tabular form. For example, let, for example, in one column of the table is the income of various organizations for a certain period, and in the other column you need to place income tax, which is calculated in a certain way depending on the income received. Instead of performing a manual tax calculation, the corresponding calculation formulas can be written in the cells of this column, and Excel will calculate it herself tax and will put the corresponding values ​​in the correct cells instead of standing there. formulas. Therefore, Excel is often quite often called processor spreadsheets.

Main features of MS Excel

The program for processing spreadsheets MS Excel provides the user with a rich set of possibilities for creating and modifying tables, which can contain numbers, text, dates, monetary units, graphics, as well as mathematical and other formulas for performing calculations. Entering data into tables, formatting them and performing various kinds of calculations are largely automated. Means are provided for presenting numerical data in the form of diagrams, creating, sorting and filtering lists, statistical analysis of data and solving optimization problems.

In addition to specific tools typical for working with spreadsheets, MS Excel has a standard for Windows applications a set of file operations, has access to the clipboard and undo and redo mechanisms.

MS Excel documents are written to files with the extension .xls. In addition, MS Excel can work with spreadsheets and charts created in other common packages (for example, Lotus 1-2-3), as well as transform the files it creates for use by other programs.

Main features and tools of MS Excel:

Wide possibilities of creating and modifying tables of arbitrary structure;

Autocomplete table cells;

Rich set of table formatting capabilities;

A rich set of various functions for performing calculations;

Automation of building diagrams of various types;

Powerful mechanisms for creating and processing lists (databases): sorting, filtering, searching;

Mechanisms for automating the creation of reports.

In addition to the specific, typical for programs for processing electronic tables, MS Excel has a number of capabilities and tools used in the text editor MS Word and in other applications of the MS Office package, as well as in operating system Windows:

Powerful built-in help system, availability of context-sensitive help;

Changing the shape of the text cursor and mouse pointer when changing the current situation in the editor, making it easier for the user to navigate the program and possible actions of his own;

A set of blanks (templates) of documents, the presence of wizards - subsystems that automatize the work on standard documents in standard situations;

The ability to import - convert files from formats of other spreadsheet programs to MS Excel format, and export - convert files from MS Excel format to formats of other programs;

Access to the clipboard, which allows you to transfer fragments both within one document and between different documents and applications;

Mechanisms for undoing and restoring the last performed actions after it (rollback and rollback);

Search and replace substrings;

Automation tools for working with documents - autocorrect, autoformat, auto-transfer, etc .;

The ability to format characters, paragraphs, pages, create a background, framing, underline;

Checking the correct spelling of words (spelling) using the built-in dictionary in different languages;

Extensive possibilities for managing the printing of documents (determining the number of copies, selective printing of pages, setting the print quality, etc.);

Anyone who uses a computer in their daily work, in one way or another, came across the office application Excel, which is included in the standard Microsoft Office package. It is available in any version of the package. And quite often, starting an acquaintance with the program, many users ask themselves the question of how to use Excel on their own?

What is Excel?

First, let's define what Excel is and what this application is for. Probably, many have heard that the program is a spreadsheet editor, but the principles of its operation are fundamentally different from the same tables created in Word.

If in Word a table is more likely an element in which a text is displayed, or then a sheet with an Excel table is, in fact, a unified mathematical machine that is capable of performing a variety of calculations based on the specified data types and formulas by which one or another mathematical or an algebraic operation.

How to learn to work in Excel on your own and can you do it?

As the heroine of the film "Office Romance" said, you can teach a hare to smoke. Basically, nothing is impossible. Let's try to understand the elementary principles of the application's functioning and dwell on understanding its basic capabilities.

Of course, the reviews of people who understand the specifics of the application indicate that it is possible, for example, to download some self-instruction manual for working in Excel, however, as practice shows, and especially the comments of novice users, such materials are often presented in a too abstruse form, and it can be quite difficult to figure it out.

It seems that the best training option would be to study the basic capabilities of the program, and then use them, so to speak, "by a scientific poke method." It goes without saying that you first need to consider the basic functional elements of Microsoft Excel (the lessons on the program show just this) in order to get a complete picture of how it works.

Key elements to look out for

The very first thing the user pays attention to when starting the application is a sheet in the form of a table, in which the cells are located, numbered in different ways, depending on the version of the application itself. In earlier versions, columns were designated with letters, and rows with numbers and numbers. In other releases, all markings are in digital form only.

What is it for? Yes, just so that you can always determine the cell number to specify a specific calculation operation, like how coordinates in a two-dimensional system are specified for a point. It will be clear later how to work with them.

Another important component is the formula bar - a special field on the left with an "f x" icon. This is where all the operations are set. In this case, the mathematical actions themselves are designated in the same way as it is accepted in the international classification (the equal sign "=", multiplication "*" division "/", etc.). Trigonometric values ​​also correspond to international designations (sin, cos, tg, etc.). But this is the simplest thing. More complicated operations will have to be mastered using the help system or concrete examples, since some formulas can look quite specific (exponential, logarithmic, tensor, matrix, etc.).

Above, as in others office programs the main panel and the main sections of the menu are located with the main items of operations and buttons for quick access to one or another function.

and the simplest operations with them

Consideration of whether it is impossible without a key understanding of the types of data entered in the cells of the table. Immediately, we note that after prescribing some information, you can press the enter button, the Esc key, or simply move the rectangle from the desired cell to another - the data will be saved. Editing a cell is called by double-clicking or pressing the F2 key, and upon completion of data entry, saving occurs exclusively by pressing the Enter key.

Now a few words about what you can enter in each cell. The format menu is invoked by right clicking on the active cell. On the left, there is a special column indicating the data type (general, numeric, text, percentage, date, etc.). If a general format is selected, the program, roughly speaking, itself determines what exactly the entered value looks like (for example, if you enter 01/01/16, the date January 1, 2016 will be recognized).

When entering a number, you can also use the indication of the number of decimal places (by default, one character is displayed, although when entering two, the program simply rounds the visible value, although the true value does not change).

When using, say, a text data type, whatever the user enters, it will be displayed exactly as typed on the keyboard, unchanged.

Here's what's interesting: if you hover the cursor over the selected cell, a cross appears in the lower right corner, and by dragging it while holding down the left mouse button, you can copy the data to the next cells in order. But the data will change. If you take the same example with a date, the next value would be January 2, and so on. This copying is useful when specifying the same formula for different cells (sometimes even with cross-calculations).

When it comes to formulas, a double approach can be used for the simplest operations. For example, for the sum of cells A1 and B1, which must be calculated in cell C1, you need to place the rectangle in the field C1 and specify the calculation by the formula "= A1 + B1". You can do differently by setting the equality "= SUM (A1: B1)" (this method is more used for large gaps between cells, although you can use the automatic sum function, as well as the English version of the SUM sum command).

Excel program: how to work with Excel sheets

When working with sheets, you can perform many actions: add sheets, change their name, delete unnecessary ones, etc. But the most important thing is that any cells located on different sheets can be interconnected by certain formulas (especially when large amounts of information of different types are entered).

How to learn to work in Excel on your own in terms of use and calculations? Not everything is so simple here. As the reviews of users who once mastered this spreadsheet editor show, it will be quite difficult to do this without outside help. You need to read at least the help system of the program itself. The simplest way is to enter cells in the same formula by selecting them (you can do this on one sheet or on different ones. Again, if you enter the sum of several fields, you can write "= SUM", and then simply select one by one while holding down the Ctrl key the cells you need, but this is the most primitive example.

Additional features

But the program can not only create tables with different types of data. On their basis, in a couple of seconds, you can build all kinds of graphs and diagrams by setting for automatic construction either a selected range of cells, or specify it in manual mode when entering the corresponding menu.

In addition, the program has the ability to use special add-ins, executable scripts based on Visual Basic. You can insert any objects in the form of graphics, video, audio or something else. In general, there are enough opportunities. And here is just a small fraction of all that this unique program is capable of.

But what can I say, with the right approach, it can calculate matrices, solve all sorts of equations of any complexity, find, create databases and connect them with other applications like Microsoft Access and much more - you just can't list everything.

Outcome

Now, it’s probably already clear that the question of how to learn to work in Excel on your own is not so easy to consider. Of course, if you master the basic principles of working in the editor, it will not be difficult to set the simplest operations. User reviews indicate that you can learn this in a maximum of a week. But if you need to use more complex calculations, and even more so, work with binding to databases, no matter how anyone wants it, you simply cannot do without special literature or courses. Moreover, it is very likely that you will even have to pull up your knowledge of algebra and geometry from the school course. Without this, you can not even dream of fully using the spreadsheet editor.

Microsoft Excel program is designed to organize data in a table for documenting and graphical presentation of information. MS Excel workbooks provide the ability to store and organize data for calculating the sum of values ​​in cells ...

Share your work on social media

If this work did not suit you at the bottom of the page there is a list of similar works. You can also use the search button

CONTENT

I Introduction II Main part 2.1 Description of functions of MS Excel 1.2 Program window 1.4 Structure of spreadsheets 1.5. Functions 1. 6 Possible mistakes when using functions in formulas 1.7 Data types and analysis 1.7 Scripts III Practical part 3.1 Application of functions 3.2. Script application 3.2.1. An example of calculating the internal rate of investment turnover 3.2. Building diagrams IV. Organization of the workplace V. Occupational safety while working with a PC Vi. Bibliography

I Introduction

MicrosoftExcel - a program designed to organize data in a table for documentation and graphical presentation of information.

MS Excel is used to create complex documents in which it is necessary:

· Use the same data in different worksheets;

· Change and restore links.

The advantage of MS Excel is that the program helps to operate with large amounts of information. MS Excel workbooks provide the ability to store and organize data, calculate the sum of values ​​in cells. MS Excel provides a wide range of methods to make information easy to read.

Nowadays, it is important for every person to know and have skills in working with Microsoft Office applications, since the modern world is full of a huge amount of information, with which you just need to be able to work.

Objective: to get acquainted with the functions of MS Excel for data processing

Objective: to consider the practical application of functions MS Excel

II Main part

2.1 Description of functions of MS Excel

1.2 Program window

To start Excel, run the command Start / Programs / Microsoft Office / Microsoft Excel.

After loading the program, a working Microsoft Excel window will open, containing menu items, as well as toolbars where buttons for creating a new workbook, opening an existing one, etc. are located.

The Microsoft Excel window with the loaded spreadsheet will look as shown in Figure 1.

Fig 1. - Microsoft Excel window

The main elements of the working window are:

1. Title bar (the name of the program is indicated in it) with buttons for controlling the program window and the document window (Minimize, Minimize to window or Maximize to full screen, Close);

2. The main menu bar (each menu item is a set of commands united by a common functional focus) plus a window for searching for help information.

3. Toolbars (Standard Formatting, etc.).

4. The line of formulas containing the Name field and the Insert function (fx) button as elements is intended for entering and editing values ​​or formulas in cells. The Name field displays the address of the current cell.

5. Work area (active worksheet).

6. Scroll bars (vertical and horizontal).

7. A set of tabs (sheet tabs) to move between worksheets.

8. Status bar

1.4 Structure of spreadsheets

A file created using MS Excel is usually called a workbook. You can create as many workbooks as the availability of free memory on the appropriate memory hardware allows. You can open as many workbooks as they have been created. However, only one current (open) workbook can be an active workbook.

A workbook is a collection of worksheets, each with a tabular structure. In the document window, only the current (active) worksheet is displayed, with which you are working. Each worksheet has a title that appears on the sheet tab at the bottom of the window. Using shortcuts, you can switch to other worksheets in the same workbook. To rename a worksheet, you need to double-click on its shortcut and replace the old name with a new one, or by executing the following commands: Format menu, Sheet line in the Rename menu list. Or you can, by placing the mouse pointer on the shortcut of the active worksheet, right-click, then in the context menu that appears, click on the Rename line and rename. You can add (insert) new sheets to the workbook or delete unnecessary ones. A sheet can be inserted by executing the Insert menu command, the Sheet line in the list of menu items. The sheet will be inserted in front of the active sheet. The execution of the above actions can be carried out using the context menu, which is activated by pressing the right mouse button, the pointer of which should be set on the tab of the corresponding sheet. To swap worksheets, you need to place the mouse pointer on the tab of the sheet being moved, press the left mouse button and drag the tab to the desired location.

A worksheet (table) consists of rows and columns. The columns are headed by capital Latin letters and, further, by two letter combinations. The worksheet contains 256 columns in total, named A through IV. Lines are sequentially numbered from 1 to 65536.

At the intersection of columns and rows, table cells are formed. They are the minimum elements for storing data. Each cell has its own address. The cell address consists of the column name and the row number at the intersection of which the cell is located, for example, A1, B5, DE324. Cell addresses are used when writing formulas that determine the relationship between values ​​located in different cells. Data entry and editing operations are always carried out only in the active cell. Data in adjacent cells that form a rectangular region can be referenced as a whole in formulas. A group of cells bounded by a rectangular area is called a range. The most commonly used rectangular ranges are those formed at the intersection of a group of sequential rows and a group of columns that are sequential. A range of cells is denoted by the colon-separated address of the first cell and the address of the last cell of the range, for example, B5: F15. Selection of a range of cells can be carried out by dragging the mouse pointer from one corner cell to the opposite cell along the diagonal. At the same time, the frame of the current (active) cell expands, covering the entire selected range. Fig. 2

Fig. 2

To speed up and simplify the computational work, Excel provides the user with a powerful apparatus of worksheet functions that allow almost all possible calculations.

1.5. Functions

All in all, MS Excel contains over 400 worksheet functions (built-in functions). All of them, in accordance with their purpose, are divided into 11 groups (categories):

1. financial functions;

2. date and time functions;

3. arithmetic and trigonometric (mathematical) functions;

4. statistical functions;

5. functions of links and substitutions;

6. database functions (list analysis);

7. text functions;

8. logical functions;

9. information functions(checking properties and values);

10.engineering functions;

11.Improved functions.

Writing any function to a cell of a worksheet must begin with the equals symbol (=). If a function is used as part of any other complex function or in a formula (mega formula), then the symbol exactly (=) is written before this function (formula). Any function can be called by specifying its name and the argument (parameter) or list following it in parentheses. The presence of parentheses is required, they serve as a sign that the name used is the name of a function. List parameters (function arguments) are separated by semicolons (;). Their number should not exceed 30, and the length of a formula containing as many function calls as you like should not exceed 1024 characters. When writing (entering) a formula, it is recommended to type all names in lowercase letters, then correctly entered names are displayed in uppercase letters.

1.6 Possible errors when using functions in formulas

When working with spreadsheets, it is important not only to know how to use them, but also not to make common mistakes. Studies have shown that more than half of people who often use Microsoft Excel in their work keep a regular calculator on their desktop! The reason turned out to be simple: in order to perform the operation of summing two or more cells to obtain an intermediate result (and, as practice shows, most people have to perform such an operation quite often), it is necessary to perform two extra steps. Find a place in the current table where the total amount will be located, and activate the summation operation by pressing the S (amount) button. And only after that you can select those cells, the values ​​of which are supposed to be summed up.

V Excel cell instead of the expected calculated value, you see ####### (sharp). This is just a sign that the cell is not wide enough to display the resulting number.

The following values, called error constants, Excel displays in cells formulas containing, if errors occur when calculating these formulas:

1. #NAME? - Incorrectly entered function name or cell address.

2. # DIV / 0! - The denominator value in the formula is zero (division by zero).

3. # NUMBER! - The value of the function argument is not valid. For example, ln (0), ln (-2) ,.

4. # VALUE! - Function parameters entered incorrectly. For example, instead of a range of cells, they are listed sequentially.

1.6 Data analysis in MS Excel

Data - information:

Obtained by measurement, observation, logical or arithmetic operations;

Presented in a form suitable for permanent storage, transmission and (automated) processing.

1.7 Data types and analysis

In Excel, a data type is a type, a value stored in a cell.

When data is entered into a worksheet, Excel automatically analyzes it and determines the type of data. The default data type assigned to a cell determines how data analysis can be applied to that cell.

For example, most data analysis tools use numeric values. If you try to enter a text value, the program will respond with an error message.

Data types:

1. Text

2. Numeric

3. Number

4. Numeric characters

5. Fractions

6. Date and time

7. Writing

8. Time

9. Formulas

Data analysis is a field of computer science that deals with the construction and research of the most general mathematical methods and computational algorithms for extracting knowledge from experimental (in a broad sense) data.

Data Analysis - Comparison of different information.

Working with a table is not limited to simply entering data into it. It is difficult to imagine an area where analysis of this data would not be required.

Datasheets are part of a task pool that is sometimes referred to as what-if analysis tools. A data table is a range of cells that shows how changing specific values ​​in formulas affects the results of those formulas. Tables provide a way to quickly calculate multiple versions in a single operation, and a way to view and compare the results of all the different versions on a single sheet.

MS Excel provides ample opportunities for analyzing the data in the list. Analysis tools include:

List processing using various formulas and functions;

Building diagrams and using MS Excel maps;

Checking the data of worksheets and workbooks for errors;

Structuring worksheets;

Automatic summing up (including partial sum wizard);

Data consolidation;

Pivot tables;

Special means analysis of sample records and data - selection of a parameter, search for a solution, scenarios, etc.

1.7 Scripts

One of the main benefits of data analysis is predicting future events based on today's information.

Scripting is part of a task pool that is sometimes referred to as what-if analysis tools. (What-if analysis. The process of changing cell values ​​and analyzing the effect of THESE changes on the result of calculating formulas on a worksheet, such as changing the interest rate used in the depreciation table to determine payment amounts.)

A script is a collection of values ​​that in Microsoft application Office Excel are saved and can be automatically inserted into the sheet. Scripts can be used to predict the results of worksheet calculation models. It is possible to create and save different groups of values ​​in a worksheet, and then switch to any of these new scripts to view different results... Or, you can create multiple input datasets ( mutable cells) for any number of variables and assign a name to each set. By the name of the selected dataset, MS Excel will form the analysis results on the worksheet. In addition, the Script Manager allows you to create a script summary report, which displays the results of substitution of various combinations of input parameters.

As the script is developed, the data on the sheet will change. For this reason, before starting to work with the script, you will come to create a script, save the original data, or create a copy of the Excel sheet.

All scripts are created in the Add Script dialog box. First of all, you need to specify the cells to display the projected changes. Cell references are separated from each other by a colon or semicolon. Then, in the Script Cell Value dialog box, each cell is assigned a new value. These values ​​are used when executing the corresponding script. After entering the values, a script is generated. If you select a different scenario, the values ​​in the cells change as indicated in the scenario.

To protect the script, the checkboxes selected at the bottom of the Add Script dialog box are used. The Prohibit Changes check box prevents users from modifying the script. If the Hide check box is enabled, users will not be able to see the script by opening the sheet. These options apply only when sheet protection is set.

If you want to compare several scenarios at the same time, you can create a Final Report by clicking on the Report button in the dialog box.

In many economic challenges the calculation result depends on several parameters that can be controlled.

The script manager is opened by the Service / Scripts command (Fig. 1). In the Scenario Manager window, using the corresponding buttons, you can add a new scenario, edit, delete or display an existing one, as well as combine several different scenarios and get a final report on existing scenarios.

III Practical part

3.1 Application of functions

P / p No.	Book title		The year of publishing	Price	genre	Markdown	Publisher
	Sergey Yesenin. Complete works in one volume	Sergey Yesenin	2009 r.	650r	Poems, poetry	440 rub	Alpha book
	Flying barge haulers	Zakhar Prilepin	2014	325 rub	Men's prose	220r	Edited by Elena Shubina, AST
	Avsonius. Poems	Decimus Avsonius	1993 year	768 rub	Poetry	538 RUB	The science
	I love	Vladimir Mayakovsky	2012 r.	90 rub	Poems, poetry	50 rub	ABC, ABC-Atticus
	M. Lermontov. Full composition of writings	Mikhail Lermontov	2014	572r		460 rub	ABC, ABC-Atticus
Mean
Price				2405		1708

To calculate the average, we use the function:

Average (E2: E6)

To calculate the cost, we use the formula

SUM (E2: E6)

These functions could be replaced by the formula

= (E2 + E3 + E4 + E5 + E6) / 5

E2 + E3 + E4 + E5 + E6

But in this case, if there were more data, then it would be easy to make a mistake and much more time for writing would be spoiled.

3.2. Script application

3.2.1. An example of calculating the internal rate of investment turnover

Output data: project costs amount to RUB 700 million. Expected revenues over the next five years will amount to 70, 90, 300, 250, 300 million rubles. Consider also the following options (project costs are presented with a minus sign):

600; 50, 100; 200; 200; 300;

650; 90; 120; 200; 250; 250;

500, 100,100, 200, 250, 250.

Figure 1. The Script Manager window

solution:

The IRR function is used to calculate the investment's internal rate of return (internal rate of return).

IRR - IRR - Returns the internal rate of return for a series of cash flows, represented by their numerical values. These cash flows do not have to be equal in value. However, they must occur at regular intervals, such as monthly or annually.

The internal rate of return is the interest rate adopted for an investment consisting of payments (negative values) and income (positive values), which are made in successive and equal periods.

VSD (Value; Guess)

Values ​​must contain at least one positive and one negative value.

IRR uses order of values ​​to interpret the order of payments or receipts. Make sure that payments and receipts are entered in the correct order.

If the argument, which is an array or a reference, contains text, boolean values or empty cells, such values ​​are ignored.

An assumption is a value about which it is assumed that it is close to the result of IRR.

In our case, the function for solving the problem uses only the Value argument, one of which is necessarily negative (project costs). If the internal rate of investment turnover turns out to be higher than the market rate of return, then the project is considered economically viable. If not, then the project should be rejected.

The solution is shown in Fig. 2. Formulas for calculations:

In cell B14:

BC (B5: B10)

In cell C14:

IF (B14> 12); "

The project is economically feasible ";

"The project must be discarded")

Rice. 2. Calculations of the internal rate of investment turnover

1. Consider this example for all combinations of the original data. To create a script, use the Service | Scripts | Add button (fig. 3). After clicking the OK button, it becomes possible to enter new values ​​for the cells to be changed (Fig. 4).

To save the results for the first scenario, there is no need to edit the cell values ​​- just click the OK button (to confirm the default values ​​and exit to the Scenario Manager window).

Rice. 3. Adding a script for a combination of source data

Rice. 4. Window for changing cell values

3. To add new scripts to the task under consideration, just click the Add button in the Script Manager window and repeat the above steps, changing the values ​​in the cells of the initial data (Fig. 5).

Scenario "Speed ​​of turnover 1" corresponds to the data (-700, 70, 90, 300; 250; 300), Scenario "Speed ​​of turnover 2" - (-600, 50, 100, 200, 200, 300),

Scenario "Speed ​​of turnover 3" - (-650, 90; 120; 200; 250; 250).

By clicking the Output button, you can view on the worksheet

calculation results for the corresponding combination of initial values.

Rice. 5. Scenario manager window with added scripts

4. To get a summary report on all added scenarios, click the Report button in the scenario manager window. In the window that appears, the script report, select the required report type and provide links to the cells in which the resulting functions are calculated. When you click the OK button, a script report is displayed on the corresponding sheet of the workbook (Fig. 6).

Rice. 6. Report on scenarios for calculating the rate of investment turnover

3.2. Building diagrams

Diagrams are used to graphically represent the data of worksheets (tables). The worksheet data that is used to create charts is connected to it and the chart is updated when it changes. You can create an inline chart, add it directly to a worksheet, or execute it on separate sheet charts in a workbook. Once created, you can add titles and gridlines to your chart. You can use auto formats to change the format of the chart.

To create a chart using the Chart Wizard, follow these steps:

1. Highlight the data you want to use in the chart.

2. Run the Insert / Chart command or click the Chart Wizard button.

3. The Chart Wizard dialog box will appear (step 1 of 4). Select a chart type from the appropriate list and then its type. Then click on the Next> button. You can see a preview of the chart by left-clicking on the View Result button.

Rice. 9. - Diagram wizard

4. If the address of the range in the Range text box on the Data Range tab is correct for the Next> button. Otherwise, select the range in the working window or enter the addresses of its cells and then click on the Next> button. You can make some changes by selecting the Row tab. A new dialog box will appear.

5. In this window, you can improve the diagram using the appropriate tabs. For example, a histogram might look like this. Click the Next> button. The last window of the Chart Wizard appears.

6. Specify a location for the chart.

7. Click the Finish button. The diagram will appear on the worksheet.

After inserting a chart into a worksheet, you can resize it or move it to another location by first selecting it. To select a chart, place the mouse cursor on it and click the left mouse button. In this case, a thin rectangular frame with markers in the form of black squares in the corners, in the middle of each side (dimensional markers) will appear near the diagram.

To resize the chart, do the following:

1. Select the chart.

2. Resize the chart by dragging the dimension handles. To resize proportionally, move the corner dimension handles, to change the width or height - the corresponding handles in the middle of the sides.

Rice. 10. - Chart wizard, data source

Figure 11. - Diagram wizard, diagram parameters

spreadsheet script diagram

Figure 12. - Chart wizard, chart placement

Figure 13. - Moving the diagram

IV ... Organization of the workplace

Hardware

Pentium (R) Dual - Core CPU E 5300 computer

2.6 GHz, 0.99 GB RAM

Software

1. Operating system Microsoft Windows XP Professional

Service Pack 3

1. Microsoft Office 2013

V ... Labor protection while working with a PC

General safety requirements

This manual applies to personnel operating computer facilities and peripheral equipment. The instruction contains general instructions for the safe use of electrical equipment in an institution. The requirements of this manual are binding, no deviations from it are allowed. Only specially trained personnel who are at least 18 years old, who are fit for health reasons and qualifications to perform the specified work, are allowed to independently operate electrical equipment.

Safety requirements before starting work

Before starting work, you should make sure that the electrical wiring, switches, sockets, with which the equipment is connected to the network, are in good working order, that the computer is grounded, and that it is working properly.

Safety requirements during work

To reduce or prevent the influence of hazardous and harmful factors, it is necessary to comply with sanitary rules and regulations. Hygienic requirements for video display terminals, personal electronic computers and work organization (Approved by the Resolution of the State Committee for Sanitary and Epidemiological Supervision of Russia dated July 14, 1996 N 14 SanPiN 2.2.2.542-96), and Appendix 1.2

To avoid damage to the insulation of the wires and the occurrence of short circuits, it is not allowed to: hang anything on the wires, paint over and whitewash cords and wires, lay wires and cords behind gas and water pipes, for heating system batteries, pull the plug out of the socket by the cord, force must be attached to the body of the plug.

To avoid electric shock, it is prohibited to: often turn on and off the computer unnecessarily, touch the screen and the back of the computer blocks, work on computing technology and peripheral equipment with wet hands, work on computer technology and peripheral equipment that have violations of the integrity of the case, wire insulation faults, faulty power-on indication, with signs of electrical voltage on the case, place foreign objects on computer technology and peripheral equipment.

Do not clean electrical equipment from dust and dirt while energized.

It is forbidden to check the performance of electrical equipment in unsuitable for operation rooms with conductive floors, damp, not allowing to ground accessible metal parts.

It is unacceptable to repair computer technology and peripheral equipment under voltage. Repair of electrical equipment is carried out only by specialist technicians in compliance with the necessary technical requirements.

In order to avoid electric shock, when using electrical appliances, you must not touch any pipelines, radiators, metal structures connected to the ground at the same time.

Take special care when using electricity in damp areas.

Safety requirements in emergency situations

If a malfunction is detected, immediately de-energize the electrical equipment, notify the administration. Continuation of work is possible only after elimination of the malfunction.

If a broken wire is found, it is necessary to immediately inform the administration about this, to take measures to exclude contact with people. Touching the wire is life-threatening.

In all cases of electric shock to a person, call a doctor immediately. Before the arrival of the doctor, it is necessary, without wasting time, to begin providing first aid to the victim.

Artificial respiration should be started immediately, the most effective of which is mouth-to-mouth¦ or mouth-to-nose¦, as well as external cardiac massage.

Artificial respiration for an electric shock is performed until the arrival of a doctor.

It is forbidden to have flammable substances in the workplace

It is prohibited in the premises:

a) light a fire;

b) turn on electrical equipment if the room smells of gas;

c) smoking;

d) dry anything on heating devices;

e) cover ventilation openings in electrical equipment

Sources of ignition are:

a) spark during discharge of static electricity

b) sparks from electrical equipment

c) sparks from impact and friction

d) open flame

In the event of a fire hazard or fire, personnel must immediately take the necessary measures to eliminate it, and at the same time notify the administration of the fire.

Rooms with electrical equipment must be equipped with OU-2 or OUB-3 fire extinguishers.

Safety requirements at the end of work

After completing the work, it is necessary to de-energize all computer facilities and peripheral equipment. In the case of a continuous production process, only the necessary equipment should be left on.

Vi. Bibliography

1. "Data Analysis in Excel" - Ginger Simon: Publishing House - "Dialectics", 2004.
2. "Microsoft Office Excel for Student" - L.V. Rudikov: publishing house - "BHV-Petersburg"; 2005
3. Simonovich S., Evseev G. "Excel". - "M .: INFRA M", 1998 ..
4. "Training. Excel 2000". - M .: Publishing house "Media", 2000.
5. "Fundamentals of Informatics: Textbook. Manual" / A.N. Morozevich, N.N. Govyadinova and others; Ed. A.N. Morozevich. - Minsk: "New knowledge", 2001.
6. Langer M ... "Microsoft Office Excel 2003 for Windows ". - "NT Press" - 2005.
7. Verlan A.F., Apatova N.V. Informatics, -K., Kvazar-Micro, 1998 ..
8. The newest encyclopedia of a personal computer 2000. 2nd ed., Revised. and add. -M .: OLMA-PRESS, 2000.-pp. 394-430.
9. Rudenko V.D., Makarchuk A.N., Patlandzhou N.A. Practical course of informatics / ed. Matsiona, -K., Phoenix, 1997.
10. A self-instruction manual for working on a personal computer / ed. Yu.S. Kovtanyuk, S.V. Solovyana - К: Junior, 2001 .-- p. 231-304.
11. Simonovich S.V., Evseev G.A., Alekseeva A.G. Special Informatics: Textbook. - M .: AST-PRESS: Inforkom-Press, 1999.
12. Official site of Microsoft Corp. on the Internet: http://www.microsoft.com/rus

Other similar works that may interest you. Wshm>
237.		Create macros and apply them in Excel	403.86 KB
	Creation of macros and their use in Excel Topics covered: The concept of a macro. Assigning a macro to graphic images. Understanding Macros Before we start writing VB programs, let's take a simple opportunity to create a VB macro program using McroRecorder. In addition, the generated macro code can serve as a basis for further development.
19515.		Practical application of the theory of motivation in the organization	319.27 KB
	Problems of labor motivation at Kazakhstani enterprises. The main thing in management is to encourage employees to develop their abilities for more intensive and productive work. To achieve the set goal, the following tasks were solved in the work: Based on the set goal, we can formulate the main tasks of this study: study of the theoretical foundations and modern trends of labor motivation and its role in improving the efficiency of the enterprise; analysis of the effectiveness of the motivation system in JSC BTA Ipoteka ...
20220.		Practical application of expert systems in economics	262.87 KB
	20 Practical application of expert systems in economics 26 Historical aspects and evolution of expert systems 26 Expert systems for economic analysis of diagnostics and forecasting situations. 33 Review of examples of expert systems in economics and computer systems... Expert systems deal with objects of the real world, operations with which usually require significant human experience. Expert systems have one big difference: they are not designed to ...
4777.		VІKNO PROGRAM EXCEL	146.22 KB
	Operations with sheets Basic operations are used to display the work sheets selected in the context menu as they are displayed by clicking the right mouse button of the arkusha tab. To take revenge on the command: Add Insert View Delete Change Renme Move Copy Move or Copy View all sheets Select ll Sheets and so on For example, add a type of arkush to be inserted: Arkush Worksheet Diagram Chrt Macro MS Excel. Vibir type arkusha can be inserted.
4776.		STAR TABLES IN EXCEL	39.01 KB
	Creation of tables є one of the best can be loaded into MS Excel from the analysis of the given tables and lists. Creation of tables of manual data for analysis of data for several reasons: Allowing the creation of common tables so that you can give the possibility of a group of similar data for the analysis of statistical characteristics of recording ...
3861.		PREPARATION OF THE TEST IN MS EXCEL	256.43 KB
	PREPARATION OF THE TEST IN MS EXCEL, On Sheet1 we will type the questions and on Sheet2 we will place the answer options the list of answers must be vertical the first answer - empty so that after the first student answers the test can be returned to its original position m. Fill in the fields: Press OK and in cell D1 the following function will be inserted: The meaning of the function is as follows: If the answer in the tested cell C1 coincides with the correct one, then put 1 point in cell D1, otherwise 0 in this case the answer will be correct - it has noticeably worsened, it has serial number 2 because answer # 1 is empty. 1...
1577.		Victorian listings for the EXCEL program	1.93 MB
	Having entered the range of the list, you can use the data in the list and analyze it directly from the list of positions between the list. For example, if you do not have any data on the list, you can filter a row of files and navigate through the tables. If you have entered the range of the list, you can use the data in the list and analyze it directly from the list of positions between the list.
21340.		Practical research in the secondary school № 2 r. the village of Yekaterinovka, Yekaterinovsky district	274.54 KB
	Institute social work in the education system: theoretical aspect. Legal and regulatory framework The historical aspect of the organization of social work in educational institutions. Normative legal acts as the basis of social work in the education system. Essential characteristics of social work in the education system.
7166.		Purpose of spreadsheets. Introduction to MS Excel	76.37 KB
	At the bottom of the book window are tabs for sheets and buttons for scrolling them, and at the top of the title bar. In addition, the window contains sheets and scroll bars. The two middle buttons scroll one tab to the left or right. The listed scroll buttons and the label splitting marker do not activate the sheets of the book.
20180.		Options and Chart Objects in Microsoft Office Excel	1.01 MB
	Charts allow you to: display data more clearly facilitate their perception help to observe changes in values ​​during analysis and comparison. And for analysis, it is convenient to use diagrams with their special capabilities. The result of the work of a spreadsheet processor is a document in the form of a table or diagram.