Excel - Leere Zellen als 0 rechnen
Hallo, ich habe folgendes Problem: Ich muss in Excel für ca. 4.000 Materialien die Bedarfe berechnen (Spalte I). Hierzu sind die Materialien in einer Spalte aufgeführt (A). In den danebenstehenden Spalten (B:H) sind die jeweiligen Produkte (Produkt 1 - Produkt 7) mit den jeweiligen Bedarfen (B2:H2) aufgeführt. Die Häufigkeit, wie oft ein Material in ein Produkt eingeht ist in der Matrix (B5:H4032) vermerkt.
Nun ist mein Problem: Wenn ich den Bedarf je Material berechne, wird mir immer eine Fehlermeldung (#WERT) ausgegeben. Ich vermute, dass die leeren Zellen nicht als 0 sondern als fehlerhafter Wert gerechnet werden.
Wie kann ich nun die Zellen so formatieren, dass eine leere Zelle als 0 gerechnet wird?
Meine Formel mit der ich gerechnet habe sieht wie folgt aus: =B5B2+C5C2+D5D2+E5E2+F5F2+G5G2+H5*H2
Die Ideallösung für mich wäre, wenn die leeren Zellen weiterhin leer bleiben, jedoch in der Berechnung als 0 berücksichtigt werden. Kann mir jemand sagen wie ich das hin bekomme?
Ich habe das Tabellenblatt zur Veranschaulichung als Bilddatei beigefügt.
Danke schon einmal im Voraus!
4 Antworten
meinst du die Formel so:
= B5 * B2 + C5 * C2 + D5 * D2 + E5 * E2 + F5 * F2 + G5 * G2 + H5 * H2
Möglicherweise hilft Dir der Ansatz aus http://www.office-loesung.de/ftopic383819_0_0_asc.php weiter. Die Funktion "Summewenn" kennst Du vermutlich.
Leider nein - so lässt sich nur die Summe der Zeile/Spalte berechnen. Da ich jedoch für jedes Produkt unterschiedliche Bedarfe habe funktioniert das leider nicht. :(
Trotzdem vielen Dank für die schnelle Hilfe!
Erfasse in I5 folgende Formel =SUMMENPRODUKT(($B$2:$H$2)*(B5:H5))
B:H steht hier für den gesamt Bereich bei 150 Produkten also =SUMMENPRODUKT(($B$2:$EU$2)*(B5:EU5))
Diese Formel nimmt Leerzellen als 0 an, ist relativ kompakt und kann heruntergezogen werden.
Gruß Chris
Ergebnis: #WERT Ist aber schonmal der Schritt in die richtige Richtung - Sehe ich mir die einzelnen Berechnungsschritte an, dann bekomme ich folgendes Ergebnis: SUMMENPRODUKT({#WERT!,#WERT!, ... ,49000, ... ,#WERT!)
Die 49.000 entspricht hierbei dem Produkt, welches berechnet werden sollte. Alle Als "#WERT!" dargestellten Berechnungen sind meiner Meinung nach auf eine Berechnung ohne eine "0" also mit einer leeren Zelle zurückzuführen.
Die Formel an sich ist schonmal klasse! Danke dafür! Siehst du noch eine Möglichkeit die Leeren Zellen als 0 zu berechnen? Wenn ich die Formel in der Beispiel-Tabelle anwende funktioniert sie. Da ich in meiner original-Tabelle die Werte aus anderen Tabellen "reinkopiert" habe ist hier wohl ein anderes Format vorhanden. Wie kann ich dieses nun entsprechend meiner Anforderungen ändern?
Das bedeutet die Zellen enthalten Text. Enthalten die Zellen wirklich einen Leerstring oder vielleicht Leerzeichen die nur nicht sichtbar sind?
Die Zellen sind schon alle durch GLÄTTEN bereinigt worden - also nichts mit versteckten Leerzeichen -.-
Versuch es mal hiermit:
{=SUMMENPRODUKT(($B$2:$EU$2)*(WENN(B5:EU5="";0;B5:EU5)))}
ACHTUNG! Das ist eine Matrixformel. Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit: STRG & SHIFT & ENTER (alle drei gleichzeitig). DAS erzeugt die {} und macht die Formel zu einer Matrixformel.
Erst nach dem Abschliessen die Formel runterziehen
und genau das ist das Problem, GLÄTTEN ist eine Textformel und diese erzeugt immer Text. Also einen Leerstring. Mit der ArrayFormel sollte es aber klappen. Daher nach dem Glätten immer mit 1 multiplizieren. Dadurch wird wieder eine Zahl daraus
Also =GLÄTTEN(A1)*1
Deine Vermutung ist richtig. Das kannst du relativ einfach über Wenn/Dann realisieren.....also freg in der Berechnung einfach dasentsprechende Feld mit ab also =Wenn I1 LEER;0;I1.........und hängst dann deine Berechnung an
Naja du brauchst sie ja nur einmal tippen und kannst sie dann über die Kopierfunktion nach unten ziehen (hoffe du weist was gemeint ist) der Bezug auf die Felder wird automatisch erstellt wenn in der ersten Formel kein absoluter Bezug festgelegt wird ($I1$)
klar - allerdings nimmt das erstellen der Formel erheblich Zeit in Anspruch da ich bis zur Spalte FB jeweils eine WENN-Funktion in der Fornel unterbringen muss.
... ich dachte da komme ich mit einer Kürzeren Formel aus :S Dann werde ich mich mal ans eintippen machen...
Achso - eine Frage noch: Habt ihr alle lange Weile oder warum beantwortet ihr innerhalb von 10 Minuten meine Frage? Ich mein 1. ist Fragen beantworten doch sicher nicht unterhaltsam und 2. geht es hier um eine mega trockene Excel-Frage... Also warum macht ihr das??
--> nicht das ich etwas dagegen hätte ;)
Habe die Formel jetzt für die ersten paar Zellen geschrieben. Wenn ich allerdings entern will, um mir das Zwischenergebnis anzeigen zu lassen kommt eine Fehlermeldung das die Formel zu lang ist. Was kann ich nun tun? :S
SUMMENPRODUKT verwenden und sich ne Menge Tipparbeit sparen ggg
Danke erstmal! --> Funktioniert.
Allerdings ist das Beispiel nur eine vereinfachte Darstellung. Die echte Tabelle hat >150 Produkte. Muss ich nun für jedes Produkt die WENN Funktion anwenden? Das ist nämlich ne ganze Menge zu Tippen :S
Formel: =WENN(B5="";0;B5B2)+WENN(C5="";0;C5C2)+....
Gibt es noch eine andere Möglichkeit?
Oh ja - da wurde der Multiplikator wohl nicht übernommen :S