Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Absolute Bezüge herstellen
586 views
Skip to first unread message
Harald Friis
Jun 17, 2014, 6:38:08 AM6/17/14
to
Moin in die Runde,
eine Frage zu den absoluten und relativen Bezï¿œgen - fï¿œr Excel 2010, aber
das spielt vermutlich keine Rolle:
Bekanntermaï¿œen kann ich den Bezug mit Dollarzeichen absolut stellen. Nun
habe ich eine eher umfangreiche Tabelle, in der von einer Ausgangsformel
ca. 30 Spalten und 50 Zeilen diese Formel auto-ausgefï¿œllt werden kann.
Die Formel in A1 ist also (beispielhaft) [Wert aus einer anderen Tabelle
in der dortigen A1]*n. In B1 wï¿œre es dann das andere B1*n, in F5 das
andere F5*n. (Die Formel selbst bezieht sich auf eine
Summewenn-Berechnung, aber das ist fï¿œr das Prinzip ja egal.)
Diese nun auto-ausgefï¿œllte Tabelle mï¿œsste ich im nachhinein auf absolut
setzen (also mit Dollarzeichen versehen), da ein VBA-Code die Zeilen in
ein neues Blatt kopiert und ein Diagramm daraus baut.
Jetzt kenne ich derzeit zwei Mï¿œglichkeiten: ich nehme nicht
auto-ausfï¿œllen, sondern schreibe hï¿œndisch in jede Zelle die richtige,
absolut gesetzte Formel. Oder ich nehme das auto-ausfï¿œllen und packe mit
F4 in jede Zelle die Dollarzeichen. Beides ist eine Arbeit fï¿œr lange
Zeit, bei der sich die Wahrscheinlichkeit eines Nervenzusammenbruchs
nahe 1 befindet.
Gibt es also eine Mï¿œglichkeit, automatisiert bestehende Formeln absolut
zu setzen? Mit VBA oder gar per sonstiger Methode?
Danke fï¿œrs Lesen bis zum Ende und Danke^n fï¿œr Tipps und Hilfen.
Gruￜ
Harald Friis
eine Frage zu den absoluten und relativen Bezï¿œgen - fï¿œr Excel 2010, aber
das spielt vermutlich keine Rolle:
Bekanntermaï¿œen kann ich den Bezug mit Dollarzeichen absolut stellen. Nun
habe ich eine eher umfangreiche Tabelle, in der von einer Ausgangsformel
ca. 30 Spalten und 50 Zeilen diese Formel auto-ausgefï¿œllt werden kann.
Die Formel in A1 ist also (beispielhaft) [Wert aus einer anderen Tabelle
in der dortigen A1]*n. In B1 wï¿œre es dann das andere B1*n, in F5 das
andere F5*n. (Die Formel selbst bezieht sich auf eine
Summewenn-Berechnung, aber das ist fï¿œr das Prinzip ja egal.)
Diese nun auto-ausgefï¿œllte Tabelle mï¿œsste ich im nachhinein auf absolut
setzen (also mit Dollarzeichen versehen), da ein VBA-Code die Zeilen in
ein neues Blatt kopiert und ein Diagramm daraus baut.
Jetzt kenne ich derzeit zwei Mï¿œglichkeiten: ich nehme nicht
auto-ausfï¿œllen, sondern schreibe hï¿œndisch in jede Zelle die richtige,
absolut gesetzte Formel. Oder ich nehme das auto-ausfï¿œllen und packe mit
F4 in jede Zelle die Dollarzeichen. Beides ist eine Arbeit fï¿œr lange
Zeit, bei der sich die Wahrscheinlichkeit eines Nervenzusammenbruchs
nahe 1 befindet.
Gibt es also eine Mï¿œglichkeit, automatisiert bestehende Formeln absolut
zu setzen? Mit VBA oder gar per sonstiger Methode?
Danke fï¿œrs Lesen bis zum Ende und Danke^n fï¿œr Tipps und Hilfen.
Gruￜ
Harald Friis
Claus Busch
Jun 17, 2014, 7:01:51 AM6/17/14
to
Hallo Harald,
Am Tue, 17 Jun 2014 12:38:08 +0200 schrieb Harald Friis:
> Diese nun auto-ausgef�llte Tabelle m�sste ich im nachhinein auf absolut
nicht aus relativen Formeln erstellt werden k�nnen?
> Gibt es also eine M�glichkeit, automatisiert bestehende Formeln absolut
Formel. Du k�nntest mit Suchen und Ersetzen den ersten Teil erledigen
Suchen nach !A und Ersetzen durch !$A$.
Das Gleiche dann noch mit dem Doppelpunkt.
Suche nach :A und ersetzen mit :$A$
Oder du schreibst dir in VBA ein Array mit allen in den Formeln
vorkommenden Buchstaben und durchl�ufst dann alle Zellen des Blattes mit
Formeln und dieses Array und ersetzt die in der Formel gefunden
Buchstaben durch in Dollarzeichen eingepackte Buchstaben.
Gewisserma�en also finde A und ersetze durch $A$ und das f�r alle
Buchstaben.
Mit freundlichen Gr��en
Claus
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Am Tue, 17 Jun 2014 12:38:08 +0200 schrieb Harald Friis:
> Diese nun auto-ausgef�llte Tabelle m�sste ich im nachhinein auf absolut
> setzen (also mit Dollarzeichen versehen), da ein VBA-Code die Zeilen in
> ein neues Blatt kopiert und ein Diagramm daraus baut.
warum kopierst du hier nicht die Werte? Und warum soll ein Diagramm
> ein neues Blatt kopiert und ein Diagramm daraus baut.
nicht aus relativen Formeln erstellt werden k�nnen?
> Gibt es also eine M�glichkeit, automatisiert bestehende Formeln absolut
> zu setzen? Mit VBA oder gar per sonstiger Methode?
Da du auf andere Tabellen zugreifst, hast du das Ausrufezeichen in der
Formel. Du k�nntest mit Suchen und Ersetzen den ersten Teil erledigen
Suchen nach !A und Ersetzen durch !$A$.
Das Gleiche dann noch mit dem Doppelpunkt.
Suche nach :A und ersetzen mit :$A$
Oder du schreibst dir in VBA ein Array mit allen in den Formeln
vorkommenden Buchstaben und durchl�ufst dann alle Zellen des Blattes mit
Formeln und dieses Array und ersetzt die in der Formel gefunden
Buchstaben durch in Dollarzeichen eingepackte Buchstaben.
Gewisserma�en also finde A und ersetze durch $A$ und das f�r alle
Buchstaben.
Mit freundlichen Gr��en
Claus
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Harald Friis
Jun 17, 2014, 11:18:48 AM6/17/14
to
Hallo Claus,
Am 17.06.2014 13:01, schrieb Claus Busch:
> warum kopierst du hier nicht die Werte? Und warum soll ein Diagramm
> nicht aus relativen Formeln erstellt werden kï¿œnnen?
das Kopieren der Werte habe ich auch ï¿œberlegt. Allerdings mï¿œsste es bei
ï¿œnderungen immer neu erstellt werden. Wï¿œre eine Notlï¿œsung gewesen. Das
Diagramm kann man auch aus relativen Formeln erstellen. Bloￜ greift das
neue Blatt nicht mehr auf die echten Zahlen. Wenn in Zeile 10 auf die
Zeile 10 der ersten Tabelle zugegriffen wird, diese Zeile aber in die
Zeile 1 kopiert wird, dann klappt es halt nicht so, wie es soll. Das
lï¿œsst sich - entsprechende, bei mir nicht ausreichend vorhandene
VBA-Kenntnisse vorausgesetzt - sicher auch eleganter lï¿œsen. ;-)
> Da du auf andere Tabellen zugreifst, hast du das Ausrufezeichen in der
> Formel. Du kï¿œnntest mit Suchen und Ersetzen den ersten Teil erledigen
wunderbar. Herzlichen Dank.
Gruￜ
Harald Friis
Am 17.06.2014 13:01, schrieb Claus Busch:
> warum kopierst du hier nicht die Werte? Und warum soll ein Diagramm
das Kopieren der Werte habe ich auch ï¿œberlegt. Allerdings mï¿œsste es bei
ï¿œnderungen immer neu erstellt werden. Wï¿œre eine Notlï¿œsung gewesen. Das
Diagramm kann man auch aus relativen Formeln erstellen. Bloￜ greift das
neue Blatt nicht mehr auf die echten Zahlen. Wenn in Zeile 10 auf die
Zeile 10 der ersten Tabelle zugegriffen wird, diese Zeile aber in die
Zeile 1 kopiert wird, dann klappt es halt nicht so, wie es soll. Das
lï¿œsst sich - entsprechende, bei mir nicht ausreichend vorhandene
VBA-Kenntnisse vorausgesetzt - sicher auch eleganter lï¿œsen. ;-)
> Da du auf andere Tabellen zugreifst, hast du das Ausrufezeichen in der
> Suchen nach !A und Ersetzen durch !$A$.
> Das Gleiche dann noch mit dem Doppelpunkt.
> Suche nach :A und ersetzen mit :$A$
Gutes kann so einfach sein. Da wï¿œre ich nicht drauf gekommen. Es klappt
> Das Gleiche dann noch mit dem Doppelpunkt.
> Suche nach :A und ersetzen mit :$A$
wunderbar. Herzlichen Dank.
Gruￜ
Harald Friis
Claus Busch
Jun 17, 2014, 11:20:58 AM6/17/14
to
Hallo Harald,
Am Tue, 17 Jun 2014 17:18:48 +0200 schrieb Harald Friis:
> Gutes kann so einfach sein. Da w�re ich nicht drauf gekommen. Es klappt
> wunderbar. Herzlichen Dank.
mit VBA dann:
Sub Formel()
Dim rngC As Range
Dim myStr As String
Dim myArr As Variant
Dim i As Long
myStr = "A,B,C,D,E,F,G,H,I,J,K"
myArr = Split(myStr, ",")
With ActiveSheet
For Each rngC In .UsedRange
If rngC.HasFormula Then
For i = LBound(myArr) To UBound(myArr)
rngC.FormulaLocal =
Replace(Replace(Replace(Replace(rngC.FormulaLocal, _
"!" & myArr(i), "!$" & myArr(i) & "$"), ":" &
myArr(i), _
":$" & myArr(i) & "$"), ";" & myArr(i), ";$" &
myArr(i) & "$"), _
"(" & myArr(i), "($" & myArr(i) & "$")
Next
End If
Next
End With
End Sub
Achte auf die Zeilenumbr�che!
Am Tue, 17 Jun 2014 17:18:48 +0200 schrieb Harald Friis:
> Gutes kann so einfach sein. Da w�re ich nicht drauf gekommen. Es klappt
> wunderbar. Herzlichen Dank.
mit VBA dann:
Sub Formel()
Dim rngC As Range
Dim myStr As String
Dim myArr As Variant
Dim i As Long
myStr = "A,B,C,D,E,F,G,H,I,J,K"
myArr = Split(myStr, ",")
With ActiveSheet
For Each rngC In .UsedRange
If rngC.HasFormula Then
For i = LBound(myArr) To UBound(myArr)
rngC.FormulaLocal =
Replace(Replace(Replace(Replace(rngC.FormulaLocal, _
"!" & myArr(i), "!$" & myArr(i) & "$"), ":" &
myArr(i), _
":$" & myArr(i) & "$"), ";" & myArr(i), ";$" &
myArr(i) & "$"), _
"(" & myArr(i), "($" & myArr(i) & "$")
Next
End If
Next
End With
End Sub
Achte auf die Zeilenumbr�che!
Harald Friis
Jun 17, 2014, 11:34:12 AM6/17/14
to
Hallo Claus,
Am 17.06.2014 17:20, schrieb Claus Busch:
> mit VBA dann:
auch dafï¿œr danke, ich teste es aus.
Gruￜ
Harald Friis
Am 17.06.2014 17:20, schrieb Claus Busch:
> mit VBA dann:
auch dafï¿œr danke, ich teste es aus.
Gruￜ
Harald Friis
HC Ahlmann
Jun 17, 2014, 3:59:14 PM6/17/14
to
Harald Friis <Haral...@gmx.de> wrote:
> Jetzt kenne ich derzeit zwei M�glichkeiten: ich nehme nicht
> auto-ausf�llen, sondern schreibe h�ndisch in jede Zelle die richtige,
> absolut gesetzte Formel. Oder ich nehme das auto-ausf�llen und packe mit
> F4 in jede Zelle die Dollarzeichen. Beides ist eine Arbeit f�r lange
Zelle eine Bezeichnung und ein paar vorangehende Zeichen, die einen
unverwechselbaren String ergeben, den man mit Suchen und Ersetzen um das
Dollar-Zeichen erg�nzen kann.
--
Munterbleiben
HC
<http://hc-ahlmann.gmxhome.de/> Bordkassen, Kochen an Bord, T�rnberichte
> Jetzt kenne ich derzeit zwei M�glichkeiten: ich nehme nicht
> auto-ausf�llen, sondern schreibe h�ndisch in jede Zelle die richtige,
> absolut gesetzte Formel. Oder ich nehme das auto-ausf�llen und packe mit
> F4 in jede Zelle die Dollarzeichen. Beides ist eine Arbeit f�r lange
> Zeit, bei der sich die Wahrscheinlichkeit eines Nervenzusammenbruchs
> nahe 1 befindet.
3. M�glichkeit: Die Formel enth�lt doch f�r jede absolut zu beziehende
> nahe 1 befindet.
Zelle eine Bezeichnung und ein paar vorangehende Zeichen, die einen
unverwechselbaren String ergeben, den man mit Suchen und Ersetzen um das
Dollar-Zeichen erg�nzen kann.
--
Munterbleiben
HC
<http://hc-ahlmann.gmxhome.de/> Bordkassen, Kochen an Bord, T�rnberichte
0 new messages