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

Обновлено: 06.07.2024

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

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

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

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

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

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

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

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

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

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

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

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

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

Выделяем диапазон с формулами на листе
Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .

После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Замена формул вычисляемой величиной

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

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

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

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

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

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

Щелкните Текущий массив.

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

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

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

Нажмите F9, а затем ввод.

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

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

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

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

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

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

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

Чтобы вычислить выбранную часть, нажмите F9.

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

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

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

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

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

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

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

Если его упустить – содержимое будет воспринято, как текст. Добавьте знак равенства и пересчитайте формулу.
Пробел перед «=». Если перед знаком равенства случайно указан пробел, это тоже будет воспринято, как текст.

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

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

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

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

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

5 комментариев

В экселе есть баг. Наша компания даже создавал инцидент, но Microsoft написал, что устранен будет в следующих версиях.
Баг: иногда, если формат указан как текстовый, то ексель не воспринимает формулу как формулу, даже если сменить формат. Это могут быть две одинаковые ячейки с одинаковыми параметрами, но в одной ексель рассчитывает формулу, а в другой нет.
Лечение: заменить формат на общий, дважды щелкнуть на ячейку, нажать Enter.

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

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

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

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

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

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

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

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

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

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

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

0 Спам не работает. Как была формула так и остается. При чем значение не вычисляется. Т.е. далее следующая ячейка при вычислении должна использовать это значение, а его нет, вместо него формула так и осталась прописана, из за этого вся таблица не работает 0 Спам И ещё: после написания формулы в "строке формул" (формула при этом отображается в ячейке) нужно не забыть --> по иконке "Сохранить". И тогда в ячейке далее будут отображаться значения. Жаль что у самих преподавателей нет желания разрешить конфликт. Им дана власть н

Девиз: поднемите руки выше!
по

Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.

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

О работе с сайтом

Мы используем cookie.

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

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

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

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

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

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

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

Вы можете скачать этот шаблон Formula Excel здесь - Показать шаблон Formula Excel.

Вот пример данных, используемых для примеров:

Мы рассмотрим несколько примеров, чтобы лучше это понять.

Пример № 1 - Двойной щелчок одной ячейки для показа формулы

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

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

Теперь вам нужно перейти на вкладку формулы и щелкнуть на опции «Показать формулы в Excel», как показано ниже:

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

Пример № 2 - Показать формулу в Excel

Теперь перейдите на вкладку «Показать формулу», после чего вы увидите формулы в ячейках столбца F, как показано ниже:

Поместив курсор перед формулой, вы увидите формулу в этой ячейке.

Это показывает формулу, а не результат.

Пример № 3 - включив параметр Показать формулу в книге

Перейдите к опции «Файл», теперь перейдите к «Опции»

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

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

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

Пример № 4 - Сочетание клавиш, чтобы увидеть формулу рабочей книги

Клавиши быстрого доступа клавиатуры (CTRL + `). Вы можете найти Ctrl с левой стороны и ` справа над кнопкой Tab на правой стороне клавиатуры.

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

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

Пример: использование нашего предыдущего примера данных, чтобы показать, как это работает.

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

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

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

Пример. В приведенных ниже примерах данных выберите все ячейки.

Теперь нажмите «Найти и выбрать », затем выберите «Заменить»

На вкладке замены введите «=» в поле «Найти что» и «=» в поле «Заменить на», а затем нажмите «Заменить все».

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

Что нужно помнить о показе формулы в Excel

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

Формулы Excel отображаются в виде текста и не показывают результат. Как показывает клетка:

= сумма (A1, B1), но не результат.

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

Это показано ниже:

Теперь сделайте форматирование от текста до общего

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