Excel-VBA-Schulung. Wir üben das Programmieren von eigenen Funktionen (also function), die in Excel verwendet werden sollen. Ich zeige einen Fehler:
Und erkläre, dass man die Ursache gut finden kann, indem man einen Haltepunkt in Excel setzt:
Dann muss man die Funktion editieren (doppelklick oder [F2]) und sie wird erneut aufgerufen und berechnet:
Allerdings: nichts passiert. Ich brauche eine Weile, bis ich verstehe. Die Parameter sind vom Typ Double deklariert. Einer der Eingabewerte ist jedoch keine Zahl:
Und so wird die Funktion schon direkt nach dem Aufruf abgebrochen und liefert die Fehlermeldung #WERT, ohne dass die Zeile mit dem Haltepunkt erreicht wird. Also flugs den Wert der Zelle in eine Zahl ändern und schon wird der Haltepunkt erreicht.
Ich habe ein mächtiges Werkzeug mit Excel VBA erstellt, in welchem unter anderem Dateien geöffnet werden, Informationen ausgelesen und in die eigene Datei geschrieben werden. So nach dem Motto:
Dim xlDatei As Workbook
Set xlDatei = Application.Workbooks.Open("C:\BIA_contoso_IT.xlsm")
' -- tue etwas
xlDatei.Close SaveChanges:=False
Set xlDatei = Nothing
Ich teste mehrere Male.
Mit Erstaunen stelle ich im Projekt-Explorer fest, dass die Datei mehrmals geöffnet ist:
Ein Blick in Excel unter Ansicht / Fenster wechseln kann dies nicht bestätigen. Und schließlich: eine Datei mit einem bestimmten Namen kann in Excel nur ein Mal geöffnet sein.
Beim sechsten Test erhalte ich folgende wunderliche Meldung:
Ich beende das Programm Excel, öffne – der Spuk ist verschwunden. Ich starte das Programm – die verwunderliche Meldung kommt erneut. Die Ursache ist gefunden: Excel behauptet plötzlich, dass das Tabellenblatt Nummer 3 nicht vorhanden ist!?! In der Datei befinden sich zirka 34 Tabellenblätter, die ich alle „prüfe“.
Automatisierungsfehler: Ungültige Vorreferenz oder Referenz zu unkompiliertem Typ
Ich verstehe es gerade nicht. Habe ich – in meiner aktuellen Version 2201 ein neues Feature entdeckt?
* Ebenso kann es helfen in Datei / Optionen / Erweitert in der Gruppe „Allgemein“ die Option „Inhalt für die Papierformate skalieren“ zu deaktivieren:
* Josef hat festgestellt, dass bei Nichtproportionalschriften (bspw. Courier) der Umbruch häufig bestehen bleibt. Das heißt: diese merkwürdige Umbruch-Geschichte muss etwas mit den Schriften zu tun haben. Vielleicht gibt es andere Schriftfamilien (statt TrueType besser OpenType oder echte Druckerschriften …), die gegen die falschen Umbrüche resistent sind.
Vielleicht – so kam der Vorschlag – sollte man die Wingdings verwenden. Dann kann man zwar nichts mehr lesen, aber dann spielt der Umbruch auch keine Rolle mehr.
Und schließlich erreichte mich noch folgende Mail von Josef:
„Mir ist heute morgen nochmal die Test-Mappe in die Hände gefallen mit den abweichenden Zeilenumbrüchen – hab die wohl nicht zugemacht gestern Abend… und prompt ist die Neugier wach geworden… 🙂
Microsoft scheint das Problem schon ne ganze Weile zu kennen. Schuld ist angeblich Windows, nicht Excel. Auch Programme haben wohl Kommunikationsprobleme.
Den Workaround mit der Zellformatvorlage „Standard“ habe ich ausprobiert, war aber nicht wirklich erfolgreich. Der Text sah zwar anders aus (logisch), die Zeilenumbrüche waren auch anders, aber immer noch falsch… 😛
In einer Excelschulung wollte ich zeigen, dass zwei gesetzte Filter einem logischen UND entsprechen und somit eine Schnittmenge darstellen:
Ich visualisiere das mit zwei Ellipsen:
Und wollte die Schnittmenge deutlich hervorheben, indem ich beiden Formen kombinieren. Aber das Zusammenführen der Formen ist in Excel leider nicht möglich! Ich habe darüber geschrieben:
Ernst hat sich nun die Frage gestellt, ob man so ein Problem nicht mit VBA lösen kann. Konkret: ob man die Formen nicht nach PowerPoint kopieren kann, dort zusammenführen kann und anschließend wieder zurück kopieren kann. Die Antwort lautet: „ja“. Allerdings: einige Dinge gibt es hierbei zu beachten:
Im ersten Schritt wird geprüft, ob mindestens zwei Formen markiert wurden.
Anschließend wird ein Auswahldialog aufgerufen:
Er prüft, ob PowerPoint bereits geöffnet ist:
On Error GoTo Fehler
Set PP_app = GetObject(Class:="PowerPoint.Application")
PP_app.Visible = True 'Stellt die Sichtbarkeit auf an.
PPObjektaufrufen = True 'und setzt die Funktionsrückmeldung auf True.
Exit Function 'Funktion wird verlassen.
Fehler:
PPObjektaufrufen = False 'Die Funktionsrückmeldung wird auf False gesetzt.
Falls nicht, wird es geöffnet:
Set PP_app = CreateObject(Class:="Powerpoint.Application")
PP_app.Visible = True 'Stellt die Sichtbarkeit auf an.
Wichtig hierbei ist, dass PowerPoint sichtbar ist!
Die Formen werden kopiert und eingefügt:
Selection.Copy 'Die selektierten Formen werden in die Zwischenablage kopiert.
Application.ActiveWindow.Selection.ShapeRange(1).Name = OriginalName 'Danach wird der Originalname der erstselektierten Form wieder hergestellt
PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank 'Es wird eine neue Präsentation mit einer leeren Folie erstellt.
'Die Slide.Add-Methode ist eine verborgene PowerPoint-Methode
PP_app.ActivePresentation.Slides(1).Shapes.Paste 'Die Formen werden aus der Zwischenablage in die PP-Präsentation eingefügt.
PP_app.ActivePresentation.Slides(1).Shapes.SelectAll 'und selektiert.
wichtig – die Methode Add der Sammlung Slides ist dies ein verborgenes Element: Diese werden von Intellisense nur dann angezeigt, wenn die Option „verborgene Elemente anzeigen“ bestätigt wurde. Ich habe einmal darüber geschrieben:
Wichtig ist hierbei herauszufinden, welches die zentrale Form ist, also die Form, von welcher die anderen die Formatierung erben:
'PrimaryShape:= Die Form, von der die resultierende Form ihre Formatierung erbt.
'Wenn Fehler auftritt, wird dieser abgefangen
On Error GoTo Fehler1
PP_app.ActiveWindow.Selection.ShapeRange.MergeShapes MergeCmd:=Formform, _
PrimaryShape:=PP_app.ActiveWindow.Selection.ShapeRange("PrimaerForm")
'Fehlerroutine zurückstellen auf Fehler allgemein
On Error GoTo FehlerAll
PP_app.ActivePresentation.Slides(1).Shapes.SelectAll 'die neu erstandene Form wird selektiert.
'Wurde eine Form selektiert, wird diese nach Excel übertragen. Es könnte, beispielsweise bei der Aktion
'Schnittmenge bilden, der Fall auftreten, dass keine Form erzeugt wird.
Das Ergebnis wird zurück nach Excel kopiert:
'Wieviel Formen sind selektiert?
AnzahlFormen = 0
AnzahlFormen = PP_app.ActiveWindow.Selection.ShapeRange.Count 'Anzahl selektierte Formen
If AnzahlFormen > 0 Then 'Ist keine Form selektiert erfolgt keine Aktion.
PP_app.ActiveWindow.Selection.Cut 'Ansonsten werden die selektierten Formen ausgeschnitten und in die Zwischenablage kopiert.
If Not MergeFehler Then ActiveSheet.Paste 'und werden dann aus der Zwischenablage nach Excel kopiert.
End If
PowerPoint wird geschlossen:
If PPschonGestartet Then 'Wurde keine neue PP-Instanz gestartet.
PP_app.ActivePresentation.Close 'wird nur die neu erzeugte PP-Präsentation geschlossen
Else
If NeuePPwiederSchliessen Then
PP_app.Quit 'Steht dieser Schalter auf True wird PP wieder geschlossen.
End If
End If
Set PP_app = Nothing ' Objektvariable wird auf Nothing gesetzt
Exit Sub
Ein beachtliches Werk mit vielen Fallstricken – vielen Dank an Ernst Börgener.
Der Urheber behauptet dass das korrekt ist und keinen Fehler darstellt.
Ich würde mich herzlich freuen, wenn wir kurz miteinander sprechen können.
Schon jetzt herzlichen Dank.
Mit freundlichen Grüßen
####
Hallo Herr P.,
welchen Wert hat denn „MenueName“? Wenn Sie mit der Maus über diesen Variablennamen beim Debuggen fahren – was wird denn angezeigt? Und was bei „SubMenueName“?
Umgekehrt: Arbeiten Sie noch mit Menüs? Die sind doch seit Excel 2007 verschwunden? Ich verwende seit vielen Jahres das Ribbon und füge dort Symbole ein.
danke für den Code. Mir fehlt allerdings immer noch ein Befehl: ein anderes Makro ruft dieses Makros auf und löscht das Symbol in der Symbolleiste. Ich weiß nicht welches, weil ich diese Variable nicht „sehe“.
Im Makro
Sub Menue_Loeschen_ZV()
lautet die erste Zeile
On Error Resume Next
also auf Deutsch: sollte ich – das Makro – das Symbol nicht löschen können (Controls(MenueName).Delete) – beispielsweise weil es schon gelöscht wurde … na: dann mach halt gar nichts! Auch keine Fehlermeldung! Ist völlig okay so!
Jedoch im Makro
Sub Menueeintrag_loeschen_ZV()
fehlt diese Zeile.
Tipp: Fügen sie nach der Sub-Zeile einfach die On-Error-Zeile ein (wurde wahrscheinlich vergessen), also so:
„Sadly, there is no way to test it at this point, and we can only rely on the results of the previous tests and do debugging later if needed.“
Auch Mourad Louha schreibt:
„Ich bin wirklich sehr gespannt, was das Excel-Team in den kommenden Wochen und Monaten an Verbesserungen zu den LAMBDA-Funktionen zur Verfügung stellen wird.“
Wie weit ist es von München nach Moskau? Und nach Madrid?
Da ich nächste Woche einen Vortrag über die neuen LAMBDA-Funktionen NACHZEILE, MATRIXERSTELLEN, WURDEAUSGELASSEN, REDUCE, .. halte, probiere ich ein wenig. Und habe folgendes interessantes Beispiel gefunden.
Die Koordinaten von München (beispielsweise Marienplatz) sind
herausfinden. Die Entfernung zweier Punkte kann man nicht mit dem Satz des Pythagoras berechnen, sondern mit Hilfe von sphärischer Trigonometrie. Ein Blick in die Formelsammlung oder ins Internet liefert die Lösung:
Da Sinus und Cosiuns von einer Einheitskugel ausgehen, muss das Ergebnis mit dem Radius der Erde (ungefähr 6.380 km) multipliziert werden. Und da Excel mit der Funktion BOGENMASS diese Angaben in GRAD umrechnet, lautet die Formel:
Das kann man doch sicherlich mit den neuen Arrayfunktionen, beispielsweise mit LAMBDA und REDUCE abkürzen. Da zwei Mal der COSINUS verwendet wird und ein drittes Mal der Cosinus einer Differenz, ermittle ich die Differenz unterhalb der Daten:
… erhalte einen Fehlerwert. Die Ursache ist schnell gefunden. Ich muss nicht nur das Dezimaltrennzeichen von Punkt in Komma ändern, sondern auch die (unsichtbaren) Leerzeichen, die auf der Homepage vor den Zahlen eingetragen waren, entfernen. Dann klappt es:
Dann funktioniert es. Nach Madrid sind es von München aus „nur“ 1.486 km – ist also näher als Moskau.
Seltsam. Manchmal – aber nur manchmal verschwindet das Kästchen zum Herunterziehen, wenn eine Zelle markiert ist:
Immerhin: es erscheint sofort wieder, wenn ich auf eine andere Zelle klicke.
Nachvollziehen kann ich auch nicht folgendes Phänomen: Trage ich in eine Zelle eine Formel ein, wird die Formel grau hinterlegt in der Zelle angezeigt:
Auch dieser Spuk verschwindet bald wieder. Seltsam …
Dir zum Jahresanfang alles Gute – bleib gesund und für die Arbeit (notwendiges Kleingeld) viel Erfolg!!!
…und ich stelle mich im Neuen Jahr ganz schön doof an. So würde ich gern Deine Hilfe annehmen.
In meinen Programmen will ich etwas Neues probieren und es gelingt mir nicht.
Bisher funktioniert alles prima […] Alles ist gut!
Nun: Mein Wunsch wäre eine etwas komplexere Auswertung, die ich vorher erstellt habe und der in einer Datei Auswertung.xlsx liegt. Diese Arbeitsmappe kann ich nun per Programm über Application.Workbooks.Open „C:\Pfad\Auswertung.xlsx“ öffnen – das funktioniert.
Aber jetzt: Jetzt möchte ich diese Mappe für die weitere Benutzung zuweisen (ich will diese Mappe ja per Programm bearbeiten) Set xlsDatei(i) = ?????? und da geht es nicht weiter…
Es ist bestimmt nur eine Kleinigkeit, aber ich habe mich irgendwie festgebissen…
Ich danke Dir schon im voraus für Deine Hilfe!
Liebe Grüße Wolfgang
####
Äh ….
Set xlsDatei(i) = Application.Workbooks.Open(„C:\Pfad\Auswertung.xlsx“)
Moin Wolfgang,
DAS erschüttert mich! Das weißt du doch selbst: Methoden haben in VBA zwei Schreibweisen: Leerzeichen, wenn etwas ausgeführt wird (zöffne die Datei) und Klammer, wenn etwas an eine Variable übergeben wird (… und speichere es als xlsDatei).
In der letzten Excelschulung wurde ich gefragt, ob man Pivottabellen auf einem geschützten Tabellenblatt erzeugen, ändern und aktualisieren kann?
Ich gebe zu – ich war ein bisschen unsicher.
Und das sind die Antworten:
Auf ein schreibgeschütztes Blatt kann keine Pivottabelle eingefügt werden. Auch dann nicht, wenn alle Optionen zum Zulassen aktiviert sind. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Eine Aktualisierung ist nicht möglich, wenn das Blatt geschützt ist. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Wurde beim Blattschutz die Option “ PivotTable und PivotChart verwenden“ aktiviert wurde, kann man die Felder in die Zeilen, Spalten, Filter, … ziehen und von dort wieder entfernen – auch wenn die Zellen gesperrt sind.
Wurde das Tabellenblatt mit der Datenquelle geschützt, kann man keine Pivottabelle erstellen:
Geneigte Leserin, verehrter Leser, hat jemand von euch eine Idee? ####
Hallo René,
Jetzt habe ich eine Sache, die ich nicht wirklich gebacken bekomme. Ich habe bisher keine Möglichkeit gefunden, eine Frage zu stellen, die dann jemand vorbereitet beantworten kann. Nachdem die Lösung (wenn sie vorhanden ist) wahrscheinlich umfangreicher ist, frage ich mal einfach an, ob ja jemand weiter helfen kann.
Ich hänge an der „AutoFit“-Funktion für Zeilenhöhen. Das Problem ist eigentlich ein alter Bekannter: Man hat einen Text, der länger ist als die Zelle es ermöglicht, stellt die Zelle auf „Zeilenumbruch“, und weist Excel entweder händisch (Doppelklick auf Zeilenhöhe) oder per VBA (AutoFit) an, die passende Zeilenhöhe einzustellen. Jetzt ist Excel aber kein Layoutprogramm, und macht nur WYSI ungefähr WYG. Die Zeilenhöhen sind auf dem Bildschirm nicht immer wirklich passend, manchmal sind es zu große Zeilen (zu kleine eher selten).
Noch schlimmer wird es, wenn ich auf die Druckvorschau (bzw. später den Druck) gehe. Da werden dann die Zeilenumbrüche neu gesetzt (in der Regel passt in die Zeile im Druck mehr als auf dem Bildschirm), und die Zeilenhöhe passt dann noch weniger als vorher.
Zu allem Überfluss scheint das Ergebnis auch vom verwendeten Drucker abzuhängen. Natürlich könnte ich jetzt die Zeilenhöhen manuell nacharbeiten, aber bei längeren automatisch erstellten Dokumenten ist das echt mühsam und nicht gerade geeignet, das einem Kunden zu verkaufen. Vor allem, wenn da die Seitenumbrüche dran hängen.
Kennst du oder jemand dazu eine befriedigende Lösung, mit der sich sicher passende Zeilenhöhen erstellen lassen?
Schöne Grüße
Peter
Hallo Peter,
[…]
Zu „autofit“. Ich
kenne das Problem, habe es aber nicht eingrenzen können.
Autofit ist eine
Methode – sie macht einmalig und keine Eigenschaft, die man vielleicht mit ein
paar Parametern überlisten könnte …
Mir ist auch
aufgefallen: Manchmal (in letzter Zeit seltener) ist der Umbruch in der
Seitenansicht nicht der gleiche wie in der Normalansicht.
Ich habe ab und zu
in VBA-Programmierungen „kleine“ Lösungen gebaut („suche“ die
Überschriftszeilen und sorge dafür, dass sie nicht am Ende der Seite stehen
oder erhöhe die Zeilenhöhe vor dem Speichern als PDF um 1 pt …)
Im Bild oben fehlt seit ein paar Tagen der Eintrag Makros, wie
auch beim Menüband. Haben Sie eine Idee, wie ich das wieder herstellen kann?
Ich danke Ihnen schon jetzt für Ihre Unterstützung:
Mit freundlichen Grüßen
WK
Hallo Herr K.,
m.W. ging das noch
nie in Visio. Der Grund: in Word kann ich Makros in der normal.dotm, in Excel
in der Personal.xlsm speichern, so dass sie immer zur Verfügung stehen. Visio
ist vorlagen- d.h. dateibasiert. Da Symbole in der Symbolleiste für den Schnellzugriff
immer sichtbar sind, sollten sie nicht Makros verwenden, die in einer
bestimmten Datei liegen.
Ich habe gelacht. Für die nächste Schulung, bei der mehrere Dutzend Teilnehmerinnen und Teilnehmer geschult werden sollen, hat der IT-Leiter eine Namensliste angelegt: wer aus welcher Abteilung sich für welche Schulung eingetragen hat.
Und hier ist die Unterschriftliste, sagt er und schmunzelt: ich habe doch keine Lust die Namen per Hand einzutragen. Deshalb habe ein eine kleine Formel geschrieben. Alles andere würde doch nur nerven.
Hallo ich brauch bitte
mal Hilfe bei bedingter Formatierung!
Kann man wenn eine Zelle
automatisch die Farbe rot erhält über die bedingte Formatierung dann da
automatisch einen Buchstaben mit einfügen ?
Vielen Dank für eure
Hilfe
=======
Du kannst eine bedingte Formatierung mit Hintergrundfarbe, Schriftfarbe und einem (benutzerdefinierten) Zahlenformat versehen. Beispielsweise „Rot“ – dann wird dieser Text angezeigt, wenn die Bedingung erfüllt ist.
PowerQuery stellt für Zahlenformate alle (nur denkbaren) Varianten auf Basis der Gebietsschemata zur Verfügung. Allerdings fehlt die ISO-Norm bei der Kalenderwoche.
Okay.
Wir haben eine Liste mit Ländernamen, die sortiert werden:
Es fällt auf, dass PowerQuery streng nach Groß- und Kleinschreibung sortiert. Deshalb steht die USA vor Ungarn:
Das kann man mit dem Befehl each Text.Upper korrigieren:
Aber: Österreich befindet sich am Ende. Das Alphabet wird US-amerikanisch sortiert. Und: der Befehl Sort stellt keinen Parameter zur Verfügung eine Länderkennung einzutragen. Im Deutschen wird a < ä < b sortiert, im Spanischen a < b < c < ch < d … < l < ll < m < n < ñ < o …
Für jedes Land, das heißt: für jede Sprache müsste man eine Hilfstabelle anlegen. Sehr mühsam!
Schöne Frage gestern in der Outlook-Schulung: Wie kann ich eine Regel erstellen, bei der alle Mails, bei denen ich auf BCC gesetzt wurde, in einen bestimmten Ordner verschieben?
Ich weiß es nicht. Geht wahrscheinlich nicht. Oder?
Hallo Herr Martin, im Anhang sende ich Ihnen eine Exeltabelle mit einer „mauell erstellten Kopfzeile“ und einer Zeilenschaltung in der Zelle „Anschrift“. Für einen Serienbrief benötige ich die „Kopzeilen“ ebenso die Zeilenschaltung nicht. Wie entferne ich am schnellsten die „Kopfzeilen“ und die Zeilenschaltung in der Zelle. (Teilenschaltung in einer Zelle zu entfernen, habe ich in Ihren Videos schon gefunden). Ich möchte alle Daten in einer Spalte haben. Ich würde mich freuen, wenn Sie mir dabei helfen würden. Mit freundlichen Grüßen PV (Ein Fan Ihrer Office-Kurse)
Hallo Herr V.,
das habe ich
gemacht :
* Mit Suchen und Ersetzen die Zeichenschaltung (Strg + J)
durch einen Schrägstrich ersetzt.
* den Verbund aller verbundenen Zellen aufgehoben
* den Textumbruch entfernt
* mit einem AutoFilter in der Namensspalte den Text „Name“
und die leeren Zellen gefiltert und entfernt
Schade! In PowerPoint kann man „Formen zusammenführen“, also: vereinigen, kombinieren, in Einzelmengen zerlegen, Schnittmengen bilden oder subtrahieren:
Auf der (Computer-)Tastatur gibt es zwei Tasten für [Enter]. Aber mit der rechten [Enter]-Taste kann man keinen Zeilenumbruch, keine Zeilenschaltung in Formen erzeugen:
Ich öffne im Windows-Explorer das Eigenschaftenfenster einer Datei und wechsle auf die Registerkarte „Sicherheit“. Dort finde ich den Dateinamen mit Pfad, den ich markiere und nach Excel kopiere. Achtung: Ich markiere von rechts nach links:
Ein zweites Mal – jetzt wird von links nach rechts markiert und anschließend kopiert:
Ich kopiere beide Varianten nach Excel – die erste ist oben, die zweite unten. Ich ermittle die Anzahl der Zeichen mit LÄNGE und bin erstaunt. Ich löse das erste Zeichen mit der Funktion LINKS heraus und bin wieder erstaunt:
Wandelt man das Zeichen vor dem Laufwerksbuchstaben D mit Code in den ASCII-Code um und mit ZEICHEN wieder zurück, so erhält man ein „?“
Ich bin erstaunt.
Noch schlimmer wird es, wenn man mit PowerQuery und diese Access-Datenbank zugreift
und den Pfad durch den ersten Text ersetzt:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
lautet die Fehlermeldung. Man sieht das Zeichen nicht – weder in Excel noch in Word, im Editor oder in PowerQuery. Und wundert sich über diese merkwürdige Fehlermeldung.
Da gibt es nur eine Lösung: Will man den Dateinamen mit Pfad ermitteln, muss man im Explorer über das Symbol „Pfad kopieren“ den Verzeichnisnamen und Dateinamen in die Zwischenablage kopieren. (danke an Martin Weiß für diesen Tipp)
Es fing harmlos an. Ein Anruf am Wochenende. Ob ich mal kurz helfen könne. „Worum geht es denn?“ In einer Liste tauchen Werte mehrmals auf – sie sollen auf einer Serienbrief-Seite stehen. „Machen Sie doch eine Pivottabelle“, war meine lapidare Antwort. Ganz so einfach gestaltet sich das Problem allerdings nicht.
Die Banken (aus der letzten Spalte) wiederholen sich, sie sollen gruppiert werden und zu jeder Bank alle Kunden aufgelistet werden, die bei ihr Mitglied sind. Mit weiteren Informationen.
Für das Gruppieren beginnen wir mit der Funktion EINDEUTIG, entscheiden uns aber später für eine Pivottabelle, weil man hier am leichtesten filtern kann:
Alle Kunden sollen aufgelistet werden. Warum nicht mit FILTER?
Allerdings sollen die Daten ja nicht untereinander, sondern in einer Zelle stehen. Also muss man die Texte verketten. Die Funktion TEXTVERKETTEN hilft hierbei:
Außerdem soll noch ein bestimmter Datumswert gefiltert werden. Das Jahr wird ausgelagert. Man könnte mit der Funktion JAHR arbeiten – wir entscheiden uns für einen ZWISCHEN-Bereich, also >= und <=
Kennst du Anrufe, die beginnen mit einem „hast du mal einen Moment Zeit?“ oder: „störe ich gerade?“
Richtig: Angelika rief am Wochenende an. Meine Kollegin Angelika. Sie bereitet gerade einen Excel-Makro-Kurs vor und suchte einige gute Gedanken für Befehle, die man mit dem Makrorekorder aufzeichnen könne. Dabei stieß sie auf die benutzerdefinierte Kopfzeile.
„Sag mal“, fragte sie, „wenn ich in der Kopfzeile aufzeichne: Seitennummerierung, Datum, Dateiname und Tabellenblattname passiert etwas ganz Komisches.“
Zuerst glaubte ich es nicht. Also – auch aufgezeichnet:
Eigentlich alles okay. Ich habe keine Ahnung, was hier passiert!
Ich rate Angelika, im Makrokurs nur den Firmennamen als Text aufzuzeichnen. Das klappt!
Okay – für Datum und Uhrzeit stehen die VBA-Befehle Date und Time zur Verfügung; für den Dateiname ActiveWorkbook.Name (oder Fullname), für den Blattnamen ActiveSheet.Name, aber für die Seitennummer? Ich weiß keine Lösung.
Danke an Angelika Meyer für diesen Hinweis!
Und nur wenig nach der Veröffentlichung des Artikels erreicht mich ein Kommentar von Ernst. Großartig – DAS ist des Rätsels Lösung! Hier sein Kommentar:
Hallo Rene,
dieses eigenartige Verhalten tritt bei mir (Excel 2019) nur dann auf,
wenn vor dem Festlegen der PageSetup-Eigenschaften die
Application.PrintCommunication-Eigenschaft auf False gesetzt wird. Wenn
die Kommunikation mit dem Drucker nicht abgeschaltet wird, tritt dieses
Verhalten nicht auf.
Bei der Makroaufzeichnung werden die Befehle
Application.PrintCommunication = False und Application.PrintCommunication = True
verwendet.
Bei Microsoft steht wohl folgender Hinweis:
Legen Sie die PrintCommunication-Eigenschaft auf False fest, um die
Ausführung von Code zu beschleunigen, mit dem PageSetup-Eigenschaften
festgelegt werden.
Legen Sie die PrintCommunication-Eigenschaft nach dem Festlegen der
Eigenschaften auf True fest, um alle im Cache vorhandenen
PageSetup-Befehle auszuführen.
Ich weiß, dass beim Arbeiten mit dem Funktionsassistenten bei der Funktion ZÄHLENWENN ein Bildschirmfehler (Grafikfehler) auftritt. Ich habe ihn bereits beschrieben:
Allerdings trat dieser Fehler bei einem Teilnehmer auch bei der Funktion WENN auf – er wollte zwei WENN-Funktionen ineinander verketten:
Vorgestern erhielt ich die Frage, wo denn die Symbolleiste für den Schnellzugriff sei. Da ich keinen Blick auf das entsprechende Excel werfen konnte. vermutete ich, dass sie unter dem Menüband eingeschaltet war.
Weit gefehlt: der Anwender hatte sie ganz ausgeschaltet. Böses, kleines Kontrollkästchen!
In einem Formular steht eine Formel. Okay – es befinden sich eine Reihe an Formeln dort – aber einige sind besonders lang. Manchmal soll die Zelle mit einem Wert überschrieben werden und nicht mehr variablen sein (wenn bestimmte Voraussetzungen erfüllt werden, welche die Formel nicht abbildet); dann wiederum soll die Formel zurückgesetzt werden. Also gehe ich auf die Suche – wie heißt der VBA-Code der Formel. Die Berechnung lautet:
Excel-Schulung. Ich beginne mit den Funktionen SUMME, ANZAHL, MITTELWERT & co. Danach die wohl zweitwichtigste Gruppe: Funktionen der Kategorie „Logik“. Ich zeige, dass man WENN tippen kann oder über den Funktionsassistenten, den man in Formeln in der Gruppe „Logik“ eingeben kann.
Völlig entgeistert starre ich auf die angebotenen Funktionen. Waren die ALLE gestern schon da?
Guten Morgen zusammen,
vielleicht könnt Ihr mir helfen
Ich habe für meine
Masterarbeit Wetterdaten bekommen und würde jetzt gerne aus den Zahlen für die
Windrichtung den Text „Nord“, bzw. „Ost“ usw. generieren.
Dabei ist
0<=x<45 =>
„Nord“
45<=x<135 =>
„Ost“
135<=x<225 =>
„Süd“
225<=x<315 =>
„West“
315<=x<380 =>
„Nord“
Dafür hab ich folgende
WENN-Funktion verwenden wollen:
Gestern in der Excelschulung. Wir besprechen den Autofilter. Ich erkläre die Option „Daten haben Überschriften“ beim Assistenten „benutzerdefiniertes Sortieren“.
Ein Teilnehmer fragt, warum bei ihm diese Option ausgegraut, also inaktiv sei:
Die Antwort ist schnell gefunden: der Autofilter wurde eingeschaltet – dadurch wird die erste Zeile als Überschriftszeile definiert.
Gestern in der Excelschulung. Ich erkläre den Autofilter. Wir haben eine Liste mit zirka 12.000 Datensätzen. Wir filtern alle Hamburger und Hamburgerinnen:
Zu der gefilterten Liste fügen wir alle Personen hinzu, die in Flensburg wohnen:
Und so machen wir weiter mit Bremen, Husum, Kiel, Buxtehude, Uelzen, Itzehoe, …
Ein Teilnehmer meldet sich und fragt, wie und ob man denn erkennen könne, welche Orte gefiltert seien:
Ich habe eine Weile überlegen müssen. Fährt man mit der Maus über das Filtersymbol, werde alle Filterkriterien im Quickinfo angezeigt:
Nennen wir ihn B. B. kann für Björn stehen. Oder für Benno. Für Benjamin oder für Boris. Egal. Wir nennen ihn B.
B. ist Teilnehmer meiner Excelschulung und stellt eine Frage zum Aufbereiten von CSV-Dateien, die er in regelmäßigen Abständen erhält. Er denkt an eine VBA-Lösung – ich schlage PowerQuery vor. Die Datei wird aufgerufen, transformiert und nach Excel zurück geschrieben.
Allerdings: der Pfad, beziehungsweise der Dateiname soll variabel sein. Eigentlich kein Problem, denke ich, und lasse B. Pfad und Dateiname in die Excelmappe schreiben, mit einer Überschrift versehen und in eine (intelligente) Tabelle umwandeln.
Beide Tabellen werden nach PowerQuery gezogen, und dort mit einem Drilldown in einen Text verwandelt. Sie werden in dem Befehl
File.Contents
verwendet; die Sicherheitsstufe dieser Arbeitsmappe wurde ignoriert. Und dann das Erstaunliche:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
Stirnrunzeln.
Probieren. Beispielsweise Pfad und Dateiname in PowerQuery (oder in Excel) zu verketten und diese Zeichenkette zu verwenden. Beides schlägt fehl:
Immer wieder die gleiche Fehlermeldung:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
In Ruhe, alleine, und ohne B. schaue ich mir die Zeichenkette genau an und probiere. Erstaunt stelle ich fest, dass das erste Zeichen nicht der Laufwerksbuchstabe ist. In Excel kann man das mit der Funktion LINKS oder TEIL ermitteln. Der ASCII-Code lautet 63 – eigentlich ein Fragezeichen.
Ich überlege, probiere und frage B. Er hat eigentlich nur den Namen des Verzeichnisses aus den Dateieigenschaften kopiert. Und ich habe ihm zugesehen.
Ich weiß nicht, wie dieses merkwürdige Zeichen in die Excelzelle gelangt ist. Ich weiß, dass Excel bei einigen Zeichen (geschützte Leerzeichen, bedingte Trennstriche, …), die man über Word, Outlook oder eine Webseite nach Excel kopieren kann, Probleme hat. Aber hier? Keine Ahnung.
Lösung des Problems: Pfad neu tippen – und dann klappt es!?!
Sicherlich hätte ich sehr, sehr lange gesucht. Und mich gewundert. Zum Glück hat mir Tanja Kuhn geholfen. Beziehungsweise mich schon vorher aufmerksam gemacht.
Die Aufgabe: in einem Wordformular soll dynamisch, das heißt per VBA, die Kopfzeile (und auch die Fußzeile) ausgetauscht werden:
Obwohl weder das Dokument noch einer der Abschnitte geschützt ist, erhalte ich eine Fehlermeldung:
Die Antwort: das Bild befindet sich in einem Inhaltssteuerelement und das wurde im Entwurfsmodus in den Eigenschaften geschützt. Folglich kann auch nicht die Kopfzeile gelöscht werden …
Könntest du dir das mal durchlesen, eventuell kennst du eine Lösung.
Ich öffne aus einer Exeltabelle heraus eine weitere Exceldatei. Dies ist ein Bestellformular.
Dort trage ich dann bestimmte Werte ein in die entsprechenden Felder.
Ich öffne diue Datei folgendermassen:
Jetzt ist es aber so das beim Oeffnen der Datei eine MsgBox aufgeblendet wird die sagt man muss einen Vorgang auswählen.
Das ist auch sinnvoll wenn man die BANF(Bestellanforderung) händisch ausfüllt.
Aber nicht bei einem Automatismus.
Ausgelöst wird die MsgBox durch das Event „Worksheet_Change“
Ich frage mich ob es eine Mögliichkeit gibt, die Tabelle so zu öffnen das alle Makros deaktiviert sind und bleiben.
Ich hatte das was gelesen das es so gehen sollte:
Public
Function MinusNull()
MinusNull = -0#
End Function
Durch den Aufruf =MinusNull() wird eine negative Null in der
Zelle eingetragen. Diese negative Null kann nun als reiner Wert in andere
Zellen kopiert werden.
Wenn
diese negative Null in A1 kopiert und in B1 eine einfache Null eintrage wird,
ergeben sich folgende Ergebnisse bei einem Vergleich dieser Zellen.
-0
0
Formeltext
Wert
=A1=B1
FALSCH
=A1<B1
WAHR
=VORZEICHEN(A1)
-1
=VORZEICHEN(B1)
0
=TEXT(A1;“0,00000000000000000″)
0,00000000000000000
Die
TEXT()-Funktion unterschlägt das Minus-Zeichen. Bei allen anderen Vergleichen
gibt es einen Unterschied zwischen einer negativen und der normalen Null.
Ob die Möglichkeit eine negative Null in eine Excelzelle einzutragen Sinn
macht ist allerdings fraglich.
In dem beigefügten Beispiel kann ich
nicht nachvollziehen, warum Ergebnisse Null und einige negativ Null sind.
Du hast sicher eine Idee . Danke.
Beste Grüße
Traudl
Hallo Traudl,
lass dir mal ganz viele Nachkommastellen anzeigen. Dann siehst du in A2, B2 und C2 weiterhin ,90 und ,12 und ,22 aber in D2 befindet sich ein Rundungsfehler: ,000000000203727
Auch mit der Funktion =TEXT(D2;“0,000000000000000″) kann man es sich anzeigen lassen.
Liebe Grüße
VBA-Schulung. Eine Teilnehmerin sieht ihren Fehler nicht und bittet mich zu helfen. Ich schaue mir den Code an:
Böse Leerzeichen! Der Fehler ist schnell gefunden: Am Ende des Tabellenblattnamens hatte sie aus Versehen ein Leerzeichen geschrieben, also statt Set xlTabelle = xlDatei.Sheets(„Almodovar“)
VBA-Schulung. Eine Teilnehmerin fragt mich, warum sie keinen Button mehr einfügen kann:
Meine erste Vermutung: Cursor sitzt in der Zelle. Nein!
Meine zweite Vermutung: ein Makro läuft noch: Nein!
Dann fiel es mir ein: „Drück mal [Strg] + [6]!“ Das war die Lösung!
Mit der Tastenkombination [Strg] + [6] wird die Anzeige von Bildern, Diagrammen, Formen, …. unterdrückt. Und also auch die Anzeige von Buttons.
Wie hat sie das gemacht? Wir haben vorher das Thema „Zahlen Formatieren“ und Aufzeichnen mit dem Makrorekorder behandelt. Ich habe die Tastenkombination [Umschalt] + [Strg] + [6] für das Zahlenformat „Standard“ gezeigt. Wahrscheinlich hat sie [Strg] + [6] gedrückt – damit werden Bilder ausgeblendet.
Man kann diese Einstellung auch über die Optionen deaktivieren:
Volker zeigt mir eine Fehlermeldung in Excel, die er noch nie gesehen hat:
Für Excel waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.
Wir sind erstaunt.
Ich probiere ein bisschen.
Ha – es gelingt mir den Fehler zu reproduzieren:
Ich erstelle in einer leeren Mappe ein zweites Tabellenblatt, beziehe mich auf dem zweiten Blatt in der Zelle A1 auf einen Bereich des ersten Blattes:
=Tabelle1!A:RD
Excel schafft es nicht diese 472 x 1.048.576 Zellen zu verknüpfen. Die Meldung „Für Excel waren beim Berechnen einer oder mehrerer Formeln nicht genügend Ressourcen vorhanden. Daher können diese Formeln nicht ausgewertet werden.“ ist die Folge:
Es funktioniert natürlich auch mit anderen Bereichen, beispielsweise
Gestern habe ich ein Referat über „leere Zellen“ gehalten. Ich habe gezeigt, dass man eine leere Zelle durch einen Wert ersetzen kann – beispielsweise durch 0:
Klappt: der Suchen und Ersetzen-Dialog tut gute Dienste:
Sieben leere Zellen werden mit der Zahl 0 gefüllt.
Auch umgekehrt funktioniert es: Ersetze 0 durch „nichts“, also leere Zellen, in denen die Zahl 0 steht:
Ich stutze: warum werden jetzt zehn Änderungen vorgenommen?
Klar – ersetzt werden nicht die Zellen, in denen die Zahl 0 steht, sondern die Ziffer 0 wird gelöscht. Auch 70 wird 7. Kurz überlegt.
Klar: man muss die Option „Gesamten Zellinhalt vergleichen“ aktivieren:
Excel ist nicht konsistent im Umgang mit leeren Zellen. In den meisten Funktionen wird eine leere Zelle als 0 interpretiert. Aber eben halt nicht immer. Gestern habe ich mich mal wieder geirrt.
Ich wollte die Funktion HÄUFIG mit der Funktion ZÄHLENWENNS nachbauen – wollte die Bereiche definieren.
Mit der Funktion HÄUFIGKEIT (als Matrixfunktion) kann man berechnen, wie viele Daten in den entsprechenden Klassen vorhanden sind:
Kennen Sie die beiden Symbole „Dezimalstelle hinzufügen“ und „Dezimalstelle entfernen“? Verwechseln Sie diese beiden Symbole auch regelmäßig?
Greg Nash (https://www.dearwatson.net.au/) gibt einen Tipp:
To add or remove decimal places in #Microsoft #Excel first click on the WRONG button several times, then click on the correct button twice as much as you had to.
Schöne Frage in der letzten PowerQuery-Schulung: warum kann man eigentlich keine Duplikate ermitteln lassen? Oder – wie in Excel – Duplikate löschen lassen?
Stimmt – DAFÜR gibt es in PowerQuery leider keinen Assistenten. Muss man „per Hand“ machen.
Tanja Kuhn schreibt: „Das geht beides. Duplikate löschen per Rechtsklick. Duplikate anzeigen über Gruppierung.“
Danke für den Hinweis – zur Gruppierung hätte der Teilnehme, der sich so eine Option beim Import der Daten gewünscht hatte, sicherlich angemerkt, dass man es dann auch „Duplikatensuche“ nennen sollte. Das „Duplikate löschen“ habe ich glatt übersehen / vergessen … (ich schäme mich! *lach*)
Der Teilnehmer dachte übrigens beim Verknüpfen von zwei Tabellen in einer 1:n-Beziehung an Access, bei dem beim Aktivieren der referentiellen Integrität automatisch überprüft wird, ob alle n-Elemente auf der 1-Seite vorkommen. So einen Haken oder eine Meldung hat er vermisst.
Die Antwort ist einfach: du darfst „FALSCH“ nicht in Anführungszeichen setzen – es handelt sich hier nicht um einen Text, sondern um einen (booleschen) Wert. Also:
Schöne Frage in der letzten Excelschulung. Ich habe eine Übung erstellt: Dutzende von Fehlern: Bezugsfehler, Formatierungsfehler, falsch Zeichen („x“ statt „*“; „;“ statt „:“, …) ausgeblendete Zeilen, weiße Schriftfarbe, …
Danach erstellen wir eine Pivottabelle. Eine Teilnehmerin fragt, wo Quellen von Rechenfehlern liegen können. Man sieht das Ergebnis einer Summe – aber stimmt es auch?
Ich überlege:
Der Bereich kann falsch gewählt sein
Wird mit Bereichen gearbeitet, kann sich die Pivottabelle beispielsweise auf einen Bereich auf einem falschen Tabellenblatt beziehen
Wird mit intelligenten Tabellen gearbeitet, kann eine falsche Tabelle verwendet worden sein.
Das kann man über Pivottable-Analyse / Datenquelle ändern herausfinden.
Die Pivottabelle wurde nicht aktualisiert.
Die Beschriftung wurde sinnentstellt geändert.
Habe ich etwas vergessen? Sicherlich … Ich fand die Frage sehr interessant …
Hinter den Zellen liegt eine bedingte Formatierung. Color und ColorIndex liefern die voreingestellten Zellfarben und nicht die durch die Datenüberprüfung angezeigten … Es ist übrigens recht mühsam, das Ergebnis der bedingten Formatierung zu ermitteln.
Unglaublich! Kaum macht man Excel zu und wieder auf, sieht es völlig anders aus. Ohne Vorwarnung! Padautz!
17:07 Uhr17:28 Uhr
Die meisten Dinge habe ich schnell wieder gefunden. Die meisten. Für den Rückgägigbefehl habe ich drei Mal hinschauen müssen. Suchspiel: wer findet ihn?
Excelschulung. Wir erstellen einen Kalender. Daran kann man einige Funktionen üben: die Funktion WENN, Datumsfunktionen, Textfunktionen. Um Funktionen aus der Kategorie „Nachschlagen und verweisen“ zu zeigen, erstelle ich einen mehrsprachigen Kalender. Über eine Auswahlliste (Datenüberprüfung) wird die Sprache gewählt:
Mit VERGLEICH wird die Zeilennummer ermittelt; INDEX „holt“ den Ländercode aus einer kleinen Tabelle:
Eine Teilnehmerin sagt, dass es bei ihr nicht funktioniere. Ich schaue auf ihren Bildschirm. Richtig: Sie hat Excel 2013. Das erkenne ich sofort an den Großbuchstaben der Texte im Menüband. Bis Excel 2013 wurde nicht der ISO-Sprach- und Ländercode verwendet, sondern ein anderer. Zum Glück finde ich ihn auf der Festplatte in einem älteren Beispiel:
Excel-VBA-Schulung. Eine Teilnehmerin möchte ein kleines Programm mit mir geschrieben haben: Jede Woche erhält sie eine Liste und jede Woche muss sie in dieser Liste Berechnungen durchführen. Eine bestimmte ID (beispielsweise Idefix) wird gesucht, sämtliche Werte (hier drei) werden wie folgt berechnet:
Die Anzahl der Römer wird mit der Anzahl der Piraten multipliziert und die einzelnen Produkte summiert. Das Ergebnis wird durch die Summe der Römer dividiert. Aber nur dann, wenn keine Hinkelsteine vorhanden sind. Alles klar? – Klar!
Ich beginne Schritt für Schritt. Multipliziere und addiere – hierfür bietet sich doch SUMMENPRODUKT an, oder?. Also: los geht’s:
=SUMMENPRODUKT((A:A=G5)*(B:B)*(C:C))
Ich habe drei Mal überlegen müssen, woher die Fehlermeldung rührt. Die Antwort:
Klar: ich multipliziere jede Zelle jeder Spalte. Und das funktioniert bei der Überschrift (Text!) natürlich nicht!
Ich muss ändern. Entweder so:
=SUMMENPRODUKT((A:A=G5)*1;(B:B);(C:C))
Oder indem ich auf den Bereich ohne Überschrift verweise:
=SUMMENPRODUKT((A2:A40=G5)*(B2:B40)*(C2:C40))
Oder indem ich den Fehler mit WENNFEHLER abfange, oder oder oder.
Und DANN ist der Rest auch kein Problem – beispielsweise so:
Und diese Formel kann man mit dem Makrorekorder aufzeichnen und über alle Zellen „laufen lassen“. Das Ganze wird in der Datei Personal.xlsb gespeichert.
Im gleichen „Atemzug“ habe ich dann noch ein Problem mit [=ZELLE(„dateiname“)] gefunden. Das ist scheinbar nicht immer zwingend das aktuelle Workbook, welches dort angezeigt wird bzw. der Inhalt aktualisiert sich nicht automatisch. Wenn zwischenzeitlich eine andere Arbeitsmappe geöffnet war, steht noch deren Pfad im Feld….
Viele Grüße,
Jörn
Hallo Jörn,
ja – ich weiß –
ZELLE wird nicht aktualisiert – es gibt da so einige Funktionen in Excel,
beispielsweise JETZT(). Mit [F9] oder Formeln / Neu berechnen kann man die
Neuberechnung manuell erzwingen.
Kennt ihr das? Eigentlich sollte es nicht so sein. Aber einer der Kunden bestellt unbedingt darauf. Hat auch einen guten Grund dafür.
Der Kunde ist König!
Nun – gut – soll er seinen Willen haben!
In einem sehr umfangreichen Projekt, das mit VBA realisiert wurde, soll eine Auswahl über eine Auswahlliste getroffen werden. Aber eben ein Kunde möchte Freitext haben. Eigentlich widerspricht dies dem Workflow.
Also füge ich unter der Liste ein Textfeld (!) ein und formatiere es so, dass es aussieht als wäre es ein Bezeichnungsfeld. Man muss einige der Eigenschaften ändern:
Das verraten wir natürlich nur einem Kunden. Damit DER Freitext eingeben kann. Weil er es will. Weil er es braucht. Eben: weil der Kunde König ist:
PowerQuery-Schulung. Ein Teilnehmer sagt, dass er nicht den gesamte Ordnernamen sehen kann und deshalb nicht den richtigen Ordner deselektieren kann.
„Dann schieben Sie halt die Bildlaufleiste nach rechts“, meine ich. „Geht nicht!“
Was ist pasiert?
Wir üben in der PowerQuery-Schulung den Zugriff auf Ordner:
Der Teilnehmer hat die Dateien (auf OneDrive) in einem sehr, sehr langen Ordnernamen abgelegt. Und wirklich: es ist dann leider nicht mehr möglich, die Bildlaufleiste so zu verschieben, dass ich das rechte Ende des Ordners sehen kann:
Die Lösung: Da ich den Text kenne, der am Ende steht, kann ich den gewünschten Ordner auch über „endet nicht mit“ filtern. DAS klappt.
Gestern PowerQuery-Schulung. Wir üben und probieren den Zugriff: Excelmappen, Textdateien, XML, json, die SQL-Datenbank, Ordner, Web, … alles klappt.
SharePoint?
Der Teilnehmer kopiert seinen Sharepoint-Pfad in das Eingabefeld:
Und noch bevor ich sagen kann, dass er sich über das Microsoft-Konto – drei Zeilen darunter – anmelden muss, erhält er eine Fehlermeldung:
Zweiter Versuch: erneute Anmeldung. Das Resultat: sofortige Fehlermeldung ohne die Möglichkeit sich über das „Microsoft-Konto“ anzumelden. Wie gelangt man wieder dort hinein?
Es dauert eine Weile, bis wir es gefunden haben:
Man muss über die Datenquelleneinstellungen den Pfad löschen:
… dann wird man bei der nächsten Anmeldung wieder nach ALLEN Einstellungsoptionen gefragt.
Guten Abend René, ich grüße zur späten Abendstunde und erlaube mir um diese Uhrzeit noch eine Frage mitzusenden: Ich möchte lediglich in Erfahrung bringen, welche Möglichkeiten es (nicht) gibt, bzgl. des EXCEL-Solvers: (1) Funktionieren sollte … der Einsatz „Solver“ über Ribbon „Daten“/“Solver“, bei geschütztem Tabellenblatt, wenn zusätzlich VBA genutzt wird und beim Makrolauf das Passwort (Tabellenblatt) am Anfang aufgehoben und gegen Makroende wieder gesetzt wird das kann man dem www entnehmen (2) Nicht funktionstüchtig ist: Aktivierter Tabellenblattschutz, keine VBA-Nutzung. „Solver“-Einsatz über Ribbon „Daten“/“Solver“ meine heutige Erfahrung Mir geht es vor allem um Position (2). Die Argumentation beim Kunden: Da (2) nicht funktionstüchtig ist, muss ich (1) realisieren. Ein ok zu (1) und (2) wäre nett, sicherlich kennst du die Thematiken von deinen Kunden. Ein Dankeschön für dein ok & Gruß Jürgen
Moin, Jürgen,
was macht der Solver? Er liest Werte aus einem Tabellenblatt, rechnet und schreibt Werte zurück. Beziehungsweise schreibt Zwischenergebnisse zurück und prüft, ob sich die Ergebnisse dem gewünschten Ziel annähern.
Das kann auf einem geschützten Blatt nicht funktionieren.
Ich mache es (bei anderen) VBA-Programmen immer so, dass ich den Blattschutz aufheben, Werte eintrage und dann den Schutz wieder setze.
Übrigens: auch ohne zu schreiben – es gibt einige VBA-Befehle, die man auf einem geschützten Blatt nicht ausführen kann, beispielsweise CurrentRegion!?!
Liebe Grüße
Rene
Und ich sage es noch deutlich. Aber er hört nicht.
Listen in Excel sollten eine Überschrift besitzen, wenn man die Listen sortiert und filtert; sie müssen eine Überschrift besitzen, wenn man mit einer Pivottabelle arbeitet.
Der Teilnehmer der Excelschulung hört nicht; erstellt einer Liste, bei der eine Spalte keine Überschrift besitzt:
Das Ergebnis: Excel geht davon aus, dass die Liste keine Überschrift hat und sortiert die erste Zeile ein:
Der Teilnehmer wundert sich.
Man kann es deutlich zeigen, wie Excel diese Liste interpretiert. Die benutzerdefinierte Sortierung zeigt auf, dass keine Überschrift identifiziert wurde:
Nachtrag: bei einer intelligenten Tabelle wäre das nicht passiert. Aber die lernen wir erst später …
Auch wenn die bedingte Formatierung in Excel bei Datenbalken, Farbskalen und Symbolsätzen behauptet, sie könne „Formeln“, unterstützt sie jedoch nur absolute Bezüge. Schade!
Ein Teilnehmer der letzten Excelschulung hatte folgendes Problem. Eine Liste von Mitarbeitern und Mitarbeiterinnen nehmen an Fortbildungen teil. In einer Spalte werden die Summen der Stunden, an sie an Fortbildungen teilgenommen haben, aufgelistet. Eigentlich sollten sie bis zum ersten Quartal 15 Stunden absolviert haben, bis Ende des zweiten Quartals 22,5 Stunden, 30 bis Ausgang des dritten Quartals und 45,5 bis Ende des Jahres. Die Hälfte wäre noch okay – wünschenswert ist die volle Stundenzahl. Nun möchte der Controller durch lustige Fähnchen (grün, gelb und rot) den Status visualisieren. Ein gemischter Bezug der Form =C$2 wäre gut. Allerdings weigert sich die bedingte Formatierung:
Absolute Bezüge werden unterstützt, relative und gemischte leider nicht. Also muss man für die vier Quartale vier Bezüge erstellen. Zum Glück sind es nur vier!
Excelschulung. Eine Teilnehmerin möchte eine Dropdownliste durch eine Datenüberprüfung haben, in der Smileys angezeigt werden. Ich überlege: in der Schriftart Wingdings gibt es drei Smileys. Man kann sie über Einfügen / Symbol einfügen, oder indem man die Buchstaben J, K und L mit der Schriftart Wingdings formatiert.
Fügt man eine Datenüberprüfung ein, werden jedoch nur die drei Buchstaben dargestellt – auch das Formatieren der Zelle nutzt nichts:
Ich überlege: vielleicht werde ich in den nicht druckbaren Zeichen fündig, die man mit [ALT] + [1], [ALT] + [2], … erzeugen kann. Jedoch finden sich nur zwei Smileys hinter den Nummern 1 und 2:
Mourad hat eine Idee und hilft mir. Im Unicode-Zeichensatz (beispielsweise der Schriftart Calibri) finden sich Smileys:
Wenn ich Word die Unicode-Zahl eintrage (beispielsweise U+1F600) und anschließend [ALT] + [C] erhalte ich das dahinter liegende Symbol:
In Excel funktioniert das leider nicht. Muss ich die Zeichen von Word nach Excel kopieren? Quatsch, meint Mourad – du kannst sie doch direkt von der Internetseite nach Excel kopieren:
Oder mit der Funktion UNIZEICHEN umwandeln, also beispielsweise:
=UNIZEICHEN(128512)
Klappt! Und so können wir eine lustige Auswahlliste erstellen:
Auch mein Add-In [Strg] + [Q] funktioniert:
Eine großes Dankeschön an Mourad Louha für die Hilfe.
wenn in A und B Name und Vorname stehen. Ich fange immer in einer Zelle an; trage dort die Bed. Formatierung ein und erweitere ANSCHLIESSEND den Bereich.
cool. Die Funktion xVergleich kenne
ich gar nicht. Vielen Dank für den Tipp
XVERWEIS und XVERGLEICH
erweitern SVERWEIS und VERGLEICH. Umgekehrt: ich habe es zuerst mit
=Zählenwenn(A:A&B:B;“Rene
Martin“)
versucht – geht aber
leider nicht … ZÄHLENWENN versagt hier … Schade!
In der Systemsteuerung von Windows findet sich bei den Eigenschaften der Maus in der Registerkarte „Zeigeroptionen“ die Einstellung, dass die Zeigerposition durch Drücken der [Strg]-Taste besser in den Fokus gerückt wird.
Das verwende ich in der letzten Excelschulung, während ich Diagramme erkläre. Allerdings: ständig öffnet sich das Dialogfeld „Diagrammelemente“. Das nervt!
Excelschulung. Wir üben die WENN-Funktion. Ich erkläre, dass man Text in Excel in Anführungszeichen setzen muss – manche Assistenten machen dies automatisch; andere nicht.
Meine Empfehlung: Immer per Hand die Anführungszeichen setzen:
Danach üben wir die bedingte Formatierung – auch eine Art „WENN“. Da die Teilnehmerinnen und Teilnehmer meinen Rat befolgen, tragen Sie den Text in Anführungszeichen ein:
Was passiert? – Nichts! Der Grund:
Excel wandelt den Text „Pandora Papers“ in „““Pandora Papers“““ um – Excel geht davon aus, dass die Anführungszeichen Teil des Suchtextes sind. Also raus damit!
Excelschulung. Wir üben Diagramme. Ein Teilnehmer fragt, ob man denn die Beschriftung von sehr vielen Zahlen im Diagramm automatisch besser darstellen lassen kann, beispielsweise „alternierend oben und unten“ oder: „nur jeden zweiten anzeigen“ oder: „so positionieren, dass sie sich nicht überlassen“.
Meine Antwort: leider nein! So etwas habe ich mir auch schon oft gewünscht. Manchmal muss man jede Zahl einzeln per Maus positionieren.
Hallo in die Runde, Dies ist mein erster Post. Normalerweise, wenn ich einen Bereich als Tabelle formatiert habe, wurde eine die Formel mit dem Drücken der Enter-Taste automatisch bis zum Ende der Tabelle ergänzt. Seit kurzem funktioniert das nicht mehr. Ich muss wohl irgendwas verstellt haben. Könnt ihr mir bitte sagen, wie ich das zurück stelle? Unter einstellungen ist formel erweitern auf automatisch. Aber das löst das Problem nicht. Ich nutze Excel für Mac. Vielen dank für Eure Hilfe Gruß Stephen
Für eine Firma erstelle eine Feiertagstabelle. Der Lieferant verlangt einen Zuschlag, wenn in der Woche (Mo – Fr) ein Feiertag liegt. Die Basis sind die Feiertage von NRW.
Diese Liste wird auf einem anderen Tabellenblatt verwendet:
Ich klicke auf das Symbol f(x), um die Formel im Funktionsassistenten zu bearbeiten:
Das Ergebnis: „Formelergebnis = Veränderlich“ ?!? Ein Klick auf [OK] und das Meldungsfenster wird geschlossen. Aha!
ich hoffe es geht Dir gut! Ich betreue gerade ein ziemlich spannendes Projekt für ein Unternehmen in der Schweiz, dass mich in meinen VBA-Kenntnissen bisher schon ziemlich gefordert aber auch gefördert hat. Jetzt bin ich allerdings an einem Punkt wo ich mit Google und alleinigem überlegen nicht mehr weiterkomme und habe die Hoffnung, dass Du einen Tipp für mich hast.
[…]
Ich habe quasi jeweils eine Liste mit den nach Wunsch aufbereiteten Rohdaten. In dieser soll jetzt an Hand von Daten aus einem Konfigfile (wird wie die Rohdaten über PowerQuery vom Server eingelesen) der entsprechende Filter auf den Verkäufer gesetzt werden und die daraus resultierenden Tabellen in ein neues Dokument exportiert werden. Ist der Vorgang abgeschlossen, kommt der nächste Filter etc. etc. Ich brauche also meines Erachtens einen iterativen Filter der auf Grund eines Kriteriums aus dem Konfigfile erstellt wird.
Leider bekomme ich diesen Part nicht wirklich hin.
Ich hoffe dass meine Mail soweit erstmal nachvollziehbar ist
und dass Du vielleicht eine Idee hast, was ich hier noch machen oder an wen ich
mich noch wenden kann. Ich weiß leider nicht mehr weiter und der Kunde wartet
auf sein Reporttool.
Über eine Rückmeldung von Dir würde ich mich sehr freuen.
Danke Dir und liebe Grüße
Paul
Hallo Paul,
kennst du den
Spezialfilter? Hast du schon einmal den AdvancedFilter in VBA benutzt? Ist
nicht sehr schnell, aber nur eine Zeile Code um eine Liste durch eine
Filterkriteriumsliste zu ziehen.
Zuerst hört sich die Frage ganz einfach an, aber dann kam ich ins Schleudern:
„Hallo Rene
zum Thema Datumsberechnungen hätte ich gleich eine Frage:
kann Excel auch Zeiträume erkennen, die sich überschneiden, aber
unterschiedliche Anfangs- und Endzeiten haben?
Also zum Beispiel:
Mitarbeiter A arbeitet vom
01.05.2021 bis 31.08.2021
Mitarbeiter B arbeitet vom
01.06.2021 bis 15.09.2021
In welchem Zeitraum haben
beide gearbeitet
Oder
Von
Bis
Thema
8:00
12:00
Nachdenken
11:30
12:30
Pause
12:30
15:00
Nix tun
Ich überlege. Und erweitere das Beispiel. Das Oktoberfest hätte in diesem Jahr vom 18. September bis 03. Oktober stattgefunden. Adelheid ist von 01.09. bis 30.09 in München; Basti vom 25.09. bis 25.10.; Christoph vom 27.09. bis 30.09.; Doris vom 10.09. bis 10.10.; Erich vom 01.09. bis 10.09. und Franziska vom 10.10. bis 20.10. Wie viele Tage hätten sie aufs Oktoberfest gehen können?
Im ersten Schritt habe ich das visualisiert:
Ich beginne mit Erich und Franziska: Wenn Ende < Beginn Oktoberfest, dann 0. Wenn Anfang > Ende Oktoberfest, dann 0:
=WENN(B33<$B$21;0)
Dann die umschließenden Bereiche:
=WENN(UND(B28>=$B$21;B29<=B22);B29-B28+1;0)
Und schließlich die überschneidenden Bereiche:
Wenn Anfang <= Anfang Oktoberfest und Ende <= Ende Oktoberfest, dann Ende – Anfang Oktoberfest:
=WENN(UND(B24<=$B$21;B25<=$B$22);B25-$B$21+1;0)
Analog die zweite Überschneidung:
Wenn Ende >= Ende Oktoberfest und Anfang >= Anfang Oktoberfest, dann Ende Oktoberfest – Anfang:
=WENN(UND(B27>=$B$22;B26>=$B$21);$B$22-B26+1;0)
Dabei fällt auf, dass die ersten beiden Fälle mit einbezogen werden – man muss sie explizit ausschließen, beispielsweise mit einem dritten Fall:
und Ende >= Anfang Oktoberfest, beziehungsweise: Anfang <= Ende Oktoberfest:
Ein Summieren der Varianten verbietet sich, da stets mit >= und <= gearbeitet wurde. So könnte ein Datumsbereich vom 18.09. bis 20.09. in zwei der sechs Kategorien fallen. Entweder man ändert einige der <=in < oder man baue den Baum auf:
Fall: leere Menge
Fall: ganzer Bereich
Fall: Überschneidung
Fall:
=WENN(ODER(B33<$B$21;B32>$B$22);0)
2. Fall a: Datumsbereich liegt im Oktoberfestzeitraum:
Natürlich hätte man die Bedingungen auch umdrehen können.
Natürlich hätte man den Bedingungsbaum auch anders aufbauen können:
Fall: Anfang <= Oktoberfest Anfang? Ja: Fall a) Ende vor Oktoberfest Anfang? Fall b) Ende nach Oktoberfest Ende? Fall c) Ende zwischen Oktoberfest Anfang und Ende? […]
Die Teilnehmerinnen waren begeistert und erschöpft. Leider haben sie nur Excel 2016. Ich überlegte mir, ob man das Problem nicht mit den Matrixfunktionen SEQUENZ & co lösen kann. Die Antwort: Man kann:
Wir beginnen mit Adelheid. Im ersten Schritt werden alle Adelheid-Tage und Oktoberfesttage aufgelistet. Die Funktion SEQUENZ hilft hierbei:
=SEQUENZ(A6-A5+1;1;A5)
Nun kann man zählen, wie oft jeder Adelheid-Tag in der Oktoberfest-Tagesliste vorkommt – einmal oder kein Mal:
Für die anderen Personen muss diese Formel nur entsprechend angepasst werden. Gibt es keine Überschneidung wie bei Erich und Franziska gibt die Formel den Wert #NULL! aus. Wichtig ist das Leerzeichen in der Mitte der Formel. Dadurch wird die Schnittmenge ermittelt.
Salü
Ernst
PS: Nachtrag:
Allerdings
kann man durch eine kleine Änderung der Formel meines Lösungsvorschlages (aus
Zeilen() mache Zeile() und gebe die Formel als Matrixformel ein) eine
einspaltige Matrix erzeugen, in der die Datumswerte der Schnittmenge
eingetragen sind.
Und schließlich kann man das Problem auch mit VBA lösen. Man muss zwei Bereiche (Range) definieren und die Schnittmenge (Application.Intersect) bestimmen:
Dim xlBereich1 As Range
Dim xlBereich2 As Range
Dim xlSchnittmenge As Range
With ThisWorkbook.Worksheets("Helmut")
Set xlBereich1 = .Range(.Cells(Range("B2").Value2, 1),
.Cells(.Range("B3").Value2, 1))
Set xlBereich2 = .Range(.Cells(Range("B5").Value2, 1),
.Cells(.Range("B6").Value2, 1))
End With
Set xlSchnittmenge = Application.Intersect(xlBereich1, xlBereich2)
MsgBox xlSchnittmenge.Cells.Count
Ich habe für meine Kollegen zur Budgetplanung 2022 je
Abteilung ein Excel auf Teams eingestellt.
Das Excel enthält eine Power Query Abfrage auf alle
Abteilungs-„Auftragsbücher“, und in PowerPivot ein Datenmodell für die
Beziehungen zwischen den Tabellen.
MAC Benutzer scheinen aber Probleme mit der Datei zu haben (s. Screenshots unten)
Wie kann man die volle Funktionalität der Datei auch für MAC
Benutzer herstellen?
Es wäre super, wenn Du hier einen Rat hast
Vielen Dank und beste Grüße Katrin
Hi Katrin,
1. Antwort: Mac ist nicht meine Welt – ich habe keinen.
2. Antwort: ich weiß, dass der mac lange Zeit nicht
PowerQuery unterstützt hat; soweit ich weiß, kann er das inzwischen.
3. Antwort: der Mac unterstützt (noch) nicht das Datenmodell von Excel.
Sehr geehrter Dr. Martin, Wir haben in unserem Unternehmen Probleme bei der Formatierung unserer Statistikauswertung. Wir formatieren eine Spalte farblich größer als /kleiner als/ zwischen, Jedoch ist es nicht möglich dieses Vorgehen auf die anderen spalten zu übertragen und ich or müssen somit jede Spalte seperat formatieren. Ist es möglich das ganze auf alle spalten zu übernehmen?
Hallo Herr R.,
und so funktioniert es. Beginnen Sie bei einer Zelle, beispielsweise links oben. Liegt der Wert dieser Zelle zwischen der Unter- und Obergrenze, soll er grün werden. Die Formel lautet:
=UND(B6>=B$5;B6<=B$3)
UND, weil beide Bedingungen erfüllt sein müssen.
B6 darf kein $-Zeichen haben – diese Zelle ist variabel, soll in der Position geändert werden.
B$5 und B$3 haben ein Dollarzeichen vor der Zeilennummer 3 und 5. Das bedeutet: beim Herunterziehen verändert sich die Zeile nicht – sie bleibt fix. Die Spalte B hat kein $-Zeichen – sie ist wieder variabel oder veränderlich.
Im nächsten Schritt wird der Bereich im Assistenten „Regeln verwalten“ auf den gesamten Bereich ausgedehnt:
Analog beim zweiten Schritt. Man könnte die Werte, die außerhalb liegen, mit zwei bedingten Formatierungen abarbeiten, oder mit einer. Ich entscheide mich für eine Bedingung. Die Formel lautet:
=ODER(B6<B$5;B6>B$3)
Auch hier gibt: B6 ist relativ, in B$3 und B$5 sind die Zeilen 3 und 5 fixiert; jedoch nicht die Spalte B. Und auch hier kann man im zweiten Schritt den Bereich erweitern. Das fertige Ergebnis:
Sie erstellt in Excel ein Diagramm, in dem zwei Datenreihen verwendet werden: die Differenz zum Vorjahr und die Absolutwerte. Das Ergebnis der Berechnung wird in einem Säulendiagramm mit zwei dargestellt. Eine Datenreihe wird ausgeblendet – die Beschriftung am oberen Rand dargestellt.
Warum nur, fragt Carmen, warm kann ich die Beschriftungselemente nicht ausrichten?
Ich möchte gerne ein Symbol in die Gruppe „Zahl“ einfügen, mit dessen Hilfe ich eine Zahl als Datum formatieren kann:
Meine Gegenfrage, ob nicht die Tastenkombination [Strg] + [#] gute Dienste tut, wurde verneint. „Ich hätte gerne zwei Symbole – eines für „kurzes Datum“, eines für „langes Datum“ war der Wunsch.“
Makros werden aber bei uns nicht unterstützt …
Schade – ich weiß keinen Ausweg! Keine Lösung für dieses Problem OHNE Makros.
ich lese regelmäßig deinen Blog „Excel nervt“ und hatte dir
vor einiger Zeit auch schon einmal eine Mail zu deinem Buch „Excel: Zahlen. Rechnen.
Formeln“ geschickt, die du mir sehr freundlich und ausführlich beantwortet
hattest.
Heute bräuchte ich mal deine Hilfe. Ich sitze hier vor einem Problem, bei dem ich alleine nicht weiterkomme. Es geht um einen Milchviehbetrieb, der seine Jungtiere von einem Aufzuchtsbetrieb großziehen lässt. Ich soll nun rückwirkend die Aufzuchtskosten pro Monat überprüfen. Dazu steht mir eine Tabelle zur Verfügung mit dem Abgangsdatum der Tiere vom Milchviehbetrieb (= Zugangsdatum Aufzuchtsbetrieb) und dem Zugangsdatum der Tiere beim Milchviehbetrieb. Ist das Tier aktuell noch beim Aufzuchtsbetrieb, ist jeweilige Feld für das Zugangsdatum leer. Pro Tag, den eine Kuh beim Aufzuchtsbetrieb ist, erhält der Aufzuchtsbetrieb eine Pauschale (z.B. 1€ pro Kuh und Tag). Ich hatte irgendwie gedacht, dass man das relativ leicht über eine Formel ermitteln könnte. Problematisch ist vor allem ein angebrochener Monat, wenn beispielsweise eine Kuh am 5. März 2021 an den Aufzuchtsbetrieb geliefert wird, erhält der Aufzuchtsbetrieb für diese Kuh ja theoretisch 27 €.
Anbei habe ich eine Beispieltabelle hinzugefügt. In den Spalten E bis R möchte für den jeweiligen Monat und für jede Kuh die Tage ermitteln, die diese beim Aufzuchtsbetrieb war.
Kannst du mir da weiterhelfen? Irgendwie stehe ich gerade
auf dem Schlauch.
Vielen Dank im Voraus.
Hallo,
hübsche Fingerübung.
Ich würde in die erste Zeile jeweils den 01. April 2020, 01. Mai 2020, … und als April 2020, Mai 2020, … formatieren. Also mit MMM JJJJ
Du musst einen „Baum abarbeiten“:
1. Fall: sind Jahr und Monat identisch -> dann rechne die Anzahl Tage bis Ende des Monats.
Die Erklärung: Ich brauche in diesem Fall die Anzahl der Tage bis zum Ende des Monats. Die Funktion
MONATSENDE($C2;0)
berechnet den letzten Tag des Monats (hier: 30.04.2020). Und davon wird das Datum abgezogen (hier: 11.04.2020). Das Ergebnis lautet 19; plus 1 = 20 = die Anzahl der Tage vom 11. bis zum 30. (beide einschließlich)
2. Fall: liegt Abgang vor dem Datum (bspw. 01. April) und Zugang nach dem nächsten Monat -> dann voller Monat (die zweite WENN-Funktion wird die Stelle der 0 gesetzt:)
Für diese Kühe hatte ich die Auswertung händisch vorgenommen und komme auf das gleiche Ergebnis wie mit deiner Formel. Scheint also zu passen.
Einen „Baum“ hatte ich mir auch
schon überlegt, allerdings hatte ich Schwierigkeiten bei dem Umgang mit den
„angebrochenen“ Monaten und den Kühen ohne Zugangsdatum.
In der ersten Zeile hatte ich
sogar schon jeweils den 1. des Monats als Datum eingetragen, weil ich damit
rechnen wollte. Ich hatte es dann über „Zellen formatieren…“ – Kategorie: Datum
– Typ: Mrz. 12 umgewandelt. Blöd, dass Excel dann beim Mai auch einen Punkt
macht, wie mir gerade auffällt.
Vielen, vielen Dank für deine
Hilfe und mach weiter so. Ich werde deinen Blog auf jeden Fall weiter
verfolgen.
Amüsiert. Ich erstelle in Excel mit VBA eine Eingabemaske, in der verschiedene Begriffe stehen:
Wenn aus allen drei Listenfeldern etwas ausgewählt wird, werden die drei Begriffe in die entsprechenden Spalten eingetragen.
Klappt.
Danach wird die Auswahl entfernt
ListIndex = -1
Klappt nicht. Der Grund: das Ereignis Click deselektiert die drei Listen und DANN wird der Klick durchgeführt; das heißt: NUN ist ein Eintrag markiert.
Doof!
Ich mache mich auf die Suche, ob eines der Ereignisse ein Parameter Cancel besitzt, mit dessen Hilfe man ihn abbrechen könnte.
Fehlanzeige.
Also noch einmal schauen und probieren. Dann finde ich die Lösung: ich muss Click durch MouseUp ersetzen – DANN funktiert es: zuerst wird der Mausklick durchgeführt und DANACH der Code abgearbeitet (nicht umgekehrt wie beim Ereignis Click:
Private Sub lstRechts_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Ich öffne mein Excel, das auf dem Desktop installiert und darin eine Datei. Ich kopiere einen Teil einer Tabelle nach Excel online, das auf dem SharePoint gespeichert ist.
Allerdings: ich darf nicht über das Kontextmenü einfügen, sondern muss die Tastenkombination(en) verwenden.
Ich schätze es ausserordentlich, dass Du mich unterstützt und fühle mich geschmeichelt.
Du hast natürlich vollkommen Recht mit dem Hinweis, dass in einer Spalte
keine Zahlen und Texte stehen sollten.
Dieser Umstand ist dadurch entstanden, weil die Tabelle zusätzlich für
einen anderen Zweck benutzt wurde. Hierbei wurden die Zeilen insofern
erweitert, indem für jeden Kunden eine zusätzliche Zeile eingetragen wurde.
Hatte ein Kunde einen Service, wurde in der entsprechenden Spalte eine 1
reingeschrieben und im Anschluss mit einem Flow in einen ScharePoint Liste
übertragen. Anders hätte ich ja die Kunden Records nicht handeln können.
Nun habe ich den Servicekatalog und die Zuweisung der Kunden zum jeweiligen Service getrennt. Somit konnte ich die Tabelle vom Servicekatalog wieder «drehen», womit die Services in den Zeilen stehen und die Spaltenwerte zu den jeweiligen Daten passen.
Eine andere Lösung gibt es nicht, wenn ich Deine Erklärungen richtig
verstanden habe und eigentlich scheint es mir auch logisch.
Nun muss ich einige weitere Anpassungen an dem ganzen Konstrukt
vornehmen und einen neuen Flow erstellen. Dabei hoffe ich natürlich, dass ich
mir keine neue Baustelle geschaffen habe.
Nochmals herzlichen Dank für Deine wertvolle Hilfe und die guten Tipps
Herby
#####
Hallo Herby,
in meinen vielen
Jahren Exceltraining und meinen vielen Artikeln auf excel-nervt habe ich
gelernt, dass
* Excel ein sehr
gutes Programm ist
* Excel manchmal
etwas eigenwillig ist
* Anwender und
Anwenderinnen oft Wünsche haben, die sich SO nicht direkt umsetzen lassen (ich
verstehe oft die Hintergründe)
* dass man sich auf das Denken von Excel einlassen muss (ist halt ein Mann*) und man manchmal seine Daten etwas anders organisieren muss, damit man zum Ziel kommt
Liebe Grüße
Rene
*) Die Frage, ob Excel männlich oder weiblich ist, stelle ich häufig in Schulungen. Und amüsiere mich dann über die Antworten à la: „Excel ist männlich, weil …“ oder „Excel muss eine Frau sein, der nur so kann man sich erklären …“
In einem Ordner befinden sich mehrere Hundert Mails. Eine davon ist ungelesen. Ich finde sie nicht. Na – kein Problem, denke ich und füge das Feld „Gelesen“ aus der Feldliste hinzu. Leider kann man DARÜBER nicht sortieren:
Okay – noch ein Versuch: Filtern.
Erstaunlicherweise kann ich nach gelesen/ungelesen filtern:
Mit VBA wird eine Userform (eine Maske) erstellt zur bequemen Dateneingabe. Der Wert eines Textfeldes wird als String interpretiert und als solcher bei Dezimalzahlen in eine Excelliste eingetragen. Man erkennt es, weil die Zahlen linksbündig in der Zelle stehen:
Dummerweise wird ein Text immer größer als eine Zahl definiert, so dass eine Formel
Es ist unglaublich, aber ich habe wirklich das Gefühl, dass ich in jeden «Sche…sstopf» falle, welchen Microsoft zu bieten hat.
Seit 2 Tagen kämpfe ich mit dem Problem, dass in einer table in jeder Zelle scheinbar versteckte Tabs vorhanden sind. Dies hat natürlich die traurige Konsequenz, dass damit s- oder wverweise auf diese table kläglich scheitern und zu #NV Fehlern führen.
Zum Problem mit Tabs hast Du ja den Artikel tabulatoren | Excel nervt … (excel-nervt.de) geschrieben, doch in meinem Fall hilft mir dieser (wenigstens im Moment) nicht wirklich weiter.
Ich muss dazu vielleicht etwas ausholen und den Vorgang beschreiben, welcher mich zum Problem geführt hat. Am Anfang steht Excel File mit einer table. Diese table wird mittels Power Automate in eine SharePoint Online Liste geschrieben.
In einem anderen Excel File werden die Daten der SharePoint Liste wieder mit einer PowerQuery Abfrage eingelesen und stehen somit wieder in einer table, auf welche ich eben mit dem erwähnten wverweis zugreifen möchte. Der Befehl führt eben zu dem #NV und nach langem Suchen, habe ich letztendlich herausgefunden, dass in der abgefragten table in allen Zellen ein tab steht. Interessanterweise ist es aber so, dass in der table sämtliche Zellwerte linksbündig angezeigt werden. Klicke ich dann bei denjenigen Zellen welche eine Zahl enthalten nicht auf sondern in die Zelle, dann springen die Zahlen nach rechts (ohne dass ich ausser dem Klick in die Zelle etwas anderes mache) Noch verwirrender (wenigstens für mich) ist die Tatsache, dass die Zellformatierungen danach erhalten bleiben. Ich meine damit, dass diejenigen Zellen in welche ich wie beschrieben einmal reingeklickt habe, auch nach einem reload der Power Query Abfrage erhalten bleiben.
Hast Du vielleicht eine Erklärung für dieses Verhalten? Wieso und wann wurden die Tabs in die Zellen geschrieben und gibt keine Möglichkeit dies zu beeinflussen?
Bezugnehmend auf Deine vorherige Antwort ist es aber sicherlich schon so, dass man solche Phänomene auch mit der besten Schulung nicht abwenden kann ☹
Würde mich auf jeden Fall darüber freuen, wenn Du eine Idee zu meinem neuen Problem hättest
Lieber Gruss
Hallo Herby,
das Problem ist mir und vielen anderen bekannt – ich würde
es nicht als Anomalie, sondern als Bug von Excel bezeichnen.
– dort beschreibe ich mehrere Lösungen (mein Liebling ist Daten / Text in Spalten) und auch, wie dieses Phänomen zustande kommt.
Liebe Grüße
Rene
Hallo Rene
Danke für die abermals hilfreiche Unterstützung
Mein Problem schein aber irgendwie anders gelagert zu sein und entgegen meiner
vorherigen Problemschilderung ist es leider nicht so, dass der Fehler mit einem
Klick in eine der betroffenen Zellen «nachaltig» gelöst wird.
Zur besseren Veranschaulichung habe ich eine Kopie der Tabelle erstell, welche
auf der PQ Abfrage beruht. Am Bsp der Zelle B2 kannst Du sehen, dass der
Zellwert nach einem Klick in die Zelle, nach rechts gesprungen ist.
Sobald ich das bei irgend einer benötigten Zelle mache, welche einen Zahlenwert
enthält, springen die Werte nach rechts und die Formeln mit den darauf
referenzierenden Zellen, funktionieren.
Wenn ich hingegen die PQ Abfrage aktualisiere, springen die Zahlen wieder nach
links und die Formeln bringen den #NV
D.h die PQ Abfrage erzeugt die falschen Daten und dabei spielt es
überhaupt keine Rolle, wie die Zellen formatiert sind.
Die Spalten der Daten Quelle (ShareListe) sind ausnahmslos als standard
formatiert und dies lässt sich auch nicht ändern, da innerhalb einer Spalte
unterschiedliche Daten vorhanden sind.
Wie bei Excel gibt es beim PQ unter Transformieren/Bereinigen die Trim
Funktion, mit welcher eigentlich ein tab aus einer Zelle entfernt werden
sollte.
Aber bis dato ist mir dies damit nicht gelungen
Das Problem muss beim erzeugen der Tabelle gelöst werden, da die Daten
dynamisch sind und laufend aktualisiert werden. Oder anders ausgedrückt, eine
neue Abfrage würde die vormals vorgenommenen Korrekturen mir den Daten
überschreiben.
Das File Servicekatalog Quelldaten dient als Datenquelle, das heisst wenn sich
irgendwelche Daten vom Servicekatalog geändert haben, werden diese dort
eingepflegt. Eine Flow schreibt die Daten in die SharePoint Liste, welche dann
wie PQ Abfrage von überall in eine Servicekatalog.xlsx gelesen werden können.
Die Quelldatei hat das Problem auf jeden Fall nicht, d.h entweder auf dem
SharePoint oder bei anschliessenden PQ Abfrage wird ein problematischer tab
angehängt ☹
Vielleicht mache ich einen Denkfehler und/oder Du hast eine Idee, was ich
ändern muss
Lieber Gruss
Hallo Herby,
Das Problem ist Folgendes:
In einer Spalten stehen Zahlen und Texte.
Wird diese Liste nach PowerQuery „gezogen“ und dort der Typ nicht explizit angepasst, so bleiben die Zahlen Zahlen (rechtsbündig) und die Texte Texte.
Verwendet man in PowerQuery jedoch den Datentyp „Text“, dann „schiebt“ Excel unter diese Zahlen ein Textformat (das so nicht sichtbar ist).
Da die Zelle als Standard (oder Zahl) formatiert ist, verschwindet das Textformat beim Editieren (Doppelklick) der Zelle. Andererseits: Nach Aktualisierung von PowerQuery haben wir die gleiche Situation wie am Anfang.
Gegenfrage: Warum MÜSSEN in einer Spalte Zahlen und Texte
stehen? Das widerspricht einem Datenbankdenken.
Und: wenn schon Zahlen – dann sollten sie auch Texte bleiben
– als Informationen und nicht zum Rechnen verwendet werden.
Wie kann ich es erreichen, dass eine Datenreihe im 15-Minuten-Takt fortgesetzt wird? Beispielsweise für einen Stundenplan. Ich schaue nach:
Erstaunlicherweise lässt der Assistent „Datenreihe“, den man in der Gruppe „Bearbeiten“ in der Registerkarte „Start“ findet, keine Uhrzeiten zu …
Natürlich könnte man es mit einer Formel erreichen:
=A2+15/24/60
Da in der Schulung Anfängerinnen waren, die bislang noch wenig Erfahrung mit Formeln hatten, schlage ich die naheliegende Lösung vor: zwei Startzeiten eintragen, markieren und runterziehen:
Kennen Sie das? In einer Excelliste wurde ein Autofilter aktiviert. Ein Kriterium wird gefiltert. Nun wird auf die gefilterte Liste ein Rechteck gelegt, beispielsweise zur Kommentierung:
Wird nun der Filter entfernt wird die Ferm seeeeehhhhhhhr, seeeeehhhhhhhr lang:
Wir haben einen Ordner. Nennen wir ihn „Bilanz“. In diesem Ordner liegen zwei Dateien: August.xlsx und September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
Beide Dateien werden geschlossen, der Ordner wird umbenannt, beispiesweise in „Bilanz2021“. Das Öffnen und Aktualisieren der Datei funktioniert problemlos.
Wird haben einen Ordner. Nennen wir ihn „Bilanz“. Darin befinden zwei weitere Ordner: „August“ und „September“. Im Verzeichnis „August“ befindet sich eine Datei August.xlsx, im September-Verzeichnis eine Datei mit Namen September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
Beide Dateien werden geschlossen, der Ordner „August“ wird umbenannt, beispiesweise in „August2021“. Das Öffnen und Aktualisieren der Datei funktioniert JETZt nicht mehr:
Manchmal amüsieren und erstaunen mich Fragen in Excelschulungen. Beispielsweise folgende:
In einer Liste stehen Email-Adressen. Um herauszufinden, ob einige der Adressen doppelt vorkommen, wird eine Pivottabelle aufgesetzt, die Adressen werden gruppiert und gezählt:
Die Anzahlspalte wird absteigend sortiert.
Eine Teilnehmerin fragt, warum ein Doppelklick auf einen Eintrag (eine Mailadresse) die Möglichkeit bietet, weitere Details einzublenden, während ein Doppelklick auf die Anzahl diese aggregierte Zahl zu „entfalten“, also alle Datensätze anzuzeigen, die sich dahinter verbergen.
Clevere Frage. Und: ich weiß keine Antwort. Ich kann nur vermuten, warum Microsoft das SO eingerichtet hat.
Excelschulung. Wir erstellen ein Eingabeformular. In einer Zelle steht eine Prozentzahl – sie wird auf Eingabewerte zwischen 0% und 10% festgelegt – also in der Datenüberprüfung werden die Grenzwerte 0 und 0,1 eingetragen:
Ein Teilnehmer probiert aus und trägt die Zahl 25 ein. Das Prozentzeichen bleibt stehen:
Er erhält – wie erwartet – einen Fehlerwert. Die Zahl MIT Prozenzwert werden markiert:
Der Teilnehmer versucht es ein zweites Mal – diesmal trägt er 7,5 ein:
Da er das Prozentzeichen zuvor markiert hatte, wurde es gelöscht – 7,5 ist nun 7,5 und nicht 7,5%. Also erfolgt wieder eine Fehlermeldung!
Hum. Das heißt: man muss schon ganz genau hinschauen, was man einträgt und welche Mechanismen Excel verwendet …
Eine schöne Frage in der letzten PowerBI-Schulung:
Wie viele Funktionen kann man in DAX ineinander verschachteln. „Genug“ lautete meine Antwort. „Sehr viele“, um etwas präziser zu sein. Ich habe gesucht und nicht gefunden. Erstaunlich. Also habe ich ausprobiert. Aber 100 Ebenen habe ich aufgehört:
anbei die beiden Files (Visio und Excel Tabelle)
Bei der Tabelle handelt es sich um eine Copy aus einem sehr umfangreichen Excel Workbook, welches unter anderem eben die Tabelle produziert, welche die enthaltenen Services (Visio Shapes) steuert. Falls ein Kunden einen Service in einer Ausprägung bekommt, soll das entsprechend Shape auf dem Visio erscheinen.
Ein komischen Phänomen oder Verhalten ist mir bei der Fehlersuche aufgefallen. Wenn ich im Excel File in der Spalte Layer die Formel mit fixen Werten ersetze, dann läuft es irgendwie deutlich besser. Kann es sein, dass Visio im trotz dem vorher benötigten Datenabgleich im Hintergrund noch irgendetwas mit den Daten macht?
Ich habe keine andere Erklärung, wieso es ohne Formeln im Excel in Visio besser funktionieren sollte ???
Wenn ich Deine Bücher zu VBA und Visio Programmierung durchgearbeitet habe, bin ich sicher in der Lage den Visio Update direkt von Excel aus zu steuern
Freundliche Grüsse Herby
Hallo Herby,
wenn du bei bestimmten Fällen die Variable shapeOnLayer auf True setzt:
If LCase(shp.Layer(iLyr).Name) = LCase(lyrName) Then
shapeOnLayer = True
Else
und dies später abfragst:
If shapeOnLayer = False Then
lyr.Add shp, 0
End If
muss du am Anfang der Schleife die Variable wieder „zurück“ auf False setzen:
For iRow = 0 To UBound(rowIDs)
data = drs.GetRowData(rowIDs(iRow))
shapeId = data(idColumn)
lyrName = data(lyrColumn)
shapeOnLayer = False
Guten Abend René
Zuerst vielen Dank
Meine Programmier Skills sind leider zu
bescheiden um richtig folgen zu können.
Ich werde aber versuchen dies im Script
anzupassen und hoffe, dass es dann funzt. Auf jeden Fall ist es super, dass Du
die Probleme gefunden hast.
Lieber Gruss und einen schönen Abend (soweit man
das mit unserem tollen Somner überhaupt noch wünschen kann)
Herby
hoffe, Du hast einen schönen Urlaub ohne großen Regen, aber vielleicht Zeit für eine kleine Knobel-Aufgabe in VBA
Ich habe eine Tabelle, in der alles getan werden darf, d.h. auch gefiltert, aber nur nicht sortiert.
Schutz geht leider nicht, da sich dann leider die Tabelle nicht dynamisch erweitert.
In diesem Artikel steht, dass man mit Hilfe des Events „BeforeSort“ die Sortierroutinen abfangen kann.
Aber leider kriege ich das nicht hin und im Internet habe ich auch nichts gefunden. Weißt Du, wie man dieses Event in Excel platzieren kann? Würde mich freuen.
Hallo Johannes,
ich habe mal ein bisschen gewühlt und probiert:
1. Das SortObjekt existiert – allerdings besitzt es keine
Ereignisse (wie MS behauptet): Das sieht man, wenn man versucht in einem
Klassenmodul einzutragen:
Public WithEvents SO As so…
2. Ich habe überlegt, ob man die Symbole wegnehmen kann. Das
Problem: Man kann über die Registerkarte Start und Daten den Sortierbefehl
aufrufen; über das Kontextmenü oder über die Pfeilchen, die der Filter, die
intelligente Tabelle, die Pivottabelle filtern. Das heißt: es wird sehr mühsam,
dem Anwender die Symbole wegzunehmen.
3. Ich würde alle Zellen auf „nicht gesperrt“ setzen, das Blatt schützen – außer der Sortieroption. Dann kann der Anwender (fast) alles – was er nicht kann, ist beispielsweise einen AutoFilter einschalten.
mit großem Interesse verfolge ich Ihre Excel-Seminare und mag es sehr von Ihnen neue Dinge zu lernen und mein Wissen zu erweitern. Helfen Sie auch bei speziellen Excel-Problemen? Ich habe hier eine größere Datenbank. Basis sind verschiedene Materialnummer in einer Spalte, welche mehrmals auftreten, da es zu den Materialien mehrere Bestellungen gibt mit unterschiedlichen Konditionen. Mich interessiert pro Materialnummer der Maximalpreis. Ich habe hier an die 80 Materialien und dazu jeweils mehrere Bestellungen. Der Maximalpreis sollte dann in einer zz. Spalte erscheinen. Mit freundlichen Grüßen
Hallo Frau S., ich würde eine Pivottabelle erstelle. Gruppieren Sie die Materialnummern und ziehen Sie dann die Preise in das Wertefeld. Ändern Sie in den Wertfeldeinstellungen die Summe in MAX. Alternative: Wenn Sie Excel in Microsoft 365 haben: mit der Funktion EINDEUTIG erhalten sie die eindeutige Liste der Materialnummern. Mit MAXWENN können Sie das MAX pro ID berechnen lassen. Hilft Ihnen das? LG :: Rene Martin
Hallo Herr Martin, vielen Dank für die prompte Antwort. Die Pivottabelle ist der Lösung für mich. Entsprechende Seminare dazu stehen noch auf meiner persönlichen Agenda. Für den akuten Fall, haben Sie mir prima geholfen. Grüße,
Outlook-Schulung. Eine Teilnehmerin sagt, dass sie in Outlook über Ansicht den Aufgabenbereich „Aufgaben“ eingebunden hat. Allerdings werden die Aufgaben nur in diesem Aufgabenbereich angezeigt, wenn sie nach Outlook Heute (also auf ihr Postfach) wechselt.
Die Ursache ist schnell gefunden und erklärt: es gibt zwei Arten von Aufgaben: solche, die man selbst erstellt (sie heißen Aufgaben) und Mails, die man zur Nachverfolgung kennzeichnet (auf Wiedervorlage legt).
Damit die zwei unterschiedlichen Aufgaben zusammengefasst sind, kann man die Aufgaben von der Aufgabenliste in die Aufgaben schieben.
danke noch einmal für Deinen Hinweis!, allerdings gelingt mit solch ein Bild (siehe Deine EMail „Verweise VBAProject“ ) nicht. Da kommt kein „Solver“ vor. Und das Laden der „SOLVER32.DLL funktioniert zwar, aber Visio kann damit nichts anfangen (wie in meiner ersten Mail) schon geschrieben.
Nun habe ich ein 64-Bit -System. Leider ist aus der kargen Fehlermeldung die Ursache, dass Visio die SOLVER32.DLL nicht mag, nicht zu erklären. Und eine SOLVER64.DLL gibt es wohl nicht (soweit ich meine Suche auch ausdehnte).
Nun werde ich mich wohl mit der nächsten Microsoft-Unzulänglichkeit auch zufrieden geben müssen. So ein großer „Laden“ und soviel Mangel (es gibt ja auch so viel Versionen…)
Vergangenheit: Hätte ich im Steuerungsbereich meinen Kunden soviel „Nichtfunktion“ zugemutet – ich glaube, da wäre ich arm dran…
… und ich kann es nicht lassen, zu mailen, mit den Infos:
aktuelle Formel (Konstrukt von gestern), wo noch etwas fehlt: =WENN(ANZAHL(J9;L9;N9)=0;““; WENN(ODER(ANZAHL(J9;L9;N9)=2;ANZAHL(J9;L9;N9)=3); MITTELWERT(J9;L9;N9); „“))
drei Eingabezellen
wenn keine Eingaben in den Zellen >> Zelle mit Ergebnis ist leer
der MW wird berechnet, wenn zwei oder drei Zellen Werte enthalten
Frage: Kann man noch prüfen wenn ODER(Prüfung auf TEXT(J9), Prüfung auf TEXT(L9), Prüfung auf TEXT(N9)): „“ D.h.: Wenn mindestens eine der Zellen Text beinhaltet >>> Ergebnis der Zelle: Nix drinnen Ich habe schon ein wenig schlechtes Gewissen … Ich maile noch ein DANKESCHÖN & Gruß Jürgen
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Die Werte werden kummuliert. In der ersten Zelle steht
=WENN(F15<>"";F15;"")
Darunter befindet sich die Formel:
=WENN(G15<>"";G15+F16;"")
So hatte ich es vor vielen Jahren auch gemacht. Inwzischen löse ich das Problem der kummulierten Werte mit einer Formel, die ich nach unten ziehe:
=SUMME($F$15:F15)
Fazit: Lehrer und Lehrerinnen sollten regelmäßig ihre Übungsaufgaben durchsehen und überprüfen, ob es bessere Lösungestechniken, neue Funktionen gibt, mit denen man Aufgaben eleganer lösen kann und testen, ob sie einige Bedingungen vergessen haben.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Der prozentuale Anteil wird berechnet:
=WENN(UND(A15<>"";B15<>"");D15*100/$D$25;"")
Okay, mit der Funktion
UND(A15<>"";B15<>"")
wird überprüft, ob die Zellen der Spalte A und B gefüllt sind. Eine hübsche Übung, um UND und WENN zu üben.
Dennoch: WENNFEHLER wäre sicherlich besser gewesen, um ALLE Fehler abzufangen.
Aber noch mehr irritiert mich die Berechnung
D15*100/$D$25
Warum wird mit 100 multipliziert? Ich hätte den Anteil von einer Dezimalzahl in eine Prozentzahl formatiert. Und damit weitergerechnet.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
Ich komme bei der Verwendung der Funktion RANG ins Grübeln:
=RANG(B15;$B$15:$B$24;0)
Wenn man diese Funktion in Excel eintippt, sieht man vor dem FUnktionsnamen ein Ausrufezeichen. Das bedeutet, dass diese Funktion durch andere erstetzt wurde und aus Kompatibilitätsgründen noch zur Verfügung steht. Richtig: in Excel 2010 wurd diese Funktion RANG durch RANG.GLEICH und RANG.MITTELW abgelöst. Man findet RANG nun in der Kategorie Kompatibilität:
Das Problem bei der Funktion Rang ist die Antwort auf die Frage, welche Zahl weisen wir zwei gleich großen Werten zu? Also: wenn es den größten Werte zwei Mal gibt? Zählen wir dann 1; 1; 3 (so rechnet RANG.GLEICH und RANG) oder 1,5; 1,5; 3 – so rechnet RANG.MITTELW.
Ich probiere es aus, ändere zwei Werte so, dass sie gleich groß sind. Das Ergebnis: Fehler in der weiteren Berechnung, die nicht abgefangen wurden:
Fazit: vielleicht hätte der Lehrer oder die Lehrerin in den letzten zehn Jahren einmal das Beispiel neu nachrechnen sollen und auf Konsistenz prüfen sollen. Und vor allem: prüfen sollen, ob es inzwischen nicht neue, bessere Funktionen zur Lösung des von ihm oder ihr gestellten Problems gibt.
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen einige Formeln:
In Spalte A stehen Lieferantennummern, in Spalte B Umsatzzahlen. In Spalte C wird der Rang berechnet:
Danach Nummer 3, Nummer 4, … Uff! Kennt der Lehrer oder die Lehrerin nicht die Funktion ZEILE? Ich stutze und wundere mich …
#####
Hallo Rene,
kannst du vielleicht auch deinen Verbesserungsvorschlag für die Formel dazu schreiben. Mir erschließt sich grad nämlich nicht, wie man die Funktion ZEILE hier einbauen soll. Wahrscheinlich steh ich nur auf dem Schlauch…
Lieber Anonymous,
die Funktion ZEILE hat zwei „Gesichter“:
=ZEILE()
liefert die Zeilennummer der aktuellen Zelle. Steht also diese Funktion in C7,liefert =ZEILE() die Zahl 7. Beim Herunterziehen erhalte ich 8, 9, 10, …
Die Funktion der Lehrerin oder des Lehrers
VERGLEICH(1;$C$15:$C$24;0)
sucht den Wert 1 in der Rang-Spalte, also die Zeile, die den größten Wert enthält.
VERGLEICH(2;$C$15:$C$24;0)
sucht den zweitgrößten Wert.
Ich bin sicher, dass die Lehrerin oder der Lehrer die Werte 1, 2, 3, 4, … per Hand getippt hat. Da die Formel in D15 stand, hätte man auch schreiben (und herunterziehen) können:
Sie zeigt mir Beispiele von ihrem Lehrer, die sie verstehen und können muss, da ähnliche Beispiele Teile der Prüfung von Excel sind. Geübt werden sollen WENN und SVERWEIS:
Ich sehe folgende Formel:
=WENN(A17="";"";SVERWEIS(A17;Leistung;2))
Ist ja okay zu überprüfen, ob A17 leer ist, aber wäre es nicht besser ALLE Fehler mit einem WENNFEHLER abzufangen?
In der letzten Outlook-Schulung erzählt mir ein Teilnehmer, dass er gerne mit Kategorien arbeitet. Er hat sich mehrere Kategorien angelegt und weist den Mails diese Kategorien zu. Manche Mails liegen auch auf zwei Kategorien. Dann sortiert (also gruppiert) er nach Kategorien. Soweit so gut.
Nun möchte er eine Mail aus einer Kategorie löschen. Diese Mail steht jedoch ein zweites Mal in einer anderen Kategorie. DORT soll sie jedoch nicht gelöscht werden. Er will auch nicht die Kategorie von der Mail entfernen, da die Kategorie im Archivordner noch benötigt wird.
Er hat die Antwort selbst gegeben: Er kopiert die Mail, so dass sie zwei Mal vorhanden ist. Eine andere Lösung habe ich auch nicht gefunden.
ich
habe eine Frage zum Liniendiagramm, leider stimmen meine Werte nicht und ich
bekomme es nicht hin ohne das die Linien aus meinem Diagramm verschwinden.
Vielleicht
könne sie mir spontan helfen.
Danke
Hallo Frau D.,
Sie müssen die Reihe mit den „großen Zahlen“, also Jergl, Michal, Hans und Veitli auf eine Sekundärachse legen. Und dann möglicherweise die Skalierung der beiden Achsen anpassen.
Hoffe, dass du, deine Familie und dein Umfeld alle gesund seid.
Du hast mir vor einiger Zeit geholfen mehrere Belegnummern in eine Zelle zu schreiben. Nun wollte ich auch das Datum (auch mehrere) auf gleiche Weise anzeigen lassen. Herausgekommen sind dann die Zahl(en) vom Datum. Wenn nur eine Zahl (=Datum) angezeigt wurde konnte ich das Datumformat. Wenn nur ein Zahlenwert vorhanden ist, habe ich, wie gelernt, die Formel mit 1 multipliziert und dann das Datumformat angewendet. Meine Vermutung ist, dass man die Formel um eine Formatierungsformel ergänzt. Videos über die allgemeine Formatierung mit Formeln in der Zelle habe ich leider nicht gefunden. Bitte um Lösung.
der Kunde hat ein sehr „altes“ Excel (Excel 2013? Excel 2016?). Ich verwende die Funktion TEXTKETTE (in VBA: CONCAT). Genauer: mit dem Befehl
Do Until Len(Application.WorksheetFunction.Concat(xlBlatt.Range(xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 0), xlTabelle.Range.Cells(1).Offset(lngAnzahl + 1, 8)))) = 0
lngAnzahl = lngAnzahl + 1
Loop ' -- wie viele Zeilen sind gefüllt (wird in der Variable lngAnzahl gespeichert)
überprüfe ich, in wie vielen Zeilen der Liste etwas steht, beziehungsweise, ich suche die Zeile, in der die ersten neun Spalten leer sind oder ob die Formeln, die darin stehen, „“ ergeben.
Ich habe es geändert. Könntest du ihnen bitte diese Version schicken
Nicht nur Excel nervt – manchmal auch andere. Gestern hat mich Outlook erstaunt.
Outlook-Schulung. Wir sortieren unsere Mails nach Kategorien und nach den Fähnchen zum Nachverfolgen, indem ich auf die Überschrift klicke:
Eine Teilnehmerin möchte nach beidem sortieren: zuerst nach Kennzeichnungsstatus und anschließend nach Kategorie. Kein Problem: Über die Registerkarte Ansicht öffne ich die Ansichtseinstellungen und klicke dort auf die Schaltfläche „Sortieren“. Ich sortiere nach „Kennzeichnungsstatus“ und anschließend? Ich finde die „Kategorien“ nicht.
Okay, in den „verfügbaren Feldern“ gibt es „Alle Dokumentfelder“. Und dort „Kategorien“. Aber das führt zur Frage, ob ich eine neue Spalte hinzufügen möchte:
Ein Klick auf „Ja“ und ich habe eine weitere leere Kategorienspalte.
Erstaunlich: beim Suchen-Dialog gibt es weder eine Liste „Alle Felder“ noch kann ich die Kategien finden, um danach zu sortieren.
Und: ein erster Klick auf die Überschrift „Kategorie“ und ein zweiter auf „Kennzeichnungsstatus“ bewirkt nicht das Gewünschte: Outlook hat eigene Sortiervorstellungen.
Habe ich etwas übersehen? Oder nervt Outlook auch? Manchmal.
Gestern in der Excelschulung. Interessante Frage: Wenn ich ein Datum herunterziehe, ist die Schrittweise ein Tag. Wenn ich eine Uhrzeit herunterziehe ist die Schrittweise eine Stunde. Kann ich das in Excel einstellen, dass Excel im 15-Minuten-Takt zählt?
Ich überlege. Da gibt es doch den Assistenten Ausfüllen / Datenreihe im Register „Start“:
Allerdings: dort kann man nur die Schrittweite für Datumsangaben – nicht für Uhrzeiten eintragen:
Damit bleibt nur:
Entweder zwei Uhrzeiten eintragen, markieren und herunterziehen
Oder eine Formel: Bezug auf die obere Zelle und dann plus 15 (Minute) / 24 (Stunden) / 60 (Minuten):
Excelstammtisch. Hartmut zeigt, dass man das Datenmodell von Excel nach PowerBI importieren kann.
Ich frage, ob er wisse, wann das zu Problemen führt. Und zeige eine Datei:
Darin befinden sich Tabellen, die ins Datenmodell geladen wurden. Mit Hilfe des Datenmodells wurde eine Pivottabelle erstellt. Die Tabellen wurden mit Measures angereichert und sind untereinander verknüpft.
Nun will ich diese Datei (genauer: die Daten, Verknüpfungen und Measures) nach PowerBI importieren:
Ich erhalte eine Fehlermeldung – fast nichts wird importiert:
Wir machen uns auf die Suche – Hartmut wird fündig. Man darf nicht die Daten in Tabellen in der Arbeitsmappe halten und diese ins Datenmodell laden, sondern man muss sie mit PowerQuery importieren. So:
Diese Daten werden nun ins Datenmodell geladen – dort kann man sie verknüpfen
und mit Measures anreichern:
Das Ergebnis:
Ein erneuter Import nach PowerBI Desktop:
Klappt!
Ein Dankeschön an Hartmut Hilbich für das Suchen und Auffinden der Lösung des Importproblems. Hartmut schreibt dazu:
„Das Problem bestand hier (besteht!) darin, dass PBID das Datenmodell selbst sehr wohl importiert, aber nicht gleichzeitig auch die Quelltabellen!
Ich habe die
Quelltabelle mit PQ abgefragt und das PP-Modell exemplarisch mit 2 Measures
versehen. Der Import in PBID funktioniert dann einwandfrei!
Mein Fazit: Es ist nicht
ratsam, die Quelldaten physikalisch gemeinsam mit dem PP-Modell zu speichern.
Also entweder die Daten direkt mit PP abfragen, oder aber (besser) mit PQ
abfragen. Was also innerhalb von PP kein Problem ist, wird dann aber eines beim
Import in PBID.
So schwierig kann das wohl nicht sein, dachte ich. Und probierte es. Allerdings: die Lösung des Problems war doch komplizierter als gedacht.
Vor einigen Jahren hatte ich die Aufgabe in einer sehr großen Excelliste (zirka 60.000 Zeilen) die Daten „zu putzen“. Mitarbeiterinnen und Mitarbeiter hatten an unterschiedlichen Stellen in einer Spalte Informationen eingetragen – allerdings mehrere Informationen getrennt durch Trennzeichen. Durch verschiedene Trennzeichen – mal ein „/“, mal ein Semikolon, mal ein „:::“, mal ein „-„:
Ich habe damals einige VBA-Makros geschrieben, um die Daten „zu putzen“. Ich frage mich, ob man sie mit PowerQuery bereinigen kann. Man kann!
Ich erstelle eine Liste der Trennzeichen:
Ich importiere die Daten und trenne die Liste „hart“ an einem Zeichen:
trennt die Spalte. Wie kann man alle Trennzeichen verwenden? Ich importiere die Trennzeichenliste und wandle sie in über Transformieren / In Liste konvertieren in eine Liste um:
Kann SplitTextByDelimiter meine tbl_Trennzeichen verarbeiten? Nein!
Ich gehe auf die Suche:
SplitTextByAnyDelimiter kann die Liste verarbeiten:
Schlecht! Ich schaue den Parameter genauer an – er heißt:
columnNamesOrNumber
Also versuche ich eine Zahl. Ich beginne bei 99:
Der linke Teil sieht vielversprechend aus:
– der rechte nicht:
Ich überlege: ich muss berechnen wie viele neue Spalten erzeugt werden. Ich muss berechnen wie oft die Trennzeichen der Liste tbl_Trennzeichen in jedem der Texte vorkommt.
Leider stellt PowerQuery keine Funktion zur Verfügung, mit deren Hilfe man die Anzahl der vorkommenden Zeichen in einer anderen Zeichenkette ermitteln kann. So etwas berechne ich (auch in Excel) immer wie folgt:
Länge(Zeichenkette) - Länge(Zeichenkette ohne gesuchten Zeichen)
Ich überlege: minus jedes Element der Liste. Also genauer:
(Text as text) =>
List.Accumulate(
tbl_Trennzeichen,
0,
(state, current) =>
state +
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
)
Ich muss die Anzahl kumulieren. Der Befehl List.Accumulate tut gute Dienste. Er möchte eine Liste haben (tbl_Trennzeichen), einen Beginn (0) und eine Funktion. Diese Funktion erhält zwei Teile:
(state, current)
Die Variable state „merkt“ sich die Zahl, current greift auf jede Zeile zu. Allerdings darf ich nicht einfach die Differenz aus Länge vorher und Länge nachher bilden:
Gemeint ist – umgangssprachlich – der mittlere Wert einer Datenreihe. Hat man zwei mittlere Werte (bei einer geraden Anzahl Elemente), berechnet sich der Median als Durchschnitt der beiden mittleren Werte.
Wer braucht denn so etwas?
Stellen Sie sich vor, sie haben eine Basketballmannschaft, die aus fünf Spielern besteht: vier recht kleinen und einem sehr langen Spieler. Der Mittelwert würde das Bild „verzerren“, weil der Ausreißer den Durchschnitt nach oben verschiebt. Der Median gibt dagegen ein besseres Bild der Mitte, weil er resistent gegenüber Ausreißern ist:
Median – eine statistische Größe also. Nun finde ich in einer Berechnung von Transportkosten folgende Formel:
Verwundert reibe ich die Augen? Warum werden die Logistikkosten mit einem Median berechnet?
Dann verstehe ich: es wird Bezug genommen auf die drei Werte Länge, Breite und Höhe. Von diesen drei Werten wird der größte Wert (MAX), der kleinste Wert (MIN) und der mittlere Wert (MEDIAN) berechnet. Sehr clever!
„Kompilierungsfehler im ausgeblendeten Modul. Dieser Fehler tritt häufig auf, wenn der Code nicht mehr mit der Version, Plattform oder Architektur dieser Anwendung kompatibel ist. Klicken Sie auf „Hilfe“, um Informationen dazu zu erhalten, wie Sie diesen Fehler beheben können.“
Der Fehler entsteht, wenn ich im Code im Modul basKonstanten die Versionsnummer ändern will.
Nach Änderung und Neustart der Tabelle kommt o.g. Fehler. Danach kann kein Dashboard mehr ohne Fehler aufgerufen werden!
Wenn das so wieder wie vorher funktioniert, dann wäre es
perfekt.
Mit freundlichen Grüßen
Hallo Herr L.,
der Grund des Fehlers ist Folgender:
Das Datum war als Datum definiert in der Form #Monat/Tag/Jahr#. Ich weiß nicht, was Sie eingetragen haben – aber vielleicht nicht als Datum. Da dieses Datum nur einmal als Text verwendet und angezeigt wird, habe ich einen Text daraus gemacht (somit wird nicht mehr impliziert konvertiert – hier lief wohl etwas schief).
Public Const p_cdatAppStand As String = "08.07.2021"
Ich muss per Programmierung den Inhalt einer Formel mit einem Bezug auswerten. Ich überlege:
Jede Bezugsformel innerhalb einer Arbeitsmappe hat in Excel die Form:
=Blattname!Zellbezug
Prima! Liegt auf dem Blatt „BMW“ in der Zelle „Z8“ ein Wert, kann man die Formel
=BMW!Z8
gut auswerten, indem man an dem Ausrufezeichen trennt. Vor dem Ausrufezeichen: Blattname; hinter dem Ausrufezeichen: Zellbezug. Gesagt – getan.
Jedoch: mir fällt auf, dass mein Programm manchmal einen Fehler produziert. Beispielsweise beim Bezug auf das Blatt „Alfa Romeo“, „Aston Martin“, „Rolls-Roycs“, und so weiter. Klar, ein Bezug auf das Blatt „Alfa Romeo“ wird dargestellt als:
=’Alfa Romeo‘!P1
Da der Blattname ein Leerzeichen (oder Gedankenstrich) enthält, muss ich den Apostroph aus dem Blattnamen löschen. Wirklich? Sollte ich ihn nicht besser von links und rechts löschen? Also: Wenn das erste Zeichen = ‚, dann entfernen. Wenn das letzte Zeichen = ‚, dann entfernen? Kann ein Blattname ein Apostroph enthalten? Ich probiere aus:
Tatsächlich: Mercedes‘ Benz funktioniert! Excel verbietet bei Namen von Tabellenblättern am Anfang und am Ende ein Apostroph, aber innerhalb des Namens ist es erlaubt.
Und wie sieht der Bezug auf dieses Blatt aus?
='Mercedes'' Benz'!W117
Erstaunlich! Der Apostroph wird entwertet, indem das Zeichen zwei Mal geschrieben wird! Das ist mir noch nie aufgefallen!
Das heißt: ich muss zwei Hochkommata (‚ ‚) durch eines ersetzen und muss den Apostroph am Anfang und am Ende löschen. Perfide!
Übrigens: Bevor Sie jetzt erboste Kommentare schreiben: die in Sindelfingen produzierende Automobilfirma heißt MERCEDES BENZ – ohne Apostroph!
Hallo Herr Martin, im Anhang sende ich Ihnen eine Exeltabelle mit einer „mauell erstellten Kopfzeile“ und einer Zeilenschaltung in der Zelle „Anschrift“. Für einen Serienbrief benötige ich die „Kopzeilen“ ebenso die Zeilenschaltung nicht. Wie entferne ich am schnellsten die „Kopfzeilen“ und die Zeilenschaltung in der Zelle. (Teilenschaltung in einer Zelle zu entfernen, habe ich in Ihren Videos schon gefunden). Ich möchte alle Daten in einer Spalte haben. Ich würde mich freuen, wenn Sie mir dabei helfen würden. Mit freundlichen Grüßen PV (Ein Fan Ihrer Office-Kurse)
Hallo Herr V.,
das habe ich
gemacht :
* Mit Suchen und Ersetzen die Zeichenschaltung (Strg + J) durch einen Schrägstrich ersetzt. (die Zeichenschaltung hat den Code 10 – man kann auch die Funktion WECHSELN verwenden:
=WECHSELN(F4;ZEICHEN(10);"/")
* den Verbund aller verbundenen Zellen aufgehoben
* den Textumbruch entfernt
* mit einem AutoFilter in der Spalte „Card Number“ den Text „Card Number“ und die leeren Zellen gefiltert und entfernt.
* die leeren Spalten gelöscht.
Hallo Herr Martin, ich habe noch etwas vergessen. In der Spalte „Badge Holder Name“ sind viele Namen nicht korrekt geschrieben. Wie kann ich Straßennamen schnell ändern. Ich mache es oft über Suchen + Ersetzen. Aber in vielen Situationen ist das nciht möglich. Gibt es spezielle Formel oder Funktionen die das erleichtern. Nach dem Säubern der Exeltabelle, wie kann ich dann automatisch eine Kopfzeile erstellen, so das man die Mitgliederliste anschließend ausdrucken oder auch ein PDF schreiben kann. Ich freue mich über Ihre Nachricht.
Und zu Ihrer Frage: wenn Sie immer die gleichen Ersetzungen haben, erstellen Sie eine Tabelle und verwenden die Excel-Funktion WECHSELN.
In Excel liegen einige Zahlen als Währung vor. Ich erstelle ein 2D-Säulendiagramm mit gruppierten Säulen. Ich lasse mir die Datenbeschriftung der Säulen anzeigen und drehe die Zahlen um 90 Grad:
An anderer Stelle wird eine weitere Datenreihe eingefügt – diese soll mit ins Diagramm integriert werden:
Das Verblüffende: Die neuen Zahlen werden nun mit der Währung $ formatiert:
Werden die Zahlen mit dem Zahlenformat Buchhaltung formatiert, werden drei Nachkommastellen eingefügt:
Auch bei Dezimalzahlen tritt dieser Effekt auf:
Jedoch: NICHT IMMER! Ich weiß nicht, wann diese Zahlen falsch formatiert werden:
Eine sehr hübsche Knobelaufgabe. Ich erstelle Formulare für eine Firma, die Waren liefert. Dabei sind einige komplexe Berechnungen nötig. Beispielsweise folgende:
„Sollte das Lieferdatum in eine Woche mit einem Feiertag fallen, werden 25% mehr Kosten berechnet“.
Ich frage nach: „Welche Feiertage?“ Die Antwort: die Feiertage von NRW.
Zweite Frage: Wenn der Feiertag auf einen Samstag oder Sonntag fällt? Die Antwort: dann soll er nicht berücksichtigt werden.
Erster Schritt: Ich erstelle eine Liste der Feiertage von Nordrhein-Westfalen:
Man kann die beweglichen Feiertage auf Basis des Ostersonntags berechnen, für den es eine Formel gibt. Und die festen Feiertage berechnen. Oder man kopiert sich diese Liste aus dem Internet. Oder greift mit PowerQuery auf eine Feiertagsliste im Internet zu.
Im zweiten Schritt erstelle ich eine Spalte mit Datumsangaben – beispielsweise vom 30.12.2019 bis zum 31.12.2034.
prüft, ob das Datum ein Feiertag ist (also in der Feiertagsliste steht) und ob der Feiertag auf einen Tag von Montag bis Freitag fällt (also Wochentag <= 5):
Danach überprüfe ich, ob in der Woche ein Feiertag („F“) liegt:
PowerBI-Schulung. Wir greifen auf Excelmappen zu, die auf Sharepoint liegen. Es kommt die Frage, ob man auch auf einen Sharepoint-Ordner zugreifen kann. Klar kann man:
Man muss nur den Ordnerpfad kopieren und eintragen:
Und – erhält einen Fehler:
Ach, klar, natürlich: man muss sich natürlich noch anmelden. Ist ein bisschen versteckt:
Erstaunlicherweise kann man JETZT OHNE Anmedlung in Excel über PowerQuery auf einen Sharepoint-Ordner zugreifen. Muss ich das verstehen?
Amüsant: ich habe eine große Excelliste mit mehrere Tausend Datensätzen. Ich bearbeite sie in PowerQuery:
Ich importiere eine zweite Liste und verknüpfe sie mit einem Left outer Join:
Das Ergebnis sieht in PowerQuery gut aus:
Ich lade die Tabelle zurück nach Excel und erhalte einen Fehler:
Zurück zu PowerQuery versuche ich einen Right outer Join:
Die Ursache? PowerQuery zeigt nur 1.000 Datensätze. Wenn in der Liste DANACH eine Zelle mit einem fehlerhaften Wert steht, wird er bei einem Left Outer Join nicht angezeigt. Erst in Excel. Natürlich kann man sich in PowerQuery auf die Suche nach dem fehlerhaften Datensatz machen und ihn entfernen. Oder in Excel:
Danke an Christa für diesen Hinweis und danke für die Bemerkung, dass die Fehlermeldung in älteren PowerQuery-Versionen eine andere war:
Konto anlegen und löschen funktioniert soweit, bis auf das Problem, was wir schon mal hatten und gelöst wurde (Numerischer Kontoname z.B. 01 -> wird darauf 1 generiert!). Das hatten Sie bereits schon super umgesetzt.
Also formatiere ich die Spalte mit den Kontonummern erneut als Text. Mal sehen, wann Herr L. es wieder „kaputt macht“.
Herr L. schickt mir ein Video, das er erstellt hat, damit ich besser erkenne, woran der Fehler liegen könne, der in Excel auftritt. Ich öffne das Video und erhalte folgende Fehlermeldung:
Wiedergabe nicht möglich. Schaffen Sie den Inhalt erneut an.
Ich öffne eine Testdatei, die mir ein Anwender zugeschickt hat, und wundere mich, wo das Menüband (das Ribbon) ist.
Dann fällt es mir ein: es gab da doch … Richtig:
Es gab einen Excel 4.0-Makrobefehl, mit dem man die Symbolleiste ausblenden konnte. Dieser funktioniert im aktuellen Excel noch immer und blendet hier das Menüband aus. Der Befehl lautet:
mein Bekannter hat
sich die neue Version angeschaut.
Jetzt lässt sich der Datensatz zwar anlegen, aber es treten neue Fehler auf, die bisher noch nicht vorhanden waren.
In der Tabelle erscheint nach dem Löschen des Datensatzes ein Fehler.
Hallo Herr L.,
öffnen Sie mal bitte eine ältere Version und klicken Sie dort auf Datenverwaltung und löschen Sie einen Datensatz.
Klicken Sie anschließend auf den Datensatz direkt über dem Datensatz, den Sie gelöscht haben.
Sie erhalten den Fehler.
Heißt: DIESER
Fehler war schon lange drin, bevor Sie mir die Datei geschickt haben.
Woher kommt er?
Beim Löschen einer Zeile liefert die Zeile darüber einen Fehler:
Sie greifen auf den
VALUE dieser Zelle zu – das knallt!
Die Ursache des
Fehlers:
In der Spalte K (Membership) greift die Formel für den Wert „Silber“ auf die Zeile darunter zu! Die Formeln dieser Spalte sind falsch! Schon bevor Sie mir die Datei geschickt haben!
=WENN(UND(H2="Spain";J2>200);"Black";WENN(UND(H2="Spain";J2>180);"Platinum";WENN(UND(H2="Spain";J2>150);"Gold";WENN(UND(H3="Spain";J2>140);"Silber";WENN(UND(H2="Spain";J2>130);"Standard";WENN(UND(H2="Spain";J2>50);"Blue";"out of order"))))))
Fazit: es sind nicht neue Fehler – in Ihrem Programm sind einige alte Fehler, für die ich mich nicht verantwortlich zeichne.
An dieser Maske dlgKundenverwaltung habe ich nichts geändert.
schöne Grüße
Rene Martin
*) An Gott glauben wir; alle anderen müssen Daten zeigen!
Ich erstelle ein Eingabeformular in Excel für eine Firma. Da mit einem Wert, beispielsweise 7,75 €, weitergerechnet wird, lösche ich den Text „7,75 € (nicht enthalten in Frachtraten)“, der in der Zelle stand, ersetze ihn durch die Zahl 7,75 und formatiere die Zelle mit einem benutzerdefinierten Zahlenformat
0,00" € (nicht enthalten in Frachtraten)"
Nach einer Weile stelle ich erstaunt fest, dass der Text als Quickinfo angezeigt wird. Was passiert hier? Was habe ich hier gemacht?
Nein – es liegt keine Notiz und kein Kommentar unter der Zelle.
Nein – es liegt keine Datenüberprüfung mit einer Eingabemeldung unter dem Text.
Nein – es wurde auch kein QuickInfo eines Hyperlinks verwendet:
Erstaunt reibe ich die Augen. Das habe ich selbst gemacht! Aber wie?
Schließlich komme ich hinter des Rätsels Lösung:
Trägt man in Excel in eine Zelle eine Zahl ein, formatiert diese Zahl (beispielsweise als Währung) und verkleinert die Spalte so, dass die formatierte Zahl in der Zelle nicht angezeigt werden kann, wird die formatierte Zahl als Quickinfo angezeigt, wenn sich der Mauszeiger darüber bewegt. Aha!
Wenn ich nun mehrere Zellen markiere und die formatierte Zahl „über die Auswahl zentriere“:
wird die Zahl klar lesbar in den Zellen angezeigt. Da die Zahl aber für die Zelle, in der sie sich befindet, zu „breit“ ist, bleibt das QuickInfo stehen:
Eben! Und so kann ich eine Zahl über mehrere Zellen ausrichten …
mir ist ein Fehler
aufgefallen, der vorher nicht vorhanden war.
Die Excel-Tabelle
in Excel 365 funktioniert tadellos, soweit ich getestet habe.
Ein Freund von mir
hat leider aus der alten Version die Daten nicht in die neue Version reinbekommen
und so hat er sich entschlossen, diese neu einzugeben.
Wenn er ein Konto
anlegt – das geht noch.
Dann will er
Einzahlungen in das Einzahlformular hinzufügen, dies scheint erst mal nicht zu
funktionieren. In der Liste zeigt er nichts an!
Wenn ich mir die
Tabelle außerhalb des Formulars anschaue, sind die Daten angelegt – Die
Paketnummer wird nicht mehr hochgezählt.
Auch ein speichern
und erneuter Start der Tabelle bringt keine Abhilfe.
In der alten Version (bei mir 6.11) funktioniert es noch tadellos. Mein Bekannter setzt Excel 2016 (neuste Updates sind installiert) ein. Bei mir geht es, bei ihm nicht, mit der letzten Version.
#####
Hallo Herr L.,
in Ihrem Programm finde ich die Codezeile:
If rngI.Value = Me.cmbAuswahlKontoAlleInvestments.Value Then
Sie prüfen, ob eine Kontonummer ausgewählt wurde. Da Kontonummern Zahlen sein können (4711) vergleichen Sie diese Zahl mit dem TEXT aus der Combobox (alle Steuerelemente liefern immer Texte).
Deshalb kann es
nicht funktionieren! Der Fehler war vorher schon vorhanden; ist Ihnen in IHRER
Liste nicht aufgefallen, weil dort alle Konten alphanumerisch ist.
Ich habe es korrigiert:
If CStr(rngI.Value) = Me.cmbAuswahlKontoAlleInvestments.Value Then
Gestern habe ich für den Excelstammtisch einige Dateien vorbereitet, um zu zeigen, was PowerQuery verlangsamt. Ich habe eine Liste mit Dummy-Namen mit 20.000 Datensätzen:
Diese verknüpfe ich mit einer Liste, die zwei Zeilen lang ist:
Das Ergebnis: 28.877 Datensätze
Ein zweiter Blick auf die Liste zeigt, dass einige Zeilen (nicht alle!) nun zwei Mal in der Liste auftauchen:
Nein – an der Verwendung eines Primärschlüssels liegt es nicht – die Zeile
Trägt man in Word in einer Tabelle Text ein, verbreitet sich die Spalte, in der der Text steht.
Diese (oft nicht gewünschte) Eigenschaft kann über das Symbol „AutoAnpassen“ der kontextsensitiven Registerkarte „Layout“ ausgeschaltet werden. Dort kann man von der Option „Automatisch an Inhalt anpassen“ an „Feste Spaltenbreite“ umschalten.
Leider zeigt Word nicht an, welche Option aktiviert wurde – keiner der drei Optionen ist mit grauer Farbe unterlegt.
Auf einem Formular soll – unter anderem – der Preis für eine gelieferte Menge berechnet werden – in Abhängigkeit vom Gewicht. Dafür gibt es eine Tabelle:
In der Originaltabelle befand sich der Text „kg“ hinter den Zahlen – den habe ich schnell gelöscht. Eine schöne Aufgabe für XVERWEIS denke ich – eine Formel – alles drin, alles dran …
Mich beschleicht ein Gedanke …
Ich rufe den Kunden an und bitte ihn in einer leeren Excelmappe die drei Zeichen =XV zu tippen. „Ich sehe nichts“ lautet die Antwort. Das heißt: sie haben noch eine ältere Excel-Version, in der die Funktion XVERWEIS und XVERGLEICH noch nicht vorhanden sind. Damit natürlich auch noch nicht die neuen und ach so praktischen Matrixfunktionen. *gggrrrrr*
Ich bin verblüfft. Dass Excel sehr viele Algorithmen beinhaltet, die zum Teil hilfreich zum Teil verwirrend sind, ist bekannt. Aber diesen Mechanismus kannte ich bislang noch nicht:
Drei untereinander stehende Zellen sind auf die gleiche Art formatiert (beispielsweise Zahlenformat oder Hintergrundfarbe) und mit Text gefüllt.
Trägt man eine weitere Information darunter ein, so wird das Format übernommen. Das war mir bekannt. Aber ich wusste nicht, dass es auch bei einer Leerzeile funktioniert:
Hat man eine formatierte Zelle und darunter eine Leerzeile, so wird die nächste Zelle bei der Texteingabe wieder formatiert. Beim dritten Mal endet der Spuk jedoch:
Das kann praktisch sein, es kann aber auch verwirrend oder störend sein.
Immerhin: man kann es deaktivieren über: Datei / Optionen / Erweitert / Gruppe [Bearbeitungsoptionen] / Datenbereichsformate und -formeln erweitern.
Ich habe in angehängter Datei im Inhaltsverzeichnis den Fall, dass die Seitenzahlen in einigen Überschriften (Brote, Pizza, Gebäck, …) nicht rechtsbündig stehen.
Hast Du eine Idee, woran das liegt?
Danke, Gunnar
klar, Gunnar,
Word: davon lebe ich auch. Normalerweise.
Die Antwort ist denkbar einfach: „Brote“ und „Gebäck“ basieren auf der Formatvorlage „Verzeichnis 2“ und „Verzeichnis 3“. Dort ist ein hängender Einzug von 1,25 cm eingestellt. 1,25 cm heißt um 1,25 cm, nicht auf die Position 1,25 cm. Da diese Wörter kürzer als 1,25 cm springt der Tab auf den gesetzten Einzug.
Die Lösung: Einzug auf 0,5 cm oder 0 setzen. Dann klappt es.
Liebe Grüße
Rene
Fazit: Nicht alles, was nach Bug aussieht, ist auch einer. Manchmal sind auch die Menschen, die vor dem Computer sitzen, die Ursache für Fehler.
Einzelplatzschulung. Oder „Coaching“, wie es auf Deutsch heißt. Ein älterer Herr möchte die Grundlagen der Anwendungsprogramme lernen.
Wir beginnen mit Outlook. Die Möglichkeit einen Screenshot zu erstellen und in eine Mail einzufügen gefällt ihm. Ich erkläre ihm das Vorgehen:
Es funktioniert auch in anderen Programmen, erläutere ich und zeige ihm Excel. In der Registerkarte „Einfügen“ fällt sein suchender Blick auf die Mitte der Registerkarte, wo „Screenshot“ auch in Outlook zu sehen war. Kein Screenshot!
Ein Blick streift nach recht – kein „Screenshot“-Symbol. Ah – etwas weiter links – DA werden wir fündig.
Und PowerPoint? Dort finden das Symbol noch weiter links:
Immerhin: in allen Anwendungsprogrammen (auch Word) befindet sich dieses Symbol in der Gruppe „Illustrationen“.
Um die Suche zu erleichtern, erläutere ich ihm das Windows-eigene Programm „Snipping Tools“.
Letzte Woche hatte ich eine Einzelplatzschulung. Nennen wir es Coaching: ein älterer Herr wollte sich fit in den Anwendungsprogrammen machen. Ich erkläre die Grundlagen: Text und Zahl. „Bitte schließen Sie die Eingabe immer mit [Enter] ab.“ (Dass es noch andere Möglichkeiten gibt, wusste er selbst.) „Bitte drücken Sie immer die Ok-Taste. In der Kirche wird sie auch die [Amen]-Taste genannt.“ Er schmunzelt.
Wir üben Formeln und drücken [Enter]. Immer. Nicht wegklicken! Okay – verstanden.
Wir ziehen die Formel am Ausfüllkästchen nach unten. Er drückt [Enter]. Okay … ich muss mich ein bisschen korrigieren.
Ich erstelle eine Userform in Excel VBA. Darin sollen Werte „nach unten“ weitergegeben werden, wenn ein Kontrollkästchen angeklickt wird. Die Kästchen heißen chkFamilie02, chkFamilie03, chkFamilie04, … chkFamilie12
Ich prüfe, ob das letzte ausgewählt wurde oder ob noch weitere unten ausgewählt wurden:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False Or frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False Or intZeile > 12
Das läuft an die Wand – eine Fehlermeldung ist die Folge. Mein Denkfehler:
wenn intZeile > 12, dann wird das geürft. Beispielsweise: intZeile hat den Wert 13. Allerdings: es wird auch geprüft, ob das Control chkFamilie12 sichtbar ist. Und das gibt es nicht!
OR (und auch AND) in VBA prüft (leider!) immer alle Teile. Und stoppt nicht, wenn einer der beiden Zweige falsch ist. Also anders gelöst – nicht ganz elegant – aber okay:
Do Until frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Visible = False frmAuswahl.Controls("chkFamilie" & Format(intZeile, "00")).Value = False
strZeile = Format(intZeile, "00")
frmAuswahl.Controls("lblInfoZeile" & strZeile).Caption = strText
intZeile = intZeile + 1
If intZeile > 12 Then Exit Do
Loop
Ich habe hier einige Artikel zu dem kostenlosen PlugIn Excel-DNA geschrieben, mit dem man Excelfunktionen (und andere Werkzeuge) in Excel schreiben kann und welche Probleme es dabei gibt.
Nun habe ich einen Artikel – nein: genauer: es sind drei Artikel – darüber veröffentlicht: in der neuen dotnetpro.
Die Datenüberprüfung in Excel ist hinlänglich bekannt. Ein sehr praktisches Werkzeug, mit dem man eine vordefinierte Liste an Begriffen für Zellen bereitstellt.
Oder sicherstellt, dass nur bestimmte Werte in eine Zelle eingetragen werden.
Allerdings: bei sehr vielen Auswahlmöglichkeiten (sprich: langen Listen) ist die Suche und die Navigation sehr mühsam. Außerdem muss man sich auf feste Listen beschränken und darf keine freien Einträge verwenden. (ich habe auf diesem Blog schon einige Male gelästert und gespottet). Ich habe ein kleines Add-In geschrieben – Strg + Q (so wird es gestartet), mit dem eine bequeme Auswahl und eine freie Texteingabe möglich ist.
Interessiert? Für private Nutzung stelle ich es kostenlos zur Verfügung; für geschäftliche Verwendung hätte ich gerne 10 Euro. Dann erhältst du auch eine genaue Beschreibung der Installation und Bedienung und eine Rechnung. Interessiert? Gerne kannst du es auf meiner Seite compurem.de herunterladen – du findest es hinter dem Menü Programmierung ::: [Strg] + [Q].
Wenn man mit VBA programmiert und wissen möchte, ob in einem Text ein anderer vorhanden ist, kann man mit den Funktionen Left, Right, Mid oder Instr arbeiten. Oder den Vergleichsoperator Like verwenden. Also beispielsweise
If txtIBAN.Value Like „DE*“ Then …
Die Aufgabe: ich will Namen in Excel prüfen. Ich möchte wissen, ob sie auf intelligente Tabellen verweisen, also einen Aufbau haben, wie beispielsweise:
=tbl_Feiertage![#Alle]
oder:
=tbl_Feiertage[2021]
Also prüfe ich:
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).RefersToLocal Like "=*[*]" Then
MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
End If
Next
Und wundere mich, warum die If-Verzweigung nichts findet.
Okay – noch ein Versuch – ich lasse die letzte eckige Klammer weg und prüfe erneut:
For i = 1 To ThisWorkbook.Names.Count
If ThisWorkbook.Names(i).RefersToLocal Like "=*[*" Then
MsgBox ThisWorkbook.Names(i).Name & " bezieht sich auf: " & ThisWorkbook.Names(i).RefersToLocal
End If
Next
Verwundert reibe ich mir die Augen. Was klappt nicht? Die Prüfung
If ThisWorkbook.Names(i).RefersToLocal Like "=*" Then
Bernhard hat mich darauf aufmerksam gemacht. Ist mir bislang nicht aufgefallen.
Die Funktion DATEDIF in Excel und die VBA-Funktion DateDiff rechnen unterschiedlich.
Trägt man in zwei Zellen die Datumsangaben 20.05.2021 und 01.06.2021 ein, so beträgt bei der Excelfunktion DATEDIF mit dem Parameter „M“ das Ergebnis 0, bei der VBA-Funktion dagegen 1.
Ich habe eine kleine Tabelle aufgebaut: im oberen Teil einige Datumsdifferenzen auf Basis des Monats:
Im unteren Teil verwende ich ein kleines VBA-Makro:
Sub BerechneDateDIFF()
Dim intZeile As Integer
Dim intSpalte As Integer
For intZeile = 21 To 30
For intSpalte = 2 To 20
ActiveSheet.Cells(intZeile, intSpalte).Value = DateDiff("M", ActiveSheet.Cells(intZeile, 1).Value, ActiveSheet.Cells(20, intSpalte).Value)
Next intSpalte
Next intZeile
End Sub
Das Ergebnis:
Die Unterschiede habe ich mit einer bedingten Formatierung farblich hervorgehoben.
Ein Dankeschön für den wertvollen Hinweis an Bernhard Ramroth.
Zuerst habe ich mich geärgert. In PowerQuery gab es früher ein Symbol „Von Tabelle“. Daraus wurde in der Gruppe „Daten abrufen und transformieren“ das Symbol „Aus Tabelle/Bereich“.
Seit ein paar Tagen heißt es nun „Vom Blatt“
Muss das sein? Ständiges Umbenennen?
Frank Arentd-Theilen hat mich auf den Grund hingewiesen (danke für den Hinweis):
Ja – denn nun kann man Listen in Excel, die mit den neuen Arrayfunktionen erstellt wurden, beispielsweise mit FILTER, SORTIEREN und SORTIERENNACH in PowerQuery importieren:
Das funktioniert auch mit der Funktion SEQUENZ:
Okay – zugegeben – leider nicht immer. Wenn diese Matrixfunktionen innerhalb einer Liste stehen, wie beispielsweise hier in diesem Monatskalender:
dann wandelt PowerQuery die gesamte Liste in eine (intelligente) Tabelle um und – scheitert! Klar: Tabellen dürfen keine Matrixfunktionen verwenden …
Ich soll ein Excel-Formular (per VBA) auswerten. Schnell stelle ich fest, dass einige Zellen gesperrt sind. Das ist nicht sehr geschickt, da diese Zellen vom Anwender und von der Anwenderin ausgefüllt werden sollen:
Wie finde ich diese Zellen? Es gibt leider keine Suchoption dafür, so dass alle gesperrten oder nicht gesperrten Zellen markiert werden.
Also anders: mit der Ersetzenfunktion gelingt es: Öffnet man die Optionen, kann man in Excel nach Formaten suchen. Eben: beispielsweise nicht gesperrte Zellen. Es erweist sich geschickter, die offenen Zellen zu finden, als die gesperrten, da alle Zellen in den 1.048.567 x 16.384 Zeilen und Spalten gesperrt sind.
Und diese werden durch eine Hintergrundfarbe ersetzt. So findet man schnell die nicht gesperrten Zellen:
Gestern auf dem Excelstammtisch stellte Volker folgendes Problem bei/mit intelligenten (dynamischen; strukturierten) Tabellen vor.
Wir haben eine Liste, die zu einer intelligenten Tabelle verwandelt wurde:
Die Tabelle heißt tbl_Planeten.
Auf einem zweite Tabellenblatt wird Bezug auf diese Tabelle genommen; genauer: auf jede Spalte:
=WENNFEHLER(tbl_Planeten[@Planet];"")
Erstaunt stellt man fest, dass der erste Planet – Merkur – fehlt. Die Antwort ist simpel: „@“ bezieht sich auf die Informationen der aktuellen Zeile. Da die zweite Tabelle erst ab Zeile zwei beginnt (die erste fängt in der ersten Zeile an), ist der Bezug versetzt. Man muss also bei Tabellen gleich positionieren. Das birgt Gefahren.
Fazit: besser SO nicht Tabellen miteinander verknüpfen. Es gibt bessere Lösungen: PowerQuery sei an dieser Stelle genannt. Oder relative Bezüge.
Danke an Volker für diesen amüsanten und wichtigen Hinweis!
Ich suche jemanden, der fit in power query und power pivot
ist und dem ich ca. eine stunde lang fragen stellen kann.
Hintergrund: Ich habe einen größeren Auftrag, da geht es um mehrere Tools im Excel-Umfeld, da geht es bei einem Tool jetzt erst mal darum, ob power query da was bringen würde.
Ich hatte Schlimmes oder Schwieriges befürchtet. Aber die zentrale Frage war weder schlimm noch schwierig zu beantworten.
Gegeben sei eine Auftragstabelle mit Verkaufsdaten:
Diese Liste wird nach PowerQuery gezogen und dort bearbeitet. Das Ergebnis wird zurückgegeben:
Die erste Frage lautete: Wie kann man Anfang und Ende als Filter in PowerQuery einbauen?
Die Antwort:
Man muss die jeweils zwei Zellen in eine intelligente Tabelle konvertieren.
Man muss diese ebenso nach PowerQuery importieren.,
Dort den Datentyp in Datum ändern.
Und dort ein Drilldown durchführen. Das heißt: die Tabelle in einen Wert, besser: in eine Variable, verwandeln.
Diese Variable hat einen Namen – er kann verwendet werden.
Also so:
Man schaltet einen beliebigen Datumsfilter ein („Zwischen“):
Und ersetzt in M die beiden Werte durch die Variablennamen:
Fertig! Test in Excel:
Und natürlich kam danach die Frage:
ich möchte die berechnung lieber in PowerPivot vornehmen und mit einer Pivottabelle gruppieren und das Meassure verwenden.
Ich habe jetzt in DAX diesen ausdruck, der funktioniert:
Ich sollte doch eine Seite „Outlook-nervt“ eröffnen. In letzter Zeit werde ich häufig mit Outlook-Fragen, Outlook-Problemen und Outlook-Wünschen konfrontiert. Beispielsweise mit folgendem Wunsch:
Sehr geehrter Herr Martin, Sie haben vor geraumer Zeit einen Outlook-Kurs für meine ganze Abteilung abgehalten und weil Sie Spezialist im Outlook sind möchte ich mich mit einer Frage vertrauensvoll an Sie wenden. Ich hoffe das ist in Ordnung für Sie. Ich versuche das Problem zu erklären: Wir haben 2 Kunden die eine ähnliche Email Adresse haben: die eine fängt mit „contoso“ an die andere mit „re-contoso“. Fakt ist, dass wenn man die im Email-Feld „An“ angibt, dass man die leicht verwechseln kann. Genau das ist auch kürzlich passiert und somit hat ein Kunde die Unterlagen vom anderen Kunden erhalten, was sehr problematisch ist. Jetzt meine Frage an Sie: Gibt es eine Möglichkeit, dass man bei diesen 2 Email-Adressen so eine Art Sperre generiert? Wie zum Beispiel durch ein Fenster mit der Frage: sind Sie sicher, dass Sie diese Email an diese Adresse versenden möchten? Es würde mich sehr freuen, wenn Sie sich mit mir in Kontakt setzen würden. Bis dahin wünsche ich Ihnen noch einen schönen Tag. Mit freundlichen Grüßen
Hallo Frau L., mit einer Regel geht so etwas leider nicht – eine Regel kann man nur erstellen für „Mail die an … gesendet wurde“.
Aber mit einem kleinen VBA-Skript geht das:
Fügen Sie in Outlook die Registerkarte „Entwicklertools“ ein:
Wechseln Sie in dort in die Entwicklungsumgebung „Visualbasic“
Doppelklicken Sie im Projektfenster auf ThisOutlooksession:
Wechseln Sie auf der rechten Seite über das Dropdown zu Application:
Es öffnen sich folgende Codezeilen:
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
End Sub
Dieses Ereignis überwacht das Senden einer Mail. Dort hinein muss folgender Code, so dass das Ganz dann so aussieht.
Option Compare Text
Private Sub Application_ItemSend(ByVal Item As Object, Cancel As Boolean)
On Error Resume Next
If Item.To Like „contoso“ Then
If MsgBox(„Wollen Sie diese Mail wirklich an “ & Item.To & “ senden?“, vbInformation + vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
End If
End If
End Sub
Versuchen Sie es am besten mit dem Namen einer Kollegin (oder Ihrem Namen). Sie können Groß- und Kleinschreibung ausschalten, indem Sie VOR das Makro den Befehl
Kommen Sie damit klar?
schöne Grüße
René Martin
Wir haben es heute zusammen an ihrem Rechner implementiert. Sie ist sehr glücklich über das Ergebnis.
Wer macht denn So etwas? Warum macht Andreas Thehos SO etwas? Ich weiß es nicht. Aber es ist interessant und amüsant:
Er schreibt in Word einen Text und fügt einen Kommentar ein:
Der Text wird nach Excel kopiert – mit der Option „Ursprüngliche Formatierung beibehalten“:
Der Text des Kommentars wird in eine eigene Zelle eingefügt.
Nun wird ein Zellformat von einer anderen Zelle auf die Zelle mit dem Text übertragen:
Das Ergebnis verblüfft. Oder auch nicht?!
Die Formatierung endet an der Stelle, an der der Kommentar stand.
Das muss ich genauer anschauen!
Ich zerlege den Text mit der Funktion TEIL in seine Bestandteile, beispielsweise mit:
=TEIL($A$1;SPALTE(BD1);1)
Die Funktion wird nach rechts gezogen:
An der Stelle Kommentar|Leerzeichen befinden sich jetzt zwei (!) Zeichen. Mit der Funktion CODE sehe ich es mir genauer an:
Die Funktion CODE liefert die Zahl 32 – klaro: für das Leerzeichen und die Zahl 160!?! Sie liegt zwischen dem Zeichen Ÿ und dem umgekehrten Ausrufezeichen, wie es im Spanischen verwendet wird:¡
Danke Andreas, für diesen interessanten Hinweis. Es bleibt für mich immer noch die Antwort auf die Frage offen – wer macht denn so etwas?
Umgekehrt – ich hatte vor einigen Jahren mal ein langes Gespräch mit dem Verantwortlichen von LibreOffice. Er hat mir gesagt, dass das Werkzeug „Format übertragen“ die Hölle ist (LibreOffice hat sich lange geweigert so etwas zu implementieren, haben aber schließlich dem Druck der Pinsel-Liebhaber aus der Microsoft-Fraktion nachgegeben) – denn – welches Format wird übertragen? Was ist Format? Ist Kommentar auch Format? …
Übrigens, ist jetzt auch zwei Kollegen passiert, die Home & Business 2019 haben, also nicht das Abo-Produkt. Eines Tages kam ein Update, die graphische Benutzeroberfläche, also sämtliche Schaltflächen veränderten sich und auf einmal waren die Microsoft 365-Funktionen drin (also XVERWEIS, XVERGLEIC, etc.).
Dann, zwei bis drei Tage später installierte sich wieder ein Update und alles war wieder weg – hab ich mir also nicht eingebildet. 😉 Diese Geschichte glaubt mir nicht jeder.
Was denkt sich Microsoft wohl dabei? Ist das wie dem Hund
die sprichwörtliche Wurst hinhalten (schau mal was du alles Tolles haben
könntest, wenn du das Abo-Produkt bestellst?) oder einfach ein Bug?
Ich wollte doch nur in Word die Rechtschreibprüfung starten. Ich klicke auf die Schaltfläche „Editor“ und lese:
Um dieses Dokument zu überprüfen, müssen die folgenden Ausnahmen in den Korrekturoptionen deaktiviert sein.
- Rechtschreibfehler nur in diesem Dokument ausblenden
- Grammatikfehler nur in diesem Dokument ausblenden
Möchten Sie s
Hatte da jemand keine Lust mehr? Oder einen Schluckauf? Oder fing die Kaffeepause oder der Feierabend an? Amüsant …
ich „spiele“ z.Zt. mit einer Tabelle (Mappe1), in der Excel partout nicht rechnen will. In der Kopfleiste habe ich die für die entsprechenden Spalten die Formeln und die Zellen-Formatierungen eingegeben. Der Wert in Spalte #30 ist die Basis für alle Berechnungen in der entsprechenden Zeile.
Kopiert habe ich nach
den Anweisungen der EXCEL-Hilfe und anderen Tipps aus dem Internet.
Für Ihre Hilfe wäre ich Ihnen sehr dankbar.
Hallo Herr M.,
beim Öffnen der Datei meldet Excel, dass ein Zirkelbezug vorliegt. Richtig: in der Statuszeile links unten steht es auch:
Denn: in der Zelle N30 steht die Formel
=EL30
in EL30 steht:
=(100%-EK30)/100%
in EK30 steht:
=(100%-EI30)/100%
in EI30 wird wieder Bezug genommen auf N30 mit:
=(EK30-EH30)/N30
Hier schließt sich der Kreis. Das müssen Sie lösen – sonst rechnet Excel nicht mehr!
Ich arbeite seit einer Weile in einer Excelmappe, die ich dann schließe. Öffne eine neue Datei, trage eine 1 ein und wundere mich über die Fehlermeldung:
Sie müssen eine Form auswählen.
Mir dämmert es. Die letzte Aktion in der letzten Datei war das Arbeiten mit Grafik – ich hatte das Werkzeug „Objekte auswählen“ aktiviert und nicht wieder zurückgeschaltet:
In verschiedenen Programmiersprachen gibt es verschiedene Konventionen für die Benennung der Dinge. Beispielsweise die Variablen oder Parameter. Zu den Namenskonventionen gehören beispielsweise die Reddick-Namenskonventionen, in denen eine String oder Long-Variable mit strNachname oder lngZeile benannt wird. Oder die ungarische Notation, in welcher diese variablen stNachname und lZeile genannt würden.
Wer in VBA programmiert stellt schnell fest, dass die Kombinationsfelder, Listenfelder, Register und Multiseiten eine andere Struktur haben als die anderen Steuerelemente auf den Dialogen (Userformen):
Nicht nur, dass sie Null-basiert sind (anders als die übrigen Elemente), die Parameter werden nicht mit „sprechenden“ Namen angezeigt (wie eigentlich üblich), sondern gemäß der dort verwendeten Namenskonvention:
Und so bin ich glatt reingefallen, weil der letzte Parameter nicht IIndex (also mit zwei „ii“) heißt, sondern klein-L-Index … Verwirrend!
ich bekomme es nicht hin, anbei mein Diagramm, es sollte allerdings so aussehen und ich bekomme es einfach nicht hin.
So sieht es bei mir aus:
Wären Sie so freundlich und würden mir helfen.
Danke
Mfg
Hallo Frau Dobner,
ich helfe doch gerne. Für Ihr Problemchen gibt es zwei Lösungen: entweder sie vertauschen Zeile und Spalte:
Allerdings „erkennt“ dann die Liste Spalte 1 als Daten. Diese muss man rausnehmen und den Datenreihen1 und Datenreihen2 als Legendeneinträge zuweisen (die Jahreszahlen)
Besser. Sie schreiben die Daten von oben nach unten:
Schon doof. Excel zeigt viele Dinge an, aber leider nicht die Liste der Pivottabellen. Mourad Louha schlägt ein kleines VBA-Skript vor, um die Liste aller Pivottabellen auszulesen:
Public Sub ListPivotTables() Dim c As Long Dim b As Worksheet Dim o As PivotTable Dim St As Worksheet On Error Resume Next Set b = ThisWorkbook.Worksheets.Add c = c + 1 b.Cells(c, 1).Value = "Name" b.Cells(c, 2).Value = "Quelle" b.Cells(c, 3).Value = "Aktualisierung" b.Cells(c, 4).Value = "Arbeitsblatt" b.Cells(c, 5).Value = "Bereich" b.Cells(c, 6).Value = "MDX" For Each St In ThisWorkbook.Worksheets For Each o In St.PivotTables c = c + 1 b.Cells(c, 1).Value = o.Name b.Cells(c, 2).Value = o.SourceData b.Cells(c, 3).Value = o.RefreshDate b.Cells(c, 4).Value = o.Name b.Cells(c, 5).Value = o.TableRange1.Address b.Cells(c, 6).Value = o.MDX Next Next End Sub
Ich habe es ein klein wenig überarbeitet. Das Ergebnis:
Oder man benennt die Datei mit der Endung ZIP um, entpackt das Archiv und wirft einen Blick in die beiden Ordner pivotCache und pivotTable. Darin verbergen sich sämtliche Informationen zu den Pivottabellen:
PowerPoint-Schulung. Wir erstellen eine Grafik. Dafür verwende ich die Firmenvorlage. Ich zeige dem Teilnehmer, wie man eine Form erstellt. Sie verschwindet. Ich bin erstaunt. Noch einmal: Form auswählen, Rechteck aufziehen – nichts ist sichtbar. Doch: beim Aufziehen sieht man die Form. Dann ist sie unsichtbar. Aber markiert. Also: Konturfarbe wählen. Die Form erscheint. Die Füllung ist transparent und wird ebenfalls sofort mit einer Farbe versehen. Wir beginnen Text einzutragen – die Form „schnurrt“ auf ein Minimum zusammen. Auch hier ist der „Bösewicht“ schnell gefunden: im Aufgabenbereich „Form formatieren“ wurde in den Textoptionen eingestellt, dass sich die „Größe der Form dem Text anpassen“ soll.
Dann klappt es: wir können eine Grafik erstellen und beschriften:
Jedoch: bei weiteren Formen vergesse ich die Grundeinstellungen, übertrage die Formatierung mit dem Pinsel „Format übertragen“ – padautz – die Form wird wieder klein, weil noch kein Text in der Form steht …
Als ich frage, wer diese Vorlage erstellt hat, erhalte ich keine Antwort. Ich bitte darum, sie noch einmal zu überarbeiten …
Ab und zu muss ich meine Dogmen und Glaubenssätze über Bord werfen. Beispielweise die Antwort auf meine Frage in Excel: „Wie findet man in einem geschützten Formular die nicht gesperrten Zellen?“
Bislang zeige ich meinen Teilnehmern und Teilnehmerinnen, dass man mit der [Tabulatortaste] von (offener) Zelle zu (offener) Zelle in einem geschützten Excelformular springen kann – wie im Internet, wie in einem Word-Formular oder einem PDF-Formular.
Weit gefehlt!
Sind mehrere Zellen nach unten (!) verbunden, „springt“ Excel von links nach rechts und von rechts nach links. Aber nicht weiter nach unten!
Eine der gestellten Fragen hat mich amüsiert – ich habe schon einmal in diesem Blog darüber gepostet.
In einer Exceltabelle ist die erste Zeile und die erste Spalte fixiert. Ich erkläre, wie man mit [Strg] + [Pfeil oben] nach „oben“ springen kann. Was ist oben?
Eine Teilnehmerin beschwert sich, dass sie nun nicht die erste Zeile der Datensätze angezeigt bekommt, sondern dass sich die Markierung in der Überschriftszeile befindet, die allerdings immer noch fixiert ist.
Als ich ihr erkläre, dass sie nun in einem zweiten Schritt mit [Pfeil unten] eine Zeile nach unten springen kann und so die ersten Zeilen der Tabelle sieht, ist sie zufrieden.
Eine Sache, die auch bei mir hier (trotz 6GB Arbeitsspeicher) immer wieder kommt, ist folgendes:
Nicht genügend Systemressourcen.
Debuggen kommt dann das hier.. Beim Ausführen einer ganz normalen SQL Anweisung, (zugegeben in einer Rekursion) bleibt er hier stecken..
Auf was muss ich denn da besonders achten?.. Oder könnte man das mit speziellen Funktion abfangen?
Viele Grüße nach München, Bernd
Hi Bernd,
Ich kenne solche Fehler … Nervig!
Versuch mal die Anweisungen rst!dblAnzWdh und
Nz(rst!dblWeightPart, 0) in zwei (Double-)Variablen zu schreiben und dann das
Produkt auszuführen (das Ergebnis wieder in eine dritte Variable), ebenso die
rst!ID.
Warum finde ich in der Liste der Seitengrößen kein Format A0? Ich möchte das Dokument als PDF speichern!
Bei meiner Kollegin habe ich das doch gesehen!
Die Antwort ist schnell gefunden: wird ein „normaler“ Drucker ausgewählt, der kein A0 drucken kann, wird dieser auch nicht vorgeschlagen. Man muss einen PDF-DRUCKER wählen, dann steht A0 zur Verfügung und jetzt kann man auch ein A0-PDF erstellen (speichern oder drucken):
Gestern habe ich eine ganz interessante Outlook-Frage erhalten. Wie siehst du das:
„Bspw
versende ich einen Termin im Outlook an 200 Teilnehmer und erhalte rund 50 Absagen,
soweit so gut. Sende ich jedoch ein Update dieses Termins, so erhalten auch
jene Teilnehmer welche sich abgemeldet haben, wieder meine Info. Ich erhalte
nun Antworten von Menschen welche sich ja schon eingangs bei mir abgemeldet
haben und mir erneut und entnervt mitteilen, dass sie nicht kommen können.
Ich weiss, dass in IBM Lotus Notes, all jene Teilnehmer welche sich einmal für
den Termin abgemeldet hatten, dann auch keine nervigen Updates mehr erhalten.“
Meine Antwort wäre: das geht nicht. Outlook geht davon aus,
dass eine Absage nicht eine Absage zu dem TERMIN ist, sondern zu dem ZEITPUNKT
an dem der Termin stattfindet. Deshalb wird ein Verschieben den Leuten
mitgeteilt als Möglichkeit JETZT teilzunehmen.
Wie siehst du das? Oder weißt du einen Schalter? Ich würde ihr raten die Leute, die abgesagt haben aus der Einladung rauszuwerfen … Dann könnte es aber sein … oder? Oder einfach einen kleinen Kommentartext in die Einladung zu schreiben.
René
Hallo guten Morgen,
Ja, René, es ist genau so, wie Du es sagst.
Man könnte künftig vielleicht eine Umfrage vorschalten, wer überhaupt Lust auf die Veranstaltung hat und dann nur die zum Termin einladen, die Ja gesagt haben. Da passt es dann auch bei Verschiebungen
Einfach nicht aufgepasst. Dabei weiß ich das eigentlich!
In einem großen, langen VBA-Programm werden Daten aus verschiedenen Dateien des gleichen Ordners, in dem sich die Datei mit dem Code befindet, zusammengefasst. Diese Dateien soll geöffnet werden, Inhalt herauskopiert und danach wieder geschlossen werden.
Ich überprüfe im ersten Schritt, ob es sich um eine XLSM oder XLSB-Datei handelt, denn nur dort liegen die gesuchten Daten:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" Then
Klappt wunderbar.
Fast.
Denn auch die Datei, in der sich der Code befindet, wird bearbeitet, also geschlossen. Das ist nicht Sinn der Sache. Also schließe ich aus:
If Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb" And strDatei <> ThisWorkbook.Name Then
und wundere mich, warum es nicht funktioniert. Die aktuelle Datei wird immer noch geschlossen. Seltsam.
Ich prüfe:
der Inhalt von strDatei entspricht ThisWorkbook.Name. Warum fließt diese Abfrage in die Bedingung, warum wird sie ausgewertet, oder genauer: warum liefert die IF-Verzweigung den Wert Wahr? Es dauert ein paar Sekunden, dann dämmert es mir:
AND ist stärker als OR. Die IF-Verzeigung überprüft, ob die Datei strDatei die Endung XLSB hat UND die gleiche Datei ist. Nein – ich laufende Datei habe die Endung XLSM. Oder: hast du die Endung XLSM? ja – DAS ist korrekt. Also wird die Bedingung ausgewertet. *ggrrrrr* Also noch einmal – diesmal mit Klammer:
If (Right(strDatei, 4) = "xlsm" Or Right(strDatei, 4) = "xlsb") And strDatei <> ThisWorkbook.Name Then
Ein Bild. Ein sehr großes und scharfes Bild. Eine Architekturzeichnung:
Ich füge es in eine Excel-Arbeitsmappe, speichere sie, öffne sie und sehe:
Unscharf!
Okay – Excel stellt die Option „Bilder in Datei nicht komprimieren“ zur Verfügung. Hätte ich auswählen sollen:
Allerdings: in Visio? Was mache ich in Visio, wo eigentlich Bilder hingehören und herkommen? Dort suche eine solche Einstellung vergebens. Dort wird ab einer bestimmten Dateigröße komprimiert:
Okay – zugegeben – dort kann man die VDSX-Datei als ZIP umbenennen, extrahieren und dann im Ordner „media“ das verkleinerte Bild durch das Original ersetzen:
Witzig. Vorlauter Formeln und Assistenten übersieht man manchmal die Wirklichkeit.
Wir schauen die Zielwertsuche an. Mit der Funktion RMZ (regelmäßige Zahlungen) wird die Annuität berechnet.
Nun soll „zurückgerechnet“ werden. Ich möchte wissen wie hoch der Zinssatz bei einer Rückzahlung von 220 Euro ist. Also Zielwertsuche (sie soll ja auch gezeigt werden:
Sie rechnet und liefert:
-4,92% !?! Minus !?!
Es dauert eine Weile, bis wir verstehen: Das Ergebnis ist korrekt. Man müsste mit negativen Zinsen rechnen, um auf diesen Betrag zu kommen … Doch nicht falsch und auch nicht so unrealistisch.
Gestern im Excelstammtisch. Frank Arendt-Theilen macht darauf aufmerksam, das PowerQuery einen Parameter bei der Funktionen RUNDEN (Number.Round) anbietet:
IntelliSense zeigt allerdings in M, dass diese Funktion einen weiteren Parameter besitzt: roundingMode mit fünf Konstanten:
Die Standardeinstellung von PowerQuery ist RoundingMode.ToEven. Damit unterscheidet sich diese Rundenfunktion von RUNDEN in Excel. Dort wird RoundingMode.AwayFromZero verwendet. Deshalb unterscheiden sich diese beiden Runden-Funktionen: PQ rundet wie VBA; Excel rundet anders …
Vielen Dank, Frank für diesen sehr, sehr wertvollen Hinweis!
Excelschulung. Ich erkläre und zeige (intelligente, dynamische, formatierte, strukturierte) Tabellen:
Ein Teilnehmer behauptet, dass diese Tabellen seine Überschriften löschen würden. Ich bin irritiert. Ich habe eine Weile gebraucht, um zu verstehen, dass die weiße Schriftfarbe, die Excel als Standard einsetzt, nicht sehr clever ist bei einer gelben Hintergrundfarbe …
Natürlich ist die Überschrift noch vorhanden. Nur eben – sehr schlecht lesbar!
Outlookschulung. Es kam eine Frage zur Lesebestätigung. Ich sende mehrere Mails mit Lesebestätigung an verschiedenen Personen.
Einige haben einen grünen Haken, andere nicht !?! Diejenigen mit einem grünen Haken haben beim Öffnen eine Gruppe „Anzeigen“:
Wenn ich auf den „Status“ klicke, sehe ich die Liste der
Mails – ohne Antwort – obwohl ich die Lesebestätigung bejaht habe.
Bei den anderen fehlt diese Gruppe – obwohl eine Lesebestätigung dranhängt.
Auch hier habe ich meine Kollegin gefragt – sie wusste auch keine Antwort. Auch hier muss ich mit einem Schulterzucken verbleiben. Sorry … Wenn ich noch etwas erfahre, melde ich mich wieder. Kennst du die Antwort auf diese Frage / die Lösung für dieses Problem?
Ich hätte schwören können, dass es vorhanden ist. Eine Teilnehmerin in der letzten Schulung wollte das Entspricht-Zeichen ( ≙ ) haben. Das Gleichheitszeichen mit dem Dach darüber. Ich hätte es in der Liste der Zeichen vermutet, die die Schriftart Symbol zur Verfügung stellt. Oder im „normalen“ Zeichensatz.
Ich wurde eines Besseren belehrt. Man findet es beispielsweise in der Arial Unicode:
Ich schaue auf dem Firmenrechner nach. Bei dieser Firma ist die Arial Unicode nicht installiert. Was tun?
Nun – wir finden das Zeichen auf einer Seite im Internet und kopieren es von dort heraus. Und hinterlegen es in der AutoKorrektur. Die Teilnehmerin war glücklich. Und ich sehr erstaunt, dass dieses (wie ich dachte) wichtige Zeichen nicht vorhanden ist. Ich werde mal an die IT schreiben und bitten die Unicode-Schriften nachzuinstallieren. In Word könnte man es in einem Feld nachbauen, aber in Excel, PowerPoint und Outlook?
