Werbung von der Bank: „Legen Sie Ihr Geld in Immobilien an!“ – Prima Idee! Ich kaufe mir gleich am Montag mit meinem Ersparten ein Eigentumszelt.

Ich erstelle ein Add-In für eine Firma. Es soll eine große Liste per Knopfdruck in Einzelteile zerlegen und diese an bestimmten Stellen auf der Festplatte speichern.

Dazu benötige ich eine eindeutige Liste der Kategorien:

Ich überlege: da die Firma Excel 2016 einsetzt, hat sie noch nicht die Funktion EINDEUTIG. Also erzeuge ich per Programmierung eine Pivottabelle und erhalte so eine (sogar sortierte) Liste der einzelnen Kategorien:

Sub MachePivot()

    Dim xlBlattAktiv As Worksheet
    Dim xlBlattHilf As Worksheet
    Dim xlPivotCache As PivotCache
    Dim xlPivotTabelle As PivotTable
    Dim lngZeilen As Long
    Dim lngSpalten As Long
    
    Set xlBlattAktiv = ActiveSheet
    Set xlBlattHilf = ThisWorkbook.Worksheets.Add

    lngZeilen = xlBlattAktiv.Range("A1").CurrentRegion.Rows.Count
    lngSpalten = xlBlattAktiv.Range("A1").CurrentRegion.Columns.Count

    Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C" & lngSpalten, _
        Version:=8)
    Set xlPivotTabelle = xlPivotCache.CreatePivotTable( _
        TableDestination:=xlBlattHilf.Range("A1"), _
        TableName:="RenesPivot2", _
        DefaultVersion:=8)
        
    xlPivotTabelle.ColumnGrand = False
    xlPivotTabelle.RowGrand = False
    
    With xlPivotTabelle.PivotFields("Kategorie")
        .Orientation = xlRowField
        .Position = 1
    End With

End Sub

Ich teste – und: padautz: Fehler!

Ich brauche eine Weile, bis ich die Ursache finde. In der Liste gab es zwei Mal eine Spalte mit der Bezeichnung „Kommentar“. Excel 2016 schafft es nicht, die Spalten für die Pivottabelle umzubenennen (wie neuere Excelversionen):

Also: dann grenzen wir den Bereich doch ein!

    Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C4" , _
        Version:=8)

Geht doch:

Hab gerade zwei Männer im Partnerlook gesehen und sie höflich gefragt, ob sie schwul sind. Mich dafür verhaften, finde ich schon krass …

Irgendwie doof!

In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:

Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:

Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:

Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:

Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?

Ich weiß es nicht?

Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?

Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?

Irgendwie doof!

Werdet ihr beim Saubermachen auch immer so abgelenkt von Dingen, die ihr findet?

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Beim Importieren von Daten „erkennt“ PoweryQuery den Datentyp der Spalten. Das kann nervig oder lästig sein oder auch zu Fehlern führen:

Diese Option kann man deaktivieren:

„Spaltentypen und -überschriften für unstrukturierte Tabellen niemals erkennen.“

Danke, Martin, für diesen wertvollen Tipp.

Vom Winterschlaf direkt in die Frühjahrsmüdigkeit. Ich führe ein Leben im Einklang mit der Natur.

Ich will in der Excelschulung demonstrieren, dass Listen eine Überschriftszeile haben sollten; ja – dass Pivottabellen auf Listen aufsetzen, bei denen jede Spalte eine eindeutige Überschrift besitzt.

Ich lösche eine Überschrift heraus:

erstelle eine Pivottabelle, vergesse aber, den Haken beim Datenmodell zu entfernen:

Da die Daten ins Datenmodell geladen werden, muss jede Spalte eine Überschrift haben. Da dies nicht gegeben ist, füllt Excel nicht die leere Überschrift auf, sondern beginnt ab der zweiten Zeile, die als Überschrift verwendet wird:

Eigentlich wollte ich die Fehlermeldung

Der PivotTable-Feldname ist ungültig. Um einen PivotTable-Bericht zu erstellen, müssen Sie Daten verwenden, die einer Liste mit Spaltenüberschriften organisiert sind. Wenn Sie den Namen eines PivotTable-Berichtsfeldes ändern, müssen Sie einen neuen Namen für das Feld eingeben.

zeigen:

Gruseliges Excel!

Ich habe einen Kollegen. Man könnte ihn liebevoll Quatschkopf oder Frechdachs nennen oder sich über seine Späße freuen. Mann kann es aber auch anders sehen – das möchte ich hier nun doch nicht schreiben.

Auf alle Fälle: Vor Kurzem war er an meinem Rechner und hat etwas verstellt. Immer wenn ich Excel öffne, dann sehe ich folgendes Excel. Natürlich kann ich alte Dateien öffnen und damit arbeiten – aber für neue Dateien – das geht gar nicht! Was hat er denn gemacht? Und: wie bekomme ich es wieder weg?

Spalten- und Zeileköpfe - mal anders ...

Spalten- und Zeileköpfe – mal anders …

Die Antwort: Wenn Sie genau hinschauen, stellen Sie fest, dass eine sehr merkwürdige Schrift eingestellt ist. Die Schrift wirkt sich nicht nur auf die Zellen aus, sondern auch auf die Zeilen- und Spaltenköpfe. Netterweise nicht auf das Menüband.

Wechseln Sie in die Optionen und legen Sie dort „diese Schriftart fest, die Sie als Standardschriftart verwenden“ möchten.

Man kann "lustige" Schriftarten für die Zeilen- und Spaltenköpfe  verwenden.

Man kann „lustige“ Schriftarten für die Zeilen- und Spaltenköpfe verwenden.

Diagramme – leerer Eintrag in der Achse

Warum übernimmt Excel nicht die Überschrift der Tabelle als Überschrift für Diagramme? Warum wird sie als leere Kategorie angezeigt?

Leerer Kategorienpunkt im Diagramm

Leerer Kategorienpunkt im Diagramm

Die Antwort: Schreiben Sie die Überschrift nicht über die Spalte mit den Kategorien – sonst „geht Excel davon aus“, dass es sich hierbei um eine Kategorie ohne Wert handelt. Schreiben Sie die Überschrift über die Wertespalte, also über die zweite Spalte.

Die Überschrift wird zur Überschrift.

Die Überschrift wird zur Überschrift.

Pivottabelle funktioniert nicht

Dieser Befehl setzt mindestens zwei Zeilen voraus, die Quelldaten enthalten. Der Befehl kann nicht auf die Auswahl von nur einer Zelle angewendet werden.

… behauptet Excel. Er lügt doch – ich habe eine Liste auf der ich eine Pivottabelle aufsetzen möchte. Warum klappt das nicht?

Pivot geht nicht.

Pivot geht nicht.

Den Grund kann man HIER sehr schnell sehen – in der die Spalte D hat keine Überschrift. Pivottabellen verlangen unbedingt, dass jede Spalte immer eine Überschrift haben müssen. Hier sieht man es – schwieriger wird es, wenn die Tabelle sehr groß ist, das heißt, wenn die Tabelle sehr viele Spalten besitzt. Sie können mit der Tastenkombination [Strg]+[→], beziehungsweise [Strg]+[←] überprüfen, ob jede Spalte eine Überschrift besitzt.

Komische Überschriften

Ups – plötzlich steht in der ersten Zeile statt A, b, C, … die Zahl 1, 2, 3. Was ist passiert?

Man kann diese Einstellung in den Optionen / Formeln wieder ausschalten. Sie heißt Z1S1-Bezugsart.

Übrigens würde Excel nun statt =C3 schreiben: =ZS(-9). Und statt =SUMME(G2:G30) steht nun in der Zelle: =SUMME(Z(-9)S(-3):Z(19)S(-3))

Z1S1 - US-amerikanische Schreibweise

Z1S1 – US-amerikanische Schreibweise

Der „Kopf“ ist weg

… Und plötzlich komme ich nicht mehr in die ersten Zeilen …

Eine perfide Sache: Scrollen Sie in Excel etwas nach unten, so dass die ersten Zeilen oberhalb des sichtbaren Bildschirm stehen. Setzen Sie nun den Cursor in die oberste sichtbare Zelle. Wenn Sie nun alles fixieren (oder einfrieren), was darüber steht, gelangen Sie nicht mehr in die Zeilen darüber.

Die Lösung ist klar: Die Fixierung wieder aufheben!

Mit geschicktem Fixieren kann man Bereiche "verschwinden" lassen.

Mit geschicktem Fixieren kann man Bereiche „verschwinden“ lassen.

Die Überschrift ist weg

Ich wollte nur schnell die Überschrift formatieren – genauer – über die Auswahl zentrieren – schwupps – ist sie weg.

Die Antwort: Markieren Sie nicht die ganze Zeile, sondern nur die Zellen, über die die Überschrift zentriert werden soll. Wenn Sie alle Zeilen markieren, wird der Inhalt von A1 in Mitte der 16.384 Spalten gestellt – also ziemlich weit rechts.

Übrigens kann man das wieder auflösen mit einem erneuten Klick auf das Symbol „Verbinden und zentrieren“.

Hinweis: Sie können auch alle Zellen markieren und alle Verbindungen aufheben, indem Sie auf dieses Symbol klicken.

Die Überschrift verschwindet ...

Die Überschrift verschwindet …

Wie geht denn das? Namen statt Spaltenköpfe?

Eine Kollegin hat in einer Exceltabelle stallt den Spaltennamen A | B | C … die sprechenden Überschriften der Tabelle: Kundennummer | Vorname | Nachname. Wie geht denn das?

Die Antwort: Wenn man eine Liste als Tabelle formatiert (Einfügen / Tabelle), und nun in der Registerkarte „Entwurf“ die Formatvorlage löscht, ebenso wie den Autofilter (Register „Daten“), dann wird beim Scrollen nach unten die Überschrift anstelle der Spalteköpfe angezeigt.

Sprechende Namen statt Spaltenköpfe

Sprechende Namen statt Spaltenköpfe