Fonction pour afficher des données en fonction de 3 variables !

[cm1864]

Bonjour,
Je suis bloqué sur une formule a élaboré.
J'ai en effet besoin d'afficher des cellules dont le contenu se trouve dans diverses feuilles du classeur. Le contenu a afficher est fonction de trois paramètres (année 1, année 2, tour opérateur).
Le but de la manœuvre est d'analyser l'évolution d'un tour opérateur sur deux années.
Dans le classeur ci-joint vous trouverez mon problème ré-expliqué et les cellules bien mises en évidence.
Je vous remercie pour votre aide.
Amicalement,
CM1864

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

re,

Un NB.SI ne permet de gérer qu'un seul critère.

Dès lors qu'il y a plus d'un critère, la fonction SOMMEPROD permet généralement de résoudre le problème.

Pour le SOMMEPROD de G12, je vais voir. Cependant, depuis le début de ton fil, ton fichier a dû bien changer. Il serait peut-être bon de joindre la dernière version pour qu'on puisse discuter sur les mêmes bases.

Quant à Excel, c'est une passion. Reste à savoir si elle est "simple" ...

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Je te joins mon fichier avec la feuille SYNTHESE TO remplie et complète !!
Maintenant je bosse sur SYNTHESE pour voir l'évolution simplement par année ...

J'ai bien compris pour SOMMEPROD, mais quand Excel me donne le résultat il n'est pas juste car je met l'argument "individuel" après le premier indirect, mais rien derrière le deuxième ?!

C'est une chouette passion et très utile ! Je trouve ça très gentil de ta part d'aider sur les forums !

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

RE?

En G12, onglet Synthese, essaye avec :

=NB.SI(INDIRECT("'SUIVI DOX "&$D$3&"'!$K$10:$K$1990");"Individuel")

J'utilise NB.SI, parce qu'il y a un critère : "Individuel"


en G16 : Pas de critère (il faut simplement compter le nombre de lignes), la fonction NBVAL doit suffire :

=NBVAL(INDIRECT("'SUIVI DOX "&$D$3&"'!$H$10:$H$1990"))

Je te laisse adapter pour les autres cellules.

N'hésite pas à revenir si souci.

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

NB.SI fonctionne paraitement.

NBVAL (j'ai presque compris la logique maintenant) ne fonctionne pas, le résultat est incohérent, alors que les noms de colonnes sont les bons ??!

Peux tu me donner un dernier coup de pouce ? Après je te laisse tranquille car je serais dans le train !
Merci,
cm1864

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

re,

Sur quelle cellule la fonction NBVAL ne retourne-t-elle pas le résultat attendu ?

Et quel est ce résultat attendu ?

En connaissant la réponse, il sera plus facile de comprendre et d'adapter.

A te (re)lire

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonjour,

Le tableau a dû être modifié, et les fonctions ne "fonctionnent" plus ...
Voici ma demande :

Dans la feuille SUIVI ANNUEL :

1 / Les en-têtes de Colonne doivent conserver les mois "Janvier, Février, etc." mais l'année doit pouvoir évoluer en fonction de la cellule D4. Si D4 = 2013, alors G:H9 doit contenir le texte suivant : "Janvier 2013".

2 / Dans les cellules G10:G50, l'idée est de calculer en fonction des variables en D4 et D5. Si D4 = 2011 et D5 = "", alors G11 doit être égal au nombre total de dossier réalisé sur la période 2011 (les valeurs étant calculées depuis la feuille SUIVI DOX). Idem, pour G12 qui doit calculer le nombre total de dossiers "individuels" réalisé en 2011.
Mais si D5 = "Club Med", alors le calcul doit tenir compte de cette nouvelle variable en calculant le nombre de dossiers "Club Med" en "2011" , etc.

Dans la feuille EVOLUTION :

1 / Dans les cellules G:H11 à G:H50, les calculs doivent tenir compte des variables D4, D5, D6. C'est-à-dire que la recherche donnera soit le résultat pour D4 et D6 soit uniquement D4 si D6 est vide.
Exmple : si D4 = 2012 et D6 = "", la cellule G11 affiche le nombre total de dossiers réalisés en 2012. Mais si D4 = 2012 et D6 = Club Med, alors la cellule G11 affiche le nombre total de dossiers "Club Med" réalisés en 2012.

Je vous laisse mon tableau en pièce jointe.
J'espère votre aide rapidement car je repars à l'étranger dans 4 heures !

Un ENORME MERCI pour votre très précieuse aide !

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonsoir,

Le fait de ne plus avoir qu'un seul onglet avec toutes les données simplifie le problème. Plus besoin de la fonction INDIRECT pour gérer les différents onglets.

Par contre, il faut gérer les données mois par mois selon l'année.

Je suis donc passé par l'utilisation de la fonction SOMMEPROD.

Je te laisse découvrir dans le fichier joint où j'ai commencé à compléter les cellules de l'onglet "Suivi annuel"

Je te laisse adapter pour l'autre onglet.

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Salut Tibo,

De retour au bureau en France, je découvre ta solution au problème ...
Pour l'affichage G9 à AC9 (les mois en texte avec changement automatique de l'année) c'est parfait même si je ne comprends pas tout dans la formule.

