It's no secret that computers are becoming more powerful every year, and software products are becoming more advanced. 2012 in the region information technologies was marked by the appearance Microsoft Office. Each component of this package contains new, but compared to previous versions of this software product, functional resources.

In office work, spreadsheets are among the components that are traditionally most in demand by users. And the new Microsoft Excel application is undoubtedly a big success. This software product can be effectively used to automate various areas of office activities. A completely redesigned interface is the main distinguishing feature of Microsoft Excel. Therefore, in this chapter we will first explain the interface this application.

Despite significant changes appearance, the Microsoft Excel program is absolutely recognizable. To launch this application, use the Start Programs Microsoft Office Microsoft route Office Excel. The appearance of the worksheet and menu commands (Fig. 1.1) indicate that we are looking at familiar spreadsheets. This is formally confirmed by the title line, which is located at the top of the sheet, where the name of the software product is present.

Besides the name running application The header contains the name of the currently opened file. Microsoft Excel automatically assigns the name Book1 to newly created files.

A new interface element is the Office button, which is located in the upper left corner of the application window. It is round and immediately catches your eye, especially since it has a beautiful logo on it. Pressing this button causes a menu to appear on the screen (Fig. 1.2), a number of commands of which are familiar to us from older versions of Microsoft Excel, and some are new to the user. Here you will see traditional commands for opening and saving workbooks. Most of the menu (on the right) is occupied by a list of documents that the user has recently opened.

At the bottom of the menu (see Figure 1.2) there is an Excel Options button, which allows you to open a window with many program settings options. In previous versions of Microsoft Excel, these options were present in the Options and Settings sections (Tools menu). Another part of the parameters in Microsoft Excel appeared for the first time. One of the main elements of the Microsoft Excel window is the ribbon (Fig. 1.3). It replaced the toolbars and menu bar found in previous versions Excel. Adding a ribbon is related to the convenience of finding the necessary commands. We can say that tape is a significant improvement in tools. The main feature is that the commands on the ribbon are grouped into thematic tabs, making it easy to find the required icon. According to the Microsoft concept, the goal of the transition from the classic interface to the new one is to focus on results and solve practical problems as quickly and conveniently as possible. Effectiveness is achieved through better organization of teams and greater visibility.

The ribbon is organized as a set of thematic tabs containing management tools. Each tab has a name that reflects its purpose. Most of the tools are buttons; some tools are checkboxes, counters, and combo boxes. The tools on each tab are divided into logical groups. Groups are a block of commands enclosed in a rectangular frame. Similar to tabs, each group of tools has a name that reflects its purpose. At any given time, only one tab is active (selected). A tab can be activated (selected) by clicking the mouse, and all groups of tools for this tab are displayed on the screen.

A similar organization of the modern menu Microsoft versions Excel allows the user to spend less time navigating to the required commands. At first glance, horizontal tabs make the work more difficult, but just a few days of practice will change this opinion to the opposite.

MS Excel. Initial information. Basic editing techniques. Carrying out calculations

Purpose and brief description of Microsoft Excel

The spreadsheet processor Microsoft Excel 2003 is one of the best modern software products for creating spreadsheets, allowing you to carry out fairly complex calculations, including statistical modeling, with exceptional visual presentation of the results.

Some of the new features discussed in previous chapters also apply to Excel, such as task panes and pop-up buttons with drop-down menus. Most new or improved features will not be visible to you until you start using the program. Numerous innovations have simplified using Excel in key areas such as relationship management, search tools Find And Replace, sorting, using international number formats, editing cells, checking errors, printing, working with headers and footers, etc.

Structure Excel workbooks

Excel application window

Launching Excel is done in the same way as most Microsoft Office applications. Click the button Start on the taskbar, select the command from the menu that appears Programs and then the Microsoft Excel option.

When you first start the program, a blank sheet appears, as shown in Fig. 14.1. Many of the Excel window elements—the main menu, toolbar, taskbar, and status bar—are common to Office applications. Therefore, some experience with Word allows you to quickly get used to Excel.

If, when you first start the program, the working window is not maximized whole screen, you can expand it manually by clicking the button located in the upper right corner of the window.

To make visible hidden panels Tools or hide panels that are visible by default, place the pointer over one of the panels and right-click. A list of toolbars appears. Those with a checkbox next to their name are currently visible. Accordingly, to make any of the panels visible, select its name in the list. Selecting this name again will hide the panel.

Rice. 14.1. View of the Microsoft Excel window after starting the program

IN status bar displays information about the status of various keys, including Num Lock. Various messages are displayed on the left, for example about the selected command. When you open a previously created workbook, the status bar graphically shows how complete the process is. The status bar also informs you whether you can enter data or select a command. In this case, it displays Ready. A distinctive property of this line in Excel is the presence auto calculation fields, which shows the result of a predefined function (the default is the sum function) for the selected cells of the current worksheet.

For more information about the Excel interface and its commands, click Help: Microsoft Excel.

The working area of ​​a window is a collection of cells of a large table and is called worksheet. Any sheet is divided into a grid consisting of rows and columns. The number of rows can be up to 65536, and the number of columns - 256. Each column corresponds to a letter, and each line - a number. The intersection of a row and a column is called cell, and each of them has its own name, which is sometimes called cell address. For example, at the intersection of a column A and strings 1 there is a cell A1.

Formula bar

Excel has formula bar, which is located under the toolbars and is designed to process the contents of cells.

It is divided into three parts. The right part is used to display the contents of the current cell, which can be edited using the buttons located in the center of the line. Data editing is carried out either in a cell or in the formula bar. To switch to data editing mode in the formula bar, press the key F2 or click on the right side of the formula bar. To set the data editing mode in a cell, you need to double-click on it, the cell will become active and an input cursor will appear in it.

The middle part of the formula bar contains buttons for processing the contents of the cell. The button with a cross is used to cancel the last action (the action of this button is similar to the action of the button Esc). A button with a check mark is used to confirm data entry or change the contents of a cell; it corresponds to the key Enter. Using the third button, you can activate the formula bar, which allows you to enter and edit formulas.

On the left side of the formula bar there is a name field in which the address of the active cell or the size of the selected range is indicated. For example, record 2R x 3C indicates that the selected range consists of two rows and three columns.

window After launching a Microsoft Excel spreadsheet, a program window appears on the screen, which, depending on the user’s settings, can consist of a different number of elements.

Microsoft Let's look at the program window in the standard setting:

1. Header line 1 , which displays the name of the Microsoft Excel program and, separated by a dash, the name of the open table. When you first launch Microsoft Excel, a new spreadsheet is automatically created, which is assigned the default name Book1. On the right side of the title bar there are three on-screen buttons for controlling the size of the window 2. On the left side is located panel quick access 3, where with standard settings the buttons are displayed from left to right: Saving a Document, Formatting, Viewing and Printing. This panel we can easily customize to suit your needs, i.e. remove unnecessary buttons and add the ones most frequently used in your work. To do this, simply click on the rightmost settings button of this panel 4. As a result, a selection list of shortcut buttons 5 will appear, where by checking or unchecking the corresponding box, we will get a quick access panel optimized for our work.
2. Menu bar 6 , containing eight tabs. Each tab contains a specific set of commands, combined according to some characteristic and displayed on the ribbon 7. For example, we need to insert an object into a book, for which we click on the Insert tab and a set of commands for this tab will be displayed on the ribbon (i.e. we can insert diagrams, text, tables, illustrations, etc.). When the Home tab is active, the ribbon displays commands corresponding to the menu bar tabs of the same name 7 .
3. Formula line 8, which in turn is divided into three parts:
   • The left side displays current cell address 9 . An address is an alphanumeric designation of a cell, meaning at the intersection of which column and which row the current cell is located.
   • In the middle part there are three screen buttons 10, which appear the moment the user begins to enter text, a number, or a formula into a cell. Cancel 11 – is intended to delete erroneously entered information in a cell. You can also press this button by pressing the Esc key on your keyboard. Input 12 – designed to remember information entered into the cell. This on-screen button can also be pressed using the Enter key on the keyboard, or by left-clicking on any other cell. If any formula is entered into a cell, then by pressing this on-screen button, the program begins to make calculations using this formula. Change formula 13 – is intended for editing the formula of the current cell if the cell contains a formula.
   • The right side displays contents of the current cell. If in a cell text or number entered, then on the right side of the formula bar the same text or the same number is displayed. If any formula, then the result of the calculation using this formula is displayed in the cell itself, and the formula itself is displayed on the right side of the formula bar.
4. Framing the worksheet 14 is the area in which the rows and columns of the worksheet are numbered. Moreover columns are numbered with letters of the Latin alphabet A – Z, AA, AB..., and rows are numbered from 1 to 65536. Framing a worksheet allows you to determine the address of the current cell, select the entire column or entire row by clicking on the desired letter or number, change the column width or row height, select all worksheet cells by left-clicking on the empty space between the numbering of rows and columns.
5. Worksheet 15, consisting of 256 columns and 65536 rows (that's approximately 16777216 cells). The spreadsheet file is called a Workbook and can consist of a varying number of worksheets. In the standard setup, the workbook consists of three worksheets. And each worksheet in turn consists of large number of pages. The Microsoft Excel spreadsheet file has the extension *.xls. To move through the table cells, vertical 16 and horizontal 17 scroll bars are used. To move through the worksheet, the user just needs to left-click on the corresponding sheet sheet 18 tab at the bottom of the worksheet. If the number of sheets does not fit within the horizontal scrolling limits, then to move between sheets 19 on-screen buttons located to the left of the shortcuts are used:
   • move to first sheet from list of worksheets 20
   • move to previous sheet from list of worksheets 21
   • move to next sheet from list of worksheets 22
   • move to last sheet from list of worksheets 23

Excel It is possible to assign worksheets individual custom name. To do this, double-click on the corresponding sheet label. In this case, the name of the sheet on the label will be highlighted in black. Next, use the keyboard to enter a new name for this sheet and press the Enter key.

menu You can also change the sequence of worksheets. To do this, you need to move the mouse pointer to the corresponding worksheet shortcut, press the left mouse button and, without releasing it, move the worksheet to the desired location. In this case, the place where the worksheet will be inserted will be indicated by a black arrow.

book In order to add a new sheet to the worksheet, you must click the Insert sheet 24 button or use the keyboard shortcut Shift+F11.

6. Status bar 25, which displays help information of a service nature. Here we can change the type of presentation of the worksheet and its scale using the corresponding on-screen buttons and the slider in the lower right part of the window.

The main elements of the working window are:

Title bar (it indicates the name of the program) with buttons for controlling the program window and document window (Collapse, Minimize to window or Maximize to full screen, Close);

The main menu bar (each menu item is a set of commands united by a common functional focus) plus a window for searching for help information.

Toolbars (Standard, Formatting, etc.).

The formula bar, containing as elements the Name field and the Insert Function (fx) button, is intended for entering and editing values ​​or formulas in cells. The Name field displays the address of the current cell.

Work area (active worksheet).

Scroll bars (vertical and horizontal).

A set of shortcuts (sheet shortcuts) for moving between worksheets.

Status bar.

1.3 Spreadsheet structure

A file created using MS Excel is usually called workbook. You can create as many workbooks as the availability of free memory on the appropriate memory device allows. You can open as many workbooks as you have created. However, only one current (open) workbook can be an active workbook.

Workbook is a set worksheets, each of which has a table structure. The document window displays only the current (active) worksheet with which you are working. Each worksheet has a title, which appears on the worksheet tab at the bottom of the window. Using shortcuts, you can switch to other worksheets included in the same workbook. To rename a worksheet, you need to double-click on its tab and replace the old name with a new one or by executing the following commands: Format menu, Sheet line in the menu list, Rename. Or you can, by placing the mouse pointer on the active worksheet shortcut, right-click, then in the context menu that appears, click on the Rename line and perform the renaming. You can add (insert) new sheets to the workbook or delete unnecessary ones. Inserting a sheet can be done by executing the Insert menu command, line Sheet in the list of menu items. The sheet will be inserted before the active sheet. The above actions can also be performed using the context menu, which is activated by clicking the right mouse button, the pointer of which should be placed on the tab of the corresponding sheet. To swap worksheets, you need to place the mouse pointer on the tab of the sheet being moved, press the left mouse button and drag the tab to the desired location.

Worksheet(table) consists of rows and columns. The columns are headed with capital Latin letters and, further, with two-letter combinations. The worksheet contains a total of 256 columns, named A through IV. The lines are numbered sequentially from 1 to 65536.

At the intersection of columns and rows, cells tables. They are the minimum elements designed to store data. Each cell has its own address. Address cell consists of the column name and row number at the intersection of which the cell is located, for example, A1, B5, DE324. Cell addresses are used when writing formulas that define the relationship between values ​​located in different cells. At the current moment in time, only one cell can be active, which is activated by clicking on it and highlighted with a frame. This frame acts as a cursor in Excel. Data entry and editing operations are always performed only in the active cell.

Data located in adjacent cells that form a rectangular area can be referenced as a single unit in formulas. A group of cells bounded by a rectangular area is called range. The most commonly used rectangular ranges are formed at the intersection of a group of sequential rows and a group of sequential columns. A range of cells is indicated by specifying the address of the first cell and the address of the last cell in the range, separated by a colon, for example, B5:F15. Selecting a range of cells can be done by dragging the mouse pointer from one corner cell to the opposite cell diagonally. The frame of the current (active) cell expands, covering the entire selected range.

To speed up and simplify computational work, Excel puts at the user's disposal a powerful apparatus of worksheet functions that allow almost all possible calculations to be carried out.

In total, MS Excel contains more than 400 worksheet functions (built-in functions). All of them, according to their purpose, are divided into 11 groups (categories):

financial functions;

date and time functions;

arithmetic and trigonometric (mathematical) functions;

statistical functions;

functions of links and substitutions;

database functions (list analysis);

text functions;

logical functions;

information functions (checking properties and values);

engineering functions;

external functions.

Writing any function into a worksheet cell must begin with the equals symbol (=). If a function is used as part of any other complex function or in a formula (megaformula), then the equal symbol (=) is written before this function (formula). Any function is accessed by specifying its name followed by an argument (parameter) or a list of parameters in parentheses. The presence of parentheses is required; they serve as a sign that the name used is the name of a function. List parameters (function arguments) are separated by semicolons (;). Their number should not exceed 30, and the length of a formula containing as many calls to functions as desired should not exceed 1024 characters. When writing (entering) a formula, it is recommended to type all names in lowercase letters, then correctly entered names will be displayed in capital letters.

Main elements worker window are:

• 1. Title bar (it indicates the name of the program) with buttons for controlling the program window and document window (Collapse, Minimize to window or Maximize to full screen, Close);
• 2. Main menu bar (each menu item is a set of commands united by a common functional focus) plus a window for searching for help information.
• 3. Toolbars (Standard, Formatting, etc.).
• 4. The formula bar, containing as elements the Name field and the Insert Function (fx) button, is intended for entering and editing values ​​or formulas in cells. The Name field displays the address of the current cell.
• 5. Work area (active worksheet).
• 6. Scroll bars (vertical and horizontal).
• 7. A set of shortcuts (sheet shortcuts) for moving between worksheets.
• 8. Status bar.

Spreadsheet structure

A file created using MS Excel is usually called working a book. You can create as many workbooks as your availability allows. free memory on the corresponding memory device. You can open as many workbooks as you have created. However, only one current (open) workbook can be an active workbook.

Working book is a set workers sheets, each of which has a table structure. The document window displays only the current (active) worksheet with which you are working. Each worksheet has a title, which appears on the worksheet tab at the bottom of the window. Using shortcuts, you can switch to other worksheets included in the same workbook. To rename a worksheet, you need to double-click on its tab and replace the old name with a new one or by executing the following commands: Format menu, Sheet line in the menu list, Rename. Or you can, by placing the mouse pointer on the active worksheet shortcut, right-click, then in the context menu that appears, click on the Rename line and perform the renaming. You can add (insert) new sheets to the workbook or delete unnecessary ones. Inserting a sheet can be done by executing the Insert menu command, line Sheet in the list of menu items. The sheet will be inserted before the active sheet. The above actions can also be performed using the context menu, which is activated by clicking the right mouse button, the pointer of which should be placed on the tab of the corresponding sheet. To swap worksheets, you need to place the mouse pointer on the tab of the sheet being moved, press the left mouse button and drag the tab to the desired location.

Worker sheet(table) consists of rows and columns. The columns are headed with capital Latin letters and, further, with two-letter combinations. The worksheet contains a total of 256 columns, named A through IV. The lines are numbered sequentially from 1 to 65536.

At the intersection of columns and rows, cells tables. They are the minimum elements designed to store data. Each cell has its own address. Address cell consists of the column name and row number at the intersection of which the cell is located, for example, A1, B5, DE324. Cell addresses are used when writing formulas that define the relationship between values ​​located in different cells. At the current moment in time, only one cell can be active, which is activated by clicking on it and highlighted with a frame. This frame acts as a cursor in Excel. Data entry and editing operations are always performed only in the active cell.

Data located in adjacent cells that form a rectangular area can be referenced as a single unit in formulas. A group of cells bounded by a rectangular area is called range. The most commonly used rectangular ranges are formed at the intersection of a group of sequential rows and a group of sequential columns. A range of cells is indicated by specifying the address of the first cell and the address of the last cell in the range, separated by a colon, for example, B5:F15. Selecting a range of cells can be done by dragging the mouse pointer from one corner cell to the opposite cell diagonally. The frame of the current (active) cell expands, covering the entire selected range.

To speed up and simplify computational work, Excel puts at the user's disposal a powerful apparatus of worksheet functions that allow almost all possible calculations to be carried out.

In total, MS Excel contains more than 400 worksheet functions (built-in functions). All of them, according to their purpose, are divided into 11 groups (categories):

• 1. financial functions;
• 2. date and time functions;
• 3. arithmetic and trigonometric (mathematical) functions;
• 4. statistical functions;
• 5. functions of links and substitutions;
• 6. database functions (list analysis);
• 7. text functions;
• 8. logical functions;
• 9. information functions (checking properties and values);
• 10. engineering functions;
• 11. external functions.

Writing any function into a worksheet cell must begin with the equals symbol (=). If a function is used as part of any other complex function or in a formula (megaformula), then the equal symbol (=) is written before this function (formula). Any function is accessed by specifying its name followed by an argument (parameter) or a list of parameters in parentheses. The presence of parentheses is required; they serve as a sign that the name used is the name of a function. List parameters (function arguments) are separated by semicolons (;). Their number should not exceed 30, and the length of a formula containing as many calls to functions as desired should not exceed 1024 characters. When writing (entering) a formula, it is recommended to type all names in lowercase letters, then correctly entered names will be displayed in capital letters.