Разумевање Екцел ћелија у односу на функције опсега у ВБА

Разумевање Екцел ћелија у односу на функције опсега у ВБА

Екцел је моћан. Ако га често користите, вероватно већ знате много трикова користећи формуле или аутоматско форматирање, али користећи Ћелије и Домет функцијама у ВБА -у, можете побољшати Екцел аналитику на потпуно нови ниво.





Проблем при кориштењу функција Ћелије и распон у ВБА -и је тај што на напредним нивоима већини људи није тешко разумјети како ове функције заправо функционирају. Њихова употреба може бити врло збуњујућа. Ево како их можете користити на начине које вероватно нисте ни замислили.



Функција ћелија

Функције ћелија и опсега вам омогућавају да кажете вашу ВБА скрипту тачно где на свом радном листу желите да добијете или поставите податке. Главна разлика између две ћелије је оно на шта се позивају.





Ћелије обично упућују на једну ћелију одједном, док Домет упућује на групу ћелија одједном. Формат ове функције је Ћелије (ред, колона) .

Ово се односи на сваку ћелију на целом листу. То је један пример где функција Ћелије не упућује на једну ћелију:



Worksheets('Sheet1').Cells

Ово се односи на трећу ћелију лево, у горњем реду. Ћелија Ц1:

Worksheets('Sheet1').Cells(3)

Следећи код упућује на ћелију Д15:



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

Ако желите, можете такође референцирати ћелију Д15 са 'Ћелије (15,' Д ')' --- можете користити слово колоне.

Постоји велика флексибилност у могућности позивања на ћелију помоћу броја за колону и ћелију, посебно са скриптама које то могу петља кроз велики број ћелија (и извршите прорачуне на њима) врло брзо. У наставку ћемо до тога доћи детаљније.



Функција распона

На много начина, функција Опсег је далеко моћнија од употребе ћелија, јер вам омогућава да референцирате једну ћелију или одређени распон ћелија одједном. Нећете желети да пролазите кроз функцију Ранге, јер референце за ћелије нису бројеви (осим ако не уградите функцију Целлс унутар ње).

Формат ове функције је Опсег (ћелија 1, ћелија 2) . Свака ћелија може бити означена словним бројем.

Погледајмо неколико примера.

како инсталирати Виндовс 10 на УСБ

Овде се функција распона позива на ћелију А5:

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

Овде се функција распона односи на све ћелије између А1 до Е20:

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

Као што је горе поменуто, не морате да користите доделе ћелија с бројевима. Заправо можете користити две функције ћелије унутар функције опсега да бисте идентификовали опсег на листу, овако:


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

Горњи код упућује на исти опсег као и функција Ранге ('А1: Е20'). Вредност његове употребе је у томе што ће вам омогућити да напишете код који динамички ради са опсезима помоћу петљи.

Сада када разумете како да форматирате функције Ћелије и опсег, заронимо у то како можете креативно користити ове функције у свом ВБА коду.

Обрада података помоћу функције ћелија

Функција Ћелије је најкориснија ако имате сложену формулу коју желите да изведете у више опсега ћелија. Ови распони такође могу постојати на више листова.

Узмимо једноставан пример. Рецимо да управљате продајним тимом од 11 људи и сваког месеца желите да погледате њихов учинак.

Можда ћете морати Схеет1 који прати њихов број продаје и њихов обим продаје.

на Схеет2 где можете пратити њихове оцене клијената ваше компаније у последњих 30 дана.

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

Алтернатива овоме је креирање ВБА скрипте коју или покрећете да покренете сваки пут када отворите лист, или је покреће командно дугме на листу тако да можете да контролишете када израчунава. Ионако бисте могли користити ВБА скрипту за извлачење свих података о продаји из спољне датотеке.

Па зашто једноставно не покренете прорачуне за бонус колону у истој скрипти у то време?

Функција ћелија на делу

Ако никада раније нисте писали ВБА у Екцелу, мораћете да омогућите ставку менија Девелопер. Да бисте то урадили, идите на Датотека > Опције . Кликните на Прилагодите траку . Коначно, изаберите Девелопер из левог окна, Додати у десно окно и проверите да ли је потврђен избор у пољу за потврду.

Сада, када кликнете У реду и вратите се на главни лист, видећете опцију менија Девелопер.

Можете користити Инсерт мени за унос командног дугмета или само кликните Виев Цоде за почетак кодирања.

У овом примеру ћемо омогућити покретање скрипте сваки пут када се радна свеска отвори. Да бисте то урадили, само кликните Виев Цоде из развојног менија и залепите следећу нову функцију у прозор кода.

Private Sub Workbook_Open()
End Sub

Прозор вашег кода ће изгледати отприлике овако.

Сада сте спремни да напишете код за руковање прорачуном. Користећи једну петљу, можете корачати кроз свих 11 запослених, а помоћу функције Ћелије повуците три променљиве потребне за прорачун.

Запамтите да функција Ћелије има ред и ступац као параметре за идентификацију сваке појединачне ћелије. Направићемо 'к' ред, користити број за тражење података сваке колоне. Број редова је број запослених, па ће то бити од 1 до 11. Идентификатор колоне ће бити 2 за број продаје, 3 за обим продаје и 2 из листа 2 за оцену повратних информација.

Коначни израчун користи сљедеће постотке за додавање до 100 посто укупног бонуса. Заснован је на идеалном броју продаје од 50, обиму продаје као 50.000 долара и оцени повратних информација од 10.

  • (Број продаје/50) к 0,4
  • (Обим продаје/50.000) к 0,5
  • (Оцена повратних информација/10) к 0,1

Овај једноставан приступ даје запосленима у продаји пондерисани бонус. За бројање 50, запремину од 50.000 долара и оцену 10 --- они добијају цео максимални бонус за месец дана. Међутим, све што је недовољно по било ком фактору смањује бонус. Све боље од идеалног повећава бонус.

Погледајмо сада како се сва та логика може извући у врло једноставном, кратком ВБА скрипту:

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

Овако ће изгледати излаз ове скрипте.

шта можеш да урадиш са пиром од малине

Ако желите да колона Бонус приказује стварни долар, а не проценат, могли бисте је помножити са максималним износом бонуса. Још боље, ставите тај износ у ћелију на другом листу и наведите то у свом коду. Ово би олакшало касније промену вредности без потребе за уређивањем кода.

Љепота функције ћелија је у томе што можете изградити прилично креативну логику из које ћете извући податке много ћелија на много различитих листова, а неке изведите прилично сложене калкулације са њима.

Можете извршити све врсте радњи на ћелијама помоћу функције Ћелије --- ствари попут брисања ћелија, промене обликовања фонта и још много тога.

Да бисте истражили све што можете учинити даље, погледајте Мицрософт МСДН страница за објекат Ћелије.

Форматирање ћелија са функцијом опсега

За петље кроз многе ћелије једну по једну, функција Ћелије је савршена. Али ако желите да примените нешто на читав низ ћелија одједном, функција Ранге је далеко ефикаснија.

Један од примера за ово може бити форматирање низа ћелија помоћу скрипте, ако су испуњени одређени услови.

На пример, рецимо да ако укупан обим продаје свих запослених у продаји премаши укупно 400.000 долара, желите да истакнете све ћелије у колони са бонусом зеленом бојом како бисте означили да је тим зарадио додатни тимски бонус.

Хајде да погледамо како то можете учинити ИФ изјава .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

Када се ово покрене, ако је ћелија изнад циља тима, све ћелије у опсегу ће бити испуњене зеленом бојом.

Ово је само један једноставан пример многих радњи које можете извршити на групама ћелија помоћу функције Ранге. Остале ствари које можете учинити укључују:

  • Нанесите скицу на групу
  • Проверите правопис текста унутар опсега ћелија
  • Очистите, копирајте или изрежите ћелије
  • Претражујте опсег помоћу методе 'Пронађи'
  • Много више

Обавезно прочитајте Мицрософт МСДН страница да објекат Ранге види све могућности.

Подигните Екцел на следећи ниво

Сада када разумете разлике између функција Ћелије и опсег, време је да своје ВБА скрипте подигнете на виши ниво. Даннов чланак о коришћењу функција бројања и додавања у Екцелу омогућиће вам да направите још напредније скрипте које могу врло брзо акумулирати вредности у свим вашим скуповима података.

А ако тек почињете са ВБА у Екцелу, не заборавите да имамо фантастичну уводни водич за Екцел ВБА и за вас.

Објави Објави Твеет Емаил Цанон против Никона: Која марка фотоапарата је боља?

Цанон и Никон су два највећа имена у индустрији фотоапарата. Али који бренд нуди бољу линију камера и објектива?

Прочитајте следеће Повезане теме
  • Продуктивност
  • Програмирање
  • Висуал Басиц програмирање
  • Мицрософт Екцел
О аутору Риан Дубе(942 објављених чланака)

Риан има диплому електротехнике. Радио је 13 година у инжењерингу аутоматизације, 5 година у ИТ -у, а сада је инжењер апликација. Бивши главни уредник МакеУсеОф -а, говорио је на националним конференцијама о визуализацији података и био је представљен на националној телевизији и радију.

Више од Риана Дубеа

Претплатите се на наш билтен

Придружите се нашем билтену за техничке савете, критике, бесплатне е -књиге и ексклузивне понуде!

Кликните овде да бисте се претплатили