En revanche, pour le calcul nombre de dossiers par exemple, ca ne prend en compte que D4 mais pas D5.
Peux tu voir si on peut faire évoluer les formules pour qu'elles intègrent D5.

En fait si D4 = 2011 et D5 = vide, alors G11 compte tous les dossiers en 2011.
Mais si D4 = 2011 et D5 = Club Med (par exemple), alors G11 compte tous les dossiers Club Med uniquement réalisés en 2011.
C'est pour ca que je pensait qu'il fallait tout de même utilisé INDIRECT (je me trompe certainement mais bon).

Peux tu voir ça stp ?
Merci beaucoup !
Amicalement,
cm1864


PS. si tu as une seconde de plus pour m'expliquer pourquoi tu mets A:A dans cette formule, je suis preneur !!

DATE($D$4;(COLONNES($A:A)+1)/2;1)

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonsoir,

Pour tenir compte du critère D5, qu'il soit complété ou non, je te propose une solution avec une formule matricielle :

en G11 :

=SOMME((TEXTE('SUIVI DOX'!$B$10:$B$100;"mmaa")=TEXTE('SUIVI ANNUEL'!G$9;"mmaa"))*(SI($D$5="";1;'SUIVI DOX'!$C$10:$C$100='SUIVI ANNUEL'!$D$5)))

Formule matricielle à valider par CTRL + MAJ + ENTREE

à recopier en I11, K11, ...

En G12 :

=SOMME((TEXTE('SUIVI DOX'!$B$10:$B$100;"mmaa")=TEXTE('SUIVI ANNUEL'!G$9;"mmaa"))*(SI($D$5="";1;'SUIVI DOX'!$C$10:$C$100='SUIVI ANNUEL'!$D$5))*('SUIVI DOX'!$K$10:$K$100="Individuel"))

Formule également matricielle donc à valider par CTRL + MAJ + ENTREE

Pour ce qui est de COLONNES($A:A), cette formule agit en fait comme un compteur lorsqu'on la recopie vers la droite.

COLONNES($A:A) donne comme résultat : 1

recopié une fois vers la droite, ça donne : COLONNES($A:B) qui donne comme résultat : 2

Compte tenu du fait que les mois sont renseignés toutes les deux colonnes, j'ai ajouté 1 et divisé par 2. On obtient ainsi les valeurs 1 à 12, paramètre du mois dans la fonction DATE.

Je joins le fichier avec quelques cellules complétées avec les formules proposées (cellules en jaune).

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonjour Tibo,

I picked it up ! C'est exactement ce que je recherchais ! Je te remercie énormément pour ton aide !
Et la cerise sur le gâteau ... je comprends bien la formule. Pour le compteur A:A, j'ai un peu de mal encore mais bon je vais étudier tout ça un peu plus tard car je dois encore beaucoup travailler sur ce projet car je souhaite qu'il soit opérationnel d'ici mi-septembre.

Merci beaucoup, merci au forum. Le post est à mon sens résolu.
Excellente journée à vous et à bientôt (probablement ...).

Amicalement,

cm1864

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Salut Tibo,

J'ai cloturé la discussion un peu vite, avec la modification du nombre d'onglet, les autres formules sont incorrectes !! J'ai essayé d'adapter un NB.SI(INDIRECT ...) mais sans succès !
Alors voici le tableau bien complété ci-joint (j'ai découvert l'utilisation de SIERREUR à ce propos).

Voici, pour l'onglet EVOLUTION (en rouge) je souhaite dans les cellules en violet pouvoir calculer le nombre de dossier en fonction du contenu des cellules (liste déroulante) vertes.

Donc :

Si D4 = 2011, D5 = "" alors G11 calcul le nombre total de dossiers réalisés en 2011.
Si D4 = 2011, D5 = "Club Med" alors G11 calcul le nombre total de dossiers Club Med réalisés en 2011.

Enfin pour la colonne "CUMUL", je souhaite que si D5 = "" alors S11 = 0 mais si D5 = Club Med alors S11 calcul le nombre total de dossiers Club Med quelque soit le mois ou l'année.

Après cela, je vais essayer de ne plus t’embêter ...
Merci encore pour ton aide.

Amicalement,
cm1864

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonsoir,

Pour l'onglet Evolution, première préconisation : supprimer les fusions de cellules (car pas possible d'utiliser des formules matricielles dans des cellules fusionnées). Si nécessaire, tu élargis la colonne et tu supprimes la colonne devenue inutile. Les fusions de cellules sont parfois nécessaires, mais si ça n'est pas le cas, il est fortement conseillé de s'en passer.

en G11 (après défusion), je te propose une formule matricielle :

=SOMME((ANNEE('SUIVI DOX'!$B$10:$B$2000)=G$9)*(SI($D$6="";1;'SUIVI DOX'!$C$10:$C$2000=$D$6)))

Formule matricielle à valider par CTRL + MAJ + ENTREE

Je te laisse adapter pour les autres cellules.

@+

P.S. : saches que tu ne nous embêtes pas du tout. Il est toujours intéressant d'offrir des propositions de solutions à quelqu'un, surtout lorsqu'il cherche en plus à les comprendre.

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonsoir Tibo,

Merci pour ta réponse, la solution fonctionne à merveille et j'ai su adapter à toutes les autres cellules.
Néanmoins, je bloque sur la colonne CUMUL car je ne parviens pas à trouver formule qui fonctionne.

J'ai essayé de retirer ANNEE et =G$9 (=SOMME((ANNEE('SUIVI DOX'!$B$10:$B$2000)=G$9)*(SI($D$6="";1;'SUIVI DOX'!$C$10:$C$2000=$D$6)))), d'utiliser un NB.SI et NBVAL, SOMME.SI, etc ... sans succès !

En fait mon problème sur Excel vient du fait que je sais pas bien utiliser l'imbrication des formules et que certaines se ressemblent et je ne sais pas lesquelles choisir ... mais je progresse bien, je te rassure !

Merci de m'apporter un dernier coup de main sur cette colonne.
Après il me faudra apprendre l'utilisation de INDIRECT et INDEX avec EQUIV pour un onglet à venir ! (en cours de conception).

Excellente nuit à toi, et à demain.
Bien amicalement,

cm1864

 

[Tibo]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonjour,

A quoi correspond exactement ce cumul ?

S'il s'agit de la somme des deux années, la fonction SOMME devrait suffire.

Si souci, reviens avec la dernière version de ton fichier pour qu'on soit sur la même base, fichier sur lequel tu nous indiquerais manuellement le résultat attendu (avec une explication sur le pourquoi du comment).

@+

 

[cm1864]

Re : Fonction pour afficher des données en fonction de 3 variables !

Bonjour,

Je souhaite en effet faire la somme totale (peu importe l'année).
Je ne peux pas faire la somme des deux colonnes précédentes (car il manquerait des années).

Concrètement, si la cellule D6 est vide, alors O11 calcule le nombre total de dossiers réalisés (toutes années confondues).

Mais si D6 = Club Med, alors O11 calcule le nombre total de dossiers Club Med réalisés (toutes années confondues).

Le but est de pouvoir suivre les chiffres globaux et chiffres globaux par tour opérateur, sans tenir compte de l'année, car certains évènements commerciaux (animation marché, réunions, mais aussi cadeaux ! Exemple, dès 200 K€ réalisés chez un certain TO, ils offrent à mes collègues une semaine de vacances) doivent être déclenchés à certains résultats.

J'avais pensé à la formule mais ca ne marche pas ......

=SOMME('SUIVI DOX'!$B$10:$B$2000)*(SI($D$6="";1;'SUIVI DOX'!$C$10:$C$2000=$D$6)))

Le fichier est en pièce jointe.
Merci, à tout à l'heure.

cm1864