Microsoft 365 Transformer une chaines de données en tableau dynamique

[leolo7]

Bonjour,

J'arrive avec cette chaine de données pour exemple :

(Groupe_A; Apple; 67; 33; 7; Groupe_A; Bravo; 54; 23; 3; Groupe_A; Tony; 1; 1; 1; Groupe_A; Charlie; 94; 40; 9; Groupe_A; Thomas; 100; 40; 10).​

Je veux transformer cette chaine en un tableau dynamique sur lequel je pourrai faire des analyses. J'utilise la dernière version de Microsoft 365 pour Mac.

Comme celui-c.

Si possible, j'aimerais que ce tableau ait une en-tete.

 

[djidji59430]

Bonjour à tous,

Avec power query

 

[leolo7]

Bonjour à tous,

Avec power query

Merci,

Excel sur Mac ne semble pas avoir les mêmes possibilités avec Power Query que la version Windows.

Voici les seuls choix disponibles. Je n'ai pas l'option pour "Obtenir les données". À moins que je ne sois pas au bon endroit dans Excel.

 

[AtTheOne]

Bonsoir @leolo7

Avec une fonction et l’évènement Worksheet_Change de la feuille on obtient le résultat souhaité dynamiquement. (le tableau structuré suit l'évolution de la chaîne transmise)
Il faut réserver une place pour le résultat de la fonction dynamique.

Cordialement
Alain

 

[djidji59430]

Merci,

Excel sur Mac ne semble pas avoir les mêmes possibilités avec Power Query que la version Windows.

Voici les seuls choix disponibles. Je n'ai pas l'option pour "Obtenir les données". À moins que je ne sois pas au bon endroit dans Excel.

As-tu essayé" à partir d'une base de donnée" ? Là, tu dois encore avoir un choix

Crdlmt

 

[leolo7]

As-tu essayé" à partir d'une base de donnée" ? Là, tu dois encore avoir un choix

Crdlmt

Lorsque j'essaie, j'ai le message d'erreur suivant :
Microsoft Excel ne peut pas terminer cette opération. L’administrateur de Microsoft interface ODBC n’est pas installé. Pour l’installer, exécutez le programme d’installation et installez le pilote correspondant au type de base(s) de données auquel vous voulez accéder.

Le lien m'amène sur cette page (https://support.microsoft.com/fr-fr...pour-mac-9fa6bc7f-d19e-4f7f-9be4-92e85c77d712) et là je suis complètement perdu.

Merci

 

[djidji59430]

re

Ils disent qu'il y a un truc a vérifier avant
regarde

 

[leolo7]

Bonsoir @leolo7

Avec une fonction et l’évènement Worksheet_Change de la feuille on obtient le résultat souhaité dynamiquement. (le tableau structuré suit l'évolution de la chaîne transmise)
Il faut réserver une place pour le résultat de la fonction dynamique.

Cordialement
Alain

Bonjour,

J'y suis presque.

Tu m'as bien fait travailler.

Après quelques heures de travail , j'ai réussi à déchiffrer ton code pour l'importer dans mon fichier. Dans mon fichier original, il y a 38 colonnes. J'ai donc modifié ton code en conséquence.

J'ai réussi à transposer la chaîne de texte dans le tableau structuré (Résult), Par ailleurs, le tableau dynamique final ne se met pas à jour. J'ai essayé d'ajouter manuellement une colonne, mais ça ne donne pas de résultat.

Merci pour ton aide.

 

[leolo7]

re

Ils disent qu'il y a un truc a vérifier avant
regarde

Super, je vais regarder ça, mais je ne suis pas certain que je veuille utiliser une version bêta.

Merci

 

[AtTheOne]

Bonsoir @leolo7

@leolo7 a dit

Après quelques heures de travail , j'ai réussi à déchiffrer ton code pour l'importer dans mon fichier. Dans mon fichier original, il y a 38 colonnes. J'ai donc modifié ton code en conséquence.

J'ai réussi à transposer la chaîne de texte dans le tableau structuré (Résult), Par ailleurs, le tableau dynamique final ne se met pas à jour. J'ai essayé d'ajouter manuellement une colonne, mais ça ne donne pas de résultat.

J'ai fait quelques modifications

Tout d'abord j'ai supprimé les accents de mes noms (Noms définis, Fonctions, variables...), ça se passait mal avec les allés-retours entre Mac et Windows.

J'ai nommé la cellule qui contient la chaîne à convertir "Chaine_Source".

J'ai commenté le code.

La mise à jour du tableau "Tb_Result" se fait directement par macro (sans formule).
Donc la cellule "Result_Fonction" contenant le résultat de la fonction n'est plus utile (c'est de la place gagnée sur la feuille). Je l'ai laissé mais tu peux supprimer cette zone devenue inutile.

La fonction ChaineToTableau dépend toujours de la constante NbCol déclarée en début de code.

Public StopCalc As Boolean

Function ChaineToTableau(Chaine As String) As Variant
Application.Volatile

Const NbCol As Byte = 38     'Nombre de colonnes par enregistrement
Const Sep As String = "; "   'Séparateur de la chaîne ( ; + espace)

Dim TbBrut, TbRes As Variant
Dim i As Integer, j As Integer, k As Integer
 
     'Ne pas faire le calcul si l'appel est provoqué par l'évènement Worksheet_Change de Feuil1
     If StopCalc Then Exit Function
 
     'Supprimer les parenthèse qui entourent la chaîne
     Chaine = Replace(Replace(Chaine, "(", ""), ")", "")
 
     'Fractionner la chaîne suivant le séparateur dans le tableau brut à une seule dimension
     TbBrut = Split(Chaine, Sep)
 
     'Calcul du nombre d'enregistrements
     Nb_Enrgt = (UBound(TbBrut) + 1) / NbCol
 
     'Dimensionnement du tableau résultat à 2 dimensions
     ReDim TbRes(1 To Nb_Enrgt, 1 To NbCol)
 
     k = 0     'Index pour le parcourt de TbBrut
     For i = 1 To Nb_Enrgt                             'Changement de ligne
          For j = 1 To NbCol                           'Changement de colonne
               If IsNumeric(TbBrut(k)) Then
                    TbRes(i, j) = CLng(TbBrut(k))      'Conversion en nombre si nombre sous forme de chaîne
               Else
                    TbRes(i, j) = TbBrut(k)            'Tel quel sinon
               End If
               k = k + 1
          Next
     Next
 
     ChaineToTableau = TbRes                          'La fonction renvoie le tableau

End Function

L'événement Worksheet_Change de Feuil1 surveille les modifications de la cellule "Chaine_Source".

Le code appelle la fonction ChaineToTableau et stocke les résultats dans un tableau brut.
Ensuite il fait le ménage sur le contenu du tableau "Tb_Result", le redimensionne en fonction du nombre de lignes et de colonnes trouvées par la fonction.
Puis il charge le tableau avec le résultat de la fonction (plus de formule dans le tableau "Tb_Result")

Private Sub Worksheet_Change(ByVal Target As Range)
 
     'Si l'évènement n'est pas provoqué par une modification de la chaîne Sortir
     If Intersect(Target, Feuil1.[Chaine_Source]) Is Nothing Then Exit Sub
 
     Dim tb, Nb_Col As Integer, Nb_Enrgt As Long, OldSize As Long, NewSize As Long, OldCol As Integer

     'Remplir tb avec le résultat de la fonction ChaineToTableau
     tb = ChaineToTableau(Feuil1.[Chaine_Source])      'Chaine_Source : emplacement contenant la chaîne dans Feuil1
 
     'Dimensions du résultat
     Nb_Enrgt = UBound(tb, 1) - LBound(tb, 1) + 1
     Nb_Col = UBound(tb, 2) - LBound(tb, 2) + 1

     'Inhiber le calcul de la fonction "ChaineToTableau" pendant les modifications du tableau "Tb_Result"
     StopCalc = True
     Application.EnableEvents = False
 
     With Feuil1.ListObjects("Tb_Result")  'Actions sur le Tableau "Tb_Result"
      
          OldSize = .Range.Rows.Count - Abs(.ShowHeaders) - Abs(.ShowTotals)       'Nb lignes de données
          OldCol = .ListColumns.Count                                              'Nb colonnes de données

         'Effacer les lignes actuelles
          .Range.Offset(Abs(.ShowHeaders)).Resize(1).ClearContents                 'Effacer le contenu de la 1ere ligne de données
          .Range.Offset(Abs(.ShowHeaders) + 1).Resize(OldSize - 1).ClearContents   'Supprimer sauf 1ere ligne de données

         'Ramener le Listobject à 2 colonnes, effacer les anciennes données
          If OldCol > 2 Then
               .Resize .Range.Resize(, 2)
               .Range.Offset(Abs(.ShowHeaders)).Resize(OldSize - Abs(.ShowHeaders) - Abs(.ShowTotals), OldCol).Clear
          End If

          'Redimensionner le tableau en fonction du resultat de la fonction ChaineToTableau
          NewSize = Nb_Enrgt + Abs(.ShowHeaders) + Abs(.ShowTotals)
          .Resize .Range.Resize(NewSize, Nb_Col)

          'Renommer les colonnes 3 à Nb_Col
          For i = 3 To Nb_Col: .HeaderRowRange.Columns(i).Value = "Exam" & i: Next

          'Charger les données
          .Range.Offset(Abs(.ShowHeaders)).Resize(Nb_Enrgt).Value = tb


     End With
 
     Application.EnableEvents = True
     StopCalc = False
 
     Feuil1.Calculate 'Pour recalculer le zone utilisant la fonction ChaineToTableau
 

End Sub

Voilà, fais des essais avec le fichier joint et tiens moi informé des résultats

Bon courage

Alain

 

[leolo7]

Bonsoir @leolo7

@leolo7 a dit



J'ai fait quelques modifications

Tout d'abord j'ai supprimé les accents de mes noms (Noms définis, Fonctions, variables...), ça se plaçait mal avec les allés-retours entre Mac et Windows.

J'ai nommé la cellule qui contient la chaîne à convertir "Chaine_Source".

J'ai commenté le code.

La mise à jour du tableau "Tb_Result" se fait directement par macro (sans formule).
Donc la cellule "Result_Fonction" contenant le résultat de la fonction n'est plus utile (c'est de la place gagnée sur la feuille). Je l'ai laissé mais tu peux supprimer cette zone devenue inutile.

La fonction ChaineToTableau dépend toujours de la constante NbCol déclarée en début de code.

Public StopCalc As Boolean

Function ChaineToTableau(Chaine As String) As Variant
Application.Volatile

Const NbCol As Byte = 38     'Nombre de colonnes par enregistrement
Const Sep As String = "; "   'Séparateur de la chaîne ( ; + espace)

Dim TbBrut, TbRes As Variant
Dim i As Integer, j As Integer, k As Integer
 
     'Ne pas faire le calcul si l'appel est provoqué par l'évènement Worksheet_Change de Feuil1
     If StopCalc Then Exit Function
 
     'Supprimer les parenthèse qui entourent la chaîne
     Chaine = Replace(Replace(Chaine, "(", ""), ")", "")
 
     'Fractionner la chaîne suivant le séparateur dans le tableau brut à une seule dimension
     TbBrut = Split(Chaine, Sep)
 
     'Calcul du nombre d'enregistrements
     Nb_Enrgt = (UBound(TbBrut) + 1) / NbCol
 
     'Dimensionnement du tableau résultat à 2 dimensions
     ReDim TbRes(1 To Nb_Enrgt, 1 To NbCol)
 
     k = 0     'Index pour le parcourt de TbBrut
     For i = 1 To Nb_Enrgt                             'Changement de ligne
          For j = 1 To NbCol                           'Changement de colonne
               If IsNumeric(TbBrut(k)) Then
                    TbRes(i, j) = CLng(TbBrut(k))      'Conversion en nombre si nombre sous forme de chaîne
               Else
                    TbRes(i, j) = TbBrut(k)            'Tel quel sinon
               End If
               k = k + 1
          Next
     Next
 
     ChaineToTableau = TbRes                          'La fonction renvoie le tableau

End Function

L'événement Worksheet_Change de Feuil1 surveille les modifications de la cellule "Chaine_Source".

Le code appelle la fonction ChaineToTableau et stocke les résultats dans un tableau brut.
Ensuite il fait le ménage sur le contenu du tableau "Tb_Result", le redimensionne en fonction du nombre de lignes et de colonnes trouvées par la fonction.
Puis il charge le tableau avec le résultat de la fonction (plus de formule dans le tableau "Tb_Result")

Private Sub Worksheet_Change(ByVal Target As Range)
 
     'Si l'évènement n'est pas provoqué par une modification de la chaîne Sortir
     If Intersect(Target, Feuil1.[Chaine_Source]) Is Nothing Then Exit Sub
 
     Dim tb, Nb_Col As Integer, Nb_Enrgt As Long, OldSize As Long, NewSize As Long, OldCol As Integer

     'Remplir tb avec le résultat de la fonction ChaineToTableau
     tb = ChaineToTableau(Feuil1.[Chaine_Source])      'Chaine_Source : emplacement contenant la chaîne dans Feuil1
 
     'Dimensions du résultat
     Nb_Enrgt = UBound(tb, 1) - LBound(tb, 1) + 1
     Nb_Col = UBound(tb, 2) - LBound(tb, 2) + 1

     'Inhiber le calcul de la fonction "ChaineToTableau" pendant les modifications du tableau "Tb_Result"
     StopCalc = True
     Application.EnableEvents = False
 
     With Feuil1.ListObjects("Tb_Result")  'Actions sur le Tableau "Tb_Result"
      
          OldSize = .Range.Rows.Count - Abs(.ShowHeaders) - Abs(.ShowTotals)       'Nb lignes de données
          OldCol = .ListColumns.Count                                              'Nb colonnes de données

         'Effacer les lignes actuelles
          .Range.Offset(Abs(.ShowHeaders)).Resize(1).ClearContents                 'Effacer le contenu de la 1ere ligne de données
          .Range.Offset(Abs(.ShowHeaders) + 1).Resize(OldSize - 1).ClearContents   'Supprimer sauf 1ere ligne de données

         'Ramener le Listobject à 2 colonnes, effacer les anciennes données
          If OldCol > 2 Then
               .Resize .Range.Resize(, 2)
               .Range.Offset(Abs(.ShowHeaders)).Resize(OldSize - Abs(.ShowHeaders) - Abs(.ShowTotals), OldCol).Clear
          End If

          'Redimensionner le tableau en fonction du resultat de la fonction ChaineToTableau
          NewSize = Nb_Enrgt + Abs(.ShowHeaders) + Abs(.ShowTotals)
          .Resize .Range.Resize(NewSize, Nb_Col)

          'Renommer les colonnes 3 à Nb_Col
          For i = 3 To Nb_Col: .HeaderRowRange.Columns(i).Value = "Exam" & i: Next

          'Charger les données
          .Range.Offset(Abs(.ShowHeaders)).Resize(Nb_Enrgt).Value = tb


     End With
 
     Application.EnableEvents = True
     StopCalc = False
 
     Feuil1.Calculate 'Pour recalculer le zone utilisant la fonction ChaineToTableau
 

End Sub

Voilà, fais des essais avec le fichier joint et tiens moi informé des résultats

Bon courage

Alain

OMG! Merci énormément . Je me mets au boulot. Je te tiens au courant.