GENERAL INFORMATION ON EXCEL

Excel software belongs to a class of numerical information processing systems called SPREADSHEET. The literal translation of the term “spreadsheet” from English into Russian means “SPREADED SHEET (of paper)”. Meanwhile, in the computer world, this term means a completely specific class of software, which we call "ELECTRONIC TABLES" or systems for processing numerical information.

BRIEF HISTORICAL FLASHBACK

The first VisiCalc spreadsheet was released by Visi Corporation in 1981, and it is from this moment that it is customary to count the history of spreadsheets as an independent type of software.

The idea of ​​separating tables into a special class of documents and creating a specialized program that performs all kinds of operations with tabular data turned out to be very successful and was taken up by many companies. The popularity of spreadsheets has grown exponentially.

In 1983, Lotus Development Corporation released the 1-2-3 spreadsheet, which has become the de facto standard in its field for many years.

In 1985, the first version for the Macintosh platform, the most widely used Microsoft Excel today, appeared. A year later, this sector of desktop applications was replenished with the Quattro package, created by Borland. International Corporation. In 1989 it comes out under the name Quattro Pro.

Spreadsheets today occupy one of the leading places in the sales structure of business software... The new generation of ET is characterized by a new level of functionality. In addition to traditional tools (such as calculations using standard functions, auto-recalculation, combining worksheets), modern ET packages are focused on working in the Internet environment, supplemented by teamwork tools, and significantly expanded functions for creating business graphics. The main competitors among the manufacturers of this software in 1998 were Corel (Quattro Pro 7), Microsoft (Excel 97, 7.0), Lotus Development (Lotus 1-2-3).

APPLICATIONS FOR ELECTRONIC TABLES

¨ ¨ accounting and banking;

¨ ¨ planning resource allocation;

¨ ¨ design and estimate works;

¨ ¨ engineering calculations;

¨ ¨ processing of large amounts of information;

¨ ¨ study of dynamic processes.

KEY FEATURES OF ELECTRONIC TABLES

· · Analysis and modeling based on the performance of calculations and data processing;

· · Design of tables, reports;

· · Formatting of the data contained in the table;

· · Construction of diagrams of the required type;

· · Creation and maintenance of databases with the ability to select records by a given criterion and sort by any parameter;

· Transfer (insertion) into the table of information from documents created in other applications running in the Windows environment;

· · Printing of the final document in whole or in part;

