Разбиране на Excel клетки срещу функции на диапазон във VBA

Разбиране на Excel клетки срещу функции на диапазон във VBA

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





Проблемът с използването на функциите „Клетки и диапазон“ във VBA е, че на напредналите нива повечето хора трудно разбират как действително работят тези функции. Използването им може да стане много объркващо. Ето как можете да ги използвате по начини, които вероятно никога не сте си представяли.



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

Функциите на клетките и обхвата ви позволяват да разберете вашия VBA скрипт точно къде на работния си лист искате да получите или да поставите данни. Основната разлика между двете клетки е това, което те препращат.





Клетки обикновено се позовават на една клетка наведнъж, докато Обхват препраща към група клетки наведнъж. Форматът на тази функция е Клетки (ред, колона) .

Това се позовава на всяка отделна клетка в целия лист. Това е единственият пример, при който функцията Cells не препраща към една клетка:



Worksheets('Sheet1').Cells

Това препраща към третата клетка отляво, на горния ред. Клетка С1:

Worksheets('Sheet1').Cells(3)

Следният код препраща към клетка D15:



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

Ако искате, можете също да посочите клетка D15 с „Клетки (15,„ D “)“ --- можете да използвате буквата на колоната.

Има голяма гъвкавост в възможността да се позовавате на клетка, използвайки номер за колона и клетка, особено със скриптове, които могат цикъл през голям брой клетки (и извършване на изчисления върху тях) много бързо. Ще стигнем до това по -подробно по -долу.



Функцията Range

В много отношения функцията Range е далеч по -мощна от използването на клетки, защото ви позволява да се позовавате или на една клетка, или на определен диапазон от клетки, наведнъж. Няма да искате да преминете през функция Range, защото препратките за клетки не са числа (освен ако не вградите функцията Cells вътре в нея).

Форматът на тази функция е Обхват (клетка #1, клетка #2) . Всяка клетка може да бъде обозначена с буквен номер.

Нека разгледаме няколко примера.

как да получите достъп до флаш устройство на windows 10

Тук функцията за диапазон препраща към клетка A5:

Worksheets('Sheet1').Range('A5')

Тук функцията range обхваща всички клетки между A1 до E20:

Worksheets('Sheet1').Range('A1:E20')

Както бе споменато по-горе, не е нужно да използвате присвояване на клетки с цифри. Всъщност можете да използвате две функции Cells вътре във функция Range, за да идентифицирате диапазон в листа, като този:


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

Горният код препраща към същия диапазон като функцията Range ('A1: E20'). Стойността при използването му е, че ще ви позволи да напишете код, който динамично работи с диапазони, използвайки цикли.

Сега, когато разбрахте как да форматирате функциите „Клетки и диапазон“, нека се задълбочим в това как можете да използвате творчески тези функции във вашия код на VBA.

Обработка на данни с функция за клетки

Функцията Клетки е най -полезна, когато имате сложна формула, която искате да изпълните в множество диапазони от клетки. Тези диапазони могат да съществуват и на няколко листа.

Нека вземем прост пример. Да предположим, че управлявате екип по продажби от 11 души и всеки месец искате да гледате представянето им.

Може би сте имали Лист 1 който проследява техния брой продажби и техния обем.

На Лист 2 е мястото, където проследявате оценката им за обратна връзка за последните 30 дни от клиентите на вашата компания.

Ако искате да изчислите бонуса на първия лист, като използвате стойности от двата листа, има няколко начина да направите това. Можете да напишете формула в първата клетка, която извършва изчислението, използвайки данни в двата листа, и да я плъзнете надолу. Това ще свърши работа.

Алтернатива на това е създаването на VBA скрипт, който или задействате, за да стартирате всеки път, когато отворите листа, или се задейства чрез команден бутон на листа, за да можете да контролирате кога той изчислява. Все пак може да използвате VBA скрипт, за да изтеглите всички данни за продажбите от външен файл.

Така че защо просто не задействате изчисленията за бонус колоната в същия скрипт по това време?

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

Ако никога преди не сте писали VBA в Excel, ще трябва да активирате елемента от менюто Developer. За да направите това, отидете на Файл > Настроики . Кликнете върху Персонализирайте лентата . Накрая изберете Developer от левия прозорец, Добавяне го в десния прозорец и се уверете, че квадратчето за отметка е поставено.

Сега, когато щракнете Добре и се върнете към основния лист, ще видите опцията от менюто за програмисти.

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

В този пример ще накараме скрипта да се изпълнява при всяко отваряне на работната книга. За да направите това, просто щракнете Преглед на кода от менюто за програмисти и поставете следната нова функция в прозореца на кода.

Private Sub Workbook_Open()
End Sub

Прозорецът на вашия код ще изглежда така.

Сега сте готови да напишете кода, за да се справите с изчислението. Използвайки един цикъл, можете да преминете през всичките 11 служители и с функцията Клетки издърпайте трите променливи, необходими за изчислението.

Не забравяйте, че функцията Клетки има ред и колона като параметри за идентифициране на всяка отделна клетка. Ще направим '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.

Форматиране на клетки с функция Range

За циклично преминаване през много клетки една по една, функцията 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 на следващото ниво

Сега, когато разбирате разликите между функциите „Клетки“ и „Обхват“, е време да пренесете скриптовете си VBA на следващото ниво. Статията на Дан за използването на функции за броене и добавяне в Excel ще ви позволи да изградите още по -усъвършенствани скриптове, които могат да натрупват стойности във всичките ви набори от данни много бързо.

И ако тепърва започвате с VBA в Excel, не забравяйте, че имаме фантастично уводно ръководство за Excel VBA и за теб.

Дял Дял Туит електронна поща Canon срещу Nikon: Коя марка фотоапарат е по -добра?

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

Прочетете Напред Свързани теми
  • Производителност
  • Програмиране
  • Visual Basic програмиране
  • Microsoft Excel
За автора Райън Дубе(942 публикувани статии)

Райън има бакалавърска степен по електротехника. Работил е 13 години в областта на автоматизацията, 5 години в областта на информационните технологии, а сега е инженер по приложения. Бивш управляващ редактор на MakeUseOf, той говори на национални конференции за визуализация на данни и е включен в националната телевизия и радио.

Още от Райън Дубе

Абонирайте се за нашия бюлетин

Присъединете се към нашия бюлетин за технически съвети, рецензии, безплатни електронни книги и изключителни оферти!

Щракнете тук, за да се абонирате