Comprendre les cellules Excel et les fonctions de plage dans VBA
Excel est puissant. Si vous l’utilisez beaucoup, vous en savez probablement déjà beaucoup sur l’utilisation des formules ou du formatage automatique, mais avec la fonctionnalité de plage et de cellule de VBA, vous pouvez porter votre analyse Excel à un tout autre niveau.
Le problème avec l’utilisation des fonctions de cellule et de plage dans les macros VBA est qu’à un niveau avancé, la plupart des gens ont du mal à comprendre comment ces fonctions fonctionnent réellement. Leur utilisation peut devenir très déroutante. Voici comment vous pouvez les utiliser d’une manière que vous n’avez probablement jamais imaginée.
Fonctions cellulaires Visual Basic
Les fonctions de cellule et de plage vous permettent d’indiquer à votre script VBA exactement où vous souhaitez obtenir ou placer des données sur une feuille de calcul. La principale différence entre les deux cellules est ce à quoi elles se réfèrent.
Les fonctions de cellule VBA font généralement référence à une cellule à la fois, tandis que Portée Référencez un groupe de cellules à la fois.
Voici le format de la fonction de cellule :
cellule (ligne, colonne)
Cela fait référence à chaque cellule de la feuille de calcul entière. Voici un exemple de ceci, où la fonction de macrocellule VBA ne fait pas référence à une seule cellule :
Feuille(« Feuille1 »).Cellules
Au lieu de cela, ce code fait référence à la troisième cellule à partir de la gauche, dans la première ligne, c’est-à-dire cellule C1:
Faire une vidéo du jour
Feuille(« Feuille1 »).Cellules(3)
Prenons un peu de risque en référençant la cellule D15 Utilisez la fonction de cellule VBA :
Feuille(« Feuille1 »).Cellules(15,4)
Si vous préférez, vous pouvez également référencer la cellule D15 à l’aide du code VBA de fonction de cellule suivant :
cellule(15, »D ») »
—Vous pouvez utiliser des lettres de colonne.
Il y a beaucoup de flexibilité dans l’utilisation des fonctions de cellule VBA, car vous pouvez vous référer aux cellules en utilisant les nombres de colonnes et de cellules, en particulier en utilisant le traverser De nombreuses cellules (et effectuer des calculs sur celles-ci) sont très rapides.
fonction de plage
À bien des égards, la fonction Plage est beaucoup plus puissante que l’utilisation de Cellules car elle vous permet de référencer une seule cellule ou une plage spécifique de cellules à la fois. Vous ne voulez pas itérer sur la fonction Range car la référence de cellule n’est pas un nombre (sauf si vous y intégrez une fonction de cellule Excel VBA).
Le format de la fonction est le suivant :
Plage (cellule 1, cellule 2)
Chaque cellule peut être spécifiée avec un numéro de lettre. Regardons quelques exemples.
Le code suivant est une fonction de plage qui fait référence à une cellule A5:
Feuille(« Feuille1 »).Plage(« A5 »)
Ici, la fonction de plage fait référence à toutes les cellules entre A1 et E20 :
Feuille(« Feuille1 »).Plage(« A1:E20 »)
Comme mentionné ci-dessus, vous n’êtes pas obligé d’utiliser des affectations de cellules alphanumériques. Vous pouvez en fait utiliser deux fonctions de cellule VBA dans la fonction Range pour identifier une plage sur une feuille de calcul, comme ceci :
Utiliser Feuille(« Feuille1 »)
.Plage(.Cellules(1, 1), _
.Cellules(20, 5))
fini à
La portée référencée par ce code est la même que Gamme(« A1:E20 ») Une fonction. L’intérêt de l’utiliser est qu’il vous permet d’écrire du code qui gère dynamiquement les plages à l’aide de boucles.
Maintenant que vous savez comment formater les fonctions Cellules et Plage, voyons comment utiliser ces fonctions de manière créative dans le code VBA.
Utilisation des données à l’aide des fonctions de cellule
Les fonctions de cellule Visual Basic sont utiles lorsque vous avez une formule complexe que vous souhaitez utiliser sur plusieurs plages de cellules. Ces plages peuvent également exister dans plusieurs feuilles de calcul.
Prenons un exemple simple. Supposons que vous gérez une équipe commerciale de 11 personnes et que vous souhaitiez voir leurs performances chaque mois.
vous pouvez avoir Tableau 1 Suivez leurs ventes et ventes.
exister Tableau 2, Vous disposez de données pour suivre les évaluations des commentaires des clients de votre entreprise au cours des 30 derniers jours.
Si vous souhaitez utiliser les valeurs des deux feuilles pour calculer le bonus sur la première feuille, il existe plusieurs façons de procéder. Vous pouvez écrire une formule dans la première cellule qui effectue un calcul à l’aide des données des deux feuilles de calcul et la faire glisser vers le bas. Ça marchera.
Une autre façon consiste à créer un script VBA qui s’exécute lorsque vous ouvrez la feuille de calcul, ou vous le déclenchez à partir d’un bouton de commande sur la feuille de calcul afin que vous puissiez contrôler quand il calcule. Dans tous les cas, vous pouvez utiliser un script VBA pour extraire toutes les données de vente d’un fichier externe.
Alors pourquoi ne pas déclencher le calcul de la colonne bonus dans le même script à ce moment-là ?
Fonction de cellule macro Excel en action
Si vous n’avez jamais écrit de code VBA dans Excel auparavant, vous devez activer développeur Élément du menu.Pour ce faire, rendez-vous sur document > Choix. Cliquez sur Personnaliser le rubanEnfin, sélectionnez développeur Depuis le volet de gauche, Ajouter à Déplacez-le vers le volet de droite et assurez-vous que la case est cochée.
Maintenant, lorsque vous cliquez d’accord et revenez à la feuille principale et vous verrez l’option de menu développeur.
tu peux l’utiliser insérer menu pour insérer un bouton de commande, ou cliquez simplement sur Afficher le code Commencez à coder.
Dans cet exemple, vous allez configurer le script pour qu’il s’exécute à chaque ouverture du classeur.Pour ce faire, il vous suffit de cliquer Afficher le code Dans le menu développeur, collez la nouvelle fonction suivante dans la fenêtre de code.
Enfant privé Workbook_Open()
fin sous
Votre fenêtre de code ressemblera à ceci.
Vous êtes maintenant prêt à écrire du code pour gérer le calcul. À l’aide d’une seule boucle, vous pouvez parcourir les 11 employés et, à l’aide d’une fonction de cellule Excel VBA, extraire les trois variables nécessaires au calcul.
rappelles toi feuille de calcul.cellules La fonction prend une ligne et une colonne comme arguments pour identifier chaque cellule individuelle.vous devez remplacer X Utilisez des références de ligne et utilisez des nombres pour demander des données pour chaque colonne. Le nombre de lignes est le nombre d’employés, donc ce sera de 1 à 11. Les identificateurs de colonne seraient 2 pour le nombre de ventes, 3 pour les ventes et 2 dans le tableau 2 pour le score de rétroaction.
Le calcul final utilise les pourcentages suivants pour ajouter jusqu’à 100 % du score bonus total. Il est basé sur des ventes idéales de 50, 50 000 $ de ventes et un score de rétroaction de 10.
- (Nombre de ventes/50) x 0,4
- (Ventes/50 000) x 0,5
- (Score des commentaires/10) x 0,1
Cette approche simple offre aux employés des ventes des primes pondérées. Un décompte de 50, un volume de 50 000 $ et un score de 10 – ils recevront le premier prix complet du mois. Cependant, tout ce qui n’est pas parfait dans n’importe quel facteur réduira le bonus. Tout ce qui est meilleur qu’idéal augmente le bonus.
Voyons maintenant comment implémenter toute cette logique dans un script VBA très simple et court :
Enfant privé Workbook_Open()
pour x = 2 à 12
Feuilles de calcul(« Feuille1 »).Cellules(x, 4) = (Feuilles de calcul(« Feuille1 »).Cellules(x, 2).Valeur / 50) * 0,4 _
+ (Feuille(« Feuille1 »).Cellules(x, 3).Valeur / 50000) * 0,5 _
+ (Feuilles de calcul(« Feuille2 »).Cellules(x, 2).Valeur / 10) * 0,1 _
x suivant
fin sous
Ceci est la sortie de ce script.
Si vous souhaitez que la colonne bonus affiche le bonus réel en dollars au lieu d’un pourcentage, vous pouvez le multiplier par le montant maximum du bonus. Mieux encore, mettez ce montant dans une cellule sur une autre feuille et référencez-le dans votre code. Cela facilitera la modification ultérieure de la valeur sans modifier le code.
La beauté des méthodes de cellule dans VBA est que vous pouvez créer une logique très créative pour extraire des données de nombreuses cellules dans de nombreuses feuilles de calcul et les utiliser pour effectuer des calculs très complexes.
Vous pouvez utiliser la fonction Cellules pour effectuer diverses opérations sur les cellules, telles que l’effacement des cellules, la modification de la mise en forme de la police, etc.
Pour explorer davantage toutes les choses que vous pouvez faire, consultez la page Microsoft MSDN pour l’objet Cells.
Formater des cellules avec des fonctions de plage
La fonction des cellules est parfaite pour un cycle à travers plusieurs cellules. Cependant, si vous souhaitez appliquer quelque chose à une plage entière de cellules à la fois, la fonction Plage est beaucoup plus efficace. Il y a beaucoup à attendre dans la comparaison continue des plages VBA aux cellules.
Un cas d’utilisation peut consister à utiliser un script pour formater une plage de cellules si certaines conditions sont remplies.
Par exemple, supposons que toutes les ventes de tous les commerciaux totalisent plus de 400 000 $ et que vous souhaitiez mettre en surbrillance toutes les cellules de la colonne Bonus en vert pour indiquer que l’équipe a reçu des bonus d’équipe supplémentaires.
Voyons comment faire cela avec une instruction IF.
Enfant privé Workbook_Open()
Si Worksheets(« Sheet1 »).Cells(13, 3).Value > 400000 alors
ActiveSheet.Range(« D2:D12 »).Interior.ColorIndex = 4
Au cas où
fin sous
Au moment de l’exécution, si une cellule dépasse l’objectif de l’équipe, toutes les cellules de la plage seront remplies de vert.
Ceci n’est qu’un exemple simple des nombreuses choses que vous pouvez faire avec un ensemble de cellules à l’aide de la fonction Plage. D’autres choses que vous pouvez faire incluent :
- Appliquer le contour autour du groupe
- Vérifier l’orthographe du texte dans une plage de cellules
- Effacer, copier ou couper des cellules
- utiliser Trouver méthode
Assurez-vous de lire la page Microsoft MSDN de l’objet Range pour voir toutes les possibilités.
Faites passer Excel au niveau supérieur
Maintenant que vous comprenez la différence entre les fonctions Cellules et Plage, il est temps de faire passer vos scripts VBA au niveau supérieur. L’article de Dann sur l’utilisation des fonctions de comptage et d’addition dans Excel vous permettra de créer des scripts plus avancés qui accumulent très rapidement des valeurs pour tous les ensembles de données.
A propos de l’auteur
