Inst. f¨ ur Angew. Informationsverarbeitung Prof. Dr. Franz Schweiggert Michaela Weiss M¨arz 2015 Lerneinheit 1 Brush up your Bachelor – Informatik Lerneinheit 1: Fortgeschrittene Themen der Tabellenkalkulation Aufgabe 1: Excel-Theorie a) Wie kann man in Excel einen Bereich referenzieren? b) Welche drei unterschiedlichen Arten von Zellreferenzierungen gibt es in Excel und wie unterscheiden sich diese? Geben Sie jeweils die entsprechende Referenzierung der Zelle A1 an. Aufgabe 2: Lebenshaltungskosten Ziel dieser Aufgabe ist es, mit einfachen Excelmitteln eine Analyse der Lebenshaltungskosten vorzunehmen (siehe Abbildung). Die Daten liegen in Form einer csv-Datei vor. a) Laden Sie die Datei lebenshaltung.txt (Windows ANSI Kodierung) von der Homepage herunter. Diese beinhaltet Ihre“ Lebenshaltungskosten ab Januar 2012 bis heute in monatlicher ” Form. Importieren Sie die Datei (ohne die 1. Spalte!) in ein Exceltabellenblatt, so ¨ dass Excel den Import automatisch anpasst, wenn Anderungen an der Originaldatei vorgenommen werden. Der Import soll erst in Zelle A4 beginnen. F¨ ugen Sie entsprechend der ¨ Abbildung eine Uberschrift hinzu. ¨ b) Andern Sie das Zellenformat f¨ ur die Eintragungen der Miete, Nebenkosten, Fahrtkosten und alle Kosten f¨ ur Essen, Party und Sonstiges auf W¨ahrung“. ” c) Berechnen Sie in Spalte I die monatlichen Gesamtkosten f¨ ur jeden aufgef¨ uhrten Monat. d) Erstellen Sie in Spalte J jeweils eine verbundene Zelle“ f¨ ur alle Zeilen, die zu einem Jahr ” geh¨oren. Nehmen Sie hierzu im Jahr 2014 die noch leeren Zeilen f¨ ur die sp¨ateren Eintragungen von M¨arz bis Dezember hinzu. Berechnen Sie in den verbundenen Zellen jeweils die Jahresgesamtkosten. e) Erstellen Sie die monatlichen Durchschnittskosten f¨ ur Miete, Nebenkosten, Fahrtkosten, Essen, Urlaub, Party, Sonstiges und f¨ ur die monatlichen Gesamtkosten. Diese Angaben sollen oberhalb der jeweiligen Spalte abgespeichert werden. Damit eine sp¨atere Aktualisierung des Dateiimports m¨oglich ist, soll die Berechnung so dynamisch erfolgen, dass automatisch die korrekte Anzahl als Teiler verwendet wird. f) Erstellen Sie in Spalte K wiederum verbundene Zellen f¨ ur jedes Jahr und berechnen Sie hier die prozentuale Erh¨ ohung der Jahresgesamtkosten im Vergleich zu 2012. Die Werte sollen als Prozentzahlen mit zwei Nachkommastellen dargestellt werden. ¨ g) Offnen Sie die Inputdatei mit Hilfe eines Editors und f¨ ugen Sie eine Zeile (mit entsprechenden ¨ Eintr¨agen) f¨ ur M¨ arz 2015 hinzu. Speichern Sie diese Anderung und aktualisieren Sie Ihre Excelanalyse unter Verwendung der Updatefunktion. Beachten Sie hierbei, dass je nach Wahl der Importoptionen ggf. bei den Berechnungen in der neuen Zeile sowie der letzten alten ¨ Zeile Anderungen n¨otig sein k¨onnen. Gewu osung: ¨ nschtes Endaussehen der L¨ Aufgabe 3: Tante Emma Laden a.) Importieren Sie u ¨ber den Web-Import in Ihre Excel-Arbeitsmappe die Preis- bzw. AbsatzTabellen von der Internetseite www.mathematik.uni-ulm.de/sai/glis14. Verwenden Sie f¨ ur jede Tabelle ein eigenes Tabellenblatt und beschriften Sie dieses dementsprechend. b.) Berechnen Sie f¨ ur das 1. Quartal den Umsatz sowie den Anteil am Gesamtumsatz jedes Produktes und verwenden Sie die passende Zellenformatierung (W¨ahrung bzw. Prozent). Berechnen Sie zudem den Gesamtumsatz. Hinweis: Sie k¨onnen davon ausgehen, dass die Preisliste auf der Webseite immer alphabetisch aufsteigend sortiert ist. Beachten Sie allerdings, dass im Laufe der Zeit neue Produkte in das Produktportfolio aufgenommen werden k¨onnen. Daher m¨ ussen Sie den Preis eines Produktes aus der Preistabelle mittels des Produktnamens und nicht u ¨ber eine statische Zellenreferenz ermitteln. c.) Die angegebenen Produktnamen enthalten jeweils ein Anfangsk¨ urzel, das dabei hilft, jedes Produkt zu der richtigen Produktgruppe zuzuordnen. Erstellen Sie im Tabellenblatt des 1. Quartals eine Spalte, die fu ¨ r jedes Produkt die passende Produktgruppe ausschreibt. Hinweis: Die Produktkategorien sollen NICHT manuell eingetragen werden, sondern automatisch von Excel anhand des Pr¨afixes erkannt werden. Zur Abspaltung der Anfangszeichen kann die Funktion LINKS(...) verwendet werden, deren Funktionsweise mit Hilfe des Formelassistenten oder anhand eines Beispiels erschlossen werden kann. d.) Bestimmen Sie mit Hilfe der Einteilung in Warengruppen aus Teilaufgabe c, f¨ ur das 1. Quartal automatisch den Umsatz jeder der 3 Produktgruppen. Berechnen Sie zus¨atzlich die Anteile dieser Produktgruppen am Gesamtumsatz (Formatvorgabe: W¨ahrung bzw. Prozent). e.) Finden Sie f¨ ur jedes Quartal automatisch die Verkaufsmenge und den Namen des ProduktBestseller (gr¨oßte verkaufte Menge) heraus. Diese Analyse soll automatisch erfolgen und sich dynamisch anpassen, falls die Verkaufsmengen ge¨andert werden. Hinweis: 1. Sie k¨onnen davon ausgehen, dass es immer ein Produkt mit der maximalen Verkaufsmenge gibt und nicht mehrere Produkte mit derselben maximalen Verkaufsmenge. 2. Ben¨otigt die automatische Analyse die Sortierung der Daten, so ist dieser manuelle Eingriff (nach jeder Aktualisierung der Daten) zul¨assig. 3. Die Identifizierung des Bestsellers soll jedoch nicht mittels einer Sortierung der Daten und manuellem oder automatischen Nachschlagen in der letzten/ersten Zeile erfolgen! f.) Finden Sie f¨ ur jedes Quartal die Verkaufsmenge und den Namen des Produkts mit der geringsten Verkaufsmenge heraus. Hierbei sollen Jahreszeiten-bedingte Verkaufsmengen von 0 St¨ uck NICHT mit einbezogen werden. So ist beispielsweise im 1. Quartal 2013 nicht O Erdbeeren, sondern G Sellerie die richtige Antwort. Diese Analyse soll wieder komplett automatisch erfolgen. Es gelten analog die Hinweise aus Teilaufgabe d. g.) Passen Sie die Rahmen, die Schrifthervorhebungen und farblichen Hinterlegungen gem¨aß des nachfolgenden Screenshots an.