Additionner les valeurs de cellules de même couleur
Bonjour à tous.tes,
J'aimerai tenir un tableau pour compter mes heures de travail en fonction du contrat de travail.
Tout fonctionne bien, les mises en forme conditionnelles, les additions d'heures, les calculs de paie par jour/mois.
J'ai trouvé sur le net des formules personnalisées pour me permettre de compter le nombre de jour travailler en fonction du contrat (Intermittent, CDD et CDI) et additionner le nombre d'heures et la paie total en fonction du contrat.
Le compte du nombre de jour fonctionne correctement, le calcul se fait automatiquement à la rentrée des valeurs et au changement de couleur des cellules.
En revanche pour ce qui est de l'addition du nombre d'heures et du total de la paie ça ne fonctionne pas :
Le calcul s'effectue pour la paie mais ne s'acctualise pas sans retaper la formule dans la case. Et l'addition du nombre d'heure ne fonctionne tout simplement pas.
Je peux vous partager ma feuille de test si vous souhaiter tester.
Bonne journée !
En espérant recevoir votre aide.
PS : Voici les 2 formules personnalisées que j'utilise :
-CompteCouleurs :
function CompteCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();
var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();
var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
var color = colorCell.getBackground();
var count = 0;
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color )
count=count+1;
return count;
};-SommeCouleurs
/**
* Permet de sommer le contenu des cellules de même couleur.
* Pour réinitialiser les fonctions changer une valeur dans une cellule de la plage de calcul
* @constructor
* @param {$A$1:$E$10} plage - plage à calculer.
* @param {E1} couleur ref - cellule dont le background est la référence de couleur.
* @return La somme des cellules de même couleur.
* @customfunction
*/
function SommeCouleurs(plage,couleur) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = activeRange.getSheet();
var formule = activeRange.getFormula();
//permet de trouver dans la formule la plage et de supprimer les autres caractères
var laplage = formule.match(/\((.*)\;/).pop();
var range = activeSheet.getRange(laplage);
var bg = range.getBackgrounds();
var values = range.getValues();
//permet de trouver dans la formule la cellule de référence couleur et de supprimer les autres caractères
var lacouleur = formule.match(/\;(.*)\)/).pop();
var colorCell = activeSheet.getRange(lacouleur);
var color = colorCell.getBackground();
//var color ="#ff0000";
var total = 0;
//nécessite la fonction isTypeNumber pour ne pas utiliser les cellules alphanumériques
for(var i=0;i<bg.length;i++)
for(var j=0;j<bg[0].length;j++)
if( bg[i][j] == color && isTypeNumber(values[i][j]) )
total=total+(values[i][j]*1);
return total;
};
function isTypeNumber(arg) {
return typeof arg == 'number';
}Bonjour,
Les formules ne sont actualisées qu'en cas de changement de valeur, pas en cas de changement de couleur. Il faut dans ce cas ajouter un paramètre, genre case à cocher), fictif car pas pris en compte dans le script, mais qui a l'avantage de déclencher le recalcul.
Mais dans ce cas, le formules de type var laplage = formule.match(/\((.*)\;/).pop(); ne fonctionnent plus !
J'ai adapté et simplifié en ce qui me concerne
- la formule est dans une cellule colorée de la même couleur que les cellules à compter/additionner
- les cellules ne sont pas forcément dans la même feuille que le résultat du calul
- la formule fait appel à une case à cocher pour actualiser
par exemple
=sommeSelonCouleur( 'Feuille 4'!A2:B7 ; $Z$1 )où Z1 est une case à cocher
function sommeSelonCouleur(range) {
var r = SpreadsheetApp.getActiveRange();
var couleur = r.getBackgrounds();
var total = 0;
var adresses = r.getFormula().match(/(?<=\().*(?=\))/g)[0].split(/[;|,]/)
//var adresses = r.getFormula().match(/\((.*)\)/).pop().split(/[;|,]/)
for (var i = 0; i < adresses.length - 1 ; i++) {
try {
var f = SpreadsheetApp.getSheetByName(adresses[i].split('!')[0].replace("'", ""));
var adresse = adresses[i].split('!')[1].trim();
}
catch (e) {
var f = SpreadsheetApp.getActiveSheet();
var adresse = adresses[i].trim()
}
var couleurs = f.getRange(adresse).getBackgrounds();
var valeurs = f.getRange(adresse).getValues();
for (var j = 0; j < couleurs.length; j++)
for (var k = 0; k < couleurs[i].length; k++)
if (couleurs[j][k] == couleur)
if ((typeof valeurs[j][k]) == 'number')
total += valeurs[j][k];
}
return total;
};
function nbSelonCouleur(range) {
var r = SpreadsheetApp.getActiveRange();
var adresse = r.getFormula().match(/(?<=\().*(?=;)/g)
try{
var f = SpreadsheetApp.getSheetByName(adresse.split('!')[0].replace("'",""));
var adresse = adresse.split('!')[1];
}
catch(e){
var f = SpreadsheetApp.getActiveSheet();
}
var couleurs = f.getRange(adresse).getBackgrounds();
var couleur = r.getBackgrounds();
var nb = 0;
for (var i = 0; i < couleurs.length; i++)
for (var k = 0; k < couleurs[i].length; k++)
if ( couleurs[i][k] == couleur )
nb++;
return nb;
};Bonjour Steelson !
Merci beaucoup pour ton aide déjà.
J'ai fait la modif à l'instant en revenant du taf, la case à cocher n'est pas trop problématique, c'est même plutôt satisfaisant de voir toute les cellules se remplirent d'un coup.
Ca marche très bien pour les cellules qui nécessite NB, en revanche pour la SOMME, seul les cellules comptant les devises (ici €) fonctionnent et pas celles sensées compter les heures.
Voici comment est sont noté les cellules concernés par ces fonctions :
Les cellules pour le total heures journée sont : fx(C1)=A1-B1 formaté en heure [h]h[mm] où A1=l'heure de début ; B1=l'heure de fin les deux formatés en durée [h]h[mm]
Les cellules pour les devises ne sont pas des nombres fixes c'est le résultat de : fx(E1) =(C1*24)*D1 formaté en € où C1=le total d'heures journée ([h]h[mm]) ; D1=le taux horaires formaté en €
Dis moi si tu trouves ce qui peut faire que la fonction ne fonctionne pas sur les cellules de calcul d'heure.
En tout cas merci, tu as déjà résolu une partie de mon problème. N'hésite pas à me dire si tu veux une copie de mon tableau pour voir par toi même
Peux-tu partager une copie simplifiée de ton fichier ? https://www.sheets-pratique.com/fr/cours/partage
Ah ben merci pour cette trouvaille ! en effet avec les heures, c'est tellement bien fait qu'il faut ruser ...
Dans l'urgence, j'ai fait une fonction spéciale ... mais je vais voir comment intégrer cela.
La raison est qu'une date ce n'est pas un nombre en GSheets (contrairement à excel), mais un objet.
function SommeHeuresSelonCouleur(range) {
var r = SpreadsheetApp.getActiveRange();
var couleur = r.getBackgrounds();
var total = 0;
var adresses = r.getFormula().match(/(?<=\().*(?=\))/g)[0].split(/[;|,]/)
for (var i = 0; i < adresses.length - 1 ; i++) {
try {
var f = SpreadsheetApp.getSheetByName(adresses[i].split('!')[0].replace("'", ""));
var adresse = adresses[i].split('!')[1].trim();
}
catch (e) {
var f = SpreadsheetApp.getActiveSheet();
var adresse = adresses[i].trim()
}
var couleurs = f.getRange(adresse).getBackgrounds();
var valeurs = f.getRange(adresse).getValues();
for (var j = 0; j < couleurs.length; j++)
for (var k = 0; k < couleurs[i].length; k++)
if (couleurs[j][k] == couleur)
total += Number(valeurs[j][k].getHours()+valeurs[j][k].getMinutes()/60);
}
return total/24;
};Ah super ! Merci beaucoup !
Mais il n'y a aucune urgences !
Je penses ajouter encore quelques trucs, merci pour ton aide, il est pas exclu que je redemande quelques conseils à l'avenir