Kurs: Tabellenkalkulation mit Open
Werbung
BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: Kurs: Tabellenkalkulation mit Open-Office Programm „Calc“ für Einsteiger Inhaltsverzeichnis 1. Das Programm startet........................................................................................................2 2. Aktivieren neuer Zellen......................................................................................................2 3. Eingabe von Informationen in eine Zelle...........................................................................3 4. Mehrere Zellen markieren und formatieren.......................................................................4 5. Formel aufstellen................................................................................................................5 6. Kopieren von Formeln........................................................................................................6 7. Rechenoperationen............................................................................................................7 8. Zentrieren von Zahlen oder Text.......................................................................................7 9. Aufgabe 1: Lohn.................................................................................................................8 10. Zeilenumbruch von Text in einer Zelle.............................................................................9 11. Zahlenformate ändern......................................................................................................9 12. Aufgabe 2: Baumaterial...................................................................................................9 13. Errechnen von Zeitdifferenzen.......................................................................................10 14. Die Summenfunktion......................................................................................................10 15. Aufgabe 3: Kostümverleih..............................................................................................10 16. Kopieren von Zellen.......................................................................................................11 17. Aufgabe 4: Buchhandlung „Erst lesen, dann kaufen!“...................................................11 18. Zellen formatieren..........................................................................................................12 18.1 Rahmen und Linien......................................................................................................12 19. Absolute und relative Zellbezüge...................................................................................13 Übungen: Videothek.............................................................................................................14 Übung: Wagenverkauf.........................................................................................................14 20. Wenn-Formel.................................................................................................................16 20.1 Beispiele.......................................................................................................................17 20.2 Schriftliche Übung........................................................................................................18 21. Funktionen - Maximum - Minimum................................................................................20 22. Markieren getrennter Zellen...........................................................................................21 23. Aufgabe 5: Blumencenter..............................................................................................21 24. Aufgabe 6: Miete............................................................................................................22 25. Die verschachtelte WENN-Formel.................................................................................23 26. Aufgabe 7: Promille........................................................................................................25 27. Aufgabe 8: Zuschüsse...................................................................................................26 Anhang:................................................................................................................................27 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 1/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 1. Das Programm startet Wenn Sie das Programm starten, sehen Sie folgenden Bildschirm: Calc ist ein Programm, mit dem Rechenoperationen durchgeführt und Daten verwaltet werden können. Die Informationen werden in Zellen eingegeben, die in einem Matrixschema (Schachbrettartig) angeordnet sind. Jede Zelle hat einen Namen, der durch ihre Lage definiert ist – zuerst der Buchstabe des Spaltenkopfes und dann die Zahl des Zeilenkopfes. Die GroßKleinschreibung bei Buchstaben ist egal. Der Name (Koordinate) der aktiven Zelle lautet also B4 bzw. b4. Die jeweilige aktive Zelle ist im Arbeitsblatt mit einer fetten Umrandung markiert. Zu Beginn startet Calc immer mit der aktiven Zelle A1. Calc ist ein Teil des OpenOffice Paketes (Calc, Writer, Impress, Math, Draw, Base) und kann unter einer Linux- als auch Windows-Oberfläche verwendet werden. Die Ausführungen dieses Lehrgangs beziehen sich auf die Version Calc 2.0.x. Werden ältere Versionen benutzt, kann es gelegentlich zu Abweichungen bei den Bildschirmdarstellungen bzw. Menüführungen kommen. 2. Aktivieren neuer Zellen Dies kann mit der Maus durch Anklicken weiterer Zellen geschehen. Wer lieber mit Tastenkombinationen arbeitet, hat folgende Möglichkeiten, von Zelle zu Zelle zu navigieren: Tastenkombinationen Bewegung RETURN-Taste Nach unten UMSCHALT+RETURN-Taste Nach oben TAB-Taste Nach rechts UMSCHALT+TAB-Taste Nach links Eine dritte Variante ist das Navigieren mit den Cursor-Tasten. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 2/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 3. Eingabe von Informationen in eine Zelle Dies kann in einer aktiven Zelle in drei verschiedenen Darstellungsformen geschehen: • als Zahl • als Text • als Formel (Rechenvorschrift), die eine Zahl als Ergebnis liefert. Jeder Eintrag in eine aktive Zelle erscheint gleichzeitig in der Bearbeitungsleiste. Beachten Sie: • Mit Zahlen kann Calc rechnen. Zahlen werden nach Aktivieren einer neuen Zelle automatisch nach rechts ausgerichtet. • Mit Text kann Calc nicht rechnen. Text wird nach Aktivieren einer neuen Zelle automatisch nach links ausgerichtet. Eine eingegebene Kombination von Zahl und Text wertet Calc als Text, richtet diesen nach links aus und kann nicht damit rechnen. Die Dateneingabe kann auch in der Rechenleiste erfolgen. Durch Drücken der RETURN-Taste wird die Eingabe bestätigt. Dieser Weg hat manchmal Vorteile. Die Eingabe lässt sich nach Wunsch korrigieren, ohne die bisherige Eingabe zu löschen. Bei Doppelklick in eine Zelle ist die Eingabe ebenfalls veränderbar, ohne sie vollständig neu eingeben zu müssen. Tragen Sie den folgenden Text in die entsprechenden Zellen ein: Und da ist auch schon das erste Problem: Die Zelle D1 ist zu klein für den Eintrag. Lösung: ➔ Platziere die Maus auf der Begrenzungslinie zwischen der Zelle D und E. ➔ Drücke die linke Maus-Taste. ➔ Ziehe bei gedrückter linker MAUS-Taste die Begrenzungslinie zwischen D und E so weit nach rechts, bis der gesamte Inhalt zu erkennen ist. Tipp: Schneller geht es mit einem Doppelklick auf die Begrenzungslinie zwischen D und E. Die optimale Breite wird automatisch eingestellt. Achten Sie dabei auf die besondere Form des MAUS-Zeigers Tipp: Beim Eingeben der Spalte mit den Wochenbezeichnungen können Sie sich die Arbeit erleichtern indem Sie die erste Zelle (A3) eingeben und danach die aktivierte Zelle mit dem MAUS-Zeiger unten rechts packen Sie und bis zur Zelle A6 ziehen. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 3/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: Nun vervollständigen Sie die Tabelle mit folgenden Zahlen: Sie sehen, die Zahlen stehen rechts und die Wörter links in der jeweiligen Zelle. Im Folgenden soll das optische Erscheinungsbild der Zahlen geändert werden. 4. Mehrere Zellen markieren und formatieren ➔ Klicke mit der MAUS in die linke oberste Zelle der zu markierenden Zelle. ➔ Halte die linke MAUS-Taste gedrückt. ➔ Zieh mit gedrückter linker MAUS-Taste bis zur äußersten rechten Zelle und danach bis zur untersten Zelle, die Sie markieren willst. Alle markierten Zellen sind nun bis auf die erste Zelle gefärbt. Es erscheint dieses Bild: Nachdem Sie die Zahlen markiert haben, klicken Sie auf das Zeichen rechts oben in der Symbolleiste. Folgendes Ergebnis erscheint auf dem Bildschirm: Im letzten Schritt sind die Zellen auf die Währung € formatiert worden. Beachte: Die Zellen der Spalte D zeigen diese Darstellung noch nicht. Im nächsten Schritt auf Seite 5 wirst Sie den Vorteil dieser Formatierung genau verstehen. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 4/27 BBS 1 Technik Kaiserslautern OpenOffice Schulung Kurs: Tabellenkalkulation Calc (OpenOffice.org) Datum: Und jetzt ist es Ihre Aufgabe, zu errechnen, wie viel Geld im Monat noch vorhanden ist oder ob die Person einen Kredit bei den Eltern aufnehmen muss. Sie müssten also rechnen: Taschengeld – Ausgaben 5. Formel aufstellen In Calc werden aber keine Zahlen in die Formel eingetragen, sondern die Zellen, in denen die Zahlen stehen. Dadurch ist es möglich, dass Sie die Zahlen in den Zellen verändern können, und die Formel berechnet das Ergebnis neu. Außerdem haben Sie die Möglichkeit, die Formel zu kopieren. Doch dazu gleich mehr. Eine Formel beginnt immer mit einem Gleichheitszeichen (=). Die Formel lautet also: =B3-C3 ➔ Schreiben Sie diese Formel in die Zelle D3 und bestätigen mit RETURN. ➔ Danach klicken Sie zurück in die Zelle D3. ➔ In der Zelle sehen Sie das Ergebnis, in der Rechenleiste können Sie die Formel erkennen, die Sie eingegeben haben. Ihr Ergebnis müsste so aussehen: Aktive Zelle „Anfasser“ oder „Ausfüllkästchen“ an der rechten unteren Ecke der aktiven Zelle Eine Formel können Sie auch direkt in der Rechenleiste eingeben. Dazu müssen Sie aber vorher die Zelle markiert haben, in der die Formel stehen soll. In einer Formel können Sie folgende mathematischen Operatoren verwenden: Addition + Subtraktion - Multiplikation * Division / Prozent % Potenz ^ Um nun die anderen Zellen auszurechnen, ist es nicht notwendig, immer wieder die Formel neu einzugeben. Die Formel für D4 müsste lauten: B4-C4, für D5 wäre es: B5-C5 usw. Sie sehen, die Formel ändert sich nur in der Zeilenangabe, aber ansonsten bleibt sie gleich. Calc ist ein Programm, das sozusagen logisch denkt: „Ich stehe in D3 und soll rechnen: B3-C3. Werde ich, die Formel, nach D4 kopiert, dann rechne ich eben: B4-C4!“ 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 5/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: Also kopieren Sie die Formel von D3 in die Zellen D4 bis D6, und das geht ganz einfach: 6. Kopieren von Formeln Die aktive Zelle ist D3, die Zelle, in der die zu kopierende Formel steht. ➔ Gehen Sie mit der Maus auf den Anfasser rechts unten in der Zelle. Die MAUS verändert ihr Aussehen zu einem kleinen schwarzen Kreuz. ➔ Drücken Sie die linke MAUS-Taste und ziehe die Maus bei gedrückter linker MAUS-Taste bis zum Feld D6. Schon ist die Formel kopiert und alle Ergebnisse stehen da. Calc übernimmt automatisch das Format der beiden Zellen der Formel und überträgt es auf die Spalte D. Das Ergebnis erscheint jetzt in €. Deine Tabelle müsste nun folgendes Aussehen haben: Sie sehen, es ist sehr einfach, große Datenmengen sehr schnell und einfach auszurechnen. Das bisher Gelernte sollen Sie jetzt an einigen Aufgaben üben. Bei manchen Aufgaben wird noch etwas Neues hinzukommen, was aber vorher erklärt wird. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 6/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 7. Rechenoperationen In Calc heißen die Rechenzeichen für Addition, Subtraktion, Multiplikation und Division OPERATOREN (es gibt noch mehr) und sind folgendermaßen vergeben: Niedrige Priorität (Vorrang) Höhere Priorität (Vorrang) Addition + Multiplikation * Subtraktion - Division / Sie finden diese Zeichen rechts auf dem Num-Block der Tastatur. Bei gleicher Priorität wird die Formel von links nach rechts ausgerechnet. Es ist wichtig, Klammern zu setzen, da Calc die mathematischen Regeln befolgt: Punktrechnung vor Strichrechnung! Aufgabe: 3*4/2=__, aber 3*2/4=__ bzw. 2+5*9=__, aber 5*(9+2)=__ Die Lösung befindet sich im Anhang 8. Zentrieren von Zahlen oder Text Die Zahlen in den Spalten B und E sollen zentriert werden. ➔ Nach der Eingabe der Zahlen markiere die Zellen B3 bis B9 bzw. E3 bis E9. ➔ Zentrieren sie durch Anklicken des entsprechenden Symbols. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 7/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 9. Aufgabe 1: Lohn 1. Erstellen Sie diese Tabelle und speichern Sie unter dem Namen Lohn ab. 2. Errechne danach über Formeln die leeren Zellen D3, F3 und H3. 3. Kopiere anschließend die Formeln in die restlichen freien Zellen. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 8/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 10. Zeilenumbruch von Text in einer Zelle Sie haben kennen gelernt, wie man eine Spalte vergrößert, damit der gesamte Text zu sehen ist. Eine weitere Möglichkeit, den gesamten Text in einer Zelle anzuzeigen, ist der so genannte Zeilenumbruch. Dabei geht man folgendermaßen vor: ➔ Trage den gesamten Text in die Zelle ein. ➔ Bestätige mit RETURN. ➔ Aktiviere die betreffende Zelle erneut. ➔ Format - Zellen - Registerkarte Ausrichtung - Eigenschaften – Zeilenumbruch Diese Formatierung müssen Sie bei den Zellen B1 bis H1 in Aufgabe 2 anwenden. 11. Zahlenformate ändern Die Zahlen in den Zellen C2 bis F14 sollen mit zwei Dezimalstellen dargestellt werden. ➔ Markiere die Zellen C2 bis F14. ➔ Format – Zellen – Registerkarte Zahlen – Kategorie: Zahl – Dezimalstelle: 2 Fehlermeldung: ######### Diese wird angezeigt, wenn die Zelle zu klein ist. ➔ vergrößere die entsprechende Spalte! 12. Aufgabe 2: Baumaterial 1. Erstelle diese Tabelle und speichere sie unter dem Namen Baumaterial ab. 2. Errechnen Sie danach über Formeln die leeren Zellen D2, D3, G2 und H2. 3. Kopieren Sie anschließend die Formel in die restlichen freien Zellen. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 9/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 13. Errechnen von Zeitdifferenzen Wie errechnen Sie die Differenz von Tagen zwischen zwei Datumsangaben? Ganz einfach: Großes (jüngeres) Datum – kleines (älteres) Datum OpenOffice.org stellt Jahreszahlen intern vierstellig dar. Zwischen dem 1.1.99 und dem 1.1.01 wird eine Differenz von zwei Jahren berechnet. Mit der Einstellung Zweistellige Jahreszahlen können Sie den Bereich an Jahren festlegen, innerhalb dessen eine zweistellige Jahresangabe zu 2000 addiert wird. Das heißt, wenn Sie hier 1.1.20 einstellen, wird die Eingabe "1.1.20" als 1.1.2020 und nicht als 1.1.1920 interpretiert OpenOffice.org interpretiert Datumsangaben mit dem Wert "0" als 30. Dezember 1899. 14. Die Summenfunktion Wenn Sie z.B.: die Zellen von B1 bis B20 in Aufgabe 3 addieren möchten, haben Sie zwei Möglichkeiten, dies über eine Formel zu erreichen: Formel 1: =B1+B2+B3+ usw. Formel 2: =SUMME(B1:B20) Summiert alle Zahlen eines Zellbereichs. Syntax: SUMME(Zahl 1; Zahl 2; ...; Zahl 30). Zahl 1 bis Zahl 30 sind bis zu 30 Argumente, deren Summe berechnet werden soll. Beispiel: Bei Eingabe der Zahlen 2; 3 und 4 in die Textfelder Zahl 1; 2 und 3 erhalten Sie als Ergebnis 9 angezeigt. SUMME(A1;A3;B5) berechnet die Summe der drei Zellen. SUMME(A1:E10) berechnet die Summe aller Zellen im Zellbereich von A1 bis E10. 15. Aufgabe 3: Kostümverleih 1. Erstellen Sie diese Tabelle nach der Vorlage und speichern Sie sie unter dem Namen Kostümverleih. 2. Errechnen Sie danach über Formeln die leeren Zellen E5 und F5 und kopieren Sie anschließend die Formeln in die restlichen freien Zellen. 3. Benutze in der Zelle F15 die Summenfunktion. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 10/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 16. Kopieren von Zellen ➔ Markieren Sie die Zellen A5 bis A11. ➔ Klicken Sie auf das Symbol für KOPIEREN. ➔ Klicken Sie in die Zelle A14 (das ist die erste Zelle des Bereichs, in den das zu Kopierende eingefügt werden soll). ➔ Drücken Sie RETURN. 17. Aufgabe 4: Buchhandlung „Erst lesen, dann kaufen!“ 1. Erstellen Sie diese Tabelle und speichern Sie sie unter dem Namen Buchhandlung ab. 2. Verschieben Sie mit der MAUS die Spaltenköpfe so, dass deine Tabelle wie die Vorlage aussieht. 3. Berechnen Sie in Zelle E5 über eine Formel den Einkaufspreis. Der Verkaufspreis ist hierbei der um 40% erhöhte Einkaufspreis. 4. Errechnen Sie danach über Formeln die leeren Zellen B14 bis F14. 5. Kopieren Sie anschließend die Formeln in die restlichen freien Zellen. 6. Benutzen Sie in der Zelle F22 die Summenfunktion. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 11/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 18. Zellen formatieren Es gibt zwei Wege, um zur Formatierungsvorlage von Zellen zu kommen: 1. Format – Zelle 2. Rechte MAUS-Taste – Zellen formatieren. Auf beiden Wegen erscheint das folgende Fenster mit verschiedenen Registerkarten. 18.1 Rahmen und Linien Um einen kompletten Rahmen um eine Tabelle der Aufgabe 4 (vorhergehende Seite) zu legen, gehst Sie so vor: ➔ Markiere die Zellen A3 bis F11. ➔ Format -Zellen -Registerkarte Umrandung ➔ Klicke auf die Schaltfläche und es werden die äußeren Zellen des markierten Bereichs eingerahmt. Die Linienart und die Farbe musst Sie vor dem Setzen der Linie auswählen. Ungewollte oder falsche Linien entfernst Sie, indem Sie erneut auf die Schaltfläche drückst, mit der Sie die Linie gesetzt hast. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 12/27 BBS 1 Technik Kaiserslautern OpenOffice Schulung Kurs: Tabellenkalkulation Calc (OpenOffice.org) Datum: 19. Absolute und relative Zellbezüge Sie erinnern sich, dass zu Beginn erklärt wurde, dass Calc ein logisch arbeitendes Rechenprogramm ist. A B C 1 2 3 =A1+B1 2 5 4 =A2+B2 3 7 5 =A3+B3 Wenn Sie in die Zelle C1 die Formel =A1+B1 eingeben und diese dann nach C2 kopieren, dann ersetzt Calc automatisch die Formel (siehe Tabelle). Dies nennt man einen relativen Zellbezug, die Formel ist nur relativ in ihrer Bedeutung mit der Zelle verbunden, denn sie ändert sich, wenn man sie kopiert. Bei der nun folgenden Aufgabe ist es so, dass Sie in der Spalte A drei Zellen mit Werten haben, aber in der Spalte B nur eine Zelle mit einem Wert, und zwar in B1. A B C 1 2 3 2 5 =A2+B2 3 7 =A3+B3 =A1+B1 Sie möchten jetzt jede Zahl aus der Spalte A mit der Zelle B1 addieren. In C1 geben Sie die gewohnte Formel ein: =A1+B1. Das Ergebnis wird sein: 5. Jetzt kopieren Sie die Formel wie gewohnt nach unten und Calc arbeitet wie gewohnt. Aus der Formel =A1+B1 wird =A2+B2 und das Ergebnis ist leider, aber logisch 5, denn die Zelle B2 ist leer. Calc soll in der Formel beim Kopieren zwar A1 durch A2 ersetzen, aber nicht B1 durch B2. B1 soll B1 bleiben, diese Zelle soll sich nicht verändern. Also “kleben“ wir die Zelle einfach fest, und das geschieht, indem man vor den Buchstaben und vor die Zahl ein $ setzt. Jetzt kann sich das Feld B1 beim Kopieren nicht mehr verändern und das gewünschte Ergebnis wird erscheinen. A B C 1 2 3 =A1+$B$1 2 5 =A2+$B$1 3 7 =A3+$B$1 Dies nennt man einen absoluten Zellbezug. In der Formel A1+$B$1 hat die Zelle B1 eine feste Beziehung, sie verändert sich beim Kopieren nicht mehr. Tipp: OpenOffice.org kann den aktuellen Bezug, in dem der Cursor in der Eingabezeile steht, von relativ nach absolut und zurück umschreiben, wenn Sie (Umschalt) (F4) drücken. Wenn Sie mit einer relativen Adresse wie A1 beginnen, gilt folgendes: Beim ersten Tastendruck wird Zeile und Spalte auf absolut gesetzt ($A$1), beim nächsten Tastendruck nur die Zeile (A$1), dann nur die Spalte ($A1), dann wird der Bezug wieder in beiden Richtungen relativ (A1). 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 13/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: Übungen: Videothek 1. Erstelle diese Tabelle und speichere sie unter dem Namen Videothek ab. 2. Verschiebe mit der Maus die Spaltenköpfe so, dass deine Tabelle wie die Vorlage aussieht. 3. Formatiere die Zellen D5 bis D14 über Format-Zellen-Registerkarte ZahlenBenutzerdefiniert. 4. Berechne die Zelle E5 über eine Formel. 5. Kopiere anschließend die Formel in die restlichen Zellen. 6. Formatiere die Zellen E5 bis E14 entsprechend den Zellen C5 bis C14. 7. Erst nachdem alle Formeln errechnet und kopiert wurden, werden am Ende die Linien gesetzt. Übung: Wagenverkauf 1. Erstelle die Tabelle und speichere sie unter dem Namen Wagenverkauf ab. 2. Verbinde die Zellen B2 bis D2. 3. Formatiere die Zelle E4 mit einem Zeilenumbruch. 4. Berechne die Zellen E5 und F5 über Formeln. 5. Kopiere anschließend die Formeln in die restlichen freien Zellen. 6. Formatiere die Zellen E5 bis E11 entsprechend den Zellen D5 bis D11. 7. Formatiere die Zellen F5 bis F11 entsprechend der Zelle F2. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 14/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 15/27 OpenOffice Schulung Datum: BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 20. Wenn-Formel Die WENN-Formel ist von besonderer Bedeutung, da man mit dieser Formel ➔ Zellen auf ihren Inhalt hin überprüfen, ➔ weitere Aktionen veranlassen, ➔ verschiedene Formeln miteinander verknüpfen kann. Grundform der WENN-Formel Die Grundform der WENN-Formel besteht aus drei Teilen. Alle drei Teile werden durch ein Semikolon voneinander getrennt. = WENN (Prüfung; Dann_Wert; Sonst_Wert) Beispiel für eine WENN-Funktion: A 1 3 2 8 B =WENN(A1>2;1;5) Diese Formel, die sich in der Zelle B1 befindet, heißt übersetzt: Wenn der Wert, der sich in der Zelle A1 befindet, größer ist als 2, dann soll in der Zelle B1 eine 1, ansonsten eine 5 erscheinen. Schauen wir uns die drei Teile der WENN-Formel einmal genauer an: Prüfung ist ein beliebiger Wert oder Ausdruck, der WAHR oder FALSCH sein kann. Dann_Wert ist das Ergebnis der Funktion, wenn die Wahrheitsprüfung WAHR ergibt. Sonst_Wert (optional) ist das Ergebnis der Funktion, wenn die Wahrheitsprüfung FALSCH ergibt. In OpenOffice.org Calc Funktionen dürfen Parameter, die als "optional" gekennzeichnet sind, nur dann ausgelassen werden, wenn ihnen kein weiterer Parameter mehr folgt. So können Sie beispielsweise in einer Funktion mit vier Parametern, von denen die letzten beiden als "optional" gekennzeichnet sind, die Parameter 4 bzw. 3 und 4 auslassen, jedoch nicht Parameter 3 allein. Es gibt die verschiedenen Vergleichsoperatoren: Vergleichsoperator Gleich (=) Kleiner als (<) Größer als (>) Kleiner gleich (<=) Größer gleich (>=) Ungleich (<>) Wirkung Zeigt die Werte, die gleich der Bedingung sind. Zeigt die Werte, die kleiner als die Bedingung sind. Zeigt die Werte, die größer als die Bedingung sind. Zeigt die Werte, die kleiner gleich der Bedingung sind. Zeigt die Werte, die größer gleich der Bedingung sind. Zeigt die Werte, die ungleich der Bedingung sind. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 16/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: Der Vergleichswert kann sein: Beispiel a eine Zahl 2 b eine andere Zelle A2 c „ein Text“ “Prima Klasse“ d eine Anweisung zu einer Berechnung A1+4 e eine Anweisung zu einer Berechnung kombiniert mit einer weiteren Formel Summe(A1:A2) 20.1 Beispiele A 1 3 B =WENN ... (a,b,c,d,e) 2 8 a. =WENN(A1>2;1;5) Wenn der Wert, der sich in der Zelle A1 befindet, größer ist als 2, dann soll in der Zelle B1 eine 1, ansonsten eine 5 erscheinen. b. =WENN(A1>A2;A2;5) Wenn der Wert, der sich in der Zelle A1 befindet, größer ist als der Wert der sich in Zelle A2 befindet, dann soll in der Zelle B1 der Wert der Zelle A2 (also eine 8), ansonsten eine 5 erscheinen. c. =WENN(A1>2;“Prima“;5) Wenn der Wert, der sich in der Zelle A1 befindet, größer ist als 2, dann soll in der Zelle B1 der Text „Prima“, ansonsten eine 5 erscheinen. Achtung: Wenn ein Text erscheinen soll, muss dieser in der WENN-Formel immer in Anführungszeichen „oben“ gesetzt werden. Wenn kein Text und keine Zahl erscheinen sollen, gibt man Anführungszeichen – Leertaste- Anführungszeichen ein. d. =WENN(A1>2;A1+4;5) Wenn der Wert, der sich in der Zelle A1 befindet, größer ist als 2, dann soll in der Zelle B1 der Wert der Zelle A1 (also 3) mit der Zahl 4 addiert werden, ansonsten soll eine 5 erscheinen. e. =WENN(A1>2;Summe(A1:A2);5) Wenn der Wert, der sich in der Zelle A1 befindet, größer ist als 2, dann soll in der Zelle B1 die Summe der Zellen von A1 bis A2 gebildet werden, ansonsten eine 5 erscheinen. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 17/27 BBS 1 Technik Kaiserslautern OpenOffice Schulung Kurs: Tabellenkalkulation Calc (OpenOffice.org) Datum: 20.2 Schriftliche Übung A B C 1 Name Alter 2 Petra ? 3 Klaus ? Die WENN-Formel soll in dem Feld C2 stehen. 1. Aufgabe: Wenn Klaus älter ist als Petra, soll eine 1 erscheinen, ansonsten eine 0. Formel:________________________________________________________________________ 2. Aufgabe: Wenn Klaus jünger ist als Petra, soll eine 1 erscheinen,ansonsten eine 0. Formel:________________________________________________________________________ 3. Aufgabe: Wenn Klaus so alt ist wie Petra, soll eine 1 erscheinen, ansonsten eine 0. Formel:________________________________________________________________________ 4. Aufgabe: Wenn Klaus älter ist als Petra, soll „Ich bin älter“ erscheinen, ansonsten „Ich bin jünger“. Formel:________________________________________________________________________ 5. Aufgabe: Wenn Petra älter ist als Klaus, soll der Name von Petra erscheinen, ansonsten nichts. Formel:________________________________________________________________________ 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 18/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 6. Aufgabe: Wenn Petra 15 Jahre alt ist, soll zu ihrem Alter eine 3 addiert werden, ansonsten soll das Alter von Klaus erscheinen. Formel:________________________________________________________________________ 7. Aufgabe: Wenn Klaus nicht so alt ist wie Petra, soll das Durchschnittsalter der beiden angezeigt werden, ansonsten soll das Alter von den beiden angezeigt werden. Formel:________________________________________________________________________ 8. Aufgabe: Wenn Petra jünger ist als Klaus, soll die Summe der beiden Alterswerte errechnet werden, ansonsten soll nichts erscheinen. Formel:________________________________________________________________________ 9. Aufgabe: Wenn Klaus und Petra zusammen älter als 20 Jahre sind, soll der Text folgen „Wir sind alt“, ansonsten soll der Name von Petra erscheinen. Formel:________________________________________________________________________ 10. Aufgabe: Wenn die ältere Person der beiden älter als 14 ist, soll eine 20 erscheinen, ansonsten nichts. Formel:________________________________________________________________________ 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 19/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 21. Funktionen - Maximum - Minimum Als Beispiel verwenden wir die Max() bzw. Min() Funktion. Diese Funktion sucht das Maximum bzw. Minimum aus einer Anzahl von Zahlenwerten. Zuerst wird die Zelle ausgewählt, in die das Maximum bzw. Minimum geschrieben werden soll. Dann gibt man in die Funktionsleiste das Zeichen "=MAX(....)" ein. Damit startet man den Funktionsvorgang. Nun wählt man den gewünschten Zahlenbereich aus und beendet die Eingabe mit der ")"-Taste. Drückt man die "ENTER-Taste", dann wird das Maximum bzw. Minimum aus dem Zahlenbereich angegeben. Wichtig dabei: Ändert man nachträglich den Zellinhalt, dann wird auch der Ausgabewert geändert. MIN Berechnet die Zahl, die die kleinste der als Argumente übergebenen Zahlen ist. Syntax: MIN(Zahl 1; Zahl 2; ...Zahl 30) Zahl 1; Zahl 2;...Zahl 30 sind numerische Argumente, deren kleinste Zahl bestimmt werden soll. Jede Zahl kann auch durch einen Bezug ersetzt werden. MAX Berechnet den größten Wert innerhalb einer Argumentliste. Syntax: MAX(Zahl 1; Zahl 2; ...Zahl 30) Zahl 1; Zahl 2;...Zahl 30 sind numerische Argumente, deren größte Zahl bestimmt werden soll. Jede Zahl kann auch durch einen Bezug ersetzt werden. Beispiel: =MIN(A1:B100) gibt den kleinsten Wert aus dieser Liste zurück. =MAX(A1:B100) gibt den größten Wert aus dieser Liste zurück. =MAX(A1;A2;A3;50;100;200) gibt den größten Wert aus dieser Liste zurück. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 20/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 22. Markieren getrennter Zellen ➔ Markiere die ersten Zellen bzw. die erste Zelle. ➔ Drücken die STRG-Taste und lasse sie nicht mehr los. ➔ Markiere die nächsten Zellen. ➔ Immer schön die STRG-Taste festhalten. 23. Aufgabe 5: Blumencenter 1. Erstelle diese Tabelle und speichere sie unter dem Namen Blumencenter ab. 2. Verbinde die Zellen von B1 bis G1. 3. Verschiebe mit der MAUS die Spaltenköpfe so, dass deine Tabelle wie die Vorlage aussieht. 4. Formatiere die Zellen B3 bis G3 mit einem Zeilenumbruch. 5. Berechne die Zellen E4 und F4 über eine Formel. 6. Berechne die Zelle G4 über eine WENN-Formel. Bedingung: Wenn der Gewinnanteil am Umsatz in % größer als 10 ist, soll „Gut“, ansonsten „Schlecht“ erscheinen. Formel:__________________________________________________________________ 7. Kopiere anschließend die Formel in die restlichen freien Zellen. 8. Errechne die Zellen D12 bis D14 über Formeln mit einer Funktion. 9. Formatiere die Zellen E4 bis E10 und D12 bis D14 entsprechend den Zellen C4 bis D10. 10. Formatiere die Zellen F4 bis f10 über ein benutzerdefiniertes Format. Es sollen zwei Dezimalstellen und das Prozentzeichen erscheinen. 11. Erst nachdem alle Formeln errechnet und kopiert wurden, werden am Ende die Linien gesetzt. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 21/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 24. Aufgabe 6: Miete 1. Erstelle diese Tabelle und speichere sie unter dem Namen Miete ab. 2. Verbinde die Zellen von B2 bis F2. 3. Berechne die Zelle D5 über eine Formel. 4. Berechne die Zellen E5 und F5 über eine WENN-Formel. Bedingung für E5: Wenn der Preis pro qm mehr als 10,5 beträgt, soll „O.K.“, ansonsten „Zu billig!“ erscheinen. Formel:__________________________________________________________________ Bedingung für F5: Wenn in der Zelle Bewertung „Zu billig!“ erscheint, soll der um 10% erhöhte Mietpreis, ansonsten der alte Mietpreis erscheinen. Formel:__________________________________________________________________ 5. Kopiere anschließend die Formeln in die restlichen freien Zellen. Calc bietet noch weitere Funktionen, mit denen Sie Daten auf der Grundlage einer Bedingung analysieren kannst. Mit ZÄHLENWENN kannst Sie die Anzahl bestimmter Werte innerhalb eines Zellbereichs ermitteln, wenn bestimmte Bedingungen erfüllt werden. Formelaufbau:=ZÄHLENWENN(Bereich;“Kriterien“). Wichtig: Die Kriterien müssen immer in Anführungszeichen gesetzt werden. Beispiel für Aufgabe 6: Zähle die Anzahl der Zellen, in denen ein kleinerer Mietwert als 400 enthalten ist. Formel:___________________________________________________________________ SUMMEWENN dient dazu, die Summe eines Zellbereichs zu berechnen, wenn bestimmte Bedingungen erfüllt sind. Formelaufbau: =SUMMEWENN(Bereich;“Kriterien“). Beispiel für Aufgabe 6: Addiere die Werte der Zellen, in denen ein größerer Mietwert als 700 enthalten ist. Formel: _______________________________________________________________________ 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 22/27 BBS 1 Technik Kaiserslautern OpenOffice Schulung Kurs: Tabellenkalkulation Calc (OpenOffice.org) Datum: 25. Die verschachtelte WENN-Formel 1 A B C Name Größe in cm 2 Petra 150 3 Klaus 160 4 Judith 140 Mit einer einfachen WENN-Formel können Sie eine Zelle auf einen Wert hin überprüfen. Nun möchten Sie aber die Zelle B2 auf zwei Werte hin überprüfen. Mathematische Beschreibung: groesse> 180 160< groesse≤180 160≤groesse Groß Mittelgroß Klein Beispiel 1a: Wenn im Feld B2 eine Zahl steht, die größer ist als 180, soll „Groß“ erscheinen, wenn im Feld B2 eine Zahl steht, die größer ist als 160, dann soll „Mittelgroß“ erscheinen, ansonsten soll „Klein“ erscheinen. Formel: =WENN( B2>180;“Groß“; WENN(B2>160;“Mittelgroß“;“Klein“) ) Ganz wichtig ist die Reihenfolge der Zahlenwerte in der WENN-Formel. Benutzt man den Vergleichsoperator Größer (>), dann muss der größte Wert, mit dem Sie eine Zelle überprüfen wollen, in der ersten WENN-Formel stehen, der kleinste Wert kommt zum Schluss. ==> Variante 1: =WENN (Wahrheitswert; Dann-Wert; WENN(Wahrheitswert;Dann-Wert;Sonst-Wert) ) Vom Aufbau her bleibt die WENN-Formel gleich. Bei einer verschachtelten WENN-Formel kommt nur an die Stelle, an der Sie den Sonst-Wert eingeben, ein neues WENN (ohne „=“). Danach öffnen Sie eine neue Klammer, und dann folgt wie bei einer einfachen WENN-Formel wieder der Wahrheitswert. Wichtig ist, dass Sie am Ende zwei Klammern schließen müssen, da Sie auch zwei Klammern geöffnet haben. Benutzten Sie dagegen den Vergleichsoperator Kleiner (<), dann ist es genau umgekehrt: Jetzt kommt der kleinste Wert in den ersten Teil der WENN-Formel und der größte in den letzten Teil. Beispiel 1b: 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 23/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) Formel: OpenOffice Schulung Datum: =WENN( B2<=160;“Klein“; WENN(B2<=180;“Mittelgroß“;“Groß“) ) Achtung: Eine WENN-Formel kann auch mehr als einmal verschachtelt werden. Variante 2: =WENN (Wahrheitswert; WENN(Wahrheitswert;Dann-Wert;Sonst-Wert);Sonst-Wert) Bei diesem Aufbau kommt an die Stelle des Dann-Werts die neue WENN-Formel. Auch hier müssen Sie zwei Klammern schließen. Beispiel 2a: Formel: = WENN(B2<=180; WENN(B2<=160;“Klein“;“Mittelgroß“) ; “Groß“ ) Beispiel 2b: Formel: = WENN(B2>160; WENN(B2>180;“Groß“;“Mittelgroß“) ; 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 24/27 “Klein“ ) BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 26. Aufgabe 7: Promille 1. Erstelle diese Tabelle und speichere sie unter dem Namen Promille ab. 2. Verbinde die Zellen von B2 bis E2. 3. Verschiebe mit der Maus die Spaltenköpfe so, dass deine Tabelle wie die Vorlage aussieht. 4. Berechne die Zelle D6 und E6 über eine WENN-Funktion. Bedingung für D6: Wenn der Alkoholgehalt bei mindestens 0,5 Promille liegt, soll eine 1, ansonsten eine 0 erscheinen. Formel:__________________________________________________________________ Bedingung für E6: Bei einem Alkoholgehalt von 1,2 und mehr müssen 1200 € Geldstrafe bezahlt werden, bei einem Alkoholgehalt von 0,5 Promille und mehr sollen 680 € Geldstrafe gezahlt werden, ansonsten braucht man nichts zu bezahlen. Formel:__________________________________________________________________ 5. Kopiere anschließend die Formeln in die restlichen freien Zellen. 6. Formatiere die Zellen E6 bis E15 mit dem 1000er-Trennzeichen ohne Dezimalstellen, aber mit dem Euro-Zeichen. 7. Erst nachdem alle Formeln errechnet und kopiert wurden, werden am Ende die Linien gesetzt. 8. Ermitteln Sie in der Zelle D18 die Anzahl der Promille Fahrer. 9. Ermitteln Sie in der Zelle D19 die Summe aller Promillewerte über 0,5. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 25/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) OpenOffice Schulung Datum: 27. Aufgabe 8: Zuschüsse 1. Erstelle diese Tabelle und speichere sie unter dem Namen Zuschüsse ab. 2. Verbinde die Zellen von B2 bis I2. 3. Verschiebe mit der Maus die Spaltenköpfe so, dass deine Tabelle wie die Vorlage aussieht. 4. Formatiere die Zellen B4 bis I4 mit einem Zeilenumbruch. 5. Berechne die Zellen F5 bis H5 über eine WENN-Funktion. Bedingung für F5: Wenn die Kinderzahl größer oder gleich 5 ist, bekommt man 15% Mietkostenzuschuss auf die Kaltmiete, ist die Kinderzahl größer oder gleich 3, bekommt man 10% Zuschuss auf die Kaltmiete, ansonsten bekommt man 5% Zuschuss auf die Kaltmiete. Formel:__________________________________________________________________ Bedingung für G5: Wenn die Kinderzahl größer als 5 ist, bekommt man 240 € pro Kind, ist die Kinderzahl größer als 2, bekommt man 129 € pro Kind, ansonsten bekommt man 70 € pro Kind. Formel:__________________________________________________________________ Bedingung für H5: Wenn eine Person allein erziehend ist, bekommt diese 60 € pro Kind, ansonsten bekommt sie nichts. Formel:__________________________________________________________________ 6. Berechne die Zelle I5 über eine Formel. 7. Kopiere anschließend die Formel in die restlichen freien Zellen. 8. Formatiere die Zellen F5 bis I13 entsprechend den Zellen E5 bis E13. 9. Erst nachdem alle Formeln errechnet und kopiert wurden, werden am Ende die Linien gesetzt. 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 26/27 BBS 1 Technik Kaiserslautern Kurs: Tabellenkalkulation Calc (OpenOffice.org) Anhang: Lösungen: Beispiele Rechenpriorität: 3*4/2=6, aber 3*2/4=1,5 bzw. 5*9+2=47, aber 5*(9+2)=55 4.12.2006 K.Kober 16.08.2011 U.Ruby Seite 27/27 OpenOffice Schulung Datum:
