Замена некоторых формул значениями

Рубрика: 7. Полезняшки Excel

В силу различных обстоятельств иногда возникает потребность заменить формулы в ячейках значениями. Если это связный прямоугольный диапазон, сделать такую замену весьма просто. Выделите диапазон, нажмите Ctrl+C. Не снимая выделения, кликните правой кнопкой мыши в любом месте выделенного диапазона, выберите Специальная вставка, и в открывшемся окне выберите значения. Нажмите Ok. Если вы любите клавиатурные сокращения, то для специальной вставки используйте Alt+Я+М+З (версия для Excel 2016 и позднее; все буквы русские).

Ris. 1. Vstavit tolko znacheniya

Рис. 1. Вставить только значения

Скачать заметку в формате Word или pdf

Иногда заменить формулы на значения нужно в отдельных ячейках, или в нескольких несвязанных диапазонах. У меня такая потребность возникала в следующих ситуациях. 1. Использовал модель на основе функции СЛЧИС(), а затем презентовал результаты в отчете (Excel, Word, PowerPoint). Требовалось, чтобы вид графиков в Excel и в презентации совпадал. Поскольку волатильная функция СЛЧИС() пересчитывается при каждом изменении в книге, нужно было сгенерировать модель и графики на ее основе, а затем избавиться от пересчета. 2. Построил отчет о продажах на основе функций кубов (в частности КУБЗНАЧЕНИЕ). В отчете представлены, как исторические данные, так и текущие. Исходных данных очень много, и при каждом обновлении пересчитываются все суммы в отчете. Понятно, что исторические данные не изменяются, и в них формулы можно заменить на значения.

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

Выделение ячеек

Чтобы выделить ячейки, содержащие определенную формулу или фрагмент формулы, выберите диапазон, покрывающий все ячейки, требующие замены (естественно, диапазон будет содержать и некоторые ячейки, которые не нужно изменять). Если требуется выберите все ячейки на листе. Для этого нажмите Ctrl+Ф (или А английское). По возможности избегайте выделения всего листа, так как большое количество ячеек обрабатывается дольше.

Вызовите окно поиска – Ctrl+F (или А русское). Введите строку для поиска в поле Найти. В нашем примере мы заменяем значениями формулы кубов, относящиеся к 2002 году. Строка «2002» содержится в формуле КУБЗНАЧЕНИЕ только для ячеек, извлекающих данные за соответствующий год (см. цифру 1 на рис. 2).

Нажмите кнопку Параметры (2) и в окне появятся дополнительные опции. Проверьте, что Область поиска = формулы, а галочка Ячейка целиком отсутствует (3). Нажмите кнопку Найти все (4). В нижней части окна появятся строки с ячейками, удовлетворяющими условиям поиска. Выберите первый элемент (5), а затем нажмите Ctrl+Ф, чтобы выбрать все элементы в списке. Нажмите Закрыть (6). Ячейки, содержащие формулу куба и относящиеся к 2002 г. выбраны на листе Excel.

Ris. 2. Okno Najti

Рис. 2. Окно Найти; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Замена формул на значения

Напишите и запустите следующий макрос:

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

Идея подсмотрена у Jon Acampora.


Прокомментировать