EXCEL

1. EXCEL Techniques avancées

2. Les Noms dans les formules Construire ce tableau en démarrant de la cellule A1 Au lieu d'exprimer les formules en utilisant les références de cellules (exemple : = C7*D7) on préférera attribuer des noms aux différentes plages et on les utilisera dans les calculs. Cela donnera des formules du style : = Qté * PrixUnitaire C'est une autre façon de travailler.

3. Définir un nom pour une cellule Sélectionner la cellule. Cliquer sur la zone "Nom" située à l'extrémité gauche de la barre de formule Tapez le nom de la cellule ou de la plage à la place de G3 : Appuyez sur ENTREE. Attention un espace dans le nom est interdit Application : Appelez TauxEuro la cellule G3, appelez TauxDollar la cellule G4, et TauxTVA la cellule B16

4. Définir un nom pour une plage de cellules 1ère méthode : Sélectionner la plage à nommer Tapez le nom dans la zone nom : Application : Nommez la plage C7:C13 : "Qté":

5. 2ème méthode : se servir des en-têtes de colonnes Cette méthode est plus rapide pour nommer plusieurs plages en même temps Sélectionnez la zone contenant les plages à nommer y compris les titres de colonnes Utilisez la commande Insertion/Noms/Créer et choisissez : Ligne du haut Application Nommez les colonnes de la plage D6:G13 Vérifiez la création des noms en déroulant la liste des noms :

6. Se servir des en-têtes de lignes Sélectionnez la zone contenant les plages à nommer y compris les titres de lignes Utilisez la commande Insertion/Noms/Créer et choisissez : Colonne de gauche Application Nommez les lignes de la plage D14:G16 Vérifiez la création des noms en déroulant la liste des noms :

7. Supprimer un nom Si un nom a été mal défini ou n'est pas nécessaire il faut le supprimer par : Insertion/Nom/Définir. Sélectionner le nom dans la liste Cliquer "Supprimer", puis "OK"

8. Coller un nom dans une formule Utiliser la touche F3 pour coller les noms dans les formules Application : exemple de création de formule Sélectionnez la cellule E7 Tapez le signe égal (=) pour commencer la formule. Appuyez sur la touche F3 pour accéder à la"liste des noms" Sélectionnez le nom Qté Tapez le signe "multiplier" (*) Appuyez à nouveau sur la touche F3 Sélectionnez le nom PrixUnitaire La formule devient : Validez Recopiez la formule et vérifiez son écriture dans toutes les cellules

9. Remplacer des références de cellule par un nom défini Si vous avez écrit des formules avec des références avant de créer les noms, les noms définis par la suite peuvent venir remplacer les références . Insertion/Nom/Appliquer. Dans la zone "Attribuer un nom", sélectionnez le ou les noms à attribuer.

10. Exercice Réalisez le tableau suivant en définissant des noms pour les cellules et en utilisant ces noms dans les calculs

11. Fonctions statistiques conditionnelles

12. Fonction SOMME.SI() Cette fonction permet d'additionner des cellules qui répondent à un certain critère. Dans l'exemple suivant nous voulons faire la somme des factures de chaque personne Nommer les plages : "client" et "montant Application Définir les noms : A2:A9  Client C2:C9  Montant Ecrire la formule dans F2 : =SOMME.SI(client;"Pierrot";montant) Ou mieux =SOMME.SI(client;E2;montant) Recopiez en dessous et modifier éventuellement les formules SOMME.SI(client;"Jacquet";montant) SOMME.SI(client;"Marlin";montant)

13. NB.SI Pour compter le nombre de facture on utilisera la fonction NB.SI qui détermine le nombre de cellules non vides répondant à un critère à l'intérieur d'une plage Application : Compléter le tableau précédent =NB.SI(client;"pierrot") ou mieux NB.SI(client;E2) =NB.SI(client;"jacquet") ou mieux NB.SI(client;E3) =NB.SI(client;"marlin") ou mieux NB.SI(client;E4)

14. Exercice • La société EXPEDIFRUIT livre en fruits plusieurs pays d'europe. • Construisez le tableau ci-dessous qui permet d'inscrire chaque livraison. Supposons par convention que ce tableau ne dépassera pas la ligne 100 • Posez les formules qui vont permettre de connaître le total des expéditions pour le pays saisi dans la cellule grisée

15. Exercice La société JOUFFLU SA emploie trois personnes pour assurer la permanence à l'accueil de l'entreprise. Afin de comptabiliser le nombre de permanences pour chaque personne, créez le tableau suivant qui permettra de prendre en compte 52 semaines Comptez les jours de permanence pour chaque personne

16. La mise en forme conditionnelle Nous allons faire en sorte que les cellules apparaissent dans les formats suivants : Si le nombre est inférieur à 250 : police rouge sur fond rose Si le nombre est inférieur à 500 : police Orange sur fond jaune Si le nombre est inférieur à 750 : police verte sur fond vert clair

17. Sélectionnez la plage B4:G9 Format/Mise en forme conditionnelle… Remplissez la première condition Cliquez ensuite sur pour préparer le format de police et de fond grâce aux onglets Puis après avoir cliqué sur Ok, appuyez sur pour préparer la 2ème condition comme vous l'avez fait pour la première Répétez les mêmes opérations pour la 3ème La boîte de dialogue est limitée à 3 conditions

18. Le résultat : Changez certains nombres et constatez le changement de mise en forme

19. Construire LE PLANNING d'un cabinet d'infirmières, la saisie du mois dans la cellule A4 devra entraîner la mise à jour automatique des colonnes A et B (dates) Saisissez quelques codes : R pour repos, T pour travail et TF pour travail un jour férié ou un week-end Placer les formules dans le tableau de droite pour comptabiliser pour chaque personne le nombre de jour de travail dans chaque catégorie.

20. LES FONCTIONS LOGIQUES

21. Fonction SI() La fonction SI permet d'exécuter un test conditionnel sur des valeurs et des formules Syntaxe: SI(test_logique; valeur_si_vrai; valeur_si_faux) test_logique : est toute expression dont le résultat peut être VRAI ou FAUX. valeur_si_vrai : est la valeur qui est renvoyée si le test logique est VRAI. valeur_si_faux : est la valeur qui est renvoyée si le test logique est FAUX. Exemple =SI(Note>=12;"Réussite";"Echec") Recopie Nommer la plage : "Note"

22. Exercice On fixe un taux de commission de 5% des ventes pour les vendeurs et de 8% pour les représentants. Placer la bonne formule dans la colonne commission

23. Bon de commande Remplissez le bon de commande suivant sachant que : La quantité livrée ne peut excéder la quantité disponible Le reste à livrer ne doit être affiché que si une commande n'a pas été honorée en totalité

24. Calcul de salaire AIDE POUR LES CALCULS La maladie se calcule sur le salaire brut La vieillesse : si le brut est supérieur au plafond on calcule sur le plafond et dans le cas contraire la cotisation se calcule sur le brut Retraite et Assedic tranche A se calculent sur le salaire brut Assedic tranche B se calcule sur la différence entre le brut et le plafond - il n'y a pas de cotisation si le salaire brut est inférieur au plafond Le reste est sans difficulté

25. Calcul de prime • Calculez le montant de la prime : • Si les ventes sont supérieures au quota, la prime est égale à 50 € par unité vendue au-delà du quota • Sinon la prime est nulle • Calculez les totaux, la prime minimale et la prime maximale • Calculez le nombre de pièces vendues par département

26. Concevoir une feuille de calcul pour enregistrer les ventes de l'année Mettre en place les formules de calcul des commissions, sachant que le taux de commission varie selon que l'objectif mensuel est atteint ou pas. Travailler avec noms pour les cellules

27. Imbrication des fonctions SI Il est possible d'imbriquer jusqu'à sept fonctions SI comme arguments valeur_si_vrai et valeur_si_faux pour élaborer des tests plus complexes. Exemple : Les commerciaux de l'entreprise X n'ont pas de commission pour des ventes mensuelles < 20 000 F en revanche ils ont 5% à partir de cette somme, voire 8% à partir de 40 000 F. Poser ce calcul. La formule :=SI(ventes<20000;0;SI(ventes<40000;5%*ventes;8%*ventes)) recopiez

28. Fonction ET() Dans le cas ou plusieurs conditions doivent être réunies on utilisera la fonction ET qui renvoie VRAI si tous les arguments sont VRAI; et FAUX si au moins l'un des arguments est FAUX. Syntaxe : ET(valeur_logique1; valeur_logique2; ...) Exemple : Les commerciaux de l'entreprise X reçoivent un magnum de champagne en cadeau si les ventes des trois derniers mois sont supérieures ou égales à 3000€ Nommez les colonnes : Oct, Nov et Déc Dans un premier temps écrire cette formule dans E2 ET(Oct>=30000;Nov>=30000;Déc>=30000) Recopiez vers le bas Vous constatez que le résultat est VRAI ou FAUX selon que les trois mois sont >=3000 ou pas. Complétez la formule par une interprétation du VRAI ou du FAUX =SI(ET(Oct>=30000;Nov>=30000;Déc>=30000);"Oui";"Non")

29. Fonction OU() Dans le cas ou au moins une condition doivent être vrai parmi plusieurs on utilisera la fonction OU qui renvoie VRAI si un argument est VRAI et FAUX si tous les arguments sont FAUX. Syntaxe : OU(valeur_logique1, valeur_logique2, ...) Exemple : Placer "contentieux" dans la colonne action pour les clients dont le dû est supérieur à 5 000 € ou dont le retard est supérieur à 60 jours La formule sera : =SI(OU(Dette>=5000;Retard>60);"contentieux";"")

30. 1) Calculez le retard par rapport à l'échéance 2) Calculez la somme restant due sachant que certains avaient effectué un apport initial 3) Calculez les frais de dossier soit 15 € pour les clients de code A et 20 € pour ceux de code B 4) Calculez les intérêts sur les sommes restant dues en appliquant le taux journalier prévu 5) Evaluez le total de la dette 6) Dans la colonne "à engager" vous noterez "Contentieux" pour les clients de code B dont le retard est supérieur à 70 jours, et "Relance" pour les autres 7) Dans la colonne "suivi" vous noterez "arrêt des ventes" pour les clients dont la dette et supérieure à 5000 € et pour ceux dont le retard est supérieur à 70 jours, vous noterez "Paiement comptant" pour les autres