查找单元格匹配值并返回行号 [英] Find Cell Matching Value And Return Rownumber

问题描述

员工表在单元格C2中包含员工的姓名.数据表上的员工姓名也应在B3:B153范围内.

The employee sheet contains the name of the employee in cell C2. The name of the employee should also be on the data sheet in the range B3:B153.

如何获取数据表中与员工姓名匹配的单元格的行号?

How can I get the rownumber of the cell on the data sheet that matches the employee name?

我尝试了以下脚本，但似乎不起作用.

I tried the following script but it doesn't seem to work.

  var Sheet = SpreadsheetApp.getActive();
  var Employeesheet = Sheet.getSheetByName('Employee')
  var DataSheet = Sheet.getSheetByName('Data');
  var Column = Sheet.getRange(3,2,151,1);
  var Values = column.getValues(); 
  var Row = 0;

  while ( Values[Row] && Values[Row][0] !=(EmployeeSheet.getRange(2,3,1,1).getValue()) ) {
    Row++;
  }

  if ( Values[Row][0] === (EmployeeSheet.getRange(2,3,1,1).getValue()) ) 
    return Row+1;
  else 
    return -1;

  }

推荐答案

此处是代码

function rowOfEmployee(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var employeeName = sheet.getRange("C2").getValue();
  for(var i = 0; i<data.length;i++){
    if(data[i][1] == employeeName){ //[1] because column B
      Logger.log((i+1))
      return i+1;
    }
  }
}

当您要执行这种查找时，最好使用sheet.getDataRange().getValues()检索数据，因为在这种情况下，您将以值表的形式获取数据，因此速度更快.实际上，当您使用标准EmployeeSheet.getRange(2,3,1,1).getValue()时，您将检索到一个对象，该对象需要更多时间来处理，并且每次查询电子表格时都需要

When you want to perform this kind of lookup it is better to retrieve data with sheet.getDataRange().getValues() because in this case you will get data as a table of values this is faster. When you use the standard EmployeeSheet.getRange(2,3,1,1).getValue() in fact you retrieve an object which need more time to be processed and each time you query the spreadsheet.

在我的示例中，我只执行一次查询以检索所有数据，而不是执行每次查询以检索一个数据.

In my exemple I made only one query to retrieve all data instead n query to retrieve one data each time.

史蒂芬(Stéphane)

Stéphane

这篇关于查找单元格匹配值并返回行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！