fonction DROITEREG associée à une fonction SI (double condition)

[lili28]

Bonjour,
Je suis débutante sur EXCEL et je rencontre un problème que je ne sais pas résoudre.
Je ne trouve pas non plus de réponse sur le net .
Il s’agit de l’utilisation de la fonction DROITEREG. Je souhaiterais y associer une double condition.
Je joins un fichier ce sera plus parlant.
Le fichier joint comporte 2 onglets : une base de données et un onglet nuage de points.
J’ai fait figurer sur le nuage de points une courbe de tendance linéaire et y ai affiché l’équation correspondante. Au-dessus du tableau, figurent les résultats de la fonction DROITEREG (dont j’ai besoin par ailleurs).
Quand je filtre sur les 2 premières colonnes de la BDD, le nuage se met bien à jour ainsi que la courbe de tendance et l’équation associée.
Je souhaiterais qu’il en soit de même pour les valeurs issues de la fonction DROITEREG.
J’ai fait plein d’essais en associant à la fonction « DROITEREG » la fonction « SI » (colonne A ="lundi et colonne B = "janvier" par exemple), mais je n’y parviens pas : je ne trouve pas la bonne syntaxe à moins que ces 2 fonctions ne soient pas compatibles.
Quelqu’un aurait-il la solution SVP ? (la plus simple serait le mieux compte tenu de mon niveau !).
Un grand merci

 

[job75]

Bonsoir lili28, bienvenue sur XLD,

Voyez le fichier joint et cette fonction VBA à placer impérativement dans un module standard :

Function Visible(r As Range)
Dim a(), n&
For Each r In r
    If Not r.EntireRow.Hidden Then
        ReDim Preserve a(n)
        a(n) = r
        n = n + 1
    End If
Next
Visible = a 'tableau à une dimension
End Function

Elle est utilisée dans la formule matricielle sur C2: D3 :

=DROITEREG(Visible(bdd!C2:C69);Visible(bdd!D2:D69);VRAI;VRAI)

A+

 

[lili28]

Bonjour job75,

Merci beaucoup de votre réponse.
J'ai fait un test et je m'aperçois que ma demande n'est pas suffisamment précise.

Je me permets de vous adresser un fichier plus complet.
En effet, les filtres sur les 2 premières colonnes ne s'opèrent pas directement dans l'onglet "BDD" mais dans l'onglet "nuage" dans les cellules O7 et O8.
Cela permet de faire des filtres "manuels" directement dans l'onglet "bdd" s

 

[lili28]

Bonjour job75,

Merci beaucoup de votre réponse.
J'ai fait un test et je m'aperçois que ma demande n'était pas suffisamment précise.
Désolée

Je me permets de vous adresser un fichier plus complet.
En effet, les filtres sur les 2 premières colonnes ne s'opèrent pas directement dans l'onglet "BDD" mais dans l'onglet "nuage" dans les cellules O7 et O8.
Cela permet de faire des filtres directement dans l'onglet "bdd" (par exemple sur la colonne "commentaire") sans que cela n'ai d'impact sur la formule de la courbe de tendance mais en mettant à jour le nuage de points (dans ce cas la courbe ne correspond pas aux points du nuage, mais c'est volontaire)
J'ai fait figurer la médiane qui répond à cette demande en cumulant la fonction "médiane" avec la fonction "SI" mais que je n'arrive pas à reproduire cela avec la fonction DROITEREG.
Est-ce possible ou voyez-vous une autre solution?

Encore merci de votre aide!
Cordialement

 

[job75]

Bonjour lili28, le forum,

Puisque vous ne voulez pas appliquer un filtre à la 1ère feuille il faut un autre fonction VBA :

Function Filtrer(colonnes As Range, col1%, col2%, crit1$, crit2$, col%)
Dim tablo, a(), i&, n&
tablo = Intersect(colonnes, colonnes.Parent.UsedRange.EntireRow) 'matrice, plus rapide
For i = 2 To UBound(tablo)
    If tablo(i, col1) = crit1 And tablo(i, col2) = crit2 Then
        ReDim Preserve a(n)
        a(n) = tablo(i, col)
        n = n + 1
    End If
Next
If n Then Filtrer = a 'tableau à une dimension
End Function

Elle est utilisée dans les formules en C2: D3 et N13 du fichier joint, elle n'agit pas sur le graphique.

A+

 

[lili28]

Re-bonjour job75,

Merci de votre retour.
C'est une autre solution mais qui ne répond pas précisément à mon besoin. Ce n'est pas simple à expliquer clairement, désolée.
Dans le fichier de travail que j'utilise, la formule pour calculer la médiane correspond tout à fait à ce dont j'ai besoin.
C'est celle qui est reportée en cellule N11 du fichier joint (pas de macro). La valeur de médiane évolue en fonction de la sélection opérée en O7 et O8. Cela nous permet, en filtrant par exemple "manuellement" (c'est à dire sans formule) sur la colonne G de l'onglet bdd, de conserver la valeur de médiane représentée sur le nuage de points (non représentée dans le fichier transmis) et issue de la sélection en O7 et O8 tout en visualisant uniquement les points issus du filtre "manuel" de la colonne G de l'onglet bdd.

Je pense qu'en ajoutant la fonction "SI" à la fonction DROITERREG (autrement dit en transposant ce qui a été fait pour la formule de médiane en N11 de l'onglet nuage), je devrais obtenir les résultats escomptés.
Mon problème est comment faire?
J'ai fait de nombreux essais : soit je fais une erreur de syntaxe, soit la fonction DROITEREG n'est pas compatible avec la fonction "SI".

Qu'en pensez-vous?

Encore merci de votre aide (et de votre patience!)
Cordialement

 

[job75]

La fonction DROITEREG doit travailler sur des cellules numériques jointives.

Si vous voulez que le graphique soit mis à jour avec les cellules O7 et O8 il faut utiliser la feuille auxiliaire "Filtre".

Et placer ces macros dans le code de la feuille "nuage" :

Private Sub Worksheet_Activate()
Worksheet_Change [O7] 'lance la macro
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [O7:O8]) Is Nothing Then Exit Sub
Dim crit1$, crit2$, tablo, n&, i&, j%
crit1 = [O7] & "*": crit2 = [O8] & "*"
tablo = Sheets("bdd").[A1].CurrentRegion.Resize(, 4) 'matrice, plus rapide
n = 1
For i = 2 To UBound(tablo)
    If tablo(i, 1) Like crit1 And tablo(i, 2) Like crit2 Then
        n = n + 1
        For j = 1 To 4
            tablo(n, j) = tablo(i, j)
        Next j
    End If
Next i
With Sheets("Filtre")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    .UsedRange.ClearContents 'RAZ
    [C2].Name = "Y": [D2].Name = "X" 'RAZ
    .[A1].Resize(n, 4) = tablo
    If n > 1 Then
        .[C2].Resize(n - 1).Name = "Y" 'plage nommée
        .[D2].Resize(n - 1).Name = "X" 'plage nommée
    End If
End With
End Sub

La macro défini les plages "X" et "Y" utilisées par le graphique et par les formules en C2: D3 et N13.

Fichier (3).

A+

 

[lili28]

Une nouvelle fois merci bcp à vous

Cela ne produit pas les mêmes effets que ceux de la médiane avec la formule : =MEDIANE(SI((bdd!$A$2:$A$69=nuage!$O$7)*(bdd!$B$2:$B$69=nuage!$O$8);bdd!$C$2:$C$69)), effets dont j'ai besoin.

Savez-vous quelle serait la syntaxe en couplant la fonction DROITEREG avec la fonction SI?
Je suis quasi certaine d'obtenir le résultat escompté si je parviens à associer les deux.

Merci à vous

Cordialement

 

[lili28]

En complément à mon précédant message : j'ai bien compris que la fonction DROITEREG doit travailler sur des cellules numériques jointives.
En triant les données correctement dans l'onglet BDD, l'association DROITEREG et SI est peut-être possible?

Merci
Cordialement

 

[job75]

Je vous ai donné 3 solutions, ça suffit amplement.

Si vous n'aimez pas le VBA il faudra vous y faire car c'est élémentaire.

 

[lili28]

Bonjour JOB75,

C'est noté.

Je vous remercie de votre disponibilité et de vos retours (je m'aperçois que la dernière solution propose la création d'un onglet "filtre" que je n'avais pas vu. Cela aurait été la bonne solution si étaient rapatriées toutes les colonnes de l'onglet source y compris les formules présentes dans les cellules).

Cordialement

 

[job75]

Bonjour lili28,

je m'aperçois que la dernière solution propose la création d'un onglet "filtre" que je n'avais pas vu. Cela aurait été la bonne solution si étaient rapatriées toutes les colonnes de l'onglet source y compris les formules présentes dans les cellules

Bah l'onglet "Filtre" sert à définir les plages "X" et "Y" mais si vous voulez récupérer les 7 colonnes pas de problème, voyez ce fichier (4).

A+

 

[job75]

Le fichier (4) s'était vérolé, je viens de le remplacer.

 

[lili28]

Un grand merci à vous JOB75! Il me reste maintenant à reporter tout cela dans mon fichier de travail!

Juste une petite précision : j'ai ouvert le fichier lors de votre 1er post du jour. Qd vous dites vérolé, vous entendez erreur?

 

[job75]

Non, une vérole assez classique : une feuille supplémentaire de type ThisWorlbook s'était glissée dans le VBA.