Содержание
Excel мощный Если вы часто его используете, вы, вероятно, уже знаете много хитростей, используя формулы или автоформатирование, но используя ячейки а также Спектр Функции в VBA, вы можете поднять свою аналитику Excel на совершенно новый уровень.
Проблема с использованием функций Cells и Range в VBA состоит в том, что на продвинутых уровнях большинству людей трудно понять, как эти функции действительно работают. Использование их может очень запутать. Вот как вы можете использовать их так, как вы, вероятно, никогда не предполагали.
Функция клеток
Функции Cells и Range позволяют вам сообщить ваш VBA-скрипт
точно, где на вашем рабочем листе вы хотите получить, или разместить данные. Основное различие между двумя ячейками состоит в том, что они ссылаются.
ячейки обычно ссылаются на одну ячейку за раз, в то время как Спектр ссылается на группу клеток сразу. Формат этой функции Клетки (ряд, столбец).
Это относится к каждой ячейке на всем листе. Это единственный пример, когда функция Cells не ссылается на одну ячейку:
Worksheets("Sheet1").CellsЭто относится к третьей ячейке слева в верхнем ряду. Ячейка С1:
Worksheets("Sheet1").Cells(3)Следующий код ссылается на ячейку D15:
Worksheets("Sheet1").Cells(15,4)Если вы хотите, вы также можете ссылаться на ячейку D15 с помощью «Ячейки (15,« D »)» — вам разрешено использовать букву столбца.
Существует большая гибкость в возможности ссылаться на ячейку, используя число для столбца и ячейки, особенно с помощью сценариев, которые могут Переберите большое количество ячеек (и выполнять на них вычисления) очень быстро. Подробнее об этом мы поговорим ниже.
Функция диапазона
Во многих отношениях функция Range намного мощнее, чем использование ячеек, поскольку она позволяет вам ссылаться либо на одну ячейку, либо на определенный диапазон ячеек одновременно. Вы не собираетесь зацикливаться на функции Range, потому что ссылки на ячейки не являются числами (если только вы не внедрили в них функцию Cells).
Формат этой функции Диапазон (ячейка № 1, ячейка № 2). Каждая ячейка может быть обозначена буквой-номером.
Давайте посмотрим на несколько примеров.
Здесь функция диапазона ссылается на ячейку A5:
Worksheets("Sheet1").Range("A5")Здесь функция диапазона ссылается на все ячейки между A1 и E20:
Worksheets("Sheet1").Range("A1:E20")Как уже упоминалось выше, вам не нужно использовать числовые назначения. На самом деле вы можете использовать две функции Cells внутри функции Range для определения диапазона на листе, например так:
With Worksheets("Sheet1")
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
Приведенный выше код ссылается на тот же диапазон, что и функция Range («A1: E20»). Ценность его использования заключается в том, что он позволит вам писать код, который динамически работает с диапазонами, используя циклы
,
Теперь, когда вы понимаете, как форматировать функции Cells и Range, давайте рассмотрим, как вы можете творчески использовать эти функции в своем коде VBA.
Обработка данных с функцией ячеек
Функция Cells наиболее полезна, когда у вас есть сложная формула, которую вы хотите выполнить в нескольких диапазонах ячеек. Эти диапазоны также могут существовать на нескольких листах.
Давайте возьмем простой пример. Допустим, вы управляете командой продаж из 11 человек, и каждый месяц вы хотите посмотреть на их эффективность.
Ты можешь иметь Лист1 который отслеживает их количество продаж и объем продаж.
На Sheet2 это место, где вы отслеживаете их отзывы за последние 30 дней от клиентов вашей компании.
Если вы хотите рассчитать бонус на первом листе, используя значения из двух листов, есть несколько способов сделать это. Вы можете написать формулу в первой ячейке, которая выполняет вычисления, используя данные на двух листах, и перетащить ее вниз. Это сработает.
Альтернативой этому является создание сценария VBA, который вы запускаете при каждом открытии листа или запускаете с помощью командной кнопки на листе, чтобы вы могли контролировать время его расчета. Вы можете использовать сценарий VBA для извлечения всех данных о продажах из внешнего файла в любом случае.
Так почему бы не запустить вычисления для бонусного столбца в одном и том же сценарии в то время?
Клетки функционируют в действии
Если вы никогда ранее не писали VBA в Excel, вам нужно включить пункт меню «Разработчик». Для этого перейдите в файл > Опции. Нажмите на Настроить ленту. Наконец, выберите Developer из левой панели, добавлять на правой панели, и убедитесь, что флажок установлен.
Теперь, когда вы нажимаете Хорошо и вернитесь на основной лист, вы увидите пункт меню Разработчик.
Вы можете использовать Вставить меню, чтобы вставить командную кнопку, или просто нажмите Посмотреть код начать кодирование.
В этом примере мы будем запускать скрипт при каждом открытии книги. Для этого просто нажмите Посмотреть код из меню разработчика и вставьте следующую новую функцию в окно кода.
Private Sub Workbook_Open()
End SubВаше окно кода будет выглядеть примерно так.
Теперь вы готовы написать код для обработки расчетов. Используя один цикл, вы можете пройтись по всем 11 сотрудникам, а с помощью функции Cells ввести три переменные, необходимые для расчета.
Помните, что функция Cells имеет строку и столбец в качестве параметров для идентификации каждой отдельной ячейки. Мы сделаем «x» в строке, будем использовать число для запроса данных каждого столбца. Количество строк — это число сотрудников, поэтому оно будет от 1 до 11. Идентификатор столбца будет 2 для количества продаж, 3 для объема продаж и 2 из листа 2 для оценки обратной связи.
В окончательном расчете используются следующие проценты для суммирования до 100 процентов от общего бонуса. Он основан на идеальном количестве продаж 50, объеме продаж 50 000 долларов и обратной связи 10.
- (Количество продаж / 50) х 0,4
- (Объем продаж / 50000) х 0,5
- (Оценка обратной связи / 10) х 0,1
Этот простой подход дает продавцам взвешенный бонус. За счет 50, объем 50 000 долларов и 10 баллов они получают весь максимальный бонус за месяц. Тем не менее, все, что находится в идеальном состоянии по любому фактору, уменьшает бонус. Все, что лучше идеального, повышает бонус.
Теперь давайте посмотрим, как можно реализовать всю эту логику в очень простом и коротком VBA-скрипте:
Private Sub Workbook_Open()
For x = 2 To 12
Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub
Вот как будет выглядеть вывод этого скрипта.
Если вы хотите, чтобы в столбце «Бонус» отображался фактический долларовый бонус, а не процент, вы можете умножить его на максимальную сумму бонуса. А еще лучше, поместите эту сумму в ячейку на другом листе и укажите ее в своем коде. Это упростит изменение значения позже без необходимости редактировать код.
Прелесть функции Cells в том, что вы можете создать довольно креативную логику для извлечения данных из много клеток по многим различным листам, и выполните с ними довольно сложные вычисления.
Вы можете выполнять всевозможные действия с ячейками с помощью функции Cells — например, очистка ячеек, изменение форматирования шрифта и многое другое.
Чтобы изучить все, что вы можете сделать дальше, загляните на страницу Microsoft MSDN для объекта Cells.
Форматирование ячеек с функцией диапазона
Функция «Ячейки» идеально подходит для циклического прохождения множества ячеек по одной за раз. Но если вы хотите применить что-то сразу ко всему диапазону ячеек, функция Range будет гораздо более эффективной.
Одним из вариантов использования этого может быть форматирование диапазона ячеек с использованием скрипта, если выполняются определенные условия.
Например, скажем, если общий объем продаж всех сотрудников отдела продаж превышает 400 000 долл. США, вы хотите выделить все ячейки в столбце бонуса зеленым, чтобы показать, что команда заработала дополнительный командный бонус.
Давайте посмотрим, как вы можете сделать это с помощью заявления IF
,
Private Sub Workbook_Open()
If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then
ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4
End If
End SubКогда это выполняется, если ячейка превышает цель команды, все ячейки в диапазоне будут заполнены зеленым цветом.
Это всего лишь один простой пример множества действий, которые вы можете выполнять над группами ячеек с помощью функции Range. Другие вещи, которые вы можете сделать, включают в себя:
- Применить план вокруг группы
- Проверьте правильность написания текста внутри диапазона ячеек
- Очистить, скопировать или вырезать ячейки
- Поиск по диапазону с помощью метода «Найти»
- Намного больше
Обязательно прочитайте страницу Microsoft MSDN для объекта Range, чтобы увидеть все возможности.
Возьмите Excel на следующий уровень
Теперь, когда вы понимаете различия между функциями Cells и Range, пришло время перевести ваши сценарии VBA на новый уровень. Статья Данна об использовании функций подсчета и добавления в Excel позволит вам создавать еще более сложные сценарии, которые могут очень быстро накапливать значения во всех ваших наборах данных.
И если вы только начинаете работать с VBA в Excel, не забудьте, что у нас есть фантастическое вводное руководство по Excel VBA
для тебя тоже.
