Примеры использования функции ЛЕВСИМВ в Excel работа с текстом
Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.
Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?
Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.
Исходная таблица данных:
Для выделения первого символа, которым является искомое название переменной, используем формулу:
Второй равен 1 по умолчанию, поэтому функция возвращает первый символ. Аналогично выделим названия переменных из других строк. В результате получим:
Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.
Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?
Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.
Выделим код ошибки с помощью следующей формулы:
- ЛЕВСИМВ(B5;НАЙТИ(«3»;B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
- 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).
В результате получим:
Выделим часть текста ошибки:
В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(«:»;B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).
В результате получим:
Округление чисел текстовыми функциями в текстовой строке
Пример 3. Создать функцию для округления дробных чисел до ближайших целых значений с использованием формул для работы с текстом.
Форма для округления числовых значений имеет следующий вид:
В ячейке B3 введена следующая сложная формула:
=5,ЛЕВСИМВ(A2+1,НАЙТИ(«.»,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(«.»,A2)-1)),»Число не введено»)’ > 
- Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
- Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
- Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(«,»;B2)-1), то есть целой части числа +1 по правилам округления.
- Если <5, то будет возвращен результат работы функции ЛЕВСИМВ(B2;НАЙТИ(«,»;B2)-1)), то есть просто целая часть исходного числа.
Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.
Функция ЛЕВСИМВ в Excel и особенности ее использования
Функция ЛЕВСИМВ используется наряду с прочими функциями для работы с текстовыми строками в Excel (ПРАВСИМВ, СИМВОЛ, СЦЕПИТЬ, ДЛСТР и прочими) и имеет следующую синтаксическую запись:
- текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
- [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.
- Если необязательный аргумента [число_знаков] явно не указан, по умолчанию будет возвращен первый символ строки. Например, функция =ЛЕВСИМВ(«текст») вернет значение «т».
- Числовое значение, переданное в качестве необязательного аргумента, должно быть взято из диапазона неотрицательных чисел, то есть должно быть равным нулю или больше нуля. В противном случае будет возвращен код ошибки #ЗНАЧ!.
- Если в качестве аргумента [число_знаков] будет переданы текстовые данные, функция ЛЕВСИМВ вернет код ошибки #ЗНАЧ!.
- Если число, переданное в качестве необязательного аргумента, превышает количество знаков в текстовой строке, рассматриваемая функция вернет весь текст целиком.
- Excel выполняет автоматическое преобразование типов данных там, где это возможно. Например, рассмотрим некоторые возможные варианты записи функции ЛЕВСИМВ и результаты ее выполнения:
- ЛЕВСИМВ(«текст»;200%) вернет значение «те» (преобразование процентов в числовое значение);
- при аргументах: («текст»;ИСТИНА) вернет «т» (логическое ИСТИНА эквивалентно числовому значению 1);
- при аргументах: («текст»;ЛОЖЬ) вернет пустое значение «», поскольку логическое ЛОЖЬ эквивалентно числовому 0 (нуль);
- при аргументах: («текст»;15/7) вернет значение «те».
Примечание 2: в Excel также существует схожая по принципу работы функция ЛЕВБ, возвращающая определенное количество символов текстовой строки слева на основе определенного количества байтов. Если по умолчанию используется язык с поддержкой двухбайтовой кодировки, для получения первого символа строки требуется в качестве аргумента указать число 2, а для получения последующих второго, третьего и прочих символов необходимо указывать соответствующие числа, кратные двум. Для языков с однобайтовой кодировкой функции ЛЕВСИМВ и ЛЕВБ возвращают идентичные результаты.
Как работает функция левсимв в excel
Часто журналистам приходится работать с данными, представленными в виде, непригодном для анализа. Мы называем их «грязными» данными. В таблицах встречаются опечатки и ошибки, объединенные ячейки, продублированные значения, названия написаны по-разному – где-то с большой, где-то с маленькой буквы, где-то пропущены значения, эти пропуски могут кодироваться по-разному – где-то стоит прочерк, где-то 0 – все это признаки «грязных» данных. В этом выпуске – про функции, с помощью которых можно очистить данные, чтобы подготовить их к анализу.
- Для примера мы будем использовать несколько наборов данных, собранных в этой таблице: от деклараций депутатов Госдумы до реестра камер наблюдения за москвичами.
Объединенные ячейки
В некоторых датасетах объединенные ячейки могут мешать анализу данных: они не позволяют, например, выполнять фильтрацию. В наборе данных с декларациями депутатов Госдумы объединенные ячейки мешают быстро выяснить, кто задекларировал самый высокий годовой доход. Чтобы избавиться от этой проблемы, необходимо выделить столбец с объединенными ячейками, нажать на специальный символ в меню и выбрать опцию «Отменить объединение ячеек».
Фильтрация для очистки данных
Следующая проблема – лишние данные. Например, в наборе данных с годовыми доходами депутатов отображены доходы супруг и супругов, а также строка «ребенок». Чтобы оставить только депутатов и их доходы, отфильтруем данные по значению: уберем галочки с опций «Пустые», «супруг», «супруга», «ребенок». Останется таблица с ФИО депутата и его задекларированным доходом. Подробнее о том, как выполнить фильтрацию, мы рассказывали в первом выпуске курса.
«Найти и заменить»
Еще один признак «грязных» данных – значения из одной категории записаны в разных форматах. Например, в датасете с декларациями у одного депутата доходы записаны с запятой: «988498353,84» (и Google Таблицы воспринимают их как числа), а у другого с точкой: «4870390.49» (таблицы воспринимают их как текст). Такие данные невозможно анализировать автоматически, например, получить правильные результаты после сортировки. Самый легкий способ исправить эту проблему – выделить столбец с доходами, в меню «Изменить» выбрать «Найти и заменить» и заменить точки на запятые.
Преобразование регистра
Иногда нам требуется изменить регистр текста. Например, данные в декларациях депутатов записаны в таком виде: «СКОЧ Андрей Владимирович». Если бы нам понадобилось автоматически сопоставить эти данные с данными за другой год, в котором было бы верное написание, регистр этого текста помешал бы это сделать. В этом случае нам пригодится функция ПРОПНАЧ (PROPER), которая преобразует все первые буквы слов в заглавные. Подробнее о том, как работают функции, мы рассказывали в этом выпуске. Функция ПРОПИСН (UPPER) приведет все первые буквы слов в верхний регистр. Функция СТРОЧН (LOWER) – в нижний регистр.
Убрать переносы строк
Часто в ячейки закрадываются лишние переносы строк. Например, в датасете с декларациями фамилия написана на первой строке ячейки, а имя и отчество на второй. Чтобы избавиться от переносов строк, нужно ввести формулу =ПОДСТАВИТЬ(A2;СИМВОЛ(10);» «), в которой сперва указать адрес ячейки, из которой надо удалить переносы строк, затем символ, который мы хотим заменить (СИМВОЛ(10) – это переносы строк), а затем в кавычках символ, который мы хотим подставить вместо старого. В данном случае – пробел. В нашем случае мы получим запись ФИО депутатов в виде «Скоч Андрей Владимирович» без переносов строк.
Соединение нескольких таблиц с пересекающимися данными
Если бы мы хотели сравнить доходы депутатов за разные годы, нам понадобилось бы объединить два датасета в один. Сделать это можно с помощью функции ВПР(VLOOKUP). В нашем случае формула выглядит так: =ВПР(D7;A$1:B$447;2;0). В скобках на первом месте прописывается ячейка с искомым содержанием (D7 – имя депутата) из первой таблицы, затем диапазон поиска – вторая таблица, номер столбца, значение из которого мы хотим извлечь (2 столбец – с доходами депутата), а в конце 0, если нам требуется поиск точного соответствия. Тогда ячейка с формулой подставит рядом с именем депутата его доход за предшествующий год. После этого можно будет автоматически проводить сравнение данных за несколько лет. Если где-то отобразится ошибка #Н/Д (нет данных), значит искомое значение не было найдено во второй таблице. В данном случае это может означать, что депутат в тот год еще не вступил в должность и не подавал декларацию о доходах.
Соединение данных из разных ячеек в одну
Иногда перед выполнением анализа нам требуется соединить данные из нескольких ячеек в одну. Например, в датасете ФНС о физических лицах, являющихся руководителями нескольких юридических лиц, фамилия, имя и отчество одного человека размещены в разных ячейках, а не в одной. Чтобы исправить это, используем функцию =JOIN(» «;B2:D2). Внутри в кавычках прописываем разделитель, которым будут отделены данные в новой ячейке (в данном случае это пробел) и диапазон строк, которые надо соединить.
Разделение данных из одной ячейки на несколько
В других случаях нам наоборот надо разделить содержимое ячейки на несколько столбцов. Например, чтобы посчитать, на какой улице Москвы «в местах массового скопления людей» установлено больше всего камер наблюдения, из ячейки с адресом нам нужно вычленить только улицу. Сделать это можно с помощью функции =SPLIT(A2;»,»), которой надо задать ячейку с адресом и разделитель, которым разделены значения в адресе. В данном случае это запятая: «город Москва, Никольская улица, дом 25». В таком случае мы получим отдельные столбцы с городом, улицей и домом.
Удаление лишних пробелов
Часто работать с данными мешают лишние пробелы в ячейке. Например, в датасете с адресами камер наблюдения, в столбце с улицей после разделения ячейки с адресом образовался пробел перед названием улицы. Чтобы убрать из ячейки все пробелы кроме одиночных пробелов между словами, можно воспользоваться функцией СЖПРОБЕЛЫ(TRIM). Например: =TRIM(A2): после названия функции надо указать нужную ячейку.
Удаление дубликатов данных
В некоторых случаях в данных попадаются продублированные значения. Если бы в датасете с улицами с камерами наблюдения нам понадобилось бы оставить только уникальные улицы, необходимо было бы удалить дубли. Для этого надо выделить диапазон данных, выбрать в меню «Данные» опцию «Удалить дубликаты».
Преобразование дат
Чтобы извлечь из строки с датой только год – например, чтобы посчитать, в каком году происходило больше всего закупок какого-либо товара, воспользуйтесь функцией ГОД(YEAR). Чтобы извлечь только месяц – МЕСЯЦ (MONTH), день – ДЕНЬ(DAY).
Извлечение части содержимого ячейки
Часто для очистки данных требуется выделить несколько символов из ячейки в отдельную. В наборе данных по военным расходам разных стран, годы записаны в таком виде: «2015 [YR2015»]. Чтобы преобразовать это в запись вида «2015», можно воспользоваться функцией ЛЕВСИМВ(LEFT), которая возвращает несколько первых символов строки. В нашем случае: =ЛЕВСИМВ(C1;4). Сперва мы указываем адрес ячейки, а затем количество символов, которые хотим оставить. В итоге в ячейке останется только «2015». Чтобы извлечь символы с правой стороны, подойдет функция ПРАВСИМВ(RIGHT).
Перевод данных с другого языка
Журналисты часто работают с данными на разных языках. Чтобы перевести текст в таблице с одного языка на другой, нужно прописать в ячейке функцию GOOGLETRANSLATE. После нее указывается ячейка, которую надо перевести, язык оригинала и язык перевода. Например: =GOOGLETRANSLATE(A2;»en»;»ru»), чтобы перевести текст из ячейки A2 с английского на русский. После этого важно проверить переведенные данные на предмет ошибок: перевод может быть дословным.
Транспонирование таблицы
Иногда данные представлены в виде, неудобном для анализа: например, заголовки данных расположены в первом столбце, а не в первой строке таблицы. Если вы хотите поменять местами строки и столбцы в таблице, выделите пустую ячейку под старой таблицей, выберите функцию ТРАНСП(TRANSPOSE) и укажите ей диапазон старой таблицы.
Excel функция ЛЕВСИМВ (LEFT)
Microsoft Excel функция ЛЕВСИМВ позволяет извлекать подстроку из строки, начиная с самого левого символа.
Функция ЛЕВСИМВ — это встроенная в Excel функция, которая относится к категории строковых / текстовых функций.
Её можно использовать как функцию рабочего листа (WS) и функцию VBA в Excel.
Как функцию рабочего листа, функцию ЛЕВСИМВ можно ввести как часть формулы в ячейку рабочего листа.
В качестве функции VBA вы можете использовать функцию Left в коде макроса, который вводится через редактор Microsoft Visual Basic Editor.
Синтаксис
Синтаксис функции ЛЕВСИМВ в Microsoft Excel:
Аргументы или параметры
Возвращаемое значение
Функция ЛЕВСИМВ возвращает строковое / текстовое значение.
Применение
- Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000
Тип функции
- Функция рабочего листа (WS)
- Функция VBA
Пример (как функция рабочего листа)
Рассмотрим несколько примеров функции ЛЕВСИМВ чтобы понять, как использовать Excel функцию ЛЕВСИМВ в качестве функции рабочего листа в Microsoft Excel:
Hа основе электронной таблицы Excel выше, будут возвращены следующие примеры:
Функция ЛЕВСИМВ, ЛЕВБ (LEFT, LEFTB)
Функция ЛЕВБ возвращает первый символ или несколько первых символов текстовой строки на основе заданного числа байтов.
Функция ЛЕВБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой двухбайтовой кодировки. В противном случае функция ЛЕВБ работает так же, как функция ЛЕВСИМВ, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим двухбайтовую кодировку, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.