Функция поиск позиции в excel: Функция ПОИСКПОЗ — Служба поддержки Майкрософт

Функция ПОИСКПОЗ (MATCH) — Справочник

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Вместе с функцией ИНДЕКС функция ПОИСКПОЗ являет собой более гибкую и мощною альтернативу функции ВПР

Описание функции ПОИСКПОЗ

Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула

=ПОИСКПОЗ(25;A1:A3;0)

возвращает значение 2, поскольку элемент 25 является вторым в диапазоне.

Функцией ПОИСКПОЗ следует пользоваться вместо одной из функций ПРОСМОТР, когда требуется найти позицию элемента в диапазоне, а не сам элемент. Например, функцию ПОИСКПОЗ можно использовать для передачи значения аргумента номер_строки функции ИНДЕКС.

Синтаксис

=ПОИСКПОЗ(искомое_значение; просматриваемый_массив; [тип_сопоставления])

Аргументы

искомое_значениепросматриваемый_массивтип_сопоставления

Обязательный аргумент. Значение, которое сопоставляется со значениями в аргументе просматриваемый_массив. Например, при поиске номера в телефонной книге имя абонента указывается в качестве искомого значения, а нужным значением будет номер телефона. Аргумент искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение.

Обязательный аргумент. Диапазон ячеек, в которых производится поиск.

Необязательный аргумент. Число -1, 0 или 1. Аргумент тип_сопоставления указывает, каким образом в Microsoft Excel искомое_значение сопоставляется со значениями в аргументе просматриваемый_массив. По умолчанию в качестве этого аргумента используется значение 1.

В приведенной ниже таблице описано, как функция находит значения в зависимости от аргумента тип_сопоставления.

Тип сопоставления	Поведение
1 или опущен	Функция ПОИСКПОЗ находит наибольшее значение, которое меньше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по возрастанию: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА.
0	Функция ПОИСКПОЗ находит первое значение, равное аргументу искомое_значение. Просматриваемый_массив может быть не упорядочен.
-1	Функция ПОИСКПОЗ находит наименьшее значение, которое больше или равно значению аргумента искомое_значение. Просматриваемый_массив должен быть упорядочен по убыванию: ИСТИНА, ЛОЖЬ, Z-A, …, 2, 1, 0, -1, -2, … и т. д.

Замечания

• Функция ПОИСКПОЗ возвращает не само значение, а его позицию в аргументе просматриваемый_массив. Например, функция ПОИСКПОЗ(«б»;{«а»;»б»;»в»};0) возвращает 2 — относительную позицию буквы «б» в массиве {«а»;»б»;»в»}.
• Функция ПОИСКПОЗ не различает регистры при сопоставлении текста.
• Если функция ПОИСКПОЗ не находит соответствующего значения, возвращается значение ошибки #Н/Д.
• Если тип_сопоставления равен 0 и искомое_значение является текстом, то искомое_значение может содержать подстановочные знаки: звездочку (*) и вопросительный знак (?). Звездочка соответствует любой последовательности знаков, вопросительный знак — любому одиночному знаку. Если нужно найти сам вопросительный знак или звездочку, перед ними следует ввести знак тильды (~).

Пример

Видео работы функции

Альтернатива ВПР#ExcelWordlChamp 1-2#ExcelWordlChamp 3-4

Дополнительные материалы

Альтернатива функции ВПР (VLOOKUP)
#ExcelWordlChamp 1-2 решаем задачи первого тура часть 2
#ExcelWorldChamp 3-4 последнее взрывоопасное задание

ПОИСКПОЗ в Excel: примеры от специалиста

Всем привет. В Сегодняшнем нашем уроке мы поговорим про достаточно известную функцию в Excel – ПОИСКПОЗ. Я постараюсь все рассказывать на конкретных примерах, чтобы вам было понятнее. Также мы рассмотрим не только функцию поиска позиции в Excel, но и работу её при комбинации с другими инструментами, а в частности с ИНДЕКС. Урок достаточно сложный, поэтому я постарался описать все как можно подробнее. Я вам настоятельно рекомендую читать очень внимательно и не пропускать ни одной строчки. Если же у вас возникнут дополнительные вопросы, или что-то будет непонятно – пишите в комментариях, и я вам помогу.

Содержание

1. Как работает оператор ПОИСКПОЗ
2. Пример 1: Поиск адреса элемента
3. Пример 2: Поиск товара и использование адресов
4. Пример 3: Работа с числовыми значениями
5. Пример 4: Использование с другими функциями
6. Задать вопрос автору статьи

