Alternativa all'utilizzo di ARRAYFORMULA con QUERY o INDEX

Question

Spiegazione più mirata:

Questo è il foglio di calcolo https://docs.google.com/spreadsheets/d/1eMlf9QrI59mdOlUzQSzQherSXxcMbJq9iSyHIxKNaRM/edit?usp=sharing

Sul foglio "6-7 Master 2020-21" nella colonna ES, ogni riga deve avere un valore che proviene dal foglio "SummaryCitizenship". Questo valore è nella colonna E del foglio "SummaryCitizenship". La riga nel foglio "SummaryCitizenship" da cui proviene il valore deve corrispondere ai seguenti valori La colonna B ("SummaryCitizenship") di quella riga corrisponde alla colonna B ("6-7 Master 2020-21") AND Column C = 1 AND Column D = Responsabilità personale

Potrei inserire questa formula in ogni cella della colonna ES ("6-7 Master 2020-21")

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

e funziona, ma le informazioni nella colonna B ("6-7 Master 2020-21") sono dinamiche e cambieranno più volte al giorno, per lo più aggiungendo nuove righe al foglio. Ciò significa che ho bisogno che la formula non si trovi in ​​ogni cella in ES, ma piuttosto nella cella ES1 o ES2 e influisca sul resto del foglio come farebbe un ARRAYFORMULA.

Ho anche provato

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

Quella formula funzionerà anche se inserita in ogni cella di ES, ma non funziona con un ARRAYFORMULA

Vecchia domanda che spiega in modo più dettagliato:

Devo controllare i valori in "SummaryCitizenship!" foglio contro 3 condizioni e restituisce il valore di una colonna da quel confronto. Posso farlo in 2 modi in ogni cella; uno utilizzando il filtro e l'indice e un altro utilizzando la query. Sfortunatamente, il numero di righe in "6-7 Master 2020-21!" il foglio cambia costantemente, quindi non posso semplicemente incollare la formula in ogni cella. Quel foglio ha più di 1700 righe e probabilmente ne avrà quasi 3000 prima della fine dell'anno scolastico. Inoltre, non so quando viene aggiunta una nuova riga, quindi non posso semplicemente inserire e aggiungere la formula quando necessario. Ho davvero bisogno di qualcosa che funzioni dal riferimento cellulare ES2 o ES1.

Ecco le formule che funzionano se incollate in ogni cella:

=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)

Se solo potessi far funzionare uno di questi con arrayFormula, verrei impostato. Purtroppo, non lo fanno.

Nello pseudo codice, ciò di cui ho bisogno è: se l'ID univoco dello studente (colonna B "6-7 Master 2020-21") corrisponde all'ID univoco sul foglio "SummaryCitizenship" colonna B e la colonna "SummaryCitizenship" trimestre C è 1 e la colonna D dello standard PRIDE "SummaryCitizenship" è "Responsabilità personale", restituire la colonna C della somma del valore di adeguamento punti "SummaryCitizenship" nella colonna ES di "6-7 Master 2020-21". Fallo per tutte le righe di "6-7 Master 2020-21!" colonna ES preferibilmente con una voce di funzione in ES1 o ES2.

Non so molto di GAS, ma posso farci un po '. Se hai una soluzione che include GAS, ti sarei grato anche per questo.

Answer 1

Corretto, INDEX()non funziona con ArrayFormula, ma possiamo usare Vlookup per aggirare questo problema .

Questo dovrebbe darti quello che vuoi:

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

(Non dimenticare di cancellare la tua colonna!)

Versione contrassegnata:

=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
        )
    )
)