Supportnet / Forum / Tabellenkalkulation
Mehrere WENN bzw. SVERWEIS Funktionen
Frage
Hallo,
bei einer Kalkulationserstellung ist ein Problem mit WENN und SVERWEIS Funktionen aufgetreten.
Ein Beispiel habe ich bei netupload hinterlegt: http://www.netupload.de/detail.php?img=6749da98fb881860fa12818d43f4c289.xls
Beschreibung meines Problems:
Ich suche eine Formel mit der Excel bei der Eingabe eines Artikels und des Typs den entsprechenden Preis aus mehreren Preislisten heraussucht.
Der Artikel wird in meinem Beispiel im Ordner ´Kalkulation´ mit Hilfe eines pull-down-Menüs in Spalte A eingetragen. Der Typ in Spalte B.
In C2 habe ich eine Formel eingegeben mit der ich jeweils aus 1 Preisliste die Preise ausgeben lassen kann.
Wie kann man diese Formel erweitern, dass in Abhängigkeit von der Eingabe in A und B in den entprechenden Preislisten gesucht wird? (Verknüpfung von WENN-Funktionen?)
Die optimalste Lösung wäre eine Formel, welche die Eingabe in A in die Formel in C kopiert und dann in der richtigen Preisliste sucht.
Hoffe jemand hat eine Lösung für mich. Danke schon mal im Voraus.
Gruss
Thomas
Antwort 1 von Tomschi
Vielleicht klappts damit:
http://www.netupload.de/img.php/995ae7c7e16b6f3e5d1f6ebe6ecf742a.xls
Tom
http://www.netupload.de/img.php/995ae7c7e16b6f3e5d1f6ebe6ecf742a.xls
Tom
Antwort 2 von CaroS
Hallo
ich habe Deine Formel mal in 3 Schritten verändert, vielleicht ist da was für Dich dabei.
http://www.netupload.de/detail.php?img=2d32d7896713327d97a783b01cf2...
Variante 3 benötigt für die Zuordnung von ´erster Buchstabe des Typs´ zur Tabelle, in der die Preise stehen, eine kleine <Dingsbums> (Matrix, Übersicht, ... ?) ... erkennbar an der blauen Farbe ...
(Das Wort ´Tabelle´ ist eigentlich schon für das Tabellenblatt vorgesehen, und eine Tabelle in/auf einer Tabelle ist nicht nur sprachlich unschön, sondern könnte auch zu Missverständnissen führen, da damit zwei Dinge unterschiedlicher Art gemeint sind.)
Trotzdem ist Variante 3 sicherlich die komfortabelste, so lange man gewährleisten kann, dass alle Typ-Bezeichnungen mit einem entsprechenden Buchstaben beginnen und diese Buchstaben eine eindeutige Zuordnung zu den Preistabellen ermöglichen.
Gruß,
CaroS
ich habe Deine Formel mal in 3 Schritten verändert, vielleicht ist da was für Dich dabei.
http://www.netupload.de/detail.php?img=2d32d7896713327d97a783b01cf2...
Variante 3 benötigt für die Zuordnung von ´erster Buchstabe des Typs´ zur Tabelle, in der die Preise stehen, eine kleine <Dingsbums> (Matrix, Übersicht, ... ?) ... erkennbar an der blauen Farbe ...
(Das Wort ´Tabelle´ ist eigentlich schon für das Tabellenblatt vorgesehen, und eine Tabelle in/auf einer Tabelle ist nicht nur sprachlich unschön, sondern könnte auch zu Missverständnissen führen, da damit zwei Dinge unterschiedlicher Art gemeint sind.)
Trotzdem ist Variante 3 sicherlich die komfortabelste, so lange man gewährleisten kann, dass alle Typ-Bezeichnungen mit einem entsprechenden Buchstaben beginnen und diese Buchstaben eine eindeutige Zuordnung zu den Preistabellen ermöglichen.
Gruß,
CaroS
Antwort 3 von 10Deluxe
Hallo,
danke für eure Lösungen.
@Tom
sehr interessante Lösung.
Bei der Auswahl in Spalte A von "Motor" und "Rohrleitung" kann man somit in Spalte B entsprechend der Preislisten die Artikeltypen auswählen.
Wieso funktioniert dieses bei "Ventil" nicht?
Wie ist das pull-down-Menü in Spalte B genau aufgebaut?
@Caro
Variante 3 geht leider nicht , da in meiner Aufgabenstellung verschiedene Typbezeichnungen in einer Preisliste vorhanden sind.
Gibt es einen Möglichkeit die Formel der Varianten 1und 2 zu kürzen?
Gruss
Thomas
danke für eure Lösungen.
@Tom
sehr interessante Lösung.
Bei der Auswahl in Spalte A von "Motor" und "Rohrleitung" kann man somit in Spalte B entsprechend der Preislisten die Artikeltypen auswählen.
Wieso funktioniert dieses bei "Ventil" nicht?
Wie ist das pull-down-Menü in Spalte B genau aufgebaut?
@Caro
Variante 3 geht leider nicht , da in meiner Aufgabenstellung verschiedene Typbezeichnungen in einer Preisliste vorhanden sind.
Gibt es einen Möglichkeit die Formel der Varianten 1und 2 zu kürzen?
Gruss
Thomas
Antwort 4 von Tomschi
Guten Morgen!
In Deiner Quelle stand Ventil + Leerzeichen.
Die Formel fragte jedoch auf Ventil OHNE Leerzeichen ab. --> Sorry, habe ich übersehen.
Ich habe dies jetzt geändert und jeder Rubriksbezeichnung einen eigenen Namen zugewiesen. Nun ist es egal wie die Schreibweise aussieht.
In Spalte B habe ich eine benutzerdefinierte Gültigkeitsregel definiert. Daher die unterschiedlichen Drop-Down-Menüs.
http://www.netupload.de/img.php/d676aae36f945811772ae3e9ea00e170.xls
@CaroS
Du hast in Deiner Variantel 3 übersehen, dass in "Rohrleitungen" der Datenbereich bis zur Zeile 12 geht. Dies müsste noch individuell angepasst werden.
Ausserdem gehst Du wohl davon aus, dass die Tabellennamen nie geändert werden.
Ansonsten finde ich Deine Variante 3 sehr interessant und ich werde mir dies wohl mal "vormerken".
Tom
In Deiner Quelle stand Ventil + Leerzeichen.
Die Formel fragte jedoch auf Ventil OHNE Leerzeichen ab. --> Sorry, habe ich übersehen.
Ich habe dies jetzt geändert und jeder Rubriksbezeichnung einen eigenen Namen zugewiesen. Nun ist es egal wie die Schreibweise aussieht.
In Spalte B habe ich eine benutzerdefinierte Gültigkeitsregel definiert. Daher die unterschiedlichen Drop-Down-Menüs.
http://www.netupload.de/img.php/d676aae36f945811772ae3e9ea00e170.xls
@CaroS
Du hast in Deiner Variantel 3 übersehen, dass in "Rohrleitungen" der Datenbereich bis zur Zeile 12 geht. Dies müsste noch individuell angepasst werden.
Ausserdem gehst Du wohl davon aus, dass die Tabellennamen nie geändert werden.
Ansonsten finde ich Deine Variante 3 sehr interessant und ich werde mir dies wohl mal "vormerken".
Tom
Antwort 5 von CaroS
Hallo Thomas,
da mir im Moment ein bisschen der Überblick fehlt, frage ich einfach mal nach, welche Variante jetzt in welcher Richtung am besten weiterentwickelt werden sollte.
Und außerdem noch die Frage, warum Variante 3 nicht geht. Entweder ich hab´s noch nicht richtig verstanden:
"Variante 3 geht leider nicht , da in meiner Aufgabenstellung
verschiedene Typbezeichnungen in einer Preisliste vorhanden sind"
oder Du hast eine Kleinigkeit übersehen oder es geht wirklich nicht wegen des Umfangs und der Komplexität, die ich nicht einschätzen kann.
Angenommen, in einer Preisliste (Tabelle) mit dem Namen "Preise Kleinteile" stehen solche verschiedenen Typbezeichnungen wie A 123 XX, A 124 XX, B 235 YY, B 236 YY, C 448 ZZ, C 449 ZZ (beginnend mit verschiedenen Buchstaben A, B, C, ...), dann braucht man in der "Zuordnungstabelle" (blau) nur schreiben:
A Preise Kleinteile
B Preise Kleinteile
C Preise Kleinteile
Wenn dann ein A-Teil, B-Teil oder C-Teil in ´Kalkulation´ auftaucht, wird in allen drei Fällen auf die Preisliste "Preise Kleinteile" zugegriffen. Eindeutigkeit ist nur insofern erforderlich, dass es in anderen Preislisten nicht noch weitere A-, B- oder C-Teile gibt, so dass die Formel anhand des ersten Buchstabens nicht entscheiden kann, auf welche Preisliste sie nun zugreifen muss.
An dieser Stelle könnte man das Kriterium natürlich auch auf 2 oder mehr Zeichen erweitern, so dass sich dann wieder Eindeutigkeit ergibt. Es muss ja nicht unbedingt nur der erste Buchstabe verwendet werden, das war nur erstmal mein erster Ansatz.
"Gibt es einen Möglichkeit die Formel der Varianten 1und 2 zu kürzen?"
Ein wenig verkürzen könnte man sie, aber sobald Du mehr als die 3 jetzigen Preislisten verwenden willst, würden sie wieder länger werden. Bei mehr als 7 taucht dann zusätzlich noch das Problem auf, dass eine WENN-Funktion nur 7-fach verschachtelt werden kann. Grundsätzlich besser wäre es, sich von der Fallunterscheidung mit Hilfe von WENN zu trennen, was gerade die Variante 3 von den ersten beiden unterscheidet.
Deshalb wäre es gut zu wissen, wie viele verschiedene Typ-Gruppen und wie viele Preislisten es insgesamt geben wird. Auch eine konkrete Aufzählung der beiden Mengen wäre nicht schlecht, um zu sehen, wie man das Kriterium zur Fallunterscheidung definieren kann/muss.
Gruß,
CaroS
da mir im Moment ein bisschen der Überblick fehlt, frage ich einfach mal nach, welche Variante jetzt in welcher Richtung am besten weiterentwickelt werden sollte.
Und außerdem noch die Frage, warum Variante 3 nicht geht. Entweder ich hab´s noch nicht richtig verstanden:
"Variante 3 geht leider nicht , da in meiner Aufgabenstellung
verschiedene Typbezeichnungen in einer Preisliste vorhanden sind"
oder Du hast eine Kleinigkeit übersehen oder es geht wirklich nicht wegen des Umfangs und der Komplexität, die ich nicht einschätzen kann.
Angenommen, in einer Preisliste (Tabelle) mit dem Namen "Preise Kleinteile" stehen solche verschiedenen Typbezeichnungen wie A 123 XX, A 124 XX, B 235 YY, B 236 YY, C 448 ZZ, C 449 ZZ (beginnend mit verschiedenen Buchstaben A, B, C, ...), dann braucht man in der "Zuordnungstabelle" (blau) nur schreiben:
A Preise Kleinteile
B Preise Kleinteile
C Preise Kleinteile
Wenn dann ein A-Teil, B-Teil oder C-Teil in ´Kalkulation´ auftaucht, wird in allen drei Fällen auf die Preisliste "Preise Kleinteile" zugegriffen. Eindeutigkeit ist nur insofern erforderlich, dass es in anderen Preislisten nicht noch weitere A-, B- oder C-Teile gibt, so dass die Formel anhand des ersten Buchstabens nicht entscheiden kann, auf welche Preisliste sie nun zugreifen muss.
An dieser Stelle könnte man das Kriterium natürlich auch auf 2 oder mehr Zeichen erweitern, so dass sich dann wieder Eindeutigkeit ergibt. Es muss ja nicht unbedingt nur der erste Buchstabe verwendet werden, das war nur erstmal mein erster Ansatz.
"Gibt es einen Möglichkeit die Formel der Varianten 1und 2 zu kürzen?"
Ein wenig verkürzen könnte man sie, aber sobald Du mehr als die 3 jetzigen Preislisten verwenden willst, würden sie wieder länger werden. Bei mehr als 7 taucht dann zusätzlich noch das Problem auf, dass eine WENN-Funktion nur 7-fach verschachtelt werden kann. Grundsätzlich besser wäre es, sich von der Fallunterscheidung mit Hilfe von WENN zu trennen, was gerade die Variante 3 von den ersten beiden unterscheidet.
Deshalb wäre es gut zu wissen, wie viele verschiedene Typ-Gruppen und wie viele Preislisten es insgesamt geben wird. Auch eine konkrete Aufzählung der beiden Mengen wäre nicht schlecht, um zu sehen, wie man das Kriterium zur Fallunterscheidung definieren kann/muss.
Gruß,
CaroS
Antwort 6 von Aliba
Hi,
hab mir die Datei auch mal angeschaut.
Ich würde hier mit Bereichsnamen arbeiten.
In allen Preislisten den Bereich A3 bis z.B. B40
markieren, EINFÜGEN - NAMEN - DEFINIEREN und dann die entsprechenden Namen Motor, Rohrleitung und Ventil vergeben.
Dann in Spalte C die Formel:
=WENN(B2="";"";SVERWEIS(B2;INDIREKT(A2);2;0))
Das setzt allerdings voraus, daß in Spalte A auch immer ein Eintrag steht, wenn in Spalte B ein Eintrag ist.
Wenn, wie CaroS schon geschrieben hat, auch anhand der Artikelbezeichnung 1. Stelle erkennbar ist, um welche Artikelkategorie es sich handelt könnte man es auch so lösen:
Anstelle der Namen Motor.....
vergibst Du die Namen:
M_1
V_1
R_1
und dann die Formel:
=WENN(B2="";"";SVERWEIS(B2;INDIREKT(LINKS(B2;1)&"_1");2;0))
CU Aliba
hab mir die Datei auch mal angeschaut.
Ich würde hier mit Bereichsnamen arbeiten.
In allen Preislisten den Bereich A3 bis z.B. B40
markieren, EINFÜGEN - NAMEN - DEFINIEREN und dann die entsprechenden Namen Motor, Rohrleitung und Ventil vergeben.
Dann in Spalte C die Formel:
=WENN(B2="";"";SVERWEIS(B2;INDIREKT(A2);2;0))
Das setzt allerdings voraus, daß in Spalte A auch immer ein Eintrag steht, wenn in Spalte B ein Eintrag ist.
Wenn, wie CaroS schon geschrieben hat, auch anhand der Artikelbezeichnung 1. Stelle erkennbar ist, um welche Artikelkategorie es sich handelt könnte man es auch so lösen:
Anstelle der Namen Motor.....
vergibst Du die Namen:
M_1
V_1
R_1
und dann die Formel:
=WENN(B2="";"";SVERWEIS(B2;INDIREKT(LINKS(B2;1)&"_1");2;0))
CU Aliba
Antwort 7 von CaroS
Hallo!
Ich fasse es noch einmal etwas anders zusammen. Entweder findet sich eine einfache Möglichkeit, aus der Typbezeichnung eindeutig die Preisliste zu ermitteln, die verwen-det werden muss, und zwar mit einer "Berechnung" und nicht mit einer Fallunter-scheidung (z. B. mit Hilfe der von mir vorgeschlagenen "Zuordnungstabelle"), dann kriegt man das alles irgendwie "relativ einfach und relativ kurz" in einer SVERWEIS-Formel unter.
Oder aber man muss tatsächlich eine ziemlich aufwändige Fallunterscheidung durch-führen, bei der man zunächst nachguckt, was in Spalte A steht. Dabei hat man allerdings zwei neue Probleme und ein altes.
1. steht die Artikelbezeichnung (Ventil, Motor, ...) immer nur in der ersten Zeile. In einer tieferen Zeile müsste man sich diese Information erst recht aufwändig beschaffen, da man ja nicht weiß, wie viele Zeilen höher man nachsehen muss.
2. gibt es kleine, aber bedauerliche Unterschiede zwischen der Artikelbezeichnung (Ventil, Rohrleitung) und dem Namen der zugehörigen Preisliste (Preise Ventile, Preise Rohrleitungen). Auch sehr kleine Unterschiede (Ventil mit oder oder nachfolgendes Leerzeichen) führen zu Fehlern, wie man gesehen hat. Um aus den Artikelbezeichnung die zugehörigen Preislisten zu ermitteln, müsste eine exakte Übereinstimmung der Bezeichnungen gewährleistet sein, am besten sogar ohne das Wort "Preisliste ", sonst hat man mit Formeln so gut wie keine Chance.
3. Wenn man bei der Fallunterscheidung auf die WENN-Funktion zurückgreifen muss, stößt man schnell auf Grenzen (max. 7 Ebenen), die weiteren Aufwand verursachen (WENN-Funktionen verketten).
Man sollte hier gewisse "Gestaltungsmöglichkeiten" bei der Wahl von Bezeichnungen, dem Aufbau und der Zusammensetzung von Listen usw. nutzen, um der Programmierung ein Stück entgegen zu kommen und eine einfachere Lösung zu ermöglichen. Als letztes, aber sicherlich nicht schlechtestes Mittel, bleibt in jedem Falle noch VBA.
Gruß,
CaroS
Ich fasse es noch einmal etwas anders zusammen. Entweder findet sich eine einfache Möglichkeit, aus der Typbezeichnung eindeutig die Preisliste zu ermitteln, die verwen-det werden muss, und zwar mit einer "Berechnung" und nicht mit einer Fallunter-scheidung (z. B. mit Hilfe der von mir vorgeschlagenen "Zuordnungstabelle"), dann kriegt man das alles irgendwie "relativ einfach und relativ kurz" in einer SVERWEIS-Formel unter.
Oder aber man muss tatsächlich eine ziemlich aufwändige Fallunterscheidung durch-führen, bei der man zunächst nachguckt, was in Spalte A steht. Dabei hat man allerdings zwei neue Probleme und ein altes.
1. steht die Artikelbezeichnung (Ventil, Motor, ...) immer nur in der ersten Zeile. In einer tieferen Zeile müsste man sich diese Information erst recht aufwändig beschaffen, da man ja nicht weiß, wie viele Zeilen höher man nachsehen muss.
2. gibt es kleine, aber bedauerliche Unterschiede zwischen der Artikelbezeichnung (Ventil, Rohrleitung) und dem Namen der zugehörigen Preisliste (Preise Ventile, Preise Rohrleitungen). Auch sehr kleine Unterschiede (Ventil mit oder oder nachfolgendes Leerzeichen) führen zu Fehlern, wie man gesehen hat. Um aus den Artikelbezeichnung die zugehörigen Preislisten zu ermitteln, müsste eine exakte Übereinstimmung der Bezeichnungen gewährleistet sein, am besten sogar ohne das Wort "Preisliste ", sonst hat man mit Formeln so gut wie keine Chance.
3. Wenn man bei der Fallunterscheidung auf die WENN-Funktion zurückgreifen muss, stößt man schnell auf Grenzen (max. 7 Ebenen), die weiteren Aufwand verursachen (WENN-Funktionen verketten).
Man sollte hier gewisse "Gestaltungsmöglichkeiten" bei der Wahl von Bezeichnungen, dem Aufbau und der Zusammensetzung von Listen usw. nutzen, um der Programmierung ein Stück entgegen zu kommen und eine einfachere Lösung zu ermöglichen. Als letztes, aber sicherlich nicht schlechtestes Mittel, bleibt in jedem Falle noch VBA.
Gruß,
CaroS
Antwort 8 von 10Deluxe
Hallo,
ich danke euch für eure Hilfe. Ich habe alle Lösungen überprüft und eine ausgewählt mit der wir in der Firma sehr gut arbeiten können.
@Tom
Die Lösung mit den drag-drop Menüs ist perfekt für meinen
Anwendungsfall.
@CaroS
Ich habe mich für die Verknüpfung mit den Preislisten für deine Variante 1 entschieden.
Die Restriktion der 7 WENN-Verknüpfungen besteht in diesem Fall natürlich weiterhin. Aber durch Zusammenfassen von einigen Preislisten werde ich mit der Formel sehr gut arbeiten können.
Variante 3 ist in meinem Fall nicht unbedingt geeignet, da die Preislisten recht groß sind und viele verschiedene Artikelbezeichnungen aufgelistet sind. Es kann somit zu Überschneidungen kommen. Wenn ich dich richtig verstanden habe, kann man diesem Problem durch Verlängerung um weitere Zeichen in der blauen Tabelle deiner Lösung beheben. Ich denke, die Komplexität würde somit eher steigen. Trotzdem ist diese Lösung sehr interessant und wird auch von mir für ´vorgemerkt´.
Der Gefahr bei der Namensgebung einen Fehler zu machen, bin ich auch begegnet. Bei einer Lösung bin ich leider überfragt.
@Aliba
Danke auch für die genaue Beschreibung der Bereichsnamen-Möglichkeit.
Noch eine allgemeine Frage:
Gibt es eine ´Excel-Fibel´ in der man nach derartigen Lösungen suchen kann oder habt ihr euch dieses Wissen auf einem anderen Weg angeeignet?
Gruss
Thomas
ich danke euch für eure Hilfe. Ich habe alle Lösungen überprüft und eine ausgewählt mit der wir in der Firma sehr gut arbeiten können.
@Tom
Die Lösung mit den drag-drop Menüs ist perfekt für meinen
Anwendungsfall.
@CaroS
Ich habe mich für die Verknüpfung mit den Preislisten für deine Variante 1 entschieden.
Die Restriktion der 7 WENN-Verknüpfungen besteht in diesem Fall natürlich weiterhin. Aber durch Zusammenfassen von einigen Preislisten werde ich mit der Formel sehr gut arbeiten können.
Variante 3 ist in meinem Fall nicht unbedingt geeignet, da die Preislisten recht groß sind und viele verschiedene Artikelbezeichnungen aufgelistet sind. Es kann somit zu Überschneidungen kommen. Wenn ich dich richtig verstanden habe, kann man diesem Problem durch Verlängerung um weitere Zeichen in der blauen Tabelle deiner Lösung beheben. Ich denke, die Komplexität würde somit eher steigen. Trotzdem ist diese Lösung sehr interessant und wird auch von mir für ´vorgemerkt´.
Der Gefahr bei der Namensgebung einen Fehler zu machen, bin ich auch begegnet. Bei einer Lösung bin ich leider überfragt.
@Aliba
Danke auch für die genaue Beschreibung der Bereichsnamen-Möglichkeit.
Noch eine allgemeine Frage:
Gibt es eine ´Excel-Fibel´ in der man nach derartigen Lösungen suchen kann oder habt ihr euch dieses Wissen auf einem anderen Weg angeeignet?
Gruss
Thomas
Antwort 9 von CaroS
Hallo Thomas,
Excel-Bücher gibt es viele, die eine ultimative Fibel oder Bibel meiner Meinung nach allerdings nicht. Was ich öfter mal und mehr als alle Bücher benutze, das ist die Excel-Hilfe. In den verschiedenen Excel-Foren kann man auch eine Menge lernen, wenn man sich ansieht, wie andere Leute Probleme lösen. Bei mir bleibt allerdings nur was hängen, wenn ich das dann selber praktisch ausprobiere. Schon allein die Übernahme einer fremdem Lösung - entweder in ein völlig leeres Tabellenblatt, ohne Daten, ohne Zusammenhänge, oder in irgendeine noch "freie Ecke" eines ziemlich überfüllten Tabellenblatts - erfordet bestimmte Anpassungen, ohne die es nicht funktioniert. Da muss man sich also damit auseinandersetzen - ich glaube, das ist nicht ganz unwichtig.
Gruß,
CaroS
Excel-Bücher gibt es viele, die eine ultimative Fibel oder Bibel meiner Meinung nach allerdings nicht. Was ich öfter mal und mehr als alle Bücher benutze, das ist die Excel-Hilfe. In den verschiedenen Excel-Foren kann man auch eine Menge lernen, wenn man sich ansieht, wie andere Leute Probleme lösen. Bei mir bleibt allerdings nur was hängen, wenn ich das dann selber praktisch ausprobiere. Schon allein die Übernahme einer fremdem Lösung - entweder in ein völlig leeres Tabellenblatt, ohne Daten, ohne Zusammenhänge, oder in irgendeine noch "freie Ecke" eines ziemlich überfüllten Tabellenblatts - erfordet bestimmte Anpassungen, ohne die es nicht funktioniert. Da muss man sich also damit auseinandersetzen - ich glaube, das ist nicht ganz unwichtig.
Gruß,
CaroS