Как работает оператор ПОИСКПОЗ

ПРИМЕЧАНИЕ! Если вам что-то будет в этой главе не понятно, не переживайте, мы еще раз все повторим на примерах. Ваша задача внимательно прочесть эту главу и попытаться уловить саму суть. Саму структуру мы уже разберем далее.

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

Теперь давайте рассмотрим синтаксис:

=ПОИСКПОЗ(Значение;Массив; [Точность поиска])

• Значение – это тот элемент, которые мы хотим найти. Может принимать любые значения от цифирного и символьного до логического или адреса ячейки.
• Массив – это диапазон ячеек, среди которых и будет искать функция ПОИСКПОЗ.
• Точность поиска – этот аргумент указывает на то, насколько точным должно быть значение, которые мы ищем. Может принимать три значения. «1» – если данный элемент не найден по точному значению, то выводит информацию о максимально приближенном по убиванию. «-1» – то же самое что и «1», но выводит максимально приближенное по возрастанию. «0» – ищет только точные совпадения. Данные аргумент не является обязательным и его можно опустить из функции.

Если функция не находит совпадений, то выводит:

#Н/Д

Если в массиве присутствует сразу несколько одинаковых элементов, то он выводит адрес ячейки самого первого. Как правило, данная функция не используется отдельно – только вкупе с другими функциями для работы с адресами ячеек и массивами.

Пример 1: Поиск адреса элемента

В первом примере мы рассмотрим, как именно вообще работает функция, как её заполнять и что она возвращает. Представим себе, что нам нужно среди множества товаров найти адрес массива со значением «Молоко».

1. Поставьте курсор в любую свободную ячейку, куда мы хотим вывести эти данные.
2. Далее рядом со строкой значений кликните по значку «Вставка функции».

3. Ставим «Категорию» – «Ссылки и массивы».
4. Ищем нашу функцию, выделяем её и жмем «ОК».

5. Теперь уже заполняем значения. В первой строке указываем адрес элемента, который мы хотим найти. Вы можете указать как адрес, кликнув мышкой, так и вписать вручную – например:

“Молоко”

6. Далее ниже указываем диапазон адресов – их можно выбрать мышкой, или вписать вручную. Например:

A2:A6

7. «Тип_сопоставления» – это как раз та самая точность. Так как мы работаем с текстом, то лучше указать полную точность:

0

8. Жмем «ОК».

Обратите внимание, что функция выводит адрес относительно массива, а не адрес ячейки. То есть «Молоко» находится в 4 строке, но в массиве товаров адрес – 3. Об этом нужно всегда помнить.

Пример 2: Поиск товара и использование адресов

Прошлый пример нам дал понять, как именно работает функция. Но работать с ней таким образом не очень удобно. Давайте рассмотрим еще один простой пример, где мы сможем немного автоматизировать процесс, дабы не вызывать эту функцию по отдельности для каждого элемента.

1. Давайте создадим еще две строки – «Поиск товара» и «Адрес». Первую строку мы будем использовать в качестве исходного значения, которое в любой момент будет меняться. Для примера введем туда название любого элемента из массива.
2. Теперь ставим курсор в поле «Адрес» и вставляем нашу функцию.

3. По сути, мы делаем все то же самое, только вместо «Искомого значения» мы используем адрес соседней ячейки.

Теперь вы можете изменять элемент, и адрес автоматически будет пересчитываться. Это удобно, когда вы работаете с большими массивами и данными. Попробуйте на практике поизменять значения элемента, который мы хотим найти, на другое.

Пример 3: Работа с числовыми значениями

С текстом и символами работать куда проще, так как, обычно нам нужно найти элементы с точным совпадением. Давайте же посмотрим пример работы с числами и примерными значениями. Наша задача найти товар с прибылью 30 000 или с максимально приближенным значением.

1. Так функция работает последовательно и выводит адрес элемента, который подходит лучше всего – нам нужно отсортировать колонку. Выделите колонку с цифрами, нажав по букве сверху.
2. Перейдите на вкладку «Главная».

3. Теперь справа в разделе «Редактирование» находим значок «Сортировка и фильтр» и выбираем «Сортировка по убыванию».

4. Оставляем настройку по умолчанию и жмем по кнопке сортировки. Вы увидите, что сортировка произошла также со строками.

