Вставить формулу Excel vba в ячейку

Формула

Самый лучший и простой способ ввести формулу в ячейку программно (даже если вы профессионал) – это написать ее в ячейке, выбрать ее со знаком равенства в строке формул и вставить ее между кавычками в строке формул. код. Этот подход становится еще более удобным, когда вы начинаете разрабатывать сложные формулы или начинаете использовать переменные и другие адреса ячеек в своих формулах, как показано в части 5 этого урока.

Когда вы хотите ввести формулу в ячейку, вы должны написать:
Range("A1").Select
Selection.Formula = "=C8+C9"
Обратите внимание на две знаки равенства (=), включая знак в двойных кавычках, как если бы вы вводили его вручную.

Опять же, вам не нужно выбирать ячейку, чтобы ввести в нее формулу. В любом месте листа вы можете написать:
Range("A1").Formula = "=C8+C9"

Если вы напишете следующее:
Range("A1:A8").Formula = "=C8+C9"
Формула в A1 будет =C8+C9, формула в A2 будет быть =C9+C10 и так далее. Если вы хотите, чтобы во всех ячейках была точная формула =C8+C9, вам нужно написать:
Range("A1:A8").Formula = "=$C$8+$C$9"

Массив формул

Как вы узнали на веб-сайте в Excel, вы не будете часто использовать формулы массива, за исключением случаев, когда вам нужно использовать ИНДЕКС/ПОИСКПОЗ с более чем одним аргументом. Такая формула будет выглядеть следующим образом:
Range("A1:A8").FormulaArray Arial" color="green"> =INDEX($D$11:$D$18,MATCH(1,($A$11: $A$18=H20)*($B$11:$B$18=I20)*($C$11:$C$18=J20),0),1) "

ФормулаR1C1

Вот еще одна формула, которую вы будете использовать только в определенных труднодоступных местах. Допустим, в ячейке C1 вам нужна формула «=A1+B1», а в ячейке H1 вам нужна формула «=F1+G1». Вы можете использовать единую формулировку, которая будет адаптироваться к любой выбранной ячейке. Вам нужна формула, которая говорит: «Суммируйте столбцы ячейки 2 слева и столбец ячейки слева». Такая формула:
Range("C1,H1").FormulaR1C1 = "=RC[-2]+RC[-1]"

Если вам нужна формула, которая говорит "суммировать ячейку на 2 строки выше и ячейку на одну строку выше".
Range("C1,H1").FormulaR1C1 = "=R[-2]C+R[-1]C"

Узнайте больше советов
и практических примеров реальных решений реальных проблем
с пошаговыми инструкциями по разработке в 25

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

Excel VBA для вставки формулы с относительной ссылкой (быстрый просмотр)

Загрузить практическую рабочую тетрадь

Загрузите это учебное пособие, чтобы тренироваться, пока читаете эту статью.

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

Здесь у нас есть набор данных с названиями некоторых продуктов и их продаж в определенные годы компании Mars Group.

Мы хотим, чтобы процент роста продаж за 2016–2017 годы был указан в столбце I. Формула роста продаж выглядит так:

Например, для первого продукта Desktop формула в ячейке I5 будет выглядеть так:

Для второго продукта Ноутбук это будет:

Наша цель сегодня — вставить эти формулы с относительными ссылками на ячейки с помощью VBA.

1.Вставка формулы с относительной ссылкой на ячейку в одну ячейку с помощью Excel VBA

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

Например, давайте вставим формулу (E5-D5)/D5 в ячейку I5.

Я покажу вам пошаговую процедуру для достижения этой цели.

⧪ Шаг 1. Объявление целевой ячейки

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

⧪ Шаг 2. Размещение формулы

Далее мы должны выделить указанную формулу. Вот он (E5-D5)/D5 .

⧪ Шаг 3. Вставка формулы в ячейку

Наконец, мы вставим формулу в ячейку. Это самый важный шаг.

Итак, полный код VBA будет таким:

⧭ Код VBA:

⧭ Вывод:

Запустите этот код. Он введет формулу (E5-D5)/D5 в ячейку I5. Выход формулы составляет 26%.

2. Ввод формулы с относительной ссылкой на ячейку в один столбец с помощью Excel VBA

Мы научились вводить формулу в одну ячейку с помощью VBA. Теперь мы научимся вводить формулы в один столбец.

⧪ Шаг 1. Объявление диапазона столбца назначения

Прежде всего мы объявим диапазон целевого столбца. Здесь I5:I4.

⧪ Шаг 2. Выделение формулы и вставка ее в первую ячейку

Далее мы выделим формулу и вставим ее в первую ячейку столбца.

⧪ Шаг 3. Копирование формулы

Затем скопируем формулу из первой ячейки.

⧪ Шаг 4. Вставка в остальные ячейки с помощью цикла For

Это самый важный шаг. Мы будем повторять цикл for, чтобы вставить формулу в остальные ячейки столбца. Для этой цели мы будем использовать свойство xlPasteFormulas VBA.

⧪ Шаг 5 (необязательно): отключение режима CutCopyMode

Наконец, мы отключим CutCopyMode VBA. Вы можете пропустить этот шаг, если хотите.

Поэтому полный код VBA будет таким:

⧭ Код VBA:

⧭ Вывод:

Запустите этот код. Он введет формулу роста продаж в каждую ячейку столбца I5:I14 с увеличением ссылки на ячейку.

Например, ячейка I5 получит (E5-D5)/D5 .

Ячейка I6 получит (E6-D6)/D6 .

3. Вставка формулы с относительной ссылкой на ячейку в несколько столбцов с помощью Excel VBA

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

⧪ Шаг 1. Объявление диапазона назначения

Как и в предыдущих двух методах, мы должны сначала объявить целевой диапазон. Вот это I5:K14.

⧪ Шаг 2. Выделение формулы, ее вставка в первую ячейку, копирование и вставка в первый столбец

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

⧪ Шаг 3. Вставка формулы в остальные столбцы

Далее мы повторим еще два цикла for, чтобы вставить формулу в остальную часть столбца.

⧪ Шаг 4 (необязательно): отключение режима CutCopyMode

Наконец, мы отключим CutCopyMode VBA. Как и в случае со способом 2, вы можете пропустить этот шаг, если хотите.

Поэтому полный код VBA будет таким:

⧭ Код VBA:

⧭ Вывод:

Запустите этот код, и он вставит формулу роста продаж в каждую ячейку диапазона I5:K14 с увеличением ссылки на ячейку.

Например, ячейка I5 получит (E5-D5)/D5 .

Ячейка J5 получит (F5-E5)/E5 .

Опять же, ячейка I6 получит (E6-D6)/D6 .

Ячейка J6 получит (F6-E6)/E6 .

Что нужно помнить

В этой статье мы использовали свойство xlPasteFormulas метода PasteSpecial языка VBA. Помимо этого свойства, у метода PasteSpecial есть еще 11 свойств. Нажмите здесь, чтобы узнать о них подробнее.

Заключение

Итак, это способы вставки формулы с относительной ссылкой на ячейку в одну ячейку или диапазон ячеек с помощью VBA в Excel. У вас есть вопросы? Не стесняйтесь спрашивать нас. И не забудьте посетить наш сайт ExcelDemy для получения дополнительных сообщений и обновлений.

Этот метод возвращает фактическую формулу вместо выходного значения.

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

Для этого мы используем свойство Formula.

Чтобы получить формулу в ячейке A1, введите ее в макросе:

Я поставлю MsgBox перед этой строкой кода, чтобы мы могли видеть вывод в Excel:

Вернитесь в Excel и запустите его в ячейке с датой, и вот результат:

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

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

Примечания

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

Загрузите прикрепленную книгу, чтобы увидеть этот пример в Excel.

Вопрос? Спросите об этом на нашем форуме Excel

Курс Excel VBA — от новичка до эксперта

200+ видеоуроков 50+ часов обучения 200+ руководств Excel

С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)

Курс VBA — от новичка до эксперта

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

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

Ключевое слово Me в макросах Excel и VBA
Учебное пособие. Ключевое слово Me в Excel VBA позволяет ссылаться либо на текущий рабочий лист, либо на рабочую книгу.

Выбор диапазонов ячеек в Excel с помощью макросов и VBA.
Учебное пособие. В этом учебном пособии по Excel VBA особое внимание уделяется выбору диапазонов ячеек в Excel. Это.

Именование ячеек в Excel для упрощения использования формул/функций
Учебное пособие. В этом учебном пособии я собираюсь представить идею именованных ячеек. Именованная ячейка — это ячейка w.

Подпишитесь на еженедельные уроки

БОНУС: подпишитесь сейчас, чтобы загрузить нашу электронную книгу Top Tutorials!

Ссылка на наши 15 лучших учебных пособий была отправлена ​​вам, проверьте свою электронную почту, чтобы загрузить ее!

(Если вы не видите электронное письмо, проверьте папку "Спам" или "Промоакции" и обязательно добавьте нас в список контактов, чтобы получать наши электронные письма в будущем.)

Возможно использовать готовые формулы Excel с помощью программирования VBA. Это свойства, которые можно использовать с Range или Cells .

Формула VBA

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

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

Несколько формул

Вы можете вставить несколько формул одновременно, используя свойство Formula. Для этого просто определите объект Range, размер которого больше одной ячейки, и предопределенная формула будет «перетаскиваться» по всему диапазону.

"Перетаскивание" с помощью VBA:

Еще один способ выполнить то же действие — использовать метод FillDown.

Формула VBALocal

FormulaLocal добавляет на лист предопределенные формулы Excel. Однако эти формулы должны быть написаны на местном языке Excel (в случае Бразилии — на португальском).

Как и свойство Formula, FormulaLocal можно использовать для создания нескольких формул.

Формула VBAR1C1

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

FormulaR1C1 позволяет использовать готовые формулы Excel в VBA, легко интегрируя их в циклы и подсчитывая переменные.

• R относится к строкам в случае вертикального смещения.
• C относится к столбцам в случае смещения по горизонтали.
• N — целое число, указывающее, на сколько строк и/или столбцов нужно сдвинуть.

• Относительное обозначение: используйте в качестве ссылки диапазон, вызвавший его.

Если N опущено, предполагается значение 0.

В примере RC[-4]:R[5]C[-3] приводит к "B2: C7". Эти ячейки получаются путем: отступления 4 столбцов влево от RC[-4] от Range("F2") для получения "B2"; и 5 строк вниз и 3 столбца влево R[5]C[-3] от Range("F2"), чтобы получить "C7".

• Абсолютное обозначение: используйте начало таблицы в качестве ссылки.

Отрицательное значение N можно использовать только в относительной записи.

Две записи (относительная и абсолютная) могут быть объединены.

Функция рабочего листа VBA

К формулам Excel также можно получить доступ с помощью методов объекта WorksheetFunction.

К формулам Excel также можно получить доступ аналогично функциям, созданным в VBA.

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

Одним из больших преимуществ такого доступа к формулам Excel является возможность более легкого использования их в среде VBA.

Чтобы получить список доступных формул Excel в этом формате, просто введите WorksheetFunction. автоматически появится меню параметров со всеми формулами:

Читайте также:

  
• Как отфильтровать пустые ячейки в Excel
  
• Как сохранить слово без возможности редактирования
  
• Программа не может быть запущена, так как на компьютере отсутствует d3d12 dll
  
• Как создать встречу в Outlook
  
• Как выполнить автонастройку в Adobe Audition