Excel est puissant. Si vous l'utilisez beaucoup, vous connaissez probablement déjà beaucoup de trucs en utilisant des formules ou l'autoformatage, mais en utilisant les fonctions Cells et Range dans VBA, vous pouvez augmenter vos analyses Excel à un tout autre niveau.
Le problème avec l'utilisation des fonctions Cells et Range dans VBA est qu'aux niveaux avancés, la plupart des gens ont du mal à comprendre comment ces fonctions fonctionnent réellement. Les utiliser peut devenir très confus. Voici comment vous pouvez les utiliser d'une manière que vous n'avez probablement jamais imaginée.
La fonction des cellules
4 Les erreurs que vous pouvez éviter lors de la programmation des macros Excel avec VBA 4 Les erreurs que vous pouvez éviter lors de la programmation des macros Excel avec VBA Le code et les macros simples sont les clés des superpuissances Microsoft Excel. Même les non-programmeurs peuvent facilement ajouter des fonctionnalités impressionnantes à leurs feuilles de calcul avec Virtual Basics for Applications (VBA). Évitez simplement ces erreurs de programmation de débutant! Lire plus exactement où sur votre feuille de calcul que vous souhaitez obtenir, ou placer des données. La principale différence entre les deux cellules est ce qu'elles référencent.
Les cellules font généralement référence à une seule cellule à la fois, tandis que Range 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 l'exemple où la fonction Cells ne fait référence à aucune cellule:
Worksheets("Sheet1").Cells Ceci fait référence à la troisième cellule à partir de la gauche, de la rangée supérieure. Cellule C1:
Worksheets("Sheet1").Cells(3) Le code suivant référence la cellule D15:
Worksheets("Sheet1").Cells(15, 4) Si vous le souhaitez, vous pouvez également faire référence à la cellule D15 avec "Cellules (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 traverser un grand nombre de cellules (et effectuer des calculs dessus) très rapidement. Nous y reviendrons plus en détail ci-dessous.
La fonction Range
À de nombreux égards, la fonction Range 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 faire de boucle sur une fonction Range, car les références pour les cellules ne sont pas des nombres (sauf si vous incorporez la fonction Cells à l'intérieur).
Le format de cette fonction est Range (Cellule 1, Cellule 2) . Chaque cellule peut être désignée par un numéro de lettre.
Regardons quelques exemples.
Ici, la fonction range fait référence à la cellule A5:
Worksheets("Sheet1").Range("A5") Ici, la fonction range fait référence à toutes les cellules entre A1 et E20:
Worksheets("Sheet1").Range("A1:E20") Comme mentionné ci-dessus, vous n'avez pas besoin d'utiliser des affectations de cellules de nombres-lettres. Vous pouvez réellement utiliser deux fonctions Cells à l'intérieur d'une fonction Range pour identifier une plage sur la feuille, comme ceci:
With Worksheets("Sheet1") .Range(.Cells(1, 1), _ .Cells(20, 5)) End With Le code ci-dessus fait référence à la même plage que la fonction Range ("A1: E20"). La valeur de son utilisation, c'est qu'il vous permettrait d'écrire du code qui fonctionne dynamiquement avec des gammes utilisant des boucles Comment les boucles While-Do dans la programmation informatique Comment fonctionnent les boucles While-Do dans la programmation informatique Les boucles sont l'un des premiers types de contrôle ll apprendra en programmation. Vous connaissez probablement les boucles while et for, mais que fait une boucle do-while? Lire la suite .
Maintenant que vous avez compris comment formater les fonctions Cells et Range, expliquons comment utiliser ces fonctions de manière créative dans votre code VBA.
Traitement des données avec la fonction des cellules
La fonction Cells est plus 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. Disons que vous gérez une équipe de vente de 11 personnes, et chaque mois vous voulez regarder leur performance.
Vous pouvez avoir Sheet1 qui suit leur nombre de ventes, et leur volume de ventes.
Sur Sheet2, vous pouvez suivre leur évaluation des 30 derniers jours auprès des clients de votre entreprise.
Si vous voulez calculer le bonus sur la première feuille en utilisant les valeurs des deux feuilles, il y a plusieurs façons de le faire. Vous pouvez écrire une formule dans la première cellule qui effectue le calcul en utilisant des données sur les deux feuilles et le faire glisser vers le bas. Ça va marcher.
Une alternative à ceci est de créer un script VBA que vous déclenchez à 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 utiliser un script VBA pour extraire toutes les données de vente d'un fichier externe de toute façon.
Alors pourquoi ne pas simplement déclencher les calculs de la colonne bonus dans le même script à ce moment-là?
La fonction des cellules en action
Si vous n'avez jamais écrit VBA dans Excel auparavant, vous devez activer l'élément de menu Developer. Pour ce faire, allez dans Fichier > Options . Cliquez sur Personnaliser le ruban . Enfin, choisissez Développeur dans le volet de gauche, ajoutez- le au volet de droite et assurez-vous que la case à cocher est sélectionnée.
Maintenant, lorsque vous cliquez sur OK et revenez à la feuille principale, vous verrez l'option de menu Developer.
Vous pouvez utiliser le menu Insertion pour insérer un bouton de commande ou simplement cliquer sur Afficher le code pour commencer le codage.
Dans cet exemple, nous allons exécuter le script chaque fois que le classeur est ouvert. Pour ce faire, cliquez simplement sur Afficher le code dans le menu du développeur et collez la nouvelle fonction suivante dans la fenêtre de code.
Private Sub Workbook_Open() End Sub Votre fenêtre de code ressemblera à ceci.
Vous êtes maintenant prêt à écrire le code pour gérer le calcul. En utilisant une seule boucle, vous pouvez parcourir les 11 employés, et avec la fonction Cellules, tirez les trois variables nécessaires pour le calcul.
Rappelez-vous que la fonction Cells a une ligne et une colonne comme paramètres pour identifier chaque cellule individuelle. Nous allons faire "x" la ligne, utilisez un nombre pour demander les données de chaque colonne. Le nombre de lignes correspond au nombre d'employés. Il est donc compris entre 1 et 11. L'identifiant de colonne est 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. Il est basé sur un chiffre d'affaires idéal de 50, un volume de ventes de 50 000 $ et une note de rétroaction de 10.
- (Nombre de ventes / 50) x 0, 4
- (Volume des ventes / 50 000) x 0, 5
- (Feedback Score / 10) x 0.1
Cette approche simple donne aux employés des ventes un bonus pondéré. 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 un facteur réduit le bonus. Rien de mieux que l'idéal augmente le bonus.
Voyons maintenant comment toute cette logique peut être retirée dans un script VBA très simple et court:
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 C'est ce que la sortie de ce script va ressembler.
Si vous souhaitez que la colonne Bonus affiche le bonus en dollars réel 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 Cells est que vous pouvez créer une logique assez créative pour extraire des données de nombreuses cellules sur plusieurs feuilles différentes, et effectuer des calculs assez complexes avec celles-ci.
Vous pouvez effectuer toutes sortes d'actions sur les cellules à l'aide de la fonction Cellules, par exemple effacer les cellules, modifier la mise en forme des polices, etc.
Pour explorer tout ce que vous pouvez faire plus loin, consultez la page Microsoft MSDN pour l'objet Cells.
Formatage des cellules avec fonction de plage
Pour boucler à travers de nombreuses cellules une à la fois, la fonction Cells est parfaite. Mais si vous souhaitez appliquer tout un ensemble à une gamme entière de cellules, la fonction Range est bien plus efficace.
Un cas d'utilisation pour cela pourrait être de formater une plage de cellules en utilisant un script, si certaines conditions sont remplies.
Par exemple, si le total des ventes dépasse 400 000 dollars, vous souhaitez mettre en surbrillance toutes les cellules de la colonne bonus pour indiquer que l'équipe a gagné un bonus d'équipe supplémentaire.
Jetons un coup d'oeil à la façon dont vous pouvez le faire avec une instruction IF Comment utiliser les instructions IF dans Microsoft Excel Comment utiliser les instructions IF dans Microsoft Excel Que vous soyez un expert chevronné ou un débutant de tableur, vous voudrez peut-être consulter cette page guide des instructions IF dans Excel. Lire la suite .
Private Sub Workbook_Open() If Worksheets("Sheet1").Cells(13, 3).Value>400000 Then ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4 End If End Sub Lorsque cela se produit, si la cellule dépasse le but de l'équipe, toutes les cellules de la plage seront remplies en vert.
Ceci est juste un exemple simple des nombreuses actions que vous pouvez effectuer sur des groupes de cellules en utilisant la fonction Range. D'autres choses que vous pouvez faire incluent:
- Appliquer un contour autour du groupe
- Vérifier l'orthographe du texte dans une plage de cellules
- Effacer, copier ou couper des cellules
- Rechercher dans une plage avec la méthode "Rechercher"
- Beaucoup plus
Assurez-vous de lire la page Microsoft MSDN de l'objet Range pour voir toutes les possibilités.
Amenez Excel au niveau supérieur
Maintenant que vous comprenez les différences entre les fonctions Cells et Range, il est temps de passer votre script VBA au niveau suivant. L'article de Dann sur l'utilisation des fonctions de comptage et d'ajout dans Excel vous permettra de créer des scripts encore plus avancés qui accumuleront très rapidement des valeurs dans tous vos ensembles de données.
Et si vous commencez simplement avec VBA dans Excel, n'oubliez pas que nous avons un guide d'introduction fantastique à Excel VBA Tutoriel de programmation Excel VBA pour les débutants Tutoriel de programmation VBA Excel pour les débutants VBA est un outil électrique Microsoft Office. Vous pouvez l'utiliser pour automatiser des tâches avec des macros, définir des déclencheurs et bien plus encore. Nous vous présenterons la programmation visuelle de base d'Excel avec un projet simple. Lisez plus pour vous aussi.