Author Archives: Medardus

Der Praktikant hat sich geschnitten und möchte ein Pflaster. Ich zeige ihm einen Rambo-Film und reiche ihm den Lötkolben.

Ich darf JavaScript unterrichten.

Ich erkläre, wie man eine Variable um den Wert 1 erhöht. Drei Möglichkeiten stellt diese Sprache zur Verfügung:

x = x + 1;
x +=1;
x++;

Wir machen eine Übung. Ein Teilnehmer sagt, dass sein Programm nicht richtig rechne. Seine Variable würde nicht erhöht werden. Ich schaue es mir an:

x = x++;

Richtig: zuerst geschieht die Übergabe, dann wird der Wert erhöht. Das heißt: die Variable verändert ihren Wert nicht. *ggrrrr*

Mein Mann positioniert den Rasensprenger und ruft: „Mach bitte das Wasser an; aber erst, wenn ich weg bin!“ — Ihr hättet es auch getan …

Hallo Rene,

nun, seit langem, wieder mal was Fachliches.

Ganz was Einfaches:

In meinen Programmpaket gibt es unheimlich viele Variable. Und wenn was Neues dazu kommt, kommen neue Variable hinzu.

Ich habe bisher noch keine einfache und schnelle Lösung – wie findet man im Programm „nicht benutzte Variable“ ?

So „Tabula rasa“ ist gesucht. Einfach all diese Variablen ungesehen ganz schnell löschen….

Liebe Grüße Wolfgang

Hallo Wolfgang,

ja ich weiß – aber da hat der Uralt-Popel-Editor von VBA nichts zu bieten.

In Visual Studio werden nicht verwendete Variablen angezeigt

Ich lösche die Variablen in VBA und „kompiliere“. Oder manchmal suche ich auch im Code, ob sie noch verwendet wird.

Ist mühsam, ich weiß … Ich entschuldige mich nicht dafür *lach*

Liebe Grüße

Rene

Wenn eine Schraube locker ist, hat das Leben etwas mehr Spiel

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Die Möglichkeit, den Datentyp über ein Gebietsschema auszuwählen, ist hinlänglich bekannt:

(Randbemerkung: mit der Taste [F] gelangt man am schnellsten zu Englisch / USA)

Benötigt man das andere Gebietsschema mehrmals, kann man dies in den Optionen in den Regionalen Einstellungen der Arbeitsmappe festlegen:

Hinweis: Nicht verwechseln mit den Regionalen Einstellungen, welche die Sprache der Namen der Variablen (beispielsweise Gefilterte Zeilen, Geänderter Typ, Sortierte Zeilen, …) festlegt:

Danke, Martin, für diesen wertvollen Tipp.

Sehr rücksichtsvoll vom Badespiegel, dass er beschlägt, wenn ich aus der Dusche komme.

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Kennt ihr das Problem? Man erstellt eine Reihe von Abfragen:

schließt den Editor, aber aus Versehen lädt man die Abfragen nicht als Verbindung, sondern als Tabelle – padautz – schon hat man 20 (ungewünschte) Tabellen.

Abhilfe schafft in den Optionen die „Standardeinstellung zum Laden von Abfragen“. Wählt man dort die Option „benutzerdefinierte Standardeinstellung“ und deaktiviert alle Kontrollkästchen, so werden die Abfragen nicht als Tabelle in Excel eingetragen.

Danke, Martin, für diesen wertvollen Tipp.

Werdet ihr beim Saubermachen auch immer so abgelenkt von Dingen, die ihr findet?

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Beim Importieren von Daten „erkennt“ PoweryQuery den Datentyp der Spalten. Das kann nervig oder lästig sein oder auch zu Fehlern führen:

Diese Option kann man deaktivieren:

„Spaltentypen und -überschriften für unstrukturierte Tabellen niemals erkennen.“

Danke, Martin, für diesen wertvollen Tipp.

Lass deinen Drucker nie spüren, dass du in letzter Minute ausdrucken möchtest. Sie können deine Angst spüren.

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Wir haben drei Möglichkeiten gefunden, um Werte, die sich in Excel befinden, als Parameter in PowerQuery zu verwenden:

  1. Die Werte stehen in einer intelligenten Tabelle:

2. Die Zellen, in denen sich die Werte befinden, werden mit einem Namen versehen:

3. Die Werte sind das Ergebnis von Berechnungen von Array-Funktionen, beispielsweise FILTER oder SEQUENZ:

Danke, Martin, für diesen wertvollen Tipp.

Gute Mütter lassen ihre Kinder die Rührstäbe abschlecken. Großartige Mütter schalten vorher das Gerät aus.

Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:

Lagert man in Excel einen Datenpfad in eine Zelle, kann man den Wert in PowerQuery verwenden, wenn man einen Drilldown erzeugt hat:

Verwendet man nun diesen Wert, also diese Variable, ist eine Firewall-Meldung die Folge:

Natürlich kann man in den Optionen diese Firewall-Einstellungen ausschalten. Oder man kann das Problem umgehen, indem man den Verweis auf die Excelzelle nicht in einer getrennten Abfrage belässt, sondern in die Formel einbaut, beispielsweise so:

= Folder.Files(Excel.CurrentWorkbook(){[Name="tblPfad"]}[Content]{0}[Pfad])

Danke, Martin, für diesen wertvollen Tipp.

Während Bruce Lee täglich am Kämpfen war, hat sich sein Bruder Müs ein Frühstücksimperium aufgebaut.

Hallo Herr Martin,

mittlerweile sehe ich schon die Zielgerade J.

Die Probleme mit dem Titel und dem Vorwort habe ich jetzt erledigt.

Ich hänge aber immer noch am Druckauftrag.

Anbei mal die aktuellen Codes:

[… es folgen einige Dutzend Codezeilen]

Folgende Fehler treten aber bei mir auf: Nachdem ich diese ProtectBefehle eingebaut habe kommt folgende Fehlermeldung:

Hallo Herr R.,

mir fehlt noch eine Info:

* öffnen Sie mal bitte Ihr VBA-Projekt (also die Word-Datei

* Wechseln Sie nach VBA

* Heben Sie den Schutz auf

* Klicken Sie auf den Menüpunkt Debuggen / Kompilieren In welcher Zeile entsteht der Fehler? Könnten Sie mir bitte diese Zeile oder auch die Zeilen „außenrum“ schicken.

Hallo Herr Martin,

Sie sind aber schnell… Meinen Sie das?

*lach*

DAS ist Falsch.

Es muss heißen:

ActiveDocument.Protect NoReset:=True

Zwischen Protect und NoReset muss ein Leerzeichen statt des Punktes stehen

Liebe Grüße

Rene Martin

PS: und dann noch einmal Debuggen, bitte!

####

Aber immer noch J

stimmt, Herr R., da habe ich doch mal einen Artikel darüber geschrieben.

Die Methode Protect verlangt unbedingt den Parameter Type, obwohl IntelliSense dies nicht anzeigt:

ActiveDocument.Protect NoReset:=True, Type:=wdAllowOnlyFormFields

Eben am Kondomständer im Drogeriemarkt vorbeigelaufen. So nah am Sex war ich schon lange nicht mehr.

Hallo Herr Martin,

ZÄHLENWENN (und SUMMEWENN) kenne ich. Damit kann ich die Anzahl der Zeilen bestimmen, die einem Kriterium genügen. ZÄHLENWENNS kenne ich auch. Damit kann ich zählen, wie oft mehrere Kriterien erfüllt sind, die mit einem logischen UND verknüpft sind.

Soweit so gut.

Aber wie kann ich eine Formel erstellen (gerne auch mit SUMMENPRODUKT), in der die Zeilen gezählt werden, bei denen in der ersten oder zweiten oder dritten Spalte nichts steht. Also: mindestens eine Spalte muss gefüllt sein.

Hallo Frau S.

ich würde die die drei Zellen verketten und wenn alle drei nicht leer sind, also <>““, dann würde ich diese zählen. Da Falsch = 0 und Wahr = 1, kann man die Summe verwenden.

beispielsweise so

=SUMME((C4:C15&D4:D15&E4:E15<>"")*1)

Hallo Herr Martin,

vielen Dank für die pragmatische Lösung, gefällt mir 😉

Heute Abend werde ich wieder über die Stränge schlagen und nach 21 Uhr einen Film gucken.

Hallo Herr Martin,

ich muss zeitlich und gedanklich kapitulieren und gebe daher gerne folgendes Problem an sie weiter.

In einem Excel.xlsx wird Bezug genommen auf den Wert von einem „Berechnung Planerfüllung.xlsx“,

entweder direkt oder mit einer Rundungsformel ergänzt, z.B.

  • =SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)
  • =RUNDEN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)/SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$H$1:$H$12);3)

In diesen zahlreichen Bezügen wurde übersehen, dass der Wert nur übernommen werden darf, wenn er >0 ist.

Um nicht per Hand die zahlreichen Stellen zu ändern, wollte ich dafür eine Formel schreiben,

die den Inhalt dieser Zellen ändert in

  • =WENN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)=0;““;SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12))
  • =WENN(RUNDEN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)/SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$H$1:$H$12);3)=0;““;RUNDEN(SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$G$1:$G$12)/SUMMEWENN(‚[Berechnung Planerfüllung.xlsx]Daten‘!$F$1:$F$12;A2;'[Berechnung Planerfüllung.xlsx]Daten‘!$H$1:$H$12);3)).\Tab 29\[Tab29_AP_W_Lage_D.xlsx]Lage_D_1930-1949′!$G$10;-2);““)

Beim Aufbau dieser Formel mit Textkettung scheitere ich daran, dass der Inhalt der Zelle sofort als Formel interpretiert wird und nicht als Text.

z.B. irgendwie so:  =“=Wenn(„&ERSETZEN(I25;1;1;““)&“>0;“&ERSETZEN(I25;1;1;““)&“;““““)“ Fällt Ihnen dazu was ein?

Meinen ersten Vorschlag – mit Ersetzen ([Strg] + [H]) zu arbeiten, scheitert, weil die Formeln auf diesem Tabellenblatt unterschiedlich aufgebaut sind und auf verschiedene Tabellenblätter zugreifen.

Meine zweite Nachfrage, warum sie denn die 0 ausblenden will und ob man das nicht per Formatierung (oder über die Optionen) machen könne, wurde mit einem „manchmal sind auch Fehler in den Formeln drin – die möchte ich auch – im Nachhinein – abfangen“ quittiert.

Mein dritter Gedanke: eine Formel zu ändern und dann nach unten zu ziehen, scheitert, weil die Formeln alle unterschiedlich sind.

Man scheitert auch mit folgenden Schritten:

FORMELTEXT liefert den Namen der Formel

WECHSELN (oder ERSETZEN) ersetzt einen Formelteil durch einen anderen.

Das Ergebnis ist keine Formel, sondern ein Text.

Man kann ihn kopieren und als Inhalt einfügen und dann den Text in der Zelle markieren und anschließend in die erste Zelle einfügen; aber das ist bei vielen (unterschiedlichen) Zellen sehr mühsam. Aber – einen besseren Weg weiß ich nicht.

Ich will nicht irgendwann im Leben auf verpasste Chancen zurückblicken und denken müssen: Das hätte ich essen können!

„Wo finde ich denn die Fußnoten in Excel?“, fragt mich eine Dame, die ich in puncto Excel berate.

Die gibt es nicht in Excel. Sie zeigt mir ihre Datei, die etwa so aussieht:

„Ich habe hier Sternchen eingefügt“, erklärt sie mir. Die kann ich ja auch hochgestellt formatieren.“

Ich nicke mit dem Kopf.

„Ob man denn auch bei Zahlen ein hochgestelltes Sternchen hinzuformatieren könne“, möchte sie wissen. „Nein – Zahlen sind ein Objekt“, lautet meine Antwort, „in Excel kann man nicht beispielsweise bei der Uhrzeit 1200 die beiden 00 hochstellen. Geht nicht!“

„Und bei Formeln?“, fragt sie:

Beispielsweise

=Vorjahr!D45&"*"

Meine Antwort: „Das geht leider auch nicht!“ Eine Formel kann eine Zahl liefern, aber keine formatierte Zahl. Ich kann die GANZE Zelle formatieren, aber leider nicht einen Teil des Ergebnisses der Formel.“

Sie bedauert.

Als ich argumentiere: „Aber PowerPoint hat auch keine Fußnoten“, antwortet sie: „aber dort kann ich einfach in die Texte Sternchen einfügen.“ Stimmt: PowerPoint kennt auch keine Zahlen wie Excel und keine Formeln.

Immer wenn ich ein paar Kilo verliere, finde ich sie kurz darauf im Kühlschrank wieder.

Guten Tag Herr Martin,

Ich habe ein Tabellenblatt, in dem jeden Tag neue Daten eingetragen werden und dann diese wieder gelöscht werden, da diese Daten mit einer Auswertung zusammenhängen. Da am nächsten Tag dort wieder neue Daten eingetragen werden müssen.

Und Power-Query aktualisiert ja im Normalfall nur die aktuellen.

Ich habe nun folgendes versucht wie James Baylay in folgendem Beitrag:

https://blog.jamesbayley.com/2018/04/23/power-query-how-to-load-only-fresh-rows-and-create-an-excel-history-table/

Die Schritte sehen so aus:

let
    Quelle = Excel.CurrentWorkbook(){[Name="tblZusammenfassung"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Schulz", Int64.Type}, {"Jackli", type number}, {"Marli", Int64.Type}, {"Jergli", Int64.Type}, {"Michal", Int64.Type}, {"Hans", Int64.Type}, {"Veitli", Int64.Type}}),
    #"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ", tblDieSieben}),
    #"Entfernte Duplikate" = Table.Distinct(#"Angefügte Abfrage")
in
    #"Entfernte Duplikate"

Ich habe folgendes Problem. Wenn ich bei der Abfrage «tblZusammenfassung» Laden-in   / Nur Verbindung erstellen ausführe, dann kommt die untenstehende Fehlermeldung:

Vielleicht haben sie einen Tipp für mich, wie ich das Problem lösen könnte.

Ich würde mich über eine positive Antwort freuen.

Was mir an IQ fehlt, hol ich mit BMW wieder auf

VBA-Schulung. Ich zeige und erkläre den Makrorekorder.

Aufgabe: Zeichnen Sie ein Makro auf, das eine Zelle formatiert, beispielsweise: fett, kursiv, Schriftart, Schriftgröße, Ausrichtung, Hintergrundfarbe, … und testen es an einer anderen Zelle.

Eine Teilnehmerin beschwert sich, dass der Makrorekorder nicht „optimale Spaltenbreite“ aufzeichnet.

Was hat sie gemacht? Sie hat ihr Makro in einer anderen Zelle in einer anderen Spalte ausprobiert. Leider zeichnet der Makrorekorder „hart“ den Spaltennamen auf, beispielsweise:

Columns("D:D").EntireColumn.AutoFit

Später erkläre ich den Unterschied zwischen der relativen und der absoluten Aufzeichnung

Wir zeichnen das Makro relativ auf und erhalten:

ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFit

Mein Kommentar: funktioniert, aber „schön“ ist der Code nicht gerade. Besser wäre sicherlich:

ActiveCell.EntireColumn.AutoFit

Warum warst du gestern nicht zu Hause, obwohl du wusstest, dass ich bei dir vorbeikommen wollte? – Nicht schlecht: Frage und Antwort in einem Satz.

Oh – man muss so aufpassen! Durchläuft man in VBA mit einem Zähler eine Sammlung an Objekten, greift sich Excel das erste, das zweite, das dritte, … Objekt. Aber welches ist das erste Objekt?

Bei Tabellenblättern ist das erste Objekt das linkeste. Dann das zweite von links. Dann das dritte.

Leider gibt es viele Sammlungen mit einer anderen „Reihenfolge“: die Reihenfolge, in der die Objekte erstellt wurden. Beispielsweise bei Diagrammen. Das erste ist nicht das linkeste, sondern das Diagramm, das zuerst erstellt wurde. Okay – das leuchtet ein.

Allerdings – wie sieht es bei Links aus. Wenn in einer Spalte mehrere Links stehen, ist der erste Link dann der oberste? Wird dann von oben nach unten weiter gezählt?

Nein! Auch hier gilt die Regel: die Reihenfolge des Erstellens entscheidet! Das heißt:

Das ist der erste Link:

Und nun wird der zweite und dann der dritte Link erstellt:

Und schließlich die übrigen 40.

Lässt man sie auslesen, erhält man:

Sub Links()
    Dim i As Integer
    Dim xlBlatt As Worksheet
    
    Set xlBlatt = ActiveSheet
    For i = 1 To xlBlatt.Hyperlinks.Count
        xlBlatt.Range("C" & (i + 3)).Value = xlBlatt.Hyperlinks(i).SubAddress
    Next i
End Sub

folgende Liste:

Zum Glück verfügt das Objekt Link über die Eigenschaften Range und damit über Range.Row und Range.Column. Natürlich auch über Range.Address. So kann man die Links lokalisieren.

Ich stand auf der Waage. Sie hat mir bestätigt, dass alle Cookies gespeichert wurden.

Oh, Mann – wie kann man nur zwei Wörterbücher gleich nennen?

Genauer: in Word gibt es ein Wörterbuch. In Excel auch. Das VBA-Objekt heißt in Word „Dictionary“. In Excel „CheckSpelling“.

Zugleich gibt es neben Array und Collections in VBA eine Sammlung „Dictionary“. Sie kann verwendet werden, wenn man die „Microsoft Scripting Runtime“-Bibliothek einbindet. Und dann stellt sich die Frage: „welches Dictionary wird denn nun verwendet?“

Richtig: es tritt ein Fehler auf, wenn beide verwendet werden. Genauer: der Fehler tritt dann auf, wenn man ZUERST einen Verweis auf die Word-Bibliothek setzt und anschließend auf die „Microsoft Scripting Runtime“-Bibliothek :

Die besser Lösung (statt dem Ändern der Reihenfolge): Die Bibliotheken voll qualifizieren. Dann klappt es:

Dim wdApp As Word.Application
Dim wdWordWoerterbuch As Word.Dictionary
Dim wdWordBenutzerWoerterbuecher As Word.Dictionaries

' -- Dictionary
Dim wdListeDict As Scripting.Dictionary

Set wdListeDict = New Scripting.Dictionary
wdListeDict.Add "007", "Bond"

' -- das Word-Wörterbuch
Set wdApp = New Word.Application
Set wdWordBenutzerWoerterbuecher = wdApp.CustomDictionaries
For Each wdWordWoerterbuch In wdWordBenutzerWoerterbuecher
    MsgBox wdWordWoerterbuch.Name
Next

Ich bin aus dem Töpferkurs rausgeflogen. Wahrscheinlich habe ich mich im Ton vergriffen.

Auch Word nervt gewaltig. Ein Formular mit Formularfeldern wird mit Inhalten (Titel, Name und weitere) gefüllt.

Es wird Bezug genommen – entweder mit REF oder durch einfaches Nennen des Formularfeldnamens in einer Feldfunktion. Klappt:

Was passiert jedoch, wenn ein Feld (Titel) nicht gefüllt wird:

Eine unschöne Lücke.

Nun – bei Serienbriefen kann man dies mit einfachen IF-Funktionen abfangen:

In Word scheitere ich.

Und: Word stellt zwar einige Rechenoperationen für Zahlen zur Verfügung: in den Formularfeldern

Und auch in den Feldfunktionen

Aber leider beziehen sich diese Berechnungen nur auf Zahlen – für Texte ist hier nichts vorgesehen – noch nicht einmal eine Verkettungsfunktion.

Ich bin sehr irritiert, dass DAS nicht funktioniert. Ich muss das Problem wohl anders lösen.

Habe Reinigungstipps mit Wodka bekommen. Ich trinke jetzt Wodka auf der Couch und muss sagen, dass die Wohnung schon viel sauberer aussieht.

Hallo Herr Martin,

in dieser Datei sind die Verknüpfungen falsch zugeordnet.

Ich habe versucht, dies über die Änderung der Hyperlinks zu korrigieren.

Wie kann die Zuordnung dauerhaft geändert werden? Sie scheint intern abgelegt zu sein.

=========

Hallo Herr H.,

Öffnen Sie den Blattschutz (haben Sie wahrscheinlich),

öffnen Sie den Link (haben Sie wahrscheinlich)

und ändern dann an den DREI Stellen den Bezug:

* Beschriftung (anzuzeigender Text)

* Quickinfo

* Ziel

Hab heute 20 Cent auf dem Parkplatz gefunden. Ich werde erst mal weiter so leben wie bisher, damit die Nachbarn nichts merken.

Hallo Rene,

mir ist doch noch etwas besseres eingefallen, lässt sich aber auch nicht umsetzen…

Ich wollte jetzt direkt in Power Query filtern nach den Zeilen, welche im Jahr, welches ich im Excel Blatt auswähle, ungleich 0 sind.

Irgendwo passt was noch nicht, hoffe du kannst mir helfen 😀

Hallo Nadine,

Wenn du Leerzeilen / Leerzellen rausfiltern möchtest, dann nicht wie in Excel mit zwei Anführungszeichen, sondern mit Null. Ähnlich wie Datenbanken hat PowerQuery einen eigenen Datentyp für leere Zellen: null:

= Table.SelectRows(#“Geänderter Typ“, each [Datum] <> null)

Und: der Feldname darf nicht in Anführungszeichen gesetzt werden – das Feld heißt: [Datum], nicht [„Datum“].

Ich habe neuerdings die Durchsage „Liebe Kunden, wir öffnen Kasse 2 für Sie“ als Klingelton auf meinem Handy. Einkaufen macht plötzlich viel mehr Spaß!

Hallo Rene,

ich habe schon einiges in Power Query bearbeitet und bin gerade auf dem Stand, dass ich mir durch Filter genau die Daten aus Power Query ziehe welche ich benötige.

Aktuelles Problem: Ich möchte nur die Zeilen haben, welche in dem Jahr Werte haben, welches ich als Filter eingebe. Ich will aber nicht nur das Jahr, sondern alle Werte dann, wenn in diesem speziellen Jahr ein Wert vorhanden ist.

Den Filter „Jahr“ habe ich nicht in Power Query benutzt, da ich noch keine Lösung gefunden habe.

###

Hallo Nadine,

1.) wenn du einen Filter definierst, kannst du ihn auch in PowerQuery „reinziehen“. Danach würde ich ihn als Drilldown in einen Wert umwandeln – etwas so:

2.) Filtere ein beliebiges Datum. Es sieht dann so aus:

= Table.SelectRows(#“Gefilterte Zeilen“, each [Datum] >= #date(2020, 1, 1))

Und nun ersetze ich die Jahreszahl 2020 durch meine „Variable“ aus Schritt 1.

Ich mache im Moment drei Diäten. Von einer alleine wird doch kein Mensch satt!

PowerQuery-Schulung.

Eine Teilnehmerin fragt, wie sie den M-Code einer Abfrage dokumentieren, das heißt in Word speichern kann.

Ich antworte ihr, dass sie den „Erweiterten Editor“ öffnen kann und dort den Code einsehen und herauskopieren kann.

Eine andere Teilnehmerin weist mich darauf hin, wie man alle Codetexte aus allen Abfragen erhält:

„Du musst nach Excel wechseln:

Dort musst du die Abfragen markieren und kopieren:

Und das Ergebnis kann man nach Word einfügen. PowerQuery fügt nicht die Namen der Abfragen, sondern den M-Code ein:

Ich bin begeistert! Ein Dankeschön an Lydia Homann für diesen guten Tipp. Schon nervt Excel wieder ein bisschen weniger!

Ein Hai besteht zu 64% aus Haiweiß, zu 33% aus Haigelb und 3% aus Haischale. Weiß nur kaum jemand.

In Excel kann man, wenn die Berechnungsoptionen auf „manuell“ gestellt sind, die ganze Datei neu berechnen lassen oder ein Blatt:

Und was fragt die Teilnehmerin in der letzten Excelschulung:

„Kann ich auch alle Blätter neu berechnen lassen, aber nicht das aktuelle? Weil dort sehr viele Formeln stehen – DIESE Aktualisierung ist sehr zeitaufwändig.“

Ich wüsste nicht wie (außer natürlich mit einem Makro).

Ich ärgere mich ja nicht immer über Excel – manchmal schlafe ich auch.

Die neuen Sicherheitseinstellungen kennen Sie? Auf meinem Rechner erhalte ich die Warnmeldung:

Es wurde im Internet schon viel über Sinn oder Unsinn dieser Sicherheitshürde diskutiert.

Sie entfernen es über die Eigenschaften der Datei:

Die Erläuterung:

Makros aus dem Internet werden in Office standardmäßig blockiert. – Deploy Office | Microsoft Docs

Kennst du das? Montag Morgen voller Freude aus dem Haus stürmen? – Ich kenne das auch nicht!

Reguläre Ausdrücke – sie sind so klasse – warum mag Microsoft sie nicht?

Die Aufgabe: Extrahieren Sie aus zirka 30.000 Zellen einer Exceltabelle die darin befindlichen Datumsinformationen. Es finden sich Texte wie:

Maistraße 17 (St. 29.03.2016), Flurnummer-alt: 47/11

Bahnhofstraße 1 – 17.3.2022 – Flurnummer-alt: 08/15

Hirtenweg 3A 05-2005 – Garagen, Flurnummer-alt: 00/77

Ich überlege: Formeln wären eine Option. Aber sehr umständlich.

VBA wäre gut.

PowerQuery auch – kennt aber keine regulären Ausdrücke.

Ich entscheide mich für VBA.

Dort muss man einen Verweis auf „Microsoft VBScript Regular Expressions“ einbinden. Oder diese Klasse mit

Set regex = CreateObject("vbscript.regexp")

aufrufen. Dann kann man definieren:

    ' -- Muster: ***01.01.2022***
    strMuster1 = ".*\d{1,2}\.\d{1,2}\.\d{2,4}.*"
    strMuster1_Raus = "\d{1,2}\.\d{1,2}\.\d{2,4}"

Und kann nun extrahieren:

    regex.Pattern = strMuster1
    regexRaus.Pattern = strMuster1_Raus
    regexRaus.Global = True
    For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        If regex.Test(Range("I" & i).Value) = True Then
            Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
            j = 0
            For Each strFund In strTreffer
                strTemp = strFund
                If IsDate(strTemp) Then
                    If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
                        Range("Q" & i).Offset(0, j).Value = strTemp
                        Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                    
                    Else
                        Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
                        If Year(CDate(strTemp)) > Year(Date) Then
                            Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                        End If
                    End If
                    
                Else
                    Range("Q" & i).Offset(0, j).Value = strTemp
                    Range("Q" & i).Offset(0, j).Interior.Color = vbRed
                End If
                j = j + 1
            Next
        End If
    Next

Vier andere Varianten werden analog abgearbeitet. Klappt.

Warum hat VBA nicht als Standard Regex eingebunden?

Warum kennt PowerQuery keine regulären Ausdrücke?

Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?

Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:

Trotzdem: ich ziehe hier VBA vor.

Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.

Ich habe mir die Beine wachsen lassen. – Waren sie vorher kürzer?

Hallo Herr Martin,

Es geht um die Extrahierung von Datums- und Adressinformationen aus einem Bemerkungsfeld im Rahmen der Datenmigration.

Die Ausgangsinformationen stehen einer Spalte.

Diese Felder enthalten Stichtage und Adressen (Straße, Hausnummer, Zusatz).

Zum Stichtag:

  • Es gilt folgende Grundregel:
    Falls im Bemerkungsfeld ein Stichtag aufgeführt ist,
    dann soll dieser als Stichtag übernommen werden
    ansonsten gilt das Ausgabedatum als Stichtag
  • Der Stichtag ist mit unterschiedlichsten Schreibweisen im Bemerkungsfeld versteckt.
    Beispiele dazu:
    • (Stichtag 18.07.2014)
    • ST 05.08.2021
    • ST: 18.02.2022
    • (St. 08.01.2018)
    • (St. 01.09.16)
    • (31.08.16)
    • St.08.10.2015
    • StT 24.6.14
    • St. 01-2017
    • St. 10/2015
    • St.04/2005
    • (St. 24.04.2014+16.07.2015)
    • ST 05.06.1990 u. 11.12.1991
    • 2 Stichtage zus.gefasst 17.06.2020 + 06.10.2021
  • Aufgabe ist:
    • Unterschiedliche Schreibweisen für den Stichtag herauszufinden und zu berücksichtigen
    • Nach obiger Grundregel die Stichtage als Datum zu extrahieren
    • Weitere ggfs. notwendige Regeln abzuklären und zu ergänzen:
      • Falls reine Monatsangabe, dann den 1. als Tag verwenden
      • Falls zwei Stichtage angegeben, diese markieren, müssen manuell geklärt werden
        (bei zwei Datensätzen Stichtage aufteilen, bei einem Datensatz wahrscheinlich erster Stichtag)

Zur Adresse:

  • Es gilt folgende Grundregel:
    Die Adresse aus dem Bemerkungsfeld soll extrahiert
    und in Straße, Hausnummer und Zusatz getrennt übernommen werden
  • Einschränkungen:
    • Es gibt einzelne Datensätze zu auswärtigen Gemeinden oder zu speziellen Portfolioobjekten,
      für die keine Adresse zu extrahieren sind. Diese sind in der Spalte „Keine Adresse“ gekennzeichnet.
    • Im Migrationsskript wurden für viele Datensätze die Adresse bereits extrahiert
      und zwar für die Adressen, deren Straßennamen bekannt waren.
      Diese sind in den Spalten REFERENZOBJEKTADRESSE_STRASSE, …_HAUSNUMMER und …_ZUSATZ entsprechend gefüllt
      und müssen nicht mehr berücksichtigt werden.
  • Die Adresse steht, sofern vorhanden, am Anfang des Bemerkungsfeldes.
    Falls keine exakte Adresse vorhanden war, steht vor dem Straßennamen teilweise „Nähe“ oder „am“, „an der“, …
    oder auch zwei Straßennamen, an dem sich ein Grundstück befindet
    Beispiele für Adressen:
  • Adenauerallee
  • Ackerweg
  • Alte Poststrasse
  • Alt-Moabit
  • Am Borsigturm
  • Augsburger Straße
  • Augsburger Strasse
  • Avenue d’Ouchy
  • Clius
  • Grosse Praesidenten Str.
  • Im Astenfeld
  • Inge Beisheim Platz
  • Kammelenbergstrasse
  • Aufgabe ist:
    • Adressen zu erkennen und zu extrahieren gemäß obiger Grundregel inkl. der Einschränkungen
    • Weitere ggfs. notwendige Regeln abzuklären und zu ergänzen:
      • Bezugsangaben wie Nähe, am usw. mit Straßennamen extrahieren, sofern keine Hausnummer vorhanden

Falls zwei Adressen/Straßen vorhanden, keine extrahieren

Meine Antwort: Uff! So etwas können nur Menschen eintragen! Ich hoffe, dass bei der nächsten Dateneingabe die Daten besser werden.

(Randbemerkung: Beim Screenshot handelt es sich um rein fiktive Daten!)

Früher wollte ich Busfahrer werden, weil ich das Zischen der Türen so liebte. Dann entdeckte ich Bierdosen …

Osterkaffee mit Nachbarn. Frau Nachbarin „greift mein Wissen ab“ und stellt mir eine Frage. Natürlich zu Outlook.

„Sag mal: ich möchte mehrere Personen einladen, aber so, dass sie sich nicht gegenseitig sehen; also ich möchte sie auf BCC setzen. Wo schalte ich das bei einer Besprechungseinladung ein?“

Meines Wissens geht das nicht, denn der Witz an Einladungen ist ja, dass jeder jeden im Terminplanungs-Assistenten sehen kann.

Mein Vorschlag, einen Termin zu erstellen und diesen als Anlage an alle BCC zu senden, stieß auf keine Gegenliebe: „Ich will ja die Leute einladen. Sie sollen zu- oder absagen. Das will ich ja in meinem Kalender sehen. Aber so, dass DIE nicht die Mailadressen der anderen sehen.“

Schwierige Aufgabe – ich weiß keine Lösung dafür.

Wie alt ich bin? Ich bin 25 zzgl. MWSt und Versandkosten.

Die lieben, kleinen Unterschiede.

Danke an Tanja Kuhn für folgenden Hinweis:

Wählt man in Excel bei den Zellformaten das Zahlenformat „Sonderformat“, so ist die Liste bei Deutsch (Schweiz) und Deutsch (Österreich) leer:

In Deutsch (Deutschland) jedoch gibt es Auswahlmöglichkeiten:

„Vielleicht gibt es in der Schweiz gar keine Postleitzahlen, Sozialversicherungsnummern oder Bücher mit ISBN“, witzle ich. Wir lachen beide.

What’s your address? – 151.194.25.39 – No – your local address? – 127.0.0.1 – I mean your physical address? – 19:08:AF:51:11:08

Ich bin gerade völlig perplex. Ich programmiere für eine Schweizer Firma ein Excel-Tool. Ich lasse alle Tabellenblätter, die mit Monatsnamen beschriftet sind, ausblenden. Nur das Blatt MRZ bleibt stehen. In der Schweiz bleibt es stehen.

Ich frage Tanja Kuhn. Sie hat die Schweizer Oberfläche von Excel:

Und ja: sie bestätigt es: seit einigen Versionen lautet die Abkürzung des dritten Monats in der Schweiz nicht Mrz, wie in der ISO 8601 (EN 28601:1992) festgelegt, sondern Mär. Das erkennt man schnell, wenn man den Text „Jan“ einträgt unter herunterzieht:

Und eben deshalb liefert der VBA-Befehl

Format(DateSerial(2022, 3, 1), "MMM")

in der Schweiz etwas anderes als in Deutschland (oder Österreich).

Perfide!

Als mit klar wurde, wie lange es dauern würde, „Nana Mouskouri“ in die Schulbank zu schnitzen, habe ich beschlossen, lieber „Kiss“ zu hören.

Böse!

Eine Funktionen der intelligenten Tabellen sind „ausgegraut“. Warum?

Auch sortieren und filtern funktioniert nicht …

Man muss schon ganz genau hinschauen, um zu entdecken, dass es sich hier um ZWEI Tabellen handelt, die nebeneinander eingefügt wurden …

Ich habe mich heute nackt gewogen. In der Obstabteilung wurde es daraufhin etwas unruhig.

Word. Manchmal auch ärgerlich.

Ich weiß – nicht alle Feldfunktionen werden automatisch aktualisiert. Beispielsweise REF. Innerhalb eines Textes ist es kein Problem mit REF auf ein Formularfeld zu verweisen. Klappt prima.

Klappt allerdings nicht, wenn sich das Feld in einem Textfeld oder in der Kopf-, beziehungsweise Fußzeile befindet:

Beim Drucken wird des natürlich aktualisiert:

Oder man muss ein Makro schreiben …

Das Alter ist auf Rosen gebettet: Gürtelrose, Arthrose, Fibrose, Neurose und Osteoporose

Sehr geehrter Herr Martin,

als Anhang schicke ich Ihnen eine Beispieldatei.

Es geht hier um den Umgang mit einem umfangreichen Fußnotentext (hier: rot).

Wie Sie sehen, ist auf der ersten Seite Haupt- und darunter Fußnotentext.

Nun ist aber ab Seite 2 nur noch Fußnotentext zu sehen.

Wie kann man das anstellen, daß auch ab Seite 2 ff. sowohl Haupt- als auch Fußnotentext platziert werden kann.

Ich habe leider keine Lösung gefunden.

Hallo Herr F.,

schöne Frage. Ich habe mal nachgeschaut (haben Sie sicherlich auch schon): Wenn ich in der Entwurfsansicht mit über Referenzen die Notizen anzeigen lasse, finde ich nur die Fußnoten, Trennlinien und Fortsetzungstexte, aber keine Einstellungen oder Optionen für eine maximale Länge.

Ich habe auch eine Tabelle versucht – auch das klappt nicht.

Sorry – aber ich weiß keine Lösung dafür.

Ich würde eher fragen, ob wirklich sooooo viel Text in der Fußnote stehen muss – wären hier Endnoten nicht besser geeignet? Oder sollte der Text nicht besser im Text stehen?

Liebe Grüße

Rene Martin

Wenn ich noch mehr Kaffee trinke, heißt meine Blutgruppe bald Arabica.

Hallo Rene,

komme tatsächlich um VBA nicht herum und benötige Deine Unterstützung.

Ich habe mir mit Hilfe YouTube ein kleines Makro gebastelt, welches nur das Tabellenblatt „Ausdruck“ zum Ausdrucken zulässt.

Wie muss ich vorgehen, wenn ich nun noch ein zweites Tabellenblatt zulassen möchte?

Da hast Du doch sicher eine Idee, oder?

Sub Workbook_BeforePrint(Cancel As Boolean)

Dim WsName As String

WsName = „Ausdruck“

For Each xWs In Application.ActiveWorkbook.Windows(1).SelectedSheets

    If xWs.Name <> WsName Then

    MsgBox („Kein Ausdruck dieses Tabellenblattes möglich!“)

        Cancel = True

    End If

Next

End Sub

Gruß

Christian

###

Hallo Christian,

ich glaube du denkst viel zu kompliziert – ich denke das folgende Makro macht, was du möchtest:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    If ActiveSheet.Name <> „Ausdruck“ And ActiveSheet.Name <> „Ausdruck2“ Then

        MsgBox „Kein Ausdruck des Tabellenblattes “ & ActiveSheet.Name & “ möglich!“

        Cancel = True

        End

    End If

End Sub

Wenn weibliche Tintenfische keine Lust haben sich zu paaren, erwürgen sie das Männchen und verspeisen es. Nur mal so am Rande erwähnt …

Schönen guten Tag, Herr Martin,

Ich möchte wissen, ob es eine Funktion gibt, die mir das Einbetten von Visio-Organisations-Diagrammen in PowerPoint ermöglicht, die einem Powerpoint-Anwender die Bearbeitung der Objekte ermöglicht, ohne dass er Visio auf dem Rechner installiert hat.

Ich hatte gehofft, dass es im Hintergrund der Einbettung eine solche Anwendung gäbe, doch ich habe verschiedene Objekt-Einbettungen in PowerPoint aus Visio ausprobiert, habe es mit Kopieren und mit Links versucht, aber meine Kollegin, die keine Visio-Lizenz auf dem Rechner hat, kann keine davon bearbeiten (die Links griffen übrigens auf einen für meine Kollegin gesperrten Ordner zu, das wird auch so bleiben).

Vielen Dank im Voraus,

mit freundlichen Grüßen aus Hamburg,

####

Hallo Frau T.,

ich fasse Ihre Frage zusammen: Sie möchten eine Visio-Zeichnung bearbeiten, ohne dass Visio installiert ist. Das geht nicht!

Es ginge auch nicht, wenn Sie eine Excel-Tabelle nach Word kopieren und diese dann auf einem anderen Rechner, auf dem kein Excel installiert ist, bearbeiten wollen.

Ich habe es mal probiert: Wenn Sie die Visio-Zeichnung kopieren

und als „Erweiterte Metadatei“ in PowerPoint einfügen,

können Sie diese in PowerPoint als „Grafik umwandeln“ (bearbeiten),

die Gruppierung aufheben und nun jedes Objekt wie eine PowerPoint-Form bearbeiten.

Aber ich fürchte, das wollen Sie nicht …

Und: ich fürchte, dass ich Ihnen hier nicht helfen kann.

Liebe Grüße

Rene Martin

STERB!!! – Der Imperativ von „sterben“ wird mit „i“ gebildet, du bildungsresistenter Intelligenzallergiker. – Sterbi?

Lieber Herr Martin,

nun haben sich bezüglich der Kriterien- „=“-Auswahl einige Fragen ergeben.

Zur Erinnerung: Sie hatten uns Excel-Dokumente erstellt, in denen unterschiedliche Funktionen kombiniert wurden, damit bestimmte Filterungen auf ein eingelesenes Exceldokument ausgeführt werden können.

Zu den Fragen:

  1. Wie ist es möglich, dass bei dem Bereich Kriterium-„=“ auch mehrere Werte eines Attributs angegeben werden können?
    Z.B. Attribut
    Lagekarte-GAA = G – Gut
    Lagekarte-GAA = D – Durchschnitt
    Und es werden alle Kauffälle angezeigt, die entweder Lagekarte-GAA = G – Gut ODER Lagekarte-GAA = D – Durchschnitt haben.

Hallo Frau I.,,

Habe ich Sie richtig verstanden: Sie möchten eine Mehrfachauswahl mit ODER.

Das Multiplikationszeichen entspricht dem logischen UND; das Additionszeichen den logischen ODER.

Also so:

=WENNFEHLER(FILTER(qry_Datenzugriff_SpaltenAuswahl;

(WENN(ODER(A2=““;B2=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A2&“]“)=B2))+

WENN(ODER(A3=““;B3=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A3&“]“)=B3))+

WENN(ODER(A4=““;B4=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A4&“]“)=B4))+

WENN(ODER(A5=““;B5=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A5&“]“)=B5))+

WENN(ODER(A6=““;B6=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A6&“]“)=B6))+

WENN(ODER(A7=““;B7=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A7&“]“)=B7))+

WENN(ODER(D2=““;E2=““;F2=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D2&“]“)>=E2)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D2&“]“)<=F2))*

WENN(ODER(D3=““;E3=““;F3=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D3&“]“)>=E3)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D3&“]“)<=F3))*

WENN(ODER(D4=““;E4=““;F4=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D4&“]“)>=E4)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D4&“]“)<=F4))*

WENN(ODER(D5=““;E5=““;F5=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D5&“]“)>=E5)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D5&“]“)<=F5))*

WENN(ODER(D6=““;E6=““;F6=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D6&“]“)>=E6)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D6&“]“)<=F6))*

WENN(ODER(D7=““;E7=““;F7=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D7&“]“)>=E7)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D7&“]“)<=F7))

);““)

Kontostand angeschaut und gedacht: „arm aber sexy“. In den Spiegel geschaut und gedacht: „Mist, nur arm!“

Ein Hi in den Süden,

     hallo Rene,


heute ist Samstag und das halbe Wochenende schon einmal rum. Hm.

Der sechste Arbeitstag läuft bei mir noch bis 0 Uhr, morgen auch, aber eingeschränkt.

Was wurde aus meinem aktuellen EXCEL-VBA-Projekt?

(1) Deinen VBA zum Setzen des Filters habe ich versucht zu erweitern durch Variablen.
     Klappt nicht so dolle.

    s_DatenRange = "$A$1:$I$" & l_ZeileLetzte
    s_DatumStart = s_Start_Jahr & ", " & s_Start_Monat & ", " & s_Start_Tag
    s_DatumEnde = s_Ende_Jahr & ", " & s_Ende_Monat & ", " & s_Ende_Tag

    MsgBox "Jetzt kommt das Problem: DateSerial - Argument ist nicht optional"

' Versuch 1
    ActiveSheet.Range(" & s_DatenRange & ").AutoFilter Field:=6, Criteria1:= _
    ">=" & CDbl(DateSerial(" & s_DatumStart & ")), Operator:=xlAnd, Criteria2:="<=" & CDbl(DateSerial(" & s_DatumEnde  & "))

' Versuch 2

Dim s_teil_1, s_teil_2 As String

s_teil_1 = DateSerial(" & s_DatumStart & ")
s_teil_2 = DateSerial(" & s_DatumEnde & ")
s_teil_1 = DateSerial(s_DatumStart)
s_teil_2 = DateSerial(s_DatumEnde)

    ActiveSheet.Range(" & s_DatenRange & ").AutoFilter Field:=6, Criteria1:= _
    ">=" & CDbl(" & s_teil_1 & "), Operator:=xlAnd, Criteria2:="<=" & CDbl(" & s_teil_2 & ")

Hallo Jürgen,

Zu DateSerial: Kennst du dich Excel-Funktion DATUM nicht? Sie wandelt eine JahresZAHL, eine MonatsZAHL und eine TagesZAHL in ein Datum um. Und eben das macht DateSerial in VBA.

Wer schlanker wirken möchte, sollte sich in der Nähe von Elefanten aufhalten.

Einfach nicht aufgepasst.

Access

Ich erstelle ein Endlosformular mit einem Textfeld, das natürlich mehrmals angezeigt wird:

Das Feld, oder genauer: der Detailbereich soll so groß werden wie der Text es vorgibt. Ein paar Zeilen Code sind nötig:

         If intZeilen > 6 Then
            
            Me.Detailbereich.Height = (1701 / 6) * intZeilen
            Me.txtAktueller_Stand.Height = (1701 / 6) * intZeilen
            
         Else
            
            Me.Detailbereich.Height = 1701
            Me.txtAktueller_Stand.Height = 1701
            
         End If

Der Detailbereich wird größer, wenn längerer Text eingegeben wurde und der Cursor in das Textfeld gesetzt wird:

Allerdings: der Bereich wird nicht wieder kleiner:

Die Lösung:

ich darf nicht zuerst den Detailbereich verkleinern und anschließend das Textfeld, sondern umgekehrt: zuerst das Textfeld und DANN den Detailbereich:

            Me.txtAktueller_Stand.Height = (1701 / 6) * intZeilen
            Me.Detailbereich.Height = (1701 / 6) * intZeilen

DANN klappt es auch:

Mein Arzt hat gesagt, ich soll mein Trinkverhalten beobachten. Ich bin jetzt auf der Suche nach einer Bar mit Spiegel.

Excelstammtisch letzte Woche.

Sabrina stelle eine interessante Frage:

In einer Liste – egal ob intelligente Tabelle oder Bereich – wurde ein Filter gesetzt:

Über diesen Autofilter wird in einer Spalte gefiltert:

Eine Zelle einer anderen Spalte wird markiert und kopiert:

Schaltet man den Filter über das Symbol „Alle löschen“ in der Registerkarte „Daten“ aus:

Wird leider der Kopiermodus deaktiviert. Der Zellinhalt kann nicht mehr in das Textfeld des Autofilters eingetragen werden:

Auch das Öffnen der Zwischenablage bringt keinen Erfolg.

Ich schlage vor, nicht die Zelle, sondern den Zellinhalt zu markieren und zu kopieren:

Klappt, stößt aber nicht auf Gegenliebe.

Josef meldet sich zu Wort. Man findet im Kontextmenü der Zelle den Befehl:

Nach dem Wert der ausgewählten Zelle filtern.

So erhält man die Schnittmenge beider Filter:

Deaktiviert man allerdings den ersten Filter:

hat man nur noch den zweiten Filter.

Sabrina ist begeistert und Excel nervt ein bisschen weniger.

Danke an Josef Feißt für diesen wertvollen Hinweis.

Du bist so süß, wenn du betrunken bist. Du bist auch süß, wenn ich betrunken bin.

Excelstammtisch letzte Woche.

Frank zeigt die unterschiedlichen Orte, an denen man Measures erstellen kann.

Wählt man Power Pivot / Measures / Measures verwalten, so kann man den Dialog an allen vier Seiten (und der Ecke) vergrößern:

Dagegen in einer Pivottabelle, deren Daten dem Datenmodell hinzugefügt wurden, kann der Dialog „Measure bearbeiten“ nur an der Ecke vergrößert werden:

Amüsant.

Danke an Frank Arendt-Theilen für den Hinweis.

Ich bin jetzt in einem Alter, in dem dir der Körper am nächsten Tag ganz leise ins Ohr flüstert: Mach das nie, nie wieder!

Excelstammtisch letzte Woche.

Volker präsentiert eine clevere Lösung für die Datenüberprüfung. Er weist auf folgendes Phänomen hin:

Liegt eine intelligente Tabelle auf dem gleichen Tabellenblatt, bewirkt eine Erweiterung, dass die Liste der Datenüberprüfung auch erweitert wird:

Liegt die intelligente Tabelle jedoch auf einem anderen Tabellenblatt, wird die Liste der Datenüberprüfung nicht erweitert.

Danke an Volker Pagel für den guten Hinweis.

Januar ist wie Montag. Nur länger.

Excelstammtisch letzte Woche.

Frank stellt die neuen Elemente von Excel vor. Beispielsweise findet sich im Kontextmenü der Statuszeile ein neuer Eintrag „Sheetnummer“:

Abgesehen vom merkwürdig übersetzen Namen „Sheetnummer“ eigentlich eine praktische Sache: Die Anzahl der Tabellenblätter werden angezeigt.

Allerdings: Auch ausgeblendete Blätter (auch xlSheetveryHidden) werden aufgelistet.

Josef wirft die Frage in den Raum, ob wir das wollen. Zu Recht: ich will das eigentlich nicht – manchmal „verstecke“ ich bewusst Dinge in Tabellen, die ich weitergebe, die nicht auf den ersten Blick einsehbar sind …

Danke an Frank Arendt-Theilen für diese Information.

Mein Mann hat sich gerade rasiert. Laut Waschbecken habe ich ein Wildschwein mit Haarausfall geheiratet.

Excelstammtisch letzte Woche.

Frank stellt den Navigator vor, den Excel vor Kurzem in Microsoft 365 eingeführt hat:

Woah, denke ich: endlich nach 20 Jahren hat Microsoft von openOffice Calc und LibreOffice Calc den Navigator abgeschaut – den gibt es dort seit Ewigkeiten!

Der Navigator in Calc

Danke an Frank Arendt-Theilen für den Hinweis.

Am kältesten ist es da, wo es am draußensten ist.

Hallo genervter Excel-Verliebter,

kennst Du das Problem beim Löschen von Zeilen bei ausgeblendeten Spalten?

Eine intelligente Tabelle mit Filter.

Hier sind auch die ganzen Zeilen und alle gefilterten Zeilen markiert und STRG + Minus funktioniert nicht. Spalte B ist ausgeblendet.

Zeilen in einem gefilterten Bereich oder in einer gefilterten Tabelle können nicht verschoben werden.

Sind nur die Daten markiert und nicht die ganzen Zeilen, funktioniert es doch wieder mit Meldung. Es sind dann aber wieder alle Daten der Zeile weg und nicht nur die markierten Tabellenwerte.

Grüßle

Andreas (Thehos)

Die Wahrheit kommt ans Licht und die Zahnpasta nicht zurück in die Tube.

In Excel kann man eine Reihe von Elementen einfügen: Bilder, 3D-Grafiken, Diagramme, Formen und auch SmartArts:

Öffnet man diese Datei in Excel online, wo SmartArts nicht unterstützt werden, wird dort ein Rechteck angezeigt:

Ebenso in teams:

Andreas Thehos schreibt:

„habe heute einen schönen Excel-Fehler gefunden.

SmartArts sorgen dafür, dass sämtliche Objekte eines Tabellenblatts entfernt werden.

Die Datei liegt auf einem SharePoint Online. Sobald jemand online per Browser oder Teams darauf zugreift und auch nur das Tabellenblatt wechselt, werden bei der Synchronisation alle Objekte im Blatt des SmartArts entfernt. Anschließend gibt es einen Fehler in Excel in drawingsX.xml

Kennst du das?“

Leider kann ich das nicht nachvollziehen …

Es hat keinen Sinn, über die Männer zu jammern. Wir müssen mit dem vorhandenen Material arbeiten.

Einfach nicht aufgepasst!

Ich habe eine Arbeitsmappe mit sieben + ein, also acht Tabellenblattern. Sie heißen:

Chef, Brummbär, Schlafmütz, Hatschi, Pimpel, Happy, Seppel und Schneewittchen

Die ersten drei Blätter sind ausgeblendet, ebenso das letzte (Master-)Blatt:

Also auf Excel-Seite sieht das so aus:

Nun soll per VBA das erste Blatt sichtbar gemacht werden, umbenannt werden und an eine bestimmte Position verschoben werden. Das Masterblatt „Schneewittchen“ wird anschließend wieder unsichtbar gemacht.

Schritt für Schritt:

1.) Zugriff auf das (unsichtbare) Blatt „Schneewittchen:

    Dim xlBlatt As Worksheet
    Dim i As Integer
    Dim strBlattname As String
    
    Set xlBlatt = ThisWorkbook.Worksheets("Schneewittchen")

2.) Blatt wird eingeblendet:

    xlBlatt.Visible = xlSheetVisible

3.) Blatt wird vor das erste Blatt geschoben:

    xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)

4.) Mit Hilfe einer benutzerdefinierten Funktion GibtEsBlattname wird überprüft, welcher Blattname noch nicht existiert, beispielsweise „Schneewittchen 001“:

    i = 0
    Do
        i = i + 1
        strBlattname = "Schneewittchen " & Format(i, "000")
    Loop Until GibtEsBlattname(strBlattname) = False

5.) Das erste Blatt wird umbenannt:

ThisWorkbook.Worksheets(1).Name = strBlattname

6.) Das Masterblatt „Schneewittchen“ wird ausgeblendet:

ThisWorkbook.Worksheets("Schneewittchen").Visible = xlSheetVeryHidden

7.) Das neu erzeugte Blatt wird nach hinten verschoben:

ThisWorkbook.Worksheets(strBlattname).Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

Und: ein Fehler ist die Folge:

Die Move-Methode des Worksheet-Objektes konnte nicht ausgeführt werden.

Der Fehler liegt in folgender Zeile:

xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)

Welches ist das ERSTE Tabellenblatt? Blatt Nummer 1 oder das erste sichtbare Tabellenblatt. Die Antwort: letzteres: das Blatt wird vor das erste sichtbare Blatt kopiert. Damit ist es anschließend nicht das erste Blatt! Sondern die drei ausgeblendeten Blätter liegen noch weiter links von dem Blatt.

Leider besitzt die Methode Copy kein Objekt, an das das Ergebnis des Kopierens übergeben werden kann.

Also muss man sich „auf die Suche“ nach dem Blatt machen, beispielsweise: durchlaufe alle Blätter und such das Blatt, das „Schneewittchen(2)“ heißt, besser

ThisWorkbook.Worksheets(1).Name Like "Schneewittchen *"

Dann klappt es auch!

Wer den Spruch „Aus den Augen – aus dem Sinn“ erfunden hat, hat sicherlich nie eine Spinne unter dem Bett verschwinden gesehen.

Nein, das ist nicht schön! Visio weigert sich, einen Button auf das Zeichenblatt einzufügen:

Ich lösche einige Shapes auf dem Zeichenblatt, wie vorgeschlagen – aber es nützt: gar nichts.

Mein Trick: Ich habe ein zweites Zeichenblatt erstellt, dort einen Button eingefügt; die Zeichnung auf das zweite Blatt kopiert und alles wieder zurückkopiert. Klappt. Okay – ich musste noch die Buttons umbenennen – die Namen hat mir Visio bei der Kopieraktion zurückgesetzt.

Klappt. Doof!

Forscher haben Mikroplastik im menschlichen Darm gefunden. Die nächste Evolutionsstufe heißt dann „Lego“.

Hallo Rene,

deine Mails mit positiven Informationen und Anhang erreichten mich.

Themenwechsel 3 von 3:

Falls du an weiteren Fragen rund um EXCEL-VBA Interesse hast, informiere mich einfach. Dazu habe ich dir zwei Dateien mitgesendet.
Die Lösung der Probleme ist kein MUSS.

Die Thematiken:

(1) ein Filterproblem ist bestimmt nicht so schwierig zu lösen. Eine Liste:

Der Filter:

Nach Filtersetzen werden keine Daten angezeigt.

Der Code:

    ActiveSheet.Range("$A$3:$F$12").AutoFilter Field:=4, Criteria1:= _
        ">=01.01.2000", Operator:=xlAnd, Criteria2:="<=31.03.2022"

Hallo Jürgen,

der Filter verlangt einen Datumswert, der in eine Zahl konvertiert wurde:

    ActiveSheet.Range("$A$3:$F$12").AutoFilter _
        Field:=4, 
        Criteria1:= ">=" & CDbl(DateSerial(2000, 1, 1)), _
        Operator:=xlAnd, _
        Criteria2:="<=" & CDbl(DateSerial(2022, 3, 31))

Dann klappt es:


Früher haben wir uns gegenseitig eingecremt – da war es erotisch – heute ist es eher rheumatisch

Einer gewinnt! Oder: es kann nur einen Highlander geben.

Schöne Frage in der Excel-Schulung: kann ich eine (intelligente) Tabelle mit einer bedingten Formatierung versehen? Wissen Sie es? Welche Farbe gewinnt?

Gegeben sei ein Listenbereich:

Er wird in eine (intelligente) Tabelle konvertiert:

Natürlich kann ich die Schriftfarbe festlegen (beispielsweise für alle Flüsse aus Asien):

Aber: wer gewinnt, wenn ich eine Hintergrundfarbe hinzufüge?

Wer wohl?

Richtig: die bedingte Formatierung!

Und: was passiert, wenn die Liste verlängert wird? Richtig: dann wird die bedingte Formatierung, wie auch andere Formatierungen, mitgenommen:

Ich empfehle dennoch: entweder intelligente Tabelle OHNE Formatierung oder bedingte Formatierung nur mit Schriftfarbe.

Jedesmal, wenn ich morgens die Augen öffne, denkt der Teufel: „au weia, es ist wach!“

Perfide. Ich habe eine Excelmappe auf der Festplatte – das VBA-Projekt ist mit Kennwort geschützt. Ich kenne das Kennwort. Dennoch: es lässt sich nicht öffnen:

Ich brauche eine ganze Weile, bis es mir dämmert: ich habe die Sprache der Tastatur geändert:

Und richtig: dort sind Y und Z vertauscht und auch die Sonderzeichen sitzen an anderen Positionen …

Meine Laune ist gerade im Keller. Ich hoffe, sie bringt Wein mit.

Hallo Rene

Es ist schon eine Weile her, dass wir Kontakt hatten und ich hoffe es geht Dir in Anbetracht der Weltlage einigermassen gut?

Leider kämpfe ich wieder mit einem Problem, wobei ich mich ehrlich gesagt mehr darüber ärgere, dass ich das Problem nicht finde, als über den Fehler selbst.
Es geht wieder um das VBA Script, welches die Excel Daten auswertet und je nach Zuweisung im Layer meine Shapes sichtbar macht, oder eben nicht.

Das Script macht eigentlich was es soll, doch nach dem Durchlaufen der letzten Zeile, kommt eben der genannte Fehler.
Vielleicht hast Du etwas Zeit und bist willig mir zu helfen? Herzlichen Dank im Voraus für eine Reaktion.

Hallo H.,,

schau mal in deine Daten: der letzte Datensatz ist leer:

Du musst die überprüfen/abfangen, beispielsweise mit:


If IsNull(data(idColumn)) …

Liebe Grüße

Rene

Hast du in den 15 Sekunden, die ich nicht im Raum war, die ganze Schokolade alleine gegessen? – Mach mich nicht für deine Fehler verantwortlich!

Excelschulung. Schwerpunkt: Listen.

Ich erkläre den Nutzen und die Vorteile von (intelligenten) Tabellen. Beispielsweise Diagramme. Setzt man auf eine intelligente Tabelle ein Diagramm auf:

so wird die Erweiterung der Liste sofort ins Diagramm aufgenommen:

Was mich jedoch irritiert: Warum zeigt das Diagramm weder im Diagrammbereich, in den Achsenbeschriftungen noch in den Legendeneinträgen den Namen der Tabelle an – sondern immer noch den Bereich?

Die Pivottabelle zeigt doch auch die „korrekte“ Datenquelle an:

Vom Winterschlaf direkt in die Frühjahrsmüdigkeit. Ich führe ein Leben im Einklang mit der Natur.

Ich will in der Excelschulung demonstrieren, dass Listen eine Überschriftszeile haben sollten; ja – dass Pivottabellen auf Listen aufsetzen, bei denen jede Spalte eine eindeutige Überschrift besitzt.

Ich lösche eine Überschrift heraus:

erstelle eine Pivottabelle, vergesse aber, den Haken beim Datenmodell zu entfernen:

Da die Daten ins Datenmodell geladen werden, muss jede Spalte eine Überschrift haben. Da dies nicht gegeben ist, füllt Excel nicht die leere Überschrift auf, sondern beginnt ab der zweiten Zeile, die als Überschrift verwendet wird:

Eigentlich wollte ich die Fehlermeldung

Der PivotTable-Feldname ist ungültig. Um einen PivotTable-Bericht zu erstellen, müssen Sie Daten verwenden, die einer Liste mit Spaltenüberschriften organisiert sind. Wenn Sie den Namen eines PivotTable-Berichtsfeldes ändern, müssen Sie einen neuen Namen für das Feld eingeben.

zeigen:

Du kochst gut. Sogar der Rauchmelder jubelt dir zu!

Excelschulung. Thema: Listen. Ich beginne mit der Antwort auf die Frage, wie eine Liste in Excel aufgebaut werden soll:

„EINE Überschriftszeile“ deklamiere ich: „EINE, genau EINE – jede Überschrift MUSS eine Überschrift haben.“

Wir ändern die Daten, verschieben, löschen, benennen um, … und: sortieren.

Ein Teilnehmer fragt, warum die Überschrift IN der Liste steht. Meine Antwort: „Weil Sie nicht tun, was ich Ihnen sage!“ Sie haben sicherlich eine Spalte OHNE Überschrift versehen. Machen Sie mal bitte den letzten Schritt zurück!“

„Tatsächlich“, lautet seine Antwort: „eine Spalte hatte keine Überschrift!“

Ich schiebe nach: Wenn Sie unsicher sind, ob Ihre Liste eine Überschrift hat, beziehungsweise die erste Zeile als Überschrift erkennt, dann verwenden Sie die „benutzerdefinierte Sortierung“. Dort ist deutlich zu erkennen: Überschrift oder keine Überschrift; dort kann man auch explizit einschalten: „Bitte mit Überschrift“. Das heißt: die erste Zeile bleibt beim Sortieren bitte oben stehen!

Hast du etwas zum Trinken? – Wasser! – Etwas Härteres? – Eis!

Ich bin verblüfft!

Ich soll – weil Lotus Notes in einer Firma abgeschaltet wird – eine Notes-Datenbank in Access nachbauen.

Access hat weder Kästchen zum Auf- und Zuklappen von Bereichen:

Noch kann man in Access EINZELNE Bereiche in einem Endlosformular dynamisch vergrößern lassen. Letzteres habe ich mit VBA gelöst – die Zeilenzahl ermittelt und dann den Detailbereich – genauer: ALLE Detailbereiche – vergrößert:

Der erste Text:

Der zweite Text – der Detailbereich wird größer:

Der dritte Text – der Detailbereich wird größer:

Und noch Text – der Detailbereich wird kleiner:

Es bereitet Kinder gut auf ihr späteres Leben vor, wenn man ihre Sandburgen mit den Worten „entspricht nicht den deutschen Bauverordnungen“ zerstört.

Etwa zu der Zeit als die Dinosaurier verschwanden, habe ich eine Access-Datenbank erstellt – eine MDB-Datei.

Nun hätte ich gerne die Daten dieser Datei, ich versuche sie zu öffnen – und: padautz: Access kann nicht (mehr). Das ist sehr ärgerlich. Auch mit PowerQuery komme ich nicht mehr an die Daten. Da werden die Dinosaurier wohl sehr traurig werden, wenn sie nie mehr das Licht der Welt erblicken können …

Beim Schnitzel Klopfen sollte man nicht Ramstein hören. Das Schnitzel passt sonst nicht mehr in die Pfanne.

Ich soll eine online-Excel-Schulung über teams halten. 1,5 Stunden (sic!) sind angesetzt. Bevor die Schulung stattfindet, bittet mich die Dame, die diese Schulung organisiert, ob ich – wie immer – einen Screenshot der Teilnehmerinnen und Teilnehmer zu machen. Erwartet werden zirka 150 (sic!) Nasen. Am besten – so schiebt sie nach – wären mehrere Screenshots – zu Beginn, in der Mitte und einer am Ende.

Ich überlege: Ein Screenshot aller Teilnehmerinnen und Teilnehmer – das bedeutet, dass ich mehrere Screenshots machen muss, da ich mit einer Aufnahme nicht alle Namen „einfangen“ kann. Aber wie soll ich, während ich rede und meinen Bildschirm teile, einen Screenshot machen? „Moment mal – ich muss Sie mal kurz abfotografieren?“ – Ein absurdes Vorgehen.

Da fällt mir ein, dass teams eine Option „Anwesenheitsbericht“ anbietet:

Ich probiere NACH der Schulung (?!?) aus und: tatsächlich: ich erhalte einen Bericht und eine Übersicht, wer sich wann angemeldet hat und wer wann gegangen ist. Klasse.

Ob das datenschutzrechtlich in Ordnung ist, sei dahin gestellt … Aber die Dame, die diese Schulung organisierte, war sehr zufrieden.

Voll nett. Eine japanische Familie hat mir heute im Park ihre Spiegelreflexkamera geschenkt. Den Rest habe ich nicht verstanden.

Man schickt mir eine Fehlermeldungmeldung.

Das Arbeitsblatt oder die Arbeitsblätter, die in die Zielarbeitsmappe kopiert oder verschoben werden, besitzen möglicherweise angefügte Makros. Makrocode stellt ein potenzielles Sicherheitsrisiko dar. Sie sollten den Vorgang nur fortsetzen, wenn Sie sicher sind, dass der Makrocode von einer vertrauenswürdigen Quelle stammt. Möchten Sie den Vorgang fortsetzen?

Ich habe keine Ahnung, wo man das in Excel einstellen kann. Ich vermute, diese Option wurde in den Gruppenrichtlinien von der IT abgefangen. Oder? Kennt jemand diese Meldung?

Ich bin immer wieder fasziniert von diesem Tupperwaren-Patent, bei dem der Deckel auf der einen Seite hochspringt, wenn man die andere Seite gerade geschlossen hat.

Wie doof.

Word-Schulung. Online. Über teams.

Ich beginne bei der Mikrotypografie: bedingter Trennstrich, Halbgeviertstrich und natürlich geschütztes Leerzeichen. Ich zeige, dass man ein „Wort“ vor dem Leerzeichen mit dem Wort „hinter“ dem Leerzeichen zusammenhalten kann, indem man die Tastenkombination [Umschalt] + [Strg] + [Leertaste] drückt:

Was passiert? Richtig: die Steuerungsleiste von teams wird aufgerufen:
*ggrrrrrr*

Also gut – dann muss ich dieses Zeichen über Einfügen / Symbol einfügen. Da mir teams die Taste „gestohlen“ hat …

Immer wenn ich sage, „ich bin auch nur ein Mensch“, meldet sich die Waage aus dem Hintergrund und ruft: „Anderthalb“.

Es ist zum Haare-Raufen. Hätte ich welche auf meinem Kopf! Unglaublich! Excel ärgert mich, wo es nur kann. Wenn ich schon einen Fehler haben möchte – was passiert? – Richtig – natürlich kein Fehler! Es ist zum Haare-Raufen!

Was ist geschehen?

Excelschulung. Turboschulung: ich zeige in einer Stunde Listen: sortieren, filtern, intelligente Tabellen, Datenschnitt und Pivottabellen. Eine Teilnehmerin bedankt sich für die Infos zu den Pivottabellen – das hätte ihr sehr weitergeholfen; nun verstehe sie den Gedanken, der dahinter steht. Und: „so schwierig ist das gar nicht“:

Ich wiederhole. „Der Aufbau der Tabelle ist wichtig: Entweder Sie nehmen eine intelligente Tabelle oder Sie achten darauf, dass Ihre Liste keine Leerzeile und keine Leerspalte hat. Und: jede Spalte muss eine Überschrift haben.“

Ich demonstriere es, lösche eine Spaltenüberschrift raus

erstelle eine Pivottabelle – und: es klappt! Excel unterläuft meine Schulung. Jetzt, wo Excel einen Fehler erzeugen sollte tut Excel: NICHTS! Fügt den gelöschten Spaltennamen ein:

Der Gedanke: Klar – eine zweite Pivottabelle wird nicht auf der Liste aufgesetzt, sondern auf dem Pivotcache. Deshalb weiß Excel auch den Namen der fehlenden Spaltenüberschrift. Der Fehler käme beim Aktualisieren zum Tragen.

Oder – damit die Teilnehmerin mir glaubt – ich kopiere die Liste in eine andere Datei, erstelle dort die Pivottabelle und:

HURRA – die Fehlermeldung!

Ich wohne nun seit fast 20 Jahren; aber es hat noch niemand geklingelt, um sich Eier auszuleihen. Ich glaube, ich schmeiße die jetzt weg.

Letzte Woche habe ich eine interessante Frage erhalten. Vielleicht kann man sie mit LAMBDA lösen. Aber da er kein LAMBDA hat, habe ich eine Lösung mit Hilfsspalte gebaut. Etwas Besseres ist mir nicht eingefallen.

Die Fragestellung: in mehreren getrennt nebeneinander stehenden Reihen befinden sich Daten. Gesucht ist der Rang:

Problem 1: Die Funktion RANG setzt einen zusammenhängenden Wertebereich voraus.

Problem 2: diese Funktion ist in keiner anderen Funktion vorhanden – nicht in AGGREGAT oder TEILERGEBNIS, …

Deshalb habe ich es mit einer Hilfsspalte gelöst: Alle Werte untereinander geschrieben, sortiert und über die Funktion VERGLEICH die Position ermittelt. Mir ist nichts Eleganteres eingefallen:

In der Vorlesung ertönt plötzlich das Wort „klausurrelevant“. Alle erwachen aus dem Tiefschlaf, 200 Kulis klicken, 7 Bierflaschen fallen um, in der letzten Reihe wird ein Lagerfeuer ausgetreten.

Ein bisschen kniffelig war es. Ein bisschen probieren musste ich schon.

Die Aufgabe: in einer Zeile soll ein „x“ an einer oder mehreren Positionen stehen. Die erste Position (von links) soll ermittelt werden und aus einer anderen Zeile (der Überschriftszeile), der entsprechende Wert hierzu angezeigt werden.

Den Wert zu finden, ist nicht schwierig:

=VERGLEICH("x";E165:W165;0)

Den zugehörigen Wert zu ermitteln, auch nicht:

=INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0))

Da die leeren Zellen einen Fehler erzeugen würden, kann dieser noch abgefangen werden:

=WENNFEHLER(INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0));"")

Klappt!

Allerdings: In anderen Blöcken werden Zahlen eingetragen. Welche ist die erste Spalte, in der eine Zahl steht?

Die Funktion

=VERGLEICH(">0";E111:S111;0)

versagt jedoch. Allerdings … nach einigen Versuchen:

=VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0)

funktioniert! Und man kann die Überschrift suchen über:

=INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0))

und schließlich:

=WENNFEHLER(INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0));"")

Da diese Formulare dynamisch per VBA erzeugt werden, stellt es nun keine große Herausforderung mehr dar, diese in VBA-Code umzuwandeln:

.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer  & "C23,MATCH(""x"",RC[-20]:RC[-2],0)),"""")"

und analog:

.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(TRUE,INDEX(ISNUMBER(RC[-20]:RC[-2]),0),0)),"""")"

Geh mal zur Seite, Kaffee – das ist ein Fall für Alkohol!

Windows kann auch ganz schön nerven!

Vor Kurzem habe ich auf meinem Laptop auf Windows 11 umgestellt. Der erste Schreck kam in der Form, dass Laufwerk D nicht mehr gefunden wurde. Nicht schön.

Zuerst habe ich in den Diensten gewühlt; anschließend in der Datenträgerverwaltung. Dort wurde ich fündig und habe den „Datenträger neu eingelesen“

Vorgestern habe ich mit Martin geplaudert. Über teams. 13 Uhr haben wir ausgemacht. Um 12.59 Uhr wollte ich den „Raum betreten“ – da schickte er mir eine Mail, wo ich denn bleibe. Ein Witz von mir folgte – „ist doch erst 13.00 Uhr“. „Nein“, war Martins Antwort, „es ist 13.08 Uhr“. Mein Blick auf eine andere Uhr gab ihm recht: meine Laptop-Uhr ging fast zehn Minuten nach. „Werden die Uhren denn nicht automatisch gestellt?“

Nach dem Schwatz machte ich mich auf die Suche. Tatsächlich: durch die Systemumstellung war eingestellt:

„Uhrzeit automatisch festlegen: aus“

Also: Fluchs einschalten – jetzt tickt es bei mir wieder richtig. Zumindest auf meinem Laptop mit Windows 11.

Mein Vater: „oh, ihr habt ja eine Waage im Flur stehen!“ – Was dann geschah, war nicht schön für den Staubsaugerrobotter.

Ich gestehe, dass ich mich manchmal irre. Oder Dinge übersehe. Oder schlicht und einfach nicht weiß. Umso mehr freue ich mich darüber, dass andere meinen Blog mitlesen und mir korrigierende Antworten schreiben. Eine Berichtigung hat mich vorgestern erreicht – ich möchte sie hier gerne wiederholen, weil ich danke, dass sie wichtig ist.

In dem Artikel

habe ich geschrieben, dass die Funktion WURDEAUSGELASSEN nicht das gewünschte Ergebnis liefert.

Sven berichtigt mich und schreibt:

Optionale Parameter in der LAMBDA-Funktion müssen in eckigen Klammern [ ] angegeben werden!

=LAMBDA(Wert1;[Wert2];WENN(WURDEAUSGELASSEN(Wert2);Wert1*1,19;Wert1*Wert2))

=LAMBDA([Bereich];WENN(WURDEAUSGELASSEN(Bereich);“Bitte wählen Sie einen Bereich aus!“;MAX(Bereich)-MIN(Bereich)))

Man beachte [Bereich] als Parameter-Angabe in der LAMBDA-Funktion!

Hat mich auch mehrere Stunden gekostet, bis ich das rausbekommen hatte…

####

Danke für den guten und wichtigen Hinweis!

Das eindeutigste Zeichen dafür, dass es intelligentes Leben im Weltraum gibt ist die Tatsache, dass uns noch niemand kontaktieren wollte.

Solche Meldungen erfreuen immer wieder Sinn und Gemüt:

Das Projekt kann nicht erstellt werden, weil das „Excel Visual Studio-Entwurfszeitadapter-Add-In“ nicht ordnungsgemäß ausgeführt wird. Das Add-In wurde in Excel möglicherweise deaktiviert oder für inaktiv erklärt oder in den Einstellungen im Sicherheitscenter sind alle Add-Ins deaktiviert. Überprüfen Sie den Add-In-Status in den Excel-Optionen. Wenn das Add-In aktiv und aktiviert ist, reparieren Sie Visual Studio Tools for Office, oder führen Sie eine Neuinstallation aus,

Lange, sehr lange sitze ich davor und versuche den Sinn zu begreifen …

Sollte jemals die Polizei meine Wohnung stürmen, könnten sie in keinem Raum „sauber“ sagen.

Die Aufgabe hört sich simpel an – aber ich wüsste keine einfache Lösung.

Frage in einer Excelschulung: „Wie kann ich die Abteilung bequem auswählen?“ Noch bevor ich „Datenüberprüfung“ nachschieben konnte, kam: „ich habe manchmal ein und manchmal mehrere Kriterien.“

Stimmt: mit einer Dropdownliste (Datenüberprüfung) kann nur eine Auswahl getroffen werden. Wenn bereits ein Text in einer Zelle steht, beispielsweise in C6 „Controlling“ kann ich nicht mit einer Formel diesen Text verketten mit einem anderen Text. Das wäre ein Zirkelbezug. VBA und Programmierung schied aus.

Meine Lösung sieht folgendermaßen aus: alle Elemente (hier: Abteilungen) werden aufgelistet. In mehreren Zellen wird eine Einzelauswahl getroffen:

Diese Texte werden verkettet. Wichtig ist der Parameter Leere Zellen ignorieren: WAHR:

=TEXTVERKETTEN(ZEICHEN(10);WAHR;C2:H2)

Und diesen Text könnte man mit Kopieren / Inhalte einfügen in die gewünschte Zelle einfügen.

Die Teilnehmer waren nur mäßig zufrieden. Ich auch. Ich überlege noch nach einer besseren Lösung. Ohne VBA.

Alexa, stell den Wecker auf 4:30 Uhr. – Ich, wenn ich zu Besuch bei jemand bin, der eine Alexa hat.

Hi Rene

Wie geht es dir?

Du, ich muss mich verzweifelt bei dir melden mit einem Excel-Problem. Ich mache einen Import zu WordPress und der Kunde hat mir die Inhalte als Excel geliefert. Es geht um Schadbilder (Gärtner-Themen). Jedes Schadbild wird ein Artikel und sollte deshalb eine Zeile sein. Soweit so gut, jetzt der Kniff: Jeder Text hat Zwischentitel und diese sind aber als Spalten im Excel File angelegt. Also sind die verschiedenen Spalten nicht einzelne Felder in WordPress, sondern ein grosses Textfeld. Und die Spaltentitel sollten jeweils als Zwischentitel in diesen Texten zu finden sein. Die Zwischentitel sollten zudem ein HTML H-Tag erhalten und nicht einfach „fett und grösser“ sein.

Kannst du mir da vielleicht sagen, wie ich weiterkommen kann? Bitte sei ehrlich, wenn das deine Kapazitäten sprengt. Dann machen wir das manuell, das würde auch gehen, es sind um die 140 Artikel.

Ich gestehe – ich habe zuerst überlegt, dieses Problem mit TEXTVERKETTEN zu lösen. Als Trennzeichen hätte ich „</p><p>“ oder Ähnliches eingegeben. Aber irgendwie gefiel mir die Rechnerei nicht.

Warum nicht PowerQuery?

Klar: 1. Schritt: Liste in Tabelle verwandeln. Die Daten aus Tabelle/Bereich importieren:

Das Zauberwort heißt „entpivotieren“. Und schon habe ich eine Tabelle mit zwei Spalten: in der ersten steht die Überschrift, in der zweite die Daten aus den entsprechenden Tabellen:

Und das kann problemlos zu einer Spalte verkettet werden:

"<h1>" & [Attribut] & "</h1>#(lf)<p>" & [Wert] & "<p>"

Die nicht mehr benötigten Spalten werden gelöscht, der Rest in Text konvertiert:

Und zurück nach Excel.

Man hätte die Zeilen in PowerQuery zu einem Wert zusammenfassen können – ich denke, es ist geschickter in Excel mit TEXTVERKETTEN zu erledigen.

Ihre Reaktion:

unglaublich, ich staune! Ich war mir sicher, dass du es kannst, wenn Excel es kann. Aber ich hab schon an Excel gezweifelt.

Danke vielmals!

Lass uns ein Fernglas kaufen! – Und dann? – Und dann sehen wir weiter.

Ein Dankeschön an Martin Weiß. Er hat auf unserem Excelstammtisch sehr schön die Unterschiede zwischen Excel online (sprich: Excel für das Web) und Excel Desktop herausgearbeitet. Ich habe mir noch nie die Mühe gemacht, die Symbole nebeneinander zu stellen. Dabei ist mir aufgefallen, dass sich einige Beschriftungen unterscheiden:

Zellenformatvorlagen und Formatvorlagen:

Bilder und Grafiken, Link und Hyperlink:

Filter und Filtern, Sortieren und benutzerdefinierte Sortierung:

Tabellenansicht und Arbeitsmappenansicht:

Wer findet weitere Unterschiede?

Ich weiß – DAS sind lediglich Marginalien – spannender sind die Unterschiede der Versionen online und Desktop, die Gemeinsamkeiten und die Frage, was beim Datenaustausch passiert.

Wer Antworten auf diese Fragen sucht, wird fündig auf Martins Blog:

https://www.tabellenexperte.de/excel-voellig-kostenlos-wo-gibts-denn-so-was/

Meine Oma ist Griechin. Fetalicherseits.

Hallo René,

jetzt mein kleines Excel-Phänomen, ein etwas interessantem Verhalten von Excel im Bezug auf Leerzellen, die nicht leer sind.

Natürlich habe ich deinen Artikel „Excel und das Nichts“ gelesen, ich denke, das folgende Phänomen geht in diese Richtung.

Bin mal gespannt, ob du das auch schon so gesehen hast.

Wir wollten ein paar ganz simple X-Y-Punktdiagramme erstellen bzw. ein Kollege ruft mich an, „Ich kann kein korrektes Punkt-Diagramm erstellen, das gibt’s doch nicht!“.

Der Datenbereich für die Y-Achse weist einige Leerzellen auf (die sind aber egal), die X-Achse hatte zunächst eine durchgehende Datenreihe, also ohne Lücken.

Dann wollten wir für ein 2. Diagramm eine andere Spalte als X-Achse nehmen (Spalte I). Excel erstellte nun aber nicht ein Punktdiagramm, welches die beiden Datenreihen als X-Y-Kombination zeigte, sondern „zählte“ sozusagen hoch, begann bei 1 und endete bei 54 (Ende der Datenreihe).

Beispiel: 66,67 wäre der eigentlich Wert, es wurde aber die 37 (es war der 37ste Wert in der Spalte) als X-Wert genommen.

Die Ursache war recht schnell gefunden, es gab eine leere Zelle in der Datenreihe, die aber doch nicht ganz leer war: Tippe in Zelle I16 und drücke „entf“ und beobachte, wie sich das Diagramm verändert.

Die Entstehungsgeschichte der Leerzelle, die nicht ganz leer ist, ist auch ganz interessant:

  1. Mittels „Wennfehler(XXX, ““) zu einem „Gänsefüßchen-Leer gemacht
  2. Mittels Powerquery à Aus Datei à Aus Ordner abgerufen und zusammengeführt (PowerQuery zeigt auch nicht „null“ für die Zelle an)
  3. Mittels copy+paste irgendwo anders hin kopiert.

Dominic Dauphin

####

Hübsch! Sehr hübsch!

Hallo Dominic,

das „Hochzählen“ kommt daher, dass Excel keine Werte mehr erkennt, sondern Texte. Wenn du die Werte der x-Achse a, b, c, … nennst, vergibt Excel auch eine fortlaufende Nummer.

Du hast eine Funktion vergessen:

=ISTTEXT

Sie liefert WAHR!

Ich erhalte deine Zelle auch, indem ich in eine Zelle

=““

Schreibe, die Zelle kopiere und dann als Wert einfüge …

Wirklich sehr hübsch!

Liebe Grüße Rene

Zittert ihr Lehrling immer so? – Elektriker: Ist nur ne Phase.

Hallo René,

dachte mir, ich melde mich mal wieder mit einem kleinen Office-Phänomen. Bin gespannt, ob du das schon gesehen hast (in der Regel ist die Antwort ja, aber man weiß ja nie 😉 ).

Ich habe auf einer PowerPoint-Folie ein paar simple Ring-Diagramme und ich möchte keine Führungslinie zur Datenbeschriftung. So sieht die Folie in diesem Moment aus:

Jetzt speichere ich die Datei, schließe sie, und wenn ich sie öffne, dann wandern bei 4 der 6 Diagramme wieder von selbst die schwarzen Führungslinien der Datenbeschriftung hinein.

Ich krieg diese nicht raus.

Versucht habe ich:

  • Rechtsklick auf die Führungslinie, Entf. Gedrückt
  • Datenbeschriftung formatieren -> Häkchen bei Führungslinie entfernen
  • Bauerntrick, Farbe der Führungslinie auf weiß mit 100% Transparenz -> Sie kommt als schwarze Linie wieder
  • Diagramm ohne Linie als Formatvorlage abgespeichert -> Diagrammtyp ändern -> die eben gespeicherte Vorlage ausgewählt
  • Mit VBA und .FullSieriesCollection(1).HasLeaderLines = False

Und jetzt fällt mir nix mehr ein. 🙂

Haste das schon mal gesehen?

Danke dir und viele Grüße, Dominic

####

OK, kurz drauf hab ich dann die Lösung gefunden:

  1. „im Kleinen“: Die Datenbeschriftung mit der Maus soweit nach oben schieben, bis die Linie von selbst verschwindet
  2. „im Größeren“, d.h. es gibt hunderte solcher Folien mit eben diesen Diagrammen:
    mit „.FullSeriesCollection(1).Points(1).DataLabel.Left = XX“ sowie „.Top = “ jeweils eine Position eingeben, bei der die Linien verschwinden und die trotzdem relativ mittig ist. Und das dann eben über zwei For-Each Schleifen für die ganze pptx durchführen.

Trotzdem strange. 😉

Viele Grüße, Dominic

####

Hallo Dominic,

ich muss immer nur ein bisschen warten, dann lösen sich die Probleme von alleine. Beziehungsweise: die Fragenden finden selbst eine Lösung.

Ich glaube, ich habe zu den (heißen die wirklich so?) Führungslinien noch keinen Artikel geschrieben. Und stimmt: DAS ist mir auch aufgefallen, dass diese Teilchen störrisch sind im Sinne von anzeigen und verschwinden. Allerdings: ich verwende sie recht selten und wenn (wie du in Punkt 1) schiebe ich so ein bisschen hin und her bis sie verschwinden. Den Algorithmus – ab wann sie auftauchen und wann nicht, kenne ich nicht.

Wenn ich alle IN EINEM Diagramm (ich weiß, ich weiß, du hast mehrere Diagramme) die Linien ausblenden will, markiere ich sie alle:

und schalte sie über die Eigenschaften aus:

LG :: Rene

Spieglein, Spieglein, … – Geh joggen!

Eine hübsche Frage in der letzten PowerQuery-Schulung. Ich habe einen Moment überlegen müssen.

Die Aufgabe: wir exportieren aus unserer Datenbank regelmäßig eine Liste, die wir weiterverarbeiten müssen. Allerdings benötigen wir nicht alle Spalten. Dummerweise ändern sich die Spaltennamen regelmäßig … Wie kann ich nur die Spalten behalten, die mit „p_“ beginnen?

„Oder“, schob die Kollegin hinterher – alle Spalten, deren Überschrift eine Zahl (oder keine Zahl) enthalten …:

So schwer kann das doch nicht sein, oder? Alle Feldnamen, die mit „p_“ beginnen …:

Ich brauche die Überschrift. Man kann sie „extrahieren“, indem man alle Daten löscht:

Danach die Überschriften als erste Zeile verwendet und diese Zeile vertauscht (transponiert):

Nun kann man die Überschriften mit „p_“ filtern und in eine Liste konvertieren:

Ich nenne diesen Schritt „Selektierte_Ueberschrift“.

Man wäre auch mit einer Zeile M zu diesem Ergebnis gelangt:

Table.ColumnNames
Der Rest ist klar: in Tabelle konvertieren, filtern, in Liste konvertieren.

Und wie verwendet man diese Liste als Filter?

Zunächst benötigt man die Liste. Mit fx kann ein Bezug auf die Tabelle hergestellt werden:

Löscht man nun Spalten, lautet der M-Befehl:

= Table.RemoveColumns(Benutzerdefiniert1,{"p_Gender", "p_Name", "p_StreetAddress"})

Entfernt man andere Spalten, lautet er:

= Table.SelectColumns(Benutzerdefiniert1,{"p_Gender", "p_Name", "p_StreetAddress"})

Und das kann durch den Namen der Liste ersetzt werden:

= Table.SelectColumns(Benutzerdefiniert1,Selektierte_Ueberschrift)

oder analog:


= Table.RemoveColumns(Benutzerdefiniert1,Selektierte_Ueberschrift)

Bleibt noch die Antwort auf die Frage: „und wie entferne ich alle Spalten, die Zahlen (Ziffern) enthalten?“

Ohne M könnte man einen Filter mit zehn Kriterien anlegen:

enthält nicht 1 und enthält nicht 2 und enthält nicht 3 … – ein bisschen Klickarbeit …

Aber durchaus machbar für jede und jeden – auch ohne Programmierkenntnisse. Und mit M? – Nun – die Antwort auf die Frage: „Wie baue ich eine Schleife und lösche alle Zeilen, die nicht 1 und nicht 2 und nicht 3, … enthalten“ überlasse ich der geneigten Leserin und dem geneigten Leser!

Auf alle Fälle waren die Teilnehmerinnen der Schulung zufrieden.

Wann willst du dich deinem Alter entsprechend verhalten? – Sag ich nicht!

Ich wohne in der Albert-Roßhaupter-Straße. Eigentlich müsste sie – nach neuer deutscher Rechtschreibung – Albert-Rosshaupter-Straße heißen. Aber der Name des SPD-Politikers wurde nicht geändert. Sei’s drum. Was passiert allerdings bei der Suche in Excel von Wörtern mit „ß“ und „ss“?

Die Funktion SVERWEIS unterscheidet; XVERWEIS allerdings nicht:

Erstaunlich! Umso erstaunlicher ist es, dass im Spanischen weder zwischen „n“ und „ñ“ unterschieden wird:

Im spanischen Excel werden auch nicht die Vokale mit und ohne Akzent unterschieden:

nicht bei BUSCARV (SVERWEIS) und nicht bei BUSCARX (XVERWEIS). Sehr erstauntlich.

Danke an Mourad Louha für diesen wertvollen Tipp!

Für alle, die nicht wollen, dass Alexa mithört: im Sommer kommt Alex, eine männliche Version; er hört überhaupt nicht zu!

Die Frage ist gut – die Antwort leider nicht befriedigend.

Eine Liste von Namen und eine Liste von Abteilungen.

Soweit so gut – die Zuweisung kann per Datenüberprüfung erfolgen:

Allerdings: eine Person kann für mehrere Abteilungen arbeiten (eine klassische n : m-Beziehung). Die Namen sollen untereinander aufgelistet werden. Also so:

Leider kann man über die Liste der Datenauswahl nur einen Eintrag auswählen und keine Mehrfachselektion vornehmen. Auch mit einer Formel funktioniert es nicht: „addiere zu dem vorhandenen Wert einen weiteren hinzu“ – das wäre ein klassischer Zirkelbezug.

Meine Lösung war folgende: Neben der Liste werden die einzelnen Einträge ausgewählt:

Die ausgewählten Einträge werden darunter zu einer Zeichenkette verkettet:

Die Funktion

=TEXTVERKETTEN(ZEICHEN(10);WAHR;H2:H10)

löst dieses Problem.

Wichtig ist hierbei, dass der Textumbruch eingeschaltet ist, sonst sieht man den Effekt nicht.

Diese Zelle kann nun kopiert und als Wert in eine andere Zelle eingefügt werden.

Zugegeben: Nicht perfekt – aber es funktioniert!

Was für ein Gefühl muss der Tropfen haben, der das Fass zum Überlaufen bringt. (Nikolaus Cybinski)

Die Idee ist gut – sie funktioniert nur leider nicht.

Ein Teilnehmer einer Excelschulung möchte eine fortlaufende Reihe erzeugen. Er möchte, dass „Lücken übersprungen“ werden und dass die Reihe bequem fortgesetzt werden kann.

Kein Problem, oder:

Die Formel

=WENN(B2="";"";MAX($A$1:A1)+1)

hilft hierbei.

Damit unter der Liste neue Daten mit einer fortlaufenden Nummer eingetragen werden können, wandle ich die Liste in eine (intelligente) Tabelle um:

Ein neuer Name:
Lücke und ein weiterer Name:

Klappt.

Wird eine Zeile gelöscht:

funktioniert der Mechanismus hervorragend:

Jedoch: wird eine Zeile eingefügt:

Dann versagt der Mechanismus leider:

Was man feststellen kann, wenn man einen Namen einträgt:

Schade!

Egal wie viele Semester Germanistik du studiert hast – beim Kauf eines Döners sagst du „ohne scharf“! – ist das klar?

Und schon wieder eine Frage zu Outlook:

Hallo Hr. Dr. Martin,

Kann man sich in dieser Tabelle, welche die Suchergebnisse im Kalender zeigt, eine Spalte zum Anzeigen des Antwortstatus darstellen lassen?

Meine Antwort:

schöne Frage, Herr L.,

Sie möchten die Besprechungen von Outlook durchsuchen und von jeder gefundenen Besprechung die Liste der eingeladenen Personen und deren Antwort sich anzeigen lassen?

Sie können sich die Liste der eingeladenen (erforderlichen) Teilnehmer anzeigen lassen und Ihren eigenen Besprechungsstatus; aber ich finde kein Feld für den Antwortstatus der eingeladenen Personen.

Ich sehe schon – bei all diesen Fragen – da fehlt noch viel in Outlook.

In Hosentaschen finde ich oft Geld. Wäre leichter, wenn die Leute beim Kontrollieren stillhalten würden …

Schöne Frage zu Access. Dort kann man Tabellen – auch mit deren Verknüpfungen – leicht in eine XML-Datei exportieren – die Einstellungen sind komplexer als beim Export Excel nach XML:

Allerdings – so lautet seine Frage: wie kann man den Namen des Wurzelelements dataroot ändern?

Er hat recht – ich finde keine Einstellung hierzu. Also doch per Hand oder mit ein paar Zeilen VBA-Code …

Oh, da braut sich was zusammen! – Ein Bier? – Nein, was Böses! – Ein alkoholfreies Bier?

Ich weiß, dass es schwierig ist einen Text von einer Sprache in eine andere zu übersetzen. Auch noch, wenn die Zeit drängt. Dennoch: Hilfetexte sind auch ein Aushängeschild. Für Microsoft. Die ihre Texte automatisch – besser: halbautomatisch – übersetzen. Wohl, ohne dass ein (deutschsprachiger) Mensch darüber schaut. Und so habe ich auch schon einige Male gespottet. Auch Josef reibt sich verwundert die Augen und berichtet:

„Grüß dich Rene!

Gerade hatte ich ein sehr amüsantes Gespräch mit einem Kollegen 🙂

Er wollte eine Excel Formel haben und hat – ganz vorbildlich – die Excel Hilfe konsultiert.  Leider war die wenig hilfreich, also rief er mich an.

Die Formel sollte ein Datum berechnen, ausgehend von einem Startdatum + X Monate.  So weit so gut…

Kollege ganz stolz:  „Guck ich mach genau das, was da steht: EDATE(…“

Ich: Stop!   EDATE?  Das muss EDATUM heißen.  Hast Du etwa eine englische Internetseite gefunden?

Kollege: Neeee! Ich bin doch nicht doof! Ist die deutsche Microsoft Hilfe Seite!

Mit EDATUM(Startdatum;Dauer) hats wunderbar funktioniert.

Erst dachte ich: Naja… maschinell übersetzter Hilfe-Artikel… wird halt der Screenshot englisch sein, schade!   

Aber nein!   Das Ding ist eine fröhliche Mischung aus Englisch und Deutsch 😛

Wir haben herzlich gelacht! 🙂

Vermutlich hat da jemand versucht zu übersetzen, aber dann war plötzlich Zeit für Feierabend…

https://support.microsoft.com/de-de/office/datumswerte-addieren-oder-subtrahieren-b83768f5-f695-4311-98b1-757345f7e926

Im Text steht (mehrfach) EDATE
Die Beispiel-Formel im Text hat
o Deutsche Datumsschreibweise 15.05.19
o und ein Semikolon als Trenner zwischen den Parametern
o aber als Rückgabewert ein Datum in amerikanischer Schreibweise (4/15/19)
Im Screenshot
o Ist die Formel in der Bearbeitungszeile englisch, inklusive Komma als Trenner
o Die Spaltenbeschriftungen und Monatsnamen sind aber deutsch
Unterhalb des Screenshots wirds dann ganz verrückt:
o In Schritt 3 steht „Geben Sie =EDATE(A2;B2) in Zelle C2 ein,… “
Das wird weder in einem deutschen noch in einem amerikanischen Excel funktionieren.
Entweder EDATE und Komma oder EDATUM und Semikolon
In der Excel Hilfe zur EDATUM() Funktion ist es besser. Da steht nur einmal EDATE statt EDATUM in der Überschrift, aber sonst stimmts!
Viele Grüße und bis zum nächsten (online) Excel Stammtisch!
Josef“

Diese Mischung ist auch zu finden bei:

https://support.microsoft.com/de-de/office/edatum-funktion-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5

Danke, Josef, für den Beitrag!

Ich habe eine Doku über die zehn wirksamsten Methoden gesehen, wie man sich vor einem Hai-Angriff schützt. Allerdings: „Bleiben Sie einfach am Ufer“ war nicht dabei …

Outlook-Schulung.

Sehr schöne Frage: wie kann ich die bedingte Formatierung so verwenden, dass Mails, die aus meiner Firma kommen, in einer Farbe dargestellt werden, Mails von extern in einer anderen Farbe.

Ich wüsste nicht, wie man das einstellen kann. Über die bedingte Formatierung kann ich nur eine Person (oder mehrere Personen) einstellen; jedoch nicht „intern“ und „extern“.

Schade, denn beim Abwesenheitsassistenten habe ich diese Möglichkeit:

Outlook ist leider (auch hier) nicht konsistent.

Nachtrag: meine Kollegin Angelika Meyer hat die Idee, dies mit einer Regel zu überprüfen. Immerhin, man kann dort alle Mails, deren Absender in „meinem“ Firmenadressbuch gefunden werden, mit einer Kategorie belegt werden und damit mit einer Farbe versehen werden:

Und die anderen?

Als ich jung war, waren Singles aus Vinyl und nicht aus Verzweiflung.

Excel-VBA-Schulung. Wir programmieren eine eigene Funktion. Als Übung gebe ich auf eine Funktion zu schreiben, welche die Hypotenuse in einem rechtwinkligen Dreieck berechnet.

Also:

Ich tippe die Lösung, die ich mit einem „Quick & Dirty“ kommentiere:

Ich verlasse die Codezeile:

Ein Fehler ist die Folge. Darf man nicht die Funktion Sqr (Wurzel) aufrufen und im Aufruf rechnen?

Okay – dann „sauberer“ in zwei Zeilen:

Schon wieder ist die Zeile
    c = a^2 + b^2

in der Funktion

Function Hypotenuse(a As Double, b As Double) As Double
    Dim c As Double
    c = a^2 + b^2
    Hypotenuse = Sqr(c)
End Function

falsch. Es dämmert mir. Richtig: das Caret-Zeichen (^) – der Zirkumflex – darf nicht direkt hinter die Variable geschrieben werden, da a^ zum Erstellen von Long Long-Datentypen in einer 64-Bit-Umgebung verwendet wird. Man muss ein Leerzeichen zwischen Variable und dem „Dach“ ein Leerzeichen schreiben:

Also:

Function Hypotenuse(a As Double, b As Double) As Double
    Dim c As Double
    c = a ^ 2 + b ^ 2
    Hypotenuse = Sqr(c)
End Function

oder in der Kurzform:

Function Hypotenuse(a As Double, b As Double) As Double
    Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End Function

Das funktioniert!

Ich habe nun Michael Wendler als Weckton. Jetzt wache ich immer fünf Minuten früher auf, damit ich mir das nicht anhören muss.

Oooch, Leute – versteckt doch die Sachen nicht so gut! Sonst muss ich so lange suchen.

Ich bespreche mit den Mitarbeiterinnen und Mitarbeitern einer Firma eine große Excel-Formel und deren Anpassungen. Die Formel liefert entweder „nicht erfüllt“ (und zeigt Schriftfarbe und Hintergrund Rot) oder „erfüllt“ in grüner Farbe:

Ich schaue die Formel genauer an: Die Formel liefert 0 (das entspricht FALSCH):

oder 1 (also WAHR):

Angezeigt wird aber Text und Farbe. Um die Funktion herum wurde keine WENN-Funktion gebaut, welche die Texte anzeigt. Die Farbe entstammt sicherlich der bedingten Formatierung:

Die Registerkarte „Ausfüllen“:

Die Registerkarte „Schrift“:

Die Registerkarte „Zahlen“:

Ich bin erstaunt. Ich hätte ein benutzerdefiniertes Zahlenformat erwartet, das hier verwendet wurde. Woher kommt nun der Text?

Ich habe eine Weile überlegt. Dann fiel es mir ein: man kann Text als benutzerdefiniertes Zahlenformat im allgemeinen Zahlenformat einstellen. Heureka – und DAS ist des Rätsels Lösung:

Das Zahlenformat lautet:

[=1]"erfüllt";[=0]"nicht erfüllt"

Mein Appell lautet: Versteckt die Sachen doch nicht so gut! Andere Menschen finden Sie nicht mehr! Oder suchen sehr lange.

Wenn man das Müsli mit Eierlikör anrührt, sieht der Tag gleich viel freundlicher aus.

„Guten Morgen,

ich versuche gerade eine PowerQuery-Auswertung aus den Interviewfragebogen zu erstellen.

Ich erhalte allerdings die Fehlermeldung „Die Konvertierung in Number war nicht möglich.

Was mache ich da falsch?“

Was mache ich mit so einer Mail? Richtig: ich schlage vor, mir das Ganze über teams anzusehen. Und tatsächlich:

Okay. Langsam. Von vorne bitte. Können wir uns das Ganze mal bitte in Ruhe ansehen? Was machen Sie?

In einem Ordner befinden sich mehr als 50 Excelmappen:

Jede dieser Mappen hat folgenden Aufbau:

In Spalte A befindet sich in jedem Formular eine Nummer der Form 0., 1., 2., …

Aus einigen dieser Gruppen sollen Informationen ausgelesen werden. Diese Informationen befinden sich in Spalten rechts daneben. Soweit so gut – PowerQuery ist das richtige Werkzeug hierfür. Wir schauen uns das Ganze an – Schritt für Schritt:

  1. Schritt: Leere Arbeitsmappe. Daten / Daten abrufen und transformieren / Daten abrufen / Aus Datei / Aus Ordner

2. Schritt. Der Ordner wird ausgewählt; die Daten werden transformiert.

3. Schritt: Unterordner werden ausgeschlossen; andere Dateitypen ebenso:

4. Schritt: In der Spalte „Content“ befindet sich der Inhalt. Da die Spalten alle den gleichen Aufbau haben, kann man die anderen Spalten löschen und diese Spalte „entpacken“:

Da alle Dateien den gleichen Aufbau und das gleiche Tabellenblatt haben, stellt dies kein Problem dar:

Das Ergebnis:

Da Informationen aus bestimmten „Gruppen“ geholt werden, wird die erste Spalte über Transformieren / Ausfüllen „nach unten gezogen“:

Einige Spalten werden gelöscht. Aus der ersten Spalte werden einige der benötigten Spalten selektiert:

Das Ergebnis wird zurück nach Excel geschrieben (Start / Schließen & Laden / Schießen & Laden in). Obwohl die Daten in Powerquery korrekt angezeigt werden:

ist die Fehlermeldung die Folge:

[DataFormat.Error]. Die Konvertierung in „Number“ war nicht möglich.

Ich stutze. Zurück zu PowerQuery. Vielleicht ist „irgend etwas“ in der ersten Spalte?!? Es sieht nicht so aus:

Aber: „Die Liste kann unvollständig sein.“ Ich lasse mir über Ansicht die „Spaltenqualität“ anzeigen:

Kein Fehler in der ersten Spalte!?!

Wirklich nicht?

Wir wissen, dass PowerQuery zu Beginn nur 1.000 Zeilen auswertet. Bei 50 Formularen x zirka 150 Zeilen sind das 7.500 Zeilen. Okay – ich lasse ALLE Zeilen auswerten, indem ich auf der Statuszeile von 1.000 auf „alle“ wechsle:

Und tatsächlich: JETZT lautet die Beschriftung der Zeile „Spaltenqualität“

Unerwarteter Fehler.

Aha!

Ich gehe auf die Suche – Schritt für Schritt zurück. Schon bald ist klar, dass die Häufigkeit der Fehler unter 1% liegt:

Der Fehler tritt auf, als der Typ geändert wird. Moment – DAS habe ich doch gar nicht gemacht:

Richtig: in Datei / Optionen und Einstellungen / Abfrageoptionen lautet die Grundeinstellung:

Spaltentypen und -überschriften für unstrukturierte Quellen immer erkennen. Und richtig: Das produziert den Fehler:

[DataFormat.Error]

Aha – diese Einstellung bewirkt, dass aus 0., 1., 2., … die Zahlen 1, 2, 3, … werden. Das heißt: in einer der Dateien befindet sich wahrscheinlich in Spalte A eine andere Informationen.

Welche Datei? Zurück zum Anfang:

Ich entferne die erste und die zweite Spalte (den Dateinamen) nicht:

Bevor der Datentyp geändert wird, lasse ich mir alle Inhalte anzeigen:

und stelle fest, dass in einer (oder mehreren) Zellen ein Punkt vorhanden ist:

Da ich die Dateinamen „sehe“, kann ich die Spalte in den Datentyp „Text“ konvertieren und den Übeltäter filtern:

Als Text erzeugt der Punkt kein Problem, allerdings bei der (automatischen) Umwandlung in Zahl.

Die Lösung liegt auf der Hand: entweder man löscht den Punkt in PowerQuery raus oder man geht auf die Suche in der Datei:

Und dann funktioniert die Zusammenfassung problemlos:

Fazit: Vermeiden Sie – wenn möglich – die automatische Datenkonvertierung.

Verwenden Sie ALLE Daten bei der Fehlersuche.

Verwenden Sie die Werkzeuge der Registerkarte Ansicht, also: Spaltenqualität, Spaltenprofil und Spaltenverteilung.

Mit nur 12% Akku das Haus verlassen – man muss auch echt mal was riskieren im Leben.

Fürchterlich!

In einer gespeicherten Datei befindet sich eine Liste von Daten. Der Bereich wurde „Quellnymphen“ genannt.

Auf einem zweiten Tabellenblatt befinden sich Dropdownlisten (Datenüberprüfungen), welche auf die Liste über den Namen zugreifen:

Ich möchte nun beide Tabellenblätter in eine neue, schon gespeicherte (!) Datei kopieren. Da auf beiden Blättern sich eine intelligente Tabelle befindet, kann ich nicht beide Blätter markieren und kopieren:

Also einzeln. Zuerst das Blatt mit den Datenüberprüfungen und anschließend das Tabellenblatt mit den Quelldaten. Die Datenüberprüfung funktioniert und greift auf die Liste zu, die hinter den Namen liegt:

Schließt man allerdings die Quelldatei, wird die Datenüberprüfung noch angezeigt – ja – sie greift sogar noch auf die Namensliste zu:

jedoch: sie lässt sich nicht mehr öffnen!

Okay – noch einmal:

Ich kopiere erneut das Datenblatt in die andere, bereits gespeicherte Datei, anschließend das Blatt mit der Datenüberprüfung:

Das Ergebnis ist das Gleiche.

Der Namensmanager gibt Auskunft. Dadurch, dass zwei Blätter mit Namen (eines besitzt einen Namen, eines verwendet einen Namen) kopiert werden, wird nun zwei Mal ein Name angelegt: ein lokaler, der auf die andere Datei zugreift (?!?) und ein globaler, der aber nicht von der Datenüberprüfung verwendet wird.

Uff!

Ähnlich perfide gestaltet sich das Ganze, wenn Quelldatei und Zieldatei im gleichen Ordner liegen. Kopiert man die Tabellenblätter hinüber, speichert beide Dateien, schließt sie und öffnet die Zieldatei, lässt sich – wie oben beschrieben – die Liste der Datenüberprüfungen nicht mehr öffnen. Schließt man die Zieldatei erneut und löscht die Quelldatei, ist eine Meldung nach der verknüpften Datei die Folge:

Das bedeutet: Das Kopieren von Blättern wird hinfällig, wenn Bezüge auf Namen vorhanden sind!

Gestern habe ich alkoholfreies Bier gekauft und mit Karte bezahlt. Heute ruft mich die Bank an und fragt, ob meine Karte gestohlen wurde …

Excel-VBA-Schulung. Wir üben das Programmieren von eigenen Funktionen (also function), die in Excel verwendet werden sollen. Ich zeige einen Fehler:

Und erkläre, dass man die Ursache gut finden kann, indem man einen Haltepunkt in Excel setzt:

Dann muss man die Funktion editieren (doppelklick oder [F2]) und sie wird erneut aufgerufen und berechnet:

Allerdings: nichts passiert. Ich brauche eine Weile, bis ich verstehe. Die Parameter sind vom Typ Double deklariert. Einer der Eingabewerte ist jedoch keine Zahl:

Und so wird die Funktion schon direkt nach dem Aufruf abgebrochen und liefert die Fehlermeldung #WERT, ohne dass die Zeile mit dem Haltepunkt erreicht wird. Also flugs den Wert der Zelle in eine Zahl ändern und schon wird der Haltepunkt erreicht.

Die Krankheit, die alles Essbare in Plastikdosen packt, nennt man Tupperkulose.

Ist das ein Bug? Was passiert denn da gerade?

Ich habe ein mächtiges Werkzeug mit Excel VBA erstellt, in welchem unter anderem Dateien geöffnet werden, Informationen ausgelesen und in die eigene Datei geschrieben werden. So nach dem Motto:

Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open("C:\BIA_contoso_IT.xlsm")
' -- tue etwas
xlDatei.Close SaveChanges:=False
Set xlDatei = Nothing

Ich teste mehrere Male.

Mit Erstaunen stelle ich im Projekt-Explorer fest, dass die Datei mehrmals geöffnet ist:

Ein Blick in Excel unter Ansicht / Fenster wechseln kann dies nicht bestätigen. Und schließlich: eine Datei mit einem bestimmten Namen kann in Excel nur ein Mal geöffnet sein.

Beim sechsten Test erhalte ich folgende wunderliche Meldung:

Ich beende das Programm Excel, öffne – der Spuk ist verschwunden. Ich starte das Programm – die verwunderliche Meldung kommt erneut. Die Ursache ist gefunden: Excel behauptet plötzlich, dass das Tabellenblatt Nummer 3 nicht vorhanden ist!?! In der Datei befinden sich zirka 34 Tabellenblätter, die ich alle „prüfe“.

Automatisierungsfehler: Ungültige Vorreferenz oder Referenz zu unkompiliertem Typ

Ich verstehe es gerade nicht. Habe ich – in meiner aktuellen Version 2201 ein neues Feature entdeckt?

Magst du das Kribbeln im Nacken? – Ja – Gut, dann lasse ich die Spinne da. *wie ich zu meinem Tinnitus kam*

Vorgestern haben wir auf dem Excelstammtisch über folgendes Phänomen diskutiert. Trägt man in Excel längeren Text (mit Textumbruch) ein:

ist der Zeilenumbruch beim Ausdruck anders:

Auch das Verwenden eines anderen Druckers hilft nicht:

* Keiner kennt ein Tool/Add-In/Workaround, mit dem das lösbar wäre.

* Christian empfiehlt einen Blick auf:

Excel Print Preview not matching the actual printed document – Microsoft Tech Community

* Ebenso kann es helfen in Datei / Optionen / Erweitert in der Gruppe „Allgemein“ die Option „Inhalt für die Papierformate skalieren“ zu deaktivieren:

* Josef hat festgestellt, dass bei Nichtproportionalschriften (bspw. Courier) der Umbruch häufig bestehen bleibt. Das heißt: diese merkwürdige Umbruch-Geschichte muss etwas mit den Schriften zu tun haben. Vielleicht gibt es andere Schriftfamilien (statt TrueType besser OpenType oder echte Druckerschriften …), die gegen die falschen Umbrüche resistent sind.

Vielleicht – so kam der Vorschlag – sollte man die Wingdings verwenden. Dann kann man zwar nichts mehr lesen, aber dann spielt der Umbruch auch keine Rolle mehr.

Und schließlich erreichte mich noch folgende Mail von Josef:

„Mir ist heute morgen nochmal die Test-Mappe in die Hände gefallen mit den abweichenden Zeilenumbrüchen – hab die wohl nicht zugemacht gestern Abend… und prompt ist die Neugier wach geworden… 🙂

Schaut mal, was ich gefunden hab:   https://support.microsoft.com/de-de/topic/the-column-width-is-not-the-same-when-printed-in-excel-9756db9b-ba72-e6b8-7d94-db84e148dd85

Microsoft scheint das Problem schon ne ganze Weile zu kennen. Schuld ist angeblich Windows, nicht Excel.   Auch Programme haben wohl Kommunikationsprobleme.

Den Workaround mit der Zellformatvorlage „Standard“ habe ich ausprobiert, war aber nicht wirklich erfolgreich.   Der Text sah zwar anders aus (logisch), die Zeilenumbrüche waren auch anders, aber immer noch falsch… 😛

Geschirrspülmaschinen sind super – bis man sie ausräumen muss!

Was habe ich nur angerichtet?

In einer Excelschulung wollte ich zeigen, dass zwei gesetzte Filter einem logischen UND entsprechen und somit eine Schnittmenge darstellen:

Ich visualisiere das mit zwei Ellipsen:

Und wollte die Schnittmenge deutlich hervorheben, indem ich beiden Formen kombinieren. Aber das Zusammenführen der Formen ist in Excel leider nicht möglich! Ich habe darüber geschrieben:

https://www.excel-nervt.de/eines-muss-ich-meiner-muedigkeit-ja-lassen-kondition-hat-sie-ja/

Damit habe ich eine kleine Diskussion ausgelöst.

Ernst hat sich nun die Frage gestellt, ob man so ein Problem nicht mit VBA lösen kann. Konkret: ob man die Formen nicht nach PowerPoint kopieren kann, dort zusammenführen kann und anschließend wieder zurück kopieren kann. Die Antwort lautet: „ja“. Allerdings: einige Dinge gibt es hierbei zu beachten:

Im ersten Schritt wird geprüft, ob mindestens zwei Formen markiert wurden.

Anschließend wird ein Auswahldialog aufgerufen:

Er prüft, ob PowerPoint bereits geöffnet ist:

On Error GoTo Fehler
Set PP_app = GetObject(Class:="PowerPoint.Application")
PP_app.Visible = True                                   'Stellt die Sichtbarkeit auf an.
PPObjektaufrufen = True                                 'und setzt die Funktionsrückmeldung auf True.
Exit Function                                           'Funktion wird verlassen.
Fehler:
PPObjektaufrufen = False 'Die Funktionsrückmeldung wird auf False gesetzt.

Falls nicht, wird es geöffnet:

Set PP_app = CreateObject(Class:="Powerpoint.Application")
PP_app.Visible = True                                       'Stellt die Sichtbarkeit auf an.

Wichtig hierbei ist, dass PowerPoint sichtbar ist!

Die Formen werden kopiert und eingefügt:

Selection.Copy                                                      'Die selektierten Formen werden in die Zwischenablage kopiert.
Application.ActiveWindow.Selection.ShapeRange(1).Name = OriginalName 'Danach wird der Originalname der erstselektierten Form wieder hergestellt
PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank 'Es wird eine neue Präsentation mit einer leeren Folie erstellt.
                                                                    'Die Slide.Add-Methode ist eine verborgene PowerPoint-Methode
PP_app.ActivePresentation.Slides(1).Shapes.Paste                    'Die Formen werden aus der Zwischenablage in die PP-Präsentation eingefügt.
PP_app.ActivePresentation.Slides(1).Shapes.SelectAll                'und selektiert.

Dabei war auch die Zeile

PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank

wichtig – die Methode Add der Sammlung Slides ist dies ein verborgenes Element: Diese werden von Intellisense nur dann angezeigt, wenn die Option „verborgene Elemente anzeigen“ bestätigt wurde. Ich habe einmal darüber geschrieben:

https://www.excel-nervt.de/was-war-das-fuer-ein-krach-heute-nacht-die-schuhe-sind-umgefallen-bitte-ich-stand-noch-drin/

Wichtig ist hierbei herauszufinden, welches die zentrale Form ist, also die Form, von welcher die anderen die Formatierung erben:

'PrimaryShape:=  Die Form, von der die resultierende Form ihre Formatierung erbt.
'Wenn Fehler auftritt, wird dieser abgefangen
On Error GoTo Fehler1
PP_app.ActiveWindow.Selection.ShapeRange.MergeShapes MergeCmd:=Formform, _
    PrimaryShape:=PP_app.ActiveWindow.Selection.ShapeRange("PrimaerForm")
'Fehlerroutine zurückstellen auf Fehler allgemein
On Error GoTo FehlerAll

PP_app.ActivePresentation.Slides(1).Shapes.SelectAll            'die neu erstandene Form wird selektiert.

'Wurde eine Form selektiert, wird diese nach Excel übertragen. Es könnte, beispielsweise bei der Aktion
'Schnittmenge bilden, der Fall auftreten, dass keine Form erzeugt wird.

Das Ergebnis wird zurück nach Excel kopiert:

'Wieviel Formen sind selektiert?
AnzahlFormen = 0
AnzahlFormen = PP_app.ActiveWindow.Selection.ShapeRange.Count   'Anzahl selektierte Formen
If AnzahlFormen > 0 Then                                        'Ist keine Form selektiert erfolgt keine Aktion.
    PP_app.ActiveWindow.Selection.Cut                           'Ansonsten werden die selektierten Formen ausgeschnitten und in die Zwischenablage kopiert.
    If Not MergeFehler Then ActiveSheet.Paste                   'und werden dann aus der Zwischenablage nach Excel kopiert.
End If

PowerPoint wird geschlossen:

If PPschonGestartet Then                'Wurde keine neue PP-Instanz gestartet.
    PP_app.ActivePresentation.Close     'wird nur die neu erzeugte PP-Präsentation geschlossen
   Else
    If NeuePPwiederSchliessen Then
       PP_app.Quit                      'Steht dieser Schalter auf True wird PP wieder geschlossen.
    End If
End If
Set PP_app = Nothing                ' Objektvariable wird auf Nothing gesetzt
Exit Sub

Ein beachtliches Werk mit vielen Fallstricken – vielen Dank an Ernst Börgener.

Wer möchte, kann sich das Beispiel herunterladen:

https://www.excel-nervt.de/wp-content/uploads/2022/01/Formenvereinigen.xlsm

Der Autor freut sich über Lob, Kritik und Anmerkungen!

Als Kind hatte ich kein Smartphone oder Tablett. Ich habe die Cornflakes-Packung beim Frühstück gelesen.

Hallo Herr Martin

Ich hoffe Sie sind gut ins neue Jahr gekommen.

Wir hatten schon einmal kontakt aufgenommen.

Folgende  Meldungen bekomme:

Nach dem Debuggen bekomme ich diese Meldung:


Der Urheber behauptet dass das korrekt ist und keinen Fehler darstellt.
 
Ich würde mich herzlich freuen, wenn wir  kurz miteinander sprechen können.
 
Schon jetzt herzlichen Dank.
 
Mit freundlichen Grüßen

####

Hallo Herr P.,

welchen Wert hat denn „MenueName“? Wenn Sie mit der Maus über diesen Variablennamen beim Debuggen fahren – was wird denn angezeigt? Und was bei „SubMenueName“?

Umgekehrt: Arbeiten Sie noch mit Menüs? Die sind doch seit Excel 2007 verschwunden? Ich verwende seit vielen Jahres das Ribbon und füge dort Symbole ein.

Liebe Grüße Rene Martin

####

Herr Martin,

erstmal Danke für Ihre Rückmeldung,

was Sie schreiben sind für mich Böhmische Dörfer.

Sorry, dass sind zu hohe Ansprüche für mich.

Ich habe Ihnen die VBA kopiert.

‚~~Begin~~#################################################################################

Sub Menue_Erstellen_ZV()

    Dim MB As Object, MWMMenue As Object, Befehl As Object

    Set MB = CommandBars.ActiveMenuBar

    If (Menüpunkt_vorhanden(MenueName)) Then

        If (Menüeintrag_vorhanden(MenueName, SubMenueName)) Then

            Menueeintrag_loeschen_ZV

        End If

        Set MWMMenue = MB.Controls(MenueName)

        Set Befehl = MWMMenue.Controls.Add(Type:=msoControlButton, ID:=1)

        With Befehl

            .Caption = SubMenueName

            .OnAction = „ZV_ZellenVerbinden“

        End With

    Else

        Set MWMMenue = MB.Controls.Add(Type:=msoControlPopup, Temporary:=True)

        MWMMenue.Caption = MenueName

        Set Befehl = MWMMenue.Controls.Add(Type:=msoControlButton, ID:=1)

        With Befehl

            .Caption = SubMenueName

            .OnAction = „ZV_ZellenVerbinden“

        End With

    End If

End Sub

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

Sub Menue_Loeschen_ZV()

    On Error Resume Next

    CommandBars.ActiveMenuBar.Controls(MenueName).Delete

End Sub

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

Function Menüpunkt_vorhanden(Bezeichnung) As Boolean

    Menüpunkt_vorhanden = False

    Dim MNU As CommandBarControl

    For Each MNU In Application.CommandBars _

      („Worksheet Menu Bar“).Controls

        If UCase(MNU.Caption) = UCase(Bezeichnung) Then

            Menüpunkt_vorhanden = True

            Exit Function

        End If

    Next MNU

End Function

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

Function Menüeintrag_vorhanden(SubMenü, Bezeichnung) As Boolean

    Menüeintrag_vorhanden = False

    Dim MNU As CommandBarControl

    For Each MNU In Application.CommandBars _

      („Worksheet Menu Bar“).Controls(SubMenü).Controls

        If UCase(MNU.Caption) = UCase(Bezeichnung) Then

            Menüeintrag_vorhanden = True

            Exit Function

        End If

    Next MNU

End Function

‚~~~END~~~#################################################################################

‚~~Begin~~#################################################################################

‚Löscht den Eintrag SubMenueName im Menü „MWM“

Sub Menueeintrag_loeschen_ZV()

     CommandBars(„Worksheet Menu Bar“). _

       Controls(MenueName).Controls(SubMenueName).Delete

End Sub

‚~~~END~~~#################################################################################

Schon jetzt herzlichen Dank für Ihre Rückmeldung.

####

Hallo Herr P.,

danke für den Code. Mir fehlt allerdings immer noch ein Befehl: ein anderes Makro ruft dieses Makros auf und löscht das Symbol in der Symbolleiste. Ich weiß nicht welches, weil ich diese Variable nicht „sehe“.

Im Makro

Sub Menue_Loeschen_ZV()

lautet die erste Zeile

On Error Resume Next

also auf Deutsch: sollte ich – das Makro – das Symbol nicht löschen können (Controls(MenueName).Delete) – beispielsweise weil es schon gelöscht wurde … na: dann mach halt gar nichts! Auch keine Fehlermeldung! Ist völlig okay so!

Jedoch im Makro

Sub Menueeintrag_loeschen_ZV()

fehlt diese Zeile.

Tipp: Fügen sie nach der Sub-Zeile einfach die On-Error-Zeile ein (wurde wahrscheinlich vergessen), also so:

Sub Menueeintrag_loeschen_ZV()

On Error Resume Next

CommandBars(„Worksheet Menu Bar“). _

       Controls(MenueName).Controls(SubMenueName).Delete

Probieren Sie es mal.

Klappt das?

Liebe Grüße

Rene Martin

Wenn ich das Wäscheaufkommen hier in diesem Haushalt sehe, muss ich stark davon ausgehen, dass hier Leute wohnen, die ich noch nie gesehen habe.

LAMBDA zu testen ist sehr, sehr mühsam. DIe Funktion rechnet korrekt, liefert aber im Funktionsassistenten:

Svetlana Cheusheva schreibt auf ihrer Seite

https://www.ablebits.com/office-addins-blog/2021/06/16/write-recursive-lambda-function-excel/

„Sadly, there is no way to test it at this point, and we can only rely on the results of the previous tests and do debugging later if needed.“

Auch Mourad Louha schreibt:

„Ich bin wirklich sehr gespannt, was das Excel-Team in den kommenden Wochen und Monaten an Verbesserungen zu den LAMBDA-Funktionen zur Verfügung stellen wird.“

http://www.excel-ticker.de/die-lambda-funktion-in-excel/

Da es hier offenbar nur um dummes Zeugs geht – nun mal was Ernsthaftes: weiß jemand, was eine Giraffe kostet?

Wie weit ist es von München nach Moskau? Und nach Madrid?

Da ich nächste Woche einen Vortrag über die neuen LAMBDA-Funktionen NACHZEILE, MATRIXERSTELLEN, WURDEAUSGELASSEN, REDUCE, .. halte, probiere ich ein wenig. Und habe folgendes interessantes Beispiel gefunden.

Die Koordinaten von München (beispielsweise Marienplatz) sind

Lat: 48,1371079 und Lon: 11,5753822.

Die vom Roten Platz in Moskau lauten

Lat: 55,7536283 und Lon: 37,6213796006738

Das kann man beispielsweise über

https://www.koordinatengps.de/

herausfinden. Die Entfernung zweier Punkte kann man nicht mit dem Satz des Pythagoras berechnen, sondern mit Hilfe von sphärischer Trigonometrie. Ein Blick in die Formelsammlung oder ins Internet liefert die Lösung:

Entfernung = 6378,388 * acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1))

Da Sinus und Cosiuns von einer Einheitskugel ausgehen, muss das Ergebnis mit dem Radius der Erde (ungefähr 6.380 km) multipliziert werden. Und da Excel mit der Funktion BOGENMASS diese Angaben in GRAD umrechnet, lautet die Formel:

= 6378*ARCCOS(SIN(BOGENMASS(B2))*SIN(BOGENMASS(B3))+COS(BOGENMASS(B2))*COS(BOGENMASS(B3))*COS(BOGENMASS(C3-C2)))

Das kann man doch sicherlich mit den neuen Arrayfunktionen, beispielsweise mit LAMBDA und REDUCE abkürzen. Da zwei Mal der COSINUS verwendet wird und ein drittes Mal der Cosinus einer Differenz, ermittle ich die Differenz unterhalb der Daten:

Und berechne nun:

= 6378*ARCCOS(REDUCE(1;B2:B3;LAMBDA(a;b;a*SIN(BOGENMASS(b))))+REDUCE(1;B2:B4;LAMBDA(a;b;a*COS(BOGENMASS(b)))))

Die Formel ist etwas kürzer als die erste:

Okay – München – Moskau sind 1.962 km Luftlinie. Und nach Madrid? Ich hole die Daten aus:

https://www.koordinatengps.de/

Ich markiere, kopiere, füge ein und:

Und trage es in meine Formel ein und …

… erhalte einen Fehlerwert. Die Ursache ist schnell gefunden. Ich muss nicht nur das Dezimaltrennzeichen von Punkt in Komma ändern, sondern auch die (unsichtbaren) Leerzeichen, die auf der Homepage vor den Zahlen eingetragen waren, entfernen. Dann klappt es:

Dann funktioniert es. Nach Madrid sind es von München aus „nur“ 1.486 km – ist also näher als Moskau.

Mit einem Maulwurf kann man nicht über das Universum reden

Seltsam. Manchmal – aber nur manchmal verschwindet das Kästchen zum Herunterziehen, wenn eine Zelle markiert ist:

Immerhin: es erscheint sofort wieder, wenn ich auf eine andere Zelle klicke.

Nachvollziehen kann ich auch nicht folgendes Phänomen: Trage ich in eine Zelle eine Formel ein, wird die Formel grau hinterlegt in der Zelle angezeigt:

Auch dieser Spuk verschwindet bald wieder. Seltsam …

Ich überlege mit dem Trinken aufzuhören, aber ich schwanke noch.

Hallo Rene,

Dir zum Jahresanfang alles Gute – bleib gesund und für die Arbeit (notwendiges Kleingeld) viel Erfolg!!!


…und ich stelle mich im Neuen Jahr ganz schön doof an.
So würde ich gern Deine Hilfe annehmen.

In meinen Programmen will ich etwas Neues probieren und es gelingt mir nicht.

Bisher funktioniert alles prima […] Alles ist gut!

Nun:
Mein Wunsch wäre eine etwas komplexere Auswertung, die ich vorher erstellt habe und der in einer Datei Auswertung.xlsx liegt.
Diese Arbeitsmappe kann ich nun per Programm über
      Application.Workbooks.Open „C:\Pfad\Auswertung.xlsx“
öffnen – das funktioniert.

Aber jetzt:
Jetzt möchte ich diese Mappe für die weitere Benutzung zuweisen (ich will diese Mappe ja per Programm bearbeiten)
       Set xlsDatei(i) = ??????
und da geht es nicht weiter…

Es ist bestimmt nur eine Kleinigkeit, aber ich habe mich irgendwie festgebissen…

Ich danke Dir schon im voraus für Deine Hilfe!

Liebe Grüße
Wolfgang

####

Äh ….

    Set xlsDatei(i) = Application.Workbooks.Open(„C:\Pfad\Auswertung.xlsx“)

Moin Wolfgang,

DAS erschüttert mich! Das weißt du doch selbst: Methoden haben in VBA zwei Schreibweisen: Leerzeichen, wenn etwas ausgeführt wird (zöffne die Datei) und Klammer, wenn etwas an eine Variable übergeben wird (… und speichere es als xlsDatei).

Liebe Grüße und einen guten Jahresanfang!

Rene


Fazit des letzten Jahres: 27 Aufrisse; keiner davon die Nacht mit mir verbracht: davon Chipstüten: 27

Da muss Microsoft wohl noch einmal ran. Ich versuche mich an der neuen Funktion

WURDEAUSGELASSEN

Ich erstelle die Funktion

=LAMBDA(Bereich;MAX(Bereich)-MIN(Bereich))

Und speichere sie im Namensmanager unter dem Namen „Spannweite“:

Ich teste sie – es funktioniert:

Ich ändere die Funktion

=LAMBDA(Bereich;WENN(WURDEAUSGELASSEN(Bereich);"Bitte wählen Sie einen Bereich aus!";MAX(Bereich)-MIN(Bereich)))
Die Funktion mit Bereich:

Die Funktion ohne Bereich:

Nicht das gewünschte Ergebnis!

Auch im Englischen funktioniert ISOMITTED nicht …

Ich hab als Kind viel mit Autos gespielt, jede Barbie hatte eins!

Ohne zu spicken – kennst du die Antwort?

In der letzten Excelschulung wurde ich gefragt, ob man Pivottabellen auf einem geschützten Tabellenblatt erzeugen, ändern und aktualisieren kann?

Ich gebe zu – ich war ein bisschen unsicher.

Und das sind die Antworten:

  • Auf ein schreibgeschütztes Blatt kann keine Pivottabelle eingefügt werden. Auch dann nicht, wenn alle Optionen zum Zulassen aktiviert sind. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.

Eine Aktualisierung ist nicht möglich, wenn das Blatt geschützt ist. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.

Wurde beim Blattschutz die Option “ PivotTable und PivotChart verwenden“ aktiviert wurde, kann man die Felder in die Zeilen, Spalten, Filter, … ziehen und von dort wieder entfernen – auch wenn die Zellen gesperrt sind.

Wurde das Tabellenblatt mit der Datenquelle geschützt, kann man keine Pivottabelle erstellen:

Weihnachten ist vorbei – ich habe alle Cookies gelöscht!

Ein bisschen doof ist es schon:

Ich habe eine (intelligente) Tabelle.

Ich füge eine Ergebniszeile hinzu – dort wird gerechnet:

Ich entferne die Farben und wandle sie in eine Liste um:

Was passiert? Die Formeln
=TEILERGEBNIS(109;Tabelle1!$E$2:$E$10)

bleiben stehen. Ebenso die Beschriftung der ersten Zelle: „Ergebnis“:

Wandelt man diese Liste nun erneut in eine Tabelle um:

ist die Formelzeile nun Teil der Tabelle.

Man kann erneut eine Ergebniszeile hinzufügen – das Ganze ist nun recht verwirrend:

May your coffee be strong and your Monday be short!

Geneigte Leserin, verehrter Leser,
hat jemand von euch eine Idee?
####

Hallo René,

Jetzt habe ich eine Sache, die ich nicht wirklich gebacken bekomme. Ich habe bisher keine Möglichkeit gefunden, eine Frage zu stellen, die dann jemand vorbereitet beantworten kann. Nachdem die Lösung (wenn sie vorhanden ist) wahrscheinlich umfangreicher ist, frage ich mal einfach an, ob ja jemand weiter helfen kann.

Ich hänge an der „AutoFit“-Funktion für Zeilenhöhen. Das Problem ist eigentlich ein alter Bekannter: Man hat einen Text, der länger ist als die Zelle es ermöglicht, stellt die Zelle auf „Zeilenumbruch“, und weist Excel entweder händisch (Doppelklick auf Zeilenhöhe) oder per VBA (AutoFit) an, die passende Zeilenhöhe einzustellen. Jetzt ist Excel aber kein Layoutprogramm, und macht nur WYSI ungefähr WYG. Die Zeilenhöhen sind auf dem Bildschirm nicht immer wirklich passend, manchmal sind es zu große Zeilen (zu kleine eher selten).

Noch schlimmer wird es, wenn ich auf die Druckvorschau (bzw. später den Druck) gehe. Da werden dann die Zeilenumbrüche neu gesetzt (in der Regel passt in die Zeile im Druck mehr als auf dem Bildschirm), und die Zeilenhöhe passt dann noch weniger als vorher.

Zu allem Überfluss scheint das Ergebnis auch vom verwendeten Drucker abzuhängen. Natürlich könnte ich jetzt die Zeilenhöhen manuell nacharbeiten, aber bei längeren automatisch erstellten Dokumenten ist das echt mühsam und nicht gerade geeignet, das einem Kunden zu verkaufen. Vor allem, wenn da die Seitenumbrüche dran hängen.

Kennst du oder jemand dazu eine befriedigende Lösung, mit der sich sicher passende Zeilenhöhen erstellen lassen?

Schöne Grüße

Peter

Hallo Peter,

[…]

Zu „autofit“. Ich kenne das Problem, habe es aber nicht eingrenzen können.

Autofit ist eine Methode – sie macht einmalig und keine Eigenschaft, die man vielleicht mit ein paar Parametern überlisten könnte …

Mir ist auch aufgefallen: Manchmal (in letzter Zeit seltener) ist der Umbruch in der Seitenansicht nicht der gleiche wie in der Normalansicht.

Ich habe ab und zu in VBA-Programmierungen „kleine“ Lösungen gebaut („suche“ die Überschriftszeilen und sorge dafür, dass sie nicht am Ende der Seite stehen oder erhöhe die Zeilenhöhe vor dem Speichern als PDF um 1 pt …)

Ich hatte keine Probleme damit gute Verstecke für die Weihnachtsgeschenke zu finden, sondern eher damit, mich später an sie zu erinnern.

Guten Tag Herr Dr. Martin,

Im Bild oben fehlt seit ein paar Tagen der Eintrag Makros, wie auch beim Menüband. Haben Sie eine Idee, wie ich das wieder herstellen kann? Ich danke Ihnen schon jetzt für Ihre Unterstützung:

Mit freundlichen Grüßen

WK

Hallo Herr K.,

m.W. ging das noch nie in Visio. Der Grund: in Word kann ich Makros in der normal.dotm, in Excel in der Personal.xlsm speichern, so dass sie immer zur Verfügung stehen. Visio ist vorlagen- d.h. dateibasiert. Da Symbole in der Symbolleiste für den Schnellzugriff immer sichtbar sind, sollten sie nicht Makros verwenden, die in einer bestimmten Datei liegen.

Schöne Grüße

Rene Martin

Ich wünsche mir an Weihnachten einen Hamster. Gute Idee – mal was anderes als Rotkraut unmd Gans.

Ich habe gelacht. Für die nächste Schulung, bei der mehrere Dutzend Teilnehmerinnen und Teilnehmer geschult werden sollen, hat der IT-Leiter eine Namensliste angelegt: wer aus welcher Abteilung sich für welche Schulung eingetragen hat.

Und hier ist die Unterschriftliste, sagt er und schmunzelt: ich habe doch keine Lust die Namen per Hand einzutragen. Deshalb habe ein eine kleine Formel geschrieben. Alles andere würde doch nur nerven.

Recht hat er, denke ich und lache.

Ich nehme dieses Jahr an Weihnachten keine Pakete für die Nachbarn an. Letztes Jahr war nur Schrott drin.

Verblüfft. Ich erstelle eine Pivottabelle und möchte die Jahreszahlen gruppieren:

Eine Fehlermeldung ist die Folge:

Kann den markierten Bereich nicht gruppieren.

Okay? – und warum?

Ein Blick in die Daten liefert die Lösung: die Jahreszahlen sich als Text formatiert?!!?!

In Zahlen umwandeln – dann klappt es …

Ich soll meiner Frau ein Shampoo kaufen und soll nun entscheiden, ob ihre Haare glanzlos, strapaziert oder fettig sind. Ich kann nur verlieren!

Hallo ich brauch bitte mal Hilfe bei bedingter Formatierung!

Kann man wenn eine Zelle automatisch die Farbe rot erhält über die bedingte Formatierung dann da automatisch einen Buchstaben mit einfügen ?

Vielen Dank für eure Hilfe

=======

Du kannst eine bedingte Formatierung mit Hintergrundfarbe, Schriftfarbe und einem (benutzerdefinierten) Zahlenformat versehen. Beispielsweise „Rot“ – dann wird dieser Text angezeigt, wenn die Bedingung erfüllt ist.

Hört bitte auf, euch an Weihnachten den perfekten Mann zu wünschen! Ich wurde schon drei Mal gekidnappt.

Christian ist irritiert. Ich auch.

PowerQuery stellt für Zahlenformate alle (nur denkbaren) Varianten auf Basis der Gebietsschemata zur Verfügung. Allerdings fehlt die ISO-Norm bei der Kalenderwoche.

Okay.

Wir haben eine Liste mit Ländernamen, die sortiert werden:

Es fällt auf, dass PowerQuery streng nach Groß- und Kleinschreibung sortiert. Deshalb steht die USA vor Ungarn:

Das kann man mit dem Befehl each Text.Upper korrigieren:

Aber: Österreich befindet sich am Ende. Das Alphabet wird US-amerikanisch sortiert. Und: der Befehl Sort stellt keinen Parameter zur Verfügung eine Länderkennung einzutragen. Im Deutschen wird a < ä < b sortiert, im Spanischen a < b < c < ch < d … < l < ll < m < n < ñ < o …

Für jedes Land, das heißt: für jede Sprache müsste man eine Hilfstabelle anlegen. Sehr mühsam!

Danke an Christian Gröblacher für diesen Hinweis.

Wenn ich sowieso zur Hölle fahre, kann ich auch die landschaftlich schöne Route nehmen

Merkwürdig. In einer Zelle steht eine Formel:

=SUMMENPRODUKT((JAHR(Tabelle2[Aktionsstart])=2021)*(Tabelle2[Carrier für den Versand]="contoso")*(Tabelle2[Gesamtmenge]))

Ich benötige die Formel und lasse sie vom Makrorekorder aufzeichnen:

    ActiveCell.Formula2R1C1 = _
        "=SUMPRODUCT((YEAR(Tabelle2[Aktionsstart])=2021)*(Tabelle2[Carrier für den Versand]=""contoso"")*(Tabelle2[Gesamtmenge]))"

Baue sie etwas um.

Der Kunde beschwert sich über einen Fehler. Der Grund: die Eigenschaft

Formula2R1C1

ist noch nicht in Excel 2013 verfügbar *ggrrrr*

Warum zeichnet Excel nicht

ActiveCell.FormulaR1C1 = 

auf?

Kunden, die Weihrauch kauften, bestellten auch Myrrhe und Gold.

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.

* den Verbund aller verbundenen Zellen aufgehoben

* den Textumbruch entfernt

* mit einem AutoFilter in der Namensspalte den Text „Name“ und die leeren Zellen gefiltert und entfernt

* die leeren Spalten gelöscht.

Advent ist, wenn der Dachboden entrümpelt und alles wieder in der Wohnung verteilt wird.

Boah, was ist denn das? Ich bin sehr irritiert!

Ich öffne im Windows-Explorer das Eigenschaftenfenster einer Datei und wechsle auf die Registerkarte „Sicherheit“. Dort finde ich den Dateinamen mit Pfad, den ich markiere und nach Excel kopiere. Achtung: Ich markiere von rechts nach links:

Ein zweites Mal – jetzt wird von links nach rechts markiert und anschließend kopiert:

Ich kopiere beide Varianten nach Excel – die erste ist oben, die zweite unten. Ich ermittle die Anzahl der Zeichen mit LÄNGE und bin erstaunt. Ich löse das erste Zeichen mit der Funktion LINKS heraus und bin wieder erstaunt:

Wandelt man das Zeichen vor dem Laufwerksbuchstaben D mit Code in den ASCII-Code um und mit ZEICHEN wieder zurück, so erhält man ein „?“

Ich bin erstaunt.

Noch schlimmer wird es, wenn man mit PowerQuery und diese Access-Datenbank zugreift

und den Pfad durch den ersten Text ersetzt:

DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.

lautet die Fehlermeldung. Man sieht das Zeichen nicht – weder in Excel noch in Word, im Editor oder in PowerQuery. Und wundert sich über diese merkwürdige Fehlermeldung.

Da gibt es nur eine Lösung: Will man den Dateinamen mit Pfad ermitteln, muss man im Explorer über das Symbol „Pfad kopieren“ den Verzeichnisnamen und Dateinamen in die Zwischenablage kopieren.
(danke an Martin Weiß für diesen Tipp)

Lars Krismes verhaftet! Es sitzt jetzt im Verlies Navidad.

Es fing harmlos an. Ein Anruf am Wochenende. Ob ich mal kurz helfen könne. „Worum geht es denn?“ In einer Liste tauchen Werte mehrmals auf – sie sollen auf einer Serienbrief-Seite stehen. „Machen Sie doch eine Pivottabelle“, war meine lapidare Antwort. Ganz so einfach gestaltet sich das Problem allerdings nicht.

Die Banken (aus der letzten Spalte) wiederholen sich, sie sollen gruppiert werden und zu jeder Bank alle Kunden aufgelistet werden, die bei ihr Mitglied sind. Mit weiteren Informationen.

Für das Gruppieren beginnen wir mit der Funktion EINDEUTIG, entscheiden uns aber später für eine Pivottabelle, weil man hier am leichtesten filtern kann:

Alle Kunden sollen aufgelistet werden. Warum nicht mit FILTER?

Die Formel

=FILTER(Tabelle1!$A$2:$O$12384;Tabelle1!$N$2:$N$12384=A3)

tut gute Dienste:

Alle Spalten werden geliefert. Ich benötige aber nur die Namen. Also wird reduziert:

=FILTER(Tabelle1!$D$2:$D$12384;Tabelle1!$N$2:$N$12384=A3)

Allerdings sollen die Daten ja nicht untereinander, sondern in einer Zelle stehen. Also muss man die Texte verketten. Die Funktion TEXTVERKETTEN hilft hierbei:

=TEXTVERKETTEN(ZEICHEN(10);WAHR;FILTER(Tabelle1!$D$2:$D$12384;Tabelle1!$N$2:$N$12384=A3))

Das Ergebnis verblüfft. Klar – man muss noch den Textumbruch einschalten:

Herunterziehen – und wieder ein Erstaunen:

Das müssten doch mehr Namen sein! – Klar: man muss die optimale Zeilenhöhe aktivieren. Doppelklick – dann funktioniert es:

Und so wird der Rest ausgefüllt. Allerdings – bei den Währungen und Datumsangaben muss man sich noch mit der Funktion TEXT behelfen:

=TEXTVERKETTEN(ZEICHEN(10);WAHR;"€ "&TEXT(FILTER(Tabelle1!$H$2:$H$12384;Tabelle1!$N$2:$N$12384=A3);"#.##0,00"))

Außerdem soll noch ein bestimmter Datumswert gefiltert werden. Das Jahr wird ausgelagert. Man könnte mit der Funktion JAHR arbeiten – wir entscheiden uns für einen ZWISCHEN-Bereich, also >= und <=

=TEXTVERKETTEN(ZEICHEN(10);WAHR;TEXT(FILTER(Tabelle1!$K$2:$K$12384;(Tabelle1!$N$2:$N$12384=A3)*(Tabelle1!$L$2:$L$12384<=DATUM($L$2;12;31))*(Tabelle1!$L$2:$L$12384>=DATUM($L$2-100;1;1)));"TT.MM.JJJJ"))

Und so kann man auf Basis dieser Tabelle einen Serienbrief erstellen.

Die Bank wird einmal aufgelistet; die Namen alle einzeln untereinander:

Und das Ganze in der Vorschau:

Ein Stückchen Arbeit – aber ein Mensch war glücklich und ich zufrieden.

Übrigens: die Matrixfunktionen FILTER, EINDEUTIG, SEQUENZ & co sind seeeehhhhhr langsam. Beim Herunterziehen meldet Excel:

Und benötigt leider seeeeeehhhhhhhr viel Zeit:

Es gibt Tage, da trete ich nicht ins Fettnäpfchen. Da falle ich in die Fritöse.

Kennst du Anrufe, die beginnen mit einem „hast du mal einen Moment Zeit?“ oder: „störe ich gerade?“

Richtig: Angelika rief am Wochenende an. Meine Kollegin Angelika. Sie bereitet gerade einen Excel-Makro-Kurs vor und suchte einige gute Gedanken für Befehle, die man mit dem Makrorekorder aufzeichnen könne. Dabei stieß sie auf die benutzerdefinierte Kopfzeile.

„Sag mal“, fragte sie, „wenn ich in der Kopfzeile aufzeichne: Seitennummerierung, Datum, Dateiname und Tabellenblattname passiert etwas ganz Komisches.“

Zuerst glaubte ich es nicht. Also – auch aufgezeichnet:

Ich lasse das Makro laufen:

Das Ergebnis verblüfft:

Und noch einmal:

Und wieder:

Und wieder. Und wieder. Und wieder ….

Ich schaue im Code nach:

[...]
    With ActiveSheet.PageSetup
        .LeftHeader = "&P / &N"
        .CenterHeader = "&D / &T"
        .RightHeader = "&Z&F / &A"

Eigentlich alles okay. Ich habe keine Ahnung, was hier passiert!

Ich rate Angelika, im Makrokurs nur den Firmennamen als Text aufzuzeichnen. Das klappt!

Okay – für Datum und Uhrzeit stehen die VBA-Befehle Date und Time zur Verfügung; für den Dateiname ActiveWorkbook.Name (oder Fullname), für den Blattnamen ActiveSheet.Name, aber für die Seitennummer? Ich weiß keine Lösung.

Danke an Angelika Meyer für diesen Hinweis!

Und nur wenig nach der Veröffentlichung des Artikels erreicht mich ein Kommentar von Ernst. Großartig – DAS ist des Rätsels Lösung! Hier sein Kommentar:

Hallo Rene,

dieses eigenartige Verhalten tritt bei mir (Excel 2019) nur dann auf, wenn vor dem Festlegen der PageSetup-Eigenschaften die Application.PrintCommunication-Eigenschaft auf False gesetzt wird. Wenn die Kommunikation mit dem Drucker nicht abgeschaltet wird, tritt dieses Verhalten nicht auf.

Bei der Makroaufzeichnung werden die Befehle
Application.PrintCommunication = False und Application.PrintCommunication = True
verwendet.

Bei Microsoft steht wohl folgender Hinweis:

Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die Ausführung von Code zu beschleunigen, mit dem PageSetup-Eigenschaften festgelegt werden.

Legen Sie die PrintCommunication-Eigenschaft nach dem Festlegen der Eigenschaften auf True fest, um alle im Cache vorhandenen PageSetup-Befehle auszuführen.

Scheinbar klappt dies nicht richtig.

Salü

Ernst

Gerade die Zutaten des Gewürzgurkenglases durchgelesen: Gurken, Wasser, Branntweinessig, Zucker, Zwiebeln, Speisesalz. Und jetzt noch einmal mit der Melodie „Freude schöner Götterfunken“!

Ich verstehe Excel ab und zu nicht.

Seit letzter Woche habe ich in der Kategorie „Text“ die neue Funktion „MATRIXZUTEXT“. Sie wandelt die Werte eines Bereichs in eine Zeichenkette um:

Ich sehe keinen Unterschied zur Funktion TEXTVERKETTEN.

Okay – es gibt einen zweiten Parameter – trägt man beim Parameter Format den Wert 1 ein, erfolgt die Darstellung des Textes als:

{„Nr“.“Länge“.“Name“.“Kontinent“.“Quellgebiet“.“Mündung“.“Einzugsgebiet“.“Mittlerer Abfluss“;1.6852.“Nil“.“Afrika“.“Ruandaberge“.“Mittelmeer“.3254853.2660;2.6448.“Amazonas“.“Südamerika“.“Anden, Peruanische Ostkordillere“.“Atlantischer Ozean“.6112000.206000;3.6380.“Jangtsekiang“.“Asien“.“Tibet“.“Ostchinesisches Meer“.1722155.31900;4.6051.“Mississippi“.“Nordamerika“.“Rocky Mountains“.“Golf von Mexiko“.2981076.18400;5.5540.“Jenissei“.“Asien“.“Sajangebirge“.“Arktischer Ozean“.2554482.19600;6.5410.“Ob“.“Asien“.“Mongolischer Altai“.“Obbusen“.2972497.12500;7.5052.“Amur“.“Asien“.“Chentii-Gebirge“.“Ochotskisches Meer“.2400000.11400;8.4845.“Gelber Fluss“.“Asien“.“Bayan-Har-Gebirge“.“Gelbes Meer“.752000.2570;9.4835.“Kongo“.“Afrika“.“Südlich des Tanganjikasees“.“Atlantischer Ozean“.3730474.41800;10.4500.“Mekong“.“Asien“.“Tibet“.“Südchinesisches Meer“.795000.15000}

Es bleibt die Antwort auf die Frage: „Und wer braucht das?“

Wissenschaftler: Unsere Erkenntnisse sind nutzlos, wenn sie aus dem Kontext gerissen werden. Medien: Wissenschaftler sagen, ihre Erkenntnisse sind nutzlos.

Seltsame Ereignisse in der letzten Excelschulung.

Ich weiß, dass beim Arbeiten mit dem Funktionsassistenten bei der Funktion ZÄHLENWENN ein Bildschirmfehler (Grafikfehler) auftritt. Ich habe ihn bereits beschrieben:

Allerdings trat dieser Fehler bei einem Teilnehmer auch bei der Funktion WENN auf – er wollte zwei WENN-Funktionen ineinander verketten:

Als Deutscher stelle ich das Geschirr nicht einfach in die Spülmaschine. Ich spüle es per Hand vor, damit die Spülmaschine nichts Schlechtes von mir denkt

Vorgestern erhielt ich die Frage, wo denn die Symbolleiste für den Schnellzugriff sei. Da ich keinen Blick auf das entsprechende Excel werfen konnte. vermutete ich, dass sie unter dem Menüband eingeschaltet war.

Weit gefehlt: der Anwender hatte sie ganz ausgeschaltet. Böses, kleines Kontrollkästchen!

Eine Packung Toffifee hat 600 Kalorien. Aber das stört mich nicht – ich esse ja nicht die Packung!

In einem Formular steht eine Formel. Okay – es befinden sich eine Reihe an Formeln dort – aber einige sind besonders lang. Manchmal soll die Zelle mit einem Wert überschrieben werden und nicht mehr variablen sein (wenn bestimmte Voraussetzungen erfüllt werden, welche die Formel nicht abbildet); dann wiederum soll die Formel zurückgesetzt werden. Also gehe ich auf die Suche – wie heißt der VBA-Code der Formel. Die Berechnung lautet:

=WENN(Datenblatt_Logistikdaten!$B$40="";"";WENN(UND(Datenblatt_Logistikdaten!$B$39="Paket";Datenblatt_Logistikdaten!$B$40="DHL Paket");Datenblatt_Logistikdaten!L16; WENN(UND(Datenblatt_Logistikdaten!$B$39="Paket";Datenblatt_Logistikdaten!$B$40="Hermes Paket");Datenblatt_Logistikdaten!M16; WENN(UND(Datenblatt_Logistikdaten!$B$39="2MH";Datenblatt_Logistikdaten!$B$40="Hermes 2-MH");Datenblatt_Logistikdaten!L47; WENN(UND(Datenblatt_Logistikdaten!$B$39="2MH";Datenblatt_Logistikdaten!$B$40="AO");Datenblatt_Logistikdaten!M47; WENN(UND(Datenblatt_Logistikdaten!$B$39="Spedition";Datenblatt_Logistikdaten!$B$40="DSV");Datenblatt_Logistikdaten!L30; WENN(UND(Datenblatt_Logistikdaten!$B$39="Spedition";Datenblatt_Logistikdaten!$B$40="Hellmann");Datenblatt_Logistikdaten!M30;"")))))))

Ich zeichne mit dem Makrorekorder auf und stutze:

Ein seltsamer Umbruch! Noch erstaunlicher ist er am Ende der Zeile:

Der Makrorekorder bricht die Codezeile nach einer bestimmten Anzahl von Zellen um, was sehr merkwürdig wirkt:

ActiveCell.FormulaR1C1 = _
"=IF(Datenblatt_Logistikdaten!R40C2="""","""",IF(AND(Datenblatt_Logistikdaten!R39C2=""Paket"",Datenblatt_Logistikdaten!R40C2=""DHL Paket""),Datenblatt_Logistikdaten!R[-41]C[8], IF(AND(Datenblatt_Logistikdaten!R39C2=""Paket"",Datenblatt_Logistikdaten!R40C2=""Hermes Paket""),Datenblatt_Logistikdaten!R[-41]C[9], IF(AND(Datenblatt_Logistikdaten!R39C2=""2MH"",Datenblatt_L" & _
        "ogistikdaten!R40C2=""Hermes 2-MH""),Datenblatt_Logistikdaten!R[-10]C[8], IF(AND(Datenblatt_Logistikdaten!R39C2=""2MH"",Datenblatt_Logistikdaten!R40C2=""AO""),Datenblatt_Logistikdaten!R[-10]C[9], IF(AND(Datenblatt_Logistikdaten!R39C2=""Spedition"",Datenblatt_Logistikdaten!R40C2=""DSV""),Datenblatt_Logistikdaten!R[-27]C[8], IF(AND(Datenblatt_Logistikdaten!R39C2=""Sped" & _
"ition"",Datenblatt_Logistikdaten!R40C2=""Hellmann""),Datenblatt_Logistikdaten!R[-27]C[9],"""")))))))" & _
        ""

Nun ja – das kann (und sollte) man ja korrigieren!

Warum heißen Männer-Duschgels immer „active“, „Sport“ oder „Energy“? – Emotional würden mich eher „lazy“, „sleepy“ oder „lethargy“ abholen.

Excel-Schulung. Ich beginne mit den Funktionen SUMME, ANZAHL, MITTELWERT & co. Danach die wohl zweitwichtigste Gruppe: Funktionen der Kategorie „Logik“. Ich zeige, dass man WENN tippen kann oder über den Funktionsassistenten, den man in Formeln in der Gruppe „Logik“ eingeben kann.

Völlig entgeistert starre ich auf die angebotenen Funktionen. Waren die ALLE gestern schon da?

Es gibt eigentlich nur drei Dinge, die man fürs Skifahren lernen muss: wie man die Skier anzieht, wie man bergab fährt und wie man einen Krankenhausflur entlangläuft. (Lord Mancroft)

Guten Morgen zusammen, vielleicht könnt Ihr mir helfen

Ich habe für meine Masterarbeit Wetterdaten bekommen und würde jetzt gerne aus den Zahlen für die Windrichtung den Text „Nord“, bzw. „Ost“ usw. generieren. Dabei ist

0<=x<45 => „Nord“

45<=x<135 => „Ost“

135<=x<225 => „Süd“

225<=x<315 => „West“

315<=x<380 => „Nord“

Dafür hab ich folgende WENN-Funktion verwenden wollen:

=WENN(0<=A2<45;“Nord“;WENN(45<=A2<135;“Ost“;WENN(135<=A2<225;“Süd“;WENN(225<=A2<315;“West“;WENN(315<=A2<380;“Nord“;“x“)))))

Wie Ihr auf dem Screenshot sehen könnt, zeigt mir Excel aber nur das „alternative“ x an… Ich kann mir aber nicht erklären warum?

Excel kennt kein „zwischen“. Du darfst nicht schreiben

0<=A2<45

sondern mit UND:

=WENN(UND(0<=A2;A2<45);“Nord“;WENN(UND(45<=A2;A2<135);“Ost“;WENN(UND(135<=A2;A2<225);“Süd“;WENN(UND(225<=A2;A2<315);“West“;WENN(UND(315<=A2;A2<380);“Nord“;“x“)))))

Wenn deine Freundin fragt: „Schatz, kann ich so rausgehen“ – ist „Klar, ist ja schon dunkel“ die falsche Antwort.

Gestern in der Excelschulung. Wir besprechen den Autofilter. Ich erkläre die Option „Daten haben Überschriften“ beim Assistenten „benutzerdefiniertes Sortieren“.

Ein Teilnehmer fragt, warum bei ihm diese Option ausgegraut, also inaktiv sei:

Die Antwort ist schnell gefunden: der Autofilter wurde eingeschaltet – dadurch wird die erste Zeile als Überschriftszeile definiert.

Es gibt Leute, da denke ich mir im Gespräch plötzlich: Ach guck mal, die Evolution macht auch mal Pause.

Gestern in der Excelschulung. Ich erkläre den Autofilter. Wir haben eine Liste mit zirka 12.000 Datensätzen. Wir filtern alle Hamburger und Hamburgerinnen:

Zu der gefilterten Liste fügen wir alle Personen hinzu, die in Flensburg wohnen:

Und so machen wir weiter mit Bremen, Husum, Kiel, Buxtehude, Uelzen, Itzehoe, …

Ein Teilnehmer meldet sich und fragt, wie und ob man denn erkennen könne, welche Orte gefiltert seien:

Ich habe eine Weile überlegen müssen. Fährt man mit der Maus über das Filtersymbol, werde alle Filterkriterien im Quickinfo angezeigt:

Sehr versteckt!

Wenn ich zwei Dinge gleichzeitig kann, dann ist es lächeln und dabei Mordgedanken hegen.

Nennen wir ihn B. B. kann für Björn stehen. Oder für Benno. Für Benjamin oder für Boris. Egal. Wir nennen ihn B.

B. ist Teilnehmer meiner Excelschulung und stellt eine Frage zum Aufbereiten von CSV-Dateien, die er in regelmäßigen Abständen erhält. Er denkt an eine VBA-Lösung – ich schlage PowerQuery vor. Die Datei wird aufgerufen, transformiert und nach Excel zurück geschrieben.

Allerdings: der Pfad, beziehungsweise der Dateiname soll variabel sein. Eigentlich kein Problem, denke ich, und lasse B. Pfad und Dateiname in die Excelmappe schreiben, mit einer Überschrift versehen und in eine (intelligente) Tabelle umwandeln.

Beide Tabellen werden nach PowerQuery gezogen, und dort mit einem Drilldown in einen Text verwandelt. Sie werden in dem Befehl

File.Contents

verwendet; die Sicherheitsstufe dieser Arbeitsmappe wurde ignoriert. Und dann das Erstaunliche:

DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.

Stirnrunzeln.

Probieren. Beispielsweise Pfad und Dateiname in PowerQuery (oder in Excel) zu verketten und diese Zeichenkette zu verwenden. Beides schlägt fehl:

Immer wieder die gleiche Fehlermeldung:

DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.

In Ruhe, alleine, und ohne B. schaue ich mir die Zeichenkette genau an und probiere. Erstaunt stelle ich fest, dass das erste Zeichen nicht der Laufwerksbuchstabe ist. In Excel kann man das mit der Funktion LINKS oder TEIL ermitteln. Der ASCII-Code lautet 63 – eigentlich ein Fragezeichen.

Ich überlege, probiere und frage B. Er hat eigentlich nur den Namen des Verzeichnisses aus den Dateieigenschaften kopiert. Und ich habe ihm zugesehen.

Ich weiß nicht, wie dieses merkwürdige Zeichen in die Excelzelle gelangt ist. Ich weiß, dass Excel bei einigen Zeichen (geschützte Leerzeichen, bedingte Trennstriche, …), die man über Word, Outlook oder eine Webseite nach Excel kopieren kann, Probleme hat. Aber hier? Keine Ahnung.

Lösung des Problems: Pfad neu tippen – und dann klappt es!?!

Ich habe keine Schokoladenseite. Ich sehe von allen Seiten aus wie Vanillepudding.

Sicherlich hätte ich sehr, sehr lange gesucht. Und mich gewundert. Zum Glück hat mir Tanja Kuhn geholfen. Beziehungsweise mich schon vorher aufmerksam gemacht.

Die Aufgabe: in einem Wordformular soll dynamisch, das heißt per VBA, die Kopfzeile (und auch die Fußzeile) ausgetauscht werden:

Ich beginne mit dem Löschen der Kopfzeile.

ActiveDocument.Sections(i).Headers(wdHeaderFooterFirstPage).Range.Delete

Obwohl weder das Dokument noch einer der Abschnitte geschützt ist, erhalte ich eine Fehlermeldung:

Die Antwort: das Bild befindet sich in einem Inhaltssteuerelement und das wurde im Entwurfsmodus in den Eigenschaften geschützt. Folglich kann auch nicht die Kopfzeile gelöscht werden …

Böse! Ganz böse!

Schlägerei im Altenheim: Wenn sich zwei streiten, fliegen die Dritten

Hallo Rene, hier Andre :-)!,

Ich hatte eine Frage zu Excel.

Könntest du dir das mal durchlesen, eventuell kennst du eine Lösung.

Ich öffne aus einer Exeltabelle heraus eine weitere Exceldatei. Dies ist ein Bestellformular.
Dort trage ich dann bestimmte Werte ein in die entsprechenden Felder.
Ich öffne diue Datei folgendermassen:

    Workbooks.Open FileName:=AblagepfadBanfVorlage + DateinameBanfVorlage

Jetzt ist es aber so das beim Oeffnen der Datei eine MsgBox aufgeblendet wird die sagt man muss einen Vorgang auswählen.
Das ist auch sinnvoll wenn man die BANF(Bestellanforderung) händisch ausfüllt.
Aber nicht bei einem Automatismus.
Ausgelöst wird die MsgBox durch das Event „Worksheet_Change“

Ich frage mich ob es eine Mögliichkeit gibt, die Tabelle so zu öffnen das alle Makros deaktiviert sind und bleiben.
Ich hatte das was gelesen das es so gehen sollte:

Application.EnableEvents = False

Workbooks.Open FileName:=AblagepfadBanfVorlage + DateinameBanfVorlage

Application.EnableEvents = True

Aber das funktioniert nicht.
hast Du da vielleicht eine schnelle Idee.

####

Hallo André,

ich würde die MELDUNGEN (nicht die Makros!) unterdrücken mit:

Application.DisplayAlerts = False

und danach wieder die Warnmeldungen einschalten (= True)

Liebe Grüße

René

Heute mal den Salzstreuer auffüllen. Dauert bei den kleinen Löchern ja immer ewig.

Hallo Rene,

Hallo Rene,

schöne Grüße an dich. Als ich den Artikel über die negative Null gelesen habe (https://www.excel-nervt.de/entweder-sie-geben-mir-eine-gehalterhoehung-oder-erzaehle-500-kollegen-ich-haette-eine-bekommen/) , ist mir eingefallen, dass ich ebenfalls Versuche zu diesem Thema gemacht habe. Tatsächlich ist es möglich in eine Excelzelle eine negative Null einzutragen

Dazu verwende ich folgende kleine Funktion.

Public Function MinusNull()
    MinusNull = -0#
End Function

Durch den Aufruf  =MinusNull()  wird eine negative Null in der Zelle eingetragen. Diese negative Null kann nun als reiner Wert in andere Zellen kopiert werden.

Wenn diese negative Null in A1 kopiert und in B1 eine einfache Null eintrage wird, ergeben sich folgende Ergebnisse bei einem Vergleich dieser Zellen.

-0 0
Formeltext Wert
=A1=B1 FALSCH
=A1<B1 WAHR
=VORZEICHEN(A1) -1
=VORZEICHEN(B1) 0
=TEXT(A1;“0,00000000000000000″) 0,00000000000000000

Die TEXT()-Funktion unterschlägt das Minus-Zeichen. Bei allen anderen Vergleichen gibt es einen Unterschied zwischen einer negativen und der normalen Null.

Ob die Möglichkeit eine negative Null in eine Excelzelle einzutragen Sinn macht ist allerdings fraglich.

Schöne Grüße
bleib gesund

Ernst

Entweder Sie geben mir eine Gehalterhöhung oder erzähle 500 Kollegen, ich hätte eine bekommen!

Lieber René,

kennst Du eine negative Null?

In dem beigefügten Beispiel kann ich nicht nachvollziehen, warum Ergebnisse Null und einige negativ Null sind.

Du hast sicher eine Idee . Danke.

Beste Grüße

Traudl

Hallo Traudl,

lass dir mal ganz viele Nachkommastellen anzeigen. Dann siehst du in A2, B2 und C2 weiterhin ,90 und ,12 und ,22 aber in D2 befindet sich ein Rundungsfehler: ,000000000203727

Auch mit der Funktion =TEXT(D2;“0,000000000000000″) kann man es sich anzeigen lassen.
Liebe Grüße

Rene

Ich dachte, mir hätte jemand beim Joggen nachgepfiffen. War aber nur meine Lunge.

VBA-Schulung. Eine Teilnehmerin sieht ihren Fehler nicht und bittet mich zu helfen. Ich schaue mir den Code an:

Böse Leerzeichen! Der Fehler ist schnell gefunden: Am Ende des Tabellenblattnamens hatte sie aus Versehen ein Leerzeichen geschrieben, also statt Set xlTabelle = xlDatei.Sheets(„Almodovar“)

Set xlTabelle = xlDatei.Sheets("Almodovar ")

Ich geh mit meiner Laterne und meine Laterne mit mir. Da vorne ist eine Taverne. Dort tausche ich das Ding gegen Bier.

VBA-Schulung. Eine Teilnehmerin fragt mich, warum sie keinen Button mehr einfügen kann:

Meine erste Vermutung: Cursor sitzt in der Zelle. Nein!

Meine zweite Vermutung: ein Makro läuft noch: Nein!

Dann fiel es mir ein: „Drück mal [Strg] + [6]!“
Das war die Lösung!

Mit der Tastenkombination [Strg] + [6] wird die Anzeige von Bildern, Diagrammen, Formen, …. unterdrückt. Und also auch die Anzeige von Buttons.

Wie hat sie das gemacht? Wir haben vorher das Thema „Zahlen Formatieren“ und Aufzeichnen mit dem Makrorekorder behandelt. Ich habe die Tastenkombination [Umschalt] + [Strg] + [6] für das Zahlenformat „Standard“ gezeigt. Wahrscheinlich hat sie [Strg] + [6] gedrückt – damit werden Bilder ausgeblendet.

Man kann diese Einstellung auch über die Optionen deaktivieren:

Wenn ich am Morgen ins Badezimmer gehe, wird mir klar, dass es nicht gut ist, Menschen nach ihrer äußeren Erscheinung zu beurteilen.

Excel schwächelt.

Volker zeigt mir eine Fehlermeldung in Excel, die er noch nie gesehen hat:

Für Excel waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.

Wir sind erstaunt.

Ich probiere ein bisschen.

Ha – es gelingt mir den Fehler zu reproduzieren:

Ich erstelle in einer leeren Mappe ein zweites Tabellenblatt, beziehe mich auf dem zweiten Blatt in der Zelle A1 auf einen Bereich des ersten Blattes:

=Tabelle1!A:RD

Excel schafft es nicht diese 472 x 1.048.576 Zellen zu verknüpfen. Die Meldung
„Für Excel waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.“
ist die Folge:

Es funktioniert natürlich auch mit anderen Bereichen, beispielsweise

=Tabelle1!Z:DF

Danke an Volker Pagel für diesen Hinweis.

Männer schnarchen, um ihre Frauen vor wilden Tieren zu beschüzen. Frauen habe kalte Füße, um die wilden Tiere direkt zu töten.

Gestern habe ich ein Referat über „leere Zellen“ gehalten. Ich habe gezeigt, dass man eine leere Zelle durch einen Wert ersetzen kann – beispielsweise durch 0:

Klappt: der Suchen und Ersetzen-Dialog tut gute Dienste:

Sieben leere Zellen werden mit der Zahl 0 gefüllt.

Auch umgekehrt funktioniert es: Ersetze 0 durch „nichts“, also leere Zellen, in denen die Zahl 0 steht:

Ich stutze: warum werden jetzt zehn Änderungen vorgenommen?

Klar – ersetzt werden nicht die Zellen, in denen die Zahl 0 steht, sondern die Ziffer 0 wird gelöscht. Auch 70 wird 7. Kurz überlegt.

Klar: man muss die Option „Gesamten Zellinhalt vergleichen“ aktivieren:

Dann funktioniert auch das.

Manchmal, wenn mir langweilig ist, kaufe ich Kondome, fülle sie mit Tapetenkleister und werfe sie durchs offene Schlafzimmerfenster auf die Straße.

Excel ist nicht konsistent im Umgang mit leeren Zellen. In den meisten Funktionen wird eine leere Zelle als 0 interpretiert. Aber eben halt nicht immer. Gestern habe ich mich mal wieder geirrt.

Ich wollte die Funktion HÄUFIG mit der Funktion ZÄHLENWENNS nachbauen – wollte die Bereiche definieren.

Mit der Funktion HÄUFIGKEIT (als Matrixfunktion) kann man berechnen, wie viele Daten in den entsprechenden Klassen vorhanden sind:

=HÄUFIGKEIT(B2:K11;M2:M7)

Also baue ich nach:

=ZÄHLENWENNS($B$2:$K$11;">"&M1;$B$2:$K$11;"<="&M2)

Die erste Zelle wird falsch berechnet – klar – weil in der Zelle Text steht:

Also lösche ich die Überschrift:

Das Ergebnis ändert sich nicht.

Klar, denn der Formelteil

">"&M1

wird nicht ausgewertet als >0, sondern nur als >.

Also doch: eine 0 einfügen – dann klappt es:

Nehmen Sie Ihren Köter hier weg, ich spür schon einen Floh! – Komm, Rex, gehn wir. Die Frau hat Flöhe.

Das ist mir ja noch gar nicht aufgefallen. Volker hat darauf aufmerksam gemacht:

In einer Excelmappe befindet sich ein Tabellenblatt („Kontinente“) mit sechs intelligenten Tabellen: tbl_Europa, tbl_Antarktis, tbl_Afrika, …

Die Datei wird einmal als Excel-Arbeitsmappe (XLSX) und einmal Excel-Binärarbeitsmappe (XLSB) gespeichert.

Greift man mit PowerQuery auf die XLSX-Mappe zu, ist das Ergebnis bekannt: angezeigt wird das Tabellenblatt und die sechs intelligenten Tabellen:

Beim Zugriff auf die XLSB-Datei dauert der Zugriff nicht nur sehr, sehr lange – angezeigt wird nur das Tabellenblatt:

Danke an Volker Pagel für diesen Hinweis.

Sein Fazit: Don’t use xlsb!

Seine Kollegen kommentieren es:

Andreas:

Vielleicht ist xlsb dem alten Format xls zu ähnlich. Die xls aus 2003 lässt sich auch nicht einlesen.

Jens:
Volle Zustimmung!

Über Excel.Workbook ([Content]) werden xlsb auch nicht erkannt.

Ist echt nen Problem…xlsb ist bei uns relativ beliebt, da xlsm nicht per Mail versendet werden kann. Makro sind BÖSE 

Aber xlsb kann auch Makros enthalten.

Liebe Männer, die ihr mir schreibt: „Suche Frau“ – ich habe eure Frauen nicht!

Kennen Sie die beiden Symbole „Dezimalstelle hinzufügen“ und „Dezimalstelle entfernen“? Verwechseln Sie diese beiden Symbole auch regelmäßig?

Greg Nash (https://www.dearwatson.net.au/) gibt einen Tipp:

To add or remove decimal places in #Microsoft #Excel first click on the WRONG button several times, then click on the correct button twice as much as you had to.

Oberste Regel beim Putzen mit lauter Musik: die Klobürste ist nie, nie, nieeee das Mikrophon. Niemals!

Schöne Frage in der letzten PowerQuery-Schulung: warum kann man eigentlich keine Duplikate ermitteln lassen? Oder – wie in Excel – Duplikate löschen lassen?

Stimmt – DAFÜR gibt es in PowerQuery leider keinen Assistenten. Muss man „per Hand“ machen.

Tanja Kuhn schreibt: „Das geht beides. Duplikate löschen per Rechtsklick. Duplikate anzeigen über Gruppierung.“

Danke für den Hinweis – zur Gruppierung hätte der Teilnehme, der sich so eine Option beim Import der Daten gewünscht hatte, sicherlich angemerkt, dass man es dann auch „Duplikatensuche“ nennen sollte. Das „Duplikate löschen“ habe ich glatt übersehen / vergessen … (ich schäme mich! *lach*)

Der Teilnehmer dachte übrigens beim Verknüpfen von zwei Tabellen in einer 1:n-Beziehung an Access, bei dem beim Aktivieren der referentiellen Integrität automatisch überprüft wird, ob alle n-Elemente auf der 1-Seite vorkommen. So einen Haken oder eine Meldung hat er vermisst.

Neun von zehn Enten empfehlen Rindersteak zu Weihnachten.

Schöne Frage in der letzten Excelschulung. Ich habe eine Übung erstellt: Dutzende von Fehlern: Bezugsfehler, Formatierungsfehler, falsch Zeichen („x“ statt „*“; „;“ statt „:“, …) ausgeblendete Zeilen, weiße Schriftfarbe, …

Danach erstellen wir eine Pivottabelle. Eine Teilnehmerin fragt, wo Quellen von Rechenfehlern liegen können. Man sieht das Ergebnis einer Summe – aber stimmt es auch?

Ich überlege:

  • Der Bereich kann falsch gewählt sein
  • Wird mit Bereichen gearbeitet, kann sich die Pivottabelle beispielsweise auf einen Bereich auf einem falschen Tabellenblatt beziehen
  • Wird mit intelligenten Tabellen gearbeitet, kann eine falsche Tabelle verwendet worden sein.

Das kann man über Pivottable-Analyse / Datenquelle ändern herausfinden.

  • Die Pivottabelle wurde nicht aktualisiert.
  • Die Beschriftung wurde sinnentstellt geändert.

Habe ich etwas vergessen? Sicherlich … Ich fand die Frage sehr interessant …

Der Balkon ist immer noch voller Wespen, obwohl der Experte, der sich das Nest neulich ansah, meinte, im Oktober sind sie dann alle weg. Ich stelle ihnen mal einen Kalender raus.

Ich benötige den Code einer Farbe einer Zelle von Excel. Also schreibe ich:

MsgBox "Color:" & ActiveCell.Interior.Color & vbCr & "ColorIndex: " & ActiveCell.Interior.ColorIndex

Das Ergebnis:

Ich teste an einer anderen Zelle:

Und schließlich:

Drei Mal die gleichen Werte ???

Es dämmert mir …

Hinter den Zellen liegt eine bedingte Formatierung. Color und ColorIndex liefern die voreingestellten Zellfarben und nicht die durch die Datenüberprüfung angezeigten … Es ist übrigens recht mühsam, das Ergebnis der bedingten Formatierung zu ermitteln.

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

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

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

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

Und DAMIT funktioniert es nun auch bei ihr:

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

Einfach nicht aufgepasst!

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

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

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

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

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

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

Ich muss ändern. Entweder so:

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

Oder indem ich auf den Bereich ohne Überschrift verweise:

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

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

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

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

Gerechnet wird also:

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

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

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,

1 2 3 10