Функция Excel ПРАВИЛЬНО с примерами формул
В последних нескольких статьях мы обсуждали различные функции Text — те, которые используются для управления текстовыми строками. Сегодня мы сосредоточимся на функции RIGHT, которая предназначена для возврата заданного количества символов с крайней правой стороны строки. Как и другие текстовые функции Excel, RIGHT очень проста и понятна, тем не менее, у нее есть несколько неочевидных применений, которые могут оказаться полезными в вашей работе.
Синтаксис функции Excel ПРАВИЛЬНО
Функция ПРАВИЛЬНО в Excel возвращает указанное количество символов с конца текстовой строки.
Синтаксис функции ПРАВИЛЬНЫЙ следующий:
ПРАВИЛЬНО(текст, [num_chars])
Где:
- Текст (обязательно) — текстовая строка, из которой вы хотите извлечь символы.
- Num_chars (необязательно) — количество символов для извлечения, начиная с крайнего правого символа.
- Если num_chars опущен, возвращается 1 последний символ строки (по умолчанию).
- Если num_chars превышает общее количество символов в строке, возвращаются все символы.
- Если num_chars является отрицательным числом, правильная формула возвращает #ЗНАЧ! ошибка.
Например, чтобы извлечь последние 3 символа из строки в ячейке A2, используйте следующую формулу:
=ВПРАВО(A2, 3)
Результат может выглядеть примерно так:
Важная заметка! Функция Excel ПРАВИЛЬНО всегда возвращает текстовую строку, даже если исходное значение является числом. Чтобы заставить формулу Right выводить число, используйте ее в сочетании с функцией VALUE, как показано в этот пример.
Как использовать функцию ПРАВИЛЬНО в Excel – примеры формул
В реальных рабочих листах функция Excel ПРАВИЛЬНО редко используется сама по себе. В большинстве случаев вы будете использовать его вместе с другими функциями Excel как часть более сложных формул.
Как получить подстроку, которая идет после определенного символа
Если вы хотите извлечь подстроку, следующую за определенным символом, используйте функцию ПОИСК или НАЙТИ, чтобы определить позицию этого символа, вычтите позицию из общей длины строки, возвращаемой функцией ДЛСТР, и извлеките это количество символов из самого правого стороны исходной строки.
ПРАВИЛЬНО(нитьЛЕН(нить) – ПОИСК(персонаж, нить))
Допустим, ячейка A2 содержит имя и фамилию, разделенные пробелом, и вы хотите перетащить фамилию в другую ячейку. Просто возьмите приведенную выше общую формулу и поставьте A2 вместо нитьи ” ” (пробел) в темпе персонаж:
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(” “,A2))
Формула даст следующий результат:
Аналогичным образом вы можете получить подстроку, которая следует за любым другим символом, например, запятой, точкой с запятой, дефисом и т. д. Например, чтобы извлечь подстроку, следующую за дефисом, используйте следующую формулу:
=ВПРАВО(A2,ДЛСТР(A2)-ПОИСК(“-“,A2))
Результат будет выглядеть примерно так:
Как извлечь подстроку после последнего вхождения разделителя
При работе со сложными строками, которые содержат несколько вхождений одного и того же разделителя, вам часто может понадобиться получить текст справа от последнего вхождения разделителя. Чтобы упростить понимание, взгляните на следующие исходные данные и желаемый результат:
Как вы можете видеть на снимке экрана выше, столбец A содержит список ошибок. Ваша цель — получить описание ошибки, которое следует после последнего двоеточия в каждой строке. Дополнительная сложность заключается в том, что исходные строки могут содержать разное количество экземпляров разделителя, например, A3 содержит 3 двоеточия, а A5 — только одно.
Ключом к поиску решения является определение положения последнего разделителя в исходной строке (последнее появление двоеточия в этом примере). Для этого вам нужно будет использовать несколько различных функций:
- Получить количество разделителей в исходной строке. Это легкая часть:
- Во-первых, вы вычисляете общую длину строки с помощью функции ДЛСТР: ДЛСТР(A2)
- Во-вторых, вы вычисляете длину строки без разделителей с помощью функции ПОДСТАВИТЬ, которая заменяет все вхождения двоеточия ничем: ДЛСТР(ПОДСТАВИТЬ(A2,”:”,””))
- Наконец, вы вычитаете длину исходной строки без разделителей из общей длины строки: LEN(A2)-LEN(SUBSTITUTE(A2,”:”,””))
Чтобы убедиться, что формула работает правильно, вы можете ввести ее в отдельную ячейку, и результатом будет 2, то есть количество двоеточий в ячейке A2.
- Замените последний разделитель каким-нибудь уникальным символом. Чтобы извлечь текст, который следует за последним разделителем в строке, нам нужно каким-то образом «пометить» это последнее вхождение разделителя. Для этого давайте заменим последнее вхождение двоеточия на символ, который нигде не встречается в исходных строках, например, на знак решетки (#).
Если вы знакомы с синтаксисом функции ПОДСТАВИТЬ в Excel, возможно, вы помните, что у нее есть 4-й необязательный аргумент (номер_экземпляра), который позволяет заменить только определенное вхождение указанного символа. А так как мы уже подсчитали количество разделителей в строке, просто укажите указанную выше функцию в четвертом аргументе другой функции ПОДСТАВИТЬ:
=ПОДСТАВИТЬ(A2,”:”,”#”,ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(A2,”:”,””)))
Если вы поместите эту формулу в отдельную ячейку, она вернет эту строку: ОШИБКА: 432# Время ожидания соединения истекло
- Получить позицию последнего разделителя в строке. В зависимости от того, каким символом вы заменили последний разделитель, используйте SEARCH без учета регистра или FIND с учетом регистра, чтобы определить положение этого символа в строке. Мы заменили последнее двоеточие знаком #, поэтому используем следующую формулу, чтобы узнать его позицию:
=ПОИСК(“#”, ПОДСТАВИТЬ(A2,”:”,”#”,ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2,”:”,””))))
В этом примере формула возвращает 10, что является позицией # в замененной строке.
- Возвращает подстроку справа от последнего разделителя. Теперь, когда вы знаете позицию последнего разделителя в строке, все, что вам нужно сделать, это вычесть это число из общей длины строки и получить функцию ПРАВИЛЬНО, чтобы она возвращала столько символов с конца исходной строки:
=ПРАВО(A2,ДЛСТР(A2)-ПОИСК(“$”,ЗАМЕНИТЬ(A2,”:”,”$”,ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2,”:”,””)))))))))
Как показано на скриншоте ниже, формула работает идеально:
Если вы работаете с большим набором данных, где разные ячейки могут содержать разные разделители, вы можете заключить приведенную выше формулу в функцию ЕСЛИОШИБКА, чтобы предотвратить возможные ошибки:
=ЕСЛИОШИБКА(ПРАВО(A2,ДЛСТР(A2)-ПОИСК(“$”,ЗАМЕНИТЬ(A2,”:”,”$”,ДЛСТР(A2)-ДЛСТР(ЗАМЕНИТЬ(A2,”:”,””))) )) А2)
В случае, если определенная строка не содержит ни одного вхождения указанного разделителя, будет возвращена исходная строка, как в строке 6 на снимке экрана ниже:
Как удалить первые N символов из строки
Помимо извлечения подстроки из конца строки, функция Excel ПРАВИЛЬНО пригодится в ситуациях, когда вы хотите удалить определенное количество символов из начала строки.
В наборе данных, используемом в предыдущем примере, вы можете удалить слово «ERROR», которое появляется в начале каждой строки, и оставить только номер ошибки и описание. Чтобы это сделать, вычтите количество символов, которое нужно удалить, из общей длины строки и укажите это число в num_chars аргумент функции Excel ПРАВИЛЬНО:
ПРАВИЛЬНО(нитьЛЕН(нить)-number_of_chars_to_remove)
В этом примере мы удаляем первые 6 символов (5 букв и двоеточие) из текстовой строки в A2, поэтому наша формула выглядит следующим образом:
=ВПРАВО(A2, ДЛИН(A2)-6)
Может ли функция Excel ПРАВИЛЬНО вернуть число?
Как упоминалось в начале этого руководства, функция ПРАВИЛЬНО в Excel всегда возвращает текстовую строку, даже если исходное значение является числом. Но что, если вы работаете с числовым набором данных и хотите, чтобы вывод тоже был числовым? Простым обходным решением является вложение формулы Right в функцию VALUE, которая специально разработана для преобразования строки, представляющей число, в число.
Например, чтобы извлечь последние 5 символов (почтовый индекс) из строки в A2 и преобразовать извлеченные символы в число, используйте следующую формулу:
= ЗНАЧЕНИЕ (ПРАВО (A2, 5))
На снимке экрана ниже показан результат — обратите внимание на числа, выровненные по правому краю в столбце B, а не на текстовые строки, выровненные по левому краю в столбце A:
Почему ПРАВАЯ функция не работает с датами?
Поскольку функция Excel RIGHT предназначена для работы с текстовыми строками, тогда как даты представлены числами во внутренней системе Excel, формула Right не может получить отдельную часть даты, например день, месяц или год. Если вы попытаетесь сделать это, все, что вы получите, — это несколько последних цифр числа, представляющего дату.
Предположим, у вас есть дата 18 января 2017 г. в ячейке А1. Если вы попытаетесь извлечь год с помощью формулы ПРАВИЛЬНО (A1,4), результатом будет 2753, что является последними 4 цифрами числа 42753, которое представляет 18 января 2017 года в системе Excel.
«Итак, как мне получить определенную часть даты?», — спросите вы. С помощью одной из следующих функций:
На следующем снимке экрана показаны результаты:
Если ваши даты представлены текстовыми строками, что часто бывает при экспорте данных из внешнего источника, ничто не мешает вам использовать функцию ПРАВИЛЬНО для извлечения последних нескольких символов в строке, представляющих определенную часть даты:
Не работает функция Excel ПРАВИЛЬНО – причины и решения
Если правильная формула не работает правильно на вашем листе, скорее всего, это происходит по одной из следующих причин:
- В исходных данных есть один или несколько пробелов в конце. Чтобы быстро удалить лишние пробелы в ячейках, используйте либо функцию Excel TRIM, либо надстройку Cell Cleaner.
- num_chars аргумент меньше нуля. Конечно, вам вряд ли захочется намеренно подставлять в формулу отрицательное число, но если num_chars аргумент вычисляется другой функцией Excel или комбинацией различных функций, и ваша правильная формула возвращает #ЗНАЧ! ошибка, обязательно проверьте вложенные функции на наличие ошибок.
- Исходное значение — дата. Если вы внимательно следили за этим руководством, вы уже знаете, почему функция ПРАВИЛЬНО не может работать с датами. Если кто-то пропустил предыдущий раздел, вы можете найти полную информацию в Почему функция Excel ПРАВИЛЬНО не работает с датами.
Вот как вы используете функцию ПРАВИЛЬНО в Excel. Чтобы поближе познакомиться с формулами, обсуждаемыми в этом руководстве, вы можете загрузить наш образец книги ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе.
Доступные загрузки
Функция Excel ПРАВИЛЬНО – примеры (файл .xlsx)
