для чего предназначены текстовые функции
Текстовые функции Excel
Все знают, что табличный процессор Microsoft Excel предназначен для обработки числовой информации. Его функционал позволяет обрабатывать большие объемы вычисляемых данных. А ведь с его помощью можно обработать и текст. Это могут быть фамилии, имена, отчества сотрудников, почтовые адреса и много другой текстовой информации, записанной в ячейки таблиц.
Различные сервисы интернета позволяют осуществлять импорт данных в формате электронных таблиц и не всегда эти данные удобны для дальнейшей обработки. А если требуется скорректировать текст на сотнях строк, то без текстовых функций не обойтись. В этой статье я предлагаю вам ознакомиться с текстовыми функциями в Excel и разобраться на примерах с их использованием.
Текстовые функции Microsoft Excel
Сначала рассмотрим основные текстовые функции, которые использованы в примерах ниже. Excel использует русские названия функций в отличие от своего ближайшего аналога LibreOffice.Calc. Ниже приведен формат записи текстовой функции и ее действие.
Задача 1. Объединение текстовых строк
Есть список сотрудников. Фамилии, имена и отчества разнесены в отдельные столбцы. Необходимо объединить данные в один столбец.
Решение. Задача достаточно простая и для ее реализации воспользуемся функцией СЦЕПИТЬ.
В ячейку D1 запишем формулу =СЦЕПИТЬ(A1;» «;B1;» «;C1). Можно воспользоваться мастером функций.
Далее скопируем ее на весь необходимый диапазон столбца D.
Если в функции указать только адреса ячеек, то текст склеится в одну длинную строку. Поэтому добавляем пробел в кавычках между адресами ячеек.
Посмотрите на рисунок ниже. Результат преобразования в столбце D.
Окно мастера функции СЦЕПИТЬ
Задача 2. Разделение текстовых строк
После импорта данных сотрудников их фамилии, имена и отчества оказались в одном столбце. Необходимо разделить данные по столбцам.
Решение. Задача сложнее предыдущей и для ее реализации понадобится несколько текстовых функций.
Для отделения фамилии сотрудника и запишем в ячейку B1 формулу
=ЛЕВСИМВ(A1;НАЙТИ(» «;A1))
В этой формуле количество выводимых знаков подсчитывается функцией Найти, которая определяет местонахождение первого пробела в строке.
Для записи имени в ячейку C1 запишем следующую формулу
=ПСТР(A1;НАЙТИ(» «;A1)+1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)-НАЙТИ(» «;A1)-1)
Если посмотреть на синтаксис записи данной функции, то получаем:
Отчество получается в ячейке D1 по более сложной формуле
=ПСТР(A1;ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1;ДЛСТР(A1)-ПОИСК(» «;A1;НАЙТИ(» «;A1)+1)+1)
Здесь количество знаков в отчестве определяется как разность общего количества символов (ДЛСТР) и позицией второго пробела.
В рассмотренных примерах функции ПОИСК и НАЙТИ выполняют одинаковые операции, так как разница в регистрах символов не учитывается. Возможно обойтись только одной из них.
Задача 3. Укорачивание текстовых строк
В список сотрудников внести изменения. Записать в одном столбце Фамилии и инициалы.
Решение. В зависимости от исходного состояния списка возможны два варианта.
1 вариант. Исходные данные содержатся в одном столбце. ФИО разделены одинарным пробелом.
Записываем следующую формулу
=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(» «;A1));ПСТР(A1;НАЙТИ(» «;A1);2);».»;ПСТР(A1;НАЙТИ(» «;A1; НАЙТИ(» «;A1)+1);2);».»)
Преобразуем имя и отчество в инициалы (исходные данные в одном столбце)
2 вариант. Исходные данные содержатся в разных столбцах.
Формула для преобразования
=СЦЕПИТЬ(A1;» «;ЛЕВСИМВ(B1);».»;ЛЕВСИМВ(C1);».»)
Преобразуем имя и отчество в инициалы (исходные данные в разных столбцах)
Друзья, если вы можете привести свои примеры использования текстовых функций в Excel, расскажите о них в комментариях. Рекомендую посмотреть статью о построении графиков функций в Excel. Понравилась статья, поделитесь с друзьями. Кнопочки социальных сетей внизу.
Дорогой читатель! Вы посмотрели статью до конца. Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали или откройте содержание блога. 
Текстовые функции
Большое количество функций предназначено для обработки текстов. С помощью этих функций пользователь может преобразовывать прописные литеры в строчные, текстовые значения в числовые и обратно, а также выполнять целый ряд других операций. В качестве аргументов текстовых функций используются, как правило, цепочки символов.
Функция ДЛСТР
Результат: Количество символов в текстовом аргументе текст.
Функция ЗАМЕНИТЬ
Результат: В текстовом аргументе старый_текст заменяет число_литер символов, начиная с нач_ном-ого, на текст новый_текст.
Функция ЗНАЧЕН
Результат: Преобразует текстовый аргумент текст в числовой формат.
Обычно Excel при необходимости автоматически осуществляет преобразование из текстового представления в числовое.
Функция КОДСИВМ
Результат: ASCII-код первого символа текста.
Функция ЛЕВСИМВ
Результат: Возвращает первые количество_символов символов текстового аргумента текст в виде текстового значения.
Функция НАЙТИ
Результат: Находит вхождение одной текстовой строки в другую текстовую строку и возвращает номер символа, с которого начинается первое вхождение искомой строки. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК функция НАЙТИ учитывает регистр и не допускает символов шаблона.
Если аргумент нач_позиция меньше или равен 0, или больше, чем длина текста, или не найден аргумент искомый_текст, то функция НАЙТИ выдает значение ошибки #ЗНАЧ!.
Функция ПЕЧСИМВ
Результат: Из аргумента текст будут удалены все управляющие символы.
Функция ПОВТОР
Результат: Текст, указанный в аргументе текст, повторяется сколько_раз раз.
Результат может иметь длину не более 255 символов.
Функция ПОДСТАВИТЬ
Результат: Текст, указанный в аргументе старый_текст, заменяется в аргументе текст на текст новый_текст.
Функция ПОИСК
Результат: См. описание функции НАЙТИ.
См. описание функции НАЙТИ.
Функция ПРАВСИМВ
Результат: Последние число_символов символов текстового аргумента текст в качестве текстового значения.
Функция ПРОПИСИ
Результат: Преобразует все буквы в тексте в прописные.
Функция ПРОПНАЧ
Функция ПСТР
Результат: Текстовое значение, которое включает количество_символов символов из текстового аргумента текст, начиная с позиции номер нач_позиция.
Функция РУБЛЬ
Результат: Преобразует число в текстовый формат, используя денежный формат с округлением до заданного числа десятичных знаков. Применяется следующий формат: #.##0,00р;-#.##0,00р.
Функция СЖПРОБЕЛЫ
Результат: Удаляет все пробелы из текстового аргумента текст, оставляя только по одному между словами.
Функция СИМВОЛ
Результат: Символ, соответствующий ASCII-коду числа, заданного аргументом число. Функция CHAR используется для преобразования текстовых файлов в текстовый формат данного компьютера.
Функция СОВПАД
Функция СТРОЧН
Результат: Все прописные буквы текстового аргумента текст будут преобразованы в строчные.
Функция СЦЕПИТЬ
Результат: Объединяет несколько текстовых элементов в один.
Функция Т
Результат: Аргумент значение выводится в текстовом виде. Если аргументу значение не соответствует текст, то функция Т возвращает «».
Обычно Excel автоматически преобразует значения к нужному типу.
Функция ТЕКСТ
Результат: Преобразует значение в текст в заданном числовом формате.
Функция ФИКСИРОВАННЫЙ
Результат: Число число, округленное до число_знаков десятичных разрядов, в формате с фиксированной запятой в виде текстовой строки.
Текстовые функции (справка)
Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.
Примечание: Маркер версии обозначает версию Excel, в которой она впервые появилась. В более ранних версиях эта функция отсутствует. Например, маркер версии 2013 означает, что данная функция доступна в выпуске Excel 2013 и всех последующих версиях.
Для языков с двухбайтовыми наборами знаков (например, катакана) преобразует полноширинные (двухбайтовые) знаки в полуширинные (однобайтовые).
Преобразует число в текст, используя денежный формат ß (БАТ).
Возвращает символ с заданным кодом.
Удаляет из текста все непечатаемые символы.
Возвращает числовой код первого знака в текстовой строке.
СЦЕП
Объединяет текст из нескольких диапазонов или строк, но не добавляет разделитель или аргументы IgnoreEmpty.
Объединяет несколько текстовых элементов в один.
DBCS
Для языков с двухбайтовыми наборами знаков (например, катакана) преобразует полуширинные (однобайтовые) знаки в текстовой строке в полноширинные (двухбайтовые).
Преобразует число в текст, используя денежный формат рубля.
Проверяет идентичность двух текстовых значений.
Ищет вхождения одного текстового значения в другом (с учетом регистра).
Форматирует число и преобразует его в текст с заданным числом десятичных знаков.
Возвращают крайние слева знаки текстового значения.
Возвращают количество знаков в текстовой строке.
Преобразует все буквы текста в строчные.
Возвращают заданное число знаков из строки текста, начиная с указанной позиции.
ЧЗНАЧ
Преобразует текст в число независимо от языкового стандарта.
Извлекает фонетические (фуригана) знаки из текстовой строки.
Преобразует первую букву в каждом слове текста в прописную.
Заменяют знаки в тексте.
Повторяет текст заданное число раз.
Возвращают крайние справа знаки текстовой строки.
Ищут вхождения одного текстового значения в другом (без учета регистра).
Заменяет в текстовой строке старый текст новым.
Преобразует аргументы в текст.
Форматирует число и преобразует его в текст.
ОБЪЕДИНИТЬ
Объединяет текст из нескольких диапазонов или строк, вставляя между текстовыми значениями указанный разделитель. Если в качестве разделителя используется пустая текстовая строка, функция эффективно объединит диапазоны.
Удаляет из текста пробелы.
ЮНИСИМВ
Возвращает символ Юникод, на который ссылается заданное числовое значение.
UNICODE
Возвращает число (кодовую страницу), которая соответствует первому символу текста.
Преобразует все буквы текста в прописные.
Преобразует текстовый аргумент в число.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM. Подробнее об этих различиях.
Урок по информатике «Текстовые функции в Exсel»
Цель урока:
Задачи урока:
Тип урока: комбинированный
Обеспечение урока: ПК, MS Excel
1. Оргмомент: приветствие, объявление цели урока
2. Актуализация ЗУН учащихся (опрос учащихся)
– В чем отличие табличного процессора и электронных таблиц?
– Как записать формулу в Excel?
– Из чего состоит имя ячейки?
– Назовите 3 виды ссылок в Excel
3. Объяснение нового материала
– Рассмотрим основные текстовые функции:
1) ДЛСТР (текст) – подсчитывает количество символов в текстовой строке
Пример. Найти длину строки Иванова Анна
2) ЗАМЕНИТЬ (стар.текст; нач_ном; число_литер; нов.текст) – замена части текстовой строки на другую текстовую строку
Пример. Заменить Иванова Анна на Иванова Лена
3) ЗНАЧЕН (текст) – преобразование текстового аргумента в число
4) ЛЕВСИМВ (текст; количество символов) – возвращает указанное количество знаков с начала строки текста
Пример. Выделить фамилию Иванова
5) ПРАВСИМВ (текст; количество символов) – возвращает указанное количество знаков с конца текста
Пример. Выделить имя Анна
6) СЖПРОБЕЛЫ(текст) – удаление из текста лишних пробелов, за исключением одиночных пробелов между словами
7) СОВПАД(текст1; текст2) – проверяет идентичность двух строк текста и возвращает значение истина или ложь. Прописные и строчные буквы различаются.
8) СЦЕПИТЬ (текст1; текст2; …)– объединяет несколько текстовых строк в одну
9) НАЙТИ(искомый текст; просматриваемый текст; нач.позиция) – возвращает позицию начала искомой строки текста в содержащей ее строке текста
4. Закрепление материала (практическая работа на компьютере)
Дан список сотрудников – фамилия, имя. Используя текстовые функции, разбить по ячейкам фамилию и имя каждого сотрудника
Подсказка.
– Как выделить фамилию? Давайте рассуждать. Признаком того, что фамилия закончилась, служит пробел. Таким образом, слева нужно выделить количество символов до пробела.
1 шаг. Найти пробел – НАЙТИ(“ “;A1;1)
2 шаг. Выделить слева нужное количество символов – ЛЕВСИМВ(А1; …)
Итоговая формула будет иметь вид:
– Как выделить имя? Похожим способом, только справа. Также найдем позицию пробела, но теперь нужно из длины строки вычесть позицию пробела и получим количество символов в имени: ДЛСТР(А1)–НАЙТИ(“ ”; А1;1)
5. Подведение итогов урока (метод контроля, коррекции, обобщения)
6. Домашнее задание
– выучить формулы, рассмотренные на уроке
– придумать задание с использованием текстовых функций (должно быть не менее 3-х функций)
Использование текстовых функций электронных таблиц
Педагогические науки
Похожие материалы
Электронные таблицы представляют собой удобный инструмент для автоматизации вычислений. Они широко используются в различных областях, что обосновано универсальностью этого вида программного обеспечения.
Наиболее популярными направлениями применения электронных таблиц являются следующие:
Вместе с тем, электронные таблицы часто используются далеко не на полную мощность: это объясняется тем, что, изучив базовые возможности программы, пользователи решают на этом остановиться, даже не догадываясь о том, что область использования электронных таблиц намного шире.
В данной статье мы рассмотрим обработку текстовых данных с помощью электронных таблиц. Несмотря на то, что данное направление использования электронных таблиц не является популярным, с текстовыми данными пользователи сталкиваются часто, например, с ФИО, номерами банковских карт, адресами клиентов или сотрудников, комментариями и т.п. Все эти данные являются строками, поэтому полезно уметь обрабатывать информацию подобного типа в электронных таблицах. Для этого используются текстовые функции. Рассмотрим их на примере табличного процессора Excel, который является одним из наиболее популярных [7].
Текстовых функций в электронных таблицах Excel достаточно много [9], рассмотрим наиболее полезные и интересные: ДЛСТР, ЗАМЕНИТЬ, ЛЕВСИМВ, НАЙТИ, ПОДСТАВИТЬ, ПРАВСИМВ, ПРОПИСН, ПРОПНАЧ, ПСТР, СЖПРОБЕЛЫ, СОВПАД, СТРОЧН, СЦЕПИТЬ [6, 8].
Список всех текстовых функций доступен по команде Вставка>Вставка функции, далее нужно выбрать в поле Категория позицию Текстовые (рис. 1).
Рисунок 1. Диалоговое окно «Вставка функции»
Кратко охарактеризуем вышеуказанные текстовые функции.
Возвращает количество знаков в текстовой строке.
Определение аргумента: текст — это строка, длину которой следует определить (пробелы считаются знаками).
Заменяет часть строки текста на другую строку.
Синтаксис: =ЗАМЕНИТЬ(старый_текст; нач_позиция; число_знаков; новый_текст).
Возвращает указанное количество знаков с начала строки текста.
Синтаксис: =ЛЕВСИМВ(текст; количество_знаков).
Возвращает позицию начала искомой строки текста в содержащей её строке текста (прописные и строчные буквы различаются).
Синтаксис: =НАЙТИ(искомый_текст;просматриваемый_текст; нач_позиция).
Заменяет новым текстом старый текст в текстовой строке.
Синтаксис: =ПОДСТАВИТЬ(текст; стар_текст; нов_текст; номер_вхождения).
Замечание: функция «ПОДСТАВИТЬ» схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие — если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.
Возвращает указанное число знаков с конца строки текста.
Синтаксис: =ПРАВСИМВ(текст; количество_знаков).
Делает все буквы в строке текста прописными.
Определение аргумента: текст — строка, буквы которой требуется преобразовать в прописные.
Делает прописной первую букву в каждом слове текста, преобразуя все другие буквы в строчные.
Определение аргумента: текст — строка текста, в которой первая буква преобразуется в прописную.
Возвращает заданное число знаков из строки текста, начиная с указанной позиции.
Синтаксис: =ПСТР(текст; начальная_позиция; количество_знаков).
Удаляет из текста лишние пробелы (кроме одиночных пробелов между словами).
Определение аргумента: текст — это текст, из которого удаляются пробелы.
Проверяет идентичность двух строк текста и возвращает значение ИСТИНА или ЛОЖЬ (прописные и строчные буквы различаются).
Делает все буквы в строке текста строчными
Определение аргумента: текст — строка, буквы которой требуется преобразовать в строчные (знаки, не являющиеся буквами, не изменяются).
Объединяет несколько текстовых строк в одну.
Определения аргументов: текст1, текст2 — это текстовые строки, которые следует объединить в одну строку.
Приведем примеры задач на использование текстовых функций электронных таблиц.
Разделите в электронных таблицах с помощью текстовых функций фамилию, имя и отчество в разные столбцы, если они записаны в одной ячейке (рис. 2).
Рисунок 2. Исходные данные к задаче 1
1. Сформулируем словесный алгоритм:
2. Формализуем приведенный словесный алгоритм в электронных таблицах Excel:
3. Копированием формул на другие ячейки убеждаемся в правильности решения (рис. 3).
Рисунок 3. Результат решения задачи 1
1. Приведенное решение неединственное.
2. Задачу 1 можно усложнить, если задать условия:
Объедините в электронных таблицах с помощью текстовых функций фамилию, имя и отчество в одну ячейку, если они записаны в разных столбцах (рис. 4).
Рисунок 4. Исходные данные к задаче 2
1. Для объединения отдельных строк текста в одну ячейку используем функцию СЦЕП, учитывая необходимость добавления пробела между фамилией и именем, именем и отчеством; итоговая формула для получения ФИО: =СЦЕП(A2;» «;B2;» «;C2).
2. Копированием формул на другие ячейки убеждаемся в правильности решения (рис. 5).
Рисунок 5. Результат решения задачи 2
Используя слова «информация» и «оператор», получите в электронной таблице слова «информатор» и «операция».
1. Заполним электронную таблицу исходными данными (рис. 6).
Рисунок 6. Исходные данные к задаче 3
2. Для получения заданных слов будем использовать функцию ЗАМЕНИТЬ (рис. 7):
Рисунок 7. Решение задачи 3
3. В результате получим требуемые слова (рис. 8).
Рисунок 8. Результат решения задачи 3
Приведенное решение неединственное, например:
В заключении отметим, что рассмотренные задачи иллюстрируют лишь основы использования текстовых функций электронных таблиц [4, 5, 10]. В жизни встречается масса других задач с текстовыми данными, которые можно рационально решить с помощью текстовых функций в электронных таблицах.
Список литературы
Завершение формирования электронного архива по направлению «Науки о Земле и энергетика»
Создание электронного архива по направлению «Науки о Земле и энергетика»
Электронное периодическое издание зарегистрировано в Федеральной службе по надзору в сфере связи, информационных технологий и массовых коммуникаций (Роскомнадзор), свидетельство о регистрации СМИ — ЭЛ № ФС77-41429 от 23.07.2010 г.
Соучредители СМИ: Долганов А.А., Майоров Е.В.