Dynamische Tabelle Excel

von | 20/03/2017 | Excel

Dynamische Tabelle in Excel

Wir stellen Ihnen eine dynamische Tabelle in Excel vor. Im Video mit einer Beispieldatei und hier als Textbeitrag haben wir für Sie das Prozedere noch einmal zusammengefasst. Die dynamische Tabelle bildet eine Funktionalität in Excel, die leicht übersehen wird. In unserer Tabelle sind keine leeren Spalte und Zeilen enthalten. Wie immer, wenn Sie mit Datensatzlisten arbeiten; gleichgültig ob Sie sortieren, filtern, berechnen oder eben eine dynamische Tabelle erzeugen wollen

Video

dynamische tabelle erzeugen

Dynamische Tabelle erzeugen

Wie man eine dynamische Tabelle erzeugt

Klicken Sie irgendwo innerhalb Ihrer Tabelle, gleichgültig ob in der ersten, in der letzten Spalte, am Anfang, am Ende der Zeilen und wählen Sie aus dem „Register START“ den Schalter „Als Tabelle formatieren“. Im darauffolgenden Fenster öffnen sich zahlreiche Tabellenformatvorlagen.

Auf den ersten Blick wirkt das Ganze lediglich wie eine Farbauswahl. Wir wählen im Beispiel einen mittleren Typ aus den Tabellenformatvorlagen. Der hell-dunkel-wechsel ist bei langen Listen gut zu lesen.

als tabelle formatieren

Tabelle formatieren

In neuen Fenster erkennt das System den Daten- oder Eingabebereich automatisch, markiert ihn und erkennt auch, dass die Tabelle Überschriften/Feldnamen hat. Mit O. K. dieses Fenster bestätigen. An dieser Stelle haben Sie bereits an dynamische Tabelle erzeugt

Merkmale dynamische Tabelle

register tabellentools

Register Tabellentools

Wenn sie genauer auf den Bildschirm schauen, stellen Sie fest, dass mit Erzeugen dieser dynamischen Tabelle, ein weiteres Befehlsregister, nämlich Tabellentools, aufgetaucht ist.

Weiter bemerken Sie, das offensichtlich automatisch die Filterfunktion eingeschaltet ist. Das könnte man natürlich auch über Daten filtern erreichen. Hier gehört das alles zum Gesamtpaket Dynamische Tabellen. Weiter zum Gesamtpaket gehört, dass rechts unten in der letzten Zelle der Tabelle, eine kleine Eckmarkierung auftaucht. Diese Markierung stellt das aktuelle Ende der Tabelle dar.

Schauen wir, was diese Tabelle so dynamisch macht

und was die Arbeit mit einer dynamischen Tabelle so angenehm erscheinen lässt.

fixierung spaltenueberschriften

Spaltenüberschriften-Fixierung

Sie scrollen in der Tabelle nach unten. Fügen Sie zum Beispiel einen weiteren Mitarbeiter hinzu. Bemerken Sie, dass automatisch die Titel, d.h. die Spaltenüberschriften, fixiert sind? Diese Fixierung ist erst dann aufgehoben, d.h. Überschriften verschwinden erst dann, wenn Sie unterhalb der Tabelle klicken, um einen neuen Eintrag vorzunehmen. Nach der Eingabe Ihres neuen Mitarbeiters sind die Titel wieder fixiert und die Formatierung wird automatisch fortgeführt. Wenn Sie statt einer neuen Zeile eine neue Spalte einfügen, etwa um eine Berechnung zu ergänzen, wird die Formatierung in alle Richtungen fortgeführt.

 

dynamische tabelle neue spalte

Neue Spalte in einer dynamischen tabelle

Sie ergänzen in unserem Beispiel eine Spalte mit dem Namen „Provision“. Tragen Sie in die Zelle eine Formel ein, werden Sie bemerken, dass diese Formel ein wenig anders aussieht als andere Formeln. Mit Hilfe einer einfachen Rechenanweisung berechnen Sie für jeden Mitarbeiter 3 % von seinem Umsatz als Provisionsbetrag. Wie immer beginnen Sie im ersten Ergebnisfeld mit einem Gleichheitszeichen und klicken das erste Rechenfeld an. Normalerweise muss die Formel lauten: = E2 * 3 %.

Klicken Sie E an, dann taucht nicht etwa die Adresse E2 auf, sondern der Platzhalter „@ und der Datenfeldname „Umsatz“. Also @Umsatz. Fügen Sue nun noch hinzu: * 3 %. Die Formel lautet komplett: @Umsatz*3%. Bestätigen Sie diese Berechnung mit ENTER. Die Formel wird automatisch in alle Zeilen Ihrer dynamischen Tabelle kopiert. Das Format ist noch nicht so gelungen. Bitte markieren Sie die Spalte und wählen das Währungsformat aus.

Nehmen Sie nun einen weiteren Eintrag auf. Sie werden feststellen, dass auch die Formel automatisch fortgeführt wird. Man muss die Formel nicht kopieren. Die Filterpfeile, die integriert sind, entsprechen den Standard Filterpfeilen wie sie in Datensatzlisten Verwendung finden können.

Dynamische Tabelle wieder in eine normale Tabelle überführen

register tabellentools

Register Tabellentools

Möchten Sie die Liste lediglich umformatieren, entscheiden Sie sich für eine andere Tabellenformatvorlage. Im Register Tabellentools setzen Sie mit Häkchen die verschiedenen Optionen ihrer Tabelle. Beispielsweise können Sie bestimmen, dass die erste Spalte separat hervorgehoben wollen soll. Unter Tabellentools können Sie Ihre Tabelle gegebenenfalls auch exportieren, also direkt zum Beispiel nach Visio übergeben, oder in eine SharePoint Liste umwandeln. „Duplikate entfernen“ entspricht dem normalen Befehl des Registers Daten. Aus dieser Tabelle lässt sich sofort eine Pivot-Tabelle erzeugen.

 

