injecter un array dans une formule

[insosama]

Bonjour

j'ai créé une fonction "split", accessible donc sous forme de formule, qui retourne un array.
donc une cellule contenant par exemple 14//18//22
retourne un tableau de valeur
{"14"\"18"\"22"}

testé avec l'aide de rédaction de formule, cela fonctionne parfaitement, et c'est effectivement ce que retourne mon split.

par contre le résultat affiché est la premiere valeur seule du tableau. la encore, c'est tout a fait normal (donc ici 14)

j'aurais aimé faire un "somme" dessus (ou n'importe quelle autre fonction basique mathématique disponible dans excel)

je tente donc un "somme(cnum(split("14//18//22";"//")))"

cela me retourne ... 14

pourtant, par contre, la fonction "index" marche très bien

index(split("14//18//22";"//");2) retourne 18

comment donc lire mon array dans une plage de valeur tel que demandé dans toute les formules somme, somme si, moyenne, etc ...

cordialement.

 

[Caillou]

Re : injecter un array dans une formule

Bonjour,

A mon avis, si tu veux de l'aide, tu devrais poster ta fonction split....

Caillou

 

[Dranreb]

Re : injecter un array dans une formule

Bonsoir.

Moi je l'ai écrite comme ça pour voir:

Function Split(ByVal Z As String, S As String) As String()
Split = VBA.Split(Z, S)
End   Function

=SOMME(CNUM(Split("14//18//22";"//")))

Renvoie 54, validé en matriciel (Ctrl+Maj+Entrrée) évidemment puisqu'on veut que CNUM(Split("14//18//22";"//")) soit évaué comme un tableau !

Remarque, je préfère quand même lui donner un autre nom :

Function Scindé(ByVal Z As String, Optional ByVal S As String = " ") As String()
Scindé = Split(Z, S)
End Function

 

[insosama]

Re : injecter un array dans une formule

ah cool cette histoire de matriciel, je ne savais pas !
la solution sera peut etre d'ajouter un parametre dans la fonction scindé (optional numeric as boolean = false)
et si true, d'ajouter une conversion éventuelle des données de "string" a "double".
le résultat de la fonction etant alors du "variant"
ca devrait permettre de se passer de CNUM, donc d'avoir a executer le code différement d'un code "classique", non ?

enfin, je vais faire des essais autour de cette idée.

cordialement.

 

[Dranreb]

Re : injecter un array dans une formule

Bonjour.

Oui, je me demande presque si je ne vais pas me la garder aussi comme ça :

Function Scindé(ByVal Z As String, Optional ByVal S As String = " ") As Variant()
Dim Te() As String, Ts(), C As Long
Te = Split(Z, S)
ReDim Ts(1 To UBound(Te) + 1)
For C = 1 To UBound(Ts)
   If IsNumeric(Te(C - 1)) Then Ts(C) = CDbl(Te(C - 1)) Else Ts(C) = Te(C - 1)
   Next C
'Scindé = Ts  Non, ça pourrait apporter un petit + de l'écrire comme ça :
If Application.Caller.Rows.Count > 1 Then Scindé = WorksheetFunction.Transpose(Ts) Else Scindé = Ts
End Function

 

[insosama]

Re : injecter un array dans une formule

j'ai du mal a comprendre le but de ton dernier "if" : ?

sinon, oui, elle est magnifique ta petite fonction
moi j'ai ajouté aussi deux variables, une pour gerer des index qui sont ignoré (par exemple le 0, qui dans mon cas contient un titre), l'autre optionnel aussi, pour trouver "le nombre de valeur" ou "la valeur d'une référence / index" donné.

d'ailleur, question lié :
comment on fait pour retourner une valeur string seule dans une fonction sensé retourner un tableau ?
pour le moment, je créé un tableau mono valeur. c'est un peu bete, mais visiblement il refuse autrement.

cordialement.

 

[Dranreb]

Re : injecter un array dans une formule

Le but de mon dernier If concerne surtout l'utilisation matricielle: elle adapte l'orientation en lignes ou colonnes du résultat à celui de la plage matricielle appelante.
Comprends pas l'intérêt d'essayer de rendre autre chose qu'un tableau si un tableau est rendu dans le cas général : =Scindé("toto") m'affiche bien "toto" dans la cellule.
Mais enfin c'est possible en enlevant les parenthèses derrière As Variant. au lieu de renvoyer un tableau de Variant il renvoie, dans le cas général, un Variant contenant un tableau de Variant, mais ça semble passer tout aussi bien pour Excel

Function Scindé(ByVal Z As String, Optional ByVal S As String = " ") As Variant
Dim Te() As String, Ts(), C As Long
Te = Split(Z, S)
ReDim Ts(1 To UBound(Te) + 1)
For C = 1 To UBound(Ts)
   If IsNumeric(Te(C - 1)) Then Ts(C) = CDbl(Te(C - 1)) Else Ts(C) = Te(C - 1)
   Next C
If UBound(Ts) = 1 Then
   Scindé = Ts(1)
ElseIf Application.Caller.Rows.Count > 1 Then
   Scindé = WorksheetFunction.Transpose(Ts)
Else: Scindé = Ts: End If
End Function

 

[insosama]

Re : injecter un array dans une formule

pour retourner un message d'erreur. ma fonction split integre quelques options en plus.
j'ai fait autrement de mon coté, mais c'est bon a savoir qu'excel "interprete" convenablement aussi avec ta solution

j'avais besoin de d'autres fonctions, notament pouvoir trouver le "nombre" d'occurence, exclure des index, ou retourner la valeur que d'un seul index, voir dans ce cas, pouvoir faire un "double split".

tout fonctionne, et passe très bien avec les formules de base d'excel. c'est PARFAIT.
merci beaucoup

pour info, voici mon code, complet.

Function SplitValue(ByVal strInput As String, Optional ByVal strSeparateur As String = "|", Optional Index As Long = -1, Optional IndexExclude As String = "", _
    Optional SecondaryDelimeter As String = "") As Variant()

Dim FirstSplit() As String, SplitExclude() As String, TempArray(), C As Long, j As Long, z As Long, good As Boolean

FirstSplit = Split(strInput, strSeparateur)
If IndexExclude <> "" Then SplitExclude = Split(IndexExclude, strSeparateur)

    If IndexExclude = "" And Index < 0 Then
        ReDim TempArray(1 To UBound(FirstSplit) + 1)
    End If

    z = 0
    
    For C = 1 To UBound(FirstSplit) + 1
        If IndexExclude <> "" Then
            'retourne l'ensemble imputé de certains index
            good = True
            For j = 0 To UBound(SplitExclude)
                If SplitExclude(j) = C Then
                    good = False
                End If
            Next
            If good = True Then
                ReDim Preserve TempArray(z)
                If IsNumeric(FirstSplit(C - 1)) Then
                    TempArray(z) = CDbl(FirstSplit(C - 1))
                Else
                    TempArray(z) = FirstSplit(C - 1)
                End If
                z = z + 1
                good = False
            End If
            If Index = 0 And C = UBound(FirstSplit) + 1 Then
                'retourne le total imputé de certains elements
                j = UBound(TempArray) + 1
                ReDim TempArray(0)
                TempArray(0) = j
            End If
        Else
            Select Case Index
                Case Is < 0
                    'retourne l'ensemble complet
                    If IsNumeric(FirstSplit(C - 1)) Then
                        TempArray(C) = CDbl(FirstSplit(C - 1))
                    Else
                        TempArray(C) = FirstSplit(C - 1)
                    End If
                Case 0
                    'retourne le total
                    j = UBound(FirstSplit) + 1
                    ReDim TempArray(0)
                    TempArray(0) = j
                    Exit For
                Case Is > 0
                    'retourne un et un seul index; dans ce cas, voir si y'a un second "split"
                    If SecondaryDelimeter = "" Then
                        'retourne une valeur seule
                        ReDim TempArray(0)
                        If UBound(FirstSplit) + 1 <= Index Then
                            TempArray(0) = "#ERROR INDEX TO HIGHT#"
                        Else
                            TempArray(0) = FirstSplit(Index - 1)
                        End If
                    Else
                        'sous découpe encore en un nouveau tableau. traite les résultat en nombre
                        If UBound(FirstSplit) + 1 <= Index Then
                            ReDim TempArray(0)
                            TempArray(0) = "#ERROR INDEX TO HIGHT#"
                        Else
                            FirstSplit = Split(FirstSplit(Index - 1), SecondaryDelimeter)
                            ReDim TempArray(1 To UBound(FirstSplit) + 1)
                            For j = 1 To UBound(FirstSplit) + 1
                                If IsNumeric(FirstSplit(j - 1)) Then
                                    TempArray(j) = CDbl(FirstSplit(j - 1))
                                Else
                                    TempArray(j) = FirstSplit(j - 1)
                                End If
                            Next
                        End If
                    End If
                    Exit For
            End Select
        End If
    Next C
    
    
'Scindé = Ts  Non, ça pourrait apporter un petit + de l'écrire comme ça :
    If Application.Caller.Rows.Count > 1 Then
        SplitValue = WorksheetFunction.Transpose(TempArray)
    Else
        SplitValue = TempArray
    End If
End Function