How to edit data in ms excel cells. Excel Spreadsheet Editor

Excel allows you to enter three types of data into cells: numbers, text, formulas. Text can be used for table headings, explanations, or notes on a worksheet. If Excel does not recognize the data type as numeric or as a formula, then the data is treated as text.

Numbers are used to represent digital information and can be entered in various formats: general, currency, financial, percentage, etc. Date and time can also be treated as numbers.

Formulas entered into a cell perform calculations, manage database operations, check cell properties and values, and are used to establish relationships between cells and arrays using address references.

Any formula begins with a sign (=). If a formula is entered into a cell, then by default the cell will show the result of the calculation.

Entering data into a cell

Data is typed directly into the active cell and displayed in the formula bar. Data can also be entered in the formula bar.

Data input

1. Click on a cell.
2. Enter data, for example Report for 2001.
3. Press key Enter.
4. To cancel an entry, press Esc.

While typing in the formula bar, click the button Enter- √ to confirm, and to cancel the entry, press the button Cancel - ×.

Pressing the cursor keys or clicking on another cell will always cause the entered data to be stored in the active cell before moving to the next one.

Text that is too wide to fit in the current cell will visually overlap adjacent cells when it is actually contained in one cell. Excel limits text or formulas in a cell to 255 characters.

Numbers that are too large to be displayed inside the current cell will be displayed as a sequence of characters # # # #. To show a numeric value in a cell, you need to increase the width of the column (see the "Formatting Cells" section).

You can delete the contents of a cell using the key Delete.

Cell selection

Many operations, such as inserting rows or columns, deleting, copying, or moving cells, require one or more cells to be selected before the operation can begin.

The selection area can be either a separate cell or occupy an entire workbook. The active cell is always part of the selection. The selection area must be rectangular and can be defined as:

• one or more cells;
• one or more columns;
• one or more lines;
• one or more worksheets.

Tab. 17 illustrates some of the possible combinations.

Table 17. Designations of table areas

Cells can be selected using the mouse or keyboard, or a combination of both (Table 18). The selected cells will differ in color.

Table 18. Selection of areas of the table

To deselect, click on any cell. To select an area with the mouse if the selection area exceeds the size of the window, as in other applications, autoscroll is used. This means that if the mouse pointer goes outside the window, the sheet will automatically scroll in that direction.

Editing cell content

Editing the contents of a cell can be done either in the cell or in the formula bar. The Input and Edit modes are shown in the status bar.

To edit, double-click in the cell you want to edit, or click in the formula bar.

The mouse pointer can be used to move to the editing location. In addition, in edit mode, you can use following keys:

Just like in Word, pressing the Insert key toggles between insert and overwrite modes.

Undo and redo actions

Excel allows you to undo changes made in a workbook. Although this feature is applicable to most commands, there are exceptions for it (for example, you cannot undo deleting and renaming a sheet).

Command Undo from the Edit menu context dependent. When the user types or edits data in the formula bar, the menu Edit the command corresponding to the last operation performed will be offered.

On the standard panel to undo the last teams you should press the button or cancel several commands by selecting them from the list.

After team selection Undo in the Edit menu the command will change to the Redo command.

Insert rows and columns

Additional rows or columns can be inserted as needed anywhere in the table. Command Insert (Insert) in the Edit menu (Edit) can be used to insert a new column left from current column or new row above current line.

Multiple columns and rows can be added when selecting an area that includes more than one column or row.

1. Select as many columns or rows as you want to insert.
2. Select Insert, Rows (Insert, Rows) or Insert, Columns (Insert, Columns) or press the key combination ctrl and + on the numeric keypad.

To remove rows or columns:

1. Select the rows or columns to delete.
2. Select Edit, Delete (Edit, Delete) or press the key combination Ctrl and - on the numeric keypad.

Inserting and deleting columns or rows shifts the addresses of the remaining data in the table, so you need to be especially careful when inserting or deleting.

Moving and copying data

Moving and copying data is one of the main operations used when working with tabular data, and not only the contents of the cells are copied to a new location, but also their formatting.

Moving and copying the contents of cells can be done in two ways:

• menu commands Edit (Edit);
• dragging with the mouse.

As soon as the user selects the cell and selects the command Cut or Copy (Cut or Copy) on the menu Edit Excel will copy the contents of the cell to the clipboard.

When moved, the data of the original cells will be pasted to the new location.

Data copying is used to duplicate information. Once the content of one cell is copied, it can be pasted into a single cell or into an area of ​​cells multiple times. In addition, the selected area is surrounded by a movable dotted border that will remain until the operation is completed or canceled.

The border looks like a pulsating dotted frame that surrounds the selected object. Pasting the contents of cells is only possible when this border exists.

Command usage Paste in the Edit menu after selecting the command Cut (Cut) will turn off the border.

Command usage Paste (Paste) after the command Copy (Copy) will not disable the border, i.e. the user can continue to specify other destinations for inserting data and use the command Insert again.

Keystroke Enter will paste the selection at the new location specified by the mouse and turn off the border.

Keystroke Esc will cancel the copy-to-buffer operation and disable the border.

When pasting data from more than one cell, you only need to specify the upper left corner of the area of ​​cells on the worksheet that you are pasting into.

Moving and Copying Using the Menu

Possibility Drag and Drop allows you to move or copy the contents of selected cells with the mouse. This feature is especially useful when moving and copying to short distances(within the visible area of ​​the worksheet).

Move and copy by drag and drop

