[Calc] Réaliser un diagramme à partir d'une plage calculée

Venez découvrir tous les tutoriels, modèles et autres foires aux questions afin de maîtriser rapidement votre suite bureautique favorite.

Modérateur : Vilains modOOs

Règles du forum
Aucune question dans cette section !
Cette section est faite pour présenter les tutoriels. Si vous avez une question sur l'installation, le fonctionnement ou l'utilisation, vous devez poster dans la section du module où se produit le problème.

Ce tutoriel vous a-t-il aidé ou répondu à votre problème ?

Oui
5
83%
Non
0
Aucun vote
En partie
1
17%
 
Nombre total de votes : 6

Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

[Calc] Réaliser un diagramme à partir d'une plage calculée

Message par Jeff »

Bonjour,

En attendant la résolution de l'issue 64604, qui devrait permettre la saisie de formule comme plage pour un diagramme, je vous propose ci-après quelques pistes pour réaliser des diagrammes avec une plage calculée.

Important : ce tutoriel a été réalisé avec la version 3.2.1 d'OOo ; en conséquence, certaines fonctionnalités peuvent ne pas être disponibles avec une version antérieure.
  1. Deux avantages :
  2. Même si une plage importante est utilisée comme plage de données, la largeur du diagramme sera optimisée par rapport aux données saisies.
  3. La plage de donnée peut-être choisie selon les critères calculés
Ce tutoriel (qui n'a rien d'exhaustif), fait largement appel à la fonction DECALER, à l'usage des noms, ainsi qu'aux bases de la saisie/correction/validation d'une formule matricielle.

La fonction DECALER :

DECALER(référence;lignes;colonnes;hauteur;largeur)
(Les éléments en gras sont à renseigner)

Référence : est le point de départ du calcul de la plage ; par exemple A1
Lignes : indique de combien de ligne on décale par rapport à la référence ;
si ligne=6, alors la fonction DECALER(A1;6;0) retourne le contenu de A7
Colonnes : indique de combien de colonne on décale par rapport à la référence ; si Colonne=2, alors la fonction DECALER(A1;6;2) retourne le contenu de C7

Pour retourner une matrice avec cette fonction, il suffit de renseigner les 2 derniers paramètres, puis de valider matriciellement par Ctrl+Maj+Entrée

Hauteur : indique la hauteur de la matrice (en nombre de cellules) qui est à retourner ;
si Hauteur=5, DECALER(A1;6;2;5) retourne le contenu de la plage C7:C11
Largeur : indique la largeur de la matrice (en nombre de cellules) qui est à retourner;
si largeur=3, DECALER(A1;6;2;5;3) retourne le contenu de la plage C7:E11

Attention : à moins de ressaisir la formule, la plage retournée est fixe, l'astuce est donc de saisir la fonction DECALER pour retourner la plage la plus grande possible ; par la suite, les erreurs retournées par absence de données ne seront pas interprétées par le module de diagramme de Calc (version OOo 3.2 minimum).

Un aperçu du diagramme obtenu avec le choix des dates à afficher :
DPN4_choix_dates.png
DPN4_choix_dates.png (9.39 Kio) Consulté 16352 fois
Le classeur ayant servi à l'élaboration de ce tutoriel est disponible en fin de fil de discussion.
Dernière modification par Oukcha le 10 janv. 2015 12:26, modifié 15 fois.
Raison : Mise à jour hyperlien
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Choix de la plage de données

Message par Jeff »

Dans le classeur joint, ( feuille Choix_plage du classeur joint), les données sont en plage A1:B182.
La colonne A correspond aux dates, la colonne B contient des valeurs numériques.
La plage A2:A182 est nommée Dates (menu > Insertion > Noms > Définir)
DPN_presentation_donnees.png
DPN_presentation_donnees.png (3.88 Kio) Consulté 16320 fois
Le décalage va être calculé par rapport à A1. L'utilisateur renseigne la première cellule voulue ainsi que la dernière respectivement en E3 et E5.

Comme précisé auparavant, le premier calcul de la matrice par la fonction DECALER va être réalisé sur la plage maximale possible, soit du 01/01/10 au 30/06/10 dans cet exemple.

Pour trouver les numéros de lignes nécessaires aux calculs, il suffit d'utiliser la fonction EQUIV.
La plage calculée sera donc :
DECALER(A1;EQUIV(E3;Dates);;EQUIV(E5;Dates)+1-EQUIV(E3;Dates);2)

Référence : A1
Lignes : c'est la ligne dont le contenu en E3 est présent dans la plage Dates
Colonnes : aucun décalage de colonne (le paramètre peut être omis si les autres paramètres suivants suffisent pour le calcul)
Hauteur : c'est la ligne dont le contenu est en E5 + 1 ligne d'entête – la quantité de ligne du premier contenu en E3 ; donc :
- contenu est en E5 + 1 ligne d'entête = EQUIV(E5;Dates)+1
quantité de ligne du premier contenu en E3 = EQUIV(E3;Dates)
d'où hauteur = EQUIV(E5;Dates)+1-EQUIV(E3;Dates)
Largeur : 2 pour nos deux colonnes

Après validation par Ctrl+Maj+Entrée, la plage étant encore sélectionnée, menu Insertion > Diagramme.

Type de diagramme : pour cet exemple, le choix est un type XY(dispersion), sous-type Points et lignes.
A l'étape 3 de l'assistant, la plage pour le nom est renseignée
DPN2_creationXY.png
Le diagramme étant encore en mode Edition, il faut rétablir le format JJ/MM/AA qui n'a pas été interprété par la fonction DECALER ; choisir le menu Format > Axes > Axe X, onglet Nombre, décocher Format de la source et choisir un format date :
DPN3_formatage_diagramme.png
Le diagramme est maintenant tout à fait opérationnel, et n'affichera que les données choisies entre la valeur en E3 et celle en E5.

Le classeur ayant servi à l'élaboration de ce tutoriel est disponible en fin de fil de discussion.
Dernière modification par Jeff le 01 sept. 2010 13:47, modifié 4 fois.
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Faire défiler l'affichage des données

Message par Jeff »

Dans l'exemple joint, le diagramme va afficher les données mois par mois, à l'aide d'un bouton fléché.
  • Dans un premier temps, il faut créer le bouton :
  • Menu Affichage > Barres d'outils > cocher Contrôle de formulaire
  • Cliquer sur l'icône (Dés)Activer le mode conception
  • Cliquer sur l'icône Compteur (le pointeur de souris se change en croix)
  • Dessiner le compteur avec un clic maintenu sur une zone de la feuille
  • Ce compteur va aider à sélectionner les mois de janvier à juin, un petit paramétrage s'impose :
    Clic-droit sur le compteur
    • onglet Contrôle :
    • valeur min = 1 (pour janvier)
    • valeur max = 6 (pour juin)
    • Récupérer la valeur du compteur dans une cellule :
    • onglet Données :
    • Renseigner la cellule devant accueillir la valeur du compteur (dans cet exemple A1)
    Le compteur est maintenant opérationnel, et affiche les chiffres de 1 à 6.
Le petit + : En B1, saisir la formule suivante :

Code : Tout sélectionner

=NOMPROPRE(TEXTE(DATE(10;A1;1);"mmmm"))
Ainsi, cette cellule B1 pourra servir à afficher dans la légende, de façon dynamique, le mois en cours :
DPN6_legende_dynamique.png
Pour créer la première date grâce à ce compteur, le plus simple est d'utiliser la fonction DATE au jour 1.
Pour calculer la plage sur le mois en entier, décaler la date précédente d'un mois grâce à la fonction MOIS.DECALER, puis déduire un jour :
DPN5_calcul_ligne_date.png
DPN5_calcul_ligne_date.png (8.58 Kio) Consulté 16320 fois
Il faut maintenant calculer la plage avec la fonction DECALER.
Tout comme l'exemple précédent, il faut utiliser la fonction DECALER pour retourner la plage la plus grande possible ; dans cette optique, le compteur est donc soit sur 1,3 ou 5

DECALER(référence;lignes;colonnes;hauteur;largeur)

