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

С первых дней своей работы я на собственном горьком опыте усвоил одну вещь:

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

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

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

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

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

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

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

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

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

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

• И затем Alt+ES V.

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

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

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

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

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

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

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

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

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

1. Сначала выберите диапазон ячеек, в которых содержатся формулы.
2. Теперь щелкните правой кнопкой мыши по краю выделения.
3. И, удерживая правую кнопку мыши, перетащите диапазон вправо и мгновенно верните его в исходное место и отбросьте.
4. Здесь вы получите меню опций.
5. В этом меню параметров выберите «Копировать сюда только как значения».

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

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

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

 Sub ConvertToValues() Dim MyRange As Range Dim MyCell As Range Select Case MsgBox("You Can't Undo This Action. " & _ "Save Workbook First?", _ vbYesNoCancel, "Alert") Case Is = vbYes ThisWorkbook.Save Case Is = vbCancel Exit Sub End Select Set MyRange = Selection For Each MyCell In MyRange If MyCell.HasFormula Then MyCell.Formula = MyCell.Value End If Next MyCell End Sub

Связанный: Учебное пособие по Excel VBA

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

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

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

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

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

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

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

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

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

• Откроется ваша таблица в редакторе Power Query.

• Оттуда просто нажмите «Закрыть и загрузить».

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

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

Заключение

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

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

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

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

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