Maximale Werte im Vergleich zum vorherigen Array zählen

442
R.T.

Ich versuche die Anzahl der Werte in einer Zeile zu zählen, die größer als der MAX () - Wert eines (Teils) der obigen Spalte sind.

Zum Beispiel: im Vergleich zu den 3 Zellen über sich. In E kommt der 'Count'. Ich habe die Zellen markiert, die mit * gezählt werden sollen.

Maximale Werte im Vergleich zum vorherigen Array zählen

Mein letztes Array ist ungefähr 200x5000 ... Ich habe schon versucht (Zelle E4 im Beispiel), Sachen wie: 

{=SUM(IF(A4:D4>=MAX(OFFSET(A1,ROW($1:$4)-1,0,3,1)),1,0))}  {=COUNTIF(A4:D4,">="&MAX(OFFSET(A1,0,ROW($1:$4)-1,3,1)))}

Ich hoffe, dass die Funktion ROW () & OFFSET () die MAX-Funktion über die Spalten aufteilt ... Aber keine gibt ein anständiges Ergebnis. Ich kann den MAX () natürlich über 200 verschiedene Spalten auf einem anderen Blatt teilen. Aber ich bekomme Albträume, weil ich es nicht mit einer einzelnen Array-Formel schaffen kann.

1

2 Antworten auf die Frage

2
pat2015

Wenn Sie streng nach einem Ansatz suchen, der auf einer Arbeitsblattfunktion basiert, ist dies möglicherweise nicht die empfohlene Lösung. Hier habe ich jedoch eine kleine UDF (User Defined Function) verwendet, um die Aufgabe zu vereinfachen.

Diese UDF nimmt einen Bereich auf und gibt ein Array von Max jeder Spalte in diesem Bereich zurück.

Drücken Sie ALT+ F11, um den VBA-Editor aufzurufen. Fügen Sie im Menü Einfügen ein Modul ein. Fügen Sie nun den folgenden Code ein.

Function Ret_Arr(inpt As Range) As Variant   Dim v As Variant  ReDim v(1 To inpt.Columns.Count)  For i = 1 To inpt.Columns.Count  v(i) = Application.Max(inpt.Columns(i))  Next i  Ret_Arr = v   End Function

Beachten Sie, dass in diesem Basiscode nicht viele Überprüfungen enthalten sind, um ihn für alle Fälle robust zu machen.

Dies übernimmt Range und gibt ein Array von Max aus jeder Spalte zurück, das Sie in Ihrer Arrayformel verwenden können.

Speichern Sie den VBA-Code und das Arbeitsblatt als XLSM-Makro-aktiviertes Arbeitsblatt, falls Sie Excel 2007 oder höher verwenden.

In diesem Beispiel befinden sich Beispieldaten in Zellen A1: D8. Geben Sie in E2 die folgende Formel ein und drücken Sie CTRL+ SHIFT+ ENTERinnerhalb der Formelleiste, um eine Array-Formel zu erstellen.

Die Formel wird in geschweifte Klammern eingeschlossen, um anzuzeigen, dass es sich um eine Array-Formel handelt

=SUM(IF($A2:$D2>=Ret_Arr($A$1:D1),1,0))

Nun soll für jeden Bereich über dem Ret_Arr das Array von Max jeder Spalte zurückgegeben werden. Wenn Sie den korrekten Bereich nicht an Ret_Arr übergeben, erhalten Sie möglicherweise fehlerhafte Ergebnisse. Wenn Ihre fraglichen Spalten also A bis D sind, müssen Sie den gleichen Bereich der Spalten A bis D an die UDF übergeben.

Arbeit ,, perfekt 10 ☺ Rajesh S vor 5 Jahren 0
Vielen Dank. Ich gehe mit der VBA, es ist eine elegante Lösung. Aber ich bin immer noch überrascht, dass es nicht formelbasiert ist. R.T. vor 5 Jahren 0
0
XOR LX

In E4, Matrixformel **:

=COUNT(1/(MMULT(TRANSPOSE(ROW(A$1:D3)^0),N(A4:D4>=A$1:D3))=ROWS(A$1:D3)))

Kopieren Sie es nach Bedarf.

Grüße

** Array-Formeln werden nicht auf die gleiche Weise eingegeben wie 'Standard'-Formeln. Statt nur ENTER zu drücken, halten Sie zuerst STRG und UMSCHALT gedrückt und drücken nur dann ENTER. Wenn Sie es richtig gemacht haben, werden Sie feststellen, dass Excel die Formel mit geschweiften Klammern {} umgibt (Sie sollten diese jedoch nicht manuell einfügen).

Verwandte Probleme