Discussion :

[Beerzebub]

Bonjour,

Je rencontre un petit problème d'utilisation de la formule SOMMEPROD avec du texte, c'est-à-dire que j'aimerais savoir si il est possible de combiner SOMMEPROD avec un filtre "contient".

Exemple avec deux colonnes:
A--------------B
PARIS-----Niveau trop bas
PARIS-----Niveau trop haut
PARIS-----Comm.
LYON------Intrusion
LYON------Intrusion
LYON------Etat haut

Et j'aimerais compter combien ont en colonne A "PARIS" ET en colonne B contienne "Niveau".

Pour le moment je me sert de la formule =SOMMEPROD((A:A="PARIS")*(B:B="Niveau trop bas")), qui me compte bien qu'il y en a un seul.
Mais dans mon cas ayant plus de 10 000 lignes, et bien trop de cellules différentes mais contenant le mot "Niveau" en commun, j'aurais aimer savoir si il existe une solution.

D'avance merci, pour votre aide.

[EngueEngue]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=Sommeprod((A:A="PARIS")*(gauche(B:B;6)="Niveau"))

[rjamin]

Bonjour,
A tester

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A:A="Paris")*(B:B=TROUVE("Niveau";B11;1)>0))

[Beerzebub]

Tout d'abord merci, mais il arrive que le mot clef soit au milieu c'est à dire "Attente niveau trés haut". Dans ce cas "niveau" se trouve au milieu d'autres mots.

[QuestVba]

Bonjour,

Tu peux essayer ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A:A="PARIS")*(NON(ESTERREUR(TROUVE("niveau";B:B)))))

• TROUVE("niveau";B:B) : renvoie une erreur #VALEUR lorsque la cellule ne contient pas "niveau"
• ESTERREUR(TROUVE("niveau";B:B)) : renvoie VRAI lorsque la cellule ne contient pas "niveau"
• NON(ESTERREUR(TROUVE("niveau";B:B))) : renvoie donc VRAI lorsque la cellule contient "niveau"

Du coup, SOMMEPROD en tient compte

[Beerzebub]

Bonjour,

Tu peux essayer ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A:A="PARIS")*(NON(ESTERREUR(TROUVE("niveau";B:B)))))

• TROUVE("niveau";B:B) : renvoie une erreur #VALEUR lorsque la cellule ne contient pas "niveau"
• ESTERREUR(TROUVE("niveau";B:B)) : renvoie VRAI lorsque la cellule ne contient pas "niveau"
• NON(ESTERREUR(TROUVE("niveau";B:B))) : renvoie donc VRAI lorsque la cellule contient "niveau"

Du coup, SOMMEPROD en tient compte

Merci beaucoup QuestVba, cela fonctionne a merveille, est il possible d'ajouter plus "d'objet" a la recherche ? C'est-à-dire rajouter dans la colonne B, "trop" avec en plus "Réservoir" a savoir que ces deux objets ne sont pas dans la même cellule.

J'ai essayé de doubler la deuxième partie de la fonction, mais sans succès.

[papouclo]

Bonjour,
Une autre proposition avec la formule NB.SI.ENS (à partir d'Excel 2007) et le caractère générique *

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS(A:A;"PARIS";B:B;"*niveau*")

Cordialement
Claude

[QuestVba]

Tu veux utiliser combien de critères ? Comment veux-tu compter si les critères sont dans la même cellule ?

tu peux faire une addition de SOMMEPROD

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A:A="PARIS")*(NON(ESTERREUR(TROUVE("niveau";B:B)))))+SOMMEPROD((A:A="PARIS")*(NON(ESTERREUR(TROUVE("haut";B:B)))))

[Beerzebub]

Bonjour, encore merci de vos réponses.

Bonjour,
Une autre proposition avec la formule NB.SI.ENS (à partir d'Excel 2007) et le caractère générique *

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS(A:A;"PARIS";B:B;"*niveau*")

Cordialement
Claude

Merci Claude, cette fonction fonctionne très bien aussi, mais lorsque j'ai essayé de m'en servir hier, elle m'indiquait 0, je ne comprend pas pourquoi maintenant elle fonctionne, j'ai du très probablement faire une erreur de syntaxe.

Tu veux utiliser combien de critères ? Comment veux-tu compter si les critères sont dans la même cellule ?

Ces deux questions me sont elles destinés ? Si oui, je ne comprend pas tout a fait.

[QuestVba]

Bonjour,

Oui, je n'ai pas compris (également) ceci:

est il possible d'ajouter plus "d'objet" a la recherche ? C'est-à-dire rajouter dans la colonne B, "trop" avec en plus "Réservoir" a savoir que ces deux objets ne sont pas dans la même cellule.

J'ai essayé de doubler la deuxième partie de la fonction, mais sans succès.

[Beerzebub]

Bonjour,

Bonjour,

Oui, je n'ai pas compris (également) ceci:

C'est en rapport avec cette fonction:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A:A="PARIS")*(NON(ESTERREUR(TROUVE("niveau";B:B)))))

Savoir si il est possible en plus de "niveau" chercher toujours dans la même colonne "courant".
Un peu comme ça, bien que la fonction soit fausse dans ce cas.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD((A:A="PARIS")*(NON(ESTERREUR(TROUVE("niveau";"courant";B:B)))))

En espérant que ce soit clair.

[papouclo]

Bonjour,
Avec le premier tableau de données légèrement modifié (PARIS au lieu de LYON en A6) , le nombre de lignes avec PARIS en colonne A et niveau ou haut en colonne B est donné par la formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS(A:A;"paris";B:B;"*niveau*") + NB.SI.ENS(A:A;"paris";B:B;"*haut*") - NB.SI.ENS(A:A;"paris";B:B;"*niveau*haut*")

[IMG][/IMG]
La première partie de la formule, c'est à dire la somme

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= NB.SI.ENS(A:A;"paris";B:B;"*niveau*") + NB.SI.ENS(A:A;"paris";B:B;"*haut*")

peut s'obtenir encore avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

= SOMMEPROD ( NB.SI.ENS ( A:A ; "paris" ; B:B ; "*" & {"niveau";"haut"} & "*" ) )

Cordialement
Claude

[Beerzebub]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

- NB.SI.ENS(A:A;"paris";B:B;"*niveau*haut*")

Si j'ai bien compris, cette partie de la fonction permet de supprimer les doublons ? En tout cas bravo, j'ai à apprendre sur Excel, qui m'a l'air d'être un logiciel hyper complet.

[papouclo]

la formule est une somme de trois termes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS(A:A;"paris";B:B;"*niveau*") + NB.SI.ENS(A:A;"paris";B:B;"*haut*") - NB.SI.ENS(A:A;"paris";B:B;"*niveau*haut*")

Le premier terme NB.SI.ENS(A:A;"paris";B:B;"*niveau*") décompte les lignes contenant niveau en colonne B (et bien sûr Paris en colonne A). La ligne 2 est donc comptée à ce moment .
Le deuxième terme NB.SI.ENS(A:A;"paris";B:B;"*haut*") décompte les lignes contenant haut en colonne B. La ligne 2 est donc comptée une seconde fois à ce moment.
Elle est comptée 2 fois donc une fois de trop comme toutes les lignes contenant à la fois niveau et haut en colonne B et bien sûr Paris en A.
NB.SI.ENS(A:A;"paris";B:B;"*niveau*haut*") décompte les lignes contenant simultanément niveau et haut en colonne B. En retranchant ce troisième décompte de la somme des deux premiers,
on corrige donc le problème précédemment évoqué.
Cordialement
Claude