Excel-Tipps von Jörg Stottrop

< zurück

Folgende Excel-Formeln und Funktionen verwende ich in meiner täglichen Arbeit als Unternehmensberater:

  • Zellbezug auf ein anderes Arbeitsblatt: =Tabelle!Zelle. Dadurch können Inhalte anderer Tabellenblätter quasi importiert werden. Beispiel: Der Inhalt der Zelle A3 des Tabellenblatts Tabelle4 soll in der Tabelle1 in der Zelle A1 angezeigt werden. In die Zelle A1 der Tabelle1 wird „=Tabelle4!A3“ geschrieben. Vor dem Ausrufezeichen steht der Name des Tabellenblatts. Nach dem Ausrufezeichen die Bezeichnung der jeweiligen Zelle. Die Bezeichnung der Zelle findet man bei Excel links oben im Namenfeld links von der Bearbeitungsleiste. Er besteht immer aus einem oder mehreren Buchstaben für die Spalte und einer Ziffer für die Zeile. Excel kann auch die Inhalte anderer Excel-Tabellen importieren. Dafür muss man vor dem Namen des Tabellenblatts den korrekten Namen der Quell-Datei in eckigen Klammern schreiben.
  • Absoluter Zellbezug: Bei Berechnungen arbeitet Excel automatisch mit relativen Zellbezügen. Wenn in Zelle A2 der Preis pro Stück und in Zelle A3 die Stückzahl steht, kann man in Zelle A4 das Produkt A2*A3 bilden. Verschiebt man jetzt die Zelle A2 in die Zelle B2, so wird das Ergebnis falsch, da Excel den ersten Faktor nicht mehr findet.Dies kann man per Dollar-Zeichen ändern. Man nennt dies „absoluter Zellbezug“. Das funktioniert wie folgt: Man schreibt in die Berechnungszelle vor der Spalten- und Zellenbezeichnung jeweils das Dollarzeichen. Das sieht so aus: „=$A$2*$A$3“. Jetzt kann man beide Zellen per Paste und copy woanders hin kopieren und in der Berechnungszelle bleibt das Ergebnis immer gleich. Wechslen zwischen absolutem und relativem Zellenbezug geht wie folgt: Man geht auf die fragliche Berechnungszelle. Dann drückt man F2 und es erscheint der Inhalt der Bearbeitungszeile in der Zelle. Durch mehrmaliges Drücken von F4 kann man nun zwischen den verschiedenen Arten von Zellbezügen wechseln, ohne jedesmal genau das Dollarzeichen einfügen oder entfernen zu müssen.
  • Anzahl2: gibt die Anzahl nicht-leerer Zeilen an =ANZAHL2(F4:F83).
  • Die Anzahl leerer Zellen kann man mit folgender Funktion ermitteln: =ANZAHLLEEREZELLEN(F4:F83).
  • Zählenwenn: zählt die Elemente, die das Suchkriterium enthalten. Mit der folgenden Formel wird ermittelt, wie oft das Wort „Kran“ in einer Spalte F vorkommt =ZÄHLENWENN(F4:F183;“Kran“).
  • Die Funktion Zählenwenns zählt mehrere Kriterien in mehreren Bereichen. So kann man zum Beispiel die Anzahl der Krane von einem Hersteller etwa Abus ermitteln.
  • Die Funktion Summewenn arbeitet mit zwei Spalten. Sie addiert Werte einer Spalte, für die ein Merkmal einer zweiten Spalte zutrifft. In einer Tabelle mit den Tages-Umsätzen der eigenen Produkte will man beispielsweise wissen, wie der Gesamtumsatz des Produkts „ApplicationA“ ist. Dazu gibt man folgende Formel ein: =SUMMEWENN(A3:A209;“ApplicationA“;B3:B209) In der Spalte A stehen die Produktnamen, dabei ist unter anderem das Produkt „ApplicationA“.In der Spalte B stehen die Einzelumsätze in Euro hinter den jeweiligen Produktnamen, die man addieren will. Drückt man auf Enter durchforstet Excel nun die Tabelle in der Spalte A nach dem eingegebenen Produktnamen. Immer wenn ApplicationA auftaucht, nimmt es den Eurowert aus Spalte B und addiert ihn bis zur Zeile 209 und gibt das Ergebnis in der Zelle aus, in der diese Funktion steht.
  • Analog zur Funktion Zählenwenns gibt es auch Summewenns. Hiermit können Zahlen aus einem Bereich addiert werden, die mehrere Kriterien erfüllen. Beispielsweise können so die Wartungskosten für Kräne der Firma Abus ermittelt werden aus einer langen Tabelle, in der alle Wartungskosten für alle Maschinen von verschiedenen Lieferanten aufgeführt werden. Oder es können die Umsätze für das Produkt ApplicationA im Land Schweiz ermittelt werden. Ähnlich wie bei Zählenwenns können hier zahlreiche verschiedene Kriterien eingegeben werden. Dabei hilft der Funktionsassistent.
  • Excel-Funktionsassistent nutzen: Auf die Zelle klicken, die das Ergebnis anzeigen soll und dann auf die Schaltfläche fx in der Bearbeitungsleiste oben. Dies öffnet den Funktionsassistent von Excel, der bei der Eingabe von Formeln hilft.
  • Das heutige Datum wird in einer Zelle mit der Funktion = heute() angezeigt. Die Zelle muss zur korrekten Anzeige als Datum formatiert sein.
  • Liefer- oder Abgabetermin berechnen mit der Funktion „Arbeitstag“. Dazu das Anfangsdatum angeben, dann die Zahl der benötigten Arbeitstage bis zur Fertigstellung des Auftrags. Zusätzlich die Anzahl der freien Tage wie gesetzliche Feiertage oder Urlaub angeben. Die Daten jeweils mit Semikolon trennen und auf die Enter taste drücken: Es erscheint das Fertigstellungsdatum bzw. der Ausliefertermin. Auch hier darauf achten, die Zelle als Datum zu formatieren.
  • Die Funktion =Kalenderwoche() gibt die Kalenderwoche eines Datums wieder. So kann man beispielsweise die Kalenderwoche des Auslieferdatums angeben, wenn man sich nicht auf einen einzigen Tag festlegen will.
  • Nettoarbeitstage gibt die Anzahl der Arbeitstage in einem Zeitintervall an. Beispielsweise zwischen heute und dem 31.12. des Jahres. Neben dem Start- und dem Enddatum müssen auch die Anzahl oder die Daten der freien Tage wie zum Beispiel gesetzliche Feiertage eingegeben werden.So kann man auch ausrechnen, wie viele Tage man noch bis zum Urlaubsbeginn arbeiten muss.
  • Eine weitere nützliche Funktion ist Wochentag. Damit kann man feststellen, auf welchen Wochentag man den Abgabe- oder Liefertermin gelegt hat. Dazu kann man den Funktionsassistenten aufrufen und das gewünschte Datum eingeben. Allerdings sollte man das Feld „Typ“ im Funktionsassistent offen lassen. Excel zeigt das Ergebnis zunächst als Zahl an. 1 ist ein Sonntag und 7 ein Samstag. Das ist natürlich verwirrend. Zur Ausgabe des Namens des Wochentags als Wort, muss man die betreffende Zelle formatieren und zwar wie folgt: Rechte Maustaste – Zelle formatieren – benutzerdefiniert – die folgenden Buchstaben eingeben: TTTT. Dann erscheint der Name des Wochentages je nach eingestellter Sprache.
  • Eine Zufallszahl wird mit =Zufallszahl() erzeugt.
  • Dropdown-Liste benutzen. Will man nur bestimmte Daten oder Wörter in einer Spalte oder bestimmten Zellen zulassen, so kann man mit Dropdown-Listen arbeiten. Beispielsweise, wenn nur bestimmte Personen, Niederlassungen oder Firmen in einer Tabelle eingetragen werden dürfen. Dazu erstellt man zunächst die Liste der zugelassenen Daten, entweder am Rande desselben Tabellenblatts oder in einem anderen Tabellenblatt, markiert diese und vergibt einen Namen. Dazu klickt man auf das Menü „Formeln“ und dort auf „Namen definieren“. Dann gibt man einen Namen, den man sich für diese Liste ausgedacht hat, ein. Wenn die Liste in einem anderen Tabellenblatt ist, muss man dies an dieser Stelle angeben. Dann wird mit „ok“ dieser Schritt bestätigt. Als nächstes werden die Zellen markiert, in denen künftig die ausgewählten Daten stehen werden. Dann geht man auf das Menü „Formeln“ und dort auf die Gruppe „Datentools“ und dort auf „Datenüberprüfen“. Nun öffnet sich ein Fenster. Im ersten Reiter „Einstellungen“ wählt man unter „Zulassen“ die Alternative „Liste“ und gibt den Namen der Liste mit einem vorangestellten Gleichheitszeichen ein oder markiert diese. Zum Bestätigen dieses Schritts klickt man auf ok. Das war´s. Nun kann mit dem Ausfüllen der Tabelle begonnen werden. Gibt man in den markierten Zellen einen anderen als einen der zulässigen Werte ein, so erscheint eine Fehlermeldung von Excel.
  • Weitere nützliche Funktionen sind sverweis und wenn.
  • Komplexere Datenanalysen gelingen mit Histogramm, gleitender Durchschnitt, exponentieller Glättung, Regressionsanalyse oder Excel-Solver. Dazu muss das Add-In-Paket aktiviert werden, was im normalen Excel-Ausgangszustand häufig nicht der Fall ist. Die Aktivierung geschieht über über Extras-Daten oder über Optionen-Add-Ins. In Excel 2010 lassen sich die Datenanalyse-Tools über das Menü Daten – Analyse – Datenanalyse aufrufen.
  • Pivot-Tabellen sind in Excel-2010 nicht mehr über das Menue Daten, sondern über den Reiter „Einfügen“ und dann „Pivot“ zu finden.
  • Weitere Hilfen sind durch Drücken der Taste F1 oder im deutschsprachigen Microsoft Excel-Hilfe-Center oder bei spezialisierten Anbietern wie www.office-kompetenz.de/category/excel/ erhältlich.