Gelegentlich befinden sich in Excel-Tabellen Zellen, die augenscheinlich Zahlen enthalten, deren Summe aber den Wert 0 ergibt. Diese Zahlen sind als Text formatiert. Ich nenne sie Text-Zahlen. Häufig entstehen solche Text-Zahlen durch Datenbank - Exporte (Screenshot 1).

Nachfolgend stelle ich zwei Wege vor, wie man aus diesen Text-Zahlen trotzdem eine ordentliche Summe bilden kann.

Aufgabe:

Angenommen ich habe eine Liste von Wörtern (blaue Tabelle im Bild rechts) und möchte feststellen, ob diese Wörter in einer anderen Liste (grüne Tabelle im Bild links) vorkommen. Dabei sollen auch die Vorkommen gefunden werden, in denen das Wort nur ein Teil des Textes ist. Groß- und Kleinschreibung soll dabei keine Rolle spielen.

• In der ersten Spalte der grünen Tabelle steht der Text, der durchsucht werden soll.
  Wir untersuchen also beispielsweise, ob in dem Text Mehrfamilienhaus ein Text aus der blauen Tabelle vorkommt, also Haus, Baum, Fluss oder Stein.
• In der zweiten Spalte soll der gefundene Text aus der blauen Spalte stehen. In dem Fall von Mehrfamilienhaus ist es das Wort Haus.
• #NV wird ausgegeben, wenn kein Wort aus der blauen Tabelle im Suchtext vorkommt.

Wer regelmäßig Outlook-Kontaktgruppen (Verteiler) erstellt und pflegt, der hat sich vielleicht schon einmal Gedanken darüber gemacht, wie sich diese Arbeit vereinfachen lässt.

Vor einiger Zeit habe ich dazu eine kleine Excel-Anwendung geschrieben
(Siehe Screenshot oben).

=WERT(WECHSELN(WECHSELN(WECHSELN(WECHSELN(B4;"$";"");".";"#");",";"");"#";","))

Die Aufgabenstellung lautete,

• aus einer Datentabelle nur gefilterte Daten auf ein anderes Tabellenblatt, eine Art Dashboard, zu kopieren.
• Dabei sollten die Filterkriterien dynamisch angepasst werden können.
• Die gefilterten Ergebnisse sollten nach Anpassen der Filter mit einem Klick aktualisiert werden können.

Wie das realisiert werden kann, möchte ich hier gern vorstellen.

Über eine Anzahl von Tabellenblättern ist die Summe aus einer bestimmten Zelle auf diesen Blättern zu bilden. Die Tabellenblätter sind dabei alle gleich aufgebaut.

Es werden ständig neue Tabellenblätter in die Arbeitsmappe aufgenommen oder von dort entfernt. Die Formel für die Berechnung der Summe soll sich davon nicht beeindrucken lassen.

Wie man das geschickt anstellen kann, möchte ich hier zeigen.

In dieser Excel-Mappe (siehe Download) ist ein Suchwort versteckt. Es ist recht einfach zu erraten. Etwas schwieriger könnte es sein, das Suchwort zu ändern. Schaffst Du es?

Kürzlich stand ich vor der Aufgabe, ein Säulendiagramm mit zwei Datenreihen zu erstellen.

Allerdings waren die Größenordnungen der beiden Datenreihen sehr unterschiedlich. Es musste eine Sekundärachse her.

1. Fügt man die Sekundärachse ein, so liegen die beiden Säulen danach allerdings übereinander, was nicht gerade schön aussieht (siehe Bild oben). Wie schafft man es, die beiden Säulen nebeneinander anzuordnen?

SVERWEIS() ist eine super EXCEL-Funktion, mit der man nach Informationen in einem Zellenbereich (Matrix genannt) suchen kann. Dabei sucht SVERWEIS in der jeweils ersten Spalte dieser Matrix nach einem bestimmten Wert. Ist der Wert gefunden, kann jeder Wert, der in der gleichen Zeile dieser Matrix steht, zurückgegeben werden (Parameter: Spaltenindex). Eine nette Anleitung dazu findet man im Internet zum Beispiel hier:

https://support.office.com/de-de/article/SVERWEIS-Funktion-0BBC8083-26FE-4963-8AB8-93A18AD188A1

Was ist aber nun, wenn das Suchkriterium, nur Teil der Zeichenketten in der ersten Spalte einer Matrix ist? Für diesen Fall möchte ich drei Lösungen vorstellen.

Das komplette Modul stelle ich unten zum Download bereit.

Seit der DSGVO wünschen sich viele Kunden, dass ihre Daten verschlüsselt werden. Also wurde das Access-Backend eines Kunden mit einem Passwort verschlüsselt. Es gibt in dieser Firma aber auch Anwendungen in Word und Excel, die auf diese Datenbank zugreifen und sich Informationen beispielweise für Kundenbriefe oder Kalkulationen ziehen. Während die Word-Anwendung auch mit der verschlüsselten Datenbank super funktioniert hat, meldete die Excel-Anwendung: "Kein zulässiges Kennwort." 

In beiden Fällen wurde die Connection via VBA auf die komplett gleiche Art und Weise hergestellt:

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=[Backend-Dateiname mit Pfad]; Jet OLEDB:Database Password=[Passwort]"

Seit Office 2013 steht der Befehl "Zuletzt verwendete Datei öffnen" auf der GUI nicht mehr für die Übernahme in die Symbolleiste für den Schnellzugriff zur Verfügung. Statt dessen gibt es den Befehl "Öffnen", der zur Liste der zuletzt verwendeten Dokumente (Backstage) führt. Auch der Befehl "Öffnen (Strg+O)", der direkt die Auswahl einer Datei aus dem Office-Öffnen-Dialog ermöglicht, fehlt. Nennt mich altmodisch, aber ich mag die beiden. Ich möchte Euch zeigen, wie man diese Befehle wieder in die Schnellstartleiste aufnimmt.

Benutzerdefinierte VBA-Tabellenfunktionen (User Defined Functions) sind ein mächtiges Instrument, um Excel spezifische Geschäftslogik beizubringen. Zudem verbessern sie entscheidend die Lesbarkeit von Excel-Formeln. Wer hat nicht schon über ein mehrfach verschachteltes Funktionsungetüm gegrübelt, um herauszubekommen, was da eigentlich gerechnet wird?

Manchmal braucht man benutzerdefinierte Funktionen, die immer neu berechnet werden, wenn sich irgendetwas in der Excel-Mappe ändert. Solche Funktionen nennt man volatil. Ein Beispiel für eine volatile Funktion ist übrigens die integrierte Funktion JETZT().  

Es ist sehr einfach, eine benutzerdefinierte Funktion volatil zu machen.

Das war für mich eine Überraschung!

Bisher habe ich neue Zeilen in meine (intelligenten) Tabellen eingefügt, indem ich mit der Maus am Tabellen-Ende-Kennzeichen (blauer Haken unten rechts) gezogen habe.

Kürzlich habe ich nun gelernt, dass auch in Excel, so wie in Word, mit der Tab-Taste neue Zeilen an eine Tabelle angefügt werden können und zwar auch,  wenn eine Ergebniszeile vorhanden ist (Bild1).

Inhalte unterhalb der Tabelle werden automatisch nach unten verschoben.

Super! Das macht mein Leben leichter.

Schön wäre es, wenn sich Zahlen unabhängig von der Zahl ihrer Dezimalstellen am Komma ausrichten würden (Siehe Bild 1 rechte Spalte).

Hilfreich ist da ein benutzerdefinertes Zahlenformat.

Vielen Dank den findigen Excel-Freaks, die eine Excel-Berechnung daraus abgeleitet haben.

Und so sieht sie aus:

SVERWEIS und WVERWEIS sind prima, helfen aber nicht, wenn mehr als eine Suchbedingung ausgewertet werden muss. Vor Kurzem stand ich vor dieser Aufgabe und bin über folgende, wie ich finde, geniale Lösung gestolpert (http://www.excelformeln.de/formeln.html?welcher=48).

=VERWEIS(1;1/(A3:A12&B3:B12=E3&F3);C3:C12)

Vielleicht kennt Ihr das ja schon, aber falls nicht, dann kann dieser kleine Tipp das Leben mit Excel schon ganz schön erleichtern.

Will man Zellenbereich per Drag&Drop an eine andere, nicht leere Stelle des Tabellenblatts schieben, dann bekommt man die Mitteilung: „Hier gibt es schon Daten. Möchten Sie diese ersetzten?“. Der Zielbereich wird also überschreiben.

Mit einem kleinen Trick kann man den markierten Zellenbereich auch so verschieben, dass er an der Zielposition eingefügt wird.

Für die Anrede im Brief ist durchaus einiges zu beachten:

1. Gibt es überhaupt einen Ansprechpartner, oder kenne ich nur den Namen der Firma oder der Institution?
2. Ist der Ansprechpartner ein Herr oder eine Dame?
3. Hat der Ansprechpartner einen Titel?
4. Träger von Amtsbezeichnungen, zum Beispiel Professoren, werden nur mit Ihrem Amt, nicht mit dem Nachnamen angesprochen.

Diese Logik kann man bereits in die Empfängerliste des Sereinbriefes etablieren. Damit wird es auf dem Hauptdokument einfacher.

Hier die Formel:

Wer für Serienbriefe Empfängerlisten aus Excel verwendet, kennt vielleicht das Problem: Werden von dort Uhrzeiten im Serienbrief verwendet, dann erscheint die Uhrzeit im amerikanischen Format (Siehe Bild links).

Abhilfe schafft der Formatschalter \@ "HH:mm" (Siehe Bild rechts).

Von diesen Formatschaltern gibt es viele.

Nützlich ist auch \# "00000" um beispielsweise Postleitzahlen, die in Excel als Zahlen eingetragen sind, immer mit 5 Stellen, also auch mit führenden Nullen, darstellen zu können.

Um einen Geldwert zu formatieren, könnte man beispielsweise den Formatschalter

\# "#.##0,00 €" verwenden. Es werden der Tausender-Trenner, zwei Dezimalstellen und das Euro-Zeichen ausgegeben.

Aktuell finde ich auf den Seiten von Microsoft keine schöne Auflistung aller Formatschalter. Hier (Liste der Feldfunktionen) sind bei den jeweiligen Feldfunktionen immerhin auch Informationen zu Formatschaltern zu finden. Leider sind die nicht vollständig.