如何使用包含VLOOKUP公式的IMPORTRANGE链接各种Google电子表格而不返回＃N / A？ [英] How to link various Google spreadsheets using IMPORTRANGEs that contain VLOOKUP formulas without getting #N/A returned?

问题描述

问题：使用IMPORTRANGE从不同电子表格中提取数据时，原始电子表格中包含VLOOKUP的表单中的单元格经常（但不总是）返回＃N / A（错误：未在VLOOKUP评估中找到值'xxx'）。在原始工作表中，公式计算正确并显示一个值。这种情况并不总是会发生，有时它确实会拉到合适的价位。

意图：

来自不同电子表格的数据，将它们结合到不同的电子表格中并对它们进行一些计算。然后将此计算电子表格（或多个计算电子表格）中的信息提取到报表电子表格中，以便进一步计算和格式化。

设置：

有几个源数据电子表格，例如dataspreadsheet1，dataspreadsheet2和dataspreadsheet3。计算电子表格（calcspreadsheet）被创建，在每个数据电子表格中创建sheet1的副本，并分别命名这些工作表datasheet1，datasheet2和datasheet3。用于此的IMPORTRANGE语句创建如下：importrange（+ VLOOKUP（dataspreadsheet1，filelist！A1：C1000,3，FALSE），sheet1！a1：Z1000）其中
filelist！A1：C1000是计算表中包含名称，类型和ID的工作表。

这些工作表数据表1-3中的值随后将用于另一工作表calcsheet1在电子表格中。主要目标是从3个数据表添加每日数据，但这些数据表并不都具有同一行上的相同数据，因此再次使用VLOOKUP来确保日期的添加使用该日期的行datasheet1-3无论其行号。例如。 VLOOKUP（$ A11，'datasheet1'！$ A：$ P，4）+ VLOOKUP（$ A11，'datasheet2'！$ A：$ P，4）+ VLOOKUP（$ A11，'datasheet3'！$ A：$ P ，4）其中列A是所有表中的日期列。

这似乎工作正常，虽然打开calccalreadsheet它可能需要很长时间才能通过更新，在此期间会显示大量的＃N / A。

问题出现在报告表格被创建时，反过来使用IMPORTRANGE调用来从calcsheet1中获取信息以便能够使用它。这通常但并非总是在一开始就导致问题状态。在这个报告电子表格中的IMPORTRANGE调用的生成方式与电子表格中的IMPORTRANGE调用类似：= importrange（+ VLOOKUP（calc！B1，sheetcodes！A1：C3000,3，FALSE），sheet1！a1：Z1000）calc！ B1包含源电子表格的名称（在这个计算中将是'calcspreadsheet'和sheetcodes！A1：C3000再次包含名称，类型和ID在相应列中的工作表列表

解决方法我尝试过：

我注意到IMPORTRANGE在不包含VLOOKUP的单元格上效果更好所以我尝试复制calcsheet的内容到计算表中的另一个工作表（称为出口单，但只有那里的值，而不是公式），然后在此出口单上使用IMPORTRANGE。 （）;
//获取活动电子表格和活动工作表= ss.getSheetByName（stream）;
// sh eet.activate（）;

var source = SpreadsheetApp.getActiveSpreadsheet（）。getSheetByName（calcsheet）;
var sourceDataRange = source.getDataRange（）;
var sourceSheetValues = sourceDataRange.getValues（）;
var sourceRows = sourceDataRange.getNumRows（）;
var sourceColumns = sourceDataRange.getNumColumns（）;

var destination = SpreadsheetApp.getActiveSpreadsheet（）;
SpreadsheetApp.setActiveSheet（destination.setActiveSheet（exportsheet））;
destination.getDataRange（）。offset（0，0，sourceRows，sourceColumns）.setValues（sourceSheetValues）;
}

这看起来很有效，但不幸的是复制脚本用于复制值Calcsheet into exportsheet现在显示了相同的行为，它有时会工作，有时会给＃N / A，所以给我留下同样的问题。

我的问题：

我读过各种类似的问题和回应，提到这个功能是脾气暴躁或有错误。其他人表示在IMPORTRANGE中不可能使用动态引用。鉴于它有时会起作用，有时候我不认为这个函数本身可能是正确的，但是在设置中会有同步或超时问题。

 我如何设置上述功能。无论是使用IMPORTRANGE还是VLOOKUP都可以进行一些更改/添加，或者以与开始时不同的方式构建。 
  

解决方案

当我在Vlookup中遇到这个问题时，我把vlookup包装在一个IF中，并测试＃N / A。

试试这样：= IF（ISNA（VLOOKUP（...））， VLOOKUP（...））

The problem:

When using IMPORTRANGE to pull data from a different spreadsheet, cells in the origin spreadsheet that contain a formule containing VLOOKUP often (but not always) return #N/A (ERROR: Did not find value 'xxx' in VLOOKUP evaluation). In the origin sheet the formulas are calculated correctly and showing a value though. This does not always happen, sometimes it does pull in the proper value.

The intent:

To take data from different spreadsheets, combine them in a different spreadsheet and do some calculations on them. And then pull info from this calculation spreadsheet (or from multiple calculation spreadsheets) into a reporting spreadsheet for some further calculations and formatting.

The set-up:

There are several source data spreadsheets,say dataspreadsheet1, dataspreadsheet2 and dataspreadsheet3. A calculation spreadsheet (calcspreadsheet) is created that creates a copy of sheet1 in each of the data spreadsheets and names these sheets datasheet1, datasheet2 and datasheet3 respectively. The IMPORTRANGE statement used for this is created as follows: importrange(+VLOOKUP("dataspreadsheet1",filelist!A1:C1000,3,FALSE),"sheet1!a1:Z1000") where filelist!A1:C1000 is a sheet in calcspreadsheet that contains Name, Type and Id in respective columns.

The values in each of these sheets datasheet1-3 are then used for calculations in another sheet, calcsheet1 in the calcspreadsheet. As the main goal of this is to add up daily values from the 3 dataspreadsheets, but those sourcesheets do not all have the same data on the same line a VLOOKUP is used again to make sure additions for a date use the line for the date in datasheet1-3 regardless of its row number. E.g. VLOOKUP($A11,'datasheet1'!$A:$P,4) + VLOOKUP($A11,'datasheet2'!$A:$P,4) + VLOOKUP($A11,'datasheet3'!$A:$P,4) where column A is the date column in all sheets.

This appears to work fine, although upon opening calcspreadsheet it can take a long time for it to go through an update, during which time lots of #N/A's are displayed. Eventually it comes right though.

The problem arise when a reportspreadsheet is created that in turn used an IMPORTRANGE call to pull the info from calcsheet1 in order to be able to work with it. This often, but not always, results in the problem states at the start. The IMPORTRANGE call in this reportspreadsheet is generated in a similar way as that in the calcspreadsheet: =importrange(+VLOOKUP(calc!B1,sheetcodes!A1:C3000,3,FALSE),"sheet1!a1:Z1000") where calc!B1 contains the name of the source spreadsheet (in this calc that would be 'calcspreadsheet' and sheetcodes!A1:C3000 again contains a list of sheets with Name, Type and Id in respective columns

A work-around I tried:

What I did notice that IMPORTRANGE works better on cells that do not contain VLOOKUP So I tried to copy the content of calcsheet to another sheet in calcspreadsheet, called exportsheet but having only the values in there, not formulas and then use IMPORTRANGE on this exportsheet. The copy script used is as follows:

function exportPrep() {
  // Get the active spreadsheet and the active sheet
  //var ss = SpreadsheetApp.getActiveSpreadsheet();
  //var sheet = ss.getSheetByName("stream");
  //sheet.activate();

  var source = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("calcsheet");
  var sourceDataRange = source.getDataRange();
  var sourceSheetValues = sourceDataRange.getValues();
  var sourceRows = sourceDataRange.getNumRows();
  var sourceColumns = sourceDataRange.getNumColumns();

  var destination = SpreadsheetApp.getActiveSpreadsheet();
  SpreadsheetApp.setActiveSheet(destination.setActiveSheet("exportsheet"));
  destination.getDataRange().offset(0, 0, sourceRows, sourceColumns).setValues(sourceSheetValues);
      }

This seemed to work, but unfortunately the copy script used to copy the value of calcsheet into exportsheet now showed the same behaviour, it would sometimes work and sometimes give the #N/A and so leaves me with the same problem.

My questions:

I've read various posts with similar issues and responses that mentioned this function was temperamental or had a bug. Other stated it is not possible to use dynamic references in IMPORTRANGE. Given that it sometimes works and sometimes not I suspect the function itself might be correct but that there are sync or time-out issues in the set-up.

How can I set up the above functionality. Either with the use of IMPORTRANGE and VLOOKUP at all with some changes/additions, or built in a different way from the get-go. 

解决方案

So I've not done this with importrange but I when I have this issue with Vlookup I wrap the vlookup in an IF and test for the #N/A.
Try something like: =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

这篇关于如何使用包含VLOOKUP公式的IMPORTRANGE链接各种Google电子表格而不返回＃N / A？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！