Games Of Life

Wir machen nicht nur Excel – auch „richtige“ Programmiersprachen sollen (demnächst?) zum Einsatz kommen. Aktuell lerne ich C#.

Schöne Sachen kann man damit machen… zum Beispiel ist Games of Life eine tolle Übung.

Das „Spielbrett“ besteht aus Spalten und Zeilen (also ähnlich wie in Excel) und die einzelnen Zellen können zwei Zustände haben: „tot“ oder „lebendig“.

Es gibt einige wenige Regeln:

  • Eine Zelle mit weniger als X Nachbarn stirbt an Vereinsamung
  • Eine Zelle mit mehr als Y Nachbarn stirbt an Überbevölkerung
  • Eine Zelle mit Z Nachbarn bleibt am Leben

Durch unterschiedliche X, Y und Z- Werte ergeben sich unterschiedliche „Welten“. In der 23/3-Welt gilt für x=2, y=3 und z=3 – x und y (Zelle „stirbt“) werden zu 23 zusammengesetzt.

Wenn diese Berechnungen nun in einer Schleife ablaufen, entstehen dabei teilweise faszinierende Muster – es gibt oszillierende Figuren, wandernde Figuren („Raumschiffe“) und statische Figuren, die sich ohne Außeneinwirkung nicht verändern.

Hier zwei Beispiele für oszillierende Muster aus der 23/3-Welt, das „Oktagon“…

…und der „Pulsator“:

Wie aus einem recht einfachen Start-Muster komplexe Figuren entstehen, ist in diesem Video zu sehen (13/3-Welt, dieses Start-Pattern funktioniert auch in der 24/3-Welt, ist jedoch bei Weitem nicht so schön):

