0
(0)

Dans ce premier article (Voir Les nouvelles fonctions GROUPER.PAR (GROUPEBY) et CROISER.AVEC (PIVOTBY)) j'ai présenté les fonctions GROUPER.AVEC (GROUPBY) et CROISER.AVEC (PIVOTBY).

Le but de cet article est de tenter de reproduire les possibilités offertes par les tableaux croisés dynamiques au moyen de la fonction CROISER.AVEC.

C'est aussi l'excuse de pousser un peu plus loin les manipulations et résultats que nous permet cette fonction.

Cette fonction étant très récente et sujette à évolution, il faut prendre cette article comme une "ébauche", n'ayant pas non plus d'expérience sur son utilisation.

Base de données de départ

Une base de données un peu plus conséquente que pour le premier article (Les nouvelles fonctions GROUPER.PAR (GROUPEBY) et CROISER.AVEC (PIVOTBY)) permettant plus de manipulations.

Excel : CROISER.AVEC Base de données de départ

Disposition des étiquettes de ligne et de colonne

Positionnement des champs en Ligne, Colonne, Valeur.

  • En ligne (Row_fields) : Contrat, Civilité2, Service.
  • En colonne (Col_fields) : Ville, Classification.
  • En valeur (Fonction) : Moyenne et NBVAL de Salaire et Nb_Enfants.
