Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция mid в excel на русском

ПСТР, ПСТРБ (функции ПСТР, ПСТРБ)

В этой статье описаны синтаксис формулы и использование функций ПСТР и ПСТРБ в Microsoft Excel.

Описание

Функция ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Функция ПСТРБ возвращает определенное число знаков из текстовой строки, начиная с указанной позиции, на основе заданного числа байтов.

Эти функции могут быть доступны не на всех языках.

Функция ПСТР предназначена для языков с однобайтовой кодировкой, а ПСТРБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.

Функция ПСТР всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.

Функция ПСТРБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ПСТРБ считает каждый символ за один.

К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.

Синтаксис

Аргументы функций ПСТР и ПСТРБ описаны ниже.

Текст Обязательный. Текстовая строка, содержащая символы, которые требуется извлечь.

Начальная_позиция Обязательный. Позиция первого знака, извлекаемого из текста. Первый знак в тексте имеет начальную позицию 1 и так далее.

Если start_num больше, чем длина текста, функция ПСТР/ПСТРБ возвращает «» (пустая текст).

Если start_num меньше, чем длина текста, но start_num и num_chars превышает длину текста, функция ПСТР/ПСТРБ возвращает символы до конца текста.

Если start_num меньше 1, функция ПСТР/ПСТРБ возвращает значение #VALUE! значение ошибки #ЗНАЧ!.

Число_знаков Требуется для MID. Указывает, сколько знаков должна вернуть функция ПСТР.

Если значение «число_знаков» отрицательно, функция ПСТР возвращает значение ошибки #ЗНАЧ!.

Число_байтов Требуется для ПСТРБ. Указывает, сколько знаков должна вернуть функция ПСТРБ (в пересчете на байты).

Если значение «число_байтов» отрицательно, функция ПСТРБ возвращает значение ошибки #ЗНАЧ!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Возвращает пять знаков из строки в ячейке А2, начиная с первого знака.

Возвращает двадцать знаков из строки в ячейке А2, начиная с седьмого знака. Так как количество возвращаемых знаков (20) больше длины строки (10), возвращаются все знаки, начиная с седьмого. Пустые символы (пробелы) не добавляются в конец строки.

Так как начальная позиция больше, чем длина строки (10), возвращается пустая строка.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Трюк №78. Как в Excel строить мегаформулы

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

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

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

Предположим, есть длинный список имен людей, каждое из которых состоит из имени, отчества и фамилии, то есть по одному полному имени на ячейку. Задача состоит в том, чтобы написать формулу в соседнем столбце, которая будет извлекать только фамилию человека, который позвонил в отель Скандинавия и забронировал шикарный номер люкс.

Следовательно, надо найти начало фамилии, третьего слова в ячейке. Фактически, это означает поиск позиции второго пробела в ячейке. В Excel нет стандартной встроенной функции для автоматического поиска второго пробела в ячейке, но можно воспользоваться функцией НАЙТИ (FIND), применив ее так, чтобы получить желаемый результат.

Читать еще:  Comobject excel application

В ячейку А1 введите имя David John Hawley (или любое другое имя, состоящее из трех слов). В ячейку С1 введите следующую функцию: =FIND(» «;A1) , в русской версии Excel =НАЙТИ(» «;A1) . Функция НАЙТИ (FIND) ищет одну текстовую строку искомый_текст (find_text) внутри другой текстовой строки текст_для_поиска (within_text) и возвращает число, обозначающее начальную позицию искомый_текст (find_text), если отсчет начинается с первого символа текст_для_поиска (within_text). Синтаксис функции =FIND(find_text;within_text,;start_num) , в русской версии Excel =НАЙТИ(искомый_текст;текст_для_поиска;нач_позиция) .

Эта функция вернет начальную позицию первого символа пробела в ячейке А1, так как приказали ей найти «» (пробел) в ячейке А1. Если в ячейке находится текст David John Hawley, то функция вернет значение 6. Но вы ищете второй пробел, а не первый. Теперь нужно использовать число, возвращенное формулой в ячейке С1, как начальную позицию для другой функции НАЙТИ (FIND), чтобы найти второй символ пробела. Так, в ячейке С2 введите формулу =FIND(» «;A1;C1+1) , в русской версии Excel =НАЙТИ(» «;A1;C1+1) . Обратите внимание, что в этот раз вы передали функции НАЙТИ (FIND) третий аргумент, начальную позицию, найденную С1 (в данном примере равную 6), к которой прибавлена единица; она будет служить начальной точкой для функции НАЙТИ (FIND), начиная с которой она будет искать пробел. Вторая функция возвратит значение, обозначающее позицию второго символа пробела.

Вооружившись этими функциями, вы хотите, чтобы следующая функция выбрала все символы до конца строки текста. Воспользуйтесь функцией ПСТР (MID), которая предназначена для выделения диапазона символов из строки. В ячейке СЗ введите следующую формулу: =MID(A1;C2+1;256) , в русской версии Excel =ПСТР(А1;С2+1;256) . Функция ПСТР (MID) возвращает указанное количество символов текстовой строки, начиная с указанной позиции. Вот ее синтаксис: MID(text;start_num;num_chars) , в русской версии Excel ПСТР(текст;начальная_позиция;количество_символов) . Вы приказали функции ПСТР (MID) выбрать 256 символов из ячейки А1, начиная с первого символа после второго пробела в текстовой строке. Число 256 используется только чтобы гарантировать, что, независимо от длины фамилии (предполагая, что она все же короче 256 символов), вы получите фамилию человека полностью.

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

Щелкните ячейку С2, в строке формул выделите функцию и скопируйте ее целиком, кроме знака =, вот так: FIND(» «.A1;C1+1) , в русской версии Excel НАЙТИ(» «;A1;C1+1) . Нажмите клавишу Enter, чтобы покинуть ячейку, и вы окажетесь в ячейке С3. Выделив ячейку С3, в строке формул выделите ссылку на ячейку С2 и вставьте (сочетание клавиш Ctrl+V) функцию НАЙТИ (FIND), которую только что скопировали. Нажмите клавишу Enter. Функция в ячейке СЗ должна выглядеть так: =MID(A1;FIND(» «;А1;С1+1)+1;256) , в русской версии Excel =ПСТР(А1;НАЙТИ(» «;А1;С1+1)+1;256) .

Теперь необходимо заменить ссылку на ячейку С1 функцией, которая находится в ячейке С1. Выделите ячейку С1, выделите в строке формул формулу, опустив символ =, щелкните на кнопке Копировать (Сору), а затем дважды нажмите клавишу Enter, чтобы попасть обратно в ячейку С3. Находясь в ячейке С3, выделите С1 в строке формул и вставьте только что скопированную функцию НАЙТИ (FIND). Нажмите клавишу Enter.

Теперь осталось вырезать ячейку С3 и вставить ее в ячейку В1, а затем удалить формулы, оставшиеся в ячейках С1 и С2. Вы должны получить итоговую формулу, которая выглядит так: =MID(A1;FIND(» «;A1;FIND(» «;А1)+1)+1;256) , в русской версии Excel =ПСТР(А1;НАЙТИ(» «;А1;НАЙТИ(» «;A1)+1)+1;256) .

Следуя этой концепции, вы должны понять, как конструировать мегаформулы, используя разнообразные функции Excel. Сначала необходимо запланировать путь, по которому вы собираетесь идти к цели, а затем в индивидуальных ячейках получать необходимые результаты. Наконец, нужно заменить все ссылки на ячейки функциями, которые находятся в этих ячейках. Если получилось больше семи уровней вложенности, понадобится функция ДВССЫЛ (INDIRECT), описанная в разделе «Трюк №74. Отображение отрицательных значений времени».

Читать еще:  Какой браузер лучше для просмотра видео

Пример функция ПСТР для разделения текста на части в Excel

Функция ПСТР в Excel предназначена для выделения подстроки из строки текста, переданной в качестве первого аргумента, и возвращает требуемое количество символов начиная с заданной позиции.

Примеры использования функции ПСТР в Excel

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

Как разделить текст на несколько ячеек по столбцам в Excel?

Пример 1. В столбце таблицы содержатся даты, записанные в виде текстовых строк. Записать отдельно в соседних столбцах номер дня, месяца и год, выделенные из представленных дат.

Вид исходной таблицы данных:

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

  • A2:A10 – диапазон ячеек с текстовым представлением дат, из которых будут выделены номера дней;
  • 1 – номер начальной позиции символа извлекаемой подстроки (первый символ в исходной строке);
  • 2 – номер последней позиции символа извлекаемой подстроки.

Аналогичным способом выделим номера месяца и годы для заполнения соответствующих столбцов с учетом, что номер месяца начинается с 4-го символа в каждой строке, а год – с 7-го. Используем следующие формулы:

Вид заполненной таблицы данных:

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

Как вырезать часть текста ячейки в Excel?

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

Вид таблицы данных:

Для заполнения столбца «Наименование» используем следующую формулу:

Функция НАЙТИ возвращает номер позиции символа пробела « » в просматриваемой строке, который принимается в качестве аргумента число_знаков функции ПСТР. В результате расчетов получим:

Для заполнения столбца «Марка» используем следующую формулу массива:

Функция НАЙТИ возвращает позицию символа пробела. К полученному числу прибавляется единица для нахождения позиции первого символа названия марки продукта. Итоговое значение используется в качестве аргумента начальная_позиция функции ПСТР. Для упрощения, вместо поиска номера последней позиции (например, с помощью функции ДЛСТР) указано число 100, которое в данном примере гарантированно превышает количество знаков в изначальной строке.

В результате расчетов получим:

Как посчитать возраст по дате рождения в Excel?

Пример 3. В таблице содержатся данные о сотрудниках в столбцах ФИО и дата рождения. Создать столбец, в котором будет отображаться фамилия сотрудника и его возраст в формате «Иванов – 27».

Вид исходной таблицы:

Для возврата строки с фамилией и текущим возрастом используем следующую формулу:

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

Для соединения (конкатенации) полученных строк используются символы «&». В результате вычислений получим:

Особенности использования функции ПСТР в Excel

Функция имеет следующую синтаксическую запись:

  • текст – обязательный для заполнения аргумент, принимающий ссылку на ячейку с текстом или текстовую строку, заключенную в кавычки, из которой будет извлечена подстрока определенной длины начиная с указанной позиции первого символа;
  • начальная_позиция – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – длина строки, из которой требуется извлечь подстроку заданного размера. Начальная позиция символа в строке соответствует числу 1. Если данный аргумент принимает дробное число из диапазона допустимых значений, дробная часть будет усечена;
  • число_знаков – обязательный аргумент, принимающий значение из диапазона неотрицательных чисел, которое характеризует длину в символах возвращаемой подстроки. Если в качестве этого аргумента передано число 0 (нуль), функция ПСТР вернет пустую строку. Если аргумент задан числом, превышающим количество символов в строке, будет возвращена вся часть строки начиная с указанной вторым аргументом позиции. В дробных числах, используемых в качестве данного аргумента, дробная часть усекается.
Читать еще:  Функция рост в excel

Функция ПСТРБ имеет схожий синтаксис:

Она отличается единственным аргументом:

  • число_байтов – обязательный аргумент, принимающий целые числа из диапазона от 1 до N, где N – число байтов в исходной строке, характеризующий количество байт в возвращаемой подстроке.
  1. Функция ПСТР вернет пустую строку, если в качестве аргумента начальная_позиция было передано число, превышающее количество символов в исходной строке.
  2. Если в качестве аргумента начальная_позиция было передано значение 1, а аргумент число_знаков определен числом, которое равно или больше общему числу знаков в исходной строке, функция ПСТР вернет всю строку целиком.
  3. Если аргумент начальная_позиция был указан числом из диапазона отрицательных чисел или 0 (нулем), функция ПСТР вернет код ошибки #ЗНАЧ!.
  4. Если аргумент число_знаков задан отрицательным числом, результатом выполнения функции ПСТР будет код ошибки #ЗНАЧ!.

Функции VBA для работы с текстом

  • ASC ()— эта функция позволяет вернуть числовой код для переданного символа. Например, ASC( «D» ) вернет 68. Эту функцию удобно использовать для того, чтобы определить следующую или предыдущую букву. Обычно она используется вместе с функцией Chr(), которая производит обратную операцию — возвращает символ по переданному его числовому коду.Варианты этой функции — AscB() и AscW():
    • AscB () — возвращает только первый байт числового кода для символа.
    • AscW () — возвращает код для символа в кодировке Unicode
  • Chr () — возвращает символ по его числовому коду. Может использоваться в паре с функцией Asc(), но чаще всего её применяют, когда нужно вывести служебный символ (например кавычки — » ), т.к. кавычки просто так в VBA-коде не ввести(нужно ставить двойные). Я обычно именно эту функцию и использую.

    Dim sWord As String sWord = Chr(34) & «Слово в кавычках» & Chr(34)

    Есть варианты этой функции — ChrB() и ChrW(). Работают аналогично таким же вариантам для функции Asc().
    InStr () и InStrRev ()— одна из самых популярных функций. Позволяет обнаружить в теле строковой переменной символ или последовательность символов и вернуть их позицию. Если последовательность не обнаружена, то возвращается 0.

    Dim sStr As String sStr = «w» If InStr(1, «Hello, World!», sStr, vbTextCompare) > 0 Then MsgBox «Искомое слово присутствует!» Else MsgBox «Искомое слово отсутствует!» End If

    Разница функций в том, что InStr() ищет указанное слово от начала строки, а InStrRev() с конца строки
    Left (), Right (), Mid ()— возможность взять указанное вами количество символов из существующей строковой переменной слева, справа или из середины соответственно.

    Dim sStr As String sStr = «Hello, World!» MsgBox Mid(sStr, 1, 5)

    Dim sStr As String sStr = «Hello, World!» MsgBox Replace(sStr, «Hello», «Bay»)

    Dim sStr As String sStr = «Hello, World!» MsgBox StrConv(«Hello, World!», vbUpperCase)

    В качестве второго параметра параметра могут применяться константы:

    • vbUpperCase: Преобразует все текстовые символы в ВЕРХНИЙ РЕГИСТР
    • vbLowerCase: Преобразует все текстовые символы в нижний регистр
    • vbProperCase: Переводит первый символ каждого слова в Верхний Регистр
    • *vbWide: Преобразует символы строки из однобайтовых в двухбайтовые
    • *vbNarrow: Преобразует символы строки из двухбайтовых в однобайтовые
    • **vbKatakana: Преобразует символы Hiragana в символы Katakana
    • **vbHiragana: Преобразует символы Katakana в символы Hiragana
    • ***vbUnicode: Преобразует строку в Юникод с помощью кодовой страницы системы по умолчанию
    • ***vbFromUnicode: Преобразует строку из Юникод в кодовую страницу системы по умолчанию

    * применимо для локализацией Дальнего востока
    ** применимо только для Японии
    *** не поддерживается операционными системами под управлением Macintosh

  • StrReverse () — «перевернуть» строку, разместив ее символы в обратном порядке. Функция работает только начиная от Excel 2000 и выше. Пример использования функции, а так же иные методы переворачивания слова можно посмотреть в этой статье: Как перевернуть слово?
  • Tab () — еще одна функция, которая используется для форматирования вывода на консоль. Размножает символы табуляции в том количестве, в котором вы укажете. Если никакое количество не указано, просто вставляет символ табуляции. Для вставки символа табуляции в строковое значение можно также использовать константу vbTab.
  • String () — позволяет получить строку из указанного количества символов (которые опять-таки указываются Вами). Обычно используются для форматирования вывода совместно с функцией Len().
  • Ссылка на основную публикацию
    Adblock
    detector