Перейти к содержимому

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

  • автор:

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

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

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

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

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

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

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

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

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

Как выбрать диапазон, содержащий формулу массива

Щелкните ячейку в формуле массива.

На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить, а затем выберите команду Перейти.

Нажмите кнопку Дополнительный.

Щелкните Текущий массив.

Нажмите кнопку Копировать .

Нажмите кнопку В .

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

В следующем примере показана формула в ячейке D2, которая умножает ячейки A2, B2 и скидку из ячейки C2 для расчета суммы счета для продажи. Чтобы скопировать фактическое значение вместо формулы из ячейки на другой книгу или на другой, можно преобразовать формулу в ячейку в ее значение, выстроив следующее:

Нажмите F2, чтобы изменить ячейку.

Нажмите F9, а затем ввод.

В строке формул показана формула.

После преобразования ячейки из формулы в значение в области формул отображается значение 1932,322. Обратите внимание, что 1932,322 — фактическое вычисляемого значения, а 1932,32 — значение, отображаемого в ячейке в валютном формате.

В строке формул показано значение.

Совет: При редактировании ячейки с формулой можно нажать F9, чтобы окончательно заменить формулу ее вычисляемой величиной.

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

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

При замене части формулы ее значением ее часть восстановить невозможно.

Щелкните ячейку с формулой.

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

Чтобы вычислить выбранную часть, нажмите F9.

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

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

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

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

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

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

Копирование значений

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

Копирование формул

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

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

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

Убрать формулу в Excel оставив значения

Удаление формул в Microsoft Excel

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

Процедура удаления

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

http://lumpics.ru/how-to-enable-or-disable-Формула в Microsoft Excelmacros-in-excel/

Способ 1: копирование значений через параметры вставки

Скопировать данные без формулы в другую область можно при помощи параметров вставки.

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

Копирование в Microsoft Excel

Вставка в Microsoft Excel

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

Таблица вставлена в Microsoft Excel

Способ 2: копирование специальной вставкой

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

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

Вставка с помощью специальной вставки в Microsoft Excel

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

Вставка с сохранением форматирования в Microsoft Excel

Способ 3: удаление формулы из исходной таблицы

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

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

Повторное копирование в Microsoft Excel

Повторная вставка в Microsoft Excel

Удаление таблицы в Microsoft Excel

Настройки удаления в Microsoft Excel

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

Способ 4: удаление формул без создания транзитного диапазона

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

    Выделяем диапазон, в котором нужно удалить формулы. Кликаем по кнопке «Копировать», размещенной на ленте или набираем на клавиатуре комбинацию клавиш Ctrl+C. Эти действия равнозначны.

Копирование в программе Microsoft Excel

Вставка в программе Microsoft Excel

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

Формул в таблице нет Microsoft Excel

Способ 5: использование макроса

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

    Переходим во вкладку «Разработчик». Кликаем по кнопке «Visual Basic», размещенной на ленте в блоке инструментов «Код».

Переход в редактор макросов в Microsoft Excel

Sub Удаление_формул() Selection.Value = Selection.Value End Sub

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

Редактор макросов в Microsoft Excel

Переход к макросам в Microsoft Excel

Запуск макроса в Microsoft Excel

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

Максрос выполнил задачу в Microsoft Excel

Урок: Как включить или отключить макросы в Excel

Урок: Как создать макрос в Excel

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

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

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

Очистка содержимого в Microsoft Excel

Содержимое очищено в Microsoft Excel

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

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

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

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

Как добавить новую строку с формулами в Excel?

Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?

  1. Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы. Исходная стрпока.
  2. Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C). Копировать.
  3. После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами. Место для вставки.
  4. Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).

Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.

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

Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?

Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».

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

На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:

Абсолютная ссылка.

Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.

Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».

Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.

  1. Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»). Заменить.
  2. Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все». Пример1.
  3. После этого вы можете скопировать необходимую строку, а в ее формулах не изменятся адреса ссылок на ячейки, так как они временно деактивированные. Пример2.
  4. Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места

В результате при копировании во всех строках ссылки формул остались неизменными.

Специальное копирование значений и формул

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

Копирование значений

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

Копирование формул

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

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

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

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

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

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

Как добавить новую строку с формулами в Excel?

Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?

  1. Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы. Исходная стрпока.
  2. Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C). Копировать.
  3. После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами. Место для вставки.
  4. Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).

Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.

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

Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?

Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».

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

На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:

Абсолютная ссылка.

Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.

Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».

Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.

  1. Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»). Заменить.
  2. Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все». Пример1.
  3. После этого вы можете скопировать необходимую строку, а в ее формулах не изменятся адреса ссылок на ячейки, так как они временно деактивированные. Пример2.
  4. Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места

В результате при копировании во всех строках ссылки формул остались неизменными.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *