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

Обновлено: 21.11.2024

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

Например, если вы используете функцию Excel СЕГОДНЯ для вставки текущей даты в ячейку и оставляете ее в виде формулы, она будет обновляться, если вы откроете книгу на следующий день. Если это не то, что вам нужно, вам нужно преобразовать формулу в статическое значение.

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

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

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

Использование сочетания клавиш

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

Вот как это сделать:

Выберите ячейки, для которых вы хотите преобразовать формулы в значения.
Скопируйте ячейки (Control + C).
Вставить как значения — сочетание клавиш — ALT + ESV

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

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

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

Вот шаги по преобразованию формул в значения с помощью специальной вставки:

Выберите ячейки, для которых вы хотите преобразовать формулы в значения.
Скопируйте ячейки (Control + C).
На главную –> Буфер обмена –> Вставить –> Специальная вставка.

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

Чтобы эффективно использовать формулы, необходимо уяснить три важных момента:

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

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

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

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

Важно! Изменение любого параметра влияет на все открытые книги.

Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Формулы".

В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Формулы".

Выполните одно из следующих действий:

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

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

Чтобы отключить автоматический перерасчет и пересчитывать открытые книги только в том случае, если вы делаете это явным образом (нажав клавишу F9), в разделе "Параметры расчета" в разделе "Расчет рабочей книги" нажмите "Вручную".

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

Чтобы вручную пересчитать все открытые рабочие листы, включая таблицы данных, и обновить все открытые листы диаграмм, на вкладке "Формулы" в группе "Расчет" нажмите кнопку "Рассчитать сейчас".

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

Совет. Кроме того, многие из этих параметров можно изменить вне диалогового окна «Параметры Excel». На вкладке "Формулы" в группе "Расчет" нажмите "Параметры расчета" и выберите "Автоматически".

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

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

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

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

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

Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Формулы".

В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Формулы".

В разделе "Параметры расчета" установите флажок "Включить итеративный расчет".

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

Чтобы установить максимальную сумму сдачи между результатами пересчета, введите ее в поле "Максимальная сдача". Чем меньше число, тем точнее результат и тем больше времени требуется Excel для пересчета рабочего листа.

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

Прежде чем изменить точность вычислений, имейте в виду следующие важные моменты:

По умолчанию Excel вычисляет сохраненные, а не отображаемые значения

Отображаемое и распечатываемое значение зависит от того, как вы выбрали форматирование и отображение сохраненного значения. Например, ячейка, в которой отображается дата «22.06.2008», также содержит порядковый номер, который является сохраненным значением даты в ячейке. Вы можете изменить отображение даты в другом формате (например, на «22 июня 2008 г.»), но изменение отображения значения на листе не изменит сохраненное значение.

Будьте осторожны при изменении точности вычислений

Когда формула выполняет вычисления, Excel обычно использует значения, хранящиеся в ячейках, на которые ссылается формула. Например, если каждая из двух ячеек содержит значение 10,005 и ячейки отформатированы для отображения значений в денежном формате, в каждой ячейке отображается значение 10,01 доллара США. Если вы добавите две ячейки вместе, результат составит 20,01 доллара США, поскольку Excel добавляет сохраненные значения 10,005 и 10,005, а не отображаемые значения.

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

Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Дополнительно".

В Excel 2007 нажмите кнопку Microsoft Office, выберите «Параметры Excel» и выберите категорию «Дополнительно».

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

Хотя Excel ограничивает точность до 15 цифр, это не означает, что 15 цифр — это предел размера числа, которое вы можете хранить в Excel. Предел составляет 9,99999999999999E+307 для положительных чисел и -9,999999999999999E+307 для отрицательных чисел. Это примерно то же самое, что 1 или -1 с последующими 308 нулями.

Точность в Excel означает, что любое число, превышающее 15 цифр, сохраняется и отображается с точностью до 15 цифр. Эти цифры могут быть в любой комбинации до или после запятой. Любые цифры справа от 15-й цифры будут нулями. Например, 1234567.890123456 имеет 16 цифр (7 цифр до и 9 цифр после запятой). В Excel он хранится и отображается как 1234567.89012345 (это отображается в строке формул и в ячейке). Если вы установите для ячейки числовой формат, чтобы отображались все цифры (вместо научного формата, такого как 1.23457E+06), вы увидите, что число отображается как 1234567.890123450. 6 в конце (16-я цифра) отбрасывается и заменяется 0. Точность останавливается на 15-й цифре, поэтому все последующие цифры равны нулю.

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

Перейдите на вкладку "Файл", нажмите "Параметры" и выберите категорию "Дополнительно".

В Excel 2007 нажмите кнопку Microsoft Office, выберите "Параметры Excel" и выберите категорию "Дополнительно".

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

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

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

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

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

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

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

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

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

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

В электронной таблице Excel для Интернета перейдите на вкладку "Формулы".

В раскрывающемся списке рядом с параметрами расчета выберите один из следующих параметров:

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

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

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

Чтобы вручную пересчитать рабочую книгу (включая таблицы данных), нажмите «Рассчитать рабочую книгу».

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

Нужна дополнительная помощь?

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

поиск меню

Урок 13. Простые формулы

Введение

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

Математические операторы

Excel использует стандартные операторы для формул, такие как знак "плюс" для сложения (+), знак "минус" для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и знак вставки. (^) для показателей степени.

Стандартные операторы

Все формулы в Excel должны начинаться со знака равенства (=). Это связано с тем, что ячейка содержит или равна формуле и вычисляемому ею значению.

Ссылки на ячейки

