Mise à jour automatique d'une Formule

Le 03/05/2022 à 17:34

Bonjour,
Je souhaiterais savoir si il est possible d'automatisé la mise à jour d'une formule. Je m'explique, j'ai un fichier Sheets ou j'ai plusieurs feuilles, et les données de chaque feuilles sont reprise dans une autre feuille avec une formule de ce type :
='Lyam Roux'!W7+'Jesus Martinez'!W7+'Toto Riina'!W7
Est-il possible de faire en sorte que dès que je créer une nouvelle fiche elle soit rajouter dans la commande et à l'inverse dès que j'en supprime une elle soit enlever de la commande.
Psk tout faire manuellement sur le nombre incalculable de formules que j'ai c'est vraiment long.
Merci d'avance pour votre réponse.

Le 03/05/2022 à 21:40

Bonjour, et bienvenue

Peux-tu en dire davantage sur le type de formule ?

Peux-tu partager un fichier ?

Il est possible d'appliquer cette fonction qui va reprendre toutes les feuilles situées entre 2 feuilles témoins d et f en faisant la somme des cellules des feuilles se situant au même endroit (en mettant juste =sum3D() en W7 de la feuille de calcul)

// ajouter une case à cocher dans l'appel de la formule pour la réactiver en cas d'ajout de feuille ou de modif de données
function sum3D(){
  var fs = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var plage = SpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation()
  for (var i=0 ; i<fs.length ; i++){
    if(fs[i].getName() == 'd'){var f1=i}
    if(fs[i].getName() == 'f'){var f2=i}
  }
  var result = 0
  for (var i=f1+1 ; i<f2 ; i++){
    result += fs[i].getRange(plage).getValue()
  }
  return result
}

j'ai aussi d'autres variantes, mais je pense qu'un audit plus approfondi de ton document peut amener des solutions plus adaptées et plus simples

Le 03/05/2022 à 22:29

Bonjour Vectah, le fil

Je subodore une construction du style "un employé=un onglet"

et je poursuis donc 10 employés c'est exploitable, mais 100 employés feront beaucoup d'onglets (ne serait-ce qu'à lire), cpdt que 1000 employés obligeront à avoir une formule gigantesque !?

Le 04/05/2022 à 00:19

Oui c'est un employé = une feuille. Par exemple j'ai eu le problème tout à l'heure, un employé est partit donc je dois enlevé la feuille mais du coup toutes mes formules sont en erreur et je suis obligé d'aller dans chaque commandes trouvé le nom de cette personne et l'enlever de la commande. Pareil si j'embauche un employé je dois le rajouter partout. Et pour la réponse de Steelson, en gros cela voudrait dire que si j'ai une feuille a gauche et une feuille a droite de mes employé chaque feuille que je rajouterai au milieu seront rajouter a la commande si dans la cellule W7 il y a son nom ?

Le 04/05/2022 à 01:03

Vectah

Questions indiscrètes...

  1. Combien d'onglets (donc d'employés) à gérer ?
  2. Le turn-over est-il important dans ta société ?
  3. Est-ce qu'il ne serait pas plus simple de créer un onglet unique servant de fiche employé couplé à une base de données des informations à analyser, éventuellement automatisé par un code VBA adapté
Le 05/05/2022 à 17:52

Bonjour,
1. Pour l'instant 15 mais peut être plus ou moins dans le futur
2. Oui très important
3. J'ai pas compris désolé
Mais sinon je me disais, il existerait pas une commande qui cherche automatiquement la case de chaque feuilles. Puisque les données que je cherche à regrouper sont à chaque fois dans la même case, par exemple F7, si je demande à une formule de prendre dans chaque feuille la case F7 et d'additionner les données récupérer cela serait bon. Mais est-ce qu'une formule comme celle-ci existe ?

Le 05/05/2022 à 18:05
Vectah a écrit :

si je demande à une formule de prendre dans chaque feuille la case F7 et d'additionner les données récupérer cela serait bon. Mais est-ce qu'une formule comme celle-ci existe ?

  1. C'est en effet ce que j'ai proposé https://forum.excel-pratique.com/sheets/mise-a-jour-automatique-d-une-formule-171401#p1062839
  2. Mais la solution évoquée par Green SoftS me parait plus professionnelle. à savoir entretenir une base de données et si besoin en récupérer les infos sur une feuille unique configurable en fonction du salarié (en menu déroulant), cette feuille permettant aussi de mettre à jour le base de données ... il faut que je retrouve l'exercice fait il y a quelques mois sur les entretiens individuels.
Le 05/05/2022 à 18:24

Ah oui d'accord mais cette formule je la rentre ou ?

Et comment on créer la case pour l'appel de la formule ?
Je suis pas un expert en google sheets comme vous pouvez le voir

Le 05/05/2022 à 18:37

pour le 2/ en fait je l'avais fait sur excel https://www.excel-pratique.com/fr/telechargements/utilitaires/excel-formulaire-no438

pour le 1/

  • tu rentres le script dans l'éditeur de script (par le menu Extensions > App Script), remplace ce qui est proposé et enregistre
  • tu crées 2 onglets fictifs appelés d et f qui vont encadrer les feuilles à sommer
  • tu crées une feuille de résultats et tu mets la formule dans le même champ / la même position que les valeurs à sommer
  • tu mets justes =sum3D() ou mieux =sum3D($A$1) en mettant en A1 une case à cocher qui permettra de réactualiser, sa position dans la feuille de résultats suffit à comprendre quelles cellules sont à sommer dans les feuilles comprises entre d et f

mets nous au besoin un fichier bidon partagé pour peaufiner la maquette

Le 05/05/2022 à 21:29

tout fonctionne c'est parfait par contre est-ce que c'est possible qu'elle s'actualise toute seule ? Car la donnée qu'elle me donne n'est pas actualisée à chaque fois qu'un des employé rajoute une donnée, et je voudrais que cela s'actualise tout le temps.

Le 06/05/2022 à 04:17

Il faut alors détecter le changement par onEdit et le répercuter.

Cela fonctionne que si les données sont introduites manuellement une par une

Il faut définir le nom de la feuille de synthèse

  const feuille = "synthèse"
function onEdit(e) {
  const feuille = "synthèse"
  var f = e.source.getActiveSheet()
  var r = e.source.getActiveRange()
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  var formule = ss.getSheetByName(feuille).getRange(r.getA1Notation()).getFormula()
  if(formule.includes('=sum3D')){
    ss.getSheetByName(feuille).getRange(r.getA1Notation()).setValue(0)
    SpreadsheetApp.flush()
    ss.getSheetByName(feuille).getRange(r.getA1Notation()).setFormula(formule)
  }
}

si dans l'ensemble, le calcul est pénalisant, j'ai une autre méthode pour les grandes quantités de données ... peux-tu me dire combien tu as de cellules concernées par feuille ?

Le 07/05/2022 à 18:49

Bah chaque calcul compte une cellule par employé, et j'ai 12 calculs en tout. Donc 12 cellules concernées par feuilles mais dans 12 calculs différents.

Et si tu peux me dire où est-ce qu'il faut que je copie colle chaque truc que tu m'envoi, Merci

Le 07/05/2022 à 19:08

Fais menu extension >> app script, et remplace ce qui est proposé par les scripts ci-dessus, puis enregistre

ferme ton fichier et ouvre a nouveau

partage éventuellement un fichier test

Le 07/05/2022 à 21:51

Euh je sais pas si je l'ai mal copier coller mais cela ne marche pas ou alors il faut m'expliquer si je dois faire autre chose. Je te partage mon fichier ce sera plus simple et si jamais je suis débutant vraiment xD https://docs.google.com/spreadsheets/d/1zcv4pZBi0NLmX-KWqDfIuDoLcbgUmmeIIuB0n86MCPM/edit?usp=sharing

Le 08/05/2022 à 04:06

la somme fonctionne, parfait !

par contre l'automatisme ne fonctionne pas avec onEdit (même si on remplace synthèse par calcul puisque c'est la feuille qui comporte les formules en sum3D) car on est dans le cas de figure ci-dessous

Steelson a écrit :

Il faut alors détecter le changement par onEdit et le répercuter.

Cela fonctionne que si les données sont introduites manuellement une par une

en effet, onEdit ne détectera que les données modifiées manuellement dans les zones E7, H7, etc.

donc onEdit ne sert pas et il faut jouer sur le rafraichissement en cliquant sur A1

je vais te proposer une autre solution qui va résoudre ce problème, tu choisiras ensuite laquelle tu mets en œuvre, elle permettra d'écrire automatiquement ces formules

Vectah a écrit :

les données de chaque feuilles sont reprise dans une autre feuille avec une formule de ce type :
='Lyam Roux'!W7+'Jesus Martinez'!W7+'Toto Riina'!W7
Est-il possible de faire en sorte que dès que je créer une nouvelle fiche elle soit rajouter dans la commande et à l'inverse dès que j'en supprime une elle soit enlever de la commande.

Le 08/05/2022 à 07:39

Autre version

on oublie tout ce qui a été fait sauf le fait d'avoir cette feuille calcul avec la case à cocher en A1

image

ce script va écrire les formules dans les cellules ayant déjà une formule en prenant en compte toutes les feuilles entre d et f

il suffit de cliquer sur A1 pour réactualiser

function onEdit(e) {
  var f = e.source.getActiveSheet()
  var r = e.source.getActiveRange()
  if (f.getName()=='calcul' && r.getA1Notation()=='A1' && r.getValue()==true){
    actualiserFormules()
    r.setValue(!r.getValue())
  }
}
function actualiserFormules() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var calcul = ss.getSheetByName('calcul')
  var d = ss.getSheetByName('d').getIndex()
  var f = ss.getSheetByName('f').getIndex()
  var base = '='
  ss.getSheets().forEach(sh => {
    if (sh.getIndex() > d && sh.getIndex() < f) {
      base += `+'${sh.getName()}'!♦♣`
    }
  })
  calcul.getRange(1, 1, calcul.getLastRow(), calcul.getLastColumn()).getFormulas().forEach((r, i) => {
    r.forEach((c, j) => {
      if (c != '') {
        var adresse = columnToLetter(+j + 1) + (+i + 1)
        calcul.getRange(adresse).setFormula(base.replace(/♦♣/g, adresse))
      }
    })
  })
}
function columnToLetter(column) {
  var temp, letter = '';
  while (column > 0) {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

et pour démarrer de rien, il suffirait de mettre une formule bidon genre =1 dans une cellule !

Rechercher des sujets similaires à "mise jour automatique formule"