Skip to content

Pivot Tabelle Spaltenbreite anpassen

Heute morgen habe ich einen Tipp auf dem ExcelBlog gesehen der mir so gut gefallen hat dass ich es einfach für die Deutschsprachige Excel Community auch hervorheben musste.

Wer kennt das nicht: Man erstellt eine Pivot Tabelle und passt die Spaltenbreite so an dass alles Lesbar ist. Beim nächsten aktualisieren der Daten fängt man wieder von vorne an!

Pivot Spaltenbreite beibehalten bei Aktualisierung:

1. Rechts-Klickt irgendwo in die Pivot Tabelle und dann Pivot-Table Tools…

image

2. Im Menü unter Layout & Format das Häkchen bei Spaltenbreiten bei Aktualisierung automatisch anpassen rausnehmen

SNAGHTML19641f

Sehr einfach! Aber kennen muss man es!

Posted in Pivot Tabellen, Tipps.

Tagged with .

No comments

By Lukas – Mai 9, 2012

Histogramme erstellen in Excel

image

Immer wieder werden wir mit Histogramme erstellen konfrontiert. Histogramme kann man natürlich auch manuell in Excel erstellen, aber es gibt eine einfachere Möglichkeit dies zu erledigen: Das Datenanalyse Histogramm Werkzeug

(ACHTUNG: Damit das Histogramm Werkzeug benutzt werden kann muss das  Analyse Add-In installiert sein.)

Beispiel: Histogramm der Mittleren KM Laufleistung

Wir wollen als Beispiel ein Histogramm erstellen welches die Mittlere Kilometerlaufleistung eines Fahrzeuges zeigt. Wir haben ungefähr 4500 Werte. Wir wollen bei einer mittleren KM Laufleistung von 3750 anfangen und nachher in 2500KM Schritten weiter raufgehen bis 28750.

Continued…

Posted in Tools.

Tagged with , .

No comments

By Lukas – Mai 7, 2012

Wöchentlicher Planer zum ausdrucken

image

Ironischerweise arbeite ich den ganzen Tag am PC, kann aber weder meine To Do Liste, noch meine Wochenplanung effektiv am PC machen! Es muss auf Papier sein, sonst klappt das bei mir nicht!

So habe ich mir dann halt eben einen Planer in Excel erstellt den ich ausdrucken kann.

Da ich ein Fan von Stephen R. Covey’s “Sieben Wege zur Effektivität” (Original Titel: The Seven Habits of Highly Effective People) bin, habe ich in meiner Vorlage einen schwachen Hinweis auf die “7 Habits” Philosophie abgebracht (unten Links: “Wöchentliches Q2 Planen”). Der Planer weicht aber im wesentlichen nicht von einem normalen ab und sollte so auch breiteren Einsatz finden können.

Wer ähnlich wie ich, sich einfach nicht vom Papier trennen kann, darf den Planer nutzen wenn er hilfreich ist und gefällt!

Download: Wöchentlicher Planer (11)

Posted in Allgemein.

Tagged with .

No comments

By Lukas – Mai 3, 2012

Dynamische Bereiche mit Namens-Manager

 

Öfters werden wir in bestehenden Berichten mit Daten konfrontiert die sich in Ihrer Struktur verändern.

Es kommt ein neuer Monat zum Resultat hinzu. Die Anzahl Zeilen mit den Kundenaufträgen ist mal kürzer, mal länger. Das Management hat beschlossen die Verkaufsgebiete zu erweitern oder reduzieren, oder die Anzahl Produktkategorien zu vergrößern oder verringern. Verkäufer Müller geht, Verkäufer Meier kommt. Das Risk Management erhält eine neue Kategorie. Etc etc etc.

Das ganze Zahlengerüst muss dann um diese neuen Rubriken erweitert werden.

Dynamische Bereiche können uns helfen diese (langweilige und deswegen auch oft fehlerbehaftete) Arbeit zu eliminieren!

Zwei Methoden um dynamische Bereiche zu erstellen

Namens-Manager

In diesem Artikel konzentrieren wir uns auf diese Methode.

Continued…

Posted in Dynamische Berichte.

Tagged with , , .

No comments

By Lukas – April 30, 2012

Alle Grafikobjekte auf einmal löschen

Manchmal kopieren oder importieren wir Daten aus anderen Applikationen und es kommen ungewollt Grafikobjekte hinzu. Je nach Anzahl der Objekte kann es für das manuelle Auswählen und Löschen die Grenze meiner Nerven des Machbaren sprengen.

image

Glücklicherweise hat Excel hier eine einfache Lösung: Finden & Ersetzen

Im Start Menü>Suchen und Auswählen>Inhalte auswählen

image

Es öffnet sich ein Fenster. Hier “Objekte” auswählen und bestätigen.

SNAGHTML2d0898

image

Fertig!

Als Anstoß für den Tipp in ein Artikel umzusetzen möchte ich mich bei Bacon Bits bedanken!

PS:

Währen man in Excel 2003 noch mit genau diesem Problem zu kämpfen hatte, nämlich nach dem Datenimport aus dem Web alle Bilder löschen musste, so hat Microsoft ab Version 2010 (evtl. 2007) in die Andere Bresche geschlagen: jetzt kann man per Datenimport aus dem Web gar keine Bilder mehr importieren!

Posted in Tipps.

Tagged with .

No comments

By Lukas – April 25, 2012

Gültigkeitsprüfung für Dropdown-Liste

image

Vor kurzem wurde ich gefragt wie man eine Dropdown-Liste mit einer Gültigkeitsprüfung verbindet, so dass die Dropdown-Liste nur unter gewissen Bedingungen angezeigt wird. Das ist machbar und auch gar nicht so schwierig!

Die Werte Dropdown-Liste welche man dem User zur Auswahl geben möchte wird im Namens-Manager zuerst erfasst – z.B. als “Liste”. In unserem Beispiel (hier die Datei mit dem Beispiel (15)) sind das die Zellen B3:B6.

SNAGHTML8aa804

Mit Daten Menü>Datenüberprüfung>Datenüberprüfung… rufen wir das unten angezeigte Fenster auf und wählen in Einstellungen>Gültigkeitskriterien unter Zulassen “Liste” aus.

SNAGHTML8d454c

Als Quelle benutzen wir eine Formel anstelle eines einfachen Zellbereiches (die obige Datenüberprüfung entspricht der aus Zelle E4 im Beispiel):

=WENN(D4<>””;Liste)

Die Formel macht eine einfache Prüfung ob der Namen des Verkäufers eingegeben wurde bevor es die Region als Dropdown zur Auswahl zulässt. Das dritte Argument der WENN Formel wurde weggelassen da wir bei einer FALSCH Auswertung keine Werte anbieten wollen.

Wählen wir nun eine Zeile an in unserem Bericht der noch keinen Verkäufernamen erfasst hat, so kommt zwar das Dropdown-Symbol, lässt jedoch keine Auswahl zu.

Posted in Bedingte Formatierung, Dynamische Berichte.

Tagged with , , , .

No comments

By Lukas – April 17, 2012

Daten mit zwei Listenfelder verbinden

image

Im heutigen Beispiel zeige ich wie man Daten mit zwei Listenfelder verbinden kann um einen Wert zurückzugeben.

Excel bietet drei Varianten Listenfelder einzusetzen:

  1. Formularsteuerelemente
  2. ActiveX-Steuerelemente
  3. VBA

Im aktuellen Beispiel setze ich die Listenfelder via den ActiveX-Steuerelementen ein. Das Beispiel wäre jedoch genau so gut machbar mit den anderen beiden Varianten der Optionsfelder.

Listenfelder via Steuerelemente

Das Beispiel könnt Ihr hier als Excel Datei herunterladen: Beispiel Optionsfelder (16)

Die Matrix mit den relevanten Informationen sieht so aus:

image

Wir setzen die relevanten Kriterien die der User auslesen kann fest: Er soll anhand von dem Kanton, der Ortschaft, und des Geschäftsstellen Typ auslesen können.

Continued…

Posted in Dynamische Berichte, Steuerelemente.

Tagged with , , , , .

No comments

By Lukas – April 14, 2012

Pfund in Kilogramm umwandeln mit Excel

Excel hat eine wunderbare Formel mit der man ganz einfach verschiedenste Maßeinheiten Umwandeln und berechnen kann (ACHTUNG: das  Analyse-AddIn muss installiert sein):

=UMWANDELN(Zahl; Von_Maßeinheit; In_Maßeinheit)

Hier ein kleines Beispiel um Kilogramm in Pfund umzuwandeln:

image

Man muss für das jeweilige Mass den genauen Code kennen. Diesen findet man in der Excel Hilfe welche man am besten via der Funktion aufruft.

Welche Einheiten gibt es in UMWANDELN?

Die Einheiten welche berechnet werden können sind folgende:

Gewichte und Masse:

Gramm, Stück, Pfund (Handelsgewicht), U (Atommasseeinheit), Unze (Handelsgewicht)

Entfernung:

Meter, Feste Meile, Seemeile, Zoll, Fuß, Yard, Ångstrom, Pica

Zeit:

Jahr, Tag, Stunde, Minute, Sekunde

Es gibt auch noch Einheiten für Druck, Kraft, Energie, Potenziell, Magnetismus, Temperatur, und Flüssigmaße!

Ihr seht also, dass Maß ist ziemlich voll! :- )

Ich fand es einfach gut zu wissen dass es das gibt!

Posted in Formeln.

Tagged with .

No comments

By Lukas – April 8, 2012

Excel Kalender mit einer einzigen Formel

SNAGHTMLce51db

Es ist zwar schon etwas spät im neuen Jahr um einen Kalender zu machen, aber es ist trotzdem ein interessantes Beispiel was sich machen lässt mit ganz einfachen Excel Formeln.

Wir wollen also den Kalender erstellen aber nur eine einzige Formel dafür benutzen, so dass wir nicht immer wieder alles neu eingeben müssen.

Die Logik der Kalender Formel

Wer es sich gleich selber in Excel anschauen möchte kann den Kalender hier herunterladen (19)!

Die Formel ist aus 7 anderen Formeln zusammengesetzt: WENN, MONAT, JAHR, DATUM, HEUTE, ZEILE, und SPALTE. Hier die Kalender Formel aus der ersten Zelle welche in B4 steht und den 1. Januar berechnet:

=WENN(MONAT(DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))>ZEILE()-ZEILE(A$3);”";DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))

Kernformel

Ich habe den Kern der Formel unten in Rot angezeigt.

=WENN(MONAT(DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))>ZEILE()-ZEILE(A$3);”";DATUM(JAHR(HEUTE());ZEILE()-ZEILE(A$3);SPALTE()-SPALTE(A$3)))

Fangen wir also nur mit diesem Kernteil an um zu sehen wie das funktioniert:

Continued…

Posted in Bedingte Formatierung, Dynamische Berichte, Formeln.

Tagged with , , , , , , , .

No comments

By Lukas – April 8, 2012

Eindeutige Liste per Formel Erstellen

In diesem Beitrag zeige ich wie man eine Eindeutige Liste mit einer Formel erstellen kann.

image

Die Liste ist bewusst sehr kurz gehalten für das Beispiel, könnte aber beliebig lang sein. Das Ziel ist es aus der Liste in Spalte B eine eindeutige Liste zu erstellen in Spalte D in dem man nur Formeln benutzt.

Dies kann man machen in dem man eine Matrix Formel verwendet, so wie in Zelle D3:

{=INDEX(Liste;VERGLEICH(0;ZÄHLENWENN($D$2:D2;Liste);0);1)}

Die Formel Erklärt

Hier die Datei damit Ihr es gleich selber bei Euch in Excel anschauen könnt:Eindeutige Liste erstellen mit Formel (17)

Zwei obligatorische Hinweise:

  • Dies ist eine Matrix Formel: die geschweiften Klammern werden nicht eingegeben sondern von Excel erstellt in dem man am Schluss CTRL+SHIFT+ENTER drückt.

{=INDEX(Liste;VERGLEICH(0;ZÄHLENWENN($D$2:D2;Liste);0);1)}

  • Um Matrix Formeln zu verstehen hilft es ungemein wenn man diese Schritt für Schritt selber auswertet in Excel mit der F9 Taste!

Continued…

Posted in Formeln.

Tagged with , , , .

No comments

By Lukas – März 29, 2012

Bad Behavior has blocked 27 access attempts in the last 7 days.