Vier Stunden Zocken soll süchtig machen. – Ich gehe acht Stunden arbeiten, aber süchtig danach bin ich noch nicht.

Nicht aufgepasst:

In der Excelschulung stoßen wir auf folgendes Problem:

Ein Datenbanksystem liefert die Daten einer Spalte entweder als Jahreszahl – Q1 (oder Q2, Q3, Q4) oder < 2018 Q1.

Angenommen man möchte nach dem Quartal oder dem Jahr filtern. Dann wäre es sinnvoll, diese Informationen in eigenen Spalten zu haben. Ich zeige die drei Techniken:

  1. Daten / Text in Spalten
  2. Blitzvorschau
  3. Formeln

Als Formel, um das Jahr zu extrahieren, verwende ich:

=WENN(LINKS(K2;1)="<";TEIL(K2;3;4);LINKS(K2;4))

Ein Teilnehmer fragt, was denn wäre, wenn statt einem „<“ ein „>“ als erstes Zeichen in der Zelle stünde.

„Nun“, so lautet meine Antwort, dann könnte man überprüfen, ob das erste Zeichen eine Ziffer ist. Beispielsweise so:

=WENN(ISTZAHL(LINKS(K2;1));LINKS(K2;4);TEIL(K2;3;4))

Die Lösung ist leider falsch:

Auch ISTTEXT funktioniert nicht. Das Problem: LINKS(K2;1) liefert immer einen Text – egal, ob es sich um eine Ziffer oder ein anderes Zeichen handelt. Eine Lösung für dieses Problem wäre beispielsweise:

=WENN(ISTFEHLER(LINKS(K2;1)*1);TEIL(K2;3;4);LINKS(K2;4))

Man multipliziert das erste Zeichen mit 1. Ist das erste Zeichen ein Buchstabe oder ein anderes, nichtnumerische Zeichen, ist ein Fehler die Folge. Dieser kann abgefangen werden.

Der Teilnehmer ist zufrieden.

Mein Rechner hat sich aufgehängt. Meine Wäsche macht das nie.

Volker Pagel ist sehr irritiert. Er hat lange gesucht, um die Ursache für folgende Inkonsequenz zu finden.

In einer Liste befinden sich Kostenstellennummer, beispielsweise 010, 0010 oder 000010.

Die Funktionen der Klasse ZÄHLENWENN, SUMMEWENN & co unterscheiden nicht zwischen 010, 0010 oder 00001, obwohl diese als Text vorliegen. Wahrscheinlich werden diese Texte intern als Zahlen behandelt.

Anders dagegen die Funktionen WENN, SVERWEIS, XVERWEIS, SUMMENPRODUKT & co. HIER wird klar zwischen 010 und 00010 unterschieden!

Rosi hat jetzt unter 32168 eine Rufumleitung. Zu Layla.

Perfide.

Warum funktioniert der Filter nicht? In einer Liste befinden sich Zahlen – allerdings bietet der Autofilter die Einstellung „Textfilter“ an:

Ich filtere „beginnt nicht mit ‚-‚“, um die negativen „Zahlen“ auszublenden. Das Ergebnis:

Die negativen Zahlen bleiben stehen. Ich filtere „größer oder gleich 0“ – es klappt:

Und die Ursache? Warum zeigt Excel „Textfilter“ an, obwohl es sich um Zahlen handelt? Obwohl die Spalte mit dem Zahlenformat „Zahl“ formatiert ist?

Die Antwort: in der Spalte befinden sich keine Zahlen, sondern Formeln, die manchmal einen Text (eine leere Zeichenkette) liefern:

=WENNFEHLER(O2-P2;"")

Das Ergebnis sind Texte („“) und Zahlen. Text gewinnt – obwohl die Spalte korrekt als Zahl formatiert ist. Perfide!

Herr Ober, können Sie bitte den Daumen von meinem Schnitzel nehmen! – Damit es mir schon wieder runterfällt, oder was?

Hallo Rene,

herzlichen Dank für deine Bemühungen! Das bringt mich ein großes Stück weiter. Leider funktioniert aber irgendeine Kleinigkeit noch nicht… Ich hab viel probiert, komm aber nicht auf den Fehler.

Expression.Error: Der Wert "2022" kann nicht in den Typ "Text" konvertiert werden.

Die Fehlermeldung verstehe ich nicht. Das Jahr, nach welchem gefiltert werden soll.

Hallo Nadine,

ist die Zahl 2023 in der Zelle als Text formatiert?

Und: was macht „geänderter Typ“? – in Text oder Zahl konvertieren?

Liebe Grüße

Rene

Hallo Rene,

genau, ich habe dann extra die 2023 in Text formatiert. Ursprünglich hatte ich es als Zahl, da kam allerdings auch diese Fehlermeldung, weshalb ich die 2023 dann in Text formatiert habe.

Hier die Schritte, welche ich in der Jahrestabelle ausgeführt habe:

Dort wo dann die Formel eingefügt wird, also dort, wo nacher nach diesem Jahr gesucht werden soll, sieht die Formatierung so aus:

Hallo Nadine,

Folgende Ursache: Ich vermute in deiner Zelle stehe die Jahreszahl als ZAHL – in meiner ersten Städtedatei hatte ich sie als Text formatiert.

Damit du auch einen Text erhältst, muss dein zweiter Schritt

= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SOP"]}[Content],{{"Column1", type text}}){0}[Column1]

verwendet werden

In deinem Code

= Table.SelectRows(Quelle, each (Record.Field(_ , Excel.CurrentWorkbook(){[Name="SOP"]}[Content]{0}[Column1]) <> null))

Zusammengefasst: der in Excel eingetragene Wert in eine Zahl. Die Spaltenüberschrift jedoch ein Text. Irgendwann muss die Zahl in einen Text konvertiert werden!

Darf ich dir deine Nase zurückgeben. Sie steckt in meinen Angelegenheiten.

Excelschulung. Wir erstellen einen Kalender. Daran kann man einige Funktionen üben: die Funktion WENN, Datumsfunktionen, Textfunktionen. Um Funktionen aus der Kategorie „Nachschlagen und verweisen“ zu zeigen, erstelle ich einen mehrsprachigen Kalender. Über eine Auswahlliste (Datenüberprüfung) wird die Sprache gewählt:

Mit VERGLEICH wird die Zeilennummer ermittelt; INDEX „holt“ den Ländercode aus einer kleinen Tabelle:

Eine Teilnehmerin sagt, dass es bei ihr nicht funktioniere. Ich schaue auf ihren Bildschirm. Richtig: Sie hat Excel 2013. Das erkenne ich sofort an den Großbuchstaben der Texte im Menüband. Bis Excel 2013 wurde nicht der ISO-Sprach- und Ländercode verwendet, sondern ein anderer. Zum Glück finde ich ihn auf der Festplatte in einem älteren Beispiel:

Und DAMIT funktioniert es nun auch bei ihr:

Früher waren es Mickey-Maus Hefte. Dann folgten Yps und Bravo. Anschließend der Playboy. Und heute ist es die Apotheken-Umschau.

Einfach nicht aufgepasst!

Excel-VBA-Schulung. Eine Teilnehmerin möchte ein kleines Programm mit mir geschrieben haben: Jede Woche erhält sie eine Liste und jede Woche muss sie in dieser Liste Berechnungen durchführen. Eine bestimmte ID (beispielsweise Idefix) wird gesucht, sämtliche Werte (hier drei) werden wie folgt berechnet:

Die Anzahl der Römer wird mit der Anzahl der Piraten multipliziert und die einzelnen Produkte summiert. Das Ergebnis wird durch die Summe der Römer dividiert. Aber nur dann, wenn keine Hinkelsteine vorhanden sind. Alles klar? – Klar!

Ich beginne Schritt für Schritt. Multipliziere und addiere – hierfür bietet sich doch SUMMENPRODUKT an, oder?. Also: los geht’s:

=SUMMENPRODUKT((A:A=G5)*(B:B)*(C:C))

Ich habe drei Mal überlegen müssen, woher die Fehlermeldung rührt. Die Antwort:

Klar: ich multipliziere jede Zelle jeder Spalte. Und das funktioniert bei der Überschrift (Text!) natürlich nicht!

Ich muss ändern. Entweder so:

=SUMMENPRODUKT((A:A=G5)*1;(B:B);(C:C))

Oder indem ich auf den Bereich ohne Überschrift verweise:

=SUMMENPRODUKT((A2:A40=G5)*(B2:B40)*(C2:C40))

Oder indem ich den Fehler mit WENNFEHLER abfange, oder oder oder.

Und DANN ist der Rest auch kein Problem – beispielsweise so:

=SUMMENPRODUKT((A2:A40=G9)*1;(B2:B40);(C2:C40);(D2:D40<>"Hinkelstein")*1)/SUMMEWENNS(B2:B40;A2:A40;G9;D2:D40;"<>Hinkelstein")

Gerechnet wird also:

(10 x 31 + 40 x 50) / (10 + 40) = 46,2

Und diese Formel kann man mit dem Makrorekorder aufzeichnen und über alle Zellen „laufen lassen“. Das Ganze wird in der Datei Personal.xlsb gespeichert.

Das sind keine Speckröllchen – das ist externer Speicherplatz für noch mehr Bauchgefühl.

Hallo Rene

Ich schätze es ausserordentlich, dass Du mich unterstützt und fühle mich geschmeichelt.

Du hast natürlich vollkommen Recht mit dem Hinweis, dass in einer Spalte keine Zahlen und Texte stehen sollten.

Dieser Umstand ist dadurch entstanden, weil die Tabelle zusätzlich für einen anderen Zweck benutzt wurde. Hierbei wurden die Zeilen insofern erweitert, indem für jeden Kunden eine zusätzliche Zeile eingetragen wurde.

Hatte ein Kunde einen Service, wurde in der entsprechenden Spalte eine 1 reingeschrieben und im Anschluss mit einem Flow in einen ScharePoint Liste übertragen. Anders hätte ich ja die Kunden Records nicht handeln können.

Nun habe ich den Servicekatalog und die Zuweisung der Kunden zum jeweiligen Service getrennt. Somit konnte ich die Tabelle vom Servicekatalog wieder «drehen», womit die Services in den Zeilen stehen und die Spaltenwerte zu den jeweiligen Daten passen.

Eine andere Lösung gibt es nicht, wenn ich Deine Erklärungen richtig verstanden habe und eigentlich scheint es mir auch logisch.

Nun muss ich einige weitere Anpassungen an dem ganzen Konstrukt vornehmen und einen neuen Flow erstellen. Dabei hoffe ich natürlich, dass ich mir keine neue Baustelle geschaffen habe.

Nochmals herzlichen Dank für Deine wertvolle Hilfe und die guten Tipps

Herby

#####

Hallo Herby,

in meinen vielen Jahren Exceltraining und meinen vielen Artikeln auf excel-nervt habe ich gelernt, dass

* Excel ein sehr gutes Programm ist

* Excel manchmal etwas eigenwillig ist

* Anwender und Anwenderinnen oft Wünsche haben, die sich SO nicht direkt umsetzen lassen (ich verstehe oft die Hintergründe)

* dass man sich auf das Denken von Excel einlassen muss (ist halt ein Mann*) und man manchmal seine Daten etwas anders organisieren muss, damit man zum Ziel kommt

Liebe Grüße

Rene

*) Die Frage, ob Excel männlich oder weiblich ist, stelle ich häufig in Schulungen. Und amüsiere mich dann über die Antworten à la: „Excel ist männlich, weil …“ oder „Excel muss eine Frau sein, der nur so kann man sich erklären …“

Heute habe ich gelesen, was auf der Flasche Shampoo steht: Für extra Volumen und mehr Fülle!!! Kein Wunder, dass es mir schwerfällt, mein Gewicht zu kontrollieren! Ich werde ab sofort Geschirrspülmittel benutzen! Da steht drauf: entfernt auch hartnäckiges Fett.

Einfach nicht aufgepasst!

Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:

Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel

=WENN(J2>1000;WAHR;FALSCH)

immer WAHR liefert!

Zweite Grundregel der Gastronomie: Immer Butter bei die Tische!

Hallo Rene!

Hoffe, dass du, deine Familie und dein Umfeld alle gesund seid.

Du hast mir vor einiger Zeit geholfen mehrere Belegnummern in eine Zelle zu schreiben. Nun wollte ich auch das Datum (auch mehrere) auf gleiche Weise anzeigen lassen. Herausgekommen sind dann die Zahl(en) vom Datum. Wenn nur eine Zahl (=Datum) angezeigt wurde konnte ich das Datumformat. Wenn nur ein Zahlenwert vorhanden ist, habe ich, wie gelernt, die Formel mit 1 multipliziert und dann das Datumformat angewendet. Meine Vermutung ist, dass man die Formel um eine Formatierungsformel ergänzt. Videos über die allgemeine Formatierung mit Formeln in der Zelle habe ich leider nicht gefunden. Bitte um Lösung.

Vielen Dank, Peter

Hallo Peter,   die Formel muss statt

=TEXTVERKETTEN(", ";WAHR; FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0")) 

  lauten:   

=TEXTVERKETTEN(", ";WAHR; TEXT(FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0");"TT.MM.JJJJ")) 

   Die Funktion TEXT formatiert eine Zahl (und damit auch ein Datum).

LG Rene

Schwitzt du noch oder klebst du schon?

Nachtrag 2 zur letzten Verion:

Folgendes wurde getestet:

Konto anlegen und löschen funktioniert soweit, bis auf das Problem, was wir schon mal hatten und gelöst wurde (Numerischer Kontoname z.B. 01 -> wird darauf 1 generiert!). Das hatten Sie bereits schon super umgesetzt.

Also formatiere ich die Spalte mit den Kontonummern erneut als Text. Mal sehen, wann Herr L. es wieder „kaputt macht“.

Um zu verstehen, warum manche Menschen überall ihren Senf dazu geben, musst du lernen, wie eine Bratwurst zu denken.

Hallo Herr Martin,

mir ist ein Fehler aufgefallen, der vorher nicht vorhanden war.

Die Excel-Tabelle in Excel 365 funktioniert tadellos, soweit ich getestet habe.

Ein Freund von mir hat leider aus der alten Version die Daten nicht in die neue Version reinbekommen und so hat er sich entschlossen, diese neu einzugeben.

Wenn er ein Konto anlegt – das geht noch.

Dann will er Einzahlungen in das Einzahlformular hinzufügen, dies scheint erst mal nicht zu funktionieren. In der Liste zeigt er nichts an!

Wenn ich mir die Tabelle außerhalb des Formulars anschaue, sind die Daten angelegt – Die Paketnummer wird nicht mehr hochgezählt.

Auch ein speichern und erneuter Start der Tabelle bringt keine Abhilfe.

In der alten Version (bei mir 6.11) funktioniert es noch tadellos. Mein Bekannter setzt Excel 2016 (neuste Updates sind installiert) ein. Bei mir geht es, bei ihm nicht, mit der letzten Version.

#####

Hallo Herr L.,

in Ihrem Programm finde ich die Codezeile:

If rngI.Value = Me.cmbAuswahlKontoAlleInvestments.Value Then

Sie prüfen, ob eine Kontonummer ausgewählt wurde. Da Kontonummern Zahlen sein können (4711) vergleichen Sie diese Zahl mit dem TEXT aus der Combobox (alle Steuerelemente liefern immer Texte).

Deshalb kann es nicht funktionieren! Der Fehler war vorher schon vorhanden; ist Ihnen in IHRER Liste nicht aufgefallen, weil dort alle Konten alphanumerisch ist.

Ich habe es korrigiert:

If CStr(rngI.Value) = Me.cmbAuswahlKontoAlleInvestments.Value Then

Darf ich dich mal nackt fotografieren? – Klar, wenn dir das nicht zu kalt ist, meinetwegen …

Amüsant. Wir kennen das: Wenn ich in Excel eine Zahl schreibe, herunter oder nach rechts (oder nach oben oder links) ziehe, steht in der jede Zelle die gleiche Zahl:

Drückt man die [Strg]-Taste, zählt Excel weiter:

Ebenso kann man das Weiterzählen über das Smarttag erzwingen:

Bei Datumsangaben oder bei Text-Zahl-Gemischen wird hingegen weitergezählt:

Natürlich kann man auch den Assistenten „Datenreihe ausfüllen“ verwenden, den Sie in Start / Bearbeiten / Ausfüllen finden:

Formatiert man allerdings eine Zelle als Text und fügt eine Zahl ein (oder schreibt ein Apostroph vor eine Zahl; zieht nun diese Text-Zahl nach unten oder rechts, so wird jetzt weitergezählt. Ähnlich wie bei Text-Zahl-Gemischen.

Mal sollte öfter mal einen Mutausbruch haben.

Hallo Herr Martin,

vielen Dank für die schnelle Beantwortung meiner Frage.

Ich werde bei den großen Uhrzeiten versuchen in der Formel für den Durchschnittswert die Minuten zu kürzen so dass nur noch mit den Stunden gerechnet wird. Wie ich die Formel erstelle weiss ich noch nicht aber ich werde mal rumprobieren.

Hallo Herr Martin,

ich habe es mit Runden, Kürzen und Ganzzahl versucht und bekomme weiterhin einen Fehler #Wert. Ich denke, ich brauche hier doch noch einmal ihre Hilfe.

Hallo Herr J.,

wenn Sie in Excel etwas in eine Zelle eintragen, müssen Sie die Eingabe mit [Enter] abschließen. Der Grund: Excel prüft, ob Sie Text oder Zahl eingetragen haben: Text steht linksbündig, Zahlen rechtsbündig. Die Eingabe von 1oo oder 1,,50 oder 31-11-2020 wird zu Text und steht linksbündig. Auch: 1000000:30. Das ist keine Zahl; Excel kann damit nicht rechnen; die Funktion ISTTEXT kann das prüfen. Und diese müssen Sie auch verwenden.

Wenn die „zu lange Uhrzeit“ Text ist, müssen Sie sie als Text behandeln, sonst als Uhrzeit.

=WENN(ISTTEXT(B3);WERT(LINKS(B3;SUCHEN(„:“;B3)-1));STUNDE(B3))

Übrigens: Sie haben Ihre „Texte“ rechtsbündig formatiert …

schafft Abhilfe.

Hilft da?

schöne Grüße Rene Martin

Bei meinem Heiligenschein blinkt schon wider die Inspektionsleuchte.

Ach, wie dumm. Eine Fehlermeldung in meinem VBA-Projekt:

Der Grund? Ich lese den Value der Zelle aus. Die Fehlermeldung #NV ist allerdings kein Value. Mit der Eigenschaft „Text“ kann man sie abfangen.

Und wie kommt diese Fehlermeldung nach Excel?

Nun – die Daten wurden aus einer (Access-)Datenbank gezogen. Diese wiederum holt sich die Informationen von Visio. Und dort wurde – durch ungeschicktes Kopieren und Einfügen der Fehler #NA erzeugt. Er wurde in die Datenbank als Text eingetragen – allerdings bei Excel ist ein Fehler die Folge.

Ich habe den Wecker wieder zurückgebracht. Er macht morgens so laute Geräusche. Das raubt mir den Schlaf.

Prosa oder Lyrik?

Das Verhalten von langen Text in Zellen ist folgendes:

Schreibt man einen langen Text in eine unformatierte Zelle, fließt der Text über den rechten Zellenrand hinaus (bei allen Schriften, die von links nach rechts laufen):

Schaltet man einen Textumbruch (früher: Zeilenumbruch) ein, fließt der Text nach unten.

Okay – ich würde wahrscheinlich die Spalte verbreitern.

Schreibt man dagegen einen langen Text in eine Zelle und bricht die Zeilen mit einem Zeilenumbruch ([Alt] + [Enter]) um, schaltet Excel automatisch die Formatierung „Textumbruch“ ein:

Schaltet man den Textumbruch aus, fließt der Text nach rechts weiter über den Zellenrand hinaus. Steht in der Zelle daneben Text (beispielsweise ein Leerzeichen), wird nur der Text der Zelle angezeigt:

Den restlichen Text kann man auch verstecken, indem man die Zeilenhöhe verringert. So kann der Textumbruch eingeschaltet bleiben:

Und die restlichen 12.000 Zeichen werden nicht angezeigt.

Übrigens: die Obergrenze der Zeichenzahl einer Zelle bei Excel liegt bei 32.767 Zeichen. Die maximale Zeilenhöhe bei 409 Punkt:

Tipps für einen flachen Bauch: nur flache Sachen essen. Schokolade zum Beispiel. Oder Pizza.

Hallo Herr Martin,
Das Feld für die Prozessnummer ist zu kurz. Mit 20 Zeichen muss es dann aber passen.

Hallo Herr H.,
voilà – jetzt mit 20 Zeichen Prozessnummer.
schöne Grüße
Rene Martin

PS: Ich traue Ihnen ja nicht. Besser: in vorauseilendem Gehorsam habe ich die Prozessnummer als Text formatiert – sollten Sie mal eine Nummer wie 12345678901234567890 haben würde Excel 1,2345E+19 machen …

Hallo Herr Martin,
Gut mitgedacht! Die Prozessnummern werden mit Punkten getrennt und Excel macht in vorauseilendem Gehorsam ein Datum daraus, wenn es passen könnte. Ich habe das bisher immer mit einem Hochkomma umschifft .

Sommer kommt – fuck the Pullis!

Das Telefon klingelt.
„Hallo René, ich bin’s: Angelika. Hast du mal nen Moment Zeit?“

Kennt ihr Gespräche, die so beginnen? „Hast du mal einen Moment Zeit?“ Das dauert normalerweise sehr, sehr lange. Egal – ich nehme mir die Zeit für Angelika.

„Jep, schieß los – was gibt’s?“
„Ach mein Excel nervt wieder! Ich bereite gerade eine Schulung vor. Excel zeigt mir immer nur die Formeln an, nicht die Ergebnisse. Ich bin völlig verzweifelt; ich weiß nicht, was ich machen soll!“
„Hast du die Formel-Anzeige eingeschaltet? – Formeln / Formeln anzeigen.“
„Ne, da habe ich schon geschaut.“
„Wie sind denn die Zellen formatiert? Wie lautet das Zahlenformat“
„Na – als Standard sind sie formatiert!“
„Mach mal einen Doppelklick auf die Zelle mit der Formel! Und dann [Enter]“
„Ach – jetzt geht es! Was war das?“
„Ich frag mal zurück: was hast du gemacht?“

„Also von vorne. Ich bereite gerade die Excelschulung für nächste Woche vor. Ich habe eine Liste mit Telefonnummern:

Ich füge Spalten ein und zeigen den Teilnehmern den Assistenten Daten / Text in Spalten.

Dann will ich die Teile wieder zusammenbauen. Und: egal, was ich verwendet habe: das &-Zeichen, VERKETTEN, TEXTKETTE, TEXTVERKETTEN … immer die Formel statt das Ergebnis!“

„Klar! Du hast die Telefonnummern als Text formatiert.“ Durch das Spalten Einfügen übernimmst du das Textformat. Und deshalb rechnet die Formel nicht mehr, sondern wird als Text eingefügt.“

„René – danke! Ich wusste doch, dass Excel nervt. Und dass du mir helfen kannst!“

Woran erkennt man, dass man zu viel mit Excel gearbeitet hat? Wenn man die Augen schließt und sich das Excel-Grid in die Netzhaut eingebrannt hat (Daniel Kogan – excelhero)

Verblüffend!

Man schreibe in eine Zelle den Text ‚WAHR. Das Ergebnis wird linksbündig als Text angezeigt. Das Gleiche passiert, wenn man eine Zelle als Text formatiert und anschließend mit den Buchstaben WAHR füllt.

Soweit so gut. Die Überprüfung mit der Funktion ISTTEXT bestätigt, dass ein Text in der Zelle steht; ISTLOG verneint die Existenz eines booleschen Wertes (WAHR oder FALSCH). Auch die Überprüfung mit =Zelle=WAHR und =ZELLE=“WAHR“, verneint das erste und bestätigt das zweite:

Irritierend ist nun, dass die Funktion ZÄHLENWENN 0 liefert, wenn man den TEXT „WAHR“ zählt:

Der Blick in den Funktionsassistenten irritiert:

Verblüffend!

Wenn jemand auf meine schwarzen Klamotten zeigt und mich fragt, wessen Beerdigung heute sei, ist ein Blick durch den Raum und ein „Ich habe mich noch nicht entschieden“ immer eine gute Antwort.

Anders als PowerBI kann man mit PowerQuery in Excel leider noch nicht auf PDFs zugreifen. Microsoft hat versprochen, dieses Feature im Frühjahr 2020 einzubauen. Ich bin gespannt.

Bis dahin: Text aus einem PDF kopieren und in Excel einfügen. Ich schaue erstaunt: Wo ist der Text?

Ich editiere die Zelle ([F2]). Der Text erscheint. Allerdings ohne Leerzeichen davor:

Ein Blick in den Dialog Zellen formatieren / Ausrichtung liefert die Antwort: durch das Kopieren wurde ein Einzug mitkopiert:

Also: Einzug raus und schon ist der Text in der Zelle!

Bei Hagenbeck gewesen. Drei Stunden im Streichelzoo gestanden – nix passiert! Selbst die dicke Ziege wurde mehr befummelt als ich!

Man muss einfach immer genau hinschauen. Immer!

Ich habe eine Excelliste, in der trage ich Informationen zu meinen Schulungen ein. Ich habe eine Spalte „Stunden außerhalb“, in der ich bislang Texte eingetragen habe wie „2 x 24“ oder „3 x 10“. Nun hätte ich gerne die Summe der Tage ermittelt, also die Zahlen, die vor dem „x“ stehen summiert. Nichts leichter als das:

=LINKS(I351;SUCHEN(„x“;I351)-2)

liefert die Zahlen. Allerdings: steht kein Text in der Zelle, so liefert SUCHEN(„x“;I351) einen Fehler. Der kann bequem mit WENNFEHLER abgefangen werden:

=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);““)

Das Ergebnis:

Darauf setzte ich eine Pivottabelle auf und ändere den Vorschlag „ANZAHL“ in „SUMME“:

Eine Fehlermeldung ist die Folge. Vielleicht hängt es mit dem „“ der Funktion WENNFEHLER zusammen? Ich ändere die Formel in:

=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);0)

Die leeren Zellen werden mit einer „0“ aufgefüllt. Sieht nicht schön aus. Also ändere ich das Zahlenformat in ein benutzerdefiniertes:

0;-0;;

Und wundere mich erneut. Alles ist weg!

Langsam dämmert es mir: klar – die Funktion LINKS liefert einen Text. Das heißt: der Text „2“ wird durch das benutzerdefinierte Zahlenformat weggeblendet. Und: Excel erkennt keine Zahlen für die Pivottabelle, die summiert werden könnten. Also noch einmal die Formel anpassen. Der Text muss in eine Zahl konvertiert werden: mit WERT, mit „–“ oder mit „*1“

=WENNFEHLER(WERT(LINKS(I351;SUCHEN(„x“;I351)-2));0)

Und dann kann die Pivottabelle auch summieren:

Aktualisieren nicht vergessen!

Nur noch vier Mal ausschlafen bis zu „wir schenken uns nichts in diesem Jahr“

Die Aufgabe ist einfach. Zu einem Text (einem Buchstaben) sollen Kreissymbole dargestellt werden. Das kann man prima mit der Bedingten Formatierung erledigen. Allerdings: sie akzeptiert keine Texte. Also muss man den Text in eine Zahl umwandeln – beispielsweise mit der Funktion CODE (oder UNICODE). Darauf kann man eine Bedingte Formatierung aufsetzen.

Schluss mit Binsenweisheiten und anderen unlustigen Sprüchen in der Headline!!

Verständlich, aber dennoch erstaunlich: Wenn eine Liste Datumsangaben in der ersten Zeile hat und wenn man diese Liste in eine (intelligente/dynamische) Tabelle verwandelt, so werden diese Datumsangaben zu Text. Klaro – Feldnamen/Überschriften müssen Text sein.

Beim Zurückkonvertieren in einen Bereich bleiben natürlich die Datumsangaben als Text stehen:

Es ist schön, morgens aufzuwachen und als erstes den Menschen zu sehen, den man am meisten liebt. – Ich hätte mir früher einen Spiegel neben das Bette stellen sollen.

Excel-Schulung. Wir üben die WENN-FUNKTION:

=WENN(B5>20;B5*750;“sorry – keine Provision“)

Ich lasse die Werte summieren. Ich lasse den Mittelwert berechnen.

Eine Teilnehmerin meldet sich und sagt, dass sie ein anderes Ergebnis habe:

Ich schaue nach – klar – sie hat die Formel:

=WENN(B5>20;B5*750;0)

Dadurch wird zwar die Summe gleich berechnet; MITTELWERT (und SUMME) übergehen den Text – bei der Zahl 0 wird jedoch die ANZAHL anders berechnet – deshalb das unterschiedliche Ergebnis beim MITTELWERT (=SUMME/ANZAHL).

Echte Männer fahren Traktor

Mit Lorenz Hölscher (http://www.software-dozent.de/) haben wir für unsere Exceltage (www.munich-office-group.de) 2019 einen hervorragenden Dozenten gefunden. Er hat über Themen wie sichere Datenqualität und Dateneingabe, Verbesserungen im VBA-Code referiert. Und er stellte die neuen Array-Funktionen vor, die in Excel nun Einzug gefunden haben.

Auch er kann sich manchmal freche Bemerkungen nicht verkneifen, wenn er fragt, warum in Excel die Funktion TEXT in VBA Format genannt wurde – warum HEUTE() in Access Datum() heißt … Und er machte Witze über Praktikanten, die so etwas implementiert haben – solche Witze machen seine Vorträge nicht nur lehrreich, sondern auch amüsant.

Für mich kein Dessert – ich muss auf meinen Bauch aufpassen! – Ist er nicht schon groß genug, um auf sich selbst aufzupassen?

Sehr geehrter Herr Martin,

Warum beginnt Excel mit der Nummer 3 und hängt die Nummern 1 und 2 hinten an???

Über jeden Hinweis wäre ich dankbar.

Mit freundlichen Grüßen

Hallo Herr K.,

und des Rätsels Lösung finden Sie nicht? Nun – dann will ich es Ihnen „verraten“:

Ein Blick auf Ihre Liste „Rohdaten“ liefert die Antwort: Dort befindet sich neben den Zellen mit einer „1“ und einer „2“ ein Smarttag mit dem Hinweis, dass die Zelle als Text formatiert ist oder ein Apostroph vorangestellt wurde. Da beides nicht der Fall ist liegt die Vermutung nahe, dass Sie diese Daten aus einem System erhalten haben, das diesen Text „darunter“ geschoben hat:

Da es sich um zehn Zahlen handelt, habe ich sie noch einmal eingetragen.

Achtung: Aktualisieren der Pivottabelle auf dem Blatt „Nutzung“ genügt nicht; ich habe die Daten über Entwurf / Berichtslayout „im Tabellenformat anzeigen“ lassen und dann sortiert:

Zur Info: Excel sortiert: 7 < 14 < 104 < hundertvier < sieben < vierzehn. Zuerst Zahl, dann Text

schöne Grüße

Rene Martin

Wenn du beim ersten Date nicht so aussiehst wie die Fotos, die du auf der Dating-Plattform hochgeladen hast, zahlst du so lange die Cocktails, bis du den Fotos ähnlich siehst.

Excelschulung. Wir erstellen Diagramme. Bei mir funktioniert es nicht lautet der Kommentar einer Teilnehmerin:

Ich habe eine Weile gesucht, um herauszufinden, warum die Linie auf dem Nullwert liegt. Die Lösung finde ich in den Optionen:

Die Teilnehmerin hat als Dezimaltrennzeichen den Punkt aktiviert. Dadurch wird die Zahl nicht als Zahl erkannt, sondern als Text. Und da sie die Zellen rechtsbündig formatiert hat … Also – Option ausschalten – und schon funktioniert es:

Darf ich die Brötchen zusammen in eine Tüte packen? – Bitte jedes einzeln! Die haben sich beim letzten Mal fürchterlich gestritten.

In einem Excelforum finde ich die Frage, ob Excel Datumsangaben nicht richtig sortiert:

Die Antwort ist einfach: „Unter“ der Datumszahl befindet sich Text. Auch wenn die Zahlenformatierung korrekt „Datum“ anzeigt, muss das Textformat entfernt werden. Dass es sich um Text handelt kann man leicht mit einem Doppelklick und [Enter] auf eine Zelle verifizieren – dann sortiert Excel korrekt.

In dem Artikel

zeige ich mehrere Lösungsansätze auf, wie man dieses merkwürdige Format entfernen kann.

Alexa – wie ist der Verkehr heute? – Du hast keinen!

Sehr geehrter Herr Martin,

es gibt den Befehl bei Word, wo man mit der Taste F4 den letzten Befehl wiederholen kann. Im Prinzip klappt es auch. Kurioserweise klappt dieser Tastaturbefehl nicht mehr, wenn ich mit der „Formel“: =rand() Text einfüge, dann mit F4 die Wiedereinfügung erreichen will.

Ich habe das an einem anderen Rechner probiert: da gibt es keine Probleme. Nur bei meinem Laptop klappt es nicht. Es kann sein, daß ich aus Versehen eine Tastenkombination tätigte, an die ich mich aber nicht erinnere, die diese Funktion speziell für das Einfügen des Blindtextes deaktivierte.

Hätten Sie eine Idee, was da im Hintergrund passiert sein könnte?

Vielen Dank im voraus für Ihre Antwort.

Mit freundlichen Grüßen

Hallo Herr F.,

ich habe es ausprobiert: [F4] wiederholt in meinem Text auch nicht =rand().

Mir ist aufgefallen, dass die Texteingabe nur manchmal wiederholt wird. Versuchen Sie es: schreiben Sie ein paar Zeilen Text, klicken an eine andere Stelle und drücken dann [F4]. Bei kurzen Texten (Geldbeträge – dafür verwende ich es häufig), klappt es meistens, bei längeren Texten fast nie.

sorry – ich habe keine Ahnung warum

Hallo Herr Martin,

das war aber eine extrem schnelle Antwort. Aber schade, daß Sie keine Antwort haben.

Es könnte vielleicht ein Fehler bei Word 2019 sein; das nennt man wohl einen Bug; denn bei der Version 2016 gibt es keine Probleme mit =rand() und anschließend F4. Ich nehme an, daß Sie auch Word 2019 benutzen.

Nachtrag

Hallo Herr F.,

Sie wissen, dass Sie in die Klammern Werte eintragen können: =rand(10,5) liefert zehn Absätze à zirka fünf Zeilen Text.

Mit Socken über den Boden schlurfen ist auch irgendwie putzen

Dass Excel Text „unter“ eine Zelle schiebt, in der sich Zahlen, Datumswerte oder Uhrzeiten befinden, ist hinlänglich bekannt. So etwas wird normalerweise von (Datenbank-)systemen erzeugt, bei denen der Excelexport schlecht programmiert wurde (SAP & co).

Perfide ist es allerdings, wenn in einer Zelle nicht eine Zahl steht, sondern die Zahl mit einem Gleichheitszeichen beginnt …

Seit Florian Silbereisen zum Traumschiff gegangen ist, sympathisiere ich mit Eisbergen.

Hallo, Herr  Martin,

Das ist ja schon prima – bei mir zeigt die x-Achse nur Zahlen mit Schrittweite 0.5 Tage an, auch bei Umformatierung werden die x-Werte ignoriert, wenn man dann den Wertebereich der x-Achse ändert, ist die Grafik weg 🙁 Die Daten sind 21. Jahrhundert, etwa 90 Tage.

Anbei das Excel mit dem beschriebenen Problem. Das rechte Diagramm ohne Linien ist eine Kopie des linken mit korrekt formatierter X-Achse.

*lach*

da haben Sie etwas Lustiges gemacht:

ich setze auf Ihren Daten ein Diagramm auf und wundere mich, dass in der x-Achse nicht die Datumswerte, sondern 1, 2 und 3 angezeigt werden.

Ich formatiere die x-Achse als Datum und erhalte die Werte 01.01.1900, 02.01.1900, …

Ich werfe einen Blick auf Ihre drei Datumsangaben – sieht alles okay aus. Ich verbreitere die Spalte – und da haben wir es: die Werte stehen linksbündig – das heißt Excel, ein Mensch oder eine Maschine hat Text „unter“ die Datumswerte geschoben. Deshalb kann Excel diese Angaben nicht als Datumszahl interpretieren und verwendet 1, 2 und 3.

Umgekehrt: Wenn Sie nun die Achsenskalierung vom 01-10-2018 bis 28-02-2019 laufen lassen, beiden sich diese drei kleinen Zahl gaaaaaanz weit links.

Lösung: Doppelklick auf die einzelnen Datums-Zellen, damit es wieder ein vernünftiges Datum wird.

Wenn Sie das Phänomen häufig haben, werfen Sie einen Blick auf:

https://www.excel-nervt.de/sap-co/

schöne Grüße aus München

Rene Martin

PS: Jetzt muss ich unsere Exceltage vorbereiten, die im Oktober in München stattfinden werden.

 

Mir wurde ein Modeljob von einer Agentur angeboten. Ich bin das „Vorher“.

Ich weiß, das sollte man nicht machen. Ich lasse den Anwender über eine Inputbox eine Zahl eingeben, speichere sie in einer String-Variablen und schreibe das Ergebnis nach Excel:

Sub ZahlAlsTextEintragen()

Dim s As String

s = InputBox(„Bitte eine Zahl eintragen.“)

ActiveCell.Value = s

End Sub

Das Ergebnis: 1 bleibt 1 (Excel schafft eine korrekte Typkonvertierung). Aus 1,5 und 1,55 werden die Texte 1,5 und 1,55 (sie stehen linksbündig in der Zelle und werden mit einem Smarttag versehen, das „die Zahl in dieser Zelle als Text formatiert ist oder ein Apostroph vorangestellt wurde“.)

Gibt man allerdings 1,555 ein, wird diese Zahl in 1.555 konvertiert. Ups!

Ich komme übrigens aus Ironien, das liegt am sarkastischen Meer.

Man zwingt mich mal wieder libreOffice Calc zu unterrichten. Doch – es gibt (noch) Firmen, Behörden und Institutionen, die diese Tabellenkalkulation einsetzen! Sie ist noch sehr viel inkonsequenter als Excel. Setzt man in Excel ein Apostroph (‚) vor einen Text, eine Zahl oder eine Formel, wird das Ergebnis als Text interpretiert und steht linksbündig in der Zelle. In Calc dagegen werden nur Zahlen als Text interpretiert. Das bedeutet:

‚089

steht korrekt als 089 in der Zelle. Eine Erklärung

=belegt

liefert einen Fehler, das Apostroph liefert keine Abhilfe. Während die Artikelnummer oder Abteilungsbezeichnung 3.1 als Text interpretiert wird, ist 3.1.1 in Calc eine Datumsangabe und wird – wie in Excel – umgewandelt. Hier schafft das Apostroph Abhilfe.

Calc unterscheidet übrigens zwischen „Zelle als Text formatieren“ und dem Apostroph. Denn:

=belegt

kann man mit der Textformatierung angezeigt bekommen. Komisch, oder?

Ich höre, dass der Wein atmen muss. Ich will ihn doch trinken und nicht wiederbeleben.

In Excel kann man keine Datentypen definieren. Okay – man kann Zellen formatieren. Man kann über eine Datenüberprüfung festlegen, dass nur Datumsangaben oder Zahlen in eine Spalte eingetragen werden soll.

Was geschieht aber in folgendem Fall: in einer Personalliste stehen Mitarbeiternamen mit Abteilungsnummern. Die meisten Abteilungsnummern sind numerisch; jedoch nicht alle. Werden diese Daten nach Access importiert, überprüft Access die ersten (16?) Datensätze. Wird beispielsweise in Zeile 8 ein Text gefunden, wird der Spalte (dem Feld) der Datentyp Text zugewiesen.

Befindet sich jedoch der erste Text in Zeile 50, wird von einem Zahlentyp ausgegangen und der nun „falsche“ Wert wird in eine Importfehlertabelle geschrieben.

Auch ein Anfügen an eine vorhandenen Tabelle, in der der Datentyp definiert ist, bringt keinen Erfolg, da zuerst die Access-Prüfung stattfindet und anschließend angefügt wird. Man könnte die Importschritte speichern oder per Programmierung die Excelmappe öffnen und in der entsprechenden Spalte die Formatierung auf Text ändern. Doof!

Danke an Stefan, den Excel-Killer, für diesen Hinweis.

i am not weird i am a limited edition

Es ist so schrecklich! Eigentlich ist das Problem einfach: eine Mitarbeiterin möchte wissen, ob beim Anwender Excel die deutsche oder englische Oberfläche eingeschaltet hat. Per VBA wäre dies kein Problem; allerdings wollen wir eine Lösung entwickeln, die auf Formeln basiert.

Wir beginnen:

=WENN(TEXT(DATUM(2018;1;1);“MMMM“)=“Januar“;“de“;“en“)

Wenn der erste Monat des Jahres „Januar“ heißt, bin ich Deutsch. Test: Excel wird auf englisch umgestellt:

Klar. die Ländereinstellung der Systemsteuerung ist noch immer „deutsch“ – deshalb ist der erste Monat Januar. Blöd zum Testen!

Zweiter Versuch:

=IF(ISERROR(TEXT(TODAY();“dddd“));“de“;“en“)

zeigt „en“ an. Bei der deutschen Oberfläche ebenso. Der Grund:

TEXT(HEUTE();“dddd“)

liefert „dddd“?!? Und eben keinen Fehler.

Noch ein Versuch:

=IF(ISERROR(INFO(„DIRECTORY“));“de“;“en“)

Auch hier weigert sich Excel hartnäckig „deutsch“ zu werden. Der Grund:

INFO(„DIRECTORY“)

funktioniert auch im Deutschen korrekt. Erst

=WENN(ISTFEHLER(INFO(„VERZEICHNIS“));“en“;“de“)

klappt. Das englische Excel kennt „VERZEICHNIS“ nicht. Zum Glück ist diese Funktion volatil, das heißt: wird beim Start von Excel neu berechnet.

Mehrsprachige Umgebungen sind die Hölle! Nicht nur in Excel … Und: das oben genannte Beispiel ist nur Spitze des Eisberges.

Ich kann 3 stimmig singen … laut, falsch und mit voller Begeisterung

Heute im Excel-Coaching in einer Rechtsanwaltskanzlei.

Die Aufgabe: Aus einer Liste von Prozesskosten soll eine Übersicht erstellt werden, wir oft Kosten im Bereich 0 – 10.000 Euro, 10.000 – 20.000 Euro 20.000 – 30.000 Euro und so weiter vorhanden sind. Ich überlege: ZÄHLENWENN oder HÄUFIGKEIT? Ich entscheide mich für eine Pivottabelle. Schnell erstellt, schnell gruppiert – aber: padautz!

„Kann den markierten Bereich nicht gruppieren.“

Dieser Satz hat kein Subjekt. Und auch keine Begründung. Versuche es mehrmals, ziehe, schiebe, lösche … geht nicht. Bis ich ans Ende der Pivottabelle schaue:

Da hat doch tatsächlich jemand Zahlen falsch eingegeben: 49,240.8 oder 11,593.00. Und hat auch noch einige Texte eingetragen: „to be added“, bzw. „t.b.a.“ Also doch ZÄHLENWENN …

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

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

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!

Ich sags jetzt nur noch einmal!

Und immer wieder die Frage, was man tun kann, wenn ein Word-Serienbrief Zahlenformate – konkret Währung und Datumsangaben – nicht mitnimmt.

Die Liste – mit Datum und Währung

Der Serienbrief

Die Vorschau – SO NICHT!

Die erste Lösung: bearbeiten Sie in Word die Formularfelder ([Alt] + [F9]). Fügen Sie einen Schalter ein: \@ für Datum und \# für Zahlen. In Anführungszeichen wird das Format eingegeben:

Man kann den Serienbrief nachformatieren

Die zweite Lösung: Schalten Sie in den Optionen in Word ein:

Dateiformatkonvertierung beim Öffnen bestätigen.

Die Option in Word

Wenn Sie nun einen Serienbrief erstellen und eine Datenquelle hinzufügen, werden Sie nach der Art des Einfügens gefragt. Nachdem Sie die die Option „Alle“ aktiviert haben, wählen Sie aus der Liste „DDE“. Dann werden die Formate übernommen.

DDE!

Die dritte Lösung: In Excel können Sie eine Zahl mit der Funktion TEXT in einen (formatierten) Text verwandeln. Beim zweiten Parameter schalten Sie die Formatanweisung ein, beispielsweise 0,00 oder TT.MM.JJJJ

So geht es auch

Jede Variante hat ihre Vor- und Nachteile. Ich bevorzuge DDE.

Radfahrer aller Länder vereinigt Euch! Ihr habt nichts zu verlieren, außer Euren Ketten!

Böses Excel! Oder besser: Böser Excel-Anwender!

Ich wundere mich: in einer Liste steht der Text „Umsatz“ als Überschrift. Zwischen dem Zellrand und dem ersten Buchstaben klafft allerdings eine kleine Lücke:

20161210luecke01

Das Editieren der Zelle hilft nicht weiter – vor dem „U“ befindet sich kein Leerzeichen:

20161210luecke02

Also gehe ich auf die Suche. Ein Format? Ein Einzug? Ein Blick in den Dialog bringt auch keine Lösung des Problems:

20161210luecke03

Plötzlich entdecke ich das Zahlenformat. Die Zelle mit dem Text ist als „Buchhaltung“ formatiert. Wahrscheinlich hat der Anwender die ganze Spalte (also mit der Überschrift) so formatiert:

20161210luecke04

Zahlenformat „Standard“ – und schon funktioniert es! Der Text sitzt am linken Rand. Ohne Lücke.

20161210luecke05

Größenvergleich

Hallo Rene,

erst einmal sorry für die späte Rückmeldung, ich war im Urlaub. Das Problem mit den Verbindern habe ich mittlerweile gelöst. Jetzt hab ich aber ein weiteres Probelm allerdings diesmal mit Excel.

Ich schreibe Daten aus Visio per VBA Makro nach Excel. In Excel werden die Daten dann aufsteigend nach einer Positionsnummer sortiert. Allerdings hat Excel mit der Sortierung der Positionsnummer einige Probleme. Wenn die Nummern beispielsweise 1, 2, 3, ,4 usw lauten läuft alles gut – bei Zahlen wie 1.1, 1.2, 1.3, …. 1.9 klappt auch noch die Sortierung einwandfrei.

Sobald ich aber Nummern ab 1.10 oder 2.11 benutze wird die Sortierung nicht mehr eingehalten. Es sieht dann beispielsweise wie folgt aus :

1.1
1.10
1.11
1.12
1.2
….

Ich möchte allerdings folgende Sortierung:

1.1
1.2
1.3

1.10
1.11

Hast du eine Idee wie ich das anstellen kann ? Hab im Internet schon etwas gegoogelt allerdings finde ich leider keine Lösung dazu. Achja die Nummern werden als Text behandelt.

Liebe Grüße

Stephan
##########

Hallo Stephan,

Excel unterscheidet bei fast allen Dingen zwischen Text und Zahl: rechnen, Zahlenformat, Dateneingabe, filtern, … und eben auch beim Sortieren. 1.1 und 1.10 wird als Text erkannt und eben wie Text sortiert: Anton < Berta < Cäsar < Dora …

Würde Excel die Zahlen als Zahlen erkennen, würde er ein Datum daraus machen:

01. Januar

01. Januar 2001

und so weiter

Die einzige Lösung, die ich sehe: Gib die Daten in der Form 01.01, 01.02., … 01.10, 01.11, … 01.20, 01.21 … ein. Dann produziert die Textsortierung keine Probleme.

Übrigens: das Gleiche Problem hast Du auch bei Dateinamen, wenn Deine Kamera Bilder mit Dateinamen pic1, pic2, … pic10, pic11, … speichert. Dann gilt: pic1 < pic10 < pic11 < pic2 …

Liebe Grüße

Rene

grafik27

Wenn ich während der Arbeit mit Excel sterbe und in die Hölle komme, wie lange wird es wohl dauern, bis ich merke, dass ich nicht mehr vor Excel sitze?

Mit PowerPivot können Felder aus verschiedenen Tabellen des Datenmodells verknüpft werden. Excel verlangt dabei, dass mindestens eine Tabelle nur eindeutige Werte besitzt (die 1-Seite der 1:n-Beziehung). Falls dies nicht der Fall ist, wird es mit einer Fehlermeldung quittiert.

Bedauerlicherweise werden nicht die Datentypen überprüft. So ist es möglich, dass man Text mit Zahl oder Datum verknüpfen kann. Diese Funktion hätte man auch integrieren können …

Geht nicht!

Geht nicht!

Text mit Zahl - das geht schon !?!

Text mit Zahl – das geht schon !?!

Komisch?

Wirklich komisch. In unserer Firma gibt es Abteilungen. Sie tragen Nummern der Form 1 oder 1.5 oder 1.3.1. Erstaunlicherweise zeigt Excel bei „1“ und bei „1.3.1“ grüne Ecken an. Bei „1.5“ allerdings nicht …

Viele grüne Ecken, aber nicht überall ...

Viele grüne Ecken, aber nicht überall …

Die Antwort finden Sie, wenn Sie mit der Maus über das Smarttag fahren:

Unterschiedliche "Fehler"

Unterschiedliche „Fehler“

Die Antwort:

„1“ wir interpretiert als Zahl 1, die als Text formatiert wurde.

„1.3.1“ wird interpretiert als Datum: 01.03.2001, das als Text formatiert wurde.

„1.5“ – hum – keine Ahnung – eigentlich interpretiert es Excel als Datum (01.05. im aktuellen Jahr). Warum hier kein Smarttag erscheint – keine Ahnung …

Keine gute Kopie?

Heute in der Schulung: Wir duplizieren Formen und beschriften sie. Ein Teilnehmer rief mich zu seinem Platz und fragte mich, warum er eine Form nicht beschriften kann. Bei der ersten war es kein Problem, die anderen lassen sich auch beschriften – lediglich die letzte – dort ließ Excel keinen Text zu:

 

Einer geht nicht.

20160602Bild02

20160602Bild03

Mir fiel schnell auf, dass die ersten Formen die Registerkartengruppe „Zeichentools“ aufweisen, die letzte jedoch „Bildtools“. Wie war das möglich? Wurde doch nur kopiert …

Die Antwort lieferte der Anwender selbst:

Ich glaube, ich habe beim Kopieren und Einfügen die Option „Einfügen“ gewählt …

Wusste ich auch nicht, dass man so aus einer Form ein nicht beschreibbares Bild machen kann.

Des Rätsels Lösung

Des Rätsels Lösung

 

Und wo bleibt die Kompatibilität?

Eine unschöne Sache haben wir in der letzten Visio-Schulung festgestellt. Dort ist es möglich an Shapes (graphische Objekte) Daten zu hängen. Diese kann man definieren – beispielsweise als Currency.

Die Daten in Visio

Die Daten in Visio

Normalerweise funktioniert der Datenexport hervorragen. Nicht jedoch, wenn die Daten im englischsprachigen Visio als Currency definiert werden, vom Typ „Euro“ (€). Wird dann der Export durchgeführt, nimmt Visio das Euro-Symbol als Text mit; die Daten stehen nicht als Zahlen in Excel, sondern als Text. So kann eine Summe nicht gezogen werden.

Schade eigentlich, dass beim Datenaustausch auf internationale Unterschiede nicht geachtet wurde.

Leider keine Summe

Leider keine Summe

today is only yesterday’s tomorrow

Hallo Herr Martin,

ich erhalte regelmäßig Daten von einem System (ORBIS). Diese muss ich anschließend filtern. Nun passiert es regelmäßig, dass er in Den Spalten, in denen sich ein Datum befindet, nicht „Datumsfilter“ anzeigt, sondern „Textfilter“. Ich benötige aber Datumsfilter. Was ist da los?

Falsches Datum

Falsches Datum

Die Antwort: Viele Datenbanksystem „schieben“ beim Excelexport Text unter die Zahlen oder Datumsangaben. Erstaunlicherweise kann man das weder sehen noch per Formatierung ändern.

Die Lösung: Sie müssen diese „falsche“ Datumsspalte markieren und mit dem Assistenten Daten / Text in Spalten in das überführen, was eigentlich drin ist.

Weitere Lösungsansätze finden Sie in meinem Artikel vom 09. Jan 2015 „SAP & co

Komische Uhrzeit

Ich wollte nur einen kleinen S-Bahn-Fahrplan erstellen. Da die S-Bahn alle 20 Minuten fährt, habe ich zwei Spalten mit den Uhrzeiten eingetragen, markiert und rübergezogen. Aber irgendwie will der nicht …

Komische Uhrzeiten

Komische Uhrzeiten

Die Antwort können Sie selbst erkennen: Sie haben keine Uhrzeiten eingetragen, sondern diese als Text gekennzeichnet (’05:11 statt 05:11). Deshalb erkennt Excel diese nicht als Uhrzeiten und zählt nur die „Zahlen“ weiter.

Mag der keine Schweizer?

Hallo zusammen.

Ich brauche nochmal Ihre Hilfe. Ich habe in einer Tabelle Postleitzahlen. Damit Excel die deutschen Postleitzahlen, die mit einer führenden „0“ beginnen, als solche darstellt, habe ich die Funktion TEXT verwendet. Klappt prima.

Ich habe diese Funktion erweitert, damit er mir das Länderkennzeichen „D“ mit der PLZ anzeigt:

=TEXT(G2;“D-00000″)

klappt prima

klappt prima

Wenn ich jedoch mit einer WENN-Funktion überprüfe, ob Österreich oder Schweiz, dann zickt er bei der Schweiz. Warum? Österreich geht doch auch. Die Funktion lautet:

=WENN(I2=“D“;TEXT(G2;“D-00000″);WENN(I2=“CH“;TEXT(G2;“CH-0000″);TEXT(G2;“A-0000″)))

Schweiz - geht nicht!

Schweiz – geht nicht!

Die Antwort: bei den benutzerdefinierten Zahlenformaten sind einige Zeichen reserviert: T, M, J für Tag, Monat und Jahr, H, m, S für Stunde, Minute, Sekunde und einige andere. „D“ und „A“ und „C“ zufälligerweise nicht – aber eben der Buchstabe „H“. Sie müssen DIESEN Text in Anführungszeichen setzen. Da die Funktion TEXT das Format allerdings schon in Anführungszeichen verlangt, müssen die Anführungszeichen doppelt geschrieben werden – dann interpretiert Excel sie als einfaches Anführungszeichen. Es geht also so:

=WENN(I2=“D“;TEXT(G2;“D-00000″);WENN(I2=“CH“;TEXT(G2;“““CH““-0000″);TEXT(G2;“A-0000″)))

so klappt es!

so klappt es!

Heute hier morgen dort …

Ich verstehe es nicht. Ich habe eine Spalte vom Typ „Text“ formatiert und trage dort unsere Abteilungsnummern ein. Ich muss sie als Text formatieren, weil wir Nummern in der Form 4.2, aber auch 4.4.4 oder 3.1.2 haben. Excel würde aus allen Abteilungsnummern ein Datum machen. Erstaunlicherweise zeigt er nur bei den Abteilungen der x und x.x.x eine Fehlerüberprüfung (ein grünes Eck) an. Interessant, nicht wahr?

Das grüne Eck erscheint nicht bei allen Abteilungen

Das grüne Eck erscheint nicht bei allen Abteilungen

Ja, wirklich interessant. Übrigens: Wenn Sie in eine Zelle die Abteilungsnummer mit einem Hochkomma eingeben, also: ‚3.1.1 erhalten Sie auch eine Fehlerüberprüfung, bei 3.1 nicht.

Datümer und andere Irrtümer

Hallo Herr Martin,
Sie haben mir schon einmal bei meiner Excel-Tabelle geholfen, die ich immer noch nutze und sehr gut funktioniert.
Jetzt habe ich eine kleine Frage.
Ich benutze eine Tabelle, die im Datum aufsteigend ist.
In dem Feld B2 ist das Datum, in Feld A2 das Datum von B2 als „TTT“ formatiert.
Jetzt habe ich heute versucht, die Spalte A1 in einen Filter einzubauen.
Ich möchte dadurch zum Beispiel mir alle Montage oder Dienstage anzeigen lassen.
Als ich den Filter gesetzt habe, hat er mir nur die einzelnen Datums angezeigt.
Die waren dann unter 2017 …. dann nach Montaten …. und dann nach dem Tagen im Monat aufgelistet.
Wie kann ich dies Umstellen, das er mir nur die Tage Montag bis Sonntag liefert?
Es ist für die Kursplanung gedacht, die mir dann viel Scrollarbeit abnimmt, wenn ich nur Mittwochskurse habe.
Anbei schicke ich Ihnen die Datei mit, vielleicht ist es dann einfacher zu verstehen.

Filter - so nicht!

Filter – so nicht!

Hallo Herr S.,
das würde ich auch nicht, wenn ich Excel wäre …
Formatieren heißt doch nur „gestalten“, das bedeutet: nicht den Wert, sondern nur das Aussehen der Zelle zu ändern. Also: wenn Sie eine Zelle, in der eine 7 steht fett machen oder mit einer großen Schrift oder rot formatieren, dann befindet sich immer noch der Wert 7 darin.
Das bedeutet: Sie dürfen das Datum nicht formatieren, sondern müssen es in einen Text umwandeln – beispielsweise mit der Funktion TEXT. Dann können Sie die Liste sortieren oder Filtern (siehe Anlage).
schöne Grüße
Rene Martin

Die Funktion TEXT liefert die Lösung

Die Funktion TEXT liefert die Lösung

Eine unerwartete Sortierreihenfolge?!

Eigentlich wollte ich nur die Liste nach den Gerätenamen sortieren.

Eine "normale" Sortierung?

Eine „normale“ Sortierung?

Und dann diese Meldung:

Meldung

Meldung

Der folgende Sortierschlüssel enthält Zahlen, die als Text formatiert wurden und kann daher eine unerwartete Sortierreihenfolge ergeben.

Die Antwort: Sie haben die Spalte, in der die Gerätebezeichnungen stehen, als Text formatiert, was hinsichtlich der Artikelbezeichnungen vernünftig ist. Nun „weiß“ Excel nicht, ob die Zahlen der Größe nach (also nach dem Wert) sortiert werden sollen oder als Texte, das heißt alphabetisch von links nach rechts gelesen.

Übrigens: Interessant: Wenn Sie auf eine Zelle innerhalb der Spalte klicken und das Symbol „Nach Größe sortieren“ verwenden (A↓Z oder Z↓A), erhalten Sie diese Frage nicht.

Grüne Ecken

Ich weiß, dass Excel nicht alles annimmt. Wenn ich eine Telefonvorwahl oder eine Artikelnummer, die mit einer 0 beginnt, eingebe, muss ich ein Apostroph davor setzen – sonst löscht er mir die Null am Anfang. Okay. Ich weiß, dass ich das auch machen muss, wenn ein Text mit einem Gleichheitszeichen, einem Gedankenstrich oder einem Pluszeichen beginnt. Okay. Ich weiß, dass ich unsere Abteilung 2-5 und die Personalnummer 23.12 so auch nicht eintragen darf.

Was ich aber nicht verstehe, warum er manchmal grüne Ecken bringt mit dem Hinweis, dass es sich hier um eine Zahl handelt, die mit einem Apostroph beginnt, manchmal nicht:

Grüne Ecke mit Hinweis

Grüne Ecke mit Hinweis

ecken20150130_1

Kein Hinweis; keine grüne Ecke

Kein Hinweis; keine grüne Ecke

Die Antwort: Excel interpretiert die Zahl 089 als Zahl. Bei -Werbung handelt es sich um einen Text – dieser kann gar nicht in eine Zahl umgewandelt werden. Ich gebe Ihnen recht: dass er 24.12 und 1-4 nicht als DatumsZAHL erkennt, erstaunt. Sollte Ihnen die grünen Ecken nicht gefallen, können Sie diese in den Optionen / Formeln / Fehlerüberprüfung oder Regeln für die Fehlerüberprüfung ausschalten.

Mein Tipp: Lassen Sie sie eingeschaltet – denn sie können praktischen Nutzen haben.

Falsche und richtige Schalttage

Haben Sie einmal folgende amüsante Sache versucht? Tragen Sie in eine Zelle das „falsche“ Datum 29-2-2015 ein. Excel „sucht“ eine interne Zahl dazu; findet keine und stellt das ganze als Text linksbündig in der Zelle dar. So weit, so klar. Wird diese Zelle nun nach unten gezogen, wird die „letzte“ Zahl 2015 weiter gezählt. Auch noch klar. Allerdings existiert der 29-2-2016 (und natürlich der 29-2-2020, …). Obwohl die Zelle mit dem Zahlenformat „Standard“ formatiert ist, wandelt Excel dieses Datum nicht als Datum um. Irgendwie „merkt“ er sich die Textinformation der ersten „falschen“ Zelle. Erst ein Doppelklick auf die Zelle und ein Verlassen mit [Enter] oder [Tab] wandelt nun das Datum in ein Datum um.

Schalttage ...

Schalttage …

Excel -> Text -> Excel

Ich exportiere eine Exceltabelle in eine Textdatei oder eine csv-Datei. Manchmal kann ich sie wieder in Excel importieren – manchmal nicht.

Der Export

Der Export

Der Import - aus den Währungen wird Text.

Der Import – aus den Währungen wird Text.

Das ist ein großes Problem. Leider kann man in Excel keine genauen Exportspezifikationen festlegen – das heißt – wie das Datum und Zahlen (Tausendertrennzeichen, Dezimaltrennzeichen, Formatierungen, …) exportiert werden sollen. Man muss stets ein wenig „probieren“ – ob alles mitgenommen wird – Zahlen, Datumsangaben, Umlaute, …

Machen Sie die Probe aus Exempel und exportieren Sie die Datei in die verschiedenen Textformate – Sie werden sich wundern …

Erstaunliche Unterschiede - kein eineitliches Datenformat

Erstaunliche Unterschiede – kein einheitliches Datenformat

Die bedingte Formatierung funktioniert nicht

Warum kann ich nicht mit der bedingten Formatierung bestimmte Länder hervorheben?

Die bedingte Formatierung funktioniert nicht

Die bedingte Formatierung funktioniert nicht

Wenn Sie eine Formel verwenden, die einen Zelle mit einem Text vergleicht, muss der Text in Anführungszeichen geschrieben werden. Dann funktioniert es:

Texte immer in Anführungszeichen!

Texte immer in Anführungszeichen!

Zugegeben: An vielen Stellen ist Excel nicht konsequent. Wenn Sie sich die Texte über die „Regeln zum Hervorheben von Zellen“ anzeigen lassen, müssen – ja DÜRFEN – Sie keine Anführungszeichen setzen:

Hier ohne Anführungszeichen!

Hier ohne Anführungszeichen!

Excel rechnet falsch

Bei meinem Kollegen funktioniert es – auf meinem Rechner aber nicht!

Ein Kollege hat auf seinem Rechner eine Formel programmiert, die bei einem Geburtstagskind „happy birthday“ anzeigt. Wenn ich diese Datei bei mir öffne, dann sehe ich zwar die Geburtstagskinder aber auch noch viele andere. Warum?

So viele haben heute gar nicht Geburtstag!

So viele haben heute gar nicht Geburtstag!

Die Formel sieht folgendermaßen aus:

=WENN(TEXT(I2;“DD-MM“)=TEXT(HEUTE();“DD-MM“);“happy birthday“;““)

Wenn Sie genau hinschauen, fällt auf, dass das Datum in der Form DD-MM formatiert wurde, also day und month. Mit Sicherheit verwendet der Kollege eine englischsprachige Excelversion. Dort benutzt Excel DMY statt TMJ. Da diese Formatangabe als Text in Anführungszeichen in der Formel steht, wird sie nicht übersetzt (anders als beispielsweise die Formeln – aus SUM wird SUMME, aus VLOOKUP wird SVERWEIS, … Entweder Sie korrigieren die Formel in:

=WENN(TEXT(I2;“TT-MM“)=TEXT(HEUTE();“TT-MM“);“happy birthday“;““)

oder Sie schreiben es ohne die Funktion TEXT, die leider nicht mehrsprachig ist:

=WENN(UND(TAG(I2)=TAG(HEUTE());MONAT(I2)=MONAT(HEUTE()));“happy birthday“;““)

Man muss die Formel ändern oder eine andere Formel verwenden, damit man alle Geburtstagkinder sieht - alle die heute, am 13.01. Geburtstag haben.

Man muss die Formel ändern oder eine andere Formel verwenden, damit man alle Geburtstagkinder sieht – alle die heute, am 13.01. Geburtstag haben.

Text oder Zahl?

Eigentlich müsste man es sofort sehen: Texte stehen in Zellen am linken Rand; Zahlen rechtsbündig. Wenn man sich vertippt, beispielsweise den Buchstaben „O“ statt die Ziffer „0“ oder den Buchstaben „l“ statt der Ziffer „1“ eingibt, kann Excel mit diesen Texten nicht rechnen.

Warum machen Menschen so etwas? Es gab einige Schreibmaschinen, auf denen gab es keine Ziffer „0“ oder keine Ziffer „1“. Dort musste man auf die Buchstaben „O“, beziehungsweise „l“ zurückgreifen.

Bitte kein "O" statt der Ziffer "0"; bitte kein "l" statt der Ziffer "1" eingeben.

Bitte kein „O“ statt der Ziffer „0“; bitte kein „l“ statt der Ziffer „1“ eingeben.