Home-theater-designers
Excel és potent. Si el feu servir molt, probablement ja coneixeu molts trucs amb fórmules o format automàtic, però fent ús de Cèl · lules i Rang funcions de VBA, podeu augmentar les vostres anàlisis d'Excel a un nivell completament nou.
El problema amb l’ús de les funcions Cells and Range a VBA és que, a nivells avançats, a la majoria de la gent li costa entendre com funcionen aquestes funcions. El seu ús pot resultar molt confús. A continuació s’explica com podeu fer-ne ús de maneres que probablement mai no us heu imaginat.
La funció de les cèl·lules
Les funcions Cells i Range us permeten dir-ho el vostre guió VBA exactament on voleu obtenir o col·locar dades del full de càlcul. La principal diferència entre les dues cel·les és la que fan referència.
Cèl · lules solen fer referència a una sola cel·la alhora, mentre que Rang fa referència a un grup de cel·les alhora. El format d'aquesta funció és Cel·les (fila, columna) .
Això fa referència a cada cel·la de tot el full. És l’únic exemple en què la funció de cel·les no fa referència a una sola cel·la:
Worksheets('Sheet1').CellsAixò fa referència a la tercera cel·la de l'esquerra, de la fila superior. Cèl·lula C1:
Worksheets('Sheet1').Cells(3)El codi següent fa referència a la cel·la D15:
Worksheets('Sheet1').Cells(15,4)Si volíeu, també podríeu fer referència a la cel·la D15 amb 'Cel·les (15,' D ')': podeu utilitzar la lletra de la columna.
Hi ha molta flexibilitat per poder fer referència a una cel·la mitjançant un número per a columna i cel·la, especialment amb scripts que poden fer-ho fer un bucle un gran nombre de cel·les (i realitzeu càlculs) molt ràpidament. Arribarem a això amb més detall a continuació.
La funció de rang
En molts sentits, la funció Range és molt més potent que l’ús de cel·les, ja que us permet fer referència a una sola cel·la o a un interval específic de cel·les alhora. No voldreu passar per una funció Range, perquè les referències de les cel·les no són números (tret que hi incorporeu la funció Cells).
El format d'aquesta funció és Rang (cel·la 1, cel·la 2) . Cada cel·la es pot designar mitjançant un número de lletra.
Vegem alguns exemples.
coses per instal·lar en un nou PC
Aquí, la funció d'abast fa referència a la cel·la A5:
Worksheets('Sheet1').Range('A5')Aquí, la funció d’interval fa referència a totes les cel·les entre A1 i E20:
Worksheets('Sheet1').Range('A1:E20')Com s'ha esmentat anteriorment, no cal que feu servir assignacions de cel·les de lletres numèriques. En realitat, podríeu utilitzar dues funcions de cel·les dins d'una funció de rang per identificar un interval al full, com aquest:
With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
El codi anterior fa referència al mateix rang que fa la funció Range ('A1: E20'). El valor d’utilitzar-lo és que us permetrà escriure codi que funcioni dinàmicament amb intervals mitjançant bucles.
Ara que enteneu com formatar les funcions de cel·les i rang, aprofundim en com podeu fer un ús creatiu d’aquestes funcions al vostre codi VBA.
Funció de processament de dades amb cel·les
La funció de cel·les és molt útil quan teniu una fórmula complexa que voleu realitzar en diversos rangs de cel·les. Aquests intervals també poden existir en diversos fulls.
Posem un exemple senzill. Suposem que gestioneu un equip de vendes d’11 persones i que cada mes voleu veure el seu rendiment.
Potser sí Full 1 que fa un seguiment del seu recompte de vendes i del seu volum de vendes.
Encès Full 2 és el lloc on feu un seguiment dels seus comentaris dels darrers 30 dies dels clients de la vostra empresa.
Si voleu calcular la bonificació del primer full mitjançant valors dels dos fulls, hi ha diverses maneres de fer-ho. Podeu escriure una fórmula a la primera cel·la que faci el càlcul utilitzant dades dels dos fulls i arrossegar-la cap avall. Això funcionarà.
Una alternativa a això és crear un script VBA que activareu per executar-lo cada vegada que obriu el full o bé es desencadenarà mitjançant un botó d’ordres del full perquè pugueu controlar quan es calcula. Podeu utilitzar un script VBA per extreure totes les dades de vendes d’un fitxer extern de totes maneres.
Llavors, per què no activar els càlculs de la columna de bonificació del mateix script en aquell moment?
La funció de les cèl·lules en acció
Si mai no heu escrit VBA a Excel, haureu d’habilitar l’element del menú Desenvolupador. Per fer-ho, aneu a Dossier > Opcions . Fer clic a Personalitza la cinta . Finalment, trieu Desenvolupador al tauler esquerre, Afegeix al tauler dret i assegureu-vos que la casella de selecció estigui seleccionada.
Ara, quan feu clic D'acord i torneu al full principal, veureu l'opció del menú Desenvolupador.
Podeu utilitzar el fitxer Insereix per inserir un botó d’ordres o simplement fer clic Mostra el codi per començar a codificar.
En aquest exemple, farem que l'script s'executi cada vegada que s'obre el llibre. Per fer-ho, només cal que feu clic a Mostra el codi des del menú del desenvolupador i enganxeu la següent nova funció a la finestra del codi.
Private Sub Workbook_Open()
End SubLa finestra del codi tindrà un aspecte semblant.
Ara ja esteu a punt per escriure el codi per gestionar el càlcul. Utilitzant un sol bucle, podeu passar pels 11 empleats i, amb la funció Cells, introduïu les tres variables necessàries per al càlcul.
Recordeu que la funció Cèl·lules té fila i columna com a paràmetres per identificar cada cel·la. Farem 'x' la fila, utilitzarem un número per sol·licitar les dades de cada columna. El nombre de files és el nombre d'empleats, de manera que passarà d'1 a 11. L'identificador de columna serà 2 per al recompte de vendes, 3 per al volum de vendes i 2 per al full 2 per a la puntuació de comentaris.
El càlcul final utilitza els percentatges següents per sumar el 100% de la puntuació de la bonificació total. Es basa en un recompte de vendes ideal de 50, un volum de vendes de 50.000 dòlars i una puntuació de comentaris de 10.
- (Recompte de vendes / 50) x 0,4
- (Volum de vendes / 50.000) x 0,5
- (Puntuació de comentaris / 10) x 0,1
Aquest enfocament senzill proporciona als empleats de vendes una bonificació ponderada. Per un recompte de 50, un volum de 50.000 dòlars i una puntuació de 10, obtenen la bonificació màxima completa del mes. Tanmateix, tot el que no sigui perfecte per qualsevol factor redueix la bonificació. Qualsevol cosa millor que l’ideal augmenta la bonificació.
Vegem ara com es pot treure tota aquesta lògica en un guió VBA molt senzill i curt:
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
Així serà la sortida d’aquest script.
què es pot fer amb un pi de gerds
Si voleu que la columna Bonus mostri la bonificació real en dòlars en lloc del percentatge, podeu multiplicar-la per l'import màxim de la bonificació. Millor encara, col·loqueu aquesta quantitat en una cel·la d’un altre full i consulteu-la al vostre codi. Això facilitaria canviar el valor més tard sense haver d’editar el codi.
La bellesa de la funció Cells és que podeu crear una lògica bastant creativa per obtenir dades moltes cèl·lules a través de molts fulls diferents i realitzeu-ne alguns càlculs força complexos amb ells.
Podeu realitzar tot tipus d’accions a les cel·les mitjançant la funció de cel·les: coses com esborrar les cel·les, canviar el format del tipus de lletra i molt més.
Per explorar tot el que podeu fer més, consulteu el Pàgina MSDN de Microsoft per a l'objecte Cells.
Format de cel·les amb funció de rang
Per fer un bucle a través de moltes cel·les una a la vegada, la funció de cel·les és perfecta. Però si voleu aplicar alguna cosa a un rang complet de cel·les alhora, la funció Range és molt més eficient.
Un cas d'ús pot ser formatar un interval de cel·les mitjançant script, si es compleixen determinades condicions.
Per exemple, diguem que si la suma de tot el volum de vendes de tots els empleats de vendes supera els 400.000 dòlars en total, voleu ressaltar totes les cel·les de la columna de bonificació en verd per significar que l'equip ha guanyat una bonificació d'equip addicional.
Fem una ullada a com podeu fer-ho una declaració IF .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubQuan s'executa, si la cel·la supera l'objectiu de l'equip, totes les cel·les de l'interval s'ompliran de color verd.
Aquest és només un exemple senzill de les moltes accions que podeu realitzar en grups de cel·les mitjançant la funció Range. Altres coses que podeu fer són:
- Apliqueu un esquema al voltant del grup
- Comproveu l'ortografia del text dins d'un interval de cel·les
- Esborreu, copieu o talleu les cel·les
- Cerqueu a través d'un interval amb el mètode 'Cerca'
- Molt més
Assegureu-vos de llegir el document Pàgina MSDN de Microsoft perquè l'objecte Range vegi totes les possibilitats.
Porteu Excel al següent nivell
Ara que enteneu les diferències entre les funcions Cells i Range, ha arribat el moment de portar els vostres scripts VBA al següent nivell. L’article de Dann sobre l’ús de funcions Comptar i afegir a Excel us permetrà crear scripts encara més avançats que puguin acumular valors a tots els vostres conjunts de dades molt ràpidament.
I si tot just comenceu amb VBA a Excel, no oblideu que en tenim un de fantàstic guia introductòria a Excel VBA per a tu també.
Compartir Compartir Tweet Correu electrònic Canon vs. Nikon: quina marca de càmera és millor?Canon i Nikon són els dos noms més importants de la indústria de les càmeres. Però, quina marca ofereix la millor gamma de càmeres i objectius?
Llegiu a continuació Temes relacionats- Productivitat
- Programació
- Programació Visual Basic
- Microsoft Excel
Ryan és llicenciat en Enginyeria Elèctrica. Ha treballat 13 anys en enginyeria d'automatització, 5 anys en informàtica i ara és enginyer d'aplicacions. Antic editor gerent de MakeUseOf, ha parlat a conferències nacionals sobre visualització de dades i ha estat presentat a la ràdio i la televisió nacionals.
Més de Ryan DubeSubscriu-te al nostre butlletí
Uniu-vos al nostre butlletí per obtenir consells tècnics, ressenyes, llibres electrònics gratuïts i ofertes exclusives.
Feu clic aquí per subscriure-us