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

AT office work Spreadsheets are among the components that are traditionally most requested by users. And a new application Microsoft Excel undoubtedly a great success. This software can be effectively used to automate various areas of office activities. Completely redesigned main interface distinguishing feature Microsoft Excel. Therefore, in this chapter, we will first explain the interface this application.

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

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

The 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 the 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 old versions of Microsoft Excel, and some are new to the user. Here you will see the traditional commands for opening and saving workbooks. Most of the menu (on the right) is occupied by a list of documents that have been opened by the user recently.

At the bottom of the menu (see Fig. 1.2) there is a button Excel Options, which allows you to open a window with many settings for the program. These options in previous versions of Microsoft Excel 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 has 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 the ribbon 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 icon you need. According to the Microsoft concept, the goal of the transition from the classic interface to the new one is to be result-oriented and to solve practical problems as quickly and conveniently as possible. Efficiency is achieved through better organization of teams and greater visibility.

The ribbon is organized as a set of themed tabs containing management tools. Each tab has a name that reflects its purpose. Most of the tools are buttons, individual tools are checkboxes, spinners, combo boxes. The tools on each tab are divided into logical groups. Groups are a block of commands enclosed in a rectangular frame. Like tabs, each group of tools has a name that reflects its purpose. Only one tab is active (selected) at a time. A tab can be activated (selected) with a mouse click, and all groups of tools on that tab are displayed on the screen.

A similar organization of the menu of modern Microsoft versions Excel allows the user to spend less time and go to the necessary commands. Horizontal tabs at first glance complicate the work, but just a few days of practice change this opinion to the opposite.

Basic concepts of MS Excel 3 spreadsheets

Active cell and its modes of operation. four

Range (block) of cells. Range selection.

Changing the height and width of cells, rows and columns 5

Data types and presentation formats 6

Autocomplete cells 7

Calculations using formulas and functions 9

Diagramming 14

Practical work 18

Application: . Error message. 34

Literature 34

Introduction.

MS spreadsheet processor excel refers to the class of application programs designed to create and process tabular data in electronic form. Since 1994 it has been the most popular spreadsheet processor in the world. Program MS Excel, as the market leader in spreadsheet software, sets the trends in this area.

spreadsheet processor MS Excel is designed for creating, formatting and processing spreadsheets and has advanced business graphics tools for easy display of entered and calculated data. The Excel spreadsheet is most widely used in economic and accounting calculations, for compiling various forms, for order processing, production planning, personnel accounting and production costs. Besides, MS Excel has powerful mathematical and engineering functions, which allows you to solve many problems from the field of natural and technical sciences. The possibilities of using this program, of course, go far beyond those mentioned.

One of the most important functional extensions MS Excel is a built-in Visual Basic for Applications (VBA) programming environment designed to solve applied problems in MS Office. Thanks to VBA, Microsoft was able not only to expand the capabilities of the Excel macro language, but also to introduce new level application programming, since VBA allows you to create full-fledged application packages that go far beyond spreadsheet processing in their functions.

spreadsheet processor MS Excel- a program that is used by accountants, economists, financiers, statisticians, engineers, doctors in their professional activities.

The possibilities of the program are very diverse:

· Data entry and editing.

Formatting cells, rows and columns of a table

Entering formulas (automation of calculations)

Application of a wide range of diverse functions

· Construction, editing and printing of diagrams.

・Preview and print table

· Creation and maintenance of databases

Target:

Assistance to teachers in teaching students how to work with a spreadsheet MS Excel: give basic concepts, show features MS Excel, all the advantages of working with formulas and functions using cell references, visual presentation of calculation results using diagrams with examples.

General view of the working window of MS Excel

Interface spreadsheet processor EXCEL is little different from the interface text editor WORD. Interface elements such as the title bar, Top Menu, toolbars, scrollbars, status bar are almost the same, except for the replacement or addition of some buttons (function wizard, chart wizard, etc.) on the toolbar Standard. The special interface elements are the cell name bar, the formula bar, the column name bar, and the row number column. At the bottom of the screen there is a worksheet shortcut area and buttons for scrolling them (sheets). On fig. 1 shows a general view of the MS Excel window.

Rice. 1 General view of the MS Excel window

Basic concepts of MS Excel spreadsheets

The main MS Excel document is a Workbook, which is a *.xls file. The book consists of worksheets (Worksheet), which are designated as Sheet 1 (Sheet 1), etc. (Fig. 1). The 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 label of the sheet to be renamed, and then enter a new name, or using context menu(right-click on the label of the sheet to be renamed). The context menu also allows you to add new leaf to a workbook, delete and copy the selected sheet. A worksheet or spreadsheet consists of 65536 rows (rows) and 256 columns or columns (columns) that are displayed on a computer screen. Rows are numbered with integers from 1 to 65536, and columns are denoted by Latin letters A, B, …, Z, AA, AB, …IV. At the intersection of a row and a column, the main structural element of the table is located - a cell (cell). The contents of a cell can be accessed by its address (link), for example, A5. The address of the selected cell is displayed in the Cell Name Bar.

