Alternative à l'utilisation d'ARRAYFORMULA avec QUERY ou INDEX

Question

Explication plus ciblée:

Ceci est la feuille de calcul https://docs.google.com/spreadsheets/d/1eMlf9QrI59mdOlUzQSzQherSXxcMbJq9iSyHIxKNaRM/edit?usp=sharing

Sur la feuille «6-7 Master 2020-21» de la colonne ES, chaque ligne doit avoir une valeur provenant de la feuille «SummaryCitizenship». Cette valeur se trouve dans la colonne E de la feuille «SummaryCitizenship». La ligne de la feuille "SummaryCitizenship" dont provient la valeur doit correspondre aux valeurs suivantes. La colonne B ("SummaryCitizenship") de cette ligne correspond à la colonne B ("6-7 Master 2020-21") AND Column C = 1 AND Column D = Responsabilité personnelle

Je pourrais mettre cette formule dans chaque cellule de la colonne ES ("6-7 Master 2020-21")

=QUERY(SummaryCitizenship!A1:E12,"select E where B = '"&B2&"' and C = 1 and D = 'Personal Responsibility' ",0)

et cela fonctionne, mais les informations de la colonne B ("6-7 Master 2020-21") sont dynamiques et changeront plusieurs fois par jour, ajoutant principalement de nouvelles lignes à la feuille. Cela signifie que j'ai besoin que la formule ne soit pas dans chaque cellule de ES, mais plutôt dans la cellule ES1 ou ES2 et qu'elle affecte le reste de la feuille comme le ferait un ARRAYFORMULA.

J'ai aussi essayé

=INDEX(FILTER(SummaryCitizenship!$A$2:$E,SummaryCitizenship!$B$2:$B=B1,SummaryCitizenship!$C$2:$C=1,SummaryCitizenship!$D$2:$D="Personal Responsibility"),0,5) 

Cette formule fonctionnera également lorsqu'elle est placée dans chaque cellule de ES, mais ne fonctionne pas avec un ARRAYFORMULA

Ancienne question qui explique plus en détail:

J'ai besoin de vérifier les valeurs dans le "SummaryCitizenship!" feuille avec 3 conditions et renvoyer la valeur d'une colonne à partir de cette comparaison. Je peux le faire de 2 façons dans chaque cellule; un utilisant un filtre et un index et un autre utilisant une requête. Malheureusement, le nombre de lignes dans '6-7 Master 2020-21!' La feuille change constamment, je ne peux donc pas simplement coller la formule dans chaque cellule. Cette feuille a plus de 1700 lignes et en aura probablement près de 3000 avant la fin de l'année scolaire. De plus, je ne sais pas quand une nouvelle ligne est ajoutée, je ne peux donc pas simplement entrer et ajouter la formule si nécessaire. J'ai vraiment besoin de quelque chose qui fonctionnera à partir de la référence de cellule ES2 ou ES1.

Voici les formules qui fonctionnent une fois collées dans chaque cellule:

=INDEX(FILTER(SummaryCitizenship!$A$2:$E,SummaryCitizenship!$B$2:$B=B1,SummaryCitizenship!$C$2:$C=1,SummaryCitizenship!$D$2:$D="Personal Responsibility"),0,5)

=QUERY(SummaryCitizenship!A1:E12,"select E where B = '"&B2&"' and C = 1 and D = 'Personal Responsibility' ",0)

Si je pouvais seulement faire fonctionner l'un ou l'autre de ceux-ci avec arrayFormula, je serais défini. Malheureusement, ils ne le font pas.

Dans le pseudo-code, ce dont j'ai besoin est: Si l'ID unique de l'étudiant (colonne B «6-7 Master 2020-21») correspond à l'ID unique de la colonne «SummaryCitizenship» de la feuille B et que la colonne C Quarter «SummaryCitizenship» vaut 1 la colonne D "SummaryCitizenship" de la norme PRIDE est "Responsabilité personnelle", renvoyez la somme de la valeur d'ajustement de points "SummaryCitizenship" colonne C dans la colonne ES du "6-7 Master 2020-21". Faites cela pour toutes les lignes de "6-7 Master 2020-21!" colonne ES de préférence avec une entrée de fonction dans ES1 ou ES2.

Je ne connais pas grand-chose à GAS, mais je peux faire un peu avec. Si vous avez une solution qui inclut le GAZ, je vous en serais également reconnaissant.

Answer 1

Correct, INDEX()ne fonctionne pas avec ArrayFormula, mais nous pouvons utiliser Vlookup pour contourner ce problème .

Cela devrait vous donner ce que vous voulez:

=ArrayFormula(IFNA(VLOOKUP(B2:B,FILTER(SummaryCitizenship!B2:E,SummaryCitizenship!C2:C=1,SummaryCitizenship!D2:D="Personal Responsibility"),4,0)))

(N'oubliez pas de vider votre colonne!)

Version balisée:

=ArrayFormula(
    IFNA(                           // Blank if NA
        VLOOKUP(
            B2:B,                   // Unique ID lookup
            FILTER(                 // Gives us the filter conditions on other table
                SummaryCitizenship!B2:E,    // Key column for VLOOKUP is B
                SummaryCitizenship!C2:C=1,
                SummaryCitizenship!D2:D="Personal Responsibility"
            ),
            4,                      // Index to column 4
            0                       // Exact match
        )
    )
)