5. Выбираем любую ячейку и вставляем в неё функцию.

6. Теперь вставляем в первую строчку наше приближенное значение, которое мы хотим найти. Указываем диапазон массива. И в конце ставим «-1», чтобы также попробовать найти не точное совпадение, а приближенное.

7. И мы нашли товар с прибылью, приближенную к 30 000 – им оказалось «Молоко».

А теперь попробуйте взять 2-ой пример с автоматизацией и двумя дополнительными ячейками и применить его к данной ситуации с числами. Еще один момент – если вы сортируете числа по возрастанию, то используем значение «1». На самом деле это самая сложная часть этой функции и нужно будет несколько раз попрактиковаться, чтобы понять – как именно она работает.

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

Как же нам в Excel найти значение в диапазоне по конкретному условию? – для этого мы будем использовать дополнительную функцию ИНДЕКС. ИНДЕКС – это функция, которая выводит значение ячейки массива по заданному адресу строки или столбца. Синтаксис достаточно простой:

=ИНДЕКС(массив;номер_строки;номер_столбца)

Если вам пока ничего не понятно, не стоит переживать – сейчас мы все разберем на примере. В нашем примере – наша задача вывести не просто адрес массива, а наименование товара, которое имеет приближенную сумму к числу 32 000.

1. Давайте теперь попробуем отсортировать сумму по возрастанию – аналогично выделяем весь столбец.

2. Выбираем «Сортировку по возрастанию».

3. В качестве суммы мы будем использовать значение – 32 000. Теперь нам нужно найти товар – вставляем туда функцию.

4. Используем:

ИНДЕКС

5. Нам нужна обычная формула, поэтому оставьте настройки по умолчанию.

6. В качестве массива указываем диапазон товаров.
7. В «Номер строки» нам нужно вписать уже формулу ПОИСКПОЗ. В качестве значения, по которому мы будем искать, указываем соседнюю строку 32 000. Далее указываем диапазон всех сумм. В конце ставим «1», так как мы до этого делали сортировку по возрастанию (вспоминаем прошлый пример). «Номер столбца» не указываем.

Далее он выведет правильную информацию. Если вы посмотрите на картинку ниже, то вы можете немного запутаться – почему «Хлеб», который имеет сумму «23013» максимально приближен к 32 000, а не «Молоко» с суммой в «33670».

Все дело в сортировке. Тут вы должны уяснить и понять, каким образом работает функция ПОИСКПОЗ. При выставлении в формуле аргумента «1» – он возвращает самое близкое к этому по убыванию, а это как раз 23013.

Если вы попытаетесь поставить аргумент «-1», то, скорее всего, вы увидите ошибку, так как сортировка идет по возрастанию. Если же вы хотите получить значение «Молоко», то нужно сначала отсортировать товары по убыванию, а потом использовать эту формулу с аргументом «-1».

Я понимаю, что понять это сразу достаточно сложно, поэтому я вам советую потренироваться и попробовать оба варианта с различными аргументами. Таким образом вы сможете понять логику функции и сможете её применять в ваших примерах и задачах. Лучше всего использовать вместе формулы индекса и поиска позиции в Эксель – да так сложнее, но в итоге вы и поймете, как их обычно используют на практике.

На этом все, дорогие читатели портала WiFiGiD.RU. Я понимаю, что урок получился достаточно сложный. Если что-то было непонятно, или вам нужно что-то объяснить – пишите свои вопросы в комментариях, и я вам помогу.

Используйте функцию ПОИСКПОЗ в Excel, чтобы найти положение значения в списке. Изучите Microsoft Excel

Вы здесь

Главная » Изучите Microsoft Excel » Используйте функцию ПОИСКПОЗ в Excel, чтобы найти положение значения в списке

Форма поиска

Поиск

.

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

Синтаксис функции ПОИСКПОЗ()

Функция ПОИСКПОЗ() имеет следующий синтаксис:

=ПОИСКПОЗ( искомое_значение, искомый_массив, тип_сопоставления)

• lookup_value — это значение, которое вы хотите найти в списке. Это необходимо для работы функции.
• lookup_array — это диапазон ячеек, содержащих список. Это необходимо для работы функции.
• match_type — это необязательное значение , определяющее тип совпадения, которое вы ищете. Он может иметь три возможных значения: -1, 0 и 1. Если его не указать, Excel примет значение 1.

Функция ПОИСКПОЗ находит позицию вашего искомого_значения в диапазоне ячеек, в котором вы просматриваете (массив искомого_массива). Важно отметить, что если в вашем списке нет искомого_значения, функция ПОИСКПОЗ вернет ошибку #NA.

Крайне важно — понимание параметра match_type

Понимание параметра match_type является ключом к эффективному использованию функции ПОИСКПОЗ.

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

• Значение match_type, равное 1 , должно использоваться, когда список отсортирован в порядке по возрастанию (от меньшего к большему).
  • Если ваш список содержит искомое_значение более одного раза, ПОИСКПОЗ вернет позицию последних экземпляров этого значения.
  • Если ваш список не отсортирован, иногда вы получите правильный ответ, но иногда вы получите ошибку #Н/Д.
  • Если ваш список не содержит искомое_значение, ПОИСКПОЗ вернет позицию следующего наибольшего значения.
  • Если вы не укажете match_type, Excel автоматически использует 1 в качестве match_type.
• Значение match_type, равное 0 , означает, что ваш список не нужно сортировать.
  • ПОИСКПОЗ будет искать точное совпадение с вашим искомым_значением в списке
  • ПОИСКПОЗ вернет позицию первых вхождений искомого_значения, которое он находит, даже если в списке есть дубликаты.
  • Важно! ПОИСКПОЗ возвращает ошибку #Н/Д, если искомое_значение отсутствует в списке.
• Значение match_type, равное -1 , следует использовать, когда список отсортирован в порядке по убыванию (от большего к меньшему).
  • Если ваш список не отсортирован, вы иногда будете получать правильный ответ, но иногда вы получите ошибку #N/A.
  • Если ваш список содержит искомое_значение более одного раза, ПОИСКПОЗ вернет позицию последних экземпляр в списке этого значения.
  • Если ваш список не содержит искомое_значение, ПОИСКПОЗ вернет позицию следующего наименьшего значения.

Использование функции ПОИСКПОЗ в Excel — найти совпадение в списке без дубликатов

В этом примере показано, как использовать функцию ПОИСКПОЗ, если ваш список не содержит дубликатов .

• Данные для этого примера представляют собой список имен людей. Список не отсортирован по алфавиту.
• Мы хотим найти, где конкретное лицо появляется в списке. В этом случае мы знаем, что каждый человек появляется только один раз.
• В этой ситуации вы должны установить тип_сопоставления равным 0. Если вы этого не сделаете, Excel установит тип_сопоставления равным 1, и ваша формула, скорее всего, вернет неправильный результат.
• Вы вводите функцию ПОИСКПОЗ в свою электронную таблицу следующим образом:
• В приведенном выше примере ячейка B12 содержит функцию ПОИСКПОЗ, которая возвращает значение 5, поскольку Рамит занимает 4-е место в списке.
  • Обратите внимание, что вы получите результат #N/A, если вы введете в ячейку B11 имя, которого нет в списке.
• B13 показывает нам функцию ПОИСКПОЗ в том виде, в каком она была введена:
  • Значение, для которого необходимо найти совпадение, находится в ячейке B11. Мы также могли бы ввести Ramit в функцию ПОИСКПОЗ непосредственно как значение поиска, а не ссылаться на B11.
  • Диапазон ячеек для поиска: A3:A9, то есть ячейки, содержащие наш список имен.
  • Мы установили для параметра match_type значение 0, поскольку каждое имя появляется только один раз.

Функция ПОИСКПОЗ в действии — поиск соответствия в списке с дубликатами

ПОИСКПОЗ усложняется, когда у вас есть списки, содержащие повторяющиеся значения, поскольку ПОИСКПОЗ может возвращать только одно значение. Другими словами, даже если искомое значение появляется 3 раза, ПОИСКПОЗ может дать вам позицию только одного из этих значений.

Использование ПОИСКПОЗ в несортированном списке, который содержит повторяющиеся значения

• Если ваш список содержит дубликаты, но не отсортирован, наиболее надежный способ использования ПОИСКПОЗ — установить для параметра match_type значение 0.
  • Это вернет позицию первых экземпляров match_type в вашем списке.
  • Кроме того, результат будет возвращен только в случае точного совпадения. Установка match_type на 1 или -1, когда ваш список не отсортирован, будет возвращать непредсказуемые, а иногда и неверные результаты.
