Will man mit Daten strukturiert umgehen, kann Excel hilfreiche Dienste leisten. Oft liegen die Rohdaten aber gar nicht so geordnet vor, wie es eine Weiterverarbeitung erfordern würde. Da bietet Excel weitreichende Funktionen, um derlei Unbill beizukommen. Diesen und anderen Problemen widmen sich die folgenden Excel-Tipps.
Das Gros der nachfolgenden Tipps richtet sich an Anwender von Microsoft Excel 2010. Einige der Lösungen funktionieren aber auch vorzüglich mit den Vorgängerversionen, manchmal haben sich lediglich die Menüpunkte ein wenig geändert. Klappt der Tipp mit älteren Excel-Versionen, haben wir dies an Ort und Stelle vermerkt. Und die meisten Tipps funktionieren auch mit der neueren Version Excel 2013.
Daten aus einer Zelle auf mehrere Zellen aufteilen
Das gehört zu den Standardsituationen bei der Datenübernahme: Man bekommt Daten nicht so organisiert übergeben, wie man sie für die Weiterverarbeitung benötigen würde. Einzelne Daten sind in einer Zelle zusammengefasst, die für eine vernünftige Verarbeitung getrennt sein sollten. Die ganz großen Klassiker dabei sind Vorname und Name sowie Postleitzahl und Ort. Natürlich kann man sich für entsprechende Trennungsaufgaben komplexe Formeln mithilfe der Funktionen LINKS, RECHTS, FINDEN et cetera bauen. Für das Gros der Vorkommnisse, wie die oben erwähnten Beispiele, dürften ein paar Mausklicks und der Textkonvertierungsassistent jedoch allemal genügen.
Markieren Sie hierfür die betroffenen Zellen. Wechseln Sie auf den Menüpunkt Daten und starten dort den Textkonvertierungsassistent über den Punkt Text in Spalten. Bestätigen Sie das erste Fenster mit der gewählten Option Getrennt und klicken Sie auf Weiter. Achten Sie im nächsten Fenster darauf, dass als Trennzeichen unbedingt Leerzeichen ausgewählt ist. Nachfolgend erhalten Sie eine Vorschau der künftigen Zellenaufteilung.
Aktivieren Sie zudem noch die Option Aufeinanderfolgende Trennzeichen als ein Zeichen behandeln. Damit können Sie gegebenenfalls auch gleich Nachlässigkeiten bei der Eingabe wie doppelte Leerzeichen ausbügeln. Das Ganze klappt wie im gezeigten Beispiel auch dann, wenn mehr als zwei Daten in einer Zelle zusammengefasst sind.
Dieser Tipp funktioniert mit Excel 2010. In vorhergehenden Excel-Versionen ist die Herangehensweise ähnlich. Ab Excel 2013 kann man sich hierfür auch die Blitzvorschau zunutze machen.
Speicherort für Dateien dauerhaft einblenden
Insbesondere wenn man Tabellen und Arbeitsmappen auf Netzwerklaufwerken ablegt, kann es hilfreich sein, diesen exakten Pfad in der Schnellzugriffsleiste von Excel einzublenden. Der häufig beliebig komplexe Pfad lässt sich so schnell an Kollegen weiterreichen.
Eben hat man noch die Arbeitsmappe gespeichert, und schon bräuchte man genau diesen Pfad, um ihn an die ebenfalls am Projekt beteiligten Kollegen zu senden. Schließlich ist es allemal besser, lediglich den Link als die ganze Datei beispielsweise per Mail zu versenden. In Excel 2010 können Sie den genauen Speicherort der Datei einfach in der Schnellzugriffsleiste einblenden.
Klicken Sie hierzu auf den Pfeil neben der Schnellzugriffsleiste und wählen Sie Weitere Befehle. In der Box Befehle auswählen ist die Option Befehle nicht im Menüband die richtige Wahl. Fügen Sie den Befehl Dokumentenspeicherort der Schnellzugriffsleiste hinzu.
Wenn Sie die Box nicht mehr in der Schnellzugriffsleiste haben wollen, klicken Sie mit der rechten Maustaste auf den Rand der Box. Wählen Sie Aus Symbolleiste für den Schnellzugriff entfernen.
Dieser Tipp funktioniert mit Excel 2010. Die Vorgehensweise in Excel 2007 ist ähnlich.
Mit tatsächlichen Arbeitstagen rechnen
Wer komplexe Auswertungen erstellen muss oder für Projektabläufe verantwortlich ist, tut gut daran, die entsprechenden Zeiträume auf tatsächliche Arbeitstage zu untersuchen. Das dient sowohl als Planungs- als auch als Argumentationshilfe. Excel stellt hierfür eine gesonderte Funktion zur Verfügung.
Die Situation ist bekannt: Es fallen leichtfertig Äußerungen wie "Das Projekt lässt sich locker in einem Monat stemmen", und dann bringt besagter Monat gerade mal 21 Arbeitstage mit, obwohl es ein Monat mit 31 Bruttotagen ist. Und schon hat man sich einen unrealistischen Abgabetermin eingehandelt.
Über die Excel-Funktion Nettoarbeitstage können Sie berechnen, wie viele Arbeitstage zwischen zwei Daten tatsächlich verfügbar sind. Die Syntax der Funktion ist denkbar einfach:
=NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;Freie_Tage)
Wenn Sie lediglich Ausgangsdatum und Enddatum eingeben, liefert Ihnen Excel die Arbeitstage für den entsprechenden Zeitraum, ohne die Wochenenden. Damit auch Feiertage oder andere freie Tage, wie etwa Betriebsferien oder geregelte Brückentage, berücksichtigt werden, gilt es noch eine Liste für das Argument "Freie_Tage" anzulegen. Legen Sie hierfür einen Zellbereich mit den entsprechenden Daten an und vergeben Sie für diesen Bereich einen Namen, den Sie wiederum entsprechend in die Formel einfügen.
Die Funktion gehört seit Office 2003 zu Excel.
Dateien ohne Makros speichern
Bei Excel werden zur Arbeitserleichterung gerne Makros verwendet. Wenn man Excel-Dateien aber weitergeben möchte, ist es oft unerwünscht, dass diese Makros enthalten. Zumal beim Öffnen der Dateien Sicherheitswarnungen für eine Verunsicherung vieler Empfänger sorgen.
Excel 2007 und Excel 2010 benutzen unterschiedliche Dateitypen, je nachdem, ob der Anwender Tabellen mit oder ohne Makros sichern möchte. Wenn man die Dateien über Speichern unter als Excel-Arbeitsmappe ablegt, entfernt die Tabellenkalkulation alle Makros daraus. Die Dateien erhalten in diesem Fall die Erweiterung XLSX anstatt XLSM.
Standardschrift ändern
Microsofts Excel-Versionen verwenden für neue Dokumente immer die Schriftart Calibri mit 11 Punkt. Um diese Einstellung zu ändern, suchen viele Benutzer nach einer Standardvorlagendatei analog der Normal.dot in Word. Doch in Excel muss man an anderer Stelle ansetzen.
Die notwendige Einstellung finden Sie in Excel 2010 unter Datei / Optionen / Allgemein. Im Abschnitt Beim Erstellen neuer Arbeitsmappen lassen sich Schriftart und -grad für neue Arbeitsmappen festlegen.
In Excel 2007 klicken Sie auf die Office-Schaltfläche, dann auf Excel-Optionen und die Kategorie Häufig verwendet.
Die geänderten Einstellungen sind erst nach einem Neustart von Excel wirksam und gelten nur für danach angelegte Arbeitsmappen.
Zeichenketten aus URLs trennen
Sie bekommen eine Auswertung angeliefert, in der Webadressen mit Zahlenwerten verknüpft sind. Eigentlich möchten Sie die Zahlenwerte aber nur einer bestimmten Zeichenkette der URL zuordnen. Der Textkonvertierungsassistent leistet da praktische Hilfe.
In einer Spalte sind die besagten Webadressen aufgelistet, die anderen Spalten beinhalten die zugehörigen Werte. Wenn Sie sich nur für URLs interessieren, die bestimmte Zeichenketten enthalten, kann man das natürlich prima über Formeln lösen. Mit einem Formelkonstrukt, das Funktionen wie WENN und FINDEN beinhaltet, lässt sich derlei bewältigen. Für viele Fälle dürfte auch die folgende Vorgehensweise per Assistenten genügen.
Mit dem Textkonvertierungsassistenten von Excel können Sie die URLs relativ einfach in die einzelnen Zeichenketten zwischen den "/" aufteilen. Jede Zeichenkette zwischen den Trennzeichen landet danach in einer eigenen Spalte. Somit können Sie für Ihre Weiterverarbeitung nicht wichtige Spalten löschen oder ausblenden oder ganz einfach auch nur mit Filtern arbeiten.
Markieren Sie hierfür die betroffenen Zellen mit den Webadressen. Wechseln Sie auf den Menüpunkt Daten und starten dort den Textkonvertierungsassistent über den Punkt Text in Spalten. Bestätigen Sie das erste Fenster mit der gewählten Option Getrennt und klicken auf Weiter. Achten Sie im nächsten Fenster darauf, dass als Trennzeichen unter Andere unbedingt der "/" (Schrägstrich, Slash) angegeben ist. Nachfolgend erhalten Sie eine Vorschau der künftigen Zellenaufteilung.
Achten Sie darauf, eventuell einen neuen Zielbereich anzugeben, damit andere vorhandene Daten nicht überschrieben werden. Oder verlegen Sie die Aktion gleich in ein anderes Arbeitsblatt. So können Sie jetzt diejenigen Daten nach den Bestandteilen der URL selektieren, die für eine Weiterverarbeitung wichtig sind. Die Möglichkeiten sind da vielfältig: Mancherorts wird ja das Datum automatisch in die URL als Zeichenkette übergeben; so können Sie es wieder getrennt ausweisen.
Dieser Tipp funktioniert mit Excel 2010. In vorhergehenden Excel-Versionen ist die Herangehensweise ähnlich.
Tabellenzeilen farblich abwechselnd hervorheben
Umfangreichere Tabellen mit vielen Spalten und Zeilen lassen sich besser lesen, wenn gerade und ungerade Zeilen abwechselnd eingefärbt sind. In den aktuellen Versionen von Microsofts Tabellenkalkulation muss für diese Funktion niemand mehr ein Makro schreiben.
Excel ab Version 2007 unterstützt den Anwender bei diesem Problem durch eine integrierte Funktion für eine übersichtlichere Darstellung. Es reicht aus, den Bereich, in dem die Daten der Tabelle enthalten sind, zu markieren. Jetzt klickt man auf die Registerkarte Einfügen und anschließend auf die Schaltfläche Tabelle. Abschließend bestätigt man noch die Nachfrage nach dem Datenbereich mit OK.
Dieser Trick funktioniert mit Microsoft Excel 2007 und 2010.
Bedingte Formatierung optimal nutzen
Das Aussehen von Zellen in Excel lässt sich mit einer Bedingung verknüpfen. Diese umfangreiche Funktion war bislang allerdings umständlich in der Handhabung. Dass es auch anders geht, zeigt die Tabellenkalkulation in den aktuellen Versionen.
Excel 2007 und Excel 2010 bringen eine Vielzahl vorgefertigter Schemata für die bedingte Formatierung mit. Der Anwender kann zum Beispiel automatische Farbskalen verwenden oder im Zellenhintergrund einen Balken anzeigen lassen.
Um die neuen Möglichkeiten zu nutzen, markieren Sie die zu bearbeitenden Zellen. Klicken Sie auf der Registerkarte Start auf das Symbol Bedingte Formatierung. Im nun angezeigten Auswahlmenü stehen die unterschiedlichen Formatierungstypen zur Verfügung. Mit dem Befehl Weitere Regeln können Sie auch eigene Varianten erstellen.
Eingabe auf bestimmte Werte in Zellen beschränken
Die Situation ist bekannt: Da hat man eigentlich ein eindeutiges Excel-Sheet angelegt und die Anwender tragen Daten nach Lust und Laune ein. Eine saubere Datenerfassung stellt die Datenüberprüfung sicher.
Es gehört zu den alltäglichen Problemen, dass zwischen dem Ersteller eines Excel-Sheets und den Anwendern, die sie ausfüllen, nicht immer ein mentales Übereinkommen hinsichtlich der Zellinhalte besteht. Da werden dann beispielsweise alphanumerische Werte eingeben, obwohl eigentlich nur ganze Zahlen gewünscht waren. Dies erschwert so manche Datenerfassung und macht Nacharbeiten erforderlich. Mit der Funktion Datenüberprüfung aus Excel kann man da Missverständnissen vorbeugen.
Markieren Sie hierfür den Bereich, für den die Überprüfen gelten soll. Wechseln Sie dann zu Daten/Datenüberprüfung. Achtung: Wenn die Datei bereits freigegeben ist, lässt sich die Datenüberprüfung nicht mehr einrichten. Im ersten Register Einstellungen können Sie unter Zulassen bestimmen, welche Werte Sie erlauben wollen. In dem Register Eingabemeldung können Sie einen Text eingeben. Diesen bekommt der Anwender angezeigt, wenn er die Zelle anwählt.
Sie können im Register Fehlermeldung wählen, welche Art angezeigt wird. Zur Auswahl stehen: Stopp, Warnung und Information. Da gilt es bei der Wahl die Sensibilität der Anwender zu berücksichtigten. Bei Stopp bekommt der Anwender solange einen Fehler angezeigt, bis er einen korrekten Wert eingibt. Bei Warnung erscheint die Fehlermeldung und der Warnhinweise, der Anwender kann sich jedoch über Fortfahren und Ja darüber hinweg setzen. Information ist die weichste Variante, hier bekommt der Anwender lediglich den Hinweis angezeigt.
Anzahl unterschiedlicher Werte ermitteln
Sie haben eine Liste bekommen und müssen auf die Schnelle ermitteln, wie viele unterschiedliche Werte diese in einem Bereich enthält. Eine kleine Formel erledigt diese Berechnung.
Eine Problemstellung, die immer wieder mal auftaucht: Man muss für einen bestimmten Bereich in Excel ermitteln, wie viele unterschiedliche Werte auftauchen, um ein Projekt weiter abarbeiten zu können. Die Funktion ZÄHLENWENN leistet da hilfreiche Dienste. Verpackt in eine Matrixfunktion, ermittelt sie die Anzahl der unterschiedlichen Werte.
Die Werte befinden sich in unserem Beispiel in dem Bereich Teilnehmer, wir haben den Bereich B2:B13 so benannt. Mit der Formel
{=ZÄHLENWENN(Teilnehmer;Teilnehmer)}
Diese Formel ist als Matrixfunktion einzugeben, denn es soll ja für jeden Wert gezählt werden, wie oft dieser vorkommt. Wenn Sie die Formel mit STRG + UMSCHALTTASTE + ENTER eingeben, fügt Excel automatisch die geschweiften Klammern hinzu und führt dies als Matrixfunktion aus. Dies ist auch für die nachfolgenden Formeln zu beachten.
Das Element Bernhard kommt in unserem Beispiel fünf Mal vor, obige Formel liefert also "5" zurück. Da jeder Teilnehmer ja nur einmal gezählt werden soll, teilt man 1 durch die Anzahl. Wenn man jetzt das Ganze summiert, bekommt man die Anzahl der unterschiedlichen Werte im angegebenen Bereich mit:
{=SUMME(1/ZÄHLENWENN(Teilnehmer;Teilnehmer))}
Wer in der Liste mit leeren Zellen zu rechnen hat, kann diese eventuell noch durch eine WENN-Bedingung ausschließen, da die Formel sonst einen Fehler auswirft. Also etwa so:
{=SUMME(WENN(Teilnehmer=" ";0;1/ZÄHLENWENN(Teilnehmer;Teilnehmer)))}
Werte nach Bedingungen summieren
Die Aufgabenstellung kommt durchaus häufiger vor: Man möchte aus eine Excel-Liste Werte summieren, wenn bestimmte Kriterien erfüllt sind. Dabei kommt häufig die populäre SVERWEIS-Funktion ins Spiel, in unserem Beispiel setzen wir aber auf die Funktion SUMMENPRODUKT.
In unserem Fall verkaufen eine Reihe von Vertriebsmitarbeitern wiederum eine Reihe unterschiedlicher Produkte. Prinzipiell kann jeder Verkäufer jedes Produkt verkaufen. In der Excel-Liste werden die Aufträge chronologisch erfasst: In der ersten Spalte steht der Verkäufer, in der zweiten das Produkt und in der dritten die Stückzahl. Wie wollen am Ende in einer Abfrage wissen, wie viel Stück des Produktes A der Verkäufer X verkauft hat. Sprich, es wird folgende Aufgabe gestellt: Wenn die Bedingungen in Spalte A und B erfüllt sind, summiere die entsprechenden Werte der Spalte C.
Die Kombination aus Verkäufer und Produkt, die abgefragt werden soll, geben wir in gesonderte Zellen ein, unter denen das Ergebnis gelistet wird. Prinzipiell funktioniert die Funktion wie folgt:
=SUMMENPRODUKT(Matrix1; Matrix2;Matrix3….)
In unserem kleinen Beispiel stehen die Namen der Verkäufer in dem Bereich A2:A11, die verkauften Produkte in B2:B11 und die jeweiligen Stückzahlen in C2:C11. In den Zellen F1 (Verkäufer) und F2 (Produkt) tragen wir die Kriterien ein, in F3 folgt die Formel mit unserem Ergebnis. Dementsprechend sieht die Formel in unserem Beispiel wie folgt aus:
=SUMMENPRODUKT((A2:A11=F1)*(B2:B11=F2)*(C2:C11))
Der Verkäufer Bernhard hat in Zeile 2 von Produkt A eine Stückzahl von 5 verkauft, und in Zeile 11 stehen bei Bernhard eine Stückzahl von 4 für das Produkt A. Geben wir nun in unsere Zellen F1 entsprechend Bernhard und in F2 A ein, so lautet das Ergebnis in F3 9.
Im nächsten Schritt möchten wir ein weiteres Kriterium erfüllt wissen: Die Produkte können jeweils aus unterschiedlichen Lagerhäusern kommen, zwei verschiedene existieren, in der Tabelle stehen diese Werte jetzt in dem Bereich C2:C11, die Stückzahl rutscht eine Spalte weiter. Die Abfrage der Kriterien erfolgt jetzt in den Zellen G1, G2 und G3. Damit sieht die Formel wie folgt aus:
=SUMMENPRODUKT((A2:A11=G1)*(B2:B11=G2)*(C2:C11=G3)*(D2:D11))
Es werden nun nur noch die Werte summiert, wenn alle drei Kriterien erfüllt sind.
Wie so oft in Excel ist dies natürlich nicht der einzige Weg, der zum entsprechenden Ziel führt. Natürlich sind Lösungen mithilfe von SVERWEIS oder INDEX ebenso machbar, und selbstverständlich könnte man die Abfrage der gesuchten Kriterien über Auswahllisten sicherer gestalten. (mje)