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

- 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 (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.

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 .

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

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)

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 liste ou 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 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.
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 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)

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 :

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 - Format automatique pour sélectionner une mise en forme pré-établie.

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)

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.

Bon courage !