dynamische tabelle umwandeln

Dynamische Tabelle umwandeln in eine normale tabelle

Umwandlung dynamische Tabelle zurück in die normale Tabelle

Es gibt mal wieder zwei Möglichkeiten, die dynamische Tabelle in eine ganz normale Tabelle umzuwandeln. Wenn Sie die Formatierung (Farbe etc.) beibehalten möchte, dann wählen Sie den Schalter „in Bereich konvertieren“.

Wenn Sie dagegen auch die Farbgebung wieder neutralisieren möchten, nehmen Sie zuerst die Farbe fort, in dem Sie sich im Bereich der Tabellenformatvorlagen für die helle Vorlage entscheiden. Anschließend wie oben beschrieben mit dem Schalter „in Bereich konvertieren“ die Tabelle umwandeln. Mit OK bestätigen Sie den Vorgang.

Lediglich eine Eigenheit erinnert an die dynamische Tabelle. Die Formel in unserer Beispieltabelle – in der Spalte Provision – ist anderes aufgebaut, als wenn von Hand eingetragen worden wäre. Der Blattname ist dem Rechenfeld vorangestellt, die Spalte E ist mit einem Dollarzeichen fixiert. Natürlich ist die Formel völlig funktionstüchtig.

Vorteile von dynamischen Tabellen sind zweifellos die automatische Übertragbarkeit der Formate und mehr noch, das automatische Übernehmen/Ergänzen von Formeln.

Danke und viel Spaß und Erfolg mit Excel.

Excel Blattschutz einrichten

von | 31/10/2016 | Excel | 0 Kommentare

Excel Blattschutz einrichten

Möchten Sie das versehentliche Überschreiben von Formeln, oder das Eintragen aktueller Werte verhindern? PCA Wrana zeigt im Video, wie Sie den Excel Blattschutz einrichten, damit Ihre Excel-Tabellenblätter von Dritten nur gezielt oder gar nicht bearbeitet werden können.

Im Video Tutorial „Excel Blattschutz einrichten“ zeigen wir Ihnen Schritt für Schritt, die Einstellungen für den Blattschutz, auch mit Vergabe eines Kennwortes. Darüber hinaus können Sie die Videoinhalte in diesem Beitrag weiter unten nachlesen. Das Video steht zur  kostenlosen Verfügung. In unserer Beispieltabelle sehen Sie zwölf Monate des aktuellen Jahres. Dazu die entsprechenden Kategorien für die Ausgaben und Beträge. Die Zellen sind mit Werten gefüllt bis einschließlich August. Im unteren Blattteil befindet sich ein Abfragebereich. Der Anwender kann den Monat und die Kategorie wählen. Der entsprechende Betrag wird mit Hilfe einer Formel angezeigt.

Excel Blattschutz einrichten im Einzelnen – was dürfen Anwender (Dritte) in der Tabelle bearbeiten und was nicht.

Es soll möglich sein,

  • dass Anwender (Dritte) Werte eintragen bzw. korrigieren können.
  • Monate und Kategorien sollen im Abfragebereich ausgewählt werden können.

Es soll nicht möglich sein,

  • die Formel für die Berechnung zu löschen oder zu ändern.
  • irgendwo im freien Bereich etwas einzutragen.

Excel Blattschutz einrichten in zwei Schritten

Im ersten Schritt

  • markieren Sie allen Zellen in denen Sie später Änderungen bzw. Eingaben vornehmen möchte. Das sind natürlich die Zellen, die die Werte enthalten sollen, darüber hinaus die Listenfelder im unteren Bereich unseres Beispiels – also die Felder „Monat“ und „Kategorie“ im Abfragebereich.
  • Mit einem Rechtsklick in die Tabelle gelangen Sie zum Dialogfeld „Zellen formatieren“. Im neuen Fenster sehen Sie das letzte Register „Schutz“, das für uns von Bedeutung ist. Klicken Sie dieses Register an.
  • Es öffnet ein neues Fenster. In diesem Fenster entfernen Sie das Häkchen vor dem Feld „Gesperrt“.
    Erläuterung dazu: Standardmäßig sind alle Zellen gesperrt. Das bedeutet, wenn Sie nicht vorher die variablen Felder freigeben, können Sie in keinem einzigen Feld der gesamten Tabelle eine Eingabe vornehmen. Die Sperrung deaktivieren Sie mit der Entfernung des Häkchens wie oben beschrieben und bestätigen mit OK.

Im zweiten Schritt – Thema: Excel Blattschutz einrichten

  • wechseln Sie mit einem Klick auf das Register „Überprüfen“.
  • Hier erfolgt mit einem Klick auf „Blatt schützen“ der Blattschutz.
  • Es öffnet sich ein neues Dialogfenster „Blatt schützen:
  • Sie können ein Kennwort eintragen.
  • Differenzieren Sie weiter, was dem Anwender erlaubt werden soll. Hierzu ein paar Erläuterung: Gewöhnlich darf der Anwender „Gesperrte Zellen auswählen“, sich beispielsweise informieren mit welcher Formel gearbeitet wird. Er darf natürlich auch „Nicht gesperrte Zellen auswählen“, sonst wären dem Anwender überhaupt keine Eingaben möglich. Manchmal ist es hilfreich das Formatieren der Spalten oder Zeilen zu gestatten. Wenn man während des Jahres feststellt, dass Beträge so hoch werden, dass die Spaltenbreite zur Anzeige angepasst werden muss. Weiterhin überlegen Sie, ob der Anwender Sortiervorgänge ausführen darf. Ein paar Augenblicke überlegen und hier weitere Optionen sinnvoll wählen könnte von Ihrem Interesse sein.
  • Letzter Schritt im Dialogfenster „Blatt schützen“: Mit OK bestätigen Sie Ihre Eingaben und werden wie immer bei Kennworteingaben darum gebeten, ihr Kennwort zu wiederholen bzw. zu bestätigen.

Excel Blattschutz einrichten am Ende auf Funktion prüfen

Wechseln Sie nun in der Menüleiste mit einem Klick auf Start in das gleichnamige Register. Sie sehen auf den ersten Blick, dass der Blattschutz aktiv ist. Sie Können Schriftart oder Schriftgröße und Zahlenformate nicht verändern. Sie können auf dem Blatt nichts einfügen, außer Kopf- und Fußzeile, was sich streng genommen auch nicht im Tabellenbereich befindet.

Der Anwender kann neue Werte aufnehmen, oder falsch erfasste Werte verändern/korrigieren. Weiterhin ist es möglich im Abfragefenster Monat und Kategorie auszuwählen. Mit einem Klick auf das Formelfeld kann der Anwender einen Blick auf die Formel werfen. Es ist nicht möglich mit Entfernen die Formel zu löschen, oder an irgendeiner Stelle im Tabellenblatt etwas einzutragen. So haben wir uns das vorgestellt. Zum Thema Excel Blattschutz einrichten sind wir mit dieser Lektion am Ende angekommen und wünschen Ihnen

viel Spaß und Erfolg mit Excel.

Das könnte Sie auch interessieren

Excel Ergebnisse und Zielwerte in einem Diagramm darstellen

von | 14/10/2016 | Excel

Zwei Diagrammtypen in einer Grafik kombinieren

Video

 

Excel Ergebnisse und Zielwerte in einem Diagramm darstellen

Wir zeigen Ihnen, wie Sie Excel Ergebnisse und Zielwerte in einem Diagramm darstellen. Hier bietet sich die Kombination zweier Diagrammtypen an: Die Ergebniswerte als Säulen, die Zielvorgaben in Linienform. Das lässt sich mit wenigen Handgriffen umsetzen – aus einem Standard-Säulendiagramm wird schnell und einfach eine Verbund-Grafik.

Zu den Säulen, die die Umsätze darstellen, werden Sie die Zielvorgabe für das vergangene Jahr als Linie abbilden. In der Beispieltabelle sind sämtliche Texte und Werte abgelegt. Zusätzlich haben wir in unserem Beispiel bereits einen Druckbereich eingerichtet, der das Diagramm aufnehmen soll.

Am besten, Sie schauen sich das nachfolgende Excel Video zum Thema an:  Excel Ergebnisse und Zielwerte in einem Diagramm darstellen; weiter unten, unter dem Video-Beitrag, lesen Sie das Ganze auch noch einmal als Text-Kurzform. 

Excel Standard Säulendiagramm einfügen

Schritt 1: Stellen Sie den Cursor in Ihren Datenbereich und holen Sie sich über EINFÜGEN – SÄULE – 2D-SÄULE ein Standard-Säulendiagramm. Dieses Säulendiagramm passen Sie ein wenig in der Größe an. Sie werden nun die roten Säulen durch eine rote Linie ersetzten. Die y-Achsenbeschriftung-Skalierung, die X-Achsenbeschriftung werden Sie 1:1 übernehmen und anschließend die Legende löschen.

Excel Diagrammtyp ändern

Schritt 2: Klicken Sie eine der roten Säulen an. Damit ist die ganze Datenreihe markiert für die Sie jetzt den Diagrammtyp LINIE einstellen werden. Über ENTWURF – DIAGRAMMTYP ÄNDERN wechseln Sie für die rote Datenreihe zum Typ LINIE.

Excel Datenreihen formatieren

Schritt 3: Nur Layout. Um die Linie noch besser sichtbar zu machen und mehr hervorzuheben, markieren Sie die Linie und öffnen mit der rechten Maustaste das Dialogfeld DATENREIHEN FORMATIEREN. Hier bearbeiten Sie die Linienart, in dem Sie die Breite der Linie Ihren Wünschen anpassen. In unserem Beispiel wählt Conny 3.5 P.

Zum Schluss markieren Sie ich noch die Legende, um sie zu löschen. Über die Seitenansicht können Sie das Ergebnis prüfen.

Viel Spaß und Erfolg mit Excel.

Das könnte Sie auch interessieren

Excel Spalten sortieren

von | 16/06/2016 | Excel | 0 Kommentare

Excel Spalten sortieren

In Excel erledigen Sie wahrscheinlich sehr häufig das auf-oder absteigende Sortieren in Tabellen. Eine Routineaufgabe. In Excel Spalten sortieren ist seltener erforderlich, ist aber mit den Menübefehlen genauso möglich. Cornelia Volk zeigt Ihnen, wie Sie mit ein paar Klicks in Excel Spalten sortieren.

In unserem Beispiel haben die Mitarbeiter, deren Namen in Zeile 1 hinterlegt sind, Messwerte erfasst. Diese Tabelle soll nun nicht nach den Datumsangaben, also zeilenweise sortiert werden, sondern spaltenweise nach den Namen der Mitarbeiter.

Schritt für Schritt Excel Spalten sortieren

  1. Markieren Sie den Sortierbereich.
  2. Über das Register DATEN – SORTIEREN gelangen Sie zum Sortierdialog. Es öffnet sich eine neue Dialogbox mit Namen „SORTIEREN“.
  • Excel 2007/2010 klicken Sie im Register DATEN in der Gruppe SORTIEREN UND FILTERN auf das Symbol SORTIEREN.
  • Excel bis Version 2003 den Befehl DATEN – SORTIEREN auf.

excel spalten sortieren detail 1

excel spalten sortieren

  1. Im Fenster „Sortieren“ klicken Sie auf OPTIONEN und erhalten eine weitere Dialogbox (Sortieroptionen). Aktivieren Sie die Option „Spalten sortieren“.

excel spalten sortieren detail 2

  1. und wählen nun im gleichen Fenster unter ZEILE im Dropdown Menü die Zeile aus, nach der Ihre Spalten sortiert werden sollen. In unserem Beispiel stehen die Namen der Mitarbeiter in Zeile 1, also wählen wir im Beispiel auch die Zeile 1 aus.
  2. Mit OK bestätigen.

Die Tabelle liegt nun in der gewünschten Sortierung vor. Optimieren Sie ggf. das Layout.

Teilergebnis in Excel

von | 15/05/2016 | Excel | 0 Kommentare

Teilergebnis in Excel

Wir zeigen Ihnen, wie Sie schnell und unkompliziert Ihre Datensatzlisten mit Hilfe der Funktion Teilergebnis in Excel auswerten können.

Im Video üben wir mit einer Beispieltabelle. Sie erstellen ein

  1. Teilergebnis in Excel für die Umsatzzahlen der Mitarbeiter pro Gebiet.
  2. Das Hinzufügen einer Auswertung zu einem bestehenden Teilergebnis wie z.B. Mittelwert und höchster Umsatz anzeigen lassen.
  3. Seitenumbrüche zwischen Gruppen einfügen – für lange Datensatzlisten

Teilergebnis in Excel für die Umsatzzahlen der Mitarbeiter pro Gebiet

Erste Voraussetzung:

Die Spalte, nach der Sie auswerten möchten, muss sortiert vorliegen. Also klicken Sie irgendwo in einen Bereich dieser Spalte (im Beispiel ist es B) und über „DATEN“ im Menü klicken Sie auf den Button „aufsteigende Sortierung“.

Zweite Voraussetzung:

Füllen Sie ein Dialogfeld aus – wieder über „DATEN“ –  in der Befehlsgruppe „Gliederung“ befindet sich der Schalter „TEILERGEBNIS“. Excel schlägt vor, die Datensätze nach der ersten Spalte, also nach dem Namen zusammen zu fassen. Wir werden aber nach „Gebiet“ auswerten und wählen Gruppieren nach Gebiet. Im Feld „Unter Verwendung von:“ belassen wir es bei der Summe.

Excel schlägt als Auswertungsfeld die letzte Spalte vor, in diesem Fall den Umsatz, was an dieser Stelle auch korrekt ist. Mit einem Klick auf OK schließen Sie ab und erhalten Ihr erstes Teilergebnis in Excel.

Gliederungssymbole und Hierarchieebenen in Teilergebnisse

Im linken Rand von Excel erkennen Sie nun Gliederungssymbole. Excel hat hier drei Hierarchieebenen erkannt. Standardmäßig wird immer die niedrigste Ebene angezeigt, also sehen Sie im Moment alle Einzelheiten. Mit einem Klick auf die zweite Ebene, bekommen Sie lediglich Ihre Zusammenfassung, die Gruppensummen bzw. die Teilergebnisse. Schalten Sie mit einem Klick in die erste Ebene, sehen Sie lediglich den Gesamtbetrag.

Möchten Sie ein Ergebnis genauer betrachten, klicken Sie auf das PLUS-Zeichen bei z.B. Rheinland-Pfalz und haben nun den Blick auf die Umsätze der Mitarbeiter dieses Bundeslandes.

Tabelle wieder in den Ursprungszustand zurück versetzen

Im Menü DATEN > in der Befehlsgruppe „Gliederung“ befindet sich der Schalter „TEILERGEBNIS“. Den klicken Sie wieder an, wie schon zur Erstellung. Im neuen Fenster „Teilergebnisse“ klicken Sie auf das Feld „ Alle entfernen“. Ihre Tabelle befindet sich sofort wieder im Ursprungszustand.

Das Hinzufügen einer Auswertung zu einem bestehenden Teilergebnis

wie z.B. Mittelwert und höchster Umsatz anzeigen lassen.

Bleiben wir noch einen Moment bei den weiteren Möglichkeiten und klicken noch einmal auf den Schalter Teilergebnis.

Sie können unter verschiedenen statistischen Funktionen wählen. Im Video stellt Ihnen Cornelia Volk den Mittelwert vor und fügt eine zusätzliche Auswertung hinzu – das beste/höchste Umsatz-Einzelergebnis.

Wichtiges zu beachten: wenn Sie eine Auswertung in einer Tabelle hinzufügen möchten, ist eine Einstellung entscheidend: Entfernen Sie das Häkchen vor „ Vorhandene Teilergebnisse ersetzen“. In unserem Beispiel bedeutete das Vergessen dieser Einstellung, dass nur der Mittelwert angezeigt würde.

Abschließend auf OK klicken und sich dem linken Rand zuwenden, der um eine weitere Gliederungsebene erweitert wurde. Diese Auswertung hat nun 4 Ebenen. Klicken Sie sich doch mal durch die Ebenen und schauen sich das Ergebnis an.

teilergebnis in excel

Seitenumbrüche zwischen Gruppen einfügen bei langen Datensatzlisten

Die Funktion „Teilergebnis “ in Excel bietet bei langen Datensatzlisten auch die Möglichkeit, zwischen den einzelnen Gruppen Seitenumbrüche einzusetzen.

In unserer Beispieltabelle sind alle vorherigen Gruppierungen gelöscht. Bitte klicken Sie nun erneut auf „Teilergebnis“ und gruppieren im Fenster nach Gebiet – Summe – Umsatz, so wie oben bereits beschrieben.

Zusätzlich setzten Sie nun ein Häkchen vor „Seitenumbrüche zwischen Gruppen einfügen“. Mit OK bestätigen Sie diese Auswahl und erkennen an den gestrichelten Linien die Seitenumbrüche. Selbstverständlich noch besser zu erkennen in der Seitenansicht.

Danke für Ihre Aufmerksamkeit.

Viel Erfolg mit Excel wünscht PCA Wrana

Das könnte Sie auch interessieren

Benutzerdefinierte Zahlenformate in Excel erstellen

von | 30/04/2016 | Excel | 0 Kommentare

Wie Sie benutzerdefinierte Zahlenformate in Excel erstellen.

Unsere drei Beispiele behandeln die „benutzerdefinierte Zahlenformate“ gefolgt von Beispielen für die  „führenden Nullen“ bis hin zum Beispiel für das „Datum mit Tagesanzeige„.

Im Video sind alle Schritte dazu erklärt, aber auch hier als Text noch einmal geschrieben.

Beispiel benutzerdefinierte Zahlenformate

Im Bereich der Spalte A unserer Beispiel Tabelle haben wir einige Werte zusammengestellt:

  1. Einen positiven Wert mit drei Nachkommastellen
  2. Einen negativen Wert ohne Nachkommastellen
  3. Eine Null
  4. Einen Text wie k.A. für „keine Angabe“

Genau diese vier Optionen können Sie unterschiedlich formatieren lassen. Dazu markieren Sie diesen Eingabebereich mit den vier Werten. Im Startregister wählen Sie die Befehlsgruppe „Zahl“ und klicken auf das kleine Listendreieck. Im nächsten Fenster „Zellen formatieren“ nehmen Sie die Einstellungen vor:

Im Register Zahlen wählen Sie die Kategorie „Benutzerdefiniert“.  Zahlreiche Formate finden Sie hier bereits. Wählen Sie am besten ein Format, das Ihren Wünschen relativ nahe kommt. In unserem Beispiel möchten wir positive wie auch negative Werte mit ggf. 1000er Stellen anzeigen lassen. Außerdem sollen negative Werte in rot erscheinen.

benutzerdefinierte zahlenformate in excel erstellen

Unsere Auswahl wird nun angepasst. Da hier keine Währungsbeträge abgebildet werden sollen, löschen Sie das Eurosymbol im Feld „Typ“ heraus – in unserem Fall an zwei Stellen.

Positive und negative Werte werden bereits formatiert in unserer Vorauswahl. Wir setzten ein Semikolon am Ende hinter 0,00 und eröffnen uns damit eine neue Möglichkeit, nämlich was erscheinen soll, wenn eine Null eingetragen wird. In diesem Fall schreiben wir die Notiz „entfällt“ geschlossen von einem Semikolon. Es soll noch ein Befehl folgen.

Wenn der Anwender versehentlich einen Text in die Zeile einträgt, dann soll ihn ein entsprechender Hinweis auf sein Versäumnis aufmerksam machen. Für Sie bedeutet das, dass Sie „ungültiger Texteintrag“ eintragen (oder was Ihnen am besten passt).

Ohne Semikolon, aber in Anführungszeichen, das Fenster mit OK bestätigen und damit schließen. Überprüfen Sie Ihre Formatierungen in der Tabelle und ändern die Zellwerte.

benuterdefinierte Zahlenformate in excel - fertiges Beispiel

Die führenden Nullen

Sie möchten Artikelnummern immer vierstellig anzeigen lassen. Der Wert soll von links nach rechts mit Nullen aufgefüllt werden.

Wie gehabt, markieren Sie den Eingabebereich (oder Erfassungsbereich) und gelangen wieder über Start – Befehlsgruppe Zahl zum Fenster „Zellen formatieren“ Kartei „Zahlen“ und wählen mit einem Klick „Benutzerdefiniert“.

Klicken Sie zunächst auf die einfache 0 (Null). Ergänzen Sie im Feld „Typ“ nun weitere 3 Nullen, sodass dort vier Nullen abgebildet werden. Im Feld „Beispiel“ darüber sehen Sie schon in einer Vorschau, wie das Format aussehen wird. Bestätigen Sie Ihre Eingaben und Auswahl mit OK. Prüfen Sie in der Tabelle mit ein paar Beispielzahlen Ihre Formatierung. Aus der Eingabe einer 7 wird automatisch 0007 und bei einer vierstelligen Zahl werden keine Nullen vorangestellt.

Datum mit Tagesanzeige

Oft wünscht man sich zum Datum, den Wochentag im Klartext angezeigt zu bekommen wie Montag, 18.04.2016.

Bitte markieren Sie wieder den Eingabebereich und verfahren weiter wie in obigen Beispielen. Im Feld Typ steht schon die Standardangabe TT.MM.JJJJ für z.B. 19.04.2016

Sie ergänzen nun einfach wie folgt auf TTT TT für die Anzeige Die 19.04.2016.

Zusammengefasst bedeuten die Ergänzungen bzw. Erweiterungen:

Um drei TTT erweitern, also TTT TT stehen für den abgekürzten Tagesnamen, z.B. Mon.

