Office: Anzahl der Top 5 Werte zeilenweise Zählen

Hallo Liebe Forenmitglieder,

ich habe eine Tabelle mit 40 Namen untereinander und trage wöchentlich eine Zahl in
der Spalte der jeweiligen KW ein. (Mit einer bedingten Formatierung ermittle ich,
wer von den 40 Namen diese Woche die Besten 5 waren).
Aber nun hätte ich noch gern in Spalte C automatisch gezählt, wie oft in den 52 Wochen
dieses Unternehmen einer der besten war.
(Mit "ZÄHLENWENN(D9:AU9;ODER(KGRÖSSTE($AU$2:$AU$41;1);KGRÖSSTE($AU$2:$AU$41;2);KGRÖSSTE($AU$2:$AU$41;3);KGRÖSSTE($AU$2:$AU$41;4);KGRÖSSTE($AU$2:$AU$41;5)))" habe ich leider keinen Erfolg.

Habt Ihr eine gute Idee dazu? Sicherlich ist es wieder total logisch, wenn man die Lösung sieht.

Lieben Dank an Euch und einen schönen Sonntag;
Thomas

 

Hallo,

vielleicht so:

 

Hallo BoskoBiati,
vielen Dank für Deine Hilfe.
Allerdings wenn ich die grünen Felder in Zeile 10 von links nach rechts durchzähle, erhalte ich 21
und nicht wie Deine Formel ausgibt 23.
Eigentlich müssten ja nur die grünen Felder gezählt werden. Ein kleiner Quercheck zeigt, dass es
vermutlich nur in der Zeile 10 nicht stimmt. Warum auch immer.

Lieben Gruß
Thomas

PS: Lieben Dank nochmals auch für Deine Formel zu meinem Beitrag "Letzter Arbeitstag im Quartal".
Das klappte prima - bin nur leider erkrankt und konnte mich daher nicht mehr gleich zurück melden.

 

Hallo Edgar,

in Zeile 3 Stand 8 jetzt 9.
Mit VBA hätte ich da auch 8 ermittelt.

starte den VBA Editor (Alt+F11), Bild sollte zweigeteilt sein ansonsten Strg+R, gehe auf Deine Datei, rechte Maustaste, Einfügen Modul, Code ins rechte Fenster kopieren, VBA Editor schließen (Alt+F4, oder Kreuz rechts oben).
Register Ansicht, Befehlsgruppe Makros, auf Makro klicken, Makros anzeigen, Makro auswählen und ausführen (Alt+F8 ).

Code:

Option Explicit

Sub Farbe()
    Dim InLetzte As Integer                         ' Variable Letzte Spalte
    Dim LoLetzte As Long                            ' Variable letzte Zeile
    Dim InI As Integer                              ' Schleifenvariable Spalte
    Dim LoI As Long                                 ' Schleifenvariable Zeile
    Dim InJ As Integer                              ' Zelle mit Farbe
    ' Letzte Splate
    InLetzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
    'letzte Zeile
    LoLetzte = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    For LoI = 2 To LoLetzte
        InJ = 0
        For InI = 4 To InLetzte
            If Cells(LoI, InI).DisplayFormat.Interior.Color = 13561798 Then
                InJ = InJ + 1
            End If
        Next InI
        Cells(LoI, 3) = InJ
    Next LoI
End Sub

 

Hallo Hajo,

und auch Dir vielen Dank für Deine Lösung, die ist natürlich super. Dennoch packt mich auch die
Neugierde nach einer formelbasierten Lösung. Einfach weil man es gern wüsste.
Und ja, man stellt sich die Lösung oft so einfach vor, und über Formeln ist es dann doch oft nicht nur
"mal schnell" umgesetzt.

Lieben Dank Euch :)
Thomas

 

Hallo Thomas,

ich bin dann raus.
Ich vermute Edgar fibdet noch den Fehler in seiner Formel.

Gruß Hajo

 

Hi,

wenn Du mit 365 arbeitest, wobei ich hoffe, dass noch jemand das ohne Hilfszellen hinkriegt, mit irgendeiner Lambda-Funktion:

 

Hallo Hajo und BoskoBiati,

lieben Dank für Eure Hilfe und Bosko, auch Danke für Deine "Fehlersuche" falls es überhaupt einer war.....

Liebe Grüße und einen schönen Sonntag Abend,
Thomas

 

Hallo Edgar,

wenn du in Zelle C2 folgende Formel kopierst:
=NACHZEILE($A$2:$A$41; LAMBDA(Nr; LET(rgKw; NACHSPALTE($D$2:$BC$41; LAMBDA(kw; RANG.MITTELW(INDEX(kw;Nr);kw;0))); SUMME(WENNFEHLER(1*(rgKw<=5);0)))))
so erhältst du im Zellbereich C2:C41 die gleichen Werte wie mit deinen Formeln - allerdings ohne den Hilfszellen D44:BC48.
Leichte Abweichungen erhält man, wenn man statt RANG.MITTELW(...) die Funktion RANG.GLEICH(...) verwendet.

 

Hallo Anton,

ich glaube, ich werde LAMBDA nie verstehen.

 

Hallo Edgar,

nicht verzweifeln, ich hatte anfangs auch Schwierigkeiten. Mir hat aber geholfen, dass ich Gemeinsamkeiten zwischen den neuen Funktionen entdeckt habe.

1.Gruppe:
LET(varI;AusdruckI;...;varN;AusdruckN; RückgabeAusdruck(varI;...;varN))
LAMBDA(ParamI;...; ParamN; RückgabeAusdruck(ParamI;..;ParamN))
Bei beiden stellt der letzte Ausdruck den Wert zur Verfügung, der aus dem LET() bzw. LAMBDA() zurückkommt.
Während LET als ein System effizienterer (lokaler) Wertzuweisungen gedacht ist mit abschließendem Rückgabewert, stellt LAMBDA eine anonyme benutzerdefinierte Funktion dar (mit Funktionsparametern und Rückgabewert). Einen Namen erhält sie entweder im Namensmanager oder durch Zuweisung an eine LET-Variable. Bei Letzterem ist der Name natürlich nur lokal (=innerhalb der LET-Funktion) gültig. LAMBDA-Funktionen dürfen sogar rekursiv programmiert werden - dann aber müssen sie im Namensmanager definiert werden wegen des dann benötigten Namens.

2.Gruppe:
NACHZEILE(Array; (Eta-)LAMBDA(ArrayZeile; RückgabeAusdruck(ArrayZeile)))
NACHSPALTE(Array; (Eta-)LAMBDA(ArraySpalte; RückgabeAusdruck(ArraySpalte)))
MAP(Array; (Eta-)LAMBDA(ArrayZelle; RückgabeAusdruck(ArrayZelle)))
Jede dieser 3 Funktionen benötigt 2 Parameter: der erste ist ein Array, der zweite ist eine benutzerdef.Funktion, die 1 Parameter benötigt und einen Rückgabewert erzeugt. Die Bezeichnung "(Eta-)LAMBDA" weist darauf hin, dass man die Funktion aus einer vordefinierten Liste entnehmen könnte (wobei dann kein weiterer Parameter bzw. Berechnungsausdruck anzugeben ist, zB. =NACHZEILE(A2:C3;SUMME) statt =NACHZEILE(A2:C3; LAMBDA(zl; SUMME(zl))) ).
Jede dieser 3 Funktionen gibt den je Schritt erzeugten Rückgabewert in einem Array zurück.

3.Gruppe:
SCAN(InitAkku; Array; LAMBDA(Akku; ArrayZelle; RückgabeAusdruckAkku(Akku;ArrayZelle)))
REDUCE(InitAkku; Array; LAMBDA(Akku; ArrayZelle; RückgabeAusdruckAkku(Akku;ArrayZelle)))
Beide Funktionen benötigen 3 Parameter: der erste ist ein InitAkku-Wert, der zweite ist ein Array und der dritte eine benutzerdef.Funktion, die 2 Parameter benötigt und einen (Akku-)Rückgabewert erzeugt.
Beide Funktionen befüllen den Akku zuerst mit dem InitAkku-Wert und nach jedem Schritt mit dem RückgabeAusdruckAkku-Wert. SCAN gibt den Inhalt des Akku nach jedem Schritt zurück (Rückgabe-Array), während REDUCE nur den Akku-Endstand zurückgibt als Einzelwert.

4.Gruppe:
MATRIXERSTELLEN(AnzahlZeilen;AnzahlSpalten; LAMBDA(IndexZeile;IndexSpalte; RückgabeAusdruck(...)))
Diese Funktion erstellt eine Matrix mit AnzahlZeilen und AnzahlSpalten und benötigt eine benutzerdef.Funktion mit 2 Ganzzahl-Parametern und einem Rückgabewert, zB: =MATRIXERSTELLEN( 5; 3; LAMBDA(i; j; 5*i+2*j-4))

5.Gruppe:
GRUPPIERENNACH(ArraySpalte; WerteSpalte; (Eta-)LAMBDA-Funktion; diverseParams...)
PIVOTMIT(ArraySpalte;WerteSpalte; ArrayZeile;WerteZeile; (Eta-)LAMBDA-Funktion; diverseParams...)
Diese Funktion erstellen anstatt der PivotTable formelbasierte Pivot-Bereiche. Hier gibt es sehr viele Konfigurationsmöglichkeiten, die ich hier nicht aufzählen will/kann.

 

Hallo Anton,

vielen lieben Dank für die ausführliche Erläuterung. Ich werde mir das mal in Ruhe zu Gemüte führen, vielleicht kann ich dann mal selbst mit Lambda arbeiten. Leider kann man Lambda nicht mit der Formelauswertung nachverfolgen.

 

Hallo Edgar,

als Alternative gibt es von Microsoft ein Add-In namens "Excel Labs", das auch mit einem deutschen Excel zurecht kommt. Damit erstelle ich die meisten etwas längeren Formeln.
Es enthält auch einen Formel-Debugger.
Man kann damit die länglichen Formeln, die sich durch den Einsatz von diversen LAMBDA-Funktionen ergeben, automatisch strukturiert darstellen lassen. Z.B. die Formel aus dem Posting #9 in diesem Thread sieht in der Darstellung dieses Add-In wie folgt aus:

Dieses Fenster lässt sich in der Breite verstellen und die enthaltenen Formeln passen sich mit ihrer Umbruch-Tiefe automatisch an. In diesem Add-In ist außer dem "Advanced formula environment" ein Editor für Python-Excel-Zellen und natürlich der obligatorische Copilot enthalten. Beides habe ich selten im Einsatz.

 

Hallo Excel-Fan's,

wenn ich in der 365-Formel von Exl121150 diesen Teil:
1*(rgKw<=5) so ändere 1*(rgKw<6)
ergeben sich teilweise andere Ergebnisse [so schon in Zeile 2 (addidas AG) 5 statt 6]
Was ist da passiert?

fragt sich Luschi
aus klein-Paris

 

Hallo Excel-Fan's

habe den Unterschied klären können, nach dem es mir gelungen ist, den Inhalt der Variablen 'rgKW' für 1 Firma darzustellen. Habe das mal aufgedröselt in der angehängten Beispieldatei.

Gruß von Luschi
aus klein-Paris