Любая формула в microsoft excel начинается со следующего символа: Обзор формул — Служба поддержки Майкрософт

Содержание

Ввод формул

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

Виды функций

Логические функции можно разделить не только по типу, например «Тест», «Логика», «Статистический», но и по типу аргумента. Следовательно, существуют разные типы функций:

— без аргументов;

— с аргументом;

— с фиксированным количеством аргументов;

— с переменным количеством аргументов;

— с необязательными аргументами.

Еще немного о функциях

Кроме того, Excel различает функции внутри формул по наличию круглых скобок, скобки должны обязательно стоять после имени функции, без пробелов и других дополнительных символов. Особенно важно отметить, что круглые скобки рассматриваются как особый элемент формулы, или как часть математических выражений, или как определение параметров функции, или как определение приоритета выполнения операций. Поэтому скобки в формулах пишутся только попарно; любая открывающая скобка должна быть объединена с закрывающей. В крайнем случае, если круглых скобок много и вы допустили ошибку при наборе текста, Excel всегда исправит и предложит возможность удалить или добавить круглые скобки в формулу.

С функциями без аргументов все просто, при вводе в них пишется имя и сразу после имени пары открывающая и закрывающая скобки. С функциями, которые тоже имеют параметр, ничего особенного, в скобках указан только один параметр. Но в функциях, где есть более двух параметров, вам нужно отделить один параметр от другого. В русскоязычной версии Excel это делается с помощью символа «;» (точка с запятой). Параметры разделяются точкой с запятой не только в русском языке, но и в ряде других локализованных версий Excel. Если вы посмотрите примеры формул на англоязычных сайтах, вы заметите, что параметры разделены запятой. Такое поведение происходит из-за языкового стандарта системы. Кстати, символ-разделитель параметров используется не только в обычных формулах Excel, но и в других элементах или надстройках. Например, надстройка Power Pivot для Excel также использует региональный разделитель при вводе формул DAX.

Вводим формулу

На листе перейдите в пустую ячейку и начните вводить формулу:

= 1 + 2

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

Еще одна небольшая заметка. Рекомендуется завершить ввод формулы, нажав клавишу Enter. Несмотря на то, что Excel похож на болтливую птицу, он выделяется своим интеллектом и изобретательностью и становится совершенно другим, когда формула оказывается в процессе редактирования. Это означает, что он может автоматически запрашивать подтверждение вставки формулы и выход из модификации, даже нажав кнопку мыши, однако лучше подтвердить окончание вставки формулы, нажав клавишу «Ввод» или «Enter» на панели ввода формулы.

Лексический анализ при вводе формул

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

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

Автозаполнение при вводе

По мере ввода функций в формулы Excel отображает предложения с параметрами, которые наиболее точно соответствуют уже введенным символам. Чаще всего при обучении вставке функций автозаполнение отображается в формулах на примере функции «ЕДИНИЦА». Дело в том, что функция уникальна по параметрам и это хорошо демонстрирует все возможные варианты заполнения функции. Итак, приступим .. как всегда, любая формула начинается со знака равенства. Введите знак равенства и в русской раскладке нажмите клавишу «А» (при вводе формул Excel не учитывает регистр символов, при нажатии «А» или «а» результат будет тот же). После ввода первой буквы появится всплывающая подсказка со списком функций, начинающимся с буквы «А».

Обычно функция «АГРЕГАТ» будет первой в списке функций Excel, начинающихся с буквы «А». Также сразу видно, что нужная функция сразу выделяется в списке. Что ж, для полного счастья, если вы забыли, что делает функция, рядом отображается подсказка о выполняемой функцией операции. Функция в списке выбрана, и нажатие клавиши «Tab» автоматически вставит ее в формулу.

