Formation Excel en pdf les formules de calcul, tutoriel & guide de travaux pratiques en pdf.

Réalisation du tableaux

A. Entrée des données non calculées

Dans cette formation Excel, entrez les valeurs de départ pour les ventes de A et B au premier trimestre (300 000 et 50 000), ainsi que le détail des coûts fixes, qui comme leur nom l’indique n’ont pas à être calculés.

B. Entrée des formules de calcul

Voici, données en français, les formules de calcul rencontrées dans le tableau; n’appliquez ces formules que dans la première colonne, les autres colonnes seront obtenues par une recopie ultérieure.

Les zones de totalisation
* La zone TOTAL VENTES
= VENTES A + VENTES B
* La zone TOTAL COUTS VARIABLES
= SALAIRES VARIABLES
+ ENTRETIEN
+ REPARATIONS
+ MATIERES PREMIERES

Le calcul des charges variables
On utilisera les formules suivantes pour calculer les charges variables. Ces formules sont bien sûr arbitraires et on peut imaginer que chaque entreprise décide celles dont elle estime qu’elles sont le plus proche de la réalité.
ENTRETIEN = 10% du TOTAL VENTES
REPARATIONS = 12% du TOTAL VENTES
MATIERES PREMIERES = 20% des VENTES A + 15% des VENTES B
SALAIRES VARIABLES :
Si le TOTAL VENTES du trimestre est supérieur à 450 000 €, les salaires supplémentaires sont estimés à 30% de la « frange » qui dépasse 450 000 €, sinon ils sont nuls.
exemple :
TOTAL VENTES = 500 000 €.
SALAIRES VARIABLES = (500000 – 450000) * 30% = 50000 * 0,3 = 15000 €.

Les zones de marge
– MARGE SUR COUTS VARIABLES = TOTAL VENTES – TOTAL COUTS VARIABLES
– RESULTAT DU TRIMESTRE = TOTAL VENTES – TOTAL DES COUTS
– RESULTAT CUMULE = TOTAL DES RESULTATS DE L’ANNEE, CUMULE…
C’est-à-dire RESULTAT DU TRIMESTRE EN COURS + RESULTAT CUMULE DU TRIMESTRE PRECEDENT –
Attention, cette formule est impossible au trimestre 1, il vous faut alors trouver autre chose…

Détails des calculs
N’oubliez pas de commencer la saisie de vos formules de calcul par la touche « = ».
* Pour ce qui concerne les sommes (totalisations)
, Excel vous propose 3 solutions parmi lesquelles vous allez devoir choisir à chaque fois la plus appropriée.
– Le bouton SOMME AUTOMATIQUE. En cliquant sur celui-ci, Excel met en oeuvre la fonction
SOMME(début : fin).
ex:
TOTAL DE VENTES est défini automatiquement par B7=SOMME(B5:B6)
Vous pouvez mettre en oeuvre cette fonction vous-même : entrez le signe égal (=) dans la cellule devant contenir votre formule, tapez « SOMME(« , puis sélectionnez à la souris ou avec votre clavier la zone à sommer; fermez la parenthèse et validez.
Vous devez savoir, pour utiliser cette fonction, qu’Excel interprète les deux points (« : ») comme séparateur de zone qui signifie d’une certaine façon « JUSQU’A », mais que vous pouvez utiliser aussi le point virgule signifiant « ET », pour sommer plusieurs zones:
SOMME(DébutZoneA:FinZoneA ; DébutZoneB:FinZoneB ; CelluleUnique ;…)
où  « : » signifie « jusqu’à » et  « ; » signifie « et »
Cette « grammaire » est utilisée par toutes les fonctions Excel, il est donc primordial de bien la comprendre.
Il est encore possible, voire obligatoire, pour les sommes ne faisant intervenir que des cellules non contigües, d’entrer le signe égal, puis de cliquer sur le premier terme, d’entrer l’opérateur (« + »), puis de cliquer sur le second terme, etc. enfin de valider.
Cette dernière solution permet aussi de calculer les zones de marge, qui sont des soustractions de cellules éloignées.
ex:
la MARGE SUR COUTS VARIABLES est définie par vos soins par
B22=B7-B20
* La seule véritable difficulté réside dans le calcul des SALAIRES VARIABLES.
Pour cela, nous allons calculer (TOTAL VENTES – 450 000). Le résultat sera NEGATIF si le TOTAL VENTESest inférieur à 450 000 et positif ou nul sinon. Il faut donc calculer 30% de l’expression (TOTAL VENTES – 450 000) si celle-ci est positive.
Pour automatiser ce que nous venons de décomposer, nous allons utiliser la fonction MAX( ) d’Excel qui permet de choisir le plus grand nombre dans une liste de cellules ou de zones.
ex:
si une cellule contient l’expression = MAX(2;3;7;12;0), Excel affichera 12.
Comme nous venons de le voir, le point virgule signifie « ET » et permet l’énumération d’éléments, mais la fonction MAX( ) accepte aussi des zones délimitées par « : » (« JUSQU’A »).
Les éléments de la liste entre parenthèses peuvent être :
– des valeurs (45;8;-9…)
– des références de cellules de la feuilles de travail des références aussi bien Absolues que
Relatives (B12; $D$21…)
– des zones délimitées par « : » (B2:C4…)
– des expressions mathématiques comprenant des chiffres ou/et des références de cellules
(E5*4;5+3;$F$2*5…)
Dans notre cas, nous choisirons donc de multiplier le maximum des 2 nombres suivants : TOTAL VENTES –
450000 et
ZERO par 0,3 (c.a.d 30%). Cette solution nous garantissant de ne pas obtenir un résultat
NEGATIF !
La formule sera donc :
B16=MAX(B7-450000;0)*0,3
* La dernière difficulté réside dans le calcul du RESULTAT CUMULE
– Ce dernier sera calculé en additionnant le RESULTAT CUMULE du trimestre en cours avec le RESULTAT CUMULE du trimestre précédent.
Mais… il faut faire attention, car au trimestre 1 ce calcul est impossible : et pour cause, il n’y a pas de trimestre précédent. Le RESULTAT CUMULE du trimestre 1 est donc calculé comme (et par conséquent égal au) RESULTAT CUMULE du trimestre en cours.

………

Si le lien ne fonctionne pas correctement, veuillez nous contacter (mentionner le lien dans votre message)
Excel les formules de calcul (408 Ko) (Cours PDF)