Zellwert nummerisch automatisch als negative Zahl behandeln?
Hallo Zusammen,
In einer Exceltabelle habe ich in Spalte A einen Code der für die ganze Zeile gelten soll, also z.B. in Zelle A20 den Eintrag LKU. Wenn nun in dieser Zelle dieser Code steht sollen in allen Zellen von D20 - BG20 die Eingabe von Zahlenwerten als negative Zahl behandelt werden ohne dass die Eingabe mit Negativzeichen erfolgt.
Lässt sich das mit einer Formel lösen?
Besten Dank zum Voraus und beste Grüsse
Rolf
3 Antworten
Nach Kommunikation mit dem Fragesteller neue mögliche Antwort:
durch den Bereich.Verschieben Teil beziehst du dich quasi immer auf die umgewandelten Daten, das funktioniert sowohl im simplen Formel wie Summe (B7 bis G7, als auch mit modernen Array Formeln wie Nachzeile AY2 bis AY6.
Die genutzten Formeln kannst du der Liste unten entnehmen.
Die Spalten H bis AX habe ich ausgeblendet, da ich sonst nicht alles auf ein Bild bekomme.
Da ich mir jetzt keine Daten aus den Fingern saugen wollte, habe ich einfach in B2 eine Zahlenmatrix erstellt.
----- Alte Antwort (veraltet)
kommt drauf an ob ich deine Frage richtig verstehe
du hast in A20 eine Code wenn dieser mit einer bestimmten Zeichenkette übereinstimmt, dann sollten alle Zahlen in einer davon losgelösten Tabelle als negative Werte betrachtet werden.
Die Frage die sich mir nun stellt soll die 20 als -20 auch dargestellt werden oder willst du nur, dass von eine externen Zelle aus, die diesen Werten arbeiten muss, die Werte als negative verarbeitet werden. Dies wirst du du nur via VBA erledigen können, reicht es dir hingegen die Werte woanders als negative zu verarbeiten, dann geht das recht simpel.
Noch einfacher ging es mit:
=B2:B21*-(A2:A21="LKU")
Als Ergänzung durch die Anregung von @GutenTag2003 und @evtldotcha hier die Bebilderte Idee, leider
jetzt wäre die Frage, wie sich das Format von Excel vielleicht so manipulieren lässt wie mit der Formel Text. Wenn das geht, wäre dies die smarteste Lösung ohne zusätzliche Hilfsspalten
Gut wenn die Zahlen nur zur weiteren Berechnung Bereit stellen sollen, dann kannst du die in den folgenden Berechnungen einfachen ins negative Wandeln. Wie du für optische negativ Darstellung sorgen kannst, habe die ja schon andere User gezeigt, ansonsten wie gesagt hier nochmal.
Format und -0;-0;-0 eingeben, dann werden sowohl negative, als auch positive und die 0 negativ dargestellt, wobei das -Zeichen vor der ebenfalls dargestellt wird.
Ich könnte mir also folgendes Vorstellen du hast in Spalte A deinen Code LKU oder eben auch nicht in Spalte B stellt du deine Zahlen negativ dar, wenn der Code übereinstimmt, dies nur für dich. Spalte C trägst du folgendes sein:
=WENN((A2="LKU")*(B2<>"");TEXT(B2;"-0;-0;-0")*1;WENN(B2<>"";B2*1;""))
Sofern du unbedingt die 0 vermeiden willst, wenn vor die Zelle ist, ist dir das nicht wichtig, kann das zweite Wenn und auch *(B2<>"") weglassen.
Im Anschluss würde ich diese Spalte ausblenden und für alle Berechnungen nehmen.
Also Anstelle =summe(B2:B50) kommt nun =summe(C2:C50), wenn du lieber bei der von Feldern klickst was ja nun in die Spalte C, da sie ausgeblendet ist schwer möglich ist kannst du mit Formeln wie Bereich verschieben arbeiten.
=SUMME(BEREICH.VERSCHIEBEN(B2:B6;;1))
Alternativ statt immer die nachfolgende Spalte auszublenden, was ich eher doof find und sei es nur für das kopieren von Werten, da du ja schreibst, dass deine Tabelle bis BG geht, halte ich es für die Elegantere Varianten die Spalten irgendwo an das Ende der Tabelle zu machen, also Spalte IB du hast nun also einen Festen Abstand zwischen B und IB; C zu IC etc. in diesem Falle wären das 234 Spalten =Spalte(IB:IB)-Spalte(B:B)
Nun kannst du die Bereich Verschieben Formel von Oben verwenden und anstatt 1 einzutragen wie bei mir oben trägst du entweder Fest 234 ein oder verwendest die Spaltenformel, da die sich dynamisch anpasst, wenn du mal ganze Spalten aus dem Arbeitsblatt löschst. Wenn ich dir das nochmal visualisieren soll, dann gib kurz bescheid, dann hänge ich noch eine Antwort hinzu.
Kommt drauf an, was du mit diesen Werten vor hast.
Wenn du eine Formellösung suchst, würde ich mir die entsprechenden Werte in einen anderen Teil der Tabelle kopieren und mit der Kopie weiter arbeiten:
Z.B, in BH20:
=WENN(A20="LKU";-D20;D20)
Und diese Formel ziehen/kopieren
.
.
Antwort zurückgezogen.
Die Folgen wären, nachfolgende Rechenoperationen ergeben keine brauchbaren Ergebnisse mehr.
Damit bleiben die Werte in diesen Zellen aber als Minuswert
... ist das mit Excel so? (Ist als ernste Nachfrage interessehalber gemeint)
Ich gehe normalerweise von einer Trennung von Format und Inhalt aus und daher würde ich mal annehmen, dass der Zellinhalt unabhängig von dessen Präsentation (Zellformat) weiterhin ein positiver Wert bleibt (Rechnen könnte man damit dann nicht und wenn ich das in LibreOffice so mache, kommt beim Rechnen auch nicht das heraus, was man vom bloßen Anschein erwarten würde).
Nicht wenn die Zellen als - formatiert sind ... und macht auch Probleme bei anderen, folgernden Rechenoperationen
Nicht wirklich zu empfehlen
Danke, aber ganz verstehe ich es immer noch nicht.
Das heißt, der Eingabe-Algorithmus wandelt basierend auf einem vorhandenen Format den Zahlenwert echt um und macht den Zahlenwert tatsächlich negativ? Komplett weird in meinen Augen ....
so jetzt versucht Nummer 2:
So ganz unsmart finde ich deine Idee nicht, leider musste ich gerade feststellen, Excel ist leider inkonsistent bei der Behandlung von Formaten:
Wenn ich das Format ob manuell oder mit Bedingter Formatierung auf -0;-0;-0 ändere und rechne im Anschluss mit dem Wert z.B. (optisch) -5 * 1 erhalte ich 5.
Verwende ich aber die Formel =Text(A1;"-0;-0;-0") in A1 steht 5 und rechne dieses Ergebnis dann * 1 erhalte ich -5.
Ich hänge das mal als Ergänzung unter meine Antwort an Bilder sagen mehr als 1000 Worte
Vielen Dank Gipfelstürmer,
Ja, das geht in die richtige Richtung. Die negativen Werte in den Zellen D20:BG20 sollen auch als negative Zahlen für Berechnungen bereit stehen. Ergänzung - nicht jede Zelle von D20:BG20 wird mit einem Wert gefüllt. Es hat also in diesem Bereich auch leere Zellen.