Formule DECALER

[fablog]

Bonjour tout le monde,

J'ai de nouveau besoin de votre aide concernant la formule DECALER, dont je ne comprends toujours pas la syntaxe.

Ci-joint un exemple de ce que je voudrais faire, dans lequel les données en rouge seront la recopie des données en bleu, grâce à la formule DECALER. Avec DECALER je pourrait faire une recopier incrémentée par la suite d'où la nécessité de cette formule.

Merci par avance pour votre aide.

Fabien

 

[CISCO]

Re : Formule DECALER

Bonjour

Essaye avec

=DECALER($D$3;EQUIV($F5;$B$4:$B$14;0)-2+EQUIV(G$4;$C$4:$C$6;0);)

formule que l'on peut copier-coller vers le bas et vers la droite.

Le dernier point-virgule est indispensable, même si on n'indique pas de décalage de colonne.

@ plus

 

[job75]

Re : Formule DECALER

Bonjour fablog, CISCO,

Sélectionner G5:I5 et entrer la formule =TRANSPOSE(DECALER(D$1;EQUIV(F5;B:B;0)-2;;3))

Validation matricielle par Ctrl+Maj+Entrée.

Copier/Collage spécial-Formules de G5:I5 sur G6:I7.

Fichier joint.

A+

 

[fablog]

Re : Formule DECALER

Merci Cisco et job75 pour votre aide. job75, j'ai réussi a reproduire dans le même fichier d'exemple cette matrice mais pourrais tu m'expliquer chaque élément de cette formule pour que je puisse l'appliquer à mon vrai fichier de travaille stp?

Merci!

 

[BOISGONTIER]

Re : Formule DECALER

Bonsoir,

=DECALER($D$4;(LIGNES($1:1)-1)*4+COLONNES($A:A)-1;)

Pour récupérer la première colonne 1,2,3

=DECALER($D$4;(LIGNES($1:1)-1)*4;)

Pour récupérer les autres colonnes, on ajoute COLONNES($A:A)-1

La fonction decaler()


JB

 

[job75]

Re : Formule DECALER

Re,

DECALER(D$1;EQUIV(F5;B:B;0)-2;;3) est une plage verticale :

- de hauteur 3 (cellules)

- dont le début est déterminé par EQUIV(F5;B:B;0)-2 qui décale D1 vers le bas.

TRANSPOSE transforme ce vecteur vertical en un vecteur horizontal.

A+

 

[fablog]

Re : Formule DECALER

Merci BOISGONTIER, votre formule fonctionne parfaitement en recopie incrémentée, malheureusement mon document Excel permet de rajouter une personne par une macro mais votre formule ne s'incrémente pas. C'est certainement mon fichier qui est mal conçu mais je ne peux le changer à cause du temps que cela me prendrait.

job75, je pense que je vais devoir explorer l'utilisation d'une matrice. Merci pour le détail de votre formule mais j'avoue que je suis quand même perdu. Auriez vous l'amabilité de m'indiquer la formule pour le fichier ci-joint qui est la version originale de mon document? Le tableau que je veux compléter grâce à une formule est dans la feuille Traitement4.

Merci par avance.

 

[CISCO]

Re : Formule DECALER

Bonsoir

Perso, je trouve ma formule assez simple à utiliser. Si ton tableau évolue dans le temps, il suffit d'utiliser à la place de $B$4:$B$14 une plage dynamique définie par l'intermédiaire du gestionnaire de noms (dans le fichier en pièce jointe, jusque la ligne 1000).

@ plus

 

[job75]

Re : Formule DECALER

Re fablog,

Votre base de donnée en feuille Traitement4 est organisée en Tableau Excel.

Dans ce cas une formule matricielle sur plusieurs cellules n'est pas possible.

Adaptez donc les formules de CISCO ou de BOISGONTIER.

A+

 

[job75]

Re : Formule DECALER

Re,

La formule de JB (la plus simple) s'adapte très bien et s'incrémente.

En Traitement4!C16 :

=DECALER(Traitement1!$DT$9;(LIGNES(C$15:C15)-1)*23+COLONNES($C15:C15)-1;)

Fichier joint.

Bonne nuit.

 

[job75]

Re : Formule DECALER

Bonjour fablog, CISCO, JB, le forum,

S'il s'agit de remplir le tableau en feuille "Traitement4" voici une solution VBA :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B:B,DT:DT]) Is Nothing Then Exit Sub
Dim s1 As Range, s2 As Range, dest As Range, h&, c As Range, d&
Set s1 = [B5] 'cellule source, à adapter
Set s2 = [DT9:DT23] 'plage source, à adapter
Set dest = Sheets("Traitement4").[B16] 'destination, à adapter
h = s2.Count
On Error Resume Next 'si aucune SpecialCells
For Each c In s1.Resize(Rows.Count - s1.Row + 1) _
  .SpecialCells(xlCellTypeConstants)
  dest = c
  d = c.Row - s1.Row 'décalage
  dest(, 2).Resize(, h) = Application.Transpose(s2.Offset(d).Value)
  Set dest = dest(2)
Next
dest.Parent.Rows(dest.Row & ":" & Rows.Count).Delete
End Sub

Cette macro est à placer en feuille "Traitement1".

Dans cette feuille modifiez/validez une cellule quelconque en colonne B ou DT.

Fichier joint.

Bonne journée.

 

[job75]

Re : Formule DECALER

Re,

La macro précédente suppose qu'il n'y a que les noms sous la cellule source B5.

S'il y a d'autres données il faut utiliser un autre critère de détermination, par exemple le pas de 23 :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B:B,DT:DT]) Is Nothing Then Exit Sub
Dim s1 As Range, s2 As Range, dest As Range, pas&, h&, d&
Set s1 = [B5] 'cellule source, à adapter
Set s2 = [DT9:DT23] 'plage source, à adapter
Set dest = Sheets("Traitement4").[B16] 'destination, à adapter
pas = 23 'pas à adapter
h = s2.Count
For d = 0 To Cells(Rows.Count, s1.Column).End(xlUp).Row - s1.Row Step pas
  If s1(d + 1) <> "" Then
    dest = s1(d + 1)
    dest(, 2).Resize(, h) = Application.Transpose(s2.Offset(d).Value)
    Set dest = dest(2)
  End If
Next
dest.Parent.Rows(dest.Row & ":" & Rows.Count).Delete
End Sub

Edit : on n'utilise plus SpecialCells.

Fichier (2).

A+

 

[job75]

Re : Formule DECALER

Re,

Surtout s'il y a beaucoup de noms, on peut préférer une macro Worksheet_Activate :

Private Sub Worksheet_Activate()
Dim s1 As Range, s2 As Range, dest As Range, pas&, h&, derlig&, d&
With Sheets("Traitement1")
  Set s1 = .[B5] 'cellule source, à adapter
  Set s2 = .[DT9:DT23] 'plage source, à adapter
  Set dest = [B16] 'destination, à adapter
  pas = 23 'pas à adapter
  h = s2.Count
  derlig = .Cells(.Rows.Count, s1.Column).End(xlUp).Row
End With
Application.ScreenUpdating = False
For d = 0 To derlig - s1.Row Step pas
  If s1(d + 1) <> "" Then
    dest = s1(d + 1)
    dest(, 2).Resize(, h) = Application.Transpose(s2.Offset(d))
    Set dest = dest(2)
  End If
Next
Rows(dest.Row & ":" & Rows.Count).Delete
End Sub

A placer dans le code de la feuille "Traitement4".

Fichier (3).

A+

 

[job75]

Re : Formule DECALER

Re,

Avec 1000 noms, sur Win 8 - Excel 2013, la macro précédente s'exécute en 3,2 secondes.

C'est acceptable, mais sur des tableaux plus grands il faudrait utiliser des tableaux VBA pour aller plus vite.

A+

 

[job75]

Re : Formule DECALER

Re,

Alors voici la solution par tableaux VBA, beaucoup plus rapide :

Private Sub Worksheet_Activate()
Dim s1 As Range, s2 As Range, dest As Range, pas&
Dim h%, derlig&, d&, n&, a(), i%, b(), j&
With Sheets("Traitement1")
  Set s1 = .[B5] 'cellule source, à adapter
  Set s2 = .[DT9:DT23] 'plage source, à adapter
  Set dest = [B16] 'destination, à adapter
  pas = 23 'pas à adapter
  h = s2.Count
  derlig = .Cells(.Rows.Count, s1.Column).End(xlUp).Row
End With
'---remplissage du tableau VBA---
For d = 0 To derlig - s1.Row Step pas
  If s1(d + 1) <> "" Then
    ReDim Preserve a(h, n) 'base 0
    a(0, n) = s1(d + 1)
    For i = 1 To h
      a(i, n) = s2.Offset(d)(i)
    Next
    n = n + 1
  End If
Next
'---transposition du tableau VBA---
ReDim b(n, h) 'base 0
For j = 0 To n - 1
  For i = 0 To h
    b(j, i) = a(i, j)
  Next
Next
'---restitution---
If n Then dest.Resize(n, h + 1) = b
Rows(dest(n + 1).Row & ":" & Rows.Count).Delete
End Sub

Fichier (4).

Avec 1000 noms => 0,32 seconde, donc 10 fois plus rapide que la version (3).

A+