Un cercle dans un graphique

Directe­ment inspiré de cet arti­cle, voici un petit fichi­er qui per­met de représen­ter un cer­cle dans un graphique.

Çà pour­rait être utile, sait-on jamais 🙂

La magie du fichi­er se trou­ve dans la créa­tion de noms (For­mules — Ges­tion­naires de noms)

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

Dépivotez un tableau avec une seule formule (Office 365)

Avec l’in­tro­duc­tion des fonc­tions matricielles dynamiques et de la fonc­tion LET, Excel devient de plus en plus un lan­gage de pro­gram­ma­tion matriciel (je ne sais pas si ça existe, mais ça sonne très bien 🙂 ). 

LET per­met de stock­er l’équiv­a­lent de vari­ables pour les réu­tilis­er plusieurs fois dans la for­mule. La beauté de la fonc­tion LET est qu’il suf­fit juste de chang­er les pre­miers paramètres pour adapter la fonc­tion à n’im­porte quel tableau.

Tableau simple

L’ob­jec­tif ici est dépiv­ot­er ce tableau qui con­tient une colonne Ville, et 4 colonnes de dates : 

Il existe d’autres méth­odes dis­cutées ici, mais vous pou­vez utilis­er cette for­mule pour dépiv­ot­er le tableau : 

=LET(T_Lignes;Tableau1[Ville];T_Colonnes;Tableau1[[#En-têtes];[2019]:[2022]];T_Data;Tableau1[[2019]:[2022]]; T_Nb_Lignes;LIGNES(T_Lignes);T_Nb_Colonnes;COLONNES(T_Colonnes); Index_Ligne;MOD(SEQUENCE(T_Nb_Lignes*T_Nb_Colonnes)-1;T_Nb_Lignes)+1; Index_Colonne;RECHERCHEV(SEQUENCE(T_Nb_Colonnes*T_Nb_Lignes;1);CHOISIR({1.2};SEQUENCE(T_Nb_Colonnes;1;1;T_Nb_Lignes);SEQUENCE(T_Nb_Colonnes;1;1;1));2;1); CHOISIR({1.2.3}; INDEX(T_Lignes;Index_Ligne); INDEX(T_Colonnes;1;Index_Colonne); INDEX(T_Data;Index_Ligne;Index_Colonne)))

L’ex­pli­ca­tion de la for­mule est un peu longue, mais en gros, on cal­cule les coor­don­nées lignes et colonne de chaque titre ou valeur. Puis, avec la fonc­tion INDEX, on recon­stru­it les dif­férentes colonnes. La fonc­tion CHOISIR per­met de les fusionner.

Plus de colonnes à gauche

On peut aller plus loin avec deux colonnes, par exem­ple la ville et le pays. La for­mule ci-dessous doit prob­a­ble­ment encore pou­voir être opti­misée pour sélec­tion­ner la plage au com­plet, plutôt que deux colonnes dis­tinctes (Ville et Pays).

=LET(T_Lignes;Tableau1[Ville];T_Lignes2;Tableau1[Pays];T_Colonnes;Tableau1[[#En-têtes];[2019]:[2022]];T_Data;Tableau1[[2019]:[2022]]; T_Nb_Lignes;LIGNES(T_Lignes);T_Nb_Colonnes;COLONNES(T_Colonnes); Index_Ligne;MOD(SEQUENCE(T_Nb_Lignes*T_Nb_Colonnes)-1;T_Nb_Lignes)+1; Index_Colonne;RECHERCHEV(SEQUENCE(T_Nb_Colonnes*T_Nb_Lignes;1);CHOISIR({1.2};SEQUENCE(T_Nb_Colonnes;1;1;T_Nb_Lignes);SEQUENCE(T_Nb_Colonnes;1;1;1));2;1); CHOISIR({1.2.3.4}; INDEX(T_Lignes;Index_Ligne); INDEX(T_Lignes2;Index_Ligne); INDEX(T_Colonnes;1;Index_Colonne); INDEX(T_Data;Index_Ligne;Index_Colonne)))

Plus de colonnes à droite

Et encore plus loin avec une deux­ième série de montants. 

Atten­tion, il faut impéra­tive­ment le même nom­bre de colonnes pour les années, et elles doivent être dans le même ordre (2019,2020,2021,2022)

=LET(T_Lignes;TableauN2[Ville];T_Lignes2;TableauN2[Pays]; T_Colonnes;TableauN2[[#En-têtes];[Profit 2019]:[Profit 2022]];T_Colonnes2;TableauN2[[#En-têtes];[Quantité 2019]:[Quantité 2022]]; T_Data;TableauN2[[Profit 2019]:[Profit 2022]];T_Data2;TableauN2[[Quantité 2019]:[Quantité 2022]]; T_Nb_Lignes;LIGNES(T_Lignes);T_Nb_Colonnes;COLONNES(T_Colonnes); Index_Ligne;MOD(SEQUENCE(T_Nb_Lignes*T_Nb_Colonnes)-1;T_Nb_Lignes)+1; Index_Colonne;RECHERCHEV(SEQUENCE(T_Nb_Colonnes*T_Nb_Lignes;1);CHOISIR({1.2};SEQUENCE(T_Nb_Colonnes;1;1;T_Nb_Lignes);SEQUENCE(T_Nb_Colonnes;1;1;1));2;1); CHOISIR({1.2.3.4.5}; INDEX(T_Lignes;Index_Ligne);INDEX(T_Lignes2;Index_Ligne); CNUM(DROITE( INDEX(T_Colonnes;1;Index_Colonne);4)); INDEX(T_Data;Index_Ligne;Index_Colonne); INDEX(T_Data2;Index_Ligne;Index_Colonne)))

N’hésitez-pas si vous voyez des amélio­ra­tions ou des lim­ites à la formule.

Quelques sources d’inspiration : 

Répéter une valeur plusieurs fois avec Recherchev (en Anglais)

https://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

Ajouter une ou plusieurs lignes à une table de données

Voici un petit code qui per­met d’a­jouter une ou plusieurs lignes à une table de don­nées, tout en incré­men­tant la pre­mière colonne con­tenant la clef. Ce code sera aus­si dis­cuté plus en pro­fondeur durant ce sémi­naire. Notez aus­si l’u­til­i­sa­tion de Application.WorksheetFunction.Sequence qui ne fonc­tionne qu’avec Office 365, mais qui per­met de réduire plusieurs lignes de code à juste une seule ligne.

Vous pou­vez télécharg­er le fichi­er ici

Voici le code et plus bas la vidéo expli­quant com­ment il fonctionne.

  • Il ne faut pas oubli­er de renom­mer la feuille en VBA (ALT+F11 pour ouvrir VBE, puis clic sur la feuille, puis touche F4 pour affich­er les pro­priétés et dans la pro­priété (Name), inscrire wksData. 
  • Renom­mez aus­si le tableau dans Excel (se met­tre sur le tableau, puis onglet Créa­tion de tableau et enfin, tout à fait à gauche, on inscrit le nom). Ici, il est nom­mé Data.
Option Explicit
Sub testAjouterNLignes()
AjouterNLignes 10
End Sub
Sub AjouterNLignes(lNbLignes As Long)
    'on definit l'objet correspond a la table Data
    Dim tblData As ListObject
    Set tblData = wksData.ListObjects("Data")

    If tblData.DataBodyRange Is Nothing Then ' cas d'un tableau vide
        tblData.ListRows.Add 'on ajoute une ligne
        tblData.Resize tblData.Range.Resize(tblData.Range.Rows.Count + lNbLignes - 1)
    Else
        tblData.Resize tblData.Range.Resize(tblData.Range.Rows.Count + lNbLignes)
    End If
    tblData.DataBodyRange.Offset( _
        tblData.DataBodyRange.Rows.Count - lNbLignes, _
        0).Resize(lNbLignes, 1) = _
        Application.WorksheetFunction.Sequence( _
        lNbLignes, 1, Application.WorksheetFunction.Max(tblData.ListColumns("No").Range.Value) + 1, 1)
End Sub
En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel