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

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

  • автор:

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

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

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

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

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

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

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

сразу после ввода или вставки значения.

  1. Выберите ячейку или диапазон ячеек, содержащих формулы. Если формула является формула массива, выделите диапазон ячеек, содержащих формулу массива. Выбор диапазона, содержащего формулу массива
    1. Щелкните ячейку в формуле массива.
    2. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.
    3. Нажмите кнопку Дополнительный.
    4. Щелкните Текущий массив.

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

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

    В строке формул показана формула.

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

    В строке формул показано значение.

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

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

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

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

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

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

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

    Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

    Нужно разорвать все абсолютно ссылки во всей книге python-скриптом, но чтобы значения остались. Можно макросом. Как это реализовать?

    Отслеживать
    49k 17 17 золотых знаков 56 56 серебряных знаков 100 100 бронзовых знаков
    задан 9 окт 2019 в 4:45
    Айжана Кадралеева Айжана Кадралеева
    11 1 1 бронзовый знак

    Тут описано много способов, посмотрите что вам подходит! В том числе с макросами. planetaexcel.ru/techniques/11/215

    9 окт 2019 в 4:48
    Нашла подходящее!
    9 окт 2019 в 5:54

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

    9 окт 2019 в 6:37

    1 ответ 1

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

    Это решение мне помогло. Пример макроса:

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

    Отслеживать
    8,379 3 3 золотых знака 18 18 серебряных знаков 24 24 бронзовых знака
    ответ дан 9 окт 2019 в 7:56
    Айжана Кадралеева Айжана Кадралеева
    11 1 1 бронзовый знак
    Здесь не принято публиковать код картинками. Посмотрите, я поправил удалённый ответ.
    9 окт 2019 в 8:08

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

    Замените формулу результатом в Excel для Mac

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

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

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

    Значок

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

    Paste Options button

    1. Выделите ячейку с формулой. Если формула вводится как формула массива, выделите диапазон, содержащий формулу массива.
    2. На вкладке Главная нажмите кнопку Копировать.
    3. На вкладке Главная нажмите кнопку Вставить.
    4. Щелкните стрелку рядом с элементом Параметры вставки

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

    Внимание: После замены части формулы ее значением восстановить эту часть не удастся.

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

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

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

    Разработчики могут возвращать формулы Microsoft Excel из представлений, хранимых процедур или SQL запросов.

    SELECT id , name , qty , price , '=@qty*@price' AS amount .

    Продукты SaveToDB преобразуют такие формулы в расчетные формулы, специфичные для каждой платформы (Excel, DataTable или JavaScript).

    Надстройка SaveToDB поддерживает практически все формулы Microsoft Excel. DBEdit, DBGate и ODataDB поддерживают подмножество формул.

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

    ConverFormulas и DoNotConverFormulas

    Разработчики могут включить или отключить преобразование формул, используя типы ConvertFormulas и DoNotConverFormulas в таблице xls.handlers.

    ID TABLE_SCHEMA TABLE_NAME COLUMN_NAME EVENT_NAME HANDLER_SCHEMA HANDLER_NAME HANDLER_TYPE HANDLER_CODE
    s02 usp_cashbook5 balance ConvertFormulas ATTRIBUTE
    xls handlers HANDLER_CODE DoNotConvertFormulas ATTRIBUTE

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

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

    Formula и FormulaValue

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

    ID TABLE_SCHEMA TABLE_NAME COLUMN_NAME EVENT_NAME HANDLER_SCHEMA HANDLER_NAME HANDLER_TYPE HANDLER_CODE
    s04 usp_order_form amount Formula ATTRIBUTE =@qty*@price
    s22 cashbook amount DoNotChange ATTRIBUTE

    Этот случай аналогичен представленному во введении.

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

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

    Это очень мощная возможность для пересчета всех значений колонки.

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

    Вторая возможность — это тип FormulaValue.

    ID TABLE_SCHEMA TABLE_NAME COLUMN_NAME EVENT_NAME HANDLER_SCHEMA HANDLER_NAME HANDLER_TYPE HANDLER_CODE
    s22 cashbook modified_by FormulaValue ATTRIBUTE =DomainUserName()
    s22 cashbook modified_on FormulaValue ATTRIBUTE =NOW()
    s22 cashbook modified_by DoNotChange ATTRIBUTE
    s22 cashbook modified_on DoNotChange ATTRIBUTE

    В отличие от типа Formula, значения рассчитываются в момент изменения пользователем любой ячейки строки.

    Вы можете использовать встроенные формулы Excel и специальные встроенные формулы: =DomainUserName() и =UserName().

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

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

    Например, ниже представлена конфигурация SQLite для получения данных акций из платформы Thinkorswim, с использованием SQL запроса и таблицы xls.objects:

    ID TABLE_SCHEMA TABLE_NAME TABLE_TYPE TABLE_CODE INSERT_OBJECT UPDATE_OBJECT DELETE_OBJECT
    DDE WatchList CODE WatchList WatchList WatchList

    где поле TABLE_CODE содержит следующий SQL запрос:

    SELECT Symbol , '=TOS|LAST!' + Symbol AS Last , '=TOS|NET_CHANGE!' + Symbol As NetChange , '=TOS|PERCENT_CHANGE!' + Symbol As Change , '=TOS|HIGH!' + Symbol As High , '=TOS|LOW!' + Symbol As Low , '=SUBSTITUTE(TOS|VOLUME!' + Symbol+'," ","")+0' As Volume FROM WatchList

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

    Свойства колонок
    Преобразование гиперссылок

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

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

Ваш адрес email не будет опубликован. Обязательные поля помечены *