Блог

Я работаю с Google forms и Google Sheets. Я пытаюсь создать сводную таблицу, которая будет автоматически обновляться по мере заполнения формы.

Я смог извлечь данные из других листов, используя функцию ФИЛЬТРА. Теперь я хочу добавить столбец, в котором отображается название страны, в отфильтрованный столбец. Я попытался использовать concatenate, но это сработало не так хорошо, как я надеялся. Может кто-нибудь помочь мне выяснить, как решить эту проблему.

Пожалуйста, смотрите Здесь пример проблемы.

Ну, это очень неэлегантный способ грубой силы, но я думаю, что это работает. Смотрите Решение-GK в вашем листе.

 =QUERY({
   {TRANSPOSE(SPLIT(REPT("Nigeria~",ROWS(UNIQUE(FILTER(NIGERIA!A:E,NIGERIA!C:C<TODAY(),NIGERIA!B:B="Charity Fundraiser")))),"~")),
                                         UNIQUE(FILTER(NIGERIA!A:E,NIGERIA!C:C<TODAY(),NIGERIA!B:B="Charity Fundraiser"))}; 
   {TRANSPOSE(SPLIT(REPT("Sierra Leone~",ROWS(UNIQUE(FILTER('SIERRA LEONE'!A:E,'SIERRA LEONE'!C:C<TODAY(),'SIERRA LEONE'!B:B="Charity Fundraiser")))),"~")),
                                              UNIQUE(FILTER('SIERRA LEONE'!A:E,'SIERRA LEONE'!C:C<TODAY(),'SIERRA LEONE'!B:B="Charity Fundraiser"))}},
  "select Col1,Col2,Col3,Col4, Col5 where Col2 is not null")
  

Я добавил жестко закодированный литерал названия страны, повторил его столько раз, сколько необходимо для сопоставления строк данных, и поместил его в первый столбец в существующем массиве данных. Я повторяю это для второго массива, который у вас есть для второй страны.

Я уверен, что есть гораздо более элегантные способы сделать это, поэтому мы посмотрим, что еще предлагается. Если у вас где-то в вашем листе был список названий ваших стран — ie. Нигерия и Сьерра-Леоне, возможно, многие другие — я уверен, что элегантным решением было бы перебирать эти имена, извлекая имя для построения объединенных диапазонов данных, а также добавляя имя в качестве текста для каждой строки.

Без необходимости в списке на листе, немного кода может найти все ваши имена вкладок и исключить те, которые не являются данными, например. Решение здесь и резюме, а все остальное обработать как данные.

Примечание: я не понимаю, нужны ли вам ваши UNIQUE утверждения, если только вы по какой-то причине не ожидаете дубликатов. Кроме того, внешний QUERY , похоже, не нужен — внутренний FILTERs , похоже, делает все, что вам нужно.

Вы можете сделать это с помощью пользовательской функции Apps Script.

Сначала откройте связанный скрипт, выбрав Сервис> Редактор сценариев, и скопируйте в скрипт следующие функции (проверьте встроенные комментарии для получения более подробной информации о коде):

 // Copyright 2020 Google LLC.
// SPDX-License-Identifier: Apache-2.0

function SUMMARIZE_FUNDRAISING_EVENTS(sheetNames, ...ranges) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames = sheetNames.split(","); // Comma-separated string to array of sheet names
  const filteredData = sheetNames.map(sheetName => { // Iterate through each sheet name
    const sheet = ss.getSheetByName(sheetName);
    if (sheet) { // Check if sheet exists with this name
      const sheetData = sheet.getRange(2,1,sheet.getLastRow()-1,4).getValues(); // Source sheet data
      const filteredData = sheetData.filter(rowData => {
        return rowData[1] === "Charity Fundraiser" amp;amp; rowData[2] < new Date()
      }); // Filter data according to date and type of event
      filteredData.forEach(filteredRow => filteredRow.unshift(sheetName)); // Add sheet name to filtered data
      return filteredData;
    }  
  }).flat();
  return filteredData;
}
  

После того, как он определен, вы можете использовать функцию SUMMARIZE_FUNDRAISING_EVENTS так же, как и любую встроенную функцию sheets. Эта функция будет принимать ряд параметров:

• Разделенная запятыми строка с именами листов, данные которых должны быть суммированы (не добавляйте пробелы после запятой или аналогичные).
• Разные диапазоны источников (в вашем случае, NIGERIA!A:E и 'SIERRA LEONE'!A:E ).

Оба эти способа необходимы, потому что, с одной стороны, указание исходных диапазонов в качестве параметров гарантирует, что функция выполняется и обновляет сводные данные каждый раз при редактировании исходных диапазонов, а с другой стороны, при передаче в качестве параметров эти исходные диапазоны не содержат информации об именах листов, который понадобится скрипту при возврате обобщенных данных.

Пример вызова функции:

Ссылка:

• Пользовательские функции в таблицах Google