Live-Forum - Die aktuellen Beiträge
Datum
Titel
25.04.2024 00:42:03
Listbox mit gefilterten Daten füllen
24.04.2024 20:38:58
Summe mit Bedingung, nur letzte Ergebnisse anzeigen
24.04.2024 19:29:30
Formel filter
24.04.2024 18:49:56
merge & select
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1496to1500
1496to1500
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

MAX(MMULT()) erweitern um Rankingmöglichkeit

MAX(MMULT()) erweitern um Rankingmöglichkeit
08.06.2016 17:25:03
Patryk
Hallo zusammen,
ich nutze die Formel =MAX(MMULT... um bei ca. 2000 Zeilen die grösste Summe je Zeile zu ermitteln. Das Ergebnis ist somit die Summe der Zeile mit der größten Summe aller Zeilen.
Für ein Ranking brauche ich nun die zweitgrösste Zeilensumme, drittgrösste Zeilensumme, viertgrösste... etc.
Eine Hilfszeile mit der Summe kann ich nicht nutzen, da die Anzahl der Spalten variabel ist (z.B. Jan-März, April-November, etc.) und durch "indirekt" gesteuert wird. (siehe Datei; Formel!B4)
https://www.herber.de/bbs/user/106098.xlsx
VBA kommt leider nicht in Frage.
Vielleicht hat jemand eine Idee. :-/
Schon mal Danke vorab!!
P.

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit MMULT() INDEX() und VERGLEICH() ...
08.06.2016 19:35:08
...
Hallo Patrick,
... INDIREKT() sollte nicht und ADRESSE() braucht man nicht.
Den Bereichsnamen Werte macht die Formel übersichtlicher.
Die +0 ist notwendig, falls evtl. Leerzellen in der Tabelle stehen.
Die Datentabelle hab ich hier natürlich nicht nochmal dargestellt.
 ABCDE
3StartmonatAug 15  Max:
4EndmonatOkt 15  6202
5     

Formeln der Tabelle
ZelleFormel
E4=MAX(MMULT(Werte+0;INDEX(ZEILE(A1:INDEX(A:A;MAX(INDEX(SPALTE(Werte); ))-SPALTE(Werte)+1)); )^0))
Namen in Formeln
ZelleNameBezieht sich auf
E4Werte=INDEX(Daten!$4:$4;VERGLEICH(Tabelle1!$B$3;Daten!$3:$3;)):INDEX(Daten!$2999:$2999;VERGLEICH(Tabelle1!$B$4;Daten!$3:$3;))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: mit MMULT() INDEX() und VERGLEICH() ...
08.06.2016 21:33:03
Patryk
Hallo Werner,
vielen Dank für die sehr elegante Lösung. :-) Gleich umgesetzt!
Hättest du vielleicht eine Lösung dafür, wie ich das Ranking hinbekomme im Stil von kgrösste?
Danke vorab!
VG
Patryk

AW: einfach mit AGGREGAT() anstelle MAX() ...
09.06.2016 07:24:05
...
Hallo Patryk,
... in E4: =AGGREGAT(14;6;(MMULT(Werte+0;INDEX(ZEILE(A1:INDEX(A:A;MAX(INDEX(SPALTE(Werte);))-SPALTE(Werte)+1));)^0));SPALTE(A1)) und Formel ziehend nach rechts kopieren und ... ferdsch ...
Gruß Werner
.. , - ...

AW: übrigens ausreichend wäre hier auch ...
09.06.2016 20:15:34
...
Hallo Patryk,
... anstelle AGGREGAT() die klassische Funktion KGRÖSSTE() einzusetzen. Denn MMULT() hat in bzw. mit der Formel ja bereits eine Matrix gebildet, die KGRÖSSTE() auch ohne den spez. Matrixformelabschluss auswerten kann. Also so:
=KGRÖSSTE(MMULT(Werte+0;INDEX(ZEILE(A1:INDEX(A:A;MAX(INDEX(SPALTE(Werte);))-SPALTE(Werte)+1));)^0)); SPALTE(A1))
Darin ist nun auch gleich das überflüssige Klammerpaar beseitigt, dass ich heute Morgen in der AGGREGAT()-Formel stehen lasen habe.
Gruß Werner
.. , - ...

Anzeige
AW: übrigens ausreichend wäre hier auch ...
09.06.2016 21:53:44
Patryk
Hallo Werner,
wenn ich einen Hut aufhätte, dann würde ich diesen jetzt in einer übertrieben und respektvollen Art und Weise vor dir ziehen. :-)
Vielen vielen Dank für die Hilfe!!!!!!!!!!!!!
Mit besten und herzlich dankbaren Grüßen
Patryk

AW: übrigens ausreichend wäre hier auch ...
10.06.2016 08:06:12
Patryk
Hallo Werner,
leider muss ich dich nochmal um etwas fragen. Ich hoffe, dass ich es nicht überstrapaziere. :-/
Nach weiteren Schritten bin ich jetzt auf 2 Problemsituationen gestoßen:
1. Ich möchte gerne den Geschäftspartner (aus Spalte A in Daten) wiedergeben.
2. Wie kann ich das Problem lösen, wenn es zwei identische Ergebnisse im Ranking gibt und ich dann diese 2 verschiedenen Geschäftspartner anzeigen lassen möchte im Ranking.
So habe ich es mir aufgebaut. In
Spalte A------------------Spalte B
Geschäftspartner---------Anzahl
X----------------------------650
y----------------------------642
z----------------------------631
a----------------------------631
u----------------------------599
schon mal vielen dank für die Unterstützung!
VG Patryk

Anzeige
AW: übrigens ausreichend wäre hier auch ...
10.06.2016 10:02:05
Patryk
Häkchen vergessen :-)

Geschäftspartner auch bei gleicher Punktzahl
10.06.2016 12:00:39
Josef
Hallo Patryk
Ich würde das mit einer Hilfsspalte in Tabelle Daten realisieren.
Das macht die Berechnungen einfacher, und du hast damit auch die Möglichkeit gezielt die Anzahl irgendeines Kunden in der gewählten Zeitspanne zu ermitteln.
https://www.herber.de/bbs/user/106147.xlsx
Gruss Sepp

AW: Geschäftspartner auch bei gleicher Punktzahl
12.06.2016 14:05:42
Patryk
hmm....in der Form hatte ich die Hilfszeile noch nicht bedacht.
werde ich morgen im Office mal ausprobieren, ob es die anderen Formeln beeinträchtigt bzw. mit denen kompatibel ist.
Gebe Feedback, wie es geklappt hat!
Danke schon mal!!!

Anzeige
AW: Geschäftspartner auch bei gleicher Punktzahl
13.06.2016 09:14:09
Patryk
funktioniert einwandfrei!!!
VIELEN VIELEN DANK!

AW: ohne Hilfsspalte mit einem kleinen "Trick" ...
12.06.2016 22:09:16
...
Hallo Patryk,
... der Lösungsvorschlag den Sepp vorgeschlagen führt natürlich zum richtigen Ergebnis und ist der einfachere Weg. Aber mein folgender Lösungsvorschlag dürfte mindestens genau so schnell in der Auswertung sein.
Dazu muss meine ursprüngliche Auswertungsformel in E4 für die Ermittlung der größten Summenwerte nur etwas erweitert werden und nun doch wieder anstelle mit KGRÖSSTE() mit AGGREGAT() ausgewertet werden.
Mein kleiner Trick ermöglicht jedoch dafür die Ersetzung Sepp´s AGGREGAT()-Formel für die Ermittlung der dazugehörigen Geschäftspartner durch eine einfache Formel.
Die Formel in C4 nach unten kopiert ergibt die gleichen Ergebnisse, wie sie Sepp ermittelt. Die Formel D4 dürfte aber mE auch schon ausreichend sein, sie ergibt nur eine anders sortiertes Listing bei identischer Gesamtwertezahl.
Meine beiden Formeln setzen jedoch voraus, dass die Ergebniswerte mit einer auf zwei Stellen gerundeten Summe ausreichend sind (den G-Partner 2102 und 2013 habe ich im Tabellenblatt Daten ein paar Kommawerte dazu geschrieben, um dies für den Fall nach zu weisen):
 ABCDE
3StartmonatMai 15G-PartnerMax:
4EndmonatAug 15210621068262,00
5  210521058258,00
6  210421048254,00
7  210221038250,34
8  210321028250,34
9  210121018242,00
10     

Formeln der Tabelle
ZelleFormel
C4=INDEX(Daten!A:A;(1-RUNDEN(REST(L4*100;1); 4))/1%%)
D4=INDEX(Daten!A:A;RUNDEN(REST(E4*100;1); 4)/1%%)
E4=AGGREGAT(14;6;MMULT(RUNDEN(Werte;2)+0;INDEX(ZEILE(A$1:INDEX(A:A;MAX(INDEX(SPALTE(Werte); ))-SPALTE(Werte)+1)); )^0)+ZEILE(Werte)%%%;ZEILE(A1))
Namen in Formeln
ZelleNameBezieht sich auf
E4Werte=INDEX(Daten!$4:$4;VERGLEICH(Tabelle1!$B$3;Daten!$3:$3;)):INDEX(Daten!$2999:$2999;VERGLEICH(Tabelle1!$B$4;Daten!$3:$3;))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: ohne Hilfsspalte mit einem kleinen "Trick" ...
13.06.2016 09:13:19
Patryk
ich bin einfach nur fassungslos begeistert. :-)
VIELEN VIELEN VIELEN DANK!!!!

AW: ergänzende Angaben ...
13.06.2016 12:19:44
...
Hallo Patryk,
... hatte eben noch festgestellt, dass ich gestern Abend Dir die Basis für die Ermittlung des alternativen Listings der G-Partner in der Spalte C "unterschlagen hatte" Dies hab ich jetzt in Spalte F nachgeholt. Deren Formeln unterscheiden sich auch nur durch den Abzug der ZEILEn()-Nummern von der Formeln in Spalte E von der dortigen Addition dieser (also nur ein - anstelle eines + an einer Stelle)
Außerdem muss ich der Vollständigkeit halber noch darauf verweisen, dass sämtliche Zahlenangaben in Spalte E:F mit dem benutzerdefinierten Zahlenformat: 2 Dezimalstellen nach dem Komma formatiert sind (die für die G-Partner-Ermittlung maßgebenden Nachkommastellen ab der 3 Nachkommastelle sind damit ausgeblendet, weil diese nur dafür ermittelt wurden).
Hier nochmal die Formelzusammenstellung, wobei davon mE die Formeln der Spalte D und E völlig ausreichend sind und C und F nicht benötigt werden:
 ABCDEF
3StartmonatMai 15G-PartnerMax (+)Max (-)
4EndmonatAug 15210621068262,008262,00
5  210521058258,008258,00
6  210421048254,008254,00
7  210221038250,348250,34
8  210321028250,348250,34
9  210121018242,008242,00
10  210021008238,008238,00
11      

Formeln der Tabelle
ZelleFormel
C4=INDEX(Daten!A:A;(1-RUNDEN(REST(F4*100;1); 4))/1%%)
D4=INDEX(Daten!A:A;RUNDEN(REST(E4*100;1); 4)/1%%)
E4=AGGREGAT(14;6;MMULT(RUNDEN(Werte;2)+0;INDEX(ZEILE(A$1:INDEX(A:A;MAX(INDEX(SPALTE(Werte); ))-SPALTE(Werte)+1)); )^0)+ZEILE(Werte)%%%;ZEILE(A1))
F4=AGGREGAT(14;6;MMULT(RUNDEN(Werte;2)+0;INDEX(ZEILE(A$1:INDEX(A:A;MAX(INDEX(SPALTE(Werte); ))-SPALTE(Werte)+1)); )^0)-ZEILE(Werte)%%%;ZEILE(A1))
Namen in Formeln
ZelleNameBezieht sich auf
E4Werte=INDEX(Daten!$4:$4;VERGLEICH(MMULT_160608!$B$3;Daten!$3:$3;)):INDEX(Daten!$2999:$2999;VERGLEICH(MMULT_160608!$B$4;Daten!$3:$3;))
F4Werte=INDEX(Daten!$4:$4;VERGLEICH(MMULT_160608!$B$3;Daten!$3:$3;)):INDEX(Daten!$2999:$2999;VERGLEICH(MMULT_160608!$B$4;Daten!$3:$3;))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige

23 Forumthreads zu ähnlichen Themen

Max(wenn()) geht, Min(wenn()) nicht
Peter


Hallo,
Wieso geht Max(Wenn() und nicht Min(Wenn()?
=MAX(WENN(('PSMS calculations'!$A$2:$A$1294=A3)*('PSMS calculations'!$C$2:$C$1294=B3)=1; 'PSMS calculations'!$P$2:$P$1294;0)) funktioniert wunderbar.
Die selbe als Min(Wenn() geht nicht:
=MIN(WENN(('PSMS calculations'!$A$...
06.09.2012 18:18:10
Thread-ID: 1277692
5 Beiträge
Anzeige
Summe(Bereich.Verschieben()) über mehrere Tabellen
Falk

Hallo zusammen,
funktioniert die Funktion Summe(Bereich.Verschieben()) eigentlich auch über mehrere Tabellenblätter?
Ich habe für eine Datei die für die Kostenstelle mehrere Blätter enthält für die einzelnen Kostenarten. Jedes Blätt für die Kostenarten ist gleich aufgebaut; A1 = Januar,...
10.09.2013 10:24:15
Thread-ID: 1330149
3 Beiträge
Makro MAX(WENN())
Björn

Hi,
folgendes Problem ist in meinem VBA-Makro aufgetreten:
In einer Excel-Datei existieren mehrere Tabellenblätter. Im Tabellenblatt 1 (Spalte D) sind Starttermine in der Form TT:MM:YY hh:mm:ss aufgelistet. Hinter jedem Datum steht ein Zahlenwert (Spalte G).
Es kann vorkommen, dass...
25.07.2014 15:56:50
Thread-ID: 1372341
3 Beiträge
Anzeige
Arrayfrage: array(array(),array())
Arthur
Hallo Excelianer.
Um schnell an ein zweidimensionales Array zu gelangen dachte ich mir: Nimm die Array()-Funktion. Was dabei herauskommt ist ein array, aber nicht wie erwartet.
Sub beispiel() Dim XArr XArr = Array(Array("Hello", "World", "!"), Array(42, 7, 12)) End Sub Wie bekommt man das...
09.04.2016 10:51:32
Thread-ID: 1486996
14 Beiträge
Zählen mit Bedingung (nicht ZÄHLENWENN())
Savarol

Hallo Zusammen,
in Tabelle 1 stehen in Spalte A Materialnummern, in Tabelle 2 in Spalte A auch die Materialnummern und dahinter ein Lieferant und Bestellnummer...
Jetzt möchte ich wissen, bei wieviel unterschiedlichen Lieferanten habe ich das Teil bestellt.
Es kommt also vor, dass d...
20.09.2017 08:23:51
Thread-ID: 1581452
8 Beiträge
=index(Vergleich();Vergleich())
Bjoern

Hallo Forumer,
ich habe ein kniffliges Problem zu lösen.
Ich hab eine komplexe Tabelle (Matrix) vorliegen, die nach bestimmten Kriterien ausgelesen werden soll.
Die Tabelle zeigt folgendes Szenario:
In einer Fabrik gibt es 3 unterschiedliche Center, die jeweils 4 unterschiedlich...
01.12.2017 19:42:55
Thread-ID: 1594863
8 Beiträge
Indirekt mit Index(Vergleich()) vernküpfen
Stefan

Hallo zusammen
Ich bin eine Rezeptesammlung für die Lagerküche am schreiben.
Jedes Rezept steht in einem eigenen Tabellenblatt. In einem weiteren Blatt steht eine Liste mit den einzelnen Rezepten.
Für die Einkaufsliste möchte ich gerne die Zutatenmengen aus den einzelnen Rezepten au...
07.02.2019 09:42:30
Thread-ID: 1673896
10 Beiträge
filter(array()) 2-Dim
Toni

Hallo Excel-Freunde, einen schönen Einstieg ins Wochenende,
mit nachfolgender Aufgabenstellung zum Thema filter(array()) komme ich aktuell nicht weiter.
Filter() setzt ja bekanntlich bei 1-dim an, was ich mit den beiden Funktionen zu umgehen versuche, noch ohne Erfolg und irgendwie ist...
09.08.2019 09:45:45
Thread-ID: 1706778
9 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
4
4
2
2
7
13
7
7
9
8
3
6
4
8
1
4
6
4
3
2
4
6
6