Analyse de données avec libre office


Analyse de données avec libre office

 

Version 4.1 Guide Calc Chapitre 9 Analyse des données Utiliser les scénarios, la recherche de valeur cible, le solveur et autres outils LibreOffice est une marque déposée de The Document Fondation Plus d?informations sur fr.libreoffice.org Copyright Ce document est Copyright © 2010?2013 par ses contributeurs tels que listés ci-dessous. Vous pouvez le distribuer et/ou le modifier sous les termes des licences GNU General Public License (http://www.gnu.org/licenses/gpl.html), version 3 ou ultérieure ou Creative Commons Attribution License (http://creativecommons.org/licenses/by/3.0/), version 3.0 ou ultérieure. Tous les noms de marque à l'intérieur de ce guide appartiennent à leur propriétaire légitime. Contributeurs Auteur : Christian Chenal Relecteurs : Philippe Clément Retours Veuillez envoyer vos commentaires ou suggestions à propos de ce document à : docfr.libreoffice.org Remerciements Ce chapitre est basé sur les Chapitres 9 de OpenOffice.org 3.3 Calc Guide (anglais), LibreOffice 3.4 Calc Guide (anglais) et LibreOffice 4.0 Guide Calc (français). Les contributeurs à ces chapitres sont : Jean Hollis Weber Nikita Telang James Andrew Claire Wood Barbara Duprey Hal Parker John A Smith Christian Chenal Philippe Clément Pierre-Yves Samyn Laurent Balland-Poirier Date de publication et version du logiciel Publié le 3 décembre 2013. Basé sur LibreOffice 4.1.1. Apparence de LibreOffice LibreOffice fonctionne sous les systèmes d'exploitation Windows, Linux et Mac OS X, chacun de ces systèmes pouvant exister sous plusieurs versions. Chacune de ces configurations peut donner lieu à des options d'affichage différentes (polices, couleurs, thèmes, gestionnaire de fenêtres, icônes). De plus, ces options d'affichage sont pour certaines personnalisables par les utilisateurs. Les illustrations de ce chapitre ont été réalisées sous Windows avec le jeu d'icônes Galaxy. Certaines images ne ressembleront donc pas exactement à ce que vous voyez sur votre ordinateur. En cas de confusion sur une icône, dont l'apparence peut être très différente d'un jeu à l'autre, vous pouvez vérifier son nom, qui apparaît dans l'infobulle lorsque vous placez le pointeur de la souris au-dessus. Note pour les utilisateurs Mac Certaines combinaisons de touches et certains éléments de menus sont différents entre un Mac et Windows ou Linux. La table ci-dessous donne quelques équivalents pour les instructions de ce chapitre. Pour une liste plus détaillée, voyez dans l'Aide. Windows/Linux Équivalent Mac Effet Sélection de menu Outils > Options LibreOffice > Préférences Accès aux options de paramétrage Clic droit ?+clic Ouvre un menu contextuel Ctrl (Contrôle) ? (Commande) Utilisé avec d'autres touches F5 Maj+?+F5 Ouvre le Navigateur F11 ?+T Ouvre la fenêtre Styles & Formatage Table des matières Copyright.........................................................................................................................................2 Apparence de LibreOffice................................................................................................................2 Note pour les utilisateurs Mac..........................................................................................................3 Introduction......................................................................................................................................5 Consolider les données....................................................................................................................5 Créer des sous-totaux......................................................................................................................7 Utiliser des scénarios "et si"...........................................................................................................10 Créer des scénarios...................................................................................................................11 Paramètres................................................................................................................................11 Modifier des scénarios...............................................................................................................12 Modifier les propriétés des scénarios....................................................................................12 Modifier les valeurs des cellules des scénarios.....................................................................12 Gérer les scénarios en utilisant le Navigateur...........................................................................13 Suivre les valeurs des scénarios...............................................................................................14 Utiliser l'outil Opérations Multiples..................................................................................................14 Opérations multiples en colonnes ou en lignes..........................................................................15 Calcul avec une formule et une variable...............................................................................15 Calculer plusieurs formules simultanément...........................................................................16 Opérations multiples en colonnes et en lignes...........................................................................17 Calculer avec deux variables................................................................................................18 Travailler à l'envers en utilisant la Recherche de valeur cible.........................................................19 Exemple de Recherche de valeur cible.....................................................................................19 Utiliser le Solveur...........................................................................................................................20 Exemple d'utilisation du Solveur................................................................................................21 Analyser les données d'un fichier source XML...............................................................................23 Analyse des données 4 Introduction Une fois que vous vous êtes familiarisé avec les fonctions et les formules, l'étape suivante est d'apprendre à utiliser les processus automatiques de Calc pour effectuer des analyses rapides et puissantes de vos données. Calc dispose de plusieurs outils pour vous aider à manipuler les informations de vos classeurs, allant des fonctionnalités pour copier et réutiliser les données à la création automatique de sous- totaux, afin de vous aider à trouver les réponses dont vous avez besoin. Ces outils sont répartis entre les menus Outils et Données. Si vous débutez avec les classeurs, ces outils peuvent paraître lourds au premier abord. Cependant, ils peuvent devenir plus simples si vous comprenez qu'ils dépendent tous de la saisie d'une cellule ou d'une plage de cellules qui contient les données sur lesquelles vous travaillez. Vous pouvez toujours saisir les cellules ou les plages manuellement, mais, dans la plupart des cas, il est plus facile de sélectionner les cellules avec la souris. Cliquez sur l'icône Réduire sur le côté d'un champ pour réduire temporairement la taille de la fenêtre d'un outil, et pour ainsi voir le classeur qui se trouve en dessous et sélectionner les cellules nécessaires. Parfois, vous devrez faire plusieurs tentatives pour trouver quelles données vont dans quels champs, mais lorsque vous avez un ensemble d'options, certaines d'entre elles peuvent être ignorées dans certains cas de figure. Essayez de bien garder en tête le but essentiel de chaque outil, et vous ne devriez pas avoir d'ennuis avec les outils de Calc. Vous n'avez pas forcément besoin de les apprendre, particulièrement si votre utilisation des classeurs est simple, mais si votre manipulation de données devient plus sophistiquée, ces outils peuvent vous faire gagner du temps, notamment si vous commencez à travailler avec des situations hypothétiques. De plus, ils peuvent vous permettre de préserver votre travail, de le reprendre plus tard ou de le partager avec d'autres personnes. La Table de pilote (connue également sous le nom de tableau croisé dynamique par les utilisateurs de Microsoft Excel) est un outil qui ne sera pas traité ici, car c'est un sujet plus vaste qui nécessite un chapitre séparé ; voir le Chapitre 8 (Utiliser la table de pilote). Consolider les données La commande Données > Consolider apporte une façon de combiner les données de deux plages de cellules ou plus en une seule nouvelle plage en utilisant une fonction d'agrégation de données comme Somme ou Moyenne. Au cours de la consolidation des données, le contenu des cellules de plusieurs feuilles peut être combiné en un seul endroit. L'effet obtenu est que les copies des plages identifiées sont empilées à partir de leur coin supérieur gauche à la position spécifiée du résultat, et l'opération choisie est utilisée dans chaque cellule pour calculer chaque valeur de résultat. Dans l'exemple de la Figure 1, la plage A2:A5 est combinée avec la plage C5:C10 en utilisant la fonction Somme. Le résultat est placé dans la cellule E2. Consolider les données 5 Figure 1 : Exemple de consolidation de données 1) Ouvrez le document contenant les plages de cellules à consolider. 2) Choisissez Données > Consolider pour ouvrir la boîte de dialogue Consolider. La Figure 2 montre cette boîte de dialogue après avoir effectué les étapes ci-dessous. Figure 2 : Définition des données à consolider 3) La liste déroulante Plage de données source contient tous les noms définis (créés avec Données > Définir la plage), afin que vous puissiez rapidement sélectionner une plage. Si la plage de données source n'a pas été nommée, cliquez dans le champ à la droite de la liste déroulante et saisissez la référence de la première plage de données, ou utilisez la souris pour sélectionner la plage dans la feuille. Vous aurez peut-être besoin de déplacer la boîte de dialogue Consolider ou de cliquer sur l'icône Réduire pour atteindre les cellules voulues. 4) Cliquez sur Ajouter. La plage sélectionnée est ajoutée dans la liste Plages de consolidation. 5) Sélectionnez les plages supplémentaires et cliquez sur Ajouter après chaque sélection. Consolider les données 6 6) Indiquez l'endroit où vous voulez afficher les résultats en choisissant un nom de cible dans la liste déroulante Copier le résultat vers. Si la cible n'est pas nommée, cliquez dans le champ à la droite de Copier le résultat vers et saisissez la référence de la plage cible, ou sélectionnez la plage dans la feuille avec la souris. Copier le résultat vers ne conserve que le coin supérieur droit de cette plage. 7) Choisissez une fonction d'agrégation dans la liste déroulante Fonction. Elle indique la façon dont les valeurs des plages de consolidation vont être combinées. La fonction par défaut est Somme. La plupart des fonctions disponibles sont des fonctions statistiques (comme Moyenne, Min, Max ou Variance), et l'outil est particulièrement utile quand vous travaillez à chaque fois sur les mêmes données. 8) Vous pouvez cliquer sur Plus pour accéder aux paramètres de consolidation supplémentaires : ? Cochez Lier aux données source pour produire des formules dans la plage de résultat, plutôt que les résultats des valeurs en cours. Si vous liez les données, les modifications des valeurs des plages source seront automatiquement répercutées dans la plage de résultat. Attention Dans la plage cible, les références aux cellules de la plage source sont insérées dans des lignes consécutives ordonnées qui sont immédiatement masquées. Seul le résultat final de la fonction sélectionnée est affiché. Les fonctions de plans et de groupes vous permettent d'afficher ou de masquer le détail des calculs. Si les lignes correspondantes ne peuvent pas être groupées, un message d'erreur apparaît. ? Sous Consolider par, cochez Étiquettes de ligne ou Étiquettes de colonne si les cellules des plages de consolidation ne doivent pas être combinées selon leur position dans la plage, mais selon la correspondance d'une étiquette de ligne ou de colonne. Pour ceci, l'étiquette doit être contenue dans les plages de consolidation. Le texte de ces étiquettes doit être absolument identique pour que les lignes ou les colonnes soient associées. Si ce n'est pas le cas, une nouvelle ligne ou colonne est ajoutée dans la plage cible. 9) Cliquez sur OK pour consolider les plages. Astuce Si vous travaillez régulièrement avec les mêmes plages, vous avez intérêt à utiliser Données > Définir la plage pour leur donner un nom. Les plages de consolidation et de résultat sont enregistrées dans le document. Si vous ouvrez ultérieurement un document dans lequel une consolidation avait été définie, ce paramétrage sera à nouveau disponible. Créer des sous-totaux SOUS.TOTAL est une fonction de la catégorie Mathématique quand vous utilisez l'Assistant Fonction. Parce que cette fonction est très utile, elle dispose d'une interface graphique disponible à partir de Données > Sous-totaux. Comme son nom le suggère, SOUS.TOTAL totalise les données disposées dans un tableau, c'est- à-dire dans un groupe de cellules avec des en-têtes de colonnes. Dans la boîte de dialogue Sous- totaux, vous pouvez sélectionner jusqu'à trois niveaux de regroupement, et choisir une fonction d'agrégation pour chaque colonne de la plage. Quand vous cliquez sur OK, Calc ajoute des lignes de sous-total et de total général aux tableaux sélectionnés, en utilisant les styles de cellules Résultat pour différencier ces cellules. Par défaut, les éléments identiques de votre tableau formeront un groupe au-dessus du sous-total. Créer des sous-totaux 7 Pour insérer des sous-totaux dans votre feuille : 1) Vérifiez que les colonnes comportent bien un en-tête. Dans le cas contraire, les colonnes seront identifiées par la valeur de la première ligne et des confusions pourraient être possibles. 2) Sélectionnez la plage de cellules pour laquelle vous voulez calculer des sous-totaux. Vous pouvez sélectionner une seule cellule et laisser Calc détecter automatiquement la plage de données à utiliser, comme pour la Table de pilote. Voir Chapitre 8 (Utiliser la table de pilote) pour plus de détails sur le fonctionnement de cette détection automatique. Choisissez ensuite Données > Sous-totaux. 3) Dans la boîte de dialogue Sous-totaux (Figure 3), dans la liste déroulante Grouper par, sélectionnez la colonne correspondant au regroupement avec lequel les sous-totaux seront calculés. Un sous-total sera effectué pour chaque valeur distincte de cette colonne. Figure 3 : Paramétrer des sous-totaux 4) Dans la zone Calculer les sous-totaux pour, sélectionnez une colonne à totaliser puis, dans la liste Utiliser la fonction, sélectionnez la fonction d'agrégation que vous voulez utiliser pour cette colonne. Procédez de même pour les autres colonnes à totaliser : sélection de la colonne, puis de la fonction d'agrégation. Si le contenu de ces colonnes change ultérieurement, les sous-totaux seront automatiquement recalculés. 5) Cliquez sur OK. Attention Une erreur fréquente consiste à sélectionner d'abord toutes les colonnes dans la liste Calculer les sous-totaux pour puis à sélectionner une fonction d'agrégation. Ce choix ne s'appliquera qu'à la colonne sélectionnée. Procédez comme indiqué ci-dessus si vous désirez des fonctions d'agrégation différentes par colonne. Créer des sous-totaux 8 Vous pouvez utiliser plusieurs groupes successifs pour construire une hiérarchie de sous-totaux. Chaque groupe du 1er groupe sera divisé en sous-totaux du 2e groupe, et ainsi de suite. Chaque groupe peut totaliser des colonnes différentes et utiliser des fonctions d'agrégation différentes. Calc insère, à gauche des en-têtes de ligne, des contrôles de plan qui représentent graphiquement la structure des sous-totaux. Les contrôles de plan ressemblent à la structure arborescente d'un gestionnaire de fichiers. Ils sont réservés à un usage à l'écran et ne s'impriment pas. Les contrôles de plan ont des signes plus ou moins au début d'un groupe pour "ouvrir" ou "refermer" le niveau correspondant, autrement dit pour afficher ou masquer les cellules. De plus, les contrôles de plan comprennent des boutons avec des numéros pour afficher ou masquer les différents niveaux. Il peut y avoir au maximum 5 niveaux de plan : le nombre 1 représente le premier niveau de regroupement, le total général. Le nombre le plus grand permet de "déplier" tous les niveaux pour afficher les données non regroupées. Les nombres 2 à 4 de la Figure 4 correspondent aux niveaux "intermédiaires". Figure 4 : Exemple de résultat de deux groupes de sous-totaux Créer des sous-totaux 9 Dans l'onglet Options (Figure 5), vous pouvez modifier la façon dont les sous-totaux sont affichés, notamment en choisissant l'ordre du tri des données. Figure 5 : Options des sous-totaux Saut de page entre les groupes Insère une nouvelle page après chaque groupe de données totalisées. Respecter la casse Change de groupe de données totalisées si la casse de l'étiquette de données change. Trier au préalable selon les groupes Si l'option est cochée, vous n'avez pas besoin que la plage de cellules soit triée dans l'ordre des sous-totaux que vous souhaitez : Calc effectuera automatiquement ce tri préalablement au calcul. Si vous décochez cette option et que la plage de cellules n'est pas triée, un sous-total sera calculé à chaque changement de valeur et vous pourrez obtenir plusieurs sous-totaux pour une même valeur, ce qui n'est pas forcément ce que vous désirez. Utiliser des scénarios "et si" Les scénarios constituent un outil pour traiter des questions de type "et-si". Chaque scénario a un nom, et peut être modifié et formaté séparément. Quand vous imprimez un classeur, seul le contenu du scénario actif est imprimé. Un scénario est principalement un ensemble enregistré de valeurs de cellules pour vos calculs. Vous pouvez facilement passer d'un de ces ensembles à l'autre en utilisant le Navigateur ou la liste déroulante qui peut s'afficher à côté des cellules impactées. Par exemple, si vous vouliez calculer l'effet de différents taux d'intérêt sur un investissement, vous pourriez ajouter un scénario pour chaque taux d'intérêt et afficher rapidement les résultats. Les formules liées aux valeurs modifiées par votre scénario se mettent à jour lors de l'ouverture de ce dernier. Si toutes vos Utiliser des scénarios "et si" 10 sources de revenus utilisent des scénarios, vous pouvez construire un modèle complexe de votre situation financière. Créer des scénarios La commande Outils > Scénarios ouvre une boîte de dialogue contenant les options pour créer un scénario. Pour créer un scénario : 1) Sélectionnez les cellules qui contiennent les valeurs qui seront modifiées selon les scénarios. Pour sélectionner plusieurs plages, gardez appuyée la touche Ctrl pendant que vous cliquez. Vous devez sélectionner au moins deux cellules. 2) Choisissez Outils > Scénarios. 3) Dans la boîte de dialogue Créer un scénario (Figure 6), saisissez un nom pour le nouveau scénario. Il est préférable d'utiliser un nom explicite qui identifie clairement le scénario, et de ne pas conserver le nom par défaut comme dans la figure ci-dessous. Ce nom est affiché dans le Navigateur et dans la barre de titre de la bordure autour du scénario dans la feuille elle-même. Figure 6 : Créer un scénario 4) Vous pouvez ajouter des informations dans la zone Commentaire. L'exemple montre le commentaire par défaut. Cette information est affichée dans le Navigateur quand vous cliquez sur l'icône Scénarios et vous sélectionnez le scénario voulu. 5) Vous pouvez cocher ou décocher les options de la section Paramètres. Voir ci-dessous pour plus de détails. 6) Cliquez sur OK pour fermer la boîte de dialogue. Le nouveau scénario est automatiquement activé. Vous pouvez créer plusieurs scénarios pour une plage de cellules donnée. Paramètres La partie basse de la boîte de dialogue Créer un scénario comporte plusieurs options. Les paramètres par défaut (Figure 6) conviennent généralement à la plupart des situations. Utiliser des scénarios "et si" 11 Afficher la bordure Place une bordure autour de la plage de cellules que votre scénario modifie. Pour choisir la couleur de cette bordure, utilisez la liste déroulante à la droite de cette option. La bordure comporte une barre de titre qui affiche le nom du scénario actif. Cliquez sur le bouton flèche à droite du nom de scénario pour ouvrir la liste déroulante comprenant tous les scénarios définis pour cette plage de cellules à l'intérieur de la bordure. Vous pouvez choisir un scénario de cette liste à tout moment. Recopier Recopie toutes les modifications que vous apportez aux valeurs des cellules du scénario dans le scénario actif. Si vous décochez cette option, les valeurs enregistrées du scénario ne changeront pas si vous faites des modifications. Le comportement de Recopier dépend de la protection des cellules, de la protection de la feuille et du paramètre Empêcher les modifications (voir Tableau 1 page 13). Attention Si vous affichez un scénario avec l'option Recopier activée et qu'ensuite vous créez un nouveau scénario en modifiant les valeurs et en effectuant Outils > Scénarios, vous aurez par là même écrasé les valeurs du premier scénario. Vous pouvez facilement éviter cela en ne modifiant les valeurs qu'une fois le nouveau scénario affiché et actif. Copier la feuille entière Ajoute à votre document une feuille qui affiche en permanence le nouveau scénario dans son intégralité : toute la feuille est copiée et non pas uniquement les cellules variables. En plus de créer le scénario, cette option le rend sélectionnable comme n'importe quelle feuille du classeur. Empêcher les modifications Empêche d'apporter les modifications à un scénario dans lequel l'option Recopier est activée si la feuille est protégée mais les cellules ne le sont pas. Empêche également les modifications des paramètres décrits dans cette section si la feuille est protégée. Une explication plus complète des différentes situations est donnée ci-dessous. Modifier des scénarios Les scénarios comportent deux aspects différents qui peuvent être gérés indépendamment : ? les propriétés des scénarios (les paramètres décrits ci-dessus) ; ? les valeurs des cellules des scénarios (les saisies à l'intérieur des bordures des scénarios). Le fait que chacun de ces deux aspects puisse être modifié dépend à la fois des propriétés existantes du scénario et de l'état en cours de la protection de la feuille et des cellules. Modifier les propriétés des scénarios Si la feuille est protégée (Outils > Protéger le document > Feuille) et Empêcher les modifications est cochée, les propriétés du scénario ne peuvent alors être modifiées. Si la feuille est protégée et Empêcher les modifications n'est pas cochée, les propriétés du scénario peuvent alors être modifiées, sauf Empêcher les modifications et Copier la feuille entière qui sont désactivées. Si la feuille n'est pas protégée, Empêcher les modifications n'a alors aucun effet et toutes les propriétés du scénario peuvent être modifiées. Modifier les valeurs des cellules des scénarios Le Tableau 1 résume l'interaction entre les différents paramètres concernant l'empêchement ou l'autorisation des modifications des valeurs de cellules du scénario. Utiliser des scénarios "et si" 12 Tableau 1. Comportement d'Empêcher les modifications concernant la modification des valeurs des cellules d'un scénario Paramètres Modification autorisée Protection de la feuille ACTIF Protection de la cellule du scénario INACTIF Empêcher les modifications ACTIF Recopier ACTIF La valeur de la cellule du scénario ne peut pas être modifiée. Protection de la feuille ACTIF Protection de la cellule du scénario INACTIF Empêcher les modifications INACTIF Recopier ACTIF La valeur de la cellule du scénario peut être modifiée et le scénario est mis à jour. Protection de la feuille ACTIF Protection de la cellule du scénario INACTIF Empêcher les modifications ACTIF ou INACTIF Recopier INACTIF La valeur de la cellule du scénario peut être modifiée, mais le scénario n'est pas mis à jour, à cause du paramètre Recopier. Protection de la feuille ACTIF Protection de la cellule du scénario ACTIF Empêcher les modifications ACTIF ou INACTIF Recopier ACTIF ou INACTIF La valeur de la cellule du scénario ne peut pas être modifiée. Protection de la feuille INACTIF Protection de la cellule du scénario ACTIF ou INACTIF Empêcher les modifications ACTIF ou INACTIF Recopier ACTIF ou INACTIF La valeur de la cellule du scénario peut être modifiée et le scénario est mis à jour ou non selon le paramètre Recopier Gérer les scénarios en utilisant le Navigateur Une fois les scénarios ajoutés à votre classeur, vous pouvez basculer vers un scénario particulier en le sélectionnant depuis une liste du Navigateur. Cliquez sur l'icône Scénarios du Navigateur (voir Figure 7). Seuls les scénarios définis sur la feuille active sont énumérés, avec les commentaires saisis à la création de ces scénarios. Figure 7 : Scénarios dans le Navigateur Utiliser des scénarios "et si" 13 Pour appliquer un scénario à la feuille en cours, double-cliquez sur le nom du scénario dans le Navigateur. Pour supprimer un scénario, faites un clic droit sur son nom dans le Navigateur et choisissez Supprimer. Pour modifier un scénario, et notamment son nom et ses commentaires, faites un clic droit sur son nom dans le Navigateur et choisissez Propriétés. La boîte de dialogue Éditer le scénario est la même que Créer un scénario (Figure 6). Suivre les valeurs des scénarios Pour savoir comment les valeurs d'un scénario affectent les autres valeurs, sélectionnez une des cellules variables du scénario, puis choisissez Outils > Audit > Repérer les dépendants. Les flèches pointent vers les cellules qui dépendent directement de la cellule en cours. Utiliser l'outil Opérations Multiples Comme les scénarios, Données > Opérations multiples est un outil de planification pour les questions de type "Et si". Il applique la même formule à différentes cellules, mais avec des valeurs de paramètres différentes : dans la feuille, vous saisissez une formule pour calculer un résultat à partir de valeurs qui sont stockées dans d'autres cellules. Puis, vous déterminez une plage de cellules dans laquelle vous saisissez des valeurs fixes et la commande Opérations multiples va calculer le résultat en fonction de la formule. Contrairement aux scénarios, l'outil Opérations Multiples ne présente pas des versions alternatives des mêmes cellules avec une liste déroulante. Il crée à la place un tableau de formules : un ensemble distinct de cellules affichant les résultats de l'application d'une formule à une liste de valeurs alternatives pour les variables de cette formule. Bien que cet outil ne soit pas répertorié parmi les fonctions, il s'agit en réalité d?une fonction similaire aux autres, qui vous permet de calculer différents résultats sans avoir à les saisir et à les exécuter séparément. Pour utiliser l'outil Opérations Multiples, vous avez besoin de deux tableaux de cellules. Le premier tableau contient les valeurs d'origine ou par défaut et les formules à leur appliquer. Les formules doivent se trouver dans une plage. Le second tableau est le tableau pour les formules. Il est créé en saisissant une liste de valeurs alternatives pour une ou deux valeurs d'origine. Une fois les valeurs alternatives créées, vous pouvez avec l'outil Opérations Multiples spécifier quelles formules vous allez utiliser, ainsi que les valeurs d'origine utilisées par ces formules. Le second tableau se complète alors avec les résultats en utilisant chaque valeur alternative à la place des valeurs d'origine. L'outil Opérations Multiples peut utiliser plusieurs formules, mais seulement une ou deux variables. Avec une variable, le tableau des valeurs alternatives des variables sera dans une seule colonne ou ligne. Avec deux variables, vous devriez concevoir votre tableau de cellules de telle manière à ce qu'une variable soit disposée en colonne et l'autre en ligne. Mettre en place des opérations multiples peut être difficile au départ. Par exemple, si vous utilisez deux variables, vous devez les sélectionner soigneusement pour qu'elles forment un tableau significatif. Toutes les paires de variables ne sont pas forcément utiles dans ce tableau. De plus, même avec une seule variable, un utilisateur novice peut facilement commettre des erreurs ou oublier les relations entre les cellules du tableau d'origine et celles du tableau pour les formules. Dans ce cas, Outils > Audit peut aider à clarifier les relations. Vous pouvez également rendre les tableaux pour les formules plus faciles à utiliser si vous suivez une logique simple de conception. Placez les tableaux d'origine et pour les formules à des endroits assez proches dans la même feuille, et dotez-les d'en-têtes de lignes et de colonnes. Ces petites Utiliser l'outil Opérations Multiples 14 recommandations de conception facilitent le travail avec les tableaux pour les formules, particulièrement quand vous corrigez des erreurs ou ajustez les résultats. Note Si vous exportez un classeur contenant des opérations multiples vers Microsoft Excel, l'emplacement des cellules contenant la formule doit être explicitement défini par rapport à la plage de données. Opérations multiples en colonnes ou en lignes Dans votre classeur, saisissez une formule pour calculer un résultat à partir des valeurs stockées dans d'autres cellules. Puis, fixez une plage de cellules pour contenir une liste d'alternatives à l'une des valeurs utilisées dans la formule. La commande Opérations multiples produit une liste de résultats à côté de vos valeurs alternatives en appliquant la formule à chacune d'entre elles. Note Avant de choisir l'option Données > Opérations multiples, assurez-vous d'avoir sélectionné non seulement la liste de vos valeurs alternatives, mais aussi les cellules adjacentes où les résultats vont être placés. Dans le champ Formules de la boîte de dialogue Opérations multiples (Figure 8), saisissez la référence de la cellule contenant la formule que vous voulez utiliser. La disposition de vos valeurs alternatives va décider de la façon dont vous allez remplir le reste de la boîte de dialogue. Si vous les avez listées dans une seule colonne, vous allez saisir le champ Cellule de saisie de colonne. Si elles sont sur une seule ligne, vous allez saisir le champ Cellule de saisie de ligne. Vous pouvez également utiliser les deux champs dans des cas plus avancés. Les deux versions sont expliquées ci-dessous. Tout ceci sera mieux expliqué avec des exemples. Les références de cellules correspondent à celles des figures suivantes. Supposez que vous produisiez des jouets que vous vendez 10 ? chacun (cellule B1). Le coût de fabrication de chaque jouet est de 2 ? (cellule B2). De plus, vous devez supporter des coûts fixes de 10 000 ? par an (cellule B3). Quel bénéfice allez-vous obtenir par an si vous vendez un nombre particulier de jouets ? Calcul avec une formule et une variable 1) Pour calculer le bénéfice, saisissez tout d'abord un nombre quelconque en tant que quantité (nombre d'articles vendus) : 2 000 dans cet exemple (cellule B4). Le bénéfice se calcule de la manière suivante : Bénéfice = Quantité * (Prix de vente ? Coût de fabrication) ? Coûts fixes. Saisissez cette formule dans B5 : =B4*(B1-B2)-B3. 2) Dans la colonne D, saisissez une série de valeurs alternatives pour les quantités annuelles vendues, l'une en dessous de l'autre ; par exemple, de 500 à 5000, par tranches de 500. 3) Sélectionnez la plage D2:E11, et donc les valeurs de la colonne D et les cellules vides (qui vont recevoir les résultats) de la colonne E situées à côté. 4) Choisissez Données > Opérations multiples. 5) Avec le curseur situé dans le champ Formules de la boîte de dialogue Opérations multiples, cliquez dans la cellule B5. 6) Placez le curseur dans le champ Cellule de saisie de colonne et cliquez dans la cellule B4. Cela signifie que B4, la quantité, est la variable de la formule qui doit être remplacée par les valeurs alternatives de la colonne. La Figure 8 montre la feuille et la boîte de dialogue Opérations multiples. Utiliser l'outil Opérations Multiples 15 Figure 8 : Feuille et boîte de dialogue Opérations multiples montrant les saisies 7) Cliquez sur OK. Les bénéfices pour les différentes quantités s'affichent alors dans la colonne E (Figure 9). Figure 9 : Feuille avec résultats d'opérations multiples Calculer plusieurs formules simultanément 1) Dans la feuille de l'exemple précédent, supprimer le contenu de la colonne E. 2) Saisissez dans la cellule C5 la formule =B5/B4, pour calculer le bénéfice annuel par article vendu. 3) Sélectionnez la plage D2:F11, soit trois colonnes. 4) Choisissez Données > Opérations multiples. 5) Avec le curseur situé dans le champ Formules de la boîte de dialogue Opérations multiples, sélectionnez les cellules B5 et C5. 6) Placez le curseur dans le champ Cellule de saisie de colonne et cliquez dans la cellule B4. La Figure 10 montre la feuille et la boîte de dialogue Opérations multiples. Utiliser l'outil Opérations Multiples 16 Figure 10 : Feuille et boîte de dialogue Opérations multiples montrant les saisies 7) Cliquez sur OK. Les bénéfices sont listés dans la colonne E et les bénéfices annuels par article dans la colonne F. Figure 11 : Résultats des opérations multiples Opérations multiples en colonnes et en lignes Vous pouvez effectuer des opérations multiples simultanément avec des colonnes et des lignes, dans des plages de type tableau croisé. La formule doit utiliser au moins deux variables et les valeurs alternatives devraient être disposées de telle façon qu'un jeu de données se trouve dans une seule ligne et l'autre jeu dans une seule colonne. Ces deux jeux de valeurs alternatives vont former les en-têtes de ligne et de colonne pour les résultats de la table générée par la procédure des Opérations Multiples. Sélectionnez la plage définie par les deux plages de données (qui inclut donc toutes les cellules vides qui vont contenir les résultats) et choisissez Données > Opérations multiples. Saisissez la référence de la cellule de la formule dans le champ Formules. Les champs Cellule de saisie de Utiliser l'outil Opérations Multiples 17 ligne et Cellule de saisie de colonne sont utilisés pour saisir la référence aux cellules correspondantes de la formule. Calculer avec deux variables Vous voulez maintenant faire varier non plus seulement la quantité produite annuellement, mais également le prix de vente, et vous recherchez le bénéfice obtenu dans chaque cas. Étendez la table de la Figure 10. Les cellules de D2 à D11 contiennent déjà les nombres 500, 1000, et ainsi de suite jusqu'à 5000. De E1 à H1, saisissez les nombres 8, 10, 15 et 20. 1) Sélectionnez la plage D1:H11. 2) Choisissez Données > Opérations multiples. 3) Avec le curseur situé dans le champ Formules de la boîte de dialogue Opérations multiples, cliquez dans la cellule B5 (bénéfice). 4) Placez le curseur dans le champ Cellule de saisie de ligne et cliquez dans la cellule B1. Cela signifie que B1, le prix de vente, est la variable saisie horizontalement (avec les valeurs 8, 10, 15 et 20). 5) Placez le curseur dans le champ Cellule de saisie de colonne et cliquez dans la cellule B4. Cela signifie que B4, la quantité, est la variable saisie verticalement. Figure 12 : Feuille et boîte de dialogue Opérations multiples montrant les saisies 6) Cliquez sur OK. Les bénéfices pour les différents prix de vente et les différentes quantités sont affichés dans la plage E2:H11. Attention Faites attention de bien entrer la bonne référence de cellule dans le bon champ. Le champ Cellule de saisie de ligne devra contenir non pas la référence de la cellule qui constitue chaque ligne (B4 dans l'exemple de la Figure 13, une ligne par quantité) dans le résultat (plage D1:H11 dans l'exemple), mais celle de la variable dont les valeurs alternatives ont été saisies sur une seule ligne (B1 dans l'exemple, une ligne comprend les différentes valeurs du prix de vente). Utiliser l'outil Opérations Multiples 18 Figure 13 : Résultats des opérations multiples Travailler à l'envers en utilisant la Recherche de valeur cible Habituellement, vous créez une formule pour calculer un résultat par rapport à des valeurs existantes. Par opposition, en utilisant Outils > Recherche de valeur cible, vous pouvez découvrir quelles valeurs vont produire le résultat que vous voulez. D'autres utilisations de la recherche de valeur cible peuvent être plus compliquées, mais la méthode reste néanmoins la même. Un seul argument peut être modifié dans une recherche de valeur cible. Exemple de Recherche de valeur cible Pour calculer un intérêt total (I), créez un tableau avec les valeurs pour le capital (C), le nombre d'années (n) et le taux d'intérêt (i). La formule est I = C * n * i. Supposons que le taux d'intérêt i de 7,5 % et le nombre d'années n de 1 restent constants. Vous voulez savoir quel capital C vous devez investir pour obtenir un revenu total particulier I. Dans cet exemple, calculez quel capital C est nécessaire pour obtenir un revenu total de 15 000 ?. Saisissez les valeurs ci-dessus dans des cellules adjacentes (pour le capital C, une valeur arbitraire comme 100 000 ? ou laissez la cellule vide ; pour le nombre d'années n, 1 ; pour le taux d'intérêt i, 7,5%). Saisissez la formule pour calculer l'intérêt total I dans une autre cellule. Dans l'exemple, la formule est =B1*B2*B3. 1) Placez le curseur dans la cellule de la formule (B4) et choisissez Outils > Recherche de valeur cible. 2) Dans la boîte de dialogue Recherche de valeur cible, la cellule correcte est déjà saisie dans le champ Cellule de formule. 3) Placez le curseur dans le champ Cellule variable. Dans la feuille, cliquez dans la cellule qui contient la valeur à rechercher, B1 dans cet exemple. 4) Saisissez le résultat de la formule voulu dans le champ Valeur cible. Dans cet exemple, la valeur est de 15 000. La Figure 14 montre les cellules et les champs. Travailler à l'envers en utilisant la Recherche de valeur cible 19 Figure 14 : Exemple de paramétrage de recherche de valeur cible 5) Cliquez sur OK. Un message apparaît pour vous informer que la Recherche de valeur cible a réussi. Cliquez sur Oui pour insérer la valeur cible dans la cellule variable. Le résultat est montré ci-dessous. Figure 15 : Résultat de la recherche de valeur cible Utiliser le Solveur Outils > Solveur est en fait une forme plus élaborée de la Recherche de valeur cible. Une des différences est que le Solveur peut traiter des équations avec plusieurs variables inconnues. Par ailleurs, il permet en plus d?une valeur cible d?atteindre le minimum ou le maximum du résultat selon un ensemble de règles que vous définissez. Chacune de ces règles définit si un argument de la formule doit être supérieur, inférieur ou égal à une valeur que vous saisissez. Si vous voulez que la valeur d?une cellule reste fixe, vous devez saisir une règle qui spécifie explicitement que la cellule doit être égale à la valeur saisie. Pour les arguments que vous voulez voir varier, vous pouvez ajouter deux règles pour définir l'intervalle des valeurs possibles : les conditions de limite. Par exemple, vous pouvez mettre en place la contrainte que l'une des variables ou cellules ne doit pas être supérieure à une autre variable ou à une valeur donnée. Vous pouvez également définir la contrainte qu'une ou plusieurs variables doivent être des entiers (sans valeurs décimales) ou des valeurs binaires (seuls 0 et 1 sont permis). Une fois la construction des règles terminée, cliquez sur le bouton Résoudre pour démarrer le processus automatique d'ajustement des valeurs et de calcul des résultats. Selon la complexité de la tâche, cela peut prendre du temps. Utiliser le Solveur 20 Exemple d'utilisation du Solveur Supposez que vous disposiez de 10 000 ? que vous souhaitez investir dans deux fonds communs de placement pour une année. Le fonds X est un fonds peu risqué avec un taux d'intérêt de 8 % et le fonds Y est un fonds plus risqué avec un taux d'intérêt de 12 %. Quel somme devriez-vous investir dans chaque fonds pour obtenir un intérêt total de 1 000 ? ? Pour connaître la réponse, utilisez le Solveur : 1) Saisissez les en-têtes et les données : ? en-têtes de colonnes : Intérêt obtenu, Montant investi, Taux d'intérêt et Période, dans les cellules B1 à E1 ; ? taux d'intérêt : 8 et 12 dans les cellules D2 et D3 ; ? période : 1 (année), dans les cellules E2 et E3 ; ? montant investi total : 10000, dans la cellule C4. 2) Saisissez une valeur arbitraire (0 ou laissez vide) dans la cellule C2 comme montant investi dans le fonds X. ? en-têtes de lignes : Fonds X, Fonds Y et Total, dans les cellules A2 à A4. 3) Saisissez les formules : ? Dans la cellule C3, saisissez la formule =C4-C2 (montant total ? montant investi dans le fonds X) comme montant investi dans le fonds Y. ? Dans les cellules B2 et B3, saisissez la formule pour calculer l'intérêt obtenu (voir Figure 16). ? Dans la cellule B4, saisissez la formule =B2+B3 comme intérêt total obtenu. Figure 16 : Exemple de mise en place pour le Solveur 4) Choisissez Outils > Solveur. La boîte de dialogue Solveur (Figure 17) s'ouvre. Utiliser le Solveur 21 Figure 17 : Boîte de dialogue Solveur 5) Cliquez dans le champ Cellule cible. Dans la feuille, cliquez dans la cellule qui contient la valeur cible. Dans cet exemple, c'est la cellule B4 qui contient l'intérêt total obtenu. 6) Sélectionnez Valeur de et saisissez 1000 dans le champ à côté. Dans cet exemple, la valeur cible de la cellule est de 1000 étant donné que vous cherchez à obtenir un intérêt total de 1 000 ?. Cochez Maximum ou Minimum si la valeur cible de la cellule doit être un de ces extrêmes. 7) Cliquez dans le champ Par modification de cellule et cliquez dans la cellule C2 dans la feuille. Dans cet exemple, vous devez trouver le montant investi dans le fonds X. 8) Saisissez les conditions de limite des variables en choisissant les champs Référence de cellule, Opérateur et Valeur. Dans cet exemple, le montant investi dans le fonds X (cellule C2) ne doit pas être supérieur au montant total investi et ne doit pas être inférieur à 0. 9) Cliquez sur Résoudre. Une boîte de dialogue apparaît, qui vous informe de l'évolution du calcul. Cliquez sur OK et un autre message apparaît vous indiquant le résultat de la résolution. Cliquez sur Conserver le résultat pour entrer le résultat dans la cellule avec la valeur variable. La Figure 18 illustre le résultat. Figure 18 : Résultat des opérations du Solveur Utiliser le Solveur 22 Note Le Solveur dispose de deux méthodes de résolution non linéaire (algorithmes évolutionnaires DEPS et SCO) grâce à l?extension Solveur de programmation non linéaire installée par défaut. Le wiki de LibreOffice dispose d?une page d?aide détaillée à ce sujet : http://wiki.documentfoundation.org/FR/Calc/NLPSolver Analyser les données d'un fichier source XML Vous pouvez copier les données d'un fichier externe au format XML dans votre classeur afin de pouvoir les manipuler selon votre besoin. Pour plus d'informations sur le format XML, voir la page Wikipedia Extensible Markup Language. Choisissez la commande Données > Source XML. La fenêtre Source XML s'ouvre. Cliquez sur le bouton Parcourir pour définir le fichier source et choisissez le fichier source XML dans la fenêtre Ouvrir. La zone de gauche affiche alors la structure arborescente du fichier (Figure 19). Trois types d'éléments composent la structure : ? Les attributs () sont importés dans une cellule. ? Les éléments non récurrents (</>) sont importés dans une cellule. ? Les éléments récurrents (<//>) sont importés dans une plage de cellule à partir de la cellule spécifiée. La plage comprend une colonne par composant et une ligne pour chaque occurrence, et sa première ligne est constituée du nom de chacun des composants. Pour chaque élément que vous souhaitez importer, sélectionnez-le dans la zone de gauche et saisissez la référence de la cellule cible dans la zone Cellules reportées. Une fois que tous les paramétrages sont effectués, cliquez sur le bouton Importer. Figure 19 : Boîte de dialogue Source XML Analyser les données d'un fichier source XML 23

PARTAGER SUR

Envoyer le lien par email
801
READS
11
DOWN
0
FOLLOW
4
EMBED
DOCUMENT # TAGS
#libre office  #data nalysis 

Licence Publique Générale GNU


DOCUMENT # INDEX
Informatique 
img

Partagé par  mercad.jenny

 Suivre

Auteur:
Source:Non communiquée