Rapports Excel avec sous-totaux et niveaux imbriqués

Vous pouvez imbriquer des niveaux pour créer une hiérarchie et utiliser des formules Excel pour faire des calculs.

Il est par exemple possible de créer une hiérarchie d'années/de catégories pour obtenir un rapport comprenant les ventes annuelles de chaque catégorie de produits. Vous pouvez ajouter des formules et étiquettes récapitulatives à chaque niveau du rapport pour indiquer les valeurs affichées à ce niveau. Pour en savoir plus sur les niveaux, consultez la rubrique suivante : Rapports Excel avec des niveaux (uniquement en anglais)

À faire

Dans ce tutoriel, les objets QlikView insérés entre les balises d'ouverture et de fermeture sont sous-divisés dans le rapport final.

Vous allez :

1. Créer une table personnalisée en ajoutant deux colonnes.
2. Ajouter deux champs en tant que niveaux pour que le rapport Excel présente trois strates d'informations.
3. Ajouter des formules SOMME pour insérer des totaux et sous-totaux dans vos tables.
4. Personnaliser des formules SOMME à l'aide de la mise en forme Excel. Ce tutoriel suggère une mise en forme qui peut être personnalisée pour qu'elle réponde à vos besoins.

Ce tutoriel utilise des données QlikView se trouvant dans Fichiers exemples (uniquement en anglais). Vous pouvez aussi utiliser vos données QlikView ou Qlik Sense.

Création d'un rapport Excel

Procédez comme suit :

1. Sélectionnez Rapports dans le menu principal de Qlik NPrinting, puis cliquez sur Créer un rapport.
2. Renseignez le champ Titre du rapport. Report with nested levels and subtotals.
3. Sélectionnez Excel dans la liste déroulante Type.
4. Sélectionnez une application dans la liste déroulante Application.
5. Cliquez sur Créer pour créer le rapport.
6. Cliquez sur Éditer le modèle pour ouvrir l'éditeur de modèle.

Sélection de niveaux et d'objets de table

1. Cliquez sur le nœud Levels (Niveaux) avec le bouton droit de la souris, puis sélectionnez Add levels (Ajouter des niveaux).

2. Cliquez sur les objets à ajouter. Par exemple, ajoutez les champs Country et CategoryName.

   Pour sélectionner plusieurs éléments, maintenez la touche CTRL ou Shift enfoncée.

3. Cliquez sur OK.

   

4. Cliquez sur le nœud Tables avec le bouton droit de la souris, puis sélectionnez Add objets (Ajouter des objets).
5. Cliquez sur l'objet à ajouter. Par exemple, sélectionnez Top 10 Products.

6. Cliquez sur OK.

   

Ajout d'une table

Vous pouvez ajouter l'objet de table entier au modèle. Dans cet exemple, vous n'ajoutez que deux colonnes.

Procédez comme suit :

1. Sous le nœud Tables, développez le nœud Top 10 Products.
2. Cliquez sur Total Sales. Dans le volet Properties (Propriétés), désélectionnez la case à cocher Keep Source Formats (Conserver les formats source).
3. Répétez l'opération pour ProductName.

4. Faites glisser les nœuds ProductName et Total Sales dans des cellules vides du modèle.

   Par exemple, les cellules D6 et E6.

5. Cliquez sur <Total Sales> et choisissez Devise comme mise en forme.

6. Dans la cellule E8, saisissez la formule Excel : =SUM(E6:E7).

   Cette formule inclut une ligne vide. Qlik NPrinting ajoutera donc des lignes le cas échéant pour contenir toutes les valeurs.

7. Dans la cellule D8, saisissez Total:.

8. Mettez en forme la cellule E8 grâce aux outils d'Excel.

   Par exemple :

   • Police 12px gras
   • Justification à droite
   • Personnalisé = Comptabilité sans chiffres à droite de la décimale.

   

Ajout du premier niveau

Procédez comme suit :

1. Déplacez le nœud Country_Level dans la cellule C4.
2. Déplacez la balise de fermeture </Country_Level> dans la cellule C10 pour que Country Level comprenne la cellule contenant la SOMME et une ligne vide.
3. Cliquez sur + pour développer le nœud Country_Level.

4. Déplacez le jeton de nœud Country dans la cellule C6.

   Cela ajoute un titre dynamique.

5. Mettez en forme la cellule C6 comme suit : 12px et Gras.

6. Dans la cellule E11, saisissez la formule : =SUM(E6:E11)/2.

   La somme est divisée par deux, car la fonction SUM ajoute toutes les valeurs, y compris la somme des valeurs contenues dans la cellule E8.

7. Mettez en forme la cellule E11 comme suit :
   • Police 12px gras
   • Justification à droite
   • Personnalisé = Comptabilité sans chiffres à droite de la décimale.

   

Ajout du second niveau

Procédez comme suit :

1. Déplacez le jeton de nœud CategoryName_Level dans la cellule A2.
2. Déplacez la balise de fermeture </CategoryName_Level> vers A14.
3. Cliquez sur + pour développer le nœud CategoryName_Level.

4. Déplacez le jeton de nœud CategoryName dans la cellule B3.

   Cela ajoute un titre dynamique.

5. Mettez en forme la cellule B3 comme suit : 16px Gras.
6. Déplacez un second nœud CategoryName sur la cellule D11. Faites un double clic sur cette cellule et ajoutez le mot "total" pour que la cellule affiche <CategoryName> total:.
7. Dans la cellule D14, saisissez Grand total:.

8. Dans la cellule E14, saisissez la formule : =SUM(E2:E14)/3

   La somme est divisée par trois, car la fonction SOMME ajoute toutes les valeurs, y compris les sous-totaux des cellules E8 et E11.

9. Mettez en forme la cellule E14 comme suit :
• Police 14px gras
• Justification à droite
• Personnalisé = Comptabilité sans chiffres à droite de la décimale.

Aperçu du rapport

Procédez comme suit :

1. Cliquez sur Aperçu.

   Excel lance et affiche le rapport.

2. Le rapport est alors organisé selon le premier niveau et puis par le second niveau.

   

3. Chaque table possède un total en bas. Chaque catégorie possède un total avec une étiquette dynamique.

   

4. En bas, se trouve un total général des produits de tous les pays.

   

5. Cliquez sur Enregistrer et fermer pour enregistrer le modèle et fermer l'éditeur de modèle.