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 positions 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 functions for creating business graphics have been significantly expanded. 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. 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 when copying a formula, the reference to a cell does not change, then there is the possibility of specifying 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:

· · address

· Content

· · 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 for quickly navigating 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:

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 , 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

Concept, purpose and capabilities of spreadsheets

A spreadsheet is a computer equivalent of an ordinary table, in the cells of which data of various types are recorded: texts, dates, formulas, numbers. To manage the spreadsheet, a special set of programs is used - a spreadsheet processor. Spreadsheets are widely used not only in economic and accounting calculations, the scope of their application is much wider.

Purpose of spreadsheets

· Preparation of tabular documents, processing of summaries, drawing up registers and other documents;

· Analysis using data search and sorting mechanisms;

· Carrying out of the same type of calculations on large data sets;

· Creation of databases;

· Automation of the computation process;

· Construction of diagrams and graphs on the basis of available data and other work.

Spreadsheet capabilities

· Multivariance of operations, that is, information processing in several ways. The user's task is to choose the most convenient one.

· Quick access to the most frequently used processing functions, concentrated in the context menu. Access to the most frequently used commands using the iconic menu located below the main menu bar.

· Availability of various means with which the user can design and modify the screen and tables. For example, split the screen into several windows, fix the headers of rows and tables, create templates for worksheets used to create letterheads, faxes, and various corporate documents;

· The presence of built-in functions for working with databases, lists, dates and time values; functions for engineering, mathematical, textual, financial, logical and other calculations.

· Construction of various types of diagrams and graphs.

Concepts and definitions of the MS-EXCEL program

The following terms are used when working with a spreadsheet:

· Workbook;

· table;

· Table cell;

· Table row;

· Table column.

Workbook is the main object of the spreadsheet. A table consists of worksheets, a sheet can contain one or more tables. The number of sheets in the workbook can be any, by default there are usually three of them. Sheets can be created, deleted, moved, renamed, etc. The sheets of the book are numbered with numbers, but they can be given any names. For example, Sheet 1 might be named Group 1 and Sheet 2 might be named Group 2.

The way the workbook and sheets are displayed on the screen may vary. In this case, the book may or may not be divided into separate sheets. To change the presentation form of the table workspace, select the commands View, Page layout. Each sheet in the workbook contains 256 columns and 65,536 rows. The number of columns and rows in the table can be changed. A separate table can be created on each sheet of the book.

Spreadsheet is a document to be created that consists of rows and columns.

Table cell is an element located at the intersection of rows and columns. Each cell is characterized by its own parameters - address, content, value, format. Numbers, text, formulas can be stored in table cells.

The cell we are working with at the moment is called active. It is highlighted with a bold frame. To activate a cell, click on it with the mouse.

Line is a horizontal row of table cells.

Column are cells arranged vertically. In the table, the rows and columns are numbered. The cell address is indicated by by specifying the line number and column number, at the intersection of which it is located. For example, cell B5. When referring to another sheet, the sheet name is additionally indicated. So, the entry Sheet2! A1 means a reference to cell A1 of sheet 2.

The columns of the table are named in Latin letters (column heading). Columns one through twenty-six are designated by letters from A to Z, then combinations of two letters AA, AB, … , BA, BB, etc. Rows of the table are denoted by numbers (row header).

Each individual cell has its own address: it consists of the column number and the row to which it belongs. Cell names are formed by the column letter followed by the row number. The address of each cell is in the upper left corner of the program window to the left of the formula bar in the name field.

Default table cell parameters:

· Cell width is equal to 9 digits;

· Character data is left aligned;

· Digital data - on the right edge.

Data types in the MS-EXCEL table

Each of the Excel cells can be filled with different types of data: text, numerical values, even graphics. In order for the information you entered to be processed correctly, it is necessary to assign a specific format to a cell (or more often to an entire column or row). This operation, like many others, can be performed using the Context menu of a cell or a selected fragment of a table. Click on the required cell with the right mouse button and select the desired item from the menu Cell format.

· General - these cells can contain both text and numeric information.

· Numeric - for digital information.

· Monetary - to reflect monetary values ​​in a predetermined currency.

· Financial - to display monetary values ​​with alignment by separator and fractional part.

Percentage

· Fractional.

Exponential

Text

· Optional - this format is used when compiling a small database or list of addresses for entering postal codes, telephone numbers, personnel numbers.

At the same time, the amount of information that can be entered into the cells of the Excel working field is not limited to the visible area: columns and rows can be stretched. Each cell can contain up to 65,000 text or numeric characters in any format. Excel interprets the entered numeric data as values ​​and can perform calculations with them. Values ​​can be displayed in numeric format, date format, time format, and others. Excel presents numbers that are too large or too small in exponential form. For example, instead of 48,900,000, 4.89E + 07 will appear in the cell. The precision of the number (the number of decimal places) can be adjusted using the buttons on the Formatting toolbar, that is, apply a special number format. By default, numbers appear in General numeric format. Excel interprets entered text data as text (sometimes called text boxes). Excel cannot perform calculations on text data. If the text entered into a cell exceeds the visible column width in length, 2 display options are possible:

· The next cells in the row are empty - then the text will visually cover these cells.

· In the next cells there is data - then the right part of the text will be hidden behind these cells.

There is also a third type of data - formulas. Excel interprets this type of information as commands that instruct you to calculate the values ​​in a cell or group of cells. Formulas differ from other data in that they always begin with an "=" (equal) sign. The formula is displayed only in the formula bar, while the cell displays only the result of its execution.

A text type is a sequence composed of any 256 characters of the character code table. Features of the text data type:

· By default it is aligned to the left border of the cell;

· If the number of characters of the entered value is greater than the width of the cell, then it occupies the adjacent cell to the right, if this cell is empty. If the cell is not empty, then the part of the value that is not visible on the screen is located under the nonblank cell to the right. To see all the contents of a cell, the column must be expanded.

The numeric type is numbers and some accompanying characters. The whole and fractional parts of the number are separated from each other by a comma. Enter numbers using the numeric keys on the right side of the keyboard. If a date appears when entering a number instead of a number, you must run the commands Edit, Clear, All, and then enter the number correctly.

Features of the numeric data type:

· By default it is aligned to the right border of the cell;

If the width of the cell is not enough to display the whole number, then symbols appear in the cell ### ... To see the entire number, the column needs to be expanded.

Numeric data in a cell can be displayed in various formats - monetary, financial, percentage, fractional, exponential, text, additional.

Let's enter the number 12345.67 in cell A1. Activating commands Format, Cells. In the appeared Format Cells window, activate the Number tab and view all possible representations.

The date and time type is a special case of this numeric type.

When you enter a date and time, the program converts them to a special number representing the number of days elapsed from the beginning of the century to the entered date. The entered date (for example, March 3, 2003) can be presented in one of the following formats:

After fixing the date in the cell, a dot is set as a separator between the day, month and year. The entry will look like this:

or 12.Mar.03.

Features of the data type "date":

· As a separator here, not a comma is used, but other characters - period, space, slash. With this input, the datum is automatically recognized as datatype. date and the cell into which this data is entered is assigned the format Dates;

If, after entering the date in the cell (we will enter the date in cell A1), 12.03) assign it General or Number format, then the screen will display the number of days from 01/01/1900 to the day of the entered date.

The entered time can have the following formats:

Formula type is data that is represented as a formula. In a cell with a formula, not the formula itself is visible, but the value calculated by this formula, depending on the values ​​in other cells of the table. An explicit feature of Formula Data is the "=" symbol.

To familiarize yourself with the peculiarities of entering data of a formula type, you should activate the commands: Accessing Help, Creating Formulas and Checking Books, Entering Formulas, Calculating Values ​​in Formulas .

One of the operands of the formula can be a Sheet function or built-in function. A built-in function is a program for calculating a predefined algorithm. Such functions implement calculations according to the most common algorithms.

usually bundled into a library of built-in functions. To inscribe an inline function into the active cell, use the control - Function wizard.