=CROISER.AVEC(
  ASSEMB.H(
            Tableau1[[#Tout];[Contrat]];
            Tableau1[[#Tout];[Civilite2]];
            Tableau1[[#Tout];[Service]]);
  ASSEMB.H(
            Tableau1[[#Tout];[Ville]];
            Tableau1[[#Tout];[Classification]]);
  ASSEMB.H(
            Tableau1[[#Tout];[Salaire]];
            Tableau1[[#Tout];[Nb_Enfants]]);
  ASSEMB.H(MOYENNE;NBVAL))

Excel CROISER.AVEC : Disposition des champs

Le but ici étant de tester les possibilités de la fonction mais bien évidemment un tel tableau est illisible car trop complexe.

Disposition des résultats de synthèse

Positionnement des champs Valeur en horizontal/vertical.

En horizontal/colonne (ASSEMB.H)

=CROISER.AVEC(
  Tableau1[[#Tout];[Contrat]];
  ;
  Tableau1[[#Tout];[Salaire]];
  ASSEMB.H(NBVAL;MOYENNE))

Excel CROISER.AVEC : Disposition des champs Valeur

En vertical/ligne (ASSEMB.V)

=CROISER.AVEC(
  Tableau1[[#Tout];[Contrat]];
  ;
  Tableau1[[#Tout];[Salaire]];
  ASSEMB.V(NBVAL;MOYENNE))

Excel CROISER.AVEC : Disposition des champs Valeur

Disposition globale

Nous obtenons une disposition qui correspond à la disposition sous forme Tabulaire des tableaux croisés (sans sous-totaux).

Les dispositions en forme Compactée et Plan ne sont pas réalisable.

=CROISER.AVEC(
  ASSEMB.H(
            Tableau1[[#Tout];[Contrat]];
            Tableau1[[#Tout];[Ville]]);
  ;
  Tableau1[[#Tout];[Salaire]];
  NBVAL)

Résultat obtenu

Excel CROISER.AVEC : Disposition globale

Rappel : Dispositions des tableaux croisés dynamique.

Forme Tabulaire

Excel CROISER.AVEC : Disposition TCD Tabulaire

Forme Compactée (non réalisable)

Excel CROISER.AVEC : Disposition TCD compactée

Forme Plan (non réalisable)

Excel CROISER.AVEC : Disposition TCD Plan

Mise en forme du tableau

La fonction CROISER.AVEC renvoyant l'ensemble du tableau, la mise en forme est assez problématique.

Elle peut être faite :

  • Manuellement : Mais doit être refaite si le tableau se propage plus/moins lors de sa mise à jour.
  • Automatiquement : Nécessite de nombreuses Mise en forme conditionnelles plus ou moins complexe.
=CROISER.AVEC(
  ASSEMB.H(
          Tableau1[[#Tout];[Contrat]];
          Tableau1[[#Tout];[Service]]);
  ASSEMB.H(
          Tableau1[[#Tout];[Ville]];
          Tableau1[[#Tout];[Classification]]);
  Tableau1[[#Tout];[Matricule]];
  NBVAL;;2)

Mise en forme par des mises en forme conditionnelles

Exemple de rendu

Excel CROISER.AVEC : Mise en forme automatique

Mises en forme conditionnelles appliquées

Excel CROISER.AVEC : Mise en forme conditionnelles appliquées

Par exemple, une règle pour :

  • Les bordures,
  • La ligne de titre,
  • Les lignes de sous-totaux,
  • La ligne de Total général,
  • ...

Remarques

  • La plage d'application ne peut pas être prévue et donc doit être prise avec une marge suffisante.
    Ceci obligera parfois à ajouter des tests d'existences de contenu du type C$2<>"".
  • La répétition des titres peut être masquée en donnant au texte la même couleur que le fond.
  • Les sous-totaux peuvent être repérés par l'absence d'étiquette du champ de droite sur la ligne correspondante ($C4="").

Création de regroupements personnalisés

Pour les valeurs de type Date

Par exemple, regrouper les données par année et mois de naissance.

=CROISER.AVEC(
  ASSEMB.H(
          ANNEE(Tableau1[[#Tout];[Naissance]]);
          MOIS(Tableau1[[#Tout];[Naissance]]));
  ;
  Tableau1[[#Tout];[Matricule]];
  NBVAL)

Excel CROISER.AVEC : Regroupement par année

Pour les valeurs de type Numérique

Par exemple, regrouper les données par tranche de 1 000 € de salaire.

On génère les bornes des plages en faisant des arrondis au multiple (ici 1 000).

=CROISER.AVEC(
  PLANCHER.MATH(Tableau1[[#Tout];[Salaire]];1000) & "-" & PLAFOND.MATH(Tableau1[[#Tout];[Salaire]];1000);
  ;
  Tableau1[[#Tout];[Matricule]];
  NBVAL)

Excel CROISER.AVEC : Regroupement par tranches de valeurs

Remarque : Les étiquettes de colonne ont été ici ajoutées manuellement.

Pour les valeurs de type Alphanumérique (le texte)

Par exemple, regrouper les données par pôle regroupant plusieurs services.

=LET(
  Support;ESTNUM(EQUIV(Tableau1[[#Tout];[Service]];{"Accueil";"Achats";"laboratoire";"Réparation"};0));
  RH;ESTNUM(EQUIV(Tableau1[[#Tout];[Service]];{"Accueil";"Comptabilité";"Direction";"Personnel"};0));
  Service2;SI(Support;"Support";SI(RH;"RH"; Tableau1[[#Tout];[Service]]));
  Service; Tableau1[[#Tout];[Service]];
  CROISER.AVEC(
                ASSEMB.H(Service2;Service);
                ;
                Tableau1[[#Tout];[Matricule]];
                NBVAL))

Principe :

  • Via les variables Support et RH (fonction LET), on identifie l'appartenance du service aux pôles.
    • EQUIV renvoie la position (un numérique) si le service est trouvé sinon #N/A.
    • ESTNUM nous permet d'obtenir une matrice de booléen (VRAI si position, FAUX si #N/A).
  • La variable Service2 convertit les VRAI en étiquette "Support" ou "RH".
Excel CROISER.AVEC : Regroupement arbitraire alphanumérique

Remarque : L'affichage de la 2eme colonne d'étiquette n'est pas obligatoire (laissée ici pour exemple).

Les fonctions de synthèses

Les fonctions SOMME, NBVAL, MOYENNE, MAX, MIN, ECARTYPE.STANDARD, ECARTYPE.PEARSON, VAR.S, VAR.P.N des tableaux croisés dynamiques sont déjà présentes.

Nous disposons en plus de NB, MEDIANE, PRODUIT, POUCENTAGE.DE, CONCAT, TABLEAU.EN.TEXTE, MODE.SIMPLE.

Il manque la fonction "cachée" Total distinct/Comptage de valeurs (dénombrement sans les doublons). Pour l'obtenir dans les tableaux croisés dynamiques il faut utiliser le modèle de données.

Pour faire cette synthèse avec la fonction CROISER.AVEC, il faut créer une fonction LAMBDA (Voir Les bases de la fonction LAMBDA).

Exemple : Nombre de Nom différents par ville (ignorer les doublons/homonymes).

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  ;
  Tableau1[[#Tout];[Nom]];
  LAMBDA(v;NBVAL(UNIQUE(v)));3)

Excel CROISER.AVEC : Fonction Total distinct/Comptage de valeurs

Simulation des modes d'affichage des valeurs

Je vous propose de simuler les modes suivants :

  • % du total général,
  • % du total de la colonne,
  • % du total de la ligne,
  • % de,
  • Différence par rapport,
  • Différence en % par rapport,
  • % du total du parent,
  • % du total de la ligne parente,
  • % du total de la colonne parente,
  • Résultat cumul par,
  • % résultat cumulé dans,
  • Rang - Du plus petit au plus grand,
  • Rand - Du plus grand au plus petit.

Le dernier mode me semblent réalisable mais je vous laisse le plaisir de le générer 😊 :

  • Index.

Pourcentage du total de la colonne

Pour les valeurs numériques

Exemple : Pourcentage du nombre d'enfants en fonction de la Ville et par type de Contrat (piège classique : Nombre d'enfants, c'est ici la SOMME de la colonne Nb_enfants et non le nombre de valeurs de cette colonne).

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Contrat]];
  Tableau1[[#Tout];[Nb_Enfants]];
  POURCENTAGE.DE;3)

Excel CROISER.AVEC : % de la colonne avec des valeurs numériques

Pour les valeurs alphanumériques

Exemple : Pourcentage du nombre de personne en fonction de la Ville et par type de Contrat.

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Contrat]];
  ESTTEXTE(Tableau1[[#Tout];[Nom]])*1;
  POURCENTAGE.DE;3)

Via ESTTEXTE on remplace le texte par du booléen puis on le convertit en numérique (*1).

Excel CROISER.AVEC : % de la colonne avec des valeurs alphanumériques

Pourcentage du total de la ligne

Exemple : Pourcentage du nombre d'enfants en fonction du Contrat et par Ville.

=TRANSPOSE(CROISER.AVEC(
  Tableau1[[#Tout];[Contrat]];
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Nb_Enfants]];
  POURCENTAGE.DE;3))

Excel CROISER.AVEC : % de la ligne avec des valeurs numériques

Remarque : pour de l'alphanumérique, passer par ESTTEXTE(...)*1 comme pour la version colonne.

Pourcentage du total général

Pour les valeurs numériques

Exemple : Pourcentage du nombre d'enfants en fonction de l'ensemble des enfants, par type de Contrat et par Ville.

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Contrat]];
  Tableau1[[#Tout];[Nb_Enfants]]/SOMME(Tableau1[[#Tout];[Nb_Enfants]]);
  SOMME;3)

Principe : On passe chaque valeur Nb_enfants en % puis on en fait la Somme.

Excel CROISER.AVEC : % du total avec des valeurs numériques

Autre solution

=LET(
tab;CROISER.AVEC(
                  Tableau1[[#Tout];[Ville]];Tableau1[[#Tout];[Contrat]];
                  Tableau1[[#Tout];[Nb_Enfants]];
                  SOMME;3);
SIERREUR(tab/SOMME(Tableau1[[#Tout];[Nb_Enfants]]);tab))

Principe : On divise le tableau résultat de CROISER.AVEC par le total et on intercepte les erreurs liées aux étiquettes avec SIERREUR ou un SI(ESTNUM(...)...).

Pour les valeurs alphanumériques

Exemple : Pourcentage du nombre de personne en fonction de l'ensemble des personnes, par type de Contrat et par Ville.

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Contrat]];
  ESTTEXTE(Tableau1[[#Tout];[Nom]])/NBVAL(Tableau1[Nom]);
  SOMME;3)

Attention : on divise par NBVAL(Tableau1[Nom]) et non NBVAL(Tableau1[[#Tout];[Nom]]) sinon l'étiquette de colonne est comptabilisée.

Excel CROISER.AVEC : % du total avec des valeurs alphanumériques

Pourcentage de (par rapport à un des éléments du tableau)

Exemple : Pourcentage du nombre de personnes en fonction de la population Dijonnaise, par type de Contrat et par Ville.

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Contrat]];
  1/NB.SI.ENS(
              Tableau1[[#Tout];[Ville]];"Dijon";
              Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]]);
  SOMME;3)

Principe : On divise le nombre de personnes par le nombre de Dijonnais ayant le même type de Contrat.

Remarque :

  • 1/NB.SI est préférable à ESTTEXTE(Tableau1[[#Tout];[Nom]])/NB.SI... pour les cas où des Noms sont laissés vides.
  • Un étonnant NB.SI.ENS avec Tableau1[[#Tout];[Contrat]] à la fois en plage et en critère !

Différence par rapport (à un des éléments du tableau)

Exemple : Nombre de personnes par Ville et type de Contrat par rapport à Dijon (en plus ou en moins).

=CROISER.AVEC(
  Tableau1[[#Tout];[Ville]];
  Tableau1[[#Tout];[Contrat]];
  1-NB.SI.ENS(
              Tableau1[[#Tout];[Ville]];"Dijon";
              Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]])
  /NB.SI.ENS(
              Tableau1[[#Tout];[Ville]];Tableau1[[#Tout];[Ville]];
              Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]]);
  SOMME;3;0)

Explications du résultat pour CDD/Couternon par exemple

Il y a 55 personnes en CDD à Dijon. On doit donc retirer cette valeur aux résultats de Couternon et Quetigny.

Il y a 90 personnes en CDD à Couternon.
La formule va donc faire 90 additions pour arriver au résultat (35).
La formule va être (1-55/90) + (1-55/90) +.... 90 fois (90 fois 1 donne 90 et 90 fois 55/90 donne 55).

Excel CROISER.AVEC : Différence (par rapport à Dijon) Equation
Excel CROISER.AVEC : Différence (par rapport à Dijon)

Différence en % par rapport (à un des éléments du tableau)

Exemple : Nombre de personnes par Ville et type de Contrat par rapport à Dijon (en plus ou en moins) en pourcentage.

=LET(
  NbRef;NB.SI.ENS(
                  Tableau1[[#Tout];[Ville]];"Dijon";
                  Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]]);
  CROISER.AVEC(
    Tableau1[[#Tout];[Ville]];
    Tableau1[[#Tout];[Contrat]];
    (1-NbRef/NB.SI.ENS(
                        Tableau1[[#Tout];[Ville]];Tableau1[[#Tout];[Ville]];
                        Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]]))
                  /NbRef;
    SOMME;3;0))

Explications du résultat pour CDD/Couternon par exemple

Même principe que précédemment mais le résultat sera divisé par le nombre de Dijonnais.

Cette valeur étant utilisé 2 fois, on va utiliser une fonction LET pour la mémoriser (NbRef).

Excel CROISER.AVEC : Différence en % (par rapport à Dijon)

% du total du parent

Exemple : Nombre de personnes par Ville, Civilité et type de Contrat en % par rapport à la ville (parent).

=CROISER.AVEC(
  ASSEMB.H(
          Tableau1[[#Tout];[Ville]];
          Tableau1[[#Tout];[Civilite1]]);
  Tableau1[[#Tout];[Contrat]];
  1/NB.SI.ENS(
              Tableau1[[#Tout];[Ville]];Tableau1[[#Tout];[Ville]];
              Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]]);
  SOMME;3;2)

Excel CROISER.AVEC : % du total parent

% du total de la ligne parente

Exemple : Nombre de personnes par Ville, Civilité et type de Contrat en % par rapport à la ville (parent).

=LET(
  tab;CROISER.AVEC(
                  ASSEMB.H(
                            Tableau1[[#Tout];[Ville]];
                            Tableau1[[#Tout];[Civilite1]]);
                  Tableau1[[#Tout];[Contrat]];
                  ESTTEXTE(Tableau1[[#Tout];[Nom]])*1;SOMME;3;2);
  SIERREUR(
          tab/NB.SI.ENS(
                        Tableau1[[#Tout];[Ville]];
                        SI(INDEX(tab;;2)="";"*";INDEX(tab;;1));Tableau1[[#Tout];[Contrat]];"CDD");
          tab))

Principe :

  • On divise le tableau résultat de CROISER.AVEC par un total et on intercepte les erreurs liées aux étiquettes avec SIERREUR ou un SI(ESTNUM(...)...).
  • Ce total diviseur change en fonction de la ligne, soit le global (ville="*"), soit celui lié à la ville.
Excel CROISER.AVEC : % du total Ligne parent

Résultat cumul par

Exemple : Nombre de personnes par Ville, Civilité et type de Contrat en valeurs cumulées par Ville.

=LET(
  tab;CROISER.AVEC(
                  Tableau1[[#Tout];[Ville]];
                  Tableau1[[#Tout];[Contrat]];
                  ESTTEXTE(Tableau1[[#Tout];[Nom]])*1;
                  SOMME;3);
  val;N(tab);
  nbl;LIGNES(val);
  SI(ESTTEXTE(tab);tab;PRODUITMAT(N(SEQUENCE(nbl)>=SEQUENCE(;nbl));val)))

Principe :

  • Tab : Le tableau sans cumul.
  • Val : Le tableau sans cumul ou les valeurs d'étiquettes sont passées à 0 (pour ne pas gêner).
  • nbl : Nombre de lignes de Val.
  • Si la cellule de Tab est du texte, on utilise cette valeur (étiquette) sinon on prend celle calculée à partir de Val.
  • PRODUITMAT permet d'obtenir une matrice cumul des valeurs (voir image ci dessous).
    • En vert : Matrice Val.
    • En rose : Matrice N(SEQUENCE(nbl)>=SEQUENCE(;nbl)).
    • En blanc : Matrice résultat de PRODUITMAT.
Excel CROISER.AVEC : Mode Résultat cumulé par. Résultat de la fonction PRODUITMAT

Version avec cumul

Excel CROISER.AVEC : Mode Résultat cumulé par

Version sans cumul (pour comparaison)

Excel CROISER.AVEC : Mode Résultat cumulé par. Version de comparaison sans cumul

% résultat cumulé dans

Exemple : Nombre de personnes par Ville, Civilité et type de Contrat en pourcentages cumulés par Ville.

=LET(
  tab;CROISER.AVEC(
                  Tableau1[[#Tout];[Ville]];
                  Tableau1[[#Tout];[Contrat]];
                  1/NB.SI.ENS(Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Contrat]]);
                  SOMME;3);
  val;N(tab);
  nbl;LIGNES(val);
  SI(ESTTEXTE(tab);tab;PRODUITMAT(N(SEQUENCE(nbl)>=SEQUENCE(;nbl));val)))

Excel CROISER.AVEC : Mode % Résultat cumulé par

Rang - Du plus petit au plus grand / Rang - Du plus grand au plus petit

Exemple : Classement du nombre de personnes par Ville et type de Contrat en colonne (par Ville).

=LET(
  Tab;CROISER.AVEC(
                  Tableau1[[#Tout];[Ville]];
                  Tableau1[[#Tout];[Contrat]];
                  ESTTEXTE(Tableau1[[#Tout];[Contrat]])*1;
                  SOMME;3;0);
  NbChpLigne; 1;
  NbChpColonne; 1;
  Sens; -1;
  Plage;EXCLURE(Tab;NbChpColonne+1;NbChpLigne);
  Class;MAKEARRAY(
                  LIGNES(Tab);COLONNES(Tab);
                  LAMBDA(r;c;
                         SI(ET(r>NbChpColonne+1;c>NbChpLigne);
                           LET(
                               Col;INDEX(Plage;;c-NbChpLigne);
                               EQUIV(
                                     INDEX(plage;r-NbChpColonne -1;c-NbChpLigne);
                                     TRIER(FILTRE(Col;ESTNUM(Col));;Sens);
                                     0))
                           )));
  SI(ESTTEXTE(Tab);Tab;Class))

Principe :

  • Tab : Le tableau sans classement.
  • NbChpLigne : Le nombre de champs placés en étiquette de ligne.
  • NbChpColonne : Le nombre de champs placés en étiquette de colonne.
  • Sens : Ordre du classement.
    • 1 : Du plus petit au plus grand.
    • -1 : Du plus grand au plus petit.
  • Plage : Le tableau sans étiquettes de ligne ou de colonne.
  • Class : Tableau avec le classement des valeurs.
    • Les lignes/colonnes correspondants aux étiquettes sont remplies par FAUX (Argument Valeur_Si_Faux la fonction SI non précisé car inutile).
    • Génération du classement via une fonction EQUIV (la fonction EQUATION.RANG étant non utilisable).
    • Les valeurs vides sont considérées comme du texte et décale le classement d'où le FILTRE pour les éliminer.
  • On génère le résultat en piochant dans les étiquettes dans Tab et les valeurs dans Class.

Excel CROISER.AVEC : Mode Rang (classement) par ville

Personnalisation étiquettes

La solution la plus simple est de passer par des mises en forme conditionnelles.

Exemple : Si la cellule est égale à "Ville" appliquer le format de nombre ;;;"Agglomération" permettant ainsi de remplacer l'étiquette Ville par Agglomération.

Utilisation des segments pour appliquer un filtre

Une solution "lourde", utilisable pour un tableau de bord.

Le principe

Le segment filtre le tableau structuré.
Via l'argument Filtre_array de la fonction CROISER.AVEC, on ne prend en compte que les lignes visibles.

Mise en œuvre

On va ajouter une colonne (je l'ai nommée Filtre) dans la base de données permettant de savoir si la ligne est filtrée ou non.

Pour cela on va utiliser une astuce secrète !

La fonction SOUS.TOTAL est une des seules fonctions EXCEL dont le résultat change en fonction du filtre appliqué.
On va lui demander, par exemple, de compter la valeur à côté d'elle.

  • Si la valeur est affichée (donc/car ligne affichée), on obtient 1.
  • Si la valeur est masquée (donc/car ligne masquée), on obtient 0 (on ne peut pas voir cette valeur car la ligne la contenant est masquée 😁).
=SOUS.TOTAL(3;[@Ville])

Il nous reste donc qu'a indiquer à CROISER.AVEC de ne prendre que les lignes où Filtre est à 1.

=CROISER.AVEC(
ASSEMB.H(Tableau1[[#Tout];[Contrat]];Tableau1[[#Tout];[Ville]]);
;
Tableau1[[#Tout];[Nom]];
NBVAL;3;;;;;
Tableau1[[#Tout];[Filtre]]=1)

Excel CROISER.AVEC :  utiliser un segment

Rappel : Les segments Chronologie de sont pas utilisable (du moins pas directement 😁 voir Une chronologie (segment) pour un tableau, c'est possible !).

Divers

Merci pour votre attention bienveillante.

Article intéressant ?

Cliquez sur une étoile pour noter cet article !

Note moyenne 0 / 5. Nombre de votes : 0

Aucun vote pour l'instant ! Soyez le premier à noter ce post.

Nous sommes désolés que cet article ne vous ait pas été utile !

Améliorons cet article !

Dites nous comment nous pouvons améliorer cet article ?

A lire également

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *