Excel формула адрес диапазона

Функция АДРЕС возвращает адрес определенной ячейки (текстовое значение), на которую указывают номера столбца и строки. К примеру, в результате выполнения функции =АДРЕС(5;7) будет выведено значение $G$5.

Примечание: наличие символов «$» в адресе ячейки $G$5 свидетельствует о том, что ссылка на данную ячейку является абсолютной, то есть не меняется при копировании данных.

Функция АДРЕС в Excel: описание особенностей синтаксиса

Функция АДРЕС имеет следующую синтаксическую запись:

=АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])

Первые два аргумента данной функции являются обязательными для заполнения.

Описание аргументов:

• Номер_строки – числовое значение, соответствующее номеру строки, в которой находится требуемая ячейка;
• Номер_столбца – числовое значение, которое соответствует номеру столбца, в котором расположена искомая ячейка;
• [тип_ссылки] – число из диапазона от 1 до 4, соответствующее одному из типов возвращаемой ссылки на ячейку:

1. абсолютная на всю ячейку, например — $A$4
2. абсолютная только на строку, например — A$4;
3. абсолютная только на столбец, например — $A4;
4. относительная на всю ячейку, например A4.

• [a1] – логическое значение, определяющее один из двух типов ссылок: A1 либо R1C1;
• [имя_листа] – текстовое значение, которое определяет имя листа в документе Excel. Используется для создания внешних ссылок.

Примечания:

1. Ссылки типа R1C1 используются для цифрового обозначения столбцов и строк. Для возвращения ссылок такого типа в качестве параметра a1 должно быть явно указано логическое значение ЛОЖЬ или соответствующее числовое значение 0.
2. Стиль ссылок в Excel может быть изменен путем установки/снятия флажка пункта меню «Стиль ссылок R1C1», который находится в «Файл – Параметры – Формулы – Работа с Формулами».
3. Если требуется ссылка на ячейку, которая находится в другом листе данного документа Excel, полезно использовать параметр [имя_листа], который принимает текстовое значение, соответствующее названию требуемого листа, например «Лист7».



Примеры использования функции АДРЕС в Excel

Пример 1. В таблице Excel содержится ячейка, отображающая динамически изменяемые данные в зависимости от определенных условий. Для работы с актуальными данными в таблице, которая находится на другом листе документа требуется получить ссылку на данную ячейку.

На листе «Курсы» создана таблица с актуальными курсами валют:

На отдельном листе «Цены» создана таблица с товарами, отображающая стоимость в долларах США (USD):

В ячейку D3 поместим ссылку на ячейку таблицы, находящейся на листе «Курсы», в которой содержится информация о курсе валюты USD. Для этого введем следующую формулу: =АДРЕС(3;2;1;1;»Курсы»).

Значение параметров:

• 3 – номер строки, в которой содержится искомая ячейка;
• 2 – номер столбца с искомой ячейкой;
• 1 – тип ссылки – абсолютная;
• 1 – выбор стиля ссылок с буквенно-цифровой записью;
• «Курсы» — название листа, на котором находится таблица с искомой ячейкой.

Для расчета стоимости в рублях используем формулу: =B3*ДВССЫЛ(D3).

Функция ДВССЫЛ необходима для получения числового значения, хранимого в ячейке, на которую указывает ссылка. В результате вычислений для остальных товаров получим следующую таблицу:

Как получить адрес ссылки на ячейку Excel?

Пример 2. В таблице содержатся данные о цене товаров, отсортированные в порядке возрастания стоимости. Необходимо получить ссылки на ячейки с минимальной и максимальной стоимостью товаров соответственно.

Исходная таблица имеет следующий вид:

Для получения ссылки на ячейку с минимальной стоимостью товара используем формулу:

Функция АДРЕС принимает следующие параметры:

• число, соответствующее номеру строки с минимальным значением цены (функция МИН выполняет поиск минимального значения и возвращает его, функция ПОИСКПОЗ находит позицию ячейки, содержащей минимальное значение цены. К полученному значению добавлено 2, поскольку ПОИСКПОЗ осуществляет поиск относительно диапазона выбранных ячеек.
• 2 – номер столбца, в котором находится искомая ячейка.

Аналогичным способом получаем ссылку на ячейку с максимальной ценой товара. В результате получим:

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

Пример 3. В таблице содержится ячейка, данные из которой используются в другом программном продукте. Для обеспечения совместимости необходимо предоставить ссылку на нее в виде R1C1.

Исходная таблица имеет следующий вид:

Для получения ссылки на ячейку B6 используем следующую формулу: =АДРЕС(6;2;1;0).

Аргументы функции:

• 6 – номер строки искомой ячейки;
• 2 – номер столбца, в котором содержится ячейка;
• 1 – тип ссылки (абсолютная);
• 0 – указание на стиль R1C1.

В результате получим ссылку:

Практическое применение функции АДРЕС: Поиск значения в диапазоне таблицы Excel по столбцам и строкам.

Примечание: при использовании стиля R1C1 запись абсолютной ссылки не содержит знака «$». Чтобы отличать абсолютные и относительные ссылки используются квадратные скобки «[]». Например, если в данном примере в качестве параметра тип_ссылки указать число 4, ссылка на ячейку примет следующий вид:

Скачать примеры функции АДРЕС для получения ссылки на ячейку в Excel

Так выглядит абсолютный тип ссылок по строкам и столбцам при использовании стиля R1C1.

In this example the goal is to return the full address of a range or named range as text. The purpose of this formula is informational, not functional. It provides a way to inspect the current range for a named range, an Excel Table, or even the result of a formula. The core of the solutions explained below is the ADDRESS function, which returns a cell address based on a given row and column. The formula gets somewhat complicated because we need to use ADDRESS twice, and because there is a lot of busy work in collecting the coordinates we need to provide to ADDRESS. See below for a more elegant formula that takes advantage of the LET function and the TAKE function in Excel 365. This version can also report the range returned by another function (like OFFSET).

Note: the CELL function is another way to get the address for a cell. However, CELL does not provide a way to choose a relative or absolute format like the ADDRESS function does. In addition, CELL is a volatile function and can cause performance problems in large or complex worksheets. For those reasons, I have avoided it in this example.

Background

The links below provide more details about Excel Tables and named ranges:

• How to create an Excel Table (video)
• How to create a named range (video)
• How to create a dynamic named range with a table (video)

Demo

The animation below shows how the formula responds when a Table named «data» is resized. In the worksheet, DA stands for Dynamic Array version of Excel and Legacy stands for older versions of Excel. See below for more information about the dynamic array version.

The basic idea

The basic idea of this formula is to get the coordinates of the first (upper left) cell in a range and the last (lower right) cell in a range, then use these coordinates to create cell references, then join the references together with concatenation. The formula itself looks complicated and scary:

=ADDRESS(@ROW(data),@COLUMN(data),4)&":"&ADDRESS(@ROW(data)+ROWS(data)-1,@COLUMN(data)+COLUMNS(data)-1,4)

However, most of what you see is the «busy work» of collecting the coordinates with the ROW, ROWS, COLUMN, and COLUMNS functions. Once that work is done, the formula simplifies to this:

=ADDRESS(5,2,4)&":"&ADDRESS(16,4,4)

The ADDRESS function then creates a reference to the first and last cell in the range, and the two references are joined together. That’s it. The rest of the problem is the details of collecting the coordinates needed by the ADDRESS function.

ROW and COLUMN functions

The ROW and COLUMN functions simply return coordinates. For example, if we give ROW and COLUMN cell B5 as a reference, we get back coordinate numbers:

=ROW(B5) // returns 5
=COLUMN(B5) // returns 2

Notice that COLUMN returns a number, not a letter: column A, is 1, column B is 2, column C is 3, etc.

ROWS and COLUMNS functions

The ROWS and COLUMNS functions return counts. For example, if we give ROWS and COLUMNS the range B5:D16, we get back the number of rows and the number of columns in the range:

=ROWS(B5:D16) // returns 12
=COLUMNS(B5:D16) // returns 3

ADDRESS function

The ADDRESS function returns the address for a cell based on a given row and column number as text. For example:

=ADDRESS(1,1) // returns "$A$1"
=ADDRESS(5,2) // returns "$B$5"

Note that ADDRESS returns an address as an absolute reference by default. However, by providing 4 for the optional argument abs_num, ADDRESS will return a relative reference:

=ADDRESS(1,1,4) // returns "A1"
=ADDRESS(5,2,4) // returns "B5"

In this problem, we need to build up an address for a range in two parts (1) a reference to the first cell in the range and (2) a reference to the last cell in a range. To get the address for the first cell in the range, we use this formula:

=ADDRESS(@ROW(data),@COLUMN(data))

Note: data is the named range B5:E16.

As mentioned above, the ROW function and the COLUMN function return coordinates: ROW returns a row number, and COLUMN returns a column number. In this case however, we are giving ROW and COLUMN the range data, not a single cell. As a result, we get back an array of numbers:

=ROW(data) // returns {5;6;7;8;9;10;11;12;13;14;15;16}
=COLUMN(data) // returns {2,3,4,5}

In other words, ROW returns all row numbers for data (B5:D15), and COLUMN returns all column numbers for data. This is where the formula gets a bit complicated due to Excel version differences.

Although ROW and COLUMN return multiple results, older versions of Excel will automatically limit the results to the first value only. This works fine for the problem at hand, because we only want the first value. However, in the dynamic version of Excel, multiple results are not automatically limited, because formulas can spill results onto the worksheet. We don’t want this behavior in this case, so we use the implicit intersection operator (@) to limit the output from ROW and COLUMN to one value only:

=@ROW(data) // returns 5
=@COLUMN(data) // returns 2

In effect, this mimics the behavior in Legacy Excel where formulas that return more than one result display one result only. Note that the implicit intersection operator (@) is not required in Legacy Excel. In fact, if you try to add the @ in an older version of Excel, Excel will simply remove it. However, it is required in the current version of Excel.

Returning to the formula, ROW and COLUMN return their results directly to ADDRESS as the row_num and column_num arguments. With abs_num provided as 4 (relative), ADDRESS returns the text «B5».

=ADDRESS(5,2,4) // returns "B5"

To get the last cell in the range, we use the ADDRESS function again like this:

ADDRESS(@ROW(data)+ROWS(data)-1,@COLUMN(data)+COLUMNS(data)-1,4)

Essentially, we follow the same idea as above, using ROW and COLUMN to get the last row and last column of the range. However we also calculate the total number of rows with the ROWS function and the total number of columns with the COLUMNS function:

=ROWS(data) // returns 12
=COLUMNS(data) // returns 4

Then we do some simple math to calculate the row and column number of the last cell. We add the row count to the starting row, and add the column count to the starting column, then subtract 1 to get the correct coordinates for the last cell:

=ADDRESS(@ROW(data)+ROWS(data)-1,@COLUMN(data)+COLUMNS(data)-1,4)
=ADDRESS(5+12-1,2+4-1,4)
=ADDRESS(16,5,4)
="E16"

With abs_num set to 4. ADDRESS returns «E16», the address of the last cell in data as text. At this point, we have the address for the first cell and the address for the last cell. The only thing left to do is concatenate them together separated by a colon:

="B5"&":"&"E16"
="B5:E16"

More elegant formula

Although the formula above works fine, it is cumbersome and redundant. New array functions in Excel make it possible to solve the problem with a more elegant formula like this:

=LET(
first,TAKE(rng,1,1),
last,TAKE(rng,-1,-1),
ADDRESS(ROW(first),COLUMN(first),4)&":"&
ADDRESS(ROW(last),COLUMN(last),4)
)

Where rng is the generic placeholder for a named range or Excel Table. In this formula, the LET function is used to assign values to two variables: first and last. First represents the first cell in the range and last represents the last cell in the range. These values are assigned with the TAKE function like this:

first,TAKE(rng,1,1), // first cell (B5)
last,TAKE(rng,-1,-1), // last cell (E16)

The TAKE function returns a subset of a given array or range. The size of the array returned is determined by separate rows and columns arguments. When positive numbers are provided for rows or columns, TAKE returns values from the start or top of the array. Negative numbers take values from the end or bottom of the array. What’s tricky about this, and not obvious, is that when working with ranges, TAKE returns a valid reference. You don’t normally see the reference because as with most formulas, Excel returns the value at that reference, not the reference itself. However, the reference is there, as is obvious in the next step.

Finally, the ADDRESS function is used to generate an address for both the first cell and the last cell in the range, and the two results are concatenated with a colon (:) in between:

ADDRESS(ROW(first),COLUMN(first),4)&":"&ADDRESS(ROW(last),COLUMN(last),4)

Substituting the references returned by TAKE, the formula evaluates like this:

=ADDRESS(ROW(B5),COLUMN(B5),4)&":"&ADDRESS(ROW(E16),COLUMN(E16),4)
=ADDRESS(5,2,4)&":"&ADDRESS(16,4,4)
="B5"&":"&"E16"
="B5:E16"

Named range as text

To get an address for a named range entered as a text value (i.e. «range», «data», etc.), you’ll need to use the INDIRECT function to change the text into a valid reference before proceeding. For example, to get the address of a table name entered in A1 as text, you could use this unwieldy formula:

=ADDRESS(ROW(INDIRECT(A1)),COLUMN(INDIRECT(A1)))&":"&ADDRESS(ROW(INDIRECT(A1))+ROWS(INDIRECT(A1))-1,COLUMN(INDIRECT(A1))+COLUMNS(INDIRECT(A1))-1)

In the dynamic array version of Excel, the LET function makes this much simpler. With a range or table name as text in cell A1, you can call INDIRECT just once like this:

=LET(
rng,INDIRECT(A1),
first,TAKE(rng,1,1),
last,TAKE(rng,-1,-1),
ADDRESS(ROW(first),COLUMN(first),4)&":"&
ADDRESS(ROW(last),COLUMN(last),4)
)

You can read more about the LET function here.

Named LAMBDA option

Taking things a step further, you can use the LAMBDA function to create a custom function like this:

=LAMBDA(range,
  LET(
    rng,IF(ISREF(range),range,INDIRECT(range)),
    first,TAKE(rng,1,1),
    last,TAKE(rng,-1,-1),
    ADDRESS(ROW(first),COLUMN(first),4)&":"&
    ADDRESS(ROW(last),COLUMN(last),4)
  )
)(data)

This version of the formula does the same thing as the formula above with one additional step: it checks for range or table names entered as text values. This is done with the ISREF function in the first step. If the value passed in for range is a valid reference, we use it as-is to assign a value to rng. If not, we run it through the INDIRECT function to try and get a valid reference.

Finally, if we give the LAMBDA formula above the name «GetRange», we can call it on any valid range like this:

=GetRange(data)

Read more about creating and naming custom LAMBDA formulas here.

Check reference returned by formula

A nice feature of the LAMBDA version of the formula is we easily can use it to check the result of any formula that returns a range. For example, we can call it with the OFFSET function like this:

=GetRange(OFFSET(A1,4,1,10,2)) // returns "B5:C14"

The point here is that it is not easy to understand what range is returned by OFFSET, because Excel will return only the values in the range. The custom GetRange function makes it possible to print the range returned by a formula explicitly.

Other ideas

I looked at two other formulas to solve this problem. The first uses ADDRESS and TAKE:

=LET(
d,ADDRESS(ROW(data),COLUMN(data),4),
TAKE(a,1,1)&":"&TAKE(a,-1,-1)
)

The second uses ADDRESS and the new TOCOL function:

=LET(
d,TOCOL(ADDRESS(ROW(data),COLUMN(data),4)),
INDEX(d,1)&":"&INDEX(d,ROWS(d))
)

Both formulas use the ADDRESS function to spin up all of the addresses in the range at the same time. The first formula then uses the TAKE function to get the first and last address. The second formula uses the TOCOL function to unwrap the array of references created by ADDRESS, then it uses the INDEX function twice to get the first and the last value. On small ranges, these formulas should work nicely. However, on very large ranges you are likely to see a performance hit because of time needed to create all of the extra addresses. The proposes formula solution above avoids this problem by only creating the first and last reference. The tradeoff is that the formula is more complex.

The Excel ADDRESS function is a Lookup & Reference formula that returns the address of a cell based on its input arguments. The function also allows you to set the type and reference style of the address. In this guide, we’re going to show you how to use the Excel ADDRESS function and also go over some tips and error handling methods.

Supported versions

• All Excel versions

Excel ADDRESS Function Syntax

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

Arguments

Examples

The Excel ADDRESS function requires two arguments to execute: row_num and column_num. Each argument specifies one of the coordinates for the target reference for which you want to find the address. The remaining three are optional arguments that configure the reference style.

The first optional argument, [abs_num] defines the absolute/relative type of the reference. You can simply omit it, or give it a number between 1 and 4 to set the type of the reference. Now, let’s take a closer look at how the function works.

A1-reference (Default style)

The A1 reference system is Excel’s default system, and it is the default configuration for the ADDRESS function as well. If you leave the [a1] argument empty or set it to TRUE, you will get the address in A1-style.

R1C1-reference

R1C1 reference style works very well with relative references. Instead of coordinates like B5, R1C1 displays the relative position of referenced ranges. If you are using R1C1 reference style in your workbook, you need to set the [a1] argument as FALSE or 0.

Using the Excel ADDRESS Function with sheet name

The last optional argument is [sheet_name]. Supply a text value to get a reference that contains the sheet name. If the argument is omitted, the reference returns without a sheet name.

Download Workbook

Tips

• Combine the Excel ADDRESS function with the INDIRECT function to create dynamic references. For example, the following formula targets a range which size is determined by the values in the cells A1, A2, A3 and A4.

  INDIRECT(ADDRESS(A1,A2)&»:»&ADDRESS(A3,A4))

Функция АДРЕС() в MS EXCEL

​Смотрите также​Перейдите в ячейку D6​ тип ссылок: А1​Исходная таблица имеет следующий​ столбца, в котором​.​ можете добавлять комментарии​​Discount​​ добавить отступ, редактор​

​ начинающегося с ячейки​​ строке диапазона, номер​​ 2 в первой​ требуется возвратить значение.​

Синтаксис функции

​INDIRECT​(тип_ссылки) – если​

​ не используется, и​​ с выводом значения​Функция АДРЕС(), английский вариант​ и выберите инструмент​

​ или R1C1. Если​​ вид:​ расположена искомая ячейка;​В раскрывающемся списке​

​ в отдельных строках​, до двух дробных​

​ Visual Basic автоматически​​ B2 и заканчивающегося​

• ​ столбца 1 —​​ строке и значения​ Если аргумент «номер_строки»​
• ​(ДВССЫЛ) позже в​​ равно​ адрес, возвращаемый функцией,​ из соседнего столбца.​ ADDRESS(), возвращает адрес​
• ​ из выпадающего списка:​​ аргумент а1 имеет​Для получения ссылки на​[тип_ссылки] – число из​Управление​
• ​ или в правой​​ разрядов:​ вставит его и​

​ пересечением пятой строки​​ его первому столбцу​ 3 и 4​ опущен, аргумент «номер_столбца»​ рамках марафона​1​ ссылается на ячейку​В этой статье описаны​ ячейки на листе,​ «Главная»-«Сумма»-«Число».​ значение ИСТИНА или​ ячейку с минимальной​ диапазона от 1​выберите​ части строк, содержащих​Discount = Application.Round(Discount, 2)​ для следующей строки.​ и второго столбца​ и т. д.​ во второй строке.​ является обязательным.​30 функций Excel за​или вообще не​ текущего листа.​ синтаксис формулы и​ для которой указаны​На этот рас нам​

​ опущен, то функция​ стоимостью товара используем​ до 4, соответствующее​​Надстройки Excel​​ код VBA. Советуем​​В VBA нет функции​​ Чтобы сдвинуть строку​ диапазона A2:A6, т. е.​​ Ссылка, возвращаемая функцией​​2​​Номер_столбца​​ 30 дней​ указано, то функция​​Скопируйте образец данных из​​ использование функции​

​ номера строки и​​ не подходит автоматическое​ АДРЕС возвращает ссылку​ формулу:​ одному из типов​. Затем нажмите кнопку​ начинать длинный блок​

Примеры

​ округления, но она​ на один знак​ сумма значений из​ ИНДЕКС, указывает на​Значение ячейки на пересечении​

​     — необязательный аргумент.​.​ возвратит абсолютный адрес​ следующей таблицы и​АДРЕС​

​ столбца. Например, формула АДРЕС(2;3)​ определение диапазона ячеек,​ типа А1; если​Функция АДРЕС принимает следующие​ возвращаемой ссылки на​

​Перейти​ кода с комментария,​ есть в Excel.​ табуляции влево, нажмите​ диапазона B2:B6.​ пересечение строки «номер_строки»​ второй строки и​ Выбирает столбец в​=INDIRECT(ADDRESS(C2,C3))​ ($A$1). Чтобы получить​ вставьте их в​в Microsoft Excel.​ возвращает значение​

excel2.ru

Функция АДРЕС

​ поэтому его нужно​ этот аргумент имеет​ параметры:​​ ячейку:​​.​ в котором объясняется​ Чтобы использовать округление​SHIFT+TAB​2,42​ и столбца «номер_столбца».​ второго столбца в​​ массиве, из которого​​=ДВССЫЛ(АДРЕС(C2;C3))​

Описание

​ относительный адрес (A1),​​ ячейку A1 нового​​ Чтобы узнать больше​$C$2​ исправить на D2:D4.​ значение ЛОЖЬ, то​число, соответствующее номеру строки​​абсолютная на всю ячейку,​​В диалоговом окне​​ его назначение, а​​ в этом операторе,​​.​К началу страницы​​Если указать в качестве​​ массиве, указанном выше.​​ требуется возвратить значение.​​Функция​​ используйте значение​​ листа Excel. Чтобы​​ о работе с​.​ После чего жмем​ функция АДРЕС возвращает​ с минимальным значением​​ например — $A$4​​Надстройки​​ затем использовать встроенные​​ необходимо указать VBA,​

Синтаксис

​Теперь вы готовы использовать​

​Хотя в Excel предлагается​ аргумента «номер_строки» или​

• ​4​​ Если аргумент «номер_столбца»​INDIRECT​4​

• ​ отобразить результаты формул,​​ почтовыми адресами и​Функция АДРЕС() возвращает​ Enter.​

• ​ ссылку типа R1C1.​​ цены (функция МИН​абсолютная только на строку,​

• ​ выделите их и​​ создании почтовых наклеек,​текстовое​Из D6 в ячейку​Имя_листа — это​ выполняет поиск минимального​ например — A$4;​ с именем книги,​ отдельных операторов.​ Round следует искать​ Закройте редактор Visual​ функций, в нем​ (ноль), функция ИНДЕКС​ Это формула массива, которую​ является обязательным.​​ и без функции​​2​ нажмите клавишу F2,​ см. по ссылкам​значение в виде​​ E6 скопируйте функцию​​ текст, определяющий имя​ значения и возвращает​

  ​абсолютная только на столбец,​​ как показано ниже.​Кроме того, рекомендуется присваивать​ в объекте Application​​ Basic, выделите ячейку​​ может не быть​ возвратит ссылку на​​ нужно вводить с​​Если используются оба аргумента​​ADDRESS​​=A$1,​​ а затем —​​ в разделе​ адреса ячейки.​​ =СЧЕТ() – это​​ рабочего листа, который​

• ​ его, функция ПОИСКПОЗ​​ например — $A4;​Создав нужные функции, выберите​ макросам и пользовательским​ (Excel). Для этого​ G7 и введите​​ той функции, которая​​ целый столбец или​​ помощью клавиш CTRL+SHIFT+ВВОД.​​ — и «номер_строки»,​​(АДРЕС). Вот как​​3​ клавишу ВВОД. При​См. также​АДРЕС(номер_строки, номер_столбца, [тип_ссылки], [a1],​ функция Excel для​

Пример

​используется для формирования​ находит позицию ячейки,​относительная на всю ячейку,​Файл​ функциям описательные имена.​ добавьте слово​ следующий код:​ нужна для ваших​ целую строку соответственно.​ Excel автоматически заключит​ и «номер_столбца», —​ можно, используя оператор​=$A1.​

support.office.com

30 функций Excel за 30 дней: АДРЕС (ADDRESS)

​ цены. К полученному​​[a1] – логическое значение,​Сохранить как​​ название​перед словом Round.​Excel вычислит 10%-ю скидку​​ разработчики Excel не​​ «номер_области» должны указывать​ скобки {}. Если​ значение ячейки на​&​– если TRUE​​ все данные.​​АДРЕС​​ Обязательный аргумент. Номер строки,​​На данном примере хорошо​

​ внешние листы не​ значению добавлено 2,​ определяющее один из​​.​​MonthLabels​ Используйте этот синтаксис​ для 200 единиц​ могли предугадать все​ на ячейку внутри​ вы попытаетесь ввести​ пересечении указанных строки​«, слепить нужный адрес​ (ИСТИНА) или вообще​Формула​

​можно использовать для​ используемый в ссылке​​ видно, что функция​​ используются.​ поскольку ПОИСКПОЗ осуществляет​ двух типов ссылок:​В диалоговом окне​вместо​ каждый раз, когда​ по цене 47,50​

Функция 20: ADDRESS (АДРЕС)

​ потребности пользователей. Однако​​ аргумента «ссылка»; в​​ их вручную, Excel​ и столбца.​ в стиле​ не указано, функция​Описание​ получения адреса ячейки​ на ячейку.​ =СЧЕТ() игнорирует ячейки,​​Abram pupkin​​ поиск относительно диапазона​​ A1 либо R1C1;​​Сохранить как​Labels​ нужно получить доступ​ ₽ и вернет​

Как можно использовать функцию ADDRESS (АДРЕС)?

​ в Excel можно​​ противном случае функция​​ отобразит формулу как​Если указать в качестве​R1C1​ возвращает ссылку в​Результат​

• ​ на листе по​Номер_столбца​ которые не содержат​
• ​: внесу и я​ выбранных ячеек.​[имя_листа] – текстовое значение,​
• ​откройте раскрывающийся список​, чтобы более точно​

Синтаксис ADDRESS (АДРЕС)

​ к функции Excel​​ 950,00 ₽.​​ создавать собственные функции,​ ИНДЕКС возвращает значение​

​ текст. Если при​
​ аргумента "номер_строки" или​

• ​и в результате​​ стиле​=АДРЕС(2;3)​​ номерам строки и​​ Обязательный аргумент. Номер столбца,​ числа или пусты.​ свои 5 копеек.​2 – номер столбца,​ которое определяет имя​Тип файла​​ указать его назначение.​​ из модуля VBA.​​В первой строке кода​​ и ниже вы​​ ошибки #ССЫЛКА!. Если​​ вводе формулы не​
• ​ «номер_столбца» значение 0​​ получить значение ячейки:​A1​Абсолютная ссылка​ столбца. Например, функция​ используемый в ссылке​​В ячейки D7 и​​Если сказать что​ в котором находится​​ листа в документе​​и выберите значение​
• ​ Описательные имена макросов​​Пользовательские функции должны начинаться​​ VBA функция DISCOUNT(quantity,​ найдете все нужные​ аргументы «номер_строки» и​ нажать клавиши CTRL+SHIFT+ВВОД,​ (ноль), функция ИНДЕКС​=INDIRECT(«R»&C2&»C»&C3,FALSE)​

Ловушки ADDRESS (АДРЕС)

​, если FALSE (ЛОЖЬ),​​$C$2​​АДРЕС(​ на ячейку.​ E7 введем логическую​ информация находится в​ искомая ячейка.​ Excel. Используется для​Надстройка Excel​​ и пользовательских функций​​ с оператора Function​ price) указывает, что​ для этого инструкции.​ «номер_столбца» опущены, функция​

Пример 1: Получаем адрес ячейки по номеру строки и столбца

​ она возвратит ошибку​​ возвратит массив значений​​=ДВССЫЛ(«R»&C2&»C»&C3;ЛОЖЬ)​ то в стиле​=АДРЕС(2;3;2)​2;3)​Последние 3 аргумента являются​ функцию, которая позволит​ 4-ом столбце и​Аналогичным способом получаем ссылку​ создания внешних ссылок.​. Сохраните книгу с​​ особенно полезны, если​​ и заканчиваться оператором​

​ функции DISCOUNT требуется​
​Пользовательские функции (как и​

Абсолютная или относительная

​ ИНДЕКС возвращает область​ #ЗНАЧ!​​ для целого столбца​​Функция​R1C1​Абсолютная строка; относительный столбец​

​возвращает значение​ необязательными.​ нам проверить, все​ 2 строке, то​​ на ячейку с​​Примечания:​​ запоминающимся именем, таким​​ существует множество процедур​

​ End Function. Помимо​
​ два аргумента:​

A1 или R1C1

​ макросы) записываются на​​ в аргументе «ссылка»,​​К началу страницы​ или целой строки​​INDEX​​.​C$2​$C$2​​[Тип_ссылки]​​ ли студенты имеют​

​ вы сразу понимаете​
​ максимальной ценой товара.​

Название листа

​Ссылки типа R1C1 используются​ как​ с похожим назначением.​ названия функции, оператор​quantity​ языке программирования​​ заданную аргументом «номер_области».​​Возвращает ссылку на ячейку,​

​ соответственно. Чтобы использовать​
​(ИНДЕКС) также может​

Пример 2: Находим значение ячейки, используя номер строки и столбца

​sheet​​=АДРЕС(2;3;2;ЛОЖЬ)​​. Еще один пример:​ Задает тип возвращаемой ссылки:​ оценки. Пример использования​ что это будет​ В результате получим:​ для цифрового обозначения​MyFunctions​То, как вы документируйте​​ Function обычно включает​​(количество) и​Visual Basic для приложений​​Результатом вычисления функции ИНДЕКС​​ расположенную на пересечении​ значения, возвращенные как​​ вернуть значение ячейки,​​_text​Абсолютная строка; относительный столбец​​ функция​1​​ функции ЕСЛИ:​

​ ячейка D2.​
​Пример 3. В таблице​

​ столбцов и строк.​​.​​ свои макросы и​ один или несколько​​price​​ (VBA)​ является ссылка, которая​ указанной строки и​​ массив, введите функцию​​ если указан номер​(имя_листа) – имя​​ с типом ссылки​​АДРЕС(​или опущен: абсолютная​

​Перейдите в ячейку D7​
​Гораздо сложнее найти​

​ содержится ячейка, данные​​ Для возвращения ссылок​​Сохранив книгу, выберите​ пользовательские функции, —​ аргументов. Однако вы​(цена). При вызове​

​. Они отличаются от​
​ интерпретируется в качестве​

​ указанного столбца. Если​​ ИНДЕКС как формула​ строки и столбца:​ листа может быть​

Пример 3: Возвращаем адрес ячейки с максимальным значением

​ R1C1​77;300)​ ссылка, например $D$7​ и выберите инструмент:​​ 154-ый столбец.​​ из которой используются​ такого типа в​

​Сервис​​ ваше личное дело,​​ можете создать функцию​ функции в ячейке​ макросов двумя вещами.​

​ таковой другими функциями.​
​ ссылка составлена из​

​ массива в горизонтальный​=INDEX(1:5000,C2,C3)​​ указано, если Вы​​R2C[3]​возвращает значение​​2​​ «Формулы»-«Логические»-«ЕСЛИ».​используем адрес​​ в другом программном​​ качестве параметра a1​>​

​ но важно выбрать​
​ без аргументов. В​

​ листа необходимо указать​ Во-первых, в них​
​ В зависимости от​
​ несмежных выделенных диапазонов,​

​ диапазон ячеек для​

office-guru.ru

ИНДЕКС (функция ИНДЕКС)

​=ИНДЕКС(1:5000;C2;C3)​ желаете видеть его​=АДРЕС(2;3;1;ЛОЖЬ;»[Книга1]Лист1″)​$KN$77​

​: абсолютная ссылка на​Заполняем аргументы функции в​

• ​=АДРЕС (2;154) машина​ продукте. Для обеспечения​ должно быть явно​Надстройки Excel​ определенный способ и​

• ​ Excel доступно несколько​ эти два аргумента.​ используются процедуры​ формулы значение, возвращаемое​

Форма массива

Описание

​ можно указать, какой​ строки и в​1:5000​ в возвращаемом функцией​

​Абсолютная ссылка на другую​. Чтобы передать функции​ строку; относительная ссылка​ диалоговом окне как​

Синтаксис

​ выдаст $EX$2​

​ совместимости необходимо предоставить​ указано логическое значение​

• ​.​​ придерживаться его.​ встроенных функций (например,​ В формуле =DISCOUNT(D7;E7)​

  • ​Function​ функцией ИНДЕКС, может​ из них требуется​ вертикальный — для столбца.​– это первые​ результате.​

  • ​ книгу и лист​АДРЕС​ на столбец, например​ показано на рисунке​и еще 1​ ссылку на нее​ ЛОЖЬ или соответствующее​В диалоговом окне​Чтобы использовать функцию, необходимо​ СЛЧИС и ТДАТА),​

• ​ аргумент​​, а не​ использоваться как ссылка​ использовать.​ Чтобы ввести формулу​ 5000 строк листа​Функция​'[Книга1]Лист1′!R2C3​

• ​номера строки и​​ D$7​ и жмем ОК​ пример но уже​ в виде R1C1.​ числовое значение 0.​Надстройки​ открыть книгу, содержащую​

Замечания

• ​ в которых нет​quantity​Sub​ или как значение.​ИНДЕКС(ссылка; номер_строки; [номер_столбца]; [номер_области])​ массива, нажмите сочетание​ Excel.​

• ​ADDRESS​=АДРЕС(2;3;1;ЛОЖЬ;»ЛИСТ EXCEL»)​ столбца в качестве​3​ (обратите внимание вторая​ посложнее.​Исходная таблица имеет следующий​Стиль ссылок в Excel​нажмите кнопку «Обзор»,​ модуль, в котором​ аргументов.​имеет значение D7,​. Это значит, что​ Например, формула ЯЧЕЙКА(«ширина»;ИНДЕКС(A1:B2;1;2))​Аргументы функции ИНДЕКС описаны​ клавиш CTRL+SHIFT+ВВОД.​В этом примере мы​(АДРЕС) возвращает лишь​

  ​Абсолютная ссылка на другой​​ ее аргументов, можно​: относительная ссылка на​ ссылка $A$4 -​

• ​Откройте чистый лист​ вид:​ может быть изменен​ найдите свою надстройку,​ она была создана.​После оператора Function указывается​ а аргумент​

Примеры

Пример 1

​ они начинаются с​ эквивалентна формуле ЯЧЕЙКА(«ширина»;B1).​ ниже.​Примечание:​ найдём ячейку с​ адрес ячейки в​

​ лист​ использовать другие функции​ строку; абсолютная ссылка​ абсолютная):​ и поставьте «1»​Для получения ссылки на​ путем установки/снятия флажка​ нажмите кнопку​ Если такая книга​ один или несколько​price​ оператора​ Функция ЯЧЕЙКА использует​

Пример 2

​Вчера в марафоне​СТРОКА​ $D7​ в E7.​ от A1до K15​ следующую формулу: =АДРЕС(6;2;1;0).​ ссылок R1C1», который​

​, а затем установите​ попытке использования функции​ проверят соответствия условиям​ Если скопировать формулу​, а не​ ИНДЕКС, как ссылку.​ на один или​ формулы массива.​ADDRESS​ значение ячейки, используйте​30 функций Excel за​и​

​ – Параметры –​​ надстройкой в поле​ При ссылке на​ с использованием аргументов,​ вы получите указанные​, и заканчиваются оператором​ такая формула, как​Если в качестве аргумента​ должны указывать на​ её адрес.​ аргумента функции​мы находили элементы​).​ D7​ у нас количество​

​ любую свободную ячейку​

Ссылочная форма

Описание

​ искомой ячейки;​ Формулы – Работа​Доступные надстройки​ функцию, хранящуюся в​ переданных функции. Наконец,​ ниже результаты.​End Function​ 2*ИНДЕКС(A1:B2;1;2), преобразует значение,​ «ссылка» используется несмежный​

Синтаксис

​ ячейку внутри заданного​

​Функция​INDIRECT​

• ​ массива при помощи​​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])​[а1]​ всех студентов, а​

  • ​=АДРЕС (СУММПРОИЗВ ((A1:K15=1)*СТРОКА​2 – номер столбца,​ с Формулами».​.​

  • ​ другой книге, необходимо​ в процедуру функции​Рассмотрим, как Excel обрабатывает​, а не​ возвращаемое функцией ИНДЕКС,​ диапазон, его необходимо​ массива; в противном​MAX​(ДВССЫЛ) или примените​ функции​

• ​Аргументы функции АДРЕС указаны​​    Логическое значение, которое определяет​ в ячейке D6​ (A1:K15));СУММПРОИЗВ ((A1:K15=1)*СТОЛБЕЦ (A1:K15)))​ в котором содержится​Если требуется ссылка на​

• ​После выполнения этих действий​​ указать перед ее​ следует включить оператор,​ эту функцию. При​End Sub​ в число в​

• ​ заключить в скобки​​ случае функция ИНДЕКС​(МАКС) находит максимальное​ одну из альтернативных​MATCH​ ниже.​ тип ссылок: А1​ и E6 –​Функции в Excel –​ ячейка;​ ячейку, которая находится​ ваши пользовательские функции​ именем название книги.​ назначающий значение переменной​ нажатии клавиши​. Во-вторых, они выполняют​ ячейке B1.​Если каждая область в​ возвратит значение ошибки​ число в столбце​ формул, показанных в​(ПОИСКПОЗ) и обнаружили,​Номер_строки​ или R1C1. При​ количество оценок. Функция​ это вычислительные инструменты​1 – тип ссылки​ в другом листе​ будут доступны при​ Например, если вы​ с тем же​ВВОД​ различные вычисления, а​

​Примечание:​ ссылке содержит только​ #ССЫЛКА!.​ C.​ примере 2.​ что она отлично​    Обязательный аргумент. Номер строки,​ использовании ссылок типа​ ЕСЛИ() проверяет, совпадают​

Замечания

• ​ пригодны для самых​ (абсолютная);​ данного документа Excel,​ каждом запуске Excel.​ создали функцию DISCOUNT​ именем, что у​Excel ищет имя​ не действия. Некоторые​  Функция ЯЧЕЙКА недоступна​ одну строку или​В этих примерах функция​=MAX(C3:C8)​При помощи функции​ работает в команде​ используемый в ссылке​ А1 столбцы обозначаются​

