Мини-курс "5 чудес Excel"
Как в ячейки Excel с числами добавить формулу
Мы сделаем это простыми приёмами базового уровня, без макросов.
В приведённых ниже примерах нельзя составить формулу в первой ячейке и протянуть, так как значения остаются внесёнными от руки и в каждой ячейке они разные. Также есть причины, по которым некоторые пользователи не хотят делать расчёты рядом с таблицей.
Почему? Подозреваю, что две таблицы на связях не нужны, но есть необходимость пересчитать данные в таблице и сохранить историю пересчёта, а это возможно только если в ячейке будет формула.
Желание пользователя – закон )) Смотрите ниже подробное описание, пробуйте и пусть всё получится.
Задача
В ячейках просто числа и к ним нужно подставить формулу, умножающую эти числа на конкретное значение либо на ячейку.
Вот стартовая таблица:
А мы хотим, чтобы данные в ячейках выглядели и были посчитаны вот так:
Решение
Самое простое решение состоит в том, чтобы рядом с таблицей в любой ячейке написать =1,2 или ссылку на ячейку с корректирующим числом (в примере =$G$1), скопировать эту вспомогательную ячейку, выделить диапазон таблицы, которые нужно пересчитать, нажать CTRL+ALT+V и в окне специальной вставки выбрать переключатель умножить.
Получите результат, как на скрине ниже. Ячейку справа от таблицы можно почистить, таблица не пострадает.
На курсах я часто слышу вопрос, как сделать наоборот. Например, деление некоторого числа или ячейки на те значения, которые уже есть в таблице? Буду рада узнать, что за бизнес-ситуация требует такого решения, но ловите решение, это тоже возможно.
Решение наоборот
Без временных вспомогательных ячеек не обойтись. Справа от таблицы соберём нужный текст формулы с помощью знака амперсанд & и растянем формулу на нужное количество ячеек (в примере C5:C16):
Теперь скопируем получившиеся тексты формул (в примере из ячеек C5:C16) и вставим значениями вместо исходных данных таблицы (в примере начиная с ячейки B5). После вставки получится так:
Диапазон вспомогательных формул справа от таблицы можно удалить. И останется превратить данные в таблице в формулы. Сейчас это просто текст, так как мы использовали знак &. Смена формата ничем не поможет. Нужно заставить Excel обновить данные ячеек и тогда он сам превратит их в формулы. Поможет в этом окно поиска и замены.
Выбираем ячейки C5:C16, нажимаем CTRL+F, на вкладке Заменить в поле Найти вписываем знак равно, и в поле Заменить на вписываем знак равно, жмём Заменить все, немного ждём. Готово.
Выбираем ячейки C5:C16, нажимаем CTRL+F, на вкладке Заменить в поле Найти вписываем знак равно, и в поле Заменить на вписываем знак равно, жмём Заменить все, немного ждём. Готово.
А если уже формула?
В ячейке уже написана формула. И в неё нужно подставить ещё некоторые расчёты. Исходная таблица с формулами:
Выбираем диапазон данных для подстановки формулы и для начала убираем из ячеек знак равно, используя окно поиска и замены CTRL+F. Получится вот так:
А дальше работаем по сценарию, который я описала выше. То есть справа от таблицы составляем текст будущей формулы.
Потом копируем результаты значениями на место исходных данных таблицы. И можно сразу после вставки (так как нужный диапазон уже выбран) через CTRL+F обновить данные, а потом и ставшие лишними формулы справа от таблицы удалить
Честно – в большинстве случаев я бы оставила исходные данные, как есть, а формулу написала бы в свободных ячейках. Исходник можно и скрыть, и сгруппировать. Но если всё же нужно именно формулу дописать в ячейках, где уже есть числовые данные или другие формулы, то вы теперь знаете, как это сделать.
Подробные разборки со знаком & и все полезные настройки окна поиска и замены поясняю на курсе Базовые навыки.
Подробные разборки со знаком & и все полезные настройки окна поиска и замены поясняю на курсе Базовые навыки.
Вас ждёт несколько десятков простых способов решения типовых рабочих задач. Подключайтесь к обучению. Всё решаемо. Кнопка с программой и условиями курса чуть ниже.