查找和替换列中的值 [英] Find and replace values from column

问题描述

我有一个包含五个不同工作表的文档.这些工作表包含五列的表格，每个表格超过400行.我正在尝试用其他文本字符串替换在这些表中找到的特定文本字符串.为此，我用另一个表($ replacement-table)创建了另一个工作表，其中包含我想在一个列中替换的字符串和我想在另一列中替换为它们的字符串.替换的字符串数量超过500，而且我不确定它们是否都出现在其他表中.

I have a document consisting of five different worksheets. These sheets contain tables of five columns and in excess of 400 rows each. I am trying to replace specific text strings found in these tables with other text strings. To this end, I've created another sheet with another table ($replacement-table), containing the strings I want to replace in one column and the strings I want to replace them with in another column. The number of strings to be replaced in excess of 500, and I'm not certain that they all crop up somewhere in the other sheets.

虽然我可以手动进行操作，但我想提出一种自动进行操作的方法.因此，我正在寻找一个脚本，该脚本查找$ replacement-table的A2中的值是否出现在文档中的其他任何位置，如果是，则将其替换为$ replacement-table的B2中的值. A3和B3，A4和B4等也是如此.

While I could do it manually, I'd rather come up with a way to automate it. Therefore, I'm looking for a script that looks up whether the value in A2 of $replacement-table shows up anywhere else in the document, and if so, replaces it with the value found in B2 of $replacement-table. The same goes for A3 and B3, A4 and B4, and so on and so forth.

可悲的是，我什至没有起点，因为我对告诉Google表格要执行的操作所需的语法或功能都没有经验.关于如何实现我的目标(将在五个不同表中找到的广泛选择的字符串替换为在特定表中排列的相应字符串)的任何想法(甚至是显而易见的/即插即用的解决方案?)?

Sadly, I don't even have a starting point, as I'm not experienced in either the syntax or the functions required to tell Google Sheets what I want it to do. Any ideas (or even obvious/plug-and-play solutions?) on how to achieve my goal of replacing a wide selection of strings found in five different tables with the corresponding strings arranged in a certain table?

推荐答案

下面是一个可能有帮助的简单示例.

Here's a simple example that might help.

function replMyText(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName('replacements');
  var rgtxt=sh.getRange('A1:A7');//text to replace
  var rgrep=sh.getRange('D1:E5');//replacement table
  var txtA=rgtxt.getValues();
  var repA=rgrep.getValues();
  for(var i=0;i<txtA.length;i++){
    for(var j=0;j<repA.length;j++){
      if(txtA[i][0]==repA[j][0]){
        txtA[i][0]=repA[j][1];
      }
    }
  }
  rgtxt.setValues(txtA);
}

这是我的测试纸的图像:

Here's an image of my test sheet:

这篇关于查找和替换列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！