Microsoft Excel

Herbers Excel/VBA-Archiv

Summenprodukt und Teilergebnis mit Variable

Betrifft: Summenprodukt und Teilergebnis mit Variable von: Dominic
Geschrieben am: 07.10.2014 10:53:53


WENN($F$7=2;(SUMMENPRODUKT(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN($E$16:$E$196;ZEILE($E$16:$E$196)-MIN(ZEILE($E$16:$E$196));0;1));--(E16<$E$16:$E$196))+1);SUMMENPRODUKT(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN($F$16:$F$196;ZEILE($F$16:$F$196)-MIN(ZEILE($F$16:$F$196));0;1));--(F16<$F$16:$F$196))+1)
~f~
Diese Formel verwende ich um ein flexibles Ranking System im Bereich Zeile 16 bis 196 zu erhalten. Aufgeführt wird dann der jeweilige Rang (1, 2, 3, 4 usw. für den jeweiligen Platz/ Rang.

Nun ist es jedoch so, dass der Bereich variabel ist. Insgesamt weist der mögliche Bereich eine Größe von Zeile 16 bis 253 auf. Je nachdem wie viele Teilnehmer hier aufgelistet werden, ist der Bereich also größer oder kleiner als 196 wie in diesem Fall.

Vorab: Die Funktion RANG() kann ich nicht nutzen,
da ich diese Liste (von Zeile 16-253) auch mit gewissen Filtern belegen kann, zeige mir nur alle Teilnehmer der Klasse ABC - dann muss der Rang natürlich NUR alle Teilnehmer dieser Klasse berücksichtigen.

Verkürztes und verinfachtes Beispiel:

Klassizifierung gesamt:

RANG / Teilnehmer / Kategorie

1 Teilnehmer A (Kategorie ZZZ)
2 Teilnehmer B (Kategorie ZZZ)
3 Teilnehmer C (Kategorie ZZZ)
4 Teilnehmer D (Kategorie AAA)
5 Teilnehmer E (Kategorie ZZZ)
6 Teilnehmer F (Kategorie ZZZ)
7 Teilnehmer G (Kategorie AAA)
8 Teilnehmer H (Kategorie ZZZ)
9 Teilnehmer I (Kategorie AAA)
10 Teilnehmer J (Kategorie ZZZ)

Filtere ich nun nach Kategorie AAA sieht die Klassifzierung wie folgt aus:

RANG / Teilnehmer / Kategorie

1 Teilnehmer D (Kategorie AAA)
2 Teilnehmer G (Kategorie AAA)
3 Teilnehmer I (Kategorie AAA)

Mit Rang würde hier bei Teilnehmer D, G und I die Plätze 4, 7 und 9 stehen....

Aktuell sieht mein Versuch wie folgt aus, hier habe ich die gleiche Schreibweise wie in VB verwendet, so funktioniert es aber leider nicht.

WENN($F$7=2;(SUMMENPRODUKT(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN("$E$16:" & $AD$1";ZEILE("$E$16:" & $AD$1")-MIN(ZEILE("$E$16:" & "$AD$1));0;1));--(E16<"$E$16:" &$AD$1))+1);SUMMENPRODUKT(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN("$F$16:" & $AD$2";ZEILE("$F$16:" & $AD$2")-MIN(ZEILE("$F$16:" & $AD$2"));0;1));--(F16<"$F$16:" & $AD$2))+1)


So habe ich es nun versucht, in Zelle AD1 steht z. B. "E198" und in AD2 steht F198 in Abhängigkeit der Kundenanzahl...

Funktioniert aber so nicht ganz. Wie bekomme ich den Variablen Zellbereich in meine Formel eingefügt?

  

Betrifft: ein relevanter Tabellenauszug wäre hilfreich ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 11:00:05

Hallo Dominic,

... denn das Nachbauen Deiner Struktur und Formeln ist sicher den meisten zu Zeit aufwändig.


Gruß Werner
.. , - ...


  

Betrifft: AW: ein relevanter Tabellenauszug wäre hilfreich ... von: Dominic
Geschrieben am: 07.10.2014 11:36:04

https://www.herber.de/bbs/user/92998.xlsx


Hier die Musterdatei.

Übrigens kann der letzte Formel Teil entfernt werden, es wird nur der folgende Teil benötigt.


WENN($F$7=2;(SUMMENPRODUKT(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN($E$16:$E$196;ZEILE($E$16:$E$196)-MIN(ZEILE($E$16:$E$196));0;1));--(E16<$E$16:$E$196))+1);


benötigt.


  

Betrifft: zunächst nachgefragt ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 11:43:17

Hallo Dominic,

.. warum setzt Du hierfür keine PIVOTauswertung ein? Diese macht doch genau das und viel mehr sowie viel schneller und einfacher, das was Du (auf den ersten Blick) anstrebst? Oder?


Gruß Werner
.. , - ...


  

Betrifft: AW: zunächst nachgefragt ... von: Dominic
Geschrieben am: 07.10.2014 11:46:34

Hi,

neben der Info, dass die Rang Funktion hier nicht gewünscht / möglich ist, habe ich doch glatt vergessen darauf hinzuweisen, dass auch eine Pivot Tabelle hier keine mögliche Option darstellt.

Sorry, es geht also nicht mit der Pivot Tabelle, kann man meine bisherige Formel nicht einfach um den variablen Bereich erweitern? Ich wette ich hatte bei mir nur die Definition falsch eingefügt, wusste es aber grad nicht besser.

Gruß
Dominic


  

Betrifft: und warum ... von: neopa C (paneo)
Geschrieben am: 07.10.2014 11:57:51

Hallo Dominic,

.. in A11 nicht einfach z.B.:

=SUMMENPRODUKT(TEILERGEBNIS(3;BEREICH.VERSCHIEBEN($D$11:$D$99;ZEILE($D$11:$D$99)-MIN(ZEILE($D$ _
11:$D$99));0;1));--(D11<$D$11:$D$99))+1


Gruß Werner
.. , - ...


  

Betrifft: AW: und warum ... von: Dominic
Geschrieben am: 07.10.2014 12:11:05

Wahnsinn, warum funktioniert denn das jetzt so wie von dir vorgeschlagen und so einfach!?

Ich habe das natürlich zunächst so ausprobiert indem ich den max. möglichen Zellbereich direkt angegeben habe. Doch bei mir hatte es den Rang "zerschossen". Jetzt funktioniert es aber plötzlich... Kurios!


 

Beiträge aus den Excel-Beispielen zum Thema "Summenprodukt und Teilergebnis mit Variable"