Функция адрес в excel примеры – Все про Эксель

Функцию АДРЕС можно использовать для получения адреса ячейки на листе по номерам строки и столбца.

Описание

Функцию АДРЕС можно использовать для получения адреса ячейки на листе по номерам строки и столбца. Например, функция АДРЕС(2;3) возвращает значение $C$2. Еще один пример: функция АДРЕС(77;300) возвращает значение $KN$77. Чтобы передать функции АДРЕС номера строки и столбца в качестве ее аргументов, можно использовать другие функции (например, функции СТРОКА и СТОЛБЕЦ).

Функция ВПР

Вертикальное первое равенство. Ищет совпадение по ключу в первом столбце определенного диапазона и возвращает значение из указанного столбца этого диапазона в совпавшей с ключом строке.

Синтаксис: =ВПР(ключ; диапазон; номер_столбца; [интервальный_просмотр]), где

  • ключ – обязательный аргумент. Искомое значение, для которого необходимо вернуть значение.
  • диапазон – обязательный аргумент. Таблица, в которой необходимо найти значение по ключу. Первый столбец таблицы (диапазона) должен содержать значение совпадающее с ключом, иначе будет возвращена ошибка #Н/Д.
  • номер_столбца – обязательный аргумент. Порядковый номер столбца в указанном диапазоне из которого необходимо возвратить значение в случае совпадения ключа.
  • интервальный_просмотр – необязательный аргумент. Логическое значение указывающее тип просмотра:
    • ЛОЖЬ – функция ищет точное совпадение по первому столбцу таблицы. Если возможно несколько совпадений, то возвращено будет самое первое. Если совпадение не найдено, то функция возвращает ошибку #Н/Д.
    • ИСТИНА – функция ищет приблизительное совпадение. Является значением по умолчанию. Приблизительное совпадение означает, если не было найдено ни одного совпадения, то функция вернет значение предыдущего ключа. При этом предыдущим будет считаться тот ключ, который идет перед искомым согласно сортировке от меньшего к большему либо от А до Я. Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Когда найдено несколько совпадений, возвращается последнее из них.

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

Пример использования:

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

Различие интервальных просмотров ВПР

Для цены необходимо использовать функцию ВПР с точным совпадением (интервальный просмотр ЛОЖЬ), так как данный параметр определен для всех товаров и не предусматривает использование цены другого товара, если вдруг она по случайности еще не определена.

Для определения расположения товара используется ВПР с приблизительным совпадением (интервальный просмотр ИСТИНА), так как распределение товара осуществляется по категориям. Из-за того, что наименование товара состоит из названия категории плюс дополнительный текст, по условиям сортировки от А до Я наименования товаров будут идти сразу после наименования категории, поэтому когда в таблице не обнаруживается совпадений с ключом подтягивается первое значение сверху.

В принципе, данный подход устраивал бы, если для товаров, для которых отсутствует категория, не подтягивалось расположение. Обратите внимание на товар «Лук Подмосковье». Для него определено расположение «Стелаж №2», хотя в первой таблице нет категории «Лук». Это происходит все по тем же причинам, когда функцией не обнаруживается равенств, то она определяет для значения значение меньшего самого близкого по сортировке ключа, а для «Лук Подмосковье» это категория «Картофель».

Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(” “;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.

Помимо всего описанного, функция ВПР позволяет применять для текстовых значений подстановочные символы – * (звездочка – любое количество любых символов) и ? (один любой символ). Например, для искомого значения “*” & “иван” & “*” могут подойти строки Иван, Иванов, диван и т.д.

Также данная функция может искать значения в массивах – =ВПР(1;{2;”Два”:1;”Один”};2;ЛОЖЬ) – результат выполнения строка «Два».

1. Диалоговые окна «Вставка функции» и «Аргументы функции»

Шаг 1. Вводим в ячейку А7 Максимум и переходим в ячейку В7:

Вставка функции Excel

Шаг 2. Открываем диалоговое окно «Вставка функции» (лента Формулы → команда Вставить функцию):

Вставка функции Excel

Как только мы вызвали диалоговое окно «Вставка функции», в вычисляемой ячейке появился знак «=».

Шаг 3. Выбираем в списке из 10 последних использовавших функций «МАКС» (поле «Выберите функцию»).

Это ещё один способ вставить функцию в дополнение к уже известным по прошлой статье (Excel 9). В Excel встроено около 300 функций. Вряд ли вы будете использовать все функции. Поэтому полезно знать, где находится список из самых вами любимых функций, чтобы избежать длительных поисков.

В поле «Поиск функции» вы можете ввести описание нужной вам функции, и Excel услужливо поможет вам в поиске.

При нажатии кнопки выпадающего меню поля «Категории» вы увидите перечень всех категорий функций:

Вставка функции Excel

Нашли функцию МАКС? ОК!

Но у нас с вами мелкая неприятность – диалоговое окно «Аргументы функции» перекрывает ряд чисел:

Вставка функции Excel

Шаг 4. Щелкаем по значку со стрелкой справа от поля «Число!». Диалоговое окно свернется и вам надо выбрать диапазон, в котором будет искаться максимум.

Вставка функции Excel

Шаг 5. Щелкаем по значку со стрелкой справа от поля «Число!» – диалоговое окно «Аргументы функции» развернется. Нажимаем ОК.

В предыдущих версиях Excel эта операция носила название «Мастер функций.

Понять и запомнить! Кнопка со стрелкой, которая позволяет свернуть и развернуть диалоговое окно, встречается очень часто, так что рекомендую запомнить эту полезную операцию

Функция СТРОКА

Определяет и возвращает номер строки указанной ссылкой ячейки.

Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.

Пример использования:

=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.

Примеры

Как видно из рисунка ниже (см. файл примера ) функция АДРЕС() возвращает адрес ячейки во всевозможных форматах.

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

Формула =ДВССЫЛ(АДРЕС(6;5)) просто выведет значение из 6-й строки 5 столбца (Е). Эта формула эквивалентна формуле =Е6 .

Возникает вопрос: “Зачем весь этот огород с функцией АДРЕС() ?”. Дело в том, что существуют определенные задачи, в которых использование функции АДРЕС() очень удобно, например Транспонирование таблиц или Нумерация столбцов буквами или Поиск позиции ТЕКСТа с выводом значения из соседнего столбца.

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

Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.

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

  • Строка – обязательный аргумент. Число, представляющая номер строки, для которой необходимо вернуть адрес;
  • Столбец – обязательный аргумент. Число, представляющее номер столбца целевой ячейки.
  • тип_закрепления – необязательный аргумент. Число от 1 до 4, обозначающее закрепление индексов ссылки:
    • 1 – значение по умолчанию, когда закреплены все индексы;
    • 2 – закрепление индекса строки;
    • 3 – закрепление индекса столбца;
    • 4 – адрес без закреплений.
  • стиль_ссылки – необязательный аргумент. Логическое значение:
    • ИСТИНА – формат ссылок «A1»;
    • ЛОЖЬ – формат ссылок «R1C1».
  • имя_листа – необязательный аргумент. Строка, представляющая имя листа, который необходимо добавить к тексту адреса ячейки.

Пример использования:

=АДРЕС(1;1) – возвращает $A$1.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;”Лист1″) – результат выполнения функции Лист1!R[1]C[1].

ГПР

Похожим образом работает и функция ГПР (HLOOKUP):

funktsii-ssylki-massivy-excel-3.png

Примечания Remarks

Если ссылка содержит более одной ячейки, аргументы RowAbsolute и ColumnAbsolute применяются ко всем строкам и столбцам. If the reference contains more than one cell, RowAbsolute and ColumnAbsolute apply to all rows and columns.

Рейтинг
( 1 оценка, среднее 5 из 5 )
Загрузка ...