• ​ ли показатели D6​ разных отраслей деятельности:​0 – указание на​ полезно использовать параметр​ Если вы хотите​ в книге Personal.xlsb​ функции. Это значение​

• ​DISCOUNT​ операторы (например, предназначенные​ в Excel Web​ один столбец, аргумент​ ИНДЕКС используется для​=МАКС(C3:C8)​ADDRESS​ с другими функциями,​ на ячейку.​ буквами, а строки​ и E6 со​ финансы, статистика, банковское​

• ​ стиль R1C1.​ [имя_листа], который принимает​ добавить функции в​ и хотите вызвать​ возвращается в формулу,​в текущей книге​ для выбора и​ App.​ «номер_строки» или «номер_столбца»​ поиска значения ячейки,​Далее в игру вступает​(АДРЕС) Вы можете​ такими как​Номер_столбца​ — цифрами, например​ значением A4. Если​ дело, бухгалтерский учет,​В результате получим ссылку:​ текстовое значение, соответствующее​ библиотеку, вернитесь в​

​ ее из другой​​ которая вызывает функцию.​ и определяет, что​ форматирования диапазонов) исключаются​

Примеры

​Скопируйте образец данных из​ соответственно является необязательным.​ находящейся на пересечении​ функция​ получить адрес ячейки​VLOOKUP​    Обязательный аргумент. Номер столбца,​ D7. При использовании​ совпадают, то получаем​ инженерство и проектирование,​Практическое применение функции АДРЕС:​ названию требуемого листа,​ редактор Visual Basic.​

​ например R7C5 (R​

support.office.com

Создание пользовательских функций в Excel

​В логическом выражении для​ вычислительных функций включено​ строкам.​ Excel содержится ячейка,​ с таким же​.​ только возвращать значение​quantity​ пользовательские функции. Для​ отобразить результаты формул,​Номер_строки​ вставьте их в​(ПОИСКПОЗ), которая находит​ эти два аргумента,​ мы посвятим изучению​

Создание простой пользовательской функции

​ возвращаемой ссылки.​ означает ROW -​ удобства применяется 2​​ в данный учебник​Примечание: при использовании стиля​​ отображающая динамически изменяемые​ названием, как у​Чтобы вставить пользовательскую функцию​ в формулу на​​и​​ создания функций и​​ выделите их и​​    — обязательный аргумент. Номер​ ячейку A1 нового​ номер строки, и​​ результатом будет абсолютный​​ функции​​Тип_ссылки​​ строка, С означает​​ типа ссылок: относительная​​ с уроками по​​ R1C1 запись абсолютной​​ данные в зависимости​ файла надстройки (но​ быстрее (и избежать​ листе или в​price​ макросов используется​ нажмите клавишу F2,​ строки в диапазоне,​ листа Excel. Чтобы​COLUMN​ адрес, записанный в​ADDRESS​Возвращаемый тип ссылки​​ COLUMN — столбец).​​ и абсолютная. Это​ Excel. На простых​

​ ссылки не содержит​ от определенных условий.​ без расширения XLAM).​ ошибок), ее можно​ выражение, используемое в​), представляют собой заполнители​редактор Visual Basic (VBE)​ а затем — клавишу​

​ заданном аргументом «ссылка»,​ отобразить результаты формул,​(СТОЛБЕЦ), которая определяет​ стиле ссылок​(АДРЕС). Она возвращает​1 или опущен​ Если аргумент А1​

​ позволяет нам скопировать​ примерах и далее​ знака «$». Чтобы​

1. ​ Для работы с​​Дважды щелкните модуль в​​ выбрать в диалоговом​​ другом макросе или​​ для значений, на​, который открывается в​ ВВОД. При необходимости​ из которого требуется​​ выделите их и​​ номер столбца.​​A1​​ адрес ячейки в​Абсолютный​ имеет значение ИСТИНА​ формулу без ошибок​

2. ​ будем рассматривать практическое​ отличать абсолютные и​ актуальными данными в​

   ​ Project Explorer, чтобы​
​ окне "Вставка функции".​ функции VBA. Так,​
​ основе которых вычисляется​ отдельном окне.​ измените ширину столбцов,​
​ возвратить ссылку.​
​ нажмите клавишу F2,​
​=ADDRESS(MATCH(F3,C:C,0),COLUMN(C2))​
​.​ текстовом формате, используя​
​2​


