In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:
Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:
Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:
Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:
Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?
Ich weiß es nicht?
Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?
Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?
Folgender Beitrag hat mich vor einigen Tagen erreicht, den ich gerne weitergeben möchte:
„
Guten Tag, Excel mag zwar Vieles können, aber MS kann es nicht erklären. Nach einigen Stunden Probierens bleibt folgendes Problem: Ich habe ein Liniendiagramm mit Temperaturwerten für die Jahre 10800 bis 1600 vuZ, also -10800 bis -1600.
Fehler in Excel: man muss ihm erst über eine Box „sagen“, dass er die negativen (mit Minuszeichen versehenen) Werte aufsteigend anzeigt. Leider schickt aber Excel dann entgegen normaler Verfahren auch unerwünscht die vertikale Beschriftung der Temperaturdaten nach rechts, ohne dass man das irgendwo separat einstellen kann. Unmöglich! Jedenfalls steht eine Anleitung, wenn vorhanden, nicht da wo sie hingehört, nämlich Achsenbeschriftung. 2.1. Ungelöst: Ich möchte, dass nur die Tausender (und vielleicht die Hunderter, je nach späterer Größe) angezeigt werden. Excel erlaubt aber, soweit ich sehe, nur die Abstände zu bestimmen und zeigt nun entgegen dem Userwunsch die Daten ab 1600 rückwärts in Tausendern an. Unmöglich. Man wird nach tausend Dingen gefragt. Nur, wie bringe ich Excel bei, von -10’000 an die Tausender (wertemäßig) aufsteigen zu lassen? (Merke: -1’000 ist ein höherer Wert als -2’000 und sollte daher rechts erscheinen) 2.2. Senkrechte Achsen erscheinen – egal mit welcher Einstellung, nur bei 1600, 5600 und 9600, also in 4000 Jahren Abstand. Hab ich nirgends eingestellt. Das Programm ist zum Verzweifeln, im Gegensatz zu meinem wunderbaren Atari-Spreadsheet-Programm BSTAT seligen Angedenkens. 2.3. Von statistischen Glättungsverfahren will ich gar nicht erst anfangen. Mit genervten Grüßen und Bewunderung für die Hilfsbereitschaft, Hans J. Holm“
Schöne Frage in der letzten PowerQuery-Schulung: wo befindet sich das (auch Excel bekannte) Symbol, das erlaubt nicht nur eine Spalte zu sortieren, sondern nach mehreren:
Die Antwort: ein SYMBOL hierfür gibt es nicht – man muss die Spalten in der gewünschten Sortierreihenfolge anklicken und sortieren, beispielsweise zuerst Ort; innerhalb eines Ortes (Aachen) nach der PLZ, innerhalb einer PLZ (beispielsweise 52062) nach der Straße, …
PowerQuery quittiert die Mehrfachsortierung mit dem Befehl
Wie kann ich denn in einer Wordtabelle zwei Uhrzeiten berechnen? Beispiel: A2 und B2 sind als Feld mit Datum HH:mm definiert. Bei C2 kommt aber leider nie 01:30 als Ergebnis raus. A2 = 12:00 B2 = 13:30 C2 = Hier soll das Ergebnis stehen von der Rechnung B2 – A2
Hallo Herr D.,
Sie haben recht, Herr Gauger, sowohl bei Berechnungen in Feldfunktionen als auch in Tabellen kann Word nicht mit Uhrzeiten rechnen. Das Ergebnis ist ein krudes Gemisch aus Stunden und Minuten, also unbrauchbar. Schade!
Hallo Rene.
Mir ist folgende Methode bekannt um in Word mit Uhrzeiten zu rechnen.
Dazu muss in den beiden Textformatfeldern welche die Uhrzeiten enthalten bei „Optionen für Textformatfelder“ eine Textmarke eingetragen werden.
Dann wird in die Zelle in der das Ergebnis der Uhrzeitberechnung angezeigt werden soll, folgendes FELD eingetragen.
Wenn in den Textformularfeldern die Box „beim Verlassen berechnen“ angekreuzt wird, kommt es zu einer Aktualisierung auch des REF-Feldes. Scheinbar werden dann alle Felder im aktiven Dokument (außer denen in den Kopf- oder Fußzeilen) aktualisiert.
Salü
Ernst
Hallo Rene
Die Darstellung als HH:mm hat mir keine Ruhe gelassen., so dass ich doch noch eine (wohl sehr umständliche) Methode gefunden habe.
Die zusammengesetzte Feldfunktion zur Berechnung der Zeitdifferenz ist sehr umständlich. Allerdings ist die Funktion immer die gleiche, so dass sie als Schnellbaustein abgespeichert werden kann. Einzige die angesprochenen Textmarken müssen jeweils geändert werden.
warum findet Excel Costa Rica nicht? Es gibt Costa Rica!
Genauer: ich habe mit einer Suchformel
=INDEX($F$2:$F$114;VERGLEICH(A9;$G$2:$G$114;0))
einen Wert gesucht. Alle Werte werden gefunden – nur nicht Costa Rica:
Zuerst vermutete ich ein Leerzeichen hinter einer der beiden Wörter. Die Schreibweise ist korrekt. Dann vermute ich das Leerzeichen als Übeltäter. Und so ist es auch: die Funktion
=CODE(TEIL(G23;6;1))
liefert den Wert 160 (geschütztes Leerzeichen) und nicht 32 (Leerzeichen), wie erwartet:
Auf mein Nachfragen erfahre ich: „… ich habe die Liste doch aus wikipedia kopiert. Und ich habe sie explizit als Wert eingefügt – OHNE Formatierung!“ Das genügt leider nicht … wikipedia verwendet an einigen Stellen geschützte Leerzeichen und bedingte Trennstriche. Das macht das Weiterverrbeiten solcher Listen in Excel manchmal mühsam. Böses Excel …
ich hoffe das du bei diesen sommerlichen Temperaturen einen guten und entspannten Tag hast.
Zurzeit beschäftige ich mich mit der Ribbon-Programmierung in MS-Office. In Word gibt es ein Button Schließen/Alles Schließen, mit der MsoID = „FileCloseOrCloseAll“. Klickt man diesen Button an, wird das aktuelle Dokument geschlossen. Wird beim Anklicken die Umschalttaste gedrückt, werden alle offenen Dokumente geschlossen.
Wie bekomme ich eine solle Funktionalität bei einem benutzerdefinierten Button hin. Ich möchte zwei unterschiedliche Makros aufrufen, je nachdem ob beim Anklicken die Feststelltaste gedrückt oder nicht gedrückt ist.
Hast du eine Idee?
Liebe Grüße und
Salü
Ernst
Hallo Ernst,
ich glaube nicht, dass das geht. Die Steuerellemente haben folgende Ereignisse:
Ereignis
Beispiel
onAction (button)
Sub Prozedur(ByRef Control As IRibbonControl)
onAction (checkBox, toggleButton)
Sub Prozedur(ByRef Control As IRibbonControl, ByRef Pressed As Boolean)
onAction (dropDown, galley)
Sub Prozedur(ByRef Control As IRibbonControl, ByRef SelectedID As String, ByRef SelectedIndex As Integer)
onChange (editBox, comboBox)
Sub Prozedur(ByRef Control As IRibbonControl, ByRef Text As String)
Das heißt: für die Buttons gibt es beim Aufruf einer Prozedur kein Parameter, der eine weitere Taste abfangen kann.
Auch bei den Methoden:
Methode
Beschreibung
ExecuteMso
Führt das vom idMso-Parameter angegebene Steuerelement aus.
GetEnabledMso
Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement aktiviert ist.
GetImageMso
Gibt ein IPictureDisp-Objekt des vom idMso-Parameter angegebenen Steuerelementbilds zurück, wobei die Abmessung von Height und Width angegeben wird.
GetLabelMso
Gibt die Beschriftung des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück.
GetPressedMso
Gibt einen Wert zurück, der angibt, ob das vom idMso-Parameter angegebene Umschaltflächen-Steuerelement gedrückt wird.
GetScreentipMso
Gibt die QuickInfo des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück.
GetSupertipMso
Gibt eine MultiInfo zum vom idMso-Parameter angegebenen Steuerelement als Wert vom Typ String zurück.
GetVisibleMso
Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement sichtbar ist.
Kann man mit GetPressedMso nur bei Toggle-Buttons prüfen, ober ein- oder ausgeschaltet wurde, aber nicht wie gedrückt wurde.
Ich kann diese Frage mal nächste Woche auf meinem Blog veröffentlichen – vielleicht weiß jemand eine Antwort …
Liebe Grüße
Rene
Hallo Rene.
Nach deinen Ausführungen bin ich auf die Idee gekommen folgendes in WORD auszuprobieren.
Bei zwei geöffneten Dokumenten habe ich im Direktbereich des VBA-Editors den Befehl
CommandBars.ExecuteMso(„FileCloseOrCloseAll“)
einzugeben und danach nur die Enter-Taste gedrückt.
Es wird die Schließen-Routine aufgerufen.
Wird allerdings die Shift- und die Enter-Taste gleichzeitig gedrückt, wird die AllesSchließen-Routine aufgerufen.
Daraufhin habe ich folgendes versucht:
Ich habe in ein Word-Dokument mit Hilfe des Office RibbonX Editor diese Sequenz eingefügt.
und im VBA-Bereich des gleichen Dokumentes folgendes.
Option Explicit
'API zum feststellen des Keyboard-Status deklarieren.
#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
Sub OnActionButton(control As IRibbonControl)
Select Case control.ID
Case "SaveAsDocxOrPdf": SpeicherAlsDocxOderPdf 'Neuer Umbruch
Case Else
MsgBox "Fehler in OnAction: '" & control.ID & "' nicht erkannt"
End Select
End Sub
Sub SpeicherAlsDocxOderPdf()
If Abs(GetKeyState(&H10) < 0) Then 'Abfrage ob Shifttaste gedrückt ist
CommandBars.ExecuteMso ("FileSaveAsPdfOrXps")
Else
CommandBars.ExecuteMso ("FileSaveAsWordDocx")
End If
End Sub
Und es klappt. Wird das benutzerdefinierte Icon „Als DOCX oder PDF speichern“ angeklickt, erscheint das Formular „Speichern als Dokument ohne Makros“. Wird während des Anklicken des Icon die Shift-Taste gedrückt, erscheint das Formular „Als PDF oder XPS veröffentlichen“.
Salü
Ernst
Das ist sehr, sehr clever! DARAUF wäre ich nie gekommen.
Ich habe eine Datei, in der sich verschiedene Werte befinden. Diese Datei wird mit Kennwortschutz gespeichert:
Einige Werte dieser Datei werden in eine andere Datei verknüpft:
Sind beide Dateien geschlossen, wird die Zieldatei geöffnet, so fragt Excel danach, ob die Daten aktualisiert werden sollen. Danach erfolgt die Frage nach dem Kennwort der kennwortgeschützten, verknüpften Datei.
Wurde allerdings die verknüpfte Datei an andere Stelle kopiert, dort bearbeitet und wieder zurückgespielt, erkennt Excel dies – aufgrund des Kennwortschutzes – nicht. Eine Neuberechnung zeigt nicht die aktuellen Daten. Man muss die Originaldatei öffnen, um eine Aktualisierung der Daten zu erreichen …
herzlichen Dank für deine Bemühungen! Das bringt mich ein großes Stück weiter. Leider funktioniert aber irgendeine Kleinigkeit noch nicht… Ich hab viel probiert, komm aber nicht auf den Fehler.
Expression.Error: Der Wert "2022" kann nicht in den Typ "Text" konvertiert werden.
Die Fehlermeldung verstehe ich nicht. Das Jahr, nach welchem gefiltert werden soll.
Hallo Nadine,
ist die Zahl 2023 in der Zelle als Text formatiert?
Und: was macht „geänderter Typ“? – in Text oder Zahl konvertieren?
Liebe Grüße
Rene
Hallo Rene,
genau, ich habe dann extra die 2023 in Text formatiert. Ursprünglich hatte ich es als Zahl, da kam allerdings auch diese Fehlermeldung, weshalb ich die 2023 dann in Text formatiert habe.
Hier die Schritte, welche ich in der Jahrestabelle ausgeführt habe:
Dort wo dann die Formel eingefügt wird, also dort, wo nacher nach diesem Jahr gesucht werden soll, sieht die Formatierung so aus:
Hallo Nadine,
Folgende Ursache: Ich vermute in deiner Zelle stehe die Jahreszahl als ZAHL – in meiner ersten Städtedatei hatte ich sie als Text formatiert.
Damit du auch einen Text erhältst, muss dein zweiter Schritt
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SOP"]}[Content],{{"Column1", type text}}){0}[Column1]
verwendet werden
In deinem Code
= Table.SelectRows(Quelle, each (Record.Field(_ , Excel.CurrentWorkbook(){[Name="SOP"]}[Content]{0}[Column1]) <> null))
Zusammengefasst: der in Excel eingetragene Wert in eine Zahl. Die Spaltenüberschrift jedoch ein Text. Irgendwann muss die Zahl in einen Text konvertiert werden!
Ich habe schon eine Weile überlegen müssen. Folgende Frage erreichte mich:
„Allerdings möchte ich nun in einer Spalte, die nicht fest definiert ist, filtern. Ist dies möglich?
Ziel: Ich möchte das es mir nur die Zeilen anzeigt, die in einer bestimmten Jahres-Spalte einen Wert haben.
Der Anwender des Tools sollte die Möglichkeit haben, ein Jahr einzugeben in einem bestimmten Feld.
Hier wählt der Anwender das Jahr aus. Daraufhin sollte in Power Query in der Spalte, mit der Bezeichnung 2026, nach Werten <> null gefilter werden. Im Screenshot würde dann nur noch die erste Zeile erscheinen, da in der Spalte 2026 nur in der ersten Zeile ein Wert enthalten ist. Ich hab schon viel rumprobiert und bekomm es nicht hin.“
Ich ziehe das Ergebnis des Filters (versehen mit dem Namen „Jahr“) nach PowerQuery und benennen die Abfrage „Jahr“. Sie sieht folgendermaßen aus:
Eine schöne Frage in der letzten PowerQuery-Schulung.
Eine Tabelle soll verändert werden. Im linken Bereich befinden sich Informationen (nennen wir sie „Metadaten“), im rechten Bereich in mehreren Spalten weitere Informationen zu diesen Metadaten.
Jede dieser Gruppen, bestehend aus jeweils drei Spalten, soll neben die anderen Daten geschrieben werden, so dass die Metainformation so oft auftaucht, wie Gruppen vorhanden sind. Dabei können beliebig vieler dieser Gruppen auftauchen.
Das Ziel:
Mein erster Gedanke:
Ich fasse mit dem Befehl „Spalten zusammenführen“ jeweils die einzelnen Spalten einer Gruppe zusammen:
Anschließend kann man diese Spalten entpivotieren
und danach am Trennzeichen (hier: „|“) teilen.
Aber: das Verfahren ist umständlich, weil (hier:) bei 17 Gruppen 17 Mal entpivotiert werden muss. Da die Anzahl der Gruppen variabel ist, ging ich auf die Suche, ob man das mit geschickten M-Befehlen (einer Schleife!) abkürzen und dynamisch halten kann.
Da fiel mit der Artikel von Hildegard Hügemann in die Finger:
Anschließend werden die Überschriften benötigt in der Form A – B – C. Leider stehen sie hier als A1 – B1 – C1 – A2 – B2 – C2 – A3 – … Die Zahlen müssen entfernt werden. Man kann sie mit dem Assistenten „Spalte teilen“ und er Option „Nach Wechsel von Nicht-Ziffer zu Ziffer“ herauslösen:
DIESE (spätere Überschriftsspalte) wird nun pivotiert, wobei die Werte (letzte Spalte) natürlich nicht aggregiert werden (verbirgt sich in den „Erweiterten Optionen“):
Der Rest ist „Kosmetik“: Datentypen festlegen, Spalten löschen, leere Spalten entfernen (wegfiltern), Spalten umbenennen, …
Klasse!
Ein großes Dankeschön an Hildegard Hügemann für die Lösung – hier habe ich glatt „in die falsche Richtung gedacht“.
Kennst du das? Man möchte in PowerQuery in mehreren Spalten den Datentyp ändern. Ein Klick auf das kleine Symbol und alle Markierungen werden aufgehoben:
Abhilfe schafft der Befehl Transformieren / Datentyp. Lästig:
Die Lösung zeigt Frank Arendt-Theilen:
Man muss die [Strg]-Taste halten und zwei Mal auf das kleine Symbol klicken. Dann klappt es:
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Details:
Key=[Record]
Table=[Table]
nicht weiter.“
Ich schaue mir das Ganze an. Was haben wir gemacht?
In einem Tabellenblatt werden drei Dateien aufgelistet und der Pfad, in dem sich diese Dateien befinden. Diese vier Zellen haben Namen – hier: Schweinchen1, Schweinchen2, Schweinchen3 und Pfad:
Über Daten / Daten abrufen / aus Datei greife ich auf eine der drei Dateien zu:
Die Datentypen werden nicht automatisch erkannt; übrig bleiben drei Schritte; das Ergebnis wird nach Excel zuzrückgegeben:
Eine der drei Zellen mit Namen wird über Daten / Daten abrufen / Aus Tabelle/Bereich in PowerQuery verwendet. Nach einem Drilldown erhält man den Inhalt der Zelle:
Dies wird für die übrigen Zellen wiederholt. Fügt man nun diese Variablen in den Befehl Excel.Workbook ein, so ist eine Firewall-Meldung die Folge:
Diese kann man umgehen, indem man den Code (Zugriff auf den Inhalt einer Zelle mit Namen) in eine Zeile schreibt:
Dies wird auch für die anderen beiden Dateien durchgeführt, die anschließend in Excel geladen werden:
Die Hilfsabfragen Schweinchen1, Schweinchen2, … kann man getrost löschen.
So habe ich die Vorlage erstellt. Und nun kommt die Fehlermeldung:
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Ich begebe mich auf die Suche. Der Fehler taucht beim Zugriff auf das Tabellenblatt „Tabelle1“ auf. Nachgeschaut: bei einer anderen Datei heißt das Tabellenblatt „Sheet1“:
Also muss ich auch noch den „harten“ Namen entfernen. Ich mache es so:
Word-Schulung. Eine Teilnehmerin möchte ein „x“ mit einer Tilde dargestellt haben – das Symbol für Median.
Wir überlegen zusammen. Man könnte es mit einer Feldfunktion erzeugen:
Das Feld
{EQ \O(x;~)}
schiebt die beiden Zeichen „x“ und „~“ ineinander. Damit die Tilde über dem Buchstaben steht, muss man sie höher formatieren – man findet dieses Attribut in den Zeichenformatierungen:
Das Ergebnis könnte man in der Autokorrektur oder als Schnellbaustein abspeichern.
Natürlich könnte man auch den Formeleditor heranziehen.
Schneller funktioniert es sicherlich, wenn man das Zeichen von einer Internetseite kopiert:
Schließlich werden wir sogar in Word fündig: es gibt bereits dieses Zeichen in der Autokorrektur. Man muss
x\tilde
eintragen.
Achtung: dies funktioniert jedoch nur dann, wenn die Option „Mathematische Autokorrekturregeln in nicht mathematischen Bereichen verwenden“ aktiviert ist.
Nachtrag:
Hallo Rene,
Mir ist noch folgende Methode zur Erzeugung des Median-Symbol in WORD bekannt. Zuerst wird das x-Zeichen eingegeben (es kann auch jedes andere Zeichen sein) und danach wird bei gedrückter ALT-Taste die Zahlenfolge 771 auf der Zifferntastatur eingegeben.
Salü Ernst
Danke für den tollen Tipp, Ernst!
Und noch einer von Ernst:
Hallo Rene,
da diese Methode auf meinem Laptop nicht möglich ist, weil dieser keine Zifferntastatur aufweist, habe ich weiter gesucht und noch eine andere Methode gefunden.
Dabei können zwei verschiedene Reihenfolgen der Zeichenerstellung angewendet werden.
Nach Eingabe des x-Zeichen schreibt man den Wert 303 (was der Hex-Wert von 771 ist) markiert diesen Wert und drückt ALT-c.
oder
303 wird zuerst eingegeben, danach wird ALT-c, danach die Cursortaste links und dann das x-Zeichen gedrückt.
Statt dem x können alle möglichen anderen Zeichen (Buchstaben, Zahlen, Satzzeichen wie ?) und statt dem Wert 303 können auch andere Zahlenwerte verwendet werde. 305 ergibt z.B. einen großen Strich über dem Buchstaben.
Diese Methode hat den Vorteil, dass die Zifferntastatur nicht benötigt wird.
Salü
Ernst
*uff* noch eine Lösung:
Hallo Rene,
und der Witz ist, dass es eine weitere noch einfachere Methode gibt, die auch auf einen Laptop funktioniert.
In WORD kann jeder beliebige Unicode aufgerufen werden, indem u+ dann der Hex-Wert des Unicodezeichens eingegeben und danach ALT-c gedrückt wird. Dies gilt natürlich auch für die Zeichen der Unicode-Block „Kombinierende diakritische Zeichen“.
Somit kann z.B. das Medianzeichen durch die Kombination xu+303 und drücken von ALT-c erzeugt werden.
Der Unicode-Block der „Kombinierende diakritische Zeichen“ umfasst den Bereich Hex300 (Dez771) bis Hex36F (Dez879) und somit 112 Zeichen die mit allen möglichen Buchstaben kombiniert werden können.
habe ich darauf hingewiesen, dass in Word Ref-Felder, wenn sie sich in der Kopf- oder Fußzeile befinden, nicht automatisch aktualisiert werden. Und gestern kam erneut die Frage:
„Ich möchte in ein Worddokument Informationen eintragen und diese in der Kopfzeile anzeigen.“
Ich erkläre, dass man Formularfelder verwenden könnte und der Feldfunktion Ref darauf zugreifen könnte:
Allerdings werden sie nicht automatisch aktualisiert. Erst beim Drucken oder wenn man alles markiert und mit [F9] aktualisiert, sieht man in der Kopfzeile den Text, der ins Dokument eingegeben wurde.
Ebenso ungeschickt sind Textmarken, die mit der gleichen Feldfunktion Ref aufgerufen werden können. Auch hier wird nicht sofort aktualisiert, und es besteht die Gefahr, dass die Textmarke aus Versehen gelöscht wird:
Mir fällt eine dritte Lösung ein: Die Funktion Styleref wiederholt den Text einer Formatvorlage. Also könnte man eine Tabelle anlegen, denn dort wird eine Zelle als Absatz behandelt. Ihr wird eine (Absatz-)Formatvorlage zugewiesen (hier: Thema und ID).
Auf diese wird mit StyleRef verwiesen. Erstaunlicherweise muss ich den Bildschirm nach oben und unten scrollen, damit aktualisiert wird, aber dennoch: so sieht man in der Kopfzeile (oder Fußzeile) schnell den Text, der im Dokument eingegeben wurde:
Die Aufgabe lautet: ich möchte die Anzahl der Zeilen wissen, in denen in mindestens einer der vier Spalten etwas steht. Oder Anzahl der Spalten, bei denen Wert Spalte A <> „“ oder Wert Spalte B <> „“ oder Wert Spalte C <> „“ oder Wert Spalte D <> „“.
Ich probiere. Mit & werden die vier Zellen verkettet:
Ich habe eine Excel Arbeitsmappe, mit welcher Offerten gerechnet werden.
Der Artikelstamm liegt in einer SharePoint Liste und die besagte Arbeitsmappe verfügt über mehrere Tabellenblätter bei welchem jeweils eine gefilterte Abfrage auf den zentralen Artikelstamm gemacht wird.
Die auf diese Weise gruppierten bzw. gefilterten Artikel werden mit einer Anzahl versehen, sodass Preise ermittelt werden können.
Das klappt soweit auch alles doch am Schluss möchte ich das Ganze so ausdrucken können, dass keine fixen Seitenumbrüche pro Tabellenblatt gemacht werden.
Die Kopf und Fusszeile sollten gemeinsam benutzt werden und pro Tabellenblatt müssten drei Wiederholungszeilen bestehen, dann jeweils n Zeilen mit Artikeln, aber eben nicht immer eine neue Seite. Meinst Du ist das machbar oder habe ich da wiederum ein Problem?
Hallo,
kurz und knapp,
die Antwort auf diese Frage findest du in den Tiefen meines Blogs: es geht nicht. Die Frage habe ich vor einigen Jahren erhalten, weil man in Lotus 1-2-3 mehrere Tabellen auf einem Blatt ausdrucken konnte. In Excel ist so eine fortlaufende Tabelle nicht möglich.
ich möchte Sie nochmals belästigen, auch auf die Gefahr hin, dass ich wieder zu doof bin, um es selbst zu finden!
Ich möchte auf dem Excel-Arbeitsblatt in der Kopfzeile das Datum der Erstellung oder zuletzt geändert / zuletzt gedruckt einfügen, wie ich es aus Word kenne. (SAVEDATE / PRINTDATE) Ich habe in den Funktionen nur das „Aktuelle“ Datum gefunden. Selber etwas konstruieren kann ich als bloße Userin leider nicht. Können Sie helfen? Vielen Dank schon mal!
Hallo Frau H.,
das geht leider nicht.
In der Kopfzeile fehlen einige Dinge:
* Verknüpfung mit Inhalt einer Zelle
* Formeln
* weitere Feldfunktionen
…
Mit einem Makro kann man das lösen – wird aber auch umständlich: „woher hole ich die Information „zuletzt gedruckt“.
Sicherlich keine zufrieden stellende Antwort: ich würde das Datum per Hand in die Kopfzeile schreiben. Oder vielleicht über den Blattnamen, den man ja in der Kopfzeile anzeigen lassen kann.
In meinen Outlook-Schulungen sage ich den Teilnehmerinnen und Teilnehmern immer, dass sie sich Mail, die sie geschrieben haben, noch einmal durchlesen sollen.
Warum? Ich helfe gerne. Aber es ist mühsam, den Inhalt einer solchen Mail herauszufinden. Bin ich überhaupt gemeint? Wer ist Sabine? Worum geht es? Folge Mail erreichte mich vor einigen Tagen:
„Lieber Herr Brockelmann – ich hoffe es geht Ihnen gut?
Ich hätte eine Frage.
Ich habe erst Sabine interviewt.
Dann ergab sich dass Jennys Prozess zeitlich davor anfängt……und dasbekam ich dann nicht hin in der Datei.
Ich interviewe nur 1 Prozess als Muster – damit meine Chefin mal sieht was Prozessmanagement kann (allerdings hier nicht reine Lehre BPMN 2.0.).
Jetzt konnte ich den Vorlagen Prozess Sabine nicht nach rechts schieben (ich habe es nicht hinbekommen) und wissen Sie (sicher) wie man den Rahmen (das Feld nach ganz links wieder zieht) die Schablone? Das sich alles unendlich nach rechts erweitert ist gut.
Ich hoffe ich war verständlich….
Herzlichen Gruß aus xxx aus dem Home Office heute“
Ein Teilnehmer zeigt mir seine Tabelle. Sie hat sehr viele Spalten, in denen Informationszahlen stehen:
Seine Frage:
„Wenn ich eine Pivottabelle estelle (in der ich die vorkommenden Werte zähle), kann ich nicht die einzelnen Spalten in die Werte ziehen. Was muss ich tun?
Die Aufgabe: Die Orte werden in den Zeilen gruppiert, in die Statusangaben in den Spalten. Man kann nun eine Person in die Zeilen ziehen und sich in den Werten die Anzahl der Einträge anzeigen lassen:
Jedoch: sobald eine zweite Person hinzukommt, arbeitet die Pivottabelle nicht so wie gewünscht:
Da das Ziel war aus einer Pivottabelle ein Diagramm zu erzeugen, scheiden mehrere Pivottabellen aus.
Ich überlege: die Form der Tabelle ist unglücklich gewählt. Man darf die Informationen nicht in Zeilen und Spalten abtragen. Man muss die Tabelle entpivotieren. Hier bietet sich PowerQuery an:
Gesagt, getan – die Liste wird erstellt. Eine Pivottabelle erzeugt:
Genau SO wollte er es haben! Als Basis für ein Diagramm. Er war begeistert.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Die Möglichkeit, den Datentyp über ein Gebietsschema auszuwählen, ist hinlänglich bekannt:
(Randbemerkung: mit der Taste [F] gelangt man am schnellsten zu Englisch / USA)
Benötigt man das andere Gebietsschema mehrmals, kann man dies in den Optionen in den Regionalen Einstellungen der Arbeitsmappe festlegen:
Hinweis: Nicht verwechseln mit den Regionalen Einstellungen, welche die Sprache der Namen der Variablen (beispielsweise Gefilterte Zeilen, Geänderter Typ, Sortierte Zeilen, …) festlegt:
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Kennt ihr das Problem? Man erstellt eine Reihe von Abfragen:
schließt den Editor, aber aus Versehen lädt man die Abfragen nicht als Verbindung, sondern als Tabelle – padautz – schon hat man 20 (ungewünschte) Tabellen.
Abhilfe schafft in den Optionen die „Standardeinstellung zum Laden von Abfragen“. Wählt man dort die Option „benutzerdefinierte Standardeinstellung“ und deaktiviert alle Kontrollkästchen, so werden die Abfragen nicht als Tabelle in Excel eingetragen.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Beim Importieren von Daten „erkennt“ PoweryQuery den Datentyp der Spalten. Das kann nervig oder lästig sein oder auch zu Fehlern führen:
Diese Option kann man deaktivieren:
„Spaltentypen und -überschriften für unstrukturierte Tabellen niemals erkennen.“
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Wir haben drei Möglichkeiten gefunden, um Werte, die sich in Excel befinden, als Parameter in PowerQuery zu verwenden:
Die Werte stehen in einer intelligenten Tabelle:
2. Die Zellen, in denen sich die Werte befinden, werden mit einem Namen versehen:
3. Die Werte sind das Ergebnis von Berechnungen von Array-Funktionen, beispielsweise FILTER oder SEQUENZ:
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Lagert man in Excel einen Datenpfad in eine Zelle, kann man den Wert in PowerQuery verwenden, wenn man einen Drilldown erzeugt hat:
Verwendet man nun diesen Wert, also diese Variable, ist eine Firewall-Meldung die Folge:
Natürlich kann man in den Optionen diese Firewall-Einstellungen ausschalten. Oder man kann das Problem umgehen, indem man den Verweis auf die Excelzelle nicht in einer getrennten Abfrage belässt, sondern in die Formel einbaut, beispielsweise so:
Die Probleme mit dem Titel und dem Vorwort habe ich jetzt erledigt.
Ich hänge aber immer noch am Druckauftrag.
Anbei mal die aktuellen Codes:
[… es folgen einige Dutzend Codezeilen]
Folgende Fehler treten aber bei mir auf: Nachdem ich diese ProtectBefehle eingebaut habe kommt folgende Fehlermeldung:
Hallo Herr R.,
mir fehlt noch eine Info:
* öffnen Sie mal bitte Ihr VBA-Projekt (also die Word-Datei
* Wechseln Sie nach VBA
* Heben Sie den Schutz auf
* Klicken Sie auf den Menüpunkt Debuggen / Kompilieren In welcher Zeile entsteht der Fehler? Könnten Sie mir bitte diese Zeile oder auch die Zeilen „außenrum“ schicken.
Hallo Herr Martin,
Sie sind aber schnell… Meinen Sie das?
*lach*
DAS ist Falsch.
Es muss heißen:
ActiveDocument.Protect NoReset:=True
Zwischen Protect und NoReset muss ein Leerzeichen statt des Punktes stehen
Liebe Grüße
Rene Martin
PS: und dann noch einmal Debuggen, bitte!
####
Aber immer noch J
stimmt, Herr R., da habe ich doch mal einen Artikel darüber geschrieben.
Die Methode Protect verlangt unbedingt den Parameter Type, obwohl IntelliSense dies nicht anzeigt:
ZÄHLENWENN (und SUMMEWENN) kenne ich. Damit kann ich die Anzahl der Zeilen bestimmen, die einem Kriterium genügen. ZÄHLENWENNS kenne ich auch. Damit kann ich zählen, wie oft mehrere Kriterien erfüllt sind, die mit einem logischen UND verknüpft sind.
Soweit so gut.
Aber wie kann ich eine Formel erstellen (gerne auch mit SUMMENPRODUKT), in der die Zeilen gezählt werden, bei denen in der ersten oder zweiten oder dritten Spalte nichts steht. Also: mindestens eine Spalte muss gefüllt sein.
Hallo Frau S.
ich würde die die drei Zellen verketten und wenn alle drei nicht leer sind, also <>““, dann würde ich diese zählen. Da Falsch = 0 und Wahr = 1, kann man die Summe verwenden.
beispielsweise so
=SUMME((C4:C15&D4:D15&E4:E15<>"")*1)
Hallo Herr Martin,
vielen Dank für die pragmatische Lösung, gefällt mir 😉
Beim Aufbau dieser Formel mit Textkettung scheitere ich daran, dass der Inhalt der Zelle sofort als Formel interpretiert wird und nicht als Text.
z.B. irgendwie so: =“=Wenn(„&ERSETZEN(I25;1;1;““)&“>0;“&ERSETZEN(I25;1;1;““)&“;““““)“ Fällt Ihnen dazu was ein?
Meinen ersten Vorschlag – mit Ersetzen ([Strg] + [H]) zu arbeiten, scheitert, weil die Formeln auf diesem Tabellenblatt unterschiedlich aufgebaut sind und auf verschiedene Tabellenblätter zugreifen.
Meine zweite Nachfrage, warum sie denn die 0 ausblenden will und ob man das nicht per Formatierung (oder über die Optionen) machen könne, wurde mit einem „manchmal sind auch Fehler in den Formeln drin – die möchte ich auch – im Nachhinein – abfangen“ quittiert.
Mein dritter Gedanke: eine Formel zu ändern und dann nach unten zu ziehen, scheitert, weil die Formeln alle unterschiedlich sind.
Man scheitert auch mit folgenden Schritten:
FORMELTEXT liefert den Namen der Formel
WECHSELN (oder ERSETZEN) ersetzt einen Formelteil durch einen anderen.
Das Ergebnis ist keine Formel, sondern ein Text.
Man kann ihn kopieren und als Inhalt einfügen und dann den Text in der Zelle markieren und anschließend in die erste Zelle einfügen; aber das ist bei vielen (unterschiedlichen) Zellen sehr mühsam. Aber – einen besseren Weg weiß ich nicht.
„Wo finde ich denn die Fußnoten in Excel?“, fragt mich eine Dame, die ich in puncto Excel berate.
Die gibt es nicht in Excel. Sie zeigt mir ihre Datei, die etwa so aussieht:
„Ich habe hier Sternchen eingefügt“, erklärt sie mir. Die kann ich ja auch hochgestellt formatieren.“
Ich nicke mit dem Kopf.
„Ob man denn auch bei Zahlen ein hochgestelltes Sternchen hinzuformatieren könne“, möchte sie wissen. „Nein – Zahlen sind ein Objekt“, lautet meine Antwort, „in Excel kann man nicht beispielsweise bei der Uhrzeit 1200 die beiden 00 hochstellen. Geht nicht!“
„Und bei Formeln?“, fragt sie:
Beispielsweise
=Vorjahr!D45&"*"
Meine Antwort: „Das geht leider auch nicht!“ Eine Formel kann eine Zahl liefern, aber keine formatierte Zahl. Ich kann die GANZE Zelle formatieren, aber leider nicht einen Teil des Ergebnisses der Formel.“
Sie bedauert.
Als ich argumentiere: „Aber PowerPoint hat auch keine Fußnoten“, antwortet sie: „aber dort kann ich einfach in die Texte Sternchen einfügen.“ Stimmt: PowerPoint kennt auch keine Zahlen wie Excel und keine Formeln.
Ich habe ein Tabellenblatt, in dem jeden Tag neue Daten eingetragen werden und dann diese wieder gelöscht werden, da diese Daten mit einer Auswertung zusammenhängen. Da am nächsten Tag dort wieder neue Daten eingetragen werden müssen.
Und Power-Query aktualisiert ja im Normalfall nur die aktuellen.
Ich habe nun folgendes versucht wie James Baylay in folgendem Beitrag:
let
Quelle = Excel.CurrentWorkbook(){[Name="tblZusammenfassung"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Schulz", Int64.Type}, {"Jackli", type number}, {"Marli", Int64.Type}, {"Jergli", Int64.Type}, {"Michal", Int64.Type}, {"Hans", Int64.Type}, {"Veitli", Int64.Type}}),
#"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ", tblDieSieben}),
#"Entfernte Duplikate" = Table.Distinct(#"Angefügte Abfrage")
in
#"Entfernte Duplikate"
Ich habe folgendes Problem. Wenn ich bei der Abfrage «tblZusammenfassung» Laden-in / Nur Verbindung erstellen ausführe, dann kommt die untenstehende Fehlermeldung:
Vielleicht haben sie einen Tipp für mich, wie ich das Problem lösen könnte.
VBA-Schulung. Ich zeige und erkläre den Makrorekorder.
Aufgabe: Zeichnen Sie ein Makro auf, das eine Zelle formatiert, beispielsweise: fett, kursiv, Schriftart, Schriftgröße, Ausrichtung, Hintergrundfarbe, … und testen es an einer anderen Zelle.
Eine Teilnehmerin beschwert sich, dass der Makrorekorder nicht „optimale Spaltenbreite“ aufzeichnet.
Was hat sie gemacht? Sie hat ihr Makro in einer anderen Zelle in einer anderen Spalte ausprobiert. Leider zeichnet der Makrorekorder „hart“ den Spaltennamen auf, beispielsweise:
Columns("D:D").EntireColumn.AutoFit
Später erkläre ich den Unterschied zwischen der relativen und der absoluten Aufzeichnung
Oh – man muss so aufpassen! Durchläuft man in VBA mit einem Zähler eine Sammlung an Objekten, greift sich Excel das erste, das zweite, das dritte, … Objekt. Aber welches ist das erste Objekt?
Bei Tabellenblättern ist das erste Objekt das linkeste. Dann das zweite von links. Dann das dritte.
Leider gibt es viele Sammlungen mit einer anderen „Reihenfolge“: die Reihenfolge, in der die Objekte erstellt wurden. Beispielsweise bei Diagrammen. Das erste ist nicht das linkeste, sondern das Diagramm, das zuerst erstellt wurde. Okay – das leuchtet ein.
Allerdings – wie sieht es bei Links aus. Wenn in einer Spalte mehrere Links stehen, ist der erste Link dann der oberste? Wird dann von oben nach unten weiter gezählt?
Nein! Auch hier gilt die Regel: die Reihenfolge des Erstellens entscheidet! Das heißt:
Das ist der erste Link:
Und nun wird der zweite und dann der dritte Link erstellt:
Und schließlich die übrigen 40.
Lässt man sie auslesen, erhält man:
Sub Links()
Dim i As Integer
Dim xlBlatt As Worksheet
Set xlBlatt = ActiveSheet
For i = 1 To xlBlatt.Hyperlinks.Count
xlBlatt.Range("C" & (i + 3)).Value = xlBlatt.Hyperlinks(i).SubAddress
Next i
End Sub
folgende Liste:
Zum Glück verfügt das Objekt Link über die Eigenschaften Range und damit über Range.Row und Range.Column. Natürlich auch über Range.Address. So kann man die Links lokalisieren.
Oh, Mann – wie kann man nur zwei Wörterbücher gleich nennen?
Genauer: in Word gibt es ein Wörterbuch. In Excel auch. Das VBA-Objekt heißt in Word „Dictionary“. In Excel „CheckSpelling“.
Zugleich gibt es neben Array und Collections in VBA eine Sammlung „Dictionary“. Sie kann verwendet werden, wenn man die „Microsoft Scripting Runtime“-Bibliothek einbindet. Und dann stellt sich die Frage: „welches Dictionary wird denn nun verwendet?“
Richtig: es tritt ein Fehler auf, wenn beide verwendet werden. Genauer: der Fehler tritt dann auf, wenn man ZUERST einen Verweis auf die Word-Bibliothek setzt und anschließend auf die „Microsoft Scripting Runtime“-Bibliothek :
Die besser Lösung (statt dem Ändern der Reihenfolge): Die Bibliotheken voll qualifizieren. Dann klappt es:
Dim wdApp As Word.Application
Dim wdWordWoerterbuch As Word.Dictionary
Dim wdWordBenutzerWoerterbuecher As Word.Dictionaries
' -- Dictionary
Dim wdListeDict As Scripting.Dictionary
Set wdListeDict = New Scripting.Dictionary
wdListeDict.Add "007", "Bond"
' -- das Word-Wörterbuch
Set wdApp = New Word.Application
Set wdWordBenutzerWoerterbuecher = wdApp.CustomDictionaries
For Each wdWordWoerterbuch In wdWordBenutzerWoerterbuecher
MsgBox wdWordWoerterbuch.Name
Next
mir ist doch noch etwas besseres eingefallen, lässt sich aber auch nicht umsetzen…
Ich wollte jetzt direkt in Power Query filtern nach den Zeilen, welche im Jahr, welches ich im Excel Blatt auswähle, ungleich 0 sind.
Irgendwo passt was noch nicht, hoffe du kannst mir helfen 😀
Hallo Nadine,
Wenn du Leerzeilen / Leerzellen rausfiltern möchtest, dann nicht wie in Excel mit zwei Anführungszeichen, sondern mit Null. Ähnlich wie Datenbanken hat PowerQuery einen eigenen Datentyp für leere Zellen: null:
= Table.SelectRows(#“Geänderter Typ“, each [Datum] <> null)
Und: der Feldname darf nicht in Anführungszeichen gesetzt werden – das Feld heißt: [Datum], nicht [„Datum“].
ich habe schon einiges in Power Query bearbeitet und bin gerade auf dem Stand, dass ich mir durch Filter genau die Daten aus Power Query ziehe welche ich benötige.
Aktuelles Problem: Ich möchte nur die Zeilen haben, welche in dem Jahr Werte haben, welches ich als Filter eingebe. Ich will aber nicht nur das Jahr, sondern alle Werte dann, wenn in diesem speziellen Jahr ein Wert vorhanden ist.
Den Filter „Jahr“ habe ich nicht in Power Query benutzt, da ich noch keine Lösung gefunden habe.
###
Hallo Nadine,
1.) wenn du einen Filter definierst, kannst du ihn auch in PowerQuery „reinziehen“. Danach würde ich ihn als Drilldown in einen Wert umwandeln – etwas so:
2.) Filtere ein beliebiges Datum. Es sieht dann so aus:
= Table.SelectRows(#“Gefilterte Zeilen“, each [Datum] >= #date(2020, 1, 1))
Und nun ersetze ich die Jahreszahl 2020 durch meine „Variable“ aus Schritt 1.
In Excel kann man, wenn die Berechnungsoptionen auf „manuell“ gestellt sind, die ganze Datei neu berechnen lassen oder ein Blatt:
Und was fragt die Teilnehmerin in der letzten Excelschulung:
„Kann ich auch alle Blätter neu berechnen lassen, aber nicht das aktuelle? Weil dort sehr viele Formeln stehen – DIESE Aktualisierung ist sehr zeitaufwändig.“
Ich wüsste nicht wie (außer natürlich mit einem Makro).
regex.Pattern = strMuster1
regexRaus.Pattern = strMuster1_Raus
regexRaus.Global = True
For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
If regex.Test(Range("I" & i).Value) = True Then
Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
j = 0
For Each strFund In strTreffer
strTemp = strFund
If IsDate(strTemp) Then
If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
Else
Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
If Year(CDate(strTemp)) > Year(Date) Then
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
End If
Else
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
j = j + 1
Next
End If
Next
Vier andere Varianten werden analog abgearbeitet. Klappt.
Warum hat VBA nicht als Standard Regex eingebunden?
Warum kennt PowerQuery keine regulären Ausdrücke?
Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?
Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:
Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.
Es geht um die Extrahierung von Datums- und Adressinformationen aus einem Bemerkungsfeld im Rahmen der Datenmigration.
Die Ausgangsinformationen stehen einer Spalte.
Diese Felder enthalten Stichtage und Adressen (Straße, Hausnummer, Zusatz).
Zum Stichtag:
Es gilt folgende Grundregel: Falls im Bemerkungsfeld ein Stichtag aufgeführt ist, dann soll dieser als Stichtag übernommen werden ansonsten gilt das Ausgabedatum als Stichtag
Der Stichtag ist mit unterschiedlichsten Schreibweisen im Bemerkungsfeld versteckt. Beispiele dazu:
(Stichtag 18.07.2014)
ST 05.08.2021
ST: 18.02.2022
(St. 08.01.2018)
(St. 01.09.16)
(31.08.16)
St.08.10.2015
StT 24.6.14
St. 01-2017
St. 10/2015
St.04/2005
(St. 24.04.2014+16.07.2015)
ST 05.06.1990 u. 11.12.1991
2 Stichtage zus.gefasst 17.06.2020 + 06.10.2021
Aufgabe ist:
Unterschiedliche Schreibweisen für den Stichtag herauszufinden und zu berücksichtigen
Nach obiger Grundregel die Stichtage als Datum zu extrahieren
Weitere ggfs. notwendige Regeln abzuklären und zu ergänzen:
Falls reine Monatsangabe, dann den 1. als Tag verwenden
Falls zwei Stichtage angegeben, diese markieren, müssen manuell geklärt werden (bei zwei Datensätzen Stichtage aufteilen, bei einem Datensatz wahrscheinlich erster Stichtag)
Zur Adresse:
Es gilt folgende Grundregel: Die Adresse aus dem Bemerkungsfeld soll extrahiert und in Straße, Hausnummer und Zusatz getrennt übernommen werden
Einschränkungen:
Es gibt einzelne Datensätze zu auswärtigen Gemeinden oder zu speziellen Portfolioobjekten, für die keine Adresse zu extrahieren sind. Diese sind in der Spalte „Keine Adresse“ gekennzeichnet.
Im Migrationsskript wurden für viele Datensätze die Adresse bereits extrahiert und zwar für die Adressen, deren Straßennamen bekannt waren. Diese sind in den Spalten REFERENZOBJEKTADRESSE_STRASSE, …_HAUSNUMMER und …_ZUSATZ entsprechend gefüllt und müssen nicht mehr berücksichtigt werden.
Die Adresse steht, sofern vorhanden, am Anfang des Bemerkungsfeldes. Falls keine exakte Adresse vorhanden war, steht vor dem Straßennamen teilweise „Nähe“ oder „am“, „an der“, … oder auch zwei Straßennamen, an dem sich ein Grundstück befindet Beispiele für Adressen:
Adenauerallee
Ackerweg
Alte Poststrasse
Alt-Moabit
Am Borsigturm
Augsburger Straße
Augsburger Strasse
Avenue d’Ouchy
Clius
Grosse Praesidenten Str.
Im Astenfeld
Inge Beisheim Platz
Kammelenbergstrasse
Aufgabe ist:
Adressen zu erkennen und zu extrahieren gemäß obiger Grundregel inkl. der Einschränkungen
Weitere ggfs. notwendige Regeln abzuklären und zu ergänzen:
Bezugsangaben wie Nähe, am usw. mit Straßennamen extrahieren, sofern keine Hausnummer vorhanden
Falls zwei Adressen/Straßen vorhanden, keine extrahieren
Meine Antwort: Uff! So etwas können nur Menschen eintragen! Ich hoffe, dass bei der nächsten Dateneingabe die Daten besser werden.
(Randbemerkung: Beim Screenshot handelt es sich um rein fiktive Daten!)
Osterkaffee mit Nachbarn. Frau Nachbarin „greift mein Wissen ab“ und stellt mir eine Frage. Natürlich zu Outlook.
„Sag mal: ich möchte mehrere Personen einladen, aber so, dass sie sich nicht gegenseitig sehen; also ich möchte sie auf BCC setzen. Wo schalte ich das bei einer Besprechungseinladung ein?“
Meines Wissens geht das nicht, denn der Witz an Einladungen ist ja, dass jeder jeden im Terminplanungs-Assistenten sehen kann.
Mein Vorschlag, einen Termin zu erstellen und diesen als Anlage an alle BCC zu senden, stieß auf keine Gegenliebe: „Ich will ja die Leute einladen. Sie sollen zu- oder absagen. Das will ich ja in meinem Kalender sehen. Aber so, dass DIE nicht die Mailadressen der anderen sehen.“
Ich bin gerade völlig perplex. Ich programmiere für eine Schweizer Firma ein Excel-Tool. Ich lasse alle Tabellenblätter, die mit Monatsnamen beschriftet sind, ausblenden. Nur das Blatt MRZ bleibt stehen. In der Schweiz bleibt es stehen.
Ich frage Tanja Kuhn. Sie hat die Schweizer Oberfläche von Excel:
Und ja: sie bestätigt es: seit einigen Versionen lautet die Abkürzung des dritten Monats in der Schweiz nicht Mrz, wie in der ISO 8601 (EN 28601:1992) festgelegt, sondern Mär. Das erkennt man schnell, wenn man den Text „Jan“ einträgt unter herunterzieht:
Und eben deshalb liefert der VBA-Befehl
Format(DateSerial(2022, 3, 1), "MMM")
in der Schweiz etwas anderes als in Deutschland (oder Österreich).
Ich weiß – nicht alle Feldfunktionen werden automatisch aktualisiert. Beispielsweise REF. Innerhalb eines Textes ist es kein Problem mit REF auf ein Formularfeld zu verweisen. Klappt prima.
Klappt allerdings nicht, wenn sich das Feld in einem Textfeld oder in der Kopf-, beziehungsweise Fußzeile befindet:
Es geht hier um den Umgang mit einem umfangreichen Fußnotentext (hier: rot).
Wie Sie sehen, ist auf der ersten Seite Haupt- und darunter Fußnotentext.
Nun ist aber ab Seite 2 nur noch Fußnotentext zu sehen.
Wie kann man das anstellen, daß auch ab Seite 2 ff. sowohl Haupt- als auch Fußnotentext platziert werden kann.
Ich habe leider keine Lösung gefunden.
Hallo Herr F.,
schöne Frage. Ich habe mal nachgeschaut (haben Sie sicherlich auch schon): Wenn ich in der Entwurfsansicht mit über Referenzen die Notizen anzeigen lasse, finde ich nur die Fußnoten, Trennlinien und Fortsetzungstexte, aber keine Einstellungen oder Optionen für eine maximale Länge.
Ich habe auch eine Tabelle versucht – auch das klappt nicht.
Sorry – aber ich weiß keine Lösung dafür.
Ich würde eher fragen, ob wirklich sooooo viel Text in der Fußnote stehen muss – wären hier Endnoten nicht besser geeignet? Oder sollte der Text nicht besser im Text stehen?
Ich möchte wissen, ob es eine Funktion gibt, die mir das Einbetten von Visio-Organisations-Diagrammen in PowerPoint ermöglicht, die einem Powerpoint-Anwender die Bearbeitung der Objekte ermöglicht, ohne dass er Visio auf dem Rechner installiert hat.
Ich hatte gehofft, dass es im Hintergrund der Einbettung eine solche Anwendung gäbe, doch ich habe verschiedene Objekt-Einbettungen in PowerPoint aus Visio ausprobiert, habe es mit Kopieren und mit Links versucht, aber meine Kollegin, die keine Visio-Lizenz auf dem Rechner hat, kann keine davon bearbeiten (die Links griffen übrigens auf einen für meine Kollegin gesperrten Ordner zu, das wird auch so bleiben).
Vielen Dank im Voraus,
mit freundlichen Grüßen aus Hamburg,
####
Hallo Frau T.,
ich fasse Ihre Frage zusammen: Sie möchten eine Visio-Zeichnung bearbeiten, ohne dass Visio installiert ist. Das geht nicht!
Es ginge auch nicht, wenn Sie eine Excel-Tabelle nach Word kopieren und diese dann auf einem anderen Rechner, auf dem kein Excel installiert ist, bearbeiten wollen.
Ich habe es mal probiert: Wenn Sie die Visio-Zeichnung kopieren
und als „Erweiterte Metadatei“ in PowerPoint einfügen,
können Sie diese in PowerPoint als „Grafik umwandeln“ (bearbeiten),
die Gruppierung aufheben und nun jedes Objekt wie eine PowerPoint-Form bearbeiten.
Aber ich fürchte, das wollen Sie nicht …
Und: ich fürchte, dass ich Ihnen hier nicht helfen kann.
nun haben sich bezüglich der Kriterien- „=“-Auswahl einige Fragen ergeben.
Zur Erinnerung: Sie hatten uns Excel-Dokumente erstellt, in denen unterschiedliche Funktionen kombiniert wurden, damit bestimmte Filterungen auf ein eingelesenes Exceldokument ausgeführt werden können.
Zu den Fragen:
Wie ist es möglich, dass bei dem Bereich Kriterium-„=“ auch mehrere Werte eines Attributs angegeben werden können? Z.B. Attribut Lagekarte-GAA = G – Gut Lagekarte-GAA = D – Durchschnitt Und es werden alle Kauffälle angezeigt, die entweder Lagekarte-GAA = G – Gut ODER Lagekarte-GAA = D – Durchschnitt haben.
Hallo Frau I.,,
Habe ich Sie richtig verstanden: Sie möchten eine Mehrfachauswahl mit ODER.
Das Multiplikationszeichen entspricht dem logischen UND; das Additionszeichen den logischen ODER.
Zu DateSerial: Kennst du dich Excel-Funktion DATUM nicht? Sie wandelt eine JahresZAHL, eine MonatsZAHL und eine TagesZAHL in ein Datum um. Und eben das macht DateSerial in VBA.
Frank stellt die neuen Elemente von Excel vor. Beispielsweise findet sich im Kontextmenü der Statuszeile ein neuer Eintrag „Sheetnummer“:
Abgesehen vom merkwürdig übersetzen Namen „Sheetnummer“ eigentlich eine praktische Sache: Die Anzahl der Tabellenblätter werden angezeigt.
Allerdings: Auch ausgeblendete Blätter (auch xlSheetveryHidden) werden aufgelistet.
Josef wirft die Frage in den Raum, ob wir das wollen. Zu Recht: ich will das eigentlich nicht – manchmal „verstecke“ ich bewusst Dinge in Tabellen, die ich weitergebe, die nicht auf den ersten Blick einsehbar sind …
Danke an Frank Arendt-Theilen für diese Information.
Frank stellt den Navigator vor, den Excel vor Kurzem in Microsoft 365 eingeführt hat:
Woah, denke ich: endlich nach 20 Jahren hat Microsoft von openOffice Calc und LibreOffice Calc den Navigator abgeschaut – den gibt es dort seit Ewigkeiten!
kennst Du das Problem beim Löschen von Zeilen bei ausgeblendeten Spalten?
Eine intelligente Tabelle mit Filter.
Hier sind auch die ganzen Zeilen und alle gefilterten Zeilen markiert und STRG + Minus funktioniert nicht. Spalte B ist ausgeblendet.
Zeilen in einem gefilterten Bereich oder in einer gefilterten Tabelle können nicht verschoben werden.
Sind nur die Daten markiert und nicht die ganzen Zeilen, funktioniert es doch wieder mit Meldung. Es sind dann aber wieder alle Daten der Zeile weg und nicht nur die markierten Tabellenwerte.
In Excel kann man eine Reihe von Elementen einfügen: Bilder, 3D-Grafiken, Diagramme, Formen und auch SmartArts:
Öffnet man diese Datei in Excel online, wo SmartArts nicht unterstützt werden, wird dort ein Rechteck angezeigt:
Ebenso in teams:
Andreas Thehos schreibt:
„habe heute einen schönen Excel-Fehler gefunden.
SmartArts sorgen dafür, dass sämtliche Objekte eines Tabellenblatts entfernt werden.
Die Datei liegt auf einem SharePoint Online. Sobald jemand online per Browser oder Teams darauf zugreift und auch nur das Tabellenblatt wechselt, werden bei der Synchronisation alle Objekte im Blatt des SmartArts entfernt. Anschließend gibt es einen Fehler in Excel in drawingsX.xml
Ich habe eine Arbeitsmappe mit sieben + ein, also acht Tabellenblattern. Sie heißen:
Chef, Brummbär, Schlafmütz, Hatschi, Pimpel, Happy, Seppel und Schneewittchen
Die ersten drei Blätter sind ausgeblendet, ebenso das letzte (Master-)Blatt:
Also auf Excel-Seite sieht das so aus:
Nun soll per VBA das erste Blatt sichtbar gemacht werden, umbenannt werden und an eine bestimmte Position verschoben werden. Das Masterblatt „Schneewittchen“ wird anschließend wieder unsichtbar gemacht.
Schritt für Schritt:
1.) Zugriff auf das (unsichtbare) Blatt „Schneewittchen:
Dim xlBlatt As Worksheet
Dim i As Integer
Dim strBlattname As String
Set xlBlatt = ThisWorkbook.Worksheets("Schneewittchen")
2.) Blatt wird eingeblendet:
xlBlatt.Visible = xlSheetVisible
3.) Blatt wird vor das erste Blatt geschoben:
xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)
4.) Mit Hilfe einer benutzerdefinierten Funktion GibtEsBlattname wird überprüft, welcher Blattname noch nicht existiert, beispielsweise „Schneewittchen 001“:
i = 0
Do
i = i + 1
strBlattname = "Schneewittchen " & Format(i, "000")
Loop Until GibtEsBlattname(strBlattname) = False
5.) Das erste Blatt wird umbenannt:
ThisWorkbook.Worksheets(1).Name = strBlattname
6.) Das Masterblatt „Schneewittchen“ wird ausgeblendet:
Die Move-Methode des Worksheet-Objektes konnte nicht ausgeführt werden.
Der Fehler liegt in folgender Zeile:
xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)
Welches ist das ERSTE Tabellenblatt? Blatt Nummer 1 oder das erste sichtbare Tabellenblatt. Die Antwort: letzteres: das Blatt wird vor das erste sichtbare Blatt kopiert. Damit ist es anschließend nicht das erste Blatt! Sondern die drei ausgeblendeten Blätter liegen noch weiter links von dem Blatt.
Leider besitzt die Methode Copy kein Objekt, an das das Ergebnis des Kopierens übergeben werden kann.
Also muss man sich „auf die Suche“ nach dem Blatt machen, beispielsweise: durchlaufe alle Blätter und such das Blatt, das „Schneewittchen(2)“ heißt, besser
ThisWorkbook.Worksheets(1).Name Like "Schneewittchen *"
Nein, das ist nicht schön! Visio weigert sich, einen Button auf das Zeichenblatt einzufügen:
Ich lösche einige Shapes auf dem Zeichenblatt, wie vorgeschlagen – aber es nützt: gar nichts.
Mein Trick: Ich habe ein zweites Zeichenblatt erstellt, dort einen Button eingefügt; die Zeichnung auf das zweite Blatt kopiert und alles wieder zurückkopiert. Klappt. Okay – ich musste noch die Buttons umbenennen – die Namen hat mir Visio bei der Kopieraktion zurückgesetzt.
deine Mails mit positiven Informationen und Anhang erreichten mich.
Themenwechsel 3 von 3:
Falls du an weiteren Fragen rund um EXCEL-VBA Interesse hast, informiere mich einfach. Dazu habe ich dir zwei Dateien mitgesendet. Die Lösung der Probleme ist kein MUSS.
Die Thematiken:
(1) ein Filterproblem ist bestimmt nicht so schwierig zu lösen. Eine Liste:
Einer gewinnt! Oder: es kann nur einen Highlander geben.
Schöne Frage in der Excel-Schulung: kann ich eine (intelligente) Tabelle mit einer bedingten Formatierung versehen? Wissen Sie es? Welche Farbe gewinnt?
Gegeben sei ein Listenbereich:
Er wird in eine (intelligente) Tabelle konvertiert:
Natürlich kann ich die Schriftfarbe festlegen (beispielsweise für alle Flüsse aus Asien):
Aber: wer gewinnt, wenn ich eine Hintergrundfarbe hinzufüge?
Wer wohl?
Richtig: die bedingte Formatierung!
Und: was passiert, wenn die Liste verlängert wird? Richtig: dann wird die bedingte Formatierung, wie auch andere Formatierungen, mitgenommen:
Ich empfehle dennoch: entweder intelligente Tabelle OHNE Formatierung oder bedingte Formatierung nur mit Schriftfarbe.
Perfide. Ich habe eine Excelmappe auf der Festplatte – das VBA-Projekt ist mit Kennwort geschützt. Ich kenne das Kennwort. Dennoch: es lässt sich nicht öffnen:
Ich brauche eine ganze Weile, bis es mir dämmert: ich habe die Sprache der Tastatur geändert:
Und richtig: dort sind Y und Z vertauscht und auch die Sonderzeichen sitzen an anderen Positionen …
Es ist schon eine Weile her, dass wir Kontakt hatten und ich hoffe es geht Dir in Anbetracht der Weltlage einigermassen gut?
Leider kämpfe ich wieder mit einem Problem, wobei ich mich ehrlich gesagt mehr darüber ärgere, dass ich das Problem nicht finde, als über den Fehler selbst. Es geht wieder um das VBA Script, welches die Excel Daten auswertet und je nach Zuweisung im Layer meine Shapes sichtbar macht, oder eben nicht.
Das Script macht eigentlich was es soll, doch nach dem Durchlaufen der letzten Zeile, kommt eben der genannte Fehler. Vielleicht hast Du etwas Zeit und bist willig mir zu helfen? Herzlichen Dank im Voraus für eine Reaktion.
Hallo H.,,
schau mal in deine Daten: der letzte Datensatz ist leer:
Du musst die überprüfen/abfangen, beispielsweise mit:
Ich erkläre den Nutzen und die Vorteile von (intelligenten) Tabellen. Beispielsweise Diagramme. Setzt man auf eine intelligente Tabelle ein Diagramm auf:
so wird die Erweiterung der Liste sofort ins Diagramm aufgenommen:
Was mich jedoch irritiert: Warum zeigt das Diagramm weder im Diagrammbereich, in den Achsenbeschriftungen noch in den Legendeneinträgen den Namen der Tabelle an – sondern immer noch den Bereich?
Die Pivottabelle zeigt doch auch die „korrekte“ Datenquelle an:
Ich will in der Excelschulung demonstrieren, dass Listen eine Überschriftszeile haben sollten; ja – dass Pivottabellen auf Listen aufsetzen, bei denen jede Spalte eine eindeutige Überschrift besitzt.
Ich lösche eine Überschrift heraus:
erstelle eine Pivottabelle, vergesse aber, den Haken beim Datenmodell zu entfernen:
Da die Daten ins Datenmodell geladen werden, muss jede Spalte eine Überschrift haben. Da dies nicht gegeben ist, füllt Excel nicht die leere Überschrift auf, sondern beginnt ab der zweiten Zeile, die als Überschrift verwendet wird:
Eigentlich wollte ich die Fehlermeldung
Der PivotTable-Feldname ist ungültig. Um einen PivotTable-Bericht zu erstellen, müssen Sie Daten verwenden, die einer Liste mit Spaltenüberschriften organisiert sind. Wenn Sie den Namen eines PivotTable-Berichtsfeldes ändern, müssen Sie einen neuen Namen für das Feld eingeben.
Excelschulung. Thema: Listen. Ich beginne mit der Antwort auf die Frage, wie eine Liste in Excel aufgebaut werden soll:
„EINE Überschriftszeile“ deklamiere ich: „EINE, genau EINE – jede Überschrift MUSS eine Überschrift haben.“
Wir ändern die Daten, verschieben, löschen, benennen um, … und: sortieren.
Ein Teilnehmer fragt, warum die Überschrift IN der Liste steht. Meine Antwort: „Weil Sie nicht tun, was ich Ihnen sage!“ Sie haben sicherlich eine Spalte OHNE Überschrift versehen. Machen Sie mal bitte den letzten Schritt zurück!“
„Tatsächlich“, lautet seine Antwort: „eine Spalte hatte keine Überschrift!“
Ich schiebe nach: Wenn Sie unsicher sind, ob Ihre Liste eine Überschrift hat, beziehungsweise die erste Zeile als Überschrift erkennt, dann verwenden Sie die „benutzerdefinierte Sortierung“. Dort ist deutlich zu erkennen: Überschrift oder keine Überschrift; dort kann man auch explizit einschalten: „Bitte mit Überschrift“. Das heißt: die erste Zeile bleibt beim Sortieren bitte oben stehen!
Ich soll – weil Lotus Notes in einer Firma abgeschaltet wird – eine Notes-Datenbank in Access nachbauen.
Access hat weder Kästchen zum Auf- und Zuklappen von Bereichen:
Noch kann man in Access EINZELNE Bereiche in einem Endlosformular dynamisch vergrößern lassen. Letzteres habe ich mit VBA gelöst – die Zeilenzahl ermittelt und dann den Detailbereich – genauer: ALLE Detailbereiche – vergrößert:
Etwa zu der Zeit als die Dinosaurier verschwanden, habe ich eine Access-Datenbank erstellt – eine MDB-Datei.
Nun hätte ich gerne die Daten dieser Datei, ich versuche sie zu öffnen – und: padautz: Access kann nicht (mehr). Das ist sehr ärgerlich. Auch mit PowerQuery komme ich nicht mehr an die Daten. Da werden die Dinosaurier wohl sehr traurig werden, wenn sie nie mehr das Licht der Welt erblicken können …
Ich soll eine online-Excel-Schulung über teams halten. 1,5 Stunden (sic!) sind angesetzt. Bevor die Schulung stattfindet, bittet mich die Dame, die diese Schulung organisiert, ob ich – wie immer – einen Screenshot der Teilnehmerinnen und Teilnehmer zu machen. Erwartet werden zirka 150 (sic!) Nasen. Am besten – so schiebt sie nach – wären mehrere Screenshots – zu Beginn, in der Mitte und einer am Ende.
Ich überlege: Ein Screenshot aller Teilnehmerinnen und Teilnehmer – das bedeutet, dass ich mehrere Screenshots machen muss, da ich mit einer Aufnahme nicht alle Namen „einfangen“ kann. Aber wie soll ich, während ich rede und meinen Bildschirm teile, einen Screenshot machen? „Moment mal – ich muss Sie mal kurz abfotografieren?“ – Ein absurdes Vorgehen.
Da fällt mir ein, dass teams eine Option „Anwesenheitsbericht“ anbietet:
Ich probiere NACH der Schulung (?!?) aus und: tatsächlich: ich erhalte einen Bericht und eine Übersicht, wer sich wann angemeldet hat und wer wann gegangen ist. Klasse.
Ob das datenschutzrechtlich in Ordnung ist, sei dahin gestellt … Aber die Dame, die diese Schulung organisierte, war sehr zufrieden.
Das Arbeitsblatt oder die Arbeitsblätter, die in die Zielarbeitsmappe kopiert oder verschoben werden, besitzen möglicherweise angefügte Makros. Makrocode stellt ein potenzielles Sicherheitsrisiko dar. Sie sollten den Vorgang nur fortsetzen, wenn Sie sicher sind, dass der Makrocode von einer vertrauenswürdigen Quelle stammt. Möchten Sie den Vorgang fortsetzen?
Ich habe keine Ahnung, wo man das in Excel einstellen kann. Ich vermute, diese Option wurde in den Gruppenrichtlinien von der IT abgefangen. Oder? Kennt jemand diese Meldung?
Ich beginne bei der Mikrotypografie: bedingter Trennstrich, Halbgeviertstrich und natürlich geschütztes Leerzeichen. Ich zeige, dass man ein „Wort“ vor dem Leerzeichen mit dem Wort „hinter“ dem Leerzeichen zusammenhalten kann, indem man die Tastenkombination [Umschalt] + [Strg] + [Leertaste] drückt:
Was passiert? Richtig: die Steuerungsleiste von teams wird aufgerufen:*ggrrrrrr*
Also gut – dann muss ich dieses Zeichen über Einfügen / Symbol einfügen. Da mir teams die Taste „gestohlen“ hat …
Es ist zum Haare-Raufen. Hätte ich welche auf meinem Kopf! Unglaublich! Excel ärgert mich, wo es nur kann. Wenn ich schon einen Fehler haben möchte – was passiert? – Richtig – natürlich kein Fehler! Es ist zum Haare-Raufen!
Was ist geschehen?
Excelschulung. Turboschulung: ich zeige in einer Stunde Listen: sortieren, filtern, intelligente Tabellen, Datenschnitt und Pivottabellen. Eine Teilnehmerin bedankt sich für die Infos zu den Pivottabellen – das hätte ihr sehr weitergeholfen; nun verstehe sie den Gedanken, der dahinter steht. Und: „so schwierig ist das gar nicht“:
Ich wiederhole. „Der Aufbau der Tabelle ist wichtig: Entweder Sie nehmen eine intelligente Tabelle oder Sie achten darauf, dass Ihre Liste keine Leerzeile und keine Leerspalte hat. Und: jede Spalte muss eine Überschrift haben.“
Ich demonstriere es, lösche eine Spaltenüberschrift raus
erstelle eine Pivottabelle – und: es klappt! Excel unterläuft meine Schulung. Jetzt, wo Excel einen Fehler erzeugen sollte tut Excel: NICHTS! Fügt den gelöschten Spaltennamen ein:
Der Gedanke: Klar – eine zweite Pivottabelle wird nicht auf der Liste aufgesetzt, sondern auf dem Pivotcache. Deshalb weiß Excel auch den Namen der fehlenden Spaltenüberschrift. Der Fehler käme beim Aktualisieren zum Tragen.
Oder – damit die Teilnehmerin mir glaubt – ich kopiere die Liste in eine andere Datei, erstelle dort die Pivottabelle und:
Letzte Woche habe ich eine interessante Frage erhalten. Vielleicht kann man sie mit LAMBDA lösen. Aber da er kein LAMBDA hat, habe ich eine Lösung mit Hilfsspalte gebaut. Etwas Besseres ist mir nicht eingefallen.
Die Fragestellung: in mehreren getrennt nebeneinander stehenden Reihen befinden sich Daten. Gesucht ist der Rang:
Problem 1: Die Funktion RANG setzt einen zusammenhängenden Wertebereich voraus.
Problem 2: diese Funktion ist in keiner anderen Funktion vorhanden – nicht in AGGREGAT oder TEILERGEBNIS, …
Deshalb habe ich es mit einer Hilfsspalte gelöst: Alle Werte untereinander geschrieben, sortiert und über die Funktion VERGLEICH die Position ermittelt. Mir ist nichts Eleganteres eingefallen:
Ein bisschen kniffelig war es. Ein bisschen probieren musste ich schon.
Die Aufgabe: in einer Zeile soll ein „x“ an einer oder mehreren Positionen stehen. Die erste Position (von links) soll ermittelt werden und aus einer anderen Zeile (der Überschriftszeile), der entsprechende Wert hierzu angezeigt werden.
Den Wert zu finden, ist nicht schwierig:
=VERGLEICH("x";E165:W165;0)
Den zugehörigen Wert zu ermitteln, auch nicht:
=INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0))
Da die leeren Zellen einen Fehler erzeugen würden, kann dieser noch abgefangen werden:
Vor Kurzem habe ich auf meinem Laptop auf Windows 11 umgestellt. Der erste Schreck kam in der Form, dass Laufwerk D nicht mehr gefunden wurde. Nicht schön.
Zuerst habe ich in den Diensten gewühlt; anschließend in der Datenträgerverwaltung. Dort wurde ich fündig und habe den „Datenträger neu eingelesen“
Vorgestern habe ich mit Martin geplaudert. Über teams. 13 Uhr haben wir ausgemacht. Um 12.59 Uhr wollte ich den „Raum betreten“ – da schickte er mir eine Mail, wo ich denn bleibe. Ein Witz von mir folgte – „ist doch erst 13.00 Uhr“. „Nein“, war Martins Antwort, „es ist 13.08 Uhr“. Mein Blick auf eine andere Uhr gab ihm recht: meine Laptop-Uhr ging fast zehn Minuten nach. „Werden die Uhren denn nicht automatisch gestellt?“
Nach dem Schwatz machte ich mich auf die Suche. Tatsächlich: durch die Systemumstellung war eingestellt:
„Uhrzeit automatisch festlegen: aus“
Also: Fluchs einschalten – jetzt tickt es bei mir wieder richtig. Zumindest auf meinem Laptop mit Windows 11.
Ich gestehe, dass ich mich manchmal irre. Oder Dinge übersehe. Oder schlicht und einfach nicht weiß. Umso mehr freue ich mich darüber, dass andere meinen Blog mitlesen und mir korrigierende Antworten schreiben. Eine Berichtigung hat mich vorgestern erreicht – ich möchte sie hier gerne wiederholen, weil ich danke, dass sie wichtig ist.
Solche Meldungen erfreuen immer wieder Sinn und Gemüt:
Das Projekt kann nicht erstellt werden, weil das „Excel Visual Studio-Entwurfszeitadapter-Add-In“ nicht ordnungsgemäß ausgeführt wird. Das Add-In wurde in Excel möglicherweise deaktiviert oder für inaktiv erklärt oder in den Einstellungen im Sicherheitscenter sind alle Add-Ins deaktiviert. Überprüfen Sie den Add-In-Status in den Excel-Optionen. Wenn das Add-In aktiv und aktiviert ist, reparieren Sie Visual Studio Tools for Office, oder führen Sie eine Neuinstallation aus,
Lange, sehr lange sitze ich davor und versuche den Sinn zu begreifen …
Die Aufgabe hört sich simpel an – aber ich wüsste keine einfache Lösung.
Frage in einer Excelschulung: „Wie kann ich die Abteilung bequem auswählen?“ Noch bevor ich „Datenüberprüfung“ nachschieben konnte, kam: „ich habe manchmal ein und manchmal mehrere Kriterien.“
Stimmt: mit einer Dropdownliste (Datenüberprüfung) kann nur eine Auswahl getroffen werden. Wenn bereits ein Text in einer Zelle steht, beispielsweise in C6 „Controlling“ kann ich nicht mit einer Formel diesen Text verketten mit einem anderen Text. Das wäre ein Zirkelbezug. VBA und Programmierung schied aus.
Meine Lösung sieht folgendermaßen aus: alle Elemente (hier: Abteilungen) werden aufgelistet. In mehreren Zellen wird eine Einzelauswahl getroffen:
Diese Texte werden verkettet. Wichtig ist der Parameter Leere Zellen ignorieren: WAHR:
=TEXTVERKETTEN(ZEICHEN(10);WAHR;C2:H2)
Und diesen Text könnte man mit Kopieren / Inhalte einfügen in die gewünschte Zelle einfügen.
Die Teilnehmer waren nur mäßig zufrieden. Ich auch. Ich überlege noch nach einer besseren Lösung. Ohne VBA.
Du, ich muss mich verzweifelt bei dir melden mit einem Excel-Problem. Ich mache einen Import zu WordPress und der Kunde hat mir die Inhalte als Excel geliefert. Es geht um Schadbilder (Gärtner-Themen). Jedes Schadbild wird ein Artikel und sollte deshalb eine Zeile sein. Soweit so gut, jetzt der Kniff: Jeder Text hat Zwischentitel und diese sind aber als Spalten im Excel File angelegt. Also sind die verschiedenen Spalten nicht einzelne Felder in WordPress, sondern ein grosses Textfeld. Und die Spaltentitel sollten jeweils als Zwischentitel in diesen Texten zu finden sein. Die Zwischentitel sollten zudem ein HTML H-Tag erhalten und nicht einfach „fett und grösser“ sein.
Kannst du mir da vielleicht sagen, wie ich weiterkommen kann? Bitte sei ehrlich, wenn das deine Kapazitäten sprengt. Dann machen wir das manuell, das würde auch gehen, es sind um die 140 Artikel.
Ich gestehe – ich habe zuerst überlegt, dieses Problem mit TEXTVERKETTEN zu lösen. Als Trennzeichen hätte ich „</p><p>“ oder Ähnliches eingegeben. Aber irgendwie gefiel mir die Rechnerei nicht.
Warum nicht PowerQuery?
Klar: 1. Schritt: Liste in Tabelle verwandeln. Die Daten aus Tabelle/Bereich importieren:
Das Zauberwort heißt „entpivotieren“. Und schon habe ich eine Tabelle mit zwei Spalten: in der ersten steht die Überschrift, in der zweite die Daten aus den entsprechenden Tabellen:
Und das kann problemlos zu einer Spalte verkettet werden:
Es hat nichts mit Excel zu tun, aber es amüsiert mich: seit einigen Tagen erscheint rechts in der Taskleiste regelmäßig ein Häschen mit Deutschlandflagge:
Kaum versuche ich darauf zu klicken. hoppelt es fort und verschwindet. Und zeigt an, dass es Nacht ist oder regnet. Oder beides:
Ein Dankeschön an Martin Weiß. Er hat auf unserem Excelstammtisch sehr schön die Unterschiede zwischen Excel online (sprich: Excel für das Web) und Excel Desktop herausgearbeitet. Ich habe mir noch nie die Mühe gemacht, die Symbole nebeneinander zu stellen. Dabei ist mir aufgefallen, dass sich einige Beschriftungen unterscheiden:
Zellenformatvorlagen und Formatvorlagen:
Bilder und Grafiken, Link und Hyperlink:
Filter und Filtern, Sortieren und benutzerdefinierte Sortierung:
Tabellenansicht und Arbeitsmappenansicht:
Wer findet weitere Unterschiede?
Ich weiß – DAS sind lediglich Marginalien – spannender sind die Unterschiede der Versionen online und Desktop, die Gemeinsamkeiten und die Frage, was beim Datenaustausch passiert.
Wer Antworten auf diese Fragen sucht, wird fündig auf Martins Blog:
jetzt mein kleines Excel-Phänomen, ein etwas interessantem Verhalten von Excel im Bezug auf Leerzellen, die nicht leer sind.
Natürlich habe ich deinen Artikel „Excel und das Nichts“ gelesen, ich denke, das folgende Phänomen geht in diese Richtung.
Bin mal gespannt, ob du das auch schon so gesehen hast.
Wir wollten ein paar ganz simple X-Y-Punktdiagramme erstellen bzw. ein Kollege ruft mich an, „Ich kann kein korrektes Punkt-Diagramm erstellen, das gibt’s doch nicht!“.
Der Datenbereich für die Y-Achse weist einige Leerzellen auf (die sind aber egal), die X-Achse hatte zunächst eine durchgehende Datenreihe, also ohne Lücken.
Dann wollten wir für ein 2. Diagramm eine andere Spalte als X-Achse nehmen (Spalte I). Excel erstellte nun aber nicht ein Punktdiagramm, welches die beiden Datenreihen als X-Y-Kombination zeigte, sondern „zählte“ sozusagen hoch, begann bei 1 und endete bei 54 (Ende der Datenreihe).
Beispiel: 66,67 wäre der eigentlich Wert, es wurde aber die 37 (es war der 37ste Wert in der Spalte) als X-Wert genommen.
Die Ursache war recht schnell gefunden, es gab eine leere Zelle in der Datenreihe, die aber doch nicht ganz leer war: Tippe in Zelle I16 und drücke „entf“ und beobachte, wie sich das Diagramm verändert.
Die Entstehungsgeschichte der Leerzelle, die nicht ganz leer ist, ist auch ganz interessant:
Mittels „Wennfehler(XXX, ““) zu einem „Gänsefüßchen-Leer gemacht
Mittels Powerquery à Aus Datei à Aus Ordner abgerufen und zusammengeführt (PowerQuery zeigt auch nicht „null“ für die Zelle an)
Mittels copy+paste irgendwo anders hin kopiert.
Dominic Dauphin
####
Hübsch! Sehr hübsch!
Hallo Dominic,
das „Hochzählen“ kommt daher, dass Excel keine Werte mehr erkennt, sondern Texte. Wenn du die Werte der x-Achse a, b, c, … nennst, vergibt Excel auch eine fortlaufende Nummer.
Du hast eine Funktion vergessen:
=ISTTEXT
Sie liefert WAHR!
Ich erhalte deine Zelle auch, indem ich in eine Zelle
=““
Schreibe, die Zelle kopiere und dann als Wert einfüge …
dachte mir, ich melde mich mal wieder mit einem kleinen Office-Phänomen. Bin gespannt, ob du das schon gesehen hast (in der Regel ist die Antwort ja, aber man weiß ja nie 😉 ).
Ich habe auf einer PowerPoint-Folie ein paar simple Ring-Diagramme und ich möchte keine Führungslinie zur Datenbeschriftung. So sieht die Folie in diesem Moment aus:
Jetzt speichere ich die Datei, schließe sie, und wenn ich sie öffne, dann wandern bei 4 der 6 Diagramme wieder von selbst die schwarzen Führungslinien der Datenbeschriftung hinein.
Ich krieg diese nicht raus.
Versucht habe ich:
Rechtsklick auf die Führungslinie, Entf. Gedrückt
Datenbeschriftung formatieren -> Häkchen bei Führungslinie entfernen
Bauerntrick, Farbe der Führungslinie auf weiß mit 100% Transparenz -> Sie kommt als schwarze Linie wieder
Diagramm ohne Linie als Formatvorlage abgespeichert -> Diagrammtyp ändern -> die eben gespeicherte Vorlage ausgewählt
Mit VBA und .FullSieriesCollection(1).HasLeaderLines = False
Und jetzt fällt mir nix mehr ein. 🙂
Haste das schon mal gesehen?
Danke dir und viele Grüße, Dominic
####
OK, kurz drauf hab ich dann die Lösung gefunden:
„im Kleinen“: Die Datenbeschriftung mit der Maus soweit nach oben schieben, bis die Linie von selbst verschwindet
„im Größeren“, d.h. es gibt hunderte solcher Folien mit eben diesen Diagrammen: mit „.FullSeriesCollection(1).Points(1).DataLabel.Left = XX“ sowie „.Top = “ jeweils eine Position eingeben, bei der die Linien verschwinden und die trotzdem relativ mittig ist. Und das dann eben über zwei For-Each Schleifen für die ganze pptx durchführen.
Trotzdem strange. 😉
Viele Grüße, Dominic
####
Hallo Dominic,
ich muss immer nur ein bisschen warten, dann lösen sich die Probleme von alleine. Beziehungsweise: die Fragenden finden selbst eine Lösung.
Ich glaube, ich habe zu den (heißen die wirklich so?) Führungslinien noch keinen Artikel geschrieben. Und stimmt: DAS ist mir auch aufgefallen, dass diese Teilchen störrisch sind im Sinne von anzeigen und verschwinden. Allerdings: ich verwende sie recht selten und wenn (wie du in Punkt 1) schiebe ich so ein bisschen hin und her bis sie verschwinden. Den Algorithmus – ab wann sie auftauchen und wann nicht, kenne ich nicht.
Wenn ich alle IN EINEM Diagramm (ich weiß, ich weiß, du hast mehrere Diagramme) die Linien ausblenden will, markiere ich sie alle:
Eine hübsche Frage in der letzten PowerQuery-Schulung. Ich habe einen Moment überlegen müssen.
Die Aufgabe: wir exportieren aus unserer Datenbank regelmäßig eine Liste, die wir weiterverarbeiten müssen. Allerdings benötigen wir nicht alle Spalten. Dummerweise ändern sich die Spaltennamen regelmäßig … Wie kann ich nur die Spalten behalten, die mit „p_“ beginnen?
„Oder“, schob die Kollegin hinterher – alle Spalten, deren Überschrift eine Zahl (oder keine Zahl) enthalten …:
So schwer kann das doch nicht sein, oder? Alle Feldnamen, die mit „p_“ beginnen …:
Ich brauche die Überschrift. Man kann sie „extrahieren“, indem man alle Daten löscht:
Danach die Überschriften als erste Zeile verwendet und diese Zeile vertauscht (transponiert):
Nun kann man die Überschriften mit „p_“ filtern und in eine Liste konvertieren:
Ich nenne diesen Schritt „Selektierte_Ueberschrift“.
Man wäre auch mit einer Zeile M zu diesem Ergebnis gelangt:
Table.ColumnNames
Der Rest ist klar: in Tabelle konvertieren, filtern, in Liste konvertieren.
Und wie verwendet man diese Liste als Filter?
Zunächst benötigt man die Liste. Mit fx kann ein Bezug auf die Tabelle hergestellt werden:
Bleibt noch die Antwort auf die Frage: „und wie entferne ich alle Spalten, die Zahlen (Ziffern) enthalten?“
Ohne M könnte man einen Filter mit zehn Kriterien anlegen:
enthält nicht 1 und enthält nicht 2 und enthält nicht 3 … – ein bisschen Klickarbeit …
Aber durchaus machbar für jede und jeden – auch ohne Programmierkenntnisse. Und mit M? – Nun – die Antwort auf die Frage: „Wie baue ich eine Schleife und lösche alle Zeilen, die nicht 1 und nicht 2 und nicht 3, … enthalten“ überlasse ich der geneigten Leserin und dem geneigten Leser!
Auf alle Fälle waren die Teilnehmerinnen der Schulung zufrieden.
Ich wohne in der Albert-Roßhaupter-Straße. Eigentlich müsste sie – nach neuer deutscher Rechtschreibung – Albert-Rosshaupter-Straße heißen. Aber der Name des SPD-Politikers wurde nicht geändert. Sei’s drum. Was passiert allerdings bei der Suche in Excel von Wörtern mit „ß“ und „ss“?
Die Funktion SVERWEIS unterscheidet; XVERWEIS allerdings nicht:
Erstaunlich! Umso erstaunlicher ist es, dass im Spanischen weder zwischen „n“ und „ñ“ unterschieden wird:
Im spanischen Excel werden auch nicht die Vokale mit und ohne Akzent unterschieden:
nicht bei BUSCARV (SVERWEIS) und nicht bei BUSCARX (XVERWEIS). Sehr erstauntlich.
Die Frage ist gut – die Antwort leider nicht befriedigend.
Eine Liste von Namen und eine Liste von Abteilungen.
Soweit so gut – die Zuweisung kann per Datenüberprüfung erfolgen:
Allerdings: eine Person kann für mehrere Abteilungen arbeiten (eine klassische n : m-Beziehung). Die Namen sollen untereinander aufgelistet werden. Also so:
Leider kann man über die Liste der Datenauswahl nur einen Eintrag auswählen und keine Mehrfachselektion vornehmen. Auch mit einer Formel funktioniert es nicht: „addiere zu dem vorhandenen Wert einen weiteren hinzu“ – das wäre ein klassischer Zirkelbezug.
Meine Lösung war folgende: Neben der Liste werden die einzelnen Einträge ausgewählt:
Die ausgewählten Einträge werden darunter zu einer Zeichenkette verkettet:
Die Funktion
=TEXTVERKETTEN(ZEICHEN(10);WAHR;H2:H10)
löst dieses Problem.
Wichtig ist hierbei, dass der Textumbruch eingeschaltet ist, sonst sieht man den Effekt nicht.
Diese Zelle kann nun kopiert und als Wert in eine andere Zelle eingefügt werden.
Die Idee ist gut – sie funktioniert nur leider nicht.
Ein Teilnehmer einer Excelschulung möchte eine fortlaufende Reihe erzeugen. Er möchte, dass „Lücken übersprungen“ werden und dass die Reihe bequem fortgesetzt werden kann.
Kein Problem, oder:
Die Formel
=WENN(B2="";"";MAX($A$1:A1)+1)
hilft hierbei.
Damit unter der Liste neue Daten mit einer fortlaufenden Nummer eingetragen werden können, wandle ich die Liste in eine (intelligente) Tabelle um:
Ein neuer Name:Lücke und ein weiterer Name:
Klappt.
Wird eine Zeile gelöscht:
funktioniert der Mechanismus hervorragend:
Jedoch: wird eine Zeile eingefügt:
Dann versagt der Mechanismus leider:
Was man feststellen kann, wenn man einen Namen einträgt:
Kann man sich in dieser Tabelle, welche die Suchergebnisse im Kalender zeigt, eine Spalte zum Anzeigen des Antwortstatus darstellen lassen?
Meine Antwort:
schöne Frage, Herr L.,
Sie möchten die Besprechungen von Outlook durchsuchen und von jeder gefundenen Besprechung die Liste der eingeladenen Personen und deren Antwort sich anzeigen lassen?
Sie können sich die Liste der eingeladenen (erforderlichen) Teilnehmer anzeigen lassen und Ihren eigenen Besprechungsstatus; aber ich finde kein Feld für den Antwortstatus der eingeladenen Personen.
Ich sehe schon – bei all diesen Fragen – da fehlt noch viel in Outlook.
Schöne Frage zu Access. Dort kann man Tabellen – auch mit deren Verknüpfungen – leicht in eine XML-Datei exportieren – die Einstellungen sind komplexer als beim Export Excel nach XML:
Allerdings – so lautet seine Frage: wie kann man den Namen des Wurzelelements dataroot ändern?
Er hat recht – ich finde keine Einstellung hierzu. Also doch per Hand oder mit ein paar Zeilen VBA-Code …
Ich weiß, dass es schwierig ist einen Text von einer Sprache in eine andere zu übersetzen. Auch noch, wenn die Zeit drängt. Dennoch: Hilfetexte sind auch ein Aushängeschild. Für Microsoft. Die ihre Texte automatisch – besser: halbautomatisch – übersetzen. Wohl, ohne dass ein (deutschsprachiger) Mensch darüber schaut. Und so habe ich auch schon einige Male gespottet. Auch Josef reibt sich verwundert die Augen und berichtet:
„Grüß dich Rene!
Gerade hatte ich ein sehr amüsantes Gespräch mit einem Kollegen 🙂
Er wollte eine Excel Formel haben und hat – ganz vorbildlich – die Excel Hilfe konsultiert. Leider war die wenig hilfreich, also rief er mich an.
Die Formel sollte ein Datum berechnen, ausgehend von einem Startdatum + X Monate. So weit so gut…
Kollege ganz stolz: „Guck ich mach genau das, was da steht: EDATE(…“
Ich: Stop! EDATE? Das muss EDATUM heißen. Hast Du etwa eine englische Internetseite gefunden?
Kollege: Neeee! Ich bin doch nicht doof! Ist die deutsche Microsoft Hilfe Seite!
Mit EDATUM(Startdatum;Dauer) hats wunderbar funktioniert.
Erst dachte ich: Naja… maschinell übersetzter Hilfe-Artikel… wird halt der Screenshot englisch sein, schade!
Aber nein! Das Ding ist eine fröhliche Mischung aus Englisch und Deutsch 😛
Wir haben herzlich gelacht! 🙂
Vermutlich hat da jemand versucht zu übersetzen, aber dann war plötzlich Zeit für Feierabend…
Im Text steht (mehrfach) EDATE Die Beispiel-Formel im Text hat o Deutsche Datumsschreibweise 15.05.19 o und ein Semikolon als Trenner zwischen den Parametern o aber als Rückgabewert ein Datum in amerikanischer Schreibweise (4/15/19) Im Screenshot o Ist die Formel in der Bearbeitungszeile englisch, inklusive Komma als Trenner o Die Spaltenbeschriftungen und Monatsnamen sind aber deutsch Unterhalb des Screenshots wirds dann ganz verrückt: o In Schritt 3 steht „Geben Sie =EDATE(A2;B2) in Zelle C2 ein,… “ Das wird weder in einem deutschen noch in einem amerikanischen Excel funktionieren. Entweder EDATE und Komma oder EDATUM und Semikolon In der Excel Hilfe zur EDATUM() Funktion ist es besser. Da steht nur einmal EDATE statt EDATUM in der Überschrift, aber sonst stimmts! Viele Grüße und bis zum nächsten (online) Excel Stammtisch! Josef“
Sehr schöne Frage: wie kann ich die bedingte Formatierung so verwenden, dass Mails, die aus meiner Firma kommen, in einer Farbe dargestellt werden, Mails von extern in einer anderen Farbe.
Ich wüsste nicht, wie man das einstellen kann. Über die bedingte Formatierung kann ich nur eine Person (oder mehrere Personen) einstellen; jedoch nicht „intern“ und „extern“.
Schade, denn beim Abwesenheitsassistenten habe ich diese Möglichkeit:
Outlook ist leider (auch hier) nicht konsistent.
Nachtrag: meine Kollegin Angelika Meyer hat die Idee, dies mit einer Regel zu überprüfen. Immerhin, man kann dort alle Mails, deren Absender in „meinem“ Firmenadressbuch gefunden werden, mit einer Kategorie belegt werden und damit mit einer Farbe versehen werden:
Excel-VBA-Schulung. Wir programmieren eine eigene Funktion. Als Übung gebe ich auf eine Funktion zu schreiben, welche die Hypotenuse in einem rechtwinkligen Dreieck berechnet.
Also:
Ich tippe die Lösung, die ich mit einem „Quick & Dirty“ kommentiere:
Ich verlasse die Codezeile:
Ein Fehler ist die Folge. Darf man nicht die Funktion Sqr (Wurzel) aufrufen und im Aufruf rechnen?
Okay – dann „sauberer“ in zwei Zeilen:
Schon wieder ist die Zeile
c = a^2 + b^2
in der Funktion
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a^2 + b^2
Hypotenuse = Sqr(c)
End Function
falsch. Es dämmert mir. Richtig: das Caret-Zeichen (^) – der Zirkumflex – darf nicht direkt hinter die Variable geschrieben werden, da a^ zum Erstellen von Long Long-Datentypen in einer 64-Bit-Umgebung verwendet wird. Man muss ein Leerzeichen zwischen Variable und dem „Dach“ ein Leerzeichen schreiben:
Also:
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a ^ 2 + b ^ 2
Hypotenuse = Sqr(c)
End Function
oder in der Kurzform:
Function Hypotenuse(a As Double, b As Double) As Double
Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End Function
Oooch, Leute – versteckt doch die Sachen nicht so gut! Sonst muss ich so lange suchen.
Ich bespreche mit den Mitarbeiterinnen und Mitarbeitern einer Firma eine große Excel-Formel und deren Anpassungen. Die Formel liefert entweder „nicht erfüllt“ (und zeigt Schriftfarbe und Hintergrund Rot) oder „erfüllt“ in grüner Farbe:
Ich schaue die Formel genauer an: Die Formel liefert 0 (das entspricht FALSCH):
oder 1 (also WAHR):
Angezeigt wird aber Text und Farbe. Um die Funktion herum wurde keine WENN-Funktion gebaut, welche die Texte anzeigt. Die Farbe entstammt sicherlich der bedingten Formatierung:
Die Registerkarte „Ausfüllen“:
Die Registerkarte „Schrift“:
Die Registerkarte „Zahlen“:
Ich bin erstaunt. Ich hätte ein benutzerdefiniertes Zahlenformat erwartet, das hier verwendet wurde. Woher kommt nun der Text?
Ich habe eine Weile überlegt. Dann fiel es mir ein: man kann Text als benutzerdefiniertes Zahlenformat im allgemeinen Zahlenformat einstellen. Heureka – und DAS ist des Rätsels Lösung:
Das Zahlenformat lautet:
[=1]"erfüllt";[=0]"nicht erfüllt"
Mein Appell lautet: Versteckt die Sachen doch nicht so gut! Andere Menschen finden Sie nicht mehr! Oder suchen sehr lange.
ich versuche gerade eine PowerQuery-Auswertung aus den Interviewfragebogen zu erstellen.
Ich erhalte allerdings die Fehlermeldung „Die Konvertierung in Number war nicht möglich.
Was mache ich da falsch?“
Was mache ich mit so einer Mail? Richtig: ich schlage vor, mir das Ganze über teams anzusehen. Und tatsächlich:
Okay. Langsam. Von vorne bitte. Können wir uns das Ganze mal bitte in Ruhe ansehen? Was machen Sie?
In einem Ordner befinden sich mehr als 50 Excelmappen:
Jede dieser Mappen hat folgenden Aufbau:
In Spalte A befindet sich in jedem Formular eine Nummer der Form 0., 1., 2., …
Aus einigen dieser Gruppen sollen Informationen ausgelesen werden. Diese Informationen befinden sich in Spalten rechts daneben. Soweit so gut – PowerQuery ist das richtige Werkzeug hierfür. Wir schauen uns das Ganze an – Schritt für Schritt:
Schritt: Leere Arbeitsmappe. Daten / Daten abrufen und transformieren / Daten abrufen / Aus Datei / Aus Ordner
2. Schritt. Der Ordner wird ausgewählt; die Daten werden transformiert.
3. Schritt: Unterordner werden ausgeschlossen; andere Dateitypen ebenso:
4. Schritt: In der Spalte „Content“ befindet sich der Inhalt. Da die Spalten alle den gleichen Aufbau haben, kann man die anderen Spalten löschen und diese Spalte „entpacken“:
Da alle Dateien den gleichen Aufbau und das gleiche Tabellenblatt haben, stellt dies kein Problem dar:
Das Ergebnis:
Da Informationen aus bestimmten „Gruppen“ geholt werden, wird die erste Spalte über Transformieren / Ausfüllen „nach unten gezogen“:
Einige Spalten werden gelöscht. Aus der ersten Spalte werden einige der benötigten Spalten selektiert:
Das Ergebnis wird zurück nach Excel geschrieben (Start / Schließen & Laden / Schießen & Laden in). Obwohl die Daten in Powerquery korrekt angezeigt werden:
ist die Fehlermeldung die Folge:
[DataFormat.Error]. Die Konvertierung in „Number“ war nicht möglich.
Ich stutze. Zurück zu PowerQuery. Vielleicht ist „irgend etwas“ in der ersten Spalte?!? Es sieht nicht so aus:
Aber: „Die Liste kann unvollständig sein.“ Ich lasse mir über Ansicht die „Spaltenqualität“ anzeigen:
Kein Fehler in der ersten Spalte!?!
Wirklich nicht?
Wir wissen, dass PowerQuery zu Beginn nur 1.000 Zeilen auswertet. Bei 50 Formularen x zirka 150 Zeilen sind das 7.500 Zeilen. Okay – ich lasse ALLE Zeilen auswerten, indem ich auf der Statuszeile von 1.000 auf „alle“ wechsle:
Und tatsächlich: JETZT lautet die Beschriftung der Zeile „Spaltenqualität“
Unerwarteter Fehler.
Aha!
Ich gehe auf die Suche – Schritt für Schritt zurück. Schon bald ist klar, dass die Häufigkeit der Fehler unter 1% liegt:
Der Fehler tritt auf, als der Typ geändert wird. Moment – DAS habe ich doch gar nicht gemacht:
Richtig: in Datei / Optionen und Einstellungen / Abfrageoptionen lautet die Grundeinstellung:
Spaltentypen und -überschriften für unstrukturierte Quellen immer erkennen. Und richtig: Das produziert den Fehler:
[DataFormat.Error]
Aha – diese Einstellung bewirkt, dass aus 0., 1., 2., … die Zahlen 1, 2, 3, … werden. Das heißt: in einer der Dateien befindet sich wahrscheinlich in Spalte A eine andere Informationen.
Welche Datei? Zurück zum Anfang:
Ich entferne die erste und die zweite Spalte (den Dateinamen) nicht:
Bevor der Datentyp geändert wird, lasse ich mir alle Inhalte anzeigen:
und stelle fest, dass in einer (oder mehreren) Zellen ein Punkt vorhanden ist:
Da ich die Dateinamen „sehe“, kann ich die Spalte in den Datentyp „Text“ konvertieren und den Übeltäter filtern:
Als Text erzeugt der Punkt kein Problem, allerdings bei der (automatischen) Umwandlung in Zahl.
Die Lösung liegt auf der Hand: entweder man löscht den Punkt in PowerQuery raus oder man geht auf die Suche in der Datei:
Und dann funktioniert die Zusammenfassung problemlos:
Fazit: Vermeiden Sie – wenn möglich – die automatische Datenkonvertierung.
Verwenden Sie ALLE Daten bei der Fehlersuche.
Verwenden Sie die Werkzeuge der Registerkarte Ansicht, also: Spaltenqualität, Spaltenprofil und Spaltenverteilung.
In einer gespeicherten Datei befindet sich eine Liste von Daten. Der Bereich wurde „Quellnymphen“ genannt.
Auf einem zweiten Tabellenblatt befinden sich Dropdownlisten (Datenüberprüfungen), welche auf die Liste über den Namen zugreifen:
Ich möchte nun beide Tabellenblätter in eine neue, schon gespeicherte (!) Datei kopieren. Da auf beiden Blättern sich eine intelligente Tabelle befindet, kann ich nicht beide Blätter markieren und kopieren:
Also einzeln. Zuerst das Blatt mit den Datenüberprüfungen und anschließend das Tabellenblatt mit den Quelldaten. Die Datenüberprüfung funktioniert und greift auf die Liste zu, die hinter den Namen liegt:
Schließt man allerdings die Quelldatei, wird die Datenüberprüfung noch angezeigt – ja – sie greift sogar noch auf die Namensliste zu:
jedoch: sie lässt sich nicht mehr öffnen!
Okay – noch einmal:
Ich kopiere erneut das Datenblatt in die andere, bereits gespeicherte Datei, anschließend das Blatt mit der Datenüberprüfung:
Das Ergebnis ist das Gleiche.
Der Namensmanager gibt Auskunft. Dadurch, dass zwei Blätter mit Namen (eines besitzt einen Namen, eines verwendet einen Namen) kopiert werden, wird nun zwei Mal ein Name angelegt: ein lokaler, der auf die andere Datei zugreift (?!?) und ein globaler, der aber nicht von der Datenüberprüfung verwendet wird.
Uff!
Ähnlich perfide gestaltet sich das Ganze, wenn Quelldatei und Zieldatei im gleichen Ordner liegen. Kopiert man die Tabellenblätter hinüber, speichert beide Dateien, schließt sie und öffnet die Zieldatei, lässt sich – wie oben beschrieben – die Liste der Datenüberprüfungen nicht mehr öffnen. Schließt man die Zieldatei erneut und löscht die Quelldatei, ist eine Meldung nach der verknüpften Datei die Folge:
Das bedeutet: Das Kopieren von Blättern wird hinfällig, wenn Bezüge auf Namen vorhanden sind!
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:
