Aide pour calcul en fonction d'une liste

[r1v3r]

Bonjour,

Je fais appel à vos talents pour des calculs dans mon fichier.

L'explication :
Je souhaiterai que les calculs dans les encadrés rouge, bleu et vert se fassent en fonction du critère que je choisis dans les cellules C15 ET/OU D15. (filtre auto activé dessus)

D'après mes recherches cela devrait passer par une fonction =BD... mais le résultats reste systématiquement 0. J'imagine que soit je formule mal soit cette fonction n'est finalement pas adaptée.

En espérant que la solution existe je vous remercie d'avance.



EDIT (05/08) : j'ai ajouter des explications dans le fichier pour être éventuellement plus clair dans la recherche d'une solution. (du coup le fichier dépassait la limite de taille j'ai donc du le zipper)

 

[Tibo]

Re : Aide pour calcul en fonction d'une liste

Bonjour,

Avec ce que j'ai compris, une piste avec la fonction SOMMEPROD (à la place de NB) :

en F2 :

=SI(SOMMEPROD(($C$17:$C$47=$C$15)*($D$17:$D$47=$D$15))=0;"";SOMMEPROD(($C$17:$C$47=$C$15)*($D$17:$D$47=$D$15)*(U17:U47<=3))/SOMMEPROD(($C$17:$C$47=$C$15)*($D$17:$D$47=$D$15)))

Je te laisse tester puis adapter.

@+

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

Bonjour,

Merci déjà,

par contre cela ne fonctionne pas. Le résultat n'affiche rien pour F2 (normal puisque aucune donnée n'est rentrée pour le mois de janvier), j'ai donc remplacé les champs pour tester sur le mois de Mai (C141:C171) mais le résultat reste identique : rien ne s'affiche.

Est-il normal que le champs (F141:F171 pour le mois de Mai par exemple) n'apparaisse pas dans la formule proposée ?

ps: il est évident que je ne suis pas expert

Merci

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

up !

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

:snif:

Il y a bien une âme charitable pour trouver la solution

 

[Tibo]

Re : Aide pour calcul en fonction d'une liste

Bonjour,

J'ai ressorti et astiqué la boule de cristal pour tenter de comprendre (plutôt deviner) ton souhait :

en F2, je te propose ceci :

=SOMMEPROD((MOIS($B$17:$B$392)=LIGNES($1:1))*($C$17:$C$392<>"")*(U$17:U$392<=3)*(U$17:U$392<>""))/SOMMEPROD((MOIS($B$17:$B$392)=LIGNES($1:1))*($C$17:$C$392<>"")*(F$17:F$392<>""))

à recopier vers le bas et vers la droite

On peut gérer les #DIV/0! en ajoutant un test au début de la formule ou en les masquant avec une MFC.

Je te laisse adapter pour tes autres tableaux.

@+

 

[Monique]

Re : Aide pour calcul en fonction d'une liste

Bonjour vous deux,

Je commence à comprendre
Tu filtres sur les lieux ou sur les types, c'est ça ?
Si c'est ça :

Dans le fichier joint :
tu filtres sur 1 lieu sans autre critère que le lieu et un seul lieu
le lieu en question se retrouve en Y1 (formule matricielle, à valider par ctrl, maj et entrée)
la formule en U2:AH13 tient compte du lieu choisi

Si tu ne filtres pas, Y1 est vide et la formule prend en compte tous les lieux.

=SOMMEPROD(((Lieu=$Y$1)+($Y$1=""))*(U$16:U$392=$T2)*(ESTNUM(U$16:U$392)))/SOMMEPROD(((Lieu=$Y$1)+($Y$1=""))*ESTNUM(F$16:F$392))

Tu peux aussi ne pas filtrer et avoir, en Y1, une liste de validation contenant tous les lieux.

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

Bonsoir,

Je vous remercie tous les deux.

J'ai editer les fichier de départ justement pour clarifier un peu ma volonté, on s'est donc croisé et manqué sur ça.

Cela semble plutot bien fonctionner a priori. je vais tester sous toutes ses formes le fichier proposé. ==> cela ne fonctionne pas complètement en fait
Question @Monique : faut-il garder le tableau en BI2:BV13 ?

Enfin, est-il possible d'appliquer ses formules au 3e tableau ? Celui encadré en vert (AJ2:AQ13 dans le fichier de monique) ==> le calcul dedans n'est pas le même que les autres.

Je vais essayer de comprendre les formulations de vos 2 propositions bien plus compliquées que mon niveau dans excel ne permet d'élaborer.

Encore Merci.

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

ah ! Correction : je filtre "lieu" avec "auteuil" ==> il n'y a pas eu de courses en aout pourtant le tableau affiche 50% de réussite pour ce mois.

Si je filtre "type" avec "P" par exemple, Y1 affiche "saint cloud" pourtant il y plusieurs lieux possibles pour "P"
mais SURTOUT le tableau dit des betises ==> il affiche par exemple en U13 : 100% ce qui n'est pas réellement le cas.

Il doit probablement n'y avoir qu'une petite correction a apporter.

 

[Monique]

Re : Aide pour calcul en fonction d'une liste

Re,

je filtre "lieu" avec "auteuil" ==> il n'y a pas eu de courses en aout pourtant le tableau affiche 50% de réussite pour ce mois.
C'est normal : le tableau de F à S ne tient pas compte du choix du filtre

Si je filtre "type" avec "P" par exemple, Y1 affiche "saint cloud" pourtant il y plusieurs lieux possibles pour "P"
Normal aussi, relis mon message : tu filtres sur 1 lieu sans autre critère que le lieu et un seul lieu
Je n'ai rien fait pour le type
Mais ça risque d'être laborieux

Fichier joint :
le tableau AJ2:AQ13 tient compte du filtre sur le lieu
le tableau AV2:BC13 n'en tient pas compte, il prend en compte tous les lieux de la colonne C, qu'il y ait ou non un filtre.

Tableau de U à AH : la formule est mise au conditionnel.

 

[Monique]

Re : Aide pour calcul en fonction d'une liste

Bonjour,

Par formule, on peut obtenir tous les n° des lignes filtrées.
=TRANSPOSE(PETITE.VALEUR(SI(SOUS.TOTAL(3;DECALER(Un;LIGNE(Lieu)-LIGNE(Un));LIGNE(Lieu));LIGNE(INDIRECT("1:"&SOUS.TOTAL(3;Date)))))
à valider, si on veut voir, en sélectionnant plusieurs cellules.
ça forme une ligne horizontale "virtuelle" de n°, nommée "Visible", que l'on peut utiliser dans les formules.

=SOMMEPROD((LIGNE(Date)=Visible)*ESTNUM(Date)) renvoie le nb de dates visibles

Le temps de recalcul était long.
Formules nommées, ça va mieux mais quand même
Entre 1 et 2 secondes si peu de lignes visibles
Sinon, entre 30 secondes et 1 minute

Dans la 1ère cellule en haut à gauche de chaque tableau, il y a la formule originale, non nommée.

Dernier tableau à droite : rien changé, il fait le calcul sur toutes les lignes, qu'il y ait ou non un filtre.
Tous les autres tableaux : calcul sur les lignes visibles.

Formules en AJ15:AQ16 modifiées (elles prenaient une plage trop longue)

Le fichier joint est enregistré en mode de calcul sur ordre (Barre de menu – Outils – Option – Calcul). Pour lancer le recalcul sans passer par options, outils, etc., appuyer sur F9.

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

Bonjour,

C'est très fort !!! Cela me permet de faire un tri énorme ! Merci beaucoup

Ce temps de recalcul est lié à la puissance du PC seulement ou bien c'est juste le moteur d'excel qui veut ça ?

Je voudrais appliquer les mêmes calculs que le tableau central (bleu) à une autre feuille : quelles sont les étapes à respecter pour que cela fonctionne ?
==> parce que par exemple j'ai copié collé la formule en Y1 de v2 du fichier pour remettre dans le vrai tableau, validé avec ctrl maj entrée, mais ça ne fonctionnait pas donc j'imagine qu'il y a une étape à valider quelque part ou un ordre à respecter, non ?

Faut-il que la première formule en haut à gauche du tableau doit être "non nommée" (qu'est ce que ça veut dire d'ailleurs?) comme dans le fichier v3 ?

Sinon je n'ai pas très bien compris le principe de la formule TRANSPOSE pour le nombre de ligne : comment l'utiliser et pour quel aboutissement

Dans tous les cas, c'est excellent ! très voir trop compliqué pour que je comprenne bien les formules et ce qu'elles font, mais c'est énorme ce que je vais pouvoir en faire

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

Est-il possible de recalculer que sur certaines cellules (AS17:AZ392) ?

 

[Monique]

Re : Aide pour calcul en fonction d'une liste

Bonjour,

Pour Y1 : j'ai fait un copié-collé du fichier V2 au fichier V3, ça fonctionne.
Si tu as coché "ajuster" dans le format de cellule, le résultat n'est peut-être pas visible ?
Si tes cellules étaient fusionnées, ça ne marche pas. On ne peut pas valider une formule par ctrl, maj et entrée dans une cellule fusionnée
Par contre, dans un autre fichier, ça ne peut pas fonctionner, il faut nommer les plages.

Mais ce n'est plus la bonne formule
Le nombre de lieux visibles, sans doublon (qu'il y ait un filtre ou non) :

=NB(1/FREQUENCE(SI((LIGNE(Lieu)=Visible);EQUIV(Lieu;Lieu;0));LIGNE(INDIRECT("1:"&LIGNES(Lieu)))))

Le 1er des lieux visibles, qu'il y en ait 1 ou plusieurs :

=INDEX(ColLieu;MIN(SI(SOUS.TOTAL(3;DECALER(Prim;LIGNE(Lieu)-LIGNE(Prim););SI(NB.SI(I$1:I36;Lieu)=0;LIGNE(Lieu)))))

Associer les deux formules et mettre en Y1 pour avoir le seul lieu visible ou bien rien :

=SI(NB(1/FREQUENCE(SI((LIGNE(Lieu)=Visible);EQUIV(Lieu;Lieu;0));LIGNE(INDIRECT("1:"&LIGNES(Lieu)))))>1;"";INDEX(ColLieu;MIN(SI(SOUS.TOTAL(3;DECALER(Prim;LIGNE(Lieu)-LIGNE(Prim););LIGNE(Lieu)))))

Le temps de recalcul : ce n'est pas Excel, c'est le PC mais je ne peux pas l'expliquer (une histoire de mémoire, de fichiers temporaires, etc.)
On ne peut pas demander le recalcul pour seulement certaines cellules.
Excel recalcule le classeur entier et même tous les fichiers ouverts !
Tu aurais intérêt à enregistrer ton classeur en mettant un filtre ne donnant que peu de lignes (pour ne pas être surpris à l'ouverture).

La 1ère cellule en haut à gauche de chaque tableau n'a pas besoin d'être "non nommée"
Mais s'il y a une modif. à faire, ce sera plus facile de modifier dans cette cellule.

La formule Transpose()
Elle est nommée, elle s'appelle "Visible"
Elle représente une plage horizontale d'autant de cellules qu'il n'y a de lignes visibles dans ton tableau.
Pour SommeProd, il faut que toutes les plages soient de même longueur ou de même largeur (sauf quelques formules, du genre SommeProd(Nb.si))
Ici, "Lieu" contient 245 lignes et "Visible" a entre 1 et 245 valeurs
Avec 1 seule ligne visible, ça fonctionne sans Transpose() sinon ça ne fonctionne pas.

 

[r1v3r]

Re : Aide pour calcul en fonction d'une liste

le problème viens donc de la déclaration des plages : je ne savais pas cela et cela permet tout de suite de mieux comprendre toutes les formules.

Si je peux demander une dernière chose : quelle serait la formule non nommée des résultats obtenus en AJ15:AQ15 avec le système de filtre appli

Merci et bonne nuit