Archives pour la catégorie Bureautique

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.

Excel vente hifi vidéo : condition si, moyenne, pourcentage

vente hifi video micro moyenne pourcentage condition si

Les ventes de l’année en cours sont composées de 3 types :
- Hi-fi
- Vidéo
- Micro

Vous pouvez calculer la colonne total (à droite de Micro) et la ligne Total (sous Décembre) en une seule manipulation : Faites une sélection de B6 à E18 puis cliquez sur l’icône de la somme automatique.

Pour calculer la moyenne des ventes, calculer la moyenne du mois de janvier ligne 6, puis utiliser la poignée de recopie pour recopier la formule sur tous les mois de l’année.

La répartition des ventes est le pourcentage de chaque mois sur l’année. Nous pouvons donc écrire la formule de janvier en récupérant le total de janvier et en le divisant par le total de l’année et en multipliant le tout par 100 : =E6/E18*100

Attention pour cette colonne, si vous utilisez la poignée de recopie pour recopier la formule sur tous les mois, cela va engendrer des erreurs. Effectivement, la cellule du total du mois change de ligne en ligne : E6 puis E7 puis E8 etc. mais ce changement s’effectue aussi automatiquement sur le total de l’année : E18 puis E19 puis E20. Pour fixer la cellule du total de l’année à E18, nous sommes obligés d’ajouter le signe $ devant le numéro de ligne, ce qui donne la formule =E6/E$18*100 pour le mois de janvier, puis vous pouvez utiliser la poignez de recopie pour tous les autres mois.

Enfin, il n’est pas nécessaire de multiplier par 100 cette division si vous utilisez le format de cellule pourcentage (icône %).

Il nous reste enfin les colonnes Objectifs et Commissions à compléter. Nous pouvons définir que pour chaque mois, le total à atteindre est d’au moins 10000 €. Pour chaque mois, si ce niveau est atteint, nous affichons “atteint”, s’il n’est pas atteint, nous affichons “non atteint”. Nous allons allons donc écrire une formule Si : = SI(E6>=10000;”atteint”;”non atteint”)

