Datenschutzerklärung


Direktnachricht



Ihre Software
Details
Excel/VBA 🔍
Add-Ins

Suche in Beispielen und Tipps zu Excel und VBA

Suchbegriff(e) mit Leerzeichen getrennt:

Anleitung: Rechnung erstellenFormellösung

Kategorie: Tabelle ▸ Zellen

(Tipp 207) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich mit Formeln eine Rechnung erstellen?

Du hast eine Tabelle namens "Rechnung" und eine Tabelle namens "Artikel". Auf der Tabelle "Artikel" befinden sich ab A2 die Artikelnummern (hier fortlaufend numeriert), ab B2 die Artikel und ab C2 die Einzelpreise. Auf dem Blatt "Rechnung" wird die Rechnung erstellt.:

  1. In A20 bis A30 sollen die Nummern der gekauften Artikel eingetragen werden.
  2. In B20 bis B30 wird die Anzahl der gekauften Artikel eingetragen.
  3. In C20 bis C30 sollen automatisch die Artikel erscheinen. Dazu kannst Du die Formel verwenden (in einer Zeile): =WENN(ISTNV(SVERWEIS($A20;Artikel!$A$2:$C$28;2));""; SVERWEIS($A20;Artikel!$A$2:$C$28;2))
  4. In D20 bis D30 sollen die dazugehörigen Einzelpreise eingelesen werden. Die Formel dazu (in einer Zeile): =WENN(ISTNV(SVERWEIS($A20;Artikel!$A$2:$C$28;3));""; SVERWEIS($A20;Artikel!$A$2:$C$28;3))
  5. In E20 bis E30 sollen die Preise der gekauften Artikel errechnet werden, die Formel: =WENN(D20="";"";B20*D20)
  6. In E31 soll Netto ausgerechnet werden: =SUMME(E20:E30)
  7. MwSt. in E32: =E31*16%
  8. Brutto in E33: =E31+E32

Arrayformeln (01)FormellösungArrayfunktion/MatrixfunktionTipp

Kategorie: Arrays ▸ Arrayformeln

(Tipp 18) Nachricht zum Beitrag an Autor Nach oben

Was sind die Arrayformeln, die ab Excel 365 eingeführt wurden?

Mit Excel 365 wurde das System der Formeln neu erarbeitet, wie mehrfach berichtet wurde. Allerdings handelt es sich dabei eigentlich nur um eine Ergänzung, wenn auch eine sehr sinnvolle und nützliche. Die alten Formeln arbeiten ganz normal weiter - hier muss nicht befürchtet werden, dass irgendetwas nicht mehr funktioniert. Neu sind hingegen die Arrayformeln. Zum Vergleich: Bisher konnte in Formeln mit kompletten Bereichen gearbeitet werden, zum Beispiel mit Summewenn. Die Eingabe der Formeln musste dazu mit Strg + Umschalt + Enter abgeschlossen werden, woraufhin Excel um die Formel geschweifte Klammern {} legte. Die Formel wurde somit als Matrixformel erkannt und es wurde ein kompletter Bereich verarbeitet. Das ERgebnis hingegen stand als ein Wert in einer Zelle.
Mit den Arrayformeln wurde dies erweitert und auch vereinfacht. Sollen komplette Bereiche verarbeitet werden, wird die Eingabe der Formel ganz normal beendet; die geschweiften Klammern werden nicht mehr benötigt. So zum Beispiel diese Formel, die in B2:B13 Nach "Juni" sucht und aus C2:C13 die Werte addiert:

=SUMMEWENN(B2:B13;"Juni";C2:C13)

Auch hier haben wir in einer Zelle ein Ergebnis, nämlich die Summe. Der Unterschied bis hierher liegt nur in den fehlenden geschweiften Klammern.


Überlaufende bzw. verschüttete Formeln

Der größte Unterschied wird jedoch deutlich, wenn in eine Zelle folgende einfache Formel eingegeben wird:

=A2:C13

Geschieht dies bei einer Excelinstallation zum ersten Mal, erscheint eine Meldung:

Formel übergelaufen: Ihre Formel hat mehrere Werte zurückgegeben, weshalb wir sie in die benachbarten leeren Zellen haben überlaufen lassen.

Das Ergebnis wird dann auch deutlich: Die Formel liefert alle Werte, die sich im Bereich befinden, der in der Formel angegeben ist - von der 1 in A2 bis zur 600 in C13. Der erste Wert steht dabei in der Zelle mit der Formel, die anderen Werte rechts neben und unter dieser Zelle - die Formel ist übergelaufen. Das Verhalten ist vergleichbar mit der CSS-Eigenschaft float: left;. Allerdings steht die Formel tatsächlich nur in der einen Zelle; sie kann auch nur dort bearbeitet werden. Sichtbar wird das, wenn im Ergebnis der Inhalt einer anderen Zelle gelöscht werden soll - es geht nicht.

Dieses Verhalten, dass mehrere Ergebnisse ausgegeben werden sollen, kann vielfältig genutzt werden. Beispiel für eine weitere einfache Formel:

=WENN(C2:C13>400;B2:B13;"")

In Zeile 2 neben der Basistabelle liefert sie in den entsprechenden Zeilen die Monate, bei denen die Beträge größer als 400 sind. Das Gleiche passiert natürlich auch, wenn die Formel in eine andere Zeile eingetragen wird - nur hat man dann den Offset, weil die Ergebnisse ab der Zelle mit der Formel angezeigt werden.

Diese Formel würde die Zahlen in den entsprechenden Zeilen anzeigen, die zum "Juni" gehören:

=WENN(B2:B13="Juni";C2:C13;"")

Natürlich kann die als Arrayfunktion verwendete Wenn-Funktion auch in anderen Funktionen eingesetzt werden. Hier werden alle Zahlen aus C addiert, wenn sie größer als 400 sind:

=SUMME(WENN(C2:C13>400;C2:C13))

In dem Fall haben wir natürlich wieder nur ein Ergebnis, nicht eine Matrix aus mehreren Werten


Fehler: #ÜBERLAUF! bzw. #SPILL!

Dieser Fehler erscheint, wenn Excel eine Arrayformel nicht berechnen oder deren Ergebnisse nicht darstellen kann.

Meist wird dies der Fall sein, wenn schlicht zu wenig Platz für die Ausgabe ist. Dann erscheint in der Zelle mit der Formel die Meldung und es wird mit einem Rahmen dargestellt, wie viel Platz benötigt würde. Im Weg können dabei Zellinhalte sein, aber auch das Ende der Tabelle, verbundene Zellen usw.

Auch wenn in einer Arrayfunktion Zufallszahlen oder andere Ergebnisse verwendet werden, kann der Fehler erscheinen. Dann ist die innere Funktion (zum Beispiel eine für Zufallszahlen) noch nicht fertig, während die äußere aber schon rechnen möchte. Da aber die Ergebnisse der inneren Funktion fehlen bzw. unvollständig sind, kommt diese Meldung.


Schnittmengenoperator @

Möglicherweise haben Sie schon die Meldung bekommen:

Warum ist der @-Operator hier? Wir haben ein Upgrade der Formelsprache von Excel durchgeführt. Hieraus resultiert, dass Ihnen vielleicht in manchen Formeln der @-Operator auffallen wird. Ihre Formeln verhalten sich auf dieselbe Weise wie immer.

Gleichzeitig kann es sein, dass sich in Ihren Formeln auf einmal @-Zeichen nach den Gleichheitszeichen befinden, die Sie gar nicht eingetragen haben.

Dieses Verhalten hängt unmittelbar mit den Arrayformeln bzw. Arrayfunktionen zusammen. Nehmen wir obige Formel:

=WENN(C2:C13>400;C2:C13;"")

Sie liefert, wie wir gesehen haben, alle Werte, die größer als 400 sind, in und unter der Zelle mit der Formel. Was nun aber, wenn wir nur ein Ergebnis benötigen, das erste? Dieser Fall kann auftreten, wenn Formeln über mehrere Zellen gezogen oder wenn Funktionen verschachtelt werden sollen. In diesem Fall setzen wir direkt hinter das Gleichheitszeichen das @-Zeichen:

=@WENN(C2:C13>400;C2:C13;"")

Nun haben wir nur noch ein Ergebnis; in diesem Fall das erste, die 500.

Um bei diesem Beispiel zu bleiben: Setzen wir in der Formel die letzte Zeile absolut (durch das $-Zeichen), kann die Formel nach unten gezogen werden und wir haben als Ergebnis immer den Wert aus den Zellen ab der Zeile mit der Formel:

=@WENN(C2:C$13>400;C2:C$13;"")

Auf Monatsblatt beim Datum eingetragene Namen zählenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 333) Nachricht zum Beitrag an Autor Nach oben

Auf Blättern, die mit Monatsnamen benannt sind, befinden sich in Zeile 1 Ab Spalte A nebeneinander die Datumsangaben des Monats (bis AE). Unter diesen Angaben sind ab Zeile 2 die anwesenden Mitarbeiter eingetragen. Wie kann ich diese zählen?

Ab Excel 365

Zunächst muss das Blatt mit dem Monatsnamen des aktuellen Datums ermittelt werden. Das geht mit Indirekt:

=INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")

Diese Formel liefert die komplette erste Zeile mit den Datumsangaben des aktuellen Monats. Sie kann nun in der Funktion FILTER() als Suchbereich verwendet werden. Damit können alle Mitarbeiter des Tages ermittelt werden (hier bis Zeile 30):

=FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())

Die Formel gibt aus der Datumsspalte alle Einträge zurück, die bis Zeile 30 liegen.

Da aber mehr Zeilen (wie hier bis 30) verwendet werden müssen (die maximale Anwesenheit muss ja berücksichtigt werden), liefert diese Formel für die leeren Zellen, in denen also kein Mitarbeiter eingetragen ist, jeweils eine 0. Das kann also weder mit ANZAHL() noch mit ANZAHL2() gezählt werden, da sonst die 0 immer einfließen würde.

Wir verwenden im Beispiel SUMME(Wenn( und lassen die Einträge zählen, die <> 0 sind:

=SUMME(WENN(FILTER(INDIREKT(TEXT(HEUTE();"MMMM")&"!A2:AE30");INDIREKT(TEXT(HEUTE();"MMMM")&"!A1:AE1")=HEUTE())<>0;1;0))

Ältere Versionen

Hier ist die Formel etwas länger:

=ANZAHL2(INDIREKT(TEXT(HEUTE();"MMMM")&"!"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"2:"&LINKS(ADRESSE(1;VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"));4);1+(VERGLEICH(HEUTE();INDIREKT(TEXT(HEUTE();"MMMM")&"!1:1"))>26))&"1000"))

Bedingte Formatierung: Drei Preise - günstigsten farbig kennzeichnenFormellösungArrayfunktion/MatrixfunktionTipp

Kategorien: Format ▸ Bedingt und Tabelle ▸ Matrix

(Tipp 326) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die günstigsten Preise hervorheben?

In einer Tabelle stehen in einer Spalte (hier: D) untereinander verschiedene Produktnamen. Bei jedem Produkt stehen rechts daneben (hier: E, F und G) die Preise für drei verschiedene Länder.

Die Preise bei jedem Produkt sollen automatisch farbig markiert werden: der günstigste grün, der höchste rot und ansonsten gelb.

Lösung


Die Aufgabe wird mit Bedingte Formatierung im Ribbon Start erfüllt.

Dazu müssen für jede der drei Preisspalten jeweils drei Regeln erstellt werden - eben eine für den günstigsten, eine für den höchsten und eine für den restlichen Preis. Wir fangen an mit der ersten Spalte, in der Preise enthalten sind, hier also E2:E19 und markieren diese. Anschließend rufen wir die bedingte Formatierung auf und wählen dort Neue Regel.

In der Auswahlliste wählen wir den Punkt Nur Zellen formatieren, die enthalten. Dies ist die Basis für die weiteren Formatierungen.

Grüne Formatierung

Die grüne Formatierung soll erscheinen, wenn der Preis am niedrigsten ist. Hier bietet sich also die Funktion MIN zum Vergleich mit den anderen beiden Preisen an. Wir stellen also beim Zellwert auf kleiner als und tragen rechts ein: =MIN(F2:G2). Mit dem Button Formatieren legen wir die grüne Hintergrundfarbe fest.

Mit OK übernehmen wir die Regel.

Rote Formatierung

Mit rotem Hintergrund soll gekennzeichnet werden, wenn der Preis am höchsten ist. Dazu ist die Funktion MAX sinnvoll.

Wir bleiben also im Dialog und wählen Neue Regel. Anschließend führen wir die gleichen Schritte wie bei der grünen Formatierung durch, nur eben mit größer als, MAX und der roten Formatierung.

Gelbe Formatierung

Einen gelben Hintergrund soll die Zelle bekommen, wenn der Preis nicht am höchsten und nicht am niedrigsten ist, wenn er also zwischen den beiden anderen Preisen liegt.

Wir bleiben weiterhin im Dialog und wählen wieder Neue Regel. Wenn wir nun wieder Nur Zellen formatieren, die enthalten anklicken, müsste beim Zellwert schon zwischen ausgewählt sein - wenn nicht, dies nachholen.

In die Felder schreiben wir jeweils =F2 und =G2. Gelben Hintergrund auswählen und bestätigen. Nun müssten die drei Regeln im Manager angezeigt werden.

Wenn nun Zahlen eingetragen werden, sollte das beim ersten Land funktionieren, die Hintergründe müssten automatisch entsprechend der Zahlen formatiert werden. Allerdings müssen die Schritte noch für die anderen beiden Länder wiederholt werden. Beim mittleren Land aufpassen; die Zellen in MIN und MAX müssen hier mit Semikolon getrennt werden, weil es keine Bis-Bereiche sind, sondern auseinanderliegende Zellen.


Auswertung: Matrixformel

Unter der Tabelle sollen nun noch zu jedem Land die grünen, roten und gelben Zellen gezählt werden. Das Problem: Mit einer reinen Formellösung können keine farbigen Zellen gezählt werden.

Ein Lösungsansatz ist, zu zählen, wie viele Zellen in der jeweiligen Spalte größer bzw. kleiner als die Zellen daneben sind. Damit wir nicht jede Zeile einzeln berücksichtigen müssen, verwenden wir dazu eine Matrixformel, die den Bereich einer Spalte über alle Zeilen hinweg erfasst.

Hinweis: Die geschweiften Klammern nicht eingeben, sondern die Eingabe der Formel mit Strg + Umschalt + Enter abschließen. Damit erscheinen die geschweiften Klammern automatisch. Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig.

Wir beginnen beim ersten Land, hier mit der Formel in E24. Es sollen die Zellen gezählt werden, die in den Zeilen die niedrigsten Preise haben. Dabei zählen wir aber nicht, sondern wir addieren für jede dieser niedrigsten Zellen die 1. Wir bilden also die Summe, hier das Grundgerüst:

=SUMME( wenn Zahl in der Zeile am niedrigsten ; dann addiere 1; sonst addiere 0))

Wir verwenden hier diese Logik (andere Varianten gibt es natürlich auch):

Wenn die Zahl beim Land 1 (E) kleiner als die Zahl beim Land 2 (F) ist, dann wenn die Zahl beim Land 1 (E) auch kleiner als die beim Land 3 (G) ist, dann addiere 1, sonst 0, sonst 0.

Wir addieren hier also zweimal 0 - einmal für die erste Bedingung (Land 1 nicht kleiner als Land 2) und einmal für die zweite Bedingung (Land 1 nicht kleiner als Land 3).

Diese Struktur bauen wir in die Formel ein, so dass die (an Strg + Umschalt + Enter denken!) nun so aussieht:

{=SUMME(WENN(E2:E19<F2:F19;WENN(E2:E19<G2:G19;1;0);0))}

Damit haben wir die Anzahl der grünen Zellen beim Land 1 in E24. Die gleiche Formel kommt zu den anderen Ländern. Vorsicht, die kann aber nicht gezogen werden, weil die Zellen in den Formeln angepasst werden müssen.

Ebenfalls die gleiche Formel, nur mit dem größer als >, kommt in die Zeile 26, wo die roten Zellen gezählt werden:

{=SUMME(WENN(E2:E19>F2:F19;WENN(E2:E19>G2:G19;1;0)))}

Bei den gelben Zellen geht es einfacher - einfach alle Zellen in der Spalte zählen und die grünen und roten subtrahieren:

=ANZAHL(E2:E19)-E24-E26

Eine Beispieldatei mit dieser Lösung: 326_preisvergleiche.xlsx

Drucken: Leere Zeilen ausblendenMakro/Sub/Prozedur

Kategorie: Drucken/Seite

(Tipp 162) Nachricht zum Beitrag an Autor Nach oben

Wie kann man vor dem Druck leere Zeilen ausblenden und Druckbereich festlegen?

Der Druckbereich wird von der Zelle A10 bis zur Spalte E festgelegt. Die letzte Zeile des Druckbereichs ist hierbei die letzte Zeile, in der in Spalte A ein Eintrag vorhanden ist.

Sub druck() Dim intS As Integer, lngE As Long, lngZ As Long 'Spalte A: intS = 1 'Letzte Zeile mit Eintrag suchen: lngE = Cells(Rows.Count, intS).End(xlUp).Row 'Zeilen ausblenden: For lngZ = 10 To lngE If Cells(lngZ, 5) = "" Then Rows(lngZ).EntireRow.Hidden = True Next 'Druckbereich festlegen: ActiveSheet.PageSetup.PrintArea = "$A$10:$E$" & lngE 'Drucken: ActiveSheet.PrintOut 'Druckbereich aufheben: ActiveSheet.PageSetup.PrintArea = "" 'Zeilen einblenden: Rows.Hidden = False End Sub

Oder:

Sub ausblenden() Dim intI As Long, lngE As Long, lngL As Long, intS As Integer 'e = Erste Zeile mit Formel 'l = Zeile, in der Summe steht 's = Spalte, in der die Zellen auf "" überprüft werden sollen lngE = 1 lngL = 20 intS = 3 'Leere Zeilen ausblenden For intI = lngE To lngL If Cells(intI, intS).Text = "" Then Rows(intI).EntireRow.Hidden = True Next 'Drucken ActiveWindow.SelectedSheets.PrintOut Copies:=1 'Zeilen einblenden For intI = lngE To lngL Rows(intI).EntireRow.Hidden = False Next End Sub

Formeln in Zellen per VBA einfügenMakro/Sub/Prozedur

Kategorie: Tabelle ▸ Formeln

(Tipp 132) Nachricht zum Beitrag an Autor Nach oben

Wie kann man in einer Tabelle in verschiedene Spalten Formeln mit Hilfe eines Makros hineinkopieren ? Dabei ist die „Startzeile“ immer die selbe. Dagegen variiert die „Endzeile“ in Abhängigkeit der eingegebenen Daten.

Angenommen in Spalte A und B stehen Zahlen, in Spalte C sollen jeweils die Summenformeln stehen. Die Zeilenanzahl wird durch die Spalte B bestimmt.

Variante mit englischer Syntax:

Sub Formeleinfuegen() Dim lngI As LongPtr For lngI = 1 To Cells(Rows.Count, 2).End(xlUp).Row ActiveSheet.Range("C" & lngI).Formula = "=SUM(A" & lngI & ",B" & lngI & ")" Next End Sub

Variante mit deutscher Syntax:

Sub Formeleinfuegen1() Dim lngI As LongPtr For lngI = 1 To Cells(Rows.Count, 2).End(xlUp).Row ActiveSheet.Range("C" & lngI).FormulaLocal = "=SUMME(A" & lngI & ";B" & lngI & ")" Next End Sub



Matrixformel: Zahlen in Datumsspanne addierenFormellösungArrayfunktion/Matrixfunktion

Kategorien: Datum/Zeit ▸ Datum und Tabelle ▸ Matrix

(Tipp 327) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich Zahlen zu Datumsangaben, die in einem bestimmten Bereich liegen, addieren?

Ab Excel 365: Funktion FILTER()

Gegeben:

  • Spalte A im Datumsformat. Daten von 01.01.99 bis 31.12.99
  • Spalte B im Zahlenformat. Zahlen wie 2.340,00
  • Spalte C im Textformat. Immer vierstellige Zahlen wie 9000

Anforderung:

Summe der Zahlen in B, wenn das Datum in A in einem bestimmten Zeitraum liegt und in C eine bestimmte Zahl steht.

Beispiel: In E1 und E2 stehen Beginn und Ende der gewünschten Zeitspanne, in E3 die geforderte Zahl, die in C enthalten sein soll.

Formel:

Die Arrayfunktion FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0) liefert die Zahlen zu den Zeilen, die den Kriterien entsprechen. Das heißt, dass diese nur addiert werden müssen:

=SUMME(FILTER(B1:B35;(A1:A35>=E1)*(A1:A35<=E2)*(C1:C35=E3);0))

Quersumme bildenUDF - benutzerdefinierte Funktion

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 165) Nachricht zum Beitrag an Autor Nach oben

Wie kann man die Quersumme einer Zahl in einer Zelle bilden?

Function Quersumme(Zelle As Range) As Integer Dim intI As Integer Application.Volatile Quersumme = 0 If IsNumeric(Zelle) Then For intI = 1 To Len(Zelle) Quersumme = Quersumme + CInt(Mid(Zelle, intI, 1)) Next End If End Function

In die Zellen braucht man dann nur die Formel einzugeben:

=Quersumme(A1)

Eine interessante Lösung per Formel, die die Zeilennummern verwendet, finden Sie hier: exceltricks.blog.

Rechnen mit negativen ZeitenFormellösungTipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 228) Nachricht zum Beitrag an Autor Nach oben

Immer wieder erscheint die Frage: Wie kann ich in Excel mit negativen Zeiten rechnen?, da man oftmals bei der Verwaltung von Arbeitszeiten ein Problem mit den Fehlzeiten, sprich Minusstunden, hat.

Auf den ersten Blick scheint es, als könne Excel nicht mit negativen Zeiten rechnen, aber weit gefehlt.

Im Standard-Datumssystem ergibt =1:00-5:00 den Fehlerwert #######. Sofern die Anzeige ####### nicht wichtig ist, kann man damit allerdings weiterechnen. So ergibt die Summe der obigen Formel mit 5:00 den korrekten Wert 1:00. Ist also die Anzeige nicht wichtig, kann man es problemlos verwenden.

Eine andere und im Grunde bessere Methode bietet die Formel =(A1-B1) * 24. Dadurch werden die Stunden zwar in Industriestunden umgewandelt. Allerdings bietet dies eine erleichterte Weiterberechnung, gerade in Bezug auf die Berechnung von Löhnen.

Man kann auch zur Berechnung negativer Zeiten ins 1904-Datumssystem wechseln, allerdings beachte man dort die Kompatbilitätsprobleme.

Siehe auch:

Summe aus A1 von allen BlätternMakro/Sub/Prozedur

Kategorie: Mappe ▸ Tabellen

(Tipp 114) Nachricht zum Beitrag an Autor Nach oben

Wie kann ich die Summe von A1 aus jedem Blatt bilden?

Beispielsweise durch eine Schleife über die Blätter der Mappe:

Sub Addieren() Dim dblSummen As Double, intI As Integer dblSummen = 0 For intI = 1 To Worksheets.Count dblSummen = dblSummen + Worksheets(intI).Range("A1") Next MsgBox dblSummen End Sub

Summewenn mit mehreren KriterienFormellösungArrayfunktion/Matrixfunktion

Kategorien: Tabelle ▸ Zellen und Berechnungen

(Tipp 181) Nachricht zum Beitrag an Autor Nach oben

Wie kann man mit der Funktion SUMMEWENN auch mehrere Kriterien bestimmen?

Dazu bietet sich eine Array-Funktion an. Die erreicht man, indem man erst die normale Formel eingibt und die Eingabe mit Strg + Umschalttaste + Enter beendet. Dann werden automatisch um die Formel geschweifte Klammern ({}) gesetzt, woran man die Array-Funktion erkennt.

Beispiel: Folgende Formel summiert, wenn in Spalte A ein a und in derselben Zeile in Spalte B ein h ist, die entsprechenden Werte der Spalte C. Die geschweiften Klammern signalisieren, dass es ein Array ist - diese nicht mit kopieren/eingeben, sondern im Bearbeitungsmodus mit Strg + Umschalttaste + Enter erzeugen.

{=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))}

Ab Excel 365

Ab Excel 365 sind die geschweiften Klammern nicht mehr notwendig, die Formel kann ganz normal eingetragen und die Eingabe einfach mit Enter abgeschlossen werden:

=SUMME(WENN(A1:A11="a";WENN(B1:B11="h";C1:C11;"")))

Hier geht allerdings auch die Funktion SUMMEWENNS(), in die mehrere Kriterien als Parameter eingetragen werden können. Im Beispiel wäre das:

=SUMMEWENNS(C1:C11;A1:A11;"a";B1:B11;"h")

SVERWEIS: Zellen rechts addieren (auch mit Arrayfunktion)FormellösungArrayfunktion/Matrixfunktion

Kategorie: Tabelle ▸ Matrix

(Tipp 198) Nachricht zum Beitrag an Autor Nach oben

Per SVERWEIS lasse ich in Spalte A einen Begriff suchen. Wie ist es möglich, alle sich rechts vom gefundenen Begriff enthaltenen Zahlen zu addieren?

In E1 befindet sich der Suchbegriff.

=SUMME(INDIREKT("B"&VERGLEICH(E1;A1:A15;0)):INDIREKT("D"&VERGLEICH(E1;A1:A15;0)))


Dynamische Arrayfunktion (ab Excel 365)

Hier hat man natürlich leichtes Spiel, indem man die Funktion FILTER() verwendet. Im ersten Parameter, der Matrix, wählt man einfach den Bereich, in dem die Zahlen stehen. Da die dann auch ausgegeben werden, können die auch gleich addiert werden:

=SUMME(FILTER(B1:C5;A1:A5=E1;0))

Zeitwerte über 10 000 StundenTipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 230) Nachricht zum Beitrag an Autor Nach oben

Bei der Eingabe einer Zeit >= 10 000 wird diese als Text behandelt, man kann damit auch nicht weiterrechnen. Ist das Ergebnis einer Formel aber >= 10 000, ist das für Excel eine Zeit.

Um mit Zeitwerten über 10 000 Stunden rechnen zu können, müssen diese also nur auf mehrere Zellen verteilt und addiert werden. Mit dieser Summe kann problemlos weitergerechnet werden.



Zeitwerte über 24 Stunden oder 60 MinutenFormellösungTipp

Kategorien: Basics ▸ Datum/Zeit und Datum/Zeit ▸ Zeit

(Tipp 229) Nachricht zum Beitrag an Autor Nach oben

Um Zeitwerte aufzurechnen, zum Beispiel bei Laufzeiten oder Arbeitsstunden, die über 24 Std. oder 60 Min. hinausgehen, erhält man in Excel ein falsches Ergebnis. So ergibt zum Beispiel die Summe von 6:00 und 22:00 das Ergebnis 4:00.

Die Ursache für diesen „Fehler“ liegt daran, daß zu der Stundenangabe das Tagesdatum hinterlegt wird. So rechnet Excel

01.01.1999 06:00 + 01.01.1999 22:00 = 02.01.1999 04:00

Um dies zu verhindern, muss man das Format der Ergebniszelle ändern. Weist man der Zelle das Format [h]:mm zu, erhält man das gewünschte Ergebnis 28:00. Die eckigen Klammern zeigen Excel an, dass über 24 Stunden hinausgerechnet wird. Analog dazu kann man auch über 60 Minuten mit [m]:ss rechnen.