Aktive Zelle in Formel

[Carlos Naplos]

Hallo

Ich möchte in einer Formel (EXCEL 2007) Bezug auf die Zelle nehmen, in
der die Formel steht.

In einer SVERWEIS-Spalte soll, wenn nichts gefunden wird (WENNFEHLER),
der Wert aus der darüber stehenden Zelle genommen werden.

Funktionieren würde es mit

BEREICH.VERSCHIEBEN(INDIREKT(ZELLE("adresse"));-1;0)

aber geht das nicht einfacher?
Ich habe gelesen INDIREKT sei sehr rechenintensiv; hier käme es 6 * 365
+ 1-mal vor.

Erläuterung:

=WENNFEHLER(
SVERWEIS(
B$1+ZEILE()-2;
Tab_09[[Termin]:[Wert]];
SPALTE(Tab_09[Wert])
);
BEREICH.VERSCHIEBEN(INDIREKT(ZELLE("adresse"));-1;0))


In B$1 steht ein Datum (1.1.2009), welches mit JJJJ formatiert die
Überschrift bildet; in B$2 steht 0; in der Tabelle Tab_09 stehen in der
Spalte [Termin] Termine, in der Spalte [Wert] (aufsummierte) Werte.

Tabellen Tab_JJ gibt es für die Jahre ab 2009.

Ein zusammenfassendes Diagramm soll die Werte der Jahre vergleichen.

Mit B$1+ZEILE()-2 erhalte ich den n-ten Tag des Jahres.
Aber die Einträge in den Tabellen Tab_JJ sind an verschiedenen Tagen.

lg
Carlos

[Claus Busch]

Hallo Carlos,

Am Thu, 23 Jan 2014 17:01:35 +0100 schrieb Carlos Naplos:

> In B$1 steht ein Datum (1.1.2009), welches mit JJJJ formatiert die
> Überschrift bildet; in B$2 steht 0; in der Tabelle Tab_09 stehen in der
> Spalte [Termin] Termine, in der Spalte [Wert] (aufsummierte) Werte.
>
> Tabellen Tab_JJ gibt es für die Jahre ab 2009.

wenn deine Datunswerte in Tab_JJ aufsteigend sortiert sind, könntest du
doch mit VERGLEICH arbeiten und als Vergleichstyp 1 einstellen. Gibt es
eine Übereinstimmung bekommst du den korrekten Wert, sonst den nächst
kleineren:
=INDEX(Tabelle1[[#Alle];[Wert]];VERGLEICH($B$1+ZEILE()-2;Tabelle1[[#Alle];[Termin]];1))


Mit freundlichen Grüßen
Claus
--
Win XP Prof SP3 / Vista Ultimate SP2
Office 2003 SP3 /2007 Ultimate SP3

[Carlos Naplos]

Hallo Claus

Zunächst vielen Dank und entschuldige, dass ich erst jetzt antworte.

Ich habe Deine Formel soweit verstanden, musste sie allerdings noch in ein WENNFEHLER einbetten, um den Tagen vor dem ersten Termin, der auftritt einen Wert (Null) zuzuweisen. Das "#Alle" konnte ich weglassen.

Die Formel lautet also:
=WENNFEHLER(INDEX(Tabelle1[Wert];VERGLEICH($B$1+ZEILE()-2;Tabelle1[Termin];1));0)

Meine ursprüngliche Formel
=BEREICH.VERSCHIEBEN(INDIREKT(ZELLE("adresse"));-1;0)
funktionierte nur scheinbar, weil sich ZELLE("adresse") nicht, wie ich vermutete, auf die Zelle bezieht in der die Formel steht, sondern anscheinend in die beim Berechnen (F9) aktive Zelle.

Dein Formel löst zwar mein aktuelles Problem (nochmals vielen Dank) beantwortet aber nicht die allgemeine Frage:

Gibt es in einer Excel-Formel die Möglichkeit, sich auf die Zelle zu beziehen, in der die Formel steht?

Auf Deine Antwort bin ich gespannt.

Mit freundlichen Grüßen
Carlos

[Claus Busch]

Hallo Carlos,

Am Sat, 01 Feb 2014 13:48:26 +0100 schrieb Carlos Naplos:

> =BEREICH.VERSCHIEBEN(INDIREKT(ZELLE("adresse"));-1;0)

bei BEREICH.VERSCHIEBEN kannst du alle Argumente außer dem ersten mit
Formeln berechnen. Das erste Argument ist die Ausgangszelle und deren
Adresse muss in absoluter Schreibweise in der Formel stehen (leider).

> Gibt es in einer Excel-Formel die Möglichkeit, sich auf die Zelle zu beziehen, in der die Formel steht?

Mit Workarounds wie mit der jetzigen Formel geht es doch. Es kommt
darauf an, ob du in dieser Zelle nur einen Bereich zusammenbasteln
willst oder ob sie in die Berechnung eingeht. Mit Bereich.Verschieben
oder Indirekt kannst du ganz gut Bereiche erstellen. In die Berechnung
darf die Formel aber nicht eingehen (z.B. Summe oder ähnliches), denn
dann kommt die Fehlermeldung Zirkelbezug. Diese kannst du aber auch
umgehen, indem du die iterative Berechnung aktivierst. Ich würde für
solche Dinge aber VBA nutzen.

[Carlos Naplos]

Hallo Claus

Das Workaround liefert - im Gegensatz zu Deiner Formel - eben nicht
immer das, was ich möchte.

Das liegt aber nicht an BEREICH.VERSCHIEBEN, sondern daran dass ZELLE
ohne Angabe eines Bezuges sich auf "die zuletzt geänderte Zelle" und
nicht, wie ich irrtümlicherweise vermutet hatte, auf die Zelle, in der
die Formel steht, bezieht.

Das heißt:
Wenn ich die Formel ZELLE("adresse") eingebe, zeigt sie mir die
gewünschte Adresse an.
Wenn ich dann in einer andereren Zelle etwas ändere, zeigt sie mir deren
Adresse.

Ich bräuchte die VBA-Funktion "ThisCell" in einer Formel.

Kein Problem: Ich definiere sie mir einfach.

Public Function ThisCell() As Range
Set ThisCell = Application.ThisCell
End Function

Die kann ich nun in einer Formel verwenden:

BEREICH.VERSCHIEBEN(ThisCell();-1;0)

Das funktioniert, solange meine Funktion "ThisCell" in einem VBA-Modul
der Arbeitsmappe steht, in der sie verwendet wird.

Was muss ich tun, dass Funktionen auch in meinem immer mitgeladenen
MyAddIn.xlam gefunden werden?

Mit freundlichen Grüßen
Carlos

[Claus Busch]

Hallo Carlos,

Am Sat, 01 Feb 2014 15:15:38 +0100 schrieb Carlos Naplos:

> Wenn ich die Formel ZELLE("adresse") eingebe, zeigt sie mir die
> gewünschte Adresse an.
> Wenn ich dann in einer andereren Zelle etwas ändere, zeigt sie mir deren
> Adresse.

die Funktion ZELLE hat aber zwei Argumente. Wenn du auch das zweite
Argument miteingibst und dieses eventuell auch noch absolut, bekommst du
immer die richtige Adresse:
=ZELLE("adresse";$D$1)
Das gibt dir immer und überall $D$1 zurück.

> Was muss ich tun, dass Funktionen auch in meinem immer mitgeladenen
> MyAddIn.xlam gefunden werden?

schreib dir die Function in ein Modul es AddIns oder noch besser in die
PERSONAL.xlsb, dann hast du diese Funktion in jeder Mappe.

[Carlos Naplos]

Hallo Claus

Das habe ich getan.
Die Funktion steht im Modul "Funktionen" von MyAddIn.xlam und im Modul
"Modul1" von PERSONAL.XLSB, aber im EXCEL-Arbeitsblatt steht in den
Zellen, in denen sie verwendet wird "#Name?".

Wenn ich sie in ein Modul der Arbeitsmappe in der sie verwendet wird
kopiere, funktioniert es.

Mit freundlichen Grüßen
Carlos

[Claus Busch]

Hallo Carlos,

Am Sat, 01 Feb 2014 15:58:09 +0100 schrieb Carlos Naplos:

> Die Funktion steht im Modul "Funktionen" von MyAddIn.xlam und im Modul
> "Modul1" von PERSONAL.XLSB, aber im EXCEL-Arbeitsblatt steht in den
> Zellen, in denen sie verwendet wird "#Name?".

wenn die Funktion in einer anderen Mappe ist, musst du sie mit dem
Mappennamen zusammen aufrufen, also z.B. mit
=MyAddIn.xlam!ThisCell() oder wenn sie in der Personal.xlsb ist mit
=PERSONAL.xlsb!ThisCell()

[Claus Busch]

Hallo nochmals,

Am Sat, 1 Feb 2014 16:05:22 +0100 schrieb Claus Busch:

> wenn die Funktion in einer anderen Mappe ist, musst du sie mit dem
> Mappennamen zusammen aufrufen, also z.B. mit
> =MyAddIn.xlam!ThisCell() oder wenn sie in der Personal.xlsb ist mit
> =PERSONAL.xlsb!ThisCell()

ThisCell ist eine Eigenschaft, ein VBA-Wort. Ich würde es nicht als
Variablenname wählen. Außerdem kann es als Range nicht funktionieren.
Die Standardeigenschaft von Range ist Value, aber in deinem Range steht
deine Formel drin. Schreibst du in A1 als Formel =A1 bekommst du einen
Zirkelbezug, in VBA bekommst du #WERT.
Du könntest aber die Adresse von ThisCell ausgeben:

Public Function myCell() As String
myCell = Application.ThisCell.Address(0, 0)
End Function

und dann mit dieser Adresse arbeiten. Allerdings funktioniert auch dann
BEREICH.VERSCHIEBEN nicht, weil darin als erstes Argument eine Konstante
stehen muss.

[Carlos Naplos]

Hallo Claus

Es geht doch ohne das "MyAddIn.xlam!".

Nur musste ich die Zelle mit der Formel neu schreiben (Doppelklick +
Enter), F9 genügte nicht.

Ich habe es bemerkt, als EXCEL das "MyAddIn.xlam!" bei der Eingabe gar
nicht in die Formel übernahm.

Vielen Dank für Deine überaus kompetente Hilfe.
Mit freundlichen Grüßen
Carlos

[Carlos Naplos]

Hallo nochmals

schrieb Claus Busch am 01.02.2014 16:36:
> Hallo nochmals,
>
> Am Sat, 1 Feb 2014 16:05:22 +0100 schrieb Claus Busch:
>
> ThisCell ist eine Eigenschaft, ein VBA-Wort. Ich würde es nicht als
> Variablenname wählen. Außerdem kann es als Range nicht funktionieren.
> Die Standardeigenschaft von Range ist Value, aber in deinem Range steht
> deine Formel drin. Schreibst du in A1 als Formel =A1 bekommst du einen
> Zirkelbezug, in VBA bekommst du #WERT.

Da hatte ich auch meine Bedenken. Ich habe es trotzdem ausprobiert und
der Rechner ist nicht explodiert. ;-)

Aber Spaß beiseite:
- Für VBA ist es offensichtlich kein Problem die beiden Bedeutungen von
"ThisCell" (benutzerdefinierte Funktion bzw. Eigenschaft von
Application) auseinander zu halten.
- Der Grund, der Funktion den gleichen Namen zu geben, wie der
Eigenschaft des Application-Objektes, ist für mich, dass beide dasselbe
(Range-Objekt) liefern.
- Excel selbst enhält offensichtlich keine Funktion namens "ThisCell".

Natürlich führt "=ThisCell()", wenn ich es in eine Zelle schreibe, zu
einem Zirkelbezug (wie wenn ich "=A1" in die Zelle A1 schreibe), aber in
"=BEREICH.VERSCHIEBEN(ThisCell();-1;0)" funktioniert es wunderbar und
liefert mir die Standardeigenschaft (VALUE), der darüberliegenden Zelle.

Lieber Claus, unsere kleine Diskusion hat mich in meinem
EXCEL-Verständnis ein gutes Stück weitergebracht.

Nochmals vielen Dank
CN