Comment créer une chaîne de tableau INDIRECT de plusieurs références de feuille dans Google Sheets?

Si vous essayez de mettre à jour les données que votre requête recherche et que vous lui fournissez une chaîne, vous devez placer cette chaîne dans la fonction indirect (). Cela interprétera votre chaîne comme une référence de données et pointera votre requête () dans la bonne direction.

Donc, pour cela, vous auriez probablement

=QUERY(INDIRECT(I1),"Select *")

Si vous avez vraiment, vraiment besoin d'utiliser I1, essayez la formule suivante

=QUERY({INDIRECT(INDEX(SPLIT(I1,";"),1,1));INDIRECT(INDEX(SPLIT(I1,";"),1,2))},"Select *")

I2 a une formule qui renvoie cette valeur 'Semaine du 01/06'! R: P; 'Semaine du 13/01'! A: P . C'est une valeur de texte ayant deux adresses de plage séparées par un point-virgule.

La formule ci-dessus utilise SPLIT pour séparer chaque adresse, elle utilise INDEX pour prendre la partie correspondante à utiliser comme argument de INDIRECT qui renvoie un tableau de valeurs. Ces tableaux de valeurs sont utilisés pour construire un autre tableau de valeurs qui est utilisé comme premier argument de QUERY.

Les mecs qui ont copié-collé la fonction INDIRECT dans Google Sheets n'ont complètement pas compris le potentiel de celle-ci et n'ont donc fait aucun effort pour l'améliorer et couvrir la logique évidente qui est cruciale à l'ère de

en d'autres termes, INDIRECT ne peut pas prendre plus d'un tableau:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('Master Sheet');  
var src = sheet.getRange("A1");
var str = src.getValue(); 
var cell = sheet.getRange("C5"); 
cell.setFormula(str);
}

ni convertir une chaîne matricée en référence active, ce qui signifie que toute tentative de concaténation est également vaine:

=ARRAYFORMULA("=QUERY({"&TEXTJOIN("; ", 1, 
 IF(A3:A<>"", "'Week of "&LEFT(A3:A, 5)&"'!A1:D5", ))&
 "}, ""where Col1 is not null"", 1)")

le seul moyen possible est d'utiliser INDIRECT pour chaque terminer chaque plage comme:

=QUERY(
 {IFERROR(INDIRECT("Sheet1!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet2!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet3!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet4!A1:B5"), {"",""})}, 
 "where Col1 is not null", 0)

ce qui signifie que le mieux que vous puissiez faire est de préprogrammer votre tableau comme ceci si seulement une partie des feuilles / tabs est existant (ayons un scénario où seulement 2 feuilles sont créées sur un total de 4):

={INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}

donc, même si les noms de feuille sont prévisibles (ce qui ne le sont pas toujours ) préprogrammer plus de 100 feuilles comme cela serait douloureux (même s'il existe plusieurs façons sournoises d'écrire une telle formule en moins de 30 secondes)

une alternative serait d'utiliser un script pour convertir la chaîne et l'injecter comme la formule

A1 le ferait soit une formule qui traite une chaîne qui ressemble à une vraie formule:

=INDIRECT(MasterSheet!A1:A10)
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{Sheet1!A:B; Sheet2!A:B}")
————————————————————————————————————————————————————————————————————————————————————
={INDIRECT("Sheet1!A:B"; "Sheet2!A:B")}
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}")

un remplissage supplémentaire de A6: A développera la chaîne automatiquement

alors ce script prendra la chaîne de la cellule A1 et il la collera comme formule valide dans la cellule C5:

=INDIRECT("Sheet1!A:B"; "Sheet2!A:B")

bien sûr , le script peut être changé en déclencheur onOpen ou avec un nom personnalisé déclenché à partir du menu personnalisé ou via un bouton (cependant il n'est pas possible d'utiliser la fonction personnalisée comme formule directement)