Logo vorlab.de - Das Vorlagenlabor

Home > Excel-Bausteine

Excel-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

Vorschaubild Linksbündige Balkenbeschriftung 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

Herunterladen...

Pfeil nach oben

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

Herunterladen...

Pfeil nach oben

Zählen der Häufigkeit von Einträgen in einem Logbuch

Vorschaubild 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

Herunterladen...

Pfeil nach oben

Zweifache Sortierung einer Tabelle mit Hilfe von Formeln

Vorschaubild Zweifachsortierung 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

Herunterladen...

Pfeil nach oben

Diagramm mit unterbrochenen Säulen

Bild Unterbrochene 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

Herunterladen...

Pfeil nach oben

Statussignale per Dropdown

Vorschaubild Ampelsignale 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

Herunterladen...

Pfeil nach oben

Sortieren des Textinhalts einer Zelle

Vorschaubild Liste sortieren 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

Herunterladen...

Pfeil nach oben

Alphabetisch sortierte Liste

Vorschaubild Alphabetisches Sortieren 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

Herunterladen...

Pfeil nach oben

Hervorheben von Diagrammpunkten

Vorschaubild Extremwerte hervorheben 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()

Herunterladen...

Pfeil nach oben

Erstellen Zuordnungsmatrix

Vorschaubild Zurodnungsmatrix erstellen 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

Herunterladen...

Pfeil nach oben

Unikate extrahieren

Vorschaubild Eindeutige Werte ermitteln 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

Herunterladen...

Pfeil nach oben

Erstellen eines Tabellenauszugs

Vorschaubild Tabellenauszug erzeugen 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

Herunterladen...

Pfeil nach oben

Auflösen einer Zuordnungsmatrix

Vorschaubild 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

Herunterladen...

Pfeil nach oben

Summenerhaltendes Runden

Vorschaubild 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%

Herunterladen...

Pfeil nach oben

Automatisches Sortieren einer Tabelle fester Länge

Vorschaubild Automatisches Sortieren 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

Herunterladen...

Pfeil nach oben

Automatisches Darstellen einer aus einer Liste ausgewählten Datenreihe in einem Säulendiagramm

Vorschaubild Ausgewählte Datenreihe 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

Herunterladen...

Pfeil nach oben

Automatische Zusammenfassung mehrerer kleiner Werte im Kreisdiagramm

Vorschaubild Restanteil 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

Herunterladen...

Pfeil nach oben

Spitzenwerte in Liniendiagramm hervorheben

Vorschaubild Punkt im 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

Herunterladen...

Pfeil nach oben

"Formatieren" im wissenschaftlichen Zahlenformat

Vorschaubild Exponentialschreibweise 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

Herunterladen...

Pfeil nach oben

Checkliste per Mausklick abhaken

Vorschaubild Checkliste 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

Herunterladen...

Pfeil nach oben

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

Herunterladen...

Pfeil nach oben

Alternative Eingaben zulassen

Vorschaubild Alternative Eingaben 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

Herunterladen...

Pfeil nach oben