Ruft den Spaltenkopf der ersten Zelle des längsten aufeinanderfolgenden Satzes von Zellen unter einem bestimmten Schwellenwert ab

Beginnen wir damit, die Probleme mit Ihren beiden vorhandenen Formeln anzusprechen.

Ihre y * Formel, =FREQUENCY(A3:I3,K3)berechnet tatsächlich die Anzahl, wie oft die Daten unten abfällt, oder gleich dieser ist, der Schwellenwert. Um nur Werte zählen unter der Schwelle, und die Daten unter der Annahme besteht nur aus ganzzahligen Werten, müssen Sie diese Formel verwenden: .=FREQUENCY(A3:I3,K3-1)

Der FREQUENCYTeil Ihrer z * -Formel FREQUENCY(IF(A3:I3<K3, COLUMN(A3:I3)), IF(A3:I3>K3, COLUMN(A3:I3)))sollte, streng genommen, eine >=statt der >. In Ihrer vollständigen z * -Formel, in der Sie nur das Maximum der Zählwerte extrahieren, ist es eigentlich egal. Es funktioniert jedoch möglicherweise nicht ordnungsgemäß, wenn es in einer komplexeren Formel verwendet wird. Zum Beispiel funktioniert meine Lösungsformel nicht korrekt mit dem >(für den Randfall, bei dem der Wert unmittelbar vor der längsten Sequenz gleich dem Schwellenwert ist).

Die korrigierte z * -Formel lautet:

{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))))} 

Die Verwendung dieser korrigierten Formel als Basis führt zu der folgenden Lösung (Array-Eingabe in N3), die die Spaltenüberschrift der ersten Zelle der längsten Sequenz extrahiert:

{=INDEX(1:1,IFERROR(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),MOD(MAX(10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))+ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1),10^5))+1,COLUMN(A3:I3)))} 

Erläuterung:

Die verettete Version der obigen Formel lautet wie folgt:

{= INDEX( (1:1), IFERROR( SMALL( IF(A3:I3>=K3,COLUMN(A3:I3)), MOD( MAX( 10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))) +ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1+IF(1,,"N:N needs to match the column of the cell this formula is entered into") ), 10^5 ) )+1, COLUMN(A3:I3) ) )} 

Die Formel funktioniert so, dass sie die Anzahl der FREQUENCY()"bin" ändert, sodass sie auch den bin-Index enthalten. Dann wird der Index aus der Bin-Zählung entsprechend der längsten Sequenz extrahiert und mit verwendet SMALL(), um den unteren Schwellenwert für diese Bin zu ermitteln. Dieser Schwellenwert ist die Spaltennummer der Zelle unmittelbar vor der ersten Zelle der längsten Sequenz. Schließlich wird die Spaltennummer der ersten Zelle verwendet INDEX(), um den Kopf der ersten Zelle zu erhalten.

Für Ihr geliefertes Beispiel:

• FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))→ , zählt das Bin-Array
• 10^5*→ , zählt das skalierte Bin-Array
• COUNT(IF(A3:I3>=K3,))→ 4welches um eins weniger ist als die Anzahl der Fächer (dies zählt die Intervall-Schwellenwerte, aber die Anzahl der Fächer ist um eins größer)
• Also → → was sind die Indizes in das skalierte Bin-Zähl-Array,ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1ROW(INDEX(N:N,1):INDEX(N:N,5))-1
• +→ , das modifizierte Bin zählt Array
• MAX()→ 400002, die längste geänderte Anzahl der Sequenzen
• MOD(400002,10^5)→ 2, der Rang der unteren Schwelle des längsten Sequenz-Bin in dem Schwellenfeld (die Indizes in dem Bin-Zählfeld entsprechen dem Rang des unteren Schwellenwerts im Schwellenfeld)
• IF(A3:I3>=K3,COLUMN(A3:I3))→ , das Schwellenwert-Array
• SMALL(,2)+1→ 3+1→ 4die Spaltennummer der ersten Zelle der längsten Sequenz ( SMALL()ignoriert boolesche Werte; der einer Bin zugeordnete untere Schwellenwert ist die Spaltennummer der Zelle unmittelbar vor der ersten Zelle der Bin)
• IFERROR(SMALL(…)+1,COLUMN(A3:I3))ist erforderlich, da für das erste Bin kein unterer Schwellenwert vorhanden ist und wenn die längste Sequenz dem ersten Bin entspricht (dh die längste Sequenz beginnt bei der ersten Zelle des Datenbereichs), erhalten wir SMALL({…},0)+1→ #NUM!. IFERROR()fängt diesen Fehler ab und COLUMN(A3:I3)gibt die Spaltennummer der ersten Zelle zurück.
• INDEX((1:1),4)→ 4der Spaltenkopf der ersten Zelle der längsten Sequenz

Anmerkungen:

• Die verettete Formel funktioniert tatsächlich, wenn sie eingegeben wird.
• Die Klammern (1:1)sind erforderlich, um zu zwingen 1:1, auf seiner eigenen Linie zu bleiben.
• ROW(INDEX(column,1):INDEX(column,…))wird anstelle des allgemeineren verwendet, ROW(INDIRECT("1:"&…))da es nicht flüchtig ist und auch beim Löschen von Zeilen / Spalten noch funktioniert. (Vorausgesetzt columnwird auf die Spalte der Zelle gesetzt, in die die Formel natürlich eingegeben wird.)
• IF(1,,"comment")ist ein Inline-Kommentar. (Der Wert ist immer Null, so dass die Formel keinen Nettoeffekt hat.)
• Wenn Sie nur die Spaltennummer der ersten Zelle anzeigen möchten, kann die Formel vereinfacht werden, indem Sie die äußerste entfernen INDEX().

Vorbehalt:

• Wenn mehr als eine längste Sequenz vorhanden ist, gibt die obige Formel den Startheader der letzten längsten Sequenz zurück. Die Formel kann so geändert werden, dass der Startheader der ersten längsten Sequenz zurückgegeben wird, indem das Zehner-Komplement des Bin-Index anstelle des einfachen Index verwendet wird, wenn die Bin-Anzahl geändert wird:

{= INDEX (1: 1, IFERROR (KLEIN (WENN (A3: I3> = K3, SPALTE (A3: I3))), 10 ^ 5- MOD (MAX (10 ^ 5 * FREQUENCY (WENN (A3: I3 <K3.), SPALTE (A3: I3)), IF (A3: I3> = K3, SPALTE (A3: I3))) + 10 ^ 5- ( REIHE (INDEX (N: N, 1)): INDEX (N: N, COUNT.) (IF (A3: I3> = K3,)) + 1)) - 1) ), 10 ^ 5)) + 1, SPALTE (A3: I3)))}