AccueilVersion Calc 1.x Version Calc 2.x Présentation Calc 3.xBien débuter Calc Fonctions Calc Exercices Calc FormulairesAstuces CalcPrécisions calculsLa bonne formuleProgrammation BasicLiens diversDéfinitionsRecherche sur siteHumourInfos, news

EXERCICES CALC. Dépenses planifiées

 

Avec cet exemple, ou plutôt ces exemples, nous allons aborder2 méthodes pour traiter les dépenses et les synthétiser.

1ère méthode : chaque catégorie de dépenses possède une feuille de calcul propre et la méthode 2 regroupe toutes lesdonnées sur une même feuille. Chaque méthode possède avantages et inconvénients. Petit plus, en fin de cette page vous apprendrez à créer un format listing avec le formatage conditionnel.

Méthode 1

 

 

Créons autant de feuilles, par insertion si nécessaire, que de catégories de dépenses. Chaque feuille sera renommée par un nom explicite. Une feuille supplémentaire (ANNUEL) sera nécessaire pour faire la synthèse.

Sur une première feuille, inscrivons les titres des éléments de base d'un mouvement. Ensuite par Copier/Coller, il faudra refaire la même chose sur chaque feuille (sauf ANNUEL).

Des informations avec des notes pour le mode de paiement ne sont pas superflues.

Si vos listes s'allongent, pour toujours voir les titres, il est utile de fixer la ligne des titres.

Dans la feuille ANNUEL, en A1, l'année de référence. Dans la première ligne les mois et dans la première colonne les catégories.

Ici un exemple des prélèvements d'eau. Notez le format des nombres, rouges si négatifs. Notez aussi l'avantagede la ligne des titres fixée. On passe de la ligne 1 à la ligne 29. Il suffit bien sûr de remonter pour voir les autres lignes.

Une fois les données entrées, il faut aborder la synthèse.

Commençons par une des feuilles de catégorie. Une fois cette feuille au point, il suffira de recopier dans les autres feuilles.

Comme la synthèse à lieu par mois et année, nous allons extraire dans 2 colonnes les mois et années. Donc, la colonne H est réservée aux mois et la colonne Iaux années. Notez que nous laissons une colonne vide, tout simplement pour permettre de faire des tris éventuels uniquement sur la zone des données.

Connaissez-vous les fonctions qui vont nous servir ? Cherchez un peu.

Mais oui, il faut utiliser les fonctions MOIS et ANNEE. Dans la cellule H2, on inscrit =MOIS(B2) et en I2, ce sera =ANNEE(B2). Il suffira ensuite de recopier vers le bas.

Il va maintenant falloir comparer les résultats par mois, mais surtout par année pour commencer. Nous avons vu que l'année de référence était inscrite dans la cellule A1 de la feuille ANNUEL. Dans la cellule K1 entrons donc la formule = ANNUEL.A1, puis dans les cellules de L1 à W1,les valeurs de 1 à 12, pour les numéros de mois.

Dans les cellules L2 à W2, les formules pour calculer les résultats. Aïe ! Ca va se compliquer sérieusement.

Nous allons nous servir de formule matricielle, et plus précisément de la fonction SOMME. Il faut rechercher dans le tableau de données les années, les mois et catégories correspondants (les uns et les autres et non les uns ou les autres)

Supposons que nos recherches portent sur les 200 premières lignes. Voici la formule que nous emploierons en L2 :

=SOMME((((I2:I200)=K1)*((H2:H200)= L1))*(E2:E200))

Voici quelques explications:

Formules Commentaires
I2:I200 Plage de données pour les années
H2:H200 Plage de données pour les mois
E2:E200 Plage de données pour les montants
K1 L'année recherchée
L1 Le mois recherché

Il va falloir penser à la recopie de la formule dans les cellules suivantes, jusqu'à W2, et donc modifier la formule en adressage relatif et absolu. Arrivé à ce point, cherchez donc comment modifier cette formule.

Vous avez trouvé ? Bien sûr, c'est :

=SOMME(((($I$2: $I$200)=$K$1)*(($H$2: $H$200)=L$1))*($E$2: $E$200))

Maintenant il faut faire de cette formule une formule matricielle pour qu'elle n'additionne que les résultats correspondants aux critères. Pour créer cette formule matricielle, il faut non pas faire Entrée, mais Ctrl+Shift+Entrée (Shift pour la touche Majuscule). Votre formule va donc se transformer ainsi :

{=SOMME(((($I$2: $I$200)=$K$1)*(($H$2: $H$200)=L$1))*($E$2: $E$200))}

La formule matricielle est encadrée par les symboles { et }. N'essayez pas d'entrer directement ces symboles. Le résultat est que votre formule sera interprétée comme un quelconque texte.

 

 

Vous pouvez très bien recopier la formule vers la droite par la méthode traditionnelle. MAIS ! Il y un grand mais, car problème. La recopie ne sera pas bonne.

Pour recopier une formule matricielle, il faut utiliser la méthode normale en maintenant la touche Ctrl enfoncée.

Comment savoir si tout c'est bien passé ? En utilisant l'astuce suivante :

Astuce : Sélectionnez une des cellules recopiées, puis essayez de supprimer son contenu. Si vous avez le message Il n'est pas possible de ne modifier qu'une partie d'une matrice, il y a problème.

Il faudra donc annuler par l'icône sous OOo 1.1.x ou l'icône Windows ou Linux sous OOo 2.0.x, ou plus simplement via le raccourci Ctrl+Z pour toutes les versions OpenOffice.org

Tout fonctionne ? Evidement il vous faudra quelques tests pour tout vérifier. Si ça ne va pas, il faut relire ce qui est dit précédemment. Sinon, Encore une fois nous utiliserons la recopie pour copier vers les autres feuilles la zone de cellules de K1 à W2. Au final les feuilles doivent être identiques, sauf pour les données.

Nous arrivons enfin à la synthèse annuelle. Le plus dur est fait.

Il ne reste plus qu'à entrer les formules comme sur l'exemple. et ensuite, à recopier vers la droite pour les différents mois.

Et voici le résultat partiel, qui variera suivant les données entrées. Il suffit de modifier l'année en A1 pour que le tableau se recalcule automatiquement.

Petite amélioration, la suppression de l'affichage des valeurs zéro. A voir dans les options Calc.

Nous venons de voir la première méthode. Les catégories séparées peuvent apporter quelques avantages. On aurait surtout dû améliorer les formules peut visibles. Dans notre exemple, seules 200 lignes servent de référence. Il faudra donc modifier en cas de dépassement. Il faudra également améliorer la présentation.

 

 

 

 

Méthode 2

 

 

Abordons la seconde méthode. 2 ou 3 feuilles suffisent. Dans une feuille que nous nommerons Dépenses, nous allons mélanger toutes les catégories.

Voici un exemple de données. Différence principale nous avons inséré la colonne Catégorie.

Le gros défaut du système est que pour inscrire la catégorie, l'écriture doit être exacte. Si par exemple vous écrivez Electricité et plus loin Electricite, il y aura 2 catégories différentes. Donc pour être certain de ne pas faire d'erreur, il suffit de se servir des listes de tri.

 

 

Avec OOo 2.x uniquement

La Validité existe avec OOo 1.1.x, mais est améliorée dans OOo 2.x avec les Plage de cellules. Dans cet exemple, nous avons inscrits les différents libellés connus.

Ensuite, nous sélectionnons la zone utile dans la colonne Libellé de la feuille Dépenses, puis nous appelons la Validité, via Données, Validité. Il suffit de sélectionner Plage de cellules et d'inscrire la zone de données source.

Enfin, une cellule active de la colonne des Libellés apparaîtra avec un flèche à droite.

Un clic sur cet flèche déroule la liste. Il suffit de sélectionner.

Autre possibilité : le fait d'inscrire les premières lettres aident à afficher la ou les solutions possibles. Tapez Entrée pour valider.

 

 

Et maintenant qu'allons nous faire ? Comme précédemment, créer des colonnes pour calcul le mois et l'année, dans les colonnes I et J. Sans commentaires...

Ensuite, il faut recréer les formules matricielles, dans autant de lignes qu'il y a de catégories. Rien ne vous choque ? Peut-être l'avez-vous remarqué depuis l'exemple précédent. Si on supprime une ligne de données (pourquoi pas ?) qui contient également les formules matricielles, rien ne va plus. Pour contourner élégamment ce petit problème, il suffit de créer nos formules matricielles dans une autre feuille que nous appellerons Analyse.

Dans cette feuille Analyse, en A1 nous entrons l'année, puis dans les cellules B1 à M1, les numéros de mois de 1 à 12.Enfin en B2, nous entrons une première formule :

STOP. Essayez de trouver la formule complète qui analyse la catégorie Eau, avec les adressages relatifs et absolus.

 

La galère ? Un peu, c'est normal, mais bien entendu, vous avez fini par trouver la formule suivante :

= SOMME(((Dépenses.$I$2:$I$200=B$1) * (Dépenses.$J$2:$J$200=$A$1)) * (Dépenses.$E$2:$E$200="Eau")* (Dépenses.$F$2:$F$200))

Une recopie et tout va bien. Pour les autres catégories, rien de plus simple. Il suffit de recopier la première formule (celle ci-dessus) et de remplacer la catégorie, puis de recopier à nouveau à droite.

Et pour les résultats dans ANNUEL? Mais oui, c'est vraiment très simple. Si vous avez oublié (déjà ?), la solution est donnée dans la première version. Il suffit de l'adapter.

Nous avons là encore une solution qui fonctionne. Toutefois, un peu d'amélioration, notamment au niveau de la lisibilité des formules serait bienvenue. Ceci est possible en nommant les cellules ou les zones de cellules.

Dans la feuille Dépenses, nommons comme suit :

Désignation Nom Adresse ou formule
Années AA $Dépenses.$J$2:$J$32000
Mois MM $Dépenses.$I$2:$I$32000
Catégorie Catégorie $Dépenses.$E$2:$E$32000
Montant Montant $Dépenses.$F$2:$F$32000
Année choisie Ans $ANNUEL.$A$1
Mois de 1 à 12 NumeroMois CELLULE("col")-1

Pour nommer des colonnes entières, sélectionnez la colonne complète en cliquant dans le titre de la colonne, puis appelez la boite de dialogue Définir des noms via Ctrl+F3. Modifiez le numéro de ligne (2) dans Assigné à. Donnez un nom, cliquez sur OK.

Pour le numéro de mois, notez une application moins connue de donner un nom à une fonction. Nous allons utiliser la fonction CELLULE. La formule =CELLULE("col")-1 retourne une information sur le numéro de la colonne, moins un. Testez cette formule dans la page Analyse sous les numéros de mois et vous verrez que tout est conforme à nos souhaits.

Enfin, dernier point, dans la feuille Analyse, colonne N, nous recopions les différents types de catégories, toujours en vue de simplifier. Nous voici prêt pour la formule finale.

A vous. Essayez de trouvez la première formule.

La formule est :

{=SOMME(((MM=NumeroMois)*(AA=Ans))*(Catégorie=$N2)*Montant)}

Le moins qu'on puisse dire est que ce principe de formulation est nettement plus lisible que tout ce que nous avons vu précédemment. Il faut bien sûr ensuite recopier les formules, mais maintenant vous maîtrisez.

 

 

 

Présentation Listing

 

 

La présentation sous forme de listing améliore la lisibilité en modifiant la présentation de ligne toutes les x lignes.

Ici il s'agit d'un exemple simpliste mais qui fonctionne. Je me suis servi de place libre dans la feuille Analyse. En ligne 20, il suffit de recopier comme sur l'exemple ci-contre. En B20, on nomme la cellule du nom de Listing. En B21, on nomme EcartListing.

La valeur Listing sera 0 (pas de listing visible) ou 1 (listing visible), et la valeur EcartListing donnera les n lignes à modifier. Pour cet exemple j'ai créé une mise en forme des cellules bleu clair. Vous pouvez choisir ou créer autre chose.

La colonne K de la feuille Dépenses étant libre, on utilise les fonctions MOD et CELLULE. On applique la formule :

=MOD(CELLULE("row");EcartListing)

ceci à recopier autant de lignes que nécessaire.

=MOD() renvoie le reste de la division d'un nombre entier.

=(CELLULE("row") Est une fonction d'information qui donne le n° de ligne.

 

Ensuite, il suffit d'appliquer le formatage conditionnel (sélection du coin opposé en bas à droite, en remontant au coin haut à gauche). La cellule A2 est active. Je vous livre tout chaud la formule à appliquer :

=ET($Dépenses.$K2=0;Listing=1)

Puis on sélectionne le style (voir mise en forme des cellules).

Dans la formule, le ET implique que 2 conditions soient réunies : Le Listing=1 et la valeur contenue dans la colonne K.

Voici le résultat partiel.

C'est beau, non ?

 

Vous êtes satisfait ? Oui ? Pourtant, on peut faire plus simple !
On peut utiliser la fonction LIGNE suivant le même principe. Dans ce cas, nous écrirons :

=MOD(LIGNE(); EcartListing)

La suite ? vous connaissez !

 

 

 


Et maintenant, à vos claviers.

 

Ce site n'est plus mis à jour. Merci aux nombreux visiteurs depuis 2003. Consultez le Wiki LibreOffice.
   

Le tableur Calc par l'exemple : christianwtd@free.fr
Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 2.0 France License.

  .

Site optimisé pour Firefox


Dernière modification : 29/08/2010 à 18h48


Déjà plusieurs visites sur ce site depuis quelque temps (au moins vous et moi)

Compteur non certifié