Les formules Créer une zone dynamique
Créer une zone dynamique
Mercredi, 08 Juillet 2009 19:23 | 
| Les formules |
Le problème
"J'aimerai utiliser la fonction Sommeprod ici mais j'ai un souci, lorsque je veux prendre la totalité de ma colonne, il me mets un #NOMBRE. Comment puis-je remédier à ce problème ?
La solution
Salut,
Je ne savais pas en fait que cela produirait cet effet si l'on prenait l'intégralité des colonnes. Pour remédier à ce problème, je vous conseille de créer des zones nommées dynamiques. C'est pas très compliqué, vous allez voir...
Tout d'abord, il faut savoir comment on créé une zone nommée. Pour cela, selectionnez votre cellule ou votre plage et ensuite il suffit d'aller dans Insertion / Nom / Définir comme ceci:
ou directement si c'est un nom simple comme ceci:
Bon maintenant que vous savez créer une zone nommée, voici la formule magique qui permet d'obtenir une zone nommée dynamique, qui s'agrandit et se rétrécit en fonction des informations que vous entrez dans la zone.... c'est peut être pas très clair, mais un exemple concret va venir contrecarrer vos doutes. Pour cela nous allons utiliser les formules suivantes:
1 |
=decaler(Référence; Ligne; Colonnes; [hauteur],[largeur])
|
combinée à :
1 |
=nbval()
|
Maintenant que nous connaissons les deux fonctions à utiliser, c'est plutôt simple. Nous allons dire à l'ordinateur:
"Je veux que tu comptes le nombre de cellules en partant de telle cellule dans le sens de la colonne (ou dans le sens de la ligne ou encore dans les deux sens)."
Ainsi, on peut avoir plusieurs cas:
- 1e cas: La plage sera variable seulement en ligne (si vous avez compris cela, vous n'avez pas besoin de lire les deux autres...)
- 2e cas: La plage sera variable seulement en colonne
- 3e cas: la colonne et la ligne sont de tailles variables toutes les deux
Pour cela, allez donc dans le menu des zones nomées (voir un peu plus haut), entrez un nom puis votre formule:
1 |
=decaler(La_premiere_cellule_de_votre_colonne)
|
Vous devriez obtenir quelque chose comme ceci par exemple:
Ensuite, il y a les arguments lignes et colonnes. Perso, je préfère utiliser les arguments hauteurs et largeurs. On laisse donc tomber les deux premiers. Ainsi la formule devient:
1 |
=decaler(La_premiere_cellule_de_votre_colonne;;;)
|
Capture d'écran de ce que j'obtiens:
Et c'est là qu'on fait intervenir la formule NBVAL(). En fait le NBVAL() va compter le nombre de cellule non vide de la colonne. Cela va renvoyer un nombre, ce qui va décaler d'autant de cellules la taille de la plage.
1 |
=decaler(Feuil1!$B$2;;;nbval(Feuil1!$B:$B);)
|
Capture d'écran de ce que j'obtiens:
et enfin, on met le nombre de colonne que contient la plage. Si la plage ne contient qu'une colonne alors on met 1, s'il y a deux colonnes, alors on mettra 2 et ainsi de suite. Ici, c'est 1. La formule devient donc:
1 |
=decaler(Feuil1!$B$2;;;nbval(Feuil1!$B:$B);1)
|
Cliquez sur Ajouter une fois votre formule terminée. Capture d'écran de ce que vous êtes sensé obtenir.... j'espère que c'est good sinon, vous n'avez plus qu'à recommencer
:
Imaginons maintenant que ce soit ma ligne qui ai une taille variable, alors dans ce cas, on aurait fait:
1 |
=decaler(Feuil1!$B$2;;;1;nbval(Feuil1!$2:$2))
|
Ici, nous inversons le 1 avec le Nbval(). Cela signifie que la plage aura une ligne de hauteur et un nombre de colonne variables.
Maintenant, si le nombre de lignes et de colonnes sont variables, alors nous mettrons des nbval() partout. Cela correspond en gros à ceci:
1 |
=decaler(Feuil1!$B$2;;;nbval(Feuil1!$B:$B);nbval(Feuil1!$2:$2))
|
Ici, le nombre de colonnes et de lignes de la plage seront dynamiques.
Il est un peut tard là, et je suis un peu fatigué.... j'espère que j'ai été clair. Si vous ne comprenez pas tout, si j'ai oublié des choses ou que ce n'est pas clair sur certains points, dites le moi et je modifierai pour que ce soit plus clair....
Mis à jour (Mardi, 23 Février 2010 12:18)