La fonction decaler()

La fonction Decaler()
Somme n mois
Somme jusqu'au mois en cours
Somme des 6 derniers montants
Liste déroulante dynamique
Transformation tableau
TRansforme BD en Tableau
Transformation tableau en BD
Cumul au mois choisi
Décalage de blocs dans une colonne
Moyenne 3 meilleurs sur 12 derniers mois

DECALER(référence;décalageNbLignes;décalageNbColonnes;hauteur;largeur)

Décaler Synthèse

La fonction Decaler() permet de définir un champ variable.

hauteur et largeur définissent la hauteur et la largeur du champ.
décalageNbLignes et décalageNbColonnes définissent un déplacement par rapport à une référence de cellule.

Somme de n mois

Sur cet exemple, onveut calculer la somme des n premiers mois. Le nombre de mois est dans la cellule D2.

=SOMME(DECALER(B2;0;0;D2;1))

Somme jusqu'au mois en cours

En N2: =SOMME(DECALER(B2;;;;MOIS(AUJOURDHUI())))

MFC:
-Sélectionner B2:M8
-Format/Mise en forme conditionnelle/La formule est
=COLONNE()<=MOIS(AUJOURDHUI())+1

Variante: Le mois est choisi dans une liste

En N2: =SOMME(DECALER(B2;;;;EQUIV($Q$2;$B$1:$M$1;0)))

Somme des 6 derniers montants

En C2: =SOMME(DECALER(A2;NBVAL(A:A)-7;0;6))

MFC:
-Sélectionner la colonne A
-Format/Mise en forme conditionnelle/La formule est
=ET(LIGNE()>NBVAL(A:A)-6;LIGNE()<=NBVAL(A:A))

Liste déroulante dynamique

Sur l'exemple, la liste déroulante en B2 est alimentée par la liste des services en colonne F.
Si des services sont ajoutés en colonne E, la liste déroulante est automatiquement mise à jour.

-Sélectionner B2
-Données/Validation
-Choisir Liste
-Cliquer dans Source

=DECALER($F$2;0;0;NBVAL($F:$F)-1)

Récupération d'informations

Un onglet Produits contient les prix des produits pour les mois 01,02,03,..
Sur l'onglet de chaque mois, on veut récupérer les prix du mois.

Si A1 contient le no de mois:
=DECALER(Produits!C3;;A1-1)

Sans le no de Mois en A1:=DECALER(Produits!C3;;STXT(CELLULE("filename";A1);TROUVE("]";CELLULE("filename";A1))+1;99)-1)

Decaler

SommeSi et decaler

On veut la somme des colonnes B,C,D pour lesquelles les lignes contient X en colonne A.

Somme.Si Plusieurs Colonnes

=SOMMEPROD(SOMME.SI(A2:A10;"x";DECALER(B2:B10;;{0;1;2})))

Transformation d'un tableau

Plusieurs colonnes en 1 colonne

En F2: =DECALER(A$2;MOD(LIGNES($1:1)-1;LIGNES(champ));ENT((LIGNES($1:1)-1)/(LIGNES(champ))))

Plusieurs colonnes en 1 colonne

En F2: =DECALER($A$2;ENT((LIGNES($1:1)-1)/3);(LIGNES($1:1)-1)-ENT((LIGNES($1:1)-1)/3)*3)

Autre exemple

En F2: =DECALER($B$1;;MOD(LIGNES($1:1)-1;2))
En G2:=DECALER(A$2;ENT((LIGNES($1:1)-1)/2);0)
En H2:=DECALER(B$2;ENT((LIGNES($1:1)-1)/2);MOD(LIGNES($1:1)+1;2))

TransTableau

En VBA

Sub transpose()
  ligne = 2
  For Each c In Range([A2], [A65000].End(xlUp))
    Cells(ligne, 10) = [B1]
    Cells(ligne, 11) = c
    Cells(ligne, 12) = c.Offset(, 1)
    ligne = ligne + 1
    Cells(ligne, 10) = [C1]
    Cells(ligne, 11) = c
    Cells(ligne, 12) = c.Offset(, 2)
    ligne = ligne + 1
  Next c
End Sub

Autre exemple

En F1:=DECALER($A$2;(LIGNES($1:1)-1)*2;COLONNES($A:A)-1)

Autre exemple

En D1:=DECALER($A$1;ENT((LIGNES($1:1))/2);)

En G1:=DECALER($A$1;;ENT((LIGNES($1:1))/2))

Une colonne est transformée en plusieurs lignes

En C1: =DECALER($A$1;(LIGNES(A$1:A1)-1)*4+COLONNES($A:A)-1;)

Decaler Colonne Ligne
Transpose Fiche BD

1 colonne en 1 ligne :=DECALER($A$1;COLONNES($A:A)-1;0)

Une colonne est transformée en plusieurs lignes:

Bloc de 4, 1 ligne sur 2:=DECALER($A$1;(ENT(LIGNES(A$1:A1)-1))*(4/2)+(COLONNES($A:A)-1);0)
Bloc de 6, 1 ligne sur 3: =DECALER($A$1;(ENT(LIGNES(A$1:A1)-1))*(6/3)+(COLONNES($A:A)-1);0)
Bloc de 4 toutes les lignes:=DECALER($A$1;(ENT(LIGNES(A$1:A1)-1))*4+(COLONNES($A:A)-1);0)

Decaler ligne colonne
Decaler ligne colonne VBA

Transforme BD en colonne

En E1: =DECALER($A$1;;MOD(LIGNES($1:1)-1;3))
En F1: =DECALER(A$2;ENT((LIGNES($1:1)-1)/3);MOD(LIGNES($1:1)-1;3))

Decaler BD Colonne

En VBA:

Sub Essai()
  TblTitre = [A1:C1]
  n = [A65000].End(xlUp).Row - 1
  TblE = Range("A2:C" & n + 1)
  Dim TblS(1 To 12, 1 To 2)
  For i = 1 To n
    For k = 1 To 3
      TblS((i - 1) * 3 + k, 2) = TblE(i, k)
      TblS((i - 1) * 3 + k, 1) = TblTitre(1, k)
    Next k
  Next i
  [H1].Resize(n * 3, 2) = TblS
End Sub

On regroupe des infos dispersées dans une colonne

=DECALER($B$2;3*ENT((LIGNES($1:1)-1)/3);MOD(4*(LIGNES($1:1)-1);12))

Decaler

Autre exemple

Tableau récapitulatif.

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

Decaler37

Transforme BD en tableau

En D2: =DECALER($A$2;(LIGNES($2:2)-1)*4;0)
En E2: =DECALER($B$2;((LIGNES($2:2)-1)*4)+COLONNES($B:B)-1;0)

Autre exemple:

En F2:=DECALER(A$2;(LIGNES($2:2)-1)*3;0)
En I2:=DECALER($D$2;((LIGNES($2:2)-1)*3)+COLONNES($B:B)-1;0)

Autre exemple

=DECALER($B$2;3-LIGNES($1:1);3-COLONNES($A:A))

Autre exemple:

Transforme Ligne Colonne

On veut transformer un tableau de 12 colonnes (2x6) en tableau 2 colonnes.
Une cellule sur 2 du tableau source va dans la colonne de gauche du tableau à 2 colonnes.

En B9: =DECALER($B$2;ENT((LIGNES($1:1)-1)/6);(LIGNES($1:1)-1)*2-ENT((LIGNES($1:1)-1)/6)*12)
En C9: =DECALER($B$2;ENT((LIGNES($1:1)-1)/6);(LIGNES($1:1)-1)*2+1-ENT((LIGNES($1:1)-1)/6)*12)

=DECALER($B$2;nb_lignes;nb_colonnes)

Pour les 6 premières lignes du résultat:
-on se déplace de 0 ligne dans la source :ENT((LIGNES($1:1)-1)/6) -->0
-on se déplace de x colonnes dans la source:LIGNES($1:1)-1)*2 --> 0,2,4,6,....
Pour les lignes 7 à 12 du résultat:
-on se déplace d'une ligne dans la source: ENT((LIGNES($1:7)-1)/6) -->1
-on se déplace de x colonnes dans la source:LIGNES($1:7)-1)*2-ENT((LIGNES($1:7)-1)/6)*12) --> 0,2,4,..
ENT((LIGNES($1:7)-1)/6)*12 retranche 12 au nb de colonnes.

Avec une fonction personnalisée matricielle