Как конвертировать формулу в текст в Excel

Как конвертировать формулу в текст в Excel?

Познакомимся с вариациями преобразования формулы Excel в текст (в итоге выходит не значение ячейки, а формульное выражение в текстовом виде, к примеру, "=A1+A2", "=СЕГОДНЯ()" и т.д.).

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

Преобразование формулы в текст в Excel

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

• Поменять формат ячейки на текстовый, а потом произвести вычисление формулы; При всем этом для каждой ячейки необходимо будет вручную создавать изменение.
• Добавить апостроф (знак «‘») перед знаком равно (знак «=») в формульном выражении. В данном варианте подставить апостроф можно как вручную, так и через подмену («=» на «‘=» при помощи инструмента «Отыскать и поменять»).

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

• Функция Ф.ТЕКСТ (доступна начиная с версии Excel 2013);
• Пользовательская функция (UDF).

1-ый метод дозволит нам перевести формулу в текст обычными средствами в Excel, а во 2-м методе мы напишем пользовательскую функцию, которая будет делать подобные по функционалу преобразования (что и в первом методе) с маленькими видоизменениями.
Давайте подробнее остановимся на любом из их.

Функция Ф.ТЕКСТ в Excel

Начиная с версии Excel 2013 для внедрения доступна функция Ф.ТЕКСТ (FORMULATEXT в британской версии):

Ф.ТЕКСТ(ссылка)
Возвращает формулу в виде строчки.

• Ссылка(неотклонимый аргумент) — ссылка на ячейку либо спектр ячеек.

Перейдем к примерам. Применим Ф.ТЕКСТ, в качестве аргумента укажем ссылку на произвольную ячейку, где содержится какое-либо формульное выражение:

При всем этом в зависимости от избранного у вас параметра отображения стиля ссылок (A1 либо R1C1) формула автоматом будет подстраиваться под формат записи:

Замечания

При работе с данной функцией есть несколько принципиальных особенностей, на которые нужно направить внимание:

• Аргумент "Ссылка" может ссылаться на остальные листы и книжки;
• Если аргумент "Ссылка" не содержит формульное выражение либо содержит ссылку на закрытую книжку, то в итоге будет возвращено значение ошибки.

Пользовательская функция (UDF)

При использовании версии Excel 2010 либо наиболее ранешней, обычными инструментами Excel пользоваться уже не получится.
Но данную делему мы можем решить при помощи сотворения пользовательской функции (UDF), которая дозволит конвертировать формулу в текст в разных вариантах записи в зависимости от стиля ссылок.

Перейдем в редактор Visual Basic (сочетание кнопок Alt + F11), вставляем новейший модуль и добавляем последующий код:

Текстовые функции Excel в примерах

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

СЦЕПИТЬ

Для объединения содержимого ячеек в Excel, вместе с оператором конкатенации, можно применять текстовую функцию СЦЕПИТЬ. Она поочередно соединяет воединыжды значения обозначенных ячеек в одной строке.

СТРОЧН

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

ПРОПИСН

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

ПРОПНАЧ

Текстовая функция ПРОПНАЧ делает строчный первую буковку всякого слова, а все другие конвертирует в строчные.

Любая 1-ая буковка, которая следует за знаком, хорошим от буковкы, также преобразуется в верхний регистр.

ДЛСТР

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

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают данное количество знаков, начиная с начала либо с конца строчки. Пробел считается за знак.

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

СОВПАД

Функция СОВПАД дозволяет сопоставить две текстовые строчки в Excel. Если они в точности совпадают, то ворачивается значение ИСТИНА, в неприятном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

Если регистр для Вас не играет большенный роли (так бывает в большинстве случаев), то можно применить формулу, просто проверяющую равенство 2-ух ячеек.

СЖПРОБЕЛЫ

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

В вариантах, когда наличие излишнего пробела в конце либо начале строчки трудно отследить, данная функция становится просто неподменной. На рисунке ниже видно, что содержимое ячеек А1 и B1 полностью идиентично, но это не так. В ячейке А1 мы преднамеренно поставили излишний пробел в конце слова Excel. В итоге функция СОВПАД вернула нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все излишние пробелы и получим корректный итог:

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

ПОВТОР

Функция ПОВТОР повторяет текстовую строчку обозначенное количество раз. Строчка задается как 1-ый аргумент функции, а количество повторов как 2-ой.

НАЙТИ

Текстовая функция НАЙТИ находит вхождение одной строчки в другую и возвращает положение первого знака разыскиваемой фразы относительно начала текста.

Данная функция чувствительна к регистру…

… и может начинать просмотр текста с обозначенной позиции. На рисунке ниже формула начинает просмотр с 4-ого знака, т.е. c буковкы “r“. Но даже в этом случае положение знака считается относительно начала просматриваемого текста.

ПОИСК

Текстовая функция ПОИСК весьма похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

ПОДСТАВИТЬ

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

Приведенная ниже формула подменяет все вхождения слова “Excel” на “Word”:

Подменяет лишь 1-ое вхождение слова “Excel”:

Удаляет все пробелы из текстовой строчки:

ЗАМЕНИТЬ

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

Формула в примере ниже подменяет 4 знака, расположенные, начиная с седьмой позиции, на значение “2013”. Применительно к нашему примеру, формула поменяет “2010” на “2013”.

Подменяет 1-ые 5 знаков текстовой строчки, т.е. слово “Excel”, на “Word”.

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и поглядели их действие на обычных примерах. Надеюсь, что данный урок пришелся Для вас очень кстати, и Вы получили от него хотя бы малость полезной инфы. Всего хорошего и фурроров в исследовании Excel!

Функции для работы с текстом в Excel

Для удобства работы с текстом в Excel есть текстовые функции. Они упрощают обработку сходу сотен строк. Разглядим некие из их на примерах.

Примеры функции ТЕКСТ в Excel

Конвертирует числа в текст. Синтаксис: значение (числовое либо ссылка на ячейку с формулой, дающей в итоге число); формат (для отображения числа в виде текста).

Самая нужная возможность функции ТЕКСТ – форматирование числовых данных для объединения с текстовыми данными. Без использования функции Excel «не осознает», как демонстрировать числа, и конвертирует их в базисный формат.

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

Внедрение амперсанда без функции ТЕКСТ дает «неадекватный» итог:

Excel возвратил порядковый номер для даты и общий формат заместо валютного. Чтоб избежать подобного результата, применяется функция ТЕКСТ. Она форматирует значения по заданию юзера.

Формула «для даты» сейчас смотрится так:

2-ой аргумент функции – формат. Где брать строчку формата? Щелкаем правой клавишей мыши по ячейке со значением. Жмем «Формат ячеек». В открывшемся окне избираем «все форматы». Копируем подходящий в строке «Тип». Вставляем скопированное значение в формулу.

Приведем еще пример, где быть может полезна данная функция. Добавим нули в начале числа. Если ввести вручную, Excel их удалит. Потому введем формулу:

Если необходимо возвратить прежние числовые значения (без нулей), то используем оператор «—»:

Направьте внимание, что значения сейчас показываются в числовом формате.

Функция разделения текста в Excel

Отдельные текстовые функции и их композиции разрешают распределить слова из одной ячейки в отдельные ячейки:

• ЛЕВСИМВ (текст; кол-во символов) – показывает данное число символов с начала ячейки;
• ПРАВСИМВ (текст; кол-во символов) – возвращает данное количество символов с конца ячейки;
• ПОИСК (разыскиваемый текст; спектр для поиска; исходная позиция) – указывает позицию первого возникновения искомого знака либо строчки во время просмотра слева вправо

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

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

В первой строке есть лишь имя и фамилия, разбитые пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества символов – употребляется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

При помощи функции ПОИСК Excel описывает количество символов для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Потом отнимается количество символов до первого пробела (отысканное ПОИСКом).

2-ая строчка содержит имя, отчество и фамилию. Для имени используем такую же формулу:

Формула для извлечения фамилии несколько другая: Это 5 символов справа. Вложенные функции ПОИСК отыскивают 2-ой и 3-ий пробелы в строке. ПОИСК(» «;A3;1) находит 1-ый пробел слева (перед отчеством). К отысканному результату добавляем единицу (+1). Получаем ту позицию, с которой будем находить 2-ой пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит 2-ой пробел. Это будет конечная позиция отчества.

Дальше из общей длины строчки отнимается количество символов с начала строчки до второго пробела. Итог – число знаков справа, которые необходимо возвратить.

Формула «для отчества» строится по этим же принципам:

Функция объединения текста в Excel

Для объединения значений из нескольких ячеек в одну строчку употребляется оператор амперсанд (&) либо функция СЦЕПИТЬ.

К примеру, значения размещены в различных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Избираем первую ячейку с текстом и жмем на клавиатуре &. Потом – символ пробела, заключенный в кавычки (“ “). Опять — &. И так поочередно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

Внедрение функции СЦЕПИТЬ:

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

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает исходную позицию искомого текста (без учета регистра). К примеру:

Функция ПОИСК возвратила позицию 10, т.к. слово «Захар» начинается с десятого знака в строке. Где это может понадобиться?

Функция ПОИСК описывает положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Или можно поменять отысканный текст средством функции ЗАМЕНИТЬ.