Description des cellules Excel par rapport aux fonctions de plage dans VBA

Added 2018-03-28

Excel est puissant. Si vous l’utilisez beaucoup, vous connaissez probablement déjà beaucoup d’astuces avec les formules ou le formatage automatique, mais en utilisant Cellules  et Intervalle  fonctions de VBA, vous pouvez améliorer vos analyses Excel à un tout autre niveau.

Le problème lié à 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 déroutant. Voici comment vous pouvez les utiliser d’une manière que vous n’auriez probablement jamais imaginée.

La fonction des cellules

Cellules  référence généralement une seule cellule à la fois, tandis que Intervalle  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 cas où la fonction Cells ne fait pas référence à une seule cellule:

Feuilles de calcul ("Sheet1"). Cellules

Ceci fait référence à la troisième cellule à partir de la gauche, dans la rangée supérieure. Cellule C1:

Feuilles de calcul ("Sheet1"). Cellules (3)

Le code suivant fait référence à la cellule D15:

Feuilles de calcul ("Sheet1"). Cellules (15,4)

Si vous le souhaitez, vous pouvez également référencer la cellule D15 avec «Cellules (15», «D») »- vous êtes autorisé à utiliser la lettre de la colonne.

Il est très flexible de pouvoir référencer une cellule en utilisant un numéro de colonne et de cellule, en particulier avec des scripts pouvant faire une boucle  un grand nombre de cellules (et y effectuer des calculs) très rapidement. Nous y reviendrons plus en détail ci-dessous.

La fonction de portée

À bien des égards, la fonction Range est beaucoup plus puissante que Cellules, car elle vous permet de référencer simultanément une seule cellule ou une plage spécifique de cellules. Vous ne voulez pas parcourir en boucle une fonction Range, car les références des cellules ne sont pas des numéros (à moins d’y incorporer la fonction Cells).

Le format de cette fonction est Range (Cell # 1, Cell # 2) . Chaque cellule peut être désignée par une lettre.

Voyons quelques exemples.

Ici, la fonction de plage fait référence à la cellule A5:

Feuilles de calcul ("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’avez pas à utiliser d’attribution de cellules de lettres numériques. Vous pouvez en fait utiliser deux fonctions Cells dans une fonction Range pour identifier une plage sur la feuille, comme ceci:

Avec des feuilles de calcul ("Sheet1")
 .Range (.Cells (1, 1), _
 .Cellules (20, 5))
Terminer par

Maintenant que vous savez comment formater les fonctions Cells et Range, voyons comment vous pouvez utiliser ces fonctions de manière créative dans votre code VBA.

Traitement de données avec la fonction de cellules

La fonction Cells est particulièrement utile lorsque vous souhaitez effectuer une formule complexe sur plusieurs plages de cellules. Ces plages peuvent également exister sur plusieurs feuilles.

Prenons un exemple simple. Supposons que vous dirigiez une équipe de vente de 11 personnes et que vous souhaitiez examiner chaque mois leurs performances.

Vous pourriez avoir Feuille1  qui suit leur nombre de ventes, et leur volume de ventes.

Sur Feuille2  C’est à cet endroit que vous suivez leur évaluation au cours des 30 derniers jours par les clients de votre entreprise.

Si vous souhaitez calculer le bonus sur la première feuille à l'aide des valeurs des deux feuilles, vous pouvez procéder de différentes manières. Vous pouvez écrire une formule dans la première cellule qui effectue le calcul en utilisant des données sur les deux feuilles et la faire glisser vers le bas. Ça va marcher.

Une alternative à cela consiste à créer un script VBA que vous voulez soit exécuter à chaque ouverture de la feuille, soit déclenché par un bouton de commande sur la feuille afin que vous puissiez contrôler son calcul. 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 pour 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 Développeur. Pour ce faire, allez à Fichier  >   Les options . Cliquer sur Personnaliser le ruban . Enfin, choisissez Developer dans le volet de gauche, Ajouter  dans le volet de droite et assurez-vous que la case à cocher est sélectionnée.

Maintenant, quand vous cliquez D'accord  Pour revenir à la page principale, vous verrez l’option de menu Développeur.

Vous pouvez utiliser le Insérer  menu pour insérer un bouton de commande, ou cliquez simplement sur Voir le code  pour commencer à coder.

Dans cet exemple, le script sera exécuté à chaque ouverture du classeur. Pour ce faire, il suffit de cliquer sur Voir 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 à quelque chose comme ça.

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, extraire les trois variables nécessaires au calcul.

Rappelez-vous que la fonction Cells a une ligne et une colonne en tant que 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. Le nombre d'identificateurs de colonne sera donc 2 pour le nombre de ventes, 3 pour le volume des ventes et 2 de la feuille 2 pour le score de retour.

Le calcul final utilise les pourcentages suivants pour ajouter jusqu'à 100% du total des points bonus. Il est basé sur un nombre de ventes idéal de 50, un volume de ventes de 50 000 dollars et un score de rétroaction de 10.

• (Chiffre d'affaires / 50) x 0.4
• (Volume des ventes / 50 000) x 0,5
• (Score de commentaires / 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 est sous parfait sur n'importe quel facteur réduit le bonus. Quelque chose de mieux que l'idéal augmente le bonus.

Voyons maintenant comment toute cette logique peut être exploitée dans un script VBA très court et très simple:

Private Sub Workbook_Open ()
Pour x = 2 à 12
Feuilles de calcul ("Sheet1"). Cellules (x, 4) = (Feuilles de calcul ("Sheet1"). Cells (x, 2) .Value / 50) * 0.4 _
 + (Feuilles de calcul ("Sheet1"). Cells (x, 3) .Value / 50000) * 0.5 _
 + (Feuilles de calcul ("Sheet2"). Cellules (x, 2) .Value / 10) * 0.1 _
X suivant
End Sub

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 maximal du bonus. Mieux encore, placez ce montant dans une cellule d'une autre feuille et faites-le référence dans votre code. Cela faciliterait la modification ultérieure de la valeur sans avoir à modifier votre code.

La beauté de la fonction Cells réside dans le fait que vous pouvez créer une logique assez créative pour extraire des données de beaucoup de cellules  à travers de nombreuses feuilles différentes, et effectuer des calculs assez complexes  avec eux.

Vous pouvez effectuer toutes sortes d’actions sur les cellules à l’aide de la fonction Cellules, comme l’effacement des cellules, la modification du format de la police, 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 Range

Pour effectuer une boucle dans plusieurs cellules une à la fois, la fonction Cells est parfaite. Mais si vous souhaitez appliquer simultanément un élément à une plage entière de cellules, la fonction Range est beaucoup plus efficace.

Un cas d'utilisation pourrait être de formater une plage de cellules à l'aide d'un script, si certaines conditions sont remplies.

Par exemple, supposons que si le total des ventes de l’ensemble des employés des ventes dépasse 400 000 USD, vous souhaitez mettre en surbrillance toutes les cellules de la colonne des bonus en vert pour indiquer que l’équipe a gagné un bonus supplémentaire.

Private Sub Workbook_Open ()
Si Feuilles de calcul ("Sheet1"). Cells (13, 3) .Value>
 400000 alors
 ActiveSheet.Range ("D2: D12"). Interior.ColorIndex = 4
Fin si
End Sub

Lorsque cette option est exécutée, 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 Range. Les 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 le Page Microsoft MSDN  pour que l'objet Range affiche toutes les possibilités.

Prenez Excel au niveau suivant

Maintenant que vous comprenez les différences entre les fonctions Cells et Range, il est temps d’amener votre script VBA au niveau supérieur. 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 pouvant accumuler des valeurs sur tous vos ensembles très rapidement.