Eine Liebeserklärung an MS Excel
Ein bisschen nervig ist es schon: Jedes Mal, wenn ich eine Pivottabelle erstelle, muss ich das Berichtslayout im Tabellenformat anzeigen lassen, anschließend alle Elementnamen wiederholen lassen, die Gesamtergebnisse und Teilergebnisse deaktivieren und die Schaltfläche ausschalten. Nur so können die Daten weiter gegeben werden. Kann man leider nicht abspeichern.
und immer wieder muss ich einschalten …
Kann man die Standardeinstellungen der Pivottabellen ändern?
Ich habe eine Liste, in der sich Leerzellen befinden. Auf diese Liste setze ich eine Pivottabelle auf. Leider verwendet Excel nun die Funktion Anzahl statt Summe. Muss ich jede Spalte einzeln ändern?
Liste und Ergebnis
Die Antwort: Leider ja!
Sehr geehrter Herr Martin,
Außerdem habe ich noch eine Frage bezüglich Excel 2013. Ich möchte einen Text mit „Text in Spalten“ trennen. Unter Zielbereich würde ich gerne ein neues Tabellenblatt angeben. Dies ist aber leider nicht möglich. Ich bekomme immer die Meldung „Zielbezug ist ungültig“.
Wie kann ich die zu trennenden Daten in ein neues Tabellenblatt schreiben lassen.
Über eine Antwort würde ich mich sehr freuen.
Muss ich auf dem gleichen Blatt bleiben?
Hallo Frau N.,
Sie haben recht: der Assistent Daten / Text in Spalten verlangt, dass die Daten auf dem gleichen Blatt stehen müssen. Ich habe mal mit dem Makrorekorder diesen Assistenten aufgezeichnet. Und dann den Befehl Range(„U1“) (gemeint ist die Zelle U1 auf dem Blatt, auf dem ich mich befinde)
geändert in:
Selection.TextToColumns Destination:=Worksheets(„Tabelle2“).Range(„U1“), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Was passiert? Excel übergeht einfach „Tabelle2“ und schreibt die Daten auf das Blatt, auf dem sich der Cursor befindet.
Heißt für Sie: sorry: erst in einen leeren Bereich einfügen; dann ausschneiden und auf ein neues Blatt einfügen.
schöne Grüße
Rene Martin
Ich verstehe es nicht. Ich habe eine kleine Liste erstellt und ein Diagramm aufgesetzt. Damit das Diagramm sich die aktuellen Zahlen holt, habe ich den Datenbereich über Einfügen / Tabelle in eine intelligente Tabelle verwandelt. Normalerweise – wenn ich in der letzten Zelle rechts unten den Cursor platziere und dort die Tabulatortaste drücke wird eine neue Zeile angefügt und die Daten werden in das Diagramm übernommen. Bei mir jedoch nicht – nach dem dritten Athleten ist Schluss. Er hüpft wieder zurück in Zelle A97. Warum?
Keine neuen Daten möglich!
Die Antwort könnten Sie auch alleine finden. Unter Ihrer Liste ist Zeile 101 ausgeblendet. Da Excel vorhandene Zellen zur Liste (Tabelle) hinzufügt, kann er das in Ihrem Fall nicht, weil die Daten sichtbar sein müssen. Deshalb erfolgt der Sprung „zurück zum Anfang“ auf A97.
Ich verstehe es nicht. Ich wollte in einer Liste eine Überschrift ändern und habe nun dort den Text „Datum“ eingetragen. Tippen darf ich ja, aber sobald ich die Zelle verlasse steht plötzlich „Datum2“ drin. Ich habe es nicht reingeschrieben. Ich schwör’s!
Ich tippe – Excel ändert!
Sie müssen genau hinschauen. Ihre Liste ist als Tabelle formatiert. Das erkenne ich an der Registerkarte Tabellentools / Entwurf. Und ich sehe, dass es bereits eine Spalte Datum gibt (Spalte A). Tabellen (oder intelligente Tabellen) erlauben jeweils nur eindeutige Feldnamen – das heißt: Sie dürfen nicht zwei Spalten mit der Überschrift „Datum“ versehen. Sonst greift Excel ein!
Daran habe ich es erkannt.
Ich weiß nicht warum, aber wenn ich eine Pivottabelle erstelle sieht sie anders aus als bei meiner Kollegin. Und: Ich hätte auch gerne die Informationen (Jan, Feb, Mrz, …) neben den Ortsnamen und nicht darunter.
Pivot bei mir
Pivot bei ihr
Die Antwort: In den einzelnen Excel-versionen, werden verschiedene Layouts zugrunde gelegt. Sie können diese aber umschalten über die Pivottable-Tools / Entwurf / Layout / Berichtslayout. Wählen Sie dort die Option „Tabellenformat“. Übrigens: Ist Ihnen aufgefallen, dass Excel in der Version 2016 Datumsangaben gleich gruppiert?
Die Einstellung kann geändert werden.
Hallo Herr Martin,
bin ich doof, oder was? Ich habe ein kleines Projekt in Excel angelegt und dort einige Zeilen farblich unterlegt. Wenn ich nun nach der Farbe sortiere, sind alle orangenen Zeilen oben. So weit so gut:
Nach oben sortieren
Wenn ich diese Sortierung nun wieder ausschalten möchte und auf die Option „weiß“ klicke, dann funktioniert es nicht. Ich hätte gerne die ursprüngliche Reihenfolge der Daten wiederhergestellt. Muss ich wirklich die Liste schließen und wieder aufmachen?
So leider nicht!
Die Antwort: Weiß bedeutet hier nicht: „ursprüngliche Reihenfolge“ oder „Original wiederherstellen“, sondern: sortiere die Liste so, dass die weißen Zellen oben stehen. Um die ursprüngliche Reihenfolge zu erhalten, ist es am besten vor dem Sortiervorgang eine laufende Nummer einzufügen. Sie halten die ursprüngliche Reihenfolge wieder, wenn Sie die Liste nun nach dieser Nummer sortieren. Denn „sortieren“ bedeutet in Excel: ändere die physikalische Reihenfolge der Daten.
Nun stehen die weißen Zeilen oben.
Was ist denn das für eine Fehlermeldung?
Verunglückte Pivottabelle
Die Antwort: Setzen Sie den Cursor in die Tabelle oder markieren Sie nur die Spalten der Tabelle. Auf keinen Fall die leeren Spalten. Diese haben keine Überschriften (klar) und können deshalb nicht in der Pivottabelle verarbeitet werden (eigentlich auch klar).
Eigentlich wollte ich nur die Teilergebnisse anzeigen lassen. Aber Excel zeigt leider nur eine Option an. Ein bisschen wenig, nicht wahr?
Teilergebnis
Klar – Sie haben auch nur eine Spalte markiert. Entweder Sie markieren die gesamte Liste oder setzen den Cursor in die Liste ohne etwas zu markieren. Dann wird Excel alle Spaltenüberschriften als Gruppierungsoptionen bei den Teilergebnissen vorschlagen.
Was läuft hier schief? Obwohl ich die Pivottabelle aktualisiert habe, wird sie nicht aktualisiert. Oder nimmt einige Daten nicht auf.
Auch nach der Aktualisierung: dieser Datensatz fehlt!
Die Antwort: Schauen Sie mal in Pivottable-Tools / Analysieren / Daten / Datenquelle ändern nach, auf welchen Bereich, bzw. auf welches Tabellenblatt sich Excel bezieht. Vielleicht haben sie den Bereich zu klein gewählt.
Der Bereich ist nicht korrekt!
Hallo Herr Martin,
was mache ich falsch`Ich bekomme aus unseren System jede Woche eine Excel-Datei, die ich nach der Spalte C (Last Audit Date) sortieren muss. Ich klicke hierzu auf die Zelle C1, dann auf das Symbol sortieren – aber manchmal sortiert er die erste Zeile mit in die Tabelle ein, manchmal bleibt sie oben stehen. Warum? Kann man das abschalten? Die Zeile soll oben stehen bleiben. Ist doch nicht so schwierig, oder?
Die Liste wird sortiert.
Die Überschriftszeile „verschwindet“ – verzieht sich nach unten.
Die Antwort: Damit die erste Zeile oben stehen bleibt, muss jede Zelle der ersten Zeile einen Text aufweisen. Das heißt: die Zelle E1 ist leer. Deshalb „vermutet“ Excel, dass es sich bei der ersten Zeile nicht um eine Überschriftszeile handelt, sondern um eine Inhaltszeile, die nach unten mit einsortiert werden soll. Schreiben Sie also etwas rein! Dann klappt es auch beim nächsten Download und beim nächsten Sortieren.
Sie sollten etwas reinschreiben.
Warum zeigt die Feldliste nur eine Spalte zur Auswahl an und nicht – wie sonst auch – alle Spalten der Tabelle?
Heute: Pivot light?!
Die Antwort: Sicherlich haben Sie in der Tabelle einige Zellen markiert. Sie können es mit Pivottable-Tools / Analysieren / Daten / Datenquelle ändern herausfinden: Dort zeigt Excel an, auf welchen Bereich die Pivottabelle zugreift:
In der Liste wurden zwei Zellen markiert.
Wahrscheinlich haben Sie beim Erstellen der Pivottabelle einige Zellen markiert – das hätte Ihnen auffallen können. Also: Entweder NICHTS markieren oder die gesamte Liste!
Hier hätte man es merken können.
Hallo Herr Martin,
Sie haben mir schon einmal bei meiner Excel-Tabelle geholfen, die ich immer noch nutze und sehr gut funktioniert.
Jetzt habe ich eine kleine Frage.
Ich benutze eine Tabelle, die im Datum aufsteigend ist.
In dem Feld B2 ist das Datum, in Feld A2 das Datum von B2 als „TTT“ formatiert.
Jetzt habe ich heute versucht, die Spalte A1 in einen Filter einzubauen.
Ich möchte dadurch zum Beispiel mir alle Montage oder Dienstage anzeigen lassen.
Als ich den Filter gesetzt habe, hat er mir nur die einzelnen Datums angezeigt.
Die waren dann unter 2017 …. dann nach Montaten …. und dann nach dem Tagen im Monat aufgelistet.
Wie kann ich dies Umstellen, das er mir nur die Tage Montag bis Sonntag liefert?
Es ist für die Kursplanung gedacht, die mir dann viel Scrollarbeit abnimmt, wenn ich nur Mittwochskurse habe.
Anbei schicke ich Ihnen die Datei mit, vielleicht ist es dann einfacher zu verstehen.
Filter – so nicht!
Hallo Herr S.,
das würde ich auch nicht, wenn ich Excel wäre …
Formatieren heißt doch nur „gestalten“, das bedeutet: nicht den Wert, sondern nur das Aussehen der Zelle zu ändern. Also: wenn Sie eine Zelle, in der eine 7 steht fett machen oder mit einer großen Schrift oder rot formatieren, dann befindet sich immer noch der Wert 7 darin.
Das bedeutet: Sie dürfen das Datum nicht formatieren, sondern müssen es in einen Text umwandeln – beispielsweise mit der Funktion TEXT. Dann können Sie die Liste sortieren oder Filtern (siehe Anlage).
schöne Grüße
Rene Martin
Die Funktion TEXT liefert die Lösung
Hallo! Ich habe eine kleine Liste. Ich habe gelernt, dass man an dem Symbol „löschen“ erkennen kann, dass ein Filter gesetzt wurde. Ebenso sind die Zeilennummern blau und in der Statuszeile steht, dass Zeilen gefiltert wurden. Aber wo?
Wo ist denn nur der Filter?
Die Antwort: Sie müssen genau hinschauen – eine Spalte ist ausgeblendet. Und richtig: DORT wurde der Filter eingeschaltet!
In der ausgeblendeten Spalte befindet sich der Filter
Warum darf ich keine Pivottabelle erstellen? Irgend etwas klappt hier nicht!
Keine Pivottabelle
Die Antwort: Richtig: Sie müssen in der Feldliste über das Symbol „Extras“ von der Option „Nur Abschnitte für Bereiche“ wechseln zur Einstellung „Abschnitte für Felder und Abschnitt für Bereiche“:
In der falschen Einstellung …
Manchmal schlägt die Pivottabelle bei der Zusammenfassung von Zahlen die Funktion ANZAHL statt der Funktion SUMME vor. Natürlich kann man die Funktion über die Wertfeldeinstellungen umschalten – dennoch erstaunt das Verhalten. Wahrscheinlich liegt es daran, dass in den Daten leere Zellen gefunden werden und diese als Text interpretiert werden.
Anzahl statt Summe
Sind die leeren Zellen die Ursache?
Excel ist nicht konsequent, oder? Ich habe eine Liste mit Postleitzahlen, die als Zahlen in einer Spalte stehen. Wenn ich alle Postleitzahlen filtere, die mit 8 beginnen (also Raum 8) – wenn ich es benutzerdefiniert mit 8* oder beginnt mit 8 versucht, erhalte ich keinen Kunden.
8* findet keinen
Im benutzerdefinierten Filter muss ich mit >=80000 und <90000 filtern.
Wenn ich dagegen in das Textfeld, das Excel seit der Version 2010 zur Verfügung stellt 8* eintragen, so klappt es.
Hier funktioniert 8*
Die Antwort: Das ist richtig. Hier ist Excel nicht konsequent. Im „benutzerdefinierten Filter“ wird streng zwischen Zahl und Text unterschieden – beim Textfeld erstaunlicherweise nicht.
Wie hat mein Kollege denn das gemacht? Er hat eine Pivottabelle erstellt (klar – kann ich auch). In einer Spalte summiert er die Umsatzzahlen (auch klar) und daneben lässt er sich den Rang anzeigen. Das geht aber nicht bei mir! Warum?
Wo ist der Rang?
Die Antwort: Ihr Kollege hat die Pivottabelle mit Excel 2013 erstellt. Dort gibt es im Kombinationsfeld die Einstellungsmöglichkeit „Rang“. In Excel 2007 war sie leider noch nicht vorhanden.
Lösung: Sie könnten die Daten sortieren – dann sehen Sie auch die größte Zahl oben, die kleinste unten oder umgekehrt.
Rang in Pivot in Excel 2013
Ist Ihnen folgendes Phänomen schon aufgefallen? In einer Tabelle, die aus einem anderen System kommt, stehen in einer Spalte Adressinformationen – dummerweise Vorname und Nachname durch ein Leerzeichen getrennt, ebenso Postleitzahl und Ort. Noch schlimmer – die drei Adresszeilen wurden durch eine Zeilenschaltung ([Alt] + [Enter]) getrennt. Wenn Sie nun den Assistenten „Text in Spalten“ (Registerkarte „Daten“ verwenden, dort das Leerzeichen als Trennzeichen angeben:
Daten trennen
dann verschwinden die Daten, die hinter der Zeilenschaltung standen:
Daten verschwinden
Die Lösung: Man müsste die Zeilenschaltungen durch ein anderes Zeichen ersetzen, beispielsweise so:
=WECHSELN(A2;ZEICHEN(10);“/“)
Anschließend die Formel in Werte umwandeln (Inhalte einfügen) und dann erst kann man die Daten vernünftig trennen. Schon merkwürdig, dass einfach Daten verschwinden …
Mit WECHSELN kann man ersetzen.
Ich sortiere häufig Listen. Meistens funktioniert es auch. Aber leider – manchmal zeigt er einfach nicht alle Überschriften an. Beim zweiten oder dritten Anlauf klappt es dann. Woran kann das liegen?
Sortieren – leider nicht überall.
Die Antwort: Wenn Sie genau hinschauen, sehen Sie, dass Sie die Spalte „Ort“ (Spalte E) markiert haben. Deshalb schlägt Excel nur die Spalten zum Sortieren vor.
Das heißt: Entweder Sie markieren nichts oder die gesamte Tabelle. Ich empfehle nichts zu markieren. Wenn Sie unsicher sind, ob Excel wirklich den gesamten Bereich sortiert, drücken Sie die Tastenkombination [Strg] + [*]. Dann markiert Excel den zusammenhängenden Bereich, den er ohne Selektion sortieren würde.
Eigentlich wollte ich nur die Liste nach den Gerätenamen sortieren.
Eine „normale“ Sortierung?
Und dann diese Meldung:
Meldung
Der folgende Sortierschlüssel enthält Zahlen, die als Text formatiert wurden und kann daher eine unerwartete Sortierreihenfolge ergeben.
Die Antwort: Sie haben die Spalte, in der die Gerätebezeichnungen stehen, als Text formatiert, was hinsichtlich der Artikelbezeichnungen vernünftig ist. Nun „weiß“ Excel nicht, ob die Zahlen der Größe nach (also nach dem Wert) sortiert werden sollen oder als Texte, das heißt alphabetisch von links nach rechts gelesen.
Übrigens: Interessant: Wenn Sie auf eine Zelle innerhalb der Spalte klicken und das Symbol „Nach Größe sortieren“ verwenden (A↓Z oder Z↓A), erhalten Sie diese Frage nicht.
Ich habe genau aufgepasst, was meine Kollegin macht. Wenn wir einen Download von unserem System erhalten, muss eine Spalte in zwei Teile zergliedert werden. Dazu hilft der Assistent „Text in Spalten“. Den finde ich im Register „Daten“.
Aber: Als ich es das erste Mal selbst versuchen wollte, bin ich ins Grübeln gekommen. Der Assistent wollte von mir wissen, ob die Daten eine feste Breite haben (sicherlich nicht!) oder ob sie getrennt sind. Natürlich sind sie noch nicht getrennt – sonst würde ich sie doch nicht trennen wollen. Ich habe mal abgebrochen – vielleicht können Sie mir ja helfen, was ich tun soll.
Getrennte Daten
Die Antwort: Die Option „getrennt“ war schon richtig. Auch hier wurde etwas verwirrend übersetzt, beziehungsweise beschriftet. Ich hätte es wahrscheinlich beschriftet mit: „gibt es ein Trennzeichen anhand dem Excel erkennen kann, wo die Daten getrennt werden sollen“ (oder ähnliches). Im englischen Original ist es auch nicht so dolle beschriftet – auch hier könnte die Sprachabteilung nochmal drüber:
Delimited
„Choose the file type that best describes your data: Delimited – Characters such as commas or tabs separate each field.“
Wenn ich den Cursor unterhalb einer Liste positioniere und [Alt]+[↓] drücke, erscheint in der Auswahlliste nicht die komplette Liste der darüber stehenden Begriffe. Warum?
Auswahlliste
Das kann mehrere Gründe haben. Zum einen – im oberen Beispiel – befinden sich zwischen den einzelnen Gruppen Leerzeilen. Excel listet nur die Daten auf, die sich direkt darüber befinden.
Es kann aber auch sein, dass sich zu viele darüber befinden. Ich habe in eine Liste mehr als 48.500 verschiedene Namen eingefügt. Wird nun [Alt]+[↓] gedrückt, wird bis zur Zeile 36.184 aufgelistet – also nicht alle. Aber ziemlich viele!
Viele, aber nicht alle!
Das gleiche Phänomen (ein interner Cache) begegnet Ihnen auch beim Autofilter. Zwar ist er gegenüber Excel 2003 besser geworden, aber er ist dennoch begrenzt. Dort wird angezeigt, dass nicht alle Elemente aufgelistet werden. Bei mir werden mehr als 11.000 Elemente aufgelistet.
Auch der Autofilter listet nicht unendlich viele Elemente auf.
Hallo. Ich habe schon eine ganze Weile gesucht, bis ich herausgefunden habe, warum er die gallischen Tiere falsch zählt. Eigentlich müsste die Formel ANZAHL2 die Zahl 2 ergeben und nicht 4:
ANZAHL2 zählt falsch
Die Antwort habe ich nach langem Suchen gefunden: In einigen Zellen habe ich aus Versehen ein Leerzeichen eingegeben. Klar – das sehe ich nicht; das wird als Text mitgezählt.
Die Wurzel des Übels
Aber nun meine Frage: Wenn ich die Liste filtere – warum zeigt der Autofilter nicht an, dass einige Zellen Leerzeichen enthalten?
Die Antwort: Ja – Sie haben recht – der Autofilter übergeht zum Glück (oder leider?) die Leerzeichen. Der Vorteil: „Asterix“ und „Asterix “ (mit einem Leerzeichen am Ende) werden vom Filter als gleicher Text behandelt. Der Nachteil: Der Filter hilft nicht diese Leerzeichen, die an anderen Stellen Probleme verursachen, aufzufinden.
Der Autofilter übergeht Leerzeichen am Ende des Textes.
Man kann die Texte mit Suchen ([Strg]+[F]) auffinden. Oder mit Funktionen:
=LÄNGE(C2)
=WENN(LINKS(C2;1)=“ „;“x“;““)
Oder mit [Strg]+[↓] können Sie den Cursor nach unten versetzen; er springt nun zur ersten Zelle, in der etwas steht; stoppt also auch bei den Zellen, die mit einem Leerzeichen gefüllt sind.
Lieber Herr Martin,
eine allerletzte Frage (bestimmt die allerletzte!). Wir haben bei Ihnen im Kurs gelernt, dass man mit dem Assistenten „Daten trennen“ Daten auseinander pflücken kann. Aber manchmal will er einfach nicht. Können Sie mir das erklären?
Daten trennen
… und das traurige Ergebnis …
Klar – kann ich! In der einen Zelle steht „9.1: Closed without final decision“, in der anderen „8:Invoiced/Ready“. Wenn Sie den Doppelpunkt als Trennzeichen anlegen erhalten Sie „9.1“, beziehungsweise „8“. Dummerweise wird jedoch „9.1“ als Datum interpretiert – genauer als 09. Januar in diesem Jahr 2015. Excel stellt es als „09. Jan“ dar.
Die Lösung 1: Im letzten Schritt des Assistent können Sie festlegen, dass diese Spalte als Text formatiert werden soll. Dann lässt Excel das vermeintliche Datum in Frieden.
Text
Das Ergebnis
Lösung 2: Oder Sie verwenden eine Formel: Suche die Position des Doppelpunkts (mit SUCHEN oder FINDEN) und schneide von LINKS alle Zeichen abzüglich 1 ab. Beispielsweise so:
=WENNFEHLER(LINKS(U5861;FINDEN(„:“;U5861)-1);““)
Oder mit einer Formel
Lösung 3 funktioniert leider nicht – man kann bedauerlicherweise nicht die Spalte als Text VORformatieren – der Assistent „Text in Spalten“ überschreibt die Formatierung …
Hallo Herr Martin,
Wenn ich in der Tabelle Spalte P filtere (alles außer 1), danach die Spalten AR (nur 11) und AS (nur 2014) filtere, fliegt der Filter in P irgendwie raus, obwohl das Filterzeichen in der Spaltenbeschriftung angezeigt ist. Das habe ich bis dato noch nie gehabt.
Keine Tabelle
Tabelle
Die Antwort: Werfen Sie eine Blick in Ihre Liste. Es fällt auf, wenn Sie in den blauen, linken Teil klicken, dass dort die Registerkarte Tabellentools / Entwurf auftaucht. Im rechten Teil nicht. Sie haben Daten aus einer Datenbankabfrage nach Excel eingefügt – diese wird nun als Tabelle identifiziert. Sie können nicht den linken UND den rechten Bereich gleichzeitig filtern.
Die Lösung: Wandeln Sie die Tabelle über die Registerkarte Tabellentools / Entwurf Schaltfläche „In Bereich konvertieren“ um. Schalten Sie die ZWEI Filter aus und dann einmal EINEN Filter über die gesamte Liste wieder ein. Dann klappt es.
Getrennte Bereiche
In Bereich konvertieren!
Filter ausschalten und wieder einschalten
Seltsam – ich bin sicher, dass Kunden aus Hamburg in unserer Liste vorhanden sind – Excel filtert jedoch keine.
Hamburg und Berlin
Der Grund ist ein sprachlicher. Sie sagen zwar, dass Sie alle Hamburger UND Berliner Kunden filtern, Sie müssen jedoch einstellen Hamburg ODER Berlin. In keiner Zelle steht gleichzeitig Hamburg UND Berlin. Das logische UND bedeutet immer „sowohl – als auch“, also gleichzeitig. Beispielsweise: Hamburg und weiblich, oder PLZ >= 10000 und PLZ < 20000.
Seltsam – ich filtere die Top 10, also die zehn Kunden, die den höchsten Jahresbeitrag bezahlen; aber Excel filtert mehr als zehn – ganz genau: 17 Kunden. Warum denn das?
Top 10
Der Begriff „Top 10“ ist etwas verwirrend – Excel filtern nicht die ersten, größten zehn, sondern mindestens zehn. In Ihrem Beispiel gibt es 17 Kunden, die den höchsten Jahresbeitrag (196 Euro) bezahlen. Eigentlich vernünftig – Excel schneidet nicht nach zehn Zeilen (Datensätzen) die Liste ab, sondern liefert zehn oder mehr Datensätze. Denn – würde er nach zehn Zeilen abschneiden – wäre das Ergebnis unterschiedlich – je nachdem wie die Liste sortiert ist.
Manchmal ist meine Pivottabelle weg. Einfach so!
Pivottabelle ist weg
Die Antwort: In der Pivottabelle können Sie nicht beliebig markieren. Wenn Sie markieren möchten, kann es passieren, dass Sie ein Element aus der Pivottabelle löschen. Die Lösung: Entweder die verwenden die Rückgängig-Funktion oder Sie ziehen die entsprechenden Elemente im Aufgabenbereich PivotTable-Felder wieder dorthin, wo sie hingehören.
Dann wurden die Felder rausgezogen.
Manchmal erhalte ich eine seltsame Meldung, wenn ich Pivottabellen erstelle:
Das Feld, das Sie in den Spaltenbereich ziehen, enthält mehr als 256 Elemente. Nur die ersten 256 Elemente werden als Spalten angezeigt.“
Seltsame Meldung
Die Antwort: Sie verwenden eine Excel-2003-Datei (*.xls). Diese Datei stellt pro Tabellenblatt nur 256 Spalten zur Verfügung. Wenn Sie nun ein Feld in die Spalten ziehen und Excel beim gruppieren der Daten mehr als 256 Elemente ermittelt, hat er ein Problem. Deshalb die Fehlermeldung.
Eine Exceltanelle hat 1.048.576 Zeilen und 16.384 Spalten. Das ist Augenwischerei. Er geht schon vorher „in die Knie“.
In einer Liste befinden sich 10.000 Datensätze (in 16 Spalten). In einer Spalten wird mit der bedingten Formatierung alle doppelten Werte mit einer Farbe gekennzeichnet.
Duplikate finden
Wenn Sie nun die Liste nach den Farben sortieren oder filtern möchten (was eigentlich recht flott geht), wird Excel sehr, sehr langsam. Häufig zeigt er „keine Rückmeldung“ an. Manchmal „fängt“ er sich wieder, manchmal stürzt er an dieser Stelle ab.
Ich vermute, dass beim Sortieren und Filtern im Hintergrund eine permanante Neuberechnung der bedingten Formatierung geschieht, so dass Excel an seine Auslastungsgrenzen gerät.
Keine Rückmeldung
Warum taucht in der Pivottabelle noch die Kategorie „Forschung“ auf? Die haben wir doch längst mit der Abteilung „Forschung und Entwicklung“ zusammengelegt.
Pivottabelle nicht korrekt
Wenn der Bereich der Datenquelle korrekt ist, so wurde die Pivottabelle möglicherweise nicht aktualisiert. Sie können (und müssen!) den Refresh erzeugen über das Kontextmenü, über Analysieren / Daten / Aktualisieren. Oder indem Sie in Analysieren / PivotTable / Optionen / Optionen / Daten die Option einschalten „Aktualisieren beim Öffnen der Datei“.
Pivottabellen müssen aktualisiert werden.
Zuerst war alles wunderbar. Aber irgendwann hat die Pivottabelle nicht mehr richtig gerechnet.
Die Pivottabelle liefert falsche Daten.
Die Antwort finden Sie, wenn Sie sich die Datenquelle ansehen (Analysieren / Daten / Datenquelle ändern). Dann stellen Sie fest, dass wahrscheinlich die Liste erweitert wurde – die Pivottabelle jedoch noch auf den „alten“ Bereich zugreift.
Zwei Lösungen: Entweder Sie vergrößern den Bereich per Hand im Doalogfeld.
Oder Sie wählen beim Erstellen der Pivottabelle die ganze Spalte aus. Dann können Sie die Liste beliebig erweitern.
Der Bereich ist nicht korrekt.
Warum macht Excel so komische Pivottabellen?
Komischer Bereich
Excel benötigt für die Erstellung einer Pivottabelle einen zusammenhängenden und rechteckigen Bereich. Jede Spalte dieses Bereichs muss eine Überschrift (selbstredend in der ersten Zeile) haben. In diesem Beispiel werden die Spalten A bis D als Überschriften identifiziert. Spalten e bis Q haben keine Überschrift in Zeile 1. Dass der Bereich eine Überschrift in Zeile 7 hat, kann Excel nicht erkennen.
Die Lösung: Trennen Sie die den Kopf von dem Datenbereich durch eine Leerzeile. Oder markieren Sie nur den unteren Bereich. Besser: zwei getrennte Bereiche!
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.
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.
In der Pivottabelle stehen keine Daten. Aber in der Liste gibt es Daten.
Die Pivottabelle ist leer.
Vielleicht greift die Pivottabelle auf eine falsche Datenquelle zu. Das kann man leicht mit Analysieren / Daten / Datenquelle ändern überprüfen.
Und siehe da – die Daten werden aus dem Tabellenblatt „2016“ geholt – wahrscheinlich sollen sie aus dem Blatt mit dem Namen „2015“ gezogen werden.
Die falsche Datenquelle
Sehr geehrter Herrn Martin,
In der Excel Tabelle die im Anhang beigefügt ist, bekommen wir Daten von unsere EDV (sehe Sheet 1 vor Verarbeitung). Ich gehe dann in „DATA“ und „Text to Columns“ und spalte diese Tabelle so dass sie nach diese Schritte wie im 2. Sheet (Daten nach Verarbeitung) aussieht.
Das Problem liegt indem einige Zahlen immer noch so erscheinen „1 150,000“ und keine weitere Formatierung möglich ist. Da ich auch eine Summe daraus ziehen möchte.
Wie kann man diese Problem Lösen ?
Originaldaten
Daten nach dem Trennen
Die Antwort: Dummerweise liefert Ihr System die Spalte E so, dass nach dem Tausenderwert als Tausendertrennzeichen ein Leerzeichen verwendet wird. Diese müssen Sie löschen. Ich würde die Spalte (hier E) markieren und dann mit Home / Find & Replace (ganz rechts) das Leerzeichen (einfach ein Blank eintippen) durch nichts ersetzen.
Während die Assistenten sortieren, filtern, Pivottable, Teilergebnis und Diagramme voraussetzen, dass sich der Cursor auf einer Zelle innerhalb des Bereiches befindet, verlangt der Assistent „Text in Spalten“ dass die eine Spalte markiert wurde. Wenn nicht, wird es mit einer Fehlermeldung quittiert.
Es wurden keine Daten zur Analyse markiert.
Manchmal sortiert Excel nur einen Teil der Tabelle. Manchmal die gesamte Tabelle. Das heißt: Manchmal muss ich die Tabelle markieren, manchmal nicht.
Wenn Sie mindestens eine Spalte haben, in der in der Zelle eine Information steht und mindestens eine Zeile, in der in jeder Zelle ein Wert steht, erkennt Excel diesen zusammenhängenden Bereich (currentregion).
Wenn Sie nicht sicher sind, drücken Sie die Tastenkombination [Strg]+[*]. Dann sehen Sie, was Excel sortieren würde.
![[Strg]+[*] zeigt den Bereich, der sortiert wird - man erkennt deutlich eine leere, ausgeblendete Spalte K.](http://www.excel-nervt.de/wp-content/uploads/2015/01/sortieren20150105_2.jpg)
[Strg]+[*] zeigt den Bereich, der sortiert wird – man erkennt deutlich eine leere, ausgeblendete Spalte K.
Warum bringt Excel manchmal beim Sortieren so eine merkwürdige Fehlermeldung?
Am besten nicht markieren!
Die Antwort: Markieren Sie bitte nichts, wenn Sie sortieren. Setzen Sie nur den Cursor auf ein Feld, dessen Spalte Sie sortieren möchten und klicken anschließend auf das Symbol A↓Z (oder Z↓A). Wenn Sie markieren, stellt Excel die Frage, ob Sie nur diese Spalte sortieren möchten – die Daten links und rechts bleiben so stehen – das ist fatal und meistens nicht gewünscht!
Das Sortieren und Filtern geht nicht mehr. Warum?
Der Cursor sitzt außerhalb des Bereichs, den Sie sortieren oder filtern möchten. Übrigens: Auch der Assistent „Teilergebnis“ funktioniert nicht mehr.
sortieren und filtern geht nicht mehr.
Auch das kann zwei Ursachen haben. Entweder Sie verwenden falsch Feldnamen. Wenn beispielsweise in der Datenliste die Überschrift „Stadt“ lautet und Sie schreiben dann „Ort“, so wird kein Ort=Muenchen gefunden.
Noch perfider ist der Fehler, wenn Sie nicht „Ort“ eintragen, sondern „Ort „, also ein Leerzeichen hinter den Feldnamen.
So kann der Spezialfilter nicht filtern.
Wenn der Spezialfilter nicht richtig arbeitet, haben Sie entweder die Kriterien falsch formuliert oder den Bereich falsch markiert.
Wenn Sie beispielsweise leere Zellen mitmarkieren, bedeutet dies, dass der Filter sowohl die Kriterien als auch jeden beliebigen Bereich filtern soll.
Zu viele Daten werden gefiltert
Bei den Kriterien wurden leere Zellen markiert.
Warum funktioniert der Spezialfilter nicht? Ich habe doch alles richtig gemacht?
Antwort: Der Spezialfilter hat einige „Tücken“. Der Cursor muss auf dem Tabellenblatt stehen, auf das die Daten hingefiltert werden sollen. Befinden sich also in tabelle1 die Daten, befindet sich Tabelle2 die Kriterien, dann muss der Cursor auf Tabelle3 stehen, wenn die Daten dorthin gefiltert werden sollen.
Spezialfilter funktioniert nicht.
Was bitte sind Stripsets in Pivottabellen und warum funktionieren sie nicht?
Die Antwort: Nur ein Stripset einzuschalten nützt nichts. Man muss noch zusätzlich „Verbundene Zeilen“ (oder „verbundene Spalten“ aktivieren!
Das Stripset bewirkt nichts.
Doch – man muss die Zeilen verbinden!
Seltsam – ich könnte schwören, dass Kunden aus München in dieser Liste stehen. Die Liste des Autofilters zeigt allerdings keinen an.
Der Grund ist: es wurde bereits ein Filter eingeschaltet (hier: in der Spalte „Name2“). Möglicherweise sind zufällig alle Münchner Daten auf diese Art weggefiltert. Hier sieht man den gesetzten Filter zufälligerweise – es könnte jedoch sein, dass die Spalte, in der der Filter gesetzt wurde, außerhalb des Bildschirms steht.
Tipp: Dann werfen Sie einen Blick in die Registerkarte „Daten“ – wenn dort das Symbol „Löschen“ aktiv ist, so ist noch irgendwo ein Filter gesetzt.
Wo sind die Kunden aus München?
Schalten Sie zuerst den einen Filter aus und dann den zweiten Filter ein.
Eigentlich klar – der Assistent „Daten / Text in Spalten“ funktioniert nur dann, wenn nur EINE Spalte markiert ist.
Übrigens: Er funktioniert auch dann nicht, wenn Teile der Spalte einzeln mit der [Strg]-Taste markiert wurden.
Nur eine Spalte darf markiert sein.
Eine Spalte darf nicht in Teilen mit gedrückter [Strg]-Taste markiert werden.
Der Assistent „Daten / Text in Spalten“ wartet mit einer merkwürdigen Fehlermeldung auf. Was ist los?
Nun – er setzt voraus, dass Daten markiert werden – normalerweise eine Spalte. Dann klappt es.
Es wurden keine Daten zur Analyse markiert.
Eine merkwürdige Fehlermeldung, die auftritt, wenn Sie den Assistenten „Text in Spalten“ verwenden, den Sie im Register „Daten“ finden. Er kann zwei Ursachen haben:
1. Einige der Texte haben mehrere Trennzeichen und würden tatsächlich Daten überschreiben (hier im Beispiel „Peter J. Krebs“, dessen Nachname in der Spalte „Position“ stehen würde.
2. Es befinden sich Formatierungen in den Zellen, beispielsweise in Zeile 1 – die lila Hintergrundfarbe. Das genügt für Excel um von „Daten“ auszugehen.
Tipp: Wenn Sie unsicher sind, kopieren Sie die Spalte auf ein leeres Tabellenblatt und trennen dort die Daten. Schauen Sie sich dann das Ergebnis an.
Der Assistent „Daten / Text in Spalten“
Er würde Daten überschreiben.
Oder Formate werden als Daten identifiziert.
Erstaunlicherweise verschwinden die Daten, wenn ich nach der Postleitzahl filtere. Warum?
In diesem Fall wurde die PLZ als Zahl eingetragen (was man daran erkennen kann, dass sie rechtsbündig in der Zelle stehen). Wenn Sie nun die Zahlen mit „Beginnt mit 5“ oder „5*“ oder „5????“ filtern, dann vermischen Sie Text und Zahl. Das Ergebnis – Excel findet keinen Datensatz mit einem Postleittext, der mit 5 beginnt.
Die Lösung: Filter Sie „größer oder gleich 50000 und kleiner 60000“. dann klappt es.
Filtern klappt nicht.
Wenn Sie schon mit Excel 2003 (oder noch älteren Versionen) gearbeitet haben, haben Sie sich vielleicht gefragt, wo denn der alte Pivottabellen-Assistent geblieben ist. Damit konnte man mehrere Excel-Tabellen zusammenfassen.
Nun die Antwort ist einfach: Er ist seit Office 2007 nicht verschwunden, sondern nur sehr versteckt: Man muss ihn als Symbol in die Symbolleiste für den Schnellzugriff hinzufügen. Er befindet sich beispielsweise in der Kategorie „Nicht im Menüband enthaltene Befehle“ und heißt „PivotTable- und PivotChart-Assistent“.
Der „alte“ Pivottabellen-Assistent, mit dem man mehrere Bereiche zusammenfassen konnte.
Haben Sie sich auch gefragt, warum Excel das Ziel der Pivottabelle „Quelle“ nennt? Nun, das ist eine schräge Übersetzung aus dem englischen. Dort wurde der Dialog der Pivottable mit „Location“ übersetzt, also Zielort und nicht Quellbereich …
Location
Unglückliche Übersetzung
Beim Sortieren erscheint eine „lustige“ Fehlermeldung statt dem Ergebnis der Sortierung.
Der Grund: einige der Zellen sind verbunden (hier: der Vorgesetzte). Dadurch werden aus mehreren Zellen jeweils eine Zelle. Diese kann nun nicht sortiert werden.
Die Lösung: Markieren Sie das gesamte Arbeitsblatt und heben mit einem Klick den Zellverbund aller Zellen wieder auf.
Sortieren funktioniert nicht.
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
Es muss nicht immer der Schutz einer Tabelle sein, warum sortieren und filtern verhindert wird.
Ein Blick in die Titelzeile liefert den Hinweis, dass mehrere Tabellen ausgewählt wurden. Deshalb sind alle Befehle der Registerkarte „Daten“ inaktiv. So etwas passiert häufig, wenn man statt mit [Strg]+[Bild ↓] auf das nächste Blatt mit der Tastenkombination [Shift]+[Strg]+[Bild ↓] beide Tabellenblätter auswählt. Oder wenn man man Wechseln auf ein anderes Tabellenblatt fälschlicherweise die [Shift]-Taste gedrückt hält. Leider sieht man an den Farben der Registerkarten nicht gut, dass mehrere Tabellenblätter ausgewählt wurden. Die Titelzeile jedoch verrät es …
Sortieren und Filtern funktioniert nicht.
Oft werde ich gefragt, was die Fehlermeldung „Der Datenquellenverweis ist ungültig“ bedeutet. Warum man keine Pivottabelle erstellen kann. Die Antwort liegt meistens darin begründet, dass sich der Cursor außerhalb der Datenquelle (der Liste) befindet und Excel somit keine Tabelle „findet“.
Lösung: Entweder den Cursor zuvor in die Liste setzen oder im Dialogfeld den Bereich auswählen.
Pivottabelle geht nicht.