Die REST-Funktion – Beispiele in Excel, VBA und Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die Funktion REST in Excel und Google Sheets verwenden können, um den Rest einer Division zu berechnen.

 

Die REST-Funktion im Überblick

Die REST-Funktion gibt den Rest bzw. den Modulus einer Zahl nach der Division zurück.

Die REST-Funktion ist jedoch nicht nur dazu da, uns bei unseren Divisionsproblemen zu helfen. Sie ist sogar noch leistungsfähiger, wenn wir nach jedem n-ten Element in einer Liste oder jeder n-ten Zeile suchen oder ein sich wiederholendes Muster erzeugen wollen.

REST – Mathematische Grundlagen

Wenn Sie versuchen, 13 durch 4 zu dividieren, können Sie sagen, dass die Antwort 3 und der Rest 1 ist. Die „1“ ist in diesem Fall als Modulus bekannt. Daher kommt der englische Name der REST-Funktion (MOD). In einer Formel könnte man also Folgendes schreiben:

=REST(13;4)

Und die Ausgabe wäre 1.

Ein Blick auf diese Tabelle veranschaulicht, wie die Eingabe bzw. Ausgabe von REST funktioniert.

=REST(A2;3)

Beachten Sie, dass es bei der Eingabe von 3 keinen Rest gab und die Ausgabe der Formel daher 0 war. Außerdem haben wir in unserer Tabelle die ZEILE-Funktion verwendet, um unsere Werte zu erzeugen. Ein Großteil der Leistungsfähigkeit von REST ergibt sich aus der Verwendung der ZEILE oder SPALTE-Funktion, wie wir in den folgenden Beispielen sehen werden.

REST-Summe in jeder zweiten Zeile

Betrachten Sie diese Tabelle:

Zur Veranschaulichung zeigen wir Ihnen die Formel, die in der zweiten Spalte enthalten ist:

=REST(A2; 2)

Um alle geraden Zeilen zu summieren, könnten Sie eine SUMMEWENN-Formel schreiben und das Kriterium nach 0-Werten in Spalte B suchen lassen. Oder, um alle ungeraden Zeilen zu summieren, das Kriterium nach 1-Werten suchen lassen.

Die Hilfsspalte brauchen wir jedoch gar nicht zu erstellen. Sie können die Leistung von REST innerhalb der SUMMENPRODUKT-Funktion kombinieren, um alles in einem Schritt zu erledigen. Unsere Formel hierfür wäre:

=SUMMENPRODUKT(A2:A5; --(REST(B2:B2; 2)=0)

Da sie innerhalb von SUMMENPRODUKT liegt, kann die REST-Funktion unsere Array-Eingabe verarbeiten. Wir haben die Ausgabe bereits in der Hilfsspalte gesehen, aber das Array aus unserer REST-Funktion in dieser Formel wird {0; 1; 0; 1} sein. Nach der Überprüfung auf Werte, die gleich 0 sind und der Anwendung des doppelten Unär-Zeichens, wird das Array {1; 0; 1; 0} entsprechen. SUMMENPRODUKT führt dann ihre Magie aus, indem sie die Felder multipliziert, um {2; 0; 4; 0} zu erzeugen, und dann die Summe bildet, um die gewünschte Ausgabe von 6 zu erhalten.

REST-Summe – Jede n-te Zeile

Da eine Formel mit REST(x; N) bei jedem n-ten Wert eine 0 ausgibt, können wir dies nutzen, um Formeln bei der Auswahl der Werte, die in anderen Funktionen verwendet werden sollen, zu helfen. Sehen Sie sich diese Tabelle an.

Unser Ziel ist es, die Werte aus jeder mit „Gesamtsumme“ markierten Zeile zu übernehmen. Beachten Sie, dass die Summe in jeder 3. Zeile erscheint, aber in Zeile 4 beginnt. Unsere REST-Funktion wird also 3 als 2. Argument verwenden und wir müssen 1 vom ersten Argument abziehen (da 4 -1 = 3). Auf diese Weise werden die gewünschten Zeilen (4, 7, 10) Vielfache von 3 sein (3, 6, 9). Unsere Formel für die Summe der gewünschten Werte lautet dann:

=SUMMENPRODUKT(C2:C10; --(REST(ZEILE(A2:A10)+2; 3)=0))

Das erzeugte Array wird wie folgt umgewandelt:

{2; 3; 4; 5; 6; 7; 8; 9; 10}
{1; 2; 3; 4; 5; 6; 7; 8; 9}
{1; 2; 0; 1; 2; 0; 1; 2; 0}
{FALSCH; FALSCH; WAHR; FALSCH; FALSCH; WAHR; FALSCH; FALSCH; WAHR}
{0; 0; 1; 0; 0; 1; 0; 0; 1}

Das Kriterien-Array unserer SUMMENPRODUKT-Funktion ist nun so eingerichtet, dass jeder 3. Wert erfasst wird und wir erhalten das gewünschte Ergebnis von $90.

REST-Summe über Spalten

Wir haben bisher Beispiele verwendet, die vertikal verlaufen und ZEILE verwenden, aber Sie können auch horizontal mit der SPALTE-Funktion arbeiten. Betrachten Sie dieses Layout:

Wir wollen die Summe aller Positionen bilden. Unsere Formel dafür könnte wie folgt lauten:

=SUMMENPRODUKT(B2:E2*(REST(SPALTE(B2:E2); 2)=0)

In diesem Fall sind wir so eingestellt, dass wir jede 2. Spalte in unserem Bereich erfassen, so dass SUMMENPRODUKT nur Nicht-Null-Werte für die Spalten B und D beibehält. Als Referenz finden Sie hier eine Tabelle mit den Spaltennummern und dem entsprechenden Wert nach REST 2.

Jede n-te Zeile markieren

Eine weitere häufige Anwendung der REST-Funktion ist die Hervorhebung jeder n-ten Zeile. Die allgemeine Form hierfür ist:

=REST(ZEILE() ± Verschiebung; N)=0

Dabei ist N die Anzahl der Zeilen zwischen den hervorgehobenen Zeilen (z. B. um jede 3. Zeile hervorzuheben, ist N = 3), und Verschiebung ist optional die Zahl, die addiert oder subtrahiert werden muss, damit die erste hervorgehobene Zeile mit N übereinstimmt (z. B. um jede 3. Zeile hervorzuheben, aber in Zeile 5 zu beginnen, müsste 2 subtrahiert werden, da 5 -2 = 3). Beachten Sie, dass die ZEILE-Funktion die Zeilennummer der Zelle, in der sich die Formel befindet, zurückgibt, wenn Sie keine Argumente angeben.

Verwenden wir unsere Tabelle von vorhin:

Um alle Summenzeilen hervorzuheben, erstellen wir eine neue Regel für die bedingte Formatierung mit der folgenden Formel:

=REST(ZEILE()-1; 3)=0

Wenn die bedingte Formatierung diese Formel anwendet, wird in Zeile 2 Folgendes angezeigt:

=REST(2-1; 3)=0
=REST(1; 3) = 0
=1=0
=FALSCH

In Zeile 3 wird eine ähnliche Ausgabe erfolgen, aber in Zeile 4 wird dann Folgendes angezeigt:

=REST(4-1; 3)=0
=REST(3; 3) = 0
=0=0
=WAHR

 

Ganzzahlen oder gerade Zahlen hervorheben

Anstatt bestimmte Zeilen hervorzuheben, können Sie auch die tatsächlichen Werte in den Zellen überprüfen. Dies kann nützlich sein, wenn Sie Zahlen, die ein Vielfaches von N sind, finden wollen. Um beispielsweise Vielfache von 3 zu finden, würde Ihre Formel für die bedingte Formatierung wie folgt lauten:

=REST(A2; 3)=0

Bis zu diesem Punkt haben wir mit ganzen Zahlen gearbeitet. Sie können jedoch auch eine Dezimalzahl eingeben (z. B. 1,234) und dann durch 1 dividieren, um nur den Dezimalteil zu erhalten (z. B. 0,234). Diese Formel sieht wie folgt aus:

=TEIL(A2; 1)

Um nur ganze Zahlen hervorzuheben, würde die Formel für die bedingte Formatierung wie folgt lauten:

=REST(A2; 1)=0

Alle n-Zellen verketten

Bisher haben wir die REST-Funktion verwendet, um dem Computer mitzuteilen, wann er bei jedem n-ten Element einen Wert erfassen soll. Man könnte sie auch verwenden, um die Ausführung einer größeren Formel auszulösen. Betrachten Sie dieses Layout:

Wir wollen die Namen miteinander verketten, aber nur in jeder 3. Zeile und beginnend in Zeile 2. Die Formel dafür lautet:

=WENN(REST(ZEILE()+1; 3)=0; VERKETTEN(A2;" "; A3;" "; A4); "")

Unsere REST-Funktion fungiert als Kriterium für die gesamte WENN-Funktion. In diesem Beispiel haben wir 1 zu unserer Zeile hinzuaddiert, weil wir in Zeile 2 beginnen (2 + 1 = 3). Wenn die Ausgabe der REST-Funktion 0 ist, führt die Formel die Verkettung durch. Andernfalls gibt sie einfach ein Leerzeichen zurück.

Gerade bzw. ungerade Werte zählen

Wenn Sie schon einmal die Anzahl der geraden oder ungeraden Werte in einem Bereich zählen mussten, wissen Sie, dass ZÄHLENWENN dazu nicht in der Lage ist. Mit REST und SUMMENPRODUKT können wir dies jedoch tun. Schauen wir uns diese Tabelle an:

Die Formel, die wir verwenden, um die ungeraden Werte zu finden, lautet wie folgt:

=SUMMENPRODUKT(1*(REST(A2:A7; 2)=1))

Anstatt einige Zeilennummern zu laden, wird unsere REST-Fnktion die tatsächlichen Werte der Zellen in das Array laden. Die Gesamttransformation verläuft dann wie folgt:

{5; 5; 3; 3; 2; 1}
{1; 1; 1; 1; 1; 0; 1} <- Nimmt den Rest von 2
{ WAHR; WAHR; WAHR; WAHR; FALSCH; WAHR } <- Überprüft, ob der Wert 0 ist
{1; 1; 1; 1; 0; 1} <- Multipliziert mit 1, um von Wahr/Falsch in 1/0 umzuwandeln

Die SUMMENPRODUKT-Funktion summiert dann die Werte in unserem Array, was die gewünschte Antwort in Höhe von 5 ergibt.

Sich wiederholendes Muster

In allen bisherigen Beispielen wurde die Ausgabe von REST auf einen Wert geprüft. Sie können REST auch verwenden, um ein sich wiederholendes Zahlenmuster zu erzeugen, was wiederum sehr hilfreich sein kann. Nehmen wir zunächst an, wir hätten eine Liste von Elementen, die wiederholt werden sollen.

Sie könnten versuchen, sie manuell zu kopieren und einzufügen, so oft wie Sie sie brauchen, aber das wäre aufwändig. Stattdessen werden wir die INDEX-Funktion verwenden, um unsere Werte abzurufen. Damit die INDEX-Funktion funktioniert, muss das Zeilenargument eine Zahlenfolge wie die Folgende sein: {1; 2; 3; 1; 2; 3; 1 usw.}. Dies können wir mit REST erreichen.

Zunächst beginnen wir mit der ZEILE-Funktion. Wenn Sie mit der folgenden Formel beginnen:

=ZEILE(A1)

Und diese dann nach unten kopieren, erhalten Sie die grundlegende Zahlenfolge {1; 2; 3; 4; 5; 6; …}. Wenn wir die REST-Funktion mit 3 als Divisor anwenden,

=REST(ZEILE(A1); 3)

würden wir {1; 2; 0; 1; 2; 0; …} erhalten. Wir sehen, dass wir ein sich wiederholendes Muster von „0, 1, 2“ haben, aber in der ersten Reihe fehlt die anfängliche 0. Um dies zu korrigieren, gehen Sie einen Schritt zurück und subtrahieren 1 von der Zeilennummer. Dadurch ändert sich unsere Anfangssequenz in {0; 1; 2; 3; 4; 5; …}

=REST(ZEILE(A1)-1; 3)

Und nach dem Austritt aus der REST-Funktion haben wir {0; 1; 2; 0; 1; 2; …}. Das kommt dem, was wir brauchen, schon sehr nahe. Der letzte Schritt besteht darin, dem Array eine 1 hinzuzuaddieren.

=REST(ZEILE(A1)-1; 3)+1

Das ergibt nun eine Zahlenfolge von {1; 2; 3; 1; 2; 3; …}. Dies ist unsere gewünschte Folge! Wenn wir sie in eine INDEX-Funktion einfügen, erhalten wir die folgende Formel:

=INDEX(MeineListe; REST(ZEILE(A1)-1; 3)+1)

Die Ausgabe sieht nun wie folgt aus:

 

REST-Beispiele in VBA

Sie können die MOD-Funktion auch in VBA verwenden.

In VBA ist REST ein Operator (genau wie die Operatoren Plus, Minus, Multiplikation und Division). Wenn Sie also die folgenden VBA-Anweisungen ausführen

Range("C2") = Range("A2") Mod Range("B2")
Range("C3") = Range("A3") Mod Range("B3")
Range("C4") = Range("A4") Mod Range("B4")
Range("C5") = Range("A5") Mod Range("B5")
Range("C6") = Range("A6") Mod Range("B6")
Range("C7") = Range("A7") Mod Range("B7")

ergibt die folgenden Ergebnisse:

Die Funktionsargumente (Zahl, usw.) können Sie entweder direkt in die Funktion eingeben oder Variablen definieren, die Sie stattdessen verwenden.

Die REST-Funktion in Google Sheets

Die REST-Funktion funktioniert in Google Sheets genau so wie in Excel: