Besoin d'aide pour calcul dans Excel Résolu/Fermé

Question

Bonjour,

Je me tourne vers vous car je ne sais plus trop comment faire ! 
Je voudrais calculer par exemple l'indice de diversité de Shannon pour 90 stations. Le problème, c'est que j'ai dans ma matrice Excel un nombre de lignes = au nombre d'espèces dans chacune des stations et donc ce nombre de lignes est variable. De plus, j'ai x lignes et j'ai un seul résultat pour l'indice de diversité donc comment procéder ? dois-je remanier ma matrice Excel ? car je souhaiterais par la suite pouvoir glisser la formule pour les 90 stations et ne pas tout faire à la main (à l'ordi !). De plus, il ne faut pas que je fusionne des cellules car comme je dois ensuite exporter cette matrice Excel sous le logiciel stat R, je ne crois pas qu'il accepte les cellules fusionnées. Je vous envoie un exemple de mes données. 

Merci beaucoup pour votre aide
Bien cordialement,
MA


Point	Espece	Effectif	pi	pilog2pi	pi²	Shannon
2490	BUTBUT	1	0,033333333	-0,16356302	0,001111111	
2490	CARCHL	1	0,033333333	-0,16356302	0,001111111	
2490	CORONE	4	0,133333333	-0,387585413	0,017777778	
2490	EMBCIT	3	0,1	-0,332192809	0,01	
2490	FRICOE	1	0,033333333	-0,16356302	0,001111111	
2490	HIPICT	1	0,033333333	-0,16356302	0,001111111	
2490	HIPPOL	1	0,033333333	-0,16356302	0,001111111	
2490	PARMAJ	3	0,1	-0,332192809	0,01	
2490	PHYCOL	1	0,033333333	-0,16356302	0,001111111	
2490	PRUMOD	1	0,033333333	-0,16356302	0,001111111	
2490	STUVUL	6	0,2	-0,464385619	0,04	
2490	SYLCOM	2	0,066666667	-0,260459373	0,004444444	
2490	TROTRO	1	0,033333333	-0,16356302	0,001111111	
2490	TURMER	3	0,1	-0,332192809	0,01	
2490	TURPHI	1	0,033333333	-0,16356302	0,001111111

michel_m · Answer

Bonjour,

Excuses moi, mais pour le cancre que je suis,l'indice de Shannon n'est il pas un titre de science-fiction?

Plus sérieusement,met le classeur sans données confidentielles en pièce jointe (format XL97-2003) sur
http://cijoint.fr/
et colle le lien proposé - y faire un clic droit puis copier l'adresse - dans le message de réponse

et explique clairement sans rien oublier ce que tu veux en pensant que tu t'adresses à des bidouilleurs informatiques non nobelisables
:o)

 Michel

MA · Answer

Bonjour,

Voici le lien : http://www.cijoint.fr/cjlink.php?file=cj201106/cijFb4QU3W.xls

Mon problème c'est que j'ai 90 points (ou stations) avec différentes espèces observées et je dois calculer un indice de diversité appelé Shannon. Jusque là rien de bien compliqué mais ce qui me pose problème c'est comment faire ça de manière "propre"sur Excel pour pouvoir ensuite exporter ma table Excel sous un logiciel de stat (R pour ceux qui connaissent). Donc, il ne faut pas, je suppose, que je fusionne de cellules... donc comment faire pour pouvoir également glisser la formule pour toutes les stations qui je rappel n'ont pas le même nombre d'espèces donc de lignes (d'où aussi une difficulté, je pense). 

MERCI 
MA

MA · Answer

Oui désolé mais j'ai du mal a expliquer mon souci. 

En fait, j'ai plusieurs calculs à faire avec ces données et on peut prendre un exemple très simple : le calcul de l'abondance total de chaque station. Abondance total = effectif total donc c'est tout simplement la somme des effectifs de chaque espèce observée dans une station. Mais, comme je le disais auparavant, pour glisser cette formule (Somme des effectifs) comment faire dans la mesure où j'ai plusieurs lignes (= nombre d'espèces) pour chaque station et que ce nombre de lignes est variable selon les stations et que je souhaite avoir un résultat de l'abondance dans une cellule ? comment faire ? dois-je remanier ma table excel pour cela ? 
La solution à cet exemple me permettrai de résoudre mon problème pour tous les autres calculs qui correspondent à 1 résultat par station donc 1 résultat dans une cellule !

J'espère être un peu plus clair
MERCI encore
MA

MA · Answer

Pour info : 

L'indice de Shannon est un indice permettant de mesurer la biodiversité. 

H' = - ? pi ln pi
 
H' : indice de biodiversité de Shannon
 i : une espèce du milieu d'étude
 pi : Proportion d'une espèce i par rapport au nombre total d'espèces (S) dans le milieu d'étude (ou richesse spécifique du milieu), qui se calcule de la façon suivante :

 p(i) = ni / N
 
où ni est le nombre d'individus pour l'espèce i et N est l'effectif total (les individus de toutes les espèces).
 
Il est possible de choisir arbitrairement la base du logarithme1 et on trouve donc souvent dans la littérature scientifique log ou log de base 2 à la place de ln.
 
Cet indice permet de quantifier l'hétérogénéité de la biodiversité d'un milieu d'étude2 et donc d'observer une évolution au cours du temps. Cet indice varie toujours de 0 à ln S (ou log S ou log2 S, selon le choix de la base du logarithme)
 
Cet indice est l'un des plus connus et des plus utilisés par les spécialistes.

MA · Answer

H' = - SOMME (pi ln pi)

Honoré · Answer

Bonsoir,

J'avais conçu un tableau du genre avec Excel. Tu peux le télécharger et t'en inspirer. Le lien: http://sites.google.com/site/hbiaou/downloads/FormuledecalculindicedediversitedeShanon.xls

Bonne chance
Honoré

Besoin d'aide pour calcul dans Excel

6 réponses

Discussions similaires