Selber rumspielen dürft Ihr natürlich auch, denn hier gibt es meine erste „richtige“ Software zum Download (alle bisherigen Übungen waren ja „nur“ Autos und Tiere in C# bauen, um die ganzen Klassen, Methoden, Interfaces und was weiß ich alles kennenzulernen (wobei es ja durchaus auch einige alte „Bekannte“ aus VBA gab – so ganz neu ist es nun auch nicht).

– Download –

Games of Life 
(Download startet automatisch, File: GamesOfLife.zip, Größe: 14kB)

Kurzanleitung

Das Programm nach dem Download entpacken und einfach über Doppelklick starten (keine Installation nötig).
Beim Start des Programms wird zufällig ein Bereich in der Mitte des „Spielfeldes“ mit toten und lebendigen Zellen gefüllt.

„Animation starten“ führt die Berechnungen durch, lebende Zellen werden dunkel-türkis dargestellt, tote Zellen hellblau. Über den gleichen Button wird die Animation gestoppt.

„Reset“ erzeugt ein leeres Spielfeld. Eigene Muster können durch Klick auf die Zellen Zellen erzeugt werden (Klick wandelt eine tote Zelle in eine lebende um und umgekehrt).

Die Algorithmen der jeweiligen Welten können über die Radiobuttons ausgewählt werden.

Während der Animation können die Regeln („Welten“) jederzeit geändert werden. Enbenso ist es möglich, den Status einer Zelle („tot“ bzw. „lebendig“) durch einen Mausklick auf die jeweilige Zelle während der Animation zu ändern.

 

 

Zum Einsteigen habe ich hier einige interessante Startpatterns zusammengestellt: Diese gelten alle für die 23/3-Welt und sollten möglichst mittig auf dem Spielfeld plaziert werden:
Game Of Life - Startpattern

Die beiden gegenüberliegenden „U“ (Screenshot ganz links) funktionieren auch in der 13/3 Welt (und ergeben dort eigentlich die schöneren Muster).

Natürlich lassen sich diese auch kombinieren – die Kollisionen ergeben durchaus ebenfalls interessante Effekte (hierfür eignen sich zum Beispiel die beiden Pattern rechts in der ersten Zeile: der dritte von links bewegt sich waagrecht von links nach rechts, das vierte Pattern bewegt sich von links oben nach rechts unten).

In nachfolgenden Video habe ich das Pattern unten rechts im Abstand von 5 Feldern nach unten und zwei Feldern nach rechts spiegelverkehrt gebaut und anschließend die Animation gestartet. Es entstehen sechs starre Figuren (die Würfel) und zwei Gleiter (im Screenshot oben rechts), welche sich entgegengesetzt diagonal über das Spielfeld bewegen. Sie befinden sich auf Kollisionskurs mit den Würfeln… (siehe Screenshot am Ende des Artikels im Abschnitt „Spoiler“)

Viel Spaß!

 

(Disclaimer: Ich mag solche Spielereien. Ich finde es immer wieder faszinierend, wie man mit einfachen Mitteln solche Muster erzeugen kann. Dies hat übrigens die Programmierei mit der Chemie gemeinsam: Auch dort gibt es faszinierende Kunstwerke – ich erinnere mich vage an grandiose Graphiken Ende der 90iger, in denen das Wachstum der Bierhefe dargestellt wurde. Leider finde ich diese gerade nicht (wurde damals im „Spektrum der Wissenschaft“ erwähnt). Die Muster waren gar nicht mal so unähnlich zu den hier vorgestellten. Dieser Blogeintrag ist etwas fachfremd und eher in der Richtung „Fun“ einzusortieren – aber genau deshalb mache ich das auch 🙂 )

Weiterführende Links: Conwaylife Wiki

Spoiler:

Continue reading „Games Of Life“

Dropdown Auswahlliste in Excel erstellen

In diesem Artikel zeigen wir Ihnen, wie man mit Excel ein Dropdown-Menü erstellt. Mithilfe dieser „Auswahllisten“ können vordefinierte Einträge vorgegeben werden. Dies kann sinnvoll sein, wenn mehrere Anwender eine Excel-Tabelle bearbeiten und definierte Eingaben zur Auswahl stehen sollen. So lassen sich beispielsweise unterschiedliche Schreibweisen vermeiden, was unter anderem bei der Funktion ZÄHLENWENN() von entscheidender Bedeutung sein kann:

Wenn zum Beispiel in eine Namensliste der Name „Karl Heinz und darunter „Karl Heinz  (mit einem Leerzeichen nach Heinz!) eingetragen wird, würde die Funktion ZÄHLENWENN() beide Namen als unterschiedliche Einträge betrachten und ein falsches Ergebnis liefern.
Optisch gesehen scheinen drei identische Einträge auf dem Tabellenblatt zu existieren, aber Excel merkt die Feinheit, wie der Karl Heinz in die Zellen eingetragen wurde.

Mit Hilfe von Dropdown-Menüs lassen sich solche Fehler vermeiden, da hier nur definierte Einträge zur Auswahl stehen. Voraussetzung hierfür ist natürlich, dass die Quelldaten nicht von jedem bearbeitet werden können.

Auswahlliste erstellen

Die Schaltfläche für die Erstellung des Dropdown-Menüs finden Sie im Reiter „Daten“ in der Gruppe „Datentools“:

Dort stehen drei Auswahlmöglichkeiten zur Verfügung:

  • Datenüberprüfung…
  • Ungültige Daten einkreisen
  • Gültigkeitskreise löschen

In diesem Blogartikel beschränken wir uns nur auf die „Datenüberprüfung“:
Ein Klick auf diese Auswahl öffnet folgender Dialog, mit welchem wir das Dropdown-Menü einrichten können:

Im Dropdown-Menü selektieren wir „Liste“, um in der aktiven Zelle eine Auswahlliste zu erstellen:

Nun müssen noch die Einträge definiert werden – hierfür gibt es zwei Möglichkeiten:

1) Bei wenigen Daten (z.B. bei einer Auswahl zwischen „Ja“ und „Nein“) werden die benötigten Wörter in das Feld „Quelle“ eingetragen. Die einzelnen Wörter werden durch Semikola getrennt.

2) Bei vielen Einträgen empfiehlt sich ein Zellbereich: Hier werden alle Einträge in einer Spalte untereinander geschrieben und dieser Bereich dann im Feld „Quelle“ referenziert (=$B$4:$B$10):

Drei Typen der Datenüberprüfung

Das Dropdown-Menü muß nicht zwingend für die Dateneingabe verwendet werden: Die Auswahlmöglichkeiten, die im Dropdown-Menü zu finden sind, können auch von Hand eingegeben werden.
Daher sollte noch eine aussagekräftige Fehlermeldung bei einer Falscheingabe definiert werden.

In dem Reiter kann zwischen drei Typen der Datenüberprüfung gewählt werden:

Auf Grund der Auswahl ergibt sich ein unterschiedliches Verhalten von Excel nach einer falschen Dateneingabe.

Bei dem nächsten Beispiel haben wir für die zu überprüfende Dateneingabe
einen Datenbereich der Zahlen von 0 bis 10 festgelegt.
Und nun legen wir noch die Erstellungen der Excel-Meldungen fest,
bei der Sie sich für eine davon entscheiden,  je nach Erfordernis.
Das ist dann auch ganz schnell realisiert:

1) „Stop
Der Fehler muss korrigiert werden, andere Eingaben sind nicht zulässig. „Wiederholen“ führt zurück in den Bearbeitungsmodus der Zelle.

1.1) Erstellung der Fehlermeldung

1.2) Excel zeigt nach der unkorrekten Dateneingabe folgende Meldung

 

2) „Warnung
Es wird ein Hinweis angezeigt, dass ungültige Werte eingegeben wurden.
Ungültige Daten werden nach Rückfrage zugelassen. „Nein“ führt zurück in den Bearbeitungsmodus der Zelle.

2.1) Erstellung der Fehlermeldung

2.2) Excel zeigt nach der unkorrekten Dateneingabe folgende Meldung:

 

3) „Information
Ein Hinweis wird angezeigt, dass ungültige Daten eingeben wurden.
Ungültige Daten werden ohne weitere Rückfrage zugelassen.

3.1) Erstellung der Fehlermeldung

3.2) Excel zeigt nach der unkorrekten Dateneingabe folgende Meldung:

Hinweis: Ein Hinweis wird natürlich auch dann angezeigt, wenn man z.B. einen oder mehrer Buchstaben oder Sonderzeichen eingibt.

Und jetzt nur noch eine Eingabehilfe festlegen

Dem Anwender kann man natürlich noch die Dateneingabe erleichtern,
dass er keine falschen Dateneingabe vornimmt. Dies wird realisiert,
in dem man auf der Registerkarte „Eingabemeldung“ noch einen Hinweistext festlegt.

Der Hinweis wird dem Anwender angezeigt, wenn er die Eingabezelle (hier B6)
mit der Maus markiert:

 

Zeilenumbruch in Excel

Oft wird gefragt, wie man einen Zeilenumbruch in einer erzwingt.
Dies ist ganz einfach – und es gibt mehrere Möglichkeiten.

Ein langer Text wird in Excel ohne Zellformatierung über den rechten Zellrand hinaus angezeigt, sofern die Zellen rechts neben der Zelle mit dem langen Text leer sind:

Sobald jedoch ein Zelle mit einem Inhalt gefüllt ist (und hier reicht ein Leerzeichen), wird der „überstehende“ Text nicht angezeigt:

Hier wäre ein Zeilenumbruch praktisch, um den kompletten Zellinhalt anzuzeigen. Dies lässt sich auf mehreren Wegen bewerkstelligen:

Variante 1) Im Startmenü gibt es den Button „Zeilenumbruch“:

Ein Klick und lange Texte werden automatisch umgebrochen:

Variante 2) Das gleiche Ergebnis lässt sich über den Zellformat-Dialog erzielen. Diesen erreicht man entweder über die Tastenkombination [Strg]+[1] oder über einen Rechtsklick in die Zelle und „Zellen formatieren“.
Im Reiter „Ausrichtung“ finden Sie die Checkbox „Zeilenumbruch“.

Excel ist manchmal konsequent inkonsequent – und Zeilenumbruch kann auch „Textumbruch“ heißen (in manchen Excel-Versionen gibt es sogar beide Bezeichnungen): Die Checkbox „Zeilenumbruch“ hat trotz des unterschiedlichen Namens exakt die gleiche Funktion wie der Button „Textumbruch“ im Startmenü.

Variante 3) Der manuelle Zeilenumbruch. Hierfür empfiehlt es sich, die Bearbeitunsleiste aufzuziehen. Mit der Maus wird dafür an der unteren Kante der Bearbeitungsleiste mit gedrückter Maustaste nach unten gezogen (der Cursor ändert sich zum Doppelpfeil):

Mit der Tastenkombination [Alt]+[Enter] können nun an geeigneter Stelle die Zeilenumbrüche eingefügt werden.
Alternativ kann man dies auch direkt in der Zelle machen: In den Zell-Editiermodus gelangt man über [F2]

Variante 4) Falls längere Texte das Ergebnis einer Formel sind, kann ein Zeilenumbruch über die Excel-Funktion ZEICHEN(10) eingegeben werden:
Der Text wird in Anführungszeichen geschrieben und an der gewünschten Stelle wird &ZEICHEN(10)& eingefügt:

=FUNKTION(Argument;"Lorem ipsum dolor sit amet,"&ZEICHEN(10)&"consetetur sadipscing elitr,"&ZEICHEN(10)&"sed diam nonumy eirmod tempor invidunt ut labore et dolore magna";Argument)

Dies kann beispielsweise bei WENN-Bedingungen mit langen Textausgaben sinnvoll sein. Der Zeilen- oder Textumbruch muss jedoch eingeschaltet sein (sh. Variante 1+2):

Die Excelfunktion ZÄHLENWENN()

Die Funktion ZÄHLENWENN ist in Excel eine Funktion der Kategorie „Statistik“. Mit der Funktion ZÄHLENWENN() kann die Anzahl Zellen in einem definierten Bereich mit einem bestimmten Inhalt ermittelt werden.

Sie benötigt die Argumente „Bereich“ und „Suchkriterien“:
=ZÄHLENWENN(Bereich;Suchkriterien)

  • Bereich ist der Zellbereich, der durchsucht wird (Wo wird gesucht?)
  • Suchkriterium ist der Wert, nach dem gesucht werden soll (Wonach wird gesucht?)

    Excel durchläuft den „Bereich“ und bei jedem Zellwert, der dem Suchkriterium entspricht, wird gezählt. Als Ergebnis erhält man also die Anzahl des gesuchten Wertes in einem Bereich.

    ZÄHLENWENN

    Man kann nach Zahlen suchen (im Screenshot nach Werten =12.000) oder nach Texten. Falls der Text hartcodiert in der Excel-Formel stehen soll, muss dieser in Anführungszeichen gesetzt werden:
    =ZÄHLENWENN(A1:A10;“Text“)

    Zählen wenn größer/kleiner/ungleich als….

    Auch dies geht – ist jedoch etwas inkonsequent von Microsoft umgesetzt: Wenn das Suchkriterium hartcodiert in der Formel steht, lautet die Schreibweise folgendermaßen:
    =ZÄHLENWENN(B3:B21;">12000")
    Der Vergleichsoperator und das Suchkriterium werden in Anführungszeichen gesetzt – theoretisch ist das also ein Textargument. Die gesuchte Zahl wird jedoch trotzdem als Zahl erkannt.

    Wenn das Suchkriterium ein Zellbezug ist, muss der Vergleichsoperator in Anführungszeichen gesetzt werden und die Zelle mit einem Ampersand (&) angehängt (verkettet) werden:
    =ZÄHLENWENN(B3:B21;">"&E2)

    Texte zählen mit ZÄHLENWENN

    Wie bereits erwähnt, kann Excel auch Texte zählen. Das Suchkriterium muss hierfür – sofern es hartcodiert wird – in Anführungszeichen stehen. Zu beachten ist jedoch: Excel unterscheidet hier nicht zwischen Groß- und Kleinschreibung!

    Wenn das Suchkriterium in einer Zelle steht, lautet die Formel ebenso wie für Zahlen:

    =ZÄHLENWENN(B3:B21;E4)

    Oder hartcodiert:

    =ZÄHLENWENN(B3:B21;"ipsum")

    Man kann jedoch noch weitere Dinge mit ZÄHLENWENN anstellen. Wer wissen will, in wieviel Zellen ein bestimmter Buchstabe vorkommt, kann dies mit Platzhaltern herausfinden:
    Mit einem Sternchen (*) ist ein beliebiger Textstring gemeint – um herauszufinden, wieviele Wörter in Spalte B mit einem „L“ beginnen, verwendet man einfach folgende Formel:
    =ZÄHLENWENN(B3:B21;E11&"*")
    Oder hartcodiert:

    =ZÄHLENWENN(B3:B21;"L*")

    Die Fragestellung, wieviele Wörter in Spalte B ein „n“ enthalten, lässt sich mit folgender Formel beantworten:

    =ZÄHLENWENN(B3:B21;"*"&E20&"*")

    Oder hartcodiert einfach

    =ZÄHLENWENN(B3:B21;"*n*")

    Übung: Und nun darf der geneigte Leser in der angehängten Beispieltabelle alle Texte mit Sternchen suchen (Die Eingabe soll in Zelle E20 erfolgen im Tabellenblatt „Zählenwenn Texte“) – Viel Spaß beim Verzweifeln und die Lösung gibt es nach dem Klick auf „Weiterlesen“. Hier die Übungsdatei zum Download: Zählenwenn (Dateiformat xlsx, ca. 12Kb)

    Continue reading „Die Excelfunktion ZÄHLENWENN()“

Der SVERWEIS() in Excel

Der SVERWEIS() ist wahrscheinlich am einfachsten anhand einer mehrfach verschachtelten WENN()-Bedingung zu erklären. Wer gleich die SVERWEIS-Anleitung lesen möchte, darf hier klicken.

Wir haben eine Liste, in der in Spalte B die einzelnen Fachgebiete von EXCELLENT aufgelistet sind. In Spalte C steht die zugehörige URL:

Sverweis Liste

In Zelle B11 soll nun ein Fachgebiet ausgegeben werden und in Zelle C11 soll die URL erscheinen.

Dies lässt sich mit einer mehrfach verschachtelten WENN()-Bedingung bewerkstelligen (die Tabelle oben dient nur zu Veranschaulichung – für die nachfolgende Formel wäre sie nicht nötig):

=WENN(B11="Entwicklung";‌"https://www.excel-‌entwicklung.de/‌excelentwicklung.php";‌WENN(B11="Validierung";‌"https://www.excel-entwicklung.de/‌excel‌validierung.php";‌WENN(B11="GMP";"https://www.excel-‌entwicklung.de/‌excelgmp.php";‌WENN(B11="Leistungen";"https://www.excel-‌entwicklung.de/‌leistungen.php";‌WENN(B11="Excellent ‌beauftragen";‌"https://www.excel-‌entwicklung.de/‌kontakt.php";"na")))))

In Kombination mit der Tabelle wäre das ganze zwar einiges kürzer, aber nicht wesentlich praktischer:

=WENN(B11=B3;C3;­WENN(B11=B4;C4;­WENN(B11=B5;C5;­WENN(B11=B6;C6;­WENN(B11=B7;C7;"na")))))

Das funktioniert zwar, ist aber reichlich unpraktisch und unübersichtlich, oder?

Außerdem hatte ich mich beim Schreiben dieser Excel-Formel auch noch mit den Klammern verzählt, was zu dieser Fehlermeldung führte:

Dies war bei dieser kurzen Formel schnell behoben – aber stellen Sie sich einmal eine größere Liste vor!
Ganz abgesehen davon, dass es Limits bei Excel-Formeln gibt: unter anderem dürfen 8.192 Zeichen nicht überschritten werden.

Und von der Nachvollziehbarkeit fangen wir hier gar nicht erst an…

Man stößt bei mehrfach verschachtelten WENN()-Bedingungen also recht schnell an seine Grenzen.

Microsoft schreibt zu dieser Vorgehensweise auf deren Supportseiten:

Seien Sie vorsichtig!
Excel erlaubt zwar das Schachteln von bis zu 64 unterschiedlichen WENN-Funktionen, dennoch ist das nicht ratsam. Warum?

Mehrere WENN-Anweisungen erfordern ein hohes Maß an Konzentration, um korrekt erstellt zu werden und um sicherzustellen, dass deren Logik über jede Bedingung bis zum Schluss ordnungsgemäß berechnet wird. Werden die WENN-Anweisungen nicht zu 100 % korrekt geschachtelt, funktioniert die Formel möglicherweise nur zu 75 % und gibt in 25 % der Fälle unerwartete Ergebnisse zurück. Unglücklicherweise ist es fast unmöglich, diese problematischen 25 % abzufangen.

Mehrere WENN-Anweisungen sind ggf. sehr schwierig zu verwalten, besonders, wenn Sie später noch einmal darauf zurückkommen und versuchen herauszufinden, was Sie, oder – noch schlimmer – eine andere Person hiermit erreichen wollten.

Mehrere WENN-Anweisungen setzen mehrere öffnende und schließende Klammern () voraus, die je nach Komplexität der Formel schwer zu verwalten sind.

Quelle: https://support.office.com/de-de/article/wenn-funktion-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2

Eleganter lässt sich diese Aufgabe mit dem SVERWEIS() lösen!

Der SVERWEIS() benötigt folgende Argumente:

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;[Bereich_Verweis])

  • Das Argument „Suchkriterium“ erklärt sich fast von selbst: nach diesem Wert soll gesucht werden – in unserem Beispiel ist dies die Eingabe in Zelle B11.
  • Matrix sind die Zellen, die durchsucht werden sollen – das ist in unserem Beispiel die Liste mit Fachbereich und URL, also die Zellen B3:C7
  • Spaltenindex ist die Spalte, in der das zu suchende Ergebnis steht. Wichtig ist hierbei, dass von der Matrix ausgegangen werden muss: in der Matrix B3:C7 steht das gewünschte Ergebnis in der zweiten Spalte und nicht in der dritten Excel-Spalte („C“)!
  • Auf Bereich_Verweis gehe ich weiter unten ein (und diesen Absatz bitte unbedingt lesen, bevor Sie mit dem SVERWEIS() experimentieren!!).

Sverweis Syntax

Die oben konstruierte WENN()-Bedingung lässt sich also durch diesen SVERWEIS() ersetzen:

=SVERWEIS(B11;B3:C7;2)

Excel macht nun folgendes:
Die erste Spalte der Matrix (also die Zellen B3 bis B7) werden durchlaufen bis das Suchkriterium aus Zelle B11 gefunden wird. Dann wird der zugehörige Wert in der gleichen Zeile, in der das Suchkriterium gefunden wurde, aus der zweiten Spalte der Matrix (Spalte C) ausgegeben.

Das optionale Argument „Bereich_Verweis“

Hier verstehe ich Microsoft nicht – das Argument ist optional und dadurch ein ständiges Ärgernis: „Braucht man nicht“, „Schreibfaul“ oder schlicht vergessen… das Weglassen dieses Arguments führt sehr gerne und oft zu Überraschungen!

Schauen wir uns die einzelnen Ergebnisse einmal an:

Das Argument „Bereich_Verweis“ kann der Wert WAHR oder FALSCH (bzw. 1 oder 0) sein.
Wenn das optionale Argument weggelassen wird, geht Excel automatisch von WAHR aus.

Mit WAHR wird eine ungefähre Übereinstimmung gesucht – also der erste Wert in der Matrix, der „irgendwie“ zur Eingabe passt, wird verwendet und der Wert aus der zugehörigen Spalte ausgegeben. Bei alphabetisch sortierten Listen funktioniert das einigermaßen (kann aber auch hier zu Überraschungen führen).

Mit FALSCH hingegen wird die exakte Übereinstimmung gesucht.

Kurz: Um das richtige Ergebnis zu erhalten, muss man FALSCH in die Formel schreiben! (Ja, das ist Excel-Logik vom Feinsten.)

Der korrekte SVERWEIS lautet also

=SVERWEIS(B23;B15:C19;2;FALSCH)
oder
=SVERWEIS(B23;B15:C19;2;0)

…und dann stimmen auch die Ergebnisse:

Achtung:Wenn der SVERWEIS kopiert werden soll, muss das Argument Matrix absolute Zellbezüge enthalten:
=SVERWEIS(B23;$B$15:$C$19;2;FALSCH)

Hier gibt es noch eine Übungsdatei zum Download.

Die WENN()-Funktion in Excel

Excel-Jünger fragen häufig nach einer „Wenn dann“-Funktion. Diese gibt es und so eine Wenn-Abfrage ist auch nicht besonders schwer – dies ist die gute Nachricht. Die schlechte Nachricht: man kann auch bei so einfachen Funktionen relativ dumpf auf dem harten Boden der Tatsachen aufprallen.

Aber keine Sorge: Wir fangen mit einem ganz einfachen Beispiel an und kommen erst dann zu den „Excel-Fallen“

Die WENN()-Funktion

Die gesuchte Funktion heißt WENN() und benötigt folgende Argumente:

=WENN(Prüfung;Dann_Wert;Sonst_Wert)

Das war es auch schon – aber was bedeutet das nun?

Mit Prüfung ist die zu erfüllende Bedingung gemeint. Meist ist dies ein Vergleich zwischen einer Zelle und einem Formelergebnis oder einer Konstante. Auch Kombinationen sind hier möglich, wie z.B. der Vergleich zwischen zwei Formeln – das Argument Prüfung erhält jedoch immer einen Vergleich, welcher WAHR oder FALSCH ist.

Mit Dann_Wert ist der Wert gemeint, der ausgegeben wird, wenn die Bedingung erfüllt ist – auch dies kann ein Text, eine Zahl, ein Zellbezug oder das Ergebnis von Berechnungen sein: Das Ergebnis der Prüfung ist WAHR.

Mit Sonst_Wert ist der Wert gemeint, der ausgegeben wird, wenn die Bedingung nicht erfüllt ist – auch dies kann ein Text, eine Zahl, ein Zellbezug oder das Ergebnis von Berechnungen sein: Das Ergebnis des Arguments Prüfung ist FALSCH.

Vergleich einer Zelle mit einer Konstante:

Wir schreiben in Zelle B2 eine 1 und in Zelle C2 folgende Excel-Formel:

=WENN(B2=1;"Eins";"keine Eins")

Wenn-Bedingung in Excel

 

In Worten besagt diese Formel nichts anderes als:

  • WENN der Wert in Zelle B2 gleich 1 ist
  • DANN (WAHR) schreibe als Ergebnis den Text „Eins“
  • ANDERNFALLS (FALSCH) schreibe als Ergebnis den Text „keine Eins“

Um auch den Zweig „Andernfalls“ zu testen, geben wir nun in Zelle B2 eine beliebige Zahl ungleich 1 ein – und voila:

Äpfel mit Birnen vergleichen

Wie bereits oben erwähnt, können das Argument Prüfung auch ein Text sein. Zu beachten ist hierbei, dass Textargumente in Excel-Funktionen in Anführungszeichen geschrieben werden müssen!

Nehmen wir an, in einem Obstladen kostet das Kilo Äpfel 1,99 Euro und alle anderen Obstsorten 2,99 Euro.

Wir schreiben in Zelle B2 Äpfel und in Zelle C2 die Formel
=WENN(B2="Äpfel";1,99;2,99)

In Zelle B3 schreiben wir Birnen und kopieren die Formel aus Zelle C2 in Zelle C3 und erhalten folgendes Ergebnis (Anm.: Im Screenshot sind die Berechnungszellen als „Währung“ formatiert):

Das war wirklich nicht schwer, oder?
Prima – dann kommen wir nun zu den schlechten Nachrichten:

Auch mit einer einfachen WENN()-Bedingung können böse Fehler passieren

Nehmen wir wieder die Formel aus dem ersten Beispiel
=WENN(B2=1;"Eins";"keine Eins")
und ändern diese ab in
=WENN(B2="1";"Eins";"keine Eins")

Wir setzen also die 1 aus dem Argument Prüfung in Anführungszeichen.
Das Ergebnis wird wie folgt aussehen:

Logisch: Wir vergleichen den Textstring „1“ (wir erinnern uns: Textargumente werden in Anführungszeichen geschrieben) mit der Zahl 1 – und das ist eben (in diesem Fall) nicht das gleiche und folglich wird das das Argument „Sonst_Wert“ ausgegeben.

Um das ganze noch verwirrender zu machen: wenn die Zelle B2 das Textformat bekommt und anschließend(!!) die 1 eingegeben wird (die 1 wäre dann linksbündig und in der linken oberen Ecke befindet sich ein kleines grünes Dreieck), dann befindet sich in der Zelle ebenfalls ein Textstring und das erwartete Ergebnis wird ausgegeben:

Fazit: Auch wenn es gleich aussieht, ist es noch lange nicht dasselbe!

Vergleich eines Formelergebnisses mit einem Zellwert

Eine weitere böse Falle sind Ergebnisse von Berechnungszellen, die mit einer WENN()-Bedingung beurteilt werden sollen.
Dies ist zum Beispiel immer dann der Fall, wenn ein Ergebnis mit einem Akzeptanzkriterium abgeglichen werden soll – z.B. bei einer Chargenfreigabe.

Nehmen wir an, wenn ein Ergebnis <=0,3 ist, dann ist die Charge für den Verkauf freigegeben, wenn das Ergebnis >0,3 ist, wird die Charge nicht freigegeben.

Als Berechnung nehmen wir eine einfache Subtraktion, nämlich 25,3-25,0. Das Ergebnis ist 0,3 und damit ergibt die Prüfung „<=0,3" den Wert wahr und unsere WENN()-Bedingung soll den Text "freigegeben" ausgeben. Also: In Zelle C2 kommt 25,3 In Zelle C3 kommt 25,0 In Zelle C4 kommt =C3-C2 Das Akzeptanzkriterium kommt in Zelle F2: 0,3 Die Ausgabetexte "freigegeben" bzw. "Nicht freigegeben" kommen in Zelle G2 und G3 In Zelle C6 schreiben wir folgende Formel: =WENN(C4<=F2;G2;G3)

Diese besagt nichts anderes als:

  • WENN die Zahl in Zelle C4 kleiner oder gleich der Zahl in Zelle F2 ist
  • DANN gib den Text aus Zelle G2 aus (also „freigegeben“)
  • ANDERNFALLS gib den Text aus Zelle G3 aus (also „nicht freigegeben“)

Auf sehr vielen Rechnern (meiner Erfahrung nach hauptsächlich Intel-Rechner, die AMD-Rechner scheinen da etwas „robuster“ zu sein) passiert folgendes:

Des Rätsels Lösung ist ganz einfach: hierzu muß das Zellformat der Zelle C4 auf 15 Nachkommastellen erweitert werden:

Wir sehen dann schon im dem Screenshot oben, dass das Zellergebnis der Berechnung 25,3-25,0 in Excel 0,300000000000001 ergibt – und das ist tatsächlich größer als 0,3.

Behörden wie z.B. die FDA – Office of Regulatory Affairs schreiben vor, dass solche Ergebnisse für qualitätsrelevante Excel-Auswertemappen gerundet werden müssen. Die korrekte Formel lautet also im obigen Beispiel
=WENN(RUNDEN(C4;1)<=F2;G2;G3)

Man könnte die Formel auch so lassen und das Ergebnis in Zelle C4 runden (was auch eher der Best Practice entspricht) - aber dann hätte ich hier kein Beispiel gehabt, dass das Argument Prüfung auch eine Formel bzw. Excel-Funktion enthalten kann. ;)

Zusammenfassung:

  • Eine WENN()-Bedingung enthält die Argumente Prüfung, Dann_Wert und Sonst_Wert
  • Wenn das Argument Prüfung WAHR ergibt, wird das Argument Dann_Wert ausgegeben, andernfalls Sonst_Wert
  • Zu beachten sind unter anderem Rundungen und Zellformate sowie die Schreibweise der Argumente (insbesondere Anführungszeichen)

In diesem Sinne: Viel Spaß mit der WENN()-Bedingung :)

PS:
=WENN("Excel">"kompliziert";"Besuche diese Seite!";"Viel Spaß mit Excel!")

Syntax („Struktur“) von Excelformeln

Excelformeln sind nach einem definierten Schema (Syntax) aufgebaut:

=FUNKTION(Argument1;Argument2;…;Argument255)

Excelformeln Syntax

Hierbei gibt es folgende Grundregeln:

  • Eine Funktion beginnt immer mit =
  • Eine Funktion hat immer einen Namen: z.B. SUMME() oder MITTELWERT()
  • Die Argumente einer Funktion stehen immer in Klammern: ()
  • Mehrere Argumente werden durch ein Semikolon getrennt (Deutschsprachiges Excel)
  • Die Argumente einer Funktion können Zellbezüge, Funktionen, Zahlen, Textkonstanten (immer in „Anführungszeichen“) oder die Wahrheitswerte WAHR oder FALSCH sein
  • Argumente können von einer weiteren Funktion errechnet werden („verschachtelte Funktionen“)
  • Einige Funktionen benötigen keine Argumente: z.B. JETZT(),HEUTE() oder PI()
  • Aufschlüsseln von verschachtelten Funktionen erfolgt von innen nach außen

 

Außerdem gibt es folgende Limits:

  • Maximal 8 192 Zeichen
  • 64 Verschachtelungsebenen
  • 255 Argumente

 

Die Anzahl der Funktionen in einer Formel wird durch diese Grenzen limitiert.

Absolute und relative Zellbezüge in Excel

Die Zellbezüge in Excel (aber auch in anderen Tabellenkalkulationen wie z.B. LibreOffice) können absolut oder relativ sein.

Relative Zellbezüge ändern sich beim Kopieren, absolute Zellbezüge sind „fix“.
Ein absoluter Zellbezug bekommt ein Dollarzeichen ($) vorangestellt (Spalte bzw. Zeile)

Es können Spalten und/oder Zeilen absolut sein – Kombinationen von absoluten und relativen Bezügen sind also möglich.

Relative Spalte und relative Zeile: A1
Absolute Spalte und relative Zeile: $A1
Relative Spalte und absolute Zeile: A$1
Absolute Spalte und absolute Zeile: $A$1

Tipp: Das Dollarzeichen kann von Hand eingegeben werden oder mit [F4]:

Reihenfolge mit [F4]
A1 => $A$1 => A$1 => $A1

Absolute Zellbezüge sind dann wichtig, wenn man z.B. in einer Formel eine Konstante aus einer Zelle verwenden will und diese Formel über einen Zellbereich kopieren möchte: Im nachfolgenden Screenshot wurden lediglich die Formeln in den Zellen C5 und F5 von Hand geschrieben und dann nach unten kopiert.

Absoluter und relativer Zellbezug in Excel