Hallo liebe www experten,
ich stehe vor folgendem Problem.
Ich habe in Tabelle 1 mehrere Eingabefelder die ich für eine automatische Kalkulation füllen möchte.
Der Abfragewert ist ist in Tabelle1 B8. Dieser Wert muss nun z.B. in Tabelle „5. Zuschläge“ abgefragt werden und dann mit dem danebenstehendem Wert multipliziert werden.
Kurzgesagt gibt es 3 Bereiche: bis 100, 101 bis 300 und ab 301. Gelöst habe ich das mit folgender Formel:
=WENN(B8100
Hallo Patrick,
ungetestet, versuch’s ma so:
=WENN(B8100;B8
Gruß, Andreas
Wie doof ist das denn? So ein offensichtlicher Fehler… Manchmal sieht man den Wald vor lauter Bäume nicht.
Danke.
Wie kann ich jetzt meine Tabelle hochladen bzw. einen Screenshot posten um mein 2. umfangreicheres Problem zu posten?
Datei hochladen
z.B. hier:
http://www.file-upload.net/
und den Download-Link, den du da bekommst hier posten.
Gruß, Andreas
Dies ist mein Kalkulationsfile.
Ich möchte nun in F17 die Abfrage eingeben, dass er den Wert aus G3 in der Spalte A im Blatt „3. FV“ sucht und anschließend den Wert aus „Tabelle1“ B8 in Tabelle „3. FV“ in der Zeile in der er den ersten Wert gefunden hat, in dem bisher eingestellten Beispiel wäre das R20, mit B8 multipliziert.
Ich hoffe ich habe das nicht zu kompliziert ausgedrückt.
Schon jetzt noch mal vielen Dank für die Hilfe!
[MOD]: URL geändert.
Bitte Fragen beantworten
[URL=[http://www.file-upload.net/download-10101350/Kalkula…](http://www.file-upload.net/download-10101350/Kalkulation-NEU.xlsx.html]Kalkulation-NEU.xlsx[/URL)]
Dies ist mein Kalkulationsfile.
Du sprichst in ziehmlichen Rätseln.
Bitte beantworte mir mal genau die Fragen, dann sehen wir weiter:
Ich möchte nun in F17 die Abfrage eingeben,
Auf welchem Blatt?
Und was meinst du mit „Abfrage“?
dass er den Wert aus G3
Auf welchem Blatt?
in der Spalte A im Blatt „3. FV“ sucht und anschließend
den Wert aus „Tabelle1“ B8 in Tabelle „3. FV“ in der Zeile in
der er den ersten Wert gefunden hat, in dem bisher
eingestellten Beispiel wäre das R20, mit B8 multipliziert.
Und wohin soll das produkt aus B8 und R20 dann geschrieben werden?
Ich hoffe ich habe das nicht zu kompliziert ausgedrückt.
Doch
Schon jetzt noch mal vielen Dank für die Hilfe!
Warten wire mal deine Antworten ab.
Gruß, Andreas
Hi Andreas,
da wo ich nix dazu geschrieben habe beziehe ich mich immer auf tabelle1 weil dort auch die Formel eingetragen wird. In Tabelle1 soll in Zelle F17 die Abfrage (Formel) eingetragen werden.
Alsonoch mal komplett. Nicht, dass ich noch Fragen offen lasse:
In Tabelle1 Zelle F17 soll die Formel und auch das Ergebnis rein. Gesucht wird zunächst der Wert aus Tabelle1 Zelle G3. Dieser Wert soll in Tabelle „3. FV“ in Spalte A gesucht werden. Ergebnis wäre in diesem Beispiel Zeile 20. Nun soll er den Wert aus Tabelle1 in Zelle B8 in der Tabelle „3. FV“ zwischen Zellen K20 und R20 suchen (20 in diesem Fall weil er den ersten Wert ja in Zeile 20 gefunden hat). In diesem Beispiel wäre es dann Zelle R20 weil der Wert über „ab 101“ ist. Jetzt noch den darin befindlichen Wert mit dem Wert auf Tabelle1 Zelle B8 multiplizieren und fertig.
Hintergrund ist, dass 1 Palette mit 300 kg (Wert in Tabelle1 Zelle B8) die an das Depot 180 (Wert in Tabelle1 Zelle G3) in meinem Beispiel 26,40 Euro kostet und das muss ich eben automatisch berechnen lassen.
Grüße
Patrick
Jo Patrick,
ich glaub, ich habs verstanden.
Aber so, wie die Tabellen aufgebaut sind, wird das eine etwas größere Sache. Solche Zelleninhalte wie „ab 101“, „ab 301“ usw. erfordern eine recht komplexe Logik. Das möchte ich gern mit VBA lösen. Um das fehlerfrei hinzukriegen brauche ich etwas Zeit. Ich melde mich in den nächsten Tagen wieder.
Gruß, Andreas
Vielen Dank Andreas.
Ich habe von den Formeln in Excel nicht viel Ahnung. Und von VBA gleich 0 Ahnung. Ich würde das Prinzip jedoch gerne verstehen. Ist es denn in Excel unmöglich oder nur komplizierter als in VBA?
Ich habe in z.B. F13 das ganze bezüglich den Bereichen
Vielen Dank Andreas.
Hallo Patrick,
Ich habe von den Formeln in Excel nicht viel Ahnung. Und von
VBA gleich 0 Ahnung.
Dafür sind wir ja hier, dass du auch was dazulernen kannst.
Ich würde das Prinzip jedoch gerne
verstehen. Ist es denn in Excel unmöglich oder nur
komplizierter als in VBA?
Also für mich wäre es mit VBA einfacher, weil ich da größere Erfahrung als mit Formeln habe. Aber es geht sicher auch mit Formeln. Ich versuche das mal, dann lerne auch ich dabei.
Ich habe in z.B. F13 das ganze bezüglich den Bereichen In diesem Beispiel wäre es dann Zelle R20 weil der Wert über „ab 101“
Warum kann es nicht Spalte S oder W sein, die sind auch „ab 101“?
Oder gilt dein Formular nur für den Bereich „STANDARD-SERVICE“?
Das muss ich wissen bevor ich weiter machen kann.
Und noch eine Anmerkung:
In deinen WENN-Abfragen greifst du immer auf genau festgelegte Zellen zu, ohne dich um den Inhalt der Zelle (Gewichtsgrenze) zu kümmern. Was passiert, wenn du die Tabelle mal ändern musst, weil z.B. eine neue Gewichtsgrenze eingeschoben wird oder weil sich Gewichtsgrenzen ändern? Dann musst du deine ganzen Formeln neu schreiben!
Es wäre flexibler, wenn man per Formel wirklich die aktuellen Grenzgewichte abfragen würde und daraus die richtige Spalte erhielte. Das wird aber schwieriger, bei Zelleinträgen wie „ab 101“. Das ist Text, keine Zahl, die man mit einer anderen vergleichen kann. Für solche Fälle arbeitet man dann besser mit VBA, da kann man mit Schleifen u.a. arbeiten.
Aber beantworte mir erst mal meine Frage weiter oben, damit ich weiter machen kann.
Gruß
Patrick
Gruß, Andreas
Mal ein Versuch
Hallo Patrick,
was ich so aus deinen einzelnen Äußerungen verstanden habe, bin ich mal von folgenden Voraussetzungen ausgegangen:
Die Werte in Tabelle „3. KV“, Zellen K13-R13 sind immer Maximalgewichte. Bis zu dieser Grenze gelten die jeweils darunter stehenden Gebühren.
Die Spalten S-Y sind nicht relevant.
Ab über 100kg (Spalte R) ist nicht mehr der Preis für das Paket sondern pro kg angegeben.
Stimmt das so? Wenn ja, kannst du es mal mit folgender Formel in Tabelle1, Zelle F17 versuchen:
=INDIREKT(ADRESSE(VERGLEICH(G3;'3. FV'!A:A;0);VERGLEICH(B8;'3. FV'!K13:Q13)+11;;;"3. FV"))\*WENN(B8\>100;B8;1)
Sag mal Bescheid, ob das klappt.
Als Lerneffekt versuch mal selber rauszufinden, wie die Formel funktioniert.
Morgen Abend kriegst du die Erklärung von mir.
Gruß, Andreas
Hi Andreas,
zunächst einmal, funktioniert die Formel perfekt. Etwas zu perfekt sogar. Ich muss da noch ne Rundung einbauen.
Du hast alles richtig erkannt. Die Formeln beziehen sich zunächst auf den Standard. Ich werde für die anderen Bereiche noch neue Tabellnkalkulationen aufbauen. Dafür muss ich ja nur noch die Zuordnungen ändern.
Prinzipiell hast du recht, dass es einfacher wäre das ganze allgemeiner zu gestalten. Jedoch werde ich dann wieder zu sehr an meine Grenzen stoßen das ganze zu verstehen. Ich mache mir erst einmal die Mehrarbeit und schreibe dann die Formeln um wenn sich was ändert. Wenn auch irgendwann mal endlich mehr Zeit da ist werde ich einen Kurs besuchen um die Programier-Grundkenntnisse zu erlernen.
Deine Formel kann ich leider nicht lesen. INDIREKT und ADRESSE sind mir vollkommen fremd. Ich kenne da bisher nur Sachen wie SVERWEIS (sehr sehr flüchtig) und WENN, ODER, MAX etc.
Lediglich VERGLEICH kann ich als Abfrage lesen. Matrix ist dann Spalten A bis A. 0 steht für WAHR.
Und was ich noch erkannt habe ist, dass ich die Formel am Ende noch mit meinem WENN(UND( erweitern muss damit die Bereiche unter 100 kg abgedeckt sind.
Ich versuche mich mal in das INDIREKT und ADRESSE einzulesen. Wenn ich nicht klar komme frage ich sowieso wieder 
Vielen Vielen Dank!
Hallo,
der Ansatz von Andreas ist schon in die richtige Richtung.
Wenn Du in Deiner Tabelle nicht so verschiedene Grenzen hättest (einmal bis und einmal ab), sondern Du Dich nur für AB entscheiden könntest, wäre die Formel um einiges einfacher.
Denn es passt ja Deine Abfrage auch nicht.
Alles was zwischen 100 und 101 liegt, ist ja bei Dir gar nicht vorgesehen!
Also die Tabelle in ab umändern:
0-10,01-20,01-30,01…100,01
Wenn Du die Anpassung so vorgenommen hast, dann genügt in F17:
=INDEX(‚3. FV‘!$K$1:blush:R$91;VERGLEICH(G3;‚3. FV‘!A:A;0);VERGLEICH(B8;‚3. FV‘!K13:R13))*WENN(B8>100;B8;1)
VG
Erklärung der Formel
So Patrick,
hier kommt, wie gestern versprochen die Erkärung zu der Formel. Vielleicht hilft es dir etwas beim Dazulernen. Also:
Der erste VERGLEICH sucht nach dem, was in Zelle G3 steht, also nach der Nummer des Zustelldepots. Wo sucht er? Das ist der zweite Parameter der VERGLEICH-Funktion: Er sucht auf dem Blatt 3.FV in der kompletten Spalte A. Der dritte Parameter ist eine 0. Das hat hier nichts mit WAHR oder FALSCH zu tun, sondern (wenn du mal in die Hilfe für VERGLEICH schaust) gibt an, dass der exakte Wert gesucht wird, und nicht irgendwelche Größer- oder Kleinerbedingungen (das geht mit 1 bzw. -1).
Der Rückgabewert von VERGLEICH ist die Position des Gesuchten im Suchbereich. Da die komplette Spalte A durchsucht wird, entspricht das also der Zeilennummer in deinem Beispiel 20.
Der zweite VERGLEICH sucht nach dem Gewicht aus Zelle B8. Er sucht wieder auf der Tabelle 3.FV, diesmal in den Zellen K13-Q13. Das sind die Gewichtsgrenzen. Warum sucht er nicht auch noch in R13 (ab 101)? Ich habe hier den dritten Parameter weggelassen. Laut der Excel-Hilfe sucht VERGLEICH damit nach „dem größten Wert, der kleiner oder gleich dem Wert für Suchkriterium ist“. D.h., wenn das Gewicht z.B. 35kg beträgt, findet er die Spalte M, weil 30 der größte Wert ist, der kleiner oder gleich 35 ist.
Das klingt erst mal falsch, denn ein 35kg Paket muss ja nach dem Tarif bis 40kg bezahlt werden. Lösung:
Der Rückgabewert von VERGLEICH ist hier genau genommen nicht die Spalte M. Unser Suchbereich beginnt ja bei K. M ist also das dritte Element, VERGLEICH gibt also 3 zurück. Hinter dem VERGLEICH steht in der Formel +11, ergibt also 14. Die 14. Spalte ist Spalte N! Wir landen also richtig. Hier wird auch klar, warum Spalte R nicht mit durchsucht werden muss: In deinem Beispiel mit dem 300kg-Paket, findet VERGLEICH die 100 in Spalte Q als den „größten Wert, der kleiner oder gleich dem Wert für Suchkriterium ist“. Spalte Q ist im Suchbereich K-Q das 7. Element. 7+11=18, also Spalte R.
Aus den beiden VERGLEICHen ergibt sich also jetzt die Zelle in Zeile 20 und Spalte 18 als der richtige Preis.
Bisher haben wir aber nur die zwei Zahlen 20 und 18. Daraus müssen wir R20 machen. Dafür gibt es die Funktion ADRESSE, die eine Zellenadresse zurückgibt. Laut Excel-Hilfe sind die Parameter, mit denen sie gefüttert werden muss/kann.
Unsere ADRESSE wird also jetzt gefütter mit
Was kommt zurück?: ‚3. FV‘!$R$20.
Aaaaaber: Diese Adresse ist momentan noch Text! Sie ist noch nicht der Inhalt der entsprechenden Zelle. Deswegen verpacken wir das ganze jetzt noch in die Funktion INDIREKT. Die macht aus Text einen echten Zellbezug.
Wir haben also jetzt den Preis für das Paket. - Halt, noch nicht ganz. Bei Paketen über 100kg haben wir nur den Preis pro kg. Um den echten Preis zu erhalten, müssen wir also noch abfragen, ob das Paket über 100kg wiegt. Das passiert mit dem WENN:
Wir nehmen den Preis, den wir erhalten haben und mutiplizieren ihn mit …
Feddisch.
Ich hoffe, ich konnte dich etwas erhellen. Viel Spaß beim weiterlernen.
… und Fragen sind jederzeit erwünscht.
Gruß, Andreas