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 C7D7) 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 C7C13 "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
D6G13 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 D14G16
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 A2A9 ?
Client C2C9 ? Montant Ecrire la formule dans
F2 SOMME.SI(client"Pierrot"montant) Ou
mieux SOMME.SI(clientE2montant) 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(clientE2) NB.SI(client"jacquet") ou
mieux NB.SI(clientE3) NB.SI(client"marlin") ou
mieux NB.SI(clientE4)
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 B4G9 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(Notegt12"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 lt 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(venteslt200000SI(venteslt400005
ventes8ventes)) 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(Octgt30000Novgt30000Décgt30000) Recopiez
vers le bas Vous constatez que le résultat est
VRAI ou FAUX selon que les trois mois sont gt3000
ou pas. Complétez la formule par une
interprétation du VRAI ou du FAUX SI(ET(Octgt3000
0Novgt30000Décgt30000)"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(Dettegt5000Retard
gt60)"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