Autres [Résolu] Excel 2024 : Regrouper racine nom en un seul nom

kdet · Vendredi à 17:12

Bonjour à tous, le forum,

J'aurai besoin de votre aide svp. J'utilise Excel 2024 LTSC. j'ai utilisé cette formule en A2 qui est un peu longue désolé :

"=CHOISIR({1.2.3.4.5.6}; UNIQUE(TblSaisieFactures[FOURNISSEURS]); SOMME.SI.ENS(TblSaisieFactures[MONTANT]; TblSaisieFactures[FOURNISSEURS]; UNIQUE(TblSaisieFactures[FOURNISSEURS]) & "*"; TblSaisieFactures[DATE]; ">="&DATE(B$1;1;1); TblSaisieFactures[DATE]; "<="&DATE(B$1;12;31)); SOMME.SI.ENS(TblSaisieFactures[MONTANT]; TblSaisieFactures[FOURNISSEURS]; UNIQUE(TblSaisieFactures[FOURNISSEURS]) & "*"; TblSaisieFactures[DATE]; ">="&DATE(C$1;1;1); TblSaisieFactures[DATE]; "<="&DATE(C$1;12;31)); SOMME.SI.ENS(TblSaisieFactures[MONTANT]; TblSaisieFactures[FOURNISSEURS]; UNIQUE(TblSaisieFactures[FOURNISSEURS]) & "*"; TblSaisieFactures[DATE]; ">="&DATE(D$1;1;1); TblSaisieFactures[DATE]; "<="&DATE(D$1;12;31)); SOMME.SI.ENS(TblSaisieFactures[MONTANT]; TblSaisieFactures[FOURNISSEURS]; UNIQUE(TblSaisieFactures[FOURNISSEURS]) & "*"; TblSaisieFactures[DATE]; ">="&DATE(E$1;1;1); TblSaisieFactures[DATE]; "<="&DATE(E$1;12;31)); SOMME.SI.ENS(TblSaisieFactures[MONTANT]; TblSaisieFactures[FOURNISSEURS]; UNIQUE(TblSaisieFactures[FOURNISSEURS]) & "*"; TblSaisieFactures[DATE]; ">="&DATE(F$1;1;1); TblSaisieFactures[DATE]; "<="&DATE(F$1;12;31)))"

Dans mon fichier joint, colonne H et I, par quelle formule peut-on regrouper les fournisseurs qui ont les mêmes racines et de l'inclure dans la formule ci-dessus?

Vous remerciant par avance de votre aimable collaboration.

kdet

kdet · Vendredi à 20:41

job75 à dit:

Bonsoir kdet, le forum,

Pas trop compris ce que vous voulez faire mais voici une solution VBA avec filtrage par liste de validation, le code de la feuille :

VB:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("H2:H" & Rows.Count).ClearContents 'RAZ
If Intersect(Target, UsedRange(1)) Is Nothing Then Exit Sub
Dim d As Object, tablo, i&, s
Set d = CreateObject("Scripting.Dictionary")
tablo = [A1].CurrentRegion
For i = 3 To UBound(tablo)
    s = Split(tablo(i, 1))
    If UBound(s) > -1 Then d(UCase(s(0))) = ""
Next i
If d.Count = 0 Then Exit Sub
With [H2].Resize(d.Count)
    .Value = Application.Transpose(d.keys) 'Transpose est limitée à 65536 lignes
    .Sort .Cells, xlAscending, Header:=xlNo 'tri alphabétique
    .Name = "Liste"
End With
'---liste de validation---
[A1].Validation.Delete
[A1].Validation.Add xlValidateList, Formula1:="=Liste"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [A1]) Is Nothing Then [A1].CurrentRegion.Offset(1).AutoFilter 1, [A1] & "*" 'filtre automatique
End Sub

Masquez la colonne H.

A+

Re-bonjour job75, le forum,

Merci pour la version macro. mais comment l'intégrer dans mon fichier joint

kdet

kdet · Vendredi à 20:43

Hecatonchire à dit:
Voila ce que donne ma formule
(quelque problème au niveau des dates, manquantes ou erronées et les racines semblent fonctionner)

Regarde la pièce jointe 1228197

Re-bonjour Hecatonchire,

Je te remercie pour ton retour sauf que je n'ai pas la fonction PIVOTER.PAR.

kdet

Cousinhub · Vendredi à 20:59

Bonsoir,
Tu ne lis qu'une réponse sur deux....
Dommage...
Pour info, sans tableau de correspondance, ce que tu souhaites est tout simplement impossible, au vu des associations voulues
Bon courage

Hecatonchire · Vendredi à 21:28

C'est vrai qu'elle est récente mais Microsoft l'indique comme disponible pour 2024

Fonction PIVOTER.PAR - Support Microsoft

support.microsoft.com

kdet · Vendredi à 22:09

Hecatonchire à dit:
C'est vrai qu'elle est récente mais Microsoft l'indique comme disponible pour 2024
Regarde la pièce jointe 1228199

Fonction PIVOTER.PAR - Support Microsoft

support.microsoft.com

je devrais donc mettre à jour mon office.

Merci
kdet

Hecatonchire · Vendredi à 22:33

Cousinhub à dit:
Au vu des données, et des "racines", j'ai bien peur qu'un "Fractionner.Texte" ne soit 100% compatible...

Je ne prend que le 1er, la "racine", les autres étages j'ignore (en attente d'infos contradictoires de kdet)

Nain porte quoi à dit:
@Hecatonchire comment vous retrouvez les colonnes qui manquent DATE et MONTANT dans le tableau qui manque lui aussi ?

J'ai fais preuve d'imagination 😁

@kdet

Une version sans PIVOTER (attention en terme de performances !)

=LET(mfs;INDEX(FRACTIONNER.TEXTE(TblSaisieFactures[FOURNISSEURS];" ");;1);
mf;TRIER(UNIQUE(mfs));
mas;SIERREUR(ANNEE(TblSaisieFactures[DATE]);1900);
ma;TRIER(UNIQUE(mas));
smt;MAKEARRAY(NBVAL(mf);NBVAL(ma);
LAMBDA(l;c;SOMME(TblSaisieFactures[MONTANT]*(mas=INDEX(ma;c))*(mfs=INDEX(mf;l)))
));
ASSEMB.H(ASSEMB.V("";mf);ASSEMB.V(TRANSPOSE(ma);smt)))

Nain porte quoi · Vendredi à 22:47

La version avec TCD et ajout d'une colonne pour récupérer la partie gauche du nom du fournisseur (ce qui ne donne pas un résultat correct, mais à moins de faire un tableau de correspondance ça me semble la seule solution)

Edit, pour pouvoir regrouper les années j'ai du modifié mettre des dates là où elle manquait et corriger celles qui sont invalides

kdet · Hier à 05:12

Hecatonchire à dit:
Je ne prend que le 1er, la "racine", les autres étages j'ignore (en attente d'infos contradictoires de kdet)

J'ai fais preuve d'imagination 😁

@kdet

Une version sans PIVOTER (attention en terme de performances !)

=LET(mfs;INDEX(FRACTIONNER.TEXTE(TblSaisieFactures[FOURNISSEURS];" ");;1);
mf;TRIER(UNIQUE(mfs));
mas;SIERREUR(ANNEE(TblSaisieFactures[DATE]);1900);
ma;TRIER(UNIQUE(mas));
smt;MAKEARRAY(NBVAL(mf);NBVAL(ma);
LAMBDA(l;c;SOMME(TblSaisieFactures[MONTANT]*(mas=INDEX(ma;c))*(mfs=INDEX(mf;l)))
));
ASSEMB.H(ASSEMB.V("";mf);ASSEMB.V(TRANSPOSE(ma);smt)))

@Hecatonchire, le forum,

Merci pour ta contribution malgré la complexité de ma première formule, comme disait @Nain porte quoi, tu as pu la réduire avec la combinaison de LET, TRIER, LAMBDA, ASSEMBLER.H et ASSEMBLER.V

Kdet

kdet · Hier à 05:23

Nain porte quoi à dit:
La version avec TCD et ajout d'une colonne pour récupérer la partie gauche du nom du fournisseur (ce qui ne donne pas un résultat correct, mais à moins de faire un tableau de correspondance ça me semble la seule solution)

Edit, pour pouvoir regrouper les années j'ai du modifié mettre des dates là où elle manquait et corriger celles qui sont invalides
Regarde la pièce jointe 1228201

@Nain porte quoi , le forum,

D'après les remarques faites par @Cousinhub , je devrais revoir ma base car il y a effectivement un surplus d'espace, des cellules vides dans la colonne DATE.

Merci pour ton retour,
Kdet

job75 · Hier à 10:00

Bonjour kdet, le forum,

J'ai ouvert votre fichier du post #16 et je vois que le problème est très simple et se résout facilement par formules.

1) Dans la feuille "Base Factures" j'ai ajouté les 2 colonnes RACINE et ANNEE, les formules sont évidentes.

2) Dans la feuille "RECAPITUL" formule matricielle en A2 :

Code:

=SIERREUR(INDEX('Base Factures'!D:D;PETITE.VALEUR(SI(NON(NB.SI(A$1:A1;TblSaisieFactures[RACINE]));LIGNE(TblSaisieFactures));1));"")

En effet ma version Excel n'a pas la fonction UNIQUE.

3) Dans la feuille "RECAPITUL" formule normale en B2 à tirer vers la droite et le bas :

Code:

=SOMME.SI.ENS(TblSaisieFactures[[MONTANT]:[MONTANT]];TblSaisieFactures[[RACINE]:[RACINE]];$A2;TblSaisieFactures[[ANNEE]:[ANNEE]];--B$1)

Les références des colonnes sont doublées pour ne pas s'incrémenter quand on tire vers la droite.

Au cas où les années en ligne 1 seraient des textes, j'ai mis 2 tirets -- pour les convertir en nombres.

A+

kdet · Hier à 10:12

job75 à dit:
Bonjour kdet, le forum,

J'ai ouvert votre fichier du post #16 et je vois que le problème est très simple et se résout facilement par formules.

1) Dans la feuille "Base Factures" j'ai ajouté les 2 colonnes RACINE et ANNEE, les formules sont évidentes.

2) Dans la feuille "RECAPITUL" formule matricielle en A2 :

Code:

=SIERREUR(INDEX('Base Factures'!D:D;PETITE.VALEUR(SI(NON(NB.SI(A$1:A1;TblSaisieFactures[RACINE]));LIGNE(TblSaisieFactures));1));"")

En effet ma version Excel n'a pas la fonction UNIQUE.

3) Dans la feuille "RECAPITUL" formule normale en B2 à tirer vers la droite et le bas :

Code:

=SOMME.SI.ENS(TblSaisieFactures[[MONTANT]:[MONTANT]];TblSaisieFactures[[RACINE]:[RACINE]];$A2;TblSaisieFactures[[ANNEE]:[ANNEE]];--B$1)

Les références des colonnes sont doublées pour ne pas s'incrémenter quand on tire vers la droite.

Au cas où les années en ligne 1 seraient des textes, j'ai mis 2 tirets -- pour les convertir en nombres.

A+

Bonjour job75, le forum,

Je vous remercie de votre collaboration.
J'ai dû réctifier ma base avec les espaces en surplus et les dates vides et j'ai adapté vos formules avec. Tout va à merveille.
Encore encore à toi, le forum.
kdet

Cousinhub · Hier à 10:14

Hecatonchire à dit:
C'est vrai qu'elle est récente mais Microsoft l'indique comme disponible pour 2024
Regarde la pièce jointe 1228199

Fonction PIVOTER.PAR - Support Microsoft

support.microsoft.com

Bonjour,
Hélas, 2024 ne dispose pas de cette fonction...
Et le site de µSoft indique tout et n'importe quoi...
Il vaut mieux regarder par ici
Bonne journée

job75 · Hier à 10:53

Je viens de me rendre compte que même si les dates en B1:F1 de la feuille 'RECAPITUL" sont des textes il est inutile de les convertir.

Prenez donc ce fichier, j'ai enlevé les --.

kdet · Hier à 13:43

Cousinhub à dit:
Bonjour,
Hélas, 2024 ne dispose pas de cette fonction...
Et le site de µSoft indique tout et n'importe quoi...
Il vaut mieux regarder par ici
Bonne journée

@Cousinhub , le forum,

Merci pour l'info.
A+
kdet

kdet · Hier à 14:22

job75 à dit:
Je viens de me rendre compte que même si les dates en B1:F1 de la feuille 'RECAPITUL" sont des textes il est inutile de les convertir.

Prenez donc ce fichier, j'ai enlevé les --.

@job75 , le forum,

J'en prends bonne note.
Encore merci à toi, le forum
Kdet

Autres [Résolu] Excel 2024 : Regrouper racine nom en un seul nom

XLDnaute Occasionnel

Pièces jointes

XLDnaute Occasionnel

Pièces jointes

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Occasionnel

Pièces jointes

XLDnaute Occasionnel

XLDnaute Occasionnel

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Occasionnel

XLDnaute Occasionnel

Discussions similaires

Privacy & Transparency

Privacy & Transparency