Netz-Treff - Excel Formel verschieben, ohne die Bezüge zu verschieben

#436670

MudGuard

München,
04.02.2020, 16:37:11

Excel Formel verschieben, ohne die Bezüge zu verschieben (pc.sw.office)

Hi,

ich habe in verschiedenen Zellen der Spalte A Formeln stehen, die sich auf Bereiche der Spalte B beziehen.
Sowas:
in A4: =ZÄHLENWENN(B2:B29;"<="&JAHR(HEUTE()))
in A32: =ZÄHLENWENN(B30:B42;"<="&JAHR(HEUTE()))
usw.

Wie bekomme ich diese Formeln an eine vollkommen andere Stelle (A4 -> J29, A32 -> J42)?

Wenn ich das einfach kopiere, dann landet in J29 die Formel =ZÄHLENWENN(K27:K54;"<="&JAHR(HEUTE())) - der gezählte Bereich ist also was vollkommen anderes.

Ich bekomme das momentan nur hin, wenn ich Zelle A4 auswähle, dann oben in der "Adreßleiste" (?) die Formel mühsam kopiere, dann Escape drücke, dann die Zelle J29 auswähle und dort einfüge.

Geht, ist aber, weil es viele Zellen sind, sehr mühsam.

Gibt's da was einfacheres?

Danke,
Andreas

--

MudGuard
O-o-ostern

#436671 UMPirx 04.02.2020, 17:38:02 @ MudGuard	Excel Formel verschieben, ohne die Bezüge zu verschieben antworten
	Hallo, ein $ vor Spalte oder/und Zeile verhindert beim kopieren die Änderung: =ZÄHLENWENN($B$2:$B$29;"<="&JAHR(HEUTE())) =ZÄHLENWENN($B$30:$B$42;"<="&JAHR(HEUTE())) Viele Grüße Uwe

#436672

Howie

04.02.2020, 18:22:20
(editiert von Howie, 04.02.2020, 18:22:36)

@ UMPirx

Ergänzung (ed)

antworten

> Hallo,
>
> ein $ vor Spalte oder/und Zeile verhindert beim kopieren die Änderung:
>
> =ZÄHLENWENN($B$2:$B$29;"<="&JAHR(HEUTE()))
> =ZÄHLENWENN($B$30:$B$42;"<="&JAHR(HEUTE()))

Ergänzung:
Die Funktionstaste F4 "verpackt" die Zelladressen mit $, wenn die Einfügemarke in/an der Zelladresse steht.
1x F4 = $B$2
2x F4 = B$2
3x F4 =$B2
4x F4 =B2
Das erleichtert die Arbeit etwas, falls dir dieser Lösungsvorschlag mit der absoluten Zelladressierung weiterhilft.

--
Viele Grüße
Howie

#WirSindMehr
Für Demokratie, Frieden und Freiheit. Und gegen Gewalt, Hass und Hetze.
_____________________________________________________
"Die Zeichnung eines Kindes wird in 1000 Jahren eine
größere Antiquität sein als der teuerste Computer."

#436673

MudGuard

München,
04.02.2020, 19:00:26

@ UMPirx

Excel Formel verschieben, ohne die Bezüge zu verschieben

antworten

> ein $ vor Spalte oder/und Zeile verhindert beim kopieren die Änderung:
>
> =ZÄHLENWENN($B$2:$B$29;"<="&JAHR(HEUTE()))
> =ZÄHLENWENN($B$30:$B$42;"<="&JAHR(HEUTE()))

das hieße, daß ich jede Formel erst mal bearbeiten müßte, dann verschieben in die neue Zelle, dann wieder bearbeiten (damit die Formel danach wieder angepaßt wird, wenn mal wieder eine Zeile/Spalte eingefügt/gelöscht wird).

Ich hab inzwischen eine Lösung gefunden (die Tabelle enthält nicht allzu viele Formeln außer den betroffenen Zellen, daher ging das einigermaßen):

Suchen und Ersetzen (mit "in Formeln suchen"), und zwar das '=' durch etwas, was nicht vorkommt, ich hab '#@#' genommen. "Alle Ersetzen".
Jetzt sind die Formeln nur noch Text.
Dann die betroffenen Zellen an die neue Stelle kopiert.
Und zum Schluß wieder '#@#' durch '=' ersetzt.

--

MudGuard
O-o-ostern

#436674

manes

Köln,
04.02.2020, 20:33:44

@ MudGuard

Excel Formel verschieben, ohne die Bezüge zu verschieben

antworten

Hallo
Vielleicht habe ich das nicht richtig verstanden. Dieser Satz
> Wenn ich das einfach kopiere, dann landet in J29 die Formel
> =ZÄHLENWENN(K27:K54;"<="&JAHR(HEUTE())) - der gezählte Bereich ist also
> was vollkommen anderes.
liest sich so, als sollten die Formeln in A2 usw. bestehen bleiben und dass in J29, J 30 usw. genau dieselben Berechnungen (für die Spalte B) erfolgen sollen.
Wenn dem so wäre, würde ein einfacher Bezug reichen. D.h. in Zelle J 29 die Formel = A4 reinschreiben und nach unten kopieren (ziehen).

FFPX
Manes

--
Also wenn ich ehrlich sein sollte, müsste ich lügen!

#436677

MudGuard

München,
04.02.2020, 21:21:33

@ manes

Excel Formel verschieben, ohne die Bezüge zu verschieben

antworten

> liest sich so, als sollten die Formeln in A2 usw. bestehen bleiben und dass
> in J29, J 30 usw. genau dieselben Berechnungen (für die Spalte B) erfolgen
> sollen.

Ja, aber - anschließend soll in der Zelle, in der die Formel ursprünglich stand, was anderes rein -> Bezug geht nicht.

--

MudGuard
O-o-ostern

#436678

manes

Köln,
04.02.2020, 21:50:05

@ MudGuard

Excel Formel verschieben, ohne die Bezüge zu verschieben

antworten

Hallo

Gut das (vorher) zu wissen.
Vielleicht hilft dieses Makro weiter:

Sub KopiereFormeln()

Dim r_Quelle As Range
Dim r_Ziel As Range
Dim str_Formel As String
Dim int_Zeile As Integer

Set r_Quelle = Range("A4:A32")
Set r_Ziel = Range("J29")

For int_Zeile = 1 To r_Quelle.Rows.Count
str_Formel = r_Quelle.Cells(int_Zeile).Formula
r_Ziel.Cells(int_Zeile).Formula = str_Formel
Next

End Sub

FFPX
Manes

--
Also wenn ich ehrlich sein sollte, müsste ich lügen!