L'objectif est de rassembler dans une base toutes les informations concernant les budgets
de tous les voyages scolaires de façon à :
- suivre les financements, l'équilibre financier
- d'obtenir les comparatifs entre voyages
- disposer de tableaux synthétiques
1 - définir le classeur
- Ouvrir un
classeur : renommer le premier onglet (double-clic sur l'onglet, ou clic
droit sur l'onglet, renommer) en l'appelant Base et le
second , Calcul
Puis, dans la feuille Base, nous allons saisir les titres :
puis les mettre
en forme (FormatCellules, Alignement Horizontal : Centré,
alignement Vertical : Centré, Renvoyer à la ligne : coché.
Ensuite, nous posons les filtres : Menu Données, Filtre, Filtreautomatique.
Enfin : nous allons poser les volets de façon à avoir la 1ère ligne toujours à
l'écran : Positionné en A2, nous appelons le menu : Fenêtre : Figer
les volets.
Nous allons
définir le format des chiffres que nous saisirons dans la feuille après avoir cliqué
sur le petit carré à droite de la colonne A pour sélectionner toute la feuille. FormatCellule : Nombre avec 2 décimales .
Nous n'avons plus
qu'à saisir les informations pour chaque voyage. Grâce à la saisie semi-automatique,
celle-ci est facilitée.
Une fois, la
première saisie effectuée,nous filtrons sur la colonne A, l'élément ANGLETERRE.
Nous pouvons alors appeler le bouton Somme automatique et c'est la fonction Sous.Total qui
apparaît (et non la fonction Somme, car il y a eu filtrage au préalable). Le
paramètre 9 représente la fonction Somme comme le paramètre 3 correspond à Nbval
(cf. l'aide sur cette fonction). Ici, il convient de modifier la zone proposée D5:D9
en sélectionnant à la souris la zone D1:D9. Avec la poignée de
recopie, nous reproduisons cette formules sur les colonnes suivantes . Nota
: à partir de maintenant, il faudra insérer des lignes au-dessus de la ligne qui
comprend nos sous-totaux afin que les nouvelles informations soient pris en compte.
Grâce à cette fonction, avec n'importe quel filtre, vos sous-totaux seront à jour.
Il est temps d'enregistrer votre classeur : voyage.xls
Vous pouvez rajouter en-dessous des soldes (recettes-dépenses ) =D11-E11
2 - la feuille Calcul
Nous avons noter les dépenses et une partie des recettes, il nous reste à calculer la
participation des familles, pour cela, nous utiliserons la feuille Calcul
pour obtenir un tableau comme celui-ci :
La formule en B2 :
=SOMME.SI(Base!$A:$A;$B$1;Base!D:D)
La formule en D2 :
=SOMME.SI(Base!$A:$A;$B$1;Base!E:E)
La formule en F2 :
=D2-B2
La formule en D3 :
=F2/B3
La formule en F3 :
=ARRONDI(D3;-1) ou =PLAFOND(D3;10) si vous souhaitez arrondir au-dessus (résultat :
770) alors que =PLAFOND(D3;5) affiche : 765
La formule en B4 :
=B3*F3 Remarques : la fonction Somme.Si recherche dans la feuille Base
dans la colonne A, et à chaque fois qu'elle trouve ce qui a été tapé en B1,
elle additionne les valeurs correspondantes dans la colonne D (ou E).
Il ne vous reste qu'à reporter cette somme dans votre feuille Base.
Puis, nous insérons des lignes avant la ligne 9 (incorporation dans la fonction Sous.Total)
La base peut être complétée sur le même principe, des lignes peuvent corrigées,
ajoutées...
Vous pouvez alors obtenir un tableau comme celui-ci :
Le système permet la gestion souple des opérations par simple insertion de lignes ou
correction de chiffres. De plus, l'utilisation des filtres alliée à la fonction Sous.Total
offre une totalisation rapide et permet une visualisation des équilibres financiers par
voyage ou de la ventilation multiple de financements extérieurs (don, subvention...).
Enfin, vous avez une vision globale des voyages (voir même un historique sur plusieurs
années si vous rajoutez un champ supplémentaire (ANNEE)
3 - La construction d'un Tableau Croisé Dynamique
Mais, cette base ne constitue qu'un outil de gestion et ne peut être présenté tel
que à un conseil d'administration. Un Tableau Croisé Dynamique pourra vous permettre de
synthétiser vos données à partir de ces informations.
1ère étape : se positionner dans la base sur une cellule remplie (A5
par exemple), puis appeler le menu Données - Rapport de tableau Croisé Dynamique.
Un assistant vous prend en charge :
Cette première
proposition listeou base de données Excel nous convient :
2ème
étape : Très importante . Excel parcourt la zone et s'arrête une fois
une colonne et une ligne vide trouvées.
D'où l'importance, après, si nous souhaitons que ce tableau se mette à jour, d'inserer
des nouvelles lignes ENTRE ces bornes (ici A1:H18)
Vous avez la trame de votre tableau : il y a des lignes, des colonnes à synthétiser
selon VOTREPROJET. Ici, nous voulons obtenir un bilan récapitulatif de
l'ensemble des voyages en recettes et en dépenses avec éventuellement la possibilité de
mettre en relief les financements particuliers : Don, subventions...
Nous avons les champs de notre base à gauche et un tableau de réception au centre.
les déplacements des champs se font par un simple glissé avec la souris. Dans la zone LIGNE,
nous faisons glisser le champ VOYAGE, nous aurons donc un ligne par
voyage.
Dans la zone DONNEES,
nous faisons glisser les RECETTESPREVUES et DEPENSESPREVUES. Ceux-ci sont alors précédés de la fonction NB
(nombre de valeurs) - car la première ligne est non numérique - Or, la fonction NB
ne nous intéresse guère, nous préférons la fonction SOMME qui permettre la
totalisation des recettes et dépenses de chaque voyage. Pour cela, nous devons modifier
le champ par un simple double clic (sur NB RECETTES PREVUES)
Il convient de
modifier la synthèse par ; en choisissant Somme :
Le nom : Somme RECETTES PREVUES n'est pas très joli, nous allons le
modifier : mais, ôter tous les caractères devant RECETTES PREVUES déclenchera un
message d'erreur : ce nom ne peut être identique au nom du champ. Petit truc : laissez un
espace devant RECETTES PREVUES et l'affaire sera réglée.
Pour forcer un format particulier pour les chiffres affichés (par défaut : Standard),
nous pouvons cliquer sur le bouton portant ce nom et choisir Format Nombre avec 2
décimales
Puis OK
pour sortir du Format Nombre, puis OK pour sortir du champ
dynamique.
Et nous pouvons répéter les opérations pour le champ NB DEPENSES PREVUES
Pour le champ Page,
nous glissons le champ CODE afin de pouvoir effectuer des filtres sur les
différents financements.
Cette étape est terminée. Elle reflète votre projet. Un Tableau Croisé dynamique ou
TCD a besoin d'au moins 1 champ en Données et 1 champ en ligne ou colonne.
Dans cette
étape, choisissez de préférence une nouvelle feuille (par défaut) plutôt qu'un
emplacement sur une feuille existante.
Le bouton Options sera vu un autre fois, mais il est intéressant.
Appuyons sur FIN.
Nous avons donc
une nouvelle feuille (appelons-là TCD1), avec notre tableau de
synthèse. Cependant, il peut être encore amélioré facilement. Les champs (en grisé)
peuvent se déplacer à la souris très facilement vers les LIGNES (rectangle
vertical), vers les COLONNES (rectangle horizontal) vers les PAGES
(succession des rectangles horizontaux). Nous allons glisser la zone Données
vers le haut (rectangle horizontal) pour obtenir un tableau plus correct :
4 - La création d'un nouveau champ
Nous allons rajouter un nouveau champ pour obtenir les soldes par voyage.
Pour cela, nous
faisons un clic droit à l'intérieur du TCD, et choisissons Formules, Champ
calculé
Le nouveau champ
s'appellera SOLDE et la formules s'obtient en double-cliquant après
avoir conservé le signe =, sur le champ RECETTES PREVUES, puis on tape
de signe - puis on double-clique sur le champ DEPENSES PREVUES.
Enfin on appuie sur le bouton Ajouter afin d'incorporer ce nouveau champ. Puis OK
Le tableau se présente ainsi :
Pour transformer
le nom Somme SOLDE en <espace> SOLDE,
faites un clic droit sur un élément de cette colonne (ici D) et choisissez Champ.
Vous retrouverez la boîte de dialogue Champ Dynamique vue au cours de la
construction du tableau.
Avec quelques efforts de formatage de cellules, notre tableau ressemble à ceci :
Mais, on peut toujours appeler le menu Format - Formatautomatique
pour sélectionner une mise en forme pré-établie.
5 - L'utilisation du TCD
Si de nouvelles informations sont saisies, veillez à les saisir avant les bornes
définies à la 2ème phase de la construction du TCD, afin quelles soient prises en
compte .
Exemple : insérons 2 lignes avant la ligne 2 de la feuille Base.
Par le menu Données, Actualiser les données ou clic droit), le tableau
incorporera ces valeurs :
Grâce au champ Page, nous pouvons ne faire apparaître qu'une partie choisie
des données : (par exemple : les subventions du Département)
6 - La modification du TCD
Pour modifier les champs : remplacer les recettes et dépenses prévues par les
réelles , il n'est pas nécessaire de refaire toute la procédure. Un clic droit
à l'intérieur du tableau, le sous-menu Assistant, vous permet d'accèder à la
disposition du tableau et de faire les modifications nécessaires.
Nous pouvons également faire apparaître le détail d'un voyage en choisissant
d'autres champs. Placé à l'intérieur du TCD, nous rappelons l'assistant (clic droit
ou menu Données - Rapport de Tcd)
le tableau devient alors :
Devant la multiplicité des possibilités et des souplesses offertes par l'utilisation
d'Excel en base de données avec l'outil du tableau croisé dynamique, vous devriez
(logiquement) revoir l'ensemble de vos tableaux classiques au profit de bases de données
plus complètes et exploitables suivant de multiples critères.