Sommaire
Excel Perfectionnement
Excel Graphique
Divers Excel
Excel Vba début
Utilitaires
Gestion
(philosophie)
Gestion
(techniques)
Liens
Gfc Windows

écrivrez-moi !














 

 

Compléments Excel

 

 

Base de donnée et tableau croisé : faire un budget de voyage

Fichier réalisé : voyage.xls

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

voy1.gif (1888 octets)- 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 :

voy2.gif (2542 octets)

Voy3.gif (2433 octets) puis les mettre en forme (Format Cellules, Alignement Horizontal : Centré, alignement Vertical : Centré, Renvoyer à la ligne : coché.
Ensuite, nous posons les filtres : Menu Données, Filtre, Filtre automatique.
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.

 

 

voy4.gif (1744 octets)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. Format Cellule : Nombre avec 2 décimales .

 

voy5.gif (3030 octets)Nous n'avons plus qu'à saisir les informations pour chaque voyage. Grâce à la saisie semi-automatique, celle-ci est facilitée.

 

voy6.gif (5617 octets)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 voy7.gif (76 octets) 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 .
voy8.gif (3372 octets)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 :

wpeE.jpg (14376 octets)

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.
voy10.gif (2815 octets)

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 :

 

 

voy11.gif (12826 octets)

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)

voy12.gif (1719 octets)             voy13.gif (2301 octets) 

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 :

 

voy14.gif (3828 octets)Cette première proposition liste ou base de données Excel nous convient :

 

 

 

 

 

voy15.gif (2156 octets)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)

 

 

voy16.gif (5596 octets)

Vous avez la trame de votre tableau : il y a des lignes, des colonnes à synthétiser selon VOTRE PROJET. 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.
voy17.gif (2518 octets)Dans la zone LIGNE, nous faisons glisser le champ VOYAGE, nous aurons donc un ligne par voyage.

 

 

 


 

voy18.gif (2774 octets)Dans la zone DONNEES, nous faisons glisser les RECETTES PREVUES et DEPENSES PREVUES. 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)

voy19.gif (2732 octets)Il convient de modifier la synthèse par ; en choisissant Somme :
voy20.gif (1740 octets)

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.
voy21.gif (2816 octets)

 

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

 

 


voy22.gif (2161 octets)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

 


voy23.gif (1946 octets)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.

 

voy24.gif (4145 octets)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.

 

 

 

voy25.gif (3981 octets)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 :


voy26.gif (3135 octets)

 

 

 

 


4 - La création d'un nouveau champ

Nous allons rajouter un nouveau champ pour obtenir les soldes par voyage.

voy27.gif (4834 octets)Pour cela, nous faisons un clic droit à l'intérieur du TCD, et choisissons Formules, Champ calculé

 

 

 

 

 

 

voy28.gif (3750 octets)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 :

 

 

voy29.gif (3719 octets)

 

 

 

 

voy30.gif (3044 octets)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 :

 

 

voy31.gif (3812 octets)

 

Mais, on peut toujours appeler le menu Format - Format automatique pour sélectionner une mise en forme pré-établie.

 

 

 

voy32.gif (3967 octets)

 

 

 

 

 

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.

voy36.gif (4689 octets)

Par le menu Données, Actualiser les données ou clic droitvoy38.gif (272 octets)), le tableau incorporera ces valeurs :
voy37.gif (2993 octets)

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)

 

voy39.gif (3505 octets)

 

 

 

 

 

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.

voy33.gif (1579 octets)

 

 

 

 

 

voy34.gif (2355 octets)

 

 

 

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)

voy40.gif (2987 octets)

le tableau devient alors :

voy41.gif (6713 octets)

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.

Bon courage !

 

début                                         Sommaire