Excel est puissant. Si vous l'utilisez beaucoup, vous connaissez probablement déjà beaucoup d'astuces utilisant des formules ou la mise en forme automatique, mais en utilisant des Cellules et Portée fonctions dans VBA, vous pouvez booster vos analyses Excel à un tout autre niveau.
Le problème avec l'utilisation des fonctions Cellules et Plage dans VBA est qu'aux niveaux avancés, 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'auriez probablement jamais imaginée.
Les fonctions Cellules et Plage vous permettent d'indiquer à votre script VBA exactement où vous souhaitez obtenir ou placer des données sur votre feuille de calcul. La principale différence entre les deux cellules est ce à quoi elles font référence.
Cellules font généralement référence à une seule cellule à la fois, tandis que Plage fait référence à un groupe de cellules à la fois. Le format de cette fonction est Cellules (ligne, colonne) .
Cela fait référence à chaque cellule de la feuille entière. C'est le seul exemple où la fonction Cellules ne fait pas référence à une seule cellule :
Feuilles de calcul("Sheet1").Cellules Cela fait référence à la troisième cellule à partir de la gauche, de la rangée supérieure. Cellule C1 :
Feuilles de calcul("Sheet1").Cells(3) Le code suivant fait référence à la cellule D15 :
Feuilles de calcul("Sheet1").Cells(15,4) Si vous le souhaitez, vous pouvez également référencer la cellule D15 avec "Cells(15,"D")" --- vous êtes autorisé à utiliser la lettre de la colonne.
Il y a beaucoup de flexibilité dans la possibilité de référencer une cellule en utilisant un nombre pour la colonne et la cellule, en particulier avec des scripts qui peuvent faire une boucle un grand nombre de cellules (et effectuer des calculs sur celles-ci) très rapidement. Nous y reviendrons plus en détail ci-dessous.
À bien des égards, la fonction Plage est bien plus puissante que l'utilisation de Cellules, car elle vous permet de référencer soit une seule cellule, soit une plage spécifique de cellules, en une seule fois. Vous ne voudrez pas parcourir une fonction Range, car les références des cellules ne sont pas des nombres (sauf si vous y intégrez la fonction Cells).
Le format de cette fonction est Range(Cell #1,Cell #2) . Chaque cellule peut être désignée par une lettre-numéro.
Regardons quelques exemples.
Ici, la fonction de plage fait référence à la cellule A5 :
Feuilles de travail("Sheet1").Range("A5") Ici, la fonction de plage fait référence à toutes les cellules entre A1 et E20 :
Feuilles de calcul("Sheet1").Range("A1:E20") Comme mentionné ci-dessus, vous n'êtes pas obligé d'utiliser des affectations de cellules numéro-lettre. Vous pouvez en fait utiliser deux fonctions Cellules à l'intérieur d'une fonction Plage pour identifier une plage sur la feuille, comme ceci :
Avec feuilles de calcul("Feuille1")
.Plage(.Cellules(1, 1), _
.Cellules(20, 5))
Terminer par
Le code ci-dessus fait référence à la même plage que la fonction Range("A1:E20"). L'intérêt de l'utiliser est qu'il vous permettrait d'écrire du code qui fonctionne dynamiquement avec des plages à l'aide de boucles.
Maintenant que vous comprenez comment formater les fonctions Cellules et Plage, voyons comment vous pouvez faire un usage créatif de ces fonctions dans votre code VBA.
La fonction Cellules est particulièrement utile lorsque vous avez une formule complexe que vous souhaitez exécuter sur plusieurs plages de cellules. Ces plages peuvent également exister sur plusieurs feuilles.
Prenons un exemple simple. Imaginons que vous gérez une équipe de vente de 11 personnes et que vous souhaitiez suivre leurs performances chaque mois.
Vous avez peut-être Feuille 1 qui suit leur nombre de ventes et leur volume de ventes.
Sur Feuille2 est l'endroit où vous suivez leur évaluation des commentaires des clients de votre entreprise au cours des 30 derniers jours.
Si vous souhaitez calculer le bonus sur la première feuille en utilisant les valeurs des deux feuilles, il existe plusieurs façons de le faire. Vous pouvez écrire une formule dans la première cellule qui effectue le calcul en utilisant les données des deux feuilles et la faire glisser vers le bas. Cela fonctionnera.
Une alternative à cela consiste à créer un script VBA que vous déclenchez pour s'exécuter chaque fois que vous ouvrez la feuille, ou déclenché par un bouton de commande sur la feuille afin que vous puissiez contrôler quand il calcule. Vous pouvez quand même utiliser un script VBA pour extraire toutes les données de vente d'un fichier externe.
Alors pourquoi ne pas simplement déclencher les calculs de la colonne bonus dans le même script à ce moment ?
Si vous n'avez jamais écrit VBA dans Excel auparavant, vous devrez activer l'élément de menu Développeur. Pour ce faire, allez dans Fichier> Options . Cliquez sur Personnaliser le ruban . Enfin, choisissez Développeur dans le volet de gauche, Ajouter dans le volet de droite et assurez-vous que la case est cochée.
Maintenant, lorsque vous cliquez sur OK et revenez à la feuille principale, vous verrez l'option de menu Développeur.
Vous pouvez utiliser le bouton Insérer menu pour insérer un bouton de commande, ou cliquez simplement sur Afficher le code pour commencer à coder.
Dans cet exemple, nous allons exécuter le script à chaque ouverture du classeur. Pour ce faire, cliquez simplement sur Afficher le code dans le menu développeur et collez la nouvelle fonction suivante dans la fenêtre de code.
Sous-classement privé_Open()
Fin du sous-titre Votre fenêtre de code ressemblera à ceci.
Vous êtes maintenant prêt à écrire le code pour gérer le calcul. À l'aide d'une seule boucle, vous pouvez parcourir les 11 employés et, avec la fonction Cellules, extraire les trois variables nécessaires au calcul.
N'oubliez pas que la fonction Cells a une ligne et une colonne comme paramètres pour identifier chaque cellule individuelle. Nous allons faire "x" la ligne, utiliser un nombre pour demander les données de chaque colonne. Le nombre de lignes correspond au nombre d'employés, il sera donc compris entre 1 et 11. L'identifiant de colonne sera 2 pour le nombre de ventes, 3 pour le volume des ventes et 2 à partir de la feuille 2 pour le score de rétroaction.
Le calcul final utilise les pourcentages suivants pour ajouter jusqu'à 100 % du score total du bonus. Il est basé sur un nombre de ventes idéal de 50, un volume de ventes de 50 000 $ et un score de rétroaction de 10.
Cette approche simple donne aux vendeurs une prime pondérée. Pour un compte de 50, un volume de 50 000 $ et un score de 10, ils obtiennent la totalité du bonus maximum pour le mois. Cependant, tout ce qui n'est pas parfait sur n'importe quel facteur réduit le bonus. Tout ce qui est mieux qu'idéal augmente le bonus.
Voyons maintenant comment toute cette logique peut être extraite dans un script VBA court et très simple :
Sous-classement privé_Open()
Pour x =2 à 12
Feuilles de calcul("Feuille1").Cellules(x, 4) =(Feuilles de calcul("Feuille1").Cellules(x, 2).Valeur / 50) * 0,4 _
+ (Feuilles de calcul("Feuille1").Cellules(x, 3).Valeur / 50000) * 0,5 _
+ (Feuilles de calcul("Feuille2").Cellules(x, 2).Valeur / 10) * 0,1 _
X suivant
Sous-titre de fin
Voici à quoi ressemblera la sortie de ce script.
Si vous souhaitez que la colonne Bonus affiche le bonus réel en dollars plutôt que le pourcentage, vous pouvez le multiplier par le montant maximum du bonus. Mieux encore, placez ce montant dans une cellule sur une autre feuille et référencez-le dans votre code. Cela faciliterait la modification ultérieure de la valeur sans avoir à modifier votre code.
La beauté de la fonction Cellules est que vous pouvez créer une logique assez créative pour extraire des données de de nombreuses cellules sur de nombreuses feuilles différentes et effectuez des calculs assez complexes avec.
Vous pouvez effectuer toutes sortes d'actions sur les cellules à l'aide de la fonction Cellules, comme effacer les cellules, modifier la mise en forme de la police et bien plus encore.
Pour explorer davantage tout ce que vous pouvez faire, consultez la page Microsoft MSDN de l'objet Cellules.
Pour parcourir plusieurs cellules une par une, la fonction Cellules est parfaite. Mais si vous souhaitez appliquer quelque chose à une plage entière de cellules en une seule fois, la fonction Plage est beaucoup plus efficace.
Un cas d'utilisation pour cela pourrait être de formater une plage de cellules à l'aide d'un script, si certaines conditions sont remplies.
Par exemple, supposons que si le décompte de tous les volumes de ventes de tous les employés des ventes dépasse 400 000 USD au total, vous souhaitez mettre en surbrillance toutes les cellules de la colonne des bonus en vert pour indiquer que l'équipe a gagné un bonus d'équipe supplémentaire.
Voyons comment vous pouvez le faire avec une instruction IF.
Sous-classement privé_Open()
Si Worksheets("Sheet1").Cells(13, 3).Value> 400000 Alors
ActiveSheet.Range("D2:D12").Interior.ColorIndex =4
Fin si
Fin du sous-titre Lorsque cela s'exécute, si la cellule dépasse l'objectif de l'équipe, toutes les cellules de la plage seront remplies en vert.
Ceci n'est qu'un exemple simple des nombreuses actions que vous pouvez effectuer sur des groupes de cellules à l'aide de la fonction Plage. Voici d'autres choses que vous pouvez faire :
Assurez-vous de lire la page Microsoft MSDN de l'objet Range pour voir toutes les possibilités.
Maintenant que vous comprenez les différences entre les fonctions Cellules et Plage, il est temps de faire passer votre script 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 encore plus avancés qui peuvent accumuler très rapidement des valeurs dans tous vos ensembles de données.
Et si vous débutez avec VBA dans Excel, n'oubliez pas que nous avons également un fantastique guide d'introduction à Excel VBA pour vous.