Tipps und Tricks zu Excel

1/27

Tipps und Tricks zu Excel

Die wichtigsten Formeln

Erklärungen

Tipps

Inhaltsverzeichnis

Operatoren ... 3

Addition ... 3

Subtraktion ... 3

Multiplikation ... 3

Division ... 3

Potenzieren ... 3

Wurzel ziehen ... 4

Wichtige mathematische Grundregeln ... 4

Punkt vor Strich-Rechnung ... 4

Klammerregel ... 4

Funktionen ... 5

Summe ... 6

MAX ... 6

MIN ... 6

ANZAHL... 6

ANZAHL2... 7

ANZAHLLEEREZELLEN ... 7

Mittelwert ... 8

WENN ... 9

UND ... 9

ODER... 9

SUMMEWENN ... 10

ZÄHLENWENN ... 10

SVERWEIS ... 11

AUF HALBE (NOTEN) RUNDEN ...13

Funktion RUNDEN ...14

AUF ANZAHL KOMMASTELLEN RUNDEN ...14

AUF ZEHNER-STELLEN RUNDEN ...14

AUF HUNDERTER-STELLEN RUNDEN ...14

AUF HALBE (NOTEN) RUNDEN ...14

AUF 5ER RUNDEN OHNE VRUNDEN ...14

Übersicht VRUNDEN vs. RUNDEN ...15

AUFRUNDEN...15

ABRUNDEN ...15

Zeit-Funktionen und Rechnen mit Zeit ...16

JETZT ...16

HEUTE ...16

JAHR ...16

ANZAHL TAGE ...16

ANZAHL STUNDEN...16

Summe Std. über 24 ...17

Stundenlohn berechnen...17

STUNDEN IN DEZIMALZAHL ANGEBEN ...18

BrteilJahre ...19

DATEDIF...20

Prozent, Zins und Rabatt ...21

ANTEIL BERECHNEN ...21

ZINS ...21

RABATT ...22

3/24

Operatoren Addition

+

Addieren ( + )

ACHTUNG: Bei mehr als zwei zu addierenden Zahlen die Funktion SUMME benutzen

Subtraktion

–

Subtrahieren ( - )

Multiplikation

*

Multiplizieren ( * )

Division

/

Dividieren (

/ )

Potenzieren

^

Potenzieren (^x)

Im Beispiel links können Sie die Formel in B2 nach unten und nach oben kopieren, um jeweils die Potenzierung zu erhalten.

Dunkelblau = Potenz / Hellblau = Zahl, die potenziert wird

=Zahl^Potenz

Wurzel ziehen

^(1/x)

Radizieren (^(1/x))

Links wird in Zelle D2 zurück gerechnet: Also die vierte Wurzel aus 16.

=Zahl^(1/x-te Wurzel)

Wichtige mathematische Grundregeln Punkt vor

Strich-Rechnung

R

EGEL

: P

UNKT VOR

S

TRICH

Multiplikation und Division sind immer stärker als Addition und Subtraktion

Beispiel ohne Klammer:

Zuerst wird A1 mit B1 multipliziert und dann erst wird C1 hinzu- gerechnet.

Also: 3 * 4 = 12

12 + 5 = 17

Klammerregel

Beispiel mit Klammer:

Zuerst werden B2 und C2 addiert, dann erst wird das Resultat mit A2 multipliziert.

Also: 4 + 5 = 9 9 * 3 = 27

5/24

Funktionen

Funktionen sind nichts anderes als Anweisungen an Excel, wie etwas berechnet werden soll, bzw. was mit einem Zellinhalt gemacht werden soll.

Sie können Funktionen direkt eintippen (falls Sie die Syntax

¹

schon kennen) oder den Funktionsassistenten benutzen. Letzterer gibt Ihnen Hinweise zur Syntax.

Aufruf Funktionsassistent Klick auf … Eine Funktion hat IMMER den gleichen Grundaufbau (Syntax)

Orange Das muss immer stehen, egal um welche Funktion es sich handelt.

Blau Je nach Funktion steht in den Klammern nichts oder es können eine oder mehrere Angaben stehen.

= Funktionsname ( 1. Argument ; 2. Argument ; 3. Argument )

zeigt Excel an, Name der Zeigt Excel an, Angabe, welche Grenze Angabe, welche Grenze zwischen Angabe, welche Zeigt an, dass dass etwas Funktion, die dass hier jetzt die Funktion zwischen den die Funktion den Argumenten die Funktion Schluss ist

berechnet ausgeführt werden noch Infos braucht Argumenten braucht braucht

werden soll soll kommen

Es gibt Funktionen, die keine Angaben brauchen, dann steht in der Klammer nichts drin =HEUTE() Manche brauchen nur eine Angabe (wie hier der Bereich, der summiert werden soll) =SUMME(A1:C11)

Manchen brauchen mehr als eine Angabe =VRUNDEN(B3;0.05)

=WENN(A1>A2;A1-A2;A2-A1)

1 Syntax = Der genaue Aufbau einer Funktion. Wo muss was genau stehen, damit die Funktion das richtige Resultat liefern kann (Klammern, Strichpunkte, benötigte Werte, etc.)

Summe

Sollen unzusammenhängende Werte summiert werden, werden diese nach einem Strichpunkt hinzugefügt.

Dasselbe gilt für Werte, die doppelt berechnet werden (z. B. wenn ein Wert doppelt zählt)

MAX

Grösster Wert

MIN

Kleinster Wert

ANZAHL

Anzahl Zellen mit Zahlen

7/24

ANZAHL2

Anzahl nicht leerer Zellen

Anzahl Zellen mit Text

ANZAHLLEEREZELLEN

Liefert die Anzahl Zellen, die leer sind.

Achtung: Leerschlag ist nicht leer!

=ANZAHLLEEREZELLEN(Bereich)

Mittelwert

Durchschnitt berechnen

Errechnet den Durchschnitt einer Zahlenreihe oder verschiedener Werte

Abb. links: Einfacher, zusammenhängender Bereich

Abb. links: Mehrere Bereiche

Bei Werten, welche mehrfach zählen (wie z. B. doppelt wie bei Abbildung links), werden diese nochmals in der Funktion aufgeführt.

9/24

WENN

Einfache Wenn-Bedingung

Verschachtelte Wenn- Bedingung

Links gibt es nur zwei Möglichkeiten

Hier werden drei Varianten abgedeckt. Dies ist mit einer verschachtelten WENN- Funktion möglich.

Mehrere Bedingungen in der Wenn-Funktion überprüfen

UND

Zur Überprüfung von zwei oder mehreren Inhaltswerten

Wenn alle Bedingungen erfüllt sind, liefert die Formel «WAHR»

 kann für eine Wenn-Abfrage verwendet werden.

=UND(Wahrheitswert1; Wahrheitswert2; etc.)

Beispiel links: Es erhalten nur Senioren des Vereins einen Rabatt

ODER

Zur Überprüfung von zwei oder mehreren Inhaltswerten

Wenn eine der Bedingungen erfüllt sind, liefert die Formel

«WAHR»

 kann für eine Wenn-Abfrage verwendet werden

=ODER(Wahrheitswert1; Wahrheitswert2; etc.)

Beispiel links: Ist man entweder Senior oder Vereinsmitglied, erhält man Rabatt

SUMMEWENN

=SUMMEWENN(Bereich;Suchkriterium;Summe_Bereich) Wollen wir in einem Bereich nur bestimmte Werte summiert haben, dann kommt die Funktion SUMMEWENN zum Zug.

Bereich In welchem Bereich soll der gesuchte Name (z. B. Egon) drin stehen. Hier ist es A1:A10 Suchkriterium Nach was soll gesucht werden? Hier: der Name

«Egon»

Summe_Bereich Wo befinden sich die Zahlen, die summiert werden sollen (hier: B1:B10)

Im Beispiel links wird der Wert in der Spalte B nur summiert, wenn in der Spalte A der Name Erwin steht. Dasselbe passiert mit den anderen Namen.

ZÄHLENWENN

=ZÄHLENWENN(Suchbereich;Vergleichswert)

Funktioniert gleich wie SUMMEWENN, aber es wird nicht summiert, sondern gezählt wie oft der gesucht Wert vorkommt.

Im Beispiel links werten wir aus wie oft welches Menü bestellt wurde.

ACHTUNG Wenn ich vergleichen will, dann muss ich Anführungszeichen setzen

Will ich z. B. nur Werte grösser als Null, ist der Syntax folgender: =ZÄHLENWENN($B$3:$M$5;“>0“)

11/24 Mit SVERWEIS können Sie komplexe, verschachtelte WENN-Funktionen vermeiden.

SVERWEIS

SVERWEIS holt für uns gewisse Daten aus einer Liste (Matrix), so dass wir sie nicht jedes Mal von Hand reinschreiben müssen. Ein klassisches Beispiel wäre: Man tippt eine Artikelnummer ein und der ganze Rest (Artikelbezeichnung, Preis, etc.) holt Excel automatisch.

=SVERWEIS(Suchkriterium;Matrix;Spaltenindex;

Bereich_Verweis)

Suchkriterium Danach soll Excel in einer Liste (Matrix) suchen.

In Beispiel links ist das die PLZ 4132 in der Zelle B1.

Matrix Liste, in der nach dem Begriff gesucht werden soll. Diese kann auf demselben Tabellenblatt liegen oder woanders. Hier: E1:F13.

Spaltenindex Nummer der rechts daneben liegenden Spalte.

Das Suchkriterium befindet sich in Spalte 1, alle anderen rechts davon werden

durchnummeriert. Im Beispiel links ist also der Spaltenindex = 2.

Bereich_Verweis FALSCH Der Wert muss genau überein- stimmen

WAHR Es wird derjenige Wert genommen,

der unterhalb des gesuchten Wertes liegt (siehe Beispiel links)

RANG

Gibt den Rang, den eine Zahl innerhalb einer Liste von Zahlen

einnimmt.

Syntax: =RANG(Zahl, deren Rang wir ermitteln wollen; Liste aller Zahlen; Sortierreihenfolge)

Sortierreihenfolge: 0 ergibt absteigend sortiert 1 ergibt aufsteigend sortiert

0 = Der mit der höchsten Zahl, erreicht den ersten Rang (z. B. bei Punkten)

1 = Der mit der niedrigsten Zahl erreicht den ersten Rang (z. B. bei Zeitrennen)

TEILERGEBNIS

Führen Sie eine Funktion wie Summe etc. über eine gefilterte Tabelle, werden auch die ausgeblendeten Zeilen mitgerechnet.

Mit TEILERGEBNIS können diese Berechnungen korrekt ausgeführt werden.

Die Funktion wird als Zahl aus der Liste oben ausgewählt.

13/24

Funktion VRunden

Die Qual der Wahl! ... oder doch nicht?

Hier gibt es z. T. mehrere Wege zum Ziel. Merken Sie sich diejenige, welche Ihnen am besten im Gedächtnis bleibt.

VRUNDEN ist einfach in der Anwendung. Sie hat aber 2 gewichtige Nachteile:

a) Sie funktioniert nicht immer korrekt. Es gibt Situationen, in denen diese zu Rundungsfehlern führt!

b) Sie funktioniert nicht bei negativen Zahlen!

Im Berufsalltag ist deshalb von dieser Funktion abzuraten und mit der Funktion RUNDEN zu rechnen!

VRUNDEN

Vielfaches, z. B.: 0.05 0.1

0.5

5

10 50

100

1000

etc.

AUF 5ER RUNDEN

AUF HALBE

(NOTEN) RUNDEN

VRUNDEN funktioniert leider nicht immer und mit allen

Programmen (Access z. B.). Deshalb muss man auch die Alternative RUNDEN kennen

Funktion RUNDEN

AUF ANZAHL KOMMASTELLEN

RUNDEN

3  3 Kommastellen

2  2 Kommastellen 1  1 Kommastelle 0  ganze Zahl -1  Zehnerstellen -2  Hunderterstellen -3  Tausenderstellen etc.

AUF ZEHNER- STELLEN RUNDEN

AUF HUNDERTER- STELLEN RUNDEN

AUF HALBE

(NOTEN) RUNDEN

Da die RUNDEN-Funktion nur auf Dezimalstellen runden kann, ist für das Runden auf 0.5 oder 0.05 ein Trick notwendig!

IDEE

Runden auf halbe Noten

Wenn ich die Zahl so vervielfachen kann, dass dabei ganze Zahlen entstehen, ergeben alle anderen Zahlen weiterhin Dezimalstellen.

Runde ich die Dezimalstellen weg, erhalte ich eine ganze Zahl und somit einen Wert, der sich wie halbe Noten verhalten.

AUF 5ER RUNDEN OHNE VRUNDEN

Ein Beispiel zur Veranschaulichung …

4.5  4.5 mal was ergibt eine ganze Zahl?  Faktor 2 Also: 4.5 × 2 = 9

4.6 × 2 = 9.2  Runde ich den Wert auf ganze Zahlen, ergibt das 9.

=RUNDEN(F2*2;0)/2

15/24

Übersicht VRUNDEN vs.

RUNDEN

Runden VRunden

1000stel =Runden(Zahl;3) =VRunden(Zahl;0.001)

100stel =Runden(Zahl;2) =VRunden(Zahl;0.01)

10tel =Runden(Zahl;1) =VRunden(Zahl;0. 1)

1er =Runden(Zahl;0) =VRunden(Zahl;1)

10er =Runden(Zahl;-1) =VRunden(Zahl;10)

100er =Runden(Zahl;-2) =VRunden(Zahl;100)

0.05 =Runden(Zahl*20;0)/20 =VRunden(Zahl;0.05)

0.5 =Runden(Zahl*2;0)/2 =VRunden(Zahl;0.5)

0.25 =Runden(Zahl*4;0)/4 =VRunden(Zahl;0.25)

AUFRUNDEN

3  3 Kommastellen 2  2 Kommastellen 1  1 Kommastelle 0  ganze Zahl -1  Zehnerstellen -2  Hunderterstellen -3  Tausenderstellen etc.

ABRUNDEN

Zeit-Funktionen und Rechnen mit Zeit

JETZT

Ergibt das aktuelle Datum und die aktuelle Uhrzeit.

Das Zellformat entscheidet, was dargestellt wird (siehe Abb. links, wo oben das Format auf Datum und Zeit gestellt ist und unten nur auf die Zeit mit Sekunden)

In der Klammer braucht es keine Argumente (=Angaben)

HEUTE

Gibt das aktuelle Datum aus.

In der Klammer braucht es keine Argumente (=Angaben)

JAHR

Wandelt eine Zahl in ein Jahr um.

=JAHR(Zelle)

Anwendung Z. B. wenn man von einem Datum nur das Jahr braucht

ANZAHL TAGE

Berechnung der Anzahl Tage zwischen zwei Daten.

späteres Datum – früheres Datum = Anzahl Tage Das Format der Ergebniszelle muss ZAHL sein!

ANZAHL STUNDEN

Berechnung der Anzahl Stunden zwischen zwei Daten.

spätere Zeit – frühere Zeit = Anzahl Stunden ACHTUNG:

17/24

Summe Std. über 24

Ist das Total der Anzahl Std. über 24, dann muss die Formatierung der Ergebniszelle (hier B7) angepasst werden auf:

[h]:mm oder [hh]:mm

Sonst wird falsch berechnet (nämlich immer unter 24)!

Stundenlohn berechnen

Für manche Berechnungen muss man Dezimalzahlen in Zeit umrechnen oder umgekehrt.

Dafür muss man folgende Berechnung anstellen: Zeit umwandeln in Dezimalzahl: Zeit * 24

Achtung: Die Ergebniszelle muss ein Zahlenformat haben!

Dezimalzahl umwandeln in Zeit: Dezimalzahl/24

Beispiel: Ich will bei einem Stundenansatz von CHF 100.–

berechnen wie viel 15 Minuten Arbeit kosten.

Lösung: 00:15*24*100

STUNDEN IN DEZIMALZAHL ANGEBEN

Da ein Tag 24 Stunden beinhaltet und ein Tag den Wert 1 hat, multiplizieren wir die Zeit mit 24 und kommen so auf den Dezimalwert

Per Definition in Excel ist

01.01.1900 (im Datumsformat)  die Zahl 1 (im Standardformat) Die Stunden sind demnach ein Bruchteil der Zahl 1.

z. B. am 01.01.1900 um 12:00 Uhr setzt Excel den Standardwert 1.5 ein.

19/24

BrteilJahre

^Argument Beschreibung Anmerkungen Ausgangsdatum Das erste

Datum eines Zeitraums.

Wenn kein gültiges Datum eingegeben, gibt Funktion

#WERT! zurück.

Wenn Argument Dezimalwert enthält, ignoriert Funktion Ziffern hinter dem Dezimaltrennzeichen.

Enddatum Das letzte Datum eines Zeitraums.

Siehe oben

Basis Gibt an, auf

welcher Basis die Zinstage gezählt werden.

0 oder keine Eingabe = USA (NASD) 30/360 1 = Taggenau/taggenau 2 = Taggenau/360 3 = Taggenau/365 4 = Europa 30/360 Anwendungsbeispiel MARCHZINSBERECHNUNG

=BRTEILJAHRE(Ausgangsdatum;Enddatum;Basis)

DATEDIF

Anzahl Jahre

Anzahl Monate

Anzahl Tage

= DATEDIF(Startdatum;Enddatum;"Zeiteinheit")

Mit der DATEDIF Funktion können Sie verschiedene Differenzen zwischen 2 Datumswerten berechnen. Die Funktion hat in Excel ein eigenartig verstecktes Dasein. Sie ist weder im Einfügen/

Funktionen Dialog verfügbar noch wird sie in der Hilfe zu Excel überhaupt erwähnt. Es gibt sie schon seit Excel 5 und vermutlich ist sie auch in zukünftigen Versionen noch verfügbar.

Zeiteinheit Beschreibung

y Anzahl kompletter Jahre

m Anzahl kompletter Monate

d Anzahl der Tage

md Unterschied in Tagen, wobei Monate und Jahre ignoriert werden

ym Unterschied in Monaten, Tage und Jahre bleiben unberücksichtigt

yd Unterschied in Tagen, wobei die Jahre ignoriert werden

21/24

Prozent, Zins und Rabatt

ANTEIL BERECHNEN

Wollen wir ausrechnen wie viel z. B. 7 % von 150'000 sind, dann können wir in Excel direkt folgendes rechnen:

%-Faktor*Totalbetrag also 7%*150'000

ZINS

SCHULDEN

BANKKONTO

Wenn Sie Geld auf dem Bankkonto haben, erhalten Sie dafür einen Zins. Wenn Sie Schulden machen, müssen Sie dafür Zinsen zahlen.

Berechnung:

Zins = Zinssatz in % * Geldbetrag

Ein Zinssatz ist in der Regel für ein ganzes Jahr gemeint (siehe Beispiele links).

RABATT

ABZIEHEN

–

Rabatt wird in CHF angegeben.

Rabattsatz wird in % angegeben.

Preis mit Rabatt direkt berechnen

Was ist ein Rabatt?

Ein Rabatt ist wenn Sie etwas billiger kaufen können, als es eigentlich kosten würde. Im Beispiel links kriegen Sie einen Rabatt auf den Kauf einer Jeans.

Ein Rabatt wird genau gleich berechnet wie das Beispiel «Anteil berechnen» weiter oben.

Der Rabatt wird immer vom ursprünglichen Preis abgezogen!

Neuen Preis direkt berechnen:

Preis mit Rabatt ergeben im Beispiel hier 85 %.

85 % soll mit dem gegebenen Prozentsatz erzeugt werden:

100 % = 1

15 % stammt aus der Zelle B12

 Rabattsatz wird von 100 % subtrahiert.

23/24

MEHRWERTSTEUER

ABZIEHEN

+

Mehrwertsteuer wird in CHF angegeben.

Mehrwertsteuersatz wird in % angegeben.

Preis inkl. Mehrwertsteuer direkt berechnen

Was ist die Mehrwertsteuer?

Die Mehrwertsteuer wird erhoben auf alles, was man in der Schweiz kauft. Das heisst, wenn man einen Apfel, ein Auto oder einen Computer kauft, dann zahlt man darauf Steuern. Diese Steuern werden zum Preis dazugerechnet und man bezahlt sie gleich an der Kasse im Laden mit. Im Laden sieht man einem Preis nicht an, wie viel MwSt. dabei ist, aber auf einer Rechnung wird die Mehrwertsteuer meist ausgewiesen oder es steht

«Preise inkl. MwSt.».

Aktuell liegt der Mehrwertsteuersatz bei 8 %.

Neuen Preis direkt berechnen:

Preis mit Mehrwertsteuern ergeben im Beispiel hier 107.6 %.

107.6 % soll mit dem gegebenen Prozentsatz erzeugt werden:

100 % = 1

7.6 % stammt aus der Zelle B8  beides wird zusammen addiert.

ANTEIL IN %

Sie möchten wissen wie viele Prozent ein Teil von einem Ganzen ausmacht.

Aus der Sek.-Stufe sind Sie folgende Rechnung gewöhnt:

Teil/Ganzes*100

Im Excel läuft's etwas einfacher:

Ergebniszelle %-Format zuweisen (das erspart Ihnen das *100) Dann: ANTEIL/TOTAL (siehe Beispiel links)

Einheiten erstellen BENUTZER-

DEFINIERTE EINHEIT

Einheiten, die nicht vorkommen, können selber erzeugt werden.

Beispiel «km»

Dialogbox ZELLEN FORMATIEREN öffnen

 Kategorie BENUTZERDEFINIERT wählen

 Als Ausgangslage wählen Sie einen Typen aus, der Ihrem Ziel am nächsten kommt.

 Ergänzen Sie anschliessend in „“ den Text.

#'##0" km"

Nach der 0 kein Leerschlag!

Setzen Sie nach dem ersten " einen Leerschlag, damit die Einheit nicht an der Zahl «klebt».