First acquaintance with MS Excel spreadsheet processor

Table processor Is a software tool for working with spreadsheets of data that allows you to calculate, organize, analyze and graphically represent various types of data.

Elements spreadsheets are cells, rows and columns.

Workbook and worksheet

MS Excel file is also called Excel workbook... When you start MS Excel, a new book Book1 is automatically created in the computer's memory (see fig.). An Excel workbook consists of one or more worksheets (you can change their number). The sheet the user is on is called active and its tab is highlighted in white. To activate a sheet, just click on its tab.

Worksheet ET is presented in the form of a table, consisting of 256 columns, which are designated by English letters (A, B, C,… Z, AA, AB,… IV) and 65356 lines, which are indicated by numbers (1… 65356).

WITH active worksheets MS Excel workbooks, you can do the following:

Delete (main menu command Edit / Delete Sheet );

Insert ( Insert / Sheet );

Copying ( Edit / Move / Copy Sheet: check the box in the dialog box Create a copy);

Move ( Edit / Move / Copy Sheet );

Renaming ( double click on a sheet tab ).

Also, all these operations can be performed by right-clicking on the sheet tab and selecting the appropriate command from the context menu.

Cells and cell blocks

The worksheet consists of cells which are formed at the intersection of columns and rows. I AM cell- the main element of ET. The cell is uniquely specified by its coordinates ( address), where the letter (or letters) of the column is indicated first, and then the number of the line at the intersection of which it is located. for instance , A1, D12, CC10.

A cell is called active or selected if it is surrounded by a dark bold line (selection frame).

To select a cell, just click on it with the left mouse button.

Moving through the cells of the worksheet can be done:

· Using the mouse: to switch to any visible cell, place the mouse pointer on the required cell and click the left mouse button;

Using the keyboard:

Ú or key Tab- move one cell to the right;

Key Ù - move one cell to the left;

Key Û - move one cell up;

Ü or key Enter- move one cell down.

1. Formula bar located above the worksheet. It displays the contents of the current cell. The formula bar can be called or hidden through the main menu command View / Formula bar.

2. Address field located to the left of the formula bar. It displays the address of the current cell.

Any cell has two characteristics:

1) Data type numbers; symbols; formulas;

· Number- numbers in a cell can be entered with the signs =, +, - or without them. If the width of the entered number is greater than the width of the cell on the screen, then Excel displays it in exponential form or instead of the number puts the symbols #### (this will save the number in memory completely). In this case, you can increase the width of the cell.

· Symbols is any typed text.

· Formula- starts with a sign « =» and is a sequence of numbers or cell references, united by signs of arithmetic operations or functions.

2) Format, that is, a certain kind of data in a cell. There are different formats for each type of data. Cell format setting can be done via the menu command Format / Cells... For example, number format, date and time, percentage, currency, etc.

Also in ET you can work with blocks (fragments, ranges) cells. It is the part of the table, delimited by some two columns and some two rows.

Block is given by the coordinates of the upper-left and lower-right cells, separated by a colon. for instance, D3: F20, U15: AB38.

Cells and blocks can be assigned names... To do this, select a cell or block and type the name in the address field. These names are then added to the address list (arrow button next to the address field).

[x] in which commands are entered.

to record commands;

to build a diagram

to create a table

MS Excel 2010. In which tab are the commands: Ruler, Formula Bar, Grid, Headings?

[x] View

home

Insert

Formulas

data

MS Excel 2010. Which group of commands do the commands belong to: Freeze Areas, Arrange All, New Window?

[x] Window

Macros

Scale

Show

Paragraph

MS Excel. What command did the student use to automatically translate the grade from alphabetic to numeric?

[x] Macro

Formula

Replacement

Autosum

Number

MS Excel 2010. What will a student take as a basis for building a chart?

[x] Table data

Excel workbook

Number of sheets

Cell format

no answer

[x] ACCOUNT

AVERAGE

SUM

EMPTY

IF

MS Excel 2010. The student needs to determine the day of the week in the numeric format of the given date. Which function do you need to use?

[x] WEEK DAY

TIME VALUE

TODAY

CHECK

AVERAGE

MS Excel 2010. What function rounds a number to the specified number of decimal places?

[x] ROUND

Okruglt

CHECK

SUM

MAX

MS Excel. What function is used for exponentiation?

[x] DEGREE

Okruglt

CHECK

SUM

MAX

45. MS Excel 2010. Specify which functions have no arguments:

[x] TODAY, PI

IF

ACCOUNT, AVERAGE

RANK, RANGRV

SUM, MIN

MS Excel 2010. A student, when moving a chart from one sheet to another, used the "Move Chart" command. In which tab is this command located?

[x] Constructor

Layout

Format

home

Insert

MS Excel 2010. The student must indicate the name of the axes in the diagram. Which tab will he use to fulfill this requirement?

[x] Layout

Constructor

Format

home

Insert

MS Excel 2010. The student must indicate the data signature above the points in the chart. What additional parameter should he choose?

[x] Top

Left

On right

From below

Center

MS Excel 2010. The student needs to choose the type of diagram for the best display of the morbidity rate of different age groups. What kind of chart will he not be able to use?

[x] tree

bar graph

with areas

circular

schedule

MS Excel 2010. In which tab is the command to select data for building a chart?

[x] Constructor

Layout

Format

Diagram

Insert

MS Excel 2010. In the finished chart, you need to change the background. In which tab is this feature located?

[x] Format

View

Constructor

Diagram

A cell is called active when information (text, number) is entered into it

After we have created our document, we need to save it.

To save the document, we need to do the following operations:

Left-click on the file tab;

After that, a context menu will appear, in which we select the "Save" value;

V. Application of the acquired knowledge (5 min)

Test on the topic "Spreadsheets"

Student (s).
Surname, First name

Put "+" next to the correct answer

1. In a spreadsheet, the main element of the worksheet is ...

1) cell;
2) string;
3) column.

2. The cell address of the spreadsheet is ...

1) a name consisting of any sequence of characters;
2) a name consisting of a column name and a row number
3) the address of the byte of RAM allocated for the cell;

3. A cell of a spreadsheet cannot contain:

1) number;
2) text;
3) sheet.

4. Select the correct row designator in the spreadsheet:

1) K13;
2) 34;
3) AB.

5. Select the correct column label in the spreadsheet:

1) F12;
2) A;
3) 113.

6. Select the correct cell address in the spreadsheet:

1) 11D;
2) F12;
3) AB3.

VІ. Lesson summary (3 min)

So, guys, what have you learned new for yourself in today's lesson?

What is a string? (all cells located on the same horizontal level.)

What is a Column? ( all cells located in the same vertical row of the table tsy.)

What is a cell? (an elementary spreadsheet object located at the intersection of a column and a row.)

How to determine the cell address? (by row and column headings).

"Plus-minus-interesting". This exercise can be done orally or in writing, depending on the availability of time. For written execution, it is proposed to complete a three-column table. Column "P" - "plus" - records everything that was pleasant in the lesson, information and forms of work that caused positive emotions, or, in the student's opinion, can be useful for him to achieve some goals. Column "M" - "minus" - records everything that was not pleasant in the lesson, seemed boring, caused dislike, remained incomprehensible, or information that, in the student's opinion, turned out to be unnecessary and useless for him. In the column "I" - "interesting" - the students enter all the curious facts that they learned about in the lesson, what else they would like to know on this issue, questions to the teacher.

VІІ. Homework (2 min)

Change column width and row height.

Inserting and Deleting Worksheet Columns and Rows

Delete, add, copy, rename a sheet

All named operations and others are performed using the context menu, which opens in relation to the active sheet shortcut (Fig. 1)

To change the number of sheets in a newly created book, you need to open the dialog box Excel options and in the appropriate section indicate the required number of sheets:

File - Options - General

When creating new books (number of sheets)

Inserting columns and rows on a sheet is performed using the icon of the corresponding functions on the ribbon in the group Cells in the tab home (rice.). You can use the context menu to insert columns and rows. Columns are inserted to the right of the cursor, and rows are inserted at the top of the cursor.

To delete a row / column, select a row / column or several rows / columns and use the corresponding functions on the ribbon (Fig.) Or in the context menu.

Selection of rows / columns is carried out by left-clicking on the name of the row / columns.

To change the column width and row height, you must:

• Place the cursor on the border of a column or row in the area of ​​the column (row) name. The cursor changes to a double-headed arrow with a split line.
• Fix the cursor position with the left mouse button.
• Move the border in the desired direction.
• Cells are formed at the intersection of the rows and columns of the sheet.

Cell is the smallest indivisible structural unit for placing data within a worksheet. Cells can contain text, numeric values, formulas, macros. The program automatically recognizes the type of data and determines the list of operations that can be performed with them.

By their content, cells are divided into source (influencing) and dependent. The cell selected with the pointer is called active or current. To change the height or width of a cell, you need to change the row height and column width respectively. All rows are numbered, columns are designated by letters.

Excel automatically assigns unique addresses to each cell in the worksheet. Cell address, which the program assigns to it consists of a column address and a row address. The address of the active cell appears on the left side of the formula bar.

Working field Excel is a table. The table consists of columns with headings A, B, C, D, and so on, and numbered rows. Each cell on the sheet has its own name (address), for example, the topmost left cell has the address A1. This cell is framed. Any cell highlighted with a frame is active, that is, it is in this cell that you can enter data from the keyboard.

The address of the highlighted cell appears in Name field located above the heading of column A at the same level as A line of formulas.

V Excel there is a concept spacing of cells. Cell spacing is a rectangular section of a multi-cell table. The spacing is specified by specifying the address of the upper left and lower right cells of the rectangular block of cells. When specifying the interval, the addresses of these two cells are separated by a colon, for example - B7: E11.

Very often, when working in Excel, it is necessary to use the cell addressing data in the spreadsheet. For this, the CELL function was provided. Let's consider its use with specific examples.

Function value and cell properties in Excel

It is worth noting that Excel uses several functions for addressing cells:

• - LINE;
• - COLUMN and others.

CELL (), the English version of CELL (), returns information about the formatting, address, or content of a cell. The function can return detailed information about the cell format, thereby eliminating the need to use VBA in some cases. This function is especially useful if you need to display the full path of a file in cells.

How does the CELL function in Excel work?

The CELL function uses a syntax that consists of two arguments:



Examples of using the CELL function in Excel

Example 1. Given a table of accounting for the work of employees of the organization of the form:

It is necessary using the CELL function to calculate in which row and column the salary of 235,000 rubles is located.

To do this, we introduce a formula of the following form:

• - "row" and "column" - output parameter;
• - С8 - address of data with salary.

As a result of the calculations we get: row # 8 and column # 3 (C).

How to find out the width of an Excel table?

Example 2. You need to calculate the width of the table in characters. It should be noted right away that in Excel, by default, the width of columns and cells is measured in the number of characters that they fit in their value and are available for display in a cell without a line break.

Note. The default height of rows and cells in Excel is measured in base font units - pt. The larger the font, the higher the line for full display of characters in height.

Let's enter in cell C14 the formula for calculating the sum of the width of each column of the table:

• - "width" - function parameter;
• - A1 - the width of a specific column.

How to get the value of the first cell in a range

Example 3. In the condition of example 1, you need to display the content only from the first (upper left) cell from the range A5: C8.

Let's enter a formula for calculating:

The description of the formula is similar to the previous two examples.