Ich habe mehrere tausend Werte, die denen im bereitgestellten Bild ähnlich sind.
Ich versuche, die Zeitwerte in Spalte K zu summieren, aber nur, wenn sie in Spalte M den Wert „Flag“ haben und nur aufeinanderfolgende Werte.
Beispielsweise sind die Zellen M467–M477 alle markiert, und zusammengerechnet ergeben sie ca. 0,017 Stunden.
Dann werden die Zellen M478-M480 ignoriert, dann werden die Zellen M481-M483 zu ~0,0036 Stunden summiert usw. Welche automatisierte Excel-Formel kann das?
Beispiel für Tabellenwerte
Antwort1
Eine Option mit einer Hilfsspalte.
Die Formel in Q2 lautet
=IF(AND(M1<>"flag",M2="flag"),K2,IF(AND(M1="flag",M2="flag"),SUM(Q1,K2),""))
und in R2
=IF(Q3="",Q2,"")
Abschreiben.
Antwort2
Wenn die Summe in der ersten Zeile jeder Gruppe stehen soll, können Sie diese Formel verwenden:
=IF(AND(M2="Flag",M1<>"Flag"),SUM(K2:INDEX(K2:INDEX(K:K,MATCH(1E+99,K:K)+1),MATCH(TRUE,INDEX(M2:INDEX(M:M,MATCH(1E+99,K:K)+1)="",),0)-1)),"")
Brechen sie ab:
Die beiden INDEX(K:K,MATCH(1E+99,K:K)+1)suchen die letzte Zelle in Spalte K, die eine Zahl enthält, und legen diese als Umfang des referenzierten Bereichs fest. Auf diese Weise ist die Formel dynamisch; wenn neue Zeilen hinzugefügt werden, passt sich die Formel automatisch an.
Dann beginnen wir innerhalb der Summe mit der Zelle, die sich in derselben Zeile befindet. K2 ist relativ und wenn die Formel nach unten geht, tut dies auch die Referenz. Die Endzelle im Bereich wird mit einem weiteren INDEX/MATCH festgelegt.
Dieses Mal suchen wir nach der ersten Zelle aus der Zeile, in der sich die Formel befindet und die in Spalte M leer ist. Wir legen diese Zeile -1 als letzte Zeile für SUM() fest.
Das IF() führt lediglich dazu, dass die Summe und der Rest eine leere Zeichenfolge sind.
Wenn es sich am unteren Ende der Gruppe befinden soll, verwenden Sie diese Array-Formel:
=IF(AND(M2="Flag",M3=""),SUM(INDEX($K$1:K2,IFERROR(MATCH(2,IF($M$1:M2="",1)),1)+1):K2),"")
Da es sich um eine Matrixformel handelt, muss sie beim Verlassen des Bearbeitungsmodus mit Strg-Umschalt-Eingabe bestätigt werden. Bei korrekter Ausführung wird die {}Formel von Excel eingefügt.
Dieses Mal suchen wir nach dem letzten leeren Platz in M über der aktuellen Zeile, um den ersten festzulegen.
Der IFERROR bezieht sich auf die Titelzeile, wenn die Zeile direkt darunter „Flag“ enthält.