Um vier TTTT erweitern, also TTTT TT stehen für einen ausgeschrieben Tagesnamen wie z.B. Montag.

Um ein M erweitert auf drei MMM stehen für den abgekürzten Monatsnahmen.

Um zwei M erweitert auf vier MMMM stehen für den ausgeschrieben Monatsnamen.

Wenn Ihnen die Jahreszahl auch zweistellig genügt, nehmen Sie zwei J heraus. Anstatt 2016 erhalten Sie nur die 16. Wenn Sie nun ein Datum in die Zelle eingeben, wird Excel direkt Ihren Formatierungswünschen folgen.

Viel Erfolg und beste Grüße
Cornelia Volk
(PCA Wrana)

Das könnte Sie auch interessieren

Benutzerdefinierte Ansichten in Excel

von | 07/04/2016 | Excel | 0 Kommentare

Benutzerdefinierte Ansichten in Excel zum Prüfen, Drucken und Bearbeiten von Tabellenblättern

Ich möchte Ihnen die benutzerdefinierten Ansichten in Excel vorstellen. Ein gute Möglichkeit unübersichtliche, komplexe Tabellenblätter schnell in handliche Ansichten zu bringen und zu bearbeiten. Darüber hinaus lassen sich prima diverse Eingabeansichten vorbereiten. Verschiedene Mitarbeiter speichern ihre bevorzugten Einstellungen und nutzen somit unterschiedlichen Darstellungen für ein einfacheres Arbeiten.

Vorbereitet ist eine Budget Tabelle. Sie ist im Beispiel nicht wirklich sehr groß und unübersichtlich, aber durch den Zoom beispielsweise nicht in einem Blick zu erkennen. (Im Video werden die Beispiele erläutert, die wir hier im Text nicht weiter beschreiben.)

Wie gehen Sie nun vor? Im Video ab 1:45, falls Sie die Beispiele nicht sehen wollen. Im Video-Beispiel gehen wir von der Standardanzeige aus und nehmen für die benutzerdefinierte Ansichten in Excel die Einstellungen vor, die wir brauchen.

Mit benutzerdefinierte Einstellungen in Excel – für das 3. Quartal eine Bearbeitungsansicht erstellen.

Dazu blenden Sie die Spalten aus, die momentan nicht relevant sind, auch die Ergebnisspalte gehört dazu, ich kann auch Zeilen ausblenden, in denen keine Informationen aufgenommen werden, wie in unserem Beispiel die Summenzeile. Um alles größer darzustellen, stelle ich über Ansicht das Einfrieren des Fensters ein. Sie erhalten damit eine große Auflösung.

Bringen Sie den Cursor an die gewünschte Eingabestelle. Diese Einstellungen speichern Sie nun alle mit einem Klick auf dem Register Ansicht – Benutzerdefinierte Ansichten. Der Schalter „Hinzufügen“ öffnet ein Dialogfeld, in dem ich einen Namen für diese Ansicht eingebe und alle dazugehörigen Filter speichere bzw. mit OK bestätige.

Eine weitere benutzerdefinierte Ansicht in Excel , für die Budget-Ergebnisse, wird im Video noch einmal vorgestellt. Hier die Kurzform: markieren – ausblenden – zoomen – Menüleiste: Ansicht – benutzerdefinierte Ansichten –  eine weitere Einstellung hinzufügen.

Unkompliziertes Wechseln zwischen den benutzerdefinierten Ansichten in Excel

Den Schalter der benutzerdefinierten Ansichten nehmen Sie am besten in die Schnellzugriffsleiste auf. Die Aufnahme in diese Leiste erfolgt in derselben Weise wie in PowerPoint oder Word: Jeden Schalter, den Sie in einer Multifunktionsleiste finden, können Sie mit rechts anklicken und mit den ersten Kommando zur Symbolleiste für den Schnellzugriff hinzufügen. Gleichgültig in welchem Register ich mich befinde, ich wechsle meine Ansichten ohne Probleme.

Vielen Dank. Weiterhin viel Spaß und Erfolg mit Excel.

Das könnte Sie auch interessieren

Excel konsolidieren mehrerer Arbeitsblätter

von | 08/02/2016 | Excel | 0 Kommentare

Excel konsolidieren mehrerer Arbeitsblätter

Im Video zeige ich Ihnen, wie Sie aus mehreren Arbeitsblättern eine Formel erstellen. Excel nennt diesen Vorgang KONSOLIDIEREN.

Zunächst aktivieren Sie mit einem Rechtsklick auf ein Tabellenblatt (Arbeitsblatt) den Gruppenmodus und bilden für alle Tabellenblätter die Summen. Diese Vorgänge sind im Video geschildet, hier aber nicht noch einmal aufgeschrieben. Wie Sie den Gruppenmodus aktivieren, ist in einem anderen Video ausführlicher erklärt.

Das Tabellenblatt „TOTAL“ enthält noch Nullwerte, was wir mit den folgenden Schritten ändern wollen. Aus den vier Arbeitsblättern Ost, West, Süd und Nord, welche die Gesamtumsätze der vier Filialen abbilden, werden wir durch Addition der vier Arbeitsblätter den Gesamtumsatz aller Filialen errechnen und im Tabellenblatt „TOTAL“ darstellen, also in Excel konsolidieren mehrerer Arbeitsblätter durchführen.

  1. Starten Sie im Beispiel mit dem Ostblatt im Feld 4 mit dem IST-Zeichen (=) für den Formelbeginn
  2. Klicken Sie gleich danach auf das Blatt Ost und hier in das Feld B4
  3. Mit dem Plus-Zeichen (+) werden nun sämtliche weiteren Tabellenblätter addiert, d.h. in unserem Beispiel…

Plus – Westblatt – Feld B4
Plus – Südblatt – Feld B4
Plus – Nordblatt – Feld B4

Zum Schluß die Formel mit der ENTER-Taste abschließen. Diese Eingabe erfolgt einmalig, denn die Formel kann mit dem Ausfüllzeichen ausgefüllt werden sowohl nach Unten als auch nach Rechts.

Das könnte Sie auch interessieren

Gruppenmodus in Excel vereinfacht die Arbeit

von | 26/01/2016 | Excel | 0 Kommentare

Der Gruppenmodus in Excel vereinfacht die Arbeit

Warum der Gruppenmodus in Excel Ihre Arbeit vereinfacht, kann man am besten an einem Beispiel erklären. Unterstellen wir mal, dass mehrere Tabellenblätter angelegt sind, welche Umsatzzahlen für verschiedene Produkte, an den Wochentagen Montag bis Samstag und an unterschiedlichen Standorten enthalten. Zur besseren Übersicht gibt es Arbeitsblätter (Excel-Sheets) für die jeweiligen Standorte. Auf allen Arbeitsblättern sind die Tabellen in der gleichen Position und mit dem gleichen Layout angeordnet.

Sie stellen fest, ein Produkt wurde vergessen aufzunehmen. Außerdem könnte die Formatierung besser gestaltet werden und eine neue Formel wird nötig sein.

Wäre es nicht großartig, wenn mehrere Tabellenblätter gleichzeitig bearbeitet werden könnten? Das wäre eine riesige Zeitersparnis und in diesem Fall nicht zu schön, um wahr zu sein.

Im Gruppenmodus verknüpfen Sie die Excel Tabellen-Blätter und können mehrere Arbeitsblätter gleichzeitig bearbeiten.

Wie schalte ich den Gruppenmodus ein?

Mit gedrückter [Strg]-Taste oder [Shift]-Taste mehrere Tabellenblätter markieren. (Im Video zum Excel Gruppenmodus wird noch ein anderer Weg gezeigt.) Sehen Sie bitte oben in den Dateinamen! Der Dateiname ist jetzt um das Wort „GRUPPE“ erweitert. Das war es schon; der Gruppenmodus ist aktiviert.

Ob es nun Formatierungen sind, oder eine Datenbearbeitung, die durchgeführt werden muss, jede Eingabe erscheint in diesem Gruppenmodus automatisch in allen Tabellen der Gruppe. Klasse, oder?

Die Inhalte im Video

Meine Übungsdatei besteht aus fünf Blättern und stellt den Wochenumsatz einer fiktiven Firma dar mit den Filialen in Ost, West, Süd, Nord und dem Blatt Total.

Alle Blätter sind identisch aufgebaut. Die Spalte Produkte und die Wochentage von Montag bis Samstag.

Alle vier Blätter werden wir in einem Rutsch gleichzeitig formatieren. Dafür müssen wir den Gruppenmodus aktivieren. Das geschieht ganz einfach mit einem Rechtsklick auf eines der Tabellenblätter. Mit einem Klick auf „alle Blätter auswählen“ haben Sie automatisch den Gruppenmodus eingestellt. Dargestellt wird der Gruppenmodus im Dateinamen. Bitte dafür kurz nach oben schauen auf den Dateinamen. Der ist nun um das Wort „Gruppe“ erweitert.

Ab sofort gilt – was immer Sie eingeben – es gilt für alle Blätter.

Wir starten mit der Formatierung der Zahlen als Währungssymbol. Markieren Sie bitte die Daten.

Im Startregister das Symbol für Währungen wählen, ich wähle Euro, und löschen Sie die Dezimalstellen, wenn sie nicht dargestellt werden sollen.

Für die Warengruppen und die Wochentage wählen Sie eine Farbe.

Die Spalte A wird etwas breiter und der Abstand zwischen Zeile 1 und 2 vergrößert.

Wurden für alle Tabellenblätter diese Formatierungen übernommen? Mit einem Klick auf die Tabellenblätter prüfen wir das.

Achtung: Mit einem Klick auf ein Tabellenblatt wurde der Gruppenmodus aufgehoben. Wir wollen weitermachen und müssen deshalb den Gruppenmodus wieder einschalten. Wie bereits erklärt, einfach einen Rechtsklick auf ein Tabellenblatt und „alle Blätter auswählen“. Achten Sie immer oben auf den Dateinamen, der um das Wort „Gruppe“ erweitert wurde.

Ich möchte nun die Formeln der Summenfunktion löschen. Ich markiere und lösche mit der Entfernentaste.

Ich trage die Formel erneut ein mit der bekannten Summenfunktion, die nach rechts ausgefüllt wird. Analog dazu die Summenfunktion pro Produktgruppe.

Eine Formel bilden wir für den prozentualen Anteil der Produkte am Gesamtumsatz, also Produktsumme (H4) dividiert durch Gesamtsumme (H10). Das muss eine absolute Adresse sein. Diese Zahl wird mit Prozent formatiert und natürlich kopiert.  Prüfen Sie wieder alle Tabellenblätter. Das Tabellenblatt „Total“ enthält noch Nullwerte, weil keine Daten erfasst sind.

Der Gruppenmodus in Excel ist eine wahre Arbeitserleichterung.

Danke und viel Erfolg mit Excel

Ihr Othmar Wrana

Das könnte Sie auch interessieren

Excel 2010 Pivot Tabelle erstellen

von | 02/01/2016 | Excel | 0 Kommentare

Excel 2010 Pivot Tabelle erstellen-Grundlagen

Mit Excel 2010 Pivot Tabellen erstellen ist leichter als man denkt. In diesem kostenlosen Excel Grundlagen Video (ca. 7 Minuten) zeige ich Ihnen, wie Sie in Excel 2010 eine Pivot Tabelle erstellen. Ich zeige Ihnen kurz den Tabellenaufbau, den Befehl – EINFÜGEN – Pivot-Table, sowie die verschiedenen Möglichkeiten, die Daten beliebig anzuordnen. Am Ende werden Sie aus den Beispieldaten mit zwei Mausklicks ein Diagramm erzeugen können.

Inhaltsübersicht Excel 2010 Pivot Tabelle erstellen

  1. Notwendiger Tabellenaufbau
  2. Menü-Befehl Einfügen Pivot Tabelle
  3. Anordnen der Daten in der Pivot Tabelle
  4. Verändern und Bewegen der Daten- und Textfelder
  5. Erstellen eines Diagramms

Los geht´s mit dem Tabellenaufbau in Excel 2010

Ich habe eine Tabelle aus dem Personalbereich vorbereitet, mit den Spalten Name/Vorname /Abteilung/ Gehalt und so weiter. Diese Spaltenbezeichnungen werden wir verwenden, um eine Pivot Tabelle zu erzeugen.

Zu Beginn befindet sich der Cursor in der Tabelle, es ist nicht notwendig, dass Sie die Tabelle markieren.

Mit dem Befehl „EINFÜGEN –Pivot-Table“ erscheint eine neue Dialogbox:

Die Dialogbox zeigt den Bereich der Daten und erstellt die Pivot-Tabelle in einem neuen Blatt. Die Vorgaben werden jetzt einfach mit OK bestätigt.
Sie sehen links den leeren Bereich der Pivot Tabelle. Im rechten Bereich erkennen Sie alle Spaltenbeschriftungen Ihrer Daten. Im unteren Bereich werden vier Felder gezeigt, in denen Sie die Daten für die Auswertungen organisieren und anordnen werden.

Anordnen der Daten

Ich möchte zunächst für jede Abteilung die Gehälter darstellen. Dazu ziehe ich per Drag and Drop das Feld Abteilung in den Bereich der ZEILEN. Sie sehen sofort jede Abteilung im linken Pivot Teil aufgelistet. Nun nehme ich das Feld Gehalt in den Bereich der WERTE und fertig ist die erste Pivot Tabelle.

Üblicherweise werden die Werte noch mit dem Währungssymbol formatiert über das Register START; die Dezimalstellen können gelöscht werden.

Ändern und Bewegen der Daten und Textfelder

Diese erste Auswertung können Sie beliebig ändern.
Die Gehälter pro Region darstellen: Dazu entfernen Sie die Abteilung aus dem Bereich ZEILEN und ziehen nun das Feld REGION in den Bereich ZEILEN. Das Entfernen nehmen Sie ebenfalls einfach per Drag and Drop vor, in dem Sie das Feld in den leeren Bereich der Pivot Tabelle ziehen.
Eine weitere Möglichkeit ist, die Gehaltszahlungen nach Geschlecht zu definieren, sofern in der Tabelle dieses Feld angelegt wurde. Dazu wird das Feld REGION entfernt und dafür das Geschlecht eingefügt.

Pivot Tabellen können neben Wertefeldern auch mit Textfeldern „rechnen“.

Dazu ein neues Beispiel: Sie möchten wissen, wie viele Mitarbeiter in der jeweiligen Abteilung arbeiten. Sie entfernen in unserer Übungstabelle alle vorhandenen Felder aus den Bereichen.
Erneut wird das Feld Abteilung in den Bereich ZEILEN bewegt und das Feld NAME, ein Textfeld, in den Bereich WERTE verschoben. Da es sich um Text handelt, wird automatisch die Funktion Anzahl verwendet. Sie sehen die Anzahl der Mitarbeiter pro Abteilung. In unserem Fall aber, muss das Währungsformat, das von der letzten Auswertung stehen blieb, noch geändert werden auf ein Standardformat. Das erledigen Sie über die übliche Zahlen-Formatierung.

Aufteilung von Voll- und Teilzeitkräften pro Abteilung darstellen.

Bewegen Sie das Feld VOLL-TEILZEIT in den Bereich SPALTEN und prüfen Sie das Ergebnis in der Pivot Tabelle. Tauschen Sie die beiden Felder beliebig aus und üben die Variationen. Ändern Sie auch mal die Reihenfolge!

Wir erstellen ein Pivot Diagramm

Bevor Sie ein Pivot Diagramm erstellen, erfährt Ihre Pivot Tabelle noch einmal eine Änderung. Sie lassen erneut die Abteilungen und das Gehalt darstellen, formatieren wieder mit dem Währungssymbol und fügen zusätzlich die VOLL-TEILZEIT Daten im Spalten Bereich hinzu.

Um ein Diagramm zu erstellen muss auch hier nichts markiert werden, der Cursor muss sich nur in der Pivot-Tabelle befinden.
Im Register ANALYSIEREN befindet sich das ICON PIVOT Chart.

Das vorgeschlagene SÄULEN Diagramm bestätigen Sie mit OK. Violá, das Diagramm ist erstellt. Selbstverständlich wählen Sie je nach Geschmack und Erfordernissen ein anderes Diagramm Design.

Das war eine kurze Exkursion in die Welt der Excel 2010 Pivot Tabelle.

Haben Ihnen meine Erklärungen gefallen, freue ich mich, wenn Sie mich mit einem Klick auf TEILEN belohnen.

Das könnte Sie auch interessieren

WordPress Cookie Plugin von Real Cookie Banner