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

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

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

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

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

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

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

При замене формул их значениями 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.

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

Преобразование в MS EXCEL ЧИСЕЛ из ТЕКСТового формата в ЧИСЛОвой (Часть 1. Преобразование формулами)

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

Пусть из ячейки A1 содержащей «101 далматинец» с помощью формулы =ЛЕВСИМВ(A1;3) извлекли число 101. Число 101 будет сохранено в текстовом формате, т.к. эта функция возвращает только текстовые строки. Это дальнейшем может привести к ошибке. Например, при сложении функцией СУММ() такие значения попросту игнорируются (см. статью Функция СУММ() и операция сложения), так же как и при подсчете функцией СЧЁТ() , СУММЕСЛИ() и пр.

Читать еще:  Формула в excel если значение меньше 0 то равно 0

Исправить формат возвращаемого значения можно добавив функцию ЗНАЧЕН() : =ЗНАЧЕН(ЛЕВСИМВ(A1;3)) . Функция ЗНАЧЕН() преобразует значение, где это возможно, в число.

Альтернативным решением является использование формулы =ЛЕВСИМВ(A1;3)+0 или =—ЛЕВСИМВ(A1;3) или =ЛЕВСИМВ(A1;3)*1 . Два подряд знака минус дают + и заставляют EXCEL попытаться сделать вычисления с результатом возвращенным функцией ЛЕВСИМВ() . В случае успеха, если функцией ЛЕВСИМВ() действительно было возвращено число, у значения будет изменен формат на числовой.

Этот вариант позволяет преобразовывать не только в числовой формат, но и формат даты. Введем в ячейку А1 текст «1.05.2002 продажа», в ячейку B1 формулу =ЛЕВСИМВ(A1;9) , а в ячейку C1 формулу =B1+0 . В итоге, в B1 получим 1.05.2002 в текстовом формате, а в С1 получим уже обычную дату «01.05.02», точнее число 37377 (чтобы увидеть дату, формат ячейки нужно установить Дата).

Некоторые программы бухгалтерского учета отображают отрицательные значения со знаком минус (-) справа от значения. После копирования в EXCEL они сохраняются как текстовые значения. Чтобы преобразовать эти текстовые значения в числа, необходимо извлечь из него все цифры, и умножить результат на -1. Например, если в ячейке A2 содержится строка «4116-», следующая формула преобразует этот текст в значение -4116.

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

Примечание. При разделении содержимого ячеек (например, «101 далматинец») по различным столбцам с помощью инструмента Текст-по-столбцам (на вкладке Данные в группе Работа с данными пункт Текст-по-столбцам) проблем с определением формата не возникает: если значение может быть преобразовано в числовой формат, то оно будет преобразовано.

Преобразовать текст в число Excel, или как сделать ячейку текстового формата числовым форматом

Все ячейки в Excel имеют тот или иной формат, который определяет, что можно делать с данными в ячейке. Например, значение «7» может быть как числом (и можно выполнять математические действия), а может быть и текстом, то есть просто символом, с которым математические действия выполнять нельзя. И это создает сложности, решением которых будет перевести текст (символы) в число в Excel.

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

Как определить, где в ячейке текст, а где число?

  • То, что выровнено по левому краю ячейки, это — текстовый формат, по правому краю — число (если сам пользователь не применил выравнивание принудительно). Однако выравнивание ячейки по левому и правому краю через инструмент Выравнивание не меняет формата ячейки.
  • Нажать на ячейку и в верхнем меню на вкладке Главная найти формат: общий, текстовый, числовой и т.д.

Как перевести ячейку из текстового в числовой формат?

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

Выйти на то же меню можно и другим способом: выделите ячейки и нажмите правую кнопку мыши (ПКМ), в открывшемся меню выберите Формат ячеек.

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

Еще один способ преобразовать текст в число в Excel — использование формулы. Впишите в пустую соседнюю ячейку формулу. Для этого левой кнопкой мыши кликните на соседнюю ячейку и напишите формулу =ЗНАЧЕН(E4) (возможные названия: =ЗНАЧ(E4) или =VALUE(E4) зависит от используемой версии программы). ENTER.

Обратите внимание!

«Преобразовать текст в число Excel» — не совсем правильная формулировка. Правильнее будет сказать: «Преобразование из текстового формата в числовой».

Автор: Пашкова Екатерина Ивановна, преподавать основ компьютерной грамотности, офисных программ.

Автор скриншотов: Белицкий Алексей Викторович, учитель МКОУ «СОШ №7», с. Пелагиада, Шпаковский район, Ставропольский край.

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

Читать еще:  Формулы в эксель

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

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

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

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

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

2007-2019 «Педагогическое сообщество Екатерины Пашковой — PEDSOVET.SU».
12+ Свидетельство о регистрации СМИ: Эл №ФС77-41726 от 20.08.2010 г. Выдано Федеральной службой по надзору в сфере связи, информационных технологий и массовых коммуникаций.
Адрес редакции: 603111, г. Нижний Новгород, ул. Раевского 15-45
Адрес учредителя: 603111, г. Нижний Новгород, ул. Раевского 15-45
Учредитель, главный редактор: Пашкова Екатерина Ивановна
Контакты: +7-920-0-777-397, info@pedsovet.su
Домен: http://pedsovet.su/
Копирование материалов сайта строго запрещено, регулярно отслеживается и преследуется по закону.

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

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

сервис вебинаров

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

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

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

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

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

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

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

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

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

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

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

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


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


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

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


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

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

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

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

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

  1. Выделяем диапазон с формулами на листе
  2. Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
  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, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

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

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

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

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, поэтому пользуемся первым и третьим вариантом.После выпо…

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
×
×
Adblock
detector