Sie suchen Anleitungen und Tipps zum produktiven Umgang mit den Microsoft-Tools? Lesen Sie hier, wie Sie Aufgaben effizienter erledigen, typische Probleme meistern und welche Neuerungen es gibt.
Nutzen Sie rechts die Schnellnavigation und das Suchfeld.
TIPP: Versäumen Sie nicht, den Blog zu abonnieren. Klicken Sie dazu rechts auf das RSS-Symbol.
Daten an separater Stelle flexibel filtern: 6 Szenarien
Geht es um das Filtern von Daten, nutzen viele noch den Befehl AutoFilter. Er filtert die Originaltabelle nach den gesuchten Daten und blendet nicht passende Zeilen aus. Der Nachteil: Werden neue Filterkriterien gesetzt, geht das vorherige Filterergebnis verloren.
Hier schafft die Funktion FILTER Abhilfe. Sie extrahiert die gesuchten Daten aus der Originaltabelle und gibt sie an anderer Stelle im Arbeitsblatt oder in der Arbeitsmappe aus. Somit lassen sich die Ergebnisse mehrerer Filtervorgänge auf einem Blatt anordnen.
Die FILTER-Funktion gehört zu den dynamischen Arrayfunktionen und ist seit mehreren Jahren in den aktuellen Excel-Versionen verfügbar. In diesem Beitrag zeige ich anhand von sechs Szenarien, wie sie aufgebaut wird und was sie leistet.
Excel-Formeln dynamisch machen mit Magie des #-Zeichens
Wer mit dynamischen Arrayfunktionen arbeitet, wird früher oder später die Magie des #-Operators entdecken. Dieser ermöglicht es, mit nur einem Zeichen den kompletten Zellbereich zu adressieren, der mit einer dynamischen Arrayfunktion erzeugt wurde.
- Selbst alte Funktionen wie SUMMEWENN, ZÄHLENWENN, ANZAHL oder SUMME werden dynamisch, wenn der Überlauf-Operator # genutzt wird.
- Auch die Auswahl für Dropdowns kann mit Hilfe des #-Operators dynamisch gemacht werden.
Die folgenden drei Beispiele zeigen, WIE es funktioniert.
Excel: Daten ohne Duplikate (dynamisch) auflisten mit EINDEUTIG
Aus einer Liste mit Bestellungen soll herausgefunden werden: Welche Kunden haben bestellt, welche davon nur einmal?
Wer solche und ähnliche Fragen beantworten muss, braucht eine Liste ohne Duplikate, also eine Liste, in der alle Kunden nur einmal aufgeführt werden.
Die einfachste und schnellste Lösung bietet eine Funktion, die es schon länger in Excel gibt, die aber kaum bekannt ist: EINDEUTIG.
- Eine kurze Formel genügt, um alle Einträge einer Spalte genau einmal aufzulisten.
- Mehr noch: EINDEUTIG zeigt auch, welche Einträge in einer Liste nur einmal vorkommen.
Wie es funktioniert und welche Einsatzgebiete es für EINDEUTIG gibt, erklärt dieser Beitrag.
Vorschau darauf, was die Funktion EINDEUTIG leistet
PowerPoint: Schneller und leichter zum gewünschten Bild
Wer Folienlayouts mit Inhalts- und Bildplatzhaltern nutzt, kann sich auf eine neue Optik und erleichterte Bedienung freuen.
In der Beta von PowerPoint 365 sind die Änderungen bereits angekommen.
- Die Inhaltsplatzhalter bieten künftig nicht mehr acht, sondern nur noch sechs Symbole.
- Die Optik der Symbole wird modernisiert und wirkt dank Umrahmung übersichtlicher.
- Der Zugriff auf mögliche Bildquellen wird einfacher dank eines neuen Auswahlkatalogs.
Der neue Inhaltsplatzhalter bietet zwar weniger Symbole, trotzdem wird der Bedienkomfort verbessert
Dynamisches Sortieren in Excel – Daten smart ordnen ohne Änderung der Originaldaten
Beim manuellen Sortieren wird der ursprünglichen Datenbestand geändert. Wer das verhindern will, erledigt das mit Formeln. Excel bietet dazu die Funktionen SORTIEREN und SORTIERENNACH. Sie ermöglichen es, die Daten in einem separaten Bereich in beliebigen Abfolgen anzuordnen.
Die per Formel umsortierten Daten bleiben dynamisch mit den Originaldaten verknüpft. Ändern sich die Originaldaten, wird die umsortierte Ansicht automatisch aktualisiert. Wer solche flexiblen Ansichten seiner Originaldaten braucht, erfährt hier, WIE es geht.
Mit diesen drei Formelvarianten werden die Originaldaten umsortiert
Power Query: Merkmale über mehrere Spalten hinweg filtern
Wer hat schon mal versucht, eine Tabelle in Excel oder Power BI nach Merkmalen zu filtern, die über mehrere Spalten verteilt sind? Standardfilter stoßen hier an ihre Grenzen, denn sie funktionieren nur für je eine Spalte. Mit umständlichen Workarounds und Wenn-Hilfsspalten ließe sich das Problem lösen. Oder aber in Power Query mit schlauen List-Funktionen.
Im heutigen Rezept zeige ich, wie Filter dynamisch gestaltet werden, um Daten über mehrere Spalten nach bestimmten Kriterien zu ermitteln oder auszuschließen.
Die folgende Produktliste illustriert das Problem: Die Merkmale für Materialen wurden über mehrere Spalten erfasst. Gesucht werden die Produkte, in denen bestimmte Materialien auf jeden Fall vorkommen – hier Holz und Metall – und andere auf keinen Fall – hier Kunststoff.
Merkmale in verschiedenen Spalten machen das Filtern zum Aus- oder Abwählen von bestimmten Merkmalen kompliziert
Power Query: E-Mail-Adressen aus Zeichenketten auslesen
Daten, die in Freitextfelder eingegeben wurden, enthalten oft Details, die gebraucht werden. Beispielsweise E-Mail-Adressen wie in der folgenden Abbildung. Doch wie lassen sich gezielt die E-Mail-Adressen aus diesen Daten auslesen? Wie können solche wertvollen Informationen für Marketingzwecke, Supportanfragen oder Analysen nutzbar gemacht werden?
In meinem Rezept #25 zeige ich, wie in Power Query E-Mail-Adressen aus Bemerkungsfeldern extrahiert werden, indem ich alle Texte mit einem @-Zeichen verarbeite.
Alle Texte mit einem @ Zeichen extrahieren und in einer neue Spalte auflisten, ggf. getrennt mit |
Sollten Excel-Einsteiger »Power Query« lernen?
Bisher hätte ich diese Frage eher mit Nein beantwortet. Doch dann kam letzte Woche dieser Excel-Einstiegskurs: Dort berichteten 4 von 7 Teilnehmern, dass sie nicht mit Excel arbeiten und es auch nicht nutzen würden, wenn da nicht diese CSV wäre.
Bei weiterer Nachfrage kam heraus: wöchentlich muss eine CSV-Datei bearbeitet werden:
– zuerst die Angaben zum Datum in Ordnung bringen,
– dann die Daten nach einem Kriterium filtern,
– die verbleibenden Daten nach Datum sortieren,
– eine Multiplikation durchführen und
– abschließend eine überflüssige Spalte löschen.
Diese stupide Arbeit steht jede Woche an und es darf nichts vergessen werden.
Meine Reaktion? Ich zeigte, wie diese fünf Aufgaben von Power Query erledigt werden. Und, dass diese Aufbereitungsschritte beim nächsten Mal in kürzester Zeit durchgeführt werden, indem nur der Befehl Aktualisieren aufgerufen wird. Das Erstaunen bei den Teilnehmern war sichtbar, und sie wollten Power Query lernen.
In diesem Power Query Rezept #24 beschreibe ich, WIE es geht.
Die Daten der CSV-Datei filtern, sortieren, berechnen und das Ergebnis in einer Excel-Tabelle anzeigen
Power Query: Böse Überraschungen beim Runden vermeiden
Was ergibt sich, wenn die Zahl 2,5 gerundet wird? Excel liefert mit der Funktion RUNDEN das Ergebnis 3, Power Query hingegen 2.
Der Grund dafür: In Power Query wird bei Werten genau in der Mitte zwischen zwei ganzen Zahlen standardmäßig auf die nächste gerade Zahl auf- oder abgerundet. Aus 2,5 wird somit 2, aus 1,5 wird ebenfalls 2.
Wie Power Query beim Runden tickt und wie es auf kaufmännisches Runden umgestellt werden kann, beschreibe ich in diesem Rezept.
Zum Teil unterschiedliche Ergebnisse beim Runden in Excel und Power Query
Excel 365: Automatische Warnung vor veralteten Werten in Mappen mit manueller Berechnung
Wer die Berechnung seiner Arbeitsmappen auf Manuell einstellt, macht dies meist, um Tempoverluste beim Bearbeiten umfangreicher Mappen mit vielen Formeln zu vermeiden. Excel rechnet bei dieser Einstellung nur dann, wenn es explizit angewiesen wird, z. B. mit F9.
Diesem Vorteil steht allerdings ein gewichtiger Nachteil gegenüber: Wer nicht weiß oder vergessen hat, dass in einer Mappe die Berechnungsoption Manuell eingestellt ist, verlässt sich darauf, dass sich die Formelergebnisse beim Ändern der Daten automatisch anpassen. Ein folgenschwerer Irrtum!
Um dies zu vermeiden, bietet Excel 365 (im Betakanal) eine nützliche Neuerung:
- »Veraltete« Werte werden optisch hervorgehoben.
- Und zwar werden Zellwerte, die nicht mehr korrekt sind und neu berechnet werden müssen, automatisch durchgestrichen.
Hinweis auf eine weitere Neuerung in Excel 365
