Inhalte auf mehrere Spalten aufteilen mit 3 neuen Excel-Funktionen
Microsoft spendiert allen, die in Excel 365 Daten aus fremden Systemen bearbeiten müssen, neue Funktionen. Eine der typischen Aufgaben lässt sich damit einfacher erledigen: Inhalte auf mehrere Spalten aufteilen. Als Alternative zum Befehl Text in Spalten oder zu komplizierten Formellösungen mit LINKS, RECHTS, TEIL, FINDEN etc. hält Excel 365 jetzt neue Funktionen bereit: TEXTNACH, TEXTVOR und TEXTTRENNEN.
Mit 3 neuen Funktionen aus der Kategorie TEXT die Inhalte auf mehrere Spalten aufteilen
Das folgende Beispiel zeigt, welche Erleichterung diese 3 neuen TEXT-Funktionen bringen.
Dynamisch wachsende Auswertung mit nur 3 Formeln als Alternative zu Pivot
Sollen Listen ausgewertet werden, denken viele sofort an Pivot-Tabellen. Dass es auch ohne Pivot geht, zeigt die unten rechts gezeigte Umsatzauswertung nach Artikeln und Regionen.
Die Auswertung rechts entsteht mit nur drei Formeln
Mit nur drei Formeln wird diese Auswertung aufgebaut:
- die erste Formel listet in Spalte H die Artikel in sortierter Reihenfolge auf,
- die zweite sorgt für die Spaltenüberschriften ab Spalte I – natürlich auch sortiert,
- die dritte summiert die Umsätze aus der links gezeigten grauen Umsatzliste nach Artikeln und Regionen.
Dank der Formeln ist die Auswertung komplett dynamisch: kommen neue Artikel oder Regionen hinzu, erweitert sie sich automatisch nach unten bzw. nach rechts. Mit den folgenden Schritten wird die Auswertung erstellt.
Endlich: AutoVervollständigen für Dropdownlisten in Excel 365
Die Eingabe von Daten in Excel ist oft lästig und zeitraubend. Dropdownlisten sind eine Möglichkeit, das zu beschleunigen und fehlerhafte Eingaben zu vermeiden. Allerdings macht das Scrollen durch lange Dropdownlisten keinen Spaß.
Dank der Neuerung AutoVervollständigen für Dropdownlisten hat das jetzt ein Ende! Excel 365 vergleicht automatisch den Text, den ich eingebe, mit allen Elementen in der Liste und zeigt mir nur die übereinstimmenden an. Dabei findet Excel 365 nicht nur Listeneinträge, die mit meiner Eingabe beginnen, sondern auch solche, die meinen Suchtext enthalten.
Die hier vorgestellte neue Funktion gibt es laut Microsoft bisher nur im Betakanal.
Mehr zu den Neuerungen in Excel 365 und 2021 gibt es bei den Excel-Kompetenztagen vom 27. bis 29. Juni in der Kursreihe 1. Der Flyer enthält alle Informationen zum Ablauf und zu den Inhalten der insgesamt 36 Kurse.
Flyer der Excel-Kompetenztage herunterladen
3 neue Befehle erleichtern Orientierung und Navigation in Arbeitsmappen
Spektakuläre Excel-Neuerungen wie XVERWEIS oder dynamische Arrayfunktionen sind vielen Anwendern bekannt und werden inzwischen gern genutzt. Andere Neuerungen hingegen bleiben nahezu unbemerkt, obwohl sie für ALLE Excel-Anwender hilfreich sind. Wer hat Sheetnummer, Arbeitsmappenstatistiken und Navigationsbereich schon entdeckt und ausprobiert? Alle drei Befehle hat Microsoft in Excel 365 eingebaut.
Perfekt für Vergleiche: Nur bestimmte Daten im Diagramm zeigen
Das Diagramm ist ansprechend formatiert, alle Daten werden korrekt angezeigt. Doch was sagt das Diagramm eigentlich aus?
- Wir alle wissen: Je mehr Daten ein Diagramm enthält, desto schwerer fällt der Überblick.
- Abhilfe schafft das Fokussieren auf bestimmte Daten.
- Vergleiche werden so erleichtert und die Aussagekraft des Diagramms wird besser.
Das folgende Video zeigt das Problem und eine mögliche Lösung.
Excel 365: Dynamische Zeitleisten statt Gantt-Diagramm mit SEQUENZ
Um Laufzeiten von Projekten, Aufgaben, Verträgen oder auch Wartungsintervallen aufzuzeigen, sind Zeitleisten ein bewährtes Mittel. Sie lassen sich per Gantt-Diagramm oder über die Bedingte Formatierung aufbauen. In Excel 365 reicht jetzt eine einzige Formel, um die erforderlichen Datumsangaben für die Zeitleisten zu erzeugen. Dazu nutze ich die neue Arrayfunktion SEQUENZ.
Bild 1: Mit nur einer Formel wird in Zeile 4 die Datumsleiste dynamisch erzeugt und ab Zeile 5 sorgt eine Formel in der Bedingten Formatierung für die korrekte Position und Länge der farbigen Balken
Die neue Funktion SORTIERENNACH: Eine Produktliste nicht alphabetisch, sondern automatisch nach Umsatz sortieren lassen
Wie lässt sich eine Liste aller Produkte und deren Umsätze erstellen? Viele nutzen dafür eine Pivot-Tabelle. Soll noch nach Umsatz sortiert werden, ist das in Pivot schnell eingestellt. Allerdings muss die Pivot-Tabelle jedes Mal aktualisiert werden, wenn sich die Umsatzzahlen ändern. Dieses lästige manuelle Aktualisieren entfällt, wenn die neuen dynamischen Arrayfunktionen in Excel 365 verwendet werden.
Im Blogbeitrag vom 11.7.2021 habe ich gezeigt, wie eine alphabetisch sortierte Produktliste mit Umsatzwerten mit Hilfe der Funktionen SORTIEREN, EINDEUTIG und SUMMEWENN entsteht. Heute kommt das i-Tüpfelchen hinzu: die automatische Sortierung der Produkte nach errechneten Umsatzwerten. Möglich wird dies durch die Funktion SORTIERENNACH.
Bild 1: Produkteliste wird so sortiert, dass die Umsätze in absteigender Reihenfolge erscheinen
Wie auch »alte« Excel-Funktionen dynamisch werden
Meine Blogbeiträge vom 7.1.2020 und 18.2.2020 zeigen, wie sich mit den neuen Array-Funktionen in Excel 365 Listen erzeugen lassen, die dynamisch gefiltert, sortiert und ohne doppelte Werte sind. Ohne VBA-Programmierung! Das Zauberwort heißt SPILL.
Was es damit auf sich hat, ist schnell erklärt: 35 Jahre lang galt in Excel, dass EINE Formel EINE Zelle mit einem Ergebnis füllt. Mit den neuen Arrayfunktionen ändert sich das gründlich: nun kann eine Formel mehrere Zellen untereinander oder sogar mehrere Spalten nebeneinander mit Ergebnissen befüllen.
Mehr noch: selbst altbekannte Funktionen wie SUMMEWENN(S) oder ZÄHLENWENN(S) können jetzt mit nur einer Formel mehrere Zellen mit Ergebnissen liefern. Auch sie sind SPILL-fähig.
Bild 1: Sogar alte Excel-Funktionen beherrschen jetzt das SPILLING und liefern Ergebnisse für mehr als nur eine Zelle
Corona-Tests in der Schule auswerten: In nur 4 Schritten mit Excel
Vielerorts dürfen Kinder nur am Schulunterricht teilnehmen, wenn sie einen negativen Corona-Test vorweisen können, der nicht älter als 2,5 Tage ist. Doch wie sollen die Schulen die Übersicht behalten, welche Kinder wann getestet wurden und – vor allem – bei welchen Kindern der Test nicht mehr aktuell ist.
Um dem Lehrpersonal an der Schule meiner Kinder diesen Überblick zu erleichtern, welche Schüler wann und wie getestet wurden und bei wem die Tests veraltet sind, habe ich eine unkomplizierte Lösung mit Hilfe von Pivot erstellt.
Vorschau auf die Auswertung
Hier die Anleitung, wie die Lösung in 4 einfachen Schritten entsteht.
Power Query-Abfragen schützen: Geht das?
Power Query ersetzt an vielen Stellen die teils sehr aufwendige VBA-Programmierung, insbesondere wenn es um die Aufbereitung von Daten für die Analyse geht. Das ist eine große Arbeitserleichterung und Zeitersparnis. Aber da kommt auch gleich die Frage auf: VBA-Code lässt sich per Passwort schützen, aber geht das auch bei Power Query-Abfragen?
Ist der Arbeitsmappenschutz aktiv, können die Power Query-Abfragen noch aktualisiert, aber nicht mehr bearbeitet werden
In Excel 365 kann die Bearbeitung und das Löschen der Power Query-Abfragen per Arbeitsmappenschutz verhindert werden.
