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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример (стиль A1)

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

Пример (стиль R1C1)

Замечания

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

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

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

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

Интересно почитать:  Excel как вставить изображение в ячейку

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

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

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

СЦЕПИТЬ

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

Текстовые функции в Excel

СТРОЧН

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

Текстовые функции в Excel

ПРОПИСН

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

Текстовые функции в Excel

ПРОПНАЧ

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

Текстовые функции в Excel

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

Текстовые функции в Excel

ДЛСТР

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

Текстовые функции в Excel

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

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

Текстовые функции в Excel

Текстовые функции в Excel

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

Текстовые функции в Excel

СОВПАД

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

Текстовые функции в Excel

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

Текстовые функции в Excel

СЖПРОБЕЛЫ

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

Текстовые функции в Excel

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

Текстовые функции в Excel

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

Текстовые функции в Excel

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

ПОВТОР

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

Текстовые функции в Excel

НАЙТИ

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

Интересно почитать:  Как в excel объединить несколько ячеек в одну

Текстовые функции в Excel

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

Текстовые функции в Excel

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

Текстовые функции в Excel

ПОИСК

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

Текстовые функции в Excel

Текстовые функции в Excel

ПОДСТАВИТЬ

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

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

Текстовые функции в Excel

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

Текстовые функции в Excel

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

Текстовые функции в Excel

ЗАМЕНИТЬ

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

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

Текстовые функции в Excel

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

Текстовые функции в Excel

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

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

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

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

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

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

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

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

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

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

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

Формат ячеек.

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

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

Интересно почитать:  Как в excel в ячейку вставить таблицу

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

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

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

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

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

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

ФИО.

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

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

ПРАВСИМВ.

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

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

ЛЕВСИМВ.

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

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

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

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

ПСТР.

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию
Adblock
detector