Das VorlagenlaborExcel-Bausteine
Diese Seite wendet sich an alle, die ein wenig Übung in Excel haben und neugierig auf Tipps und Hinweise sind, wie man bestimmte Aufgabenstellung aus der täglichen Praxis, die auf den ersten Blick mit den Bordmitteln von Excel nicht lösbar erscheinen, doch meistern kann. Die Beispiele zeigen Lösungsansätze mit und ohne VBA. Sie enthalten eine Demonstration der Lösung und eine kurze Erläuterung, so dass sie der geübte Anwender nachvollziehen und für seine Belange anpassen kann. Wir nennen sie deshalb Bausteine. Alle Beispiele können kostenlos als Excel-Arbeitsmappe herunter geladen werden.
Inhalt
- Balkendiagramm mit linksbündiger Rubrikenbeschriftung
- Intelligente Gültigkeitsliste
- Zählen der Häufigkeit von Einträgen in einem Logbuch
- Zweifache Sortierung einer Tabelle mit Hilfe von Formeln
- Diagramm mit unterbrochenen Säulen
- Statussignale per Dropdown
- Sortieren des Textinhalts einer Zelle
- Alphabetisch sortierte Liste
- Hervorheben von Diagrammpunkten
- Erstellen Zuordnungsmatrix
- Unikate extrahieren
- Erstellen eines Tabellenauszugs
- Auflösen einer Zuordnungsmatrix
- Summenerhaltendes Runden
- Automatisches Sortieren einer Tabelle fester Länge
- Automatisches Darstellen einer aus einer Liste ausgewählten Datenreihe in einem Säulendiagramm
- Automatische Zusammenfassung mehrerer kleiner Werte im Kreisdiagramm
- Spitzenwerte in Liniendiagramm hervorheben
- "Formatieren" im wissenschaftlichen Zahlenformat
- Checkliste per Mausklick abhaken
- Bedingtes Blinken einer Zelle
- Alternative Eingaben zulassen
Balkendiagramm mit linksbündiger Rubrikenbeschriftung
In Balkendiagrammen wird die Rubrikenbeschriftung an der vertikalen Achse standardmäßig und unbeeinflussbar rechtsbündig ausgerichtet. Das ist optisch nicht immer glücklich. Hier wird ein Weg gezeigt, wie mit Hilfe eines gestapelten Balkendiagramms und unter Einbeziehung der Datenbeschriftung sowie einiger Formatierungstricks eine linksbündige Anzeige der Rubriken möglich ist.
Keywords: Tabellenkalkulation, Balkendiagramm, Gestapelte Balken, Datenbeschriftung
Intelligente Gültigkeitsliste
Die Verwendung einer Gültigkeitsliste (zu finden unter Datenüberprüfung) ist ein probates Mittel, den Nutzer vor fehlerhaften oder unzulässigen Eingaben zu bewahren. Hier wird eine VBA-Routine vorgestellt, die nach der Eingabe einen Bezug zur Gültigkeitsliste herstellt. Wenn später ein Eintrag in der Gültigkeitsliste ersetzt wird, wird diese Änderung auch in den bereits ausgefüllten Zellen wirksam. Diese Lösung enthält VBA.
Keywords: Gütigkeit, Datenüberprüfung, Worksheet Change Ereignis, VBA
Zählen der Häufigkeit von Einträgen in einem Logbuch
Eine Excel-Tabelle enthält eine logbuch-ähnliche Liste. Es soll mit Hilfe von Formeln (ohne VBA) ermittelt werden, wie oft bestimmte Einträge im Logbuch vorkommen, wobei mehrere Einträge pro Tag nur einmal gezählt werden. Anders ausgedrückt: An wie vielen Tagen kommt ein bestimmter Eintrag im Logbuch vor. Das Ergebnis ist eine Häufigkeitstabelle aller möglichen Einträge.
Keywords: Tabellenkalkulation, Tabelle, Unikate, Häufigkeit, RANG.GLEICH, ZÄHLENWENN, BEREICH.VERSCHIEBEN, Matrixformel
Zweifache Sortierung einer Tabelle mit Hilfe von Formeln
In einer Excel-Arbeitsmappe wird automatisch ein Abbild einer Tabelle erzeugt, das gegenüber der Ursprungstabelle nach zwei Feldern aufsteigend sortiert ist. Das geschieht ohne Zutun des Anwenders (d. h. ohne die Benutzerdefinierte Sortierung des Autofilters). Es werden allein Formeln zu Hilfe genommen, auf den Einsatz von VBA wird verzichtet.
Keywords: Tabellenkalkulation, Tabelle, Sortieren, ZÄHLENWENNS, ANZAHL2, SUMME, RANG.GLEICH, BEREICH.VERSCHIEBEN, Namen in Formeln, INDEX, WENN, WENNFEHLER, VERGLEICH, ZEILE, Matrixformel, SVERWEIS
Diagramm mit unterbrochenen Säulen
Bei Säulendiagrammen mit großen Zahlenwerten werden die Säulen oft abgeschnitten, um die Einzelheiten an den Säulenspitzen besser darzustellen. Das birgt aber auch die Gefahr der Verfälschung der Aussage, da die Darstelllung nicht maßstabgetreu ist. Deshalb werden die Säulen gern als unterbrochene Säulen gezeigt, um anzuzeigen, dass sie in Wirklichkeit höher sein müssten. In dieser Beispieldatei wird gezeigt, wie man solche unterbrochene Säulen in einem Excel-Diagramm erzeugen kann.
Keywords: Säulendiagramm, Unterbrechung, Primärachse, Sekundärachse, Gestaffelte Säulen
Statussignale per Dropdown
Es wird demonstriert, wie die bedingte Formatierung genutzt wird, um den Status einer Aufgabe mit Symbolen (Ampelsignalen) anzuzeigen. Da diese Formatierung nur funktioniert, wenn die Zelle, in der das Symbol angezeigt wird, einen Zahlenwert enthält, wird ein Weg gezeigt, wie der Nutzer diese Zahlen "eingibt", indem er aus einer Dropdownliste die Begriffe rot, gelb oder grün auswählt.
Keywords: Bedingte Formatierung, Symbol, Ampel, Datenüberprüfung, Gültigkeit, Gültigkeitskriterien, Zellendropdown, Benutzerdefiniertes Format
Sortieren des Textinhalts einer Zelle
Es wird eine benutzerdefinierte Funktion vorgestellt, die den Inhalt einer Textzelle sortiert und zurückgibt. Dabei wird der Textinhalt der Zelle in Elemente zerlegt, die durch ein bestimmtes Trennzeichen separiert sind. Das Trennzeichen wird der Funktion als Argument mitgegeben. Die sortierten Textelemente werden wieder unter Verwendung desselben Trennzeichens zusammengefügt und als Zeichenfolge zurückgegeben. Die Funktion ist geeignet, um Aufzählungen in einer Textzelle, die z. B. durch Komma oder Semikolon getrennt sind, alphabetisch zu sortieren. Auch Auflistungen mit Zeilenumbruch lassen sich so sortieren. Diese Arbeitsmappe enthält Programmcode (VBA/Makro).
Keywords: Benutzerdefinierte Funktion, VBA Code, Text, Liste, Sortieren, Trennzeichen
Alphabetisch sortierte Liste
Es wird ein Weg gezeigt, wie in einer Excel-Arbeitsmappe eine alphabetisch sortierte Kopie einer Liste mit Texteinträgen erstellt werden kann, ohne dabei VBA zuhilfe zu nehmen.
Keywords: Text, Sortieren, alphanumerisch, Zählenwenn-Funktion, Index-Funktion, Vergleich-Funktion
Hervorheben von Diagrammpunkten
Es liegt eine Datenreihe vor, die in einem Liniendiagramm dargestellt werden soll. Dabei sollen die Datenpunkte für Werte, die einer bestimmten Bedingung genügen durch Farbe und Form automatisch unterschieden werden (ohne Verwendung von VBA).
Keywords: Liniendiagramm, Formatieren Diagrammpunkt, NV()
Erstellen Zuordnungsmatrix
In einer Tabelle sind Objekte (Artikel/Erzeugnisse) und dazugehörige Attribute (d. h. Merkmale, Eigenschaften, Komponenten, Zubehör, Einzelheiten, Kategorien o. ä.) aufgelistet. Diese Liste von Zuordnungspaaren wird in eine Matrix überführt, die die Zuordnung veranschaulicht, indem an den zutreffenden Kreuzungspunkten von Objekt und Eigenschaft ein "x" eingetragen wird. Die Aufgabenstellung ist umgekehrt zu der in Auflösen einer Zuordnungsmatrix. Die Lösung verwendet kein VBA.
Keywords: Matrix, Zuordnung, Index-Funktion, Bereich.Verschieben-Funktion, Anzahlleerezellen-Funktion, Zeile-Funktion, Wenn-Funktion, Vergleich-Funktion, Nicht-Funktion, Istnv-Funktion, Spalte-Funktion, Bedingte Formatierung
Unikate extrahieren
Aus einer listenförmigen Tabelle mit Datenbankcharakter wird für eine bestimmte Spalte / ein bestimmtes Attribut eine separate, gefilterte Tabelle erzeugt, die nur die Unikate auflistet. Die Lösung verwendet kein VBA.
Neu: In dieser überarbeiteten Version wird gezeigt, wie die Unikatliste mit einer einzigen Formel ohne Hilfsspalten erzeugt werden kann. Es wird ein Weg gezeigt, wie die relativ komplizierte Formel bei der Verwendung in verschiedenen Tabellen einfach angepasst werden kann.
Keywords: Liste, Datenbank, Unikate, Eindeutige Werte, Tabellenauszug, Anzahl2-Funktion, Wenn-Funktion, IstNV-Funktion, Vergleich-Funktion, Zeile-Funktion, Bereich.Verschieben-Funktion, Zählenwenn-Funktion, Index-Funktion, Matrixformel
Erstellen eines Tabellenauszugs
Aus einer listenförmigen Tabelle mit Datenbankcharakter wird eine separate, gefilterte Tabelle erzeugt, die nur die Zeilen/Datensätze enthält, die einem bestimmten Filterkriterium genügen. Die Lösung verwendet kein VBA.
Keywords: Liste, Datenbank, Filter-Kriterium, Tabellenauszug, Anzahlleerezellen-Funktion, Vergleich-Funktion, Index-Funktion, Wenn-Funktion
Auflösen einer Zuordnungsmatrix
Aus einer Matrix mit Zuordnungen von Eigenschaften (Merkmalen, Komponenten, Zubehör, Einzelheiten) zu Objekten (Artikeln, Erzeugnissen) wird eine Liste erzeugt, die alle einzelnen Zuordnungspaare listenförmig darstellt. Die Aufgabenstellung ist umgekehrt zu der in Erstellen Zuordnungsmatrix. Die Lösung verwendet kein VBA.
Keywords: Matrix, Zuordnung, Zählenwenn-Funktion, Bereich.Verschieben-Funktion, Sverweis-Funktion, Vergleich-Funktion, Zahlenformat
Summenerhaltendes Runden
Beim Runden von Prozentwerten tritt häufig der Fall ein, dass die Summe der gerundeten Werte von 100% abweicht. Es wird eine Methode gezeigt, wie berechnete Prozentwerte automatisch so angepasst werden, dass die Summe der gerundeten Prozentwerte genau 100% ergibt (summenerhaltendes Runden).
Nun mit vereinfachter Formel und Behandlung des Sonderfalls Null.
Keywords: Runden-Funktion, kumulierter Rundungsfehler, summenerhaltendes Runden, Prozente, 100%
Automatisches Sortieren einer Tabelle fester Länge
Eine Tabelle fester Länge liegt in geordneter Form (z. B. alphabetisch sortiert nach Bezeichnung, aufsteigend nach Schlüssel) vor. Die Reihenfolge der Einträge ist unveränderlich. Die Zahlen werden regelmäßig aktualisiert. Die grafische Darstellung der Werte soll aber bei jeder Aktualisierung in absteigender Reihenfolge erfolgen. Dazu wird ein sortiertes Abbild der Tabelle erstellt. Die Lösung verwendet kein VBA.
Keywords: Rang-Funktion, Bereich.Verschieben-Funktion, Zählenwenn-Funktion, Vergleich-Funktion, Index-Funktion, Sverweis-Funktion, Diagramm, Balkendiagramm, Verkaufszahlen, Kgrösste-Funktion
Automatisches Darstellen einer aus einer Liste ausgewählten Datenreihe in einem Säulendiagramm
Aus einer Excel-Tabelle soll eine beliebige Zeile ausgewählt und als Grafik angezeigt werden. Wenn zum Beispiel die Tabelle die Verkaufszahlen für eine Reihe von Artikeln für die 12 Monate des Jahres enthält, dann soll die Auswahl eines Artikels dazu führen, dass die Verkaufszahlen genau dieses Artikels für alle Monate in einer Grafik dargestellt werden. VBA soll dabei nicht zum Einsatz kommen.
Keywords: Diagramm, Säulendiagramm, Verkaufszahlen, Datenreihe, Benannter Bereich, Bereich.Verschieben-Funktion, Vergleich-Funktion
Automatische Zusammenfassung mehrerer kleiner Werte im Kreisdiagramm
In einem Excel-Kreisdiagramm werden die kleineren Werte automatisch zu einer Kategorie zusammengefasst. Durch Eingabe eines Mindestwerts wird gesteuert, welche Kategorien zusammengefasst werden. Die Lösung kommt ohne VBA aus.
Keywords: Kreisdiagramm, Tortengrafik, Bereich.Verschieben-Funktion, Dynamisches Diagramm
Spitzenwerte in Liniendiagramm hervorheben
In einem Excel-Liniendiagramm werden Diagrammpunkte automatisch durch andere Farbe oder Form hervorgehoben, wenn sie einen bestimmten Wert überschreiten. Die Anzeige wird automatisch angepasst, wenn die Werte in der zugehörigen Tabelle sich ändern.
Anmerkung: Eine Lösung ohne VBA (Makrocode) ist unter "Hervorheben von Diagrammpunkten" zu finden.
Keywords: VBA, Chart_Activate-Ereignis, Liniendiagramm, Hervorhebung, Datenpunkte
"Formatieren" im wissenschaftlichen Zahlenformat
Eine Funktion gibt Mantisse und Zehnerpotenz eines Wertes zurück, so dass er alternativ zum standardmäßig von Excel angebotenen wissenschaftlichen Format mit hochgestelltem Exponenten zur Basis 10 dargestellt werden kann.
Keywords: VBA, Benutzerdefinierte Funktion, Zahlenformat, Exponentialschreibweise
Checkliste per Mausklick abhaken
Beim Doppelklick auf eine Checkbox in einer Checkliste in einem Excel-Arbeitsblatt wird diese abgehakt und grün angezeigt. Mit einem weiteren Doppelklick kann das Abhaken wieder rückgängig gemacht werden. Die leere Checkbox wird rot angezeigt.
Keywords: VBA, Worksheet_BeforeDoubleClick-Ereignis, Wingdings, Checkliste, Checkbox, Abhaken
Bedingtes Blinken einer Zelle
Das Beispiel zeigt, wie man eine Zelle in einem Excel-Arbeitsblatt in Abhängigkeit von einer Eingabe kurzzeitig aufblinken lassen kann. Damit können zum Beispiel Fehleingaben oder Treffer signalisiert werden.
Keywords: VBA, Worksheet_Change-Ereignis, Timer, Blinken, Trefferereignis
Alternative Eingaben zulassen
In einer Eingabespalte sind Werte in einer bestimmten Währung einzugeben. Liegt der Betrag in einer Fremdwährung vor, wäre eine Formel oder der umgerechnete Wert einzugeben, was nicht sehr benutzerfreundlich ist. Das Beispiel zeigt eine benutzerfreundliche Variante, bei der der Fremdwährungsbetrag in die benachbarte Zelle geschrieben wird und Excel automatisch die Formel in die Eigenwährungsspalte schreibt. Das Beispiel kann für jede Art von Umrechnung zweier Größen mit einem festen Umrechnungsverhältnis benutzt werden, z. B. kW und PS oder °C und °F.
Keywords: VBA, Worksheet_Change-Ereignis, Umrechnungen