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 format, thereby eliminating the need to use 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.
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 select context menu command Rename . You can swap sheets by dragging them by the tabs while holding down 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
A cell in Excel is the main structural element of a sheet where you can enter data and other content. In this lesson, we will learn the basics of working with cells and their contents in order to perform calculations, analyze and organize data in Excel.
Understanding Cells in Excel
Each worksheet in Excel is made up of thousands of rectangles called cells. A cell is the intersection of a row and a column. Columns in Excel are denoted by letters (A, B, C), while rows are denoted by numbers (1, 2, 3).
Based on the row and column, each cell in Excel is given a name, also known as an address. For example, C5 is the cell that is at the intersection of column C and row 5. When you select a cell, its address is displayed in the Name field. Please note that when a cell is selected, the headings of the row and column at the intersection of which it is located become highlighted.
AT Microsoft office Excel has the ability to select multiple cells at once. A set of two or more cells is called a range. Any range, just like a cell, has its own address. In most cases, the address of a range consists of the address of the top left and bottom right cells, separated by a colon. Such a range is called contiguous or continuous. For example, a range that consists of cells B1, B2, B3, B4, and B5 would be written as B1:B5.
The figure below highlights two different ranges of cells:
Select cells in Excel
To enter data or edit the contents of a cell, you must first select it.
You can also select cells using the arrow keys on your keyboard (arrow keys).
Select a range of cells in Excel
When working with Excel, it often becomes necessary to select large group cells or range.
Argument is a value that is used to calculate the values of functions.
Block - a range of cells bordering only on empty cells or column and row headings.
The clipboard is a part of memory designed to temporarily store a copied or cut information object until it is replaced by the next object.
The return value is the value that is the result of the function evaluation.
Selection is the operation of selecting an object on the monitor screen to perform any action with it.
Selection - the procedure for selecting a cell or cells for subsequent work with them. The selected range cells are displayed with a dark background, the active range cell retains a light background.
Alignment is an operation that allows you to place text at the discretion of the user: left or right, centered or justified.
Alignment - The placement of material within a cell, including line direction, character orientation, horizontal and vertical justification, and line break mode.
Typeface - character design style. For example: Times New Roman or Verdana.
Gradient is a command to gradually change the color of text from character to character.
Border - a rectangular frame that represents a selected cell or range
A range is a compact group of table cells that has a rectangular shape.
The key is the field by which the list is sorted.
Copying - an operation in which the contents of the source area are duplicated in the destination area
The autofill marker is a square in the lower right corner of the selected range border.
The parent row is the calculated row of the table, the formulas in which are compiled “manually” (not copied from anywhere).
The optimal solution is the combination input parameters models for which its output parameter is maximum or minimum.
A filtered list is a list in which rows that do not meet the selection (filtering) condition are hidden (but not deleted!)
Transfer is an operation in which the contents of the active cell or selected cells (source area) are moved to another cell or range (destination area).
Editing is the operation of changing the values of previously entered data.
List sorting - sorting the list entries in ascending or descending order by the value of one or more fields.
The status bar is a place to display information about the current state of the workspace and the program, and current prompts.
The formula bar is where formulas are displayed and edited.
The current row is the row in which the active cell is located.
The current column is the column in which the active cell is located.
Filtering is a way to search for data using filters. Filtering differs from sorting in that the filtering process does not reorder the records, but excludes from the set those records that do not meet the specified criteria.
Background - a combination of color and pattern that is used to fill a cell in order to visually enhance it.
A data form is a dialog box in which list fields correspond to input fields, and there are buttons for performing operations with lists.
Formatting is the operation of changing the presentation of data on the screen or in a printed document without changing their values.
A formula is an entry in the content area of a cell that begins with an equal sign and describes the actions that result in the calculation of a value that is placed in the display area of the same cell.
Formula - an expression that serves to perform mathematical and (or) logical operation. The formula includes: Equal sign, cell addresses, numeric constants, arithmetic operations and functions.
A function is a part of a formula that indicates the need to perform standard calculations. A function consists of a function name followed by a list of arguments enclosed in parentheses.
Template - a file that serves as a template for quick creation and filling the same file type.
A cell is an elementary structural element of a table that serves to store data, has an address and two areas: content and display.
A cell is the intersection of a column and a row in a spreadsheet. Each cell has its own address (or name), consisting of a column name and a row number, for example: A1, D3, F5, etc.
Error messages in Excel:
#### – the cell width does not allow displaying the number in the given format.
#DIV/0! - perform division by zero.
#NAME? - Invalid cell name set. It is possible that the Cyrillic alphabet was mistakenly used for typing the names, and not the Latin alphabet.
#NUMBER! - incorrect application of mathematical formulas. For example, extracting the square root of a negative number.
#VALUE! – used invalid type argument. For example: attracting a cell containing text to perform an arithmetic operation.
#EMPTY! - incorrect indication of the intersection of two areas that do not have common cells.
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 point of view Excel 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 and 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 problems of a computational nature, 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 programming language Visual Basic for Application (VBA), 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 in an open application Microsoft Excel you need to 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