Exclusion de nuls variables d'1 formule sur série de données

Bonjour,

J'ai un peu regardé les différents sujets mais impossible de savoir si ma question a déjà été postée...navrée si c'est le cas.

Mon problème est un peu compliqué à expliquer, surtout pour moi qui n'y connais rien...pour dire, j'ai déjà eu du mal à lui trouver 1 titre ! j'espère arriver à me faire comprendre

En gros, je cherche à calculer une diversité spécifique dont la formule est :

H=somme[(ni/N)*ln(ni/N)], avec "ni" le nombre d'individus d'une espèce i, et "N" le nombre total d'individus toutes espèces confondues.

Déjà, la formule est interminable à taper pour la série

Peut-être existe-t-il une façon de faire plus rapide que de re-sélectionner à chaque fois la case pour le terme suivant de la somme? Par exemple, 1 truc permettant de taper la formule telle quelle (H=somme[(ni/N)*ln(ni/N)]) et de mettre au lieu de "ni" une case indéterminée allant de la première à la dernière de la série, de façon à ce que ma formule reste toujours juste ? Que le logiciel fasse ce que j'ai fait en sélectionnant mes termes case par case mais tout seul quoi...C'est-à-dire que je ne veux pas calculer "=somme([(C3:C18)/C19]*ln[(C3:C18)/C19]", pour moi ça ne veut pas du tout dire la même chose. Je ne sais pas si vous me suivez...

Bref, ce n'est pas le plus gênant.

Le plus gênant, c'est que certaines de mes données sont nulles, or comme chacun sait, ln0 est incalculable...Il faudrait donc que je puisse exclure de ma formule les éléments nuls.

S'il ne s'agissait que d'une seule série ça irait, il me suffirait de ne pas sélectionner les cases nulles et basta. Mais comme c'est une formule compliquée, à appliquer à plusieurs séries, j'aimerais pouvoir faire 1 glissé de ma formule d'une série à l'autre...mais du coup, comme mes données nulles changent de place entre 2 séries, je ne sais pas comment les intégrer.

Je ne sais plus quoi faire...l'exemple que je vous donne est une toute petite partie de mon jeu de données, honnêtement ça me prendrait des jours pour tout calculer "à la main" (case par case et série par série quoi), et je n'ai pas ce temps là.

Voilà, soyez gentils de m'aider, si vous le pouvez

Honnêtement, si vous arrivez à me sortir de cette impasse, je vous en serai éternellement reconnaissante ! vous me direz, ça vous fera 1 belle jambe...c'est sûr, mais je peux pas faire grand chose de plus

Edit : J'ai oublié de dire, chez moi je travaille sous excel 2007, mais mon poste au bureau n'a que Excel 97, et encore pour l'instant, je suis coincée avec OpenOfficeCalc....conclusion : si vous avez la solution à mon (mes) problème, ce sera super, et la version du logiciel n'a pas tellement d'importance. Cela dit, si vous avez 1 solution pour chaque version...ce serait absolument fantastique ^^ (Je demande beaucoup, je sais )

Salut le forum

Un fichier exemple sera plus facile à travailler qu'une image.

Bon chance, car pas le temps de me taper l'écriture d'une image dans Excel

Mytå

Bonsoir Nobu,

comme le dit Myta, un fichier sera plus pratique parce que pour ta demande, le plus simple serait de faire un macro de remplissage qui fera tout les tests pour toi; mais il serait plus simple pour te faire quelque chose de cohérent d'avoir l'architecture de tes données et de ce que tu attends en sortie.

Pour joindre un fichier 2 possibilités : http://www.cjoint.com s'il est gros, et s'il fait moins de 200Ko (je crois) tu peux directement le faire depuis une réponse sur le sujet, il y a un onglet juste en dessous de la fenêtre de rédaction!

Ok pardon, je ne savais pas ce qui vous arrangeait le mieux. Je vous le joins donc ici :

Désolée, il fait plus de 200ko, il y a toute ma base de données avec...ne sachant pas de quoi vous avez besoin pour me répondre, je préfère tout mettre.

feuille 1 = base de données

feuille 2 = TCD qui me permettra de déterminer les valeurs pour remplir la feuille 3

feuille 3 = les tableaux à remplir qui me posent problème.

Voilà, j'espère que ça ira avec ça...

En tout cas, merci pour la rapidité de vos réponses ^^

Ok alors, j'ai essayé ça comme code, ça à l'air de marcher, faut un peu l'adapter pour améliorer le remplissage pour toutes tes séries; tu peux envisager une collection ou autre chose, ça dépend un peu de la tête de ta feuille complète mais avec ma solution ça peut être facile si ça reste de cette forme là.

Sub FormuleQiLnQi()
Dim resu As Double

'Nombre de répétition vers la droite (pour les éléments prospectés)
For k = 0 To 4

    'Largeur des blocs dans lesquels on met la formule (nombre de cycles de prospection)
    For j = 0 To 3
    'Réinitialisation du résultat (utile par colonne)
    resu = 0

        'Défilement des lignes
        For i = 3 To 18

            'Test
            If Sheets("Feuil3").Cells(i, j + 3 + 6 * k) <= 0 Then
            'Ici et pour la suite, le 6 c'est la largeur d'un élément prospecté, donc 6 * k c'est pour décaler le calcul
            Else
            'Calcul et addition de monôme au total
            resu = resu + (Sheets("Feuil3").Cells(i, j + 3 + 6 * k) / Sheets("Feuil3").Cells(19, j + 3 + 6 * k)) * Log(Sheets("Feuil3").Cells(i, j + 3 + 6 * k) / Sheets("Feuil3").Cells(19, j + 3 + 6 * k))
            End If

        Next i

        'Inscription du résultat dans la cellule
        Sheets("Feuil3").Cells(20, j + 3 + 6 * k) = resu

    Next j

Next k

End Sub

Il te sortira peut-être des bugs si tes données ne sont pas rentrées (ici pour "Ai" dans le 2eme élément, il te "incompatibilité de type" parce qu'il ne peut pas faire de calculs avec)

Voilà, bon courage!

Wow, super, ça marche !

Bon alors, comme je m'y attendais, c'est pas simple. Je suis une novice de 1re en macros, alors je comprends pas bien tout ce que tu m'as mis là...J'aimerais bien essayer de comprendre quand même, si jamais je dois adapter le code ou si j'ai 1 bug dont je ne connais pas l'origine, et simplement par curiosité.

Parmi les points qui me sont obscurs :

• - pourquoi y a-t-il des parenthèses après l'énoncé de la formule QilnQi ?
  - que veut dire "Dim...as double"?
  - je saisis mal à quoi correspond j... si c'est sensé être mon nombre de cycles de prospections, pourquoi avoir mis "For j=0 to 3" et pas "For j = 0 to 4" ?
  - qu'est-ce que c'est
  "Réinitialisation du résultat (utile par colonne)
  resu=0"?
  - "Cells(i,j+3+6*k)" : "i,j" compris", "+6*k" compris, mais "+3" au milieu pas compris...
  -je suis pas sûre de saisir l'enchaînement de termes
  "If...then
  Else...
  End if"
  En gros c'est pour dire que si la condition "la donnée est nulle" (If...then) alors dans les résultats de la formule (Else), ils ne sont pas pris en compte (End if)? C'est ça ?
  - pour le reste je crois avoir plus ou moins saisi le truc.

Je pose des questions dont les réponses vous paraissent peut-être toutes cons, mais j'essaye juste de comprendre comment ça marche (même si je sais bien que je m'improviserai pas "conceptrice de macro" en 2h )

En tout cas, un très grand merci pour m'avoir aidée, c'est exactement ce que je cherchais comme réponse. Comme promis tu as ma reconnaissance éternelle

Lol

Bonne journée !

Alors, je t'ai mis quelques commentaires dans le code pour expliquer un peu la fonction de chaque indice.

Sinon les parenthèses, j'en sais rien mais c'est la syntaxe d'une macro; et tu peux mettre le nom que tu veux (pas d'espaces, caractères spéciaux, et autres trucs bizarres avant les parenthèses)

Le Dim as Double c'est pour dire que le résultat est codé sur un double digit, donc avec plus de précision, après si tu veux changer le format d'affichage des résultats tu rajoute ça

Sheets("Feuil3").Cells(20, j + 3 + 6 * k).NumberFormat = ton format perso

entre next i et next j

j c'est l'indice de balayage des colonnes pour les différents cycles de prospections, donc de 0 à 3, ça en fait 4 et de 0 à 4, ça fait 5; donc trop si je ne m'abuse...

Après chaque calcul par colonne, (next j -> qui te ramène au for j ...), la valeur de resu est celle affichée dans la case en bas; donc pour la colonne suivante, on remet cette valeur à 0 avant de recommencer le test et les calculs.

Dans Cells(i,j+3+6*k), le 3 sert à commencer sur la bonne colonne, regarde avec les indices initiaux de chaque boucle quelle colonne ça désigne.

Dans le If, ici, si la valeur est négative ou nulle, on ne fait rien et on passe à next i et donc si elle est strictement positive (Else) on rajoute la valeur de ce terme au résultat. Le end if sert juste à clôturer (obligatoire)

Voilà, si jamais tu galères, repasse ici!

Merci beaucoup ! J'y vois 1 peu plus clair maintenant.

Je ne sais pas si ça suffira pour que j'arrive à résoudre un problème s'il y en a, mais je n'hésiterai pas à revenir demander de l'aide ici si ça arrive ^^

Salut le forum

Tu peux aussi utiliser une fonction personnalisée, plus souple pour le travail.

Public Function Diversite(Plage As Range)
Dim Nbre As Integer
Dim Total As Double
Dim Cellule As Range

Application.Volatile

Nbre = Application.WorksheetFunction.Sum(Plage)
Total = 0

For Each Cellule In Plage
  With Cellule
    If .Value > 0 Then
        Total = Total + (.Value / Nbre) * Log(.Value / Nbre)
    End If
  End With
Next Cellule

Diversite = Total

End Function

Pour s'en servir il suffit de saisir la plage de cellule a traiter dans la fonction

=Diversite(C3:C18)

Mytå

Cool ça Myta, je savais pas faire ça!

Bah ça va me servir tout de suite!

Merci!

Salut Myta, ta formule à l'air pas mal, le problème c'est que je n'arrive pas la tester, vu que je n'arrive même pas à la faire fonctionner...

Je m'explique : quand je fais un copié collé de ton code tel quel dans une nouvelle macro de l'éditeur de macro, puis que je lui demande d'exécuter, il m'ouvre une boîte de dialogue en me demandant de choisir une macro, mais dans laquelle ne figure pas celle que je viens de créer...

Du coup j'ai essayé en rajoutant Sub/End Sub comme dans le code de Reuk, seulement quand je colle le code entre ces 2 bornes, il transforme automatiquement le "End Sub" par "End Function", alors forcément quand j'essaye d'exécuter il me dit "Erreur de compilation - End Sub attendu"

Je change donc à nouveau ce "End Function" indésirable par "End Sub", mais voilà, quand je lui re-redemande d'exécuter, il me redit "Erreur de compilation - End Sub attendu"

Alors je me demandais où je me trompais de manipulation....quand j'ai fait le copié collé du code de Reuk dans une nouvelle macro il n'y a eu aucun problème.... Est-ce que tu pourrais m'expliquer pas à pas, comme à quelqu'un qui n'as jamais touché à excel (c'est pas mon cas, mais au moins je suis sûre de rater aucune étape ^^), quelle démarche tu suis depuis excel jusqu'à l'exécution de la macro ?

J'ai réussi à bidouiller avec le code de Reuk pour mes résultats, donc j'ai pas un besoin absolu que le tien marche, mais ça à tendance à m'agacer quand ça marche pas comme ça et que je comprends pas pourquoi, et comme je suis 1 tête de mule j'ai d'autant plus envie que ça marche J'espère que tu pourras m'aider

Re le forum

Mon code est un code de fonction.

Pour l'utiliser tu utlises les fonctions d'Excel (fx)

Et dans personnalisé tu trouveras Diversite

Dans ta feuille ça donne =Diversite(C3:C18)

Mytå

Ah d'accord, tu m'étonnes que ça marchait pas Effectivement, vu sous cet angle, c'est beaucoup plus efficace ^^ Désolée hein, comme tu l'auras compris, je suis pas une lumière en informatique

En tout cas merci, grâce à votre aide j'ai non seulement résolu mon problème, mais aussi découvert de nouveaux aspects d'excel !