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

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

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

Ключевые слова

Текст научной работы

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

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

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

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

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

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

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

Рисунок 1. Диалоговое окно «Вставка функции»

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рисунок 2. Исходные данные к задаче 1

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).

Рисунок 3. Результат решения задачи 1

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

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

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

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

Рисунок 4. Исходные данные к задаче 2

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

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

Рисунок 5. Результат решения задачи 2

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

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

Рисунок 6. Исходные данные к задаче 3

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

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

Рисунок 7. Решение задачи 3

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

Рисунок 8. Результат решения задачи 3

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

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

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

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

  1. Дронова Е.Н.
  1. Дронова Е.Н.
  1. Дронова Е.Н.
  1. Дронова Е.Н.
  1. Дронова Е.Н.

Список литературы

  1. Дронова Е.Н. Использование табличного процессора для составления математических таблиц // NovaInfo.Ru. 2015. Т. 2. № 31. С. 324-332.
  2. Дронова Е.Н. Применение табличного процессора для решения уравнений // NovaInfo.Ru. 2016. Т. 2. № 51. С. 263-266.
  3. Дронова Е.Н. Решение задач оптимизации методом подбора параметра в электронных таблицах как средство развития мыслительных операций у учащихся // Современная педагогика. 2015. № 1 (26). С. 19-24.
  4. Практические работы по теме «Обработка текста в Excel». – URL: http://msk.edu.ua/ivk/Informatika/Books/Excel/Excel_Text_functions.pdf (дата обращения: 20.03.2017).
  5. Работа с текстовыми функциями. – URL: http://www.williamspublishing.com/PDF/978-5-8459-1346-3/part.pdf (дата обращения: 20.03.2017).
  6. Работа со строками в Excel. Текстовые функции Excel. – URL: http://www.programm-school.ru/tekstovie_funkcii_excel.html (дата обращения: 20.03.2017).
  7. Рудикова Л.В. Microsoft Excel для студента. – СПб.: БХВ-Петербург, 2005. – 368 с.
  8. Текстовые функции Excel». – URL: http://office-menu.ru/uroki-excel/13-uverennoe-ispolzovanie-excel/36-tekstovye-funktsii-v-excel (дата обращения: 20.03.2017).
  9. Функции для работы с текстом в Excel». – URL: http://excelexpert.ru/funkcii-dlya-raboty-s-tekstom-v-excel (дата обращения: 20.03.2017).
  10. Функции для работы с текстом в Excel». – URL: http://exceltable.com/funkcii-excel/funkcii-raboty-s-tekstom (дата обращения: 20.03.2017).

Цитировать

Дронова, Е.Н. Использование текстовых функций электронных таблиц / Е.Н. Дронова. — Текст : электронный // NovaInfo, 2017. — № 62. — С. 335-343. — URL: https://novainfo.ru/article/12125 (дата обращения: 02.07.2022).

Поделиться

Электронное периодическое издание зарегистрировано в Федеральной службе по надзору в сфере связи, информационных технологий и массовых коммуникаций (Роскомнадзор), свидетельство о регистрации СМИ — ЭЛ № ФС77-41429 от 23.07.2010 г.

📎📎📎📎📎📎📎📎📎📎