[Résolu][Calc] Gestion Troupeau - généalogie/consanguinité

[toitoinebzh]

Bonjour à tous,

Je vous présente un projet perso que je n'arrive pas à aboutir depuis bien longtemps.
Je tente une nouvelle tentative cette fois-ci sous Libreoffice Calc.

Le projet consiste à tenir une liste d'animaux (chien, chat, bovin, ovin, ...) avec leur parenté. L'objectif est d'aider les éleveurs dans le suivi de leur troupeau, notamment sur les croisements pour éviter les effets de consanguinité.

Le tableur se décompose au final de cette manière
1 > un premier onglet qui liste les différents individus
2 > un second onglet qui permet de voir la généalogie d'un individu (sur 3 générations)
3 > un troisième onglet qui permet de visualiser un croisement potentiel (à partir du choix du père et de la mère)
4 > un quatrième onglet qui permet de voir en un clin d’œil le risque de consanguinité d'un femelle avec un mâle, ce qui permet de choisir rapidement le meilleur croisement.

Pour l'instant, les onglets 1,2,3 fonctionnent plutôt bien. Je voudrais cependant apporter des améliorations à ce fichier.
1 > création de fonctions/macro pour éviter les formules à rallonges
2 > création d'une macro pour calculer la consanguinité d'un individu
3 > correction de l'onglet 4
4 > passage à des analyses sur 5 générations

j'ai d'autres idées d'améliorations mais si j'arrive à réaliser ces 4 points ce sera déjà pas mal

Je vous joins un fichier exemple pour voir de manière concrete le fonctionnement, je suis preneur de vos suggestions pour améliorer le tout

____________
edit : dernière version disponible ici https://framagit.org/anto1ne/genecalc

[toitoinebzh]

Première question

dans mes formules, j'utilise régulièrement des fonctions du type

Code : Tout sélectionner

=RECHERCHE(G7;$BDD.$C:$BDD.$C;$BDD.$A:$BDD.$A)

j'ai tenté de créer une macro à partir de différentes discussion sur ce forum et j'ai abouti à ce bout de code pour reproduire cette fonction recherche

Code : Tout sélectionner

Sub get_nom_id(id)

Dim Feuille1 as object

Vrech = Feuille1.getCellRangeByName("$BDD.$C:$BDD.$C")
Vsort = Feuille1.getCellRangeByName("$BDD.$A:$BDD.$A")

Args = Array(id,Vrech,Vsort )

oFuncAcces = CreateUnoService("com.sun.star.sheet.FunctionAccess") 'création du service d'accès aux fonctions

get_nom_id = oFuncAcces.callFunction("LOOKUP",Args()) 'Exécution de la fonction avec les paramètres "NOM DE LA FONCTION" et arguments


End Sub

pour l'instant libreoffice m'envoi balader avec une erreur du type

Variable d'objet non définie

c'est la première fois que je fais du basic, je suis preneur de conseils de votre part

[toitoinebzh]

après de multiples essais, j'ai réussi à faire à peu près fonctionner mes fonctions

bizarrement, ces fonctions (quasi identiques les unes des autres) fonctionnent de manière aléatoire

Code : Tout sélectionner

Function get_id(label)

	sheets = ThisComponent.Sheets
	sheet = sheets.getByName("BDD")
	
	Vrech = sheet.getCellRangeByName("A1:A10000")
	Vsort = sheet.getCellRangeByName("C1:C10000")
	
	oFuncAcces = CreateUnoService("com.sun.star.sheet.FunctionAccess") 
	
	get_id = oFuncAcces.callFunction("LOOKUP",Array(label,Vrech,Vsort))

End Function

si vous avez une explication, je suis preneur

[toitoinebzh]

je continue de creuser et je commence à croire que mon comportement bizarre est lié à une limitation de LOOKUP
https://ask.libreoffice.org/en/question ... swer-here/
https://forum.openoffice.org/en/forum/v ... =9&t=15344
https://forum.openoffice.org/en/forum/v ... 4&p=242212

apparemment la fonction LOOKUP fonctionne uniquement sur une liste qui est ordonnée

pas terrible comme situation, si quelqu'un peut me confirmer ?

[Jean-Louis Cadeillan]

Bonsoir,
J'ai cherché à contribuer au calcul de la consanguinité d'un individu connaissant totalement ou non ses ascendants, sur 5 générations, mais sans utiliser de macros. N'étant pas spécialiste en génétique, je me suis contenté d'appliquer la méthode des chemins, vue par exemple ici.
Pour cela, j'ai nommé tous les ascendants par un code de type Code_0x{M ou P}x fois. Par exemple, le grand-père paternel sera codé Code_02PP : le père du père, 2 générations au-dessus. L'individu dont on cherche le taux de consanguinité est juste codé Code_00.La longueur du chemin de l'ascendant à l'individu est liée à la longueur du code, c'est ce que j'utilise. Le repérage des ascendants communs aux lignées maternelles et paternelles est repéré par la fonction NB.SI().
Les seules cellules à renseigner sont les cases en gris-bleu (la plage B1:B63). Le taux de consanguinité apparaît en M33.
L'exemple actuellement renseigné est celui que tu prends en feuille Croisement. J'ai forcé à zéro le chemin quand l'ascendant n'est pas connu, ce qui fait que dans ce cas le taux calculé est inférieur ou égal au taux réel.
Si cela peut t'aider dans ton projet…
Cordialement,
Jean-Louis

[Jeff]

Bonjour à tous,

la fonction LOOKUP fonctionne uniquement sur une liste qui est ordonnée

Oui, car cela appelle la fonction RECHERCHE de Calc, dont l'aide précise :

le vecteur de recherche pour RECHERCHE doit être trié en ordre croissant, sinon la recherche ne renvoie aucun résultat exploitable

À la place, tu peux utiliser VLOOKUP, qui appelle la fonction RECHERCHEV, dont il faut renseigner le dernier paramètre, et là aussi l'aide précise :

Si ordre_de_tri est paramétré sur FAUX ou zéro, une correspondance exacte doit être trouvée

Tu as des exemples sur le forum :
https://forum.openoffice.org/fr/forum/s ... d%5B%5D=15

A +

[toitoinebzh]

merci à vous, cela va m'aider à avancer

pour la consanguinité, il faut que je prenne le temps de comprendre le travail de Jean-Louis Cadeillan , cela m'a l'air ardu

je vais tenter VLOOKUP, je pense que ça va résoudre une partie de mes bugs, le souci est que je ne fais pas toujours ma recherche dans la colonne de gauche, ce qui correspond au fonctionnement de VLOOKUP si je ne me trompe pas

[Jeff]

le souci est que je ne fais pas toujours ma recherche dans la colonne de gauche

Alors préfère utiliser INDEX et EQUIV, en langage macro l'équivalent est INDEX et MATCH, là aussi tu trouves des exemples :
https://forum.openoffice.org/fr/forum/s ... d%5B%5D=15

[toitoinebzh]

tout d'abord un grand merci pour vos conseils, j'ai bien avancé depuis le dernier message

maintenant,
* je suis capable de générer des pedigrees en un clin d’œil grâce aux différentes macro
* je peux calculer la consanguinité d'un individu (mode tableau)
* je suis passé à des études 5 générations
* j'ai alimenté le tableur avec des données exemples trouvées sur le net

il faut que je continu de creuser/comprendre le mode de calcul de consanguinité pour essayer de le "macro-iser"

[Bidouille]

Merci de (re)lire les règles de cette section :

Pour faciliter la compréhension de votre projet, vous pouvez également joindre votre application au fur et à mesure où elle se construit.
Cependant, afin de ne pas avoir une foultitude de versions, il recommandé de mettre celle-ci dans le 1er message que vous mettrez à jour au fur et à mesure.

[toitoinebzh]

c'est noté, j'ai retiré les anciennes versions et mis la dernière version dans le 1er message.

j'ai continué à avancer et maintenant j'ai une macro qui permet de calculer la consanguinité
le temps de calcul est de quelques minutes et je commence à voir le limites de calc pour la gestion de données importantes

les fonctionnalités les plus importantes sont maintenant disponibles et je passe dans la phase de finition du fichier.

Il me reste un bug que je ne sais corriger.

J'ai une fonction qui me renvoi un Array, la fonction marche bien, le souci est que la taille de l'Array n'est pas toujours la même.

par exemple si j'ai un array de taille 2x2 et que je passe à un array 3x3, certaines infos ne s'affiche pas
dans l'autre sens, si je passe d'un 3x3 à un 2x2, certaines cases ne sont pas remplis et passe en #N/D

si vous avez une astuce pour ce genre de situation, je suis preneur

[Dude]

Salut,

Tout sur les tableaux en Basic et la partie "Retailler".

[toitoinebzh]

Salut Dude et merci pour ton aide,

Je pense que j'ai mal décrit mon souci, en effet je n'ai pas de problème de retaillage mais un souci pour écrire un array (de taille aléatoire) dans une feuille.

Jusqu'ici j'aillais par exemple en A1 et je mettais la formule "=ma_fonction_qui_renvoie_un_array()", cela fonctionne bien à la condition que la taille de l'array est toujours la même,

si la taille est différente, je mets à jour cet array (ctrl+shift+F9) mais uniquement les cases de l'ancien tableau est mis à jour dans la feuille (pas de mises à jour de l'espace de cellule pour ce tableau)

le lien que tu m'as envoyé m'a été utile même si tout n'est pas résolu

j'ai tenté d'écrire une macro qui va écrire mon array dans la feuille en mettant à jour la dimension de l'espace réservé à cet array

Code : Tout sélectionner

sub inserer_matrice_croisement()
	Dim oCell as Object
	Dim sheet as Object
	Dim oPlage as Variant
	Dim col, row as Integer
	Dim oSelect as Variant
	
	' case définie à l'avance
	sheets = ThisComponent.Sheets
	sheet = sheets.getByName("Matrice de parenté")
	col = 1
	row = 7
	
	Dim TAB(1 to 5, 1 to 8) ' TAB est utiliser ici pour du test
	oSelect = TAB  'get_matrice_croisement() ' taille aléatoire
	
	start_column = col + LBound(oSelect,1)
    start_row = row + LBound(oSelect,2)
    end_column = col + UBound(oSelect,1)
    end_row = row + UBound(oSelect,2)
    oPlage = sheet.getCellRangeByPosition(start_column, start_row, end_column, end_row)

	oPlage.Data = oSelect 
	
end sub

pour l'instant ça ne fonctionne pas, j'ai une erreur sur la dernière ligne (RuntimeException), celle qui attribue mon array aux cases

[Jean-Louis Cadeillan]

Bonsoir,
Pour mémoire, ici, une version sans macro qui gère un effectif de plus de 700 individus dont on connaît père et mère, qui affiche (sur la feuille Pedigree) l'arbre généalogique jusqu'à la 5e génération et repère sur ce même arbre les individus consanguins (nom en rouge), de n'importe lequel de ces individus (à partir de son n° de boucle par liste déroulante, en B70), ainsi que son taux de consanguinité. Sur cet arbre, pour chaque individu, sont donnés le n° de boucle, le Nom et le sexe.
La feuille Consanguinité affiche le tableau de tous les individus, leurs ascendants jusqu'à la cinquième génération et leur taux de consanguinité.
La feuille Filiation donne le père et la mère des 700 individus. Cette BDD a été construite en utilisant des fonctions aléatoires qui ont été éliminées par la suite.
La feuille NomsAnimaux a servi à construire la base fictive et sert à alimenter la feuille Filiation (mais on pourrait aisément s'en passer).
Le classeur fonctionne aussi bien avec LibreOffice qu'OpenOffice, mais il a été conçu sous LibO.
Ce classeur peut servir d'élément comparateur avec une solution macro.
Cordialement,
Jean-Louis

[toitoinebzh]

j'ai atteint la majorité des mes objectifs

je viens de résoudre mon dernier bug et tout fonctionne comme attendu

@ Jean-Louis : merci pour ton doc, c'est intéressant de voir des approches différentes convergés vers le même résultat
je me suis permis de reprendre ta base de 700 individus pour gonfler mon doc

la méthode de calcul de consanguinité reste perfectible actuellement, elle a tendance à sur-estimer la valeur, cela me va bien pour l'instant

par exemple, la consanguinité calculée sur la diapo 6 et 7 de ce pdf devrait être identique alors qu'elle ne l'est pas avec le calcul actuel
https://www.ifce.fr/wp-content/uploads/ ... inite1.pdf

je ne sais pas si je vais m'y pencher tout de suite, mais la fonction que j'ajouterai volontiers serai la mise en place d'un formulaire d'ajout/modification d'individus pour éviter les erreurs de saisie et les doublons