[x] in which commands are entered.
to record commands;
for charting
to create a table
MS Excel 2010. Which tab contains the commands: Ruler, Formula Bar, Grid, Headings?
[x] View
home
Insert
Formulas
data
MS Excel 2010. What group of commands do the following commands belong to: Freeze Panes, Arrange All, New Window?
[x] Window
Macros
Scale
Show
Paragraph
MS Excel. What command did the student use to automatically convert the grade from letter to number?
[x] Macro
Formula
Replacement
AutoSum
Number
MS Excel 2010. What will the student take as a basis when building a diagram?
[x] Table data
Excel workbook
Number of sheets
Cell Format
no answer
[x] COUNT
AVERAGE
SUM
EMPTY
IF
MS Excel 2010. The student needs to determine the day of the week in the numerical format of the given date. Which function should be used?
[x] DAYWEEK
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 to exponentiate?
[x] POWER
ROUND
CHECK
SUM
MAX
45. MS Excel 2010. Specify which functions have no arguments:
[x] TODAY, PI
IF
COUNT, AVERAGE
RANK, RANGE
SUM, MIN
MS Excel 2010. When moving a chart from one sheet to another, a student used the “Move Chart” command. What tab is this command in?
[x] Constructor
Layout
Format
home
Insert
MS Excel 2010. The student needs to 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 in the diagram needs to indicate the data label over the points. What additional option should he choose?
[x] Top
Left
On right
Bottom
Centered
MS Excel 2010. The student needs to choose the type of diagram for the best display of the morbidity criteria for different age groups. What type of diagram will he not be able to use?
[x] tree
bar graph
with areas
circular
schedule
MS Excel 2010. In which tab is the command to select data for plotting a chart?
[x] Constructor
Layout
Format
Diagram
Insert
MS Excel 2010. In the finished chart, you need to change the background. What tab is this feature in?
[x] Format
View
Constructor
Diagram
Very often, when working in Excel, you need to use data about addressing cells in a spreadsheet. For this, the CELL function was provided. Let's consider its use on specific examples.
Function value and cell properties in Excel
It is worth noting that Excel uses several functions for addressing cells:
- - LINE;
- - COLUMN and others.
The CELL() function, the English version of CELL(), returns information about the formatting, address, or contents of a cell. The function can return detailed information about the cell's format, thereby eliminating the need for VBA in some cases. The function is especially useful if you need to display the full path of the file in the cells.
How does the CELL function work in Excel?
The CELL function in its work uses a syntax that consists of two arguments:
Examples of using the CELL function in Excel
Example 1. Given a table of accounting for the work of employees of an organization of the form:
It is necessary, using the CELL function, to calculate in which row and column the salary of 235,000 rubles is located.
To do this, we introduce a formula of the following form:
- – “row” and “column” – output parameter;
- – С8 – data address with salary.
As a result of calculations, we get: row No. 8 and column No. 3 (C).
How to find out the width of an excel table?
Example 2. You need to calculate the width of the table in characters. It should be noted right away that in Excel, by default, the width of columns and cells is measured in the number of characters that they fit in their value are available for display in a cell without a line break.
Note. The height of rows and cells in Excel, by default, is measured in base font units - in pt points. The larger the font, the higher the line to fully display the characters in height.
Enter in cell C14 a formula to calculate the sum of the widths of each column of the table:
- – “width” – function parameter;
- – A1 – the width of a certain column.
How to get the value of the first cell in a range
Example 3. In the condition of example 1, you need to display the content only from the first (upper left) cell from the range A5:C8.
Let's introduce a formula for calculation:
The description of the formula is similar to the previous two examples.
The active (selected, current) cell is the worksheet cell that you are currently working with. An active cell is always marked either by thickening its border lines when it is the only selected cell, or by inverting its background color when it is one of several selected cells (block of cells).
By default, data entry and many other operations in MS Excel are associated with the active cell.
15. MS Excel interface
The MS Excel program, along with other application programs, MS Windows supports both a "mouse" and a keyboard interface - a set of tools for controlling the operation of an application program and information processing techniques. The fact is that sometimes it is obviously more convenient to use the mouse to perform some operations, and the keyboard for others. As a result, it is useful for the user of MS Excel to know the features of the application and to have the skills to work with both one and the other interface. Moreover, if a mouse is not installed on the computer or, for various reasons, it does not work reliably, then the only available means of controlling the operation of the MS Excel program is the keyboard.
In addition, the variety of ways to perform the same operations that the MS Excel program makes available to the user allows him to independently form his own style of working with this program, based on his personal preferences and primary skills in working with previously mastered PC software.
Lab #1
"GETTING STARTED WITH MS EXCEL"
1.1. Launching ms Excel
Start the MS Excel program by executing the command "Start / Programs / (MicrosoftOffice) /MicrosoftExcel".
In the MS Windows operating system, the MS Excel shortcut may also be located on the desktop or in the Quick Launch bar.
1.2. The structure and elements of the working window of the ms Excel program
Expand the working window of the MS Excel program to the full screen of the display. With the standard (initial) setting of the parameters of the MS Excel program, its working window has the form shown in fig. one.
Rice. 1. The structure and elements of the working window of the MS Excel program.
Below is a brief description of the elements of the graphical interface of the working window of MS Excel.
1. System menu buttons for controlling the behavior of the MS Excel window (upper button) and the window with the active workbook (lower button). Double clicking on any of these buttons closes the corresponding window.
2. Header line containing the name of the program (Microsoft Excel) and the name of the active workbook (Book 1). The name of the workbook corresponds to the name of the file in which it is stored. Each newly created book gets the default name: "Book N", where N is the serial number of the newly created workbook in this session with MS Excel.
3. Main menu bar of MS Excel.
4. Buttons for controlling the behavior of the MS Excel window (upper group of buttons) and the window with the active workbook (lower button).
5. Lines with toolbars: "Standard" and "Formatting".
6. Button for selecting (selecting) all cells of the current sheet of the workbook at once with a single mouse click.
7. Name field. This window displays the address (name) of the active cell or block of cells.
8. Formula bar (it displays the entered information in the active cell and edits the previously entered information).
9. Control buttons for entering information into the active cell. The left button cancels the current entry of information, the middle button confirms the correctness of the information entered, and the right button is used to insert the desired function into the cell using the "Function Wizard".
10. Buttons with headings (names) of columns. They are used to select (select) all the cells of a column at once or change their size in width.
11. Marker for horizontal splitting of the sheet into two workspaces.
12. Buttons with titles (names) of rows. They are used to select (select) all cells in a row at once or change their size in height.
13. Active (selected, current) cell (C7) on the worksheet.
14. Buttons for navigating through the sheets of the workbook. The outer buttons are used to quickly move to the beginning or end of the list of sheets, and the inner buttons are used to step forward and backward.
15. Labels with the names of workbook sheets. The name of the active (current) sheet is displayed in bold on a light background.
16. Marker for changing the width of the display area for workbook sheet labels.
17. Vertical and horizontal scroll bars (broaches).
18. Marker for vertical splitting of the sheet into two workspaces.
19. Status bar status. It displays messages about the operating mode of the MS Excel program, in which it is currently located.
20. Display area of the activated operating modes of the MS Excel program (VDL - work in the mode of keyboard selection of cells; CAPS - the mode of entering capital letters; NUM - the mode of entering numbers using the keys of the small numeric keypad; SCRL - the mode of stopping the scrolling of the ET sheet when navigating in the working window; OVR - entering information into a cell in the mode of replacing its contents).
EXERCISE 1. Without performing any mouse clicks, one by one place its cursor on all the elements of the working window of the MS Excel program described above.
Please note and remember that:
- in the process of such navigation, sometimes the geometric shape of the mouse cursor changes depending on which object (element of the object) it currently points to;
– if you place the mouse cursor on any of the toolbar buttons and do not move it for one or two seconds, you can get a hint about the purpose of this button.
1. What is a spreadsheet (ET)
Spreadsheet - a computer program that allows you to perform calculations with data presented in the form of two-dimensional arrays that mimic paper tables
2. Expand the concepts: row, column, cell, block of cells.
Title bar is used to display the name of the application.
Input and edit 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 jumping to any required page and for zooming.
Prompt line is designed to provide information to the user about his possible actions at the current moment.
Cell The 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 at the intersection of the first row and column A, which is in fourth place. Not only a cell can have an address, but also a group of adjacent cells block (range) of cells. A cell block can be 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 their parts. A block of cells is specified by specifying the address of its first and last cells, between which a separating sign is placed - a colon (B2: D5).
3. What is a cell address?
4. What cell is called current (active)?
Table hotspot called its part filled with data
5. How is absolute addressing different from relative addressing?
6. What can be the content of the ET cell?
Different tables may contain completely different information. Some cells contain text, some cells contain numeric data. From the point of view of Excel, a cell can contain three kinds of data.
Text the data is a string of text of arbitrary length. Excel reproduces such data exactly as entered. Such a cell cannot be used in calculations. If Excel cannot interpret the data as a number or as a formula, it considers it to be text data.
Numeric data is a single number entered into a cell. They cannot contain alphabetic or special characters, because mathematical operations are performed on them. The only exceptions are the decimal point (comma) and the number sign that precedes it. How numbers are considered data that define dates or amounts of money.
Formulas are instructions on which calculations are performed. In this case, the formula itself may not be visible on the screen, and the result of calculations based on it will be presented in the cell. The content of a cell is treated as a formula if it starts 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 in italics).
2. Entering a formula begins with the “=” sign. This character is entered from the keyboard.
3. After entering the “=” sign, Excel enters the formula input mode. In this mode, when a cell is selected, its address is automatically entered into the formula. This saves the user from having to know cell addresses and enter them into the formula from the keyboard.
4. Being in the formula input mode, you successively point with the left mouse button to the cells that store some numerical values, and enter the operation signs between the original values from the keyboard.
§ Operation signs must be entered between cell addresses.
§ It is more convenient to enter operation characters from the right numeric keypad. In order for this block to work in the desired mode, the indicator
5. In order for the calculation result to appear in the active cell, you must exit the formula input mode.
§
§ “Green check mark” on the formula input panel completes the formula entry, and leaves the cursor in the same cell.
9. Name the features of Excel
Spreadsheet MS Excel (spreadsheets) is one of the most commonly used applications of the integrated MS Office package, a powerful tool in capable hands that greatly simplifies 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 makes it easier to work with data and allows you to get results without programming calculations. In combination with the Visual Basic for Application (VBA) programming language, the MS Excel spreadsheet becomes universal and allows you to solve any problem at all, 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 cell currently selected is called active cell.
11. What is the sequence of creating ET.
To create a new book With Microsoft Excel open, do one of the following:
Click on the button Create on the panel Standard ;
Press a keyboard shortcut<ctrl>+ <N>;
Execute menu command File -Create and then on the panel that pops up on the right
In chapter Creation command blank book (Fig. 8.3).
After that, a new Excel document will open in front of you, ready to work. The program will give the new document a type name 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 in a ET cell?
Rules for writing formulas in Excel:
two operation symbols must not be placed 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);
formula cannot contain more than 1024 characters, formulas can be divided into many simpler ones (principle of superposition).
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 a formula.
Enter character “= ” and formula symbols (zero integers can be omitted when entering). Please note that the word is displayed on the left side of the status bar Input. Entered characters simultaneously appear in the cell A3 and in formula bar.
Fix the manual entry of the formula - press the key
The formula will appear in the formula bar when the corresponding cell is active.
Instead of a formula, you can enter a constant in a cell (directly the value is 1.35).
14. How is a formula copied? 5 How to fix 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 sheet. 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
Purpose and main features of the programMSEXCEL. Program interface. The main elements of the interface. The concept of a spreadsheet, cells, rows, columns, addressing system. Movement on the table field. Data input. Data types. Editing the contents of a cell. Changing the width and height of a cell. Cell properties (“Format Cells” command).
Target: Get familiar with spreadsheet capabilities MSEXCEL, the concept of “spreadsheet” (cell, row, column, addressing system, etc.) Repeat the software classification.
MS spreadsheet processor EXCEL belongs to the class of application programs. Since 1994 it has been the most popular spreadsheet processor in the world.
The purpose of the program is to automate calculations, build business graphics, create spreadsheet documents, and maintain databases.
Spreadsheet processor MS EXCEL is a program that is used by office managers, economists, financiers, statisticians in their professional activities.
Program features
Entering and editing data.
Formatting Table Cells, Rows, and Columns
Entering formulas (calculation automation)
Application of a wide range of diverse functions
Construction, editing and printing of diagrams.
Table preview and print
Creation and maintenance of databases
Program download
START - PROGRAMS -MS EXCEL
Program interface
Interface elements:
Title bar
Top Menu
Toolbar
Input line
table field
Status bar
Spreadsheet - a set of rows and columns, the columns are denoted by letters of the Latin alphabet, and the rows by numbers. The intersection of a row and a column is called a cell or cell. Thus, the cell address is formed from the column name and row number, for example, A1, B12. In total, the worksheet can contain 247 columns and 65536 rows. With a single cell area of 169 mm 2 for a 14' screen, one work sheet covers an area of 2735.7 m 2, which is about 60% of the area of a football field.
3 sheets are displayed in the working field of the screen in Excel, the active one is Sheet 1. To switch to another sheet, you need to click on its tab with the mouse.
For making a copy from a sheet you need to activate it, then with the key pressed ctrl grab the tab of the copied sheet with the left mouse button, without releasing it, drag it to the left or right and release the mouse button first, then ctrl. At the same time, a sheet image with a “+” sign is added to the cursor arrow. The copy is automatically named, for example, Sheet 1(2) for the first copy, Sheet 1(3) for the second, and so on. You can change the sheet name by clicking on the tab. right mouse button and selecting the command from the context menu Rename . You can swap sheets by dragging them by the tabs while pressing the key shift.
Active cell is the cell where the cursor is located. It is highlighted with a dark frame when you click on it with the mouse. Moving between cells is done with the mouse or cursor keys.
Interval (block) of cells is given by the addresses of the upper left and lower right cells, separated by a colon, for example, A1:C4. To select a block of cells, you can use the mouse or the cursor keys while pressing the key Shift. To select a column or row, click on the heading of the desired column or row. One of the techniques for selecting non-contiguous areas, for example, A1:A10 and C1:C10, is the use of the mouse when the key is pressed Ctrl.
Selected elements of a table or worksheet are moved and copied using the mouse or via the clipboard: commands Cut, Copy, Paste.
You can increase the width of the cell by placing the cursor on the border between the letters of the columns until it turns into a double-headed arrow and by pressing the left mouse button, move the border to the desired distance.
Increasing the height of the cell is performed similarly when placing the cursor on the border between the digits of the lines.
In addition, the row height and column width of the table is adjusted using the commands Line and Column from the menu Format. To change the column width with the mouse, select the cell or the entire column, select the command Format\Column\Width... and enter the desired width in millimeters in the window Column Width . Team AutoFit Width increases the width of the cell according to the length of the characters entered, commands Hide or display remove or insert a marked column. Similarly, the line height changes, its auto-selection, deletion or insertion of a line when using menu commands Format\String.
Table field movement
Mouse
Using the cursor keys
Using the paging keys PgUp , PgDn
Using the HOME, END keys
