Auteur: Christian
Herbé
Date: Mai 1999;
MAJ: avril 2010
Remarques et conventions:
Utilisez les liens hypertextes ci-dessus pour vous
déplacer
dans le document à partir d'un écran.
On utilise ici indifféremment le nom Excel ou
, XL phonétique Anglaise, pour désigner le tableur
Microsoft
ainsi que MS pour Microsoft.
Win9* ou W9* désignent Windows95 et 98. WNT ou
WinNT désigne Windows NT. W32 ou Win32 désignent les
systèmes
d'exploitation Windows en mode 32 Bits soit W9* et WNT.
Les mots en italiques indiquent
généralement
un mot clé du logiciel, une commande ou un bouton.
Chaque fois que l'on parle de variable, il s'agit d'un
ensemble de données contenues dans une seule colonne.
Plage et matrice désignent
généralement
un ensemble de cellules contiguës.
Les exemples suivant utilisent
toujours le point virgule comme séparateur d'arguments dans les
fonctions mais selon la configuration locale, vous pouvez être
amené
à utiliser la virgule.
CONSEILS
Je ne fais pas de liste de liens vers les sites consacrés
à
Excel; il est tellement plus simple de vous rediriger vers la FAQ
francophile !
Ce site contient, entre autres choses, tous les liens
indispensables
et un résumé des questions les plus fréquentes!
Le présent document n'a pas la prétention d'être
exhaustif. Par exemple, les aspects "finances", "comptabilité et
gestion" ne sont pas abordés par manque de compétences de
l'auteur dans ces domaines.Si vous "séchez" sur un
problème
Excel ou si vous vous voulez en apprendre plus, je vous conseille
vivement
de fréquenter assidûment le groupe de discussion: microsoft.public.fr.excel
accessible depuis tous les bons serveurs de "niouzes". Outre les
compétences
indéniables des contributeurs, vous y trouverez la meilleure
ambiance
qui soit sur un forum. Vous pouvez faire une recherche dans les discussions
archivées.
Le site de Daniel Josserand et le forum cité plus haut sont
en langue Française mais ce ne sont pas exclusivement des
serveurs
Français. Ils sont largement utilisés par toute la
communauté francophone. A titre d'exemple, les
Québécois
ne se sentent pas concernés par l'Euro; les Belges, les Suisses
et les Africains ne connaissent pas forcément les taux de la
T.V.A.
Française Etc. Veuillez en tenir compte dans vos interventions !
1: Formatage
conditionnel
des données
Pour mettre en couleur les valeurs inférieures à 10
et supérieures à 20 dans XL97:
2: Manipuler des
chaînes
de caractères
Séparer les Noms et Prénoms contenus dans une seule
cellule
(Supposons que A1 contient la chaîne: Jean-Philippe RAMEAU )
Les fonctions DROITE() et GAUCHE() extraient un nombre de
caractères
définis par l'utilisateur en partant de la droite ou de la
gauche,
exemple:
=DROITE(A1;6) renvoie 6 caractères en partant de la droite soit
la chaîne "RAMEAU".
La fonction NBCAR() compte le nombre de caractères d'une
chaîne,
exemple: =NBCAR(A1) renvoie 20 (attention aux espaces qui comptent
aussi
pour 1 caractère).
La fonction CHERCHE() recherche un caractère à
l'intérieur
d'une chaîne et renvoie sa position, exemple: =CHERCHE(" ";A1)
renvoie
14 parce que l'espace est en quatorzième position en partant de
la gauche. Si l'on doit séparer les noms et prénoms sur
quelques
cellules, les fonctions DROITE() et GAUCHE() suffisent amplement; en
revanche,
si le travail porte sur un grand nombre de cellules, il faut
écrire
une fonction plus élaborée.
En combinant les fonctions de texte precitées, on peut extraire
les mots situés de part et d'autres d'un caractère:
=DROITE(A1;NBCAR(A1)-CHERCHE(" ";A1)) affiche RAMEAU
=GAUCHE(A1;CHERCHE(" ";A1)) affiche Jean-Philippe + un espace
=GAUCHE(A1;CHERCHE(" ";A1)-1) affiche Jean-Philippe sans espace
3: Rendre les
données
plus explicites et plus accessibles
Il est parfois difficile de rendre compte des diverses informations
contenues dans un document. Excel offre plusieurs possibilités
visant
à faciliter l'accès à une série de
données
ou à les décrire.
Commenter une cellule (peu pratique dans la
version
5):
Il est souvent difficile de choisir un nom pour une variable. On
hésite
entre un nom court qui occupe peu d'espace mais qui décrit mal
le
contenu de la variable et un nom long qui va élargir
démesurément
le tableau. Dans ce cas, il est préférable de choisir un
nom court et d'insérer un commentaire sur la cellule contenant
un
paragraphe descriptif. Ex:
Nommer une cellule ou une plage:
Utiliser les liens hypertextes
L'apparition des documents multifeuilles depuis la version 5 est un
progrès important dans l'évolution du tableur. Cependant,
l'utilisateur rencontre les mêmes difficultés pour nommer
les feuilles que pour nommer les colonnes. Si les noms sont trop longs,
certains onglets sont masqués. On peut cependant les afficher
tous
par un clic-droit sur les flèches noires en bas à gauche
de l'écran. Lorsqu'un document est complexe, il est
préférable
de créer une feuille décrivant le document en
détails
avec des renvois ou liens hypertextes vers les feuilles ou zone de
cellules
(ils fonctionnent comme les signets dans les traitements de texte).
Pour créer un lien hypertexte:
4: Excel et les bases de
données
1er exemple: Considérons que la feuille active
du document XL est une base données. La solution la plus
élémentaire
pour faire une requête sur une feuille est d'utiliser le filtre
automatique.
2ème exemple: ce type de requête consiste à utiliser un filtre élaboré. Supposons que votre fichier est un extrait du code postal Français. La 1ère colonne contient les noms de régions, la 2ème colonne contient les numéros de départements et la 3ème contient les noms de communes
Ou bien:
D1
E1
F1
régions
départements
communes
=54
=<88
<>56
Le critère "<>56" exclu le département 56.
Ces critères vont filtrer les départements lorrains .
Le filtre élaboré est utile au delà de 2
critères
par variable.
Ces outils sont assez rudimentaires. Il est possible de faire
des requêtes plus complexes à l'aide de MSQuery, un
logiciel
connexe fournit avec Excel. L'installation par défaut de
MSoffice
ne met pas en place de raccourci vers Query. Recherchez le fichier
query
dans le dossier \program Files\Office ou regardez si données
externes est disponible dans le menu Données.
Si les options nécessaires n'ont pas été
cochées
pendant l'installation, MSquery n'est pas installé! Dans ce cas,
vous devez faire une réinstallation partielle d'Office et cocher
Accès
aux bases de données .
Si l'utilisation de MSQUERY vous pose problème, voyez cette page détaillée.
Quelques éléments sur MSquery
Avant de faire une requête sur un fichier Excel depuis Query,
vous devez nommer la zone de données à interroger.
Autre approche de la gestion de données depuis Excel: les
fonctions de recherche
Les exemples de requêtes que nous venons de voir ont l'avantage
d'être relativement facile à mettre en place ou à
utiliser
mais ils sont assez lents.
Excel contient des fonctions de recherches plus compliquées
dans leur utilisation mais infiniment plus rapides. Nous allons nous
limiter
à l'apprentissage de quatre d'entre elles.
INDEX(vecteur;position) :
Renvoie une valeur en fonction de son index ou de sa position dans
le vecteur (1 ligne ou 1 colonne):
=INDEX(B2:B4;2) affiche "Rameau" parce que c'est la 2eme cellule du
vecteur.
| A | B | |
| 1 | Prénom | Nom |
| 2 | César | Franck |
| 3 | Jean-Philippe | Rameau |
| 4 | Frédéric | Choppin |
EQUIV(valeur_cherchée;vecteur_à_explorer;type)
=EQUIV("Rameau";B2:B4;0) renvoie 2 parce que Rameau est en 2ème
position dans le vecteur de recherche
Si type = -1, la fonction recherche une valeur approchée
inférieure;
si type = 0; la fonction recherche la valeur exacte; si type = 1, la
fonction
recherche une valeur approchée supérieure. EQUIV() est
donc
l'inverse de INDEX()
RECHERCHEV(valeur_à_chercher;matrice;n°de_la_colonne_à_afficher;valeur_proche)
RECHERCHEH(valeur_à_chercher;matrice;n°de_la_cellule_à_afficher;valeur_proche)
=RECHERCHEV("Rameau";A2:B4;1;0) renvoie la chaîne
"Jean-Philippe"
valeur_à_chercher peut être une chaîne
écrite
entre guillemets anglais (") , une valeur numérique (attention
aux
valeurs arrondies!) ou une référence à une
cellule.
matrice peut être une série de valeurs
(chaîne
ou nombre) ou une référence à une plage de
cellules
.
numéro de colonne ou numéro de cellule
est l'index de la matrice à afficher.
valeur proche (1 ou 0; VRAI ou FAUX) Si égale à
1 , la fonction renvoie une valeur approchée, si =0,
recherche
la valeur exact et renvoie #NA si elle ne la trouve pas.
ATTENTION: ces fonctions sont insensibles
à la casse (majuscule/minuscule) mais sensibles à tous
les
caractères visibles ou non (cas du caractère espace). En
cas de doute sur la présence d'un espace à la fin d'une
cellule,
sélectionnez la et appuyez sur la touche F2. Si le curseur n'est
pas "collé" au dernier caractère, vous êtes en
présence
d'un espace !
Exemple de base de données multitables avec Excel
Imaginez une feuille contenant la liste des communes françaises,
imaginez une autre feuille contenant la liste des départements,
imaginez une 3eme feuille contenant la liste des régions (il est
préférable d'éclater le document en 3 feuilles
parce
qu'il y a 3 types principaux d'informations).
Extrait de la feuille régions
| A | B | |
| 1 | n° région | région |
| 2 | 13 | Limousin |
| 3 | 14 | Lorraine |
| 4 | 15 | Midi Pyrénées |
Extrait de la feuille départements
| A | B | C | |
| 1 | département | Nom | n° région |
| 2 | 54 | Meurthe et Moselle | 14 |
| 3 | 55 | Meuse | 14 |
| 4 | 56 | Morbihan | j'ai oublié ! |
| 5 | 57 | Moselle | 14 |
| A | B | |
| 1 | Nom | code postal |
| 2 | Champenoux | 54280 |
| 3 | Essey La Cote | 54830 |
| 4 | Damas aux Bois | 88330 |
| 5 | Pagny La Blanche Côte | 55140 |
| A | B | C | D | |
| 1 | commune à rechercher | région | département | code postal |
| 2 | Essey la Côte |
En D2, entrez la fonction suivante: =recherchev(val_cherchee;commune;2;0)
La valeur 2 indique que la fonction doit afficher une valeur de la
2ème colonne de la plage explorée.
La valeur 0 indique que la fonction doit afficher la valeur exacte.
En C2, entrez les fonctions suivantes: =gauche(recherchev(val_cherchee;commune;2;0);2)
La fonction recherchev() recherche la chaîne écrite en
A2 et renvoie le code postal (54830). La fonction gauche() extrait les
deux caractères de gauche et affiche 54.
En B2, entrez la fonction suivante: =recherchev(C2;region;2;0)
La fonction recherchev() cherche la valeur contenue en C2
à
l'intérieur dans la plage nommée région et affiche
le contenu de la cellule contiguë.
L'écriture de ces fonctions peut paraître
rébarbatif
à un utilisateur peu familiarisé à
l'emboîtement
de fonctions et à leur utilisation en cascade mais avec de
l'entraînement,
on arrive très vite à des résultats
stupéfiants
! Pour des raisons de mode d'accès aux enregistrements, ce type
de requête à l'aide d'un tableur est beaucoup plus rapide
en temps d'exécution que sous n'importe quel logiciel de base de
données.
A partir de là, il suffit d'écrire un nom de commune
en A2 pour obtenir son code postal ainsi que sa région.
LA FONCTION SOMMEPROD
5: Fonctions de test et
fonctions
logiques
Elles servent à afficher une valeur dans une cellule en
fonction
du contenu d'une ou plusieurs autres valeurs.
=si(test;à_executer_si_vrai;à_executer_si_faux)
; exemple avec des codes postaux:
A B C
|
|
|
|
|
| 2 |
|
|
|
|
|
|
|
|
| 4 |
|
|
=si(et(B4>=88000;<89000);"Vosges";"hors Vosges")
affiche Vosges
parce que la cellule B4 contient une valeur comprise entre 88000 et
89000.
Le test est fait par la fonction logique et()
qui dans ce cas renvoie vrai. Ceci est un 1er exemple
d'emboîtement
de fonctions. On peut faire jusqu'à 30 tests à
l'intérieur
des fonctions ou() et et() . On peut aussi
emboîter
jusqu'à 7 fonctions si(). L'emboîtement de fonctions
suivantes
permet de vérifier si un code postal est issu de la
région
Lorraine:
=si(et(B4>=88000;B4<=88999)"Vosges";si(et(B4>=54000;B4<55000);"M-et-Moselle";si(et(B4>=55000;B4<56000);"Meuse";si(et(B4=57000;B4<58000;"Moselle";"Pas_en_Lorraine"))))
Le 1er emboîtement, si(et(B4=88000;B4<89000)"Vosges";
vérifie si la valeur corresponds à un code postal
Vosgien,
si oui, la fonction affiche "Vosges" et s'arrête là. Si la
valeur n'est pas comprise entre 88000 et 88999 inclus, un 2eme test est
réalisé en recherchant une valeur entre 54000 et 54999
inclus
et ainsi de suite. Si la valeur ne correspond à aucun code
Lorrain,
le test renvoie FAUX ou 0 et affiche "Pas en Lorraine".
Les fonctions logiques ont énormément d'applications.
Vous pourrez les utiliser pour rechercher des valeurs suspectes ou
erronées.
Supposons que vous soyez chargé d'entrer dans un tableau des
notes
scolaires comprises entre zéro et vingt. Une faute de frappe
étant
toujours possible, un test va vous permettre de repérer
facilement
les valeurs inférieures à zéro ou
supérieures
à 20. Vous avez entré les notes dans la plage A2:A100.
Dans
la cellule B2, entrez ce test:
=si(ou(A2<0;A2>20);1;0)
Si A2 contient une valeur en dehors des bornes, B2 affiche1, sinon
B2 affiche 0 . Recopiez le contenu de B2 jusqu'à B100.
En B1 faites la somme de B2:B100 et vous obtiendrez le nombre
d'erreurs.
Parcourez la plage A2:A100 et corrigez les erreurs jusqu'à
obtenir
une somme de B2:B100 égale à zéro. Nous verrons
dans
les formulaires comment interdire
l'entrée
de valeurs "hors normes".
Vous pouvez aussi faire un test en comparant plusieurs cellules
même
si celles-ci contiennent des données de nature
différentes.
Construisons notre tableau de notes autrement:
A
B
C
D
E
F G
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| 3 | Adam |
|
|
|
|
somme.si(plage;condition)
Cette fonction additionne les valeurs qui correspondent à des
critères
défini par l'utilisateur ex:
=somme.si(4,5,9,11,12;=10) affiche 23, 4,5,9
ne sont pas pris en compte parce qu'ils ne répondents pas au
critère.
nb.si(plage;condition)
compte le nombre de cellules correspondant à "critères"
ex;
=nb.si(4,5,9,11,12;=10) affiche 2 parce qu'il y a deux valeurs
qui répondent aux critères.
6: Le solveur
(Voir aussi: résoudre un système
d'équation à l'aide des fonctions matricielles).
Cet outil ne pose pas de problème particulier dans son
utilisation.
Il permet la résolution de plusieurs types d'équations
avec
une ou plusieurs contraintes. La difficulté est de
définir
clairement le problème à résoudre. Nous allons le
faire pour deux exemples.
Exemple 1:
Vous êtes propriétaire d'une grande pelouse. Pour en
assurer
l'entretien, vous vous demandez si, à long terme, il est
plus
économique de louer une tondeuse pour 3000F/an ou si il vaut
mieux
l'acheter pour 10000F auquels il faut ajouter des frais financiers
annuels
à raison de 3% plus 500F/an d'entretien. Le tableau se
construit
comme ceci:
| Achat | 10000 |
| Frais Financiers annuels (3%) 10000/100*3 | 300 |
| Entretien annuel | 500 |
| coût cumulé de l'achat: 10000+(année*(300+500)) | 10800 |
| Location: 3000*année | 3000 |
| Année | 1 |
Allez dans le menu Outils puis Solveur. Voici la
boîte
de dialogue:
- La cellule à définir est B6.
- La cellule variable est également B6
- La contrainte est l'égalité des cellules B4 et B5
Le solveur propose une solution après 4.5 années. On peut vérifier cette égalité:
Location*années = achat + (années*(300 + 500))
En simplifiant: année*(3000-800) = 10000
années = 10000 / 2200
années = 4.545
Ce qui confirme la solution du solveur. L'achat de la tondeuse ne sera
économique qu'à partir de la cinquième
année.
Exemple 2:
Un éleveur doit compléter l'alimentation de ses vaches
laitières pour couvrir les besoins de production au delà
des possibilités de la ration de base.
Un minotier lui propose un aliment concentré à
1,40F/Kg.L'éleveur
se demande si il ne serait pas plus économique de fabriquer lui
même le concentré en achetant les intrants. Cet aliment
doit
contenir par Kg :
- 1 UFL (énergie)
- 50 g de PDIN (protéines)
- 50 g de PDIE (protéines)
(afin de ne pas alourdir l'exemple, on ne tient pas compte du
complément minéral)
Il dispose sur le marché des produits suivants
(données
réelles au 01/05/1999 fournies par le domaine de Mirecourt):
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Complétons le tableau précédent en modifiant la
colonne de matière sèche et en ajoutant 5 colonnes:
|
|
|
|
|
|
|
Qté | UFL | PDIN | PDIE | Prix |
|
|
|
|
|
|
|
0 | =G2*C2*B2 | =G2*D2*B2 | =G2*E2*B2 | =G2*F2 |
|
|
|
|
|
|
|
0 | =G3*C3*B3 | =G3*D3*B3 | =G3*E3*B3 | =G3*F3 |
|
|
|
|
|
|
|
0 | =G4*C4*B4 | =G4*D4*B4 | =G4*E4*B4 | =G4*F4 |
|
|
|
|
|
|
|
0 | =G5*C5*B5 | =G5*D5*B5 | =G5*E5*B5 | =G5*F5 |
|
|
|
|
|
|
|
0 | =G6*C6*B6 | =G6*D6*B6 | =G6*E6*B6 | =G6*F6 |
|
|
|
|
|
|
|
0 | =G7*C7*B7 | =G7*D7*B7 | =G7*E7*B7 | =G7*F7 |
| =somme(H2:H7) | =somme(I2:I7) | =somme(J2:J7) | =somme(K2:K7) |
Le solveur propose un mélange équilibré d'orge et de drêches à 0.89 soit une économie de (1,40-0.89)/1,40*100 = 36% auquel il faudrait ajouter la complémentation minérale.
Ce sont des feuilles avec une mise en page particulière et
des
outils destinés à simplifié le travail de
l'utilisateur
ou à contrôler la cohérence des entrées.
Contrôler les valeurs saisies:
Supposons que vous soyez chargé de créer un formulaire
destiné à la saisie de notes scolaires comprises entre 0
et 20. Vous voulez interdire les valeurs inférieures à
zéro
ou supérieures à 20.
Aide à la saisie:
Toujours avec le même exemple, supposons que vous vouliez
éviter
à l'utilisateur de taper les noms des élèves.
Écrivez
les dans l'une des feuilles du document et attribuez lui le nom
"élèves"
(vous pouvez ensuite masquer cette feuille depuis le menu Format
puis Feuille puis Masquer et la faire
réapparaître
ensuite depuis le menu Fenêtre puis Afficher)..
Sélectionnez les cellules devant recevoir les noms
d'élèves.
Depuis la boîte de dialogue Validation des données, dans
le champ Autoriser: choisissez liste. Dans le
champ
Source
tapez eleves. Chaque fois qu'une des cellules de la zone
élèves
sera sélectionnée, un bouton contenant une liste
déroulante
sera affichée et l'utilisateur n'aura qu'à
sélectionner
le nom qu'il veut. Cela vous permet aussi de contrôler
l'orthographe.
Étendre une zone de cellules contrôlée:
Si un contrôle vous satisfait et que vous voulez l'appliquer
à d'autres cellules, il suffit de copier la cellule initiale et
faire un collage spécial et cocher validation sur les
cellules
destinataires.
Autre présentation des données d'un tableau:
Allez dans le menu Données puis Grille. Vous
pouvez
utiliser cet outil pour visualiser autrement vos enregistrements.
L'ascenseur
vous permets des déplacements ligne à ligne.
Arrière plan de la feuille:
Vous pouvez encore personnaliser le formulaire en affichant une image
en "toile de fond": allez dans le menu Format puis Feuille
puis Arrière-plan et choisissez un fichier image.
Par synthèse, on entend "statistiques
récapitulatives".
Extrait du fichier de données utilisé pour les exemples
suivants (données tronquées):
Excel offre un outils très pratique, très souple et
très
puissant pour le calcul de statistiques élémentaires par
niveaux de facteur. Cet outil s'appelle un Tableau Croisé
Dynamique
que nous désignerons avec l'acronyme TCD ! Il est disponible
depuis
les menus:
- Données
- Rapport de tableaux croisés dynamiques
Ecran 1:
Cochez "liste ou base de données comme ci dessus et cliquez
sur
suivant.
écran 2:
Cliquez dans l'espace "Plage" et sélectionnez tout le tableau
de données (Ctrl *)
Cliquez sur le bouton "Suivant"
Ecran 3:
Vous remarquez dans la partie droite les boutons; ce sont les
entêtes
de colonnes du tableau sélectionné.
- cliquez et glissez le bouton "fournisseurs" dans la zone "Ligne"
- cliquez et glissez le bouton "Montant" dans la zone Données
- cliquez sur "Suivant"
Cochez "Nouvelle Feuille" pour créer un nouvel onglet
contenant
le TCD
Cliquez sur Fin
Excel vient de calculer le montant total des dépenses par
fournisseur.
L'exemple précédent était un tableau
récapitulatif
simple; on va maintenant réaliser un tableau à double
entrée.
Recommencez jusqu'à l'écran 3
Après l'écran 2, répondez oui l'écran
suivant:
Et cliquez sur le bouton suivant de cet écran:
Ecran 3B:
Placez les boutons "Budgets" et "Fournisseurs" dans la zone "Ligne.
Placez le bouton "Montant" dans la zone "Données".
Placez le bouton Emetteur" dans la zone "Colonne".
Cliquez sur "Fin"
Vous disposez d'un tableau complexe récapitulant les
dépenses
par Budget et par fournisseur (en ligne) et par émetteur (en
colonne).
Ce tableau est très riche en informations mais sa lecture est
déroutante. Vous pouvez garder toute l'information et simplifier
la présentation; dans la figure 3B, au lieu de mettre le bouton
Budget dans la zone LIGNE, placez le dans la zone "PAGE" comme ceci:
Et vous obtenez ceci (copie partielle):
Vous pouvez afficher les budgets un par un en les choisissant dans la liste sous la lettre de colonne B.
Excel97 ou 98 n'offre pas de fonction pour ce calcul;
toutefois,
la moyenne pondérée peut facilement être
calculée
à l'aide d'autres fonctions:
- entrez les valeurs à "moyenner" en colonne
A
- entrez les coefficients en colonne B
- depuis une autre cellule, utilisez les fonctions
SOMMEPROD (somme des produits) et NBVAL (nb de valeurs) comme dans
l'exemple
suivant.
La fonction SOMMEPROD multiplie chaque valeur de la colonne A par
chaque
valeur de la colonne B et additionne ces produits.
La fonction NBVAL compte les valeurs.
En divisant le résultat de SOMMEPROD par le résultat
de NBVAL, on obtient la moyenne pondérée!
Quelques fonctions
| coef de pente | ordonnée à l'origine |
| erreur standard de la pente | erreur standard de l'ordonnée à l'origine |
| Coefficient de corrélation | erreur type de Y estimé |
| statistique F | degrés de liberté |
| somme des carrés des écarts expliqué par le modèle | somme de carrés des écarts (SCE) résiduelle |
| coeff de pente attaché à X3 | coeff de pente attaché à X2 | coeff de pente attaché à X1 | ordonnée à l'origine |
| erreur standard de la pente X3 | erreur standard de la pente X2 | erreur standard de la pente X1 | erreur standard de l'ordonnée à l'origine |
| Coefficient de corrélation | erreur type de Y estimé | ||
| statistique F | degrés de liberté | ||
| SCE expliquée par le modèle | SCE résiduelle |
Régression curvilinéaire:
Dans les domaines biologiques, il est fréquent de rencontrer
des modèles de type Y=1/X+b ou Y=X0.5+b . Il suffit
de
calculer les nouvelles valeurs de X dans le tableau et
d'appliquer
ensuite la fonction droitereg() entre cette nouvelle variable X
et la variable dépendante (Y).
Modèle polynomiale:
Pour un polynôme d'ordre 3 (Y = a1X + a2X2
+a3 X3 + b) , il suffit de créer dans le
tableau
2 variables X supplémentaires en calculant X2
dans
l'une et X3 dans l'autre et appliquer la fonction droitereg()en
sélectionnant les 3 variables X dans la matrice_X .
Outils statistiques issus des macros complémentaires:
Pour les utiliser, il faut que les macros complémentaires
aient été installées. Si vous ne voyez pas de
ligne
"Utilitaires d'analyses" dans le menu Outils, allez dans
le sous menu Macros complémentaires puis cochez Utilitaires
d'analyses. Lorsque c'est fait, vous disposez des outils
statistiques
depuis le menu Outils puis utilitaires d'analyses. Voir
aussi
le calcul matriciel à la fin de cette
page.
Analyse de variance:
Excel est, et reste un tableur; par conséquent, certains outils
statistiques comme l'ANOVA (ANalysais Of VAriance) sont assez
limités.
L'analyse de variance ne peut être réalisée que sur
des données numériques y compris la/les variables
facteurs.
Si vous voulez tester un effet et si la variable "facteur" comprends
des
noms, vous devrez les remplacer par des nombres ce qui est assez
contraignant.
Autre limite: dans l'ANOVA à 2 facteurs, XL calcule les
degrés
de liberté à partir du nombre d'individus
rencontrés
dans dans le 1er niveau de chaque facteur ce qui vous oblige à
limiter
l'analyse à des plans parfaitement orthogonaux, dans les autres
cas, les résultats sont forcément erronés puisque
les degrés de liberté utilisés ne correspondent
pas
à la réalité.
Compte tenu de ces limitations, je n'approfondirai pas d'avantage cet
outil dont l'utilisation me semble trop réduit.
Analyse de corrélation:
Cliquez dans le champ Plage d'entrée puis
sélectionnez
les données à analyser. Je conseille fortement de
sélectionner
aussi les noms/titre de variables; dans ce cas, cochez le bouton Intitulés
en 1ère ligne. Si vos données sont
organisées
en colonnes, vérifiez que le bouton Colonne est
coché.
Choisissez la destination des résultats dans les options de
sortie.
Vous obtenez une matrice de corrélation.
Histogramme:
Dans plage d'entrée, sélectionnez les
données
à représenter. Utilisez plage des classes si
vous voulez contrôler les classes de l'histogramme (dans ce cas,
prenez soin de bien "encadrer" vos données). Cochez les options
de sorties en fonction de vos voeux.
Voir aussi la fonction statistique FREQUENCE().
Puisque l'ensemble des outils statistiques fonctionnent selon le
même
scénario, il est inutile d'aller plus loin dans leur
description!
(voir aussi: le solveur)
Il s'agit bien de calcul matriciel au sens mathématique du
terme.
Il n'y a pas de confusion avec le "renversement" d'une matrice de
données
qui consiste à transformer les lignes en colonnes et
inversement!
Dans ce cas, on utilise soit la fonction TRANSPOSE() ou
copier
puis collage spécial et transposer.
- Utiliser la fonction DETERMAT() pour obtenir le déterminant
d'une matrice.
- Utilisez la fonction INVERSEMAT() pour inverser une matrice.
Attention,
il s'agit d'une fonction renvoyant une matrice, vous devez donc valider
avec les touches CTRL+SHIFT+Entrée.
- Utilisez PRODUIMAT() pour calculer un produit de matrices (autre
fonction matricielle)
Exemple d'utilisation d'INVERSMAT et PRODUIMAT, résoudre ce système d'équations à trois inconnues:
2x + 23 y + 8z = 5
10x + 6 y + 90z = 6
4x + 7 y + 12z = 7
écrit sous forme matricielle:
entrez la fonction suivante dans une plage 3*3 :
=INVERSEMAT(matrice1)
et validez en appuyant simultanément sur les touches Ctrl
Shift Enter (ex dans les colonnes J K L ci
dessous)
(appelons cette matrice "matrice3")
sélectionnez une plage d'une colonne * 3 lignes (ex
colonne N) et entrez la fonction suivante:
=PRODUITMAT(matrice2;matrice3) validée
avec Ctrl Shift Enter
