Fonctions SI/ET/OU imbriquées

bonjour à tous,

je galère depuis des heures à essayer de trouver une formule mais rien de ce que j'ai essayé jusqu'à présent ne fonctionne

voici ma problématique :

j'ai un tableau de 200 000 lignes qui contient les colonnes "relations aller retour" (des lignes aeriennes) et "type compagnie" (cies low cost, aviation d'affaires, cies régulières ou groupe AF). Je souhaiterais créer une colonne supplémentaire qui répondrait à :

tu regardes la colonne aller retour, si tu trouves la même relation (ex Paris - Brest) et que dans la colonne "type de compagnie" il y a pour cette relation le "groupe AF" ET l'une des 3 autres possibilités ("Low cost", "compagnie régulière" ou "aviation d'affaires") alors tu m'écris dans la 3e colonne "ligne concurrencée" sinon tu m'écris "ligne non concurrencée"

je vous joins un exemple

je vous remercie par avance de votre aide

bien cordialement

Fabienne

10formule-ccu.docx (14.79 Ko)

Bonjour,

En clair il faut faire un NB.SI.ENS ou un équivalent, qui va compter le nombre de lignes comme celle que tu as actuellement, suivant les critères que tu as cité, il reste ensuite juste à regarder si cette valeur est supérieure à 1 (la ligne actuelle est comptée), et si c'est le cas, afficher le message concuréncée, sinon, pas concuréncée, ce test peut se faire avec un SI.

Il est également possible de compter le nombre de lignes répondants à ces critères avec un SOMMEPROD.

merci Ausecour pour ces explications. J'ai en effet essayé avec la fonction sommeprod que je connais mais je n'ai pas réussi à imbriquer toutes les conditions de la formule car pour moi il n'y a pas de valeur si vrai/valeur si faux dans une somme prod. J'ai donc essayé le NB.SI.ENS mais je n'arrive pas à faire la formule correctement

Voici les formules que j'avais tentées :