Excel is a multi-window program. This means that multiple documents (workbooks) can be opened and edited at the same time.

Appearance The presentation of the information contained in the spreadsheet can be changed by changing the data format (menu Cell Format), as well as setting the design of cells, changing their width (menu Format Row/Column), height and color (menu Cell Format).

For Excel, each file (document) is called a workbook, it contains worksheets. The active sheet of a workbook is the working window of Excel. Only one work window can be active at any given time, even if you have several workbooks open. It is in it that you can carry out operations. It is very important to be able to customize the windows, because. well-organized workspace comfortable work with the program.

Three States of the Excel Working Window

In the right upper corner windows (in the title bar) there are 5 buttons, 3 of which are designed to work with windows:

1. Excel Help
2. Ribbon display options
3. Minimize a window
4. Restore window / Maximize window
5. Close a window

The workbook window can be in one of three states:

Setting the sizes of working windows

If you use several books, you may need to display several working windows on the screen at once. To do this, we use the following methods:

Switching between Excel windows

As I said earlier, only one window from all displayed on the screen can be active for work. To switch active windows, I know 4 ways:

1. click mouse. This is the easiest method, if at least part of the window is visible on the screen, feel free to click on it. The window will be activated and displayed on top of the rest
2. Select from the Windows taskbar- one of the most popular ways. Just click on the label of the desired workbook
3. Use Hotkeys. To cycle through working windows, press Ctrl+Tab (forward) and Shift+Ctrl+Tab (reverse) on your keyboard. The analogue of this combination is Ctrl+F6 and Shift+Ctrl+F6 .
4. Using the ribbon command. Run the command: View - Window - Switch to another window. A list of available windows will open, select and go

Multiple windows on one screen

Sometimes you need to open multiple windows on the screen to provide joint work with them. This is especially true when the resolution of the computer screen is large enough. A command block is responsible for working with multiple windows. Home — View — Window.

One document in multiple windows

To open another instance in a new window already open window- click Home - View - Window - New Window. Now click Arrange All to choose the layout of the Excel windows on the screen. Select one of the screen layout options:

1. Beside
2. Top down
3. From left to right
4. cascade

So, you can automatically, without manual setting Place both Excel windows on the same screen.

Splitting a window into two areas

Another way is to split the current window instead of creating a new one. Can be divided into 2 or 4 sections.

To divide a window into 4 sections- perform the following sequence of actions:

To split a window into 2 sections, select not a cell, but an entire row (under the split line) or a column (to the right of the split line). Click Split again.

Each area will have its own scrollbars, you can manage them separately. But you're still working in the same document, so any changes you make will show up in each area.

Different books on one screen

We have discussed how to display multiple samples of the same workbook on the same screen. You can do the same with various documents. Discover all excel files to fit on the screen. Click Arrange All again to specify how you want to arrange the windows.

Synchronous window scrolling

As you already understood, when several windows are displayed on the screen, they are scrolled separately for each of the windows. But what if you are looking at some lists for compliance, and you cannot use for these purposes? It would be nice to have all windows scroll at the same time.

This is easy to implement. Click no ribbon View - Window - Synchronous Scrolling. It's that simple, you don't need to do anything else!

Closing the window

If you have finished in the workbook, close it to free up the computer's virtual memory. There are at least 4 ways to do this:

1. Click the Close button (x) in the book's address bar
2. Run the command File - Close
3. Use the keyboard shortcut Ctrl+W
4. Use the key combination Alt+F4 . This command ends Excel, all windows will be closed

If you did not save the document before closing, the program will ask if you need to save. Select Save to save all changes and close the file; Do not save - to close the book without saving; Cancel - leave the workbook open, do not save changes.

Many people underestimate the possibilities that the correct work with Microsoft Excel windows gives. Practice applying all of the above rules and you will realize that they can be of great benefit if the information is in several files and each of them should always be at hand. If the data is in different sheets one workbook - mine will be useful to you, I recommend adding it to your browser bookmarks.

If you do not understand something from this material - write comments, I will definitely answer. Always yours, Alexander Tomm.

Main elements working window are:

• 1. Title bar (it contains the name of the program) with buttons for controlling the program window and the document window (Minimize, Minimize to window or Expand to full screen, Close);
• 2. 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.
• 3. Toolbars (Standard, Formatting, etc.).
• 4. The formula bar, which contains the Name field and the Insert Function (fx) button as elements, is intended for entering and editing values ​​or formulas in cells. The Name field displays the address of the current cell.
• 5. Workspace (active worksheet).
• 6. Scroll bars (vertical and horizontal).
• 7. A set of labels (sheet labels) for moving between worksheets.
• 8. Status bar.

Structure of spreadsheets

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

Working book is a set workers sheets, each of which has a tabular structure. In the document window, only the current (active) worksheet is displayed, with which work is being carried out. Each worksheet has a title that appears on the sheet tab at the bottom of the window. With the help of shortcuts, you can switch to other worksheets included in the same workbook. To rename a worksheet, double-click on its tab and replace the old name with the new one, or by executing the following commands: Format menu, Sheet line in the menu list, Rename. Or, by placing the mouse pointer on the shortcut of the active worksheet, right-click, and then in the context menu that appears, click on the Rename line and perform 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, the Sheet line 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 pressing the right mouse button, the pointer of which should be set 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, click left button mouse and drag the tab to the desired location.

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

At the intersection of columns and rows, cells tables. They are the minimum elements for storing 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 determine the relationship between values ​​located in different cells. At the current time, only one cell can be active, which is activated by clicking on it and is highlighted with a frame. This frame in Excel acts as a cursor. 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 referred to in formulas as a whole. 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 consecutive rows and a group of consecutive columns. A range of cells is designated by specifying the address of the first cell and the address of the last cell of 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 computing Excel work provides the user with a powerful apparatus of worksheet functions, allowing to carry out almost all possible calculations.

Overall, MS Excel contains over 400 worksheet functions (built-in functions). All of them, in accordance with 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. informational functions (checking properties and values);
• 10. engineering functions;
• 11. external functions.

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

Rice. 3.1. Main window of MS Excel

In the figure, the number 1 indicates horizontal menu, and the number 15 is the status line of this program.

The book is made up of sheets. Each sheet is a separate spreadsheet, which consists of rows and columns, and has a unique name (areas with sheet names - sheet labels - are indicated in the figure by numbers

12 and 13 respectively for active and inactive sheets). By analogy with the active cell, the active sheet is the sheet with which the user is currently working. In order to make a sheet active, just click on the sheet name or select the desired sheet by successively pressing the key combination Ctrl + PgUp (sheets will be sequentially moved from the last to the first) or Ctrl + PgDn (the sheets will be sequentially moved from the first to the last).

Rows are numbered with numbers from 1 to 65536 (vertical area, indicated by number 8 in the figure), and columns - by English letters (horizontal area, indicated by number

7). The maximum number of rows is 65536 and the maximum number of columns is 256. Because in English language Since there are 26 letters in total, the first 26 columns are designated by letters from "A" to "Z" (as shown in the figure), and then their combinations are used. For example, AB, AC, AD, ...

Cells are located at the intersection of the rows and columns of the sheet (for example, one of the cells is indicated in the figure by the number 9). Each MS Excel cell has an address consisting of a column index and a row number. For example, Al, B12, AZ83 and so on. The cell highlighted in the figure by the number 9 has the address D2.

Sometimes it is convenient to associate a certain cell of a given sheet with its own name and subsequently refer to it by this name. To assign a name to a cell, go to the address display area (in the figure, area 5) and enter a name that will be available from any sheet of the book. A named reference is considered absolute. MS Excel strictly regulates how cells and ranges can and cannot be named. Here are the rules:

Cell and range names can be up to 255 characters long;

The name must begin with a letter or with the character "_";

The name can only contain letters, numbers, periods, and underscores;

Spaces are not allowed;

The name of a cell cannot be the same as its address or the value it contains;

You cannot use a single letter or number as a name without any punctuation or additional characters.

One cell on the sheet is active (in the figure, this is cell B4, which is indicated by the number 10).

It is surrounded by a thick frame, and its address is displayed in the special Name field (in the figure, area 5). Data entry is always carried out only in the active cell. At the same time, its contents are duplicated in the area indicated in the figure by the number 6 and called the Formula Bar. There are several ways to change the active cell:

Using the cursor keys;

Mouse click on the desired cell;

Using the Name field, in which you need to write down or select the name of the cell from the drop-down list.

You can navigate through the MS Excel sheet using the scrollbars (scrolling), which are indicated in the figure by the numbers 11 (for vertical scrolling) and 14 (for horizontal scrolling).

Toolbars - tool for quick access to frequently used MS Excel commands - are indicated in the figure by the number 4. Commands are displayed as buttons with icons or text. Two additional toolbar areas have a special purpose. Area 2 is designed to change the position of the panel in the MS Excel window, and area 3 is used in cases where, due to the size of the window, it is impossible to display all the toolbar commands in it.

The number of worksheet cells that are simultaneously displayed in the MS Excel window is determined by the display scale. The display scale can be changed (increasing or decreasing the amount of displayed information) using the Scale button on the Standard toolbar.