Einzelne Formelteile per Makro als Zahl darstellen

[christianheller]

Hallo Zusammen,

ich werde leider nicht fündig und weis selbst nicht mehr weiter.
Wir arbeiten mit einer großen Excel-Datei, in welcher in vielen einzelnen Arbeitsblättern unterteilt nach Bereichen die Jahreszahlen und Vorjahreszahlen erhoben werden.
In einem eigenen Blatt ("Arbeitsblatt" werden diese dann "verarbeitet", wobei der erste Schritt daraus besteht, den Durchschnittswert der jeweiligen Ansätze zu ziehen.

Beispiel: in Blatt 1
Anzahl Gewerbesteuerbescheide 2018: 150 (Zelle C1 - als Beispiel)
Anzahl Gewerbesteuerbescheide 2019: 170 (Zelle D1)

im "Arbeitsblatt" ist nun eine Zelle, die sich die beiden Werte aus Blatt 1 zieht und die gerundete Hälfte ausgibt. Das ganze mit folgender Formel:

=RUNDEN('Blatt 1!C1+'Blatt 1!D1)/2;0)

Soweit so gut. Unser Problem besteht nun dabei, dass es bei einzelnen Ansätzen interessant ist, wie der "Verlauf" also der Unterschied zwischen den Jahren ist, bzw. ob diese gesunken oder gestiegen sind.

Die uncharmante Lösung: Ich wechsel in das Blatt 1 und schaue mir die Werte an, wechsel dann zurück in das Arbeitsblatt. Ist mir bei der Masse der Ansätze zu aufwändig.

Mein Gedanke war nun, die einzelnen Formelbestandteile (z.B. 'Blatt 1!C1) mittels F9-Taste in Werte umzuwandeln, so dass am Ende =RUNDEN(150+170)/2;0) in der Formelzeile steht. Aber auch das ist sehr aufwändig, da ich bei jedem Ansatz in die Formelzeile muss, den einzelnen Bestandteil markieren muss (schauen dass ich nichts vergesse), F9 drücken, nächsten Bestandteil markieren, .....

Ich suche nun nach einem Makro, welches

• Einen gewissen Bereich durchgeht (z.B. im Arbeitsblatt den Bereich C1:C250 (da stehen die Formeln)
  
• wenn in der Zelle eine Formel steht die einzelnen Verweise in Zahlen umwandelt (NICHT jedoch das ganze Formelergebnis, siehe unten)
  
• zur nächsten Zelle springt
  
• wiederholt
  

Ist so etwas möglich und hat jemand eine Idee?

Ich finde bisher nur Ansätze, wie man die ganze Zelle / ganze Formel in einen Wert umwandeln kann, das bringt mir jedoch nichts, weil ich dann in der Beispielzelle nur "160" stehen habe, aber keinen "Verlauf".

Ich bin für jede Idee dankbar :)

Viele Grüße
Christian

[Klaus-Dieter]

Hallo Christian,

es ist schon immer "verdächtig" wenn für ein und die selbe Sache mehrere Tabellenblätter verwendet werden. Zeige doch mal eine anonymisierte Beispieldatei mit wenigen Datensätzen, dann können wir sicher konstruktiver Vorschläge erstellen, wie man das optimieren kann.

[Fennek]

Hallo,

der erste Schritt sollte es sein die beiden Zahlen zu extrahieren:

Aus dem String

Anzahl Gewerbesteuerbescheide 2018: 150
Anzahl Gewerbesteuerbescheide 2019: 170

hilft die UDF

Code:

function Extrakt(rng)
    Tx = split(rng)
    ub = ubound(Tx)
    Extrakt = Tx(ub-1) & Tx(ub)
end function

Das Ergebnis "2018: 150" muss auch noch auf zwei Zellen aufgeteilt werden, damit gerechnet werden kann. (z.B. mit "Text-in-Spalten")

mfg

[LCohen]

=PRODUKT(RECHTS(WECHSELN(C1:D1;" ";WIEDERHOLEN(" ";99));99)^{-1.1})-1 Anstieg
=RUNDEN(SUMME(RECHTS(WECHSELN(C1:D1;" ";WIEDERHOLEN(" ";99));99)/2);) Mittel

[christianheller]

Hallo Zusammen,
vielen Dank schon mal für die schnellen Antworten.

Anbei wie von Klaus-Dieter gewünscht eine Beispielsdatei. In Tabelle 1 ist die Abfrage, diese füllen andere Stellen aus. In Tabelle 2 ist dann ein Ausschnitt aus dem Arbeitsblatt, mit diesem Blatt arbeiten dann wir weiter. Hier sehe ich aktuell (Beispiel Gesamtpflichtige) nur das Ergebnis 2325 Fälle. Ich hätte gerne auf einen Blick in die Zelle/Formel die hinterlegten Zahlen gesehen, um zu sehen ob hier eine geringe Spannweite oder eine große Differenz zwischen den Jahren ist, bzw. auch ob es vom Vorjahr zum Vorvorjahr weniger oder mehr wurden. Ich hätte also einfach gerne gesehen dass das Ergebnis 2.325 aus den Einzelansätzen 2.500 (2018) und 2.150 (2019) Fällen ermittelt wurde.

Aktuell muss ich hierzu erst in das Blatt Tabelle 1 wechseln um das zu sehen, oder mir in der Formel in Tabelle 2 über F9 die einzelnen Werte herholen, dies jedoch dann für jeden Ansatz einzeln.

@LCohen. Die zweite Formel =RUNDEN((Tabelle1!C7+Tabelle1!D7)/2;0) ergibt bei mir eine Fehlermeldung, dass eine Klammer zuviel oder zuwenig ist. Wenn ich - da wo ich denke - noch Klammern setze kommt die Fehlermeldung, dass zu wenige ARgumente angegeben sind. Ich kenne nun den Wiederholen-Teil leider nicht genug, um zu sehen ob hier wirklich Argumente fehlen oder was der Fall ist?

LG
Christian

[LCohen]

Tippst Du Formeln ab? Außerdem: Warum stehen da jetzt Zahlen statt Strings? Wozu dann mein Aufwand?

[Klaus-Dieter]

Hallo,

wer oder was hindert dich an dieser Vorgehensweise?

Tabelle2

	A	B	C	D
7	Gewerbesteuer	Fälle 2018	Fälle 2019
8	Gesamtpflichtige	2500	2150	2.325
9	Erhebungsverfahren (Festsetzung und Änderungen)	120	100	110
10	Erhebungsverfahren (Festsetzung Nullbescheide)	50	60	55
11	Widersprüche bearbeiten	2	5	4

Formeln der Tabelle

Zelle Formel B7 =SVERWEIS(Tabelle2!$A7;Tabelle1!$B$6:$D$10;SPALTE(B1);FALSCH) C7 =SVERWEIS(Tabelle2!$A7;Tabelle1!$B$6:$D$10;SPALTE(C1);FALSCH) B8 =SVERWEIS(Tabelle2!$A8;Tabelle1!$B$6:$D$10;SPALTE(B2);FALSCH) C8 =SVERWEIS(Tabelle2!$A8;Tabelle1!$B$6:$D$10;SPALTE(C2);FALSCH) D8 =MITTELWERT(B8:C8) B9 =SVERWEIS(Tabelle2!$A9;Tabelle1!$B$6:$D$10;SPALTE(B3);FALSCH) C9 =SVERWEIS(Tabelle2!$A9;Tabelle1!$B$6:$D$10;SPALTE(C3);FALSCH) D9 =MITTELWERT(B9:C9) B10 =SVERWEIS(Tabelle2!$A10;Tabelle1!$B$6:$D$10;SPALTE(B4);FALSCH) C10 =SVERWEIS(Tabelle2!$A10;Tabelle1!$B$6:$D$10;SPALTE(C4);FALSCH) D10 =MITTELWERT(B10:C10) B11 =SVERWEIS(Tabelle2!$A11;Tabelle1!$B$6:$D$10;SPALTE(B5);FALSCH) C11 =SVERWEIS(Tabelle2!$A11;Tabelle1!$B$6:$D$10;SPALTE(C5);FALSCH) D11 =MITTELWERT(B11:C11)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[christianheller]

Bisher nur der Aufbau der Datei. War aber auch schon ein Gedanke, quasi wie du vorschlägst noch zwei Spalten einzufügen, dort über den SVerweis die Zahlen zu integrieren und dann daraus die Zahlen für die Formeln zu ziehen.

Dann werd ich es mal dahingehend umstellen.

Vielen Dank!