Aide formule Sheet

Le 23/07/2022 à 21:19

Bonsoir,

J’ai un fichier Excel contenant des centaines de réponses différentes à une question libre. Je souhaite compter le nombre d’apparitions de chaque mot. Je sais qu’il y a la fonction NB.SI mais qui ne comptabilise les mots qu’avec l’orthographe exacte et s’il n’y a que lui seul dans la cellule. J’ai ainsi rajouté l’étoile (*) dans la formule, ce qui m’aide à compter peu importe la position du mot dans une réponse donnée.

Ma demande est :

Comment puis-je compter la fréquence d’apparition d’un mot même s’il est parfois mal orthographié ? (Par exemple pour fromage, trouver avec juste “froma”). Puis-je faire apparaître en surbrillance l’ensemble des mots ou bouts de mots comptés quand je les rentre dans la formule ?

Je vous joins les résultats de la question, si ça peut rendre plus clair mes propos. Si vous avez d’autres astuces qui peuvent m’être utiles ou des manières d’analyser la question, merci infiniment !!!!

Désolé si c’est du repost, j’ai du mal à chercher sur internet avec ma demande actuelle.

9question-17.xlsx (29.78 Ko)
Le 24/07/2022 à 09:20

Bonjour,

Padrino31 a écrit :

j’ai du mal à chercher sur internet avec ma demande actuelle.

pas étonnant, beau défi !

Je procéderais d'abord à une analyse globale, savoir repérer tous les mots significatifs (3 lettres et plus) en minuscules et ordonnées.

Le plus difficile sera de repérer les données qui ne commencent pas par la bonne lettre, il faudrait alors faire des recoupements pour connaître le niveau de concordance des mots. À faire dans une deuxième étape.

Le 24/07/2022 à 09:38

Au passage, on pourrait utiliser la méthode de "distance Levenshtein"

Le 24/07/2022 à 10:04

Pour la première étape, en créant une 'Feuille 2'

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const [src, dst] = ['Feuille 1', 'Feuille 2'].map(n => SpreadsheetApp.getActiveSpreadsheet().getSheetByName(n));
  let myDico = new Map()
  const data = src.getDataRange()
    .getValues()
    .flat()
    .join(' ')
    .replace(/[^a-zA-Zàéèùôêâ]+/g, ' ')
    .split(' ')
    .filter(x => x.length > 2)
    .map(x => x.toLowerCase())
    .sort()
    .forEach(x => {
      if (myDico.has(x)) { myDico.set(x, myDico.get(x) + 1) } else { myDico.set(x, 1) }
    })
  let result = []
  myDico.forEach(function (value, key) {
    result.push([key, value])
  })
  dst.clear()
  dst.getRange(1, 1, result.length, result[0].length).setValues(result)
}
Le 24/07/2022 à 22:52

Merci pour ta réponse rapide,

C'est effectivement un sacré défi de trouver une réponse dans l'immensité du web.

Sinon, je n'ai pas compris ta formule que tu m'as montrée en dernier message. Je suis encore un débutant sur Sheet et ne connais que les rudiments.

Du coup, concernant mes deux questions, est-ce possible ou pas du tout ?

Après pour l'analyse, j'ai procédé en découpant chaque aliment dans une case à part pour les reclasser par la suite dans les bonnes catégories. J'ai aussi avant le reste regardé les réponses pour voir visuellement les mots récurrents afin de les compter directement de mon côté, ce qui m'enlève du tri ultérieurement.

Cependant, ce travail est long et fastidieux, d'où le fait que je quémande de l'aide pour trouver un raccourci mathématique.

Le 25/07/2022 à 01:16
Padrino31 a écrit :

Du coup, concernant mes deux questions, est-ce possible ou pas du tout ?

OUI et NON

Comment puis-je compter la fréquence d’apparition d’un mot même s’il est parfois mal orthographié ?

OUI d'une certaine façon, tu verras ma proposition

Puis-je faire apparaître en surbrillance l’ensemble des mots ou bouts de mots comptés quand je les rentre dans la formule ?

a priori NON, du moins très complexe !

Le 25/07/2022 à 01:26

Un feuille appelée données

une feuille appelée résultat

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 Rechercher les termes récurrents', 'analyse')
    .addToUi();
}

function analyse() {
  const param1 = 8
  const param2 = 0.75
  const [src, synth] = ['données', 'résultat'].map(n => SpreadsheetApp.getActiveSpreadsheet().getSheetByName(n));
  const sauf = ['les', 'des', 'pour', 'pas', 'bon', 'est', 'etc', 'que', 'ici', 'aussi', 'qui', 'bien', 'avec', 'cher', 'tous', 'plus', 'tes', 'aux', 'trop', 'sont', 'dans', 'rien', 'sel', 'tout', 'adore', 'mais', 'par', 'car', 'une', 'nous', 'saint', 'nourriture', 'faire', 'suis']
  let myDico = new Map()
  const data = src.getDataRange()
    .getValues()
    .flat()
    .map(x => x.toLowerCase())          // en minuscules
    .join(' ')
    .replace(/[^a-zàéèùôêâçî]+/g, ' ')  // on supprime tout sauf ces lettres
    .split(' ')
    .filter(x => x.length > 2)          // tous les mots de 3 lettres au moins
    .filter(x => sauf.indexOf(x) == -1) // sauf ceux de la liste
    .forEach(x => {
      if (myDico.has(x)) { myDico.set(x, myDico.get(x) + 1) } else { myDico.set(x, 1) } // comptage
    })
  let termes = []
  myDico.forEach(function (value, key) { termes.push([key, value]) })
  let criteres = termes.filter(r => r[1] >= param1).map(x => x[0]) // tous les termes avec plus de param1 occurences
  let groupes = [["termes apparaissant plus de " + param1 + " fois", "termes avec similitude > à " + Math.floor(param2 * 100) + " %", "nombre d'occurences"]]
  criteres.forEach(c => { 
    let items = [], n = 0
    termes.forEach(t => {
      if (similarity(t[0], c)>param2){
        items.push(t[0])
        n+=t[1]
      }
    })
    groupes.push([c, items.join(' '), n])
  })
  synth.clear()
  groupes = groupes.sort(function (a, b) {
    return b[2] - a[2];
  });
  synth.getRange(1, 1, groupes.length, groupes[0].length).setValues(groupes)
  synth.activate()
}

function similarity(s1, s2) {
  var longer = s1;
  var shorter = s2;
  if (s1.length < s2.length) {
    longer = s2;
    shorter = s1;
  }
  var longerLength = longer.length;
  if (longerLength == 0) {
    return 1.0;
  }
  return (longerLength - editDistance(longer, shorter)) / parseFloat(longerLength);
}

function editDistance(s1, s2) {
  s1 = s1.toLowerCase();
  s2 = s2.toLowerCase();
  var costs = new Array();
  for (var i = 0; i <= s1.length; i++) {
    var lastValue = i;
    for (var j = 0; j <= s2.length; j++) {
      if (i == 0)
        costs[j] = j;
      else {
        if (j > 0) {
          var newValue = costs[j - 1];
          if (s1.charAt(i - 1) != s2.charAt(j - 1))
            newValue = Math.min(Math.min(newValue, lastValue),
              costs[j]) + 1;
          costs[j - 1] = lastValue;
          lastValue = newValue;
        }
      }
    }
    if (i > 0)
      costs[s2.length] = lastValue;
  }
  return costs[s2.length];
}
Le 25/07/2022 à 01:33

Dans le dépouillement ...

image

on aura dans la première colonne les termes qui apparaissent plus de 8 fois, et dans la seconde les autres termes qui leur sont apparentés (à 75% selon le calcul de la distance de Levenshtein)

on pourra alors avoir des famille en doubles ou plus si plusieurs termes sont chacun obtenus plus de 8 fois sous des orthographes différentes

on peut voir aussi des termes qui ne veulent rien dire de façon isolée, comme mer ! je me suis posé la question, mais en fait il faut le lire de façon composée avec un autre terme : fruits de mer

Le 26/07/2022 à 06:39

Oublions un peu Levenshtein et mes élucubrations précédentes qui m'ont permis de m'approprier la problématique.

Voici une fonction qui permet de compter le nombre d'occurrences

=nbOccurrences(A1)

il est pris en compte le fait qu'il puisse y avoir des inversions dans les caractères.

on n'est pas obligé de mettre le mot en entier

image 
function nbOccurrences(terme) {
  const regex = new RegExp(`[${terme}]{${terme.length},}`, 'gi'); 
  const characters = terme.toLowerCase().split('')
  const values = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('données').getDataRange().getValues();
  let myVal;
  let match;
  let n = 0;
  values.map(row => row.map(value => {
    while (match = regex.exec(value)) {
      myVal = value.substring(match.index, match.index + match[0].length).toLowerCase()
      if (characters.every(inside)) { n++; }
    }
  }));
  return n;
  function inside(c) { return myVal.match(c) != null }
}
Le 27/07/2022 à 15:20

Encore merci pour tout ce temps et cette énergie dédiés à mon problème !

Quand tu me montres l’algorithme que tu as mis en place, comment je fais pour le rentrer dans Excel également ? Par exemple, avec ta formule sur l’occurrence, il faut pareillement que je rentre un de tes algorithmes, car il ne connaît pas cette fonction sinon.

Ton tableau sur le dépouillement est exactement ce que je souhaite faire (juste avec quelques précisions comme ne pas compter “fromage blanc” dans “fromage”)

Peux-tu m’expliquer étape par étape comment tu y arrives (ce qui rejoint ma première question) ou du moins me partager le tableau du dépouillement dans son intégralité ?

Mille merci l’ami !

Le 27/07/2022 à 18:12
Padrino31 a écrit :

Encore merci pour tout ce temps et cette énergie dédiés à mon problème !

c'était un vrai plaisir, ce n'est pas souvent que l'on a un casse-tête, et un challenge car en effet je n'ai rien trouvé sur la "toile" !

Padrino31 a écrit :

Quand tu me montres l’algorithme que tu as mis en place, comment je fais pour le rentrer dans Excel également ? Par exemple, avec ta formule sur l’occurrence, il faut pareillement que je rentre un de tes algorithmes, car il ne connaît pas cette fonction sinon.

je ne travaille plus avec excel, et je n'ai plus de quoi le faire car je suis passé d'un PC à un chromebook

il faudrait re-poster ta demande dans le chapitre excel

mais la fonction ne peut pas se dupliquer en l'état, il faut la ré-écrire complètement, les langages ne sont pas compatibles, excel utilise un langage propriétaire microsoft VBA et GSheets un langage pus ouvert basé sur javascript

Padrino31 a écrit :

Ton tableau sur le dépouillement est exactement ce que je souhaite faire (juste avec quelques précisions comme ne pas compter “fromage blanc” dans “fromage”)

c'est la même chose pour fruits et fruits de mer, mais là on touche les limites du système, ce n'est pas possible, on est plus dans la "statistique" ici

c'est la même chose quand tu tapes mal un terme et que le correcteur orthographique te propose des alternatives qui n'ont rien à voir !

il faut ensuite aller dans le texte pour interpréter

Padrino31 a écrit :

Peux-tu m’expliquer étape par étape comment tu y arrives (ce qui rejoint ma première question) ou du moins me partager le tableau du dépouillement dans son intégralité ?

ok, je vais le faire demain ... à suivre

Le 27/07/2022 à 21:23
Padrino31 a écrit :

Peux-tu m’expliquer étape par étape comment tu y arrives (ce qui rejoint ma première question) ou du moins me partager le tableau du dépouillement dans son intégralité ?

j'ai fait plusieurs choses avant d'y arriver, alors je ne sais plus trop de quel "tableau" tu veux parler

néanmoins, pour ce qui concerne la fonction https://forum.excel-pratique.com/sheets/aide-formule-sheet-174270#p1081770

=> voir point suivant

et pour ce qui concerne ce tableau https://forum.excel-pratique.com/sheets/aide-formule-sheet-174270#p1081539

=> voir point après si j'y arrive ce soir

Le 27/07/2022 à 21:46

Pour la formule, ce qui m'a in fine donné pas mal de fil à retordre

Pour comprendre, je l'ai un peu adaptée sur une phrase

const phrase = "former fromage froment pain roro formidable "

function nbOccurrences(terme='form') {
  const regex = new RegExp(`[${terme}]{${terme.length},}`, 'gi'); 
  const characters = terme.toLowerCase().split('')
  const values = phrase.split(' ');
  let myVal;
  let match;
  let n = 0;
  values.map(value => {
    while (match = regex.exec(value)) {
      myVal = value.substring(match.index, match.index + match[0].length).toLowerCase()
      console.log(match.input)
      if (characters.every(inside)) { n++; }
    }
  });
  return n;
  function inside(c) { return myVal.match(c) != null }
}

le terme choisi ici est "form" ce qui devrait me conduire à fromage par exemple

je vais chercher tous les mots qui contiennent f, o, r et m en 4 lettres mini contiguës, d'où l'expression régulière

const regex = new RegExp(`[form]{4,}`, 'gi')

cela me donne ce résultat, pas mal

[ 'former', 'fromage', 'froment', 'roro', 'formidable' ]

mais un terme ne comporte ni de f ni de m

d'où le fait de rechercher si au moins chaque lettre est bien présente une fois, ce qui éliminera roro (tous les autres dans ce cas seront retenus = c'est souvent le cas pour des correcteurs orthographiques)

function inside(c) { return myVal.match(c) != null }

voilà grosso modo car j'avoue que l'écriture fait appel à des notions telles que

et c'est difficile dans ton cas de simplifier sans allonger le code démesurément !

Le 27/07/2022 à 22:02

Le tableau, c'est plus long mais plus simple à expliquer

function analyse() {
  const param1 = 8
  const param2 = 0.75
  const [src, synth] = ['données', 'résultat'].map(n => SpreadsheetApp.getActiveSpreadsheet().getSheetByName(n));
  const sauf = ['les', 'des', 'pour', 'pas', 'bon', 'est', 'etc', 'que', 'ici', 'aussi', 'qui', 'bien', 'avec', 'cher', 'tous', 'plus', 'tes', 'aux', 'trop', 'sont', 'dans', 'rien', 'sel', 'tout', 'adore', 'mais', 'par', 'car', 'une', 'nous', 'saint', 'nourriture', 'faire', 'suis']
  let myDico = new Map()
  const data = src.getDataRange()
    .getValues()
    .flat()
    .map(x => x.toLowerCase())          // en minuscules
    .join(' ')
    .replace(/[^a-zàéèùôêâçî]+/g, ' ')  // on supprime tout sauf ces lettres
    .split(' ')
    .filter(x => x.length > 2)          // tous les mots de 3 lettres au moins
    .filter(x => sauf.indexOf(x) == -1) // sauf ceux de la liste
    .forEach(x => {
      if (myDico.has(x)) { myDico.set(x, myDico.get(x) + 1) } else { myDico.set(x, 1) } // comptage
    })
  let termes = []
  myDico.forEach(function (value, key) { termes.push([key, value]) })
  let criteres = termes.filter(r => r[1] >= param1).map(x => x[0]) // tous les termes avec plus de param1 occurences
  let groupes = [["termes apparaissant plus de " + param1 + " fois", "termes avec similitude > à " + Math.floor(param2 * 100) + " %", "nombre d'occurences"]]
  criteres.forEach(c => { 
    let items = [], n = 0
    termes.forEach(t => {
      if (similarity(t[0], c)>param2){
        items.push(t[0])
        n+=t[1]
      }
    })
    groupes.push([c, items.join(' '), n])
  })
  synth.clear()
  groupes = groupes.sort(function (a, b) {
    return b[2] - a[2];
  });
  synth.getRange(1, 1, groupes.length, groupes[0].length).setValues(groupes)
  synth.activate()
}
<br>

param1 : je retiendrai les mots dont le nombre d'occurrences est supérieur à 8, tels qu'ils ont été écrits avant recheche de similitude

  const param1 = 8<br>

param2 : sera ensuite le degré de similitude entre la liste des mots ci-dessus et tous les mots de la feuille

  const param2 = 0.75

j'élimine ces mots inutiles ici car ne sont pas des denrées

const sauf = ['les', 'des', 'pour', 'pas', 'bon', 'est', 'etc', 'que', 'ici', 'aussi', 'qui', 'bien', 'avec', 'cher', 'tous', 'plus', 'tes', 'aux', 'trop', 'sont', 'dans', 'rien', 'sel', 'tout', 'adore', 'mais', 'par', 'car', 'une', 'nous', 'saint', 'nourriture', 'faire', 'suis']

ensuite je prends toute la feuille, que je mets à plat (flat), en ne prenant que les minuscules, en remplaçant tout ce qui n'est pas a à z et àéèùôêâçî (encore une expression régulière) par un espace et en découpant selon les espaces, je place le tout dans un dictionnaire en comptant chaque fois que je rencontre un terme déjà connu

[^a-zàéèùôêâçî]+/g
  const data = src.getDataRange()
    .getValues()
    .flat()
    .map(x => x.toLowerCase())          // en minuscules
    .join(' ')
    .replace(/[^a-zàéèùôêâçî]+/g, ' ')  // on supprime tout sauf ces lettres
    .split(' ')
    .filter(x => x.length > 2)          // tous les mots de 3 lettres au moins
    .filter(x => sauf.indexOf(x) == -1) // sauf ceux de la liste
    .forEach(x => {
      if (myDico.has(x)) { myDico.set(x, myDico.get(x) + 1) } else { myDico.set(x, 1) } // comptage
    })

je peux maintenant en tirer mes mots dont les occurrences sont supérieurs à 8

let criteres = termes.filter(r => r[1] >= param1).map(x => x[0]) // tous les termes avec plus de param1 occurences

et pour chaque mot-critère, je vais regarder si la similitude avec chaque terme est d'au moins 75%

  criteres.forEach(c => { 
    let items = [], n = 0
    termes.forEach(t => {
      if (similarity(t[0], c)>param2){
        items.push(t[0])
        n+=t[1]
      }
    })
    groupes.push([c, items.join(' '), n])
  })

pour le calcul de la similitude, je te renvoie aux algorithmes écrits à la suite des travaux de Levenshtein utilisés de nos jours dans pas mal d'applications textuelles

...

in fine j'obtiens un tableau avec le critère (mot dont la fréquence est > 8) et tous les termes similaires à plus de 75% (à noter que chocolat et chocolatine ne sont pas similaires à plus de 75%) avec un décompte

j'ai obtenu tout ceci après 8h d'efforts !

Le 27/07/2022 à 22:04

maintenant, si tu veux aussi le résultat en surbrillance, je peux le faire si tu partages un fichier

Le 01/08/2022 à 12:14

Bonjour,

Merci pour tout le temps que tu y as passé, tu m'as bien aidé pour les termes les plus courants.

J'ai fini la liste de mon côté aussi pour bien trier les termes dans leur entièreté.

Tes explications sont très intéressantes, un poil trop technique par moment pour moi qui ne fais plus de sciences depuis mon bac S (j'en ai 25).

Je te souhaite le meilleur pour la suite !

PS : J'aimerais avoir ton numéro pour te remercier en personne si tu es d'accord

Le 01/08/2022 à 12:33
Padrino31 a écrit :

PS : J'aimerais avoir ton numéro pour te remercier en personne si tu es d'accord

tout le plaisir fut pour moi, ce n'est pas souvent que l'on a un sujet de ce niveau, du reste très intéressant et au cours duquel j'ai appris aussi des choses

tu peux toujours ma payer un café (lol) :

Le 01/10/2022 à 21:31

Salut !

Je reviens après la bataille (pour des raisons personnelles) mais je tenais à te remercier !

Je t'ai acheté un café (je ne connaissais pas ce site). J'y ai cependant appris qu'on parle ici d'un café parisien haha.

Bonne soirée à toi et à la prochaine :)

Rechercher des sujets similaires à "aide formule sheet"