Excel как вставить формулы в виде текста

Бывает, что введя формулу и нажав клавишу

ENTER

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

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

Заставить EXCEL интерпретировать введенное в ячейку выражение как формулу, а не как текст — очень просто: нажмите клавишу

F2

, затем

ENTER (ВВОД)

.

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

• выделите ячейку с формулой (на рисунке это ячейка
  
  А1
  
  );
• формат ячейки установите
  
  Общий
  
  (нажав
  
  CTRL+1
  
  и выбрав соответствующий Числовой формат или через меню

  );

• нажмите клавишу
  
  F2
  
  (т.е. войдите в
  
  Режим Правки
  
  ячейки) или поставьте курсор в

  Строку формул

  ;

• нажмите
  
  ENTER
  
  .

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

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

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

Строке формул

значения такой ячейки выглядят, например, как ‘=C15.

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

Текст-по-столбцам

:

• Выделите столбец с формулами (формулы м.б. разными)
• Вызовите

  Текст-по-столбцам

• После вызова этого инструмента во втором окне укажите в качестве разделителя апостроф ‘
• Нажмите Готово

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

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

Процедура вставки текста около формулы

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

Способ 1: использование амперсанда

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

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

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



2. Сразу после формулы ставим знак амперсанд (&). Далее в кавычках записываем слово «рублей». При этом кавычки не будут отображаться в ячейке после числа выводимого формулой. Они просто служат указателем для программы, что это текст. Для того, чтобы вывести результат в ячейку, щелкаем по кнопке Enter на клавиатуре.



3. Как видим, после этого действия, вслед за числом, которое выводит формула, находится пояснительная надпись «рублей». Но у этого варианта есть один видимый недостаток: число и текстовое пояснение слились воедино без пробела.
   

   При этом, если мы попытаемся поставить пробел вручную, то это ничего не даст. Как только будет нажата кнопка Enter, результат снова «склеится».



4. Но из сложившейся ситуации все-таки существует выход. Снова активируем ячейку, которая содержит формульное и текстовое выражения. Сразу после амперсанда открываем кавычки, затем устанавливаем пробел, кликнув по соответствующей клавише на клавиатуре, и закрываем кавычки. После этого снова ставим знак амперсанда (&). Затем щелкаем по клавише Enter.



5. Как видим, теперь результат вычисления формулы и текстовое выражение разделены пробелом.

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

При написании текста перед формулой придерживаемся следующего синтаксиса. Сразу после знака «=» открываем кавычки и записываем текст. После этого закрываем кавычки. Ставим знак амперсанда. Затем, в случае если нужно внести пробел, открываем кавычки, ставим пробел и закрываем кавычки. Щелкаем по клавише Enter.

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

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

Способ 2: применение функции СЦЕПИТЬ

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

=СЦЕПИТЬ(текст1;текст2;…)

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

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

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



2. Производится активация Мастера функций. Перемещаемся в категорию «Текстовые». Далее выделяем наименование «СЦЕПИТЬ» и жмем на кнопку «OK».



3. Запускается окошко аргументов оператора СЦЕПИТЬ. Данное окно состоит из полей под наименованием «Текст». Их количество достигает 255, но для нашего примера понадобится всего три поля. В первом мы разместим текст, во втором – ссылку на ячейку, в которой содержится формула, и в третьем опять разместим текст.

   Устанавливаем курсор в поле «Текст1». Вписываем туда слово «Итого». Писать текстовые выражения можно без кавычек, так как программа проставит их сама.

   Потом переходим в поле «Текст2». Устанавливаем туда курсор. Нам нужно тут указать то значение, которое выводит формула, а значит, следует дать ссылку на ячейку, её содержащую. Это можно сделать, просто вписав адрес вручную, но лучше установить курсор в поле и кликнуть по ячейке, содержащей формулу на листе. Адрес отобразится в окошке аргументов автоматически.

   В поле «Текст3» вписываем слово «рублей».

   После этого щелкаем по кнопке «OK».



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



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

   " ";

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

   =СЦЕПИТЬ("Итого";" ";D2;" ";"рублей")

   Щелкаем по клавише ENTER. Теперь наши значения разделены пробелами.



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



