Remkomplekty.ru

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

Функция mid в excel

Функция ПСТР в Excel

Доброго времени суток друзья!

Темой этой статьи станет еще одна полезная текстовая функция ПСТР в Excel. Могу сказать, что это не самая распространённая функция в Excel, просто в силу незнания возможностей ее использования, а совсем не то что она плохая. Основная проблема в том, что основная масса пользователей работает с цифрами и совсем забывают, что в таблицах также существуют списки, которые имеют текст, с которым наиболее удобно работать именно текстовыми функциями. А одна из самых полезных текстовых функций – это функция ПСТР.

Чем же так полезна эта функция, и как максимально эффективно ее использовать в своей работе. На эти и другие вопросы я и попытаюсь ответить, а также рассказать о полезных комбинациях, при которых раскрывается потенциал функции ПСТР в Excel.

Главная особенность функции ПСТР в Excel, это то, что с указанной в функции позиции, она возвращает указанное в ней количество знаков с любой текстовой строки. А это значит, что, функция ПСТР может вытянуть с текстовой строки любое слово, словосочетание, нужный код, номенклатурный номер, название компании или торговой марки и многое другое.

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

=ПСТР(текст; начальная позиция; количество знаков), где:

  • текст – это текстовое значение, текст или ссылка на текстовую ячейку, в которой будут производиться манипуляции;
  • начальная позиция – это указание порядкового номера текстового символа, с которого будет производиться возврат значения;
  • количество знаков – указывается целое натуральное число, которое соответствует количеству символов и будет возвращено с указанной вами в критерии «начальная позиция».

Теперь рассмотрим варианты применения функции ПСТР в Excel. И начнем с самого просто варианта, где вам просто нужно вытянуть определенное слово, например, с ячейки со значением «Телевизор SONY» нам нужно получить название торговой марки «SONY» и для этого формула должна быть следующего вида:

=ПСТР(A2;11;4) Это конечно самый примитивный пример, но он служит для понимания работы функции ПСТР, а вот для большей эффективности нужно использовать комбинации функций, таких как НАЙТИ, ДЛСТР и т.д.

Рассмотрим еще один пример с применением функции НАЙТИ. Есть, к примеру, название товара в ячейке, с названием торговой марки, а вот нам надо вытянуть эту торговую марку для дальнейшей группировки значений. Название «Клавиатура Logitech» и формула для изъятия названия торговой марки будет следующая:

=ПСТР(A3;НАЙТИ(“ ”;A3)+1;200) В формуле, функция НАЙТИ ищет, где находится пробел « » и после нахождение передает эти данные функции ПСТР, которая вернет текст от найденного знака. Корректировочное дополнение «+1» позволит начать получать данные со следующего знака от искомого, так как он входит в диапазон подсчёта.

Я надеюсь, что функция ПСТР в Excel стала вам более понятной, и вы будете использовать ее в своей работе. С другими функциями вы можете познакомится в «Справочнике функций». Если статья вам помогла, я буду, благодарен вашему нажатию на социальные кнопочки! В случае если у вас есть замечания или дополнения пишите комментарии!

До встречи в новых статьях!

«Нет ничего более деморализующего, чем маленький, но постоянный заработок.
»
Э. Уилсон

Примеры использования функций VBA

Удаление ненужных символов

Зачастую строки в программе содержат ненужные символы пробелов в конце или в начале строки, которые необходимо удалить, т.к. посторонние начальные или конечные пробелы в строке могут вызвать «неполадки» в работе программы.

В VBA имеются три функции, предназначенные для удаления начальных и конечных пробелов из строки: LTrim, RTrim, Trim. Следует иметь ввиду, что эти функции на самом деле не изменяют исходную строку, а возвращают копию строки с удаленными лишними пробелами.

Определение длины строки

Длину строки, как правило, надо знать при форматировании сообщений для пользователя или при форматировании строковых данных, вводимых процедурой в рабочий лист Excel или документ Word. VBA для этих целей использует функцию Len. В приведенным ниже листинге между знаками круглых скобок и словом присутствует по два пробела.

Следует быть внимательными со строками фиксированной длины. Поскольку строка фиксированной длины имеет всегда одну и ту же длину, функция Len всегда возвращает объявленную длину строки, независимо от фактической длины строки. Например, если в строковой переменной фиксированной длины StrokeName, имеющей длину 15 символов, фактически находится слово «солнце», то функция Len(StrokeName) возвратит результат 15. Чтобы в таком случае узнать фактическую длину строки (в нашем случае — 6), необходимо использовать следующую комбинацию функций: Len(Trim(StrokeName)).

Сравнение и поиск строк

В VBA имеются две функции, помогающих сравнивать строки: StrComp, InStr.

Функция StrComp

StrComp (String1, String2 [, Compare])

String1, String2 — любые два строковых выражения, которые необходимо сравнивать.

Compare — необязательный аргумент, может быть любой из следующих предопределенных констант (если он опускается, то используется текущая установка Option Compare):

  • vbBinaryCompare — бинарное сравнение двух строк;
  • vbTextCompare — текстовое сравнение двух строк;
  • vbDatabaseCompare — используется только в Microsoft Access.

При выполнении StrComp возвращается одно из следующих значений:

  • -1, если String1 меньше String2;
  • 0, если String1 и String2 равны;
  • 1, если String1 больше String2.

В вышеприведенном листинге в текстовом режиме сравниваются две строки: «Строка по умолчанию» и » Строка по умолчанию». Результат сравнения = 1, т.е. «Строка по умолчанию» больше, чем » Строка по умолчанию».

Поэкспериментируйте с разнообразными строками для лучшего понимания работы функции StrComp.

Функция InStr

Функция InStr дает возможность определить, содержит ли одна строка другую строку.

StrComp ([Start, ] String1, String2 [, Compare])

String1, String2 — любые допустимые строковые выражения. Функция проверяет содержится ли String1 в String2.

Compare — необязательный аргумент, может быть любой из следующих предопределенных констант (если он опускается, то используется текущая установка Option Compare):

  • vbBinaryCompare — бинарное сравнение двух строк;
  • vbTextCompare — текстовое сравнение двух строк;
  • vbDatabaseCompare — используется только в Microsoft Access.

Start — необязательный аргумент, является численным выражением и указывает положение символа в String1, с которого должна начинаться проверка.

InStr возвращает число, обозначающее положение символа в String1, где было обнаружено String2. Если InStr не находит String2 в String1, то возвращается 0. Если String1 (или String2) имеет значение Null, то функция также возвращает Null.

Разбиение строки

Иногда возникает необходимость разбиения строки на составляющие части. В VBA эту задачу решают три функции: Left, Right, Mid.

Функция Left

Left (String, Length)

String — любое допустимое строковое выражение.

Length — любое численное значение.

Функция возвращает копию String, начиная с первого символа и включая количество символов, заданных с помощью Length. Если Length является числом, большим, чем фактическая длина String, то возвращается все строковое выражение String.

Читать еще:  Vba excel удалить файл

Функция Right

Right (String, Length)

String — любое допустимое строковое выражение.

Length — любое численное значение.

Функция возвращает копию String, начиная с последнего символа и включая справа налево количество символов, заданных с помощью Length. Если Length является числом, большим, чем фактическая длина String, то возвращается все строковое выражение String. Функция Right всегда копирует символы от конца строки к ее началу.

Функция Mid

Mid (String, Start, [, Length])

String — любое допустимое строковое выражение.

Length, Start — любые численные значения.

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

Символы, которые нельзя ввести с клавиатуры

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

Чтобы включить в строку символы, которые невозможно ввести с клавиатуры, или которые имеют особое значение для VBA, используется функция Chr.

Charcode — любое численное выражение, являющееся допустимым кодом для набора символов, используемого компьютером. Должен быть целым числом от 0 до 255.

Функция Chr принимает код отдельного символа в качестве аргумента и возвращает строку, содержащую соответствующий этому коду символ. Данная функция использована в вышеприведенных листингах для перевода строки при выводе сообщения на экран Chr (13).

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

  • vbCr — символ возврата каретки. Эквивалент Chr(13)
  • vbLf — символ смещения на одну строку. Эквивалент Chr(10)
  • vbCrLf — символ возврата каретки+смещения на одну строку. Эквивалент Chr(13)+ Chr(10)
  • vbTab — символ табуляции. Эквивалент Chr(9)

Для просмотра списка код-символ надо открыть справочную систему VBA и по запросу «character sets» будет представлена соответствующая таблица.


Форматирование значений данных

Очень часто формат данных на выходе программы по тем или иным причинам нас не совсем устраивает. Эту задачу решает функция Format.

VBA-функция Format идентична функции Format в Excel и использует те же символы-заполнители форматирования данных.

Format (Expression [, Format[, Firstdayofweek [, Firstweekofyear]]])

Expression — любое допустимое выражение (обязательный аргумент).

Format — допустимое выражение именованного или определенного пользователем формата.

Firstdayofweek — константа, которая определяет первый день недели.

Firstweekofyear — константа, которая определяет первую неделю года.

Чтобы использовать функцию Format, надо либо задать предопределенный формат, либо создать образ определенного формата, используя символы-заполнители.

Пояснить результат работы функции Mid

Неправильный результат работы функции Round
Здравстыуйте. Почему-то функция VBA Round(prm, 2) где prm, 10.505, 10.525, 10.545, 10.565, 10.585.

Функции Left, Right, Mid выдают ошибку
Всем привет. Подскажите пожалуйста. Я пишу макросы в Excel-е, так вот функция Left у меня работает.

VBA не понимает свои же функции: ROUND, LEFT, MID
На одних компах всё работает нормально, а на других компилятор ругается на такие функции, как.

Заменить функцию InStrRev функциями Right, Mid или другими функции
Всем привет. Скажите пожалуйста возможно ли в следующим задание заменить функцию "InStrRev".

Ципихович Эндрю, «9090690978, 4565665465, 8548375535» вот выход после Left-Right. 8ки нету.

Слева берем все символы до нее справа все после нее. складываем — получаем строку без 8-ки? Чего надо то еще? Работает не оставляя пробела как в первом варианте
а в чем соль обработки между запятыми поясни плиз. изначально речь шла как удалить 8.
ты там точно сам себе жизнь не усложняешь умышленно?

тоже самое что и пробел

Я вчера отвалился, да и поздновато было.
Пользуйся функциями работы со строками не надо ничего придумывать с сет.

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

То что ты пишешь — не знаю что и сказать.

Есть много функций работы со строками.
2 в пример я привел. Лично я сделал бы через Left-Right
Попробуй Replace.

спасибо, говорю как сделать — знаю, знаю, что есть много способов, просто очень удивился что это работает не как я думал
_______________________________
Функция Mid
Mid(String,Start,[Length])
Mid$(String,Start,[Length])
MidB(String,Start,[Length])
MidB$(String,Start,[Length])
Функция Mid(Middle) используется для считывания заданного числа символов или байт подряд от заданной позиции в строке слева направо. Нумерация символов в строке всегда начинается с единицы. Для определения числа символов в строке следует использовать функцию Len

Возвращаемое значение
Возвращает значение типа Variant (String), содержащее указанное число символов строки

Параметры
Функция содержит именованные аргументы
String
Обязательный аргумент — строка, из которой извлекаются символы. Если аргумент имеет значение Null, возвращается Null
Start
Обязательный аргумент — значение типа Long. Позиция символа в строке String, с которого начинается нужная подстрока. Если Start больше числа символов в строке string, функция Mid возвращает пустую строку («»)
Примечание Если аргумент имеет отрицательное значение или равен 0, то генерируется ошибка времени исполнения
Length
Необязательный аргумент — значение типа Variant (Long). Число возвращаемых символов. Если этот аргумент опущен или превышает число символов, расположенных справа от позиции Start, то возвращаются все символы от позиции Start до конца строки
Примечание Если аргумент имеет отрицательное значение, то генерируется ошибка времени исполнения
Пример

Работа со строками в VBA: InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse

В этой статье разберем работу со строками в VBA на примерах функций InStr, LCASE, UCase, Left, Right, Mid, LTrim, RTrim, Trim, Len, Replace, Space, StrComp, String, StrReverse.

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

Синтаксис

Примеры

Строковые функции

Существуют предопределенные функции VBA String, которые помогают разработчикам эффективно работать со строками. Ниже приведены методы String, поддерживаемые в VBA. Пожалуйста, нажмите на каждый из методов, чтобы знать подробно.

Название функции и описание

InStr

Функция InStr возвращает первое вхождение одной строки в другую строку. Поиск происходит слева направо.

Читать еще:  Excel vba примечание

Синтаксис

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

  • Пуск — необязательный параметр. Указывает начальную позицию для поиска. Поиск начинается с первой позиции слева направо.
  • String1 — требуемый параметр. Строка для поиска.
  • String2 — требуемый параметр. Строка, по которой выполняется поиск String1.
  • Compare — Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
  • 0 = vbBinaryCompare — выполняет двоичное сравнение (по умолчанию)
  • 1 = vbTextCompare — выполняет сравнение текста

пример

Добавьте кнопку и добавьте следующую функцию.

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

Line 1 : 6
Line 2 : 0
Line 3 : 8
Line 4 : 9
Line 5 : 2
Line 6 : 16
Line 7 : 11

Возвращает первое вхождение указанной подстроки. Поиск происходит слева направо.

Функция InStrRev возвращает первое вхождение одной строки в другую строку. Поиск происходит справа налево.

Синтаксис

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

  • String1 — требуемый параметр. Строка для поиска.
  • String2 — требуемый параметр. Строка, по которой выполняется поиск String1.
  • Пуск — необязательный параметр. Указывает начальную позицию для поиска. Поиск начинается с первой позиции справа налево.
  • Compare — Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
  • 0 = vbBinaryCompare — выполняет двоичное сравнение (по умолчанию)
  • 1 = vbTextCompare — выполняет сравнение текста

пример

Добавьте кнопку и установите следующую функцию.

После выполнения вышеуказанного скрипта он производит следующий результат.

Line 1 : 6
Line 2 : 6
Line 3 : 8
Line 4 : 0
Line 5 : 2
Line 6 : 2
Line 7 : 0

Возвращает первое вхождение указанной подстроки. Поиск происходит справа налево.

LCASE

Функция LCase возвращает строку после преобразования введенной строки в строчные буквы.

Синтаксис

пример

Добавьте кнопку и поместите следующую функцию внутри нее.

После выполнения вышеуказанного скрипта он производит следующий вывод.

Line 1 : microsoft vbscript
Line 2 : ms vbscript
Line 3 : microsoft

Возвращает нижний регистр указанной строки.

UCase

Функция UCase возвращает строку после преобразования введенной строки в буквы буквы UPPER.

Синтаксис

пример

Добавьте кнопку и поместите следующую функцию внутри нее.

После выполнения вышеуказанного скрипта он производит следующий вывод.

Line 1 : MICROSOFT VBSCRIPT
Line 2 : MS VBSCRIPT
Line 3 : MICROSOFT

Возвращает верхний регистр указанной строки.

Функция Left возвращает указанное количество символов с левой стороны данной входной строки.

Синтаксис

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

  • String — обязательный параметр. Строка ввода, из которой указанное число символов должно быть возвращено с левой стороны.
  • Длина — требуемый параметр. Целое число, определяющее количество возвращаемых символов.

пример

Добавьте кнопку и добавьте следующую функцию.

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

Line 1 : Mi
Line 2 : MS VB
Line 3 : microsoft

Возвращает определенное количество символов с левой стороны строки.

Right

Функция Right возвращает указанное количество символов с правой стороны данной входной строки.

Синтаксис

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

  • String — обязательный параметр. Строка ввода, из которой указанное число символов должно быть возвращено с правой стороны.
  • Длина — требуемый параметр. Целое число, которое задает количество возвращаемых символов.

пример

Добавьте кнопку и добавьте следующую функцию.

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

Line 1 : pt
Line 2 : CRIPT
Line 3 : microsoft

Возвращает определенное количество символов с правой стороны строки.

Mid функция возвращает указанное количество символов из заданной входной строки.

Синтаксис

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

  • String — обязательный параметр. Строка ввода, из которой задано количество символов, которые нужно вернуть.
  • Начало — требуемый параметр. Целое число, определяющее начальную позицию строки.
  • Длина — необязательный параметр. Целое число, определяющее количество возвращаемых символов.

Добавьте кнопку и добавьте следующую функцию.

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

Line 1 : icrosoft VBScript
Line 2 : icros
Line 3 : osoft V

Возвращает определенное количество символов из строки на основе указанных параметров.

LTrim

Функция Ltrim удаляет пробелы с левой стороны строки.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

Когда вы выполняете функцию, она производит следующий вывод.

After Ltrim : Microsoft VBScript

Возвращает строку после удаления пробелов в левой части указанной строки.

RTrim

Функция Rtrim удаляет пробелы с правой стороны строки.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

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

After Rtrim : Microsoft VBScript

Возвращает строку после удаления пробелов в правой части указанной строки.

Функция Trim удаляет как ведущее, так и конечное пустое пространство данной входной строки.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

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

After trim : Microsoft VBScript

Возвращает строковое значение после удаления как верхнего, так и конечного пробелов.

Функция Len возвращает длину данной входной строки, включая пробелы.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

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

Length of var1 is : 18
Length of var2 is : 36

Возвращает длину данной строки.

Replace

Функция Replace заменяет указанную часть строки на определенную строку, указанное количество раз.

Синтаксис

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

  • String — обязательный параметр. Строка ввода, которую нужно искать для замены.
  • Find — требуемый параметр. Часть строки, которая будет заменена.
  • Replacewith — обязательный параметр. Строка замены, которая будет заменена на параметр find.
  • Start — необязательный параметр. Задает начальную позицию, из которой нужно искать и заменять строку. Значение по умолчанию — 1.
  • Count — необязательный параметр. Указывает количество раз, которое должна выполняться замена.
  • Compare — Необязательный параметр. Указывает метод сравнения, который будет использоваться. Значение по умолчанию — 0.
  • 0 = vbBinaryCompare — выполняет двоичное сравнение
  • 1 = vbTextCompare — выполняет текстовое сравнение

пример

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

Line 1: This is MS VBScript Programming
Line 2: This is vbScript Programming
Line 3: Th## ## VBScript Programming
Line 4: ## VBScript Programming
Line 5: Thi## i## VBScript Programming
Line 6: This is VBSc##ipt P##og##amming
Line 7: This is VBScrip## Programming

Возвращает строку после замены строки другой строкой.

Space

Функция Space заполняет строку конкретным количеством пробелов.

Синтаксис

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

Номер — требуемый параметр. Количество пробелов, которые мы хотим добавить к данной строке.

пример

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

Заполняет строку указанным количеством пробелов.

StrComp

Функция StrComp возвращает целочисленное значение после сравнения двух заданных строк. Он может возвращать любое из трех значений -1, 0 или 1 на основе входных строк для сравнения.

  • Если String1 меньше String2, то StrComp возвращает -1
  • Если String1 равно String2, то StrComp возвращает 0
  • Если String1 больше String2, то StrComp возвращает 1

Синтаксис

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

  • String1 — требуемый параметр. Первое строковое выражение.
  • String2 — требуемый параметр. Второе строковое выражение.
  • Compare — Необязательный параметр. Указывает сравнение строк.Он может принимать следующие значения.
  • 0 = vbBinaryCompare — выполняет двоичное сравнение (по умолчанию)
  • 1 = vbTextCompare — выполняет сравнение текста

пример

Добавьте кнопку и добавьте следующую функцию.

Читать еще:  Excel не обновляет значение в ячейках

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

Line 1 :0
Line 2 :1
Line 3 :1
Line 4 :0
Line 5 :1

Возвращает целочисленное значение после сравнения двух указанных строк.

String

Функция String заполняет строку указанным символом для указанного количества раз.

Синтаксис

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

  • Номер — требуемый параметр. Целочисленное значение, которое будет повторяться в течение определенного количества раз против параметра символа.
  • Символ — требуемый параметр. Значение символа, которое должно повторяться определенное количество раз.

пример

Добавьте кнопку и добавьте следующую функцию.

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

Line 1 :$$$
Line 2 :****
Line 3 :ddddd
Line 4 :AAAAAA

Возвращает строку с указанным символом для указанного количества раз.

StrReverse

Функция StrReverse меняет указанную строку.

Синтаксис

пример

Добавьте кнопку и добавьте следующую функцию.

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

Line 1 : TPIRCSBV
Line 2 : tpircSBV tsriF yM
Line 3 : 54.321

Возвращает строку после изменения последовательности символов данной строки.

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

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

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

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

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

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

В ячейку А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. Отображение отрицательных значений времени».

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×