Mettre en colonne les données d'un tableau type croisé

[seb26000]

Bonsoir à tous,

J'ai un tableau qui présente un format qui ne me permet pas de faire des analyses. J'ai besoin de le "décroiser" et de le remettre en tableau classique où les données seraient toutes en colonne.

Je vous joins un fichier où 2 onglets sont présents :
- Données : tableau actuel où les données sont présentes dans le tableau pseudo croisé
- Résultat : mise en forme des données que j'aimerai avoir (c'est un simple extrait, je souhaiterai que toutes les données soient mises dans cette forme là).


Je pense que ca doit passer par une macro pour tout décortiquer car en réalité j'ai pret de 500 lignes de données et non 13 comme dans le fichier joint. (ce qui devrait faire lorsque le fichier serait décortiquer : 500 * 12 (les 12 mois) * 5 (les 5 ans ->2012 à 2017) = 30000 lignes !!!

J'espère que vous pourrez m'aider !!

Bonne soirée !!

 

[piga25]

Re : Mettre en colonne les données d'un tableau type croisé

Bonjour,


Pourquoi ne pas simplement copier vos donnée puis de les coller sur une autre feuille en les transposant puis d'utiliser un filtre

 

[seb26000]

Re : Mettre en colonne les données d'un tableau type croisé

Bonsoir

Car cette liste peut etre amenée à évoluer sans que j'en sois informer donc le tableau ne pourrait être plus à jour et que il y a 500 ligne et non 13 comme dans le fichier donc ca peut etre tres longtemps et non industrialiser.

Bonne soirée

 

[mapomme]

Re : Mettre en colonne les données d'un tableau type croisé

Bonsoir seb26000, piga25, le forum,

Une version avec des formules.
Copier vers le bas les formules A3: D3 de la feuille "Resultat" vers le bas jusqu'à obtenir des cellules vides.

Formule en A3:

=SI(LIGNES($A$3:A3)>NBVAL(Noms)*72;"";INDEX(Noms;1+MOD((LIGNE()-LIGNE($A$3));NBVAL(Noms))))

Formule en B3:

=SI(A3="";"";INDEX(Données!$B$3:$BU$3;1+ENT((LIGNE()-LIGNE($A$3))/NBVAL(Noms))))

Formule en C3:

=SI(A3="";"";INDEX(Données!$B$4:$BU$4;1+ENT((LIGNE()-LIGNE($A$3))/NBVAL(Noms))))

Formule en D3:

=SI(A3="";"";SI(ESTVIDE(INDEX(DECALER(Données!$B$5:$BU$5;MOD((LIGNE()-LIGNE($A$3));NBVAL(Noms));0);1+ENT((LIGNE()-LIGNE($A$3))/NBVAL(Noms))));"";INDEX(DECALER(Données!$B$5:$BU$5;MOD((LIGNE()-LIGNE($A$3));NBVAL(Noms));0);1+ENT((LIGNE()-LIGNE($A$3))/NBVAL(Noms)))))

et un création d'un nom "Noms" dynamique de définition:

=DECALER(Données!$A$5;0;0;NBVAL(Données!$A$5:$A$10000);1)

 

[seb26000]

Re : Mettre en colonne les données d'un tableau type croisé

Merci beaucoup pour ton travail !!
J'ai craint juste qu'avec les 30 000 lignes excel mettent trop de temps à calculer (trop de duplication de formule)... Une solution VBA est il possible ?

Bonne soirée et merci de ta proposition

 

[mapomme]

Re : Mettre en colonne les données d'un tableau type croisé

(re)Bonsoir seb26000, le forum,

Dans ce cas une version VBA (code dans le module de la feuille "Résultat") :

Sub Deplier()
Dim Tablo(), N, i, ii, j, jj, Plage As Range, Courant As Range

Application.ScreenUpdating = False

With Sheets("Données")
  i = .Cells(Rows.Count, "a").End(xlUp).Row
  Set Plage = .Range("A3:BU" & i)
  Tablo = Plage.Value
End With

ii = UBound(Tablo, 1): jj = UBound(Tablo, 2)
Range("A3:D" & Rows.Count).ClearContents
Set Courant = Range("a3")
For j = 2 To jj
  For i = 3 To ii
    Courant(1, 1) = Tablo(i, 1)
    Courant(1, 2) = Tablo(1, j)
    Courant(1, 3) = Tablo(2, j)
    Courant(1, 4) = Tablo(i, j)
    Set Courant = Courant.Offset(1)
  Next i
Next j
Application.ScreenUpdating = True
End Sub

 

[mapomme]

Re : Mettre en colonne les données d'un tableau type croisé

(re)Bonsoir seb26000, le forum,

Une version VBA qui devrait être plus rapide que la précédente:

Sub Deplier()
Dim N, i, ii, j, jj, Plage As Range, Courant As Range
Dim Tablo(), Resultat()
Application.ScreenUpdating = False

With Sheets("Données")
  i = .Cells(Rows.Count, "a").End(xlUp).Row
  Set Plage = .Range("A3:BU" & i)
  Tablo = Plage.Value
End With

ii = UBound(Tablo, 1): jj = UBound(Tablo, 2): N = 1
ReDim Resultat(1 To (jj - 1) * (ii - 2), 1 To 4)
Range("A3:D" & Rows.Count).ClearContents
For j = 2 To jj
  For i = 3 To ii
    Resultat(N, 1) = Tablo(i, 1)
    Resultat(N, 2) = Tablo(1, j)
    Resultat(N, 3) = Tablo(2, j)
    Resultat(N, 4) = Tablo(i, j)
    N = N + 1
  Next i
Next j

Range("a3").Resize((jj - 1) * (ii - 2), 4).Value = Resultat
Application.ScreenUpdating = True
End Sub

 

[seb26000]

Re : Mettre en colonne les données d'un tableau type croisé

Bonsoir Mapomme,

Merci beaucoup de ton aide, c'est exactement cela. Est il possible que tu m'expliques le fonctionnement de ta macro ?

Sub Deplier()
Dim N, i, ii, j, jj, Plage As Range, Courant As Range
Dim Tablo(), Resultat() -> A quoi sert Tablo() et Resultat()
Application.ScreenUpdating = False

With Sheets("Données")
i = .Cells(Rows.Count, "a").End(xlUp).Row -> .cell sert à quoi ? rows.count (ca compte le nb de lignes) ? la combinaison .Cells(Rows.Count, "a") sert à quoi ?
Set Plage = .Range("A3:BU" & i) -> .range veut dire quoi ? est ce la produit du nb de ligne par le nb de colonnes afin d'avoir la taille du tableau final ?
Tablo = Plage.Value -> est ce le nb de ligne ?
End With

ii = UBound(Tablo, 1): jj = UBound(Tablo, 2): N = 1 -> aie je ne comprends pas...
ReDim Resultat(1 To (jj - 1) * (ii - 2), 1 To 4) aie je ne comprends pas à partir de là...
Range("A3" & Rows.Count).ClearContents
For j = 2 To jj
For i = 3 To ii
Resultat(N, 1) = Tablo(i, 1)
Resultat(N, 2) = Tablo(1, j)
Resultat(N, 3) = Tablo(2, j)
Resultat(N, 4) = Tablo(i, j)
N = N + 1
Next i
Next j

Range("a3").Resize((jj - 1) * (ii - 2), 4).Value = Resultat
Application.ScreenUpdating = True
End Sub

Merci encore MaPomme !!!

 

[mapomme]

Re : Mettre en colonne les données d'un tableau type croisé

Bonjour seb26000,

Des explications dans le fichier word joint.

Edit: v3a => v3b

 

[seb26000]

Re : Mettre en colonne les données d'un tableau type croisé

Merci énormement Mapomme, ton explication est vraiment complète !
Merci à toi et à tous ce qui ont tenté de m'aider !! Vous êtes trop fort !!

A+++

 

[seb26000]

Re : Mettre en colonne les données d'un tableau type croisé

Re bonsoir Mapomme,

J'ai voulu ajouté une fonction RECHERCHEV dans l'onglet résultat. Le hic, la macro est tres longue, elle doit mettre facilement 50s avant d'avoir de nouveau la main sur le fichier. J'ai du supprimer 200 lignes de l'onglet données afin de pouvoir joindre ce fichier (sinon la taille était de 3 Mo)
Peux tu voir si ma macro peut etre optimisée ?

Merci encore beaucoup !!

 

[mapomme]

Re : Mettre en colonne les données d'un tableau type croisé

Bonjour seb26000,

[...] J'ai voulu ajouté une fonction RECHERCHEV dans l'onglet résultat. Le hic, la macro est tres longue, elle doit mettre facilement 50s avant d'avoir de nouveau la main sur le fichier. J'ai du supprimer 200 lignes de l'onglet données afin de pouvoir joindre ce fichier (sinon la taille était de 3 Mo)Peux tu voir si ma macro peut etre optimisée ? [...]

Pour optimiser le code, j'ai appliqué les points suivants:

1. passage en calcul manuel en début de code (le calcul auto est réactivé en fin de code).
2. suppression de tous les Select. Les Select sont chronophages et très, très rarement indispensables.
3. Suppression de la boucle au profit de l'utilisation de range.

Sur ma p'tite bécane amoureusement montée par mes soins il ya déjà trois ans , le temps d'exécution (pour 280 lignes de données) est d'environ 0,7s.

On passe du code:

ii = UBound(Tablo, 1)
For i = 3 To ((ii - 2) * (jj - 1) + 2)
Cells(i, 5).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Tableau,2,FALSE)"
Cells(i, 6).Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Tableau,3,FALSE)"
Next i

au code:

Range("E3").Resize((jj - 1) * (ii - 2)).FormulaR1C1 = "=VLOOKUP(RC[-1],Tableau,2,FALSE)"
Range("F3").Resize((jj - 1) * (ii - 2)).FormulaR1C1 = "=VLOOKUP(RC[-2],Tableau,3,FALSE)"

 

[seb26000]

Re : Mettre en colonne les données d'un tableau type croisé

Merci Mapomme !!
Je viens d'essayer de faire un tableau croisé dynamique à partir des données présentes dans l'onglet Resultat. Je souhaitais mettre en champs de :
- ligne la variable "Poste" (colonne E de l'onglet Resultat),
- colonne la variable "Annee" puis "Mois"
- valeur la variable "Nom" pour faire figurer les noms disposant des memes triplets Annee, mois et poste

Le hic est qu'au lieu d'avoir les noms, j'ai le nombre de noms... Ca ne m'arrange pas trop.

Est il possible de faire figurer les noms et non le nombre de nom ? De plus, est il possible de mettre dans l'ordre les mois (autre que alphabetique)
Enfin est il possible de remplacer les codes erreur N/A# par la valeur HS ?


En tout cas merci encore ton aide m'est tres préciseuse !!!!!!!!

Bonne soirée

PS : j'ai enlevé de nombreuses lignes dans l'onglet Données afin de limiter la taille du fichier

 

[mapomme]

Re : Mettre en colonne les données d'un tableau type croisé

Bonjour seb26000,

Un essai brut de décoffrage sans optimisation (peut prendre un certain temps).
Dans l'ordre:
1) feuille "Données": convertir les lettres des mois en véritables dates
2) sélectionner la feuille "Resultat" pour la mise à jour
3) sur la feuille "TCD", actualiser le TCD
4) sur la feuille "TCD", cliquer sur le bouton