Création de colonnes et de lignes calculées à l'aide de formules Excel

Les formules Excel permettent d'ajouter des colonnes calculées et des totaux de ligne qui n'existent pas dans document QlikView ou l'application Qlik Sense d'origine.

À faire

Vous allez :

1. Ajouter des totaux de ligne grâce à la fonction SOMME.
2. Ajouter des colonnes calculées. Pour ce faire, il existe deux méthodes :

1. Tables Excel : Vous créez des tables à l'aide des outils de table Excel. Cette méthode permet d'obtenir de meilleures performances de création de rapports, mais elle nécessite des compétences Excel plus étendues. Nous vous recommandons d'adopter cette approche.

2. Niveaux : Vous insérez des balises de champ dans un niveau, puis vous ajoutez des formules Excel. Il s'agit d'une approche simple à implémenter, mais l'emploi de niveaux peut avoir une incidence sur les performances de création de rapports.

Cette page illustre la procédure de création de colonnes calculées selon les deux approches. Pour les besoins de la démonstration, nous calculerons une troisième colonne en soustrayant les valeurs d'une colonne de celles d'une autre. Ensuite, les valeurs dérivées de la troisième colonne seront divisées par les valeurs correspondantes de l'une des deux colonnes d'origine.

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.

À propos des calculs

Si vous souhaitez réduire au minimum le temps de création de rapports, vous pouvez ajouter les calculs souhaités dans le script de rechargement QlikView ou Qlik Sense.

Cet exemple utilise des formules Excel simples mais vous pouvez ajouter des formules plus complexes. Vous pouvez aussi insérer des formules Qlik. Voir : Formules Qlik Sense et QlikView (uniquement en anglais).

Ajout d'une table Excel avec des colonnes calculées et des totaux

Il s'agit de la première méthode de création de colonnes calculées. Vous allez ajouter l'objet QlikView source au nœud Tables, puis vous créerez un tableau Excel.

Ajout d'une table

Procédez comme suit :

1. Créez un nouveau rapport Excel ou ouvrez un rapport existant.

2. Cliquez sur le nœud Tables avec le bouton droit de la souris, puis sélectionnez Add objets (Ajouter des objets).

3. Sélectionnez un graphique dans la liste. Par exemple, ajoutez Top 10 Products. Cliquez sur OK.

   

4. Cliquez sur + à gauche pour développer le nœud de table CH184_Table - Top 10 Products.
5. Individuellement, sélectionnez chaque colonne sous CH184_Table - Top 10 Products et désélectionnez Keep Source Formats.

6. Maintenez la touche CTRL enfoncée et sélectionnez les champs de table à ajouter à la feuille.

   Par exemple : Product Name, Total Sales et Gross Profit. Glissez-les vers une ligne de cellules vide.

   

7. Sélectionnez les cellules avec l'en-tête, les balises de champs et une ligne inférieure supplémentaire.
8. Dans le ruban Excel, cliquez sur l'onglet Insertion et cliquez ensuite sur Table.

9. Vérifiez que l'indicateur Mon tableau comporte des en-têtes est activé, puis cliquez sur OK.

   

10. Dans le volet gauche, cliquez sur Extras. Faites glisser une balise deleterowdans une cellule de la ligne vide située en dessous du tableau.

    

    Cette balise permettra de supprimer la ligne vide dans le rapport final.

Ajout de la colonne calculée

Procédez comme suit :

1. Sélectionnez la première cellule vide située à droite des balises remplies.

2. Insérez la formule =[@[Total Sales]]-[@[Gross Profit]].

   Excel ajoutera automatiquement cette nouvelle colonne au tableau.

   

3. Saisissez un en-tête de colonne. Par exemple : Coûts.

4. Ensuite, utilisez l'outil de conception de table Excel pour appliquer la mise en forme.

Ajout de totaux

Procédez comme suit :

1. Sélectionnez une cellule vide en dessous de la table. Par exemple D8.

2. Saisissez une formule SOMME dans la cellule. La plage de la fonction SOMME doit inclure les éléments suivants :

   • L'adresse de la cellule dans la ligne contenant la balise d'ouverture de niveau.
   • La cellule contenant la balise de champ.
   • L'adresse d'au moins une cellule vide directement en dessous.

   Dans cet exemple, la fonction pourrait être =SUM(D4:7).

3. Cliquez et faites glisser cette formule vers E8 et F8.

4. Appliquez les outils de mise en forme d'Excel aux éléments de la ligne.

   Par exemple : les totaux Total Sales et Gross Profit peuvent avoir le format Currency (Devise) format.

   

Aperçu du rapport

Procédez comme suit :

1. Cliquez sur Aperçu.

   Excel lance et affiche le rapport.

2. Vous verrez une table avec la colonne contenant vos résultats calculés. La ligne inférieure contient les totaux de colonne.

   

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

Utilisation de niveaux pour créer une table avec des colonnes calculées et des totaux

Il s'agit de la deuxième méthode d'ajout de colonnes calculées à l'aide de niveaux. Pour en savoir plus sur les niveaux, consultez la rubrique suivante : Rapports Excel avec des niveaux (uniquement en anglais).

Ajout du niveau

Procédez comme suit :

1. Créez un nouveau rapport Excel ou ouvrez un rapport existant.
2. Cliquez sur le nœud Levels (Niveaux) avec le bouton droit de la souris, puis sélectionnez Add levels (Ajouter des niveaux).

3. Sélectionnez un graphique dans la liste. Par exemple, ajoutez Top 10 Products. Cliquez sur OK.

   

4. Cliquez sur le nœud CH184_Level - Top 10 Products et glissez-le sur trois cellules vides alignées verticalement.

   

5. Cliquez sur + à gauche pour développer le nœud de niveau CH184_Level - Top 10 Products.
6. Individuellement, sélectionnez chaque colonne sous CH184_Level - Top 10 Products et désélectionnez Keep Source Formats.

7. Maintenez la touche CTRL enfoncée et sélectionnez les champs de niveau à ajouter à la feuille.

   Par exemple : Product Name, Total Sales et Gross Profit. Déplacez-les vers une ligne vide de cellules entre les balises de niveau.

   

8. Mettez en forme ces champs selon vos préférences. Par exemple, vous pouvez définir Total Sales comme devise.

Ajout de formules et d'en-têtes

1. Sélectionnez la première cellule vide située à droite des balises de champ. Dans cet exemple, F4.
2. Cliquez sur la cellule, saisissez =, puis cliquez sur la cellule Total Sales.

3. Saisissez -, cliquez sur la cellule Gross Profit et appuyez sur Entrée. Les coûts seront calculés.

   Lorsque vous insérez une formule entre des balises de niveau, Qlik NPrinting la copie dans chaque ligne de la table finale.

   Vous pouvez ignorer la error #VALUE! dans le modèle. La raison est que la formule Excel référence les cellules contenant les balises de champ.

4. Dans la cellule G4, saisissez =F5/D5. Ainsi, les coûts sont divisés par les ventes.

5. Mettez en forme les cellules de la formule comme vous le souhaitez en utilisant les fonctions de mise en forme Excel.

   Par exemple, définissez les coûts comme devise et les coûts/ventes comme pourcentage.

6. Saisissez des en-têtes pour chaque colonne dans une ligne située à un ou deux niveaux au-dessus de la ligne contenant la balise de niveau d'ouverture Top 10 Products.

   Mettez en forme ces en-têtes comme souhaité.

   

7. Sélectionnez une cellule en dessous de la balise de fermeture de niveau </Top 10 Products_Level> de façon à prévoir au moins une cellule vide intermédiaire.

8. Dans la cellule D8, saisissez une formule SOMME Excel pour créer une ligne de totaux de colonne. La plage de somme doit inclure les éléments suivants :

   1. L'adresse de la cellule de la balise de champ.
   2. L'adresse de la cellule vide en dessous.

   Dans cet exemple, la fonction pourrait être =SUM(D4:D6).

9. Cliquez et faites glisser cette formule vers E8 et F8.

10. Appliquez les outils de mise en forme d'Excel aux éléments de la ligne.

    Par exemple : les totaux Total Sales et Gross Profit peuvent avoir le format Currency (devise).

    

Aperçu du rapport

Procédez comme suit :

1. Cliquez sur Aperçu.

   Excel lance et affiche le rapport.

2. Vous verrez une table avec les deux nouvelles colonnes contenant vos résultats calculés. La ligne inférieure contient les totaux de colonne.

   

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