1. Select an area of ​​cells to move.
2. Move the mouse pointer over the selection frame.
3. Drag the selection with the pointer to a new location. The cell area will be moved to a new place.
4. If you hold down the key while dragging ctrl, the cell area will be copied to the new location.

Special data copy

The special copying of data between files includes the command Special paste (Paste Special) in the Edit menu (Edit). Unlike the usual copying of data using the Paste command, the command can be used for calculations and transformation of information, as well as for linking workbook data (these possibilities will be discussed in the next chapter).

Command Special insert(Piste Special) often used to copy cell formatting attributes.

1. Select the cell or cells to copy.
2. Select the cell or cells in which the source data will be placed.
3. Select Editing, Paste Special (Edit, Paste Special). The Paste Special dialog box contains several options for pasting data (Figure 83).

Rice. 83. Special insert

1. Set the required options, such as formats (colouring formats only changes the formatting, not the value of the cells).
2. Select OK.

First group of dialog box options Paste Special allows you to select the content or formatting attributes to be inserted. Selecting All (AI) pastes the contents and attributes of each copied cell in a new location. Other options allow you to insert different combinations of content and/or attributes.

The second group of parameters is used only when inserting formulas or values ​​and describes the operations performed on the information being inserted into cells that already contain data (Table 19).

Table 19. Paste Special command options

Choice None means that the copied information simply replaces the contents of the cells. Choosing other options for operations, we get that the current content will be combined with the inserted information and the result of such a combination will be the new contents of the cells.

The exercise

Performing calculations with the Paste Special command

Enter the data as shown in the table. twenty.

Table 20. Initial data

1. Select the area to copy A2:A4.
2. Select Edit, Copy (Edit, Copy).
3. Click cell B2 (upper left corner of the area where the data will be placed).
4. Select Edit, Paste Special (Edit, Paste Special).
5. Set the Multiply option.
6. Click OK. Note that the border of the selection area remains on the screen.
7. Click cell C2, which will be the start of the insertion area.
8. Select Editing, Paste Special (Edit, Paste Special) and set the Transpose option.
9. Copy your own formats of column G to column H and get a table. 21.

Table 21. Result of the Paste Special command

Topic 2.3. Presentation software and basic office programming

Topic 2.4. Database management systems and expert systems

2.4.11. Training database with main button form "Training_students" - Download

application program excel

2.2. Spreadsheet processors

2.2.2. Editing and formatting Microsoft Excel worksheets

Any processing of information begins with its input into a computer. You can enter text, numbers, dates, times, serial data series, and formulas into MS Excel spreadsheets.

Data entry is carried out in three stages:

• cell selection;
• data input;
• confirmation of input (press the Enter key).

After the data is entered, it must be presented on the screen in a specific format. There are different categories of format codes for representing data in MS Excel.

To edit data in a cell, double-click on the cell and edit or correct the data.

Editing operations include:

• delete and insert rows, columns, cells and sheets;
• copying and moving cells and blocks of cells;
• editing text and numbers in cells.

Formatting operations include:

• changing number formats or the way numbers are represented;
• changing column widths;
• alignment of text and numbers in cells;
• change font and color;
• Selecting the type and color of the border;
• Filling cells.

Entering numbers and text

Any information that is processed on a computer can be represented in the form of numbers or text. By default, Excel enters numbers and text in the General format.

Entering text

Text is any sequence of characters entered into a cell that cannot be interpreted by Excel as a number, formula, date, or time of day. The entered text is left-aligned in the cell.

To enter text, select a cell and type text using the keyboard. A cell can hold up to 255 characters. If you want to enter some numbers as text, then select the cells, and then select the Format / Cells command. Next, select the “Number” tab and select Text from the list of formats that appears. Another way to enter a number as text is to type an apostrophe before the number.

If the text does not fit in a cell, then you need to increase the width of the column or enable word wrapping (Format / Cells, Alignment tab).

Entering numbers

Numeric data are numeric constants: 0 - 9, +, -, /,*, E, %, dot and comma. When working with numbers, you must be able to change the type of entered numbers: the number of decimal places, the type of the integer part, the order and sign of the number.

Excel independently determines whether the entered information refers to a number. If the characters entered into the cell refer to text, then after confirming the entry into the cell, they are aligned to the left edge of the cell, and if the characters form a number, then to the right edge of the cell.

Entering consecutive data series

To create data series, do the following:

1. Enter the first member of the series into the cell.
2. Select the area where the row will be located. To do this, move the mouse pointer to the fill marker, and at this moment, when the white cross turns into black, press the left mouse button. Further, holding down the mouse button, you need to select the desired part of the row or column. After you release the mouse button, the selected area will be filled with data.

Data format

Data in MS Excel is displayed in a specific format. By default, information is displayed in the General format. You can change the presentation format of information in selected cells. To do this, run the Format / Cells command.

The "Format Cells" dialog box will appear, in which you need to select the "Number" tab. In the left part of the "Cell Format" dialog box, the "Number Format" list shows the names of all the formats used in Excel.

Rice. one.

For the format of each category, a list of its codes is given. In the right window "Type" you can view all the format codes that are used to display information on the screen. You can use MS Excel's built-in format codes to represent the data, or enter your own (custom) format code. To enter the format code, select the line (all formats) and enter the format code characters in the “Type” input field.

Data presentation style

One way to organize data in Excel is to introduce a style. To create a style, use the Format / Style command. Executing this command opens the "Style" dialog box.

WORKING WITH SPREADSHEET

In MS EXCEL 2007

Guidelines

for laboratory work

in the discipline "Informatics"

for full-time students

directions 38.03.01 "Economics"

training profile

"Accounting, analysis and audit"

Sevastopol

METHODOLOGICAL INSTRUCTIONS for performing laboratory work « WORKING WITH ELECTRONIC TABLES IN MS EXCEL 2007" in the discipline "Informatics" for full-time students in the direction 38.03.01 "Economics" training profile "Accounting, analysis and audit" / Comp. Art. lecturer of the IT&CS department M.A.Lebedeva. - Sevastopol: Publishing House of SGU, 2014. - 43 p.

The purpose of the guidelines is to assist students in performing laboratory work in the discipline "Computer Science".

Theoretical information necessary for the performance of laboratory work, options for tasks, recommendations for implementation, requirements for the design of work are given.

Guidelines reviewed and approved at a meeting of the department Information technologies And computer systems, Protocol No. 2 dated October 14, 2015.

Reviewer: S.N.Fisun, Associate Professor of the Department of Information Technologies and Computer Systems

INTRODUCTION 4

1 LABORATORY WORK №1. ENTERING AND EDITING DATA IN MS EXCEL. 4

2 LABORATORY WORK №2. FORMATTING TABLES. USING TABLE FUNCTIONS.. 10

3 LABORATORY WORK №3. BUILDING CHARTS.. 21

4 LABORATORY WORK №4. PROCESSING LISTS.. 27

5 LABORATORY WORK №5. DATA ANALYSIS.. 33

6 LABORATORY WORK №6. CREATING MACROS.. 39

REFERENCES.. 43

INTRODUCTION

Microsoft program Office Excel 2007 has efficient tools for processing numerical information presented in the form of spreadsheets. It allows you to perform mathematical, financial and statistical calculations, draw up reports based on tables, display numerical information in the form of graphs and charts.

In the guidelines for laboratory work the basic methods of working in the MS Excel package are outlined, the procedure and rules for creating and editing spreadsheets and diagrams, performing basic calculations, sorting and filtering data, analyzing and summarizing data are described.

LAB #1

ENTERING AND EDITING DATA IN MS EXCEL

1.1 Purpose of work– learn how to enter constants and formulas into worksheet cells, gain skills in building tables using examples of basic editing commands.

Theoretical information

Entering data into a worksheet

into cells spreadsheet you can enter constants (numeric, text, logical, date-time) and formulas. Data is entered into the cell highlighted by the table cursor (the current cell). Its address is given by the numbers of the column and row at the intersection of which it is located, and is displayed in the field Name. Data entry is completed by pressing the Enter key or by clicking on another cell.

You can also enter data into a cell using a field Formula bars. To do this, first you need to make the required cell current, set the text cursor in the field Formula bars and type in the data on the keyboard.

Fig.5.1 - Excel fields

When entering numerical data, the following rules must be observed:

When entering negative numbers, you must enter a sign – or enclose the number in parentheses, for example –4 or ( 4 );

To separate groups of digits (classes), you can enter gaps, for example 1 000 000 ;

When you enter fractional numbers you must enter the integer part, separating it from the fractional part with a space. For example, the number ½ is entered as 0 1/2 Excel converts the number to 0.5 decimal, which is displayed in the field Formula bars, and the entered fractional numbers are displayed in the cell.

To indicate percentages, the symbol % is entered after the number;

To enter numbers in exponential form, use the Latin letter E (e), for example, 3003.4 \u003d 3.0034 10 3 \u003d 3.0034E + 3 .

Excel provides additional features to automate text entry. The program remembers the text data entered in the previous cells of the current column. And when you enter the first letters of such data in the next cells of this column, it automatically offers their full text.

Entering formulas

A formula in Excel is an expression that starts with an = sign and can contain numbers, text, cell references , action signs (operators), brackets and functions.

When entering formulas, the following rules must be observed:

To denote the action of raising to a power, the operator is used ^;

You cannot omit the multiplication operator;

Parentheses are used to change the order in which actions are performed;

To denote the action of finding interest, the operator is used %; for example, the formula for finding 25% of the number 134 will look like this: \u003d 134 ∙ 25%;

By default, after you enter a formula in a cell, the result of the calculation is displayed in that cell, and the entered formula is displayed in Formula bar.

You can use cell references in formulas. A cell reference consists of the address of the cell, to which is added an indication of its location if it is on another sheet. If a formula uses cell references, the formula uses data from those cells when it is calculated. When changing the data in these cells, the values ​​are automatically recalculated for all formulas that contain references to these cells.

If the content of the cell is a formula that contains a link, then when you copy the contents of this cell in the formula, automatic change links - modification of the formula: the numbers of columns (rows) in the links are changed by the difference between the numbers of the final and initial columns (rows). When moving formulas are not modified.

To ensure that the references in the formula are not modified when copying, the $ symbol is added before the row column number. Thus, the link B10 will be modified, but the link $B$10 will not. If in the link the $ symbol is added only before the column number or line number, for example $B10 or B$10, then when copying such links, they are partially modified: only the line or column number, which is not next to the $ symbol, changes.

data editing

Data editing can be done directly in a cell or in a field Formula lines. To edit the data in a cell, you can double-click the cell or make the cell current and press F2. To edit data in a field Formula bars you need to make the cell current, select the required place in the field Formula bars, edit, and then press the Enter key.

To delete data from a cell, you can make it current and press the key Delete. You can also do Home - Editing - Clear - Clear contents.

Performing operations of copying and moving data from a cell or a range of cells in a spreadsheet is carried out: using commands Copy, Cut, Paste groups Clipboard tabs home; using commands context menu objects; using key combinations; drag and drop.

If the contents of the cells being copied or moved are formulas, then when pasting from clipboard you can insert into the selected cells not the formulas themselves, but the values ​​calculated from them. To do this, you must perform Home ® Clipboard ® Paste ® Paste values.

You can insert data from clipboard into the selected cells so that they do not replace existing data, but are added to it (or subtracted from existing values, or multiplied by existing values, or existing data is divided by those that are inserted). For this you need to execute Home ® Clipboard ® Paste ® Paste Special and in the window Special insert that opens, in the group Operation select the appropriate radio button.

If you do Home ® Clipboard ® Paste ® Transpose, then when inserting, there will be a transposition of the data that is inserted from clipboard: data from columns will be in rows, and data from rows will be in columns. References in formulas when pasted this way also change accordingly.

Transposition can also be set by checking the checkbox Transpose in the window Special insert.

To insert new columns (rows) into the table, you need to select the columns (rows) before which you want to insert new ones, and execute Home - Cells - Insert - Insert rows (columns) on the sheet. If you select one column (row), then one column will be inserted before it. new column(row), and if you select several columns (rows) in a row, then as many columns (rows) as selected are inserted in front of them.

To insert multiple cells into a table:

Select the desired range of cells.

Run Home - Cells -Insert - Insert cells.

Select from the list of window radio buttons Adding Cells required

Select the OK button.

When choosing a switch cells with shift down the selected cells, along with all the cells that are below them in their columns, are shifted down, and new empty cells are inserted in their place.

Similarly, the insertion of new cells occurs when a radio button is selected. right-shifted cells.

You can insert new cells into the table immediately, along with their contents. For this you need:

Select the desired range of cells with data.

Run Home - Clipboard - Copy.

Select the upper left cell of the table range where new cells with the copied data will be inserted.

Run Home - Cells - Paste - Paste copied cells.

Select the desired switch from the list of window switches Paste copied cells.

Select the OK button.

Deleting columns, rows, individual cells and their ranges is similar to pasting. To perform these operations, you need to home - Cells - Delete - Delete cells (Delete rows (columns) from the sheet). When inserting or deleting spreadsheet fragments, formulas are modified in table fragments that are shifted. Thanks to this modification, all the results of calculations that were found before the insertion or deletion are not changed.

Job assignment

1.3.1 Create new e-book in Excel 2007. Rename Sheet1 to "Task 1". On this sheet, calculate the following expressions:

a) ;

b) 32% of the number;

Write down as an integer or a decimal fraction the numbers presented in exponential form: a) 2.15E + 03; b) 1.35E–02.

The task is performed on Sheet2. Variable value x is in the cell A1, variable value at- in a cell A2, variable value z- in a cell A3. Write down formulas for calculating expression values ​​in Excel 2007:

a) b) .

1.3.2 On sheet 3, fill in: a) cells of the range A2: A8 with members of an arithmetic progression with the first member 3 and step 2; b) cells of the range С3:С8 by members of a geometric progression with the first member 2 and the denominator 0.5.

Create a custom list of the names of eight subjects studied. Fill in the 8 cells of column D with these names.

1.3.4 In a new book, create a table containing the income of 5 firms selling computers (video equipment, cosmetics, etc.) for each month of the first half of the current year (example in Fig. 1.2). The first line contains the names of the months and is filled with a fill handle. In cell A1, enter "Firms".

Fig.1.2. General form tables "Income of firms selling computers in the 1st half of the current year"

Calculate the values ​​of the column "Total for half a year" using a formula that sums the values ​​of the cells. (=A1+.....). Duplicate the formula in a column using the fill handle.

Compute the totals for each firm for each month.

Perform data editing:

Clear the contents of the "only half a year" column . Calculate the values ​​of a column using the =SUM() function.

Add three empty rows at the top of the table.

Delete two empty lines.

Fill in the empty line with the text "Income of firms selling computers for the 1st half of 201_". Place the title in the center of the area (the button on the Center by Columns toolbar).

Copy the table to sheet 2 via the clipboard.

On sheet 2, using the commands from the Edit menu (Copy, Paste, Delete), swap the columns "Min. Income" and "Max. Income". Check the correctness of references in formulas. Perform the reverse operation of swapping columns using drag and drop

Objective; formulation of the problem; description of the tasks of section 1.3; Work conclusions.

1.5 test questions

1. List the commands used to enter and correct data. How can I change the value of a table cell?

2. List the ways to execute commands for copying, deleting, and pasting cells in a table. How to add (remove) rows and columns of a table.

3. How in excel formula different from the data. How can I place the text ==A== in a table cell.

4. What happens to a formula when it is copied?

LAB #2

Theoretical information

Numeric formats

Numeric formats are set in the window cell format, tab Number, list Numeric formats( fig.2.1)

Fig.2.1 - Number formats

General is the default format. It is used to represent numbers as they were entered. If the cell is not wide enough to display a number, it is automatically represented in exponential form.

Numerical used to represent a number as a decimal fraction with a specified number of decimal places set on the counter. In this format, you can set the separator of groups of digits as a gap between groups, as well as the representation of negative numbers: as a positive red number or in parentheses.

Monetary used to set the values ​​of the same properties as for the format Numerical, with the addition to the number of the designation of the monetary unit, which is selected from the list Designation.

date of used to represent a number as a date of a specific type

In the format Percentage data is represented by a number that is the result of multiplying the contents of the cell by 100, with a % sign

Text used to represent numbers in cells as text

Financial different from format Monetary by the fact that negative numbers are automatically represented in unsigned parentheses minus.

List (all formats) can be used to create your own format. You must select one of the existing formats from this list and make the necessary changes to it.

alignment

By default, numbers in the format Text aligned in the cell to the left, in all other formats - to the right. Texts are left-aligned by default.

To change property values horizontal alignment, vertical alignment, display, text direction, orientation numbers or text in cells you can use the group controls alignment tabs home on the Lente or tabs alignment window Cell format.

After setting the property value display- transpose by words text in a cell is displayed on multiple lines if its length is greater than the width of the cell. And after setting the value autowidth the content display mode is set, in which the font size is automatically reduced so that the content is completely displayed in the cell on one line.

Sometimes it is convenient to combine several cells that form a connected range into one cell. To do this, you need to select the cell and set the checkbox label. merging cells. After merging, all these cells will be considered as one cell, the address of which is the address of the top left of them. The data that was in the cells before the merging, except for the top left, will be lost during the merging. Therefore, it is advisable to merge the cells first, and then enter the data.

Property value orientation set or button selection Text, or by turning the slider Inscription, or by setting the angle of rotation in the field with a counter.

The above actions can be performed using group controls alignment tabs home on the Tape.

Borders, shading, protection

On the tab Border window Cell Format(Fig. 2.1), you can set the following values ​​for the properties of cell borders: the presence of all borders or only individual ones, the type and color of border lines.

Border property values ​​can also be set using the combo box Borders(its name and appearance changes depending on the last set value of this property) groups Font tabs home on the Tape.

Using tab controls fill window Cell Format or list button Fill color groups Font tabs home on the Lente, you can set the background color of the cell, the fill method, the pattern and its color.

On the tab Protection window Cell Format you can set or cancel modes cell protection And hiding formulas. Cell protection is set to protect data from unauthorized changes, and hiding formulas is set so that data is not displayed in Formula bar. To set the protection and hiding modes, check the corresponding checkboxes: Protected cell And Hide formulas, select the OK button, and then execute Peer review- Changes– Protect Sheet or home- cells- Format- Protect Sheet. In the window Sheet protection, which opens, you can set a password to remove the protection and hiding modes, as well as set permissions for certain operations when the modes are set.

Excel Functions

Excel 2007 has a built-in function library that includes more than 300 different functions. All of them for the convenience of searching are divided into groups (categories): mathematical, statistical, logical, financial, text and etc.

The function has name And result, there are functions with arguments And no arguments. The function argument can be a number, text (it must be enclosed in double quotes), an expression, a reference to a cell or a range of cells, the result of another function. When using a function in a formula, first its name is indicated, and then, if the function has arguments, it is indicated in brackets argument list through a semicolon. If the function has no arguments, then nothing is indicated in parentheses after the function name.

There are several ways to insert a function into a formula:

Use function list of function category button in group Function Library tabs Formulas on the Lente(fig.2.2);

Run Formulas - Function Library - Insert Function or select button ;

Type a function directly into a cell or field Formula lines;

Fig.2.2 - inserting a function

If you do Formulas - Function Library - Insert Function or select button Insert FunctionFormula Bars, then a window will open Function Wizard(fig.2.3).

Fig.2.3 - Function Wizard

In this window, in the list of fields Category you can select the desired category, then in the list of fields Select function select desired function. After selecting the OK button, a window opens. Function Arguments.If the function has a fixed number of arguments, then the window Function Arguments immediately contains the appropriate number of fields for their input. If the function has a non-fixed number of arguments, then several fields first appear in the window, and then, in the process of entering arguments, the following fields appear.

Math functions

Summation =SUM(numbers), numbers– a list of no more than 30 arguments, each of which is a number, a formula, or a reference to a cell containing a numeric value.

=SUMIF(interval; criterion; summed interval) - sums up the values ​​in the cells, the contents of which meet the specified criteria. 3rd argument - summed interval– optional, used to look up values ​​in a table. Constants can be used as a criterion, as well as relational operations: > ,< , >= , <=

rounding =ROUND( number; decimal places), number– rounded value; decimal places - integer specifies the rounding digit. Can be: >0 – rounding occurs in the fractional part of the number; =0 - the number is rounded up to an integer;<0 - округление происходит в целой части числа.

Work =PRODUCT(number1,number2...) multiplies all the numbers given by its arguments. Can have up to 30 arguments. Excel ignores any empty cells, text and boolean values.

Remainder of the division =MOD(number, divisor) If the number is exactly divisible by the divisor, the function returns 0. If the divisor is 0, the MOD function returns an erroneous value.

Root =ROOT(number). Argument number must be a positive number. If the number is negative, SQRT returns an error value.

Statistical functions

Used to process data.

=AVERAGE(numbers) - Calculates the arithmetic mean for a sequence of numbers. Ignores empty, boolean, text cells. Can contain up to 30 arguments.

=MAX(numbers) And =MIN(numbers) respectively returns the maximum and minimum values ​​for the given sequence of numbers.

=FASHION(numbers)—Returns the most frequently occurring value in a set of numbers.

=COUNT(interval) And =COUNTA(interval) calculates in a given interval the number of cells containing numbers, dates, formulas. COUNTA calculates the number of filled cells.

=COUNTIF(interval; criterion) counts the number of cells whose contents match the search criteria.

Logic functions

=AND, OR, NOT– function arguments are logical expressions; functions return values ​​of boolean constants: TRUE And FALSE. The NOT function has one argument: =NOT(true) returns FALSE. =NOT(B2>5) returns TRUE if cell B2 contains a value less than 5, otherwise FALSE. AND, OR can contain up to 30 arguments. Arguments of functions AND, OR, NOT can be logical functions. Used to build complex logical expressions.

=IF- is used when, depending on the value boolean expression, some calculations are performed. =IF (logical expression; meaning "true"; meaning "false"). Nested IF functions are used to construct complex conditions.

=IF(A1=100,"Always",IF(AND(A1>=80,A1<100);"Обычно";ЕСЛИ(И(А1>=60;A1<80);"Иногда";"Никогда")))

If the value in cell A1 is an integer, the formula reads: "If the value in cell A1 is 100, return the string 'Always'. Otherwise, if the value in cell A1 is between 80 and 100, return 'Normal'." otherwise, if the value in cell A1 is between 60 and 80, return the string “Sometimes.” And, if none of these conditions are met, return the string “Never.” In total, up to 7 levels of nesting of IF functions are allowed.

Using the IF function gives the formula the ability to "make decisions". Any logical expression must contain at least one comparison operator that defines the relationship between the elements of the logical expression: = Equal, > Greater than,< Меньше, >= Greater than or equal,<= Меньше или равно, <>Not equal. The result of a logical expression is the logical value TRUE (1) or the logical value FALSE (0).

Text functions

=DLSTR( text ) - returns the number of characters in a text string. The text argument must be a character string enclosed in double quotes or a cell reference.

=REPLACE(old_text; start_nom; number_of_characters; new_text) - replaces part of the string text with another text. Old_text- text in which some characters are replaced; start_nom- the position of the character in the text "old_text", starting from which the replacement will be made; num_characters- the number of characters in the text "old_text", which are replaced by new_text; new_text- text that replaces characters in the text "old_text"

=LEFT(text; number of characters) - returns the specified number of characters from the beginning of the text

=RIGHT(text; number of characters) - returns the specified number of characters from the end of the text string.

=CONCATENATE(text1; text2) - combines several text lines into one.

Date and time functions

= DATE( year; month; day) - returns the given date in Microsoft Excel numeric format.

=DATEVALUE( text ) - Converts a date from text to number format. An example of a text argument is “01/01/2015”.

=MONTH( date of ) - Converts a date in numeric format to months.

=TODAY() - Returns the current date.

=DAYWEEK( date of; [type] ) - - Converts a date in numeric format to a day of the week. type is an optional argument. An integer from 1 to 3 that specifies the counting format: 1 - the week starts on Sunday. Is the default value; 2 - the week starts on Monday; 3 - the week starts on Monday, and the countdown starts from zero.

=YEAR( date of ) - Finds the year for the given date.

Objective; formulation of the problem; description of the tasks of section 6.4; work conclusions.

2.6 Security questions

1. List the main types of formats.

2. Alignment of cell contents

3.How to change the height and width of cells?

5. Types of arguments, rules for writing arguments

LAB #3

BUILDING DIAGRAM

3.1 Purpose of work- obtaining practical skills in creating, editing, formatting charts.

Theoretical information

Create a chart

A chart is a graphical representation in which numerical data is presented in the form of geometric shapes. Charts in Excel 2007 are based on the data presented in the spreadsheet. There are 14 types of charts that you can build in Excel 2007. The most common types of charts are bar charts, pie charts, and graphs.

It is advisable to create histograms when you need to compare the values ​​of several data sets, graphically depict the differences in the values ​​of some data sets compared to others. Pie charts are recommended when you need to display parts of one whole, compare the ratio of parts and parts to the whole. It is advisable to use a graph if the amount of data in the set is large enough, or you need to display the dynamics of data changes over time, compare changes in several data series.

To build a chart, you need to select a range of cells with data on which the chart will be built. The selected range of cells can be either connected or disconnected. It is desirable that it includes labels for rows and columns.

After that you can:

Press the F11 key. As a result, a new sheet will be created, located in front of the sheet with the selected data, and a chart will be built on it, the type, appearance and values ​​of the properties of which are set by default.

Use controls on Tape:

1. Open in tab Insert in a group Diagrams a list of buttons of the desired chart type (Fig.3.1).

Fig.3.1 - Charts group of the Insert tab

2. Select the desired type of diagram.

Use window controls Insert diagrams:

1. Select on the tab Insert in a group Diagrams button to open the dialog box.

2. In the window Insert charts select the desired type and type of chart.

3. Select the OK button.

As a result, a chart of the selected type and type will be built on the sheet with the spreadsheet, the values ​​of the properties of the objects of which are set by default.

A temporary section will appear on the ribbon Working with charts with three temporary tabs with controls for working with the chart: Constructor, Layout and Format. tab Constructor automatically becomes current. On her in the group Layouts charts, you can select the desired chart layout, which determines the set of chart objects and their location, and in the group Chart styles- the style of the chart.

Sometimes you need to add new columns (rows) with data to a range of cells in a spreadsheet, for whose data a chart has already been built, or delete existing ones.

To change the range of cells that the chart is based on, you need to:

1. Select a diagram.

2. Run Constructor - Data - Select data, or use the context menu.

3. In the window Selecting a data source(fig.3.2) you can add or delete a row, as well as set labels on the horizontal axis using the button Change.

Fig.3.2 - Window "Select data source"

You can separately format the following chart elements: chart area; construction area; a series of data; axes; titles; data signatures; legend (the legend displays a list of the names of the data elements displayed on the chart and their corresponding colors).

A data series is a set of related data items displayed on a chart. Each series in the chart has a different color or labeling style indicated on the chart legend. Charts of all types except pie charts can contain multiple data series.

Categories are the values ​​of the independent variable on which the values ​​of the data series depend, usually plotted on the horizontal axis. For example, in the graph, the category values ​​are the values ​​of the function argument.

Tab is used for formatting. Layout.

The selected layout options will be applied to the selected element. For example, if the entire chart is selected, the data labels will be applied to the entire data sequence. If a single data point is selected, data labels will only be applied to the selected data sequence or the selected data point.

To format the axis, double-click and set the necessary parameters in the dialog box "Format Axis" (Fig. 3.3).

Figure 3.3 - Axis format

Plotting a Function

A function graph in Excel, like all other charts, is based on discrete values. To do this, you need to build a table containing an argument column and a function value column (one or more).

As an example, consider the procedure for constructing a graph of the function y=x 2 at XО[-0.5;0.5]. We choose the step of changing the argument (0,1). Let's enter the names of the fields - X and Y, respectively, into cells A1 and B1. In the range of cells A2:A12, enter the values ​​of the variable X: -0.5; -0.4; …; 0.5. The selected values ​​of the variable form an arithmetic progression, so let's use the autocomplete technique. In cell B2, enter the formula =A1^2. Copy the formula to cells B3:B12.

To plot a function, select a range of cells B1:B12 containing a table of function values y, select the command on the ribbon Insert - Graph(chart area).

On the x-axis, the values ​​of the argument from cells A2:A12 should be plotted. To do this, on the tab Constructor press the button Select data. In the window that appears, use the Change button, in the field Range labels of the X axis, enter the range A2:A12.

Fig.3.4. Function Graph

The name of the axes and charts are set using the tab Layout. Point markers can be set by selecting the command from the context menu Data point format.

If on the tab Constructor in a group Location click Move Chart, you can choose to place the chart on a separate sheet without displaying the table with data and series values.

To plot a function with one condition in cell B2, you need to insert a function If.

Example:

Enter the formula in cell B2: =IF(A2>=0,A2^2,A2^(1/3))

Surface construction

To construct a surface, the values ​​of one of the arguments must be placed in the first row, the values ​​of the other in the first column.

Consider an example of constructing a surface for X, zн[-1, 1]. Let the argument change step X And Y the same and equal to 0.2

In the range of cells B1:L1, enter the sequence of values: -1, -0.8, ..., 1 variable X, and in the range of cells A2:A12 - a sequence of values: -1, -0.8, ..., 1 variable Y. In cell B2, enter the formula for calculating Z:=$A2^2-B$1^2. Let's copy the formula, filling in the range B2:L12. When copying a formula, the references to the first column (A) and the first row (1) should not change. Therefore, they are preceded by a $ sign, which gives an absolute reference to column A and row 1 (mixed addressing). To type an absolute reference (for example, $A2), in the formula bar, position the cursor in front of the address A2 and double-click the F4 function key.

To build a surface, select a range of cells A1:L12 containing a table of function values ​​and its arguments, and select in the tab Insert - Others chart type Surface.

Assignment for laboratory work

3.4.1 Based on the table "Income of firms selling computers in the 1st half of the current year" build:

1) a histogram of the distribution of income of the company1 by months of the 1st quarter,

2) the schedule of income distribution of the firm2 for six months

3) a histogram of the income of all firms for

Data types

Excel allows you to enter three types of data into cells: numbers, text, formulas. Text can be used for table headings, explanations, or notes on a worksheet. If Excel does not recognize the data type as numeric or as a formula, then the data is treated as text.

Numbers are used to represent numerical information and can be entered in various formats: general, currency, financial, percentage, etc. Date and time can also be treated as numbers.

Formulas entered into a cell perform calculations, manage database operations, check cell properties and values, and are used to establish relationships between cells and arrays using address references.

Any formula starts with a sign ( = ). If a formula is entered into a cell, then by default the cell will show the result of the calculation.

Entering data into a cell

Data is typed directly into the active cell and displayed in the formula bar. Data can also be entered in the formula bar.

Data input

1. Click on a cell.
2. Enter data, for example "Report for 2001".
3. Press key Enter.
4. To cancel an entry, press Esc.

While typing in the formula bar, click the button Enter - √ to confirm, and to cancel the entry, press the button Cancel - ×.

Pressing the cursor keys or clicking on another cell will always cause the entered data to be stored in the active cell before moving to the next one.

Text that is too wide to fit in the current cell will visually overlap adjacent cells when it is actually contained in one cell. Excel limits text or formulas in a cell to 255 characters.

Numbers that are too large to be displayed inside the current cell will be displayed as a sequence of characters # # # # . To show a numeric value in a cell, you need to increase the width of the column (see the "Formatting Cells" section).

You can delete the contents of a cell using the key Delete.

Cell selection

Many operations, such as inserting rows or columns, deleting, copying, or moving cells, require one or more cells to be selected before the operation can begin.

The selection area can be either a separate cell or occupy an entire workbook. The active cell is always part of the selection. The selection area must be rectangular and can be defined as:

• one or more cells;
• one or more columns;
• one or more lines;
• one or more worksheets.

Tab. 17 illustrates some of the possible combinations.

Table 17. Designations of areas of the table.

Spreadsheet editor Excel. Entering and editing data

Task number 1: Table of Linear Function Values y=2.5x-5.5.

Create a new Microsoft Excel document.

You take Sheet1.

Rename Sheet1 giving it a name Function. For this:

Right-click on the sheet tab and select the command from the context menu that appears Rename(or double-click the sheet tab).

Enter a new sheet name from the keyboard Function.

Press a key Enter.

Select 14 cells from BUT1 before N1 and merge them Home/Cells=>Format Cells=>Alignment and check Merge Cells.

In the merged cell, enter the table title " Table of function values ​​y=2.5x-5.5". Change the font style to bold. To do this, use the command Format - Cells, select the tab Font and in the list inscription choose an option bold.

into cells A2 And A3 enter accordingly " X" And " at”, center align.

Select filled cells A2 And A3 and using the toolbar buttons:

increase the font size to 12 pt;

align to the center;

apply a bold font style.

Your table header is styled. Let's start filling in the table.

You need to fill in a series of cells with numbers from -6 to 6. To do this:

To cell IN 2 enter the value of the left border of the selected interval " -6 ", to cell C 2 next number " -5 ».

Select both cells. The selected cells are bordered by a frame, in the lower right corner of which there is a small black square - fill marker.

If you move the mouse pointer to the fill handle, and at the moment when the mouse pointer takes the form of a black cross, drag the fill handle a few cells to the right, then the entire row of selected cells will be filled with data. By selecting 2 cells, you indicated the principle by which the remaining cells should be filled. Drag the selection handle to the right border of the interval (to the cell N2)

To cell IN 3 enter the formula \u003d 2.5 * B2 - 5.5.

Highlight a cell B3 and type in the formula \u003d 2.5 * B2 - 5.5(do not forget to switch to Latin, because column headings are defined in Latin letters, and Russian letters BUT, IN, FROM although similar to Latin, they are not an equivalent replacement).

After entering the formula, fix it by pressing the key Enter, the cell will contain the result of the calculation by the formula, and the Formula Bar will contain the formula itself.

The formula can be viewed in the Formula Bar by highlighting the corresponding cell.

If you typed a formula incorrectly, you can correct it in the formula bar by first selecting the cell.

Highlight a cell IN3 and fill in the formula by dragging the fill handle to the right, a row of cells, to the right IN3 before N 3 .

Highlight a cell C3 and look in the Formula Bar, what the formula looks like, it took the form \u003d 2.5 * С2–5.5. It is noticeable that the references in the formula have changed relative to the offset of the formula itself.

Now all the cells are filled with data, it remains only to arrange them:

Select table and run command cells– Format– AutoFit Column Width.

Set the table header Function value tabley=2.5x-5.5". For this:

Highlight a heading in a cell BUT1 and apply bold characters to the contents of the cell.

Select 14 cells from BUT1 before N3(Table) and execute the command Format– cells, select bookmark alignment and set the switches to " Centered» (horizontal and vertical alignment). This will position the title in the center of the selected block of cells.

Frame the table. For this:

Select a table (without a header) and run the command Format - Cells, select tab Border, define the line style and activate the radio buttons Top, Bottom, Left, Right (External, Internal). This procedure applies to each of the cells.

Select heading: from A1 before N1 and, having done the same operations, set the switch External.

Browse ( File– View).

Task number 2: Prepare a spreadsheet to calculate your daily spending on public transport.

You take Sheet2.

Rename Sheet2 , giving it the name Daily spending.

Create a "header" of the table: Headings by row - modes of transport, in just a week for all modes of transport; by columns - cost of one trip, days of the week, total number of trips per week, total money spent per week for each type of transport.

Heading by columns (cells B1 to K1) issue:

Change text direction Rotate text up

Align Center

Align text to the center between the top and bottom borders of the cell.

Fill cells green

Heading by lines (from A2 to A5) issue:

Select AutoFit Column Width.

Fill cells with yellow.

Merge cells B 5 through K 5.

Table Borders: Select the entire table and make all cell borders visible.

Fill in the table with data as shown in the example:

We carry out calculations

We consider how much money was spent per week on bus trips:

Activate the desired cell ( K2)

Enter in the formula bar= J 2* B 2 , which means that we multiply the total number of trips by the cost of one trip.

After entering the formula, pressEnter. So, we calculated how much money was spent per week on the bus.

Calculate for yourself how many trips were made in a week by trolleybus, by fixed-route taxi; Calculate how much money was spent in a week on trips by trolleybus, by fixed-route taxi; Calculate how much money was spent on all vehicles in a week, the answer should be in cell B 5 (combined cells).

Browse ( File– View).

Save the document in your group folder.

Task number 3: Create the same Pythagorean multiplication table. As shown in the example.

Open a new Sheet3

Rename it to Pythagorean Table

Numbers 1-9 are entered using auto-complete (1, 2 and dragging the black X)

All cells with white fill should be calculated using the formula absolute link(the formula is written in one cell and copied to the rest of the rows or columns, the fixation of the cell address corresponding to copying is used - "$")

Prompt:

To calculate how much3*4 done: Selected the cell where the answer should be => enter the formula=$ A 5* E 2 => Enter .

Browse ( File– View).

Save the document in your group folder.