7. После этого, как видим, ненужный нам столбец скрыт, но при этом данные в ячейке, в которой расположена функция СЦЕПИТЬ отображаются корректно.

Читайте также: Функция СЦЕПИТЬ в Экселе
Как скрыть столбцы в Экселе

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вставка текста в ячейку с формулой в Microsoft Excel

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

Процедура вставки текста около формулы

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

Способ 1: использование амперсанда

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

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

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

Сразу после формулы ставим знак амперсанд (&). Далее в кавычках записываем слово «рублей». При этом кавычки не будут отображаться в ячейке после числа выводимого формулой. Они просто служат указателем для программы, что это текст. Для того, чтобы вывести результат в ячейку, щелкаем по кнопке Enter на клавиатуре.

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

При этом, если мы попытаемся поставить пробел вручную, то это ничего не даст. Как только будет нажата кнопка Enter, результат снова «склеится».

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

При написании текста перед формулой придерживаемся следующего синтаксиса. Сразу после знака «=» открываем кавычки и записываем текст. После этого закрываем кавычки. Ставим знак амперсанда. Затем, в случае если нужно внести пробел, открываем кавычки, ставим пробел и закрываем кавычки. Щелкаем по клавише Enter.

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

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

Способ 2: применение функции СЦЕПИТЬ

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

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

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

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

Производится активация Мастера функций. Перемещаемся в категорию «Текстовые». Далее выделяем наименование «СЦЕПИТЬ» и жмем на кнопку «OK».

Запускается окошко аргументов оператора СЦЕПИТЬ. Данное окно состоит из полей под наименованием «Текст». Их количество достигает 255, но для нашего примера понадобится всего три поля. В первом мы разместим текст, во втором – ссылку на ячейку, в которой содержится формула, и в третьем опять разместим текст.

Устанавливаем курсор в поле «Текст1». Вписываем туда слово «Итого». Писать текстовые выражения можно без кавычек, так как программа проставит их сама.

Потом переходим в поле «Текст2». Устанавливаем туда курсор. Нам нужно тут указать то значение, которое выводит формула, а значит, следует дать ссылку на ячейку, её содержащую. Это можно сделать, просто вписав адрес вручную, но лучше установить курсор в поле и кликнуть по ячейке, содержащей формулу на листе. Адрес отобразится в окошке аргументов автоматически.

В поле «Текст3» вписываем слово «рублей».

После этого щелкаем по кнопке «OK».

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

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

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

=СЦЕПИТЬ(«Итого»;» «;D2;» «;»рублей»)

Щелкаем по клавише ENTER. Теперь наши значения разделены пробелами.

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Использование текста в формулах

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

Мы часто слышим, что использование текста в формулах (например, такого: «продано 2347 единиц товара») облегчает понимание данных. Чтобы включить текст в функции и формулы, заключите его в кавычки («»). Кавычки указывают, что введенная фраза является текстом. Под текстом подразумевается любой символ, в том числе цифры, пробелы и знаки пунктуации. Пример:

= A2 &» продал «& B2 &» единицы.»

Для этого примера предположим, что ячейки в столбце A содержат названия, а ячейки в столбце B — количество продаж. Результат будет приблизительно такой: Климов продал 234 единицы.

Для объединения значений в столбцах А и В с текстом в формуле используются амперсанды (&). Также обратите внимание, что ячейка В2 не заключена в кавычки. Они окружают текст, который идет до и после ячейки.

Вот еще один пример распространенные задачи, добавив даты на лист. Функции текст и сегодня используется для составления фразы, например «Сегодня — Пятница, январь 20.»

Давайте посмотрим, как выполняется данный из внутри и снаружи. Функция TODAY вычисляет текущую дату, но отображается номер, например 40679. Функция текст нажмите преобразует число в удобочитаемым дату сначала задание числа в текст, а затем указав «дддд, мммм дд» для управления как дата отображается—«Пятница, 20 янв.»

Убедитесь, что формат даты «дддд, дд мммм» заключен в кавычки, и обратите внимание на то, как в формате используются запятые и пробелы. Обычно запятые используются в формулах для отделения аргументов — частей данных, необходимых для выполнения формулы. Но если запятые рассматриваются как текст, их можно использовать по необходимости.

И наконец, в формуле используется & для объединения форматированной даты со словами «Сегодня ». И, да-да, после слова «сегодня» необходимо поставить пробел.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Объединение текста и чисел

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

Использование числового формата для отображения текста до или после числа в ячейке

Если столбец, который нужно отсортировать, включает в себя числа и текст, например #15 продуктов, #100 продуктов, #200 товаров — она может не отсортировать так, как ожидалось. Ячейки, содержащие 15, 100 и 200, можно форматировать так, чтобы они отображались на листе в виде #15 продуктов, #100 продуктов и #200 продуктов.

Использование настраиваемого числового формата для отображения числа с текстом без изменения режима сортировки номера. Таким образом, вы измените способ отображения номера без изменения значения.

Выполните указанные ниже действия:

Выделите ячейки, которые нужно отформатировать.

На вкладке Главная в группе число щелкните стрелку.

В списке Категория выберите категорию, например «Настраиваемая «, а затем — встроенный формат, похожий на нужный.

В поле Type (тип ) измените коды форматов чисел, чтобы создать нужный формат.

Чтобы в ячейке отображались как текст, так и числа, заключите их в двойные кавычки («») или перед числами с помощью обратной косой черты ().

Примечание. При редактировании встроенного формата формат не удаляется.

12 как #12 продукта

Текст, заключенный в кавычки (включая пробелы), отображается перед числом в ячейке. В коде 0 — число, содержащееся в ячейке (например, 12).

12:00 в качестве 12:00 AM

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

-12 в виде $-12,00 недостачи и 12 в $12,00 излишков

$0,00 «излишки»; $-0,00 «недостачи»

Значение отображается в денежном формате. Кроме того, если ячейка содержит положительное значение (или 0), после значения отображается «излишек». Если ячейка содержит отрицательное значение, вместо этого отображается «нехватка».

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

Если в ячейке объединяются числа и текст, числа становятся текстом и больше не заключаются в виде числовых значений. Это означает, что на них больше нельзя выполнять математические операции.

Для объединения чисел используйте функции сцепить или concat, Text или объединить и оператор амперсанд (&).

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

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

Посмотрите различные примеры на рисунке ниже.

Внимательно посмотрите на использование функции текст во втором примере на рисунке. При присоединении номера к текстовой строке с помощью оператора конкатенации используйте функцию текст для управления способом отображения числа. Формула использует основное значение из ячейки, на которую указывает ссылка (4 в этом примере), а не отформатированного значения, которое вы видите в ячейке (40%). Чтобы восстановить форматирование чисел, используйте функцию текст .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

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

Обычно Microsoft Excel показывает рассчитанные результаты, когда вы вводите формулы в ячейки. Однако иногда может потребоваться показать только формулу в ячейке, например = СЦЕПИТЬ («000»; «- 2»), как ты с этим справишься? Есть несколько способов решить эту проблему:

Преобразование формулы в текстовую строку с функцией поиска и замены

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

Преобразование формулы в текстовую строку или наоборот одним щелчком мыши

Предположим, у вас есть диапазон формул в столбце C, и вам нужно отобразить столбец с исходными формулами, но не с их расчетными результатами, как показано на следующих снимках экрана:

Для решения этой задачи Найти и заменить функция может вам помочь, пожалуйста, сделайте следующее:

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

2. Затем нажмите Ctrl + H , чтобы открыть Найти и заменить диалоговое окно в диалоговом окне под Замените вкладка, введите равно = войдите в Найти то, что текстовое поле и введите ‘= в Заменить текстовое поле, см. снимок экрана:

3. Затем нажмите Заменить все , вы можете увидеть, что все рассчитанные результаты заменены исходными текстовыми строками формулы, см. снимок экрана:

Следующий код VBA также поможет вам легко справиться с этим.

1. Удерживайте другой + F11 ключи в Excel, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модули, и вставьте следующий макрос в Окно модуля.

Function ShowF(Rng As Range)
ShowF = Rng.Formula
End Function

3. В пустой ячейке, например в ячейке D2, введите формулу. = ShowF (C2).

4. Затем щелкните ячейку D2 и перетащите маркер заливки. к тому ассортименту, который вам нужен.

Если у вас есть Kutools for Excel, С его Преобразовать формулу в текст функция, вы можете преобразовать несколько формул в текстовые строки одним щелчком мыши.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Выберите формулы, которые хотите преобразовать.

2. Нажмите Кутулс > Содержание > Преобразовать формулу в текст, и выбранные вами формулы были сразу преобразованы в текстовые строки, см. снимок экрана:

Советы: Если вы хотите преобразовать текстовые строки формулы обратно в вычисленные результаты, просто примените утилиту преобразования текста в формулу, как показано на следующем снимке экрана:

Если вы хотите узнать больше об этой функции, посетите Преобразовать формулу в текст.

Скачать и бесплатную пробную версию Kutools for Excel Сейчас !

Преобразование формул в значения

Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из «калькулятора-переростка» в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:

• Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
• Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
• Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
• Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

1. Выделите диапазон с формулами, которые нужно заменить на значения.
2. Скопируйте его правой кнопкой мыши – Копировать (Copy).
3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

   
   либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:

   
   Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.

   В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

   

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

1. Копируем выделенный диапазон Ctrl+C
2. Тут же вставляем обратно сочетанием Ctrl+V
3. Жмём Ctrl, чтобы вызвать меню вариантов вставки
4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub

Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:

Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
 

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub
 

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью. 

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

В этом случае:

• всё будет максимально быстро и просто
• можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
• в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
• любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

Ссылки по теме

• Что такое макросы, как их использовать, копировать и запускать
• Как скопировать формулы без сдвига ссылок
• Как считать в Excel без формул

Объединение текста и чисел

​Смотрите также​​: buchlotnik, AlexM, спасибо!​ не вариант.​ Application.ThousandsSeparator, » «)​ РУБЛЬ, которая преобразует​Функция ТЕКСТ требует 2​ из текста сделать​ & i.FormulaLocal​ получил ответ»​ прямо в ней.​На первом рисунке​ столбцов А и​ вас актуальными справочными​ функцию​функции СЦЕПИТЬ​ числом. В этом​ 200 # продукта.​Примечание:​В параметрах замены​AlexM, апостроф -​End Function​ любое число в​

​ обязательных для заполнения​ формулу — я​Next​** ТУРБО-ЁЖ **​С уважением, Александр.​ та ячейка, в​ В с текстом​ материалами на вашем​текст​. Несмотря на​ коде «0» обозначает​Использование пользовательского числового формата​Мы стараемся как​ (ctrl+H) выбирается область​

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

​ это решение. Только​Скопируйте его в модуль​ текст и отображает​ аргумента:​ не представляю{/post}{/quote}​End Sub​: Скорее всего, что​vikttur​ которой мне надо​ в формуле используются​ языке. Эта страница​.​ то, что функция​ число, которое содержится​ для отображения номера​ можно оперативнее обеспечивать​

​ поиска: формулы. Не​ вот как быть,​ («Insert»-«Module») VBA-редактора (ALT+F11).​ его в денежном​Значение – ссылка на​Попробуйте Ctrl+Ё, иногда​Инструкция: Выделяем диапазон,​ топикстартер не знает,​

​: Что-то напоминает тему,​

1. ​ рядом с результатом​ амперсанды (​

2. ​ переведена автоматически, поэтому​​Функция СЦЕПИТЬ​​СЦЕПИТЬ​​ в ячейке (например,​​ с текстом, не​
   

3. ​ вас актуальными справочными​​ знал, что так​​ если ячеек с​​ При необходимости прочитайте:​​ формате:​ исходное число (в​ помогает :-)​ выполняем код.{/post}{/quote}​

4. ​ что с помощью​​ которая рядом:​​ вставить слово, а​&​ ее текст может​СЦЕП​

   ​по-прежнему доступен для​ 12).​ изменяя порядок сортировки​ материалами на вашем​ можно.​ разными формулами очень​Примеры как создать пользовательскую​Данное решение весьма ограничено​
   ​ данном случае).​​Часто в отчетах Excel​Вы ГЕНИЙ!!! Это​ функции ЯЧЕЙКА можно​

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

