И как это может сократить вашу рабочую нагрузку

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

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

Это справедливый вопрос, зачем вам готовить формулы ячеек с помощью VBA? Однако ответ не из простых. Возможны миллионы вариантов использования этой силы, и никакие два человека никогда не могут столкнуться с одними и теми же.

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

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

Вводить формулы в ячейки легко!

Это правда! Это просто. Даже используя VBA, довольно легко задать формулы в ячейке. Возьмем, к примеру, что ячейка A1 равна сумме ячеек A2 и A3.

Давайте использовать следующий код:

После запуска этого кода на вашем активном рабочем листе будет следующее.

Это кажется относительно простым, правда? Это! Но что, если бы у нас был длинный список значений, и мы хотели бы добавить их все. Давай попробуем. Я заполню каждую ячейку между ячейками B2 и Z3 формулой «= ROUND (RAND () * 100,0)», чтобы получить серию целых чисел, которые мы можем добавить в строку 1. Затем я копирую их и вставляю как значения, чтобы предотвратить их дальнейшее изменение.

Как видите, у нас есть набор данных, и теперь мы хотим их добавить. Можем ли мы использовать тот же код? Я собираюсь изменить код, чтобы применить формулу ко всей строке 1.

Заметили проблему?

Каждая из этих ячеек будет добавлять ячейки A2 и A3.

Теперь любой обычный пользователь Excel скажет на это: «Ну, я могу просто скопировать ячейку A1, а затем вставить ее в ячейки с B1 по Z1». Они были бы совершенно правы. Однако это очень упрощенная ситуация, которую можно использовать для решения все более сложных проблем.

Возможно, вы не знаете, какие столбцы будете добавлять.

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

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

Если что-то из этого применимо к вам, как и ко мне, знание того, как вводить абсолютные ссылки на ячейки из VBA, будет спасением. Проблема сразу видна, что мы не хотим вручную вводить B, C, D, E и т. Д., Потому что в противном случае мы должны сами заполнять формулы.

Мы можем обойти это, полагаясь на функцию Address () ячеек в VBA, а также комбинируя строки.

Я разбил следующий код, чтобы ему было легче следовать при составлении нашей формулы. Если вы посмотрите на комментарий, вы увидите формулу, как если бы вы могли записать ее в одну строку.

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

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

После этого у нас есть четыре строки, два набора по две строки для создания каждой из наших ссылок. В третьей строке цикла For мы разделяем адрес нашей известной ячейки, Cell B1, C1 или * Anything * 1, как это определено кодом VBA Cells (1, x), где x - номер столбца.

Когда у нас есть адрес, он будет в формате «$ A $ 1», поэтому мы используем функцию разделения VBA для разделения этой строки на основе «$», а затем берем строку, хранящуюся в позиции «1», которая будет быть буквой столбца, на который нам нужно сослаться.

Вам может быть интересно, почему мы не использовали элемент, хранящийся в 0 в Сплите, вместо 1, поскольку первый элемент должен быть «A», верно? К сожалению, «А» не будет первым пунктом. Первым элементом в Сплите будет «», как показано на скриншоте ниже. Этот результат обусловлен тем, что строка была разделена на «$» и все, что до того, как она стала первым элементом, все, что после него, и до тех пор, пока следующий «$» не станет элементом два, и так далее.

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

После запуска макроса в каждом столбце появятся правильные формулы! Поздравляю!

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

Загляните в мой Профиль и подпишитесь на меня, чтобы узнать больше об Excel, программировании и технологиях в целом. Спасибо за прочтение!