Formule pour trier plusieurs colonnes
ria55
Messages postés
17
Date d'inscription
Statut
Membre
Dernière intervention
-
ria55 Messages postés 17 Date d'inscription Statut Membre Dernière intervention -
ria55 Messages postés 17 Date d'inscription Statut Membre Dernière intervention -
Bjr. Je veux faire quelque chose en Excel mais je m'embrouille.
Jai eu la chance de tomber sur une discusion entre les membres manubeni et via55 qui m'a aidé à faire beaucoup de chose. Voici lien : https://forums.commentcamarche.net/forum/affich-30284015-realiser-un-regroupement-de-donnees-en-excel#newanswer (pour les besoins j'ai adopté les mêmes noms pour les colonnes)
Mais mon cas nécessite quelques adaptations.
Dans la colonne J j'ai trié les noms.
Voilà mon problème :
Dans la colonne K je dois avoir les mises correspondant à chaque personne avant de faire les autres choses. Pour trouver la nouvelle mise dans la colonne K, on doit considerer les colonnes B, F et J.
Dans colonne F se trouve les anciennes mises.
(Pour chaque numero dans la colonne B correspond une mise dans la colonne F)
Par exemple pour la feuille teste1.
- Pour la personne dans la colonne J2, sa mise sera 500 car de B2 à B7 on a que « 1 » dans toutes les colonnes.
- Mais si par exemple on trouve le meme nom aillers et que le contenu de la colonne B est different de « 1 », on notera la nouvelle valeur de la colonne F dans la colonne K
Par exemple pour la selection de la colonne J21 à J35 il y a deux personnes. La deuxième personne se trouve seulement dans la colonne J32. Quand on prend la première personne qui occupe les colonne :J21 à J31 et J33 à J35, on doit noter les mises suivantes dans la colonne K : K21 -> 1000 ; K25 -> 1000 ; K26 -> 1000 ; K31 -> 500 et K33 -> 1000
En bref pour chaque personne (colonne J), qand le numero correspondant dans la colonne B change, on note la mise (colonne F) qui correspond à ce numéro dans la colonne K.
Merci pour votre aide.
Voici le fichier joint
https://www.cjoint.com/?3FxqcekCXJu
cordlmnt
Jai eu la chance de tomber sur une discusion entre les membres manubeni et via55 qui m'a aidé à faire beaucoup de chose. Voici lien : https://forums.commentcamarche.net/forum/affich-30284015-realiser-un-regroupement-de-donnees-en-excel#newanswer (pour les besoins j'ai adopté les mêmes noms pour les colonnes)
Mais mon cas nécessite quelques adaptations.
Dans la colonne J j'ai trié les noms.
Voilà mon problème :
Dans la colonne K je dois avoir les mises correspondant à chaque personne avant de faire les autres choses. Pour trouver la nouvelle mise dans la colonne K, on doit considerer les colonnes B, F et J.
Dans colonne F se trouve les anciennes mises.
(Pour chaque numero dans la colonne B correspond une mise dans la colonne F)
Par exemple pour la feuille teste1.
- Pour la personne dans la colonne J2, sa mise sera 500 car de B2 à B7 on a que « 1 » dans toutes les colonnes.
- Mais si par exemple on trouve le meme nom aillers et que le contenu de la colonne B est different de « 1 », on notera la nouvelle valeur de la colonne F dans la colonne K
Par exemple pour la selection de la colonne J21 à J35 il y a deux personnes. La deuxième personne se trouve seulement dans la colonne J32. Quand on prend la première personne qui occupe les colonne :J21 à J31 et J33 à J35, on doit noter les mises suivantes dans la colonne K : K21 -> 1000 ; K25 -> 1000 ; K26 -> 1000 ; K31 -> 500 et K33 -> 1000
En bref pour chaque personne (colonne J), qand le numero correspondant dans la colonne B change, on note la mise (colonne F) qui correspond à ce numéro dans la colonne K.
Merci pour votre aide.
Voici le fichier joint
https://www.cjoint.com/?3FxqcekCXJu
cordlmnt
A voir également:
- Formule pour trier plusieurs colonnes
- Formule moyenne excel plusieurs colonnes - Guide
- Trier colonne excel - Guide
- Formule excel pour additionner plusieurs cellules - Guide
- Formule si et - Guide
- Comment faire des colonnes sur word - Guide
8 réponses
Bonsoir ria
Si j'ai bien compris ce que tu veux, càd inscrire la mise quand le nom de la personne change OU lorsque le n° en B change, en K2 mettre :
=SI(OU(J2<>J1;B2<>B1);F2;"") à étirer vers le bas ensuite (double clic sur carré noir en bas à droite de la cellule)
mais ATTENTION, avant il faut vérifier tous les noms de la colonne D, il faut impérativement qu'ils soient toujours orthographiés pareil pour que la formule fonctionne; ce qui n'est pas le cas par exemple pour AWOUSSI Kossi qui est tantôt écrit avec un espace entre le nom et le prénom, tantôt avec 2 espaces
Cdlmnt
Si j'ai bien compris ce que tu veux, càd inscrire la mise quand le nom de la personne change OU lorsque le n° en B change, en K2 mettre :
=SI(OU(J2<>J1;B2<>B1);F2;"") à étirer vers le bas ensuite (double clic sur carré noir en bas à droite de la cellule)
mais ATTENTION, avant il faut vérifier tous les noms de la colonne D, il faut impérativement qu'ils soient toujours orthographiés pareil pour que la formule fonctionne; ce qui n'est pas le cas par exemple pour AWOUSSI Kossi qui est tantôt écrit avec un espace entre le nom et le prénom, tantôt avec 2 espaces
Cdlmnt
Bjr Mr via55. merci beaucoup pour cette aide.
c'est cela la logique. merci
mais permettez de vous posez 2 questions svp:
1-N'y a t-il pas un moyen pour revoir l'orthographe des noms càd les uniformisé pour avoir seulement un seul espace entre les noms et les prénoms... si oui ça m'aiderait beaucoup
2-N'y a t-il pas une formule pour compter le nombre de fois qu'un nom apparait sur chaque feuille ou si possible le nombre de fois qu'un nom apparait sur toutes les feuilles du classeur.
Merci une fois encore pour votre assistance.
c'est cela la logique. merci
mais permettez de vous posez 2 questions svp:
1-N'y a t-il pas un moyen pour revoir l'orthographe des noms càd les uniformisé pour avoir seulement un seul espace entre les noms et les prénoms... si oui ça m'aiderait beaucoup
2-N'y a t-il pas une formule pour compter le nombre de fois qu'un nom apparait sur chaque feuille ou si possible le nombre de fois qu'un nom apparait sur toutes les feuilles du classeur.
Merci une fois encore pour votre assistance.
re,
1) tu peux utiliser le Rechercher Remplacer (Ctrl+H) pour rechercher un nom prénom écrit avec 2 espaces et le remplacer par le même nom prénom avec un seul espace
2) =NB.SI("teste1!D:D";"AWOUSSI Kossi") pour compter le nombre de fois où ce nom revient dans la colonne D de la feuille teste1
Suffit ensuite d'ajouter les NB.SI de chaque feuille pour avoir le nombre total
Cdlmnt
1) tu peux utiliser le Rechercher Remplacer (Ctrl+H) pour rechercher un nom prénom écrit avec 2 espaces et le remplacer par le même nom prénom avec un seul espace
2) =NB.SI("teste1!D:D";"AWOUSSI Kossi") pour compter le nombre de fois où ce nom revient dans la colonne D de la feuille teste1
Suffit ensuite d'ajouter les NB.SI de chaque feuille pour avoir le nombre total
Cdlmnt
Bjr Mr
merci pour votre aide.
svp jai 2 inquiétude:
1-la formule pour compter ne marche pas....
=NB.SI("teste1!D:D";"AWOUSSI Kossi")
2-pour eviter de faire beaucoup de changement dans mon code, jai adapté mon cas à votre code. jai donc créé une feuille RECAP sur laquelle il y a les nom en colonne A, mise en colonne B et les montants en C.
mais au cours de la compilation je remarque que certains noms n'apparaissent pas sur ma feuille "recap" svp est ce que vous aurez une idée? svp
le fichier join est disponible
voici le code:
Sub recap1()
'desactive rafraissement écran
Application.ScreenUpdating = False
' boucle sur les feuilles depuis la 2eme jusqu'à la dernière
For N = 2 To Sheets.Count
'selectionne la feuille
Sheets(N).Select
' dernière ligne non vide de la feuille
DL = Range("J" & Rows.Count).End(xlUp).Row
' boucle de la 2eme ligne à la dernière non vide de la feuille
For x = 2 To DL
' récup le nom en D
nom = Sheets(N).Range("J" & x).Value
' derniere ligne non vide de la feuille RECAP
DLR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
' recherche du nom dans RECAP
resultat = Application.VLookup(nom, Sheets(1).Range("A2:A" & DLR), 1, False)
' si non trouvé la ligne où écrire sera la 1ere après la derniere non vide
'sinon on recherche en quel ligne est le nom dans la colonne A de RECAP
If (IsError(resultat)) Then ligne = DLR + 1 Else ligne = Application.WorksheetFunction.Match(nom, Sheets(1).Range("A:A"), False)
'Inscription dans RECAP du nom en colonne A et addition des montants en colonne B
With Sheets(1)
.Range("A" & ligne) = nom
.Range("B" & ligne) = .Range("B" & ligne).Value + Sheets(N).Range("K" & x)
.Range("C" & ligne) = .Range("C" & ligne).Value + Sheets(N).Range("G" & x)
End With
Next x
Next N
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
merci pour votre aide.
svp jai 2 inquiétude:
1-la formule pour compter ne marche pas....
=NB.SI("teste1!D:D";"AWOUSSI Kossi")
2-pour eviter de faire beaucoup de changement dans mon code, jai adapté mon cas à votre code. jai donc créé une feuille RECAP sur laquelle il y a les nom en colonne A, mise en colonne B et les montants en C.
mais au cours de la compilation je remarque que certains noms n'apparaissent pas sur ma feuille "recap" svp est ce que vous aurez une idée? svp
le fichier join est disponible
voici le code:
Sub recap1()
'desactive rafraissement écran
Application.ScreenUpdating = False
' boucle sur les feuilles depuis la 2eme jusqu'à la dernière
For N = 2 To Sheets.Count
'selectionne la feuille
Sheets(N).Select
' dernière ligne non vide de la feuille
DL = Range("J" & Rows.Count).End(xlUp).Row
' boucle de la 2eme ligne à la dernière non vide de la feuille
For x = 2 To DL
' récup le nom en D
nom = Sheets(N).Range("J" & x).Value
' derniere ligne non vide de la feuille RECAP
DLR = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
' recherche du nom dans RECAP
resultat = Application.VLookup(nom, Sheets(1).Range("A2:A" & DLR), 1, False)
' si non trouvé la ligne où écrire sera la 1ere après la derniere non vide
'sinon on recherche en quel ligne est le nom dans la colonne A de RECAP
If (IsError(resultat)) Then ligne = DLR + 1 Else ligne = Application.WorksheetFunction.Match(nom, Sheets(1).Range("A:A"), False)
'Inscription dans RECAP du nom en colonne A et addition des montants en colonne B
With Sheets(1)
.Range("A" & ligne) = nom
.Range("B" & ligne) = .Range("B" & ligne).Value + Sheets(N).Range("K" & x)
.Range("C" & ligne) = .Range("C" & ligne).Value + Sheets(N).Range("G" & x)
End With
Next x
Next N
Sheets(1).Select
Application.ScreenUpdating = True
End Sub
1-voici un fichier de test
https://www.cjoint.com/?3Fznxsn1DlJ
quand jai compilé, le nom "lANGUEH Amélé" par exemple n'apparait pas dans la feuille RECAP1 mais ce nom se trouve dans les colonne J204 à J224 de la feuille test 1
2- svp cett formule pour compter le nombre de fois qu'une personne apparait sur la feuille ne marche pas
=NB.SI("test 1!D:D";"TCHALA MADELAINE")
merci beaucoup pour votre aide
https://www.cjoint.com/?3Fznxsn1DlJ
quand jai compilé, le nom "lANGUEH Amélé" par exemple n'apparait pas dans la feuille RECAP1 mais ce nom se trouve dans les colonne J204 à J224 de la feuille test 1
2- svp cett formule pour compter le nombre de fois qu'une personne apparait sur la feuille ne marche pas
=NB.SI("test 1!D:D";"TCHALA MADELAINE")
merci beaucoup pour votre aide
1) Pour supprimer facilement tous les espaces inutiles :
Formule à mettre en J2 de test 1 et à étirer vers le bas
=SI(D2="";J1;SUPPRESPACE(D2))
Faire la même chose pour Test 2
2) Formule de décompte qui fonctionne :
mettre en F3 de Recap :=NB.SI('test 1'!J:J;F2) (attention le nom de la feuille est entre apostrophe et non entre guillemets)
et entrer le nom à compter en F2; avec TCHALA MADELAINE cela me donne 26 par ex
lANGUEH Amélé avec un Lminuscule en début est la même chose que LANGUEH Amélé avec un L majuscule, Excel ne fait pas dans la macro entre minuscules et majuscules; tout est donc comptabilisé sous le nom LANGUEH et lANGUEH n'apparaît pas c'est normal. Par contre si c'était IANGUEH commençant par un I majuscule (dont on ne fait pas la différence avec le L minuscule dans la police Arial ) ce serait bien en compte pour 2 noms différents
Donc ATTENTION à uniformiser les noms dans test 1, il y en a plein écrit différemment et qui doivent concerner la même personne on trouve par ex :
LANGUEH AMELE et LANGUEH Amélé qui sont 2 noms différents pour excel du fait des accents sur le prénom
mais aussi :
ADANOU Affiyo et ADANOU AFIYO
ou ADEDEDJE Yawa Clara ,ADEDJE YAWA ,ADEDJE YAWA C et ADEDJE Yaya Glara !!
Ton fichier est peu utilisable en l'état, quand tu as fait le récap fait un tri alphabétique sur la colonne des noms et tu verras toutes les redondances !
Pour accélérer la macro tu peux désactiver le calcul automatique en mettant cette ligne après celle qui désactive déjà le rafraichissement écran :
Application.Calculation = xlCalculationManual
sans oublier avant le End sub de rajouter celle-là pour réactiver le calcul auto :
Application.Calculation =xlCalculationAutomatic
Bonne suite
Cdlmnt
Formule à mettre en J2 de test 1 et à étirer vers le bas
=SI(D2="";J1;SUPPRESPACE(D2))
Faire la même chose pour Test 2
2) Formule de décompte qui fonctionne :
mettre en F3 de Recap :=NB.SI('test 1'!J:J;F2) (attention le nom de la feuille est entre apostrophe et non entre guillemets)
et entrer le nom à compter en F2; avec TCHALA MADELAINE cela me donne 26 par ex
lANGUEH Amélé avec un Lminuscule en début est la même chose que LANGUEH Amélé avec un L majuscule, Excel ne fait pas dans la macro entre minuscules et majuscules; tout est donc comptabilisé sous le nom LANGUEH et lANGUEH n'apparaît pas c'est normal. Par contre si c'était IANGUEH commençant par un I majuscule (dont on ne fait pas la différence avec le L minuscule dans la police Arial ) ce serait bien en compte pour 2 noms différents
Donc ATTENTION à uniformiser les noms dans test 1, il y en a plein écrit différemment et qui doivent concerner la même personne on trouve par ex :
LANGUEH AMELE et LANGUEH Amélé qui sont 2 noms différents pour excel du fait des accents sur le prénom
mais aussi :
ADANOU Affiyo et ADANOU AFIYO
ou ADEDEDJE Yawa Clara ,ADEDJE YAWA ,ADEDJE YAWA C et ADEDJE Yaya Glara !!
Ton fichier est peu utilisable en l'état, quand tu as fait le récap fait un tri alphabétique sur la colonne des noms et tu verras toutes les redondances !
Pour accélérer la macro tu peux désactiver le calcul automatique en mettant cette ligne après celle qui désactive déjà le rafraichissement écran :
Application.Calculation = xlCalculationManual
sans oublier avant le End sub de rajouter celle-là pour réactiver le calcul auto :
Application.Calculation =xlCalculationAutomatic
Bonne suite
Cdlmnt
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
merci beaucoup Mr via
j'ai essayé et tout à l'air d'aller bien... merci surtout pour la technique de suppression d'espace inutiles.
svp j'aimerais vous demander quelque chose.
A propos de la formule de décompte =NB.SI('test 1'!J:J;F2)
je veux afficher sur la feuille RECAP le nombre de fois que chaque personne a été saisie devant le nom de la personne .
est ce qu'il y a la possibilité de le faire.
merci.
crdlmnt
j'ai essayé et tout à l'air d'aller bien... merci surtout pour la technique de suppression d'espace inutiles.
svp j'aimerais vous demander quelque chose.
A propos de la formule de décompte =NB.SI('test 1'!J:J;F2)
je veux afficher sur la feuille RECAP le nombre de fois que chaque personne a été saisie devant le nom de la personne .
est ce qu'il y a la possibilité de le faire.
merci.
crdlmnt
Oui c'est possible
mais
1) si tu rajoutes une colonne avant les noms ce sera la colonne A les noms seront en B, le reste en C et D il faut donc modifier toutes les references dans la macro concernant la feuille Recap, changer les A en B , les B en C etc
2) si tu veux compter le nombre de fois dans les 2 feuilles test 1 et test 2 il faut adapter la formule ainsi en A2 de Recap :
=NB.SI('test 1'!J:J;B2) +NB.SI('test 2'!J:J;B2)
Cdlmnt
mais
1) si tu rajoutes une colonne avant les noms ce sera la colonne A les noms seront en B, le reste en C et D il faut donc modifier toutes les references dans la macro concernant la feuille Recap, changer les A en B , les B en C etc
2) si tu veux compter le nombre de fois dans les 2 feuilles test 1 et test 2 il faut adapter la formule ainsi en A2 de Recap :
=NB.SI('test 1'!J:J;B2) +NB.SI('test 2'!J:J;B2)
Cdlmnt
bjr
svp jai un souci au nivau de cette formule en K2 : =SI(OU(J2<>J1;B2<>B1);F2;0) la formule marche bien.
seulement il y a un cas que je n'arrive pas à ajouter.
c'est le cas où le nom d'une personne apparait dans plusieurs cellules mais ces cellules ne se suivent pas (colonne J)
mais avec les meme chiffres dans les cellules de la colonne B corresponantes
par exemple quand je selectionne le nom "AMEOGNINE DODJI" dans la colonne J sur la feuille "tetste 1" on a:
De la cellule K63 à K71 c'est bon car cest le meme chiffre (11) qui apparait dans les cellules B63 à B71, on prend
la mise une seule fois, ce qui correspond à la cellule K63 et les autres cest 0
dans B137 et B151 on a toujours le chiffre 11 qui apparait, donc on doit avoir dans K137 et K151 les montants 0
comme mise
De meme dans les autres cellules B183, B191, B211, B235, B260, B290, B315, B341, B367, B414, B441,, B462, B514,
B545, B570, B595, B626 et B685 c'est le meme chiffre 30 qui apparait (pour cette meme personne). donc on doit avoir
cest le meme cas avec la personne "NOMENYO AKOUVI" au niveau des lignes 141 et 173: cest le meme chiffre en colonne B
donc on doit plus ecrire le montant de 500 en K141 et K173 mais ecrire 0
voici un fichier test:
https://www.cjoint.com/?3FEjZTQvtqM
svp jai un souci au nivau de cette formule en K2 : =SI(OU(J2<>J1;B2<>B1);F2;0) la formule marche bien.
seulement il y a un cas que je n'arrive pas à ajouter.
c'est le cas où le nom d'une personne apparait dans plusieurs cellules mais ces cellules ne se suivent pas (colonne J)
mais avec les meme chiffres dans les cellules de la colonne B corresponantes
par exemple quand je selectionne le nom "AMEOGNINE DODJI" dans la colonne J sur la feuille "tetste 1" on a:
De la cellule K63 à K71 c'est bon car cest le meme chiffre (11) qui apparait dans les cellules B63 à B71, on prend
la mise une seule fois, ce qui correspond à la cellule K63 et les autres cest 0
dans B137 et B151 on a toujours le chiffre 11 qui apparait, donc on doit avoir dans K137 et K151 les montants 0
comme mise
De meme dans les autres cellules B183, B191, B211, B235, B260, B290, B315, B341, B367, B414, B441,, B462, B514,
B545, B570, B595, B626 et B685 c'est le meme chiffre 30 qui apparait (pour cette meme personne). donc on doit avoir
cest le meme cas avec la personne "NOMENYO AKOUVI" au niveau des lignes 141 et 173: cest le meme chiffre en colonne B
donc on doit plus ecrire le montant de 500 en K141 et K173 mais ecrire 0
voici un fichier test:
https://www.cjoint.com/?3FEjZTQvtqM
bjr Mr via55.
svp quelqu'un veut me donner une aide, pour y arriver? svp
ou bien y'a t-il un moyen pour regrouper les noms dans la colonne J (une sorte de trie)? de telle sorte que les meme noms se suivent, cela pourra résoudre mon problème... svp
merci à tous
cdlmnt
svp quelqu'un veut me donner une aide, pour y arriver? svp
ou bien y'a t-il un moyen pour regrouper les noms dans la colonne J (une sorte de trie)? de telle sorte que les meme noms se suivent, cela pourra résoudre mon problème... svp
merci à tous
cdlmnt
Bonjour Ria
Oui tu dois avoir trouvé la solution il faut trier, mais ce n'est pas possible sur les feuilles test (le tri ne marche pas, sans doute à cause des formules sur les noms) donc la manip est la suivante :
Avant toute chose faire une copie du classeur et travailler sur cette copie (car on va supprimer des feuilles; donc mieux vaut garder un original!)
1) selectionner les colonnes A à J MAIS PAS la K dans test 1
2) se positionner en A1 d'une nouvelle feuille
3)Clic droit Collage spécial puis Valeurs
4) sur cette nouvelle page sélectionner toutes les colonnes de A à J puis onglet Données puis TRier, cocher Mes données ont des en-têtes puis Trier par : choisir colonne nom et prénom (la J) et faire effectuer tri
5) Revenir sur test 1 selectionner toute la colonne K et venir la coller en colonne K de la nouvelle feuille
6) Donner un nom différent à cette nouvelle feuille puis supprimer la feuille Test1 (sinon la macro la prendra en compte aussi et ça fausserait tout bien sur)
7) Recommencer les mêmes opérations pour la feuille Test2
Cdlmnt
Oui tu dois avoir trouvé la solution il faut trier, mais ce n'est pas possible sur les feuilles test (le tri ne marche pas, sans doute à cause des formules sur les noms) donc la manip est la suivante :
Avant toute chose faire une copie du classeur et travailler sur cette copie (car on va supprimer des feuilles; donc mieux vaut garder un original!)
1) selectionner les colonnes A à J MAIS PAS la K dans test 1
2) se positionner en A1 d'une nouvelle feuille
3)Clic droit Collage spécial puis Valeurs
4) sur cette nouvelle page sélectionner toutes les colonnes de A à J puis onglet Données puis TRier, cocher Mes données ont des en-têtes puis Trier par : choisir colonne nom et prénom (la J) et faire effectuer tri
5) Revenir sur test 1 selectionner toute la colonne K et venir la coller en colonne K de la nouvelle feuille
6) Donner un nom différent à cette nouvelle feuille puis supprimer la feuille Test1 (sinon la macro la prendra en compte aussi et ça fausserait tout bien sur)
7) Recommencer les mêmes opérations pour la feuille Test2
Cdlmnt