MS-Office-Forum

MS-Office-Forum (https://www.ms-office-forum.net/forum/index.php)
-   Microsoft Excel (https://www.ms-office-forum.net/forum/forumdisplay.php?f=29)
-   -   Erweiterte Wenn-Funktion? (https://www.ms-office-forum.net/forum/showthread.php?t=345840)

FGottschalk 14.09.2017 10:10

Erweiterte Wenn-Funktion?
 
Hallo,
ich bin relativ unerfahren mit Excel auserhalb der einfach Standardanforderungen. Daher bin ich jetzt auf ein Problem gestoßen.

Folgendes Szenario (Beispiel):
3 Verkaufsgebiete
Je 5 Kugel Eis von Hersteller 1 und Hersteller 2
In jedem Gebiet kostet eine Kugel unterschiedlich viel und nimmt man mehrere wird es günstiger.

Ich habe für jedes Gebiet ein eigenes Tabellenblatt erstellt in Form von:
Gebiet 1
Kugel Preis Hersteller 1 Preis Hersteller 2
1 1€ 1,10€
2 2€ 2,05€
3 3€ 3,00€
4 4€ 3,95€
5 5€ 4,90€

Gebiet 2
Kugel Preis Hersteller 1 Preis Hersteller 2
1 0,95€ 1,05€
2 1,80€ 2,00€
3 2,95€ 2,95€
4 3,80€ 3,90€
5 4,75€ 4,85€

Gebiet 3
Kugel Preis Hersteller 1 Preis Hersteller 2
1 0,90€ 1,11€
2 1,80€ 2,11€
3 2,70€ 3,11€
4 3,60€ 3,11€
5 4,50€ 4,11€

Einfach MIN-Funktion auf den einzelnen Tabellenblätter ist in einer Extraspalte eingebaut um den jeweils Hersteller für x-Kugeln zu finden.

Nun möchte ich auf einem extra Tabellenblatt durch die Eingabe des Gebietes in ein Feld 1 und der Anzahl der Kugel in ein anderes Feld 2 den günstigsten Hersteller in einem dritten Feld 3 angezeigt bekommen.

Nun könnte ich ja mit sehr viel ausdauer eine endlose WENN-Funktion bauen, jeweils mit der Abfrage "WENN in Feld 1 "1" steht und in Feld 2 "1"; DANN zeige den Hersteller an; SONST schaue ob in Feld 1 "1" steht und in Feld 2 "2" und wieder von vorne etc.

Ich bin mir sehr sicher das es dafür eine einfachere Funktion gibt, damit Excel durch Angabe von Gebiet und Kugelanzahl den richtigen Hersteller findet. Zumindest hoffe ich es. Bei der realen Situation sind es 100 Gebiete und 5 Hersteller und es geht natürlich nicht um Eiskugeln^^

Hoffe ihr könnt mir helfen, wenn mehr Angaben gebraucht werden,einfach sagen.

Vorab schon mal Danke für die Hilfe :)

Hans Hofmann 14.09.2017 11:09

Hallo,

versuche es mit VERWEIS(Suchschlüssel;Suchbereich;Ergebnisbereich)...

jack_D 14.09.2017 11:26

Moin,

mal eine Lösung mit Indirekt ..
Aufbau entsprechend deiner Vorgabe.
Je Gebiet ein Blatt zB. "Gebiet 1"
Je Blatt
1. Zeile Überschriften + HErsteller bezeichnung
1. Spalte Anzahl Kugeln




Agg
 AB
1Gebiet1
2Kugel5
3HerstellerZweiter

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
B3=INDEX(INDIREKT("'Gebiet "&B1&"'!B1"):INDIREKT("'Gebiet "&B1&"'!C1");;VERGLEICH(MIN(INDIREKT("'Gebiet "&B1&"'!B"&B2+1):INDIREKT("'Gebiet "&B1&"'!C"&B2+1));INDIREKT("'Gebiet "&B1&"'!B"&B2+1):INDIREKT("'Gebiet "&B1&"'!C"&B2+1);0))  =INDEX(INDIRECT("'Gebiet "&R[-2]C&"'!B1"):INDIRECT("'Gebiet "&R[-2]C&"'!C1"),,MATCH(MIN(INDIRECT("'Gebiet "&R[-2]C&"'!B"&R[-1]C+1):INDIRECT("'Gebiet "&R[-2]C&"'!C"&R[-1]C+1)),INDIRECT("'Gebiet "&R[-2]C&"'!B"&R[-1]C+1):INDIRECT("'Gebiet "&R[-2]C&"'!C"&R[-1]C+1),0))

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.15 einschl 64 Bit


Grüße

Hans Hofmann 14.09.2017 15:42

Also ich würde INDIREKT meiden, wie der Teufel das Weihwasser....
Damit hebelst Du die Berechnungsstrategie Von xls komplett aus - jede Änderung führt zur Berechnung aller Formeln, was bei dem angedeuteten Datenvolumen keine gute Idee sein könnte?
Das muss anders gehen - dazu müsste man aber genaueres zur Datenstruktur wissen!

Superingo2 14.09.2017 16:49

Hallo HW,

glaube mir, Jack kennt die Nachteile von INDIREKT() wahrscheinlich sogar besser als Du, aber eben auch die Vorzüge.

Zitat:

jede Änderung führt zur Berechnung aller Formeln,
....und damit zeigst Du, dass Du INDIREKT() gar nicht wirklich verstehst, denn es werden lediglich die Zellen mit volatilen Formeln (also hier die mit INDIREKT) bei jeder Änderung irgendeiner Zelle berechnet, alle anderen Formeln werden nur berechnet, wenn sie in Zusammenhang mit der geänderten Zelle stehen!!!!

LG Ingo

Frank Furter 14.09.2017 16:50

Hallo Hans der TS schrieb..: "Ich habe für jedes Gebiet ein eigenes Tabellenblatt erstellt..." Also da liegt INDIREKT() nahe, aber natürlich geht das bei überschaubarer Anzahl der TabellenBlätter auch ohne INDIREKT()...

FGottschalk 14.09.2017 16:51

Hast ne PN :)

jack_D 14.09.2017 22:17

Moin,

Ja. Mir sind die Nachteile von indirekt() durchaus bewusst.
Und ich versuche volatile Funktionen so oft es geht zu meiden.

Danke an der Stelle an Ingo der nicht nur weiß wie indirekt funktioniert... sondern sogar mein Level auf dem Schirm hat ;-)

Jedoch hat der TE von mehreren Blättern geredet und da fällt mir (ohne UDF) keine andere Lösung ein.
Wenn einer eine hat, dann gern her damit ;-)
Ich Lerne gern neues dazu.

aber das mit der begrenzten Anzahl an Blättern was Frankfurter erwähnt, würd ich gern mal sehen. :-)

Grüße Jack

Frank Furter 14.09.2017 22:39

Hallo Jack, ich kenne die Datei (ja auch) nicht, aber so quick & dirty ginge..:

Arbeitsblatt mit dem Namen 'Agg'
*ABC
1Gebiet33
2Kugel22
3HerstellerPreis Hersteller 1Preis Hersteller 1

ZelleFormel
C1=B1
C2=B2
B3=INDEX(INDIREKT("'Gebiet "&B1&"'!B1"):INDIREKT("'Gebiet "&B1&"'!C1");;VERGLEICH(MIN(INDIREKT("'Gebiet "&B1&"'!B"&B2+1):INDIREKT("'Gebiet "&B1&"'!C"&B2+1));INDIREKT("'Gebiet "&B1&"'!B"&B2+1):INDIREKT("'Gebiet "&B1&"'!C"&B2+1);0))
C3=INDEX(WAHL(C1;'Gebiet 1'!B1:C1;'Gebiet 2'!B1:C1;'Gebiet 3'!B1:C1);;VERGLEICH(MIN(INDEX(WAHL(C1;'Gebiet 1'!B2:C6;'Gebiet 2'!B2:C6;'Gebiet 3'!B2:C6);C2;0));INDEX(WAHL(C1;'Gebiet 1'!B2:C6;'Gebiet 2'!B2:C6;'Gebiet 3'!B2:C6);C2;0);0))
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

jack_D 14.09.2017 23:07

Moin Frankfurter

So was ähnliches hab ich mir auch vorgestellt. Konnte es aber nicht gedanklich umsetzen.
Mit Wahl() hab ich noch nie gearbeitet. Sollt ich mir mal anschauen. :-)
Danke für deinen Beitrag !

Beste Grüße


Alle Zeitangaben in WEZ +1. Es ist jetzt 08:06 Uhr.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.