Author Archives: Medardus

Meine Motivation ging heute Morgen winkend an mir vorbei.

Heute in der Excel-Schulung wir erstellen aus der aktuellen Liste der Goldmedaillen der Olympiade ein Diagramm. Ich fordere die Teilnehmer auf, die Balken dicker zu machen. Ich zeige ihnen die Option „Abstandbreite“, die verringert werden muss, damit die Balken breiter werden.

Eine Teilnehmerin schaut mich fragend an. Ich werfe einen Blick auf ihren Bildschirm und entdecke, dass sie den Aufgabenbereich so schmal zusammen geschoben hat, dass man nicht erkennen kann, dass sich hinter der Abstandsbreite und der Reihenachsenüberlappung jeweils ein Schieberegler befinden.

Der Schieberegler ist nicht zu erkennen.

Man muss den Aufgabenbereich vergrößern – dann sieht man den Schieberegler.

Hier wird nicht geflucht, verdammt nochmal!

Eigentlich wollte ich nur ein Formular ändern. Und dann erhalte ich folgende lustige Meldung:

»Ihr Computer ist nicht für die Verwendung von Informationsrechten (Information Rights Management, IRM) eingerichtet. Um IRM einzurichten, melden Sie sich bei Office an, und öffnen Sie eine vorhandene Nachricht oder ein Dokument, das IRM geschützt ist, oder wenden Sie sich an Ihr (sic!) Helpdesk.«

Auch Word nervt manchmal gewaltig!

Man muss nicht alles glauben was stimmt

Hi Rene,

alles gut bei Dir? Wie geht´s?

Kleiner Excel-Bug, da musste ich direkt an Dich denken 😊

  • Wir haben ein Programm, das heißt ITS.
  • Beim Schreiben in dieser Mail macht es automatisch „IST“ daraus, durch Einstellung der Autokorrektur-Option (kleiner blauer Blitz) kann man die automatische Änderung rückgängig machen
  • Witzig: In Excel geht das nicht. Es erscheint kein Pfeil o.Ä.. Entweder ich muss I.T.S. daraus machen, oder ähnliche komplizierte Umwege nehmen…

Liebe Grüße

Marius

####

Hi Marius,

das ist lieb, dass du an mich denkst. Den letzten Punkt habe ich natürlich schon längst auf der Seite „geht nicht – sorry Leute“ aufgenommen; zur Autokorrektur habe ich auch einen Artikel: aus FRA wird FRAU, aus WENG wird WENIG, aus DNA wird DANN, aus Wei wird Wie, …

Und: ich wollte einmal in einer Schulung Folgendes zeigen:

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:

Das Ergebnis verblüfft; aber des Rätsels Lösung ist schnell gefunden: Autokorrektur!

Liebe Grüße  – aber ich nehme euer ITS gerne auf (denn für heute habe ich mal keinen Artikel …)

Rene

Für [wahre] Freunde geh ich durch die Hölle… den [anderen] zeig ich gern den Weg dorthin.

Gerade mit Andreas Thehos diskutiert (danke für den Hinweis – Andreas!):

Ich trage in A1 eine Zahl ein. Ich nenne diese Zelle MWSt. Ich trage in D1 einen anderen Wert ein und nenne diese Zelle redMWSt.

Ich lasse folgendes Makro über das Dokument laufen:

ActiveWorkbook.Names(„MWSt“).Visible = False

Damit taucht der Name „MWSt“ nicht mehr in der Liste der Namen (im Namensfeld oder im Namensmanager) auf.

Man kann allerdings damit arbeiten:

=500*MWSt

Ganz blöde: der Inquire übergeht auch den ausgeblendeten Namen:

Das beste Mittel gegen eine Überdosis Realität ist Humor.

Gestern schrieb Holger, dass „aufgrund der Datumserkennung jede Menge Gene falsch geschrieben werden? 1MRZ und so Zeugs…“. Ich schaue nach:

1MRZ ist ein „Crystal structure of a flavin binding protein from Thermotoga Maritima, TM379″. Aha. Schnell eine Suchmaschine angeworfen. Auch

3JAN
2FEB
5FEB
1MRZ
2MRZ
4MRZ
1JUN
3JUN
4JUN
5JUN
1JUL
2JUL
3JUL
4JUL
2AUG
3AUG
1SEP
1OKT
2OKT
3OKT
4OKT
2NOV
3NOV
4NOV
2DEZ
3DEZ
4DEZ
5DEZ

werden gefunden. Kopiert man diese Liste nach Excel erhält man:

Holger kommentierte: „Kein Wunder, daß Excel im Labor so beliebt ist, was? Excel ist ein ganzes Chemikalienlager“

Gott ist mit an Sicherheit grenzender Wahrscheinlichkeit ein tschechischer Schlagersänger.

Walter ist genervt. Er arbeitet gerne mit (intelligenten) Tabellen. Jedoch benötigt er manchmal Formeln der Form $A1:$F1. Das ist in (intelligenten) Tabellen jedoch nicht möglich.

Walter weiß, dass er diese Formeln eintragen kann. Walter möchte das aber nicht. Also geht Walter auf die Suche und findet in den Optionen in der Kategorie „Formeln“ die Einstellung „Tabellennamen im Formular verwenden“. Walter schaltet diese Option aus. Nun ist Walter glücklich – denn nun kann er markieren und erhält $A1:$F1 statt Tabelle1[Bundesland].

Selbstgespräche geben einem die Chance, Recht zu behalten.

Das ist mir noch nie aufgefallen. Heute in der VBA-Schulung. Ich gebe als Übung auf zwei Makros zu erstellen: eines soll sämtliche Tabellenblätter schützen, eines soll den Blattschutz aufheben. Ein Teilnehmer testet und bemerkt ein Ruckeln:

Und hier der Code – falls jemand selbst testen möchte. Das Ruckeln erscheint beim Schutzaufheben:

Sub AlleBlätterSchützen()
Dim i As Integer

For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Protect
Next

End Sub

Sub Blattschutzaufheben()
Dim i As Integer

For i = 1 To ActiveWorkbook.Sheets.Count
ActiveWorkbook.Sheets(i).Unprotect
Next

End Sub

Gesucht tot und/oder lebendig: Schrödingers Katze

Heute in der VBA-Schulung in einem chemischen Konzern. Wir üben Makrorekorder: ein benutzerdefiniertes Zahlenformat wird aufgezeichnet:

Eine Teilnehmerin fragte mich, warum Excel „Wasser“ in die Zelle einfügt. Ich habe zwei Mal hinschauen müssen, bis ich entdeckt hatte, dass Sie versehentlich auf die Zelle H20 geklickt hat und dies per Makrorekorder aufgezeichnet hat …

Die ersten 5 Tage nach dem Wochenende sind die schlimmsten

Hallo lieber René,

…da habe ich doch gleich vorab eine Frage:

wie lassen sich die Objekte in der beigefügten Excel-Tabelle löschen?

Ich hatte Daten aus dem Internet kopiert.

LG Traudl

Hallo Traudl,

Du musst in der Registerkarte „Entwicklertools“ den Entwurfsmodus einschalten.

Dann markieren

und löschen

Übrigens: es liegen acht übereinander – also mehrmals löschen!

Oder eben: Start / Suchen und Auswählen / Inhalte auswählen / Objekte

Dann sind sie alle markiert. Einmal löschen.

Rene

einmal weg

zweimal weg

dreimal weg

siebenmal weg

ganz weg!

Auf meinem Grabstein soll später mal stehen „Ich würd‘ jetzt auch lieber am Strand liegen…“

Haben die den Cosinus vergessen?

Nicht, dass ich sie unbedingt benötige – aber ich wollte in VBA etwas bei den Worksheetfunctions nachsehen. Dort kann man sämtliche Excel-Funktionen verwenden. Sämtliche? Nun: Sinus, Cosinus und Tanges finde ich nicht – nur ACos (ArcCos), ASin (ArcSin), Sinh, Cosh, … Auch der Objektkatalog zeigt diese trigonometrischen Funktionen nicht an.

Hormone, ab in die Ecke und nachdenken, was ihr gerade getan habt

Hallo René,

ich habe diese Datei grad von einem User erhalten.

Das ist zwar seltsam, liegt aber wohl an der Art wie Excel rechnet (und ist ein bekannter Fehler)

Viele Grüße

Stefan

#####

Hallo Stefan,

habe ich habe eine kleine Liste zum Thema Rechenungenauigkeiten (und ihre Folgen) in Excel auf meinem Blog. Bspw:

http://www.excel-nervt.de/diejenigen-die-wissen-wie-es-nicht-geht-sollen-nicht-diejenigen-stoeren-die-es-bereits-tun/

Wenn du die Zahl 8625,21 einträgst, in XML nachschaust, dann steht eine andere Zahl drin. Ich habe nachgesehen – die beiden Zahlen -132120,21 und 132145,38 sind korrekt. Die Summe macht Probleme …

Wir lernen: Dateneingabe ist problematisch; runterziehen auch – und rechnen sowieso. Wir sollten Excel hernehmen, um hübsche Tabellen zu drucken. Mit bunten Rändern.

meint zynisch

Rene

Schokoladenfondue macht man aus Schokoladenresten. – Ich verstehe diesen Satz nicht.

Ich verstehe es nicht. Ich erhalte eine Datei. In einer Spalte stehen die Zahlen (Zahlenformat: Standard) linksbündig in der Zelle. Die Zellen sind zentriert formatiert!

Auf den Zellen liegt eine bedingte Formatierung. Schalte ich sie aus, sieht das Ganze so aus:

Zellenformat links; Ausrichtung: zentriert. Schalte ich eine bedingte Formatierung ein, fällt auf, dass die Zellen, welche die Bedingung erfüllen, ihre Ausrichtung ändern ?!?

Ich erstelle ein neues Tabellenblatt – die Grundausrichtung ist – linksbündig!

Ich kopiere das neue Blatt in eine neue Arbeitsmappe und schaue mir den XML-Code an. Ich finde im XML-Code ZWEI Zellformate:

Normalerweise steht bei einer leeren Tabelle in der Datei „styles.xml“ im Knoten cellXfs nur die erste Zeile.

Nun meine Frage: Hat jemand eine Idee, wie man die Grundausrichtung eines Tabellenblattes ändern kann? Also eine Arbeitsmappe so manipulieren kann, dass ein neu erzeugtes Tabellenblatt in dieser Datei eine Grundausrichtung „links“ hat.

Und: nein – nicht über Zellformatvorlagen (habe ich nachgeschaut – die sind nicht angerührt):

Der Kühlschrank ist das beste Beispiel dafür, dass innere Werte zählen.

Perfide!

Versuchen Sie einmal Folgendes: erstellen Sie eine oder mehrere Listen in einer gespeicherten Excelmappe.

Wechseln Sie auf ein anderes Tabellenblatt und erstellen dort eine bedingte Formatierung und/oder eine Datenüberprüfung mit einem Bezug auf das erste Blatt:

Kopieren Sie das Blatt in eine neue Arbeitsmappe. Speichern Sie die neue Mappe, schließen alles und öffnen die neue Zieldatei. Excel weist darauf hin, dass sich in der Arbeitsmappe eine Verknüpfung befindet:

Über Datei / Informationen sieht man diese Verknüpfung, aber:

Man kann sie nicht löschen! Auch Datei / Informationen / Auf Probleme überprüfen weist nur darauf hin, dass es eine Verknüpfung gibt. Verrät weder wo, noch hilft der Assistent diese Verknüpfung zu finden:

Man muss wissen so sie stecken – zum Glück kann man ja Bedingte Formatierung und Datenüberprüfung suchen. Und dort findet man dann die korrupten Verknüpfungen:

Ein Dankeschön an Bettina, die sich mit diesem Problem herumgeärgert hat, für diesen wertvollen Hinweis.

Excel – Zahlen / rechnen / Formeln

Und jetzt nerve ich mal:

Mein neues Excel-Buch ist erschienen – 544 Seiten zum Thema Zahlen, rechnen, Formeln, Funktionen, verknüpfen, knobeln, denken, Probleme in und um Excel lösen. Mein gesamtes Wissen (okay – ein großer Teil davon). Von Erklärungen SVERWEIS, verschachtelte WENN-Funktionen und Datumsberechnungen über die Funktionen der Kategorie Nachschlagen & Verweisen, Textfunktionen und Statistik bis hin zum numerischen Integrieren, Iterationen und Determinatenberechnungen. ich denke – da ist für jeden etwas dabei. Erhältlich in jedem Buchladen: Excel: Zahlen rechnen Formeln: Formeln, Berechnungen und Verknüpfungen in Excel Taschenbuch – 10. Januar 2018 von René Martin (Autor) ISBN-10: 3746064872 ISBN-13: 978-3746064871

Hier könnte ein einfühlsames Zitat stehen. Tut es aber nicht.

Hilfe! Da war ein Witzbold an meinem Rechner! Mein Excel sieht ganz kaputt aus? Was hat er gemacht? Und: wie bekomme ich es wieder normal?

Die Antwort: Der Witzbold hat Registerkarten ausgeblendet, vertauscht; in den Registern Gruppen vertauscht und ausgeblendet. Aber Sie können den Urzustand wiederherstellen, indem Sie in den Optionen / Menüband anpassen das Ganze mit Zurücksetzen / Alle Anpassungen zurücksetzen:

Endlich hat mein Leben wieder Gin

Excel-VBA-Schulung: Fünf Minuten nach Beginn. Wir schreiben unser erstes Hello-World-Programm. Ein Teilnehmer drückt aus Versehen die Enter-Taste:

Und fragt erstaunt: „muss ich das Programm kompilieren, bevor es läuft?“

Er hat recht: der Begriff „Kompilieren“ ist schlecht gewählt für die Titelzeile des Meldungsfensters.

Sofort schalten wir die „automatische Syntaxüberprüfung“ aus, deren Begriff ebenfalls schlecht gewählt ist.

…ich bin klein, mein Herz ist rein.. alles gelogen sagte der Wolf und frass das Rotkäppchen…

Ich habe heute mit einem Freund ein paar Excel-VBA-Lösungen programmiert.

In einer Tabelle sollen für den Ausdruck unter jeder Ergebniszeilen (Zeile mit den Zwischensummen) ein Seitenumbruch eingefügt werden (und noch ein paar weitere Dinge eingeschaltet und formatiert werden). Der Hintergrund: jedes dieser Blöcke sollte ausgedruckt an jeweils einen Kunden verschickt werden.

Der erste Test erstaunt:

Oha – man darf nur 1.026 manuelle Seitenumbrüche einfügen – mehr erlaubt Excel nicht.

Draußen nur Kännchen

Erstaunlich. Eine Excel-Schulung. Wir üben die Grundrechenarten und Summe. Formatieren die Tabelle – unter anderem mit dem Buchhaltungsformat.

Wir schauen und das Ergebnis in der Seitenvorschau an (Datei / Drucken):

Upps – kein Eurozeichen! Ich vergrößere den Zomm – und schwups – da sind sie!

Das Erstaunliche: bei einigen Teilnehmern hat es funktioniert, bei einigen wurden nur einige (nicht alle) € verschluckt; und: nach einer Weile wurden sie korrekt angezeigt: rein – raus – rein – raus …

Es ist unmöglich etwas narrensicher zu machen, denn Narren sind ja so erfindungsreich.

Sehr geehrter Herr Martin,

ich habe mir irgendwo ein Video angesehen, wo man z. B. bei Postleitzahlen festlegen kann, daß nur 5 Zahlen verwendet werden sollen:

Gebe ich aber eine PLZ ein, die mit einer Null beginnt, erscheint eine Fehlermeldung.

Ich möchte zum einen diese Datenüberprüfung ermöglichen, denn aber eine PLZ schreiben können, die mit einer Null beginnt. Wie muß diese Einstellung in der Datenüberprüfung aussehen?

Es wäre sehr nett, wenn Sie mir einen Hinweis geben würden.

Herzliche Grüße

F.

Sehr geehrter Herr F.,

wenn Sie die Zahl 01157 in eine (unformatierte) Zelle eingeben, dann wandelt Excel diese Zahl in die vierstellige Zahl 1157 um. Damit die 0 „stehen“ bleibt, müssen Sie die Zelle(n) vorher als Text formatieren.

Und das ist auch die Antwort: als Text formatieren UND die Datenüberprüfung einstellen.

schöne Grüße

Rene Martin

…. loading… ███████████████] 99,99%

Eigentlich schade. Wer den Konjunktor XOR aus dem Physikunterricht oder aus der bitweisen Verknüpfung aus dem Informatikunterricht kennt, der wird enttäuscht sein, dass man ihn in Excel dafür leider nicht einsetzen kann. Der Grund: Wandelt man eine Zahl mit der Funktion DEZINBIN um, so ist der Binärwert leider keine Zahl mehr, sondern ein Text. Und damit kann man SO nicht weiterrechnen. Dann halt umständlich …

Save the date

Auch im kommenden Jahr 2018 finden wieder in München die Excellent days statt:

Vom 19. – 20. Oktober werden hervorragende Referenten zu den Themenblöcken „Techniken und Tools“, „Formeln & Funktionen“ und „Power & co“ sprechen.

Interesse:

Weitere (vorläufige) Informationen finden Sie unter

www.munich-office-groupe.de

Ab Februar steht das Programm und der Veranstaltungsort, die genauen Themen und die Referenten fest.

Männer verfahren sich nicht… sie kreisen ihr Ziel ein!!

Haben Sie ein Tablett? Verwenden Sie den Tablettmodus? Haben Sie damit schon VBA programmiert? Zum Beispiel folgende Schleife:

Sub Schleife()

Dim i as Integer

MesBox „Los geht’s“

For i = 1 to 10

MsgBox i

Next

Msgbox „fertig – uff!“

End Sub

Das Ergebnis: Manchmal (!?!) flackert der Bildschirm unangenehm beim Testen (wie hier bei der 7):

Tschüss Niveau – bis Montag

Vielleicht sollte ich nicht so viel nachprüfen. Aber manchmal will ich es einfach wissen.

Ich trage in eine Spalte unterschiedliche Dinge ein: ganze Zahlen, Dezimalzahlen, Datumsangaben, Uhrzeiten, Text. Ich überprüfe ihre Existenz mit den beiden Funktionen ISTZAHL und ISTTEXT. Die Ergebnisse sind komplementär. Jedoch bei den beiden Gebilden WAHR und FALSCH liefert Excel: ISTZAHL: nö! ISTTEXT: keine Spur!

Ja – was ist es denn?

 

vorgestern dachte ich noch, es könnte etwas werden …

Vielleicht ist der Name der VBA-Funktion etwas unglücklich gewählt:

Function Autor()
Autor = ActiveWorkbook.BuiltinDocumentProperties(„Author“)
End Function

Dennoch: Dass Excel so eine starke Abneigung gegen diesen Namen verspürt und ihn noch nicht einmal in Groß-/Kleinschreibung anzeigt, finde ich schon frech:

„Benutzer“ oder „Benutzername“ darf ich.

Wegen Rückrufaktion vorübergehend aus dem Sortiment genommen

Schon erstaunt.

Ich schreibe in eine Excelarbeitsmappe einen Text.

Ich speichere die Datei. Benenne die Endung um in „.zip“. Ich entzippe die Datei, suche im Ordner „xl“ nach der Datei „sharedStrings.xml“ und öffne sie in einem guten XML-Editor:

Ich will es wissen! Ich füge weitere Tags hinzu:

Speichere die XML-Datei, zippe das Archiv und sehe nach. Kein Fehler beim Öffnen der Datei (!), kein weiterer Text wird angezeigt. Das Ergebnis sieht so aus:

Ich schaue nach: Datei / Informationen / Auf Probleme überprüfen – Excel findet keine Probleme:

Keine Benutzerdefinierten XML-Daten. Ich schaue im Inquire nach – auch dort wird nichts gefunden:

Das heißt: Hacker aller Länder: versteckt Eure Daten in XML-Elementen! Das findet kein Mensch!

Randbemerkung: okay, okay – man darf nicht jeden beliebigen Knoten an jeder Stelle platzieren. Aber das hat man schnell herausgefunden, was man darf.

Was passiert eigentlich mit den Kindern, die bei IKEA nicht aus dem Småland abgeholt werden? Und wieso sind dort die Hot Dogs so günstig?

Amüsant: Ich öffne eine alte Datei, die seit Jahren auf meiner Festplatte liegt. Die Beschriftung der Registerkarte „Einflußgröße“ ist noch in der alten Rechtschreibung geschrieben.

Da mache ich doch flux aus dem „ß“ ein „ss“. Darf ich aber nicht! Man muss Excel schon überlisten, um aus alt neu zu machen. Probiert es aus … Verblüffend und amüsant.

I kiss better than I cook!

Ich trage in einer Liste Zahlen von 1 bis 32 ein. Ich lasse mir mit Excel 16.0.8625 mit der Funktion ZEICHEN das dahinter liegende Zeichen anzeigen.

Wunderbar. Ich öffne diese Datei auf einem anderen Rechner mit Excel 16.0.8730. Ups! Was macht Excel mit der Funktion ZEICHEN bei euch?
Liegt es an der Excel-Version oder sind noch andere Dinge im Spiel (Betriebssystem, Add-Ins …)? Fragen über Fragen …

Es reicht nicht, keine Ideen zu haben, man muss auch unfähig sein, diese umzusetzen!

Kennt ihr dieses Phänomen?

Ich gebe zwei Datumsangaben aus dem letzten Jahrhundert (bspw. 01-01-1997 und 01-01-1998) untereinander ein; daneben zwei Zahlen. Auf diesen Viererblock setze ich ein Punkt (XY)-Diagramm auf.

Ich vergrößere den Bereich, indem ich ein weiteres Datum – allerdings aus dem 21. Jahrhundert (bspw. 22.12.2017) – eintrage. Daneben ein Wert. Ich erweitere den Datenreihenbereich durch Herunterziehen. Was passiert?

Excel beginnt nun plötzlich beim ersten, möglichen Datum – beim 01.01.1900 – SO hatte ich das allerdings nicht gewollt, Schweinebacke!

Natürlich kann man das wieder korrekt hinformatieren. Muss man das korrekt hinformatieren. Trotzdem: ist nervig! Excel – lass die Achse doch einfach so – nimm die kleinste Zahl und die größte, gib einen Toleranzbereich dazu – und gut ist!

Wie ist mir dieses Phänomen aufgefallen? Ich habe ein Diagramm für die Diäten der Abgeordneten des Deutschen Bundestages erstellt. Und dachte, es sei ganz nett, die letzte Linie – die letzte Erhöhung bis ins nächste Jahr weiterzuziehen …

Hier muss man Hand anlegen, damit man DIESES Ergebnis erhält.

A geht, B geht, C nicht, D und E dann auch wieder

Mal wieder verblüfft. Ich „spiele“ gerade ein bisschen Excel – berechne pythagoräische Zahlen. Um sie mit Hilfe des Solvers berechnen zu lassen, vergebe ich Zellnamen: a, b und dann: Fehlermeldung:

Sie müssen entweder einen gültigen Zellbezug oder einen gültigen Namen für den markierten Bereich angeben.

?!?

Verstehe ich nicht. D funktioniert dann wieder, e ebenso … C ist weder eine Funktion noch ein Zellname.

Erzähle nicht, wie Du warst, sondern zeige, wie Du jetzt bist.

Wie oft muss ich es noch sagen: Programmiert sauber!

In einer Firma lief unter Excel 2007 ein Programm mit folgendem Code:

Dim BlattName

Sheets(„Diagramme“).Visible = True
Sheets(„nocheins“).Visible = True

BlattName = ActiveSheet.Name

Abgesehen davon dass die Variable „BlattName“ nicht sauber von Typ As String deklariert wurde, dass die Eigenschaft Visible eigentlich den Wert der Konstanten xlSheetVisible erhalten sollte und nicht True (True ist 1; xlSheetVisible ist -1) läuft es unter Excel 2016 an die Wand. Der Grund:

Nach Sheets(„Diagramme“).Visible = True ist der Fokus noch auf dem Blatt von dem aus der Code gestartet wurde. Werden jedoch zwei Blätter eingeblendet, wechselt Excel in der Version 2016 nun auf eines der eingeblendeten Blätter. Der Blattname lautet nun nicht mehr wird das ursprünglich aktive Blatt, sondern wie eines der Blätter, die zuvor ausgeblendet waren.

Das kann man gut mit einem Meldungsfenster verifizieren:

Sheets(„Diagramme“).Visible = True
Sheets(„nocheins“).Visible = True

BlattName = ActiveSheet.Name

MsgBox BlattName

Sauber programmieren heißt beispielsweise:

Dim BlattName As String
Dim xlBlattDiagramme As Worksheet
Dim xlBlattNochEins As Worksheet
Dim xlBlattAktual As Worksheet

Set xlBlattAktual = ActiveSheet
Set xlBlattDiagramme = Sheets(„Diagramme“)
Set xlBlattNochEins = Sheets(„nocheins“)

xlBlattDiagramme.Visible = xlSheetVisible
xlBlattNochEins.Visible = xlSheetVisible

BlattName = xlBlattAktual.Name
‚ wird eigentlich nicht mehr benötigt

MsgBox BlattName

 

Ich würde dich gerne einmal treffen, aber ich werfe immer daneben.

Ärgerlich. Wirklich sehr ärgerlich.

Ein Mitarbeiter einer Firma erstellt in Excel 2007 eine Liste mit Bildern und Dropdownlisten, die über eine Datenüberprüfung gefüllt werden. Über 60 Namen „suchen“ mit der Funktion INDIREKT den Wert der Dropdownliste in einem anderen Bereich und geben die Zelle zurück, in der sich ein Bild befindet.

Diese Namen werden auf die Bilder angewendet: über die Bearbeitungszeile wird der Name mit bspw. =Bild24 zugewiesen.

Das Problem: Es funktioniert prima in Excel 2007. Öffnet man diese Datei jedoch in Excel 2010 oder höher, sind die Verweise weg. Alle! Das heißt: noch einmal die 60 Verweise setzen.

Wirklich ärgerlich!

Leider keine Dynamik mehr …

Vorhin war’s noch da

Ich schaue mir eine fremde Datei an. Auf einem Tabellenblatt befindet sich ein Diagramm. Ich versuche herauszufinden, woher es die Daten bezieht. Die Quelle liegt auf einem anderen Blatt. Ich will zurück zu dem Blatt „Diagramm“ – doch das ist verschwunden.

Ich brauche schon eine Weile, bis ich dahiner komme:

In VBA befindet sich im Objekt Worksheet beim Ergebnis Deactivate folgender Befehl:

Private Sub Worksheet_Deactivate()
Sheets(„Diagramm“).Visible = xlSheetHidden
End Sub

Interessanter Mechanismus: Lieber Anwender, sobald du das Blatt verlässt, bekommst du es nicht mehr zu Gesicht. Natürlich kann man es sich über eine Schaltfläche wieder einblenden lassen …

Muss das immer einfach sein?

Heute in der Excel-VBA-Schulung.

Wir erstellen eine Dialog, in dem die Eingaben überprüft werden. Ich schaue mir die Lösungen der Teilnehmer an. Das Ergebnis funktioniert:

Die Lösung der Teilnehmerin:

[…]

ElseIf Me.txtGeld.Value >= 1 = False Then

[…]

Ich stutze über die Folge. Warum funktioniert sie trotzdem?

Die Antwort: der erste Teil wird ausgewertet: Ist 0,75 >= 1 -> nein.

Dann der zweite Teil: ist WAHR = FALSE? – nein.

Error 404

Sehr perfide!. Wenn man zwei Zeilen (oder Spalten) mit der Maus zusammenschieben, kann man sie mit der Maus wieder „auseinanderschieben“, das heißt sichtbar machen, das heißt: einblenden. Man kann sie aber auch über den Befehl „einblenden“ (im Kontextmenü der Zeilenköpfe oder über Start / Zellen / Format / Ausblenden & Einblenden / Zeilen einblenden wieder sichtbar machen.

Hat man sie jedoch nicht ganz zusammen geschoben, werden sie nicht mehr eingeblendet. Oder genauer: Beträgt die Zeilenhöhe 0,1 oder mehr, ist „einblenden“ zwecklos, da die Zeile für Excel nicht ausgeblendet ist.

Was passiert, wenn man sich zweimal halbtot gelacht hat?

Ich musste eine Weile suchen. Obwohl in der Zelle eine Formel (und eine bedingte Formatierung) war, habe ich den Ergebniswert der Formel nicht gesehen.

Nein – die Schriftfarbe der Zelle war nicht weiß; es lag kein Zahlenformat unter den Zellen. Dann habe ich es entdeckt: Im Dialogfeld der bedingten Formatierung befindet sich eine Option „nur Symbol anzeigen“. Und die war aktiviert.

Gibts da auch was von Ratiofarm?

Heute hat man mich gezwungen einen Excelkurs für Mac-User zu geben. Ich gestehe – ich habe einige Male daneben gegriffen – die cmd-Taste und die Strg-Taste verwechselt. Einige Mal „Steuerung“ statt „Controll“gesagt und natürlich auch einige Kleinigkeiten durcheinander gebracht. Zwei oder drei Mal musste ich sogar suchen – wo die Excel-Befehle denn auf dem Mac versteckt sind … Aber ich habe sie gefunden.

Nicht gefunden habe ich allerdings Folgendes:

  • Warum funktioniert bei einigen Rechnern (Excel 2016 Mac) die Tastenkombination [Shift] + [Ctrl] + [;], beziehungsweise [Shift] + [cmd] + [;] (aktuelles Datum) nicht?
  • Warum lassen sich keine Objekte (PDF, Word-Dokumente) einbetten?
  • Wenn ich über Seitenlayout / Seite einrichten / die Optionen „Kommentare am Ende des Blattes“ aktiviere (Register „Blatt“) – warum werden sie dann nicht ausgedruckt?

Warum haben 24-Stunden-Tankstellen Schlösser an den Türen?

Christian ist genervt. Er hat eine Tabelle mit Werten aus denen er ein Kreisdiagramm erzeugt. Christian lässt sich die Datenbeschriftungen anzeigen – die Werte mit 0 werden auch im Diagramm angezeigt. Eigentlich sind sie obsolet.

Ein manuelles Löschen bringt auch nicht viel, denn wenn die Werte sich ändern, würde nun nichts mehr angezeigt werden. Wir benötigen also eine Art „bedingte Anzeige“ – bei 0 wild keine Beschriftung angezeigt; bei jedem anderen Wert schon.

Christian findet die Lösung: #NV

Man muss die Daten etwas modifizieren – statt der Zahl 0 wird nun #NV angezeigt. Denn – dieser Wert wird übergangen.

Und jetzt ist Christian nicht mehr genervt.

Die Überschrift des Diagrammtitels kann man übrigens aus dem Textfeld mit

= [Klick auf die Zelle]

verknüpfen.

Danke, Christian, für diesen guten Tipp mit #NV.

 

Wenn du aufhörst, über mich Lügen zu erzählen, werde ich aufhören, über dich Wahrheiten zu verbreiten.

Schon wieder bin ich erstaunt. Ein Kunde möchte, dass seine Pivottabelle, die ich mit VBA erzeuge, kursiv formatiert wird. Da ich keine Ahnung habe, wie der Befehl lautet ein Pivottabellenformat für das Stripset zu ändern, greife ich auf den Makrorekorder zurück. Er zeichnet auf:

Sub Kursiv()
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1). _
Clear
ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1). _
Font.FontStyle = „Kursiv“
With ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements( _
xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.799981688894314
End With

[…]

Ich bin verblüfft: der Befehl

.Font.FontStyle = „Kursiv“

sieht nicht sehr sprach- und länderunabhängig aus. Ich teste – es läuft. Ich ändere die Zeile in die mir bekannte Eigenschaft Italic:

ActiveWorkbook.TableStyles(„contoso-Stil“).TableStyleElements(xlColumnStripe1).Font.Italic = True

Und: es läuft noch immer! Dieser Befehl gefällt mir besser. Makrorekorder – warum kannst du das nicht selbst?

Warum heißt es Gebet, wenn ich mit Gott rede, aber Psychose, wenn Gott mit mir spricht?

Hallo René,

ich habe eben eine Datei direkt über Excel als Anhang an eine Outlook Email verschickt. Symbol „Email“ aus Schnellleiste.

Dann habe ich einen Text in der Mail geschrieben und die Mail verschickt.

Nun traten zwei Phänomene auf: Die Email wurde nicht in „gesendete Objekte“ gespeichert (ist auch über Suchfunktion nirgens, auch nicht im Papierkorb) und (noch ärgerlicher), der Text der Email wurde nicht mit übermittelt.

Kennst Du das?

#####

Hallo Bettina,

das ist lustig – ich habe heute von einer großen Firma hier in München (Deutsches Patentamt) eine Mail erhalten, in der sich fragen, was da passiert. Habe es vorhin bei getestet – bei mir klappt alles: Mail ist in den „gesendeten Objekten“ und Anhang ist dran.

Ich verwende IMAP. Kann es damit zusammenhängen? Vielleicht hat der Exchange-Server / POP3 Schwierigkeiten?

Ich bleibe dran – ist wohl eher ein Outlook-Problem, denn ein Excel-Phänomen

Liebe Grüße

Rene

####

 

Many people are alive only because it’s illegal to shoot them.

Excel kann schon ganz schön nerven!

Gestern in der Excel-VBA-Schulung. Wir erstellen ein (komplexes) Makro, das eine andere Datei öffnet und dann weitere Befehle ausführt. Beispielsweise so:

Sub TastenkombiTest()
Workbooks.Open „D:\Eigene Dateien\Excel\Asterix.xlsx“
MsgBox „Excel kann schon ganz schön nerven!“
End Sub

In Excel weisen wir dem Makro über Entwicklertools / Code / Makros / Optionen eine Tastenkombination zu. Erster Versuch: das Makro wird auf [Strg] + [Shift] + [i] gelegt. Das Ergebnis: Das Makro bricht nach dem Öffnen der Datei ab.

Zweiter Versuch: das Makro wird auf [Strg] + [i] gelegt. Es klappt!

Muss ich das verstehen?

Wenn Sie es schon nichts vom Stand der Wissenschaft sagen können dann glänzen Sie doch wenigstens mit populären Vorurteilen!

Hallo Herr Martin,

Da wir aktuell des Öfteren Probleme mit der Performance von Excel haben und sich unsere IT-Abteilung damit leider weniger im Detail auskennt wende ich mich an Sie. Vielleicht können Sie uns in folgendem Fall weiterhelfen: im Anhang habe ich eine beispielhafte Datei durch die bei uns beispielsweise beim Filtern sofort alle Prozessoren ausgelastet sind. Die Datei hat weniger als 2 MB und enthält einige Formeln. Können Sie einschätzen, ob es nur an den Formeln liegt oder eventuell an technischen Umständen? Lastet es bei Ihnen ebenfalls gleich alles aus? Wir haben dieses Problem häufiger im Controlling, obwohl die Dateien selten besonders groß sind.

Es wäre super, wenn Sie sich das ganze mal ansehen und mir Ihre Einschätzung dazu mitteilen könnten.
Mit freundlichen Grüßen

####

Hallo Frau Kopov,

Jep – beim Öffnen benötigt die Datei zu viele Ressourcen.

Ich habe mir das Teilchen angesehen. Kennen Sie den Inquire? Ich glaube, wir haben das Add-In in der Schulung nicht besprochen. Sie finden es unter Datei / Optionen / Add-Ins COM-Add-Ins und dort Inquire. Damit kann man Dateien analysieren.

Erstaunlicherweise weigert sich Inquire – irgendetwas scheint an der Datei korrupt zu sein.

Kennen Sie die Tools von Charles Williams: http://decisionmodels.com/index.htm

Damit habe ich versucht Ihre Datei zu putzen. Leider verrät er mir nicht, was los war/ist.

Erstes Ergebnis: Er scheint schneller zu sein.

Spannende Sache – ich werde sie mir am Wochenende mal genauer ansehen.

Nieder mit der Frühjahrsmüdigkeit! Es lebe der Winterschlaf!

Heute in der Excel-VBA-Schulung. Ein Teilnehmer sagt mir, dass er ein Programm nicht starten kann. Er erhält eine Meldung, dass er „Else ohne If“ geschrieben hätte.

Ich erwiderte: nun – er habe wohl If vergessen. Oder falsch geschrieben. Er verneinte. Ich schaute mit den Code an:

Für With fehlt das Gegenstück End With. Nicht ganz korrekt die Meldung … sie taucht so an mehreren Stellen auf. Auch in folgenden Beispiel:

Dim i As Integer
For i = 1 To 10
If i > 9 Then
MsgBox „ziemlich groß“
Next

Hier lautet die Fehlermeldung: „Next ohne For“.

Echte Freunde hören nicht nur, was du sagst, sondern verstehen auch, was du meinst.

Letzte Woche in der libreOffice-Calc-Schulung.

Wir üben die Technik des Ziehens und erstellen einen Kalender.

Ein Teilnehmer sagt, dass es bei ihm nicht funktioniere. Ich ahne, was er falsch gemacht hat.

Richtig: er hat nicht „Montag“ in die Zelle eingetragen, sondern „Montag “ – also mit einem Leerzeichen hinter dem Wochentag.

Schnell nachgeschaut: Excel übergeht freundlicherweise diesen Eingabefehler. Und schreibt die weiteren Wochentage ohne Leerzeichen in die Tabelle. Ist hat doch besser als Calc.

och nö!

Man zwingt mich immer noch Calc zu unterrichten.

Und es ist so gruselig schlecht – Bugs in der bedingten Formatierung, in den Zellformatvorlagen (die sich nicht mehr ändern lassen), in der Darstellung; unlogischer Aufbau, verwirrende Bedienerführung. Ein Klick auf einen Hyperlink öffnet die Datei und das Programm (Writer, pdf, Browser, …) im Hintergrund. Die Liste lässt sich fortsetzen.

Ein amüsanter Fehler: wenn eine Zahl mit 0,00 km (statt mit 0,00 „km“) formatiert wird, erlaubt Calc das, zeigt aber Unfug an:

Kann ich so nicht beurteilen. Müsste ich nackt sehen.

Man zwingt mich erneut Calc zu unterrichten. Und ich entdecke wieder ganz viele schreckliche Dinge – viel schlimmer als man es sich in Excel jemals vorstellen kann.

Ich zeige den Teilnehmerinnen und Teilnehmern, dass man mit einem Minuszeichen bequem auf der rechten Zahlentastatur ein Datum eingeben kann, also:

20-11-17

Die erste Stolperfalle – man darf nicht

20-11-2017

eintragen – dies wird nicht als Datum erkannt!

Ein Teilnehmer versucht es mit seinem Geburtsdatum – padautz: es funktioniert nicht. Und tatsächlich: libreOffice Calc akzeptiert diese Schreibweise nur bei Datumsangaben zwischen 2000 und 2031.

Wegen Rückrufaktion vorübergehend aus dem Sortiment genommen

Eigentlich hätte ich es wissen können.

Vor einigen Jahren rief mich ein Teilnehmer in einer VBA-Schulung, weil sein Code nicht funktionierte:

Ich habe lange gesucht, bis ich verstanden hatte, dass er die Variable intSpaltenAnzahl deklariert hat, dagegen der Variablen intSpaltenAnzah1 einen Wert zuweist. Zugegeben: bei einer anderen Schrift als der Courier sieht man das besser.

Gestern beim Programmieren zeichnet der Makrorekorder folgenden Code auf:

ActiveWorkbook.TableStyles.Add („RenesVorlage“)
With ActiveWorkbook.TableStyles(„RenesVorlage“)
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = False
.ShowAsAvailableTimelineStyle = False
End With
With ActiveWorkbook.TableStyles(„RenesVorlage“).TableStyleElements(xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.399945066682943
End With
ActiveSheet.PivotTables(„PivotTable1“).ShowTableStyleColumnStripes = True

Und ich bin ja wieder reingefallen – es heißt xlColumnStripe1 und nicht xlColumnStripel.

Obwohl?! – Stripel klingt auch lustig.

Being funny is not everyone’s first choice

Auf nichts ist mehr Verlass!

Für einen Kunde habe ich ein Add-In für Excel erstellt. Am Ende werden daten in einer Pivottabelle zusammengefasst. Der Kunde möchte noch ein bisschen Farbe ins Spiel bringen. Kein Problem, denke ich – in Pivottabellen lustige Formatierungen einschalten kann man mittels der Pivottable-Formate erreichen. Doch wie heißen sie in Excel? Der Makrorekorder hilft. Ich zeichne die Farbe auf:

ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1). _
Clear
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399914548173467
End With

Wunderbar!

Und Linien? Ich zeichne auf:

With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeTop)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeBottom)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeLeft)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With
With ActiveWorkbook.TableStyles(„Rene-Stil“).TableStyleElements( _
xlColumnStripe1).Borders(xlEdgeRight)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
.LineStyle = xlNone
End With

[…]

Ich teste – lasse den Code abspielen. Was passiert? Nichts! Teste erneut, …

Ich habe eine Weile benötigt, bis ich gesehen hat, dass der Makrorekorder eine dünne schwarze Linie mit der Eigenschaft LineStyle = xlNone aufzeichnet hat ?!? Also: raus damit. Code noch „putzen“ – und schon läuft es!

xlDateiNeu.TableStyles.Add „Rene-Stil“

With xlDateiNeu.TableStyles(„Rene-Stil“)
.ShowAsAvailablePivotTableStyle = True
.ShowAsAvailableTableStyle = False
.ShowAsAvailableSlicerStyle = False
.ShowAsAvailableTimelineStyle = False
End With

xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).StripeSize = 2
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Interior
.ThemeColor = xlThemeColorAccent4
.TintAndShade = 0.399945066682943
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeTop)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeBottom)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeLeft)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With
With xlDateiNeu.TableStyles(„Rene-Stil“).TableStyleElements(xlColumnStripe1).Borders(xlEdgeRight)
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
‚.LineStyle = xlNone
End With

Mit leerem Hirn spricht man nicht!

In den letzten drei Tagen habe ich libreOffice Calc unterrichtet. Die Landeshauptstadt München setzt die – nicht mehr aktuelle – Version 5.1 ein. Nach drei Tagen Calc weiß man Excel zu schätzen.

Wenn man ein Tabellenblatt schützt, kann man immer noch einen Doppelklick in eine geschützte Zelle machen. Aber natürlich nicht mehr ändern. Im Funktionsassistenten funktioniert die Tastenkombination [Shift] + [F4] zum Umwandeln in einen absoluten Bezug nicht.

Die Liste könnte endlos fortgesetzt werden. Amüsiert hat mich Folgendes. In einen Text wird über Einfügen / Sonderzeichen aus der Schriftart Symbol das Summensymbol (Σ):

Nach Bestätigen der Eingabe sieht die Zelle folgendermaßen aus:

Ein Trost: in der aktuellen Version 5.2 ist dieser Bug behoben. Das nützt den Mitarbeiterinnen und Mitarbeitern der Landeshauptstadt Münchens allerdings nichts…

Ich gehe absichtlich nicht ins Fitnessstudio – ich möchte nicht so aussehen, als könnte ich irgendwem beim Umzug helfen !

Es ist schon verblüffend: In einer Datei stehen Uhrzeiten. Sie werden mit dem benutzerdefinierten Format „mm“ formatiert.

Speichert man die Datei, schließt sie und öffnet sie erhält man andere Angaben:

Ein Blick in das Format verrät, dass aus mm MM wurde:

Die Lösung: man muss sie mit [mm]. Dann übersteht dieses Format auch das Speichern:

Danke an meine Kollegin Angelika Meyer für die Frage und an meinen Kollegen Stefan Lau.

Und ich habe mich nicht an die Spitze der Nahrungskette hochgearbeitet, um Vegetarier zu werden.

Hübsche Fehlermeldung: „Zu wenige Zeilenfortsetzungen“.

Die Ursache: Mit dem Makrorekorder wird der Befehl Datei / Öffnen (eine Textdatei) aufgezeichnet. Da die Textdatei zirka 200 Spalten hat, kann dies nicht in einem Array gespeichert werden, der in diesem Makro intern verwendet wird:

Tötet es bevor es Eier legt

Heute in der VBA-Schulung.

Da Excel keine Funktion besitzt, um einen Text „rumzudrehen“, verwenden wir die VBA-Funktion StrReverse:

Function RC(Text As String) As String
RC = StrReverse(Text)
End Function

Sie wird in Excel angezeigt und kann verwendent werden:

Padautz – vielleicht doch nicht. Möglicherweise ist der Name RC schon vergeben – RowColumn? Ich weiß es nicht. Ein Umbenennen in beispielsweise ReCo hilft.

Mein Name ist Hase! – Falsch! – Nein, mein Name ist nicht Falsch

Lustiger Fehler in der VBA-Schulung. Finden Sie ihn?

Sub Eingabe()
Dim Geschlecht As String
Dim Benutzername As String

Geschlecht = InputBox(„Bitte Geschlecht angeben – bitte m oder w!“)
If Geschlecht = „m“ Or Geschlecht = „M“ Then
Benutzername = InputBox(„Bitte sag deinen Namen!“, „TF“)
MsgBox „Hallo lieber “ = Benutzername
ElseIf LCase(Geschlecht) = „w“ Then
Benutzername = InputBox(„Bitte sag deinen Namen!“, „TF“)
MsgBox „Hallo liebe “ = Benutzername
Else
MsgBox „Falsche Eingabe!“
End If

End Sub

Das Ergebnis:

Ein Text ist ein Text ist ein Text

Was passieren kann, wenn man einen Text als Text formatiert – darauf habe ich schon hingewiesen. Auch was passiert, wenn man einen (langen) Text als Buchhaltung formatiert:

Amüsant ist dagegen auch folgendes Phänomen: Wenn man einen Text als Datum, Prozentwert oder Währung formatiert:

und dann mit der Funktion LÄNGE weiter rechnet – allerdings mit mindestens zwei Rechenoperationen (beispielsweise LÄNGE – 1 oder LÄNGE x 1), dann wird das Zahlenformat übernommen:

Erstaunlicherweise: FINDEN und SUCHEN liefern auch Zahlen – sie übernehmen jedoch nicht das Zahlenformat.

Ich wär auch lieber reich als sexy – aber was soll ich machen….?I

Eine Funktion liefert einen Wert.

Schnell entdeckt man jedoch, dass auch Formatierungen übernommen werden. Meistens korrekt:

Steht in A1 eine Zahl, die als Währung oder Buchhaltung formatiert ist, dann wird eine Berechnung

=A1*19% als Währung oder Buchhaltung formatiert.

Stehen in A1 und A2 Datumsangaben, dann liefert die Differenz eine Zahl und kein Datum. Wird jedoch

=A1+30 berechnet, so ist das Ergebnis als Datum formatiert.  Die Summe, Mittelwert, Max und Min von mit Währung oder Buchhaltung formatierten Zellen, werden korrekt wieder in diesem Format angezeigt. So weit, so gut.

Allerdings liefert

=WENN(WOCHENTAG(A1;2)>5;A1+3;A1+1)

leider kein Datum, sondern eine Zahl.

(Erklärung: Ermittle zu einem Datum den nächsten Arbeitstag)

Und schließlich: Steht in A1 ein Text, beispielsweise „Excel nervt“. Ist diese Zelle als Text formatiert (okay – nicht nötig; aber stört eigentlich nicht), so liefert:

=LÄNGE(A1)

die Zahl 11.

Jedoch liefert:

=LÄNGE(A1)*1

den Text „11“ (linksbündig). Editiert man die Zelle, so steht die Formel nun als Formel in der Zelle. Irgendwie doof …

Ich weiß, du hast recht – aber meine Meinung gefällt mir besser.

Hallo Herr Martin,

ich befinde mich derzeit in einem Excel-Grundkurs und bin bei einer „verschachtelten Wenn-Funktion“ auf folgende Problematik gestoßen:
Excel rechnet nicht weiter, sobald der erste „Wahr“wert erreicht ist, was zu logischen Fehlern führt, sofern die Abfragewerte aufsteigend abgefragt werden.
Der „Sonst“Wert wird allerdings korrekt ausgegeben.
In meinem Beispiel habe ich dann die Abfragewerte 25% und 20% umgekehrt und es hat wie geplant funktioniert.
Wo liegt mein Fehler?
Haben Sie eventuell eine Idee zur Vereinfachung der Funktion?
Im voraus vielen Dank für Ihre Hilfe.
Mit freundlichen Grüßen
Jan

Hallo Herr S.,

Sie haben recht: Excel arbeitet Wenn-Funktionen baumartig ab. Also:

Wenn > 80% dann

wenn > 75% dann

sonst: Rest <= 75%

Sie können es aber auch „umbauen“:

wenn <= 75% dann

wenn <= 80% dann

Rest: sonst > 80%

Tipp: ich zeichne manchmal so eine Baumstruktur auf, um es besser zu verstehen.

Tipp 2: schreiben Sie 2 und 5 und nicht „2“ oder „5“. Sie möchten ja mit dem Zahle weiterrechnen. Sie können übrigens auch 2% und 5% verwenden

Hallo Herr Martin,

vielen dank für die schnelle Antwort.

Das ganze ist ja ganz schön verwirrend, gibt es dann noch eine andere Funktion die das Ziel einfacher erreicht?

Schöne Grüße

Jan

Hallo Herr S.

schauen Sie sich einmal die Funktion SVWEIS an – dort werden mehrere Fälle auf einer Ebene abgearbeitet.

Alles, was ich will ist teuer, macht dick oder antwortet mir nicht

In der letzten Excel-Schulung habe ich die (intelligenten) Tabellen vorgestellt. Ich zeige, dass die Spaltenköpfe A, B, C, … durch die Überschriftszeile ersetzt werden:

Ein Teilnehmer fragt mich, warum es bei ihm nicht funktioniere.

Die Antwort ist schnell gefunden: er hatte eine Fixierung eingeschaltet.

Wenn Superkleber überall klebt, warum dann nicht an der Innenseite der Tube?

Heute in der Excel-Schulung haben wir folgendes interessante Phänomen festgestellt:

In einer (intelligenten) Tabelle befindet sich eine Reihe mit Datumsangaben, die nicht fortlaufend vorhanden sind. Auf der Reihe und den zugehörigen Werten (beispielsweise Geldbeträgen) wird nun ein Diagramm aufgesetzt. Das Ergebnis verblüfft: die Abstände der Datumsangaben werden gemäß ihrem Datumswert auf der Achse abgetragen.

Und was, wenn ich das nicht will?

Nun – Excel interpretiert bei intelligenten Tabellen die „automatische Auswahl basierend auf den Daten“ gemäß der Datumswerte. Kann man umschalten in „Textachse“.

Bitte bevorraten Sie sich, dieser Artikel ist nicht ständig im Sortiment…

Wer in Excel gerne mit Namen arbeitet, weiß die Funktionstaste [F3] zu schätzen. Man kann den Dialog, der die Namen anzeigt aus der bedingten Formatierung, der Datenüberprüfung, den Diagrammen, … heraus aufrufen.

Leider nicht aus den Steuerelementen heraus, wenn dort im Formatierungsdialog ein Wert in eine Zelle geschrieben wird, die einen Namen hat.

F3 geht leider nicht

Danke an Tony de Jonker für diesen Hinweis

Wer hatte bloß die Idee, ein S in das Wort „lispeln“ zu stecken?

Ich erstelle eine PivotTabelle und möchte dort die Werte zusammenfassen.

Warum hat mein Kollege am Ende der Liste der Funktionen die Funktion „Diskrete Anzahl“ – ich dagegen nicht:

seins

meins

Die Antwort: die letzte Funktion „Diskrete Anzahl“ erscheint nur, wenn Sie beim erstellen der Pivottabelle „den Datenmodell diese Daten hinzufügen“ aktivieren.

geht doch!

Ein Dankeschön an Christian für diesen Hinweis.

Warum ist das Ansetzen des Rotstifts eine so bewährte Methode, wieder schwarze Zahlen zu schreiben?

Inquire ist schon klasse. Er analysiert Tabellen und findet eine Menge „verborgener“ Inhalte: ausgeblendete Zeilen, weiße Schriftfarbe, … Allerdings: zwei Dinge findet dieses Analysewerkzeug nicht:

1.) Wenn eine Form auf einer Zelle liegt (hier etwas versetzt, damit man sie besser sehen kann)

2.) Wenn eine Zelle (hier E4) benutzerdefiniert mit

;;;

formatiert ist. Der leere Wert vor dem ersten Semikolon gibt an: positive Zahlen werden nicht dargestellt; der zweite legt das Gleiche für negative fest. Der dritte für 0.

Kritik zur Kenntnis genommen. Ignoriervorgang eingeleitet.

Von Version zu Version werden die Dinge ein klein wenig anderes beschriftet. Über den Sinn könnte man reflektieren.

Jedoch: warum beschriftet Microsoft nicht ALLE Stellen? Konsequent? Gleich?

So laufen noch immer „Datenüberprüfung“ und „Gültigkeit“ (im Dialog Inhalte einfügen) rum, der „Textumbruch“ heißt noch immer „Zeilenumbruch“ (im Dialog „Zelle formatieren / Ausrichtung“) und auf „Link“ statt „Hyperlink“ hat man sich auch noch nicht ganz festlegen können …

Eigentlich bin ich ganz anders, nur komme ich so selten dazu

In einer Liste befindet sich in einer Spalte Vorname und Zuname. Daneben steht in einer Spalte die Email-Adresse. Was macht die Blitzvorschau, wenn sie nun die Möglichkeit hätte, den Vornamen sowohl aus der Namesspalte als auch aus der Mailspalte herauszuholen?

Die Antwort: der gefundene Name wird aus der ersten Spalte herausgeholt – ohne Hinweis, Warnung oder Fehlermeldung. Das heißt: Bei der Verwendung der Assistenten: Genau hinschauen!

Betrunkene sagen die Wahrheit. Blöd, das sie immer so nuscheln.

Hallo Rene,

bitte wieder Tipparbeit bei der schon bekannten Tabelle.

Durch die Hinweise auf VBA in manchen Vorträgen habe ich mir einige der Schulungen angesehen.
Ach oh je 🙁
Es ist toll, dass man sich nachdem das Programm feriggestellt ist arbeit erspart. Die Suche im Internet nach geeigneten Code hat natürlich nicht zu einem Ergebnis geführt.
Dazu braucht man ein erweitertes Grundwissen das ich nicht habe.

Meine Bitte und Anliegen:
Kannst Du mir bitte VBA Code für die Tabelle mit der Datumskorrektur schreiben?

Das alte Datum soll ausgeblendet werden.
Das einfügen der Spalte für das neu formatierte Datum muss variable sein. Es kommt neuerdings auch eine Tabelle mit Point vor dem Datum.

Bitte ein alphabethisches Inhaltsverzeichnis mit Link auf die aktuellen Tabellen.

Zu VBA
Kannst Du mir ein Buch empfehlen?
Kann man in den Büchern, ähnlich wie in denen von Excel, bestimmte Code für Funktionen (kopieren, einfügen, Formeln in VBA) finden?

Im Internet habe ich für das Einfügen einer Spalte Code entdeckt. Wollte aber eine variable Möglichkeit, die ich nicht gefunden habe. Mitbekommen habe ich, dass man Variable definieren muss. Wie diese jedoch bezeichnet werden habe ich nicht herausgefunden. Die Zellen werden in Ziffern aufgeteilt. Buchstaben und Zahlen für die Bezeichnung der Zellen habe ich in anderen Code entdeckt. Erwähnt wurde auch der Unterschied zwischen USA und dem localen Code.

Mit besten Grüßen
Peter

#####

Hallo Peter,

hier der Code:

Sub MonateErsetzen()
Dim strSpalte As String
Dim i As Integer
Dim strMonateDeutsch
Dim strMonateEnglisch

strMonateDeutsch = Array(„Jänner“, „Februar“, „März“, „April“, „Mai“, „Juni“, „Juli“, „August“, „September“, „Oktober“, „November“, „Dezember“)
strMonateEnglisch = Array(„January“, „February“, „March“, „April“,
„May“, „June“, „July“, „August“, „September“, „October“, „November“,
„December“)

strSpalte = InputBox(„In welcher Spalte sollen die Datumswerte ersetzt werden?“)

For i = 0 To UBound(strMonateDeutsch)
ActiveSheet.Columns(strSpalte & „:“ & strSpalte).Replace What:=strMonateEnglisch(i), Replacement:=strMonateDeutsch(i), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next i

End Sub

Wie kommt man dazu? Zeichen mit dem Makrorekorder auf – beispielsweise ersetze „January“ durch „Januar“. Dann erhältst du folgenden Code:

Sub Makro1()

‚ Makro1 Makro


Selection.Replace What:=“February“, Replacement:=“Februar“, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Nun – und da bauen wir ein bisschen Makro außenrum.

Ich empfehle keine Bücher. Jeder hat eine andere Art zu lesen und zu lernen.
Geh in eine große Buchhandlung, hole einen Meter VBA-Literatur raus, blättere sie durch und bestelle dann das Buch, das dir am besten gefallen hat, in einer kleinen Buchhandlung.

Tipp: ich würde einen Kurs besuchen (Volkshochschule oder privater Anbieter). Dort bekommst du in kurzer Zeit am schnellsten die wichtigste Informationen. Dozent zeigt auch, was man falsch machen kann und hilft.
Liebe Grüße

Rene

#######

Hallo Rene,

Danke.

Leider hilft mir dieser Code nicht weiter nachdem nur der Monatsname in deutscher Schreibweise umsetzt wird. Benötigt wird ein ‚echtes‘ Datum.

In der Spalte C steht einmal ein echtes Datum und dann das Datum als Text.

In der neuen Spalte D die eingefügt wurde, hast du mit der untenstehenden Formel ein ‚echtes‘ Datum erstellt.

=WENN(ISTZAHL(C2);C2;DATUM(RECHTS(C2;4);VERGLEICH(TEIL(C2;SUCHEN(“ „;C2)+1;LÄNGE(C2)-8);{„January“;“February“;“March“;“April“;“May“;“June“;“July“;“August“;“September“;“October“;“November“;“December“};0);LINKS(C2;2)))

erstellt.

 

Bitte im VBA Code umsetzen, dass eine neue Spalte erzeugt wird, das Datum nach deiner Formel eingesetzt wird und die Spalte mit den nicht brauchbaren Angaben ausgeblendet wird.

Deine Vorschläge werde ich befolgen. Schau mal in die Buchhandlung was dort aufliegt. Ein Kurs ist auch eine gute Idee.

Besten Dank,

Peter

#####

Hallo Peter,

probiere es aus!

Durch das Ersetzen „erkennt“ Excel ein Datum und wandelt in eine Datumszahl um

LG ::  Rene

Meine aufregendsten Tage waren meistens Nächte.

Hallo Rene,

ich hatte Ihnen vor geraumer Zeit Kontakt zu Ihnen aufgenommen, weil ich ein Phänomen mit einer Excel-Datei hatte – das ist immer noch nicht gelöst ☹

Aber darum geht es jetzt nicht …

Seit geraumer Zeit kann ich in Excel (auch in Word) mittels Alt+F11 den VBA-Editor nicht mehr starten – es passiert einfach gar nichts.

Die Tasten funktionieren, das habe ich überprüft.

Ich bin ziemlich ratlos.

Vielleicht haben Sie in Ihren Expertenrunden mal davon gehört. Vielen Dank schon mal 😊

Hallo Matthias,

 

nein – natürlich nicht amüsant. DAS Phänomen kenne ich nicht – ich werde es mal bei unserem nächsten Excel-Stammtisch in die Runde werfen, aber ich fürchte, ich weiß schon die Antwort.

Gegenfragen:

* Was zeigt Word unter Datei / Optionen / Menüband anpassen / Tastenkombinationen anpassen: an, wenn Sie dort im Textfeld die Tastenkombination Alt + F11 eingeben? Bei mir steht bei „derzeit zugewiesen“: AnsichtVBCode.

* Was passiert wenn Sie in Excel das Makro Test laufen lassen und dann in Excel die Tastenkombi Alt + F11 drücken?

 

Sub Test()

Application.OnKey „%{F11}“, „Beispiel“

End Sub

 

Sub Beispiel()

MsgBox „Ich bin die neue Taste“

End Sub

 

Und was passiert, wenn Sie in Excel in Datei / Optionen / Trustcenter / Einstellungen für das Trustcenter / Makroeinstellungen dem Zugriff auf das VBA-Projektobjektmodell vertrauen. Und dann das folgende Test-Makro starten:

 

Sub Test()

Application.OnKey „%{F11}“, „Beispiel“

End Sub

 

Sub Beispiel()

Application.VBE.MainWindow.Visible = True End Sub

 

schöne Grüße

Rene

Hallo Rene,

vielen Dank für Ihre Antwort und Mühe. Ich habe Ihr Makro Test ausführen wollen und wunderte mich das einfach nichts passiert – da viel mir es plötzlich wie Schuppen von den Augen.

Als ich Alt+F8 betätigte kam eine Meldung der Software „Geforce Experience“ … diese Software wurde offensichtlich beim letzten von mir durchgeführten Treiber-Update aktiviert und die Funktion „spielerinternes Overlay“ aktiviert. Die hatte ich vor längerer Zeit deaktiviert und nicht weiter beachtet…

Das ist so ein Beispiel dafür, dass bestimmte Software meint besonders wichtig zu sein und am Nutzer vorbei „kleine Nützlichkeiten“ aktiviert ☹

Nochmal vielen Dank und vielleicht begegnet Ihnen ja diese Frage wiedermal – dann ist es geklärt.

Schöne Grüße zurück

Wenns nich im Regal steht hamwers nich

Auch hübsch. Wir erstellen eine intelligente Tabelle.

Tragen unterhalb einen neuen Datensatz ein:

bemerken, dass dieser Datensatz bereits vorhanden ist und drücken [Strg] + [-], um ihn zu löschen. Der Cursor bewegt sich an den Anfang der Tabelle. Ich drücke erneut [Strg] + [-] und stelle mit Entsetzen fest, dass Excel nicht die aktuelle Zeile, sondern die aktuelle Spalte ohne Nachfragen gelöscht hat:

Breakfast is the most important drink of the day!

Hallo Renè,

kannst du mir bitte wieder helfen?

Es dreht sich um das Datum.

Aus dem Internet habe ich diese Tabelle kopiert. Einmal wird ein richtiges Datum und dann ein Datum in Textformat angezeigt. Bei der Textform fehlt nach dem Tag der Punkt.

Die Formatierung soll natürlich österreichisch sein ;-).  Datum und Uhrzeit von dir haben mir bei dieser Tabelle auch nicht weiter geholfen. Zumindest habe ich nichts entdeckt.

Die Liste wird mit einer anderen verglichen um zu sehen ob der Start gemeldet wurde.

Bedanke mich für deine Unterstützung,

mit bestem Gruß

Peter

Hallo Peter,

hübsche Fingerübung. Kommst du mit der Lösung klar?

In Spalte E steht das Datum als Datum; in F als Text.

Übrigens: Hübsches Problem – da im Englischen April, August, September und November genauso heißen wie bei uns, machen diese Monate keine Probleme.

schöne Grüße aus dem Land, wo die Jänners Januare heißen

Rene

Die rechte Formel sieht wie folgt aus:

{=TEXT(WENN(ISTZAHL(D2);D2;DATUM(RECHTS(D2;4);VERGLEICH(TEIL(D2;SUCHEN(“ „;D2)+1;LÄNGE(D2)-8);{„January“;“February“;“March“;“April“;“May“;“June“;“July“;“August“;“September“;“October“;“November“;“December“};0);LINKS(D2;2)));“TT.MMM JJJJ“)}

die linke ohne die Funktion TEXT.

Excellent Days – Rabattaktion

Wir gehen in die Endrunde. Da noch einige Plätze bei unserer Veranstaltung frei sind, bieten wir den ersten fünf Interessierten diese Plätze bei einem Rabatt von 300 EUR an.
Wie?
Melden Sie sich auf unserer Seite
http://www.munich-office-group.de/

mit dem Promocode „compurem Promocode“ an und Sie erhalten den reduzierten Eintrittspreis von 898 EUR.

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.

Übrigens: Ich werde auch einen Vortrag über Bugs in Excel halten.

Tipp der Woche: Datenschnitt für mehrere Pivottabellen

Diesmal kein Rumnörgeln – ich kann auch anders:

Auf der Seite:
gibt es diese Woche wieder einen Tipp von mir – Rene Martin.
Nun ja – wer möchte – schaut einfach mal rein – dort gibt es bei video2brain in dieser Woche und in jeder weiteren Woche einen kostenlosen Tipp. Diese Woche:

Datenschnitt für mehrere Pivottabellen

Neulich hab ich es bei ParShip versucht: Leider ohne Erfolg. Die vermitteln nur Singles mit Niveau …

Natürlich kenne ich das Phänomen. Aber es verblüfft immer wieder.

Gegeben sei ein Liste mit einer Zahlenkolonne, in der sich ein Textformat druntergeschoben hat.

Um diesen Text zu entfernen (Smarttags sind leider nicht in Sicht), schreibe ich die Zahl 1 in eine Zelle, kopieren sie und füge sie über den Bereich mit „Inhalte einfügen / multiplizieren“ ein:

Ich weiß – es gibt noch andere Möglichkeiten – ich habe sie im Beitrag „SAP & co“ aufgelistet. Auf alle Fälle – ich bin glücklich:

Jedoch – aus irgend einem Grund (Macht der Gewohnheit?) drücke ich die Enter-Taste (zur Bestätigung?)

Klar – die noch im Zwischenspeicher befindliche Zahl wird in den markierten Bereich eingefügt … DAS wollte ich eigentlich nicht.

Der Hauptgrund für Stress ist der tägliche Kontakt mit Idioten.

Heute in der Excel-Schulung. Aus gegebenem Anlass – bald ist Bundestagswahl – erstellen wir ein Diagramm der aktuellen Sitzverteilung des deutschen Bundestages. Das Ergebnis soll folgendermaßen aussehen:

Zuerst werden die Daten eingetragen. Auf ihnen wird ein Ringdiagramm aufgesetzt:

Damit der untere Teil „frei“ bleiben kann, wird unter die Daten erneut die Summe eingegeben. Dieser teil soll „wegformatiert“ werden.

Der Ring wird gedreht; die Innenringgröße verkleinert:

Unglücklicherweise hat Excel die erste Spalte nicht als Beschriftung erkannt. Dies kann schnell über Entwurf / Daten / Daten auswählen geändert werden. Der horizontalen Achsenbeschriftung (sic!) wird die erste Spalte zugewiesen. Sie erscheint nun in der Legende:

Und nun das Verblüffende: Durch das erneute Zuweisen besteht zwar weiterhin eine Verknüpfung zu den Zellen, wenn der Text geändert wird. Jedoch: wenn die Formatierung der Ringsegmente geändert wird, wird dies in der Legende nicht mehr mitgenommen. Erstaunlich!

schade aber auch!

Übrigens: geht wählen!

Excellent days

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.

Übrigens: Ich werde auch einen Vortrag über Bugs in Excel halten.

Nur weil du nicht paranoid bist, heißt es noch lange nicht, dass sie hier nicht hinter dir her sind…

Einfach blöde. Ich habe einen Verweis von einer Excelmappe auf eine andere:

vorher …

Diese Mappe wird unter einem anderen Namen gespeichert. Der Verweis wird nun auf die neue Datei gesetzt; bleibt nicht mehr bei der alten:

… nachher

Leider stellt Excel keinen Schalter zur Verfügung, diesen Verweis NICHT mitzunehmen.

Wenn ein streunender Kater nach 12 Tagen wieder auftaucht – alle happy! Aber mach das mal als Mann.

Bisher hat es genervt. Kennen Sie das?

Eine Zahlenreihe. Daneben wird eine Formel eingefügt. Der Beginn des Formelbereichs ist die obere Zelle:

Die Formel wird mit der Tastenkombination [Shift] + [Strg] + [↓] nach unten „gezogen“.

Problem: nun möchte man „zurück“ zu der Zelle, in der die Formel eingetragen wurde. Die Lösung: Drücken Sie die Tastenkombination [Strg] + [Rück], also die Taste [⇐]. Und schon nervt es nicht mehr.

Danke an Christian für diesen Tipp.

Wenn die Leidenschaft zur Tür hereintritt, rettet sich die Vernunft durchs Fenster

Was mache ich falsch? Die Gruppe „Sortieren und filtern“ zeigt das Symbol „Löschen“ aktiv an. Ich habe einen Filter gesetzt.

Ich habe auch noch das Symbol in die Symbolleiste für den Schnellzugriff gezogen – dort ist es allerdings inaktiv:

Die Antwort: Richtig – Sie haben das Symbol „Filter löschen“ hinzugefügt. Wenn Sie sich das Symbol in dem Optionen ansehen, so sehen Sie den englischen Begriff „PivotClearFilters“.

Eben. Sie müssen das Symbol „Alle Filter löschen“ hinzufügen. Dann klappt es!

Danke an Bettina für den Hinweis.

 

Ich bin nicht perfekt. Aber trotzdem sehr gut gelungen.

Hallo Herr Martin,

nach meinem Urlaub komme ich nun endlich dazu diverse Dinge aus unserer Schulung umzusetzen. Wie es der Teufel will, komme ich an einer Stelle absolut nicht weiter.

Ich möchte ein dynamisches Diagramm erstellen. Dies funktioniert auch für die Werte darin (also die Linien) und für die Beschriftung sofern diese ein Datum oder eine Zahl ist. Ich habe nun aber häufiger den Fall, dass die Achsenbeschriftung ein Text ist. Das bekomme ich nicht hin! Es ergibt mir schon kein korrektes Ergebnis bei der Formel, wodurch das Diagramm natürlich auch nicht funktioniert.

Ich habe eine beispielhafte Datei angehängt. Es wäre super wenn Sie sich das mal ansehen und mir kurz Rückmeldung geben könnten. Ich finde einfach keine Lösung. Auch die Kolleginnen sind ratlos.

Herzlichen Dank im Voraus & viele Grüße,

SK.

Hallo Frau K.,

da waren drei Fehlerchen drin:

Sie müssen drei Namen anlegen: zwei für die Linien (hatten Sie) und einen weiteren für die Datenbeschriftung (der hat gefehlt). Und den verwenden Sie in Daten auswählen / horizontale Achsenbeschriftung.

Und: Sie müssen bei der Formel BEREICH.VERSCHIEBEN übers Ziel rausschießen: Sie zählen mit ANZAHL wie viele Daten Sie erfasst haben im Bereich (ich habe nun $A$6:$A$1700 verwendet).

Und: bitte ermitteln Sie die Anzahl der Texte mit der Funktion ANZAHL2 – nicht mit ANZAHL. Dann klappt es.

schöne Grüße

Rene Martin

 

Für eine vernünftige Headline fehlt mir die richtige Kreativitätstechnik.

Schon irgendwie doof!

In einer Arbeitsmappe befindet sich ein Tabellenblatt – nennen wir es „Jena“. In dieser Mappe befindet sich ein zweites Tabellenblatt, das Bezug auf das erste Blatt nimmt. Die Formeln können dann beispielsweise so aussehen:

=Jena!H4

In einer zweiten Arbeitsmappe gibt es auch ein Tabellenblatt „Jena“. Wird nun das zweite Blatt aus der ersten Daten in die zweite kopiert oder werden die Zellen in die zweite Datei kopiert, so wird der Bezug auf die erste Datei „mitgenommen“ und nicht auf die zweite Datei:

Ärgerlich!

Niemand hat die Absicht, sich eine Headline auszudenken…

Das erste Add-In ist installiert. Es folgt eine zweite Version. Die zweite Datei.xlam wird an einem anderen Speicherort abgelegt.

Wird nun das Add-In erneut installiert, erhält man eine Meldung:

Klar soll sie ersetzt werden – was für eine Frage!

Und was passiert? NICHTS!

Heißt: Man muss erst das alte Add-In löschen und dann das neue installieren. Besser: das neue an den gleichen Ort speichern, in dem sich das alte Add-In befand – das alte also durch das neue ersetzen. Schon perfide!

Ihre Werbung, schon für einen Euro am Tag !

Nicht nur Excel nervt. Manchmal auch PowerPoint. Oder Outlook. Oder Word.

Eben! Gestern schickte mir ein Zuschauer meiner Lernvideos von video2brain ein Word-Dokument zu mit der Bitte den Fehler in seinem Word zu finden.

„Der Text steht immer in der Mitte der Seite“ beschrieb er das Problem.

Zuerst vermutete ich eine Absatzformatierung. Fehlanzeige! Dann tippte ich auf eine Einstellung in den Tabellen. Auch daneben.

Und dann habe ich es entdeckt: im Dialog „Seite einrichten“, den man in Word über „Layout“ erreicht, gibt es auf der Registerkarte „Layout“ die Option „Vertikale Ausrichtung“. Diese wurde „als Standard festgelegt“.

Ganz schön perfide!

Eigentlich wurde ich gut erzogen. Keine Ahnung, was danach passiert ist.

Beim Ausscheiden und Einfügen einer Zelle werden sämtliche Formate „ausgeschnitten“ – das heißt: auf Standard zurückgesetzt.

Ausschneiden ….

… und einfügen

Linien werden jedoch nur dann ausgeschnitten, wenn sie von der „richtigen Seite“ hinzuformatiert wurden.

Erstaunlicherweise werden Sie auch nicht dann ausgeschnitten, wenn die ganze Zeile mit einer Linie formatiert wurde – egal von welcher Seite die Linie eingefügt wurde.

Ausschneiden …

… und wieder eingefügt …

 

Tipp der Woche: In Diagrammen Elemente ausblenden

Diesmal kein Rumnörgeln – ich kann auch anders:

Auf der Seite:
gibt es diese Woche wieder einen Tipp von mir – Rene Martin.
Nun ja – wer möchte – schaut einfach mal rein – dort gibt es bei video2brain in dieser Woche und in jeder weiteren Woche einen kostenlosen Tipp. Diese Woche:

In Diagrammen Elemente ausblenden

Meine geheime Superkraft: Ich kann mich selber völlig aus dem Konzept bringen, obwohl ich vorher gar keins hatte.

Ich bin jeden Tag aufs Neue verblüfft.

Kennen Sie das? Ich erstelle eine Pivottabelle und darf dort ein „Berechnetes Element“ hinzufügen.

Statt gruppierten Textinformationen verwende ich ein Datumsfeld. Nun darf ich kein „Berechnetes Element“ hinzufügen. Verstanden.

Ich lösche das Datumsfeld und füge erneut ein Feld hinzu, bei dem Texte gruppiert werden. Ich darf immer noch kein „Berechnetes Element“ hinzufügen.

Pivot „merkt“ sich den Gruppierungsmodus. Frech – gell!

Um ein tadelloses Mitglied einer Schafherde sein zu können, muß man vor allem ein Schaf sein. (Albert Einstein)

Heute kam die Frage, ob man nicht die Pfeilchen der Zellen, die eine Datenüberprüfung besitzen, permanent sichtbar machen kann. Und – nein! – Steuerelemente sind keine Alternative.

Die Antwort: nö, sorry, leider nicht – aber man kann die Datenüberprüfung finden – mit Start / Bearbeiten / Suchen und auswählen:

Nicht nur Excel nervt

Gerade eben habe ich gelacht als ich für eine Schulung Feiertage in Outlook eingefügt habe: Am 31.12. ist Sylvester. Da hat Microsoft wohl zu viel Rambo geschaut … Abgesehen davon ist Silvester doch gar kein Feiertag … Egal – man kann knallen …

Übrigens: Hätte ich Zeit, würde ich gerne weitere Blogs unterhalten!

Für ein Burn out fehlt mir einfach die Zeit

Ein Kunde möchte per Programmierung Bilder nach Excel eingelesen habe. Das ist kein Problem, ebenso wenig wie das Verschieben und Verkleinern der Bilder. Jedoch: er möchte sie auch komprimiert haben. Weder im Objektkatalog noch in einer Suchmaschine finde ich die Möglichkeit per VBA auf den Assistenten „Bilder verkleinern“ zu gelangen. Auch der Makrorekorder hilft nicht weiter.

Also doch sendkeys. Ich drück die Alt-Taste und sehe, dass die Registerkarte „Format“ mit „JV“ erreicht wird. Dann „l“ für „Bilder komprimieren“ und anschließend „e“ für – „96 ppi“.

Also:

SendKeys „%jvle“

SendKeys „{Enter}“

Klappt nicht. Auch nicht:

SendKeys „%j“
SendKeys „%v“
SendKeys „%l“
SendKeys „%e“

Nach einigem Probieren komme ich dahinter, dass ich nur „j“ senden darf – nicht „jv“. Warum sagen die das nicht gleich?

Ungeschoren bleiben ist der Wunsch aller Schafe

Sehr geehrter Herr Martin!

Ich verfolge begeistert Ihre V2B Kurse!

Ich bin Anfänger in Excel und habe für ein  spezifisches Problem bislang keine passende Lösung gefunden.

Wär fein, wenn Sie mir weiterhelfen können.

Ich möchte die negativen Stunden in Spalte F mit Vorzeichen „minus“ und in roter Farbe angezeigt bekommen. Formatiert als Zeit, so dass ich die Spalte summieren kann.

Rechenvorgang: D44-G44-E44 bzw. AZ IST minus AZ SOLL minus Mittagspause 30 Minuten (nur bei AZ IST größer als 6 Stunden, bis größer gleich 6 Stunden, keine Mittagspause).

Wäre nett, wenn Sie mir hier weiterhelfen können.

Vielen Dank im Voraus!

Mit freundlichem Gruß

######

Hallo Herr D.,

eigentlich geht das nicht.

Excel beginnt bei den Datumsangabe am 01.01.1900. Das heißt: 12:00 Uhr wird intern behandelt wir ein halber Tag seit dem 01.01.1900 – also 0,5. Wenn Sie nun -06:00 Stunden haben möchten, wird dies als 31. Dez. 1899 18:00 Uhr behandelt – das geht eigentlich nicht.

Und jetzt zum „eigentlich“:

Sie können in den Optionen /Erweitert den beginn auf 1904 „hochsetzen“ – dann haben Sie zwischen 199 und 1904 vier Jahre „Puffer, in denen Sie mit negativen Uhrzeiten rechnen können.

Ich rate allerdings davon ab, weil dann alle Datumsangaben in dieser Datei um vier Jahre verschoben werden. Und ich sehe in Ihrer Liste 28.05.2016

Alternative: Ich würde Hilfsspalten verwenden: +/- AZ pos:

=WENN(D8-G8-E8>0;D8-G8-E8;0)

+/- AZ neg:

=WENN(D8-G8-E8<0;ABS(D8-G8-E8);0)

Hilft Ihnen das?

######

Hallo Herr Martin!

Ja, so mach ich`s.

Vielen herzlichen Dank für Ihre Hilfe!

Schöne Grüße nach München!

Das Problem ist nicht mein leichter Knall. Das Problem ist jemanden zu finden, der einen möglichst kompatiblen Knall hat.

Ich versuche mittels VBA ein Bild auf 10 cm zu verkleinern. Beim ersten teil hilft der Makrorekorder. Jedoch: wenn das Bild nicht im Querformat, sondern im Hochformat vorliegt, muss ich die Height und nicht die Width verändern. Also gehe ich auf die Suche nach dem Befehl „Winkel“. In Visio heißt er Angle. Jedoch in Excel VBA?

Ein Blick in den Eigenschaften-Dialog (Größe und Position) zeigt: auf diesem Dialog heißt er „Drehung“. Und richtig: unter „Rotation“ werde ich fündig.

Kann Microsoft die Objekte, Eigenschaften und Methoden in den einzelnen Applikationen nicht gleich benennen? Es nervt!

Die Basis einer gesunden Ordnung ist ein großer Papierkorb …

Über die Rechenungenauigkeit in Excel ist schon viel geschrieben worden. Sie finden im Internet eine Reihe Artikel zu diesem Thema.

Auch ich habe auf diesem Blog mich schon einige Male darüber ausgelassen:
Rechenungenauigkeit II
Rechenungenauigkeit
Das Lästern ist die Wurzelbürste der Psyche
Die Küche sieht aus wie Sau. Hab das Licht ausgemacht. Jetzt geht’s.
Excel kann alles – außer Kaffee kochen und rechnen.
Ein Kompromiss ist nur dann gerecht, brauchbar und dauerhaft, wenn beide Parteien damit gleich unzufrieden sind. (Henry Kissinger)

Heute habe ich ein weiteres amüsantes Phänomen festgestellt. Kennen Sie es?

Tragen Sie in eine Zelle die Formel

=(0,5-0,4-0,1)*1

ein. Das Ergebnis ist eine Rechenungenauigkeit ≈ -0,000000000000000027755575615629.

Formatieren Sie diese Zahl als Währung. Das Ergebnis lautet:

0,00 € (oder eine andere Landeswährung)

Formatieren Sie diese Zahl mit der Kategorie Buchhaltung. Das Ergebnis lautet:

– 0,00 € (oder eine andere Landeswährung)

Na ja – halt so ungefähr 0; ±0 – so genau wollen wir es jetzt auch nicht wissen.

Ich kann nicht verhindern, dass ich alt werde. Aber ich kann dafür sorgen, dass ich Spaß dabei habe.

Och nö – Leute – so nicht!

Vor ein paar Tagen habe ich eine Anfrage geschickt bekommen, warum das Makro, das unter Excel 2007 funktionierte, nicht mehr in Excel 2016 läuft. Ein Blick auf den Code – und ich schlage die Hände über dem Kopf zusammen:

  • Die Variablen sind nicht „sauber“ deklariert
  • Es werden keine Objekte adressiert, sondern „angesprungen“
  • Es werden die Grenzen zwischen Objekten (Zelle(A1) und ihren Eigenschaften (der Wert der Zelle A1) vermischt
  • Es finden keine Fehlerüberprüfungen statt (beispielsweise was passiert, wenn der Text „Stg“ nicht gefunden wird …)

Also: „sauber“ programmieren – so wie ihr es in meinen Schulungen lernt. Dann läuft das Programm auch noch in den neueren Excel-Versionen.

Damit Excel nicht mehr nervt!

Tipp der Woche: Mit mehreren Blättern und auf mehreren Blättern gleichzeitig arbeiten

Diesmal kein Rumnörgeln – ich kann auch anders:

Auf der Seite:
gibt es diese Woche wieder einen Tipp von mir – Rene Martin.
Nun ja – wer möchte – schaut einfach mal rein – dort gibt es bei video2brain in dieser Woche und in jeder weiteren Woche einen kostenlosen Tipp. Diese Woche:

Mit mehreren Blättern und auf mehreren Blättern gleichzeitig arbeiten

Wer will schon den aalglatten Prinzen, wenn man schon den Hofnarren mit Dreitagebart haben kann?

Ist Ihnen das schon aufgefallen:
In einer Excelmappe gibt es zwei Tabellenblätter: Tabelle1 und Tabelle2. Tabelle1 liegt links; Tabelle2 rechts. Tabelle2 wird ausgeblendet.


Wenn man nun Tabelle1 kopiert: Kopie erstellen (ans Ende stellen) und anschließend Tabelle2 wieder einblendet: Liegt die Kopie nun links oder rechts von Tabelle2?


Die Lösung: sie liegt links von der ehemals ausgeblendeten Tabelle. „Ans Ende stellen“ heißt also: „Ans Ende der sichtbaren Tabellen stellen“. Ist das schlimm? Man sieht doch, wo die Tabellen liegen?
Die Antwort:
Wenn Sie per Programmierung ein Blatt in eine andere Datei kopieren, beispielsweise so:
Dim xlBlatt As Worksheet
Dim xlDatei As Workbook

Set xlDatei = Application.Workbooks.Open(„D:\Excel\Testdatei.xlsx“)
Set xlBlatt = ThisWorkbook.Worksheets(„Tabelle1“)
xlBlatt.Copy After:=xlDatei.Worksheets(xlDatei.Worksheets.Count)

MsgBox xlDatei.Worksheets(xlDatei.Worksheets.Count).Name
Nun liefert das Meldungsfenster nicht den Namen des kopierten Blattes, sondern den Namen des letzten Blattes (wenn es ausgeblendet war). Und: leider liefert die Methode Copy kein Objekt, also kein Verweis auf ein Tabellenblatt zurück.
Heißt: gut aufpassen! Sonst nervt das Ergebnis!

Und wie geht das wieder weg?

Ein Add-In in Excel zu erstellen ist nicht schwierig – man muss nur eine Excelmappe mit Makros als Add-In speichern.

Ein Add-In einzubinden ist auch nicht schwierig. Unter Datei / Optionen / Add-Ins / Excel-AddIns [Los] findet man die Schaltfläche „Hinzufügen“. Damit kann ein Add-IN eingebunden werden.

Man kann es deaktivieren – aber wie bekommt man es eigentlich weg?

Die einzige Möglichkeit ist das Add-In umzubenennen, es dann erneut zu aktivieren und/oder deaktivieren. Dann wird erkannt, dass es nicht mehr vorhanden ist:

Ich bin nicht doof. Ich denke nur anders – und die anderen verstehen es nicht.

Ich würde gerne den Algorithmus verstehen:

Ich trage in A1 eine Zahl ein. Beispielsweise 1. Ziehe rüber – die Zahl wird in die anderen Zellen geschrieben.

Steht jedoch darunter eine Formel – es kann eine einfache sein (=A1 oder =HEUTE()) oder auch eine komplexe Berechnung; werden beide Zellen markiert (die Richtung des Markierens ist egal – von oben nach unten oder von unten nach oben) und zieht man nun beide Zellen rüber wird die Zahl hochgezählt: 1, 2, 3, 4, …

Wann zählt Excel nicht weiter?

Wenn untereinander steht:

  • Zahl | Zahl | Formel
  • Zahl | Zahl | Zahl | Formel
  • Formel | Zahl | Zahl
  • Zahl | Zahl | Formel | Formel

Wann zählt Excel weiter?

Wenn untereinander steht:

  • Zahl | Formel | Zahl | Formel
  • Zahl | Formel | Zahl | Zahl | Formel
  • Formel | Zahl
  • Formel | Zahl | Formel | Zahl
  • Zahl | Formel | Formel | Zahl

Ich finde die Regel nicht …

Phantasie ist etwas, was sich manche Menschen so gar nicht vorstellen können…

Hallo Herr Martin,

das ist doof: Ich markiere in einem Diagramm einen Datenpunkt und lasse mir für den Datenpunkt seinen Wert anzeigen:

Wenn ich nun statt des Wertes die Rubrikenbeschriftung oder Kategoriennnamen haben möchte, werden diese für alle Werte angezeigt:

Die Antwort: Ja – das ist ziemlich blöde. Sie können es aber mit einem Trick umgehen:

Markieren Sie den Datenpunkt wie gehabt. Wechseln Sie über Entwurf / Diagrammlayouts / Diagrammelement hinzufügen / Datenbeschriftungen auf „Weitere Datenbeschriftungsoptionen“.

Wenn Sie dort wechseln, dann erhalten Sie die Anzeige nur für einen Punkt.

Gefühle sind heutzutage auch nur noch was für ganz Mutige.

Excel ist an vielen Stellen nicht konsequent. Der Text „1“ ist beispielsweise etwas anderes als die Zahl 1. Dennoch kann man den Text mit 1 multiplizieren.

Ebenso verhält es sich mit WAHR und 1. Eigentlich entspricht Wahr dem Wert 1 und Falsch dem Wert 0. Eigentlich. Multipliziert man die beiden Werte WAHR und FALSCH, beziehungsweise die Funktionen =WAHR() und =FALSCH() mit 1, erhält man 1 beziehungsweise 0. Intern handelt es sich jedoch um einen anderen Wert. Also
=WAHR=1

liefert FALSCH.

Und diese Unschärfe führt auch dazu, das Wahr-Werte nicht addiert werden können:

=SUMMENPRODUKT(C:C=“Briefpapier“)

liefert 0.

=SUMMENPRODUKT((C:C=“Briefpapier“)*1)

dagegen das korrekte Ergebnis:

Alle Tage sind gleich lang, aber unterschiedlich breit

Achtung!

Wir haben eine Liste, auf die wir einen Filter anwenden:

Schreibt man in eine andere Zelle einen Wert, kopiert ihn und fügt ihn über die gefilterten Daten ein, funktioniert dies:

Kopiert man jedoch einen Wert und fügt ihn mit Inhalte einfügen / Werte ein, so werde die dazwischenliegenden Werte überschrieben:

Die Lösung: man muss nach dem Filtern die nur sichtbaren Zellen auswählen (Start / Bearbeiten / Suchen und Auswählen / Inhalte auswählen). Die Tastenkombination [Alt] + [Shift] + [,] (also: [Alt] + [Shift] + [;]) tut das Gleiche.

DANN sind Sie auf der sicheren Seite.

Hatte Kribbeln im Bauch … dachte schon bin verliebt … aber war doch nur Hunger …

Schon mal probiert?

Eine Datei greift mit einer Formel auf eine zweite Datei zu. Kein Problem.

Doch: ist ein Problem – nämlich, wenn es sich bei der Formel um SUMMEWENN, ZÄHLENWENN & co handelt. Dann muss nämlich die Quelldatei offen sein, damit kein Fehler angezeigt wird. Sehr erstaunlich!

Komm auf die dunkle Seite – wir haben die Kekse!

Kennen Sie die Datenüberprüfung? Dort kann ich eine Liste festlegen. Dafür gibt es zwei Varianten: entweder man trägt die Daten in Zellen ein und verweist auf den Zellbereich (das funktioniert auch, wenn die Zellen einen Namen tragen) oder man trägt die Daten direkt ein. Vor allem bei kleineren Listen m/w, intern/extern, Beamter/Angestellter/Arbeiter, … empfiehlt es sich die Daten „hart“ einzutragen. Jedoch:

befinden dich die Daten (beispielsweise m;w) in der Liste, ist diese Liste case-sensitiv! Bei einer ausgelagerten Liste nicht!

Für eine vernünftige Headline fehlt mir die richtige Kreativitätstechnik

Vergessen?

Viele Dialog kann man an der rechten Ecken Ecke „aufziehen“, das heißt vergrößern. Das ist gut und wichtig, weil sich dadurch das Eingabefeld vergrößert und der darin befindliche Text lesbar wird. Leider ist dies nicht bei allen Dialogen der Fall, wie beispielsweise bei den Diagrammen: Daten auswählen (Registerkarte „Entwurf“) / Bearbeiten):

Haben die das vergessen?

Zwischen Reden und Tun liegt das Meer

Ärgerlich: viele Dinge kann man auf mehreren Blättern gleichzeitig erledigen, wenn man mehrere Blätter mit [Strg9- oder [Shift]-Taste markiert (also den Gruppenmodus aktiviert): Zellen formatieren, beschriften, Formeln einfügen, Spalten verbreitern, die Seite einrichten. Leider nicht einen Blattschutz auf alle selektierten Blätter anwenden. Und: Folgende Sache geht leider auch nicht:

Man kann zwar auf allen Blättern in Kopf- oder Fußzeile ein Bild einfügen. Ändern man jedoch die Bildgröße, wird sie nur auf einem Blatt geändert. Schade!

Du bist erst dann wirklich erfolgreich wenn Deine Schwiegermutter das sagt.

Unglaublich: gestern (12.07.2017) fand sich in der Süddeutschen Zeitung auf Seite 8 folgendes Diagramm:

In jeder Excel-Schulung predige ich, dass Datumsangaben, die nicht äquidistant sind, nicht auf einem Liniendiagramm mit gleichem Abstand abgetragen werden dürfen. Man muss ein Punktdiagramm (ein XY-Diagramm) verwenden, um den unterschiedlichen Abständen Rechnung zu genügen. Sonst wird das Diagramm und seine Aussage verzerrt.

Haben die nicht aufgepasst (schlampig) oder wollen die bewusst Daten verdrehen (böswillig)?

Alle wollen individuell sein. Aber wehe es ist mal jemand anders.

Wir können ja so froh sein, dass wir Microsoft Excel verwenden (dürfen).

Heute in der libreOffice Calc-Schulung haben wir festgestellt, dass eine Linie, die an den rechten Zellrand angefügt/formatiert wird auch von dort wieder weggenommen werden muss und nicht vom linken Rand der Nachbarzelle.

Zu Glück hat Excel dieses unglückliche Verhalten schon seit vielen Versionen bereinigt.

Himmiherrgotzsakramentzefixallelujaglumpfarregtz

Es ist schön, wenn Excel Assistenten zur Verfügung stellt. Beispielsweise einen zum Duplikate entfernen:

Da ich diese Funktionalität in einem umfangreichen Programm benötige, zeichne ich ihn mit dem Makrorekorder auf:

ActiveSheet.Range(„$A$1:$J$78“).RemoveDuplicates _
Columns:=Array(1, 2, 3, 4, 5, 6, 7 _
, 8, 9, 10), Header:=xlYes

Der Parameter Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10) gefällt mir nicht.

In der Hilfe steht, dass man ihn weglassen kann – dann würden alle Spalten verwendet werden. Ein Test zeigt: Das ist falsch. Lässt man den Parameter weg, passiert: GAR NICHTS!

Also programmieren wir den Parameter:

Dim intSpalten() As Integer
Dim i As Integer

ReDim intSpalten(0)
intSpalten(0) = 1
For i = 2 To ActiveSheet.Range(„A1“).CurrentRegion.Columns.Count
ReDim Preserve intSpalten(UBound(intSpalten) + 1)
intSpalten(UBound(intSpalten)) = i
Next

ActiveSheet.Range(„A1“).CurrentRegion.RemoveDuplicates _
Columns:=intSpalten, Header:=xlYes

Das Ergebnis ist eine Fehlermeldung:

Verwundert reibe ich mir die Augen. Probieren und eine lange Suche liefert das Ergebnis: Man muss das Array vom Typ Variant deklarieren. Und: der Parameter Columns verlangt den Wert in Klammern !?! Dann klappt es: die Spaltenanzahl des Assistenten „Duplikate entfernen“ wird dynamisch:

Dim intSpalten
Dim i As Integer

ReDim intSpalten(0)
intSpalten(0) = 1
For i = 2 To ActiveSheet.Range(„A1“).CurrentRegion.Columns.Count
ReDim Preserve intSpalten(UBound(intSpalten) + 1)
intSpalten(UBound(intSpalten)) = i
Next

ActiveSheet.Range(„A1“).CurrentRegion.RemoveDuplicates _
Columns:=(intSpalten), Header:=xlYes

PS: Ein Dankeschön an Dominik Petri für den Hinweis!

I bring the problems – you bring the drinks

Gestern beim Programmieren. Ich habe eine Liste – sagen wir mal mit zirka 50.000 Einträgen. Eigentlich nicht viel für Excel. Ich starte den Assistenten „Spezialfilter“ (Daten / Sortieren und Filtern / Erweitert) mit der Option „Keine Duplikate“:

Das Ergebnis: Excel wird sehr, sehr langsam (er benötigt zirka eine Minute für das Berechnen – wenn nicht sogar ein Absturz die Folge ist):

Die Lösung: „Duplikate entfernen“ im gleichen Register, Gruppe „Datentools“. In Bruchteilen von Sekunden erhalte ich das Ergebnis:

Meine Vermutung: Assistenten, die irgendwann in Excel Einzug hielten, werden NIEMALS mehr überarbeitet! Schade eigentlich.

Wir sind zwar zu nichts zu gebrauchen, aber dafür zu allem fähig.

Schade. Wenn man in PowerPoint ein Diagramm erstellt, wird Excel ohne Menüband geöffnet. Dort werden die Daten eingetragen. Leider kann man Zahlen dort nicht runterziehen.

Ja – okay – ich weiß – man kann Excel schließen und danach erneut über Daten bearbeiten / Daten in Excel bearbeiten öffnen. Dann wird das Menüband angezeigt und man kann die Reihe per Ziehen ausfüllen.

Tipp der Woche: Listen transponieren und um 180 Grad drehen

Diesmal kein Rumnörgeln – ich kann auch anders:

Auf der Seite:
gibt es diese Woche wieder einen Tipp von mir – Rene Martin.
Nun ja – wer möchte – schaut einfach mal rein – dort gibt es bei video2brain in dieser Woche und in jeder weiteren Woche einen kostenlosen Tipp. Diese Woche:

Listen transponieren und um 180 Grad drehen

Excellent Days

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.

Ich bin schon mit dem Besten zufrieden…

Manche Übersetzungen sind wirklich sehr merkwürdig.

In der letzten Excel-Schulung fragte eine Teilnehmerin, was folgende Erklärung bedeutet:

Text: „ist die Zeichenfolge mit den Zeichen, die Sie kopieren wollen“

Noch erstaulicher ist die Beschreibung für die Funktion T:

T „wandelt die Argumente in Text um.“ T benötigt  einen Wert, „den Sie überprüfen wollen. Wenn der Wert kein Text ist, werden zwei Anführungsstriche (leerer Text) zurück gegeben.“

Versteht ihr das?

Microsoft hat Besserung gelobt – mal sehen …

So ist es gut – komm auf die dunkle Seite der Macht!

Wollt ihr wissen, wie man Excel zum Absturz bekommt? Man muss die Funktion AGGREGAT in einem Namen verwenden und diesen in einem Diagramm.

Das Ganze geht so:

Eine Tabelle holt sich Werte aus einer anderen Liste. Da einige Werte nicht gefunden werden, werden diese als #NV angezeigt. In einem Diagramm werden die entsprechenden Kategorien verwendet:

Unschön, denke ich mir. Die Jahreszahlen, die keinen Wert haben, sollen ausgeblendet werden. Und lege vier Namen an: „Bau“, „IT“, „Verwaltung“ und „sonstiges“. Sie haben die Form:

=BEREICH.VERSCHIEBEN(Tabelle1!$D$2;1;0;1;AGGREGAT(2;6;Tabelle1!$D$3:$J$3))

AGGREGAT deshalb, weil es die Fehlerwerte übergeht.

Ich versuche nun den Namen im Diagramm einzufügen, das heißt aus der ersten Datenreihe

=DATENREIHE(Tabelle1!$C$3;Tabelle1!$D$2:$J$2;Tabelle1!$D$3:$J$3;1)

wird ein:

=DATENREIHE(Tabelle1!$C$3;Tabelle1!$D$2:$J$2;Tabelle1!Bau;1)

Das Ergebnis: ABSTURZ!

Die Lösung ist simpel: Man lagert die Funktion AGGREGAT in eine Zelle aus (hier: L3). Man gibt ihr einen Namen – beispielsweise AGGREGAT.

Und ändert nun die Namen in:

=BEREICH.VERSCHIEBEN(Tabelle1!$D$2;1;0;1;AGGREGAT)

Nun kann der Bereich geändert werden:

=DATENREIHE(Tabelle1!$C$3;Tabelle1!$D$2:$J$2;Tabelle1!Bau;1)

Wer dies ausprobieren möchte, kann die Dateien herunterladen: AGGREGAT und AGGREGAT02.

 

Ich musste erst die Schafe aus meiner Wohnung treiben, die ich beim Einschlafen gezählt hatte.

Noch eine hübsche Frage aus der Schulung:

Wir üben den Spezialfilter, der über die Schaltfläche „Erweitert“ in der Registerkarte „Daten“ erreichbar ist.

„Ich erhalte eine seltsame Fehlermeldung“, lautet die Teilnehmerfrage:

Klar: „Sie haben nicht auf die Schaltfläche „Erweitert“ geklickt, sondern auf das Filtersymbol, mit dem der Autofilter eingeschaltet wird. Und – Excel hat recht – „Dies kann nicht auf den ausgewählten Bereich angewendet werden.“ Eben – es wurde nur eine Zelle ohne Daten markiert.

Blöde Party, wenn ich meine Hose finde, gehe ich!

Gestern in der Excelschulung. Wir üben Tabellen (intelligente Tabellen, dynamische Tabellen, formatierte Tabellen). Eine Teilnehmerin fragt, warum bei ihr das Symbol grau unterlegt ist – warum sie keine Tabelle erstellen darf. Es gibt zu viele Möglichkeiten – ich gehe zu ihrem Rechner:

Die Ursache ist schnell gefunden – sie hat bereits eine Tabelle erstellt; diese allerdings wieder weiß eingefärbt. Das sieht man deutlich an den Tabellentools / Registerkarte „Entwurf“.

Mein Bett und ich lieben uns aber der Wecker kommt damit nicht klar!

Excel unterscheidet an fast keiner Stelle zwischen Groß- und Kleinschreibung.

Ich kann einen Zellnamen (f3) in Kleinbuchstaben eintragen, einen selbst erstellten Namen in Kleinbuchstaben schreiben, Funktionen (summe), bei Vergleichen wird nicht unterschieden (=WENN(„RENE“=“rene“;1;0) liefert 1), sortieren (dort kann man es einschalten), filtern, …

An einer Stelle(*) wird jedoch unterschieden: bei der Datenüberprüfung:

In einem Kalender darf der Mitarbeiter U für Urlaub, S für Seminar, K für krank, D für Dienstreise und T für Telearbeitstag eintragen. Verboten sind ihm bei einer solchen Liste jedoch die Kleinbuchstaben. Ärgerlich!

(*) Ich weiß, es gibt noch weitere Stellen, bei denen Excel nicht case-sensitiv ist – jedoch bei der Datenüberprüfung ärgert es.

Und ich weiß: man könnte die Liste natürlich mit beiden Varianten erstellen. Oder über die Option „benutzerdefiniert“ die Groß- und Kleinschreibung abfangen. Aber warum nicht einfach bei der Liste?

Ich bin sehr wohl spontan … wenn man mir früh genug Bescheid sagt

Eigentlich unterscheidet Excel zwischen Text und Zahl. Eigentlich. Sicherlich kennen Sie folgendes Phänomen:

Eine Spalte ist als Text formatiert:

In einer anderen Spalte stehen ZAHLEN. Diese werden mit der Funktion SVERWEIS als #NV (nicht vorhanden) quittiert:

Das habe ich schon mehrmals beschrieben – beispielsweise in: „Sverweis funktioniert nicht“
Ebenso werden sie bei der Überprüfung auf Gleichheit

{=ODER(K2=$G$2:$G$22)}

(als Matrixformel) korrekt als unterschiedliche Werte erkannt:

Verwendet man statt dem Gleichheitsoperator „=“ jedoch die Funktion IDENTISCH werden die Texte und Zahlen als gleich(wertig) eingestuft:

{=ODER(IDENTISCH(K2;$G$2:$G$22))}

Ebenso übergeht ZÄHLENWENN diesen Unterschied:

Auch hier gilt mal wieder – schade, dass Excel an so vielen unterschiedlich ist, beziehungsweise einfach nicht konsequent. Kein Verlass auf gar nichts!

Diejenigen, die wissen, wie es nicht geht, sollen nicht diejenigen stören, die es bereits tun.

Ich bin sehr irritiert.

Auf unseren letzten Excel-Stammtisch haben wir folgendes Phänomen festgestellt:

Tragen Sie in eine Zelle den Wert 8625,21 ein. Speichern Sie die Datei. Ändern Sie den Dateinamen, indem Sie „.zip“ als Ende einfügen. Entzippen Sie die Datei. Öffnen Sie die Datei sheet1.xml, die Sie im Ordner xl/worksheets finden. Und was sehen Sie dort?

8625.2099999999991

???

Das heißt: nicht nur beim Rechnen und Herunterziehen hat Excel interne Rundungsfehler, sondern bereits bei der Eingabe. Und das schon bei „kleinen“ Zahlen.

Finden wir das komisch?

Wie doof ist denn das immer noch?

Ich habe es noch einmal ausprobiert: anderer Rechner, anderes Betriebssystem – gleiches Phänomen.

Ich versuche es von Neuem: In A1 steht das Jahr 2017, in B1 der Monat 5. In C1 die Formel

=DATUM($A$1;$B$1;SPALTE()-2)

Rüberziehen bis AG1.

Die Datumsangaben werden als Tag mit TT formatiert.

In C1:AG38 wird folgende bedingte Formatierung verwendet:

=WOCHENTAG(C$1;2)>5

Sieht so aus:

Monatszahl in B1 ändern. Sieht so aus:

Tipp der Woche: Zwischen Tabellenblättern bewegen

Diesmal kein Rumnörgeln – ich kann auch anders:

Auf der Seite:
gibt es diese Woche wieder einen Tipp von mir – Rene Martin.
Nun ja – wer möchte – schaut einfach mal rein – dort gibt es bei video2brain in dieser Woche und in jeder weiteren Woche einen kostenlosen Tipp. Diese Woche:

Zwischen Tabellenblättern bewegen

Ich kann auch ohne Alkohol lustig sein. Aber sicher ist sicher.

Ü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.“

Vorhersagen sind immer schwierig — vor allem über die Zukunft.

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.

Ich schimpfe nie beim Autofahren – ich raste direkt aus.

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 …

Als ich von den schlimmen Folgen des Trinkens las, gab ich sofort das Lesen auf.

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?

Habe heute mal wieder Sport gemacht. Ritter Sport. Zwei Tafeln.

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?

Excellent Days

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.

Ich habe meine Ernährung jetzt umgestellt: Die Kekse stehen jetzt links von der Tastatur.

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!

Old age is no place for sissies. (Bette Davis)

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.

I ¤ non ASCII-characters

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!

 

Tipp der Woche: Text oder Zahl

Diesmal kein Rumnörgeln – ich kann auch anders:

Auf der Seite:
https://www.video2brain.com/de/videotraining/excel-tipps-jede-woche-neu
kann die Welt jede Woche Rene Martin schauen. Will die Welt das? hat die Welt DARAUF gewartet?
Nun ja – wer möchte – schaut einfach mal mittwochs rein – dort gibt es bei video2brain jede Woche einen Tipp, den man eine Woche lang kostenlos ansehen kann.
Diese Woche:

In einer Zahlenreihe Texte finden

Kaffee von glücklichen Bohnen

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 bin nicht dumm. Habe nur Pech beim Denken

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.

Wer morgens kalt duscht, lügt auch den Rest des Tages

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“))

Viele halten mich für arrogant. Woher wollen die das denn wissen? Ich unterhalte mich ja nicht mit denen!

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.

Wer Rechtschreibfehler findet darf sie behalten.

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 ?!?

Alle sagten immer das geht nicht, dann kam jemand, der das nicht wusste, und hat es einfach gemacht!

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.

Das Universum spielt nicht immer fair, aber dafür hat es einen höllischen Sinn für Humor

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

 

An dem Tag, an dem du mich das erste Mal nackt siehst, wirst du denken: „Ich habe einen verdammt guten Wal getroffen!“

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)

Immer auf dem Teppich bleiben – und keine großen Sprünge machen, wenn das Daruntergekehrte verborgen bleiben soll.

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\…

Aus Langeweile hätte ich eben fast gearbeitet. Man muss aber auch höllisch aufpassen.

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.

Mit Begeisterung

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

Mein Leben ist ziemlich betrinkenswert.

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 …

Lächle – du kannst sie nicht alle töten!

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?

 

Du musst schon selbst Konfetti in dein Leben pusten.

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 …

Die wichtigsten Dinge sind ja sowieso nicht Dinge.

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?

Ich hab das schon verstanden. Ist mir nur egal.

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:

Das Leben ist kurz… Iss den Nachtisch zuerst!

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.

In eigener Sache

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:

  • Tastenkombinationen: eine Auswahl

https://www.video2brain.com/de/tutorial/tastenkombinationen-eine-auswahl

  • AutoKorrektur und Namen

https://www.video2brain.com/de/tutorial/autokorrektur-und-namen

  • Zeilenumbrüche entfernen

https://www.video2brain.com/de/tutorial/zeilenumbrueche-entfernen

  • Spalten vertauschen

https://www.video2brain.com/de/tutorial/spalten-vertauschen

Ich hatte in der Schule nur Singen und Klatschen

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:

Da muss ich erst einmal eine Nacht darüber feiern.

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!

Nur hohle Menschen urteilen nicht nach dem Schein. Das wahre Geheimnis der Welt ist das Sichtbare, nicht das Unsichtbare. (Oscar Wilde)

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.

Das Gras wird gebeten über die Sache zu wachsen. Das GRAS bitte!

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.

Qualität ist, wenn die Kunden zurückkommen und nicht die Ware.

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!

1 2 3 5