Home-theater-designers
Excel est puissant. Si vous l'utilisez beaucoup, vous connaissez probablement déjà de nombreuses astuces utilisant des formules ou la mise en forme automatique, mais en utilisant Cellules et Varier fonctions dans VBA, vous pouvez augmenter vos analyses Excel à un tout nouveau 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 déroutant. Voici comment vous pouvez les utiliser d'une manière que vous n'auriez probablement jamais imaginée.
La fonction Cellules
Les fonctions Cells et Range vous permettent de dire votre script VBA exactement où sur votre feuille de calcul vous voulez obtenir, ou placez les données. La principale différence entre les deux cellules réside dans leur référence.
Cellules font généralement référence à une seule cellule à la fois, tandis que Varier 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 Cells ne fait pas référence à une seule cellule :
Worksheets('Sheet1').CellsCela fait référence à la troisième cellule à partir de la gauche, de la rangée du haut. Cellule C1 :
Worksheets('Sheet1').Cells(3)Le code suivant fait référence à la cellule D15 :
Worksheets('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é à pouvoir référencer une cellule en utilisant un numéro pour la colonne et la cellule, en particulier avec des scripts qui peuvent boucle à travers un grand nombre de cellules (et effectuer des calculs dessus) très rapidement. Nous y reviendrons plus en détail ci-dessous.
La fonction de portée
À 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 n'allez pas vouloir parcourir une fonction Range, car les références pour les cellules ne sont pas des nombres (à moins que vous n'y incorporiez la fonction Cells).
Le format de cette fonction est Plage (Cellule #1,Cellule #2) . Chaque cellule peut être désignée par une lettre-chiffre.
Regardons quelques exemples.
comment réduire la taille du fichier jpeg ?
Ici, la fonction de plage fait référence à la cellule A5 :
Worksheets('Sheet1').Range('A5')Ici, la fonction de plage référence toutes les cellules entre A1 et E20 :
Worksheets('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 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'). 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 Cells et Range, voyons comment vous pouvez utiliser ces fonctions de manière créative dans votre code VBA.
Fonction de traitement des données avec des cellules
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. Supposons que vous gérez une équipe de vente de 11 personnes et que vous souhaitiez chaque mois examiner leurs performances.
Vous pourriez avoir Feuille1 qui suit le nombre de leurs ventes et leur volume de ventes.
Au 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 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-là ?
La fonction cellulaire en action
Si vous n'avez jamais écrit de VBA dans Excel auparavant, vous devrez activer l'élément de menu Développeur. Pour ce faire, rendez-vous sur Déposer > Options . Cliquer 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 d'accord et revenez à la feuille 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 Afficher le code pour commencer à coder.
Dans cet exemple, nous exécuterons le script à chaque ouverture du classeur. Pour cela, il suffit de cliquer Afficher le code dans le menu développeur et collez la nouvelle fonction suivante dans la fenêtre de code.
Private Sub Workbook_Open()
End SubVotre 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 des lignes et des colonnes 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 la colonne sera 2 pour le nombre de ventes, 3 pour le volume des ventes et 2 pour la feuille 2 pour le score de rétroaction.
Le calcul final utilise les pourcentages suivants pour ajouter jusqu'à 100 pour cent du score total du bonus. Il est basé sur un nombre idéal de ventes de 50, un volume de ventes de 50 000 $ et un score de retour de 10.
- (Nombre de ventes/50) x 0,4
- (Volume des ventes/50.000) x 0,5
- (Note de rétroaction/10) x 0,1
Cette approche simple offre aux commerciaux un bonus pondéré. Pour un compte de 50, un volume de 50 000 $ et un score de 10 --- ils obtiennent l'intégralité 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 que l'idéal augmente le bonus.
Voyons maintenant comment toute cette logique peut être extraite 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
Voici à quoi ressemblera la sortie de ce script.
que pouvez-vous faire avec un framboise pi
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 d'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 beaucoup de cellules sur de nombreuses feuilles différentes et effectuer certaines calculs assez complexes avec eux.
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 tout ce que vous pouvez faire plus loin, consultez le Page Microsoft MSDN pour l'objet Cellules.
Formatage des cellules avec la fonction de plage
Pour parcourir plusieurs cellules une par une, la fonction Cells est parfaite. Mais si vous souhaitez appliquer quelque chose à toute une plage de cellules à la fois, la fonction Range 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, disons que si le total de tous les volumes de ventes de tous les employés de vente dépasse 400 000 $ au total, vous souhaitez mettre en surbrillance toutes les cellules de la colonne des bonus en vert pour signifier que l'équipe a gagné un bonus d'équipe supplémentaire.
Voyons comment vous pouvez le faire avec une instruction SI .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubLorsque cela fonctionne, 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. 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 'Find'
- Beaucoup plus
Assurez-vous de lire le Page Microsoft MSDN pour l'objet Range pour voir toutes les possibilités.
Faites passer Excel au niveau supérieur
Maintenant que vous comprenez les différences entre les fonctions Cells et Range, 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'ajout 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 un fantastique guide d'introduction à Excel VBA pour toi aussi.
Partager Partager Tweeter E-mail Canon vs Nikon : quelle marque d'appareil photo est la meilleure ?Canon et Nikon sont les deux plus grands noms de l'industrie de l'appareil photo. Mais quelle marque propose la meilleure gamme d'appareils photo et d'objectifs ?
Lire la suite Rubriques connexes- Productivité
- La programmation
- Programmation Visual Basic
- Microsoft Excel
Ryan est titulaire d'un baccalauréat en génie électrique. Il a travaillé 13 ans dans l'ingénierie d'automatisation, 5 ans dans l'informatique, et est maintenant ingénieur d'applications. Ancien rédacteur en chef de MakeUseOf, il a pris la parole lors de conférences nationales sur la visualisation des données et a été présenté à la télévision et à la radio nationales.
Plus de Ryan DubeAbonnez-vous à notre newsletter
Rejoignez notre newsletter pour des conseils techniques, des critiques, des ebooks gratuits et des offres exclusives !
Cliquez ici pour vous abonner