Funktionen

Was können die neuen Excel-Funktionen TEXTVOR, TEXTNACH und TEXTTEILEN?

von
CSV-Format

In früheren Excelversionen musste man sich beim Trennen unterschiedlich langer Bestandteile (z.B. Namen, Ortsnamen …) ziemlich umständlich mit den Funktionen LINKS, RECHTS, FINDEN, LÄNGE behelfen. Mit den neuen Funktionen geht das jetzt erheblich einfacher.

Excel Funktion TEXTTEILEN

Die Funktion TEXTTEILEN kann immer dann genutzt werden, wenn der Inhalt einer Zelle auf mehrere Zellen aufgeteilt werden soll. Voraussetzung hierfür ist, dass zwischen den einzelnen Werten ein oder mehrere Trennzeichen vorhanden sind. Das Trennzeichen kann dabei explizit auch aus mehreren Zeichen zusammengesetzt sein!

Weiterlesen

 

Alter berechnen in Excel

von

Eine auf den ersten Blick ganz simple Aufgabenstellung hat sicher schon den ein oder anderen frustriert. Es geht um die genaue Berechnung des Alters in Excel. Sollten Sie gerade auch genau zu diesem Thema im Internet recherchiert haben, wird es Sie vielleicht trösten, dass selbst der Beitrag im offiziellen Microsoft Support fehlerhafte Lösungsvorschläge anbietet. Zum Staunen und Schmunzeln habe ich den Beitrag verlinkt, allerdings sollten Sie die angebotenen Lösungsansätze getrost ignorieren. 

Exakte Berechnungen mit der DATEDIF-Funktion

Die beste Funktion um das exakte Alter zu berechnen taucht im oben genannten Beitrag gar nicht auf. Dabei können Sie mit der Funktion DATEDIF() sehr einfach die Differenz zwischen zwei Datumswerten berechnen. Die Funktion ist auch sonst sehr versteckt und taucht weder im Funktionsassistenten noch im Register Formeln auf. Und genau aus diesem Grund möchte ich Ihnen die Funktion und ihre Einsatzmöglichkeiten in diesem Beitrag vorstellen. Die Funktion ist wie folgt aufgebaut:

Weiterlesen

 

Textdatei in Excel 365 importieren

von

Microsoft ersetzte still und heimlich in Office 365 den komfortablen Text Import Wizard der früheren Excel-Versionen durch die “Get & Transform”-Werkzeuge. Die Werkzeuge gab es parallel auch schon in den älteren Excel-Versionen und konnten über die Funktion “Neue Abfrage” aufgerufen werden. Der bisherige Text Import Assistent wurde bedauerlicherweise in Excel 365 und zuvor bereits in Excel 2016 (ab Version 1704) deaktiviert.

Text-Import Assistent

Mit den aktuellen “Get & Transform”-Werkzeugen werden die Daten durch das Herstellen einer Verbindung zur Textdatei über Power Query importiert. Die Funktion ist sehr leistungsfähig, aber für den statischen Import bestimmter Daten schlicht und einfach überdimensioniert und deutlich komplexer. Wer nur gelegentlich eine Textdatei (z.B. txt oder csv) ins Excel-Format konvertieren möchte, wird hierfür bestimmt eine möglichst schnellere und einfachere Lösung suchen.

Weiterlesen

 

Sonderzeichen Suchen und Ersetzen in Excel

von
HTML Sonderzeichen

Mit Excel werden häufig Daten aus anderen Systemen importiert, analysiert, weiterverarbeitet und anschließend für die Nutzung in anderen Programmen oder Datenbanken auch häufig wieder exportiert. Sonderzeichen können dabei häufig zu Problemen führen. In diesem Beitrag erhalten Sie Lösungsansätze für eine reibungslose Verarbeitung der Daten in Excel.

Weiterlesen

 

Mehrere Autofilter in einem Excel Tabellenblatt nutzen

von

Jeder der mit Excel arbeitet wird früher oder später mit dieser Funktion seine Erfahrungen machen. Die Rede ist hier von einer der beliebtesten Funktionen in Excel, dem Autofilter. Mit wenigen Klicks können Sie nach dem Aktivieren der Funktion über die Symbolleiste “Start” oder “Daten” beliebige Spalten Sortieren oder Filtern.

Sortieren und Filtern

Leider hat der Autofilter einen entscheidenen Nachteil. In jedem Tabellenblatt kann maximal ein Autofilter aktiviert werden. Beim Versuch für einen zweiten Zellbereich einen Autofilter zu aktivieren, verschwindet stattdessen der erste bereits gesetzte Autofilter. Mit einem Klick auf “Filtern” wird also entweder ein Autofilter eingerichtet, oder wieder entfernt, sofern bereits ein Autofilter im Tabellenblatt aktiviert ist.

Weiterlesen

 

Excel Spaltenbuchstaben ermitteln

von

In VBA wird im Allgemeinen nicht der Spaltenbuchstabe, sondern die Spaltennummer verwendet. Spalte A entspricht der Spaltennummer 1, Spalte B der Spaltennummer 2, Spalte C der Spaltennummer 3 usw.

Möchten Sie nun aus einer Spaltennummer wieder den Spaltenbuchstabe ermitteln gibt es hierfür verschiedene Ansätze, die je nach Verwendungszweck mehr oder weniger geeignet sind.

Fehlerhafter VBA Code auf der Microsoft Support Seite

Im Internet gibt es allerdings auch Lösungsansätze, die nicht ungeprüft verwendet werden sollten. So stellt z.B. selbst Microsoft auf einer Support Seite einen fehlerhaften Algorithmus für die Ermittlung des Spaltenbuchstaben vor.

Zunächst einmal ist dieser generell nur auf zwei Stellen ausgelegt. Seit der Excel 2007 sind inzwischen jedoch 16.384 Spalten bis zum Spaltenbuchstabe XFD vorhanden. Um alle Spaltenbuchstaben ermitteln zu können muss also in jedem Fall ein Algorithmus genutzt werden, der dreistellig die Spaltennummer in den jeweiligen Spaltenbuchstabe(n) umwandeln kann. Davor gab es einen Spaltenbereich der nur 256 Spalten bis Spalte IV unterstützte.

Aber auch in diesem Fall hätte der Algorithmus auf der Microsoft Seite Fehler produziert. Der erste Fehler tritt nämlich bereits bei Spalte 53 (BA) auf. Hier wirft der Microsoft Alogrithmus A[ aus. Mit jedem Durchlauf von A bis Z verschiebt sich fortan der Fehler um eine weitere Spalte (CA, CB / DA, DB, DC / EA, EB, EC, ED / …). Ab Spalte 4508 (FQJ) erzeugt das Makro dann sogar die erste Fehlermeldung.

Excel VBA Spaltenbuchstabe aus Zahl ermitteln

Nachfolgend nun zwei Lösungsvorschläge, die für alle 16.384 Spalten aus der Spaltennummer den Excel Spaltenbuchstaben ermitteln.

Weiterlesen

 

Wissenswert: Sverweis Funktion und Makro Alternative

von

Die Sverweis Formel-Funktion ist eine der beliebtesten Excel Funktionen überhaupt. Die Nutzung kann allerdings auch Probleme bereiten und die Performance bei großen Tabellen stark beeinträchtigen. In diesem Beitrag erfahren Sie, wofür Sie die Sverweis-Funktion nutzen können, worauf Sie achten müssen und schließlich was alternativ Makros leisten können. Dazu stelle ich Ihnen zwei Makros vor. Das erste Makro nutzt die Sverweis-Tabellenblatt-Funktion vlookup() in VBA und bietet somit alle Möglichkeiten der Formel-Funktion. Das zweite Makro stellt die Funktionsweise in ähnlicher Weise nach und bietet im Praxiseinsatz noch weitere Möglichkeiten, die mit der Sverweis-Funktion sonst nicht zu realisieren sind.

Teil 1: Wissenswert: Sverweis Funktion und Makro Alternative
Teil 2: Besser als SVERWEIS: Die XVERWEIS Funktion

Sverweis-Formel im Tabellenblatt
Mit der Sverweis-Funktion können Sie grundsätzlich fehlende Daten aus einer anderen Tabelle zuspielen. Haben Sie zum Beispiel eine Liste mit Postleitzahlen bei denen die Orte fehlen können Sie über einen Sverweis in einer zusätzlichen Spalte die Orte in Abhänigkeit zur Postleitzahl hinzufügen. Voraussetzung ist, dass Sie über ein Postleitzahlenverzeichnis verfügen in denen zu allen Postleitzahlen die Orte gespeichert sind. Sie benötigen also eine Datenbasis aus der Sie die fehlenden Werte zuspielen können. Es sind dabei allerdings noch drei Punkte unbedingt zu beachten.

Weiterlesen

 

Straße und Hausnummer mit Excel trennen Teil 1 (Variante ohne Makro)

von

Ein häufiges Problem ist die Übernahme von Daten, die nicht in der gewünschten Datenqualität vorliegen. Im folgenden Beitrag stelle ich eine Möglichkeit vor, wie Sie die in einer Spalte vorliegende Anschrift bestehend aus Straße und Hausnummer mit Excel trennen können und in zwei Spalten übernehmen. Die Lösung dieser Problematik ist sogar ganz ohne den Einsatz eines VBA Makros mit den Standard-Funktionen in Excel möglich.

Teil 1: Straße und Hausnummer mit Excel trennen – Variante ohne Makro
Teil 2: Straße und Hausnummer mit Excel trennen – Variante mit Makro

Vorbereitung und Datenqualität
Ich gehe für den nachfolgenden Lösungsweg davon aus, dass die Anschriften bestehend aus Straßenname und Hausnummer untereinander in Spalte A stehen. Die Spalten B bis F werden im folgenden für Hilfsspalten und die Ergebnisse benötigt, sollten daher vollständig leer sein. Am Ende des Beitrags steht eine Beispieldatei mit den Formeln zum Download als Muster bereit.

Straße Hausnummer trennen mit Excel

Insgesamt benötigen Sie 5 Formeln um ans Ziel zu kommen. Eine Besonderheit gibt es am Ende noch für Sonderformen der Hausnummer zu berücksichtigen. Darauf komme ich am Ende des Beitrags nochmal zurück. Ich gehe für die folgenden Schritte zunächst von der idealen Datenlage (z.B. “Hartenthaler Str. 22” oder “An der Promenade 6”) aus. Es können mehrere Leerzeichen im Straßenname vorhanden sein und es besteht zudem immer ein Leerzeichen zur Abgrenzung der Hausnummer.

Weiterlesen

 

Excel VBA – Datei Öffnen Dialog

von
Öffnen Dialog

Gelegentlich werden Sie für eine bestimmte Aufgabe verschiedene Dateien benötigen. Das kann z.B. vorkommen wenn Sie ein Excel-Tool nutzen, dass die Bearbeitung, Datenerfassung und/oder die Erstellung von Schriftwechseln übernimmt und hierfür die Daten aus einer extern gespeicherten Adressliste, Anbieterliste etc. über ein Makro eingelesen und verarbeitet werden soll.

Heute stelle ich Ihnen zwei kleine VBA-Bausteine vor mit denen Sie während eines Makroablaufs an einer beliebigen Stelle diesen Vorgang in den automatisierten Ablauf integrieren können.

Weiterlesen

 

Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen

von

Wenn Sie eine Tabelle in Excel haben bei der Sie die Spaltenüberschriften an der Stelle der Zeilenüberschriften und umgekehrt haben wollen, können Sie das ganz einfach ohne großen Aufwand mit einer Excel-Standardfunktion lösen. Das geht sehr schnell und ist wesentlich einfacher, als ein entsprechendes Makro zu programmieren.

Teil 1: Mit unterschiedlichen Varianten die letzte benutzte Zeile oder Spalte ermitteln/
Teil 2: Leere Zeilen in Exceltabellen löschen
Teil 3: Doppelte Zeilen löschen
Teil 4: Spalten und Zeilen in Excel Tabellen ohne großen Aufwand tauschen

Allerdings müssen Sie die Funktion erst einmal finden. Die Lösung dieses Problems ist über das Fenster „Inhalte einfügen“ und der darin enthaltenen Funktion „Transponieren“ zu finden.



Abbildung 1
Das Fenster „Inhalte einfügen“ können Sie entweder über „Bearbeiten“ in der Menüleiste oder über drücken der rechten Maustaste im Tabellenbereich auswählen.

Weiterlesen