photosdematures.com

Comptador VBA d'Excel

Hi ha diverses funcions a MS Excel per comptar valors tant si es tracta d’una cadena, com de nombres. El recompte es pot fer segons alguns criteris. Les funcions inclouen COUNT, COUNTA, COUNTBLANK, COUNTIF i COUNTIFS en excel. Tot i això, aquestes funcions no poden fer algunes tasques com comptar les cel·les en funció del seu color, comptar només valors en negreta, etc. És per això que crearem un comptador a VBA perquè puguem comptar per a aquest tipus de tasques a Excel.

Anem a crear alguns comptadors en Excel VBA.

Exemples de comptador Excel VBA

Podeu descarregar aquesta plantilla Excel de comptador VBA aquí: plantilla Excel de comptador VBA

A continuació es mostren exemples del comptador a VBA.

Exemple 1

Suposem que tenim dades com les anteriors per a 32 files. Crearem un comptador VBA, que comptarà els valors que siguin majors de 50 i un comptador més per comptar els valors, que siguin inferiors a 50. Crearem el codi VBA d’aquesta manera perquè l’usuari pugui tenir dades per a files il·limitades en excel.

Per fer el mateix, els passos serien:

Assegureu-vos que Desenvolupador la pestanya Excel és visible. Per fer visible la pestanya (si no), els passos són els següents:

Feu clic al botó 'Dossier' de la cinta i seleccioneu "Opció" de la llista.

Trieu "Personalitza la cinta ' a la llista, marqueu la casella per a 'Desenvolupador'i feu clic a D'acord.

Ara el Pestanya "Desenvolupador" és visible.

Inseriu el botó d'ordres mitjançant el fitxer "Insereix" disponible a la comanda Grup de ‘controls’ al Pestanya "Desenvolupador".

Mentre es prem el botó ALT , creeu el botó d’ordre amb el ratolí. Si seguim pressionant Tecla ALT, a continuació, les vores del botó d'ordre van automàticament amb la vora de les cel·les.

Feu clic amb el botó dret sobre el botó d'ordres per obrir el menú contextual (assegureu-vos que "Mode de disseny" està activat en cas contrari, no podrem obrir el menú contextual).

Trieu "Propietats" des del menú.

Canvieu les propietats del botó d'ordre, és a dir, Nom, Subtítols i Tipus de lletra, etc.

Torneu a fer clic amb el botó dret i trieu "Mostra el codi" des del menú contextual.

Visual Basic Editor s'obre ara i, per defecte, ja s'ha creat una subrutina per al botó d'ordres.

Escriurem codi ara. Declararem 3 variables. Un per a propòsits de bucle, un per comptar i un per emmagatzemar el valor de l'última fila.

Utilitzarem el codi per seleccionar la cel·la A1 i, a continuació, la regió actual de la cel·la A1 i, a continuació, baixarem a la darrera fila plena per obtenir l’últim número de fila emplenada.

Córrerem un 'Per' bucle a VBA per comprovar els valors escrits a la cel·la A2 fins a l'última cel·la emplenada de la columna A. Augmentarem el valor del 'comptador' variable per 1 si el valor és superior a 50 i canviarà el color de la font de la cel·la a "Blau" i si el valor és inferior a 50, el color de la font de la cel·la seria "Vermell".

Després de comprovar i comptar, hem de mostrar els valors. Per fer el mateix, utilitzarem "VBA MsgBox".

Codi:

 Private Sub CountingCellsbyValue_Click () Dim i, counter As Integer Dim lastrow As Long lastrow = Range ("A1"). CurrentRegion.End (xlDown). Row For i = 2 To lastrow If Cells (i, 1) .Value> 50 Then comptador = comptador + 1 cel·les (i, 1) .Font.ColorIndex = 5 altres cel·les (i, 1) .Font.ColorIndex = 3 Finalitzar si següent i MsgBox "Hi ha" & comptador i "valors superiors a 50" & _ vbCrLf & "Hi ha" valors de & lastrow - counter & "que són inferiors a 50" End Sub 

Desactiveu el fitxer "Mode de disseny" i feu clic al botó "Botó d'ordres". El resultat seria el següent.

Exemple 2

Suposem que volem crear el comptador de temps amb Excel VBA de la següent manera:

Si fem clic al botó 'Començar' botó, s'inicia el temporitzador i si fem clic al botó 'Atura' botó, el temporitzador s’atura.

Per fer el mateix, els passos serien:

Creeu un format com aquest en un full Excel.

Canvieu el format de la cel·la A2 com a 'Hh: mm: ss'.

Combineu les cel·les C3 a G7 mitjançant la combinació i centre d'Excel ordre al fitxer Grup "Alineació" al Pestanya "Inici".

Doneu la referència de la cel·la A2 per a la cel·la que acaba de combinar i, a continuació, feu el format com si fes l'estil de la font "Baskerville", mida de la lletra a 60, etc.

Creeu dos botons d'ordres 'Començar' i 'Atura' utilitzant el Ordre "Insereix" disponible al Grup de ‘controls’ al Pestanya "Desenvolupador".

Utilitzant el Ordre "Propietats" disponible al Grup de ‘controls’ al Pestanya "Desenvolupador", canvieu les propietats.

Seleccioneu els botons d'ordres un per un i trieu el botó "Mostra el codi" ordre des de "Controls" grup al 'Desenvolupador' per escriure el codi de la següent manera.

Trieu el botó d’ordre corresponent al menú desplegable.

Inseriu un mòdul a "Aquest llibre de treball" fent clic amb el botó dret al ratolí "Aquest llibre de treball" i després tria "Insereix" i llavors "Mòdul".

Escriviu el següent codi al mòdul.

Codi:

 Sub start_time () Application.OnTime Now + TimeValue ("00:00:01"), "next_moment" Final Sub Sub end_time () Application.OnTime Now + TimeValue ("00:00:01"), "next_moment",, False End Sub Sub next_moment () If Fulls de treball ("Comptador de temps"). Interval ("A2"). Valor = 0 i després Surt de fulls de treball ("Comptador de temps"). Interval ("A2"). Valor = Fulls de treball ("Temps Comptador "). Interval (" A2 "). Valor: valor i temps (" 00:00:01 ") start_time End Sub 

Hem utilitzat elpuntual‘Mètode del Aplicació object, que s’utilitza per executar un procediment a una hora programada. El procediment, que hem programat per executar, és "Moment_pròxim".

Deseu el codi. Escriviu l’hora a la cel·la A2 i feu clic a 'Començar' per iniciar el comptador de temps.

Exemple 3

Suposem que tenim una llista d’estudiants junt amb les notes obtingudes per ells. Volem comptar el nombre d’alumnes que han aprovat i han suspès.

Per fer el mateix, escriurem el codi VBA.

Els passos serien:

Obriu l'editor de Visual Basic prement drecera a Excel Alt + F11 i feu doble clic a ‘Full3 (Nombre d’estudiants)’ per inserir una subrutina basada en un esdeveniment a Sheet3.

Trieu "Full de treball" des del menú desplegable.

Mentre escollim "Full de treball" a la llista, podem veure, hi ha diversos esdeveniments al menú desplegable adjacent. Hem de triar "SelectionChange" de la llista.

Declararem la variable VBA 'Lastrow' per emmagatzemar el número de la darrera fila com a llista per a estudiants pot augmentar, 'passar' per emmagatzemar diversos estudiants que van aprovar i 'Fracassar' per emmagatzemar un nombre d'alumnes que van fracassar.

Emmagatzemarem el valor de l'últim número de fila a ‘Lastrow’.

Crearem el Bucle 'per' per comptar segons la condició.

Hem establert la condició si el nombre total de notes és superior a 99 i afegiu-hi el valor 1 'passar' variable i afegiu 1 valor al fitxer 'Fracassar' variable si falla la condició.

L’última afirmació és l’encapçalament "Resum" atrevit.

Per imprimir els valors del full, el codi seria:

Codi:

 Private Sub Worksheet_SelectionChange (ByVal Target As Range) Dim lastrow Dim Long Dim Pass As Integer Dim fail As Integer lastrow = Range ("A1"). CurrentRegion.End (xlDown). Row For i = 2 To lastrow If Cells (i, 5 )> 99 A continuació, passa = passa + 1 Si no, falla = falla + 1 Finalitza si les cel·les (1, 7) .Font.Bold = True Next i Range ("G1"). Valor = "Summary" Range ("G2"). Valor = "El nombre d'estudiants que han aprovat és" & passa l'interval ("G3"). Valor = "El nombre d'estudiants que han suspès és" & suspèn End Sub 

Ara, sempre que hi hagi un canvi de selecció, els valors es tornaran a calcular de la manera següent:

Coses que cal recordar

1. Deseu el fitxer després d’escriure codi a VBA amb l’extensió excel .xlsm, en cas contrari, la macro no funcionarà.
2. Utilitzeu el bucle "Per" quan ja es decideixi que per quantes vegades s'executarà el codi al bucle VBA.