Robbie écrit principalement sur Skyrim, mais met également occasionnellement en lumière les chances des applications Microsoft telles qu’Excel et Outlook.
Dans cet article, nous allons examiner quelque chose de plutôt cool et quelque chose dont j’ai besoin dans mes propres feuilles de calcul Excel depuis un certain temps.
Imaginez si vous voulez, une feuille de calcul où vous enregistrez les factures, les ventes, les visites Web, etc., puis SOMME eux pour vous donner un total cumulé. Chaque fois que vous ajoutez des lignes supplémentaires à votre feuille de calcul, vous devez mettre à jour votre SOMME formule pour tenir compte des éléments supplémentaires.
Eh bien plus maintenant ! Bienvenue dans la plage dynamique utilisant le DÉCALAGE et COUNTA les fonctions. Grâce à cette fonction, chaque fois que des lignes sont ajoutées (ou supprimées) et également chaque fois que des cellules copiées sont insérées, le SOMME la formule sera automatiquement et dynamiquement mise à jour pour refléter cela.
Utilisation de la fonction OFFSET dans une formule dans Excel 2007 et Excel 2010
la DÉCALAGE La fonction vous permet de demander à Excel de vous indiquer le contenu d’une cellule (ou d’une plage de cellules) à un certain nombre de lignes et de colonnes d’une cellule (ou de cellules) de référence.
si tu utilises DÉCALAGE seul, il renverra la valeur de la cellule à laquelle vous l’avez référé. Nous allons commencer par cela à titre d’exemple :
=DECALAGE(B3,1,1,1,1)
Exemple d’utilisation de la fonction OFFSET dans une formule dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
La formule est composée des parties suivantes :
- La cellule à partir de laquelle vous demandez à Excel de démarrer (dans ce cas B3) (connue par Excel sous le nom de Référence)
- La partie suivante de la formule est le nombre de Lignes (qui peut être vers le haut ou vers le bas) de la cellule de référence à la cellule que vous regardez. Nous nous intéressons à la cellule C4, donc cette valeur est 1 car elle se trouve une ligne en dessous de la cellule B3.
- Ensuite, nous devons savoir combien Colonnes du départ à la cellule de référence. C’est encore 1 car c’est une colonne de B3 à C4
- Le quatrième élément de la formule est la taille. Il s’agit du nombre de lignes que vous souhaitez que les données renvoyées soient.
- Enfin, nous avons largeur ou le nombre de colonnes que vous voulez que les données soient.
Noter: Lignes et Colonnes peut être un nombre négatif ou positif. Veuillez vous référer au tableau ci-dessous pour savoir quand utiliser des valeurs positives ou négatives.
Quand utiliser des nombres positifs et négatifs pour les lignes et les colonnes dans les formules OFFSET
Noter: la taille et largeur doivent être tous les deux positifs. S’ils ne sont pas spécifiés, Excel utilisera la hauteur et la largeur de la référence.
Noter: Si vous obtenez un #VALUE, vous avez généralement demandé à Excel d’afficher quelque chose qu’il ne peut pas afficher.
Pour illustrer ceci :
Si j’utilise la formule suivante :
Faites défiler pour continuer
=DECALAGE(B3,1,1,2,1)
Je demande à Excel d’afficher les résultats d’une cellule dans deux cellules, ce qu’il ne peut pas faire, il renvoie une erreur.
Exemple d’erreur #VALUE reçue lors de l’utilisation de la fonction OFFSET dans une formule dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
Noter: Si vous obtenez une erreur #REF, vous êtes sorti du bord de votre feuille de calcul.
Cette formule entraîne une erreur #REF, car il n’y a pas une telle ligne quatre lignes au-dessus de B3.
=DECALAGE(B3,-4,2,1,1)
Exemple d’erreur #REF reçue lors de l’utilisation de la fonction OFFSET dans une formule dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
Utilisation de OFFSET avec COUNTA dans une formule pour créer une plage dynamique dans Excel 2007 et Excel 2010
Maintenant que nous comprenons le DÉCALAGE fonction et comment l’utiliser dans une formule, nous arrivons à la partie vraiment intéressante de cet article; nous arrivons à créer une plage dynamique en l’utilisant parallèlement COUNTA. on peut alors SOMME les valeurs dans la plage dynamique et cette formule recalculera automatiquement si des cellules sont ajoutées, supprimées ou si leur contenu est modifié. Bien sûr, SOMME est juste un exemple que j’ai choisi pour cet article, vous pouvez utiliser MOYEN, MAX ou toute autre fonction que vous jugez appropriée à vos besoins en plus de DÉCALAGE et COUNTA les fonctions.
Voyons d’abord rapidement ce que COUNTA fait pour nous dans cet exemple :
Il compte simplement le nombre de cellules non vides entre la cellule de début et la cellule de fin (dans ce cas il y a 29 cellules non vides entre la cellule C3 et la cellule C32).
=COMPTERA(C3:C32)
Exemple de la fonction NBVAL utilisée dans une formule dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
Maintenant, nous incluons le COUNTA formule ci-dessus dans le cadre de notre DÉCALAGE formule pour créer une plage dynamique. La formule finie est :
=SOMME(DECALAGE(C3,0,0,COMPTE(C3:C32),1))
Nous commencerons par regarder les DÉCALAGE partie de la formule :
DECALAGE(C3,0,0,COMPTE(C3:C32),1)
- Nous commençons à la cellule C3 comme notre Référence
- Le nombre de Lignes nous demandons à Excel de passer de la cellule de départ de C3 à 0
- De même, le nombre de Colonnes est également 0 (j’aime ajouter et supprimer des colonnes à mes données, donc le garder à 0 signifie que la formule ne se cassera jamais)
- Vient maintenant la partie dynamique de toute la formule. pour le la taillenous disons à Excel que la hauteur est le résultat de la formule COUNTA qui est actuellement de 29. Si nous ajoutons ou supprimons des lignes, cela s’ajustera automatiquement !
- Enfin, nous informons Excel de la largeurqui dans ce cas est 1 colonne.
Maintenant que nous comprenons le DÉCALAGE section de la formule, nous ajoutons le SOMME fonction pour compléter la formule globale nous donnant:
=SOMME(DECALAGE(C3,0,0,COMPTE(C3:C32),1))
si nous maintenant Insérer une ligne vide supplémentaire, vous remarquerez que le total des ventes diminue de 18 (qui est la valeur dans la cellule C32 qui est la dernière ligne) de 1 085 dans la figure ci-dessus à 1 067 dans la figure ci-dessous.
L’insertion d’une ligne vide dans une plage dynamique entraînera des formules inexactes dans Excel 2007 et Excel 2010.
http://robbiecwilson.hubpages.com/
J’ajoute maintenant une valeur dans la ligne vide et le total des ventes est maintenant correct et inclut la valeur dans C32.
Avec les données saisies dans la cellule C19, la formule calculant les valeurs dans la plage dynamique dans Excel 2007 et Excel 2010 est désormais correcte.
http://robbiecwilson.hubpages.com/
Noter: L’insertion d’une ligne vide dans votre plage dynamique conduira aux résultats affichés dans votre SOMME formule étant incorrecte.
Noter: Si vous ajoutez des lignes supplémentaires, par exemple en Insertion de cellules copiées Excel augmentera simplement le COUNTA partie de la formule qui la rend dynamique.
A titre d’exemple, j’ai ajouté quatre lignes à ma feuille de calcul et Excel a automatiquement mis à jour ma formule !
=SOMME(DECALAGE(C3,0,0,COMPTE(C3:C36),1))
Plages dynamiques dans Excel
La possibilité d’avoir des plages dynamiques dans vos feuilles de calcul Excel contenant des données telles que les ventes, les factures, les visites Web, etc., qui peuvent ensuite être additionnées ou moyennées, est en effet très utile. Cela signifie que vous n’avez plus à mettre à jour manuellement vos formules chaque fois que vous effectuez des ajouts ou des suppressions dans vos données.
Pour y parvenir :
- Nous avons commencé par utiliser le DÉCALAGE fonction dans la formule. Cette fonction permet de démarrer avec une cellule (ou des cellules) de référence et de demander à Excel d’afficher le contenu d’une autre zone un nombre précis de lignes et de colonnes à partir de la cellule de référence
- Ensuite, nous avons ajouté le COUNTA fonction de fournir la la taille de la zone pour DÉCALAGE. Chaque fois que des cellules sont ajoutées ou supprimées de la plage, le COUNTA section de la formule est mise à jour automatiquement.
- En ajoutant le SOMME fonction à cette formule, nous pouvons maintenant additionner les cellules affichées par Excel via DÉCALAGE.
Lorsque toutes ces fonctions sont ajoutées à une formule, celle-ci additionne désormais dynamiquement une plage de cellules, se mettant à jour automatiquement lorsque des données sont ajoutées ou supprimées de la plage. C’est pour moi la beauté et la puissance d’Excel, le fait que vous pouvez ajouter deux fonctions ensemble et créer quelque chose qui est non seulement très utile, mais aussi plus que la somme de ses parties.
J’espère que vous avez trouvé cet article utile et informatif et qu’en utilisant DÉCALAGE transformera vos feuilles de calcul de la même manière qu’il a transformé la mienne. Merci beaucoup pour votre lecture, n’hésitez pas à laisser vos commentaires ci-dessous.
Et enfin….
Cet article est exact et fidèle au meilleur de la connaissance de l’auteur. Le contenu est uniquement à des fins d’information ou de divertissement et ne remplace pas un conseil personnel ou un conseil professionnel en matière commerciale, financière, juridique ou technique.
© 2013 Robbie C Wilson
Cendré le 18 octobre 2017 :
Merci pour une très belle explication. Pourriez-vous également suggérer comment faire en sorte que la fonction ‘COUNTA’ exclue les valeurs NA()?
Jen le 02 mai 2017 :
Cela ressemble à un fouillis. ne pouvez-vous pas ajouter une feuille de calcul reflétant vos observations… Cela aurait eu plus de sens que cette façon illogique d’entraînements