Хотя вы можете создавать простые формулы в Excel вручную (например, =2+2 или =5*5), в большинстве случаев для создания формулы вы будете использовать адреса ячеек. Это называется созданием ссылки на ячейку. Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, поскольку вы можете изменить значение ячеек, на которые ссылаетесь, без необходимости переписывать формулу.

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

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

Примеры простых формул

Чтобы создать формулу:

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

Выбор ячейки B3

Ввод знака =

Ссылка на ячейку B1

Ссылка на ячейку B2

Полная формула и расчетное значение

Изменение значений со ссылками на ячейки

Истинное преимущество ссылок на ячейки заключается в том, что они позволяют обновлять данные на листе без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки B1 с 1200 до 1800 долларов. Формула в ячейке B3 автоматически пересчитает и отобразит новое значение в ячейке B3.

Пересчитанное значение ячейки

Excel не всегда сообщит вам, если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.

Чтобы создать формулу с помощью метода «укажи и щелкни»:

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

Выбор ячейки D3

Ссылка на ячейку B3

Ссылка на ячейку C3

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

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

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

Чтобы изменить формулу:

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

Выбор ячейки B3

Выбор формулы для редактирования

Неуместная ссылка на ячейку

Редактирование формулы

Новое рассчитанное значение

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

Чтобы отобразить все формулы в электронной таблице, вы можете, удерживая клавишу Ctrl, нажать ` (ударение). Клавиша серьезного акцента обычно находится в верхнем левом углу клавиатуры. Вы можете снова нажать Ctrl+`, чтобы вернуться к обычному виду.

У вас могут быть разные причины для преобразования формул в значения:

Чтобы иметь возможность быстро вставлять значения в другие книги или листы, не тратя время на специальное копирование/вставку.
Чтобы ваши исходные формулы оставались неизвестными, когда вы отправляете книгу другому человеку (например, ваша розничная надбавка к оптовой цене).
Чтобы предотвратить изменение результата при изменении чисел в связанных ячейках.
Сохраните результат формулы rand().
Если в вашей рабочей тетради много сложных формул, из-за которых пересчет выполняется очень медленно.И вы не можете переключить параметр "Расчет рабочей тетради" в ручной режим.

Преобразование формул в значения с помощью ярлыков Excel

Вам нужно заменить его результаты значениями.

Просто следуйте простым шагам ниже:

Выделите все ячейки с формулами, которые вы хотите преобразовать.
Нажмите Ctrl + C или Ctrl + Ins, чтобы скопировать формулы и их результаты в буфер обмена.
Нажмите Shift + F10, а затем – V, чтобы вставить обратно в ячейки Excel только значения.
Shift + F10 + V — это самый короткий способ использования диалогового окна Excel "Специальная вставка — только значения".

Вот оно! Если этот способ все еще недостаточно быстр для вас, обратите внимание на следующий совет.

Замена формул значениями в пару кликов мыши

Вы когда-нибудь чувствовали, что некоторые рутинные задачи в Excel, которые можно выполнить в несколько кликов, отнимают у вас слишком много времени? Если да, добро пожаловать в наш Ultimate Suite для Excel.

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

Установив Ultimate Suite в Excel, выполните следующие действия, чтобы заставить его работать:

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

Я рекомендую вам изучить другие функции нашего Ultimate Suite. Могу вас заверить, что это сэкономит 4-5 минут на одной задаче Excel, 5-10 минут на другой задаче, а к концу дня сэкономит вам час или больше. Сколько стоит час вашей работы? :)

Вас также может заинтересовать

48 комментариев к "Как заменить формулы их значениями в Excel"

Кажется, что для всех этих ответов требуется сочетание клавиш ctrl + c, а затем специальная вставка. Нет ли способа ПРОСТО скопировать значение из одной ячейки в другую, не сохраняя ячейку, на которую указывает ссылка. это основная функция любого языка программирования, и я не могу найти ни одного ответа, который не включал бы специальную пасту. мне нужно скопировать и вставить в отфильтрованном представлении, я не могу вставить в целевую ячейку, ошибка множественного выбора.

Это не будет работать при множественном выборе. Пожалуйста, дайте какую-нибудь идею, чтобы скопировать и вставить значения из множественного выбора..
например, для. скопируйте формулы из (A2: B50), (A55: B90) и хотите вставить их выходное значение в те же ячейки.
Пожалуйста, помогите мне решить эту проблему.

Здравствуйте, Милинд!
К сожалению, функции "Копировать", "Вырезать" и "Вставить" не работают с несколькими вариантами выбора в Excel.

Для тех, кто пытается преобразовать вывод формулы в число, у меня может быть решение. У меня была вложенная формула ЕСЛИ, которая выводит значение в столбце B, в зависимости от того, что было введено в столбце A. Затем я хотел суммировать столбец B, но не мог, потому что Excel не может СУММИРОВАНИЕ формул. Затем я попробовал C1: = B1 и столбец SUM C. Тот же результат, потому что Excel видит столбец C как столбец формул. Однако, если ввести C1: =B1*1, столбец C становится значением, и вуаля, SUM C1:C12 дает реальную сумму. Я уверен, что эту простую C1: =B1*1 можно использовать во многих других ситуациях.

Надеюсь, это поможет.

Я надеюсь, что кто-то может мне помочь.
Пример: 0:20:00 минут — это цель, которую я поставил, но некоторые студенты превышают ее (0:23:45). Я оцениваю 20+ мин на 100%. Все, что выше, нужно вычесть. Как я могу использовать формулу, которая удаляет лишнее время и изменяет значение на 0:20:00?

У меня есть более 20 000 строк, которые должны стать значениями. Я никак не могу сделать это вручную.

Читайте также: