How to get a value from Excel cell. Cell function in Excel and examples of its use. Function Values \u200b\u200band Cell Properties in Excel

Returns information on formatting, location or cell content.

Syntax:

Get a host (n_tipa; link)

NOM_TIPA - The number defining which type of cell information you want to get. The following list shows the possible values \u200b\u200bof the argument and the corresponding results.

Nizhipa returns

1 Absolute link top left cell in argument Link as text in the current style of the workspace.
2 Line number of the upper cell in the argument link.
3 The number of the upper cell column in the argument link.
4 Same as the type (link).
5 Content argument link.
6 The formula in the argument reference in the form of text, the style of which A1 or R1C1 - depending on the parameters of the working space.
7 Cell format number (for example, M / D / GG or "Main").
8 Number showing horizontal cell alignment:

1 \u003d Normal
2 \u003d left
3 \u003d centered
4 \u003d Right
5 \u003d Fill out
6 \u003d on both edges
7 \u003d centen through cells

9 Number, showing the style of the left border, assigned to the cell:

0 \u003d no border
1 \u003d thin line
2 \u003d Middle Line
3 \u003d Bar Line
4 \u003d dotted line
5 \u003d thick line
6 \u003d Double Line
7 \u003d thinner

10 Number, showing the style of the right border, assigned to the cell. Return numbers, see the description of the argument number 9.
11 Number, showing the style of the upper bound, assigned to the cell. Return numbers, see the description of the argument number 9.
12 Number indicating the bottom of the bottom of the cell. Return numbers, see the description of the argument number 9.
13 Number from 0 to 18, showing the selected cell as displayed on the "Pattern" panel of the dialog box of the cells, which appears if you select the cell command in the format menu. If the pattern is not selected, the value 0 is returned.
14 If the cell is blocked, the value of truth is returned, otherwise the value of the lie is returned.
15 If the cell is hidden, the value of the truth is returned, otherwise the lie returns.
16 The horizontal array of two elements containing the width of the active cell and the logical value indicating whether the cell width is set to the standard value (truth) or to the user (lies).
17 Cell height at points.
18 Font name in the form of text.
19 Font size at points.
20 If all cell characters or only the first character are highlighted in bold, the value of the truth is returned, otherwise a lie returns.
21 If all cell characters or only the first character are in italics, the value of truth is returned, otherwise a lie returns.
22 If all cell characters or only the first symbol are highlighted, the value of the truth is returned, otherwise a lie returns.
23 If all cell characters or only the first symbol are highlighted, the value of the truth is returned, otherwise a lie returns.
24 Number from 1 to 56, indicating the color of the font. If the color of the font is selected automatically, the value 0 is returned.
25 If all cell characters or only the first symbol are circled with a contour, the value of truth is returned, otherwise a lie returns. This type is not supported. Microsoft Excel. For Windows.
26 If all cell characters or only the first character are rolled, the value of the truth is returned, otherwise a lie returns. This type is not supported by Microsoft Excel for Windows.
27 Number showing whether the partition breaks next to the cell:

0 \u003d not broken
1 \u003d on line
2 \u003d in columns
3 \u003d and on lines and by columns

28 Line level (contour).
29 Column level (contour).
30 If the contents of the active cell line is the final line, the truth is returned, otherwise a lie returns.
31 If the contents of the active cell line is the final column, the truth is returned, otherwise a lie returns.
32 Name of the working book and a sheet containing a cell. If the window contains only one sheet with the same name as the working book without expansion, only the name of the book in the form of Book1.xls is returned. Otherwise, the name of the sheet is returned in the form "[Book1] list1".
33 If the cell is formatted with the transfer according to the words, the truth is returned, otherwise a lie returns.
34 Number from 1 to 56, denoting the color of the left border. If the color is automatically selected, 0.
35 Number from 1 to 56, denoting the color of the right border. If the color is automatically selected, 0.
36 Number from 1 to 56, denoting the color of the upper border. If the color is automatically selected, 0.
37 Number from 1 to 56, denoting the color of the lower boundary. If the color is automatically selected, 0.
38 Number from 1 to 56, indicating the color of the foreground shadow. If the color is automatically selected, 0.
39 Number from 1 to 56, indicating the color of the shadow of the background. If the color is automatically selected, 0.
40 Cell style in the form of text.
41 returns the formula in the active cell (useful for international formats of macros sheets).
42 Horizontal distance measured at points from the left edge active window to the left edge of the cell. It may be a negative number if the window is scrolled outside the cell.
43 Vertical distance measured at points from the top edge of the active window to the top edge of the cell. It may be a negative number if the window is scrolled outside the cell.
44 Horizontal distance measured at points from the left edge of the active window to the right edge of the cell. It may be a negative number if the window is scrolled outside the cell.
45 Vertical distance measured at points from the top edge of the active window to the lower edge of the cell. It may be a negative number if the window is scrolled outside the cell.
46 If the cell contains a text note, truth is returned, otherwise a lie returns.
47 If the cell contains a sound note, truth is returned, otherwise a lie returns.
48 If the cell contains a formula, truth is returned; If it contains a constant - a lie returns.
49 If the cell is part of the array, the truth is returned, otherwise a lie returns
50 Number, showing vertical cell alignment:

1 \u003d up
2 \u003d centered
3 \u003d down
4 \u003d on both edges

51 Number, showing vertical cell orientation:

0 \u003d Gorizonotal
1 \u003d Vertical
2 \u003d directed up
3 \u003d directed down

52 Cell prefix symbol (or text alignment) or empty text ("") if the cell does not contain text.
53 The contents of the cell if it is in the data moment is displayed in the form of text, which includes any additional digits or symbols that are the result of the cell formatting.
54 Returns a name summary Tablecontaining an active cell.
55 Returns the position of the cell inside the pivot table.
56 Returns the name of the field containing a reference to the active cell if it is inside the consolidated table.
57 If all cell characters or only the first symbol are formatted with an adhesive font, the value of truth is returned, otherwise a lie returns.
58 Returns the font style in the form of the text of all cell characters or only the first character, as shown in the cell format dialog box on the Font tab. For example, "bold italic".

59 Returns the figure for an "underscore" style:

1 \u003d no
2 \u003d single
3 \u003d Double
4 \u003d single cash
5 \u003d double money

60 If all cell characters or only the first character are formatted with a substitution font, the value of truth is returned, otherwise a lie returns.
61 Returns the name of the pivot table element for the active cell in the form of text.
62 Returns the name of the working book and the current sheet in the form "[Book1] list1".
63 Fills the Color Cell (background).
64 Returns the cell background pattern.
65 Returns the value of the truth if the equalization parameter is enabled (only for Microsoft Excel for Far East only); Otherwise, returns a lie.
66 Returns a working book name containing a cell in the form of Book1.xls.

Examples:

The following macro formula returns the value of truth if the B4 cell on the sheet 1 is highlighted in bold:

I caught myself thinking that it was not fully mastered the functionality of the function \u003d cell () (Cell in the English version). I'm fixed. Next, I will tell you more about the functionality of the function and how to apply it in practice, for example, how to find a row number or column, cell address, or even the address and file name. The cell function in Excel, disassemble more.

Who knows VBA, surprised, the functional is easily replaced by software. But we are engaged in optimizing without programming, for users.

If in a nutshell, then the function \u003d cell () works with cell properties and has two arguments

• type type - Mandatory props, text, filled with arguments to choose from the table below
• link — optional argument, Link address to the cell, for some types of information, such as "Name File" it does not need to be specified. If the argument is the range, because The result of the upper left cell is standardly displayed.

Table of argument Type typepresented in the figure at the beginning of the page, the table is lower, as well as in the example (the list is presented in the formula sequence)

Type type	Description	Cell value	Returns
"address"	Cell address in A1 format (if the range is the top left cell)		$ D $ 3
"protection"	0, if the cell is unlocked in the cell format, and 1, if the cell is blocked.		1
"File name"	The full path to the file, including the name and name of the sheet. For incomcoming sheet is returned empty "" C: \\ users \\ .... \\ Example.xlsx		C: \\ Users \\ User \\ Desktop \\ [Function Cell.XLSX] 1
"prefix"	Text. Code denoting cell alignment, decoding below	text	^
"brackets"	1, if the format of negative numbers in brackets; Otherwise - 0.		0
"content"	Not formula, cell value	5	5
"column"	The column number of the argument "Link".		4
"line"	The line number of the argument "Link".		10
"a type"	Text. Depends on the type of data, the value "B" is an empty cell, "l" - constant, "V" - any other content.	1	v.
"format"	Text. The letter corresponds to a specific format (). Values \u200b\u200bfor various formats are shown below in the table. If color changes with negative values, then added "-".		C2.
"color"	1, if the cell changes color for negative values; Otherwise \u003d 0 (zero)		0
"width"	Integer. Column width of the reference		8

Decoding type of information " prefix»

