Suchen Sie die vorherige Zeile mit einem bestimmten Wert in Spalte A

Question

Anhand des obigen Beispielblatts, das die semantischen Zeilen „Kopfzeile“, „Unterüberschrift“ und „Zwischensumme“ enthält, versuche ich, eine Formel zu ermitteln, um die vorherige Unterüberschriftszeile relativ zur aktuellen Zelle zu lokalisieren. Wenn die Formel beispielsweise in eingegeben würde F5, würde sie die Zeile lokalisieren 2, und wenn sie in eingegeben würde F17, würde sie die Zeile lokalisieren 13.

Die Zeilen werden bedingt als Überschrift, Unterüberschrift oder Zwischensumme formatiert, je nach Vorhandensein von Werten Hoder Sin Tder Spalte $A:$A, d. h. die Unterüberschriftszeile nist eine Zeile, in der $An = "S". Nun möchte ich dieses Konzept auf meine Formeln ausweiten.

Auf eine Kopfzeile folgt immer eine Unterüberschrift (ich muss mir also keine Gedanken über Kopf- und Unterüberschriften machen, die in der falschen Reihenfolge stehen).

Ich habe Folgendes versucht:

1. =MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)

   Dies gibt immer Zeile zurück 2, weil MATCHgibt dieErsteÜbereinstimmung im Satz, und ich kann die OFFSETHöhe nicht begrenzen (d. h. rekursiv, da die vorherige Unterüberschriftsposition unbekannt ist);

2. {=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}

   Dies gibt auch zurück 2, weil selbst im Array-Kontext (d. h. mit Ctrl+ Alt+ Enter) MATCHimmer noch nur das erste Ergebnis zurückgegeben wird.

3. =LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)

   Dies gibt zurück 0, da IFhier kein Array erwartet wird, und erweitert es daher OFFSET($A5, 0, 0, -ROW($A5), 1)zu einem einzelnen Wert 0, der nicht übereinstimmt "S"und als Zahl LARGEbehandelt wird .FALSE

4. {=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}

   Dies gibt zurück #VALUE, weil die Array-Erweiterung zu früh erfolgt, was -ROW($A5)als Array übrig bleibt -{5}, was auch kein gültiger numerischer heightParameter ist OFFSET(ich wollte, IF(OFFSET(...)="S",...)dass das Bit ein Array ist, nicht das -ROW($A5)Bit, aber Excel kann nicht unterscheiden).

Ich konzentriere mich derzeit auf Excel 2010. Frühere Versionen sind nicht anwendbar (obwohl die Vorwärtskompatibilität ein Bonus ist). Ich versuche, VBA zu vermeiden, da es für mich schwieriger ist, *.xlsm-Dateien zu verteilen als *.xlsx-Dateien (außerdem weiß ich bereits, wie das mit VBA geht).

Gibt es noch andere Dinge, die ich versuchen kann?

Answer 1

Der einfache Weg, dies zu tun, besteht darin, zu schummeln und eine gemischte absolute/relative Formel zu verwenden. Dies ist eine Array-Formel (Eingabe mit STRG+UMSCHALT+EINGABE), die in die Zelle eingegeben wird, B4aber an beliebiger Stelle in Zeile 4 stehen könnte. Sie gibt die Zeilennummer der markierten Formel zurück S.

=MAX(IF($A$1:A4="S",ROW($A$1:A4)))

Beim Kopieren nach unten wird der zweite Teil der Referenz B4 and A4erhöht. Dadurch wird sichergestellt, dass Sie die Zeile mit der größten Übereinstimmung erhalten, dieüberdie aktuelle Zeile. Sie können diese Formeln schneller eingeben, indem Sie F4nach dem Eingeben/Auswählen des entsprechenden Bereichs verwenden. Dadurch werden die Dollarzeichen durch alle Auswahlmöglichkeiten durchlaufen.

Bild der Bereiche

Wird verwendet, um Ihre Formeln zu ersetzen

Nachdem ich die Frage ein wenig gelesen habe (und basierend auf der Bearbeitung von @SteveTaylor), scheint es, dass Sie dies dazu verwenden, Ihre Formeln zu aktualisieren. Sie können die von oben zurückgegebene Zeile verwenden, um INDEXDatenbereiche zum Summieren zu erhalten. Ich sehe 2 Formeln, die ersetzt werden können:

• Berechnung der Gesamtsumme für jede beschriftete Datenzeile. In diesem Fall kann dynamisch auf die darüber liegende Zwischensummenzeile verwiesen werden.
• Gesamtberechnung für die Zwischensummenzeile. In diesem Fall können die oben zu summierenden Werte dynamisch referenziert werden.

Für die einzeiligen Daten können Sie die Formel verwenden, beginnend F3mit einer Array-Formel. Beachten Sie, dass ich auf die Verwendung umgestiegen bin, SUMPRODUCTwodurch es viel einfacher ist, zu mehr als 2 Spalten zu gelangen.

=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)

Für die Formel zur Gesamtzeile können Sie, beginnend in F11, wieder die Array-Formel verwenden:

=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))

Wenn Sie eine Formel für alle anderen wollen, können Sie diese zu einer verschachtelten Formel IFbasierend auf dem Wert in der Spalte kombinieren A. Hier ist die Array-Formel, deren Anfangszeile F2nach unten kopiert werden kann.

=IF(
  A2="S", 
  SUM(D2:E2), 
    IF(A2="T", 
      SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))), 
      C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))

Diese Formel unterscheidet nicht zwischen einer leeren Zeile und einer „Daten“-Zeile. Derzeit gibt sie 0 für die Abstandszeile zurück, was in Ordnung ist.

Bild der Ergebnisse und Formelnfür zwei Blöcke Ihrer Daten.