Bereich.Verschieben mit Vergleich

Computer: Software & Programmierung Office Software
1

Hallo ExpertInnen,

aus einem Excel-Buch habe ich folgende Formel:

=BEREICH.VERSCHIEBEN(Phasen;1;VERGLEICH($A$2:stuck_out_tongue_winking_eye:hasen;0)-1;100;1)

Sie funktioniert einwandfrei, aber ich sitze jetzt seit fast 2 Stunden mit Excel-Hilfe und gegoogelt, um sie zu verstehen (was mir nicht gelungen ist…:smile:

In Tabelle 1 stehen die Daten. Namensbezug zu „Phasen“ = $a$1:blush:e$1

In Tabelle 2 steht in A2 eine Gültigkeitsliste; Quelle = „Phasen“
In B2 steht auch eine Gültigkeitsliste; Quelle ist obige Formel.

Dieses Makro steht in Tabelle2:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 1 Then
With ActiveCell.Offset(0, 1)
.Value = „“
.Select
End With
End If
End Sub

Da die Formel genau das macht, was ich für mein Projekt brauche, möchte ich sie auch gerne verstehen.

Ist jemand so nett, mir das mal en detail aufzudröseln?

Ich würde mich freuen und bedank mich schon mal im voraus.

Liebe Grüße

Jorge

2

Hallo Jorge,

In Tabelle 1 stehen die Daten. Namensbezug zu „Phasen“ =
$a$1:blush:e$1

schau nochmal nach, sicher steht dort: =Tabelle1!$A$1:blush:E$1

Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │
──┼──────┼──────┼──────┼──────┼──────┤
1 │ 2006 │ 2007 │ 2008 │ 2009 │ 2010 │
──┼──────┼──────┼──────┼──────┼──────┤
2 │ 2008 │ │ 1 │ │ │
──┼──────┼──────┼──────┼──────┼──────┤
3 │ 2 │ │ 2 │ │ │
──┼──────┼──────┼──────┼──────┼──────┤
4 │ 15 │ │ 3 │ │ │
──┼──────┼──────┼──────┼──────┼──────┤
5 │ │ │ 4 │ │ │
──┼──────┼──────┼──────┼──────┼──────┤
6 │ │ │ 5 │ │ │
──┴──────┴──────┴──────┴──────┴──────┘
Benutzte Formeln:
A3: =BEREICH.VERSCHIEBEN(Phasen;1;VERGLEICH($A$2:stuck\_out\_tongue\_winking\_eye:hasen;0)-1;100;1)
A4: =SUMME(BEREICH.VERSCHIEBEN(Phasen;1;VERGLEICH($A$2:stuck\_out\_tongue\_winking\_eye:hasen;0)-1;100;1))

Festgelegte Namen:
Phasen: =Tabelle1!$A$1:blush:E$1

Tabellendarstellung erreicht mit dem Code in FAQ:2363

So, nochmal die Formel: =BEREICH.VERSCHIEBEN(Phasen;1;VERGLEICH($A$2:stuck_out_tongue_winking_eye:hasen;0)-1;100;1)

Mit Vergleich ermitellst du die Position vom Wert in A2 in Phasen, hier also die 3, da es die dritte Zelle ist.
Vom Ergebnis von Vergleich ziehst du in Der Formel 1 ab, also sieht die Formel quasi so aus:

=BEREICH.VERSCHIEBEN(Phasen; 1;2 ;100;1)

Jetzt kommt Bereich.Verschieben und verschiebt den Bereich von Phasen um 1 Zeile nach unten und 2 Spalten nach rechts (das gibst du mit dem 3ten und 4ten Parameter von Bereich.Verschieben an.

Das heißt die Anfangszelle von Phasen war mal A1, nach der Verschiebung ist es jetzt C2.

=BEREICH.VERSCHIEBEN(Phasen;1;2; 100;1 )

Mit den letzten beiden Paraetern gibst du die neue größe des neuen Bereichs an, also umfaßt der Bereich jetzt C2:C101 (100 zeilen,1 Spalte.

Gibst du nun in einem beliebigen Blatt in einer Zelle die Gültigkeitsliste =Phasen an, so wird dir 2006,2077,2088 usw aufgelistet.

Gibst du die Formel an, so wird dir,1,2,3,4,5 aufgelistet

Dieses Makro steht in Tabelle2:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Column = 1 Then
With ActiveCell.Offset(0, 1)
.Value = „“
.Select
End With
End If
End Sub

Den Zusammenhang zwischen Code und Formel verstehe ich nicht, was soll man mit dem Code tun?

Gruß
Reinhard