Archives de catégorie : Excel

Excel Tableau croisé dynamique

tableau croise dynamique 2

Sélectionnez la totalité des données avec les entêtes de colonnes de B6 à E23. Cliquez sur le menu Insertion puis Tableau Croisé Dynamique. Une fenêtre s’ouvre, vous pouvez cliquer directement sur OK. Une colonne s’affiche à droite de la fenêtre.

tab croisé dyn 3

Cochez les 4 cases « nom », « montant », « date », « courses ». Ces 4 champs sont classés automatiquement en tant qu’ « Etiquettes de lignes » et le tableau généré à gauche n’est pas du tout lisible, alors que l’objectif d’un tableau croisé dynamique est d’être plus clair que le tableau d’origine.

tab croisé dyn 4

Nous allons arrangé cela en faisant glisser le champs « course » des étiquettes de lignes vers les étiquettes de colonnes. Et voilà le résultat :

tab croisé dyn 5

Vous pouvez ajouter un graphique. Cliquez d’abord dans le tableau croisé dynamique pour le sélectionner, puis aller dans le menu « option », puis « Graphique Croisé Dynamique »

tab croisé dyn 6

Excel Consolidation

consolidation

Vous pouvez tout de suite commencer à compléter les colonnes et lignes total des tableaux du Mans, de Rennes et de Tours.

Sélectionnez les cellules vides du 1er tableau « Somme » du Tableau de Bord Général. Puis cliquez sur le menu « Données » puis « Consolider ».

consolider1

 

Choisissez la fonction Somme

Puis cliquez sur le petit bouton rouge de la ligne Référence pour sélectionner le 1er tableau du Mans.

consolider2

 

CLiquez sur Ajouter pour que cette sélection soit mémoriser dans la zone « Toutes les Références »

consolider3

 

Faites la même chose pour les 2 autres tableaux : ceux de Rennes et de Tours

consolider4

 

Validez en fin en cliquant sur OK.

Dans le tableau de Bord général, vous pouvez suivre ce tutoriel en changement seulement la fonction somme par la fonction moyenne.

Excel Planning

Commençons par la 1ère feuille de notre classeur :

excel planning 1

Les colonnes début, fin, pause, et total sont au format heure. Le total pour chaque ligne est le temps qui s’écoule entre le début et la fin, la pause en moins. Ce qui donne =F2-E2-G2 pour le 1er juin 2017. Puis vous pouvez utiliser la poignée de recopie pour recopier cette formule pour toutes les dates.

Lorsque vous faites la somme automatique du total du mois de juin, le total qui s’affiche ne correspond pas au total que vous attendez. Au lieu de 39h, nous avons seulement 15h. Nous sommes donc en dessous de 24h. En effet, le format heure fonctionne comme une horloge : lorsque 24h sont passées, nous recommençons une nouvelle journée à 0h. Pour que l’on puisse dépasser 24h, nous devons changer le type de format heure. Il en existe plusieurs dans Excel.

Puis pour calculer le salaire horaire, nous devons passer par une étape intermédiaire : le total au centième. En effet : un salaire est en base décimale (nous allons jusqu’à 99€ avant de passer à 100€), alors que le format heure est en base hexadécimale (nous allons jusqu’à 59 min avant de passer à 1h). Le total au centième est le total d’heure non pas en hexadécimal, mais en décimal. Pour faire le calcul, pas besoin de formule. Récupérons la valeur du total et changeons seulement le format de la cellule. Nous pouvons maintenant calculer le salaire en fonction du tarif horaire : =H18*H19

excel planning 2

 

Même chose pour la feuille du mois de juillet.

excel planning 3

Pour la feuille Bilan, récupérons les salaires des mois de juin et juillet et faisons une somme automatique. Pour récupérer le salaire de juin, commencer par taper le signe égale « = » pour commencer la formule, puis cliquez sur la feuille juin pour sur le salaire de juin. Ne cliquez pas ensuite sur la feuille Bilan, appuyez seulement sur la touche Entrée pour valider votre formule, et Excel reviendra automatiquement sur la feuille Bilan.

excel planning 4

 

Terminons par la feuille Base. Elle contient ce qu’il possible d’afficher dans les colonnes Missions, lieux et Véhicules des mois de juin et juillet, Par exemple dans la feuille de juin, lorsque vous cliquez en B2, il y a déjà le texte « Formation » dans la cellule, mais il s’affiche aussi sur la droite de la cellule la petite flèche d’un menu déroulant. Cliquez dessus, et dans une liste déroulante s’affiche la liste des missions qui existent dans la feuille Base.

Nous pouvons créer ce genre de menu déroulant grâce à la fonctionnalité « Validation des données ». Les colonnes Mission, Lieu et Véhicule des feuilles juin et juillet sont déjà remplies, il nous faut donc supprimer ces valeurs mes aussi les menus déroulants. Sélectionnez ces 3 colonnes de A2 à D16, puis allez dans le menu « Données » puis « Validation des données ». Une fenêtre s’ouvre. Dans le menu déroulant « Autoriser », choisissez « Tout ».