Référence : Choix_plage.A1 (cellule A1 de la feuille Choix_plage)
Lignes : EQUIV(DATE(10;A1;1);Dates) (ligne de la plage Dates correspondant à l'année 2010, du numéro de mois en A1, au jour 1)
Colonne : (inutile de renseigner)
Hauteur : EQUIV(MOIS.DECALER(DATE(10;A1;1);1);Dates)-EQUIV(DATE(10;A1;1);Dates)
(EQUIV(MOIS.DECALER(DATE(10;A1;1);1)) = ligne de la plage Dates correspondant à l'année 2010, du numéro de mois en A1, au jour 1, avec un décalage de 1 mois
il faut ensuite soustraire les lignes calculées précédemment :
-EQUIV(DATE(10;A1;1);Dates))
Largeur : 2 (pour les deux colonnes)

La formule matricielle est donc :

Code : Tout sélectionner

=DECALER(Choix_plage.A1;EQUIV(DATE(10;A1;1);Dates); ;EQUIV(MOIS.DECALER(DATE(10;A1;1);1);Dates)-EQUIV(DATE(10;A1;1);Dates);2)
à valider par Ctrl+Maj+Entrée

La plage étant encore sélectionnée, menu Insertion > Diagramme.
Le choix de diagramme est encore pour cet exemple un type XY(Dispersion), sous-type Points et Lignes.
A l'étape 3 de l'assistant, sélectionner la cellule B1 comme plage pour le nom.
Enfin, formater l'axe X comme dans l'exemple précédent.
DPN8_defilement_mois_par_mois.png
DPN8_defilement_mois_par_mois.png (9.61 Kio) Consulté 16320 fois
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Afficher les n dernières données

Message par Jeff »

Dans cet exemple (onglet Afficher_n_derniere_ligne du classeur joint), l'utilisateur choisit le nombre de données à afficher en partant de la fin de la liste saisie en colonne B.

Il faut donc dans un premier temps retrouver l'adresse de la dernière cellule renseignée dans cette colonne, cette cellule sera la référence pour calculer le décalage ; pour ce faire, j'utilise une formule donnée par gerard24, formule matricielle qui permet de trouver l'adresse de la dernière cellule renseignée, même si présence de cellules vides :

Code : Tout sélectionner

=INDIRECT(ADRESSE(MAX((plage<>"")*LIGNE(plage));COLONNE(plage)))
(Par défaut, cette formule renvoie le contenu de la dernière cellule, puisqu'interprétée par la fonction INDIRECT.)

Il faut aussi connaître le numéro de ligne de cette cellule, afin de limiter le décalage voulu par l'utilisateur ; en effet, si seulement 5 données sont saisies, l'utilisateur ne doit pas pouvoir en sélectionner davantage. La formule matricielle découlant de la formule précédente pour obtenir le numéro de ligne est :

Code : Tout sélectionner

=MAX((plage<>"")*LIGNE(plage))
(ne pas oublier ensuite de limiter le nombre de ligne possible en tenant compte des lignes d'entête)

Le nombre de données désiré dans le diagramme est saisi en cellule D15 ; sur cette cellule est établi un contrôle de validité basée sur le contenu de la cellule D12 (menu Données > Validité).

Comme indiqué précédemment, il faut utiliser la fonction DECALER pour retourner la plage la plus grande possible, il faut donc saisir une donnée dans la dernière ligne du tableau (ici, cellule B20), et indiquer le décalage maximal en cellule D15, avant de saisir la formule calculant la plage.

Les différents éléments pour construire la plage sont donc :
DPN9_n_dernieres_cellules.png
DECALER(référence;lignes;colonnes;hauteur;largeur)

référence : INDIRECT(ADRESSE(MAX((B1:B20<>"")*LIGNE(B1:B20));COLONNE(B1:B20)))
lignes : 1-D15 (nombre de lignes désirées avec un résultat négatif pour décaler du nombre de ligne vers le haut ; cette cellule D15 est soumise à un contrôle de validité)
colonne :-1 (pour décaler d'une colonne vers la gauche)
hauteur : D15
largeur : 2

La plage peut donc être calculée via la formule matricielle :

Code : Tout sélectionner

=DECALER(INDIRECT(ADRESSE(MAX((B1:B20<>"")*LIGNE(B1:B20));COLONNE(B1:B20)));1-D15;-1;D15;2)
DPN10_diagramme_dernieres_cellules.png
Dernière modification par Jeff le 08 sept. 2010 22:59, modifié 1 fois.
Jeff
GourOOu
GourOOu
Messages : 9608
Inscription : 18 sept. 2006 11:40
Localisation : France

Affichage par numéro de semaines

Message par Jeff »

Dans cet exemple (onglet Affichage par numéro de semaines du classeur joint), l'utilisateur sélectionne dans une liste déroulante le numéro de semaine, les données correspondantes sont alors affichées sur le diagramme.

Dans cet exemple, il s'agit d'un relevé d'unités, de dizaines et de centaines, relevé réalisé un jour sur deux.
Les dates sont saisies dans la plage A2:A184, dans l'ordre chronologique.
(cet exemple ne tient pas compte des doublons, par exemple, en 2008, les premiers et les derniers jours de la semaine appartiennent à la semaine 1)
  • La première étape consiste donc à fabriquer en cellule G2 une liste déroulante faisant apparaître le numéro de la semaine selon les dates saisies ; pour cela, dans le menu Données > Validité, est employée la fonction NO.SEMAINE :
    DPN11_Donnees_validite_no_semaine.png
  • Seconde étape :
    Par rapport au numéro de semaine sélectionné en G2, il faut ensuite trouver la première date correspondante ; comme dans les exemples précédents, une fonction EQUIV matricielle fait l'affaire (l'utilisation de la fonction matricielle est impérative, puisqu'est renseignée une plage pour la fonction NO.SEMAINE) :

    Code : Tout sélectionner

    =EQUIV($G$2;NO.SEMAINE($A$2:$A$184;0);0)
    Qui peut être traduite de la façon suivante :
    =on recherche l'équivalent ( de G2 ; des numéros de semaine ( de la plage A2:A184 ; la semaine commence un lundi ); la plage de recherche n'est pas triée)
    Grâce à cette formule, il est maintenant aisé de trouver l'adresse de cette ligne, toujours en utilisant une formule matricielle, avec une fonction ADRESSE :

    Code : Tout sélectionner

    =ADRESSE(EQUIV($G$2;NO.SEMAINE($A$2:$A$184;0);0)+1;1)
    Cette formule va renvoyer un texte représentant l'adresse de la cellule voulue ; pour que ce texte soit correctement interprété, il faut encore englober cette formule dans une fonction INDIRECT matricielle :

    Code : Tout sélectionner

    =INDIRECT(ADRESSE(EQUIV($G$2;NO.SEMAINE($A$2:$A$184;0);0)+1;1))
  • Troisième étape : il faut maintenant déterminer combien de dates correspondent au numéro de semaine choisi en cellule G2 ; pour ce comptage, j'utilise une fonction NB.SI matricielle( l'utilisation de la fonction matricielle est impérative, puisqu'est renseignée une plage pour la fonction NO.SEMAINE) :

    Code : Tout sélectionner

    =NB.SI(NO.SEMAINE($A$2:$A$184;0);$G$2)
    Grâce à cette dernière formule, on peut alors déterminer quelle sera la dernière dates à afficher, l'adresse de la première cellule correspondant à la semaine choisie sera décalée autant de fois qu'il y a de dates correspondant à ce numéro de semaine.
    Pour cela, il est possible d'utiliser la formule suivante :

    Code : Tout sélectionner

    =DECALER(INDIRECT(ADRESSE(EQUIV($G$2;NO.SEMAINE($A$2:$A$184;0);0)+1;1));NB.SI(NO.SEMAINE($A$2:$A$184;0);$G$2)-1;0)
  • Quatrième étape : Comme indiqué précédemment, il faut utiliser la fonction DECALER pour retourner la plage la plus grande possible ; comme le relevé est effectué un jour sur deux, le maximum de dates à afficher est de 4. Ce maximum est atteint dès la semaine 2, il faut donc au préalable sélectionner le chiffre 2 dans la liste déroulante en G2.

    Ensuite la formule matricielle pour obtenir la plage calculée pourrait donc ressembler à ceci :

    DECALER(référence;lignes;colonnes;hauteur;largeur)

    référence : c'est la première date correspondant à la semaine choisie, date obtenue par la formule matricielle :

    Code : Tout sélectionner

    =EQUIV($G$2;NO.SEMAINE($A$2:$A$184;0);0)
    lignes : (inutile de renseigner)
    colonnes : (inutile de renseigner)
    hauteur : c'est le nombre de dates correspondant à la semaine choisie, nombre obtenue par la formule matricielle :

    Code : Tout sélectionner

    =NB.SI(NO.SEMAINE($A$2:$A$184;0);$G$2)
    largeur : 4 colonnes à afficher, donc 4

    La formule matricielle retournant la plage est donc :

    Code : Tout sélectionner

    =DECALER(INDIRECT(ADRESSE(EQUIV($G$2;NO.SEMAINE($A$2:$A$184;0);0)+1;1)); ; ;NB.SI(NO.SEMAINE($A$2:$A$184;0);$G$2);4)


La plage obtenue étant encore en surbrillance, appeler le menu Insertion > Diagramme ;
Le choix de diagramme est encore pour cet exemple un type XY(Dispersion), sous-type Points et Lignes.

Pour simplifier l'affichage des étiquettes de colonne, à l'étape 2 de l'assistant, la plage est changé de $'Affichage par numéro de semaine'.$F$20:$I$23 vers $'Affichage par numéro de semaine'.$F$19:$I$23, et la première ligne est définie comme étiquette.
  • Dernière étape, le diagramme étant toujours en mode Edition (sinon clic-droit sur le diagramme > Editer) :
    • Menu Format > Axes > Axes X, onglet Nombre, décocher Format de la source et choisir un format date afin de rétablir le format JJ/MM/AA qui n'a pas été interprété par la fonction DECALER
    • Menu Format > Axes > Axes Y, onglet Echelle, cocher Echelle logarithmique, pour que l'interprétation des données soit plus explicite.
DPN13_diagramme_no_semaine.png
Aucune question dans ce fil (voir bandeau rouge en haut de page), les questions sur les diagrammes sont à poser en section Tableur.

.
Pièces jointes
Diagramme_plage_calculee.ods
(65.22 Kio) Téléchargé 996 fois
Répondre