nb si ensemble et plusieurs infos dans la même cellule

didi25

XLDnaute Nouveau
Bonjour,

J'ai un tableau excel, fait par mes précédentes collègues, recensant les demandes reçues en 2017.

On me demande de préparer des stats grâce à ce tableau, qui fait plus de 2000 lignes.

J'ai réussi à sortir quelques chiffres, mais je bloque complètement concernant ce point précis : lorsque j'utilise la formule nb.si.ens avec des **, excel comptabilise 3 fois la même ligne.

Auriez-vous une solution ?

Comme je n'arrive pas à mieux expliquer, je vous joint un bout de fichier, ainsi que le tableau de stats que je suis censée remplir...et la petite formule qui ne fonctionne, mais pas comme je le souhaite...

Pour moi, à par créer une colonne pour chaque mois, ou une ligne pour chaque nouvelle demande, je ne vois pas comment y arriver.

Dans l'attente de votre/vos réponses, et espérant (encore une fois!) que vous saurez m'apporter votre aide...

Bien cordialement,


didi25
 

Pièces jointes

  • Classeur1 ESSAI.xlsx
    10.7 KB · Affichages: 35

didi25

XLDnaute Nouveau
Bonjour,

Il n'y a pas forcement 3 mois différents dans la cellule. Il peut n'y en avoir qu'un (dans ce cas, je sais quelle formule appliquée), tout comme il peut y en avoir 8 ou 12.

Le résultat que je souhaite trouver, c'est :
JUILLET : 0 commission, 1 urgence.
AOUT : 1 commission, 0 urgence.
SEPTEMBRE : 1 commission, 0 urgence.

ci-joint le tableau, avec les résultats souhaités en jaune.

Je vais finir par remplir le tableau "à la main", mais plus de 2000 lignes... :(

Si vous ne trouvez pas comment faire avec le tableau actuel, je veux bien des idées, pour en faire un nouveau en 2018, qui sera plus efficace pour les stats !!

Merci d'avance pour votre aide.

Didi25
 

Pièces jointes

  • Classeur1 ESSAI.xlsx
    10.8 KB · Affichages: 28

didi25

XLDnaute Nouveau
Merci...

Depuis que je suis arrivée dans mon nouveau service, je ne cesse de faire appel à votre communauté...

Autant je sais faire des tableaux et des stats à partir de tableaux créés par mes soins, et donc mis en forme pour me simplifier la vie, autant ici, avec les tableaux existants, je galère !!!
 

CISCO

XLDnaute Barbatruc
Bonjour

Cf. une solution en pièce jointe.
Dans le fichier Classeur1 ESSAI, j'ai utilisé une colonne R intermédiaire contenant une formule matricielle devant donc être validée avec les 3 touches Ctrl+maj+entrer. Cette formule donne la position du mots JUILLET, AOUT... dans la cellule B2.
La formule de la colonne S peut être tirée vers le bas et vers la droite.
Dans le fichier Classeur1 ESSAIbis, j'ai mis quelques noms pour simplifier l'écriture de la formule de la colonne R.
Dans le fichier Classeur1 ESSAIter, j'ai supprimé la colonne intermédiaire en faisant "glisser" la formule correspondante dans les autres formules à droite. Il faut donc dans ce cas valider aussi en matriciel les formules des colonnes Commission et URGENCE.

Dans les trois cas, c'est la même méthode, mais avec une présentation un peu différente.

Bien sûr, il faut que les noms des mois et les mots commission et URGENCE soient écrits de la même manière partout. Un accent malheureux, oublié ou en trop, et les résultats seront partiellement faux.

A tester davantage, bien sûr, par précaution.

Ceci dit, je ne vois pas trop comment tu vas utiliser cela facilement pour 2000 lignes. Cela serait plus pratique si les mois étaient présentés en colonne...

@ plus
 

Pièces jointes

  • Classeur1 ESSAI.xlsx
    11.9 KB · Affichages: 27
  • Classeur1 ESSAIbis.xlsx
    11.9 KB · Affichages: 30
  • Classeur1 ESSAIter.xlsx
    11.6 KB · Affichages: 35

CISCO

XLDnaute Barbatruc
Bonjour

Cela serait plus pratique ainsi pour traiter 2000 lignes. Il y a deux types de formules, l'une en rouge, l'autre en bleu foncé.

@ plus
 

Pièces jointes

  • Classeur1 ESSAI.xlsx
    13.6 KB · Affichages: 24
Dernière édition:

gosselien

XLDnaute Barbatruc
Bonjour,
@didi25
présentation de tableau qui ne rime à rien amha...
pourquoi 3 mois dans une seule cellule ?
Excel a au minimum 65536 lignes , il ne faut pas hésiter à mettre une donnée par colonne/ligne nécessaire !
Je dis ça parce que les excellentes formules de mes petits camarades risque d'être difficiles à reproduire quand le fichier devra être modifié ...
Ce n'est qu'un avis :)
P.
 

CISCO

XLDnaute Barbatruc
Bonsoir

A tout hasard, une petite explication. La méthode, sur la ligne 3, consiste a repérer la position du mois en cours, JUILLET par ex, dans la cellule B3, à l'aide des retours à la ligne, ce qui donne un nombre x (1 pour JUILLET). Ensuite, on lit le xème mot de la cellule E3 et on le compare à URGENCE ou à Commission. Si c'est la même chose, on écrit 1, sinon, rien.

En détail cela donne

CAR(10) représente un retour à la ligne

Dans AH3
CHERCHE(CAR(10)&AH$2;CAR(10)&$B3) renvoie la position de CAR(10)JUILLET dans CAR(10)JUILLETCAR(10)AOUTCAR(10)SEPTEMBRE, donc 1.

PETITE.VALEUR(....) renvoie la position des CAR(10) dans CAR(10)&B3 = CAR(10)JUILLETCAR(10)AOUTCAR(10)SEPTEMBRE, ce qui donne 1, 9 et 14 (Ici, PETITE.VALEUR renvoie plusieurs valeurs, entre autre, parce que la formule est matricielle).

EQUIV(VRAI;1={1;9;14};0) donne EQUIV(VRAI;{VRAI;FAUX;FAUX};0) qui renvoie 1.

Autrement dit, JUILLET est le premier mot de la cellule B3.

SUBSTITUE(CAR(10)&$E3;CAR(10);"µ";1) renvoie µURGENCECAR(10)CommissionCAR(10)Commission

CHERCHE("µ";µURGENCECAR(10)CommissionCAR(10)Commission) renvoie 1

STXT(CAR(10)&$E3;1+1;NBCAR($AB$1)) donne URGENCE (7 caractères de l'expression µURGENCECAR(10)CommissionCAR(10)Commission à partir du 2ème (=1+1))

SI(STXT(CAR(10)&$E3;1+1;NBCAR($AB$1)) = URGENCE;1;"") = SI(VRAI;1;"") renvoie donc 1.

Cela parait bien compliqué, et ne semble pas très justifié pour le premier mot de la cellule B3.

Si on regarde dans W3, on va peut être mieux voir comment cela fonctionne.

CHERCHE(CAR(10)&W$2;CAR(10)&$B3) renvoie la position de CAR(10)AOUT dans CAR(10)JUILLETCAR(10)AOUTCAR(10)SEPTEMBRE, donc 9.

PETITE.VALEUR(....) renvoie la position des CAR(10) dans CAR(10)&B3 = CAR(10)JUILLETCAR(10)AOUTCAR(10)SEPTEMBRE, ce qui donne 1, 9 et 14 (cela n'a pas changé par rapport à l'exemple précédent).

EQUIV(VRAI;9={1;9;14};0) renvoie 2.

Autrement dit, AOUT est le second mot de la cellule B3.

SUBSTITUE(CAR(10)&$E3;CAR(10);"µ";2) renvoie CAR(10)URGENCEµCommissionCAR(10)Commission (On a remplacé le second CAR(10) par un µ)

CHERCHE("µ";CAR(10)URGENCEµCommissionCAR(10)Commission) renvoie 9 (C'est une coïncidence que cela donne le même nombre 9 que si dessus. Cela vient du fait que JUILLET et URGENCE comportent tous les deux 7 caractères)

STXT(CAR(10)&$E3;9+1;NBCAR($P$1)) donne Commission (10 caractères de l'expression CAR(10)URGENCEµCommissionCAR(10)Commission à partir du 10 ème)

SI(STXT(CAR(10)&$E3;9+1;NBCAR($P$1)) = Commission;1;"") = SI(VRAI;1;"") renvoie donc 1.


Rem : Cela ne fonctionne correctement que si chaque mois n'est écrit qu'une seule fois au max dans la cellule de la colonne B, dans B3 par ex. Si on a JUILLETCAR(10)JUILLETCAR(10)AOUT, cela ne donnera pas le bon résultat car le CHERCHE(CAR(10)&$AH$2 ne renverra que la position du premier CAR(10)JUILLET rencontré, pas celle du second.

@ plus
 
Dernière édition:

gosselien

XLDnaute Barbatruc
Hello tous,

Autant je sais faire des tableaux et des stats à partir de tableaux créés par mes soins, et donc mis en forme pour me simplifier la vie, autant ici, avec les tableaux existants, je galère !!!

Raison de plus pour changer ça non ?

Je suis étonné que personne n'abonde dans mon sens pour dire à @didi25 que son tableau est mal construit au départ et pourrait l'être de manière plus "ergonomique".
N'est ce pas un peu tiré par les cheveux que d'obliger des gens (très compétents au demeurant) à pondre des formules complexes ?
Cisco a très bien expliqué sa formule mais perso, je trouve qu'un tableau "normal" eut été plus simple à gérer :)

Persiste et signe

P.
 

Discussions similaires

Statistiques des forums

Discussions
311 724
Messages
2 081 936
Membres
101 844
dernier inscrit
pktla