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

Conception Discussion :

Grouper et compter les éléments d'une cellule


Sujet :

Conception

  1. #1
    Membre du Club
    Profil pro
    Inscrit en
    mars 2009
    Messages
    152
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2009
    Messages : 152
    Points : 67
    Points
    67
    Par défaut Grouper et compter les éléments d'une cellule
    Bonjour,

    Après une formule laborieuse, j'obtiens

    Terrasse|Terrasse|Jardin|Jardin|

    comme résultat dans la même cellule.


    J'aimerais trouver la formule qui permet d'obtenir
    2 terrasse et 2 jardin

    je verrai par la suite pour rajouter les s à la fin.

    NB : je ne suis pas obligé d'utiliser le symbole pipe | et ce ne sont pas forcément les termes terrasse et jardin qui vont apparaître.

    Et le tout sans macro (malheureusement).

    Merci beaucoup pour votre aide.

  2. #2
    Membre expérimenté
    Inscrit en
    septembre 2007
    Messages
    987
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 987
    Points : 1 600
    Points
    1 600
    Par défaut
    Bonjour,
    Citation Envoyé par jpg75014 Voir le message
    Après une formule laborieuse, J'aimerais trouver la formule qui permet d'obtenir 2 terrasse et 2 jardin
    Je ne sais pas ce que tu attends comme aide mais sans la moindre idée du classeur de recherche, il n'y a donc qu'à faire fonctionner la boule de cristal mais la mienne est cassée.

  3. #3
    Membre du Club
    Profil pro
    Inscrit en
    mars 2009
    Messages
    152
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2009
    Messages : 152
    Points : 67
    Points
    67
    Par défaut
    dans ta citation,

    Envoyé par jpg75014
    Après une formule laborieuse, J'aimerais trouver la formule qui permet d'obtenir 2 terrasse et 2 jardin

    il manque ce bout de phrase :

    j'obtiens

    Terrasse|Terrasse|Jardin|Jardin|

    comme résultat dans la même cellule.

    De ce résultat je voudrais faire une formule qui me permet d'obtenir 2 terrasse et 2 jardin.

    Il manque une information ?

  4. #4
    Membre expérimenté
    Inscrit en
    septembre 2007
    Messages
    987
    Détails du profil
    Informations forums :
    Inscription : septembre 2007
    Messages : 987
    Points : 1 600
    Points
    1 600
    Par défaut
    Bonjour,
    Citation Envoyé par jpg75014 Voir le message
    dans ta citation, il manque ce bout de phrase
    Je n'ai effectivement pas tout cité mais je pense que plutôt qu'à faire une formule pour modifier un résultat de formule, il serait sans doute plus judicieux de modifier ta "formule laborieuse" pour obtenir le résultat souhaité, sans doute par ce que je ne suis pas adepte des formules en cascade.

  5. #5
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 396
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 396
    Points : 50 281
    Points
    50 281
    Billets dans le blog
    92
    Par défaut
    Salut.

    Le principe de base pour compter les occurrences d'un mot dans une phrase est:
    • de compter la longueur L1 de la phrase;
    • de substituer "" au mot concerné;
    • de compter la longueur L2 de la phrase modifiée;
    • de diviser (L2-L1) par la longueur du mot recherché.



    Selon ta version d'Excel, tu auras une formule longue (XL < 365) ou une plus courte (XL 365, grâce à Let qui permet de définir des variables qui sont utilisées dans la formule)

    Nom : 2021-03-26_175321.png
Affichages : 43
Taille : 108,5 Ko



    Comme anasecu, je suis curieux de voir la formule qui concatène tes valeurs, car il y a peut-être bien plus simple pour compter des occurrences.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  6. #6
    Membre du Club
    Profil pro
    Inscrit en
    mars 2009
    Messages
    152
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2009
    Messages : 152
    Points : 67
    Points
    67
    Par défaut
    Bonjour à vous deux, merci pour vos réponses.

    J'ai bien Office 365, je peux donc utiliser LET() par contre, je ne comprends pas l'argument Nbr.


    La formule que j'utilise est la suivante. Lorsque je trouve une valeur > 0 (c'est une surface), je ramène l'entête sans la numérotation.

    Nom : extérieurs.jpg
Affichages : 37
Taille : 237,8 Ko

    Comme je l'expliquais, je peux avoir d'autres résultats que Terrasse ou Jardin et dans certains cas, seulement 2 terrasses, 1 loggia et 2 jardins (min/max 5 colonnes).
    En revanche, ils seront toujours dans l'ordre Terrasse 1,2,3..Jardin 1, 2 et seront toujours numérotés (avec un espace avant le numéro)

    Merci encore et bonne journée.

  7. #7
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 396
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 396
    Points : 50 281
    Points
    50 281
    Billets dans le blog
    92
    Par défaut
    Je ne comprends toujours pas la finalité de ton exercice. Si l'assemblage avec les pipes sert juste à pouvoir mieux dénombrer, il n'est pas nécessaire de passer par cette étape.

    XL365 => utilisation de LET

    Nom : 2021-03-27_152104.png
Affichages : 36
Taille : 72,5 Ko


    Comment fonctionne LET et pourquoi l'utiliser?

    LET permet de définir des valeurs qui seront utilisées dans la formule. C'est pratique pour trois raisons:
    1. éviter la répétition d'un calcul au sein de la formule (gain de temps de calcul);
    2. rendre une formule plus courte;
    3. rendre une formule plus lisible par l'utilisation de noms explicite et courts pour englober des formules plus longues.


    Sa syntaxe est simple: On définit les paires Nom;Valeur puis on écrit la formule en utilisant les noms plutôt que les valeurs. L'exemple ci-dessus illustre bien cette utilisation

    =LET(Terrasses;SOMMEPROD((A2:F2>0)*(A$1:F$1="Terrasse"));Jardins;SOMMEPROD((A2:F2>0)*(A$1:F$1="Jardin"));Terrasses & " Terrasse" & SI(Terrasses>1;"s";"") & " et " & Jardins & " jardin" & SI(Jardins>1;"s";""))

    On voit donc que l'on a attribué UNE fois la valeur de SOMMEPROD((A2:F2>0)*(A$1:F$1="Terrasse")) à une variable nommée Terrasses et on réutilise Terrasses autant de fois que l'on veut dans la formule à la place de SOMMEPROD. On ne réalise donc qu'une fois le calcul pour affecter le résultat à la variable.

    Sans LET, on était obligé d'écrire deux fois SOMMEPROD((A2:F2>0)*(A$1:F$1="Terrasse")) et donc, de le calculer deux fois:

    =SOMMEPROD((A2:F2>0)*(A$1:F$1="Terrasse")) & " Terrasse" & SI(SOMMEPROD((A2:F2>0)*(A$1:F$1="Terrasse"))>1;"s";"") & " et " &SOMMEPROD((A2:F2>0)*(A$1:F$1="Jardin")) & " jardin" & SI(SOMMEPROD((A2:F2>0)*(A$1:F$1="Jardin"))>1;"s";"")


    Citation Envoyé par jpg75014 Voir le message
    [...]je ne comprends pas l'argument Nbr.[...]
    L'argument Nbr est donc le nom d'une variable qui est déterminée par l'argument suivant. Cela permet de ne pas recalculer le nombre d'occurrence chaque fois qu'on en a besoin dans la formule.
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  8. #8
    Membre du Club
    Profil pro
    Inscrit en
    mars 2009
    Messages
    152
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations forums :
    Inscription : mars 2009
    Messages : 152
    Points : 67
    Points
    67
    Par défaut
    Bonjour Pierre,

    Merci pour cette réponse. Je reprends le post un peu tardivement, désolé.

    Déjà dans mon premier post, je précisais que le pipe n'était pas nécessaire. Mais je souhaitais quand même faire une formule intermédiaire pour enlever la numérotation.
    Dans ton exemple qui fonctionne bien (bravo) tu l'enlèves directement dans le nom des colonnes. ça je ne peux pas le faire s'agissant d'un tableau et non pas d'une plage : les noms de colonnes doivent être différents, donc Terrasse 1, Terrasse 2...

    Ensuite, je précisais également qu'il n'y aurait pas forcément selon le tableau les mêmes noms de colonnes. Dans un cas, je peux avoir balcon à la place de terrasse.
    Ce qui est sûr, c'est que j'aurais tout le temps 5 colonnes à disposition.

    Donc je ne peux pas mettre le mot "Terrasse" ou "Balcon" en dur dans la formule.
    Et je ne souhaite pas non plus faire apparaître 0 jardin quand il n'y en a pas.

    D'où la sélection faite dans ma formule avec les pipes.

    Merci encore et bonne journée.

  9. #9
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 396
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 396
    Points : 50 281
    Points
    50 281
    Billets dans le blog
    92
    Par défaut
    Je pense qu'il faudrait revoir l'ergonomie de ta saisie/sélection car ici, tu compliques ton travail, de manière inutile à mon avis.

    Si tu peux modifier l'ergonomie, pourrais-tu expliquer en français (sans jargon Excel) ce que tu souhaites réaliser, un peu comme si tu rédigeais un cahier des charges simplifié?
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

  10. #10
    Responsable
    Office & Excel


    Homme Profil pro
    Formateur et développeur chez EXCELLEZ.net
    Inscrit en
    novembre 2003
    Messages
    17 396
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 54
    Localisation : Belgique

    Informations professionnelles :
    Activité : Formateur et développeur chez EXCELLEZ.net
    Secteur : Enseignement

    Informations forums :
    Inscription : novembre 2003
    Messages : 17 396
    Points : 50 281
    Points
    50 281
    Billets dans le blog
    92
    Par défaut
    Avec la même ergonomie, je pense qu'il n'y a pas de possibilité de solution purement Excel, en tout cas sans montage lourd et encore, je ne vois pas de solution sans VBA puisqu'il faudrait une double matricielle. Mais perso je réfléchirais à une autre interface pour saisir mes données.

    Je propose donc une fonction perso en VBA qui pourra être utilisée "comme une fonction native" dans ton tableau.

    Nom : 2021-04-09_115106.png
Affichages : 16
Taille : 53,8 Ko


    Le code de la fonction à placer dans un module standard. Ce code s'appuie aussi sur les facilités de la 365 et notamment les fonctions JOINDRE.TEXTE (TEXTJOIN) et UNIQUE. Hormis l'utilisation de EVALUATE avec les fonctions spécifiques 365, c'est un code assez banal qui compte aussi la différence de caractères entre deux chaines pour compter le nombre d'occurrences d'un mot dans la chaine comme je l'avais montré en Excel.

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    Function Composition(Index As Long) As String
      Dim CountOf As Long
      Dim Formula As String
      Dim Headers
      Dim i As Long
      Dim Temp As String
      Dim Value As String
     
      Application.Volatile
      Formula = "=TEXTJOIN("";"",TRUE,IF(A{ligne}:E{ligne}<>"""",LEFT($A$1:$E$1,LEN($A$1:$E$1)-2),""""))"
      Temp = Evaluate(Replace(Formula, "{ligne}", Index))
      Headers = Evaluate("=UNIQUE(TRANSPOSE((LEFT(A1:E1,LEN(A1:E1)-2))))")
      For i = 1 To UBound(Headers)
        CountOf = (Len(Temp) - Len(Replace(Temp, Headers(i, 1), ""))) / Len(Headers(i, 1))
        If CountOf > 0 Then Value = Value & CountOf & " " & Headers(i, 1) & IIf(CountOf > 1, "s", "") & ", "
      Next i
      If Value <> "" Then Value = Left(Value, Len(Value) - 2)
      Composition = Value
      Erase Headers
    End Function



    Au passage, avec Excel 365 et même si ça n'aide pas ici, voici une formule "simple" qui permet de reprendre les intitulés de colonnes "utilisées". Cette formule est d'ailleurs utilisée dans le code que j'ai donné pour alimenter la variable Temp...

    Nom : 2021-04-09_113047.png
Affichages : 16
Taille : 47,0 Ko
    "Plus les hommes seront éclairés, plus ils seront libres" (Voltaire)
    ---------------
    Mes remarques et critiques sont purement techniques. Ne les prenez jamais pour des attaques personnelles...
    Pensez à utiliser les tableaux structurés. Ils vous simplifieront la vie, tant en Excel qu'en VBA ==> mon tuto
    Le VBA ne palliera jamais une mauvaise conception de classeur ou un manque de connaissances des outils natifs d'Excel...
    Ce ne sont pas des bonnes pratiques parce que ce sont les miennes, ce sont les miennes parce que ce sont des bonnes pratiques
    VBA pour Excel? Pensez D'ABORD en EXCEL avant de penser en VBA...
    ---------------

Discussions similaires

  1. compter les éléments d'une liste
    Par gaston64 dans le forum Macros et VBA Excel
    Réponses: 0
    Dernier message: 10/02/2010, 21h56
  2. Des explications pour cet algorithme (compter les éléments d'une pile)
    Par adel01 dans le forum Algorithmes et structures de données
    Réponses: 3
    Dernier message: 20/12/2009, 23h16
  3. compter les éléments dans une balise
    Par Sh4dow49 dans le forum Général JavaScript
    Réponses: 16
    Dernier message: 05/09/2008, 20h00
  4. VBA-E Compter les champs d'une cellule
    Par porc-bouc dans le forum Macros et VBA Excel
    Réponses: 8
    Dernier message: 26/09/2007, 16h05
  5. [TestStand] Compter les éléments d'une chaîne de caractères
    Par capblans dans le forum Autres langages
    Réponses: 2
    Dernier message: 29/04/2005, 09h29

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