So frieren Sie eine Zelle in einer Excel-Formel ein

Benutzer sind häufig mit der Notwendigkeit konfrontiert, eine Zelle in einer Formel anzuheften. Dies tritt beispielsweise in Situationen auf, in denen Sie eine Formel kopieren möchten, der Link jedoch nicht um die gleiche Anzahl von Zellen nach oben und unten verschoben wird, wie er von seiner ursprünglichen Position kopiert wurde.

In diesem Fall können Sie den Zellbezug in Excel korrigieren. Und dies kann auf mehrere Arten gleichzeitig geschehen. Lassen Sie uns einen genaueren Blick darauf werfen, wie Sie dieses Ziel erreichen können.

Was ist ein excel-link

Das Blatt besteht aus Zellen. Jeder von ihnen enthält spezifische Informationen. Andere Zellen können es in Berechnungen verwenden. Aber wie verstehen sie, woher sie die Daten bekommen? Es hilft ihnen, Verbindungen herzustellen.

Jeder Link bezeichnet eine Zelle mit einem Buchstaben und einer Zahl. Ein Buchstabe steht für eine Spalte und eine Zahl für eine Zeile. 

Es gibt drei Arten von Links: absolut, relativ und gemischt. Der zweite ist standardmäßig eingestellt. Eine absolute Referenz ist eine, die eine feste Adresse sowohl einer Spalte als auch einer Spalte hat. Dementsprechend ist gemischt diejenige, bei der entweder eine separate Spalte oder eine Zeile festgelegt ist.

Die 1-Methode

Um sowohl Spalten- als auch Zeilenadressen zu speichern, gehen Sie folgendermaßen vor:

1. Klicken Sie auf die Zelle mit der Formel.
2. Klicken Sie auf die Bearbeitungsleiste für die Zelle, die wir benötigen.
3. Drücken Sie F4.

Als Folge ändert sich der Zellbezug auf absolut. Es ist an dem charakteristischen Dollarzeichen zu erkennen. Wenn Sie beispielsweise auf Zelle B2 und dann auf F4 klicken, sieht der Link so aus: $B$2.

Was bedeutet das Dollarzeichen vor dem Adressteil pro Zelle?

1. Wenn es vor einem Buchstaben steht, zeigt es an, dass die Spaltenreferenz gleich bleibt, egal wohin die Formel verschoben wurde.
2. Wenn das Dollarzeichen vor der Zahl steht, zeigt dies an, dass die Zeichenfolge fixiert ist. 

Die 2-Methode

Diese Methode ist fast die gleiche wie die vorherige, nur müssen Sie zweimal F4 drücken. Wenn wir zum Beispiel Zelle B2 hatten, wird es danach zu B$2. Mit einfachen Worten, auf diese Weise haben wir es geschafft, die Linie zu reparieren. In diesem Fall ändert sich der Buchstabe der Spalte.

Dies ist beispielsweise in Tabellen sehr praktisch, in denen Sie den Inhalt der zweiten Zelle von oben in der untersten Zelle anzeigen müssen. Anstatt eine solche Formel viele Male auszuführen, reicht es aus, die Zeile zu fixieren und die Spalte ändern zu lassen.

Die 3-Methode

Dies ist völlig identisch mit der vorherigen Methode, nur dass Sie dreimal die Taste F4 drücken müssen. Dann ist nur der Verweis auf die Spalte absolut, und die Zeile bleibt fest.

Die 4-Methode

Angenommen, wir haben einen absoluten Verweis auf eine Zelle, aber hier war es notwendig, ihn relativ zu machen. Drücken Sie dazu so oft die Taste F4, bis kein $-Zeichen im Link steht. Dann wird es relativ, und wenn Sie die Formel verschieben oder kopieren, ändern sich sowohl die Spaltenadresse als auch die Zeilenadresse.

Pinning-Zellen für eine große Reichweite

Wir sehen, dass die oben genannten Methoden überhaupt keine Schwierigkeiten bei der Durchführung darstellen. Aber die Aufgaben sind spezifisch. Und zum Beispiel, was zu tun ist, wenn wir mehrere Dutzend Formeln gleichzeitig haben, deren Verknüpfungen in absolute umgewandelt werden müssen. 

Leider erreichen Standard-Excel-Methoden dieses Ziel nicht. Dazu müssen Sie ein spezielles Addon namens VBA-Excel verwenden. Es enthält viele zusätzliche Funktionen, mit denen Sie häufige Aufgaben mit Excel viel schneller ausführen können.

Es enthält mehr als hundert benutzerdefinierte Funktionen und 25 verschiedene Makros und wird außerdem regelmäßig aktualisiert. Es ermöglicht Ihnen, die Arbeit in fast jedem Aspekt zu verbessern:

1. Zellen.
2. Macro.
3. Funktionen unterschiedlicher Art.
4. Links und Arrays.

Mit diesem Add-In können Sie insbesondere Links in einer großen Anzahl von Formeln auf einmal reparieren. Dazu müssen Sie die folgenden Schritte ausführen:

1. Wählen Sie einen Bereich aus.
2. Öffnen Sie die Registerkarte VBA-Excel, die nach der Installation angezeigt wird. 
3. Öffnen Sie das Menü „Funktionen“, in dem sich die Option „Formeln sperren“ befindet.
   

   

4. Als nächstes erscheint ein Dialogfeld, in dem Sie den erforderlichen Parameter angeben müssen. Mit diesem Addon können Sie eine Spalte und eine Spalte separat anheften und auch eine bereits vorhandene Anheftung mit einem Paket entfernen. Nachdem der gewünschte Parameter mit dem entsprechenden Optionsfeld ausgewählt wurde, müssen Sie Ihre Aktionen mit einem Klick auf „OK“ bestätigen.

Beispiel

Nehmen wir ein Beispiel, um es klarer zu machen. Nehmen wir an, wir haben Informationen, die die Warenkosten, ihre Gesamtmenge und den Verkaufserlös beschreiben. Und wir stehen vor der Aufgabe, die Tabelle anhand der Menge und der Kosten automatisch zu ermitteln, wie viel Geld wir ohne Abzug von Verlusten verdient haben.

In unserem Beispiel müssen Sie dafür die Formel = eingebenB2*C2. Es ist ganz einfach, wie Sie sehen können. Es ist sehr einfach, ihr Beispiel zu verwenden, um zu beschreiben, wie Sie die Adresse einer Zelle oder ihrer einzelnen Spalte oder Zeile festlegen können. 

In diesem Beispiel können Sie natürlich versuchen, die Formel mit der Autofill-Markierung nach unten zu ziehen, aber in diesem Fall werden die Zellen automatisch geändert. In Zelle D3 wird es also eine andere Formel geben, in der die Zahlen jeweils durch 3 ersetzt werden. Außerdem nimmt die Formel gemäß dem Schema – D4 – die Form an = B4 * C4, D5 – ähnlich, aber mit Nummer 5 und so weiter.

Wenn es notwendig ist (in den meisten Fällen stellt sich heraus), gibt es keine Probleme. Wenn Sie jedoch die Formel in einer Zelle fixieren müssen, damit sie sich beim Ziehen nicht ändert, wird dies etwas schwieriger. 

Angenommen, wir müssen den Dollarumsatz bestimmen. Legen wir es in Zelle B7. Lassen Sie uns ein wenig nostalgisch werden und die Kosten von 35 Rubel pro Dollar angeben. Um die Einnahmen in Dollar zu ermitteln, ist es daher erforderlich, den Betrag in Rubel durch den Dollarkurs zu dividieren.

So sieht es in unserem Beispiel aus.

Wenn wir, ähnlich wie in der vorherigen Version, versuchen, eine Formel vorzuschreiben, werden wir scheitern. Ebenso ändert sich die Formel in die entsprechende. In unserem Beispiel wird es so sein: =E3*B8. Von hier aus können wir sehen. dass der erste Teil der Formel zu E3 geworden ist, und wir uns diese Aufgabe gestellt haben, aber wir brauchen den zweiten Teil der Formel nicht zu B8 zu ändern. Daher müssen wir die Referenz in eine absolute umwandeln. Sie können dies tun, ohne die Taste F4 zu drücken, indem Sie einfach ein Dollarzeichen setzen.

Nachdem wir den Verweis auf die zweite Zelle in einen absoluten umgewandelt hatten, wurde er vor Änderungen geschützt. Jetzt können Sie es sicher mit dem AutoFill-Griff ziehen. Alle festen Daten bleiben gleich, unabhängig von der Position der Formel, und nicht festgeschriebene Daten ändern sich flexibel. In allen Zellen werden die in dieser Zeile beschriebenen Einnahmen in Rubel durch denselben Dollar-Wechselkurs dividiert.

Die Formel selbst sieht so aus:

=D2/$B$7

Achtung! Wir haben zwei Dollarzeichen angegeben. Auf diese Weise zeigen wir dem Programm, dass sowohl die Spalte als auch die Zeile korrigiert werden müssen.

Zellbezüge in Makros

Ein Makro ist eine Unterroutine, mit der Sie Aktionen automatisieren können. Im Gegensatz zur Standardfunktionalität von Excel können Sie mit einem Makro in nur wenigen Codezeilen sofort eine bestimmte Zelle festlegen und bestimmte Aktionen ausführen. Nützlich für die Stapelverarbeitung von Informationen, z. B. wenn keine Möglichkeit besteht, Add-Ons zu installieren (z. B. wird ein Firmencomputer verwendet, kein privater).

Zuerst müssen Sie verstehen, dass das Schlüsselkonzept eines Makros Objekte sind, die andere Objekte enthalten können. Das Workbooks-Objekt ist für das elektronische Buch (d. h. das Dokument) verantwortlich. Es enthält das Sheets-Objekt, das eine Sammlung aller Blätter eines geöffneten Dokuments ist. 

Dementsprechend sind Zellen ein Cells-Objekt. Es enthält alle Zellen eines bestimmten Blattes.

Jedes Objekt wird mit eingeklammerten Argumenten qualifiziert. Im Fall von Zellen wird auf sie in dieser Reihenfolge verwiesen. Die Zeilennummer wird zuerst aufgeführt, gefolgt von der Spaltennummer oder dem Buchstaben (beide Formate sind zulässig).

Eine Codezeile mit einem Verweis auf Zelle C5 würde beispielsweise so aussehen:

Arbeitsmappen („Book2.xlsm“). Blätter („List2“). Zellen (5, 3)

Arbeitsmappen („Book2.xlsm“). Blätter („List2“). Zellen (5, „C“)

Sie können auch über ein Objekt auf eine Zelle zugreifen Aufgeräumt. Im Allgemeinen soll es einen Verweis auf einen Bereich geben (dessen Elemente übrigens auch absolut oder relativ sein können), aber Sie können einfach einen Zellennamen im gleichen Format wie in einem Excel-Dokument angeben.

In diesem Fall sieht die Zeile wie folgt aus.

Arbeitsmappen („Book2.xlsm“).Blätter („List2“).Bereich („C5“)

Es mag scheinen, dass diese Option bequemer ist, aber der Vorteil der ersten beiden Optionen besteht darin, dass Sie Variablen in Klammern verwenden und einen Link angeben können, der nicht mehr absolut ist, sondern so etwas wie ein relativer, der von den Ergebnissen abhängt von die Berechnungen.

Somit können Makros effektiv in Programmen verwendet werden. Tatsächlich sind alle Verweise auf Zellen oder Bereiche hier absolut und können daher auch mit ihnen fixiert werden. Es stimmt, es ist nicht so bequem. Die Verwendung von Makros kann nützlich sein, wenn Sie komplexe Programme mit einer großen Anzahl von Schritten im Algorithmus schreiben. Im Allgemeinen ist die standardmäßige Verwendung absoluter oder relativer Referenzen viel bequemer. 

Schlussfolgerungen

Wir haben herausgefunden, was eine Zellreferenz ist, wie sie funktioniert und wozu sie dient. Wir haben den Unterschied zwischen absoluten und relativen Referenzen verstanden und herausgefunden, was getan werden muss, um einen Typ in einen anderen umzuwandeln (in einfachen Worten, die Adresse zu reparieren oder sie zu lösen). Wir haben herausgefunden, wie Sie dies sofort mit einer großen Anzahl von Werten tun können. Jetzt haben Sie die Flexibilität, diese Funktion in den richtigen Situationen zu verwenden.