· · Organization of interaction in the working group (collective use, that is, distribution and viewing of spreadsheets by all members of the working group;

· · Work on the Internet (data retrieval and publication of information) using the ET toolkit.

ADVANTAGES OF USING THIS IN SOLVING PROBLEMS

1. 1. Solving problems using spreadsheets frees you from compiling an algorithm and debugging a program. You only need in a certain way, write in the table the initial data and mathematical relations included in the model.

2. 2.When using formulas of the same type, there is no need to enter them repeatedly, you can copy formula into the desired cell. This will automatically recalculate the relative addresses found in the formula. If it is necessary that the reference to a cell does not change when copying a formula, then it is possible to set an absolute (unchangeable) cell address (automatic setting of links).

COMPOSITION OF ELECTRONIC TABLE

The table uses columns(256) and strings (16384).

Lines are numbered from 1 to 16384, columns are marked with Latin letters from A to Z, and combinations of letters AA, AB, ..., IV,

The element located at the intersection of a column and a row will be called - cell(cage).

The rectangular area of ​​the table is called block(range, interval) cells. It is specified by the addresses of the upper-left and lower-right cells of the block, separated by colons.

CELL MODELS IN EXCEL

Each cell in the table has the following characteristics:

· · the address

· Contents

· · image

· Format

· · name

· Note (comment)

name content image

Cell address- column and row number. Used in formulas (as a relative, absolute or mixed reference), as well as to quickly navigate a table.

Excel allows 2 link styles: A1 style and R1C1 style.

FOR EXAMPLE . Suppose that in cell D3 you need to get the product of the numbers in cells A2 (second row, first column) and B1 (first row, second column). It can be written in one of the following ways:

Link type	Style A1	R1C1 style
relative	A2 * B1	R [-1] C [-3] * R [-2] C [-2]
absolute
mixed	= $ A2 * B $ 1 A $ 2 * $ B1	R [-1] C1 * R1C [-2] R2C [-3] * R [-2] C2

Content cells can be:

· · number(signed or unsigned integer (-345), fixed-point fractional (253.62), or floating-point (2.5362e + 2));

· · text;

· · formula.

Formula- always starts with the "=" sign and can contain: numeric constants, absolute or relative references to cell addresses, built-in functions.

Function arguments are always enclosed in parentheses. Standard functions can either be entered from the keyboard, or you can use the INSERT / FUNCTION menu or the

Image- what the user sees on the monitor screen.

If the cell content is a formula, then the image will be its value.

The text placed in a cell can be "visible" in its entirety, or (if the adjacent cell is not empty) as many characters as the width of the cell.

The display of the number depends on the selected format. The same number in different formats (date, percentage, currency, etc.) will have a different image.

Cell format- number format, font, character color, frame type, background color, alignment on cell borders, cell protection.

Name- used as a replacement for the absolute cell address for use in formulas.

FOR EXAMPLE , by assigning a name to cell C3 “Product "in cell D3, you can put the formula: = Product / 3 (instead of the formula = C3 / 3). In this case, when you copy the formula, the cell address will not change.

Note- accompanying text to the contents of the cell. You can enter a note in a cell using the INSERT / NOTE menu. A cell that has a note is marked in the worksheet with a dot in the upper right corner.

So the model

cells can be represented

Spreadsheets. Purpose and main functions

One of the most productive ideas in computer science information technologies became the idea of ​​a spreadsheet. Many PC software firms have created their own versions of spreadsheet processors - applications designed to work with spreadsheets. Of these, the most famous are Lotus 1-2-3 from Lotus Development, Supercalc from Computer Associates, Multiplan and Excel from Microsoft. Domestic school computers are also equipped with simplified (educational) versions of spreadsheet processors.

Table processors (TP) are a convenient tool for economists, accountants, engineers, scientists - all those who have to work with large amounts of numerical information. These programs allow you to create tables that (unlike relational databases) are dynamic, i.e. contain so-called calculated fields, the values ​​of which are automatically recalculated according to the specified formulas when the values ​​of the original data contained in other fields change. When working with spreadsheet processors, documents are created - spreadsheets (ET). A spreadsheet (document) is created in computer memory. In the future, it can be viewed, changed, written to a magnetic disk for storage, and printed on a printer.

Table processor environment

The working field of the spreadsheet is the display screen, on which the spreadsheet is presented in the form of a matrix. ET, like a chessboard, is divided into cells, which are usually called table cells. The rows and columns of the table are labeled. Most often, the lines are numerically numbered, and the columns are alphabetic (Latin letters) designations. As on a chessboard, each cell has its own name (address), consisting of the name of the column and the line number, for example: A1, C13, F24, etc.

But if there are only 8x8 = 64 cells on the chessboard, then there are much more cells in the spreadsheet. For example, an Excel spreadsheet has a maximum table size of 256 columns and 16384 rows. Since there are only 26 letters in the Latin alphabet, then starting from the 27th column, two-letter designations are used, also in alphabetical order: AA, AB, AC, ..., AZ, BA, BB, BC, ..., BZ, CA ... Last, 256- The th column is named IY. This means that there are cells with such, for example, names: DL67, HZ10234, etc.

Excel spreadsheets. Basic information.

The presentation of data in the form of tables greatly simplifies the analysis of information. To solve problems that can be represented in the form of tables, special software packages have been developed, called spreadsheets or table processors. They are focused primarily on solving economic problems, however, they can be used to solve mathematical, physical and engineering problems, for example, to carry out calculations using formulas, build graphs and diagrams.

Excel is part of the Microsoft Office suite of programs and is designed to prepare and process spreadsheets under the Windows operating shell. Excel 4.0 and Excel 5.0 are for Windows 3.1, while Excel 7.0 and 97 are for Windows 95/98. There is Excel-2000, included in the Office-2000 software package, which runs in Windows-2000. The older the version number of Excel is, the more perfect it is.

Excel program belongs to the main office computer technologies for processing numerical data.

An Excel document is a file with an arbitrary name and XLS extension. Such a * .xls file is called a Work Book. Each * .xls file can contain from 1 to 255 spreadsheets, each of which is called a worksheet (Sheet). One spreadsheet consists of 16384 rows and 256 columns located in computer memory. The lines are numbered with integers from 1 to 16384, and the columns are designated by the letters of the Latin alphabet A, B, C,…, Z, AA, AB, AC,…, IY.

At the intersection of a column and a row is the main element of the table - a cell. You can enter initial data in any cell - number, text, as well as a formula for calculating derived information. The width of a column or row can be changed with the mouse. When you enter data into a cell, this happens automatically, i.e. spreadsheets are "fluid". To indicate a specific cell, an address is used, which is made up of the column designation and the row number at the intersection of which the cell is located, for example: A1, B2, F8, C24, AA2, etc.

To make a cell active, you need to point to it with the mouse and press the left mouse button. The cell will be highlighted with a rectangular frame. When entering a formula, you must first enter the = sign, since the = sign is a sign of the formula. A rectangular group of cells specified by the first and last cell, separated by a colon, is called a spacing. Example: C5: D10. The selection of a group of cells is made with the mouse.

Excel spreadsheets can be used to create Databases. Excel is a multi-window program. The windows are Excel worksheets. To sort data, you must specify with the mouse

Menu Data, Sort.

To start Excel, you first need to start Windows, then find the Excel icon on the desktop or in the Start button menu and double-click on it. For old Windows 3.1, you need to open the window of the MS Office program group (Excel 5.0) or Applications (Excel 4.0). The specified windows contain an Excel 4.0 or 5.0 program item. Excel is launched by double-clicking the Excel program item.

At the top is the Main (horizontal) drop-down menu, which consists of 8 items. Below the menu is the toolbar (buttons with special icons).

Get help, open Excel Help - F1 key or sign? on the menu. The Excel 5.0 help system includes the Quick Start tutorial.

To create a file, click the File menu item, then the Save As item, find the directory on the disk where the file will be located, set the file name. You can use the F12 key (Excel 4.0). The file extension will be xls. The file name, if not specified, will be book1.xls (Excel 5.0, Excel-97) or sheet1.xls (Excel 4.0).

To refresh (clear) a window in Excel, click File and then New File or Ctrl-N in Excel 5.0–97. To save the file - menu item File, Save or Ctrl-S (Excel 5.0–97), or Shift-F12 (Excel 4.0).

In order to load (read) a file from disk - menu item File, Open file or Ctrl-O (Excel 5.0–97), or Ctrl-F12 (Excel 4.0). File printout - menu item Print or Ctrl-P (Excel 5.0–97) or Ctrl-Shift-F12 (Exel 4.0). Before printing, you need to make a selection and framing that section of the table with filled cells, which should be printed.

To complete (exit) Excel - menu item File and then Exit or Alt-F4. If there is an unsaved file in the window, then it must be saved or exited without saving, but then the information will be lost.

A file created in Excel 4.0 can be read in Excel 5.0 or Excel-97, but not vice versa. In Excel, you can quickly fill tables with numbers using a formula, for example, using the Edit Menu, Fill Down.

In Excel, it is possible to use the built-in tools: the Chart Wizard for plotting graphs, the Function Wizard for the production of mathematical calculations, the program for creating pictures (as in Word).

To call the Function Wizard, take the Insert Menu, Function, select the required function in the list of built-in functions.

To call the Diagram Wizard, you need to take the Insert Menu, Diagram. But first, a column of numbers is selected.

To call the program for creating drawings, take the Insert Menu, Object, select MS Draw in the list of objects. To call the specified objects, you can also use the corresponding buttons in the toolbar. Excel also has its own drawing program.

To insert a graphic file * .bmp, * .wmf, etc. into an Excel spreadsheet, point to the Insert Menu, Picture, select the necessary graphic file containing the picture on the disk, and OK.

In Excel, as in Word, it is possible to exchange information (text, graphics, formulas, diagrams, etc.) with other applications (Word, MS Works, PaintBrush, etc.) through the Windows Clipboard.

Copying, cutting and pasting the selected contents of cells is done in the same way as in Word 6.0 through the menu item Paste or the corresponding buttons in the toolbar.

Spreadsheets are a program for creating and using documents with automatic calculation of the entered data.

Excel is the most powerful spreadsheet program today. It has many features that will greatly assist you in your daily work. It should be said that there are other spreadsheet editors in the world - for example, the Russian Lexicon-XL.

One of the most important features of spreadsheets is the ability to link cells to each other using formulas, and the program allows you to work with different formats for displaying numbers - monetary, whole, date, time, percent and many others.

Excel window

The Excel working window contains a standard title bar, menus, and toolbars. Below them is a panel containing a name field and a formula bar.

The status bar at the bottom of the Excel window displays information about the program's status, modes of operation and operations being performed. For example, if your keyboard is in Num Lock mode, NUM appears at the right end of the status bar. The status bar also informs the user that it is possible to enter data or execute a command. In this case, it displays the word Done.

Workbooks and worksheets

When you start Excel, a window opens containing a new workbook. A workbook is a multi-page Excel document, each page of which is called a worksheet. The active page is displayed in the document window. By default, the workbook contains three worksheets named Sheet1, Sheet2, and Sheet3. The user can insert additional sheets into the book (the maximum possible number is 255). The left end of the horizontal scroll bar contains sheet tabs and scroll buttons for viewing them.

Excel tables are created on a worksheet. The worksheet is divided into rows and columns, which intersect to form cells. The contents of the table are entered into the cells. The worksheet has 256 columns (A through IV) and 65,536 rows. By default, rows are numbered, and columns are designated with one or two Latin letters.

Each cell has its own address, which is determined by the column name and row number to which the cell belongs. For example, the cell in the upper left corner of the worksheet with the text "Name" it contains has the address A1. While some of the text appears to flow into cell B1 in the next column, in fact, all of the text is in cell A1.

The active cell A1 in the figure is surrounded by a frame, which is called the frame of the active cell, and the column and row headers of this cell are highlighted. Only one cell is active at a time, and data can only be entered into it. To select a cell, click on it with the mouse button, and the cell becomes active (current).

Move around the worksheet

When talking about moving around a worksheet, they usually mean moving the frame of the active (current) cell. It moves one line up or down, or one column left or right with the cursor keys (arrow keys). The most frequently used keyboard shortcuts for this purpose are shown in the table.

Keys	Moving
Page Down	One screen down
Page Up	One screen up
Home	To the beginning of the current line
Ctrl + Home	To cell A1
Ctrl + End	To the last filled table cell
Ctrl + ^	Up to the first filled cell
Ctrl + v	Down to the first filled cell
Ctrl +<	Left to the first filled cell
Ctrl +>	Right to the first filled cell
Ctrl + Page Up	To the next sheet of the workbook
Ctrl + Page Down	To the previous sheet of the workbook

When you move the border of the active cell, Excel can automatically shift the borders of the visible area so that the active cell is always visible. Moving the borders of the visible area without changing the position of the active cell is called moving the worksheet or scrolling. If you need to view other areas of the worksheet, use the scroll bar items. To shuffle up or down one line, click the up or down arrow button.

These buttons are located at the top and bottom ends of the vertical scroll bar. To move one column to the left or right, use the same buttons at the ends of the horizontal scroll bar. To move up, down, left, or right one window, click on free space respectively above, below, to the left or to the right of the slider on the scroll bar.

If you need to move multiple rows or columns, use the scroll bars. To move quickly through large tables, hold down the slider while dragging.

Data input

Three types of data can be entered into the cells of the worksheet: numbers, formulas and text. Numbers (we will also refer to them as date and time values) are values ​​that are used in calculations, say, to determine the number of days by which a videotape is overdue or to calculate the accrued interest on a deposit for a certain period of time. Formulas are instructions entered into cells that are used for calculations. Text is information that is neither a number nor a formula. The text is treated as a sequence of characters, even if it is a set of numbers.

Data can only be entered into the active cell - either directly or using the formula bar, which is located under the toolbar at the top of the screen, Select the desired cell and then start entering data. The cursor will immediately appear in the cell, and the characters you enter will appear both in the cell and in the formula bar; this will make the formula bar control buttons available.

If an error occurs during data entry, click the Cancel button with a red cross in the formula bar. The input is also canceled by pressing a key on the keyboard. Press the Enter button (it has a green checkmark) to finish entering data - the formula bar buttons will disappear. Use the key to complete the entry and move to another cell.

By default, Excel supports an auto-completion feature for entering data into a cell. If several initial characters entered in the active cell coincide with the characters located in any of the cells of this column, then the missing part of the characters will be entered automatically. Excel automatically enters only those records that contain text or text combined with numbers. Records consisting entirely of numbers, dates, or times must be entered manually.

In the case when the entered numeric values ​​do not fit into the cell due to insufficient column width, several hash characters (#) are displayed on the screen instead. However, you can see the contents of such a cell in a pop-up window by placing the mouse pointer on it.

Data editing

There are two ways to change the contents of a cell. When you enter a new value in the selected cell, the old information is replaced by the entered one. This is the easiest way to replace an erroneously entered numeric value (for example, 15 instead of 17) or correct a string containing a small number of characters or short text. If the cell contains a cumbersome formula or a long string of characters that need only minor adjustments, use the formula bar is preferable. To do this, first click on a cell and place the cursor in the formula bar, and then use your mouse or keyboard and make the edit. The function key also allows you to switch to the editing mode of the active cell. You can also double-click a cell and make changes directly to it. The techniques for editing the contents of a cell are the same as in any text editor: to select characters in a cell, use the cursor keys, and to delete them, use the and keys. When you're done editing, just press a key or click the Enter button in the formula bar.

To delete the content, select the required cell, and then press the key or right-click on the cell and in the context menu that opens, select the Clear Content item. You should not call the Delete command, because this removes cells from the sheet, and adjacent cells move and take their place.

Selecting cells and ranges

The selection operation is performed to indicate the cells to which the next command or action should apply. In Excel, at least one cell is always highlighted with a frame - the active one. A cell is the smallest structural unit that can be highlighted. A group of selected cells is called a range. To select a range of cells, move the pointer to the first cell of the range (make sure that the pointer looks like a large cross, as when you select the active cell) and, while holding the mouse button, drag it in the desired direction to the last cell to be included in the range, then release the button ...

This operation can also be performed using the keyboard: place the Cell Pointer at the beginning of the selected area, press the key and then, while holding it down, expand the selected range using the cursor keys.

To select one column (row), just click on its header. To select several adjacent columns (rows), click on the first column (row) of the selected range, and then drag the pointer over the corresponding columns (rows). You can select the entire worksheet by clicking on the gray rectangle at the intersection of the row and column headings.

Selection of non-contiguous ranges is performed when the action of the command should be extended to cells located in different parts worksheet. Multiple non-contiguous ranges of cells can be selected by moving the mouse while holding down the key.

Select non-adjacent columns and rows in the same way: Select the first row or first column, and then hold down the key and select the remaining rows or columns in sequence.

Unions and intersections

Joins and intersections are special kinds of cell ranges. A join contains all cells that belong to multiple ranges. If you need, for example, to calculate the sum of the numbers in cells C2: C8 and C20: C28, then the union of two ranges of cells in the function argument is written as follows: C2: C8; C20: C28. In this case, a semicolon is used to indicate the union of all cells in both ranges.

A cell intersection is an area that contains cells that belong to multiple ranges at the same time. A space is used instead of a semicolon to indicate an intersection. For example, the intersection of two ranges, specified as C2: C10 A10: J20, contains only one cell, C10.

Creating formulas

There are many ways to create formulas. We will begin our discussion of this topic with methods for entering simple formulas that involve only one mathematical operation. For example, in a worksheet, you want to multiply the price by the quantity and determine the cost of an item. To get in cell C2 the size to determine the value of the goods, you should activate this cell, enter the equal sign, and then - the addresses of cells A2 and B3, connected by the multiplication sign (*). The formula entered into the cell should look like this:

Entering the formula is completed by pressing the key, after which the result of the calculation appears in the cell. However, the actual content of the cell will still be the formula that is displayed in the formula bar when the cell is activated, as well as in the edit mode for the contents of this cell (you can set this mode with a function key).

The disadvantage of this method is that when drawing up complex formulas, mistakes and inaccuracies are often made, for example, you can enter the wrong cell address. When there are thousands of numbers on a worksheet, the likelihood of errors increases dramatically.

Another method is based on the more reliable point-and-click method that Excel is famous for. This technique is traditional for all spreadsheet programs. In order to insert the cell address into the formula, it is enough to click on this cell instead of entering it from the keyboard.

With any method of creating formulas for input control, you can use the formula bar, which is activated by pressing the [=] key.

Calculation of the amount is one of those operations that are most often used when performing economic and technical calculations. Excel has quick way summing the contents of several cells in a row or column using the AutoSum button located on the standard toolbar.

Select the cells containing the numbers that you want to add (you can include in the selection area and an empty cell intended to accommodate the result of the summation). Then click on the AutoSum button. Excel will sum the values ​​and place the result in the first blank cell to the right or bottom (regardless of whether you selected it or not).

Table decoration

To make the data on the worksheet presentable, the table should be formatted in a certain way - set the width of the columns and the height of the rows, select the text font, set the borders of the cells, etc.

Change column widths and row heights

The clarity of the table largely depends on how well the column widths and row heights are chosen. The unit of measure for column width is a value equal to the character width of the standard sheet font. By default, the columns in an Excel worksheet are wide enough to display 8.43 characters. If the value contains more or less characters, the column width must be changed.

The column width can be changed using the mouse or commands on the Format menu. By choosing Format> Column> AutoFit Width, you will enable the program to set the optimal column width and row height by itself based on the analysis of the contents of the selected cells.

The most convenient way to change the column width is with the mouse. First, move the pointer to the right border of the column heading to make it look like a double-headed arrow. Then, while holding down left button mouse, move the edge of the column header to the left or right until the desired width is set, and release the mouse button.

Moving the border to the right increases the width of the column, and moving it to the left decreases it. The width value (in pixels) is displayed in the tooltip. When you double-click on the border of a column heading, Excel will automatically set its width so that the longest record in the column fits completely in the cell.

The exact value of the column width is set in the Column Width dialog box, which is opened by the Format> Column> Width command.

Similarly, you can change the width of several selected columns. In this case, it is enough to use the mouse to determine the desired width for one column. As soon as you release the left mouse button, all selected columns will have the same width. To set the optimal width for each of them, taking into account the contents of the cells, double-click on the border of the header of any of the selected columns.

Never leave blank columns in your worksheet. They create problems in the process of calculations, when sorting and using other means of the program.

Formatting cells

If you do not want to format the table manually, use the automatic formatting function. The program itself will create a beautiful and visual table, and you can pay more attention to working with its contents. To perform auto-formatting, use the Format> Auto-format command. If the layout of the table offered by the program does not suit you, you can use the Excel formatting tools. The program offers a wide variety of tools and options that will allow you to create professionally designed and visual tables.

There are two types of alignment in Excel: horizontal and vertical.

Horizontal alignment

By default, text in cells is left aligned, numbers are right, and booleans and error messages are centered. The default alignment options are changed using the buttons on the formatting toolbar: you can align text or numbers to the right or center. In Excel, these functions work the same as in the Word text editor.

In addition, another way of alignment is available in the Excel toolbar - by combining several cells into one. When using it, the data and the upper left cell of the selected range are distributed across all its cells: and are centered. This method is often used to create worksheet titles. Note that a cell resulting from a merge of multiple cells will only fit data from one cell. Data from other cells is destroyed.

There are several other alignment methods that are not available when working with the formatting toolbar. They can be found on the Alignment tab of the Format Cells dialog box.

Vertical text alignment

The vertical alignment of the content is set in the vertical list on the Alignment tab of the Format Cells dialog box. Bottom alignment is used by default. Center and top alignment is used to offset the contents of the cell accordingly.

Borders and color

Effective use of fonts can help you make your spreadsheets more readable. When designing worksheets, an important role is given to tools for creating range boundaries and color highlighting of information, which first of all must be paid attention to when familiarizing with the table. A border is an outline that surrounds a cell or range of cells. By using different borders, as well as changing the column widths and row heights, you can create tables of any kind. Fill is used to highlight specific areas of the worksheet with color. For greater clarity, you can highlight both text information and numerical values ​​in colored font. To control these formatting options, use the Borders, Fill Color, and Font Color buttons on the formatting bar. There are arrows next to the buttons to open the corresponding palettes.

Text cells with color, frames and rulers, paint over areas with a background or color, build graphs and diagrams based on tabular data, insert pictures into the table, etc. etc. Excel is powerful enough, its capabilities, especially in the latest versions, are quite extensive. Only one mathematical, logical, accounting, statistical functions that Excel can perform on tabular ...

Rice. 18 ☺ Do your own work with the Format Cells dialog box and explore the capabilities of each of the available tabs. Tools for automatic summarization and analysis of spreadsheet data Excel provides a variety of ways to automatically summarize and analyze data: 1. Automatic calculations; 2. Tools for working with databases and lists, allowing ...

Every modern PC user who has at least once encountered the Microsoft Office suite of programs, which is one of the most widespread today, knows that it must include the MS Excel application. We will now look at the basic concept of spreadsheets. Along the way will be given brief information about the main elements of the structure of tabular elements and data and some of their capabilities.

What is an Excel Spreadsheet Processor?

The first mention of the Excel program dates back to 1987, when the Microsoft corporation released its famous office suite of programs and applications, united under the general name MS Office.

In fact, an Excel spreadsheet processor is a universal tool for processing mathematical data of all types and levels of complexity. This includes mathematics, and algebra, and geometry, and trigonometry, and work with matrices, and the solution of the most complex systems of equations, and much more. Almost everything related to this plan is presented in the program itself. The functions of spreadsheets are such that many users not only do not know about them, they do not even suspect how powerful this software product is. But first things first.

Getting started with spreadsheets

As soon as the user opens Excel, he sees in front of him a table created from the so-called default template. The main work area consists of columns and rows, the intersection of which forms cells. In the understanding of working with all the main element of a spreadsheet is a cell, since it is in it that they are entered.

Each cell is numbered, consisting of the ordinal designation of a column and a row, however, in different versions of the application it may differ. So, for example, in the 2003 version, the very first cell at the intersection of column "A" and row "1" is designated as "A1". In the 2010 version, this approach has been changed. Here, the designation is presented in the form of ordinal numbers and numbers, but in the description line, columns are indicated as "C" and rows as "R". Many do not understand what this is connected with. But everything is simple here: "R" is the first letter of the English word row (row, row), and "C" is the first letter of the word column (column).

Data used

Speaking about the fact that the main element of a spreadsheet is a cell, you need to understand that this applies exclusively to the input data (values, text, formulas, functional dependencies, etc.). The data in cells can be in different formats. The function of changing the format of the cell can be called from by clicking on it with the right mouse button (right mouse button).

Data can be presented in text, numerical, percentage, exponential, monetary (currency), fractional and time formats, as well as in the form of a date. In addition, you can specify additional options for the view or decimal places when using numbers.

Main program window: structure

If you look closely at the main application window, you will notice that in the standard version the table can contain 256 columns and 65536 rows. At the bottom are the sheet tabs. There are three of them in the new file, but you can specify more. If we assume that the main element of a spreadsheet is a sheet, this concept should be attributed to the content on different sheets of different data, which can be used crosswise when specifying the appropriate formulas and functions.

Such operations are most useful when creating pivot tables, reports, or complex calculation systems. Separately, it should be said that if there are interconnected data on different sheets, the result when changing dependent cells and sheets is calculated automatically without re-entering a formula or function expressing one or another dependence of variables and constants.

Above is a standard panel with several main menus, and just below the line of formulas. Speaking about the data contained in the cells, we can say that this element is also the main element of the spreadsheet, since the row itself displays either text or numeric data entered in the cells, or formulas and In the sense of displaying information, the formula row and the cell are one and also. However, formatting or setting the data type is not applicable for the string. It is purely a viewer and input tool.

Formulas in Excel Spreadsheets

As for the formulas, there are a lot of them in the program. Some of them are generally unknown to many users. Of course, in order to deal with all of them, you can carefully read the same reference manual, however, the program also provides a special opportunity to automate the process.

When you click on the button on the left " f x "(Or the sign" = ») A list will be shown from which you can select the required action, which will save the user from entering the formula manually.

There is another commonly used tool. This is the autosum function, presented in the form of a button on Here, too, you do not have to enter a formula. It is enough to activate the cell in the row and column after the selected range, where the calculation is supposed to be made, and click on the button. And this is not the only example.

Related data, sheets, files and cross-references

With regard to interrelated links, data from another place in this document, a third-party file, data from an Internet resource, or an executable script can be attached to any cell and sheet. This even saves disk space and reduces the original size of the document itself. Naturally, how to create such relationships will have to be carefully figured out. But, as they say, there would be a desire.

Add-ins, charts and graphs

In terms of additional tools and capabilities, MS Excel spreadsheets provide the user with a wide choice. Apart from specific add-ins or executable scripts of Java or Visual Basic, let us focus on creating graphical visual tools for viewing analysis results.

It is clear that not everyone wants to draw a diagram or build a graph based on a huge amount of dependent data manually, and then insert such a graphic object into a table as a separate file or an attached image.

That is why their automatic creation is used with a preliminary selection of the type and type. It is clear that they are built on the basis of a certain area of ​​data. Just when building graphs and charts, it can be argued that the main element of a spreadsheet is a selected area or several areas on different sheets or in different attached files, from which the values ​​of variables and dependent calculation results will be taken.

Applying Filters

In some cases, it may be necessary to apply special filters that are installed on one or more columns. In its simplest form, it helps you search for data, text, or value in an entire column. If there is a match, all found results will be shown separately.

If there are dependencies, along with the filtered data from one column, the rest of the values ​​located in other columns and rows will be shown. But this is the simplest example, because each custom filter has its own submenu with specific search criteria or specific settings.

Conclusion

Of course, it is simply impossible to consider all the features of the MS Excel software package in one article. At least here you can understand which elements are the main ones in the tables, based on each specific case or situation, and also understand at least a little, so to speak, with the basics of working in this unique program. But to fully master it, you will have to work hard. Often, the developers themselves do not always know what their brainchild is capable of.

Applying this manual at the stage of consolidation and formation of practical skills, the student has the opportunity to update theoretical knowledge, test himself on test questions, and practice skills on practical tasks.

1. Structure of spreadsheets

A spreadsheet is an interactive numeric data processing program that stores and processes data in rectangular tables. A spreadsheet is made up of columns and rows. Column headings are designated by letters or letter combinations (A, G, AB, etc.), row headings - by numbers (1, 16, 278, etc.). Cell is the intersection of a column and a row. Each cell in the table has its own address. A spreadsheet cell address is composed of a column header and a row header, for example A1, F123, R7. The cell with which some actions are performed is highlighted with a frame and is called active. An Excel spreadsheet has 256 columns and 16384 rows. In Excel, tables are called worksheets. Worksheet (spreadsheet) is the main document type used in Excel to store and process data. By default, sheets are numbered “Sheet 1”, “Sheet 2”, etc. You can change the names and order of the sheets. The selection of another sheet of the workbook is carried out by clicking on the tab of this sheet. The selected sheet becomes active.

Control questions

1. What is the structure of spreadsheets?
2. How is a cell name set in spreadsheets?
3. What contains Sheet Excel spreadsheets?
4. What contains Workbook Excel spreadsheets?

Practical task.

1. Rename and arrange sheets in spreadsheets.

2. Entering data into a table cell

To enter data in a specific cell, you must select it with a mouse click and then click on the formula bar. The data you enter in the formula bar is reproduced in the current cell. When entering data on the left side of the formula bar, there are three buttons designed to control the input process: with a red cross, a green check mark and an icon. If you click on the button with a cross, the input will stop and the data that entered the cell in the current input session is deleted. Clicking on the button with the icon will bring up the Function Wizard dialog box. You can introduce a function.

After completing the data entry, you must fix them in the cell in any of three ways: By pressing the (Enter) key; By clicking on the checkmark button; By clicking on another cell.

You can also double-click on the selected cell to enter data, and the text cursor appears directly in the cell. Fragments of the contents of cells can be formatted in the usual way using the [Format-Cells ... / Font] tab or using the “Formatting” toolbar. You can experiment with the font yourself.

Practical tasks

1. Create a multiplication table with EXCEL (or another one according to the example).

Formulas use references to cell addresses. There are two main types of links: relative and absolute. Differences between relative and absolute references appear when you copy a formula from an active cell to another cell. Relative link in a formula is used to specify the cell address, calculated relative to the cell in which the formula resides. When you move or copy a formula from the active cell, relative references are automatically updated based on the new position of the formula. Relative links are of the form: A1, B3. By default, Excel uses relative references when typing formulas. Absolute link in a formula is used to specify a fixed cell address. Absolute references do not change when you move or copy a formula. In absolute references, a dollar sign (for example, $ A $ 1) is placed in front of the immutable cell address value.

Let's look at the action of absolute and relative links using an example. Suppose we need to calculate the cost of components for a computer in rubles, if their prices in dollars and the ruble exchange rate are known.

Copy formulas containing relative and absolute references.

1. Enter the device names in cells A5, A6 and A7, and their prices in dollars in cells B5, B6, B7. Enter the ruble exchange rate in cell C2.
2. Enter in cell C5 the formula = B5 * $ C $ 2, where B5 is a relative reference and $ C $ 2 is an absolute one. Copy formulas to cells C6 and C7; the absolute reference to cell $ C $ 2 will remain unchanged a, relative B5 is changed by the offset from the active cell.

If the dollar sign is in front of a letter (for example, $ A1), then the column coordinate is absolute and the rows are relative. If the dollar sign is in front of a number (for example, A $ 1), then, conversely, the column coordinate is relative, and the rows are absolute. Such links are called mixed.

Control questions

1. What is the difference between absolute and relative links?
2. Which links are called mixed links? Give an example.
3. In cell F13, entered the formula = F12 / $ B $ 4. Then this formula was copied to cell F16. What is the formula in cell F16?
4. In cell B7, entered the formula = (A6 + A7) * $ D $ 4. Then this formula was copied to cell F7. What is the formula in cell F7?
5. In cell D5, they entered the formula = $ A5 + B $ 5. Then this formula was copied to cell D2. What formula is in cell D2?

Practical tasks

1. Check in practice the correctness of the tasks 3, 4 and 5.
2. Open the sheet containing the table with the name of the devices and the price in USD. For the convenience of calculations, translate prices into rubles at the exchange rate, the value of which you can store in any cell convenient for you.

4. Practical task. "Price of computer devices"

In order to acquire the most efficient computer, estimate the shares that make up the prices of the devices included in it from the total price of the computer. Present the result in the form of a diagram.

Computer device price

1. Start Microsoft Excel. In your workbook, create a new worksheet for this practice activity.
2. Enter the names of the devices included in the computer and their prices in conventional units. For the convenience of calculations, we will translate prices into rubles at the rate, the value of which will be stored in cell B1 (or in another cell convenient for you).
3. Let's enter into cell C4 the formula for converting the price into rubles = $ B $ 1 * B4. Let's use the absolute reference to cell B1 to correctly copy the formula to cells C5-C15 using the [Edit - Fill - Down] command. Let's calculate the total price of the computer in cell C16 using the SUM function and determine the percentage of the price of each device.
4. Let's enter in cell D4 the formula for determining the share of the price of the device in the total price of the computer = B4 / $ B $ 16. Let's use the absolute reference to cell B16 to copy the formula correctly into cells D5-D15.
5. To represent the numbers in column D as percentages, enter the [Format-Cell ...] command. On the panel that appears Cell attributes in the window Category select Percent... The data in the table can be sorted in ascending or descending order of their values.
6. To sort devices alphabetically in ascending order, select the columns and rows of the table and click on the A-Z button. (Create copies of the table: in alphabetical order of device names, in descending order of device prices, in ascending order of device prices.). In the process of work (for example, when entering formulas), it is sometimes convenient to view the contents of cells not in the form of numbers, but in the form of formulas.
7. To view values ​​in the form of formulas, enter the [Service - Options ...] command. On the left side of the panel that appears, select Spreadsheet Document-Content. On the right side of the panel, check the Formulas box. For a visual display of the obtained numerical data, we will build a diagram.
8. Select the data area and enter the [Insert-Diagram] command. On the first panel that appears Chart auto-format (1-4) you can change the data area.
9. On the second panel, you must select the type of chart (in this case Circular volumetric) and the location of the data series (in this case the columns). The View window shows the view of the selected chart.
10. On the third and fourth panels, you can clarify the details. appearance charts and legends.
    As a result, we got a pie chart that gives a visual representation of the “contribution” of each device to the price of a computer.
    In spreadsheets, you can search for data (strings) in accordance with specified conditions. Such conditions are called filter... The search will find those strings that match the specified filter. Before starting the search, you must select at least one cell with data. For example, let's find all lines that contain information about devices worth more than $ 100.
11. Enter the command [Data-Filter-AutoFilter]. Drop-down lists containing standard search terms appear in the table column names. Expand the list in the Price column and select Condition… to enter custom conditions.
12. On the dialog panel Custom autofilter in the fields, enter the operator of the search term greater than and the value 100.
13. As a result, two rows will be found that match the specified filter. (Look in the table for devices that contain the word "Disk drive".)
14. Save the table with data and the diagram on the device price sheet.

5. Tabulation and graphing of functions in ET EXCEL.

The function Y (x) = x 2 -3x + 2 is given. Required:

Calculate the values ​​of the function Y for the values ​​of the argument x = -5; -4.5; -4;… 7; 7.5; 8;

Plot the function Y (x) using EXCEL and determine (approximately) at which values ​​of x the value of the function Y (x) is 0.

Problem solving technology

What to do? How to do? 1. Enter column headings In cell A1: “x”. To cell B1: “f (x)” 2. Enter the initial data In A2: number –5. В В2: formula = А2 ^ 2 / 2-3 * A2 + 2 3.Using autocomplete, fill in column A with the values ​​of the variable x Place the cursor on cell A2; Execute Edit / Fill / Progression Please select By columns; Arithmetic; Please select Step: 0.5; Limit value: 8 4. Compute f (x) for x values Copy the contents of cell B2 to cells B3: B28 5. Graph the function f (x) using the Chart Wizard. Highlight block A1: B28; call the Diagram Wizard; set aside space for the schedule; select: XY - point, 6 type

Cell- an indivisible element of the table that contains data.

The structure of a cell in an Excel spreadsheet has several levels:

1. The top level is the image of the contents of the cell, which is displayed on the screen. In it, we see formatted values ​​such as text, numbers, or the results of calculating formulas.

2. The second level contains hidden formatting. It contains service information about the contents of the cell, which is not displayed in any way, but is used in Excel when working.

3. The third level contains the formula. Depending on which options are set, you can see either the value calculated by the formula, or the formula itself.

4. The fourth level contains the cell name.

5. The fifth level contains commentary.

Cell address- determined by the column name and line number that form the intersection (for example A1).

Cell block- a group of sequential cells. A block of cells can consist of one cell, row, column, sequence of parts of rows and columns (for example, B2: C5).

Worksheet- contains a separate spreadsheet.

Workbook- a set of worksheets.

2. Data types

Character (text) data are descriptive. May include letters, numbers, punctuation, etc. To explicitly indicate character data, precede it with an apostrophe " (for example, writing "132 - the spreadsheet will be treated as text).

If the entered text is longer than the column size, then it will be shown in the following columns, if they are empty. Otherwise, the text will be clipped by the border of the next column.

Numeric data contain only numerical values. If the entered number is larger than the size of the cell, then it not displayed in a neighboring empty cell, and it is automatically converted to an exponential form (1.6E + 5) or the cell is clogged with signs - # .

A comma (12,1999) is used as the decimal separator.

Formula contains a record that may include arithmetic, logical and other operations performed with the data of other cells (= A1 + B1). Formula always starts with a sign equals =.

Function- a small subroutine that calculates data using several formulas (for example, the AVERAGE (B2: D5) function first calculates the sum of these cells B2: D5, and then divides the resulting sum by the total number of cells). Function recording always begins with the symbol @ .

Dates- numeric data displaying days, months, years (for example, 12.1999 - December 1999).

3. Cell format

To format cells, you must run the command Format - Cells... The result of your actions will be the appearance of a dialog box:

Rice. 1. Dialog box Cell format.

Bookmark Number allows you to select the following formats:

General- used to display text and numeric values ​​of arbitrary type.

Numerical- displays numbers in normal form with a specified precision (i.e., the number of digits after the decimal point is specified).

Monetary- displays numbers in monetary form, i.e. with indication of the monetary unit (125.56 rubles).

Financial- is used to align monetary values ​​by the separator of the integer and fractional parts.

date- displays numeric values ​​in date format. Those. if you enter the number 366, then in date format it will be 12/30/1900. Date can be displayed

In numerical form (12.04.99),

With the indication of the month and year (Apr 99),

· Indicating the day, month, year and time (12.04.99 14:40).

Time- displays numeric values ​​in time format. When specifying time, an abbreviation can be used PM, which means that the time is indicated after lunch (5:00 PM - 17:00).

Percentage- displays numbers as a percentage. When entering interest, keep in mind that interest is determined as a fraction of a unit. Those. in order to enter 85%, you must enter the number 0.85.

Fractional- displays non-whole numbers as fractions. Those. if you enter the number 0.25, it will be displayed as 1/4.

Exponential- displays numbers in exponential form. Those. all numbers are displayed in the format:

where M is a number with an integral part in the form of one digit;

E - decimal exponent pointer,

P is the order of the exponent.

For example, the number 245 will be displayed as 2.45E + 02

Text- displays numbers in text format. Actions cannot be performed on such numbers.

Additional- designed to work with addresses, phone numbers, etc.

4. Formulas and functions

4.1 Using formulas

Distinguish between arithmetic and logical formulas. V arithmetic The following operators are used in the formulas:

+ - addition;

- - subtraction;

* - multiplication;

/ - division;

^ - exponentiation

Boolean formulas use comparison operators:

= - equals;

<> - not equal;

< - less;

> - more;

<= - less or equal;

>= - more or equal.

4.2 Using functions

The spreadsheet contains the following kinds of functions:

· mathematical- perform complex mathematical operations (calculation of algorithms, trigonometric functions, etc.);

· statistical- perform operations to calculate the parameters of random variables or their distributions;

· text- perform operations on text strings, for example, calculate the length of a string, perform text replacement operations;

· brain teaser- are used to build logical expressions;

· financial- are used in complex financial calculations, for example, to determine the amount of monthly payments to repay a loan;

· date and time- are used for operations with dates and times, for example, find the number of days between two dates;

· working with the database- apply if the table is used as a database;

· checking properties and values- are used to check the data entered in the cells.

4.3 Relative and absolute addressing

Relative addressing is set for cells in formulas and functions by default and is the usual cell address, for example, B2. If relative cell addresses are used in a formula or function, then when such a formula is copied to other cells, the cell addresses will automatically change:

This addressing is convenient if you need to perform the same type of operation for several consecutive cells. However, relative addressing can be inconvenient if the values ​​are located only in specific cells. Then, when copying, it is not required to automatically change the cell addresses. For this, the formulas use absolute cell addressing.

Absolute address cells - this is the cell address, which cannot be changed in the process of copying or transferring a formula. To indicate an absolute address, enter the symbol $ ... There are two types of absolute addressing: full and partial:

· Full absolute addressing is indicated if, when copying or moving, the cell address should not change completely. To do this, the $ character is placed before the column name and row number $ A $ 1 + $ B $ 1.

Partial absolute addressing is specified if copying and moving does not change only the row number or column name A $ 1 + B $ 1

Note... If produced copying formulas and cell addresses are specified relative, then the addresses will be automatically changed. If produced carryover formulas and addresses of cells are specified relative, then the automatic change of addresses does not occur. However, if not only the formula is transferred, but also the data for the calculation, then the addresses are automatically changed.

5. General principles work

5.1 Entering and Editing Data

For input data, it is necessary to place the cursor on the required cell and enter the data from the keyboard. Data entry ends by pressing the key .

For editing the contents of the cell, place the cursor on the required cell and press the key or double click with the mouse. Data editing also ends by pressing the key .

5.2 Building diagrams

The following basic types of charts can be built in an Excel spreadsheet:

· Pie chart- used for graphic interpretation one variable. The values ​​of this variable are represented in the diagram by the slices of the circle. It can be located on a plane and in space.

· Schedule- depicts each variable as a broken line or plane (in space).

· bar graph- displays each variable as a column. It can be located on a plane and in space.

· Ruled- similar to the histogram, but located vertically. It can be located on a plane and in space.

· With areas- displays each variable as an area.

· Petal- Uses as many axes as there are data values ​​for plotting.

For plotting a diagram you need to do the following:

1. Select data. If the data is not in adjacent columns, then you can use the key to select .

2. Execute the command Insert - Chart.

3. Select the type of chart and the type of chart.

4. If necessary, change the range of cells with data.

5. Enter the attributes of the chart (the name of the axes and the chart itself, legend, etc.).

6. Indicate the location of the diagram (on the same sheet or on a new one).

7. Press the button Ready.

5.3 Working with macros

Macro- a recorded sequence of actions or commands, saved under a specific name for repeated use. Macros are an effective way to automate tedious, repetitive operations.

A macro can be created manually using commands in the Visual Basic programming language, or recorded using a macro recorder that converts a sequence of user actions into programming language code.

To create a macro, follow these steps:

1. Execute the command Service - Macro - Start Recording.

2. Specify the name of the macro and set the keyboard shortcut for calling the macro.

3. Perform actions to be performed by the macro.

4. Press the button to end recording. Stop recording on the toolbar.