1. single quotion or apostraf (') text aligned left,
2. (") - text, leveled to the right,
3. (^) - text, aligned in the center,
4. (/) - text, distributed across the entire width of the cell,
5. Empty ("") - any other content of the cell.

Cell function in Excel. How to apply?

How to find the line / column number and the file address I think you are understood from the table.

We will analyze more interesting examples:

1. It should be noted in the column all cells starting from 5 and write 1 in them

The cell function returns information about formatting, location or cell content.

Description of the cell function

The function returns information about formatting, location or cell content.

For example, if you need to make sure that it contains a numeric value, and not the text, you can use the following formula:

If (cell ("type"; A1) \u003d "V"; A1 * 2; 0) This formula calculates the product A1 * 2, only if a numeric value contains a numeric value in the cell, and returns the value 0 if the A1 cell contains text or It is empty.

Syntax

\u003d Cell (type type; [link])

Arguments

Mandatory argument. Text value specifying the type of cell information upon return. The list below shows the possible values \u200b\u200bof the "Type_inction" argument and the corresponding results.

Type type	Return value
"address"	Link to the first cell in the "Link" argument in the form of a text string.
"column"	Cell column number in the argument "Link".
"color"	1, if the cell formatting provides a color change for negative values; In all other cases - 0 (zero).
"content"	The value of the left upper cell in the link; Not formula.
"File name"	File name (including full path) containing a link in the form of a text string. If the list containing the link has not yet been saved, the empty line is returned (").
"format"	A text value corresponding to a numeric cell format. Values \u200b\u200bfor various formats are shown below in the table. If the cell changes the color when deriving negative values, at the end text value Added "-". If positive or all numbers are displayed in parentheses, at the end of the text value () is added.
"brackets"	1, if the cell formatting is provided by the display of positive or all numbers in parentheses; In all other cases - 0.
"prefix"	Text value corresponding to the prefix of the cell tag. Single quotation (') corresponds to the text to the left, double quotation (") - text, aligned to the right, the lid sign (^) - text, aligned in the center, reverse flank () - text distributed across the entire width of the cell, and empty Text (") - any other cell content.
"protection"	0, if the cell is unlocked, and 1, if the cell is blocked.
"line"	The cell number number in the "Link" argument.
"a type"	A text value corresponding to the type of data in the cell. The value "b" corresponds to a blank cell, "L" - text constant in the cell, "V" - any other content.
"width"	The width of the cell column, rounded to an integer. Unit of measurement is equal to the width of one sign for the font of the standard size.

Attention! In Excel Web App. No support for the format argument.

Optional argument. Cell, information about which you want to get. If this argument is omitted, the information specified in the "Software Type" argument is returned for the latest modified cell. If the reference argument indicates the range of cells, the cell function returns information only for the left upper range of the range.

Cell format codes

Attention! If the argument "Type_name" of the cell function is "format" value, and the cell format has been changed, to update the value of the cell function, you need to recalculate the sheet.

The formula prescribes excel program Procedure with numbers, values \u200b\u200bin a cell or group of cells. Without formulas, electronic tables are not needed in principle.

The design of the formula includes: constants, operators, references, functions, range names, Round brackets containing arguments and other formulas. For example, we will understand practical use Formulas for novice users.

Formulas in Excel for teapots

To set the formula for the cell, you need to activate it (put the cursor) and enter equal to (\u003d). You can also enter a sign of equality in the formula string. After entering the formula, press ENTER. The result of calculations will appear in the cell.

Excel applies standard mathematical operators:

The "*" symbol is used necessarily when multiplying. It is unacceptable to lower it as accepted during written arithmetic computing. That is, recording (2 + 3) 5 Excel will not understand.

Excel program can be used as a calculator. That is, to introduce into the formula of the number and operators of mathematical calculations and immediately obtain the result.

But more often introduced addresses of cells. That is, the user enters a link to the cell, with the value of which will operate the formula.

When changing values \u200b\u200bin the cells, the formula automatically recalculate the result.

The operator multiplied the value of the cell B2 by 0.5. To introduce a link to the cell in the formula, simply click on this cell.

In our example:

1. They put the cursor in the B3 cell and introduced \u003d.
2. Click on the cell B2 - Excel "designated" it (the name of the cell appeared in the formula, a "flashing" rectangle) was formed around the cell).
3. Entered the *, value 0.5 from the keyboard and press the input.

If several operators are used in the same formula, the program will handle them in the following sequence:

• %, ^;
• *, /;
• +, -.

You can change the sequence using round brackets: Excel primarily calculates the value of the expression in brackets.



As in the Excel formula, designate a permanent cell

There are two types of cell references: relative and absolute. When copying the formula, these links behave differently: relative changes, absolute remain constant.

We find in the lower right corner of the first cell of the column of the autocillry marker. Click on this point with the left mouse button, keep it and "tent" down the column.

We release the mouse button - the formula will be copied to selected cells with relative references. That is, each cell will have its own formula with its arguments.

When working with tables, the priority value has the values \u200b\u200bdisplayed in it. But an important component is also its design. Some users consider it a secondary factor and do not pay special attention to him. And in vain, because a beautifully decorated table is an important condition for better perception and understanding by users. The visualization of data is particularly played in this. For example, using visualization tools, you can paint the table cells depending on their contents. Let's find out how it can be done in the Excel program.

Of course, it is always nice to have a well-designed table, in which cells depending on the contents are painted in different colors. But this feature is particularly relevant for large tables containing a significant data array. In this case, the fill with the color of the cells will greatly facilitate users orientation in this huge quantities Information, as it can be said will be already structured.

Leaf elements can be attempting to manually paint, but again, if the table is large, it will take a considerable amount of time. In addition, in such an array of data, the human factor can play a role and errors will be allowed. Not to mention the table can be dynamic and the data in it periodically change, and massively. In this case, manually change the color in general it becomes unreal.

But the output exists. For cells that contain dynamic (changing) values \u200b\u200bapplied conditional formatting, and for statistical data you can use the tool "Find and replace".

Method 1: Conditional formatting

Using conditional formatting, you can specify certain boundaries of the values \u200b\u200bin which the cells will be painted in one color. Staining will be carried out automatically. In case the cell value, due to the change, will be out of the boundary, it will automatically repaint this leaf element.

Let's see how this method works on specific example. We have a table of income of the enterprise, in which these data is afraid. We need to highlight with different colors those elements in which the amount of income less 400000 rubles, OT. 400000 before 500000 rubles and exceed 500000 rubles.

1. We highlight the column in which information on the income of the enterprise is located. Then move to the tab "The main". Click on the button "Conditional formatting"which is located on the ribbon in the toolbar "Styles". In the list that opens, select the item "Management Rules ...".



2. The conventional formatting rules are launched. In field "Show formatting rules for" Must be set to "Current Fragment". By default, it is precisely it should be listed there, but just in case, check and in case of inconsistency, change the settings according to the above recommendations. After that, click on the button "Create a rule ...".



3. The formatting rule creation window opens. In the list of types of rules, choose a position . In the block description block, the switch must stand in the first field. "Values". In the second field, install the switch to the position "Less". In the third field, specify the value, the elements of the sheet containing the amount of which will be painted in a certain color. In our case, this value will be 400000 . After that we click on the button "Format…".



4. Cell format window opens. Move into the tab "Fill". Choose that color of the fill that we wish that cells containing the magnitude less 400000 . After that we click on the button Ok At the bottom of the window.



5. Returning to the formatting rule window and click on the button too. Ok.



6. After this action, we will again be redirected to Conditional Formatting Rules Manager. As you can see, one rule has already been added, but we have to add two more. So press the button again "Create a rule ...".



7. And again we get into the creation window. Move into the section "Format only cells that contain". In the first field of this section, leave the parameter "Cell value"and in the second exhibit a switch to the position "Between". In the third field, you need to specify the initial value of the range in which the elements of the sheet will be formatted. In our case, this is the number 400000 . In the fourth, specify the final value of this range. It will be 500000 . After that click on the button "Format…".



8. In the formatting window, we move back to the tab. "Fill", but this time you already choose another color, after which we click on the button Ok.



9. After returning to the creation window, I also click on the button Ok.



10. As we see, in Rule Dispatcher We have already created two rules. Thus, it remains to create a third. Click on the button "Create Rule".



11. In the Create Rules window, move again to the section "Format only cells that contain". In the first field, leave the option "Cell value". In the second field, install the switch to the police "More". In the third field we drive a number 500000 . Then, as in the previous cases, press the button "Format…".



12. In the window "Format cells" Again we move to the tab "Fill". This time we choose a color that differs from the two previous cases. Perform a click on the button Ok.



13. In the Rule Creation window, repeat pressing the button Ok.



14. Opens Rule Dispatcher. As you can see, all three rules are created, so we click on the button Ok.



15. Now the elements of the table are painted according to specified conditions and borders in conditional formatting settings.



16. If we change the contents in one of the cells, leaving the boundaries of one of the specified rules, then this element of the sheet will automatically change color.

In addition, it is possible to use conditional formatting somewhat differently for the color of the sheet elements in color.

Method 2: Using the "Find and Allocate" tool

If there are static data in the table that are not scheduled to be changed over time, you can use the tool to change the color of the cells by their content called "Find and highlight". The specified tool will allow you to find the specified values \u200b\u200band change the color in these cells to the user you need. But it should be noted that when changing the contents in the sheet elements, the color will not automatically change, but remains the same. In order to change the color to the relevant, you will have to repeat the procedure again. Therefore, this method is not optimal for tables with dynamic content.

Let's see how it works on a specific example, for which we take the same table of income of the enterprise.

1. We highlight a column with data that should be formatted by color. Then go to the tab "The main" and click on the button "Find and highlight"which is placed on the tape in the tool block "Editing". In the list of clicking on the item opened "To find".



2. Window starts "Find and replace" In the tab "To find". First of all, we find values \u200b\u200bto 400000 rubles. Since we have no cell, where it was contained less than 300000 rubles, then, in essence, we need to highlight all the elements in which the numbers are contained in the range from 300000 before 400000 . Unfortunately, directly indicate this range, as in the case of conventional formatting, this method It is impossible.

   But there is a possibility to do somewhat differently that we will give the same result. You can set the following template in the search bar "3 ?????". A question mark means any character. Thus, the program will search for all six-digit numbers that start with numbers "3". That is, in the issuance of searching will fall in the range 300000 – 400000 What we are required. If there were fewer numbers in the table 300000 or less 200000 , for each range in a hundred thousand, the search would have to be produced separately.

   We introduce an expression "3 ?????" in field "To find" and click on the button "Find all».



3. After that, in the lower part of the window, the results of search results are open. Click on the left mouse button on any of them. Then type the key combination Ctrl + A.. After that, all the results of the search for issuing are allocated and the elements in the column are distinguished at the same time, to which these results refer.



4. After the elements in the column are highlighted, do not rush to close the window "Find and replace". Being in the tab "The main" In which we have moved earlier, go to the tape to the tool block "Font". Click on the triangle to the right of the button "Color Fill". There is a choice of different colors of the fill. Choose the color that we wish to apply to the elements of a sheet containing the values \u200b\u200bless than 400000 rubles.



5. As we see, all cells of the column in which the values \u200b\u200bare less than 400000 rubles are allocated by the selected color.



6. Now we need to paint the elements in which the quantities are located in the range from 400000 before 500000 rubles. This range includes numbers that match the template "four??????". Drive it in the search field and click on the button "Find all"After pre-highlight the column you need.



7. Similarly, with the previous time in search results We produce the allocation of the result resulting by pressing the combination of hot keys Ctrl + A.. After that, we move to the fill color selection icon. Click on it and click on the icon of the shade you need, which will be painted by the elements of the sheet, where the values \u200b\u200bare in the range from 400000 before 500000 .



8. As you can see, after this action all the elements of the table with data in the interval with 400000 by 500000 Allocated selected color.



9. Now we have to highlight the last interval values \u200b\u200b- more 500000 . Here we are also lucky, since all the numbers are more 500000 Are in the range from 500000 before 600000 . Therefore, in the search field I introduce the expression "five?????" and click on the button "Find all". If there were values \u200b\u200bexceeding 600000 , we would have had to additionally search for expression "6 ?????" etc.



10. Again allocate search results using a combination Ctrl + A.. Next, using the tape button, select a new color to fill the interval exceeding 500000 For the same analogy, as we did before.



11. As you can see, after this action, all elements of the column will be painted, according to the numerical value, which is placed in them. Now you can close the search box by clicking standard button Closing in the upper right corner of the window, since our task can be considered solved.



12. But if we replace the number to another, which goes beyond the borders that are installed for a specific color, the color will not change, as it was in the previous way. This suggests that this option will work reliably only in those tables in which the data does not change.

As you can see, there are two ways to paint the cells depending on the numerical values \u200b\u200bthat are in them: by means of conditional formatting and using the tool "Find and replace". The first method is more progressive, as it allows you to more clearly specify the conditions for which the elements of the sheet will be distinguished. In addition, when conditional formatting, the element color is automatically changing, in case of changing the contents in it, which cannot be done. However, the pouring of the cells, depending on the value by applying the tool "Find and replace" It is also quite possible to use, but only in static tables.