Indiquer une valeur si plusieurs conditions sont remplies

Bonjour à tous,

J'ai cherché un peu sur le forum mais comme vous pouvez le constater dans le libellé du sujet, je ne sais pas trop où chercher.

Donc voilà mon problème :

j'ai 1 onglet "résultat" avec les colonnes "A" et "B" renseignées et les colonnes "C" et "D" vides.

Un autre onglet "données" avec les colonnes "A à E" complétées.

Ici c'est un extrait mais chaque onglet comporte plus de 5000 lignes

Donc je souhaite trouver une formule ou une macro qui me permet de : et c'est là que ça ce complique, comment être clair

je voudrais que pour compléter "C3" et "D3" de l'onglet "résultat", Excel recherche dans la colonne "A" de l'onglet" données" le n° correspondant, plus le fait que la donnée dans "B" de l'onglet "résultat" soit comprise entre les données des colonnes "B" et C" de l'onglet "données".

Bon si vous avez tout compris vous êtes vraiment fort sinon posez moi des questions.

Merci d'avance pour votre aide.

papuche

bonjour

un essai sans grande conviction; de plus avec 5000lignes =>vba

cordialement

Bonsoir,

Si j'ai compris, je proposerais cette formule en C3 :

[size=85]=SOMMEPROD((données!$A$10:$A$26=$A3)*($B3>=données!$B$10:$B$26)*($B3<données!$C$10:$C$26)*données!D$10:D$26)

[/size]

Elle se tire en D3 et vers le bas.

Mais ta table données semble tronquée, il y a discontinuité qui fait que certaines valeurs ne trouvent pas à se situer entre deux valeurs de la table.

Cordialement.

Ferrand

Bonjour,

Déjà, merci pour ces réponses rapides

Je vais tester sur un fichier plus complet mais voici mes premières remarques :

Dans la réponse de Tulipe4 il y a les cellules "C18 à D 18" de l'onglet résultat qui indiquent un résultat faux car il n'y a pas, dans l'onglet "données", de cellules "B" et "C" répondant aux critères.

Ce qui m'amène à l'interrogation de MFerrand concernant les données tronquées.

Il peut effectivement ne pas y avoir de cellules permettant de situer la colonne "B" de l'onglet "résultat". Et donc dans ce cas il faudrait que la cellule destination reste vide et n'indique pas "0" qui pour moi est un résultat à prendre en compte.

Je vais tester sur un fichier plus complet et je reviens vers vous mais peut être que le VBA serait pas mal, j'attends les propositions des spécialistes.

Amicalement

Papuche

Bonjour,

Modification rapide formule pour répondre à la précision apportée à ta demande :

[b]=SI(SOMMEPROD((données!$A$10:$A$26=$A3)*($B3>=données!$B$10:$B$26)*($B3<=données!$C$10:$C$26));données!D$10:D$26;"")

[/b]

Bonne journée.

Ferrand

bonjour M ferrand

Sommeprod peut etre mais dans la col b de données ,les données ne sont pas numeriques a cause du + ; d'ou ma conversion

il subsiste un probleme de taille : je suis issu des travaux publics ... donc je connais la notion de distance par rapport a un PR ...

donc quand on lit; 593 +61 ; c'est le pr 593 qui represente une distance cumulée de x metres entre pr (en general 1000 metres mais ce n'est pas toujours vrai ) et;c'est là que le ba^t blesse : + 61 metres ; ceci dit , en convertissant les + en , pour avoir une val numerique il faut( a mon humble avis) trouver le moyen de ne pas avoir 593,61 mais 593,061

bien sur on pourrait substituer le + par un 0 => 593061 mais si il y a 3 chiffres apres le + cela impose d'adjoindre une condition

qui annule ce 0 ( j'imagine laquelle.....) ;en gros; il doit toujours y avoir 3 chiffres apres la virgule

nb) EQUIV( ........1) trouve la val immediatement superieure

a moins que je prenne la tete pour rien ..... neanmoins; papuche n'est pas sorti de l'auberge

cordialement

Rebonjour,

Premier test sur le fichier complet avec la formule de MFerrand, j'ai le message "valeur" qui s'affiche dans les cellules alors que sur le fichier que j'ai posté ça fonctionne ???

Avec la nouvelle formule, même dans le fichier simple j'ai des "valeur" et "vrai" qui s'affiche ?

Pour la formule de tulipe4 je n'arrive pas à la reprendre, idem il y a un message "valeur" je pense que c'est à cause des signes en tête et fin de formule qui disparaissent et je ne sais pas comment les remettre.

En effet le "+" remplace une virgule et les chiffres après le "+" sont des mètres.

Tous devraient avoir 3 chiffres après le "+", ce sont les dernier "0" qui ne s'affichent pas. Donc 598+06 = le km598,060

Je cherche et espère trouver

Bonne journée

Papuche

re

ah!!...... pas con tulipe et sa boule de crystal

pour le coup du 0 il faut jouer du CHERCHE ("+";....) et selon la position renvoyée on met un 0 ou pas (voila la combine en gros )

pour les erreurs tu peux mettre en amont SIERREUR( la grosse formule);"")

cordialement

Bonjour,

@tulipe4

Tes considérations sont tout à fait légitimes. Mais je ne suis pas entré dans des problèmes de conversion... les valeurs des colonnes B et C sont des chaînes et les opérateurs > et < fonctionnent parfaitement avec.

Et si tu compares un nombre quelconque à une chaîne quelconque, le nombre sera toujours inférieur à la chaîne.

10000 < "" te reverra la valeur VRAI...

Effectivement, si la notation faisait apparaître que l'indication en C soit inférieure à celle de B lors de la comparaison... mais il ne m'avait pas semblé que cela fut le cas.

@Papuche

Effectivement ma correction était un peu précipitée (et faite directement dans le post, donc pas de résultat en retour). Le fait de sortir la matrice résultat de la fonction SOMMEPROD renvoie une erreur.

Je n'ai rien de mieux pour l'instant que répéter l'expression, ce qui double la longueur de la formule (que l'on pourra raccourcir en utilisant des plages nommées et éventuellement en la dédoublant avec la partie utilisée en test et répétée mise en formule nommée).

Mais il y a lieu de vérifier dans ta base que les fourchettes des col. B et C de données sont bien notées telles qu'une comparaison alphabétique fasse toujours apparaître la colonne C supérieure à B.

Je reviens dès que j'ai mieux (mais je m'absente entre temps...)

Bonne journée.

Ferrand

Voilà un raccourcissement...

=(données!$A$10:$A$26=resultats!$A3)*(resultats!$B3>=données!$B$10:$B$26)*(resultats!$B3<=données!$C$10:$C$26)

Cette expression est insérée en référence dans le gestionnaire de noms (je l'ai fait avec le nom "td") en étant positionné sur la feuille resultats en ligne 3.

La formule devient alors : =SI(SOMMEPROD(td);SOMMEPROD(td*données!D$10:D$26);"")

re M_ ferrand

en matiere de geometrie /topographie....... tout marche en val numerique , d'ou mon interrogation

c'est tout le probleme de fichiers avec des données "papelards" qui servent de base à d'eventuels calculs

et......... encore subsiste la conversion en metre des :593 ; car il faut savoir savoir a quoi il correspondent ( lambert. pr de la DDE ?????) .........

tu vois , pleins de lievres sont soulevés , ou alors pas du tout

cordialement

J'ai testé la dernière formule de MFerrand dans un autre fichier, non complet, ça fonctionne.

Je ne sais pas comment aller dans le registre des noms pour saisir "td" donc je sélectionne la td via la boite de fonction et ça semble fonctionner.

Là où ça se complique c'est quand j'essaie de faire la même chose dans le fichier plus lourd, et là c'est la galère

Je vais continuer et chercher où ça coince.

Merci encore.

bonne soirée

re

avec 5000 lignes ;+ sommeprod gourmand ; cela ne peux que ramer ; d'ailleurs je te conseille de mettre en calcul sur ordre pour menager ton poste

cordialement

edit >>>>>>idée : je ne sais pas trop ;mais il faudrait tester

avec un DECALER bien senti ; le SOMMEPROD ou ma solution pourrait n'avoir qu'a mouliner dans une plage variable (au lieu des 5000 lignes) ; cette plage peut etre definie par la quté de 610000 ( ou autre) , a partir du moment ou ils ne sont pas dispersés

bien sur; ce critere devra etre ennoncé ,soit manuellement ou a partir d'une liste de choix ; ce qui implique que l'utilisateur ait un minimum de connaissance sur le sujet

cordialement

re edit :pour acceder facilement au gestionnaire de nom ;tu fais Ctrl et la touche F3

Pour "td" (tu peux mettre un autre nom, mais le même dans la formule...) tu fais comme pour nommer une plage.

Définir un nom ou tu vas dans le gestionnaire (méthode rapide tulipe4) et Nouveau. Comme référence, au lieu d'une référence de plage, le signe = et tu colles l'expression indiquée. Attention : il faut faire cette opération en étant positionné sur la ligne 3 de resultats pour que l'expression s'applique correctement.

Par contre, comme on l'aait dit au début, je partage l'avis de tulipe4 : ça va être plutôt lourd et un solution macro est à envisager.

Bonne soirée pour le moment.

Ferrand

Bonjour,

Pour le nom, j'ai fait ce qui est indiqué sur la signature de tulipe, j'ai cherché et je crois que j'ai trouvé, mais pas encore eu le temps de tester.

Pour la lourdeur du fichier, je peux peut être multiplié les 2 onglets et donc faire des recherches sur 1000 lignes

Là où je dois encore chercher c'est que pour l'instant j'ai pas mal d'erreurs, des lignes où ce qui est repris dans les lignes n'est pas le bon résultat, voir même que pour certaine ça ressemble plus à une addition de cellules qu'à un copier/coller du chiffre indiqué.

Je continu les recherches

Merci encore pour vos commentaires qui, comme je suis débutant Excel, ne sont pas toujours clair de suite mais me permettent d'avancer.

Bonne journée

Papuche

bonjour

je demeure persuadé que les erreurs sont dues à un probleme de conversion du + et le 0 si 2 chiffres ; il y a aussi peut etre des problemes d'espace saisis involontairement ;ces derniers polluent tout >>> jouer du SUPPRESPACE

dis nous la finalité de ton ouvrage

cordialement

Re,

Je vais voir pour remplacer le+ par une virgule et mettre 3 chiffres après la "," obligatoirement mais vos formules ne me disent rien.

J'ai créé le "td" mais ça ne fonctionne pas, ce coup-ci je n'ai que des cellules vides.

Dans mon fichier les noms d'onglets ne sont pas les mêmes mais je les ai pris en charges dans la formule "td".

MFerrand, quand vous me dite " il faut faire cette opération en étant positionné sur la ligne 3 de résultats pour que l'expression s'applique correctement.", concrètement je fais comment ?

J’ai sélectionné les cellules pleines de la ligne 3 avant de faire "nouveau" pour créer "td" je suppose que ce n'est pas la bonne méthode.

Pour envisager la macro, il faut que je poste dans une autre section je suppose ?

Cordialement

Bonjour,

Rapidement car j'ai une urgence...

Tu te mets sur la feuille resultats, tu sléectionnes la cellule C3 (par exemple, ça fonctionnera tant que tu es sur la ligne 3).

Ensuite tu fais Définir un nom > La boîte de dialogue s'affiche avec en référence la cellule dans laquelle tu es. Tu effaces cette référence, garde le signe =, remplace l'adresse de la cellule par l'expression telle que je l'ai donnée pour fournir le bon résultat pour la ligne 3 (elle s'adaptera sur les lignes suivantes, de la même façon que lorsque tu tires une formule pour la recopier). Tu mets le nom "td". Tu valides. Tu vérifies dans le gestionnaire de nom que l'expression est bien complète (pas altérée ou tronquée lors du collage ; pas superflu, car cela m'arrive suffisamment souvent pour justifier cette vérification systématique).

Dans la formule que tu mets ensuite dans la cellule C3, que tu vas tirer sur D3 et vers le bas, td remplace l'expression nommée, ce qui permet de reccourcir sensiblement la formule.

A+

Bon ! Voilà une proposition macro. Aux tests elle me donne le même résultat que les formules.

Vois si elle convient sur un échantillon plus large.

Sub RechRayon()
    Dim L, K, a, b, i%, n%, f%, fd As Worksheet
    Set fd = Worksheets("données")
    i = 2
    With Worksheets("resultats")
        f = Range("A" & .Rows.Count).End(xlUp).Row
        Do
            i = i + 1
            L = .Cells(i, 1).Value
            n = fd.Columns("A").Find(L).Row
            Do While fd.Cells(n, 1).Value = L
                a = Replace(fd.Cells(n, 2).Value, "+", ",") * 1
                b = Replace(fd.Cells(n, 3).Value, "+", ",") * 1
                K = Replace(.Cells(i, 2).Value, "+", ",") * 1
                If K >= a And K <= b Then
                    .Cells(i, 3).Value = fd.Cells(n, 4).Value
                    .Cells(i, 4).Value = fd.Cells(n, 5).Value
                    Exit Do
                End If
                n = n + 1
            Loop
        Loop While i < f
    End With
End Sub

Bonne soirée.

Ferrand

NB- Là j'ai converti en décimal, comme tu peux voir.

bonjour M ferrand

il me semble que ton code ne resoud pas le probleme du 0 si il n'y a que 2 chiffres apres le +

593+62 devra donner 593.062 parcontre 593+620 fera 593.620

c'est la position du + a partir de la fin de la chaine qui determine la presence ou non du 0 apres la virgule

avec des formules on pourrait passer par NBCAR (....) - CHERCHE("+"....

cherche renvoiera souvant 4 ,mais ce n'est pas toujours le cas car il peut y avoir (dans les 5000) des PR <100

dolipranne

cordialement