Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Удаление формул из ячеек таблицы Excel

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

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

Что такое инструмент преобразования формул и для чего он мне нужен?

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

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

Формулы решений при нескольких условиях без функции ЕСЛИ

Пример 2. Таблица Excel содержит вероятности попадания в цель для стрелков из трех различных видов оружия. Рассчитайте вероятность поражения цели хотя бы из одного оружия для каждого стрелка. Некоторые ячейки содержат ошибочные данные (значения, выведенные из диапазона допустимых значений для вероятности). В таких случаях вычислите вероятность как 0. В этом случае логическая функция ЕСЛИ не может быть использована.

Читайте так же:
Как вставить подпись Outlook при отправке электронной почты в Excel?

Просмотр оригинальной работы :

Пример 2.

Для расчета вероятности мы используем формулу P(A)=1-q1q2q3, где q1, q2 и q3 — вероятности промаха (противоположных событий, т.е. попадания в цель). Мы используем следующую формулу:

«AND(B3>=0;B3<=1;C3>=0;C3<=1;D3>=0;D3<=1))" в формуле необходимо для выполнения операции сравнения значения в каждой ячейке с допустимым диапазоном (от 0 до 1). Функция TRUE возвращает TRUE, если все выражения возвращают TRUE, и FALSE, если хотя бы одно из проверенных выражений возвращает FALSE. Поскольку за этим выражением следует символ "*" (умножение), Excel автоматически преобразует полученное логическое значение в числовое (1 или 0).

Результаты расчёта для всех стрелков:

Формулы без функции ЕСЛИ.

Таким образом, расчет производится только в том случае, если все три ячейки A, B и C содержат корректные данные. Иначе будет возвращен результат 0.

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

Формулы — хорошая вещь. При изменении входных данных Excel автоматически пересчитывает, превращая «калькулятор-переросток» в эффективную автоматизированную систему обработки данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Когда формулы использовать нельзя, вместо них лучше хранить значения в ячейках. Например:

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

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

Способ 1. Классический

Этот метод прост, знаком большинству пользователей и подразумевает использование специальной вставки:

  1. Выделите диапазон с формулами, которые нужно заменить на значения.
  2. Скопируйте его правой кнопкой мыши – Копировать(Copy) .
  3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :

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

Или наведите указатель мыши на команду Paste Special, чтобы увидеть подменю:

formulas-to-values2.png

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

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

Способ 2. Только клавишами без мыши

При определенной сноровке вы сможете выполнять все вышеперечисленные действия, не прикасаясь к мышке:

  1. Скопируйте выделенную область с помощью Ctrl + C
  2. Вставьте обратно с помощью Ctrl + V
  3. Нажмите клавишу Ctrl, чтобы открыть меню
  4. Параметры вставки Нажмите клавишу с русской буквой Z или используйте клавиши со стрелками для выбора значения и подтвердите выбор клавишей Enter:

Способ 3. Только мышью без клавиш или Ловкость Рук

Это требует некоторой сноровки, но будет значительно быстрее, чем предыдущий метод. Сделайте следующее:

  1. Выберите диапазон с формулами на листе
  2. Захватите край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ кнопку мыши, перетащите его на несколько сантиметров в обе стороны, затем верните его на прежнее место
  3. После перетаскивания выберите в появившемся контекстном меню команду Копировать только как значения.

Действие становится очень быстрым и легким, стоит лишь немного попрактиковаться. Главное, чтобы сосед не толкнул вас под локоть и руки не дрожали 😉

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

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

Добавление кнопки вставки значений на панель быстрого доступа

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

Кнопка вставки значений на панели быстрого доступа

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

Подсветка горячих клавиш

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это, вероятно, самый быстрый способ.

Макрос для преобразования всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с помощью Ctrl) выглядит следующим образом

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

Наконец, чтобы сбросить все формулы рабочей книги на все листы, используйте эту конструкцию:

Код макроса можно вставить в новый модуль в файле (нажмите Alt + F11, чтобы вызвать Visual Basic, затем Insert — Module). Затем их можно запустить через вкладку Разработчик — Макросы (Developer — Macros) или сочетание клавиш Alt + F8. Любая книга, при условии, что вы открыли файл, в котором они хранятся, будет поддерживать макросы. Действия макросов нельзя отменить, поэтому используйте их с осторожностью.

Способ 6. Для ленивых

Если вы не можете сделать все вышеперечисленное, можно поступить еще проще — установить дополнение PLEX, в котором уже есть готовые макросы для преобразования формул в значения, и делать все одним щелчком мыши:

Формула воспринимается программой, как текст

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

  • В ячейке установлен текстовый формат. Для программы это сигнал, что содержимое нужно вывести в том же виде, в котором оно хранится.

    Измените формат на Числовой, как сказано здесь.
  • Нет знака «=» вначале формулы. Как я рассказывал в статье о правилах написания формул, любые вычисления начинаются со знака «равно».

    Если его упустить – содержимое будет воспринято, как текст. Добавьте знак равенства и пересчитайте формулу.
  • Пробел перед «=». Если перед знаком равенства случайно указан пробел, это тоже будет воспринято, как текст.

    Эту ошибку сложно заметить, поэтому придется проверять вручную. Удалите лишний пробел и пересчитайте.
  • Формула заключена в кавычки. Такая ошибка случается, когда работаем с текстовыми данными. Само собой, информация в кавычках трактуется, как текст.

    Удалите лишние кавычки, нажмите Enter , чтобы пересчитать.

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

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

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

Вы всегда можете задать вопрос в сообществе Excel Tech Community, попросить помощи в сообществе Answers или предложить новую функцию или улучшение на сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

Это не всегда практично. Например, если вы используете фильтры, значения будут вставляться не в правые ячейки, а в ячейки под фильтрами. Более того, эта процедура не быстрая: несколько кликов и несколько тысяч строк утомят ваши руки из-за однообразия действий.

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

Для начала следует записать макрос замены.

  1. На панели разработки создайте модуль (контейнер) для макроса:
  • Кликаем на пиктограмму Visual Basic
  • В открывшемся окне кликаем правой кнопкой мыши по книге и выбираем в контекстном меню insert => Module

  1. Откройте созданный модуль двойным кликом и введите в него текст макроса.

Знак_формулы()

Для каждой ячейки в списке выделений

cell.Formula = cell.Value

Next

Конец субстанции

Sub Замена_формул() – название макроса;

Цикл повторяющихся действий для каждой ячейки выделения;

cell.Formula = cell.Value – действие по замене формулы на значение;

End Sub — завершение макроса.

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

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

После замены формул значениями можно смело удалять промежуточные таблицы (черновики) из отчета .

Видео работы макроса замены формул на значения:

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

В надстройке YOXCEL есть команда, значительно ускоряющая эту операцию.

После того как надстройка подключена открываем рабочую книгу, в которой необходимо заменить формулы на значения. П ереходим во вкладку "ЁXCEL" Главного меню, нажимаем кнопку "Формулы" и выбираем команду "Заменить формулы на значения":

В открывшемся диалоговом окне устанавливаем необходимые параметры замены и нажимаем "ОК":

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector