Как связать различные таблицы Google с помощью IMPORTRANGE, которые содержат формулы VLOOKUP, без возврата #N/A?

Эта проблема:

При использовании IMPORTRANGE для извлечения данных из другой электронной таблицы ячейки в исходной электронной таблице, содержащие формулу, содержащую VLOOKUP, часто (но не всегда) возвращают #N/A (ОШИБКА: не найдено значение 'xxx' в оценке VLOOKUP). В исходном листе формулы рассчитаны правильно и показывают значение, хотя. Это не всегда происходит, иногда это приводит к правильному значению.

Намерение:

Чтобы взять данные из разных электронных таблиц, объедините их в другую электронную таблицу и выполните некоторые вычисления на них. А затем извлеките информацию из этой расчетной электронной таблицы (или из нескольких расчетных электронных таблиц) в отчетную электронную таблицу для дальнейших вычислений и форматирования.

Установка:

Существует несколько электронных таблиц исходных данных, например, dataspreadsheet1, dataspreadsheet2 и dataspreadsheet3. Создается электронная таблица вычислений (таблица калькуляции), которая создает копию листа 1 в каждой из электронных таблиц данных и присваивает этим листам имена листы 1, лист 2 и лист данных 3 соответственно. Оператор IMPORTRANGE, используемый для этого, создается следующим образом: importrange(+VLOOKUP("dataspreadsheet1",filelist!A1:C1000,3,FALSE),"sheet1! A1:Z1000"), где filelist! A1: C1000 - это лист в таблице калькуляции который содержит имя, тип и идентификатор в соответствующих столбцах.

Значения в каждом из этих листов datasheet1-3 затем используются для вычислений в другом листе, calcsheet1 в calcolidsheet. Поскольку основной целью этого является сложение ежедневных значений из 3 таблиц данных, но не все эти исходные таблицы имеют одинаковые данные в одной строке, VLOOKUP используется снова, чтобы убедиться, что добавления для даты используют строку для даты в Datasheet1-3 независимо от номера строки. Например, VLOOKUP($A11, "таблица данных1"!$A:$P,4) + VLOOKUP($A11, "таблица данных2"!$A:$P,4) + VLOOKUP($A11, "таблица данных3"! $ A: $ P, 4) где столбец A - столбец даты во всех листах.

Кажется, это работает нормально, хотя после открытия таблицы калькуляции обновление может занять много времени, в течение которого отображается множество # N / A. В конце концов, все идет как надо.

Проблема возникает, когда создается таблица отчетов, которая, в свою очередь, использовала вызов IMPORTRANGE, чтобы извлечь информацию из calcsheet1, чтобы иметь возможность работать с ней. Это часто, но не всегда, приводит к возникновению проблемных состояний в начале. Вызов IMPORTRANGE в этой электронной таблице отчетов генерируется аналогично вызову в калькуляционной таблице: =importrange(+VLOOKUP(calc!B1,sheetcodes!A1:C3000,3,FALSE),"sheet1! A1:Z1000"), где calc! B1 содержит имя исходной электронной таблицы (в данном калькуляре это будет "calcolvedsheet" и коды листов! A1: C3000 снова содержит список листов с именами, типами и идентификаторами в соответствующих столбцах

Обходной путь, который я попробовал:

То, что я заметил, что IMPORTRANGE лучше работает с ячейками, которые не содержат VLOOKUP. Поэтому я попытался скопировать содержимое таблицы calcsheet на другой лист в calcolvedsheet, называемый exportheet, но содержащий только значения, а не формулы, а затем использовать IMPORTRANGE на этом листе экспорта. Используемый сценарий копирования выглядит следующим образом:

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);
      }

Казалось, это сработало, но, к сожалению, сценарий копирования, использованный для копирования значения calcsheet в файл экспорта, теперь демонстрировал то же поведение, иногда работал и иногда давал # N / A, и поэтому оставлял меня с той же проблемой.

Мои вопросы:

Я читал различные посты с похожими проблемами и ответами, в которых упоминалось, что эта функция была темпераментной или имела ошибку. Другие заявили, что невозможно использовать динамические ссылки в IMPORTRANGE. Учитывая, что это иногда работает, а иногда нет, я подозреваю, что сама функция может быть правильной, но есть проблемы синхронизации или тайм-аута в настройке.

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.
Источник

1 ответ

Так что я не делал этого с помощью importrange, но когда я сталкиваюсь с этой проблемой с Vlookup, я оборачиваю vlookup в IF и проверяю #N/A.
Попробуйте что-то вроде: =IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

Источник

=IFERROR(VLOOKUP(B81,'KAM_Q3_OPPS STAGE_(H/I/L/M/N)'!$F$3:$G$111,2,False),)

Это лучший способ, который я нашел, и последние два значения [ , ) ] сделайте новый вывод, в данном случае ничего.

ЕСЛИ вы хотели 0, тогда вы бы сделали [ ,0) ]

Источник
Другие вопросы по тегам