To work with Function Wizard necessary:

On the toolbar, click the button Function insertion (f =);

In the window that appears Feature Wizard Step 1 of 2 in the left area Categories select the line Mathematical in the right area Functions we will sequentially highlight the names of the functions, paying attention to the comments that appear.

For the remaining rows from the Categories area, similar actions are performed.

As practice has shown, solving many problems of an economic nature in high-level languages ​​using the entire arsenal of techniques and methods of professional programming is a complex and cumbersome business. A fundamentally different approach was required, and it was found and implemented in the form of spreadsheets - a tool available to non-professionals. The main field of application of spreadsheets is those spheres of human activity where information is provided in the form of rectangular tables (planning and financial and accounting documents, accounting of material values, etc.), which require mathematical calculations during processing, from which, apparently, arose the term "table processor". Note that relational databases, also represented by tables, are generally not designed for calculations.

Currently, many variants of spreadsheets are known: ABAK, Varitab-86. Superplan, Multiplan, SuperCalk, QuattroPro, Excel, Lotus 1-2-3, etc. Basically they all represent a spreadsheet processor and differ only in interface and service capabilities.

A spreadsheet (ET) is a rectangular matrix consisting of cells, each of which has its own number, Fig. 1.

Rice. 1.

The cell number is determined in the usual coordinate way, for example, cell B3, etc.

A group of cells (range) is specified with a colon, for example, B3: D4 (or B3..D4) and forms a rectangle that includes cells ВЗ, СЗ, D3, B4, C4, D4.

Each of the cells can be entered number, formula(arithmetic expression) or text. If a formula is written in the ET cell, then in the initial state the value of this formula is displayed on the screen, and not it itself. The operands of the formula can be mathematical functions, constants, cell numbers (the contents of the cell with the specified number). The ET cell has a complex "multilayer" structure, it can contain a reference to another cell, the value of which is the result of calculations by another formula, etc.

Examples of functions:

sum (A2: A8) - the sum of the values ​​of all cells from A2 to A8;

sin (D5) - sine of a number from cell D5;

cos (F3) - cosine of the number from cell F3.

Example formula:

2.7. * A6 + cos (sum (D5: F7))

The above formula means that we want to get the result of the following calculations: add the product of the number from cell A6 by 2.7 with the cosine of the angle, which is the sum of the numbers from cells D5, E5, F5, D6, E6, F6, D7, E7, F7.

Data included in tables can be automatically presented in the form of graphs, charts, bar charts, etc.

The user works in a dialogue with a special program that allows him to fill the cells with the content he needs (texts, numbers or formulas for calculations); clear them, copy and delete, sort (i.e. arrange cells, as well as rows and columns from them, in a certain order); perform calculations on the entire table or part of it, save the table on disk and print part or all of it on paper, etc.

Let's give an example to illustrate the capabilities of ET.

Formation of the payroll.

This is how the initial data for filling the spreadsheet looks like:

Here we are dealing with three types of cell content: text, number, formula. Input of initial data occurs on the command line. After filling in the table, we will see on the screen:

Usually, an accounting employee, having corrected one of the numbers, had to correct the entire set of interrelated documents, which explicitly or implicitly included the corrected parameter. With the help of ET, such a change can be taken into account instantly and everywhere.

One of the most productive ideas in the field of computer information technology has become 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 column name and the row 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, but with their help it is possible 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, and Excel 7.0 and 97 are for Windows 95/98. There is Excel-2000, part of the Office-2000 software package, which runs on Windows-2000. The older the version number of Excel is, the more perfect it is.

Excel is one of 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 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. In any cell, you can enter initial data - 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 "rubbery". 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

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 the 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 you enter data, three buttons appear on the left side of the formula bar that are 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 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 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 an 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 value of the cell address.

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, entered the formula = $ A5 + B $ 5. Then this formula was copied into 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, convert 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 row (in this case the columns). The View window shows the view of the selected chart.
10. In the third and fourth panels, you can refine the details of the appearance of the chart and legend.
    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 the 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 Y function 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