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

528
zara007

Ich habe eine Formel, die die Länge der längsten Gruppe aufeinanderfolgender Zellen in einer Datenzeile berechnet, die einen bestimmten Schwellenwert unterschreiten.

Ich möchte den Spaltenkopf der ersten Zelle dieser Gruppe anzeigen. Im folgenden Beispiel möchte ich eine Zelle N3anzeigen 4, die den Wert der Spaltenkopfzelle darstellt D1. Ist das möglich?

Beispieldaten:

 | A | B | C | D | E | F | G | H | I | J | K | L | M | N | ---+---+---+---+---+---+---+---+---+---+---+-----------+---+---+---+ 1 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | X | Threshold | y*| z*| h*| ...[header] 2 | | | | | | | | | | | | | | | 3 | 20| 52| 61| 23| 18| 25| 25| 40| 42| X | 30 | 5 | 4 | ? | ...[data] 


y * -> Die Häufigkeit, mit der die Daten unter den Schwellenwert fallen, berechnet nach folgender Formel:

=FREQUENCY(A3:I3,K3) 

z * -> Die Länge des längsten aufeinanderfolgenden Satzes von Zellen unter dem Schwellenwert, berechnet mit der CSE-Formel (Array):

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

h * -> Erforderliche Formel, um den Spaltenkopf der ersten Zelle des längsten aufeinanderfolgenden Satzes von Zellen zurückzugeben.


Links zu Cross-geposteten Seiten:

https://www.mrexcel.com/forum/excel-questions/1057479-excel-formula-return-column-header-first-cell-consecutive-cells-below-threshold.html#post5077811

https://www.excelguru.ca/forums/showthread.php?9046-Return-column-header-von-first-cell-von-consecutive-cells-nach- unterhalb der Schwellengrenze

https://www.ozgrid.com/forum/forum/help-forums/excel-formulas/1203930-return-column-header-from-first-cell-from-consecutive-cells-nur-a-schwelle

https://www.excelguru.ca/forums/showthread.php?9046-Return-column-header-von-first-cell-von-consecutive-cells-how-a-threshold&p=37149&posted=1#post37149

https://www.mrexcel.com/forum/excel-fragen/1057446-return-column-header-first-cell-identified-consecutive-cells-meet-criteria.html

http://www.msofficeforums.com/newreply.php?do=newreply&noquote=1&p=129061

0
Also sollte die Antwort "M" sein? DavidPostill vor 6 Jahren 0
Die Antwort sollte '4' sein. Dies ist der Header für die erste Zelle in der längsten Folge von Zellen unterhalb des 'Schwellenwerts' (im obigen Beispiel '30'). zara007 vor 6 Jahren 0
Die Spalte 'M' ist eine Berechnung der längsten Reihe aufeinanderfolgender Zellen unter dem Schwellenwert (30): Im Beispiel beginnen diese in Spalte D (23, 18, 25, 25). zara007 vor 6 Jahren 0

1 Antwort auf die Frage

0
robinCTS

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)+13+14die 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)))}