Pour la colonne commission, à chaque fois que l’objectif est atteint, nous allons calculer la commission du vendeur, qui correspond à 5% du dépassement des 10000€. Il n’a pas de commission si l’objectif n’est pas atteint. Nous avons donc besoin d’une autre formule SI : =SI(E6>=10000;(E6-10000)*5/100;”")

 

Ajouter le menu Développeur dans le ruban Excel

Pour rendre accessible le menu “Développeur” de Excel, vous devez vous rendre dans les Options Excel :

Dans Excel 2007, cliquez sur la grosse boule dans le coin en haut à gauche, puis en bas du menu cliquez sur Options Excel.

ajoutboutonform

En restant dans le menu standard de la colonne de gauche, cochez la 3e case “Afficher l’onglet Développeur dans le ruban”.

 

Vous pouvez fermer les options Excel et vous apercevoir qu’il y a un nouvel onglet dans le ruban après l’onglet “Affichage” qui s’appelle “Développeur”.

Afficher le bouton Formulaire dans Excel

Le bouton formulaire n’existe pas dans les rubans d’Excel par défaut. Vous devez donc afficher le bouton en allant dans les Options Excel.

Dans Excel 2007, Cliquez dans la grosse boule en haut à gauche, puis Options Excel en bas à droite

ajoutboutonform

Cliquez sur la rubrique Personnaliser dans la colonne de gauche, puis sur le menu déroulant “Commandes Courantes” pour choisir “Commandes non présentées sur le ruban”.

ajoutboutonform2

Cherchez et Cliquez sur Formulaire dans la liste sous les “Commandes non présentées dans le ruban” puis cliquez sur le bouton Ajouter.

ajoutboutonform3

La Commande Formulaire est maintenant dans le colonne de droite.

ajoutboutonform4

Vous pouvez voir l’icône de la commande Formulaire au-dessus des menus Accueil, Insertion, Mise en Page, etc.

ajoutboutonform5

Excel créer un bouton insertion avec une Macro VBA

Voici notre objectif  : nous souhaitons ne pas être obligé d’utiliser l’ascenseur d’Excel pour descendre en bas d’un tableau qui contient déjà beaucoup de lignes pour y taper une donnée supplémentaire. De plus nous souhaitons que cette donnée ne soit pas ajoutée après la dernière ligne du tableau, mais à l’avant dernière ligne, juste avant la ligne Total. Et pour corser le tout, cette donnée est numérique, et doit être pris en compte dans la formule SOMME de la ligne total.

Pour cela nous allons essayer 3 méthodes différentes :
1. Le formulaire généraliste d’Excel (généré automatiquement)
2. L’enregistrement d’une macro à la souris
3. La programmation d’une macro au clavier

Nous allons voir que seule la 3e méthode nous permet de concrétiser tout notre “cahier des charges”.

1. Le formulaire généraliste d’Excel (généré automatiquement)

Pour réaliser la 1ère méthode, vous devez avoir le bouton formulaire accessible. En effet, le bouton formulaire n’existe pas dans les rubans d’Excel par défaut. Vous devez donc afficher le bouton en allant dans les Options Excel. Voir l’article Ajouter le bouton formulaire dans Excel

Dans cet exemple je vais utiliser le bouton Formulaire d’Excel qui génère un Formulaire qui n’est pas personnalisable : il réutilise les libellés de vos en-têtes de colonne que vous avez vous-mêmes taper dans votre tableau et il vous propose toujours les mêmes boutons de validation “Nouvelle” “Précédente” “suivante” “Critères” “Fermer”.

j’ai donc sélectionné mon tableau en prenant en compte mes en-têtes de colonne et la ligne total, puis j’ai cliqué sur le bouton Formulaire. En tapant une nouvelle entrée et en cliquant sur le bouton “Nouvelle”, la nouvelle ligne est insérée sous la ligne Total, alors que je voulais qu’elle soit insérée au-dessus de la ligne Total.

insertionparformgeneral3

Dans ce 2e exemple, j’ai sélectionné le tableau sans la ligne total. Quand j’utilise à nouveau le formulaire pour insérer une nouvelle ligne, voici le message d’erreur qu’Excel m’affiche

insertionparformgeneral2

Enfin dans ce dernier exemple, j’ai sélectionné seulement une cellule au-dessus de la ligne Total, mais le résultat est le même que dans le 1er exemple ci-dessus.

insertionparformgeneral

Nous allons donc aborder une autre fonctionnalité d’Excel : les macros.

2. L’enregistrement d’une macro à la souris

Les macros permettent de déclencher une séquence d’opérations qui s’enchaînent automatiquement. Cette séquence peut être créée en enregistrant vos clics de souris.

enregistremacro  enregistremacro2

Mais parfois cette méthode à la souris ne fonctionne pas, elle n’est adaptée qu’au macro les plus simples.

3. La programmation d’une macro au clavier

Je vais vous présenter ici une macro qui ne peux pas être enregistrée à la souris. Il vous faut donc écrire le code VBA au clavier.

Je pourrais écrire cette macro en l’appliquant directement à un formulaire personnalisé, mais pour apprendre le VBA plus en douceur, j’ai décidé de me passer de formulaire dans cet exemple. J’ai donc laissé volontairement une ligne vide au-dessus du tableau et je vais ajouter un bouton “insérer” à côté de cette ligne vide.

Si vous n’avez pas encore le menu Développeur dans le ruban d’Excel, à droite du menu Affichage, vous devez rendre accessible le menu “Développeur” en vous rendant dans les Options Excel : voir l’article Ajouter le menu Développeur dans le ruban Excel

Une fois que vous disposez du menu Développeur, Cliquez dessus, puis cliquez sur “Insérer” dans le bloc “Contrôles” puis sur la première icône, un rectangle gris. Vous pouvez maintenant dessiner à côté de votre tableau un rectangle à la souris dans lequel vous pourrez écrire “Insérer”.

excel macro simple

Maintenant, il s’agit de rendre fonctionnel le bouton “Insérer” quand on clique dessus. Nous allons donc écrire une macro que j’ai appelé AjoutLigne et que nous allons associer au bouton “Insérer”. Pour cela dans le menu Développeur cliquez sur l’icone “Visual Basic” tout à gauche du ruban.

J’ai procédé par étape, et nous pouvons tester si la macro fonctionne à la fin de chacune de ces étapes.

1ere étape : Ajouter une ligne vide au-dessus de la ligne total

Les lignes qui commencent par un guillemet (‘) ne sont pas du code mais des commentaires.


Sub AjoutLigne()

‘Aller en A3 ; c’est la cellule qui contient la première donnée
Range(“A3″).Select

‘Aller à la dernière ligne remplie : la ligne total. Nous allons donc vérifier à chaque ligne de la colonne A si la cellule contient le texte “total”. Pour cela, nous devons faire une boucle. A chaque itération, la cellule pris en compte (la cellule active) est celle de la ligne en dessus. La boucle s’arrête lorsque le texte “total” est trouvé dans la cellule active.
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = “total”

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

End Sub

Vous pouvez tester cette macro. Pour cela nous devons d’abord associer le code au bouton “Insérer”

Retounez dans Visual Basic pour compléter la macro.

2e étape : Récupérer les valeurs tapées dans la 1ère ligne pour les ajouter dans la ligne insérée vide


Sub AjoutLigne()

‘Aller en A3
Range(“A3″).Select

‘Aller à la dernière ligne remplie : la ligne total
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = “total”

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

‘prendre les valeurs de la première ligne pour les mettre dans la nouvelle ligne
ActiveCell.Value = Range(“A1″).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(“B1″).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(“C1″).Value

End Sub

3e étape : AJouter la formule SOMME dans la ligne total


Sub AjoutLigne()

‘Aller en A3
Range(“A3″).Select

‘Aller à la dernière ligne remplie : la ligne total
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = “total”

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

‘Récupérer le numéro de cette ligne pour construire la formule Somme du Total plus bas dans le code
LigneFinSomme = ActiveCell.Row

‘prendre les valeurs de la première ligne pour les mettre dans la nouvelle ligne
ActiveCell.Value = Range(“A1″).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(“B1″).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(“C1″).Value

‘Ajouter la formule Somme pour calculer le total
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaLocal = “=SOMME(C3:C” & LigneFinSomme & “)”

End Sub

4e étape : Supprimer les valeurs de la 1ère ligne pour pouvoir insérer directement à nouveau d’autres valeurs


Sub AjoutLigne()

‘Aller en A3
Range(“A3″).Select

‘Aller à la dernière ligne remplie : la ligne total
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = “total”

‘Insérer une ligne vide audessus de la ligne total
Selection.EntireRow.Insert

‘Récupérer le numéro de cette ligne pour construire la formule Somme du Total plus bas dans le code
LigneFinSomme = ActiveCell.Row

‘prendre les valeurs de la première ligne pour les mettre dans la nouvelle ligne
ActiveCell.Value = Range(“A1″).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(“B1″).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Range(“C1″).Value

‘Ajouter la formule Somme pour calculer le total
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaLocal = “=SOMME(C3:C” & LigneFinSomme & “)”

‘Enlever les valeurs insérer en première ligne pour pouvoir faire une nouvelle insertion sans être obligé de supprimer à la main ces données
Range(“B1″).Value = “”
Range(“C1″).Value = “”

End Sub