Alternative zur Verwendung von ARRAYFORMULA mit QUERY oder INDEX

Question

Fokussiertere Erklärung:

Dies ist die Tabelle https://docs.google.com/spreadsheets/d/1eMlf9QrI59mdOlUzQSzQherSXxcMbJq9iSyHIxKNaRM/edit?usp=sharing

Auf Blatt "6-7 Master 2020-21" in Spalte ES muss jede Zeile einen Wert haben, der aus Blatt "SummaryCitizenship" stammt. Dieser Wert steht in Spalte E des Blattes "SummaryCitizenship". Die Zeile im Blatt "SummaryCitizenship", von der der Wert stammt, sollte mit den folgenden Werten übereinstimmen. Spalte B ("SummaryCitizenship") dieser Zeile entspricht Spalte B ("6-7 Master 2020-21") UND Spalte C = 1 UND Spalte D = Persönliche Verantwortung

Ich könnte diese Formel in jede Zelle in Spalte ES einfügen ("6-7 Master 2020-21")

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

und es funktioniert, aber die Informationen in Spalte B ("6-7 Master 2020-21") sind dynamisch und ändern sich mehrmals täglich, wobei dem Blatt meistens neue Zeilen hinzugefügt werden. Das heißt, ich brauche die Formel, um nicht in jeder Zelle in ES zu sein, sondern in Zelle ES1 oder ES2 und den Rest des Blattes zu beeinflussen, wie es eine ARRAYFORMULA tun würde.

Ich habe es auch versucht

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

Diese Formel funktioniert auch, wenn sie in jeder ES-Zelle platziert wird, funktioniert jedoch nicht mit einer ARRAYFORMULA

Alte Frage, die genauer erklärt:

Ich muss die Werte in der 'SummaryCitizenship!' Blatt gegen 3 Bedingungen und geben Sie den Wert einer Spalte aus diesem Vergleich zurück. Ich kann es auf zwei Arten in jeder Zelle tun; eine mit Filter und Index und eine mit Abfrage. Leider ist die Anzahl der Zeilen in '6-7 Master 2020-21!' Das Blatt ändert sich ständig, so dass ich die Formel nicht einfach in jede Zelle einfügen kann. Dieses Blatt hat über 1700 Reihen und wird wahrscheinlich vor Ende des Schuljahres fast 3000 haben. Außerdem weiß ich nicht, wann eine neue Zeile hinzugefügt wird, daher kann ich nicht einfach vorbeischauen und die Formel bei Bedarf hinzufügen. Ich brauche wirklich etwas, das mit der Zellreferenz ES2 oder ES1 funktioniert.

Hier sind die Formeln, die funktionieren, wenn sie in jede Zelle eingefügt werden:

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

Wenn ich nur einen von beiden dazu bringen könnte, mit arrayFormula zu arbeiten, wäre ich bereit. Leider nicht.

Im Pseudocode brauche ich Folgendes: Wenn die eindeutige ID des Schülers (Spalte B "6-7 Master 2020-21") mit der UniqueID auf Blatt "SummaryCitizenship" in Spalte B übereinstimmt und die Spalte "SummaryCitizenship" des Quartals 1 und 1 ist Die Spalte D des PRIDE-Standards "SummaryCitizenship" lautet "Personal Responsibility". Geben Sie den Summenpunktanpassungswert "SummaryCitizenship" in Spalte C von Spalte 6 von "6-7 Master 2020-21" zurück. Tun Sie das für alle Zeilen von "6-7 Master 2020-21!" Spalte ES vorzugsweise mit einem Funktionseintrag in ES1 oder ES2.

Ich weiß nicht viel über GAS, aber ich kann ein wenig damit anfangen. Wenn Sie eine Lösung haben, die GAS enthält, wäre ich auch dafür dankbar.

Answer 1

Richtig, INDEX()funktioniert nicht mit ArrayFormula, aber wir können Vlookup verwenden , um das zu umgehen .

Dies sollte Ihnen das bringen, was Sie wollen:

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

(Vergessen Sie nicht, Ihre Spalte zu löschen!)

Markierte Version:

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