​ этом примере —​ нас важно, чтобы​Бывает, что введя формулу​ОБЪЕДИНЕНИЯ​ч:мм «центральноевропейское время»​ номера без изменения​ ее текст может​1. Получить текст​ апостроф во все​ и получаем максимально​

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

​ могу вставить -​​A2​

• ​ эта статья была​ и нажав клавишу​, так как​​Текущее время показано в​​ значения.​​ содержать неточности и​​ формулы с помощью​ ячейки автоматом.​​ эффективный результат:​​ соединяемое число с​Число можно форматировать любым​ что вовремя объединения​​ работает!!! А я​​ СТОЛБЕЦ. Как бы​​ что задача решается​​ выходит мое слово​и​ вам полезна. Просим​

• ​ENTER​​функции СЦЕПИТЬ​ формате даты/времени ч:мм​Выполните следующие действия.​ грамматические ошибки. Для​ макрофункции GET.FORMULA()​buchlotnik​Благодаря функции ВЗЯТЬФОРМАТ написанной​ текстом является денежно​ способом, важно лишь​ текста с числом​​ этот префикс втавлял​​ там ни было,​ другим, отличным от​ и код функции.​

Примеры

​B2​ вас уделить пару​

​пользователь видит в​могут быть недоступны​​ AM/PM, а текст​​Выделите ячейки, формат которых​ нас важно, чтобы​2. Получить текст​:​ на VBA-макросе мы​ суммой в валюте​​ соблюдать правила оформления​​ нельзя сохранить числовой​ вручную, потом редактировал​ вот требуемый макрос:​ авторского, способом.​ А мне именно​) не нужно заключать​ секунд и сообщить,​ ячейке не результат​ в будущих версиях​ «московское время» отображается​ требуется изменить.​ эта статья была​​ формулы с помощью​​sgkorolew​

См. также

• ​ просто берем значение​

• ​ рубли (или той​

• ​ форматов, который должен​

• ​ формат данных ячейки.​

support.office.com

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

​ формулу потом вруную​Sub Formula2Text2Formula()​​Guest​​ надо чтобы было​ в двойные кавычки.​ помогла ли она​ вычисления формулы, а​ Excel.​ после времени.​На вкладке​ вам полезна. Просим​

​ функции Ф.ТЕКСТ() (работает​, по поводу смены​ и формат из​ которая является по​

​ распознаваться в Excel.​ Число в ячейке​ удалял, теперь вы​Dim i As​: Да дело в​

• ​ слово и мое​ Кавычки используются только​ вам, с помощью​​ саму формулу. Причина​​TEXTJOIN​
• ​-12 как -12р. дефицит​Главная​​ вас уделить пару​​ только в Excel​ формата — см.​ исходной ячейки и​ умолчанию и указана​
• ​Например, ниже заполненная аргументами​​ отформатировано как текст.​​ можете представить как​ Range, Prefix As​ том что формул​ число​
• ​ для текста, который​​ кнопок внизу страницы.​​ — Текстовый формат​

excel2.ru

Использование текста в формулах

​Объединение текста из​​ и 12 как​в группе​ секунд и сообщить,​ 2013)​ сообщение №4.​ подставляем его как​ в региональных стандартах​ функция ТЕКСТ возвращает​Рассмотрим, например, отчет, который​ вы мне помогли!​ String​ слишком много! А​Евгений колосов​ находится в формуле​ Для удобства также​ ячейки. Покажем как​ нескольких диапазонах и/или​ 12р. избыток​число​ помогла ли она​3. Получить текст​

​Замена автоматом -​ текстовую строку. Так​ панели управления Windows).​ число в денежном​ изображен ниже на​ Думаю что не​For Each i​ тут бы я​: после формулы поставте​ до и после​ приводим ссылку на​ в этом случае​ строки, а также​0.00р. «избыток»;-0.00р. «дефицит»​щелкните стрелку.​ вам, с помощью​ формулы с помощью​ выделяете нужные ячейки,​ наша формула с​

​Функция РУБЛЬ требует для​ формате пересчитанному по​

​ рисунке. Допустим, например,​ только мне!​ In Selection​

​ ее в текст​ пробел, знак &​ ссылок на ячейки.​ оригинал (на английском​ заставить формулу работать.​ разделитель, указанный между​​Это значение отображается в​

​В списке​ кнопок внизу страницы.​ настроек: Файл -​ ctrl+H (заменить) и​ пользовательской макрофункцией ВЗЯТЬФОРМАТ​​ заполнения только 2​​ курсу 68 руб./1$:​ необходимо в отчете​Еще раз огромное​​Prefix = «»​​ и по формуле​​ , снова пробел,​​В этом примере функции​ языке) .​Итак, по какой-то причине​ каждой парой значений,​ формате «Денежный». Кроме​категории​ Для удобства также​

Использование текста в формуле, содержащей функцию

​ Параметры — Дополнительно​ делаете замену «=»​ автоматически определяет валюту​ аргумента:​​Измененная ниже формула возвращает​

​ поместить список магазинов​ спасибо!​If i.FormulaLocal <>​

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

​ — Показывать формулы,​​ на » ‘=»​​ для каждой суммы​Число – ссылка на​ долю в процентах​ и их результаты​SV​ i.Text Then Prefix​ сделал​

​ текст, снова кавычки​​ для составления фразы,​​ вы хотите сделать​​ в ячейку с​​ текст. Если разделитель​ ячейке находится положительное​

support.office.com

MS Excel: Как вставить текст рядом с формулой?

​настраиваемые​
​ оригинал (на английском​
​ а не их​sgkorolew​ в исходных значениях​ числовое значение (обязательный​ от общей выручки:​ продаж в одной​: Слэн! Это круто!​ = Chr(39)​Guest​@Link​ такие как​ данные проще было​ Тектовым форматом.​ пустую текстовую строку,​ значение (или 0),​

​и нажмите кнопку встроенный​​ языке) .​ значения​: Если в ячейку​ ячеек.​ аргумент для заполнения).​

​Простой способ проверки синтаксиса​​ ячейке:​
​ Спасибо большое!​
​i.FormulaLocal = Prefix​: Да вы абсолютно​

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

​: воспользуйтесь функцией СЦЕПИТЬ​​сегодня Пятница, 20 янв.​ понять, включая текст​Заставить EXCEL интерпретировать введенное​ эта функция будет​
​ после него будет​ формат, который похож​Предположим, что нужно создать​4. Получить текст​ с тектовым форматом​
​sgkorolew​[Число_знаков] – Количество символов​ кода формата, который​Обратите внимание, что каждое​Но я извиняюсь,​ & i.FormulaLocal​ правы! Это такая​

​например:​​= «Сегодня» & текст​ в формулах. Например​ в ячейку выражение​ эффективно объединять диапазоны.​ показан текст «(излишек)»,​ на то, которое​ предложение грамматически правильные​ формулы с помощью​ вставить формулу, то​
​: Добрый вечер!​

​ после запятой.​​ распознает Excel –​ число после объединения​
​ я так и​Next​ же тема. Просто​=сцепить (сумм (A1:A7);»​ (сегодня (); «дддд,​ может потребоваться добавить​ как формулу, а​

​TEXTJOIN​​ а если ячейка​ вы хотите.​ из нескольких столбцов​ VBA​ да — формула​Подскажите, пожалуйста, как​Если в исходном столбце​ это использование окна​

​ массовой рассылки. Или,​​ оригинал формулы продолжает​
​ текста. Но прежде​ текст.​

​Примеры различных на рисунке​​При объединении чисел и​

​ в той теме​​: Скорее всего, что​ встроен текст который​
​Допустим у меня​ отобразить, дату как​ кавычки («»). Все​);​

planetaexcel.ru

Формула форматирует число суммы как текст в одной ячейке Excel

​ =СУММ(А1+12). Как мне​в результирующую строку​ пробелы и знаки​ (нажав​во втором примере​ значения. Это означает,​ заключите текст в​ несколько способов для​ т.е. «убить» формулу,​ копировать по отдельности.​

Отформатировать число как текст в Excel с денежным форматом ячейки

​ ‘ апостроф​ которая умеет распознавать​ нажмите комбинацию горячих​ числами денежных сумм​Задумка здесь такая.​ функции ЯЧЕЙКА можно​ двух местах, а​ ее преобразовать в​

​ текста.​ препинания) между двойные​CTRL+1​ на рисунке. При​ что больше не​ двойные кавычки (»​ объединения текста и​ а на её​

​ Простите, если я​buchlotnik​ и возвращать форматы​ клавиш CTRL+1.​ в функцию ТЕКСТ.​ Дело в том​ получить имя листа​ так же цифры​ текст?​Обратите внимание: формат даты​

​ кавычки будут показаны​и выбрав соответствующий​ присоединении к числа​ может выполнять любые​ «), или чисел​ чисел.​ место поместить её​ чего-то не понимаю.​:​ ячеек. Поэтому напишем​Перейдите на вкладку «Число».​

​ Благодаря ей можно​ что в формулу​ и не пробовал​

1. ​ которые меняются с​Затем я ее​дддд, дд МММ​
2. ​ в результате как​ Числовой формат или​ в строку текста​

​ математических операций на​ с помощью обратной​Если столбец, который вы​ текст, то варианты​buchlotnik​

​sgkorolew​ свою пользовательскую макрофункцию​В секции «Числовые форматы:»​ форматировать числовые значения​ встроен текст который​

​ также воспользоваться функцией​ каждым столбцом!!! тоже​ КАК ТЕКСТ!!! отредактирова​

​заключен в кавычки​ текст.​ через меню Главное/​ с помощью оператор​ них.​ косой черты ()​

1. ​ хотите отсортировать содержит​ такие:​:​, здравствуйте, как вариант​ и добавим ее​ выберите категорию «(все​ прямо в текстовой​ меняется с каждым​
2. ​ СТОЛБЕЦ. Как бы​
3. ​ в двух местах!​ допустим =СУММ(А1+14) как​ и в нем​
4. ​Вот пример простой формулы,​ Число/ Числовой формат);​ объединения, используйте функцию​Для объединения чисел с​ в начале.​ числа и текст​1. Установить текстовый​sgkorolew​

​ — формат ячейки​

Функция РУБЛЬ для форматирования числа как текст в одной ячейке

​ в нашу формулу.​ форматы)».​ строке. Формула изображена​ листом причем в​ там ни было,​ Я легко заменил​ мне этот текст​ используются запятые и​

​ которая содержит текст:​нажмите клавишу​текст​ помощью функции СЦЕПИТЬ​Примечание:​ — 15 #​ формат ячеек и​, в вашем случае​текстовый​ Макрофункция будет называться​Введите в поле «Тип:»​ на рисунке:​

​ двух местах, а​ вот требуемый макрос:​ бы это все​

1. ​ преобразовать в формулу?​ пробелы. Если запятые​= A2 &» продал​
2. ​F2​, чтобы управлять​

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

​ или функции ОБЪЕДИНЕНИЯ,​изменение встроенного формата​ продукта, продукт #100​ перевычислить формулы​ возиться с форматом​AlexM​ ВЗЯТЬФОРМАТ (или можете​ свой пользовательский код​Формула позволяет решить данную​ так же цифры​Sub Formula2Text2Formula()​ будь это не​ Подскажите кто знает,​ рассматриваются как текст,​ «& B2 &»​(т.е. войдите в​ способом отображения чисел.​ текст и TEXTJOIN​ не приводит к​ и 200 #​

​2. Вставить апостроф​ не вариант -​
​: Сначала формат, потом​ назвать ее по-своему).​
​ формата и в​ задачу если речь​
​ которые меняются с​Dim i As​
​ формула а текст!​


​ пожалуйста! Очень нужно!​ их можно использовать​ единицы.»​

​ Режим Правки ячейки)​ В формуле используется​

​ и амперсанд (&)​ удалению формат.​ продукта — не​

​ перед знаком «​ попробуйте схему из​ вставить формулу.​ Исходный VBA-код макрофункции​ секции «Образец» наблюдайте​ идет только об​ каждым столбцом!!! тоже​ Range, Prefix As​ Но как из​Sh_Alex​ по необходимости.​Если ячейки в столбце​ или поставьте курсор​

exceltable.com

Преобразование формул в текст (Формулы/Formulas)

​ базовое значение из​​ оператор.​
​Для отображения​ может сортировать должным​=​
​ сообщения №6 с​Если наоборот, то​ выглядит так:​ как он будет​

​ одном типе валюты.​​ в двух местах!​ String​

​ формулы сделать текст?​​: Может я не​​Наконец в формуле используется​​ A содержат фамилии,​ в Строку формул;​​ ячейки, на который​

​Примечания:​​Используйте код​ образом. Можно отформатировать​
​» (равно)​ заменой «=» на​ значение полученное формулой​

​Public Function ВЗЯТЬФОРМАТ(val As​​ распознан в Excel​ Когда валют будет​ Я легко заменил​For Each i​ А потом как​
​ о том, но​&​ а ячейки в​нажмите​ указывает ссылка (в​ ​Принцип действия​ ячейки, содержащие 15,​3. Заменить формулу​

​ » =»​​ преобразуется в текст.​​ Range) As String​​ и отображен в​ несколько придется использовать​ бы это все​
​ In Selection​ из текста сделать​ что мешает Вам​для объединения слова​ столбце B проданных​

​ENTER​​ данном примере.4) —​В Excel 2016Excel Mobile и​12 как Продукт №12​ 100 и 200,​ на её текст​TimSha​sgkorolew​Dim money As​ ячейке.​ в формуле макрофункцию​ будь это не​Prefix = «»​ формулу — я​ зайти в строку​сегодня​

​ единиц, результат будет​​.​​ не форматированное значение,​​ Excel Online с​»Продукт № » 0​ чтобы они отображались​ с помощью VBA​: Азбука — Ctrl+H​: buchlotnik, меняю форматы​ String​

​​​ – об этом​ формула а текст!​
​If i.FormulaLocal <>​

​ не представляю​​ формул, провести редакцию​
​, и пробел с​ примерно такой:​Примечание:​ отображаемое в ячейке​ помощью функции​

​Текст, заключенный в кавычки​​ на листе как​
​sgkorolew​ …​ на текстовый -​
​money = WorksheetFunction.Text(val,​Более упрощенным альтернативным решением​ речь пойдет ниже.​ Но как из​ i.Text Then Prefix​
​vikttur​ и enter. Или​ отформатированной датой.​крюков продал 234 единицы.​Мы стараемся как​ (40%). Чтобы восстановить​ОБЪЕДИНЕНИЯ​
​ (включая пробел) отображается​ 15 # продукт,​: TimSha, скрин просто​
​См. скрин.​ в ячейке сохраняются​ val.NumberFormat)​ для данной задачи​ А пока разберемся​
​ формулы сделать текст?​ = Chr(39)​: Уже как слоган:​ двойной клик по​Увеличить​Для объединения значений из​ можно оперативнее обеспечивать​ форматов чисел используйте​
​заменена​ в ячейке перед​ продукт #100 и​
​ класс!!!​sgkorolew​​ результаты вычислений. Вроде​​ВЗЯТЬФОРМАТ = Replace(money,​
​ может послужить функция​ с этой формулой.​ А потом как​

​i.FormulaLocal = Prefix​​»Показал пример -​ ячейке и редактируйте​

excelworld.ru

​Увеличить​

Отображение текста формул

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

Для пользователя 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«.

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

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

Видео: