IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Formule calcul occurrences identiques sous condition


Sujet :

Excel

  1. #1
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut Formule calcul occurrences identiques sous condition
    Bonjour Le Forum,

    Je cherche à formuler un calcul :

    Dans une colonne (A), j'ai une liste de personne (Nom-Prénom), dans une colonne (B) des intitulés de formations. Exemple :

    Colonne A : Colonne B :
    DUPONT ROMAIN INFORMATIQUE
    DUPONT ROMAIN INFORMATIQUE
    DUPONT ROMAIN EXCEL
    MARTIN STEPHANE EXCEL
    OLIVIER GEORGE EXCEL


    Monsieur DUPONT est 2 fois dans mon tableau, car il a effectué 2 fois la formation informatique, mais à des dates différentes.

    J'aimerais que la formule compte Monsieur DUPONT 1 fois et non 2 fois pour la formation informatique. Je ne sais pas comment m'y prendre, malgré plusieurs essais...

    Merci de votre aide.

  2. #2
    Membre actif
    Homme Profil pro
    Ingénieur Qualité (Microtechnique)
    Inscrit en
    Février 2016
    Messages
    188
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Suisse

    Informations professionnelles :
    Activité : Ingénieur Qualité (Microtechnique)
    Secteur : Industrie

    Informations forums :
    Inscription : Février 2016
    Messages : 188
    Points : 265
    Points
    265
    Par défaut
    Hello
    Il suffit de tous mettre dans un tableau à 2 dimensions et de trier par ordre alphabétique
    http://silkyroad.developpez.com/vba/tableaux/#LXIV-D
    Après tu boucles sur le tableau pour trouver les doublons (tu trouveras donc 2 entrées avec Dupont)
    Il te suffit de garder que celle qu’il te faut.
    Pas plus difficile.
    Tu peux aussi faire un filtre :
    http://silkyroad.developpez.com/vba/tableaux/#LXI
    Avant de poser une question avez-vous testé l’enregistreur de macro ?
    http://fauconnier.developpez.com/tut...istreur-macro/
    Merci de passer vos discussion en
    Et sinon un est toujours le bienvenue.

  3. #3
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    En supposant que tes données vont de la ligne 2 à la ligne 100, tu mets en C2 la formule :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(NB.SI.ENS(B$2:B2;B2;A$2:A2;A2)=1;1;0)
    Attention, la présence ou non de $ est importante.

    Tu copies C2 en C2:C100.

    Pour connaitre le nombre de formations informatiques en ne comptant chaque personne qu'une fois :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME.SI(B2:B100;"INFORMATIQUE";C2:C100)
    Peut-être qu'un magicien des formules matricielles peut transformer ça en une seule formule.
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  4. #4
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Bonjour,

    La solution du VBA, pour le coup n'est pas approprié à ce que je souhaite faire. Mais merci

    Menhir,

    Ta solution me plait bien, par contre, la formule me compte DUPONT 2 fois pour la formation informatique.

  5. #5
    Expert éminent sénior Avatar de Menhir
    Homme Profil pro
    Ingénieur
    Inscrit en
    Juin 2007
    Messages
    16 037
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Finistère (Bretagne)

    Informations professionnelles :
    Activité : Ingénieur
    Secteur : Industrie

    Informations forums :
    Inscription : Juin 2007
    Messages : 16 037
    Points : 32 866
    Points
    32 866
    Par défaut
    Citation Envoyé par Mél3790 Voir le message
    Ta solution me plait bien, par contre, la formule me compte DUPONT 2 fois pour la formation informatique.
    Quels valeurs mets la première formule en face de chaque ligne ?
    Merci de cliquer sur pour chaque message ayant aidé puis sur pour clore cette discussion.

  6. #6
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    J'ai répondu trop vite, Menhir, tu me sauve la vie une nouvelle fois.
    J'avais fait une mauvaise manip.

    Ta solution fonctionne parfaitement, cela me convient .

    Merci beaucoup

  7. #7
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Par contre,

    Est-il possible de combiner ceci :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =NB.SI.ENS(Tableau2[FORMATION];A108;Tableau2[CATEGORIE];$B$4;Tableau2[date début];">="&$J$4)
    (Cette formule cherche les formations informatique par catégorie de personnel (ouvrier ou cadre) dont la date de début de formation est supérieur à celle d'aujourd'hui)

    Avec ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SI(NB.SI.ENS(F$3:F3;F3;B$3:B3;B3)=1;1;0)
    ?

    Merci beaucoup.

  8. #8
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Une personne aurait une idée ?
    Merci

  9. #9
    Expert éminent Avatar de casefayere
    Homme Profil pro
    RETRAITE
    Inscrit en
    Décembre 2006
    Messages
    5 138
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 69
    Localisation : France, Ardennes (Champagne Ardenne)

    Informations professionnelles :
    Activité : RETRAITE
    Secteur : Agroalimentaire - Agriculture

    Informations forums :
    Inscription : Décembre 2006
    Messages : 5 138
    Points : 9 548
    Points
    9 548
    Par défaut
    Bonjour,
    si cette formule fonctionne
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =NB.SI.ENS(Tableau2[FORMATION];A108;Tableau2[CATEGORIE];$B$4;Tableau2[date début];">="&$J$4)
    et s'il s'agit d'additionner ou de soustraire, pourquoi pas
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =NB.SI.ENS(Tableau2[FORMATION];A108;Tableau2[CATEGORIE];$B$4;Tableau2[date début];">="&$J$4)+SI(NB.SI.ENS(F$3:F3;F3;B$3:B3;B3)=1;1;0)
    "-" pour soustraire, mais je n'ai certainement pas compris
    Cordialement,
    Dom
    _____________________________________________
    Vous êtes nouveau ? pour baliser votre code, cliquer sur cet exemple : Anomaly
    pensez à cliquer sur si votre problème l'est
    Par contre, il est désagréable de voir une discussion résolue sans message final du demandeur (satisfaction, désarroi, remerciement, conclusion...)

  10. #10
    Membre confirmé
    Homme Profil pro
    conseiller
    Inscrit en
    Janvier 2013
    Messages
    367
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Vaucluse (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : conseiller
    Secteur : Administration - Collectivité locale

    Informations forums :
    Inscription : Janvier 2013
    Messages : 367
    Points : 649
    Points
    649
    Par défaut
    Bonjour,
    solution par formule matricielle (même si un TCD serait à mon avis la solution la plus appropriée) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME(N(FREQUENCE(SI(A$2:A$6=D2;EQUIV(A$2:A$6&B$2:B$6;A$2:A$6&B$2:B$6;0));LIGNE(A$1:A$6))>0))
    Nom : screenshot.10.png
Affichages : 307
Taille : 11,8 Ko

    A+

  11. #11
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    Autre proposition d'utilisation de formule matricielle intégrant les critères statut (cadre, ouvrier) et dates de début de formation.
    Nom : doublons.JPG
Affichages : 341
Taille : 103,8 Ko
    Les noms statut, noms, formation, début désignent dans l'exemple construit les plages B2:B15 , A2:A15 , C2:C15 , D2: D15 .
    Les noms choixStatut et choixDébut désignent respectivement les valeurs contenues dans les cellules $I$1 et $K$1.
    Le nom choixFormation désigne le contenu de la cellule de la colonne F sur la même ligne que la cellule dans laquelle est écrite une formule utilisant ce nom.
    La formule matricielle en G3 est :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME(SI((formation=choixFormation)*(statut=choixStatut)*(début>=choixDébut);1/NB.SI.ENS(noms;noms;formation;choixFormation;début;">="&choixDébut)))
    validée par Ctrl + Maj + Entrée
    Cordialement
    Claude
    Fichiers attachés Fichiers attachés

  12. #12
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Merci Paplouco, ta solution fonctionne parfaitement. Et serais-tu comment additionner le coût de la formation informatique pour chaque personne en prenant en compte toutes les conditions précédentes ?
    (En admettant que la formation informatique soit de 200 euros, en colonne E de ton tableau)

    merci beaucoup

  13. #13
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    J'ai réussi à adapter, merci beaucoup, cela fonctionne.

  14. #14
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Bonjour,

    Je "rouvre" cette discussion parce qu'aujourd'hui je dois améliorer cette formule matricielle :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME(SI((Tableau2[FORMATION]=A266)*(Tableau2[CATEGORIE]=$B$232)*(Tableau2[Etat]<>"Annuler")*(Tableau2[date début]<$B$1);Tableau2[COÛT TOTAL POUR UN OUVRIER :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A266;Tableau2[date début];"<"&$B$1)))
    Dans cette formule, si l'Etat de la formation est "Annuler", ça ne prend pas en compte son coût.

    Aujourd'hui, mon problème est : si une formation est "Annuler" mais est tout de même "Facturée", alors le coût de la formation doit être compté.

    Donc il faudrait rajouter dans cette formule un truc du genre : (Tableau2[Facture]="Oui").

    J'ai essayé comme ceci :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME(SI((Tableau2[FORMATION]=A266)*(Tableau2[CATEGORIE]=$B$232)*(Tableau2[Etat]<>"Annuler")*(Tableau2[Facture]="Oui")*(Tableau2[date début]<$B$1);Tableau2[COÛT TOTAL POUR UN OUVRIER :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A266;Tableau2[date début];"<"&$B$1)))
    Mais ça ne fonctionne pas

    Quelqu'un aurait une idée ?

    Merci beaucoup.

  15. #15
    Membre expert

    Homme Profil pro
    Retraité
    Inscrit en
    Juin 2012
    Messages
    1 564
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Alpes Maritimes (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Retraité
    Secteur : Enseignement

    Informations forums :
    Inscription : Juin 2012
    Messages : 1 564
    Points : 3 554
    Points
    3 554
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    Essayer la formule transformée ainsi :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    =SOMME(SI((Tableau2[FORMATION]=A266)*(Tableau2[CATEGORIE]=$B$232)*((Tableau2[Etat]<>"Annuler")+(Tableau2[Facture]="Oui"))*(Tableau2[date début]<$B$1);Tableau2[COÛT TOTAL POUR UN OUVRIER :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A266;Tableau2[date début];"<"&$B$1)))
    Cordialement
    Claude

  16. #16
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Bonjour,

    J'essaie dans la journée, je vous tiens au courant, merci !

  17. #17
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Génial, ça fonctionne !

    Merci beaucoup !

    Bonne journée.

  18. #18
    Membre du Club
    Femme Profil pro
    Assistante
    Inscrit en
    Février 2016
    Messages
    156
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Indre et Loire (Centre)

    Informations professionnelles :
    Activité : Assistante

    Informations forums :
    Inscription : Février 2016
    Messages : 156
    Points : 51
    Points
    51
    Par défaut
    Bonjour tout le monde, je repars sur ce sujet, car je suis de nouveau coincée.

    L'outil évolue au fur et à mesure, et les personnes qui l'utilise sont de plus en plus exigeantes lol

    Donc je suis avec cette formule matricielle :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    {=SOMME(SI((Tableau2[FORMATION]=A120)*(Tableau2[CATEGORIE]=$H$5)*((Tableau2[Etat]<>"Annuler")+(Tableau2[FORMATION FACTUREE MEME SI ANNULEE ?]="Oui"))*(Tableau2[date début]>=$B$1);Tableau2[COÛT TOTAL POUR UN CADRE :]/NB.SI.ENS(Tableau2[NOM Prénom];Tableau2[NOM Prénom];Tableau2[FORMATION];A120;Tableau2[date début];">="&$B$1)))}
    Le principe : que ça prenne en compte tous les coûts pour des personnes (Cadres) sur une même formation(INFORMATIQUE), que les dates de formations soient supérieur à la date du jour, que si la formation est annulée elle n'est pas calculer sauf si elle est annulée mais facturée.

    Là ce que je souhaite rajouter : si Monsieur BIDULE est planifié en formation INFORMATIQUE sur différente session, donc plusieurs dates, faut que les coûts pour lui se cumulent car les dates sont différentes. Tout en respectant les critères de la formule déjà existants.

    Merci

Discussions similaires

  1. conditions/formules calculs de dates
    Par hichem94120 dans le forum Excel
    Réponses: 5
    Dernier message: 08/11/2010, 02h09
  2. Calculer une somme sous conditions.
    Par Julieta dans le forum Excel
    Réponses: 1
    Dernier message: 22/02/2010, 20h36
  3. [XL-2007] effectuer un calcul sous condition
    Par chrnoe dans le forum Excel
    Réponses: 3
    Dernier message: 13/01/2010, 22h07
  4. [Calculs VBA] Test de condition sous VBA
    Par guen dans le forum VBA Access
    Réponses: 8
    Dernier message: 28/11/2007, 18h52
  5. [Excel] Calcul d'une somme sous condition
    Par netsabes dans le forum Macros et VBA Excel
    Réponses: 4
    Dernier message: 22/08/2006, 17h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo