Меню

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



Функция ROW (СТРОКА) в Excel. Как использовать?

Функция СТРОКА в Excel используется когда вы хотите получить значение номера строки в которой находится конкретная ячейка.

  1. Что возвращает функция
  2. Синтаксис
  3. Аргументы функции
  4. Дополнительная информация
  5. Примеры использования функции ROW (СТРОКА) в Excel
  6. Пример 1. Вычисляем номер строки ячейки
  7. Пример 2. Вычисляем номер строки конкретной ячейки

Что возвращает функция

Функция возвращает порядковый номер строки в которой находится нужная ячейка с данными. Например, =ROW(B4) или =СТРОКА(B4) вернет “4”, так как ячейка “B4: находится в четвертой строке таблицы.

Синтаксис

=ROW([reference]) – английская версия

=СТРОКА([ссылка]) – русская версия

Аргументы функции

  • [reference] ([ссылка]) – необязательный аргумент, который ссылается на ячейку или диапазон ячеек. Если при использовании функции СТРОКА ( ROW) аргумент не указан, то функция отображает номер строки для той ячейки, в которой находится функция СТРОКА(ROW) .

Дополнительная информация

  • Если аргумент функции ссылается на диапазон ячеек, то функция вернет минимальное значение из указанного диапазона. Например, =ROW(B5:D10) или =СТРОКА(B5:D10) , функция СТРОКА вернет значение “5”;
  • Если аргумент функции ссылается на массив, функция вернет номера строк в которых находится каждый элемент указанного массива;
  • Аргумент функции не может ссылаться на несколько ссылок или адресов;

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

Пример 1. Вычисляем номер строки ячейки

Excel функция - ROW (СТРОКА) в Excel

Если вы введете =СТРОКА() или =ROW() в любой ячейке => функция вернет номер строки, в которой находится ячейка с этой функцией.

Пример 2. Вычисляем номер строки конкретной ячейки

Функция ROW (СТРОКА) в Excel

Если вы укажете ссылку на ячейку с помощью функции СТРОКА или ROW , она вернет номер строки этой ячейки.

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

Источник

Использование текстовых функций электронных таблиц

Аннотация

В работе рассматриваются возможности использования текстовых функций в электронных таблицах. На примере электронных таблиц Excel охарактеризованы наиболее полезные и интересные текстовые функции: ДЛСТР, ЗАМЕНИТЬ, ЛЕВСИМВ, НАЙТИ, ПОДСТАВИТЬ, ПРАВСИМВ, ПРОПИСН, ПРОПНАЧ, ПСТР, СЖПРОБЕЛЫ, СОВПАД, СТРОЧН, СЦЕПИТЬ. Приведены примеры задач на использование описанных текстовых функций электронных таблиц.

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

Наиболее популярными направлениями применения электронных таблиц являются следующие:

  • проведение однотипных расчетов над большими наборами данных [1],
  • решение задач путем подбора значений параметров [2],
  • обработка результатов экспериментов,
  • проведение поиска оптимальных значений параметров [3],
  • подготовка табличных документов,
  • построение диаграмм и графиков по имеющимся данными др. [7].

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

В данной статье мы рассмотрим обработку текстовых данных с помощью электронных таблиц. Несмотря на то, что данное направление использования электронных таблиц не является популярным, с текстовыми данными пользователи сталкиваются часто, например, с ФИО, номерами банковских карт, адресами клиентов или сотрудников, комментариями и т.п. Все эти данные являются строками, поэтому полезно уметь обрабатывать информацию подобного типа в электронных таблицах. Для этого используются текстовые функции. Рассмотрим их на примере табличного процессора Excel, который является одним из наиболее популярных [7].

Текстовых функций в электронных таблицах Excel достаточно много [9], рассмотрим наиболее полезные и интересные: ДЛСТР, ЗАМЕНИТЬ, ЛЕВСИМВ, НАЙТИ, ПОДСТАВИТЬ, ПРАВСИМВ, ПРОПИСН, ПРОПНАЧ, ПСТР, СЖПРОБЕЛЫ, СОВПАД, СТРОЧН, СЦЕПИТЬ [6, 8].

Список всех текстовых функций доступен по команде Вставка>Вставка функции, далее нужно выбрать в поле Категория позицию Текстовые (рис. 1).

Кратко охарактеризуем вышеуказанные текстовые функции.

Функция ДЛСТР

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

Определение аргумента: текст — это строка, длину которой следует определить (пробелы считаются знаками).

Функция ЗАМЕНИТЬ

Заменяет часть строки текста на другую строку.

Синтаксис: =ЗАМЕНИТЬ(старый_текст; нач_позиция; число_знаков; новый_текст).

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

Функция ЛЕВСИМВ

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

Синтаксис: =ЛЕВСИМВ(текст; количество_знаков).

  • текст: строка текста, содержащая знаки, которые нужно извлечь;
  • количество_знаков: количество знаков, которое нужно извлечь; если не указано, принимается равным 1.

Функция НАЙТИ

Возвращает позицию начала искомой строки текста в содержащей её строке текста (прописные и строчные буквы различаются).

Синтаксис: =НАЙТИ(искомый_текст;просматриваемый_текст; нач_позиция).

  • искомый_текст: строка, которую необходимо найти;
  • просматриваемый_текст: строка, содержащая искомый текст;
  • нач_позиция: позиция, с которой нужно начать поиск; если значение не указано, то начальная позиция принимается равной 1.

Функция ПОДСТАВИТЬ

Заменяет новым текстом старый текст в текстовой строке.

Синтаксис: =ПОДСТАВИТЬ(текст; стар_текст; нов_текст; номер_вхождения).

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

Замечание: функция «ПОДСТАВИТЬ» схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие — если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Функция ПРАВСИМВ

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

Синтаксис: =ПРАВСИМВ(текст; количество_знаков).

  • текст: строка текста, содержащая знаки, которые нужно извлечь;
  • количество_знаков: количество знаков, которое нужно извлечь; если не указано, принимается равным 1.

Функция ПРОПИСН

Делает все буквы в строке текста прописными.

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

Функция ПРОПНАЧ

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

Определение аргумента: текст — строка текста, в которой первая буква преобразуется в прописную.

Функция ПСТР

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

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

  • текст: текстовая строка, из которой следует извлечь знаки;
  • начальная_позиция: позиция, начиная с которой следует извлечь знаки;
  • количество_знаков: количество знаков, которое следует извлечь из текста.

Функция СЖПРОБЕЛЫ

Удаляет из текста лишние пробелы (кроме одиночных пробелов между словами).

Определение аргумента: текст — это текст, из которого удаляются пробелы.

Функция СОВПАД

Проверяет идентичность двух строк текста и возвращает значение ИСТИНА или ЛОЖЬ (прописные и строчные буквы различаются).

  • текст1: первая текстовая строка;
  • текст2: вторая текстовая строка.

Функция СТРОЧН

Делает все буквы в строке текста строчными

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

Функция СЦЕП

Объединяет несколько текстовых строк в одну.

Определения аргументов: текст1, текст2 — это текстовые строки, которые следует объединить в одну строку.

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

Задача 1

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

Решение

1. Сформулируем словесный алгоритм:

  1. фамилию запишем как левые символы исходной строки, количество которых равно позиции первого пробела минус 1;
  2. имя запишем как символы исходной строки текста, начиная с позиции первого пробела плюс 1 до позиции второго пробела;
  3. отчество запишем как правые символы исходной строки, количество которых равно разности длины исходной строки и позиции второго пробела.
Читайте также:  Сводные таблицы впр гпр если

2. Формализуем приведенный словесный алгоритм в электронных таблицах Excel:

  1. с помощью функции НАЙТИ находим позицию первого пробела: =НАЙТИ(» «;A2;1); итоговая формула для нахождения фамилии: =ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1);
  2. с помощью функции НАЙТИ находим позицию второго пробела: =НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1); для вывода имени используем функцию ПСТР: =ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-НАЙТИ(» «;A2;1));
  3. с помощью функции ДЛСТР найдем длину всей строки, затем вычислим количество символов в отчестве =ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1); итоговая формула для вывода отчества: =ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1).

3. Копированием формул на другие ячейки убеждаемся в правильности решения (рис. 3).

Замечания

1. Приведенное решение неединственное.

2. Задачу 1 можно усложнить, если задать условия:

  • между фамилией, именем и отчеством возможно стоит не один пробел (в этом случае нужно предварительно воспользоваться функцией СЖПРОБЕЛ, которая удаляет лишние пробелы);
  • фамилия, имя и отчество набраны неграмотно с точки зрения использования прописных и строчных букв, например, ПопОВа ЛиДия владИмирОвна (в этом случае нужно предварительно воспользоваться функцией ПРОПНАЧ, которая делает прописной первую букву в каждом слове текста, преобразуя все другие буквы в строчные.

Задача 2

Объедините в электронных таблицах с помощью текстовых функций фамилию, имя и отчество в одну ячейку, если они записаны в разных столбцах (рис. 4).

Решение

1. Для объединения отдельных строк текста в одну ячейку используем функцию СЦЕП, учитывая необходимость добавления пробела между фамилией и именем, именем и отчеством; итоговая формула для получения ФИО: =СЦЕП(A2;» «;B2;» «;C2).

2. Копированием формул на другие ячейки убеждаемся в правильности решения (рис. 5).

Задача 3

Используя слова «информация» и «оператор», получите в электронной таблице слова «информатор» и «операция».

Решение

1. Заполним электронную таблицу исходными данными (рис. 6).

2. Для получения заданных слов будем использовать функцию ЗАМЕНИТЬ (рис. 7):

  • в ячейке В1 для получения слова «информатор» вводим формулу =ЗАМЕНИТЬ(A1;8;3;ПРАВСИМВ(A2;3));
  • в ячейке В2 для получения слова «оператор» вводим формулу =ЗАМЕНИТЬ(A2;6;3;ПРАВСИМВ(A1;3)).

3. В результате получим требуемые слова (рис. 8).

Замечание

Приведенное решение неединственное, например:

  • можно использовать функции ПРАВСИМВ и ПОДСТАВИТЬ, в частности, слово «информатор» задается формулой =ПОДСТАВИТЬ(A1;ПРАВСИМВ(A1;3);ПРАВСИМВ(A2;3));
  • можно использовать функции СЦЕП, ЛЕВСИМВ и ПРАВСИМВ, в частности, слово «информатор» задается формулой =СЦЕП(ЛЕВСИМВ(A1;7);ПРАВСИМВ(A2;3)).

В заключении отметим, что рассмотренные задачи иллюстрируют лишь основы использования текстовых функций электронных таблиц [4, 5, 10]. В жизни встречается масса других задач с текстовыми данными, которые можно рационально решить с помощью текстовых функций в электронных таблицах.

Источник

Строковые функции и их использование

С помощью строковой функции в Access можно создавать выражения, которые могут работать с текстом различными способами. Например, может потребоваться отобразить в форме только часть серийного номера. Также может потребоваться объединить несколько строк, например фамилию и имя. Если вы еще не раз разбирались в выражениях, см. статью «Построение выражений».

Ниже перечислены некоторые из наиболее распространенных строковых операций в Access и функции, которые используются для их выполнения:

Возврат знаков с начала строки

Если [СерийныйНомер] = «CD234», результат — «CD»

Возврат знаков с конца строки

Если [СерийныйНомер] = «CD234», результат — «234»

Поиск позиции знака в строке

Если [Имя] = «Алексей», результат — 4

Возврат знаков из середины строки

Если [СерийныйНомер] = «CD234», результат — «D2»

Удаление начальных и конечных пробелов из строки.

Если [Имя] = » Алексей «, результатом будет: «Алексей».

Объединение двух строк

Оператор «знак плюс» (+)*

Если [Имя] = «Алексей», а [Фамилия] = «Орехов», результат — «АлексейОрехов»

Объединение двух строк с пробелом между ними

Оператор «знак плюс» (+)*

Если [Имя] = «Алексей», а [Фамилия] = «Орехов», результат — «Алексей Орехов»

Изменения регистра строки на верхний или нижний

Если [Имя] = «Алексей», результат — «АЛЕКСЕЙ»

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

Если [Имя] = «Алексей», результат — 7

* Это не функция, а оператор. Однако это самый быстрый способ объединить строки. В базе данных рабочего стола для одновременного использования также можно использовать оператор амперанда (&). В приложении Access необходимо использовать знак «плюс» (+).

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

Чтобы познакомиться с построителем выражений, давайте откроем его из свойства Данные формы или представления. В зависимости от того, используете ли вы базу данных рабочего стола или веб-приложение Access, воспользуйтесь одной из процедур ниже.

Запуск построителя выражений в базе данных на компьютере

Откройте базу данных на компьютере (ACCDB-файл).

Если область навигации еще не открыта, откройте ее с помощью клавиши F11.

Если у вас уже есть какая-нибудь форма, щелкните ее в области навигации правой кнопкой мыши и выберите пункт Режим макета. Если форм у вас пока нет, на вкладке Создание нажмите Форма.

Щелкните правой кнопкой мыши текстовое поле в форме и выберите пункт Свойства.

На странице свойств нажмите Все > Данные, а затем — кнопку Построение в правой части поля свойства Данные.

В разделе Элементы выражений разверните узел Функции и выберите пункт Встроенные функции.

В разделе Категории выражений выберите пункт Текстовые.

В разделе Значения выражений выберите интересующие вас функции и прочитайте их краткое описание в нижней части построителя выражений.

Примечание: Не каждая из этих функций доступна в любом контексте. Access автоматически фильтрует список в зависимости от контекста.

Отображение построитель выражений в веб-приложении Access

Откройте веб-приложение в Access. Если вы просматриваете его в браузере, нажмите Настройки > Настроить в Access.

Щелкните таблицу в левом столбце, а затем в правой части списка таблиц выберите представление.

Нажмите Изменить, щелкните текстовое поле, а затем нажмите кнопку Данные, которая появится около этого поля.

Нажмите кнопку Построение справа от раскрывающегося списка Данные.

В разделе Элементы выражений разверните узел Функции и выберите пункт Встроенные функции.

В разделе Категории выражений выберите пункт Текстовые.

В разделе Значения выражений выберите интересующие вас функции и прочитайте их краткое описание в нижней части построителя выражений.

Комбинирование текстовых функций для большей гибкости

Иногда при каждом вызове строковой функции приходится заново вычислять ее числовые аргументы. Например, функция Left принимает строку и число, как здесь: =Left([СерийныйНомер], 2). Хорошо, если вам всегда требуются именно два первых знака строки, но иногда нужное число знаков для разных элементов различно. Вместо того чтобы однозначно задавать число знаков, его можно вычислять с помощью другой функции.

Вот пример серийных номеров, в каждом из которых есть дефис в строке. Но положение дефила различается:

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

Вместо того чтобы вводить число в качестве второго аргумента функции Left, мы подключили функцию InStr,которая возвращает позицию дефила в числовом номере. Вычитаем из этого значения 1 и получаем число знаков, которые должна вернуть функция Left. Сначала это кажется немного сложным, но после небольшой практики вы сможете комбинировать несколько выражений для получения нужного результата.

Дополнительные сведения об использовании строковых функций см. в статье Использование строковых функций в SQL-запросах в Access.

Читайте также:  Таблица умножения обычная без рисунков

Источник

Функция СТРОКА() в MS EXCEL

09 апреля 2013 г.

Синтаксис функции

Ссылка — ячейка или интервал ячеек, для которых определяется номер строки.

Если ссылка опущена, то функция СТРОКА() вернет номер строки в которую введена сама формула. Т.е. если формула =СТРОКА() введена в ячейку B5 , то результат будет 5.

В аргументе «ссылка» нельзя указывать несколько областей, т.е. запись =СТРОКА(A1:A2;B2:B3) не допустима.

Использование функции в формулах массива

Так как функция СТРОКА() в качестве аргумента позволяет ввести ссылку на диапазон ячеек, то следует ожидать, что она вернет не одно число, а массив целых чисел — соответствующих номеров строк. Для этого введите в Строке формул выражение =СТРОКА(F8:F18) . Выделите формулу и нажмите F9 (см. файл примера ).

Элементы этого массива можно вывести в отдельные ячейки. Для этого выделите диапазон A5:A18 , состоящий из 13 ячеек, в Строке формул введите выражение =СТРОКА(F8:F18) как формулу массива, нажав CTRL+SHIFT+ENTER .

Функция СТРОКА() вернула номера строк, указанных в аргументе «ссылка». Т.к. число выделенных ячеек (13) было больше числа строк диапазона F8:F18 (10), то в трех ячейках формула вернула значения ошибки #Н/Д.

Чтобы вывести номера строк в горизонтальном диапазоне используйте формулу =ТРАНСП(СТРОКА(F8:F18)) . Не забудьте нажать CTRL+SHIFT+ENTER .

Использование функции для чрезстрочного выделения

Записав в правиле Условного форматирования для диапазона A5:A18 формулу =НЕЧЁТ(СТРОКА())=СТРОКА() получим выделение цветом каждой второй строки (см. рисунок выше).

Формирование списка без повторов

Если список значений содержит повторяющиеся значения (диапазон A21:A34 ), то функция СТРОКА() может быть использована для формирования списка без повторов. Формула =ЕСЛИ(ПОИСКПОЗ(A21;$A$21:$A$34;0)=СТРОКА()-СТРОКА($A$20);A21;»») возвращает значение если оно встречается первый раз.

Источник

Как извлечь текст из ячейки при помощи функции ПСТР и специальных инструментов

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

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

Cинтаксис.

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

Функция Excel ПСТР имеет следующие аргументы:

ПСТР(текст; начальная_позиция; количество_знаков)

Где текст — это исходная текстовая строка. Далее следует позиция первого символа, который вы хотите извлечь, и количество их для извлечения.

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

Например, чтобы извлечь 6 знаков из A2, начиная с 17-го, используйте эту формулу:

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

5 вещей, которые вы должны знать о функции Excel ПСТР

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

  1. Функция ПСТР всегда возвращает текстовую строку, даже если извлеченная подстрока содержит только цифры. Это может иметь большое значение, если вы хотите использовать результат формулы ПСТР в других вычислениях. Чтобы преобразовать цифры в число, применяйте ПСТР в сочетании с функцией ЗНАЧЕН (VALUE в английской версии), как показано в этом примере. (ссылка на последний раздел).
  2. Когда начальная позиция больше, чем общая длина исходного текста, формула Excel ПСТР возвращает пустое значение («»).
  3. Если начальная позиция меньше 1, формула ПСТР возвращает ошибку #ЗНАЧ!.
  4. Когда третий аргумент меньше 0 (отрицательное число), формула ПСТР возвращает ошибку #ЗНАЧ!. Если количество знаков для извлечения равно 0, выводится пустая строка (пустая ячейка).
  5. В случае, если сумма начальной позиции и количества знаков превышает общую длину исходного текста, функция ПСТР в Excel возвращает подстроку начиная с начальной позиции и до последнего символа.

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

Как извлечь имя и фамилию.

Если у вас была возможность прочитать наши недавние уроки, вы уже знаете, как вытащить имя с помощью функции ЛЕВСИМВ и получить фамилию с помощью ПРАВСИМВ. Но, как это часто бывает в Excel, одно и то же можно сделать разными способами.

Получаем имя.

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

ПОИСК используется для сканирования исходного значения на предмет пробела (» «) и возврата его позиции, из которой вы вычитаете 1, чтобы избежать пробелов после имени. Затем вы используете ПСТР, чтобы вернуть подстроку, начинающуюся с первого знака и заканчивая предшествующим пробелу, таким образом извлекая первое имя.

Получаем фамилию.

Чтобы извлечь фамилию из A2, используйте эту формулу:

Опять же, вы используете ПОИСК, чтобы определить начальную позицию (пробел). Нам не нужно точно рассчитывать конечную позицию (как вы помните, если вместе взятые начальная позиция и количество символов больше, чем общая длина текста, возвращаются просто все оставшиеся). Итак, в аргументе количество символов вы просто указываете общую первоначальную длину , возвращаемую функцией ДЛСТР . Впрочем, вместо этого вы можете просто ввести число, представляющее самую длинную фамилию, которую вы ожидаете найти, например 100. Наконец, СЖПРОБЕЛЫ удаляет лишние интервалы, и вы получаете следующий результат:

Как выделить подстроку между двумя разделителями.

Продолжим предыдущий пример. А если, помимо имени и фамилии, ячейка A2 также содержит отчество, то как его извлечь?

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

  • Как и в предыдущем примере, используйте ПОИСК, чтобы определить позицию первого (» «), к которому вы добавляете 1, потому что вы хотите начать с символа, следующего за ним. Таким образом, вы получаете адрес начальной позиции: ПОИСК (» «; A2) +1
  • Затем вычислите позицию 2- го интервала, используя вложенные функции поиска, которые предписывают Excel начать поиск именно со 2-го: ПОИСК (» «; A2, ПОИСК (» «; A2) +1)

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

ПОИСК(» «; A2; ПОИСК(» «; A2) +1) — ПОИСК(» «; A2)

Соединив все аргументы, мы получаем формулу для извлечения подстроки между двумя пробелами:

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

Аналогичным образом вы можете извлечь текст между любыми другими разделителями:

ПСТР( строка ; ПОИСК( разделитель ; строка ) +1; ПОИСК( разделитель ; строка ; ПОИСК( разделитель ; строка ) +1) — ПОИСК( разделитель ; строка ) -1)

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

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

Как получить N-е слово из текста.

Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:

  • ДЛСТР — чтобы получить общую длину.
  • ПОВТОР — повторение определенного знака заданное количество раз.
  • ПОДСТАВИТЬ — заменить один символ другим.
  • ПСТР — извлечь подстроку.
  • СЖПРОБЕЛЫ — удалить лишние интервалы между словами.

Общая формула выглядит следующим образом:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ( строка ; » «; ПОВТОР (» «; ДЛСТР( строка ))); ( N -1) * ДЛСТР( строка ) +1; ДЛСТР( строка )))

  • Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
  • N – порядковый номер слова, которое нужно получить.
Читайте также:  Таблица рефлексия собственного опыта

Например, чтобы вытащить второе слово из A2, используйте это выражение:

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

Как работает эта формула?

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

  • ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2)))

Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.

  • Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
  • Количество букв и цифр для извлечения (второй аргумент) — самая простая часть — вы просто берете общую первоначальную длину: ДЛСТР(A2).
  • Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.

Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР($A$2))); (B2-1)*ДЛСТР($A$2)+1; ДЛСТР($A$2)))

Следующий рисунок демонстрирует улучшенный вариант (2) в действии:

Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($A$2);» «; ПОВТОР(» «;ДЛСТР(СЖПРОБЕЛЫ ($A$2)))); (B2-1)*ДЛСТР(СЖПРОБЕЛЫ($A$2))+1; ДЛСТР(СЖПРОБЕЛЫ($A$2))))

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

Извлекаем слово, содержащее определенный символ.

В этом примере показана еще одна нестандартная формула Excel ПСТР, которая извлекает слово, содержащее определенную букву или цифру, из любого места:

СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ ( строка , » «, ПОВТОР(» «, 99)), МАКС(1, НАЙТИ( символ , ПОДСТАВИТЬ( строка , » «, ПОВТОР(» «, 99))) — 50), 99))

Предполагая, что исходный текст находится в ячейке A2, и вы хотите получить слово, содержащее символ «$» (цена), выражение принимает следующую форму:

=СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)); МАКС(1;НАЙТИ(«$»;ПОДСТАВИТЬ(A2;» «;ПОВТОР(» «;99)))-50);99))

Аналогичным образом вы можете извлекать адреса электронной почты (на основе знака «@»), имена веб-сайтов (на основе «www») и так далее.

Теперь разберём пошагово:

Как и в предыдущем примере, ПОДСТАВИТЬ и ПОВТОР превращают каждый пробел в исходном тексте в несколько, точнее, в 99.

НАЙТИ находит позицию нужного символа (в данном примере $), из которой вы вычитаете 50. Это возвращает вас на 50 позиций назад и помещает где-то в середине блока из 99 пробелов, который предшествует слову, содержащему указанный символ.

МАКС используется для обработки ситуации, когда нужное значение появляется в начале исходного текста. В этом случае результат ПОИСК() — 50 будет отрицательным числом, а МАКС(1, ПОИСК() — 50) заменяет его на 1.

С этой начальной точки ПСТР отбирает следующие 99 знаков и возвращает интересующее нас слово, окруженное множеством пробелов. Как обычно, СЖПРОБЕЛЫ помогает избавиться от лишних из них, оставив только один.

Совет. Если извлекаемый отрезок очень большой, замените 99 и 50 на более крупные числа, например 1000 и 500.

Как заставить ПСТР возвращать число?

Как и другие текстовые функции, Excel ПСТР всегда возвращает текст, даже если он содержит только цифры и очень похож на число. Вы можете убедиться с этом, взглянув на пример чуть выше, когда мы получили число «20%» как текст.

Чтобы преобразовать результат в число, просто передайте полученный результат в функцию ЗНАЧЕН (VALUE в английской версии), которая преобразует текстовое значение, состоящее из цифр, в число.

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

На скриншоте ниже показан результат. Обратите внимание, что числа с выравниванием по правому краю помещены в столбец B, в отличие от исходных текстовых значений с выравниванием по левому краю в столбце A:

Тот же подход работает и для более сложных случаев. В приведенном выше примере, предполагая, что коды ошибок имеют переменную длину, вы можете извлечь их с помощью ПСТР, которая получает подстроку между двумя разделителями, вложенную в ЗНАЧЕН:

Вот как можно использовать функцию ПСТР в Excel.

Как извлечь текст из ячейки с помощью Ultimate Suite

Как вы только что видели, Microsoft Excel предоставляет набор различных функций для работы с текстовыми строками. Если вам нужно извлечь какое-то слово или часть текста из ячейки, но вы не уверены, какая функция лучше всего подходит для ваших нужд, передайте работу Ultimate Suite for Excel. Заодно не придётся возиться с формулами.

Вы просто переходите на вкладку Ablebits Data > Текст и нажимаете Извлечь (Extract ) :

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

  1. Укажите, сколько символов вы хотите получить из начала, конца или середины строки; или выберите извлечение всего текста до или после определенного символа.
  2. Щелкните Вставить результаты ( Insert Results ). Готово!

Кроме того, вы можете извлечь любое число символов с начала или в конце текста, из середины текста, между какими-то символами. Например, чтобы извлечь доменные имена из списка адресов электронной почты, вы выбираете чекбокс Все после текста (All after text) и вводите @ в поле рядом с ним. Чтобы извлечь имена пользователей, выберите переключатель Все до текста (All before text), как показано на рисунке ниже.

Помимо скорости и простоты, инструмент «Извлечь текст» имеет дополнительную ценность — он поможет вам изучить формулы Excel в целом и функции подстроки в частности. Как? Выбрав флажок Вставить как формула ( Insert as formula ) в нижней части панели, вы убедитесь, что результаты выводятся в виде формул, а не просто как значения. Естественно, эти формулы вы можете использовать в других таблицах.

В этом примере, если вы выберете ячейки B2 и C2, вы увидите следующие формулы соответственно:

  • Чтобы извлечь имя пользователя:

Сколько времени вам потребуется, чтобы самостоятельно составить эти выражения? 😉

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

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

Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Источник

Adblock
detector