Вы не хотите нажимать «Tab», дважды щелкните имя функции в списке, щелкните левой кнопкой мыши, и функция также будет вставлена ​​в формулу. При желании вы можете просто ввести полное имя функции с клавиатуры. В этом случае, если вы продолжите печатать, Excel автоматически изменит список функций, соответствующий уже введенным символам. Если продолжить ввод с клавиатуры, можно взять за правило следования предложенному списку. Таким образом, для нашей функции ввод следующей буквы «G» оставит функцию в списке, поскольку из всех функций Excel только «UNIT» начинается с букв «AG». Если бы вместо «G» мы ввели «M», мы бы оставили в списке значения «AMORUV» и «AMORUM». С другой стороны, если вы продолжаете вводить с клавиатуры, а список функций больше не появляется, это только одно: в Excel такой функции нет, и пора остановиться и проверить, что вы ввели.

Вернемся к функции, введем имя, и на втором шаге снова появится всплывающее окно.

Это лишь специфика функции, второй шаг с окном подсказок. Чтобы не уходить далеко, выберем значение «1-mean». Затем мы вставляем точку с запятой и переходим на следующий уровень поиска.

Следующие параметры

Даже не лучше, и все же выбор параметра функции. Кстати, о подборе параметров. В раскрывающемся списке легко перемещаться, проверяя строку выбора с помощью кнопок «Вверх» и «Вниз» на клавиатуре. Однако не используйте клавишу «Enter» для подтверждения вашего выбора. «Enter» подтверждает, что вы закончили ввод формулы, и, поскольку функция была введена не полностью, вероятно, появится сообщение об ошибке. При вводе функций используйте клавишу «Tab» при использовании клавиатуры или дважды щелкните левой кнопкой мыши по желаемому элементу в раскрывающемся списке. С другой стороны, как и в нашем примере, в первом и втором параметрах раскрывающийся список содержит числовые значения, поэтому мы можем просто выбрать значение, введя необходимое число. Для второго параметра мы выберем значение 2, какой метод использовать для выбора — дело привычки. После ввода параметра незабываем поставить точку с запятой.

Начиная со следующего параметра функция AGGREGATE принимает ссылки на диапазон. Функция принадлежит к группе функций с переменным количеством аргументов, и необходимо указать три параметра. То есть нам нужно указать диапазон значений.

Самый простой способ указать диапазоны — выделить нужные ячейки мышью. Щелкните левой кнопкой мыши первую ячейку диапазона и перетащите мышь, удерживая левую кнопку нажатой, на последней ячейке диапазона. Не забудьте вставить закрывающую скобку в конце, помните, что количество открытых скобок должно быть равно количеству закрытых, или наоборот. Не беда, главное, чтобы скобки были попарными. Нажав клавишу «Enter», мы подтвердим окончание ввода формулы и в результате увидим значение «30».

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

«TestDiap» — это именованный диапазон, который появляется в раскрывающемся списке, когда вы вводите значение «Te» в параметре».

Анализ текста регулярными выражениями (RegExp) в Excel

5594
11.02.2018
Скачать пример


Одной из самых трудоемких и неприятных задач при работе с текстом в Excel является парсинг — разбор буквенно-цифровой «каши» на составляющие и извлечение из нее нужных нам фрагментов. Например:

  • извлечение почтового индекса из адреса (хорошо, если индекс всегда в начале, а если нет?)

  • нахождение номера и даты счета из описания платежа в банковской выписке

  • извлечение ИНН из разношерстных описаний компаний в списке контрагентов

  • поиск номера автомобиля или артикула товара в описании и т.д.


Обычно во подобных случаях, после получасового муторного ковыряния в тексте вручную, в голову начинают приходить мысли как-то автоматизировать этот процесс (особенно если данных много). Решений тут несколько и с разной степенью сложности-эффективности:

  • Использовать встроенные текстовые функции Excel для поиска-нарезки-склейки текста: ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), СЦЕПИТЬ (CONCATENATE) и ее аналоги, ОБЪЕДИНИТЬ (JOINTEXT), СОВПАД (EXACT) и т.д. Этот способ хорош, если в тексте есть четкая логика (например, индекс всегда в начале адреса). В противном случае формулы существенно усложняются и, порой, дело доходит даже до формул массива, что сильно тормозит на больших таблицах.

  • Использование оператора проверки текстового подобия Like из Visual Basic, обернутого в пользовательскую макро-функцию. Это позволяет реализовать более гибкий поиск с использованием символов подстановки (*,#,? и т.д.) К сожалению, этот инструмент не умеет извлекать нужную подстроку из текста — только проверять, содержится ли она в нем.


Кроме вышеперечисленного, есть еще один подход, очень известный в узких кругах профессиональных программистов, веб-разработчиков и прочих технарей — это регулярные выражения (Regular Expressions = RegExp = «регэкспы» = «регулярки»). Упрощенно говоря, RegExp — это язык, где с помощью специальных символов и правил производится поиск нужных подстрок в тексте, их извлечение или замена на другой текст. Регулярные выражения — это очень мощный и красивый инструмент, на порядок превосходящий по возможностям все остальные способы работы с текстом. Многие языки программирования (C#, PHP, Perl, JavaScript…) и текстовые редакторы (Word, Notepad++…) поддерживают регулярные выражения.


Microsoft Excel, к сожалению, не имеет поддержки RegExp по-умолчанию «из коробки», но это легко исправить с помощью VBA. Откройте редактор Visual Basic с вкладки Разработчик (Developer) или сочетанием клавиш Alt+F11. Затем вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такой макрофункции: 

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript. RegExp")
    regex.Pattern = Pattern
    regex.Global = True
    If regex.Test(Text) Then
        Set matches = regex.Execute(Text)
        RegExpExtract = matches.Item(Item - 1)
        Exit Function
    End If
ErrHandl:
    RegExpExtract = CVErr(xlErrValue)
End Function


Теперь можно закрыть редактор Visual Basic и, вернувшись в Excel, опробовать нашу новую функцию. Синтаксис у нее следующий:


=RegExpExtract( Txt ; Pattern ; Item )


где

  • Txt — ячейка с текстом, который мы проверяем и из которого хотим извлечь нужную нам подстроку

  • Pattern — маска (шаблон) для поиска подстроки

  • Item — порядковый номер подстроки, которую надо извлечь, если их несколько (если не указан, то выводится первое вхождение)


Самое интересное тут, конечно, это Pattern — строка-шаблон из спецсимволов «на языке» RegExp, которая и задает, что именно и где мы хотим найти. Вот самые основные из них — для начала:












 Паттерн
 Описание

 .
Самое простое — это точка. Она обозначает любой символ в шаблоне на указанной позиции.

 \s
Любой символ, выглядящий как пробел (пробел, табуляция или перенос строки).

 \S
Анти-вариант предыдущего шаблона, т.е. любой НЕпробельный символ.

 \d
Любая цифра

 \D
Анти-вариант предыдущего, т.е. любая НЕ цифра

 \w
Любой символ латиницы (A-Z), цифра или знак подчеркивания

 \W
Анти-вариант предыдущего, т.
Начало строки

 $
Конец строки

 \b
Край слова


Если мы ищем определенное количество символов, например, шестизначный почтовый индекс или все трехбуквенные коды товаров, то на помощь нам приходят квантификаторы или кванторы — специальные выражения, задающие количество искомых знаков. Квантификаторы применяются к тому символу, что стоит перед ним:







  Квантор
 Описание

 ?
Ноль или одно вхождение. Например .? будет означать один любой символ или его отсутствие.

 +
Одно или более вхождений. Например \d+ означает любое количество цифр (т.е. любое число от 0 до бесконечности).

 *
Ноль или более вхождений, т.е. любое количество. Так \s* означает любое количество пробелов или их отсутствие.

{число} или
{число1,число2}
Если нужно задать строго определенное количество вхождений, то оно задается в фигурных скобках. Например \d{6} означает строго шесть цифр, а шаблон \s{2,5} — от двух до пяти пробелов


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

Извлекаем числа из текста


Для начала разберем простой случай — нужно извлечь из буквенно-цифровой каши первое число, например мощность источников бесперебойного питания из прайс-листа:


Логика работы регулярного выражения тут простая: \d — означает любую цифру, а квантор + говорит о том, что их количество должно быть одна или больше. Двойной минус перед функцией нужен, чтобы «на лету» преобразовать извлеченные символы в полноценное число из числа-как-текст.

Почтовый индекс


На первый взгляд, тут все просто — ищем ровно шесть цифр подряд. Используем спецсимвол \d для цифры и квантор {6} для количества знаков:


Однако, возможна ситуация, когда левее индекса в строке стоит еще один большой набор цифр подряд (номер телефона, ИНН, банковский счет и т.д.) Тогда наша регулярка выдернет из нее первых 6 цифр, т.е. сработает некорректно:



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

Телефон


Проблема с нахождением телефонного номера среди текста состоит в том, что существует очень много вариантов записи номеров — с дефисами и без, через пробелы, с кодом региона в скобках или без и т. д. Поэтому, на мой взгляд, проще сначала вычистить из исходного текста все эти символы с помощью нескольких вложенных друг в друга функций ПОДСТАВИТЬ (SUBSTITUTE), чтобы он склеился в единое целое, а потом уже примитивной регуляркой \d{11} вытаскивать 11 цифр подряд:


ИНН


Тут чуть сложнее, т.к. ИНН (в России) бывает 10-значный (у юрлиц) или 12-значный (у физлиц). Если не придираться особо, то вполне можно удовлетвориться регуляркой \d{10,12}, но она, строго говоря, будет вытаскивать все числа от 10 до 12 знаков, т.е. и ошибочно введенные 11-значные. Правильнее будет использовать два шаблона, связанных логическим ИЛИ оператором | (вертикальная черта):



Обратите внимание, что в запросе мы сначала ищем 12-разрядные, и только потом 10-разрядные числа. Если же записать нашу регулярку наоборот, то она будет вытаскивать для всех, даже длинных 12-разрядных ИНН, только первые 10 символов. То есть после срабатывания первого условия дальнейшая проверка уже не производится:


Это принципиальное отличие оператора | от стандартной экселевской логической функции ИЛИ (OR), где от перестановки аргументов результат не меняется.

Артикулы товаров


Во многих компаниях товарам и услугам присваиваются уникальные идентификаторы — артикулы, SAP-коды, SKU и т.д. Если в их обозначениях есть логика, то их можно легко вытаскивать из любого текста с помощью регулярных выражений. Например, если мы знаем, что наши артикулы всегда состоят из трех заглавных английских букв, дефиса и последующего трехразрядного числа, то:


Логика работы шаблона тут проста. [A-Z] — означает любые заглавные буквы латиницы. Следующий за ним квантор {3} говорит о том, что нам важно, чтобы таких букв было именно три. После дефиса мы ждем три цифровых разряда, поэтому добавляем на конце \d{3}

Денежные суммы


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



Паттерн \d с квантором + ищет любое число до дефиса, а \d{2} будет искать копейки (два разряда) после.


Если нужно вытащить не цены, а НДС, то можно воспользоваться третьим необязательным аргументом нашей функции RegExpExtract, задающим порядковый номер извлекаемого элемента. И, само-собой, можно заменить функцией ПОДСТАВИТЬ (SUBSTITUTE) в результатах дефис на стандартный десятичный разделитель и добавить двойной минус в начале, чтобы Excel интерпретировал найденный НДС как нормальное число:

Автомобильные номера


Если не брать спецтранспорт, прицепы и прочие мотоциклы, то стандартный российский автомобильный номер разбирается по принципу «буква — три цифры — две буквы — код региона». Причем код региона может быть 2- или 3-значным, а в качестве букв применяются только те, что похожи внешне на латиницу. Таким образом, для извлечения номеров из текста нам поможет следующая регулярка:


Время


Для извлечения времени в формате ЧЧ:ММ подойдет такое регулярное выражение:



После двоеточия фрагмент [0-5]\d, как легко сообразить, задает любое число в интервале 00-59. Перед двоеточием в скобках работают два шаблона, разделенных логическим ИЛИ (вертикальной чертой):

  • [0-1]\d — любое число в интервале 00-19

  • 2[0-3] — любое число в интервале 20-23


К полученному результату можно применить дополнительно еще и стандартную Excel’евскую функцию ВРЕМЯ (TIME), чтобы преобразовать его в понятный программе и пригодный для дальнейших расчетов формат времени.

Проверка пароля


Предположим, что нам надо проверить список придуманных пользователями паролей на корректность. По нашим правилам, в паролях могут быть только английские буквы (строчные или прописные) и цифры. ) и концом ($) в нашем тексте находились только символы из заданного в квадратных скобках набора. Если нужно проверить еще и длину пароля (например, не меньше 6 символов), то квантор + можно заменить на интервал «шесть и более» в виде {6,}:


Город из адреса


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


Давайте разберем этот шаблон поподробнее.


Если вы прочитали текст выше, то уже поняли, что некоторые символы в регулярных выражениях (точки, звездочки, знаки доллара и т.д.) несут особый смысл. Если же нужно искать сами эти символы, то перед ними ставится обратная косая черта (иногда это называют экранированием). Поэтому при поиске фрагмента «г.» мы должны написать в регулярке г\. если ищем плюсик, то \+ и т.д.


Следующих два символа в нашем шаблоне — точка и звездочка-квантор — обозначают любое количество любых символов, т. е. любое название города.


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



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


Имя файла из полного пути


Еще одна весьма распространенная ситуация — вытащить имя файла из полного пути. Тут поможет простая регулярка вида:


Тут фишка в том, что поиск, по сути, происходит в обратном направлении — от конца к началу, т. к. в конце нашего шаблона стоит $, и мы ищем все, что перед ним до первого справа обратного слэша. Бэкслэш заэкранирован, как и точка в предыдущем примере.

P.S.


«Под занавес» хочу уточнить, что все вышеописанное — это малая часть из всех возможностей, которые предоставляют регулярные выражения. Спецсимволов и правил их использования очень много и на эту тему написаны целые книги (рекомендую для начала хотя бы эту). В некотором смысле, написание регулярных выражений — это почти искусство. Почти всегда придуманную регулярку можно улучшить или дополнить, сделав ее более изящной или способным работать с более широким диапазоном вариантов входных данных.


Для анализа и разбора чужих регулярок или отладки своих собственных есть несколько удобных онлайн-сервисов: RegEx101, RegExr и др.


К сожалению, не все возможности классических регулярных выражений поддерживаются в VBA (например, обратный поиск или POSIX-классы) и умеют работать с кириллицей, но и того, что есть, думаю, хватит на первое время, чтобы вас порадовать.


Если же вы не новичок в теме, и вам есть чем поделиться — оставляйте полезные при работе в Excel регулярки в комментариях ниже. Один ум хорошо, а два сапога — пара!

Ссылки по теме

  • Замена и зачистка текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

  • Поиск и подсветка символов латиницы в русском тексте

  • Поиск ближайшего похожего текста (Иванов = Ивонов = Иваноф и т.д.)

Excel ЛЕВАЯ функция с примерами формул

В этом учебном пособии показано, как использовать ЛЕВУЮ функцию в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу «ЛЕВАЯ» возвращать число и более.

Среди многих различных функций, которые Microsoft Excel предоставляет для работы с текстовыми данными, ЛЕВАЯ является одной из наиболее широко используемых. Как следует из названия, эта функция позволяет извлекать определенное количество символов, начиная с левой стороны текстовой строки. Однако Excel LEFT способен на гораздо большее, чем его чистая сущность. В этом уроке вы найдете пару основных левых формул для понимания синтаксиса, а затем я покажу вам несколько способов, с помощью которых вы можете использовать функцию ЛЕВАЯ в Excel далеко за пределами ее основного использования.

Функция Excel ВЛЕВО — синтаксис

Функция ВЛЕВО в Excel возвращает указанное количество символов (подстроки) от начала строки.

Синтаксис функции ВЛЕВО следующий:

ЛЕВЫЙ(текст, [число_знаков])

Где:

  • Текст (обязательно) — это текстовая строка, из которой вы хотите извлечь подстроку. Обычно он предоставляется как ссылка на ячейку, содержащую текст.
  • Число_знаков (необязательно) — количество символов для извлечения, начиная с левой стороны строки.
    • Если num_chars опущено, по умолчанию используется значение 1, что означает, что формула Left будет возвращать 1 символ.
    • Если num_chars больше, чем общая длина text , формула Left вернет весь text .

Например, чтобы извлечь первые 3 символа из текста в ячейке A2, используйте следующую формулу:

=ЛЕВО(A2, 3)

На следующем снимке экрана показан результат:

Важное примечание! LEFT относится к категории текстовых функций, поэтому результатом формулы Left всегда является текстовая строка , даже если исходное значение, из которого вы извлекаете символы, является числом. Если вы работаете с числовым набором данных и хотите, чтобы функция LEFT возвращала число, используйте ее в сочетании с функцией VALUE, как показано в этом примере.

Как использовать ЛЕВУЮ функцию в Excel — примеры формул

Помимо извлечения текста слева от строки, что еще может делать функция LEFT? В следующих примерах показано, как можно использовать ЛЕВЫЙ в сочетании с другими функциями Excel для решения более сложных задач.

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

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

Если имя и фамилия разделены пробелом, проблема сводится к определению положения пробела в строке, что легко сделать с помощью функции ПОИСК или НАЙТИ.

Предположим, что полное имя находится в ячейке A2, позиция пробела возвращается по этой простой формуле: ПОИСК(» «,A2)). А теперь вы вставляете эту формулу в аргумент num_chars функции LEFT:

=ВЛЕВО(A2, ПОИСК(" ", A2))

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

=ЛЕВОЕ(A2,ПОИСК(" ",A2)-1)

Таким же образом вы можете извлечь код страны из столбца телефонных номеров. Единственное отличие состоит в том, что вы используете функцию поиска, чтобы узнать положение первого дефиса («-«), а не пробела:

=ВЛЕВО(A2, ПОИСК("-", A2)-1)

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

ЛЕВЫЙ( строка , ПОИСК( символа , строка ) — 1)

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

Вы уже знаете, как использовать функцию Excel НАЛЕВО для получения подстроки из начала текстовой строки. Но иногда вы можете захотеть сделать что-то другое — удалить определенное количество символов с конца строки и перетащить оставшуюся часть строки в другую ячейку. Для этого используйте функцию LEFT в сочетании с LEN, например:

ЛЕВЫЙ( строка, ДЛСТР( строка ) — число_символов_для_удаления )

Формула работает по следующей логике: функция ДЛСТР получает общее количество символов в строке, затем вы вычитаете количество ненужных символов из общей длины, а функция ЛЕВЫЙ возвращает оставшиеся символы.

Например, чтобы удалить последние 7 символов из текста в формате A2, используйте следующую формулу:

=ЛЕВО(A2,ДЛСТР(A2)-7)

Как показано на скриншоте ниже, формула успешно отрезает постфикс « — ToDo» (4 буквы, дефис и 2 пробела) из текстовых строк в столбце А.

Как заставить ЛЕВУЮ функцию возвращать число

Как вы уже знаете, функция Excel ВЛЕВО всегда возвращает текст, даже если вы извлекаете из числа несколько первых цифр. Для вас это означает, что вы не сможете использовать результаты формул Left в вычислениях или в других функциях Excel, которые работают с числами.

Итак, как же заставить Excel LEFT выводить число, а не текстовую строку? Просто поместив его в функцию ЗНАЧ, предназначенную для преобразования строки, представляющей число, в число, например: ЗНАЧЕНИЕ(ЛЕВО())

Например, чтобы извлечь первые 2 символа из строки в A2 и преобразовать вывод в числа, используйте следующую формулу:

= ЗНАЧЕНИЕ (ЛЕВО (A2,2))

Результат будет выглядеть примерно так:

Как вы можете видеть на снимке экрана выше, числа в столбце B, полученные с помощью формулы Value Left, выделены в ячейках по правому краю, в отличие от текста, выровненного по левому краю в столбце A. Поскольку Excel распознает выходные данные как числа, вы можете чтобы суммировать и усреднить эти значения, найти минимальное и максимальное значение и выполнить любые другие вычисления.

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

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

  • Разделить строку запятой, двоеточием, косой чертой, тире или другим разделителем
  • Как разделить строку по разрыву строки
  • Как преобразовать 8-число в дату
  • Подсчитать количество символов до или после заданного символа
  • Формула массива для выполнения различных вычислений над числами в разных диапазонах

