☰

Addition der Formelbestandteile (Netto vs. Brutto)

tag-icon • tag-icon microsoft-excel worksheet-function
Addition der Formelbestandteile (Netto vs. Brutto)

Ich möchte die Komponenten einer Formel über einen Bereich hinweg addieren. Wir haben einen Abschnitt, der die Netto-Cashflows für bestimmte Artikel und mehrere bestimmte Artikel anzeigt.

Beispielsweise =8+9-5wird „12“ über mehrere Zellen hinweg angezeigt. Wenn die Werte nicht auf Zellenebene saldiert würden, wäre es einfach, eine SUMIFFormel zu verwenden, um nur die positiven und negativen Zellen zu addieren. Da die Saldierung jedoch auf Zellenebene erfolgt, versuche ich, die zugrunde liegenden Werte zu untersuchen und das Äquivalent der SUMIFFormel, jedoch für alle zugrunde liegenden Komponenten, zu verwenden.

Für das obige Beispiel versuche ich, eine Zusammenfassungszelle für den Bereich mit „+17“ und eine Zelle mit „-5“ zu erhalten.

Wie kann ich das machen?

Antwort1

Das Hauptproblem besteht darin, wie auf die Formelzeichenfolge einer anderen Zelle im Arbeitsblatt zugegriffen werden kann. Es gibt keine vorhandene Arbeitsblattfunktion, die dies kann. Sie müssen also zunächst eine der folgenden zwei Möglichkeiten nutzen: 1) Verwenden Sie die Funktion „Suchen und Ersetzen“, um den Inhalt der Zellen so zu ändern, dass sie keine Formeln mehr, sondern Zeichenfolgen enthalten, oder 2) Verwenden Sie VBA, um auf die Formelzeichenfolge der Zellen zuzugreifen.

(1) kann erreicht werden, indem Sie die Zellen auswählen, die Sie bearbeiten möchten, und dann Ctrl+ drücken H, um das Dialogfeld „Ersetzen“ zu öffnen. Suchen Sie nach =(vorausgesetzt, die einzigen Formeln, die Sie interessieren, sind wie in Ihrem Beispiel und enthalten keine nachfolgenden Gleichheitszeichen) und ersetzen Sie durch nichts. Dadurch wird eine Zelle mit der Formel, die =8+9-5den Wert 12 anzeigt, in eine Zelle geändert, die die Zeichenfolge anzeigt 8+9-5.

(2) kann durch Zugriff auf die Zelleneigenschaft erreicht werden .Formula. Um beispielsweise die Formel der Zelle A1 zu erhalten, würden Sie eine ganz einfache UDF wie diese verwenden:

Public Function GetFormula(r as Range) as String
   GetFormula = r.Formula
End Function

Sobald Sie diese Zeichenfolgen haben, müssen Sie mit beiden Methoden geschickt mit Zeichenfolgenmanipulationsfunktionen umgehen, z. B. FIND, LEN, und VALUE, um die gewünschten Werte zu extrahieren. Da dies sowohl für Sie als auch für mich Zeitverschwendung wäre, verzichte ich hier darauf.

Antwort2

Fügen Sie Ihrer Arbeitsmappe ein Blatt hinzu; nennen wir es „Setup“.

Geben Sie in A1 ein 8, in A2 ein 9, in A3 ein -5. Kopieren Sie so viele Spalten, wie diese Formel mit Konstanten in den Spalten Ihres Originalblatts vorhanden ist. (Je nachdem, wie der Bereich organisiert ist, müssen Sie das Einrichtungsblatt möglicherweise so einrichten, dass es A1, B1, C1 verwendet und stattdessen nach unten kopiert. Die Idee besteht darin, einen Bereich zu erstellen, der der Form des Bereichs auf dem Originalblatt entspricht.)

Ersetzen Sie auf dem Originalblatt alle Formeln =8+9-5durch =Setup!A1+Setup!A2+Setup!A3. Dies kann mit einer Suchen-und-Ersetzen-Operation erfolgen, die die Formeln auswertet.

Jetzt können Sie bedingte Formeln auf dem Setup-Blatt anstelle der fest codierten Werte im Originalblatt erstellen.

=sumif(Setup!A1:A3,">0")

=sumif(Setup!A1:A3,"<0")

verwandte Informationen