Как из формулы сделать текст в excel?

14 Янв

Добрый день, читатели блога! Сегодня мы поговорим о том как при необходимости отобразить формулу в ячейке как текст. Ситуация нестандартная, но иногда может пригодиться, особенно для проверки вводимых функций.

Для пользователя Excel всегда встаёт вопрос: как же узнать где в ячейке есть формула, а где нет? 

Пойдём по порядку и опустим случай, когда мы щёлкаем на ячейку и смотрим в строчку формул, а также используем кнопку «Показать формулы». 

Способ №1

Определить есть ли формула в ячейке нам поможет функция =ЕФОРМУЛА.

Результатом вычислений будет два значения: ИСТИНА — формула в ячейке есть; ЛОЖЬ — формулы в ячейке нет.

Ячейки с формулами найдены. Преобразовать формулу в текст поможет функция =Ф.ТЕКСТ. Она возвращает формулу в виде строки. Есть и минус: её можно использовать только с версии Excel 2013 и выше.

Способ №2

Воспользуемся гибкими возможностями именованных диапазонов в Excel. Переходим на вкладку «Формулы», в блоке кнопок «Определенные имена» нажимаем «Диспетчер имён».

Щёлкаем «Создать», далее вводим имя диапазона (например, ПОЛУЧИТЬФОРМУЛУ), область оставим «Книга», поле «Примечание» можно оставить пустым, в поле «Формула» напишем =ПОЛУЧИТЬ.ЯЧЕЙКУ(6;Лист2!C1). В примере формула находится в ячейке С1, у вас она может быть где угодно, но важно помнить одно — такой способ работает только в ячейке правее ячейки с формулой!

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

Как только оно появилось сразу щелкаем по нему два раза.

Подтверждаем ввод. Видим результат.

Способ №3

Воспользуемся созданием пользовательской функции на VBA.

Добавим новый модуль (как это сделать можно прочитать ЗДЕСЬ):

1. Вкладка «Разработчик», блок кнопок «Код», кнопка «Visual Basic»;
2. Далее «Insert» — > «Module».

Вставляем код:

Function FFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)
‘Application.Volatile True
If ShowFormula Then
If Cell.HasFormula Then
FFormula = «Текст формулы: » & IIf(Cell.HasArray, «{» & Cell.FormulaLocal & «}», Cell.FormulaLocal)
Else
FFormula = «Значение ячейки: » & Cell.Value
End If
Else
FFormula = Cell.HasFormula
End If
End Function

Разбор функции будет в видео ниже. Теперь нам остаётся ввести созданную функцию. Обязательно ввести через точку с запятой «Истина», чтобы активировать возможность показа формулы переменной «ShowFormula«.

Подтверждаем ввод, смотрим на результат.

• Если вы знаете ещё варианты решения этой задачи — оставляйте комментарии под статьёй!
• Видео:

Источник: http://pcandlife.ru/otobrazhenie-teksta-formul/

7 формул для работы с текстами объявлений в Excel

Сколько программ и сервисов вы используете при работе над текстом объявлений для контекстной рекламы? Хотелось бы, чтобы одним из первых вы назвали MS Excel, поскольку он помогает справляться со многими задачами. Не зря Яндекс.Директ и Google AdWords позволяют загружать кампании с помощью xls- и csv-файлов. В этом материале мы разберем семь полезных формул для работы с текстом и ключевыми фразами в Excel.

1. Функция ДЛСТР — Подсчет символов в ячейке

Эта формула может быть знакома многим, но существует полезный лайфхак. В строке ввода для соседней ячейки прописываем: = ДЛСТР(А1), где ДЛСТР – функция, (А1) – положение ячейки, взятое в скобки. Так вы посчитаете количество знаков в строке.

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

1. сначала выделить столбец с цифрами,
2. затем на главной панели меню выбрать Условное форматирование -> Правила выделения ячеек -> Больше,

1. в появившемся окне ввести максимальное количество символов,
2. выбрать необходимый параметр выделения.

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

2. Функция СЖПРОБЕЛЫ— Удаление лишних пробелов

При копировании большого массива текста в нем могут появиться лишние пробелы (до, после или между словами), что затруднит дальнейшую работу. Функция СЖПРОБЕЛЫ позволяет избавиться от всех лишних пробелов, кроме одинарных, в заданных ячейках. Вводим формулу = СЖПРОБЕЛЫ(А1), где (А1) – ячейка к изменению, и растягиваем на необходимое количество ячеек.

3. Формула СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))) — Преобразует первое слово ячейки с прописной буквы

Чтобы преобразовать имеющийся ключевую фразу в заголовок или текст объявления без привлечения сторонних сервисов, применяем эту формулу: =СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))), где А1 – необходимая ячейка.

Эта формула, как и предыдущая, работает не «поверх» ячеек с изменяемыми данными, а в ячейке ввода формулы.

Совет: теперь, когда вы преобразовали текст, фактически ваши ячейки содержат формулы, а не сам текст. Когда вы будете копировать содержимое этих ячеек для дальнейшей работы, скопируются формулы, а не текст. Чтобы в ячейках оказался измененный текст, нужно:

1. выделить все измененные ячейки,
2. нажать сочетание клавиш Ctrl+C;
3. затем, не переходя на другие ячейки, нажать сочетание клавиш Ctrl+V;
4. в выпадающем меню Параметров вставки выбрать пункт «Только значения».

Все, теперь ячейки содержат только текстовые значения!

4. Формула СЦЕПИТЬ(А1;“ ”;А2 ;“ ”;А3;…) — Объединяет 2 и более ячеек в одну с добавлением пробела между ними

• Разберем формулу подробней:
• =СЦЕПИТЬ(А1;» «;А2 ;» «;А3;…), где А1, А2, А3 – ячейки к объединению,
• “ ” – пробелы, которые проставятся между содержимым ячеек.
• Если сочетание символов “ ” не применить, ячейки «склеятся» без пробела.

5. Применение функций СЦЕПИТЬ, ЕСЛИ и ДЛСТР — Добавляет продающее окончание

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

Сначала нужно определиться с окончанием. Например, для разного допустимого количества символов можно взять «Купить со скидкой 30%!», «Скидка 30%!», «!».

Надо помнить, что после содержания первой ячейки нужно добавить знак препинания, а перед продающим окончанием – пробел. Т.е. продающие окончания должны иметь вид: «.

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

В зависимости от ограничения по символам (например, в заголовке рекламного объявления в Директе максимальное количество знаков — 33), считаем, какое допустимое количество символов должна содержать ячейка, к которой добавляем окончание. Проще говоря, из 33 вычитаем получившееся количество символов продающих окончаний.

Далее вставляем формулу =ЕСЛИ(ДЛСТР(A2)

Источник: https://ppc.world/articles/7-formul-dlya-raboty-s-tekstami-obyavlenij-v-excel/

Текстовые функции в Excel. Часть №3

       Добрый день уважаемый читатель!

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

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

Для этих целей я и отобрал 21 текстовую функцию, чтобы разобрать по отдельности каждую из них.

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

      Для тех, кто не читал первую и вторую части, я повторюсь где можно отыскать весь набор функций. Перейдите на панели инструментов во вкладку «Формулы» и нажмите иконку «Текстовые», в выпадающем меню выберете функцию которая вам нужна, это будет быстрый способ.

Вторым вариантом будет в этой же вкладке нажать иконку «Вставить функцию» и в новом диалоговом окне в категории выбираете «Текстовые», но данный способ я считаю лучшим так как снизу окна идет описание выбранной функции что очень полезно.

Часть №3″ class=»aligncenter size-full wp-image-1895″ src=»http://topexcel.ru/wp-content/uploads/2017/08/Tekstovie_function_part3_2.jpg» width=»476″ srcset=»http://topexcel.ru/wp-content/uploads/2017/08/Tekstovie_function_part3_2.jpg 476w, http://topexcel.ru/wp-content/uploads/2017/08/Tekstovie_function_part3_2-283×300.jpg 283w» sizes=»(max-width: 476px) 100vw, 476px»>

Функция Т

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

      Синтаксис функции:

            = Т(_значение_), где:

• значение – ссылка на значение которое необходимо проверить.

      Пример применения:

Функция ПЕЧСИМВ

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

      Синтаксис функции:

           = ПЕЧСИМВ(_текст_), где:

• текст – указание на ячейку в которой необходимо стереть непечатаемые символы.

      Пример применения:

Функция РУБЛЬ

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

       Синтаксис функции:

            = РУБЛЬ(_число_, _число_знаков_), где:

• число – это ссылка на ячейку, которая содержит число, формула или же любое число, требующее преобразований;
• число знаков – указывается сколько цифр после запятой вам нужно. Отрицательное значение делает округление слева от запятой, а положительное – справа. Если аргумент не указан, то значение по умолчанию 2 знака.

       Пример применения:       Стоить помнить, что форматирование ячейки с помощью функции РУБЛЬ, в отличие от стандартного форматирование через контекстное меню, превращает свой результат в текст, в отличие от других способов. Но, в принципе, использовать в формулах результат функции возможно, так как MS Excel имеет возможность превращать числа которые вносятся как текст, в процессе вычисления в числовые значения.

Функция ПОИСК

•      Она очень похожа на ранее рассматриваемую функцию НАЙТИ, но отличие заключается в том у этой функции нет чувствительности к регистру символов, поэтому поиск можно сделать более гибким.
•       Синтаксис функции:
•            = ПОИСК(_искомый_текст_; _текст_для_начала_поиска_;[_начальная_позиция_]), где:

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

       Пример применения:

Функция ТЕКСТ

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

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

      Синтаксис функции:

           = ТЕКСТ(_значение_;_формат_), где:

• значение – указывается числовое значение или указывается ссылка на него;
• формат – вводится формат который будет применен к аргументу «Значение».

      Пример применения:

Функция СЦЕПИТЬ

       При помощи этой функции вы можете соединять текстовые значения с разных ячеек между собой. Для объединения вам доступно 255 значений, что позволит вам создать чуть ли не маленький рассказ.

        Синтаксис функции:

             = СЦЕПИТЬ(_текст№1_;_ [текст№2]_;….), где:

• текст№1 – являет собой первый элемент который нужно сцепить с другими, обязательный аргумент;
• текст№2 – не является обязательным аргументом. Предоставляет 255 значений, как элементы слияния.

       Пример применения:        Обращаю ваше внимание что функция не умеет ставить пробелы между аргументами, поэтому вы сделаете это самостоятельно. Так же при желании можно сцеплять текст с помощью знака амперсанда «&» не используя функцию. Более детально о возможностях вы прочтете в статье «Как используется функция СЦЕПИТЬ в Excel».

Функция ФИКСИРОВАННЫЙ

1.      Функция производит округление к указанному количеству десятичных знаков, производит форматирование в десятичном формате и возвращает полученный результат как текст.

2.        Синтаксис функции:
3.             = ФИКСИРОВАННЫЙ(_число_;_число_знаков_;_без_разделителей_), где:

• число – это ссылка на числовое значение или число, которое будет округлено и превращено в текст;
• число знаков – указываем количество цифр после запятой;
• без разделителей – этот аргумент является логическим значением и если он указан как ИСТИНА, то функция не будет включать разделители тысяч в текст который возвращается.

       Пример применения:       Обращаю ваше внимание что для аргумента «Число знаков» есть возможность указать до 127 значащих цифр, также если аргумент отрицательный, число будет округлено до десятичного знака, а в случае отсутствия аргумента, по умолчанию его значение будет равно 2. Если для аргумента «Без разделителей» указана ЛОЖЬ или он отсутствует, разделители тысяч будут включены. Также напоминаю, что отформатированное число функцией ФИКСИРОВАННЫЙ будет переделано в текст.

        Ну вот и описаны все запланированные текстовые функции в Excel. Я надеюсь подборка в 21 функцию вам понравилась и стала полезной. Не забудьте также посмотреть часть 1 и часть 2 этой статьи.

       А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!

       Не забудьте поблагодарить автора!

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/tekstovye-funkcii-v-excel-chast-3/

Как преобразовать формулу в текст в Excel?

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

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

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

Однако вне зависимости от целей преобразования нам понадобится способ извлечь из ячейки строку с формулой.

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

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

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

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

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

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

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

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

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

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

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

Замечания

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

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

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

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

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

Public Function FText(myRange As Range) As String
FText = myRange.FormulaLocal
End Function

Public Function FText(myRange As Range) As String    FText = myRange.FormulaLocal

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

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

В данном примере мы использовали свойство диапазона FormulaLocal, которое позволяет преобразовать формульное выражение со стилем ссылок A1, однако в зависимости от предпочтений стиль записи можно изменить, а именно поменять свойство FormulaLocal на один из следующих вариантов:

• Formula — формат A1 (англоязычная формула);
• FormulaR1C1 — формат R1C1 (англоязычная);
• FormulaLocal — формат A1 (неанглоязычная/местная);
• FormulaR1C1Local — формат R1C1 (неанглоязычная/местная).

Выбираем необходимый формат записи, корректируем код FText в VBA и на выходе получаем итоговое преобразование:

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Источник: https://tutorexcel.ru/funktsii/kak-preobrazovat-formulu-v-tekst-v-excel/

Формула в MS EXCEL отображается как Текстовая строка

Бывает, что введя формулу и нажав клавишу ENTER пользователь видит в ячейке не результат вычисления формулы, а саму формулу. Причина — Текстовый формат ячейки. Покажем как в этом случае заставить формулу работать.

Итак, по какой-то причине пользователь ввел формулу в ячейку с Тектовым форматом.

Заставить EXCEL интерпретировать введенное в ячейку выражение как формулу, а не как текст — очень просто: нажмите клавишу F2, затем ENTER (ВВОД).

Ниже приведено более подробное объяснение:

• выделите ячейку с формулой (на рисунке это ячейка А1);
• формат ячейки установите Общий (нажав CTRL+1 и выбрав соответствующий Числовой формат или через меню );
• нажмите клавишу F2 (т.е. войдите в Режим Правки ячейки) или поставьте курсор в Строку формул;
• нажмите ENTER.

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

Если на листе десятки разных формул в таком формате (скорее всего они были импортированы в EXCEL из другого приложения), то скорее всего придется бороться с каждой формулой индивидуально.

Однако, если перед формулами стоит  апостроф ('), то существует подход, который позволяет быстро преобразовать значение ячейки в формулу.  Апостроф сообщает EXCEL что ячейка д.б. текстовой не зависимо от того какой фактически установлен Формат ячеек. В Строке формул значения такой ячейки выглядят, например, как '=C15.

Формально слевастоящий апостроф не является символом и инструмент Найти/Заменить не сработает, но сработает Текст-по-столбцам:

• Выделите столбец с формулами (формулы м.б. разными)
• Вызовите Текст-по-столбцам
• После вызова этого инструмента во втором окне укажите в качестве разделителя апостроф '
• Нажмите Готово

Все апострофы удалятся и формулы заработают. Но, проблемой может быть, если Ваши формулы содержат апострофы.

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

Источник: https://excel2.ru/articles/formula-v-ms-excel-otobrazhaetsya-kak-tekstovaya-stroka