Erzähl doch mal was von dir! – Haha, das kenne ich. Und dann rufst du die Bullen.
Wo finde ich eine Liste der Links? Und wie kann ich alle Links eines Tabellenblatts löschen?
Das geht in Excel leider nicht – das muss man (mit VBA) programmieren.
Autor: Medardus
Schockbilder auf Zugarettenschachteln halten niemanden vom Rauchen ab. Ich sehe jeden Monat auf meinen Gehaltszettel und gehe trotzdem arbeiten.
Schöne Frage in der Excelschulung. Wir haben verschiedene Farben festgelegt, mit denen wir bestimmte Aufgaben kennzeichnen:
Nun passiert es manchmal, dass einige Kolleginnen und Kollegen ANDERE Farben verwenden als die Farben, die wir festgelegt haben. Das macht das Sortieren und Filtern mühsam. Kann man Farben schützen – in dem Sinne, dass nur bestimmte Farben verwendet werden können?
Die Antwort: leider nein. Mit VBA ist das möglich, aber nicht mit den Excel-Bordmitteln.
Umgekehrt: ich würde ein Auswahlfeld für die Aufgaben anbieten und auf Basis dieses Textes mit einer bedingten Formatierung die Zeilen enfärben.
Wie war dein Tag? – Gib mir einfach den Wein!
Ein bisschen peinlich ist es schon. Ich habe für meine Excelschulungen viele Beispieltabellen mit Dummy-Daten. Einige davon sind hervorragend, um Dinge zu zeigen. In einer Schulung, in der ich die Pivottabellen erläutere, formatiert eine Teilnehmerin die Umsatzzahlen mit einem Klick auf das Symbol „Buchhaltungszahlenformat“ die Zahlen als Währung.
Und fragt mich, warum Excel „DM“ als Währung verwendet.
Die Antwort: weil ich diese Datei seit mehr als 20 Jahren verwende, zwar die Jahreszahlen austausche, aber immer noch die gleiche Datei.
Okay: ich sollte zumindest die Werte in eine neue, leere Datei kopieren, in der Euro in der Standardzellformatvorlage verwendet wird …
Ich bin wie von Bahlsen – ich hab einen an der Waffel
Gelächter in der Excelschulung.
Wir lernen Pivottabellen:
Da Leute dies unterschiedlich aussprechen, wiederhole ich den Namen „Pivot“ in verschiedenen Aussprachevarianten. Auch in der „französischen“ – ohne „t“.
Eine Teilnehmerin aus der Slowakei schaut irritiert auf. Ihr Nachbar kommentiert: „Du denkst jetzt an Bier, oder?“
Mit meinem Gewicht habt ich Glück; immer wenn ich etwas verloren habe, finde ich es im Kühlschrank wieder.
Warum sie denn keinen Kommentar erstellen könne, wollte die Teilnehmerin in der Schulung wissen:
Klar: in der Zelle befindet sich bereits eine Notiz. Und diese war markiert.
Sag mal, bist du über deinen Ex-Freund hinweg? – Ja, drei Mal. Das war vielleicht ein Gehoppele mit dem kleinen Auto.
Schöne Frage in der Excelschulung. In einer Excelmappe sind mehrere Tabellenblätter vorhanden, die alle den gleichen Aufbau aufweisen. Von allen soll nur ein Teil (jeweils der gleiche) ausgedruckt werden.
Leider versagt der Gruppierungsmodus (also mehrere Tabellenblätter markieren) UND „Druckbereich festlegen“:
Wir überlegen zusammen. Wenn mehrere Blätter markiert sind, wird die Markierung eines Bereichs auf allen Blättern übernommen.
Wählt man nun beim Drucken die Option „Auswahl drucken“ wird von jedem Blatt der selektierte Bereich gedruckt.
Die Teilnehmerin war sehr zufrieden.
Heute war ich eine Stunde auf dem Laufband. Morgen schalte ich es vielleicht sogar ein.
Nach der Excelschulung zeigt mir ein Teilnehmer eine Pivottabelle. Er möchte dort eine Berechnung durchführen. Eigentlich recht einfach: einen Wert durch 1.000 teilen. Kein Problem, oder?
Allerdings erhalten wir eine Fehlermeldung:
Es dauert eine Weile, bis ich dahinter komme, dass ich mir die Liste einmal anschauen sollte. Und tatsächlich: dort finde ich eine Spaltenüberschrift
in der sich nicht nur Leerzeichen und Sonderzeichen ([ ]) befinden, sondern auch noch Zeilenumbrüche ([Alt] + [Enter]).
Also noch ein Versuch: Da ich die unterste Zeile nicht sehe, drücke ich so lange die Taste [Pfeil unten] und [Pfeil rechts], bis ich glaube, das Ende erreicht zu haben. Dann füge ich die Rechnung (hier: / 1000) ein.
Geht doch:
Im Zoo wird ein Mädchen gesucht: 5 Jahre alt, rosa Shirt, rosa Hose, rosa Kappe und rosa Rucksack. Ich habe schon fünf Stück eingesammelt – ich hoffe, das gibt einen Bonus.
Dominic Dauphin fragt, warum Outlook zwei Rechtschreibhilfen implementiert hat:
Ob man mit der einen bald darauf hingewiesen wird, dass man nicht richtig gegendert hat, will er wissen …
Ich habe starke Kopfschmerzen. Hast du etwas dagegen? – Nein, ich habe nichts dagegen. Du kannst ruhig Kopfschmerzen haben.
Speichert man in Outlook eine Anlage ab und öffnet sie und versucht nun in Outlook diese Anlage erneut zu speichern, erhält man folgende amüsante Meldung:
„Warum gleich zwei Mal?“, fragt sich Dominic, dem ich diesen Hinweis verdanke.
Hinweis: Microsoft war diesmal schnell. Inzwischen ist diese Meldung vereinfacht worden.
Ich hatte sechs verschiedene Stimmen im Kopf. Jetzt sind es nur noch fünf. Den Moralapostel habe ich im Alkohol ertränkt.
Excelschulung
„Mit welcher Funktion“, will eine Teilnehmerin wissen, kann man die Änderung einer Zelle protokollieren. Das heißt: „wie kann man ermitteln, um welchen Wert eine Zelle geändert wurde.“
„Schöne Frage“, denke ich und antworte: „Das geht mit Excel-Formeln leider nicht. Das müsse man per Programmierung ermitteln. Beim Ändern einer Zelle wird der alte Wert mit dem neuen Wert verglichen.“
Ich mag es nicht, wenn der Wecker klingelt und man aus dem Schlaf gerissen wird. Aber Feierabend ist nun mal Feierabend.
In der Excelschulung zeige ich, dass man mit [Strg] + [Pfeil unten] an das untere Ende (auf die letzte gefüllte Zelle) in einem Bereich springen kann. Mit [Strg] + [Pfeil oben] an das obere Ende.
Die Teilnehmerin fragt, wie man in die Mitte der Liste springen kann. Ich lache und sage ihr, dass es dafür keine Tastenkombination gibt. Aber: man kann den Zellnamen, beispielsweise A16, in das Namensfeld eintragen, [Enter] drücken – dann steht der Cursor in der Mitte der Liste.
Ein echter Pirat weint nicht. Ein echter Pirat heult Rum.
Hallo Herr Martin,
können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:
- gegeben: ein Bereich mit insgesamt 14 Spalten und beliebig vielen Zeilen
- Ziel: eine intelligente Tabelle bei der Spalte 1, Spalte 2-5, Spalte 6-10; Spalte 11-14 jeweils ein eigenes coloriertes Stripset besitzen (siehe Abbildung = 1. Versuch)
- meine Versuche + aufgetretene Problematik:
- Versuch: mehrere intelligente Tabellen mit dem jeweiligen Stripset (für Zeilen und Spalten) aneinanderfügen
–> beim Hinzufügen einer neuen Zeile in Spalte 1 (und ff. neu alphabetisch sortieren lassen), sortieren sich die Zeilen der anderen Tabellen nicht automatisch mit –> Chaos
Idee: Verknüpfung der aneinanderliegenden Tabellen, damit jede auf die Veränderung bspw. in der ersten Spalte (bzw. Tabelle) reagiert - Versuch: eine 14 Spalten umfassende intelligente Tabelle mit benutzerdefinierter Tabellenformatierung
–> da die Tabellenabschnitte aus einer unterschiedlichen Anzahl aus Spalten bestehen, lässt sich nicht mal ansatzweise mein Ziel über das Formatieren des Stripsets „erste Spalte“ / „zweite Spalte“ verfolgen
Idee: Hinzufügen der Auswahlfelder Stripset „dritte Spalte“, „vierte Spalte“, usw. - Versuch: eine 14 Spalten umfassende intelligente Tabelle mit „überdeckender“ händisch eingestellten Formatierung
–> händisch eingestellte Formatierung ist nicht intelligent, sodass bei Filterfunktion die farbliche Unterscheidung zwischen den Zeilen nicht mehr gegeben war (Bsp. Zeile 1 gelb, Zeile 2 grün, Zeile 3 gelb; Filter Z.2 ; Zeile 1 gelb, Zeile 3 gelb)
Idee: Anwenden der bedingten Formatierung. Hierbei habe ich schnell festgestellt, dass mir der Ansatz gänzlich fehlt –> an welche „Variable“ od. „Konstante“ binde ich die Formatierung, damit ich einfach eine normale intelligente Tabelle erhalte, bei der sich die Hintergrundfarben unterschiedlich vieler Spalten abschnittsweise unterscheiden, die sich zusätzlich mitverändern, sollte man filtern oder neue Zeilen an unterschiedlicher Stelle hinzufügen
Ich hoffe, ich konnte es einigermaßen verständlich machen, woran es bei mir scheitert. Weiterhin hoffe ich, dass Sie Zeit und Lust haben, sich mit dieser Problematik zu befassen.
Mit freundlichen Grüßen
Marcel
PS: Ihr Forum „Excel nervt …“ ist mega unterhaltsam und hilfreich und befasst sich verständlich mit atypischen Fragen im Umgang mit dem Programm. Einfach genial. Hat mir sehr gefallen!
#####
Hallo Herr Gröschel,
1.) ich würde nicht mehrere intelligente Tabellen verwenden. Das widerspricht dem Gedanken der Tabellen.
2.) Ich habe nachgeschaut: es geht nicht mit dem Stripset. DAS ist recht einfach aufgebaut und erlaubt nicht so viele Varianten, wie Sie es gerne hätten.
3.) Warum nicht bedingte Formatierung? Setze ich auch gerne ein. Beispielsweise um eine Zeile farblich zu hinterlegen.
Die Formel (bspw.):
=UND(SPALTE()>=11;SPALTE()<=14)
Hilft das?
Liebe Grüße
Rene Martin
####
Guten Abend Herr Martin,
vielen Dank, dass Sie so schnell geantwortet haben! Ihre Formel hat mir grundsätzlich weitergeholfen. Damit konnte ich das Problem der verschiedenen Spaltenfarben in meiner Tabelle lösen. Es war simpel und genial.
Dennoch blieb das Problem der farblichen Abgrenzung zwei aufeinanderfolgender Zeilen bestehen. Zuerst probierte ich es mit:
=REST(ZEILE();2)
Hat soweit gut funktioniert, bis ich dann die Filterfunktion der intelligenten Tabelle verwendet habe. Im Ergebnis unterschied das Programm nicht zwischen eingeblendeten und ausgeblendeten Zeilen.
Basierend darauf kam ich durch weitere Recherche zur Ziellösung:
=REST(TEILERGEBNIS(3;$B$5:$B5);2)=0
(wobei die erste Spalte meiner Tabelle in Blattspalte B beginnt und ab Zeile 5 stets einen Wert beinhaltet)
Hierbei ist zu beachten, dass der gewünschte Effekt bzw. konkret die gewünschte farbliche Formatierung in der gesamten Tabelle nur dann funktioniert, wenn in Spalte B, in jeder Zelle der Tabelle auch ein beliebiger Wert steht. In meinem Fall befinden sich hier jeweils die Bezeichnungen.
Das wiederum bedeutet, dass beim Hinzufügen einer neuen Zeile vorerst nicht der gewünschte Effekt eintritt, sondern erst nach befüllen der neuen Zelle in Spalte B. Es ist unterm Strich noch nicht perfekt, aber es lässt sich damit arbeiten.
Mit freundlichen Grüßen
Marcel Gröschel
Bitte keine Anfragen für einen Dreier – wenn ich zwei Personen gleichzeitig enttäuschen will, gehe ich zu meinen Eltern.
Ich unterrichte PowerQuery in einer internationalen Firma. Einige haben die englische Oberfläche eingestellt und damit auch das Dezimaltrennzeichen „.“ und die Datumsschreibweise „MM/TT/JJJJ“, einige Teilnehmer und Teilnehmerinnen „sprechen“ deutschen, also das Komma als Dezimaltrennzeichen und Datum in der Form „TT.MM.JJJJ“.
Beim Festlegen des Datentyps müssen nun einige auf „Dezimalzahl“ klicken, andere das Gebietsschema festlegen. Da ich verschiedene Übungsbeispiele mitgebracht habe, muss man entweder die eine oder andere Variante wählen:
Ich überlege: Wenn nun eine solche Datei mit einem PowerQuery-Zugriff ausgetauscht wird, wäre es doch sinnvoll IMMER das Gebietsschema der Quelle festzulegen, da es ansonsten zu Fehlern kommen kann:
Meine Eltern haben mir als Kind verboten den Schrank mit Putzmitteln zu öffnen. Das wirkt bis heute.
Was Menschen alles machen (wollen).
Eine Teilnehmerin in der Schulung fragte, wie man geöffnete Dateien sortiert.
Der Gedanke: sie öffnet mehrere Dateien – allerdings leider nicht in der von ihr gewünschten Reihenfolge – und sucht dann eine Datei. Das kann bei sehr vielen Dateien recht mühsam sein, wenn man mit [Alt] + [TAB] sich durch die Dateien bewegt:
Meines Wissens kann man nicht die Reihenfolge ändern.
Auch nicht die Reihenfolge, wenn man auf das Excelsymbol in der Taskleiste klickt:
Aber: Da in der Registerkarte Ansicht im Symbol „Fenster wechseln“ die Dateinamen alphabetisch sortiert sind, fand sie diese Lösung sehr attraktiv:
Elternabend. Die immer wiederkehrende Strafe für ungeschützten Sex.
Nicht aufgepasst!
Eine Teilnehmerin möchte wissen, in welcher Zeile und in welcher Spalte sich ein Wert befindet:
Nun: Schritt I: die Formel
=H18=A1:J14liefert eine Matrix mit Wahrheitswerten:
Und nun die Zeilennummer:
=MAX((H18=A1:J14)*ZEILE())Das ist definitiv falsch, wie man sofort auf den ersten Blick sieht:
Etwas überlegen – mir dämmert es – klar – die Formel muss natürlich lauten:
=MAX((H18=A1:J14)*ZEILE(A1:J14))Analog für die Spalte:
Ich habe im Homeoffice dem Paketboten aus Versehen nackt die Türe aufgemacht. Ich: „UPS.“ Er: „Nein, DHL.“
Menschen machen manchmal lustige Dinge.
Warum sie eine intelligente Tabelle nicht umbenennen dürfe, fragt eine Teilnehmerin in der Excelschulung.
Seltsam, denke ich: ein Tabellenblatt, eine intelligente Tabelle – Excel behauptet, dass dieser Name bereits vorhanden sei.
Ich werfe einen Blick in den Namensmanager:
Dort finde ich eine intelligente Tabelle und einen Namen. Was hat die Teilnehmerin gemacht?
Schritt 1: Wandle den Bereich in eine Tabelle um. Sie heißt nun Tabelle1:
Markiere die Liste und vergebe ihr einen Namen. Also nicht der Tabelle, sondern dem Bereich (hier: A1:F25)
Und so sieht man den Namen der Tabelle, aber nicht, dass ein anderer Bereich bereits mit dem Namen belegt ist, den man selbst gerne vergeben möchte … Perfide!
Papa, wie heißen diese Dinger, die Winterschlaf halten und die in Scharen rauskommen, wenn die Sonne scheint? – Motorradfahrer!
Ach wie doof.
VBA-Schulung. Ein Teilnehmer wollte JSON-Datein in Excel haben. Kein Problem, sagte ich und zeigte ihm PowerQuery.
Allerdings – nach einigen fragenden Blicken – stellten wir fest, dass in seinem Excel 2016 (anders als meines in Microsoft 365) noch kein JSON-Zugriff vorhanden ist.- Ärgerlich!
Witze über Tofu finde ich geschmacklos.
Irre! Ich erstelle in Desktop-Excel eine Tabelle:
und füge einige Zeilen ein:
Alles okay!
Ich lade diese Datei in teams auf den Sharepoint:
und füge einige Zeilen ein – padautz: es erscheinen einige Zellen in schwarzer Farbe:
Ich habe die Formatvorlagen durchgesehen, die bedingten Formatierungen, es gibt keine schwarze Farbe auf dem Tabellenblatt, er rührt auch nicht von der Formatierung der intelligenten Tabelle.
Erst wenn ich alle Formatierungen lösche und neu einschalte, verschwindet der Spuk.
Ich habe keine Ahnung, woher und warum sich Excel auf dem SharePoint in diesen Zellen die Hintergrundfarbe zieht …
Tausche Mittwoch Morgen gegen Freitag Abend.
Schöne Frage in der Excelschulung:
Mit PowerQuery wurde eine Abfrage erstellt. Diese enthält eine Liste. Wie kann man eine Dropdownliste mit einer Datenüberprüfung auf diese Liste erstellen, ohne dass die Daten nach Excel geladen werden?
Ich wusste keine Antwort …
Dass du deinem Mann in seinem Alter noch Knutschflecke verpasst hast, finde ich toll! – Würgemale, meine Liebe – das sind Würgemale.
Etwas irritiert bin ich schon. In einer PowerBI-Schulung erzählt mir ein Teilnehmer, dass er die Daten gerne in Excel hätte. Und dass er eigentlich gerne mit PowerQuery in Excel darauf zugreifen würde. Und nicht mit PowerBI. Aber die IT hätte ihm gesagt, dass es nicht gut ist, wenn mit PowerQuery so viele Abfragen auf die Datenbank abgesetzt werden. Besser wäre es, mit PowerBI auf die Datenbank zuzugreifen.
Ich bin mir nicht sicher, ob die Damen und Herren von der IT nicht wissen, dass PowerBI auch PowerQuery verwendet.
Bei Schwerelosigkeit kann man Pizza beidseitig belegen. Das ist dann auch keine einseitige Ernährung.
Excelschulung. Wir üben Funktionen. Eine Teilnehmerin ruft mich, weil eine Formel nicht funktioniert:
Die Formel:
=INDEX($B$2:$I$10;VERGLEICH(L2;§A$2:$A$10;0);VERGLEICH(M2;$B$1:$I$1;0))Ich habe drei Mal hinschauen müssen, bis ich das §-Zeichen anstatt des $-Zeichens entdeckt hatte.
Wenn ich das Gefühl habe nicht aus meinem Leben gemacht zu haben, schaue ich fünf Minuten RTL – dann geht’s wieder.
Im Gegensatz zum Gehirn meldet sich der Magen, wenn er leer ist.
Schöne Frage in der Excelschulung: Wir haben auf SharePoint mehrere Excelmappen, in denen der Autofilter eingeschaltet ist. Kolleginnen und Kollegen laden die Dateien runter, filtern, vergessen den Filter auszuschalten und – nun ist für den nächsten Kollegen der „alte“ Filter aktiviert. Ob man das einstellen könne, dass beim Schließen der Datei zwar der Autofilter gesetzt bleibt, jedoch nicht eine mögliche Filterung.
Leider gibt es dafür keine Einstellung, lautete meine Antwort – das müsse man programmieren. Ein langes Gesicht war die Folge.
Gerade meinen verheirateten Chef bei tinder gefunden. Ich bin schon so gut wie befördert.
Warum rechnet der XVERWEIS bei mir nicht, möchte ein Teilnehmer in meiner Excelschulung wissen:
Ich lasse die korrekt berechnete Zelle editieren:
Ich sehe den Fehler nicht. Er als ich den Teilnehmer bitte, eine andere, fehlerhafte Zelle mit einem Doppelklick zu editieren, sehe ich, dass von den acht Dollarzeichen eines fehlt. Und somit sind die beiden Spalten nicht gleich groß.
Kinderhelden sind unrealistisch: Benjamin Blümchen, ein sprechender Elefant. Bibi Blocksberg: ein hexendes Kind. Aber bei Bob dem Baumeister haben sie es übertrieben: ein pünktlicher und motivierter Handwerker!
Ich schmunzle, wenn Teilnehmerinnen oder Teilnehmer in meiner Excelschulung mich darauf aufmerksam machen, dass sie einen Fehler haben, ihn aber nicht finden. Und das, obwohl sie es GANZ GENAU SO GEMACHT HABEN, wie ich. Eben nicht, denke ich mir.
Wir üben die Funktionen der Kategorie „Nachschlagen und verweisen“: INDEX. Ich habe auf dem ersten Tabellenblatt „Entfernungen“ einen Entfernungsliste (von A nach B). Ei Wert soll Zeilen und ein anderer Spaltenweise gesucht werden. Auf einem zweiten Blatt beginne ich zu tippen:
=INDEX
und wechsle auf das erste Tabellenblatt, wo ich den Bereich markiere und fixiere ([F4]):
Dann drücke ich das Semikolon, um danach auf dem zweiten Blatt den zweiten Wert einzugeben. Allerdings vergisst die Teilnehmerin das Semikolon, klickt auf das andere Blatt, was Excel interpretiert als: „Sie möchte sicherlich einen anderen Blattnamen haben …“ Eben – das führt zu einem Fehler:
Dafür, dass Katzen so reinliche Tiere sind und sich so gerne putzen, engagieren sie sich erstaunlich wenig im Haushalt.
Schade. Ich hätte es gut brauchen können.
In intelligenten Tabellen kann man – anders als bei der Datenüberprüfung – leider nicht die Überschriften mit einer ausgelagerten Liste verknüpfen …
Mein Mann sagt, er könne die Witze über alte Männer nicht mehr hören. Vielleicht sollte ich doch lauter sprechen.
Hallo.
Mir ist es gerade passiert, dass mein erstelltes Liniendiagramm plötzlich nicht mehr sichtbar war.
Dann habe ich erst versucht, ein neues Diagramm zu erstellen und da war mein ursprüngliches Diagramm für kurze Zeit wieder sichtbar. Sobald ich jedoch bei dem neuen Diagramm auf OK drückte, war das alte wieder weg.
Ich bin dann mal kurz auf den Menüpunkt „Ansicht“ und dort auf „Umbruchvorschau“ gegangen und das ursprüngliche Diagramm war wieder da. Bei Rückkehr in die Normalansicht blieb es dann auch sichtbar und ich konnte das neue Diagramm löschen.
Excel scheint da irgendeinen Bug zu haben …
####
Ich kenne diese Anomalie nicht – sorry, Elke – dazu kann ich leider nichts sagen …
Wach? – Nein. Nennen wir es: „ansprechbar und in der Lage, über die Uhrzeit zu jammern.“
Lustige Frage eines Teilnehmers der letzten Excelschulung (ich habe hier schon darüber geschrieben):
Kann man eine intelligente Tabelle mit der Funktion SEQUENZ ausstatten, so dass ihre Größe sich dynamisch ändern kann:
Die Antwort:
Leider nein:
Stell dir vor es gibt Grieg und keiner hört hin.
Guten Morgen René,
ist dir das schon begegnet?
Hat heute ein Kollege rumgeschickt mit der Bitte um Hilfe, ich selbst hab das aber auch zum ersten Mal gesehen:
Meine These lautet, dass der Gegenüber (also der Verfasser der Kommentare) Microsoft 365 nutzt, mein Kollege hingegen Home&Business 2019, also eine etwas ältere Kauflizenz und dass die Ursache der Fehlermeldung in Kompatibilitätsproblemen der beiden Versionen liegt.
Die Kommentare konnten auch wirklich nicht angezeigt werden.
Meine Lösung war, die Datei auf OneDrive zu speichern und mit der Web-Version von PowerPoint zu öffnen, was dann auch funktioniert hat.
Nur irgendwie ist das schon doof, wenn bei sowas Simplen wie der Kommentarfunktion schon Probleme zwischen den beiden Versionen entstehen (Absicht von Microsoft um Menschen zur Abo-Version zu drängen?)-
Danke dir und viele Grüße, Dominic
###
Hi Dominic,
es bleibt spannend: NEIN – DAS habe ich noch nicht gesehen.
Zugegeben: ich arbeite selbst wenig mit PowerPoint und schule es auch sehr selten.
Liebe Grüße
Rene
Gibt es eigentlich Medizin gegen Ohrwürmer? – Ja. Griechischer Wein.
Lieber René,
nach langer Zeit melde ich mich mal wieder mit einem kleinen Rätsel bzw. mit so einem kleinen „Bin ich doof?“-Moment. Es geht um die Schriftfarbe in PowerPoint
Das erste Problemchen war, dass ich daran gescheitert bin, mittels .TextFrame.TextRange.Font.Color.RGB die Schriftfarbe eines (bzw. natürlich mehrerer) Textfelder auszulesen – es wurde immer der anscheinend unsinnige Wert –2147483648 ausgegeben.
Farbenblind? Naja, irgendwie war das schon aber richtig (Beispielfolie im Anhang) – sieht grün aus.
Ich tippe in das Textfeld und klicke unter Start à Gruppe „Schriftart“ à Schriftfarbe à weitere Farben à Benutzerdefiniert und schaue mir den RGB-Code an: (0, 176, 80). Soweit so gut.
So, jetzt klicke ich aber auf den Rahmen des Textfeldes, da erscheint auch eine schöne Schaltfläche, die „Schriftfarbe“ heißt:
Hier ist jetzt ein RGB Wert von (0,0,0) zu sehen:
Ich würde jetzt mal spekulieren, dass dieses schwarz auch hinter der Ausgabe von –2147483648 steckt, die .TextFrame.TextRange.Font.Color.RGB ausspuckt.
Ist das normal, dass die eine Schriftfarbe nichts mehr der anderen zu tun hat? Ich bin verwirrt.
Viele Grüße, Dominic
###
Hi Dominic,
hübsch! Ich erkläre in PP-Schulungen immer, dass es drei Status bei Objekten gibt: entweder du bist „drauf“ (hast das Objekt markiert), oder „drin“ (im Text, in der Tabelle, im Diagramm, …) oder gar nicht markiert – also auf der Folie.
Richtig: wenn du den Rahmen markierst, ist die Schriftfarbe schwarz:
Wenn du dagegen IN dem Text bist, ist die Schriftfarbe „dein“ Grün:
Das heißt: du darfst nicht die Farbe des Rahmens auslesen (schreiben schon, bspw.: ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Font.Color = RGB(255, 0, 0) ), sondern eines Zeichens:
MsgBox ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Characters(1).Font.Color
Liebe Grüße
Rene
###
Guten Morgen René,
jaaa…. „.characters(1)“ hat gefehlt – jetzt klappt alles, wie es sollte.
Vielen Dank für diese Lösung.
Viele Grüße, Dominic
Ey, hast du TIK TOK? – Ja, es ist kurz vor halb drei.
Hallo Martin
Bei mir erscheint seit geraumer Zeit auch diese ominöse Meldung mit dem „Das wird nicht funktionieren bla bla bal“ Allerdings erhalte ich die Meldung wenn ich versuche in einer Tabelle eine Spalte einzufügen und es ist egal ob links, oder rechts.
Perfide ist, dass das Ganze ab der Spalte BQ mit allen Tabellen funktioniert.
Da dachte ich, da muss irgendwo in einer „Zombie Zelle“ etwas stehen, was diese Funktion ab der Spalte A bis BQ stört. Also habe ich versucht die Tabellen in den betroffenen Spalten einfach nach rechts, > Spalte BQ zu verschieben. Dies mit der Absicht danach alle Spalten A bis BQ löschen zu können:
Mit einigen Tabellen hat das funktioniert, doch plötzlich geht das Einfügen von Spalten in einer Tabelle welche ab der Spalte BQ steht, auch nicht mehr und mir gehen langsam aber sicher die Ideen aus, was die Ursache sein könnte und bin schon ziemlich genervt.
Hast Du vielleicht eine Idee, was die Ursache sein könnte?
###
Hallo Herby,
nein – DAS kenne ich nicht. Ab und zu friert mein Excel ein; ich klicke wild drauf rum – aber meistens ist der „Schaden“ behoben, wenn ich Excel schließe und wieder öffne.
Seltsam.
Liebe Grüße
René
###
Die Kaffeemaschine ist eben durchgelaufen. Ohne zu grüßen. Dieses arrogante Melittafiltertütenflittchen!
Stört mich das? ja – das stört mich!
Die hellgrauen Balken stehen über die dunkelgrauen. Also: auf die Suche nach dem Übeltäter: Füllfarbe, Muster, Linie, bedingte Formatierung …
Leg dich nicht mit Zucker an – er ist raffiniert!
Es geht weiter.
Unterschiedliche Formen in Outlook (Partner/in, Assistent(in), Manager*in, Organisator*in und viele gar nicht gegendert …
Zurück bleiben traurige Teilnehmer.
UUUAAAAAHHHH!
Wie war dein Wochenende? – Voll geil! – 3er probiert! – Wie war’s? – Der Bademeister musste mich schreiend runtertragen.
Warum findet Excel „Bielefeld“ nicht?
Ich sehe den Text doch in Zelle B19.
Der Grund: In B19 steht nicht „Bielefeld“, sondern eine Formel:
Stimmt – ich muss die Option „Suchen in“ umschalten von „Formeln“ auf „Werte“. Dann wird auch „Bielefeld“ gefunden:
Wenn man nach dem Tod verbrannt und in eine Sanduhr gefüllt wird, dann könnte man noch an Spieleabenden mit seiner Familie teilnehmen.
Lieber Herr Martin, ich habe eine „für mich“ komplexe Frage und finde keine Antworten im Internet. Kann xls 2016 bedingte Formatierungen für relative Bezüge herstellen? Ich möchte dass mein Wert heute abhängig vom Wert gestern, als bedingte Formatierung rote oder grüne Pfeile einsetzen. Wenn Wert höher als gestern -> grüner Pfeil. Wenn Wert niedriger als gestern -> roter Pfeil. Über die Wenn-Funktion kann ich ja keine bedingte Formatierung einbauen. Wissen Sie was ich tun kann ausser die Werte einzufärben, was ich schon tat? Ich würde zusätzlich gerne eben die Pfeile einbauen. Viele liebe und sonnige Grüsse
###
Hallo Frau Pap,
die bedingte Formatierung kann bei Symbolen leider keine Formeln mit relativen Bezüge verwenden. Vor einigen Jahren hat mich ein Freund darauf aufmerksam gemacht; ich habe ihn nicht geglaubt und es bestätigt. Ich habe einen Artikel darüber geschrieben:
Ich esse nicht. Ich schenke Kalorien ein Zuhause.
Oh, nö, Leute, echt jetzt.
Gestern sah die Registerkarte „Formeln“ in meinem Excel so aus:
Der Name Manager*in wurde zurückübersetzt. Allerdings in den Eigenschaften der Datei befindet sich noch ein*e Manager*in.
Dafür beglückt mich Outlook mit folgender Meldung:
Wenn es die Linguisten krachen lassen, veranstalten sie Komma-Saufen.
excel 😀
Hallo Herr Martin, ich verzweifle an einer wahrscheinlich simplen Excel Aufgae im Zuge meines Onboardings.
1) Fill in the question mark: 2,7,17,?,41,53,67,79
Das ist die Aufgabe. Ich gehe davon aus, dass es eine Art Logiktest ist, und excel die möglichkeit besitzt das ? durch die korrekte Zahl zu ersetzen?
Haben Sie hier ggf. einen Tipp für mich? Ich wäre Ihnen sehr dankbar und Sie würden qusi meine Woche retten. 😀
Alles Gute für Sie.
Viele Grüße aus Hamburg
#
Hallo Frau F.,
die Lösung lautet 29.
Was bekomme ich für die Erklärung?
#
Hahahaha 😀 meine Hochachtung!
Und wie ist die Excel Formel dazu? Dann wären Sie mein Held des Tages!!!
#
keine Excel-Formel.
Werfen Sie mal einen Blick auf die ersten Primzahlen:
2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79, 83, 89, 97.
Und nun streichen Sie jeweils zwei weg …
#
Verrückt! Mit Zahlen habe ich es leider nicht so, aber das sieht sehr logisch aus!
Da das nun aber eine >Excel Skill Aufgabe ist. Frage ich mich, was möchte der Prüfer hier von mir. Irgendwas mit Excel muss die Frage doch zu tun haben.-
Zumal Sie in einem Excel Sheet gestellt ist
#
Hallo Frau F.,
erzeugen Sie zu den Werten ein Diagramm und lassen sich eine Trendlinie anzeigen. Der polynomische Trend approximiert am besten. Auch hier lautet die Lösung ungefähr 29.
Werbung von der Bank: „Legen Sie Ihr Geld in Immobilien an!“ – Prima Idee! Ich kaufe mir gleich am Montag mit meinem Ersparten ein Eigentumszelt.
Ich erstelle ein Add-In für eine Firma. Es soll eine große Liste per Knopfdruck in Einzelteile zerlegen und diese an bestimmten Stellen auf der Festplatte speichern.
Dazu benötige ich eine eindeutige Liste der Kategorien:
Ich überlege: da die Firma Excel 2016 einsetzt, hat sie noch nicht die Funktion EINDEUTIG. Also erzeuge ich per Programmierung eine Pivottabelle und erhalte so eine (sogar sortierte) Liste der einzelnen Kategorien:
Sub MachePivot()
Dim xlBlattAktiv As Worksheet
Dim xlBlattHilf As Worksheet
Dim xlPivotCache As PivotCache
Dim xlPivotTabelle As PivotTable
Dim lngZeilen As Long
Dim lngSpalten As Long
Set xlBlattAktiv = ActiveSheet
Set xlBlattHilf = ThisWorkbook.Worksheets.Add
lngZeilen = xlBlattAktiv.Range("A1").CurrentRegion.Rows.Count
lngSpalten = xlBlattAktiv.Range("A1").CurrentRegion.Columns.Count
Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C" & lngSpalten, _
Version:=8)
Set xlPivotTabelle = xlPivotCache.CreatePivotTable( _
TableDestination:=xlBlattHilf.Range("A1"), _
TableName:="RenesPivot2", _
DefaultVersion:=8)
xlPivotTabelle.ColumnGrand = False
xlPivotTabelle.RowGrand = False
With xlPivotTabelle.PivotFields("Kategorie")
.Orientation = xlRowField
.Position = 1
End With
End Sub
Ich teste – und: padautz: Fehler!
Ich brauche eine Weile, bis ich die Ursache finde. In der Liste gab es zwei Mal eine Spalte mit der Bezeichnung „Kommentar“. Excel 2016 schafft es nicht, die Spalten für die Pivottabelle umzubenennen (wie neuere Excelversionen):
Also: dann grenzen wir den Bereich doch ein!
Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C4" , _
Version:=8)
Geht doch:
Eine Bekannte meinte, ich solle froh sein, dass meine Kinder noch so klein seien. Ihr Sohn sei 16 und würde kiffen. „Dann schläft er wenigstens durch“ ist als Reaktion unangebracht. Das weiß ich jetzt.
Warum machen die das?
Ich entdecke die Seite
https://de.wikipedia.org/wiki/Liste_der_britischen_Premierminister
Ich greife mit Powerquery auf die Seite zu und lasse mir die Namen und die Datumsangaben (absteigend sortiert) zurückgeben:
Mit der Funktion TEXTTEILEN will ich die Datumsangaben trennen:
=TEXTTEILEN(Die_Liste_der_Premierminister__seit_1721__Bearbeiten___Quelltext_bearbeiten[@Amtsantritt];" ")und bin über das Ergebnis verblüfft:
Tag und Monat werden nicht getrennt. Ich ahne es. Ich ermittle den Code des dritten Zeichens der Zelle C2:
=CODE(TEIL(C2;3;1))Das Ergebnis lautet 160. Anders dagegen das zweite Leerzeichen, also Zeichen Nummer 8:
=CODE(TEIL(C2;8;1))Hier lautet das Ergebnis 32, also Leerzeichen, während es sich beim ersten Blank um ein geschütztes Leerzeichen handelt. Warum das? Soll beim Verschieben der Seite Tag und Monat nicht getrennt werden? Was vielleicht auf der Internetseite „schön“ aussieht, erweist sich als Ärgernis bei der Weiterverarbeitung der Daten. Oder will wikipedia nicht, dass man seine Daten weiter benutzt?
Übrigens: Wählt man in PowerQuery den Befehl „Spalte teilen“, so schlägt PowerQuery #(00A0) als Trennzeichen vor …
Habe meinen Wecker heute auf die Arbeit mitgenommen. Damit er sieht, wozu er mich jeden Tag nötigt.
Irre!
Wenn man mit PowerQuery auf eine „ältere“ XLS-Excelmappe zugreift, werden folgende Spalten angezeigt:
Die drei Spalten (mit ihren Informationen) Item, Kind und Hidden fehlen.
Würde man eine XLS-Datei in XLSX umbenennen, wäre das Ergebnis das Gleiche wie bei XLS:
Wer macht denn so etwas? Und: DAS würde man in Excel doch sofort bemerken.
Ich erhalte vorgestern die Frage, warum das PowerQuery-Tool, das ich für die Firma erstellt habe bei einer Datei nicht läuft. Ich stelle fest – obwohl die Datei vom Format XLSX ist, werden nur die beiden Spalte Name und Date angezeigt, nicht jedoch Item, Kind und Hidden. Warum?
Ich gehe auf die Suche.
Ich stelle fest, dass die im openXML-Format verwendete interne Datei app.xml (im Ordner docProps) folgendermaßen aussieht:
Wenn ich die Datei, die von Apache POI erstellt wurde, öffne, speichere und schließe, sieht diese XML-Datei so aus:
SO jetzt jede XLSX-Datei aus, die von Excel erzeugt und in Excel gespeichert wurde.
Das bedeutet: das (umstrittene) Werkzeug Apache POI produziert XLSX-Dateien, die nicht genau der Spezifikation von Microsoft entsprechen. Ist das schlimm?
Ja, weil mein Werkzeug auf die Spalte „Kind“ zugreift und diese nicht findet …
Männer warten auf die perfekte Frau. Playboy ist schuld. Frauen warten auf den perfekten Mann. Disney ist schuld.
Nicht aufgepasst. In einer Liste existiert eine Datenüberprüfung. Die Quelle der Liste ist jedoch keine „feste Liste“, sondern liegt in einem Zellbereich:
Per VBA kopiere ich diese Liste auf ein anderes Tabellenblatt und wundere mich, warum die Datenüberprüfung nicht mehr funktioniert. Die Antwort: der Verweis wird auf das neue Blatt gesetzt …
Ich brauche heute einen zweiten Kaffee, der nachguckt, warum der erste seine Arbeit nicht macht.
Schade. Die Funktion SORTIERENNACH listet beim ersten Parameter „Sortierreihenfolge1“ die beiden Möglichkeiten „Aufsteigend“ und „Absteigend“ auf.
Beim zweiten Parameter „Sortierreihenfolge2“ leider nicht mehr – hier muss man sich die Zahl des ersten gemerkt haben:
Alter, wenn du mit dem Finger übers Handy wischst, löst sich ein Teil vom Display. Krass! – Das ist ein Buch, Kevin.
Irgendwie doof. Warum nennt Microsoft die Parameter xlToRight und xlToLeft, aber xlDown und xlUp in der Funktion End? Das führt leicht zu Tippfehlern, wenn man nicht genau bei IntelliSense hinschaut:
„Ich putze, bügle, koche und räume auf – ich komme mir vor wie Aschenputtel.“ – „Ich habe dir ja gesagt, dass das Leben mit mir wie im Märchen für dich sein wird.“
Ein Dankeschön an Mark Risner. Er hat die Fehlermeldung „Wir haben alle Daten neben Ihrer Auswahl untersucht und konnten kein Muster zum Ausfüllen mit Werten erkennen.“ erhalten.
Was hat er gemacht: mehrmals hintereinander das Ergebnis der Blitzvorschau gelöscht und erneut die Blitzvorschau aktiviert. Irgendwann funktionierte sie nicht mehr.
Ich konnte diesen Fehler und diese Fehlermeldung leider nicht reproduzieren …
Mein Ex hat angerufen – Was wollte er? – Ich habe nicht abgenommen. – Das sehe ich, aber was wollte dein Ex?
Erstellt man in Pivottabellen ein Stripset, wird es nur angezeigt, wenn die Option „Gebänderte Zeilen“ aktiviert ist.
Erstaunlicherweise verschwinden die Farben auch dann, wenn keine Werte vorhanden ist – das heißt: man kann mit den Stripsets keine gruppierte Liste formatieren:
Danke an Christian Steckmann für diesen Hinweis.
„Ene, mene, Miste, es rappelt in der Kiste.“ – Warum ich auf Beerdigungen nicht mehr die Lieder aussuchen darf.
Eigentlich wollte ich heute zu einem anderen Thema schreiben. Aber gestern hat mir Mark folgendes Foto geschickt:
Ich habe gelacht und gedacht – „oh ja – wie oft ist das richtig!“
Der Körper, den ich möchte, passt einfach nicht zu dem Essen, das ich liebe.
Ich vergesse es immer wieder: die Arrayfunktionen kann man leider nicht per Doppelklick auf das Kästchen herunterziehen:
Keiner hat so viele falsche Hoffnungen wie Eltern, die Bücher in den Urlaub, den sie mit der Familie verbringen, mitnehmen.
Ui – da muss Microsoft noch einmal ran! Beim letzten Update wurde in PowerPoint aus den Führungslinien:
nun die Beschriftung in der Registerkarte „Ansicht“ falsch angezeigt:
Ich fühle mich krank. Ich habe die Symptome gegoogelt und herausgefunden, dass die Zylinderkopfdichtung die Ursache daran hat.
Die neuen Arrayfunktionen, die dynamisch in mehrere Zellen die Ergebnisse schreiben und die „starren“ (intelligenten) Tabellen, arbeiten nicht zusammen. Will man die Funktion TEXTTEILEN auf eine Zelle (also auf eine Spalte) anwenden, um die Inhalte zu trennen:
lautet das Ergebnis #Überlauf, da diese Funktion keine neuen Spalten generieren kann:
Oh, süß, der Kleine – ist das Ihrer? – Es gibt Sätze, die hört man im Kindergarten weitaus lieber als in der Sauna.
Microsoft hat es nun doch wahrgemacht: die Begriffe werden gegendert. Wird es auch bald männliche Zellen, Spalten und Zeilen geben? Und einen weiblichen Nachfolger, Autor und Thesaurus?
Muss mein Leben neu strukturieren. Bin in den Exceljahren.
Ich habe gerade ein Buch gesehen: „Leben mit 5 Euro am Tag.“ Kosten des Buches: 19,99 Euro. Genau mein Humor!
Wollen die im Ernst? Ich dachte, das sei ein Scherz …
https://techcommunity.microsoft.com/t5/deutsch/translation-into-german-of-the-designation-name-manager-in-excel/idi-p/3603239?fbclid=IwAR0cFG6sXssV1si1pElUL3PWWOBBGJZEWa7bUQw7zqsoLL58mXquZjUOZko
Danke an Mourad Louha für den Hinweis und danke Mourad, dass du dich GEGEN das Gendern aussprichst.
Schon fast September und noch immer kein Spekulatius im Supermarkt
Die gute Nachricht: ich bekomme den oberen Knopf meiner superengen Jeans endlich wieder zu. Die schlechte Nachricht: Ich habe sie nicht an.
In Excel erstellt man einen Zeilenumbruch in einer Zelle mit der Tastenkombination [ALT] + [Enter]. Der dahinterliegende Code hat die Nummer 10. Importiert man eine Liste mit Zellen mit Zeilenumbrüchen nach PowerQuery, sieht man schnell, dass #(lf), also Linefeed diesem Zeichen entspricht.
Aha, denke ich mir: es wäre doch schön, wenn eine Liste nicht mit Semikola getrennt wären:
Ich öffne den Dialog und trage statt Semikolon in der Kategorie „benutzerderfiniert“ #(lf) ein;
Das Ergebnis irritiert:
PowerQuery hat den Code geändert in:
= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), "#(#)(lf)"), type text})Natürlich könnte man es per Hand ändern in „#(lf)“
Oder durch die Funktion
Character.FromNumber(10)ersetzen:
= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), Character.FromNumber(10)), type text})Schließlich entdecke ich, dass in der Kategorie „benutzerdefiniert“, dass man die Texte „mithilfe von Sonderzeichen verketten“ kann. Und dort findet sich auch der Zeilenvorschub. Und dieses #(lf) wird von PowerQuery auch nicht geändert.
Ich will nicht sagen, dass mein Nacken zu viel Sonne abbekommen hat, aber hinter mir bleiben Menschen stehen und warten auf Grün.
Lieber Rene, Du hast mir vor einiger Zeit mit einem Tipp zu Snagit geholfen. Jetzt habe ich eine Frage, die evtl. blöd erscheint..aber ic bekomme es nicht hin. Vielleicht hättest Du 2 Min. Zeit mir zu helfen?
Unter der Registerkarte Daten habe ich im Menüband auf der rechten Seite einfach zu viel leere von leerer Fläche und gleichzeitig wird sieht man z.B. das Symbol für Blitzvorschau ohne Bezeichnung.
Ich habe Dir einen Screenshot gesendet. Kann man irgendwie diesen leeren Bereich in der Registerkarte Daten loswerden?
Ich wäre Dir wirklich dankbar für einen Tipp
Liebe Grüße Max
Hallo Maximilian,
die Antwort kurz und knapp: NEIN.
Wenn du in die Definition der Symbole reinschaust – dort gibt es nur die Größen small und normal. Wenn ich beispielsweise einen oder zwei small-Symbole verwende, ist noch Platz für eine drittes nach unten. Das hatte ich beispielsweise mal in folgender Gruppe für Programme, die ich für eine Sparkasse gebaut habe:
Die Breite der Symbole richtet sich nach dem Text. Ist er zu lang, werden die Gruppen zusammengefasst. Wenn noch Platz nach rechts übrig ist … Tja – keine Chance. Auf einem anderen Bildschirm wird das dann anders berechnet …
Jedes Jahr pünktlich zu Weihnachten kommt dieser beschissende Coca Cola-Truck vorbei. Jetzt, bei dieser Affenhitze hat ihn noch keiner gesehen!
hallo René,
so wie du es geschrieben hast, habe ich das Makro aufgezeichnet: ändere die Skalierung der y-Achse. Allerdings erhalte ich einen Fehler, wenn ich das Makro laufen lasse:
Hallo Nadine,
wahrscheinlich liegt es daran, dass du das Diagramm nicht markiert hast? (ActiveChart)
Liebe Grüße
Rene
Heute morgen habe ich auf der Eingangstreppe eine Männersocke gefunden. Wo bist du, mein Aschenputtel?
Wo kommen die ganzen Deos her? Aus dem Spraywald?
Hallo René,
Habs.
Allerdings funtkioniert die Berechnung nicht…
Hallo Nadine,
in PowerQuery musst du für „hoch“, also Potenz schreiben:
Number.Power
PowerQuery „spricht“ US-Amerikanisch, also 0.3 statt 0,3.
Und den natürlichen Logarithmus musst du schreiben:
Number.Ln
Liebe Grüße
Rene
„Hoch die Hände, Monatsende!“. Frank J; Bankräuber und knapp bei Kasse.
Nervig!
Ich erhalte eine Excelmappe von einem Teilnehmer zugeschickt – er möchte, dass wir darüber reden.
Ich öffne die Datei und werde aufgefordert, mich anzumelden. Eine Verweigerung bewirkt, dass die Datei nicht geöffnet wird. Also erneut: Datei öffnen, meine Mailadresse eingeben:
Ich werde auf das Firmenportal weitergeleitet, wo ich mich erneut authentifizieren soll.
Kann ich nicht – ich breche ab. Erneut muss ich mich anmelden. Das Spiel wird vier Mal wiederholt. Dann bin ich drin.
Ich entdecke, dass in der Datei zwei Namen auf andere Dateien verweisen, die auf dem SharePoint der Firma liegen.
Diese Namen werden nicht verwendet. Ich lösche sie. Schließe die Datei, öffne sie wieder und: erneut muss ich mich mehrmals „anmelden“. Ich wühle weiter und werde fündig. In den Informationen der Datei befinden sich weitere Verknüpfungen:
Zum Glück kann ich sie löschen und speichern. Beim nächsten Öffnen entdecke ich: der Spuk ist vorbei!
Meine Frau meinte, ich solle ein paar Kaffeebohnen malen. Jetzt ist es auch wieder nicht recht.
VBA-Schulung. Wir üben den Makrorekorder. Ändert man die Schriftart über das Dropdownsymbol in der Registerkarte „Start“,
zeichnet der Makrorekorder auf:
Sub Schriftart()
'
' Schriftart Makro
'
'
With Selection.Font
.Name = "Algerian"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End SubAlso: der ganze Dialog (oder genauer: die Registerkarte „Schrift“ des Dialogs „Zeichen formatieren“) wird aufgezeichnet.
Ein Teilnehmer fragt, was denn aufgezeichnet wird, wenn man im Dialog „Zeichen formatieren“ die Schrift ändert und dies aufzeichnet. Peinlich: ich weiß es nicht. Wir zeichnen auf. Das gleiche Ergebnis – auch hier wird nicht der gesamte Dialog (wie beispielsweise beim „Seite einrichten“) aufgezeichnet, aber auch nicht nur der eine Befehl, der geändert wurde, sondern sämtliche Befehle des Registers:
Flensburg ist wie Payback sammeln – nach acht Punkten gibt es ein Fahrrad.
Die neuen Textfunktionen sind noch nicht ganz bei mir angekommen:
Sie sprechen noch ein bisschen Englisch; aber – was schlimmer ist – sie arbeiten noch nicht korrekt. TEXTTEILEN sollte alle Textteile liefern.
Okay, okay, verstanden – diese Arrayfunktion darf nicht (anders als TEXTVERKETTEN) auf den ganzen Bereich angewendet werden, sondern nur auf eine Zelle. Und dann runterziehen!
Aber sie spricht immer noch Englisch mit mir …
Nachts bei 35° zusammen im Bett liegen – das schweißt zusammen …
Ich will nicht sagen, dass ich ein anspruchsvoller Beifahrer bin. Ich bringe lediglich Bremswünsche, Überholvorschläge und Schaltempfehlungen ein.
Geschmunzelt habe ich gestern schon.
Eine Schulung. Ein Grafikprogramm. Und es kommt – wie so häufig – die Frage auf: „Ach, wo ich mich gerade mit Ihnen unterhalte – ich habe da mal eine Excelfrage. Wir haben vom Kunden eine Datei erhalten, da ist ein Schutz drauf. Können Sie den wegmachen?“
Ich lasse mir die Datei zeigen:
Und dann: sehen Sie, wenn ich nun den Tippfehler in Zelle B11 ändern will, dann darf ich das nicht. Da ist ein Schutz drauf. Sie zeigt es mir:
Ich lache und bitte sie in der Datenüberprüfung nachzusehen. Wir werden fündig: die Werte werden von einem ausgeblendeten Tabellenblatt „geholt“:
Das Tabellenblatt „Daten“ kann man einblenden – tatsächlich: dort finden sich in A2:A4 die Daten:
Wir ändern den Tippfehler, blenden das Tabellenblatt wieder aus. Natürlich ist sie traurig, dass die Fehler auf dem anderen Blatt nicht sofort geändert wurden. Aber mit dem Befehl „Ersetzen“ funktioniert das schnell.
Was mich DANN sehr amüsiert hat, war ihre Erzählung (sie zeigte mit den Chatverlauf in teams), dass sie bei der IT Ihrer Firma nachfragte, ob diese nicht die Ursache des Problems kenne. Sie hatte gebeten, man möge die Originaldatei ohne Schutz kommen lassen. Oder: die Datei noch einmal erstellen!?! Ich war schon sehr irritiert und habe einen Grundkurs Excel in Ihrer Firma angeboten.
Jawoll! Bewerft den Bastard mit Dreck und Erde! – Oma, bitte! Wir sind hier auf einer Beerdigung!
Amüsiert.
VBA-Schulung. Ich lasse während der Schulung kleine „Lösungen“ programmieren. Ich schaue den Teilnehmern und Teilnehmerinnen über die Schulter. Bei einem der Teilnehmer finde ich die Codezeilen:
i = 3
MsgBox ActiveCell(i)Die ActiveCell ist B1, ActiveCell(3) liefert den Inhalt der Zelle B3, hier: „Peking“:
Das heißt: ActiveCell(1) meint die Zelle selbst, ebenso wie ActiveCell(1, 1). Dies ist also keine Kurzschreibweise für ActiveCell.Offset(1, 1), denn damit wäre C2 gemeint. Auch nicht für Cells(1, 1) – das wäre A1.
Diese Schreibweise gehört dann zur Kategorie ActiveCell.Range(„A1“), womit B1, also die aktive Zelle gemeint ist (A1 ist hier ein relativer Bezug).
Ich würde schreiben:
Cells(3, 2)
oder
Range(„B1“).Offset(2, 0)
wenn ich B3 meine.
Und: die Eigenschaft „Value“ nicht vergessen!
Und: noch angeben, auf welchen Tabellenblatt die Zelle liegt.
Es geht aber auch anders. Wer denn möchte …
Gerade gelesen: Das Gehirn räumt im Schlaf auf. Von wegen! Wenn ich abends ins Bett gehe, ist die Bude morgens noch unordentlich.
VBA-Schulung. Wie aufmerksam manchmal Teilnehmerinnen und Teilnehmer sind. Und erstaunlich, was mir manchmal entgangen ist.
Warum ist das „x“ zum Schließen des Meldungsfensters bei den Parametern vbAbortRetryIgnore und vbYesNo ausgegraut, also nicht aktivierbar,
während es bei den anderen vier Parametern aktiv ist? Ich weiß es nicht …
Du weißt, dass du alt wirst, wenn in einem Horrorfilm nervige Teenager umgebracht werden und du den Mörder verstehen kannst.
Erstaunlich. In einem Bereich stehen Datumsangaben. In einer Zelle steht eine andere Datumsangabe. Sie soll „gesucht“ werden. Weder ZÄHLENWENN noch VERGLEICH haben ein Problem damit:
Auch der VBA-Befehl CountIf
datDatum = ThisWorkbook.Worksheets(1).Range("F19").Value
MsgBox Application.WorksheetFunction.CountIf(ThisWorkbook.Worksheets(1).Range("F1:F16"), datDatum)arbeitet problemlos. Allerdings liefert MATCH einen Fehler:
Der Befehl
Application.WorksheetFunction.Match(datDatum, ThisWorkbook.Worksheets(1).Range("F1:F16"), 0)klappt nicht.
Auch nicht in:
Application.WorksheetFunction.Match(ThisWorkbook.Worksheets(1).Range("F19").Value, _
ThisWorkbook.Worksheets(1).Range("F1:F16"), 0)Wahrscheinlich ist die Ursache folgende: Excel kennt nur die Datentypen Text und Zahl, während VBA verschiedene Datentypen für Zahlen kennt (Integer, Long, Double, …), aber auch für Datum (Date).
Also: Flux den Wert der Variablen datDatum in eine Long-Zahl konvertieren (CLng(datDatum) ) – und schon klappt es wieder:
Application.WorksheetFunction.Match(CLng(datDatum), ThisWorkbook.Worksheets(1).Range("F1:F16"), 0)In meinem Alter habe ich schon so viel Elend gesehen – da machen Selfies auch nichts mehr aus.
Hallo René,
Kennst Du Exelcat?
https://www.migros.ch/de/brand/exelcat
… fragte mich ein Bekannter.
Ich schaue nach und lache:
Was machst du beruflich? – Ich helfe Menschen! – Bist du Arzt? – Nein: Bierfahrer.
Manchmal nervt Excel aufgrund merkwürdiger Einstellungen.
Manchmal nerven Anwenderinnen und Anwender, die merkwürdige Dinge machen.
Es gibt noch andere nervige Quellen.
Excelschulung. Eine Teilnehmerin möchte einen Zeitstempel haben. Sie möchte in eine Spalte ein Namenskürzel eintragen, mit der Folge, dass in der Spalte daneben die aktuelle Uhrzeit eingetragen wird. Allerdings soll diese nicht verändert werden. Das heißt: HEUTE() wird beim ersten Mal eingetragen; steht bereits ein Datum in der Zelle, wird das Datum mit sich selbst überschrieben:
WENN(B2="";HEUTE();B2)Das Ganze wird „ummantelt“ von der Prüfung des Namenskürzels:
=WENN(A2<>"";WENN(B2="";HEUTE();B2);"")Also so:
Damit dies funktioniert, müssen die Iterationen (in den Optionen) aktiviert sein – eine Iterationszahl von 1 genügt:
„Genau so habe ich gemacht“, insistiert die Teilnehmerin – aber am nächsten Tag geht es nicht mehr.
Wir schauen nach: Excel beenden und erneut öffnen: Klappt!
Rechner herunterfahren und neu starten: die Option „Iterative Berechnung aktivieren“ wurde deaktiviert. Wahrscheinlich von einem Script von der IT, das beim Starten des Rechners Einstellungen ändert.
Auch die IT-Abteilung einer Firma kann nerven!
Hat die Schlammpackung deine Frau schöner gemacht? – nur kurz, das Zeug geht ab!
Klickt man in Excel auf Datei / Speichern unter, wählt einen Dateinamen einer anderen Datei, die bereits existiert, wird man gefragt, ob man die Datei ersetzen möchte. In VBA liefert der Befehl SaveAs die gleiche Meldung:
Jedoch nicht die Methode SaveCopyAs. Dort wird die vorhandene Datei (sofern nicht geöffnet) kommentarlos überschrieben.
Randbemerkung: Klickt man im Meldungsfenster auf die Buttons „Nein“ oder „Abbrechen“ ist eine Fehlermeldung die Folge …
Menschen, seufzt der Kaffee. Jeden Morgen schweigen sie mich missmutig an, aber der Weinflasche erzählen sie abends ihr halbes Leben.
Will man Formeln in Werte verwandeln, sind mehrere Schritte nötig:
- Die Zellen mit den Formeln kopieren
- Über das Kontextmenü auf das Symbol [123] klicken
Hierfür sind mehrere Klicks nötig. Etwas schneller geht es mit der Tastenkombination [Strg] + [Alt] + [V] – damit wird der Dialog „Inhalte einfügen“ aufgerufen. [Alt] + [W] (für Werte) und mit [Enter] bestätigen.
Damit Excel etwas weniger nervt.
Randbemerkung: Man kann auch den Bereich mit der rechten Maustaste verschieben und wieder an gleicher Stelle fallen lassen und anschließend die Option „Hierhin nur als Werte kopieren“ auswählen:
Heute habe ich gesehen, dass der Honig teurer geworden ist. Ich wusste gar nicht, dass Bienen mit Diesel fliegen.
Schöne Frage in der Excelschulung als wir (intelligente) Tabellen anschauen:
Wenn man hinter (oder unter) der Tabelle eine neue Spalte einfügt, wird diese in die Tabelle übernommen:
Wird jedoch VOR der Tabelle eine Spalte eingefügt, ist sie nicht Teil der Tabelle. Abhilfe schafft das Symbol „Tabellengröße ändern“, mit dessen Hilfe der Bereich der Tabelle erweitert werden kann:
Rosi hat jetzt unter 32168 eine Rufumleitung. Zu Layla.
Perfide.
Warum funktioniert der Filter nicht? In einer Liste befinden sich Zahlen – allerdings bietet der Autofilter die Einstellung „Textfilter“ an:
Ich filtere „beginnt nicht mit ‚-‚“, um die negativen „Zahlen“ auszublenden. Das Ergebnis:
Die negativen Zahlen bleiben stehen. Ich filtere „größer oder gleich 0“ – es klappt:
Und die Ursache? Warum zeigt Excel „Textfilter“ an, obwohl es sich um Zahlen handelt? Obwohl die Spalte mit dem Zahlenformat „Zahl“ formatiert ist?
Die Antwort: in der Spalte befinden sich keine Zahlen, sondern Formeln, die manchmal einen Text (eine leere Zeichenkette) liefern:
=WENNFEHLER(O2-P2;"")Das Ergebnis sind Texte („“) und Zahlen. Text gewinnt – obwohl die Spalte korrekt als Zahl formatiert ist. Perfide!
Sportlehrer: „Heute machen wir Dehnübungen“. Schüler: „Das heißt ‚die Übungen'“.
Schöne Frage in der PowerPoint-Schulung: wo man die Option finden kann, mit der man Bilder innerhalb einer Form verschieben kann, also: zoomen, stretchen oder an die Form anpassen:
Die Antwort: diese Option gibt es nicht, aber wenn man das Werkzeug „Zuschneiden“ aktiviert, kann man das Bild unabhängig von der Form verschieben und skalieren.
Österreich ist das zweitunfreundlichste Land der Welt – na, i will ma gar ned vorstellen, dass die Leut irgendwo noch deppada sin.
Warum das Diagramm die Daten nicht korrekt darstelle, will die Teilnehmerin in der PowerPoint-Schulung wissen:
Als ich nachfrage, was sie gemacht habe, zeigt sie es mir: den Startbereich der Tabelle gelöscht, indem sie die Spalten markiert und gelöscht hat:
Ich rate ihr das Diagramm zu löschen und neu zu erstellen …
Sarah Connor hat ihr Kind „Delphine Malou“ genannt. Mein Kind soll später mal „Tunfisch Traudel“ heißen.
PowerPoint-Schulung. Der Klassiker: Warum kann ich mit dem Pinsel, also mit dem Werkzeug „Format übertragen“ eine der Formen nicht formatieren?
Die Antwort ist schnell gefunden: wird die Form markiert, ist die kontextsensitive Registerkarte nicht mit „Formformat“ beschriftet, sondern mit „Bildformat“. Beim Kopieren wurde die Form aus Versehen als Grafik eingefügt …
Frau kommt heulend aus der Umkleidekabine: „Sie ist zu eng!“- „Die Hose oder die Kabine?“ – Der Tod trat sofort ein.
PowerPoint meets Excel
PowerPoint-Schulung. Wir schauen uns die Erstellung von Diagrammen in PowerPoint an. Eine Teilnehmerin fragt, wie sie die Überschrift ändern könne. Das gehe nicht, sagt sie.
Ich bitte sie, ihren Bildschirm zu teilen:
Ah – sie hat bei der Dateneingabe nach unten gescrollt …
Ich bin süß, schüchtern und unschuldig … Jetzt hört auf zu lachen!
Ich starte Excel und erhalte folgende Meldung:
Dieser Dateityp wird in der geschützten Ansicht nicht unterstützt.
Ich habe eine Weile gesucht, bis ich die Ursache gefunden habe. Irgendein Update von Excel kollidiert mit einem der installierten Add-Ins.
Der Computer sagt „Drucker suchen“ und ich stelle mir vor, wie die Daten rumlaufen und panisch rufen „Ein Drucker! Ist ein Drucker anwesend?“
Hallo Herr Martin,
ich habe die Formel so angepasst, dass auf die richtigen Werte zugegriffen wird (d.h. es wird der Zinssatz verwendet, der als Drilldown-Liste angelegt wurde, s.u.). Leider kommt immer wieder eine Fehlermeldung, die ich nicht lösen konnte, auch nicht durch eine Internetrecherche. Den Code habe ich mit Notepade++ zusammengebaut und Ihnen angehängt. Das ist der Fehler (beim Komma):
Hallo Frau I.,
1. PowerQuery unterschiedet zwischen Groß- und Kleinschreibung.
Der Befehl lautet
Number.Power
(groß „N“, groß „P“)
2. Stimmt – ist mir später aufgefallen – ich habe die zweite Formel (P) vergessen. Und: NEIN: es gibt keine Barwert- oder andere finanzmathematische Funktion in PowerQuery
Liebe Grüße
Rene Martin
Nachtrag:
so könnte die Lösung aussehen:
Starten Sie den PowerQuery-Abfrageeditor.
Klicken Sie auf die Funktion fnBarwert.
Klicken Sie auf Ansicht / Erweiterter Editor.
Dort sehen Sie die Berechnung:
let
Barwert = (Zins as number, Restnutzdauer as number) as number =>
let
q = 1 + Zins / 100,
Ergebnis = (Number.Power(q , Restnutzdauer) - 1) / (Number.Power(q , Restnutzdauer) * (q-1))
in
Ergebnis
in BarwertIst der Zins bei Ihnen eine Zahl oder eine Prozentzahl – also 3 oder 3%? Ist die Restnutzungsdauer in Jahren oder Monaten?
Tragen Sie einfach die entsprechenden Zahlen links ein – rechts wird der Barwert berechnet. Korrekt?
Liebe Grüße
Rene Martin
Krass, was duschen bewirkt: eben noch müde, kaputt und schlecht gelaunt. Jetzt müde, kaputt und schlecht gelaunt mit Vanillegeruch.
Hallo Herr Martin,
Nun muss ich eine weitere Spalten mit berechneten Werten aus anderen Spalten einfügen. Die neue Spalte soll den Barwertfaktor enthalten. Die Formel dazu:
Also =(((1 + [#“Verzinsung/LZ“] / 100) ^ [#“Restlaufzeit“] ) – 1) / (((1 + [#“Verzinsung/LZ“] / 100) ^ [#“Restlaufzeit“] ) *( (1 + [#“Verzinsung/LZ“] / 100) – 1 ))
Wie kann ich in Powerquery eine Potenz berechnen? Mit ^ geht es wohl nicht …
Oder gibt es bereits eine Funktion, die den Barwert berechnet?
Herzliche Grüße,
####
Hallo Frau I.,
Sie berechnen die Potenz mit Number.Power
Also für den Kapitalisierungsfaktor so:
Also = Table.AddColumn(#"Geänderter Typ", "Also", each (Number.Power([#"Verzinsung/LZ"], [Restlaufzeit]) - 1) / ( Number.Power([#"Verzinsung/LZ"], [Restlaufzeit]) * ([#"Verzinsung/LZ"] - 1)))Liebe Grüße Rene Martin
Findest du, dass ich zu wenig Busen habe. – Nein: zwei sind okay!
Lustige Frage in der letzten PowerQuery-Schulung:
Wenn ich den Datentyp über das Gebietsschema ändere (beispielsweise Englisch (USA), habe ich die Möglichkeit mit einem Klick auf Zahnrad-Symbol hinter dem Schritt den Schritt zu ändern:
Wenn allerdings für sehr viele Spalten der Datentyp geändert wurde, gibt es für DIESEN Schritt kein Zahnradsymbol. Was macht man nun, wenn einer der Datentypen einer Spalte falsch ist?
Die einfachste Möglichkeit: Man markiert die Spalte und ändert den Datentyp in den richtigen Typ. Dann wird dieses Element ersetzt.
Natürlich kann man auch in der Bearbeitungsleiste den Teil per Hand korrigieren:
Oder: man löscht den ganzen Schritt und erstellt ihn neu. Letztere Variante ist natürlich wenig sinnvoll …
Lust auf Vollkornnudeln? – Warum fängst du jetzt Streit an?
Lieber Herr Martin,
haben Sie vielen Dank, das hat prima funktioniert!
Nun soll aber im Tabellenblatt ein Wert manuell eingegeben werden, auf den dann die Berechnung der Spalte zugreift (ähnlich der Eingabe bei den Filterkriterien). Geht das?
#####
Hallo Frau I.,
Wenn Sie Werte auslagern möchten, dann „ziehen“ Sie die Daten nach PowerQuery, wählen den korrekten Datentyp (Text oder Zahl) und machen ein Drilldown, so dass nur noch ein Wert übrig bleibt.
Erstellen Sie eine neue Spalte, rechnen dort zuerst mit einem „harten“ Wert (beispielsweise +5) und ersetzen dann die zahl durch Ihre Variable (hier: + tbl_Plus)
Hilft das?
Hallo Herr Martin,
haben Sie vielen Dank!
Ich habe ein bisschen damit herum probiert. Sofern ich bei einer Tabelle neue Spalten aus derselben Tabelle hinzufüge, klappt alle prime. Allerdings erhalte ich einen Fehler beim Hinzufügen einer Spalte in die Tabelle qry_Datenzugriff, die sich berechnen soll aus „Bodenwert“ (Spalte mit vielen Werten der Tabelle qry_Datenzugriff) mal „Zinssatz“ (Spalte der Tabelle tbl_Zinssatz mit nur einem Wert). Wie kann ich das lösen?
Hallo Frau Issel,
die ersten beiden Schritte sind richtig: Sie laden die Tabelle nach PowerQuery; Sie wandeln den Typ in Dezimalzahl (oder Prozentzahl) um.
Aber dann fehlt der Drilldown: Sie müssen den Wert der Zelle in einen Wert verwandeln. Klicken Sie mit der rechten Maustaste auf die Zelle und führen den Drilldown durch:
Das Ergebnis sieht so aus:
Kann in einer Zeile geschrieben werden:
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz]Und diese lange Formel können Sie nun in Ihrer Berechnung verwenden, also statt:
Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* 1.3)Schreiben Sie:
Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz] )Klappt das?
Liebe Grüße
Rene Martin
Mein Zimmer war eigentlich aufgeräumt. Aber dann wusste ich nicht, was ich anziehen sollte.
Die Frau hat einen Beutel: „Heute Rettich ich die Welt.“ Ich lese das und zucchinerlich zusammen.
Lieber Herr Martin,
haben Sie vielen Dank, das hat prima funktioniert!
Nun habe ich eine neue Herausforderung: ich möchte weitere Spalten mit unterschiedlichen Berechnungen hinzufügen, bei denen neue Spalten auf Werte von zuvor hinzugefügte Spalten zugreifen. Das sollte möglich sein, vermute ich.
#####
Hallo Frau Issel,
zu Ihren Fragen: klar können sie mit einer berechneten Spalte weiterrechnen: Sie fügen eine benutzerdefinierte Spalte ein: MWST = [Netto] + 0.07
und rechnen damit weiter:
Brutto = [MWST] + [Netto]
Da ist ein Fehler auf der Speisekarte. Sie haben „Rucola“ geschrieben. Da fehlt das „m“.
Guten Tag Herr Martin,
ich möchte aus den Werten von 2 Spalten Werte für eine 3. Spalte berechnen lassen und damit dann weiter rechnen. Kann ich das in PowerQuery erreichen?
Bspw. im Dokument Report09f.xlxs die Werte der Spalte K mal die der Spalte L. Anschließend möchte ich darüber Min, Max, Mittelwert berechnen, analog zu den Spalten, die im Exportdokument schon vorhanden sind.
Viele Grüße,
####
Hallo Frau I.,
in PowerQuery können Sie über „Spalte hinzufügen“ / Benutzerdefinierte Spalte eine Berechnung hinzufügen. Geben Sie dort den Namen der neuen Spalte an und die Berechnung, indem Sie auf diese langen Feldnamen doppelklicken!
Diese Spalte wird ans Ende der Tabelle gesetzt; Sie können sie schnell (über das Kontextmenü) an den Anfang verschieben:
Und dann per Hand etwas nach rechts:
Für die Aggregatfunktionen: erstellen Sie einen Verweis auf die Tabelle
Markieren die Spalte und wählen aus Transformieren / Statistiken die gewünschte Funktion aus.
Hilft das?
Liebe Grüße
Rene Martin
Hab gerade zwei Männer im Partnerlook gesehen und sie höflich gefragt, ob sie schwul sind. Mich dafür verhaften, finde ich schon krass …
Irgendwie doof!
In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:
Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:
Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:
Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:
Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?
Ich weiß es nicht?
Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?
Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?
Irgendwie doof!
Traf kürzlich einen Mikrobiologen – er war doch viel größer als ich dachte …
Folgender Beitrag hat mich vor einigen Tagen erreicht, den ich gerne weitergeben möchte:
„
Guten Tag,
Excel mag zwar Vieles können, aber MS kann es nicht erklären.
Nach einigen Stunden Probierens bleibt folgendes Problem: Ich habe ein Liniendiagramm mit Temperaturwerten für die Jahre 10800 bis 1600 vuZ, also -10800 bis -1600.
- Fehler in Excel: man muss ihm erst über eine Box „sagen“, dass er die negativen (mit Minuszeichen versehenen) Werte aufsteigend anzeigt. Leider schickt aber Excel dann entgegen normaler Verfahren auch unerwünscht die vertikale Beschriftung der Temperaturdaten nach rechts, ohne dass man das irgendwo separat einstellen kann. Unmöglich! Jedenfalls steht eine Anleitung, wenn vorhanden, nicht da wo sie hingehört, nämlich Achsenbeschriftung.
2.1. Ungelöst: Ich möchte, dass nur die Tausender (und vielleicht die Hunderter, je nach späterer Größe) angezeigt werden. Excel erlaubt aber, soweit ich sehe, nur die Abstände zu bestimmen und zeigt nun entgegen dem Userwunsch die Daten ab 1600 rückwärts in Tausendern an. Unmöglich. Man wird nach tausend Dingen gefragt. Nur, wie bringe ich Excel bei, von -10’000 an die Tausender (wertemäßig) aufsteigen zu lassen? (Merke: -1’000 ist ein höherer Wert als -2’000 und sollte daher rechts erscheinen)
2.2. Senkrechte Achsen erscheinen – egal mit welcher Einstellung, nur bei 1600, 5600 und 9600, also in 4000 Jahren Abstand. Hab ich nirgends eingestellt. Das Programm ist zum Verzweifeln, im Gegensatz zu meinem wunderbaren Atari-Spreadsheet-Programm BSTAT seligen Angedenkens.
2.3. Von statistischen Glättungsverfahren will ich gar nicht erst anfangen.
Mit genervten Grüßen und Bewunderung für die Hilfsbereitschaft,
Hans J. Holm“
Meine schlimmste Schrift: 3.) Grundschule 2.) betrunken 1.) wenn der Postbote das digitale Unterschriftengerät hinhält
„haha – nix schlafen gehen!“ brüllte mein verkorkster Schlafrhythmus und fuhr laut hupend auf einem Bobbicar durch mein Hirn.
Schöne Frage in der letzten PowerQuery-Schulung: wo befindet sich das (auch Excel bekannte) Symbol, das erlaubt nicht nur eine Spalte zu sortieren, sondern nach mehreren:
Die Antwort: ein SYMBOL hierfür gibt es nicht – man muss die Spalten in der gewünschten Sortierreihenfolge anklicken und sortieren, beispielsweise zuerst Ort; innerhalb eines Ortes (Aachen) nach der PLZ, innerhalb einer PLZ (beispielsweise 52062) nach der Straße, …
PowerQuery quittiert die Mehrfachsortierung mit dem Befehl
= Table.Sort(#"Geänderter Typ",{{"Ort", Order.Ascending}, {"Plz", Order.Ascending}, {"Strasse", Order.Ascending}})Ähnlich wie SQL:
SELECT *
FROM Kunden
ORDER BY Kunden.[Ort], Kunden.[Plz], Kunden.Strasse
Der Teilnehmer war zufrieden.
Jesus konnte Wasser zu Wein verwandeln, trotzdem wurde ihm geraten, Tischler zu werden. Die Arbeitsagentur war schon damals nicht so ganz perfekt …
Hallo Herr Martin,
Wie kann ich denn in einer Wordtabelle zwei Uhrzeiten berechnen? Beispiel: A2 und B2 sind als Feld mit Datum HH:mm definiert. Bei C2 kommt aber leider nie 01:30 als Ergebnis raus. A2 = 12:00 B2 = 13:30 C2 = Hier soll das Ergebnis stehen von der Rechnung B2 – A2
Hallo Herr D.,
Sie haben recht, Herr Gauger, sowohl bei Berechnungen in Feldfunktionen als auch in Tabellen kann Word nicht mit Uhrzeiten rechnen. Das Ergebnis ist ein krudes Gemisch aus Stunden und Minuten, also unbrauchbar. Schade!
Hallo Rene.
Mir ist folgende Methode bekannt um in Word mit Uhrzeiten zu rechnen.
Dazu muss in den beiden Textformatfeldern welche die Uhrzeiten enthalten bei „Optionen für Textformatfelder“ eine Textmarke eingetragen werden.
Dann wird in die Zelle in der das Ergebnis der Uhrzeitberechnung angezeigt werden soll, folgendes FELD eingetragen.
Wenn in den Textformularfeldern die Box „beim Verlassen berechnen“ angekreuzt wird, kommt es zu einer Aktualisierung auch des REF-Feldes. Scheinbar werden dann alle Felder im aktiven Dokument (außer denen in den Kopf- oder Fußzeilen) aktualisiert.
Salü
Ernst
Hallo Rene
Die Darstellung als HH:mm hat mir keine Ruhe gelassen., so dass ich doch noch eine (wohl sehr umständliche) Methode gefunden habe.
| (Uhrzeit) ab | (Uhrzeit) bis | Differenz (Minuten) | Differenz (HH:mm) |
| 11:58 | 13:05 | 67 | 01:07 |
| 12:00 | 13:15 | 75 | 01:15 |
| 14:49 | 16:00 | 71 | 01:11 |
Folgende Feldfunktionen stehen in den Berechnungsfeldern
C2:
{={={Z1S2\@“H“}*60 +{Z1S2 \@ „m“}} -{={Z1S1\@“H“}*60+{Z1S1\@ „m“}}}
C3:
{={={Z2S2\@“H“}*60 +{Z2S2 \@“m“} }-{={Z2S1\@“H“}*60+{Z2S1\@“m“}}}
C4:
{={={Z3S2\@“H“}*60+{ Z3S2\@ m“}}-{ ={Z3S1\@“H“}*60+{Z3S1\@“m“}}}
D2:
{ IF „{ ={ Z1S2\@“m“}-{ Z1S1\@“m“} }“ <„0″ {={ Z1S2\@“H“}-{ Z1S1\@“H“}-1\#“00″} { ={Z1S2 \@“H“}-{Z1S1 \@“H“} }\#“00″}:{ IF „{ ={Z1S2 \@“m“}-{Z1S1 \@“m“} }“<„0″ { =60+{Z1S2 \@“m“}-{Z1S1 \@“m“}\#“00″} { ={Z1S2 \@“m“}-{Z1S1 \@“m“} }\#“00″}
D3:
{ IF „{ ={ Z2S2\@“m“}-{ Z2S1\@“m“} }“ <„0″ {={ Z2S2\@“H“}-{ Z2S1\@“H“}-1\#“00″} { ={Z2S2 \@“H“}-{Z2S1 \@“H“} }\#“00″}:{ IF „{ ={Z2S2 \@“m“}-{Z2S1 \@“m“} }“<„0″ { =60+{Z2S2 \@“m“}-{Z2S1 \@“m“}\#“00″} { ={Z2S2 \@“m“}-{Z2S1 \@“m“} }\#“00″}
D4:
{ IF „{ ={ Z3S2\@“m“}-{ Z3S1\@“m“} }“ <„0″ {={ Z3S2\@“H“}-{ Z3S1\@“H“}-1\#“00″} { ={Z3S2 \@“H“}-{Z3S1 \@“H“} }\#“00″}:{ IF „{ ={Z3S2 \@“m“}-{Z3S1 \@“m“} }“<„0″ { =60+{Z3S2 \@“m“}-{Z3S1 \@“m“}\#“00″} { ={Z3S2 \@“m“}-{Z3S1 \@“m“} }\#“00″}
Die zusammengesetzte Feldfunktion zur Berechnung der Zeitdifferenz ist sehr umständlich. Allerdings ist die Funktion immer die gleiche, so dass sie als Schnellbaustein abgespeichert werden kann. Einzige die angesprochenen Textmarken müssen jeweils geändert werden.
Salü Ernst
Wenn du das Nutella-Glas ganz nah ans Ohr hältst … kannst du deine Bikinifigur weinen hören!
Hallo Herr Martin,
warum findet Excel Costa Rica nicht? Es gibt Costa Rica!
Genauer: ich habe mit einer Suchformel
=INDEX($F$2:$F$114;VERGLEICH(A9;$G$2:$G$114;0))einen Wert gesucht. Alle Werte werden gefunden – nur nicht Costa Rica:
Zuerst vermutete ich ein Leerzeichen hinter einer der beiden Wörter. Die Schreibweise ist korrekt. Dann vermute ich das Leerzeichen als Übeltäter. Und so ist es auch: die Funktion
=CODE(TEIL(G23;6;1))liefert den Wert 160 (geschütztes Leerzeichen) und nicht 32 (Leerzeichen), wie erwartet:
Auf mein Nachfragen erfahre ich: „… ich habe die Liste doch aus wikipedia kopiert. Und ich habe sie explizit als Wert eingefügt – OHNE Formatierung!“ Das genügt leider nicht … wikipedia verwendet an einigen Stellen geschützte Leerzeichen und bedingte Trennstriche. Das macht das Weiterverrbeiten solcher Listen in Excel manchmal mühsam. Böses Excel …
Früher durfte man erst anfangen zu essen, wenn alle am Tisch waren. Heute: wenn alle ein Foto davon gemacht haben.
Hallo Rene,
ich hoffe das du bei diesen sommerlichen Temperaturen einen guten und entspannten Tag hast.
Zurzeit beschäftige ich mich mit der Ribbon-Programmierung in MS-Office. In Word gibt es ein Button Schließen/Alles Schließen, mit der MsoID = „FileCloseOrCloseAll“. Klickt man diesen Button an, wird das aktuelle Dokument geschlossen. Wird beim Anklicken die Umschalttaste gedrückt, werden alle offenen Dokumente geschlossen.
Wie bekomme ich eine solle Funktionalität bei einem benutzerdefinierten Button hin. Ich möchte zwei unterschiedliche Makros aufrufen, je nachdem ob beim Anklicken die Feststelltaste gedrückt oder nicht gedrückt ist.
Hast du eine Idee?
Liebe Grüße und
Salü
Ernst
Hallo Ernst,
ich glaube nicht, dass das geht. Die Steuerellemente haben folgende Ereignisse:
| Ereignis | Beispiel |
| onAction (button) | Sub Prozedur(ByRef Control As IRibbonControl) |
| onAction (checkBox, toggleButton) | Sub Prozedur(ByRef Control As IRibbonControl, ByRef Pressed As Boolean) |
| onAction (dropDown, galley) | Sub Prozedur(ByRef Control As IRibbonControl, ByRef SelectedID As String, ByRef SelectedIndex As Integer) |
| onChange (editBox, comboBox) | Sub Prozedur(ByRef Control As IRibbonControl, ByRef Text As String) |
Das heißt: für die Buttons gibt es beim Aufruf einer Prozedur kein Parameter, der eine weitere Taste abfangen kann.
Auch bei den Methoden:
| Methode | Beschreibung |
| ExecuteMso | Führt das vom idMso-Parameter angegebene Steuerelement aus. |
| GetEnabledMso | Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement aktiviert ist. |
| GetImageMso | Gibt ein IPictureDisp-Objekt des vom idMso-Parameter angegebenen Steuerelementbilds zurück, wobei die Abmessung von Height und Width angegeben wird. |
| GetLabelMso | Gibt die Beschriftung des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück. |
| GetPressedMso | Gibt einen Wert zurück, der angibt, ob das vom idMso-Parameter angegebene Umschaltflächen-Steuerelement gedrückt wird. |
| GetScreentipMso | Gibt die QuickInfo des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück. |
| GetSupertipMso | Gibt eine MultiInfo zum vom idMso-Parameter angegebenen Steuerelement als Wert vom Typ String zurück. |
| GetVisibleMso | Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement sichtbar ist. |
Kann man mit GetPressedMso nur bei Toggle-Buttons prüfen, ober ein- oder ausgeschaltet wurde, aber nicht wie gedrückt wurde.
Ich kann diese Frage mal nächste Woche auf meinem Blog veröffentlichen – vielleicht weiß jemand eine Antwort …
Liebe Grüße
Rene
Hallo Rene.
Nach deinen Ausführungen bin ich auf die Idee gekommen folgendes in WORD auszuprobieren.
Bei zwei geöffneten Dokumenten habe ich im Direktbereich des VBA-Editors den Befehl
CommandBars.ExecuteMso(„FileCloseOrCloseAll“)
einzugeben und danach nur die Enter-Taste gedrückt.
Es wird die Schließen-Routine aufgerufen.
Wird allerdings die Shift- und die Enter-Taste gleichzeitig gedrückt, wird die AllesSchließen-Routine aufgerufen.
Daraufhin habe ich folgendes versucht:
Ich habe in ein Word-Dokument mit Hilfe des Office RibbonX Editor diese Sequenz eingefügt.
und im VBA-Bereich des gleichen Dokumentes folgendes.
Option Explicit
'API zum feststellen des Keyboard-Status deklarieren.
#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
Sub OnActionButton(control As IRibbonControl)
Select Case control.ID
Case "SaveAsDocxOrPdf": SpeicherAlsDocxOderPdf 'Neuer Umbruch
Case Else
MsgBox "Fehler in OnAction: '" & control.ID & "' nicht erkannt"
End Select
End Sub
Sub SpeicherAlsDocxOderPdf()
If Abs(GetKeyState(&H10) < 0) Then 'Abfrage ob Shifttaste gedrückt ist
CommandBars.ExecuteMso ("FileSaveAsPdfOrXps")
Else
CommandBars.ExecuteMso ("FileSaveAsWordDocx")
End If
End SubUnd es klappt. Wird das benutzerdefinierte Icon „Als DOCX oder PDF speichern“ angeklickt, erscheint das Formular „Speichern als Dokument ohne Makros“. Wird während des Anklicken des Icon die Shift-Taste gedrückt, erscheint das Formular „Als PDF oder XPS veröffentlichen“.
Salü
Ernst
Das ist sehr, sehr clever! DARAUF wäre ich nie gekommen.
LG Rene
Einfach mal kopfschüttelnd aus dem Fahrstuhl kommen und den Wartenden ein „Gehen Sie nicht rein! Die Spülung ist kaputt!“ zurufen.
Tomatensoße weiß genau, wann du ein frisches Hemd angezogen hast.
Irgendwie doof.
Ich habe eine Datei, in der sich verschiedene Werte befinden. Diese Datei wird mit Kennwortschutz gespeichert:
Einige Werte dieser Datei werden in eine andere Datei verknüpft:
Sind beide Dateien geschlossen, wird die Zieldatei geöffnet, so fragt Excel danach, ob die Daten aktualisiert werden sollen. Danach erfolgt die Frage nach dem Kennwort der kennwortgeschützten, verknüpften Datei.
Wurde allerdings die verknüpfte Datei an andere Stelle kopiert, dort bearbeitet und wieder zurückgespielt, erkennt Excel dies – aufgrund des Kennwortschutzes – nicht. Eine Neuberechnung zeigt nicht die aktuellen Daten. Man muss die Originaldatei öffnen, um eine Aktualisierung der Daten zu erreichen …
Herr Ober, können Sie bitte den Daumen von meinem Schnitzel nehmen! – Damit es mir schon wieder runterfällt, oder was?
Hallo Rene,
herzlichen Dank für deine Bemühungen! Das bringt mich ein großes Stück weiter. Leider funktioniert aber irgendeine Kleinigkeit noch nicht… Ich hab viel probiert, komm aber nicht auf den Fehler.
Expression.Error: Der Wert "2022" kann nicht in den Typ "Text" konvertiert werden.
Die Fehlermeldung verstehe ich nicht. Das Jahr, nach welchem gefiltert werden soll.
Hallo Nadine,
ist die Zahl 2023 in der Zelle als Text formatiert?
Und: was macht „geänderter Typ“? – in Text oder Zahl konvertieren?
Liebe Grüße
Rene
Hallo Rene,
genau, ich habe dann extra die 2023 in Text formatiert. Ursprünglich hatte ich es als Zahl, da kam allerdings auch diese Fehlermeldung, weshalb ich die 2023 dann in Text formatiert habe.
Hier die Schritte, welche ich in der Jahrestabelle ausgeführt habe:
Dort wo dann die Formel eingefügt wird, also dort, wo nacher nach diesem Jahr gesucht werden soll, sieht die Formatierung so aus:
Hallo Nadine,
Folgende Ursache: Ich vermute in deiner Zelle stehe die Jahreszahl als ZAHL – in meiner ersten Städtedatei hatte ich sie als Text formatiert.
Damit du auch einen Text erhältst, muss dein zweiter Schritt
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SOP"]}[Content],{{"Column1", type text}}){0}[Column1]
verwendet werden
In deinem Code
= Table.SelectRows(Quelle, each (Record.Field(_ , Excel.CurrentWorkbook(){[Name="SOP"]}[Content]{0}[Column1]) <> null))Zusammengefasst: der in Excel eingetragene Wert in eine Zahl. Die Spaltenüberschrift jedoch ein Text. Irgendwann muss die Zahl in einen Text konvertiert werden!
Wenn euch jemand vor die Frage stellt: Sex oder Magnum – nehmt ihr dann Mandel oder Classic?
Ich habe schon eine Weile überlegen müssen. Folgende Frage erreichte mich:
„Allerdings möchte ich nun in einer Spalte, die nicht fest definiert ist, filtern. Ist dies möglich?
Ziel: Ich möchte das es mir nur die Zeilen anzeigt, die in einer bestimmten Jahres-Spalte einen Wert haben.
Der Anwender des Tools sollte die Möglichkeit haben, ein Jahr einzugeben in einem bestimmten Feld.
Hier wählt der Anwender das Jahr aus. Daraufhin sollte in Power Query in der Spalte, mit der Bezeichnung 2026, nach Werten <> null gefilter werden. Im Screenshot würde dann nur noch die erste Zeile erscheinen, da in der Spalte 2026 nur in der ersten Zeile ein Wert enthalten ist. Ich hab schon viel rumprobiert und bekomm es nicht hin.“
Ich ziehe das Ergebnis des Filters (versehen mit dem Namen „Jahr“) nach PowerQuery und benennen die Abfrage „Jahr“. Sie sieht folgendermaßen aus:
= Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]Ich erstelle eine Spalte mit diesem Wert:
= Table.Column(#"Geänderter Typ", Jahr)Das Ergebnis:
Wird eine Spalte der Liste gefiltert, lautet der M-Befehl (beispielsweise für das Jahr 1950) folgendermaßen:
= Table.SelectRows(#"Gefilterte Zeilen", each ([1950] <> null))
Baue ich dort allerdings das Ergebnis der Abfrage ein, klappt die Filterung nicht mehr:
= Table.SelectRows(#"Gefilterte Zeilen", each (Jahr <> null))
Auch alle anderen Versuche greifen nicht – werde das Weiterverarbeiten der Tabelle oder Liste noch eine Hilfsspalte, bei der aus
= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if [1950] <> null then null else "x")ändert in
= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if Jahr <> null then null else "x")Schließlich – der Gedanke: [1950] muss durch
Record.Field(_, Jahr) ersetzt werden. Die ganze Zeile lautet laso:
= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Jahr) <> null))oder komplett:
= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]) <> null))Geht doch!
PS: Die rote Farbe im Kopf habe ich mit der bedingten Formatierung erzeugt. Man darf jetzt nur nicht die Aktualisierung vergessen.
Einerseits will man für jemanden eine Freude machen und einen Käsekuchen backen, anderseits ifft der sowiefo viel su ungefund.
Eine schöne Frage in der letzten PowerQuery-Schulung.
Eine Tabelle soll verändert werden. Im linken Bereich befinden sich Informationen (nennen wir sie „Metadaten“), im rechten Bereich in mehreren Spalten weitere Informationen zu diesen Metadaten.
Jede dieser Gruppen, bestehend aus jeweils drei Spalten, soll neben die anderen Daten geschrieben werden, so dass die Metainformation so oft auftaucht, wie Gruppen vorhanden sind. Dabei können beliebig vieler dieser Gruppen auftauchen.
Das Ziel:
Mein erster Gedanke:
Ich fasse mit dem Befehl „Spalten zusammenführen“ jeweils die einzelnen Spalten einer Gruppe zusammen:
Anschließend kann man diese Spalten entpivotieren
und danach am Trennzeichen (hier: „|“) teilen.
Aber: das Verfahren ist umständlich, weil (hier:) bei 17 Gruppen 17 Mal entpivotiert werden muss. Da die Anzahl der Gruppen variabel ist, ging ich auf die Suche, ob man das mit geschickten M-Befehlen (einer Schleife!) abkürzen und dynamisch halten kann.
Da fiel mit der Artikel von Hildegard Hügemann in die Finger:
https://www.office-kompetenz.de/inhalte-aus-spalten-auf-zeilen-verteilen-mit-power-query/
Und genau DAS ist die Lösung:
Zuerst muss man den „rechten“ Teil entpivotieren:
Anschließend werden die Überschriften benötigt in der Form A – B – C. Leider stehen sie hier als A1 – B1 – C1 – A2 – B2 – C2 – A3 – … Die Zahlen müssen entfernt werden. Man kann sie mit dem Assistenten „Spalte teilen“ und er Option „Nach Wechsel von Nicht-Ziffer zu Ziffer“ herauslösen:
DIESE (spätere Überschriftsspalte) wird nun pivotiert, wobei die Werte (letzte Spalte) natürlich nicht aggregiert werden (verbirgt sich in den „Erweiterten Optionen“):
Der Rest ist „Kosmetik“: Datentypen festlegen, Spalten löschen, leere Spalten entfernen (wegfiltern), Spalten umbenennen, …
Klasse!
Ein großes Dankeschön an Hildegard Hügemann für die Lösung – hier habe ich glatt „in die falsche Richtung gedacht“.
Ich kann mich nicht entscheiden, ob ein Kindergarten oder die Glasfront eines Fitnessstudios besser geeignet ist, um davor ein Eis zu essen.
Seit ich nicht mehr rauche, fast keinen Alkohol mehr trinke und mehr Sport treibe, lüge ich häufiger.
Kennst du das? Man möchte in PowerQuery in mehreren Spalten den Datentyp ändern. Ein Klick auf das kleine Symbol und alle Markierungen werden aufgehoben:
Abhilfe schafft der Befehl Transformieren / Datentyp. Lästig:
Die Lösung zeigt Frank Arendt-Theilen:
Man muss die [Strg]-Taste halten und zwei Mal auf das kleine Symbol klicken. Dann klappt es:
Meine Freundin hat sich gerade gewogen und mich danach gefragt, was ich glaube, wie viele Kilo sie wiege. Ich habe mich tot gestellt. Sicher ist sicher.
Wer PowerQuery kennt, kennt sicher das Problem von IntelliSense:
Man beginnt etwas zu tippen, wundert sich über die angezeigten Vorschläge:
Man findet den korrekten Vorschlag, klickt darauf oder bestätigt mit der Tabulatortaste und: das Objekt wird zwei Mal genannt:
Ärgerlich!
Wyn Hopkins hat den Grund gefunden:
„The devil is in the dot.“
Man muss beide Befehl ohne Punkt schreiben, wobei Groß- und Kleinschreibung keine Rolle spielen! Dann klappt es. Ich bin begeistert:
Jeden Morgen stehe ich vor dem Spiegel und stelle mir die Frage: „Restaurieren oder unter Denkmalschutz stellen?“
Kennt ihr das? Eine Zelle bleibt schwarz. Hat keine Lust … Zum guten Glück verschwindet es sofort wieder …
Wenn ein Beamter behauptet, er habe sich im Dienst den A… aufgerissen, dann glaube ihm! Das Toilettenpapier in Behörden stammt vom billigsten Anbieter.
Männer können einfach nicht richtig zuhören. – Sind wir gar nicht!
Ich erhalte eine Mail.
„Ich komme leider mit der Fehlermeldung
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Details:
Key=[Record]
Table=[Table]
nicht weiter.“
Ich schaue mir das Ganze an. Was haben wir gemacht?
In einem Tabellenblatt werden drei Dateien aufgelistet und der Pfad, in dem sich diese Dateien befinden. Diese vier Zellen haben Namen – hier: Schweinchen1, Schweinchen2, Schweinchen3 und Pfad:
Über Daten / Daten abrufen / aus Datei greife ich auf eine der drei Dateien zu:
Die Datentypen werden nicht automatisch erkannt; übrig bleiben drei Schritte; das Ergebnis wird nach Excel zuzrückgegeben:
Eine der drei Zellen mit Namen wird über Daten / Daten abrufen / Aus Tabelle/Bereich in PowerQuery verwendet. Nach einem Drilldown erhält man den Inhalt der Zelle:
Dies wird für die übrigen Zellen wiederholt. Fügt man nun diese Variablen in den Befehl Excel.Workbook ein, so ist eine Firewall-Meldung die Folge:
Diese kann man umgehen, indem man den Code (Zugriff auf den Inhalt einer Zelle mit Namen) in eine Zeile schreibt:
Excel.CurrentWorkbook(){[Name="Schweinchen3"]}[Content]{0}[Column1]In der Codezeile
= Excel.Workbook(File.Contents(Pfad & Schweinchen1), null, true)müssen die beiden Variablen durch ihre Funktion ersetzt werden (was den Code nicht gerade lesbar macht):
= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true)Klappt:
Dies wird auch für die anderen beiden Dateien durchgeführt, die anschließend in Excel geladen werden:
Die Hilfsabfragen Schweinchen1, Schweinchen2, … kann man getrost löschen.
So habe ich die Vorlage erstellt. Und nun kommt die Fehlermeldung:
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Ich begebe mich auf die Suche. Der Fehler taucht beim Zugriff auf das Tabellenblatt „Tabelle1“ auf. Nachgeschaut: bei einer anderen Datei heißt das Tabellenblatt „Sheet1“:
Also muss ich auch noch den „harten“ Namen entfernen. Ich mache es so:
#"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])und lösche die Zeile „Navigation“. Klappt:
Der ganze Code (jetzt: völlig unverständlich!):
let
Quelle = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true),
#"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])
in
#"Höher gestufte Header"Die übrigen zwei Abfragen werden analog angepasst – nun klappt alles!
Zuerst schuf Gott den Mann und sah, dass es gut war. Dann schuf er die Frau und sagte: „Okay, die müssen sich halt schminken!“
Word-Schulung. Eine Teilnehmerin möchte ein „x“ mit einer Tilde dargestellt haben – das Symbol für Median.
Wir überlegen zusammen. Man könnte es mit einer Feldfunktion erzeugen:
Das Feld
{EQ \O(x;~)}schiebt die beiden Zeichen „x“ und „~“ ineinander. Damit die Tilde über dem Buchstaben steht, muss man sie höher formatieren – man findet dieses Attribut in den Zeichenformatierungen:
Das Ergebnis könnte man in der Autokorrektur oder als Schnellbaustein abspeichern.
Natürlich könnte man auch den Formeleditor heranziehen.
Schneller funktioniert es sicherlich, wenn man das Zeichen von einer Internetseite kopiert:
Schließlich werden wir sogar in Word fündig: es gibt bereits dieses Zeichen in der Autokorrektur. Man muss
x\tildeeintragen.
Achtung: dies funktioniert jedoch nur dann, wenn die Option „Mathematische Autokorrekturregeln in nicht mathematischen Bereichen verwenden“ aktiviert ist.
Nachtrag:
Hallo Rene,
Mir ist noch folgende Methode zur Erzeugung des Median-Symbol in WORD bekannt. Zuerst wird das x-Zeichen eingegeben (es kann auch jedes andere Zeichen sein) und danach wird bei gedrückter ALT-Taste die Zahlenfolge 771 auf der Zifferntastatur eingegeben.
Salü Ernst
Danke für den tollen Tipp, Ernst!
Und noch einer von Ernst:
Hallo Rene,
da diese Methode auf meinem Laptop nicht möglich ist, weil dieser keine Zifferntastatur aufweist, habe ich weiter gesucht und noch eine andere Methode gefunden.
Dabei können zwei verschiedene Reihenfolgen der Zeichenerstellung angewendet werden.
Nach Eingabe des x-Zeichen schreibt man den Wert 303 (was der Hex-Wert von 771 ist) markiert diesen Wert und drückt ALT-c.
oder
303 wird zuerst eingegeben, danach wird ALT-c, danach die Cursortaste links und dann das x-Zeichen gedrückt.
Statt dem x können alle möglichen anderen Zeichen (Buchstaben, Zahlen, Satzzeichen wie ?) und statt dem Wert 303 können auch andere Zahlenwerte verwendet werde. 305 ergibt z.B. einen großen Strich über dem Buchstaben.
Diese Methode hat den Vorteil, dass die Zifferntastatur nicht benötigt wird.
Salü
Ernst
*uff* noch eine Lösung:
Hallo Rene,
und der Witz ist, dass es eine weitere noch einfachere Methode gibt, die auch auf einen Laptop funktioniert.
In WORD kann jeder beliebige Unicode aufgerufen werden, indem u+ dann der Hex-Wert des Unicodezeichens eingegeben und danach ALT-c gedrückt wird. Dies gilt natürlich auch für die Zeichen der Unicode-Block „Kombinierende diakritische Zeichen“.
Somit kann z.B. das Medianzeichen durch die Kombination xu+303 und drücken von ALT-c erzeugt werden.
Der Unicode-Block der „Kombinierende diakritische Zeichen“ umfasst den Bereich Hex300 (Dez771) bis Hex36F (Dez879) und somit 112 Zeichen die mit allen möglichen Buchstaben kombiniert werden können.
Salü Ernst
Mir ist heute Morgen ein Joghurt aus der Hand gefallen. War nicht mehr haltbar.
Wordschulung.
In dem Artikel
habe ich darauf hingewiesen, dass in Word Ref-Felder, wenn sie sich in der Kopf- oder Fußzeile befinden, nicht automatisch aktualisiert werden. Und gestern kam erneut die Frage:
„Ich möchte in ein Worddokument Informationen eintragen und diese in der Kopfzeile anzeigen.“
Ich erkläre, dass man Formularfelder verwenden könnte und der Feldfunktion Ref darauf zugreifen könnte:
Allerdings werden sie nicht automatisch aktualisiert. Erst beim Drucken oder wenn man alles markiert und mit [F9] aktualisiert, sieht man in der Kopfzeile den Text, der ins Dokument eingegeben wurde.
Ebenso ungeschickt sind Textmarken, die mit der gleichen Feldfunktion Ref aufgerufen werden können. Auch hier wird nicht sofort aktualisiert, und es besteht die Gefahr, dass die Textmarke aus Versehen gelöscht wird:
Mir fällt eine dritte Lösung ein: Die Funktion Styleref wiederholt den Text einer Formatvorlage. Also könnte man eine Tabelle anlegen, denn dort wird eine Zelle als Absatz behandelt. Ihr wird eine (Absatz-)Formatvorlage zugewiesen (hier: Thema und ID).
Auf diese wird mit StyleRef verwiesen. Erstaunlicherweise muss ich den Bildschirm nach oben und unten scrollen, damit aktualisiert wird, aber dennoch: so sieht man in der Kopfzeile (oder Fußzeile) schnell den Text, der im Dokument eingegeben wurde:
Hast du zugenommen? – Nein, ich habe mich auseinander gelebt.
*ups*
Die Aufgabe lautet: ich möchte die Anzahl der Zeilen wissen, in denen in mindestens einer der vier Spalten etwas steht. Oder Anzahl der Spalten, bei denen Wert Spalte A <> „“ oder Wert Spalte B <> „“ oder Wert Spalte C <> „“ oder Wert Spalte D <> „“.
Ich probiere. Mit & werden die vier Zellen verkettet:
Hier:
=tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos]Während die Funktion
=ANZAHLLEEREZELLEN(G2#)
die Zahl 3 ergibt, liefert
=ANZAHL2(G2#)die Zahl 26. Die vermeintlich leeren Zellen werden mitgezählt.
Die Formel darf folglich NICHT lauten:
=ANZAHL2(tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos])Die Formel
=G6=""liefert WAHR, die Formel
=ISTLEER(G6)liefert FALSCH.
Eine Lösung könnte so aussehen: die Funktion FILTER liefert die korrekte Liste, beispielsweise so:
=FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>""))
Und die kann man zählen:
=ANZAHL2(FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>"")))Das Glas ist leer, möchtest du noch eins? – Was soll ich mit zwei leeren Gläsern?
Hallo Rene
Darf ich dich noch etwas Fragen?
Ich habe eine Excel Arbeitsmappe, mit welcher Offerten gerechnet werden.
Der Artikelstamm liegt in einer SharePoint Liste und die besagte Arbeitsmappe verfügt über mehrere Tabellenblätter bei welchem jeweils eine gefilterte Abfrage auf den zentralen Artikelstamm gemacht wird.
Die auf diese Weise gruppierten bzw. gefilterten Artikel werden mit einer Anzahl versehen, sodass Preise ermittelt werden können.
Das klappt soweit auch alles doch am Schluss möchte ich das Ganze so ausdrucken können, dass keine fixen Seitenumbrüche pro Tabellenblatt gemacht werden.
Die Kopf und Fusszeile sollten gemeinsam benutzt werden und pro Tabellenblatt müssten drei Wiederholungszeilen bestehen, dann jeweils n Zeilen mit Artikeln, aber eben nicht immer eine neue Seite. Meinst Du ist das machbar oder habe ich da wiederum ein Problem?
Hallo,
kurz und knapp,
die Antwort auf diese Frage findest du in den Tiefen meines Blogs: es geht nicht. Die Frage habe ich vor einigen Jahren erhalten, weil man in Lotus 1-2-3 mehrere Tabellen auf einem Blatt ausdrucken konnte. In Excel ist so eine fortlaufende Tabelle nicht möglich.
Sorry!
Liebe Grüße
Rene
Sie: „Schatz – mach mir ein Kompliment!“ – Er: Du hast einen fantastischen Mann!“
Hallo Herr Martin,
ich möchte Sie nochmals belästigen, auch auf die Gefahr hin, dass ich wieder zu doof bin, um es selbst zu finden!
Ich möchte auf dem Excel-Arbeitsblatt in der Kopfzeile das Datum der Erstellung oder zuletzt geändert / zuletzt gedruckt einfügen, wie ich es aus Word kenne. (SAVEDATE / PRINTDATE) Ich habe in den Funktionen nur das „Aktuelle“ Datum gefunden. Selber etwas konstruieren kann ich als bloße Userin leider nicht. Können Sie helfen? Vielen Dank schon mal!
Hallo Frau H.,
das geht leider nicht.
In der Kopfzeile fehlen einige Dinge:
* Verknüpfung mit Inhalt einer Zelle
* Formeln
* weitere Feldfunktionen
…
Mit einem Makro kann man das lösen – wird aber auch umständlich: „woher hole ich die Information „zuletzt gedruckt“.
Sicherlich keine zufrieden stellende Antwort: ich würde das Datum per Hand in die Kopfzeile schreiben. Oder vielleicht über den Blattnamen, den man ja in der Kopfzeile anzeigen lassen kann.
Liebe Grüße
Rene Martin
Danke für die schnelle Antwort!
Wenigstens liegt es diesmal nicht an mir!!!
Alles Gute aus Düsseldorf!
Auf der Packung der WC-Duftsteine stand „hinten eindrücken!“ Tat zwar ein wenig weh beim Sitzen, aber wenn ich jetzt furze, riecht es nach Meeresbrise.
In meinen Outlook-Schulungen sage ich den Teilnehmerinnen und Teilnehmern immer, dass sie sich Mail, die sie geschrieben haben, noch einmal durchlesen sollen.
Warum? Ich helfe gerne. Aber es ist mühsam, den Inhalt einer solchen Mail herauszufinden. Bin ich überhaupt gemeint? Wer ist Sabine? Worum geht es? Folge Mail erreichte mich vor einigen Tagen:
„Lieber Herr Brockelmann – ich hoffe es geht Ihnen gut?
Ich hätte eine Frage.
Ich habe erst Sabine interviewt.
Dann ergab sich dass Jennys Prozess zeitlich davor anfängt……und dasbekam ich dann nicht hin in der Datei.
Ich interviewe nur 1 Prozess als Muster – damit meine Chefin mal sieht was Prozessmanagement kann (allerdings hier nicht reine Lehre BPMN 2.0.).
Jetzt konnte ich den Vorlagen Prozess Sabine nicht nach rechts schieben (ich habe es nicht hinbekommen) und wissen Sie (sicher) wie man den Rahmen (das Feld nach ganz links wieder zieht) die Schablone? Das sich alles unendlich nach rechts erweitert ist gut.
Ich hoffe ich war verständlich….
Herzlichen Gruß aus xxx aus dem Home Office heute“
Hannibal Lecter hat unhöfliche Menschen einfach aufgegessen. Ich wollte es nur einmal gesagt haben.
Eine schöne Frage in der letzten Schulung.
Ein Teilnehmer zeigt mir seine Tabelle. Sie hat sehr viele Spalten, in denen Informationszahlen stehen:
Seine Frage:
„Wenn ich eine Pivottabelle estelle (in der ich die vorkommenden Werte zähle), kann ich nicht die einzelnen Spalten in die Werte ziehen. Was muss ich tun?
Die Aufgabe: Die Orte werden in den Zeilen gruppiert, in die Statusangaben in den Spalten. Man kann nun eine Person in die Zeilen ziehen und sich in den Werten die Anzahl der Einträge anzeigen lassen:
Jedoch: sobald eine zweite Person hinzukommt, arbeitet die Pivottabelle nicht so wie gewünscht:
Da das Ziel war aus einer Pivottabelle ein Diagramm zu erzeugen, scheiden mehrere Pivottabellen aus.
Ich überlege: die Form der Tabelle ist unglücklich gewählt. Man darf die Informationen nicht in Zeilen und Spalten abtragen. Man muss die Tabelle entpivotieren. Hier bietet sich PowerQuery an:
Gesagt, getan – die Liste wird erstellt. Eine Pivottabelle erzeugt:
Genau SO wollte er es haben! Als Basis für ein Diagramm. Er war begeistert.
Je größer der Dachschaden, desto besser ist der Blick auf die Sterne.
Schatz – wo ist das Bier? – Hast du mal auf die Uhr geschaut? – Ja – da ist es nicht.
Zum Vatertag. Heute mal kein Excel. Sondern: Kochende Männer. Männer mit Partnerin. Flirtende Männer.
Gefunden in den Tiefen des Internets. Geschmunzelt.
Mein Staubsauger hat einen Namen. Kevin. Er ist so oft allein zu Hause.
Ich erhalte eine Mail mit folgender Frage:
„Können Sie mir eine Formel nennen, welche eine Formel in Werte verwandelt?“
Ich schmunzle und antworte ihn, dass man dies mit Kopieren / Inhalte einfügen als Werte erledigen kann:
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:
- 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:
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.AutoFitSpä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.AutoFitMein Kommentar: funktioniert, aber „schön“ ist der Code nicht gerade. Besser wäre sicherlich:
ActiveCell.EntireColumn.AutoFitWarum 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 Subfolgende 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
Kaum scheint die Sonne wieder, sind die Fenster dreckig.
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
NextVier 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.
- Es gibt einzelne Datensätze zu auswärtigen Gemeinden oder zu speziellen Portfolioobjekten,
- 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.
Ich habe erfolgreich die Uni abgeschlossen. Peter H., 65 Jahre, Hausmeister
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
Ich wurde gerade bei einer Verkehrskontrolle nach Restalkohol gefragt. Diese Bettelei im öffentlichen Dienst nimmt beängstigende Züge an.
Superpraktisch – in bin begeistert. In PowerPoint gibt jetzt eine Funktion „sperren“. Darauf habe ich schon lange gewartet.
Allerdings: warum nicht auch in Excel? Vielleicht kommt es ja noch …
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
Der Nachbar küsst seine Frau jeden Morgen zum Abschied. Warum tust du das nie? – Weil ich sie nicht kenne.
Zunge am heißen Kaffee verbrannt. Ist aber nur ne Schlürfwunde.
Hallo Herr Martin,
Und noch gleich eine Frage hinterher, s. Anhang.
Hallo Frau I.,
Sie können die Spannweite als Text darstellen lassen, beispielsweise so:
=I11&“ – „&I12
Oder Sie können die Zahlen auch formatiert darstellen:
=TEXT(I11;“#.##0,00″)&“ – „&TEXT(I12;“#.##0,00“)
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:
- 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 IfDer 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!
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 = xlSheetVisible3.) 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 = strBlattname6.) Das Masterblatt „Schneewittchen“ wird ausgeblendet:
ThisWorkbook.Worksheets("Schneewittchen").Visible = xlSheetVeryHidden7.) 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:
Heute habe ich gelesen, was auf der Shampooflasche steht: Für extra Volumen und mehr Fülle! Kein Wunder, dass es mir schwer fällt, mein Gewicht zu kontrollieren! Ich werde ab sofort Geschirrspülmittel benutzen! Dort lese ich: „entfernt auch hartnäckiges Fett.“
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?
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:
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!
Meine Jeans sagt: Hör auf, ständig Kekse zu naschen! Meine Jogginghose flüstert: Keine Sorge, ich bin für dich da!
Es hat nichts mit Excel zu tun, aber es amüsiert mich: seit einigen Tagen erscheint rechts in der Taskleiste regelmäßig ein Häschen mit Deutschlandflagge:
Kaum versuche ich darauf zu klicken. hoppelt es fort und verschwindet. Und zeigt an, dass es Nacht ist oder regnet. Oder beides:
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:
- Mittels „Wennfehler(XXX, ““) zu einem „Gänsefüßchen-Leer gemacht
- Mittels Powerquery à Aus Datei à Aus Ordner abgerufen und zusammengeführt (PowerQuery zeigt auch nicht „null“ für die Zelle an)
- 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:
- „im Kleinen“: Die Datenbeschriftung mit der Maus soweit nach oben schieben, bis die Linie von selbst verschwindet
- „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
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.
Auch Wolkenkratzer haben mal als Keller angefangen
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:
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?
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…
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:
c = a^2 + b^2in 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 Functionfalsch. 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 Functionoder in der Kurzform:
Function Hypotenuse(a As Double, b As Double) As Double
Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End FunctionDas 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.
Schatz – ich höre Stimmen! – Ich rede ja auch schon seit Stunden mit dir.
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:
- 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:
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:
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.
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:
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!