Recherche cellule ds colonne et affiche noms correspondants

Le 29/11/2009 à 18:36

Bonjour à tous,

Je viens de m'inscrire à votre forum car je n'ai pu y trouver de réponse en cherchant mon problème, que voici :

Dans une colonne A, 20 prénoms. Dans les 12 colonnes suivantes, les 12 mois de l'année. (col B à M)

Chaque mois je rentre en face du prénom, le montant de la cotisation de la personne.

Je parviens facilement à faire afficher dans une cellule le nombre de personnes n'ayant pas cotisé tel mois, avec la fonction nb.vide.

Mais mon souhait est le suivant, (et j'ai cherché avec les fonctions recherchev, index ou index+equiv, mais je n'ai rien trouvé de satisfaisant) :

Je voudrais faire apparaître dans une cellule (ou dans une plage ?) LES prénoms des gens n'ayant rien cotisé pour tel mois.

En clair, si 3 personnes (de la colonne A) n'ont rien cotisé au mois de mai, (3 cellules vides en colonne F), afficher les prénoms des 3 personnes.

Merci beaucoup de m'aider pour trouver cette solution. Vous l'aurez compris mon tableau réel comprend en fait plus de 1000 noms, donc je l'ai raccourci

MrMojo

Le 29/11/2009 à 19:56

Bonsoir

Compte tenu du nombre important de nom, une solution sans matricielle avec une colonne intermédiaire (à masquer si besoin)

112cotisation.zip (4.22 Ko)
Le 29/11/2009 à 22:35

Excellent !

Merci de la rapidité avec laquelle vous avez élaboré ces formules, auxquelles je ne comprends pas grand' chose , mais qui ont l'air de fonctionner ! C'est vraiment très proche de ce que je recherche !

Malheureusement j'aurais souhaité pouvoir faire afficher les prénoms sur la base de cellules vides, et non de cellules remplies de zéros.

C'est vrai que j'ai omis de vous dire que je ne mettais pas que des nombres, mais aussi des "x" : si la personne a commencé à cotiser seulement en mai, je ne vais pas la comptabiliser comme non-cotisante pour les mois 01 à 04, mais les "x" compteront pour son nombre de timbres restants. Idem pour les adhérents qui partent, ou décédés : on ne peut pas les compter comme encore cotisants bien sûr !

Normalement quand je mets un montant dans une cellule, celle-ci change de couleur automatiquement, de façon à avoir une vue d'ensemble des cotisants de telle ou telle ville.

Si je mets un zéro dans la cellule, la couleur de la cellule ne change pas non plus, mais la cellule est comptée comme "non-vide", ce qui ne va plus pour d'autres calculs. (ou alors je changerai mes autres calculs si cela n'est pas possible autrement)

En fait le tableau original est hyper-vaste, avec des calculs pas très compliqués certes, mais importants.

Serait-il possible de faire ce que vous proposez, mais en partant de cellules vides, et sans liste déroulante mais 12 fois (une sous chaque mois ??)

Et merci de m'expliquer brièvement ce que vous entendez par "formules nommées", ainsi que l'importance de la colonne O dans votre tableau.

Enfin, je voudrais vous dire que mon doigt a dû glisser lors du premier message : le tableau comporte + de 100 noms, mais pas plus de 1000 ! (Enfin pour le moment !)

Merci de m'indiquer clairement si toutes ces questions ne vous importunent pas (trop ?) et si cela est faisable ou non.

Cordialement,

MrMojo

PS : ci-joint votre tableau, j'y ai collé mon tableau onglet 2

26cotisation.zip (5.93 Ko)
Le 30/11/2009 à 19:32

Bonsoir

Avec ces données, la résolution par formules montre ses limites.Une solution VBA serait plus adaptée (mais, je ne sais pas faire)

Dans ce fichier, une solution un peu délicate à mettre en oeuvre qui rencontre 2 difficultés

1) Si plusieurs prénoms sont identiques, le résultat n'est pas obtenu. (Pour l'exemple, j'ai ajouté 1, 2, ou 3 aux prénoms en doubles ou en triples)

2) La liste obtenue est inversée par rapport au tableau

Cependant, tous les noms recherchés sont là.

C'est pourquoi, une solution avec des zéros est nettement plus facile (fichier précédent) le 0 pouvant rester invisible en supprimant son affichage (Menu "Outils" "Options" onglet "Affichage" et décocher "Valeur 0"

Cordialement

43cotisationv2.zip (9.24 Ko)
3test.xlsx (208.51 Ko)
Le 30/11/2009 à 22:33

Amadeus,

Je vous remercie infiniement de la solution apportée à mon souci, je ne pouvais pas répondre plus tôt car en réunion jusqu'à 22h !

En effet je crois que j'opterai plutôt pour la solution avec les zéros car plus élégante et plus simple, tout en devant modifier mes autres calculs (mais c'est pas bien méchant !)

Les formules que vous mettez en oeuvre sont tout à fait nouvelles pour moi et ouvrent un champ totalement inexploré, donc vous avez fait un double boulot : résoudre mon problème et m'intéresser davantage à excel.

Merci encore de votre disponibilité,

Très cordialement,

MrMojo

Le 14/12/2009 à 01:14

Rebonjour Amadeus,

Je suis géné de devoir vous déranger une fois de plus à ce propos, mais j'ai un petit souci avec votre tableau 'cotisations v2' : quand tous les adhérents ont donné une cotisation, je me retrouve avec des lignes #REF! partout !! Comment faire ?

A quoi faut-il faire atention si on copie les formules ? Faut-il nommer d'autres champs ? Doit-on nommer toujours en faisant 'insertion' 'nom' ou peut-on mettre un nom dans la case en haut à gauche ? (Je veux dire la zone nom au-dessus de la cellule A1)

J'ai aussi un autre prob. concernant votre premier tableau 'cotisations' : comment faire pour qu'apparaissent aussi les NOMS des personnes concernées, à côté des prénoms ? Je sais que j'aurais dû vous demander cela dès le début, mais je ne savais pas que ce serait aussi compliqué !! Et puis enfin toujours pour ce tableau (que je trouve plus élégant c'est évident !), comment faire pour copier les formules dans mon propre tableau ? Je réussis bien à obtenir ma liste déroulante de mois, mais rien n'est identique pour la suite : ça n'a pas l'air de marcher !! (Je sais bien que j'ai dû mal recopier ou nommer qqchose...)

Merci infiniement pour toute l'aide que vous pourrez m'apporter.

Cordialement,

MrMojo

Le 14/12/2009 à 09:37

Bonjour

Il n'y a aucune raison d'être géné, le Forum est là pour aider à résoudre les soucis..Comme 2 classeurs sont concernés, je vais essayer de répondre dans l'ordre.

Concernant le Fichier CotisationV2.xls

avec votre tableau 'cotisations v2' : quand tous les adhérents ont donné une cotisation, je me retrouve avec des lignes #REF! partout !! Comment faire ?

Sur le fichier CotisationV2.xls Feuille Mon tableau; remplacer ligne 24 la première formule par:

=SI(NB.VIDE(G$2:G$20)>0;DECALER($B$1;MAX(LIGNE($2:$20)*(G$2:G$20=""))-1;);"")

(Matricielle Ctrl+Maj+Entrée)

A quoi faut-il faire atention si on copie les formules ?

1) Aux signes dollar ($) dont les positions sont importantes surtout lors des incrémentations de formules.

2) Aux formules matricielles à Valider avec les 3 touches Ctrl+Maj+Entrée pour qu'Excel transforme la formule classique en Matricielle.

Faut-il nommer d'autres champs ?

Non! Pour quoi faire, puisque ceux servants pour les formules sont déjà nommés.

Doit-on nommer toujours en faisant 'insertion' 'nom' ou peut-on mettre un nom dans la case en haut à gauche ? (Je veux dire la zone nom au-dessus de la cellule A1)

Non, les 2 méthodes fonctionnent pour nommer des cellules ou des champs. Par contre, pour nommer une formule, il faut passer par "Insertion" "Nom"..

Pour le fichier Cotisation.xls et l'affichage des noms, je te suggère de nous envoyer ou ton fichier (en enlevant les données personnelles ) ou un exemple de celui-ci avec les Noms et prénoms dans la bonne colonne, ce qui te facilitera par la suite l'adaptation à ton fichier.

Cordialement

33cotisationv2.zip (9.10 Ko)
Le 21/01/2010 à 00:14

Bonjour à tous et merci infiniement pour votre aide précieuse : c'est exactement ce que je recherchais !

Par contre je ne m'attendais pas à de telles formules, je pensais qu'il existait une fonction toute prête à l'emploi !!

Merci encore.

Cordialement,

MrMojo

Le 23/01/2010 à 12:45

Bonjour à tous,

Solution VBA sans formules

Cliquer sur un mois, résultat en colonne "A"

on pourrait extraire un tableau complet sur une autre feuille.

Amicalement

Claude

45mrmojo2.zip (15.11 Ko)
Le 23/01/2010 à 23:38

Bonsoir Claude,

C'est tout simplement confondant de simplicité et d'élégance !!!

C'est exactement ce qu'il me fallait !!

Et si l'on voulait mettre plus de noms dans plus de villes, il faut changer le code dans vba ? Pouvez-vous me dire dans quelle ligne et quoi changer ?

Et puisqu'on en est là, et pour extraire le tout sur une autre feuille ?

Enfin, comment transférer votre programme vba sur mon véritable fichier ? Faut-il recréer le script ??

Merci infiniment.

MrMojo

Le 24/01/2010 à 14:40

Bonjour à tous,

Version panachée avec extraction sur feuille "Extrait"

peut servir sur plusieurs années,

Lire explications sur commentaire en A1

Amicalement

Claude

21mrmojo3.zip (28.59 Ko)
Le 09/02/2010 à 15:50

Bonjour M. Dubois,

et mes excuses pour ne pas vous avoir remercié plus tôt : beaucoup de soucis d'ordinateur et de temps !

Donc Merci !!! Votre bout de programme marche "excellement" bien !

Je suis assez admiratif devant tant d'ingéniosité, il faut être un véritable crack en informatique. Ca s'apprend où ??

J'avais commencé l'informatique avec le langage BASIC à l'époque du D.O.S., où Windows n'existait pas encore... L'époque MO5, TO7 et oric.... Mais ça m'a l'air beaucoup plus recherché que le BASIC de l'époque, votre VBA ...!

Bref.

Je voulais vous demander s'il était possible de virer le fichier "mojo3", car j'ai la macro 'filtreBoucle' qui s'ouvre AVEC le fichier mojo3 quand on clique sur "extrait" DANS MON FICHIER de cotisations, puis cela ouvre l'onglet "extrait" sur mon propre fichier.

La cause de ma demande est celle-ci : quand j'ouvre mon fichier général de cotisations, on me demande si je veux rompre les liaisons avec un autre fichier (mojo3 en l'occurence), et je n'ose pas cliquer sur oui !! Tout marche bien comme ça en fait, mais je voulais savoir si on pouvait rompre, où sinon s'il faut recréer la macro, ce que je ne sais pas faire.

Si je ne suis pas assez clair merci de me le dire !!

Merci de votre aide.

MrMojo

Le 09/02/2010 à 22:50

Bonsoir,

Tu veux dire que dans ton fichier réel tu as déjà un onglet nommé "Extrait" ?

confirme

Claude

Le 10/02/2010 à 01:42

Oui je confirme ; j'ai dû le recopier pour appliquer ton vba...

MrMojo

Le 10/02/2010 à 09:22

Bonjour,

Le + simple aurait été de continuer sur mon fichier en écrasant les données par les tiennes,

et en y ajoutant éventuellement tes autres feuilles.

Sur chaque feuilles d'année, tu doit avoir dans le VBE de le feuille ce code :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CL As Byte
    If Not Application.Intersect(Target, Range("d1:o1")) Is Nothing Then
 ''Macros par Claude Dubois pour "MrMojo" Excel-Pratique le 24/01/2010
        If Target.Count > 1 Then Exit Sub
        Application.ScreenUpdating = False
        CL = Target.Column
        Range("a4:a50").Clear
        Range("a1") = "Impayé " & Cells(1, CL)
        Cells(2, 18).FormulaR1C1 = "=ISBLANK(RC" & CL & ")"
        Range("b1:o" & [c65000].End(xlUp).Row).AdvancedFilter _
        Action:=xlFilterCopy, CriteriaRange:=Range( _
        "r1:r2"), CopyToRange:=Range("a3"), Unique:=False
        Cells(2, 18).ClearContents
    End If
End Sub

Puis dans un module standard ces 2 macros :

Sub FiltreBoucle() 
''Macros par Claude Dubois pour "MrMojo" E-Pratique le 24/01/2010
Dim Lg%, i As Byte, Y As Byte, x, z
    Application.ScreenUpdating = False
    Lg = Range("c65536").End(xlUp).Row
        Sheets("Extrait").Range("a3:a" & Lg + 10).EntireRow.Delete
        x = WorksheetFunction.Min(12, Month(Date) + 1)
        z = Year(Date)
        If Range("a2") < z Then x = 12 'année
    For i = 4 To 3 + x
        With Sheets("Extrait")
            .Range("b1") = ActiveSheet.Name 'feuille appelante
            Y = i - 2
            .Cells(2, Y) = "Nom"
            Cells(2, 18).FormulaR1C1 = "=ISBLANK(RC" & i & ")"
            Range("b1:o" & Lg).AdvancedFilter _
            Action:=xlFilterCopy, CriteriaRange:=Range( _
            "r1:r2"), CopyToRange:=.Cells(2, Y), Unique:=False
            .Cells(2, Y) = Cells(1, i) 'mois
            .Range("b:m").EntireColumn.AutoFit
        End With
    Next i
        Cells(2, 18).ClearContents
        Sheets("Extrait").Activate
        Application.Goto Range("a1"), Scroll:=True
        Range("b2").Activate
End Sub
Sub Retour()
Dim Sh$
Sh = Range("b1")
    Sheets(Sh).Activate
    Application.Goto Range("a1"), Scroll:=True
End Sub

Sur le ou les boutons "Exrait", vérifie en cliquant droite dessus, si la macro affectée est bien sur

ton fichier et non sur "MrMojo3".

Il ne faut pas conserver de liaisons,

Si tu n'y arrive pas, envoie-moi ton fichier complet en MP,

je te le remettrais d'équerre.

Amicalement

Claude

Le 06/12/2010 à 01:26

Bonjour Mr Dubois, ou un modérateur de ce merveilleux site grâce auquel on apprend tous les jours !!

J'ai un souci similaire au souci du début d'année (cotisations.xls), mais moins compliqué, je pense...

Données : Dans une colonne K, les n° des journaux dans l'ordre chronologique (23, 24, 25, 26.....), que j'ai nommée "Nouveaux_Num"

Dans une colonne L, les dates de parution de ces journaux (en face de 23, --> 15/10/10, en face de 24, --> 22/10/10) etc., que j'ai nommée "Date_parution"

Dans une cellule F3, la personne doit choisir un numéro dans la liste "Nouveaux_Num" (j'ai fait ça avec la fonction 'validation'.) Bon.

Question (et conséquemment, problème !) : comment faire apparaître dans une cellule unique (en G3 par exemple), la date de parution correspondant au choix de la personne en F3 ?

Faut-il des formules normales ? Des matricielles ? Ou du code VBA ? J'ai essayé de calquer sur les macros de mon précédent fichier mais j'ai passé des HEURES dessus sans pouvoir comprendre ce que je devrais modifier !

Je vous joins le petit fichier nommé "Num_Parution_MrMojo" pour rendre ma question plus simple.

(L'explication de tout cela est la suivante : la personne en charge du suivi des abonnements au journal voudrait voir simplifié son travail. En choisissant le n° du journal en cours, elle peut voir d'un coup d'oeil, dans le tableau à gauche, si l'abonnement est fini ou pas, et ce en fonction du n° de journal en cours. En colonne E, les numéros se colorent suivant qu'il reste 3 ou 4 numéros d'abonnement à courir). Grâce à votre aide la personne en charge de tout cela pourrait voir la date de parution du n° choisi cellule F3, car les numéros seuls sont moins parlant qu'une date)

Je vous remercie par avance infiniment de l'aide que vous pouvez m'apporter de nouveau pour résoudre ce problème, et dans l'attente de vous lire, je vous souhaite bien du courage dans votre travail, quel qu'il soit.

Très cordialement,

MrMojo

9num-parution-mrmojo.zip (18.23 Ko)
Le 06/12/2010 à 12:43

Bonjour,

Pas besoin d'un tel laïus

un simple RECHERCHEV (abc d'Excel)

en G3

=RECHERCHEV(F3;K3:L500;2;0)

Amicalement

Claude

Rechercher des sujets similaires à "recherche colonne affiche noms correspondants"