Excel-Formeln für bedingte Summen und bessere WENN-Bedingungen
Aktualisiert

Tipps zu Office 365

Diese Excel-Formeln erleichtern die Arbeit

Zu den vielen Funktionen von Excel gehört auch die Fähigkeit, Zeit zu sparen. Diese Formel-Tipps zeigen Ihnen, wie Sie Tabellen schnell und schön formatieren, visualisieren und berechnen. Und wie Excel 2016 endlich ein altes Problem löst.

Tabelle schnell formatieren

Excel-Tabellen sind oftmals etwas trockene Materie – ein Eindruck, dem Sie mit geschickter Formatierung entgegenwirken können. Um eine bestehende Tabelle schnell anhand einer Vorlage zu formatieren, wählen Sie den Tabellenbereich aus und aus dem Menüband «Einfügen > Tabelle». Diese Funktion fügt keine neuen Werte ein, sondern formatiert eine bestehende Tabelle.

Im Dialogfenster legen Sie fest, ob Ihre Tabelle Spaltenüberschriften besitzt oder nicht. Anschliessend klicken Sie auf «Ok», um eine hübsch dargestellte Tabelle samt Sortierfunktion über die Spaltenüberschriften zu erhalten.

Die verschiedenen Vorlagen finden Sie im Menüband «Entwurf». Um die Formatierung zu ändern, wählen Sie die Tabelle erneut aus und aus obigem Menüband eine passende Tabellenformatvorlage. Diese Vorlagen können Sie um eigene ergänzen, beispielsweise, um Hausschriften und -farben zu berücksichtigen.

Zellen mit Platzhalter-Werten füllen

Sie erstellen eine Tabellenvorlage, die Sie nun mit Werten füllen möchten, um die Formeln zu testen? Dabei hilft Ihnen die Funktion «ZUFALLSBEREICH». Sie generiert einen Wert aus einem vordefinierten Zahlenbereich: «=ZUFALLSBEREICH(1;1000)» ergibt einen Wert zwischen 1 und 1000.

 

Mit Zufallszahlen füllen Sie eine Tabelle schnell mit «Blindtext».

 

Fügen Sie die Formel in die linke obere Zelle des gewünschten Bereichs ein. Packen Sie nun die Zellmarkierung mit der Maus am Quadrätchen in der rechten unteren Ecke und erweitern Sie den Bereich über die gewünschten Zellen, um diese mit Zufallswerten zu füllen.

Zeitperioden berechnen

Aus Kompatibilitätsgründen bietet Excel auch in der aktuellen Ausgabe von Microsoft Office 365 einige Formeln, die aus dem längst nicht mehr existierenden Lotus-1-2-3 stammen. Diese Funktionen tauchen auch nicht in der Funktionsliste auf. Praktisch ist «DATEDIF» – die Funktion will englisch geschrieben und «bedient» werden. Sie berechnet den Unterschied zwischen Zeitangaben in Jahren, Monaten oder Tagen.

So berechnet beispielsweise «=DATEDIF(„1.1.2016″;HEUTE();“d“)», wie viele Tage seit anfangs dieses Jahres verstrichen sind. Anfangs- und Enddatum können natürlich auch in einer Zelle stehen. Die Zeiteinheit als dritter Parameter muss englisch angegeben werden, also «y» für Jahr, «m» für Monat und «d» für Tag.

Summieren und Zählen unter Bedingungen

Angenommen, Sie haben in einer Tabelle das Alter von Personen erfasst und möchten jetzt wissen, wer erwachsen und wer minderjährig ist. Hier kommt die Funktion «ZÄHLENWENN» ins Spiel. Sie zählt, wie viele Zellen in einem Bereich ein Kriterium erfüllen. Für das unten abgebildete Beispiel lautet die Formel:

=ZÄHLENWENN(B2:B8;“>17″)

Excel-Funktionen SUMMEWENN und ZÄHLENWENN
Hier hilft SUMMEWENN, das Durchschnittsalter der Erwachsenen zu berechnen.

Um nun das Durchschnittsalter auszurechnen, können Sie die Funktion «SUMMEWENN» verwenden. Sie ist gleich aufgebaut. Im Beispiel lautet die Formel so – in Zelle B9 ist die Anzahl erwachsener Personen festgehalten:

=SUMMEWENN(B2:B8;“>17″)/B9

Für die Altersbestimmung können Sie übrigens die bereits erwähnte «DATEDIF»-Funktion verwenden – praktisch, um im Gastgewerbe schnell zu berechnen, wer über 16 oder 18 ist. Wenn im Feld A2 das Geburtsdatum steht, rechnet =DATEDIF(A2;HEUTE();“y“) das aktuelle Alter in Jahren aus.

Express-Diagramme mit Sparklines

Um Zahlenverläufe schnell zu visualisieren und damit besser lesbar zu machen, können Sie «Sparklines» nutzen. Hierbei handelt es sich um «Diagramme in einer Zelle», die Sie an Ihre Tabelle anhängen.

Wählen Sie eine an die Tabelle angrenzende Spalte aus, und aus dem Menüband «Einfügen» eins der vorgeschlagenen Sparklines-Diagramme. Im anschliessenden Dialogfeld wählen Sie den Bereich der Tabelle aus, dessen Zellen oder Spalten grafisch ausgewertet werden sollen. Ein Klick auf «Ok» fügt die Minidiagramme ein.

Um Sparklines zu formatieren, wählen Sie eine Sparklines-Zelle aus und öffnen das Menüband «Entwurf».

Verschachtelte Bedingungen gelöst (endlich!)

Für diesen Tipp benötigen Sie Excel 2016 mit den aktuellen Updates!

Mit dem jüngsten Update zu Excel 2016 versorgt Microsoft endlich verschachtelte WENN-Konstruktionen in der Mottenkiste. Stellen Sie sich folgende Situation vor: In einer Spalte haben Sie Zahlen von 1 bis 7, die Wochentagen entsprechen (1 = Montag, 2 = Dienstag etc.). In der Spalte rechts möchten Sie nun den Wochentag als Text ausgeben. Bislang mussten Sie hierfür zahlreiche WENN-Bedingungen verschachteln. Nun hat Excel aber die aus zahlreichen Programmiersprachen bekannte «Switch»-Funktion eingeführt, die in der deutschen Version etwas missverständlich «ERSTERWERT» heisst. Um eine Zahl in einen Wochentag umzuwandeln, können Sie folgendes Konstrukt verwenden (das sich der Lesbarkeit halber auf die ersten drei Wochentage beschränkt):

=ERSTERWERT(B13;1;“Montag“;2;“Dienstag“;3;“Mittwoch“;“keine Übereinstimmung“)

Dieses Konstrukt ergibt «Montag», wenn in Zelle B13 «1» steht, «Dienstag» bei «2» und «Mittwoch» bei «3». Bei allen anderen Werten erscheint «keine Übereinstimmung».

 

Verschachtelte Bedingungen lassen sich nun dank neuer Formeln elegant lösen. 

Wenn Sie Bedingungen überprüfen wollen («wenn Zellwert >» etc.), können Sie die ebenfalls neue Funktion «WENNS» (englisch: IFS) verwenden, die gleich aufgebaut ist wie ERSTERWERT. Elegant, nicht wahr?

Jetzt lesen

Schreibe einen Kommentar zu Timo Antworten abbrechen

Ihre E-Mail-Adresse wird nicht veröffentlicht.

11 Kommentare zu “Diese Excel-Formeln erleichtern die Arbeit

  1. ICH VERSTEHE NICHT WAS BEIM NICHT ROBOTER EIN KOMISCHES WORT GESCHRIEBEN,WEIL FÜR MICH EIN SCHWERES WORT IST.ICH BIN SCHWERHÖRIG.

      1. Wir hatten damals diese Formel bilateral diskutiert. Aber ich wiederhole mich gerne hier: Wenn beide Bereiche identisch sind, reicht es, den Bereich einmal anzugeben. Wenn der zu summierende Bereich an einem anderen Ort in der Tabelle steht, muss er explizit angegeben werden.

  2. Seit ca. 1 Jahr haben wir das Swiss-Paket abo für Fr.69.00i.Den Betrag senden wir im Dauerauftrag monatlich über unsere Bank. Wir haben Ihnen bereits 2X mitgeteilt,dass wir keine Rechnung per Post wünschen ,was bei Ihnen nicht verstanden wird. ?Sie können das Abo Fr5.00 günstiger anbieten für den Betrag den sie monatlich für den Versand aufwenden.sie sind eh einer der teuersten Anbieter

    1. … auch hier hätte das Unternehmen Swisscom bzw der Administrator reagieren müssen
      a. zum Schutz der persönlichen Daten des Autors
      b. weil es einfach ’schlecht‘ aussieht so etwas umkommentiert stehen zu lassen