Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 10:08:11
Summieren von Zellen mit bedingter Formatierung
26.04.2024 09:08:00
Export alle Tabellenblätter als csv - in bestimmten Ordner
26.04.2024 08:56:40
Über alle Spalten nur €-Werte zählen
26.04.2024 08:08:24
Pivot Vormonatsveränderung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
592to596
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
592to596
592to596
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

bedingter SVERWEIS

bedingter SVERWEIS
05.04.2005 15:12:33
Nikolaus
Hallo,
Ich nage an folgendem Problem mit folgender Ausgangssituation.
Der Umsatz entscheidet über die Förderung. Das ist also ein einfacher SVERWEIS, so in etwa: =SVERWEIS($N14;'Förderstaffel 2005'!$B$2:$C$8;2).
Nun gibt es aber *verschiedene* Förderstaffeln. Eine bestimmte Nummer entscheidet dann, welche Förderstaffel zur Anwendung kommt. Mein erste Gedanke war, eben mehrere WENN-Abfragen hintereinander zu setzen, aber ich habe 30 (!) Staffeln und Excel sagt, daß die Formel zu lang wird. das sieht in etwa so aus:
=WENN($P11=1;SVERWEIS($N11;'Sonder Staffel'!$D$46:$E$50;2);WENN($P11=2;SVERWEIS($N11;'Sonder Staffel'!$D$53:$E$57;2);WENN($P11=3;SVERWEIS($N11;'Sonder Staffel'!$D$60:$E$64;2);WENN($P11=4;SVERWEIS($N11;'Sonder Staffel'!$D$67:$E$71;2);WENN($P11=5;SVERWEIS($N11;'Sonder Staffel'!$D$74:$E$78;2);WENN($P11=6;SVERWEIS($N11;'Sonder Staffel'!$D$81:$E$85;2);WENN($P11=7;SVERWEIS($N11;'Sonder Staffel'!$D$88:$E$93;2);SVERWEIS($N11;'Sonder Staffel'!$D$39:$E$43;2))))))))
Nicht nur, daß ich nicht genug Förderstaffeln unterbringe, ist das eine häßliche Formel.
Hat jemand eine Idee, wie ich das (elegant) lösen kann.
BG,
Nikolaus

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: bedingter SVERWEIS
05.04.2005 15:31:56
UweN
Hallo Nikolaus,
eigentlich würde ich mir niiiieemals anmaßen, die Frage eines EXCEL - Profis auch nur anzuschauen, geschweige denn, mich an einer Antwort zu versuchen !!!!
Aber vielleicht kann ich mit meinen bescheidenen Kenntnissen doch etwas beitragen ;-)
Mir fallen mehrere Lösungsansätze ein:
Wenn Du die Struktur (das Datenmodell) Deiner Liste beeinflussen kannst, wäre es am einfachsten, wenn Du Deine Liste in Spalte D und E um eine weitere Spalte erweiterst und dort die Förderstaffel direkt dem Datensatz zuordnest. Etwas anderes machst Du mit Deinen Wenn - Schleifen im Prinzip auch nicht. Anschließend kannst du einen einfachen SVERWEIS nehmen.
Wenn das nicht geht, würde ich versuchen, die Zeilen Deiner Förderstaffeln in einer separaten Tabelle abzulegen und mit INDIRECT und SVERWEIS die gewünscht Zeile zu ermitteln versuchen.
z.B.:
Spalte A: Förderstaffel
Spalte B: Erste Zeile
Spalte C: Letzte Zeile
Darauf innerhalb einer INDIREKT - Funktion einen SVERWEIS anwenden, um die Zeilen herauszufinden.
Vielleicht hilft es Dir ja weiter
Viele Grüße
Uwe
Anzeige
AW: bedingter SVERWEIS
05.04.2005 16:25:01
Nikolaus
Hi,
Ich glaube, ich war zu ungenau in meiner Beschreibung des Problems. Wie gesagt, grundsätzlich entscheidet der Umsatz über den Prozentsatz, der in der Förderstaffel hinterlegt ist. Die Förderstaffel ist in einem eigenen Tabellenblatt und die Formel hier =SVERWEIS($N14;'Förderstaffel 2005'!$B$2:$C$8;2) macht das. In der Spalte B des Blattes 'Förderstaffel 2005' ist die Umsatzschwelle und in Spalte C der entsprechende Prozensatz. Z.B. 100.000 2%.
Jetzt gibt es aber für manche Firmen eine eigene Staffel, wo z.B. bei 100.000 3% zum Tragen kommen, oder aber vielleicht auch nur 1,5%. Dazu habe ich eben den jeweiligen Staffeln eine Nummer gegeben, 1, 2, 3 ... und diese Nummer neben den Umsatz geschrieben. Die lange Formel prüft die "Staffelnummer" und nimmt dann die entsprechende Staffel.
Uwe, mir sind Deine zwei Lösungsansätze nicht ganz klar. Kannst Du mir die in einem Beispiel erklären?
Nikolaus
Anzeige
AW: bedingter SVERWEIS
05.04.2005 17:09:47
UweN
Hallo Nikolaus,
hab' mal versucht, aus Deinen Angaben eine Datei zu basteln und die entsprechende Formel zusammenzustellen.
Ergebnis findest Du hier:
https://www.herber.de/bbs/user/20721.xls
Wie funktioniert das ?
In Sheet1 stehen die Firmen mit ihren Umsätzen und in Spalte O die "Sonderstaffeln"
Ich bin jetzt davon ausgegangen, daß Du für die Firmen ohne "Sonderstaffel" immer die 0 einträgst. Wenn Dir das nicht gefällt, mußt Du das halt mit ISTFEHLER noch abfangen.
In Spalte R und S wird ermittelt, welche Zeilen für den SVERWEIS in Arbeitsblatt Förderstaffel je nach Sonderstaffel relevant sind. Diese Zeilen werden dann in Spalte P im SVERWEIS mittels der Funktion INDIREKT verwendet.
Das Ergebnis von Spalte R und S kannst Du natürlich auch direkt in die Formel in Spalte P einbauen. Das dient hier nur der Übersichtlichkeit.
Trifft das in etwas Deine Vorstellung ?
viele Grüße
Uwe
Anzeige
AW: bedingter SVERWEIS
05.04.2005 17:21:33
UweN
.... NACHTRAG
Mein Beispiel funktioniert nicht mit Staffel 4, weil die VERGLEICH - Funktion in Spalte S nicht mehr fündig wird (Max - Wert + 1 ist in Förderstaffel ja nicht enthalten).
Uneleganter Lösungsansatz:
Schreib in Förderstaffel A27 den Wert 5 rein. Füge also quasi einen "Dummy - Datensatz" hinzu, damit die Formel in Spalte S noch einen Wert findet.
Geht bestimmt auch eleganter, fällt mir auf die Schnelle jetzt aber nicht ein....
Viele Grüße
Uwe
AW: bedingter SVERWEIS
05.04.2005 18:03:34
Nikolaus
Hallo!
Danke, Uwe! Das ist ja spitze und macht genau das, was ich will. Ich wär' nie auf diesen Lösungsansatz gekommen. Wie bist Du auf diese Idee gekommen? *auch-lernen-wollen*
Immerhin: Wie man diese hübsche Auswahlliste (Spalte O in Sheet 1) macht, habe ich ausgekobelt?
Eine schwere Zusatzfrage. :) Ich habe Deine Musterdatei modifiziert. https://www.herber.de/bbs/user/20723.xls
Jetzt gehören manche Firmen zu eine Gruppe und gemeinsame ist der Umsatz höher und somit kämen die einzelnen Firmen in eine höhere Staffel. Beispiel Firma 2: gemeinsam kommen sie in die 10% Staffel.
Nikolaus
Anzeige
AW: bedingter SVERWEIS
05.04.2005 18:19:50
Nikolaus
So, jetzt habe ich selber die Lösung gefunden. Muß ja meiner Behauptung "Excel-Profi" zu sein, gerecht werden:
=SVERWEIS(SUMMEWENN(L3:L7;L4;N3:N7);INDIREKT("Förderstaffel!B"&R4&":C"&S4);2)
Ein Schönheitsfehler ist nur, wenn es keine Gruppe gibt. ISTLEER sollte da helfen:
=SVERWEIS(WENN(ISTLEER(L5);N5;SUMMEWENN(L4:L8;L5;N4:N8));INDIREKT("Förderstaffel!B"&R5&":C"&S5);2)
Funktionieren tut es; ein wenig lang, aber es funktioniert.
Nikolaus
AW: bedingter SVERWEIS
05.04.2005 18:32:43
UweN
Hallo Nikolaus,
zu Deiner ersten Frage:
schau einfach mal öfter hier in diesem Forum vorbei und verfolge die Threads, die Dich interessieren.
Du wirst sehen, daß häufig ähnliche Fragestellungen rund um SVERWEIS, Datenkonsolidierung, Zeitberechnung etc. gestellt und beantwortet werden (ganz abgesehen von den ganzen VBA - Fragen und Lösungen ... aber das ist nicht mein Ding)
Und nochwas: Stufe Dich selber lieber nicht als EXCEL - Profi ein, wenn Du eine Frage an dieses Forum stellst.
Hier gibt es echt geniale Super Cracks, die auch immer gerne helfen, aber kaum jemanden, der "ungestraft" von sich behaupten darf, ein EXCEL - Profi zu sein ;-)
Die Lösung für Dein Zusatzproblem findest Du übrigens in dieser Datei:
https://www.herber.de/bbs/user/20725.xls
Diesen Lösungsansatz mit SUMMENPRODUKT hab' ich übrigens auch hier gelernt ....
Viele Grüße
Uwe
Anzeige
AW: bedingter SVERWEIS --> Offen
08.04.2005 17:21:54
UweN

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige