Замена формулы результатом ее вычисления
Иногда нужно заменить формулу результатом ее вычисления. В Excel сделать это довольно просто.
В строке формул или непосредственно в ячейке поставьте курсор в конце формулы и нажмите F9 (на Mac: Fn+F9). Вся формула будет автоматически заменена не результат ее вычисления.
Аналогично можно поступить и с фрагментом формулы: выделяем фрагмент формулы в строке формул или непосредственно в ячейке:
Нажимаем F9 (на Mac: Fn+F9). Выделенные фрагмент будет заменен результатом вычисления этого фрагмента:
Расписание ближайших групп:
Как преобразовать формулу Excel в текст
Проверяющему теперь совсем не обязательно «открывать» ячейки и смотреть какая же формула была введена. А если расчет был распечатан? Тогда о проверки формулы речь вообще не идет.
Синтаксис
Функция =ТЕКСТФОРМУЛЫ(ЯЧЕЙКА) имеет всего 1 аргумент:
- ЯЧЕЙКА — ссылка на ячейку, в которой содержится формула.
Примеры
Ниже приведены примеры использования данной функции, которые наглядно демонстрируют ее работу.
Код формулы на VBA
Всего одна строчка кода.
Public Function ТЕКСТФОРМУЛЫ(ЯЧЕЙКА As Range) As String ТЕКСТФОРМУЛЫ = ЯЧЕЙКА.FormulaLocal End Function
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Преобразование формул в значения
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать(Copy) .
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl + C
- Тут же вставляем обратно сочетанием Ctrl + V
- Жмём Ctrl , чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали 😉
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Sub Formulas_To_Values_Selection() 'преобразование формул в значения в выделенном диапазоне(ах) Dim smallrng As Range For Each smallrng In Selection.Areas smallrng.Value = smallrng.Value Next smallrng End Sub
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
Sub Formulas_To_Values_Sheet() 'преобразование формул в значения на текущем листе ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value End Sub
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Sub Formulas_To_Values_Book() 'преобразование формул в значения во всей книге For Each ws In ActiveWorkbook.Worksheets ws.UsedRange.Value = ws.UsedRange.Value Next ws End Sub
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
- всё будет максимально быстро и просто
- можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl + Z как обычно
- в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
- любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX
Ссылки по теме
- Что такое макросы, как их использовать, копировать и запускать
- Как скопировать формулы без сдвига ссылок
- Как считать в Excel без формул
Как преобразовать формулу в текст в excel
Для того, чтобы значения ячеек в таблице были не результатом расчета формулы, например, » =СЛУЧМЕЖДУ(0;210)», необходимо преобразовать данные ячеек, оставив вместо результата вычисления формулы только значения. В противном случае, случайные значения в ячейках будут каждый раз меняться или зависеть от других ячеек.
Копирование и преобразование формул в значения в таблицах Excel — довольно важная и часто востребованная задача, особенно когда на листе делаешь промежуточные вычисления, которые после завершения промежуточного этапа необходимо перенести в другую книгу Excel.
Наиболее простое решение основано на использовании буфера обмена в Excel. Чтобы преобразовать данные ячеек экселевской таблицы в набор значений, необходимо:
1. выделить диапазон ячеек — ячейки станут полупрозрачного светло-синего цвета;
2. скопировать ячейки в буфер (правый клик и Копировать, или Ctrl+C) – вокруг диапазона ячеек будет двигаться рамка
3. Вставить из буфера только скопированные значения (правый клик, в разделе «Параметры вставки» нажать на значок «123» .
Excel. Преобразование формулы в значение
Если знаете другие способы, поделитесь ими в комментариях.