Eine Liebeserklärung an MS Excel
Diesmal kein Rumnörgeln – ich kann auch anders:
Zwischen Tabellenblättern bewegen
Über merkwürdige Prozente / Prozentwerte habe ich hier in diesem Blog schon einige Male mich geäußert. Nun wieder:
Geben Sie ein paar Prozentwerte ein. Legen Sie eine bedingte Formatierung drüber mit einem Symbolsatz. Das Ergebnis verblüfft:
Warum wird die Zelle, in der 70% steht mit einem vollen Kreis gekennzeichnet – 70% ist doch nicht >=80 Prozent?
Des Rätsels Lösung: Wenn Sie jeden Wert in Verhältnis zum größten Wert setzen, also beispielsweise
=D2/MAX($D$2:$D$16)
dann ergeben sich andere Werte – nämlich 88% bei 70%.
Das heißt: 80% heißt bei Excel:
80% des größten Wertes der Liste. Dabei wird die Liste dynamisch erweitert oder verkleinert wenn Sie Werte löschen oder hinzufügen. Warum sagen die das nicht gleich? So? Denn: wenn Wert >= 80% ist so falsch!
Ein großes Dankeschön an Peter, der mich auf diese Merkwürdigkeit, auf dieses verwirrende Phänomen und auf dieses auf den ersten Blick erstaunliche Verhalten hingewiesen hat. Er schreibt dazu:
„Es ist eben für den arglosen Benutzer nicht erkennbar, dass die Auswahl Prozent in der Symbolformatierung eine gänzlich andere Rechenlogik besitzt als die Formatierung Wert.“
Wisst ihr wie ich das gemacht habe? Nein – das Bild ist nicht bearbeitet! Heute beim Programmieren habe ich erstaunt festgestellt, dass in einer Zelle anderer Text steht als in der Bearbeitungsleiste.
Nun – ein paar Zeilen Code:
Range(„E1“).Value = „Nervt Excel?“
Application.ScreenUpdating = True
[Hier muss Code stehen, der einige Sekunden benötigt, um ausgeführt zu werden]
Application.ScreenUpdating = False
Range(„E1“).Value = „Excel nervt!“
Die Zeile
Application.ScreenUpdating = True
bewirkt die Anzeige der „Sanduhr“ und bewirkt, dass nur nur die Tabelle aktualisiert wird – nicht jedoch die Bearbeitungsleiste.
Achtung: Code muss zwei Mal ausgeführt werden, damit ich „Excel nervt!“ und „Nervt Excel?“ sehe.
gestern in der Excel-Schulung wollte eine Teilnehmerin wissen, warum „ersetzen“ (von suchen und ersetzen) als Funktion „WECHSELN“ heißt und nicht „ERSETZEN“. Denn ERSETZEN macht ja etwas anderes:
Die Antwort: Ich weiß es nicht. Auch im Englischen heißen die Funktionen SUBSTITUTE und REPLACE. Irgendwie doof gemacht …
Diesmal kein Rumnörgeln – ich kann auch anders:
Alles zu Kommentaren
Amüsant. Ist Ihnen das schon aufgefallen? – Wenn man mehrere Zellen in Excel markiert, steht in der Statuszeile Anzahl, Summe, … Man kann die Liste der Funktionen erweitern.
Noch nie aufgefallen ist mir Folgendes: Wenn man formatierte Zahlen markiert – beispielsweise Zahlen mit Tausenderpunkt und ohne Nachkommastellen, wird die Summe und der Mittelwert ebenso formatiert. Ebenso Minimum und Maximum. Anzahl und Numerische Zahl jedoch nicht:
Als „Standard“ formatierte Zahlen erhalten folgendes Ergebnis:
Das ist konsequent, dass Anzahl keine Nachkommastellen hat – aber ein Tausendertrennzeichen hätte man der armen Anzahl schon spendieren können. Finden Sie nicht?
Amüsante Fehlermeldung. Dabei wollte ich doch nur ein weiteres Feld in eine Pivottabelle einfügen:
Aber die Ursache ist schnell gefunden: Neben der Pivottabelle befand sich eine Formel. Excel kann nicht eine Spalte einfügen, verschiebt also nicht die Tabelle, sondern überschreibt die Formel:
Die Frage bleibt: Hätte man nicht „Tabelle2 enthält bereits Daten“ etwas anders formulieren können?
Schlimm genug, dass Microsoft seine Beschriftungen von Version zu Version ändert – auch neue Übersetzungen müssen nicht besser sein.
Noch schlimmer dagegen ist, dass dies nicht einheitlich gemacht wird: So wurde aus dem Zeilenumbruch der Textumbruch – aber leider nicht überall:
Diesmal kein Rumnörgeln – ich kann auch anders:
Leerzeichen löschen
Man weiß nie alles in Excel. Und die meisten von uns möchten mehr wissen.
Deshalb veranstaltet die Munich-Office-Group, bei der ich mitarbeite, am 20/21. Oktober 2017 in München die EXCELlent Days:
9 hervorragende EXCEL-Kenner erläutern in 3 parallel laufenden Tracks in 24 Vorträgen Lösungen für kniffelige Probleme rund um Microsoft Excel und zeigen neue effiziente Wege auf.
Die Vorträge beginnen dort, wo die Excel-Aufbaukurse enden
Weitere Informationen findet Ihr auf unserer Seite
http://www.munich-office-group.de/
Ich würde mich freuen, wenn wir uns in München im Oktober auf der Veranstaltung sehen würden. Ich freue mich auch darüber, wenn ihr Werbung bei Kolleginnen und Kollegen dafür macht.
Amüsant. Ich schreibe in eine Zelle, die mit der Schriftart „Calibri“ formatiert ist, mit dem Zahlenformat und mit dem Zellformat „Standard“ einen Text.
Ich bestätige die Eingabe:
Schwupp: Lustige Zeichen.
Der Kenner bemerkt sofort, dass Excel die Zelle automatisch in der Schriftart WingDings formatiert hat. Der Grund: in den drei Zellen darüber befinden sich Zeichen, die über Einfügen / Symbol aus der Schriftart WingDings eingefügt wurden. Sobald mindestens drei WingDings-Zeichen übereinander stehen, „vermutet“ Excel, dass das nächste Zeichen nun auch in der gleichen Schrift formatiert werden soll. Will ich das? Nein!
Amüsant. Wollte gerade etwas ausprobieren:
Ich nenne eine Spalte „Name“. Eine zweite Werte. Ich trage Phantasienamen und -werte ein. Setze eine Pivottabelle auf. Nö – „Werte“ wird nicht akzeptiert – das muss schon Werte2 heißen!
Was ist denn das schon wieder? Ich darf den Filter „löschen“ aber nicht ausschalten! Auch alle anderen Schaltflächen sind „ausgegraut“.
Die Antwort: Auf dem Tabellenblatt befindet sich eine Linie. Diese Linie ist markiert. Deshalb sind viele Tabellenfunktionen deaktiviert.
So schnell kann man alt aussehen. Versuchen Sie mal Folgendes:
Erstellen Sie eine Liste und formatieren Sie diese als intelligente Tabelle.
Legen Sie einen Datenschnitt fest.
Tragen Sie neben der Tabelle Kriterien ein und filtern die Tabelle mit Hilfe des Spezialfilters („Erweitert“) an eine andere Stelle.
Und schon ist der Datenschnitt veraltet! So schnell geht es!
Zum Glück kann man ihn wieder aktualisieren.
Letzte Woche in der Excel-VBA-Schulung. Um die Objekte näher zu bringen, beginne ich mit mit dem Makrorekorder. Wir zeichnen eine Reihe Befehle auf. Beispielsweise:
Füge in die Kopfzeile ein Bild ein:
ActiveSheet.PageSetup.RightHeaderPicture.Filename = _
„F:\Eigene Bilder\Bali\PIC00020.jpg“
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = „“
.PrintTitleColumns = „“
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = „“
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = „“
.CenterHeader = „“
.RightHeader = „&G“
.LeftFooter = „“
.CenterFooter = „“
.RightFooter = „“
[…]
Das Ergebnis:
Wir löschen das Bild und führen das Makro erneut aus. Das Ergebnis: nichts! Ein Blick in den Dialog „Seite einrichten“ zeigt jedoch, dass etwas in der Kopfzeile ist. Ein Bild?
Der Grund ist schnell gefunden: Die Zeile:
Application.PrintCommunication = False
„Gibt an, ob die Kommunikation mit dem Drucker aktiviert ist.“ Deshalb wird zwar das Bild eingefügt aber nicht angezeigt. Also: Zeile löschen – und schon funktioniert es. Manchmal (oft!?!) liefert der Makrorekorder eben doch nicht den besten Code …
Übrigens: auf der Microsoft-Seite findet sich folgende Erklärung:
„Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die Ausführung von Code zu beschleunigen, der PageSetup-Eigenschaften festlegt. Legen Sie die PrintCommunication-Eigenschaft auf True fest, nachdem Sie Eigenschaften zum Ausführen eines Commits aller zwischengespeicherten PageSetup-Befehle festgelegt haben.“
Aha!
Diesmal kein Rumnörgeln – ich kann auch anders:
In einer Zahlenreihe Texte finden
Letzte Woche in der Excel-Schulung. Wir üben das gestalten von Tabellen. Eine Teilnehmerin fragt mich, was sie gemacht hat. Das Ergebnis von schräggestelltem Text neben vertikal verlaufendem Text ist verblüffend:
Noch erstaunlicher ist der Effekt, wenn ein Text nach links und einer nach rechts geneigt wird:
Und: Finger weg vom Zusammenspiel Textausrichtung: schräg UND Linien:
Meine Empfehlung: Finger weg von schräg. Das ist wirklich schräg!
Ich dachte, das Zahlenformat „Standard“ bedeutet, dass Zahlen „in Frieden“ gelassen werden. Also: ohne Tausendertrennzeichen, ohne Rundungen der Nachkommastellen.
Denkste!
Eine Zahl, die insgesamt mindestens elf Ziffern hat (beispielsweise acht Ziffern vor dem Komma und drei danach oder auch: eine Ziffer vor dem Komma und zehn nach dem Dezimaltrennzeichen) werden trotz oder auch beim Zahlenformat „Standard“ gerundet formatiert.
Irgendwie doof. Mal wieder nicht konsistent.
Kennen Sie das? Ich erstelle eine Liste mit Verkäufernamen, Monatsnamen und Umsatzzahlen. Über Formeln / Definierte Namen / Aus Auswahl erstellen werden die Spaltennamen und Zeilennamen zu Namen der entsprechenden Zeile und Spalte:
Nun kann man die Schnittmenge berechnen:
=Roth Umsatz
Leider kann man diese Werte nicht auslagern – das führt zu einem Fehler:
Das ist erstaunlich, denn folgende Formeln funktionieren problemlos:
=SUMME(INDIREKT(„C2:C7“))
=SUMME(INDIREKT(„Umsatz“))
Aber eben leider nicht:
=SUMME(INDIREKT(„Umsatz Roth“))
und auch nicht:
=SUMME(INDIREKT(„C2:C5 C3:C7“))
Schade, dass INDIREKT keine Schnittmenge verarbeiten kann.
Nachtrag: Danke an XLarium für den wertvollen Hinweis (⇓):
Es funktioniert mit:
=INDIREKT(„Umsatz“) INDIREKT(„Roth“)
und:
=SUMME(INDIREKT(„C2:C5“) INDIREKT(„C3:C7“))
Excel ist eben eine echte Liebesbeziehung. Was nervt wird liebevoll beschrieben, was funktioniert ist „normal“ 🙂
Tolle Seite, macht Spaß. Vielen Dank Herr Martin
Margrit
Erinnern Sie sich noch? Windows 7? Die Beispielbilder: Wüste, Tulpe, Qualle, Koala, …
Ein Teilneer der Excel-VBA-Schulung möchte Bilder per VBA in seine Exceldatei einfügen. Die Dateinamen stehen dabei bereits in einer Exceltabelle.
„Kein Problem – schreiben Sie einfach ein paar Dateinamen in eine Tabelle.“ Alle Teilnehmer verwenden den Ordner „Beispielbilder“, kopieren den Pfad, tippen die Dateinamen mit der Endung ab. Ich verwende meinen eigen Ordner, in dem ich ein paar Urlaubsbilder habe.
Wir lassen das Makro laufen:
Dim strDateiname As String
Dim i As Integer
For i = 1 To ActiveSheet.Range(„A1“).CurrentRegion.wors.Count – 1
strDateiname = ActiveSheet.Range(„A1“).Offset(i, 0).Value
ActiveSheet.Pictures.Insert strDateiname
Next
Während es bei mir funktioniert, erhalten die Teilnehmer eine Fehlermeldung. Die Dateinamen sind doch korrekt geschrieben, oder:
Ein Blick in die Eigenschaften – Registerkarte „Sicherheit“ belehrt mich eines Besseren. Die Datei Wüste heißt „desert.jpg“, die Qualle „jellyfish.jpg, „penguins .jpg“, „lighthouse.jpg“, …
Ich gestehe – das ist mir noch nie aufgefallen, dass die Dateien einen Alias tragen, also dass die Beispielbilder eigentlich einen anderen Dateinamen aufweisen.
Amüsant: Ich darf eine Zelle „mfg“ nennen, also ihr den Namen „mfg“ geben.
Aber „mfg2“ darf ich sie nicht nennen:
Der Grund ist einleuchtend: da man Namen über das Namensfeld (links neben der Bearbeitungsleiste) vergeben kann, würde ein dort eingegebener Name zur Zelle MFG2 springen. Deshalb dürfen auch nicht die Namen „MF2“ oder „M2“ vergeben werden. Nur „mfg_2“.
Nicht mehr verständlich ist es jedoch, wenn Sie ein Makro mit dem Makrorekorder aufzeichnen, das sie „mfg2“ nennen. DAS ist nicht erlaubt.
Ganz unverständlich wird die Sache jedoch, wenn Sie im Visual Basic-Editor ein Makro erstellen, das den Namen „mfg2“ trägt. DORT ist der Name erlaubt und bereitet keine Probleme ?!?
Heute in der Outlook-Schulung kam die Frage auf, warum der Export- und Importassistent, verschwunden ist, mit dem man Kontakte nach Excel exportieren, beziehungsweise importieren konnte. Ich blieb der Teilnehmerin eine Antwort schuldig. Wahrscheinlich würde Microsoft antworten, dass keiner mehr diese Funktion benutzt hat …
Die Reste des Assistenten
Hallo René,
wie geht es Dir? Bist Du gut wieder nach München gekommen nach dem Aufenthalt bei uns hier in Leipzig?
Ich habe ein Problem in Excel und wenn Deine Zeit es mal erlaubt, dann bitte helfe mir bei der folgenden Angelegenheit.
Ich möchte gern bei Excel in einer Zelle eine Datumsauswahl (Aktives Steuerelement) einfügen, aber ich kann unter Entwickler-Tools kein „Microsoft Data and Time Picker Control“ finden.
Evtl. gibt es ein „Picker“, welchen ich dann formatieren kann…
Ich komme nicht zur Lösung und möchte es gern in meinem Excel-Sheet integrieren.
Das möchte ich erreichen
Vorab vielen Dank für Deine Bemühungen!
Viele Grüße nach München.
Daniel
Hallo Daniel,
Ich rate dir von weiteren Steuerelementen ab. Der Grund: Sie müssen auf dem PC installiert sein.
Wenn du kein solches Element hast (beispielsweise weil kein Visual Studio installiert ist) – dann hast du es auch nicht. Könnte man runterladen aus dem Internet.
Jedoch: wenn du die Datei weitergibst, muss dieses Steuerelement auf dem Zielrechner auch installiert sein. Sonst geht es nicht.
Also doch lieber eintippen.
Weißt du noch: ein Datum kann man auf dem Zahlenblock beispielsweise 13-05-2017 eingeben.
Hallo René,
und hier sende ich Dir die Datei mit meinen Hinweisen.
Aber warum nimmt Excel die neuen Zielumsätze (denen kein Umsatz vorausgegangen war) nicht ins Gesamtergebnis auf?
Ich berechne: Zielumsatz = WENN(Umsatz>0;Umsatz*110%;500)
Viele Grüße
Angelika
Hallo Angelika,
Ich weiß wo der Denkfehler – oder der Pivotfehler steckt:
Pivot rechet nicht Summe der einzelnen berechneten Werte, sondern: berechnet die Summe:
Also nicht (in deinem Beispiel 3.045,90 + 500,00 + 594,83 + 2.692,80), die ja berechnet sind: = WENN(Umsatz>0;Umsatz*110%;500)
sondern Pivot rechnet: 2.769,00 + 0,00 + 540,75 + 2.448,00. Wenn diese Summe (5.757,75) > 0;Umsatz*110%;500) -> also 5.757,75 + 1,1 = 6.333,53
Nervt Excel?
Liebe Grüße :: Rene
Am 20./21. Oktober finden in München die ersten Excellent-Days statt:
+ 24 hervorragende Referate
+ 9 ausgezeichnete Referenten
+ stehen Ihnen auch zu einem persönlichen Gespräch zur Verfügung
+ In München; Hotel Le Méridien
+ Weitere Infos: Excellent-Days
Kennen Sie das? Sie arbeiten mit Excel, Kinder oder Nichten und Neffen mit libreOffice, ein Freund mit Numbers oder Google Tabellen? Und Sie sind erstaunt, dass es in der einen Tabellenkalkulation Funktionen gibt, die in der anderen fehlt. Nervig und ärgerlich (gerade beim Austausch der Tabellen).
Der Zeitschriftenverlag Heise hat sich die Mühe gemacht, sämtliche Funktionen dieser vier Tabellenkalkulationen aufzulisten und zu vergleichen:
https://www.heise.de/mac-and-i/downloads/65/2/1/7/2/5/5/4/Formelfunktionen_Vergleich.pdf
Beim Durchsehen der Liste fällt auf, dass auch in dem geliebten Excel einige (wichtige) Funktionen fehlen, die in einem der anderen Programme integriert sind:
Ostersonntag, Tagname, Monatsname, BasisInZahl (habe ich noch nie vermisst), Laufzeit, ZGZ, ISEMAIL, ISURL, AKTUEL, FORMEL (heißt in Excel: FORMELTEXT), BEREICH.ÜBERSCHNEIDEN, BEREICH.VERBINDEN, POLYNOM, COUNTUNIQUE, FARBE, UMRECHNEN (entspricht EUROKONVERT), B, KOVARIANZ (heißt KOVAR, KOVARIANZ.P und KOVARIANZ.S in Excel), SCHÄTZER.EXP.VOR.MULT, SCHÄTZER.EXP.MULT, KLARTEXT, REGEXEXTRACT, REGEXMATCH, REGEXREPLACE und 59 weitere Funktionen …
Formeln im Vergleich (Ausschnitt)
Heute in der Excel-VBA-Schulung. Wir erstellen ein Beispiel: Zähle solange eine Nummer hoch, bis es eine Datei mit der entsprechenden Nummer nicht mehr gibt. Speichere dann die Datei unter dieser Nummer.
Bei mir funktioniert das Beispiel. Ein Teilnehmer hatte einen Fehler und rief mich. Ich war erstaunt über die Fehlermeldung:
Dateiname oder -nummer falsch
Noch erstaunter war ich, dass die Datei gespeichert war in:
D:\D:\Eigene Dateien\Excel\…
Zwei Mal D:\D:\ …?!?
Zuerst wollte ich Excel wüst beschimpfen, dann schaute ich mir den Code genauer an. Der Teilnehmer hatte die Datei gespeichert in.
„D:\“ & strPfad & strDatei
Erstaunlich: die Datei ist im Explorer nicht auffindbar. Excel (und VBA) behaupte jedoch, dass der FullName lautet:
D:\D:\Eigene Dateien\Excel\…
Hallo Herr Martin,
ich benötige eine Funktion, die das Zeichen π (pi) einfügt. Allerdings zeichnet der Makrorekorder auf:
ActiveCell.FormulaR1C1 = „?“
oder:
ActiveCell.FormulaR1C1 = „P“
Die Antwort: Sie müssen das Makro nachbearbeiten. Beispielsweise:
ActiveCell.Value = „P“
ActiveCell.Font.Name = „Symbol“
Am 20./21. Oktober finden in München die ersten Excellent-Days statt:
+ 24 hervorragende Referate
+ 9 ausgezeichnete Referenten
+ stehen Ihnen auch zu einem persönlichen Gespräch zur Verfügung
+ In München; Hotel Le Méridien
+ Weitere Infos: Excellent-Days
Gestern in der Zeitung gesehen (SZ vom 08.05.2017; S. 1):
Versucht in Excel nachzubauen. Gescheitert. Mit einer Sekundärachse klappt es nicht.
Dann kam ich auf die Idee die Linienstärke der einen Balkenreihe zu erhöhen. Klappt:
Gestern in der Excel-Schulung. Wir üben die WENN-Funktion. Standard-Beispiel: eine Provisionsberechnung. Ein Teilnehmer ruft mich, weil er eine Fehlermeldung erhält:
Ich gestehe: ich habe drei Mal hinschauen müssen, bis ich es entdeckt hatte: das schließende Anführungszeichen bei dem Sonst-Zweig (Wert_wenn_falsch) fehlt.
Hallo Herr Martin,
[…] Dennoch herzlichen Dank für Ihre verdammt schnelle Hilfe.
Übrigens: wo immer ich mit Excel zu tun habe, verweise ich auf Ihre Excel-nervende Website, die mit Begeisterung aufgenommen wird.
===========================
danke für den netten Kommentar – so etwas freut mich
Rene Martin
Perfide!
Ich erstelle zwei bedingte Formatierungen. Die eine überprüft, ob in Spalte F ein Wert > 800 steht. Wenn ja, dann wird die Schriftfarbe auf blau gesetzt. Die zwei Bedingung wird auf die gesamte Tabelle angewendet. Sie formatiert die Hintergrundfarbe (mit einem anderen) Blau. Das Ergebnis sieht wie folgt aus:
Wechselt man in den Dialog „Formatieren“ auf die Registerkarte „Ausfüllen“, ist die Schaltfläche „Keine Farbe“ der Hintergrundfarbe unterlegt.
Begeht man jedoch den Fehler und klickt auf diese Schaltfläche, sieht man zwar auf dem Dialog keinen Unterschied – allerdings wird nun die Option „Keine Farbe“ aktiv – das heißt: die blaue Hintergrundfarbe wird von „keiner Farbe“ überschrieben.
Irgendwie doof …
Dummer Doppelpunkt.
Ich wollte doch nur „Beispiel 2:“ herunterziehen, damit ich die Texte Beispiel 3:, Beispiel 4:, Beispiel 5:, … erhalte. Schade – geht nicht!
Liebe VBA-User: Ist euch das schon aufgefallen:
Ich erstelle in Excel 2016 eine UserForm. Auf der UserForm befindet sich eine Befehlsschaltfläche mit folgenden zwei Codezeilen:
Workbooks.Add
Unload Me
In dem Projekt befindet sich ein Makro:
Sub MaskeStart()
UserForm1.Show
End Sub
Dieses Makro wird an eine Schaltfläche auf dem Zeichenblatt gebunden (dabei ist es egal, ob es sich um ein Formularsteuerelement oder ein Active-X-Steuerelement handelt.
Ich „mache das Steuerelement scharf“, klicke darauf, die Maske startet, eine neue Datei wird geöffnet, in der ich allerdings keine Registerkarte aktivieren kann. Das war doch in älteren Excelversionen nicht der Fall, oder irre ich mich?
Amüsant: In Visio werden in Shapes ein paar Felder eingefügt. Diese Informationen werden mit VBA nach Excel exportiert:
Das Ergebnis: Zeichen, die ich noch nie in Excel gesehen habe:
Hallo,
ich konnte bei meinem geliebten Excel 2003 mehrere Tabellenbereiche, die nacheinander mit den gleichen verschachtelten Sortierkriterien sortiert werden sollten, einfach nacheinander markieren, nach dem ersten Block die Kriterien erstellen und sortieren, dann nach dem zweiten, dritten, … Block einfach nur Strg-Y zum Wiederholen drücken und die gleichen Kriterien wurden auf den nächsten Block angewendet.
Wenn ich das jetzt in Excel 2016 versuche, wird beim Drücken von Strg-Y nicht der aktuell markierte Bereich mit den vorher erstellen Kriterien sortiert, sondern die Sortierung des vorher markierten Blocks wird wiederholt.
Und die mühevoll erstellten Sortierkriterien vergisst Excel zu allem Überfluß dann auch noch, daß ich diese beim jedem neuen Block manuell neu erstellen muss.
Das nervt – und verdient es vielleicht, in die Rubrik der nervenden Excel-Features aufgenommen zu werden…
Oder bin ich einfach nur zu dämlich? Gibt es etwa irgendeine versteckte Einstellung, die bewährte Funktion des Wiederholens einer Sortierung wieder zu reanimieren? Oder ist hier eine wichtige Funktion einfach unterschlagen worden?
Viele Grüße
einmal sortieren …
… aber dann ist auch wirklich genug sortiert!
Hallo Herr J.,
und das ging früher wirklich? Ich gestehe: im „alten“ Excel habe ich das nie gemacht/benötigt. Ich gestehe – ich habe kein „altes“ Excel mehr hier – aber ich glaube Ihnen mal. Ich wüsste auch nicht, wie man das Sortieren auf eine andere Art wiederholen könnte.
Wenn Sie es in ein Forum stellen, werden gefühlte 100.000 Excel-User posten „das kann man doch programmieren“. Andere 100.000 werden schreiben „nimm doch eine Datenbank, bspw. Access“.
schöne Grüße und Danke für den Hinweis
Rene Martin
PS : Ist das ein Trost: gerade probiert – in libreOffice Calc funktioniert „Wiederholen“ auch nicht …
Heute in der VBA-Schulung war ich verblüfft. Wir wollten herausfinden, wie der Befehl für das Zahlenformat „Währung“ lautet. Ich forderte die Teilnehmer auf, den Befehl „formatiere“ eine Zelle als Währung aufzuzeichnen und sich das Ergebnis anzusehen. Wir erhielten vier verschiedene Ergebnisse.
Vier? Zwei hätte ich verstanden. Währung und Buchhaltung. Aber vier? Die VBA-Befehle lauten:
‚ — Buchhaltung
Selection.NumberFormat = _
„_-* #,##0.00 [$€-de-DE]_-;-* #,##0.00 [$€-de-DE]_-;_-* „“-„“?? [$€-de-DE]_-;_-@_-“
Selection.NumberFormat = „_($* #,##0.00_);_($* (#,##0.00);_($* „“-„“??_);_(@_)“
‚ — Währung
Selection.NumberFormat = „#,##0.00 $“
Selection.NumberFormat = „$#,##0.00_);[Red]($#,##0.00)“
Schaut man das genauer an, findet man die Unterschiede:
Denn – es ist ein Unterschied, ob man das Symbol „Buchhaltungszahlenformat“ in der Gruppe „Zahl“ verwendet oder „Buchhaltung“ aus dem Kombinationsfeld der gleichen Gruppe (oder über den Dialog Zellen formatieren / Zahlen / Buchhaltung).
Es ist ein Unterschied, ob Sie das Symbol „Währung“ verwenden (oder über den Dialog Zellen formatieren / Zahlen / Währung) oder – die Tastenkombination [Shift] + [Strg] + [$]. Ob das nicht zu Problemen führen kann?
Erstaunt hat es mich doch für einen Moment.
Für die Teilnahmebestätigungen der Excel-Schulung morgen markiere ich in Outlook in der Mail die Namen und kopiere sie nach Excel. Und wundere mich, warum sie in der Spalte neben der Spalte stehen, in die ich sie eingefügt habe:
Klar: Excel übernimmt den Einzug der Absatzformatierung von Outlook. Kann man leicht im Dialog „Zellen formatieren“ nachsehen:
Amüsant.
Ich gebe in Excel eine große Zahl ein. Ich kann sie mit dem benutzerdefinierten Zahlenformat 0.. formatieren. Damit werden die letzten sechs Ziffern nicht mehr dargestellt, das heißt: wegformatiert.
Das funktioniert prima, wenn in der Windows-Systemsteuerung als Sprache Deutsch (Deutschland) oder Deutsch (Österreich) eingestellt ist. Ist jedoch Deutsch (Schweiz) voreingestellt, so finden sich in den „Weiteren Einstellungen“ bei dem Symbol für Zifferngruppierung ein Apostroph als Zeichen und nicht ein Punkt. Somit funktioniert dieses benutzerdefinierte Zahlenformat mit dieser Einstellung nicht. Das heißt: bei den „normalen“ Schweizer Einstellungen klappt das nicht.
Die Windows-Einstellungen
Amüsante Randbemerkung: Ich Deutscher öffne Excel, formatiere eine Zahl mit 0..
Stelle nun das Symbol für Zifferngruppierung auf Apostroph um.
Excel ändert die benutzerdefinierte Formatierung mit.
Bei einer Neuformatierung verweigert er sich natürlich bei 0..
Kann praktisch – kann ärgerlich sein – Stichwort: Datenaustausch.
Seit ein paar Tage ist mein Video-Training „Excel 2016: Tipps, Tricks, Techniken. Schneller, eleganter und besser arbeiten“ ist bereits erschienen.
Unter folgendem Link findest du es: https://www.video2brain.com/de/videotraining/excel-2016-tipps-tricks-techniken
Unter diesem Link findest du es auf LinkedIn Learning:
https://www.linkedin.com/learning/excel-2016-tipps-tricks-techniken?u=104
Freie Videos:
https://www.video2brain.com/de/tutorial/tastenkombinationen-eine-auswahl
https://www.video2brain.com/de/tutorial/autokorrektur-und-namen
https://www.video2brain.com/de/tutorial/zeilenumbrueche-entfernen
Das ist ärgerlich! Wenn ich ein Tabellenblatt schütze, habe ich die Möglichkeit festzulegen, dass der Anwender filtern darf, das heißt den Autofilter verwenden darf:
Leider kann er auf dem geschützten Blatt nicht mit Datenschnitten arbeiten!
Die Antwort: Doch! Sie müssen die beiden Optionen „AutoFilter verwenden“ UND „Objekte bearbeiten“ aktivieren. Dann kann der Anwender auch mit den Datenschnitten arbeiten:
Oder Sie legen die Datenschnitte auf ein zweites, nicht geschütztes Tabellenblatt. Dann klappt es auch:
In der letzten Excelschulung fragte mich ein Teilnehmer, warum ein Legendeneintrag in einem Diagramm fehle:
Ein verschieben, Aktualisieren oder Vergrößern der Legende brachte die fehlende Jahreszahl nicht zurück.
Wir konnten reproduzieren, was wahrscheinlich passiert ist: Jemand hat einen Legendeneintrag markiert und gelöscht:
Das Originaldiagramm
Und: man erhält den fehlenden Eintrag schnell wieder, indem die komplette Legende gelöscht wird und wieder eingefügt wird:
Legende weg!
Legende ist nun vollständig wieder da!
Excel-Schulung. Thema Pivot. Frage einer Teilnehmerin: „Warum sieht Ihre Pivottabelle anders aus als unsere? Wir verwenden doch beide Excel 2016 und die gleichen Daten.“
Die Antwort war schnell gefunden: Ich hatte auf meinem Laptop noch eine alte *.xls-Version der Beispieldatei erwischt – während ich den Teilnehmern die Datei als *.xlsx zur Verfügung gestellt hatte.
Ich gestehe: ich weiß auch nicht alles.
Noch schlimmer: manchmal bin ich fest davon überzeugt, dass etwas nicht geht. Und dann geht es doch.
Heute hat mich folgende Mail erreicht:
„Hallo Herr Dr. René Martin,
im Video ‘2599_02_05-datenüberpruefung_nutzen.mp4‘ sagten Sie:
Achtung, wenn Sie mit Namen arbeiten, müssen Sie genau wissen, wie die Namen geschrieben werden, weil innerhalb der Datenüberprüfung haben Sie keine Möglichkeit festzustellen, wie heißt der Name nochmal – es gibt hier keine Auswahlliste, an der sie erkennen können, wie der Name geschrieben wurde.
Das ist so nicht korrekt, denn wenn der Cursor im Feld ‘Quelle‘ steht, bringt die F3-Tasste alle definierten Workbooks-Namen zum Vorschein und man kann auswählen.
Gruß von Luschi
Aus klein-Paris“
Danke an Luschi. Und ich habe wieder etwas gelernt.
Heute in der Excel-Schulung haben wir uns schon ein bisschen gewundert. Hand aufs Herz – hätten Sie das gewusst?
Wir erstellen eine Pivottabelle:
Mit [F2] kann man eine Zelle editieren und den Text ändern. Ich ändere den Text „GROSS“ in „klein“. Konsequenterweise wird nun „klein“ in „GROSS“ umbenannt:
Wenn ich nun aber „klein“ in „mittel“, „GROSS“ in „klein“ und „mittel“ in „GROSS“ umbenenne – so habe ich die Texte vertauscht. verblüffend!
Geht doch!
Hallo Herr Martin,
zufällig bin ich letzte Woche auf Ihre Internetseite excel-nervt.de gestoßen und bin total begeistert.
Es bringt Spaß zu sehen mit wie viel Humor sie es schaffen die Inhalte interessant an die Leser zu bringen.
Mit freundlichen Grüßen und machen Sie weiter so!
i. A. Gitta S.
Amüsant. Einer der Verkäufer – Herr Sonntag – wird in einer Pivottabelle nach oben sortiert:
Die Vermutung liegt nahe, dass Excel zuerst Monatsnamen und Wochentage sortiert und dann den „Rest“ alphabetisch. Schön und gut – aber wie bekommt man den Sonntag einsortiert?
Die Antwort findet sich in den „weiteren Sortieroptionen“: Dort muss „Sortiert nach“ – der richtigen Spalte eingeschaltet sein. Und anschließend schaltet man über den Befehl „weitere Optionen“ „bei jeder Berichtsaktualisierung automatisch sortieren“ aus.
Ein weiteres Dankeschön an Pia Bork, die nicht nur das Problem kannte, sondern auch seine Lösung.
Erstaunlich.
Eine Pivottabelle gruppiert alle Daten und summiert bei manchen den Wert 0. Diese Zeilen möchte man nun löschen (heißt: filtern). Wenn man den Filter in der Feldliste auswählt passiert – NICHTS!
so nicht!
Man muss den Filter der Pivottabelle bemühen, also den Wertefilter der Zeilenbeschriftungen. dann klappt es.
so schon!
Ein dankeschön an Pia Bork für diesen Hinweis.
Heute auf dem Exel-Stammtisch. Wir haben über die Begriffe diskutiert: % des Vorgängerzeilen-Gesamtergebnisses, % Differenz von, …
Hier hätte Microsoft etwas bessere Begriffe verwenden können. Vielleicht in der nächsten Version.
Aber ein Dankeschön an Pia und Stefan, die uns diese Begriffe an Beispielen erklärt haben.
Diese Woche in der Visio-Schulung.
Wir importieren als Datenquelle ein Tabellenblatt von Excel an eine Zeichnung.
Leider kann man dort weder filtern noch suchen. Das macht das Auffinden bei mehreren Tausend Datensätzen mühsam. Selbst bei 200 Zeilen nützt das Sortieren nicht viel, weil man nun mühevoll mit dem Mausrädchen nach unten scrollen muss. Sollte in Visio implementiert werden.
„Warum darf ich in der einen Datei keine Blätter löschen?“ fragte heute eine Teilnehmerin in der Excel-Schulung.
Die Antwort war schnell gefunden: „In der Titelzeile steht, dass die Datei freigegeben wurde. Da mehrere Personen zur gleichen zeit darauf zugreifen können, wurde diese Funktion deaktiviert.“
Manchmal haben meine Teilnehmer recht.
Mein Formatieren der Zeichen der Diagrammsbeschriftungen fiel einem Teilnehmer auf, dass dieser Dialog sehr unterschiedlich zum Dialog Zeichenformatieren einer Zelle aussieht:
Diagramme
Zellen
Zum Vergleich: der Dialog zum Formatieren von Zeichen in PowerPoint:
Heute in der Excel-Schulung beim Erstellen eines Diagramms wunderte sich der Teilnehmer. Obwohl er „vertikale Ausrichtung: oben“ aktiviert hatte, wanderten die Datenbeschriftungen nicht über die Markierungspunkte.
Die Ursache: Er hatte aus der Kategorie „Größe und Eigenschaften“ die Textausrichtung gewählt und nicht aus den Beschriftungsoptionen die Beschriftungsposition:
Samstag Abend auf einer Party. Natürlich kommt das Gespräch auch auf Excel.
„Warum hat Excel eigentlich keine Bullets, keine Aufzählungszeichen“, will ein Bekannter wissen. Stimmt – Excel hat keine Aufzählungszeichen wie Word, PowerPoint oder Visio. Braucht man das wirklich, will ich wissen.
Ich überlege: Man könnte in einer Hilfsspalte ein Sonderzeichen einfügen:
Nicht elegant, aber effektiv.
Natürlich kann man die Zeichen auch mit einem „normalen“ Zeichen der gleichen Schrift verketten. Aber: braucht man das wirklich?
Er erklärt mir: „Wenn ich vor eine Liste einen Strich mache, dann will Excel das nicht.“ Aha! – Dann erkläre ich ihm, dass er ein Apostroph davor setzen muss. Dann klappt es.
Manchmal denke ich zu kompliziert.
Heute musste ich schmunzeln.
Excelschulung: Einführung in Excel. Ich zeige, wie man eine Zeile löscht.
Ein Teilnehmer meldet sich und sagt, dass bei ihm am Arbeitsplatz die Zeilennummern Lücken aufweisen. Dass es Kollegen hinbekommen haben, die fortlaufende Nummerierung zu durchbrechen.
Ich schaue ihn erstaunt an und erwidere, dass das nicht geht. Beim Löschen einer Zeile werden nachfolgende Zeilen „nach oben geschoben“. Die Nummerierung bleibt.
Ich frage ihn, ob sie vielleicht Zeilen ausgeblendet haben. Er verneint.
Am Nachmittag üben wir wie man filtert. Ich zeige den Autofilter.
Der Teilnehmer strahlt und freut sich: „ich glaube, ich weiß jetzt, warum Zeilennummern fehlen. Die haben einen Filter eingeschaltet.“
Heute Excel mit Office 365 unterrichtet. Ich wollte die benutzerdefinierten Listen zeigen.
Wollte zeigen, dass man dort mit einem Umbruch einen neuen Eintrag eingeben kann:
Ging aber nicht!
Also gut; – dann eben per Komma trennen – geht doch auch!
Hallo Herr Martin,
ich möchte in einer Tabelle einen Bereich (hier: die Haltestellen) mehrfach einfügen. Ich finde aber leider diesen Befehl nicht:
Die Antwort: Den gibt es auch nicht. Allerdings können Sie die Zellen markieren und am Kästchen runterziehen. Damit erreichen Sie auch eine Vervielfältigung.
Heute: eine Schulung „umsteigen von Office 2007 auf Office 2016“. In 3,5 Stunden. Viel zu wenig Zeit für vier Programme (natürlich kamen auch noch Fragen zu OneNote). Also die wichtigsten Dinge ansprechen.
Beispielsweise die Datenschnitte in Excel.
Eine Teilnehmerin meldete sich und fragte, warum bei ihr die Datenschnitte nicht funktionieren:
Merkwürdiger Datenschnitt
Die Antwort war schnell gefunden: Die Tabelle wurde nicht als „intelligente Tabelle“ formatiert. Das kann man schnell an der fehlenden Registerkarte „Tabellentools“ erkennen.
Wahrscheinlich hatte sie die Option Einfügen / Tabelle betätigt und dann die Tabelle wieder über Tabellentools / Entwurf / In Bereich konvertieren zurück verwandelt. Sieht man nicht auf den ersten Blick, weil die Formatierungen bleiben.
Wir lernen heute: Datenschnitte können nur auf Pivottabellen oder intelligenten Tabellen aufgesetzt werden.
Hi René,
ich kriege grad seit 2 h einen Vogel mit Pivot:
Habe unterschiedliche Mitarbeiter, die unterschiedliche Bonisätze bekommen sollen.
Die Bonitabelle liegt in anderem Tabellenblatt.
Damit ich in Pivot für jeden MA den richtigen Bonusbetrag anzeigen kann, habe ich mir mit SVerweis die Sätze in meine Ausgangstabelle geholt.
Und ein Feld berechnet. Soweit alles schön…
…nur, der Bonusbetrag ist um den Faktor 10 zu hoch!
Wer macht da was falsch : ich oder Excel?
Hiielfe! Kannst Du helfen?
Viele Grüße – Angelika
#####
Hallo Angelika,
der Knackpunkt in der Pivottabelle liegt im berechneten Feld. Das kann man leicht zeigen. Bau mal folgende Tabelle auf:
Setze eine Pivottabelle auf, gruppiere die Namen, summiere die Beträge:
Wenn du nun ein berechnetes Feld einfügst – Betrag * Provision – stellst du fest, dass die berechnete Provision doppelt so hoch ist, wie sie sein sollte:
Der Grund: die beiden Provisionssätze für Rene werden summiert (5% + 5% = 10%) und diese Zahl mit der Summe der Beträge multipliziert. Wenn du die Summe mit 5% multiplizieren möchtest, musst du den Provisionssatz (über einen SVERWEIS) reinholen und damit multiplizieren. Dann klappt es:
Der Problem mit dem Datum hat mich gestern noch beschäftigt.
In der Zelle D3 steht nichts. Erstaunlicherweise liefert =JAHR(D3) keinen Fehler (wie ich vermutet hätte), sondern 1900. Warum?
Die Antwort: Schreiben Sie in eine Zelle das Datum 05.01.1900. Subtrahieren Sie von diesem Datum die Zahl 1. Setzen die Formel fort. Nach dem 01. Januar 1900 folgt der 00. Januar 1900. Dann ein Fehler:
Und das ist der Grund, warum man von einer leeren Zelle die Jahresinformation auslesen kann. Auch JAHR(0) liefert das Jahr 1900. Steht in der Zelle allerdings ein leerer Text („“) oder #NV, dann ist ein Fehler die Folge:
Das heißt im Umkehrschluss: WENNFEHLER(JAHR(D3);““) fängt keinen Fehler ab, wenn die Zelle D3 nicht gefüllt ist. Lediglich wenn in der Zelle kein Datum, also Text steht. Mit WENNFEHLER kann man diese Information also nicht abfangen.
Ich habe mich heute sehr amüsiert. Ich war in einer großen Firma, die Sie auch kennen. Dort haben mir Mitarbeiter eine große Exceltabelle gezeigt, mit der Bitte, ihnen die Formeln zu erklären und möglicherweise zu verbessern. Das Grundproblem tauchte an sehr vielen Stellen auf: In zwei unterschiedlichen Spalten stehen Datumsangaben. Allerdings: nicht in jeder Zelle.
Es sollen die Datumsdifferenzen berechnet werden. Allerdings nicht Ende – Anfang, da die leeren Zellen ein Ergebnis verfälschen würden. Nun hat ein Kollege – wahrscheinlich über Jahre – verschiedene Formeln eingetragen:
Ist okay – hier habe ich nichts zu nörgeln.
Das Leerzeichen stört mich; würde ich nicht machen – besser: „“.
Warum einfach, wenn es auch umständlich geht. D2-C2 entspricht DATEDIF(C2;D2;“d“).
Ganz schlecht: D2-C2 liefert keinen Fehler, wenn eine der beiden Zellen leer ist. Das Ergebnis ist beispielsweise -42780.
Bis Excel 2003 gab es noch nicht die Funktion WENNFEHLER – bis dahin musste man WENN(ISTFEHLER(… schreiben. Falsch und überflüssig!
Gut: beide Zellen werden überprüft!
Die Funktion ISTZAHL habe ich in der Tabelle nicht gefunden.
Wir haben uns amüsiert, weil das gleiche Problem von einem Anwender auf verschiedene Arten gelöst wurde. Das ist nicht konsistent und auch nicht nachzuvollziehen. Aber man kann ja mal vermuten, was die Ursache des Formelwandels war …
Und immer wieder die Frage, was man tun kann, wenn ein Word-Serienbrief Zahlenformate – konkret Währung und Datumsangaben – nicht mitnimmt.
Die Liste – mit Datum und Währung
Der Serienbrief
Die Vorschau – SO NICHT!
Die erste Lösung: bearbeiten Sie in Word die Formularfelder ([Alt] + [F9]). Fügen Sie einen Schalter ein: \@ für Datum und \# für Zahlen. In Anführungszeichen wird das Format eingegeben:
Man kann den Serienbrief nachformatieren
Die zweite Lösung: Schalten Sie in den Optionen in Word ein:
Dateiformatkonvertierung beim Öffnen bestätigen.
Die Option in Word
Wenn Sie nun einen Serienbrief erstellen und eine Datenquelle hinzufügen, werden Sie nach der Art des Einfügens gefragt. Nachdem Sie die die Option „Alle“ aktiviert haben, wählen Sie aus der Liste „DDE“. Dann werden die Formate übernommen.
DDE!
Die dritte Lösung: In Excel können Sie eine Zahl mit der Funktion TEXT in einen (formatierten) Text verwandeln. Beim zweiten Parameter schalten Sie die Formatanweisung ein, beispielsweise 0,00 oder TT.MM.JJJJ
So geht es auch
Jede Variante hat ihre Vor- und Nachteile. Ich bevorzuge DDE.
Ich bin verwirrt: Seit einer Weile startet mein Excel „leer“. Also ohne Datei. Ich muss jetzt jedes Mal eine neue Datei öffnen. Warum denn das? Und vor allem: Wie schalte ich es wieder ab?
Nach dem Start von Excel
Die Antwort finden Sie in der Registerkarte „Ansicht“. Wenn Sie dort auf das Symbol „Einblenden“ klicken, werden sämtliche Dateien aufgelistet, die mit Excel geladen wurden, aber ausgeblendet sind. Dazu gehört die PERSONAL.XLSB, aber auch eine (oder mehrere) Sicherheitskopie(n) davon.
Ausgeblendete Dateien
Blenden Sie die Datei PERSONAL.XLSB ein, klicken Sie auf Speichern unter, um den Speicherpfad dieser Datei zu ermitteln. Beenden Sie Excel, wechseln im Windows-Explorer in den entsprechenden Pfad und löschen diese „Sicherheitskopien“. Dann wird Excel wieder korrekt gestartet.
Raus damit!
Ich öffne eine PivotTabelle. Erstaunlich: Neben den Monatsnamen befinden sich auch die Zahlen der Monate als Vorschlag. Aber beim Einblenden wird nichts angezeigt:
Auch in der Ursprungsliste befinden sich diese Werte nicht. Ein Blick auf die Datenherkunft zeigt, dass in der Liste eine Formel steht:
=WENN(ISTZAHL(A2);TEXT(DATUM(JAHR(A2);MONAT(A2);1);“MMM“);““)
Wahrscheinlich wurde eine alte Formel, wie beispielsweise:
=WENN(ISTZAHL(A2);MONAT(A2);““)
durch diese Formel ersetzt.
Ein Aktualisieren der Pivottabelle nützt nichts, um die alten, nicht mehr vorhandenen Werte, zu löschen. Auch ein Entfernen der Spalte „Monat“ nützt nichts. Man muss beides machen: das Feld entfernen, aktualisieren und erneut einfügen. Dann sind die „überflüssigen“ Werte weg:
Gestern in der Excel-Schulung haben wir eine Alterspyramide erstellt. Natürlich muss man den einen Zwei negativ, den anderen positiv darstellen.
Es kam die Frage, wie man die Achse manipulieren könne, damit der negative Zwei auch mit positiven Zahlen beschriftet ist. Die Antwort: Man muss das Zahlenformat ändern in 0;0. Die erste 0 steht für positive Zahlen, die zweite für negative. Sie werden nun ohne Minuszeichen dargestellt.
Manchmal sind es nicht die Fehlermeldungen, die mich amüsieren:
sondern die Kommentare dazu:
„Hallo Rene,
ich verstehe etwas nicht … wenn ich diesen Code: […]
dann bekomme eine Fehlermeldung. Es fehlt ein with block und so.
Danke für deine Hilfe.“
Natürlich konnte ich helfen: „an Objektvariablen musst du etwas mit dem Schlüsselwort SET übergeben, also:
Set xlRange = ActiveSheet.UsedRange
Ich habe nun die Beiträge dieser Seite des Jahres 2015 zusammengefasst und überarbeitet. Man kann die nun als Buch oder e-book kaufen:
Produktinformation
Taschenbuch: 336 Seiten
Verlag: Books on Demand; Auflage: 1 (8. Februar 2017)
Sprache: Deutsch
ISBN-10: 3743182548
ISBN-13: 978-3743182547
Excel kann in Diagrammen nicht die Beschriftung der y-Achse nummerieren. Also machen wir das per Hand: Ich füge eine Form ein, beschrifte sie mit den Zahlen von eins bis zehn.
Da die Abstände nicht stimmen, füge ich eine Absatzmarke ein. Um sie auf den korrekten Zeilenabstand zu bekommen, verkleinere ich die Schriftgröße auf 1 pt. Nun kann ich mehrere Absatzmarken einfügen und erhalte so etwas den korrekten Abstand.
Zugegeben: gut ist es nicht. Elegant auch nicht. Aber eine einfache Lösung.
Was ist denn das? Und überhaupt: Warum ist mir das noch nicht früher aufgefallen?
Ich erstelle eine Liste; schalte dort den Autofilter ein und filtere. Unter der Liste trage ich einen Monatsnamen oder Wochentag ein und ziehe ihn herunter. Excel weigert sich „weiterzuzählen“:
Der Autofilter bleibt weiterhin eingeschaltet; es sind jetzt aber keine Daten gefiltert. Nun darf ich weiterzählen:
Unabhängig von der Filterung – nach rechts darf ich ziehen und Reihe ausfüllen:
Übrigens: bei einer gefilterten intelligenten Tabelle tritt dieser Effekt nicht auf:
Sehr seltsam. *grübel*
Excel treibt uns zum Wahnsinn? – Nö – WIR treiben jetzt mal Excel zum Wahnsinn! Und so geht es:
– neue Datei anlegen
– Formatvorlage „Standard“ mit irgendeiner Füllfarbe versehen – jetzt wird das ganze Tabellenblatt eingefärbt
– Tabellenblatt markieren und „Füllfarbe: keine Füllung“ zuweisen – alles wieder weiß
– Dann eine bedingte Formatierung mit Füllfarbe verwenden
– Bedingtes Format sieht man am Bildschirm
– Seitenansicht und im Ausdruck: keine Farbe
######
Wie kommen wir auf so etwas? Und: wer ist „wir“? Wir hatten gestern in München wieder unseren Excel-Stammtisch, bei dem ich einen Vortrag über Fehler, Ungenauigkeiten, Unschärfen und Probleme in Excel referiert habe (Motto: „Excel nervt“). Eine Datei hat uns alle beschäftigt: warum sehe ich die Farbe der bedingten Formatierung in Excel – im Ausdruck dagegen ist sie weg. Ich habe schon im XML-Code gewühlt und bemerkt, dass das Problem in der styles.xml lag. Genauer konnte ich es nicht einkreisen.
Hilfe kam von Pia Bork. Hier noch einmal ein dickes DANKESCHÖN an sie für das Aufspüren des Fehlers. Von ihr stammt die obige Beschreibung.
Und warum nerven wir nun Excel? Nun – probiert mal das Kochrezept von oben aus. Klickt nun auf eine andere Zelle, auf ein anderes Blatt, in ein anderes Programm und wieder zurück … Excel rechnet sich grün und blau! köstlich! Das ist die Strafe fürs mich-so-oft nerven!
PS: Pias Kommentar: „Herzlichen Glückwunsch an alle, die so einen Fehler suchen müssen!“
Die bedingte Formatierung in Excel
Die bedingte Formatierung in der Seitenansicht
Heute in der Excelschulung. Wir erstellen ein 3D-Diagramm mit Hilfe der 3D-Oberfläche.
Die Teilnehmer entdecken die Drehung und beginnen die X-Drehung, Y-Drehung und Perspektive zu verändern.
Ein Teilnehmer fragt mich: Warum passiert bei der Schaltfläche „Zurücksetzen“ gar nichts?
Ja – warum eigentlich nicht? Die Schaltfläche „Standarddrehung“ setzt lediglich X-Drehung und Y-Drehung zurück. Schade – eigentlich!
Amüsant:
In einer Spalte werden die Preis in EUR angegeben. Da wir bis 2002 noch DM in Deutschland hatten werden in einer Spalte daneben die Euro-Preise eingetragen. Die EURO-Preise aus den 80er und 90er Jahren wurden umgerechnet. Darauf wird ein Liniendiagramm aufgesetzt.
Das Liniendiagramm
Verändert man den Datentyp in „gestapelte Linien“ werden die Daten kumuliert. Die leeren Zellen werden als 0 interpretiert:
Gestapelte Linien
Wechselt man wieder zurück auf Linie, werden jetzt die leeren Zellen als Nullwerte verarbeitet.
Anders als am Anfang
Besteht eine Liste aus einer Datenreihe, wird die Überschrift ins Diagramm übernommen:
Besteht eine Liste jedoch aus mehreren Spalten, werden Überschriften nicht im Titel angezeigt (nur in der Legende):
Die Kamera ist nicht unbedingt das eleganteste Werkzeug.
Abhilfe schafft ein Verweis auf die Zelle, in der sich der Text befindet. Das funktioniert in der Titelzeile
oder auch eine Form, die in das Diagramm eingefügt wurde.
Achtung: Die Eingabe muss über die Bearbeitungsleiste funktionieren! Und: man muss den Tabellenblattnamen angeben!
Excel bietet in Diagrammen die Möglichkeit, die Beschriftung in Formen anzeigen zu lassen. Dumm nur, dass sie nicht in der Legende auftauchen.
Letzte Woche in der Excel-Schulung. Ich erkläre, dass es manchmal besser ist, den Funktionsassistenten zu verwenden, weil dort einige Informationen angeboten werden, manchmal ist die Eingabe über die Tastatur die bessere Wahl, weil Parameter aufgelistet und erklärt werden.
Ein Teilnehmer meldet sich und fragt traurig, warum er keine Formelvervollständigung hat:
och!
Die Lösung war schnell gefunden: In den Option war in der Kategorie „Formeln“ die Option „AutoVervollständigen für Formeln“ deaktiviert.
… und der Teilnehmer war wieder glücklich …
Ich erstelle eine 3 x 3-Matrix.
Berechne die inverse Matrix mit der Funktion MINV:
Multipliziere die beiden Matrizen mit der Funktion MMULT – das Ergebnis – na, ja: fast richtig. Ein bisschen Abweichung ist halt häufig in Excel:
Wenn ich die berechneten Zahle der inversen Matrix per Hand eingebe, erhalte ich eine korrekte Einheitsmatrix. Die Rechenungenauigkeit liegt also bei MINV.
Heute in der Excelschulung kam mal wieder die Frage, ob man einzelne Datenpunkte in einem Diagramm verschieben kann. Meine Antwort ist „nein“. Zu Hause überlegen ich – vielleicht geht es ja doch – vielleicht gibt es irgendwo einen Schalter. Früher ging es doch auch.
Leider nicht verschiebbar.
Ich schaue mich im Internet um: auf der Seite
finde ich folgenden Kommentar:
„Excel 2007: Datenpunkt im Diagramm verschieben
Problem: Ich habe hier ein einfaches Punkte-Diagramm. Darin möchte ich einen der Datenpunkte per Maus verschieben, so dass in der Bezugstabelle der entsprechende Wert angepasst wird. In Excel 2003 ging das noch, dachte ich. Wie siehts unter Excel 2007 aus?
Lösung: Bei der gesuchten Funktion handelt es sich um eine Art «grafische Zielwertsuche». Dieses Feature ist bzw. war tatsächlich in Excel 2003 noch vorhanden. Aufgrund der seltenen Nutzung hat’s das Feature aber nicht mehr in die 2007-er und 2010-er Version von Excel geschafft.“
Ich habe diesen und weiter zehn Punkte zu meiner Liste
hinzugefügt. Den Teilnehmern ist einiges aufgefallen.
Auf nichts ist mehr Verlass:
Ich trage einige Zahlen in Excel ein. Schalte den Autofilter ein, filtere die Daten. Unter der Liste ein Klick auf das Summen-Symbol – die Funktion TEILERGEBNIS mit dem Parameter 9 wird verwendet. Nur die gefilterten Daten werden summiert.
Ich markiere eine Zeile und blende sie aus:
Ich bin irritiert: Die ausgeblendete Zeile wird nicht summiert.
Irritiert deshalb, weil die Hilfe erläutert, dass der Parameter 109 die ausgeblendeten und gefilterten Daten nicht summiert, der Parameter 9 jedoch nur die gefilterten.
Ich schalte den Filter aus, blende die Zeile aus – sie wird JETZT nicht mitsummiert.
Das heißt: der Parameter 9 summiert Werte von ausgeblendeten Zeilen, wenn kein Filter gesetzt ist, summiert sie jedoch nicht, bei einem eingeschalteten Filter.
Da ist doch was faul!
Danke an Maximilian für den Hinweis.
Ich trage in einer Zelle die Funktion =exp(1) ein, da ich die Konstante e benötige. Ich benenne die Zelle e:
Ich schreibe nun die Formel
=Sin(e) in eine andere Zelle – die Konstante, das heißt: der Name, wird erkannt:
Nach Schließen der Klammer wird die Formel bestätigt:
Das Ergebnis verblüfft; aber des Rätsels Lösung ist schnell gefunden:
AutoKorrektur heißt der Bösewicht!
Hallo Rene,
wir haben hier gerade Excel Stammtisch in Basel und merken: Excel nervt! J So haben wir an Dich gedacht 😉
Wir hätten gerne das Wort „Versuch“ ganz links. Denn leider schiebt Excel das Wort weiter nach rechts je größer die Schriftart.
Ich denke, da gibt es keine Lösung, aber vielleicht hast Du schon eine Lösung gefunden?
Sorry, Johannes, das hängt mit der Spationierung zusammen. Und: Excel ist überhaupt kein Programm für „vernünftige“ Textverarbeitung. Eine Teilnehmerin wollte man einen exakten Zeilenabstand haben 😉
Eine Kollegin hat mir letzte Woche eine Excelmappe mit einem Makro geschickt. Das Makro kopiert von mehreren Tabellenblättern Bereiche und fügt sie in eine bestehende Präsentation ein.
Die Kollegin hatte ein paar Fragen zum Makro. Da ich die PowerPoint-Präsentation nicht hatte, änderte ich einige wenige Befehle. Beispielsweise „greife auf Folie 1, 2, 3 zu“ wurde geändert in: „füge eine neue Folie ein“. Der Befehl AddSlides war schnell gefunden:
Allerdings erhielt ich hier einen Fehler. Nochmal nachschauen:
Doch: Sildes.AddSlide. Korrekt!
Ebenso die Parameter: an welcher Stelle soll eine neue Folie erzeugt werden? Welches Layout soll verwendet werden? Ebenfalls korrekt!
Mir dämmerte es. Vor gefühlten 100.000 Jahre habe ich mal PowerPoint programmiert. Und dort herausgefunden, dass die Methode nicht AddSlides heißt, sondern Add. Geändert. Tatsächlich: IntelliSense listet nun sogar die korrekten Layout-Konstanten auf.
Hum – Microsoft hätte wahrlich in den letzten 15 Jahren einen so wichtigen Befehl wie „füge neue Folie ein“ korrigieren können. Oder bin ich der einzige, dem dies aufgefallen ist?
Amüsiert war ich schon. In einer Firma sollte ich letzte Woche Excel-Formeln anpassen. Man erklärte mir, dass jeder Auditvorgang ein Revisionsdatum hat. Möglicherweise auch ein zweites und ein drittes. Wenn es ein drittes Datum gibt, gibt es auch ein zweites. Also: die Tabelle ist gefüllt: entweder 1. Datum oder 1. und 2. oder 1. und 2. und 3. oder alle vier Spalten sind mit einer Datumsinformation gefüllt.
Ein Kollege hatte eine Formel erstellt:
=WENN(H2=““;1;WENN(I2=““;2;WENN(J2=““;3;4)))
Meine Aufgabe war es diese Formel für weitere Datumsangaben anzupassen. Ich war etwas irritiert.
=ANZAHL(G2:J2)
hätte das Gleiche getan. Und ist leichter anzupassen. Die Teilnehmer waren begeistert und glücklich.
Eine Excelmappe geöffnet. Einen Hyperlink angeklickt. Eine lustige Fehlermeldung erhalten:
Die Richtlinien Ihrer Organisation verhindern, dass diese Aktion abgeschlossen werden kann. Wenden Sie sich an Ihr Helpdesk, um weitere Informationen zu erhalten.
Welche Organisation, frage ich mich. Überhaupt: Welche Richtlinien? Und: welche Aktion? Ich habe doch nur auf einen Hyperlink geklickt. Ach ja, Microsoft: einen Helpdesk, den ich im Akkusativ fragen würde, habe ich auch nicht. Was tun?
Der Blick fällt nach oben. Ach so – die Bearbeitung wurde noch nicht aktiviert:
Guten Tag Herr Martin,
vielen Dank für Ihre informative Seite, welche mir nun bereits schon einige Male helfen konnte. Dieses Mal wurde ich leider trotz intensiver Suche im gesamten Internet nicht fündigt. Es geht um folgendes Problem:
Eine Kollegin hat eine Excelmappe in der mehrere Zellen über Bedingte Formatierungen eingefärbt werden. Die werden jedoch beim drucken nicht übernommen. Schon der Druckvorschau (und auch beim drucken selber die Farben nciht verwendet, sondern alle Zellen mit weißen Hintergrund dargestellt bzw. gedruckt.
die bedingte Formatierung
aber leider nicht sichtbar …
Hallo Herr O.,
Mir ist folgendes aufgefallen:
Wenn ich von Ihrer Datei ein Tabellenblatt in eine neue Datei verschiebe (egal welches Blatt), habe ich dort den gleichen Effekt. Oder umgekehrt: Bedingte Formatierung „reagiert nicht“.
Ich habe die Datei im xlsx-Format gespeichert, entzippt und „reingeschaut“ und verglichen mit einer „normalen“ Excel-Datei.
Ich weiß nicht genau an welcher Stelle, aber bei der styles.xml (im Ordner xl) finde ich Unterschiede zur „normalen“ Datei.
Ich habe nun die gesamte Datei durch „meine“ styles.xml ersetzt, geöffnet – damit sind leider sämtliche Formatierungen weg, aber nun funktioniert die bedingte Formatierung wieder.
Das heißt: durch irgendein Speichern/Austausch/Öffnen, … wurde etwas in den Formaten „zerschossen“. Und zwar in den Formaten der Datei (nicht des Tabellenblattes). Ich weiß nicht wann, bei welcher Aktion und nicht genau was. Aber wenn Sie die Datei neu formatieren, können Sie mit Ihr arbeiten.
Wenn Sie einen Blick in meine Datei werfen, erhalten Sie eine Meldung, dass etwas nicht stimmt. Klar: ich habe ja die styles.xml ausgetauscht.
schöne Grüße
Rene Martin
PS: Wenn ich ganz viel Zeit habe, schaue ich mir Ihre Styles-Datei noch einmal genauer an – vielleicht kann ich die Stelle lokalisieren, wo der Fehler auftritt.
Nö, besonnen kann ich nicht bleiben. Plötzlich darf ich keine Symbole mehr in mein Ribbon einfügen. Was ist da los?
Rien ne va plus
Die Antwort finden Sie, wenn Sie einen Blick ins Ribbon werfen:
Die Excel-Registerkarten wurden ausgeblendet. Wahrscheinlich im XML-Code. In einer „normalen“ Excel-Datei dürfen Sie natürlich wieder Symbole einfügen. So viele wie Sie möchten.
Was ist denn nun schon wieder los? Ich möchte eigentlich nur mit dem Befehl Copy ein Blatt in eine andere Datei kopieren:
Die Antwort finden Sie, wenn Sie genau hinschauen. Das zu kopierende Blatt („YTD“) ist xlSheetVeryHidden. Sie müssen die Eigenschaft Visible entweder auf xlSheetVisible oder auf xlSheetHidden stellen. Danach können Sie ihn ja wieder „sehr gut verstecken“.
Die Ursache
Ich versuche per VBA benutzerdefinierte Eigenschaften an eine Datei zu binden. Nichts leichter als das, denke ich:
Man definiert eine Variable vom Typ CustomDocumentProperties (oder Property) und fügt zu der Sammlung ein weiteres Element mit der Methode Add hinzu. Sie möchte Name und Value. Klingt vernünftig. Ich werde jedoch eines Besseren belehrt:
Typen unverträglich? Okay – dann ohne Objektverweis:
Falsch Anzahl an Argumenten? Aber IntelliSense hat mir doch … Ein Blick in die Hilfe verrät, dass ich die CustomDocumentProperties vom Typ DocumentProperties deklarieren muss. Aha:
Und richtig: Dort wird noch zwingend der Parameter „LinkToContent“ verlangt.
Nächster Test:
??? Etwas probieren und schon habe ich die Lösung: Obwohl die Eigenschaft „Type“ in eckigen Klammern, also optional, angegeben wurde, ist dieser Wert zwingend erforderlich.
Kaum probiert man eine halbe Stunde – schon klappt es auch. Flexibilität braucht man schon – nicht nur im Niveau:
Ich glaube, da muss Microsoft noch mal ran.
Eine Exceltabelle wird in Word eingebunden und als pdf gespeichert. Das Ergebnis ist alles andere als schön:
Speichern unter / pdf
Druckt man Sie dagegen mit einem guten pdf-Drucker (beispielsweise von Adobe), dann sieht das Ergebnis erheblich besser aus:
Drucken / Adobe Acrobat
It Takes Almost 10 Hours to Manually Reach the Bottom of an Excel Sheet
Ich würde ja Strg + 
drücken. Und dann zum Italiener gehen statt die Pizza am Telefon zu bestellen.
Amüsant. Heute in der VBA-Schulung. Wir basteln eine dynamische UserForm. Bei der Auswahl „ein Verkäufer“ kann ein Verkäufer ausgewählt werden, bei der Auswahl „alle Verkäufer“ wird das Listenfeld inaktiv.
Der Code:
Private Sub optAlleVerkäufer_Click()
Me.lstVerkäufer.BackColor = &H8000000F
Me.lstVerkäufer.Enabled = False
Me.lstVerkäufer.BorderStyle = fmBorderStyleSingle
End Sub
Private Sub optEinVerkäufer_Click()
Me.lstVerkäufer.BackColor = &H8000000E
Me.lstVerkäufer.Enabled = True
Me.lstVerkäufer.BorderStyle = fmBorderStyleNone
End Sub
So sieht es nach der Initialisierung aus:
Neun Mal wechseln zwischen alle und ein Verkäufer:
Nach 17 Mal wechseln:
Nach 22 Mal:
Amüsant ?!? Das Listenfeld wird immer kleiner. Man muss gar nicht das Listenfeld inaktiv (Enabled = False) setzen. Es genügt die Eigenschaft BorderStyle zu ändern. Ein paar Mal. Nach zehn bis 20 Klicks schrumpft das Listenfeld bedenklich:
Private Sub optAlleVerkäufer_Click()
Me.lstVerkäufer.BorderStyle = fmBorderStyleSingle
End Sub
Private Sub optEinVerkäufer_Click()
Me.lstVerkäufer.BorderStyle = fmBorderStyleNone
End Sub
Zugegeben: ein bisschen suspekt sind mit die Tabellen, die intelligente Tabellen oder formatierte Tabellen immer noch. Wenn Sie beispielsweise eine fortlaufende Nummerierung erzeugen möchten, können Sie auf eine Zahlenspalte zugreifen und dort beispielsweise die Formel eintragen:
=ANZAHL($C$1:C2)
Die Liste wird korrekt gefüllt:
Wird jedoch ein neuer Datensatz eingetragen, dann „zerschießt“ Excel die vorletzte Formel:
Abhilfe schafft bei der Eingabe der Formel
=ANZAHL($C$1:C2)
anstelle des Zellnamens C2 ein Klick auf die Zelle C2. Dann wird aus der Formel:
=ANZAHL($C$1:[@Alter])
Und damit funktioniert es.
Ein großes Dankeschön an Christian für diesen Hinweis.
Guten Morgen Herr Martin,
gerade bin ich über Ihre sehr amüsante und auch informative Seite gestolpert. Klasse, gefällt mir sehr gut.
Aber Excel (2016) nervt… an einer Stelle, die schon weh tut. Haben Sie vielleicht einen Tipp?
Problem:
In einer Excel-Tabelle habe ich Text in einer Zelle, der Klassiker…
Wenn ich nun F2 drücke kann ich den gesamten bzw. Teile des Textes ins Clipboard kopieren, das funktioniert auch wie erwartet.
Das eigentlich nervige ist die Tatsache, dass der Teil des Textes, den ich markiert habe, nicht mehr farblich vom Hintergrund der Zelle zu unterscheiden ist, früher war der blau oder sowas. Da konnte man halt genau sehen, was gerade aktuell markiert ist.
Das tritt unabhängig davon auf, ob die Zelle eine Hintergrundfarbe hat oder nicht.
Haben Sie eine Idee?
Beste Grüße
M.
######
Hallo Herr R.,
danke für das Lob. Ich gestehe, einige Dinge kann ich nicht erklären, beziehungsweise nachvollziehen. Vor Kurzem hat mir eine Freundin einen Screenshot mit Kommentaren geschickt:
Bei mir sahen sie so aus:
Dafür habe ich keine Erklärung, auch nicht für den Fehler, der an einigen Rechnern in Excel 2010 aufgetaucht ist:
beschrieben:
http://www.excel-nervt.de/seitenansicht/
######
Guten Morgen Herr Martin,
Fehler gefunden, eigene Dusseligkeit (obwohl ich MS gern einen Teil zuschiebe).
Der markierte Bereich hat genau dieselbe Farbe wie mein globaler Fensterhintergrund. Das Grau habe ich nun etwas dunkler gemacht, Excel neu gestartet und die Markierung ist sichtbar.
Pia hat mich darauf aufmerksam gemacht. Auf der Microsoft-Seite
findet sich folgende amüsante Sortierung:
Wieso findet man PIVOTDATENZUORDNEN, gefolgt von VARIATIONEN, bei dem Buchstaben G? Nicht nur dort – das ganze Alphabet ist „korrupt“. Ein Blick auf die englischen Funktionsnamen (man erhält sie mit dem Makrorekorder oder auf der Seite Mit dem Excel Translator oder einer beliebigen anderen Seite, die die Begriffe mehrsprachig auflistet. Sie heißen: GETPIVOTDATA und GROWTH.
Kommentar: Und nebenbei: was soll eigentlich diese Schwachsinnsseite? Da kommt genau der gleiche Mini-Kurz-Text wie bei der Funktion. Das sind keine „Weiteren Hinweise“, das ist überflüssig wie ein Kropf. WIE genervt ich bin!
Böses Excel! Oder besser: Böser Excel-Anwender!
Ich wundere mich: in einer Liste steht der Text „Umsatz“ als Überschrift. Zwischen dem Zellrand und dem ersten Buchstaben klafft allerdings eine kleine Lücke:
Das Editieren der Zelle hilft nicht weiter – vor dem „U“ befindet sich kein Leerzeichen:
Also gehe ich auf die Suche. Ein Format? Ein Einzug? Ein Blick in den Dialog bringt auch keine Lösung des Problems:
Plötzlich entdecke ich das Zahlenformat. Die Zelle mit dem Text ist als „Buchhaltung“ formatiert. Wahrscheinlich hat der Anwender die ganze Spalte (also mit der Überschrift) so formatiert:
Zahlenformat „Standard“ – und schon funktioniert es! Der Text sitzt am linken Rand. Ohne Lücke.
Amüsant: Ich verwende den Assistenten Daten / Text in Spalten, um die Postleitzahl vom Ort zu trennen. Die erste Zeile – Frankfurt am Main habe ich vergessen:
Die Daten wurden getrennt – nur einer nicht.
Kein Problem: Doppelklick auf die Zelle, Ortsnamen markieren und ausschneiden:
Jedoch beim Einfügen bin ich verblüfft: Excel hat sich den Assistenten gemerkt und trennt diesen Text:
Eh! – Das will ich nicht!
Nachtrag zum Beitrag
Manchmal rede ich mit mir selbst. Und dann lachen wir beide.
Ich habe ein Diagramm mit gestapelten Säulen in Excel 2007 erstellt. Ich öffne es in Excel 2016, erstelle auf Basis der Daten ein weiteres Diagramm. Tatsächlich – die Einträge der Legende sind vertauscht.
Übrigens – wenn ich in Excel 2016 im „alten“ (oberen) Diagramm die Legende entferne und neu einfüge, erhalte ich die „neue“ Reihenfolge. Ich hätte es auch nicht anders erwartet.
Geschmunzelt habe ich schon:
Gestern in der VBA-Schulung haben wir uns Eigenschaften und Methoden von Objekten in Excel VBA angesehen. Beispielsweise von ActiveWorkbook: die Eigenschaften Name, Path, FullName, die Methoden Save, Close und eben auch PrintOut. Mit dem Parameter Copies kann man eine Datei mehrmals ausdrucken lassen, erklärte ich. Ein Teilnehmer wollte wissen, was denn passiere, wenn man die Datei beispielsweise 100.000 Mal ausdrucken würde. Da mein Laptop nicht mit einem Drucker verbunden war, habe ich es getestet: Das Ergebnis:
Maximal 32.767 (215-1) Mal darf man ausdrucken
Ist ja auch logisch: mehr darf man auch nicht in Excel:
32.767 genügt doch auch, oder?
Vorgestern in der PowerPoint-Schulung:
Teilnehmerin: Wie kann ich denn die Daten des Diagramms erneut bearbeiten? Wie komme ich zu der Excel-Tabelle zurück?
Trainer: Klicken Sie auf die Registerkarte „Entwurf“. Dort finden Sie in der Gruppe „Daten“ eine Schaltfläche „Daten bearbeiten“. Damit wird die Excel-Tabelle erneut geöffnet.
Teilnehmerin: Ich sehe in Entwurf kein Symbol „Daten bearbeiten“.
Trainer: Ziemlich weit rechts. Das dritte Symbol von rechts.
Teilnehmerin: Bei mir gibt es so ein Symbol nicht.
Trainer schaut auf den Bildschirm der Teilnehmerin: Sie haben recht. Es gibt zwei Registerkarten „Entwurf“. Ich meine die kontextabhängige Registerkarte „Entwurf“ in den „Diagrammtools“. Nicht die linke Registerkarte „Entwurf“.
Zwei Mal Entwurf
Fazit: Als EDV-Trainer müssen wir immer ganz genau sagen, wovon wir sprechen. Viele Bezeichnungen tauchen in den Anwendungsprogrammen mehrmals auf (Entwurf, Einfügen, Tabelle, …) Sonst sehen und verstehen es die Teilnehmer und Teilnehmerinnen, die Anwender und Anwenderinnen nicht.
Übrigens: in der englischen Version sind auch beide Registerkarten mit „Design“ beschriftet.
Doch, manche Dinge begeistern mich:
Wird in einer intelligenten Tabelle (in einer formatierten Tabelle) eine ganze Zeile markiert und diese am Rand heruntergezogen, wird die darunterliegende Zeile überschrieben. Abhilfe schafft das Drücken der Shift-Taste:
ganze Zeile verschieben
Markiert man dagegen nur die Zellen einer Zelle bis zum Ende der Tabelle, kann man ohne weitere Taste diese Zeile herunterziehen:
Ist mir nie aufgefallen. Heute in der PowerPoint-2016-Schulung ist mir aufgefallen, dass bei den Diagrammen mit gestapelten Säulen die Legende „umgekehrt“ angezeigt. Während in PowerPoint 2007 noch Ausland | Inland angezeigt wurde:
wird in PowerPoint 2016 nun Inland | Ausland angezeigt:
Eigentlich nicht schlimm – es geht ja schließlich nur darum, welche Kategorie durch welche Farbe repräsentiert wird. Dennoch: amüsant!
Amüsante Sache heute in der VBA-Schulung:
Ein Teilnehmer fragte mich, warum sein Markorekorder nicht funktioniert. Tatsächlich: Beim Versuch ein Makro aufzuzeichnen kam eine erstaunliche Fehlermeldung:
Komisch?!
Zuerst schaute ich nach, ob noch ein Makro läuft. Nein. Alles war okay – das Stoppen brachte nicht den gewünschten Erfolg. Dann sah ich es: in einem Modul war ein fehlerhaftes Makro – wahrscheinlich hatte der Teilnehmer beim Kopieren das Sub – End Sub vergessen.
Übrigens: Trotz Fehlermeldung konnte das Makro aufgezeichnet werden …
Ich weiß – man sollte es nicht. Aber ich bin sicher, dass es Leute gibt, die so etwas tun.
In einer Pivottabelle werden die Werte (beispielsweise Summe der Umsätze) formatiert. Danach formatiert man beispielsweise die Spalte C. Wer gewinnt? Der letzte.
Das Amüsante ist, dass sich die Pivottabelle die Formateinstellungen „gemerkt“ hat. Das kann verwirrend sein.
Der letzte gewinnt.
Übrigens: Wenn Sie mehrmals „hart“ – das heißt über das Excel-Zahlenformat und über die Wertfeldeinstellungen / Zahlenformat formatieren, passieren seltsame Dinge. Aber so etwas tut man ja auch nicht 😉
Verblüfft war ich schon. Ein Teilnehmer der letzten Excel-Schulung zeigte mit eine Datei, die er aus dem USA erhalten hat. Darin befanden sich mehrere Zellen mit bedingten Formatierungen:
Ein Klick auf die „rote“ Bedingung (Zellwert<=0,81) zeigte allerdings die „grüne“ Bedingung (Zellwert = 1) an. Ich war verblüfft!
Die Lösung fand ich als ich das Dokument entzippte und mit die Datei sheet1.xml im Ordner xl\worksheets anschaute. Dort war bei mehreren Bedingungen (nicht bei allen!) das Attribut priority auf den gleichen Wert gesetzt: mehrmals auf 4, auf 5, auf 6, … Natürlich müssen dort unterschiedliche Werte stehen. Per Hand geändert, die Dateien gezippt – und schon lief die bedingte Formatierung wieder.
Die XML-Datei
Ich weiß zwar nicht, wann das Problem entstanden ist (USA – Deutschland) oder Excel 2007 – 2010, … aber immerhin – Problemursache gefunden und Problem gelöst.
Ich weiß, dass ich nicht zwei Datenüberprüfungen verketten kann oder – wie bei der bedingten Formatierung – nacheinander anwenden kann. Trotzdem – es wäre doch schön, wenn der Anwender aus einer Liste bestimmte Werte eintragen darf (Liste) – jedoch nur, wenn kein Feiertag und kein Wochenende ist:
Datenüberprüfung / Liste
Die Antwort: Wenn Sie mit der Datenüberprüfung eine benutzerdefinierte (Formel) eingeben, dann funktioniert es. Beispielsweise so:
=UND(ZÄHLENWENN(Feiertage!$B$1:$J$20;DATUM($B$2;$C$2;D$5))=0;WOCHENTAG(D$5;2)<=5;ODER(D7=“U“;D7=“M“;D7=“S“;D7=“K“;D7=“TZ“))
Das Ergebnis:
geht doch!
Zur Erklärung:
ZÄHLENWENN überprüft die Anzahl der berechneten Datumsangaben
DATUM($B$2;$C$2;D$5)
auf dem Tabellenblatt Feiertage. Die Zahl muss 0 sein, das heißt, sie ist nicht vorhanden – also ist das Datum kein Feiertag.
WOCHENTAG ermittelt, ob es sich um einen Tag von 1 – 5, also um einen Tag von Montag bis Freitag handelt.
ODER(D7=“U“;D7=“M“ …
prüft, ob wirklich nur einer der Texte eingegeben wurde.
Da alle drei Bedingungen gleichzeitig erfüllt sein müssen, werden mit der Funktion UND verkettet.
Es wäre ja schön, wenn man in der x- und y-Achse einzelne Beschriftungen löschen und formatieren könnte. Beispielsweise die benötigten Jahreszahlen anzeigen und nicht im Abstand von jeweils einem, fünf oder zehn Jahren. Geht aber leider nicht:
schade …
Ist das so gewollt?
In einer Liste gibt es Berechnungen. Beispielsweise wird mit der Funktion MONAT aus einer Spalte die Monatszahl herausgerechnet:
Liste mit Formel(n)
Wendet man nun den Spezialfilter an (Daten / Sortieren und Filtern / Erweitert), um bestimmte Informationen herauszufiltern, wandelt Excel die Formel(n) in Werte um:
Spezialfilter und sein Ergebnis
Ist Ihnen das aufgefallen? Sie haben eine intelligente Tabelle (Einfügen / Tabelle) und möchten diese transponieren. Geht nicht!
Transponieren von Tabellen nicht möglich
Es geht dann, wenn Sie die Tabelle ohne Überschrift markieren und kopieren:
Ohne Überschrift klappt das Transponieren
Es ist sicherlich nicht wichtig. Aber es hat mich amüsiert.
Ist Ihnen schon einmal aufgefallen, dass Excel, nachdem der Filter gesetzt wurde, im Kontextmenü schreibt „Zeilen löschen“ und nicht „Zellen löschen“?
Es wäre mir fast entgangen:
In der Ausgabe 7/2016 der dotnetpro erschien eine Rezension über mein Buch excel-nervt. Also über die Zusammenfassung des Blog des Jahres 2015.
Danke für die Rezension
Es könnte alles so schön sein. Aber nein – ist es nicht. Doch von vorne.
Ich habe eine Liste. In dieser Liste befinden sich in Spalte B Bundesländer, in Spalte C einige Städte. Dummerweise befinden sich Lücken dazwischen: Also: für Baden-Württemberg sind vier Zeilen reserviert, für Bayern drei und so weiter:
Ich hätte gerne die Spalte B durchgehend gefüllt, also Ba-Wü, Ba-Wü, Ba-Wü, Ba-Wü, BY, BY, BY und so weiter.
Eigentlich kein Problem. Man markiert die Spalte B und wählt den Befehl Start (Gruppe Bearbeiten) Suchen und Auswählen / Inhalte auswählen und dort die Option „Leerzellen“:
Freundlicherweise markiert Excel nur die Leerzellen innerhalb des benötigten Bereichs:
In diese wird die Formel
=B2
eingetragen. Da die Formel auf alle Zellen angewendet werden muss, wird sie mit [Strg] + [Enter] beendet:
Nun stehen in allen Zellen die Werte, die sich darüber befinden. So weit so gut. Also nur noch kopieren und Inhalte als Werte einfügen.
Erster Schritt: die markierten Zellen kopieren und beispielsweise über das Kontextmenü Inhalte einfügen / Werte:
Padautz! „Diese Aktion funktioniert nicht bei einer Mehrfachauswahl!“
Ach, ja, ich erinnere mich.
Also: Ganze Spalte markieren, erneuter Versuch – und:
Nöööö! „Sie können dies hier nicht einfügen, da der Kopieren-Bereich und der Einfügebereich nicht die gleiche Größe haben“:
Excel und ich – wir sprechen einfach nicht die gleiche Sprache. Warum tut er nicht, was ich will. Drei Anläufe – beim dritten klappt es: Markierung auflösen, erneut die gesamte Spalte B markieren, kopieren – und nun endlich darf ich die Inhalte als Werte einfügen.
Geschafft!!!
Okay – verstanden. Ich habe ein Ringdiagramm erstellt. Allerdings hätte ich nun gerne die Demokraten auf der rechten Seite und die Republikaner links. Ich finde diese Option jedoch nicht:
Wo vertausche ich links und rechts?
Die Antwort: Leider gibt es diese Option nicht bei den Kreis- und Ringdiagrammen. Sie müssen die Reihenfolge der Daten in der Tabelle ändern.
Und: ja, ich weiß: bei Säulen- und Balkendiagrammen darf man die Reihenfolge ändern …
Hier geht es.
Hallo zusammen.
Ich wollte mal eben schnell die Sitzverteilung im Repräsentantenhaus der USA darstellen. Ich habe in Excel die Zahlen der Republikaner und Demokraten eingetragen, die Summe gezogen – die wollte ich später transparent „wegformatieren“. Allerdings: Es gelingt mir nicht das Diagramm „rumzudrehen“ – also so zu drehen, dass die anderen Hälfte unten liegt. Früher ging das doch, oder?
Die Reihenoptionen lassen sich nicht öffnen
Die Antwort: Sie haben aus Versehen den Typ „Sunburst“ gewählt:
Falscher Diagrammtyp
Sie hätten Kreis / Ring wählen müssen:
Ring ist besser geeignet.
Denn damit funktioniert es:
Übrigens: bei „Sunburst“ sind einige Optionen inaktiv.
Heute in der Excel-Schulung. Ein Teilnehmer zeigt mir seine Lösung, wie er die Anzahl der Zahlen im 90er-Bereich ermittelt hat: Er berechnet die Differenz zweier ZÄHLENWENN-Funktionen:
=ZÄHLENWENN(C8:C37;“<100″)-ZÄHLENWENN(C8:C37;“<90″)
Ich zeige seinen Ansatz der Gruppe. Mit dem Funktionsassistenten rufe ich Zählenwenn auf:
Schritt 1
Normalerweise trägt Excel im Funktionsassistenten um die Bedingung <100 automatisch die Anführungszeichen ein. Jedoch ein Klick in die Bearbeitungsleiste lehrt mich eines Besseren:
Schritt 2
Ich fahre fort: tippe ein Minus und rufe erneut den Funktionsassistenten auf:
Schritt 3
Der Blick in die Bearbeitungsleiste zeigt mir, dass die Anführungszeichen in der ersten Funktion fehlen. Und richtig: Das Bestätigen der Funktion wird mit einer Fehlermeldung quittiert:
Schritt 4
Schade – denn gerade der Funktionsassistent nimmt Anwendern und Anwenderinnen, die mit Formeln noch nicht so sehr geübt sind, die Arbeit an vielen Stellen ab …
Also doch: Ich zeige dem Teilnehmer ZÄHLENWENNS, die ihm sehr gut gefällt …
Ich habe in einer Datei mehrere Tabellenblätter. Ich arbeite gerne mit dem Gruppierungsmodus: markiere mehrere Blätter und ändere nun blattübergreifend Spaltenbreite, Zellformate, Kopf- und Fußzeile, etc. Leider ist es mir nicht möglich in der Umbruchvorschau die Seitenbreite zu verändern.
Die Antwort: Sorry, DAS geht leider nicht. Ebenso wenig wie mehrere Blätter gleichzeitig schützen nicht funktioniert. Sie können allerdings Einstellungen über Seitenlayout / Seite einrichten vornehmen.
Wie hat denn das mein Kollege gemacht? In einer Tabelle befinden sich Formeln. Das Blatt ist nicht geschützt (die Namen darf ich beispielsweise ändern). Versuche ich jedoch die Formel zu ändern, erhalte ich eine Fehlermeldung:
ziemlich cleverer Schutz!
Die Antwort: Die Zellen, in denen sich Formeln befinden, wurden markiert und in der Datenüberprüfung die Formel <>““ eingegeben. So können sie nicht mehr geändert werden:
Übrigens: Das Löschen wird allerdings nicht verhindert!
Ich hätte gerne nach den Februar-Daten einen Seitenumbruch eingefügt.
Seitenumbruch
Aber irgendwie geht das nicht. Eine Idee, warum?
Nichts passiert
Die Antwort: Werfen Sie einen Blick in die Gruppe „An Format anpassen“ in der Registerkarte „Seitenlayout“. Dort wurde bei Höhe (gemeint ist die Anzahl der ausgedruckten Seiten) die Zahl 1 (1 Seite) eingegeben. Deshalb dürfen Sie keine weiteren Seiten(umbrüche) einfügen.
Erstaunlich: aus der Dropdownliste „Zahlenformat“ kann etwas ausgewählt werden oder man kann etwas eintragen:
Benutzerdefiniertes Zahlenformat
Und was passiert danach? Nach der Eingabe? – Nichts!
Wäre auch zu schön gewesen.
Sharepoint arbeitet gut mit den anderen Office-Produkten von Microsoft zusammen. Warum sollte man eine andere Kalkulationstabelle als Excel verwenden?
Oder will Microsoft zeigen, dass sie kompatibel mit anderen und offen für andere Programme sind?
Es käme auf einen Versuch an zu testen, wie gut libreOffice Calc mit Sharepoint zusammen arbeitet …
In Sharepoint-Listen kann man gruppieren und Daten zusammenfassen – beispielsweise Zwischensummen (Teilergebnisse) und Endsummen berechnen:
Schade, dass die Zwischensummen und Endergebnisse beim Export nach Excel verloren gehen …
Ist das so gewollt?
Wenn man in OneNote eine Kalkulationstabelle, das heißt eine vorhandene Exceltabelle, als Tabelle einfügt, werden sämtliche verborgenen Blätter angezeigt. Auch die Blätter, die veryHidden sind. Ist das gewollt? Ich werde am Montag mal den OneNote-Experten fragen:
Erstaunlich: In Microsoft OneNote gibt es ein Symbol in der Multifunktionsleiste „Kalkulationstabelle“. Okay – damit kann man „vorhandene Excel-Tabellen“ öffnen und „neue Excel-Tabellen“ erstellen.
Erstaunlich, dass Microsoft wahrnimmt, dass andere Kalkulationsprogramme existieren.
Ich habe es ausprobiert: Öffnet man eine libreOffice (oder openOffice.org) *ODS-Datei, wird sie in Excel geöffnet. Excel kann sie (mehr oder weniger gut) konvertieren.
Hilfe! Ein Kollege hat mir in einer meiner Excel-Mappe, in der ich mein Haushaltsbuch führe, ein Bild eingefügt. Ich bekomme es nicht mehr weg. In der Kopfzeile liegt es nicht!
Batman stört und soll verschwinden!
Die Antwort: Es würde über die Registerkarte „Seitenlayout“ eingefügt und kann dort über das Symbol „Hintergrund löschen“ (Gruppe: „Seite einrichten“) wieder entfernt werden.
Hallo zusammen,
ich schalte in Excel häufig den Textumbruch ein. Früher hier er Zeilenumbruch.
An manchen Stellen, wie beispielsweise „Endpreis“ oder „Bestellmenge“ bricht er jedoch nicht in die nächste Zeile um. Warum?
Die Antwort: Der Text ist zu kurz. Er passt in die Zelle. Warum also umbrechen?
Heute in der Excel-Schulung musste ich schmunzeln. Ich zeigte gerade, dass man mit den Tastenkombinationen [Shift] + [Strg] + [↓] bis zur letzten gefüllten Zelle einer Spalte markieren kann, mit [Shift] + [Strg] + [Ende] bis zum Ende des gefüllten Bereiches.
Plötzlich: Helle Aufregung. Bei einer Teilnehmerin stand der Bildschirm Kopf:
Blöde zum Lesen
Da Problem kannte ich: Auf vielen PC sind Tasten [Alt] + [Strg] + [↓], [Alt] + [Strg] + [↑], [Alt] + [Strg] + [←] und [Alt] + [Strg] + [→] belegt, um die Anzeige des Bildschirms zu drehen. Also wieder zurück und die Dame war glücklich.
… und ich finde die Ursache …
Heute in der VBA-Schulung. Ein Teilnehmer fragt mich, wo der Fehler in der folgenden Codezeile ist:
ActiveWorkbook.Worksheets(„Filme“).Activate
warum?
Nein – ActiveWorkbook ist richtig – es war keine andere Datei offen. Jedoch hatte der Teilnehmer das Tabellenblatt „Filme “ benannt – mit einem Leerzeichen Ende …
Böses Leerzeichen!
Heute in der VBA-Schulung fragte mich eine Teilnehmerin, warum sie eine UserForm nicht löschen dürfe. Im ersten Moment war ich verblüfft.
Wo bitte ist „entfernen“?
Dann fiel mein Blick auf die Titelzeile. Aha – der Fehler war schnell gefunden: an einer anderen Stelle lief noch ein Makro, das gerade unterbrochen war:
Ah!
Ich habe eine Spalte, in der das Geschlecht angegeben wird. Dort habe ich mit Hilfe einer Datenüberprüfung eine Dropdownliste für männlich und weiblich erstellt. Klappt prima.
Liste
In einer anderen Spalte lege ich nun fest, dass dort nur die Zahlen zwischen (einschließlich) 1 und 10 eingetragen werden dürfen. Ich finde aber den Schalter nicht, mit dem ich eine Auswahlliste erhalte:
wo ist die Liste?
Die Antwort: Diesen Schalter gibt es nicht. Entweder Liste oder ganze Zahl zwischen 1 und 10. Wenn Sie sich für „ganze Zahl“ entscheiden, müssen Sie die Zahl eintragen und können nicht die Zahl über ein Dropdownfeld auswählen. Ist auch ein bisschen verständlich – man könnte ja schließlich auch die Grenzen zwischen 1 und 1.000.000 wählen. Dann müsste Excel eine Liste mit einer Million Zeilen zur Verfügung stellen …
Nö, gar nicht traurig – geht doch!
Hallo Michaela,
manchmal bin ich selbst verblüfft – aber es geht:
Wenn in einer Zelle die Zahl 17 steht, kannst Du sie mit
0,0000.
in 0,0170 formatieren – der Punkt verschiebt die Kommastellen drei Positionen nach links
Liebe Grüße
Rene
geht doch!
Ein großes Dankeschön an Dominik Petri für seinen Vortrag Power BI auf den Anwendertagen. Wer ihn und seinen Vortrag in Fulda am 15./16. Dezember 2016 erleben möchte findet weitere Informationen auf:
Heute hat er einige Male schmunzeln möchte, beispielsweise bei der Übersetzung von „city“ und „place“ zu „Ort“. Etwas verwirrend – auf Deutsch:
Welcher Ort?
Auch das Fehlen der Jahresangabe in einigen Diagrammen ist merkwürdig:
Welches Jahr?
Immerhin: Microsoft beweist in Fehlermeldungen Humor mit „Stirnrunzeln senden“:
Ich habe in Visio eine Exceltabelle eingebettet. Wenn ich nun die Visio-Zeichnung als pdf speichere werden die grünen Ecken der Fehlerüberprüfung ausgedruckt. Übrigens: Die Kommentare mit ihren roten Ecken auch. Das will ich doch gar nicht!
Weg mit den Ecken!
Ist Ihnen das schon einmal aufgefallen:
Eine Datei wird freigegeben (Registerkarte „Überprüfen“). Nun werden nach Speicherung der Datei die Änderung der Zellinhalte und Formatierungen der anderen Kollegen angezeigt.
Allerdings nicht, wenn ein Kollege Zeilen und/oder Spalten fixiert. Eigentlich praktisch – aber es verwundert doch ein bisschen. Zuerst.
fix und nicht frei
Warum darf ich einige Aktionen nicht auf das Tabellenblatt anwenden?
komisch?
Die Antwort: Die Datei wurde freigegeben: Deshalb dürfen Sie nicht mehr alle Aktionen durchführen – möglicherweise arbeiten andere Kollegen auch mit dieser Datei.
Excel-Schulung. Ich erkläre die Funktion SUMME. Anschließend eine Übung. Unter der Spalte mit den laufenden Nummern soll die Summe der Preise gezogen werden. Die Preise befinden sich jedoch in einer anderen Spalte.
Ich schaue einer Teilnehmerin über die Schulter: Sie klickt auf die leere Zelle, in der sich die Summe befinden soll:
SUMME
Der Vorschlag wird verworfen, indem sie auf der erste Zelle der Zahlenkolonne klickt:
Hier beginnt die SUMME
Anschließend tippt sie einen Doppelpunkt:
SUMME bis …
Und schaut mich nun erstaunt an, was Excel „tut“. Zugegeben: Excel schlägt eine Zelle vor bis zu der der Bereich ausgedehnt werden könnte. Jedoch ist die Anfängerin überfordert, weil nach der Eingabe eines Doppelpunktes etwas „anderes“ in der Formel steht.
Vielleicht wäre hier keine Hilfe besser gewesen.
Manchmal bin ich selbst verblüfft. Heute habe ich Excel 2007 unterrichtet. Doch, doch, es gibt viele Firmen in Deutschland und anderen Ländern, die Office 2007 einsetzen.
Und ich hätte felsenfest behauptet, dass es nicht stimmt, was eine Teilnehmerin behauptete. Sie fragte mich: In einer Spalten stehen Mengenangaben, in einer anderen Preise. Die Preise sind als Euro formatiert. Warum wird bei =B4*D4 das Ergebnis nicht auch als Währung oder Buchhaltung formatiert?
Format wird nicht mitgenommen
Tatsächlich: Während in „meinem“ Excel 2016 das Ergebnis korrekt formatiert wird, war dies in Excel 2007 noch nicht der Fall. Übrigens: bei =B4+D4 wurde das Ergebnis mit einem Eurozeichen angezeigt. Schön, dass dieses Fehlen behoben wurde.
Hallo Rene,
da hast Du Dir richtig viel Mühe gemacht! 🙂
Ergänzungs-Tipp für SAP & Co:
Eine 1 in eine leere Zelle schreiben, diese 1 dann kopieren, den Bereich der als Text formatierten Zahlen markieren und Diese über „Inhalte einfügen“ + „Multiplizieren“ in Zahlen umwandeln.
Beste Wochenendgrüße aus Berlin
Uwe
Warum klappt das denn nicht?
Ich habe eine Liste, in der ich die Nachnamen filtern möchte mit
entspricht M?yer
oder
entspricht M?ier
Erstaunlicherweise filtert Excel nur Mayer und Meyer, allerdings nicht Meier oder Maier. Ich bin sicher, dass solche Namen vorhanden sind.
geht nicht
Die Antwort: Sie müssen auch beim zweiten Kriterium die Option „entspricht“ einschalten. Wenn in dieser Combobox nichts ausgewählt wurde, wird das Kriterium entfernt:
bitte beide!
Was mache ich falsch?
Ich wollte mal schnell einen Kalender erzeugen und die Wochenende mit einer grauen Zellfarbe hinterlegen.
Keine Wochenenden!
Die Antwort: Sie haben als Formel
=WOCHENTAG(B2;2>5)
geschrieben. Die Klammer wurde falsch gesetzt. Es muss heißen:
=WOCHENTAG(B2;2)>5
Ah, danke – jetzt funktioniert es!
Übrigens: 2>5 liefert den Wert FALSCH. FALSCH entspricht in Excel der Zahl 0. Hier wurde versucht Wochentag(B2;0) zu berechnen. Excel verlangt allerdings die Parameter 1, 2 oder 3 und liefert bei 0 einen Fehler. Da Fehler in der Bedingten Formatierung nicht angezeigt, sondern stillschweigend übergangen werden, wird keiner der Tage grau formatiert.
Heute in der Excelschulung fragte mich ein Teilnehmer, warum seine Liste so merkwürdig weitergezählt wird:
komisch ?!?
Ich habe schnell entdeckt, was er gemacht hat: in der obersten Zelle stand nicht 1%, sondern =1%. In der zweiten dagegen 2%. Dadurch interpretiert Excel zwei unterschiedliche Dinge – eine Formel und einen Wert.
Beim Herunterziehen wiederholt Excel dieses Muster – abwechselnd Formel und Wert. Und der Wert wird weitergezählt. Den Sprung von 2% auf 102% erklärt man mit der Schrittweise 1 (=100%).
Seltsam, was manche Menschen machen …
Übrigens: amüsante Randbemerkung: Wenn Sie =1% in eine Zelle eintrage, wird das Ergebnis der Formel nicht formatiert – man sieht also 0,01, während in der Eingabezeile korrekt „=1%“ steht.
Hallo zusammen,
ich habe eine benutzerdefinierte Liste angelegt, die ich manchmal verwende. Allerdings schreibt Excel manchmal die Orte in Kleinbuchstaben. Warum?
Kleine Orte
Die Antwort: Sie haben den ersten Ortsnamen („Köln“) in Kleinbuchstaben eingetragen. Deshalb werden nun alle Texte in Minuskeln geschrieben.
Hallo Herr Martin,
doch, doch ich bin ganz sicher. Ich habe der Zelle G16 einen Namen („Betreuer“) gegeben. Aber nach einer Weile verschwindet er – er ist weder links oben im Namensfeld noch im Namensmanager zu sehen. Haben Sie eine Ahnung warum?
Namen verschwinden
Wahrscheinlich wird ihre Datei durch Code (VBA oder VS.NET) gesteuert. Und wahrscheinlich hat dort jemand ein Makro geschrieben, das einen Namen „Betreuer“ anlegt – allerdings unsichtbar.
Man kann herausfinden, welche Namen verwendet wurden, beispielsweise so:
Sub Namenstest()
Dim i As Integer
Dim strListe As String
For i = 1 To ActiveWorkbook.Names.Count
strListe = strListe & vbCr & ActiveWorkbook.Names(i).Name & „:“ & ActiveWorkbook.Names(i).Value & “ sichtbar: “ & ActiveWorkbook.Names(i).Visible
Next
MsgBox strListe
End Sub
Mehrere unsichtbare Namen
Denn: Man kann unsichtbare Namen generieren, die mit der Datei gespeichert werden:
ActiveWorkbook.Names.Add Name:=“Betreuer“, RefersTo:=“$G$35:$K$35″, Visible:=False
Hallo Herr Martin. Ich habe eine große Tabelle, bei ich Zeile 1 und Spalte A fixiert habe.
Oben und links wurde fixiert.
Wie gelange ich schnell in die linke, obere Ecke?
– Meine Antwort: Drücken Sie [Strg] + [Pos1]
Nicht ganz links oben
– Ich weiß – allerdings landet der Cursor dann in der Zelle B2, weil ja Zeile 1 und Spalte A fixiert sind.
– Dann tippen Sie doch den Zellnamen A1 in das Namensfeld!
Links und oben? – Nicht ganz!
– Ich weiß – allerdings wird dann die Tabelle nicht nach links und nicht nach oben mitbewegt – ich würde dann auch gerne Spalte B und Zeile 2 sehen.
– Sorry – aber ich glaube, DAFÜR gibt es keine Lösung!
Gestern in der Excelschulung kam eine Teilnehmerin mit einer Datei. Ob ich sie mir mal ansehen könne? – Klar! Mein erstes Erstaunen: Die Datei hatte eine Größe von fast 10 MByte!
Ziemlich groß!
Das Öffnen dauerte – wie befürchtet – sehr lange.
Ein Blick in die Statuszeile zeigte mir an, dass die Datei 58.300 Seite lang war!
58.300 Seiten ?!?
Mit [Strg] + [Ende] zur letzten Zelle U1048576. Diese Spalte war leer!
Also zurück zu U1. Von dort markierte ich mit [Shift]+[Strg]+[Ende] bis zur letzten Zelle und löschte Inhalt und Format der nicht benötigten Spalten.
Alles muss raus!
Ein Blick: „nur“ noch 29.150 Seiten
nur noch halb so viele Blätter
Meine Nachfrage, ob sie denn alle Blätter benötigt, wurde verneint. Also: Ab A10000 wurde der „Rest“ der Tabelle nach unten markiert und ebenfalls gelöscht.
Raus damit!
Und schließlich habe ich festgestellt, dass unterschiedliche Linienstärken und Zellformate verwendet wurden. Raus mit den Formaten; alles markieren und neu und einheitlich formatieren.
Doch nicht so!
Und schließlich müssen nicht alle fortlaufenden Nummern der ersten vier Spalten bereits eingetragen sein – dies kann man mit Hilfe einer Formel erledigen, die man elegant runterziehen kann:
Das Ergebnis: 230 Seiten und eine Datei, die nur noch 610 Byte groß ist.
Das sieht schon besser aus.
Ich sehe unterschiedliche Schriftgrößen in verschiedenen Zellen. Ein Klick auf jede Zelle zeigt an, dass sie in Arial, 10 pt formatiert ist. Woher rührt der Unterschied zwischen B2 und C2?
Zwei Mal 10 pt – trotzdem unterschiedlich
Die Antwort: Man kann ein Zeichenformat „auf“ eine Zelle legen oder auch die einzelnen Zeichen formatieren. Wahrscheinlich wurden bei den Zellen in der Spalte B die Zeichen einzeln mit 6 pt formatiert; die ganze Zelle jedoch mit 10 pt. Die „inneren“ Formate gewinnen:
Die Lösung
Hallo Herr Martin,
seit mein Kollege an meinem Rechner war, zeigt er nicht mehr 6.000,00 an sondern 6 000,00. Ist zwar auch hübsch, aber so möchte ich das nicht. Was hat er gemacht?
Warum plötzlich Leerzeichen als Tausendertrennzeichen?
Die Ursache kann entweder in der Systemsteuerung liegen. Schauen Sie mal unter „Region“ nach, welches Tausendertrennzeichen dort eingestellt ist:
Systemsteuerung?
Falls dies korrekt ist, werfen Sie einen Blick in die Excel-Optionen – genauer: in „Erweitert“. Dort muss eingestellt sein, dass das „Trennzeichen vom Betriebssystem übernommen“ wird.
Hier ist der Haken!
Warum verschwinden denn die Daten?
Heute in der Excel-Schulung erklärt mir eine Teilnehmerin, dass durch das Ausblenden von Spalten darin vorhandene Daten verschwinden. Ich glaube es nicht und frage nach. Nachdem sie die Datei gespeichert hat, behauptet sie. Ich lasse mir genau erklären, was sie tut:
„Nun, ich blende die Spalte aus, trage einige Informationen ein, lösche andere Informationen; blende irgendwann die Spalte wieder ein. Und die ausgeblendeten Daten sind dann weg.“
Klar – ich kann nachvollziehen, was sie getan hat: Blendet die Spalte aus, markiert die Zellen links und rechts der ausgeblendeten Spalte (und damit auch die ausgeblendete Spalte selbst) und lösche diese Daten (und damit auch die unsichtbaren). Ich glaube, DAS wird sie nun nicht mehr tun:
Spalte ausblenden
Zellen markieren und löschen
Spalte wieder einblenden
Letzte Woche habe ich LibreOffice Calc unterrichtet. Dort gibt es wohl noch mehr Bugs, Ungereimtheiten, Merkwürdigkeiten und sonderbare Phänomene als in Excel.
Beispielsweise existiert in Calc ein Zahlenformat „Wahrheitswert“. Und ähnlich wie in Excel schiebt auch Calc bei einigen Aktionen nach bestimmten Regeln dieses Format unter die Zellen. Das bewirkt, dass nicht die Ergebniszahl 50 angezeigt wird, sondern „WAHR“. Man wundert sich:
50 wird als WAHR formatiert.
Als freuen wir uns über das kleinere Übel Excel und leben mit seinen Fehlern und Schwächen. Und hoffen, dass Excel DAS nicht übernimmt.
Hallo Herr Martin,
auf der Suche meines Excel-Problems bin ich über Ihre Seite gestolpert und würd mich freuen, wenn Sie mir ggf. helfen könnten: Ich habe hier eine ziemlich umfangreiche Pivot Tabelle und vermisse die Funktion, die es mir erlaubt anstatt des Min. oder Max-Wertes bei den Wertfeldeinstellungen Quartile auszugeben. Aber auch bei den Rohdaten tue ich mir schwer. Ich schaffe es zwar für eine vorgegebene Auswahl die Quartile zu berechnen, aber was ich benötige ist eine dynamische Ausgabe, z.B. bei Auswahl über den Auto-Filter. Auch mit Excel 2016 und Power-Pivot habe ich es bisher nicht geschafft. Haben Sie eine Idee wie so was gehen könnte?
Viele Grüße
#####
Hallo Herr B.,
das ist eine interessante Frage – danke dafür.
Ich habe heute auf dem Nachhauseweg geknobelt, wie man so etwas lösen wollte. Dachte an PowerPivot – aber wenn Sie schreiben, dass es DORT kein Quartil gibt, muss ich dort auch nicht mehr auf die Suche geben.
Wenn Sie eine einfache Pivottabelle hätten, könnten Sie Sie mit Matrixfunktionen oder der Funktion AGGREGAT nachbauen. Ich habe es mal versucht – siehe Anhang. Allerdings schreiben Sie „umfangreiche“ Pivottabelle – ich fürchte das Schlimmste.
Übrigens hat Andreas Thehos ein Video dazu gemacht:
Heute in der Excel-Schulung fragte ein Teilnehmer, warum er seine Balken im Diagramm nicht breiter machen könne.
Etwas dicker wäre vielleicht ganz schön.
Die Antwort lieferte mir ein Blick auf die Daten: Er hatte Leerzeilen und -spalten zwischen den Daten eingegeben:
Hallo Herr Martin,
ich weiß nicht, was passiert ist: Ich habe eine Tabelle in Excel angelegt:
Die neue Tabelle
Ich wollte nun ein neues Tabellenblatt einfügen – und nun das:
???
Die Antwort: Wenn Sie genau hinsehen, haben Sie nicht ein neues Blatt, sondern ein Diagramm eingefügt. Wahrscheinlich wollten Sie die Tastenkombination [Shift] + [F11] drücken, haben aber nur [F11] gedrückt. löschen Sie das Diagramm und versuchen es noch einmal!
Die Antwort
Ich bin wirklich kein Fan von libreOffice Calc. Aber ein paar Dinge hätte Microsoft dort abschauen können. Und in Excel implementieren können. Beispielsweise die Silbentrennung:
Heute habe ich LibteOffice Calc unterrichtet.
Ein Teilnehmer kopiert eine Liste und fügt sie nicht AUF einer Zelle, sondern IN einer Zelle ein. Das Ergebnis sieht wie folgt aus:
Eine Teilnehmerin schreibt „Montag “ (an Ende ein Leerzeichen) in eine Zelle, zieht es nach rechts und wundert sich, warm Calc die Wochentage nicht weiterzählt.
Und nun mein Lob an Excel: DIESE beiden Fehler tauchen in der Tabellenkalkulation Excel nicht auf!
Ich erstelle eine Pivottabelle.
Obwohl ich nach dem Ortsnamen sortiere, weigert sich Excel zu sortieren. Warum?
Geht nicht!
Die Ursache der vermeintlich falschen Sortierung finden Sie, wenn Sie einen Blick in Benutzerdefinierten Listen werfen. Befindet sich dort diese Liste hat sie Vorrang gegenüber der Standardsortierung. Anders als beim „gewöhnlichen“ Sortieren kann hier nicht umgestellt werden.
Lösung 1: Löschen Sie diese Liste
Lösung 2: Wenn Sie nicht auf Ihre Liste verzichten möchten, erstellen Sie diese Liste neu, sortieren diese Liste und importieren nun die sortierte Liste. Dann wird Excel auf diese sortierte Liste zugreifen.
Einen Haken hat diese Lösung: Wenn nun ein Begriff in der Pivottabelle auftaucht, der nicht in der benutzerdefinierten Liste vorhanden ist, steht er am Ende der Pivottabelle … Also doch Lösung 1 ?!?
Danke an Andreas Thehos für diesen Hinweis. Er hat auf
ein Video dazu erstellt.
Hier befindet sich der Übeltäter!
Sehr geehrter Herr Martin,
ich habe ein Problem mit Excel und bin bei meiner Internetrecherce auf ihre Seite gestoßen. Sie gefällt mir sehr gut.
Da ich leider keine Info zu meinem Fehler gefunden habe hoffe ich das Sie mir weiterhelfen können.
Hier mein Fehler:
Wenn ich in der Kopf- oder Fußzeile eine Zahl als erstes Zeichen einsetze macht mir Excel automatisch ein Leerzeichen davor. Wenn ich einen Buchstaben als erste Zeichen habe dann wird nichts eingefügt. Ich habe keine Ahnung warum und wie man diesen Automatismus deaktiviert.
Unter Seitenlayout/Seite einrichten ändert sich auch nichts.
Ich hoffe Sie haben eine Lösung
Die Eingabe
Und plötzlich ein Leerzeichen vor dem 1. Quartal
Hallo Frau H.,
was bekomme ich für die Antwort? *lach*
Im Ernst:
Sub Kopfzeile()
MsgBox ActiveSheet.PageSetup.LeftHeader
End Sub
Es liefert:
„&“ITC Quay Sans Book,Standard“1. Quartal
GWEGH“
PS: DIESER Bug in Excel hat mich wirklich sehr irritiert. DARÜBER bin ich nämlich noch nie gestolpert.
Warum dürfen mal wieder alle – warum nur ich nicht?
Heute wieder in der Excel-Schulung: Eine Teilnehmerin fragte mich, warum sie keine Diagramme erstellen durfte:
Kein Diagramm möglich
Ich wusste, wo ich suchen musste: In den Optionen war eingeschaltet:
„Objekte anzeigen: [x] Keine (Objekte ausblenden)“
Der Grund: Vielleicht hatte sie aus Versehen auf die Tastenkombination [Strg] + [6] gedrückt. Damit werden Bilder deaktiviert (was Sie im linken Teil des Menübandes sehen können). Vielleicht wollte sie [Shift] + [Strg] + [6] drücken (Standardzahlenformat) oder [Shift] + [6] (das &-Zeichen).
Die Ursache
Hallo,
Ich wollte doch nur einen kleinen Kalender erstellen. Warum sieht die „fünf“ so komisch aus?
Die Antwort: Wahrscheinlich haben Sie Option „Durchgestrichen“ aktiviert. Die Tastenkombination [Strg] + [5] aktiviert diese Formatierung. Vielleicht haben Sie sie fälschlicherweise gedrückt.
Durchgestrichen
Ich würde ja gerne lesen – aber ich finde den Text nicht.Er lautet: „Die Liste ist die Basis für die Überprüfung aller „Geräte und Maschinen“ (GERMA). Es werden alle notwendigen Informationen […]“
Haben Sie einen Tipp, Herr Martin:
Die Antwort: Wenn Sie für die Zelle D3 den Dialog „Zelle formatieren“ aufrufen und dort die Registerkarte „Ausrichtung“, so stellen Sie fest, dass die Option „An Zellgröße anpassen“ eingeschaltet wurde.
Der verschwundene Text
Hallo Herr Martin,
ich habe eine Liste, bei der ich Auswertungen erstellen soll. Es geht dabei um eine Gewichtung. Folgender Schlüssel liegt der Tabelle zugrunde:
Ich erstelle eine Formel:
=ZÄHLENWENN(C5:C70;“*x*“)*1+ZÄHLENWENN(C5:C70;“*P*“)*2+ZÄHLENWENN(C5:C70;“*EG*“)*3+
ZÄHLENWENN(C5:C70;“*En*“)*4+ZÄHLENWENN(C5:C70;“*Ew*“)*5+ZÄHLENWENN(C5:C70;“*Ek*“)*2+
ZÄHLENWENN(C5:C70;“*Em*“)*3+ZÄHLENWENN(C5:C70;“*B*“)*4+ZÄHLENWENN(C5:C70;“*A*“)*4+
N(„ZÄHLENWENN(C5:C70;““*Aw*““)*4″)+ZÄHLENWENN(C5:C70;“*K*“)*1+ZÄHLENWENN(C5:C70;“*V*“)*1+ZÄHLENWENN(C5:C70;“*U*“)*1
Vielleicht nicht clever – aber nachvollziehbar. Der Wert „Aw“ wurde auskommentiert – deshalb die Funktion „N“. Allerdings – an einer Stelle rechnet er nicht richtig – ich finde den Fehler nicht:
Die Antwort: Man muss schon genau hinschauen. Sie haben in die Matrix weitere Informationen eingetragen. Bei Frau Weiß finden sich die beiden Texte SPX. Dort wird das „x“ natürlich auch mitgezählt. Diese Texte dürfen Sie nicht in die Tabelle schreiben.
Ich hätte es nicht geglaubt. Sehen Sie selbst:
In einer gestalteten Tabelle wurden mehrere Zellen verbunden – hier: die Zellen E1:E3:
Wählt man nun das Werkzeug „Format übertragen“
und klickt (aus Versehen?) auf andere Zellen. Am besten solche, in denen Zahlen stehen:
Die Zellen werden nun nicht zu einer Zelle verbunden, sondern der Inhalt der unteren Zelle wird lediglich ausgeblendet. Davon kann man sich mit einem Blick auf die Summe überzeugen – das Überschlagen der sichtbaren der Spalte G Zahlen kann niemals die Summe 5538 ergeben:
Der Grund: Deaktiviert man die Option „Zellen verbinden“:
so taucht die verborgene (die verborgenen) Zelle(n) wieder auf – sie waren niemals gelöscht, sondern nur ausgeblendet:
Auf dieses unglaubliche Phänomen hat mich Andreas Thehos aufmerksam gemacht – danke dafür!
Kennen Sie den?
Tragen Sie in eine Spalte die Uhrzeiten 0:00 und 0:01 untereinander ein, markieren beide Zellen und ziehen sie herunter. Tragen Sie daneben die Uhrzeiten 8:00 und 8:01 ein, markieren beide und ziehen sie ebenfalls herunter. Berechnen Sie daneben die Differenz:
Kein Problem
Lassen Sie sich mit der Formel
=F2=F1
anzeigen, ob die Werte der beiden übereinanderliegenden Zellen identisch ist:
Komisch?!
Das Ergebnis erstaunt. Warum ist manchmal 08:00 = 08:00, manchmal jedoch nicht?
Die Antwort auf die Frage erhält man, wenn man die Differenzen als Standard formatiert und sich die Nachkommastellen anzeigen lässt:
Das Problem der 15 Nachkommastellen wurde schon häufig beschrieben
Rechenungenauigkeit II
Excel rechnet falsch
Nicht ganz rund die Sache
– auch hier taucht es auf.
Danke an Andreas Thehos für diesen Hinweis.
Uff!
In einer Liste stehen Zahlenwerte. Daneben eine Spalte mit Berechnungen, beispielsweise die Mehrwertsteuer:
Trägt man nun statt der Zelle H2 den Tabellennamen und den Zellnamen ein (dies passiert, wenn man beispielsweise bei der Formeleingabe auf ein anderes Blatt wechselt), dann hat Excel kein Problem damit.
Jedoch: Sortiert man die Liste, so erstaunt das Ergebnis: Die Werte sind falsch. Schaut man sich die Formeln an, stellt man fest, dass die Bezüge nicht sortiert wurden. Anders wenn wenn man statt Blattname!Zellname nur Zellname eingegeben hätte.
Uuuuuaaaaahhhh!
Danke an Andreas Thehos für diesen wunderbaren Hinweis!
Das Problem wurde schon häufig in Foren beschrieben: Trägt man in Excel in eine Zelle Q1 (oder q1) ein, zieht diese Zelle nach unten, erhält man Q2, Q3, Q4, Q1, Q2, Q3, Q4, Q1, …
Diese Phänomen kann man erklären, da Excel in der deutschsprachigen Version den Text „Quartal“ erkennt und bei Quartal 1 nach dem Herunterziehen nach vier Quartalen wieder von vorne beginnt. Ebenfalls bei 1. Quartal (Achtung: Mit Leerzeichen dazwischen, wie ich in
Autoausfüllen – gar nicht automatisch
beschrieben habe.)
Erstaunlich jedoch, dass bei dem Text „Halbjahr“ Excel dies nicht als Wiederholung erkennt. Auch sonst ist mir keine andere Text-Zahlenkombination bekannt, bei der Excel dieses Phänomen aufweist.
Übrigens: Ausschalten kann man es nicht. Wenn man Q1, Q2, Q3, Q4, Q5, Q6, … haben möchte muss man die ersten fünf Werte Q1, Q2, Q3, Q4, Q5 eintragen und dann herunterziehen. Auch eine [Strg]-Taste verhindert nicht, dass auf Q4 wieder Q1 folgt, sondern kopiert den Wert.
Nach vier Quartalen wieder von vorne
geht doch
Nachtrag: Wenn Sie die englischsprachige Oberfläche eingeschaltet haben, müssen Sie selbstverständlich „quarter 1“ eintragen, um diesen Effekt zu erzielen.
Ich halte die Zahlen für übertrieben, die seit einigen Tagen durch die Presse gehen:
Immerhin: Hätten Sie meine Beiträge
Januar geht nicht
Plötzlich stehen andere Zahlen in der Zelle
Aus FRA wird Frau aus KLR wird klar aus weng wird wenig
gelesen, hätten sie es verstanden.
Übrigens: erstaunlich, dass in der Presse nicht Zellnamen und Gennamen, wie beispielsweise 1700043E15 erwähnen, die beim Eintragen oder Hineinkopieren auch Probleme bereiten:
1700043E15
Ich werde regelmäßig gefragt, wie man Exceltabellen „kleiner“ machen kann. Vor allem bis Excel 2003, in denen *.xls noch kein gezipptes XML-Archiv war, wurden Dateien schnell sehr groß.
Ich habe eine Reihe von Vorschlägen.
Einige davon nimmt Inquire mit seinem Werkzeug „Übermäßige Zellformatierung entfernen“ ab. Auf der Seite
https://support.office.com/de-de/article/Verwendungsm%C3%B6glichkeiten-f%C3%BCr-Inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42
wird erklärt: „Haben Sie schon einmal eine Arbeitsmappe geöffnet und festgestellt, dass sie langsam geladen wird oder riesengroß geworden ist? Möglicherweise ist in der Arbeitsmappe auf Zeilen oder Spalten Formatierung angewendet, von der Sie nichts wissen. Verwenden Sie den Befehl Übermäßige Zellformatierung bereinigen, um übermäßige Formatierung zu entfernen und die Dateigröße deutlich zu verringern. Hiermit können Sie ein „Arbeitsblattaufblähen“ vermeiden, wodurch Excel schneller wird.“ Und auf
https://support.office.com/de-de/article/Bereinigen-von-%C3%BCberm%C3%A4%C3%9Figer-Zellformatierung-auf-einem-Arbeitsblatt-e744c248-6925-4e77-9d49-4874f7474738
lesen wir: „Das Bereinigen von übermäßiger Formatierung wird ausgeführt, indem Zellen vom jeweiligen Arbeitsblatt entfernt werden, die sich hinter der letzten nicht leeren Zelle befinden. Wird eine bedingte Formatierung beispielsweise auf eine gesamte Zeile angewendet und sind in der Zeile nur bis zur Spalte V Daten enthalten, wird die bedingte Formatierung aus den Zellen entfernt, die auf die Spalte V folgen.“
Übermäßige Zellformatierung entfernen
Ist das alles? Kein Putzen von doppelten Einträgen in der bedingten Formatierung? Kein Zusammenfassen von ähnlichen Farben? Kein Löschen von nicht verwendeten Formatvorlagen? Oder Zahlenformaten? Keine Vorschläge zum Verkleinern von Bildern? Da wäre noch einiges zu tun …
Erstaunlich: Obwohl ich nichts anklicke – noch nicht einmal den Mauszeiger bewege – blinkt mein Excel. Genauer: das Einfügen-Symbol in der Registerkarte „Start“.
Guckst du:
Die Idee, endlich in Excel eine gute (!) Funktion zu implementieren, mit deren Hilfe man zwei Dateien vergleichen kann, war dringend nötig. Und so findet sich seit Excel 2013 (wenn Sie die richtige Version gekauft haben) hinter der Registerkarte „Inquire“ eine Schaltfläche „Dateien vergleichen“:
Dateien vergleichen
Das Ergebnis von zwei „größeren“ Dateien begeistert:
Tabellen vergleichen
Jedoch weniger das Design, wenn es darum geht, VBA-Code zu vergleichen. Zugegeben – die Option ist praktisch – das Ergebnis schwer zu interpretieren.
VBA1 und VBA2
Microsoft hat in Excel 2013 das Analysewerkzeug „Inquire“ eingeführt, das in Excel 2016 nicht geändert wurde. Damit erspart man sich die umständlich Suche, ob es Verknüpfungen, ausgeblendete Zeilen, Spalten, Blätter gibt, ob Zahlen als Text formatiert wurden, wo Formeln stecken, die einen Fehler liefern, wo Zirkelbezüge zu finden sind, …
Inquire
Damit ist Microsoft auf dem richtigen Weg. Für alle, die fremde Dateien analysieren möchten („Was hat der Kollege denn da gemacht?“) oder die zwei Dateien miteinander vergleichen möchten – ein richtiger Schritt in die richtige Richtung. Jedoch mir fehlen:
Wird leider nicht vom Inquire gefunden – unter dem Rechteck befindet sich die Zahl 3000
Fazit: Guter Ansatz, muss jedoch erweitert werden. Wenn Microsoft mich fragen würde – ich könnte Ihnen viele Dinge nennen, die Anwendern Probleme verursachen.
Und: ein dankeschön an Stefan, der mir geholfen hat, das Teilchen auseinanderzunehmen.
Ich verstehe es nicht. In einer Datei zeigt Excel im Projektfenster von VBA sämtliche Tabellenblätter als Worksheet und als Datei. Obwohl Intellisense beim Tabellenname alle Methoden und Eigenschaften anzeigt, führt das Ausführen zu einem „schwerwiegenden Fehler“. Ich stehe ratlos davor …
Die merkwürdige Tabelle
Das ist eine geniale Idee, sie hat nämlich alle Attribute der Genialität. Eigentlich ist es naheliegend und so simpel – trotzdem kam niemand darauf:
Excel mal nicht aus der Perspektive „Excel ist toll, und wir erklären es auch gerne den dümmsten Anwendern“, sondern aus der Perspektive „Wir Anwender sind doch nicht blöd, sondern diejenigen, die sich den Mist ausgedacht haben!“ beschreiben.
Meinen herzlichen Glückwunsch zu der wirklich informativen Seite!
Ralf
Ich bin wirklich verblüfft. Wusste nicht, dass es geht:
Man erstellt eine Datenüberprüfung für mehrere Zellen (beispielsweise Zahlen, Datumswerte oder Listen).
Datenüberprüfung (Gültigkeit)
Diese (und einige anderen Zellen) werden über Zellen formatieren / Schutz „nicht gesperrt“. Das Blatt wird mit einem Blattschutz versehen. Nun ist es für den Anwender möglich, in die freien Zellen etwas einzutragen und diesen Inhalt in die Zellen mit Datenüberprüfung hineinzukopieren.
Das heißt: Das Einfügen von Inhalten unterläuft die Datenüberprüfung. Perfide!
Nix wird geprüft!
Danke an Julian für diesen Hinweis.
In Excel 2016 kamen neue Diagrammtypen hinzu. Vor allem die Treemaps sind nicht ganz einfach zu durchschauen.
Erster Test: Eine Liste der Länder Europas:
Kein Ergebnis
Klar: Excel verwendet die letzte Spalte (Die Texte „Hauptstädte“) als numerische Werte. Also: Spalte löschen oder Leerspalte einfügen:
zu viele Kategorien
Auch nicht überzeugend: Jedes Land wird einzeln in der Legende aufgelistet. Besser wäre es mit einer zweiten Kategorie zu arbeiten:
besser – aber (noch) nicht gut
Noch besser wäre es, diese Hauptkategorie (EU/Nicht-EU) zu sortieren. Und: wenn man sich auf eine Zahlenspalte beschränkt (Einwohner statt Fläche) – sieht das Ganze recht gut aus:
sortiert
Wir lernen: bei einer Spalte und vielen Daten wirkt das Ergebnis von Treemap etwas verloren:
viele – zu viele?
Völlig absurd wird es, wenn man mehrere Tausend Datensätze in einem Treemap darstellen möchte:
Zeitgenössische Kunst?
Also eine Pivottabelle davorschalten?
Pivot?
Geht leider nicht – man muss die Pivottabelle erst in Daten umwandeln (kopieren / Inhalte einfügen). Dann hat man ein aussagekräftiges Treemap-Diagramm:
so!
Inquire ist ein mächtiges Tool, das eine komprimierte Datenanalyse einer Arbeitsmappe liefert, für deren Elemente man lange suchen müsste. Ich gestehe – ich habe es nicht gleich verstanden:
Die einzelnen gefundenen Elemente werden auf der linken Seite aufgelistet und sind dort einsehbar. Warum jedoch liefert der Export in eine andere Datei keine Informationen, sondern schreibt einfach nichts in die leere Datei?
Wohin verschwinden die Informationen?
Die Antwort: Ich hätte auf der linken Seite die Objekte auswählen müssen, die ich exportieren möchte. An vielen Stellen warnt Excel – hier jedoch nicht und überlässt dem erstaunt auf die leere Arbeitsmappe Schauenden die Suche. Ein Hinweis, dass nichts ausgewählt wurde wäre einfach schön gewesen … Vielleicht in Excel 2019.
Amüsante, aber erklärbare Sache:
Ein Datum kann benutzerdefiniert, beispielsweise mit
TTTT, “ den “ TT. MMMM JJJJ
formatiert werden:
Ändert man das Format in:
„München“, „den“ TT.MM.JJJJ
erhält man eine Fehlermeldung:
Der Grund: JETZT wird das Komma als Zahlentrennzeichen interpretiert, wie man leicht zeigen kann, wenn man eine Zahl so formatiert:
Lustiges Ergebnis
Ich versuche mich mit den „intelligenten Tabellen“ (den formatierten Tabellen) anzufreunden. Aber sie machen es mir verdammt schwer.
Ich habe eine Artikelliste – umgewandelt als „intelligente Tabelle“. Soweit, so gut. Wenn ich neue Daten unterhalb der Liste eintrage, wird die Liste automatisch erweitert. Prima.
Wenn ich allerdings statt eines Wertes eine Formel eintrage – beispielsweise um aus dem Bruttowert den Nettowert zu berechnen, so beendet Excel die Liste und zeigt mir die Formale als Formel unterhalb der Tabelle an. Das will ich aber nicht!
DAS will ich nicht!
Excel zwingt mich zuerst mindestens einen Wert in die neue Zeile einzutragen – DANN erst erlaubt er Formeln …
Hallo Herr Martin,
ich habe eine sehr große Datei in Excel für meine Projekte. Ich kopiere neben der Überschrift immer eine Zeile heraus, die ich in Outlook einfüge. Dort ist es allerdings nicht möglich, die Spaltenbreite zu ändern, obwohl er den Mauszeiger korrekt anzeigt. Gibt es hierfür einen Trick?
Spalte wird nicht breiter
Die Antwort: Wenn Sie in den Tabellentools / Layout in der Gruppe „Zellengröße“ die Spaltenbreite numerisch eintragen – beispielsweise aus 3 cm 8 cm machen, wird die Spalte breiter. Obwohl Outlook es nicht anbietet.
Geht doch – über die Spaltenbreite
Sehr geehrter Herr Dr. Martin,
zu Ihrem obigen Buch habe ich eine kurze Frage. Auf Seite 125 habe ich die Aufgabe 7.2.1 gelöst. Mit der Zielwertsuche komme ich auf zwei Lösungen, wie Sie anhand der beigefügten Tabelle sehen können.
Gibt es zwei Lösungsmöglichkeiten?
Freundliche Grüße
CR
Zielwertsuche
Hallo Herr R.,
in D3 steht die Formel =B3+C3 // in E3 jedoch =(B4*2)+C4.
Deshalb kommen zwei unterschiedliche Ergebnisse raus. Die zweite Tabelle ist korrekt durchgerechnet.
schöne Grüße
Rene Martin
Hallo Herr Martin,
Vielen Dank für diese Zusammenstellung. Ich bin ebenfalls Trainer und werde gelegentlich auf Ihre Sammlung verweisen, denn ich stelle bei Seminaren mitunter fest, dass Mitarbeiter schon mal annehmen, bestimmte „Erscheinungen“ würden nur bei ihnen auftreten.
Gruß Thorsten
Hallo Rene,
erst einmal sorry für die späte Rückmeldung, ich war im Urlaub. Das Problem mit den Verbindern habe ich mittlerweile gelöst. Jetzt hab ich aber ein weiteres Probelm allerdings diesmal mit Excel.
Ich schreibe Daten aus Visio per VBA Makro nach Excel. In Excel werden die Daten dann aufsteigend nach einer Positionsnummer sortiert. Allerdings hat Excel mit der Sortierung der Positionsnummer einige Probleme. Wenn die Nummern beispielsweise 1, 2, 3, ,4 usw lauten läuft alles gut – bei Zahlen wie 1.1, 1.2, 1.3, …. 1.9 klappt auch noch die Sortierung einwandfrei.
Sobald ich aber Nummern ab 1.10 oder 2.11 benutze wird die Sortierung nicht mehr eingehalten. Es sieht dann beispielsweise wie folgt aus :
1.1
1.10
1.11
1.12
1.2
….
Ich möchte allerdings folgende Sortierung:
1.1
1.2
1.3
…
1.10
1.11
Hast du eine Idee wie ich das anstellen kann ? Hab im Internet schon etwas gegoogelt allerdings finde ich leider keine Lösung dazu. Achja die Nummern werden als Text behandelt.
Liebe Grüße
Stephan
##########
Hallo Stephan,
Excel unterscheidet bei fast allen Dingen zwischen Text und Zahl: rechnen, Zahlenformat, Dateneingabe, filtern, … und eben auch beim Sortieren. 1.1 und 1.10 wird als Text erkannt und eben wie Text sortiert: Anton < Berta < Cäsar < Dora …
Würde Excel die Zahlen als Zahlen erkennen, würde er ein Datum daraus machen:
01. Januar
01. Januar 2001
und so weiter
Die einzige Lösung, die ich sehe: Gib die Daten in der Form 01.01, 01.02., … 01.10, 01.11, … 01.20, 01.21 … ein. Dann produziert die Textsortierung keine Probleme.
Übrigens: das Gleiche Problem hast Du auch bei Dateinamen, wenn Deine Kamera Bilder mit Dateinamen pic1, pic2, … pic10, pic11, … speichert. Dann gilt: pic1 < pic10 < pic11 < pic2 …
Liebe Grüße
Rene
Mit PowerPivot können Felder aus verschiedenen Tabellen des Datenmodells verknüpft werden. Excel verlangt dabei, dass mindestens eine Tabelle nur eindeutige Werte besitzt (die 1-Seite der 1:n-Beziehung). Falls dies nicht der Fall ist, wird es mit einer Fehlermeldung quittiert.
Bedauerlicherweise werden nicht die Datentypen überprüft. So ist es möglich, dass man Text mit Zahl oder Datum verknüpfen kann. Diese Funktion hätte man auch integrieren können …
Geht nicht!
Text mit Zahl – das geht schon !?!
Wie kann denn so etwas sein? Ich erhalte einen Download aus SAP, bewegen mich mit [Strg] + [↓] ans Ende der Liste (Zeile 572), markiere die Spalte (C) und lese in der Statuszeile Anzahl: 636. Wer kann hier nicht zählen?
Zählt Excel falsch?
Die Antwort: Drücken Sie erneut [Strg] + [↓]. Weiter unten auf dem Tabellenblatt befindet sich ein weiterer Bereich, der auch mitgezählt wird, den Sie aber nicht sehen …
Noch mehr Daten – also doch richtig gezählt.
Heute in der VBA-Schulung ist uns folgende amüsante Sache aufgefallen:
In einer Datei – egal ob in der Personal.xlsb oder einer anderen beliebigen Makrodatei wird ein Makros geschrieben:
Sub Hinkelstein()
Application.Workbooks.Open „E:\Eigene Dateien\Comics\Asterix.xlsx“
MsgBox „Wo ist Obelix?“
End Sub
Legt man in den Optionen das Makro auf einen Shortcut – beispielsweise [Strg] + [q] gibt es keine Probleme.
Verwendet man jedoch [Strg] + [Shift] + [A], wird nur die erste Zeile ausgeführt – nach Öffnen der Datei bricht das Makro ab (auch bei längeren Makros). Muss ich das verstehen?
Nach Asterix ist Schluss
Wenn ich mehrere Spalten markieren und einen Doppelklick zwischen zwei Spalten in den Spaltenkopf mache, werde manchmal alle Spalten optimal breit, manchmal nur eine. Warum?
Die Antwort: Das hängt davon ab, wie Sie markieren. Wenn Sie einen Bereich markieren macht Excel etwas anderes als bei markierten Spalten:
Ein markierter Bereich
Markierte Spalten
Warum klappt die Suche manchmal nicht?
Ich habe irgendwann entdeckt, dass man in Excel nach Farben suchen kann. Tolle Sache. Dennoch: Manchmal klappt es nicht. Dabei habe ich sogar die eine formatierte Zelle ausgewählt, um sicher zu sein, dass ich den korrekten Farbton treffe. Warum klappt es nicht?
Excel findet nichts.
Die Antwort: Die Zelle ist nicht formatiert. Sie wurde mit der bedingten Formatierung formatiert. Da Excel nicht „erkennen“ kann, welche Farbe die entsprechende Bedingung liefert, geht er von der Grundfarbe aus.
Schöne Seite, steckt viel Arbeit drin. Zu dem Kapital Datenaustausch mit externen Anwendungen könnte ich auch schon Bücher schreiben. (CSV-Import/Export, ISO/ANSI-Konvertierung, Text nach Zahl – Zahl nach Text usw.).
Was vielleicht noch eine sinnvolle Ergänzung wäre zu der Seite. Excel im Verbund/Austausch mit Word. Excel im Verbund/Austausch mit Access, da es sich ja auch um Office-Anwendungen handelt, die dann viele Leute auch haben, die Excel auf dem Rechner haben. Eventuell noch die Unterschiede zwischen den einzelnen Excel-Versionen aus den Kompabilitätsproblemlisten, damit man weiß, was genau mit welcher Excel Version funktioniert und was sich ggf. ab einer neueren Version geändert hat.
Das Kapitel Fehler aus Verknüpften Dokumenten, die später gelöscht worden sind, wäre vielleicht auch noch interessant. (Häufiger Fehler in Netzwerken, neben Änderung von Benutzer-Rechten im Netzwerk, Excel kann dann die verknüpften Dokumente nicht mehr öffnen, was zu einem Fehler führt). Also dass wäre das Kapitel „Excel im Unternehmensnetzwerk oder Arbeitsgruppen“. Noch eine Fehlergruppe ist „Excel im internationalen Umfeld“ z.B. durch den Austausch mit andersprachigen Excel-Versionen.
Thorsten
Warum darf ich in der Seitenansicht nicht zoomen?
Heute: kein Zoom
Die Antwort: die Tabelle ist so klein, dass man sie nicht „hochzoomen“ kann – sie passt komplett bei dieser Bildschirmauflösung auf die Seite.
Warum steht der Mai am rechten Rand? Ich habe ihn doch gar nicht rechtsbündig formatiert …
Warum rechts?
Die Antwort finden Sie im Dialog „Zellen formatieren / Ausrichtung“
Die Ausrichtung wurde auf -1° gestellt. Übrigen: Wenn Sie die Ausrichtung auf +1° einschalten und zusätzlich den Zeilenumbruch aktivieren, verschwindet der „ai“ vom Wonnemonat:
geht auch ohne „ai“
Wirklich komisch. In unserer Firma gibt es Abteilungen. Sie tragen Nummern der Form 1 oder 1.5 oder 1.3.1. Erstaunlicherweise zeigt Excel bei „1“ und bei „1.3.1“ grüne Ecken an. Bei „1.5“ allerdings nicht …
Viele grüne Ecken, aber nicht überall …
Die Antwort finden Sie, wenn Sie mit der Maus über das Smarttag fahren:
Unterschiedliche „Fehler“
Die Antwort:
„1“ wir interpretiert als Zahl 1, die als Text formatiert wurde.
„1.3.1“ wird interpretiert als Datum: 01.03.2001, das als Text formatiert wurde.
„1.5“ – hum – keine Ahnung – eigentlich interpretiert es Excel als Datum (01.05. im aktuellen Jahr). Warum hier kein Smarttag erscheint – keine Ahnung …
Hallo Herr Martin,
warum stehen die „-Zeichen nicht in der Mitte der Zelle? Ich habe Sie doch zentriert?
Die Antwort erhalten Sie, wenn Sie die Zellen linksbündig formatieren. Vor den Anführungszeichen befinden sich noch Leerzeichen – wahrscheinlich wollte jemand „per Hand“ die Gänsefüßchen in die Litte der Zelle schieben.
Und was läuft hier schon wieder schief?
spinne ich oder Excel?
Die Antwort finden Sie, wenn Sie einen Blick in den Dialog „Zellen formatieren“ werfen:
Darum!
Des Rätsels Lösung: Die Ausrichtung wurde auf 1° gestellt …
Herr Martin,
aufgrund eines Problems mit Excel – oder eher eines völligen Versagens dieser Software – ließ es sich nicht vermeiden, Google mit meinen Sorgen zu füttern. Nach einigen Tagen und vielen Suchkombinationen gelangte ich schließlich auch auf Ihre Internetseite „excel-nervt.de“ und schmunzelte bereits in den Google-Suchergebnissen, als ich den Namen Ihres Projektes entdeckte.
Ohne zu ausführlich werden zu wollen: Wir erwarben kürzlich ein Office-Paket (2013) und ich war der Meinung, eine Rechnungsvorlage auf Basis eines DIN-gerechten Briefbogens erstellen zu müssen, um den angestaubten Rechnungsdokumenten, die bis dato Verwendung fanden, eine modernere Note zu verleihen. Ich war zuversichtlich und wusste, dass Textfelder, Formen und die Möglichkeit, Größenangaben millimetergenau festlegen zu können, mein Vorhaben zu einem guten Ergebnis führen sollten. Ich habe mich geirrt – sehr sogar!
Selbstverständlich ist mir bewusst, das Excel kein Layout-Programm ist und akzeptiere diese Tatsache auch. Aber was diese Software, die bei Neuerscheinung sicher mehrere Hundert Euro gekostet hatte, „von sich gibt“, lässt mich als Anwender verzweifeln.
Einen Kreis, der proportional mit 1 cm angelegt ist, druckt Excel aus Prinzip nicht rund oder wie soll ich das Ergebnis auf dem Papier interpretieren?! Aber egal, dann lege ich die Form eben unproportional an, sieht auf dem Bildschirm aus, wie ein Ei, wird dafür aber kreisrund gedruckt – hey, Erfolgserlebnis. DIN, feste Maße? Na ja, kann man versuchen, wird von Excel aber mehr oder weniger ignoriert. Dann halt ausdrucken, messen und korrigieren. Macht ja nichts, dass das Objekt mal eben 5 mm „danebengedruckt“ wurde. Textfeld 8,5 cm breit angelegt? Ha! Nicht mit Excel, das „klaut“ einfach mal ein paar Millimeter.
Welchen Fehler kann man machen? Das DIN-Dokument im Seitenlayout anlegen und dann in den Normalmodus wechseln. Kann man auch gleich bisschen Geld zerreißen, oder so – macht sicher genauso viel „Freude“. Textfelder sind plötzlich schmaler, umbrechen also auf einmal den enthaltenen Text. Angelegte Linien sind kürzer. Und warum hat ein Objekt in zwei unterschiedlichen Ansichten unterschiedliche Maße?! Da geht mir mein Logikverständnis mächtig flöten.
Nun hat man sein Dokument für einen ergebnisorientierten Ausdruck „hingebogen“, fällt einem plötzlich ein, dass man die Rechnung ja auch mal als PDF verschicken möchte. Der „Spaß“ geht also am Montag in die nächste Runde und ich will ehrlich gesagt noch gar nicht wissen, wie das PDF aussehen wird. Vermutlich brav wie das angelegte Dokument – also mit Eierkreisen, abgeschnittenen Textfeldern und zu kurzen Linien. Muss ich wirklich einen PDF-Creator (Druck) installieren, um vor Excel auf die Knie zu fallen, damit man sein Dokument auch so nutzen kann, wie man sich das vorgestellt hat? Dann öffnen wir halt der nächsten Software-Seuche Tor und Tür. Schöne, neue Welt.
Grüße
Robert=
Die Seite ist gut gelungen – Hut ab! Ein Hinweis zu dem Beitrag http://www.excel-nervt.de/strg/. Was an der Tastenkombination Strg+# noch nervt: Sollten Zellen mit Zahlen markiert sein wenn die Tastenkombination verwendet wird, so werden die Zahlen als Datumsangaben formatiert!!!
Peter
Warum darf ich plötzlich nicht in die Fußzeile schreiben?
Warum?
Die Antwort: Der Text ist zu lang. Die Fußzeile in Excel erlaubt eine Textlänge von maximal 255 Zeichen. Nicht nur in VBA, sondern auch in Excel:
Kann man die Fläche unter einer Linie in einem Diagramm füllen?
Geht das?
Die Antwort: nein. Aber Sie können den Diagrammtyp „Fläche“ verwenden:
So schon!
Hilfe! Warum darf ich kein Diagramm erstellen?
Geht nicht?!
Die Antwort: Vielleicht haben Sie aus Versehen auf die Tastenkombination [Strg] + [6] gedrückt. Damit deaktivieren Sie die Bilder (was Sie im linken Teil des Menübandes sehen können). Vielleicht wollten Sie [Shift] + [Strg] + [6] drücken (Standardzahlenformat) oder [Shift] + [6] (das &-Zeichen).
Und schon geht es wieder …
Hallo Herr Martin,
ich würde gerne in einer Pivottabelle eine Spalte sortieren, aber es klappt nicht. Warum?
Sortieren – geht nicht!
Die Antwort: Sie haben in Ihrem Beispiel eine Abhängigkeit zwischen dem Ort und dem Firmenname. Eine Firma ist in einem Ort. Deutlicher wird es, wenn Sie Daten verwenden, bei denen n Firmen in m Orten auftauchen. Beispielsweise Artikel und Verkäufer:
Hier geht es!
Hier wird deutlich, was sortieren bedeutet. Innerhalb der ersten Kategorie wird die zweite Kategorie sortiert. Aber pro Gruppe wird erneut sortiert. Da im ersten Beispiel nur ein Wert pro erste Kategorie vorhanden ist, „sieht man die Sortierung“ nicht.
Wer sich mit PowerPivot beschäftigt, der stellt fest, dass zusätzlich zu Excel ein weiteres Programm geöffnet wird. Das bedeutet: Wenn die Eingabe in Excel nicht beendet wird, erhalten Sie beim Öffnen von PowerPivot eine lustige Fehlermeldung.
Umgekehrt – wenn Sie beispielsweise in PowerPivot eine Formel eingeben oder ändern; die Eingabe jedoch nicht beenden, erhalten Sie „auf der anderen Seite“ (in PowerPivot) auch eine Fehlermeldung:
Das Perfide: Man „sieht“ die andere Seite nicht – man muss explizit hinüber wechseln.
