WPS宏模拟vlookup函数功能

第一版：不指定返回值，比对500行左右，运行2秒

function JIn26() {
    //记录时间
    console.log("当前时间：‌" + new Date().toLocaleString());
    
    // InputBox方法可弹出框，用于交互
    var rst1 = InputBox("输入比对列的Sheet页及列号、‌<br>被比对列的Sheet页及列号用','隔开：‌");//    Sheet1,5,Sheet2,4
    var strArray = rst1.split(',');
    var sheet1 = strArray[0];
    var col1 = Number(strArray[1]);
    var sheet2 = strArray[2];
    var col2 = Number(strArray[3]);
    
    // 使用字典存储Sheet2页被比对列的数据，‌以提高查找效率
    var bijiaoDict = {};
    var sheet2Data = Application.Worksheets.Item(sheet2).Range(
        Cells(2, col2),
        Cells(Cells.Item(2, col2).End(xlDown).Row, col2)
    ).Value();
    for (let yy = 0; yy < sheet2Data.length; yy++) {
        bijiaoDict[sheet2Data[yy]] = true;
    }

    //定位到Sheet1页在用户输入的比较列旁边插入一列
    Application.Worksheets.Item(sheet1).Range(
        Cells(2, col1 + 1),
        Cells(Cells.Item(2, col1 + 1).End(xlDown).Row, col1 + 1)
    ).EntireColumn.Insert(xlShiftToRight, false);
    
    //定位到Sheet1页数据
    var sheet1Data = Application.Worksheets.Item(sheet1).Range(
        Cells(2, col1),
        Cells(Cells.Item(2, col1).End(xlDown).Row, col1)    
    ).Value();
    
    //遍历赋值
    for (let y1 = 2; y1 <= sheet1Data.length + 1; y1++) {
        var czz = sheet1Data[y1 - 2];
        if (bijiaoDict[czz]) {
            Application.Worksheets.Item(sheet1).Cells.Item(y1, col1 + 1).Value2 = czz;
        } else {
            Application.Worksheets.Item(sheet1).Cells.Item(y1, col1 + 1).Value2 = "未找到";
        }
    }
    console.log("结束时间：‌" + new Date().toLocaleString());
}

第二版：指定返回值，比对500行左右，运行40秒

 function JIn25()
{
 console.log("开始时间：‌" + new Date().toLocaleString());
 
 // InputBox方法可弹出框，用于交互
 var rst1 = InputBox("请输入比对列的Sheet页及列号、‌<br>被比对列的Sheet页及列号、返回值<br>的列号被用','隔开：‌");//    Sheet1,5,Sheet2,4,7    
    var strArray = rst1.split(',');
    var sheet1 = strArray[0];
    var col1 = Number(strArray[1]);
    var sheet2 = strArray[2];
    var col2 = Number(strArray[3]);
    var col3 = Number(strArray[4]);
    
 if(strArray.length!=5 || isNaN(col3) || col3==""){
     alert("输入错误！");
 }else{  

 //封装被比较列值的数组
 Application.Worksheets.Item(sheet2).Activate();
 var range1 = Range(Cells(2,col2),Cells(Cells.Item(2,col2).End(xlDown).Row,col2)).Value();
 var range2 = Range(Cells(2,col3),Cells(Cells.Item(2,col3).End(xlDown).Row,col3)).Value();
 var bijiao = new Array();
 
 for (let y = 0; y < range1.length; y++) {
     bijiao.push(range1[y]+":"+range2[y]);
 }
 
//Sheet1页比较列的旁边插入空白列
 Application.Worksheets.Item(sheet1).Activate();
 Range(Cells(2,Number(col1)+1),Cells(Cells.Item(2,Number(col1)+1).End(xlDown).Row,Number(col1)+1)).EntireColumn.Insert(xlShiftToRight, false);
 
 //Sheet1页比较列的值for循环
 var le = Sheets(sheet1).Cells.Item(2,col1).End(xlDown).Row;
   for (let y1 = 2; y1 <= le; y1++) {
     var czz = Sheets(sheet1).Cells.Item(y1,col1).Value2;
    
   //遍历比较
    for (let yy = 2; yy < bijiao.length+2; yy++) {
     var value = bijiao[yy-2];
     var value1 = value.split(':');
     if(czz==value1[0]){
       Application.Worksheets.Item(sheet1).Activate();
        var nubr = Number(col1)+1;
      Cells.Item(y1,nubr).Value2 = value1[1];
      break;
     }else{
       Application.Worksheets.Item(sheet1).Activate();
        var nub = Number(col1)+1;
      Cells.Item(y1,nub).Value2 = "#Name";
     }
    }
  }
  console.log("结束时间：‌" + new Date().toLocaleString());
  }
 }