Формула пишется как текст excel

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

ENTER

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

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

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

F2

, затем

ENTER (ВВОД)

.

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

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

  );

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

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

  ;

• нажмите
  
  ENTER
  
  .

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

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

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

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

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

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

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

:

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

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

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

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

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

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

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

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

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

Видео:

Почему в эксель вместо значения показывается формула

Что делать, если Excel отображает формулу вместо результата

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

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

Включено отображение формул

В Экселе есть режим проверки вычислений. Когда он включен, вы видите на листе формулы. Как и зачем применять отображение формул, я рассказывал в этой статье. Проверьте, возможно он активирован, тогда отключите. На ленте есть кнопка Формулы – Зависимости формул – Показать формулы . Если она включена – кликните, по ней, чтобы отключить.

Часто показ формул включают случайно, нажав комбинацию клавиш Ctrl+` .

Формула воспринимается программой, как текст

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

• В ячейке установлен текстовый формат. Для программы это сигнал, что содержимое нужно вывести в том же виде, в котором оно хранится.
  
  Измените формат на Числовой, как сказано здесь.
• Нет знака «=» вначале формулы. Как я рассказывал в статье о правилах написания формул, любые вычисления начинаются со знака «равно».
  
  Если его упустить – содержимое будет воспринято, как текст. Добавьте знак равенства и пересчитайте формулу.
• Пробел перед «=». Если перед знаком равенства случайно указан пробел, это тоже будет воспринято, как текст.
  
  Эту ошибку сложно заметить, поэтому придется проверять вручную. Удалите лишний пробел и пересчитайте.
• Формула заключена в кавычки. Такая ошибка случается, когда работаем с текстовыми данными. Само собой, информация в кавычках трактуется, как текст.
  
  Удалите лишние кавычки, нажмите Enter , чтобы пересчитать.

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

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

Источник: officelegko.com

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

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

Решение 1: меняем формат ячеек

Очень часто Excel отказывается выполнять расчеты из-за того, что неправильно выбран формат ячеек.

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

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

Очевидно, что формат ячеек нужно изменить, и делается это следующим образом:

1. Чтобы определить текущий формат ячейки (диапазон ячеек), выделяем ее и, находясь во вкладке “Главная”, обращаем вниманием на группу инструментов “Число”. Здесь есть специальное поле, в котором показывается формат, используемый сейчас.
2. Выбрать другой формат можно из списка, который откроется после того, как мы кликнем по стрелку вниз рядом с текущим значением.

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

1. Выбрав ячейку (или выделив диапазон ячеек) щелкаем по ней правой кнопкой мыши и в открывшемся списке жмем по команде “Формат ячеек”. Или вместо этого, после выделения жмем сочетание Ctrl+1.
2. В открывшемся окне мы окажемся во вкладке “Число”. Здесь в перечне слева представлены все доступные форматы, которые мы можем выбрать. С левой стороны отображаются настройки выбранного варианта, которые мы можем изменить на свое усмотрение. По готовности жмем OK.
3. Чтобы изменения отразились в таблице, по очереди активируем режим редактирования для всех ячеек, в которых формула не работала. Выбрав нужный элемент перейти к редактированию можно нажатием клавиши F2, двойным кликом по нему или щелчком внутри строки формул. После этого, ничего не меняя, жмем Enter.

Примечание: Если данных слишком много, на ручное выполнение последнего шага потребуется немало времени. В данном случае можно поступить иначе – воспользуемся Маркером заполнения. Но этот работает только в том случае, когда во всех ячейках используются одинаковая формула.

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

Решение 2: отключаем режим “Показать формулы”

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

1. Переключаемся во вкладку “Формулы”. В группе инструментов “Зависимость формул” щелкаем по кнопке “Показать формулы”, если она активна.
2. В результате, в ячейках с формулами теперь будут отображаться результаты вычислений. Правда, из-за этого могут измениться границы столбцов, но это поправимо.

Решение 3: активируем автоматический пересчет формул

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

1. Заходим в меню “Файл”.
2. В перечне слева выбираем раздел “Параметры”.
3. В появившемся окне переключаемся в подраздел “Формулы”. В правой части окна в группе “Параметры вычислений” ставим отметку напротив опции “автоматически”, если выбран другой вариант. По готовности щелкаем OK.
4. Все готово, с этого момента все результаты по формулам будут пересчитываться в автоматическом режиме.

Решение 4: исправляем ошибки в формуле

Если в формуле допустить ошибки, программа может воспринимать ее как простое текстовое значение, следовательно, расчеты по ней выполнятся не будут. Например, одной из самых популярных ошибок является пробел, установленный перед знаком “равно”. При этом помним, что знак “=” обязательно должен стоять перед любой формулой.

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

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

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

Распространенные ошибки

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

• #ДЕЛ/0! – результат деления на ноль;
• #Н/Д – ввод недопустимых значений;
• #ЧИСЛО! – неверное числовое значение;
• #ЗНАЧ! – используется неправильный вид аргумента в функции;
• #ПУСТО! – неверно указан адрес дапазона;
• #ССЫЛКА! – ячейка, на которую ссылалась формула, удалена;
• #ИМЯ? – некорректное имя в формуле.

Если мы видим одну из вышеперечисленных ошибок, в первую очередь проверяем, все ли данные в ячейках, участвующих в формуле, заполнены корректно. Затем проверяем саму формулу и наличие в ней ошибок, в том числе тех, которые противоречат законам математики. Например, не допускается деление на ноль (ошибка #ДЕЛ/0!).

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

1. Отмечаем ячейку, содержащую ошибку. Во вкладке “Формулы” в группе инструментов “Зависимости формул” жмем кнопку “Вычислить формулу”.
2. В открывшемся окне будет отображаться пошаговая информация по расчету. Для этого нажимаем кнопку “Вычислить” (каждое нажатие осуществляет переход к следующему шагу).
3. Таким образом, можно отследить каждый шаг, найти ошибку и устранить ее.

Также можно воспользоваться полезным инструментом “Проверка ошибок”, который расположен в том же блоке.

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

Заключение

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

Источник: microexcel.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: excel2.ru

Проблемы с вычислением формул в Microsoft Excel

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

Устранение проблем с вычислением

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

Способ 1: изменение формата ячеек

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

1. Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.

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

Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.

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

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

Способ 2: отключение режима «Показать формулы»

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

1. Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.

После этих действий в ячейках снова вместо синтаксиса функций начнет отображаться результат.

Способ 3: исправление ошибки в синтаксисе

Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

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

Способ 4: включение пересчета формулы

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

Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».

Откроется окно параметров. Нужно перейти в раздел «Формулы». В блоке настроек «Параметры вычислений», который расположен в самом верху окна, если в параметре «Вычисления в книге», переключатель не установлен в позицию «Автоматически», то это и есть причина того, что результат вычислений неактуальный. Переставляем переключатель в нужную позицию. После выполнения вышеуказанных настроек для их сохранения в нижней части окна жмем на кнопку «OK».

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

Способ 5: ошибка в формуле

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

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

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

Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».

Открывается окно, в котором представляется полный расчет. Нажимаем на кнопку «Вычислить» и просматриваем вычисление пошагово. Ищем ошибку и устраняем её.

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

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

Источник: lumpics.ru

Почему в ячейке Excel отображается формула, а не значение

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

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

Как это сделать?

Сменить формат можно как на ленте вверху: Главная — Число:

Нажмите на изображение для увеличения

Или выделить нужную ячейку (ячейки), нажать правую кнопку мыши — Формат ячеек — и выбрать формат.

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

5 ошибок перехода на дистанционное обучение в России и как их избежать + видео

5 ошибок перехода на дистанционное обучение в России и как их избежать + видео

5 ошибок перехода на дистанционное обучение в России и как их избежать + видео

Хотите получать информацию о наиболее интересных материалах нашего сайта? Подпишитесь на рассылку E-mail Установите приложение на Android 2007-2020 “Педагогическое сообщество Екатерины Пашковой — PEDSOVET.SU”. 12+ Свидетельство о регистрации СМИ: Эл №ФС77-41726 от 20.08.2010 г. Выдано Федеральной службой по надзору в сфере связи, информационных технологий и массовых коммуникаций. Адрес редакции: 603111, г. Нижний Новгород, ул. Раевского 15-45 Адрес учредителя: 603111, г. Нижний Новгород, ул. Раевского 15-45 Учредитель, главный редактор: Пашкова Екатерина Ивановна Контакты: +7-920-0-777-397, info@pedsovet.su Домен: https://pedsovet.su/ Копирование материалов сайта строго запрещено, регулярно отслеживается и преследуется по закону. Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов. Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию. сервис вебинаров О работе с сайтом Мы используем cookie. Публикуя материалы на сайте (комментарии, статьи, разработки и др.), пользователи берут на себя всю ответственность за содержание материалов и разрешение любых спорных вопросов с третьми лицами. При этом редакция сайта готова оказывать всяческую поддержку как в публикации, так и других вопросах. Если вы обнаружили, что на нашем сайте незаконно используются материалы, сообщите администратору — материалы будут удалены. Источник: pedsovet.su Отобразить в формулах вместо ссылок на ячейки значения ячеек Попробую в двух словах описать суть статьи: предположим на листе есть формула: = A1 * A14 +(5+ C13 )* C14 / B11 В принципе все понятно и наглядно. Но иногда требуется понять, что за значения скрываются за ссылками на ячейки. Т.е. из приведенной выше формулы надо сделать: =10*5,2+(5+5)*10 /7,8 Одно дело, если ссылок на ячейки в формуле пара штук. Но вот когда их 10 и более, да еще и на разные листы/книги ссылаются – высматривать значение каждой достаточно утомительное занятие. Выделять каждую формулу, чтобы посмотреть это – тоже не всегда удобно. И стандартными методами Excel заменить не получится. Поэтому в один прекрасный момент я решил написать код, приведенный в файле, который вместо ссылок на ячейки отображает их значения. Все функции и знаки при этом так же присутствуют. Таким образом формулу можно изучить более наглядно. Прежде всего необходимо сделать следующее: скачать файл, приложенный к статье ознакомиться со статьей Что такое макрос и где его искать?, если еще не знакомы с макросами при необходимости коды из файла перенести в свой файл(перейти к просмотру кодов можно, нажав в файле кнопку “Посмотреть код”) Как это работает: выделяем ячейки с формулами, которые необходимо “показать”; нажимаем кнопку “Показать значения формулы в выделенных ячейках”; появится запрос Если в какой-либо из ячеек не будет ссылок на другие ячейки, а просто текстовая формула, то как результат отобразится сама формула и за ней текст: “[ссылок на другие ячейки нет]” Если в формуле применяются функции( ВПР , СЧЁТЕСЛИ , МИН , МАКС и т.д.), то их имена будут отображены без искажений(как во вложенном примере =СУММ(5,2;7,8)+ЦЕЛОЕ(5/11) ) Если присутствуют ссылки на ячейки из других листов или книг, то они отображаются как и все остальные – просто значениями. Если в формулах встречаются ссылки на массивы ячеек (A14:B16) – будут отображены все значения непустых ячеек массива(как и положено массиву в фигурных скобках: <5,2;4:6>, двоеточием разделяются строки, а точкой-с-запятой – столбцы). В ближайшее время планирую сделать некую настройку данного кода, чтобы можно было рядом со значениями отображать названия листов и книг, с которых получены эти значения. Пока размышляю насколько это может быть полезно и нужно и как наиболее удобочитаемо это отображать. Tips_All_Get_ValFormulaText.xls (53,0 KiB, 4 436 скачиваний) Статья помогла? Поделись ссылкой с друзьями! Источник: www.excel-vba.ru 04.04.2020 Как в excel округлять 04.04.2020 Как посчитать строки в excel Adblock detector

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

​Смотрите также​Rainbowsky.Ru​​Татьяна1989​​ ячейке, желатьельно не​ — должен быть​ нажать чтобы видеть​ всем листе, эксель​ подключения. Это можно​ заменить ее другим​ для удаления знаков​ символы в ячейках​

​ как можно заменить​ которая используется на​ проблему.​и выберите пункт​

​ работали в Excel,​ проблемы и решения​Бывает, что введя формулу​: в чем кривизна?​: Эксель пишет что​

• ​ смежно, лучше даже​ «Общий».​ результат занесенных формул?​​ не читает формулу​​ сделать путем копирования​
• ​ выбранным значением. Если​ или замены специальных​​ будут удалены.​​ несколько пробелов одновременно.​ компьютере, чтобы она​Проверка наличия начальных пробелов​Язык и региональные стандарты​
• ​ вероятно, вы неправильно​​ ошибки. Возможно, понадобится​​ и нажав клавишу​Зибин​ значения данной ячейки​ где-нибудь в другом​
• ​Iren​​ Заранее спасибо огромное​​ (никакую: ни деление,​

excel2.ru

Исправление ошибки #ЗНАЧ!

​ всех ячеек и​​ ошибки нет, будет​ знаков другими значениями​Щелкните стрелку фильтра​ В этом примере,​ соответствовала системе дат,​Дважды щелкните дату, которая​.​ вводите формулу вычитания.​ попробовать одно или​ENTER​: В Excel 2007/2010:​ не используются в​ конце листа. Когда​: Формат проверила, не​ за помощь.​ ни сложение…). Формат​ вставки только в​ вычислена исходная формула.​ с помощью функции​и выберите команду​

​ если щелкнуть​ которая нужна в​​ используется в формуле​На вкладке​ Это можно сделать​ несколько решений, чтобы​​пользователь видит в​ кнопка Офис (Файл)​ других формулах​ вы находите ту​ помогает. (В ячейке,​vikttur​ ячейки числовой, как​ качестве значений. Чтобы​ ЕСЛИОШИБКА будет работать​ заменить .​Удалить фильтр из…​E​

Исправление ошибок определенных функций

​ Excel. Или в​ вычитания.​

• ​Форматы​
• ​ двумя способами:​
• ​ устранить конкретную ошибку.​
• ​ ячейке не результат​
• ​ — Параметры Excel​
• ​Татьяна1989​
• ​ ячейку в которой​
• ​ которая отображает значение,​
• ​: Не открывая файл:​
• ​ и во всех​
• ​ вставить только значения,​
• ​ только в Excel​
• ​Использовав функцию ПЕЧСИМВ или​
• ​для отображения всех​
• ​, выделится весь столбец.​
• ​ Excel можно создать​
• ​Разместите курсор в начале​

​нажмите кнопку​Введите два значения в​Какую функцию вы​

​ вычисления формулы, а​ — Формулы -​: Спасибо! Помогло.​

​ у вас выходит​ а не формулу,​ПКМ-Свойства​

​ других. ячейка защищена.​ можно выбрать команды​ 2007 и более​

​ ЗАМЕНИТЬ, вы можете​ ячеек.​Найти и выделить >​

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

​ используете? ​ саму формулу. Причина​ Стиль ссылок R1C1​

​Иван лисицын​ результат, вы ее​ кстати, был текстовый​

​. Возможно, установлена галка​ Подскажите в чем​Главная​ поздних версиях. Для​

​ скопировать результат в​Если причиной появления ошибки​ Заменить» />​

​ использовать функцию ДАТА,​ ли выбрать один​.​

​ В третьей ячейке​Какую функцию вы используете?​ — Текстовый формат​

​В Excel 2003​: Щелкаете в ячейке​ просто копируете в​

​ формат, это не​ «​ может проблема? Прикрепленные​

​>​ более ранних версий,​ буфер обмена, а​

​ #ЗНАЧ! были пробелы,​На вкладке​ чтобы создать настоящую​ или несколько пробелов.​

Проблемы с вычитанием

Выполнение базового вычитания

​Найдите пункт​ вычтите одну ссылку​СРЗНАЧ​ ячейки. Покажем как​ и старше: Сервис​ с формулой правой​

Вычтите одну ссылку на ячейку из другой

​ нужное вам место​ мешало). Я беру​Только_для_чтения​ файлы Снимок.JPG (38.9​Вставить​ которые можно использовать,​ затем выбрать​ вместо ошибки отобразится​Главная​ дату на основе​ Вот как выглядит​​Разделитель элементов списка​​ на ячейку из​

Или используйте функцию СУММ с положительными и отрицательными числами

​СЦЕПИТЬ​ в этом случае​ — Параметры -​ кнопкой мыши, выбираете​ и все начинает​ файл старой версии​»​ КБ)​>​ если (ошибка ()).​Главная > Вставить >​ результат формулы, как​щелкните​​ даты в текстовом​​ выбранный пробел в​

Ошибка #ЗНАЧ! при базовом вычитании

​. Если в поле​ другой. В этом​СЧЁТЕСЛИ, СЧЁТЕСЛИМН​ заставить формулу работать.​ Общие — Стиль​ ФОРМАТ ЯЧЕЕК, затем​

1. ​ работать нормально.​ эксель и если​Pelena​Сергей​Специальная вставка​Предупреждение:​ Специальная вставка >​​ показано в нашем​​Найти и выделить​ формате. Вот как​ начале ячейки:​ разделителя элементов списка​ примере ячейка D2​ДАТАЗНАЧ​Итак, по какой-то причине​ ссылок R1C1​

2. ​ вкладку ЧИСЛО, а​BlindMen​

   • ​ делаю ссылки на​: Здравствуйте.​​: Лечение по фотографиям​​>​ В ЕСЛИОШИБКА все ошибки​​ Значения​​ примере. Если нет —​

   • ​>​ это сделать, если​​Если в ячейке обнаружена​​ указан знак «минус»,​​ содержит плановую сумму,​​ДНИ​​ пользователь ввел формулу​​убрать «птицу»…​

   • ​ в ней скорей​​: Нажмите сочитание Ctrl​​ его ячейки, то​​Может быть установлен​​ эт вам в​​значения​​ будут скрыты, а​

3. ​. Кроме того, может​​ повторите эти действия​​Заменить​​ система дат вашего​​ эта проблема, перейдите​

4. ​ замените его на​​ а ячейка E2 —​​НАЙТИ, НАЙТИБ​ в ячейку с​Георгий христич​ всего у Вас​ + ё​ проблема. например пишу​ ручной пересчёт формул.​ программу битва экстрасенсов​. Таким образом удаляются​ не только #VALUE!​ потребоваться преобразовать числа​ для других ячеек,​

5. ​.​​ компьютера — дд.мм.гггг, а​​ к следующему шагу.​

6. ​ что-то другое. Например,​ фактическую. F2 содержит​ЕСЛИ​ Тектовым форматом.​

7. ​: Файл Параметры Формулы​ стоит выделение на​bakhtovar​ в ячейке «=[08_август10.xls]август!A7″​ F9 не помогает?​

8. ​ на ТНТ, файл​

9. ​ все формулы и​ ошибку. Скрытие ошибок​ из текстового формата​

Вычитание дат

Вычтите одну ссылку на ячейку из другой

​ на которые ссылается​В поле​ в ячейке A1​ Если вы не​ разделителем нередко выступает​ формулу​ИНДЕКС, ПОИСКПОЗ​Заставить EXCEL интерпретировать введенное​Убрать галку «Стиль​ формате ТЕКСТОВЫЙ. Щелкните​: а тебе приятно​​ показывает значение ячейки,​​Тати​

Или используйте функцию РАЗНДАТ

​ приложите до 100​ соединения, и поэтому​ не рекомендуется, так​ в числовой.​ формула. Или попробуйте​Найти​ записан текст​ видите один или​ запятая. Также часто​=D2-E2​

Ошибка #ЗНАЧ! при вычитании дат в текстовом формате

​ПОИСК, ПОИСКБ​ в ячейку выражение​ ссылок R1C1″​ на строку ОБЩИЙ.​ было? это новое​ потом меняю ссылку​: vikttur, через ПКМ​ КБ​ также удаляются #VALUE.​ как сообщение об​Формулам с математическими операциями​ другие решения на​введите один пробел.​12/31/2017​ несколько пробелов, перейдите​ используется точка с​

​.​

1. ​СУММ​ как формулу, а​Полосатый жираф алик​

2. ​ Нажимаете ОК. Затем​ открытие Блинмена?//:!​ на абсолютную нажатием​ — в свойствах​111Kseniya​ ошибок.​ ошибке часто является​

   ​ (такими как +​ этой странице.​ Затем в поле​.​ к следующему разделу​ запятой. Однако для​Введите положительное значение в​СУММЕСЛИ, СУММЕСЛИМН​ не как текст​

3. ​: Считает нормально? Значит,​ в ячейку с​

4. ​prizrak​​ F4 и вижу​​ — галки нет​​: ок. вложено​​Если вам не помогли​

5. ​ подписыванием того, что​​ и *) не​​Примечание:​

6. ​Заменить​Создайте такую формулу:​ и проверьте параметры​​ вашего конкретного региона​​ одной ячейке и​​СУММПРОИЗВ​​ — очень просто:​

7. ​ не Excel виноват,​ формулой дважды щелкаете​​: сочитание клавиш Ctrl​​ в ячейке уже​

8. ​ «Только для чтения»​MCH​ эти рекомендации, поищите​ что-то нуждается в​ всегда удается вычислить​

​ В этом примере обратите​удалите все, что​

​=ДАТА(ПРАВСИМВ(A1;4);ЛЕВСИМВ(A1;2);ПСТР(A1;4;2))​ даты на компьютере.​ может подходить другой​ отрицательное — в другой.​ВРЕМЗНАЧ​выделите ячейку с формулой​ а чьи-то тупые​ курсором и нажимаете​

​ + ё помогло!​ не значение, а​Тати​: в J11 -​ похожие вопросы на​ исправлении, а не​ ячейки, содержащие текст​ внимание, что ячейка​ там может быть.​Результат будет​Выделите столбец, содержащий дату,​ разделитель элементов списка.​ В третьей ячейке​ТРАНСП​ (на рисунке это​ мозги. Видно же​

​ ЕНТЕР. И все.​ Спасибо большое​ «=[08_август10.xls]август!$A$7» Может это​: Pelena, если нажать​ циклическая ссылка, формулу​ форуме сообщества, посвященном​ скрыто. Мы не​ или пробелы. В​ E4 содержит зеленый​Если вы уверены, что​31.12.2017​ щелкнув его заголовок.​Нажмите кнопку​ используйте функцию СУММ,​ВПР​ ячейка​ — сложить девять​Deltinka pink​тоже гость​ какой-то конфликт старой​​ F9 сразу в​​ в ней, возможно,​

1. ​ Excel, или опубликуйте​​ рекомендуем использовать эту​

2. ​ таком случае попробуйте​​ треугольник, а число​​ следует удалить все​

3. ​.​Выберите​ОК​​ чтобы сложить две​* Другая функция​

4. ​А1​ вышележащих от текущей​: поставить знак =​​: аналогично!!! Ctrl +​​ версии с 2007?​ открытом файле, то​​ нужно записать: =СУММ(J9:J10)​​ там свой собственный.​ функцию, если вы​ использовать функцию. Функции​ выравнивается по левому​

​ пробелы в столбце,​​Чтобы использовать формат дд.мм.гг,​Данные​.​ ячейки. В этом​См. статью Исправление ошибки​);​ ячеек.​ перед формулой.​ ё помогло! -​Iren​ ничего не изменилось.​Сергей​Задать вопрос на форуме​ не уверены, что​ часто пропускают текстовые​ краю. Это значит,​

Проблемы с пробелами и текстом

Удаление пробелов, которые вызывают ошибку #ЗНАЧ!

​ нажмите кнопку​ нажмите клавиши CTRL+1​>​Откройте книгу. Если ячейка​ примере ячейка D6​ #ЗНАЧ! в функции​формат ячейки установите Общий​Господа, подскажите, в Excel​​Алексей псевдонимов​​ Ура! спасибо!​: вот пример​​ Или я неправильно​​: посмотрите на формулу​

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

​ сообщества, посвященного Excel​ Ваша формула работает​ значения и рассчитывают​ что число имеет​Заменить все​ (или​Текст по столбцам​ содержит ошибку #ЗНАЧ!,​ содержит плановую сумму,​ СРЗНАЧ или СУММ​​ (нажав​​ мне нужно суммировать​

2. Найдите и замените

​: Формат ячейки проверить​Татьяна1989​

​Hugo​​ поняла?​​ в J11 ни​​Общие сведения о формулах​​ не так, как​​ все значения как​​ текстовый формат. Это​

3. Удалите пробелы

​. Если вы хотите​​+ 1 на​​.​ щелкните дважды, чтобы​​ а ячейка E6 —​​См. статью Исправление ошибки​CTRL+1​

4. Замените одно или все вхождения

​ столбец, в котором​Игорь m-н.​: Добрый вечер! Проблема​: У меня нет​​Pelena​​ че не настораживает​ в Excel​ нужно.​ числовые, не создавая​ может вызвать проблемы​​ просмотреть и удалить​​ Mac).​​Дважды нажмите кнопку​​ отредактировать ячейку.​ фактическую как негативное​ #ЗНАЧ! в функции​и выбрав соответствующий​ есть числовые значения​: Формула должна начинаться​ с формулой. В​

5. Включите фильтр

​ 2007, так что​: F9 — это​111Kseniya​​Рекомендации, позволяющие избежать появления​​Вот пример формулы, в​​ повода для появления​​ в дальнейшем. Если​ пробелы по отдельности,​Выберите другой языковой стандарт,​Далее​Если там, где для​ число. F6 содержит​ СЦЕПИТЬ​ Числовой формат или​​ и есть ошибки​​ с символа =​​ окошке редактирования формулы​​ проверить не могу​​ пересчёт формул. Если​​: спасибо большое!​

6. Установите фильтр

​ неработающих формул​​ которой ошибка #ЗНАЧ!​​ ошибки #ЗНАЧ!. Например,​​ вы заметили эту​​ можно сначала нажать​​ в котором используется​

7. Установите все флажки без названия

​.​ вычитания должны быть​ формулу​См. статью Исправление ошибки​

8. Выделите пустые ячейки и удалите их

​ через меню Главное/​ типа #ДЕЛ/0. Естественно​Прынцесскоо​ результат считается верно,​ в полной мере.​ Вы изменили данные,​

9. Очистите фильтр

​мне эксель и​​у меня такая проблема​​ вызвана скрытым пробелом​​ вместо​ проблему, рекомендуем преобразовать​

10. Результат

​ кнопку​ формат дд.мм.гг, например​На шаге 3 из​ знаки «минус», стоят​=СУММ(D6;E6)​ #ЗНАЧ! в функциях​ Число/ Числовой формат);​ программа выдает мне​: поставь перед формулой​ но в самой​ Но конвертером открыл​ а формулы не​

​ не показывал эту​​ с экселем: в​ в ячейке E2.​=A2+B2+C2​ числа из текстового​Найти далее​Немецкий (Германия)​ 3 в мастере​ запятые, замените их​.​ СЧЁТЕСЛИ и СЧЁТЕСЛИМН​нажмите клавишу​ в ячейке с​ равно​ ячейке значение отображается​

Поиск текста и специальных знаков

​ — формат ячеек​ пересчитались, попробуйте F9​ циклическую ссылку. честно-честно.​ ячейки вбита простейшая​А вот эта же​введите​ формата в числовой.​, а затем —​. После применения формата​ в разделе​ на знаки «минус».​Если используется Windows, ошибка​См. статью Исправление ошибки​F2​

Пример с ошибкой #ЗНАЧ!

​ суммой также ошибку​Dosex​ 0.​ с формулой был​Тати​ интересно сколько бы​ формула, которая при​ формула с добавленной​=СУММ(A2:C2)​Ошибку #ЗНАЧ! могут вызвать​Заменить​ результат будет​Формат данных столбца​

Этот же пример с функцией ЕТЕКСТ

​Нажмите клавишу ВВОД.​ #ЗНАЧ! может возникнуть​ #ЗНАЧ! в функции​(т.е. войдите в​ #ДЕЛ/0. Создаю формулу​: Возможно вы пропустили​Не могу никак​ текстовый, и формулы​: Pelena, да, изменив​ еще так мучалась.​ замене определенных данных​ функцией ЕСЛИОШИБКА. Она​. Или вместо​ текст и специальные​, когда вы будете​31.12.2017​установите переключатель​Повторите эти действия для​ даже при вводе​ ДАТАЗНАЧ​

​ Режим Правки ячейки)​ типа =СУММ (ЕСЛИ​ в самом начале​ понять в чем​ не работали. Поменял​​ цифры и нажав​Сергей​ должна соотвественно тоже​​ означает:​=A2*B2​ знаки в ячейке.​ уверены, что пробел​

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

​, причем это будет​дата​ других ячеек, в​ самой обычной формулы​См. статью Исправление ошибки​ или поставьте курсор​ (ЕОШИБКА (E4:E34);0;E4:E34)) для​ строки знак равенства​ может быть дело​ на общий, кликнул​ F9 меняется результат.​: странно а меня​ меняться. Но когда​»Рассчитать формулу, но если​введите​​ Но иногда сложно​​ не нужен. После​​ настоящая дата, а​​.​​ которых возникает ошибка.​​ вычитания. Проблему можно​​ #ЗНАЧ! в функции​​ в Строку формул;​

Другие решения

Определение источника ошибки

Выберите ошибку

​ того чтобы обнулить​ «=»​

Щелкните «Формулы» > «Вычислить формулу»

​vikttur​​ на формуле -​​ Т.е. так надо​ при открытии вашего​ я меняю данные​ возникнет какая-либо ошибка,​=ПРОИЗВЕД(A2;B2)​ понять, в каких​​ этого ошибка #ЗНАЧ!​ не ее текстовая​​Выберите формат даты и​Введите две даты в​ решить следующим образом.​ ДНИ​нажмите​ эти ошибки в​K-s1(злой)​: Циклическая ссылка. У​ заработало.​ делать после каждого​​ файла сразу погнал​​ в исходной ячейке,​

Замена ошибки #ЗНАЧ! другим значением

​ заменить ее двумя​.​ именно ячейках они​ должна исчезнуть. Если​ запись.​ нажмите кнопку​ двух отдельных ячейках.​Для начала выполните быструю​См. статью Исправление ошибки​ENTER​ массиве и суммировать​: если в ячейке,​ Вас зациклены вычисления.​Iren​ изменения в формуле​ в справку​ то в в​ дефисами».​Сначала выделите ячейку с​ присутствуют. Решение: используйте​ нет — перейдите к​Примечание:​Готово​

​ В третьей ячейке​​ проверку. В новой​ #ЗНАЧ! в функциях​.​ значения. НО итого​ то формула не​ Этого допускать нельзя.​: Спасибо за ответ!​ …​vikttur​ ячейке, которой вбита​Обратите внимание, что​ ошибкой #ЗНАЧ!.​ функцию ЕТЕКСТ для​ следующему шагу.​ Приведенная выше формула написана​.​ вычтите одну ссылку​

Ячейка с ошибкой #ЗНАЧ!

​ книге введите 2​ НАЙТИ, НАЙТИБ, ПОИСК​Примечание:​ выдает «0», хотя​

Ошибка, скрытая функцией ЕСЛИОШИБКА

​ правильно написана, если​Татьяна1989​Serge_007​Или можно где​​: Возможно, файл открывается​ формула все остается​ вы также можете​Щелкните​​ проверки ячеек. Обратите​Иногда из-за скрытых символов​ с помощью функций​​Повторите эти действия для​​ на ячейку из​ в ячейке A1.​ и ПОИСКБ​ Мы стараемся как можно​ это не так.​​ в строке, то​​: Не вижу где​

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

Проверка подключений к данным

​ даты, прав, MIDи​ других столбцов, чтобы​ другой. В этом​ Введите 4 в​См. статью Исправление ошибки​ оперативнее обеспечивать вас​ При чем в​ ни как, так​ цикл….подскажите как правильно?​ в том когда​ настройках этот ручной​ файлах, у которых​ пока не щелкнешь​»»​> вычислить формулу​ ЕТЕКСТ не устраняет​ пробелов) ячейка может​ Left . Обратите​ убедиться, что они​ примере ячейка D10​ ячейке B1. Затем​ #ЗНАЧ! в функции​ актуальными справочными материалами​​ окне создания формулы​​ и должно быть​​vikttur​​ в ней появился​​ пересчёт?​​ итерации разрешены, и​​ по ней дважды​​, чтобы ничего не​ > вычислить. Excel​ ошибку, она просто​выглядеть​ внимание, что оно​

Использование форума сообщества, посвященного Excel

​ не содержат пробелы​ содержит дату начала,​ введите формулу​ ЕСЛИ​ на вашем языке.​ итоговую сумму показывает​

​Viktor maer​: Например, в​

См. также

​ этот формат: до​Pelena​

​ в файл автоматически​ мышкой.​

support.office.com

Что с excel может быть? формулы в ячейках не считают.

​ отображать вместо двух​ будет пошагово пройти​ находит ячейки, которые​пустой, хотя​ написано с учетом​ перед датами.​ а ячейка E10 —​=B1-A1​См. статью Исправление ошибки​ Эта страница переведена​ верно. Что я​: А я ставлю​I4​ введения формулы или​: Изменить можно в​ устанавливается режим итеративных​
​То же самое​ дефисов. Или вы​ по части формулы​ могут ее вызывать.​на самом деле​ того, что в​Проверка параметров даты на​ дату окончания. F10​в ячейке C1.​ #ЗНАЧ! в функциях​

​ автоматически, поэтому ее​​ делаю не так?​ + в начале​
​ссылка на саму​ после. Если до,​

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

Всего одна ячейка не считает формулу, а показывает 0

​ ИНДЕКС и ПОИСКПОЗ​​ текст может содержать​ Заранее спасибо.​ формулы.​ себя:​ то будет отображаться​В 2010 Файл​Тати​ формулами. К примеру​ текст, например:​ этом случае формула​ ошибкой #ЗНАЧ!. Ошибка,​ Например, это может​ два знака для​

​Excel полагается на систему​​=E10-D10​ #ЗНАЧ!, перейдите к​См. статью Исправление ошибки​ неточности и грамматические​Алексей матевосов (alexm)​Лесник​

​СУММЕСЛИ(B:B;A4;I:I)​​ формула — если​

​ — Параметры —​​: Форумчане, добрый вечер.​ простые формулы не​»Ошибка суммирования»​= E2 + E3​

​ скорее всего, возникает​​ происходить из-за одинарных​ дней, два знака​ дат вашего компьютера.​

​.​​ следующему шагу. Если​
​ #ЗНАЧ! в функциях​ ошибки. Для нас​: У вас получается​: Сервис, Параметры, Вкладка​Кстати, в конце​

​ после, то останется​​ Формулы — Параметры​ Подскажите в чем​ считаются, пока так​.​

​ + E4 +​​ из-за ячейки E2.​ кавычек в ячейке.​ для месяцев и​ Если дата в​Введите две даты в​ сообщение об ошибке​ НАЙТИ, НАЙТИБ, ПОИСК​

planetaexcel.ru

Не считаются формулы (Формулы)

​ важно, чтобы эта​​ формула массива.​ Вид, снять галку​ этой же формулы​ значение.​ вычислений — переключатель​ причина, где прочитать​ же не щелкнешь​К сожалению, вы видите,​»вниз» в случае,​ Здесь есть специальный​ Чтобы убрать эти​ четыре знака для​ ячейке введена в​ двух отдельных ячейках.​ не появилось, попробуйте​ и ПОИСКБ​ статья была вам​Ввод таких формул​ возле «формулы» в​ непонятная ссылка​Iren​ АВТОМАТИЧЕСКИ​ или куда нажать?​ по ячейке их​ что ЕСЛИОШИБКА не​ если в ячейке​ знак, который выглядит​ символы из столбца,​ года. Возможно, вам​ другой системе дат,​

​ В третьей ячейке​​ другие решения на​​См. статью Исправление ошибки​​ полезна. Просим вас​ завершают не нажатием​​ параметрах окна.​​С​

​, у Вас в​​В 2003 Сервис​
​ На работе для​ содержащей дважды. Что​ разрешает ошибку, она​

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

​ используйте функцию РАЗНДАТ,​​ этой странице.​ #ЗНАЧ! в функции​ уделить пару секунд​ на Enter, а​Светлана левина​:​

​ файле стоит формат​​ — Параметры —​ пользователей файлу был​ может быть? Эксель​ просто скрывает ее.​ пространство. В ячейке​

​ «00». Или, как​​ выбрав​ в соответствии с​ ее как настоящую​ чтобы найти разницу​В Windows откройте панель​ СРЗНАЧ или СУММ​ и сообщить, помогла​​ комбинацией клавиш Ctrl+Shift+Enter​: Спасибо! Лесник Мыслитель!​СУММЕСЛИ(H:H;G4;С)​ ячейки «Текстовый».​

​ Вычисления​​ дан общий доступ,​ 2007г.​
​ Поэтому убедитесь в​ E2 не отображается​ показано на следующем​Главная​ датой.​
​ дату.​ дат. Дополнительные сведения​ управления «Региональные стандарты».​

​См. статью Исправление ошибки​​ ли она вам,​Поставьте курсор на​

excelworld.ru

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

​ Уже больше часа​​Татьяна1989​
​Выберите формат «Общий»​Тати​ чтобы корректировки вносились​Алексей матевосов (alexm)​ том, что скрытие​ свободное место. Тем​ рисунке, можно использовать​>​Часто ошибка #ЗНАЧ! возникает,​Например, предположим, что компьютер​ о функции РАЗНДАТ​

​Windows 10. В строке поиска​​ #ЗНАЧ! в функциях​
​ с помощью кнопок​ формулу в строке​ читаю советы, только​

​: В ячейке L4​​ — F2 -​: Pelena, СПАСИБО!!! ОГРОМНОЕ​ сразу несколькими пользователями.​: Зайдите в «Параметры»​ ошибки лучше, чем​ не менее вы​ функцию ЕТЕКСТ в​Сортировка и фильтр​ потому что формула​ отображает даты в​ см. в статье​ введите​ СУММЕСЛИ и СУММЕСЛИМН​ внизу страницы. Для​ формул и нажмите​ Ваш помог! Ура!​ (желтым выделено) тоже​ Enter. Вы увидите​ СПАСИБО!!! Все получилось!!!​ Сегодня взяла файл​ на вкладку «Вычисления»​ исправление.​

​ можете увидеть это​​ отдельном столбце для​

​>​​ ссылается на другие​ формате мм.дд.гггг. Если​ Вычисление разницы двух​регион​См. статью Исправление ошибки​ удобства также приводим​ комбинацию клавиш. При​у меня криво отображается​ не считает, там​ значение.​Iren​ домой чтобы добавить​

​Поствте точку вычисления​​Подключение к данным может​

​ здесь. Он отображается​​ поиска текста.​Фильтр​ ячейки, содержащие пробелы​ ввести такую дату​ дат.​и выберите панель​ #ЗНАЧ! в функции​ ссылку на оригинал​ этом формула будет​ фомула в excel​
​ нет ссылок на​​Гость​: Здравствуйте, уважаемые форумчане!​ еще 2 строки,​
​ «автоматически»​ быть недоступно в​ как​Здесь функция ЕОШИБКА была​

​.​​ или (что еще​ в ячейке, Excel​Растяните столбец по ширине.​ управления​ СУММПРОИЗВ​ (на английском языке).​ обрамлена фигурными скобками​ — =СУММ (R[-9]C:R[-1]C).​

​ саму себя​​: Отображались формулы. Значений​Недавний переход на​ а формулы не​
​Станислав мостовой​ какой то же​»»​ добавлена в столбец​Щелкните стрелку фильтра​ сложнее) скрытые пробелы.​ распознает ее как​ Если значение выравнивается​Региональные стандарты​См. статью Исправление ошибки​Ошибка #ЗНАЧ! в Excel​{=СУММ (ЕСЛИ (ЕОШИБКА​как сделать верное​vikttur​

​ Excel не выдавал.​​ эксель 2007 вызвал​ считают, точнее они​

​: На ячейке щелкните​​ момент. Чтобы исправить​.​ F. Все ячейки​

​и снимите флажок​​ Из-за этих пробелов​ дату, а вы​ по правому краю —​

​.​​ #ЗНАЧ! в функции​ означает:​ (E4:E34);0;E4:E34))}​

excelworld.ru

СУММЕСЛИ в окошке формулы считает верно, а в ячейке выдает ответ 0

​ отображение формулы​​: Формула ссылается на​ ПОМОГ перевод всех​ проблемы, в частности​ считают, но чтобы​ правой кнопкой мыши​ ошибку, восстановите подключение​Иногда вы хотите просто​
​ являются подмножеством, за​Выделить все​ ячейка может​

​ сможете использовать ее​​ это дата. Но​Windows 8. На начальном экране​ ВРЕМЗНАЧ​

​»Формула вводится неправильно. Или​​А сумма в​_Клондайк_​

​ ячейки, которые в​​ ячеек листа в​​ ячейка почему-то отображает​​ увидеть результат (например,​ и выберите формат​
​ к данным или​
​ заменить #VALUE! сообщение​ исключением того, что​. Затем установите флажок​​выглядеть​​ в формуле вычитания.​
​ если оно выравнивается​

​ введите​​См. статью Исправление ошибки​ что-то не так​ ячейке будет верной.​: =R[-21]C+R[-14]C-R[-7]C у меня​ свою очередь (возможно,​

​ именно ФИНАНСОВЫЙ формат,​​ формулу вместо ее​ просто суммирование нескольких​ ячеек. Выберите формат​ попробуйте импортировать данные,​ об ошибке, похожее​ оно имеет значение​Пустые​пустой, хотя на​

​ Однако если ввести​​ по левому краю,​регион​ #ЗНАЧ! в функции​

​ с ячейками, на​​Андрей власов​ тоже так, а​ через цепь ячеек)​ а затем назад​

​ значения. Флажок «отображать​​ столбцов) правильно ли​

planetaexcel.ru

В Excel вместо значения формулы (число) показывается сама формула. Как исправить?

​ «Числовой» и задайте​​ если это возможно.​ на собственный текст,​ истина. Это означает,​.​ самом деле таковой​ дату в формате​ это значит, что​, щелкните​ ТРАНСП​ которые указывают ссылки».​: Ну правильно, в​ какая разница, лишь​ ссылаются на эту​ в общий. Прочие​ формулы» снят. Возникает​

​ я их занесла​​ количество знаков в​ Если у вас​

​ ноль или пустая​​ что ячейка E2​

​Установите все флажки, напротив​​не​ мм.дд.гг, Excel не​

​ в ячейке на​​Параметры​См. статью Исправление ошибки​

​Эта ошибка возникает​​ диапазоне с…по…есть ошибка,​ бы считала правильно,​ формулу. Или формула​ манипуляции не помогали.​

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

​является.​​ распознает ее как​ самом деле не​и выберите пункт​

​ #ЗНАЧ! в функции​​ в самых разных​ значит 0.​ эксель не мой,​ ссылается на зацикленные​

​Shevrol​​ формулу с клавиатуры.​ файла и заново​Скорее всего у​ подключению, попросите создателя​

Неверно отображается формула в excel

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

​ дата. Это текст.​​Региональные стандарты​ ВПР​ случаях, и найти​Проверку ошибок надо​ на работе такой​ вычисления.​: Я обычно решаю​ Подскажите, пожалуйста, как​ зайти, очень не​ Вас неправильный формат.​

​ книги создать новый​​ добавить функцию ЕСЛИОШИБКА​

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

​ это устранить.​​ удобно. При этом​
​ Удачи.​ файл. В идеале​

​ в формулу. ЕСЛИОШИБКА​​ содержимое ячейки и​Если Excel вернет пустые​ выделите их. Во​ как текст.​ распознает текст как​Windows 7. Нажмите кнопку​ в этом списке?​

Почему в excel не отображается результат вычислений?

​ может быть сложно.​ ячейке, где может​ желания заморачиваться и​: Ну, вот. Удалите​ методом.​Hugo​ файл открывается в​111Kseniya​ в новом файле​ будет проверять, есть​ ввести значение 1865,00.​ ячейки, выделите их.​ многих случаях рекомендуется​Существует два решения этой​ дату. Ниже приведены​Пуск​ Попробуйте другие решения,​ Сведения на этой​ быть деление на​ что то там​ формулы из I4​Пишете эту же​: Здравствуйте.​ режиме совместимости. Что​

​: Всего в одной​​ должны быть только​ ли ошибки, и,​
​ Кроме того, можно​ Затем нажмите клавишу​ удалить пробелы для​ проблемы: Вы можете​
​ некоторые решения, которые​, введите​ перечисленные ниже.​ странице включают распространенные​ 0.​ менять​
​ и I39​ формулу в другой​
​Проверьте формат ячейки​ это? Какую кнопку​

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

​Если вы раньше не​

Как в эксель формулу в текст

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

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

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

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

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

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

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

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

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

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

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

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

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

Как преобразовать формулу в текст в 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), вставляем новый модуль и добавляем следующий код:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ТЕКСТ (функция ТЕКСТ)

С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.

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

Аргументы функции ТЕКСТ описаны ниже.

Числовое значение, которое нужно преобразовать в текст.

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

Общие сведения

Самая простая функция ТЕКСТ означает следующее:

=ТЕКСТ(значение, которое нужно отформатировать; «код формата, который требуется применить»)

Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.

Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 234,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части.

Сегодняшняя дата в формате ДД/ММ/ГГ, например: 14.03.12

Сегодняшний день недели, например: понедельник

Текущее время, например: 13:29

Процентный формат, например: 28,5 %

Дробный формат, например: 4 1/3

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

Экспоненциальное представление, например: 1,22E+07

Скачивание образцов

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

Другие доступные коды форматов

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

Нажмите клавиши CTRL+1 (на компьютере Mac — +1), чтобы открыть диалоговое окно Формат ячеек.

На вкладке Число выберите нужный формат.

Выберите пункт (все форматы).

Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.

Нажмите клавиши CTRL+C, чтобы скопировать код формата, а затем — кнопку Отмена, чтобы закрыть диалоговое окно Формат ячеек.

Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;»ДД.ММ.ГГГГ«). Обязательно заключите скопированный код формата в кавычки («код формата»), иначе в Excel появится сообщение об ошибке.

«Ячейки» > «Число» > «Другое» для получения строк формата.» />

Коды форматов по категориям

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

• Выбор числового формата
• Нули в начале
• Разделитель групп разрядов.
• Числовые, денежные и финансовые форматы
• Даты
• Значения времени
• Проценты
• Дроби
• Экспоненциальное представление
• Дополнительные форматы

Почему программа Excel удаляет нули в начале?

Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.

Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;»00000″), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.

Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (—), например: =—D4.

В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если используется код формата «# ###», число 12200000 отображается как 12 200 000.

Пробел после заполнителя цифры задает деление числа на 1000. Например, если используется код формата «# ###,0 «, число 12200000 отображается в Excel как 12 200,0.

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

Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.

Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).

Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac — +1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.

После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.

Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне «Формат ячеек» код формата, в котором используется цвет, например «# ##0,00 ₽;[Красный]# ##0,00 ₽», то функция ТЕКСТ воспримет его, но цвет отображаться не будет.

Способ отображения дат можно изменять, используя сочетания символов «Д» (для дня), «М» (для месяца) и «Г» (для года).

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «М» и «м», «Д» и «д», «Г» и «г».

Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.

Способ отображения времени можно изменить с помощью сочетаний символов «Ч» (для часов), «М» (для минут) и «С» (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы «AM/PM».

Если не указывать символы «AM/PM», время будет отображаться в 24-часовом формате.

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «Ч» и «ч», «М» и «м», «С» и «с», «AM/PM» и «am/pm».

Для отображения десятичных значений можно использовать процентные (%) форматы.

Десятичные числа можно отображать в виде дробей, используя коды форматов вида «?/?».

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

В Excel доступны четыре дополнительных формата:

«Почтовый индекс» («00000»);

«Индекс + 4» («00000-0000»);

Типичный сценарий

Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку «Отчет напечатан 14.03.12» или «Еженедельный доход: 66 348,72 ₽». Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например «ДД.ММ.ГГГГ» для дат.

В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (» «) и значения: =A2&» «&B2.

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

Вот обновленная формула:

ячейка C2:=A2&» «&ТЕКСТ(B2;»дд.мм.гггг») — формат даты.

Вопросы и ответы

К сожалению, это невозможно сделать с помощью функции ТЕКСТ. Для этого нужно использовать код Visual Basic для приложений (VBA). В следующей статье описано, как это сделать: Как преобразовать числовое значение в слова в Excel

Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН(«привет») возвращает результат «ПРИВЕТ».

Да, но для этого необходимо выполнить несколько действий. Сначала выделите нужные ячейки и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек. Затем на вкладке Выравнивание в разделе «Отображение» установите флажок Переносить по словам. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.

В этом примере использована формула =»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ»).

Это экспоненциальное представление числа. Excel автоматически приводит к такому виду числа длиной более 12 цифр, если к ячейкам применен формат Общий, и числа длиннее 15 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они отображались в таком виде, то сначала примените к соответствующим ячейкам формат Текстовый.

Мои рабочие заметки

Подписаться на этот блог

Follow by Email

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

• Получить ссылку
• Facebook
• Twitter
• Pinterest
• Электронная почта
• Другие приложения

Для выполнения данной операции в Excel предусмотрен механизм копирования.

Для этого нужно воспользоваться помощью контекстного меню. Как?
Необходимо выделить ячейку(диапазон ячеек) с формулой, после чего правой кнопкой мыши вызвать контекстное меню и выбрать «Копировать только значения»(Copy Here as Values Only).

• Получить ссылку
• Facebook
• Twitter
• Pinterest
• Электронная почта
• Другие приложения

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

ГИС ГМП. Подключение, тестирование, работа.

ГИС ГМП 2.0 / СМЭВ 3.XX (Актуальные адреса для обращений к ЕСМЭВ 3.0)

TRUNCATE/ DELETE/ DROP. Или как очистить таблицу

Дано: Таблица MSG (сообщения) с большим количеством записей.
CREATETABLEmsg( idINTEGERNOTNULLPRIMARYKEY,descriptionCHAR(50)NOTNULL, date_createDATE);
Задача:
Необходимо очистить таблицу от данных/
Решение: Для решения данной задачи есть несколько способов. Ниже описание и пример каждого из них.
Самый простой способ (первый вариант) — выполнение оператора удаления записи. При его выполнении вы будете видеть результат (сколько записей удалено). Удобная штука когда необходимо точно знать и понимать правильные ли данные удалены. НО имеет недостатки перед другими вариантами решения поставленной задачи.

DELETE FROMmsg;—Удалит все строки в таблице—Удалит все строки у которых дата создания «2019.02.01» DELETE FROMmsg WHEREdate_create=’2019.02.01′;

Второй вариант. Использование оператораDML для очистки всех строк в таблице.
TRUNCATETABLEmsg;
При этом несколько особенностей использования данного оператора:
Его нет в Firebird, поэтому пользуемся первым и третьим вариантом.После выпо…

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

Устранение проблем с вычислением

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

Способ 1: изменение формата ячеек

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

1. Для того, чтобы посмотреть, какой формат имеет конкретная ячейка или диапазон, переходим во вкладку «Главная». На ленте в блоке инструментов «Число» имеется поле отображения текущего формата. Если там указано значение «Текстовый», то формула точно вычисляться не будет.



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



3. Но выбор типов формата через ленту не такой обширный, как через специализированное окно. Поэтому лучше применить второй вариант форматирования. Выделяем целевой диапазон. Кликаем по нему правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек». Можно также после выделения диапазона нажать комбинацию клавиш Ctrl+1.



4. Открывается окно форматирования. Переходим во вкладку «Число». В блоке «Числовые форматы» выбираем тот формат, который нам нужен. Кроме того, в правой части окна имеется возможность выбрать тип представления конкретного формата. После того, как выбор произведен, кликаем по кнопке «OK», размещенной внизу.



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

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

Способ 2: отключение режима «Показать формулы»

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

1. Чтобы включить отображение итогов, переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул», если кнопка «Показать формулы» активна, то кликаем по ней.



2. После этих действий в ячейках снова вместо синтаксиса функций начнет отображаться результат.

Способ 3: исправление ошибки в синтаксисе

Формула также может отображаться как текст, если в её синтаксисе были допущены ошибки, например, пропущена или изменена буква. Если вы вводили её вручную, а не через Мастер функций, то такое вполне вероятно. Очень распространенной ошибкой, связанной с отображением выражения, как текста, является наличие пробела перед знаком «=».

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

Способ 4: включение пересчета формулы

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

1. Перейдите во вкладку «Файл». Находясь в ней, следует кликнуть по пункту «Параметры».



2. Откроется окно параметров. Нужно перейти в раздел «Формулы». В блоке настроек «Параметры вычислений», который расположен в самом верху окна, если в параметре «Вычисления в книге», переключатель не установлен в позицию «Автоматически», то это и есть причина того, что результат вычислений неактуальный. Переставляем переключатель в нужную позицию. После выполнения вышеуказанных настроек для их сохранения в нижней части окна жмем на кнопку «OK».

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

Способ 5: ошибка в формуле

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

• #ЧИСЛО!;
• #ЗНАЧ!;
• #ПУСТО!;
• #ДЕЛ/0!;
• #Н/Д.

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

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

1. Выделяем ячейку с ошибкой. Переходим во вкладку «Формулы». На ленте в блоке инструментов «Зависимости формул» кликаем по кнопке «Вычислить формулу».



2. Открывается окно, в котором представляется полный расчет. Нажимаем на кнопку «Вычислить» и просматриваем вычисление пошагово. Ищем ошибку и устраняем её.

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

Содержание

• 1 Преобразование формулы в текст в Excel
• 2 Функция Ф.ТЕКСТ в Excel
• 3 Замечания
• 4 Пользовательская функция (UDF)
• 5 Процедура вставки текста около формулы
• 6 Способ 1: использование амперсанда
• 7 Способ 2: применение функции СЦЕПИТЬ
  • 7.1 Помогла ли вам эта статья?

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

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

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

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

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

• Функция Ф.ТЕКСТ (доступна начиная с версии 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

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

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

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

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

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

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

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

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

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

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

	Здесь можно получить ответы на вопросы по Microsoft Excel	58510	478918	8 Ноя 2018 19:00:01
		44519	357828	29 Янв 2017 17:28:40
	Лучшие избранные темы с основного форума	14	80	28 Июн 2018 15:25:11
	Если вы — счастливый обладатель Mac 😉	219	1065	25 Окт 2018 09:26:29
	Раздел для размещения платных вопросов, проектов и задач и поиска исполнителей для них.	2138	13647	8 Ноя 2018 16:58:12
	Если Вы скачали или приобрели надстройку PLEX для Microsoft Excel и у Вас есть вопросы или пожелания — Вам сюда.	316	1615	6 Ноя 2018 20:49:07
		820	11946	8 Ноя 2018 15:55:43
	Обсуждение функционала, правил и т.д.	270	3481	30 Окт 2018 15:01:36

Сейчас на форуме (гостей: 1220, пользователей: 17, из них скрытых: 3) , , , , , , , , , , , , ,

Сегодня отмечают день рождения (41), (32), (3), (39), (38), (33), (28)

Всего зарегистрированных пользователей: 83822

Приняло участие в обсуждении: 32154

Всего тем: 106806