La fonction decaler()

Accueil

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

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

Une colonne est transformée en plusieurs lignes

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

2 lignes sont compactées sur 1 ligne

=DECALER($A$1;ENT((COLONNE()-COLONNE($G$4))/4)+(LIGNE()-LIGNE($G$4))*2;MOD(COLONNE()-COLONNE($G$4);4))

Transforme Tableau en BD

=DECALER(A$2;ENT((LIGNES($1:1)-1)/3);0)
=DECALER($B$1;;MOD(LIGNES($1:1)-1;3))
=DECALER(B$2;ENT((LIGNES($1:1)-1)/3);MOD(LIGNES($1:1)-1;3))

Autre exemple

Transforme Tableau BD

=DECALER(B$1;;ENT((LIGNES($1:1)-1)/7))
=DECALER(A$2;MOD(LIGNES($1:1)-1;7);)
=DECALER(B$2;MOD(LIGNES($1:1)-1;7);ENT((LIGNES($1:1)-1)/7))

2 Colonnes en 1 colonne

=DECALER($A$2;((LIGNE()-1)/2)-MOD(LIGNE();2);MOD(LIGNE();2))

Lignes en colonne

=DECALER($A$1;ENT((LIGNES($1:1)-1)/5);MOD((LIGNES($1:1)-1);5))

Transformation d'une ligne en colonne avec saut de colonne

TransformeLigneColonne

=DECALER($A$2;;LIGNES($1:1)-1+ENT((LIGNES($1:1)-1)/5))

Cumul au mois choisi

On veut le cumul des produits au mois choisi en A3.

En C5:=SOMME(DECALER(objectifs;EQUIV(A5;produits;0)-1;0;1;EQUIV($A$3;mois;0)))

Décalage de blocs dans une colonne

On veut décaler les blocs en colonne B

-Sélectionner B7:J7
=DECALER(B2:J2;0;MIN(SI(B2:Z2<>"";COLONNE(B2:Z2)))-2)
Valider avec Maj+Ctrl+Entrée

Moyenne des 3 meilleurs sur les 12 derniers mois:

=MOYENNE(GRANDE.VALEUR(DECALER(B2;NBVAL(B:B)-13;0;12);{1.2.3}))

MFC:
-Sélectionner la colonne A
-Format/Mise en forme conditionnelle

=ET(LIGNE()>NBVAL(A:A)-12;LIGNE()<=NBVAL(A:A))

 

 

 

 

 


Exemples

Liste dynamique
Décaler synthèse
DécalerEquivIndex