Вставить формулу Excel vba в ячейку
Обновлено: 04.07.2024
Обратите внимание, что вам не нужно выбирать ячейку, чтобы ввести в нее значение, вы можете сделать это из любого места на листе. Например, из ячейки G45 вы можете написать:
Range("A1").Value = 32
Вы даже можете изменить значение ячеек на другом листе с помощью:
Sheets("SoAndSo") .Range("A1").Value = 32
Вы также можете ввести одно и то же значение во многие ячейки с помощью:
Range("A1:B32").Value = 32
Если вы хотите ввести текстовую строку в ячейку, вам нужно использовать двойные кавычки, например:
Range("A1").Value = "Peter" вернет Peter. Обратите внимание на кавычки.
Если вы хотите ввести текст с отображением двойных кавычек в ячейке, вам нужно утроить двойные кавычки, например:
Range("A1").Value = """ Peter"""< /i> вернет "Питер" в кавычках
Формула
Самый лучший и простой способ ввести формулу в ячейку программно (даже если вы профессионал) – это написать ее в ячейке, выбрать ее со знаком равенства в строке формул и вставить ее между кавычками в строке формул. код. Этот подход становится еще более удобным, когда вы начинаете разрабатывать сложные формулы или начинаете использовать переменные и другие адреса ячеек в своих формулах, как показано в части 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. автоматически появится меню параметров со всеми формулами:
Читайте также: