Active cell in an Excel table. Test work

TEST. Working in Excel. Creating Spreadsheets

1. Microsoft Excel is...

application program, designed for storing and processing data in the form of a table *

application program for processing code tables

a computer device that manages its resources in the process of processing data in tabular form

system program that manages computer resources

2. The spreadsheet is:

a set of numbered lines and columns named in Latin letters *

a set of rows and numbered columns named in letters of the Latin alphabet;

a collection of numbered rows and columns;

a collection of rows and columns named arbitrarily by the user.

3. What commands can you use to run the program? Excel?

a) Start – Programs – Microsoft Office – Microsoft Office Excel *

b) Pusk – Programs – Catalog Windows

c) Start – Programs – Standard – Notebook

d) Programs – Microsoft Office – Microsoft Excel

4. What is the name of the processing object?in Excel?

book * c) page

sheet d) text

5. What needs to be done to create a new book in the programExcel?

6. What extension do the files have? programs Excel?

Xls* b) .doc c) .txt d) .ppt

7. Minimum table component in the programExcel is...

cell * c) book

formulad) there is no right answer

8. Indicate what a worksheet cell address consists of in a programme Excel.

column designation, row number *

column designation

column number

line number

9. The active cell in an Excel table is the cell...

to record commands

selected cell*

10. How to select non-adjacent cells in Excel?

< Ctrl > and, while holding it, click on other cells *

< Shift >

click on the first cell,press< Alt > and, while holding it, click on other cells

perform the following actions: Edit – Go – Select.

11. How to select a range of cells in an Excel table?

click on the first cell, press < Shift> and, holding it, click on the last cell *

click on the first cell, press < Ctrl >

click on the first cell, press < Alt> and, holding it, click on the last cell

perform actions Edit – Go – Select

12 . Specify the correct actions to take when you finish entering data into a cell in Excel.

press a key< Enter > *

click on the button< Cancel > formula bars

press a key< End >

press a key < Space >

13 . With the help of which function key V Microsoft Excel Is it possible to edit and enter data into the active cell?

F2 * b)F5 c)F7 d)F4

14. How to delete the contents of cells in Excel?

select the cell and click< Del > *

select the cell and click< Ctrl > + < Del >

select the cell, click the left mouse button, select the command in the dialog box that appears Clear contents

select the cell and run the commands: View– Ordinary.

15. What commands can you use to add cells?to the table in Excel?

16 . Before you enter in formation into a cell in a programme Excel, you need...

a) make the cell active *

b) create a new cell

c) call context menu click right click mice

d) press the key Delete .

17. Specify how the names of rows on the worksheet are indicated in the programExcel.

are indicated by letters of the Latin alphabet

numbered with numbers *

18. Specify how the column names on the worksheet are indicated in the programExcel.

19. If you click on a row header in Excel,...

the entire line will be highlighted *

the contents of the line will appear

formula expression expected

a new line will be inserted

20. The main data in the program table is called Excel, ...

data that cannot be determined from other cells *

data that can be determined from the values of other cells

derived data

all spreadsheet data is basic

21. Table data is called derivatives Excel, …

data that cannot be determined from other cells

data that is determined by the values of other cells*

Basic data

all spreadsheet data is derived

22. Provide the correct column designations in Excel.

A * b ) A 12 c) 21 d) 12A

23. Specify the correct row designation in Excel.

A1 b)18 * c)21 A d) DD

24. Enter the correct cell address in a programme Excel.

B1 * c) #A10

"A10000 d) BZ_99

25 . The range of a table in Excel is...

26. Specify the correct designations for table ranges in Excel.

A:A2. b)23:4DE. c) 1A. d) A1:B2 *

27. What data can you enter? to cell in Excel?

number c) formula

text d) all listed *

28. What formatting is applicable to cellsin Excel?

framing and filling

text alignment and font format

data type, width and height

all options are correct *

29. You can change the data format in a single cell using panels …

30. You can format cells from a specific range using commands...

31.You can change the width and height of cells using the commands...

Format – String; Format - Column*

Service – String; Service – Column

Insert – Row; Insert - Column

Edit – String; Edit - Column

32. If you double-click on a filled table cell in Excel, the mode is activated...

33. Is the program capable Excel automatically continue the sequence of homogeneous data?

Yes * b) no

only a sequence of natural numbers

only sequence of dates

34. Using the functionAutofill in the program tableExcelCan…

create series of numbers, days, dates, quarters, etc.*

automatically perform simple calculations

perform complex calculations automatically

make changes to the contents of a cell

35. You can automatically fill an Excel table with homogeneous data using the commands...

Edit – Fill – Progression *

View - Fill - Progression

Format - Fill - Progression

Service – Autofill

[x] in which commands are entered.

to record commands;

to plot a diagram

to create a table

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

[x] View

home

Insert

Formulas

data

MS Excel 2010. To which group of commands do the commands belong: Freeze Panes, Arrange All, New Window?

[x] Window

Macros

Scale

Show

Paragraph

MS Excel. What command did the student use to automatically convert a grade from a letter equivalent to a digital equivalent?

[x] Macro

Formula

Replacement

Autosum

Number

MS Excel 2010. What will the student take as a basis when constructing a diagram?

[x] Table data

Excel workbook

Number of sheets

Cell Format

no answer

[x] ACCOUNT

AVERAGE

SUM

EMPTY

MS Excel 2010. The student needs to determine the day of the week in the numeric format of a given date. Which function should I use?

[x] WEEKDAY

TIMEVALUE

TODAY

CHECK

AVERAGE

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

[x] ROUND

ROUND

CHECK

SUM

MAX

MS Excel. What function is used for exponentiation?

[x] DEGREE

ROUND

CHECK

SUM

MAX

45. MS Excel 2010. Indicate which functions do not have arguments:

[x] TODAY, PI

COUNT, AVERAGE

RANK, RANGRV

SUM, MIN

MS Excel 2010. The student used the “Move Chart” command when moving a chart from one sheet to another. Which tab is it located in? this command?

[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 label above the points in the diagram. Which additional parameter does he need to choose?

[x] Top

Left

On right

From below

Centered

MS Excel 2010. The student must select the type of chart to best display the morbidity criteria for various age groups. What type of chart will he not be able to use?

[x] tree-like

bar chart

with regions

circular

schedule

MS Excel 2010. In which tab is the command to select data for creating 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 opportunity located?

[x] Format

View

Constructor

Diagram

MOSCOW INSTITUTE OF INTERNATIONAL

ECONOMIC RELATIONS

Specialty: "JURIPRUDENCE"

COURSE WORK

BY DISCIPLINE:

« COMPUTER SCIENCE AND MATHEMATICS»

On the topic

"The Purpose of SpreadsheetsMSExceland the main functions performed by these tables"

COMPLETED:STUDENT

CORRESPONDENCE BRANCH

Gudyushkin Evgeniy Nikolaevich

CHECKED BY: __________

GRADE: ___________

Ufa – 2007

Introduction 3 pages

1 . Concept Spreadsheets Microsoft Excel 4 pages

2. Data types and formats for their presentation 7 pages.

3. Application of operators in formulas 8 p.

4. Active cell and its operating modes 11 pages.

5. Auto fill cells 12 pages.

6. Selecting ranges 14 pages.

7. Moving around the worksheet 15 pages.

8. Copying and moving cells and other objects 16 pages.

Conclusion 18 pages

List of used literature 20 pages.

Introduction

Today, many companies store and/or process part of their business information using the publicly available MS Excel program.
To effectively work with spreadsheet data, macros and controls are used - lists, switches, counters, buttons, calendars, etc. Macros automate repetitive and time-consuming processing of data stored in a table. Controls placed on a worksheet form the workbook's user interface, which controls the entire operation of its tables and charts. Spreadsheets that use a graphical interface and macros to accomplish one or more related tasks are often called spreadsheet applications. As a rule, such “professional” tables are developed using the built-in MS Excel language Visual Basic for Applications (VBA) programming.

Microsoft Excel Spreadsheets- an extremely powerful program that allows you to achieve the desired result in various ways, even in the most seemingly simple situations. Excel offers rich capabilities for building complex formulas. Armed with a few mathematical operators and rules for entering values into cells, you can turn your worksheet into a powerful programmable calculator. Excel has several hundred built-in functions that perform a wide range of different calculations.

Functions- these are special, pre-created formulas that allow you to easily and quickly perform complex calculations and perform financial and statistical analysis. Using Excel, you can create complex charts for worksheet data. Using macros in Excel worksheets allows you to work interactively with the user and perform cumbersome and routine operations with just a few clicks of the mouse button.

1 . Concept Spreadsheets Microsoft Excel

The main MS Excel document is a workbook, which is a file with the extension *.xls. A workbook can be thought of as the electronic equivalent of a binder. The book consists of worksheets. The maximum number of worksheets is 255, they are designated as Sheet 1, etc. (Fig. 1). A worksheet can be associated with the concept of “document” or “spreadsheet”. Sheets of the book can be moved, copied, renamed. Moving can be done by dragging the sheet tab with the mouse. To rename, you can double-click the left mouse button on the tab of the sheet to be renamed, and then enter a new name. Copying can be conveniently done using the context menu. The context menu also allows you to add new leaf into the book and, if necessary, select all the sheets. In the user view, an Excel 97 spreadsheet (worksheet) consists of 65,536 rows and 256 columns or columns, which are displayed on the computer screen. Rows are numbered with integers from 1 to 65536, and columns or columns are designated by letters of the Latin alphabet A, B, ..., Z, AA, AB, ...IV. At the intersection of a row and a column, the main structural element of the table is located - the cell. The contents of a cell can be accessed by its address (link), for example, A5. A table (MS Excel uses the term worksheet, which we will continue to use) is a set of elementary cells, each of which belongs to a certain column and at the same time belongs to a certain row. Typically, in a graphical representation, cells in the same column are placed vertically next to each other, and cells in the same row are placed next to each other horizontally. The rows and columns are identified in some way, for example, the columns are named and the rows are numbered. The resulting data structure is called a worksheet. All rows contain the same number of cells and all columns contain the same number of cells, that is, the worksheet has a rectangular shape. By default, the worksheet has 256 columns named A through IV and 16384 rows named 1 through 16384 (this is enough in most cases). The column name and row number together uniquely identify the cell that (at the same time) belongs to them. This identifier is called a cell address or cell reference. Let us immediately note that MS Excel also supports another addressing system (link style), when both rows and columns are numbered. This referencing style is sometimes convenient and is needed for compatibility with other spreadsheet systems. Typically, addresses in the form “column name - row number” are used. You can also assign your own names to cells and use these names to refer to cells along with addresses.

In addition to the concept of a cell, the concept of a cell interval is used - a rectangular area of adjacent cells. The interval is specified by specifying the address of the top-left cell and the bottom-right cell, separated by the symbol: (colon). For example, the entry E5:G10 defines the cell range. Accordingly, an interval consisting of several whole columns is indicated by the names of the leftmost and rightmost columns, separated by a colon, for example, the entry B:G does not denote Boris Grebenshchikov, but all the cells in columns B, C, D, E, F and G. Worksheet cells are designed to hold different values. Thus, a cell can play the same role as a variable in mathematics: it has a designation (name or address) and can have and change meaning. Any calculation consists of calculating the values of other variables from the values of one variable. Typically, the calculation method is described using a formula containing mathematical operations and functions. But the formula itself is also a value that can be stored in a cell! This is the basic idea of spreadsheets: some worksheet cells are used as independent variables that must be given values from the outside, and other cells are used as dependent variables (they are called dependent cells in MS Excel), which contain formulas that refer to on independent variables (In MS Excel they are called influencing cells). The user enters the initial data into the influencing cells, calculations are automatically made using the formulas located in the dependent cells, and the user sees the finished result of the calculations in the dependent cells. Excel's capabilities are sufficient to describe (and therefore automatically perform) any calculations (in any case, these capabilities are certainly sufficient for calculations practically needed in business).

MS Excel is not just a spreadsheet with data and formulas that perform basic calculations using four arithmetic operations and some built-in functions. It is a versatile data processing system that can be used to analyze and present data in a visual form, and this powerful Microsoft Office tool can truly produce amazing results - in the right hands, of course. Forecasting in MS Excel is one of the most commonly used features. MS Excel easily extrapolates a number of data - for example, to analyze existing actual data, assess the current trend of their change and obtain a short-term forecast for the future on this basis.

Excel is a multi-window program. This means that you can open and edit multiple documents (workbooks) at the same time.

The appearance of the information contained in the spreadsheet can be changed by changing the data format, as well as setting the design of the cells, changing their width, height and color (menu Cell Format, team View).

2. Data types and formats for their presentation

You can enter one of the following types of data into any cell:

– number (numbers can be presented in various formats: general, numeric with various numbers of decimal places, monetary or financial, as well as fractional or exponential formats);

– text (any combination of characters – in general or text formats);

– date (in a special date format);

– time (time format);

– a formula for performing the necessary calculations (a formula is an arbitrary mathematical expression starting with the = sign). A formula can contain function calls, various operators, and cell references. For example, the formula =PI() located in cell A5 returns the value of the number p (3.1415...) to this cell, or the formula =RAND() located in cell A6 returns a random number in the range from 0 to 1.

Cell format can be controlled using the menu Cell Format.

3. Using operators in formulas

Arithmetic operator	Meaning	Example
+ (plus sign)	Addition
- (minus sign)	Subtraction
Unary minus
* (asterisk)	Multiplication
/ (slash)
% (percent sign)
^ (lid)	Construction to the degree	3^2 (analogous to 3*3)

For example, the formula =COS(A5)/2+SIN(A5/6) located in cell A8 will return the value 0, provided that the formula =PI() is located in cell A5.

Comparison Operators are used to denote comparison operations between two numbers. The result of a comparison operation is the logical value TRUE or FALSE.

Comparison operator	3Meaning	Example
= (equal sign)
> (greater than sign)

>= (greater than sign and equal sign)	More or equal
	Less or equal
(greater than sign and less than sign)

For example, let's say cell B5 has the formula =PI( ) , and in cell C5 =RAND( ) . In cell D5 you need to insert the result of dividing B5 by C5. There is a possibility of an erroneous situation – division by zero. To prevent this, you must enter the following formula in cell D5 =IF(C50;B5/C5;"You can't divide by zero"). In this example, the IF function returns either the result of dividing the contents of two cells, or, in the case when C5 = 0, a warning about the inadmissibility of division by zero.

Text operator «&» is used to denote the operation of concatenating multiple text strings or text constants into a single string.

For example, let's say cell A5 contains the formula =PI(), and in cell A6 =RAND(). The result of the product of the contents of these cells can be placed in cell A7 in the form of a formula ="Product of PI by a random number ="&A5*A6.

Address operators define cell ranges.

: (colon) is a range operator that refers to all cells between the range's boundaries, including those cells themselves.

Example . B5:B15 – cells of column B, from the fifth to the fifteenth.

, (comma) is a union operator that refers to the union of range cells (in fact, it is the union of several references into one reference).

Example . The SUM(B5:B15,D5:D15) function will sum the contents of the cells in these two ranges.

(space) – An intersection operator that refers to common cells in ranges.

Example . The SUM(B5:B15 A7:D7) function will return the contents of cell B7, because it is precisely this that is common to the indicated ranges.

4. Active cell and its operating modes

An active cell is a cell with which the user can currently work (or is working) by entering or editing data or formulas. The address of the active cell is displayed in the cell name field (see Figure 1).

An active cell can be in three main operating modes, which are shown in Fig. 2.

In mode Selected cell All basic modes of editing and setting cell parameters are available. Selecting multiple cells is done by dragging the cursor while holding down the left mouse button across the selected cells. In this case, the first cell from the selected range remains active.

IN Edit mode the active cell switches automatically when you start typing any characters on the keyboard or when you go to the formula editing line.

In the third mode, a moving dotted border around a cell indicates that its contents are on the clipboard and can be used for a paste operation.

5. Auto fill cells

Compiling tables is a labor-intensive process. Excel developers have made this task easier with the auto-fill mechanism. It can be used where a certain number of adjacent cells must be filled with homogeneous information. The first element of the data series is entered into one of the cells, and the second into the next. Then you should select both cells (by dragging while holding down the left mouse button), place the mouse pointer on the auto fill marker (a black square in the lower right corner of a cell or group of selected cells, and the mouse pointer takes the form of a black cross), press the left mouse button and drag it is further along the row or column that must be filled in automatically. Filling will occur from existing lists (see menu Service – Options… – Lists), or according to the principle: next value = previous + step. You can create the desired list yourself and add it to the list of standard ones.

Here are examples showing the capabilities of auto-fill.

In the first two cells, enter the dates 02/27/00 and 02/28/00. Having completed the sequence of actions described above, we get the following series:

Let Xmin=2, Xmin+h=2,2. Let's enter these values into adjacent cells and apply auto-fill. We get the series:

Please note that the default decimal separator can be a comma. And if Excel does not perceive the data entered with dots as numbers, simply replace the dots in the numbers with commas or change the default decimal place in the Windows settings through the menu Start(subparagraph Settings, Control Panel, Language and standards).

Comment. If the data row to be filled extends beyond the working screen, it is more convenient to use the command Edit – Fill –Progression…

6. Selecting ranges

You can select a range of contiguous cells by selecting the first cell in the range, pressing SHIFT, and then selecting the last cell in the range.

A special case is when the range boundary is beyond visibility (outside the current screen). Then, while holding down the Shift key, press the End key and the desired arrow key in sequence. If the range consists of several rows or columns, do not release Shift, press END again, and then press the arrow key (in the direction you want to make the selection).

If you need to select non-adjacent cells (or ranges thereof), use the CTRL key. Select the cells in the first range. Then, while holding CTRL, select the second range with the mouse, then the third, etc. It is interesting that the first cell of the last selected range will be active.

You can select one row (or column) by left-clicking on the name of the row (column). To select several adjacent rows (columns), drag the mouse with the left button pressed over the names of the selected rows (columns). Non-adjacent rows (columns) are selected using the CTRL key.

All sheet cells can be selected using the button Select all, located at the intersection of the column names row and the row names column. You can deselect the selection by left-clicking anywhere on the sheet.

7. Moving around the worksheet

Only a small fragment of the worksheet is displayed on the screen. Therefore, it is necessary to be able to move to the desired part of it. You can use scroll bars for this.

In some cases it is more convenient to use the keyboard. You can return to the first cell of the sheet using the CTRL+HOME keys. Using the CTRL+END key combination you can move to the last (rightmost) cell of the sheet. Pressing the Home key will take you to the beginning of the current line. You can move one screen left or right (keys ALT + PgUp and ALT + PgDn, respectively). Using the combinations CTRL + PgUp and CTRL + PgDn you can quickly move from one sheet of a workbook to another. The PgUp and PgDn keys allow you to scroll the screen up and down.

You can move to the border of the current block of information on the worksheet by pressing CTRL and the desired arrow key.

8. Copying and moving cells and other objects

To copy and move a cell or group of cells, it is convenient to use the capabilities provided by the Clipboard of the Windows operating system. All functions for interacting with the clipboard in Excel are implemented through the menu Edit and consist of three main features:

1. Copying selected cells (cells, arbitrary text, drawing or any other object) to the intermediate clipboard (the object does not disappear from the screen and remains in its place). Copying is carried out through the sub-item Copy or via the hotkey combination Ctrl+C.

2. Cutting the selected object to the intermediate clipboard is carried out through the sub-item Cut or via the hotkey combination Ctrl+X. In this case, the selected object, as a rule, disappears from the screen and is placed in the intermediate clipboard (when moving part of the table to a new place for Excel after the operation Cut cells do not disappear from the spreadsheet until the operation is performed Insert).

3. Inserting an object from the clipboard to the place where the editing cursor is currently located or into the current cell of the spreadsheet. To do this, we will use the subparagraph Insert or press the key combination Ctrl+V.

Comment. Hot combinations may differ from those shown above depending on the Excel version or installation type.

Second way. You can also perform operations to interact with the intermediate clipboard through context menu selected object (the context menu is called by right-clicking on the selected object). Interaction operations with the intermediate buffer are usually grouped into a separate block and are easily recognized.

The third way to perform operations with the intermediate clipboard is to use the toolbar with the main buttons for interacting with the buffer located on it, which are shown below

Conclusion

Considering this topic, I came to the conclusion that one of the most productive ideas in the field of computer information technology was the idea of a spreadsheet. Many PC software developers have created their own versions of spreadsheet processors - application programs 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 table 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, that is, they contain so-called calculated fields, the values of which are automatically recalculated using specified formulas when the values of the source data contained in other fields change. When working with spreadsheet processors, documents are created - spreadsheets (ET). A spreadsheet (document) is created in the computer's memory. In the future, it can be viewed, changed, recorded on magnetic disk for storage, print on a printer. Presenting data in the form of tables greatly simplifies the analysis of information. They are focused primarily on solving economic problems, but with their help you can solve mathematical, physical and engineering problems, for example, carry out calculations using formulas, build graphs and diagrams.

From all of the above, we can conclude that MS Excel spreadsheets are a very convenient and almost indispensable work tool for both experienced users and beginners. This program greatly simplifies and makes work in many areas of our lives easier and more enjoyable.

Every person whose work involves drawing up any reports, drawing graphs, or performing complex calculations must be able to use MS Excel. This will greatly simplify and make all routine work faster and less labor-intensive.

References

Blatner P., Ulrich L., Cook K. Using Excel 2005. Special edition. - M., K., St. Petersburg. 2005.

Karpov B. Microsoft Office 2000 Handbook. - St. Petersburg: Peter, 2004.

Akhmetov K., Borzenko A. Modern personal computer. - M.: Computer press, 1995.

Bott Ed. Using Microsoft Office 97. - K.: Dialectics, 1997.

Dodge M., Kinata K., Stinson K. Effective work with Excel 7.0 for Windows - St. Petersburg: Peter, 1996.

Novikov F.A., Yatsenko A. Microsoft Office as a whole – St. Petersburg: BHV-St. Petersburg, 2004.

Martik Althaus Michael Orlet, Excel 7.0. - M.: Bikom, 2002.

Nicole N., Albrecht R. Spreadsheets Excel 7.0. -M.: EKOM., 2002.

Nicole N., Albrecht R. Excel 7.0 spreadsheets for advanced users. -M.: EKOM., 2002.

Probityuk A. Excel 7.0 for Windows in the bureau. -K., BHV, 2000.

d) a system program that manages resources personal computer when working with tables.

2. The fundamental difference between a spreadsheet and a regular one is:

a) the ability to process data structured in the form of a table;

b) the ability to automatically recalculate data specified by formulas when the original data changes;

c) the ability to visually represent the connections between the processed data;

d) the ability to process data presented in strings of various types.

3. Spreadsheet rows:

a) are named by the user in an arbitrary manner;

b) are designated by the letters of the Russian alphabet A...Z;

d) are numbered.

4.The cell address in Excel consists of:

a) file name;

b) a given set of characters;

c) the column name and row number at the intersection of which the cell is located;

d) the row number and column name at the intersection of which the cell is located.

5. If in Excel you make a cell active and press the Delete key, then:

a) the contents of the cell will be deleted;

b) the cell format will be cleared;

c) the cell will be deleted;

d) the cell name will be deleted.

6. The information in the table is presented as:

a) files; b) records; c) text, numbers, formulas.

7. Quick recalculation of data in a spreadsheet occurs due to...

a) distribution of information across cells;

b) the presence of formulas connecting data;

c) fast processor operation.

8.An active cell in Excel is:

a) cell with address A1; b) cell highlighted with a frame; c) the cell in which the data is entered.

9.The range of cells in a spreadsheet is...

a) the set of all filled table cells;

c) a set of cells forming a rectangular area;

d) a set of cells forming an area of arbitrary shape.

10.Specify an invalid formula for cell F1

a) =A1+B1*D1; b) =A1+B1/F1; c) =C1.

11. Specify an invalid formula to write in cell D1

a) =2A 1+B 2; b) =A 1+B 2+C 3; c) =A 1-C 3; d) all formulas are acceptable.

12. You can't delete in a spreadsheet.

a) line; b) column; c) cell name; d) the contents of the cell.

13. A group of cells is highlighted in a spreadsheetC 3: F 10. How many cells are in this group?

a) 21; b) 24; c) 28; d) 32.

14. Expression , written in accordance with the rules accepted in mathematics, in a spreadsheet has the form:

a)3*(A1+B1)/(5*(2*B1–3*A2));

b)3(A1+B1)/5(2B1–3A2);

c)3* (A1+B1)/ 5* (2* B1–3* A2);

d)3(A1+B1)/(5(2B1–3A2)).

15. When moving or copying in a spreadsheet, absolute references:

a) do not change;

b) are transformed regardless of the new position of the formula;

c) are transformed depending on the new position of the formula;

d) are transformed depending on the length of the formula.

16 . The spreadsheet cell H5 contains the formula =$B$5*V5. What formula will be obtained from it when copied into cell H7:

a)=$B$7*V7; b)=$B$5*V5; c)=$B$5*V7; d)=B$7*V7.

17. In the spreadsheet, cell A1 contains the number 10, B1 contains the formula =A1/2, and C1 contains the formula =SUM(A1:B1). What is the value of C1:

a)10; b)15; at 2; d)150.

18. The diagram is:

a) a form of graphical representation of numerical values, which makes it easier to interpret numerical data;

b) schedule;

c) prepared table;

19. A bar chart is:

Answers:

The Word word processor has several built-in programs that implement various objects in document being created. They are accessed through Insert/Object.... Let's look at the two most popular of them: chart builder and formula editor.

Building charts

The application is used to create diagrams.

Select the cells in the table whose data is used to build the chart. The contents of the top row and left column of the selected area are used to label tick marks on the coordinate axes.

Via the menu Insert/Object.../Create in the "Object type" list, select "Microsoft Graph Chart", after which a table with data and a diagram will be displayed.

Set labels in the table: labels from the first row of the table that appears are used to mark the horizontal axis, and from the first column - for the legend. The legend is an explanatory figure to the right of the diagram.

Edit the diagram. To do this, right-click on the diagram element being edited - a menu for editing it will appear.

Quit Microsoft Graph by clicking in the main window outside the graph.

Move the chart to Right place document and adjust its size.

Example

Let's build a diagram illustrating the dynamics of revenue (see table above). To do this, select the interval A2:F5 in the table and load “Microsoft Graph”. Please note that this range includes a row with the names of months and a column with the names of cities. They are used for horizontal axis and legend labels.

If you need to edit the chart, you should right-click inside the chart and select the "Chart Options" menu item. The Chart Options window opens, allowing you to make most corrections. For example, to set labels on a horizontal axis, you need to open the "Axes" tab and select the "X Axis (Categories)" checkbox and the "Automatic" label. If you need to change the chart type, you should select the "Chart Type" menu item.

To set the vertical orientation of the labels under the X axis, right-click on any of the labels, select the "Format Axis" menu and on the "Alignment" tab set the vertical orientation.

If, with the "Microsoft Graph" object active, access the menu Data/Rows form columns, then the X axis will display data from the table columns.

All actions for editing a chart can be performed through the menu bar, which replaces the main menu while Microsoft Graph is running.

The final stage of editing a diagram is to resize it and place it in the desired place on the page.

Working with the formula editor

The "Microsoft Equation" formula editor is a program that is installed when installing the Word editor at the user's request. The editor has a large set of mathematical symbols and allows you to display fairly complex formulas. Unlike the TEX editor, where the formula is first encoded and then rendered special program, "Microsoft Equation" allows you to see the formula as it is written. After recording the formula, its dimensions can be changed like a regular drawing.

To write a formula, you need to place the cursor in the right place and go to the menu Insert/Object/Microsoft Equation 3.0. In this case, a frame for entering a formula and a formula editor panel containing two rows of buttons should appear. The top row of buttons determines symbol palette, lower - template palette. You can complete a formula by clicking outside the input field.

The general procedure for setting formulas is to select the required element in the formula editor panel and refine it in the menu that appears. Regular characters are entered from the keyboard into the designated fields. When moving from one field to another and when entering new fields, you must monitor the position and size of the cursor. For example, the cursor for a fraction is larger than its numerator or denominator. In this way, the space for entering the next character is shown.

Inserting spaces in formulas cannot be done by simply pressing the corresponding key. There are several types of spaces that are available in the Character Palette. If you need to insert spaces frequently, it is convenient to use the key combinations shown in the table.

Aligning a formula may be necessary, for example, after resizing it, despite the fact that in most cases the formula editor itself aligns the formula relative to the line in which it is written. To align the entire formula or part of it, you need to call the formula for editing by double-clicking the mouse, select the part to be aligned and press the combination of the Ctrl key and one of the cursor keys the required number of times, depending on the direction of the alignment. Each click moves the selected part by 1 pixel.

Control questions

How do you access built-in programs that implement various objects in the created document?
What application are you using to create the diagrams?
How to choose a charting application?
How to build a diagram?
What is a legend in a chart?
How to set labels for horizontal axis markings and legends?
How to edit a diagram?
What are the capabilities of the formula editor?
How to write a formula in a document?
What is the general order of a set of formulas?
How to insert a space into a formula?
How to align a formula?

This is a chapter from the book: Michael Girvin. Ctrl+Shift+Enter. Mastering array formulas in Excel.

This note is for those who are truly interested in complex array formulas. If you just need to extract a list of unique values once, it's much easier to use an Advanced Filter or PivotTable. The main advantages of using formulas are automatic updating when changing/adding source data or selection criteria. Before reading, it is advisable to refresh your memory of the ideas contained in previous materials:

(Chapter 11);
(Chapter 13);
(Chapter 15);
(Chapter 17).

Rice. 19.1. Retrieve unique records using option Advanced filter

Download the note in format or, examples in format

Retrieving a unique list from a single column using an option Advanced filter

In Fig. Figure 19.1 shows the data set (range A1:C9). Your goal is to get a list of unique racing tracks. Since you need to save the original data, you cannot use the option Remove duplicates(menu DATA –> Work with data –> Remove duplicates). But you can use Advanced filter. To open the dialog box Advanced filter, go through the menu DATA –> Sorting and Filter –> Additionally, or press and hold the Alt key and then press S, L (for Excel 2007 or later).

In the dialog box that opens Advanced filter(Fig. 19.1) set the option copy the result to another location, check the checkbox Only unique entries, specify the area from which unique values will be extracted ($B$1:$B$9), and the first cell where the extracted data will be placed ($E$1). In Fig. Figure 19.2 shows the resulting unique list (range E1:E6). If you do not include the field name in Original range dialog box Advanced filter(instead of putting $B$2:$B$9 in Figure 19.1), Excel will treat the first row of the range as the field name, and you risk getting a duplicate. In Fig. Figure 19.3 shows one of the many possible uses of a unique list.

Retrieving a unique list based on a criterion using the option Advanced filter

In the last example, you retrieved a unique list from a single column. An advanced filter can also retrieve a unique set of records (that is, entire rows from the source table) using a criterion. In Fig. Figures 19.4 and 19.5 show a situation in which you need to extract unique records from the range A1:D10 for which the company name is equal to ABC. Later in this chapter, you'll see how to do this job using a formula. However, if you don't need the process to be automatic, you can use Advanced filter, which is certainly simpler than the formula.

Rice. 19.4. You need unique records for Company ABC; To enlarge the image, right-click on it and select Open image in new tab

Rice. 19.5. Usage Advanced filter to retrieve unique records based on criteria is much easier than the formula method. However, the retrieved records will not be automatically updated if the criteria or source data changes

Retrieving a unique list from a single column using a PivotTable

If you already use PivotTables, then you know that every time you put any field in the area Strings or Columns(Fig. 19.6), you will automatically receive a unique list. In Fig. Figure 19.6 shows how you can quickly create a unique list of race tracks and then count the number of visits to each track. While a pivot table is useful for retrieving a unique list from a single column, you won't find it useful for retrieving unique records based on criteria.

Rice. 19.6. You can use a pivot table when you need a unique list and subsequent calculation based on it

Extracting a unique list from a single column using formulas and a helper column

Using a helper column makes retrieving unique data easier than using array formulas (Figure 19.7). This example uses the methods you learned in (Using the COUNTIF Function) and (Using a Helper Column). If you now change the source data in the range B2:B9, the formulas will automatically reflect those changes in the area D15:D21.

Array formula: Retrieving a unique list from a single column using the SMALL function

Because the array formulas used in this section are quite complex to understand, their creation is divided into stages: first, a fragment that counts unique values (Chapter 17); the second is criterion-based data extraction (Chapter 15). In Fig. Figure 19.8 shows the formula for calculating unique values (since this is an array formula, it is entered by pressing Ctrl+Shift+Enter). Note the following aspects of this formula:

The FREQUENCY function returns an array of numbers (Figure 19.9): for the first appearance of a race track, the number of its occurrences in the original data is returned; for each subsequent appearance of the race track, zero is returned (see). For example, Sumner appears in the first and fifth positions of the array. In the first position, the FREQUENCY function returns 2 - the total number of Summer in the range B2:B9, in the fifth position - 0.
The FREQUENCY function is placed in the argument log_expression IF functions, so the IF function returns TRUE for any non-null value and FALSE for a null value.
Argument value_if_true The IF function contains 1s, so the SUM function counts the number of such 1s.

Rice. 19.8. The FREQUENCY function is placed in the argument log_expression IF functions

Rice. 19.9. (1) the FREQUENCY function returns an array of numbers; (2) the IF function returns 1 for non-zero numbers, and FALSE for zeros.

Now let's create a formula for extracting a unique list. In Fig. Figure 19.10 shows an array of relative positions placed in an argument array functions SMALL.

In the previous example (Fig. 19.9) in the argument value_if_true The IF function placed a one, so the IF function returned ones and FALSE. Here (Fig. 19.10) the argument value_if_true contains: ROW($B$2:$B$9)-ROW($B$2)+1. Therefore, the IF function (within the SMALL function) returns the relative position number in the range with a unique race track, or FALSE for duplicates (Figure 19.11).

Rice. 11.19. The IF function returns the relative position number in the range with a unique race track, or FALSE for duplicates

In Fig. 19.12 show the results of the formula. In Fig. Figure 19.13 shows that as soon as the initial data changed, the formulas immediately reflected these changes. But what if you add new entries? Next, you'll see how to create dynamic range formulas.

Rice. 19.13. If the source data changes, the formula updates immediately. Filter and Advanced Filter can't update automatically without writing VBA code

Array formula: Retrieving a unique list from a single column using dynamic range

Let's expand on the last example with what you learned about formulas using specific names based on dynamic ranges (). In Fig. 19.14 shows the formula for determining the name Route. This formula assumes that you will never enter an entry after line 51.

Rice. 19.14. Name Definition Route based on formula

Once you define a name, you can use it in any formula. In Fig. Figure 19.15 shows how to use a name to count the number of unique values (compare with Figure 19.8). And in Fig. Figure 19.16 shows a formula that extracts the unique values themselves from a list of race tracks. Note that instead of the fragment range»» (as it was in Fig. 19.8 and 19.10), the ITEXT function is used (any text will return the value TRUE). When using ETEXT, if you enter a number (as in cell B11), or any other non-text, the formula will ignore that value. In Fig. Figure 19.17 shows that the formula automatically retrieves any new track names, ignoring numbers.

Rice. 19.16. Retrieve unique trace name based on dynamic range

Creating a unique value formula for a dropdown list

Based on the example just discussed, let's define the second name - RouteList, also based on dynamic range, but now referring to a list of unique traces (range E5:E14, Fig. 19.18). Since the range E5:E14 contains only text and empty values (zero-length test strings - ""), the argument lookup_value MATCH functions can use wildcards *? (which means at least one character). And in the argument match_type The MATCH function should use a value of -1, which will find the last text element in a column that contains at least one character. As shown in Fig. 19.18, then you can use a specific name in the field Source window Validation of entered values(For more information on creating a drop-down list, see). The dropdown list can expand and contract as new data is added or removed in column B.

If wildcards should be treated like regular characters

As you learned in, sometimes wildcards should be treated as characters. In Fig. Figure 19.18 shows how you can modify the formulas for such cases. You append a tilde before the argument range lookup_value MATCH function and append an empty string to the back of the range in the argument viewed_array.

Use a helper column or array formula to retrieve unique records based on criteria

At the beginning of the note, it was shown that for retrieving unique records based on criteria, Advanced filter. However, if you need instant updating, you can use a helper column (Figure 19.20) or array formulas (Figure 19.21).

Dynamic formulas to extract customer names and sales volume

The formulas are shown in Fig. 19.22. For example, if you add new entry TTTrucks to line 17 , the SUMIF formula in cell F15 will automatically add the new value. If you add a new client in column B, it will immediately appear in column E, and the SUMIF formula in column F will show the new total.

Rice. 19.22. Using a specific name and two array formulas to extract unique customers and sales volume

Note that the SUMIF function has an argument sum_range contains one cell – $C$10. Here's what the SUMSLI formula reference says on this topic: argument sum_range may not be the same size as the argument range. When determining the actual cells to be summed, the top left cell of the argument is used as the starting cell sum_range, and then the cells of the part of the range corresponding in size to the argument are summed range. Formulas entered in cells E15 and F15 are copied along the columns.

Sorting numeric values

The formulas for sorting numbers are quite simple, but the formulas for sorting mixed data are insanely complex. Therefore, if you do not require instant updating, then it is better to do without formulas by using the option Sorting. In Fig. Figure 19.23 shows two sorting formulas.

In Fig. Figure 19.24 shows how you can use a helper column to sort numbers. Since the RANK function does not sort like numbers (giving them the same rank), a COUNTIF function is added to distinguish between them. Note that the COUNTIF function has an extended range that starts one line higher. This is necessary so that the first appearance of any number does not give a contribution. The second appearance of the number will increase the rank by one. This sequential numbering establishes the order in which the INDEX and MATCH functions retrieve records in the range A8:B12.

If you can afford to create an auxiliary column in the data extraction area (range A10:A14 in Figure 19.25), it is convenient to use the sorting of numbers described above based on the SMALL function, and based on this, extract the names using the array function.

Rice. 19.25. If you can't use a helper column, use a sort based on the SMALL function (in cell A11) and an array formula (in cell B11)

Often in business and sports, you need to extract the top N values and the names associated with those values. Start your solution with the COUNTIF formula (cell A11 in Figure 19.26), which will determine the number of records to display. Note that the argument criterion in the COUNTIF function in cell A11 – more or equal the value in cell D8. This allows you to display all the boundary values (in our example, although we need to display Top 3, there are four suitable values).

Rice. 19.26. Extracting the top three total scores and their corresponding names. When N changes in cell D8, area A15:B21 will be updated

Sorting text values

If the use of an auxiliary column is permissible, the task is not so difficult (Fig. 19.27). Comparison operators process text characters based on the ASCII numeric codes assigned to the characters. In cell C3, the first COUNTIF function returns zero, and the second adds one. In C4: 2+1, C5: 0+2, C6: 3+1.

Sort mixed data

The formula that allows you to extract unique values from mixed data and then sort them is very large (Figure 19.28). When creating it, we used ideas that were encountered earlier in this book. Let's start learning the formula by looking at how the standard sort function works in Excel.

Excel sorts the results into: numbers first, then text (including zero-length strings), FALSE, TRUE, error values in the order they appear, blank cells. All sorting occurs in accordance with ASCII codes. There are 255 ASCII codes, each corresponding to a number from 1 to 255:

For example, the number 5 corresponds to the ASCII code 53, and the character S corresponds to the ASCII code 83. If you sort the two values, 5 and S, from smallest to largest, then 5 will be higher than S because 53 is less than 83.

The data set in the range A2:A5 (Fig. 29) is converted into the range E2:E5 in accordance with the sorting rules. To better understand the principles of sorting, consider the values in the range C2:C5. For example, if you ask the question “How many people outrank me?” to the ID in cell A2 (54678), the answer will be zero, because in a sorted list, ID 54678 will be the top one. SD-987-56 will have three IDs above it. You need a formula to get values in the range C2:C5.

To begin, select the range E1:H1 and type =TRANSP(A2:A5) in the formula bar and enter the formula by pressing Ctrl+Shift+Enter (Fig. 19.30). Next, select the range E2:H5 in the formula bar, type =A2:A5>E1:H1 and enter the formula by pressing Ctrl+Shift+Enter (Fig. 19.31). In Fig. Figure 19.32 shows the result, which is a rectangular array of TRUE and FALSE values that correspond to each of the cells in the resulting array, as the answer to the question “Is the row header greater than the column header?”

Rice. 19.30. Select the range E1:H1 and enter array formulas

Rice. 19.31. In the range E2:H5, enter the array formula =A2:A5>E1:H1

Rice. 19.32. Each cell in the range E2:H5 contains the answer to the question “Is the row header greater than the column header?”

For example, in cell E3 the question is: SD-987-56 > 54678. Since 54678 is less than SD-987-56, the answer is TRUE. Note that the range E3:H3 includes three TRUE values and one FALSE value. Looking back at Fig. 19.29, you can see that it is the number three that is in cell C3.

As shown in Figures 19.33 and 19.34, you can convert the values TRUE and FALSE to ones and zeros by adding a double negation to the array formula. Since the original array (E2:H5) is 4x4 and you want the result to be a 4x1 array, use the MULTIPLE function (see Figure 19.35 i). The MULTIPLE function is an array function, so enter it by pressing Ctrl+Shift+Enter (Fig. 19.36). Now, instead of using the range E2:H5, add the appropriate elements inside the formula (Figure 19.37).

Rice. 19.36. By selecting the range C2:C5 and entering the MULTIPLE array function, you get a column of numbers that tell you how many IDs in the sorted list are higher than the selected one

Rice. 19.37. Instead of using the auxiliary range E2:H5, the corresponding elements are added inside the formula

In Fig. Figure 19.38 shows how you can replace an array of constants with the fragment ROW($A$2:$A$5)^0.

Rice. 19.39. To deal with potential empty cells, all occurrences of A2:A5 should be supplemented with an IF test (A2:A5"",A2:A5); the ROW function does not require such an addition, because the function works with the cell address, not its contents

Because the final formula will be used elsewhere, you need to make all ranges absolute (Figure 19.40). In Fig. Figure 19.41 shows the resulting values.

Rice. 19.40. Ranges A2:A5 turned into absolute

Since this element will be used twice later, you can save it under a specific name. As shown in the dialog box (Fig. 19.42), the formula is given the name SZB - How Many Values Are Greater.

Argument array The INDEX function refers to the source range A2:A5.
The first MATCH function will tell the INDEX function the relative position of the element in the array A2:A5.
While the argument lookup_value The MATCH function is left empty.
Defined name (DSN) in argument viewed_array will allow you to first access an element that has a value of 0, then 2, and finally 3.
Zero in argumentation match_type specifies an exact match, which will eliminate the need to refer to duplicates.

Rice. 19.43. You start a formula to extract and sort data in cell A11. Argument lookup_value MATCH functions leave empty for now

Before you create an argument lookup_value SEARCH functions, remember what you actually need. There are three unique IDs that need to be sorted, so you'll need three numbers in the argument lookup_value as the formula is copied down. These numbers will allow you to find the relative position in the A2:A5 array, which is what you need to provide to the INDEX function:

In cell A11, the MATCH function will return 0, which corresponds to the relative position of 1 within the defined SZB name.
When the formula is copied down into cell A12, the MATCH function should return the number 2 and the relative position = 4 within the MSB.
In cell A13, the MATCH function should return 3 and relative position = 2 within the MSB.

A picture emerges when you think about the argument lookup_value when copying the formula down, the query must match: “Give minimum value inside a specific SZB name that has not yet been used." As shown in Fig. 19.44 the formula element MIN(IF(END(MATCH($A$2:$A$5,A$10:A10,0)),MSB)) returns the minimum value when copying the formula down, exactly answering the query. The reason this works is that the UND(MATCH($A$2:$A$5,A$10:A10,0)) fragment compares two lists (see). Notice the expanding range A$10:A10 in the argument viewed_array. In cell A11, the combination of UNM and MATCH helps extract all the unique numbers from the SZB and provide them to the MIN function. When you copy the formula down to cell A12, the ID that was retrieved in cell A11 is again present in the expanded range and will again be found in the range $A$2:$A$5. However, UND returns FALSE, and the value 0 will not be extracted from the SZB. To see this, enter the array formula in Figure 19.44 by pressing Ctrl+Shift+Enter and copy it down.

Rice. 19.44. Formula element in argument lookup_value The MATCH function matches the request: "Give the minimum value within a specific SZB name that has not yet been used"

In Fig. 19.45 shows that in the argument viewed_array The second MATCH function expanded the range A$10:A10 to A$10:A11. To understand how this formula works, select its fragments sequentially and click on F9 (Fig. 19.46–19.49).

Rice. 19.45. The expanding range A$10:A11 now (in cell A12) includes the first ID (54678)

Rice. 19.46. The combination of the UNM and the second MATCH functions supplies an array of Boolean values; two FALSE values exclude null values from the defined SZB name

Rice. 19.47. The zeros are eliminated and only the numbers 3 and 2 remain; the number 2 is the minimum, so it should be extracted next

Rice. 19.48. The MIN function selects the number 2; the MATCH function can now find the correct relative position for the INDEX function

Rice. 19.49. The INDEX function will retrieve the value 2, which corresponds to the relative fourth position of the ID in the range A2:A5

Now, returning to cell A11, you can add another condition so that empty cells do not affect the formula (Figure 19.50).

Rice. 19.50. There are two conditions inside the MIN function; first: “are the cells empty?”, second: “has the value not been used yet?”

In Fig. 19.51 shows the final formula. It added a condition to ensure that rows in the range A11:A15 remain empty after sorted unique values are extracted. In Fig. Figure 19.52 shows what happens if cell A3 is left blank. Our addition to check for empty cells worked.

It wasn't easy. But if you've read this far, I hope you enjoyed it.

1. What is a spreadsheet (ET)

Spreadsheet - a computer program that allows you to carry out calculations with data presented in the form of two-dimensional arrays that imitate paper tables

2. Expand the concepts: row, column, cell, block of cells.

Title bar serves to display the name of the application.

Input and editing line displays data or formulas entered into the current table cell. In this row, you can view or edit the contents of this cell, as well as see the formula itself.

Status bar contains information for going to any required page and for changing the image scale.

Hint line is intended to provide information to the user about his possible actions at the current moment.

Cell a worksheet is formed by the intersection of a row and a column, and its address is determined by the letter of the column and the row number: for example, cell A1 is located at the intersection of the first row and column A, which is in fourth place. Not only a cell, but also a group of adjacent cells can have an address block (range) of cells. A block of cells can be considered a row or part of a row, a column or part of a column, as well as a rectangle consisting of several rows and columns or parts thereof. A block of cells is specified by indicating the addresses of its first and last cells, between which a separating character is placed - a colon (B2: D5).

3. What is a cell address?

4. Which cell is called the current (active)?

Active table area the part filled with data is called

5. How does absolute addressing differ from relative addressing?

6. What could be the contents of an ET cell?

Different tables may contain completely different information. Some cells contain text, some contain numeric data. From an Excel perspective, a cell can contain three types of data.

Text the data is a string of text of arbitrary length. Excel reproduces such data exactly as it was entered. Such a cell cannot be used in calculations. If Excel cannot interpret the data as a number or as a formula, the program assumes it is text data.

Numerical data is a single number entered into a cell. They cannot contain alphabetic or special characters, since mathematical operations are performed with them. The only exceptions are the decimal point (comma) and the number sign that precedes it. Data that define dates or monetary amounts are considered as numbers.

Formulas are the instructions by which calculations are performed. In this case, the formula itself may not be visible on the screen, but the result of calculations using it will be presented in the cell. The contents of a cell are treated as a formula if they begin with an equal sign.

7. What are the rules for writing formulas?

1. Make sure that the cell in which you want to get the calculation result is active (highlighted with an italic frame).

2. Entering a formula begins with the “=” sign. This character is entered from the keyboard.

3. After entering the “=” sign, Excel switches to formula entry mode. In this mode, when you select a cell, its address is automatically entered into the formula. This eliminates the need for the user to know cell addresses and enter them into the formula from the keyboard.

4. While in formula entry mode, you sequentially point with the left mouse button at the cells storing certain numerical values, and enter the operation signs between the original values using the keyboard.

§ Operation signs must be entered between cell addresses.

§ It is more convenient to enter operation characters from the right numeric block of the keyboard. For this unit to work in the desired mode, the indicator must be enabled.

5. In order for the calculation result to appear in the active cell, you must exit the formula entry mode.

§ completes entering the formula and moves the cursor to the next cell.

§ “Green checkmark” on the formula input panel completes entering the formula, and leaves the cursor in the same cell.

9. Name the functionality of Excel

The spreadsheet processor MS Excel (spreadsheets) is one of the most commonly used applications in the integrated MS Office package. most powerful tool in capable hands, greatly simplifying routine daily work. The main purpose of MS Excel is to solve almost any calculation problems, the input data of which can be presented in the form of tables. The use of spreadsheets simplifies working with data and allows you to obtain results without programming calculations. In combination with the Visual Basic for Application (VBA) programming language, the MS Excel spreadsheet processor becomes universal and allows you to solve any problem, regardless of its nature.

10. What is the structure of the Excel window?

Basic structural elements of a spreadsheet:

Line

Column

Cell

A spreadsheet can have multiple sheets. These sheets form book. The currently selected cell is called active cell.

11. What is the sequence of creating ET.

To create a new book in the open Microsoft application Excel needs to do one of the following:

Click the button Create on the panel Standard ;

Press the keyboard shortcut<Ctrl>+ <N>;

Execute menu command File -Create and then in the panel that pops up on the right

In chapter Creation team Blank book (Fig. 8.3).

After this, a new Excel document will open in front of you, ready for work. The program will assign a type name to the new document Book1 , but you can give the document a unique name when you save it.

12. How to clear the current ET cell?

Delete button

13. How to enter a formula into an ET cell?

Rules for writing formulas in Excel:

two operation symbols must not appear together;

each opening parenthesis must have a matching closing parenthesis (Excel will not allow you to fix the entry of a formula into a cell if there is no matching parenthesis);

the formula cannot contain more than 1024 characters; formulas can be divided into many simpler ones (superposition principle).

Algorithm 2.1. Entering a simple formula

The following simple formula is used to multiply 135 by 1%.

To enter a simple formula, do the following:

Select (activate) a cell A3 ,to enter the formula.

Enter character “= ” and formula symbols (zero integers can be omitted when entering). Notice that the word appears on the left side of the status bar Enter. The characters you enter appear simultaneously in the cell A3 and in formula bar.

Commit manual input formulas - press the key . The result of the formula 1.35 is displayed in the cell A3. The word appears on the left side of the status bar Ready.

The formula will appear in the formula bar when the corresponding cell is active.

Instead of a formula, you can enter a constant into the cell (directly the value 1.35).

14. How is a formula copied? 5 How to correct the contents of a cell?

15. How to format numeric data?

The Cell command of the Format menu allows you to control the output (display) of numeric and text values in the activated cell.

16. How to delete rows (columns) of ET?

Blank cells can be inserted above or to the left of the active cell on the worksheet. In this case, the remaining cells will be shifted down in the same column or to the right in the same row. In the same way, you can insert rows above the selected row or columns to the left of the selected column. Cells, rows, and columns can also be deleted.

17. How to align data in ET cells

BY DISCIPLINE:

3. Application of operators in formulas 8 p.

5. Auto fill cells 12 pages.

3Meaning

Example

5. Auto fill cells

Building charts

Working with the formula editor

Control questions

Retrieving a unique list from a single column using an option Advanced filter

Retrieving a unique list based on a criterion using the option Advanced filter

Retrieving a unique list from a single column using a PivotTable

Extracting a unique list from a single column using formulas and a helper column

Array formula: Retrieving a unique list from a single column using the SMALL function

Array formula: Retrieving a unique list from a single column using dynamic range

Creating a unique value formula for a dropdown list

If wildcards should be treated like regular characters

Use a helper column or array formula to retrieve unique records based on criteria

Dynamic formulas to extract customer names and sales volume

Sorting numeric values

Sorting text values

Sort mixed data