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

Замена формулы на ее результат

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

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

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

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

Замена формул с помощью вычисляемых значений

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

Выделите ячейку или диапазон ячеек, содержащих формулы.

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

Выбор диапазона, содержащего формулу массива

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Нажмите кнопку Текущий массив.

Нажмите кнопку » копировать «.

Нажмите кнопку вставить .

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

В следующем примере показана формула в ячейке D2, которая умножает ячейки a2, B2 и скидку, полученную из C2, для расчета суммы счета для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой лист или в другую книгу, можно преобразовать формулу в ее ячейку в ее значение, выполнив указанные ниже действия.

Нажмите клавишу F2, чтобы изменить значение в ячейке.

Нажмите клавишу F9 и нажмите клавишу ВВОД.

После преобразования ячейки из формулы в значение оно будет отображено как 1932,322 в строке формул. Обратите внимание, что 1932,322 является фактическим вычисленным значением, а 1932,32 — значением, которое отображается в ячейке в денежном формате.

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

Замена части формулы значением, полученным при ее вычислении

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

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

Щелкните ячейку, содержащую формулу.

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

Чтобы вычислить выделенный фрагмент, нажмите клавишу F9.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

формулы->зависимости формул->проверка наличия ошибок
показывает что не правильно, но как исправить думать не хочется

ALL__: формулы->зависимости формул->проверка наличия ошибок
показывает что не правильно, но как исправить думать не хочется

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

Не пойму в чем вопрос. Если задать формат ячеек «Текст», то после записи формулы в неё она останется текстом (не формулой!).
Кроме того у Вас сами формулы с ошибками: «=A10+tekct(B10)» — (написано латинницей)такой функции нет, есть, в зависимости от языка, «TEXT» или «ТЕКСТ» (русскими буквами).

Да, с «ТЕКСТ»-ом латиницей действительно прокол.

Независимо от формата ячейки в ней должно производиться вычисление (по умолчанию). Формат ячейки определяет только формат результата вычислений.
Можно задать режим ручного пересчета — сначала вводятся значения ячеек-операндов, а затем дается команда «пересчитать». Но на данном листе режим автоматический.
Т.е. в ячейке не должно отображаться «=A4&B4», а должно быть «ПВ31»

Взводатор: Формат ячейки определяет только формат результата вычислений.

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

SAK: аче как написать текст начина

Все начинающееся с ‘ воспринимается как текст, это ведется еще со времен ДОСовского «Суперкалка».
Назначим ячейке формат «общий».
Поставим в ячейку =1+2 — увидим результант 3
Поставим ‘=1+2 — увидим =1+2
Формат в обоих случаях так и останется «общий»
Чтобы отобразить символ ‘ в начале строки, его надо предварить таким же.

Взводатор: все дело в формате именно той ячейки, которая содержит формулу
Вот именно. И изменить формат именно этой ячейки не получается — он самопроизвольно возвращается взад.
Зато получается изменить формат ВСЕХ ячеек листа — на «общий». И тогда формулы начинают восприниматься как формулы.

AN1440: Зато получается изменить формат ВСЕХ ячеек листа — на «общий». И тогда формулы начинают восприниматься как формулы.

Попробовал выделить все ячейки (левый верхний квадратик) и поставить формат «общий» — ничего не изменилось.

SAK: Формат ячейки ещё и определяет способ интерпретации вводимых в ячейку данных.

Да, но в дальнейшем возможно автоматическое изменение формата. Например, в этом файле ячейку В27 (содержит число 8) умножил на два, получил 16, выравнивание по правому краю т.е. как число по умолчанию. Изменил формат результирующей ячейки на «текстовый» — 16 выравнялось по левому краю, т.е. как текст по умолчанию. В соседней ячейке 16 умножаю на 2 и получаю 32 в виде текста. Добавляю 2 и получаю 18 тоже как текст.

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

Взводатор: Да, но в дальнейшем возможно автоматическое изменение формата.

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

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

Среди функций есть T() — возвращает значение выражения если оно текст, в противном случае пустую строку. Экспериментируем на новом листе:
Заносим A1: 3, B1: 4, C1: =A1+B1, D1: =Т(C1)
Видим в C1 — 7, в D1 — пусто.
Устанавливаем формат ячейки C1 — «Текст»
Видим что несмотря на изменение формата ячейки в D1 по прежнему пусто.
Вводим в C1 любой текст и он тут же появляется в D1.
Вводим в C1 формулу и видим эту формулу как в C1 так и в D1. Т.е. теперь там не формула, а текст.
Меняем формат C1 на «Общий» или «Числовой» — в C1 и D1 по прежнему отображается текст.
Вводим в C1 любое число или формулу — в D1 теперь пусто.

Взводатор: Изменил формат результирующей ячейки на «текстовый» — 16 выравнялось по левому краю, т.е. как текст по умолчанию. В соседней ячейке 16 умножаю на 2 и получаю 32 в виде текста. Добавляю 2 и получаю 18 тоже как текст.

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

Однако, обратите внимание на строку статуса (в самом низу Excel, под рабочими листами), там есть место где показывается сумма выделенных ячеек, причем сумма вычисляется только для числовых ячеек. Устанавливаем формат A1 «Текст», записываем в A1, A2, A3 число 1. Устанавливаем формат A2 — «Текст». А теперь выделяем ячейки A1:A3 и получаем сумму в строке статуса: 2. Т.е. изменение формата заполненной ячейки не приводит к преобразованию её содержимого из числового в текстовое, оно так и остаётся числовым.

——
PS Строго говоря надо бы разделить понятия «формат» и «тип» ячейки. Вот «текст», «число», «дата» и т.п. — это тип, а формат — это размер шрифта, выравнивание, направление текста и т.д.

PPS. Кстати, формат ячеек «дата» — это тоже числовой с особым отображением, дата там представлена количеством дней прошедших с 30 декабря 1899 года.

Microsoft Excel

трюки • приёмы • решения

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

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

Для осуществления этого действия перейдите на вкладку Формулы ленты инструментов Excel и нажмите на кнопку Показать формулы. Также вы можете использовать сочетание Ctrl+`.

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

1. Выберите ячейку, содержащую подлежащую преобразованию формулу.
2. Произведите на ней двойной щелчок мыши или используйте кнопку F2 для активации редактирования непосредственно в ячейке.
3. Нажмите F9. Формула будет преобразована в значение.
4. Нажмите на кнопку Enter. При этом Excel подтвердит изменения.

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

1. Выделите ячейку, содержащую необходимую формулу.
2. Скопируйте ячейку (используйте сочетание клавиш Ctrl+С).
3. Выберите ячейку или несколько ячеек, в которые необходимо скопировать результат.
4. Выберите пункт Главная ленты инструментов, далее активируйте раскрывающийся список действий под кнопкой Вставить и выберите пункт Вставить значения.

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