ТЕКСТОВЫЕ ФУНКЦИИ
С помощью функций обработки текста можно с помощью формул производить действия над строками текста – например, изменить регистр или определить длину строки. Можно также объединить несколько строк в одну.
К примеру, с помощью функций СЕГОДНЯ и ТЕКСТ можно создать сообщение, содержащее текущую дату и привести его к виду "дд-ммм-гг":
="Балансовый отчет от "&ТЕКСТ(СЕГОДНЯ(),"дд-мм-гг")
Функция СЦЕПИТЬ объединяет несколько текстовых строк в одну.
Синтаксис: СЦЕПИТЬ (текст1 ; текст2;...).
В качестве аргументов могут быть указаны от 1 до 30 элементов текста, объединяемых в один элемент текста.
Вместо функции СЦЕПИТЬ для объединения текстов можно использовать оператор &.
Примеры: если в ячейке А5 содержится значение "Петренко", то функция СЦЕПИТЬ("студент ";А5) вернет результат "студент Петренко". Тот же результат будет получен, если использовать выражение = "студент "&А5.
Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение (т. е. номер символа) начала искомого текста относительно крайнего левого символа просматриваемого текста.
Синтаксис: НАЙТИ (искомый_текст; просматриваемый_текст ; нач_позиция). При этом нач_позиция – это позиция символа в просматриваемом тексте, с которой следует начинать поиск. Если этот аргумент опущен, то он полагается равным 1. Аргумент нач_позиция можно использовать, чтобы пропустить нужное количество символов с левого края текста.
Примеры: НАЙТИ("е"; "предприятие") вернет 3, а НАЙТИ("е"; "предприятие";6) вернет 11.
Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК , но в отличие от функции ПОИСК , функция НАЙТИ учитывает регистр и не допускает символов шаблона.
Синтаксис: ПОИСК (искомый_текст; текст_для_поиска ; нач_позиция).
В искомом тексте можно использовать символы шаблона: знак вопроса? (соответствует одному любому символу) и звездочка * (соответствует любой последовательности символов). Если искомый_текст не найден, то возвращается значение ошибки #ЗНАЧ! .
Пример: если ячейка B17 содержит слово "доход", а ячейка A14 ПОИСК($B$17;$A$14) равняется 7.
Функция ПОИСК часто используется совместно с функцией ЗАМЕНИТЬ, которая заменяет часть текстовой строки на другую текстовую строку.
Синтаксис: ЗАМЕНИТЬ (старый_текст; нач_ном; число_литер; новый_текст).
Старый_текст – это текст, в котором желательно заменить некоторые литеры. Нач_ном – это позиция символа в старом_тексте, начиная с которой происходит замена. Число_литер – это число символов в старом_тексте, которые заменяются новым_текстом. Новый_текст – это текст, который заменяет символы в тексте старый_текст.
Пример: следующая формула заменяет две последние цифры в тексте 1990 на 91: ЗАМЕНИТЬ("1990"; 3; 2; "91") равняется "1991".
Пример совместного использования функций ПОИСК и ЗАМЕНИТЬ : если ячейка B17 по-прежнему содержит слово "доход", а ячейка A14 содержит "Сумма Доходов", то ЗАМЕНИТЬ($A$14;ПОИСК($B$17;$A$14);7;"Расходов") вернет текст "Сумма Расходов". В данном примере функция ПОИСК использована для того, чтобы задать функции ЗАМЕНИТЬ корректное значение аргумента нач_позиция, с которой нужно вставить новый текст.
В том случае, если надо заменить определенный текст в строке на другой фрагмент текста вне зависимости от номера его начальной позиции, используется функция ПОДСТАВИТЬ. Функция подставляет фрагмент нов_текст вместо фрагмента стар_текст в указанной первой текстовой строке.
Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст ; номер_вхождения).
При этом номер_вхождения определяет, какое вхождение фрагмента стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то только это вхождение фрагмента стар_текст заменяется. В противном случае, каждое вхождение фрагмента стар_текст в текстовой строке заменяется на фрагмент нов_текст.
Пример: если в ячейке С3 содержится значение "Годовой отчет", то ПОДСТАВИТЬ(С3; "Годовой"; "Недельный") равняется "Недельный отчет".
Функция ПСТР (подстрока) позволяет выделить фрагмент текста указанной длины из строки, начиная с указанной позиции.
Синтаксис: ПСТР (текст; начальная_позиция; количество_символов).
Начальная_позиция указывает, с какой позиции надо извлечь фрагмент текста. Количество_символов указывает, сколько символов нужно вернуть.
Примеры: ПСТР("Расчетная ведомость";11;9) равняется "ведомость", а ПСТР("1234"; 5; 5) равняется "" (пустая строка).
Функцию ПСТР часто используют совместно с функцией НАЙТИ , чтобы определить позицию, начиная с которой надо выделять фрагмент текста.
Для подсчета количества символов в тексте существует функция ДЛСТР (длина строки).
Синтаксис: ДЛСТР (текст) .
Пробелы считаются как символы.
Примеры: ДЛСТР("Харьков – первая столица") возвращает значение 24, а ДЛСТР("") имеет значение 0.
Функция СОВПАД сравнивает две строки текста и возвращает значение ИСТИНА, если они в точности совпадают, и ЛОЖЬ – в противном случае. Функция СОВПАД учитывает регистр, но игнорирует различия в форматировании. Функция СОВПАД используется для того, чтобы проверить, входит ли некоторый текст в документ.
Синтаксис: СОВПАД (текст1; текст2) .
Для преобразования всех буквенных символов в строке из строчных в прописные или наоборот используются соответственно функции ПРОПИСН и СТРОЧН . Чтобы первая буква каждого слова стала прописной – функция ПРОПНАЧ .
Синтаксис: ПРОПИСН (текст) ,
СТРОЧН (текст), ПРОПНАЧ (текст).
Часто необходимо бывает преобразовать числовое значение в текст по заданному числовому формату. Для этого служит функция ТЕКСТ .
Синтаксис: ТЕКСТ (значение; формат) .
Формат должен быть указан в текстовой форме (с вкладки Число диалога Формат ячеек ). Формат не может содержать звездочку (*) и не может быть Общим числовым форматом.
Примеры: если в ячейках А6 и А7 соответственно содержатся значения 2,715 и "15.4.91", то ТЕКСТ (А6; "0,00 грн.") равняется "2,72 грн.", а результатом функции ТЕКСТ(А7; "Д МММ, ГГГГ") будет "15 Апр, 1991".
Замечание: форматирование ячейки меняет только формат, но не значение. Использование функции ТЕКСТ преобразует значение в форматированный текст, и результат больше не участвует в вычислениях как число.
Познакомимся с вариантами преобразования формулы Excel в текст
(в результате получается не значение ячейки, а формульное выражение в текстовом виде, например, "=A1+A2", "=СЕГОДНЯ()" и т.д.).
Возникают ситуации когда необходимо отобразить в ячейке не значение формульного выражения, а именно ее текстовую запись.
С помощью мы можем увидеть все формульные выражения листа и книги в текстовом виде, однако если мы хотим показать формулу не для всех, а для каких-то конкретных ячеек, то такой вариант не подходит.
Однако вне зависимости от целей преобразования нам понадобится способ извлечь из ячейки строку с формулой.
Начнем с более простого варианта, а именно предположим, что нам нужно преобразовать формулу в текст в самой ячейке (т.е. заменить значение на текстовую запись). Тогда в этом случае есть несколько способов преобразования:
Теперь перейдем к более общему случаю и рассмотрим 2 основных варианта перевода формулы в текст (т.е. получить текстовую запись):
Первый способ позволит нам перевести формулу в текст стандартными средствами в Excel, а во втором способе мы напишем пользовательскую функцию, которая будет выполнять аналогичные по функционалу преобразования (что и в первом способе) с небольшими видоизменениями.
Давайте подробнее остановимся на каждом из них.
Начиная с версии Excel 2013 для применения доступна функция Ф.ТЕКСТ (FORMULATEXT в английской версии):
Перейдем к примерам. Применим Ф.ТЕКСТ , в качестве аргумента укажем ссылку на произвольную ячейку, где содержится какое-либо формульное выражение:
При этом в зависимости от выбранного у вас параметра отображения стиля ссылок (A1
или R1C1
) формула автоматически будет подстраиваться под формат записи:
При работе с данной функцией есть несколько важных особенностей, на которые необходимо обратить внимание:
При использовании версии Excel 2010 или более ранней, стандартными инструментами Excel воспользоваться уже не получится.
Однако данную проблему мы можем решить с помощью создания пользовательской функции (UDF), которая позволит преобразовать формулу в текст в различных вариантах записи в зависимости от стиля ссылок.
Перейдем в редактор ( Alt + F11), вставляем новый модуль и добавляем следующий код:
Visual Basic
Public Function FText(myRange As Range) As String FText = myRange.FormulaLocal End Function
Public Function FText (myRange As Range ) As String FText = myRange . FormulaLocal End Function |
Как обычно, к новой функции мы можем обратиться либо через мастер функций (выбрав ее из категории Определенные пользователем ), либо ввести формульное выражение в пустую ячейку:
Как видим результат работы пользовательской функции FText
получился точно таким же, как и у стандартной Ф.ТЕКСТ
.
В данном примере мы использовали свойство диапазона FormulaLocal , которое позволяет преобразовать формульное выражение со стилем ссылок A1, однако в зависимости от предпочтений стиль записи можно изменить, а именно поменять свойство FormulaLocal на один из следующих вариантов:
Выбираем необходимый формат записи, корректируем код FText в VBA и на выходе получаем итоговое преобразование:
В строки программы Microsoft Office Exel можно заносить любую информацию, которая необходима пользователям для успешного выполнения поставленных задач. Чтобы работать наиболее продуктивно, необходимо уметь разбираться с данными в строках. В этом материале будут рассмотрены примеры текстовых функций в Excel.
Чтобы найти их в программе, проследуйте во вкладку под названием "Формулы", откройте список и выберите "Текстовые".
Данная функция дает пользователю возможность объединять между собой несколько строк. Максимально допустимое для соединения количество достигает 255. Обратите внимание на то, что проставлять пробелы здесь необходимо самостоятельно. В теле "СЦЕПИТЬ" должен содержаться минимум один аргумент. Рассмотрим пример ее записи и действия:
Альтернативой данной функции для работы с текстом является символ &, который также отвечает за объединение строк.
Она отвечает за возвращение из требуемой строки куска текста в указанном количестве символов. Строка начинается с отмеченного символа. Ключевые позиции (текст, начальная позиция, количество знаков). Каждый из них определяется следующим образом:
Как пример можно привести следующую ситуацию. В ячейке А1 находится некоторый текст. Нужно вернуть два последних слова, общая длина символов у них 12. Символ, который необходимо вернуть в конкретной фразе, числится под порядковым номером 12.
Выполняет замену готового текста на необходимый в том месте, которое будет указано пользователем. Чем-то похожа на функцию под названием "Заменить". Но о ней подробности будут представлены позже. Главное отличие данного варианта в том, что он автоматически вычисляет вхождения требуемой строки и выполняет замену.
Выглядит конкретная функция следующим образом: текст; старый текст; новый текст; номер вхождения.
Разберем каждый из аргументов:
Выполняет функцию замены части строки в конкретном количестве символов. Процесс начинается с символа, который указан по счету, на новый текст. Выглядит она следующим образом: старый текст; начальная позиция; количество знаков; новый текст.
Рассмотрим по отдельности каждый из аргументов данной текстовой функции в Excel:
Теперь рассмотрим пример использования текстовой функции в Excel. В ячейке А1 содержится строка со словом "старый". Оно начинается с 19-го символа и имеет длительность из шести идентичных единиц. Замена происходит на слово "новый".
Стоит отметить, что данная ситуация достаточно двоякая. С одной стороны, если пользователь будет работать с небольшим количеством ячеек, то данный вариант отлично сработает. Но как быть в ситуации, когда предстоит выполнить замену среди нескольких тысяч строк? В таком случае стоит обратиться к двум другим текстовым функциям Excel, которые будут рассмотрены чуть позже:
Данная текстовая функция в Excel помогает пользователю определять длину строки. Результатом ее работы будет возврат целого числа, демонстрирующего объем символов в тексте. Выглядит она так: =ДЛСТР(текст).
При работе с текстом выполняет функцию возврата числа, которое представляет собой вхождение первого символа находящегося в подстроке необходимого текста. В том случае, если данные не были обнаружены, вместо числа происходит возврат ошибки "#ЗНАЧ!".
Вот ее форма в документе. Но без конкретных аргументов: =НАЙТИ(искомый текст; текст для поиска; нач. позиция).
Теперь рассмотрим по отдельности все приведенные аргументы:
Все знают, что табличный процессор Microsoft Excel предназначен для обработки числовой информации. Его функционал позволяет обрабатывать большие объемы вычисляемых данных. А ведь с его помощью можно обработать и текст. Это могут быть фамилии, имена, отчества сотрудников, почтовые адреса и много другой текстовой информации, записанной в ячейки таблиц.
Различные сервисы интернета позволяют осуществлять импорт данных в формате электронных таблиц и не всегда эти данные удобны для дальнейшей обработки. А если требуется скорректировать текст на сотнях строк, то без текстовых функций не обойтись. В этой статье я предлагаю вам ознакомиться с текстовыми функциями в Excel и разобраться на примерах с их использованием.
Сначала рассмотрим основные текстовые функции, которые использованы в примерах ниже. Excel использует русские названия функций в отличие от своего ближайшего аналога LibreOffice.Calc. Ниже приведен формат записи текстовой функции и ее действие.
СЦЕПИТЬ(текст1;[текст2];…)
- соединяет несколько текстовых строк из разных ячеек в одну.
ЛЕВСИМВ(текст;[число_знаков])
- выводит первый символ или заданное число первых символов из текстовой строки.
НАЙТИ(искомый_текст;просматриваемый_текст;[нач_позиция]) -
находит позицию начала вхождения искомого текста в заданной текстовой строке с учетом регистра.
ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])
- тоже самое, как и Найти, но без учета регистра.
ПСТР(текст;начальная_позиция;число_знаков)
- выводит заданное число символов из строки текста, начиная с указанной позиции.
ДЛСТР(текст)
- считает количество знаков в текстовой строке.
Есть список сотрудников. Фамилии, имена и отчества разнесены в отдельные столбцы. Необходимо объединить данные в один столбец.
Решение. Задача достаточно простая и для ее реализации воспользуемся функцией СЦЕПИТЬ.
В ячейку D1 запишем формулу =СЦЕПИТЬ(A1;" ";B1;" ";C1). Можно воспользоваться мастером функций.
Если в функции указать только адреса ячеек, то текст склеится в одну длинную строку. Поэтому добавляем пробел в кавычках между адресами ячеек.
Посмотрите на рисунок ниже. Результат преобразования в столбце D.
Окно мастера функции СЦЕПИТЬПосле импорта данных сотрудников их фамилии, имена и отчества оказались в одном столбце. Необходимо разделить данные по столбцам.
Решение. Задача сложнее предыдущей и для ее реализации понадобится несколько текстовых функций.
Для отделения фамилии сотрудника и запишем в ячейку B1 формулу
=ЛЕВСИМВ(A1;НАЙТИ(" ";A1))
В этой формуле количество выводимых знаков подсчитывается функцией Найти , которая определяет местонахождение первого пробела в строке.
Строка формул при разделении ФИО Для записи имени в ячейку C1 запишем следующую формулу
=ПСТР(A1;НАЙТИ(" ";A1)+1;ПОИСК(" ";A1;НАЙТИ(" ";A1)+1)-НАЙТИ(" ";A1)-1)
Если посмотреть на синтаксис записи данной функции, то получаем:
Отчество получается в ячейке D1 по более сложной формуле
=ПСТР(A1;ПОИСК(" ";A1;НАЙТИ(" ";A1)+1)+1;ДЛСТР(A1)-ПОИСК(" ";A1;НАЙТИ(" ";A1)+1)+1)
Здесь количество знаков в отчестве определяется как разность общего количества символов (ДЛСТР) и позицией второго пробела.
В рассмотренных примерах функции ПОИСК и НАЙТИ выполняют одинаковые операции, так как разница в регистрах символов не учитывается. Возможно обойтись только одной из них.
В список сотрудников внести изменения. Записать в одном столбце Фамилии и инициалы.
Решение. В зависимости от исходного состояния списка возможны два варианта.
1 вариант. Исходные данные содержатся в одном столбце. ФИО разделены одинарным пробелом.
Записываем следующую формулу
=СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1));ПСТР(A1;НАЙТИ(" ";A1);2);".";ПСТР(A1;НАЙТИ(" ";A1; НАЙТИ(" ";A1)+1);2);".")
Преобразуем имя и отчество в инициалы (исходные данные в одном столбце) 2 вариант. Исходные данные содержатся в разных столбцах.
Формула для преобразования
=СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1);".";ЛЕВСИМВ(C1);".")
Преобразуем имя и отчество в инициалы (исходные данные в разных столбцах) Друзья, если вы можете привести свои примеры использования текстовых функций в Excel, расскажите о них в комментариях. Рекомендую посмотреть . Понравилась статья, поделитесь с друзьями. Кнопочки социальных сетей внизу.
Дорогой читатель! Вы посмотрели статью до конца.
Получили вы ответ на свой вопрос? Напишите в комментариях пару слов.
Если ответа не нашли, укажите что искали .
Excel необходим в случаях, когда вам нужно упорядочить, обработать и сохранить много информации. Он поможет автоматизировать вычисления, делает их проще и надежнее. Формулы в Excel позволяют проводить сколь угодно сложные вычисления и получать результаты моментально.
Прежде чем учиться этому, следует понять несколько базовых принципов.
Каждая ячейка в Excel является неделимой единицей с собственным идентификатором (адрес), который обозначается буквой (номер столбца) и цифрой (номер строки). Отображается адрес в поле над таблицей.
Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:
Сложение
- Вычитание
/ Деление
* Умножение
Если вам необходимо указать число, а не адрес ячейки – вводите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».
Ввод их всегда осуществляется после нажатия на «=». Но что делать, если однотипных расчетов много? В таком случае можно указать одну, а затем ее просто скопировать. Для этого следует ввести формулу, а затем «растянуть» ее в нужном направлении, чтобы размножить.
Установите указатель на копируемую ячейку и наведите указатель мыши на правый нижний угол (на квадратик). Он должен принять вид простого крестика с равными сторонами.
Нажмите левую кнопку и тяните.
Отпустите тогда, когда надо прекратить копирование. В этот момент появятся результаты вычислений.
Также можно растянуть и вправо.
Переведите указатель на соседнюю ячейку. Вы увидите такую же запись, но с другими адресами.
При копировании таким образом номера строки увеличиваются, если сдвиг происходит вниз, или увеличиваются номера столбцов – если вправо. Это называется относительная адресация.
Давайте введем в таблицу значение НДС и посчитаем цену с налогом.
Цена с НДС высчитывается как цена*(1+НДС). Введем последовательность в первую ячейку.
Попробуем скопировать запись.
Результат получился странный.
Проверим содержимое во второй ячейке.
Как видим, при копировании сместилась не только цена, но и НДС. А нам необходимо, чтобы эта ячейка оставалась фиксированной. Закрепим ее с помощью абсолютной ссылки. Для этого переведите указатель на первую ячейку и щелкните в строке формул на адрес B2.
Нажмите F4. Адрес будет разбавлен знаком «$». Это и есть признак абсолютно ячейки.
Теперь после копирования адрес B2 останется неизменным.
Если вы случайно ввели данные не в ту ячейку, просто перенесите их. Для этого наведите указатель мыши на любую границу, дождитесь, когда мышь станет похожа на крестик со стрелочками, нажмите левую кнопку и тяните. В нужном месте просто отпустите манипулятор.
Excel предлагает большое количество функций, которые разбиты по категориям. Посмотреть полный перечень можно, нажав на кнопку Fx около строки формул или открыв раздел «Формулы» на панели инструментов.
Расскажем о некоторых функциях.
Эта функция позволяет задавать условие и проводить расчет в зависимости от его истинности или ложности. Например, если количество проданного товара больше 4 пачек, следует закупить еще.
Чтобы вставить результат в зависимости от условия, добавим еще один столбец в таблицу.
В первой ячейке под заголовком этого столбца установим указатель и нажмем пункт «Логические» на панели инструментов. Выберем функцию «Если».
Как и при вставке любой функции, откроется окно для заполнения аргументов.
Укажем условие. Для этого необходимо щелкнуть в первую строку и выбрать первую ячейку «Продано». Далее поставим знак «>» и укажем число 4.
Во второй строке напишем «Закупить». Эта надпись будет появляться для тех товаров, которые были распроданы. Последнюю строку можно оставить пустой, так как у нас нет действий, если условие ложно.
Нажмите ОК и скопируйте запись для всего столбца.
Чтобы в ячейке не выводилось «ЛОЖЬ» снова откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строки формул. Вставьте курсор на третью строку и поставьте пробел в кавычках.
Затем ОК и снова скопируйте.
Теперь мы видим, какой товар следует закупить.
Эта функция позволяет применить формат к содержимому ячейки. При этом любой тип данных преобразуется в текст, а значит не может быть использован для дальнейших вычислений. Добавим столбец чтобы отформатировать итоговую сумму.
В первую ячейку введем функцию (кнопка «Текстовые» в разделе «Формулы»).
В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».
Нажмем ОК и скопируем.
Если попробовать использовать эту сумму в вычислениях, то получим сообщение об ошибке.
«ЗНАЧ» обозначает, что вычисления не могут быть произведены.
Примеры форматов вы можете видеть на скриншоте.
Excel предоставляет много возможностей по работе с датами. Одна из них, ДАТА, позволяет построить дату из трех чисел. Это удобно, если вы имеете три разных столбца – день, месяц, год.
Поставьте указатель на первую ячейку четвертого столбца и выберите функцию из списка «Дата и время».
Расставьте адреса ячеек соответствующим образом и нажмите ОК.
Скопируйте запись.
На случай, если необходимо сложить большое число данных, в Excel предусмотрена функция СУММ
. Для примера посчитаем сумму для проданных товаров.
Поставьте указатель в ячейку F12. В ней будет осуществляться подсчет итога.
Перейдите на панель «Формулы» и нажмите «Автосумма».
Excel автоматически выделит ближайший числовой диапазон.
Вы можете выделить другой диапазон. В данном примере Excel все сделал правильно. Нажмите ОК. Обратите внимание на содержимое ячейки. Функция СУММ подставилась автоматически.
При вставке диапазона указывается адрес первой ячейки, двоеточие и адрес последней ячейки. «:» означает «Взять все ячейки между первой и последней. Если вам надо перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)
Мы рассказали, как сделать формулу в Excel. Это те знания, которые могут пригодиться даже в быту. Вы можете вести свой личный бюджет и контролировать расходы.
На скриншоте показаны формулы, которые вводятся для подсчета сумм доходов и расходов, а также расчет баланса на конец месяца. Добавьте листы в книгу для каждого месяца, если не хотите, чтобы все таблицы располагались на одном. Для этого просто нажмите на «+» внизу окна.
Чтобы переименовать лист, два раза на нем щелкните и введите имя.
Таблицу можно сделать еще подробнее.
Excel – очень полезная программа, а вычисления в нем дают практически неограниченные возможности.
Отличного Вам дня!
