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

Discussions sur les projets et applications développés en liaison avec une suite bureautique libre.

Modérateur: Vilains modOOs

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

Messagepar toitoinebzh » 06 Mai 2021 20:41

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
Pièces jointes
Elevage4.ods
(455.84 Kio) Téléchargé 18 fois
Dernière édition par toitoinebzh le 29 Mai 2021 18:44, édité 8 fois.
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: Gestion Troupeau - genalogie/consaguinite

Messagepar toitoinebzh » 06 Mai 2021 20:48

Première question

dans mes formules, j'utilise régulièrement des fonctions du type
Code : Tout sélectionner   AgrandirRéduire
=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   AgrandirRéduire
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
Dernière édition par toitoinebzh le 10 Mai 2021 17:42, édité 1 fois.
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 07 Mai 2021 17:00

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   AgrandirRéduire
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
Dernière édition par toitoinebzh le 10 Mai 2021 17:42, édité 1 fois.
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 07 Mai 2021 20:57

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 :roll:

pas terrible comme situation, si quelqu'un peut me confirmer ?
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar Jean-Louis Cadeillan » 07 Mai 2021 21:58

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
Pièces jointes
toitoinebzh_20210506_JLC01.ods
(25.76 Kio) Téléchargé 17 fois
LibO 7.0.6.2 (x64 avec Java 1.8.0_281) et AOO 4.1.10 (avec Java x32 1.8.0_241), Windows 7 Édition Intégrale 64 SP1, (Domicile)
LibO 5.4.1.2 (x86) sous Ubuntu LTS 16.04.1, noyau 4.4.0-93 et Xfce 4.12, Java (x86) 1.8.0_131 (Travail) [obligation de version]
Avatar de l’utilisateur
Jean-Louis Cadeillan
GourOOu
GourOOu
 
Message(s) : 5176
Inscrit le : 03 Jan 2009 00:56

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar Jeff » 08 Mai 2021 11:29

Bonjour à tous,

toitoinebzh a écrit: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 :
L'aide F1 a écrit: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 :
L'aide F1 a écrit: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 :
search.php?st=0&sk=t&sd=d&sr=posts&keywords=VLOOKUP&fid%5B%5D=8&fid%5B%5D=15

A +
À lire avant tout !

Pour télécharger Apache OpenOffice

AOO 4.1.10 sur Ubuntu 20.04.02 x64 (Focal Fossa), et sur Win 10 pro x64
Jeff
GourOOu
GourOOu
 
Message(s) : 8502
Inscrit le : 18 Sep 2006 10:40
Localisation : France

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 08 Mai 2021 14:55

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
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar Jeff » 08 Mai 2021 18:33

toitoinebzh a écrit: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 :
search.php?st=0&sk=t&sd=d&sr=posts&keywords=index++match&fid%5B%5D=8&fid%5B%5D=15
À lire avant tout !

Pour télécharger Apache OpenOffice

AOO 4.1.10 sur Ubuntu 20.04.02 x64 (Focal Fossa), et sur Win 10 pro x64
Jeff
GourOOu
GourOOu
 
Message(s) : 8502
Inscrit le : 18 Sep 2006 10:40
Localisation : France

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 08 Mai 2021 19:21

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"
Dernière édition par toitoinebzh le 10 Mai 2021 17:42, édité 1 fois.
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar Bidouille » 10 Mai 2021 07:23

Merci de (re)lire les règles de cette section :
Dans ces règles, il y a écrit: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.
Avatar de l’utilisateur
Bidouille
RespOOnsable forum
RespOOnsable forum
 
Message(s) : 10708
Inscrit le : 08 Nov 2005 17:23
Localisation : Brest, France

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 10 Mai 2021 17:50

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é :D :D
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 bogue 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
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar Dude » 10 Mai 2021 19:39

Salut,

Tout sur les tableaux en Basic et la partie "Retailler".
Avatar de l’utilisateur
Dude
Grand Maître de l'OOffice
Grand Maître de l'OOffice
 
Message(s) : 22587
Inscrit le : 03 Mars 2006 08:45
Localisation : 127.0.0.1

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 11 Mai 2021 16:44

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   AgrandirRéduire
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
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar Jean-Louis Cadeillan » 11 Mai 2021 21:25

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
LibO 7.0.6.2 (x64 avec Java 1.8.0_281) et AOO 4.1.10 (avec Java x32 1.8.0_241), Windows 7 Édition Intégrale 64 SP1, (Domicile)
LibO 5.4.1.2 (x86) sous Ubuntu LTS 16.04.1, noyau 4.4.0-93 et Xfce 4.12, Java (x86) 1.8.0_131 (Travail) [obligation de version]
Avatar de l’utilisateur
Jean-Louis Cadeillan
GourOOu
GourOOu
 
Message(s) : 5176
Inscrit le : 03 Jan 2009 00:56

Re: [Calc] Gestion Troupeau - généalogie/consanguinité

Messagepar toitoinebzh » 12 Mai 2021 16:26

:D :D j'ai atteint la majorité des mes objectifs :D :D

je viens de résoudre mon dernier bogue 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
xubuntu 20.04 - Libreoffice 6.4.7.2
toitoinebzh
Membre lOOyal
Membre lOOyal
 
Message(s) : 38
Inscrit le : 27 Nov 2007 19:14


Retour vers Projets

Qui est en ligne ?

Utilisateur(s) parcourant ce forum : Aucun utilisateur inscrit et 2 invité(s)