Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1168to1172
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
Inhaltsverzeichnis

Top-Differenzen suchen

Top-Differenzen suchen
Chris
Guten Morgen zusammen,
ich suche eine Lösung zu folgender Aufgabe:
Ich würde mir gerne in einem separaten Tabellenblatt (Tabelle2) die größten Differenzen aus einem anderen Tabellenblatt (Tabelle1) anzeigen lassen. Das Ganze für einen bestimmten Monat
In Tabelle1 stehen einmal in Zeile 1 (E1:V1) Monate in der Form Jul 10.
In Spalte C stehen verschiedene Produkte, bspw. C2:C5 "Auto" oder C6:C9 "Fahrrad"
In Spalte D stehen für die verschiedenen Produkte mehrere Kennzahlen, bspw. "Umsatz" (D4 = Umsatz des Autos, D8 = Umsatz des Fahrrads usw.) und "Kosten" (D5, D9 usw.).
In Tabelle 2 sollen nun die 10 größten Differenzen zwischen Umsatz und Kosten aufgelistet werden, allerdings nur für einen zu betrachtenden Monat, den ich in Tabelle2 C2 eingebe (Jul 10).
Jemand eine Idee, wie das gehen könnte?

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Top-Differenzen suchen
09.08.2010 11:02:55
Klaus
Hallo Chris,
In Spalte D stehen sowohl Umsätze als auch Kosten? Du solltest deinen Tabellenblatt-Aufbau überdenken.
So wie es aussieht wird sich das ohne Hilfsspalten kaum lösen lassen. Mal als Ansatz:
Schreibe die Differenz (Umsatz-Kosten) in eine Hilfs-Tabelle.
Die Top-10 des Januar ermittelts du wie gehabt (=KGRÖSSTE()). Per =VERGLEICH(Tabelle2!C2;E1:V1;) ermittelst du wie weit die Matrix des KGRÖSSTE verschoben werden muss (und verschiebst sie per =BEREICH.VERSCHIEBEN). Welches Produkt das nun ist, ermittelst du über eine INDEX Formel auf die Produkte. Wenn die Differenzen gleich sein können, mache sie eindeutig indem du ihnen =ZEILE()/100000000000 addierst.
Grüße,
Klaus M.vdT.
Anzeige
AW: Top-Differenzen suchen
09.08.2010 11:08:26
David
Hallo Chris,
prinzipiell könnte man das so lösen:
Tabelle1
 ABCDEFGH
1MonatProduktUmsatzKosten  gesucht:   
2Jan 10Auto1000800      max. Gewinn
3Jan 10Auto20001700  Jan 10Auto300
4Jan 10Bus30002400       
5Feb 10Bus1000900       
6Feb 10Bus20001450       
7Feb 10Auto40003650       
8Feb 10Auto20001950       

verwendete Formeln
Zelle Formel Bereich
H3 {=MAX((A2:A8=F3)*(B2:B8=G3)*(C2:C8-D2:D8))} $H$3
{} Matrixformel mit Strg+Umschalt+Enter abschließen

Tabellendarstellung in Foren Version 5.30


Dazu müsste dann allerdings der Umsatz in einer separaten Spalte zu den Kosten stehen.
Gruß
David
Anzeige
AW: Top-Differenzen suchen
09.08.2010 11:22:15
Klaus
Hallo David,
ja - mit einem vernünftigen Tabellenblattaufbau wäre es leicht zu lösen :-) Darum auch gleich mein Seitenhieb am Anfang meines Beitrages.
Beachte, dass Chris schrieb:
In Tabelle1 stehen einmal in Zeile 1 (E1:V1) Monate in der Form Jul 10.
dass heisst seine Monate stehen in einer horizontalen Matrix ... darum mein Hinweis auf Bereich.Verschieben
Hallo Chris,
schau dir mal David's Tabellenblatt-Aufbau an und Vergleich ihn mit deinem. Ist dieses Beispiel nicht eleganter und logischer?
Grüße,
Klaus M.vdT.
AW: Top-Differenzen suchen
09.08.2010 11:10:38
David
Am besten wäre es natürlich, wenn du eine Beispieltabelle posten würdest, denn deine Beschreibung ist nicht sehr aussagekräftig hinsichtlich der Daten, die in den nicht von dir erwähnten Zellen stehen.
Gruß
David
Anzeige
AW: Top-Differenzen suchen
09.08.2010 11:23:02
Chris
Hi,
erstmal danke für die Tipps. Werde das mal prüfen, ob ich das verwenden kann.
Anbei eine Beispieldatei, die vom Layout dem Original ähnelt. Kann das Layout leider nicht verändern, da sie von einem Kollegen erzeugt wird.
https://www.herber.de/bbs/user/70982.xls
AW: Top-Differenzen suchen
09.08.2010 11:46:36
David
Hallo Chris,
ich sehe leider keine Möglichkeit, dass in dieser Struktur wie gewünscht errechnen zu lassen. Ggf. könnte man über eine Umsetztabelle eine andere Struktur erzeugen. Wenn du diese in ein anderes Tabellenblatt einfügst und dieses ausblendest, ist das sogar quasi "unsichtbar".
Alternativ noch per VBA, aber nicht von mir.
Gruß
David
PS: Trotz Beispieltabelle sind die Daten recht dürftig. Ich habe das jetzt so interpretiert, dass das Maximum in einem Monat über ALLE Produkte gesucht wird. Daraus resultiert meine oben gemachte Antwort. Wenn dies nicht so ist, bitte weiter erläutern.
Anzeige
dito AW: Top-Differenzen suchen
09.08.2010 12:04:42
Klaus
Hallo Chris,
ich schliesse mich David an: Aus dieser Struktur ist nichts zu gewinnen. Ausser vielleicht mit einem gutem dutzend Hilfsspalten oder sehr viel Handarbeit.
Sprich doch mal mit dem Kollegen, von welchem du die Liste erhälst.
Grüße,
Klaus M.vdT.
AW: Lösungsvorschlag
09.08.2010 12:32:43
mpb
Hallo Chris,
in Zelle D5 in Tabelle2 Deiner Beispieldatei folgende Matrixformel eingeben und bis D14 kopieren:
=KGRÖSSTE(WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0) -WENN(Tabelle1!$D$3:$D$501="Kosten";INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0);ZEILE()-4)
Funktioniert aber nur, wenn die Tabellen exakt so, wie in Deinem Beispiel aufgebaut sind.
Was noch fehlt, ist in Tablle 2 das zugehörige "Produkt" in C5:C14 auszugeben, aber danach hattest Du ja nicht gefragt. ;-)
Gruß
Martin
Anzeige
AW: Ergänzung
09.08.2010 12:47:13
mpb
Hallo Chris,
in Tabelle 2 in Zelle C5 folgende Matrixformel eingeben und bis C14 nach unten kopieren:
=INDIREKT("Tabelle1!C"&VERGLEICH(KGRÖSSTE(WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2; VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1")&":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;) );0)-WENN(Tabelle1!$D$3:$D$501="Kosten";INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0);ZEILE()-4); WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1") &":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0)-WENN(Tabelle1!$D$3:$D$501="Kosten"; INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2; Tabelle1!$1:$1;0);4;;));0);0))
Noch ein Hinweis: Beide Formeln sind so ausgelegt, dass Einträge in Tabelle1 bis Zeile 500 erfasst werden. Wenn das zu wenig ist, musst Du in den Formeln überall die "Zahl" 500 bzw. 501 durch entsprechend höhere Angaben ersetzen, z.B. durch 10000 und 10001. Wie das die Performance beeinträchtigt, kann ich Dir nicht sagen. Am besten einfach ausprobieren.
Gruß
Martin
Anzeige
AW: Ergänzung
09.08.2010 13:28:24
Chris
Wow! Ziemlich geile Formel! Vielen Dank, das funktioniert fast perfekt.
Die Differenzen werden korrekt in der Reihenfolge dargestellt, allerdings nur diejenigen Produkte, bei denen der Umsatz größer als die Kosten sind. Wie müsste ich denn die Formel anpassen, sodass auch bei Kosten größer Umsatz die Produkte angezeigt werden? Also immer der größte Betrag aus Umsatz minus Kosten?
AW: Ergänzung
09.08.2010 14:09:03
mpb
Hallo Chris,
in C5 folgende Matrixformel:
=INDIREKT("Tabelle1!C"&VERGLEICH(KGRÖSSTE(WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2; VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1")&":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;) );0)-WENN(Tabelle1!$D$3:$D$501="Kosten";INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));100000000000000);ZEILE()-4); WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1") &":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0)-WENN(Tabelle1!$D$3:$D$501="Kosten"; INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2; Tabelle1!$1:$1;0);4;;));100000000000000);0))
In D5 folgende Matricformel:
=KGRÖSSTE(WENN(Tabelle1!$D$2:$D$50="Umsatz";INDIREKT(ADRESSE(2;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(50;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0) -WENN(Tabelle1!$D$3:$D$51="Kosten";INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1") &":"&ADRESSE(51;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));100000000000000);ZEILE()-4)
und dann bis Zeile 14 nach unten kopieren.
Gruß
Martin
Anzeige
AW: Ergänzung
09.08.2010 15:22:17
Chris
Hi,
das klappt noch nicht ganz.
Wäre super, wenn der Betrag der Differenz angezeigt werden würde und dementsprechend auch sortiert wird. Im Bsp.-File also zuerst LKW, Fahrrad, Flugzeug usw.
https://www.herber.de/bbs/user/70983.xls
AW: Rückfrage
09.08.2010 15:42:30
mpb
Hallo Chris,
wie Du sicher erkennen kannst, sind die Formeln nicht gerade trivial, daher wäre es schön, wenn Du Deine Anforderungen klar definieren könntest. Sonst bastle ich mir hier einen Wolf.
Im Moment ist die Ausgabe wie folgt:
Fahrrad 500
Auto 400
BUS 0
Flugzeug -500
LKW -620
M.E. war das auch die ursprüngliche Anforderung. Der höchste Gewinn zuerst und dann absteigend sortiert. Wenn ich Dich richtig verstehe, willst Du jetzt folgendes:
LKW -620
Fahrrad 500
Flugzeug -500
Auto 400
BUS 0
Oder doch so ("Betrag der Differenz"):
LKW 620
Fahrrad 500
Flugzeug 500
Auto 400
BUS 0
Aber wozu sollen diese beiden Varianten denn gut sein?
Gruß
Martin
Anzeige
AW: Rückfrage
09.08.2010 15:49:01
Chris
Sorry für die unklare Erklärung.
Hab die Beispieldatei einfach mit wahllosen Beispielen gefüttert. Im Original sind die Größen Umsatz und Umsatzforecast. D.h. ich will dadurch auflisten, wie sich Umsatz und Forecast unterschieden haben...wahrscheinlich ist das so klarer zu verstehen.
Deshalb ist die letzte Variante (Betrag der Differenz) die gesuchte Alternative.
Habe gerade noch gesehen, dass du in den Formeln für Tabelle2 Spalte D öfters TAbelle1!$D$2:$D$50 schreibst. Gilt das dann nur für Produkte und Kennzahlen bis Zeile 50?
AW: Teillösung
09.08.2010 16:28:50
mpb
Hallo Chris,
"vereinfachte" Beispiele sind i.d.R. schlecht, weil sie die Beantworter in die Irre führen.
Für Deinen Wunsch fällt mir keine 100%ige Lösung ein.
In D5:
=KGRÖSSTE(ABS(WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2;VERGLEICH($C$2;Tabelle1!$1:$1;0); 4;; "Tabelle1")&":"&ADRESSE(50;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0) -WENN(Tabelle1!$D$3:$D$501="Kosten";INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1") &":"&ADRESSE(501;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0));ZEILE()-4)
Formel funktioniert nur dann korrekt, wenn es mindestens 10 Produkte gibt, für die die Differenz größer als Null ist.
In C5:
=INDIREKT("Tabelle1!C"&VERGLEICH(KGRÖSSTE(ABS(WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2; VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;"Tabelle1")&":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; ) );0)-WENN(Tabelle1!$D$3:$D$501="Kosten";INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0));ZEILE()-4); ABS(WENN(Tabelle1!$D$2:$D$500="Umsatz";INDIREKT(ADRESSE(2;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1") &":"&ADRESSE(500;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;;));0) -WENN(Tabelle1!$D$3:$D$501="Kosten"; INDIREKT(ADRESSE(3;VERGLEICH($C$2;Tabelle1!$1:$1;0);4;; "Tabelle1")&":"&ADRESSE(501;VERGLEICH($C$2; Tabelle1!$1:$1;0);4;;));0));0))
Anzeige des Produkts ist nur dann korrekt, wenn der Betrag der Differenz für 2 Produkte nicht genau gleich ist. Dann wird das erste Produkt in der Liste 2mal (hier: Fahrrad) und das zweite Produkt (hier: Flugzeug) gar nicht angezeigt.
Ich möchte abschließend nochmal darauf hinweisen, dass der Tabellenaufbau denkbar ungeeignet für eine Formellösung ist (das haben ja auch andere Foris schon geschrieben). Wenn Du mit diesen Formeln nicht leben kannst, solltest Du ernsthaft über einen anderen Tabellenaufbau oder ggf. eine Lösung über VBA nachdenken.
Gruß
Martin
P.S. Die 50 statt 500 hatte ich zwischenzeitlich für Testzwecke benutzt, ist korrigiert.
P.P.S. Anrede und Grußformel sind hier eigentlich üblich, da höflicher.
Anzeige
AW: Teillösung
09.08.2010 16:43:42
Chris
Hi Martin,
das klappt perfekt (ein kleiner Fehler: in ersterer Formel hat sich noch eine 50 statt 500 versteckt). Vielen Dank.
Wegen Format: Im Grunde hast du/habt ihr recht wegen des Layouts. Mal schauen, ob der Kollege da was machen kann.
Wegen Anrede und Gruß: Dachte das macht man immer nur anfangs und am Schluss, aber werds mir merken.
Also Vielen Dank Euch!
Gruß
Chris

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige