Auditportal.ru

Аудит Портал
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как найти адрес ячейки, содержащей определенный текст в Excel?

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

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

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

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

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

U RL-адреса было бы намного легче читать, если бы в них присутствовал только один слеш.

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

Чтобы решить такие проблемы, найдите последнюю косую черту в URL.

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

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

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

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

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

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

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

  • REPLACE (A2; «/»,»») — Эта часть формулы заменяет косую черту пустой строкой. Так, например, если вы хотите найти вхождение любой строки, кроме слэша, используйте его здесь.
  • LEN (A2) -LEN (REPLACE (A2; «/»,»»)) — Эта часть скажет вам, сколько косых черт находится в строке. Просто вычтите длину строки без косой черты из длины строки с косой чертой.
  • SUBSTITUTE (A2, «/», «@», LEN (A2) -LEN (SUBSTITUTE (A2, «/», «»))) — В этой части формулы вместо последней косой черты будет подставлена @. Идея заключается в том, чтобы сделать этого персонажа уникальным. Вы можете использовать любой символ, который вам нравится. Только убедитесь, что она уникальна и не находится уже на линии.
  • LEN («@», SUBSTITUTE (A2, «/», «@», LEN (A2) -LEN (SUBSTITUTE (A2, «/», «»)), 1) — Эта часть формулы даст вам позицию последней косой черты.
  • LEN (A2) -FIND («@», SUBSTITUTE (A2, «/», «@», LEN (A2) -LEN (SUBSTITUTE (A2, «/», «»)), 1) — Эта часть формулы говорит вам, сколько символов находится после последней косой черты.
  • = RIGHT (A2; LEN (A2) -FIND («@», SUBSTITUTE (A2, «/», «@», LEN (A2) -LEN (SUBSTITUTE (A2, «/», «»))), 1 )) — Теперь мы получим только строку после последней косой черты.
Читайте так же:
Как запустить макрос на основе значения ячейки в Excel?

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

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

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

Вот те же URL, которые мы использовали ранее (см. ниже):

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

Вот формула, которая поможет справиться с этой задачей:

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

Это работает следующим образом:

  • Настраиваемая функция LastPosition возвращает позицию косой черты. Эта функция принимает два аргумента — ссылку на ячейку с URL и символ, положение которого мы хотим найти.
  • Тогда функция TRUE дает нам все символы после слэша.

Ниже приведен код VBA, лежащий в основе этой функции:

Для работы этой функции необходимо использовать редактор V B. После этого функцию можно использовать так же, как и любую другую функцию Excel.

Скопируйте и вставьте в серверную часть VB следующий код:

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

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

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

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

Возможно, вам также понравятся следующие учебники по Excel:

Excel. Формула адреса ячейки с максимальным (минимальным) значением в диапазоне

Чем больше я использую формулы массивов, тем больше мне это нравится! Несколько месяцев назад мне нужно было найти ячейку, содержащую максимальное значение в диапазоне. В качестве первого шага я создал диапазон A1:F10 (рис. 1). 1), заполненный случайными целыми числами от 1 до 100 с помощью функции =FIGURE(1;100).

Читайте так же:
Как импортировать несколько текстовых файлов из папки на один лист?

Рис. 1. Внутренний алканс

Скачайте записку в формате Word, примеры — в формате Excel.

Примечания. Если рисунки выглядят мелко, загрузите их на свой ПК. Мне не удалось «укротить» кавычки внутри формул, они все время изображались как «лапочки», поэтому пришлось использовать рисунки. Текст формул можно получить в файле Word или Excel.

Чтобы было легче визуально отслеживать максимальные и минимальные значения в диапазоне, я использовал условное форматирование (рис. 2).

Рисунок 2: Условное форматирование во всех ячейках диапазона позволяет выделить цветом ячейки, содержащие максимальные и минимальные значения

Excel обновляет функцию =HIFT() после завершения операции с числами (но не при форматировании). Нажмите F9 (команда «пересчитать»).

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

Аналогичную формулу, которая вычисляет адрес ячейки с максимальным значением в диапазоне, я нашел в книге «Формулы в MS Excel 2010» Валькенбах:

Формула массива возвращает номер строки, которая содержит максимальное значение в одномерном вертикальном диапазоне «массива»:

Для того чтобы применить формулу Уокенбаха, нам нужно назвать наш диапазон значений A1:F10. На рис. 1 показан динамический массив с именем array. 3). Чтобы упростить задачу, вместо использования массива можно набрать формулу, выделенную на рис. 3, следующую: =Лист1!$A$1:$F$10. Когда позже вам понадобится расширить диапазон, преимущества динамического массива на основе HUMBLE станут очевидными. Добавьте, например, строки и/или столбцы. При этом, если вы создали статический диапазон, вам придется вручную изменить область определения массива под именем массива. Однако если вы создали динамический массив, вам ничего не придется делать! Тем, кто не знаком с работой функции AMSH, я рекомендую прочитать статью Автоматическое обновление сводной таблицы.