• В этом примере у нас есть список чисел, который не отсортирован и содержит повторяющиеся значения. Мы хотим найти позицию определенного числа в этом списке
• В приведенном выше примере мы ищем позицию 52 в списке. Как видите, 52 повторяется в списке три раза.
• Позиция, возвращаемая функцией ПОИСКПОЗ в этом примере, равна 6, потому что это первый экземпляр 52, обнаруженный ПОИСКПОЗ (при запуске из списка и обработке вниз). Два других значения игнорируются функцией ПОИСКПОЗ.
• Очень важно помнить об этом. ПОИСКПОЗ может найти только один экземпляр значения в списке. Это не позволит вам специально искать второй или третий экземпляр значения в списке.

Использование ПОИСКПОЗ в списке, отсортированном в порядке возрастания с повторяющимися значениями

• Если ваш список отсортирован в порядке возрастания, вы должны установить match_type равным 1.
  • Вы также можете вообще не указывать значение match_type, поскольку Excel примет значение 1, если match_type отсутствует.
• В следующем примере используется тот же набор чисел, что и в предыдущем примере:

.

• Обратите внимание, что в этом примере список отсортирован и число 52 занимает позиции 5, 6 и 7 в списке.
• Функция ПОИСКПОЗ с типом соответствия, установленным на 1, вернула 7. Это связано с тем, что последний экземпляр 52 в списке находится в позиции 7.
• Еще раз помните, что ПОИСКПОЗ может вернуть только одно значение из списка.
  • Если вы хотите, чтобы ПОИСКПОЗ возвращал позицию первого экземпляра 52, вы можете установить match_type равным 0.
  • Нет надежного способа заставить ПОИСКПОЗ вернуть позицию второго экземпляра 52 в списке.

Использование ПОИСКПОЗ в списке, отсортированном по убыванию с повторяющимися значениями

• Если ваш список отсортирован в порядке убывания, вы должны установить match_type на -1.
• В следующем примере используется тот же набор чисел, что и в двух предыдущих примерах:

.

• Обратите внимание, что в этом примере список отсортирован и число 52 занимает позиции 5, 6 и 7 в списке.
• Функция ПОИСКПОЗ с типом соответствия, для которого задано значение -1, вернула 7. Как и в предыдущем примере, это связано с тем, что последний экземпляр 52 в списке находится в позиции 7.
• Еще раз помните, что ПОИСКПОЗ может вернуть только одно значение из списка.
  • Если вы хотите, чтобы функция MATCH возвращала позицию первого экземпляра числа 52, вы можете установить match_type равным 0.
  • Нет надежного способа заставить ПОИСКПОЗ вернуть позицию второго экземпляра 52 в списке.

Краткое описание функции ПОИСКПОЗ

ПОИСКПОЗ выполняет очень специфическую и несколько ограниченную функцию в Excel. Если вы планируете использовать ПОИСКПОЗ в своих электронных таблицах, убедитесь, что вы понимаете работу, которую выполняет match_type, определяя, как функция ПОИСКПОЗ будет решать, какой результат следует вернуть. Также рекомендуется включать match_type при использовании функции ПОИСКПОЗ, даже если вам это не нужно. Это позволяет избежать ошибок и упрощает устранение неполадок.

.

Хотите узнать больше? Попробуйте эти уроки:

Как использовать функцию ИНДЕКС Excel для поиска данных в таблице

Как использовать функцию ВПР в Excel

Найти минимальное или максимальное значение в диапазоне ячеек в Excel

Использовать ИНДЕКС для поиска нескольких значений в таблице список

Как использовать INDEX MATCH вместо VLOOKUP

.

.

.

.

Найти позицию последнего вхождения символа в Excel

Из этого руководства вы узнаете, как найти позицию последнего вхождения символа в строке в Excel.

Несколько дней назад коллега столкнулся с этой проблемой.

У него был список URL-адресов, как показано ниже, и ему нужно было извлечь все символы после последней косой черты («/»).

Так, например, из https://example.com/archive/ январь ему нужно было извлечь «январь».

Было бы очень просто, если бы в URL была только одна косая черта.

У него был огромный список из тысяч URL-адресов разной длины и разного количества косых черт.

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

В этом уроке я покажу вам два способа сделать это:

• Использование формулы Excel
• Использование пользовательской функции (созданной с помощью VBA)

Получение последней позиции символа с помощью формулы Excel

Когда у вас есть позиция последнего вхождения, вы можете просто извлечь что-нибудь справа от нее, используя функцию ПРАВИЛЬНО.

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

 =ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ("@",ЗАМЕНИТЬ(A2,"/","@",ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2,"/",""))) ,1))
 

Как работает эта формула?

Давайте разберем формулу и объясним, как работает каждая ее часть.

• ПОДСТАВИТЬ (A2, «/», «» ) — В этой части формулы косая черта заменяется пустой строкой. Так, например, если вы хотите найти любую строку, кроме косой черты, используйте ее здесь.
• LEN(A2)-LEN(SUBSTITUTE(A2″,/», «» )) — Эта часть сообщит вам, сколько косых черт в строке. Он просто вычитает длину строки без косой черты из длины строки с косой чертой.
• ПОДСТАВИТЬ(A2”,/””,”@”,ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2”,/”””,”))) – Эта часть формулы заменит последнюю косую черту на @ . Идея состоит в том, чтобы сделать этого персонажа уникальным. Вы можете использовать любого персонажа, которого захотите. Просто убедитесь, что он уникален и еще не появляется в строке.
• НАЙТИ(«@»,ПОДСТАВИТЬ(A2″,/»»,»@»,ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(А2″,/»»,»))),1) – Эта часть формула даст вам позицию последней косой черты.
• ДЛСТР(A2)-НАЙТИ(«@»,ЗАМЕНИТЬ(A2″,/»»,»@»,ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2″,/»»,»»))),1) – Эта часть формулы говорит нам, сколько символов осталось после последней косой черты.
• =ПРАВИЛЬНО(A2,ДЛСТР(A2)-НАЙТИ(«@»,ЗАМЕНИТЬ(A2″,/»»,»@»,ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2″,/»»»,»)) ),1)) — Теперь это просто даст нам строку после последней косой черты.

Получение последней позиции символа с помощью пользовательской функции (VBA)

Хотя приведенная выше формула великолепна и прекрасно работает, она немного сложна.

Если вам удобно работать с VBA, вы можете использовать пользовательскую функцию (также называемую функцией, определяемой пользователем), созданную с помощью VBA. Это может упростить формулу и сэкономить время, если вам приходится делать это часто.

Давайте использовать тот же набор данных URL-адресов (как показано ниже):

Для этого случая я создал функцию LastPosition, которая находит последнюю позицию указанного символа (в данном случае это косая черта). ).

Вот формула, которая это сделает:

 =ПРАВО(A2,LEN(A2)-LastPosition(A2,"/")+1) 

Как видите, это намного проще, чем первое мы использовали выше.

Вот как это работает:

• LastPosition — наша пользовательская функция — возвращает положение косой черты. Эта функция принимает два аргумента — ссылку на ячейку с URL-адресом и символ, позицию которого нам нужно найти.
• Функция RIGHT затем дает нам все символы после косой черты.

Вот код VBA, создавший эту функцию:

 Функция LastPosition (rCell As Range, rChar As String)
'Эта функция возвращает последнюю позицию указанного символа
«Этот код был разработан Sumit Bansal (https://trumpexcel.com)
Dim rLen как целое число
rLen = Len(rCell)
Для i = rLen To 1 Шаг -1
Если Mid(rCell, i - 1, 1) = rChar Тогда
 Последняя позиция = я
 Выход из функции
Конец, если
Далее я
End Function 

Чтобы эта функция заработала, вам нужно поместить ее в VB Editor. После этого вы можете использовать эту функцию, как и любую другую обычную функцию Excel.

Вот шаги для копирования и вставки этого кода в фоновую часть VB:

Вот шаги для размещения этого кода в редакторе VB:

1. Перейдите на вкладку Разработчик.
2. Выберите параметр Visual Basic. Это откроет редактор VB в бэкэнде.
3. В панели Project Explorer редактора VB щелкните правой кнопкой мыши любой объект книги, в которую вы хотите вставить код. Если вы не видите Project Explorer, перейдите на вкладку View и нажмите Project Explorer.
4. Перейдите в раздел «Вставка» и нажмите «Модуль». Это вставит объект модуля для вашей книги.
5. Скопируйте и вставьте код в окно модуля.

Теперь формула будет доступна на всех листах рабочей книги.

Обратите внимание, что книгу необходимо сохранить в формате .XLSM, так как в ней есть макрос. Кроме того, если вы хотите, чтобы эта формула была доступна во всех используемых вами книгах, вы можете либо сохранить ее в личной книге макросов, либо создать на ее основе надстройку.