Excel ЛЕВАЯ функция не работает — причины и решения

Если функция Excel ВЛЕВО не работает должным образом на ваших листах, это, скорее всего, вызвано одной из следующих причин.

1. Аргумент Num_chars меньше нуля

Если ваша формула Excel Left возвращает #VALUE! ошибка, первое, что вы должны проверить, это значение в аргументе num_chars . Если это отрицательное число, просто уберите минус, и ошибка исчезнет (конечно, маловероятно, что кто-то намеренно поставит туда отрицательное число, но ошибаться свойственно человеку 🙂

Чаще всего ошибка ЗНАЧ возникает, когда аргумент num_chars представлен другой функцией. В этом случае скопируйте эту функцию в другую ячейку или выберите ее в строке формул и нажмите F9, чтобы увидеть, чему она соответствует. Если значение меньше 0, то проверьте функцию на наличие ошибок.

Чтобы лучше проиллюстрировать это, давайте возьмем формулу Left, которую мы использовали в первом примере для извлечения телефонных кодов страны: LEFT(A2, SEARCH(«-«, A2)-1). Как вы помните, функция поиска в 9Аргумент 0003 num_chars вычисляет позицию первого дефиса в исходной строке, из которой мы вычитаем 1, чтобы удалить дефис из окончательного результата. Если я случайно заменю -1, скажем, на -11, формула выдаст ошибку #ЗНАЧ, потому что аргумент num_chars равен отрицательным числам:

