Понимание ячеек Excel и функций диапазона в VBA

Понимание ячеек Excel и функций диапазона в VBA

Excel - это мощный инструмент. Если вы его часто используете, вы, вероятно, уже знаете много трюков с использованием формул или автоформатирования, но с использованием Клетки а также Диапазон функций в VBA, вы можете поднять аналитику Excel на совершенно новый уровень.





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



Функция клеток

Функции Cells и Range позволяют вам сказать ваш сценарий VBA где именно на листе вы хотите получить или разместить данные. Основное различие между двумя ячейками - это то, на что они ссылаются.





Клетки обычно ссылаются на одну ячейку за раз, в то время как Диапазон ссылается сразу на группу ячеек. Формат этой функции: Ячейки (строка, столбец) .

Это ссылается на каждую ячейку на всем листе. Это один из примеров, когда функция Cells не ссылается ни на одну ячейку:



Worksheets('Sheet1').Cells

Это ссылка на третью слева ячейку в верхнем ряду. Ячейка C1:

Worksheets('Sheet1').Cells(3)

Следующий код ссылается на ячейку D15:



Worksheets('Sheet1').Cells(15,4)

Если вы хотите, вы также можете ссылаться на ячейку D15 с помощью «Cells (15, 'D')» - вам разрешено использовать букву столбца.

Возможность ссылаться на ячейку с использованием номера для столбца и ячейки дает большую гибкость, особенно со сценариями, которые могут Переберите большое количество ячеек (и производить по ним расчеты) очень быстро. Мы вернемся к этому более подробно ниже.



Функция диапазона

Во многих отношениях функция Range намного мощнее, чем использование ячеек, потому что она позволяет вам ссылаться либо на одну ячейку, либо на определенный диапазон ячеек одновременно. Вы не захотите перебирать функцию Range, потому что ссылки на ячейки не являются числами (если вы не встроили в нее функцию Cells).

Формат этой функции: Диапазон (ячейка №1, ячейка №2) . Каждую ячейку можно обозначить буквой-номером.

Давайте посмотрим на несколько примеров.

как увидеть старые сообщения на iphone

Здесь функция диапазона ссылается на ячейку 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.

Обработка данных с помощью функции ячеек

Функция 'Ячейки' наиболее полезна, когда у вас есть сложная формула, которую вы хотите выполнить для нескольких диапазонов ячеек. Эти диапазоны также могут существовать на нескольких листах.

Возьмем простой пример. Допустим, вы управляете отделом продаж из 11 человек и каждый месяц хотите следить за их работой.

Ты можешь иметь Лист1 который отслеживает их количество продаж и их объем продаж.

На Лист2 Здесь вы отслеживаете их рейтинг отзывов клиентов вашей компании за последние 30 дней.

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

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

Так почему бы просто не запустить вычисления для столбца бонусов в том же скрипте в это время?

Функция клеток в действии

Если вы никогда раньше не писали VBA в Excel, вам нужно включить пункт меню «Разработчик». Для этого перейдите в Файл > Параметры . Нажмите на Настроить ленту . Наконец, выберите Разработчик на левой панели, Добавлять его на правой панели и убедитесь, что флажок установлен.

Теперь, когда вы нажимаете Ok и вернитесь к основному листу, вы увидите опцию меню разработчика.

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

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

Private Sub Workbook_Open()
End Sub

Окно вашего кода будет выглядеть примерно так.

Теперь вы готовы написать код для обработки вычислений. Используя один цикл, вы можете пройти по всем 11 сотрудникам, а с помощью функции Cells вытащите три переменные, необходимые для расчета.

Помните, что функция Cells имеет строку и столбец в качестве параметров для идентификации каждой отдельной ячейки. Мы сделаем строку «x», используя число для запроса данных каждого столбца. Количество строк - это количество сотрудников, поэтому оно будет от 1 до 11. Идентификатором столбца будет 2 для количества продаж, 3 для объема продаж и 2 из листа 2 для оценки обратной связи.

В окончательном расчете используются следующие проценты, чтобы добавить до 100 процентов от общего бонусного балла. Он основан на идеальном количестве продаж 50, объеме продаж 50 000 долларов и 10 баллах по отзывам.

  • (Количество продаж / 50) x 0,4
  • (Объем продаж / 50 000) x 0,5
  • (Оценка обратной связи / 10) x 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.

Форматирование ячеек с помощью функции диапазона

Функция 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 для тебя тоже.

Делиться Делиться Твитнуть Эл. адрес Canon против Nikon: какой бренд камеры лучше?

Canon и Nikon - два крупнейших имени в индустрии фотоаппаратов. Но какой бренд предлагает лучшую линейку камер и объективов?

Читать далее Похожие темы
  • Продуктивность
  • Программирование
  • Программирование на Visual Basic
  • Майкрософт Эксель
Об авторе Райан Дьюб(Опубликовано 942 статей)

Райан имеет степень бакалавра электротехники. Он проработал 13 лет в области автоматизации, 5 лет в ИТ, а теперь работает инженером по приложениям. Бывший управляющий редактор MakeUseOf, он выступал на национальных конференциях по визуализации данных и выступал на национальном телевидении и радио.

Ещё от Ryan Dube

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

Подпишитесь на нашу рассылку технических советов, обзоров, бесплатных электронных книг и эксклюзивных предложений!

Нажмите здесь, чтобы подписаться