Author Archives: Medardus

2 Uhr, Verkehrskontrolle, zwei sehr junge Polizisten, halbe Kinder. Kopf: „Sag jetzt nichts Falsches!“. Ich: „Wissen eure Eltern, dass ihr noch wach seid?“

Guten Morgen,

Im gleichen „Atemzug“ habe ich dann noch ein Problem mit [=ZELLE(„dateiname“)] gefunden. Das ist scheinbar nicht immer zwingend das aktuelle Workbook, welches dort angezeigt wird bzw. der Inhalt aktualisiert sich nicht automatisch. Wenn zwischenzeitlich eine andere Arbeitsmappe geöffnet war, steht noch deren Pfad im Feld….

Viele Grüße,

Jörn

Hallo Jörn,

ja – ich weiß – ZELLE wird nicht aktualisiert – es gibt da so einige Funktionen in Excel, beispielsweise JETZT(). Mit [F9] oder Formeln / Neu berechnen kann man die Neuberechnung manuell erzwingen.

Liebe Grüße

Rene

Hat jemand die Nummer von der Stiefmutter von Schneewittchen. Ich bräuchte mal ein paar Äpfel.

Kennt ihr das? Eigentlich sollte es nicht so sein. Aber einer der Kunden bestellt unbedingt darauf. Hat auch einen guten Grund dafür.

Der Kunde ist König!

Nun – gut – soll er seinen Willen haben!

In einem sehr umfangreichen Projekt, das mit VBA realisiert wurde, soll eine Auswahl über eine Auswahlliste getroffen werden. Aber eben ein Kunde möchte Freitext haben. Eigentlich widerspricht dies dem Workflow.

Also füge ich unter der Liste ein Textfeld (!) ein und formatiere es so, dass es aussieht als wäre es ein Bezeichnungsfeld. Man muss einige der Eigenschaften ändern:

Das verraten wir natürlich nur einem Kunden. Damit DER Freitext eingeben kann. Weil er es will. Weil er es braucht. Eben: weil der Kunde König ist:

Ich gehe mal raus. Habe gehört, bei dem Sturm fliegt alle 11 Minuten ein Sigle vorbei. Ich orkanshippe jetzt.

Einfach übersehen. PowerQuery-Schulung. Wir wollen auf einen Sharepoint-Ordner zugreifen:

Geht aber nicht. Ich frage meinen Freund und Kollegen Hans-Peter Pfister um Rat. Seine Antwort:

Hoi René

Nur kurz, ohne viel drum-herum, bin gerade unter Wasser.

Nimm den SharePoint Ordner Konnektoren, nicht den für SP-Liste.

Das Leben kann manchmal so einfach sein!

Heute kommt Mutti und bringt zehn Original Thüringer mit. Ich weiß gar nicht, wo die alle schlafen sollen.

PowerQuery-Schulung. Ein Teilnehmer sagt, dass er nicht den gesamte Ordnernamen sehen kann und deshalb nicht den richtigen Ordner deselektieren kann.

„Dann schieben Sie halt die Bildlaufleiste nach rechts“, meine ich. „Geht nicht!“

Was ist pasiert?

Wir üben in der PowerQuery-Schulung den Zugriff auf Ordner:

Der Teilnehmer hat die Dateien (auf OneDrive) in einem sehr, sehr langen Ordnernamen abgelegt. Und wirklich: es ist dann leider nicht mehr möglich, die Bildlaufleiste so zu verschieben, dass ich das rechte Ende des Ordners sehen kann:

Die Lösung: Da ich den Text kenne, der am Ende steht, kann ich den gewünschten Ordner auch über „endet nicht mit“ filtern. DAS klappt.

Superman und Chuck Norris hatten eine Wette. Der Wetteinsatz: der Verlierer muss in Strumpfhosen rum laufen. Der Ausgang ist bekannt.

Gestern PowerQuery-Schulung. Wir üben und probieren den Zugriff: Excelmappen, Textdateien, XML, json, die SQL-Datenbank, Ordner, Web, … alles klappt.

SharePoint?

Der Teilnehmer kopiert seinen Sharepoint-Pfad in das Eingabefeld:

Und noch bevor ich sagen kann, dass er sich über das Microsoft-Konto – drei Zeilen darunter – anmelden muss, erhält er eine Fehlermeldung:

Zweiter Versuch: erneute Anmeldung. Das Resultat: sofortige Fehlermeldung ohne die Möglichkeit sich über das „Microsoft-Konto“ anzumelden. Wie gelangt man wieder dort hinein?

Es dauert eine Weile, bis wir es gefunden haben:

Man muss über die Datenquelleneinstellungen den Pfad löschen:

… dann wird man bei der nächsten Anmeldung wieder nach ALLEN Einstellungsoptionen gefragt.

Ich weiß gar nicht, was du beruflich machst. – Ich auch nicht – ich gehe da einfach hin.

Guten Abend René,
ich grüße zur späten Abendstunde und erlaube mir um diese Uhrzeit noch eine Frage mitzusenden:
Ich möchte lediglich in Erfahrung bringen, welche Möglichkeiten es (nicht) gibt, bzgl. des EXCEL-Solvers:
(1) Funktionieren sollte …
der Einsatz „Solver“ über Ribbon „Daten“/“Solver“, bei geschütztem Tabellenblatt, wenn zusätzlich VBA genutzt wird und beim Makrolauf das Passwort (Tabellenblatt) am Anfang aufgehoben und gegen Makroende wieder gesetzt wird das kann man dem www entnehmen
(2) Nicht funktionstüchtig ist:
Aktivierter Tabellenblattschutz, keine VBA-Nutzung. „Solver“-Einsatz über Ribbon „Daten“/“Solver“
meine heutige Erfahrung
Mir geht es vor allem um Position (2). Die Argumentation beim Kunden:
Da (2) nicht funktionstüchtig ist, muss ich (1) realisieren.
Ein ok zu (1) und (2) wäre nett, sicherlich kennst du die Thematiken von deinen Kunden.
Ein Dankeschön für dein ok & Gruß
Jürgen

Moin, Jürgen,
was macht der Solver? Er liest Werte aus einem Tabellenblatt, rechnet und schreibt Werte zurück. Beziehungsweise schreibt Zwischenergebnisse zurück und prüft, ob sich die Ergebnisse dem gewünschten Ziel annähern.
Das kann auf einem geschützten Blatt nicht funktionieren.
Ich mache es (bei anderen) VBA-Programmen immer so, dass ich den Blattschutz aufheben, Werte eintrage und dann den Schutz wieder setze.
Übrigens: auch ohne zu schreiben – es gibt einige VBA-Befehle, die man auf einem geschützten Blatt nicht ausführen kann, beispielsweise CurrentRegion!?!
Liebe Grüße
Rene

Ich bin jetzt in dem Alter, in dem ein Schneidersitz mit einer dreitägigen Ganzkörperlähmung bestraft wird.

Und ich sage es noch deutlich. Aber er hört nicht.

Listen in Excel sollten eine Überschrift besitzen, wenn man die Listen sortiert und filtert; sie müssen eine Überschrift besitzen, wenn man mit einer Pivottabelle arbeitet.

Der Teilnehmer der Excelschulung hört nicht; erstellt einer Liste, bei der eine Spalte keine Überschrift besitzt:

Das Ergebnis: Excel geht davon aus, dass die Liste keine Überschrift hat und sortiert die erste Zeile ein:

Der Teilnehmer wundert sich.

Man kann es deutlich zeigen, wie Excel diese Liste interpretiert. Die benutzerdefinierte Sortierung zeigt auf, dass keine Überschrift identifiziert wurde:

Nachtrag: bei einer intelligenten Tabelle wäre das nicht passiert. Aber die lernen wir erst später …

Früher bin ich mit zehn Mark in den Laden gegangen. Raus gekommen bin ich mit Bravo, Hubba Bubba, Chips, Cola, Ahoj-Brause, „Brauner Bär“-Eis und mit mindestens zwei Packungen Marlboro. Und heute? – Überall Kameras!

Angelika will’s wissen. Sie möchte gerne die Koordinaten von München

48° 8' 6.45" N 11° 34' 55.132" E 

durch Formatieren von 48080645 und 113455132 erhalten:

Also verwendet sie das benutzerdefinierte Zahlenformat

##.° ##' ##.##''

und erhält leider

Okay – Probleme mit dem Punkt. Also noch einmal:

Also verwendet sie das benutzerdefinierte Zahlenformat

##.° ##' ## . ##''

Klappt, aber ist nicht schön, weil Lücke vor und nach dem Punkt:

Die Lösung: sie muss den Punkt entwerten. Der Backslash tut hier gute Dienste:

##° ##' ##\.##''

Und auch die zweite Zahl:

Die beiden Buchstaben „N“ und „E“ hinzuzufügen stellt kein Problem mehr dar.

Auf der Verpackung des Wildlachses, den ich gerade verzehre, steht: „Fisch mit Zukunft“. Ich will ja nicht schwarzsehen, aber rosig wird diese Zukunft sicherlich nicht …

Auch wenn die bedingte Formatierung in Excel bei Datenbalken, Farbskalen und Symbolsätzen behauptet, sie könne „Formeln“, unterstützt sie jedoch nur absolute Bezüge. Schade!

Ein Teilnehmer der letzten Excelschulung hatte folgendes Problem. Eine Liste von Mitarbeitern und Mitarbeiterinnen nehmen an Fortbildungen teil. In einer Spalte werden die Summen der Stunden, an sie an Fortbildungen teilgenommen haben, aufgelistet. Eigentlich sollten sie bis zum ersten Quartal 15 Stunden absolviert haben, bis Ende des zweiten Quartals 22,5 Stunden, 30 bis Ausgang des dritten Quartals und 45,5 bis Ende des Jahres. Die Hälfte wäre noch okay – wünschenswert ist die volle Stundenzahl.
Nun möchte der Controller durch lustige Fähnchen (grün, gelb und rot) den Status visualisieren. Ein gemischter Bezug der Form =C$2 wäre gut. Allerdings weigert sich die bedingte Formatierung:

Absolute Bezüge werden unterstützt, relative und gemischte leider nicht. Also muss man für die vier Quartale vier Bezüge erstellen. Zum Glück sind es nur vier!

Ich trage Schwarz bei der Arbeit und ein Kollege fragt mich: „Wer wird denn heute beerdigt?“ Ich schaue mich um und sage laut: „Die Entscheidung ist noch nicht gefallen.“ Totenstille.

Excelschulung. Die Frage kenne ich: kann man eine Dropdownliste, die man per Datenüberprüfung erzeugt hat, sortieren:

Die Antwort lautet: „NEIN“. Dafür habe ich mal ein Werkzeug gebaut, das solche Listen sortiert und mit dessen Hilfe man bequem auswählen kann.

Sie finden es auf meiner Homepage:

https://www.compurem.de/?page_id=559

Ohne Brille werde ich oft angesprochen – ich weiß nur leider nicht, von wem.

Excelschulung. Eine Teilnehmerin möchte eine Dropdownliste durch eine Datenüberprüfung haben, in der Smileys angezeigt werden. Ich überlege: in der Schriftart Wingdings gibt es drei Smileys. Man kann sie über Einfügen / Symbol einfügen, oder indem man die Buchstaben J, K und L mit der Schriftart Wingdings formatiert.

Fügt man eine Datenüberprüfung ein, werden jedoch nur die drei Buchstaben dargestellt – auch das Formatieren der Zelle nutzt nichts:

Ich überlege: vielleicht werde ich in den nicht druckbaren Zeichen fündig, die man mit [ALT] + [1], [ALT] + [2], … erzeugen kann. Jedoch finden sich nur zwei Smileys hinter den Nummern 1 und 2:

Mourad hat eine Idee und hilft mir. Im Unicode-Zeichensatz (beispielsweise der Schriftart Calibri) finden sich Smileys:

https://de.wikipedia.org/wiki/Unicodeblock_Smileys?fbclid=IwAR0LB-Y2bptAeo9O0qfDe7QXo_ArCgd8ektotaDSVsFqDC5exITcmk39fbQ

Wenn ich Word die Unicode-Zahl eintrage (beispielsweise U+1F600) und anschließend [ALT] + [C] erhalte ich das dahinter liegende Symbol:

In Excel funktioniert das leider nicht. Muss ich die Zeichen von Word nach Excel kopieren? Quatsch, meint Mourad – du kannst sie doch direkt von der Internetseite nach Excel kopieren:

Oder mit der Funktion UNIZEICHEN umwandeln, also beispielsweise:

=UNIZEICHEN(128512)

Klappt! Und so können wir eine lustige Auswahlliste erstellen:

Auch mein Add-In [Strg] + [Q] funktioniert:

Eine großes Dankeschön an Mourad Louha für die Hilfe.

Meine Lust ist gerade losgegangen meine Motivation zu suchen. Jetzt sind beide weg!

Hallo zusammen,

ich möchte folgendes in Excel durchführen.

Ich habe verschiedene Spalten in Excel mit Daten.

Spalte A:

Name

Spalte B:

Vorname

Spalte C:

Geburtsdatum

Spalte D:

Organisation

Excel soll mir jetzt die Einträge markieren die doppelt sind, jedoch nur wenn Name und Vorname identisch sind sprich Spalte A UND B.

Wie kann ich das am besten lösen.

Doppelte Werte anzeigen ist ja an sich nicht schwierig jedoch weiss ich nicht wie ich in dem o.g. Fall vorgehen muss.

Habt ihr da zufällig eine Lösung

Hallo Jörg,

hast du XVERGLEICH?

Dann könntest du es mit folgender Formel in der bedingten Formatierung lösen:

=WENNNV(XVERGLEICH($A2&$B2;$A3:$A$27&$B3:$B$27;0);0)+WENNNV(XVERGLEICH($A2&$B2;$A1:$A$1&$B1:$B$1;0);0)

wenn in A und B Name und Vorname stehen. Ich fange immer in einer Zelle an; trage dort die Bed. Formatierung ein und erweitere ANSCHLIESSEND den Bereich.

cool. Die Funktion xVergleich kenne ich gar nicht. Vielen Dank für den Tipp

XVERWEIS und XVERGLEICH erweitern SVERWEIS und VERGLEICH. Umgekehrt: ich habe es zuerst mit

=Zählenwenn(A:A&B:B;“Rene Martin“)

versucht – geht aber leider nicht … ZÄHLENWENN versagt hier … Schade!

Vorgestern war mein erster Praktikumstag bei facebook. Lief nicht so gut.

In der Systemsteuerung von Windows findet sich bei den Eigenschaften der Maus in der Registerkarte „Zeigeroptionen“ die Einstellung, dass die Zeigerposition durch Drücken der [Strg]-Taste besser in den Fokus gerückt wird.

Das verwende ich in der letzten Excelschulung, während ich Diagramme erkläre. Allerdings: ständig öffnet sich das Dialogfeld „Diagrammelemente“. Das nervt!

Ich führe gerade mit dem Rad einen Autokorso an. Es wird gehupt und geschrien – die Stimmung ist riesig!

Excelschulung. Wir üben die WENN-Funktion. Ich erkläre, dass man Text in Excel in Anführungszeichen setzen muss – manche Assistenten machen dies automatisch; andere nicht.

Meine Empfehlung: Immer per Hand die Anführungszeichen setzen:

Danach üben wir die bedingte Formatierung – auch eine Art „WENN“. Da die Teilnehmerinnen und Teilnehmer meinen Rat befolgen, tragen Sie den Text in Anführungszeichen ein:

Was passiert? – Nichts! Der Grund:

Excel wandelt den Text „Pandora Papers“ in „““Pandora Papers“““ um – Excel geht davon aus, dass die Anführungszeichen Teil des Suchtextes sind. Also raus damit!

Mädls, hört auf eure Fotos so sehr zu bearbeiten! Stellt euch mal vor, ihr werdet vermisst und man sucht nach Beyoncé, obwohl ihr wie Alf ausseht.

Excelschulung. Wir üben Diagramme. Ein Teilnehmer fragt, ob man denn die Beschriftung von sehr vielen Zahlen im Diagramm automatisch besser darstellen lassen kann, beispielsweise „alternierend oben und unten“ oder: „nur jeden zweiten anzeigen“ oder: „so positionieren, dass sie sich nicht überlassen“.

Meine Antwort: leider nein! So etwas habe ich mir auch schon oft gewünscht. Manchmal muss man jede Zahl einzeln per Maus positionieren.

Rapper rappen, Rockstars rocken, aber was machen eigentlich Popstars?

Hallo in die Runde, Dies ist mein erster Post. Normalerweise, wenn ich einen Bereich als Tabelle formatiert habe, wurde eine die Formel mit dem Drücken der Enter-Taste automatisch bis zum Ende der Tabelle ergänzt. Seit kurzem funktioniert das nicht mehr. Ich muss wohl irgendwas verstellt haben. Könnt ihr mir bitte sagen, wie ich das zurück stelle? Unter einstellungen ist formel erweitern auf automatisch. Aber das löst das Problem nicht. Ich nutze Excel für Mac. Vielen dank für Eure Hilfe Gruß Stephen

Hallo Stephen,

schau mal in der Autokorrektur nach.

LG :: Rene

Ich esse eine Tafel Schoklade meistens innerhalb von drei Minuten auf. Das liegt daran, dass mir wirklich wichtig ist, dass sie frisch ist und nciht so lange geöffnet!

Für eine Firma erstelle eine Feiertagstabelle. Der Lieferant verlangt einen Zuschlag, wenn in der Woche (Mo – Fr) ein Feiertag liegt. Die Basis sind die Feiertage von NRW.

Diese Liste wird auf einem anderen Tabellenblatt verwendet:

Ich klicke auf das Symbol f(x), um die Formel im Funktionsassistenten zu bearbeiten:

Das Ergebnis: „Formelergebnis = Veränderlich“ ?!? Ein Klick auf [OK] und das Meldungsfenster wird geschlossen. Aha!

Immerhin: die Formelüberwachung funktioniert:

Ich hab mich am Bahnhof durch ein mobiles Impfteam impfen lassen und hab jetzt eine Frage: Ist es normal, dass das Zeug mit einem Löffel heiß gemacht wird und warum wollten die 200 Euro, obwohl ich doch krankenversichert bin?

Hallo Rene,

ich hoffe es geht Dir gut! Ich betreue gerade ein ziemlich spannendes Projekt für ein Unternehmen in der Schweiz, dass mich in meinen VBA-Kenntnissen bisher schon ziemlich gefordert aber auch gefördert hat. Jetzt bin ich allerdings an einem Punkt wo ich mit Google und alleinigem überlegen nicht mehr weiterkomme und habe die Hoffnung, dass Du einen Tipp für mich hast.

[…]

Ich habe quasi jeweils eine Liste mit den nach Wunsch aufbereiteten Rohdaten. In dieser soll jetzt an Hand von Daten aus einem Konfigfile (wird wie die Rohdaten über PowerQuery vom Server eingelesen) der entsprechende Filter auf den Verkäufer gesetzt werden und die daraus resultierenden Tabellen in ein neues Dokument exportiert werden. Ist der Vorgang abgeschlossen, kommt der nächste Filter etc. etc. Ich brauche also meines Erachtens einen iterativen Filter der auf Grund eines Kriteriums aus dem Konfigfile erstellt wird.

Leider bekomme ich diesen Part nicht wirklich hin.

Ich hoffe dass meine Mail soweit erstmal nachvollziehbar ist und dass Du vielleicht eine Idee hast, was ich hier noch machen oder an wen ich mich noch wenden kann. Ich weiß leider nicht mehr weiter und der Kunde wartet auf sein Reporttool.

Über eine Rückmeldung von Dir würde ich mich sehr freuen.

Danke Dir und liebe Grüße

Paul

Hallo Paul,

kennst du den Spezialfilter? Hast du schon einmal den AdvancedFilter in VBA benutzt? Ist nicht sehr schnell, aber nur eine Zeile Code um eine Liste durch eine Filterkriteriumsliste zu ziehen.

Verwende ich oft und gerne.

Wer spät zu Bett geht und früh heraus muss, weiß, woher das Wort Morgengrauen kommt.

Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:

„Hallo Rene

zum Thema Datumsberechnungen hätte ich gleich eine Frage:

kann Excel auch Zeiträume erkennen, die sich überschneiden, aber unterschiedliche Anfangs- und Endzeiten haben?

Also zum Beispiel:

Mitarbeiter A arbeitet vom 01.05.2021 bis 31.08.2021

Mitarbeiter B arbeitet vom 01.06.2021 bis 15.09.2021

In welchem Zeitraum haben beide gearbeitet

Oder

Von Bis Thema
8:00 12:00 Nachdenken
11:30 12:30 Pause
12:30 15:00 Nix tun

Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?

Im ersten Schritt habe ich das visualisiert:

Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:

=WENN(B33<$B$21;0)

Dann die umschließenden Bereiche:

=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)

Und schließlich die überschneidenden Bereiche:

Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)

Analog die zweite Überschneidung:

Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:

=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)

Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:

und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:

=WENN(UND(B24<=$B$21;B25<=$B$22;B25>=$B$21);B25-$B$21+1;0)

beziehungsweise:

=WENN(UND(B27>=$B$22;B26>=$B$21;B26<=$B$22);$B$22-B26+1;0)

so:

Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:

  1. Fall: leere Menge
  2. Fall: ganzer Bereich
  3. Fall: Überschneidung
  1. Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)

2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:

=WENN(ODER(B33<$B$21;B32>$B$22);0;WENN(UND(B32>=$B$21;B33<=$B$22);B33-B32+1;0))

2. Fall b: Oktoberfestzeitraum liegt im Datumsbereich:

=WENN(ODER(B31<$B$21;B30>$B$22);0;WENN(UND(B30>=$B$21;B31<=$B$22);B31-B30+1;WENN(UND($B$21>=B30;$B$22<=B31);$B$22-$B$21+1;0)))

Und schließlich die beiden Fälle Nummer 3:

=WENN(ODER(B25<$B$21;B24>$B$22);0;WENN(UND(B24>=$B$21;B25<=$B$22);B25-B24+1;WENN(UND($B$21>=B24;$B$22<=B25);$B$22-$B$21+1;WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0))))

und:

=WENN(ODER(B27<$B$21;B26>$B$22);0;WENN(UND(B26>=$B$21;B27<=$B$22);B27-B26+1;WENN(UND($B$21>=B26;$B$22<=B27);$B$22-$B$21+1;WENN(UND(B26<=$B$21;B27<=$B$22);B27-$B$21+1;WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)))))

Natürlich hätte man die Bedingungen auch umdrehen können.

Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:

  1. Fall: Anfang <= Oktoberfest Anfang? Ja:
    Fall a) Ende vor Oktoberfest Anfang?
    Fall b) Ende nach Oktoberfest Ende?
    Fall c) Ende zwischen Oktoberfest Anfang und Ende?
    […]

Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:

Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:

=SEQUENZ(A6-A5+1;1;A5)

Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:

=ZÄHLENWENN($E$1#;C1)

Und diese Kolonne kann man summieren.

Oder in einer Formel:

=SUMME(ZÄHLENWENN($C$1#;SEQUENZ(A6-A5+1;1;A5)))

Geht das auch mit FILTER? Klar:

=FILTER(C1#;(C1#>=A5)*(C1#<=A6))

Oder in einer Formel:

=FILTER(SEQUENZ(A2-A1+1;1;A1);(SEQUENZ(A2-A1+1;1;A1)>=A5)*(SEQUENZ(A2-A1+1;1;A1)<=A6))

Ich bin sicher, dass es für dieses Problem noch weitere Lösungen gibt. Viel Spaß beim Knobeln.

Hallo Rene,
meine Lösungsvorschlag kommt mit einer einer einfachen Formel (ohne Matrixformel) aus.

Das Startdatum des Oktoberfest steht in B2
Das Enddatum des Oktoberfest steht in B3

Der erste Tag von Adele in München steht in B5
Der letzte Tag von Adele in München steht in B6

Dann berechnet folgende Formel die Überschneidung, also die Anzahl von Tagen die Adele auf das Oktoberfest gehen kann.

=ZEILEN(INDEX(A:A;B2):INDEX(A:A;B3) INDEX(A:A;B5):INDEX(A:A;B6))

Im Falle Adele 13 Tage.

Für die anderen Personen muss diese Formel nur entsprechend angepasst werden.
Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus.
Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.

Salü

Ernst

PS: Nachtrag:

Allerdings kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge eingetragen sind.

Also

=ZEILEN(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6))

Ergebnis = 13

{=ZEILE(INDEX($A:$A;$B$2):INDEX($A:$A;$B$3) INDEX($A:$A;B5):INDEX($A:$A;B6)) }

Ergebnis ( Zellen als kurzes Datum formatiert.):

18.09.2021
19.09.2021
20.09.2021
21.09.2021
22.09.2021
23.09.2021
24.09.2021
25.09.2021
26.09.2021
27.09.2021
28.09.2021
29.09.2021
30.09.2021

Danke Ernst – sehr clever!

Und schließlich hat Helmut Cantzler eine Lösung mit SUMMENPRODUKT gefunden:

=SUMMENPRODUKT((SEQUENZ($B$3-$B$2+1;;$B$2;1)<=B6)*(SEQUENZ($B$3-$B$2+1;;$B$2;1)>=B5))

Danke an Helmut – auch sehr clever!

Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:

    Dim xlBereich1 As Range
    Dim xlBereich2 As Range
    Dim xlSchnittmenge As Range
    With ThisWorkbook.Worksheets("Helmut")
        Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1), 
           .Cells(.Range("B3").Value2, 1))
        Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1), 
           .Cells(.Range("B6").Value2, 1))
    End With
    Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
    MsgBox xlSchnittmenge.Cells.Count

Oder in einem Befehl:

MsgBox Application.Intersect(Range(Cells(Range("B2").Value2, 1), Cells(Range("B3").Value2, 1)), Range(Cells(Range("B5").Value2, 1), Cells(Range("B6").Value2, 1))).Cells.Count

Klappt! Danke an Andreas Protzmann für diesen Hinweis. Auch clever!

Und schließlich reagiert Christian:

Auch sehr clever! Und sehr elegant! Danke, Christian.

In meinem Horoskop stand, dass ein großer Reichtum auf mich zukommen wird. Heute bin ich fast von einem Geldtransporter überfahren worden.

Hallo Rene,

Ich habe für meine Kollegen zur Budgetplanung 2022 je Abteilung ein Excel auf Teams eingestellt.

Das Excel enthält eine Power Query Abfrage auf alle Abteilungs-„Auftragsbücher“, und in PowerPivot ein Datenmodell für die Beziehungen zwischen den Tabellen.

MAC Benutzer scheinen aber Probleme mit der Datei zu haben (s. Screenshots unten)

Wie kann man die volle Funktionalität der Datei auch für MAC Benutzer herstellen?

Es wäre super, wenn Du hier einen Rat hast

Vielen Dank und beste Grüße Katrin

Hi Katrin,

1. Antwort: Mac ist nicht meine Welt – ich habe keinen.

2. Antwort: ich weiß, dass der mac lange Zeit nicht PowerQuery unterstützt hat; soweit ich weiß, kann er das inzwischen.

3. Antwort: der Mac unterstützt (noch) nicht das Datenmodell von Excel.

4. Schau mal:

https://support.microsoft.com/de-de/office/wo-ist-power-pivot-enthalten-aa64e217-4b6e-410b-8337-20b87e1c2a4b

Liebe Grüße

Rene

PS: Danke an Hans-Peter Pfister für den Link.

Immer wenn ich Berichte über Haiattacken sehe, frage ich mich ,wie blöde muss man denn eigentlich sein? Ich meine – das hört man doch schon an der Musik, wenn der Hai näher kommt.

Schöne Frage gestern in der PowerQuery-Schulung:

Warum kann man bei vielen Befehlen, beispielsweise dem Filtern, den Dialog wieder anzeigen lassen, um dort schnell Änderungen vorzunehmen:

Jedoch nicht beim Ändern des Datentyps?

Man müsste die Korrekturen in der M-Codezeile vornehmen oder erneut in den entsprechenden Spalten.

Im Kochbuch steht: Man reibe drei Tage alte Brötchen. Nach einem halben Tag hatte ich die Badewanne und die Nase voll!

Sehr geehrter Dr. Martin,
Wir haben in unserem Unternehmen Probleme bei der Formatierung unserer Statistikauswertung.
Wir formatieren eine Spalte farblich größer als /kleiner als/ zwischen,
Jedoch ist es nicht möglich dieses Vorgehen auf die anderen spalten zu übertragen und ich or müssen somit jede Spalte seperat formatieren.
Ist es möglich das ganze auf alle spalten zu übernehmen?

Hallo Herr R.,

und so funktioniert es. Beginnen Sie bei einer Zelle, beispielsweise links oben. Liegt der Wert dieser Zelle zwischen der Unter- und Obergrenze, soll er grün werden. Die Formel lautet:

=UND(B6>=B$5;B6<=B$3)

UND, weil beide Bedingungen erfüllt sein müssen.

B6 darf kein $-Zeichen haben – diese Zelle ist variabel, soll in der Position geändert werden.

B$5 und B$3 haben ein Dollarzeichen vor der Zeilennummer 3 und 5. Das bedeutet: beim Herunterziehen verändert sich die Zeile nicht – sie bleibt fix. Die Spalte B hat kein $-Zeichen – sie ist wieder variabel oder veränderlich.

Im nächsten Schritt wird der Bereich im Assistenten „Regeln verwalten“ auf den gesamten Bereich ausgedehnt:

Analog beim zweiten Schritt. Man könnte die Werte, die außerhalb liegen, mit zwei bedingten Formatierungen abarbeiten, oder mit einer. Ich entscheide mich für eine Bedingung. Die Formel lautet:

=ODER(B6<B$5;B6>B$3)

Auch hier gibt: B6 ist relativ, in B$3 und B$5 sind die Zeilen 3 und 5 fixiert; jedoch nicht die Spalte B. Und auch hier kann man im zweiten Schritt den Bereich erweitern. Das fertige Ergebnis:

„9 out of 10 forest fires are caused by humans,“ all I hear is, „There’s a bear out there who knows how to use matches.“

Hallo Renè!

Habe mir wieder Arbeit im Verein aufgehalst. 

Eine Tabelle in englischer Sprache enthält in Spalten auch Namen wie Michael, Manuel,

Hueber, Baeuerle, Michaeller  ….

Die machen Schwierigkeiten.

Nach dem Filtern der Namen habe ich mir mit Michael = M1beholfen. Mehrarbeit hat es

gegeben, nachdem ich die Namen nicht gleich aufgeschrieben habe :-(((

Gibt es eine einfachere Methode die Namen in der Tabelle nicht zu ändern?

Die Vollständigkeit lässt sich bei der Funktion ‚wenn‘ schwer überprüfen. 

Bitte um deine bewährte Hilfe.

Vielen Dank,

Peter

Hi Peter,

so ein Problem hatte ich mal – wie soll ein System erkennen, dass Manuel Bauer korrekt ist, Juergen Boese aber umgewandelt werden muss.

Das muss leider (!) händisch gemacht werden – wir haben damals die 5.000 Namen durchgesehen …

Liebe Grüße

Rene

Früher habe mich aus dem Haus geschlichen, um zu Partys zu gehen. Heute schleiche ich mich von den Partys, um nach Hause zu gehen.

Carmen ist verärgert.

Sie erstellt in Excel ein Diagramm, in dem zwei Datenreihen verwendet werden: die Differenz zum Vorjahr und die Absolutwerte. Das Ergebnis der Berechnung wird in einem Säulendiagramm mit zwei dargestellt. Eine Datenreihe wird ausgeblendet – die Beschriftung am oberen Rand dargestellt.

Warum nur, fragt Carmen, warm kann ich die Beschriftungselemente nicht ausrichten?

Ja – das wäre praktisch!

Bevor du mit dem Kopf durch die Wand willst, überlege dir, was du im Nebenzimmer willst.

Schöne Frage, gestern in der Excelschulung:

Ich möchte gerne ein Symbol in die Gruppe „Zahl“ einfügen, mit dessen Hilfe ich eine Zahl als Datum formatieren kann:

Meine Gegenfrage, ob nicht die Tastenkombination [Strg] + [#] gute Dienste tut, wurde verneint. „Ich hätte gerne zwei Symbole – eines für „kurzes Datum“, eines für „langes Datum“ war der Wunsch.“

Makros werden aber bei uns nicht unterstützt …

Schade – ich weiß keinen Ausweg! Keine Lösung für dieses Problem OHNE Makros.

Was gibt es heute zum Essen? – Dasmussdringedweg mit Reis.

Im Moment nervt mich Outlook sehr. Beim Drücken einer Tasten verschwindet die Mail, an der ich schreibe, in den Ordner „Entwürfe“.

Gestern ist mir beim Schreiben einer Mail folgendes passiert: die Zeilen schieben sich übereinander:

Ich klappe die Mail aus und sehe:

Nicht wirklich schön!

Also: Mail speichern; Outlook beenden; Kopf schütteln; Outlook erneut öffnen – dann klappt es!

Seltsam – Outlook nervt auch!

Karotten verbessern die Sehkraft. Bier verdoppelt sie!

Hallo René,

ich lese regelmäßig deinen Blog „Excel nervt“ und hatte dir vor einiger Zeit auch schon einmal eine Mail zu deinem Buch „Excel: Zahlen. Rechnen. Formeln“ geschickt, die du mir sehr freundlich und ausführlich beantwortet hattest.

Heute bräuchte ich mal deine Hilfe. Ich sitze hier vor einem Problem, bei dem ich alleine nicht weiterkomme. Es geht um einen Milchviehbetrieb, der seine Jungtiere von einem Aufzuchtsbetrieb großziehen lässt. Ich soll nun rückwirkend die Aufzuchtskosten pro Monat überprüfen. Dazu steht mir eine Tabelle zur Verfügung mit dem Abgangsdatum der Tiere vom Milchviehbetrieb (= Zugangsdatum Aufzuchtsbetrieb) und dem Zugangsdatum der Tiere beim Milchviehbetrieb. Ist das Tier aktuell noch beim Aufzuchtsbetrieb, ist jeweilige Feld für das Zugangsdatum leer. Pro Tag, den eine Kuh beim Aufzuchtsbetrieb ist, erhält der Aufzuchtsbetrieb eine Pauschale (z.B. 1€ pro Kuh und Tag). Ich hatte irgendwie gedacht, dass man das relativ leicht über eine Formel ermitteln könnte. Problematisch ist vor allem ein angebrochener Monat, wenn beispielsweise eine Kuh am 5. März 2021 an den Aufzuchtsbetrieb geliefert wird, erhält der Aufzuchtsbetrieb für diese Kuh ja theoretisch 27 €.

Anbei habe ich eine Beispieltabelle hinzugefügt. In den Spalten E bis R möchte für den jeweiligen Monat und für jede Kuh die Tage ermitteln, die diese beim Aufzuchtsbetrieb war.

Kannst du mir da weiterhelfen? Irgendwie stehe ich gerade auf dem Schlauch.

Vielen Dank im Voraus.

Hallo,

hübsche Fingerübung.

Ich würde in die erste Zeile jeweils den 01. April 2020, 01. Mai 2020, … und als April 2020, Mai 2020, … formatieren. Also mit MMM JJJJ

Du musst einen „Baum abarbeiten“:

1. Fall: sind Jahr und Monat identisch -> dann rechne die Anzahl Tage bis Ende des Monats.

=WENN(UND(JAHR(E$1)=JAHR($C2);MONAT(E$1)=MONAT($C2));MONATSENDE($C2;0)-$C2+1;0)

Die Erklärung: Ich brauche in diesem Fall die Anzahl der Tage bis zum Ende des Monats. Die Funktion

MONATSENDE($C2;0)

berechnet den letzten Tag des Monats (hier: 30.04.2020). Und davon wird das Datum abgezogen (hier: 11.04.2020). Das Ergebnis lautet 19; plus 1 = 20 = die Anzahl der Tage vom 11. bis zum 30. (beide einschließlich)

2. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem nächsten Monat -> dann voller Monat (die zweite WENN-Funktion wird die Stelle der 0 gesetzt:)

=WENN(UND(JAHR(F$1)=JAHR($C2);MONAT(F$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<F$1;$D2>=G$1);TAG(MONATSENDE(F$1;0));0))

Die Erklärung:

TAG(MONATSENDE(F$1;0))

liefert die Anzahl der Tage des Monats – hier 31 beim Mai.

3. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem Datum (bspw. 01. April) -> Anzahl der Tage des Monats

=WENN(UND(JAHR(M$1)=JAHR($C2);MONAT(M$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<M$1;$D2>=N$1);TAG(MONATSENDE(M$1;0));
WENN(UND($C2<M$1;$D2>M$1);TAG($D2);0)))

Auch hier wird wieder die 0 durch eine neue, dritte WENN-Funktion ersetzt. Die Funktion

TAG($D2)

berechnet die Anzahl der Tage bis zum Tag, also aus dem 17.12.2020 wird die Zahl 17 berechnet.

4. Fall: noch kein Zugang eingetragen und Abgang vor dem Datum (bspw. 01. April) -> dann Tag des Monats

=WENN(UND(JAHR(Q$1)=JAHR($C7);MONAT(Q$1)=MONAT($C7));
MONATSENDE($C7;0)-$C7+1;
WENN(UND($C7<Q$1;$D7>=R$1);TAG(MONATSENDE(Q$1;0));
WENN(UND($C7<Q$1;$D7>Q$1);TAG($D7);
WENN(UND($D7="";$C7<Q$1);TAG(MONATSENDE(Q$1;0));0))))

Die Erklärung: Ebenso wie oben berechnet

TAG(MONATSENDE(Q$1;0)

die Anzahl der Tage des Monats.

Stimmt das?

Hallo Rene,

vielen Dank für die schnelle Antwort.

Für diese Kühe hatte ich die Auswertung händisch vorgenommen und komme auf das gleiche Ergebnis wie mit deiner Formel. Scheint also zu passen.

Einen „Baum“ hatte ich mir auch schon überlegt, allerdings hatte ich Schwierigkeiten bei dem Umgang mit den „angebrochenen“ Monaten und den Kühen ohne Zugangsdatum.

In der ersten Zeile hatte ich sogar schon jeweils den 1. des Monats als Datum eingetragen, weil ich damit rechnen wollte. Ich hatte es dann über „Zellen formatieren…“ – Kategorie: Datum – Typ: Mrz. 12 umgewandelt. Blöd, dass Excel dann beim Mai auch einen Punkt macht, wie mir gerade auffällt.

Vielen, vielen Dank für deine Hilfe und mach weiter so. Ich werde deinen Blog auf jeden Fall weiter verfolgen.

Liebe Grüße

Dringend gesucht! Du bist ein Mann? Kletterst gerne? Hast keine Angst vor Wasser? Dann melde dich schnell – ich suche jemanden zum Fensterputzen.

Amüsiert. Ich erstelle in Excel mit VBA eine Eingabemaske, in der verschiedene Begriffe stehen:

Wenn aus allen drei Listenfeldern etwas ausgewählt wird, werden die drei Begriffe in die entsprechenden Spalten eingetragen.

Klappt.

Danach wird die Auswahl entfernt

ListIndex = -1

Klappt nicht. Der Grund: das Ereignis Click deselektiert die drei Listen und DANN wird der Klick durchgeführt; das heißt: NUN ist ein Eintrag markiert.

Doof!

Ich mache mich auf die Suche, ob eines der Ereignisse ein Parameter Cancel besitzt, mit dessen Hilfe man ihn abbrechen könnte.

Fehlanzeige.

Also noch einmal schauen und probieren. Dann finde ich die Lösung: ich muss Click durch MouseUp ersetzen – DANN funktiert es: zuerst wird der Mausklick durchgeführt und DANACH der Code abgearbeitet (nicht umgekehrt wie beim Ereignis Click:

Private Sub lstRechts_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

Suche eine verheiratete Frau, betrogen, rachsüchtig, die das Auto ihres mannes für 300 Euro verkaufen will.

Amüsant. Muss ich das verstehen?

Ich öffne mein Excel, das auf dem Desktop installiert und darin eine Datei. Ich kopiere einen Teil einer Tabelle nach Excel online, das auf dem SharePoint gespeichert ist.

Allerdings: ich darf nicht über das Kontextmenü einfügen, sondern muss die Tastenkombination(en) verwenden.

Muss ich das verstehen?

Gestern mehrere Krimis im Internet bestellt. Heute Angebot für Messer und Müllsäcke bekommen. Die denken echt mit!

Das ist mir ja noch nie aufgefallen! Eine Teilnehmerin in der letzten Excelschulung hat mich darauf aufmerksam gemacht.

Setzt man in einer (intelligenten) Tabelle den Mauszeiger zwischen Überschrift und erste Zeile, kann man nur die Überschrift markieren:

Ist allerdings die Überschrift markiert …

… führt ein Klick auf die Unterkante dazu, dass die ganze Spalte markiert wird.

Aha – wieder etwas gelernt!

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

Mein Kind isst nicht gerne Fleisch. Wodurch kann ich es ersetzen? – Durch einen Dackel! Hunde essen gerne Fleisch.

Erstaunliches Outlook.

In einem Ordner befinden sich mehrere Hundert Mails. Eine davon ist ungelesen. Ich finde sie nicht. Na – kein Problem, denke ich und füge das Feld „Gelesen“ aus der Feldliste hinzu. Leider kann man DARÜBER nicht sortieren:

Okay – noch ein Versuch: Filtern.

Erstaunlicherweise kann ich nach gelesen/ungelesen filtern:

Geht doch! Trotzdem: seltsam!

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!

Eine der Nudeln schwimmt im Kochtopf oben. Das macht mich wahnsinnig: ist die tot oder was?

Hallo Rene

Es ist unglaublich, aber ich habe wirklich das Gefühl, dass ich in jeden «Sche…sstopf» falle, welchen Microsoft zu bieten hat.

Seit 2 Tagen kämpfe ich mit dem Problem, dass in einer table in jeder Zelle scheinbar versteckte Tabs vorhanden sind.
Dies hat natürlich die traurige Konsequenz, dass damit s- oder wverweise auf diese table kläglich scheitern und zu #NV Fehlern führen.

Zum Problem mit Tabs hast Du ja den Artikel tabulatoren | Excel nervt … (excel-nervt.de) geschrieben, doch in meinem Fall hilft mir dieser (wenigstens im Moment) nicht wirklich weiter.

Ich muss dazu vielleicht etwas ausholen und den Vorgang beschreiben, welcher mich zum Problem geführt hat.
Am Anfang steht Excel File mit einer table. Diese table wird mittels Power Automate in eine SharePoint Online Liste geschrieben.

In einem anderen Excel File werden die Daten der SharePoint Liste wieder mit einer PowerQuery Abfrage eingelesen und stehen somit wieder in einer table, auf welche ich eben mit dem erwähnten wverweis zugreifen möchte. Der Befehl führt eben zu dem #NV und nach langem Suchen, habe ich letztendlich herausgefunden, dass in der abgefragten table in allen Zellen ein tab steht.
Interessanterweise ist es aber so, dass in der table sämtliche Zellwerte linksbündig angezeigt werden. Klicke ich dann bei denjenigen Zellen welche eine Zahl enthalten nicht auf sondern in die Zelle, dann springen die Zahlen nach rechts (ohne dass ich ausser dem Klick in die Zelle etwas anderes mache)
Noch verwirrender (wenigstens für mich) ist die Tatsache, dass die Zellformatierungen danach erhalten bleiben. Ich meine damit, dass diejenigen Zellen in welche ich wie beschrieben einmal reingeklickt habe, auch nach einem reload der Power Query Abfrage erhalten bleiben.

Hast Du vielleicht eine Erklärung für dieses Verhalten?
Wieso und wann wurden die Tabs in die Zellen geschrieben und gibt keine Möglichkeit dies zu beeinflussen?

Bezugnehmend auf Deine vorherige Antwort ist es aber sicherlich schon so, dass man solche Phänomene auch mit der besten Schulung nicht abwenden kann ☹

Würde mich auf jeden Fall darüber freuen, wenn Du eine Idee zu meinem neuen Problem hättest

Lieber Gruss

Hallo Herby,

das Problem ist mir und vielen anderen bekannt – ich würde es nicht als Anomalie, sondern als Bug von Excel bezeichnen.

Wirf mal einen Blick in das PDF in

https://www.compurem.de/buecherdownloads/Zahlenformate.zip

– dort beschreibe ich mehrere Lösungen (mein Liebling ist Daten / Text in Spalten) und auch, wie dieses Phänomen zustande kommt.

Liebe Grüße

Rene

Hallo Rene

Danke für die abermals hilfreiche Unterstützung

Mein Problem schein aber irgendwie anders gelagert zu sein und entgegen meiner vorherigen Problemschilderung ist es leider nicht so, dass der Fehler mit einem Klick in eine der betroffenen Zellen «nachaltig» gelöst wird.
Zur besseren Veranschaulichung habe ich eine Kopie der Tabelle erstell, welche auf der PQ Abfrage beruht. Am Bsp der Zelle B2 kannst Du sehen, dass der Zellwert nach einem Klick in die Zelle, nach rechts gesprungen ist.
Sobald ich das bei irgend einer benötigten Zelle mache, welche einen Zahlenwert enthält, springen die Werte nach rechts und die Formeln mit den darauf referenzierenden Zellen, funktionieren.
Wenn ich hingegen die PQ Abfrage aktualisiere, springen die Zahlen wieder nach links und die Formeln bringen den #NV

D.h die PQ Abfrage erzeugt die falschen Daten und dabei spielt es überhaupt keine Rolle, wie die Zellen formatiert sind.

Die Spalten der Daten Quelle (ShareListe) sind ausnahmslos als standard formatiert und dies lässt sich auch nicht ändern, da innerhalb einer Spalte unterschiedliche Daten vorhanden sind.
Wie bei Excel gibt es beim PQ unter Transformieren/Bereinigen die Trim Funktion, mit welcher eigentlich ein tab aus einer Zelle entfernt werden sollte.
Aber bis dato ist mir dies damit nicht gelungen

Das Problem muss beim erzeugen der Tabelle gelöst werden, da die Daten dynamisch sind und laufend aktualisiert werden. Oder anders ausgedrückt, eine neue Abfrage würde die vormals vorgenommenen Korrekturen mir den Daten überschreiben.

Das File Servicekatalog Quelldaten dient als Datenquelle, das heisst wenn sich irgendwelche Daten vom Servicekatalog geändert haben, werden diese dort eingepflegt. Eine Flow schreibt die Daten in die SharePoint Liste, welche dann wie PQ Abfrage von überall in eine Servicekatalog.xlsx gelesen werden können. Die Quelldatei hat das Problem auf jeden Fall nicht, d.h entweder auf dem SharePoint oder bei anschliessenden PQ Abfrage wird ein problematischer tab angehängt ☹

Vielleicht mache ich einen Denkfehler und/oder Du hast eine Idee, was ich ändern muss

Lieber Gruss

Hallo Herby,

Das Problem ist Folgendes:

In einer Spalten stehen Zahlen und Texte.

Wird diese Liste nach PowerQuery „gezogen“ und dort der Typ nicht explizit angepasst, so bleiben die Zahlen Zahlen (rechtsbündig) und die Texte Texte.

Verwendet man in PowerQuery jedoch den Datentyp „Text“, dann „schiebt“ Excel unter diese Zahlen ein Textformat (das so nicht sichtbar ist).

Da die Zelle als Standard (oder Zahl) formatiert ist, verschwindet das Textformat beim Editieren (Doppelklick) der Zelle. Andererseits: Nach Aktualisierung von PowerQuery haben wir die gleiche Situation wie am Anfang.

Gegenfrage: Warum MÜSSEN in einer Spalte Zahlen und Texte stehen? Das widerspricht einem Datenbankdenken.

Und: wenn schon Zahlen – dann sollten sie auch Texte bleiben – als Informationen und nicht zum Rechnen verwendet werden.

Liebe Grüße

Rene



Ein Pessimist flucht, wenn ihm ein Vogel auf dem Kopf kackt. Ein Optimist freut sich, dass Kühe nicht fliegen können.

Schöne Frage in der letzten Excelschulung:

Wie kann ich es erreichen, dass eine Datenreihe im 15-Minuten-Takt fortgesetzt wird? Beispielsweise für einen Stundenplan. Ich schaue nach:

Erstaunlicherweise lässt der Assistent „Datenreihe“, den man in der Gruppe „Bearbeiten“ in der Registerkarte „Start“ findet, keine Uhrzeiten zu …

Natürlich könnte man es mit einer Formel erreichen:

=A2+15/24/60

Da in der Schulung Anfängerinnen waren, die bislang noch wenig Erfahrung mit Formeln hatten, schlage ich die naheliegende Lösung vor: zwei Startzeiten eintragen, markieren und runterziehen:

Geht auch so!

378 Einkaufswagen. Aber ich nehme den dreibeinigen, nach links ziehenden. Immer.

Wir haben einen Ordner. Nennen wir ihn „Bilanz“. In diesem Ordner liegen zwei Dateien: August.xlsx und September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:

='C:\Users\Rene Martin\Documents\Bilanz\[August.xlsx]Tabelle1'!$D$10

Beide Dateien werden geschlossen, der Ordner wird umbenannt, beispiesweise in „Bilanz2021“. Das Öffnen und Aktualisieren der Datei funktioniert problemlos.

Wird haben einen Ordner. Nennen wir ihn „Bilanz“. Darin befinden zwei weitere Ordner: „August“ und „September“. Im Verzeichnis „August“ befindet sich eine Datei August.xlsx, im September-Verzeichnis eine Datei mit Namen September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:

='C:\Users\Rene Martin\Documents\Bilanz\August\[August.xlsx]Tabelle1'!$D$10

Beide Dateien werden geschlossen, der Ordner „August“ wird umbenannt, beispiesweise in „August2021“. Das Öffnen und Aktualisieren der Datei funktioniert JETZt nicht mehr:

Ärgerlich!

Wie groß können Frösche werden? – Das kommt darauf an, wie oft man mit dem Auto darüber fährt. – Schweigen. – Ich sollte Moderator einer Kindersendung werden.

Manchmal amüsieren und erstaunen mich Fragen in Excelschulungen. Beispielsweise folgende:

In einer Liste stehen Email-Adressen. Um herauszufinden, ob einige der Adressen doppelt vorkommen, wird eine Pivottabelle aufgesetzt, die Adressen werden gruppiert und gezählt:

Die Anzahlspalte wird absteigend sortiert.

Eine Teilnehmerin fragt, warum ein Doppelklick auf einen Eintrag (eine Mailadresse) die Möglichkeit bietet, weitere Details einzublenden, während ein Doppelklick auf die Anzahl diese aggregierte Zahl zu „entfalten“, also alle Datensätze anzuzeigen, die sich dahinter verbergen.

Clevere Frage. Und: ich weiß keine Antwort. Ich kann nur vermuten, warum Microsoft das SO eingerichtet hat.

Im Garten ist eine Schnecke mit Haus und drumherum fünf Nacktschnecken. Vermutlich eine Immobilienbesichtigung.

Excelschulung. Wir erstellen ein Eingabeformular. In einer Zelle steht eine Prozentzahl – sie wird auf Eingabewerte zwischen 0% und 10% festgelegt – also in der Datenüberprüfung werden die Grenzwerte 0 und 0,1 eingetragen:

Ein Teilnehmer probiert aus und trägt die Zahl 25 ein. Das Prozentzeichen bleibt stehen:

Er erhält – wie erwartet – einen Fehlerwert. Die Zahl MIT Prozenzwert werden markiert:

Der Teilnehmer versucht es ein zweites Mal – diesmal trägt er 7,5 ein:

Da er das Prozentzeichen zuvor markiert hatte, wurde es gelöscht – 7,5 ist nun 7,5 und nicht 7,5%. Also erfolgt wieder eine Fehlermeldung!

Hum. Das heißt: man muss schon ganz genau hinschauen, was man einträgt und welche Mechanismen Excel verwendet …

Oh, ein neues Möbelstück. Lass uns darauf anstoßen! – Kleiner Zeh: o ja, gerne!

Eine schöne Frage in der letzten PowerBI-Schulung:

Wie viele Funktionen kann man in DAX ineinander verschachteln. „Genug“ lautete meine Antwort. „Sehr viele“, um etwas präziser zu sein. Ich habe gesucht und nicht gefunden. Erstaunlich. Also habe ich ausprobiert. Aber 100 Ebenen habe ich aufgehört:

Das dürfte genügen.

Fürs Erste.

Neue Ultraschall-Zahnbürste benutzt. Bin jetzt mit 127 Fledermäusen und mit Batman im Badezimmer.

Schöne Frage in der letzten PowerBI-Schulung, auf die ich keine Antwort wusste:

Warum steht bei den Beziehungen in PowerBI eine Kreuzfilterrichtung zur Verfügung:

im Datenmodell in Excel dagegen nicht?

Übrigens: Gute Erklärungen, was Kreizfilterrichtungen sind, finde ich auf:

https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships#understanding-additional-options

und auf:

https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships

Als ich klein war haben mir meine Eltern verboten an den Schrank mit den Putzmitteln zu gehen. Es wirkt bis heute!

Hallo René

anbei die beiden Files (Visio und Excel Tabelle)
Bei der Tabelle handelt es sich um eine Copy aus einem sehr umfangreichen Excel Workbook, welches unter anderem eben die Tabelle produziert, welche die enthaltenen Services (Visio Shapes) steuert. Falls ein Kunden einen Service in einer Ausprägung bekommt, soll das entsprechend Shape auf dem Visio erscheinen.

Ein komischen Phänomen oder Verhalten ist mir bei der Fehlersuche aufgefallen. Wenn ich im Excel File in der Spalte Layer die Formel mit fixen Werten ersetze, dann läuft es irgendwie deutlich besser. Kann es sein, dass Visio im trotz dem vorher benötigten Datenabgleich im Hintergrund noch irgendetwas mit den Daten macht?
Ich habe keine andere Erklärung, wieso es ohne Formeln im Excel in Visio besser funktionieren sollte ???

Wenn ich Deine Bücher zu VBA und Visio Programmierung durchgearbeitet habe, bin ich sicher in der Lage den Visio Update direkt von Excel aus zu steuern

Freundliche Grüsse
Herby

Hallo Herby,

wenn du bei bestimmten Fällen die Variable shapeOnLayer auf True setzt:

                If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
                    shapeOnLayer = True
                Else

und dies später abfragst:

            If shapeOnLayer = False Then
                lyr.Add shp, 0
            End If

muss du am Anfang der Schleife die Variable wieder „zurück“ auf False setzen:

    For iRow = 0 To UBound(rowIDs)
        data = drs.GetRowData(rowIDs(iRow))
        shapeId = data(idColumn)
        lyrName = data(lyrColumn)
        shapeOnLayer = False

Guten Abend René

Zuerst vielen Dank

Meine Programmier Skills sind leider zu bescheiden um richtig folgen zu können. 

Ich werde aber versuchen dies im Script anzupassen und hoffe, dass es dann funzt. Auf jeden Fall ist es super, dass Du die Probleme gefunden hast.

Lieber Gruss und einen schönen Abend (soweit man das mit unserem tollen Somner überhaupt noch wünschen kann) Herby

Ich bin wie Batman. Ich muss oft nachts raus.

Hi Rene,

hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA

Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.

In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.

https://docs.microsoft.com/en-us/office/vba/api/excel.sortfield

Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.

Hallo Johannes,

ich habe mal ein bisschen gewühlt und probiert:

1. Das SortObjekt existiert – allerdings besitzt es keine Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem Klassenmodul einzutragen:

Public WithEvents SO As so…

2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam, dem Anwender die Symbole wegzunehmen.

3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.

Tja!

Hilft das?

Liebe Grüße

Rene

Grundregeln im Restaurant: Alles, was sich bewegt – bedienen! Alles, was sich nicht bewegt: Putzen! Ausnahme: Der Chef. Den nicht putzen!

Sehr geehrter Herr Martin,

mit großem Interesse verfolge ich Ihre Excel-Seminare und mag es sehr von Ihnen neue Dinge zu lernen und mein Wissen zu erweitern. Helfen Sie auch bei speziellen Excel-Problemen? Ich habe hier eine größere Datenbank. Basis sind verschiedene Materialnummer in einer Spalte, welche mehrmals auftreten, da es zu den Materialien mehrere Bestellungen gibt mit unterschiedlichen Konditionen. Mich interessiert pro Materialnummer der Maximalpreis. Ich habe hier an die 80 Materialien und dazu jeweils mehrere Bestellungen. Der Maximalpreis sollte dann in einer zz. Spalte erscheinen. Mit freundlichen Grüßen

Hallo Frau S.,
ich würde eine Pivottabelle erstelle. Gruppieren Sie die Materialnummern und ziehen Sie dann die Preise in das Wertefeld. Ändern Sie in den Wertfeldeinstellungen die Summe in MAX.
Alternative: Wenn Sie Excel in Microsoft 365 haben: mit der Funktion EINDEUTIG erhalten sie die eindeutige Liste der Materialnummern. Mit MAXWENN können Sie das MAX pro ID berechnen lassen. Hilft Ihnen das?
LG :: Rene Martin

Hallo Herr Martin, vielen Dank für die prompte Antwort. Die Pivottabelle ist der Lösung für mich. Entsprechende Seminare dazu stehen noch auf meiner persönlichen Agenda. Für den akuten Fall, haben Sie mir prima geholfen. Grüße,

Ich habe das mit den Menschen wirklich lange versucht. Ich möchte jetzt bitte wieder zurück auf meinen Heimatplaneten.

Outlook-Schulung. Eine Teilnehmerin sagt, dass sie in Outlook über Ansicht den Aufgabenbereich „Aufgaben“ eingebunden hat. Allerdings werden die Aufgaben nur in diesem Aufgabenbereich angezeigt, wenn sie nach Outlook Heute (also auf ihr Postfach) wechselt.

Die Ursache ist schnell gefunden und erklärt: es gibt zwei Arten von Aufgaben: solche, die man selbst erstellt (sie heißen Aufgaben) und Mails, die man zur Nachverfolgung kennzeichnet (auf Wiedervorlage legt).

Damit die zwei unterschiedlichen Aufgaben zusammengefasst sind, kann man die Aufgaben von der Aufgabenliste in die Aufgaben schieben.

Verwirrende Begrifflichkeiten!

Kalorien werden beim Überbacken mit Käse – wie jeder weiß – abgetötet.

Hallo Rene,

einen Tag Freizeit vom Bauunternehmen – und ich habe mich mal Deinem Hinweis zum „Solver“ gewidmet.
Prima Sache.

Nun könnte ich das vielleicht auch gebrauchen und habe mal nachgeschaut.
Leider gelang es mir nicht den Solver ins VBA unterzubringen….

Hast Du eine Idee, was ich vergessen haben könnte?

Mein Office ist 2007, mein Visio 2010.
Der Solver läuft im Excel.

Anbei die Meldungen beim Versuch, den Verweis einzustellen.

Was sagt der Profi??

Hallo Wolfgang,

du musst den Solver zu den AddIns hinzufügen und dann über Extras / Verweise einbinden. Dann stehen dir die Befehle zur Verfügung:

Schau mal auf:

https://peltiertech.com/Excel/SolverVBA.html

Liebe Grüße

Rene

Hallo Rene,

danke noch einmal für Deinen Hinweis!,
allerdings gelingt mit solch ein Bild  (siehe Deine EMail  „Verweise VBAProject“ ) nicht.
Da kommt kein „Solver“ vor. Und das Laden der „SOLVER32.DLL funktioniert zwar, aber Visio kann damit nichts anfangen (wie in meiner ersten Mail) schon geschrieben.

Nun habe ich ein 64-Bit -System. Leider ist aus der kargen Fehlermeldung die Ursache, dass Visio die SOLVER32.DLL nicht mag, nicht zu erklären.
Und eine SOLVER64.DLL gibt es wohl nicht (soweit ich meine Suche auch ausdehnte).

Nun werde ich mich wohl mit der nächsten Microsoft-Unzulänglichkeit auch zufrieden geben müssen.
So ein großer „Laden“ und soviel Mangel (es gibt ja auch so viel Versionen…)

Vergangenheit: Hätte ich im Steuerungsbereich meinen Kunden soviel „Nichtfunktion“ zugemutet – ich glaube, da wäre ich arm dran…


Liebe Grüße
(und möglichst stressfreie Spaziergänge…)

Wolfgang

Hallo Wolfgang,

ich habe in Excel den Solver eingebunden. Mit dem Makrorekorder aufgezeichnet. Dann kannte Excel ihn (in den Verweisen)

Von Visio aus habe ich das (noch) ausprobiert …

Liebe Grüße

Rene

Hallo Wolfgang,

ich habe noch einmal nachgeschaut: du verwendest eine andere Visio-Version als Excel. Kann das die Ursache sein?

Zeichne das Teilchen doch einmal in Excel mit dem Makrorekorder auf. Läuft?

Dann wandle die Befehle in Late Binding (Object) um. Läuft?

Und dann von Visio!

Was passiert?

Liebe Grüße Rene

Intelligente Überschriften zu schreiben, ist gar nicht so einfach. Schon ein einziger Buchstabendreher kann den ganzen Inhalt urinieren.

… und ich kann es nicht lassen, zu mailen, mit den Infos:

  • aktuelle Formel (Konstrukt von gestern), wo noch etwas fehlt:
    =WENN(ANZAHL(J9;L9;N9)=0;““; WENN(ODER(ANZAHL(J9;L9;N9)=2;ANZAHL(J9;L9;N9)=3); MITTELWERT(J9;L9;N9); „“))
  • drei Eingabezellen
  • wenn keine Eingaben in den Zellen >> Zelle mit Ergebnis ist leer
  • der MW wird berechnet, wenn zwei oder drei Zellen Werte enthalten

Frage: Kann man noch prüfen
wenn ODER(Prüfung auf TEXT(J9), Prüfung auf TEXT(L9), Prüfung auf TEXT(N9)): „“
D.h.: Wenn mindestens eine der Zellen Text beinhaltet >>> Ergebnis der Zelle: Nix drinnen
Ich habe schon ein wenig schlechtes Gewissen …
Ich maile noch ein DANKESCHÖN & Gruß
Jürgen

Hallo Jürgen,

deine Bedingungen werden mit

=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);"");"")

abgefangen. Die Funktion MITTELWERT übergeht (ebenso wie SUMME, ANZAHL, MAX, …) Texte. Senn du das explizit abfangen willst, dann beispielsweise so:

=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)<ANZAHL2(J9;L9;N9);"";WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);""));"")

Liebe Grüße

Rene

Haben heute ein Koffein-Shampoo benutzt. Meine Haare sind jetzt schon auf dem Weg zur Arbeit. Ich suche noch die Hose.

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

Die Werte werden kummuliert. In der ersten Zelle steht

=WENN(F15<>"";F15;"")

Darunter befindet sich die Formel:

=WENN(G15<>"";G15+F16;"")

So hatte ich es vor vielen Jahren auch gemacht. Inwzischen löse ich das Problem der kummulierten Werte mit einer Formel, die ich nach unten ziehe:

=SUMME($F$15:F15)

Fazit: Lehrer und Lehrerinnen sollten regelmäßig ihre Übungsaufgaben durchsehen und überprüfen, ob es bessere Lösungestechniken, neue Funktionen gibt, mit denen man Aufgaben eleganer lösen kann und testen, ob sie einige Bedingungen vergessen haben.

Ich achte auf meine Ernährung: Was schmeckt, wird gegessen.

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

Der prozentuale Anteil wird berechnet:

=WENN(UND(A15<>"";B15<>"");D15*100/$D$25;"")

Okay, mit der Funktion

UND(A15<>"";B15<>"")

wird überprüft, ob die Zellen der Spalte A und B gefüllt sind. Eine hübsche Übung, um UND und WENN zu üben.

Dennoch: WENNFEHLER wäre sicherlich besser gewesen, um ALLE Fehler abzufangen.

Aber noch mehr irritiert mich die Berechnung

D15*100/$D$25

Warum wird mit 100 multipliziert? Ich hätte den Anteil von einer Dezimalzahl in eine Prozentzahl formatiert. Und damit weitergerechnet.

Wäre das nicht eher im Sinne von Excel gewesen?

Stellt euch vor, ihr lasst eine Dornenhecke um euer Schloss wachsen, um in Ruhe schlafen zu können und dann kommt ein übermotivierter Prinz und versaut wieder alles.

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

Ich komme bei der Verwendung der Funktion RANG ins Grübeln:

=RANG(B15;$B$15:$B$24;0)

Wenn man diese Funktion in Excel eintippt, sieht man vor dem FUnktionsnamen ein Ausrufezeichen. Das bedeutet, dass diese Funktion durch andere erstetzt wurde und aus Kompatibilitätsgründen noch zur Verfügung steht. Richtig: in Excel 2010 wurd diese Funktion RANG durch RANG.GLEICH und RANG.MITTELW abgelöst. Man findet RANG nun in der Kategorie Kompatibilität:

Das Problem bei der Funktion Rang ist die Antwort auf die Frage, welche Zahl weisen wir zwei gleich großen Werten zu? Also: wenn es den größten Werte zwei Mal gibt? Zählen wir dann 1; 1; 3 (so rechnet RANG.GLEICH und RANG) oder 1,5; 1,5; 3 – so rechnet RANG.MITTELW.

Ich probiere es aus, ändere zwei Werte so, dass sie gleich groß sind. Das Ergebnis: Fehler in der weiteren Berechnung, die nicht abgefangen wurden:

Fazit: vielleicht hätte der Lehrer oder die Lehrerin in den letzten zehn Jahren einmal das Beispiel neu nachrechnen sollen und auf Konsistenz prüfen sollen. Und vor allem: prüfen sollen, ob es inzwischen nicht neue, bessere Funktionen zur Lösung des von ihm oder ihr gestellten Problems gibt.

Warum bestellen wir runde Pizzas in quadratischen Schachteln und essen sie dann als Dreiecke?

Excelschulung. Eine junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:

In Spalte A stehen Lieferantennummern, in Spalte B Umsatzzahlen. In Spalte C wird der Rang berechnet:

=RANG(B15;$B$15:$B$24;0)

Doch schon Spalte D lässt mich stutzen:

=INDEX($A$15:$B$24;(VERGLEICH(1;$C$15:$C$24;0));2)

Darunter befindet sich die Formel:

=INDEX($A$15:$B$24;(VERGLEICH(2;$C$15:$C$24;0));2)

Danach Nummer 3, Nummer 4, … Uff! Kennt der Lehrer oder die Lehrerin nicht die Funktion ZEILE? Ich stutze und wundere mich …

#####

Hallo Rene,

kannst du vielleicht auch deinen Verbesserungsvorschlag für die Formel dazu schreiben. Mir erschließt sich grad nämlich nicht, wie man die Funktion ZEILE hier einbauen soll. Wahrscheinlich steh ich nur auf dem Schlauch…

Lieber Anonymous,

die Funktion ZEILE hat zwei „Gesichter“:

=ZEILE()

liefert die Zeilennummer der aktuellen Zelle. Steht also diese Funktion in C7,liefert =ZEILE() die Zahl 7. Beim Herunterziehen erhalte ich 8, 9, 10, …

Die Funktion der Lehrerin oder des Lehrers

VERGLEICH(1;$C$15:$C$24;0)

sucht den Wert 1 in der Rang-Spalte, also die Zeile, die den größten Wert enthält.

VERGLEICH(2;$C$15:$C$24;0)

sucht den zweitgrößten Wert.

Ich bin sicher, dass die Lehrerin oder der Lehrer die Werte 1, 2, 3, 4, … per Hand getippt hat. Da die Formel in D15 stand, hätte man auch schreiben (und herunterziehen) können:

=INDEX($A$15:$B$24;(VERGLEICH(ZEILE()-14;$C$15:$C$24;0));2)

Auch das ist noch etwas umständlich. Man kann mit ZEILE einen Bezug auf eine Zelle herstellen und von dieser Zeile die Zeilennummer ermitteln. Also:

=ZEILE(A1)

liefert 1. Und beim Herunterziehen erhält man die Werte1, 2, 3, 4, die den größten, zweitgrößten, drittgrößten, … Wert liefert. Also:

=INDEX($A$15:$B$24;(VERGLEICH(ZEILE(A1);$C$15:$C$24;0));2)

Entweder finden Jogger Leichen oder sie werden selbst ermordet. – Was soll daran gesund sein?

Excelschulung. EIne junge Teilnehmerin. Azubi.

Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:

Ich sehe folgende Formel:

=WENN(A17="";"";SVERWEIS(A17;Leistung;2))

Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?

Sicherlich dient diese Formel WENN zum Üben …

Intelligente Heizung, intelligente Zahnbürste, intelligente Roboter, intelligente Kamera … ich fänd intelligente Menschen total gut!

In der letzten Outlook-Schulung erzählt mir ein Teilnehmer, dass er gerne mit Kategorien arbeitet. Er hat sich mehrere Kategorien angelegt und weist den Mails diese Kategorien zu. Manche Mails liegen auch auf zwei Kategorien. Dann sortiert (also gruppiert) er nach Kategorien. Soweit so gut.

Nun möchte er eine Mail aus einer Kategorie löschen. Diese Mail steht jedoch ein zweites Mal in einer anderen Kategorie. DORT soll sie jedoch nicht gelöscht werden. Er will auch nicht die Kategorie von der Mail entfernen, da die Kategorie im Archivordner noch benötigt wird.

Er hat die Antwort selbst gegeben: Er kopiert die Mail, so dass sie zwei Mal vorhanden ist. Eine andere Lösung habe ich auch nicht gefunden.

Ich habe mir jetzt ein Seniorennavi zugelegt. Es sagt mir nicht nur die Strecke und das Ziel an, sondern auch, was ich dort wollte.

Hallo Herr Martin,

ich habe eine Frage zum Liniendiagramm, leider stimmen meine Werte nicht und ich bekomme es nicht hin ohne das die Linien aus meinem Diagramm verschwinden.

Vielleicht könne sie mir spontan helfen.

Danke

Hallo Frau D.,

Sie müssen die Reihe mit den „großen Zahlen“, also Jergl, Michal, Hans und Veitli auf eine Sekundärachse legen. Und dann möglicherweise die Skalierung der beiden Achsen anpassen.

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

Wenn du einen Mann verstehen willst, muss du denken wie ein Mann! – Hä? – Das ist schon mal gut für den Anfang!

Moin René

Kurze Frage:

Ich suche gerade nach einer Lösung, finde aber nichts. Vielleicht weisst du was dazu.

Wenn ich alle Spalten ausblenden, so wird der „fehlende“ Bereich mit einem grauen Hintergrund ersetzt.

Frage: wie kann ich die Farbe anpassen? Also statt grau bspw. weiss?

GLG Hp

schöne Frage, HP. – m.W. kann man DAS nicht anpassen.

Die Farbe wird über das Office-Design festgelegt, das man über Datei / Optionen / Allgemein festlegen kann.

MICH hat es bislang noch nie gestört …

Die Mehrheit der Affen bezweifelt, dass die Menschen von ihnen abstammen.

Hätten Sie es gewusst? Gestern kam eine Frage in der Excelschulung … ich war mir nicht sicher. Also: schnell ausprobieren!

Kann man in einem geschützten Tabellenblatt Kommentare und Notizen eintragen, lautete die interessante Frage?

Ich probiere: Zellen nicht sperren (über den Dialog „Zellen formatieren“ und anschließend Blatt schützen:

Das Ergebnis:

Man kann Kommentare eintragen – keine Notizen!

Noch ein Versuch: ich schütze das Blatt und aktiviere die Option „Objekte bearbeiten“:

DANN kann man auch Notizen einfügen:

Haben Sie es gewusst? Ich nicht …

Ich schmecke gar nichts – oh mein Gott – ich habe Corona! – Immer dieselbst Show, wenn es Tofu gibt.

Guten Tag René

Der Kunde hat folgende Fehlermeldung 

Image.jpeg

Hast du eine Vermutung was das sein könnte?

Bei mir ist es gelaufen. 

LG Tanja

##########

ja, Tanja,

der Kunde hat ein sehr „altes“ Excel (Excel 2013? Excel 2016?). Ich verwende die Funktion TEXTKETTE (in VBA: CONCAT). Genauer: mit dem Befehl

    Do Until Len(Application.WorksheetFunction.Concat(xlBlatt.Range(xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 0), xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 8)))) = 0
        lngAnzahl = lngAnzahl + 1
    Loop ' -- wie viele Zeilen sind gefüllt (wird in der Variable lngAnzahl gespeichert)

überprüfe ich, in wie vielen Zeilen der Liste etwas steht, beziehungsweise, ich suche die Zeile, in der die ersten neun Spalten leer sind oder ob die Formeln, die darin stehen, „“ ergeben.

Ich habe es geändert. Könntest du ihnen bitte diese Version schicken

Liebe Grüße

Rene

Auf meiner Waage wütet ein Unwetter. Ein Zunahmi!

Ich bin verblüfft. Und irritiert.

Nicht nur Excel nervt – manchmal auch andere. Gestern hat mich Outlook erstaunt.

Outlook-Schulung. Wir sortieren unsere Mails nach Kategorien und nach den Fähnchen zum Nachverfolgen, indem ich auf die Überschrift klicke:

Eine Teilnehmerin möchte nach beidem sortieren: zuerst nach Kennzeichnungsstatus und anschließend nach Kategorie. Kein Problem: Über die Registerkarte Ansicht öffne ich die Ansichtseinstellungen und klicke dort auf die Schaltfläche „Sortieren“. Ich sortiere nach „Kennzeichnungsstatus“ und anschließend? Ich finde die „Kategorien“ nicht.

Okay, in den „verfügbaren Feldern“ gibt es „Alle Dokumentfelder“. Und dort „Kategorien“. Aber das führt zur Frage, ob ich eine neue Spalte hinzufügen möchte:

Ein Klick auf „Ja“ und ich habe eine weitere leere Kategorienspalte.

Erstaunlich: beim Suchen-Dialog gibt es weder eine Liste „Alle Felder“ noch kann ich die Kategien finden, um danach zu sortieren.

Und: ein erster Klick auf die Überschrift „Kategorie“ und ein zweiter auf „Kennzeichnungsstatus“ bewirkt nicht das Gewünschte: Outlook hat eigene Sortiervorstellungen.

Habe ich etwas übersehen? Oder nervt Outlook auch? Manchmal.

Werbung: „8 von 10 Kunden benutzen DIESEN Staubsauger zum Saugen.“ – Ich frage mich gerade: was mache die anderen beiden Kunden mit DIESEM Staubsauger?

Erstaunlich. Das ist mir noch nie aufgefallen. War das schon immer?

Erstellt man in Excel eine Datenüberprüfung mit einer Liste und trägt die einzelnen Werte in das Textfeld als Quelle ein

kann man die Texte gefolgt von einem Leerzeichen eingeben:

Verwendet man dagegen als Liste die Werte einiger Zellen, die ausgewählt werden:

kann man die Texte NICHT gefolgt von einem Leerzeichen eintragen. SO sollte es doch eigentlich sein, oder?

Wenn man Kindern erzählt, dass Toffifee aus karamellisierten Kuhaugen gemacht wird, kann man die Schachtel alleine leeressen.

Gestern in der Excelschulung. Interessante Frage: Wenn ich ein Datum herunterziehe, ist die Schrittweise ein Tag. Wenn ich eine Uhrzeit herunterziehe ist die Schrittweise eine Stunde. Kann ich das in Excel einstellen, dass Excel im 15-Minuten-Takt zählt?

Ich überlege. Da gibt es doch den Assistenten Ausfüllen / Datenreihe im Register „Start“:

Allerdings: dort kann man nur die Schrittweite für Datumsangaben – nicht für Uhrzeiten eintragen:

Damit bleibt nur:

  • Entweder zwei Uhrzeiten eintragen, markieren und herunterziehen
  • Oder eine Formel: Bezug auf die obere Zelle und dann plus 15 (Minute) / 24 (Stunden) / 60 (Minuten):

Solange mir niemand das Wasser reichen kann, nehme ich das Bier.

Excelstammtisch. Hartmut zeigt, dass man das Datenmodell von Excel nach PowerBI importieren kann.

Ich frage, ob er wisse, wann das zu Problemen führt. Und zeige eine Datei:

Darin befinden sich Tabellen, die ins Datenmodell geladen wurden. Mit Hilfe des Datenmodells wurde eine Pivottabelle erstellt. Die Tabellen wurden mit Measures angereichert und sind untereinander verknüpft.

Nun will ich diese Datei (genauer: die Daten, Verknüpfungen und Measures) nach PowerBI importieren:

Ich erhalte eine Fehlermeldung – fast nichts wird importiert:

Wir machen uns auf die Suche – Hartmut wird fündig. Man darf nicht die Daten in Tabellen in der Arbeitsmappe halten und diese ins Datenmodell laden, sondern man muss sie mit PowerQuery importieren. So:

Diese Daten werden nun ins Datenmodell geladen – dort kann man sie verknüpfen

und mit Measures anreichern:

Das Ergebnis:

Ein erneuter Import nach PowerBI Desktop:

Klappt!

Ein Dankeschön an Hartmut Hilbich für das Suchen und Auffinden der Lösung des Importproblems. Hartmut schreibt dazu:

„Das Problem bestand hier (besteht!) darin, dass PBID das Datenmodell selbst sehr wohl importiert, aber nicht gleichzeitig auch die Quelltabellen!

Ich habe die Quelltabelle mit PQ abgefragt und das PP-Modell exemplarisch mit 2 Measures versehen. Der Import in PBID funktioniert dann einwandfrei!

Mein Fazit: Es ist nicht ratsam, die Quelldaten physikalisch gemeinsam mit dem PP-Modell zu speichern. Also entweder die Daten direkt mit PP abfragen, oder aber (besser) mit PQ abfragen. Was also innerhalb von PP kein Problem ist, wird dann aber eines beim Import in PBID.

Good to know!“

Ich habe schon wieder etwas im Internet bestellt: Etwas Praktisches für die Küche: High Heels. Damit ich oben ans Gewürzregal rankomme.

So schwierig kann das wohl nicht sein, dachte ich. Und probierte es. Allerdings: die Lösung des Problems war doch komplizierter als gedacht.

Vor einigen Jahren hatte ich die Aufgabe in einer sehr großen Excelliste (zirka 60.000 Zeilen) die Daten „zu putzen“. Mitarbeiterinnen und Mitarbeiter hatten an unterschiedlichen Stellen in einer Spalte Informationen eingetragen – allerdings mehrere Informationen getrennt durch Trennzeichen. Durch verschiedene Trennzeichen – mal ein „/“, mal ein Semikolon, mal ein „:::“, mal ein „-„:

Ich habe damals einige VBA-Makros geschrieben, um die Daten „zu putzen“. Ich frage mich, ob man sie mit PowerQuery bereinigen kann. Man kann!

Ich erstelle eine Liste der Trennzeichen:

Ich importiere die Daten und trenne die Liste „hart“ an einem Zeichen:

Der Befehl

= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"PK.1", "PK.2", "PK.3"})

trennt die Spalte. Wie kann man alle Trennzeichen verwenden? Ich importiere die Trennzeichenliste und wandle sie in über Transformieren / In Liste konvertieren in eine Liste um:

Kann SplitTextByDelimiter meine tbl_Trennzeichen verarbeiten? Nein!

Ich gehe auf die Suche:

SplitTextByAnyDelimiter kann die Liste verarbeiten:

= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), {"PK.1", "PK.2", "PK.3"})

Jedoch: {„PK.1“, „PK.2“, „PK.3“} legt fest, dass DREI neue Spalten geliefert werden mit den Namen „PK.1“, „PK.2“ und „PK.3“. Ich probiere aus:

= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), {"PK.1", "PK.2", "PK.3", "PK.98", "PK.99"})

Klappt! Ich erhalte weitere Spalten:

Allerdings: Wie viele Spalten entstehen denn? Ich versuche es ohne den letzten Parameter:

= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen))

Und erhalte EINE neue Spalte:

Schlecht! Ich schaue den Parameter genauer an – er heißt:

columnNamesOrNumber

Also versuche ich eine Zahl. Ich beginne bei 99:

Der linke Teil sieht vielversprechend aus:

– der rechte nicht:

Ich überlege: ich muss berechnen wie viele neue Spalten erzeugt werden. Ich muss berechnen wie oft die Trennzeichen der Liste tbl_Trennzeichen in jedem der Texte vorkommt.

Leider stellt PowerQuery keine Funktion zur Verfügung, mit deren Hilfe man die Anzahl der vorkommenden Zeichen in einer anderen Zeichenkette ermitteln kann. So etwas berechne ich (auch in Excel) immer wie folgt:

Länge(Zeichenkette) - Länge(Zeichenkette ohne gesuchten Zeichen)

oder

Länge(Zeichenkette) - Länge(Ersetze(Zeichenkette; gesuchten Zeichen))

Eine PowerQuery-Abfrage muss her:

(Text.Length(Text) - 
                Text.Length(Text.Replace(Text -

Ich überlege: minus jedes Element der Liste. Also genauer:

(Text as text) =>

     List.Accumulate(
         tbl_Trennzeichen,
         0,
         (state, current) => 
            state + 
                (Text.Length(Text) - 
                Text.Length(Text.Replace(Text, current, ""))) 
    ) 

Ich muss die Anzahl kumulieren. Der Befehl List.Accumulate tut gute Dienste. Er möchte eine Liste haben (tbl_Trennzeichen), einen Beginn (0) und eine Funktion. Diese Funktion erhält zwei Teile:

(state, current)

Die Variable state „merkt“ sich die Zahl, current greift auf jede Zeile zu. Allerdings darf ich nicht einfach die Differenz aus Länge vorher und Länge nachher bilden:

Text.Length(Text) - 
                Text.Length(Text.Replace(Text, current, ""))

sondern muss durch die Länge teilen. Also wenn ich von der Länge

Hallo:::ich:::bin:::es:::wieder

31 die ::: entferne:

Halloichbineswieder

bleiben 19 Zeichen. Differenz = 12. Da aber nur vier Mal das ::: auftaucht, muss ich es noch durch die Länge teilen, also:

(Text.Length(Text) - 
                Text.Length(Text.Replace(Text, current, ""))) 
                / Text.Length(current)

Und schließlich: Wenn ich in ich-will-das zwei Bindestriche finde, erhalte ich nach dem Trennen DREI Teile.

Die komplette Funktion, die ich fxAnzahlTrennzeichen nenne sieht dann so aus:

(Text as text) =>

     List.Accumulate(
         tbl_Trennzeichen,
         0,
         (state, current) => 
            state + 
                (Text.Length(Text) - 
                Text.Length(Text.Replace(Text, current, ""))) 
                / Text.Length(current)
    ) + 1

Und kann verwendet werden:

Von dieser Spalte wird das Maximum berechnet und über ein Drilldown als Zahl „gespeichert“ (MAXZeichen):

Ein Klick auf fx und ein Bezug wird zu einem vorhergehenden Schritt hergestellt:

= #"Geänderter Typ"

Dort wird getrennt:

= Table.SplitColumn(Benutzerdefiniert1, "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), MAXZeichen )

Und in Excel:

Probe aufs Exempel – ich trage in der Liste den folgenden Text ein:

A-B-C-D-E-F-G-H-I

und aktualisiere:

Klappt!

Witzig – noch während des Schreibens und Zwischenspeichern erhalte ich die Info, dass Oz du Soleil (zeitgleich mit mir) auch eine Lösung gepostet hat:

https://www.youtube.com/watch?v=EiHDsZxJ_EI

Er beschreitet einen ganz anderen Weg …

Bei diesen Temperaturen Bier in einem geschlossenen Auto zu lassen – das ist schon Bierquälerei!

Kennen Sie den Median? Die Excel-Funktion MEDIAN?

Gemeint ist – umgangssprachlich – der mittlere Wert einer Datenreihe. Hat man zwei mittlere Werte (bei einer geraden Anzahl Elemente), berechnet sich der Median als Durchschnitt der beiden mittleren Werte.

Wer braucht denn so etwas?

Stellen Sie sich vor, sie haben eine Basketballmannschaft, die aus fünf Spielern besteht: vier recht kleinen und einem sehr langen Spieler. Der Mittelwert würde das Bild „verzerren“, weil der Ausreißer den Durchschnitt nach oben verschiebt. Der Median gibt dagegen ein besseres Bild der Mitte, weil er resistent gegenüber Ausreißern ist:

Median – eine statistische Größe also. Nun finde ich in einer Berechnung von Transportkosten folgende Formel:

=WENN(G15="";"";WENN(UND(ODER(MAX(Logistikdaten!I38:K38)>120;MEDIAN(Logistikdaten!I38:K38)>60;MIN(Logistikdaten!I38:K38)>60);$G$15<=440);"Ja";"Nein"))

Verwundert reibe ich die Augen? Warum werden die Logistikkosten mit einem Median berechnet?

Dann verstehe ich: es wird Bezug genommen auf die drei Werte Länge, Breite und Höhe. Von diesen drei Werten wird der größte Wert (MAX), der kleinste Wert (MIN) und der mittlere Wert (MEDIAN) berechnet. Sehr clever!

Dieser Wein schmeckt nach Waldboden, Eicheln und einem Hauch von Trüffeln. – Na, dann schütt ihn halt weg!

Hallo Herr Martin,

das sieht erst mal gut aus.
Auch das löschen scheint schneller zu funktionieren.

Werde die Version zum Testen meinem Bekannten schicken.
Wer einer ein Fehler findet dann er.

Habe gesehen, dass man das Konto „NEU“ löschen kann. Habe ich aus Versehen gemacht.

Danach kann man keine Konto mehr hinzufügen, da die Schaltfläche dann ausgegraut ist.
Kann man das noch verbessern?

Hallo Herr L.

den Ändern-Button hatten Sie dynamisch bei „(Neu)“ aktiviert und deaktiviert – den Löschen-Button wohl vergessen. Ist auch drin.

Hallo Herr Martin,

Sie haben vollkommen Recht.

Der Fehler war vorher schon da. Ist mir nie aufgefallen, da ich nie Konten bei mir selbst gelöscht hatte.

#####

Fazit: Wie mein Lorenz: ich solle auch einen Blog aufmachen mit dem Titel kunde-nervt …

Sonnenaufgänge sind ja echt schön, liegen aber zeitlich total ungünstig …

Hallo Herr Martin,

habe noch einen Fehler gefunden.

„Kompilierungsfehler im ausgeblendeten Modul. Dieser Fehler tritt häufig auf, wenn der Code nicht mehr mit der Version, Plattform oder Architektur dieser Anwendung kompatibel ist. Klicken Sie auf „Hilfe“, um Informationen dazu zu erhalten, wie Sie diesen Fehler beheben können.“

Der Fehler entsteht, wenn ich im Code im Modul basKonstanten die Versionsnummer ändern will.

Nach Änderung und Neustart der Tabelle kommt o.g. Fehler. Danach kann kein Dashboard mehr ohne Fehler aufgerufen werden!

Wenn das so wieder wie vorher funktioniert, dann wäre es perfekt.

Mit freundlichen Grüßen

Hallo Herr L.,

der Grund des Fehlers ist Folgender:

Das Datum war als Datum definiert in der Form #Monat/Tag/Jahr#. Ich weiß nicht, was Sie eingetragen haben – aber vielleicht nicht als Datum. Da dieses Datum nur einmal als Text verwendet und angezeigt wird, habe ich einen Text daraus gemacht (somit wird nicht mehr impliziert konvertiert – hier lief wohl etwas schief).

Public Const p_cdatAppStand As String = "08.07.2021"

Die wollen doch tatsächlich, dass ich auch Dienstag bis Freitag komme. In der Stellenanzeige stand ausdrücklich: „Facharbeiter für Montage gesucht.“

Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:

Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:

=Blattname!Zellbezug

Prima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel

=BMW!Z8

gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.

Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:

=’Alfa Romeo‘!P1

Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:

Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.

Und wie sieht der Bezug auf dieses Blatt aus?

='Mercedes'' Benz'!W117

Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!

Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!

Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!

Ab Mitte 30 fängt man an, die Aussage „gut im Bett“ neu zu interpretieren. Bedeutet jetzt: schläft durch, schnarcht nicht, bleibt auf einer Hälfte der Matratze und klaut dir nachts nicht die Bettdecke.

Hallo Herr Martin, im Anhang sende ich Ihnen eine Exeltabelle mit einer „mauell erstellten Kopfzeile“ und einer Zeilenschaltung in der Zelle „Anschrift“. Für einen Serienbrief benötige ich die „Kopzeilen“ ebenso die Zeilenschaltung nicht. Wie entferne ich am schnellsten die „Kopfzeilen“ und die Zeilenschaltung in der Zelle. (Teilenschaltung in einer Zelle zu entfernen, habe ich in Ihren Videos schon gefunden). Ich möchte alle Daten in einer Spalte haben. Ich würde mich freuen, wenn Sie mir dabei helfen würden. Mit freundlichen Grüßen PV (Ein Fan Ihrer Office-Kurse)

Hallo Herr V.,

das habe ich gemacht :

* Mit Suchen und Ersetzen die Zeichenschaltung (Strg + J) durch einen Schrägstrich ersetzt. (die Zeichenschaltung hat den Code 10 – man kann auch die Funktion WECHSELN verwenden:

=WECHSELN(F4;ZEICHEN(10);"/")

* den Verbund aller verbundenen Zellen aufgehoben

* den Textumbruch entfernt

* mit einem AutoFilter in der Spalte „Card Number“ den Text „Card Number“ und die leeren Zellen gefiltert und entfernt.

* die leeren Spalten gelöscht.

Hallo Herr Martin, ich habe noch etwas vergessen. In der Spalte „Badge Holder Name“ sind viele Namen nicht korrekt geschrieben. Wie kann ich Straßennamen schnell ändern. Ich mache es oft über Suchen + Ersetzen. Aber in vielen Situationen ist das nciht möglich. Gibt es spezielle Formel oder Funktionen die das erleichtern. Nach dem Säubern der Exeltabelle, wie kann ich dann automatisch eine Kopfzeile erstellen, so das man die Mitgliederliste anschließend ausdrucken oder auch ein PDF schreiben kann. Ich freue mich über Ihre Nachricht.

Und zu Ihrer Frage: wenn Sie immer die gleichen Ersetzungen haben, erstellen Sie eine Tabelle und verwenden die Excel-Funktion WECHSELN.

Liebe Grüße  

René Martin

Verkaufe gebrauchte facebook-Freunde. Sie liken nicht, und haben noch nie kommentiert, aber sie wissen alles über mich.

Letzte Woche in der PowerBI-Schulung. Wir schauen uns einige der DAX-Funktionen an. Beispielsweise CONCATENATE.

Es folgt die Frage, warum man mit der Excel-Funktion VERKETTEN ( CONCATENATE) mehrere Texte verketten kann:

Dagegen in DAX:

Zwei Texte sind kein Problem. Jedoch bei drei Texten:

=CONCATENATE(tbl_ottos_mops[ottos];" ";tbl_ottos_mops[mops])

Also verketten wir in DAX mehrere Texte mit dem et-Zeichen „&“. Das funktioniert!

=tbl_ottos_mops[ottos]&" "&tbl_ottos_mops[mops]&" "&tbl_ottos_mops[Spalte1]&" "&tbl_ottos_mops[Spalte2]

Theoretisch können manche Menschen schwimmen, weil sie hohl sind. In Wirklichkeit können sie nicht schwimmen, weil sie nicht ganz dicht sind.

In Excel liegen einige Zahlen als Währung vor. Ich erstelle ein 2D-Säulendiagramm mit gruppierten Säulen. Ich lasse mir die Datenbeschriftung der Säulen anzeigen und drehe die Zahlen um 90 Grad:

An anderer Stelle wird eine weitere Datenreihe eingefügt – diese soll mit ins Diagramm integriert werden:

Das Verblüffende: Die neuen Zahlen werden nun mit der Währung $ formatiert:

Werden die Zahlen mit dem Zahlenformat Buchhaltung formatiert, werden drei Nachkommastellen eingefügt:

Auch bei Dezimalzahlen tritt dieser Effekt auf:

Jedoch: NICHT IMMER! Ich weiß nicht, wann diese Zahlen falsch formatiert werden:

Nur noch zwei Mal joggen, dann ist wieder Weihnachten.

Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:

„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.

Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.

Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.

Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:

Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.

Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.

Die Formel

=WENN(UND(ZÄHLENWENN($B$2:$P$13;A21)>0;WOCHENTAG(A21;2)<=5);"F";"")

prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):

Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:

Dazu verwende ich die Formel:

=WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A21;-WOCHENTAG(A21;3);1;7;1);"F")>0;"F";"")

Der Rest ist trivial, oder? Man sucht ein Datum in der ersten Datumsspalte und prüft, ob in der Wochenspalte ein „F“ steht oder nicht:

=WENN(SVERWEIS(H20;A21:C5501;3;FALSCH)="F";25%;0%)

Beispiel: der 01. Mai 2021 fiel auf einen Samstag, also ist für den 29. April kein Zuschlag erforderlich.

Pfingstmontag war in diesem Jahr am 24. Mai – also muss für den 26. Mai 2021 ein Zuschlag bezahlt werden.

Eine hübsche Knobelaufgabe – ich habe ein bisschen überlegen müssen.

An alle, die behaupten, sie wüssten, wie der Hase läuft: Er hoppelt!

PowerBI-Schulung. Wir greifen auf Excelmappen zu, die auf Sharepoint liegen. Es kommt die Frage, ob man auch auf einen Sharepoint-Ordner zugreifen kann. Klar kann man:

Man muss nur den Ordnerpfad kopieren und eintragen:

Und – erhält einen Fehler:

Ach, klar, natürlich: man muss sich natürlich noch anmelden. Ist ein bisschen versteckt:

Erstaunlicherweise kann man JETZT OHNE Anmedlung in Excel über PowerQuery auf einen Sharepoint-Ordner zugreifen. Muss ich das verstehen?

Boah – 38 Grad im Schatten – das hält ja keine Sau aus! – Meine Fresse – warum gehst du auch in den Schatten!

Eine schöne Frage, die gestern in der Excel-Schulung kam. Ich zeigte wie man Listen sortiert und dass man auch nach Farben sortieren kann.

Dann zeigte und erläuterte ich den Autofilter. Auch dort kann man nach Farben filtern:

Es kam die Frage, warum man nicht nach mehrere Farben filtern könne – schließlich könne man auch nach mehreren Farben sortieren:

Stimmt – warum eigentlich nicht? Ich weiß es nicht!

Eine Fee fragte mich, ob ich einen riesigen Penis oder ein tolles Gedächtnis haben möchte. Ich habe gewählt. Aber ich kann mich nicht mehr erinnern, wofür ich mich entschieden habe.

Hallo Herr Martin,

könnten Sie bitte noch berechnen, ob das Paket Sperrgut ist.

Ich frage zurück: „Woran erkenne ich, ob es Sperrgut ist?“ Ich erhalte die schriftliche Antwort:

„Länge und Breite dürfen nicht größer als 2,40 Meter sein.“

Ich überlege:

Länge + Breite < 2,4?

Länge < 2,4 UND Breite < 2,4?

Länge < 2,4 ODER Breite < 2,4?

Was ist mit der Höhe? – Kann man das Paket nicht drehen?

Ich warte noch auf die Antwort.

Wahrscheinlich überlegen sie selbst …

Mal den Teufel nicht an die Wand! – Aber das ist doch ein Spiegel, in den du hineinschaust …

Amüsant: ich habe eine große Excelliste mit mehrere Tausend Datensätzen. Ich bearbeite sie in PowerQuery:

Ich importiere eine zweite Liste und verknüpfe sie mit einem Left outer Join:

Das Ergebnis sieht in PowerQuery gut aus:

Ich lade die Tabelle zurück nach Excel und erhalte einen Fehler:

Zurück zu PowerQuery versuche ich einen Right outer Join:

Die Ursache? PowerQuery zeigt nur 1.000 Datensätze. Wenn in der Liste DANACH eine Zelle mit einem fehlerhaften Wert steht, wird er bei einem Left Outer Join nicht angezeigt. Erst in Excel. Natürlich kann man sich in PowerQuery auf die Suche nach dem fehlerhaften Datensatz machen und ihn entfernen. Oder in Excel:

Danke an Christa für diesen Hinweis und danke für die Bemerkung, dass die Fehlermeldung in älteren PowerQuery-Versionen eine andere war:

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

Ich bin so froh, einer dieser Menschen zu sein, die problemlos Musik hören können, ohne dabei zu joggen.

Herr L. schickt mir ein Video, das er erstellt hat, damit ich besser erkenne, woran der Fehler liegen könne, der in Excel auftritt. Ich öffne das Video und erhalte folgende Fehlermeldung:

Wiedergabe nicht möglich. Schaffen Sie den Inhalt erneut an.

Das Verb „anschaffen“ amüsiert mich.

Der Straßenbelag: hart aber Teer.

Amüsant.

Ich öffne eine Testdatei, die mir ein Anwender zugeschickt hat, und wundere mich, wo das Menüband (das Ribbon) ist.

Dann fällt es mir ein: es gab da doch … Richtig:

Es gab einen Excel 4.0-Makrobefehl, mit dem man die Symbolleiste ausblenden konnte. Dieser funktioniert im aktuellen Excel noch immer und blendet hier das Menüband aus. Der Befehl lautet:

Application.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"", False)"

Und: nicht vergessen: Nach Beendigung bitte wieder einschalten!

En Dios creemos; todos los demás deben mostrar datos.*)

Hallo Herr Martin,

mein Bekannter hat sich die neue Version angeschaut.

Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.

In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.

Hallo Herr L.,

öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.

Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.

Sie erhalten den Fehler.

Heißt: DIESER Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.

Woher kommt er?

Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:

Sie greifen auf den VALUE dieser Zelle zu – das knallt!

Die Ursache des Fehlers:

In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!

=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))

Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.

An dieser Maske dlgKundenverwaltung habe ich nichts geändert.

schöne Grüße

Rene Martin

*) An Gott glauben wir; alle anderen müssen Daten zeigen!

Hier war ich schon mal! Das WLAN verbindet sich automatisch!

Ich bin erstaunt und amüsiert.

Was ist passiert?

Ich erstelle ein Eingabeformular in Excel für eine Firma. Da mit einem Wert, beispielsweise 7,75 €, weitergerechnet wird, lösche ich den Text „7,75 € (nicht enthalten in Frachtraten)“, der in der Zelle stand, ersetze ihn durch die Zahl 7,75 und formatiere die Zelle mit einem benutzerdefinierten Zahlenformat

0,00" € (nicht enthalten in Frachtraten)"

Nach einer Weile stelle ich erstaunt fest, dass der Text als Quickinfo angezeigt wird. Was passiert hier? Was habe ich hier gemacht?

Nein – es liegt keine Notiz und kein Kommentar unter der Zelle.

Nein – es liegt keine Datenüberprüfung mit einer Eingabemeldung unter dem Text.

Nein – es wurde auch kein QuickInfo eines Hyperlinks verwendet:

Erstaunt reibe ich die Augen. Das habe ich selbst gemacht! Aber wie?

Schließlich komme ich hinter des Rätsels Lösung:

Trägt man in Excel in eine Zelle eine Zahl ein, formatiert diese Zahl (beispielsweise als Währung) und verkleinert die Spalte so, dass die formatierte Zahl in der Zelle nicht angezeigt werden kann, wird die formatierte Zahl als Quickinfo angezeigt, wenn sich der Mauszeiger darüber bewegt. Aha!

Wenn ich nun mehrere Zellen markiere und die formatierte Zahl „über die Auswahl zentriere“:

wird die Zahl klar lesbar in den Zellen angezeigt. Da die Zahl aber für die Zelle, in der sie sich befindet, zu „breit“ ist, bleibt das QuickInfo stehen:

Eben! Und so kann ich eine Zahl über mehrere Zellen ausrichten …

… und formatieren, beispielsweise (hier:) mit

0" - Die Antwort auf alle Fragen!"

Und dann habe ich gelacht!

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

Ich habe jetzt genau das richtige Alter. Muss nur noch herausfinden für was.

Nicht aufgepasst. Nicht genau hingeschaut.

Gestern habe ich für den Excelstammtisch einige Dateien vorbereitet, um zu zeigen, was PowerQuery verlangsamt. Ich habe eine Liste mit Dummy-Namen mit 20.000 Datensätzen:

Diese verknüpfe ich mit einer Liste, die zwei Zeilen lang ist:

Das Ergebnis: 28.877 Datensätze

Ein zweiter Blick auf die Liste zeigt, dass einige Zeilen (nicht alle!) nun zwei Mal in der Liste auftauchen:

Nein – an der Verwendung eines Primärschlüssels liegt es nicht – die Zeile

= Table.AddKey(#"Geänderter Typ",{"Geschlecht"}, true)

ist nicht der Grund!

Schließlich finde ich des Rätsels Lösung: ich habe beim Verknüpfen der beiden Listen aus Versehen die Fuzzy-Logik aktiviert.

Haken raus – und ich erhalte wieder die ursprünglichen 20.000 Zeilen.

Auf meinem Grabstein soll einmal stehen: „Hat die Gruppe verlassen.“

Markus Hahner hat darauf hingewiesen.

Trägt man in Word in einer Tabelle Text ein, verbreitet sich die Spalte, in der der Text steht.

Diese (oft nicht gewünschte) Eigenschaft kann über das Symbol „AutoAnpassen“ der kontextsensitiven Registerkarte „Layout“ ausgeschaltet werden. Dort kann man von der Option „Automatisch an Inhalt anpassen“ an „Feste Spaltenbreite“ umschalten.

Leider zeigt Word nicht an, welche Option aktiviert wurde – keiner der drei Optionen ist mit grauer Farbe unterlegt.

Liebe Nachbarn – am Mittwoch ist Vollmond. Es kann etwas lauter bei uns werden. Die Werwolfs.

Sehr ärgerlich!

Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:

In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …

Mich beschleicht ein Gedanke …

Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*

Hinher ist man nicht immer klüger. Manchmal ist man hinterher auch einfach ärmer, überfressen, betrunken oder schwanger.

Ich bin verblüfft. Dass Excel sehr viele Algorithmen beinhaltet, die zum Teil hilfreich zum Teil verwirrend sind, ist bekannt. Aber diesen Mechanismus kannte ich bislang noch nicht:

Drei untereinander stehende Zellen sind auf die gleiche Art formatiert (beispielsweise Zahlenformat oder Hintergrundfarbe) und mit Text gefüllt.

Trägt man eine weitere Information darunter ein, so wird das Format übernommen. Das war mir bekannt. Aber ich wusste nicht, dass es auch bei einer Leerzeile funktioniert:

Hat man eine formatierte Zelle und darunter eine Leerzeile, so wird die nächste Zelle bei der Texteingabe wieder formatiert. Beim dritten Mal endet der Spuk jedoch:

Das kann praktisch sein, es kann aber auch verwirrend oder störend sein.

Immerhin: man kann es deaktivieren über: Datei / Optionen / Erweitert / Gruppe [Bearbeitungsoptionen] / Datenbereichsformate und -formeln erweitern.

Ick bin übrigens nur Single weil ick von Haus aus meine Mitmenschen durch meine enorme Attraktivität einschüchtere. Vermute ich zumindest.

Wie oft habe ich es schon gesagt: nicht verbinden! Das bringt nur Ärger!

Richtig: ich erhalte ein Formular mit mehreren verbundenen Zellen – hier: 2 x 2 Zellen sind verbunden.

Ich erstelle einen Verweis auf diese Zelle(n):

#ÜBERLAUF! ist die Folge. War ja klar …

Wenn auf der Packung steht: „schmeckt wie selbstgemacht“ und du dir denkst: „Bitte nicht!“

WORD – Bug in Inhaltsverzeichnis ?

Hallo Rene,

bei MS Word kannst Du sicher auch helfen?

Ich habe in angehängter Datei im Inhaltsverzeichnis den Fall, dass die Seitenzahlen in einigen Überschriften (Brote, Pizza, Gebäck, …) nicht rechtsbündig stehen.

Hast Du eine Idee, woran das liegt?

Danke, Gunnar

klar, Gunnar,

Word: davon lebe ich auch. Normalerweise.

Die Antwort ist denkbar einfach: „Brote“ und „Gebäck“ basieren auf der Formatvorlage „Verzeichnis 2“ und „Verzeichnis 3“. Dort ist ein hängender Einzug von 1,25 cm eingestellt. 1,25 cm heißt um 1,25 cm, nicht auf die Position 1,25 cm. Da diese Wörter kürzer als 1,25 cm springt der Tab auf den gesetzten Einzug.

Die Lösung: Einzug auf 0,5 cm oder 0 setzen. Dann klappt es.

Liebe Grüße

Rene

Fazit: Nicht alles, was nach Bug aussieht, ist auch einer. Manchmal sind auch die Menschen, die vor dem Computer sitzen, die Ursache für Fehler.

Liebst du mich? — Mmmmhmm — Moment – ich mach mal das Klebeband weg.

Einzelplatzschulung. Oder „Coaching“, wie es auf Deutsch heißt. Ein älterer Herr möchte die Grundlagen der Anwendungsprogramme lernen.

Wir beginnen mit Outlook. Die Möglichkeit einen Screenshot zu erstellen und in eine Mail einzufügen gefällt ihm. Ich erkläre ihm das Vorgehen:

Es funktioniert auch in anderen Programmen, erläutere ich und zeige ihm Excel. In der Registerkarte „Einfügen“ fällt sein suchender Blick auf die Mitte der Registerkarte, wo „Screenshot“ auch in Outlook zu sehen war. Kein Screenshot!

Ein Blick streift nach recht – kein „Screenshot“-Symbol. Ah – etwas weiter links – DA werden wir fündig.

Und PowerPoint? Dort finden das Symbol noch weiter links:

Immerhin: in allen Anwendungsprogrammen (auch Word) befindet sich dieses Symbol in der Gruppe „Illustrationen“.

Um die Suche zu erleichtern, erläutere ich ihm das Windows-eigene Programm „Snipping Tools“.

Schatz – wir lebennun seit fünf Jahren hier zusammen – findest du mich schüchtern? – DU WOHNST HIER?

Letzte Woche hatte ich eine Einzelplatzschulung. Nennen wir es Coaching: ein älterer Herr wollte sich fit in den Anwendungsprogrammen machen. Ich erkläre die Grundlagen: Text und Zahl. „Bitte schließen Sie die Eingabe immer mit [Enter] ab.“ (Dass es noch andere Möglichkeiten gibt, wusste er selbst.) „Bitte drücken Sie immer die Ok-Taste. In der Kirche wird sie auch die [Amen]-Taste genannt.“ Er schmunzelt.

Wir üben Formeln und drücken [Enter]. Immer. Nicht wegklicken! Okay – verstanden.

Wir ziehen die Formel am Ausfüllkästchen nach unten. Er drückt [Enter]. Okay … ich muss mich ein bisschen korrigieren.

Ein Pakettransporter verlor gestern eine große Ladung Synonymwörterbücher. Die Augenzeugen waren fassungslos, bestürzt, erschüttert, schockiert, betroffen, aufgewühlt, konsterniert, perplex, entsetzt, erschrocken und entgeistert.

Einfach nicht aufgepasst. Dabei weiß ich es doch:

Ich erstelle eine Userform in Excel VBA. Darin sollen Werte „nach unten“ weitergegeben werden, wenn ein Kontrollkästchen angeklickt wird. Die Kästchen heißen chkFamilie02, chkFamilie03, chkFamilie04, … chkFamilie12

Ich prüfe, ob das letzte ausgewählt wurde oder ob noch weitere unten ausgewählt wurden:

Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False Or frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False Or intZeile > 12

Das läuft an die Wand – eine Fehlermeldung ist die Folge. Mein Denkfehler:

wenn intZeile > 12, dann wird das geürft. Beispielsweise: intZeile hat den Wert 13. Allerdings: es wird auch geprüft, ob das Control chkFamilie12 sichtbar ist. Und das gibt es nicht!

OR (und auch AND) in VBA prüft (leider!) immer alle Teile. Und stoppt nicht, wenn einer der beiden Zweige falsch ist. Also anders gelöst – nicht ganz elegant – aber okay:

Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False 
       strZeile = Format(intZeile, "00")
       frmAuswahl.Controls("lblInfoZeile" & strZeile).Caption = strText
       intZeile = intZeile + 1
       If intZeile > 12 Then Exit Do
Loop

Geht doch!

Kann man bei dem Auto noch etwas retten? – Ja, man könnte ein neues Auto zwischen die Nummernschilder schrauben.

Ich habe hier einige Artikel zu dem kostenlosen PlugIn Excel-DNA geschrieben, mit dem man Excelfunktionen (und andere Werkzeuge) in Excel schreiben kann und welche Probleme es dabei gibt.

Nun habe ich einen Artikel – nein: genauer: es sind drei Artikel – darüber veröffentlicht: in der neuen dotnetpro.

 

OLYMPUS DIGITAL CAMERA

Schön, dass du mich mal wieder kraulst, Schatz. – Ich finde die Katze nicht.

[Strg] + [Q]

Die Datenüberprüfung in Excel ist hinlänglich bekannt. Ein sehr praktisches Werkzeug, mit dem man eine vordefinierte Liste an Begriffen für Zellen bereitstellt.

Oder sicherstellt, dass nur bestimmte Werte in eine Zelle eingetragen werden.

Allerdings: bei sehr vielen Auswahlmöglichkeiten (sprich: langen Listen) ist die Suche und die Navigation sehr mühsam. Außerdem muss man sich auf feste Listen beschränken und darf keine freien Einträge verwenden. (ich habe auf diesem Blog schon einige Male gelästert und gespottet).
Ich habe ein kleines Add-In geschrieben – Strg + Q (so wird es gestartet), mit dem eine bequeme Auswahl und eine freie Texteingabe möglich ist.


Interessiert? Für private Nutzung stelle ich es kostenlos zur Verfügung; für geschäftliche Verwendung hätte ich gerne 10 Euro. Dann erhältst du auch eine genaue Beschreibung der Installation und Bedienung und eine Rechnung.
Interessiert?
Gerne kannst du es auf meiner Seite compurem.de herunterladen – du findest es hinter dem Menü Programmierung ::: [Strg] + [Q].

Schatz. Guck mal! Kann ich den Bikini noch tragen? – Tragen schon, aber nicht mehr anziehen!

Wenn man mit VBA programmiert und wissen möchte, ob in einem Text ein anderer vorhanden ist, kann man mit den Funktionen Left, Right, Mid oder Instr arbeiten. Oder den Vergleichsoperator Like verwenden. Also beispielsweise

If txtIBAN.Value Like „DE*“ Then …

Die Aufgabe: ich will Namen in Excel prüfen. Ich möchte wissen, ob sie auf intelligente Tabellen verweisen, also einen Aufbau haben, wie beispielsweise:

=tbl_Feiertage![#Alle]

oder:

=tbl_Feiertage[2021]

Also prüfe ich:

    For i = 1 To ThisWorkbook.Names.Count
        If ThisWorkbook.Names(i).RefersToLocal Like "=*[*]" Then
            MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
        End If
    Next

Und wundere mich, warum die If-Verzweigung nichts findet.

Okay – noch ein Versuch – ich lasse die letzte eckige Klammer weg und prüfe erneut:

For i = 1 To ThisWorkbook.Names.Count
    If ThisWorkbook.Names(i).RefersToLocal Like "=*[*" Then
        MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
    End If
Next

Verwundert reibe ich mir die Augen. Was klappt nicht? Die Prüfung

If ThisWorkbook.Names(i).RefersToLocal Like "=*" Then

funktioniert …

Ein Blick in die Hilfe beantwortet meine Frage:

https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/like-operator?f1url=%3FappId%3DDev11IDEF1%26l%3Dde-DE%26k%3Dk(vblr6.chm1008961);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue

Die eckige Klammer ist als Zeichen reserviert. Man muss sie entwerten. Weiter unten lese ich:

Ah – so funktioniert es:

If ThisWorkbook.Names(i).RefersToLocal Like "=*[[]*" Then

Oder auch so:

If ThisWorkbook.Names(i).RefersToLocal Like "=*[[]*[]]" Then

Warum nicht gleich?!?

Menschen, die mich an der Kasse vorlassen, weil sie sehen, dass ich es eilig habe und nur wenige Dinge in der Hand habe, kommen übrigens in den Himmel.

Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.

Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.

Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.

Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:

Im unteren Teil verwende ich ein kleines VBA-Makro:

Sub BerechneDateDIFF()
     Dim intZeile As Integer
     Dim intSpalte As Integer

     For intZeile = 21 To 30
         For intSpalte = 2 To 20
             ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
         Next intSpalte
     Next intZeile

End Sub

Das Ergebnis:

Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.

Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.

Ich habe gerade versucht, den Mähroboter mit einem Grasbüschel vom Nachbarn an den Gartenzaun zu locken. War wohl schon satt.

Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.

Seit ein paar Tagen heißt es nun „Vom Blatt“

Muss das sein? Ständiges Umbenennen?

Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):

Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:

Das funktioniert auch mit der Funktion SEQUENZ:

Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:

dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …

70% aller Frauen finden ihren Arsch zu dick. 20% finden ihn zu dünn. Nur 10% sind zufrieden mit dem Arsch, den sie geheiratet haben.

Ich soll ein Excel-Formular (per VBA) auswerten. Schnell stelle ich fest, dass einige Zellen gesperrt sind. Das ist nicht sehr geschickt, da diese Zellen vom Anwender und von der Anwenderin ausgefüllt werden sollen:

Wie finde ich diese Zellen? Es gibt leider keine Suchoption dafür, so dass alle gesperrten oder nicht gesperrten Zellen markiert werden.

Also anders: mit der Ersetzenfunktion gelingt es: Öffnet man die Optionen, kann man in Excel nach Formaten suchen. Eben: beispielsweise nicht gesperrte Zellen. Es erweist sich geschickter, die offenen Zellen zu finden, als die gesperrten, da alle Zellen in den 1.048.567 x 16.384 Zeilen und Spalten gesperrt sind.

Und diese werden durch eine Hintergrundfarbe ersetzt. So findet man schnell die nicht gesperrten Zellen:

Wie ist dein Verhältnis zu deinen Eltern? – 1:2

Gestern auf dem Excelstammtisch stellte Volker folgendes Problem bei/mit intelligenten (dynamischen; strukturierten) Tabellen vor.

Wir haben eine Liste, die zu einer intelligenten Tabelle verwandelt wurde:

Die Tabelle heißt tbl_Planeten.

Auf einem zweite Tabellenblatt wird Bezug auf diese Tabelle genommen; genauer: auf jede Spalte:

=WENNFEHLER(tbl_Planeten[@Planet];"")

Erstaunt stellt man fest, dass der erste Planet – Merkur – fehlt. Die Antwort ist simpel: „@“ bezieht sich auf die Informationen der aktuellen Zeile. Da die zweite Tabelle erst ab Zeile zwei beginnt (die erste fängt in der ersten Zeile an), ist der Bezug versetzt. Man muss also bei Tabellen gleich positionieren. Das birgt Gefahren.

Fazit: besser SO nicht Tabellen miteinander verknüpfen. Es gibt bessere Lösungen: PowerQuery sei an dieser Stelle genannt. Oder relative Bezüge.

Danke an Volker für diesen amüsanten und wichtigen Hinweis!

Als Kind dachte ich, dass Spargel und Erdberen illegal seien, weil wir ihn aus Autos auf abgelegenen Parkplätzen gekauft haben.

Hallo Rene,

Ich suche jemanden, der fit in power query und power pivot ist und dem ich ca. eine stunde lang fragen stellen kann.

Hintergrund: Ich habe einen größeren Auftrag, da geht es um mehrere Tools im Excel-Umfeld, da geht es bei einem Tool jetzt erst mal darum, ob power query da was bringen würde.

Ich hatte Schlimmes oder Schwieriges befürchtet. Aber die zentrale Frage war weder schlimm noch schwierig zu beantworten.

Gegeben sei eine Auftragstabelle mit Verkaufsdaten:

Diese Liste wird nach PowerQuery gezogen und dort bearbeitet. Das Ergebnis wird zurückgegeben:

Die erste Frage lautete: Wie kann man Anfang und Ende als Filter in PowerQuery einbauen?

Die Antwort:

  • Man muss die jeweils zwei Zellen in eine intelligente Tabelle konvertieren.
  • Man muss diese ebenso nach PowerQuery importieren.,
  • Dort den Datentyp in Datum ändern.
  • Und dort ein Drilldown durchführen. Das heißt: die Tabelle in einen Wert, besser: in eine Variable, verwandeln.
  • Diese Variable hat einen Namen – er kann verwendet werden.

Also so:

Man schaltet einen beliebigen Datumsfilter ein („Zwischen“):

Und ersetzt in M die beiden Werte durch die Variablennamen:

Fertig! Test in Excel:

Und natürlich kam danach die Frage:

ich möchte die berechnung lieber in PowerPivot vornehmen und mit einer Pivottabelle gruppieren und das Meassure verwenden.

Ich habe jetzt in DAX diesen ausdruck, der funktioniert:

MeineSumme:=
CALCULATE(SUM(tblBestellungen_pre[Frachtkosten]);
DATESBETWEEN(tblBestellungen_pre[Bestelldatum];
date(2019;2;1); date(2019;2;27)) )

Weißt du, wie ich hier jetzt das Datum aus den Zellen hineinbekomme?

Auch DAS war schnell geklärt:

Hi Claus,

wenn tblFrom und tblTo im Datenmodell liegen, dann mit:

MeineSumme:=CALCULATE( SUM(tblBestellungen_pre[Frachtkosten]);DATESBETWEEN(tblBestellungen_pre[Bestelldatum]; MAX(tblFrom[From]); MAX(tblTo[To])))

Und kann man Datenmodell und Meassure in einer Pivottabelle in Excel verwenden.

Hübsche Fingerübung!

Jede Packung Rosinen erzählt die tragische Geschichte von Trauben, die hätten Wein sein können.

Ich sollte doch eine Seite „Outlook-nervt“ eröffnen. In letzter Zeit werde ich häufig mit Outlook-Fragen, Outlook-Problemen und Outlook-Wünschen konfrontiert. Beispielsweise mit folgendem Wunsch:

Sehr geehrter Herr Martin,
Sie haben vor geraumer Zeit einen Outlook-Kurs für meine ganze Abteilung abgehalten und weil Sie Spezialist im Outlook sind möchte ich mich mit einer Frage vertrauensvoll an Sie wenden. Ich hoffe das ist in Ordnung für Sie.
Ich versuche das Problem zu erklären:
Wir haben 2 Kunden die eine ähnliche Email Adresse haben:
die eine fängt mit „contoso“ an die andere mit „re-contoso“.
Fakt ist, dass wenn man die im Email-Feld „An“ angibt, dass man die leicht verwechseln kann.
Genau das ist auch kürzlich passiert und somit hat ein Kunde die Unterlagen vom anderen Kunden erhalten, was sehr problematisch ist.
Jetzt meine Frage an Sie:
Gibt es eine Möglichkeit, dass man bei diesen 2 Email-Adressen so eine Art Sperre generiert? Wie zum Beispiel durch ein Fenster mit der Frage: sind Sie sicher, dass Sie diese Email an diese Adresse versenden möchten?
Es würde mich sehr freuen, wenn Sie sich mit mir in Kontakt setzen würden.
Bis dahin wünsche ich Ihnen noch einen schönen Tag.
Mit freundlichen Grüßen

Hallo Frau L.,
mit einer Regel geht so etwas leider nicht – eine Regel kann man nur erstellen für „Mail die an … gesendet wurde“.

Aber mit einem kleinen VBA-Skript geht das:

  • Fügen Sie in Outlook die Registerkarte „Entwicklertools“ ein:
  • Wechseln Sie in dort in die Entwicklungsumgebung „Visualbasic“
  • Doppelklicken Sie im Projektfenster auf ThisOutlooksession:

Wechseln Sie auf der rechten Seite über das Dropdown zu Application:
Es öffnen sich folgende Codezeilen:

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)

End Sub

Dieses Ereignis überwacht das Senden einer Mail. Dort hinein muss folgender Code, so dass das Ganz dann so aussieht.

Option Compare Text

Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error Resume Next
If Item.To Like „contoso“ Then
If MsgBox(„Wollen Sie diese Mail wirklich an “ & Item.To & “ senden?“, vbInformation + vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
End If
End If
End Sub

Versuchen Sie es am besten mit dem Namen einer Kollegin (oder Ihrem Namen). Sie können Groß- und Kleinschreibung ausschalten, indem Sie VOR das Makro den Befehl
Kommen Sie damit klar?

schöne Grüße

René Martin

Wir haben es heute zusammen an ihrem Rechner implementiert. Sie ist sehr glücklich über das Ergebnis.

Verkaufe Philips Fernseher. Bei Interesse einfach melden. Gruß, Philips Bruder.

Wer macht denn So etwas? Warum macht Andreas Thehos SO etwas? Ich weiß es nicht. Aber es ist interessant und amüsant:

Er schreibt in Word einen Text und fügt einen Kommentar ein:

Der Text wird nach Excel kopiert – mit der Option „Ursprüngliche Formatierung beibehalten“:

Der Text des Kommentars wird in eine eigene Zelle eingefügt.

Nun wird ein Zellformat von einer anderen Zelle auf die Zelle mit dem Text übertragen:

Das Ergebnis verblüfft. Oder auch nicht?!

Die Formatierung endet an der Stelle, an der der Kommentar stand.

Das muss ich genauer anschauen!

Ich zerlege den Text mit der Funktion TEIL in seine Bestandteile, beispielsweise mit:

=TEIL($A$1;SPALTE(BD1);1)

Die Funktion wird nach rechts gezogen:

An der Stelle Kommentar|Leerzeichen befinden sich jetzt zwei (!) Zeichen. Mit der Funktion CODE sehe ich es mir genauer an:

Die Funktion CODE liefert die Zahl 32 – klaro: für das Leerzeichen und die Zahl 160!?! Sie liegt zwischen dem Zeichen Ÿ und dem umgekehrten Ausrufezeichen, wie es im Spanischen verwendet wird:¡

Danke Andreas, für diesen interessanten Hinweis. Es bleibt für mich immer noch die Antwort auf die Frage offen – wer macht denn so etwas?

Umgekehrt – ich hatte vor einigen Jahren mal ein langes Gespräch mit dem Verantwortlichen von LibreOffice. Er hat mir gesagt, dass das Werkzeug „Format übertragen“ die Hölle ist (LibreOffice hat sich lange geweigert so etwas zu implementieren, haben aber schließlich dem Druck der Pinsel-Liebhaber aus der Microsoft-Fraktion nachgegeben) – denn – welches Format wird übertragen? Was ist Format? Ist Kommentar auch Format? …

Es gibt zwei Wörter, die dir im Leben viele Türen öffnen werden: „ziehen“ und „drücken“.

Übrigens, ist jetzt auch zwei Kollegen passiert, die Home & Business 2019 haben, also nicht das Abo-Produkt. Eines Tages kam ein Update, die graphische Benutzeroberfläche, also sämtliche Schaltflächen veränderten sich und auf einmal waren die Microsoft 365-Funktionen drin (also XVERWEIS, XVERGLEIC, etc.).

Dann, zwei bis drei Tage später installierte sich wieder ein Update und alles war wieder weg – hab ich mir also nicht eingebildet. 😉 Diese Geschichte glaubt mir nicht jeder.

Was denkt sich Microsoft wohl dabei? Ist das wie dem Hund die sprichwörtliche Wurst hinhalten (schau mal was du alles Tolles haben könntest, wenn du das Abo-Produkt bestellst?) oder einfach ein Bug?

Dir einen schönen Abend!

Viele Grüße, Dominic

DHL fragt für die Zustellung nach meinem Wunschnachbarn. Ich sag mal: Single; 1,85 m; schwarze Haare; sportlich; Sinn für Humor und dieses gewisse Etwas.

Ich wollte doch nur in Word die Rechtschreibprüfung starten. Ich klicke auf die Schaltfläche „Editor“ und lese:

Um dieses Dokument zu überprüfen, müssen die folgenden Ausnahmen in den Korrekturoptionen deaktiviert sein. 
- Rechtschreibfehler nur in diesem Dokument ausblenden 
- Grammatikfehler nur in diesem Dokument ausblenden 
Möchten Sie s 

Hatte da jemand keine Lust mehr? Oder einen Schluckauf? Oder fing die Kaffeepause oder der Feierabend an? Amüsant …

Schau doch bitte nach, ob die Bremslichter funktionieren! – Vorne oder hinten? – Die Menschheit wird definitiv aussterben!

Sehr geehrter Herr Martin,

ich „spiele“ z.Zt. mit einer Tabelle (Mappe1), in der Excel partout nicht rechnen will. In der Kopfleiste habe ich die für die entsprechenden Spalten die Formeln und die Zellen-Formatierungen eingegeben. Der Wert in Spalte #30 ist die Basis für alle Berechnungen in der entsprechenden Zeile.

Kopiert habe ich nach den Anweisungen der EXCEL-Hilfe und anderen Tipps aus dem Internet.

Für Ihre Hilfe wäre ich Ihnen sehr dankbar.

Hallo Herr M.,

beim Öffnen der Datei meldet Excel, dass ein Zirkelbezug vorliegt. Richtig: in der Statuszeile links unten steht es auch:

Denn: in der Zelle N30 steht die Formel

=EL30

in EL30 steht:

=(100%-EK30)/100%

in EK30 steht:

=(100%-EI30)/100%

in EI30 wird wieder Bezug genommen auf N30 mit:

=(EK30-EH30)/N30

Hier schließt sich der Kreis. Das müssen Sie lösen – sonst rechnet Excel nicht mehr!

Ich finde es gut, dass der Autokindersitz klappert, wenn er leer ist. So merkt man schnellt, dass man etwas vergessen hat.

Nicht aufgepasst.

Ich arbeite seit einer Weile in einer Excelmappe, die ich dann schließe. Öffne eine neue Datei, trage eine 1 ein und wundere mich über die Fehlermeldung:

Sie müssen eine Form auswählen.

Mir dämmert es. Die letzte Aktion in der letzten Datei war das Arbeiten mit Grafik – ich hatte das Werkzeug „Objekte auswählen“ aktiviert und nicht wieder zurückgeschaltet:

Sucht ihr morgens eure Unterwäsche auch nach den Chancen auf Sex aus? Ich trage heute einen Jute-Einkaufsbeutel.

In verschiedenen Programmiersprachen gibt es verschiedene Konventionen für die Benennung der Dinge. Beispielsweise die Variablen oder Parameter. Zu den Namenskonventionen gehören beispielsweise die Reddick-Namenskonventionen, in denen eine String oder Long-Variable mit strNachname oder lngZeile benannt wird. Oder die ungarische Notation, in welcher diese variablen stNachname und lZeile genannt würden.

Wer in VBA programmiert stellt schnell fest, dass die Kombinationsfelder, Listenfelder, Register und Multiseiten eine andere Struktur haben als die anderen Steuerelemente auf den Dialogen (Userformen):

Nicht nur, dass sie Null-basiert sind (anders als die übrigen Elemente), die Parameter werden nicht mit „sprechenden“ Namen angezeigt (wie eigentlich üblich), sondern gemäß der dort verwendeten Namenskonvention:

Und so bin ich glatt reingefallen, weil der letzte Parameter nicht IIndex (also mit zwei „ii“) heißt, sondern klein-L-Index … Verwirrend!

Schatz, findest du, dass meine Kurzhaarfrisur zu streng aussieht? – Sir, nein – Sir!

Hallo Herr Martin,

ich bekomme es nicht hin, anbei mein Diagramm, es sollte allerdings so aussehen und ich bekomme es einfach nicht hin.

So sieht es bei mir aus:

Wären Sie so freundlich und würden mir helfen.

Danke

Mfg

Hallo Frau Dobner,

ich helfe doch gerne. Für Ihr Problemchen gibt es zwei Lösungen: entweder sie vertauschen Zeile und Spalte:

Allerdings „erkennt“ dann die Liste Spalte 1 als Daten. Diese muss man rausnehmen und den Datenreihen1 und Datenreihen2 als Legendeneinträge zuweisen (die Jahreszahlen)

Besser. Sie schreiben die Daten von oben nach unten:

Letzteres Variante finde ich einfacher …

Hilft das?

Liebe Grüße Rene Martin

Ich hasse es, wenn ich ein Lied laut mitsinge und der Interpret den Text nicht kann!

Schon doof. Excel zeigt viele Dinge an, aber leider nicht die Liste der Pivottabellen. Mourad Louha schlägt ein kleines VBA-Skript vor, um die Liste aller Pivottabellen auszulesen:

Public Sub ListPivotTables()
Dim c As Long
Dim b As Worksheet
Dim o As PivotTable
Dim St As Worksheet
On Error Resume Next
Set b = ThisWorkbook.Worksheets.Add
c = c + 1
b.Cells(c, 1).Value = "Name"
b.Cells(c, 2).Value = "Quelle"
b.Cells(c, 3).Value = "Aktualisierung"
b.Cells(c, 4).Value = "Arbeitsblatt"
b.Cells(c, 5).Value = "Bereich"
b.Cells(c, 6).Value = "MDX"
For Each St In ThisWorkbook.Worksheets
For Each o In St.PivotTables
c = c + 1
b.Cells(c, 1).Value = o.Name
b.Cells(c, 2).Value = o.SourceData
b.Cells(c, 3).Value = o.RefreshDate
b.Cells(c, 4).Value = o.Name
b.Cells(c, 5).Value = o.TableRange1.Address
b.Cells(c, 6).Value = o.MDX
Next
Next
End Sub

Ich habe es ein klein wenig überarbeitet. Das Ergebnis:

Oder man benennt die Datei mit der Endung ZIP um, entpackt das Archiv und wirft einen Blick in die beiden Ordner pivotCache und pivotTable. Darin verbergen sich sämtliche Informationen zu den Pivottabellen:

RTL wird 30. Für die RTL-Zuschauer: Das sind drei Mal alle Finger.

PowerPoint-Schulung. Wir erstellen eine Grafik. Dafür verwende ich die Firmenvorlage. Ich zeige dem Teilnehmer, wie man eine Form erstellt. Sie verschwindet. Ich bin erstaunt. Noch einmal: Form auswählen, Rechteck aufziehen – nichts ist sichtbar. Doch: beim Aufziehen sieht man die Form. Dann ist sie unsichtbar. Aber markiert. Also: Konturfarbe wählen. Die Form erscheint. Die Füllung ist transparent und wird ebenfalls sofort mit einer Farbe versehen. Wir beginnen Text einzutragen – die Form „schnurrt“ auf ein Minimum zusammen. Auch hier ist der „Bösewicht“ schnell gefunden: im Aufgabenbereich „Form formatieren“ wurde in den Textoptionen eingestellt, dass sich die „Größe der Form dem Text anpassen“ soll.

Dann klappt es: wir können eine Grafik erstellen und beschriften:

Jedoch: bei weiteren Formen vergesse ich die Grundeinstellungen, übertrage die Formatierung mit dem Pinsel „Format übertragen“ – padautz – die Form wird wieder klein, weil noch kein Text in der Form steht …

Als ich frage, wer diese Vorlage erstellt hat, erhalte ich keine Antwort. Ich bitte darum, sie noch einmal zu überarbeiten …

Es genügt ein kriechendes Auto im Berufsverkehr, um die Illusion zu zerstören, ich sei ein netter, ausgeglichener und sympathischer Mensch.

Ab und zu muss ich meine Dogmen und Glaubenssätze über Bord werfen. Beispielweise die Antwort auf meine Frage in Excel: „Wie findet man in einem geschützten Formular die nicht gesperrten Zellen?“

Bislang zeige ich meinen Teilnehmern und Teilnehmerinnen, dass man mit der [Tabulatortaste] von (offener) Zelle zu (offener) Zelle in einem geschützten Excelformular springen kann – wie im Internet, wie in einem Word-Formular oder einem PDF-Formular.

Weit gefehlt!

Sind mehrere Zellen nach unten (!) verbunden, „springt“ Excel von links nach rechts und von rechts nach links. Aber nicht weiter nach unten!

Ich muss meine Antwort noch einmal überdenken!

Meine Frau ist zur Arbeit gefahren und hat gesagt: „Wenn ich zurückkomme, ist die Küche aufgeräumt!“ – Ich bin mal gespannt, wie sie das hinbekommt.

Gestern Excel-Schulung für

„Ich will da rauf!“ e.V.
Klettern für Menschen mit und ohne Behinderung

http://www.iwdr.de

Lustige Schulung; sehr nette Teilnehmerinnen.

Eine der gestellten Fragen hat mich amüsiert – ich habe schon einmal in diesem Blog darüber gepostet.

In einer Exceltabelle ist die erste Zeile und die erste Spalte fixiert. Ich erkläre, wie man mit [Strg] + [­Pfeil oben] nach „oben“ springen kann. Was ist oben?

Eine Teilnehmerin beschwert sich, dass sie nun nicht die erste Zeile der Datensätze angezeigt bekommt, sondern dass sich die Markierung in der Überschriftszeile befindet, die allerdings immer noch fixiert ist.

Als ich ihr erkläre, dass sie nun in einem zweiten Schritt mit [Pfeil unten] eine Zeile nach unten springen kann und so die ersten Zeilen der Tabelle sieht, ist sie zufrieden.

Liebe klopft nicht an und fragt, ob es gerade passt. Liebe passiert einfach. – Übrigens: genauso verhält es sich mit Durchfall.

Hallo Rene,

Eine Sache, die auch bei mir hier (trotz 6GB Arbeitsspeicher) immer wieder kommt, ist folgendes:

Nicht genügend Systemressourcen.

Debuggen kommt dann das hier.. Beim Ausführen einer ganz normalen SQL Anweisung, (zugegeben in einer Rekursion) bleibt er hier stecken..

Auf was muss ich denn da besonders achten?..
Oder könnte man das mit speziellen Funktion abfangen?

Viele Grüße nach München, Bernd

Hi Bernd,

Ich kenne solche Fehler … Nervig!

Versuch mal die Anweisungen rst!dblAnzWdh und Nz(rst!dblWeightPart, 0) in zwei (Double-)Variablen zu schreiben und dann das Produkt auszuführen (das Ergebnis wieder in eine dritte Variable), ebenso die rst!ID.

Und dann den SQL-Code zusammenzubauen.

Klappt das?

Liebe Grüße

Rene

Er sagte: „Ich oder der Hund!“ – Manchmal denke ich an ihn, wenn ich Gassi gehe.

PowerPoint-Schulung. Wir bauen uns ein Parlament.

Ich füge ein Diagramm ein, wähle den Diagrammtyp „Ring“ und trage die Daten ein: Sitze im Parlament:

Ich schließe das Datenblatt. Da ich einen Halbkreis haben möchte, bearbeite ich erneut die Daten in Excel:

Und trage die Summe unter den Zahlen ein (für den unteren Halbkreis):

Ich schließe die Daten – die Summe wird nicht übernommen:

Also noch einmal: Daten bearbeiten:

Ich versuche das blaue Ecke über die Summe zu ziehen, damit die Summe im Wertebereich eingeschlossen ist:

Weit gefehlt – es geht nicht! Der Bereich „springt“ immer wieder zurück:

Okay! Verstanden! Zeile löschen, Zahl per Hand eintragen, Datenblatt schließen (?!?) und DANN funktioniert es:

Ich bin heute so schlecht gelaunt. Ich könnte glatt eine Karriere im Öffentlichen Dienst machen.

Warum finde ich in der Liste der Seitengrößen kein Format A0? Ich möchte das Dokument als PDF speichern!

Bei meiner Kollegin habe ich das doch gesehen!

Die Antwort ist schnell gefunden: wird ein „normaler“ Drucker ausgewählt, der kein A0 drucken kann, wird dieser auch nicht vorgeschlagen. Man muss einen PDF-DRUCKER wählen, dann steht A0 zur Verfügung und jetzt kann man auch ein A0-PDF erstellen (speichern oder drucken):

Lachen ist in einer Beziehung wichtig. Aber nicht, wenn ich gegen einen Türpfosten laufe!

Hi Angelika,

Gestern habe ich eine ganz interessante Outlook-Frage erhalten. Wie siehst du das:

„Bspw versende ich einen Termin im Outlook an 200 Teilnehmer und erhalte rund 50 Absagen, soweit so gut. Sende ich jedoch ein Update dieses Termins, so erhalten auch jene Teilnehmer welche sich abgemeldet haben, wieder meine Info. Ich erhalte nun Antworten von Menschen welche sich ja schon eingangs bei mir abgemeldet haben und mir erneut und entnervt mitteilen, dass sie nicht kommen können.

Ich weiss, dass in IBM Lotus Notes, all jene Teilnehmer welche sich einmal für den Termin abgemeldet hatten, dann auch keine nervigen Updates mehr erhalten.“

Meine Antwort wäre: das geht nicht. Outlook geht davon aus, dass eine Absage nicht eine Absage zu dem TERMIN ist, sondern zu dem ZEITPUNKT an dem der Termin stattfindet. Deshalb wird ein Verschieben den Leuten mitgeteilt als Möglichkeit JETZT teilzunehmen.

Wie siehst du das? Oder weißt du einen Schalter? Ich würde ihr raten die Leute, die abgesagt haben aus der Einladung rauszuwerfen … Dann könnte es aber sein … oder? Oder einfach einen kleinen Kommentartext in die Einladung zu schreiben.

René

Hallo guten Morgen,

Ja, René, es ist genau so, wie Du es sagst.

Man könnte künftig vielleicht eine Umfrage vorschalten, wer überhaupt Lust auf die Veranstaltung hat und dann nur die zum Termin einladen, die Ja gesagt haben. Da passt es dann auch bei Verschiebungen

Viele Grüße von Angelika

Ich bin Neptun, Herr des Wassers! Spürt meine Macht, ihr Untertanen! – Alter, hör auf, meine Goldfische mit einer Gabel zu jagen!

Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!

In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.

Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:

If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then

Klappt wunderbar.

Fast.

Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:

If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then

und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.

Ich prüfe:

der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:

AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:

If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then

Und das funktioniert! Ich bin glücklich!

Ich bin ein Mann! Ich kann tun und lassen, was meine Frau will.

Ein Bild. Ein sehr großes und scharfes Bild. Eine Architekturzeichnung:

Ich füge es in eine Excel-Arbeitsmappe, speichere sie, öffne sie und sehe:

Unscharf!

Okay – Excel stellt die Option „Bilder in Datei nicht komprimieren“ zur Verfügung. Hätte ich auswählen sollen:

Allerdings: in Visio? Was mache ich in Visio, wo eigentlich Bilder hingehören und herkommen? Dort suche eine solche Einstellung vergebens. Dort wird ab einer bestimmten Dateigröße komprimiert:

Okay – zugegeben – dort kann man die VDSX-Datei als ZIP umbenennen, extrahieren und dann im Ordner „media“ das verkleinerte Bild durch das Original ersetzen:

Danke an Tanja Kuhn für den Hinweis!

Meine Kaffeemaschine wurde repariert. Der Techniker meinte, die Muffbrieme war verbrömmelt und angeeumelt. Man musste nur die Dichtbrumme abquastern und neue Flanschpaddendranwöhnern. Die sind nun vierfach gefröbbelt und neu umwienert. Na – das hätte ich auch selbst machen können.

Witzig. Vorlauter Formeln und Assistenten übersieht man manchmal die Wirklichkeit.

Wir schauen die Zielwertsuche an. Mit der Funktion RMZ (regelmäßige Zahlungen) wird die Annuität berechnet.

Nun soll „zurückgerechnet“ werden. Ich möchte wissen wie hoch der Zinssatz bei einer Rückzahlung von 220 Euro ist. Also Zielwertsuche (sie soll ja auch gezeigt werden:

Sie rechnet und liefert:

-4,92% !?! Minus !?!

Es dauert eine Weile, bis wir verstehen: Das Ergebnis ist korrekt. Man müsste mit negativen Zinsen rechnen, um auf diesen Betrag zu kommen … Doch nicht falsch und auch nicht so unrealistisch.

Danke an Hartmut Hilbich für dieses Beispiel.

Natürlich spreche ich mit mir selbst. Manchmal brauche ich eben eine kompetente Beratung.

Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:

IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:

Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …

Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!

Und: während sich auf der Microsoft-Hilfeseite

https://docs.microsoft.com/de-de/powerquery-m/number-round

leider kein Hinweis dazu findet, hat Ken Pulse es sehr gut erläutert:

https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/

XLarium weist darauf hin, dass es noch weitere Artikel und Videos zu diesem Thema gibt:

MrExcel hat vor kurzem ein Video dazu gemacht:

Weitere Videos zum Thema Runden:

MROUND In Power Query

TRUNC In Power Quer

CEILING oder FLOOR in Power Query

Ich habe 25 Jeans im Schrank. Eine, die ich jeden Tag trage, eine hippe, die ich nie trage, weil ich eigentlich nicht hip bin und 23 ausrangierte aus den Jahren 2000 – 2020, die ich für potenzielle Maler-, Garten-, Renovierungs- oder Umzugsarbeiten aufgehoben habe.

Zugegeben: geschmunzelt habe ich schon:

Excelschulung. Ich erkläre und zeige (intelligente, dynamische, formatierte, strukturierte) Tabellen:

Ein Teilnehmer behauptet, dass diese Tabellen seine Überschriften löschen würden. Ich bin irritiert. Ich habe eine Weile gebraucht, um zu verstehen, dass die weiße Schriftfarbe, die Excel als Standard einsetzt, nicht sehr clever ist bei einer gelben Hintergrundfarbe …

Natürlich ist die Überschrift noch vorhanden. Nur eben – sehr schlecht lesbar!

Wie komme ich zur Vernunft? – Tut mir leid, ich bin auch nicht von hier.

Outlookschulung. Es kam eine Frage zur Lesebestätigung. Ich sende mehrere Mails mit Lesebestätigung an verschiedenen Personen.

Einige haben einen grünen Haken, andere nicht !?! Diejenigen mit einem grünen Haken haben beim Öffnen eine Gruppe „Anzeigen“:

Wenn ich auf den „Status“ klicke, sehe ich die Liste der Mails – ohne Antwort – obwohl ich die Lesebestätigung bejaht habe.

Bei den anderen fehlt diese Gruppe – obwohl eine Lesebestätigung dranhängt.

Auch hier habe ich meine Kollegin gefragt – sie wusste auch keine Antwort. Auch hier muss ich mit einem Schulterzucken verbleiben. Sorry … Wenn ich noch etwas erfahre, melde ich mich wieder. Kennst du die Antwort auf diese Frage / die Lösung für dieses Problem?

Boah, diese Pilze flashen extrem. Ich sehe jetzt plötzlich magersüchtige Kühe. Kevin, iss deine Champignons. Das sind unsere beiden Dalmatiner.

Ich hätte schwören können, dass es vorhanden ist. Eine Teilnehmerin in der letzten Schulung wollte das Entspricht-Zeichen ( ≙ ) haben. Das Gleichheitszeichen mit dem Dach darüber. Ich hätte es in der Liste der Zeichen vermutet, die die Schriftart Symbol zur Verfügung stellt. Oder im „normalen“ Zeichensatz.

Ich wurde eines Besseren belehrt. Man findet es beispielsweise in der Arial Unicode:

Ich schaue auf dem Firmenrechner nach. Bei dieser Firma ist die Arial Unicode nicht installiert. Was tun?

Nun – wir finden das Zeichen auf einer Seite im Internet und kopieren es von dort heraus. Und hinterlegen es in der AutoKorrektur. Die Teilnehmerin war glücklich. Und ich sehr erstaunt, dass dieses (wie ich dachte) wichtige Zeichen nicht vorhanden ist. Ich werde mal an die IT schreiben und bitten die Unicode-Schriften nachzuinstallieren. In Word könnte man es in einem Feld nachbauen, aber in Excel, PowerPoint und Outlook?

Nein, ich habe deine Kochkünste nicht kritisiert. Ich habe lediglich gesagt, dass wir den einzigen Hund im Ort haben, der nicht am Tisch bettelt.

Hallo Herr Martin,

ich filtere in dem Kunden-Excel die Werte mittels einer Pivot-Tabelle. Der Filter zeigt aber nur die tatsächlich vorhandenen Werte an.

Jetzt möchte ich im Pivot einen festen Wertefilter definieren, unabhängig welche aktuellen Werte vorhanden sind:

Beispiel:

Ich möchte immer alle Einträge < 24 Stunden gefiltert haben

Aktuelle Werteinträge sind 1 und 2 Stunden. Den Filter 24 Stunden kann ich aber erst auswählen, wenn es mindestens einen Eintrag mit 24 Stunden gibt. Lässt sich dies im Pivot einstellen. Ich habe bislang keine Möglichkeit gefunden.

Hallo Herr H.,

der Gedanke der Pivottabelle ist ja, die vorhandenen Werte zu gruppieren und die Zahlen zusammenzufassen (aggregieren, also: summieren, zählen, …) Wenn Sie andere Werte sehen möchten, müssen diese in der Liste stehen (man müsste sie ausblenden).

Hallo Herr Martin,

es gibt im Office 365 Excel den Befehl FILTER, der genau das macht, was ich benötige. Nur hat mein Kunde leider eine ältere Version. Lässt sich das in einem älteren Excel mit einem workaround bauen?

Viele Grüße

Hallo Herr H.,

Nein – bitte nicht die Funktion FILTER verwenden – sonst hat die Firma ein Problem!

Was würde ich tun?

* entweder die Daten dazwischen verstecken (und die Zeilen ausblenden)

* oder die Daten auf einem anderen Blatt sammeln und dort alle notwendigen Daten einsammeln.

Hum. Sonst? Müsste mal überlegen

Liebe Grüße

Rene Martin

Hallo Herr Martin,

Filter geht beim Kunden nicht, aber ist die Funktion so gefährlich?

Ich habe mittels Pivot die Daten auf ein anderes Blatt ausgelagert und nutze das Ergebnis für die Dropdown-Felder.

Ich habe jetzt einen Dummy-Wert eingefügt, damit die Pivots die Auswahlfelder behalten, auch wenn keine Daten auszuwerten sind. Ggf. wäre eine Lösung ganz ohne Pivots zu arbeiten, aber dazu bräuchte ich so etwas wie die Filter-Funktion.

Hallo Herr H.,

nein, nein: FILTER & co sind klasse – Problem: nicht jeder hat diese Funktionen. Deshalb: bauen wir den Filter doch nach!

Werfen Sie mal einen Blick in meine Liste: In Spalte H befinden sich die sechs Werte. Einer wird in J2 ausgewählt. In Spalte L ermittle ich die Zeilennummer, falls gefunden. In Spalte M sammle ich diese Nummern ein; gruppiere sie also. Mit BEREICH.VERSCHIEBEN baue ich die Liste ab O1 auf (ich hätte auch INDIREKT oder INDEX / VERGLEICH verwenden können).

Ich könnte es auch mit AGGREGAT aufbauen – aber lassen wir das …

kommen Sie damit klar?

Liebe Grüße

Rene Martin

Die Kondomindustrie hat echt Humor. Die packen zehn Stück in eine Packung und dann sind de nur vier Jahre haltbar.

Andreas Thehos erhält Post:

Hallo Andreas,

Kannst du auch einmal ein Tutorial über diese defekten Tabellen machen und wie man die wegbekommt? Bei meinen Recherchen habe ich bis jetzt nur gefunden das die erstellt werden wenn Excel abstürzt. Die einzige Lösung de in foren geholfen hatte, war eine neue Mappe zu erstellen und alle Daten zu übertragen. Aber das kann doch nicht die Lösung sein oder? Alle Änderungen die vorgenommen werden beziehen sich nur auf „DieseArbeitsmappe“

Andreas weiß keinen Rat. Und ich? Ich hatte mal eine solche korrupte Datei gesehen – ist schon eine Weile her. Und nein – sorry – ich weiß leider auch keinen Rat.

Aber Andreas wird fündig:

https://stackoverflow.com/questions/18273071/excel-vba-project-has-generated-multiple-workbook-objects/49073426

Ich sitze im Café und verbinde mich mit dem Netzwerk „Martin Router King“. Was soll man sagen? – „I have a stream.“

In der letzten Excelschulung haben wir über Listen gesprochen. Beim Sortieren von Daten kann man nach Zeilen und Spalten sortieren. Man findet diese Einstellung in den Sortieroptionen:

Prompt kam die Frage, ob man auch nach Spalten filtern könne.

Zuerst überlegte ich, ob dies überhaupt sinnvoll sei.

Die korrekte Antwort lautet: Nein! man kann den Autofilter nicht neben der ersten Spalte einschalten. Man muss die Liste transponieren:

Dann kann man den Filter für die erste Zeile einschalten, die in der ursprünglichen Tabelle die erste Spalte darstellte:

Und so ergeben sich sicherlich einige Anwendungsbereiche.

Ich kaufe keine Bio-Lebensmittel. In meinem Alter nehme ich alles, was ich an Konservierungsstoffen bekommen kann!

Vorgestern in der Outlook-Schulung. Eine Teilnehmerin fragt mich, warum sie ihre Ordner nicht verschieben kann. Damit ein Ordner an erster Stelle stehe, müsse sie einen Unterstrich einfügen. Ich glaube es erst, als ich es sehe und wundere mich sehr.

Wir suchen eine ganze Weile und werden fündig:
Schaltet man in Outlook in der Registerkarte „Ordner“ die Option „Alle Ordner von A nach Z anzeigen“ ein, werden sie sortiert, können jetzt aber in ihrer Reihenfolge nicht mehr verschoben werden!

Ich zeige Männern gerne, was ich im Bett mag. Leider schlafen die meisten während meiner einstündigen PowerPoint-Präsentation ein.

Gestern Excelschulung. Ich zeige, wie man Zahlen benutzerdefiniert formatieren kann. Eine Teilnehmerin meldet sich zu Wort. Wie man Zahlen mit Nullen auffüllen kann. Sie hat „alte“ Personalnummern – diese sollten führende Nullen erhalten. Nun – das Zahlenformat 00000 ist wohl kein Problem:

Dann kam die Frage, wie man die Anzahl der Nullen so festlegen könne, dass die größte Zahl keine führende Null hat, alle anderen sich daran ausrichten.

In den benutzerdefinierten Zahlenformaten kann man leider keine Formel eintragen. Deshalb geht das DA wohl nicht. Aber man kann benutzerdefinierte Zahlenformate in der bedingten Formatierung einsetzen:

Die größte Anzahl der Ziffern (vor dem Komma) kann beispielsweise mit

=MAX(AUFRUNDEN(WENNFEHLER(LOG10(A:A);0);0))

ermittelt werden. Nun kann man überprüfen, ob dieses Ergebnis = 1, dann: Zahlenformat: 0. Ergebnis = 2, dann: Zahlenformat: 00, Ergebnis = 3, dann: Zahlenformat: 000, Ergebnis = 4, dann: Zahlenformat: 0000, und so weiter.

Funktioniert. Ist aber weder schön, noch elegant noch schnell!

Ich schlafe gerne nackt. Wenn es jemanden stört – es fahren auch noch andere Busse.

Hallo René,

habe mir dein Buch mal durchgearbeitet. Respekt, viele richtig spannende Sachen dabei.

Einen Satz habe ich allerdings nicht verstanden. Unter 4.10 auf Seite 104 sagst du, dass es nicht möglich sei Hintergrundfarbe, Schriftart und -größe in Notizen festzulegen.

Sie mal hier…

Hallo Hannes,

stimmt: ich hätte noch das Wörtchen „als Standard“ hinzuschreiben sollen. Du kannst nicht die Standardschrift, Standard-Hintergrund … von Notizen in Excel festlegen

Liebe Grüße und danke für den Hinweis

Rene

In einem Meeting einen Zettel hochhalten: „Sprecht ganz normal weiter! Wir werden abgehört! Lasst euch nichts anmerken!“

Lieber Rene

Wie geht es dir? Ich habe wieder mal eine Frage:

Im Anhang findest du eine Tabelle. Was ich möchte ist folgendes:

Ich möchte wissen, welche Zahlen zwischen der kleinsten und der grössten Zahl alle noch fehlen. Am liebsten hätte ich alle fehlenden Zahlen in einer Spalte.

Herzliche Grüsse

Andreas

Hallo Andreas,

ich verstehe nicht ganz – in der Liste befinden sich keine Zahlen, sondern Texte. Texte der Form CHE-xxx.yyy.zzz

Was heißt in diesem Zusammenhang „die kleinste und die größte Zahl“?

LG ::  Rene

Lieber Rene

CHE-000.000.000 (kleinst mögliche), CHE-000.000.001 …..  CHE-999.999.999 (grössmögliche).

Vielleicht sollte ich besser „Nummern“ schreiben, das „CHE-“ könnte man auch weglassen, man könnte auch die Punkte durch tausender Trennzeichen ersetzen, damit es  Zahlen werden: 000’000’001 bis 999’999’999

In der Matrix hat es eine Auswahl von möglichen „Nummern“, eine „Nummer“ ist die Grösste, eine die Kleinste, dazwischen hat es in der Matrix welche, aber es fehlen auch welche. Ich möchte gerne alle Nummern, von der kleinsten Nummer bis zu Grössten Nummer, die mir noch fehlen und so, dass ich Sie in einer Spalte habe und sortieren kann.

Konnte ich mich verständlich ausdrücken?

Herzliche Grüsse Andreas

Hoi Andreas,

kurz nachgeschaut:

die kleinste „Zahl“ in deiner Liste ist CHE-100.000.058, die größte: CHE-499.992.187. Das heißt: wir müssten fast 500 Millionen Zahlen prüfen.

Ich habe es mal mit den Zahlen zwischen 100.000.000 und 101.000.000 versucht – bei einer Formel (bist du drin? -> Zählenwenn) geht Excel in die Knie. Nicht auszudenken, wenn du das in einer Spalte „zusammengefasst“ haben möchtest und das ganze x 500!

Da stürzt Excel ab.

Ich würde es entweder mit PowerQuery lösen oder mit VBA.

Liebe Grüße

René

Hoi Rene

1.000 Dank!

Ich prüfe das nochmals…

Herzliche Grüsse

Andreas

Nach zwei Stunden Babysitten glaube ich, dass ich doch keine Kinder, sondern lieber etwas Harmloseres möchte. Krokodile zum Beispiel. Oder Löwen.

Hallo Excel-Meister

ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die Metadaten rauslesen soll.

Das Auslesen geschieht über Power Query (what else….), aber ich muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query Abfragen on the fly erstellen. Letzteres geht problemlos.

Der Ablauf:

  1. Prüfe, ob User die pbix geöffnet hat.
  2. Falls nicht, bitte freundlich darauf aufmerksam machen
  3. Falls nein, Abbruch – falls ja, pbix öffnen.

Bis dahin klappt alles.

Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI Datei authentifizieren muss, nachdem er ja gesagt hat.

falls er aber den Dialog hier abbricht (…..DAU…….), kommt eine „schöne“ Meldung:

Nun meine Frage:

Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie fange ich das ab?

Bin schon voller Zweifel…..

Merci, lieber René für deine Geduld mit mir

Freundliche Grüsse Hans Peter

########################################

die unwissenden erleuchten sich selber

habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René

ich danke dir!

Hier der Code, falls es dich interessiert.

Sub GetData()

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚ Code erstellt durch: Pfister BI Consulting GmbH

‚ Zweck: Holt Metadaten aus der Power BI Datei

‚ Erstelldatum: 8.3.2021

‚ Aenderungsdatum:

‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

‚Testen, ob pbix Datei geöffnet ist. Falls nicht, Mesagebox und fragen, ob sie geöffnet werden soll. Fall nicht, Abbruch

    If Dateigeoeffnet(Range(„Dateipfad_PBIX_Original“)) = False Then

        If MsgBox(„Die Datei muss geöffnet sein. “ & Chr(10) & “ Soll die Datei geöffnet werden?“, vbYesNo, „Power BI Datei öffnen?“) = vbNo Then

            Exit Sub

            Else: Call Open_PBIX

                Application.Wait (Now + TimeValue(„0:00:10“))

            End If

   End If

’notwendige Abfragen aktualisieren

Abfragen_starten:

On Error GoTo ErrHandler

    ActiveWorkbook.Connections(„Abfrage – Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Memory Usage Tabellen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Tabellenliste“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Liste nicht geladene Queries“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

    ActiveWorkbook.Connections(„Abfrage – Abfragen – nicht geladen“).Refresh           ‚Teil Abfrage – “ muss unbedingt vermerkt sein!

  Call Listen_befuellen

ErrHandler:

    ‚Fehler No. 1004 abfangen

    If Err = 1004 Then

        If MsgBox(„Soll der Prozess abgebrochen werden?“, vbYesNo, „Bitte Identifikation vornehmen“) = vbYes Then

            Exit Sub

        ’sonst Abfrage wieder aufnehmen

            Else: Resume Abfragen_starten

        End If

    End If

End Sub

Gruss Hp

Seirtdem ich mich vom Beinbruch erholt habe, laufe ich besser als früher. Jetzt fehlt mir nur noch eine Gehirnerschütterung.

Hallo Rene,
ich benötige BITTE DRINGEND Deine Hilfe!!!
Ich habe hier irgendetwas abgeschossen……
Kann eine ausgeschnitte Zeile nicht mehr einfügen….
DANKE

es geht es geht……
DANKE DANKE DANK!!!!!!!!!!!
Was war es???

vor der Antwort eine Frage:
klick mal bitte in Excel auf Datei / Konto.
hast du auch Version 2102?

Hallo JA, die habe ich auch.
wobei jetzt gerade ein update läuft…. 🙁
hoffe es geht dann noch alles….
habe jetzt: Version 2102 Build 13801.20294
ABER ES LÄUFT!!!

Eben, Jörg: Microsoft macht Updates. ich vermute stark, dass Microsoft etwas kaputt gemacht hat.

In eurem Makro verwendet ihr ein uraltes Tool, um Masken (Dialoge) anzuzeigen. Aus Excel 4.0. Da lief ungefähr in der Zeit, als die Dinosaurier ausstarben. Ich habe dich ja im Dezember mal gefragt, ob man (ich) nicht mal den ganzen alten Schrott, der in euren Makros steckt, erneuern soll. Und ich fürchte: heute ist es passiert – da wird etwas aus den 90er Jahren nicht mehr unterstützt …

Ich habe dir im Dezember einen Screenshot von der Maske geschickt. Deshalb wusste ich, dass sie damals bei mir noch lief. Heute bei mir auch nicht mehr.

ich habe die Maske mit den aktuellen Bordmitteln nachgebaut – deshalb sieht sie ein klein wenig anders aus … beispielsweise der Titel in der Titelzeile.

Ich werde das mal in Foren posten und fragen, ob jemand eine ältere Version hat – ob es da noch läuft …

Bevor dein Chef dich tötet, flehe um Gnade und sag ihm einen schönen Gruß von mir, dass Microsoft Sachen kaputt macht … nicht nur du …

Liebe Grüße

Rene

####

Jörg war glücklich. Ich habe ihm vorgeschlagen, das Makro zu überarbeiten. Habe ihm ein Angebot gemacht. Er hat sich seitdem nicht mehr gemeldet.

Er hat zu Hause immer das letzte Wort: Meistens: „Ja, Schatz!“

Vor zwei Tagen habe ich eine sehr nette, sympathische Gruppe von „Ich will da rauf! – Klettern für Menschen mit und ohne Behinderung“ in Excel unterrichtet. Wer schauen möchte:

https://www.iwdr.de/

Als ich Notizen (früher: Kommentare) zeigte, kam die Frage, wie man denn diese roten Ecken ausblenden könne.

Ich habe gestutzt. Meine Gegenfrage: „Warum wollen Sie das?“ wurde mit einem „ich möchte nicht, dass andere sofort meine Kommentare sehen“ beantwortet.

Nun gut.

Ich gestehe: ich habe ein bisschen suchen müssen:

Die Antwort: in den Excel-Optionen in Erweitert findet sich in der Gruppe „Anzeige“ die Option „Keine Kommentare, Hinweise oder Indikatoren“.

Zugegeben: Über Überprüfen / Notizen / Alle Notizen anzeigen könnte man sie wieder einblenden. Ebenso über den Aufgabenbereich Start / Bearbeiten / Suchen und Auswählen / Auswahlbereich:

Nachricht von den Nerven: „Falls du uns suchst – wir sind am Ende.“

Erstaunlich! Auf dem letzten Excelstammtisch, den Frank Arendt-Theilen organisiert hat, hat er angemerkt, dass die PowerQuery-Funktion Date.WeekOfYear, die man über Spalte hinzufügen / Datum / Woche / Woche des Jahres nach dem US-amerikanischen Modell rechnet. Zwar verfügt Excel seit vielen Versionen über die Funktion ISOKALENDERWOCHE, welche die KW korrekt nach ISO 8601 berechnet. Auch Outlook unterscheidet bei den Kalenderwochen zwischen USA und Europa. Jedoch nicht PowerQuery. „Haben die das vergessen?“, fragt Frank. Also muss man diese Funktion in PQ nachbauen …

Ich habe einige Punkte von der ToDo-Liste auf die WasSolls-Liste gesetzt.

Ein großes Dankeschön an Frank Arendt-Theilen, der gestern den Excelstammtisch organisiert hat. Es war wieder ein toller, informativer, spannender und erfrischender Abend.
Auch ein Dankeschön an Sven Amrhein für seinen Vortrag über PowerQuey, Excel und SharePoint. Bemerkenswert ist sein Hinweis zu der Fehlermeldung.

Eine Ausnahme vom Typ „Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException“ wurde ausgelöst.
Der Mashup-Fehler tritt bei der Datenabfrage von einer Excel-Datei auf eine zweite Excel-Datei auf, die dann auf dem SharePoint liegt.

Man muss das Dokument entweder auschecken und wieder einchecken oder – es gibt eine bessere Lösung:
Unter Datei / Informationen befindet sich ein Feld für den Manager. Dort müssen alle Anwenderinnen und Anwender eingetragen werden, die mit dieser Datei arbeiten. Dann kann reibungslos und ohne Fehlermeldung damit gearbeitet werden.

Danke an Sven für diesen wichtigen Hinweis.

Klar müsste ich mal wieder die Fenster putzen. Andererseits ist Privatsphäre auch wichtig.

Hi René, Danke für die Präsentation. Was mich kurz interessieren würde: Wenn ich z. B. das Währungssymbol von Ungarn möchte, scrolle ich mir einen Wolf, bis ich es gefunden habe. Geht das schneller? Wie lange brauchst du dafür? Tipp: Ungarn liegt zwischen Kamerun und Haiti. Oder: Polen zwischen Lateinamerika und Mazedonien. Klar, einmal gefunden, kann ich das Format einfach übertragen. Aber hin und wieder kommen so Währungs-Exoten und da ist die Auswahl sehr mühsam. Danke und dir einen schönen Abend! Christa

Hi Christa, interessante Frage. Stimmt: wenn du „H“ drückst, springt Excel zu HUF – also zu den Texten im ISO-Code – nicht zu den Symbolen. Tipp: in meinem Skript (hast du?) und auch im Internet gibt es Listen, wie man die Währungssymbole im ASCII-Code eingeben kann. Vielleicht schneller als die Sucherei. Die Ländersortierung ist immer merkwürdig. Meistens liegt eine englischsprachige Tabelle dahinter. Mich würde ja auch interessieren, ob es eine Liste der Symbole gibt. Steht das Excel? Ich mach mich mal auf die Suche… LG ::  Rene

Hi René, oh cool. Vielen Dank. Ja, ASCII hatte ich noch in der Ausbildung zur Europasekretärin. Und ich bin Baujahr 78 wohlgemerkt. Dein Skript habe ich gestern heruntergeladen. Danke!!! Ja, das würde mich interessieren, wenn du so eine Liste gefunden hast. Kein Stress

Hi Christa, ich habe mal ein bisschen probiert. Der Makrorekorder hilft nicht weiter, weil er die Zeichen nicht richtig codiert. Also: VBA scheidet aus. Ich habe mal die fast 300 Symbole angeklickt –  die Datei gespeichert, in .ZIP umbenannt und aus dem XML-Archiv die Währungssymbole herausgeholt. Auch nicht ganz befriedigend. Immerhin: ich habe entdeckt, dass es doch ein BTC-Symbol/Zeichen für Bitcoin gibt … irgendwo dazwischen. Wie man allerdings die angezeigten Texte der Combobox herausbekommt … keine Ahnung! Ich schick dir mal die Datei LG  ::  Rene

Übrigens: nach 200 Zahlenformaten hört Excel auf. Ich habe zwei Dateien erstellen müssen.

Gerne können Sie die Liste von meiner Seite herunterladen:

http://www.excel-nervt.de/wp-content/uploads/2021/03/Waehrungen.xlsx

Und: wer den Vortrag ansehen möchte – man findet ihn unter:

https://www.youtube.com/watch?v=9UI9IwDVlGc

Drosseln sind Vögel – sie in Motorräder zu stecken ist Tierquälerei.

Letzte Woche habe ich einen Vortrag auf dem Londoner Excel-meetup zu (benutzerdefinierten) Zahlenformaten in Excel gehalten. Dabei habe ich auch gezeigt, dass man mit dem Zahlenformat

[=1]0 „Motorrad“;0 „Motorräder“

die Zahl 1 anderes formatieren kann als die übrigen:

Johannes Sandkamp hat mich darauf aufmerksam gemacht, dass man die Zahlen auch bündig ausrichten kann. Der Unterstrich hilft dabei. Oder genauer: der Unterstrich gefolgt von einem Buchstaben. Also so:

[=1]0 „Motorrad“_e_r;0 „Motorräder“

Klappt. So sieht das Ergebnis aus:

Übrigens: wer den Vortrag ansehen möchte – man findet ihn unter:

https://www.youtube.com/watch?v=9UI9IwDVlGc

Ich liege hier wie Gott mich schuf! – Du hast einen beigen Pullunder mit Karomuster an und eine Feinrippunterhose. – Es ist ein grausamer Gott!

Vorgestern habe ich einen Vortrag über Zahlenformate in Excel auf dem Excel-meetup in London gehalten. Danach kam die Frage nach (ost-)arabischen Ziffern, beziehungsweise Datumsangaben. Ein Teilnehmer erzählte, wie schwierig das Umrechnen der verschiedenen Kalender ist. Ich probiere es aus:

Ich schalte die Oberfläche auf Persisch. Ich hätte auch Arabisch nehmen können. Ich trage die Zahl 1 ein, ziehe mit gedrückter [Strg]-Taste herunter. Klappt. Ich hole aus der Zeichentabelle die arabisch-indische Ziffer 1, und ziehe mit [Strg] nach unten. Klappt auch:

Amüsanterweise werden die Zahlen in „unserer“ arabischen Schreibweise in der Bearbeitungsleiste angezeigt.

Übrigens: ein Umstellen der Sprache auf Persisch (Farsi):

ermöglicht leider nicht die Eingabe der arabisch-indischen Ziffern:

Deshalb muss die Zeichentabelle herhalten:

Ich drücke [Strg] + [;] für das heutige Datum. Runterziehen – kein Problem. Ein Blick in das Kombinationsfeld Zahlenformat zeigt: das heutige Datum (12.03.2021) würde in das Datum 22.12.1399, das im Iran das aktuelle Datum ist, umgerechnet werden:

Ein Klick auf die Schaltfläche bestätigt das – nicht gerade vertrauenserweckend, wenn in der Bearbeitungsleiste ein völlig anderes Datum steht als in der Zelle:

Und die ostarabischen Ziffern? Ich wechsle die Datumssprache auf Farsi und finde dort mehrere Schreibweisen für das Datum:

Oder so?

Nicht gerade einfacher macht die Tatsache, dass wir dort noch einen dritten Kalender zur Verfügung haben: den Hijri-Kalender, den islamischen Kalender. Dort schreiben wir heute den 29.07.1442. Uff!

Übrigens: wer sich mit Arabisch und/oder persisch auskennt, stellt fest, dass die persische Ziffer für 4, 5 und 6 verwendet werden: ۴۵۶, nicht die arabische Ziffern: ٤٥٦

Ein Blick auf die Tabelle offenbart die Schwierigkeiten, die Microsoft zu bewältigen hat und auch Anwender und Anwenderinnen, die von einem Kalender in einen anderen umrechnen müssen:

Also wieder ganz schnell zurück zu Deutsch / Deutschland:

Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:

https://www.youtube.com/watch?v=9UI9IwDVlGc

und ein Dankeschön an Faraz Shaikh für seinen Hinweis zu seinem Video, indem er erklärt, wie man ein Datum aus dem Hijri-Kalender in ein Datum des gregorianischen Kalenders konvertiert:

https://www.youtube.com/watch?v=Du3uuhKel5w

Jeder sollte jemanden haben, der im entscheidenden Moment nachschenkt.

Vorgestern habe ich auf dem Excel-Meetup einen Vortrag über Zahlenformate in Excel gehalten. Nach dem Vortrag schreibt Tanja:

Hallo René

Danke für deinen Vortrag. War ein sehr guter Überblick.

Wieso hast du das Problem mit den Monaten über Hilfsspalten und nicht über die Gruppierung des Datums gemacht?

Dann wäre es ja ganz einfach die fehlenden Monate anzuzeigen.

Sonnige Grüsse aus der Schweiz

Hallo Tanja,

ich freue mich über Mails, die beginnen mit „warum hast du nicht …?“ Es gibt immer drei Möglichkeiten:

* entweder ich habe etwas übersehen – und es gibt wirklich elegantere Lösungen

* oder es gibt mehrere Lösungen; ich habe mich für die eine entschieden, weil …

* oder meine Lösung ist bewusst gewählt, weil …

Ich habe gestern Abend noch einmal nachgedacht:

[und dann folgt eine lange Erklärung, warum ich es nicht so gemacht habe, wie sie es vorschlägt. Das soll an dieser Stelle nicht wiederholt werden.]

Liebe Grüße  aus dem verregneten München

René

PS: Beim Durchschauen habe ich gerade bemerkt, dass die Option „Elemente ohne Daten anzeigen“ nicht aktivierbar ist, wenn das Datenmodell eingeschaltet ist. Wusste ich nicht …

Hallo Rene

Danke für deine Erläuterungen. […]

Ich kann mir vorstellen, warum das nicht aktiv sein kann. Beim Zusammenspiel mit dem Datenmodell kann ich ja auch nicht in der Pivot-Tabelle nach einem Datum gruppieren. Wenn ich es sauber aufbaue, brauche ich dazu im Datenmodell ja eine Kalendertabelle und dort kann das Problem mit fehlenden Jahren oder Monaten gar nicht auftauchen, da eine Kalendertabelle ja immer den kompletten «Datumsbereich» enthalten muss.

Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:

https://www.youtube.com/watch?v=9UI9IwDVlGc

Ich baue jetzt schon mal auf Placebo-Effekt: Sportsachen angezogen: der Körper denkt, ich mache Sport. Los: Kilos – purzeln!

Gestern auf Excel-meetup in London habe ich einen Vortrag über Zahlenformate in Excel gehalten. Anschließend kam folgende interessante Frage:

In einer Zelle steht =12/24. Das Ergebnis soll allerdings als Bruch dargestellt werden. Leider weigert sich bei der Zellformatierung Excel, ihn mit den ursprünglichen Werten 12 und 24 darzustellen:

Ich fürchte, das wird auch nicht funktionieren. Der Wert der Berechnung beträgt 0,5. Wie soll diese Ergebniszahl wissen aus welchen Werten sie entstanden sind. Natürlich kann mit FORMELTEXT die Funktion (und damit die Werte) anzeigen lassen – jedoch: DAMIT kann nicht weitergerechnet werden.

Übrigens: wer meinen gestrigen Vortrag ansehen möchte – man findet ihn unter:

https://www.youtube.com/watch?v=9UI9IwDVlGc

Ich soll der Praktikantin die Grundlagen für Arbeit mit Excel zeigen. Stehen jetzt im Supermarkt vor dem Spirituosenregal.*)

Hallo René, 

ich dachte, ich meld mich mal mit einer kleinen Excel-Anekdote (und einer kleinen Frage). Wahrscheinlich ein alter Hut für dich, ich fands aber ganz witzig. 😉

Zuletzt kam ein Kollege zu mir, er hat ne Excel-Frage. Er hat da eine Datei geschickt bekommen und wenn er da irgendwo in eine Excel-Zelle ne Zahl schreibt und auf „Währung“ beim Zahlenformat (das € Deutsch natürlich) klickt…dann steht da D-Mark.

Hatte ich so auch noch nicht gehört. Also schauen wir in unter „Zellen formatieren“. Da war ein benutzerdefiniertes Zahlenformat eingestellt. Kann es jetzt leider nicht 1:1 wiedergeben, da ich die Datei selbst nicht habe, aber es müsste das hier gewesen sein:

_-* #.##0 €_-;-* #.##0 €_-;_-* „-“ €_-;_-@_-

Nur anstelle das €-Zeichens stand „DM“. Wie kommt das da rein?

Ich schaue auf das Erstell-Datum der Datei…26.09.2001.

Auch gut, wenn eine Datei seit fast 20 Jahren in einem Unternehmen hin und her geschickt wird. 😉

So, jetzt zu meiner kleinen Frage. Die Einstellung Berechnungsoptionen > Manuell in der Registerkarte „Formeln“ ist, wenn ich mich nicht sehr irre, eine Einstellung, die alleine auf die jeweilige Arbeitsmappe beschränkt ist. 

Jetzt bin ich einigen Leuten begegnet, die steif und fest behaupten, dass diese Einstellung „rüberwandert“, wenn man mehrere Dateien offen hat. 

Also beispielsweise bekommst du per E-Mail eine Datei mit ausgeschalteter Berechnung, öffnest diese, hast aber noch zwei andere Dateien offen. Dann soll die Einstellung auf die zuvor geöffneten Dateien rüberwandern.

Ich konnte dieses Phänomen nie reproduzieren. Auch nicht, wenn ich aus der Mappe mit ausgeschalteter Berechnung heraus (z.B. mit Strg + n) eine neue erzeuge.

Hast du schon mal davon gehört?

Wäre es theoretisch möglich, dass man auf VBA-Ebene bei allen geöffneten Mappen die Berechnung ausschaltet, wenn ich die Datei öffne und Makros aktiviere? (Bei den Leute, die mir das berichtet haben, halte ich es eher für unwahrscheinlich, dass die xlsm-Dateien hin- und hergeschickt haben)

Danke dir und viele Grüße, 

Dominic

Hallo Dominic,

ich kann es nicht ganz nachvollziehen. Wenn du in der Excelmappe, die in der Zeit erstellt wurden als es noch keinen Euro gab, auf das Buchhaltungszahlenformatsymbol klickst, wird die Zelle als DM formatiert:

Wenn man das Tabellenblatt in eine neue Arbeitsmappe kopiert, ebenso.

Nicht jedoch, wenn ich einige Zellen in eine neue Datei kopiere; auch nicht, wenn ich das Blatt in eine vorhandene Mappe kopiere.

Ich wüsste nicht, wie dieses Zahlenformat in eine andere Datei „wandern“ kann … ich glaube diesen Menschen nicht, die so etwas behaupten …

Liebe Grüße Rene

*)PS: Danke für die hübsche Headline – sie wird den heutigen Tag überschreiben.

Solange nicht geklärt ist, warum die Dinosaurier ausgestorben sind, darf die Männergrippe nicht verharmlost werden.

Hallo Herr Dr. Martin,

da Sie mir schon mal bei dem Rauten Problem sehr geholfen haben, hoffe ich nun das Sie mir bei dem folgenden Problem auch helfen können. 

Ich kann in meinem Kalender aus einem Dropdown-Menü Tage Markieren an denen ich „im Urlaub oder Krank“ bin Auswählen. Nun möchte ich aber das nach der Auswahl der Wert 1 für einen Tag in einer anderen Zelle und auf einem andere Blatt angezeigt, und dann auch zusammen gezählt wird. Habe einen Teil dieser Funktion auch mit einer wenn Formel zb. =Wenn(H6“Krank“;“1“;““) bzw. =Wenn(B6“Urlaub“;“1“;““) hinbekommen, die Zahl 1 steht dann in der gewünschten Zelle nur wenn ich weitere Tage mit „Urlaub oder Krank „ markiere, sollten diese in der Zelle dann auch Aufaddiert werden  und das geht nicht. Wie kann ich das hinbekommen, können Sie mir da vielleicht auch weiter helfen?  Mit freundlichen Grüßen

Hallo Herr B.,

dafür gibt es zwei Lösungsansätze:

Entweder sie ermitteln in einer Spalte jeweils, ob in der Kalenderspalte der Text „krank“ steht:

=WENN(B2=“krank“;1;0)

oder in einer anderen Spalte, ob dort „Urlaub“ steht:

=WENN(B2=“Urlaub“;1;0)

Unter den Spalten ziehen Sie nun die Summe.

Oder Sie berechnen die Gesamtanzahl der „kranken“ Zellen mit

=ZÄHLENWENN(B2:B28;“krank“)

Das Gleiche für Urlaub.

Und beide zusammen? Na – addieren:

=ZÄHLENWENN(B2:B28;“krank“)+ZÄHLENWENN(B2:B28;“Urlaub“)

Werfen Sie mal einen Blick in die beigefügte Datei!

Liebe Grüße

René Martin

Dein Bart macht dich so männlich! – Nicht anfassen, die Himbeer-Aloe-Vera-Spülung muss erst einwirken!

Hallo Herr Martin

Es kommt wieder mal eine Frage aus der Schweiz.

Für den damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.

Haben Sie dazu eine Idee?

Hallo Herr S.,

im Ordner _rels befindet sich eine XML-Datei mit Namen .rels. In ihr muss eingetragen werden, dass im Ordner ribbon\customUI die Datei customUI1.xml mit der Ribbon-Definition liegt, beispielsweise so:

<Relationship Id=“rId2″ Type=“http://schemas.microsoft.com/office/2007/relationships/ui/extensibility“ Target=“ribbon/customUI/customUI1.xml“/>

Die Id muss eindeutig sein!

Liebe Grüße

Rene Martin

ceterum censeo: Headlines sind sinnlos!

Zugegeben: Ein bisschen nervös bin ich schon!
Alan Murray hat mich eingeladen am Dienstag, dem 09. März um 19 Uhr (MEZ) auf seinem Excel-meetup einen Vortrag zu halten. 175 Personen haben sich bereits angemeldet – Uff – vor so einem großen Publikum habe ich noch nie gesprochen!

Ich habe mir das Thema „Zahlenformate“ ausgesucht – gefühlte 100.000 Gimmicks rund um die Zahlenformate, die Excel bereithält. Beispielsweise die Unterschiede zwischen Währung und Buchhaltung / Currency and Accounting.

Interessiert? Neugierig?
Einfach anmelden:

https://www.meetup.com/de-DE/London-Excel-Meetup-Group/events/276115976/

Ich brauche heute einen zweiten Kaffee, der nachguckt, warum der erste seine Arbeit nicht macht …

Ich habe ein Problem mit Excel 2016:

In einer großen Tabelle (~17.000 Zeilen) muss ich ein einer Spalte eine Rechtschreibkontrolle durchführen. Hier scheinen „Alle ignorieren“ und „Immer ändern“ nicht zu funktionieren – wenn ich Fehler mit diesen Schaltflächen bearbeite (um z. B. ein bestimmtes Wort aus der Schweizer Rechtschreibung in die deutsche Standard-Rechtschreibung umzuwandeln), werden sie mir bei ihrem nächsten Vorkommen wieder angezeigt, statt (wie erwartet) einmal komplett durch die Spalte zu gehen und alle Vorkommnisse dieses Fehlers zu beheben. Dies bremst mich natürlich erheblich aus. Gibt es einen Trick, damit die Excel-Rechtschreibkontrolle so effektiv funktioniert wie in Word, oder muss ich mit diesem Problem leben? Und wurde das Problem eventuell in Excel 2019 behoben?

Ich danke für eure Antworten!

Hallo Herr P.,

Ich habe es gerade bei einer Liste mit 300.000 Zeilen probiert – in meinem Excel in Microsoft 365 läuft [alles ignorieren] prima durch: der Fehler „Huerth“ wird in Zeile 299.991 gefunden, in der übernächsten Zeile übergangen.

Fehler wird gefunden – „Alle ignorieren“
Dann wird der Fehler nicht mehr gefunden

Aber ich kann die Frage gerne mal posten – vielleicht hat jemand ein ähnliches Problem.

Liebe Grüße 

René Martin

Hallo Herr Martin,

vielen Dank für Ihre Rückmeldung. Es könnte wirklich ein Problem meiner „älteren“ Version von MS Office sein.

In jedem Fall freue ich mich schon auf Ihre Rückmeldung.

Liebe Grüße

Liebe Leserin, lieber Leser,

gerne dürfen Sie einen Kommentar abgeben, wenn SIE dieses Problem in Excel haben / kennen. Wir würden uns freuen.

Und: Die Namensliste, die Sie oben sehen, habe ich mit dem fakename-Generator erzeugt:

https://www.fakenamegenerator.com/

Ich muss meinem Gesichtsausdruck beibringen, wie man seine Meinung für sich behält.

Lieber René,

nachfolgender Code läuft fehlerfrei unter
Win 7 + Word 2010
Win 7 + Word 2013
Win 10 + Word 2019
Win 10 + Word 365

nur nicht unter Win 10 + Word 2016.
Die rot markierte Zeile ist mein Problemkind.

Vielleicht hast Du noch eine Idee.

Sub TopAktuell_Einfügen()
       Application.ScreenUpdating = False
   DocPath = "O:\Topaktuell\"
   ChDrive ("O:\")
   ChDir ("O:\Topaktuell\")
   Dim datei As String
         Selection.EndKey Unit:=wdStory
      For dokumente = 1 To 30
        If Dir(DocPath & dokumente & "_1.docx") = "" Then Exit For
   For seiten = 1 To 20
     datei = dokumente & "_" & seiten & ".docx"
     If Dir(DocPath + datei) = "" Then Exit For
    Selection.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
    FileName:=datei

     Next
 Next

   Application.ScreenUpdating = True
End Sub

Danke.

LG Traudl

Hi Traudl,

Seit über 20 Jahren schreibe ich VBA-Code. Dabei ist mir aufgefallen, dass sich einige wenige Objekte verändert haben. Ich habe mal vor vielen Jahren ein Makro geschrieben, das Text in eine Word-Tabelle geschrieben hat und den Text mit einem Hyperlink auf ein Word-Dokument versehen hat. Eine der Parameter beim Befehl Hyperlinks.Add wurde geändert.

Deshalb sage ich nicht, dass das nicht sein kann.

ABER! Bitte, bitte, bitte: adressiere doch sauber.
Ändere seinen Code in:
Dim wdBereich As Range

Set wdBereich = ActiveDocument.Range(Start:=ActiveDocument.Range.End - 1)

(Ende des Dokuments)

Und dort:
wdBereich.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=datei

Es kann natürlich sein, dass es trotzdem nicht unterstützt ist – aber eher unwahrscheinlich als das „Hoppeln“ an Ende:
Selection.EndKey Unit:=wdStory
Raus damit!

Und: wenn du gerade dabei bist:
ChDrive ("O:\")
ChDir ("O:\Topaktuell\")

Raus damit!

Und: deklariere die Variablen:
Dim docpath As String
Dim dokumente As Integer
Dim seiten As Integer
Dim datei as String

Und schließlich: Warum fügst du ein Word-Dokument in ein anderes ein? Ich würde es öffnen, an eine Objektvariable übergeben, den Inhalt (brauchst du die Formate?) entweder in einer Stringvariable speichern oder kopieren und ins Dokument einfügen.

Hilft das?

Liebe Grüße

Rene

Ich danke Dir ganz, ganz herzlich für Deine Vorschläge, lieber René.

Den Code hatte ich 2017 erstellt und war froh, dass er läuft. Er ist eine Sub aus einem unfangreichen Makro. Ich bin allerdings keine Programmiererin. Dazu benutze ich VBA viel zu wenig.

Deine Vorschläge werde ich natürlich umsetzen .

Ich gebe Dir Bescheid, wie es funktioniert.

Liebe Grüße Traudl

90% der Jugendlichen haben Schlafprobleme. Die restlichen 10% haben kein facebook.

Ich war irritiert. Ich erhalte eine Datei, öffne sie und bekomme beim Speichern eine mir bislang unbekannt Meldung:

Vorsicht: Teile Ihres Dokuments enthalten möglicherweise personenbezogene Informationen, die von der Dokumentprüfung nicht entfernt werden können.

Ich bin irritiert.

Ich suche.

Ich finde:

In der Makrosicherheit (Optionen / Dokumentschutzoptionen) wurde für diese Datei die Option „Beim Speichern personenbezogene Daten aus Dateieigenschaften entfernen“.

So ein süßer Bub! – Es ist ein Mädchen! – Aber er hat etwas Blaues an. – Sie wird zweifarbig erzogen.

Hallo Herr Martin,

ich zweifle an meinem Verstand – ich kann den Fehler in der Differenzrechnung  Zeile 38 nicht entdecken, obwohl ich alle Formatierungen und Eingaben mehrfach gecheckt habe. Der Fehler tritt nur in der Zelle i38 auf, die Formel habe ich mehrfach neu eingegeben und Zeilen und Zellen gelöscht usw. der Fehler bleibt  – HILFE!

Ich bin froh, dass es nur eine private Tabelle ist und nichts Wichtiges, aber so was ist doch nicht zu ertragen…

Übrigens: Ihre Sprüche sind grandios! Wenigstens konnte ich mehrfach laut lachen!

Mit freundlichen Grüßen Ulrike

Hallo Frau H.,

Danke für das Lob der Sprüche – manchmal befürchte ich, dass einige nur auf meine Seite wegen der dummen Sprüche klicken. Die sollen doch mit mir über Excel diskutieren! *lach*

Ich verstehe Ihr Problem nicht:

130,45- 150,31 = 19,86

SO haben Sie es in den anderen Zeilen auch gerechnet.

Wer von uns beiden „denkt falsch“?

Liebe Grüße

Rene Martin

Guten Tag Herr Martin,

erstmal danke für die schnelle Reaktion, die ich zuerst nicht verstanden habe. Beim erneuten Öffnen der gesendeten Mail konnte ich sehen, dass die Zeilen, um die es geht, darin nicht zu sehen sind, obwohl ich das ganze Blatt schicken wollte. Das liegt evtl. an der Druck-einstellung. Ich schicke die Mappe nochmal und stelle fest, dass es Zeile 39 ist, wo der Fehler auftritt (ich hab das mit der Nummerierung der Positionen verwechselt! – war ja auch schon etwas Gaga nach dem vielen rumprobieren) Die Formel heißt also: <=H39-G39> und müsste = ergeben, tut sie aber nicht.

Also noch mal meine Bitte, den Fehler zu identifizieren.

Liebe Grüße Ulrike

Hallo Frau H.,

Ich würde gerne helfen, aber Zeile 39 ist leer!

Liebe Grüße

Rene Martin

Dann bitte bei Zeile 40 nachschauen, folgende Beträge: 129,74 € – 61,67 € ergibt  nicht -67,80€ sondern – 68,07€! Ulrike

Da ist ein Zahlendreher in Ihrem Kopf oder ihrer Wahrnehmung, liebe Frau H.

In der Zelle G40 steht der Wert 129,47 und nicht 129,74:

Vier – sieben – NICHT: sieben – vier!

Liebe Grüße

Rene Martin

Oh mein Gott— gut, dass ich niemand anders gefragt habe – es ist zu peinlich!

Ganz lieben Dank für die Mühe! Ich werde die Seite weiterempfehlen – nicht nur wegen der Sprüche!

Gruß Ulrike

Was sagt der Teig zum Bäcker? – Ich bin gerührt!

Guten Tag Herr Martin,

ich bin durch Zufall auf Ihren Blog aufmerksam geworden.

Leider hat Excel immer wieder merkwürdige Verhaltensweisen.

Warum auch immer.

Ich habe auf einem Rechner ein Problem mit der Funktion Daten „Abrufen und transformieren“

Ich kann auf dem PC keinerlei Daten über diese Funktion aufrufen.

Keine CSV noch eine Tabelle aus einem SQL Server.

Ich bekomme immer wieder die Fehlermeldung, dass die Initialisierung der Daten fehlgeschlagen sei.

Lade ich die CSV auf einem anderen Rechner ein, funktioniert dies Problemlos.

Gleiches gilt bei der SQL Abfrage.

Haben Sie noch eine Idee wo ich das Problem suchen kann?

In der PowerQuery kann ich die Daten sauber sehen.

Nur das Einfügen der Daten aus der Verbindung in das Tabellenblatt funktioniert nicht.

Vielen Dank für Ihre Zeit. Mit besten Grüßen,

####

Ich habe es mir gerade angesehen: Excelversion 2016.

Ich erstelle eine intelligente Tabelle, ziehe sie in PQ, klappt; ich bearbeite, ich tue, ich mache, M-Code ist vorhanden – klappt.

Ich lade sie als Verbindung. Klappt.

Aber sobald ich die Daten als Tabelle „sehen“ / „haben“ möchte – knallt es:

Kennt jemand diesen Fehler?

Initialisierung der Datenquelle schlug fehl.
Überprüfen Sie den Datenbankserver oder kontaktieren Sie Ihren Datenbankadministrator. Vergewissern Sie sich, dass die externe Datenbank verfügbar ist, und wiederholen Sie den Vorgang. Sofern diese Nachricht wieder erscheint, erstellen Sie eine neue Datenbankquelle um mit der Datenbank Verbindung aufzunehmen.

Wo liegt Maßen? Mein Arzt sagt, ich solle dort trinken.

Irgendwie doof. Ich habe eine Tabelle, in der sich mehrere Kommentare (Notizen) befinden.

Nun lautet die Anweisung, dass ich diese Kommentare in eine eigene Spalte schreiben soll. Stellt man im Dialog „Seite einrichten“ ein, dass Kommentare am Ende der Tabelle erscheinen, kann man das im Drucken-Dialog sehen:

Au, prima, denke ich – und speichere die Datei als PDF. Was passiert?

Die Kommentare sind NICHT im PDF!

Anders jedoch, wenn ich einen PDF-Drucker installiert habe – dann kann ich die Datei drucken und Kommentare erscheinen auf dem letzten Blatt.

Warum mache ich das? Nun – das PDF kann in Word geöffnet werden; die Kommentare entnommen und weiter verarbeitet werden.

Und was mache ich, wenn ich keinen PDF-Drucker habe?

Die Antwort: ich kann die Datei mit der Endung ZIP umbenennen, entzippen und die Datei comments1.xml aus dem Ordner xl öffnen. Dort finden sich auch die Kommentartexte, die ich leicht entnehmen kann:

Anders als ihre Artgenossen die Seidenspinner werden die Zahnseidenspinner in kleinen Plastikgefäßen gehalten, wo sie zirka 50 Meter Zahnseide produzieren und dann sterben.

Eine Weile habe ich gebraucht, bis ich es verstanden habe. Excel behauptet, dass auf diesem Tabellenblatt eine Verknüpfung zu einer anderen Datei liege.

„Da liegt nix“, denke ich: auf dem Blatt befindet sich nur ein Button, der ein Makro aufruft:

Nach einer Weile dämmert es mir. Ich kopiere regelmäßig den Bereich von anderen Dateien hier auf dieses Blatt. Und richtig: damit auch die Schaltflächen. Und mit ihnen die Verknüpfungen auf andere Dateien. *gggrrrr*

Mama, was macht der Storch, nachdem er die Kinder abgeliefert hat? – Liegt auf der Couch und schaut Bundesliga.

Hallo René,

ich komme heute mit einer Frage auf Dich zu.

In einem „unserer“ Excel-Stammtische hast Du uns ein Besispiel gezeigt, wie Du durch ein Klick auf ein Bild „Inhalte aktivieren“ bestätigen kannst (d.h. der Benutzer muss nicht oben auf den orangefarbende Balken klicken , um die Makros der Arbeitsmappe zu aktivieren…).

Leider musste ich feststellen, dass meine Kollegen gerne die Aufforderung „Inhalte aktivieren“ übersehen und so meine Arbeitsmappen mit Makros nicht aktiviert werden. Um das ersichtlich zu machen bzw. zu erzwingen, würde ich gerne Deine Lösung einbauen.

Kannst Du mir dazu bitte Dein Beispiel zukommen lassen?

Das wäre großartig!

Vielen Dank & Grüße

Sebastian

so!

Ich habe ein Bild auf das Blatt gelegt, das ich beim Öffnen per Makro unsichtbar mache.

Werden die Makros nicht aktiviert, bleibt das Bild sichtbar.

Die Erklärung: Manchmal beschweren sich Anwender, dass bestimmte Befehle deaktiviert sind. Es wäre praktisch ein Makro zu haben, das meldet, dass die Makros nicht aktiviert wurden. Dies geht natürlich nicht. Um sicherzustellen, dass die Makros aktiviert wurden, kann in ein Projekt ein Bild eingefügt werden.

Werden die Makros nun aktiviert, wird das Bild gelöscht.

Man kann Shapes einen Namen geben oder mit einer Schleife alle Shapes / Bilder durchlaufen und entweder löschen oder Unsichtbar machen (Visible = False)

Das Bild wird beim Schließen der Arbeitsmappe wieder eingeblendet:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

On Error Resume Next

ThisWorkbook.Worksheets(„BCM“).Shapes(„Hinweis“).Visible = _

   msoTrue

Hallo René,

super! Klasse! 

Ich danke Dir!

Ich war von Deiner Lösung damals so begeistert, dass die hängen geblieben ist.

Da ich allerdings nicht mit (externen) Kunden arbeite, hatte ich keinen Anwendungsfall – bis ich letzte Woche erfahren durfte, dass meine Kollegen gerne den Balken übersehen und so nicht meine Datei funktioniert.

Ich werde Deine Lösung am Montag einbauen- das Bild wird selbstverständlich geändert.

Tausend Dank nochmal und bis bald.

Liebe Grüße  Sebastian 

Gute Eltern nehmen ihre Kinder mit in den Zoo. Sehr gute Eltern nehmen sie danach wieder mit nach Hause

Letzte Woche hat Christian Gröblacher auf unserem Excelstammtisch ein Referat über Sharepoint-Listen, Power Apps, Power Automate, Power Query und Power Pivot gezeigt. Am Ende kamen Exceldaten raus. Sehr beeindruckend. Das soll an dieser Stelle nicht wiederholt werden. Sondern die letzte Folie seiner Präsentation, die mich zum Schmunzeln brachte:

Vor allem das gequetschte Excel lässt Interpretationen offen: haben wir es im Griff? Müssen wir das grüne Teilchen manchmal würgen? Kann man es richtig handeln? Schaut es wirklich so blöde aus der Wäsche? Oder ist es einfach unser aller Begleiter – manchmal zur Freude, manchmal zum Leid?

Danke Christian, dass ich die Folie hier zeigen darf. Und hier noch einmal Excel – gaaaaaaaaanz groß!

Wenn ein Fahrrad gestohlen wird, hat keiner etwas gesehen. Aber wehe, jemand stellt die gelben Säcke zwei Tage zu früh auf die Straße – dann ist hier Party!

Ich erstelle für einen Kunden in Excel mit VBA ein mächtiges Eingabeformular. Schnell sind wir uns einig darüber, der der Anwender und die Anwenderin nicht mit dem Befehl Suchen-Ersetzen Texte auf einem Tabellenblatt austauschen sollen. Also nehmen wir in diesem Formular dieses Symbol (genau: die ganze Gruppe) aus dem Menüband:

Dazu sind ein paar Zeilen in der XML-Datei nötig, in der das Menüband beschrieben wird:

Der Code:

<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>

      <tab idMso="TabHome">
        <group idMso="GroupEditingExcel" visible="false" >
        </group>
      </tab>

Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:

Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:

Private Sub Workbook_Open()
    On Error Resume Next
    Application.OnKey "^f", "BitteNicht"  ' suchen
    Application.OnKey "^h", "BitteNicht"  ' ersetzen
End Sub

Beim Öffnen der Datei werden diese beide Tastenkombinationen „verbogen“, indem das Makro „BitteNicht“ aufgerufen wird. Es erscheint ein Meldungsfenster. Diese Prozedur wird auch beim Aktivieren der Mappe gestartet:

Private Sub Workbook_Activate()

Schnell merke ich, dass das Makro nach Schließen der Datei (oder auch Wechseln in eine andere Datei) noch aktiv ist. Also: raus damit:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnKey "^f", ""  ' suchen
    Application.OnKey "^h", ""  ' ersetzen
End Sub

Ebenso beim Ereignis Workbook_Deactivate.

Und was passiert? Richtig – wenn ich jetzt [Strg] + [F] drücke, passiert: NICHTS. Warum? Genau – ich muss natürlich schreiben:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.OnKey "^f"  ' suchen
    Application.OnKey "^h"  ' ersetzen
End Sub

DANN ist das Ereignis wiederhergestellt!

Das Leben ist zu kurz, um Deutsch zu lernen

Hallo Herr Martin,

Ich weiß nicht, ob Sie mir weiterhelfen können. Ich untersuche englische Dramentexte bezüglich der Wortlänge in der Anzahl der Zeichen.

Jetzt habe ich 100 Spalten. Jede Zeile enthält in jeder Spalte ein Wort eines fortlaufenden Textes. In den Spalten 101 – 110 möchte ich gern die Anzahl der Worte festhalten, die ein, zwei, drei, etc. 

Buchstaben lang sind. Spalte 111 soll für jede Zeile den Wert größer zehn enthalten.

Mit meinen Formelversuchen bin ich kläglich gescheitert, auch wenn Ihre Anmerkungen in der Formelsammlung tröstlich und erheiternd waren. 

Auch wenn Sie keinen Tipp haben sollten, herzlichen Dank für Ihre Mühe.

Hallo Herr I.,

die Lösung heißt SUMMENPRODUKT.

Sie kann als Matrixfunktion LÄNGE verarbeiten.

Werfen Sie mal einen Blick auf das Beispiel – Sie müssen natürlich Zeilen und Spalten vertauschen und einige Spalten einfügen … aber ich glaube das ist die Lösung, die Sie haben möchten. (ich zähle hier die Wörter mit zwei, drei, vier, …) Buchstaben aus Spalte A, B, C, …

Liebe Grüße

Rene Martin

Lieber Herr Martin,

Danke für die schnelle und perfekte Lösung. Der Hintergrund ist, dass sogenannte Marlowianer die Identität Marlowes mit Shakespeare behaupten und dazu die häufigste Wortlänge von 4 Buchstaben ins Feld führen, die bei beiden identisch ist. Tatsächlich ist das aber eine Eigenschaft des englischen Sprachsystems, nicht aber Autorenidentität.

Liebe Grüße

Hallo Herr I.,

ach – DIE Diskussion. Jo, kenn ich; und die hundert anderen Antworten auf Frage, wer Shakespeare wirklich war …

Sollte man wirklich solche positivistischen Ansätze, die im 19. Jahrhundert verwendet wurden, ins Feld ziehen? *hum* *hum*

Trotzdem: viel Spaß mit Willi, Chrisi & Excel

wünscht Rene Martin

Entweder ist gerade ein grünes Zebra in meinen Kleiderschrank gekrochen und singt gerade die Nationalhymne oder das waren keine Aspirin.

Access kann auch nerven! Ich erstelle gerade eine Datenbank für einen Kunden. Ich schreibe mehrere Hundert Daten per VBA in eine Tabelle. Sehe nach, ob die Daten alle „angekommen“ sind:

Up – nein!? – Die Warengruppen sind nicht da!? Der Filter zeigt nichts an!

Doch: aus irgendeinem Grund werden in dieser Spalte einfach keine Daten zum Filtern angeboten, obwohl sie weiter unten in der Tabelle zu finden sind:

Im ersten Moment dachte ich, meine VBA-Routine wäre falsch …

Männer sind alle gleich. Sie sehen nur unterschiedlich aus, damit man sie unterscheiden kann.

Hallo Herr Martin

Es kommt wieder mal eine Frage aus der Schweiz.

Für den damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.

Mein Vorgehen:

  • Entpacken der xltm.
  • Ich ergänze die customui.xml
  • Zippen aller Daten, umbenennen zu xltm .

Ergebnis: Datei kann nicht mehr geöffnet werden. Ich habe gefühlt alles genau gleich gemacht wie beim ersten Mal. Haben Sie dazu eine Idee?

Besten Dank für einen Tipp dazu 🙂

Freundliche Grüsse – Kind regards

Hallo Herr S.,

es könnte möglicherweise am Zippen liegen.

Achten Sie darauf, dass Sie „auf der richtigen Ebene“ sind und ALLE Ordner und die XML-Datei markiert haben, also HIER:

(wahrscheinlich haben Sie mehr Ordner)

Versuchen Sie es noch einmal, bitte!

schöne Grüße aus München

Rene Martin

Ich hatte auch mal Körper und Seele im Einklang. Aber da lag ich noch im Bett.

Kennt ihr das? Ich bislang nicht. Ich erhalte eine Exceldatei, die auf dem Macintosh erstellt wurde. Ich versuche die Datei als PDF zu speichern im Format PDF/A:

Excel weigert sich mit der Bemerkung:

Das Dokument wurde nicht gespeichert. Das Dokument ist möglicherweise geöffnet, oder beim Speichern ist ein Fehler aufgetreten.

Ich kreise den Fehler ein und finde ihn schließlich in einer Zelle. Darin befinden sich Zeilenumbrüche ([ALT] + [Enter]). Die mag mein PDF/A für PC nicht …

Die Ehe ist ein lustiges Wort für die Übernahme eines erwachsenen männlichen Kindes, dessen Eltern nicht mehr in der Lage waren, die Situation zu bewältigen.

Hallo Herr Martin,

ich wieß nicht mehr genau, ob ich Sie einmal auf folgende Thematik angesprochen hatte.

Es ging um die Umformatierung von Postleitzahlen aus einer Spalte in Aneinanderreihung

in einer Zelle mit Komma/-Lehrzeichen-Trennung (siehe Anhang „screenshot“):

Diese Schreibweise (nebeneinander) ist die auf unserem content-management-system.

Wenn ich nun größere Zahlenmengen erhalte, wird es händisch sehr aufwendig.

Könnten Sie mir bei diesem Prozedere wieder behilflich sein?

Vielen Dank schon vorab.

Mit besten Grüße

Hallo Herr S.,

welche Excel-Version haben Sie denn? Haben Sie die Funktion TEXTVERKETTEN? DAMIT klappt es ganz einfach:

=TEXTVERKETTEN(„, „;WAHR;E3:E14)

E3:E14 ist natürlich der Bereich der Postleitzahlen. Dann kopiere ich den Bereich an eine andere Stelle und füge ihn als Werte ein – schon habe ich die Liste.

Klappt das?

Liebe Grüße

René Martin

Hallo Herr Martin,

vielen dank für die schnelle Reaktion. Ich habe die 2010’er Version Version 14.7263.5000 (32 Bit)

Direkt das Wort TEXTVERKETTEN ist in den Formeln nicht enthalten.

Gibt es unter dieser alten Version vielleicht trotzdem eine Lösung? 

Beste Grüße nach München C. S.

Hallo Herr S.,

oder so:

wiederholen Sie in der ersten Zelle den Wert, bspw.: =E16

schreiben Sie darunter: =F16&“, „&E17

ziehen Sie die untere Formel runter!

das Ergebnis der letzten Zelle können Sie kopieren und an andere Stelle als Wert einfügen

Liebe Grüße

Rene Martin

Einfach genial,

Sie machen einem Freude! ! !

Einfach soo logisch, dass ich mir immer wieder die Frage stelle,

warum komme ich nicht einmal selbst auf solch logische Lösungen.

Ich hoffe, dass ich ihre Geduld und Zeit nicht zu sehr in Anspruch nehme. 

Ganz herzlichen Dank. Bleiben Sie gesund und zuversichtlich

C. S.

Bereits dieser Artikel enthält den Tagesbedarf an A, B, C, D, E und K und vielen weiteren, wichtigen Buchstaben.

Gestern habe ich (mal wieder) eine Knobelaufgabe in Excel gepostet. Die Aufgabe lautet:

„Lust auf Knobeln? Mit Excel? Mit Excel-Diagrammen?
In einer Liste stehen Werte. Auf diesen Werten wird ein Diagramm – ein Kreisdiagramm oder Liniendiagramm aufgesetzt. Dumm nur, dass einer oder einige Werte 0 sind. Dumm, dass die Datenbeschriftung angezeigt wird, aber kein Wert: im Kreisdiagramm kein Tortenstück; im Liniendiagramm eine Lücke. Aber natürlich automatisiert – also: wenn sich die Werte ändern, sollen die entsprechenden Werte verwendet werden. „

Die Antwort lautet natürlich, dass man aus 0 ein #NV machen muss – beispielsweise mit einer WENN-Funktion. Es genügt #NV bei den Werten zu verwenden. Das ist die Grundeinstellung der Diagramme:

Ich erhalte die Frage:

„bei meiner Excel-Version sieht das noch anders aus:“

Nachgeschaut. Auf der Seite

https://support.microsoft.com/de-de/office/anzeigen-von-leeren-zellen-nullwerten-n-v-und-ausgeblendeten-arbeitsblattdaten-in-einem-diagramm-a1ee6f0c-192f-4248-abeb-9ca49cb92274

steht:

Hinweise: 
Dieses Feature ist nur verfügbar, wenn Sie über ein Microsoft 365-Abonnement verfügen und derzeit nur für Insider verfügbar ist. Wenn Sie Microsoft 365-Abonnent sind, vergewissern Sie sich, dass Sie über die neueste Office-Version verfügen.

Ah – man (ich) muss auch das Kleingedruckte lesen!

Ich bin froh, dass ich mein Essen nicht selbst jagen muss. Ich wüsste gar nicht, wo Pommes leben!

Christian ist irritiert. Zwar übertragen und übernehmen die Arrayfunktionen (SORTIEREN, FILTERN, SEQUENZ, …) nicht die Formatierungen:

Aber wenn man mit dem Gleichheitszeichen auf einen Bereich zugreift, wird in der ersten Zelle das Zahlenformat übernommen, in den übrigen nicht:

Erklärbar – dennoch: irritierend.

Wisst ihr, was einen guten Laptop ausmacht? Eine halbe Tasse Kaffee!

Hallo Rene,

da du ja nervige Dinge sammelst, hast du vielleicht auch eine Antwort auf mein Phänomen, was mich einige Nerven gekostet hat,

bevor ich auf eine Lösung gestoßen bin. Anbei ein Screenshot meines Diagramms ( habe mich nicht getraut eine Datei zu versenden) was ich eigentlich mal ganz schnell erstellen wollte,

bis zu dem Zeitpunkt, wo ich den aus einem Fehlerindikator dargestellten dynam. Pfeil ein wenig zurechtrücken (rechts- links verschieben) wollte.

So geht es nicht!

Es hat lange gedauert den Wert im Array dann mit einem Dezimalpunkt einzugeben,….weil die bearbeitete Datenreihe immer ein Komma anzeigt. Ich denke, dass es nicht nur mir so ergeht,

oder irre ich mich ?,  wie Sam Hawkens es einmal bei Winnetou so schön sagte.

So klappt es!

Mit lieben Grüßen in den Süden.

Hallo Jürgen,

Hübsch – DAS kannte ich noch nicht. Du hast recht: HIER wird Komma als Trennzeichen und Punkt als Dezimaltrennzeichen interpretiert. Wahrscheinlich hat an dieser Stelle ein Programmierer geschlafen …

Liebe Grüße und: danke!

Rene

Schade, das es so teuer ist, Leute auf den Mond zu schießen. Ich hätte da ein paar.

Hallo René,

ich habe ein für mich unerklärliches Excel Phänomen in der Version 365.

In der Zelle C1 habe ich berechnet: 6*7=42 (s. Anlage).

Dieses Ergebnis habe ich zum Datum 18.07.2017 addiert und erhalte das Datum 24.01.6837.

Addiere ich 42 als Zahlenwert zum Datum 18.07.2017, erhalte ich das richtige Ergebnis 29.08.2017.

Hast Du eine Erklärung?

Liebe Grüße
  Traudl

Hi Traudl,

du MULTIPLIZIERST das Datum (18.07.2017 = 42.934) mit 42

42.934 x 42 = 1.803.228 = 24.1.6837

Stimmt schon!

Rene

Oh je René, danke. 

Dass ich multipliziert habe, ist mir gar nicht aufgefallen. Zudem habe ich die Berechnung mehrfach wiederholt.

Wahrscheinlich Corona Blackout.

Sorry, dass ich Dich mit so einer Lapalie belästigt habe.

Liebe Grüsse 

Traudl 

Es ist total spannend älter zu werden. Man lernt jeden Tag neue Knochen kennen.

Hi René,

ich stehe mal wieder auf dem Schlauch:

Kannst Du mir sagen, wie ich, wenn in einem Tabellenblatt einen Wert aus einem anderen Reiter angegeben ist, in der daneben liegenden Spalte automatisch den Wert aus einer anderen Spalte der Tabelle in dem anderen Reiter ausgebe?

In A2 habe ich per Dropdown mit INDIREKT „<10%“ aus tab_AntwortKategorie[Kategorie].

In B2 möchte ich automatisch den dazugehörigen Wert aus tab_AntwortKategorie[Wert].

Was muss ich in B2 eingeben?

Ohne INDIREKT wüsste ich es, aber wie gesagt – ich stehe einfach auf dem Schlauch.

Momentan „fuhrwerke ich wild (und falsch) rum“, z.B.
=WVERWEIS($AE4;tab_AntwortKategorie[Kategorie];[Wert])

Bye

Michael

Hallo Michael,

wenn du den Wert DANEBEN haben möchtest, musst du den SVERWEIS verwenden, nicht den WVERWEIS. „S“ steht für „senkrecht“, heißt: die Liste ist von oben nach unten aufgebaut.

Schau mal in mein Beispiel:

Hilft das?

Liebe Grüße

Rene

Hi René

aaaaah (über eigene Dummheit)!

Ja, danke.

Bye

Michael

Alexa, wie verhindere ich, dass meine Daten ins Internet kommen?

Hallo, ich habe auch so ein Problem in meinem Kalender zur Arbeitszeitberechnung mit den Rauten. Excel gibt mir aber den Hinweis, das entweder die Zelle zu klein ist, oder ein Negativ Wert darin enthalten ist. Bei mir scheint es wohl der Negativ Wert zu sein da eine Formel in der Zelle enthalten ist bei der ich zb. 8 von 9 in meinem Fall Stunden abziehen möchte. Da ich die Zeiten über Dropdown Listen eingebe, habe Rauten in den Zellen solange ich noch keine Werte eingegeben habe. Das sieht natürlich nicht so gut aus. Nun meine Frage. Kann man diese Rauten auch einfach nur ausblenden ohne das die darin enthaltenen Formeln auch ausgeblendet sind? Ich hoffe das ich mein Problem verständlich erklären konnte.

Gruß Uli.

Hallo Uli?

Ich würde um die Formel (wie sieht sie aus?) beispielsweise
=C2-B2
eine WENN-Funktion bauen:

=WENN(C2-B2<0;““;C2-B2)

Hilft das?

Liebe Grüße

René Martin

Hallo Herr Dr.Martin,

Danke Ihnen nochmal für ihre Hilfe, es hat geklappt mit der Formel.

War für Sie bestimmt eine Logische und einfache Sache, aber für mich als Blutiger Anfänger ein Riesen Problem. Würde mich freuen wenn ich mal wieder so ein Problem hätte, mich an Sie wenden dürfte.

Mit freundlichen Grüßen U.

Klar, Uli,

einfach schreiben! Ich helfe gerne

Liebe Grüße aus dem verregneten München

Rene Martin

PS: ich mag den Begriff „logisch“ nicht. Es läuft eine gewisse erlernbare Mechanik ab. Jeder von uns hat ein bestimmtes Wissen und erkennt Lösungen „intuitiv“ und kann sie sicherlich auch beschreiben, warum dieser oder jeder Weg der richtige (oder der geeignete) ist. Also: Kopf hoch, dranbleiben, schmunzeln – ich halte Excel für wichtig – nicht, weil ich häufig damit arbeite, sondern weil sehr, sehr viele Menschen häufig damit arbeiten.

Seit ich gesehen habe, wieviel schlanker ein Bär nach seinem Winterschlaf ist, kommt mir das Konzept noch viel attraktiver vor.

Hallo Rene,

Ich hätte da nochmal eine Frage zu den Matrixfunktionen. Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.

Gruß

Hannes

Hallo Hannes,

du musst den Namen im INDIREKT in einen Bezug umwandeln. Meinst du das?

Liebe Grüße :: Rene

Hallo Rene,

ja genau, da sind einige Haken drin.

Die Liste in der Datenüberprüfung übernimmt offensichtlich nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine Rückgabewerte aus Funktionen.

Aber wenn ich innerhalb einer „formatierten“ Tabelle einen benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.

Wenn ich über die Sequenz-Funktion Werte ermittle und die in einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben, innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.

Die Problematik ist wahrscheinlich, dass die Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher auch die Bezugsherstellung mit der Indirekt-Funktion.

Keine Ahnung, ob man das irgendwie austricksen kann.

Gruß

Hannes

PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte

Ich habe gerade „ach-da-brauchst-du-nichts-draufschreiben-man-sieht-ja-was-drin-ist“ aus der Gefriertruhe geholt und bin gespannt, was ich heute koche.

Hi Rene,

sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:

Und dann passiert:

Hallo Johannes,

eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also:
=[@Umsatz]*19%
oder:
=SUMME(Tabelle1[Umsatz])
analog:
=TEILERGEBNIS(109;[Umsatz])

DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.

Moin Rene,

Danke Dir für die ergänzenden Infos 🙂   Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂

My wife asked me why I was talking so softly in the house, I told her I was afraid that Mark Zuckerberg would hear me! She laughed, I laughed, Alexa laughed, Siri laughed and Cortana laughed (James Franco)

Merkwürdig. Ich importiere per VBA Daten aus anderen Tabellen in eine Arbeitsmappe. Die Daten der Importtabelle sind als intelligente Tabelle gespeichert, in der aktuellen Tabelle liegt auch eine intelligente Tabelle. Damit sich diese beiden Tabellen nicht überlagern, ist es wohl das Beste die Tabelle zu löschen. Aber wie heißt der Befehl „In Bereich konvertieren“?

Nun – der Makrorekorder hilft:

Sub ZurueckZuDummerTabelle()
'
' ZurueckZuDummerTabelle Makro
'

'
    
End Sub

Nein – der Makrorekorder hilft nicht! Er zeichnet nur den Befehl auf: lösche das Tabellenformat. Aber nicht: lösche die Tabelle. Also muss ich doch auf die Suche gehen. Ich werde schnell fündig: die Methode heißt Unlist. Damit klappt es:

                For j = 1 To xlBlatt.ListObjects.Count
                    xlBlatt.ListObjects(j).TableStyle = ""
                    ' -- lösche die Formtierung
                    xlBlatt.ListObjects(j).Unlist
                    ' -- in Bereich (zurück) konvertieren
                Next j

Warum nicht gleich?

Ich hab mal nachgedacht. Das Sportlichste bei mir zu Hause ist der Läufer im Flur.

Tabellenblätter in Excel in Excel haben nicht nur einen (sichtbaren) Namen, der vom Anwender oder von der Anwenderin geändert werden kann. Im VBA-Editor gibt es auch noch einen Name (Codename), der nur dort und nur per Hand geändert werden kann. Ich kann nun per Programmierung leicht überprüfen, ob alle Tabellen, die ich benötige, noch vorhanden sind. Ich habe eine Funktion GibtEsTabellenblatt geschrieben, die überprüft, ob die aktuelle Datei ein Tabellenblatt mit einem solchen Codenamen hat. Ich möchte die Arbeitsmappe nicht schützen, weil der Anwender oder die Anwenderin neue Blätter hinzufügen, löschen, umbenennen darf. Allerdings: meine Blätter dürfen nicht gelöscht werden. Ich sehe nach:

Jedes der Tabellenblätter hat ein Ereignis BeforeDelete:

Dieses Ereignis hat allerdings kein Abbruchkriterium (Cancel), mit dem man das Löschen verhindern könnte.

Der zweite Blick fällt auf das Objekt Workbook. Gibt es dort ein Abbruchparameter?

Das Ereignis dort heißt SheetBeforeDelete und hat auch keinen solchen Parameter:

Allerdings einen Parameter Sh mit dem ich gezielt überprüfen kann, welches Blatt nicht gelöscht werden darf (und die Liste schnell erweitern kann:

Select Case Sh.CodeName
    Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
        MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical

End Select

Problem: Die Meldung wird angezeigt und DANN das Blatt gelöscht. Doof!

Nächster Versuch: Und wenn ich DANACH die Arbeitsmappe schütze?

Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    On Error Resume Next
    Select Case Sh.CodeName
        Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
            MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
            ThisWorkbook.Protect
    End Select
End Sub

Klappt! Hier die drei Meldungen, die erscheinen:

Allerdings: DANN ist die Arbeitsmappe geschützt. Was ich ja eigentlich nicht wollte. Also flux den Arbeitsmappenschutz wider aufheben. Wo? Am besten beim Wechseln auf ein anderes Arbeitsblatt:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    On Error Resume Next
    ThisWorkbook.Unprotect
End Sub

Wir haben gestern einen Porno geschaut. Da hat der Mann die Frau mit Honig beträufelt und abgeleckt. Wir wollten das heute nachmachen, hatten aber keinen Honig. Um es kurz zu machen … Mit grober Leberwurst ist es nicht das Gleiche. Und wenn dann noch der Hund im Schlafzimmer liegt …

Im VBA-Editor kann man den (internen) Namen eines Tabellenblattes ändern. So kann man auf dieses Blatt über diesen (Code-)Namen zugreifen, egal an welche Position es der Anwender oder die Anwenderin schiebt; egal ob es in Excel umbenannt wird. Allerdings kann es der Anwender oder die Anwenderin löschen. Kein Problem – ich baue einen Button ein, der ein neues Blatt erzeugt:

Dim xlBlattNeu As Worksheet
Set xlBlattNeu = ThisWorkbook.Worksheets.Add
xlBlattNeu.CodeName = "tblITAnwendungen"

*gggrrrrr* CodeName ist schreibgeschützt.

Zuweisung an schreibgeschützte Eigenschaft nicht möglich.

Kann also nicht in VBA umbenannt werden. Ich kann nur überprüfen, ob das Blatt vorhanden ist. Aber nicht (den Namen) erzeugen. Schade! Ärgerlich! Aber verständlich.

Ich hasse Kettenbriefe. Wenn du sie auch so wie ich hasst, leite diese Nachricht an zehn Freunde weiter.

Wenn man per VBA auf ein Blatt zugreifen möchte, gibt es zwei Varianten. Entweder über den Namen:

Also beispielsweise

ThisWorkbook.Worksheets("Unternehmen")

oder

ActiveWorkbook.Worksheets("Standorte")

Problem: Wird die Arbeitsmappe nicht geschützt, kann der Anwender oder die Anwenderin den Namen ändern!

Die zweite Möglichkeit lautet:

ThisWorkbook.Worksheets(1)
ActiveWorkbook.Worksheets(2)

Problem: der Anwender oder die Anwenderin kann das Blatt verschieben, wenn die Arbeitsmappe nicht geschützt ist. Außerdem ist die Zählung bei ausgeblendeten Blättern schwierig.

Es gibt noch eine dritte Variante. Im VBA-Editor kann man in den Eigenschaften einen Namen festlegen:

Über diesen Namen kann man auf das Blatt zugreifen, beispielsweise:

tbl_Uebungsplanung.Range("F1").Value = _
   tbl_UnternehmenCode.Range("A1").Value ' -- Unternehmen

Erstaunlicherweise versagt:

MsgBox ThisWorkbook.tbl_UnternehmenCode.Range("A1").Value

Auch der Zugriff auf ein Blatt über seinen Codename in einer anderen Datei geht nicht!

    Dim xlDatei As Workbook
    Set xlDatei = Application.Workbooks.Open("D:\Übungspanung.xlsx")
    MsgBox xlDatei.tbl_UnternehmenCode.Range("A1").Value

Schade! Man muss mit einer Schleife über alle Blätter iterieren und abfragen, ob der Codename = „tbl_UnternehmenCode“. Und darauf einen Verweis setzen.

Schnell wieder die Beine rasieren, bevor man zu den alten Nordmann-Tannen am Straßenrand gestellt wird.

Ich erstelle gerade ein größeres VBA-Projekt für einen Kunden. Und bin mal wieder verblüfft! Die größte Verblüffung hatte ich letzte Woche. Man erstelle in Excel eine XLSM-Datei mit Makros. Ein Makro wird über eine Schaltfläche (oder ein Bild, ein SmartArt, eine Form, ein Diagramm) aufgerufen:

Ich kopiere das Tabellenblatt in eine andere Datei

und speichere die Datei OHNE Makros als XLSX! Ich schließe alles, öffne die XLSX-Datei und werde gefragt, ob ich die Makros aktivieren möchte:

Okay. Ich aktiviere die Datei. Ein Klick auf die Schaltfläche und werde darauf hingewiesen:
Microsoft Office hat ein potenzielles Sicherheitsrisiko erkannt.

Was passiert? Die andere Datei wird im Hintergrund geöffnet, das Makro wird ausgeführt! Denn: hinter der Schaltfläche steht noch immer der Dateiname und Makroname.

Fazit: Mein Glaubenssatz: XLSX-Dateien sind sicher, denn sie können keine Makros enthalten, ist erschüttert! Zwar enthalten XLSX-Datei weiterhin keine Makros, können aber Makros aus anderen Dateien aufrufen!

Siri – warum bin ich Single? – *Siri öffnet die Frontkamera*

Ich suche gerade nach einer Lösung: Bei mir ist plötzlich der Zoom in der Seitenansicht eingeschränkt. Früher sah ich eine ganze Seite bei Druckvorschau. Jetzt ist der Zoomfaktor größer und ich muss zum unteren Seitenrand skrollen.

Hat jemand eine Idee, wie man das wieder ändert? Hatte das gleiche Problem vor Jahren schon mal, aber Google findet leider diese Seite nicht mehr….

Hallo Peter,

ich schreibe mal direkt.

Meinst du so etwas?

Wenn die Skalierung Anpassen auf 1 x 1 Seite eingestellt ist, macht Excel so etwas. Zurück auf Ansicht / Normal – beim zweiten Mal ist der Spuk verschwunden.

Liebe Grüße

René

Hallo Martin,

wow, mit soo einer schnellen Reaktion habe ich nicht gerechnet. Vielen Dank!

Ich habe das „Problem“ anhand von zwei Screenshots verdeutlicht: auf dem Rechner meiner Frau wird eine Seite A4 in der Druckansicht komplett dargestellt, bei mir auf dem Rechner (seit kurzem) nicht mehr, d.h. ich muss um das Seitenende zu sehen scrollen.

Der „Schalter“ in der rechten unteren Ecke zoomt bei mir nicht mehr auf Seitengröße – wie gesagt, ich hatte das Problem vor Jahren schon mal, aber finde die Lösung nicht mehr…

Liebe Grüße

Peter

Neuer Trend bei Deutschlehrern: Kommasaufen.

Sehr geehrter Herr Martin,

ich habe eben begonnen, Ihre Übungen zu den statistischen Funktionen durchzuführen.

Bei dem Thema Häufigkeit sehe ich eine Differenz zwischen dem, was Sie zeigen und dem, was ich gemacht habe, obwohl die Zahlen, die Sie benutzen, die Zahlen sind, die ich auch genommen habe (ich habe diese Zahlen von Ihrem Beispiel abgetippt).

Ich verstehe nicht den Unterschied, obwohl ich wie Sie die Funktion Häufigkeit verwendet habe.

Das betrifft auch das Ausrechnen der Häufigkeit als Matrix. Auch hier unterscheiden sich die Zahlen.

Zur Überprüfung schicke ich Ihnen meine Datei mit.

Hallo Herr F.,

HÄUFIGKEIT liegt in zwei Varianten vor: als Matrixfunktion und als „normale“. Wenn Sie im „neuen“ Excel HÄUFIGKEIT verwenden, wird es als Arrayfunktion interpretiert: wie viele Daten sind bis zu Wert x, wie viele darüber:

Bei den „älteren“ Excel-Versionen ist dies nicht nötig. So kann ich die Funktion runterziehen. Dazu muss der Datenbereich fixiert werden. Die Daten werden also kumuliert:

Da HÄUFGKEIT matrixfähig ist, muss ich (in denen neueren Excel-Versionen) nicht mehr mit Umschalt + Strg + Enter beenden. Dann werden die einzelnen Bereichen berechnet:

Laut ADAC sind 11 Winterreifen gut. Nach meiner Erfahrung sage ich: 4 genügen!

Guten Morgen,

also, es geht um die Funktion „Text in Spalten“. War eigentlich eine Frage eines Kollegen, die ich selbst nicht beantworten konnte.

Ich habe eine CSV-Datei, in der die Werte auch wirklich durch Kommas getrennt sind:

Jetzt ist es so, dass auch innerhalb der Texte, die zu Zellinhalten werden sollen, Kommas drin sind, wie z.B. in Zeilen 1 und 2. Wenn ich jetzt „Text in Spalten“ mit der Option „Getrennt“ auswähle, dann packt Excel natürlich den Teil rechts vom Komma in eine neue Zelle:

Gibt’s ne Möglichkeit, das mit „Text in Spalten“ sauber zu trennen? Mit PowerQuery krieg ich es hin, also saubere Trennung + korrektes Anzeigen von Umlauten, ich frag mich nur, ob das „Text in Spalten“ nicht auch irgendwie kann.

Danke dir und viele Grüße, Dominic

####

Hallo Dominic,

ich denke die Frage hast du schon selbst beantwortet:

* PowerQuery

* VBA

* Formeln (uff!)

Wenn der Anwender Zeichen in seinen Texten verwendet, die als Trennzeichen vorgesehen sind; kann der Assistent Daten / Text in Spalten das nicht abfangen. Wie wir wissen: die Grenzen von „Text in Spalten“ wurden ja in PowerQuery erweitert – da geht mehr …

Liebe Grüße

Rene

PS: Man könnte die Blitzvorschau versuchen; ist aber mühsam und wahrscheinlich funktioniert auch das nicht.

Warum sind Blondinenwitze immer so kurz? – Damit auch Männer sie verstehen!

Lieber Herr Martin,

Excel nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.

Ich müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer anderen Zelle

Entsprechen. So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob

Ich den Inhalt der Zellen auch verkettet ausgeben kann.

Vielen Dank

Bevor ich eine Antwort gebe, Frau L.,

zwei Gegenfragen: sind die Daten sortiert (nach den Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht einfach“)

Liebe Grüße

Rene Martin

So ist es mir recht,

leider kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…

Eindeutig? Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich nicht aus…

Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden

Die erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet die Informationen die ich gerne zusammen verkettet hätte, also

Für 20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…

Beim Trennzeichen wäre ich emotional flexibel

Die erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.

Vielen Dank

Hallo Frau L.,

ich hoffe, Sie haben die Funktion TEXTVERKETTEN. DAMIT klappt es.

Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.

Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.

Die erste Formel lautet:

=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($I$1;
VERGLEICH(L2;$A$2:$A$27;0);0;
VERGLEICH(L2;$A$2:$A$27;1)-
VERGLEICH(L2;$A$2:$A$27;0)+1))

Die zweite:

=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($M$31;
VERGLEICH(O32;$L$32:$L$51;0);0;
VERGLEICH(O32;$L$32:$L$51;1)-
VERGLEICH(O32;$L$32:$L$51;0)+1))

Klappt das? Ist es das, was Sie möchten?

Immer wenn sich irgendwo ein Mensch an einem Papier in den Finger schneidet, kichert irgendwo ein Baum.

Ich erhalte eine Frage:

„Das Problem ist, daß sich Excel/Windows leider nicht wie beschrieben verhält:

In dem Feld müßte eigentlich 02.12.2020 stehen, es wird jedoch 02.12.1920 angezeigt, obwohl die Einstellung in der Systemsteuerung korrekt ist.

Das Problem tritt auch nur über die Formel auf. Wird das Datum manuell mit zweistelliger Jahreszahl angegeben, dann wird die Ergänzung der ersten zwei Ziffern korrekt vorgenommen …

Ich benötige die Formel zur Umsetzung eines Datumsfeldes aus einem Datenabruf im Format „JJMMTT“. Ich habe mir jetzt mit einer „wenn“-Funktion einen „Workaround“ gebastelt, aber es kann doch nicht sein, daß die Excel-Formel sich nicht an die Windows-Regeln hält …“

Und meine Antwort darauf:

„genau hinschauen! Excel verlangt bei der Funktion DATUM eine Jahreszahl zwischen 1900 und 9999:

So wie beispielsweise der Sinus die Angaben nicht in Grad, sondern im Bogenmaß haben will.

Monat verlangt beispielsweise einen Monat zwischen 1 und 12. Wenn ich die Monatszahl 24 eintrage, rechnet Excel Monat 12 + 12 weitere Monate. Ebenso beim Jahr.

Der Beginn liegt bei 1900. Wenn ich nun 20 eingebe, wird das Datum -1880 Jahre berechnet. Das kann Excel nicht. Eigentlich müsste ein Fehler die Folge sein: #ZAHL – ebenso wie bei

=DATUM(-2021;1;18)

Wahrscheinlich sind die ersten 1.900 Zahlen abgefangen und ins Jahr 1900 transformiert worden.

Eigentlich schon korrekt – gib Excel, was des Excels ist: wir leben im Jahre 2021 und nicht im Jahr 21!

Und: richtig: bei der Eingabe von Zahlen wird der eingegebene Wert immer interpretiert:

12-1-21 wird in Deutschland zum 12. Januar 2021; in den USA zum 01. Dezember 2021. Deshalb drücken wir auch [Enter] und überlassen dem Compiler, was er aus der Eingabe macht.

Liebe Grüße

Rene

Warum gibt es bei Tastaturen nicht auch so Krümelschalen wie bei Toastern?

Wenn ich in VBA für Excel programmiere, muss ich häufig ermitteln, ob ein Wert in einer Liste vorhanden ist. Die Funktion

Application.WorksheetFunction.CountIf([Spalte],[Wert])

also: ZÄHLENWENN, leistet, was ich möchte: ein Befehl (ohne Schleife) und ich habe die Information (Wert in der Spalte oder nicht vorhanden). Ebenso verwende ich häufig SUMMEWENN:

Application.WorksheetFunction.SumIf

oder – um die Zeilennummer zu ermitteln VERGLEICH:

Application.WorksheetFunction.Match([Wert],[Spalte],0)

So spare ich mir das Schreiben von Schleifen. Nun wollte ich die kumulierten Geldbeträge zu bestimmten Monaten wissen. In Excel lautet die Funktion

=SUMMENPRODUKT((MONAT(A:A)=1)*(B:B))

also: summiere die Werte der Spalte B, wenn eine Datumsangabe in der Spalte A ein Datum des ersten Monats (Januar) im Jahr enthält. Klappt wunderbar. Und in VBA? Dort versagt eine Zeile wie:

Application.WorksheetFunction.SumProduct(Month(ActiveSheet.Columns(1) = 1) * ActiveSheet.Columns(1))

Die Ursache ist schnell gefunden: Colums(1) = 1 kann nicht verarbeitet werden; auch nicht Month(Columns(1)); der Gleichheitsoperator in VBA ist nicht matrixfähig; „=“ kann nur identische Dinge vergleichen.

Schade – also doch eine Schleife …

Oma: Homofiss – iss datt wedder son Schwulengrupp? – Enkel: Oma: datt heet Homeoffice. Net Homo-fiss!

Angelika ruft erneut an: „Hallo Rene: SVERWEIS kann DOCH zwischen Groß- und Kleinschreibung unterscheiden. Schau mal, ich hab’s gefunden!“

Prinzipiell glaube ich andere Menschen NICHT. Ich schaue die Datei an, die sie mir geschickt hat:

„Schau“, sagt sie, „der Text arnstein steht einmal groß- und einmal kleingeschrieben in der Liste. Wenn ich die Liste nun sortiere, und den Parameter WAHR beim SVERWEIS verwende, findet Excel den unteren, großgeschriebenen Eintrag.“

Ich prüfe das und trage den Text in Kleinbuchstaben ein: „arnstein“.

Auch hier wird der untere gefunden. Ich stutze: „Angelika: deine Liste ist nicht sortiert. Probier mal aus: =CODE(„A“) ergibt 65, =CODE(„a“) liefert 97. Arnstein ist kleiner als arnstein. Du musst anders herum sortieren.

Wir probieren es aus:

und auch:

Beide Male wird der untere Text gefunden. Die folgende Vermutung liegt nahe:

SVERWEIS „läuft“ bei der Verwendung des Parameters WAHR in [Bereich_Verweis] in einer Liste so lange nach unten bis ein größerer Wert als der gesuchte gefunden wird. Dann „stoppt“ SVERWEIS und gibt den Wert der zuletzt gefundenen Zeile zurück. Dabei wird weder bei WAHR noch bei FALSCH zwischen Groß- und Kleinschreibung unterschieden (wie fast an kleiner Stelle in Excel).

Fazit: Wenn man Texte in Listen mit SVERWEIS suchen muss (was durchaus sein kann – Artikelnummern, Personalnummer, Länderkennzeichen, etc. die alphanumerisch aufgebaut sind), sollte man NIEMALS die Liste sortieren und mit dem Parameter WAHR (oder ohne diesen Parameter) in SVERWEIS arbeiten. Schließlich möchte man ein eindeutiges Ergebnis. WAHR ist nur bei numerischen Reihen sinnvoll, beispielsweise: Umsatzzahlen, Erlöse, Gewinne, Kosten, Kilometer … von Betrag x bis Betrag y. Oder Datum von d1 bis d2.

Ich glaube, Angelika war mit meiner Erklärung einverstanden. Ganz sicher bin ich mir nicht. Ich werde sie noch einmal anrufen.

Habe meiner Pflanze vorgeschlagen, sie nur noch einmal im Monat zu gießen. Sie ist darauf eingegangen.

Angelika fragt weiter: „Kennst du das Problem? Ich habe eine Liste in der Begriffe mehrmals in unterschiedlicher Schreibweise vorliegen – manchmal in Großbuchstaben, manchmal nur in Kleinschreibweise. SVERWEIS findet leider nur den ersten Eintrag – egal wie er geschrieben ist.“

Bevor ich antworten kann, beantwortet Angelika ihre Frage selbst: „Ich habe vor Kurzem einen Artikel von Martin gelesen; dem Tabellenexperten – er beschreibt dort das Problem mit der Lösung IDENTISCH. Diese Funktion unterscheidet Groß- und Kleinschreibung.“

https://www.tabellenexperte.de/wie-sverweis-nur-richtig/

„Ja“, pflichte ihr bei: „den Artikel habe ich auch gelesen. Ich habe es ausprobiert – ja, er hat recht. Und: seine Lösung funktioniert so!“

1 2 3 9