J'ai une question Google Sheets sur laquelle j'espérais que quelqu'un pourrait m'aider.
J'ai une liste d'environ 200 mots-clés qui ressemble à ceux ci-dessous:
**List 2** Cost Italy City trip April 1 Italy City trip June 5 Next week Italy Roundtrip 4 Italy Holiday next week 1 Hungary City holiday trip 9 .... ....
Et j'ai ensuite une autre liste avec des mots-clés mélangés avec environ 1 million de lignes. Les mots-clés de cette liste ne correspondent pas exactement à la première liste. Ce que je dois faire, c'est rechercher les mots-clés de la liste 1 (ci-dessus) dans la liste 2 (ci-dessous) et additionner toutes les valeurs de coût correspondantes. Comme vous pouvez le voir dans la liste ci-dessous, les mots-clés de la liste 1 sont dans la deuxième liste mais avec d'autres mots-clés autour d'eux. Par exemple, j'ai besoin d'une formule qui recherchera "Voyage en ville en Italie" dans la liste 1, dans la liste 2 et additionnera le coût lorsque ce mot-clé apparaît. Dans ce cas, ce serait 6 au total. Ajout du coût de "Italy City trip April" et "Italy City trip June" ensemble.
**List 1** Italy City trip Italy Roundtrip Italy Holiday Hungary City trip Czechia City trip Croatia Montenegro Roundtrip .... ....
J'espère que cela a du sens.
Toute aide serait utile être grandement apprécié
3 Réponses :
try:
=ARRAYFORMULA(QUERY({IFNA(REGEXEXTRACT(PROPER(C1:C),
TEXTJOIN("|", 1, SORT(PROPER(A1:A), 1, 0)))), D1:D},
"select Col1,sum(Col2)
where Col1 is not null
group by Col1
label sum(Col2)''", 0))
"essayez" - le euphémisme de l'année.
J'ai aussi celui-ci, mais il est un peu sensible à la casse
function myFunction() {
var ss = SpreadsheetApp.getActive();
var sheet1 = ss.getSheets()[0];
var sheet2 = ss.getSheets()[1];
var valuesSheet1 = sheet1.getRange(2,1, (sheet1.getLastRow()-1), sheet1.getLastColumn()).getValues();
var valuesCol1Sheet1 = valuesSheet1.map(function(r){return r[0]});
var valuesCol2Sheet1 = valuesSheet1.map(function(r){return r[1]});
Logger.log(valuesCol2Sheet1);
var valuesSheet2 = sheet2.getRange(2,1, (sheet2.getLastRow()-1)).getValues();
var valuesCol1Sheet2 = valuesSheet2.map(function(r){return r[0]});
for (var i = 0; i<= valuesCol1Sheet2.length-1; i++){
var price = 0;
valuesCol1Sheet1.forEach(function(elt,index){
var position = elt.toLowerCase().indexOf(valuesCol1Sheet2[i].toLowerCase());
if(position >-1){
price = price + valuesCol2Sheet1[index];
}
});
sheet2.getRange((i+2),2).setValue(price);
};
}
var valuesSheet2 = sheet2.getRange (2,1, (sheet1.getLastRow () - 1)) . Cela ne devrait-il pas être var valuesSheet2 = sheet2.getRange (2,1, (sheet2.getLastRow () - 1))
Ouais mon erreur
J'obtiens TypeError: Impossible d'appeler la méthode "toLowerCase" de undefined. sur var position = elt.toLowerCase (). IndexOf (valuesCol1Sheet2 [i] .toLowerCase ()) ; < / code>
Quelle est la valeur de votre SS1?
Fonctionne correctement lorsque for (var i = 0; i <= valuesCol1Sheet2.length-1; i ++) {. Remarque longueur moins 1; c'était ce qui causait le hichup. J'aime vraiment cette réponse; vous avez une seule boucle alors que la mienne a deux boucles;
Ouais mon mauvais, comme j'ai commencé à obtenir de la valeur de la deuxième ligne, devrait supprimer 1. Si je voudrais commencer par la troisième, devrait supprimer 2 ... Mais merci pour le commentaire, appréciez-le!
Comme je l'ai dit, j'aime beaucoup l'élégance de ce scénario. Mais quand j'ai comparé les temps d'exécution, cela prend "0,961 seconde", et le mien "so5864274503" prend "0,529 seconde". J'ai été surpris, et c'est une grande différence pour un OP qui a un million de lignes à traiter. ;-)
Oui et la raison est simple. Chaque fois que mon script trouve une correspondance, il pousse le prix dans la feuille. Pour votre script, vous devez attendre la fin des 2 boucles, puis ajouter les prix une fois. Pourquoi j'ai choisi cette façon? C'est juste en cas de plantage du script, vous avez encore des prix à comparer à vous qui ne pouvez rien obtenir ... C'est plus un choix de sécurité disons.
Vous voulez déterminer si les mots-clés d'une liste (liste n ° 1) peuvent être trouvés dans une autre liste (liste n ° 2).
La liste n ° 2 est longue de 1 000 000 lignes, je vous recommande donc de segmenter la liste pour que l'exécution les temps ne sont pas dépassés. C'est quelque chose que vous pourrez établir par essais et erreurs.
La solution est d'utiliser la méthode javascript indexOf.
Paraphrasant de w3schools : indexOf () renvoie la position de la première occurrence d'une valeur spécifiée dans une chaîne. Si la valeur n'est pas trouvée, elle renvoie -1. Donc, tester if (idx! = - 1) { ne renverra que les valeurs de la liste n ° 1 qui ont été trouvées dans la liste n ° 2. Remarque: La méthode indexOf () est sensible à la casse.
function so5864274503() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var srcname = "source";
var tgtname = "target";
var sourceSheet = ss.getSheetByName(srcname);
var targetSheet = ss.getSheetByName(tgtname);
// get the source list
var sourceLR = sourceSheet.getLastRow();
var srcData = sourceSheet.getRange(1,1,sourceLR).getValues();
//get the target list
var targetLR = targetSheet.getLastRow();
var tgtlist = targetSheet.getRange(1,1,targetLR,2).getValues();
var totalcostvalues = [];
// start looping through the keywords (list 1)
for (var s = 0;s<srcData.length;s++){
var totalcost = 0;
var value = srcData[s][0]
// start looping through the strings (List 2)
for (var i=0;i<tgtlist.length;i++){
// set cost to zero
var cumcost = 0;
// use indexOf to test if keyword is in the string
var idx = tgtlist[i][0].indexOf(value);
// value of -1 = no match, value >-1 indicates posuton in the string where the key word was found
if (idx !=-1){
var cost = tgtlist[i][1]
cumcost = cumcost + cost;
totalcost = totalcost+cost
}
}//end of loop - list2
//Logger.log("DEBUG: Summary: "+value+", totalcost = "+totalcost)
totalcostvalues.push([totalcost])
}// end of loop - list1
//Logger.log(totalcostvalues); //DEBUG
sourceSheet.getRange(1,2,sourceLR).setValues(totalcostvalues);
}
"1 million de lignes" o_O
haha je pourrais le couper
"Coupe"! Périsse la pensée.