Eine Liebeserklärung an MS Excel
Schöne Frage in der Excel-Schulung:
Wie kann man beim Spezialfilter nur einen Teil der Liste auswählen? Also beispielsweise Spalte A:F und Spalte R:U?
Antwort: geht leider nicht! Man muss vom Ergebnis den nicht benötigten Bereich löschen.
Schöne Frage, gestern in der Excelschulung:
Ich möchte gerne ein Symbol in die Gruppe „Zahl“ einfügen, mit dessen Hilfe ich eine Zahl als Datum formatieren kann:
Meine Gegenfrage, ob nicht die Tastenkombination [Strg] + [#] gute Dienste tut, wurde verneint. „Ich hätte gerne zwei Symbole – eines für „kurzes Datum“, eines für „langes Datum“ war der Wunsch.“
Makros werden aber bei uns nicht unterstützt …
Schade – ich weiß keinen Ausweg! Keine Lösung für dieses Problem OHNE Makros.
Schöne Frage gestern in der Excel-Schulung:
Wie kann man die benutzerdefinierte Liste, die beim Sortieren verwendet wird, auslagern? Um sie dynamisch zu halten?
Ich wüsste nicht, wie das funktionieren sollte …
Im Moment nervt mich Outlook sehr. Beim Drücken einer Tasten verschwindet die Mail, an der ich schreibe, in den Ordner „Entwürfe“.
Gestern ist mir beim Schreiben einer Mail folgendes passiert: die Zeilen schieben sich übereinander:
Ich klappe die Mail aus und sehe:
Nicht wirklich schön!
Also: Mail speichern; Outlook beenden; Kopf schütteln; Outlook erneut öffnen – dann klappt es!
Seltsam – Outlook nervt auch!
Eine schöne Frage, die mir gestern gestellt wurde. Ich wusste keine Antwort.
In Excel kann ich – auf Basis einer Datenliste – mehrere Diagramme erzeugen:
Aber wie kann ich in PowerPoint EINE Datenquelle für mehrere Diagramme benutzen?
Ich glaube nicht, dass das funktioniert. Oder weiß jemand eine Lösung?
Hallo René,
ich lese regelmäßig deinen Blog „Excel nervt“ und hatte dir vor einiger Zeit auch schon einmal eine Mail zu deinem Buch „Excel: Zahlen. Rechnen. Formeln“ geschickt, die du mir sehr freundlich und ausführlich beantwortet hattest.
Heute bräuchte ich mal deine Hilfe. Ich sitze hier vor einem Problem, bei dem ich alleine nicht weiterkomme. Es geht um einen Milchviehbetrieb, der seine Jungtiere von einem Aufzuchtsbetrieb großziehen lässt. Ich soll nun rückwirkend die Aufzuchtskosten pro Monat überprüfen. Dazu steht mir eine Tabelle zur Verfügung mit dem Abgangsdatum der Tiere vom Milchviehbetrieb (= Zugangsdatum Aufzuchtsbetrieb) und dem Zugangsdatum der Tiere beim Milchviehbetrieb. Ist das Tier aktuell noch beim Aufzuchtsbetrieb, ist jeweilige Feld für das Zugangsdatum leer. Pro Tag, den eine Kuh beim Aufzuchtsbetrieb ist, erhält der Aufzuchtsbetrieb eine Pauschale (z.B. 1€ pro Kuh und Tag). Ich hatte irgendwie gedacht, dass man das relativ leicht über eine Formel ermitteln könnte. Problematisch ist vor allem ein angebrochener Monat, wenn beispielsweise eine Kuh am 5. März 2021 an den Aufzuchtsbetrieb geliefert wird, erhält der Aufzuchtsbetrieb für diese Kuh ja theoretisch 27 €.
Anbei habe ich eine Beispieltabelle hinzugefügt. In den Spalten E bis R möchte für den jeweiligen Monat und für jede Kuh die Tage ermitteln, die diese beim Aufzuchtsbetrieb war.
Kannst du mir da weiterhelfen? Irgendwie stehe ich gerade auf dem Schlauch.
Vielen Dank im Voraus.
Hallo,
hübsche Fingerübung.
Ich würde in die erste Zeile jeweils den 01. April 2020, 01. Mai 2020, … und als April 2020, Mai 2020, … formatieren. Also mit MMM JJJJ
Du musst einen „Baum abarbeiten“:
1. Fall: sind Jahr und Monat identisch -> dann rechne die Anzahl Tage bis Ende des Monats.
=WENN(UND(JAHR(E$1)=JAHR($C2);MONAT(E$1)=MONAT($C2));MONATSENDE($C2;0)-$C2+1;0)
Die Erklärung: Ich brauche in diesem Fall die Anzahl der Tage bis zum Ende des Monats. Die Funktion
MONATSENDE($C2;0)berechnet den letzten Tag des Monats (hier: 30.04.2020). Und davon wird das Datum abgezogen (hier: 11.04.2020). Das Ergebnis lautet 19; plus 1 = 20 = die Anzahl der Tage vom 11. bis zum 30. (beide einschließlich)
2. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem nächsten Monat -> dann voller Monat (die zweite WENN-Funktion wird die Stelle der 0 gesetzt:)
=WENN(UND(JAHR(F$1)=JAHR($C2);MONAT(F$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<F$1;$D2>=G$1);TAG(MONATSENDE(F$1;0));0))
Die Erklärung:
TAG(MONATSENDE(F$1;0))liefert die Anzahl der Tage des Monats – hier 31 beim Mai.
3. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem Datum (bspw. 01. April) -> Anzahl der Tage des Monats
=WENN(UND(JAHR(M$1)=JAHR($C2);MONAT(M$1)=MONAT($C2));
MONATSENDE($C2;0)-$C2+1;
WENN(UND($C2<M$1;$D2>=N$1);TAG(MONATSENDE(M$1;0));
WENN(UND($C2<M$1;$D2>M$1);TAG($D2);0)))
Auch hier wird wieder die 0 durch eine neue, dritte WENN-Funktion ersetzt. Die Funktion
TAG($D2)berechnet die Anzahl der Tage bis zum Tag, also aus dem 17.12.2020 wird die Zahl 17 berechnet.
4. Fall: noch kein Zugang eingetragen und Abgang vor dem Datum (bspw. 01. April) -> dann Tag des Monats
=WENN(UND(JAHR(Q$1)=JAHR($C7);MONAT(Q$1)=MONAT($C7));
MONATSENDE($C7;0)-$C7+1;
WENN(UND($C7<Q$1;$D7>=R$1);TAG(MONATSENDE(Q$1;0));
WENN(UND($C7<Q$1;$D7>Q$1);TAG($D7);
WENN(UND($D7="";$C7<Q$1);TAG(MONATSENDE(Q$1;0));0))))
Die Erklärung: Ebenso wie oben berechnet
TAG(MONATSENDE(Q$1;0)die Anzahl der Tage des Monats.
Stimmt das?
Hallo Rene,
vielen Dank für die schnelle Antwort.
Für diese Kühe hatte ich die Auswertung händisch vorgenommen und komme auf das gleiche Ergebnis wie mit deiner Formel. Scheint also zu passen.
Einen „Baum“ hatte ich mir auch schon überlegt, allerdings hatte ich Schwierigkeiten bei dem Umgang mit den „angebrochenen“ Monaten und den Kühen ohne Zugangsdatum.
In der ersten Zeile hatte ich sogar schon jeweils den 1. des Monats als Datum eingetragen, weil ich damit rechnen wollte. Ich hatte es dann über „Zellen formatieren…“ – Kategorie: Datum – Typ: Mrz. 12 umgewandelt. Blöd, dass Excel dann beim Mai auch einen Punkt macht, wie mir gerade auffällt.
Vielen, vielen Dank für deine Hilfe und mach weiter so. Ich werde deinen Blog auf jeden Fall weiter verfolgen.
Liebe Grüße
Amüsiert.
Word zeigt mir an, dass Dateien meine Aufmerksamkeit benötigen.
Ich sehe in Excel nach – DORT verlangt keine Datei meine Aufmerksamkeit.
Übrigens: dieser Hinweis kam, weil ein Word-Dokument nicht richtig auf OneDrive hochgeladen wurde …
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 = -1Klappt 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)Amüsant. Muss ich das verstehen?
Ich öffne mein Excel, das auf dem Desktop installiert und darin eine Datei. Ich kopiere einen Teil einer Tabelle nach Excel online, das auf dem SharePoint gespeichert ist.
Allerdings: ich darf nicht über das Kontextmenü einfügen, sondern muss die Tastenkombination(en) verwenden.
Muss ich das verstehen?
Das ist mir ja noch nie aufgefallen! Eine Teilnehmerin in der letzten Excelschulung hat mich darauf aufmerksam gemacht.
Setzt man in einer (intelligenten) Tabelle den Mauszeiger zwischen Überschrift und erste Zeile, kann man nur die Überschrift markieren:
Ist allerdings die Überschrift markiert …
… führt ein Klick auf die Unterkante dazu, dass die ganze Spalte markiert wird.
Aha – wieder etwas gelernt!
Hallo Rene
Ich schätze es ausserordentlich, dass Du mich unterstützt und fühle mich geschmeichelt.
Du hast natürlich vollkommen Recht mit dem Hinweis, dass in einer Spalte keine Zahlen und Texte stehen sollten.
Dieser Umstand ist dadurch entstanden, weil die Tabelle zusätzlich für einen anderen Zweck benutzt wurde. Hierbei wurden die Zeilen insofern erweitert, indem für jeden Kunden eine zusätzliche Zeile eingetragen wurde.
Hatte ein Kunde einen Service, wurde in der entsprechenden Spalte eine 1 reingeschrieben und im Anschluss mit einem Flow in einen ScharePoint Liste übertragen. Anders hätte ich ja die Kunden Records nicht handeln können.
Nun habe ich den Servicekatalog und die Zuweisung der Kunden zum jeweiligen Service getrennt. Somit konnte ich die Tabelle vom Servicekatalog wieder «drehen», womit die Services in den Zeilen stehen und die Spaltenwerte zu den jeweiligen Daten passen.
Eine andere Lösung gibt es nicht, wenn ich Deine Erklärungen richtig verstanden habe und eigentlich scheint es mir auch logisch.
Nun muss ich einige weitere Anpassungen an dem ganzen Konstrukt vornehmen und einen neuen Flow erstellen. Dabei hoffe ich natürlich, dass ich mir keine neue Baustelle geschaffen habe.
Nochmals herzlichen Dank für Deine wertvolle Hilfe und die guten Tipps
Herby
#####
Hallo Herby,
in meinen vielen Jahren Exceltraining und meinen vielen Artikeln auf excel-nervt habe ich gelernt, dass
* Excel ein sehr gutes Programm ist
* Excel manchmal etwas eigenwillig ist
* Anwender und Anwenderinnen oft Wünsche haben, die sich SO nicht direkt umsetzen lassen (ich verstehe oft die Hintergründe)
* dass man sich auf das Denken von Excel einlassen muss (ist halt ein Mann*) und man manchmal seine Daten etwas anders organisieren muss, damit man zum Ziel kommt
Liebe Grüße
Rene
*) Die Frage, ob Excel männlich oder weiblich ist, stelle ich häufig in Schulungen. Und amüsiere mich dann über die Antworten à la: „Excel ist männlich, weil …“ oder „Excel muss eine Frau sein, der nur so kann man sich erklären …“
Erstaunliches Outlook.
In einem Ordner befinden sich mehrere Hundert Mails. Eine davon ist ungelesen. Ich finde sie nicht. Na – kein Problem, denke ich und füge das Feld „Gelesen“ aus der Feldliste hinzu. Leider kann man DARÜBER nicht sortieren:
Okay – noch ein Versuch: Filtern.
Erstaunlicherweise kann ich nach gelesen/ungelesen filtern:
Geht doch! Trotzdem: seltsam!
Einfach nicht aufgepasst!
Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:
Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel
=WENN(J2>1000;WAHR;FALSCH)immer WAHR liefert!
Hallo Rene
Es ist unglaublich, aber ich habe wirklich das Gefühl, dass ich in jeden «Sche…sstopf» falle, welchen Microsoft zu bieten hat.
Seit 2 Tagen kämpfe ich mit dem Problem, dass in einer table in jeder Zelle scheinbar versteckte Tabs vorhanden sind.
Dies hat natürlich die traurige Konsequenz, dass damit s- oder wverweise auf diese table kläglich scheitern und zu #NV Fehlern führen.
Zum Problem mit Tabs hast Du ja den Artikel tabulatoren | Excel nervt … (excel-nervt.de) geschrieben, doch in meinem Fall hilft mir dieser (wenigstens im Moment) nicht wirklich weiter.
Ich muss dazu vielleicht etwas ausholen und den Vorgang beschreiben, welcher mich zum Problem geführt hat.
Am Anfang steht Excel File mit einer table. Diese table wird mittels Power Automate in eine SharePoint Online Liste geschrieben.
In einem anderen Excel File werden die Daten der SharePoint Liste wieder mit einer PowerQuery Abfrage eingelesen und stehen somit wieder in einer table, auf welche ich eben mit dem erwähnten wverweis zugreifen möchte. Der Befehl führt eben zu dem #NV und nach langem Suchen, habe ich letztendlich herausgefunden, dass in der abgefragten table in allen Zellen ein tab steht.
Interessanterweise ist es aber so, dass in der table sämtliche Zellwerte linksbündig angezeigt werden. Klicke ich dann bei denjenigen Zellen welche eine Zahl enthalten nicht auf sondern in die Zelle, dann springen die Zahlen nach rechts (ohne dass ich ausser dem Klick in die Zelle etwas anderes mache)
Noch verwirrender (wenigstens für mich) ist die Tatsache, dass die Zellformatierungen danach erhalten bleiben. Ich meine damit, dass diejenigen Zellen in welche ich wie beschrieben einmal reingeklickt habe, auch nach einem reload der Power Query Abfrage erhalten bleiben.
Hast Du vielleicht eine Erklärung für dieses Verhalten?
Wieso und wann wurden die Tabs in die Zellen geschrieben und gibt keine Möglichkeit dies zu beeinflussen?
Bezugnehmend auf Deine vorherige Antwort ist es aber sicherlich schon so, dass man solche Phänomene auch mit der besten Schulung nicht abwenden kann ☹
Würde mich auf jeden Fall darüber freuen, wenn Du eine Idee zu meinem neuen Problem hättest
Lieber Gruss
Hallo Herby,
das Problem ist mir und vielen anderen bekannt – ich würde es nicht als Anomalie, sondern als Bug von Excel bezeichnen.
Wirf mal einen Blick in das PDF in
https://www.compurem.de/buecherdownloads/Zahlenformate.zip
– dort beschreibe ich mehrere Lösungen (mein Liebling ist Daten / Text in Spalten) und auch, wie dieses Phänomen zustande kommt.
Liebe Grüße
Rene
Hallo Rene
Danke für die abermals hilfreiche Unterstützung
Mein Problem schein aber irgendwie anders gelagert zu sein und entgegen meiner
vorherigen Problemschilderung ist es leider nicht so, dass der Fehler mit einem
Klick in eine der betroffenen Zellen «nachaltig» gelöst wird.
Zur besseren Veranschaulichung habe ich eine Kopie der Tabelle erstell, welche
auf der PQ Abfrage beruht. Am Bsp der Zelle B2 kannst Du sehen, dass der
Zellwert nach einem Klick in die Zelle, nach rechts gesprungen ist.
Sobald ich das bei irgend einer benötigten Zelle mache, welche einen Zahlenwert
enthält, springen die Werte nach rechts und die Formeln mit den darauf
referenzierenden Zellen, funktionieren.
Wenn ich hingegen die PQ Abfrage aktualisiere, springen die Zahlen wieder nach
links und die Formeln bringen den #NV
D.h die PQ Abfrage erzeugt die falschen Daten und dabei spielt es überhaupt keine Rolle, wie die Zellen formatiert sind.
Die Spalten der Daten Quelle (ShareListe) sind ausnahmslos als standard
formatiert und dies lässt sich auch nicht ändern, da innerhalb einer Spalte
unterschiedliche Daten vorhanden sind.
Wie bei Excel gibt es beim PQ unter Transformieren/Bereinigen die Trim
Funktion, mit welcher eigentlich ein tab aus einer Zelle entfernt werden
sollte.
Aber bis dato ist mir dies damit nicht gelungen
Das Problem muss beim erzeugen der Tabelle gelöst werden, da die Daten
dynamisch sind und laufend aktualisiert werden. Oder anders ausgedrückt, eine
neue Abfrage würde die vormals vorgenommenen Korrekturen mir den Daten
überschreiben.
Das File Servicekatalog Quelldaten dient als Datenquelle, das heisst wenn sich
irgendwelche Daten vom Servicekatalog geändert haben, werden diese dort
eingepflegt. Eine Flow schreibt die Daten in die SharePoint Liste, welche dann
wie PQ Abfrage von überall in eine Servicekatalog.xlsx gelesen werden können.
Die Quelldatei hat das Problem auf jeden Fall nicht, d.h entweder auf dem
SharePoint oder bei anschliessenden PQ Abfrage wird ein problematischer tab
angehängt ☹
Vielleicht mache ich einen Denkfehler und/oder Du hast eine Idee, was ich
ändern muss
Lieber Gruss
Hallo Herby,
Das Problem ist Folgendes:
In einer Spalten stehen Zahlen und Texte.
Wird diese Liste nach PowerQuery „gezogen“ und dort der Typ nicht explizit angepasst, so bleiben die Zahlen Zahlen (rechtsbündig) und die Texte Texte.
Verwendet man in PowerQuery jedoch den Datentyp „Text“, dann „schiebt“ Excel unter diese Zahlen ein Textformat (das so nicht sichtbar ist).
Da die Zelle als Standard (oder Zahl) formatiert ist, verschwindet das Textformat beim Editieren (Doppelklick) der Zelle. Andererseits: Nach Aktualisierung von PowerQuery haben wir die gleiche Situation wie am Anfang.
Gegenfrage: Warum MÜSSEN in einer Spalte Zahlen und Texte stehen? Das widerspricht einem Datenbankdenken.
Und: wenn schon Zahlen – dann sollten sie auch Texte bleiben – als Informationen und nicht zum Rechnen verwendet werden.
Liebe Grüße
Rene
Schöne Frage in der letzten Excelschulung:
Wie kann ich es erreichen, dass eine Datenreihe im 15-Minuten-Takt fortgesetzt wird? Beispielsweise für einen Stundenplan. Ich schaue nach:
Erstaunlicherweise lässt der Assistent „Datenreihe“, den man in der Gruppe „Bearbeiten“ in der Registerkarte „Start“ findet, keine Uhrzeiten zu …
Natürlich könnte man es mit einer Formel erreichen:
=A2+15/24/60
Da in der Schulung Anfängerinnen waren, die bislang noch wenig Erfahrung mit Formeln hatten, schlage ich die naheliegende Lösung vor: zwei Startzeiten eintragen, markieren und runterziehen:
Geht auch so!
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:
Schöne Frage: Wie kann man einen transparenten Hintergrund für Datenschnitte einstellen?
Die Antwort: Ich wüsste nicht wie. Sorry!
Wir haben einen Ordner. Nennen wir ihn „Bilanz“. In diesem Ordner liegen zwei Dateien: August.xlsx und September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
='C:\Users\Rene Martin\Documents\Bilanz\[August.xlsx]Tabelle1'!$D$10Beide Dateien werden geschlossen, der Ordner wird umbenannt, beispiesweise in „Bilanz2021“. Das Öffnen und Aktualisieren der Datei funktioniert problemlos.
Wird haben einen Ordner. Nennen wir ihn „Bilanz“. Darin befinden zwei weitere Ordner: „August“ und „September“. Im Verzeichnis „August“ befindet sich eine Datei August.xlsx, im September-Verzeichnis eine Datei mit Namen September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
='C:\Users\Rene Martin\Documents\Bilanz\August\[August.xlsx]Tabelle1'!$D$10Beide Dateien werden geschlossen, der Ordner „August“ wird umbenannt, beispiesweise in „August2021“. Das Öffnen und Aktualisieren der Datei funktioniert JETZt nicht mehr:
Ärgerlich!
Excel nervt nicht immer. Man kann auch schöne Sachen damit machen. Beispielsweise Folgendes, das ich in den Tiefen des Internet gefunden habe:
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:
Das dürfte genügen.
Fürs Erste.
Schöne Frage in der letzten PowerBI-Schulung, auf die ich keine Antwort wusste:
Warum steht bei den Beziehungen in PowerBI eine Kreuzfilterrichtung zur Verfügung:
im Datenmodell in Excel dagegen nicht?
Übrigens: Gute Erklärungen, was Kreizfilterrichtungen sind, finde ich auf:
und auf:
https://docs.microsoft.com/de-de/power-bi/transform-model/desktop-create-and-manage-relationships
Hallo René
anbei die beiden Files (Visio und Excel Tabelle)
Bei der Tabelle handelt es sich um eine Copy aus einem sehr umfangreichen Excel Workbook, welches unter anderem eben die Tabelle produziert, welche die enthaltenen Services (Visio Shapes) steuert. Falls ein Kunden einen Service in einer Ausprägung bekommt, soll das entsprechend Shape auf dem Visio erscheinen.
Ein komischen Phänomen oder Verhalten ist mir bei der Fehlersuche aufgefallen. Wenn ich im Excel File in der Spalte Layer die Formel mit fixen Werten ersetze, dann läuft es irgendwie deutlich besser. Kann es sein, dass Visio im trotz dem vorher benötigten Datenabgleich im Hintergrund noch irgendetwas mit den Daten macht?
Ich habe keine andere Erklärung, wieso es ohne Formeln im Excel in Visio besser funktionieren sollte ???
Wenn ich Deine Bücher zu VBA und Visio Programmierung durchgearbeitet habe, bin ich sicher in der Lage den Visio Update direkt von Excel aus zu steuern
Freundliche Grüsse
Herby
Hallo Herby,
wenn du bei bestimmten Fällen die Variable shapeOnLayer auf True setzt:
If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
shapeOnLayer = True
Elseund dies später abfragst:
If shapeOnLayer = False Then
lyr.Add shp, 0
End Ifmuss 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 = FalseGuten 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
Hi Rene,
hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA
Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.
In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.
https://docs.microsoft.com/en-us/office/vba/api/excel.sortfield
Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.
Hallo Johannes,
ich habe mal ein bisschen gewühlt und probiert:
1. Das SortObjekt existiert – allerdings besitzt es keine Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem Klassenmodul einzutragen:
Public WithEvents SO As so…
2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam, dem Anwender die Symbole wegzunehmen.
3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.
Tja!
Hilft das?
Liebe Grüße
Rene
Sehr geehrter Herr Martin,
mit großem Interesse verfolge ich Ihre Excel-Seminare und mag es sehr von Ihnen neue Dinge zu lernen und mein Wissen zu erweitern. Helfen Sie auch bei speziellen Excel-Problemen? Ich habe hier eine größere Datenbank. Basis sind verschiedene Materialnummer in einer Spalte, welche mehrmals auftreten, da es zu den Materialien mehrere Bestellungen gibt mit unterschiedlichen Konditionen. Mich interessiert pro Materialnummer der Maximalpreis. Ich habe hier an die 80 Materialien und dazu jeweils mehrere Bestellungen. Der Maximalpreis sollte dann in einer zz. Spalte erscheinen. Mit freundlichen Grüßen
Hallo Frau S.,
ich würde eine Pivottabelle erstelle. Gruppieren Sie die Materialnummern und ziehen Sie dann die Preise in das Wertefeld. Ändern Sie in den Wertfeldeinstellungen die Summe in MAX.
Alternative: Wenn Sie Excel in Microsoft 365 haben: mit der Funktion EINDEUTIG erhalten sie die eindeutige Liste der Materialnummern. Mit MAXWENN können Sie das MAX pro ID berechnen lassen. Hilft Ihnen das?
LG :: Rene Martin
Hallo Herr Martin, vielen Dank für die prompte Antwort. Die Pivottabelle ist der Lösung für mich. Entsprechende Seminare dazu stehen noch auf meiner persönlichen Agenda. Für den akuten Fall, haben Sie mir prima geholfen. Grüße,
Outlook-Schulung. Eine Teilnehmerin sagt, dass sie in Outlook über Ansicht den Aufgabenbereich „Aufgaben“ eingebunden hat. Allerdings werden die Aufgaben nur in diesem Aufgabenbereich angezeigt, wenn sie nach Outlook Heute (also auf ihr Postfach) wechselt.
Die Ursache ist schnell gefunden und erklärt: es gibt zwei Arten von Aufgaben: solche, die man selbst erstellt (sie heißen Aufgaben) und Mails, die man zur Nachverfolgung kennzeichnet (auf Wiedervorlage legt).
Damit die zwei unterschiedlichen Aufgaben zusammengefasst sind, kann man die Aufgaben von der Aufgabenliste in die Aufgaben schieben.
Verwirrende Begrifflichkeiten!
Hallo Rene,
einen Tag Freizeit vom Bauunternehmen – und ich habe mich mal Deinem Hinweis zum „Solver“ gewidmet.
Prima Sache.
Nun könnte ich das vielleicht auch gebrauchen und habe mal nachgeschaut.
Leider gelang es mir nicht den Solver ins VBA unterzubringen….
Hast Du eine Idee, was ich vergessen haben könnte?
Mein Office ist 2007, mein Visio 2010.
Der Solver läuft im Excel.
Anbei die Meldungen beim Versuch, den Verweis einzustellen.
Was sagt der Profi??
Hallo Wolfgang,
du musst den Solver zu den AddIns hinzufügen und dann über Extras / Verweise einbinden. Dann stehen dir die Befehle zur Verfügung:
Schau mal auf:
https://peltiertech.com/Excel/SolverVBA.html
Liebe Grüße
Rene
Hallo Rene,
danke noch einmal für Deinen Hinweis!,
allerdings gelingt mit solch ein Bild (siehe Deine EMail „Verweise VBAProject“ ) nicht.
Da kommt kein „Solver“ vor. Und das Laden der „SOLVER32.DLL funktioniert zwar, aber Visio kann damit nichts anfangen (wie in meiner ersten Mail) schon geschrieben.
Nun habe ich ein 64-Bit -System. Leider ist aus der kargen Fehlermeldung die Ursache, dass Visio die SOLVER32.DLL nicht mag, nicht zu erklären.
Und eine SOLVER64.DLL gibt es wohl nicht (soweit ich meine Suche auch ausdehnte).
Nun werde ich mich wohl mit der nächsten Microsoft-Unzulänglichkeit auch zufrieden geben müssen.
So ein großer „Laden“ und soviel Mangel (es gibt ja auch so viel Versionen…)
Vergangenheit: Hätte ich im Steuerungsbereich meinen Kunden soviel „Nichtfunktion“ zugemutet – ich glaube, da wäre ich arm dran…
Liebe Grüße
(und möglichst stressfreie Spaziergänge…)
Wolfgang
Hallo Wolfgang,
ich habe in Excel den Solver eingebunden. Mit dem Makrorekorder aufgezeichnet. Dann kannte Excel ihn (in den Verweisen)
Von Visio aus habe ich das (noch) ausprobiert …
Liebe Grüße
Rene
Hallo Wolfgang,
ich habe noch einmal nachgeschaut: du verwendest eine andere Visio-Version als Excel. Kann das die Ursache sein?
Zeichne das Teilchen doch einmal in Excel mit dem Makrorekorder auf. Läuft?
Dann wandle die Befehle in Late Binding (Object) um. Läuft?
Und dann von Visio!
Was passiert?
Liebe Grüße Rene
… und ich kann es nicht lassen, zu mailen, mit den Infos:
Frage: Kann man noch prüfen
wenn ODER(Prüfung auf TEXT(J9), Prüfung auf TEXT(L9), Prüfung auf TEXT(N9)): „“
D.h.: Wenn mindestens eine der Zellen Text beinhaltet >>> Ergebnis der Zelle: Nix drinnen
Ich habe schon ein wenig schlechtes Gewissen …
Ich maile noch ein DANKESCHÖN & Gruß
Jürgen
Hallo Jürgen,
deine Bedingungen werden mit
=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);"");"")abgefangen. Die Funktion MITTELWERT übergeht (ebenso wie SUMME, ANZAHL, MAX, …) Texte. Senn du das explizit abfangen willst, dann beispielsweise so:
=WENNFEHLER(WENN(ANZAHL(J9;L9;N9)<ANZAHL2(J9;L9;N9);"";WENN(ANZAHL(J9;L9;N9)>1;MITTELWERT(J9;L9;N9);""));"")Liebe Grüße
Rene
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Die Werte werden kummuliert. In der ersten Zelle steht
=WENN(F15<>"";F15;"")Darunter befindet sich die Formel:
=WENN(G15<>"";G15+F16;"")So hatte ich es vor vielen Jahren auch gemacht. Inwzischen löse ich das Problem der kummulierten Werte mit einer Formel, die ich nach unten ziehe:
=SUMME($F$15:F15)Fazit: Lehrer und Lehrerinnen sollten regelmäßig ihre Übungsaufgaben durchsehen und überprüfen, ob es bessere Lösungestechniken, neue Funktionen gibt, mit denen man Aufgaben eleganer lösen kann und testen, ob sie einige Bedingungen vergessen haben.
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Der prozentuale Anteil wird berechnet:
=WENN(UND(A15<>"";B15<>"");D15*100/$D$25;"")Okay, mit der Funktion
UND(A15<>"";B15<>"")wird überprüft, ob die Zellen der Spalte A und B gefüllt sind. Eine hübsche Übung, um UND und WENN zu üben.
Dennoch: WENNFEHLER wäre sicherlich besser gewesen, um ALLE Fehler abzufangen.
Aber noch mehr irritiert mich die Berechnung
D15*100/$D$25Warum wird mit 100 multipliziert? Ich hätte den Anteil von einer Dezimalzahl in eine Prozentzahl formatiert. Und damit weitergerechnet.
Wäre das nicht eher im Sinne von Excel gewesen?
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Ich komme bei der Verwendung der Funktion RANG ins Grübeln:
=RANG(B15;$B$15:$B$24;0)Wenn man diese Funktion in Excel eintippt, sieht man vor dem FUnktionsnamen ein Ausrufezeichen. Das bedeutet, dass diese Funktion durch andere erstetzt wurde und aus Kompatibilitätsgründen noch zur Verfügung steht. Richtig: in Excel 2010 wurd diese Funktion RANG durch RANG.GLEICH und RANG.MITTELW abgelöst. Man findet RANG nun in der Kategorie Kompatibilität:
Das Problem bei der Funktion Rang ist die Antwort auf die Frage, welche Zahl weisen wir zwei gleich großen Werten zu? Also: wenn es den größten Werte zwei Mal gibt? Zählen wir dann 1; 1; 3 (so rechnet RANG.GLEICH und RANG) oder 1,5; 1,5; 3 – so rechnet RANG.MITTELW.
Ich probiere es aus, ändere zwei Werte so, dass sie gleich groß sind. Das Ergebnis: Fehler in der weiteren Berechnung, die nicht abgefangen wurden:
Fazit: vielleicht hätte der Lehrer oder die Lehrerin in den letzten zehn Jahren einmal das Beispiel neu nachrechnen sollen und auf Konsistenz prüfen sollen. Und vor allem: prüfen sollen, ob es inzwischen nicht neue, bessere Funktionen zur Lösung des von ihm oder ihr gestellten Problems gibt.
Excelschulung. Eine junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
In Spalte A stehen Lieferantennummern, in Spalte B Umsatzzahlen. In Spalte C wird der Rang berechnet:
=RANG(B15;$B$15:$B$24;0)Doch schon Spalte D lässt mich stutzen:
=INDEX($A$15:$B$24;(VERGLEICH(1;$C$15:$C$24;0));2)Darunter befindet sich die Formel:
=INDEX($A$15:$B$24;(VERGLEICH(2;$C$15:$C$24;0));2)
Danach Nummer 3, Nummer 4, … Uff! Kennt der Lehrer oder die Lehrerin nicht die Funktion ZEILE? Ich stutze und wundere mich …
#####
Hallo Rene,
kannst du vielleicht auch deinen Verbesserungsvorschlag für die Formel dazu schreiben. Mir erschließt sich grad nämlich nicht, wie man die Funktion ZEILE hier einbauen soll. Wahrscheinlich steh ich nur auf dem Schlauch…
Lieber Anonymous,
die Funktion ZEILE hat zwei „Gesichter“:
=ZEILE()liefert die Zeilennummer der aktuellen Zelle. Steht also diese Funktion in C7,liefert =ZEILE() die Zahl 7. Beim Herunterziehen erhalte ich 8, 9, 10, …
Die Funktion der Lehrerin oder des Lehrers
VERGLEICH(1;$C$15:$C$24;0)sucht den Wert 1 in der Rang-Spalte, also die Zeile, die den größten Wert enthält.
VERGLEICH(2;$C$15:$C$24;0)sucht den zweitgrößten Wert.
Ich bin sicher, dass die Lehrerin oder der Lehrer die Werte 1, 2, 3, 4, … per Hand getippt hat. Da die Formel in D15 stand, hätte man auch schreiben (und herunterziehen) können:
=INDEX($A$15:$B$24;(VERGLEICH(ZEILE()-14;$C$15:$C$24;0));2)Auch das ist noch etwas umständlich. Man kann mit ZEILE einen Bezug auf eine Zelle herstellen und von dieser Zeile die Zeilennummer ermitteln. Also:
=ZEILE(A1)liefert 1. Und beim Herunterziehen erhält man die Werte1, 2, 3, 4, die den größten, zweitgrößten, drittgrößten, … Wert liefert. Also:
=INDEX($A$15:$B$24;(VERGLEICH(ZEILE(A1);$C$15:$C$24;0));2)Excelschulung. EIne junge Teilnehmerin. Azubi.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:
Ich sehe folgende Formel:
=WENN(A17="";"";SVERWEIS(A17;Leistung;2))Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?
Sicherlich dient diese Formel WENN zum Üben …
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.
Hallo Herr Martin,
ich habe eine Frage zum Liniendiagramm, leider stimmen meine Werte nicht und ich bekomme es nicht hin ohne das die Linien aus meinem Diagramm verschwinden.
Vielleicht könne sie mir spontan helfen.
Danke
Hallo Frau D.,
Sie müssen die Reihe mit den „großen Zahlen“, also Jergl, Michal, Hans und Veitli auf eine Sekundärachse legen. Und dann möglicherweise die Skalierung der beiden Achsen anpassen.
Hallo Rene!
Hoffe, dass du, deine Familie und dein Umfeld alle gesund seid.
Du hast mir vor einiger Zeit geholfen mehrere Belegnummern in eine Zelle zu schreiben. Nun wollte ich auch das Datum (auch mehrere) auf gleiche Weise anzeigen lassen. Herausgekommen sind dann die Zahl(en) vom Datum. Wenn nur eine Zahl (=Datum) angezeigt wurde konnte ich das Datumformat. Wenn nur ein Zahlenwert vorhanden ist, habe ich, wie gelernt, die Formel mit 1 multipliziert und dann das Datumformat angewendet. Meine Vermutung ist, dass man die Formel um eine Formatierungsformel ergänzt. Videos über die allgemeine Formatierung mit Formeln in der Zelle habe ich leider nicht gefunden. Bitte um Lösung.
Vielen Dank, Peter
Hallo Peter, die Formel muss statt
=TEXTVERKETTEN(", ";WAHR; FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0")) lauten:
=TEXTVERKETTEN(", ";WAHR; TEXT(FILTER(tbl_KF_Kosten[Datum];tbl_KF_Kosten[Pferd]=E30;"0");"TT.MM.JJJJ")) Die Funktion TEXT formatiert eine Zahl (und damit auch ein Datum).
LG Rene
Moin René
Kurze Frage:
Ich suche gerade nach einer Lösung, finde aber nichts. Vielleicht weisst du was dazu.
Wenn ich alle Spalten ausblenden, so wird der „fehlende“ Bereich mit einem grauen Hintergrund ersetzt.
Frage: wie kann ich die Farbe anpassen? Also statt grau bspw. weiss?
GLG Hp
schöne Frage, HP. – m.W. kann man DAS nicht anpassen.
Die Farbe wird über das Office-Design festgelegt, das man über Datei / Optionen / Allgemein festlegen kann.
MICH hat es bislang noch nie gestört …
Hätten Sie es gewusst? Gestern kam eine Frage in der Excelschulung … ich war mir nicht sicher. Also: schnell ausprobieren!
Kann man in einem geschützten Tabellenblatt Kommentare und Notizen eintragen, lautete die interessante Frage?
Ich probiere: Zellen nicht sperren (über den Dialog „Zellen formatieren“ und anschließend Blatt schützen:
Das Ergebnis:
Man kann Kommentare eintragen – keine Notizen!
Noch ein Versuch: ich schütze das Blatt und aktiviere die Option „Objekte bearbeiten“:
DANN kann man auch Notizen einfügen:
Haben Sie es gewusst? Ich nicht …
Guten Tag René
Der Kunde hat folgende Fehlermeldung
Hast du eine Vermutung was das sein könnte?
Bei mir ist es gelaufen.
LG Tanja
##########
ja, Tanja,
der Kunde hat ein sehr „altes“ Excel (Excel 2013? Excel 2016?). Ich verwende die Funktion TEXTKETTE (in VBA: CONCAT). Genauer: mit dem Befehl
Do Until Len(Application.WorksheetFunction.Concat(xlBlatt.Range(xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 0), xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 8)))) = 0
lngAnzahl = lngAnzahl + 1
Loop ' -- wie viele Zeilen sind gefüllt (wird in der Variable lngAnzahl gespeichert)
überprüfe ich, in wie vielen Zeilen der Liste etwas steht, beziehungsweise, ich suche die Zeile, in der die ersten neun Spalten leer sind oder ob die Formeln, die darin stehen, „“ ergeben.
Ich habe es geändert. Könntest du ihnen bitte diese Version schicken
Liebe Grüße
Rene
Ich bin verblüfft. Und irritiert.
Nicht nur Excel nervt – manchmal auch andere. Gestern hat mich Outlook erstaunt.
Outlook-Schulung. Wir sortieren unsere Mails nach Kategorien und nach den Fähnchen zum Nachverfolgen, indem ich auf die Überschrift klicke:
Eine Teilnehmerin möchte nach beidem sortieren: zuerst nach Kennzeichnungsstatus und anschließend nach Kategorie. Kein Problem: Über die Registerkarte Ansicht öffne ich die Ansichtseinstellungen und klicke dort auf die Schaltfläche „Sortieren“. Ich sortiere nach „Kennzeichnungsstatus“ und anschließend? Ich finde die „Kategorien“ nicht.
Okay, in den „verfügbaren Feldern“ gibt es „Alle Dokumentfelder“. Und dort „Kategorien“. Aber das führt zur Frage, ob ich eine neue Spalte hinzufügen möchte:
Ein Klick auf „Ja“ und ich habe eine weitere leere Kategorienspalte.
Erstaunlich: beim Suchen-Dialog gibt es weder eine Liste „Alle Felder“ noch kann ich die Kategien finden, um danach zu sortieren.
Und: ein erster Klick auf die Überschrift „Kategorie“ und ein zweiter auf „Kennzeichnungsstatus“ bewirkt nicht das Gewünschte: Outlook hat eigene Sortiervorstellungen.
Habe ich etwas übersehen? Oder nervt Outlook auch? Manchmal.
Erstaunlich. Das ist mir noch nie aufgefallen. War das schon immer?
Erstellt man in Excel eine Datenüberprüfung mit einer Liste und trägt die einzelnen Werte in das Textfeld als Quelle ein
kann man die Texte gefolgt von einem Leerzeichen eingeben:
Verwendet man dagegen als Liste die Werte einiger Zellen, die ausgewählt werden:
kann man die Texte NICHT gefolgt von einem Leerzeichen eintragen. SO sollte es doch eigentlich sein, oder?
Ob ich mir man kurz die Access-Datenbank ansehen könne, lautete die Aufgabe.
Mein erster Blick fiel in das Beziehungsfenster:
Der Code war zwar besser strukturiert, aber ich habe dankend abgelehnt, die Datenbank weiter zu pflegen …
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:
Excelstammtisch. Hartmut zeigt, dass man das Datenmodell von Excel nach PowerBI importieren kann.
Ich frage, ob er wisse, wann das zu Problemen führt. Und zeige eine Datei:
Darin befinden sich Tabellen, die ins Datenmodell geladen wurden. Mit Hilfe des Datenmodells wurde eine Pivottabelle erstellt. Die Tabellen wurden mit Measures angereichert und sind untereinander verknüpft.
Nun will ich diese Datei (genauer: die Daten, Verknüpfungen und Measures) nach PowerBI importieren:
Ich erhalte eine Fehlermeldung – fast nichts wird importiert:
Wir machen uns auf die Suche – Hartmut wird fündig. Man darf nicht die Daten in Tabellen in der Arbeitsmappe halten und diese ins Datenmodell laden, sondern man muss sie mit PowerQuery importieren. So:
Diese Daten werden nun ins Datenmodell geladen – dort kann man sie verknüpfen
und mit Measures anreichern:
Das Ergebnis:
Ein erneuter Import nach PowerBI Desktop:
Klappt!
Ein Dankeschön an Hartmut Hilbich für das Suchen und Auffinden der Lösung des Importproblems. Hartmut schreibt dazu:
„Das Problem bestand hier (besteht!) darin, dass PBID das Datenmodell selbst sehr wohl importiert, aber nicht gleichzeitig auch die Quelltabellen!
Ich habe die Quelltabelle mit PQ abgefragt und das PP-Modell exemplarisch mit 2 Measures versehen. Der Import in PBID funktioniert dann einwandfrei!
Mein Fazit: Es ist nicht ratsam, die Quelldaten physikalisch gemeinsam mit dem PP-Modell zu speichern. Also entweder die Daten direkt mit PP abfragen, oder aber (besser) mit PQ abfragen. Was also innerhalb von PP kein Problem ist, wird dann aber eines beim Import in PBID.
Good to know!“
So schwierig kann das wohl nicht sein, dachte ich. Und probierte es. Allerdings: die Lösung des Problems war doch komplizierter als gedacht.
Vor einigen Jahren hatte ich die Aufgabe in einer sehr großen Excelliste (zirka 60.000 Zeilen) die Daten „zu putzen“. Mitarbeiterinnen und Mitarbeiter hatten an unterschiedlichen Stellen in einer Spalte Informationen eingetragen – allerdings mehrere Informationen getrennt durch Trennzeichen. Durch verschiedene Trennzeichen – mal ein „/“, mal ein Semikolon, mal ein „:::“, mal ein „-„:
Ich habe damals einige VBA-Makros geschrieben, um die Daten „zu putzen“. Ich frage mich, ob man sie mit PowerQuery bereinigen kann. Man kann!
Ich erstelle eine Liste der Trennzeichen:
Ich importiere die Daten und trenne die Liste „hart“ an einem Zeichen:
Der Befehl
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"PK.1", "PK.2", "PK.3"})trennt die Spalte. Wie kann man alle Trennzeichen verwenden? Ich importiere die Trennzeichenliste und wandle sie in über Transformieren / In Liste konvertieren in eine Liste um:
Kann SplitTextByDelimiter meine tbl_Trennzeichen verarbeiten? Nein!
Ich gehe auf die Suche:
SplitTextByAnyDelimiter kann die Liste verarbeiten:
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), {"PK.1", "PK.2", "PK.3"})
Jedoch: {„PK.1“, „PK.2“, „PK.3“} legt fest, dass DREI neue Spalten geliefert werden mit den Namen „PK.1“, „PK.2“ und „PK.3“. Ich probiere aus:
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), {"PK.1", "PK.2", "PK.3", "PK.98", "PK.99"})Klappt! Ich erhalte weitere Spalten:
Allerdings: Wie viele Spalten entstehen denn? Ich versuche es ohne den letzten Parameter:
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen))Und erhalte EINE neue Spalte:
Schlecht! Ich schaue den Parameter genauer an – er heißt:
columnNamesOrNumber
Also versuche ich eine Zahl. Ich beginne bei 99:
Der linke Teil sieht vielversprechend aus:
– der rechte nicht:
Ich überlege: ich muss berechnen wie viele neue Spalten erzeugt werden. Ich muss berechnen wie oft die Trennzeichen der Liste tbl_Trennzeichen in jedem der Texte vorkommt.
Leider stellt PowerQuery keine Funktion zur Verfügung, mit deren Hilfe man die Anzahl der vorkommenden Zeichen in einer anderen Zeichenkette ermitteln kann. So etwas berechne ich (auch in Excel) immer wie folgt:
Länge(Zeichenkette) - Länge(Zeichenkette ohne gesuchten Zeichen)oder
Länge(Zeichenkette) - Länge(Ersetze(Zeichenkette; gesuchten Zeichen))Eine PowerQuery-Abfrage muss her:
(Text.Length(Text) -
Text.Length(Text.Replace(Text -Ich überlege: minus jedes Element der Liste. Also genauer:
(Text as text) =>
List.Accumulate(
tbl_Trennzeichen,
0,
(state, current) =>
state +
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
) Ich muss die Anzahl kumulieren. Der Befehl List.Accumulate tut gute Dienste. Er möchte eine Liste haben (tbl_Trennzeichen), einen Beginn (0) und eine Funktion. Diese Funktion erhält zwei Teile:
(state, current)Die Variable state „merkt“ sich die Zahl, current greift auf jede Zeile zu. Allerdings darf ich nicht einfach die Differenz aus Länge vorher und Länge nachher bilden:
Text.Length(Text) -
Text.Length(Text.Replace(Text, current, ""))sondern muss durch die Länge teilen. Also wenn ich von der Länge
Hallo:::ich:::bin:::es:::wieder31 die ::: entferne:
Halloichbineswiederbleiben 19 Zeichen. Differenz = 12. Da aber nur vier Mal das ::: auftaucht, muss ich es noch durch die Länge teilen, also:
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
/ Text.Length(current)Und schließlich: Wenn ich in ich-will-das zwei Bindestriche finde, erhalte ich nach dem Trennen DREI Teile.
Die komplette Funktion, die ich fxAnzahlTrennzeichen nenne sieht dann so aus:
(Text as text) =>
List.Accumulate(
tbl_Trennzeichen,
0,
(state, current) =>
state +
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
/ Text.Length(current)
) + 1Und kann verwendet werden:
Von dieser Spalte wird das Maximum berechnet und über ein Drilldown als Zahl „gespeichert“ (MAXZeichen):
Ein Klick auf fx und ein Bezug wird zu einem vorhergehenden Schritt hergestellt:
= #"Geänderter Typ"Dort wird getrennt:
= Table.SplitColumn(Benutzerdefiniert1, "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), MAXZeichen )
Und in Excel:
Probe aufs Exempel – ich trage in der Liste den folgenden Text ein:
A-B-C-D-E-F-G-H-Iund aktualisiere:
Klappt!
Witzig – noch während des Schreibens und Zwischenspeichern erhalte ich die Info, dass Oz du Soleil (zeitgleich mit mir) auch eine Lösung gepostet hat:
https://www.youtube.com/watch?v=EiHDsZxJ_EI
Er beschreitet einen ganz anderen Weg …
Kennen Sie den Median? Die Excel-Funktion MEDIAN?
Gemeint ist – umgangssprachlich – der mittlere Wert einer Datenreihe. Hat man zwei mittlere Werte (bei einer geraden Anzahl Elemente), berechnet sich der Median als Durchschnitt der beiden mittleren Werte.
Wer braucht denn so etwas?
Stellen Sie sich vor, sie haben eine Basketballmannschaft, die aus fünf Spielern besteht: vier recht kleinen und einem sehr langen Spieler. Der Mittelwert würde das Bild „verzerren“, weil der Ausreißer den Durchschnitt nach oben verschiebt. Der Median gibt dagegen ein besseres Bild der Mitte, weil er resistent gegenüber Ausreißern ist:
Median – eine statistische Größe also. Nun finde ich in einer Berechnung von Transportkosten folgende Formel:
=WENN(G15="";"";WENN(UND(ODER(MAX(Logistikdaten!I38:K38)>120;MEDIAN(Logistikdaten!I38:K38)>60;MIN(Logistikdaten!I38:K38)>60);$G$15<=440);"Ja";"Nein"))Verwundert reibe ich die Augen? Warum werden die Logistikkosten mit einem Median berechnet?
Dann verstehe ich: es wird Bezug genommen auf die drei Werte Länge, Breite und Höhe. Von diesen drei Werten wird der größte Wert (MAX), der kleinste Wert (MIN) und der mittlere Wert (MEDIAN) berechnet. Sehr clever!
Hallo Herr Martin,
das sieht erst mal gut aus.
Auch das löschen scheint schneller zu funktionieren.
Werde die Version zum Testen meinem Bekannten schicken.
Wer einer ein Fehler findet dann er.
Habe gesehen, dass man das Konto „NEU“ löschen kann. Habe ich aus Versehen gemacht.
Danach kann man keine Konto mehr hinzufügen, da die Schaltfläche dann ausgegraut ist.
Kann man das noch verbessern?
Hallo Herr L.
den Ändern-Button hatten Sie dynamisch bei „(Neu)“ aktiviert und deaktiviert – den Löschen-Button wohl vergessen. Ist auch drin.
Hallo Herr Martin,
Sie haben vollkommen Recht.
Der Fehler war vorher schon da. Ist mir nie aufgefallen, da ich nie Konten bei mir selbst gelöscht hatte.
#####
Fazit: Wie mein Lorenz: ich solle auch einen Blog aufmachen mit dem Titel kunde-nervt …
Hallo Herr Martin,
habe noch einen Fehler gefunden.
„Kompilierungsfehler im ausgeblendeten Modul. Dieser Fehler tritt häufig auf, wenn der Code nicht mehr mit der Version, Plattform oder Architektur dieser Anwendung kompatibel ist. Klicken Sie auf „Hilfe“, um Informationen dazu zu erhalten, wie Sie diesen Fehler beheben können.“
Der Fehler entsteht, wenn ich im Code im Modul basKonstanten die Versionsnummer ändern will.
Nach Änderung und Neustart der Tabelle kommt o.g. Fehler. Danach kann kein Dashboard mehr ohne Fehler aufgerufen werden!
Wenn das so wieder wie vorher funktioniert, dann wäre es perfekt.
Mit freundlichen Grüßen
Hallo Herr L.,
der Grund des Fehlers ist Folgender:
Das Datum war als Datum definiert in der Form #Monat/Tag/Jahr#. Ich weiß nicht, was Sie eingetragen haben – aber vielleicht nicht als Datum. Da dieses Datum nur einmal als Text verwendet und angezeigt wird, habe ich einen Text daraus gemacht (somit wird nicht mehr impliziert konvertiert – hier lief wohl etwas schief).
Public Const p_cdatAppStand As String = "08.07.2021"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!ZellbezugPrima! 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'!W117Erstaunlich! 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.
Liebe Grüße
René Martin
Letzte Woche in der PowerBI-Schulung. Wir schauen uns einige der DAX-Funktionen an. Beispielsweise CONCATENATE.
Es folgt die Frage, warum man mit der Excel-Funktion VERKETTEN ( CONCATENATE) mehrere Texte verketten kann:
Dagegen in DAX:
Zwei Texte sind kein Problem. Jedoch bei drei Texten:
=CONCATENATE(tbl_ottos_mops[ottos];" ";tbl_ottos_mops[mops])
Also verketten wir in DAX mehrere Texte mit dem et-Zeichen „&“. Das funktioniert!
=tbl_ottos_mops[ottos]&" "&tbl_ottos_mops[mops]&" "&tbl_ottos_mops[Spalte1]&" "&tbl_ottos_mops[Spalte2]
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.
Die Formel
=WENN(UND(ZÄHLENWENN($B$2:$P$13;A21)>0;WOCHENTAG(A21;2)<=5);"F";"")prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):
Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:
Dazu verwende ich die Formel:
=WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A21;-WOCHENTAG(A21;3);1;7;1);"F")>0;"F";"")Der Rest ist trivial, oder? Man sucht ein Datum in der ersten Datumsspalte und prüft, ob in der Wochenspalte ein „F“ steht oder nicht:
=WENN(SVERWEIS(H20;A21:C5501;3;FALSCH)="F";25%;0%)
Beispiel: der 01. Mai 2021 fiel auf einen Samstag, also ist für den 29. April kein Zuschlag erforderlich.
Pfingstmontag war in diesem Jahr am 24. Mai – also muss für den 26. Mai 2021 ein Zuschlag bezahlt werden.
Eine hübsche Knobelaufgabe – ich habe ein bisschen überlegen müssen.
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?
Eine schöne Frage, die gestern in der Excel-Schulung kam. Ich zeigte wie man Listen sortiert und dass man auch nach Farben sortieren kann.
Dann zeigte und erläuterte ich den Autofilter. Auch dort kann man nach Farben filtern:
Es kam die Frage, warum man nicht nach mehrere Farben filtern könne – schließlich könne man auch nach mehreren Farben sortieren:
Stimmt – warum eigentlich nicht? Ich weiß es nicht!
Hallo Herr Martin,
könnten Sie bitte noch berechnen, ob das Paket Sperrgut ist.
Ich frage zurück: „Woran erkenne ich, ob es Sperrgut ist?“ Ich erhalte die schriftliche Antwort:
„Länge und Breite dürfen nicht größer als 2,40 Meter sein.“
Ich überlege:
Länge + Breite < 2,4?
Länge < 2,4 UND Breite < 2,4?
Länge < 2,4 ODER Breite < 2,4?
Was ist mit der Höhe? – Kann man das Paket nicht drehen?
Ich warte noch auf die Antwort.
Wahrscheinlich überlegen sie selbst …
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:
Nachtrag 2 zur letzten Verion:
Folgendes wurde getestet:
Konto anlegen und löschen funktioniert soweit, bis auf das Problem, was wir schon mal hatten und gelöst wurde (Numerischer Kontoname z.B. 01 -> wird darauf 1 generiert!). Das hatten Sie bereits schon super umgesetzt.
Also formatiere ich die Spalte mit den Kontonummern erneut als Text. Mal sehen, wann Herr L. es wieder „kaputt macht“.
Ich erhalte eine Excellisten mit Preisen, in der ich einige Formeln anpassen soll. Ich wundere mich über die zentrierten Zahlen:
Ich verbreitere die Spalte(n):
Mein Blick fällt die Zentrierung. Ich wechsle die Ausrichtung zu linksbündig:
Auch sehr merkwürdig.
Dann verstehe ich: einige Zellen sich als Währung formatiert, einige als Buchhaltung. Sehr originell!
Herr L. schickt mir ein Video, das er erstellt hat, damit ich besser erkenne, woran der Fehler liegen könne, der in Excel auftritt. Ich öffne das Video und erhalte folgende Fehlermeldung:
Wiedergabe nicht möglich. Schaffen Sie den Inhalt erneut an.
Das Verb „anschaffen“ amüsiert mich.
Amüsant.
Ich öffne eine Testdatei, die mir ein Anwender zugeschickt hat, und wundere mich, wo das Menüband (das Ribbon) ist.
Dann fällt es mir ein: es gab da doch … Richtig:
Es gab einen Excel 4.0-Makrobefehl, mit dem man die Symbolleiste ausblenden konnte. Dieser funktioniert im aktuellen Excel noch immer und blendet hier das Menüband aus. Der Befehl lautet:
Application.ExecuteExcel4Macro "Show.Toolbar(""Ribbon"", False)"Und: nicht vergessen: Nach Beendigung bitte wieder einschalten!
Hallo Herr Martin,
mein Bekannter hat sich die neue Version angeschaut.
Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.
In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.
Hallo Herr L.,
öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.
Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.
Sie erhalten den Fehler.
Heißt: DIESER Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.
Woher kommt er?
Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:
Sie greifen auf den VALUE dieser Zelle zu – das knallt!
Die Ursache des Fehlers:
In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!
=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.
An dieser Maske dlgKundenverwaltung habe ich nichts geändert.
schöne Grüße
Rene Martin
*) An Gott glauben wir; alle anderen müssen Daten zeigen!
Ich bin erstaunt und amüsiert.
Was ist passiert?
Ich erstelle ein Eingabeformular in Excel für eine Firma. Da mit einem Wert, beispielsweise 7,75 €, weitergerechnet wird, lösche ich den Text „7,75 € (nicht enthalten in Frachtraten)“, der in der Zelle stand, ersetze ihn durch die Zahl 7,75 und formatiere die Zelle mit einem benutzerdefinierten Zahlenformat
0,00" € (nicht enthalten in Frachtraten)"Nach einer Weile stelle ich erstaunt fest, dass der Text als Quickinfo angezeigt wird. Was passiert hier? Was habe ich hier gemacht?
Nein – es liegt keine Notiz und kein Kommentar unter der Zelle.
Nein – es liegt keine Datenüberprüfung mit einer Eingabemeldung unter dem Text.
Nein – es wurde auch kein QuickInfo eines Hyperlinks verwendet:
Erstaunt reibe ich die Augen. Das habe ich selbst gemacht! Aber wie?
Schließlich komme ich hinter des Rätsels Lösung:
Trägt man in Excel in eine Zelle eine Zahl ein, formatiert diese Zahl (beispielsweise als Währung) und verkleinert die Spalte so, dass die formatierte Zahl in der Zelle nicht angezeigt werden kann, wird die formatierte Zahl als Quickinfo angezeigt, wenn sich der Mauszeiger darüber bewegt. Aha!
Wenn ich nun mehrere Zellen markiere und die formatierte Zahl „über die Auswahl zentriere“:
wird die Zahl klar lesbar in den Zellen angezeigt. Da die Zahl aber für die Zelle, in der sie sich befindet, zu „breit“ ist, bleibt das QuickInfo stehen:
Eben! Und so kann ich eine Zahl über mehrere Zellen ausrichten …
… und formatieren, beispielsweise (hier:) mit
0" - Die Antwort auf alle Fragen!"
Und dann habe ich gelacht!
Hallo Herr Martin,
mir ist ein Fehler aufgefallen, der vorher nicht vorhanden war.
Die Excel-Tabelle in Excel 365 funktioniert tadellos, soweit ich getestet habe.
Ein Freund von mir hat leider aus der alten Version die Daten nicht in die neue Version reinbekommen und so hat er sich entschlossen, diese neu einzugeben.
Wenn er ein Konto anlegt – das geht noch.
Dann will er Einzahlungen in das Einzahlformular hinzufügen, dies scheint erst mal nicht zu funktionieren. In der Liste zeigt er nichts an!
Wenn ich mir die Tabelle außerhalb des Formulars anschaue, sind die Daten angelegt – Die Paketnummer wird nicht mehr hochgezählt.
Auch ein speichern und erneuter Start der Tabelle bringt keine Abhilfe.
In der alten Version (bei mir 6.11) funktioniert es noch tadellos. Mein Bekannter setzt Excel 2016 (neuste Updates sind installiert) ein. Bei mir geht es, bei ihm nicht, mit der letzten Version.
#####
Hallo Herr L.,
in Ihrem Programm finde ich die Codezeile:
If rngI.Value = Me.cmbAuswahlKontoAlleInvestments.Value ThenSie 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 ThenNicht aufgepasst. Nicht genau hingeschaut.
Gestern habe ich für den Excelstammtisch einige Dateien vorbereitet, um zu zeigen, was PowerQuery verlangsamt. Ich habe eine Liste mit Dummy-Namen mit 20.000 Datensätzen:
Diese verknüpfe ich mit einer Liste, die zwei Zeilen lang ist:
Das Ergebnis: 28.877 Datensätze
Ein zweiter Blick auf die Liste zeigt, dass einige Zeilen (nicht alle!) nun zwei Mal in der Liste auftauchen:
Nein – an der Verwendung eines Primärschlüssels liegt es nicht – die Zeile
= Table.AddKey(#"Geänderter Typ",{"Geschlecht"}, true)ist nicht der Grund!
Schließlich finde ich des Rätsels Lösung: ich habe beim Verknüpfen der beiden Listen aus Versehen die Fuzzy-Logik aktiviert.
Haken raus – und ich erhalte wieder die ursprünglichen 20.000 Zeilen.
Markus Hahner hat darauf hingewiesen.
Trägt man in Word in einer Tabelle Text ein, verbreitet sich die Spalte, in der der Text steht.
Diese (oft nicht gewünschte) Eigenschaft kann über das Symbol „AutoAnpassen“ der kontextsensitiven Registerkarte „Layout“ ausgeschaltet werden. Dort kann man von der Option „Automatisch an Inhalt anpassen“ an „Feste Spaltenbreite“ umschalten.
Leider zeigt Word nicht an, welche Option aktiviert wurde – keiner der drei Optionen ist mit grauer Farbe unterlegt.
Outlook-Schulung. Die Teilnehmerin möchte den Lesebereich ausschalten. Ich zeige ihr, dass man das im Menü Ansicht desaktivieren kann:
Allerdings: sie möchte den Lesebereich für alle Ordner, Unterordner, Unterunterordner ausschalten …
Ich wüsste nicht, wo und wie man DAS deaktivieren kann.
Hübsche Frage in der letzten Outlookschulung: Wie kann ich die Schrift in der Ordner im Ordnerbereich vergrößern.
Meine Antwort: ich wüsste nicht wie! Man kann die Ordnerschrift nicht ändern, sie nicht farblich unterlegen oder auf andere Art kennzeichnen …
Sehr ärgerlich!
Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:
In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …
Mich beschleicht ein Gedanke …
Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*
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.
Wie oft habe ich es schon gesagt: nicht verbinden! Das bringt nur Ärger!
Richtig: ich erhalte ein Formular mit mehreren verbundenen Zellen – hier: 2 x 2 Zellen sind verbunden.
Ich erstelle einen Verweis auf diese Zelle(n):
#ÜBERLAUF! ist die Folge. War ja klar …
Einen schönen Fronleichnams-Feiertag wünsche ich allen Leserinnen und Lesern meines Blogs – egal, ob Sie heute arbeiten oder raus gehen.
Rene Martin
WORD – Bug in Inhaltsverzeichnis ?
Hallo Rene,
bei MS Word kannst Du sicher auch helfen?
Ich habe in angehängter Datei im Inhaltsverzeichnis den Fall, dass die Seitenzahlen in einigen Überschriften (Brote, Pizza, Gebäck, …) nicht rechtsbündig stehen.
Hast Du eine Idee, woran das liegt?
Danke, Gunnar
klar, Gunnar,
Word: davon lebe ich auch. Normalerweise.
Die Antwort ist denkbar einfach: „Brote“ und „Gebäck“ basieren auf der Formatvorlage „Verzeichnis 2“ und „Verzeichnis 3“. Dort ist ein hängender Einzug von 1,25 cm eingestellt. 1,25 cm heißt um 1,25 cm, nicht auf die Position 1,25 cm. Da diese Wörter kürzer als 1,25 cm springt der Tab auf den gesetzten Einzug.
Die Lösung: Einzug auf 0,5 cm oder 0 setzen. Dann klappt es.
Liebe Grüße
Rene
Fazit: Nicht alles, was nach Bug aussieht, ist auch einer. Manchmal sind auch die Menschen, die vor dem Computer sitzen, die Ursache für Fehler.
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.
Frech. Ordinär. Böse und gemein.
Trotzdem.
Ich habe über den Spruch gelacht, den ich vor einigen Tagen im Internet entdeckt habe:
Einfach nicht aufgepasst. Dabei weiß ich es doch:
Ich erstelle eine Userform in Excel VBA. Darin sollen Werte „nach unten“ weitergegeben werden, wenn ein Kontrollkästchen angeklickt wird. Die Kästchen heißen chkFamilie02, chkFamilie03, chkFamilie04, … chkFamilie12
Ich prüfe, ob das letzte ausgewählt wurde oder ob noch weitere unten ausgewählt wurden:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False Or frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False Or intZeile > 12
Das läuft an die Wand – eine Fehlermeldung ist die Folge. Mein Denkfehler:
wenn intZeile > 12, dann wird das geürft. Beispielsweise: intZeile hat den Wert 13. Allerdings: es wird auch geprüft, ob das Control chkFamilie12 sichtbar ist. Und das gibt es nicht!
OR (und auch AND) in VBA prüft (leider!) immer alle Teile. Und stoppt nicht, wenn einer der beiden Zweige falsch ist. Also anders gelöst – nicht ganz elegant – aber okay:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False
strZeile = Format(intZeile, "00")
frmAuswahl.Controls("lblInfoZeile" & strZeile).Caption = strText
intZeile = intZeile + 1
If intZeile > 12 Then Exit Do
LoopGeht doch!
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.
[Strg] + [Q]
Die Datenüberprüfung in Excel ist hinlänglich bekannt. Ein sehr praktisches Werkzeug, mit dem man eine vordefinierte Liste an Begriffen für Zellen bereitstellt.
Oder sicherstellt, dass nur bestimmte Werte in eine Zelle eingetragen werden.
Allerdings: bei sehr vielen Auswahlmöglichkeiten (sprich: langen Listen) ist die Suche und die Navigation sehr mühsam. Außerdem muss man sich auf feste Listen beschränken und darf keine freien Einträge verwenden. (ich habe auf diesem Blog schon einige Male gelästert und gespottet).
Ich habe ein kleines Add-In geschrieben – Strg + Q (so wird es gestartet), mit dem eine bequeme Auswahl und eine freie Texteingabe möglich ist.
Interessiert? Für private Nutzung stelle ich es kostenlos zur Verfügung; für geschäftliche Verwendung hätte ich gerne 10 Euro. Dann erhältst du auch eine genaue Beschreibung der Installation und Bedienung und eine Rechnung.
Interessiert?
Gerne kannst du es auf meiner Seite compurem.de herunterladen – du findest es hinter dem Menü Programmierung ::: [Strg] + [Q].
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
NextUnd 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 "=*" Thenfunktioniert …
Ein Blick in die Hilfe beantwortet meine Frage:
Die eckige Klammer ist als Zeichen reserviert. Man muss sie entwerten. Weiter unten lese ich:
Ah – so funktioniert es:
If ThisWorkbook.Names(i).RefersToLocal Like "=*[[]*" Then
Oder auch so:
If ThisWorkbook.Names(i).RefersToLocal Like "=*[[]*[]]" ThenWarum nicht gleich?!?
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 SubDas 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:
Heute mal nichts zu nörgeln. Sondern zum Staunen, Freuen und Begeistern.
Kennen Sie e90e50?
https://sites.google.com/site/e90e50
Großartig.
Danke, Andreas Thehos für diesen Hinweis.
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!
Hallo Rene,
Ich suche jemanden, der fit in power query und power pivot ist und dem ich ca. eine stunde lang fragen stellen kann.
Hintergrund: Ich habe einen größeren Auftrag, da geht es um mehrere Tools im Excel-Umfeld, da geht es bei einem Tool jetzt erst mal darum, ob power query da was bringen würde.
Ich hatte Schlimmes oder Schwieriges befürchtet. Aber die zentrale Frage war weder schlimm noch schwierig zu beantworten.
Gegeben sei eine Auftragstabelle mit Verkaufsdaten:
Diese Liste wird nach PowerQuery gezogen und dort bearbeitet. Das Ergebnis wird zurückgegeben:
Die erste Frage lautete: Wie kann man Anfang und Ende als Filter in PowerQuery einbauen?
Die Antwort:
Also so:
Man schaltet einen beliebigen Datumsfilter ein („Zwischen“):
Und ersetzt in M die beiden Werte durch die Variablennamen:
Fertig! Test in Excel:
Und natürlich kam danach die Frage:
ich möchte die berechnung lieber in PowerPivot vornehmen und mit einer Pivottabelle gruppieren und das Meassure verwenden.
Ich habe jetzt in DAX diesen ausdruck, der funktioniert:
MeineSumme:=
CALCULATE(SUM(tblBestellungen_pre[Frachtkosten]);
DATESBETWEEN(tblBestellungen_pre[Bestelldatum];
date(2019;2;1); date(2019;2;27)) )
Weißt du, wie ich hier jetzt das Datum aus den Zellen hineinbekomme?
Auch DAS war schnell geklärt:
Hi Claus,
wenn tblFrom und tblTo im Datenmodell liegen, dann mit:
MeineSumme:=CALCULATE( SUM(tblBestellungen_pre[Frachtkosten]);DATESBETWEEN(tblBestellungen_pre[Bestelldatum]; MAX(tblFrom[From]); MAX(tblTo[To])))
Und kann man Datenmodell und Meassure in einer Pivottabelle in Excel verwenden.
Hübsche Fingerübung!
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:
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?
Dir einen schönen Abend!
Viele Grüße, Dominic
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 …
Sehr geehrter Herr Martin,
ich „spiele“ z.Zt. mit einer Tabelle (Mappe1), in der Excel partout nicht rechnen will. In der Kopfleiste habe ich die für die entsprechenden Spalten die Formeln und die Zellen-Formatierungen eingegeben. Der Wert in Spalte #30 ist die Basis für alle Berechnungen in der entsprechenden Zeile.
Kopiert habe ich nach den Anweisungen der EXCEL-Hilfe und anderen Tipps aus dem Internet.
Für Ihre Hilfe wäre ich Ihnen sehr dankbar.
Hallo Herr M.,
beim Öffnen der Datei meldet Excel, dass ein Zirkelbezug vorliegt. Richtig: in der Statuszeile links unten steht es auch:
Denn: in der Zelle N30 steht die Formel
=EL30
in EL30 steht:
=(100%-EK30)/100%
in EK30 steht:
=(100%-EI30)/100%
in EI30 wird wieder Bezug genommen auf N30 mit:
=(EK30-EH30)/N30
Hier schließt sich der Kreis. Das müssen Sie lösen – sonst rechnet Excel nicht mehr!
Nicht aufgepasst.
Ich arbeite seit einer Weile in einer Excelmappe, die ich dann schließe. Öffne eine neue Datei, trage eine 1 ein und wundere mich über die Fehlermeldung:
Sie müssen eine Form auswählen.
Mir dämmert es. Die letzte Aktion in der letzten Datei war das Arbeiten mit Grafik – ich hatte das Werkzeug „Objekte auswählen“ aktiviert und nicht wieder zurückgeschaltet:
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!
Hallo Herr Martin,
ich bekomme es nicht hin, anbei mein Diagramm, es sollte allerdings so aussehen und ich bekomme es einfach nicht hin.
So sieht es bei mir aus:
Wären Sie so freundlich und würden mir helfen.
Danke
Mfg
Hallo Frau Dobner,
ich helfe doch gerne. Für Ihr Problemchen gibt es zwei Lösungen: entweder sie vertauschen Zeile und Spalte:
Allerdings „erkennt“ dann die Liste Spalte 1 als Daten. Diese muss man rausnehmen und den Datenreihen1 und Datenreihen2 als Legendeneinträge zuweisen (die Jahreszahlen)
Besser. Sie schreiben die Daten von oben nach unten:
Letzteres Variante finde ich einfacher …
Hilft das?
Liebe Grüße Rene Martin
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!
Ich muss meine Antwort noch einmal überdenken!
Gestern Excel-Schulung für
„Ich will da rauf!“ e.V.
Klettern für Menschen mit und ohne Behinderung
Lustige Schulung; sehr nette Teilnehmerinnen.
Eine der gestellten Fragen hat mich amüsiert – ich habe schon einmal in diesem Blog darüber gepostet.
In einer Exceltabelle ist die erste Zeile und die erste Spalte fixiert. Ich erkläre, wie man mit [Strg] + [Pfeil oben] nach „oben“ springen kann. Was ist oben?
Eine Teilnehmerin beschwert sich, dass sie nun nicht die erste Zeile der Datensätze angezeigt bekommt, sondern dass sich die Markierung in der Überschriftszeile befindet, die allerdings immer noch fixiert ist.
Als ich ihr erkläre, dass sie nun in einem zweiten Schritt mit [Pfeil unten] eine Zeile nach unten springen kann und so die ersten Zeilen der Tabelle sieht, ist sie zufrieden.
Hallo Rene,
Eine Sache, die auch bei mir hier (trotz 6GB Arbeitsspeicher) immer wieder kommt, ist folgendes:
Nicht genügend Systemressourcen.
Debuggen kommt dann das hier.. Beim Ausführen einer ganz normalen SQL Anweisung, (zugegeben in einer Rekursion) bleibt er hier stecken..
Auf was muss ich denn da besonders achten?..
Oder könnte man das mit speziellen Funktion abfangen?
Viele Grüße nach München, Bernd
Hi Bernd,
Ich kenne solche Fehler … Nervig!
Versuch mal die Anweisungen rst!dblAnzWdh und Nz(rst!dblWeightPart, 0) in zwei (Double-)Variablen zu schreiben und dann das Produkt auszuführen (das Ergebnis wieder in eine dritte Variable), ebenso die rst!ID.
Und dann den SQL-Code zusammenzubauen.
Klappt das?
Liebe Grüße
Rene
PowerPoint-Schulung. Wir bauen uns ein Parlament.
Ich füge ein Diagramm ein, wähle den Diagrammtyp „Ring“ und trage die Daten ein: Sitze im Parlament:
Ich schließe das Datenblatt. Da ich einen Halbkreis haben möchte, bearbeite ich erneut die Daten in Excel:
Und trage die Summe unter den Zahlen ein (für den unteren Halbkreis):
Ich schließe die Daten – die Summe wird nicht übernommen:
Also noch einmal: Daten bearbeiten:
Ich versuche das blaue Ecke über die Summe zu ziehen, damit die Summe im Wertebereich eingeschlossen ist:
Weit gefehlt – es geht nicht! Der Bereich „springt“ immer wieder zurück:
Okay! Verstanden! Zeile löschen, Zahl per Hand eintragen, Datenblatt schließen (?!?) und DANN funktioniert es:
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):
Hi Angelika,
Gestern habe ich eine ganz interessante Outlook-Frage erhalten. Wie siehst du das:
„Bspw
versende ich einen Termin im Outlook an 200 Teilnehmer und erhalte rund 50 Absagen,
soweit so gut. Sende ich jedoch ein Update dieses Termins, so erhalten auch
jene Teilnehmer welche sich abgemeldet haben, wieder meine Info. Ich erhalte
nun Antworten von Menschen welche sich ja schon eingangs bei mir abgemeldet
haben und mir erneut und entnervt mitteilen, dass sie nicht kommen können.
Ich weiss, dass in IBM Lotus Notes, all jene Teilnehmer welche sich einmal für
den Termin abgemeldet hatten, dann auch keine nervigen Updates mehr erhalten.“
Meine Antwort wäre: das geht nicht. Outlook geht davon aus, dass eine Absage nicht eine Absage zu dem TERMIN ist, sondern zu dem ZEITPUNKT an dem der Termin stattfindet. Deshalb wird ein Verschieben den Leuten mitgeteilt als Möglichkeit JETZT teilzunehmen.
Wie siehst du das? Oder weißt du einen Schalter? Ich würde ihr raten die Leute, die abgesagt haben aus der Einladung rauszuwerfen … Dann könnte es aber sein … oder? Oder einfach einen kleinen Kommentartext in die Einladung zu schreiben.
René
Hallo guten Morgen,
Ja, René, es ist genau so, wie Du es sagst.
Man könnte künftig vielleicht eine Umfrage vorschalten, wer überhaupt Lust auf die Veranstaltung hat und dann nur die zum Termin einladen, die Ja gesagt haben. Da passt es dann auch bei Verschiebungen
Viele Grüße von Angelika
Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!
In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.
Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then
Klappt wunderbar.
Fast.
Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then
und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.
Ich prüfe:
der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:
AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:
If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then
Und das funktioniert! Ich bin glücklich!
Ein Bild. Ein sehr großes und scharfes Bild. Eine Architekturzeichnung:
Ich füge es in eine Excel-Arbeitsmappe, speichere sie, öffne sie und sehe:
Unscharf!
Okay – Excel stellt die Option „Bilder in Datei nicht komprimieren“ zur Verfügung. Hätte ich auswählen sollen:
Allerdings: in Visio? Was mache ich in Visio, wo eigentlich Bilder hingehören und herkommen? Dort suche eine solche Einstellung vergebens. Dort wird ab einer bestimmten Dateigröße komprimiert:
Okay – zugegeben – dort kann man die VDSX-Datei als ZIP umbenennen, extrahieren und dann im Ordner „media“ das verkleinerte Bild durch das Original ersetzen:
Danke an Tanja Kuhn für den Hinweis!
Witzig. Vorlauter Formeln und Assistenten übersieht man manchmal die Wirklichkeit.
Wir schauen die Zielwertsuche an. Mit der Funktion RMZ (regelmäßige Zahlungen) wird die Annuität berechnet.
Nun soll „zurückgerechnet“ werden. Ich möchte wissen wie hoch der Zinssatz bei einer Rückzahlung von 220 Euro ist. Also Zielwertsuche (sie soll ja auch gezeigt werden:
Sie rechnet und liefert:
-4,92% !?! Minus !?!
Es dauert eine Weile, bis wir verstehen: Das Ergebnis ist korrekt. Man müsste mit negativen Zinsen rechnen, um auf diesen Betrag zu kommen … Doch nicht falsch und auch nicht so unrealistisch.
Danke an Hartmut Hilbich für dieses Beispiel.
Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:
IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:
Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …
Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!
Und: während sich auf der Microsoft-Hilfeseite
https://docs.microsoft.com/de-de/powerquery-m/number-round
leider kein Hinweis dazu findet, hat Ken Pulse es sehr gut erläutert:
https://www.excelguru.ca/blog/2014/09/17/power-query-the-round-function/
XLarium weist darauf hin, dass es noch weitere Artikel und Videos zu diesem Thema gibt:
MrExcel hat vor kurzem ein Video dazu gemacht:
Weitere Videos zum Thema Runden:
MROUND In Power Query
TRUNC In Power Quer
CEILING oder FLOOR in Power Query
Zugegeben: geschmunzelt habe ich schon:
Excelschulung. Ich erkläre und zeige (intelligente, dynamische, formatierte, strukturierte) Tabellen:
Ein Teilnehmer behauptet, dass diese Tabellen seine Überschriften löschen würden. Ich bin irritiert. Ich habe eine Weile gebraucht, um zu verstehen, dass die weiße Schriftfarbe, die Excel als Standard einsetzt, nicht sehr clever ist bei einer gelben Hintergrundfarbe …
Natürlich ist die Überschrift noch vorhanden. Nur eben – sehr schlecht lesbar!
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?
Hallo Herr Martin,
ich filtere in dem Kunden-Excel die Werte mittels einer Pivot-Tabelle. Der Filter zeigt aber nur die tatsächlich vorhandenen Werte an.
Jetzt möchte ich im Pivot einen festen Wertefilter definieren, unabhängig welche aktuellen Werte vorhanden sind:
Beispiel:
Ich möchte immer alle Einträge < 24 Stunden gefiltert haben
Aktuelle Werteinträge sind 1 und 2 Stunden. Den Filter 24 Stunden kann ich aber erst auswählen, wenn es mindestens einen Eintrag mit 24 Stunden gibt. Lässt sich dies im Pivot einstellen. Ich habe bislang keine Möglichkeit gefunden.
Hallo Herr H.,
der Gedanke der Pivottabelle ist ja, die vorhandenen Werte zu gruppieren und die Zahlen zusammenzufassen (aggregieren, also: summieren, zählen, …) Wenn Sie andere Werte sehen möchten, müssen diese in der Liste stehen (man müsste sie ausblenden).
Hallo Herr Martin,
es gibt im Office 365 Excel den Befehl FILTER, der genau das macht, was ich benötige. Nur hat mein Kunde leider eine ältere Version. Lässt sich das in einem älteren Excel mit einem workaround bauen?
Viele Grüße
Hallo Herr H.,
Nein – bitte nicht die Funktion FILTER verwenden – sonst hat die Firma ein Problem!
Was würde ich tun?
* entweder die Daten dazwischen verstecken (und die Zeilen ausblenden)
* oder die Daten auf einem anderen Blatt sammeln und dort alle notwendigen Daten einsammeln.
Hum. Sonst? Müsste mal überlegen
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Filter geht beim Kunden nicht, aber ist die Funktion so gefährlich?
Ich habe mittels Pivot die Daten auf ein anderes Blatt ausgelagert und nutze das Ergebnis für die Dropdown-Felder.
Ich habe jetzt einen Dummy-Wert eingefügt, damit die Pivots die Auswahlfelder behalten, auch wenn keine Daten auszuwerten sind. Ggf. wäre eine Lösung ganz ohne Pivots zu arbeiten, aber dazu bräuchte ich so etwas wie die Filter-Funktion.
Hallo Herr H.,
nein, nein: FILTER & co sind klasse – Problem: nicht jeder hat diese Funktionen. Deshalb: bauen wir den Filter doch nach!
Werfen Sie mal einen Blick in meine Liste: In Spalte H befinden sich die sechs Werte. Einer wird in J2 ausgewählt. In Spalte L ermittle ich die Zeilennummer, falls gefunden. In Spalte M sammle ich diese Nummern ein; gruppiere sie also. Mit BEREICH.VERSCHIEBEN baue ich die Liste ab O1 auf (ich hätte auch INDIREKT oder INDEX / VERGLEICH verwenden können).
Ich könnte es auch mit AGGREGAT aufbauen – aber lassen wir das …
kommen Sie damit klar?
Liebe Grüße
Rene Martin
Andreas Thehos erhält Post:
Hallo Andreas,
Kannst du auch einmal ein Tutorial über diese defekten Tabellen machen und wie man die wegbekommt? Bei meinen Recherchen habe ich bis jetzt nur gefunden das die erstellt werden wenn Excel abstürzt. Die einzige Lösung de in foren geholfen hatte, war eine neue Mappe zu erstellen und alle Daten zu übertragen. Aber das kann doch nicht die Lösung sein oder? Alle Änderungen die vorgenommen werden beziehen sich nur auf „DieseArbeitsmappe“
Andreas weiß keinen Rat. Und ich? Ich hatte mal eine solche korrupte Datei gesehen – ist schon eine Weile her. Und nein – sorry – ich weiß leider auch keinen Rat.
Aber Andreas wird fündig:
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!
Hallo René,
habe mir dein Buch mal durchgearbeitet. Respekt, viele richtig spannende Sachen dabei.
Einen Satz habe ich allerdings nicht verstanden. Unter 4.10 auf Seite 104 sagst du, dass es nicht möglich sei Hintergrundfarbe, Schriftart und -größe in Notizen festzulegen.
Sie mal hier…
Hallo Hannes,
stimmt: ich hätte noch das Wörtchen „als Standard“ hinzuschreiben sollen. Du kannst nicht die Standardschrift, Standard-Hintergrund … von Notizen in Excel festlegen
Liebe Grüße und danke für den Hinweis
Rene
Lieber Rene
Wie geht es dir? Ich habe wieder mal eine Frage:
Im Anhang findest du eine Tabelle. Was ich möchte ist folgendes:
Ich möchte wissen, welche Zahlen zwischen der kleinsten und der grössten Zahl alle noch fehlen. Am liebsten hätte ich alle fehlenden Zahlen in einer Spalte.
Herzliche Grüsse
Andreas
Hallo Andreas,
ich verstehe nicht ganz – in der Liste befinden sich keine Zahlen, sondern Texte. Texte der Form CHE-xxx.yyy.zzz
Was heißt in diesem Zusammenhang „die kleinste und die größte Zahl“?
LG :: Rene
Lieber Rene
CHE-000.000.000 (kleinst mögliche), CHE-000.000.001 ….. CHE-999.999.999 (grössmögliche).
Vielleicht sollte ich besser „Nummern“ schreiben, das „CHE-“ könnte man auch weglassen, man könnte auch die Punkte durch tausender Trennzeichen ersetzen, damit es Zahlen werden: 000’000’001 bis 999’999’999
In der Matrix hat es eine Auswahl von möglichen „Nummern“, eine „Nummer“ ist die Grösste, eine die Kleinste, dazwischen hat es in der Matrix welche, aber es fehlen auch welche. Ich möchte gerne alle Nummern, von der kleinsten Nummer bis zu Grössten Nummer, die mir noch fehlen und so, dass ich Sie in einer Spalte habe und sortieren kann.
Konnte ich mich verständlich ausdrücken?
Herzliche Grüsse Andreas
Hoi Andreas,
kurz nachgeschaut:
die kleinste „Zahl“ in deiner Liste ist CHE-100.000.058, die größte: CHE-499.992.187. Das heißt: wir müssten fast 500 Millionen Zahlen prüfen.
Ich habe es mal mit den Zahlen zwischen 100.000.000 und 101.000.000 versucht – bei einer Formel (bist du drin? -> Zählenwenn) geht Excel in die Knie. Nicht auszudenken, wenn du das in einer Spalte „zusammengefasst“ haben möchtest und das ganze x 500!
Da stürzt Excel ab.
Ich würde es entweder mit PowerQuery lösen oder mit VBA.
Liebe Grüße
René
Hoi Rene
1.000 Dank!
Ich prüfe das nochmals…
Herzliche Grüsse
Andreas
Hallo Excel-Meister
ich arbeite an einem VBA – Projekt, dass aus Power BI Dateien die Metadaten rauslesen soll.
Das Auslesen geschieht über Power Query (what else….), aber ich muss noch ein paar Prüfungen mit VBA erstellen und insbesondere die Power Query Abfragen on the fly erstellen. Letzteres geht problemlos.
Der Ablauf:
Bis dahin klappt alles.
Nun kommt der Punkt, wo der Benutzer sich gegenüber der Power BI Datei authentifizieren muss, nachdem er ja gesagt hat.
falls er aber den Dialog hier abbricht (…..DAU…….), kommt eine „schöne“ Meldung:
Nun meine Frage:
Wie kann ich hier meine eigene Meldung einbauen und vor allem, wie fange ich das ab?
Bin schon voller Zweifel…..
Merci, lieber René für deine Geduld mit mir
Freundliche Grüsse Hans Peter
########################################
die unwissenden erleuchten sich selber
habs gefunden. nach Drücken von „Senden“ fiel es mir wieder ein, da stand was im Buch von René
ich danke dir!
Hier der Code, falls es dich interessiert.
Sub GetData()
‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
‚ Code erstellt durch: Pfister BI Consulting GmbH
‚ Zweck: Holt Metadaten aus der Power BI Datei
‚ Erstelldatum: 8.3.2021
‚ Aenderungsdatum:
‚XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
‚Testen, ob pbix Datei geöffnet ist. Falls nicht, Mesagebox und fragen, ob sie geöffnet werden soll. Fall nicht, Abbruch
If Dateigeoeffnet(Range(„Dateipfad_PBIX_Original“)) = False Then
If MsgBox(„Die Datei muss geöffnet sein. “ & Chr(10) & “ Soll die Datei geöffnet werden?“, vbYesNo, „Power BI Datei öffnen?“) = vbNo Then
Exit Sub
Else: Call Open_PBIX
Application.Wait (Now + TimeValue(„0:00:10“))
End If
End If
’notwendige Abfragen aktualisieren
Abfragen_starten:
On Error GoTo ErrHandler
ActiveWorkbook.Connections(„Abfrage – Tabellen“).Refresh ‚Teil Abfrage – “ muss unbedingt vermerkt sein!
ActiveWorkbook.Connections(„Abfrage – Memory Usage Tabellen“).Refresh ‚Teil Abfrage – “ muss unbedingt vermerkt sein!
ActiveWorkbook.Connections(„Abfrage – Tabellenliste“).Refresh ‚Teil Abfrage – “ muss unbedingt vermerkt sein!
ActiveWorkbook.Connections(„Abfrage – Liste nicht geladene Queries“).Refresh ‚Teil Abfrage – “ muss unbedingt vermerkt sein!
ActiveWorkbook.Connections(„Abfrage – Abfragen – nicht geladen“).Refresh ‚Teil Abfrage – “ muss unbedingt vermerkt sein!
Call Listen_befuellen
ErrHandler:
‚Fehler No. 1004 abfangen
If Err = 1004 Then
If MsgBox(„Soll der Prozess abgebrochen werden?“, vbYesNo, „Bitte Identifikation vornehmen“) = vbYes Then
Exit Sub
’sonst Abfrage wieder aufnehmen
Else: Resume Abfragen_starten
End If
End If
End Sub
Gruss Hp
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.
Danke an Sven für diesen wichtigen Hinweis.
Hi René, Danke für die Präsentation. Was mich kurz interessieren würde: Wenn ich z. B. das Währungssymbol von Ungarn möchte, scrolle ich mir einen Wolf, bis ich es gefunden habe. Geht das schneller? Wie lange brauchst du dafür? Tipp: Ungarn liegt zwischen Kamerun und Haiti. Oder: Polen zwischen Lateinamerika und Mazedonien. Klar, einmal gefunden, kann ich das Format einfach übertragen. Aber hin und wieder kommen so Währungs-Exoten und da ist die Auswahl sehr mühsam. Danke und dir einen schönen Abend! Christa
Hi Christa, interessante Frage. Stimmt: wenn du „H“ drückst, springt Excel zu HUF – also zu den Texten im ISO-Code – nicht zu den Symbolen. Tipp: in meinem Skript (hast du?) und auch im Internet gibt es Listen, wie man die Währungssymbole im ASCII-Code eingeben kann. Vielleicht schneller als die Sucherei. Die Ländersortierung ist immer merkwürdig. Meistens liegt eine englischsprachige Tabelle dahinter. Mich würde ja auch interessieren, ob es eine Liste der Symbole gibt. Steht das Excel? Ich mach mich mal auf die Suche… LG :: Rene
Hi René, oh cool. Vielen Dank. Ja, ASCII hatte ich noch in der Ausbildung zur Europasekretärin. Und ich bin Baujahr 78 wohlgemerkt. Dein Skript habe ich gestern heruntergeladen. Danke!!! Ja, das würde mich interessieren, wenn du so eine Liste gefunden hast. Kein Stress
Hi Christa, ich habe mal ein bisschen probiert. Der Makrorekorder hilft nicht weiter, weil er die Zeichen nicht richtig codiert. Also: VBA scheidet aus. Ich habe mal die fast 300 Symbole angeklickt – die Datei gespeichert, in .ZIP umbenannt und aus dem XML-Archiv die Währungssymbole herausgeholt. Auch nicht ganz befriedigend. Immerhin: ich habe entdeckt, dass es doch ein BTC-Symbol/Zeichen für Bitcoin gibt … irgendwo dazwischen. Wie man allerdings die angezeigten Texte der Combobox herausbekommt … keine Ahnung! Ich schick dir mal die Datei LG :: Rene
Übrigens: nach 200 Zahlenformaten hört Excel auf. Ich habe zwei Dateien erstellen müssen.
Gerne können Sie die Liste von meiner Seite herunterladen:
http://www.excel-nervt.de/wp-content/uploads/2021/03/Waehrungen.xlsx
Und: wer den Vortrag ansehen möchte – man findet ihn unter:
https://www.youtube.com/watch?v=9UI9IwDVlGc
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:
https://www.youtube.com/watch?v=9UI9IwDVlGc
und ein Dankeschön an Faraz Shaikh für seinen Hinweis zu seinem Video, indem er erklärt, wie man ein Datum aus dem Hijri-Kalender in ein Datum des gregorianischen Kalenders konvertiert:
https://www.youtube.com/watch?v=Du3uuhKel5w
Vorgestern habe ich auf dem Excel-Meetup einen Vortrag über Zahlenformate in Excel gehalten. Nach dem Vortrag schreibt Tanja:
Hallo René
Danke für deinen Vortrag. War ein sehr guter Überblick.
Wieso hast du das Problem mit den Monaten über Hilfsspalten und nicht über die Gruppierung des Datums gemacht?
Dann wäre es ja ganz einfach die fehlenden Monate anzuzeigen.
Sonnige Grüsse aus der Schweiz
Hallo Tanja,
ich freue mich über Mails, die beginnen mit „warum hast du nicht …?“ Es gibt immer drei Möglichkeiten:
* entweder ich habe etwas übersehen – und es gibt wirklich elegantere Lösungen
* oder es gibt mehrere Lösungen; ich habe mich für die eine entschieden, weil …
* oder meine Lösung ist bewusst gewählt, weil …
Ich habe gestern Abend noch einmal nachgedacht:
[und dann folgt eine lange Erklärung, warum ich es nicht so gemacht habe, wie sie es vorschlägt. Das soll an dieser Stelle nicht wiederholt werden.]
Liebe Grüße aus dem verregneten München
René
PS: Beim Durchschauen habe ich gerade bemerkt, dass die Option „Elemente ohne Daten anzeigen“ nicht aktivierbar ist, wenn das Datenmodell eingeschaltet ist. Wusste ich nicht …
Hallo Rene
Danke für deine Erläuterungen. […]
Ich kann mir vorstellen, warum das nicht aktiv sein kann. Beim Zusammenspiel mit dem Datenmodell kann ich ja auch nicht in der Pivot-Tabelle nach einem Datum gruppieren. Wenn ich es sauber aufbaue, brauche ich dazu im Datenmodell ja eine Kalendertabelle und dort kann das Problem mit fehlenden Jahren oder Monaten gar nicht auftauchen, da eine Kalendertabelle ja immer den kompletten «Datumsbereich» enthalten muss.
Übrigens: wer meinen Vortrag ansehen möchte – man findet ihn auf:
https://www.youtube.com/watch?v=9UI9IwDVlGc
Gestern auf Excel-meetup in London habe ich einen Vortrag über Zahlenformate in Excel gehalten. Anschließend kam folgende interessante Frage:
In einer Zelle steht =12/24. Das Ergebnis soll allerdings als Bruch dargestellt werden. Leider weigert sich bei der Zellformatierung Excel, ihn mit den ursprünglichen Werten 12 und 24 darzustellen:
Ich fürchte, das wird auch nicht funktionieren. Der Wert der Berechnung beträgt 0,5. Wie soll diese Ergebniszahl wissen aus welchen Werten sie entstanden sind. Natürlich kann mit FORMELTEXT die Funktion (und damit die Werte) anzeigen lassen – jedoch: DAMIT kann nicht weitergerechnet werden.
Übrigens: wer meinen gestrigen Vortrag ansehen möchte – man findet ihn unter:
https://www.youtube.com/watch?v=9UI9IwDVlGc
Hallo René,
ich dachte, ich meld mich mal mit einer kleinen Excel-Anekdote (und einer kleinen Frage). Wahrscheinlich ein alter Hut für dich, ich fands aber ganz witzig. 😉
Zuletzt kam ein Kollege zu mir, er hat ne Excel-Frage. Er hat da eine Datei geschickt bekommen und wenn er da irgendwo in eine Excel-Zelle ne Zahl schreibt und auf „Währung“ beim Zahlenformat (das € Deutsch natürlich) klickt…dann steht da D-Mark.
Hatte ich so auch noch nicht gehört. Also schauen wir in unter „Zellen formatieren“. Da war ein benutzerdefiniertes Zahlenformat eingestellt. Kann es jetzt leider nicht 1:1 wiedergeben, da ich die Datei selbst nicht habe, aber es müsste das hier gewesen sein:
_-* #.##0 €_-;-* #.##0 €_-;_-* „-“ €_-;_-@_-
Nur anstelle das €-Zeichens stand „DM“. Wie kommt das da rein?
Ich schaue auf das Erstell-Datum der Datei…26.09.2001.
Auch gut, wenn eine Datei seit fast 20 Jahren in einem Unternehmen hin und her geschickt wird. 😉
So, jetzt zu meiner kleinen Frage. Die Einstellung Berechnungsoptionen > Manuell in der Registerkarte „Formeln“ ist, wenn ich mich nicht sehr irre, eine Einstellung, die alleine auf die jeweilige Arbeitsmappe beschränkt ist.
Jetzt bin ich einigen Leuten begegnet, die steif und fest behaupten, dass diese Einstellung „rüberwandert“, wenn man mehrere Dateien offen hat.
Also beispielsweise bekommst du per E-Mail eine Datei mit ausgeschalteter Berechnung, öffnest diese, hast aber noch zwei andere Dateien offen. Dann soll die Einstellung auf die zuvor geöffneten Dateien rüberwandern.
Ich konnte dieses Phänomen nie reproduzieren. Auch nicht, wenn ich aus der Mappe mit ausgeschalteter Berechnung heraus (z.B. mit Strg + n) eine neue erzeuge.
Hast du schon mal davon gehört?
Wäre es theoretisch möglich, dass man auf VBA-Ebene bei allen geöffneten Mappen die Berechnung ausschaltet, wenn ich die Datei öffne und Makros aktiviere? (Bei den Leute, die mir das berichtet haben, halte ich es eher für unwahrscheinlich, dass die xlsm-Dateien hin- und hergeschickt haben)
Danke dir und viele Grüße,
Dominic
Hallo Dominic,
ich kann es nicht ganz nachvollziehen. Wenn du in der Excelmappe, die in der Zeit erstellt wurden als es noch keinen Euro gab, auf das Buchhaltungszahlenformatsymbol klickst, wird die Zelle als DM formatiert:
Wenn man das Tabellenblatt in eine neue Arbeitsmappe kopiert, ebenso.
Nicht jedoch, wenn ich einige Zellen in eine neue Datei kopiere; auch nicht, wenn ich das Blatt in eine vorhandene Mappe kopiere.
Ich wüsste nicht, wie dieses Zahlenformat in eine andere Datei „wandern“ kann … ich glaube diesen Menschen nicht, die so etwas behaupten …
Liebe Grüße Rene
*)PS: Danke für die hübsche Headline – sie wird den heutigen Tag überschreiben.
Hallo Herr Dr. Martin,
da Sie mir schon mal bei dem Rauten Problem sehr geholfen haben, hoffe ich nun das Sie mir bei dem folgenden Problem auch helfen können.
Ich kann in meinem Kalender aus einem Dropdown-Menü Tage Markieren an denen ich „im Urlaub oder Krank“ bin Auswählen. Nun möchte ich aber das nach der Auswahl der Wert 1 für einen Tag in einer anderen Zelle und auf einem andere Blatt angezeigt, und dann auch zusammen gezählt wird. Habe einen Teil dieser Funktion auch mit einer wenn Formel zb. =Wenn(H6“Krank“;“1“;““) bzw. =Wenn(B6“Urlaub“;“1“;““) hinbekommen, die Zahl 1 steht dann in der gewünschten Zelle nur wenn ich weitere Tage mit „Urlaub oder Krank „ markiere, sollten diese in der Zelle dann auch Aufaddiert werden und das geht nicht. Wie kann ich das hinbekommen, können Sie mir da vielleicht auch weiter helfen? Mit freundlichen Grüßen
Hallo Herr B.,
dafür gibt es zwei Lösungsansätze:
Entweder sie ermitteln in einer Spalte jeweils, ob in der Kalenderspalte der Text „krank“ steht:
=WENN(B2=“krank“;1;0)
oder in einer anderen Spalte, ob dort „Urlaub“ steht:
=WENN(B2=“Urlaub“;1;0)
Unter den Spalten ziehen Sie nun die Summe.
Oder Sie berechnen die Gesamtanzahl der „kranken“ Zellen mit
=ZÄHLENWENN(B2:B28;“krank“)
Das Gleiche für Urlaub.
Und beide zusammen? Na – addieren:
=ZÄHLENWENN(B2:B28;“krank“)+ZÄHLENWENN(B2:B28;“Urlaub“)
Werfen Sie mal einen Blick in die beigefügte Datei!
Liebe Grüße
René Martin
Hallo Herr Martin
Es kommt wieder mal eine Frage aus der Schweiz.
Für den damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.
Haben Sie dazu eine Idee?
Hallo Herr S.,
im Ordner _rels befindet sich eine XML-Datei mit Namen .rels. In ihr muss eingetragen werden, dass im Ordner ribbon\customUI die Datei customUI1.xml mit der Ribbon-Definition liegt, beispielsweise so:
<Relationship Id=“rId2″ Type=“http://schemas.microsoft.com/office/2007/relationships/ui/extensibility“ Target=“ribbon/customUI/customUI1.xml“/>
Die Id muss eindeutig sein!
Liebe Grüße
Rene Martin
Zugegeben: Ein bisschen nervös bin ich schon!
Alan Murray hat mich eingeladen am Dienstag, dem 09. März um 19 Uhr (MEZ) auf seinem Excel-meetup einen Vortrag zu halten. 175 Personen haben sich bereits angemeldet – Uff – vor so einem großen Publikum habe ich noch nie gesprochen!
Ich habe mir das Thema „Zahlenformate“ ausgesucht – gefühlte 100.000 Gimmicks rund um die Zahlenformate, die Excel bereithält. Beispielsweise die Unterschiede zwischen Währung und Buchhaltung / Currency and Accounting.
Interessiert? Neugierig?
Einfach anmelden:
https://www.meetup.com/de-DE/London-Excel-Meetup-Group/events/276115976/
Ich habe ein Problem mit Excel 2016:
In einer großen Tabelle (~17.000 Zeilen) muss ich ein einer Spalte eine Rechtschreibkontrolle durchführen. Hier scheinen „Alle ignorieren“ und „Immer ändern“ nicht zu funktionieren – wenn ich Fehler mit diesen Schaltflächen bearbeite (um z. B. ein bestimmtes Wort aus der Schweizer Rechtschreibung in die deutsche Standard-Rechtschreibung umzuwandeln), werden sie mir bei ihrem nächsten Vorkommen wieder angezeigt, statt (wie erwartet) einmal komplett durch die Spalte zu gehen und alle Vorkommnisse dieses Fehlers zu beheben. Dies bremst mich natürlich erheblich aus. Gibt es einen Trick, damit die Excel-Rechtschreibkontrolle so effektiv funktioniert wie in Word, oder muss ich mit diesem Problem leben? Und wurde das Problem eventuell in Excel 2019 behoben?
Ich danke für eure Antworten!
Hallo Herr P.,
Ich habe es gerade bei einer Liste mit 300.000 Zeilen probiert – in meinem Excel in Microsoft 365 läuft [alles ignorieren] prima durch: der Fehler „Huerth“ wird in Zeile 299.991 gefunden, in der übernächsten Zeile übergangen.
Aber ich kann die Frage gerne mal posten – vielleicht hat jemand ein ähnliches Problem.
Liebe Grüße
René Martin
Hallo Herr Martin,
vielen Dank für Ihre Rückmeldung. Es könnte wirklich ein Problem meiner „älteren“ Version von MS Office sein.
In jedem Fall freue ich mich schon auf Ihre Rückmeldung.
Liebe Grüße
Liebe Leserin, lieber Leser,
gerne dürfen Sie einen Kommentar abgeben, wenn SIE dieses Problem in Excel haben / kennen. Wir würden uns freuen.
Und: Die Namensliste, die Sie oben sehen, habe ich mit dem fakename-Generator erzeugt:
https://www.fakenamegenerator.com/
Lieber René,
nachfolgender Code läuft fehlerfrei unter
Win 7 + Word 2010
Win 7 + Word 2013
Win 10 + Word 2019
Win 10 + Word 365
nur nicht unter Win 10 + Word 2016.
Die rot markierte Zeile ist mein Problemkind.
Vielleicht hast Du noch eine Idee.
Sub TopAktuell_Einfügen()
Application.ScreenUpdating = False
DocPath = "O:\Topaktuell\"
ChDrive ("O:\")
ChDir ("O:\Topaktuell\")
Dim datei As String
Selection.EndKey Unit:=wdStory
For dokumente = 1 To 30
If Dir(DocPath & dokumente & "_1.docx") = "" Then Exit For
For seiten = 1 To 20
datei = dokumente & "_" & seiten & ".docx"
If Dir(DocPath + datei) = "" Then Exit For
Selection.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=datei
Next
Next
Application.ScreenUpdating = True
End SubDanke.
LG Traudl
Hi Traudl,
Seit über 20 Jahren schreibe ich VBA-Code. Dabei ist mir aufgefallen, dass sich einige wenige Objekte verändert haben. Ich habe mal vor vielen Jahren ein Makro geschrieben, das Text in eine Word-Tabelle geschrieben hat und den Text mit einem Hyperlink auf ein Word-Dokument versehen hat. Eine der Parameter beim Befehl Hyperlinks.Add wurde geändert.
Deshalb sage ich nicht, dass das nicht sein kann.
ABER! Bitte, bitte, bitte: adressiere doch sauber.
Ändere seinen Code in:
Dim wdBereich As Range
Set wdBereich = ActiveDocument.Range(Start:=ActiveDocument.Range.End - 1)(Ende des Dokuments)
Und dort:
wdBereich.InlineShapes.AddOLEObject ClassType:="Word.Document.12", _
FileName:=datei
Es kann natürlich sein, dass es trotzdem nicht unterstützt ist – aber eher unwahrscheinlich als das „Hoppeln“ an Ende:
Selection.EndKey Unit:=wdStory
Raus damit!
Und: wenn du gerade dabei bist:
ChDrive ("O:\")
ChDir ("O:\Topaktuell\")
Raus damit!
Und: deklariere die Variablen:
Dim docpath As String
Dim dokumente As Integer
Dim seiten As Integer
Dim datei as String
Und schließlich: Warum fügst du ein Word-Dokument in ein anderes ein? Ich würde es öffnen, an eine Objektvariable übergeben, den Inhalt (brauchst du die Formate?) entweder in einer Stringvariable speichern oder kopieren und ins Dokument einfügen.
Hilft das?
Liebe Grüße
Rene
Ich danke Dir ganz, ganz herzlich für Deine Vorschläge, lieber René.
Den Code hatte ich 2017 erstellt und war froh, dass er läuft. Er ist eine Sub aus einem unfangreichen Makro. Ich bin allerdings keine Programmiererin. Dazu benutze ich VBA viel zu wenig.
Deine Vorschläge werde ich natürlich umsetzen .
Ich gebe Dir Bescheid, wie es funktioniert.
Liebe Grüße Traudl
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“.
Hallo Herr Martin,
ich zweifle an meinem Verstand – ich kann den Fehler in der Differenzrechnung Zeile 38 nicht entdecken, obwohl ich alle Formatierungen und Eingaben mehrfach gecheckt habe. Der Fehler tritt nur in der Zelle i38 auf, die Formel habe ich mehrfach neu eingegeben und Zeilen und Zellen gelöscht usw. der Fehler bleibt – HILFE!
Ich bin froh, dass es nur eine private Tabelle ist und nichts Wichtiges, aber so was ist doch nicht zu ertragen…
Übrigens: Ihre Sprüche sind grandios! Wenigstens konnte ich mehrfach laut lachen!
Mit freundlichen Grüßen Ulrike
Hallo Frau H.,
Danke für das Lob der Sprüche – manchmal befürchte ich, dass einige nur auf meine Seite wegen der dummen Sprüche klicken. Die sollen doch mit mir über Excel diskutieren! *lach*
Ich verstehe Ihr Problem nicht:
130,45- 150,31 = 19,86
SO haben Sie es in den anderen Zeilen auch gerechnet.
Wer von uns beiden „denkt falsch“?
Liebe Grüße
Rene Martin
Guten Tag Herr Martin,
erstmal danke für die schnelle Reaktion, die ich zuerst nicht verstanden habe. Beim erneuten Öffnen der gesendeten Mail konnte ich sehen, dass die Zeilen, um die es geht, darin nicht zu sehen sind, obwohl ich das ganze Blatt schicken wollte. Das liegt evtl. an der Druck-einstellung. Ich schicke die Mappe nochmal und stelle fest, dass es Zeile 39 ist, wo der Fehler auftritt (ich hab das mit der Nummerierung der Positionen verwechselt! – war ja auch schon etwas Gaga nach dem vielen rumprobieren) Die Formel heißt also: <=H39-G39> und müsste = ergeben, tut sie aber nicht.
Also noch mal meine Bitte, den Fehler zu identifizieren.
Liebe Grüße Ulrike
Hallo Frau H.,
Ich würde gerne helfen, aber Zeile 39 ist leer!
Liebe Grüße
Rene Martin
Dann bitte bei Zeile 40 nachschauen, folgende Beträge: 129,74 € – 61,67 € ergibt nicht -67,80€ sondern – 68,07€! Ulrike
Da ist ein Zahlendreher in Ihrem Kopf oder ihrer Wahrnehmung, liebe Frau H.
In der Zelle G40 steht der Wert 129,47 und nicht 129,74:
Vier – sieben – NICHT: sieben – vier!
Liebe Grüße
Rene Martin
Oh mein Gott— gut, dass ich niemand anders gefragt habe – es ist zu peinlich!
Ganz lieben Dank für die Mühe! Ich werde die Seite weiterempfehlen – nicht nur wegen der Sprüche!
Gruß Ulrike
Guten Tag Herr Martin,
ich bin durch Zufall auf Ihren Blog aufmerksam geworden.
Leider hat Excel immer wieder merkwürdige Verhaltensweisen.
Warum auch immer.
Ich habe auf einem Rechner ein Problem mit der Funktion Daten „Abrufen und transformieren“
Ich kann auf dem PC keinerlei Daten über diese Funktion aufrufen.
Keine CSV noch eine Tabelle aus einem SQL Server.
Ich bekomme immer wieder die Fehlermeldung, dass die Initialisierung der Daten fehlgeschlagen sei.
Lade ich die CSV auf einem anderen Rechner ein, funktioniert dies Problemlos.
Gleiches gilt bei der SQL Abfrage.
Haben Sie noch eine Idee wo ich das Problem suchen kann?
In der PowerQuery kann ich die Daten sauber sehen.
Nur das Einfügen der Daten aus der Verbindung in das Tabellenblatt funktioniert nicht.
Vielen Dank für Ihre Zeit. Mit besten Grüßen,
####
Ich habe es mir gerade angesehen: Excelversion 2016.
Ich erstelle eine intelligente Tabelle, ziehe sie in PQ, klappt; ich bearbeite, ich tue, ich mache, M-Code ist vorhanden – klappt.
Ich lade sie als Verbindung. Klappt.
Aber sobald ich die Daten als Tabelle „sehen“ / „haben“ möchte – knallt es:
Kennt jemand diesen Fehler?
Initialisierung der Datenquelle schlug fehl.
Überprüfen Sie den Datenbankserver oder kontaktieren Sie Ihren Datenbankadministrator. Vergewissern Sie sich, dass die externe Datenbank verfügbar ist, und wiederholen Sie den Vorgang. Sofern diese Nachricht wieder erscheint, erstellen Sie eine neue Datenbankquelle um mit der Datenbank Verbindung aufzunehmen.
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*
Tim will’s wissen.
Nachdem er die zehn wichtigsten Excel-Funktionen beschrieben hat, fragt er sich, welches die nächsten zehn wichtigen sind.
Habt ihr Lust ihm zu helfen?
Welchen sind deiner Meinung nach wichtige, nützliche und von dir häufig verwendete Funktionen?
Hallo René,
ich komme heute mit einer Frage auf Dich zu.
In einem „unserer“ Excel-Stammtische hast Du uns ein Besispiel gezeigt, wie Du durch ein Klick auf ein Bild „Inhalte aktivieren“ bestätigen kannst (d.h. der Benutzer muss nicht oben auf den orangefarbende Balken klicken , um die Makros der Arbeitsmappe zu aktivieren…).
Leider musste ich feststellen, dass meine Kollegen gerne die Aufforderung „Inhalte aktivieren“ übersehen und so meine Arbeitsmappen mit Makros nicht aktiviert werden. Um das ersichtlich zu machen bzw. zu erzwingen, würde ich gerne Deine Lösung einbauen.
Kannst Du mir dazu bitte Dein Beispiel zukommen lassen?
Das wäre großartig!
Vielen Dank & Grüße
Sebastian
so!
Ich habe ein Bild auf das Blatt gelegt, das ich beim Öffnen per Makro unsichtbar mache.
Werden die Makros nicht aktiviert, bleibt das Bild sichtbar.
Die Erklärung: Manchmal beschweren sich Anwender, dass bestimmte Befehle deaktiviert sind. Es wäre praktisch ein Makro zu haben, das meldet, dass die Makros nicht aktiviert wurden. Dies geht natürlich nicht. Um sicherzustellen, dass die Makros aktiviert wurden, kann in ein Projekt ein Bild eingefügt werden.
Werden die Makros nun aktiviert, wird das Bild gelöscht.
Man kann Shapes einen Namen geben oder mit einer Schleife alle Shapes / Bilder durchlaufen und entweder löschen oder Unsichtbar machen (Visible = False)
Das Bild wird beim Schließen der Arbeitsmappe wieder eingeblendet:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
ThisWorkbook.Worksheets(„BCM“).Shapes(„Hinweis“).Visible = _
msoTrue
Hallo René,
super! Klasse!
Ich danke Dir!
Ich war von Deiner Lösung damals so begeistert, dass die hängen geblieben ist.
Da ich allerdings nicht mit (externen) Kunden arbeite, hatte ich keinen Anwendungsfall – bis ich letzte Woche erfahren durfte, dass meine Kollegen gerne den Balken übersehen und so nicht meine Datei funktioniert.
Ich werde Deine Lösung am Montag einbauen- das Bild wird selbstverständlich geändert.
Tausend Dank nochmal und bis bald.
Liebe Grüße Sebastian
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:
Der Code:
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHome">
<group idMso="GroupEditingExcel" visible="false" >
</group>
</tab>Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:
Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:
Private Sub Workbook_Open()
On Error Resume Next
Application.OnKey "^f", "BitteNicht" ' suchen
Application.OnKey "^h", "BitteNicht" ' ersetzen
End SubBeim Ö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 SubEbenso 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 SubDANN ist das Ereignis wiederhergestellt!
Hallo Herr Martin,
Ich weiß nicht, ob Sie mir weiterhelfen können. Ich untersuche englische Dramentexte bezüglich der Wortlänge in der Anzahl der Zeichen.
Jetzt habe ich 100 Spalten. Jede Zeile enthält in jeder Spalte ein Wort eines fortlaufenden Textes. In den Spalten 101 – 110 möchte ich gern die Anzahl der Worte festhalten, die ein, zwei, drei, etc.
Buchstaben lang sind. Spalte 111 soll für jede Zeile den Wert größer zehn enthalten.
Mit meinen Formelversuchen bin ich kläglich gescheitert, auch wenn Ihre Anmerkungen in der Formelsammlung tröstlich und erheiternd waren.
Auch wenn Sie keinen Tipp haben sollten, herzlichen Dank für Ihre Mühe.
Hallo Herr I.,
die Lösung heißt SUMMENPRODUKT.
Sie kann als Matrixfunktion LÄNGE verarbeiten.
Werfen Sie mal einen Blick auf das Beispiel – Sie müssen natürlich Zeilen und Spalten vertauschen und einige Spalten einfügen … aber ich glaube das ist die Lösung, die Sie haben möchten. (ich zähle hier die Wörter mit zwei, drei, vier, …) Buchstaben aus Spalte A, B, C, …
Liebe Grüße
Rene Martin
Lieber Herr Martin,
Danke für die schnelle und perfekte Lösung. Der Hintergrund ist, dass sogenannte Marlowianer die Identität Marlowes mit Shakespeare behaupten und dazu die häufigste Wortlänge von 4 Buchstaben ins Feld führen, die bei beiden identisch ist. Tatsächlich ist das aber eine Eigenschaft des englischen Sprachsystems, nicht aber Autorenidentität.
Liebe Grüße
Hallo Herr I.,
ach – DIE Diskussion. Jo, kenn ich; und die hundert anderen Antworten auf Frage, wer Shakespeare wirklich war …
Sollte man wirklich solche positivistischen Ansätze, die im 19. Jahrhundert verwendet wurden, ins Feld ziehen? *hum* *hum*
Trotzdem: viel Spaß mit Willi, Chrisi & Excel
wünscht Rene Martin
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 …
Hallo Herr Martin
Es kommt wieder mal eine Frage aus der Schweiz.
Für den damaligen Kunden hatte ich mit Ihren Angaben und Ihrem Buch eine eigene Registerkarte für die Vorlage erstellt. Das hat super funktioniert. Nun gibt es noch Ergänzungen einiger zusätzlicher Schaltflächen. Nur bockt diesmal etwas.
Mein Vorgehen:
Ergebnis: Datei kann nicht mehr geöffnet werden. Ich habe gefühlt alles genau gleich gemacht wie beim ersten Mal. Haben Sie dazu eine Idee?
Besten Dank für einen Tipp dazu 🙂
Freundliche Grüsse – Kind regards
Hallo Herr S.,
es könnte möglicherweise am Zippen liegen.
Achten Sie darauf, dass Sie „auf der richtigen Ebene“ sind und ALLE Ordner und die XML-Datei markiert haben, also HIER:
(wahrscheinlich haben Sie mehr Ordner)
Versuchen Sie es noch einmal, bitte!
schöne Grüße aus München
Rene Martin
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 …
Hallo Herr Martin,
ich wieß nicht mehr genau, ob ich Sie einmal auf folgende Thematik angesprochen hatte.
Es ging um die Umformatierung von Postleitzahlen aus einer Spalte in Aneinanderreihung
in einer Zelle mit Komma/-Lehrzeichen-Trennung (siehe Anhang „screenshot“):
Diese Schreibweise (nebeneinander) ist die auf unserem content-management-system.
Wenn ich nun größere Zahlenmengen erhalte, wird es händisch sehr aufwendig.
Könnten Sie mir bei diesem Prozedere wieder behilflich sein?
Vielen Dank schon vorab.
Mit besten Grüße
Hallo Herr S.,
welche Excel-Version haben Sie denn? Haben Sie die Funktion TEXTVERKETTEN? DAMIT klappt es ganz einfach:
=TEXTVERKETTEN(„, „;WAHR;E3:E14)
E3:E14 ist natürlich der Bereich der Postleitzahlen. Dann kopiere ich den Bereich an eine andere Stelle und füge ihn als Werte ein – schon habe ich die Liste.
Klappt das?
Liebe Grüße
René Martin
Hallo Herr Martin,
vielen dank für die schnelle Reaktion. Ich habe die 2010’er Version Version 14.7263.5000 (32 Bit)
Direkt das Wort TEXTVERKETTEN ist in den Formeln nicht enthalten.
Gibt es unter dieser alten Version vielleicht trotzdem eine Lösung?
Beste Grüße nach München C. S.
Hallo Herr S.,
oder so:
wiederholen Sie in der ersten Zelle den Wert, bspw.: =E16
schreiben Sie darunter: =F16&“, „&E17
ziehen Sie die untere Formel runter!
das Ergebnis der letzten Zelle können Sie kopieren und an andere Stelle als Wert einfügen
Liebe Grüße
Rene Martin
Einfach genial,
Sie machen einem Freude! ! !
Einfach soo logisch, dass ich mir immer wieder die Frage stelle,
warum komme ich nicht einmal selbst auf solch logische Lösungen.
Ich hoffe, dass ich ihre Geduld und Zeit nicht zu sehr in Anspruch nehme.
Ganz herzlichen Dank. Bleiben Sie gesund und zuversichtlich
C. S.
Gestern habe ich (mal wieder) eine Knobelaufgabe in Excel gepostet. Die Aufgabe lautet:
„Lust auf Knobeln? Mit Excel? Mit Excel-Diagrammen?
In einer Liste stehen Werte. Auf diesen Werten wird ein Diagramm – ein Kreisdiagramm oder Liniendiagramm aufgesetzt. Dumm nur, dass einer oder einige Werte 0 sind. Dumm, dass die Datenbeschriftung angezeigt wird, aber kein Wert: im Kreisdiagramm kein Tortenstück; im Liniendiagramm eine Lücke. Aber natürlich automatisiert – also: wenn sich die Werte ändern, sollen die entsprechenden Werte verwendet werden. „
Die Antwort lautet natürlich, dass man aus 0 ein #NV machen muss – beispielsweise mit einer WENN-Funktion. Es genügt #NV bei den Werten zu verwenden. Das ist die Grundeinstellung der Diagramme:
Ich erhalte die Frage:
„bei meiner Excel-Version sieht das noch anders aus:“
Nachgeschaut. Auf der Seite
steht:
Hinweise:
Dieses Feature ist nur verfügbar, wenn Sie über ein Microsoft 365-Abonnement verfügen und derzeit nur für Insider verfügbar ist. Wenn Sie Microsoft 365-Abonnent sind, vergewissern Sie sich, dass Sie über die neueste Office-Version verfügen.Ah – man (ich) muss auch das Kleingedruckte lesen!
Christian ist irritiert. Zwar übertragen und übernehmen die Arrayfunktionen (SORTIEREN, FILTERN, SEQUENZ, …) nicht die Formatierungen:
Aber wenn man mit dem Gleichheitszeichen auf einen Bereich zugreift, wird in der ersten Zelle das Zahlenformat übernommen, in den übrigen nicht:
Erklärbar – dennoch: irritierend.
Hallo Rene,
da du ja nervige Dinge sammelst, hast du vielleicht auch eine Antwort auf mein Phänomen, was mich einige Nerven gekostet hat,
bevor ich auf eine Lösung gestoßen bin. Anbei ein Screenshot meines Diagramms ( habe mich nicht getraut eine Datei zu versenden) was ich eigentlich mal ganz schnell erstellen wollte,
bis zu dem Zeitpunkt, wo ich den aus einem Fehlerindikator dargestellten dynam. Pfeil ein wenig zurechtrücken (rechts- links verschieben) wollte.
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.
Mit lieben Grüßen in den Süden.
Hallo Jürgen,
Hübsch – DAS kannte ich noch nicht. Du hast recht: HIER wird Komma als Trennzeichen und Punkt als Dezimaltrennzeichen interpretiert. Wahrscheinlich hat an dieser Stelle ein Programmierer geschlafen …
Liebe Grüße und: danke!
Rene
Hallo René,
ich habe ein für mich unerklärliches Excel Phänomen in der Version 365.
In der Zelle C1 habe ich berechnet: 6*7=42 (s. Anlage).
Dieses Ergebnis habe ich zum Datum 18.07.2017 addiert und erhalte das Datum 24.01.6837.
Addiere ich 42 als Zahlenwert zum Datum 18.07.2017, erhalte ich das richtige Ergebnis 29.08.2017.
Hast Du eine Erklärung?
Liebe Grüße
Traudl
Hi Traudl,
du MULTIPLIZIERST das Datum (18.07.2017 = 42.934) mit 42
42.934 x 42 = 1.803.228 = 24.1.6837
Stimmt schon!
Rene
Oh je René, danke.
Dass ich multipliziert habe, ist mir gar nicht aufgefallen. Zudem habe ich die Berechnung mehrfach wiederholt.
Wahrscheinlich Corona Blackout.
Sorry, dass ich Dich mit so einer Lapalie belästigt habe.
Liebe Grüsse
Traudl
Hi René,
ich stehe mal wieder auf dem Schlauch:
Kannst Du mir sagen, wie ich, wenn in einem Tabellenblatt einen Wert aus einem anderen Reiter angegeben ist, in der daneben liegenden Spalte automatisch den Wert aus einer anderen Spalte der Tabelle in dem anderen Reiter ausgebe?
In A2 habe ich per Dropdown mit INDIREKT „<10%“ aus tab_AntwortKategorie[Kategorie].
In B2 möchte ich automatisch den dazugehörigen Wert aus tab_AntwortKategorie[Wert].
Was muss ich in B2 eingeben?
Ohne INDIREKT wüsste ich es, aber wie gesagt – ich stehe einfach auf dem Schlauch.
Momentan „fuhrwerke ich wild (und falsch) rum“, z.B.
=WVERWEIS($AE4;tab_AntwortKategorie[Kategorie];[Wert])
Bye
Michael
Hallo Michael,
wenn du den Wert DANEBEN haben möchtest, musst du den SVERWEIS verwenden, nicht den WVERWEIS. „S“ steht für „senkrecht“, heißt: die Liste ist von oben nach unten aufgebaut.
Schau mal in mein Beispiel:
Hilft das?
Liebe Grüße
Rene
Hi René
aaaaah (über eigene Dummheit)!
Ja, danke.
Bye
Michael
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.
Hallo Rene,
Ich hätte da nochmal eine Frage zu den Matrixfunktionen. Wenn ich die Rückgabematrizen in einen Bereichsnamen packe, funktioniert der auf dem Tabellenblatt wunderbar. Wenn ich allerdings diese Matrix über den Bereichsnamen in einer Datenüberprüfung als Liste ausgeben möchte, kann ich ihn zwar über F3 ansprechen, erhalte dann aber eine Fehlermeldung.
Gruß
Hannes
Hallo Hannes,
du musst den Namen im INDIREKT in einen Bezug umwandeln. Meinst du das?
Liebe Grüße :: Rene
Hallo Rene,
ja genau, da sind einige Haken drin.
Die Liste in der Datenüberprüfung übernimmt offensichtlich nur Werte aus Bereichen die auf einem Tabellenblatt liegen und keine Rückgabewerte aus Funktionen.
Aber wenn ich innerhalb einer „formatierten“ Tabelle einen benannten Bereich anlege passt sich dieser Bereich auf die Tabellengröße an und die Werte können auch in der Datenüberprüfung dynamisch verwendet werden.
Wenn ich über die Sequenz-Funktion Werte ermittle und die in einen Bereichsnamen packe, werden die Werte innerhalb eines Tabellenblattes zurückgegeben, innerhalb der Datenüberprüfung jedoch nicht als Liste übernommen.
Die Problematik ist wahrscheinlich, dass die Datenüberprüfung ausschließlich Werte aus dem Tabellenblatt ausliest, daher auch die Bezugsherstellung mit der Indirekt-Funktion.
Keine Ahnung, ob man das irgendwie austricksen kann.
Gruß
Hannes
PS: hier mein „Versuchsaufbau“ zu dem ich ein bisschen was erzählen könnte. Kannst ja vorab schon mal einen Blick drauf werfen, ob das interessant sein könnte
Hi Rene,
sag mal: kann man in intelligenten Tabellen keine Matrixfunktionen verwenden? Hintergrund: ich möchte gerne mit der Funktion SORTIEREN oder SORTIERNNACH eine Liste sortieren und diese sortierte Liste als Basis für eine Tabelle verwenden. Also so:
Und dann passiert:
Hallo Johannes,
eine kleine Überlegung: (Intelligente) Tabellen „denken“ nur zeilenweise oder in Bezug auf eine ganze Spalte. Also:
=[@Umsatz]*19%
oder:
=SUMME(Tabelle1[Umsatz])
analog:
=TEILERGEBNIS(109;[Umsatz])
DAS kollidiert mit einer Arrayfunktion (EINDEUTIG, SORTIEREN, SORTIENNACH, FILTER), die dynamisch einen Bereich definiert oder ZUFALLSMATRIX und SEQUENZ, in die die Größe eines Bereichs eingetragen wird.
Moin Rene,
Danke Dir für die ergänzenden Infos 🙂 Ich habe es jetzt so gelöst … So hat sich auch der Vorteil ergeben, dass es etwas übersichtlicher ist 🙂
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 jWarum nicht gleich?
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 SelectProblem: 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 SubKlappt! 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 SubIm 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.
Wenn man per VBA auf ein Blatt zugreifen möchte, gibt es zwei Varianten. Entweder über den Namen:
Also beispielsweise
ThisWorkbook.Worksheets("Unternehmen")oder
ActiveWorkbook.Worksheets("Standorte")Problem: Wird die Arbeitsmappe nicht geschützt, kann der Anwender oder die Anwenderin den Namen ändern!
Die zweite Möglichkeit lautet:
ThisWorkbook.Worksheets(1)
ActiveWorkbook.Worksheets(2)Problem: der Anwender oder die Anwenderin kann das Blatt verschieben, wenn die Arbeitsmappe nicht geschützt ist. Außerdem ist die Zählung bei ausgeblendeten Blättern schwierig.
Es gibt noch eine dritte Variante. Im VBA-Editor kann man in den Eigenschaften einen Namen festlegen:
Über diesen Namen kann man auf das Blatt zugreifen, beispielsweise:
tbl_Uebungsplanung.Range("F1").Value = _
tbl_UnternehmenCode.Range("A1").Value ' -- UnternehmenErstaunlicherweise versagt:
MsgBox ThisWorkbook.tbl_UnternehmenCode.Range("A1").ValueAuch 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").ValueSchade! 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…
Liebe Grüße
Peter
Sehr geehrter Herr Martin,
ich habe eben begonnen, Ihre Übungen zu den statistischen Funktionen durchzuführen.
Bei dem Thema Häufigkeit sehe ich eine Differenz zwischen dem, was Sie zeigen und dem, was ich gemacht habe, obwohl die Zahlen, die Sie benutzen, die Zahlen sind, die ich auch genommen habe (ich habe diese Zahlen von Ihrem Beispiel abgetippt).
Ich verstehe nicht den Unterschied, obwohl ich wie Sie die Funktion Häufigkeit verwendet habe.
Das betrifft auch das Ausrechnen der Häufigkeit als Matrix. Auch hier unterscheiden sich die Zahlen.
Zur Überprüfung schicke ich Ihnen meine Datei mit.
Hallo Herr F.,
HÄUFIGKEIT liegt in zwei Varianten vor: als Matrixfunktion und als „normale“. Wenn Sie im „neuen“ Excel HÄUFIGKEIT verwenden, wird es als Arrayfunktion interpretiert: wie viele Daten sind bis zu Wert x, wie viele darüber:
Bei den „älteren“ Excel-Versionen ist dies nicht nötig. So kann ich die Funktion runterziehen. Dazu muss der Datenbereich fixiert werden. Die Daten werden also kumuliert:
Da HÄUFGKEIT matrixfähig ist, muss ich (in denen neueren Excel-Versionen) nicht mehr mit Umschalt + Strg + Enter beenden. Dann werden die einzelnen Bereichen berechnet:
Guten Morgen,
also, es geht um die Funktion „Text in Spalten“. War eigentlich eine Frage eines Kollegen, die ich selbst nicht beantworten konnte.
Ich habe eine CSV-Datei, in der die Werte auch wirklich durch Kommas getrennt sind:
Jetzt ist es so, dass auch innerhalb der Texte, die zu Zellinhalten werden sollen, Kommas drin sind, wie z.B. in Zeilen 1 und 2. Wenn ich jetzt „Text in Spalten“ mit der Option „Getrennt“ auswähle, dann packt Excel natürlich den Teil rechts vom Komma in eine neue Zelle:
Gibt’s ne Möglichkeit, das mit „Text in Spalten“ sauber zu trennen? Mit PowerQuery krieg ich es hin, also saubere Trennung + korrektes Anzeigen von Umlauten, ich frag mich nur, ob das „Text in Spalten“ nicht auch irgendwie kann.
Danke dir und viele Grüße, Dominic
####
Hallo Dominic,
ich denke die Frage hast du schon selbst beantwortet:
* PowerQuery
* VBA
* Formeln (uff!)
Wenn der Anwender Zeichen in seinen Texten verwendet, die als Trennzeichen vorgesehen sind; kann der Assistent Daten / Text in Spalten das nicht abfangen. Wie wir wissen: die Grenzen von „Text in Spalten“ wurden ja in PowerQuery erweitert – da geht mehr …
Liebe Grüße
Rene
PS: Man könnte die Blitzvorschau versuchen; ist aber mühsam und wahrscheinlich funktioniert auch das nicht.
Lieber Herr Martin,
Excel nervt nicht, ich weiß nur noch nicht ob es kann was ich gerne hätte.
Ich müsste Zellen in Spalten verknüpfen, wenn diese einer Bedingung in einer anderen Zelle
Entsprechen. So wie SummeWENN ich diese Zellen zusammenaddieren kann ist die Frage ob
Ich den Inhalt der Zellen auch verkettet ausgeben kann.
Vielen Dank
Bevor ich eine Antwort gebe, Frau L.,
zwei Gegenfragen: sind die Daten sortiert (nach den Kategorien, nach denen sie gruppiert werden sollen) und: haben Sie die Funktionen FILTER und EINDEUTIG? Haben Sie XVERWEIS? (damit geht es „recht einfach“)
Liebe Grüße
Rene Martin
So ist es mir recht,
leider kein XVERWEIS und kein WENNS… hoffe auf ein besseres 2021…
Eindeutig? Index? Geht nicht mit Liste sondern nur Matrix oder Bezug, damit kenn ich mich nicht aus…
Gruppiert nach der Nummer insofern dass sie untereinander geschrieben wurden (händisch…) allerdings Filter vorhanden
Die erste Spalte soll als Referenz herangezogen werden die letzte Spalte beinhaltet die Informationen die ich gerne zusammen verkettet hätte, also
Für 20-44 sollte dort MFM, MFM; CPS, PIB stehen, fantastisch wäre, wenn jeder Wert nur 1x vorkommen würde, aber soweit wage ich nicht zu träumen…
Beim Trennzeichen wäre ich emotional flexibel
Die erste Spalte wird in einem Übersichtssheet sozusagen als Einzeiler ausgegeben.
Vielen Dank
Hallo Frau L.,
ich hoffe, Sie haben die Funktion TEXTVERKETTEN. DAMIT klappt es.
Entweder Sie setzen eine Pivottabelle auf die Liste auf, gruppieren die Daten und verketten dann die Infos der letzten Spalte. Dann haben Sie allerdings Duplikate.
Oder Sie erstellen zwei Pivottabellen: eine für die gruppierten Infos der ersten Spalte und eine mit den Werten der ersten und letzten Spalte. Und verketten so die Texte.
Die erste Formel lautet:
=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($I$1;
VERGLEICH(L2;$A$2:$A$27;0);0;
VERGLEICH(L2;$A$2:$A$27;1)-
VERGLEICH(L2;$A$2:$A$27;0)+1))
Die zweite:
=TEXTVERKETTEN(„;“;FALSCH;BEREICH.VERSCHIEBEN($M$31;
VERGLEICH(O32;$L$32:$L$51;0);0;
VERGLEICH(O32;$L$32:$L$51;1)-
VERGLEICH(O32;$L$32:$L$51;0)+1))
Klappt das? Ist es das, was Sie möchten?
Ich erhalte eine Frage:
„Das Problem ist, daß sich Excel/Windows leider nicht wie beschrieben verhält:
In dem Feld müßte eigentlich 02.12.2020 stehen, es wird jedoch 02.12.1920 angezeigt, obwohl die Einstellung in der Systemsteuerung korrekt ist.
Das Problem tritt auch nur über die Formel auf. Wird das Datum manuell mit zweistelliger Jahreszahl angegeben, dann wird die Ergänzung der ersten zwei Ziffern korrekt vorgenommen …
Ich benötige die Formel zur Umsetzung eines Datumsfeldes aus einem Datenabruf im Format „JJMMTT“. Ich habe mir jetzt mit einer „wenn“-Funktion einen „Workaround“ gebastelt, aber es kann doch nicht sein, daß die Excel-Formel sich nicht an die Windows-Regeln hält …“
Und meine Antwort darauf:
„genau hinschauen! Excel verlangt bei der Funktion DATUM eine Jahreszahl zwischen 1900 und 9999:
So wie beispielsweise der Sinus die Angaben nicht in Grad, sondern im Bogenmaß haben will.
Monat verlangt beispielsweise einen Monat zwischen 1 und 12. Wenn ich die Monatszahl 24 eintrage, rechnet Excel Monat 12 + 12 weitere Monate. Ebenso beim Jahr.
Der Beginn liegt bei 1900. Wenn ich nun 20 eingebe, wird das Datum -1880 Jahre berechnet. Das kann Excel nicht. Eigentlich müsste ein Fehler die Folge sein: #ZAHL – ebenso wie bei
=DATUM(-2021;1;18)
Wahrscheinlich sind die ersten 1.900 Zahlen abgefangen und ins Jahr 1900 transformiert worden.
Eigentlich schon korrekt – gib Excel, was des Excels ist: wir leben im Jahre 2021 und nicht im Jahr 21!
Und: richtig: bei der Eingabe von Zahlen wird der eingegebene Wert immer interpretiert:
12-1-21 wird in Deutschland zum 12. Januar 2021; in den USA zum 01. Dezember 2021. Deshalb drücken wir auch [Enter] und überlassen dem Compiler, was er aus der Eingabe macht.
Liebe Grüße
Rene
Wenn ich in VBA für Excel programmiere, muss ich häufig ermitteln, ob ein Wert in einer Liste vorhanden ist. Die Funktion
Application.WorksheetFunction.CountIf([Spalte],[Wert])also: ZÄHLENWENN, leistet, was ich möchte: ein Befehl (ohne Schleife) und ich habe die Information (Wert in der Spalte oder nicht vorhanden). Ebenso verwende ich häufig SUMMEWENN:
Application.WorksheetFunction.SumIfoder – um die Zeilennummer zu ermitteln VERGLEICH:
Application.WorksheetFunction.Match([Wert],[Spalte],0)So spare ich mir das Schreiben von Schleifen. Nun wollte ich die kumulierten Geldbeträge zu bestimmten Monaten wissen. In Excel lautet die Funktion
=SUMMENPRODUKT((MONAT(A:A)=1)*(B:B))also: summiere die Werte der Spalte B, wenn eine Datumsangabe in der Spalte A ein Datum des ersten Monats (Januar) im Jahr enthält. Klappt wunderbar. Und in VBA? Dort versagt eine Zeile wie:
Application.WorksheetFunction.SumProduct(Month(ActiveSheet.Columns(1) = 1) * ActiveSheet.Columns(1))
Die Ursache ist schnell gefunden: Colums(1) = 1 kann nicht verarbeitet werden; auch nicht Month(Columns(1)); der Gleichheitsoperator in VBA ist nicht matrixfähig; „=“ kann nur identische Dinge vergleichen.
Schade – also doch eine Schleife …
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.“
https://www.tabellenexperte.de/wie-sverweis-nur-richtig/
„Ja“, pflichte ihr bei: „den Artikel habe ich auch gelesen. Ich habe es ausprobiert – ja, er hat recht. Und: seine Lösung funktioniert so!“
Meine Freundin und Kollegin Angelika ruft an:
„Hallo René – ich hab ja während der Weihnachtsfeiertage alles vergessen. Wie geht das nochmal, dass ich Formeln in Werte umwandle?“
„Angelika, das meinst du jetzt nicht ernst? Das weißt du doch: kopieren – Inhalte einfügen als Werte“. Da gibt es ein Symbol mit einem [123].
„Nein, nein, das meine ich nicht. Das kenne ich natürlich. Nein – da gab es doch einen Trick?“
„Ach – du meinst: am Rand mit der RECHTEN Maustaste ziehen und dann fallenlassen. Dann kann man über das Kontextmenü „Hierher als Werte einfügen“:
„Ja! Genau! Das war’s! Danke!“
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 :
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:
Danke an Andreas Thehos für diesen Hinweis
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.
Manchmal muss man ein Problem von einer anderen Seite aus anschauen.
Hat nichts mit Excel zu tun – trotzdem lustig!
Lieber Herr Martin,
Ich muss zugeben, Excel nervt mich normalerweise nicht, aber ev beschäftige ich mich zuwenig
Damit..
Jetzt habe ich einen neuen Job und muss SVERWEISE nachvollziehen, prinzipiell fühle ich mich dazu
In der Lage, allerdings habe ich heute als Spaltenindex folgendes gefunden.
=SVERWEIS($A393;’specs quotation‘!$F$14:$DD$150;F$2;FALSCH)
Was bedeuted F$2
Vielleicht können sie mir ja helfen.
Vielen Dank
Judith
Ps: ich liebe ihre Seite :-J
Hallo Judith,
danke für das Lob.
Die Antwort ist (wahrscheinlich) einfach:
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!
Judith
Ich habe bereits darüber geschrieben. Bill Jelen hat einen Artikel darüber geschrieben:
https:// www .youtube.com/watch?v=iaJv6E0GRC4
Auch auf der Seite von Microsoft ist zu lesen:
https: // support.microsoft.com/en-us/office/-spill-volatile-size-05aad07c-947e-4c9b-bd6f-7b1f8ae6a7dc?fbclid=IwAR3-mdf2DsQoMOTCOt5Z2m2i2ib5_rVYLikGthKeQllPdFZM94Bfbxz_2bc
Eben:
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:
=ZUFALLSMATRIX(5;5;MIN(A2:A100);MAX(A2:A100);WAHR)
Verwendet ZUFALLSMATRIX jedoch die Funktion ZUFALLSBEREICH in den Zeilen oder Spalten, ist #Überlauf ein häufiger Fehler:
=ZUFALLSMATRIX(M11;M12)
In M11 und M12 steht:
=ZUFALLSBEREICH(1;10)
Und schließlich kann auch die Funktion SEQUENZ nicht mit ZUFALLSBEREICH zusammen arbeiten:
=SEQUENZ(M11;M12)
Fazit: Finger weg von den neuen Arrayfunktionen im Zusammenhang mit den Zufallsfunktionen
ZUFALLSBEREICH
ZUFALLSZAHL
ZUFALLSMATRIX
Eben: Dynamic array resizes may trigger […] #SPILL!.
Hallo, i ch benötige kurze Hilfe, ich habe hier ein Makro, noch von meinem Vorgänger, das wohl in einer Excel Liste eine Zeile entfernt und wo anders
dann wieder einsetzen kann. Nun ist es aber so, das ich Zeilen habe, die einen Zeilenumbruch in der Zelle haben, und dann immer eine Fehlermeldung 1004 kommt. Können Sie mir hier BITTE helfen????
Danke
Jörg
Hallo Jörg,
so groß und schwierig kann das ja wohl nicht sein – wenn das Makro vorher gelaufen ist …
schick es mir doch mal, bitte, dann schreibe ich dir etwas dazu.
Liebe Grüße Rene
Hallo Rene,
hier mal so….
In einer Excel Zelle habe ich einen Textumbruch, mit sehr viel Text.
Den kann ich zwar über den Makro „löschen“, aber dann nciht
mehr an einer anderen Stelle einfügen…..
BITTE Um HILFE! DANKE
Sub FahrtAuswaehlenEinfach()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring + " - " + Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring + " " + Sheets("Tabelle2").Cells(i, 4).Value
astring = astring + " --- " + Sheets("Tabelle2").Cells(i, 8).Value
astring = astring + " / " + Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
i = aDlg.ListBoxes("Fahrten").ListIndex
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
End SubZuerst musst ich schlucken und mich auf die Suche machen. Was bitte ist ein DialogSheet? Ich werde fündig: in Excel 4.0 konnte man ein Dialogblatt einfügen. Und darauf Steuerelemente platzieren. Und die verwenden so etwas immer noch im Jahre 2020?
Egal. Meine Vermutung liegt beim Operator „+“. Wenn in den Zellen Zahlen stehen, hat VBA ein Problem beim Verketten von
astring = astring + " " + Sheets("Tabelle2").Cells(i, 4).ValueDas ist aber nicht die Lösung. Ich bitte um die Daten. Der Zeilenumbruch ist nicht das Problem, sondern:
Der Text ist zu lang! Excel lässt mehr als 8.000 Zeichen / Zelle zu, die ListBox leider nur 255
Also schnell den Code geändert:
Sub FahrtAuswaehlen()
Dim aDlg As DialogSheet
Dim aRow As Range
Set aDlg = Sheets("Fahrtauswahl")
i = 1
aDlg.ListBoxes("Fahrten").RemoveAllItems
Do While Sheets("Tabelle2").Cells(i, 2).Value <> ""
astring = Format$(Sheets("Tabelle2").Cells(i, 2).Value, "short time")
astring = astring & " - " & Format$(Sheets("Tabelle2").Cells(i, 3).Value, "short time")
astring = astring & " " & Left(Sheets("Tabelle2").Cells(i, 4).Value, 200)
astring = astring & " --- " & Sheets("Tabelle2").Cells(i, 11).Value
aDlg.ListBoxes("Fahrten").AddItem astring
i = i + 1
Loop
If aDlg.Show = True Then
For i = aDlg.ListBoxes("Fahrten").ListCount To 1 Step -1
If aDlg.ListBoxes("Fahrten").Selected(i) Then
Sheets("Tabelle2").Rows(i).EntireRow.Cut
Sheets("Linienpläne - quer").Select
ActiveCell.EntireRow.Insert Shift:=xlDown
Sheets("Tabelle2").Rows(i).EntireRow.Delete xlUp
End If
Next
End If
End SubDie Antwort:
Hallo Rene,
JA, das wars wohl! Klappt SUPER!!!
TAusend DANK!!!
Wie kann ich mich revangieren?
D A N K E ! ! !
Gruß
Jörg
Ich hätte auch noch ein paar Primzahlen zum Tauschen:
Meine Lieblingsprimzahl zum Beispiel: 997 987 997
Ich erstelle per VBA in Excel dynamische Formulare, in die Daten eingetragen werden. Dabei greife ich auf Mastertabellen zu, die ich einblende, kopiere und modifiziere. Eine der Tabellen hat ein Kommentar (okay – eigentlich eine Notiz). Unter bestimmten Voraussetzungen muss ich ihn löschen.
Okay – ich hätte ihn auch umgekehrt einfügen können – ich entschließe mich für die Variante des Löschens:
ZelleLinksOben.Offset(i, 0).Comment.Delete
Allerdings erhalte ich einen Fehler, wenn in der Zelle kein Kommentar mehr vorhanden ist:
Wie kann ich überprüfen, ob eine Zelle einen Kommentar hat? Die Zelle hat ja keine Comments-Sammlung, so dass ich über die Eigenschaft Count herausbekäme, ob Kommentar oder nicht.
Ich schreibe eine Funktion, die auf einen Kommentar zugreift. Ist dieser nicht vorhanden, erhalte ich einen Fehler. Diesen kann ich abfangen:
Public Function GibtEsKommentar(Zelle As Range) As Boolean
Dim blnVorhanden As Boolean
Dim s As String
On Error Resume Next Err.Clear s = Zelle.Comment.Text If Err.Number = 0 Then
blnVorhanden = True Else blnVorhanden = False End If Err.Clear GibtEsKommentar = blnVorhanden
End Function
Damit kann man (ich) arbeiten:
If RisikoanalyseTyp <> "Dienstleistung" Then
If GibtEsKommentar(ZelleLinksOben) Then
.Comment.Delete ' -- lösche den Kommentar
End If
End IfNach einer Weile entdecke ich, dass es zum Löschen eines Kommentars einen anderen Befehl gibt: ClearComments
ZelleLinksOben.Offset(i, 0).ClearComments
Und er löscht einen vorhandenen Kommentar oder macht nichts, wenn sich in der Zelle kein Kommentar befindet. Warum nicht gleich so?
Böse! Böse! Sehr böse! Auch Outlook kann widerlich sein!
Ich markiere alle Anlagen einer Mail und lösche sie mir [Umschalt] + [Entf]:
Die Meldung:
Dies wird endgültig gelöscht. Okay – ja – löschen!
Allerdings: ich hatte die Mails im Posteingang markiert und endgültig gelöscht. Auch dort die gleiche Meldung:
Dies wird endgültig gelöscht.
In solchen Momenten hat man dann plötzlich sehr viel Zeit. Was tun?
Laptop hochfahren.
Internetverbindung trennen.
Die Mails des Posteingangs in einen anderen Ordner kopieren.
Internetverbindung einschalten.
Schweiß von der Stirn wischen.
Microsoft verfluchen wegen des undifferenzierten Wortes „DIES“.
Hallo René,
hast du das gelesen? 😉
https://www.golem.de/news/microsoft-excel-bringt-endlich-das-ende-von-vba-2012-152627.html
Ich finde ja diese Formulierungen ja immer so bemerkenswert, wenn in der Presse die Rede von Microsoft Excel ist:
„VBA gehört aber zu jenen Sprachen, die nahezu jedem den Verstand rauben und mit denen fast niemand freiwillig programmiert. Wer es dennoch muss und dafür bezahlt wird, wird die Bezahlung eher als Schmerzensgeld betrachten.“
Weiß nicht, ob du den 2. Satz bestätigen kannst. 😉 Viele Grüße, Dominic
Hi Dominic,
nein – das habe ich nicht gelesen. Ich würde ihm erwidern: „Wer so etwas schreibt hat diese Sprache weder gelernt noch verstanden.“
In Corona-Zeiten, in der meine Schulungen weggebrochen sind, bin ich froh, dass ich VBA-Lösungen entwickeln darf. Ich mache das sehr, sehr gerne!
Liebe Grüße
Rene
In der letzten Outlook-Schulung kam folgende interessante Frage:
Wie kann man in Outlook die Aufgabenbereiche „Kalender“ und „Aufgaben“ so einstellen, dass statt „heute“ und „morgen“ das Datum in der Titelleiste steht?
Meines Wissens geht das nicht. Ebenso wenig wie ich im Posteingang diese Texte durch das Datum ersetzen kann:
Hallo ihr Excel-Götter
Ich habe urplötzlich ein nerviges Phänomen.
Wenn ich eine Exceldatei über den normalen Dialog in Excel neu erstelle, ist alles in Ordnung.
Sobald ich aber in einem Ordner per Rechtsklick eine neue Exceldatei erstelle, erhalte ich ein arabisches Layout.
Hat das jemand von euch schon erlebt?
Und WIE löst man das?
Die üblichen .xltx habe ich schon durchforstet….. Nix!
Merci für Eure Unterstützung.
Ein genervter Hp
#####
Hoi HP,
schau mal im Explorer nach:
1. im Ordner %appdata% Roaming\Microsoft\Excel\XLSTART liegen EINIGE deiner Vorlagen:
* eine Datei Mappe.xlsx wird beim Öffnen von Excel gestartet
* eine Datei Mappe.xltx wird DANN über [Strg] + [N] oder Datei / Neu geöffnet
* eine Datei Tabelle.xltx wird verwendet, wenn du ein neues Tabellenblatt erzeugst.
Findest du hier deine Vorlage?
2. schau mal in der Registry nach. Unter
Computer\HKEY_CLASSES_ROOT\.xlsx\Excel.Sheet.12\ShellNew
Findest du den Pfad, hinter dem sich die Mappe verbergen, die über rechte Maustaste / Neu / Excel-ArbeitsBLATT (!?!) geöffnet wird. Bei mir:
C:\Program Files (x86)\Microsoft Office\Root\VFS\Windows\ShellNew\excel12.xlsx
Schau mal nach, ob du diese hast, bzw. wie sie aussieht.
@Tanja: Wenn du eine andere/weitere Lösung hast – her damit! Wir haben vor zwei Jahren mal einen Excelstammtisch zum Thema „Vorlagen“ gemacht und solche Sachen rausgefunden
Liebe Grüße aus dem großen Nachbarkanton
Rene
PS: die Laufrichtung von rechts nach links ist hübsch – ich habe mal ein bisschen arabisch und persisch gelernt und mal (aus Spaß) umgestellt – sehr amüsant!
######
[…] genau die ist es. Letztes Speicherdatum 19.3.2020….. habe ich aber noch nie bemerkt…
Habe unter Optionen/Erweitert die Option wieder auf Links – Rechts Ansicht gedreht
Nun geht es wieder!
Hab DANK für die Unterstützung. Googeln hat nix gebracht.
Ich wünsche ein schönes Wochenende.
Liebe Grüsse
Hp