2. Начальные пробелы в исходном тексте

Если ваша формула Excel Left не работает без видимой причины, проверьте исходные значения на наличие начальных пробелов. Если вы скопировали свои данные из Интернета или экспортировали из другого внешнего источника, многие такие пробелы могут скрываться незамеченными перед текстовыми записями, и вы никогда не узнаете, что они там, пока что-то не пойдет не так. Следующее изображение иллюстрирует проблему:

Чтобы избавиться от начальных пробелов на ваших листах, используйте функцию Excel TRIM или надстройку Text Toolkit.

3. Excel ЛЕВЫЙ не работает с датами

Если вы попытаетесь использовать функцию Excel LEFT для получения отдельной части даты (например, дня, месяца или года), в большинстве случаев вы получите только первые несколько цифр числа, представляющего эту дату. Дело в том, что в Microsoft Excel все даты хранятся в виде целых чисел, представляющих собой количество дней, прошедших с 1 января 19 года.00, который хранится как номер 1 (дополнительную информацию см. в формате даты Excel). То, что вы видите в ячейке, — это просто визуальное представление даты, и его отображение можно легко изменить, применив другой формат даты.

Например, если у вас есть дата 11 января 2017 года в ячейке A1, и вы пытаетесь извлечь день с помощью формулы ЛЕВЫЙ (A1,2), результатом будет 42, что является первыми двумя цифрами числа 42746. который представляет 11 января 2017 года во внутренней системе Excel.

Чтобы извлечь определенную часть даты, используйте одну из следующих функций: ДЕНЬ, МЕСЯЦ или ГОД.

Если ваши даты введены в виде текстовых строк, то ЛЕВАЯ функция будет работать без сбоев, как показано в правой части скриншота:

Вот как вы используете функцию ВЛЕВО в Excel. Я благодарю вас за чтение и надеюсь увидеть вас снова на следующей неделе.

Вас также может заинтересовать

Как извлечь текст до или после символа

Узнайте, как извлекать текст до или после символа в Excel с помощью функций НАЙТИ, ДЛСТР, ВЛЕВО и ПРАВО.

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

Вот шаги для извлечения текста перед символом:

  1. Примените формулу =НАЙТИ(“, A1)-1, чтобы вернуть правильную длину без разделителя.
  2. Извлечь текст перед заданной позицией, используя функцию ВЛЕВО.
  3. Формула: =ЛЕВЫЙ(A1, НАЙТИ(“,”, A1)-1)

Объяснение

Оцените формулу изнутри. Во-первых, вам нужно использовать функцию НАЙТИ , чтобы найти положение разделителя. В примере ячейка C3 содержит текстовую строку. Чтобы найти позицию запятой в тексте, используйте следующую формулу:

 =НАЙТИ(",",C3) = 6

Поскольку вам не нужно извлекать разделитель, вычтите общую длину с одним символом:

 =НАЙТИ(",",C3)-1 = 5

Функция ВЛЕВО использует два аргумента для возврата указанного количества символов от начала текстовой строки.

 =ВЛЕВО(C3,НАЙТИ(",",C3)-1) = ВЛЕВО(C3,5)

Вот шаги для извлечения текста после символа:

  1. Примените =НАЙТИ(“, , формула A1)-1 для возврата правильной длины без разделителя
  2. Получить общую длину текста с помощью функции ДЛСТР
  3. Используйте функцию ВПРАВО, чтобы вернуть символы после запятой
  4. Формула: =ПРАВО(A1,ДЛСТР(A1)-НАЙТИ(«»,A1)-1)

Объяснение

Некоторый текст и описательная картинка.

Если вы являетесь подписчиком Microsoft365, вы можете использовать новейшие функции обработки строк, такие как TEXTAFTER и TEXTBEFORE.

 =TEXTBEFORE(текст,разделитель,instance_num,match_mode,match_end,if_not_found)
=ТЕКСТПОСЛЕ(текст,разделитель,instance_num,match_mode,match_end,if_not_found)

Огромным преимуществом использования этих функций является аргумент « instance_num ». Вы можете легко извлечь 2-й, 3-й или n-й экземпляр.

Пользовательские функции

Для поддержки пользователей, отличных от Microsoft365, приведен эквивалентный код ( определяемая пользователем функция ) для извлечения текста перед символом. Чтобы реализовать новую функцию, нажмите Alt + F11, чтобы открыть редактор VBA. Создайте новый модуль, вставьте код и сохраните книгу в формате xlsm.

Функция TEXTBEFORE_365 (текст как строка, разделитель как строка, необязательный номер_экземпляра, As Long = 1, необязательный режим match_mode, As Long = 0, необязательный match_end, As Long = 0, необязательный if_not_found, As Variant) As Variant
 Тусклый обр как вариант
 Тусклый счет до тех пор, пока
 Если Len(текст) = 0 Или Len(разделитель) = 0 Тогда
 TEXTBEFORE_365 = if_not_found
 Выход из функции
 Конец, если
 Если match_mode = 1 Тогда
 текст = UCase(текст)
 разделитель = UCase(разделитель)
 Конец, если
 arr = Разделить (текст, разделитель)
 количество = UBound (приб.

Читайте также: