функция для отображения номера строки, если ячейка в диапазоне совпадает с ключом

#google-apps-script #google-sheets

#google-приложения-скрипт #google-таблицы #google-apps-script #google-sheets

Вопрос:

У меня есть таблица, как показано ниже.

Существует ли какая-либо функция, которая возвращает список номеров строк, совпадающих с ключом, переданным в функцию? Пример, подобный приведенному ниже, вернул бы 2 и 6 в две ячейки.
function_to_listup(«панель», «A1:A6»)

Если нет какой-либо встроенной функции, я хотел бы достичь этого с помощью локальной функции, используя Google-app-script, если это возможно.

Ответ №1:

Можно использовать простую FILTER передачу номеров строк в качестве основного аргумента и условия в качестве второго аргумента:

 =FILTER(ROW(A1:A6),A1:A6="bar")

Если вы не хотите, чтобы отфильтрованные числа содержались в отдельном массиве / ячейках, вы могли бы использовать простой IF принудительный контекст массива с ARRAYFORMULA помощью.

 =ARRAYFORMULA(JOIN(",", IF(A1:A6="bar",ROW(A1:A6),)))

Ответ №2:

Шаблон 1:

В этом шаблоне используется встроенная функция.

Пример формулы:

 =QUERY({A1:A6,ARRAYFORMULA(ROW(A1:A6))},"select Col2 where Col1='bar'")

Введите диапазон A1:A6 и номера строк ARRAYFORMULA(ROW(A1:A6)) .
Когда значение A1:A6 равно bar , возвращается номер строки той же строки.

Результат:

Шаблон 2:

В этом шаблоне используется пользовательская функция, созданная Google Apps Script. В этом случае, пожалуйста, поместите =function_to_listup("bar", "A1:A6") в ячейку. Из вашего вопроса предполагается, что диапазон равен A1Notation.

Пример сценария:

 function function_to_listup(searchValue, a1Notation) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange(a1Notation);
  const offset = range.getRow();
  const values = range.getValues();
  return values.reduce((ar, [v], i) => {
    if (v == searchValue) ar.push(i   offset);
    return ar;
  }, []);
}

В этом случае результат совпадает с приведенным выше примером формулы.

Шаблон 3:

В этом шаблоне используется пользовательская функция, созданная Google Apps Script. Для этой ситуации используется текстовый поисковик. В этом случае, пожалуйста, поместите =function_to_listup("bar", "A1:A6") в ячейку. Из вашего вопроса предполагается, что диапазон равен A1Notation.

Пример сценария:

 function function_to_listup(searchValue, a1Notation) {
  return SpreadsheetApp
    .getActiveSheet()
    .getRange(a1Notation)
    .createTextFinder(searchValue)
    .matchEntireCell(true)
    .findAll()
    .reduce((ar, r) => ar.concat(r.getRow()), []);
}

В этом случае результат совпадает с приведенным выше примером формулы.