Maintenant, nous allons remettre les menus déroulants les colonnes Mission, lieu et Véhicule. Sélectionnez ces 3 colonnes de A2 à D16, puis retournez dans le menu « Données » puis « Validation des Données ». Dans le menu déroulant « Autoriser » choisissez « Liste ». Puis dans la source, nous devons écrire la formule qui permet de récupérer les données de la feuille base. Si vous écrivez la formule =base!A2:A7 vous aurez un message d’erreur. Pour éviter ce problème, nous allons donner un nom à cette plage de données. Cependant nous allons dans un 1er temps supprimer les noms qui existent déjà. Allez dans le menu Formule puis Gestionnaire de noms. Sélectionnez tous les noms puis cliquez sur Supprimer. Maintenant nous allons recréer ces noms. Fermez cette fenêtre. Allez dans la feuille base et sélectionnez les missions de A2 à A7. Juste au-dessus de la colonne A et à gauche de fx, il y a écrit A2, c’est le nom de la 1ère cellule sélectionnée. Cliquez dessus et supprimer A2 pour écrire « mission » à la place (attention la casse minuscule majuscule compte). Validez en appuyant sur Entrée. maintenant vérifiez si le nom « mission » est bien dans le gestionnaire des noms.

Vous allez maintenant pouvoir utiliser ce nom pour la validation des données des feuilles juin et juillet. sélectionnez à nouveau les colonnes mission, lieu, véhicule de la feuille juin de B2 à D16. Puis cliquez sur le menu Données, puis Validation des données, et choisissez Liste dans le menu déroulant Autoriser. Dans la ligne source, vous pouvez ajouter =mission

Excel Le poulailler : somme, pourcentage

1. Objectif de ce tutoriel et calcul du nombre d’oeufs produits

2. Report du total d’oeufs produits
dans le 2e sous tableau pour calculer le total TTC

3. Calcul du 1er total TTC avec TVA

4. Calcul du 2e total TTC avec TVA
après prise en compte des dégats causés par le renard

 

mon poulailler calcul tva pourcentage

Cette feuille est composée de 3 petits tableaux.

Dans le 1er tableau, complétez la colonne total et la ligne total. Vous pouvez faire des sommes automatiques ligne par ligne et colonne par colonne. Mais il est aussi possible dans cet exemple de faire toutes les lignes et colonnes en même temps : faîtes une sélection de B4 à F9, puis cliquez sur la fonction Sigma de la somme automatique.

Le 2e tableau reprend le total général en F9 du 1er tableau. Il est préférable de récupérer la valeur de B9 par une formule pour éviter les erreurs de recopiage. En B12, vous pouvez donc écrire la formule =F9

Le prix unitaire HT de chaque oeuf vous est donné. Il vous sert à calculer le total HT. La formule est donc =B12*B13

Puis en B16, nous allons calculer la TVA en euros en fonction de la TVA en %. La formule est =B14*B15

En B17, la formule du total TTC est donc =B14+B16

Enfin, la 3e tableau facultatif prend en compte les aléas naturels comme le prélèvement du renard. Il mange 8% des œufs. Le renard n’est pas intéressé par l’argent, son prélèvement s’effectue directement à la production d’œufs. En E13, nous récupérons donc le total F9 puis nous allons soustraire 8% de ce même total F9. La formule est donc =F9-F9*E12

Je vous laisse calculer les nouveaux totaux HT et TTC

Excel vente de meubles : condition, moyenne, pourcentage

1ere feuille :

vente meubles moyenne pourcentage condition si

2e feuille :

vente meubles moyenne pourcentage condition si 2

Ce classeur se compose de 2 feuilles :
1 – Ventes 1er semestre
2 – prix de revient

Vous remarquez que les 2 listes d’articles des 2 feuilles contiennent les mêmes articles dans le même ordre, ce qui va nous faciliter le travail pour faire le lien entre les 2 feuilles.

Pour remplir la colonne Marge réalisée, nous allons écrire la formule pour le 1er article puis utiliser la poignée de recopie pour calcule la marge pour tous les articles.

La marge réalisée est la soustraction du prix de vente avec le prix de revient. C’est 2 valeurs sont sur 2 feuilles différentes. Cliquez dans D5 de la 1ere feuille pour écrire la formule de la marge réalisée du ART01. Tapez = puis cliquez sur C5 et tapez le moins de la soustraction. Il manque maintenant le prix de revient. Pour récupérer cette variable et l’inclure dans la formule, cliquez sur l’onglet de la feuille prix de revient en bas du tableau puis cliquez sur C5. Attention, la tentation serait de cliquez sur l’onglet de la feuille « Vente 1er sem » pour conclure la formule dans la 1ere feuille, mais la variable Prix de revient!C5 que vous venez de récupérer va se transformer en Vente 1er sem!C5 si vous faites ça. Donc restez sur la feuille Prix de revient et appuyez sur la touche Entrée : vous allez automatiquement revenir la feuille Vente 1er sem et votre formule sera validez en même temps.

Il ne vous reste plus qu’à utiliser la poignée de recopie dans la cellue D5 pour recopier automatiquement la formule à la colonne entière Marge réalisée des 15 articles .

Je vous laisse compléter la colonne Bénéfice Réalisé et Part du Bénéfice.

Une commission est donnée au vendeur si le bénéfice réalisée dépasse 10000 €. Cette commission vaut 5% du bénéfice réalisée. Ecrivez une formule condition Si dans la colonne Commision en incluant le calcul de la commission en fonction du bénéfice réalisée.