Лишними она считает пробелы в начале и конце ячейки, а также двойные, тройные и т.д. пробелы между словами и символами в середине ячейки. В целом речь о любом количестве пробелов более одного между словами. Функция часто несет вспомогательную роль при создании сложносоставных формул.
ПСТР выделяет фрагмент текста из середины текстовой записи. Функция должна знать, с какой позиции начинается извлекаемый фрагмент и сколько символов выбрать. Если количество выбираемых символов превышает доступное, будут выбраны только доступные символы.
В этом примере показано, как извлечь элемент переменной длины, который находится внутри текста. В данном случае это имя внутри ФИО. Извлекаемый текст имени всегда находится между первым и вторым пробелом.
Извлекаем имя из ФИО с помощью функции ПСТР
Пример 2
Функция ПСТР используется для извлечения из строки первого слова, содержащего нужный фрагмент. Функция ПОДСТАВИТЬ в комбинации с ПОВТОР создают большое количество пробелов между словами, чтобы гарантировать извлечение только одного слова с лишними пробелами справа и слева. Функция СЖПРОБЕЛЫ после этого удаляет их.
Извлекаем слова, содержащие определенный паттерн, с помощью функции ПСТР
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Функция ПРАВСИМВ возвращает часть адресуемого значения справа, длиной в указанное количество символов. Иными словами, позволяет извлечь последние N символов ячейки или другого объекта, к которому применяется.
Функция будет полезна лишь тогда, когда количество символов, которые нужно взять справа в строке, фиксировано и известно заранее.
На практике это количество зачастую приходится вычислять, а для этого требуется поиск с конца строки справа налево в ячейке. Поскольку простой функции для этого нет (функции ПОИСК и НАЙТИ ищут слева направо), методам решения задачи посвящена отдельная статья.
Синтаксис ПРАВСИМВ
=ПРАВСИМВ(Текст;Количество Символов)
Второй аргумент опционален. Если его не опустить (не указывать), формула возвращает один последний символ.
Если второй аргумент равен или превышает длину строки, возвращается весь исходный текст ячейки.
Если он равен нулю — возвращается пустая строка.
Если указывается отрицательное число, возвращается ошибка #ЗНАЧ.
ПРАВСИМВ – форматирование
Как и в случае с ЛЕВСИМВ, любые числовые форматы (дата, дата-время, денежный, процентный) преобразуются в натуральное число и только потом в строковый формат.
Поэтому для корректного текстового извлечения части символов может понадобиться функция ТЕКСТ (пример выше).
Применение
Следующая таблица была использована для извлечения фамилии человека из ячейки с его именем и фамилией.
Функция НАЙТИ находит позицию пробела между именем и фамилией
Длина фамилии рассчитывается путем вычитания позиции пробела из общей длины текста ячейки, которая, в свою очередь, высчитывается функцией ДЛСТР
Функция ПРАВСИМВ после этого извлекает фамилию по указанной позиции
Извлекаем второе слово с помощью функции ПРАВСИМВ
Другие примеры формул с ПРАВСИМВ
Функция крайне востребована в самых различных сценариях. Ниже список статей, в которых применяются формулы с функцией ПРАВСИМВ:
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Второй аргумент, число символов для извлечения, опционален и по умолчанию (если его не указывать) равен 1, т.е. результатом формулы будет первый символ.
Если второй аргумент равен или превышает длину строки, возвращается весь исходный текст ячейки.
Если он равен нулю – возвращается пустая строка.
Если указывается отрицательное число, возвращается ошибка #ЗНАЧ.
ЛЕВСИМВ – форматирование
Необходимо учитывать, что, если первым аргументом указать ячейку с датой или датой-временем, или формулу, возвращающую их, функция ЛЕВСИМВ преобразует их в натуральное число и только после этого в строковое значение.
Поэтому для корректного текстового извлечения части символов из дат в числовом формате может понадобиться функция ТЕКСТ (см. пример на картинке выше).
Примеры формул с функцией ЛЕВСИМВ
Рассмотрим примеры использования функции ЛЕВСИМВ на практике.
Пример 1 – извлечь первое слово
В этом простейшем примере извлекаем первое слово в ячейке с помощью комбинации функций ЛЕВСИМВ и НАЙТИ.
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1)-1)
Определяем номер позиции первого пробела и возвращаем символы до этой позиции
Таблица выше была использована для извлечения имени из строки с именем и фамилией. Функция НАЙТИ используется для определения положения пробела между именем и фамилией. Следовательно, длина имени – это позиция пробела минус один символ. Функция ЛЕВСИМВ извлекает имя на основе его длины.
А как извлечь фамилию (второе слово)? Ответ на этот вопрос смотрите в описании функции ПРАВСИМВ.
Пример 2 – извлечь кроме последнего символа
В комбинации ЛЕВСИМВ с функцией ДЛСТР извлекаем из строк переменной длины все, кроме последнего знака.
Вычисляем длину строки, вычитаем из нее 1 и возвращаем соответствующее количество символов функцией ЛЕВСИМВ
Пример 3 – извлечь текст до первой цифры (формула массива)
В каталоге освещения название коллекции всегда представлено текстовым обозначением. Артикул конкретного товара при этом всегда начинается с цифр. Задача – извлечь из названия конкретного товара его коллекцию без артикула.
Получается, задача состоит в том, чтобы извлечь символы до любой первой цифры. Сделаем это с помощью комбинации ЛЕВСИМВ с функциями МИН и ПОИСК.
Формула заимствует механику из первого примера, но является формулой массива и выглядит следующим образом:
Формула вводится сочетанием клавиш Ctrl + Shift + Enter, как любая формула массива. Но если у вас Office 365 или Excel 2021 (и позже) – то можно просто клавишей Enter.
Как работает формула:
Функция ПОИСК ищет одновременно 10 цифр, перечисленных в массиве, и возвращает массив позиций
Поскольку часть цифр при поиске возвращают ошибку, используется функция ЕСЛИОШИБКА, чтобы для таких значений вернуть заведомо наибольшее число (в данном случае 1000)
Функция МИН возвращает наименьшее из чисел – это и будет позиция первой цифры в строке
Поскольку перед цифрами также всегда присутствует пробел, вычитается 2 символа, а не 1. Можно перестраховаться на случай отсутствия пробелов – оставить 1 и убрать пробелы функцией СЖПРОБЕЛЫ.
Функция ЛЕВСИМВ возвращает текст до вычисленной таким образом позиции последнего знака, идущего перед первой цифрой и пробелом перед ней.
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Будет интересно тем из вас, у кого есть маленькие дети, которым предстоит этому научиться. А так же тем из вас, кому интересны возможности Excel и Google Spreadsheets.
Многие знают меня как фаната Excel. Это недалеко от правды – я использовал его для самых разных нужд, рабочих и нет. Решил множество исследовательских задач. Попарсил немало сайтов, обработал гигабайты текстовых данных. Написал сотни макросов, создал свою надстройку для Excel и сделал ее платным продуктом, который даже оказался востребован, чему я очень рад. Но редко решал по-настоящему личные задачи, скорее решал общественные проблемы, задачи предприятий и клиентов.
Здесь же история про очень личное. Тем не менее, поскольку результат удивил меня самого, я решил поделиться им с вами.
Эта функция преобразует фрагмент текста, который похож на число, в фактическое число.
Если число в середине длинного фрагмента текста, его нужно будет извлечь, используя другие текстовые функции, такие как ПОИСК, ПСТР, НАЙТИ, ЗАМЕНИТЬ, ЛЕВСИМВ, ПРАВСИМВ.
Синтаксис
=ЗНАЧЕН(Текст)
Форматирование
Специального форматирования не требуется.
Результат будет показан в виде числового значения на основе исходного текста.
Если знак % включен в текст, результатом будет десятичная дробь, которая может затем быть отформатирована в процентах.
Если исходный текстовый формат отображается как время чч:мм, результатом будет время.
То же самое применимо для других форматов.
Пример применения функции
Извлечь количество процентов из текста сложно, не зная заранее, сколько в нем знаков. Это может быть от 1 цифры (5%) до 4 цифр с запятой (12,25%).
Единственный способ определить процентное значение – это факт, что оно всегда заканчивается знаком %. Невозможно определить начало значения, за исключением того, что ему предшествует пробел.
Основная проблема заключается в расчете длины числа для его извлечения.
Если при извлечении предположить максимальную длину из четырех цифр и знака %, когда процент только одна цифра, при обычном извлечении по маске “?????%” в выражение попадут буквы.
Чтобы обойти проблему, можно использовать функцию ПОДСТАВИТЬ, чтобы увеличить количество пробелов между словами в тексте. Теперь при извлечении по маске
?????%
любые лишние символы будут пробелами, которые функция ЗНАЧЕН проигнорирует.
Формула ниже аналогична формуле на картинке и извлечет из ячейки A1 проценты длиной от 1 до 5 знаков, включая запятую:
Как функция ЗНАЧЕН помогает работать с числами в тексте
Функция ЗНАЧЕН в Excel преобразует текст в число, если это возможно. Это особенно полезно, когда данные представлены как текст (например, “123”, “005”, “1 000”), но вам нужно выполнить арифметические действия — сложение, умножение, процент и т.д. Без предварительного преобразования Excel не будет воспринимать такие значения как числа, а результат формулы окажется неверным или вернёт ошибку.
Часто ЗНАЧЕН комбинируется с текстовыми функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР и ПОИСК. Это позволяет «вырезать» нужную часть строки и затем применять к ней математические операции. Такой подход особенно актуален при обработке кодов, артикулов, цен, идентификаторов, которые хранятся в виде текста.
Функция ТЕКСТ конвертирует число в текст в заданном числовом формате.
Это могут быть дата, время и их фрагменты, финансовые единицы, проценты.
Возможна конвертация в собственный пользовательский формат, который можно создать на основе уже имеющихся.
Весь список форматов можно найти с помощью процедуры формат ячеек. Процедуру можно вызвать с помощью контекстного меню, которое можно вызвать клавишей или правой кнопкой мыши на любой ячейке.
И далее выбрать нужный тип в списке числовых форматов.
Синтаксис
=ТЕКСТ(Число;Формат)
Форматирование
Специального форматирования не требуется.
Логические значения не конвертируются в их численные эквиваленты, но становятся текстом.
Числа, отформатированные как текст, воспринимаются как числа.
Примеры применения функции
Пример 1
Для обеспечения корректной сортировки чисел в текстовом формате может понадобиться добавление дополнительных нулей перед числами, чтобы уравнять количество знаков.
Специалист использовал формулу ТЕКСТ с пользовательским форматом.
Добавление дополнительных нулей к числам для обеспечения их одинаковой длины
Пример 2
Телефонные номера клиентов хранятся в базе данных в нормализованном виде, безо всяких разделителей и символов. Для удобства ручного прозвона базы руководитель отдела продаж использовал пользовательский формат номера в функции ТЕКСТ.
Перевод телефонных номеров в красивый формат с помощью функции ТЕКСТ в Excel
Понравилась статья? Поддержите ее автора! Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!
Эта функция производит отрицание входящего логического значения.
Можно сказать и иначе – функция проверяет истинность значения или вычисления. Если входящее значение или вычисление ложно, результат функции – ИСТИНА, и наоборот
Синтаксис
=НЕ(Выражение)
Форматирование
Требования ко входящим значениям аналогичны функциям И, ИЛИ.
Отличие может быть в формате результата – И и ИЛИ возвращают единственное значение. Отрицание функцией НЕ производится с каждым его элементом и возвращается аналогичный массив.
Любые числа, отличные от нуля, интерпретируются как ИСТИНА, ноль – как ЛОЖЬ.
Любые текстовые значения, кроме “ЛОЖЬ”, “ИСТИНА” в текстовом формате, отдают ошибку #ЗНАЧ.
Родители выбирают имя для девочки, хотят, чтобы в имени присутствовали мягкие звуки (все три).
Excel не позволяет фильтровать строки по более чем двум критериям.
Но для этой задачи можно воспользоваться формулой массива с функцией И в сочетании с функцией ПОИСК и ЕЧИСЛО.
Обратите внимание, что для того, чтобы функция проверяла вхождения всех букв, это должна быть именно формула массива. Ее нужно вводить без фигурных скобок, но ввод в ячейку осуществлять с помощью сочетания клавиш Ctrl + Shift + Enter.
{=И(ЕЧИСЛО(ПОИСК(C$2:C$4;$A11)))}
Вот она точь-в-точь, выделена на скриншоте. Определяет, присутствуют ли в имени все три буквы:
Пример использования функции И в формуле массива
В столбце H нам пригодилась функция ИЛИ – она пропускает имена, если они содержат ИСТИНА хотя бы по одному из наборов букв.