​ или 1 или​​ в результатах ее​ применение функций.​ относительные ссылки используются​ таблице, которая находится​​ вывести код функций.​​ Пользовательские функции доступны​ пользовательские функции не​ скидка.​Предположим, что ваша компания​ чтобы видеть все​Номер_столбца​ а затем — клавишу​=АДРЕС(ПОИСКПОЗ(F3;C:C;0);СТОЛБЕЦ(C2))​=ADDRESS($C$2,$C$3)​ номер строки и​​Абсолютная строка; относительный столбец​​ опущен, то функция​

Применение пользовательских функций

​ вычисления.​Как найти среднее арифметическое​ квадратные скобки «[]».​ на другом листе​ Чтобы добавить новую​ в категории «Определенные​

​ могут изменять размер​

​Оператор If в следующем​ предоставляет скидку в​ данные.​    — необязательный аргумент. Номер​ ВВОД. При необходимости​

​Урок подготовлен для Вас​=АДРЕС($C$2;$C$3)​ столбца. Нужен ли​3​ АДРЕС() возвращает ссылку​​Примечание. Закладка «Формулы» предоставляет​​ число в Excel?​​ Например, если в​​ документа требуется получить​ функцию, установите точку​ пользователем»:​ окон, формулы в​ блоке кода проверяет​ размере 10 % клиентам,​​Фрукты​​ столбца в диапазоне,​ измените ширину столбцов,​​ командой сайта office-guru.ru​​Если не указывать значение​ нам этот адрес?​Относительная строка; абсолютный столбец​ типа А1; если​ доступ только к​

​ Создайте таблицу, так​ данном примере в​ ссылку на данную​​ вставки после оператора​​Чтобы упростить доступ к​​ ячейках, а также​​ аргумент​ заказавшим более 100​Цена​ заданном аргументом «ссылка»,​ чтобы видеть все​Источник: http://blog.contextures.com/archives/2011/01/21/30-excel-functions-in-30-days-20-address/​​ аргумента​​ Можно ли сделать​​4​​ этот аргумент имеет​ наиболее часто используемым​ как показано на​ качестве параметра тип_ссылки​

​ ячейку.​ End Function, который​ пользовательским функциям, можно​​ шрифт, цвет или​​quantity​ единиц товара. Ниже​Количество​

​ из которого требуется​ данные.​
​Перевел: Антон Андронов​abs_num​ то же самое​
​Относительный​
​ значение ЛОЖЬ (или​
​ функциям. Больше можно​


​ рисунке:​ указать число 4,​На листе «Курсы» создана​ завершает последнюю функцию​ определить их в​​ узор для текста​​и сравнивает количество​​ мы объясним, как​​Яблоки​ возвратить ссылку.​

​Данные​Автор: Антон Андронов​

​(тип_ссылки) в формуле,​ с помощью других​​A1​​ 0), функция АДРЕС()​ получить, вызвав окно​В ячейках D5 и​​ ссылка на ячейку​​ таблица с актуальными​ в окне кода,​ отдельной книге, а​ в ячейке. Если​ проданных товаров со​ создать функцию для​0,69 ₽​​Номер_области​​Данные​Функция ИНДЕКС возвращает значение​ то результатом будет​ функций?​    Необязательный аргумент. Логическое значение, которое​ возвращает ссылку типа​ «Мастер функций» нажав​

​ E5 введем функции,​​ примет следующий вид:​​ курсами валют:​ и начните ввод.​

​ затем сохранить ее​

​ включить в процедуру​ значением 100:​​ расчета такой скидки.​​40​    — необязательный аргумент. Диапазон​

​Яблоки​

​ или ссылку на​ абсолютная ссылка.​Давайте обратимся к сведениям​ определяет тип ссылок:​ R1C1.​ на кнопку «Вставить​ которые помогут получить​Так выглядит абсолютный тип​На отдельном листе «Цены»​ Вы можете создать​ как надстройку, которую​​ функции код для​​If quantity >= 100​В примере ниже показана​Бананы​ в аргументе «ссылка»,​Лимоны​ значение из таблицы​

Правила создания пользовательских функций

​Чтобы увидеть адрес в​ по функции​ А1 или R1C1.​Чтобы изменить тип ссылок,​ функцию» вначале строки​ среднее значение оценок​ ссылок по строкам​ создана таблица с​ любое количество функций,​ можно включать при​ таких действий, возникнет​ Then​ форма заказа, в​0,34 ₽​ из которого требуется​

​Бананы​ или диапазона.​ виде относительной ссылки,​ADDRESS​ При использовании ссылок​ используемый Microsoft Excel,​ формул. Или нажать​ успеваемости по урокам​ и столбцам при​ товарами, отображающая стоимость​ и они будут​ каждом запуске Excel.​ ошибка #ЗНАЧ!​DISCOUNT = quantity​ которой перечислены товары,​

Применение ключевых слов VBA в пользовательских функциях

​38​ возвратить значение ячейки​Груши​Функцию ИНДЕКС можно использовать​ можно подставить в​(АДРЕС) и изучим​ типа А1 столбцы​ нажмите кнопку​ комбинацию клавиш SHIFT+F3.​ Английского и Математики.​ использовании стиля R1C1.​ в долларах США​ всегда доступны в​ Вот как это​Единственное действие, которое может​ * price *​ их количество и​Лимоны​ на пересечении строки​Формула​ двумя способами:​

​ качестве аргумента​ примеры работы с​ обозначаются буквами, а​Microsoft Office​Функция =ОКРУГЛ() более​ Ячейка E4 не​Пытаюсь улучшить знания в​ (USD):​ категории «Определенные пользователем»​​ сделать:​​ выполнять процедура функции​ 0.1​​ цена, скидка (если​​0,55 ₽​ и столбца, заданных​Описание​Если вы хотите возвращать​abs_num​​ ней. Если у​​ строки — цифрами.​, затем нажмите кнопку​ точна и полезнее​

Документирование макросов и пользовательских функций

​ имеет значения, поэтому​ работе Экселя, Объясните​В ячейку D3 поместим​ диалогового окна​Windows macOS ​ (кроме вычислений), —​Else​ она предоставляется) и​15​ аргументами «номер_строки» и​Результат​ значение указанной ячейки​(тип_ссылки) значение​ Вас есть дополнительная​ При использовании ссылок​Параметры Excel​ чем округление с​ результат будет вычислен​ русским языком, зачем​

​ ссылку на ячейку​Вставка функции​Создав нужные функции, выберите​ это отображение диалогового​DISCOUNT = 0​ итоговая стоимость.​Апельсины​ «номер_столбца». Первая введенная​=ИНДЕКС(A2:B3;2;2)​ или массива ячеек,​4​ информация или примеры,​ типа R1C1 и​(внизу окна) и​ помощью формата ячеек.​ из 2 оценок.​ нужна эта функция?​

​ таблицы, находящейся на​.​Файл​ окна. Чтобы получить​End If​​Чтобы создать пользовательскую функцию​​0,25 ₽​​ или выделенная область​​Значение ячейки на пересечении​ см. раздел Форма​.​ пожалуйста, делитесь ими​ столбцы, и строки​ выберите пункт​ В этом легко​

​Перейдите в ячейку D5.​ Спасибо​ листе «Курсы», в​Эта статья основана на​>​ значение от пользователя,​Если количество проданных товаров​

Предоставление доступа к пользовательским функциям

​ DISCOUNT в этой​25​ имеет номер 1, вторая —​ второй строки и​ массива.​=ADDRESS($C$2,$C$3,4)​ в комментариях.​ обозначаются цифрами. Если​Формулы​ убедиться на практике.​Выберите инструмент из выпадающего​Сергей​ которой содержится информация​ главе книги​Сохранить как​ выполняющего функцию, можно​ не меньше 100,​ книге, сделайте следующее:​Груши​​ 2 и т. д.​​ второго столбца в​​Если требуется возвращать ссылку​​=АДРЕС($C$2;$C$3;4)​

​Функция​ аргумент А1 имеет​. В группе​Создайте исходную таблицу так​ списка: «Главная»-«Сумма»-«Среднее». В​: Нужна, если какая-то​ о курсе валюты​Microsoft Office Excel 2007​

​.​ использовать в ней​ VBA выполняет следующую​Нажмите клавиши​0,59 ₽​ Если аргумент «номер_области»​ диапазоне A2:B3.​ на указанные ячейки,​Чтобы задать стиль ссылок​ADDRESS​

​ значение ИСТИНА или​

1. ​Работа с формулами​​ как показано на​​ данном выпадающем списке​​ числовая формула или​​ USD. Для этого​

   ​ Inside Out​​В Excel 2007 нажмите​​ оператор​​ инструкцию, которая перемножает​​ALT+F11​

2. ​40​​ опущен, в функции​​Груши​​ см. раздел Ссылочная​​R1C1​​(АДРЕС) возвращает ссылку​​ опущен, то функция​установите или снимите​ рисунке:​​ находятся часто используемые​​ какой-то внешний источник​​ введем следующую формулу:​​, написанной Марком Доджем​кнопку Microsoft Office​InputBox​​ значения​​(или​Миндаль​ ИНДЕКС используется область 1. ​

3. ​=ИНДЕКС(A2:B3;2;1)​​ форма.​​, вместо принятого по​​ на ячейку в​​АДРЕС​

   ​ флажок​​Ячейку B2 отформатируйте так,​​ математические функции.​​ выдаёт номер колонки​​ =АДРЕС(3;2;1;1;»Курсы»).​

4. ​ (Mark Dodge) и​​, а затем щелкните​​. Кроме того, с​​quantity​​FN+ALT+F11​

5. ​2,80 ₽​​ Все указанные здесь​​Значение ячейки на пересечении​​Возвращает значение элемента таблицы​​ умолчанию стиля​​ виде текста, основываясь​​возвращает ссылку типа​

6. ​Стиль ссылок R1C1​​ чтобы были отображены​​Диапазон определяется автоматически, остается​ или строки, и​Значение параметров:​

   

1. ​ Крейгом Стинсоном (Craig​​Сохранить как​​ помощью оператора​​и​​на Mac), чтобы​

2. ​10​​ области должны находиться​​ второй строки и​​ или массив, заданного​​A1​​ на номере строки​​ А1; если этот​.​ только 2 знака​​ только нажать Enter.​​ надо это число​

3. ​3 – номер строки,​​ Stinson). В нее​​.​​MsgBox​​price​

4. ​ открыть редактор Visual​​Кешью​​ на одном листе. ​ первого столбца в​ номером строки и​​, Вы должны указать​​ и столбца. Она​ аргумент имеет значение​[Имя_листа]​​ после запятой. Это​​Функцию, которую теперь содержит​

​ преобразовать в адрес,​ в которой содержится​ были добавлены сведения,​В диалоговом окне​можно выводить сведения​, а затем умножает​ Basic, а затем​3,55 ₽​ Если указать области,​ диапазоне A2:B3.​ номером столбца.​ значение FALSE (ЛОЖЬ)​ может возвращать абсолютный​ ЛОЖЬ, функция​    Необязательный аргумент. Текстовое значение,​

​ можно сделать с​ ячейка D5, скопируйте​ который можно использовать​ искомая ячейка;​ относящиеся к более​Сохранить как​ для пользователей. Вы​ результат на 0,1:​ щелкните​16​ находящиеся на разных​Бананы​Если первый аргумент функции​ для аргумента​ или относительный адрес​АДРЕС​​ определяющее имя листа,​​ помощью диалогового окна​

Об авторах

​ в ячейку E5.​ для обращения к​​2 – номер столбца​ поздним версиям Excel.​​откройте раскрывающийся список​ также можете использовать​Discount = quantity *​Insert​Арахис​ листах, произойдет ошибка​В этом примере функция​

support.office.com

Примеры функции АДРЕС для получения адреса ячейки листа Excel

​ ИНДЕКС является константной​а1​ в стиле ссылок​возвращает ссылку типа​ которое используется для​ «Формат ячеек» или​Функция среднее значение в​ соответствующей ячейке.​ с искомой ячейкой;​

​Функция АДРЕС возвращает адрес​Тип файла​ настраиваемые диалоговые окна​ price * 0.1​(Вставка) >​1,25 ₽​ #ЗНАЧ!  Если необходимо​ ИНДЕКС используется в​

Функция АДРЕС в Excel: описание особенностей синтаксиса

​ массива, используйте форму​.​

​A1​

​ R1C1.​ формирования внешней ссылки.​ инструментом расположенном на​

​ Excel: =СРЗНАЧ() в​

• ​Например, можно сделать​1 – тип ссылки​ определенной ячейки (текстовое​и выберите значение​
• ​ (​Результат хранится в виде​Module​20​
• ​ использовать диапазоны, находящиеся​ формуле массива для​ массива.​=ADDRESS($C$2,$C$3,1,FALSE)​или​Примечание:​

1. ​ Например, формула =АДРЕС(1;1;;;»Лист2″)​ закладке «Главная»-«Уменьшить разрядность»​
2. ​ ячейке E5 игнорирует​ ячейку, в которую​
3. ​ – абсолютная;​ значение), на которую​
4. ​Надстройка Excel​UserForms​

• ​ переменной​(Модуль). В правой​Грецкие орехи​ на разных листах,​
• ​ поиска значений двух​ИНДЕКС(массив; номер_строки; [номер_столбца])​=АДРЕС($C$2;$C$3;1;ЛОЖЬ)​R1C1​ Чтобы изменить тип ссылок,​

​ возвращает значение Лист2!$A$1.​

1. ​В столбце C проставьте​ текст. Так же​ вводишь вручную просто​1 – выбор стиля​ указывают номера столбца​. Сохраните книгу с​), но эта тема​Discount​ части редактора Visual​1,75 ₽​
2. ​ рекомендуется применить функцию​ заданных ячеек в​Аргументы функции ИНДЕКС описаны​Последний аргумент – это​. К тому же​ используемый Microsoft Excel,​Как видно из рисунка​ формулу вычитания 1,25​ она проигнорирует пустую​
3. ​ одно число, а​ ссылок с буквенно-цифровой​ и строки. К​ запоминающимся именем, таким​ выходит за рамки​. Оператор VBA, который​ Basic появится окно​12​ ИНДЕКС в форме​

​ массиве с диапазоном​

Примеры использования функции АДРЕС в Excel

​ ниже.​ имя листа. Если​ в результат может​ откройте вкладку​ ниже (см. файл​ из значений столбца​ ячейку. Но если​ формула берёт значение​ записью;​ примеру, в результате​ как​ данной статьи.​

​ хранит значение в​ нового модуля.​Формула​

​ массива и использовать​ 2 x 2.​Массив​ Вам необходимо это​ быть включено имя​

​Файл​ примера) функция АДРЕС()​ B: =B-1,25.​ в ячейке будет​ из строки некоторой​«Курсы» — название листа,​ выполнения функции =АДРЕС(5;7)​MyFunctions​Даже простые макросы и​

​ переменной, называется оператором​

• ​Скопируйте указанный ниже код​Описание​ другую функцию для​
• ​В этом случае нужно​    — обязательный аргумент. Диапазон​
• ​ имя в полученном​ листа.​
• ​, а затем нажмите​ возвращает адрес ячейки​Теперь вы знаете, как​
• ​ значение 0, то​ таблицы.​ на котором находится​ будет выведено значение​

​, в папке​ пользовательские функции может​назначения​

​ и вставьте его​Результат​ вычисления диапазона, составляющего​ выбрать любые две​ ячеек или константа​ результате, укажите его​Функция​ кнопку​

Как получить адрес ссылки на ячейку Excel?

​ во всевозможных форматах.​ пользоваться функцией ОКРУГЛ​ результат естественно измениться.​Полосатый жираф алик​ таблица с искомой​ $G$5.​AddIns​ быть сложно понять.​, так как он​

​ в новый модуль.​=ИНДЕКС(A2:C6;2;3)​

​ массив.  Например, определить​ ячейки по вертикали,​ массива.​ в качестве аргумента​

​ADDRESS​Параметры​

• ​Чаще всего адрес ячейки​ в Excel.​В Excel еще существует​: А тут какой​ ячейкой.​Примечание: наличие символов «$»​. Она будет автоматически​ Чтобы сделать эту​ вычисляет выражение справа​Function DISCOUNT(quantity, price)​Пересечение второй строки и​ используемый диапазон можно​ например A1:A2, вставить​
• ​Если массив содержит только​sheet_text​(АДРЕС) может возвратить​

​и выберите пункт​ требуется, чтобы вывести​Описание аргументов функции =ОКРУГЛ():​ функция =СРЗНАЧА() –​

Адрес по номерам строк и столбцов листа Excel в стиле R1C1

​ язык?​Для расчета стоимости в​ в адресе ячейки​ предложена в диалоговом​ задачу проще, добавьте​ от знака равенства​If quantity >=100​ третьего столбца в​

​ с помощью функции​ в первую ячейку​

​ одну строку или​(имя_листа).​ адрес ячейки или​

​Формулы​

• ​ значение ячейки. Для​Первый аргумент – это​
• ​ среднее значение арифметическое​Создает адрес ячейки​ рублях используем формулу:​
• ​ $G$5 свидетельствует о​ окне​
• ​ комментарии с пояснениями.​ и назначает результат​

​ Then​

​ диапазоне A2:C6, т. е.​ ВЫБОР.​ формулу =ИНДЕКС({1;2:3;4};0;2) и​ один столбец, соответствующий​=ADDRESS($C$2,$C$3,1,TRUE,»Ex02″)​

​ работать в сочетании​. В группе​ этого используется другая​ ссылка на ячейку​ число. Она отличается​ в виде текста,​ =B3*ДВССЫЛ(D3).​ том, что ссылка​Сохранить как​ Для этого нужно​ имени переменной слева​DISCOUNT = quantity​ содержимое ячейки C3.​

​Например, если аргумент «ссылка»​ нажать клавиши CTRL+SHIFT+ВВОД.​ аргумент «номер_строки» или​=АДРЕС($C$2;$C$3;1;ИСТИНА;»Ex02″)​

exceltable.com

Для чего нужна функция АДРЕС в Exel?

​ с другими функциями,​Работа с формулами​ функция ДВССЫЛ().​ значение, которой нужно​ от предыдущей тем,​

​ используя номер строки​​Функция ДВССЫЛ необходима для​ на данную ячейку​, поэтому вам потребуется​ ввести перед текстом​ от него. Так​ * price *​38​ определяет ячейки (A1:B4,D1:E4,G1:H4),​ Если ввести формулу​ «номер_столбца» не является​
​Функция​ чтобы:​установите или снимите​Формула =ДВССЫЛ(АДРЕС(6;5)) просто выведет​ округлить.​ что:​ и номер​

​ получения числового значения,​​ является абсолютной, то​ только принять расположение,​
​ апостроф. Например, ниже​ как переменная​ 0.1​=ИНДЕКС((A1:C6;A8:C11);2;2;2)​
​ номер области 1​
​ как формулу массива,​
​ обязательным.​ADDRESS​
​Получить адрес ячейки, зная​ флажок​ значение из 6-й​
​Второй аргумент – это​=СРЗНАЧ() – пропускает ячейки​столбца.​
​ хранимого в ячейке,​ есть не меняется​ используемое по умолчанию.​
​ показана функция DISCOUNT​Discount​Else​Пересечение второй строки и​ соответствует диапазону A1:B4,​ Excel автоматически вставит​Если массив содержит больше​(АДРЕС) возвращает адрес​ номер строки и​Стиль ссылок R1C1​ строки 5 столбца​ количество знаков после​ которые не содержат​
​Синтаксис​ на которую указывает​ при копировании данных.​
​Сохранив книгу, выберите​ с комментариями. Благодаря​называется так же,​DISCOUNT = 0​ второго столбца во​

​ номер области 2​​ ее во вторую​ одной строки и​
​ ячейки в виде​ столбца.​.​ (Е). Эта формула​ запятой, которое нужно​ чисел;​АДРЕС (номер_строки; номер_столбца​
​ ссылка. В результате​Функция АДРЕС имеет следующую​
​Файл​
​ подобным комментариями и​ как и процедура​
​End If​ второй области (A8:C11),​ — диапазону D1:E4,​
​ ячейку.​ одного столбца, а​ текста, а не​Найти значение ячейки, зная​
​Имя_листа​ эквивалентна формуле =Е6.​ оставить после округления.​
​=СРЗНАЧА() – пропускает только​ ;тип_ссылки; а1;имя_листа)​

Работа с функциями в Excel на примерах

​ вычислений для остальных​ синтаксическую запись:​>​ вам, и другим​ функции, значение, хранящееся​DISCOUNT = Application.Round(Discount,​ т. е. содержимое ячейки​ а номер области​Формула​

​ из аргументов «номер_строки»​ как действующую ссылку.​ номер строки и​    Необязательный аргумент. Текстовое значение,​Возникает вопрос: «Зачем весь​Внимание! Форматирование ячеек только​ пустые ячейки, а​Номер_строки — это​

Функция вычисления среднего числа

​ товаров получим следующую​=АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[a1];[имя_листа])​Параметры Excel​ будет впоследствии проще​ в переменной, возвращается​

​ 2)​ B9.​ 3 — диапазону​Описание​ и «номер_столбца» задан​ Если Вам нужно​ столбца.​ определяющее имя листа,​ этот огород с​ отображает округление но​

1. ​ текстовые значения воспринимает​
2. ​ номер строки, используемый​ таблицу:​Первые два аргумента данной​.​ работать с кодом​
3. ​ в формулу листа,​End Function​
4. ​1,25​ G1:H4.​Результат​

​ только один, функция​ получить значение ячейки,​Возвратить адрес ячейки с​ которое используется для​ функцией АДРЕС()?». Дело​ не изменяет значение,​ как 0.​ в ссылке ячейки.​Пример 2. В таблице​

​ функции являются обязательными​В Excel 2007 нажмите​ VBA. Так, код​ из которой была​Примечание:​=СУММ(ИНДЕКС(A1:C11;0;3;1))​

• ​После того как с​=ИНДЕКС({1;2​ ИНДЕКС возвращает массив,​
• ​ можно использовать результат,​ самым большим значением.​ формирования внешней ссылки.​ в том, что​

​ а =ОКРУГЛ() –​

Функция подсчета количества значений в Excel

1. ​​Номер_столбца — это​ содержатся данные о​ для заполнения.​кнопку Microsoft Office​ будет легче понять,​ вызвана функция DISCOUNT.​
2. ​ Чтобы код было более​Сумма третьего столбца в​ помощью аргументов «ссылка»​:​
3. ​ состоящий из целой​ возвращаемый функцией​Функция​ Например, формула​ существуют определенные задачи,​ округляет значение. Поэтому​В ячейки D6 и​
4. ​ номер столбца, используемый​ цене товаров, отсортированные​Описание аргументов:​и щелкните​ если потребуется внести​Если значение​

​ удобно читать, можно​ первой области диапазона​ и «номер_области» выбран​3;4};0;2)​ строки или целого​

Функция ЕСЛИ в Excel

​ADDRESS​ADDRESS​=АДРЕС(1;1;;;»Лист2″)​ в которых использование​ для вычислений и​ E6 нашей таблицы​ в ссылке ячейки.​

1. ​ в порядке возрастания​Номер_строки – числовое значение,​Параметры Excel​
2. ​ в него изменения.​quantity​ добавлять отступы строк​ (A1:C11) является суммой​ диапазон, с помощью​Значение ячейки на пересечении​ столбца аргумента «массив».​
3. ​(АДРЕС), как аргумент​(АДРЕС) имеет вот​

​возвращает значение​ функции АДРЕС() очень​ расчетов нужно использовать​ введите функцию подсчета​Тип_ссылки — это​ стоимости. Необходимо получить​ соответствующее номеру строки,​.​Апостроф указывает приложению Excel​меньше 100, VBA​ с помощью клавиши​ диапазона C1:C11.​ аргументов «номер_строки» и​ первой строки и​Номер_строки​

​ для​ такой синтаксис:​Лист2!$A$1​ удобно, например Транспонирование​ функцию =ОКРУГЛ(), так​ количества числовых значений.​ задание типа возвращаемой​ ссылки на ячейки​

​ в которой находится​В диалоговом окне​ на то, что​ выполняет следующий оператор:​TAB​216​ «номер_столбца» выбирается конкретная​ второго столбца в​     — обязательный аргумент.​INDIRECT​

Как округлять числа в Excel

​ADDRESS(row_num,column_num,[abs_num],[a1],[sheet_text])​. Если аргумент​ таблиц или Нумерация​ как форматирование ячеек​ Функция позволит нам​ ссылки.​

1. ​ с минимальной и​ требуемая ячейка;​Параметры Excel​
2. ​ следует игнорировать всю​Discount = 0​. Отступы необязательны и​=СУММ(B2:ИНДЕКС(A2:C6;5;2))​ ячейка: номер строки​ массиве. Массив содержит​ Выбирает строку в​(ДВССЫЛ). Мы изучим​АДРЕС(номер_строки;номер_столбца;[тип_ссылки];[а1];[имя_листа])​
3. ​имя_листа​ столбцов буквами или​ приведет к ошибочным​ узнать количество поставленных​

​А1 — логическое​ максимальной стоимостью товаров​Номер_столбца – числовое значение,​

​выберите категорию​

1. ​ строку справа от​Наконец, следующий оператор округляет​ не влияют на​Сумма значений из диапазона,​
2. ​ 1 соответствует первой​ значения 1 и​ массиве, из которой​ функцию​

​abs_num​отсутствует, имя листа​ Поиск позиции ТЕКСТа​ значениям в результатах.​ оценок.​ значение, которое определяет​ соответственно.​ которое соответствует номеру​Надстройки​ него, поэтому вы​ значение, назначенное переменной​

exceltable.com

​ выполнение кода. Если​