Рисунок 3: Именование двухмерного диапазона

Вот как работает формула Валькенбаха:

С помощью функции IF создается виртуальный массив, соответствующий диапазону массива. Ячейки, в которых находится максимальное значение, будут иметь элемент виртуального массива, равный номеру строки ячейки; в противном случае элемент массива будет пуст. Функция MIN работает с виртуальными массивами и возвращает минимальную строку, содержащую максимальное значение диапазона массива. Если в диапазоне массива есть несколько ячеек с максимальными значениями, возвращается номер первой строки, содержащей это значение.

Читайте так же:
Как изменить верхний регистр на нижний в Microsoft Excel?

К сожалению, формула Уокенбаха была разработана для одномерного вертикального диапазона, поэтому вместо того, чтобы вернуть $A$8, она вернет $D$8.

Чтобы адаптировать формулу Уокенбаха для вычисления адреса ячейки с максимальным значением в двумерном диапазоне, создадим еще один динамический массив — столбецMax (рис. 4).

Рисунок 4 — Именование одномерного вертикального диапазона, содержащего один столбец, в том числе ячейку с максимальным значением

Этот одномерный вертикальный диапазон формируется в результате работы функции SMESH (рис. 5). Однако, в отличие от примера выше, здесь мы не можем обойтись без динамического массива, так как номер столбца, содержащего ячейку с максимальным значением, заранее не известен.

Рисунок 5. Функция SMESH, динамически формирующая одномерный вертикальный диапазон

Функция ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»») создает виртуальный массив, соответствующий диапазону массив. Если ячейка содержит максимальное значение, то соответствующий элемент в виртуальном массиве равен номеру столбца этой ячейки, в противном случае элемент массива равен пустой строке.

Функция МИН(ЕСЛИ(массив=МАКС(массив);СТОЛБЕЦ(массив);»»)) использует виртуальный массив в качестве своего аргумента и возвращает минимальный номер столбца, где содержится максимальное значение диапазона массив. Если в диапазоне массив имеется несколько ячеек с максимальными значениями, то возвращается номер самого левого столбца, где содержится это максимальное значение.

Итоговая формула для нахождения адреса ячейки с максимальным значением в двумерном пространстве выглядит следующим образом (рисунок 6)

Рисунок 6: Формула для нахождения адреса ячейки с максимальным значением в двумерном диапазоне.

Подробности работы формулы можно узнать с помощью Excel — вычисляя формулу шаг за шагом (Рисунок 7).

Рис. 7. Пошаговый расчет по формуле

Аналогичная формула создается для нахождения адреса ячейки с минимальным значением в двухмерном диапазоне:

Абсолютные и относительные ссылки в Excel

Ссылки в Excel могут быть абсолютными или относительными. При копировании и заполнении ячеек эти ссылки ведут себя по-разному. Относительные ссылки изменяются при копировании формулы из одной ячейки в другую, в то время как абсолютные ссылки, напротив, не изменяются независимо от того, куда вы их копируете.

Относительные ссылки в Excel

В Excel все ссылки по умолчанию являются относительными. Если вы копируете ссылку из одной ячейки в другую, позиции столбцов и строк новой ячейки будут соответствовать позициям ячейки, из которой была скопирована ссылка. Например, если скопировать формулу =A1+B1 из строки 1 в строку 2, она станет =A2+B2. Использование относительных ссылок полезно, когда нужно повторить одно и то же вычисление в нескольких столбцах и строках.

Читайте так же:
Как изменить номер стартовой страницы в Excel?

Как создать и скопировать формулу с относительными ссылками

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

  • Выделим первую ячейку, в столбце «Итог» в которой будет создана наша формула:

относительные ссылки в excel - 1

  • Вставим в ячейку D2 формулу, которая перемножает цену за блюдо и количество: =B2*C2 .
  • Нажмите клавишу «Enter» на клавиатуре. Формула произведет расчет и его результат вы увидите в ячейке D2 .

Абсолютные и относительные ссылки в Excel

  • Щелкните левой кнопкой мыши и перетащите ячейку D2 в правый нижний угол всего диапазона ячеек D3:D12 . Это скопирует формулу из ячейки D2 и перенесет ее в каждую ячейку диапазона.

относительные ссылки в excel - 3

  • Чтобы убедиться, что формулы были скопированы правильно, дважды щелкните на любой ячейке диапазона, в которой была нарисована формула, и вы увидите, как появится формула умножения ячейки.

относительные ссылки в excel - 4

Абсолютные ссылки в Excel

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

Для создания абсолютной ссылки используется знак доллара «$». С его помощью вы можете зафиксировать от изменений столбец, строку или всех вместе:

$A$2 — столбец и строка не изменяются при копировании формулы;

A$2 — при копировании формулы не меняется только строка;

$A2 — столбец не изменяется при копировании формулы .

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

Как создать и скопировать формулу с абсолютными ссылками

В нашем примере мы будем использовать в ячейке E1 — 18% как значение НДС для расчета налога на товары в колонке D . Для правильного расчета нам потребуется использовать абсолютную ссылку $E$1 в нашей формуле, так как нам важно, чтобы стоимость каждого товара перемножалась на ставку НДС, указанную в ячейке E1 . Ниже рассмотрим как мы, будем это делать:

  • Выберите ячейку, в которую мы хотим вставить формулу для расчета налога. В нашем примере это ячейка D3.
Читайте так же:
Как изменить цвета по точкам для диаграммы в Excel?

абсолютные ссылки в excel - 1

  • Напишем формулу, которая вычисляет налог на каждый товар, учитывая его цену и количество =(B3*C3)*$E$1 .

абсолютные ссылки в excel - 21

  • Расширьте формулу, чтобы включить все ячейки диапазона D4:D13.

абсолютные ссылки в excel - 3

  • Дважды щелкните ячейку в диапазоне D4:D13 и проверьте правильность работы формулы. Важно убедиться, что вы правильно ссылаетесь на ячейку $E$1 в абсолютном формате.

Как создать ссылки на другие листы в Excel

Нам часто приходится использовать в расчетах данные из разных листов файла Excel. Для этого при создании ссылки на ячейку в другом листе используйте имя листа и восклицательный знак в конце (! ). Например, если вы хотите создать ссылку на ячейку A1 на листе 1, ссылка на эту ячейку будет выглядеть следующим образом

ВАЖНО! Если в название листа, на ячейку с которого вы ссылаетесь есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (‘ ‘). Например, если название вашего листа Бюджет Финал, то ссылка на ячейку A1 будет выглядеть так:

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

  • Выберем ячейку, на которую мы хотим сослаться и обратим внимание на название листа. В нашем случае это ячейка E14 на вкладке «Меню»:

Ссылки между листами в Excel - 11

  • Давайте перейдем на рабочий лист и выделим ячейку, в которой мы хотим разместить ссылку. В нашем примере это ячейка B2.

Ссылки между листами в Excel - 22

  • В ячейке B2 введем формулу, ссылающуюся на ячейку E14 с листа «Меню»: =Меню!E14
  • Нажмем клавишу «Enter» на клавиатуре и увидим в ячейке B2 значение ячейки E14 с листа «Меню».

Если впоследствии вы переименуете лист, на который ссылались, формула будет обновлена автоматически.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе «От новичка до мастера Excel«. Успей зарегистрироваться по ссылке!

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector