Comment calculer l'occurence de plusieurs mots dans des cellules contenant du texte ?

[Luda]

Bonsoir à tous,
L'intitulé de mon post n'est pas très clair mais je n'ai pas trouvé mieux je cherche une formule (à base de NB.SI je pense) qui me permettrait de calculer le nombre d’occurrence dans plusieurs cellules qui contiennent du texte.

J'ai des codes de la forme 120t/45h/62u dans des cellules et je cherche à connaitre le nombre de "62u" par exemple dans plusieurs cellules. Le problème c'est que des codes comme celui-ci j'en ai une cinquantaine, j'aimerais donc trouver la solution pour automatiser le calcul et trouver le nombre d’occurrence de chaque code pour chacun des sites.

A B C D E
1 Sites Echantillon1 Echantillon2 Echantillon3 Echantillon4
2 A 120t/45h/62u 62t/84y 78y/96j 45h/63f
2 B 115f/87t/95h/ 65h/78r/23e 78g/98t 78r/15t/35u

et dans les colonnes suivantes j'ai la liste de tous les codes utilisés
F G I .... BD
1 120t 78g 23e 65h
2 nA? nA? nA ?
3 nB? nB? nB ?


J'espère avoir été assez clair ...
Merci d'avance pour votre aide !

 

[hoerwind]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonsoir et bienvenue sur le forum,

Je suppose que tu as un fichier avec les données sous la main.
Alors pourquoi ne pas l'avoir joint à ta question, tel que recommandé par la charte de ce forum.

De plus, si tu pouvais y ajouter la mise en forme du résultat tel que tu souhaiterais l'obtenir, la réponse qui te sera donnée devrait normalement être conforme à tes désirs.

 

[Misange]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonjour
dans l'attente d'un fichier exemple, je te dirai que la solution à ton problème passe très probablement par l'utilisation d'un tableau croisé dynamique.
Un petit tour sur la charte est une bonne idée avant de poster pour la première fois sur un forum. Il n'y est pas simplement dit qu'il ne faut pas poster de propos offensant et être gentil 🙂

 

[Luda]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonjour,
Merci pour vos réponses rapides !
Bon d'accord je l'avoue je n'ai pas lu la charte pour les raisons citées ci-dessus je pensais qu'elle rappelait seulement d'être poli, ce qui est pour moi une évidence... mais je viens de la lire, alors je reprends dans l'ordre.

J'ai un niveau débutant, bien que j'ai eu des cours sur Excel sur les principales fonctions, mais je ne connaissais pas les tableaux croisés dynamiques qui ont l'air pourtant très pratiques et j'ai la version 2007.
Vous trouverez ci-joint un fichier exemple où j'ai essayer de supprimer toutes les données qui étaient inutiles pour mon problème.
La première feuille du classeur "Cycle1" contient les données et la seconde feuille "Codes" contient la mise en forme des résultats (X) que je souhaiterais obtenir (merci pour le conseil). C'est certes plus complet que mon premier exemple mais j'espère que cela ne va pas vous compliquer le problème.

J'aimerais en fait calculer le nombre de chaque code (listés dans la ligne 2 de la feuille "codes") pour chacune des placettes (Feuille "Cycle 1" - colonne A) sachant que pour chaque placette j'ai 4 quartiers (peu importe de connaitre de quel quartier vient le code ; mais cette mise en forme m'est utile pour d'autres calculs). Le problème devient un peu plus complexe, car dans le cas où j'arriverais à avoir le nombre de chaque code pour chaque placette, je souhaiterais y appliquer un coefficient "Poids à l'hectare" - Colonne Q feuille "Cycle 1" spécifique à chaque placette, (une simple multiplication avec le nombre de code et ce coefficient) pour avoir la densité de codes à l'hectare.

J'ai laissé sur la feuille les diamètres et les espèces, car j'aimerais savoir s'il est possible d'avoir le nombre de codes (tous codes confondus) par espèce et par catégorie de diamètre... mais c'est peut-être une autre question, que j'essayerais de résoudre.

J'espère avoir été assez précis, n'hésitez pas à me demander d'autres précisions. Merci encore pour votre aide... en attendant je vais continuer à m'essayer sur le tableau croisé dynamique... mais je n'arrive toujours pas à désolidariser un ensemble de codes dans une même cellule...

 

[Misange]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonjour,
mais je n'arrive toujours pas à désolidariser un ensemble de codes dans une même cellule...

c'est un vrai problème pour l'exploitation de tes données.
Tu peux facilement convertir ta colonne contenant les codes (données/convertir, utilise le / comme séparateur) de façon à n'avoir qu'un seul code par cellule.
Mais ta base de données est mal conçue pour une exploitation simple.
Une base de données c'est 1 seul type d'info par ligne et on répète tout ce qui est commun

donc chaque code à analyser doit se trouver sur une ligne dans laquelle figurent toutes les données nécessaires pour l'interpréter : quartier, diamètre, poids à l'hectare....

j'ai commencé à te le faire mais c'est à toi de continuer...
voici un petit exemple qui t'aidera à démarrer sur les tcd.
dans la colonne A du tcd tu as les placettes et le poids à l'Ha
puis regrouper (n'importe comment !) par groupe de diamètres le nombre de codes 1.

Je t'ai mis tes tables de données sous forme de tableaux excel c'est bien plus simple à manipuler
Ce lien n'existe plus

 

[Luda]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Merci beaucoup pour tes conseils !
La base de données est sous cette forme car j'ai des calculs (dont le poids à l'hectare, mais pas seulement) qui se fait avec la distance des 4 individus étudiés pour chaque placette. Cela me semble difficile au point de vue de ces calculs de changer la structure de la base de données, car c'est important que le seul type d'information par ligne soit la placette. Sinon je peux faire un nouveau classeur spécialement pour l'analyse de ces codes, mais il n'est pas possible d'avoir une formule pour préciser qu'il y a plusieurs valeurs dans la même case ?

Merci ;-)

 

[Luda]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

c'est un vrai problème pour l'exploitation de tes données.

Une base de données c'est 1 seul type d'info par ligne et on répète tout ce qui est commun

donc chaque code à analyser doit se trouver sur une ligne dans laquelle figurent toutes les données nécessaires pour l'interpréter : quartier, diamètre, poids à l'hectare....

j'ai commencé à te le faire mais c'est à toi de continuer...

Ce lien n'existe plus

J'essaye de refaire une base de données structurée selon tes conseils pour l'analyse des codes. J'ai chacun des codes dans une cellule mais j'ai toujours qu'une seule ligne pour une placette. As-tu une technique rapide pour reproduire autant de lignes qu'il y a de codes pour chaque placette, comme tu as fait sur ton exemple ou faut il faire de l'insertion de lignes puis du copier/coller ?

Je te remercie.

 

[Misange]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Perso je ne vois pas comment par formule répondre à ta question en gardant tous les codes dans la même cellule. D'autres s'y risqueront peut être ?
Je privilégie toujours le fait d'avoir une base de données bien construite plutot que de recourir à des formules risquées et compliquées ou à des macros longuettes pour analyser une base de données pas assez structurée.
Si tu as besoin de relier tes 4 données NE NO, SE et SO alors ajoute une colonne à ta base en mettant un N° d'index qui relie les 4 valeurs. Il te sera très facile avec le TCD de remettre ensemble ces 4 valeurs pour les analyser.

 

[Misange]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Trie ta base par code 2, recopie les lignes ayant un code 2, colle les dans un autre endroit puis supprime les colonnes code 1 code 3 code 4.
Fais l'équivalent pour codes 3 et 4 et tu auras fait le job assez vite

 

[tototiti2008]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonjour Luda, Bonjour Misange 🙂,

Pas sûr d'avoir tout suivi, mais il me semble (sur la base de ton fichier original) que si en S2 tu as le code 643H
la formule

=NB.SI(I4:I46;"*"&S2&"*")

devrait te donner le nombre de codes 643H dans la colonne

Mais séparer les différents codes serait évidement beaucoup plus propre et exploitable

 

[david84]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonjour, bonjour Misange,
J'ai regardé ton fichier du message #4 et je n'ai pas compris à quoi correspondait ton tableau de l'onglet Code.
En ligne 2, je pense que ce sont les codes à rechercher, en colonne A les placettes mais à quoi correspondent les croix ?
Si ce sont les résultats à obtenir et quitte à prendre un exemple avec moins de données, note manuellement quelques résultats attendus sur ton fichier afin que l'on puisse mieux comprendre ton attente.

Perso je ne vois pas comment par formule répondre à ta question en gardant tous les codes dans la même cellule. D'autres s'y risqueront peut être ?

Peut-être en utilisant ESTNUM combiné à CHERCHE ou un NB.SI avec joker mais pour l'instant ce n'est qu'une supposition...
A+
Edit : salut Marc

 

[Misange]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Hello Marc et David,
Sachant que le nombre de codes par cellule varie, je trouve que c'est une source +++ d'ennuis que de chercher à répondre à la question avec la disposition initiale des données. MAis c'est vrai que je suis un peu maniaque sur les BDD 🙂...

 

[tototiti2008]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Bonjour David 🙂,
Re,

D'accord avec toi, Misange, sur le principe général
Mais sur une vraie BDD (relationnelle j'entend) les codes seraient dans une autre table et une relation permettrait de faire le lien entre les tables, ce qu'Excel ne sait pas faire...alors après on bidouille 😉

 

[Luda]

Re : Comment calculer l'occurence de plusieurs mots dans des cellules contenant du te

Je suis d'accord que pour les codes c'est plus simple d'avoir une ligne pour un code mais j'ai d'autres calculs à faire où il était plus simple que chaque ligne regroupe les résultats d'une placette. Mais je vais reconstruire une base pour l'analyse des codes comme me l'a conseillé Misange. Je suis toujours preneur si vous avez une solution avec cette conformation.

Merci à tous.