Möglicherweise wissen Sie nicht, wie Sie die OFFSET-Funktion in Excel verwenden

Wussten Sie , dass die OFFSET-Funktion in Microsoft Excel Ihnen bei vielen anderen Dingen helfen kann, nicht nur bei Zellreferenzen? Im Folgenden finden Sie einzigartige Möglichkeiten zur Verwendung von OFFSET in Excel, die Sie möglicherweise nicht kennen.

Die OFFSET-Funktion von Microsoft Excel ist ein flexibles und leistungsstarkes Tool. Aufgrund ihrer Komplexität und der Anzahl ähnlicher Funktionen wird diese Excel-Funktion leider nicht ausreichend genutzt.

Diese relativ einfache Funktion kann Benutzern helfen, Zeit und Aufwand zu sparen. Es vereinfacht das Abrufen genauer Datenpunkte aus einem gut sortierten Stammdatensatz. Außerdem ermöglicht es die einfache Erstellung detaillierter Berichte, interaktiver Visualisierungen und komplexer Dashboards.

Erstellen Sie einen Datensatz in benutzerdefinierter Größe

Eine der häufigsten Anwendungen der OFFSET-Funktion in Excel ist das Sammeln skalierbarer Datensätze. Bei einem großen Datensatz möchte man möglicherweise immer nur einen kleinen Teil der Daten berücksichtigen.

Um beispielsweise einen automatisierten Bericht über die Gesamtverkäufe der Mitarbeiter bereitzustellen, müssen Sie die Gesamtsumme für eine oder mehrere bestimmte Wochen abrufen.

Es kann ganz einfach sein, für jede Summe eine andere SUMME-Funktion zu schreiben. Die Verwendung der OFFSET-Funktion ist jedoch einfacher zu verwenden und schneller wiederzuverwenden.

Geben Sie zunächst in Zelle B6 das Datum ein, mit dem Sie beginnen möchten. Geben Sie dann die Anzahl der Tage ein, an denen der Bericht in B7 ausgeführt werden soll. Verwenden Sie als Nächstes die einfachen Funktionen SUMME und MITTELWERT, um mehr Daten für jeden Mitarbeiter im ausgewählten Zeitraum zu erhalten:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Das Anpassen des Startdatums oder der Startzeit ist so einfach wie das Bearbeiten einer Zelle, aber Sie können Datengruppen viel einfacher ziehen und ihre Größe ändern.

Ermöglicht das Erstellen benutzerdefinierter Berichte

Verwenden Sie die gleiche Methode wie oben, um einen vollständigen Bericht zu erstellen, indem Sie Variablen ganz einfach anpassen. Sie können dafür sorgen, dass das erste Argument in der OFFSET-Funktion auf ein anderes Blatt verweist. Dadurch können Sie Dateneingabe und Berichterstellung trennen.

Die obige Aktion ermöglicht es auch, ausgewählte Datenteile von einem Arbeitsblatt in ein anderes zu ziehen. Darüber hinaus können Sie nur Informationen anzeigen, die für den aktuellen Bericht relevant sind. Es ist ganz einfach, einen Bericht einzurichten, der die Tagessummen anhand der gleichen Daten wie zuvor anzeigt. Sie können dies tun, ohne Daten für unabhängige Daten abzurufen.

Die folgende Offset-Funktion in Zelle B5 ruft die in B2 angegebene Anzahl von Tagen ab. Es beginnt mit der in B1 ausgewählten Woche und zeigt nur Daten für den relevanten Zeitraum an:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Es ist möglich, Einträge in B1 und B2 einzuschränken und mithilfe der Datenvalidierung eine Dropdown-Liste in Excel mit verfügbaren Wochen zu erstellen.

Paginierung der Tabellenkalkulation

Die OFFSET-Funktion kann auch eine einfache Tabellenpaginierung ermöglichen, eine Aufgabe, die häufig mit der Verwendung der Registerkarte „Excel Developer“ verbunden ist. Dies kann die Verarbeitung großer Datenmengen einfacher und effektiver machen.

Wenn Sie mit Tabellenkalkulationen arbeiten, die bis zu 30.000 Datenzeilen enthalten, kann es schwierig sein, alle Daten auf einmal durchzusehen. Erstellen Sie ein Dropdown-Menü, das einen Zahlenbereich von 1 bis 300 akzeptiert, sodass Sie Daten schnell nach Seite mit 100 Ergebnissen pro Seite trennen können.

Der Ankerpunkt der OFFSET-Funktion wird immer in der obersten Datenzelle platziert. Das zweite Argument ist jedoch der vertikale Versatz. Dieser Wert entspricht der ausgewählten Seitenzahl minus 100. Der Offset ist horizontal, das dritte Argument kann auf 0 gesetzt werden.

Das 4. Argument ist auf 100 gesetzt, das 5. Argument ist die Anzahl der Spalten im Datensatz. Diese Methode macht das Aufteilen und Verwalten von Datentabellen extrem einfach.

Unten finden Sie ein Beispiel für paginierte Daten. Es enthält 500 von einem Sensor aufgezeichnete Informationen auf einem Blatt und einen paginierten Bericht auf dem anderen Blatt.

Der Bericht verwendet die Datenvalidierung in Zelle B1, um ein Dropdown-Menü mit darin enthaltenen Seiten zu erstellen. Der folgende Ausdruck in Zelle A3 erstellt den Hauptteil des Berichts:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Oben sind einige Möglichkeiten zur Verwendung der Offset-Funktion in Excel aufgeführt , die nur wenige Menschen kennen, aber sehr nützlich sind, insbesondere wenn Sie große Datenmengen verarbeiten müssen.

Ich hoffe, der Artikel ist für Sie nützlich.