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

Обновлено: 01.07.2024

С самого начала своей работы я усвоил одну важную вещь:

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

Позвольте мне рассказать вам несколько интересных способов сделать это, но перед этим.

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

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

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

Небольшое примечание. Это один из тех советов по работе с Excel, которые помогут улучшить базовые навыки работы с Excel.

1. Вставка значений с помощью простого сочетания клавиш

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

Все, что вам нужно сделать:

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

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

2. Использование специальной вставки для преобразования в значения

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

Сначала выберите диапазон данных или весь столбец.
А затем щелкните правой кнопкой мыши и скопируйте его.
Перейдите на вкладку "Главная" ➜ "Буфер обмена" ➜ "Вставить" ➜ "Специальная вставка".

Или вы также можете нажать правую кнопку мыши и выбрать Специальная вставка -> Значения.

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

3. Использование контекстного меню

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

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

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

4. Преобразование формул в значения с помощью кода VBA

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

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

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

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

6. Получите значение результата формулы с помощью Power Query

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

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

Выберите таблицу и перейдите на вкладку "Данные" ➜ "Получить и преобразовать" ➜ "Из таблицы".

Ваша таблица откроется в редакторе расширенных запросов.

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

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

Заключение

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

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

Надеюсь, этот совет был вам полезен, но теперь скажите мне одну вещь.

Какой ваш любимый? Есть ли у вас какой-либо другой метод, который вы используете при преобразовании формул в значения?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Есть три ярлыка. Ярлыки Excel. Ярлыки Excel для ПК Mac. Список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета.Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш, которые можно использовать для преобразования формул в значения в Excel. В этой статье «Преобразование формулы Excel в значение» дается пошаговое описание каждого из них.

Первый вариант с использованием Shift + F10 требует наименьшего количества нажатий клавиш; второй, Alt+E+S чуть длиннее; и в-третьих, использование ярлыков ленты через Alt + H требует наибольшего количества нажатий клавиш. Ниже приведено полное руководство по использованию каждого подхода для преобразования формул в значения.

Способ 1: Shift, F10, V

Выделите ячейки, которые хотите преобразовать.
Нажмите Ctrl + C.
Нажмите Shift + F10 и V.

Узнайте больше о быстрых клавишах в нашем бесплатном ускоренном курсе Excel.

Способ 2: Alt, E, S, V

Выделите ячейки, которые хотите преобразовать.
Нажмите Ctrl + C.
Нажмите клавиши ALT + E + S + V.

Способ 3: Alt, H, V…

Выделите ячейки, которые хотите преобразовать.
Нажмите Ctrl + C.
Нажмите Alt + H + V + S + V.

Вставить специальные значения

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

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

Преобразование формул в значения в Excel очень удобно при финансовом моделировании Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем строить модель. . Наиболее распространенное использование — выполнение побочных вычислений для получения значения, которое используется в качестве предположения. Например, представьте, что у вас есть список допущений о количестве клиентов в год в модели. Возможно, вы захотите быстро подсчитать, сколько клиентов вы ожидаете в 2019 году в отдельной области модели Excel. Типы финансовых моделей. Наиболее распространенные типы финансовых моделей включают в себя: модель с тремя отчетами, модель DCF, модель M&A, модель LBO, бюджетная модель. Найдите 10 основных типов , а затем скопируйте эту формулу и вставьте ее как значение в модель.

Другие сочетания клавиш Excel

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

Все ярлыки Excel
Список формул Excel Памятка по формулам Excel Памятка CFI по формулам Excel содержит все наиболее важные формулы для выполнения финансового анализа и моделирования в электронных таблицах Excel. Если вы хотите стать мастером финансового анализа в Excel и экспертом по построению финансовых моделей, то вы обратились по адресу.
Руководство по финансовому моделированию Бесплатное руководство по финансовому моделированию В этом руководстве по финансовому моделированию представлены советы и передовые методы работы с Excel по предположениям, драйверам, прогнозированию, связыванию трех утверждений, анализу DCF и т. д.
Как стать финансовым аналитиком Руководство по становлению финансовым аналитиком Как стать финансовым аналитиком. Следуйте руководству CFI по нетворкингу, резюме, собеседованиям, навыкам финансового моделирования и многому другому. За годы работы мы помогли тысячам людей стать финансовыми аналитиками и точно знаем, что для этого нужно.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

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

Чтобы иметь возможность быстро вставлять значения в другие книги или листы, не тратя время на специальное копирование/вставку.
Чтобы ваши исходные формулы оставались неизвестными, когда вы отправляете книгу другому человеку (например, ваша розничная надбавка к оптовой цене).
Чтобы предотвратить изменение результата при изменении чисел в связанных ячейках.
Сохраните результат формулы 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 строк, которые должны стать значениями. Я никак не могу сделать это вручную.

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