=SOMMEPROD(($A$2:$A$200000)*($B$2:$B$200000="groupe AF")*(($B$2:$B$200000="cie régulière")+($B$2:$B$200000="low cost")+($B$2:$B$200000="aviation d'affaire"));"ligne concurrencée";"ligne non concurrencée)

NB.SI.ENS(($A$2:$A$200000=A2);($B$2:$B$200000="groupe AF");ou(($B$2:$B$200000="cie régulière");($B$2:$B$200000="low cost");($B$2:$B$200000="aviation d'affaire"));"ligne concurrencée";"ligne non concurrencée)

merci encore pour votre aide

cdlt

Fabienne

Bonjour,

C'est déjà pas mal ce que tu es arrivée à faire, je tente une correction de formule:

=Si(SOMMEPROD(($A$2:$A$200000)*($B$2:$B$200000="groupe AF")*(($B$2:$B$200000="cie régulière")+($B$2:$B$200000="low cost")+($B$2:$B$200000="aviation d'affaire")))>1;"ligne concurrencée";"ligne non concurrencée)

Je viens de tester ta formule Ausecour et cela me renvoie malheureusement #VALEUR

elle me plaisait bien pourtant! je me demande si ce n'est pas qu'une question de parenthèse..

j'ai fait plusieurs tests mais sans succès..

Bonjour fabiennesd, Ausecour, le forum

Je me permet juste de dire que sur la formule reportée il manque " juste avant la dernière parenthèse de fermeture afin de chercher une référence texte il me semble

"ligne non concurrencée)

Cordialement

Bonjour fabiennesd, Ausecour, le forum

Je me permet juste de dire que sur la formule reportée il manque " juste avant la dernière parenthèse de fermeture afin de chercher une référence texte il me semble

"ligne non concurrencée)

Cordialement

C'est ça

Merci du coup de pouce

Bonjour Etsije, Ausecour,

c'est vraiment gentil d'essayer de m'aider. Vous avez l'œil et l'expérience!

malheureusement, la formule ne fonctionne toujours pas #VALEUR

la voici dans son intégralité :

=SI(SOMMEPROD(($Z$2:$Z$200000)*($Y$2:$Y$200000="groupe AF")*(($Y$2:$Y$200000="cie régulière")+($Y$2:$Y$200000="low cost")+($Y$2:$Y$200000="aviation d'affaire")))>1;"ligne concurrencée";"ligne non concurrencée")

bonne fin de journée

Bonsoir fabiennesd, ausecour, le forum

Javoue que sans un fichier excel( anonymisé bien entendu) je n'arrive pas à visionner ton soucis. Si tu pouvais joindre ton fichier ou quelque chose approchant ça m'aiderait pour t'aider lol

Cordialement

voici un fichier exemple avec dans la colonne C les réponses qui devraient apparaitre avec la formule et quelques explications ci-dessous :

Bordeaux Lyon = ligne concurrencée car en plus du groupe AF une cie low cost assure la liaison

Nice Nantes = ligne concurrencée car en plus du groupe AF une cie régulière assure la liaison

Paris Brest = non concurrencée car seul le groupe AF assure la liaison

Nantes Madrid = ligne non concurrencée car le groupe AF n'assure pas la liaison même si une low cost et une cie régulière sont présentes

voilà, j'espère que j'ai été claire

merci, etsije et Ausecour de me consacrer de votre temps

bonne soirée

Bonsoir,

On est pas si loin du SI/ET/OU du titre !

Voici donc une formule sortie du chapeau après une bonne vingtaine de minutes pour comprendre la logique

=SI(SOMMEPROD(N(($A$2:$A$15=A2)))=SOMMEPROD((($A$2:$A$15=A2)*($B$2:$B$15="groupe AF")));"Ligne non concurrencée";SI(SOMMEPROD(($A$2:$A$15=A2)*($B$2:$B$15="groupe AF"));"Ligne concurrencée";SI(MOD(SOMMEPROD(($A$2:$A$15=A2)*($B$2:$B$15={"cie régulière"."low cost"."aviation d'affaires"}));2)=0;"Ligne concurrencée";"Ligne non concurrencée")))

Ça ne fait pas rêver

A me redire avec le fichier si c'est bien cela ?

Leakim

Bonjour fabiennesd, ausecour, le forum

Si j'ai bien compris ta demande est juste de savoir si la ligne est concurrencée ou non

Si c'est cela je te renvoi ton fichier avec en colonne D une proposition

Cordialement

Bonjour,

ah oui, avec le fichier Excel ça va mille fois mieux

Je te propose cette solution:

Je t'ai mis deux formules différentes qui te donnent le même résultat, le SOMMEPROD et le NB.SI.ENS comptent chacun à leur façon, le nombre de lignes qui font le même trajet, et qui sont un type de ligne différent, le SI se charge de tester si il y en a au moins 1 qui concurence la ligne, et fait apparaitre le texte correspondant.

merci Ausecour, Etsije et Leakim,

je teste ces formules dès demain matin et je vous tiens au courant..

attn Etsije : pour répondre à ta question en fait non ..

ce n'est pas simplement de savoir si la ligne est concurrencée ou pas.

Il faut que la réponse soit "ligne concurrencée" seulement si le groupe AF opère la ligne ainsi qu'une low cost, ou une cie régulière ou une cie type aviation affaire.

si la ligne est opérée par exemple par une low cost ET une cie régulière la réponse sera "non concurrencée" puisque groupe AF ne la fait pas...

j'ai besoin, en ligne concurrencée, uniquement des lignes opérées par 2,3 ou 4 cies DONT groupe AF obligatoirement

ça complique un peu tout..

j'espère que je suis claire..

merci à vous pour le temps que vous m'offrez

et excellente soirée

attn Leakim,

comme pour les formules d'Ausecour et Etsije, je vais pouvoir tester ta formule dès demain matin en arrivant au travail.

J'ai une petite question :

que signifie le N au début de la formule?

=SI(SOMMEPROD(N(($A$2:$A$15=A2)))=SOMMEPROD((($A$2:$A$15=A2)

et quelle est la différence entre des parenthèses ( ), { } et [ ] dans les formules?

Bonjour,

En attendant que tu testes les différentes propositions.

Le N ou -- au début de la fonction sommeprod est un marqueur qui transforme le calcul en test logique vrai ou faux. d'ailleurs le *1 de Ausecour transforme le test logique en valeur 0 ou 1. C'est d’ailleurs plutôt bien vue pour dans cette situation

Pour les parenthèses () c'est pour les matrices à calculer de façon classique. les parenthèses {} sont ici utilisées pour définir une matrices de données que l'on charge en dur directement dans la formules ici "cie régulière"."low cost"."aviation d'affaires".

Donc dans la formules je prend la plage où je peux trouver l'un des trois critères : ($B$2:$B$15={"cie régulière"."low cost"."aviation d'affaires"}) . Si en b2:b15 l'un des critères est présent alors vrai

Pour [] je ne sais pas ?

Leakim

Il faut que la réponse soit "ligne concurrencée" seulement si le groupe AF opère la ligne ainsi qu'une low cost, ou une cie régulière ou une cie type aviation affaire.

si la ligne est opérée par exemple par une low cost ET une cie régulière la réponse sera "non concurrencée" puisque groupe AF ne la fait pas...

Dans ce cas mon ancienne formule ne fonctionnera pas, je te transmets une nouvelle version:

Bonjour à tous,

voici le fichier source anonymisé avec les retranscriptions de vos formules.

Les 2 premières formules d'ausecour et celle de Leakim ne fonctionnent pas, soit j'ai mal recopié soit il y a une erreur de syntaxe que je ne sais pas corriger. La formule d'Etsije renvoie une valeur qui n'est pas correcte mais c'est parce que j'avais mal exprimé mon besoin.

Les deux dernières formules transmises par Ausecour semblent bien fonctionner et ont l'air toutes deux de renvoyer des réponses correctes. Dans quelles circonstances sont elles sensées renvoyer une donnée différente?

=SI(NB.SI.ENS($Z$2:$Z$200000;$Z2; $Y$2:$Y$200000;"<>"&$Y2)>=1;"ligne concurencée";"ligne non concurencée")

=SI(ET(NB.SI.ENS($Z$2:$Z$200000;$Z2;$Y$2:$Y$200000;"<>"&"groupe AF")>=1;NB.SI.ENS

($Z$2:$Z$200000;$Z2;$Y$2:$Y$200000;"groupe AF")>=1);"ligne concurencée";"ligne non concurencée")

en testant la requête je m'aperçois qu'en fait il faut que je rajoute une condition supplémentaire

pour que tout ceci soit vrai il faut que dans la colonne Q (seats Y) le chiffre renvoyé soit supérieur à 0 (j'ai supprimé mes données pour ce fichier test.

Merci encore de tous ces conseils, de tous les forums excel testés, c'est vraiment celui ci qui me répond le plus rapidement et avec beaucoup de pédagogie

bonne journée à tous

Bonjour

J'ai modifié la dernière formule que j'avais pour rajouter un critère à chaque NB.SI.ENS, plage critère, colonne Q, critère: ">=1", comme ça, seules les lignes avec un nombre de sièges positifs et non nuls seront comptés

Voici le fichier modifié:

Bonjour,

Merci pour ton retour sur la pédagogie du Forum

Je te laisse regarder la proposition d'Ausecour.

Il faut avoir en tête que la fonction SOMMEPROD supporte mal les plages de données avec des vides ou avec des données issues d'une formule ce qui est le cas avec rechercheV...

Donc ma proposition avec Sommeprod n'est pas assez stable pour ton projet.

Bonne suite à toi.

Leakim

Rechercher des sujets similaires à "fonctions imbriquees"