Die Offset -Funktion in Excel ist ein leistungsstarkes Werkzeug unter den Suchfunktionen, das ideal für die Referenzierung eines Zellbereichs für Berechnungen ist. Diese Funktion ist besonders nützlich, wenn es um gro?e Datens?tze geht, die regelm??ig mit neuen Zeilen oder Spalten aktualisiert werden und die automatische Einbeziehung neuer Daten in Analysen ohne manuelle Anpassungen am Datenbereich erm?glichen. Eine praktische Anwendung befindet sich in Pivot -Tabellen, in denen die Datenquelle nicht als Excel -Tabelle formatiert wird, sodass neue Daten ohne manuelle ?nderungen automatisch in die Datenquelle der Pivot -Tabelle aufgenommen werden k?nnen.

Key Takeaways

• Flexible Reichweite Definition - Offset erm?glicht die Erstellung dynamischer Bereiche, die automatisch bei der ?nderung der Daten anpassen.
• Kompatibilit?t mit Diagrammen und Formeln - Durch die Verwendung von Offset in benannten Bereichen k?nnen Sie sicherstellen, dass Diagramme und Formeln mit den neuesten Daten aktualisiert werden.
• Integration mit Counta oder Count - Offset kann mit Counta oder Count kombiniert werden, um die Gr??e des Bereichs dynamisch zu bestimmen.
• Ideal für Dashboards -Dynamische Bereiche, die mit Offset erstellt wurden, halten Sie das Dashboards auf dem neuesten Stand, ohne manuelle Anpassungen zu erfordern.
• Effizienter Gebrauch erforderlich - zwar leistungsstark ist der Offset eine flüchtige Funktion, die h?ufig neu berechnet und eine sorgf?ltige Implementierung erfordert, um die Leistung aufrechtzuerhalten.

Inhaltsverzeichnis

Schnelle übersicht

Was macht es?

Es liefert einen Verweis auf einen Bereich, der aus einem bestimmten Punkt ab einem bestimmten Punkt ausgeht, wobei eine definierte Anzahl von Zeilen, Spalten und Spezifikationen der H?he und Breite von Zellen angegeben wird.

Formel -Aufschlüsselung:

= Offset (Referenz, Zeilen, Spalten, [H?he], [Breite])

Was es bedeutet:

= Offset (Beginnen Sie in dieser Zelle, bewegen Sie eine bestimmte Anzahl von Zeilen nach oben/unten, bewegen Sie sich nach links/rechts eine bestimmte Anzahl von Spalten, definieren Sie die H?he des Bereichs, definieren Sie die Breite des Bereichs).

So erstellen Sie einen dynamischen Datenbereich mit der Offset -Funktion

Schritt 1: Beginnen Sie mit der Eingabe der Offset -Funktion

= Offset

Schritt 2: Geben Sie das erste Argument des Versatzes an - wo starten Sie die Referenz?

Dies sollte die obere linke Ecke Ihrer Datenquelle sein.

Verwenden Sie eine absolute Referenz für das erste Argument, indem Sie F4 drücken

= Offset ($ a $ 10

Schritt 3: Definieren Sie das zweite Argument des Versatzes - Wie viele Zeilen, um sich nach unten zu bewegen?

Da wir bei Cell A10 beginnen wollen, geben Sie 0 ein.

= Offset ($ a $ 10, 0,,

Schritt 4: Stellen Sie das dritte Argument des Offsetes fest - Wie viele Spalten, um sich nach rechts/links zu bewegen?

Da wir die Spalten nicht verschieben müssen, geben Sie 0 ein.

= Offset ($ a $ 10, 0,0,,

Schritt 5: Bestimmen Sie das vierte Argument des Versatzes - Wie sollte die H?he der referenzierten Daten sein?

Verwenden Sie hier die Counta -Funktion, um die Anzahl der Transaktionen von Zelle A10 bis zur letzten aktiven Zelle vertikal zu z?hlen.

Um sicherzustellen, dass neue Daten erfasst werden, erweitern Sie den Bereich über die aktuellen Daten über die aktuellen Daten über die letzte Zeilennummer hinaus, dh 1.048.756.

Wenden Sie einen absoluten Verweis auf das vierte Argument an, indem Sie F4 drücken

= Offset ($ A $ 10, 0,0, Counta ($ 10: $ A $ 1048576

Schritt 6: Definieren Sie das fünfte Argument des Versatzes - Was sollte die Breite der referenzierten Daten sein?

Verwenden Sie die Counta -Funktion, um die Anzahl der Transaktionen horizontal von Zelle A10 zur letzten aktiven Zelle zu z?hlen.

Um sicherzustellen, dass neue Daten erfasst werden, erweitern Sie den Bereich über die aktuellen Daten bis zur letzten Spaltennummer, dh XFC.

Wenden Sie einen absoluten Verweis auf das fünfte Argument an, indem Sie F4 drücken

= Offset ($ A $ 10, 0,0, Counta ($ 10: $ A $ 1048576), Counta ($ A 10 $: $ xfc $ 10)))

Das folgende Bild zeigt, wie die Offset -Funktion den angegebenen Datenbereich erfasst:

Schritt 7: Erstellen Sie nach dem Einrichten der Offset -Funktion einen benannten Bereich , um sie in Schritt 9 als Datenquelle zu verwenden.

Kopieren Sie die gesamte Offset -Funktion ...

... und navigieren Sie zu Formeln> Name Manager> Neu :

Schritt 8: Geben Sie im neuen Namensdialog einen benutzerdefinierten Namen ohne Leerzeichen wie data_range im Namen: Feld ein und fügen Sie die Offset -Funktion aus Schritt 7 in das Feld auf: Dann klicken Sie auf OK .

Schritt 9: Um eine Pivot -Tabelle einzufügen, gehen Sie zu Insert> Pivot -Tabelle ...

... und geben Sie im Dialog "Pivottable" in den benannten Bereich von Schritt 8, Data_Range , in das Feld Tabelle/Range ein, w?hlen Sie ein neues/vorhandenes Arbeitsblatt und klicken Sie auf OK :

Schritt 10: Platzieren Sie in der Pivot -Tabelle das Verkaufsfeld im Bereich der Werte, das Jahresfeld im Bereich der Spalten und das Monatsfeld im Reihenbereich:

Schritt 11: Fügen Sie den leeren Zeilen in Ihrer Datenquelle neue Daten hinzu :

Schritt 12: Um die Pivot -Tabelle mit diesen neuen Daten zu aktualisieren, klicken Sie mit der rechten Maustaste in die Pivot -Tabelle und w?hlen Sie Aktualisierung aus :

Die neuen Daten werden nun in der Pivot -Tabelle angezeigt:

Schritt 13: Um zu überprüfen, ob der benannte Bereich Data_Range die neuen Daten erfasst, klicken Sie in der Pivot -Tabelle und wechseln Sie zu PivotTable Tools> Analyse/Optionen> Datenquelle ?ndern:

Dies best?tigt, dass data_range alle neuen Daten, die der Datenquelle hinzugefügt wurden, erfolgreich erfasst.

Diese Technik ist praktisch, aber wenn Sie eine Excel -Tabelle als Datenquelle verwenden, k?nnen Sie die Notwendigkeit dieser Offset -Funktion in einem benannten Bereich umgehen. Excel -Tische sind gro?artig, aber für diejenigen, die gerne mit Formeln arbeiten, ist diese Methode ein wertvoller Trick.

H?ufig gestellte Fragen

Was ist der Zweck der Offset -Funktion in Excel?
Die Offset -Funktion gibt eine Zelle oder einen Zellbereich zurück, der eine bestimmte Anzahl von Zeilen und Spalten von einem Startpunkt entfernt ist.

Warum Offset verwenden, um dynamische Bereiche zu erstellen?
Es erm?glicht die Erstellung von Bereichen, die automatisch anpassen, wenn Daten hinzugefügt oder entfernt werden, wodurch die Bedarf an manuellen Aktualisierungen beseitigt wird.

Kann Offset in Diagrammen verwendet werden?
Ja, indem Sie es in einen benannten Bereich integrieren, k?nnen Sie Offset verwenden, um die Datenquelle für Diagramme dynamisch zu aktualisieren.

Wirkt sich die Offset -Leistung aus?
Es kann; Der Offset ist eine flüchtige Funktion, die sich nach dem Arbeitsblatt neu berechnet und m?glicherweise gro?e Arbeitsmappen verlangsamt.

Das obige ist der detaillierte Inhalt vonErstellen Sie einen dynamischen Datenbereich mit der Offset -Funktion. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!