[RESOLU] : Sommeprod + indirect

[Akortys]

Bonjour,

J'ai un petit souci avec ma formule de calcul :
=SOMMEPROD((INDIRECT("Feuil2!L1:L"&NBVAL(Feuil2!A:A))=Feuil1!C17)*(1/NB.SI(INDIRECT("L1:L"&NBVAL(Feuil2!L:L));INDIRECT("B1:B"& NBVAL(Feuil2!L:L)))))

A partir d'un critère (cellule C17) en feuil1, je souhaite remonter les occurrences distinctes de la colonne L (qui est variable en taille) de la feuil2.
Mais avec cette formule j'ai un #DIV/0"

Une lumière pour les gens éclairés.

Merci d'avance

 

[chris]

Re : Sommeprod + indirect

Désolé : posté en double

 

[chris]

Re : Sommeprod + indirect

Bonjour

Une piste : SOMMEPROD N'accepte pas de colonnes entières en version 2007

Pense aux tableaux...

 

[Akortys]

Re : Sommeprod + indirect

Bonjour Chris,

Même sans mettre de colonne entiere, j'ai la même erreur :
=SOMMEPROD((INDIRECT("CountFour!L1:L500"&NBVAL(CountFour!A1:A500))=Feuil2!C17)*(1/NB.SI(INDIRECT("L1:L500"&NBVAL(CountFour!L1:L500));INDIRECT("B1:B500"& NBVAL(CountFour!L1:L500)))))

 

[chris]

Re : Sommeprod + indirect

Re

Sans fichier aucun moyen moyen de t'aider concrètement mais déjà

INDIRECT("B1:B500"& NBVAL(CountFour!L1:L500)

donne un truc du genre "B1:B50025"

si le NBVAL renvoie 25...

 

[Akortys]

Re : Sommeprod + indirect

Chris,

Je te joins un fichier qui sera certainement plus parlant que ma littérature qui n'en est pas.

L'objectif est dans nbre de lieu différent pour
- Greg je dois avoir 3
- Stephane je dois avoir 5
....

L'info doit absolument etre dans la feuil2 et récupérer dans la Feuil1 avec le critère du prenom dans la Feuil2.
Et le tableau de Feuil1 est variable.
Merci d'avance

 

[Dugenou]

Re : Sommeprod + indirect

Bonjour,

Quelques corrections :

=SOMMEPROD((INDIRECT("Feuil1!a2:a"&NBVAL(Feuil1!A1:A500))=Feuil2!C10)*(1/NB.SI(INDIRECT("Feuil1!B2:B"&NBVAL(Feuil1!$B$1:B500));INDIRECT("Feuil1!$B$2:B"&NBVAL(Feuil1!B1:B500)))))

=SOMMEPROD((INDIRECT("Feuil1!a2:a"&NBVAL(Feuil1!A1:A500))=Feuil2!C10)*(1/NB.SI(INDIRECT("Feuil1!$B$2:B"&NBVAL(Feuil1!B1:B500));INDIRECT("Feuil1!$B$2:B"&NBVAL(Feuil1!B1:B500)))))

donne un résultat quand il n'y a pas de cellule vide dans la colonne des lieux
Cependant je pense que cela ne donne pas le résultat voulu car le 1/nb.si donne une fréquence tous utilisateurs confondus et pas seulement pour l'utilisateur sélectionné

 

[Dugenou]

Re : Sommeprod + indirect

Re,
Une proposition de solution avec une colonne supplémentaire dans le tableau de données

 

[Akortys]

Re : Sommeprod + indirect

Bonjour Dugenou,

En effet, la correction permet le calcul d'une valeur maisje devrais avoir un nbre entier. Avec la correction j'ai 3.388888, chose impossible normalement.
Merci

 

[Akortys]

Re : Sommeprod + indirect

Dugenou,

Alors c'est un petite avancée, mais en tout cas, tu as bien compris la demande.
Je dis petite parce qu'il faut ajouter une colonne a mon tableau et ça c'est pas faisable car les données sont issues d'un export, et deuxièmement le calcul compte les valeurs vides comme une valeur.
Merci

 

[Dugenou]

Re : Sommeprod + indirect

Akortys,
Plusieurs remarques :

• même si c'est un export, on peut toujours ajouter une colonne à droite ou copier coller l'export dans une feuille préparée d'avance avec cette colonne.
• Ta feuille 1 est déclarée en tableau : si tu as Excel 2010 ce serait plus simple d'enregistrer au format xlsx et d'utiliser les noms de plages plutôt que les indirect qui sont un peu lourds à gérer.
• on doit pouvoir faire le calcul de la fréquence par nom dans une fonction personnalisée : j'essaye et reviens vers toi

 

[Akortys]

Re : Sommeprod + indirect

Bonsoir,

Pour ma part, j'arrive a compter le nbre d'occurences d'une colonne mais pas en fonction d'un critére précis d'une autre feuille.
Ca doit être le bon chemin, mais a mon avis il est encore long.

 

[Dugenou]

Re : Sommeprod + indirect

Bonjour,
J'ai fait de nombreux essais mais pas moyen d'y parvenir sans colonne supplémentaire. le probleme est de compter des occurrences sur deux critères 1/NB.SI.ENS() ne donne rien. Je ne possède pas asez bien la fonction Frequence pour y arriver, mais si c'est possible ce doit être avec cette méthode. Sinon, on peut créer une matrice en dehors de la feuille de données et faire les calculs sur cette dernière : c'est un peu lourd et pas élégant mais on y parvient, voir pj. Si ça te convient, on pourra travailler à rendre variable la zone de données.

Cordialement

 

[Akortys]

Re : Sommeprod + indirect

Bonjour Dugenou,

Quittes à ajouter une colonne, je préfère partir sur ta première solution, c'est à dire ajouter une colonne à la table de donnée effectuant le ratio du nombre d’apparition de la valeur.
Et faire le calcul dans la première feuille.

Par contre deux inconvénients pour ce choix c'est le fait qu'une valeur vide soit comptée comme une valeur et que le calcul du sommeprod se fasse sur une plage de données fixe et non variable.
Si tu as une idée. Je cherche de mon coté mais n'étant pas trés doué en excel, c'est long pour obtenir un résultat.

Merci pour les efforts en tout cas.

Cordialement

 

[Akortys]

Re : Sommeprod + indirect

Pour la plage de données à dimension variable j'ai modifié la formule de calcul pour y mettre :
=SOMMEPROD((INDIRECT("Feuil1!a2:a"&NBVAL(Feuil1!$A:$A))=Feuil2!C10)*(INDIRECT("Feuil1!f2:f"&NBVAL(Feuil1!$B:$B))))

au lieu de
=SOMMEPROD((INDIRECT("Feuil1!a2:a"&NBVAL(Feuil1!A1:A500))=Feuil2!C10)*(INDIRECT("Feuil1!f2:f"&NBVAL(Feuil1!B1:B500))))

cela a l'air de fonctionner. Il me reste à traiter la non prise en compte des valeurs vides. Mais là, je me heurte à des erreurs de calculs à chaque fois

J'ai modifié la formule en:
=SI(ESTVIDE(($A$2:$A$31="");"";1/SOMME(SI(($A$2:$A$31=A19)*($B$2:$B$31=B19);1;0))))

Mais sans effet.

Cordialement