Spreadsheet calculations using personal computers

14.2. Functionality of table processors

14.3. Spreadsheet technology

The spreadsheet is the most common and powerful information technology for professional data management. To manage spreadsheets, special software products have been created - table processors.

This chapter is theoretical in nature and is aimed at developing an understanding of the capabilities of the spreadsheet and its use. Basic functionality and technological operations of a spreadsheet are discussed without being tied to a specific type of table processor. The knowledge gained is basic for practical acquaintance with any new type of table processor.

You will find recommendations for the practical development of a spreadsheet processor in Chapter 5 of the workshop for the Excel 5.0 for Windows environment, which is used with great success in any workplace. The methodology proposed by the authors will allow independently master several types of table processors.

Both chapters are connected by a common idea and are structured in such a way as to gradually introduce a variety of spreadsheet technology tools - from standard procedures to complex modes used in professional work with data.

Purpose of the chapter- introduce the functionality of a spreadsheet processor and the general methodology for using a spreadsheet in professional work with data.

After studying this chapter you should know:

Purpose of the Spreadsheet

Features of a typical table processor interface

Types of data that cells can contain

Formats for representing numeric and character data

The concept of relative and absolute addresses

Concept of formulas and functions in arithmetic expressions

Rules for automatically changing relative addresses that apply when copying and moving formulas

Purpose of basic standard table processor commands

Ways to combine data from multiple spreadsheets into one

Graphical capabilities of spreadsheet processors for data presentation

The main technological stages of working with data in the environment of any table processor

14.1. Basic Concepts

History of the appearance and development of the spreadsheet

Table Processor Interface

Data stored in spreadsheet cells

Automatically change relative references when copying and moving formulas

HISTORY OF THE APPEARANCE AND DEVELOPMENT OF THE ELECTRONIC TABLE

The idea of creating a spreadsheet came from Harvard University (USA) student Dan Bricklin in 1979. Carrying out boring economic calculations using a ledger, he and his friend Bob Frankston, who knew programming, developed the first a spreadsheet program they called VisiCalc.

VisiCalc soon became one of the most successful programs. It was originally intended for computers like the Apple II, but was later transformed for all types of computers. Many believe that the sharp increase in sales of computers like Apple at that time was associated with the possibility of using the VisiCalc spreadsheet processor on them. In the similar spreadsheets that soon appeared (for example, SuperCalc), the basic ideas of VisiCalc were improved many times over.

A new significant step in the development of spreadsheets was the appearance in 1982 of Lotus 1-2-3 software on the market. Lotus was the first spreadsheet processor to integrate, in addition to conventional tools, graphics and the ability to work with database management systems. Because Lotus was designed for computers like IBM, it did for that company what VisiCalc did for Apple. After developing Lotus 1-2-3, Lotus increased its sales to $50 million in the first year. and becomes the largest independent software company. Lotus's success led to increased competition from new spreadsheets such as Paperback Software's VP Planner and Borland International's Quattro Pro, which offered much the same tools at much lower prices.

The next step was the appearance in 1987 of the Excel spreadsheet processor from Microsoft. This program offered a simpler graphical interface in combination with drop-down menus, while significantly expanding the functionality of the package and increasing the quality of the output information. Expanding the range of functionality of a spreadsheet, as a rule, leads to complication of working with the program.

Excel developers managed to find a middle ground, making it as easy as possible for the user to learn and work with the program. Thanks to this, Excel quickly gained popularity among a wide range of users. Currently, despite the release by Lotus of a new version of the spreadsheet, which uses a three-dimensional table with improved capabilities, Excel occupies a leading position in the spreadsheet processor market.

The spreadsheet processors available on the market today are capable of working in a wide range of economic applications and can satisfy almost any user.

TABLE PROCESSOR INTERFACE

What is a spreadsheet

Spreadsheet– the computer equivalent of a regular table, in the cells (cells) of which data of various types is recorded: texts, dates, formulas, numbers.

The result of calculating a formula in a cell is an image of that cell. Numerical data and dates can be considered a special case of formulas. To manage a spreadsheet, a special set of programs is used - table processor.

The main advantage of a spreadsheet is the ability to instantly recalculate all data related by formula dependencies when the value of any operand changes.

Rows, columns, cells and their addresses

The workspace of a spreadsheet consists of rows and columns that have their own names. Row names are their numbers. Line numbering starts from 1 and ends with the maximum number set for this program. The column names are letters of the Latin alphabet, first from A to Z, then from AA to AZ, BA to BZ, etc.

The maximum number of rows and columns is determined by the features of the program used and the amount of computer memory. Modern programs make it possible to create spreadsheets containing more than 1 million cells, although for practical purposes this is not required in most cases.

The intersection of a row and a column forms table cell, having its own unique address. To specify cell addresses in formulas, use links(for example, A2 or C4).

Cell– The area defined by the intersection of a column and a row in a spreadsheet.

Cell address determined by the column name (number) and row number.

Specifying a block of cells

In a spreadsheet there is a concept block(range) cells, also having its own unique address. A block of cells can be considered a row or part of a row, a column or part of a column, as well as a rectangle consisting of several rows and columns or parts thereof (Fig. 14.1). The address of a block of cells is specified by indicating the links of its first and last cells, between which, for example, a separating character is placed - a colon<: > or two points in a row <..>.

Example 14.1.

The address of the cell formed at the intersection of column G and row 3 will be expressed by the reference G3.

The address of the block formed as part of line 1 will be A1..H1.

The address of the block formed as column B will be B1 ..B 10.

The block address, formed in the form of a rectangle, will be D4..F5.

Each spreadsheet command requires specifying a block (range) of cells on which it should be executed.

The block of usable cells can be specified in two ways: either direct dialing from the keyboard the starting and ending addresses of the cells forming the range, or highlighting the corresponding part of the table using the cursor keys. It is more convenient to set a range by selecting cells.

Typical default settings for all table cells are 9-bit cell width, left-justified for character data, and right-justified basic format for numeric data.

Cell block– a group of consecutive cells. A block of cells can consist of a single cell, a row (or part thereof), a column (or part thereof), or a sequence of rows or columns (or parts thereof).

Typical interface structure

As can be seen in Fig. 14.1, when working with a spreadsheet, the table's working field and control panel are displayed on the screen. The control panel usually includes: Main menu, secondary control area, input line and help line. The location of these areas on the screen can be arbitrary and depends on the characteristics of a particular table processor.

Main menu bar contains the names of the menu of the main program modes. By selecting one of them, the user gets access to a drop-down menu containing a list of commands included in it. After selecting some drop-down menu commands, additional submenus appear.

Rice. 14.1. Spreadsheet view on screen

Auxiliary area management includes:

status bar;

toolbars;

vertical and horizontal scroll bars.

IN status bar(status line) the user will find information about the current operating mode of the program, the file name of the current spreadsheet, the number of the current window, etc. Toolbar(icon menu) contains a certain number of buttons (icons) designed to quickly activate the execution of certain menu commands and program functions. To display those areas of the table that are not currently displayed on the screen, use vertical And horizontal scroll bar. Scroll bar sliders show the relative position of the active cell in the table and are used to quickly move around it. In some spreadsheet processors, special shortcut zones are formed on the screen. When you click in such a zone, the corresponding function is called. For example, when you click on the coordinate ruler, a dialog for setting page parameters is called up.

Input line displays the data entered into the cell. In it, the user can view or edit the contents of the current cell. A special feature of the input line is the ability to see the formula or function contained in the current cell, and not its result. The input line is convenient to use to view or edit text data.

Prompt line is intended to provide messages to the user regarding his possible actions at the moment.

The given interface structure is typical for table processors designed to work in a Windows environment. For spreadsheet processors running in DOS, command buttons on toolbars and scroll bars are most often absent.

Working field– the space of a spreadsheet consisting of cells, column names and rows.

Control Panel– part of the screen that gives the user information about the active cell and its contents, menu and operating mode.

Current cell and screen

Current (active) called cell spreadsheet in which the cursor is currently located. The address and contents of the current cell are displayed in the spreadsheet input line. Moving the cursor both on the input line and on the screen is done using the cursor keys.

The monitor screen does not allow you to display the entire spreadsheet. We can view different parts of the spreadsheet by moving around it using the cursor keys. When moving around the table in this way, new rows (columns) automatically appear on the screen to replace those that we are moving away from. The part of the spreadsheet that we see on the monitor screen is called current(active) screen.

In everyday life, a person constantly uses tables: a diary at school, train schedules, class schedules, etc. A personal computer expands the possibilities of using tables due to the fact that it allows not only to present data in electronic form, but also to process it. The class of software used for this purpose is called spreadsheet processors or spreadsheets. The main purpose of spreadsheet processors is to process tabularly organized information, carry out calculations based on it and provide a visual representation of stored data and the results of their processing in the form of graphs and diagrams.

A spreadsheet or spreadsheet is an interactive data processing system based on a two-dimensional table. Table cells can contain numbers, strings, or formulas that specify the cell's dependency on other cells. The user can view, set and change the value of cells. Changing the value of a cell immediately changes the values of the cells that depend on it. Table processors also provide setting the image format, searching, and sorting. The use of spreadsheets simplifies working with data and allows you to obtain results without manual calculations. Calculation using specified formulas is performed automatically. Changing the contents of any cell leads to the recalculation of the values of all cells that are related to it by formula relations.

Spreadsheets are used in all areas of human activity, but are especially widely used for economic and accounting calculations. Currently, the most popular and effective packages of this class are Excel, Calc, Quattro Pro, Lotus 1-2-3.

A spreadsheet is the computer equivalent of a regular table, in the cells (cells) of which data of various types is recorded: texts, dates, formulas, numbers.

The main advantage of a spreadsheet is the ability to instantly recalculate all data related by formula dependencies when the value of any operand changes.

Rows, columns, cells and their addresses.

The workspace of a spreadsheet consists of rows and columns that have their own names. Row names are their numbers. Line numbering starts from 1 and ends with the maximum number set for this program. The column names are the letters of the Latin alphabet, first from A to Z, then from AA to AZ, BA to BZ, etc.

The intersection of a row and a column forms a table cell that has its own unique address. To specify cell addresses in formulas, references are used (for example, A2 or C4).

A cell is the area defined by the intersection of a column and a row of a spreadsheet.

Cell address - determined by the name (number) of the column and row number.

Specifying a block of cells

In a spreadsheet, there is the concept of a block (range) of cells, which also has its own unique address. A block of cells can be considered a row or part of a row, a column or part of a column, as well as a rectangle consisting of several rows and columns or parts thereof. The address of a block of cells is specified by indicating the links of its first and last cells, between which, for example, a separating character is placed - a colon<:>or two points in a row<..>.

The main menu line contains the menu names of the main program modes. By selecting one of them, the user gets access to a drop-down menu containing a list of commands included in it. After selecting some drop-down menu commands, additional submenus appear.

The auxiliary control area includes:

* status bar;
* toolbars;
* vertical and horizontal scroll bars.

In the status bar (status line) the user will find information about the current operating mode of the program, the file name of the current spreadsheet, the number of the current window, etc. The toolbar (icon menu) contains a certain number of buttons (icons) designed to quickly activate the execution of certain menu commands and program functions. To bring to the screen those areas of the table that are not currently displayed on the screen, use the vertical and horizontal scroll bars. Scroll bar sliders show the relative position of the active cell in the table and are used to quickly move around it. In some spreadsheet processors, special shortcut zones are formed on the screen. When you click in such a zone, the corresponding function is called. For example, when you click on the coordinate ruler, a dialog for setting page parameters is called up.

The input line displays the data entered into the cell. In it, the user can view or edit the contents of the current cell. A feature of the input line is the ability to see the formula or function contained in the current cell, and not its result. The input line is convenient to use to view or edit text data.

The hint line is intended to provide messages to the user regarding his possible actions at the moment.

The workspace is the space of a spreadsheet consisting of cells, column names and rows.

The control panel is a part of the screen that gives the user information about the active cell and its contents, menu and operating mode.

A function is understood as the dependence of one variable (y) on one (x) or several variables (x1, x2,..., xn). Moreover, each set of values of the variables x1, x2,..., xn will correspond to a single value of a certain type of dependent variable y. Functions are entered into the table as part of formulas or separately. The following types of functions can be presented in spreadsheets:

- mathematical;
- statistical;
- text;
- brain teaser;
- financial;
- date and time functions, etc.

Math functions perform various mathematical operations, such as calculating logarithms, trigonometric functions, converting radians to degrees, etc.

Statistical functions perform operations to calculate the parameters of random variables or their distributions represented by a set of numbers, for example, standard deviation, mean, median, etc.

Text functions perform operations on text strings or a sequence of characters, calculating the length of the string, converting uppercase letters to lowercase letters, and so on.

Logical functions are used to construct logical expressions, the result of which depends on the truth of the condition being tested.

Financial functions are used in complex financial calculations, for example, determining the discount rate, the size of monthly payments to repay a loan, determining depreciation charges, etc.

All functions have the same notation and include the function name and a comma-separated list of arguments in parentheses.

Modern spreadsheet processors allow the use of numerous means of automating problem solving, so it has even become possible to write specific applications based on them. In addition, they have extensive graphics capabilities. Spreadsheet processors are especially widely used in analytical activities, as well as for preparing documents of complex form.

The main purpose of the spreadsheet processor is to automate Calculations in tabular form.

For example, you can keep a progress log in a spreadsheet processor. Teachers will be able to enter student grades into it, and built-in formulas will allow them to calculate the average score for each student, the overall performance of the group in the subject, etc. Every time a teacher enters a new grade, the spreadsheet processor will automatically recalculate all the results.

Compared to its paper predecessor, the electronic one provides the user with much more opportunities to work. Various numbers, dates, texts, logical values, functions, and formulas can be written in table cells. Formulas allow you to almost instantly recalculate and display a new result in the corresponding cell when the source data changes. This feature allows you to actively use spreadsheets:

- to automate calculations;
- to present calculation results in the form of diagrams;
- for modeling, when the influence of different parameter values is studied.

The spreadsheet processor has become widespread throughout the entire economic system: in the accounting departments of firms and enterprises, in economic departments, in commercial banks and other organizations, which is associated with the large number of economic transactions and their versatility.

The table processor is an integral part of the application software of the economist's workstation, which is associated with its functionality.

The spreadsheet processor allows you to automate the process of processing economic information, carry out complex calculations, analyze them and present them in a visual form (graphs, diagrams). Nowadays, when the client is increasingly paying attention to the efficiency and clarity of the information provided, and for economists the processing and storage of large volumes of data is becoming increasingly important, Excel functions such as compiling lists, pivot tables, the ability to use formulas, copying data play an important role , formatting and design, analyzing and presenting data using charts and pivot tables, retrieving information from external databases, ensuring security.

A document created in MS Excel is called a workbook. A workbook consists of a set of worksheets. There can be up to 255 sheets in an Excel workbook. Each worksheet has a name, by default Sheet 1, Sheet 2, Sheet 3.

A table can contain both master and derived data. The advantage of spreadsheets is that they allow automatic calculation of derived data. For this purpose, formulas are used in table cells.

Excel treats the contents of a cell as a formula if it begins with an equal sign (=). Thus, to start entering a formula into a cell, just press the “=” key. However, it is more convenient to enter formulas if you click on the Edit formula button in the formula bar. In this case, the Formula Palette opens directly below the formula bar, containing the calculated value of the specified formula.

Excel spreadsheets are often used to maintain simple databases. The capabilities of such databases are noticeably less than those of databases developed in the Access program, but many prefer not to waste time on mastering a new system, but to use available tools.

1. A table used as a database usually consists of several columns, which are the fields of the database. Each line represents a separate entry. If the data is presented in this form, Excel allows you to sort and filter.
2. Sorting is the ordering of data in ascending or descending order. The easiest way to sort this is by selecting one of the cells and clicking the Sort Ascending or Sort Descending button.
3. Sorting parameters are set using the Data > Sorting command. This opens the Sort Range dialog box. In it you can select from one to three sorting fields, and also set the sort order for each field.
4. When filtering the database, only records that have the required properties are displayed. The simplest filtering tool is an autofilter. It is launched by the command Data > Filter > Auto-Filter.
5. When using the AutoFilter command, expanding buttons appear in the cells containing field headers. Clicking on such a button opens access to a list of filtering options. Records that do not meet the filtering criteria are not displayed.
6. To create a custom filter, select Other from the drop-down list. The Custom AutoFilter dialog box allows you to set a more complex filtering condition for this field.
7. The Data > Filter > Display All command allows you to display all records. To cancel the use of an autofilter, you must re-issue the command Data > Filter > Autofilter.

Send your good work in the knowledge base is simple. Use the form below

Students, graduate students, young scientists who use the knowledge base in their studies and work will be very grateful to you.

Posted on http:// www. allbest. ru/

1. Spreadsheet processor using MS EXCEL as an example

Table processor is a set of interrelated programs designed for processing spreadsheets.

In the course of entering and processing information, it is necessary to carry out various manipulations with source data (copying, deleting, transferring, various calculations, etc.). For operations on data, Microsoft Excel offers a system of tools for their implementation:

* Main (command) menu. processor cell formula link

* Toolbars (the most commonly used toolbars are Standard and Formatting). If they are not on your computer screen, go to the main menu and select “View”, “Toolbars”. Make these panels available to you.

· Context menu, which is called up with the right mouse button and provides convenient and quick access to the necessary commands.

· Formula bar, which is located under the toolbars. Let's take a closer look at it. On the left side of the formula bar you can see the coordinates of the currently active cell. The right side displays information that is entered or entered into the active cell.

Spreadsheet- a computer equivalent of a regular table, in the cells (cells) of which data of various types is recorded: texts, dates, formulas, numbers.

The main advantage of a spreadsheet is the ability to instantly recalculate all data related by formula dependencies when the value of any operand changes.

A text value is any combination of upper and lower case alphanumeric characters, including numbers and special characters.

To enter a number, date, or number as text, precede it with an apostrophe ".

A formula is an expression that calculates a new value from existing ones. Formulas can include numbers, mathematical operators, cell references, and built-in expressions called functions. The formula begins with an equal sign, for example: =10+20+30

Standard arithmetic operators: + - * / ^

The formula is displayed in the formula bar, the result is displayed in the cell.

EDITING A SHEET

Editing includes:

Selecting cells and ranges;

Cleaning and removing cells;

Copying data from one cell to another;

Move data from one cell to another using drag and drop;

Adding new rows and columns to a sheet.

FORMATTING A SHEET

Cell formatting includes:

Changing the vertical and horizontal alignment of data in a cell;

Setting number formats;

Specifying the font, text colors and background;

Adding borders and shading to cells.

2. The concept of a cell and how to work with it

Cell- The area defined by the intersection of a column and row of a spreadsheet.

Cell address- determined by the column number and row number.

Each spreadsheet command requires specifying a block (range) of cells on which it should be executed.

Cell block- a group of consecutive cells. A block of cells can consist of a single cell, a row (or part thereof), a column (or part thereof), or a sequence of rows or columns (or parts thereof).

The current document in the spreadsheet processor is called a book.

The book consists of sheets.

Each book is saved on disk as a file.

Techniques for working with cells:

· Cell selection (this is the selection of a cell with which the user or EXCEL software will work), for this you need:

1. Place the cursor on the required cell;

2. Click the left mouse button.

Visually, selection is accompanied by the appearance of a frame around the cell, in the lower right corner of which there is a small square - a fill marker (File handle). You can do the same cell block selection- this is the selection of a block of cells with which the user or EXCEL software will work.

· Entering data into cells

To enter a number, text, or formula into a cell:

1. Select a cell;

2. Enter the input data on the keyboard;

3. Press the ENTER key.

· Copying formulas is a powerful tool for automating calculations in EXCEL. It allows you to spread the influence of a formula from the first cell of a certain block of cells to the remaining cells of this block. In this case, the addresses of the cells containing the arguments of the copied formulas are automatically indexed.

To copy the formula you need:

1. Select the first cell of the block containing the formula;

2. Place the cursor on the fill marker (small square in the lower right corner of the cell). In this case, the cursor should take the form of a cross, i.e. +

3. Press the left mouse button and, while holding it, move the cursor to the last cell of the block.

If, when copying a formula, it is necessary to fix the addresses of some cells or components of these addresses, then the $ sign is used for this purpose. The cell address is also fixed when assigning a name to the cell

Setting cell borders and cell blocks

To increase the visibility of the data processing process, it is advisable to surround cells and blocks of cells with borders (frames).

To set borders around cells or blocks of cells:

2. Place the cursor over the arrow to the right of the “borders” button and click the left mouse button

3. Select with the cursor from the set of possible types of borders - a frame along all cell borders (the last type of borders in the set) and click the left mouse button. A block of cells surrounded by borders.

· Naming cells and cell blocks

To automate calculations, it is often necessary to assign names to individual cells and blocks of cells using EXCEL. To assign a name to a cell or block of cells:

1. Select a cell or block of cells;

2. Place the cursor over the arrow to the right of the name window and click the left mouse button;

3. Type a name on the keyboard, for example _ x;

4. Press the Enter key.

When setting a name, consider the following rules:

1. The name must begin with a letter or an underscore.

2. The name should not coincide with the addresses of cells and blocks, for example A5; B5:C6.

· Changing column width

When entering text data into a cell, sometimes it becomes necessary to change the column width. To change the column width:

1. Place the cursor on the right border of the column header. The cursor will then appear as follows.

2. Press the left mouse button and, while holding it, move the cursor to the right (to increase) or to the left (to decrease) the column width.

· Change the color of cells and cell blocks. To do this you need:

1. Select a cell (block of cells);

2. Place the cursor on the arrow to the right of the “fill color” button and click the left mouse button;

3. In the “fill color” menu, select the desired color with the cursor, for example, emerald, and click the left mouse button.

If, when working with a book, in formulas you put a link to a sheet or other sheets of the same book, you will receive relative links of the form “=A1” or “=Sheet2!A21”. This type of link remembers at what distance the cell(s) are located and when dragging formulas across columns or rows, the address in the formula changes relative to the cell in which the formula is written.

Absolute links are the exact opposite of relative links. The absolute cell reference remains unchanged during any manipulation with the formula. Absolute links are written as follows: “=$C$1+$D$5”. In this case, the "$" sign fixes the columns and rows. Now if you drag the formula down or to the side, the addresses will still be “=$C$1+$D$5”. The use of absolute references is useful in cases where it is necessary to use one parameter common to all formulas in calculations, and if this parameter changes, it is enough to correct it in only one cell.

A mixed reference type is both an absolute and a relative type combined. This type of link allows you to fix either a column or rows and has the following form: “=$C1+D$5”. To make it easier to change the type of links, Excel provides the F4 key.

4. Formulas

Worksheet cells contain not only values, but also formulas that are used to perform calculations using the values contained in other cells. Excel formulas can perform simple arithmetic, complex calculations, and logical tests. Moreover, they allow you to convert numbers or create alternative scenarios with immediate calculation of results, without the need for additional data entry.

A FEW IMPORTANT RULES:

· The formula always begins with an equal sign.

· You can use any arithmetic operator in the formula: addition (+), subtraction (-), multiplication (*), division (/), percentage (%) or exponentiation (^).

· To compare two values and obtain a result (Yes or No, True or False, etc.), you can use logical operators: equal to (=), greater than (>), less than (<), больше или равно (>=), less than or equal to (<=) и не равно (<>).

· To merge (or concatenate) two text strings into one, use the ampersand (&).

· You can include a cell address in any part of the formula. When performing calculations, Excel will substitute the value contained in the corresponding cell instead of the address - as if it had been entered directly in the formula.

· The order of calculations can be determined using parentheses. Without parentheses, Excel performs arithmetic operations in the following order: exponentiation, then multiplication and division, and finally addition and subtraction. The order in which the operators are executed in a formula is extremely important because it often affects the result.

· When you enter text or a number into a cell, Excel assumes that it is a value. Excel displays values exactly as they were entered (conversions are only possible if some formatting is used for the cell). However, if the first character you enter is an equal sign, Excel knows that you are entering a formula.

· If you have a cell in your worksheet whose value begins with a # sign, be aware that this is Excel's message that it cannot calculate (or display) a result using the formula contained in that cell. So-called error codes are indicated in a similar way. In order for the cell to display the correct result instead of the error code, you must fix the problem - either by editing the formula or changing the contents of the cells referenced in the formula.

5. Diagrams

To create a chart in Excel, first enter numeric data into a worksheet. You can then build a chart from them by selecting the type you want from the Office Fluent ribbon (Insert tab, Charts group).

1) Data Sheet and Chart

A chart created from data in an Excel worksheet supports a variety of chart types, allowing you to present data in the most understandable way for a particular audience. When you create a new chart or edit an existing chart, you can choose from a variety of chart types (such as bar chart or pie chart) and subtypes (such as stacked bar chart or 3-D pie chart). By combining different types in one chart, you can create a mixed chart.

A diagram consists of various elements. Some of them are displayed by default, others can be added as needed. You can change the appearance of chart elements by moving them to a different location or changing their size or format. You can also remove chart elements that you don't want to display.

b To change the chart, you can do the following:

Change the appearance of the chart axes. You can specify the scale of the axes and change the spacing between values or categories. To make the chart easier to read, you can add tick marks on the axes and specify the amount of space between them.

Add titles and data labels to the chart. To help explain the data displayed in a chart, you can add a chart title, axis titles, and data labels.

Adding a legend and data table. You can show or hide the legend, change its location or elements. For some charts, you can also display a data table that contains the legend keys and values represented in the chart.

Apply special parameters for different types of charts. For different chart types, you can apply a variety of special lines (such as swing corridors and trend lines), bars (such as up and down bars and error bars), data markers, and so on.

6. Function Wizard

Function Wizard in Excel is an assistant that allows you to quickly find and insert the desired function into a worksheet. Carefully study how the Function Wizard works; this knowledge will definitely be useful to you in the future. In this lesson we will insert the function we need using the Wizard, going through all the stages from start to finish.

1) Select the cell that will contain the formula.

2) Open the tab Formulas on the Ribbon, and then select Insert Function.

3) The Function Wizard will open. In the dialog box that appears Inserting a function enter a few keywords that describe the type of calculation the function you want performs, and then click Find.

4) Look through the list of results to find the function you want, then click OK.

5) A dialog box will appear Function Arguments. Select field Value1, and then type or select the cells you want. In our example, we will enter the range A3:A10. If necessary, we can continue to fill in the arguments in the fields Value2, Value3 etc. In the same example, we want to count the number of positions only in the range A3:A10.

6) If all data is entered correctly, click OK.

7) The Function Wizard will close and you will see the result.

7. Sorting and filtering data

Sorting or arranging lists makes it much easier to find information. After sorting, records are displayed in the order determined by the column values (alphabetical, ascending/descending by price, etc.).

For example, Make a short list for practice. Select it.

Click the button "Sort and Filter" on the panel "Editing" ribbons "Home". Select " Sorting from A to Z" or "Custom sorting.." To apply an autofilter, click the same button as when sorting - "Sort and Filter" and select "Filter"(Of course, a range of cells must be selected first). As a result, the filtered data will appear in a new list.

8. Statistical data processing

Functions that implement statistical methods of data processing and analysis are implemented in Excel in the form of special software tools - the Analysis Pack add-on, which is included in the Microsoft Office package and can be installed at the request of users. Installing the Analysis Package add-on is carried out in the same way as installing other add-ons using the Tools/Add-on command. Next, you need to check the box in front of the Analysis package item and click OK. After successful installation of the add-in, the item: Data Analysis will appear in the Tools menu, and the function category - Statistical - will become available in the Function Wizard window.

The purpose of statistical research is to discover and study relationships between statistical (economic) data and use them for study, forecasting and decision-making. The fundamental concepts of statistical analysis are the concepts of probability and random variable. Excel is not designed for complex statistical analysis and data processing, but with the help of commands available from the window

Data analysis can be done:

· descriptive statistical analysis (descriptive statistics);

· data ranking (Rank and percentile);

· graphical analysis (Histogram);

· data forecasting (Moving average. Exponential smoothing);

· regression analysis (Regression), etc.

Statistical functions for regression analysis from the Statistical category in the Function Wizard window:

ь LINEST(Y value; X value; constant; stat.) - Defines the linear trend parameters for the given array;

ь TREND(Y value; X value; new X values; constant;) - Determines predicted values in accordance with a linear trend for a given array (least squares method) and many others.

The most developed means of data analysis are the statistical procedures of the Analysis Package. They have more capabilities than statistical functions. With their help, you can solve more complex problems of processing statistical data and perform a more refined analysis of this data. To access the procedures of the Analysis Pack, you need to click on the Data Analysis line in the Tools menu. A dialog box with the appropriate name will open, which lists the procedures for statistical data analysis.

In order to launch the desired statistical procedure, you need to select it with the mouse pointer and click on the OK button. A dialog box for the called procedure will appear on the screen.

The dialog box of each procedure contains controls: input fields, drop-down lists, radio buttons, check boxes, etc. These elements allow you to set the necessary parameters for the procedure being used. Some controls are specific to one procedure or a small group of procedures. The purpose of such controls will be discussed when describing the corresponding procedures.

The main goal of statistical analysis is to clarify some properties of the population being studied. If the population is finite, then the best procedure is to consider each of its elements. However, most problems use either infinite populations or finite but difficult-to-observe ones. In this situation, it is necessary to select a subset of n elements from the population, called a sample of size n, examine its properties, and then generalize these results to the entire population. This generalization is called statistical inference.

9. Scenarios

Scripts are one of the most interesting Excel automation tools, allowing you to evaluate calculation options for a particular set of values in cells. Each of the given sets of parameters is called a scenario. Only values can be entered as script parameters. Formulas, if you enter them, are accepted by the script editor but converted to numeric constants. The point of the technology is to create forecasts based on the created model. The model must have cells that contain formulas and cells that do not contain formulas. The cells in the model are connected to each other by these formulas. Before launching the technology, we give a name to each data cell. To give names to cells, use the Formulas - Assign a name command. We give names to each cell where the number is located. The name will be the name of the indicator. After this we can launch the script technology.

After these steps, the script technology window appears. In this window, click the Add (ADD) button to add a new script. In the next window, we will need to enter the name of the first scenario in the Scenario Name field, and in the Changin Cell field we enter the addresses of cells from our model that do not have formulas. After that, click OK. And a window appears on our screen in which we make changes for our cells without the form window for making changes to the script. If we want to add another script, then click the Add (ADD) button; if this is our only scenario, click the OK button. After clicking OK, a Script Manager window will appear on the screen, in which we need to select a button to create a report. After this, a small window called SCENARIO SUMMARY will appear on the screen, in which we need to enter the addresses of the cells with formulas. After that, click OK. A report of the results of our scenarios appears on the screen.

Posted on Allbest.ru

...

Story

The idea of spreadsheets was first formulated by the American scientist Richard Mattessich, who published a study entitled “Budgeting Models and System Simulation”. The concept was supplemented by Pardo and Landau, who filed an application for the corresponding patent (U.S. Patent 4,398,249 (English)). The patent office rejected the application, but the authors through the court got this decision overturned.

The generally recognized founder of spreadsheets as a separate class of software is Dan Bricklin, who, together with Bob Frankston, developed the legendary VisiCalc program in the city. This spreadsheet editor for the Apple II computer became a “killer application” that turned the personal computer from an exotic toy for technophiles into a mass-produced business tool.

Subsequently, numerous products of this class appeared on the market - SuperCalc, Microsoft MultiPlan, Quattro Pro, Lotus 1-2-3, Microsoft Excel, OpenOffice.org Calc, AppleWorks tables and gnumeric, minimalistic Spread32.

There is a spreadsheet processor for mobile phones and PDAs called SpreadCE.

List of software products

Name	OS			Note
Name	UNIX	Mac OS X	Microsoft Windows	Note
Gnumeric	Yes	Yes	Yes
en:KSpread	Yes	Yes	Yes	Part of KOffice.
en:Lotus 1-2-3	No	No	Yes	The most famous program, first released on January 26 under DOS.
Microsoft Excel	No	Yes	Yes	First released for Mac OS.
en:Numbers	No	Yes	No	Part of iWork.
OpenOffice.org Calc	Yes	Yes	Yes	Part of OpenOffice.org.

Also at one time there were quite well-known programs: en: Quattro Pro, en: SuperCalc and VisiCalc.

Links

Wikimedia Foundation.

2010.

See what a “table processor” is in other dictionaries:

Spreadsheet processor is a category of software designed to work with spreadsheets. Initially, table editors allowed processing exclusively two-dimensional tables, primarily with numerical data, but then... ... Wikipedia

320px Loading screen SuperCalc 5 Type Spreadsheet Developer Sorcim, Computer Associates Operating system CP/M, MS DOS, Apple DOS, Windows, VAX/VMS, S/360 Per ... Wikipedia

Microsoft Excel (Windows) Microsoft Excel 2007 Type Spreadsheet processor Developer OS Microsoft Windows ... Wikipedia

- (Windows) ... Wikipedia

Type Table processor Developer ... Wikipedia

KCells ... Wikipedia

Gnumeric ... Wikipedia