Inhalt
- Verwenden der OFFSET-Funktion in einer Formel in Excel 2007 und Excel 2010
- Wann werden positive und negative Zahlen für Zeilen und Spalten in OFFSET-Formeln verwendet?
- Verwenden von OFFSET mit COUNTA in einer Formel zum Erstellen eines Dynamikbereichs in Excel 2007 und Excel 2010
- Dynamische Bereiche in Excel
- Und schlussendlich....
Robbie schreibt hauptsächlich über Skyrim, beleuchtet aber gelegentlich auch die Kuriositäten von Microsoft-Anwendungen wie Excel und Outlook.
In diesem Artikel werden wir uns etwas ansehen, das ziemlich cool ist und das ich seit einiger Zeit in meinen eigenen Excel-Tabellen benötige.
Stellen Sie sich, wenn Sie so wollen, eine Tabelle vor, in der Sie Rechnungen, Verkäufe, Web-Hits usw. aufzeichnen und dann SUMME sie, um sich eine laufende Summe zu geben. Jedes Mal, wenn Sie Ihrer Tabelle zusätzliche Zeilen hinzufügen, müssen Sie Ihre aktualisieren SUMME Formel, um die zusätzlichen Elemente zu berücksichtigen.
Na nicht mehr! Willkommen im Dynamikbereich mit dem OFFSET und COUNTA Funktionen. Mit dieser Funktion wird jedes Mal, wenn Zeilen hinzugefügt (oder gelöscht) werden und auch jedes Mal, wenn kopierte Zellen eingefügt werden, die SUMME Die Formel wird automatisch und dynamisch aktualisiert, um dies widerzuspiegeln.
Verwenden der OFFSET-Funktion in einer Formel in Excel 2007 und Excel 2010
Das OFFSET Mit dieser Funktion können Sie Excel bitten, Ihnen den Inhalt einer Zelle (oder eines Zellbereichs) eine bestimmte Anzahl von Zeilen und Spalten von einer Referenzzelle (oder Zellen) entfernt mitzuteilen.
Wenn du benutzt OFFSET Allein wird der Wert der Zelle zurückgegeben, auf die Sie verwiesen haben. Wir werden damit als Beispiel beginnen:
= OFFSET (B3,1,1,1,1)
Die Formel besteht aus folgenden Teilen:
- Die Zelle, in der Excel starten soll (in diesem Fall B3) (in Excel als bezeichnet Referenz)
- Der nächste Teil der Formel ist die Anzahl von Reihen (kann entweder nach oben oder nach unten gerichtet sein) von der Referenzzelle zu der Zelle, die Sie betrachten. Wir sind an Zelle C4 interessiert, daher ist dieser Wert 1, da er eine Zeile von Zelle B3 entfernt ist.
- Als nächstes müssen wir wissen, wie viele Säulen vom Anfang bis zur Referenzzelle. Dies ist wieder 1, da es sich um eine Spalte von B3 bis C4 handelt
- Das vierte Element der Formel ist Höhe. Dies ist die Anzahl der Zeilen, die die zurückgegebenen Daten enthalten sollen.
- Endlich haben wir Breite oder die Anzahl der Spalten, in denen die Daten gespeichert werden sollen.
Hinweis:Reihen und Säulen kann eine negative oder positive Zahl sein. In der folgenden Tabelle finden Sie Informationen zur Verwendung positiver oder negativer Werte.
Wann werden positive und negative Zahlen für Zeilen und Spalten in OFFSET-Formeln verwendet?
| Reihe | Säule |
Positiv | Unten Referenz | Bezugsrecht |
Negativ | Über der Referenz | Links von der Referenz |
Hinweis:Höhe und Breite muss beides positiv sein. Wenn sie nicht angegeben sind, verwendet Excel die Höhe und Breite der Referenz.
Hinweis: Wenn Sie einen #WERT erhalten, haben Sie Excel normalerweise gebeten, etwas anzuzeigen, das nicht angezeigt werden kann.
Um dies zu veranschaulichen:
Wenn ich die folgende Formel verwende:
= OFFSET (B3,1,1,2,1)
Ich fordere Excel auf, die Ergebnisse einer Zelle in zwei Zellen anzuzeigen, was nicht möglich ist. Daher wird ein Fehler zurückgegeben.
Hinweis: Wenn Sie einen # REF-Fehler erhalten, haben Sie den Rand Ihres Arbeitsblatts verlassen.
Diese Formel führt zu einem # REF-Fehler, da es keine solche Zeile vier Zeilen über B3 gibt.
= OFFSET (B3, -4,2,1,1)
Verwenden von OFFSET mit COUNTA in einer Formel zum Erstellen eines Dynamikbereichs in Excel 2007 und Excel 2010
Jetzt, wo wir das verstehen OFFSET Funktion und wie man es in einer Formel verwendet, kommen wir zum wirklich interessanten Teil dieses Artikels; Wir können damit einen Dynamikbereich erstellen COUNTA. Wir können dann SUMME Die Werte im Dynamikbereich und in dieser Formel werden automatisch neu berechnet, wenn Zellen hinzugefügt, entfernt oder deren Inhalt geändert werden. Natürlich, SUMME ist nur ein Beispiel, das ich für diesen Artikel ausgewählt habe, das Sie verwenden könnten DURCHSCHNITTLICH, MAX oder jede andere Funktion, die Sie für angemessen hielten, neben der OFFSET und COUNTA Funktionen.
Schauen wir uns zunächst kurz an, was COUNTA tut für uns in diesem Beispiel:
Es wird einfach die Anzahl der Zellen gezählt, die zwischen der Start- und Endzelle nicht leer sind (in diesem Fall befinden sich 29 nicht leere Zellen zwischen Zelle C3 und Zelle C32).
= COUNTA (C3: C32)
Jetzt schließen wir die ein COUNTA Formel oben als Teil unserer OFFSET Formel zum Erstellen eines Dynamikbereichs. Die fertige Formel lautet:
= SUMME (OFFSET (C3,0,0, COUNTA (C3: C32), 1))
Wir beginnen mit einem Blick auf die OFFSET Abschnitt der Formel:
OFFSET (C3,0,0, COUNTA (C3: C32), 1)
- Wir beginnen bei Zelle C3 als unsere Referenz
- Die Anzahl der Reihen Wir bitten Excel, aus der Startzelle von C3 zu wechseln, ist 0
- Ebenso die Anzahl der Säulen ist auch 0 (Ich möchte meinen Daten Spalten hinzufügen und daraus entfernen. Wenn Sie sie also auf 0 halten, wird die Formel niemals unterbrochen.)
- Jetzt kommt der dynamische Teil der gesamten Formel. Für die HöheWir teilen Excel mit, dass die Höhe das Ergebnis der COUNTA-Formel ist, die derzeit 29 ist. Sollten wir Zeilen hinzufügen oder entfernen, wird dies automatisch angepasst!
- Schließlich empfehlen wir Excel der Breite, das ist in diesem Fall 1 Spalte.
Jetzt, wo wir das verstehen OFFSET Abschnitt der Formel fügen wir die SUMME Funktion dazu, um die Gesamtformel zu vervollständigen, die uns gibt:
= SUMME (OFFSET (C3,0,0, COUNTA (C3: C32), 1))
Wenn wir jetzt Einfügen In einer zusätzlichen leeren Zeile werden Sie feststellen, dass der Gesamtumsatz um 18 (dies ist der Wert in Zelle C32, die die letzte Zeile darstellt) von 1.085 in der obigen Abbildung auf 1.067 in der folgenden Abbildung sinkt.
Ich füge jetzt einen Wert in die leere Zeile ein und der Gesamtumsatz ist jetzt korrekt und enthält den Wert in C32.
Hinweis: Das Einfügen einer leeren Zeile in Ihren Dynamikbereich führt zu den in Ihrem angezeigten Ergebnissen SUMME Formel ist falsch.
Hinweis: Sollten Sie zusätzliche Zeilen hinzufügen, zum Beispiel von Einfügen kopierter Zellen Excel erhöht einfach die COUNTA Teil der Formel, die es dynamisch macht.
Als Beispiel habe ich meiner Tabelle vier Zeilen hinzugefügt und Excel hat meine Formel automatisch aktualisiert!
= SUMME (OFFSET (C3,0,0, COUNTA (C3: C36), 1))
Dynamische Bereiche in Excel
Die Möglichkeit, dynamische Bereiche in Ihren Excel-Tabellen zu haben, die Daten wie Verkäufe, Rechnungen, Web-Treffer usw. enthalten, die dann addiert oder gemittelt werden können, ist in der Tat sehr nützlich. Dies bedeutet, dass Sie Ihre Formeln nicht mehr manuell aktualisieren müssen, wenn Sie Ihre Daten ergänzen oder löschen.
Um das zu erreichen:
- Wir haben mit dem begonnen OFFSET Funktion in der Formel. Mit dieser Funktion können Sie mit einer Referenzzelle (oder Zellen) beginnen und Excel auffordern, den Inhalt eines anderen Bereichs mit einer bestimmten Anzahl von Zeilen und Spalten aus der Referenzzelle anzuzeigen
- Als nächstes haben wir die hinzugefügt COUNTA Funktion zur Bereitstellung der Höhe des Gebiets für OFFSET. Immer wenn Zellen zum Bereich hinzugefügt oder daraus entfernt werden, wird die COUNTA Abschnitt der Formel wird automatisch aktualisiert.
- Durch Hinzufügen der SUMME Funktion zu dieser Formel können wir nun die Zellen addieren, über die Excel angezeigt hat OFFSET.
Wenn alle diese Funktionen zu einer Formel hinzugefügt werden, wird nun ein Zellbereich dynamisch summiert und automatisch aktualisiert, wenn Daten zum Bereich hinzugefügt oder daraus gelöscht werden.Dies ist für mich die Schönheit und Kraft von Excel, die Tatsache, dass Sie zwei Funktionen zusammenfügen und etwas erstellen können, das nicht nur sehr nützlich ist, sondern auch mehr als die Summe seiner Teile.
Ich hoffe, dass Sie diesen Artikel nützlich und informativ gefunden haben und dass OFFSET wird Ihre Tabellenkalkulationen auf die gleiche Weise transformieren, wie es meine transformiert hat. Vielen Dank für das Lesen, bitte hinterlassen Sie Ihre Kommentare unten.
Und schlussendlich....
Dieser Artikel ist genau und nach bestem Wissen des Autors. Der Inhalt dient nur zu Informations- oder Unterhaltungszwecken und ersetzt nicht die persönliche Beratung oder professionelle Beratung in geschäftlichen, finanziellen, rechtlichen oder technischen Angelegenheiten.
