Max Summe ermitteln und Text anzeigen

[rate mit]

Hallo,

dann versuche es mal hiermit...

[Klaus]

ahaaaaaaa.... *rumfreu
was hat die Spalte L für eine Funktion?

[rate mit]

die steht zwischen "K" und "M" .... *ggg*

ich hatte mehrere Hilfsspalten - diese kann dann wohl auch gelöscht werden...

Was machst du eigentlich z.B. bei Zahl "6" ?   (Gleichheit der Ergebnisse)

[Klaus]

Ahhh... verstehe.

Ja... verdammt gute Frage. Mist. Darüber hab ich noch gar nicht nachgedacht. Das ist freilich eine blöde Situation dann :(
Im Grunde müsste man beides anzeigen. Aber das geht mangels Platz nicht.
Wahrscheinlich dann nach dem Alphabet- und irgendwie kennzeichnen das es zwei gleiche gibt?
grrrrrrrr......

[rate mit]

Im Grunde müsste man beides anzeigen. Aber das geht mangels Platz nicht.

Du hast keinen Platz??? G4  / G5 / G6 usw. sind schon belegt?  (oder kannst du die Spalte G verbreitern?)

[Klaus]

...alle Richtungen sind belegt. Nur diese eine Spalte ist noch frei. Hinzufügen geht auch nicht, weil dann sämtliche VBA's nicht mehr funtioniern.
Ich hab mir diesmal zu wenig Platz gelassen für das Projekt :(

Breiter kann ich sie aber machen. Das ist kein Problem.

[neopa]

Hallo Klaus,

wie Du ja schon festgestellt hast, ermittelt meine Formel aus dem Beitrag vom 30.12.2 den Ergebnistext auch ganz ohne Hilfsspalten. Diese war und ist allerdings nur für einen Ergebnistext ausgelegt 8der Vollständigkeit halber habe ich diese zum Vergleich nachstehend in H3 auch dargestellt)

Die einfachste und schnellste wie flexibelste Lösung dafür, dass Du nun auch mehrere Ergebnistexte gelistet haben möchtest und diese mit der nachgestellten jeweiligen Gesamtmenge ist natürlich eine Pivotauswertung (siehe G10:H19, wobei G10:H11 der Berichtsfilterbereich ist).

Für die von Dir angestrebte Formellösung mit eingeklammerter nachgestellten jeweiligen Gesamtmenge in einer Zelle je Ergebnistext wird die Formel natürlich komplexer, ist aber auch mit einer Formel realisierbar und auch in XL2007. Da Du offensichtlich auch AGGREGAT() in einer Formel akzeptierst, nutze ich diese nachfolgend anstelle von VERWEIS(). Damit aber die Formel nicht ellenlang wird, arbeite ich mit benannten Formeln (im Namensmanager zu definieren und zwar nachfolgend bei zuvor aktivierter Zelle G3 wie unten stehend von mir angegeben), um dadurch die Formelteile die in der Zellformel nicht nur einmal genutzt werden, durch den benannten Formelnamen zu ersetzen und damit die Zellformel zu kürzen.

Die nachfolgende Zellformel in G3 ist für die Auswertung der Daten in B3:D99 definiert. Wenn mehr auszuwerten ist, sind die benannten Formeln entsprechend anzupassen (aber bitte immer nur wirklich für den ca. max zu erwartend auszuwertenden Zellbereich; alternativ dazu mit den Feldnamen arbeiten, wenn Du zuvor die Quelldatenliste als "intelligente" Tabelle umformatiert hast).

Die Formel in G3 einfach entsprechend weit nach unten ziehend kopieren. Sie listet die Daten abwärts sortiert nach der jeweiligen Gesamtmenge. Das evtl. komplizierteste darin, ist die Berücksichtigung von Ergebnistextwerten, die die gleiche Ergebnissumme haben. Dies habe ich in der benannten Formel mit dem Formelteil ...+ZEILE(...)%% berücksichtigt (und zwar für Ganzzahlen in der Mengenspalte und für zunächst max 99999 Datensätze.

 

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H
1							Zahl:	Zahl:
2	Zahl	Text	Menge	Zuordnung			6	6
3	1	brot	34	M			tomate (5)	gurke
4	2	butter	27	M			gurke (5)
5	3	fisch	24	P			messer (4)
6	5	brot	21	M			toast (3)
7	6	brot	20	M			brot (3)
8	7	toast	19	P
9	5	brot	19	M
10	6	tomate	3	P			Zahl	6
11	3	brot	17	M			Zuordnung	P
12	6	gurke	3	P
13	9	brot	12	M			Texte	Ges.Menge
14	10	tomate	11	P			gurke	5
15	6	brot	3	P			tomate	5
16	11	brot	11	M			messer	4
17	12	tomate	7	P			brot	3
18	7	gurke	13	P			toast	3
19	5	brot	6	P			Gesamt	20
20	6	gurke	2	P
21	10	toast	5	P
22	10	weintraube	5	P
23	10	löffel	4	M
24	6	messer	1	P
25	6	tomate	2	P
26	6	brot	4	M
27	6	toast	3	P
28	11	tomate	4	P
29	6	messer	3	P
30	3	toast	3	P

Name	Bezug
_ErgTxt	=INDEX(!$B:$B;AGGREGAT(15;6;ZEILE(!$B$3:$B$99)/(_SUM=AGGREGAT(14;6;_SUM/(ZÄHLENWENN(!$G$2:$G1048576;LINKS(!$B$3:$B$99;3)&"*")=0);1))/(!$A$3:$A$99=!$G$2)/(!$D$3:$D$99="P");1))

Zelle	Formel
G3	=WENNFEHLER(_ErgTxt&" ("&SUMMEWENNS($C:$C;$A:$A;$H$2;$D:$D;"P";$B:$B;_ErgTxt)&")";"")
H3	=VERWEIS(9;1/(SUMMEWENNS(C3:C99;A3:A99;H2;D3:D99;"P";B3:B99;B3:B99)=MAX(INDEX(SUMMEWENNS(C3:C99;A3:A99;H2;D3:D99;"P";B3:B99;B3:B99);)));B3:B99)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Die benante Formel _Sum wird von Gerds Tool leider nicht gelistet, weil diese nur Bestandteil der gelisteten benannten Formel _ErgTxt ist. Deshalb gebe ich Dir diese nachfolgend so an: 

PHP-Code:

=SUMMEWENNS(!$C$3:$C$99;!$A$3:$A$99;!$G$2;!$D$3:$D$99;"P";!$B$3:$B$99;!$B$3:$B$99)+ZEILE(Tabelle1!B$3:B$99)*(!$A$3:$A$99=!$G$2)*(!$D$3:$D$99="P")%% 


_Sum muss so (bei aktivierter Zelle G3) zwingend als erste im Namensmanger defniert sein, erst danach _ErgTxt dort defnieren!

[Edit:]
Habe gerade eben festgestellt dass im Tool von Gerd auch die Definition des benannten Namens _ErgTxt nicht so wiedergibt, wie es in meiner Datei (bei aktivierter Zelle G3) definiert wurde.

Richtig muss diese wie folgt lauten:

PHP-Code:

=INDEX(!$B:$B;AGGREGAT(15;6;ZEILE(!$B$3:$B$99)/(SUM=AGGREGAT(14;6;_SUM/(ZÄHLENWENN(!$G$2:$G2;LINKS(!$B$3:$B$99;3)&"*")=0);1))/(!$A$3:$A$99=!$G$2)/(!$D$3:$D$99="P");1)) 


[LCohen]

Mit XL365 (XLWeb geht auch) doch ein Quantensprung gegenüber vorher:

  Muster1.xlsx (Größe: 16,36 KB / Downloads: 3)

[Cadmus]

Hallo,

ich habe mich auch mal versucht. Mehrere Ergebnisse werden über Textverketten zusammengefasst.

Formel in I8 war nur zur Kontrolle und kann gelöscht werden.

Edit: Formel angepasst

[Klaus]

Vielen lieben Dank für Eure Hilfe.

Ich muss mich aber entschuldigen, ich habe in #4 geschrieben, dass auf meinem Heim Rechner XL Pro 2021 und auf dem Arbeitsrechner XL 365 läuft - aber ich habe nicht herausgefunden, wie ich diese Angabe in meinem Profil korrigieren kann.

Mir scheint auch, dass entweder Angaben von mir im Thread fehlen, oder aber diese untergegangen sind.
Gesucht wird der Text mit den höchsten Summenwerten der Spalte Menge - wenn die Zuordnung zu P besteht.
Die Ergebniszelle sollte die beiden Top Texte mit jeweils dahinter stehender Gesamtsumme () beinhalten.
Es kann keine Zeile oder Spalte hinzugefügt werden. Neben der Suchzahl gibt es nur eine freie Zelle welche in der Größe aber veränderbar ist.

Liebe Grüße
Klaus