Was ist die Funktion, um die aktuelle Zeilennummer und den aktuellen Spaltennamen für eine Zelle in Excel zu erhalten?
Versuchen Sie die folgende Funktion:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Erläuterung: ADDRESS(row_num, column_num, [abs_num]). [abs_num] = 4 = relative Adresse. Das bedeutet, dass der zurückgegebene Wert keine ‘$’s enthält. Für die Spalte 'AB’ gibt ADDRESS ‘AB1’ zurück. Der Vertreter entfernt die ‘1’.
Dazu können Sie die Funktionen ROW und COLUMN verwenden. Wenn Sie das Argument für diese Formeln weglassen, wird die aktuelle Zelle verwendet. Diese können direkt mit der Funktion OFFSET oder jeder anderen Funktion verwendet werden, bei der Sie sowohl die Zeile als auch die Spalte als Zahlenwerte angeben können.
Wenn Sie z. B. =ROW() in Zelle D8 eingeben, ist der zurückgegebene Wert 8, wenn Sie =COLUMN() in dieselbe Zelle eingeben, ist der zurückgegebene Wert 4. Ich empfehle die Verwendung von Buchstaben zur Darstellung der Spalte nicht, da die Dinge schwierig werden, wenn Spaltennamen mit doppelten Buchstaben übergeben werden (wobei die bloße Verwendung von Zahlen ohnehin logischer ist).
Wenn Sie dennoch den Spaltenbuchstaben erhalten möchten, können Sie einfach 64 zur Spaltennummer hinzufügen (64 ist ein Zeichen weniger als CHAR), so dass im vorigen Beispiel, wenn Sie den Wert der Zelle auf A setzen, der zurückgegebene Wert =CHAR(COLUMN()+64) wäre. Wenn Sie wollten, dass der Wert einer Zelle die Zellposition selbst ist, wäre die vollständige Formel D.
Nur zur Information, ich habe 64 aus einer ASCII-Tabelle. Sie könnten auch die Formel =CHAR(COLUMN()+64) & ROW() verwenden, so dass die aktualisierte Formel, die diese verwendet, CODE wäre. Sie müssen 1 subtrahieren, da der Mindestwert von =CHAR(COLUMN() + CODE("A") - 1) immer 1 ist, und dann wäre der Mindestrückgabewert der gesamten Formel COLUMN.
Dies funktioniert jedoch nicht bei Spalten mit zwei Buchstaben. In diesem Fall benötigen Sie die folgende Formel, um Spalten mit zwei Buchstaben richtig zu parsen:
=IF(COLUMN()>26,IF(RIGHT(CHAR(IF(MOD(COLUMN()-1,26)=0,1,MOD(COLUMN()-1,26))+64),1)="Y",CHAR(INT((COLUMN()-1)/26)+64) & "Z",CHAR(INT((COLUMN()-1)/26)+64) & CHAR(IF(MOD(COLUMN(),26)=0,1,MOD(COLUMN(),26))+64)),CHAR(COLUMN()+64))&ROW()
Ich bin mir nicht sicher, ob es einen einfacheren Weg gibt, dies zu tun oder nicht, aber ich weiß, dass dies von Zelle B bis A1 ohne Probleme funktioniert. Dies veranschaulicht jedoch, warum es am besten ist, die Verwendung von buchstabenbasierten Spaltenbezeichnern zu vermeiden und bei rein zahlenbasierten Formeln zu bleiben (z.B. Verwendung der Spaltennummer anstelle des Buchstabens mit ZZ99).
Probieren Sie dies aus
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
Damit erhalten Sie eine exakte Spaltenüberschrift, ohne jegliche $ usw.
Etwas manuell, aber weniger VBA und eine einfachere Formel:
=column()
ein - In der Zeile darunter geben Sie =Address(1,A1)
ein - Dies liefert das Ergebnis $A$1 Sobald die Werte kopiert sind, suchen und ersetzen Sie $ und 1 durch Leerzeichen.
Um den Spaltennamen zu erhalten, verwendete ich folgende Formeln:
Für eine bestimmte Zelle:
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
Für die aktuelle Zelle:
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Versuchen Sie diese Variante. Sie funktioniert auf Spalten mit 3 Buchstaben und hinterlässt kein “$” auf der Vorderseite:
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
Geben Sie dies in eine beliebige Zelle ein:
Español :
=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)
English :
=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)
Sie können column() durch eine Zeilennummer ersetzen.
Eine andere Möglichkeit wäre, so etwas wie dies zu verwenden:
=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())
Wo colid sich auf einen benannten Bereich bezieht, den Sie an anderer Stelle in der Arbeitsmappe erstellen würden und der zwei benachbarte Spalten mit mehreren Zeilen umfasst: Die erste Spalte enthält die Zahlen 1 bis n, die der Zahl COLUMN() entsprechen, die zweite die Buchstaben A - ZZ oder wie viele Spaltenverweise Sie auch immer unterbringen möchten. Wenn Sie also die obige Zeichenfolge in Zelle A1 von ‘MySheet2’ kopieren würden, würde sie als ROW() ausgewertet und den Wert zurückgeben, den sie in der entsprechenden Zelle von =MySheet1!A1 gefunden hat.
Dies würde es Ihnen beispielsweise ermöglichen, MySheet1 als Arbeitsbereich zu verwenden, um Daten zu löschen und neu einzufügen, während alle Formatierungen oder Berechnungen in MySheet1, die sich auf diese Inhalte beziehen, mit den neuen Datensätzen aus dem Arbeitsblatt mit den Zieltabellen weiterhin korrekt funktionieren.
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)
Die Adressformel funktioniert durch Rückgabe des Spalten- und Zeilennamens. Das zurückgegebene Format wird immer $(Column Letters)$(Row Numbers) sein - z.B. $AA$2 oder $XAA$243556
Wenn wir wissen, dass $ immer beim ersten Zeichen vorkommen wird, können wir die erste Mittelformel verwenden, um mit dem Ziehen von Zeichen nach dem ersten $-Zeichen (d.h. dem 2. Zeichen) zu beginnen.
Dann finden wir das nächste $-Zeichen (da wir wissen, dass es nur zwei geben wird) und wir wissen, wie viele Zeichen zwischen dem ersten und dem zweiten Dollar-Zeichen liegen. Der Rest ist eine einfache Subtraktion.
Hier ist eine VBA, benutzerdefinierte Formel, Lösung. Sie arbeitet mit Spalten mit 1, 2 und 3 Buchstaben.
Geben Sie Folgendes in einen Code-Baustein ein:
Function COLUMNLETTER(Optional rng As Range) As String
'Returns the Column Letter of the top left cell in rng.
If rng Is Nothing Then Set rng = Application.Caller
COLUMNLETTER = Left(rng.Address(0, 0), IIf(rng.Column > 26, IIf(rng.Column > 702, 3, 2), 1))
End Function
=COLUMNLETTER() in einer beliebigen Zelle gibt den Spaltenbuchstaben der Zelle zurück. =COLUMNLETTER(B3) in einer beliebigen Zelle gibt B zurück.
Diese benutzerdefinierte Funktion funktioniert hervorragend beim Erstellen von generischen Formeln innerhalb der INDIRECT-Funktion.
Die folgende Formel funktioniert unabhängig davon, wo Sie sie einsetzen (sie gibt Ihnen den Spaltennamen zurück).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")