Auteur: Christian Herbé
Date: Mai 1999;
MAJ: Octobre 2001
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.
Vous pouvez constater que la liste est longue et permet des requêtes
beaucoup plus élaborées que les filtres Excel.
La boîte suivante vous permet de choisir dans quel ordre afficher
les données et ce, variable par variable (un autre plus!). Vous
pouvez sauvegarder la requête afin de la réutiliser ultérieurement.
Même si vous ne sauvegardez pas cette requête, les interrogations
ultérieures sur ce fichier seront plus rapides puisque la base de
données est déclarées. Il suffira alors de cliquer
sur Fichier puis nouvelle et sélectionner la BDD pour accéder
à la boîte de dialogue Assistant Requête. L'utilisation
des boîtes de dialogues s'avère fastidieuse quand on doit
répéter les mêmes actions. Vous auriez sans doute intérêt
à apprendre les bases du langage SQL pour accélérer
vos requêtes exemple:
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.
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)
- Télécharger le fichier Eigen.xls contenant la
macro complémentaire SimmetricEigenvectors pour le calcul
des valeurs propres (Eigenvalues) et des vecteurs propres (Eigenvectors).
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
