Auflistung aller Excel-Tipps und -Tricks der ControllerSpielwiese
Sie können Sie somit zum Beispiel komplett ausdrucken und nach und nach ausprobieren.
| Tipp 30: Änderungen im neuen Excel 2007 |
Die neue Office Version und das neue Excel 2007 kommen auf den ersten Blick ganz anders daher. Für den Umsteiger beschreiben wir einige wichtige Änderungen, um die Neuerungen schneller zu erschließen - wie gesagt, einige für den Einstieg in die neue Version ...! Sollten Sie weitere Hinweise ergänzen wollen, schreiben Sie uns unter excel@controllerspielwiese.de und wir fügen Ihre Ergänzungen hier an! zum Dokument |
| Tipp 48: Unterjährig Durchschnittswerte korrekt berechnen |
Durchschnittswerte sind grundsätzlich leicht zu errechnen, da lediglich die Gesamtsumme durch die Anzahl der einzelnen Werte geteilt werden muss. Was jedoch, wenn der Datenbereich ständig eine unterschiedliche Anzahl von Werten aufweist? Oft kommt es z.B. vor, dass man unterjährig anhand von mehreren Monatswerten den Durchschnitt der bereits vorhandenen Monate in einem vorgegebenen Listenbereich errechnen möchte. Da die Anzahl der Werte hierbei jeden Monat zunimmt muss auch in der Durchschnittsberechnung dieser Wert flexibilisiert werden. Ansicht Beispiel Als Beispiel sei eine Tabelle definiert, die von B2 bis B13 Monatswerte enthält. Die folgende Formel prüft mit WENN zunächst, ob in B2 überhaupt ein Anfangswert vorliegt (um keinen Fehlerwert zu erzeugen) und berechnet dann die SUMME aller vorliegenden Werte (immer über alle 12 Zellen). Weiterhin wird dann anhand der Funktion ZÄHLENWENN die Anzahl der Werte ermittelt, die größer Null sind (somit die Anzahl der vorhandenen Einträge). Summe durch Anzahl Werte ergibt dann den Durchschnitt. =WENN(B2<>0;SUMME(B2:B13)/ZÄHLENWENN(B2:B13;">0");0) Beispiel: Excel Datei 97-2003 Da der allgemein bekannte Durchschnitt in der Statistik der arithmetische Mittelwert ist, kann auch die Funktion =MITTELWERT(B2:B13) genutzt werden. Beides führt zum gleichen Ergebnis. |
| Tipp 61: Bereiche dynamisch berechnen |
Um im laufenden Jahr eine monatliche (z.B. Umsatz-) Übersicht mit zugehörigen Planwerten und ggfs. noch einem dynamischen Diagramm zu erstellen, gilt es, am Ende eines jeden Monats den auszuwertenden Bereich um eine Zeile (den aktuellen Monat) zu erweitern. Dies kann man natürlich manuell tun und die entsprechenden Formeln von Hand ändern. Besser ist es jedoch, wenn sich die Auswertungen dynamisch an die nach und nach ausgefüllten Zellen anpassen. Excel kann automatisch erkennen, bis wohin ein Bereich aktuell ausgefüllt ist. Für diese Zwecke (und natürlich auch für andere und weitaus kompliziertere) gibt es die Funktion BEREICH.VERSCHIEBEN, mit der man Bereiche auf eine eigene Weise definieren kann. Die Syntax lautet wie folgt: =BEREICH.VERSCHIEBEN(AusgangsBereich;ZeilenVerschub;SpaltenVerschub;HöheZeilen;BreiteSpalten) Beispielhaft soll eine Umsatzstatistik dienen, welche wie folgt aufgebaut ist: In Spalte A stehen einer Überschrift folgend die Monate Jan-Dez.; in Spalte B die Plan-Umsatzwerte (B2 bis B13) und deren Summe in B14; in Spalte C die Ist-Umsätze bis einschließlich Monat März (C2 bis C4) und die Summe C2 bis C13 in Spalte C14. Nun sind die beiden Summen nicht vergleichbar, da es 12 Plan- und nur drei Istwerte sind. Mit folgender Formel können die den Istwerten zugehörigen Planwerte aufsummiert werden: =SUMME(BEREICH.VERSCHIEBEN(B2;0;0;ANZAHL(C2:C13);1)) Mit ANZAHL wird die Anzahl der vorhandenen Werte ausgerechnet und damit der Bereich von B2 ausgehend definiert. Die Summenformel summiert dessen Inhalte. Diese Formel kann in beliebiger Zelle stehen, also auch die Summenformel in B14 ersetzen. Eine Beispieldatei finden Sie hier: Bereich.Verschieben |
| Tipp 62: Bedingte Summierung über mehrere Suchkriterien |
Mit der Funktion SUMMEWENN kann man schnell eine Datenbank nach einem Kriterium durchsuchen und dann die Summe der jeweiligen Einzelwerte ausgeben. Möchte man jedoch nach mehreren Kriterien in einer Datenbank suchen und deren Gesamtsumme ausrechnen, wären hierfür schon mehrere Summewenn-Funktionen notwendig bzw. möglich. Mit der Funktion DBSUMME können jedoch in einer Formel gleich mehrere Bedingungen mitgegeben werden. Die Syntax der Funktion lautet: =DBSUMME(Datenbank;Feld;Suchkriterien) "Datenbank" ist der Zellbereich, der die Datentabelle darstellt; die erste Zeile muss die Spaltenüberschriften enthalten. "Feld" ist die Überschrift der Spalte, welche durchsucht werden soll "Suchkriterien" ist ein Zellbereich, der die Bedingungen für die Summierung festlegt; die erste Zeile muss die Spaltenüberschrift enthalten. Ein einfaches Beispiel finden Sie hier: DBSUMME.XLS Im Beispiel werden in Zelle C14 aus der Datenbank A1:C10 diejenigen Umsätze aus Spalte C addiert, deren Suchkriterien (=Verkaufsgebiet) in dem Zellbereich F1:F3 definiert sind. |
| Tipp 84: Summierung bei mehreren Suchkriterien durchführen |
Gibt es für die Summierung von bestimmten Werten mehrere Suchkriterien in verschiedenen Spalten, so kommt man mit der einfachen Funktion SUMMEWENN nicht weiter, denn SUMMEWENN durchsucht nur eine Spalte nach einem Suchkriterium. Wenn z.B. alle Zellen summiert werden sollen, bei denen in der 1. Spalte ein bestimmtes Suchkriterium (z.B. Kunde) steht und zusätzlich in der 2. Spalte ein weiteres (z.B. Produkt), schafft SUMMEWENN dies nicht, genauso wie es nicht mehrere Spalten aufsummieren kann. Abhilfe schafft eine Arrayformel oder ab Excel 2007 auch schon mal SUMMEWENNS. Fangen wir doch mal einfach mit der Funktion SUMMEWENNS an: Angenommen wir haben in einer Tabelle in Spalte A die Kunden und in B die Produkte. Aufzusummieren sind die Umsätze in Spalte C. Wollen wir nun für einen Kunden A die Summe aller gekauften Produkte B haben, so lautet die Formel: =SUMMEWENNS(C2:C7;A2:A7;"A";B2:B7;"B") Im Beispiel ist der Umsatzwertebereich in C2:C7 (Anders als bei SUMMEWENN steht dies Kriterium am Anfang der Formel). Aus den Kunden in A2:A7 soll Kunde A gesucht und dann zusätzlich in B2:B7 nach Produkt B und die Summe der Umsätze bei dieser Kombination ausgegeben werden. Dies lässt sich auch um weitere Kriterien ergänzen, es sind bis zu 127 Paare zulässig ... wer's braucht ... Mit einer Arrayformel sieht das Ganze dann so aus: {=SUMME(WENN((A:A="A")*(B:B="B");C:C))} Hier bedienen wir uns "nur" SUMME und WENN, allerdings innerhalb der Matrix ... Ein Beispiel und Infos zur Erstellung der Arrayformel/Matrixformel finden Sie in unserer Beispieldatei. |
| Tipp 87: Durchmesser-Zeichen (Ø) mit Tastenkombination erzeugen |
Um das Durchmesser-Zeichen in einer Zelle anzuzeigen kann man sich der Windows-Zeichentabele bedienen und dies dort suchen, kopieren und einfügen. Schneller geht es, wenn man angeleht an Tipp 86 mit einer Tastenkombination arbeitet. Vorausgesetzt, man kann sich Alt-0216 merken ... also Alt-Taste gedrückt halten und auf der Zifferntastatur (wichtig, geht nur dort!) die Ziffernreihenfolge 0216 eintippen. Sobald man die Alt-Taste wieder losläßt, erscheint das Ø-Zeichen. Die Tastenkombinationen aller Sonderzeichen, welche über die deutsche Tastatur erreichbar sind, finden Sie auf der folgenden WebSite in sinnvollen Gruppen gegliedert: http://typefacts.com/tastaturkuerzel |
| Tipp 109: Projektfortschritte mit Harvey Balls dynamisch farblich visualisieren |
In Tipp 108 haben wir gezeigt, wie Sie Harvey Balls, die kreisförmigen Ideogramme mit unterschiedlicher Füllung, auch farblich darstellen können (  ). Dies war eine einfache statische Variante für schnelles, kurzes und einmaliges Reporting, bei der sich die Balls nicht dynamisch mit den zugrundeliegenden Werten ändern. Sie können Ihre Auswertungen natürlich auch mit farbigen Balls dynamisieren. Anhand eines Beispiels mit sechs Projekten wollen wir Ihnen zeigen, wie das z.B. möglich ist: Wir benötigen hierzu die Funktion SVERWEIS, um auf die Balls über eine Hilfsspalte zuzugreifen und die Bedingte Formatierung, um die Balls gemäß einer definierten Abstufung über eine zweite Hilfsspalte farblich zu gestalten. Zunächst müssen die benötigten Symbole aus einem Zeichensatz, wie z.B. der Schriftart Arial Unicode MS, in Excel eingefügt werden (s. hierzu ggfs. auch Tipp 108). In unserem Beispiel haben wir diese in I7:I12 abgelegt und dazu in H7:H12 prozentuale Grenzwerte definiert. Dann müssen die Zellen F7:F12 markiert und die folgende Funktion in alle Zellen eingegeben werden: =SVERWEIS(E7;$H$7:$I$12;2) Formatieren Sie die Zellen F7:F12 mit dem gleichen Zeichensatz (Arial Unicode MS) und erhöhen die Schriftgröße auf einen für Ihren Report gut lesbaren Wert. Der SVERWEIS sucht den Prozentsatz aus E7 in der Matrix und gibt den Wert aus der zweiten Spalte aus. Jetzt werden bereits Harvey Balls angezeigt, welche noch gemäß der Definition in K7:L12 gefärbt werden müssen. Dies wird mit der Bedingten Formatierung für die Zellen F7:F12 erzeugt. Im Beispiel wird mit nur drei Regeln gearbeitet. Der Zellbereich F7:F12 wird zuvor komplett mit Schriftfarbe Rot formatiert, wodurch bereits zwei Fälle definiert sind. Weiter mit: Markieren, Bedingte Formatierung aufrufen, Neue Regel erstellen. Mit der Regel =$E7=$K$7, dem Format "Schwarze Schrift" und der Anwendung auf =$F$7:$F$12 wird begonnen, gefolgt von =$E7>=$K$11, dem Format "Grüne Schrift" und der Anwendung auf =$F$7:$F$12 sowie der Regel =$E7=$K$10, dem Format "Orange Schrift" und der Anwendung auf =$F$7:$F$12. Die Reihenfolge der zweiten und dritten Regel ist wichtig für die Abarbeitung in Excel, um korrekte Ergebnisse zu erzielen. Das Beispiel können Sie hier downloaden. |
| Tipp 112: Nettoarbeitstage bei unterschiedlichen Wochenarbeitszeiten |
Wie Sie Nettoarbeitstage ohne Wochenenden und Feiertage berechnen, hatten wir schon in unserem Tipp 110 gezeigt. Oft kommt es aber vor, dass man nicht alle Arbeitstage der Arbeitswoche für z.B. einen Projektplan benötigt, sondern nur die Tage an denen ein Teilzeitarbeiter auch wirklich im Hause ist oder an denen ein Projektmitarbeiter überhaupt nur fürs Projekt zur Verfügung steht. Dies ist möglich mit der Funktion NETTOARBEITSTAGE.INTL Sie gibt die Anzahl der vollen Arbeitstage zwischen zwei Datumsangaben zurück. Dabei werden Parameter verwendet, um anzugeben, welche und wie viele Tage auf Wochenenden fallen. Wochenenden und Tage, die als freie Tage angegeben sind, werden nicht zu den verfügbaren Arbeitstagen gezählt. Die Syntax lautet wie folgt: NETTOARBEITSTAGE.INTL(Ausgangsdatum;Enddatum;[Wochenende];[Freie_Tage]) Ausgangs- und Enddatum sind dabei zwingend erforderlich, um die dazwischenliegenden Arbeitstage zu berechnen. Das Ausgangsdatum kann vor oder nach dem Enddatum liegen und auch mit diesem identisch sein. Wochenende ist optional und gibt die Tage der Woche an, die als Wochenendtage behandelt werden und damit nicht zur Anzahl der vollen Arbeitstage zwischen Ausgangsdatum und Enddatum zählen sollen. Es gibt eine Vielezahl von vordefinierten Wochenendnummer für die verschiedenen Wochentage und Kombinationen. Z.B. definiert "1" oder nicht festgelegt (leer) Samstag und Sonntag als Wochenende, "11" nur den Sonntag und "17" nur den Samstag. Für das Wochenende kann aber auch ein Zeichenfolgewert aus sieben Zeichen angegeben werden, wobei jedes Zeichen für einen Wochentag, beginnend mit Montag, steht. Die "1" bezeichnet einen arbeitsfreien Tag, "0" einen Arbeitstag. Es sind nur die Zeichen "1" und "0" zulässig. Die Zeichenfolge "0000011" gibt beispielsweise ein Wochenende an, das heißt, Samstag und Sonntag. Steht ein Projektmitarbeiter also nicht am Wochenende und nicht am Dienstag und Mittwoch zur Verfügung, kann mit der Zeichenfolge "0110011" gearbeitet werden. Weiterhin können optional mit Freie_Tage auch einzelne oder mehrere Datumsangaben, welche arbeitsfreie Tage bezeichnen, definiert werden. Freie_Tage kann z.B. als Bereich von Zellen mit den entsprechenden Datumsangaben angegeben werden. Die Reihenfolge der Datumsangaben ist beliebig. |
| Tipp 120: Summierung bei mehreren Suchkriterien in verschiedenen Spalten |
Für die Summierung bei mehreren Suchkriterien in verschiedenen Spalten gibt es mehrere Möglichkeiten. In Tipp 84 hatten wir Ihnen schon ein Beispiel mit SUMMEWENNS und alternativ auch mit einer Array-Formel aufgezeigt. In diesem Tipp stellen wir Ihnen eine weitere Möglichkeit mit der Funktion SUMMENPRODUKT vor. Konkret an einem Beispiel, wie Sie außerhalb einer Datentabelle z.B. den Teilabsatz und Teilumsatz für ein bestimmtes Produkt in einer definierten Variante ermitteln können. Die Funktion SUMMENPRODUKT gibt die Summe der Produkte entsprechender Bereiche oder Arrays zurück. Im Standard ist das die Multiplikation, aber Addition, Subtraktion und Division sind ebenso möglich (s.u.). Die Syntax lautet: = SUMMENPRODUKT(Matrix1; [Matrix2]; [array3];...) In unserem Beispiel möchten wir aus einer Umsatzdatenbank die Teilumsätze von Produkt "A" in der Variante "Groß" ermitteln:  Absatz, Einzelpreis und Umsatz sind in den obigen Spalten aufgeführt. In einem separaten Bereich kann die Berechnung erfolgen (Werte in H10 (A) und I10 (Groß)):  Die Formel für den Absatz in J10 lautet: =SUMMENPRODUKT((B7:B16=H10)*(C7:C16=I10);(D7:D16)) Dabei werden aus den Bereichen B7:B16 UND C7:C16 nur die Werte selektiert, für welche die Bedingung =H10/Produkt "A" bzw. =I10/Variante "Groß" erfüllt ist. Für diese wird dann aus dem Bereich D7:D16 der Wert ermittelt und die Summe gebildet. Die Formel für den Umsatz in K10 lautet: =SUMMENPRODUKT((B7:B16=H10)*(C7:C16=I10);(D7:D16);(E7:E16)) Statt dem Semikolon (s. Syntax) zwischen den beiden letzten Matrix-Bereichen kann auch mit dem gewünschten Operator (* für Multiplikation) gearbeitet werden. Andere arithmetische Operationen führen Sie aus, indem Sie die Semikolons, welche die Arrayargumente voneinander trennen, durch die gewünschten arithmetischen Operatoren (*,/, +,-) trennen. Nachdem alle arithmetischen Vorgänge ausgeführt wurden, werden die Ergebnisse auch hierbei wie gewohnt summiert. Die als Argumente angegebenen Arrays müssen bezüglich der Zeilen- und Spaltenanzahl immer identisch sein. Ist dies nicht der Fall, gibt SUMMENPRODUKT den Fehlerwert #WERT! zurück. Gleiches ist übrigens auch mit SUMMEWENNS möglich: =SUMMEWENNS(F7:F16;B7:B16;H10;C7:C16;I10) führt zu dem gleichen Ergebnis. In unserer Beispieldatei können Sie weiterhin nachvollziehen, wie die Funktion SUMMENPRODUKT in Ihrer Grundfunktion zu dem gleichen Ergebnis wie die Funktion SUMME führt. Das Beispiel gibt's hier im Download. In unserem nächsten Tipp (November-Newsletter) lesen Sie dann, wie Sie SUMMENPRODUKT verwenden, um z.B. zu berechnen, wie viele Kunden wie oft in einem bestimmten Zeitraum gekauft haben. Ganz nützlich, zu wissen, ob Sie in erster Linie Kunden haben, die nur einmal kaufen, oder ob Sie viele Stammkunden mit Mehrfachkäufen haben. |
| Tipp 121: Anzahl Käufe je Kunde mit SUMMENPRODUKT und ZÄHLENWENN ermitteln |
In einem Monat kaufen einige Kunden mehrfach oder haben mehrere Umsatzpositionen bei deren Käufen in Ihrer Umsatzdatenbank erzeugt. Mit den Funktionen SUMMENPRODUKT und ZÄHLENWENN können Sie die Anzahl von Käufen je Kunde auswerten und somit den Anteil der Wiederkäufer und Stammkunden an den Gesamtkunden ermitteln. Zur Veranschaulichung folgendes Beispiel mit 5 verschiedenen Kunden und insgesamt 10 Käufen:  Die Anzahl der Käufe läßt sich mit ZÄHLENWENN leicht ermitteln. Funktionsweise der Funktion ZÄHLENWENN lesen Sie in Tipp 43 und 48. Im Beispiel kann man bei numerischen Kundennummern den Bereich C7:C16 nach Werten >1 durchsuchen und somit die Anzahl ausgeben: =ZÄHLENWENN(C7:C16;">1") Weiter geht es mit der Berechnung der Anzahl verschiedener Kunden mit einer Kombi aus SUMMENPRODUKT und ZÄHLENWENN: =SUMMENPRODUKT(1/ZÄHLENWENN(C7:C16;C7:C16)) Zuerst ermittelt die Funktion ZÄHLENWENN für jeden Eintrag in Spalte C wie oft der Kunde vorkommt. Um festzustellen, wie viel unterschiedliche Kunden vorkommen, wird pro Zeile ein Gewichtungsfaktor angegeben. Die Summe aller Gewichtungsfaktoren pro Kunde ergibt immer 1, weil der Kunde ja auch nur einmal gezählt werden darf. Es werden also für alle Zeilen aus Spalte C die anteiligen Werte ermittelt. Das bedeutet, wenn ein Kunde z.B. dreimal in der Tabelle gefunden wird, wird jedes Mal 1/3 gezählt und 3 x 1/3 sind wieder 1. Die Funktion SUMMENPRODUKT addiert die zuvor ermittelten 10 Werte schließlich auf. In einer kleinen Auswertung kann man nun wie folgt darstellen:  Es hat also 1 Kunde 1x gekauft, 3 kauften 2x und 1 Kunde 3x. Die Formel (hier in J19) zur Berechnung der Kunden für 3 Käufe greift auf die "3" in I19 zu und lautet wie folgt: =SUMMENPRODUKT(N(ZÄHLENWENN(C7:C16;C7:C16)=I19))/I19 ohne den Zellbezug auf I19 zur Veranschaulichung mit direkter Werteingabe auch: =SUMMENPRODUKT(N(ZÄHLENWENN(C7:C16;C7:C16)=3))/3 ZÄHLENWENN ermittelt für jeden Eintrag in der Spalte C, wie oft der Kunde vorkommt. Dann wird für jede Zeile geprüft, ob die Anzahl die Zahl 3 ist. Die Funktion N() wandelt den Wahrheitswert "Ja" in die Zahl 1 und "Nein" in die Zahl 0 um. SUMMENPRODUKT addiert die Werte der Spalte auf. Das Ergebnis bis hier ist 3. Zuletzt das Ergebnis noch durch die angegebene Anzahl der Käufe dividiert (im Beispiel 3 Käufe) und das Gesamtergebnis lautet: 1 Kunde hat 3x gekauft. Sind in dem Datenbestand auch leere Zeilen, wird der Fehler #DIV/0! ausgegeben. Hier eine Möglichkeit für das obere Beispiel zur Ermittlung der Anzahl Kunden, um den Fehler abzufangen: =SUMMENPRODUKT((C7:C17<>"")/ZÄHLENWENN(C7:C17;C7:C17&"")) Das Beispiel gibt's hier im Download. Ähnliche Fragestellungen, die so bearbeitet werden können, sind z.B. auch die Anzahl verschiedener abgesetzter Produkte je Kunde oder die Anzahl bei Lieferanten bezogener Artikel. |
| Tipp 123: Mit dynamischen Tabellen arbeiten - Dynamische Tabellen erstellen |
Aus einer statischen Tabelle eine dynamische Tabelle zu erzeugen, ist recht einfach und die Arbeit mit dynamischen Tabellen spart Zeit und bietet zusätzliche Möglichkeiten. So können Sie z.B. schon beim Aufbau der Tabelle Zeit sparen, da Excel in neuen Spalten Formeln automatisch ergänzt, Summen berechnet und Filter setzt. Sie können mit einem Klick aus Schnell-Formatvorlagen wählen, leicht Duplikate aus der Tabelle entfernen oder auch die Tabelle als Ganzes exportieren. Bei Erweiterungen aktualisieren sich auf die dynamische Tabelle beziehende Verknüpfungen automatisch. Für besondere Anforderungen stehen zusätzlich weitere Möglichkeiten zur Verfügung, wie z.B. der automatisierte Zugriff mittels (mehrfacher) Dropdownlisten. Mit diesen Möglichkeiten befassen wir uns in den kommenden Tipps. In unserem heutigen, ersten Tipp zu den dynamischen Tabellen behandeln wir das Erstellen der Tabellen, das Erweitern von Daten, das Formatieren der Tabelle sowie die Rückwandlung in eine statische Tabelle. Als Beispiel nehmen wir folgende, noch unvollständige Tabelle mit Artikeln und Preisen zur Hand:  Um aus dieser Tabelle eine dynamische Tabelle zu erzeugen und die beschriebenen Erleichterungen zu erfahren, klicken wir in ein beliebiges Feld in der Tabelle und wählen im Register „Start“ in dem Bereich „Formatvorlagen“ die Option „Als Tabelle formatieren“ (Alternativ auch mit Tastenkombination Strg+T erreichbar). Excel selektiert nun Ihren Tabellenbereich und fragt Sie, ob der Bereich Überschriften enthält. Wenn der Bereich vollständig selektiert ist bestätigen Sie mit OK. Das Ergebnis sieht wie folgt aus:  Excel hat nun aus dem statischen Bereich eine dynamische Tabelle erzeugt, einen (Standard-)Formatvorschlag erstellt und Filter gesetzt. Weiterhin finden Sie jetzt das neue Register „Entwurf“ für die Auswahl der verschiedenen Tabellentools, welches auch künftig bei Selektion der dynamischen Tabelle zur Verfügung steht: Klicken Sie neben die Tabelle, ist das Register nicht mehr vorhanden, klicken Sie wieder in die Tabelle, wird es eingeblendet. Dort können Sie nun auch eine andere Formatvorlage auswählen. Die Formate können aber auch wie gewohnt in dem Bereich „Schriftart“ verändert werden. Die erste Formatvorlage in der Liste erhält Ihre bisherigen Formatierungen. Zum Erweitern der noch fehlenden Steuerbeträge und dem Bruttopreis geben Sie nun jeweils in die oberste Zeile die entsprechenden Formeln ein und Excel fügt dies automatisch für alle Zeilen der dynamischen Tabelle ein. Allerdings zeigt Excel die Formeln in dynamischen Tabellen anders an: Aus Ihrer Eingabe =D2*0,19 macht Excel =[Preis netto]*0,19 und aus =D2+E2 wird =[Preis netto]+[MwSt 19%].  Wollen Sie aus der dynamischen Tabelle wieder eine statische Tabelle machen, wählen Sie in dem Register „Entwurf“ den Bereich „Tools“ und dort „In Bereich konvertieren“. Bestätigen Sie die Sicherheitsabfrage mit „Ja“ und alle dynamischen Funktionen sind verschwunden. Lediglich das Format der gewählten Formatvorlage bleibt erhalten.  Das Beispiel gibt's hier im Download. |
| Tipp 129: Plan-Ist-Abweichungen visualisieren mit Balkengrafiken |
In Tipp 111 haben wir bereits gezeigt, wie mit einfachen Grafikelementen Planabweichungen visualisiert werden können. In unserem aktuellen Tipp wollen wir zeigen, wie die bereits mit der Funktion „Wiederholen“ erzeugten Balkengrafiken noch etwas hübscher dargestellt und auf die Spaltenbreite angepasst werden können. Das Ergebnis aus Tipp 111 sah wie folgt aus:  Heute wollen wir in etwa da hin:  Damals hatten wir in H8 zur Darstellung der negativen Abweichung: =WENN(F8<0;WIEDERHOLEN("I";-F8);WENN(F8=0;"";"")) Die Abstände zwischen den „I“ und die Anpassbarkeit möchten wir heute verbessern. Dabei bedienen wir uns dem Symbolzeichensatz und holen uns zur Darstellung der Balken ein „█“. Über das Register „Einfügen“ im Bereich „Symbole“ auf „Ώ Symbol“ und in dem sich öffnenden Fenster das Register „Symbole“ wählen. Dort finden Sie z.B. bei der Schriftart Arial im sogenannten Subset die Blockgrafikzeichen. Sie können auch einfach die Liste der Zeichen durchscrollen und das Zeichen mit dem Code 2588 suchen: =WENN(F8<0;WIEDERHOLEN("█";-F8);WENN(F8=0;"";"")) Um jetzt noch mehr Flexibilität zu erreichen, wenn die Balken in ihrer Länge sehr unterschiedlich sind, besteht die Möglichkeit mit einem Darstellungsfaktor zu arbeiten. Wir setzen hierzu in eine Hilfszelle M5 zunächst mal den Wert 10 ein: =WENN(F8<0; WIEDERHOLEN("█";WENN(ABS(F8/$M$5)<=(100/$M$5);ABS(F8/$M$5);100/$M$5));WENN(F8=0;"";"")) Hier sagen wir der Wiederholen-Funktion mit der Wenn-Abfrage, dass ab einem Abweichungswert > 100 und definierten Skalierung auf ein 10tel die Spaltenbreite langsam eng wird. Statt mit negativen Werten zu rechnen, nutzen wir weiterhin mit der Funktion ABS nur noch die absoluten Werte für die Berechnung. Wollen wir dann noch die Abweichungswerte balkennah darstellen, gelingt das mit Hilfe der Text-Funktion sowie einer Verkettung vom Wert, einem Leerzeichen, dem Euro-Symbol sowie der Wiederholen-Funktion durch den logischen Operator UND (&): =WENN(F8<0;TEXT(F8;"+0 €;-0 €")&" "&WIEDERHOLEN("█";WENN(ABS(F8/$M$5)<=(100/$M$5);ABS(F8/$M$5);100/$M$5));WENN(F8=0;"";"")) Die Farbe kommt über die Textfarbe und die Ausrichtung erfolgt analog zu der Methode in Tipp 111. Eine Excel-Vorlage hierzu finden Sie in unserer Rubrik Berichtsvorlagen |
| Tipp 142: Ganze Zeilen in Abhängigkeit von Werten formatieren und farbig hervorheben |
Wenn Sie z.B. eine Liste regelmäßig mit Daten pflegen, ob der Kunde die Forderung gezahlt hat, oder ob der Projektstatus erreicht ist, dann kann es visuell vorteilhaft sein, die ganze Zeile farbig hervorzuheben. Somit lassen sich die noch zu bearbeitenden Datensätze schnell erblicken. Um die ganze Zeile automatisch in Abhängigkeit von Ihren Eingaben formatieren zu können, nutzen Sie die Bedingte Formatierung. Hiermit können Sie definieren, wie bestimmte Zellen basierend auf Ihren Eingaben gestaltet werden. Das geht nicht nur für die Zelle selbst, in die Sie einen Wert eingeben, sondern auch für ganze Zeilen oder Spalten. Um es an einem Beispiel erklären zu können, nehmen wir einen Ausschnitt aus unserem Liquiditätsplanungs-Tool:  Die hier eingetragenen Forderungen wurden noch nicht bezahlt. Erfolgt der Geldeingang, wird als Status „bez“ eingetragen und alle Werte in der Zeile sollen entsprechend kenntlich gemacht werden. In unserem Beispiel werden alle Einträge der gleichen Zeile anhand einer bedingten Formatierung bereits durchgestrichen. Jetzt sollen sie zusätzlich mithilfe einer weiteren bedingten Formatierung farbig hervorgehoben werden. Setzen Sie den Cursor in D6 und los geht es:  Im Register „Start“ befindet sich in der Gruppe „Formatvorlagen“ die „Bedingte Formatierung“. Mit Klick auf den kleinen Pfeil können Sie die Menüstruktur öffnen, innerhalb der es bereits einige vordefinierte Möglichkeiten für bedingte Formatierungen gibt sowie die Möglichkeiten neue Regeln zu erstellen oder bestehende zu verwalten/zu ändern. Für unser Beispiel nutzen wir die Möglichkeit „Regeln zum Hervorheben von Zellen“ und weiter von „Textinhalt...“.  In dem sich öffnenden Fenster kann nun definiert werden, bei welchem Textinhalt und mit welchen Formaten die Zellen formatiert werden sollen. Wir geben links „bez“ ein, wählen rechts mit „benutzerdefiniertem Format...“ aus und zusätzlich in dem sich dann öffnenden, üblichen Fenster für Zellformatierungen im Register „Ausfüllen“ ein helles Grün aus. Mit der Bestätigung auf OK wird unsere neue Regel angelegt. Diese können wir jetzt weiter anpassen, indem wir wiederholt auf „Bedingte Formatierung“ klicken und in dem Menü den Punkt „Regeln verwalten...“ wählen. Es öffnet sich der Manager für die Regeln zur bedingten Formatierung. Es besteht hier die Möglichkeit, sich alle Regeln einer Datei, eines Arbeitsblattes oder auch nur der aktuellen Auswahl (aktive Zellen) anzeigen zu lassen.  Sie sehen hier unsere neu erstellte Regel, wählen „Regel bearbeiten...“ und können im nächsten Fenster die „Formatierungsregel bearbeiten“.  Wählen Sie die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kann die Formel eingegeben werden, aufgrund derer die bedingte Formatierung aktiv werden soll. Geben Sie ein: =$D6="bez“ Sie können hier relative und absolute Bezüge definieren. Da wir das Kriterium „bez“ immer in der Spalte D haben, es aber auch in anderen Zeilen abgefragt werden soll, muss die Spalte absolut mit dem $-Zeichen, die Zeile relativ ohne eingegeben werden. Wählen Sie noch über den Schalter „Formatieren“ eine hellgrüne Füllfarbe und bestätigen Sie die Eingaben mit OK.  In dem Manager sehen Sie jetzt schon das definierte Format. Jetzt muss nur noch der Anwendungsbereich von $D$6 in unserem Fall auf $A$6:$F$17 erweitert werden. Nutzen Sie hier nicht die Pfeiltasten während Ihrer Eingaben, das mag Excel gar nicht, sondern klicken Sie bei Bedarf mit der Maus in die Formeln. Bestätigen Sie die Eingaben mit OK. Das Ergebnis sieht jetzt wie folgt aus:  Die verwendete Beispieldatei in Excel gibt's hier zum Download. Die Originaldatei Excel-Liquiditätsplanung 2.0 finden Sie in unseren Berichtsvorlagen. |
| Tipp 143: Berichtsname als Text zusammen mit Datumswert aus einer Zelle in der Überschrift darstellen |
Wie schön ist es, wenn man monatlich möglichst wenig Aufwand mit der Aktualisierung von Berichten hat. Ein weiterer kleiner Vorteil auf diesem Weg kann sein, dass Sie bereits in der Überschrift den Berichtsnamen mit dem aktuellen Tagesdatum oder einer Monats- und Jahresangabe aus einer Zelle verknüpfen. Da man in Excel in einer Zelle jedoch nur ein Zellformat definieren kann, wird das Datum bei Verknüpfungen nicht in dem gewünschten Format angezeigt. Mit einem kleinen Trick lassen sich Datumsangaben und Texte aber in einer Formel darstellen. Und dann ist vieles möglich ... Zellinhalte und Texte verknüpfen Sie in Excel mit dem &-Symbol (Kaufmännisches Und-Zeichen). Verknüpfen Sie in einer Zelle jedoch einen Text mit einer Datumsangabe stellt Excel das Datum nicht in einem Datumsformat dar, sondern als die fortlaufende Zahl, mit der es intern rechnet. Das würde dann aussehen, wie z.B. "Berichtsname 44621". Wandelt man die Datumsangabe jedoch in einen Text um, lassen sich die zwei Texte dann wunderbar kombinieren. Hierzu dient die Funktion TEXT, mit deren Hilfe Sie durch das Anwenden einer Formatierung mithilfe von Formatcodes die Anzeige des Zahlwertes ändern. Die Syntax lautet: =TEXT(Wert, den Sie formatieren möchten;"Formatcode, der angewendet werden soll") Diese Text-Funktion wird dann für unsere Zwecke mit dem Berichtsnamen verknüpft. Eine Lösung könnte wie folgt aussehen: ="Berichtsname "&TEXT(C6;"MMMM JJJJ") Hierbei wird unterstellt, das in der Zelle C6 ein Datum steht, dessen Format dort beliebig ist. Das Datum ist der Wert, den Sie formatieren möchten. Der Formatcode, der angewendet werden soll ist "MMMM JJJJ" - also Monat und Jahr ausgeschrieben; dazwischen eine Leerstelle! Steht in Zelle C6 z.B. der 01.03.2099 ergibt die Formel: Berichtsname März 2099 Alternativ können Sie natürlich den Berichtsnamen auch indirekt aus einer Zelle oder dem Blattregister (s. Tipp 39) auslesen. Mit den möglichen Formatcodes können Sie z.B. auch das Tagesdatum verknüpfen: =TEXT(HEUTE();"TT.MM.JJ"), oder nur den aktuellen Wochentag: =TEXT(HEUTE();"TTTT"). Auch Zeitwerte sind möglich, falls es mal um Sekunden geht ... alle verfügbaren Formatcodes finden Sie unter "Zellen formatieren" im Register "Zahlen". |
| Tipp 146: Doppelte Werte in Tabellen finden und farbig markieren |
Oft möchte man in Excel-Listen bestimmte mehrfach vorkommende Daten finden und bearbeiten. Weis man welche das sind, könnte man danach filtern. Oft sind die Datenbestände aber auch zu groß oder unklar, sodass sich ein schneller Überblick über alle mehrfach vorkommenden Werte anbietet. Excel bietet eine ganz einfache Möglichkeit, doppelte Werte in einer Liste automatisch anzuzeigen und zu formatieren. Mit Hilfe der Bedingten Formatierung können Sie alle doppelten Werte finden. In unserem Beispiel nehmen wir einen Ausschnitt von dem Forderungsbestand aus unserem Liquiditätsplanungs-Tool:  Markieren Sie zunächst Ihre Liste bzw. die Zellen, in denen Sie nach doppelten Werten suchen möchten. In unserm Beispiel wären das die Kunden in Spalte E. Tipp: Es ist nicht notwendig, die Zellen einzeln oder als Block von … bis … zu markieren. Sie können die ganze Spalte bzw. wenn gewünscht auch gleich mehrere Spalten über die Spaltenköpfe selektieren.  Im Register „Start“ befindet sich in der Gruppe „Formatvorlagen“ die „Bedingte Formatierung“. Mit Klick auf das Symbol können Sie die Menüstruktur der Bedingten Formatierung öffnen, innerhalb der es bereits einige vordefinierte Möglichkeiten für bedingte Formatierungen gibt sowie die Möglichkeiten neue Regeln zu erstellen oder bestehende zu verwalten/zu ändern. Für unser Beispiel nutzen wir die Möglichkeit „Regeln zum Hervorheben von Zellen“ und klicken weiter auf „Doppelte Werte...“.  In dem sich öffnenden Fenster kann nun definiert werden, mit welcher Füllung die doppelten Werte/Zellen formatiert werden sollen. Wir können rechts statt der vordefinierten hellroten Füllung auch unterschiedliche weitere vordefinierte Farben wählen, oder mit „benutzerdefiniertem Format...“ zusätzlich in dem sich dann öffnenden, üblichen Fenster für Zellformatierungen in den verschiedenen Registern diese Zellen andersartig formatieren. Mit der Bestätigung auf OK wird unsere neue Regel angelegt. Das Ergebnis sieht jetzt wie folgt aus:  Die verwendete Beispieldatei in Excel gibt's hier zum Download. Die Originaldatei Excel-Liquiditätsplanung 2.0 finden Sie in unseren Berichtsvorlagen. |
| Tipp 148: Vermögensgegenstände selektieren, die seit einem bestimmten Datum auf Lager liegen |
Für die Bewertung von Beständen, Forderungen und ähnlichem ist es notwendig, den Datenbestand nach dem Alter des Vermögens zu separieren. So können rechtlich notwendige Abwertungen aufgrund von Überalterung schnell - auch unterjährlich - berechnet und verfolgt werden. Bei großen Datenbeständen können Sie die Datensätze in Excel mit nur wenigen Klicks zunächst markieren und dann durch Filtern separieren und summieren. Um Daten hervorzuheben, die vor oder nach einem bestimmten Datum liegen, hilft die Bedingte Formatierung. Für unser Beispiel nehmen wir einen Ausschnitt von dem Lagerbestand aus unserem ABC-Analyse-Tool, wo wir in der Spalte E das Datum des letzten Wareneingangs interpretieren und Wareneingänge vor 2020 anzeigen wollen:  Markieren Sie zunächst die Zellen mit dem Datum, in denen Sie nach den veralteten Werten suchen möchten. In unserm Beispiel ist das die Spalte E. Tipp: Um alle Werte der Spalte schnell zu markieren, selektieren Sie die oberste Zelle, halten dann die SHIFT- und die Strg-Taste gedrückt und springen mit der „Pfeil-Taste nach unten“ zu dem letzten Wert der Spalte.  Im Register „Start“ befindet sich in der Gruppe „Formatvorlagen“ die „Bedingte Formatierung“. Mit Klick auf das Symbol können Sie die Menüstruktur der Bedingten Formatierung öffnen, innerhalb der es bereits einige vordefinierte Möglichkeiten für bedingte Formatierungen gibt. Auch finden Sie hier die Möglichkeiten neue Regeln zu erstellen oder bestehende zu verwalten/zu ändern. Für unser Beispiel nutzen wir die Möglichkeit „Regeln zum Hervorheben von Zellen“ und klicken weiter auf „Kleiner als ...“.  In dem sich öffnenden Fenster kann nun definiert werden, ab welchem Datum die älteren Werte eingefärbt und mit welcher Füllung diese Zellen formatiert werden sollen. Um zum Beispiel zum Jahresende 2022 die Bestände mit Wareneingang älter als 2 Jahren anzuzeigen, geben wir das Datum 01.01.2020 ein. Wir können rechts statt der vordefinierten hellroten Füllung auch unterschiedliche weitere vordefinierte Farben wählen, oder mit „benutzerdefiniertem Format...“ zusätzlich in dem sich dann öffnenden, üblichen Fenster für Zellformatierungen in den verschiedenen Registern diese Zellen andersartig formatieren. Mit der Bestätigung auf OK wird unsere neue Regel angelegt. Das Ergebnis sieht jetzt wie folgt aus:  Um sich jetzt nur die älteren, farblich gekennzeichneten Werte anzeigen zu lassen, können Sie, wie im obigen Tipp 147 beschrieben nach der Farbe der gekennzeichneten Zellen filtern und eine Summe bzw. ein Teilergebnis aus diesen Werten berechnen. Die Originaldatei Excel-ABC-Analyse-Tool finden Sie in unseren Berichtsvorlagen. |
| Tipp 150: Alle Formeln in einer Tabelle finden und farbig hervorheben |
In komplexen Tabellen mit vielen Formeln ist es oftmals hilfreich, sich Zellen mit Formeln zu visualisieren und somit einen besseren und schnelleren Zugriff auf Berechnungen zu haben. Dafür gibt es einerseits die Möglichkeit, in einer Tabelle zwischen der Ergebnisansicht und der Formelansicht zu wechseln oder anderseits auch mal kurzfristig alle Zellen in denen sich Formeln befinden farblich hervorzuheben. Um zwischen der Ergebnis- und der Formelansicht zu wechseln, klicken Sie auf der Registerkarte „Formeln“ in der Gruppe „Formelüberwachung“ auf die Schaltfläche „Formeln anzeigen“.  Anstatt der Formelergebnisse sehen Sie nun die hinterlegten Formeln und können diese wie gewohnt bearbeiten. Ein erneuter Klick auf die Schaltfläche „Formeln anzeigen“ lässt die Formeln wieder verschwinden und es werden wieder die Ergebnisse angezeigt. Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Stundensatzberechnung. Eine weitere Möglichkeit, alle Zellen mit Formeln hervorzuheben, besteht darin, nach Formeln zu suchen. Hierzu klicken Sie in der Registerkarte „Start“ innerhalb der Gruppe „Bearbeiten“ auf die Schaltfläche „Suchen und Auswählen“ und in dem Menü auf den Befehl „Formeln“.  Excel markiert Ihnen jetzt alle Zellen, in denen sich Formeln befinden. In dem folgenden Beispiel sind dies die Spalten mit den berechneten Abweichungen:  Möchten Sie diese lediglich markierten Zellen mit den Formeln für die weitere Bearbeitung farbig hervorheben, können Sie diese z.B. mit einer Füllfarbe oder einer anderen Formatierung versehen. Also z.B. in der Gruppe „Schriftart“ über die Schaltfläche „Füllfarbe“.  Sie sehen nun alle Formeln, können diese bearbeiten und die Formatierung danach wieder über die Suche aus allen Zellen entfernen. In dem obigen Beispiel also über die Auswahl „Keine Füllung“. Beachten Sie bei diesem Weg nur, dass Sie mit dem gewählten Format ggfs. bereits vorhandene Formate in den Formelzellen überschreiben bzw. entfernen. Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Plan-Ist-Vergleich Visualisierung. |
| Tipp 151: Gesperrte und nicht gesperrte Zellen direkt anhand eines Symbols erkennen |
Arbeitsmappen werden oft von mehreren Anwendern benutzt und es ist sodann ratsam, bestimmte Zellinhalte vor versehentlichem Überschreiben zu sperren und einen Blattschutz zu definieren. Doch es steckt viel Arbeit im Aufbau solcher geschützter Tabellen und auch bei späteren Anpassungen der Daten durch das jeweilige Sperren und Entsperren von einzelnen Zellen. Dabei fehlt oft die Übersicht, welche Zellen aktuell überhaupt gesperrt sind. In Excel hat jede Zelle standardmäßig das Attribut „Gesperrt“ und somit müssen spätere Eingabezellen vorab entsperrt werden, damit bei aktiviertem Blattschutz später eine Eingabe möglich ist. Der bekannteste Weg, um dieses Attribut zu verändern, führt über einen Rechtsklick in der gewünschten Zelle in das Kontextmenü „Zellen formatieren...“ und über die Registerkarte „Schutz“ zu dem Selektionsfeld, wo das Häkchen dann vor „Gesperrt“ entfernt werden kann. Dies geht aber auch viel schneller mit nur einem Klick und gleichzeitig kann Ihnen Excel dazu laufend zu jeder selektierten Zelle den jeweiligen Status anzeigen. Dieser Weg, den Sperrstatus einer Zelle zu verändern führt über das Startmenü und die dortige Gruppe „Zellen“. Ein Klick auf das Auswahlfeld „Format“ öffnet das folgende Menü mit den Informationen zum Schutz von Zellen:  Bild 1: Zellen per Mausklick sperren und entsperren Ist eine Zelle gesperrt, ist das Schloßsymbol in dem Menüeintrag mit einem Rahmen umlegt, bei nicht gesperrten Zellen erscheint hier nur das Schloß. Somit kann man an dieser Stelle schon erkennen, wie der Sperrstatus der jeweils markierten Zellen ist und diese mit einem Klick sperren bzw. entsperren. Um dies jetzt aber auch noch laufend im Blick zu haben, empfiehlt es sich, diese Option in die Schnellzugriffsleiste zu übernehmen. Dies geht mit einem Rechtsklick auf den Eintrag „Zelle sperren“ und der Auswahl „Zu Symbolleiste für Schnellzugriff hinzufügen“:  Bild 2: „Zelle sperren“ zur Symbolleiste für Schnellzugriff hinzufügen Die selektierten Zellen können dann mit nur einem Klick auf das neue Symbol oben in der Symbolleiste entsperrt und auch wieder gesperrt werden. An dem Symbol selbst erkennen Sie auf einen Blick anhand der Hervorhebung den jeweiligen Sperrstatus der Zellen (in diesem Beispiel eine gesperrte Zelle, das Symbol ist hervorgehoben).  Bild 3: Zelle entsperren und sperren mit einem Klick und Anzeige vom Sperrstatus in der Symbolleiste links oben Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Liquiditätsplanung. |
| Tipp 153: Mit Datenschnitten Tabellen filtern und auf das Gewünschte reduzieren |
Um große Datenbestände nach bestimmten Kriterien zu analysieren bietet Excel die Funktion "Filter". Über die damit gesetzten Pfeile in den Spaltenüberschriften haben Sie viele Möglichkeiten zum Sortieren und Filtern der Datentabelle. Auch besteht die Möglichkeit mit Pivot-Tabellen zu arbeiten. Mit einem einfachen Datenschnitt können Sie sich aber auch ohne Filter und Pivot-Tabellenkenntnisse ganz einfach einzelne Datensätze bequem anzeigen lassen und schnell zwischen diesen wechseln. Auch werden die möglichen Datenwerte visuell besser dargestellt als mit der Filteroption. Datenschnitte erleichtern somit ungeübten die Arbeit mit großen Datenbeständen.  Bild 1: Formatierte Tabelle mit Datenschnittfiltern Voraussetzung ist jedoch zunächst, dass Sie Ihre Tabelle in eine Formatierte Tabelle, auch intelligente Tabelle genannt, umwandeln. Wie (leicht) das geht, haben wir bereits in Tipp 123: "Mit dynamischen Tabellen arbeiten - Dynamische Tabellen erstellen" beschrieben. Es sind nur 3 Klicks: Menü Start / Formatvorlagen / Als Tabelle formatieren. Um jetzt einen Datenschnitt einzufügen, klicken Sie zunächst in eine Zelle Ihrer intelligenten Tabelle, um eine Verbindung zu den Daten herzustellen. Im Menü Einfügen finden Sie dann in der Gruppe Filter den Befehl Datenschnitt. In dem sich öffnenden Fenster können Sie die Kriterien (Spaltenüberschriften) auswählen, für die Sie Datenschnitte selektieren möchten. Für jedes hier gewählte Kriterium setzt Excel nach Bestätigung mit OK dann einen sogenannten Datenschnittfilter, welchen Sie bequem mit der Maus positionieren und in der Größe ändern können. Den Datenschnitt können Sie alternativ auch über das Menü Entwurf einfügen, welches bei Auswahl einer Formatierten Tabelle im Menüband erscheint. Hier lässt sich in der Gruppe Tools der Datenschnitt einfügen.  Bild 2: Auswahl der Datenschnittfiltern  Bild 3: Beispiel Datenschnittfilter Mit jedem Klick auf die angezeigten Werte (Schaltflächen) können Sie die Tabelle entsprechend nach diesen Werten filtern. Die zwei Optionsfelder neben der Überschrift des Datenschnittfilters erlauben eine Mehrfachauswahl sowie das Löschen der bereits gesetzten Filter. Solange Sie den Datenschnittfilter selektiert haben, steht ihnen das Register Optionen zur Verfügung, in dem Sie weitere Eigenschaften, wie z.B. die Größe und die Farbe ändern können. Die verwendete Beispieldatei in Excel gibt's hier zum Download. |
| Tipp 156: Datenreihen ohne Formatierungen ausfüllen |
Excel nimmt uns ja schon eine Menge Arbeit ab, z.B. wenn wir neue Tabellen erstellen und das Automatische Ausfüllen von Datenreihen in Anspruch nehmen. Auf diese Weise können wir u.a. Zahlen- und Datumsreihen schnell erzeugen. Ist die Tabelle allerdings schon formatiert und sollen nur die Werte der formatierten Datenreihen aktualisiert werden, ist es schon etwas komplizierter. Beim AutoAusfüllen füllt Excel standardmäßig ja nicht nur die Werte aus, sondern kopiert auch die Formate der Ausgangszelle gleich mit. Das stört oft, kann aber leicht umgangen werden. In einem Beispiel sollen Datumswerte aktualisiert, hier konkret in der ersten Spalte der Jan 23 durch Jan 24 ersetzt und dann die folgenden Monats- und Jahreswerte aufsteigend angepasst werden. Dabei sollen die Formate der Ausgangszelle nicht mitgenommen und die zwischendurch vorhandenen Abgrenzungen zwischen den Jahren auch nicht überschrieben werden.  Bild 1: Beispieldatei Mitarbeiter-Darlehen mit zu aktualisierender Datenreihe Ändern Sie zunächst in der Ausgangszelle das Datum auf Jan 24 und markieren Sie diese Zelle anschließend wieder. Mit der Funktion AutoAusfüllen können Sie jetzt die gesamte Datenspalte aktualisieren, indem Sie mit dem Mauszeiger über die rechte, untere Ecke der Zelle gehen und mit der rechten Maustaste den Anfasser (jetzt ein schwarzes Kreuz) anklicken und bis zum Ende Ihrer Datenliste nach unten ziehen.  Bild 2: Wert aktualisieren und Anfasser mit rechter Maustaste anklicken  Bild 3: Maustaste halten und bis zum Ende der Datenreihe ziehen ... In dem Beispiel wurde zur Veranschaulichung „nur“ bis zum Mrz24 gezogen. Beim Loslassen der rechten Maustaste erscheint ein Kontextmenü und darin wählen Sie den Befehl „Ohne Formatierung ausfüllen“. Kurzer Einschub: In der obigen Grafik sehen wir gut, dass Excel unter dem Kontext auch den vorgeschlagenen Wert für die letzte Zelle der Markierung, hier Mrz 25, anzeigt. Diese Ansicht der Vorschauwerte erflogt auch schon während des Ziehens über den jeweiligen Zellen. Zurück zu der Datenreihe: Diese ist jetzt automatisch aufsteigend in den markierten Zellen ausgefüllt und die Formatierungen sind nicht überschrieben worden.  Bild 3: Nur mit Werten - hier Datumswerte - ausgefüllte Datenreihe Die obigen Grafiken zeigen Ausschnitte aus unserem Tool zur Berechnung eines Mitarbeiter-Darlehens. Sie finden dies in unserer Toolbox bei den Berichtsvorlagen. |
| Tipp 157: Wochenenden in einer Datumsreihe automatisch farblich hervorheben |
Sie haben in Excel viele Daten als Kalender oder als Datumsreihe dargestellt und verlieren den Überblick, wann die freien Tage sind? Dann lassen Sie Excel doch z.B. die Wochenenden automatisch ganz einfach farblich hervorheben. Wir nutzen das z.B. in unserer Liquiditätsplanung oder in unseren Kalender-Tools, wo die Vorlagen auch in allen künftigen Monaten bzw. Jahren Gültigkeit haben sollen und somit die Wochenenden ständig in anderen Zellen dargestellt werden müssen – Excel passt das künftig automatisch an.  Beispiel: Der 3. und 4. Tag im Februar sind als Samstag und Sonntag farblich abgesetzt Alles was Excel braucht, ist die Information, welcher Wochentag in der jeweiligen Zelle betroffen ist und wie die Formatierung lauten soll. Das Mittel zum Zweck lautet Bedingte Formatierung. Da wir ja in jeder Zelle bereits das Datum vorliegen haben, kann Excel daraus einfach den Wochentag errechnen. Das geht mit der Funktion: WOCHENTAG(Zahl;[Typ]). Die Funktion WOCHENTAG wandelt eine fortlaufende Zahl (unser Datum) in einen Wochentag um. Der Tag wird standardmäßig als ganze Zahl ausgegeben, die einen Wert von 1 (Sonntag) bis 7 (Samstag) annehmen kann. Die Option Typ können wir für unser Beispiel zunächst vernachlässigen. Wollen wir also zunächst alle Sonntage gestalten, lautet die Syntax für die Regelbeschreibung: =WOCHENTAG(Zelle)=1 Um die Datumszeile entsprechend zu formatieren, markieren Sie zunächst alle Zellen mit den Daten, in unserm Beispiel 1. - 31. und gehen dann im Register Start auf Bedingte Formatierung und wählen Neue Regel …  Bild 2: Bedingte Formatierung wählen Für die Neue Formatierungsregel wählen Sie den Regeltyp „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ aus und geben in das Feld für die Regelbeschreibung unsere Funktion ein: =WOCHENTAG(C4)=1  Bild 3: Regelbeschreibung Jetzt können Sie noch ein Format festlegen: Button „Formatieren“ drücken und in dem sich dann öffnenden, bekannten Fenster „Zellen formatieren“ die gewünschte Farbe bzw. Hintergrund wählen.  Bild 4: Formate wählen Nach der zweimaligen Bestätigung mit OK haben Sie das Ergebnis:  Bild 5: Sonntage sind rot formatiert Um das Beispiel zu vervollständigen ist für die Formatierung der Samstage in Orange eine weitere Regel notwendig. Die Syntax lautet analog: =WOCHENTAG(C4)=7 Sollen beide Tage gleich formatiert werden, kann man das auch mit einer einzigen Regel erreichen. Entweder man verbindet die oben genannten Funktionen mit ODER, oder man beschäftigt sich mit der oben auch schon erwähnten Option Typ. Mit Typ 16 haben Samstag und Sonntag die Werte 1 bzw. 2 und in der Formel reicht ein einfaches =<3 Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Liquiditätsplanung, welches Sie kostenlos zum Üben downloaden können. |
| Tipp 159: Mit einem Klick zum aktuellen Datum springen (Daten in Zeile) |
In großen Tabellen mit vielen Einträgen kann es mühsam werden, wenn man zwischen den verschiedenen Einträgen navigieren möchte. Ob in langen Terminlisten oder Kalendern, das Scrollen und Klicken hält auf. Wir zeigen in dem heutigen Tipp eine Möglichkeit, wie Sie leicht und schnell mit einem Klick zu der Zelle mit dem heutigen Datum wechseln. Als Beispiel hierfür nehmen wir unser Tool Urlaubsplanung zur Hand, welches Sie in unseren kostenlosen Vorlagen finden. Da das Tool Urlaubsplanung sehr viele Spalten für die einzelnen Jahrestage hat, hält es auf, mit den Pfeiltasten oder den Bildlaufleisten den Cursor zur Spalte mit dem jeweils aktuellen Datum zu bewegen. In dem Tool ist deswegen in den Kopfdaten das aktuelle Datum in Zelle D4 mithilfe der Funktion =HEUTE() hinterlegt. Dieses Datum können wir mit einem Hyperlink versehen. Wird das aktuelle Datum bzw. dieser Link dann angeklickt, springt der Cursor automatisch in die Spalte und die Zelle mit dem aktuellen Datum.  Bild 1: In Zelle D4 steht das heutige Datum Die Formel für den Hyperlink lautet: =HYPERLINK("#"&ADRESSE(7;VERGLEICH(HEUTE();G7:NM7)+6);HEUTE()) Die Formel besteht aus vier Funktionen: Zunächst der Funktion =HYPERLINK(), der das Sprungziel im gleichen Tabellenblatt (#) mitgeteilt wird und welche den Linktext des heutigen Datums bekommt (HEUTE()). Die Funktion =ADRESSE wird dazwischen verwendet, um dem Link die Adresse der Zelle mit dem heutigen Datum anhand ihrer Zeilen- und Spaltennummern mitzuteilen. Die Syntax lautet verkürzt: =ADRESSE(Zeile;Spalte) =ADRESSE(7;10) würde z.B. den Wert $10$7 für eine Zelle in Zeile 7 bedeuten. Da die Spalte jedoch nicht die zehnte ist, sondern flexibel sein soll und noch gesucht wird, kommt hierfür =VERGLEICH zum Einsatz. Mit der Funktion =VERGLEICH(Suchkriterium;Suchmatrix) wird in einem Bereich von Zellen nach einem angegebenen Element gesucht und anschließend die relative Position dieses Elements in diesem Bereich zurückgegeben. =VERGLEICH(HEUTE();G7:NM7) Mit unserer VERGLEICH-Funktion wird in dem Bereich G7:NM7 nach HEUTE() gesucht (also dem heutigen Tagesdatum) und dessen relative Position (relativ zum Beginn des Bereiches) zurückgegeben. Da der Bereich mit G7 beginnt, ist dies die erste Position für die Funktion. Sollte das Tagesdatum dort stehen, lautet das Ergebnis von Vergleich 1 obwohl G die 7. Spalte ist. Da der Bereich in unserem Beispiel in Spalte G (der 7. Spalte) beginnt, müssen wir somit für einen korrekten Wert für die gesuchte Spalte die voranstehenden 6 Spalten addieren: =ADRESSE(7;VERGLEICH(HEUTE();G7:NM7)+6) Fertig: Der Hyperlink zeigt somit das heutige Datum in D4 an und springt zum aktuellen Tag. Das Excelbeispiel finden Sie in unserem Tool Urlaubsplanung. Sie finden dies in unserer Toolbox bei den Allg. Vorlagen/Checklisten. |
| Tipp 161: Mit einem Klick zum aktuellen Datum springen (Daten in Spalte) |
In unserem Excel-Tipp 159 hatten wir gezeigt, wie Sie mit nur einem Klick zum jeweiligen, heutigen Datum springen können. In dem Beispiel, unserer Urlaubsplanung, standen die Werte für das ganze Jahr in einer Zeile nebeneinander. Nun hat uns ein Leser gefragt, wie für Ihn bei einer anderen Thematik, bei der die täglichen Daten untereinanderstehen, die Formel umzubauen ist, damit er schnell zum jeweiligen Tagesdatum runterspringen kann. Nehmen wir für ein Beispiel an, dass in Zeile 6 Überschriften und in B6 konkret die Überschrift „Datum“ steht Ab Zelle B7 sind dann die Tagesdaten untereinander für einen Monat aufgeführt. In Zelle C4 steht immer das aktuelle Tagesdatum und mit einem Klick soll zu eben diesem Datum in der Spalte B gesprungen werden können:  Bild 1: In Zelle C4 steht das heutige Datum, in Spalte B alle Tagesdaten untereinander In Zelle C4 können wir nun einen Hyperlink hinterlegen, der mithilfe der Funktion =HEUTE() das aktuelle Tagesdatum anzeigt. Wird diese aktuelle Datum bzw. dieser Link dann angeklickt, springt der Cursor automatisch in die Spalte B zu der Zelle mit dem aktuellen Datum. Die Formel für den Hyperlink lautet: =HYPERLINK("#"&ADRESSE(VERGLEICH(HEUTE();B7:B37;0)+6;2);HEUTE()) Die Formel besteht aus vier Funktionen: Zunächst aus der Funktion =HYPERLINK(), der das Sprungziel im gleichen Tabellenblatt (#) mitgeteilt wird und welche den Linktext des heutigen Datums bekommt: HEUTE(). Die Funktion =ADRESSE wird dazwischen verwendet, um dem Link die Adresse der Zelle mit dem heutigen Datum anhand ihrer Zeilen- und Spaltennummern mitzuteilen. Die Syntax der Funktion lautet verkürzt: =ADRESSE(Zeile;Spalte) =ADRESSE(7;2) würde z.B. den Wert $2$7 für Zelle (B7) bedeuten. Da die Zeile jedoch nicht immer die siebte in unserer Liste ist, sondern flexibel sein soll und noch gesucht wird, kommt hierfür =VERGLEICH zum Einsatz: Mit der Funktion =VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp]) wird in einem Bereich von Zellen (Suchmatrix) nach einem angegebenen Suchkriterium gesucht und anschließend die relative Position dieses Elements in diesem Bereich zurückgegeben. Der Vergleichstyp 0 besagt, das nach einem Wert gesucht werden soll, der mit dem Wert für Suchkriterium genau übereinstimmt. Also: =VERGLEICH(HEUTE();B7:B37;0) Mit unserer VERGLEICH-Funktion wird in dem Bereich B7:B37 nach HEUTE() gesucht (also dem heutigen Tagesdatum) und dessen relative Position (relativ zum Beginn des Bereiches) zurückgegeben. Da der Bereich mit B7 beginnt, ist dies die erste Position für die Funktion. Sollte das Tagesdatum dort stehen, lautet das Ergebnis von Vergleich 1 obwohl B7 die 7. Zeile ist. Da der Bereich in unserem Beispiel in Zeile 7 beginnt, müssen wir somit für ein korrektes Ergebnis für die gesuchte Zeile die voranstehenden 6 Zeilen addieren: =ADRESSE(VERGLEICH(HEUTE();B7:B37;0)+6;2) Fertig: Der Hyperlink zeigt somit das heutige Datum in C4 an und springt zum aktuellen Tag in Spalte B. Excel-Datei zum Beispiel .... |
Schauen Sie auch in unserer Excel-Makroliste nach arbeitsvereinfachenden Tipps und Möglichkeiten für Ihr tägliches Controlling mit Excel!
In Kooperation mit ... 
Seminartipps ...| Konzernrechnungslegung I |
| Präsentieren - überzeugen - begeistern |
| Excel für Controller |
| Projektmanagement in der Praxis |
| Weitere Seminare .... |
| Nächste Webinare ... |
|---|
| Planning with Power BI |
| Expertentalk Wohnungswirtschaft: Quo Vadis Controlling |
| Managing Operational Processes Through KPIs |
| Schluss mit Excel-Chaos und Insellösungen |
| Weitere Webinare ... |
|---|
| Stellenangebote ... |
|---|
| Leiter Finanz- und Rechnungswesen / Kaufmännischer Leiter (m/w/d) |
| Teamleiter Finanzen/ Treasury (m/w/d) |
| Controller (m/w/d) |
| Controller (m/w/d) |
| Controller (m/w/d) |
| Weitere Stellenangebote ... |
|---|