ich hoffe es geht Dir gut! Ich betreue gerade ein ziemlich spannendes Projekt für ein Unternehmen in der Schweiz, dass mich in meinen VBA-Kenntnissen bisher schon ziemlich gefordert aber auch gefördert hat. Jetzt bin ich allerdings an einem Punkt wo ich mit Google und alleinigem überlegen nicht mehr weiterkomme und habe die Hoffnung, dass Du einen Tipp für mich hast.
[…]
Ich habe quasi jeweils eine Liste mit den nach Wunsch aufbereiteten Rohdaten. In dieser soll jetzt an Hand von Daten aus einem Konfigfile (wird wie die Rohdaten über PowerQuery vom Server eingelesen) der entsprechende Filter auf den Verkäufer gesetzt werden und die daraus resultierenden Tabellen in ein neues Dokument exportiert werden. Ist der Vorgang abgeschlossen, kommt der nächste Filter etc. etc. Ich brauche also meines Erachtens einen iterativen Filter der auf Grund eines Kriteriums aus dem Konfigfile erstellt wird.
Leider bekomme ich diesen Part nicht wirklich hin.
Ich hoffe dass meine Mail soweit erstmal nachvollziehbar ist
und dass Du vielleicht eine Idee hast, was ich hier noch machen oder an wen ich
mich noch wenden kann. Ich weiß leider nicht mehr weiter und der Kunde wartet
auf sein Reporttool.
Über eine Rückmeldung von Dir würde ich mich sehr freuen.
Danke Dir und liebe Grüße
Paul
Hallo Paul,
kennst du den
Spezialfilter? Hast du schon einmal den AdvancedFilter in VBA benutzt? Ist
nicht sehr schnell, aber nur eine Zeile Code um eine Liste durch eine
Filterkriteriumsliste zu ziehen.
Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:
„Hallo Rene
zum Thema Datumsberechnungen hätte ich gleich eine Frage:
kann Excel auch Zeiträume erkennen, die sich überschneiden, aber
unterschiedliche Anfangs- und Endzeiten haben?
Also zum Beispiel:
Mitarbeiter A arbeitet vom
01.05.2021 bis 31.08.2021
Mitarbeiter B arbeitet vom
01.06.2021 bis 15.09.2021
In welchem Zeitraum haben
beide gearbeitet
Oder
Von
Bis
Thema
8:00
12:00
Nachdenken
11:30
12:30
Pause
12:30
15:00
Nix tun
Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?
Im ersten Schritt habe ich das visualisiert:
Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:
=WENN(B33<$B$21;0)
Dann die umschließenden Bereiche:
=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)
Und schließlich die überschneidenden Bereiche:
Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:
=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)
Analog die zweite Überschneidung:
Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:
=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)
Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:
und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:
Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:
Fall: leere Menge
Fall: ganzer Bereich
Fall: Überschneidung
Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)
2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:
Natürlich hätte man die Bedingungen auch umdrehen können.
Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:
Fall: Anfang <= Oktoberfest Anfang? Ja: Fall a) Ende vor Oktoberfest Anfang? Fall b) Ende nach Oktoberfest Ende? Fall c) Ende zwischen Oktoberfest Anfang und Ende? […]
Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:
Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:
=SEQUENZ(A6-A5+1;1;A5)
Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:
Für die anderen Personen muss diese Formel nur entsprechend angepasst werden. Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus. Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.
Salü
Ernst
PS: Nachtrag:
Allerdings
kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus
Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine
einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge
eingetragen sind.
Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:
Dim xlBereich1 As Range
Dim xlBereich2 As Range
Dim xlSchnittmenge As Range
With ThisWorkbook.Worksheets("Helmut")
Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1),
.Cells(.Range("B3").Value2, 1))
Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1),
.Cells(.Range("B6").Value2, 1))
End With
Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
MsgBox xlSchnittmenge.Cells.Count
Ich habe für meine Kollegen zur Budgetplanung 2022 je
Abteilung ein Excel auf Teams eingestellt.
Das Excel enthält eine Power Query Abfrage auf alle
Abteilungs-„Auftragsbücher“, und in PowerPivot ein Datenmodell für die
Beziehungen zwischen den Tabellen.
MAC Benutzer scheinen aber Probleme mit der Datei zu haben (s. Screenshots unten)
Wie kann man die volle Funktionalität der Datei auch für MAC
Benutzer herstellen?
Es wäre super, wenn Du hier einen Rat hast
Vielen Dank und beste Grüße Katrin
Hi Katrin,
1. Antwort: Mac ist nicht meine Welt – ich habe keinen.
2. Antwort: ich weiß, dass der mac lange Zeit nicht
PowerQuery unterstützt hat; soweit ich weiß, kann er das inzwischen.
3. Antwort: der Mac unterstützt (noch) nicht das Datenmodell von Excel.
Sehr geehrter Dr. Martin, Wir haben in unserem Unternehmen Probleme bei der Formatierung unserer Statistikauswertung. Wir formatieren eine Spalte farblich größer als /kleiner als/ zwischen, Jedoch ist es nicht möglich dieses Vorgehen auf die anderen spalten zu übertragen und ich or müssen somit jede Spalte seperat formatieren. Ist es möglich das ganze auf alle spalten zu übernehmen?
Hallo Herr R.,
und so funktioniert es. Beginnen Sie bei einer Zelle, beispielsweise links oben. Liegt der Wert dieser Zelle zwischen der Unter- und Obergrenze, soll er grün werden. Die Formel lautet:
=UND(B6>=B$5;B6<=B$3)
UND, weil beide Bedingungen erfüllt sein müssen.
B6 darf kein $-Zeichen haben – diese Zelle ist variabel, soll in der Position geändert werden.
B$5 und B$3 haben ein Dollarzeichen vor der Zeilennummer 3 und 5. Das bedeutet: beim Herunterziehen verändert sich die Zeile nicht – sie bleibt fix. Die Spalte B hat kein $-Zeichen – sie ist wieder variabel oder veränderlich.
Im nächsten Schritt wird der Bereich im Assistenten „Regeln verwalten“ auf den gesamten Bereich ausgedehnt:
Analog beim zweiten Schritt. Man könnte die Werte, die außerhalb liegen, mit zwei bedingten Formatierungen abarbeiten, oder mit einer. Ich entscheide mich für eine Bedingung. Die Formel lautet:
=ODER(B6<B$5;B6>B$3)
Auch hier gibt: B6 ist relativ, in B$3 und B$5 sind die Zeilen 3 und 5 fixiert; jedoch nicht die Spalte B. Und auch hier kann man im zweiten Schritt den Bereich erweitern. Das fertige Ergebnis:
Sie erstellt in Excel ein Diagramm, in dem zwei Datenreihen verwendet werden: die Differenz zum Vorjahr und die Absolutwerte. Das Ergebnis der Berechnung wird in einem Säulendiagramm mit zwei dargestellt. Eine Datenreihe wird ausgeblendet – die Beschriftung am oberen Rand dargestellt.
Warum nur, fragt Carmen, warm kann ich die Beschriftungselemente nicht ausrichten?
Ich möchte gerne ein Symbol in die Gruppe „Zahl“ einfügen, mit dessen Hilfe ich eine Zahl als Datum formatieren kann:
Meine Gegenfrage, ob nicht die Tastenkombination [Strg] + [#] gute Dienste tut, wurde verneint. „Ich hätte gerne zwei Symbole – eines für „kurzes Datum“, eines für „langes Datum“ war der Wunsch.“
Makros werden aber bei uns nicht unterstützt …
Schade – ich weiß keinen Ausweg! Keine Lösung für dieses Problem OHNE Makros.
ich lese regelmäßig deinen Blog „Excel nervt“ und hatte dir
vor einiger Zeit auch schon einmal eine Mail zu deinem Buch „Excel: Zahlen. Rechnen.
Formeln“ geschickt, die du mir sehr freundlich und ausführlich beantwortet
hattest.
Heute bräuchte ich mal deine Hilfe. Ich sitze hier vor einem Problem, bei dem ich alleine nicht weiterkomme. Es geht um einen Milchviehbetrieb, der seine Jungtiere von einem Aufzuchtsbetrieb großziehen lässt. Ich soll nun rückwirkend die Aufzuchtskosten pro Monat überprüfen. Dazu steht mir eine Tabelle zur Verfügung mit dem Abgangsdatum der Tiere vom Milchviehbetrieb (= Zugangsdatum Aufzuchtsbetrieb) und dem Zugangsdatum der Tiere beim Milchviehbetrieb. Ist das Tier aktuell noch beim Aufzuchtsbetrieb, ist jeweilige Feld für das Zugangsdatum leer. Pro Tag, den eine Kuh beim Aufzuchtsbetrieb ist, erhält der Aufzuchtsbetrieb eine Pauschale (z.B. 1€ pro Kuh und Tag). Ich hatte irgendwie gedacht, dass man das relativ leicht über eine Formel ermitteln könnte. Problematisch ist vor allem ein angebrochener Monat, wenn beispielsweise eine Kuh am 5. März 2021 an den Aufzuchtsbetrieb geliefert wird, erhält der Aufzuchtsbetrieb für diese Kuh ja theoretisch 27 €.
Anbei habe ich eine Beispieltabelle hinzugefügt. In den Spalten E bis R möchte für den jeweiligen Monat und für jede Kuh die Tage ermitteln, die diese beim Aufzuchtsbetrieb war.
Kannst du mir da weiterhelfen? Irgendwie stehe ich gerade
auf dem Schlauch.
Vielen Dank im Voraus.
Hallo,
hübsche Fingerübung.
Ich würde in die erste Zeile jeweils den 01. April 2020, 01. Mai 2020, … und als April 2020, Mai 2020, … formatieren. Also mit MMM JJJJ
Du musst einen „Baum abarbeiten“:
1. Fall: sind Jahr und Monat identisch -> dann rechne die Anzahl Tage bis Ende des Monats.
Die Erklärung: Ich brauche in diesem Fall die Anzahl der Tage bis zum Ende des Monats. Die Funktion
MONATSENDE($C2;0)
berechnet den letzten Tag des Monats (hier: 30.04.2020). Und davon wird das Datum abgezogen (hier: 11.04.2020). Das Ergebnis lautet 19; plus 1 = 20 = die Anzahl der Tage vom 11. bis zum 30. (beide einschließlich)
2. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem nächsten Monat -> dann voller Monat (die zweite WENN-Funktion wird die Stelle der 0 gesetzt:)
Für diese Kühe hatte ich die Auswertung händisch vorgenommen und komme auf das gleiche Ergebnis wie mit deiner Formel. Scheint also zu passen.
Einen „Baum“ hatte ich mir auch
schon überlegt, allerdings hatte ich Schwierigkeiten bei dem Umgang mit den
„angebrochenen“ Monaten und den Kühen ohne Zugangsdatum.
In der ersten Zeile hatte ich
sogar schon jeweils den 1. des Monats als Datum eingetragen, weil ich damit
rechnen wollte. Ich hatte es dann über „Zellen formatieren…“ – Kategorie: Datum
– Typ: Mrz. 12 umgewandelt. Blöd, dass Excel dann beim Mai auch einen Punkt
macht, wie mir gerade auffällt.
Vielen, vielen Dank für deine
Hilfe und mach weiter so. Ich werde deinen Blog auf jeden Fall weiter
verfolgen.
Amüsiert. Ich erstelle in Excel mit VBA eine Eingabemaske, in der verschiedene Begriffe stehen:
Wenn aus allen drei Listenfeldern etwas ausgewählt wird, werden die drei Begriffe in die entsprechenden Spalten eingetragen.
Klappt.
Danach wird die Auswahl entfernt
ListIndex = -1
Klappt nicht. Der Grund: das Ereignis Click deselektiert die drei Listen und DANN wird der Klick durchgeführt; das heißt: NUN ist ein Eintrag markiert.
Doof!
Ich mache mich auf die Suche, ob eines der Ereignisse ein Parameter Cancel besitzt, mit dessen Hilfe man ihn abbrechen könnte.
Fehlanzeige.
Also noch einmal schauen und probieren. Dann finde ich die Lösung: ich muss Click durch MouseUp ersetzen – DANN funktiert es: zuerst wird der Mausklick durchgeführt und DANACH der Code abgearbeitet (nicht umgekehrt wie beim Ereignis Click:
Private Sub lstRechts_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Ich öffne mein Excel, das auf dem Desktop installiert und darin eine Datei. Ich kopiere einen Teil einer Tabelle nach Excel online, das auf dem SharePoint gespeichert ist.
Allerdings: ich darf nicht über das Kontextmenü einfügen, sondern muss die Tastenkombination(en) verwenden.
Ich schätze es ausserordentlich, dass Du mich unterstützt und fühle mich geschmeichelt.
Du hast natürlich vollkommen Recht mit dem Hinweis, dass in einer Spalte
keine Zahlen und Texte stehen sollten.
Dieser Umstand ist dadurch entstanden, weil die Tabelle zusätzlich für
einen anderen Zweck benutzt wurde. Hierbei wurden die Zeilen insofern
erweitert, indem für jeden Kunden eine zusätzliche Zeile eingetragen wurde.
Hatte ein Kunde einen Service, wurde in der entsprechenden Spalte eine 1
reingeschrieben und im Anschluss mit einem Flow in einen ScharePoint Liste
übertragen. Anders hätte ich ja die Kunden Records nicht handeln können.
Nun habe ich den Servicekatalog und die Zuweisung der Kunden zum jeweiligen Service getrennt. Somit konnte ich die Tabelle vom Servicekatalog wieder «drehen», womit die Services in den Zeilen stehen und die Spaltenwerte zu den jeweiligen Daten passen.
Eine andere Lösung gibt es nicht, wenn ich Deine Erklärungen richtig
verstanden habe und eigentlich scheint es mir auch logisch.
Nun muss ich einige weitere Anpassungen an dem ganzen Konstrukt
vornehmen und einen neuen Flow erstellen. Dabei hoffe ich natürlich, dass ich
mir keine neue Baustelle geschaffen habe.
Nochmals herzlichen Dank für Deine wertvolle Hilfe und die guten Tipps
Herby
#####
Hallo Herby,
in meinen vielen
Jahren Exceltraining und meinen vielen Artikeln auf excel-nervt habe ich
gelernt, dass
* Excel ein sehr
gutes Programm ist
* Excel manchmal
etwas eigenwillig ist
* Anwender und
Anwenderinnen oft Wünsche haben, die sich SO nicht direkt umsetzen lassen (ich
verstehe oft die Hintergründe)
* dass man sich auf das Denken von Excel einlassen muss (ist halt ein Mann*) und man manchmal seine Daten etwas anders organisieren muss, damit man zum Ziel kommt
Liebe Grüße
Rene
*) Die Frage, ob Excel männlich oder weiblich ist, stelle ich häufig in Schulungen. Und amüsiere mich dann über die Antworten à la: „Excel ist männlich, weil …“ oder „Excel muss eine Frau sein, der nur so kann man sich erklären …“
In einem Ordner befinden sich mehrere Hundert Mails. Eine davon ist ungelesen. Ich finde sie nicht. Na – kein Problem, denke ich und füge das Feld „Gelesen“ aus der Feldliste hinzu. Leider kann man DARÜBER nicht sortieren:
Okay – noch ein Versuch: Filtern.
Erstaunlicherweise kann ich nach gelesen/ungelesen filtern:
Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:
Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel
Es ist unglaublich, aber ich habe wirklich das Gefühl, dass ich in jeden «Sche…sstopf» falle, welchen Microsoft zu bieten hat.
Seit 2 Tagen kämpfe ich mit dem Problem, dass in einer table in jeder Zelle scheinbar versteckte Tabs vorhanden sind. Dies hat natürlich die traurige Konsequenz, dass damit s- oder wverweise auf diese table kläglich scheitern und zu #NV Fehlern führen.
Zum Problem mit Tabs hast Du ja den Artikel tabulatoren | Excel nervt … (excel-nervt.de) geschrieben, doch in meinem Fall hilft mir dieser (wenigstens im Moment) nicht wirklich weiter.
Ich muss dazu vielleicht etwas ausholen und den Vorgang beschreiben, welcher mich zum Problem geführt hat. Am Anfang steht Excel File mit einer table. Diese table wird mittels Power Automate in eine SharePoint Online Liste geschrieben.
In einem anderen Excel File werden die Daten der SharePoint Liste wieder mit einer PowerQuery Abfrage eingelesen und stehen somit wieder in einer table, auf welche ich eben mit dem erwähnten wverweis zugreifen möchte. Der Befehl führt eben zu dem #NV und nach langem Suchen, habe ich letztendlich herausgefunden, dass in der abgefragten table in allen Zellen ein tab steht. Interessanterweise ist es aber so, dass in der table sämtliche Zellwerte linksbündig angezeigt werden. Klicke ich dann bei denjenigen Zellen welche eine Zahl enthalten nicht auf sondern in die Zelle, dann springen die Zahlen nach rechts (ohne dass ich ausser dem Klick in die Zelle etwas anderes mache) Noch verwirrender (wenigstens für mich) ist die Tatsache, dass die Zellformatierungen danach erhalten bleiben. Ich meine damit, dass diejenigen Zellen in welche ich wie beschrieben einmal reingeklickt habe, auch nach einem reload der Power Query Abfrage erhalten bleiben.
Hast Du vielleicht eine Erklärung für dieses Verhalten? Wieso und wann wurden die Tabs in die Zellen geschrieben und gibt keine Möglichkeit dies zu beeinflussen?
Bezugnehmend auf Deine vorherige Antwort ist es aber sicherlich schon so, dass man solche Phänomene auch mit der besten Schulung nicht abwenden kann ☹
Würde mich auf jeden Fall darüber freuen, wenn Du eine Idee zu meinem neuen Problem hättest
Lieber Gruss
Hallo Herby,
das Problem ist mir und vielen anderen bekannt – ich würde
es nicht als Anomalie, sondern als Bug von Excel bezeichnen.
– dort beschreibe ich mehrere Lösungen (mein Liebling ist Daten / Text in Spalten) und auch, wie dieses Phänomen zustande kommt.
Liebe Grüße
Rene
Hallo Rene
Danke für die abermals hilfreiche Unterstützung
Mein Problem schein aber irgendwie anders gelagert zu sein und entgegen meiner
vorherigen Problemschilderung ist es leider nicht so, dass der Fehler mit einem
Klick in eine der betroffenen Zellen «nachaltig» gelöst wird.
Zur besseren Veranschaulichung habe ich eine Kopie der Tabelle erstell, welche
auf der PQ Abfrage beruht. Am Bsp der Zelle B2 kannst Du sehen, dass der
Zellwert nach einem Klick in die Zelle, nach rechts gesprungen ist.
Sobald ich das bei irgend einer benötigten Zelle mache, welche einen Zahlenwert
enthält, springen die Werte nach rechts und die Formeln mit den darauf
referenzierenden Zellen, funktionieren.
Wenn ich hingegen die PQ Abfrage aktualisiere, springen die Zahlen wieder nach
links und die Formeln bringen den #NV
D.h die PQ Abfrage erzeugt die falschen Daten und dabei spielt es
überhaupt keine Rolle, wie die Zellen formatiert sind.
Die Spalten der Daten Quelle (ShareListe) sind ausnahmslos als standard
formatiert und dies lässt sich auch nicht ändern, da innerhalb einer Spalte
unterschiedliche Daten vorhanden sind.
Wie bei Excel gibt es beim PQ unter Transformieren/Bereinigen die Trim
Funktion, mit welcher eigentlich ein tab aus einer Zelle entfernt werden
sollte.
Aber bis dato ist mir dies damit nicht gelungen
Das Problem muss beim erzeugen der Tabelle gelöst werden, da die Daten
dynamisch sind und laufend aktualisiert werden. Oder anders ausgedrückt, eine
neue Abfrage würde die vormals vorgenommenen Korrekturen mir den Daten
überschreiben.
Das File Servicekatalog Quelldaten dient als Datenquelle, das heisst wenn sich
irgendwelche Daten vom Servicekatalog geändert haben, werden diese dort
eingepflegt. Eine Flow schreibt die Daten in die SharePoint Liste, welche dann
wie PQ Abfrage von überall in eine Servicekatalog.xlsx gelesen werden können.
Die Quelldatei hat das Problem auf jeden Fall nicht, d.h entweder auf dem
SharePoint oder bei anschliessenden PQ Abfrage wird ein problematischer tab
angehängt ☹
Vielleicht mache ich einen Denkfehler und/oder Du hast eine Idee, was ich
ändern muss
Lieber Gruss
Hallo Herby,
Das Problem ist Folgendes:
In einer Spalten stehen Zahlen und Texte.
Wird diese Liste nach PowerQuery „gezogen“ und dort der Typ nicht explizit angepasst, so bleiben die Zahlen Zahlen (rechtsbündig) und die Texte Texte.
Verwendet man in PowerQuery jedoch den Datentyp „Text“, dann „schiebt“ Excel unter diese Zahlen ein Textformat (das so nicht sichtbar ist).
Da die Zelle als Standard (oder Zahl) formatiert ist, verschwindet das Textformat beim Editieren (Doppelklick) der Zelle. Andererseits: Nach Aktualisierung von PowerQuery haben wir die gleiche Situation wie am Anfang.
Gegenfrage: Warum MÜSSEN in einer Spalte Zahlen und Texte
stehen? Das widerspricht einem Datenbankdenken.
Und: wenn schon Zahlen – dann sollten sie auch Texte bleiben
– als Informationen und nicht zum Rechnen verwendet werden.
Wie kann ich es erreichen, dass eine Datenreihe im 15-Minuten-Takt fortgesetzt wird? Beispielsweise für einen Stundenplan. Ich schaue nach:
Erstaunlicherweise lässt der Assistent „Datenreihe“, den man in der Gruppe „Bearbeiten“ in der Registerkarte „Start“ findet, keine Uhrzeiten zu …
Natürlich könnte man es mit einer Formel erreichen:
=A2+15/24/60
Da in der Schulung Anfängerinnen waren, die bislang noch wenig Erfahrung mit Formeln hatten, schlage ich die naheliegende Lösung vor: zwei Startzeiten eintragen, markieren und runterziehen:
Kennen Sie das? In einer Excelliste wurde ein Autofilter aktiviert. Ein Kriterium wird gefiltert. Nun wird auf die gefilterte Liste ein Rechteck gelegt, beispielsweise zur Kommentierung:
Wird nun der Filter entfernt wird die Ferm seeeeehhhhhhhr, seeeeehhhhhhhr lang:
Wir haben einen Ordner. Nennen wir ihn „Bilanz“. In diesem Ordner liegen zwei Dateien: August.xlsx und September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
Beide Dateien werden geschlossen, der Ordner wird umbenannt, beispiesweise in „Bilanz2021“. Das Öffnen und Aktualisieren der Datei funktioniert problemlos.
Wird haben einen Ordner. Nennen wir ihn „Bilanz“. Darin befinden zwei weitere Ordner: „August“ und „September“. Im Verzeichnis „August“ befindet sich eine Datei August.xlsx, im September-Verzeichnis eine Datei mit Namen September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
Beide Dateien werden geschlossen, der Ordner „August“ wird umbenannt, beispiesweise in „August2021“. Das Öffnen und Aktualisieren der Datei funktioniert JETZt nicht mehr:
Manchmal amüsieren und erstaunen mich Fragen in Excelschulungen. Beispielsweise folgende:
In einer Liste stehen Email-Adressen. Um herauszufinden, ob einige der Adressen doppelt vorkommen, wird eine Pivottabelle aufgesetzt, die Adressen werden gruppiert und gezählt:
Die Anzahlspalte wird absteigend sortiert.
Eine Teilnehmerin fragt, warum ein Doppelklick auf einen Eintrag (eine Mailadresse) die Möglichkeit bietet, weitere Details einzublenden, während ein Doppelklick auf die Anzahl diese aggregierte Zahl zu „entfalten“, also alle Datensätze anzuzeigen, die sich dahinter verbergen.
Clevere Frage. Und: ich weiß keine Antwort. Ich kann nur vermuten, warum Microsoft das SO eingerichtet hat.
Excelschulung. Wir erstellen ein Eingabeformular. In einer Zelle steht eine Prozentzahl – sie wird auf Eingabewerte zwischen 0% und 10% festgelegt – also in der Datenüberprüfung werden die Grenzwerte 0 und 0,1 eingetragen:
Ein Teilnehmer probiert aus und trägt die Zahl 25 ein. Das Prozentzeichen bleibt stehen:
Er erhält – wie erwartet – einen Fehlerwert. Die Zahl MIT Prozenzwert werden markiert:
Der Teilnehmer versucht es ein zweites Mal – diesmal trägt er 7,5 ein:
Da er das Prozentzeichen zuvor markiert hatte, wurde es gelöscht – 7,5 ist nun 7,5 und nicht 7,5%. Also erfolgt wieder eine Fehlermeldung!
Hum. Das heißt: man muss schon ganz genau hinschauen, was man einträgt und welche Mechanismen Excel verwendet …
Eine schöne Frage in der letzten PowerBI-Schulung:
Wie viele Funktionen kann man in DAX ineinander verschachteln. „Genug“ lautete meine Antwort. „Sehr viele“, um etwas präziser zu sein. Ich habe gesucht und nicht gefunden. Erstaunlich. Also habe ich ausprobiert. Aber 100 Ebenen habe ich aufgehört:
anbei die beiden Files (Visio und Excel Tabelle)
Bei der Tabelle handelt es sich um eine Copy aus einem sehr umfangreichen Excel Workbook, welches unter anderem eben die Tabelle produziert, welche die enthaltenen Services (Visio Shapes) steuert. Falls ein Kunden einen Service in einer Ausprägung bekommt, soll das entsprechend Shape auf dem Visio erscheinen.
Ein komischen Phänomen oder Verhalten ist mir bei der Fehlersuche aufgefallen. Wenn ich im Excel File in der Spalte Layer die Formel mit fixen Werten ersetze, dann läuft es irgendwie deutlich besser. Kann es sein, dass Visio im trotz dem vorher benötigten Datenabgleich im Hintergrund noch irgendetwas mit den Daten macht?
Ich habe keine andere Erklärung, wieso es ohne Formeln im Excel in Visio besser funktionieren sollte ???
Wenn ich Deine Bücher zu VBA und Visio Programmierung durchgearbeitet habe, bin ich sicher in der Lage den Visio Update direkt von Excel aus zu steuern
Freundliche Grüsse Herby
Hallo Herby,
wenn du bei bestimmten Fällen die Variable shapeOnLayer auf True setzt:
If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
shapeOnLayer = True
Else
und dies später abfragst:
If shapeOnLayer = False Then
lyr.Add shp, 0
End If
muss du am Anfang der Schleife die Variable wieder „zurück“ auf False setzen:
For iRow = 0 To UBound(rowIDs)
data = drs.GetRowData(rowIDs(iRow))
shapeId = data(idColumn)
lyrName = data(lyrColumn)
shapeOnLayer = False
Guten Abend René
Zuerst vielen Dank
Meine Programmier Skills sind leider zu
bescheiden um richtig folgen zu können.
Ich werde aber versuchen dies im Script
anzupassen und hoffe, dass es dann funzt. Auf jeden Fall ist es super, dass Du
die Probleme gefunden hast.
Lieber Gruss und einen schönen Abend (soweit man
das mit unserem tollen Somner überhaupt noch wünschen kann)
Herby
hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA
Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.
In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.
Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.
Hallo Johannes,
ich habe mal ein bisschen gewühlt und probiert:
1. Das SortObjekt existiert – allerdings besitzt es keine
Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem
Klassenmodul einzutragen:
Public WithEvents SO As so…
2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das
Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl
aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die
intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam,
dem Anwender die Symbole wegzunehmen.
3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.
mit großem Interesse verfolge ich Ihre Excel-Seminare und mag es sehr von Ihnen neue Dinge zu lernen und mein Wissen zu erweitern. Helfen Sie auch bei speziellen Excel-Problemen? Ich habe hier eine größere Datenbank. Basis sind verschiedene Materialnummer in einer Spalte, welche mehrmals auftreten, da es zu den Materialien mehrere Bestellungen gibt mit unterschiedlichen Konditionen. Mich interessiert pro Materialnummer der Maximalpreis. Ich habe hier an die 80 Materialien und dazu jeweils mehrere Bestellungen. Der Maximalpreis sollte dann in einer zz. Spalte erscheinen. Mit freundlichen Grüßen
Hallo Frau S., ich würde eine Pivottabelle erstelle. Gruppieren Sie die Materialnummern und ziehen Sie dann die Preise in das Wertefeld. Ändern Sie in den Wertfeldeinstellungen die Summe in MAX. Alternative: Wenn Sie Excel in Microsoft 365 haben: mit der Funktion EINDEUTIG erhalten sie die eindeutige Liste der Materialnummern. Mit MAXWENN können Sie das MAX pro ID berechnen lassen. Hilft Ihnen das? LG :: Rene Martin
Hallo Herr Martin, vielen Dank für die prompte Antwort. Die Pivottabelle ist der Lösung für mich. Entsprechende Seminare dazu stehen noch auf meiner persönlichen Agenda. Für den akuten Fall, haben Sie mir prima geholfen. Grüße,
Outlook-Schulung. Eine Teilnehmerin sagt, dass sie in Outlook über Ansicht den Aufgabenbereich „Aufgaben“ eingebunden hat. Allerdings werden die Aufgaben nur in diesem Aufgabenbereich angezeigt, wenn sie nach Outlook Heute (also auf ihr Postfach) wechselt.
Die Ursache ist schnell gefunden und erklärt: es gibt zwei Arten von Aufgaben: solche, die man selbst erstellt (sie heißen Aufgaben) und Mails, die man zur Nachverfolgung kennzeichnet (auf Wiedervorlage legt).
Damit die zwei unterschiedlichen Aufgaben zusammengefasst sind, kann man die Aufgaben von der Aufgabenliste in die Aufgaben schieben.
danke noch einmal für Deinen Hinweis!, allerdings gelingt mit solch ein Bild (siehe Deine EMail „Verweise VBAProject“ ) nicht. Da kommt kein „Solver“ vor. Und das Laden der „SOLVER32.DLL funktioniert zwar, aber Visio kann damit nichts anfangen (wie in meiner ersten Mail) schon geschrieben.
Nun habe ich ein 64-Bit -System. Leider ist aus der kargen Fehlermeldung die Ursache, dass Visio die SOLVER32.DLL nicht mag, nicht zu erklären. Und eine SOLVER64.DLL gibt es wohl nicht (soweit ich meine Suche auch ausdehnte).
Nun werde ich mich wohl mit der nächsten Microsoft-Unzulänglichkeit auch zufrieden geben müssen. So ein großer „Laden“ und soviel Mangel (es gibt ja auch so viel Versionen…)
Vergangenheit: Hätte ich im Steuerungsbereich meinen Kunden soviel „Nichtfunktion“ zugemutet – ich glaube, da wäre ich arm dran…
… und ich kann es nicht lassen, zu mailen, mit den Infos:
aktuelle Formel (Konstrukt von gestern), wo noch etwas fehlt: =WENN(ANZAHL(J9;L9;N9)=0;““; WENN(ODER(ANZAHL(J9;L9;N9)=2;ANZAHL(J9;L9;N9)=3); MITTELWERT(J9;L9;N9); „“))
drei Eingabezellen
wenn keine Eingaben in den Zellen >> Zelle mit Ergebnis ist leer
der MW wird berechnet, wenn zwei oder drei Zellen Werte enthalten
Frage: Kann man noch prüfen wenn ODER(Prüfung auf TEXT(J9), Prüfung auf TEXT(L9), Prüfung auf TEXT(N9)): „“ D.h.: Wenn mindestens eine der Zellen Text beinhaltet >>> Ergebnis der Zelle: Nix drinnen Ich habe schon ein wenig schlechtes Gewissen … Ich maile noch ein DANKESCHÖN & Gruß Jürgen
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Die Werte werden kummuliert. In der ersten Zelle steht
=WENN(F15<>"";F15;"")
Darunter befindet sich die Formel:
=WENN(G15<>"";G15+F16;"")
So hatte ich es vor vielen Jahren auch gemacht. Inwzischen löse ich das Problem der kummulierten Werte mit einer Formel, die ich nach unten ziehe:
=SUMME($F$15:F15)
Fazit: Lehrer und Lehrerinnen sollten regelmäßig ihre Übungsaufgaben durchsehen und überprüfen, ob es bessere Lösungestechniken, neue Funktionen gibt, mit denen man Aufgaben eleganer lösen kann und testen, ob sie einige Bedingungen vergessen haben.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Der prozentuale Anteil wird berechnet:
=WENN(UND(A15<>"";B15<>"");D15*100/$D$25;"")
Okay, mit der Funktion
UND(A15<>"";B15<>"")
wird überprüft, ob die Zellen der Spalte A und B gefüllt sind. Eine hübsche Übung, um UND und WENN zu üben.
Dennoch: WENNFEHLER wäre sicherlich besser gewesen, um ALLE Fehler abzufangen.
Aber noch mehr irritiert mich die Berechnung
D15*100/$D$25
Warum wird mit 100 multipliziert? Ich hätte den Anteil von einer Dezimalzahl in eine Prozentzahl formatiert. Und damit weitergerechnet.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Ich komme bei der Verwendung der Funktion RANG ins Grübeln:
=RANG(B15;$B$15:$B$24;0)
Wenn man diese Funktion in Excel eintippt, sieht man vor dem FUnktionsnamen ein Ausrufezeichen. Das bedeutet, dass diese Funktion durch andere erstetzt wurde und aus Kompatibilitätsgründen noch zur Verfügung steht. Richtig: in Excel 2010 wurd diese Funktion RANG durch RANG.GLEICH und RANG.MITTELW abgelöst. Man findet RANG nun in der Kategorie Kompatibilität:
Das Problem bei der Funktion Rang ist die Antwort auf die Frage, welche Zahl weisen wir zwei gleich großen Werten zu? Also: wenn es den größten Werte zwei Mal gibt? Zählen wir dann 1; 1; 3 (so rechnet RANG.GLEICH und RANG) oder 1,5; 1,5; 3 – so rechnet RANG.MITTELW.
Ich probiere es aus, ändere zwei Werte so, dass sie gleich groß sind. Das Ergebnis: Fehler in der weiteren Berechnung, die nicht abgefangen wurden:
Fazit: vielleicht hätte der Lehrer oder die Lehrerin in den letzten zehn Jahren einmal das Beispiel neu nachrechnen sollen und auf Konsistenz prüfen sollen. Und vor allem: prüfen sollen, ob es inzwischen nicht neue, bessere Funktionen zur Lösung des von ihm oder ihr gestellten Problems gibt.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
In Spalte A stehen Lieferantennummern, in Spalte B Umsatzzahlen. In Spalte C wird der Rang berechnet:
Danach Nummer 3, Nummer 4, … Uff! Kennt der Lehrer oder die Lehrerin nicht die Funktion ZEILE? Ich stutze und wundere mich …
#####
Hallo Rene,
kannst du vielleicht auch deinen Verbesserungsvorschlag für die Formel dazu schreiben. Mir erschließt sich grad nämlich nicht, wie man die Funktion ZEILE hier einbauen soll. Wahrscheinlich steh ich nur auf dem Schlauch…
Lieber Anonymous,
die Funktion ZEILE hat zwei „Gesichter“:
=ZEILE()
liefert die Zeilennummer der aktuellen Zelle. Steht also diese Funktion in C7,liefert =ZEILE() die Zahl 7. Beim Herunterziehen erhalte ich 8, 9, 10, …
Die Funktion der Lehrerin oder des Lehrers
VERGLEICH(1;$C$15:$C$24;0)
sucht den Wert 1 in der Rang-Spalte, also die Zeile, die den größten Wert enthält.
VERGLEICH(2;$C$15:$C$24;0)
sucht den zweitgrößten Wert.
Ich bin sicher, dass die Lehrerin oder der Lehrer die Werte 1, 2, 3, 4, … per Hand getippt hat. Da die Formel in D15 stand, hätte man auch schreiben (und herunterziehen) können:
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:
Ich sehe folgende Formel:
=WENN(A17="";"";SVERWEIS(A17;Leistung;2))
Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?
In der letzten Outlook-Schulung erzählt mir ein Teilnehmer, dass er gerne mit Kategorien arbeitet. Er hat sich mehrere Kategorien angelegt und weist den Mails diese Kategorien zu. Manche Mails liegen auch auf zwei Kategorien. Dann sortiert (also gruppiert) er nach Kategorien. Soweit so gut.
Nun möchte er eine Mail aus einer Kategorie löschen. Diese Mail steht jedoch ein zweites Mal in einer anderen Kategorie. DORT soll sie jedoch nicht gelöscht werden. Er will auch nicht die Kategorie von der Mail entfernen, da die Kategorie im Archivordner noch benötigt wird.
Er hat die Antwort selbst gegeben: Er kopiert die Mail, so dass sie zwei Mal vorhanden ist. Eine andere Lösung habe ich auch nicht gefunden.
ich
habe eine Frage zum Liniendiagramm, leider stimmen meine Werte nicht und ich
bekomme es nicht hin ohne das die Linien aus meinem Diagramm verschwinden.
Vielleicht
könne sie mir spontan helfen.
Danke
Hallo Frau D.,
Sie müssen die Reihe mit den „großen Zahlen“, also Jergl, Michal, Hans und Veitli auf eine Sekundärachse legen. Und dann möglicherweise die Skalierung der beiden Achsen anpassen.
Hoffe, dass du, deine Familie und dein Umfeld alle gesund seid.
Du hast mir vor einiger Zeit geholfen mehrere Belegnummern in eine Zelle zu schreiben. Nun wollte ich auch das Datum (auch mehrere) auf gleiche Weise anzeigen lassen. Herausgekommen sind dann die Zahl(en) vom Datum. Wenn nur eine Zahl (=Datum) angezeigt wurde konnte ich das Datumformat. Wenn nur ein Zahlenwert vorhanden ist, habe ich, wie gelernt, die Formel mit 1 multipliziert und dann das Datumformat angewendet. Meine Vermutung ist, dass man die Formel um eine Formatierungsformel ergänzt. Videos über die allgemeine Formatierung mit Formeln in der Zelle habe ich leider nicht gefunden. Bitte um Lösung.
der Kunde hat ein sehr „altes“ Excel (Excel 2013? Excel 2016?). Ich verwende die Funktion TEXTKETTE (in VBA: CONCAT). Genauer: mit dem Befehl
Do Until Len(Application.WorksheetFunction.Concat(xlBlatt.Range(xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 0), xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 8)))) = 0
lngAnzahl = lngAnzahl + 1
Loop ' -- wie viele Zeilen sind gefüllt (wird in der Variable lngAnzahl gespeichert)
überprüfe ich, in wie vielen Zeilen der Liste etwas steht, beziehungsweise, ich suche die Zeile, in der die ersten neun Spalten leer sind oder ob die Formeln, die darin stehen, „“ ergeben.
Ich habe es geändert. Könntest du ihnen bitte diese Version schicken
Nicht nur Excel nervt – manchmal auch andere. Gestern hat mich Outlook erstaunt.
Outlook-Schulung. Wir sortieren unsere Mails nach Kategorien und nach den Fähnchen zum Nachverfolgen, indem ich auf die Überschrift klicke:
Eine Teilnehmerin möchte nach beidem sortieren: zuerst nach Kennzeichnungsstatus und anschließend nach Kategorie. Kein Problem: Über die Registerkarte Ansicht öffne ich die Ansichtseinstellungen und klicke dort auf die Schaltfläche „Sortieren“. Ich sortiere nach „Kennzeichnungsstatus“ und anschließend? Ich finde die „Kategorien“ nicht.
Okay, in den „verfügbaren Feldern“ gibt es „Alle Dokumentfelder“. Und dort „Kategorien“. Aber das führt zur Frage, ob ich eine neue Spalte hinzufügen möchte:
Ein Klick auf „Ja“ und ich habe eine weitere leere Kategorienspalte.
Erstaunlich: beim Suchen-Dialog gibt es weder eine Liste „Alle Felder“ noch kann ich die Kategien finden, um danach zu sortieren.
Und: ein erster Klick auf die Überschrift „Kategorie“ und ein zweiter auf „Kennzeichnungsstatus“ bewirkt nicht das Gewünschte: Outlook hat eigene Sortiervorstellungen.
Habe ich etwas übersehen? Oder nervt Outlook auch? Manchmal.
Gestern in der Excelschulung. Interessante Frage: Wenn ich ein Datum herunterziehe, ist die Schrittweise ein Tag. Wenn ich eine Uhrzeit herunterziehe ist die Schrittweise eine Stunde. Kann ich das in Excel einstellen, dass Excel im 15-Minuten-Takt zählt?
Ich überlege. Da gibt es doch den Assistenten Ausfüllen / Datenreihe im Register „Start“:
Allerdings: dort kann man nur die Schrittweite für Datumsangaben – nicht für Uhrzeiten eintragen:
Damit bleibt nur:
Entweder zwei Uhrzeiten eintragen, markieren und herunterziehen
Oder eine Formel: Bezug auf die obere Zelle und dann plus 15 (Minute) / 24 (Stunden) / 60 (Minuten):
Excelstammtisch. Hartmut zeigt, dass man das Datenmodell von Excel nach PowerBI importieren kann.
Ich frage, ob er wisse, wann das zu Problemen führt. Und zeige eine Datei:
Darin befinden sich Tabellen, die ins Datenmodell geladen wurden. Mit Hilfe des Datenmodells wurde eine Pivottabelle erstellt. Die Tabellen wurden mit Measures angereichert und sind untereinander verknüpft.
Nun will ich diese Datei (genauer: die Daten, Verknüpfungen und Measures) nach PowerBI importieren:
Ich erhalte eine Fehlermeldung – fast nichts wird importiert:
Wir machen uns auf die Suche – Hartmut wird fündig. Man darf nicht die Daten in Tabellen in der Arbeitsmappe halten und diese ins Datenmodell laden, sondern man muss sie mit PowerQuery importieren. So:
Diese Daten werden nun ins Datenmodell geladen – dort kann man sie verknüpfen
und mit Measures anreichern:
Das Ergebnis:
Ein erneuter Import nach PowerBI Desktop:
Klappt!
Ein Dankeschön an Hartmut Hilbich für das Suchen und Auffinden der Lösung des Importproblems. Hartmut schreibt dazu:
„Das Problem bestand hier (besteht!) darin, dass PBID das Datenmodell selbst sehr wohl importiert, aber nicht gleichzeitig auch die Quelltabellen!
Ich habe die
Quelltabelle mit PQ abgefragt und das PP-Modell exemplarisch mit 2 Measures
versehen. Der Import in PBID funktioniert dann einwandfrei!
Mein Fazit: Es ist nicht
ratsam, die Quelldaten physikalisch gemeinsam mit dem PP-Modell zu speichern.
Also entweder die Daten direkt mit PP abfragen, oder aber (besser) mit PQ
abfragen. Was also innerhalb von PP kein Problem ist, wird dann aber eines beim
Import in PBID.
So schwierig kann das wohl nicht sein, dachte ich. Und probierte es. Allerdings: die Lösung des Problems war doch komplizierter als gedacht.
Vor einigen Jahren hatte ich die Aufgabe in einer sehr großen Excelliste (zirka 60.000 Zeilen) die Daten „zu putzen“. Mitarbeiterinnen und Mitarbeiter hatten an unterschiedlichen Stellen in einer Spalte Informationen eingetragen – allerdings mehrere Informationen getrennt durch Trennzeichen. Durch verschiedene Trennzeichen – mal ein „/“, mal ein Semikolon, mal ein „:::“, mal ein „-„:
Ich habe damals einige VBA-Makros geschrieben, um die Daten „zu putzen“. Ich frage mich, ob man sie mit PowerQuery bereinigen kann. Man kann!
Ich erstelle eine Liste der Trennzeichen:
Ich importiere die Daten und trenne die Liste „hart“ an einem Zeichen:
trennt die Spalte. Wie kann man alle Trennzeichen verwenden? Ich importiere die Trennzeichenliste und wandle sie in über Transformieren / In Liste konvertieren in eine Liste um:
Kann SplitTextByDelimiter meine tbl_Trennzeichen verarbeiten? Nein!
Ich gehe auf die Suche:
SplitTextByAnyDelimiter kann die Liste verarbeiten:
Schlecht! Ich schaue den Parameter genauer an – er heißt:
columnNamesOrNumber
Also versuche ich eine Zahl. Ich beginne bei 99:
Der linke Teil sieht vielversprechend aus:
– der rechte nicht:
Ich überlege: ich muss berechnen wie viele neue Spalten erzeugt werden. Ich muss berechnen wie oft die Trennzeichen der Liste tbl_Trennzeichen in jedem der Texte vorkommt.
Leider stellt PowerQuery keine Funktion zur Verfügung, mit deren Hilfe man die Anzahl der vorkommenden Zeichen in einer anderen Zeichenkette ermitteln kann. So etwas berechne ich (auch in Excel) immer wie folgt:
Länge(Zeichenkette) - Länge(Zeichenkette ohne gesuchten Zeichen)
Ich überlege: minus jedes Element der Liste. Also genauer:
(Text as text) =>
List.Accumulate(
tbl_Trennzeichen,
0,
(state, current) =>
state +
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
)
Ich muss die Anzahl kumulieren. Der Befehl List.Accumulate tut gute Dienste. Er möchte eine Liste haben (tbl_Trennzeichen), einen Beginn (0) und eine Funktion. Diese Funktion erhält zwei Teile:
(state, current)
Die Variable state „merkt“ sich die Zahl, current greift auf jede Zeile zu. Allerdings darf ich nicht einfach die Differenz aus Länge vorher und Länge nachher bilden:
Gemeint ist – umgangssprachlich – der mittlere Wert einer Datenreihe. Hat man zwei mittlere Werte (bei einer geraden Anzahl Elemente), berechnet sich der Median als Durchschnitt der beiden mittleren Werte.
Wer braucht denn so etwas?
Stellen Sie sich vor, sie haben eine Basketballmannschaft, die aus fünf Spielern besteht: vier recht kleinen und einem sehr langen Spieler. Der Mittelwert würde das Bild „verzerren“, weil der Ausreißer den Durchschnitt nach oben verschiebt. Der Median gibt dagegen ein besseres Bild der Mitte, weil er resistent gegenüber Ausreißern ist:
Median – eine statistische Größe also. Nun finde ich in einer Berechnung von Transportkosten folgende Formel:
Verwundert reibe ich die Augen? Warum werden die Logistikkosten mit einem Median berechnet?
Dann verstehe ich: es wird Bezug genommen auf die drei Werte Länge, Breite und Höhe. Von diesen drei Werten wird der größte Wert (MAX), der kleinste Wert (MIN) und der mittlere Wert (MEDIAN) berechnet. Sehr clever!
„Kompilierungsfehler im ausgeblendeten Modul. Dieser Fehler tritt häufig auf, wenn der Code nicht mehr mit der Version, Plattform oder Architektur dieser Anwendung kompatibel ist. Klicken Sie auf „Hilfe“, um Informationen dazu zu erhalten, wie Sie diesen Fehler beheben können.“
Der Fehler entsteht, wenn ich im Code im Modul basKonstanten die Versionsnummer ändern will.
Nach Änderung und Neustart der Tabelle kommt o.g. Fehler. Danach kann kein Dashboard mehr ohne Fehler aufgerufen werden!
Wenn das so wieder wie vorher funktioniert, dann wäre es
perfekt.
Mit freundlichen Grüßen
Hallo Herr L.,
der Grund des Fehlers ist Folgender:
Das Datum war als Datum definiert in der Form #Monat/Tag/Jahr#. Ich weiß nicht, was Sie eingetragen haben – aber vielleicht nicht als Datum. Da dieses Datum nur einmal als Text verwendet und angezeigt wird, habe ich einen Text daraus gemacht (somit wird nicht mehr impliziert konvertiert – hier lief wohl etwas schief).
Public Const p_cdatAppStand As String = "08.07.2021"
Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:
Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:
=Blattname!Zellbezug
Prima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel
=BMW!Z8
gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.
Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:
=’Alfa Romeo‘!P1
Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:
Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.
Und wie sieht der Bezug auf dieses Blatt aus?
='Mercedes'' Benz'!W117
Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!
Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!
Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!
Hallo Herr Martin, im Anhang sende ich Ihnen eine Exeltabelle mit einer „mauell erstellten Kopfzeile“ und einer Zeilenschaltung in der Zelle „Anschrift“. Für einen Serienbrief benötige ich die „Kopzeilen“ ebenso die Zeilenschaltung nicht. Wie entferne ich am schnellsten die „Kopfzeilen“ und die Zeilenschaltung in der Zelle. (Teilenschaltung in einer Zelle zu entfernen, habe ich in Ihren Videos schon gefunden). Ich möchte alle Daten in einer Spalte haben. Ich würde mich freuen, wenn Sie mir dabei helfen würden. Mit freundlichen Grüßen PV (Ein Fan Ihrer Office-Kurse)
Hallo Herr V.,
das habe ich
gemacht :
* Mit Suchen und Ersetzen die Zeichenschaltung (Strg + J) durch einen Schrägstrich ersetzt. (die Zeichenschaltung hat den Code 10 – man kann auch die Funktion WECHSELN verwenden:
=WECHSELN(F4;ZEICHEN(10);"/")
* den Verbund aller verbundenen Zellen aufgehoben
* den Textumbruch entfernt
* mit einem AutoFilter in der Spalte „Card Number“ den Text „Card Number“ und die leeren Zellen gefiltert und entfernt.
* die leeren Spalten gelöscht.
Hallo Herr Martin, ich habe noch etwas vergessen. In der Spalte „Badge Holder Name“ sind viele Namen nicht korrekt geschrieben. Wie kann ich Straßennamen schnell ändern. Ich mache es oft über Suchen + Ersetzen. Aber in vielen Situationen ist das nciht möglich. Gibt es spezielle Formel oder Funktionen die das erleichtern. Nach dem Säubern der Exeltabelle, wie kann ich dann automatisch eine Kopfzeile erstellen, so das man die Mitgliederliste anschließend ausdrucken oder auch ein PDF schreiben kann. Ich freue mich über Ihre Nachricht.
Und zu Ihrer Frage: wenn Sie immer die gleichen Ersetzungen haben, erstellen Sie eine Tabelle und verwenden die Excel-Funktion WECHSELN.
In Excel liegen einige Zahlen als Währung vor. Ich erstelle ein 2D-Säulendiagramm mit gruppierten Säulen. Ich lasse mir die Datenbeschriftung der Säulen anzeigen und drehe die Zahlen um 90 Grad:
An anderer Stelle wird eine weitere Datenreihe eingefügt – diese soll mit ins Diagramm integriert werden:
Das Verblüffende: Die neuen Zahlen werden nun mit der Währung $ formatiert:
Werden die Zahlen mit dem Zahlenformat Buchhaltung formatiert, werden drei Nachkommastellen eingefügt:
Auch bei Dezimalzahlen tritt dieser Effekt auf:
Jedoch: NICHT IMMER! Ich weiß nicht, wann diese Zahlen falsch formatiert werden:
Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:
„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.
Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.
Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.
Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:
Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.
Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.
prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):
Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:
PowerBI-Schulung. Wir greifen auf Excelmappen zu, die auf Sharepoint liegen. Es kommt die Frage, ob man auch auf einen Sharepoint-Ordner zugreifen kann. Klar kann man:
Man muss nur den Ordnerpfad kopieren und eintragen:
Und – erhält einen Fehler:
Ach, klar, natürlich: man muss sich natürlich noch anmelden. Ist ein bisschen versteckt:
Erstaunlicherweise kann man JETZT OHNE Anmedlung in Excel über PowerQuery auf einen Sharepoint-Ordner zugreifen. Muss ich das verstehen?
Amüsant: ich habe eine große Excelliste mit mehrere Tausend Datensätzen. Ich bearbeite sie in PowerQuery:
Ich importiere eine zweite Liste und verknüpfe sie mit einem Left outer Join:
Das Ergebnis sieht in PowerQuery gut aus:
Ich lade die Tabelle zurück nach Excel und erhalte einen Fehler:
Zurück zu PowerQuery versuche ich einen Right outer Join:
Die Ursache? PowerQuery zeigt nur 1.000 Datensätze. Wenn in der Liste DANACH eine Zelle mit einem fehlerhaften Wert steht, wird er bei einem Left Outer Join nicht angezeigt. Erst in Excel. Natürlich kann man sich in PowerQuery auf die Suche nach dem fehlerhaften Datensatz machen und ihn entfernen. Oder in Excel:
Danke an Christa für diesen Hinweis und danke für die Bemerkung, dass die Fehlermeldung in älteren PowerQuery-Versionen eine andere war:
Konto anlegen und löschen funktioniert soweit, bis auf das Problem, was wir schon mal hatten und gelöst wurde (Numerischer Kontoname z.B. 01 -> wird darauf 1 generiert!). Das hatten Sie bereits schon super umgesetzt.
Also formatiere ich die Spalte mit den Kontonummern erneut als Text. Mal sehen, wann Herr L. es wieder „kaputt macht“.
Herr L. schickt mir ein Video, das er erstellt hat, damit ich besser erkenne, woran der Fehler liegen könne, der in Excel auftritt. Ich öffne das Video und erhalte folgende Fehlermeldung:
Wiedergabe nicht möglich. Schaffen Sie den Inhalt erneut an.
Ich öffne eine Testdatei, die mir ein Anwender zugeschickt hat, und wundere mich, wo das Menüband (das Ribbon) ist.
Dann fällt es mir ein: es gab da doch … Richtig:
Es gab einen Excel 4.0-Makrobefehl, mit dem man die Symbolleiste ausblenden konnte. Dieser funktioniert im aktuellen Excel noch immer und blendet hier das Menüband aus. Der Befehl lautet:
mein Bekannter hat
sich die neue Version angeschaut.
Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.
In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.
Hallo Herr L.,
öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.
Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.
Sie erhalten den Fehler.
Heißt: DIESER
Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.
Woher kommt er?
Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:
Sie greifen auf den
VALUE dieser Zelle zu – das knallt!
Die Ursache des
Fehlers:
In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!
=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))
Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.
An dieser Maske dlgKundenverwaltung habe ich nichts geändert.
schöne Grüße
Rene Martin
*) An Gott glauben wir; alle anderen müssen Daten zeigen!
Ich erstelle ein Eingabeformular in Excel für eine Firma. Da mit einem Wert, beispielsweise 7,75 €, weitergerechnet wird, lösche ich den Text „7,75 € (nicht enthalten in Frachtraten)“, der in der Zelle stand, ersetze ihn durch die Zahl 7,75 und formatiere die Zelle mit einem benutzerdefinierten Zahlenformat
0,00" € (nicht enthalten in Frachtraten)"
Nach einer Weile stelle ich erstaunt fest, dass der Text als Quickinfo angezeigt wird. Was passiert hier? Was habe ich hier gemacht?
Nein – es liegt keine Notiz und kein Kommentar unter der Zelle.
Nein – es liegt keine Datenüberprüfung mit einer Eingabemeldung unter dem Text.
Nein – es wurde auch kein QuickInfo eines Hyperlinks verwendet:
Erstaunt reibe ich die Augen. Das habe ich selbst gemacht! Aber wie?
Schließlich komme ich hinter des Rätsels Lösung:
Trägt man in Excel in eine Zelle eine Zahl ein, formatiert diese Zahl (beispielsweise als Währung) und verkleinert die Spalte so, dass die formatierte Zahl in der Zelle nicht angezeigt werden kann, wird die formatierte Zahl als Quickinfo angezeigt, wenn sich der Mauszeiger darüber bewegt. Aha!
Wenn ich nun mehrere Zellen markiere und die formatierte Zahl „über die Auswahl zentriere“:
wird die Zahl klar lesbar in den Zellen angezeigt. Da die Zahl aber für die Zelle, in der sie sich befindet, zu „breit“ ist, bleibt das QuickInfo stehen:
Eben! Und so kann ich eine Zahl über mehrere Zellen ausrichten …
mir ist ein Fehler
aufgefallen, der vorher nicht vorhanden war.
Die Excel-Tabelle
in Excel 365 funktioniert tadellos, soweit ich getestet habe.
Ein Freund von mir
hat leider aus der alten Version die Daten nicht in die neue Version reinbekommen
und so hat er sich entschlossen, diese neu einzugeben.
Wenn er ein Konto
anlegt – das geht noch.
Dann will er
Einzahlungen in das Einzahlformular hinzufügen, dies scheint erst mal nicht zu
funktionieren. In der Liste zeigt er nichts an!
Wenn ich mir die
Tabelle außerhalb des Formulars anschaue, sind die Daten angelegt – Die
Paketnummer wird nicht mehr hochgezählt.
Auch ein speichern
und erneuter Start der Tabelle bringt keine Abhilfe.
In der alten Version (bei mir 6.11) funktioniert es noch tadellos. Mein Bekannter setzt Excel 2016 (neuste Updates sind installiert) ein. Bei mir geht es, bei ihm nicht, mit der letzten Version.
#####
Hallo Herr L.,
in Ihrem Programm finde ich die Codezeile:
If rngI.Value = Me.cmbAuswahlKontoAlleInvestments.Value Then
Sie prüfen, ob eine Kontonummer ausgewählt wurde. Da Kontonummern Zahlen sein können (4711) vergleichen Sie diese Zahl mit dem TEXT aus der Combobox (alle Steuerelemente liefern immer Texte).
Deshalb kann es
nicht funktionieren! Der Fehler war vorher schon vorhanden; ist Ihnen in IHRER
Liste nicht aufgefallen, weil dort alle Konten alphanumerisch ist.
Ich habe es korrigiert:
If CStr(rngI.Value) = Me.cmbAuswahlKontoAlleInvestments.Value Then
Gestern habe ich für den Excelstammtisch einige Dateien vorbereitet, um zu zeigen, was PowerQuery verlangsamt. Ich habe eine Liste mit Dummy-Namen mit 20.000 Datensätzen:
Diese verknüpfe ich mit einer Liste, die zwei Zeilen lang ist:
Das Ergebnis: 28.877 Datensätze
Ein zweiter Blick auf die Liste zeigt, dass einige Zeilen (nicht alle!) nun zwei Mal in der Liste auftauchen:
Nein – an der Verwendung eines Primärschlüssels liegt es nicht – die Zeile
Trägt man in Word in einer Tabelle Text ein, verbreitet sich die Spalte, in der der Text steht.
Diese (oft nicht gewünschte) Eigenschaft kann über das Symbol „AutoAnpassen“ der kontextsensitiven Registerkarte „Layout“ ausgeschaltet werden. Dort kann man von der Option „Automatisch an Inhalt anpassen“ an „Feste Spaltenbreite“ umschalten.
Leider zeigt Word nicht an, welche Option aktiviert wurde – keiner der drei Optionen ist mit grauer Farbe unterlegt.
Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:
In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …
Mich beschleicht ein Gedanke …
Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*
Ich bin verblüfft. Dass Excel sehr viele Algorithmen beinhaltet, die zum Teil hilfreich zum Teil verwirrend sind, ist bekannt. Aber diesen Mechanismus kannte ich bislang noch nicht:
Drei untereinander stehende Zellen sind auf die gleiche Art formatiert (beispielsweise Zahlenformat oder Hintergrundfarbe) und mit Text gefüllt.
Trägt man eine weitere Information darunter ein, so wird das Format übernommen. Das war mir bekannt. Aber ich wusste nicht, dass es auch bei einer Leerzeile funktioniert:
Hat man eine formatierte Zelle und darunter eine Leerzeile, so wird die nächste Zelle bei der Texteingabe wieder formatiert. Beim dritten Mal endet der Spuk jedoch:
Das kann praktisch sein, es kann aber auch verwirrend oder störend sein.
Immerhin: man kann es deaktivieren über: Datei / Optionen / Erweitert / Gruppe [Bearbeitungsoptionen] / Datenbereichsformate und -formeln erweitern.
Ich habe in angehängter Datei im Inhaltsverzeichnis den Fall, dass die Seitenzahlen in einigen Überschriften (Brote, Pizza, Gebäck, …) nicht rechtsbündig stehen.
Hast Du eine Idee, woran das liegt?
Danke, Gunnar
klar, Gunnar,
Word: davon lebe ich auch. Normalerweise.
Die Antwort ist denkbar einfach: „Brote“ und „Gebäck“ basieren auf der Formatvorlage „Verzeichnis 2“ und „Verzeichnis 3“. Dort ist ein hängender Einzug von 1,25 cm eingestellt. 1,25 cm heißt um 1,25 cm, nicht auf die Position 1,25 cm. Da diese Wörter kürzer als 1,25 cm springt der Tab auf den gesetzten Einzug.
Die Lösung: Einzug auf 0,5 cm oder 0 setzen. Dann klappt es.
Liebe Grüße
Rene
Fazit: Nicht alles, was nach Bug aussieht, ist auch einer. Manchmal sind auch die Menschen, die vor dem Computer sitzen, die Ursache für Fehler.
Einzelplatzschulung. Oder „Coaching“, wie es auf Deutsch heißt. Ein älterer Herr möchte die Grundlagen der Anwendungsprogramme lernen.
Wir beginnen mit Outlook. Die Möglichkeit einen Screenshot zu erstellen und in eine Mail einzufügen gefällt ihm. Ich erkläre ihm das Vorgehen:
Es funktioniert auch in anderen Programmen, erläutere ich und zeige ihm Excel. In der Registerkarte „Einfügen“ fällt sein suchender Blick auf die Mitte der Registerkarte, wo „Screenshot“ auch in Outlook zu sehen war. Kein Screenshot!
Ein Blick streift nach recht – kein „Screenshot“-Symbol. Ah – etwas weiter links – DA werden wir fündig.
Und PowerPoint? Dort finden das Symbol noch weiter links:
Immerhin: in allen Anwendungsprogrammen (auch Word) befindet sich dieses Symbol in der Gruppe „Illustrationen“.
Um die Suche zu erleichtern, erläutere ich ihm das Windows-eigene Programm „Snipping Tools“.
Letzte Woche hatte ich eine Einzelplatzschulung. Nennen wir es Coaching: ein älterer Herr wollte sich fit in den Anwendungsprogrammen machen. Ich erkläre die Grundlagen: Text und Zahl. „Bitte schließen Sie die Eingabe immer mit [Enter] ab.“ (Dass es noch andere Möglichkeiten gibt, wusste er selbst.) „Bitte drücken Sie immer die Ok-Taste. In der Kirche wird sie auch die [Amen]-Taste genannt.“ Er schmunzelt.
Wir üben Formeln und drücken [Enter]. Immer. Nicht wegklicken! Okay – verstanden.
Wir ziehen die Formel am Ausfüllkästchen nach unten. Er drückt [Enter]. Okay … ich muss mich ein bisschen korrigieren.
Ich erstelle eine Userform in Excel VBA. Darin sollen Werte „nach unten“ weitergegeben werden, wenn ein Kontrollkästchen angeklickt wird. Die Kästchen heißen chkFamilie02, chkFamilie03, chkFamilie04, … chkFamilie12
Ich prüfe, ob das letzte ausgewählt wurde oder ob noch weitere unten ausgewählt wurden:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False Or frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False Or intZeile > 12
Das läuft an die Wand – eine Fehlermeldung ist die Folge. Mein Denkfehler:
wenn intZeile > 12, dann wird das geürft. Beispielsweise: intZeile hat den Wert 13. Allerdings: es wird auch geprüft, ob das Control chkFamilie12 sichtbar ist. Und das gibt es nicht!
OR (und auch AND) in VBA prüft (leider!) immer alle Teile. Und stoppt nicht, wenn einer der beiden Zweige falsch ist. Also anders gelöst – nicht ganz elegant – aber okay:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False
strZeile = Format(intZeile, "00")
frmAuswahl.Controls("lblInfoZeile" & strZeile).Caption = strText
intZeile = intZeile + 1
If intZeile > 12 Then Exit Do
Loop
Ich habe hier einige Artikel zu dem kostenlosen PlugIn Excel-DNA geschrieben, mit dem man Excelfunktionen (und andere Werkzeuge) in Excel schreiben kann und welche Probleme es dabei gibt.
Nun habe ich einen Artikel – nein: genauer: es sind drei Artikel – darüber veröffentlicht: in der neuen dotnetpro.
Die Datenüberprüfung in Excel ist hinlänglich bekannt. Ein sehr praktisches Werkzeug, mit dem man eine vordefinierte Liste an Begriffen für Zellen bereitstellt.
Oder sicherstellt, dass nur bestimmte Werte in eine Zelle eingetragen werden.
Allerdings: bei sehr vielen Auswahlmöglichkeiten (sprich: langen Listen) ist die Suche und die Navigation sehr mühsam. Außerdem muss man sich auf feste Listen beschränken und darf keine freien Einträge verwenden. (ich habe auf diesem Blog schon einige Male gelästert und gespottet). Ich habe ein kleines Add-In geschrieben – Strg + Q (so wird es gestartet), mit dem eine bequeme Auswahl und eine freie Texteingabe möglich ist.
Interessiert? Für private Nutzung stelle ich es kostenlos zur Verfügung; für geschäftliche Verwendung hätte ich gerne 10 Euro. Dann erhältst du auch eine genaue Beschreibung der Installation und Bedienung und eine Rechnung. Interessiert? Gerne kannst du es auf meiner Seite compurem.de herunterladen – du findest es hinter dem Menü Programmierung ::: [Strg] + [Q].
Wenn man mit VBA programmiert und wissen möchte, ob in einem Text ein anderer vorhanden ist, kann man mit den Funktionen Left, Right, Mid oder Instr arbeiten. Oder den Vergleichsoperator Like verwenden. Also beispielsweise
If txtIBAN.Value Like „DE*“ Then …
Die Aufgabe: ich will Namen in Excel prüfen. Ich möchte wissen, ob sie auf intelligente Tabellen verweisen, also einen Aufbau haben, wie beispielsweise:
=tbl_Feiertage![#Alle]
oder:
=tbl_Feiertage[2021]
Also prüfe ich:
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).RefersToLocal Like "=*[*]" Then
MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
End If
Next
Und wundere mich, warum die If-Verzweigung nichts findet.
Okay – noch ein Versuch – ich lasse die letzte eckige Klammer weg und prüfe erneut:
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).RefersToLocal Like "=*[*" Then
MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
End If
Next
Verwundert reibe ich mir die Augen. Was klappt nicht? Die Prüfung
If ThisWorkbook.Names(i).RefersToLocal Like "=*" Then
Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.
Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.
Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.
Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:
Im unteren Teil verwende ich ein kleines VBA-Makro:
Sub BerechneDateDIFF()
Dim intZeile As Integer
Dim intSpalte As Integer
For intZeile = 21 To 30
For intSpalte = 2 To 20
ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
Next intSpalte
Next intZeile
End Sub
Das Ergebnis:
Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.
Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.
Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.
Seit ein paar Tagen heißt es nun „Vom Blatt“
Muss das sein? Ständiges Umbenennen?
Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):
Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:
Das funktioniert auch mit der Funktion SEQUENZ:
Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:
dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …
Ich soll ein Excel-Formular (per VBA) auswerten. Schnell stelle ich fest, dass einige Zellen gesperrt sind. Das ist nicht sehr geschickt, da diese Zellen vom Anwender und von der Anwenderin ausgefüllt werden sollen:
Wie finde ich diese Zellen? Es gibt leider keine Suchoption dafür, so dass alle gesperrten oder nicht gesperrten Zellen markiert werden.
Also anders: mit der Ersetzenfunktion gelingt es: Öffnet man die Optionen, kann man in Excel nach Formaten suchen. Eben: beispielsweise nicht gesperrte Zellen. Es erweist sich geschickter, die offenen Zellen zu finden, als die gesperrten, da alle Zellen in den 1.048.567 x 16.384 Zeilen und Spalten gesperrt sind.
Und diese werden durch eine Hintergrundfarbe ersetzt. So findet man schnell die nicht gesperrten Zellen:
Gestern auf dem Excelstammtisch stellte Volker folgendes Problem bei/mit intelligenten (dynamischen; strukturierten) Tabellen vor.
Wir haben eine Liste, die zu einer intelligenten Tabelle verwandelt wurde:
Die Tabelle heißt tbl_Planeten.
Auf einem zweite Tabellenblatt wird Bezug auf diese Tabelle genommen; genauer: auf jede Spalte:
=WENNFEHLER(tbl_Planeten[@Planet];"")
Erstaunt stellt man fest, dass der erste Planet – Merkur – fehlt. Die Antwort ist simpel: „@“ bezieht sich auf die Informationen der aktuellen Zeile. Da die zweite Tabelle erst ab Zeile zwei beginnt (die erste fängt in der ersten Zeile an), ist der Bezug versetzt. Man muss also bei Tabellen gleich positionieren. Das birgt Gefahren.
Fazit: besser SO nicht Tabellen miteinander verknüpfen. Es gibt bessere Lösungen: PowerQuery sei an dieser Stelle genannt. Oder relative Bezüge.
Danke an Volker für diesen amüsanten und wichtigen Hinweis!
Ich suche jemanden, der fit in power query und power pivot
ist und dem ich ca. eine stunde lang fragen stellen kann.
Hintergrund: Ich habe einen größeren Auftrag, da geht es um mehrere Tools im Excel-Umfeld, da geht es bei einem Tool jetzt erst mal darum, ob power query da was bringen würde.
Ich hatte Schlimmes oder Schwieriges befürchtet. Aber die zentrale Frage war weder schlimm noch schwierig zu beantworten.
Gegeben sei eine Auftragstabelle mit Verkaufsdaten:
Diese Liste wird nach PowerQuery gezogen und dort bearbeitet. Das Ergebnis wird zurückgegeben:
Die erste Frage lautete: Wie kann man Anfang und Ende als Filter in PowerQuery einbauen?
Die Antwort:
Man muss die jeweils zwei Zellen in eine intelligente Tabelle konvertieren.
Man muss diese ebenso nach PowerQuery importieren.,
Dort den Datentyp in Datum ändern.
Und dort ein Drilldown durchführen. Das heißt: die Tabelle in einen Wert, besser: in eine Variable, verwandeln.
Diese Variable hat einen Namen – er kann verwendet werden.
Also so:
Man schaltet einen beliebigen Datumsfilter ein („Zwischen“):
Und ersetzt in M die beiden Werte durch die Variablennamen:
Fertig! Test in Excel:
Und natürlich kam danach die Frage:
ich möchte die berechnung lieber in PowerPivot vornehmen und mit einer Pivottabelle gruppieren und das Meassure verwenden.
Ich habe jetzt in DAX diesen ausdruck, der funktioniert:
Ich sollte doch eine Seite „Outlook-nervt“ eröffnen. In letzter Zeit werde ich häufig mit Outlook-Fragen, Outlook-Problemen und Outlook-Wünschen konfrontiert. Beispielsweise mit folgendem Wunsch:
Sehr geehrter Herr Martin, Sie haben vor geraumer Zeit einen Outlook-Kurs für meine ganze Abteilung abgehalten und weil Sie Spezialist im Outlook sind möchte ich mich mit einer Frage vertrauensvoll an Sie wenden. Ich hoffe das ist in Ordnung für Sie. Ich versuche das Problem zu erklären: Wir haben 2 Kunden die eine ähnliche Email Adresse haben: die eine fängt mit „contoso“ an die andere mit „re-contoso“. Fakt ist, dass wenn man die im Email-Feld „An“ angibt, dass man die leicht verwechseln kann. Genau das ist auch kürzlich passiert und somit hat ein Kunde die Unterlagen vom anderen Kunden erhalten, was sehr problematisch ist. Jetzt meine Frage an Sie: Gibt es eine Möglichkeit, dass man bei diesen 2 Email-Adressen so eine Art Sperre generiert? Wie zum Beispiel durch ein Fenster mit der Frage: sind Sie sicher, dass Sie diese Email an diese Adresse versenden möchten? Es würde mich sehr freuen, wenn Sie sich mit mir in Kontakt setzen würden. Bis dahin wünsche ich Ihnen noch einen schönen Tag. Mit freundlichen Grüßen
Hallo Frau L., mit einer Regel geht so etwas leider nicht – eine Regel kann man nur erstellen für „Mail die an … gesendet wurde“.
Aber mit einem kleinen VBA-Skript geht das:
Fügen Sie in Outlook die Registerkarte „Entwicklertools“ ein:
Wechseln Sie in dort in die Entwicklungsumgebung „Visualbasic“
Doppelklicken Sie im Projektfenster auf ThisOutlooksession:
Wechseln Sie auf der rechten Seite über das Dropdown zu Application:
Es öffnen sich folgende Codezeilen:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
End Sub
Dieses Ereignis überwacht das Senden einer Mail. Dort hinein muss folgender Code, so dass das Ganz dann so aussieht.
Option Compare Text
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error Resume Next
If Item.To Like „contoso“ Then
If MsgBox(„Wollen Sie diese Mail wirklich an “ & Item.To & “ senden?“, vbInformation + vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
End If
End If
End Sub
Versuchen Sie es am besten mit dem Namen einer Kollegin (oder Ihrem Namen). Sie können Groß- und Kleinschreibung ausschalten, indem Sie VOR das Makro den Befehl
Kommen Sie damit klar?
schöne Grüße
René Martin
Wir haben es heute zusammen an ihrem Rechner implementiert. Sie ist sehr glücklich über das Ergebnis.
Wer macht denn So etwas? Warum macht Andreas Thehos SO etwas? Ich weiß es nicht. Aber es ist interessant und amüsant:
Er schreibt in Word einen Text und fügt einen Kommentar ein:
Der Text wird nach Excel kopiert – mit der Option „Ursprüngliche Formatierung beibehalten“:
Der Text des Kommentars wird in eine eigene Zelle eingefügt.
Nun wird ein Zellformat von einer anderen Zelle auf die Zelle mit dem Text übertragen:
Das Ergebnis verblüfft. Oder auch nicht?!
Die Formatierung endet an der Stelle, an der der Kommentar stand.
Das muss ich genauer anschauen!
Ich zerlege den Text mit der Funktion TEIL in seine Bestandteile, beispielsweise mit:
=TEIL($A$1;SPALTE(BD1);1)
Die Funktion wird nach rechts gezogen:
An der Stelle Kommentar|Leerzeichen befinden sich jetzt zwei (!) Zeichen. Mit der Funktion CODE sehe ich es mir genauer an:
Die Funktion CODE liefert die Zahl 32 – klaro: für das Leerzeichen und die Zahl 160!?! Sie liegt zwischen dem Zeichen Ÿ und dem umgekehrten Ausrufezeichen, wie es im Spanischen verwendet wird:¡
Danke Andreas, für diesen interessanten Hinweis. Es bleibt für mich immer noch die Antwort auf die Frage offen – wer macht denn so etwas?
Umgekehrt – ich hatte vor einigen Jahren mal ein langes Gespräch mit dem Verantwortlichen von LibreOffice. Er hat mir gesagt, dass das Werkzeug „Format übertragen“ die Hölle ist (LibreOffice hat sich lange geweigert so etwas zu implementieren, haben aber schließlich dem Druck der Pinsel-Liebhaber aus der Microsoft-Fraktion nachgegeben) – denn – welches Format wird übertragen? Was ist Format? Ist Kommentar auch Format? …
Übrigens, ist jetzt auch zwei Kollegen passiert, die Home & Business 2019 haben, also nicht das Abo-Produkt. Eines Tages kam ein Update, die graphische Benutzeroberfläche, also sämtliche Schaltflächen veränderten sich und auf einmal waren die Microsoft 365-Funktionen drin (also XVERWEIS, XVERGLEIC, etc.).
Dann, zwei bis drei Tage später installierte sich wieder ein Update und alles war wieder weg – hab ich mir also nicht eingebildet. 😉 Diese Geschichte glaubt mir nicht jeder.
Was denkt sich Microsoft wohl dabei? Ist das wie dem Hund
die sprichwörtliche Wurst hinhalten (schau mal was du alles Tolles haben
könntest, wenn du das Abo-Produkt bestellst?) oder einfach ein Bug?
Ich wollte doch nur in Word die Rechtschreibprüfung starten. Ich klicke auf die Schaltfläche „Editor“ und lese:
Um dieses Dokument zu überprüfen, müssen die folgenden Ausnahmen in den Korrekturoptionen deaktiviert sein.
- Rechtschreibfehler nur in diesem Dokument ausblenden
- Grammatikfehler nur in diesem Dokument ausblenden
Möchten Sie s
Hatte da jemand keine Lust mehr? Oder einen Schluckauf? Oder fing die Kaffeepause oder der Feierabend an? Amüsant …
ich „spiele“ z.Zt. mit einer Tabelle (Mappe1), in der Excel partout nicht rechnen will. In der Kopfleiste habe ich die für die entsprechenden Spalten die Formeln und die Zellen-Formatierungen eingegeben. Der Wert in Spalte #30 ist die Basis für alle Berechnungen in der entsprechenden Zeile.
Kopiert habe ich nach
den Anweisungen der EXCEL-Hilfe und anderen Tipps aus dem Internet.
Für Ihre Hilfe wäre ich Ihnen sehr dankbar.
Hallo Herr M.,
beim Öffnen der Datei meldet Excel, dass ein Zirkelbezug vorliegt. Richtig: in der Statuszeile links unten steht es auch:
Denn: in der Zelle N30 steht die Formel
=EL30
in EL30 steht:
=(100%-EK30)/100%
in EK30 steht:
=(100%-EI30)/100%
in EI30 wird wieder Bezug genommen auf N30 mit:
=(EK30-EH30)/N30
Hier schließt sich der Kreis. Das müssen Sie lösen – sonst rechnet Excel nicht mehr!
Ich arbeite seit einer Weile in einer Excelmappe, die ich dann schließe. Öffne eine neue Datei, trage eine 1 ein und wundere mich über die Fehlermeldung:
Sie müssen eine Form auswählen.
Mir dämmert es. Die letzte Aktion in der letzten Datei war das Arbeiten mit Grafik – ich hatte das Werkzeug „Objekte auswählen“ aktiviert und nicht wieder zurückgeschaltet:
In verschiedenen Programmiersprachen gibt es verschiedene Konventionen für die Benennung der Dinge. Beispielsweise die Variablen oder Parameter. Zu den Namenskonventionen gehören beispielsweise die Reddick-Namenskonventionen, in denen eine String oder Long-Variable mit strNachname oder lngZeile benannt wird. Oder die ungarische Notation, in welcher diese variablen stNachname und lZeile genannt würden.
Wer in VBA programmiert stellt schnell fest, dass die Kombinationsfelder, Listenfelder, Register und Multiseiten eine andere Struktur haben als die anderen Steuerelemente auf den Dialogen (Userformen):
Nicht nur, dass sie Null-basiert sind (anders als die übrigen Elemente), die Parameter werden nicht mit „sprechenden“ Namen angezeigt (wie eigentlich üblich), sondern gemäß der dort verwendeten Namenskonvention:
Und so bin ich glatt reingefallen, weil der letzte Parameter nicht IIndex (also mit zwei „ii“) heißt, sondern klein-L-Index … Verwirrend!
ich bekomme es nicht hin, anbei mein Diagramm, es sollte allerdings so aussehen und ich bekomme es einfach nicht hin.
So sieht es bei mir aus:
Wären Sie so freundlich und würden mir helfen.
Danke
Mfg
Hallo Frau Dobner,
ich helfe doch gerne. Für Ihr Problemchen gibt es zwei Lösungen: entweder sie vertauschen Zeile und Spalte:
Allerdings „erkennt“ dann die Liste Spalte 1 als Daten. Diese muss man rausnehmen und den Datenreihen1 und Datenreihen2 als Legendeneinträge zuweisen (die Jahreszahlen)
Besser. Sie schreiben die Daten von oben nach unten:
Schon doof. Excel zeigt viele Dinge an, aber leider nicht die Liste der Pivottabellen. Mourad Louha schlägt ein kleines VBA-Skript vor, um die Liste aller Pivottabellen auszulesen:
Public Sub ListPivotTables() Dim c As Long Dim b As Worksheet Dim o As PivotTable Dim St As Worksheet On Error Resume Next Set b = ThisWorkbook.Worksheets.Add c = c + 1 b.Cells(c, 1).Value = "Name" b.Cells(c, 2).Value = "Quelle" b.Cells(c, 3).Value = "Aktualisierung" b.Cells(c, 4).Value = "Arbeitsblatt" b.Cells(c, 5).Value = "Bereich" b.Cells(c, 6).Value = "MDX" For Each St In ThisWorkbook.Worksheets For Each o In St.PivotTables c = c + 1 b.Cells(c, 1).Value = o.Name b.Cells(c, 2).Value = o.SourceData b.Cells(c, 3).Value = o.RefreshDate b.Cells(c, 4).Value = o.Name b.Cells(c, 5).Value = o.TableRange1.Address b.Cells(c, 6).Value = o.MDX Next Next End Sub
Ich habe es ein klein wenig überarbeitet. Das Ergebnis:
Oder man benennt die Datei mit der Endung ZIP um, entpackt das Archiv und wirft einen Blick in die beiden Ordner pivotCache und pivotTable. Darin verbergen sich sämtliche Informationen zu den Pivottabellen:
PowerPoint-Schulung. Wir erstellen eine Grafik. Dafür verwende ich die Firmenvorlage. Ich zeige dem Teilnehmer, wie man eine Form erstellt. Sie verschwindet. Ich bin erstaunt. Noch einmal: Form auswählen, Rechteck aufziehen – nichts ist sichtbar. Doch: beim Aufziehen sieht man die Form. Dann ist sie unsichtbar. Aber markiert. Also: Konturfarbe wählen. Die Form erscheint. Die Füllung ist transparent und wird ebenfalls sofort mit einer Farbe versehen. Wir beginnen Text einzutragen – die Form „schnurrt“ auf ein Minimum zusammen. Auch hier ist der „Bösewicht“ schnell gefunden: im Aufgabenbereich „Form formatieren“ wurde in den Textoptionen eingestellt, dass sich die „Größe der Form dem Text anpassen“ soll.
Dann klappt es: wir können eine Grafik erstellen und beschriften:
Jedoch: bei weiteren Formen vergesse ich die Grundeinstellungen, übertrage die Formatierung mit dem Pinsel „Format übertragen“ – padautz – die Form wird wieder klein, weil noch kein Text in der Form steht …
Als ich frage, wer diese Vorlage erstellt hat, erhalte ich keine Antwort. Ich bitte darum, sie noch einmal zu überarbeiten …
Ab und zu muss ich meine Dogmen und Glaubenssätze über Bord werfen. Beispielweise die Antwort auf meine Frage in Excel: „Wie findet man in einem geschützten Formular die nicht gesperrten Zellen?“
Bislang zeige ich meinen Teilnehmern und Teilnehmerinnen, dass man mit der [Tabulatortaste] von (offener) Zelle zu (offener) Zelle in einem geschützten Excelformular springen kann – wie im Internet, wie in einem Word-Formular oder einem PDF-Formular.
Weit gefehlt!
Sind mehrere Zellen nach unten (!) verbunden, „springt“ Excel von links nach rechts und von rechts nach links. Aber nicht weiter nach unten!
Eine der gestellten Fragen hat mich amüsiert – ich habe schon einmal in diesem Blog darüber gepostet.
In einer Exceltabelle ist die erste Zeile und die erste Spalte fixiert. Ich erkläre, wie man mit [Strg] + [Pfeil oben] nach „oben“ springen kann. Was ist oben?
Eine Teilnehmerin beschwert sich, dass sie nun nicht die erste Zeile der Datensätze angezeigt bekommt, sondern dass sich die Markierung in der Überschriftszeile befindet, die allerdings immer noch fixiert ist.
Als ich ihr erkläre, dass sie nun in einem zweiten Schritt mit [Pfeil unten] eine Zeile nach unten springen kann und so die ersten Zeilen der Tabelle sieht, ist sie zufrieden.
Eine Sache, die auch bei mir hier (trotz 6GB Arbeitsspeicher) immer wieder kommt, ist folgendes:
Nicht genügend Systemressourcen.
Debuggen kommt dann das hier.. Beim Ausführen einer ganz normalen SQL Anweisung, (zugegeben in einer Rekursion) bleibt er hier stecken..
Auf was muss ich denn da besonders achten?.. Oder könnte man das mit speziellen Funktion abfangen?
Viele Grüße nach München, Bernd
Hi Bernd,
Ich kenne solche Fehler … Nervig!
Versuch mal die Anweisungen rst!dblAnzWdh und
Nz(rst!dblWeightPart, 0) in zwei (Double-)Variablen zu schreiben und dann das
Produkt auszuführen (das Ergebnis wieder in eine dritte Variable), ebenso die
rst!ID.
Warum finde ich in der Liste der Seitengrößen kein Format A0? Ich möchte das Dokument als PDF speichern!
Bei meiner Kollegin habe ich das doch gesehen!
Die Antwort ist schnell gefunden: wird ein „normaler“ Drucker ausgewählt, der kein A0 drucken kann, wird dieser auch nicht vorgeschlagen. Man muss einen PDF-DRUCKER wählen, dann steht A0 zur Verfügung und jetzt kann man auch ein A0-PDF erstellen (speichern oder drucken):
Gestern habe ich eine ganz interessante Outlook-Frage erhalten. Wie siehst du das:
„Bspw
versende ich einen Termin im Outlook an 200 Teilnehmer und erhalte rund 50 Absagen,
soweit so gut. Sende ich jedoch ein Update dieses Termins, so erhalten auch
jene Teilnehmer welche sich abgemeldet haben, wieder meine Info. Ich erhalte
nun Antworten von Menschen welche sich ja schon eingangs bei mir abgemeldet
haben und mir erneut und entnervt mitteilen, dass sie nicht kommen können.
Ich weiss, dass in IBM Lotus Notes, all jene Teilnehmer welche sich einmal für
den Termin abgemeldet hatten, dann auch keine nervigen Updates mehr erhalten.“
Meine Antwort wäre: das geht nicht. Outlook geht davon aus,
dass eine Absage nicht eine Absage zu dem TERMIN ist, sondern zu dem ZEITPUNKT
an dem der Termin stattfindet. Deshalb wird ein Verschieben den Leuten
mitgeteilt als Möglichkeit JETZT teilzunehmen.
Wie siehst du das? Oder weißt du einen Schalter? Ich würde ihr raten die Leute, die abgesagt haben aus der Einladung rauszuwerfen … Dann könnte es aber sein … oder? Oder einfach einen kleinen Kommentartext in die Einladung zu schreiben.
René
Hallo guten Morgen,
Ja, René, es ist genau so, wie Du es sagst.
Man könnte künftig vielleicht eine Umfrage vorschalten, wer überhaupt Lust auf die Veranstaltung hat und dann nur die zum Termin einladen, die Ja gesagt haben. Da passt es dann auch bei Verschiebungen
Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!
In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.
Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then
Klappt wunderbar.
Fast.
Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then
und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.
Ich prüfe:
der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:
AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:
If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then
Ein Bild. Ein sehr großes und scharfes Bild. Eine Architekturzeichnung:
Ich füge es in eine Excel-Arbeitsmappe, speichere sie, öffne sie und sehe:
Unscharf!
Okay – Excel stellt die Option „Bilder in Datei nicht komprimieren“ zur Verfügung. Hätte ich auswählen sollen:
Allerdings: in Visio? Was mache ich in Visio, wo eigentlich Bilder hingehören und herkommen? Dort suche eine solche Einstellung vergebens. Dort wird ab einer bestimmten Dateigröße komprimiert:
Okay – zugegeben – dort kann man die VDSX-Datei als ZIP umbenennen, extrahieren und dann im Ordner „media“ das verkleinerte Bild durch das Original ersetzen:
Witzig. Vorlauter Formeln und Assistenten übersieht man manchmal die Wirklichkeit.
Wir schauen die Zielwertsuche an. Mit der Funktion RMZ (regelmäßige Zahlungen) wird die Annuität berechnet.
Nun soll „zurückgerechnet“ werden. Ich möchte wissen wie hoch der Zinssatz bei einer Rückzahlung von 220 Euro ist. Also Zielwertsuche (sie soll ja auch gezeigt werden:
Sie rechnet und liefert:
-4,92% !?! Minus !?!
Es dauert eine Weile, bis wir verstehen: Das Ergebnis ist korrekt. Man müsste mit negativen Zinsen rechnen, um auf diesen Betrag zu kommen … Doch nicht falsch und auch nicht so unrealistisch.
Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:
IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:
Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …
Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!
Excelschulung. Ich erkläre und zeige (intelligente, dynamische, formatierte, strukturierte) Tabellen:
Ein Teilnehmer behauptet, dass diese Tabellen seine Überschriften löschen würden. Ich bin irritiert. Ich habe eine Weile gebraucht, um zu verstehen, dass die weiße Schriftfarbe, die Excel als Standard einsetzt, nicht sehr clever ist bei einer gelben Hintergrundfarbe …
Natürlich ist die Überschrift noch vorhanden. Nur eben – sehr schlecht lesbar!
Outlookschulung. Es kam eine Frage zur Lesebestätigung. Ich sende mehrere Mails mit Lesebestätigung an verschiedenen Personen.
Einige haben einen grünen Haken, andere nicht !?! Diejenigen mit einem grünen Haken haben beim Öffnen eine Gruppe „Anzeigen“:
Wenn ich auf den „Status“ klicke, sehe ich die Liste der
Mails – ohne Antwort – obwohl ich die Lesebestätigung bejaht habe.
Bei den anderen fehlt diese Gruppe – obwohl eine Lesebestätigung dranhängt.
Auch hier habe ich meine Kollegin gefragt – sie wusste auch keine Antwort. Auch hier muss ich mit einem Schulterzucken verbleiben. Sorry … Wenn ich noch etwas erfahre, melde ich mich wieder. Kennst du die Antwort auf diese Frage / die Lösung für dieses Problem?
Ich hätte schwören können, dass es vorhanden ist. Eine Teilnehmerin in der letzten Schulung wollte das Entspricht-Zeichen ( ≙ ) haben. Das Gleichheitszeichen mit dem Dach darüber. Ich hätte es in der Liste der Zeichen vermutet, die die Schriftart Symbol zur Verfügung stellt. Oder im „normalen“ Zeichensatz.
Ich wurde eines Besseren belehrt. Man findet es beispielsweise in der Arial Unicode:
Ich schaue auf dem Firmenrechner nach. Bei dieser Firma ist die Arial Unicode nicht installiert. Was tun?
Nun – wir finden das Zeichen auf einer Seite im Internet und kopieren es von dort heraus. Und hinterlegen es in der AutoKorrektur. Die Teilnehmerin war glücklich. Und ich sehr erstaunt, dass dieses (wie ich dachte) wichtige Zeichen nicht vorhanden ist. Ich werde mal an die IT schreiben und bitten die Unicode-Schriften nachzuinstallieren. In Word könnte man es in einem Feld nachbauen, aber in Excel, PowerPoint und Outlook?
ich filtere in dem Kunden-Excel die Werte mittels einer
Pivot-Tabelle. Der Filter zeigt aber nur die tatsächlich vorhandenen Werte an.
Jetzt möchte ich im Pivot einen festen Wertefilter
definieren, unabhängig welche aktuellen Werte vorhanden sind:
Beispiel:
Ich möchte immer alle Einträge < 24 Stunden gefiltert
haben
Aktuelle Werteinträge sind 1 und 2 Stunden. Den Filter 24 Stunden kann ich aber erst auswählen, wenn es mindestens einen Eintrag mit 24 Stunden gibt. Lässt sich dies im Pivot einstellen. Ich habe bislang keine Möglichkeit gefunden.
Hallo Herr H.,
der Gedanke der Pivottabelle ist ja, die vorhandenen Werte zu gruppieren und die Zahlen zusammenzufassen (aggregieren, also: summieren, zählen, …) Wenn Sie andere Werte sehen möchten, müssen diese in der Liste stehen (man müsste sie ausblenden).
Hallo Herr Martin,
es gibt im Office 365 Excel den Befehl FILTER, der genau das
macht, was ich benötige. Nur hat mein Kunde leider eine ältere Version. Lässt
sich das in einem älteren Excel mit einem workaround bauen?
Viele Grüße
Hallo Herr H.,
Nein – bitte nicht die Funktion FILTER verwenden – sonst hat
die Firma ein Problem!
Was würde ich tun?
* entweder die Daten dazwischen verstecken (und die Zeilen ausblenden)
* oder die Daten auf einem anderen Blatt sammeln und dort
alle notwendigen Daten einsammeln.
Hum. Sonst? Müsste mal überlegen
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Filter geht beim Kunden nicht, aber ist die Funktion so
gefährlich?
Ich habe mittels Pivot die Daten auf ein anderes Blatt
ausgelagert und nutze das Ergebnis für die Dropdown-Felder.
Ich habe jetzt einen Dummy-Wert eingefügt, damit die Pivots die Auswahlfelder behalten, auch wenn keine Daten auszuwerten sind. Ggf. wäre eine Lösung ganz ohne Pivots zu arbeiten, aber dazu bräuchte ich so etwas wie die Filter-Funktion.
Hallo Herr H.,
nein, nein: FILTER & co sind klasse – Problem: nicht
jeder hat diese Funktionen. Deshalb: bauen wir den Filter doch nach!
Werfen Sie mal einen Blick in meine Liste: In Spalte H befinden sich die sechs Werte. Einer wird in J2 ausgewählt. In Spalte L ermittle ich die Zeilennummer, falls gefunden. In Spalte M sammle ich diese Nummern ein; gruppiere sie also. Mit BEREICH.VERSCHIEBEN baue ich die Liste ab O1 auf (ich hätte auch INDIREKT oder INDEX / VERGLEICH verwenden können).
Ich könnte es auch mit AGGREGAT aufbauen – aber lassen wir das …
Kannst du auch einmal ein Tutorial über diese defekten Tabellen machen und wie man die wegbekommt? Bei meinen Recherchen habe ich bis jetzt nur gefunden das die erstellt werden wenn Excel abstürzt. Die einzige Lösung de in foren geholfen hatte, war eine neue Mappe zu erstellen und alle Daten zu übertragen. Aber das kann doch nicht die Lösung sein oder? Alle Änderungen die vorgenommen werden beziehen sich nur auf „DieseArbeitsmappe“
Andreas weiß keinen Rat. Und ich? Ich hatte mal eine solche korrupte Datei gesehen – ist schon eine Weile her. Und nein – sorry – ich weiß leider auch keinen Rat.
In der letzten Excelschulung haben wir über Listen gesprochen. Beim Sortieren von Daten kann man nach Zeilen und Spalten sortieren. Man findet diese Einstellung in den Sortieroptionen:
Prompt kam die Frage, ob man auch nach Spalten filtern könne.
Zuerst überlegte ich, ob dies überhaupt sinnvoll sei.
Die korrekte Antwort lautet: Nein! man kann den Autofilter nicht neben der ersten Spalte einschalten. Man muss die Liste transponieren:
Dann kann man den Filter für die erste Zeile einschalten, die in der ursprünglichen Tabelle die erste Spalte darstellte:
Und so ergeben sich sicherlich einige Anwendungsbereiche.
Vorgestern in der Outlook-Schulung. Eine Teilnehmerin fragt mich, warum sie ihre Ordner nicht verschieben kann. Damit ein Ordner an erster Stelle stehe, müsse sie einen Unterstrich einfügen. Ich glaube es erst, als ich es sehe und wundere mich sehr.
Wir suchen eine ganze Weile und werden fündig: Schaltet man in Outlook in der Registerkarte „Ordner“ die Option „Alle Ordner von A nach Z anzeigen“ ein, werden sie sortiert, können jetzt aber in ihrer Reihenfolge nicht mehr verschoben werden!
Gestern Excelschulung. Ich zeige, wie man Zahlen benutzerdefiniert formatieren kann. Eine Teilnehmerin meldet sich zu Wort. Wie man Zahlen mit Nullen auffüllen kann. Sie hat „alte“ Personalnummern – diese sollten führende Nullen erhalten. Nun – das Zahlenformat 00000 ist wohl kein Problem:
Dann kam die Frage, wie man die Anzahl der Nullen so festlegen könne, dass die größte Zahl keine führende Null hat, alle anderen sich daran ausrichten.
In den benutzerdefinierten Zahlenformaten kann man leider keine Formel eintragen. Deshalb geht das DA wohl nicht. Aber man kann benutzerdefinierte Zahlenformate in der bedingten Formatierung einsetzen:
Die größte Anzahl der Ziffern (vor dem Komma) kann beispielsweise mit
=MAX(AUFRUNDEN(WENNFEHLER(LOG10(A:A);0);0))
ermittelt werden. Nun kann man überprüfen, ob dieses Ergebnis = 1, dann: Zahlenformat: 0. Ergebnis = 2, dann: Zahlenformat: 00, Ergebnis = 3, dann: Zahlenformat: 000, Ergebnis = 4, dann: Zahlenformat: 0000, und so weiter.
Funktioniert. Ist aber weder schön, noch elegant noch schnell!
habe mir dein Buch mal durchgearbeitet. Respekt, viele richtig spannende Sachen dabei.
Einen Satz
habe ich allerdings nicht verstanden. Unter 4.10 auf Seite 104 sagst du, dass
es nicht möglich sei Hintergrundfarbe, Schriftart und -größe in Notizen
festzulegen.
Sie mal hier…
Hallo Hannes,
stimmt: ich hätte noch das Wörtchen „als Standard“
hinzuschreiben sollen. Du kannst nicht die Standardschrift,
Standard-Hintergrund … von Notizen in Excel festlegen
Im Anhang
findest du eine Tabelle. Was ich möchte ist folgendes:
Ich möchte
wissen, welche Zahlen zwischen der kleinsten und der grössten Zahl alle noch
fehlen. Am liebsten hätte ich alle fehlenden Zahlen in einer Spalte.
Herzliche
Grüsse
Andreas
Hallo Andreas,
ich verstehe nicht ganz – in der Liste befinden sich
keine Zahlen, sondern Texte. Texte der Form CHE-xxx.yyy.zzz
Was heißt in diesem Zusammenhang „die kleinste und
die größte Zahl“?
Vielleicht sollte ich besser „Nummern“
schreiben, das „CHE-“ könnte man auch weglassen, man könnte auch die
Punkte durch tausender Trennzeichen ersetzen, damit es Zahlen werden: 000’000’001 bis 999’999’999
In der Matrix hat es eine Auswahl von möglichen „Nummern“,
eine „Nummer“ ist die Grösste, eine die Kleinste, dazwischen hat es
in der Matrix welche, aber es fehlen auch welche. Ich möchte gerne alle
Nummern, von der kleinsten Nummer bis zu Grössten Nummer, die mir noch fehlen
und so, dass ich Sie in einer Spalte habe und sortieren kann.
Konnte ich mich verständlich ausdrücken?
Herzliche Grüsse Andreas
Hoi Andreas,
kurz nachgeschaut:
die kleinste „Zahl“ in deiner Liste ist
CHE-100.000.058, die größte: CHE-499.992.187. Das heißt: wir müssten fast 500
Millionen Zahlen prüfen.
Ich habe es mal mit den Zahlen zwischen 100.000.000 und
101.000.000 versucht – bei einer Formel (bist du drin? -> Zählenwenn) geht
Excel in die Knie. Nicht auszudenken, wenn du das in einer Spalte
„zusammengefasst“ haben möchtest und das ganze x 500!
Da stürzt Excel ab.
Ich würde es entweder mit PowerQuery lösen oder mit VBA.
ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die
Metadaten rauslesen soll.
Das Auslesen geschieht über Power Query (what else….), aber ich
muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query
Abfragen on the fly erstellen. Letzteres geht problemlos.
Der Ablauf:
Prüfe, ob User die pbix geöffnet hat.
Falls nicht, bitte freundlich darauf aufmerksam machen
Falls nein, Abbruch – falls ja, pbix öffnen.
Bis dahin klappt alles.
Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI
Datei authentifizieren muss, nachdem er ja gesagt hat.
falls er aber den Dialog hier abbricht (…..DAU…….), kommt
eine „schöne“ Meldung:
Nun meine Frage:
Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie
fange ich das ab?
Bin schon voller Zweifel…..
Merci, lieber René für deine Geduld mit mir
Freundliche Grüsse Hans Peter
########################################
die unwissenden erleuchten sich selber
habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René
Hallo Rene, ich benötige BITTE DRINGEND Deine Hilfe!!! Ich habe hier irgendetwas abgeschossen…… Kann eine ausgeschnitte Zeile nicht mehr einfügen…. DANKE
es geht es geht…… DANKE DANKE DANK!!!!!!!!!!! Was war es???
vor der Antwort eine Frage: klick mal bitte in Excel auf Datei / Konto. hast du auch Version 2102?
Hallo JA, die habe ich auch. wobei jetzt gerade ein update läuft…. 🙁 hoffe es geht dann noch alles…. habe jetzt: Version 2102 Build 13801.20294 ABER ES LÄUFT!!!
Eben, Jörg:
Microsoft macht Updates. ich vermute stark, dass Microsoft etwas kaputt gemacht
hat.
In eurem Makro
verwendet ihr ein uraltes Tool, um Masken (Dialoge) anzuzeigen. Aus Excel 4.0.
Da lief ungefähr in der Zeit, als die Dinosaurier ausstarben. Ich habe dich ja
im Dezember mal gefragt, ob man (ich) nicht mal den ganzen alten Schrott, der
in euren Makros steckt, erneuern soll. Und ich fürchte: heute ist es passiert –
da wird etwas aus den 90er Jahren nicht mehr unterstützt …
Ich habe dir im
Dezember einen Screenshot von der Maske geschickt. Deshalb wusste ich, dass sie
damals bei mir noch lief. Heute bei mir auch nicht mehr.
ich habe die Maske
mit den aktuellen Bordmitteln nachgebaut – deshalb sieht sie ein klein wenig
anders aus … beispielsweise der Titel in der Titelzeile.
Ich werde das mal
in Foren posten und fragen, ob jemand eine ältere Version hat – ob es da noch
läuft …
Bevor dein Chef
dich tötet, flehe um Gnade und sag ihm einen schönen Gruß von mir, dass
Microsoft Sachen kaputt macht … nicht nur du …
Liebe Grüße
Rene
####
Jörg war glücklich. Ich habe ihm vorgeschlagen, das Makro zu überarbeiten. Habe ihm ein Angebot gemacht. Er hat sich seitdem nicht mehr gemeldet.
Vor zwei Tagen habe ich eine sehr nette, sympathische Gruppe von „Ich will da rauf! – Klettern für Menschen mit und ohne Behinderung“ in Excel unterrichtet. Wer schauen möchte:
Als ich Notizen (früher: Kommentare) zeigte, kam die Frage, wie man denn diese roten Ecken ausblenden könne.
Ich habe gestutzt. Meine Gegenfrage: „Warum wollen Sie das?“ wurde mit einem „ich möchte nicht, dass andere sofort meine Kommentare sehen“ beantwortet.
Nun gut.
Ich gestehe: ich habe ein bisschen suchen müssen:
Die Antwort: in den Excel-Optionen in Erweitert findet sich in der Gruppe „Anzeige“ die Option „Keine Kommentare, Hinweise oder Indikatoren“.
Zugegeben: Über Überprüfen / Notizen / Alle Notizen anzeigen könnte man sie wieder einblenden. Ebenso über den Aufgabenbereich Start / Bearbeiten / Suchen und Auswählen / Auswahlbereich:
Erstaunlich! Auf dem letzten Excelstammtisch, den Frank Arendt-Theilen organisiert hat, hat er angemerkt, dass die PowerQuery-Funktion Date.WeekOfYear, die man über Spalte hinzufügen / Datum / Woche / Woche des Jahres nach dem US-amerikanischen Modell rechnet. Zwar verfügt Excel seit vielen Versionen über die Funktion ISOKALENDERWOCHE, welche die KW korrekt nach ISO 8601 berechnet. Auch Outlook unterscheidet bei den Kalenderwochen zwischen USA und Europa. Jedoch nicht PowerQuery. „Haben die das vergessen?“, fragt Frank. Also muss man diese Funktion in PQ nachbauen …
Ein großes Dankeschön an Frank Arendt-Theilen, der gestern den Excelstammtisch organisiert hat. Es war wieder ein toller, informativer, spannender und erfrischender Abend. Auch ein Dankeschön an Sven Amrhein für seinen Vortrag über PowerQuey, Excel und SharePoint. Bemerkenswert ist sein Hinweis zu der Fehlermeldung.
Eine Ausnahme vom Typ „Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException“ wurde ausgelöst. Der Mashup-Fehler tritt bei der Datenabfrage von einer Excel-Datei auf eine zweite Excel-Datei auf, die dann auf dem SharePoint liegt.
Man muss das Dokument entweder auschecken und wieder einchecken oder – es gibt eine bessere Lösung: Unter Datei / Informationen befindet sich ein Feld für den Manager. Dort müssen alle Anwenderinnen und Anwender eingetragen werden, die mit dieser Datei arbeiten. Dann kann reibungslos und ohne Fehlermeldung damit gearbeitet werden.
Hi René, Danke für die Präsentation. Was mich kurz interessieren würde: Wenn ich z. B. das Währungssymbol von Ungarn möchte, scrolle ich mir einen Wolf, bis ich es gefunden habe. Geht das schneller? Wie lange brauchst du dafür? Tipp: Ungarn liegt zwischen Kamerun und Haiti. Oder: Polen zwischen Lateinamerika und Mazedonien. Klar, einmal gefunden, kann ich das Format einfach übertragen. Aber hin und wieder kommen so Währungs-Exoten und da ist die Auswahl sehr mühsam. Danke und dir einen schönen Abend! Christa
Hi Christa, interessante Frage. Stimmt: wenn du „H“ drückst, springt Excel zu HUF – also zu den Texten im ISO-Code – nicht zu den Symbolen. Tipp: in meinem Skript (hast du?) und auch im Internet gibt es Listen, wie man die Währungssymbole im ASCII-Code eingeben kann. Vielleicht schneller als die Sucherei. Die Ländersortierung ist immer merkwürdig. Meistens liegt eine englischsprachige Tabelle dahinter. Mich würde ja auch interessieren, ob es eine Liste der Symbole gibt. Steht das Excel? Ich mach mich mal auf die Suche… LG :: Rene
Hi René, oh cool. Vielen Dank. Ja, ASCII hatte ich noch in der Ausbildung zur Europasekretärin. Und ich bin Baujahr 78 wohlgemerkt. Dein Skript habe ich gestern heruntergeladen. Danke!!! Ja, das würde mich interessieren, wenn du so eine Liste gefunden hast. Kein Stress
Hi Christa, ich habe mal ein bisschen probiert. Der Makrorekorder hilft nicht weiter, weil er die Zeichen nicht richtig codiert. Also: VBA scheidet aus. Ich habe mal die fast 300 Symbole angeklickt – die Datei gespeichert, in .ZIP umbenannt und aus dem XML-Archiv die Währungssymbole herausgeholt. Auch nicht ganz befriedigend. Immerhin: ich habe entdeckt, dass es doch ein BTC-Symbol/Zeichen für Bitcoin gibt … irgendwo dazwischen. Wie man allerdings die angezeigten Texte der Combobox herausbekommt … keine Ahnung! Ich schick dir mal die Datei LG :: Rene
Übrigens: nach 200 Zahlenformaten hört Excel auf. Ich habe zwei Dateien erstellen müssen.
Gerne können Sie die Liste von meiner Seite herunterladen:
Letzte Woche habe ich einen Vortrag auf dem Londoner Excel-meetup zu (benutzerdefinierten) Zahlenformaten in Excel gehalten. Dabei habe ich auch gezeigt, dass man mit dem Zahlenformat
[=1]0 „Motorrad“;0 „Motorräder“
die Zahl 1 anderes formatieren kann als die übrigen:
Johannes Sandkamp hat mich darauf aufmerksam gemacht, dass man die Zahlen auch bündig ausrichten kann. Der Unterstrich hilft dabei. Oder genauer: der Unterstrich gefolgt von einem Buchstaben. Also so:
[=1]0 „Motorrad“_e_r;0 „Motorräder“
Klappt. So sieht das Ergebnis aus:
Übrigens: wer den Vortrag ansehen möchte – man findet ihn unter:
Vorgestern habe ich einen Vortrag über Zahlenformate in Excel auf dem Excel-meetup in London gehalten. Danach kam die Frage nach (ost-)arabischen Ziffern, beziehungsweise Datumsangaben. Ein Teilnehmer erzählte, wie schwierig das Umrechnen der verschiedenen Kalender ist. Ich probiere es aus:
Ich schalte die Oberfläche auf Persisch. Ich hätte auch Arabisch nehmen können. Ich trage die Zahl 1 ein, ziehe mit gedrückter [Strg]-Taste herunter. Klappt. Ich hole aus der Zeichentabelle die arabisch-indische Ziffer 1, und ziehe mit [Strg] nach unten. Klappt auch:
Amüsanterweise werden die Zahlen in „unserer“ arabischen Schreibweise in der Bearbeitungsleiste angezeigt.
Übrigens: ein Umstellen der Sprache auf Persisch (Farsi):
ermöglicht leider nicht die Eingabe der arabisch-indischen Ziffern:
Deshalb muss die Zeichentabelle herhalten:
Ich drücke [Strg] + [;] für das heutige Datum. Runterziehen – kein Problem. Ein Blick in das Kombinationsfeld Zahlenformat zeigt: das heutige Datum (12.03.2021) würde in das Datum 22.12.1399, das im Iran das aktuelle Datum ist, umgerechnet werden:
Ein Klick auf die Schaltfläche bestätigt das – nicht gerade vertrauenserweckend, wenn in der Bearbeitungsleiste ein völlig anderes Datum steht als in der Zelle:
Und die ostarabischen Ziffern? Ich wechsle die Datumssprache auf Farsi und finde dort mehrere Schreibweisen für das Datum:
Oder so?
Nicht gerade einfacher macht die Tatsache, dass wir dort noch einen dritten Kalender zur Verfügung haben: den Hijri-Kalender, den islamischen Kalender. Dort schreiben wir heute den 29.07.1442. Uff!
Übrigens: wer sich mit Arabisch und/oder persisch auskennt, stellt fest, dass die persische Ziffer für 4, 5 und 6 verwendet werden: ۴۵۶, nicht die arabische Ziffern: ٤٥٦
Ein Blick auf die Tabelle offenbart die Schwierigkeiten, die Microsoft zu bewältigen hat und auch Anwender und Anwenderinnen, die von einem Kalender in einen anderen umrechnen müssen:
Also wieder ganz schnell zurück zu Deutsch / Deutschland:
Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:
und ein Dankeschön an Faraz Shaikh für seinen Hinweis zu seinem Video, indem er erklärt, wie man ein Datum aus dem Hijri-Kalender in ein Datum des gregorianischen Kalenders konvertiert:
Vorgestern habe ich auf dem Excel-Meetup einen Vortrag über Zahlenformate in Excel gehalten. Nach dem Vortrag schreibt Tanja:
Hallo René
Danke für
deinen Vortrag. War ein sehr guter Überblick.
Wieso hast
du das Problem mit den Monaten über Hilfsspalten und nicht über die Gruppierung
des Datums gemacht?
Dann wäre
es ja ganz einfach die fehlenden Monate anzuzeigen.
Sonnige Grüsse aus der Schweiz
Hallo Tanja,
ich freue mich über Mails, die beginnen mit „warum hast du
nicht …?“ Es gibt immer drei Möglichkeiten:
* entweder ich habe etwas übersehen – und es gibt wirklich
elegantere Lösungen
* oder es gibt mehrere Lösungen; ich habe mich für die eine
entschieden, weil …
* oder meine Lösung ist bewusst gewählt, weil …
Ich habe gestern Abend noch einmal nachgedacht:
[und dann folgt eine lange Erklärung, warum ich es nicht so gemacht habe, wie sie es vorschlägt. Das soll an dieser Stelle nicht wiederholt werden.]
Liebe Grüße aus dem verregneten München
René
PS: Beim Durchschauen habe ich gerade bemerkt, dass die Option „Elemente ohne Daten anzeigen“ nicht aktivierbar ist, wenn das Datenmodell eingeschaltet ist. Wusste ich nicht …
Hallo Rene
Danke für deine Erläuterungen. […]
Ich kann mir vorstellen, warum das nicht aktiv sein kann. Beim Zusammenspiel mit dem Datenmodell kann ich ja auch nicht in der Pivot-Tabelle nach einem Datum gruppieren. Wenn ich es sauber aufbaue, brauche ich dazu im Datenmodell ja eine Kalendertabelle und dort kann das Problem mit fehlenden Jahren oder Monaten gar nicht auftauchen, da eine Kalendertabelle ja immer den kompletten «Datumsbereich» enthalten muss.
Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:
Gestern auf Excel-meetup in London habe ich einen Vortrag über Zahlenformate in Excel gehalten. Anschließend kam folgende interessante Frage:
In einer Zelle steht =12/24. Das Ergebnis soll allerdings als Bruch dargestellt werden. Leider weigert sich bei der Zellformatierung Excel, ihn mit den ursprünglichen Werten 12 und 24 darzustellen:
Ich fürchte, das wird auch nicht funktionieren. Der Wert der Berechnung beträgt 0,5. Wie soll diese Ergebniszahl wissen aus welchen Werten sie entstanden sind. Natürlich kann mit FORMELTEXT die Funktion (und damit die Werte) anzeigen lassen – jedoch: DAMIT kann nicht weitergerechnet werden.
Übrigens: wer meinen gestrigen Vortrag ansehen möchte – man findet ihn unter:
ich dachte,
ich meld mich mal mit einer kleinen Excel-Anekdote (und einer kleinen Frage).
Wahrscheinlich ein alter Hut für dich, ich fands aber ganz witzig. 😉
Zuletzt kam
ein Kollege zu mir, er hat ne Excel-Frage. Er hat da eine Datei geschickt
bekommen und wenn er da irgendwo in eine Excel-Zelle ne Zahl schreibt und auf
„Währung“ beim Zahlenformat (das € Deutsch natürlich) klickt…dann
steht da D-Mark.
Hatte ich so
auch noch nicht gehört. Also schauen wir in unter „Zellen
formatieren“. Da war ein benutzerdefiniertes Zahlenformat eingestellt.
Kann es jetzt leider nicht 1:1 wiedergeben, da ich die Datei selbst nicht habe,
aber es müsste das hier gewesen sein:
_-* #.##0
€_-;-* #.##0 €_-;_-* „-“ €_-;_-@_-
Nur anstelle
das €-Zeichens stand „DM“. Wie kommt das da rein?
Ich schaue auf
das Erstell-Datum der Datei…26.09.2001.
Auch gut, wenn
eine Datei seit fast 20 Jahren in einem Unternehmen hin und her geschickt wird.
😉
So, jetzt zu
meiner kleinen Frage. Die Einstellung Berechnungsoptionen > Manuell in der
Registerkarte „Formeln“ ist, wenn ich mich nicht sehr irre, eine
Einstellung, die alleine auf die jeweilige Arbeitsmappe beschränkt ist.
Jetzt bin ich
einigen Leuten begegnet, die steif und fest behaupten, dass diese Einstellung
„rüberwandert“, wenn man mehrere Dateien offen hat.
Also
beispielsweise bekommst du per E-Mail eine Datei mit ausgeschalteter
Berechnung, öffnest diese, hast aber noch zwei andere Dateien offen. Dann soll
die Einstellung auf die zuvor geöffneten Dateien rüberwandern.
Ich konnte
dieses Phänomen nie reproduzieren. Auch nicht, wenn ich aus der Mappe mit
ausgeschalteter Berechnung heraus (z.B. mit Strg + n) eine neue erzeuge.
Hast du schon
mal davon gehört?
Wäre es
theoretisch möglich, dass man auf VBA-Ebene bei allen geöffneten Mappen die
Berechnung ausschaltet, wenn ich die Datei öffne und Makros aktiviere? (Bei den
Leute, die mir das berichtet haben, halte ich es eher für unwahrscheinlich,
dass die xlsm-Dateien hin- und hergeschickt haben)
Danke dir und
viele Grüße,
Dominic
Hallo Dominic,
ich kann es nicht
ganz nachvollziehen. Wenn du in der Excelmappe, die in der Zeit erstellt wurden
als es noch keinen Euro gab, auf das Buchhaltungszahlenformatsymbol klickst,
wird die Zelle als DM formatiert:
Wenn man das
Tabellenblatt in eine neue Arbeitsmappe kopiert, ebenso.
Nicht jedoch, wenn
ich einige Zellen in eine neue Datei kopiere; auch nicht, wenn ich das Blatt in
eine vorhandene Mappe kopiere.
Ich wüsste nicht,
wie dieses Zahlenformat in eine andere Datei „wandern“ kann … ich glaube diesen
Menschen nicht, die so etwas behaupten …
Liebe Grüße Rene
*)PS: Danke für die hübsche Headline – sie wird den heutigen Tag überschreiben.
da Sie mir schon mal bei dem Rauten Problem sehr geholfen haben,
hoffe ich nun das Sie mir bei dem folgenden Problem auch helfen können.
Ich kann in meinem Kalender aus einem Dropdown-Menü Tage Markieren an denen ich „im Urlaub oder Krank“ bin Auswählen. Nun möchte ich aber das nach der Auswahl der Wert 1 für einen Tag in einer anderen Zelle und auf einem andere Blatt angezeigt, und dann auch zusammen gezählt wird. Habe einen Teil dieser Funktion auch mit einer wenn Formel zb. =Wenn(H6“Krank“;“1“;““) bzw. =Wenn(B6“Urlaub“;“1“;““) hinbekommen, die Zahl 1 steht dann in der gewünschten Zelle nur wenn ich weitere Tage mit „Urlaub oder Krank „ markiere, sollten diese in der Zelle dann auch Aufaddiert werden und das geht nicht. Wie kann ich das hinbekommen, können Sie mir da vielleicht auch weiter helfen? Mit freundlichen Grüßen
Hallo Herr B.,
dafür gibt es zwei
Lösungsansätze:
Entweder sie
ermitteln in einer Spalte jeweils, ob in der Kalenderspalte der Text „krank“
steht:
=WENN(B2=“krank“;1;0)
oder in einer
anderen Spalte, ob dort „Urlaub“ steht:
=WENN(B2=“Urlaub“;1;0)
Unter den Spalten
ziehen Sie nun die Summe.
Oder Sie berechnen
die Gesamtanzahl der „kranken“ Zellen mit
Für den
damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene
Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es
noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.
Haben Sie dazu eine Idee?
Hallo Herr S.,
im Ordner _rels befindet sich eine XML-Datei mit Namen .rels. In ihr muss eingetragen werden, dass im Ordner ribbon\customUI die Datei customUI1.xml mit der Ribbon-Definition liegt, beispielsweise so:
Zugegeben: Ein bisschen nervös bin ich schon! Alan Murray hat mich eingeladen am Dienstag, dem 09. März um 19 Uhr (MEZ) auf seinem Excel-meetup einen Vortrag zu halten. 175 Personen haben sich bereits angemeldet – Uff – vor so einem großen Publikum habe ich noch nie gesprochen!
Ich habe mir das Thema „Zahlenformate“ ausgesucht – gefühlte 100.000 Gimmicks rund um die Zahlenformate, die Excel bereithält. Beispielsweise die Unterschiede zwischen Währung und Buchhaltung / Currency and Accounting.
In einer großen Tabelle (~17.000 Zeilen) muss ich ein
einer Spalte eine Rechtschreibkontrolle durchführen. Hier scheinen „Alle
ignorieren“ und „Immer ändern“ nicht zu funktionieren – wenn ich
Fehler mit diesen Schaltflächen bearbeite (um z. B. ein bestimmtes Wort aus der
Schweizer Rechtschreibung in die deutsche Standard-Rechtschreibung
umzuwandeln), werden sie mir bei ihrem nächsten Vorkommen wieder angezeigt,
statt (wie erwartet) einmal komplett durch die Spalte zu gehen und alle
Vorkommnisse dieses Fehlers zu beheben. Dies bremst mich natürlich erheblich
aus. Gibt es einen Trick, damit die Excel-Rechtschreibkontrolle so effektiv
funktioniert wie in Word, oder muss ich mit diesem Problem leben? Und wurde das
Problem eventuell in Excel 2019 behoben?
Ich danke für eure Antworten!
Hallo Herr P.,
Ich habe es gerade bei einer Liste mit 300.000 Zeilen probiert – in meinem Excel in Microsoft 365 läuft [alles ignorieren] prima durch: der Fehler „Huerth“ wird in Zeile 299.991 gefunden, in der übernächsten Zeile übergangen.
Fehler wird gefunden – „Alle ignorieren“Dann wird der Fehler nicht mehr gefunden
Aber ich kann die Frage gerne mal posten – vielleicht hat jemand ein ähnliches Problem.
Liebe Grüße
René Martin
Hallo Herr Martin,
vielen Dank für Ihre Rückmeldung. Es könnte wirklich ein Problem meiner „älteren“ Version von MS Office sein.
In jedem Fall freue ich mich schon auf Ihre Rückmeldung.
Liebe Grüße
Liebe Leserin, lieber Leser,
gerne dürfen Sie einen Kommentar abgeben, wenn SIE dieses Problem in Excel haben / kennen. Wir würden uns freuen.
Und: Die Namensliste, die Sie oben sehen, habe ich mit dem fakename-Generator erzeugt:
nachfolgender Code läuft fehlerfrei unter
Win 7 + Word 2010
Win 7 + Word 2013
Win 10 + Word 2019
Win 10 + Word 365
nur nicht unter Win 10 + Word 2016.
Die rot markierte Zeile ist mein Problemkind.
Vielleicht hast Du noch eine Idee.
Sub TopAktuell_Einfügen()
Application.ScreenUpdating = False
DocPath = "O:\Topaktuell\"
ChDrive ("O:\")
ChDir ("O:\Topaktuell\")
Dim datei As String
Selection.EndKey Unit:=wdStory
For dokumente = 1 To 30
If Dir(DocPath & dokumente & "_1.docx") = "" Then Exit For
For seiten = 1 To 20
datei = dokumente & "_" & seiten & ".docx"
If Dir(DocPath + datei) = "" Then Exit For
Selection.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=datei
Next
Next
Application.ScreenUpdating = True
End Sub
Danke.
LG Traudl
Hi Traudl,
Seit über 20 Jahren schreibe ich VBA-Code. Dabei ist mir aufgefallen, dass sich einige wenige Objekte verändert haben. Ich habe mal vor vielen Jahren ein Makro geschrieben, das Text in eine Word-Tabelle geschrieben hat und den Text mit einem Hyperlink auf ein Word-Dokument versehen hat. Eine der Parameter beim Befehl Hyperlinks.Add wurde geändert.
Deshalb sage ich nicht, dass das nicht sein kann.
ABER! Bitte, bitte, bitte: adressiere doch sauber. Ändere seinen Code in: Dim wdBereich As Range
Set wdBereich = ActiveDocument.Range(Start:=ActiveDocument.Range.End - 1)
(Ende des Dokuments)
Und dort: wdBereich.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _ FileName:=datei
Es kann natürlich sein, dass es trotzdem nicht unterstützt ist – aber eher unwahrscheinlich als das „Hoppeln“ an Ende: Selection.EndKey Unit:=wdStory Raus damit!
Und: wenn du gerade dabei bist: ChDrive ("O:\") ChDir ("O:\Topaktuell\")
Raus damit!
Und: deklariere die Variablen: Dim docpath As String Dim dokumente As Integer Dim seiten As Integer Dim datei as String
Und schließlich: Warum fügst du ein Word-Dokument in ein anderes ein? Ich würde es öffnen, an eine Objektvariable übergeben, den Inhalt (brauchst du die Formate?) entweder in einer Stringvariable speichern oder kopieren und ins Dokument einfügen.
Hilft das?
Liebe Grüße
Rene
Ich danke Dir ganz, ganz herzlich
für Deine Vorschläge, lieber René.
Den Code hatte ich 2017 erstellt und
war froh, dass er läuft. Er ist eine Sub aus einem unfangreichen Makro. Ich bin
allerdings keine Programmiererin. Dazu benutze ich VBA viel zu wenig.
Ich war irritiert. Ich erhalte eine Datei, öffne sie und bekomme beim Speichern eine mir bislang unbekannt Meldung:
Vorsicht: Teile Ihres Dokuments enthalten möglicherweise personenbezogene Informationen, die von der Dokumentprüfung nicht entfernt werden können.
Ich bin irritiert.
Ich suche.
Ich finde:
In der Makrosicherheit (Optionen / Dokumentschutzoptionen) wurde für diese Datei die Option „Beim Speichern personenbezogene Daten aus Dateieigenschaften entfernen“.
ich zweifle an meinem Verstand – ich kann den Fehler in
der Differenzrechnung Zeile 38 nicht
entdecken, obwohl ich alle Formatierungen und Eingaben mehrfach gecheckt habe.
Der Fehler tritt nur in der Zelle i38 auf, die Formel habe ich mehrfach neu
eingegeben und Zeilen und Zellen gelöscht usw. der Fehler bleibt – HILFE!
Ich bin froh, dass es nur eine private Tabelle ist und
nichts Wichtiges, aber so was ist doch nicht zu ertragen…
Übrigens: Ihre Sprüche sind grandios! Wenigstens konnte
ich mehrfach laut lachen!
Mit freundlichen Grüßen Ulrike
Hallo Frau H.,
Danke für das Lob der Sprüche – manchmal befürchte ich,
dass einige nur auf meine Seite wegen der dummen Sprüche klicken. Die sollen
doch mit mir über Excel diskutieren! *lach*
Ich verstehe Ihr Problem nicht:
130,45- 150,31 = 19,86
SO haben Sie es in den anderen Zeilen auch gerechnet.
Wer von uns beiden „denkt falsch“?
Liebe Grüße
Rene Martin
Guten Tag Herr Martin,
erstmal danke für die schnelle Reaktion, die ich zuerst nicht verstanden habe. Beim erneuten Öffnen der gesendeten Mail konnte ich sehen, dass die Zeilen, um die es geht, darin nicht zu sehen sind, obwohl ich das ganze Blatt schicken wollte. Das liegt evtl. an der Druck-einstellung. Ich schicke die Mappe nochmal und stelle fest, dass es Zeile 39 ist, wo der Fehler auftritt (ich hab das mit der Nummerierung der Positionen verwechselt! – war ja auch schon etwas Gaga nach dem vielen rumprobieren) Die Formel heißt also: <=H39-G39> und müsste = ergeben, tut sie aber nicht.
Also noch mal meine Bitte, den Fehler zu identifizieren.
Liebe Grüße Ulrike
Hallo Frau H.,
Ich würde gerne helfen, aber Zeile 39 ist leer!
Liebe Grüße
Rene Martin
Dann bitte bei Zeile 40 nachschauen, folgende Beträge: 129,74 € – 61,67 € ergibt nicht -67,80€ sondern – 68,07€! Ulrike
Da ist ein Zahlendreher in Ihrem Kopf oder ihrer Wahrnehmung, liebe Frau H.
In der Zelle G40 steht der Wert 129,47 und nicht 129,74:
Vier – sieben – NICHT: sieben – vier!
Liebe Grüße
Rene Martin
Oh mein Gott— gut, dass ich niemand anders gefragt habe –
es ist zu peinlich!
Ganz lieben Dank für die Mühe! Ich werde die Seite
weiterempfehlen – nicht nur wegen der Sprüche!
ich bin durch Zufall auf Ihren Blog aufmerksam geworden.
Leider hat Excel immer wieder merkwürdige Verhaltensweisen.
Warum auch immer.
Ich habe auf einem Rechner ein Problem mit der Funktion Daten
„Abrufen und transformieren“
Ich kann auf dem PC keinerlei Daten über diese Funktion aufrufen.
Keine CSV noch eine Tabelle aus einem SQL Server.
Ich bekomme immer wieder die Fehlermeldung, dass die
Initialisierung der Daten fehlgeschlagen sei.
Lade ich die CSV auf einem anderen Rechner ein, funktioniert dies
Problemlos.
Gleiches gilt bei der SQL Abfrage.
Haben Sie noch eine Idee wo ich das Problem suchen kann?
In der PowerQuery kann ich die Daten sauber sehen.
Nur das Einfügen der Daten aus der Verbindung in das Tabellenblatt
funktioniert nicht.
Vielen Dank für Ihre Zeit. Mit besten Grüßen,
####
Ich habe es mir gerade angesehen: Excelversion 2016.
Ich erstelle eine intelligente Tabelle, ziehe sie in PQ, klappt; ich bearbeite, ich tue, ich mache, M-Code ist vorhanden – klappt.
Ich lade sie als
Verbindung. Klappt.
Aber sobald ich die Daten als Tabelle „sehen“ / „haben“ möchte – knallt es:
Kennt jemand diesen Fehler?
Initialisierung der Datenquelle schlug fehl. Überprüfen Sie den Datenbankserver oder kontaktieren Sie Ihren Datenbankadministrator. Vergewissern Sie sich, dass die externe Datenbank verfügbar ist, und wiederholen Sie den Vorgang. Sofern diese Nachricht wieder erscheint, erstellen Sie eine neue Datenbankquelle um mit der Datenbank Verbindung aufzunehmen.
Irgendwie doof. Ich habe eine Tabelle, in der sich mehrere Kommentare (Notizen) befinden.
Nun lautet die Anweisung, dass ich diese Kommentare in eine eigene Spalte schreiben soll. Stellt man im Dialog „Seite einrichten“ ein, dass Kommentare am Ende der Tabelle erscheinen, kann man das im Drucken-Dialog sehen:
Au, prima, denke ich – und speichere die Datei als PDF. Was passiert?
Die Kommentare sind NICHT im PDF!
Anders jedoch, wenn ich einen PDF-Drucker installiert habe – dann kann ich die Datei drucken und Kommentare erscheinen auf dem letzten Blatt.
Warum mache ich das? Nun – das PDF kann in Word geöffnet werden; die Kommentare entnommen und weiter verarbeitet werden.
Und was mache ich, wenn ich keinen PDF-Drucker habe?
Die Antwort: ich kann die Datei mit der Endung ZIP umbenennen, entzippen und die Datei comments1.xml aus dem Ordner xl öffnen. Dort finden sich auch die Kommentartexte, die ich leicht entnehmen kann:
Eine Weile habe ich gebraucht, bis ich es verstanden habe. Excel behauptet, dass auf diesem Tabellenblatt eine Verknüpfung zu einer anderen Datei liege.
„Da liegt nix“, denke ich: auf dem Blatt befindet sich nur ein Button, der ein Makro aufruft:
Nach einer Weile dämmert es mir. Ich kopiere regelmäßig den Bereich von anderen Dateien hier auf dieses Blatt. Und richtig: damit auch die Schaltflächen. Und mit ihnen die Verknüpfungen auf andere Dateien. *gggrrrr*
In einem „unserer“ Excel-Stammtische hast Du uns ein Besispiel gezeigt, wie Du durch ein Klick auf ein Bild „Inhalte aktivieren“ bestätigen kannst (d.h. der Benutzer muss nicht oben auf den orangefarbende Balken klicken , um die Makros der Arbeitsmappe zu aktivieren…).
Leider
musste ich feststellen, dass meine Kollegen gerne die
Aufforderung „Inhalte aktivieren“ übersehen und so meine
Arbeitsmappen mit Makros nicht aktiviert werden. Um das ersichtlich zu machen
bzw. zu erzwingen, würde ich gerne Deine Lösung einbauen.
Kannst
Du mir dazu bitte Dein Beispiel zukommen lassen?
Das
wäre großartig!
Vielen
Dank & Grüße
Sebastian
so!
Ich habe ein Bild auf das Blatt gelegt, das ich beim Öffnen
per Makro unsichtbar mache.
Werden die Makros nicht aktiviert, bleibt das Bild sichtbar.
Die Erklärung: Manchmal beschweren sich Anwender, dass bestimmte Befehle deaktiviert sind. Es wäre praktisch ein Makro zu haben, das meldet, dass die Makros nicht aktiviert wurden. Dies geht natürlich nicht. Um sicherzustellen, dass die Makros aktiviert wurden, kann in ein Projekt ein Bild eingefügt werden.
Werden die Makros nun aktiviert, wird das Bild gelöscht.
Man kann Shapes einen Namen geben oder mit einer Schleife
alle Shapes / Bilder durchlaufen und entweder löschen oder Unsichtbar machen
(Visible = False)
Das Bild wird beim Schließen der Arbeitsmappe wieder
eingeblendet:
Private Sub
Workbook_BeforeClose(Cancel As Boolean)
Ich war von Deiner Lösung damals so begeistert, dass die hängen geblieben ist.
Da ich allerdings nicht mit
(externen) Kunden arbeite, hatte ich keinen Anwendungsfall – bis ich letzte
Woche erfahren durfte, dass meine Kollegen gerne den Balken übersehen und so
nicht meine Datei funktioniert.
Ich werde Deine Lösung am Montag
einbauen- das Bild wird selbstverständlich geändert.
Letzte Woche hat Christian Gröblacher auf unserem Excelstammtisch ein Referat über Sharepoint-Listen, Power Apps, Power Automate, Power Query und Power Pivot gezeigt. Am Ende kamen Exceldaten raus. Sehr beeindruckend. Das soll an dieser Stelle nicht wiederholt werden. Sondern die letzte Folie seiner Präsentation, die mich zum Schmunzeln brachte:
Vor allem das gequetschte Excel lässt Interpretationen offen: haben wir es im Griff? Müssen wir das grüne Teilchen manchmal würgen? Kann man es richtig handeln? Schaut es wirklich so blöde aus der Wäsche? Oder ist es einfach unser aller Begleiter – manchmal zur Freude, manchmal zum Leid?
Danke Christian, dass ich die Folie hier zeigen darf. Und hier noch einmal Excel – gaaaaaaaaanz groß!
Ich erstelle für einen Kunden in Excel mit VBA ein mächtiges Eingabeformular. Schnell sind wir uns einig darüber, der der Anwender und die Anwenderin nicht mit dem Befehl Suchen-Ersetzen Texte auf einem Tabellenblatt austauschen sollen. Also nehmen wir in diesem Formular dieses Symbol (genau: die ganze Gruppe) aus dem Menüband:
Dazu sind ein paar Zeilen in der XML-Datei nötig, in der das Menüband beschrieben wird:
Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:
Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:
Private Sub Workbook_Open()
On Error Resume Next
Application.OnKey "^f", "BitteNicht" ' suchen
Application.OnKey "^h", "BitteNicht" ' ersetzen
End Sub
Beim Öffnen der Datei werden diese beide Tastenkombinationen „verbogen“, indem das Makro „BitteNicht“ aufgerufen wird. Es erscheint ein Meldungsfenster. Diese Prozedur wird auch beim Aktivieren der Mappe gestartet:
Private Sub Workbook_Activate()
Schnell merke ich, dass das Makro nach Schließen der Datei (oder auch Wechseln in eine andere Datei) noch aktiv ist. Also: raus damit:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f", "" ' suchen
Application.OnKey "^h", "" ' ersetzen
End Sub
Ebenso beim Ereignis Workbook_Deactivate.
Und was passiert? Richtig – wenn ich jetzt [Strg] + [F] drücke, passiert: NICHTS. Warum? Genau – ich muss natürlich schreiben:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f" ' suchen
Application.OnKey "^h" ' ersetzen
End Sub
Ich weiß nicht, ob Sie mir weiterhelfen können. Ich
untersuche englische Dramentexte bezüglich der Wortlänge in der Anzahl der
Zeichen.
Jetzt habe ich 100 Spalten. Jede Zeile enthält in jeder
Spalte ein Wort eines fortlaufenden Textes. In den Spalten 101 – 110 möchte ich
gern die Anzahl der Worte festhalten, die ein, zwei, drei, etc.
Buchstaben lang sind. Spalte 111 soll für jede Zeile den
Wert größer zehn enthalten.
Mit meinen Formelversuchen bin ich kläglich gescheitert,
auch wenn Ihre Anmerkungen in der Formelsammlung tröstlich und erheiternd
waren.
Auch wenn Sie keinen Tipp haben sollten, herzlichen Dank für Ihre Mühe.
Hallo Herr I.,
die Lösung heißt SUMMENPRODUKT.
Sie kann als Matrixfunktion LÄNGE verarbeiten.
Werfen Sie mal einen Blick auf das Beispiel – Sie müssen natürlich Zeilen und Spalten vertauschen und einige Spalten einfügen … aber ich glaube das ist die Lösung, die Sie haben möchten. (ich zähle hier die Wörter mit zwei, drei, vier, …) Buchstaben aus Spalte A, B, C, …
Liebe Grüße
Rene Martin
Lieber Herr Martin,
Danke für die schnelle und perfekte Lösung. Der
Hintergrund ist, dass sogenannte Marlowianer die Identität Marlowes mit
Shakespeare behaupten und dazu die häufigste Wortlänge von 4 Buchstaben ins
Feld führen, die bei beiden identisch ist. Tatsächlich ist das aber eine
Eigenschaft des englischen Sprachsystems, nicht aber Autorenidentität.
Liebe Grüße
Hallo Herr I.,
ach – DIE Diskussion. Jo, kenn ich; und die hundert
anderen Antworten auf Frage, wer Shakespeare wirklich war …
Sollte man wirklich solche positivistischen Ansätze, die
im 19. Jahrhundert verwendet wurden, ins Feld ziehen? *hum* *hum*
Access kann auch nerven! Ich erstelle gerade eine Datenbank für einen Kunden. Ich schreibe mehrere Hundert Daten per VBA in eine Tabelle. Sehe nach, ob die Daten alle „angekommen“ sind:
Up – nein!? – Die Warengruppen sind nicht da!? Der Filter zeigt nichts an!
Doch: aus irgendeinem Grund werden in dieser Spalte einfach keine Daten zum Filtern angeboten, obwohl sie weiter unten in der Tabelle zu finden sind:
Im ersten Moment dachte ich, meine VBA-Routine wäre falsch …
Für den
damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene
Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es
noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.
Mein
Vorgehen:
Entpacken der xltm.
Ich ergänze die customui.xml
Zippen aller Daten, umbenennen zu xltm .
Ergebnis:
Datei kann nicht mehr geöffnet werden. Ich habe gefühlt alles genau gleich
gemacht wie beim ersten Mal. Haben Sie dazu eine Idee?
Besten Dank für einen Tipp dazu 🙂
Freundliche Grüsse – Kind regards
Hallo Herr S.,
es könnte
möglicherweise am Zippen liegen.
Achten Sie darauf, dass Sie „auf der richtigen Ebene“ sind und ALLE Ordner und die XML-Datei markiert haben, also HIER:
Kennt ihr das? Ich bislang nicht. Ich erhalte eine Exceldatei, die auf dem Macintosh erstellt wurde. Ich versuche die Datei als PDF zu speichern im Format PDF/A:
Excel weigert sich mit der Bemerkung:
Das Dokument wurde nicht gespeichert. Das Dokument ist möglicherweise geöffnet, oder beim Speichern ist ein Fehler aufgetreten.
Ich kreise den Fehler ein und finde ihn schließlich in einer Zelle. Darin befinden sich Zeilenumbrüche ([ALT] + [Enter]). Die mag mein PDF/A für PC nicht …
ich wieß nicht mehr
genau, ob ich Sie einmal auf folgende Thematik angesprochen hatte.
Es ging um die
Umformatierung von Postleitzahlen aus einer Spalte in Aneinanderreihung
in einer Zelle mit Komma/-Lehrzeichen-Trennung (siehe Anhang „screenshot“):
Diese Schreibweise
(nebeneinander) ist die auf unserem content-management-system.
Wenn ich nun größere
Zahlenmengen erhalte, wird es händisch sehr aufwendig.
Könnten Sie mir bei
diesem Prozedere wieder behilflich sein?
Vielen Dank schon
vorab.
Mit besten Grüße
Hallo Herr S.,
welche Excel-Version
haben Sie denn? Haben Sie die Funktion TEXTVERKETTEN? DAMIT klappt es ganz
einfach:
=TEXTVERKETTEN(„,
„;WAHR;E3:E14)
E3:E14 ist natürlich
der Bereich der Postleitzahlen. Dann kopiere ich den Bereich an eine andere
Stelle und füge ihn als Werte ein – schon habe ich die Liste.
Klappt das?
Liebe Grüße
René Martin
Hallo Herr Martin,
vielen dank für die
schnelle Reaktion. Ich habe die 2010’er Version Version 14.7263.5000 (32 Bit)
Direkt das Wort
TEXTVERKETTEN ist in den Formeln nicht enthalten.
Gibt es unter dieser
alten Version vielleicht trotzdem eine Lösung?
Beste Grüße nach München C. S.
Hallo Herr S.,
oder so:
wiederholen Sie in der
ersten Zelle den Wert, bspw.: =E16
schreiben Sie
darunter: =F16&“, „&E17
ziehen Sie die untere
Formel runter!
das Ergebnis der
letzten Zelle können Sie kopieren und an andere Stelle als Wert einfügen
Liebe Grüße
Rene Martin
Einfach genial,
Sie machen einem Freude! ! !
Einfach soo logisch, dass ich mir
immer wieder die Frage stelle,
warum komme ich nicht einmal selbst
auf solch logische Lösungen.
Ich hoffe, dass ich ihre Geduld und
Zeit nicht zu sehr in Anspruch nehme.
Ganz herzlichen Dank. Bleiben Sie
gesund und zuversichtlich
Gestern habe ich (mal wieder) eine Knobelaufgabe in Excel gepostet. Die Aufgabe lautet:
„Lust auf Knobeln? Mit Excel? Mit Excel-Diagrammen? In einer Liste stehen Werte. Auf diesen Werten wird ein Diagramm – ein Kreisdiagramm oder Liniendiagramm aufgesetzt. Dumm nur, dass einer oder einige Werte 0 sind. Dumm, dass die Datenbeschriftung angezeigt wird, aber kein Wert: im Kreisdiagramm kein Tortenstück; im Liniendiagramm eine Lücke. Aber natürlich automatisiert – also: wenn sich die Werte ändern, sollen die entsprechenden Werte verwendet werden. „
Die Antwort lautet natürlich, dass man aus 0 ein #NV machen muss – beispielsweise mit einer WENN-Funktion. Es genügt #NV bei den Werten zu verwenden. Das ist die Grundeinstellung der Diagramme:
Ich erhalte die Frage:
„bei meiner Excel-Version sieht das noch anders aus:“
Hinweise:
Dieses Feature ist nur verfügbar, wenn Sie über ein Microsoft 365-Abonnement verfügen und derzeit nur für Insider verfügbar ist. Wenn Sie Microsoft 365-Abonnent sind, vergewissern Sie sich, dass Sie über die neueste Office-Version verfügen.
Ah – man (ich) muss auch das Kleingedruckte lesen!
da du ja nervige Dinge sammelst, hast du vielleicht auch eine Antwort auf mein Phänomen, was mich einige Nerven gekostet hat,
bevor ich auf eine Lösung gestoßen bin. Anbei ein Screenshot
meines Diagramms ( habe mich nicht getraut eine Datei zu versenden) was ich
eigentlich mal ganz schnell erstellen wollte,
bis zu dem Zeitpunkt, wo ich den aus einem Fehlerindikator dargestellten dynam. Pfeil ein wenig zurechtrücken (rechts- links verschieben) wollte.
So geht es nicht!
Es hat lange gedauert den Wert im Array dann mit einem Dezimalpunkt einzugeben,….weil die bearbeitete Datenreihe immer ein Komma anzeigt. Ich denke, dass es nicht nur mir so ergeht,
oder irre ich mich ?, wie Sam Hawkens es einmal bei Winnetou so schön sagte.
So klappt es!
Mit lieben Grüßen in den Süden.
Hallo Jürgen,
Hübsch – DAS kannte ich noch nicht. Du hast recht: HIER wird Komma als Trennzeichen und Punkt als Dezimaltrennzeichen interpretiert. Wahrscheinlich hat an dieser Stelle ein Programmierer geschlafen …
Kannst Du mir sagen, wie ich, wenn in einem Tabellenblatt einen Wert aus einem anderen Reiter angegeben ist, in der daneben liegenden Spalte automatisch den Wert aus einer anderen Spalte der Tabelle in dem anderen Reiter ausgebe?
In A2 habe ich per Dropdown mit INDIREKT „<10%“ aus tab_AntwortKategorie[Kategorie].
In B2 möchte ich automatisch den dazugehörigen Wert aus tab_AntwortKategorie[Wert].
Was muss ich in B2 eingeben?
Ohne INDIREKT wüsste ich es, aber wie gesagt – ich stehe einfach auf dem Schlauch.
Momentan „fuhrwerke ich wild (und falsch) rum“, z.B.
=WVERWEIS($AE4;tab_AntwortKategorie[Kategorie];[Wert])
Bye
Michael
Hallo Michael,
wenn du den Wert DANEBEN haben möchtest, musst du den SVERWEIS verwenden, nicht den WVERWEIS. „S“ steht für „senkrecht“, heißt: die Liste ist von oben nach unten aufgebaut.
Hallo, ich habe auch so ein Problem in meinem Kalender zur Arbeitszeitberechnung mit den Rauten. Excel gibt mir aber den Hinweis, das entweder die Zelle zu klein ist, oder ein Negativ Wert darin enthalten ist. Bei mir scheint es wohl der Negativ Wert zu sein da eine Formel in der Zelle enthalten ist bei der ich zb. 8 von 9 in meinem Fall Stunden abziehen möchte. Da ich die Zeiten über Dropdown Listen eingebe, habe Rauten in den Zellen solange ich noch keine Werte eingegeben habe. Das sieht natürlich nicht so gut aus. Nun meine Frage. Kann man diese Rauten auch einfach nur ausblenden ohne das die darin enthaltenen Formeln auch ausgeblendet sind? Ich hoffe das ich mein Problem verständlich erklären konnte.
Gruß Uli.
Hallo Uli?
Ich würde um die Formel (wie sieht sie aus?) beispielsweise
=C2-B2
eine WENN-Funktion bauen:
=WENN(C2-B2<0;““;C2-B2)
Hilft das?
Liebe Grüße
René Martin
Hallo Herr Dr.Martin,
Danke Ihnen nochmal für ihre Hilfe, es hat geklappt mit
der Formel.
War für Sie bestimmt eine Logische und einfache Sache, aber für mich als Blutiger Anfänger ein Riesen Problem. Würde mich freuen wenn ich mal wieder so ein Problem hätte, mich an Sie wenden dürfte.
Mit freundlichen Grüßen U.
Klar, Uli,
einfach schreiben! Ich helfe gerne
Liebe Grüße aus dem verregneten München
Rene Martin
PS: ich mag den Begriff „logisch“ nicht. Es
läuft eine gewisse erlernbare Mechanik ab. Jeder von uns hat ein bestimmtes
Wissen und erkennt Lösungen „intuitiv“ und kann sie sicherlich auch
beschreiben, warum dieser oder jeder Weg der richtige (oder der geeignete) ist.
Also: Kopf hoch, dranbleiben, schmunzeln – ich halte Excel für wichtig – nicht,
weil ich häufig damit arbeite, sondern weil sehr, sehr viele Menschen häufig
damit arbeiten.
Ich hätte da nochmal eine Frage zu den Matrixfunktionen.
Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der
auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den
Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn
zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.
Gruß
Hannes
Hallo Hannes,
du musst den Namen im INDIREKT in einen Bezug umwandeln.
Meinst du das?
Liebe Grüße :: Rene
Hallo Rene,
ja genau, da sind einige Haken drin.
Die Liste in der Datenüberprüfung übernimmt offensichtlich
nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine
Rückgabewerte aus Funktionen.
Aber wenn ich innerhalb einer „formatierten“ Tabelle einen
benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und
die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.
Wenn ich über die Sequenz-Funktion Werte ermittle und die in
einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben,
innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.
Die Problematik ist wahrscheinlich, dass die
Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher
auch die Bezugsherstellung mit der Indirekt-Funktion.
Keine Ahnung, ob man das irgendwie austricksen kann.
Gruß
Hannes
PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte
sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:
Und dann passiert:
Hallo Johannes,
eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also: =[@Umsatz]*19% oder: =SUMME(Tabelle1[Umsatz]) analog: =TEILERGEBNIS(109;[Umsatz])
DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.
Moin Rene,
Danke Dir für die ergänzenden Infos 🙂 Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂
Merkwürdig. Ich importiere per VBA Daten aus anderen Tabellen in eine Arbeitsmappe. Die Daten der Importtabelle sind als intelligente Tabelle gespeichert, in der aktuellen Tabelle liegt auch eine intelligente Tabelle. Damit sich diese beiden Tabellen nicht überlagern, ist es wohl das Beste die Tabelle zu löschen. Aber wie heißt der Befehl „In Bereich konvertieren“?
Nun – der Makrorekorder hilft:
Sub ZurueckZuDummerTabelle()
'
' ZurueckZuDummerTabelle Makro
'
'
End Sub
Nein – der Makrorekorder hilft nicht! Er zeichnet nur den Befehl auf: lösche das Tabellenformat. Aber nicht: lösche die Tabelle. Also muss ich doch auf die Suche gehen. Ich werde schnell fündig: die Methode heißt Unlist. Damit klappt es:
For j = 1 To xlBlatt.ListObjects.Count
xlBlatt.ListObjects(j).TableStyle = ""
' -- lösche die Formtierung
xlBlatt.ListObjects(j).Unlist
' -- in Bereich (zurück) konvertieren
Next j
Tabellenblätter in Excel in Excel haben nicht nur einen (sichtbaren) Namen, der vom Anwender oder von der Anwenderin geändert werden kann. Im VBA-Editor gibt es auch noch einen Name (Codename), der nur dort und nur per Hand geändert werden kann. Ich kann nun per Programmierung leicht überprüfen, ob alle Tabellen, die ich benötige, noch vorhanden sind. Ich habe eine Funktion GibtEsTabellenblatt geschrieben, die überprüft, ob die aktuelle Datei ein Tabellenblatt mit einem solchen Codenamen hat. Ich möchte die Arbeitsmappe nicht schützen, weil der Anwender oder die Anwenderin neue Blätter hinzufügen, löschen, umbenennen darf. Allerdings: meine Blätter dürfen nicht gelöscht werden. Ich sehe nach:
Jedes der Tabellenblätter hat ein Ereignis BeforeDelete:
Dieses Ereignis hat allerdings kein Abbruchkriterium (Cancel), mit dem man das Löschen verhindern könnte.
Der zweite Blick fällt auf das Objekt Workbook. Gibt es dort ein Abbruchparameter?
Das Ereignis dort heißt SheetBeforeDelete und hat auch keinen solchen Parameter:
Allerdings einen Parameter Sh mit dem ich gezielt überprüfen kann, welches Blatt nicht gelöscht werden darf (und die Liste schnell erweitern kann:
Select Case Sh.CodeName
Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
End Select
Problem: Die Meldung wird angezeigt und DANN das Blatt gelöscht. Doof!
Nächster Versuch: Und wenn ich DANACH die Arbeitsmappe schütze?
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
On Error Resume Next
Select Case Sh.CodeName
Case "tbl_Unternehmen", "tbl_Standorte", "tbl_Zuordnung1", "tbl_Organisationseinheit", "tbl_Zuordnung2", "tbl_Geschaeftsprozesse", "tbl_Zuordnung3", "tbl_Uebungstyp", "tbl_Szenario", "tbl_Verantwortlich", "tbl_Uebungsplanung", "tbl_Zuordnung4"
MsgBox "Bitte löschen Sie nicht das Tabellenblatt """ & Sh.Name & """!", vbCritical
ThisWorkbook.Protect
End Select
End Sub
Klappt! Hier die drei Meldungen, die erscheinen:
Allerdings: DANN ist die Arbeitsmappe geschützt. Was ich ja eigentlich nicht wollte. Also flux den Arbeitsmappenschutz wider aufheben. Wo? Am besten beim Wechseln auf ein anderes Arbeitsblatt:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error Resume Next
ThisWorkbook.Unprotect
End Sub
Im VBA-Editor kann man den (internen) Namen eines Tabellenblattes ändern. So kann man auf dieses Blatt über diesen (Code-)Namen zugreifen, egal an welche Position es der Anwender oder die Anwenderin schiebt; egal ob es in Excel umbenannt wird. Allerdings kann es der Anwender oder die Anwenderin löschen. Kein Problem – ich baue einen Button ein, der ein neues Blatt erzeugt:
Dim xlBlattNeu As Worksheet
Set xlBlattNeu = ThisWorkbook.Worksheets.Add
xlBlattNeu.CodeName = "tblITAnwendungen"
*gggrrrrr* CodeName ist schreibgeschützt.
Zuweisung an schreibgeschützte Eigenschaft nicht möglich.
Kann also nicht in VBA umbenannt werden. Ich kann nur überprüfen, ob das Blatt vorhanden ist. Aber nicht (den Namen) erzeugen. Schade! Ärgerlich! Aber verständlich.
Problem: der Anwender oder die Anwenderin kann das Blatt verschieben, wenn die Arbeitsmappe nicht geschützt ist. Außerdem ist die Zählung bei ausgeblendeten Blättern schwierig.
Es gibt noch eine dritte Variante. Im VBA-Editor kann man in den Eigenschaften einen Namen festlegen:
Über diesen Namen kann man auf das Blatt zugreifen, beispielsweise:
tbl_Uebungsplanung.Range("F1").Value = _
tbl_UnternehmenCode.Range("A1").Value ' -- Unternehmen
Auch der Zugriff auf ein Blatt über seinen Codename in einer anderen Datei geht nicht!
Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open("D:\Übungspanung.xlsx")
MsgBox xlDatei.tbl_UnternehmenCode.Range("A1").Value
Schade! Man muss mit einer Schleife über alle Blätter iterieren und abfragen, ob der Codename = „tbl_UnternehmenCode“. Und darauf einen Verweis setzen.
Ich erstelle gerade ein größeres VBA-Projekt für einen Kunden. Und bin mal wieder verblüfft! Die größte Verblüffung hatte ich letzte Woche. Man erstelle in Excel eine XLSM-Datei mit Makros. Ein Makro wird über eine Schaltfläche (oder ein Bild, ein SmartArt, eine Form, ein Diagramm) aufgerufen:
Ich kopiere das Tabellenblatt in eine andere Datei
und speichere die Datei OHNE Makros als XLSX! Ich schließe alles, öffne die XLSX-Datei und werde gefragt, ob ich die Makros aktivieren möchte:
Okay. Ich aktiviere die Datei. Ein Klick auf die Schaltfläche und werde darauf hingewiesen: Microsoft Office hat ein potenzielles Sicherheitsrisiko erkannt.
Was passiert? Die andere Datei wird im Hintergrund geöffnet, das Makro wird ausgeführt! Denn: hinter der Schaltfläche steht noch immer der Dateiname und Makroname.
Fazit: Mein Glaubenssatz: XLSX-Dateien sind sicher, denn sie können keine Makros enthalten, ist erschüttert! Zwar enthalten XLSX-Datei weiterhin keine Makros, können aber Makros aus anderen Dateien aufrufen!
Ich suche gerade nach einer Lösung: Bei mir ist plötzlich der Zoom in der Seitenansicht eingeschränkt. Früher sah ich eine ganze Seite bei Druckvorschau. Jetzt ist der Zoomfaktor größer und ich muss zum unteren Seitenrand skrollen.
Hat jemand eine Idee, wie man das wieder ändert? Hatte das gleiche Problem vor Jahren schon mal, aber Google findet leider diese Seite nicht mehr….
Hallo Peter,
ich schreibe mal direkt.
Meinst du so etwas?
Wenn die Skalierung Anpassen auf 1 x 1 Seite eingestellt ist, macht Excel so etwas. Zurück auf Ansicht / Normal – beim zweiten Mal ist der Spuk verschwunden.
Liebe Grüße
René
Hallo Martin,
wow, mit soo einer schnellen Reaktion habe ich nicht
gerechnet. Vielen Dank!
Ich habe das „Problem“ anhand von zwei Screenshots verdeutlicht: auf dem Rechner meiner Frau wird eine Seite A4 in der Druckansicht komplett dargestellt, bei mir auf dem Rechner (seit kurzem) nicht mehr, d.h. ich muss um das Seitenende zu sehen scrollen.
Der „Schalter“ in der rechten unteren Ecke zoomt bei mir nicht mehr auf Seitengröße – wie gesagt, ich hatte das Problem vor Jahren schon mal, aber finde die Lösung nicht mehr…
ich habe eben begonnen, Ihre Übungen zu den statistischen
Funktionen durchzuführen.
Bei dem Thema Häufigkeit sehe ich eine Differenz zwischen
dem, was Sie zeigen und dem, was ich gemacht habe, obwohl die Zahlen, die Sie
benutzen, die Zahlen sind, die ich auch genommen habe (ich habe diese Zahlen
von Ihrem Beispiel abgetippt).
Ich verstehe nicht den Unterschied, obwohl ich wie Sie die
Funktion Häufigkeit verwendet habe.
Das betrifft auch das Ausrechnen der Häufigkeit als Matrix.
Auch hier unterscheiden sich die Zahlen.
Zur Überprüfung schicke ich Ihnen meine Datei mit.
Hallo Herr F.,
HÄUFIGKEIT liegt in zwei Varianten vor: als Matrixfunktion und als „normale“. Wenn Sie im „neuen“ Excel HÄUFIGKEIT verwenden, wird es als Arrayfunktion interpretiert: wie viele Daten sind bis zu Wert x, wie viele darüber:
Bei den „älteren“ Excel-Versionen ist dies nicht nötig. So kann ich die Funktion runterziehen. Dazu muss der Datenbereich fixiert werden. Die Daten werden also kumuliert:
Da HÄUFGKEIT matrixfähig ist, muss ich (in denen neueren Excel-Versionen) nicht mehr mit Umschalt + Strg + Enter beenden. Dann werden die einzelnen Bereichen berechnet:
also, es geht um die Funktion „Text in Spalten“. War
eigentlich eine Frage eines Kollegen, die ich selbst nicht beantworten konnte.
Ich habe eine CSV-Datei, in der die Werte auch wirklich durch Kommas getrennt sind:
Jetzt ist es so, dass auch innerhalb der Texte, die zu Zellinhalten werden sollen, Kommas drin sind, wie z.B. in Zeilen 1 und 2. Wenn ich jetzt „Text in Spalten“ mit der Option „Getrennt“ auswähle, dann packt Excel natürlich den Teil rechts vom Komma in eine neue Zelle:
Gibt’s ne Möglichkeit, das mit „Text in Spalten“ sauber zu
trennen? Mit PowerQuery krieg ich es hin, also saubere Trennung + korrektes
Anzeigen von Umlauten, ich frag mich nur, ob das „Text in Spalten“ nicht auch
irgendwie kann.
Danke dir und viele Grüße, Dominic
####
Hallo Dominic,
ich denke die Frage hast du schon selbst beantwortet:
* PowerQuery
* VBA
* Formeln (uff!)
Wenn der Anwender Zeichen in seinen Texten verwendet, die
als Trennzeichen vorgesehen sind; kann der Assistent Daten / Text in Spalten
das nicht abfangen. Wie wir wissen: die Grenzen von „Text in Spalten“ wurden ja
in PowerQuery erweitert – da geht mehr …
Liebe Grüße
Rene
PS: Man könnte die Blitzvorschau versuchen; ist aber mühsam und wahrscheinlich funktioniert auch das nicht.
Excel
nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.
Ich
müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer
anderen Zelle
Entsprechen.
So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob
Ich
den Inhalt der Zellen auch verkettet ausgeben kann.
Vielen Dank
Bevor ich eine Antwort gebe, Frau L.,
zwei Gegenfragen: sind die Daten sortiert (nach den
Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die
Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht
einfach“)
Liebe Grüße
Rene Martin
So
ist es mir recht,
leider
kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…
Eindeutig?
Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich
nicht aus…
Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden
Die
erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet
die Informationen die ich gerne zusammen verkettet hätte, also
Für
20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert
nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…
Beim
Trennzeichen wäre ich emotional flexibel
Die
erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.
Vielen Dank
Hallo Frau L.,
ich hoffe, Sie
haben die Funktion TEXTVERKETTEN. DAMIT klappt es.
Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.
Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.
„Das Problem ist, daß sich Excel/Windows leider nicht wie beschrieben verhält:
In dem Feld müßte eigentlich 02.12.2020 stehen, es wird jedoch 02.12.1920 angezeigt, obwohl die Einstellung in der Systemsteuerung korrekt ist.
Das Problem tritt auch nur über die Formel auf. Wird das Datum manuell mit zweistelliger Jahreszahl angegeben, dann wird die Ergänzung der ersten zwei Ziffern korrekt vorgenommen …
Ich benötige die Formel zur Umsetzung eines Datumsfeldes aus einem Datenabruf im Format „JJMMTT“. Ich habe mir jetzt mit einer „wenn“-Funktion einen „Workaround“ gebastelt, aber es kann doch nicht sein, daß die Excel-Formel sich nicht an die Windows-Regeln hält …“
Und meine Antwort darauf:
„genau hinschauen! Excel verlangt bei der Funktion DATUM eine Jahreszahl zwischen 1900 und 9999:
So wie beispielsweise der Sinus die Angaben nicht in Grad, sondern im Bogenmaß haben will.
Monat verlangt beispielsweise einen Monat zwischen 1 und 12. Wenn ich die Monatszahl 24 eintrage, rechnet Excel Monat 12 + 12 weitere Monate. Ebenso beim Jahr.
Der Beginn liegt
bei 1900. Wenn ich nun 20 eingebe, wird das Datum -1880 Jahre berechnet. Das
kann Excel nicht. Eigentlich müsste ein Fehler die Folge sein: #ZAHL – ebenso
wie bei
=DATUM(-2021;1;18)
Wahrscheinlich sind
die ersten 1.900 Zahlen abgefangen und ins Jahr 1900 transformiert worden.
Eigentlich schon korrekt – gib Excel, was des Excels ist: wir leben im Jahre 2021 und nicht im Jahr 21!
Und: richtig: bei
der Eingabe von Zahlen wird der eingegebene Wert immer interpretiert:
12-1-21 wird in
Deutschland zum 12. Januar 2021; in den USA zum 01. Dezember 2021. Deshalb
drücken wir auch [Enter] und überlassen dem Compiler, was er aus der Eingabe
macht.
also: ZÄHLENWENN, leistet, was ich möchte: ein Befehl (ohne Schleife) und ich habe die Information (Wert in der Spalte oder nicht vorhanden). Ebenso verwende ich häufig SUMMEWENN:
Application.WorksheetFunction.SumIf
oder – um die Zeilennummer zu ermitteln VERGLEICH:
So spare ich mir das Schreiben von Schleifen. Nun wollte ich die kumulierten Geldbeträge zu bestimmten Monaten wissen. In Excel lautet die Funktion
=SUMMENPRODUKT((MONAT(A:A)=1)*(B:B))
also: summiere die Werte der Spalte B, wenn eine Datumsangabe in der Spalte A ein Datum des ersten Monats (Januar) im Jahr enthält. Klappt wunderbar. Und in VBA? Dort versagt eine Zeile wie:
Die Ursache ist schnell gefunden: Colums(1) = 1 kann nicht verarbeitet werden; auch nicht Month(Columns(1)); der Gleichheitsoperator in VBA ist nicht matrixfähig; „=“ kann nur identische Dinge vergleichen.
Angelika ruft erneut an: „Hallo Rene: SVERWEIS kann DOCH zwischen Groß- und Kleinschreibung unterscheiden. Schau mal, ich hab’s gefunden!“
Prinzipiell glaube ich andere Menschen NICHT. Ich schaue die Datei an, die sie mir geschickt hat:
„Schau“, sagt sie, „der Text arnstein steht einmal groß- und einmal kleingeschrieben in der Liste. Wenn ich die Liste nun sortiere, und den Parameter WAHR beim SVERWEIS verwende, findet Excel den unteren, großgeschriebenen Eintrag.“
Ich prüfe das und trage den Text in Kleinbuchstaben ein: „arnstein“.
Auch hier wird der untere gefunden. Ich stutze: „Angelika: deine Liste ist nicht sortiert. Probier mal aus: =CODE(„A“) ergibt 65, =CODE(„a“) liefert 97. Arnstein ist kleiner als arnstein. Du musst anders herum sortieren.
Wir probieren es aus:
und auch:
Beide Male wird der untere Text gefunden. Die folgende Vermutung liegt nahe:
SVERWEIS „läuft“ bei der Verwendung des Parameters WAHR in [Bereich_Verweis] in einer Liste so lange nach unten bis ein größerer Wert als der gesuchte gefunden wird. Dann „stoppt“ SVERWEIS und gibt den Wert der zuletzt gefundenen Zeile zurück. Dabei wird weder bei WAHR noch bei FALSCH zwischen Groß- und Kleinschreibung unterschieden (wie fast an kleiner Stelle in Excel).
Fazit: Wenn man Texte in Listen mit SVERWEIS suchen muss (was durchaus sein kann – Artikelnummern, Personalnummer, Länderkennzeichen, etc. die alphanumerisch aufgebaut sind), sollte man NIEMALS die Liste sortieren und mit dem Parameter WAHR (oder ohne diesen Parameter) in SVERWEIS arbeiten. Schließlich möchte man ein eindeutiges Ergebnis. WAHR ist nur bei numerischen Reihen sinnvoll, beispielsweise: Umsatzzahlen, Erlöse, Gewinne, Kosten, Kilometer … von Betrag x bis Betrag y. Oder Datum von d1 bis d2.
Ich glaube, Angelika war mit meiner Erklärung einverstanden. Ganz sicher bin ich mir nicht. Ich werde sie noch einmal anrufen.
Angelika fragt weiter: „Kennst du das Problem? Ich habe eine Liste in der Begriffe mehrmals in unterschiedlicher Schreibweise vorliegen – manchmal in Großbuchstaben, manchmal nur in Kleinschreibweise. SVERWEIS findet leider nur den ersten Eintrag – egal wie er geschrieben ist.“
Bevor ich antworten kann, beantwortet Angelika ihre Frage selbst: „Ich habe vor Kurzem einen Artikel von Martin gelesen; dem Tabellenexperten – er beschreibt dort das Problem mit der Lösung IDENTISCH. Diese Funktion unterscheidet Groß- und Kleinschreibung.“
Ein Freund von mir erklärt mir häufig, dass er sich nicht bei facebook anmeldet, weil fb ein Zeitfresser sei. Nun: ich kenne einen anderen Zeitfresser. Er heißt: VBA! Kennt ihr folgende Anomalie? Ich erstelle ein Userform mit einem Listenfeld. Zu den Einträgen sollen mehrere Einträge ausgewählt werden können. Ich entschließe mich für ein zweites Listenfeld, das ich daneben platziere. Dem Auftraggeber gefällt es nicht, weil die Userform groß und unübersichtlich ist (sehr viele Steuerelemente). Ich entschließe mich zu einer zweiten Userform. Also: Eintrag auf dem Listenfeld wird ausgewählt: Doppelklick, eine zweite Userform wird geöffnet, mit Werten gefüllt, von denen der Anwender mehrere auswählen kann (MultiSelect).
Zirka zwei Stunden lang habe ich über folgendes Problem gestutzt: Während des Doppelklicks öffnet sich die zweite Userform. Das Klickereignis wird schon abgefangen und sorgt dafür, dass auf der zweiten Liste der zweiten Maske auch Einträge selektiert werden. Sämtliche Versuche per VBA alles zu deselektieren (Selecetd(i) = False) scheitern! Ich habe mich dann entschieden die Userform umzubauen. Das Listenfeld der zweiten Form liegt nicht mehr über dem Listenfeld der ersten. Dann klappt es!
Böses, böses Excel – manchmal nervst du! Also doch lieber facebook – dort vertrödle ich weniger Zeit!
Die Teilnehmerin der Excelschulung überlegt sich für jede Kategorie ein eigenes Tabellenblatt zu erstellen. So kann sie es leichter als PDF speichern und verschicken.
Ich überlege, ob die Berichtsfilterseiten ein geeignetes Werkzeug sein könnten. Ich erstelle eine Pivottabelle und achte darauf, dass die Daten NICHT zum Datenmodell hinzugefügt werden. Sonst kann man keine Berichtsfilterseiten erstellen:
Dennoch: der Befehl „Berichtsfilterseiten erstellen“ ist ausgegraut …
Ich habe eine Weile überlegt. Dann fiel mir ein: damit ich Berichtsfilterseiten erstellen kann, muss ich in der Pivottabelle einen Filter erstellen:
Die Daten, die auf den einzelnen Tabellenblättern dargestellt werden sollen, müssen als Filter vorliegen.
Tatsächlich! JETZT geht es!
Also: zwei Voraussetzungen, um Berichtsfilterseiten zu erstellen :
Beim Erstellen der Pivottabelle NICHT dem Datenmodell hinzufügen!
Amüsant. Wir kennen das: Wenn ich in Excel eine Zahl schreibe, herunter oder nach rechts (oder nach oben oder links) ziehe, steht in der jede Zelle die gleiche Zahl:
Drückt man die [Strg]-Taste, zählt Excel weiter:
Ebenso kann man das Weiterzählen über das Smarttag erzwingen:
Bei Datumsangaben oder bei Text-Zahl-Gemischen wird hingegen weitergezählt:
Natürlich kann man auch den Assistenten „Datenreihe ausfüllen“ verwenden, den Sie in Start / Bearbeiten / Ausfüllen finden:
Formatiert man allerdings eine Zelle als Text und fügt eine Zahl ein (oder schreibt ein Apostroph vor eine Zahl; zieht nun diese Text-Zahl nach unten oder rechts, so wird jetzt weitergezählt. Ähnlich wie bei Text-Zahl-Gemischen.
In der letzten Excelschulung waren wir erstaunt. Wir verknüpfen mehrere Tabellen miteinander:
Warum dauert das Verknüpfen der Daten in PowerQuery so lange?
Die Ursache war schnell gefunden: die Teilnehmerin hatte den Cursor nicht in die Liste gesetzt und so aus der Liste eine (intelligente) Tabelle erzeugt, sondern die ganzen Spalten markiert und dann diese (mit den leeren Zeilen) in eine Tabelle umgewandelt.
Der Anfang der Tabelle:
Und das Ende:
Als wir den Fehler entdeckt hatten, wollte die Teilnehmerin den Bereich „per Hand“ nach oben ziehen:
Ich werde nervös, wenn Aktion SOOO lange dauern. Ein kurzer Blick … das muss doch schneller gehen … und wirklich: es geht schneller. Das Werkzeug „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ bietet eine schnelle Möglichkeit Tabellen zu vergrößern und verkleinern. Man muss nur $A$1:$E$2156 tippen – und schon ist die Tabelle kleiner. Und PowerQuery schneller!
Ich erstelle ein VBA-Projekt in Excel, das auf dem Mac und auf dem PC laufen soll. Da die Trennzeichen zwischen den Ordnern unterschiedlich sind („\“ auf PC, „/“ auf Mac), überprüfe ich, auf welchem System das Programm gerade läuft. Die Funktion
=INFO(„SYSTEM“)
liefert entweder „pcdoc“ oder „mac“. Prima!
Dann kann ich das doch in VBA verwenden. Ich werde eines Besseren belehrt:
Die Funktion INFO (oder Info) findet sich nicht in der Liste der Worksheetfunctions! Objekt unterstützt diese Eigenschaft oder Methode nicht. Lautet die Fehlermeldung. Abhilfe schafft der Befehl die Funktion in eine Zelle zu schreiben, den Wert auszulesen und die Formel wieder zu löschen. Beispielsweise so:
Dim xlBlatt As Worksheet
Dim strSystem As String
Set xlBlatt = ActiveSheet
xlBlatt.Range("A1").FormulaR1C1 = "=INFO(""SYSTEM"")"
strSystem = xlBlatt.Range("A1").Value
MsgBox strSystem
xlBlatt.Range("A1").ClearContents
Dass die Zwischenablage in Excel für Mac fehlt – damit kann ich leben:
Aber ein fehlender Namensmanager – das ist doof! Vor allem, weil man so nicht erkennen kann, ob ein Name lokal für ein Blatt oder global für die Mappe vergeben wurde.
Auch Excel online stellt keine Alternative dar, wenn man gerne mit Namen in Excel arbeitet:
Vor vier Wochen war ich in einer Rechtsanwaltskanzlei und habe gefühlte 15.000 Diagramme bearbeitet. Dabei sind mir eine Reihe an Dingen aufgefallen. Beispielsweise:
In einem Liniendiagramm wollten wir mehrere Linien glätten. Muss man jede Linie markieren und einzeln die Option „glätten“ aktivieren?
Auch die Wiederholfunktion ([Strg] + [Y] oder [F4]) funktionieren nicht …
Vor vier Wochen war ich in einer Rechtsanwaltskanzlei und habe gefühlte 15.000 Diagramme bearbeitet. Dabei sind mir eine Reihe an Dingen aufgefallen. Beispielsweise:
Wir erstellen ein Diagramm, das die durchschnittlichen Prozesskosten berechnet. Und zwar der Jahre 2013 – 2018 und 2019 – 2020. Und daneben der Gesamtdurchschnitt. Das Diagramm sah wie folgt aus:
Einer der Rechtsanwälte warf einen Blick darauf und sagte, dass die Berechnung (und die Darstellung) nicht stimmen könne: der Durchschnitt der beiden Durchschnitte sei doch der Mittelwert. Im Diagramm sei er viel zu gering.
Mein Bauchgefühl sagte, dass dies nicht stimmen könne. Ich nahm ein Blatt Papier und rechnete:
Der Durchschnitt von 2 und 4 ist 3. Der Durchschnitt von 10 und 20 ist 15. Der Durchschnitt von 3 und 15 ist 9; ebenso wie der Durchschnitt von 2, 4, 10 und 20. Sollte der Rechtsanwalt doch recht haben? Zweiter Versuch:
Ich berechne den Durchschnitt von 1, 2, 3, 4 und 5. Er lautet 3. Der Durchschnitt von 10 und 20 beträgt 15. Mittelwert von 3 und 15 ist 9; jedoch: Durchschnitt von 1, 2, 3, 4, 5, 10 und 20 lautet 6,42857
Stimmt: wenn die Anzahl unterschiedlich ist, ist der Durchschnitt zweier Duchschnitte eben nicht der Mittelwert aller Zahlen. Der Rechtsanwalt hat sich geschlagen gegeben. Wäre doch gelacht!
Vor vier Wochen war ich in einer Rechtsanwaltskanzlei und habe gefühlte 15.000 Diagramme bearbeitet. Dabei sind mir eine Reihe an Dingen aufgefallen. Beispielsweise:
Die Daten werden auf Basis anderer Daten erhoben und berechnet. Auf diesen Daten wird ein (Linien-)Diagramm aufgesetzt, das die Entwicklung abbilden soll. Erstaunlicherweise zeigt der Trend im Jahr 2020 nach unten. Der Grund ist einfach: es liegen noch keine Daten für Dezember vor; die Berechnung ergibt 0; die Trendlinie zeigt nach unten. Nein – DIESER Datenpunkt darf nicht verwendet werden.
Vor vier Wochen war ich in einer Rechtsanwaltskanzlei und habe gefühlte 15.000 Diagramme bearbeitet. Dabei sind mir eine Reihe an Dingen aufgefallen. Beispielsweise:
Wir wollten einen Titel ändern. Er ließ sich allerdings nicht editieren. Weder mit [F2] noch per Doppelklick:
Es hat eine ganze Weile gedauert, bis ich bemerkt habe, dass der Text nicht in der Titelzeile steht, sondern in einer Zelle, auf die Bezug genommen wird. Nur dort (in der Bearbeitungsleiste oder in der Zelle) kann der Text geändert werden.
Schauen Sie mal in
dem Tabellenblatt nach, welcher Wert in der Zelle F2 steht. Mit Sicherheit die
Nummer der Spalte aus der der Wert des Bereichs !$F$14:$DD$150 des Blattes „specs
quotation“ geholt wird.
Ich vermute, dass
der SVERWEIS in mehreren Zellen der gleichen Spalte verwendet wird – deshalb
wurde die Zeile 2 fixiert, also statt F2 wurde geschrieben F$2. Vielleicht wird
er auch in mehreren Spalten verwendet. Was steht in G2? Was in H2? Wenn der
SVERWEIS auch in mehreren Spalten benutzt wird, dann bedeutet F (ohne
$-Zeichen) ein relativer Bezug – das heißt: in der Spalte daneben wird der Wert
aus G$2 verwendet.
Wenn SVERWEIS nur
in einer Spalte zu finden ist, hätte man auch $F$2 schreiben können, F$2 ist
aber auch okay.
verstehen Sie die
Ausführung?
schöne Grüße aus dem kalten und sonnigen München Rene Martin
Lieber
Herr Martin,
Vielen
Dank, in Zelle F2 steht leider nix und in allen Formel Zeilen nach unten steht
immer F$2 (eh klar),
die Info die in der Zelle aufscheint steht in Spalte :-J…
Es
braucht definitiv Smiley in Outlook…
Ev
nervt es ja doch, noch so kurz vor Weihnachten…
Danke
Lg Judith
Hallo Judith,
klicken Sie mal
bitte auf die Zelle mit der Formel SVERWEIS.
klicken Sie mal auf
den Funktionsassistenten fx neben der Eingabezelle.
Dort müsste der Werte von F$2 angezeigt werden:
Die
Erleuchtung kam gerade, yup, in F2 steht 5, dass ist die 5te Spalte in der der
Wert
Steht
ausgehend von der Matrix.. Excel nervt doch nicht J
Vielen
Dank!
Liebe Grüße ins wunderschöne München!
Frohe
Feiertage und einen guten Rutsch ins nächste Jahr!
Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.
Nochmal langsam:
This error value is generally associated with the use of RAND, RANDARRAY, and RANDBETWEEN functions. Other volatile functions such as OFFSET, INDIRECT, and TODAY do not return different values on every calculation pass.
Ich probiere es aus: In einer Spalte stehen Zufallswerte:
=ZUFALLSBEREICH(„1.1.2020″;“31.12.2020“)
Die beiden Funktionen SORTIEREN und SORTIERENNACH funktionieren problemlos:
Jedoch erzeugen die beiden Funktionen FILTER und EINDEUTIG regelmäßig einen Fehler:
=FILTER(A3:A100;A3:A100>44000) und =EINDEUTIG(A3:A100)
Allerdings nicht immer:
Die Funktion ZUFALLSMATRIX, welche die Funktion ZUFALLSBEREICH in den Parametern min und max verwendet, bleibt erstaunlicherweise stabil:
