Discussion :

[jpg75014]

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.

[anasecu]

Bonjour,

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.

[jpg75014]

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 ?

[anasecu]

Bonjour,

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.

[Pierre Fauconnier]

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)





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.

[jpg75014]

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.



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.

[Pierre Fauconnier]

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




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";"")

[...]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.

[jpg75014]

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.

[Pierre Fauconnier]

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é?

[Pierre Fauconnier]

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.




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...