Archive for the ‘Excel’ Category

h1

Pie charts and Spreadsheets

Oktober 29, 2011

Wer von Euch hat schon mal intensiv darüber nachgedacht, wo die Begriffe Pie Chart und Spreadsheet eigentlich herkommen? Es ist wie mit vielen Begriffen. Sie gehören heutzutage zu unserem Alltag und wir verwenden sie, ohne über die genaue Bedeutung nachzudenken. Wenn das viele Menschen tun und die einzelnen Individuen dabei verschiedene Vorstellungen über die Bedeutung der Begriffe haben, kann das schon mal zu Problemen führen. besonders wenn das im Dialog zweier Menschen geschieht. Das Resultat können dann Kommunikationsprobleme sein.

Beim Pie Chart, zu Deutsch Kuchendiagramm hatte ich ja schon so eine Assoziation.

Wollt Ihr genaueres wissen und interessiert Euch zudem noch worin die Verbindung beider Begriffe mit Erdnussbutter besteht, dann schaut Euch das folgende Video an.

Pie charts and Spreadsheets

Das HTC Titan hat übrigens einen angenehm grossen Bildschirm und eine Kamera, die derjenigen des iPhone 4S angeblich kaum nachsteht.

Advertisements
h1

Gültigkeitsprüfung in Excel mit dynamischer Liste

August 23, 2011

Heute kam im Excel Kurs das Thema der Gültigkeitsprüfung bei der Eingabe von Daten in ein Tabellenblatt.

Diese kann z.B. bei der Eingabe von Produkten in der Spalte B des nachstehenden Beispiels auf einer gegebenen Liste von Produkten (Spalte D) basieren.

image

Nun kann es wünschbar sein, dass die Liste der zugelassenen Produkte (Spalte D) erweiterbar sein soll. Wird ein Produkt in der Liste eingefügt oder am Ende der Liste angefügt, soll es automatisch bei der Gültigkeitsprüfung berücksichtigt werden und in der Auswahlliste erscheinen.

Die dynamische Produktliste basiert auf einem dynamischen Namen, welcher mithilfe der Excel-Funktion

=BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite)

festgelegt werden kann.

Vorgehen zur Lösung:

  1. Auswählen des ersten Eintrages der Produkteliste (D3) und festlegen des Namens “Start” durch Eingabe im Namenfeld.
  2. Definition eines neuen Namens “Produkte” im Namens-Manager (Register Formeln, Gruppe Definierte Namen, Namens-Manager), mit folgendem Bezug (Eingabe der Formel ins Feld “Bezieht sich auf”):
    =Start:BEREICH.VERSCHIEBEN(Start;ANZAHL2($D$3:$D$100)-1;0;1;1)

    image

  3. Auswahl des Bereichs für die Eingaben (im Beispiel B3:B10) und Definition der Gültigkeitsprüfung (Register Daten, Gruppe Datentools, Datenüberprüfung). Zulassen: Liste. Quelle: =Produkte.
    Zellendropdown aktivieren.

    image

Werden nun im vorgesehenen Produktebereich (D3:D100) weitere Produkte eingetragen, werden diese verwendet. Die Liste muss aber lückenlos, d.h. ohne Leerzeilen sein.

Die Beispielarbeitsmappe kann hier heruntergeladen werden:

h1

Excel für Läufer und andere Sportler

Juni 14, 2011

Excel kann sehr gut mit Datum und Uhrzeit umgehen und damit lassen sich sowohl Berechnungen anstellen, als auch Daten und Zeiten sortieren. Läufer wollen es aber ganz genau wissen. Auf die Zehntelsekunde genau sollten die Zeiten schon sein.

Das wäre für Excel kein Problem, nur leider gelingt es auf einem PC in der Schweiz nicht, die Zeiten genau einzugeben. Bis zur Sekunde ist alles ok. Sobald aber versucht wird, Zehntel- oder gar Hundertstelsekunden einzugeben, interpretiert Excel die Eingabe nicht mehr als numerisch, sondern als Text und damit sind die Möglichkeiten zum Rechnen, Sortieren, etc. verbaut.

Bei Schweizer Einstellungen müsste die Eingabe für die Zeit z.B. lauten:

1:18:05.8 (meine Zeit vom Flughafenlauf 2011)

Würde man den PC in den Ländereinstellungen von Windows auf Deutsch (Deutschland) einstellen, würde alles gehen. Man müsste lediglich die Daten dem Deutschen Format entsprechend eingeben. Sind die Daten erfasst, könnte man wieder zurück auf Schweiz (Deutsch) stellen. Allerdings ist diese Vorgehensweise nicht zu empfehlen, weil dadurch die Formate für das gesamte System betroffen sind.

Eine, wenn auch mühsame Möglichkeit, wäre das Umwandeln des Textes, welcher die genaue Zeit enthält, mittels Text- und Zeitfunktionen.

Die Funktion ZEITWERT(Zeit) wandelt die Zeichenkette Zeit in den entsprechenden Zeitwert um. Leider liefert auch diese Funktion bei der Eingabe von Sekundenbruchteilen zu einem Fehler (#WERT!).

Zum Ziel führt schliesslich die Formel:

=ZEITWERT(LINKS(J16;FINDEN(".";J16)-1))+WERT(TEIL(J16;FINDEN(".";J16)+1;1))/864000

wobei in J16 die Zeit (1:18:05.8) steht, welche danach als korrekter Zeitwert ausgegeben wird (0.0542337962962963). Dieser muss dann nur noch mit einem passenden Zahlenformat für die Zelle formatiert werden, z.B.

h:mm:ss.0

Statt den ganzen Text mühevoll in eine Zeit umzuwandeln, wäre es einfacher, die Zeit auf die Sekunde genau einzugeben und die Zehntel- oder Hundertstelsekunden in eine zusätzliche Zelle zu schreiben. Diese können dann durch 864’000, bzw. 8’640’000 geteilt und zur Zeit hinzugezählt werden. Auch möglich ist es, eine Sekunde  (ZEIT(0;0;1) durch 10 zu teilen und mit der Anzahl Zehntelsekunden zu multiplizieren:

A1: 1:18:05

A2: 8

A3: A1+ZEIT(0;0;1)/10*8, ergibt 1:18:05.8 als Zeit dargestellt.

Theorie dahinter:

Excel speichert Datum und Uhrzeit als numerischen Wert. das Datum 1.1.1900 wird als Zahl 1 gespeichert, der 2.1.1900 als 2, etc. Ein ganzer Tag entspricht also der ganzen Zahl 1. Das erste gültige Datum ist der 1.1.1900. Die Anzahl Tage zwischen zwei Daten errechnet sich somit sehr einfach als Differenz der beiden Datumswerte.

Die Uhrzeit wiederum wird als Bruchteil gespeichert:

Der Tag hat 24 Stunden. 1 Stunde ist somit 1/24 (0.0416666666666667).

1 Minute entspricht 1/1440 (24*60)

1 Sekunde entspricht 1/86’400 (24*60*60)

1 Zehntelsekunde entspricht 1/864’000

1 Hundertstelsekunden wird somit als 1/8’640’000 gespeichert

Anmerkung (auch für Excel Anwender die nicht laufen):

Durch die Differenz zweier Uhrzeiten lässt sich einfach die Zeitdauer berechnen. Z.B. ergibt die Rechnung 12:00-7:30 die korrekte Arbeitszeit von 4:30. Möchte man nun diese Stundenzahl mit einem Stundenlohn von z.B. 50.00 multiplizieren, erhält man als Ergebnis Fr. 9.375. Etwas ernüchtern. Die Kontrolle der Lohntüte lohnt sich in diesem Fall.

Man sollte daran Denken, dass 1 Stunde in Excel eben nicht als 1, sondern als 1/24 dargestellt wird. Somit muss das Resultat von Anzahl_Stunden * Stundenansatz noch mit 24 multipliziert werden, was dann einen korrekten Lohn von Fr. 225.00 ergibt.

h1

Excel Kassabuch

Mai 30, 2011

Die hier beschriebene Excel Arbeitsmappe Kassabuch zeigt die Anwendung einiger Excel Funktionen.

image

Der aktuelle Saldo des jeweiligen Monats wird jeweils berechnet indem der zum Anfangssaldo des Monats alle Eingänge addiert und alle Ausgänge subtrahiert werden:

=F2+SUMME(D6:D1000)-SUMME(E6:E1000)

In der Spalte Saldo wird der Saldo nur berechnet, wenn nicht auf der Zeile ein Betrag in der Spalte Eingang oder Ausgang oder in beiden steht. Wichtig ist hier der Einsatz der absoluten Bezüge, damit jeweils alle Ein-/Ausgänge von der ersten bis zur aktuellen Zeile berücksichtigt werden.

=WENN(UND(ISTLEER(D6);ISTLEER(E6));"";$F$2+SUMME($D$6:D6)-
    SUMME($E$6:E6))

Der Anfangssaldo im Januar wird in der Zelle F2 eingegeben. Der Anfangs-saldo jedes weiteren Monats entspricht jeweils dem Saldo des Vormonats. Z.B. wird der Anfangssaldo vom Monat Februar mit folgender Formel über-nommen:

=Januar!F3

Um in der Zelle C3 nicht den Namen des Monats manuell eingeben zu müssen, wird der Name des Blattregisters mithilfe einiger verschachtelter Funktionen übernommen:

=TEIL(ZELLE("dateiname";$A$1);FINDEN("]";ZELLE("dateiname";$A$1))+1;
    LÄNGE(ZELLE("dateiname";$A$1))-FINDEN("]";ZELLE("dateiname";$A$1)))

Die Funktion ZELLE(“dateiname”;$A$1) liefert den Blattnamen inklusive dem gesamten Dateipfad.

Die Funktion LÄNGE(ZELLE(“dateiname”;$A$1) liefert die Länge der gesamten Zeichenkette.

Mit der Funktion FINDEN("]";ZELLE("dateiname";$A$1)) wird die Position der schliessenden eckigen Klammer (“]”) gefunden. An der nächsten Zeichenposition der Zeichenkette beginnt der Blattname.

Die Funktion Teil(Text; Erstes_Zeichen; Anzahl_Zeichen) extrahiert aus der Zeichenkette den Namen des Blattes.

Die Arbeitsmappe kann hier für weitere Versuche heruntergeladen werden:

h1

Korrekte Fünferrundung mit Excel

Mai 3, 2011

Die Excel Funktion RUNDEN(Zahl;Anzahl_Stellen) kann verwendet werden, um Beträge auf ganze Franken, Auf 10 Rappen oder auf Rappen zu runden.

Für die Fünferrundung muss zu einem Kniff gegriffen werden:

Der Betrag wird durch 5 geteilt (aus dem Fünfer wird ein Rappen). Der Betrag wird danach auf Rappen gerundet. Zu guter Letzt wird der gerundete Betrag wieder mit 5 multipliziert (aus dem Rappen wird wieder ein Fünfer).

Die Formel lauter somit:

=Runden(Zahl/5;2)*5

Nehmen wir an, wir möchten auf einen Preis exkl. MWST die Mehrwertsteuer aufrechnen, so lautet die Formel:

=Preis+Preis*MWST (MWST ist hierbei der Mehrwertsteuersatz in %)

Der resultierende Betrag wird sehr oft nicht einem Vielfachen von 5 Rappen entsprechen. Um nun einen korrekt auf 5 Rappen gerundeten Betrag zu erhalten, wird die letzte Formel wie folgt ergänzt:

=RUNDEN((Preis+Preis*MWST)/5;2)*5

Die in der Formel zusätzlich eingefügten roten Zeichen ( RUNDEN(( und )/5;2)*5 ) bewirken die korrekte Rundung auf 5 Rappen und sind unabhängig von der eigentlichen Berechnung des Betrages. Die beiden inneren Klammern sind nicht in jedem Falle notwendig, es ist aber zu empfehlen, diese immer zu verwenden, um auf alle Fälle die eigentliche Berechnung zuerst auszuführen (Klammerausdrücke werden immer zuerst berechnet).

Genauso gut würde die Rundung klappen, wenn zuerst mal 2 gerechnet, auf 1 Stelle gerundet und hernach wieder durch 2 geteilt würde (aus dem Fünfer wird ein Zehner) oder wenn der Betrag zuerst mit 20 multipliziert, auf ganze Zahlen gerundet und danach durch 20 geteilt würde (aus dem Fünfer mach einen Franken und runde auf ganze Franken), etc.

In vielen Lehrmitteln wird für die Fünferrundung zudem die Funktion =VRUNDEN(Zahl;Vielfaches) empfohlen. Achtung diese Funktion rechnet in gewissen Situationen falsch!

=RUNDEN(2.425/5;2)*5 ergibt 2.45 Korrekt

=VRUNDEN(2.425;0.05) ergibt 2.40 Falsch!

=RUNDEN(2.425;2) ergibt 2.43 Korrekt

=VRUNDEN(2.425;0.01) ergibt 2.42 Falsch!

h1

Excel – Nächster Montag

März 17, 2011

Im Kurs kam im Zusammenhang mit einer Übung die Frage:

Wie kann ausgehend von einem beliebigen Datum das Datum des nächsten Montags berechnet werden?

Dies wird z.B. zur Erstellung von Wochenarbeitsplänen benötigt.

image

Basis für diese Berechnung ist die Excel Funktion WOCHENTAG().

=WOCHENTAG(Datum;3) ergibt eine ganze Zahl (0 = Mo, 1 = Di, …, 6 = So), welche dem Wochentag von DATUM entspricht.

=7-WOCHENTAG(Datum;3) errechnet, wie viele Tage es noch dauert bis zum nächsten Montag.

=Datum+7-WOCHENTAG(Datum;3) ergibt schliesslich das Datum des nächsten Montags.

Ist das gegebene Datum bereits ein Montag, so ergibt die Berechnung das Datum des darauffolgenden Montags. Soll in diesem Falle das gegebene Datum als nächster Montag angesehen werden, ist eine zusätzliche WENN()-Bedingung nötig:

=WENN(WOCHENTAG(Datum;3)=0;Datum;Datum+7-WOCHENTAG(Datum;3))

Die im Bild gezeigte Datei kann mit dem folgenden Link heruntergeladen werden:

h1

Excel Funktionen Deutsch-Englisch

März 16, 2011

Im aktuell laufenden Excel Kurs kam zum wiederholten Mal die Frage auf, welche Deutschen, bzw. Englischen Funktionsnamen sich entsprechen.

Die Antwort darauf liefern Suchmaschinen wie Bing oder Google im Handumdrehen.

Der folgende Link von Microsoft Hilfe und Support ist schon etwas älter, enthält aber viele Befehle, die sich auf der Seite mit der Suchfunktion (Ctrl+F) suchen lassen:

http://support.microsoft.com/kb/500972/de

Die folgende Seite enthält ebenfalls eine Funktionsliste Deutsch/Englisch für Excel 4.0:

http://www.htl-steyr.ac.at/~morg/pcinfo/Excel/exce9uxx.htm

Die liste stammt aus der Homepage von Roger Morgen von der HTL Steyr. Dort sind auch sonst einige interessante Informationen nicht nur über Excel zu finden:

http://www.htl-steyr.ac.at/~morg/

Auf der Suche nach Excel Beispielen habe ich im Internet vor einiger Zeit auch die Homepage von Stephan Wottreng gefunden. Auch seine Seite enthält eine riesen Sammlung an Informationen aus verschiedenen Gebieten:

http://www.wottreng.ch/

Die Excel Seite seiner Homepage enthält zahlreiche Übungsbeispiele und weitere interessante Excel Beispiele:

http://www.wottreng.ch/html/excel.html

Nach wie vor erwähnenswert ist der Excel Hero Blog (Englisch), welcher viele bemerkenswerte Beispiele von animierten Excel Charts enthält.

%d Bloggern gefällt das: