So legen Sie einen Wertebereich in Excel fest

Bei der Verwendung von Microsoft Excel weiß der Benutzer oft nicht im Voraus, wie viele Informationen als Ergebnis in der Tabelle stehen werden. Daher verstehen wir nicht in allen Situationen, welche Reichweite abgedeckt werden sollte. Schließlich ist ein Satz von Zellen ein variables Konzept. Um dieses Problem zu beseitigen, ist es notwendig, die Bereichsgenerierung automatisch zu machen, sodass sie ausschließlich auf der vom Benutzer eingegebenen Datenmenge basiert.

Automatisch wechselnde Zellbereiche in Excel

Der Vorteil automatischer Bereiche in Excel besteht darin, dass sie die Verwendung von Formeln erheblich erleichtern. Darüber hinaus ermöglichen sie es, die Analyse komplexer Daten, die eine große Anzahl von Formeln enthalten, die viele Funktionen enthalten, erheblich zu vereinfachen. Sie können diesem Bereich einen Namen geben und er wird dann automatisch aktualisiert, je nachdem, welche Daten er enthält.

So nehmen Sie eine automatische Bereichsänderung in Excel vor

Angenommen, Sie sind ein Investor, der in ein Objekt investieren muss. Als Ergebnis möchten wir Informationen darüber erhalten, wie viel Sie insgesamt für die gesamte Zeit verdienen können, in der das Geld für dieses Projekt arbeitet. Um diese Informationen zu erhalten, müssen wir jedoch regelmäßig überwachen, wie viel Gesamtgewinn uns dieses Objekt bringt. Erstellen Sie den gleichen Bericht wie in diesem Screenshot.

Auf den ersten Blick ist die Lösung offensichtlich: Sie müssen nur die gesamte Spalte summieren. Erscheinen darin Einträge, wird der Betrag selbstständig aktualisiert. Aber diese Methode hat viele Nachteile:

1. Wenn das Problem auf diese Weise gelöst wird, können die in Spalte B enthaltenen Zellen nicht für andere Zwecke verwendet werden.
2. Eine solche Tabelle verbraucht viel RAM, was die Verwendung des Dokuments auf schwachen Computern unmöglich macht.

Daher ist es notwendig, dieses Problem durch dynamische Namen zu lösen. Um sie zu erstellen, müssen Sie die folgende Abfolge von Aktionen ausführen:

1. Gehen Sie zur Registerkarte „Formeln“, die sich im Hauptmenü befindet. Es wird einen Abschnitt „Definierte Namen“ geben, in dem sich eine Schaltfläche „Namen zuweisen“ befindet, auf die wir klicken müssen.
2. Als nächstes erscheint ein Dialogfeld, in dem Sie die Felder wie im Screenshot gezeigt ausfüllen müssen. Es ist wichtig zu beachten, dass wir die Funktion anwenden müssen = VERSETZUNG zusammen mit der Funktion Checkum einen automatisch aktualisierten Bereich zu erstellen. 
3. Danach müssen wir die Funktion verwenden SUM, die unseren dynamischen Bereich als Argument verwendet. 

Nach Abschluss dieser Schritte können wir sehen, wie die Abdeckung der Zellen, die zum Bereich „Einkommen“ gehören, aktualisiert wird, wenn wir dort neue Elemente hinzufügen.

OFFSET-Funktion in Excel

Schauen wir uns die Funktionen an, die wir zuvor im „range“-Feld aufgezeichnet haben. Verwendung der Funktion VERFÜGUNG Wir können die Größe des Bereichs bestimmen, wenn wir angeben, wie viele Zellen in Spalte B gefüllt sind. Die Funktionsargumente lauten wie folgt:

1. Zelle starten. Mit diesem Argument kann der Benutzer angeben, welche Zelle im Bereich als oben links betrachtet wird. Es wird nach unten und rechts gemeldet.
2. Bereichsversatz um Zeilen. Mit diesem Bereich legen wir die Anzahl der Zellen fest, um die ein Versatz von der oberen linken Zelle des Bereichs auftreten soll. Sie können nicht nur positive Werte verwenden, sondern auch Null und Minus. In diesem Fall tritt die Verschiebung möglicherweise überhaupt nicht auf oder sie wird in der entgegengesetzten Richtung ausgeführt.
3. Bereichsoffset durch Spalten. Dieser Parameter ähnelt dem vorherigen, nur dass Sie damit den Grad der horizontalen Verschiebung des Bereichs einstellen können. Auch hier können Sie sowohl Null- als auch negative Werte verwenden.
4. Die Höhe des Höhenbereichs. Tatsächlich macht uns der Titel dieses Arguments klar, was es bedeutet. Dies ist die Anzahl der Zellen, um die sich die Reichweite erhöhen soll.
5. Der Wert des Bereichs in der Breite. Das Argument ist ähnlich wie das vorherige, nur betrifft es die Spalten.

Sie müssen die letzten beiden Argumente nicht angeben, wenn dies nicht erforderlich ist. In diesem Fall ist der Bereichswert nur eine Zelle. Zum Beispiel, wenn Sie die Formel angeben =OFFSET(A1;0;0), bezieht sich diese Formel auf dieselbe Zelle wie die im ersten Argument. Wenn der vertikale Offset auf 2 Einheiten eingestellt ist, bezieht sich die Zelle in diesem Fall auf Zelle A3. Lassen Sie uns nun im Detail beschreiben, was die Funktion bedeutet Check.

COUNT-Funktion in Excel

Verwendung der Funktion Check wir ermitteln, wie viele Zellen in Spalte B wir insgesamt ausgefüllt haben. Das heißt, wir bestimmen mit zwei Funktionen, wie viele Zellen im Bereich gefüllt sind, und bestimmen basierend auf den erhaltenen Informationen die Größe des Bereichs. Daher lautet die endgültige Formel wie folgt: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Schauen wir uns an, wie man das Prinzip dieser Formel richtig versteht. Das erste Argument weist darauf hin, wo unser dynamischer Bereich beginnt. In unserem Fall ist dies die Zelle B2. Weitere Parameter haben Nullkoordinaten. Dies deutet darauf hin, dass wir keine Offsets relativ zur oberen linken Zelle benötigen. Alles, was wir eingeben, ist die vertikale Größe des Bereichs, als den wir die Funktion verwendet haben Check, die die Anzahl der Zellen bestimmt, die einige Daten enthalten. Der vierte Parameter, den wir ausgefüllt haben, ist die Einheit. Wir zeigen also, dass die Gesamtbreite des Bereichs eine Spalte sein sollte.

Also mit der Funktion Check Der Benutzer kann den Speicher so effizient wie möglich nutzen, indem er nur die Zellen lädt, die einige Werte enthalten. Dementsprechend treten bei der Arbeit keine zusätzlichen Fehler auf, die mit der schlechten Leistung des Computers zusammenhängen, auf dem die Tabellenkalkulation funktioniert.

Um die Größe des Bereichs in Abhängigkeit von der Anzahl der Spalten zu bestimmen, müssen Sie dementsprechend eine ähnliche Abfolge von Aktionen ausführen, nur müssen Sie in diesem Fall die Einheit im dritten Parameter und die Formel im vierten Parameter angeben Check.

Wir sehen, dass man mit Hilfe von Excel-Formeln nicht nur mathematische Berechnungen automatisieren kann. Dies ist nur ein Tropfen auf den heißen Stein, aber tatsächlich ermöglichen sie Ihnen, fast jede Operation zu automatisieren, die Ihnen in den Sinn kommt.

Dynamische Diagramme in Excel

So konnten wir im letzten Schritt einen dynamischen Bereich erstellen, dessen Größe ganz davon abhängt, wie viele gefüllte Zellen er enthält. Basierend auf diesen Daten können Sie nun dynamische Diagramme erstellen, die sich automatisch ändern, sobald der Benutzer Änderungen vornimmt oder eine zusätzliche Spalte oder Zeile hinzufügt. Die Reihenfolge der Aktionen ist in diesem Fall wie folgt:

1. Wir wählen unseren Bereich aus und fügen danach ein Diagramm vom Typ „Histogramm mit Gruppierung“ ein. Sie finden diesen Punkt im Abschnitt „Einfügen“ im Abschnitt „Diagramme – Histogramm“.
2. Wir klicken mit der linken Maustaste auf eine beliebige Spalte des Histogramms, woraufhin die Funktion =SERIES() in der Funktionszeile angezeigt wird. Im Screenshot sehen Sie die detaillierte Formel. 
3. Danach müssen einige Änderungen an der Formel vorgenommen werden. Sie müssen den Bereich nach „Sheet1!“ ersetzen. zum Namen des Bereichs. Daraus ergibt sich folgende Funktion: =ZEILE(Blatt1!$B$1;;Blatt1!Einnahmen;1)
4. Jetzt muss dem Bericht noch ein neuer Datensatz hinzugefügt werden, um zu prüfen, ob das Diagramm automatisch aktualisiert wird oder nicht.

Werfen wir einen Blick auf unser Diagramm.

Fassen wir zusammen, wie wir es gemacht haben. Im vorherigen Schritt haben wir einen dynamischen Bereich erstellt, dessen Größe davon abhängt, wie viele Elemente er enthält. Dazu haben wir eine Kombination von Funktionen verwendet Check и VERFÜGUNG. Wir haben diesen Bereich benannt und dann den Verweis auf diesen Namen als Bereich unseres Histogramms verwendet. Welcher spezifische Bereich in der ersten Phase als Datenquelle ausgewählt wird, ist nicht so wichtig. Die Hauptsache ist, es später durch den Namen des Bereichs zu ersetzen. Auf diese Weise können Sie viel RAM sparen.

Benannte Bereiche und ihre Verwendung

Lassen Sie uns nun ausführlicher darüber sprechen, wie Sie benannte Bereiche korrekt erstellen und sie verwenden, um die Aufgaben auszuführen, die für den Excel-Benutzer festgelegt sind.

Standardmäßig verwenden wir normale Handyadressen, um Zeit zu sparen. Dies ist nützlich, wenn Sie einen Bereich einmal oder mehrmals schreiben müssen. Wenn es ständig verwendet werden muss oder adaptiv sein muss, sollten benannte Bereiche verwendet werden. Sie erleichtern das Erstellen von Formeln erheblich, und es wird dem Benutzer nicht so schwer fallen, komplexe Formeln zu analysieren, die eine große Anzahl von Funktionen enthalten. Lassen Sie uns einige der Schritte beschreiben, die zum Erstellen dynamischer Bereiche erforderlich sind.

Alles beginnt mit der Benennung der Zelle. Wählen Sie es dazu einfach aus und schreiben Sie dann den gewünschten Namen in das Feld seines Namens. Es ist wichtig, dass es leicht zu merken ist. Bei der Namensgebung sind einige Einschränkungen zu beachten:

1. Die maximale Länge beträgt 255 Zeichen. Das reicht völlig aus, um einen Namen zu vergeben, den das Herz begehrt.
2. Der Name darf keine Leerzeichen enthalten. Wenn es also mehrere Wörter enthält, ist es möglich, sie mit dem Unterstrich zu trennen.

Wenn wir diesen Wert später auf anderen Blättern dieser Datei anzeigen oder für weitere Berechnungen verwenden müssen, müssen Sie nicht auf das allererste Blatt wechseln. Sie können einfach den Namen dieser Bereichszelle aufschreiben.

Der nächste Schritt besteht darin, einen benannten Bereich zu erstellen. Das Vorgehen ist grundsätzlich gleich. Zuerst müssen Sie einen Bereich auswählen und dann seinen Namen angeben. Danach kann dieser Name in allen anderen Datenoperationen in Excel verwendet werden. Beispielsweise werden benannte Bereiche häufig verwendet, um die Summe von Werten zu definieren.

Darüber hinaus ist es möglich, einen benannten Bereich auf der Registerkarte „Formeln“ mit dem Werkzeug „Name festlegen“ zu erstellen. Nachdem wir es ausgewählt haben, erscheint ein Fenster, in dem wir einen Namen für unseren Bereich auswählen und den Bereich angeben müssen, auf den er sich manuell erstrecken soll. Sie können auch angeben, wo dieser Bereich wirken soll: innerhalb eines einzelnen Blatts oder im gesamten Buch.

Wenn bereits ein Namensraum erstellt wurde, gibt es zu seiner Verwendung einen speziellen Dienst namens Namensmanager. Es ermöglicht nicht nur das Bearbeiten oder Hinzufügen neuer Namen, sondern auch das Löschen, wenn sie nicht mehr benötigt werden.

Zu beachten ist, dass bei der Verwendung benannter Bereiche in Formeln nach dem Löschen die Formeln nicht automatisch mit den korrekten Werten überschrieben werden. Daher können Fehler auftreten. Daher müssen Sie vor dem Löschen eines benannten Bereichs sicherstellen, dass er in keiner der Formeln verwendet wird.

Eine andere Möglichkeit, einen benannten Bereich zu erstellen, besteht darin, ihn aus einer Tabelle abzurufen. Dafür gibt es ein spezielles Werkzeug namens „Create from Selection“. Wie wir verstehen, müssen Sie, um es zu verwenden, zuerst den Bereich auswählen, den wir bearbeiten werden, und dann den Ort festlegen, an dem wir die Überschriften haben. Als Ergebnis verarbeitet Excel basierend auf diesen Daten automatisch alle Daten und Titel werden automatisch zugewiesen.

Enthält der Titel mehrere Wörter, trennt Excel diese automatisch mit einem Unterstrich.

Daher haben wir herausgefunden, wie Sie dynamische benannte Bereiche erstellen und wie Sie damit die Arbeit mit großen Datenmengen automatisieren können. Wie Sie sehen, reicht es aus, mehrere Funktionen und die in die Funktionalität integrierten Programmwerkzeuge zu verwenden. Es gibt überhaupt nichts Kompliziertes, obwohl es einem Anfänger auf den ersten Blick so erscheinen mag.