Office: SVERWEIS angepasste Formeln

Hallo,
ich habe vorhieriges Topic mit Sverweis hinbekommen, leider bekomme ich bei der Berechnung einen Überlauf und finde nicht das Problem wann und warum der auftritt.

Für eure Hilfe bin ich bereits sehr dankbar

gruss Stephan

 

Hola,
das ist jetzt nicht despektierlich gemeint, aber du hast keine Ahnung was du da genau tust, oder?
Deshalb noch mal der Rat: ihr verdient damit Geld, also lasst euch helfen indem ihr einen Auftragsprogrammierer beauftragt.
Es hilft nichts, hier Formeln zu zeigen die nicht funktionieren, aber nicht zu sagen, was wann genau passieren soll. Ist ja nicht das erste Mal.
Gruß,
steve1da

 

Hallo steve1da,

Sorry, nur zur Info, ich verdiene damit kein Geld, ich helfe einem guten Freund, der in Excel mit Copy & Paste incl. mind. 3 Fehlern hat, weil er noch mit dem Taschenrechner dabei arbeitet, ich möchte ihm nur alles vereinfachen.

zu dem Fehler du hast Recht, ich hätte es besser beschreiben müssen, leider war ich überfordert, weil die Berechnung funktioniert hat, nur beim Abändern dann plötzlich der Fehler kommt. liegt wohl am
=Wenn(MRabatt[Anzahl]=0; ... habe dann in =Wenn(Preise!L2=0; dann geht es, .... warum auch immer, ich verstehs leider nicht..

gruss stephan

 

#ÜBERLAUF erhältst du, weil deine Formel in diesem Falle 2 Ergebnisse automatisch untereinander erzeugt. Schreib die Formel mal in eine leere Zelle rechts neben die Tabelle, da werden 2 Ergebnisse erscheinen. Und da in K53 schon etwas drin steht hat Excel keinen Platz um die Ergebnisse untereinander zu schreiben. Dann erscheint #ÜBERLAUF.

 

Hallo steve1da,

Danke jetzt hab ich es verstanden, habe auch mittlerweile meinen Fehler gefunden ...
die Formel war eine Matrixformel, wenn man beim Kopieren das ignoriert ...

vielen Dank für deine Unterstützung
Gruß Stephan

 

Hi,

wenn du einen Überlauffehler bekommst, dann heißt das, dass dein Excel Spill-Formeln kennt. Das wiederum heißt, dass du keinen Abschluss als Matrix-Formel brauchst, denn dein Excel macht das automatisch. Somit steckt der Fehler in deiner Formel, da sie zwei Ergebnisse erzeugt.
=WENN(MRabatt[Anzahl]=0;xxx:xxx) erzeugt dir zwei Werte, da MRabatt aktuell zwei Datensätze enthält.
Wenn du in deiner Version die Formel mit Strg-Shift-Enter abschließt und in diesem Moment die Zelle darunter in diesem Moment belegt ist, dann passiert das selbe wie im altem Excel, nämlich dass das zweite Ergebnis einfach verworfen wird. Ist die Zelle frei, dann wird diese automatisch mit belegt und das zweite Ergebnis dort angezeigt. Dann kannst du diese Zelle auch nicht mehr ändern.

 

Hallo Helmut,

Danke dir auch sehr.

Dann ist es besser =WENN(Preise!L2=0; xxxx zu benutzen mit einem direkten Bezug zu einer Zelle?

Gruss Stephan

 

Ja genau. Oder um konsistent zu bleiben =WENN(INDEX(MRabatt[Anzahl];1)=0;xxx;xxx) Dann kannst du deine Tabelle nämlich verschieben wie du willst. Überhaupt solltest du dich selbst darauf einigen, ob du die Tabellen-Schreibweise oder normale Zell-Adressen verwendest. Das sollte man dann auch durchgehend einhalten.

 

Hallo Helmut,

ich danke dir sehr.

Ich habe erst angefangen mich mit den Tabellen auseinander zusetzen, ich weiss auch, das meine Formeln noch Verbesserungswürdig sind. Es ist für mich noch vieles Neuland und deswegen das gewohnte noch, bin aber bemüht das zu optimieren.

gruss stephan

 

Hallo,

ich habe dir in der beiliegenden Datei modifizierte Formeln eingebaut, die ebenfalls die bereits angesprochenen Probleme vermeiden (sollten).

In der Tabelle "Preise" habe ich eine Zusatzspalte eingefügt (ob Mengenrabatt möglich oder nicht).
Im Arbeitsblatt "Rechnung" habe ich in den Spalten N und O Hilfsformeln eingefügt, um unnötige Mehrfachberechnungen in den Formeln zu vermeiden.
Wie weit die Rabattberechnungen deinen Vorstellungen entsprechen, weiß ich nicht, da dazu dich bisher nicht geäußert hast.
Auch wusste ich nicht, welche Excel-Version du dann wirklich hast: Angegeben hast du Excel2016 - aber wenn du in den Rabattzellen einen Überlauf-Fehler hattest, musst du Excel2021 oder Excel365 besitzen.
Da der Zellbereich B27:L27 im Rechnungsblatt bereits zur struktur.Tabelle "Rechnung" gehört, habe ich diese "Leerzeile" mit in die Formeln genommen - die Formeln würden sonst inkonsistent.

 

Hallo Anton,

vielen Dank erstmal, ich werde mir das heute abend in Ruhe anschauen.
Ich selbst habe Office 365 Buissness, aber mein Kumpel hat nur Office 2016, deswegen muss ich bei den Formeln immer aufpassen.

Zu den Rabatten, habe ich das bisher eingebaut, was ich in seinen Rechnungen so gefunden habe. Wollte den Mengenrabatt flexible gestalten, bei meinen ersten Formeln gab es ein Problem wenn ich zum testen nur einen Rabatt anstatts 2 erfasst hatte.

ich danke dir für deine Mühe,

gruss Stephan

 

Hallo Anton,

vielen Dank sieht echt super aus, habe es auch getestet und funktioniert wie es sein soll! Nur ist berechnet er bei der Zeile 52 nicht mehr den Mengenrabatt

Gruss stephan

 

Hallo,

Das liegt an 2 Problemen mit der Formel in Zelle N52:
=SUMME(WENNFEHLER(SVERWEIS(Rechnung[Bezeichnung];Preise;6;0);0)*Rechnung[Anz.])

Problem 1:
Du hast sie geändert auf:
=SUMME(WENNFEHLER(SVERWEIS(Rechnung[Bezeichnung];Preise;6;1);0)*Rechnung[Anz.])
Das darfst du auf keinen Fall tun, denn dann wird mit hoher Wahrscheinlichkeit der SVERWEIS falsch berechnet.

Problem 2:
Die Formel funktioniert tadellos (in Excel365), so wie ich sie oben angeführt habe. Da ich kein Excel2016 besitze, kann ich nicht testen, ob sie auch dort so funktioniert oder ob sie dort zwingend als Array-Funktion (mit geschweiften Klammern) einzugeben ist:
{=SUMME(WENNFEHLER(SVERWEIS(Rechnung[Bezeichnung];Preise;6;0);0)*Rechnung[Anz.])}
Wenn ich mit meinem Excel365 das tue, erhalte ich als Resultat 0 - warum auch immer, ich weiß es nicht.
Funktionieren müsste ebenfalls auch folgende Formel:
=SUMMENPRODUKT(WENNFEHLER(SVERWEIS(Rechnung[Bezeichnung];Preise;6;0);0)*Rechnung[Anz.])

Falls auch diese Formel in Excel2016 Probleme verursacht, dann müsste im Zeilenbereich der Tabelle "Rechnung" zB. in Spalte O (Zellen O27:O46) folgende Formel ohne SUMME bzw. SUMMENPRODUKT eingegeben werden:
=WENNFEHLER(SVERWEIS(Rechnung[Bezeichnung];Preise;6;0);0)*Rechnung[Anz.]
und dann in Zelle N52 stattdessen die Formel (ohne Array-Klammern):
=SUMME(O27:O46)

 

Danke habe es angepasst nur, wird bei der Formel K52 nur der Wert K50 berücksichtigt nicht die Summe von den Werten die es dürften
gruss stephan

 

Ich konnte nach langem probieren, das letzte Problem auch anhand einer neuen Hilfsspalte lösen
danke euch allen
Gruß Stephan