Informationstechnik Übungsblatt 2 Excel PDF

Übungsblatt 2, Tabellenkalkulation Excel Kurs TEMB 2308 Lehrbriefe EXC16-1/2 Übersicht über die wichtigsten Funktionen in EXCEL 2010 1. Bearbeiten von Daten in einer Arbeitsmappe 1.1. Bearbeiten von Daten Durch Doppelklick auf eine Zelle kann der Inhalt direkt bearbeite...

Übungsblatt 2, Tabellenkalkulation Excel Kurs TEMB 2308 Lehrbriefe EXC16-1/2 Übersicht über die wichtigsten Funktionen in EXCEL 2010 1. Bearbeiten von Daten in einer Arbeitsmappe 1.1. Bearbeiten von Daten Durch Doppelklick auf eine Zelle kann der Inhalt direkt bearbeitet werden, oder die Zelle wird aktiviert und in der Bearbeitungsleiste der Inhalt geändert (links unten wird bereit oder bearbeiten angezeigt). 1.2. Einheitliches Formatieren von Zellen und Entfernen der Datenformatierung Zellen auswählen und über Registerkarte Start Formatierungsbefehle auswählen oder auch wieder entfernen. 1.3. Suchen von Daten In einer umfangreichen Tabelle ist es oft schwierig bestimmte Daten zu finden. Dies geht leichter über die Registerkarte Start und die Befehlsgruppe Bearbeiten (z.B. Suchen und Ersetzen). 2. Formeln und Funktionsbibliothek 2.1. Einfache Formeln Formeln werden immer mit einem „ = “ eingeleitet. Es können dabei die üblichen Rechenzeichen und Klammern verwendet werden. Formeln können sich auf mehrere Zellen beziehen, statt absoluter Zahlen werden dann Zelladressen verwendet. 2.2. Vordefinierte Formeln Excel stellt über die Funktionsbibliothek (Registerkarte Start und Bearbeitung) eine große Anzahl von vordefinierten Funktionen zur Verfügung. Die Schreibweise der Funktionen ist dabei immer gleich: FUNKTION(Bereich) z.B. SUMME(A6:A11). Bei manchen Funktionen können auch Bedingungen angegeben werden. Bei Eingabe eines = und eines Anfangsbuchstabens stellt Excel eine Liste von Funktionen zur Verfügung. 2.3. Absolute und relative Bezüge Beim Kopieren wird eine Zelladresse geändert, z.B. wird aus C7 die Adresse C9 , wenn man die Formel um zwei Zellen nach rechts verschiebt, C7 ist eine relative Adresse. Eine absolute Adresse wird daraus durch Verwendung des Dollarzeichens: $C$7. Der Adresswert nach dem Dollarzeichen ändert sich beim Kopieren oder Verschieben nicht. Relative und absolute Zellbezüge können auch gemischt werden, möglich sind also auch Schreibweisen wie: C7 + $B$5 ; $C7 (nur C bleibt konstant) bzw. C$7 (nur 7 bleibt). 2.4. Formeln manuell korrigieren oder löschen Entweder durch Aktivieren der Zelle und direkte Eingabe oder über die Registerkarte Formeln und den Befehl Funktion einfügen. 2.5. Suchen von Fehlern in einer Formel Fehler in einer Formel werden angezeigt über Fehlercodes: ##### (Spalte zu schmal); ##BEZUG! (ungültiger Zellbezug, z.B. fehlende Zellinhalte); #NAME? (falscher Funktionsname). Auf der Registerkarte Formeln finden Sie in der Gruppe Formelüberwachung eine Vielzahl von Funktionen wie z.B. Spur zum Vorgänger / Spur zum Nachfolger. Über Formeln anzeigen können statt der Ergebnisse nur die Formeln angezeigt werden. Über die Formelauswertung wird die Berechnung Schritt für Schritt angezeigt und es steht auch ein Überwachungsfenster für Formeln zur Verfügung. Informationstechnik Übungsblatt 2 Seite 1 von 6 3. Ändern der Darstellungsweise 3.1. Formatieren von Zellen Zellbereich markieren und Zellen formatieren auswählen (rechte Maustaste). Excel bietet auch einen Katalog an Vorlagen an (Registerkarte Start und Gruppe Formatvorlage). 3.2. Formate übertragen Sollen unabhängige Zellen oder Zellbereiche einheitlich formatiert werden, gelingt dies mit dem Befehl Format übertragen auf der Registerkarte Start. 3.3. Designvorlagen Über die Registerkarte Seitenlayout und den Befehl Designs stellt Excel Vorlagen für die gesamte Arbeitsmappe zur Verfügung. 3.4. Tabellenvorlagen Über die Registerkarte Start und die Gruppe Formatvorlagen und den Befehl Als Tabelle formatieren stehen auch Tabellenvorlagen zur Verfügung. 3.5. Hintergrundbilder lassen sich über die Registerkarte Seitenlayout einfügen. 4. Verbessern der Lesbarkeit einer Arbeitsmappe 4.1. Formatierung von nummerischen Datentypen Rechte Maustaste und Befehl Zellen formatieren. Es kann Schriftart und –größe oder auch die Darstellung als Prozentzahlen oder in einer Währung ausgewählt werden, ebenso kann die Zahl der Kommastellen festgelegt werden. 4.2. Bedingte Formatierung Über die Registerkarte und Start und den Befehl Bedingte Formatierung kann die Darstellung von Zellinhalten von Bedingungen abhängig gemacht werden, z.B. durch eine andere Hintergrundfarbe, wenn ein bestimmter Wert über- oder unterschritten wird. 4.3. Bilder und Grafiken einfügen Über die Registerkarte Einfügen und die Gruppe Illustrationen kann ein Arbeitsblatt attraktiver und informativer gemacht werden. 5. Drucken von Arbeitsblättern 5.1. Arbeitsblätter einrichten Über die Registerkarte Seitenlayout kann das Druckformat des Arbeitsblattes festgelegt werden, wie z.B. Seitenränder, Ausrichtung (Hoch- oder Querformat), Größe, Druckbereich, Umbrüche, Hintergrund, Drucktitel). 5.2. Kopf- und Fußzeilen Über die Registerkarte Seitenlayout und Seite einrichten, können in Kopf- oder Fußzeile sinnvolle Informationen eingefügt werden, wie z.B. in der Kopfzeile persönliche Informationen und in der Fußzeile z.B. Datum, Uhrzeit oder auch Seitenzahl. 5.3. Seitenumbruchvorschau Über die Registerkarte Ansicht und den Befehl Umbruchvorschau kann die Form des Arbeitsblattes angezeigt und auch geändert werden. Gedruckt werden kann das Arbeitsblatt dann über die Registerkarte Datei und den Befehl Drucken, wobei rechts genau der Ausdruck dargestellt wird. Informationstechnik Übungsblatt 2 Seite 2 von 6 6. Filtern von Daten 6.1. Filtern Über die Registerkarte Daten und den Befehl Filtern können nur die Daten ausgewählt werden, die für eine bestimmte Betrachtung wichtig sind, die anderen Daten können ausgeblendet werden. Dabei kann die Autofilterfunktion oder auch ein benutzerdefinierter Filter verwendet werden. 6.2. Autofilter Über die Registerkarte Start und die Befehlsgruppe Bearbeiten den Befehl Sortieren und Filtern und darin den Befehl Filtern auswählen. In den Spaltenüberschriften erscheint ein Pfeil, mit einem Klick darauf öffnet sich das Menü für Filtern und Sortieren. Es kann dabei nach Werten, Formaten und Kriterien gefiltert werden, aber in einer Spalte immer nur nach einem Filter. Nach Werten und Format gleichzeitig ist nicht möglich. Über benutzerdefinierter Filter können auch UND- bzw. ODER-Verknüpfungen verwendet werden. 7. Neuordnen von Daten 7.1. Einer Tabelle Berechnungsfunktionen zuweisen Über die Registerkarte Daten und den Befehl Sortieren können Sie Datentabellen in eine logische Reihenfolge bringen, aufsteigend oder absteigend. Es kann dabei nach Werten, Schriftfarbe, Zellfarbe und Zellsymbol und in mehreren Ebenen sortiert werden. 7.2. Gruppierung Über die Registerkarte Daten, die Befehlsgruppe Gliederung und den Befehl Gruppieren können Daten einer Tabelle nach bestimmten Kriterien gruppiert und auch Teilergebnisse dargestellt werden. 8. Überprüfen von Daten 8.1. Datenüberprüfung bei der Eingabe Über die Registerkarte Daten, die Befehlsgruppe Datentools und den Befehl Datenüberprüfung können für einen Bereich oder das ganze Arbeitsblatt Kriterien für die gültige Eingabe von Daten festgelegt werden. Zusätzlich kann auch eine Eingabemeldung und eine Fehlermeldung definiert werden. 8.2. Datenüberprüfung bei bereits vorhandenen Daten Vorgegangen wird wie bei 8.1., als Ergebnis erhält man keine Fehlermeldung. Es werden lediglich fehlerhafte Werte durch einen roten Kreis gekennzeichnet. 8.3. Finden von Zellen mit Gültigkeitskriterien Über die Registerkarte Start, die Befehlsgruppe Bearbeiten und den Befehl Suchen und Auswählen und Datenüberprüfung können Sie nach Zellen mit Kriterien suchen und diese Einstellungen auch löschen. 9. Erstellen und Anpassen von Diagrammen 9.1. Mit Diagrammen visualisieren Dafür steht eine Vielzahl von Diagrammtypen zur Verfügung: Säulen-, Balken-, Kreis- und Punktdiagramme. 9.2. Einfügen eines Diagramms Wertebereich einschließlich Spalten- und Zeilenüberschriften markieren, Registerkarte Einfügen und in der Gruppe Diagramme die gewünschte Form auswählen. Danach wird sofort eine Ansicht des Diagramms erstellt. Das Diagramm kann jetzt direkt verändert werden, z.B. die Größe und ein Titel eingefügt werden. Informationstechnik Übungsblatt 2 Seite 3 von 6 9.3. Diagrammtools Nachdem ein Diagramm eingefügt wurde, stehen im Menüband die Diagrammtools zur Verfügung mit den Registerblättern Typ, Daten, Diagrammlayouts und Diagrammformatvorlagen. Sie können jetzt jederzeit den Diagrammtyp wechseln, eine andere Farbgestaltung über die Formatvorlagen wählen oder auch das Diagramm verschieben. Durch Auswählen einzelner Diagrammbestandteile (z.B. Achsen) und Drücken der rechten Maustaste können alle Einzelheiten des Diagramms festgelegt werden. 10. Aufgabe Suchen Sie im Internet die Ergebnisse der Bundestagswahl 2021 mit allen Zahlen, auch im Vergleich zur vorangegangenen Wahl. a) Stellen Sie Verluste und Gewinne in einer Tabelle und in einem Balkendia- gramm dar. Die Balken sollten dabei jeweils in der zugehörigen Parteifarbe gefärbt sein. b) Geben Sie in einem Kreis- oder Tortendiagramm die aktuelle Sitzverteilung im Bundestag dar. c) Ergänzen Sie in den beiden Diagrammen Beschriftungen und Legenden. Aufgaben: Bearbeiten Sie die folgenden Excelblätter und fangen Sie mit den einfachen an. Alter berechnen.xlsx Altersklassen.xlsx Arbeitszeit.xlsx bedingte_formatierung_formel.xlsx benutzerdefinierte_zellformate.xlsx Diagramme.xlsx einfache_formeln_1_vorlage.xlsx einfache_formeln_2.xlsx einfache_wenn-funktion_1.xlsx einfache_wenn-funktion_2.xlsx einfache_wenn-funktion_3.xlsx einfache_wenn-funktion_4.xlsx Formate.xlsx Gewinnbeteiligung.xlsx grundrechenarten_1.xlsx grundrechenarten_2.xlsx grundrechenarten_3.xlsx Kopf- und Fußzeilen.xlsx Lagerhalle.docx Lauf.xlsx Rechnung.xlsx sverweis_1.xlsx sverweis_2.xlsx Umsatzbeteiligung.xlsx Verein - Beiträge.xlsx Verein - Zahlen.xlsx Zeitberechnung.xlsx Zellen.xlsx zellformatierung.xlsx Informationstechnik Übungsblatt 2 Seite 4 von 6 Vordefinierte Funktionen in Excel 1. Funktionen auf einen Bereich oder eine einzelne Zelle bezogen 1.1. Summe: SUMME(Bereichsanfang : Bereichsende) Beispiele: SUMME(B3:B10) ; SUMME(B3:E11) 1.2. bedingte Summe: SUMMEWENN(Bereichsanfang : Bereichsende; “Bedingung“) Beispiel: SUMMEWENN(B3:B10;“>10000“) ; SUMMEWENN(B3:E11;“=10“) 1.3. Mittelwert: MITTELWERT(Bereichsanfang : Bereichsende) Beispiele: MITTELWERT(B3:B10) ; MITTELWERT(B3:E11) 1.4. Maximum: MAX(Bereichsanfang : Bereichsende) Beispiele: MAX(B3:B10) ; MAX(B3:E11) 1.5. Minimum: MIN(Bereichsanfang : Bereichsende) Beispiele: MIN(B3:B10) ; MIN(B3:E11) 1.6. Anzahl: ANZAHL/ANZAHL2(Bereichsanfang : Bereichsende) Beispiele: ANZAHL(B3:B10) ; ANZAHL2(B3:E11) Hinweis: gibt die Anzahl der Zahlen/nicht leeren Zellen in diesem Bereich an 1.7. bedingtes Zählen: ZÄHLENWENN(Bereichsanfang : Bereichsende; ‘‘Bedingung‘‘ Beispiele: ZÄHLENWENN(B3:B10;“>10000“) ZÄHLENWENN(B3:C13;“A“) 1.8. Runden: RUNDEN(Wert; Stellenzahl) Beispiele: RUNDEN(B3;2) ; RUNDEN(3,5678; 1)  3,6 1.9. Formatierte Ausgabe definieren: ##.##0,0000 Die Zahl wird mit Tausenderpunkt und vier Nachkommastellen ausgegeben: 52324,25  52.324,2500 2. Logische Funktionen 2.1. UND: UND(Bedingung1;Bedingung2) liefert als Ergebnis „WAHR“, wenn beide Bedingungen erfüllt sind, ansonsten „FALSCH“ Beispiele: UND(5>3;2>1)  „WAHR“ ; UND(5>3;1>2)  „FALSCH“ 2.2. ODER: ODER(Bedingung1;Bedingung2) liefert als Ergebnis „WAHR“, wenn mindestens eine Bedingung erfüllt ist, ansonsten „FALSCH“ Beispiele: ODER(51)  „WAHR“ ; ODER(52)  „FALSCH“ 2.3. NICHT: NICHT(Wahrheitswert) kehrt den Wahrheitswert um Beispiele: NICHT(WAHR)  FALSCH ; NICHT(0)  1 [1 = WAHR] 2.4. WENN: WENN(Bedingung;DANN-Anweisung;SONST-Anweisung) Beispiele: WENN(A3>0;A3;A3) ergibt einen immer positiven Wert WENN(UND(A3>=1;A3