Autres Problème avec le #NA

[kdet]

Bonjour à tous, le forum,

J'utilise Excel 2024 (absence de la fonction GROUPER.PAR)

Je sollicite votre aide car suis confronté à un problème de #NA et que je n'arrive pas à trouver la solution.
J'ai un tableau contenant 5 fournisseurs avec des produits, prix, quantité, remise et montant. J'aimerai regrouper par Fournisseurs ou Produits tout en faire apparaitre le montant minimum excluant le "0" ou "vide".

je joints mon fichier avec le résultat attendu.
Je vous remercie d'avance de votre aimable collaboration.

kdet

 

[Dand87]

Bonjour,
Toute une formule! Avec l'énoncé LET, j'ai appelé le résultat, "Résultat". Ça permet d'afficher les différentes variables. Ici, on voit que la variable Montant ne fonctionne pas. On débogue étape par étape...

 

[fanfan38]

Bonjour
Ci joint ma solution

A+ François

 

[kdet]

Bonjour
Ci joint ma solution

A+ François

Bonjour fanfan38, le forum,

Je te remercie pour ta collaboration. Le résultat est là
kdet

 

[kdet]

Bonjour,
Toute une formule! Avec l'énoncé LET, j'ai appelé le résultat, "Résultat". Ça permet d'afficher les différentes variables. Ici, on voit que la variable Montant ne fonctionne pas. On débogue étape par étape...

Bonjour Dand87, le forum,

Merci pour ton retour, je vais décortiquer la variable Montant.
Kdet

 

[Dand87]

Bonjour Dand87, le forum,

Merci pour ton retour, je vais décortiquer la variable Montant.
Kdet

Bonjour, j'ai pensé à une solution et c'est une autre manière d'obtenir le résultat.
Voir ligne 24 pour le résultat. Les formules sont exposées en I30 et J30.

Voilà!

 

[job75]

Bonjour kdet, le forum,

Une solution VBA qui utilise le filtre avancé :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dest As Range
Set dest = [I2:M2]
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
dest.CurrentRegion.Offset(1).Clear 'RAZ
[A2].CurrentRegion.AdvancedFilter xlFilterCopy, CopyToRange:=dest 'filtre avancé copié
With dest.CurrentRegion
    .Offset(1).Interior.ColorIndex = xlNone 'efface les couleurs
    .Columns(2).Replace 0, "", xlWhole 'efface les montants nuls
    .Sort .Columns(1), xlAscending, .Columns(2), , xlAscending, Header:=xlYes '1er tri sur 2 colonnes
    .Columns(2).Replace "", 0 'éventuellement remet 0 dans les cellules vides
    .RemoveDuplicates Columns:=1, Header:=xlYes 'supprime les doublons
    .Sort .Columns(2), xlAscending, Header:=xlYes '2ème tri pour classer les montants
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

La macro est dans le code de la feuille et se déclenche quand on modifie ou valide une cellule quelconque.

A+

 

[Dand87]

Bonjour,

J'ai testé des autres solutions dont une (Résultat 3) avec une formule pour le tout.

 

[kdet]

Bonjour,

J'ai testé des autres solutions dont une (Résultat 3) avec une formule pour le tout.

Bonsoir Dand87, le forum,

Désolé pour le retour d'information.
Merci pour ta contribution. J'ai bien examiner tes propositions de solutions et c'est le résultat 3 qui répond à ma demande : TRIER(CHOISIRCOLS(FILTRE(Table1; MAP(Table1[DESIGNATIONS]; Table1[MONTANT]; LAMBDA(Desig;Montant; ET(Montant>0; Montant=MIN.SI.ENS(Table1[MONTANT]; Table1[DESIGNATIONS]; Desig; Table1[MONTANT]; ">0")))));2;6;1;3;5);2)

Encore merci à toi

kdet

 

[kdet]

Bonsoir job75, le forum,

Bonjour kdet, le forum,

Une solution VBA qui utilise le filtre avancé :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dest As Range
Set dest = [I2:M2]
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
dest.CurrentRegion.Offset(1).Clear 'RAZ
[A2].CurrentRegion.AdvancedFilter xlFilterCopy, CopyToRange:=dest 'filtre avancé copié
With dest.CurrentRegion
    .Offset(1).Interior.ColorIndex = xlNone 'efface les couleurs
    .Columns(2).Replace 0, "", xlWhole 'efface les montants nuls
    .Sort .Columns(1), xlAscending, .Columns(2), , xlAscending, Header:=xlYes '1er tri sur 2 colonnes
    .Columns(2).Replace "", 0 'éventuellement remet 0 dans les cellules vides
    .RemoveDuplicates Columns:=1, Header:=xlYes 'supprime les doublons
    .Sort .Columns(2), xlAscending, Header:=xlYes '2ème tri pour classer les montants
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

La macro est dans le code de la feuille et se déclenche quand on modifie ou valide une cellule quelconque.

A+

Encore désolé pour le retour d'information.
Merci pour ta solution en VBA qui répond aussi à mon attente. Juste une question : serait-il possible de recopier ou reproduire le format et la mise en forme du tableau initial pour le tableau de résultat?

Merci
kdet

 

[job75]

Bonsoir kdet,

Juste une question : serait-il possible de recopier ou reproduire le format et la mise en forme du tableau initial pour le tableau de résultat?

Vous pouvez dans la macro neutraliser la ligne :

.Offset(1).Interior.ColorIndex = xlNone 'efface les couleurs

mais vous verrez que le résultat n'est pas fameux (l'alternance des couleurs n'est pas respectée).

Pour créer le tableau des résultats avec des bandes alternées il faut compléter la macro :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim dest As Range, mem
Set dest = [I2:M2]
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
dest.CurrentRegion.Offset(1).Clear 'RAZ
[A2].CurrentRegion.AdvancedFilter xlFilterCopy, CopyToRange:=dest 'filtre avancé copié
With dest.CurrentRegion
    .Offset(1).Interior.ColorIndex = xlNone 'efface les couleurs
    .Columns(2).Replace 0, "", xlWhole 'efface les montants nuls
    .Sort .Columns(1), xlAscending, .Columns(2), , xlAscending, Header:=xlYes '1er tri sur 2 colonnes
    .Columns(2).Replace "", 0 'éventuellement remet 0 dans les cellules vides
    .RemoveDuplicates Columns:=1, Header:=xlYes 'supprime les doublons
    With .CurrentRegion 'nécessaire après la suppression des doublons
        .Sort .Columns(2), xlAscending, Header:=xlYes '2ème tri pour classer les montants
        '---bandes alternées---
        mem = .Columns(1) 'mémorise
        If .Row Mod 2 Then .Columns(1) = "=1/(MOD(ROW(),2)=0)" Else .Columns(1) = "=1/(MOD(ROW(),2))"
        Intersect(.Columns(1).SpecialCells(xlCellTypeFormulas, 1).EntireRow, .Cells).Interior.Color = RGB(217, 217, 217) 'gris
        .Columns(1) = mem 'restitue les valeurs
    End With
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

A+

 

[job75]

Bonjour kdet, le forum,

On peut aussi utiliser 2 tableaux structurés :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim source As Range, dest As Range, nom$
Set source = [Tableau1].ListObject.Range 'tableau structuré
Set dest = [Tableau2].ListObject.Range 'tableau structuré
nom = dest.ListObject.Name
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
On Error Resume Next 'si le DataBodyRange n'existe pas ou s'il n'y aucune SpecialCell
dest.ListObject.DataBodyRange.Delete 'RAZ
dest.ListObject.Unlist 'convertit le tableau en plage
source.AdvancedFilter xlFilterCopy, CopyToRange:=dest.Rows(1) 'filtre avancé copié
source.AutoFilter 'remet en place le filtre automatique
With dest.Resize(source.Rows.Count)
    .Columns(2).Replace 0, "", xlWhole 'efface les montants nuls
    .Sort .Columns(1), xlAscending, .Columns(2), , xlAscending, Header:=xlYes '1er tri sur 2 colonnes
    .Columns(2).Replace "", 0 'éventuellement remet 0 dans les cellules vides
    .RemoveDuplicates Columns:=1, Header:=xlYes 'supprime les doublons
    Intersect(.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow, .Cells).Delete xlUp 'supprime les lignes vides
    .Sort .Columns(2), xlAscending, Header:=xlYes '2ème tri pour classer les montants
    Me.ListObjects.Add(xlSrcRange, .Cells, , xlYes).Name = nom 'recrée le tableau structuré
    Me.ListObjects(2).TableStyle = Me.ListObjects(1).TableStyle 'copie le style
End With
Application.EnableEvents = True 'réactive les évènements
End Sub

J'ai découvert que la conversion en plage du 2ème tableau était nécessaire.

A+