Eine Liebeserklärung an MS Excel
Schöne Frage in der PowerPoint-Schulung: wo man die Option finden kann, mit der man Bilder innerhalb einer Form verschieben kann, also: zoomen, stretchen oder an die Form anpassen:
Die Antwort: diese Option gibt es nicht, aber wenn man das Werkzeug „Zuschneiden“ aktiviert, kann man das Bild unabhängig von der Form verschieben und skalieren.
Warum das Diagramm die Daten nicht korrekt darstelle, will die Teilnehmerin in der PowerPoint-Schulung wissen:
Als ich nachfrage, was sie gemacht habe, zeigt sie es mir: den Startbereich der Tabelle gelöscht, indem sie die Spalten markiert und gelöscht hat:
Ich rate ihr das Diagramm zu löschen und neu zu erstellen …
PowerPoint-Schulung. Der Klassiker: Warum kann ich mit dem Pinsel, also mit dem Werkzeug „Format übertragen“ eine der Formen nicht formatieren?
Die Antwort ist schnell gefunden: wird die Form markiert, ist die kontextsensitive Registerkarte nicht mit „Formformat“ beschriftet, sondern mit „Bildformat“. Beim Kopieren wurde die Form aus Versehen als Grafik eingefügt …
PowerPoint meets Excel
PowerPoint-Schulung. Wir schauen uns die Erstellung von Diagrammen in PowerPoint an. Eine Teilnehmerin fragt, wie sie die Überschrift ändern könne. Das gehe nicht, sagt sie.
Ich bitte sie, ihren Bildschirm zu teilen:
Ah – sie hat bei der Dateneingabe nach unten gescrollt …
Ich starte Excel und erhalte folgende Meldung:
Dieser Dateityp wird in der geschützten Ansicht nicht unterstützt.
Ich habe eine Weile gesucht, bis ich die Ursache gefunden habe. Irgendein Update von Excel kollidiert mit einem der installierten Add-Ins.
Hallo Herr Martin,
ich habe die Formel so angepasst, dass auf die richtigen Werte zugegriffen wird (d.h. es wird der Zinssatz verwendet, der als Drilldown-Liste angelegt wurde, s.u.). Leider kommt immer wieder eine Fehlermeldung, die ich nicht lösen konnte, auch nicht durch eine Internetrecherche. Den Code habe ich mit Notepade++ zusammengebaut und Ihnen angehängt. Das ist der Fehler (beim Komma):
Hallo Frau I.,
1. PowerQuery unterschiedet zwischen Groß- und Kleinschreibung.
Der Befehl lautet
Number.Power
(groß „N“, groß „P“)
2. Stimmt – ist mir später aufgefallen – ich habe die zweite Formel (P) vergessen. Und: NEIN: es gibt keine Barwert- oder andere finanzmathematische Funktion in PowerQuery
Liebe Grüße
Rene Martin
Nachtrag:
so könnte die Lösung aussehen:
Starten Sie den PowerQuery-Abfrageeditor.
Klicken Sie auf die Funktion fnBarwert.
Klicken Sie auf Ansicht / Erweiterter Editor.
Dort sehen Sie die Berechnung:
let
Barwert = (Zins as number, Restnutzdauer as number) as number =>
let
q = 1 + Zins / 100,
Ergebnis = (Number.Power(q , Restnutzdauer) - 1) / (Number.Power(q , Restnutzdauer) * (q-1))
in
Ergebnis
in BarwertIst der Zins bei Ihnen eine Zahl oder eine Prozentzahl – also 3 oder 3%? Ist die Restnutzungsdauer in Jahren oder Monaten?
Tragen Sie einfach die entsprechenden Zahlen links ein – rechts wird der Barwert berechnet. Korrekt?
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Nun muss ich eine weitere Spalten mit berechneten Werten aus anderen Spalten einfügen. Die neue Spalte soll den Barwertfaktor enthalten. Die Formel dazu:
Also =(((1 + [#“Verzinsung/LZ“] / 100) ^ [#“Restlaufzeit“] ) – 1) / (((1 + [#“Verzinsung/LZ“] / 100) ^ [#“Restlaufzeit“] ) *( (1 + [#“Verzinsung/LZ“] / 100) – 1 ))
Wie kann ich in Powerquery eine Potenz berechnen? Mit ^ geht es wohl nicht …
Oder gibt es bereits eine Funktion, die den Barwert berechnet?
Herzliche Grüße,
####
Hallo Frau I.,
Sie berechnen die Potenz mit Number.Power
Also für den Kapitalisierungsfaktor so:
Also = Table.AddColumn(#"Geänderter Typ", "Also", each (Number.Power([#"Verzinsung/LZ"], [Restlaufzeit]) - 1) / ( Number.Power([#"Verzinsung/LZ"], [Restlaufzeit]) * ([#"Verzinsung/LZ"] - 1)))Liebe Grüße Rene Martin
Lustige Frage in der letzten PowerQuery-Schulung:
Wenn ich den Datentyp über das Gebietsschema ändere (beispielsweise Englisch (USA), habe ich die Möglichkeit mit einem Klick auf Zahnrad-Symbol hinter dem Schritt den Schritt zu ändern:
Wenn allerdings für sehr viele Spalten der Datentyp geändert wurde, gibt es für DIESEN Schritt kein Zahnradsymbol. Was macht man nun, wenn einer der Datentypen einer Spalte falsch ist?
Die einfachste Möglichkeit: Man markiert die Spalte und ändert den Datentyp in den richtigen Typ. Dann wird dieses Element ersetzt.
Natürlich kann man auch in der Bearbeitungsleiste den Teil per Hand korrigieren:
Oder: man löscht den ganzen Schritt und erstellt ihn neu. Letztere Variante ist natürlich wenig sinnvoll …
Lieber Herr Martin,
haben Sie vielen Dank, das hat prima funktioniert!
Nun soll aber im Tabellenblatt ein Wert manuell eingegeben werden, auf den dann die Berechnung der Spalte zugreift (ähnlich der Eingabe bei den Filterkriterien). Geht das?
#####
Hallo Frau I.,
Wenn Sie Werte auslagern möchten, dann „ziehen“ Sie die Daten nach PowerQuery, wählen den korrekten Datentyp (Text oder Zahl) und machen ein Drilldown, so dass nur noch ein Wert übrig bleibt.
Erstellen Sie eine neue Spalte, rechnen dort zuerst mit einem „harten“ Wert (beispielsweise +5) und ersetzen dann die zahl durch Ihre Variable (hier: + tbl_Plus)
Hilft das?
Hallo Herr Martin,
haben Sie vielen Dank!
Ich habe ein bisschen damit herum probiert. Sofern ich bei einer Tabelle neue Spalten aus derselben Tabelle hinzufüge, klappt alle prime. Allerdings erhalte ich einen Fehler beim Hinzufügen einer Spalte in die Tabelle qry_Datenzugriff, die sich berechnen soll aus „Bodenwert“ (Spalte mit vielen Werten der Tabelle qry_Datenzugriff) mal „Zinssatz“ (Spalte der Tabelle tbl_Zinssatz mit nur einem Wert). Wie kann ich das lösen?
Hallo Frau Issel,
die ersten beiden Schritte sind richtig: Sie laden die Tabelle nach PowerQuery; Sie wandeln den Typ in Dezimalzahl (oder Prozentzahl) um.
Aber dann fehlt der Drilldown: Sie müssen den Wert der Zelle in einen Wert verwandeln. Klicken Sie mit der rechten Maustaste auf die Zelle und führen den Drilldown durch:
Das Ergebnis sieht so aus:
Kann in einer Zeile geschrieben werden:
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz]Und diese lange Formel können Sie nun in Ihrer Berechnung verwenden, also statt:
Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* 1.3)Schreiben Sie:
Table.AddColumn(#“Geänderter Typ“, „angemessener Zins“, each [#“Bodenwert €“]* Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="tbl_Zinssatz"]}[Content],{{"Zinssatz", type number}}){0}[Zinssatz] )Klappt das?
Liebe Grüße
Rene Martin
Lieber Herr Martin,
haben Sie vielen Dank, das hat prima funktioniert!
Nun habe ich eine neue Herausforderung: ich möchte weitere Spalten mit unterschiedlichen Berechnungen hinzufügen, bei denen neue Spalten auf Werte von zuvor hinzugefügte Spalten zugreifen. Das sollte möglich sein, vermute ich.
#####
Hallo Frau Issel,
zu Ihren Fragen: klar können sie mit einer berechneten Spalte weiterrechnen: Sie fügen eine benutzerdefinierte Spalte ein: MWST = [Netto] + 0.07
und rechnen damit weiter:
Brutto = [MWST] + [Netto]
Guten Tag Herr Martin,
ich möchte aus den Werten von 2 Spalten Werte für eine 3. Spalte berechnen lassen und damit dann weiter rechnen. Kann ich das in PowerQuery erreichen?
Bspw. im Dokument Report09f.xlxs die Werte der Spalte K mal die der Spalte L. Anschließend möchte ich darüber Min, Max, Mittelwert berechnen, analog zu den Spalten, die im Exportdokument schon vorhanden sind.
Viele Grüße,
####
Hallo Frau I.,
in PowerQuery können Sie über „Spalte hinzufügen“ / Benutzerdefinierte Spalte eine Berechnung hinzufügen. Geben Sie dort den Namen der neuen Spalte an und die Berechnung, indem Sie auf diese langen Feldnamen doppelklicken!
Diese Spalte wird ans Ende der Tabelle gesetzt; Sie können sie schnell (über das Kontextmenü) an den Anfang verschieben:
Und dann per Hand etwas nach rechts:
Für die Aggregatfunktionen: erstellen Sie einen Verweis auf die Tabelle
Markieren die Spalte und wählen aus Transformieren / Statistiken die gewünschte Funktion aus.
Hilft das?
Liebe Grüße
Rene Martin
Irgendwie doof!
In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:
Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:
Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:
Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:
Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?
Ich weiß es nicht?
Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?
Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?
Irgendwie doof!
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.
Schöne Frage in der letzten PowerQuery-Schulung: wo befindet sich das (auch Excel bekannte) Symbol, das erlaubt nicht nur eine Spalte zu sortieren, sondern nach mehreren:
Die Antwort: ein SYMBOL hierfür gibt es nicht – man muss die Spalten in der gewünschten Sortierreihenfolge anklicken und sortieren, beispielsweise zuerst Ort; innerhalb eines Ortes (Aachen) nach der PLZ, innerhalb einer PLZ (beispielsweise 52062) nach der Straße, …
PowerQuery quittiert die Mehrfachsortierung mit dem Befehl
= Table.Sort(#"Geänderter Typ",{{"Ort", Order.Ascending}, {"Plz", Order.Ascending}, {"Strasse", Order.Ascending}})Ähnlich wie SQL:
SELECT *
FROM Kunden
ORDER BY Kunden.[Ort], Kunden.[Plz], Kunden.Strasse
Der Teilnehmer war zufrieden.
Hallo Herr Martin,
Wie kann ich denn in einer Wordtabelle zwei Uhrzeiten berechnen? Beispiel: A2 und B2 sind als Feld mit Datum HH:mm definiert. Bei C2 kommt aber leider nie 01:30 als Ergebnis raus. A2 = 12:00 B2 = 13:30 C2 = Hier soll das Ergebnis stehen von der Rechnung B2 – A2
Hallo Herr D.,
Sie haben recht, Herr Gauger, sowohl bei Berechnungen in Feldfunktionen als auch in Tabellen kann Word nicht mit Uhrzeiten rechnen. Das Ergebnis ist ein krudes Gemisch aus Stunden und Minuten, also unbrauchbar. Schade!
Hallo Rene.
Mir ist folgende Methode bekannt um in Word mit Uhrzeiten zu rechnen.
Dazu muss in den beiden Textformatfeldern welche die Uhrzeiten enthalten bei „Optionen für Textformatfelder“ eine Textmarke eingetragen werden.
Dann wird in die Zelle in der das Ergebnis der Uhrzeitberechnung angezeigt werden soll, folgendes FELD eingetragen.
Wenn in den Textformularfeldern die Box „beim Verlassen berechnen“ angekreuzt wird, kommt es zu einer Aktualisierung auch des REF-Feldes. Scheinbar werden dann alle Felder im aktiven Dokument (außer denen in den Kopf- oder Fußzeilen) aktualisiert.
Salü
Ernst
Hallo Rene
Die Darstellung als HH:mm hat mir keine Ruhe gelassen., so dass ich doch noch eine (wohl sehr umständliche) Methode gefunden habe.
| (Uhrzeit) ab | (Uhrzeit) bis | Differenz (Minuten) | Differenz (HH:mm) |
| 11:58 | 13:05 | 67 | 01:07 |
| 12:00 | 13:15 | 75 | 01:15 |
| 14:49 | 16:00 | 71 | 01:11 |
Folgende Feldfunktionen stehen in den Berechnungsfeldern
C2:
{={={Z1S2\@“H“}*60 +{Z1S2 \@ „m“}} -{={Z1S1\@“H“}*60+{Z1S1\@ „m“}}}
C3:
{={={Z2S2\@“H“}*60 +{Z2S2 \@“m“} }-{={Z2S1\@“H“}*60+{Z2S1\@“m“}}}
C4:
{={={Z3S2\@“H“}*60+{ Z3S2\@ m“}}-{ ={Z3S1\@“H“}*60+{Z3S1\@“m“}}}
D2:
{ IF „{ ={ Z1S2\@“m“}-{ Z1S1\@“m“} }“ <„0″ {={ Z1S2\@“H“}-{ Z1S1\@“H“}-1\#“00″} { ={Z1S2 \@“H“}-{Z1S1 \@“H“} }\#“00″}:{ IF „{ ={Z1S2 \@“m“}-{Z1S1 \@“m“} }“<„0″ { =60+{Z1S2 \@“m“}-{Z1S1 \@“m“}\#“00″} { ={Z1S2 \@“m“}-{Z1S1 \@“m“} }\#“00″}
D3:
{ IF „{ ={ Z2S2\@“m“}-{ Z2S1\@“m“} }“ <„0″ {={ Z2S2\@“H“}-{ Z2S1\@“H“}-1\#“00″} { ={Z2S2 \@“H“}-{Z2S1 \@“H“} }\#“00″}:{ IF „{ ={Z2S2 \@“m“}-{Z2S1 \@“m“} }“<„0″ { =60+{Z2S2 \@“m“}-{Z2S1 \@“m“}\#“00″} { ={Z2S2 \@“m“}-{Z2S1 \@“m“} }\#“00″}
D4:
{ IF „{ ={ Z3S2\@“m“}-{ Z3S1\@“m“} }“ <„0″ {={ Z3S2\@“H“}-{ Z3S1\@“H“}-1\#“00″} { ={Z3S2 \@“H“}-{Z3S1 \@“H“} }\#“00″}:{ IF „{ ={Z3S2 \@“m“}-{Z3S1 \@“m“} }“<„0″ { =60+{Z3S2 \@“m“}-{Z3S1 \@“m“}\#“00″} { ={Z3S2 \@“m“}-{Z3S1 \@“m“} }\#“00″}
Die zusammengesetzte Feldfunktion zur Berechnung der Zeitdifferenz ist sehr umständlich. Allerdings ist die Funktion immer die gleiche, so dass sie als Schnellbaustein abgespeichert werden kann. Einzige die angesprochenen Textmarken müssen jeweils geändert werden.
Salü Ernst
Hallo Herr Martin,
warum findet Excel Costa Rica nicht? Es gibt Costa Rica!
Genauer: ich habe mit einer Suchformel
=INDEX($F$2:$F$114;VERGLEICH(A9;$G$2:$G$114;0))einen Wert gesucht. Alle Werte werden gefunden – nur nicht Costa Rica:
Zuerst vermutete ich ein Leerzeichen hinter einer der beiden Wörter. Die Schreibweise ist korrekt. Dann vermute ich das Leerzeichen als Übeltäter. Und so ist es auch: die Funktion
=CODE(TEIL(G23;6;1))liefert den Wert 160 (geschütztes Leerzeichen) und nicht 32 (Leerzeichen), wie erwartet:
Auf mein Nachfragen erfahre ich: „… ich habe die Liste doch aus wikipedia kopiert. Und ich habe sie explizit als Wert eingefügt – OHNE Formatierung!“ Das genügt leider nicht … wikipedia verwendet an einigen Stellen geschützte Leerzeichen und bedingte Trennstriche. Das macht das Weiterverrbeiten solcher Listen in Excel manchmal mühsam. Böses Excel …
Hallo Rene,
ich hoffe das du bei diesen sommerlichen Temperaturen einen guten und entspannten Tag hast.
Zurzeit beschäftige ich mich mit der Ribbon-Programmierung in MS-Office. In Word gibt es ein Button Schließen/Alles Schließen, mit der MsoID = „FileCloseOrCloseAll“. Klickt man diesen Button an, wird das aktuelle Dokument geschlossen. Wird beim Anklicken die Umschalttaste gedrückt, werden alle offenen Dokumente geschlossen.
Wie bekomme ich eine solle Funktionalität bei einem benutzerdefinierten Button hin. Ich möchte zwei unterschiedliche Makros aufrufen, je nachdem ob beim Anklicken die Feststelltaste gedrückt oder nicht gedrückt ist.
Hast du eine Idee?
Liebe Grüße und
Salü
Ernst
Hallo Ernst,
ich glaube nicht, dass das geht. Die Steuerellemente haben folgende Ereignisse:
| Ereignis | Beispiel |
| onAction (button) | Sub Prozedur(ByRef Control As IRibbonControl) |
| onAction (checkBox, toggleButton) | Sub Prozedur(ByRef Control As IRibbonControl, ByRef Pressed As Boolean) |
| onAction (dropDown, galley) | Sub Prozedur(ByRef Control As IRibbonControl, ByRef SelectedID As String, ByRef SelectedIndex As Integer) |
| onChange (editBox, comboBox) | Sub Prozedur(ByRef Control As IRibbonControl, ByRef Text As String) |
Das heißt: für die Buttons gibt es beim Aufruf einer Prozedur kein Parameter, der eine weitere Taste abfangen kann.
Auch bei den Methoden:
| Methode | Beschreibung |
| ExecuteMso | Führt das vom idMso-Parameter angegebene Steuerelement aus. |
| GetEnabledMso | Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement aktiviert ist. |
| GetImageMso | Gibt ein IPictureDisp-Objekt des vom idMso-Parameter angegebenen Steuerelementbilds zurück, wobei die Abmessung von Height und Width angegeben wird. |
| GetLabelMso | Gibt die Beschriftung des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück. |
| GetPressedMso | Gibt einen Wert zurück, der angibt, ob das vom idMso-Parameter angegebene Umschaltflächen-Steuerelement gedrückt wird. |
| GetScreentipMso | Gibt die QuickInfo des vom idMso-Parameter angegebenen Steuerelements als Wert vom Typ String zurück. |
| GetSupertipMso | Gibt eine MultiInfo zum vom idMso-Parameter angegebenen Steuerelement als Wert vom Typ String zurück. |
| GetVisibleMso | Gibt True zurück, wenn das vom idMso-Parameter angegebene Steuerelement sichtbar ist. |
Kann man mit GetPressedMso nur bei Toggle-Buttons prüfen, ober ein- oder ausgeschaltet wurde, aber nicht wie gedrückt wurde.
Ich kann diese Frage mal nächste Woche auf meinem Blog veröffentlichen – vielleicht weiß jemand eine Antwort …
Liebe Grüße
Rene
Hallo Rene.
Nach deinen Ausführungen bin ich auf die Idee gekommen folgendes in WORD auszuprobieren.
Bei zwei geöffneten Dokumenten habe ich im Direktbereich des VBA-Editors den Befehl
CommandBars.ExecuteMso(„FileCloseOrCloseAll“)
einzugeben und danach nur die Enter-Taste gedrückt.
Es wird die Schließen-Routine aufgerufen.
Wird allerdings die Shift- und die Enter-Taste gleichzeitig gedrückt, wird die AllesSchließen-Routine aufgerufen.
Daraufhin habe ich folgendes versucht:
Ich habe in ein Word-Dokument mit Hilfe des Office RibbonX Editor diese Sequenz eingefügt.
und im VBA-Bereich des gleichen Dokumentes folgendes.
Option Explicit
'API zum feststellen des Keyboard-Status deklarieren.
#If VBA7 Then
Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
Sub OnActionButton(control As IRibbonControl)
Select Case control.ID
Case "SaveAsDocxOrPdf": SpeicherAlsDocxOderPdf 'Neuer Umbruch
Case Else
MsgBox "Fehler in OnAction: '" & control.ID & "' nicht erkannt"
End Select
End Sub
Sub SpeicherAlsDocxOderPdf()
If Abs(GetKeyState(&H10) < 0) Then 'Abfrage ob Shifttaste gedrückt ist
CommandBars.ExecuteMso ("FileSaveAsPdfOrXps")
Else
CommandBars.ExecuteMso ("FileSaveAsWordDocx")
End If
End SubUnd es klappt. Wird das benutzerdefinierte Icon „Als DOCX oder PDF speichern“ angeklickt, erscheint das Formular „Speichern als Dokument ohne Makros“. Wird während des Anklicken des Icon die Shift-Taste gedrückt, erscheint das Formular „Als PDF oder XPS veröffentlichen“.
Salü
Ernst
Das ist sehr, sehr clever! DARAUF wäre ich nie gekommen.
LG Rene
Irgendwie doof.
Ich habe eine Datei, in der sich verschiedene Werte befinden. Diese Datei wird mit Kennwortschutz gespeichert:
Einige Werte dieser Datei werden in eine andere Datei verknüpft:
Sind beide Dateien geschlossen, wird die Zieldatei geöffnet, so fragt Excel danach, ob die Daten aktualisiert werden sollen. Danach erfolgt die Frage nach dem Kennwort der kennwortgeschützten, verknüpften Datei.
Wurde allerdings die verknüpfte Datei an andere Stelle kopiert, dort bearbeitet und wieder zurückgespielt, erkennt Excel dies – aufgrund des Kennwortschutzes – nicht. Eine Neuberechnung zeigt nicht die aktuellen Daten. Man muss die Originaldatei öffnen, um eine Aktualisierung der Daten zu erreichen …
Hallo Rene,
herzlichen Dank für deine Bemühungen! Das bringt mich ein großes Stück weiter. Leider funktioniert aber irgendeine Kleinigkeit noch nicht… Ich hab viel probiert, komm aber nicht auf den Fehler.
Expression.Error: Der Wert "2022" kann nicht in den Typ "Text" konvertiert werden.
Die Fehlermeldung verstehe ich nicht. Das Jahr, nach welchem gefiltert werden soll.
Hallo Nadine,
ist die Zahl 2023 in der Zelle als Text formatiert?
Und: was macht „geänderter Typ“? – in Text oder Zahl konvertieren?
Liebe Grüße
Rene
Hallo Rene,
genau, ich habe dann extra die 2023 in Text formatiert. Ursprünglich hatte ich es als Zahl, da kam allerdings auch diese Fehlermeldung, weshalb ich die 2023 dann in Text formatiert habe.
Hier die Schritte, welche ich in der Jahrestabelle ausgeführt habe:
Dort wo dann die Formel eingefügt wird, also dort, wo nacher nach diesem Jahr gesucht werden soll, sieht die Formatierung so aus:
Hallo Nadine,
Folgende Ursache: Ich vermute in deiner Zelle stehe die Jahreszahl als ZAHL – in meiner ersten Städtedatei hatte ich sie als Text formatiert.
Damit du auch einen Text erhältst, muss dein zweiter Schritt
= Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="SOP"]}[Content],{{"Column1", type text}}){0}[Column1]
verwendet werden
In deinem Code
= Table.SelectRows(Quelle, each (Record.Field(_ , Excel.CurrentWorkbook(){[Name="SOP"]}[Content]{0}[Column1]) <> null))Zusammengefasst: der in Excel eingetragene Wert in eine Zahl. Die Spaltenüberschrift jedoch ein Text. Irgendwann muss die Zahl in einen Text konvertiert werden!
Ich habe schon eine Weile überlegen müssen. Folgende Frage erreichte mich:
„Allerdings möchte ich nun in einer Spalte, die nicht fest definiert ist, filtern. Ist dies möglich?
Ziel: Ich möchte das es mir nur die Zeilen anzeigt, die in einer bestimmten Jahres-Spalte einen Wert haben.
Der Anwender des Tools sollte die Möglichkeit haben, ein Jahr einzugeben in einem bestimmten Feld.
Hier wählt der Anwender das Jahr aus. Daraufhin sollte in Power Query in der Spalte, mit der Bezeichnung 2026, nach Werten <> null gefilter werden. Im Screenshot würde dann nur noch die erste Zeile erscheinen, da in der Spalte 2026 nur in der ersten Zeile ein Wert enthalten ist. Ich hab schon viel rumprobiert und bekomm es nicht hin.“
Ich ziehe das Ergebnis des Filters (versehen mit dem Namen „Jahr“) nach PowerQuery und benennen die Abfrage „Jahr“. Sie sieht folgendermaßen aus:
= Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]Ich erstelle eine Spalte mit diesem Wert:
= Table.Column(#"Geänderter Typ", Jahr)Das Ergebnis:
Wird eine Spalte der Liste gefiltert, lautet der M-Befehl (beispielsweise für das Jahr 1950) folgendermaßen:
= Table.SelectRows(#"Gefilterte Zeilen", each ([1950] <> null))
Baue ich dort allerdings das Ergebnis der Abfrage ein, klappt die Filterung nicht mehr:
= Table.SelectRows(#"Gefilterte Zeilen", each (Jahr <> null))
Auch alle anderen Versuche greifen nicht – werde das Weiterverarbeiten der Tabelle oder Liste noch eine Hilfsspalte, bei der aus
= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if [1950] <> null then null else "x")ändert in
= Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each if Jahr <> null then null else "x")Schließlich – der Gedanke: [1950] muss durch
Record.Field(_, Jahr) ersetzt werden. Die ganze Zeile lautet laso:
= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Jahr) <> null))oder komplett:
= Table.SelectRows(#"Geänderter Typ", each (Record.Field(_, Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1]) <> null))Geht doch!
PS: Die rote Farbe im Kopf habe ich mit der bedingten Formatierung erzeugt. Man darf jetzt nur nicht die Aktualisierung vergessen.
Eine schöne Frage in der letzten PowerQuery-Schulung.
Eine Tabelle soll verändert werden. Im linken Bereich befinden sich Informationen (nennen wir sie „Metadaten“), im rechten Bereich in mehreren Spalten weitere Informationen zu diesen Metadaten.
Jede dieser Gruppen, bestehend aus jeweils drei Spalten, soll neben die anderen Daten geschrieben werden, so dass die Metainformation so oft auftaucht, wie Gruppen vorhanden sind. Dabei können beliebig vieler dieser Gruppen auftauchen.
Das Ziel:
Mein erster Gedanke:
Ich fasse mit dem Befehl „Spalten zusammenführen“ jeweils die einzelnen Spalten einer Gruppe zusammen:
Anschließend kann man diese Spalten entpivotieren
und danach am Trennzeichen (hier: „|“) teilen.
Aber: das Verfahren ist umständlich, weil (hier:) bei 17 Gruppen 17 Mal entpivotiert werden muss. Da die Anzahl der Gruppen variabel ist, ging ich auf die Suche, ob man das mit geschickten M-Befehlen (einer Schleife!) abkürzen und dynamisch halten kann.
Da fiel mit der Artikel von Hildegard Hügemann in die Finger:
https://www.office-kompetenz.de/inhalte-aus-spalten-auf-zeilen-verteilen-mit-power-query/
Und genau DAS ist die Lösung:
Zuerst muss man den „rechten“ Teil entpivotieren:
Anschließend werden die Überschriften benötigt in der Form A – B – C. Leider stehen sie hier als A1 – B1 – C1 – A2 – B2 – C2 – A3 – … Die Zahlen müssen entfernt werden. Man kann sie mit dem Assistenten „Spalte teilen“ und er Option „Nach Wechsel von Nicht-Ziffer zu Ziffer“ herauslösen:
DIESE (spätere Überschriftsspalte) wird nun pivotiert, wobei die Werte (letzte Spalte) natürlich nicht aggregiert werden (verbirgt sich in den „Erweiterten Optionen“):
Der Rest ist „Kosmetik“: Datentypen festlegen, Spalten löschen, leere Spalten entfernen (wegfiltern), Spalten umbenennen, …
Klasse!
Ein großes Dankeschön an Hildegard Hügemann für die Lösung – hier habe ich glatt „in die falsche Richtung gedacht“.
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:
Wer PowerQuery kennt, kennt sicher das Problem von IntelliSense:
Man beginnt etwas zu tippen, wundert sich über die angezeigten Vorschläge:
Man findet den korrekten Vorschlag, klickt darauf oder bestätigt mit der Tabulatortaste und: das Objekt wird zwei Mal genannt:
Ärgerlich!
Wyn Hopkins hat den Grund gefunden:
„The devil is in the dot.“
Man muss beide Befehl ohne Punkt schreiben, wobei Groß- und Kleinschreibung keine Rolle spielen! Dann klappt es. Ich bin begeistert:
Kennt ihr das? Eine Zelle bleibt schwarz. Hat keine Lust … Zum guten Glück verschwindet es sofort wieder …
Ich erhalte eine Mail.
„Ich komme leider mit der Fehlermeldung
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Details:
Key=[Record]
Table=[Table]
nicht weiter.“
Ich schaue mir das Ganze an. Was haben wir gemacht?
In einem Tabellenblatt werden drei Dateien aufgelistet und der Pfad, in dem sich diese Dateien befinden. Diese vier Zellen haben Namen – hier: Schweinchen1, Schweinchen2, Schweinchen3 und Pfad:
Über Daten / Daten abrufen / aus Datei greife ich auf eine der drei Dateien zu:
Die Datentypen werden nicht automatisch erkannt; übrig bleiben drei Schritte; das Ergebnis wird nach Excel zuzrückgegeben:
Eine der drei Zellen mit Namen wird über Daten / Daten abrufen / Aus Tabelle/Bereich in PowerQuery verwendet. Nach einem Drilldown erhält man den Inhalt der Zelle:
Dies wird für die übrigen Zellen wiederholt. Fügt man nun diese Variablen in den Befehl Excel.Workbook ein, so ist eine Firewall-Meldung die Folge:
Diese kann man umgehen, indem man den Code (Zugriff auf den Inhalt einer Zelle mit Namen) in eine Zeile schreibt:
Excel.CurrentWorkbook(){[Name="Schweinchen3"]}[Content]{0}[Column1]In der Codezeile
= Excel.Workbook(File.Contents(Pfad & Schweinchen1), null, true)müssen die beiden Variablen durch ihre Funktion ersetzt werden (was den Code nicht gerade lesbar macht):
= Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true)Klappt:
Dies wird auch für die anderen beiden Dateien durchgeführt, die anschließend in Excel geladen werden:
Die Hilfsabfragen Schweinchen1, Schweinchen2, … kann man getrost löschen.
So habe ich die Vorlage erstellt. Und nun kommt die Fehlermeldung:
Expression.Error: Der Schlüssel entsprach keiner Zeile in der Tabelle.
Ich begebe mich auf die Suche. Der Fehler taucht beim Zugriff auf das Tabellenblatt „Tabelle1“ auf. Nachgeschaut: bei einer anderen Datei heißt das Tabellenblatt „Sheet1“:
Also muss ich auch noch den „harten“ Namen entfernen. Ich mache es so:
#"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])und lösche die Zeile „Navigation“. Klappt:
Der ganze Code (jetzt: völlig unverständlich!):
let
Quelle = Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Pfad"]}[Content]{0}[Column1] & Excel.CurrentWorkbook(){[Name="Schweinchen1"]}[Content]{0}[Column1]), null, true),
#"Höher gestufte Header" = Table.PromoteHeaders(Quelle{[Item=Quelle{0}[Item],Kind="Sheet"]}[Data], [PromoteAllScalars=true])
in
#"Höher gestufte Header"Die übrigen zwei Abfragen werden analog angepasst – nun klappt alles!
Word-Schulung. Eine Teilnehmerin möchte ein „x“ mit einer Tilde dargestellt haben – das Symbol für Median.
Wir überlegen zusammen. Man könnte es mit einer Feldfunktion erzeugen:
Das Feld
{EQ \O(x;~)}schiebt die beiden Zeichen „x“ und „~“ ineinander. Damit die Tilde über dem Buchstaben steht, muss man sie höher formatieren – man findet dieses Attribut in den Zeichenformatierungen:
Das Ergebnis könnte man in der Autokorrektur oder als Schnellbaustein abspeichern.
Natürlich könnte man auch den Formeleditor heranziehen.
Schneller funktioniert es sicherlich, wenn man das Zeichen von einer Internetseite kopiert:
Schließlich werden wir sogar in Word fündig: es gibt bereits dieses Zeichen in der Autokorrektur. Man muss
x\tildeeintragen.
Achtung: dies funktioniert jedoch nur dann, wenn die Option „Mathematische Autokorrekturregeln in nicht mathematischen Bereichen verwenden“ aktiviert ist.
Nachtrag:
Hallo Rene,
Mir ist noch folgende Methode zur Erzeugung des Median-Symbol in WORD bekannt. Zuerst wird das x-Zeichen eingegeben (es kann auch jedes andere Zeichen sein) und danach wird bei gedrückter ALT-Taste die Zahlenfolge 771 auf der Zifferntastatur eingegeben.
Salü Ernst
Danke für den tollen Tipp, Ernst!
Und noch einer von Ernst:
Hallo Rene,
da diese Methode auf meinem Laptop nicht möglich ist, weil dieser keine Zifferntastatur aufweist, habe ich weiter gesucht und noch eine andere Methode gefunden.
Dabei können zwei verschiedene Reihenfolgen der Zeichenerstellung angewendet werden.
Nach Eingabe des x-Zeichen schreibt man den Wert 303 (was der Hex-Wert von 771 ist) markiert diesen Wert und drückt ALT-c.
oder
303 wird zuerst eingegeben, danach wird ALT-c, danach die Cursortaste links und dann das x-Zeichen gedrückt.
Statt dem x können alle möglichen anderen Zeichen (Buchstaben, Zahlen, Satzzeichen wie ?) und statt dem Wert 303 können auch andere Zahlenwerte verwendet werde. 305 ergibt z.B. einen großen Strich über dem Buchstaben.
Diese Methode hat den Vorteil, dass die Zifferntastatur nicht benötigt wird.
Salü
Ernst
*uff* noch eine Lösung:
Hallo Rene,
und der Witz ist, dass es eine weitere noch einfachere Methode gibt, die auch auf einen Laptop funktioniert.
In WORD kann jeder beliebige Unicode aufgerufen werden, indem u+ dann der Hex-Wert des Unicodezeichens eingegeben und danach ALT-c gedrückt wird. Dies gilt natürlich auch für die Zeichen der Unicode-Block „Kombinierende diakritische Zeichen“.
Somit kann z.B. das Medianzeichen durch die Kombination xu+303 und drücken von ALT-c erzeugt werden.
Der Unicode-Block der „Kombinierende diakritische Zeichen“ umfasst den Bereich Hex300 (Dez771) bis Hex36F (Dez879) und somit 112 Zeichen die mit allen möglichen Buchstaben kombiniert werden können.
Salü Ernst
Wordschulung.
In dem Artikel
habe ich darauf hingewiesen, dass in Word Ref-Felder, wenn sie sich in der Kopf- oder Fußzeile befinden, nicht automatisch aktualisiert werden. Und gestern kam erneut die Frage:
„Ich möchte in ein Worddokument Informationen eintragen und diese in der Kopfzeile anzeigen.“
Ich erkläre, dass man Formularfelder verwenden könnte und der Feldfunktion Ref darauf zugreifen könnte:
Allerdings werden sie nicht automatisch aktualisiert. Erst beim Drucken oder wenn man alles markiert und mit [F9] aktualisiert, sieht man in der Kopfzeile den Text, der ins Dokument eingegeben wurde.
Ebenso ungeschickt sind Textmarken, die mit der gleichen Feldfunktion Ref aufgerufen werden können. Auch hier wird nicht sofort aktualisiert, und es besteht die Gefahr, dass die Textmarke aus Versehen gelöscht wird:
Mir fällt eine dritte Lösung ein: Die Funktion Styleref wiederholt den Text einer Formatvorlage. Also könnte man eine Tabelle anlegen, denn dort wird eine Zelle als Absatz behandelt. Ihr wird eine (Absatz-)Formatvorlage zugewiesen (hier: Thema und ID).
Auf diese wird mit StyleRef verwiesen. Erstaunlicherweise muss ich den Bildschirm nach oben und unten scrollen, damit aktualisiert wird, aber dennoch: so sieht man in der Kopfzeile (oder Fußzeile) schnell den Text, der im Dokument eingegeben wurde:
*ups*
Die Aufgabe lautet: ich möchte die Anzahl der Zeilen wissen, in denen in mindestens einer der vier Spalten etwas steht. Oder Anzahl der Spalten, bei denen Wert Spalte A <> „“ oder Wert Spalte B <> „“ oder Wert Spalte C <> „“ oder Wert Spalte D <> „“.
Ich probiere. Mit & werden die vier Zellen verkettet:
Hier:
=tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos]Während die Funktion
=ANZAHLLEEREZELLEN(G2#)
die Zahl 3 ergibt, liefert
=ANZAHL2(G2#)die Zahl 26. Die vermeintlich leeren Zellen werden mitgezählt.
Die Formel darf folglich NICHT lauten:
=ANZAHL2(tblMusketiere[D’Artagnan]&tblMusketiere[Aramis]&tblMusketiere[Porthos]&tblMusketiere[Athos])Die Formel
=G6=""liefert WAHR, die Formel
=ISTLEER(G6)liefert FALSCH.
Eine Lösung könnte so aussehen: die Funktion FILTER liefert die korrekte Liste, beispielsweise so:
=FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>""))
Und die kann man zählen:
=ANZAHL2(FILTER(tblMusketiere[Datum];(tblMusketiere[D’Artagnan]<>"")+(tblMusketiere[Aramis]<>"")+
(tblMusketiere[Porthos]<>"")+(tblMusketiere[Athos]<>"")))Hallo Rene
Darf ich dich noch etwas Fragen?
Ich habe eine Excel Arbeitsmappe, mit welcher Offerten gerechnet werden.
Der Artikelstamm liegt in einer SharePoint Liste und die besagte Arbeitsmappe verfügt über mehrere Tabellenblätter bei welchem jeweils eine gefilterte Abfrage auf den zentralen Artikelstamm gemacht wird.
Die auf diese Weise gruppierten bzw. gefilterten Artikel werden mit einer Anzahl versehen, sodass Preise ermittelt werden können.
Das klappt soweit auch alles doch am Schluss möchte ich das Ganze so ausdrucken können, dass keine fixen Seitenumbrüche pro Tabellenblatt gemacht werden.
Die Kopf und Fusszeile sollten gemeinsam benutzt werden und pro Tabellenblatt müssten drei Wiederholungszeilen bestehen, dann jeweils n Zeilen mit Artikeln, aber eben nicht immer eine neue Seite. Meinst Du ist das machbar oder habe ich da wiederum ein Problem?
Hallo,
kurz und knapp,
die Antwort auf diese Frage findest du in den Tiefen meines Blogs: es geht nicht. Die Frage habe ich vor einigen Jahren erhalten, weil man in Lotus 1-2-3 mehrere Tabellen auf einem Blatt ausdrucken konnte. In Excel ist so eine fortlaufende Tabelle nicht möglich.
Sorry!
Liebe Grüße
Rene
Hallo Herr Martin,
ich möchte Sie nochmals belästigen, auch auf die Gefahr hin, dass ich wieder zu doof bin, um es selbst zu finden!
Ich möchte auf dem Excel-Arbeitsblatt in der Kopfzeile das Datum der Erstellung oder zuletzt geändert / zuletzt gedruckt einfügen, wie ich es aus Word kenne. (SAVEDATE / PRINTDATE) Ich habe in den Funktionen nur das „Aktuelle“ Datum gefunden. Selber etwas konstruieren kann ich als bloße Userin leider nicht. Können Sie helfen? Vielen Dank schon mal!
Hallo Frau H.,
das geht leider nicht.
In der Kopfzeile fehlen einige Dinge:
* Verknüpfung mit Inhalt einer Zelle
* Formeln
* weitere Feldfunktionen
…
Mit einem Makro kann man das lösen – wird aber auch umständlich: „woher hole ich die Information „zuletzt gedruckt“.
Sicherlich keine zufrieden stellende Antwort: ich würde das Datum per Hand in die Kopfzeile schreiben. Oder vielleicht über den Blattnamen, den man ja in der Kopfzeile anzeigen lassen kann.
Liebe Grüße
Rene Martin
Danke für die schnelle Antwort!
Wenigstens liegt es diesmal nicht an mir!!!
Alles Gute aus Düsseldorf!
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“
Eine schöne Frage in der letzten Schulung.
Ein Teilnehmer zeigt mir seine Tabelle. Sie hat sehr viele Spalten, in denen Informationszahlen stehen:
Seine Frage:
„Wenn ich eine Pivottabelle estelle (in der ich die vorkommenden Werte zähle), kann ich nicht die einzelnen Spalten in die Werte ziehen. Was muss ich tun?
Die Aufgabe: Die Orte werden in den Zeilen gruppiert, in die Statusangaben in den Spalten. Man kann nun eine Person in die Zeilen ziehen und sich in den Werten die Anzahl der Einträge anzeigen lassen:
Jedoch: sobald eine zweite Person hinzukommt, arbeitet die Pivottabelle nicht so wie gewünscht:
Da das Ziel war aus einer Pivottabelle ein Diagramm zu erzeugen, scheiden mehrere Pivottabellen aus.
Ich überlege: die Form der Tabelle ist unglücklich gewählt. Man darf die Informationen nicht in Zeilen und Spalten abtragen. Man muss die Tabelle entpivotieren. Hier bietet sich PowerQuery an:
Gesagt, getan – die Liste wird erstellt. Eine Pivottabelle erzeugt:
Genau SO wollte er es haben! Als Basis für ein Diagramm. Er war begeistert.
Zum Vatertag. Heute mal kein Excel. Sondern: Kochende Männer. Männer mit Partnerin. Flirtende Männer.
Gefunden in den Tiefen des Internets. Geschmunzelt.
Ich erhalte eine Mail mit folgender Frage:
„Können Sie mir eine Formel nennen, welche eine Formel in Werte verwandelt?“
Ich schmunzle und antworte ihn, dass man dies mit Kopieren / Inhalte einfügen als Werte erledigen kann:
Ich darf JavaScript unterrichten.
Ich erkläre, wie man eine Variable um den Wert 1 erhöht. Drei Möglichkeiten stellt diese Sprache zur Verfügung:
x = x + 1;x +=1;x++;Wir machen eine Übung. Ein Teilnehmer sagt, dass sein Programm nicht richtig rechne. Seine Variable würde nicht erhöht werden. Ich schaue es mir an:
x = x++;Richtig: zuerst geschieht die Übergabe, dann wird der Wert erhöht. Das heißt: die Variable verändert ihren Wert nicht. *ggrrrr*
Hallo Rene,
nun, seit langem, wieder mal was Fachliches.
Ganz was Einfaches:
In meinen Programmpaket gibt es unheimlich viele Variable. Und wenn was Neues dazu kommt, kommen neue Variable hinzu.
Ich habe bisher noch keine einfache und schnelle Lösung – wie findet man im Programm „nicht benutzte Variable“ ?
So „Tabula rasa“ ist gesucht. Einfach all diese Variablen ungesehen ganz schnell löschen….
Liebe Grüße Wolfgang
Hallo Wolfgang,
ja ich weiß – aber da hat der Uralt-Popel-Editor von VBA nichts zu bieten.
In Visual Studio werden nicht verwendete Variablen angezeigt
Ich lösche die Variablen in VBA und „kompiliere“. Oder manchmal suche ich auch im Code, ob sie noch verwendet wird.
Ist mühsam, ich weiß … Ich entschuldige mich nicht dafür *lach*
Liebe Grüße
Rene
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Die Möglichkeit, den Datentyp über ein Gebietsschema auszuwählen, ist hinlänglich bekannt:
(Randbemerkung: mit der Taste [F] gelangt man am schnellsten zu Englisch / USA)
Benötigt man das andere Gebietsschema mehrmals, kann man dies in den Optionen in den Regionalen Einstellungen der Arbeitsmappe festlegen:
Hinweis: Nicht verwechseln mit den Regionalen Einstellungen, welche die Sprache der Namen der Variablen (beispielsweise Gefilterte Zeilen, Geänderter Typ, Sortierte Zeilen, …) festlegt:
Danke, Martin, für diesen wertvollen Tipp.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Kennt ihr das Problem? Man erstellt eine Reihe von Abfragen:
schließt den Editor, aber aus Versehen lädt man die Abfragen nicht als Verbindung, sondern als Tabelle – padautz – schon hat man 20 (ungewünschte) Tabellen.
Abhilfe schafft in den Optionen die „Standardeinstellung zum Laden von Abfragen“. Wählt man dort die Option „benutzerdefinierte Standardeinstellung“ und deaktiviert alle Kontrollkästchen, so werden die Abfragen nicht als Tabelle in Excel eingetragen.
Danke, Martin, für diesen wertvollen Tipp.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Beim Importieren von Daten „erkennt“ PoweryQuery den Datentyp der Spalten. Das kann nervig oder lästig sein oder auch zu Fehlern führen:
Diese Option kann man deaktivieren:
„Spaltentypen und -überschriften für unstrukturierte Tabellen niemals erkennen.“
Danke, Martin, für diesen wertvollen Tipp.
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:
2. Die Zellen, in denen sich die Werte befinden, werden mit einem Namen versehen:
3. Die Werte sind das Ergebnis von Berechnungen von Array-Funktionen, beispielsweise FILTER oder SEQUENZ:
Danke, Martin, für diesen wertvollen Tipp.
Letzte Woche hat Martin Weiß (der tabellenexperte.de) auf unserem Excelstammtisch einige Tipps zu PowerQuery gegeben – damit Excel nicht mehr so nervt:
Lagert man in Excel einen Datenpfad in eine Zelle, kann man den Wert in PowerQuery verwenden, wenn man einen Drilldown erzeugt hat:
Verwendet man nun diesen Wert, also diese Variable, ist eine Firewall-Meldung die Folge:
Natürlich kann man in den Optionen diese Firewall-Einstellungen ausschalten. Oder man kann das Problem umgehen, indem man den Verweis auf die Excelzelle nicht in einer getrennten Abfrage belässt, sondern in die Formel einbaut, beispielsweise so:
= Folder.Files(Excel.CurrentWorkbook(){[Name="tblPfad"]}[Content]{0}[Pfad])Danke, Martin, für diesen wertvollen Tipp.
Hallo Herr Martin,
mittlerweile sehe ich schon die Zielgerade J.
Die Probleme mit dem Titel und dem Vorwort habe ich jetzt erledigt.
Ich hänge aber immer noch am Druckauftrag.
Anbei mal die aktuellen Codes:
[… es folgen einige Dutzend Codezeilen]
Folgende Fehler treten aber bei mir auf: Nachdem ich diese ProtectBefehle eingebaut habe kommt folgende Fehlermeldung:
Hallo Herr R.,
mir fehlt noch eine Info:
* öffnen Sie mal bitte Ihr VBA-Projekt (also die Word-Datei
* Wechseln Sie nach VBA
* Heben Sie den Schutz auf
* Klicken Sie auf den Menüpunkt Debuggen / Kompilieren In welcher Zeile entsteht der Fehler? Könnten Sie mir bitte diese Zeile oder auch die Zeilen „außenrum“ schicken.
Hallo Herr Martin,
Sie sind aber schnell… Meinen Sie das?
*lach*
DAS ist Falsch.
Es muss heißen:
ActiveDocument.Protect NoReset:=True
Zwischen Protect und NoReset muss ein Leerzeichen statt des Punktes stehen
Liebe Grüße
Rene Martin
PS: und dann noch einmal Debuggen, bitte!
####
Aber immer noch J
stimmt, Herr R., da habe ich doch mal einen Artikel darüber geschrieben.
Die Methode Protect verlangt unbedingt den Parameter Type, obwohl IntelliSense dies nicht anzeigt:
ActiveDocument.Protect NoReset:=True, Type:=wdAllowOnlyFormFields
Hallo Herr Martin,
ZÄHLENWENN (und SUMMEWENN) kenne ich. Damit kann ich die Anzahl der Zeilen bestimmen, die einem Kriterium genügen. ZÄHLENWENNS kenne ich auch. Damit kann ich zählen, wie oft mehrere Kriterien erfüllt sind, die mit einem logischen UND verknüpft sind.
Soweit so gut.
Aber wie kann ich eine Formel erstellen (gerne auch mit SUMMENPRODUKT), in der die Zeilen gezählt werden, bei denen in der ersten oder zweiten oder dritten Spalte nichts steht. Also: mindestens eine Spalte muss gefüllt sein.
Hallo Frau S.
ich würde die die drei Zellen verketten und wenn alle drei nicht leer sind, also <>““, dann würde ich diese zählen. Da Falsch = 0 und Wahr = 1, kann man die Summe verwenden.
beispielsweise so
=SUMME((C4:C15&D4:D15&E4:E15<>"")*1)Hallo Herr Martin,
vielen Dank für die pragmatische Lösung, gefällt mir 😉
Hallo Herr Martin,
ich muss zeitlich und gedanklich kapitulieren und gebe daher gerne folgendes Problem an sie weiter.
In einem Excel.xlsx wird Bezug genommen auf den Wert von einem „Berechnung Planerfüllung.xlsx“,
entweder direkt oder mit einer Rundungsformel ergänzt, z.B.
In diesen zahlreichen Bezügen wurde übersehen, dass der Wert nur übernommen werden darf, wenn er >0 ist.
Um nicht per Hand die zahlreichen Stellen zu ändern, wollte ich dafür eine Formel schreiben,
die den Inhalt dieser Zellen ändert in
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.
Guten Tag Herr Martin,
Ich habe ein Tabellenblatt, in dem jeden Tag neue Daten eingetragen werden und dann diese wieder gelöscht werden, da diese Daten mit einer Auswertung zusammenhängen. Da am nächsten Tag dort wieder neue Daten eingetragen werden müssen.
Und Power-Query aktualisiert ja im Normalfall nur die aktuellen.
Ich habe nun folgendes versucht wie James Baylay in folgendem Beitrag:
Die Schritte sehen so aus:
let
Quelle = Excel.CurrentWorkbook(){[Name="tblZusammenfassung"]}[Content],
#"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"ID", Int64.Type}, {"Schulz", Int64.Type}, {"Jackli", type number}, {"Marli", Int64.Type}, {"Jergli", Int64.Type}, {"Michal", Int64.Type}, {"Hans", Int64.Type}, {"Veitli", Int64.Type}}),
#"Angefügte Abfrage" = Table.Combine({#"Geänderter Typ", tblDieSieben}),
#"Entfernte Duplikate" = Table.Distinct(#"Angefügte Abfrage")
in
#"Entfernte Duplikate"Ich habe folgendes Problem. Wenn ich bei der Abfrage «tblZusammenfassung» Laden-in / Nur Verbindung erstellen ausführe, dann kommt die untenstehende Fehlermeldung:
Vielleicht haben sie einen Tipp für mich, wie ich das Problem lösen könnte.
Ich würde mich über eine positive Antwort freuen.
VBA-Schulung. Ich zeige und erkläre den Makrorekorder.
Aufgabe: Zeichnen Sie ein Makro auf, das eine Zelle formatiert, beispielsweise: fett, kursiv, Schriftart, Schriftgröße, Ausrichtung, Hintergrundfarbe, … und testen es an einer anderen Zelle.
Eine Teilnehmerin beschwert sich, dass der Makrorekorder nicht „optimale Spaltenbreite“ aufzeichnet.
Was hat sie gemacht? Sie hat ihr Makro in einer anderen Zelle in einer anderen Spalte ausprobiert. Leider zeichnet der Makrorekorder „hart“ den Spaltennamen auf, beispielsweise:
Columns("D:D").EntireColumn.AutoFitSpäter erkläre ich den Unterschied zwischen der relativen und der absoluten Aufzeichnung
Wir zeichnen das Makro relativ auf und erhalten:
ActiveCell.Columns("A:A").EntireColumn.EntireColumn.AutoFitMein Kommentar: funktioniert, aber „schön“ ist der Code nicht gerade. Besser wäre sicherlich:
ActiveCell.EntireColumn.AutoFitOh – man muss so aufpassen! Durchläuft man in VBA mit einem Zähler eine Sammlung an Objekten, greift sich Excel das erste, das zweite, das dritte, … Objekt. Aber welches ist das erste Objekt?
Bei Tabellenblättern ist das erste Objekt das linkeste. Dann das zweite von links. Dann das dritte.
Leider gibt es viele Sammlungen mit einer anderen „Reihenfolge“: die Reihenfolge, in der die Objekte erstellt wurden. Beispielsweise bei Diagrammen. Das erste ist nicht das linkeste, sondern das Diagramm, das zuerst erstellt wurde. Okay – das leuchtet ein.
Allerdings – wie sieht es bei Links aus. Wenn in einer Spalte mehrere Links stehen, ist der erste Link dann der oberste? Wird dann von oben nach unten weiter gezählt?
Nein! Auch hier gilt die Regel: die Reihenfolge des Erstellens entscheidet! Das heißt:
Das ist der erste Link:
Und nun wird der zweite und dann der dritte Link erstellt:
Und schließlich die übrigen 40.
Lässt man sie auslesen, erhält man:
Sub Links()
Dim i As Integer
Dim xlBlatt As Worksheet
Set xlBlatt = ActiveSheet
For i = 1 To xlBlatt.Hyperlinks.Count
xlBlatt.Range("C" & (i + 3)).Value = xlBlatt.Hyperlinks(i).SubAddress
Next i
End Subfolgende Liste:
Zum Glück verfügt das Objekt Link über die Eigenschaften Range und damit über Range.Row und Range.Column. Natürlich auch über Range.Address. So kann man die Links lokalisieren.
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
Auch Word nervt gewaltig. Ein Formular mit Formularfeldern wird mit Inhalten (Titel, Name und weitere) gefüllt.
Es wird Bezug genommen – entweder mit REF oder durch einfaches Nennen des Formularfeldnamens in einer Feldfunktion. Klappt:
Was passiert jedoch, wenn ein Feld (Titel) nicht gefüllt wird:
Eine unschöne Lücke.
Nun – bei Serienbriefen kann man dies mit einfachen IF-Funktionen abfangen:
In Word scheitere ich.
Und: Word stellt zwar einige Rechenoperationen für Zahlen zur Verfügung: in den Formularfeldern
Und auch in den Feldfunktionen
Aber leider beziehen sich diese Berechnungen nur auf Zahlen – für Texte ist hier nichts vorgesehen – noch nicht einmal eine Verkettungsfunktion.
Ich bin sehr irritiert, dass DAS nicht funktioniert. Ich muss das Problem wohl anders lösen.
Hallo Herr Martin,
in dieser Datei sind die Verknüpfungen falsch zugeordnet.
Ich habe versucht, dies über die Änderung der Hyperlinks zu korrigieren.
Wie kann die Zuordnung dauerhaft geändert werden? Sie scheint intern abgelegt zu sein.
=========
Hallo Herr H.,
Öffnen Sie den Blattschutz (haben Sie wahrscheinlich),
öffnen Sie den Link (haben Sie wahrscheinlich)
und ändern dann an den DREI Stellen den Bezug:
* Beschriftung (anzuzeigender Text)
* Quickinfo
* Ziel
Hallo Rene,
mir ist doch noch etwas besseres eingefallen, lässt sich aber auch nicht umsetzen…
Ich wollte jetzt direkt in Power Query filtern nach den Zeilen, welche im Jahr, welches ich im Excel Blatt auswähle, ungleich 0 sind.
Irgendwo passt was noch nicht, hoffe du kannst mir helfen 😀
Hallo Nadine,
Wenn du Leerzeilen / Leerzellen rausfiltern möchtest, dann nicht wie in Excel mit zwei Anführungszeichen, sondern mit Null. Ähnlich wie Datenbanken hat PowerQuery einen eigenen Datentyp für leere Zellen: null:
= Table.SelectRows(#“Geänderter Typ“, each [Datum] <> null)
Und: der Feldname darf nicht in Anführungszeichen gesetzt werden – das Feld heißt: [Datum], nicht [„Datum“].
Hallo Rene,
ich habe schon einiges in Power Query bearbeitet und bin gerade auf dem Stand, dass ich mir durch Filter genau die Daten aus Power Query ziehe welche ich benötige.
Aktuelles Problem: Ich möchte nur die Zeilen haben, welche in dem Jahr Werte haben, welches ich als Filter eingebe. Ich will aber nicht nur das Jahr, sondern alle Werte dann, wenn in diesem speziellen Jahr ein Wert vorhanden ist.
Den Filter „Jahr“ habe ich nicht in Power Query benutzt, da ich noch keine Lösung gefunden habe.
###
Hallo Nadine,
1.) wenn du einen Filter definierst, kannst du ihn auch in PowerQuery „reinziehen“. Danach würde ich ihn als Drilldown in einen Wert umwandeln – etwas so:
2.) Filtere ein beliebiges Datum. Es sieht dann so aus:
= Table.SelectRows(#“Gefilterte Zeilen“, each [Datum] >= #date(2020, 1, 1))
Und nun ersetze ich die Jahreszahl 2020 durch meine „Variable“ aus Schritt 1.
PowerQuery-Schulung.
Eine Teilnehmerin fragt, wie sie den M-Code einer Abfrage dokumentieren, das heißt in Word speichern kann.
Ich antworte ihr, dass sie den „Erweiterten Editor“ öffnen kann und dort den Code einsehen und herauskopieren kann.
Eine andere Teilnehmerin weist mich darauf hin, wie man alle Codetexte aus allen Abfragen erhält:
„Du musst nach Excel wechseln:
Dort musst du die Abfragen markieren und kopieren:
Und das Ergebnis kann man nach Word einfügen. PowerQuery fügt nicht die Namen der Abfragen, sondern den M-Code ein:
Ich bin begeistert! Ein Dankeschön an Lydia Homann für diesen guten Tipp. Schon nervt Excel wieder ein bisschen weniger!
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).
Die neuen Sicherheitseinstellungen kennen Sie? Auf meinem Rechner erhalte ich die Warnmeldung:
Es wurde im Internet schon viel über Sinn oder Unsinn dieser Sicherheitshürde diskutiert.
Sie entfernen es über die Eigenschaften der Datei:
Die Erläuterung:
Makros aus dem Internet werden in Office standardmäßig blockiert. – Deploy Office | Microsoft Docs
Reguläre Ausdrücke – sie sind so klasse – warum mag Microsoft sie nicht?
Die Aufgabe: Extrahieren Sie aus zirka 30.000 Zellen einer Exceltabelle die darin befindlichen Datumsinformationen. Es finden sich Texte wie:
Maistraße 17 (St. 29.03.2016), Flurnummer-alt: 47/11
Bahnhofstraße 1 – 17.3.2022 – Flurnummer-alt: 08/15
Hirtenweg 3A 05-2005 – Garagen, Flurnummer-alt: 00/77
Ich überlege: Formeln wären eine Option. Aber sehr umständlich.
VBA wäre gut.
PowerQuery auch – kennt aber keine regulären Ausdrücke.
Ich entscheide mich für VBA.
Dort muss man einen Verweis auf „Microsoft VBScript Regular Expressions“ einbinden. Oder diese Klasse mit
Set regex = CreateObject("vbscript.regexp")aufrufen. Dann kann man definieren:
' -- Muster: ***01.01.2022***
strMuster1 = ".*\d{1,2}\.\d{1,2}\.\d{2,4}.*"
strMuster1_Raus = "\d{1,2}\.\d{1,2}\.\d{2,4}"
Und kann nun extrahieren:
regex.Pattern = strMuster1
regexRaus.Pattern = strMuster1_Raus
regexRaus.Global = True
For i = 1 To ThisWorkbook.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
If regex.Test(Range("I" & i).Value) = True Then
Set strTreffer = regexRaus.Execute(Range("I" & i).Value)
j = 0
For Each strFund In strTreffer
strTemp = strFund
If IsDate(strTemp) Then
If Len(Split(strTemp, ".")(2)) = 3 Or Len(Split(strTemp, ".")(2)) = 1 Then
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
Else
Range("Q" & i).Offset(0, j).Value = CDate(strTemp)
If Year(CDate(strTemp)) > Year(Date) Then
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
End If
Else
Range("Q" & i).Offset(0, j).Value = strTemp
Range("Q" & i).Offset(0, j).Interior.Color = vbRed
End If
j = j + 1
Next
End If
NextVier andere Varianten werden analog abgearbeitet. Klappt.
Warum hat VBA nicht als Standard Regex eingebunden?
Warum kennt PowerQuery keine regulären Ausdrücke?
Warum kann man keine regulären Ausdrücke beim Autofilter oder Spezialfilter eingeben?
Nachtrag: Ich habe etwas gewühlt. Imke Feldmann beschreibt, wie man über JavaScript einen Zugriff auf RegEx erhält:
Trotzdem: ich ziehe hier VBA vor.
Und: vor einigen Jahren hatte ich eine XML-Schulung, in der ich die regulären Ausdrücke vorgestellt hatte. Die Teilnehmerinnen kannten sie, waren damit vertraut, arbeiteten in „anderen Welten“ damit und waren begeistert. Sie wollten sich sogar T-Shirts mit dem Aufdruck „I ♥ RegEx“ drucken lassen. Haben sie aber doch nicht.
Hallo Herr Martin,
Es geht um die Extrahierung von Datums- und Adressinformationen aus einem Bemerkungsfeld im Rahmen der Datenmigration.
Die Ausgangsinformationen stehen einer Spalte.
Diese Felder enthalten Stichtage und Adressen (Straße, Hausnummer, Zusatz).
Zum Stichtag:
Zur Adresse:
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.“
Schwierige Aufgabe – ich weiß keine Lösung dafür.
Die lieben, kleinen Unterschiede.
Danke an Tanja Kuhn für folgenden Hinweis:
Wählt man in Excel bei den Zellformaten das Zahlenformat „Sonderformat“, so ist die Liste bei Deutsch (Schweiz) und Deutsch (Österreich) leer:
In Deutsch (Deutschland) jedoch gibt es Auswahlmöglichkeiten:
„Vielleicht gibt es in der Schweiz gar keine Postleitzahlen, Sozialversicherungsnummern oder Bücher mit ISBN“, witzle ich. Wir lachen beide.
Ich bin gerade völlig perplex. Ich programmiere für eine Schweizer Firma ein Excel-Tool. Ich lasse alle Tabellenblätter, die mit Monatsnamen beschriftet sind, ausblenden. Nur das Blatt MRZ bleibt stehen. In der Schweiz bleibt es stehen.
Ich frage Tanja Kuhn. Sie hat die Schweizer Oberfläche von Excel:
Und ja: sie bestätigt es: seit einigen Versionen lautet die Abkürzung des dritten Monats in der Schweiz nicht Mrz, wie in der ISO 8601 (EN 28601:1992) festgelegt, sondern Mär. Das erkennt man schnell, wenn man den Text „Jan“ einträgt unter herunterzieht:
Und eben deshalb liefert der VBA-Befehl
Format(DateSerial(2022, 3, 1), "MMM")in der Schweiz etwas anderes als in Deutschland (oder Österreich).
Perfide!
Böse!
Eine Funktionen der intelligenten Tabellen sind „ausgegraut“. Warum?
Auch sortieren und filtern funktioniert nicht …
Man muss schon ganz genau hinschauen, um zu entdecken, dass es sich hier um ZWEI Tabellen handelt, die nebeneinander eingefügt wurden …
Word. Manchmal auch ärgerlich.
Ich weiß – nicht alle Feldfunktionen werden automatisch aktualisiert. Beispielsweise REF. Innerhalb eines Textes ist es kein Problem mit REF auf ein Formularfeld zu verweisen. Klappt prima.
Klappt allerdings nicht, wenn sich das Feld in einem Textfeld oder in der Kopf-, beziehungsweise Fußzeile befindet:
Beim Drucken wird des natürlich aktualisiert:
Oder man muss ein Makro schreiben …
Sehr geehrter Herr Martin,
als Anhang schicke ich Ihnen eine Beispieldatei.
Es geht hier um den Umgang mit einem umfangreichen Fußnotentext (hier: rot).
Wie Sie sehen, ist auf der ersten Seite Haupt- und darunter Fußnotentext.
Nun ist aber ab Seite 2 nur noch Fußnotentext zu sehen.
Wie kann man das anstellen, daß auch ab Seite 2 ff. sowohl Haupt- als auch Fußnotentext platziert werden kann.
Ich habe leider keine Lösung gefunden.
Hallo Herr F.,
schöne Frage. Ich habe mal nachgeschaut (haben Sie sicherlich auch schon): Wenn ich in der Entwurfsansicht mit über Referenzen die Notizen anzeigen lasse, finde ich nur die Fußnoten, Trennlinien und Fortsetzungstexte, aber keine Einstellungen oder Optionen für eine maximale Länge.
Ich habe auch eine Tabelle versucht – auch das klappt nicht.
Sorry – aber ich weiß keine Lösung dafür.
Ich würde eher fragen, ob wirklich sooooo viel Text in der Fußnote stehen muss – wären hier Endnoten nicht besser geeignet? Oder sollte der Text nicht besser im Text stehen?
Liebe Grüße
Rene Martin
Hallo Rene,
komme tatsächlich um VBA nicht herum und benötige Deine Unterstützung.
Ich habe mir mit Hilfe YouTube ein kleines Makro gebastelt, welches nur das Tabellenblatt „Ausdruck“ zum Ausdrucken zulässt.
Wie muss ich vorgehen, wenn ich nun noch ein zweites Tabellenblatt zulassen möchte?
Da hast Du doch sicher eine Idee, oder?
Sub Workbook_BeforePrint(Cancel As Boolean)
Dim WsName As String
WsName = „Ausdruck“
For Each xWs In Application.ActiveWorkbook.Windows(1).SelectedSheets
If xWs.Name <> WsName Then
MsgBox („Kein Ausdruck dieses Tabellenblattes möglich!“)
Cancel = True
End If
Next
End Sub
Gruß
Christian
###
Hallo Christian,
ich glaube du denkst viel zu kompliziert – ich denke das folgende Makro macht, was du möchtest:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name <> „Ausdruck“ And ActiveSheet.Name <> „Ausdruck2“ Then
MsgBox „Kein Ausdruck des Tabellenblattes “ & ActiveSheet.Name & “ möglich!“
Cancel = True
End
End If
End Sub
Superpraktisch – in bin begeistert. In PowerPoint gibt jetzt eine Funktion „sperren“. Darauf habe ich schon lange gewartet.
Allerdings: warum nicht auch in Excel? Vielleicht kommt es ja noch …
Schönen guten Tag, Herr Martin,
Ich möchte wissen, ob es eine Funktion gibt, die mir das Einbetten von Visio-Organisations-Diagrammen in PowerPoint ermöglicht, die einem Powerpoint-Anwender die Bearbeitung der Objekte ermöglicht, ohne dass er Visio auf dem Rechner installiert hat.
Ich hatte gehofft, dass es im Hintergrund der Einbettung eine solche Anwendung gäbe, doch ich habe verschiedene Objekt-Einbettungen in PowerPoint aus Visio ausprobiert, habe es mit Kopieren und mit Links versucht, aber meine Kollegin, die keine Visio-Lizenz auf dem Rechner hat, kann keine davon bearbeiten (die Links griffen übrigens auf einen für meine Kollegin gesperrten Ordner zu, das wird auch so bleiben).
Vielen Dank im Voraus,
mit freundlichen Grüßen aus Hamburg,
####
Hallo Frau T.,
ich fasse Ihre Frage zusammen: Sie möchten eine Visio-Zeichnung bearbeiten, ohne dass Visio installiert ist. Das geht nicht!
Es ginge auch nicht, wenn Sie eine Excel-Tabelle nach Word kopieren und diese dann auf einem anderen Rechner, auf dem kein Excel installiert ist, bearbeiten wollen.
Ich habe es mal probiert: Wenn Sie die Visio-Zeichnung kopieren
und als „Erweiterte Metadatei“ in PowerPoint einfügen,
können Sie diese in PowerPoint als „Grafik umwandeln“ (bearbeiten),
die Gruppierung aufheben und nun jedes Objekt wie eine PowerPoint-Form bearbeiten.
Aber ich fürchte, das wollen Sie nicht …
Und: ich fürchte, dass ich Ihnen hier nicht helfen kann.
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Und noch gleich eine Frage hinterher, s. Anhang.
Hallo Frau I.,
Sie können die Spannweite als Text darstellen lassen, beispielsweise so:
=I11&“ – „&I12
Oder Sie können die Zahlen auch formatiert darstellen:
=TEXT(I11;“#.##0,00″)&“ – „&TEXT(I12;“#.##0,00“)
Liebe Grüße
Rene Martin
Lieber Herr Martin,
nun haben sich bezüglich der Kriterien- „=“-Auswahl einige Fragen ergeben.
Zur Erinnerung: Sie hatten uns Excel-Dokumente erstellt, in denen unterschiedliche Funktionen kombiniert wurden, damit bestimmte Filterungen auf ein eingelesenes Exceldokument ausgeführt werden können.
Zu den Fragen:
Hallo Frau I.,,
Habe ich Sie richtig verstanden: Sie möchten eine Mehrfachauswahl mit ODER.
Das Multiplikationszeichen entspricht dem logischen UND; das Additionszeichen den logischen ODER.
Also so:
=WENNFEHLER(FILTER(qry_Datenzugriff_SpaltenAuswahl;
(WENN(ODER(A2=““;B2=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A2&“]“)=B2))+
WENN(ODER(A3=““;B3=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A3&“]“)=B3))+
WENN(ODER(A4=““;B4=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A4&“]“)=B4))+
WENN(ODER(A5=““;B5=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A5&“]“)=B5))+
WENN(ODER(A6=““;B6=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A6&“]“)=B6))+
WENN(ODER(A7=““;B7=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&A7&“]“)=B7))+
WENN(ODER(D2=““;E2=““;F2=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D2&“]“)>=E2)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D2&“]“)<=F2))*
WENN(ODER(D3=““;E3=““;F3=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D3&“]“)>=E3)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D3&“]“)<=F3))*
WENN(ODER(D4=““;E4=““;F4=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D4&“]“)>=E4)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D4&“]“)<=F4))*
WENN(ODER(D5=““;E5=““;F5=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D5&“]“)>=E5)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D5&“]“)<=F5))*
WENN(ODER(D6=““;E6=““;F6=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D6&“]“)>=E6)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D6&“]“)<=F6))*
WENN(ODER(D7=““;E7=““;F7=““);SEQUENZ(ZEILE(qry_Datenzugriff_SpaltenAuswahl);1;1;0);(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D7&“]“)>=E7)*(INDIREKT(„qry_Datenzugriff_SpaltenAuswahl[„&D7&“]“)<=F7))
);““)
Ein Hi in den Süden,
hallo Rene,
heute ist Samstag und das halbe Wochenende schon einmal rum. Hm.
Der sechste Arbeitstag läuft bei mir noch bis 0 Uhr, morgen auch, aber eingeschränkt.
Was wurde aus meinem aktuellen EXCEL-VBA-Projekt?
(1) Deinen VBA zum Setzen des Filters habe ich versucht zu erweitern durch Variablen.
Klappt nicht so dolle.
s_DatenRange = "$A$1:$I$" & l_ZeileLetzte
s_DatumStart = s_Start_Jahr & ", " & s_Start_Monat & ", " & s_Start_Tag
s_DatumEnde = s_Ende_Jahr & ", " & s_Ende_Monat & ", " & s_Ende_Tag
MsgBox "Jetzt kommt das Problem: DateSerial - Argument ist nicht optional"
' Versuch 1
ActiveSheet.Range(" & s_DatenRange & ").AutoFilter Field:=6, Criteria1:= _
">=" & CDbl(DateSerial(" & s_DatumStart & ")), Operator:=xlAnd, Criteria2:="<=" & CDbl(DateSerial(" & s_DatumEnde & "))
' Versuch 2
Dim s_teil_1, s_teil_2 As String
s_teil_1 = DateSerial(" & s_DatumStart & ")
s_teil_2 = DateSerial(" & s_DatumEnde & ")
s_teil_1 = DateSerial(s_DatumStart)
s_teil_2 = DateSerial(s_DatumEnde)
ActiveSheet.Range(" & s_DatenRange & ").AutoFilter Field:=6, Criteria1:= _
">=" & CDbl(" & s_teil_1 & "), Operator:=xlAnd, Criteria2:="<=" & CDbl(" & s_teil_2 & ")Hallo Jürgen,
Zu DateSerial: Kennst du dich Excel-Funktion DATUM nicht? Sie wandelt eine JahresZAHL, eine MonatsZAHL und eine TagesZAHL in ein Datum um. Und eben das macht DateSerial in VBA.
Einfach nicht aufgepasst.
Access
Ich erstelle ein Endlosformular mit einem Textfeld, das natürlich mehrmals angezeigt wird:
Das Feld, oder genauer: der Detailbereich soll so groß werden wie der Text es vorgibt. Ein paar Zeilen Code sind nötig:
If intZeilen > 6 Then
Me.Detailbereich.Height = (1701 / 6) * intZeilen
Me.txtAktueller_Stand.Height = (1701 / 6) * intZeilen
Else
Me.Detailbereich.Height = 1701
Me.txtAktueller_Stand.Height = 1701
End IfDer Detailbereich wird größer, wenn längerer Text eingegeben wurde und der Cursor in das Textfeld gesetzt wird:
Allerdings: der Bereich wird nicht wieder kleiner:
Die Lösung:
ich darf nicht zuerst den Detailbereich verkleinern und anschließend das Textfeld, sondern umgekehrt: zuerst das Textfeld und DANN den Detailbereich:
Me.txtAktueller_Stand.Height = (1701 / 6) * intZeilen
Me.Detailbereich.Height = (1701 / 6) * intZeilen
DANN klappt es auch:
Excelstammtisch letzte Woche.
Sabrina stelle eine interessante Frage:
In einer Liste – egal ob intelligente Tabelle oder Bereich – wurde ein Filter gesetzt:
Über diesen Autofilter wird in einer Spalte gefiltert:
Eine Zelle einer anderen Spalte wird markiert und kopiert:
Schaltet man den Filter über das Symbol „Alle löschen“ in der Registerkarte „Daten“ aus:
Wird leider der Kopiermodus deaktiviert. Der Zellinhalt kann nicht mehr in das Textfeld des Autofilters eingetragen werden:
Auch das Öffnen der Zwischenablage bringt keinen Erfolg.
Ich schlage vor, nicht die Zelle, sondern den Zellinhalt zu markieren und zu kopieren:
Klappt, stößt aber nicht auf Gegenliebe.
Josef meldet sich zu Wort. Man findet im Kontextmenü der Zelle den Befehl:
Nach dem Wert der ausgewählten Zelle filtern.
So erhält man die Schnittmenge beider Filter:
Deaktiviert man allerdings den ersten Filter:
hat man nur noch den zweiten Filter.
Sabrina ist begeistert und Excel nervt ein bisschen weniger.
Danke an Josef Feißt für diesen wertvollen Hinweis.
Excelstammtisch letzte Woche.
Frank zeigt die unterschiedlichen Orte, an denen man Measures erstellen kann.
Wählt man Power Pivot / Measures / Measures verwalten, so kann man den Dialog an allen vier Seiten (und der Ecke) vergrößern:
Dagegen in einer Pivottabelle, deren Daten dem Datenmodell hinzugefügt wurden, kann der Dialog „Measure bearbeiten“ nur an der Ecke vergrößert werden:
Amüsant.
Danke an Frank Arendt-Theilen für den Hinweis.
Excelstammtisch letzte Woche.
Volker präsentiert eine clevere Lösung für die Datenüberprüfung. Er weist auf folgendes Phänomen hin:
Liegt eine intelligente Tabelle auf dem gleichen Tabellenblatt, bewirkt eine Erweiterung, dass die Liste der Datenüberprüfung auch erweitert wird:
Liegt die intelligente Tabelle jedoch auf einem anderen Tabellenblatt, wird die Liste der Datenüberprüfung nicht erweitert.
Danke an Volker Pagel für den guten Hinweis.
Excelstammtisch letzte Woche.
Frank stellt die neuen Elemente von Excel vor. Beispielsweise findet sich im Kontextmenü der Statuszeile ein neuer Eintrag „Sheetnummer“:
Abgesehen vom merkwürdig übersetzen Namen „Sheetnummer“ eigentlich eine praktische Sache: Die Anzahl der Tabellenblätter werden angezeigt.
Allerdings: Auch ausgeblendete Blätter (auch xlSheetveryHidden) werden aufgelistet.
Josef wirft die Frage in den Raum, ob wir das wollen. Zu Recht: ich will das eigentlich nicht – manchmal „verstecke“ ich bewusst Dinge in Tabellen, die ich weitergebe, die nicht auf den ersten Blick einsehbar sind …
Danke an Frank Arendt-Theilen für diese Information.
Excelstammtisch letzte Woche.
Frank stellt den Navigator vor, den Excel vor Kurzem in Microsoft 365 eingeführt hat:
Woah, denke ich: endlich nach 20 Jahren hat Microsoft von openOffice Calc und LibreOffice Calc den Navigator abgeschaut – den gibt es dort seit Ewigkeiten!
Danke an Frank Arendt-Theilen für den Hinweis.
Hallo genervter Excel-Verliebter,
kennst Du das Problem beim Löschen von Zeilen bei ausgeblendeten Spalten?
Eine intelligente Tabelle mit Filter.
Hier sind auch die ganzen Zeilen und alle gefilterten Zeilen markiert und STRG + Minus funktioniert nicht. Spalte B ist ausgeblendet.
Zeilen in einem gefilterten Bereich oder in einer gefilterten Tabelle können nicht verschoben werden.
Sind nur die Daten markiert und nicht die ganzen Zeilen, funktioniert es doch wieder mit Meldung. Es sind dann aber wieder alle Daten der Zeile weg und nicht nur die markierten Tabellenwerte.
Grüßle
Andreas (Thehos)
In Excel kann man eine Reihe von Elementen einfügen: Bilder, 3D-Grafiken, Diagramme, Formen und auch SmartArts:
Öffnet man diese Datei in Excel online, wo SmartArts nicht unterstützt werden, wird dort ein Rechteck angezeigt:
Ebenso in teams:
Andreas Thehos schreibt:
„habe heute einen schönen Excel-Fehler gefunden.
SmartArts sorgen dafür, dass sämtliche Objekte eines Tabellenblatts entfernt werden.
Die Datei liegt auf einem SharePoint Online. Sobald jemand online per Browser oder Teams darauf zugreift und auch nur das Tabellenblatt wechselt, werden bei der Synchronisation alle Objekte im Blatt des SmartArts entfernt. Anschließend gibt es einen Fehler in Excel in drawingsX.xml
Kennst du das?“
Leider kann ich das nicht nachvollziehen …
Einfach nicht aufgepasst!
Ich habe eine Arbeitsmappe mit sieben + ein, also acht Tabellenblattern. Sie heißen:
Chef, Brummbär, Schlafmütz, Hatschi, Pimpel, Happy, Seppel und Schneewittchen
Die ersten drei Blätter sind ausgeblendet, ebenso das letzte (Master-)Blatt:
Also auf Excel-Seite sieht das so aus:
Nun soll per VBA das erste Blatt sichtbar gemacht werden, umbenannt werden und an eine bestimmte Position verschoben werden. Das Masterblatt „Schneewittchen“ wird anschließend wieder unsichtbar gemacht.
Schritt für Schritt:
1.) Zugriff auf das (unsichtbare) Blatt „Schneewittchen:
Dim xlBlatt As Worksheet
Dim i As Integer
Dim strBlattname As String
Set xlBlatt = ThisWorkbook.Worksheets("Schneewittchen")
2.) Blatt wird eingeblendet:
xlBlatt.Visible = xlSheetVisible3.) Blatt wird vor das erste Blatt geschoben:
xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)4.) Mit Hilfe einer benutzerdefinierten Funktion GibtEsBlattname wird überprüft, welcher Blattname noch nicht existiert, beispielsweise „Schneewittchen 001“:
i = 0
Do
i = i + 1
strBlattname = "Schneewittchen " & Format(i, "000")
Loop Until GibtEsBlattname(strBlattname) = False
5.) Das erste Blatt wird umbenannt:
ThisWorkbook.Worksheets(1).Name = strBlattname6.) Das Masterblatt „Schneewittchen“ wird ausgeblendet:
ThisWorkbook.Worksheets("Schneewittchen").Visible = xlSheetVeryHidden7.) Das neu erzeugte Blatt wird nach hinten verschoben:
ThisWorkbook.Worksheets(strBlattname).Move After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)Und: ein Fehler ist die Folge:
Die Move-Methode des Worksheet-Objektes konnte nicht ausgeführt werden.
Der Fehler liegt in folgender Zeile:
xlBlatt.Copy Before:=ThisWorkbook.Worksheets(1)Welches ist das ERSTE Tabellenblatt? Blatt Nummer 1 oder das erste sichtbare Tabellenblatt. Die Antwort: letzteres: das Blatt wird vor das erste sichtbare Blatt kopiert. Damit ist es anschließend nicht das erste Blatt! Sondern die drei ausgeblendeten Blätter liegen noch weiter links von dem Blatt.
Leider besitzt die Methode Copy kein Objekt, an das das Ergebnis des Kopierens übergeben werden kann.
Also muss man sich „auf die Suche“ nach dem Blatt machen, beispielsweise: durchlaufe alle Blätter und such das Blatt, das „Schneewittchen(2)“ heißt, besser
ThisWorkbook.Worksheets(1).Name Like "Schneewittchen *"Dann klappt es auch!
Nein, das ist nicht schön! Visio weigert sich, einen Button auf das Zeichenblatt einzufügen:
Ich lösche einige Shapes auf dem Zeichenblatt, wie vorgeschlagen – aber es nützt: gar nichts.
Mein Trick: Ich habe ein zweites Zeichenblatt erstellt, dort einen Button eingefügt; die Zeichnung auf das zweite Blatt kopiert und alles wieder zurückkopiert. Klappt. Okay – ich musste noch die Buttons umbenennen – die Namen hat mir Visio bei der Kopieraktion zurückgesetzt.
Klappt. Doof!
Hallo Rene,
deine Mails mit positiven Informationen und Anhang erreichten mich.
Themenwechsel 3 von 3:
Falls du an weiteren Fragen rund um EXCEL-VBA Interesse hast, informiere mich einfach. Dazu habe ich dir zwei Dateien mitgesendet.
Die Lösung der Probleme ist kein MUSS.
Die Thematiken:
(1) ein Filterproblem ist bestimmt nicht so schwierig zu lösen. Eine Liste:
Der Filter:
Nach Filtersetzen werden keine Daten angezeigt.
Der Code:
ActiveSheet.Range("$A$3:$F$12").AutoFilter Field:=4, Criteria1:= _
">=01.01.2000", Operator:=xlAnd, Criteria2:="<=31.03.2022"Hallo Jürgen,
der Filter verlangt einen Datumswert, der in eine Zahl konvertiert wurde:
ActiveSheet.Range("$A$3:$F$12").AutoFilter _
Field:=4,
Criteria1:= ">=" & CDbl(DateSerial(2000, 1, 1)), _
Operator:=xlAnd, _
Criteria2:="<=" & CDbl(DateSerial(2022, 3, 31))
Dann klappt es:
Einer gewinnt! Oder: es kann nur einen Highlander geben.
Schöne Frage in der Excel-Schulung: kann ich eine (intelligente) Tabelle mit einer bedingten Formatierung versehen? Wissen Sie es? Welche Farbe gewinnt?
Gegeben sei ein Listenbereich:
Er wird in eine (intelligente) Tabelle konvertiert:
Natürlich kann ich die Schriftfarbe festlegen (beispielsweise für alle Flüsse aus Asien):
Aber: wer gewinnt, wenn ich eine Hintergrundfarbe hinzufüge?
Wer wohl?
Und: was passiert, wenn die Liste verlängert wird? Richtig: dann wird die bedingte Formatierung, wie auch andere Formatierungen, mitgenommen:
Ich empfehle dennoch: entweder intelligente Tabelle OHNE Formatierung oder bedingte Formatierung nur mit Schriftfarbe.
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 …
Hallo Rene
Es ist schon eine Weile her, dass wir Kontakt hatten und ich hoffe es geht Dir in Anbetracht der Weltlage einigermassen gut?
Leider kämpfe ich wieder mit einem Problem, wobei ich mich ehrlich gesagt mehr darüber ärgere, dass ich das Problem nicht finde, als über den Fehler selbst.
Es geht wieder um das VBA Script, welches die Excel Daten auswertet und je nach Zuweisung im Layer meine Shapes sichtbar macht, oder eben nicht.
Das Script macht eigentlich was es soll, doch nach dem Durchlaufen der letzten Zeile, kommt eben der genannte Fehler.
Vielleicht hast Du etwas Zeit und bist willig mir zu helfen? Herzlichen Dank im Voraus für eine Reaktion.
Hallo H.,,
schau mal in deine Daten: der letzte Datensatz ist leer:
Du musst die überprüfen/abfangen, beispielsweise mit:
If IsNull(data(idColumn)) …
Liebe Grüße
Rene
Excelschulung. Schwerpunkt: Listen.
Ich erkläre den Nutzen und die Vorteile von (intelligenten) Tabellen. Beispielsweise Diagramme. Setzt man auf eine intelligente Tabelle ein Diagramm auf:
so wird die Erweiterung der Liste sofort ins Diagramm aufgenommen:
Was mich jedoch irritiert: Warum zeigt das Diagramm weder im Diagrammbereich, in den Achsenbeschriftungen noch in den Legendeneinträgen den Namen der Tabelle an – sondern immer noch den Bereich?
Die Pivottabelle zeigt doch auch die „korrekte“ Datenquelle an:
Ich will in der Excelschulung demonstrieren, dass Listen eine Überschriftszeile haben sollten; ja – dass Pivottabellen auf Listen aufsetzen, bei denen jede Spalte eine eindeutige Überschrift besitzt.
Ich lösche eine Überschrift heraus:
erstelle eine Pivottabelle, vergesse aber, den Haken beim Datenmodell zu entfernen:
Da die Daten ins Datenmodell geladen werden, muss jede Spalte eine Überschrift haben. Da dies nicht gegeben ist, füllt Excel nicht die leere Überschrift auf, sondern beginnt ab der zweiten Zeile, die als Überschrift verwendet wird:
Eigentlich wollte ich die Fehlermeldung
Der PivotTable-Feldname ist ungültig. Um einen PivotTable-Bericht zu erstellen, müssen Sie Daten verwenden, die einer Liste mit Spaltenüberschriften organisiert sind. Wenn Sie den Namen eines PivotTable-Berichtsfeldes ändern, müssen Sie einen neuen Namen für das Feld eingeben.
zeigen:
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 bin verblüfft!
Ich soll – weil Lotus Notes in einer Firma abgeschaltet wird – eine Notes-Datenbank in Access nachbauen.
Access hat weder Kästchen zum Auf- und Zuklappen von Bereichen:
Noch kann man in Access EINZELNE Bereiche in einem Endlosformular dynamisch vergrößern lassen. Letzteres habe ich mit VBA gelöst – die Zeilenzahl ermittelt und dann den Detailbereich – genauer: ALLE Detailbereiche – vergrößert:
Der erste Text:
Der zweite Text – der Detailbereich wird größer:
Der dritte Text – der Detailbereich wird größer:
Und noch Text – der Detailbereich wird kleiner:
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.
Man schickt mir eine Fehlermeldungmeldung.
Das Arbeitsblatt oder die Arbeitsblätter, die in die Zielarbeitsmappe kopiert oder verschoben werden, besitzen möglicherweise angefügte Makros. Makrocode stellt ein potenzielles Sicherheitsrisiko dar. Sie sollten den Vorgang nur fortsetzen, wenn Sie sicher sind, dass der Makrocode von einer vertrauenswürdigen Quelle stammt. Möchten Sie den Vorgang fortsetzen?
Ich habe keine Ahnung, wo man das in Excel einstellen kann. Ich vermute, diese Option wurde in den Gruppenrichtlinien von der IT abgefangen. Oder? Kennt jemand diese Meldung?
Wie doof.
Word-Schulung. Online. Über teams.
Ich beginne bei der Mikrotypografie: bedingter Trennstrich, Halbgeviertstrich und natürlich geschütztes Leerzeichen. Ich zeige, dass man ein „Wort“ vor dem Leerzeichen mit dem Wort „hinter“ dem Leerzeichen zusammenhalten kann, indem man die Tastenkombination [Umschalt] + [Strg] + [Leertaste] drückt:
Also gut – dann muss ich dieses Zeichen über Einfügen / Symbol einfügen. Da mir teams die Taste „gestohlen“ hat …
Es ist zum Haare-Raufen. Hätte ich welche auf meinem Kopf! Unglaublich! Excel ärgert mich, wo es nur kann. Wenn ich schon einen Fehler haben möchte – was passiert? – Richtig – natürlich kein Fehler! Es ist zum Haare-Raufen!
Was ist geschehen?
Excelschulung. Turboschulung: ich zeige in einer Stunde Listen: sortieren, filtern, intelligente Tabellen, Datenschnitt und Pivottabellen. Eine Teilnehmerin bedankt sich für die Infos zu den Pivottabellen – das hätte ihr sehr weitergeholfen; nun verstehe sie den Gedanken, der dahinter steht. Und: „so schwierig ist das gar nicht“:
Ich wiederhole. „Der Aufbau der Tabelle ist wichtig: Entweder Sie nehmen eine intelligente Tabelle oder Sie achten darauf, dass Ihre Liste keine Leerzeile und keine Leerspalte hat. Und: jede Spalte muss eine Überschrift haben.“
Ich demonstriere es, lösche eine Spaltenüberschrift raus
erstelle eine Pivottabelle – und: es klappt! Excel unterläuft meine Schulung. Jetzt, wo Excel einen Fehler erzeugen sollte tut Excel: NICHTS! Fügt den gelöschten Spaltennamen ein:
Der Gedanke: Klar – eine zweite Pivottabelle wird nicht auf der Liste aufgesetzt, sondern auf dem Pivotcache. Deshalb weiß Excel auch den Namen der fehlenden Spaltenüberschrift. Der Fehler käme beim Aktualisieren zum Tragen.
Oder – damit die Teilnehmerin mir glaubt – ich kopiere die Liste in eine andere Datei, erstelle dort die Pivottabelle und:
HURRA – die Fehlermeldung!
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:
=WENNFEHLER(INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0));"")Klappt!
Allerdings: In anderen Blöcken werden Zahlen eingetragen. Welche ist die erste Spalte, in der eine Zahl steht?
Die Funktion
=VERGLEICH(">0";E111:S111;0)versagt jedoch. Allerdings … nach einigen Versuchen:
=VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0)funktioniert! Und man kann die Überschrift suchen über:
=INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0))und schließlich:
=WENNFEHLER(INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0));"")Da diese Formulare dynamisch per VBA erzeugt werden, stellt es nun keine große Herausforderung mehr dar, diese in VBA-Code umzuwandeln:
.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(""x"",RC[-20]:RC[-2],0)),"""")"und analog:
.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(TRUE,INDEX(ISNUMBER(RC[-20]:RC[-2]),0),0)),"""")"Windows kann auch ganz schön nerven!
Vor Kurzem habe ich auf meinem Laptop auf Windows 11 umgestellt. Der erste Schreck kam in der Form, dass Laufwerk D nicht mehr gefunden wurde. Nicht schön.
Zuerst habe ich in den Diensten gewühlt; anschließend in der Datenträgerverwaltung. Dort wurde ich fündig und habe den „Datenträger neu eingelesen“
Vorgestern habe ich mit Martin geplaudert. Über teams. 13 Uhr haben wir ausgemacht. Um 12.59 Uhr wollte ich den „Raum betreten“ – da schickte er mir eine Mail, wo ich denn bleibe. Ein Witz von mir folgte – „ist doch erst 13.00 Uhr“. „Nein“, war Martins Antwort, „es ist 13.08 Uhr“. Mein Blick auf eine andere Uhr gab ihm recht: meine Laptop-Uhr ging fast zehn Minuten nach. „Werden die Uhren denn nicht automatisch gestellt?“
Nach dem Schwatz machte ich mich auf die Suche. Tatsächlich: durch die Systemumstellung war eingestellt:
„Uhrzeit automatisch festlegen: aus“
Also: Fluchs einschalten – jetzt tickt es bei mir wieder richtig. Zumindest auf meinem Laptop mit Windows 11.
Ich gestehe, dass ich mich manchmal irre. Oder Dinge übersehe. Oder schlicht und einfach nicht weiß. Umso mehr freue ich mich darüber, dass andere meinen Blog mitlesen und mir korrigierende Antworten schreiben. Eine Berichtigung hat mich vorgestern erreicht – ich möchte sie hier gerne wiederholen, weil ich danke, dass sie wichtig ist.
In dem Artikel
habe ich geschrieben, dass die Funktion WURDEAUSGELASSEN nicht das gewünschte Ergebnis liefert.
Sven berichtigt mich und schreibt:
Optionale Parameter in der LAMBDA-Funktion müssen in eckigen Klammern [ ] angegeben werden!
=LAMBDA(Wert1;[Wert2];WENN(WURDEAUSGELASSEN(Wert2);Wert1*1,19;Wert1*Wert2))
=LAMBDA([Bereich];WENN(WURDEAUSGELASSEN(Bereich);“Bitte wählen Sie einen Bereich aus!“;MAX(Bereich)-MIN(Bereich)))
Man beachte [Bereich] als Parameter-Angabe in der LAMBDA-Funktion!
Hat mich auch mehrere Stunden gekostet, bis ich das rausbekommen hatte…
####
Danke für den guten und wichtigen Hinweis!
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.
Hi Rene
Wie geht es dir?
Du, ich muss mich verzweifelt bei dir melden mit einem Excel-Problem. Ich mache einen Import zu WordPress und der Kunde hat mir die Inhalte als Excel geliefert. Es geht um Schadbilder (Gärtner-Themen). Jedes Schadbild wird ein Artikel und sollte deshalb eine Zeile sein. Soweit so gut, jetzt der Kniff: Jeder Text hat Zwischentitel und diese sind aber als Spalten im Excel File angelegt. Also sind die verschiedenen Spalten nicht einzelne Felder in WordPress, sondern ein grosses Textfeld. Und die Spaltentitel sollten jeweils als Zwischentitel in diesen Texten zu finden sein. Die Zwischentitel sollten zudem ein HTML H-Tag erhalten und nicht einfach „fett und grösser“ sein.
Kannst du mir da vielleicht sagen, wie ich weiterkommen kann? Bitte sei ehrlich, wenn das deine Kapazitäten sprengt. Dann machen wir das manuell, das würde auch gehen, es sind um die 140 Artikel.
Ich gestehe – ich habe zuerst überlegt, dieses Problem mit TEXTVERKETTEN zu lösen. Als Trennzeichen hätte ich „</p><p>“ oder Ähnliches eingegeben. Aber irgendwie gefiel mir die Rechnerei nicht.
Warum nicht PowerQuery?
Klar: 1. Schritt: Liste in Tabelle verwandeln. Die Daten aus Tabelle/Bereich importieren:
Das Zauberwort heißt „entpivotieren“. Und schon habe ich eine Tabelle mit zwei Spalten: in der ersten steht die Überschrift, in der zweite die Daten aus den entsprechenden Tabellen:
Und das kann problemlos zu einer Spalte verkettet werden:
"<h1>" & [Attribut] & "</h1>#(lf)<p>" & [Wert] & "<p>"Die nicht mehr benötigten Spalten werden gelöscht, der Rest in Text konvertiert:
Und zurück nach Excel.
Man hätte die Zeilen in PowerQuery zu einem Wert zusammenfassen können – ich denke, es ist geschickter in Excel mit TEXTVERKETTEN zu erledigen.
Ihre Reaktion:
unglaublich, ich staune! Ich war mir sicher, dass du es kannst, wenn Excel es kann. Aber ich hab schon an Excel gezweifelt.
Danke vielmals!
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:
https://www.tabellenexperte.de/excel-voellig-kostenlos-wo-gibts-denn-so-was/
Hallo René,
jetzt mein kleines Excel-Phänomen, ein etwas interessantem Verhalten von Excel im Bezug auf Leerzellen, die nicht leer sind.
Natürlich habe ich deinen Artikel „Excel und das Nichts“ gelesen, ich denke, das folgende Phänomen geht in diese Richtung.
Bin mal gespannt, ob du das auch schon so gesehen hast.
Wir wollten ein paar ganz simple X-Y-Punktdiagramme erstellen bzw. ein Kollege ruft mich an, „Ich kann kein korrektes Punkt-Diagramm erstellen, das gibt’s doch nicht!“.
Der Datenbereich für die Y-Achse weist einige Leerzellen auf (die sind aber egal), die X-Achse hatte zunächst eine durchgehende Datenreihe, also ohne Lücken.
Dann wollten wir für ein 2. Diagramm eine andere Spalte als X-Achse nehmen (Spalte I). Excel erstellte nun aber nicht ein Punktdiagramm, welches die beiden Datenreihen als X-Y-Kombination zeigte, sondern „zählte“ sozusagen hoch, begann bei 1 und endete bei 54 (Ende der Datenreihe).
Beispiel: 66,67 wäre der eigentlich Wert, es wurde aber die 37 (es war der 37ste Wert in der Spalte) als X-Wert genommen.
Die Ursache war recht schnell gefunden, es gab eine leere Zelle in der Datenreihe, die aber doch nicht ganz leer war: Tippe in Zelle I16 und drücke „entf“ und beobachte, wie sich das Diagramm verändert.
Die Entstehungsgeschichte der Leerzelle, die nicht ganz leer ist, ist auch ganz interessant:
Dominic Dauphin
####
Hübsch! Sehr hübsch!
Hallo Dominic,
das „Hochzählen“ kommt daher, dass Excel keine Werte mehr erkennt, sondern Texte. Wenn du die Werte der x-Achse a, b, c, … nennst, vergibt Excel auch eine fortlaufende Nummer.
Du hast eine Funktion vergessen:
=ISTTEXT
Sie liefert WAHR!
Ich erhalte deine Zelle auch, indem ich in eine Zelle
=““
Schreibe, die Zelle kopiere und dann als Wert einfüge …
Wirklich sehr hübsch!
Liebe Grüße Rene
Hallo René,
dachte mir, ich melde mich mal wieder mit einem kleinen Office-Phänomen. Bin gespannt, ob du das schon gesehen hast (in der Regel ist die Antwort ja, aber man weiß ja nie 😉 ).
Ich habe auf einer PowerPoint-Folie ein paar simple Ring-Diagramme und ich möchte keine Führungslinie zur Datenbeschriftung. So sieht die Folie in diesem Moment aus:
Jetzt speichere ich die Datei, schließe sie, und wenn ich sie öffne, dann wandern bei 4 der 6 Diagramme wieder von selbst die schwarzen Führungslinien der Datenbeschriftung hinein.
Ich krieg diese nicht raus.
Versucht habe ich:
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:
Trotzdem strange. 😉
Viele Grüße, Dominic
####
Hallo Dominic,
ich muss immer nur ein bisschen warten, dann lösen sich die Probleme von alleine. Beziehungsweise: die Fragenden finden selbst eine Lösung.
Ich glaube, ich habe zu den (heißen die wirklich so?) Führungslinien noch keinen Artikel geschrieben. Und stimmt: DAS ist mir auch aufgefallen, dass diese Teilchen störrisch sind im Sinne von anzeigen und verschwinden. Allerdings: ich verwende sie recht selten und wenn (wie du in Punkt 1) schiebe ich so ein bisschen hin und her bis sie verschwinden. Den Algorithmus – ab wann sie auftauchen und wann nicht, kenne ich nicht.
Wenn ich alle IN EINEM Diagramm (ich weiß, ich weiß, du hast mehrere Diagramme) die Linien ausblenden will, markiere ich sie alle:
und schalte sie über die Eigenschaften aus:
LG :: Rene
Eine hübsche Frage in der letzten PowerQuery-Schulung. Ich habe einen Moment überlegen müssen.
Die Aufgabe: wir exportieren aus unserer Datenbank regelmäßig eine Liste, die wir weiterverarbeiten müssen. Allerdings benötigen wir nicht alle Spalten. Dummerweise ändern sich die Spaltennamen regelmäßig … Wie kann ich nur die Spalten behalten, die mit „p_“ beginnen?
„Oder“, schob die Kollegin hinterher – alle Spalten, deren Überschrift eine Zahl (oder keine Zahl) enthalten …:
So schwer kann das doch nicht sein, oder? Alle Feldnamen, die mit „p_“ beginnen …:
Ich brauche die Überschrift. Man kann sie „extrahieren“, indem man alle Daten löscht:
Danach die Überschriften als erste Zeile verwendet und diese Zeile vertauscht (transponiert):
Nun kann man die Überschriften mit „p_“ filtern und in eine Liste konvertieren:
Ich nenne diesen Schritt „Selektierte_Ueberschrift“.
Man wäre auch mit einer Zeile M zu diesem Ergebnis gelangt:
Table.ColumnNames
Und wie verwendet man diese Liste als Filter?
Zunächst benötigt man die Liste. Mit fx kann ein Bezug auf die Tabelle hergestellt werden:
Löscht man nun Spalten, lautet der M-Befehl:
= Table.RemoveColumns(Benutzerdefiniert1,{"p_Gender", "p_Name", "p_StreetAddress"})Entfernt man andere Spalten, lautet er:
= Table.SelectColumns(Benutzerdefiniert1,{"p_Gender", "p_Name", "p_StreetAddress"})Und das kann durch den Namen der Liste ersetzt werden:
= Table.SelectColumns(Benutzerdefiniert1,Selektierte_Ueberschrift)
oder analog:
= Table.RemoveColumns(Benutzerdefiniert1,Selektierte_Ueberschrift)
Bleibt noch die Antwort auf die Frage: „und wie entferne ich alle Spalten, die Zahlen (Ziffern) enthalten?“
Ohne M könnte man einen Filter mit zehn Kriterien anlegen:
enthält nicht 1 und enthält nicht 2 und enthält nicht 3 … – ein bisschen Klickarbeit …
Aber durchaus machbar für jede und jeden – auch ohne Programmierkenntnisse. Und mit M? – Nun – die Antwort auf die Frage: „Wie baue ich eine Schleife und lösche alle Zeilen, die nicht 1 und nicht 2 und nicht 3, … enthalten“ überlasse ich der geneigten Leserin und dem geneigten Leser!
Auf alle Fälle waren die Teilnehmerinnen der Schulung zufrieden.
Ich wohne in der Albert-Roßhaupter-Straße. Eigentlich müsste sie – nach neuer deutscher Rechtschreibung – Albert-Rosshaupter-Straße heißen. Aber der Name des SPD-Politikers wurde nicht geändert. Sei’s drum. Was passiert allerdings bei der Suche in Excel von Wörtern mit „ß“ und „ss“?
Die Funktion SVERWEIS unterscheidet; XVERWEIS allerdings nicht:
Erstaunlich! Umso erstaunlicher ist es, dass im Spanischen weder zwischen „n“ und „ñ“ unterschieden wird:
Im spanischen Excel werden auch nicht die Vokale mit und ohne Akzent unterschieden:
nicht bei BUSCARV (SVERWEIS) und nicht bei BUSCARX (XVERWEIS). Sehr erstauntlich.
Danke an Mourad Louha für diesen wertvollen Tipp!
Die Frage ist gut – die Antwort leider nicht befriedigend.
Eine Liste von Namen und eine Liste von Abteilungen.
Soweit so gut – die Zuweisung kann per Datenüberprüfung erfolgen:
Allerdings: eine Person kann für mehrere Abteilungen arbeiten (eine klassische n : m-Beziehung). Die Namen sollen untereinander aufgelistet werden. Also so:
Leider kann man über die Liste der Datenauswahl nur einen Eintrag auswählen und keine Mehrfachselektion vornehmen. Auch mit einer Formel funktioniert es nicht: „addiere zu dem vorhandenen Wert einen weiteren hinzu“ – das wäre ein klassischer Zirkelbezug.
Meine Lösung war folgende: Neben der Liste werden die einzelnen Einträge ausgewählt:
Die ausgewählten Einträge werden darunter zu einer Zeichenkette verkettet:
Die Funktion
=TEXTVERKETTEN(ZEICHEN(10);WAHR;H2:H10)löst dieses Problem.
Wichtig ist hierbei, dass der Textumbruch eingeschaltet ist, sonst sieht man den Effekt nicht.
Diese Zelle kann nun kopiert und als Wert in eine andere Zelle eingefügt werden.
Zugegeben: Nicht perfekt – aber es funktioniert!
Die Idee ist gut – sie funktioniert nur leider nicht.
Ein Teilnehmer einer Excelschulung möchte eine fortlaufende Reihe erzeugen. Er möchte, dass „Lücken übersprungen“ werden und dass die Reihe bequem fortgesetzt werden kann.
Kein Problem, oder:
Die Formel
=WENN(B2="";"";MAX($A$1:A1)+1)hilft hierbei.
Damit unter der Liste neue Daten mit einer fortlaufenden Nummer eingetragen werden können, wandle ich die Liste in eine (intelligente) Tabelle um:
Klappt.
Wird eine Zeile gelöscht:
funktioniert der Mechanismus hervorragend:
Jedoch: wird eine Zeile eingefügt:
Dann versagt der Mechanismus leider:
Was man feststellen kann, wenn man einen Namen einträgt:
Schade!
Und schon wieder eine Frage zu Outlook:
Hallo Hr. Dr. Martin,
Kann man sich in dieser Tabelle, welche die Suchergebnisse im Kalender zeigt, eine Spalte zum Anzeigen des Antwortstatus darstellen lassen?
schöne Frage, Herr L.,
Sie möchten die Besprechungen von Outlook durchsuchen und von jeder gefundenen Besprechung die Liste der eingeladenen Personen und deren Antwort sich anzeigen lassen?
Sie können sich die Liste der eingeladenen (erforderlichen) Teilnehmer anzeigen lassen und Ihren eigenen Besprechungsstatus; aber ich finde kein Feld für den Antwortstatus der eingeladenen Personen.
Ich sehe schon – bei all diesen Fragen – da fehlt noch viel in Outlook.
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“
Diese Mischung ist auch zu finden bei:
https://support.microsoft.com/de-de/office/edatum-funktion-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5
Danke, Josef, für den Beitrag!
Outlook-Schulung.
Sehr schöne Frage: wie kann ich die bedingte Formatierung so verwenden, dass Mails, die aus meiner Firma kommen, in einer Farbe dargestellt werden, Mails von extern in einer anderen Farbe.
Ich wüsste nicht, wie man das einstellen kann. Über die bedingte Formatierung kann ich nur eine Person (oder mehrere Personen) einstellen; jedoch nicht „intern“ und „extern“.
Schade, denn beim Abwesenheitsassistenten habe ich diese Möglichkeit:
Outlook ist leider (auch hier) nicht konsistent.
Nachtrag: meine Kollegin Angelika Meyer hat die Idee, dies mit einer Regel zu überprüfen. Immerhin, man kann dort alle Mails, deren Absender in „meinem“ Firmenadressbuch gefunden werden, mit einer Kategorie belegt werden und damit mit einer Farbe versehen werden:
Und die anderen?
Excel-VBA-Schulung. Wir programmieren eine eigene Funktion. Als Übung gebe ich auf eine Funktion zu schreiben, welche die Hypotenuse in einem rechtwinkligen Dreieck berechnet.
Also:
Ich tippe die Lösung, die ich mit einem „Quick & Dirty“ kommentiere:
Ich verlasse die Codezeile:
Ein Fehler ist die Folge. Darf man nicht die Funktion Sqr (Wurzel) aufrufen und im Aufruf rechnen?
Okay – dann „sauberer“ in zwei Zeilen:
c = a^2 + b^2in der Funktion
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a^2 + b^2
Hypotenuse = Sqr(c)
End Functionfalsch. Es dämmert mir. Richtig: das Caret-Zeichen (^) – der Zirkumflex – darf nicht direkt hinter die Variable geschrieben werden, da a^ zum Erstellen von Long Long-Datentypen in einer 64-Bit-Umgebung verwendet wird. Man muss ein Leerzeichen zwischen Variable und dem „Dach“ ein Leerzeichen schreiben:
Also:
Function Hypotenuse(a As Double, b As Double) As Double
Dim c As Double
c = a ^ 2 + b ^ 2
Hypotenuse = Sqr(c)
End Functionoder in der Kurzform:
Function Hypotenuse(a As Double, b As Double) As Double
Hypotenuse = Sqr(a ^ 2 + b ^ 2)
End FunctionDas funktioniert!
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.
„Guten Morgen,
ich versuche gerade eine PowerQuery-Auswertung aus den Interviewfragebogen zu erstellen.
Ich erhalte allerdings die Fehlermeldung „Die Konvertierung in Number war nicht möglich.
Was mache ich da falsch?“
Was mache ich mit so einer Mail? Richtig: ich schlage vor, mir das Ganze über teams anzusehen. Und tatsächlich:
Okay. Langsam. Von vorne bitte. Können wir uns das Ganze mal bitte in Ruhe ansehen? Was machen Sie?
In einem Ordner befinden sich mehr als 50 Excelmappen:
Jede dieser Mappen hat folgenden Aufbau:
In Spalte A befindet sich in jedem Formular eine Nummer der Form 0., 1., 2., …
Aus einigen dieser Gruppen sollen Informationen ausgelesen werden. Diese Informationen befinden sich in Spalten rechts daneben. Soweit so gut – PowerQuery ist das richtige Werkzeug hierfür. Wir schauen uns das Ganze an – Schritt für Schritt:
2. Schritt. Der Ordner wird ausgewählt; die Daten werden transformiert.
3. Schritt: Unterordner werden ausgeschlossen; andere Dateitypen ebenso:
4. Schritt: In der Spalte „Content“ befindet sich der Inhalt. Da die Spalten alle den gleichen Aufbau haben, kann man die anderen Spalten löschen und diese Spalte „entpacken“:
Da alle Dateien den gleichen Aufbau und das gleiche Tabellenblatt haben, stellt dies kein Problem dar:
Das Ergebnis:
Da Informationen aus bestimmten „Gruppen“ geholt werden, wird die erste Spalte über Transformieren / Ausfüllen „nach unten gezogen“:
Einige Spalten werden gelöscht. Aus der ersten Spalte werden einige der benötigten Spalten selektiert:
Das Ergebnis wird zurück nach Excel geschrieben (Start / Schließen & Laden / Schießen & Laden in). Obwohl die Daten in Powerquery korrekt angezeigt werden:
ist die Fehlermeldung die Folge:
Ich stutze. Zurück zu PowerQuery. Vielleicht ist „irgend etwas“ in der ersten Spalte?!? Es sieht nicht so aus:
Aber: „Die Liste kann unvollständig sein.“ Ich lasse mir über Ansicht die „Spaltenqualität“ anzeigen:
Kein Fehler in der ersten Spalte!?!
Wirklich nicht?
Wir wissen, dass PowerQuery zu Beginn nur 1.000 Zeilen auswertet. Bei 50 Formularen x zirka 150 Zeilen sind das 7.500 Zeilen. Okay – ich lasse ALLE Zeilen auswerten, indem ich auf der Statuszeile von 1.000 auf „alle“ wechsle:
Und tatsächlich: JETZT lautet die Beschriftung der Zeile „Spaltenqualität“
Unerwarteter Fehler.
Aha!
Ich gehe auf die Suche – Schritt für Schritt zurück. Schon bald ist klar, dass die Häufigkeit der Fehler unter 1% liegt:
Der Fehler tritt auf, als der Typ geändert wird. Moment – DAS habe ich doch gar nicht gemacht:
Richtig: in Datei / Optionen und Einstellungen / Abfrageoptionen lautet die Grundeinstellung:
Spaltentypen und -überschriften für unstrukturierte Quellen immer erkennen. Und richtig: Das produziert den Fehler:
[DataFormat.Error]
Aha – diese Einstellung bewirkt, dass aus 0., 1., 2., … die Zahlen 1, 2, 3, … werden. Das heißt: in einer der Dateien befindet sich wahrscheinlich in Spalte A eine andere Informationen.
Welche Datei? Zurück zum Anfang:
Ich entferne die erste und die zweite Spalte (den Dateinamen) nicht:
Bevor der Datentyp geändert wird, lasse ich mir alle Inhalte anzeigen:
Da ich die Dateinamen „sehe“, kann ich die Spalte in den Datentyp „Text“ konvertieren und den Übeltäter filtern:
Als Text erzeugt der Punkt kein Problem, allerdings bei der (automatischen) Umwandlung in Zahl.
Und dann funktioniert die Zusammenfassung problemlos:
Fazit: Vermeiden Sie – wenn möglich – die automatische Datenkonvertierung.
Verwenden Sie ALLE Daten bei der Fehlersuche.
Verwenden Sie die Werkzeuge der Registerkarte Ansicht, also: Spaltenqualität, Spaltenprofil und Spaltenverteilung.
Fürchterlich!
In einer gespeicherten Datei befindet sich eine Liste von Daten. Der Bereich wurde „Quellnymphen“ genannt.
Auf einem zweiten Tabellenblatt befinden sich Dropdownlisten (Datenüberprüfungen), welche auf die Liste über den Namen zugreifen:
Ich möchte nun beide Tabellenblätter in eine neue, schon gespeicherte (!) Datei kopieren. Da auf beiden Blättern sich eine intelligente Tabelle befindet, kann ich nicht beide Blätter markieren und kopieren:
Also einzeln. Zuerst das Blatt mit den Datenüberprüfungen und anschließend das Tabellenblatt mit den Quelldaten. Die Datenüberprüfung funktioniert und greift auf die Liste zu, die hinter den Namen liegt:
Schließt man allerdings die Quelldatei, wird die Datenüberprüfung noch angezeigt – ja – sie greift sogar noch auf die Namensliste zu:
jedoch: sie lässt sich nicht mehr öffnen!
Okay – noch einmal:
Ich kopiere erneut das Datenblatt in die andere, bereits gespeicherte Datei, anschließend das Blatt mit der Datenüberprüfung:
Der Namensmanager gibt Auskunft. Dadurch, dass zwei Blätter mit Namen (eines besitzt einen Namen, eines verwendet einen Namen) kopiert werden, wird nun zwei Mal ein Name angelegt: ein lokaler, der auf die andere Datei zugreift (?!?) und ein globaler, der aber nicht von der Datenüberprüfung verwendet wird.
Uff!
Ähnlich perfide gestaltet sich das Ganze, wenn Quelldatei und Zieldatei im gleichen Ordner liegen. Kopiert man die Tabellenblätter hinüber, speichert beide Dateien, schließt sie und öffnet die Zieldatei, lässt sich – wie oben beschrieben – die Liste der Datenüberprüfungen nicht mehr öffnen. Schließt man die Zieldatei erneut und löscht die Quelldatei, ist eine Meldung nach der verknüpften Datei die Folge:
Das bedeutet: Das Kopieren von Blättern wird hinfällig, wenn Bezüge auf Namen vorhanden sind!
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.
Das ist mir ja noch nie aufgefallen.
Ich öffne über f(x) den Funktionsassistenten und trage eine Frage ein. Beispielsweise möchte ich wissen, ob Excel nervt:
Die Antwort:
„Bitte formulieren Sie Ihre Frage neu!“
Und: ich erhalte eine neue Kategorie an Funktionen: „Empfohlen“. Leider ist diese Kategorie leer.
Ist das ein Bug? Was passiert denn da gerade?
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 = NothingIch 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“.
Ich verstehe es gerade nicht. Habe ich – in meiner aktuellen Version 2201 ein neues Feature entdeckt?
Vorgestern haben wir auf dem Excelstammtisch über folgendes Phänomen diskutiert. Trägt man in Excel längeren Text (mit Textumbruch) ein:
ist der Zeilenumbruch beim Ausdruck anders:
Auch das Verwenden eines anderen Druckers hilft nicht:
* Keiner kennt ein Tool/Add-In/Workaround, mit dem das lösbar wäre.
* Christian empfiehlt einen Blick auf:
Excel Print Preview not matching the actual printed document – Microsoft Tech Community
* 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… 🙂
Schaut mal, was ich gefunden hab: https://support.microsoft.com/de-de/topic/the-column-width-is-not-the-same-when-printed-in-excel-9756db9b-ba72-e6b8-7d94-db84e148dd85
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… 😛
Was habe ich nur angerichtet?
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:
https://www.excel-nervt.de/eines-muss-ich-meiner-muedigkeit-ja-lassen-kondition-hat-sie-ja/
Damit habe ich eine kleine Diskussion ausgelöst.
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.
Dabei war auch die Zeile
PP_app.Presentations.Add.Slides.Add Index:=1, Layout:=ppLayoutBlank
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.
Wer möchte, kann sich das Beispiel herunterladen:
https://www.excel-nervt.de/wp-content/uploads/2022/01/Formenvereinigen.xlsm
Der Autor freut sich über Lob, Kritik und Anmerkungen!
Hallo Herr Martin
Ich hoffe Sie sind gut ins neue Jahr gekommen.
Wir hatten schon einmal kontakt aufgenommen.
Folgende Meldungen bekomme:
Nach dem Debuggen bekomme ich diese Meldung:
####
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.
Liebe Grüße Rene Martin
####
Herr Martin,
erstmal Danke für Ihre Rückmeldung,
was Sie schreiben sind für mich Böhmische Dörfer.
Sorry, dass sind zu hohe Ansprüche für mich.
Ich habe Ihnen die VBA kopiert.
‚~~Begin~~#################################################################################
Sub Menue_Erstellen_ZV()
Dim MB As Object, MWMMenue As Object, Befehl As Object
Set MB = CommandBars.ActiveMenuBar
If (Menüpunkt_vorhanden(MenueName)) Then
If (Menüeintrag_vorhanden(MenueName, SubMenueName)) Then
Menueeintrag_loeschen_ZV
End If
Set MWMMenue = MB.Controls(MenueName)
Set Befehl = MWMMenue.Controls.Add(Type:=msoControlButton, ID:=1)
With Befehl
.Caption = SubMenueName
.OnAction = „ZV_ZellenVerbinden“
End With
Else
Set MWMMenue = MB.Controls.Add(Type:=msoControlPopup, Temporary:=True)
MWMMenue.Caption = MenueName
Set Befehl = MWMMenue.Controls.Add(Type:=msoControlButton, ID:=1)
With Befehl
.Caption = SubMenueName
.OnAction = „ZV_ZellenVerbinden“
End With
End If
End Sub
‚~~~END~~~#################################################################################
‚~~Begin~~#################################################################################
Sub Menue_Loeschen_ZV()
On Error Resume Next
CommandBars.ActiveMenuBar.Controls(MenueName).Delete
End Sub
‚~~~END~~~#################################################################################
‚~~Begin~~#################################################################################
Function Menüpunkt_vorhanden(Bezeichnung) As Boolean
Menüpunkt_vorhanden = False
Dim MNU As CommandBarControl
For Each MNU In Application.CommandBars _
(„Worksheet Menu Bar“).Controls
If UCase(MNU.Caption) = UCase(Bezeichnung) Then
Menüpunkt_vorhanden = True
Exit Function
End If
Next MNU
End Function
‚~~~END~~~#################################################################################
‚~~Begin~~#################################################################################
Function Menüeintrag_vorhanden(SubMenü, Bezeichnung) As Boolean
Menüeintrag_vorhanden = False
Dim MNU As CommandBarControl
For Each MNU In Application.CommandBars _
(„Worksheet Menu Bar“).Controls(SubMenü).Controls
If UCase(MNU.Caption) = UCase(Bezeichnung) Then
Menüeintrag_vorhanden = True
Exit Function
End If
Next MNU
End Function
‚~~~END~~~#################################################################################
‚~~Begin~~#################################################################################
‚Löscht den Eintrag SubMenueName im Menü „MWM“
Sub Menueeintrag_loeschen_ZV()
CommandBars(„Worksheet Menu Bar“). _
Controls(MenueName).Controls(SubMenueName).Delete
End Sub
‚~~~END~~~#################################################################################
Schon jetzt herzlichen Dank für Ihre Rückmeldung.
####
Hallo Herr P.,
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:
Sub Menueeintrag_loeschen_ZV()
On Error Resume Next
CommandBars(„Worksheet Menu Bar“). _
Controls(MenueName).Controls(SubMenueName).Delete
Probieren Sie es mal.
Klappt das?
Liebe Grüße
Rene Martin
LAMBDA zu testen ist sehr, sehr mühsam. DIe Funktion rechnet korrekt, liefert aber im Funktionsassistenten:
Svetlana Cheusheva schreibt auf ihrer Seite
https://www.ablebits.com/office-addins-blog/2021/06/16/write-recursive-lambda-function-excel/
„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.“
http://www.excel-ticker.de/die-lambda-funktion-in-excel/
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
Lat: 48,1371079 und Lon: 11,5753822.
Die vom Roten Platz in Moskau lauten
Lat: 55,7536283 und Lon: 37,6213796006738
Das kann man beispielsweise über
https://www.koordinatengps.de/
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:
Entfernung = 6378,388 * acos(sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1))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:
= 6378*ARCCOS(SIN(BOGENMASS(B2))*SIN(BOGENMASS(B3))+COS(BOGENMASS(B2))*COS(BOGENMASS(B3))*COS(BOGENMASS(C3-C2)))
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:
Und berechne nun:
= 6378*ARCCOS(REDUCE(1;B2:B3;LAMBDA(a;b;a*SIN(BOGENMASS(b))))+REDUCE(1;B2:B4;LAMBDA(a;b;a*COS(BOGENMASS(b)))))Die Formel ist etwas kürzer als die erste:
Okay – München – Moskau sind 1.962 km Luftlinie. Und nach Madrid? Ich hole die Daten aus:
https://www.koordinatengps.de/
Und trage es in meine Formel ein und …
… 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 …
Hallo Rene,
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).
Liebe Grüße und einen guten Jahresanfang!
Rene
Geht doch. Bei drei Zellen. Okay – man hätte es auch anders lösen können. Mit TEXTVERKETTEN beispielsweise.
Aber ich habe (mal wieder) gelernt, dass man sehr gut aufpassen muss: mit leeren Zellen, der Zahl 0 und keinem Text („“).
Da muss Microsoft wohl noch einmal ran. Ich versuche mich an der neuen Funktion
WURDEAUSGELASSEN
Ich erstelle die Funktion
=LAMBDA(Bereich;MAX(Bereich)-MIN(Bereich))
Und speichere sie im Namensmanager unter dem Namen „Spannweite“:
Ich teste sie – es funktioniert:
Ich ändere die Funktion
=LAMBDA(Bereich;WENN(WURDEAUSGELASSEN(Bereich);"Bitte wählen Sie einen Bereich aus!";MAX(Bereich)-MIN(Bereich)))
Die Funktion ohne Bereich:
Nicht das gewünschte Ergebnis!
Auch im Englischen funktioniert ISOMITTED nicht …
Ohne zu spicken – kennst du die Antwort?
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:
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.
Ein bisschen doof ist es schon:
Ich habe eine (intelligente) Tabelle.
Ich füge eine Ergebniszeile hinzu – dort wird gerechnet:
Ich entferne die Farben und wandle sie in eine Liste um:
=TEILERGEBNIS(109;Tabelle1!$E$2:$E$10)
bleiben stehen. Ebenso die Beschriftung der ersten Zelle: „Ergebnis“:
Wandelt man diese Liste nun erneut in eine Tabelle um:
ist die Formelzeile nun Teil der Tabelle.
Man kann erneut eine Ergebniszeile hinzufügen – das Ganze ist nun recht verwirrend:
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 …)
Guten Tag Herr Dr. Martin,
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.
Schöne Grüße
Rene Martin
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.
Recht hat er, denke ich und lache.
Verblüfft. Ich erstelle eine Pivottabelle und möchte die Jahreszahlen gruppieren:
Eine Fehlermeldung ist die Folge:
Kann den markierten Bereich nicht gruppieren.
Okay? – und warum?
Ein Blick in die Daten liefert die Lösung: die Jahreszahlen sich als Text formatiert?!!?!
In Zahlen umwandeln – dann klappt es …
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.
Christian ist irritiert. Ich auch.
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!
Danke an Christian Gröblacher für diesen Hinweis.
Merkwürdig. In einer Zelle steht eine Formel:
=SUMMENPRODUKT((JAHR(Tabelle2[Aktionsstart])=2021)*(Tabelle2[Carrier für den Versand]="contoso")*(Tabelle2[Gesamtmenge]))Ich benötige die Formel und lasse sie vom Makrorekorder aufzeichnen:
ActiveCell.Formula2R1C1 = _
"=SUMPRODUCT((YEAR(Tabelle2[Aktionsstart])=2021)*(Tabelle2[Carrier für den Versand]=""contoso"")*(Tabelle2[Gesamtmenge]))"
Baue sie etwas um.
Der Kunde beschwert sich über einen Fehler. Der Grund: die Eigenschaft
Formula2R1C1
ist noch nicht in Excel 2013 verfügbar *ggrrrr*
Warum zeichnet Excel nicht
ActiveCell.FormulaR1C1 = auf?
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?
Hm. Irgendwie doof. Ich habe einen Bereich in Excel, in dem eine Spalte mit dem Zahlenformat „Text“ formatiert ist:
Wandelt man die Liste in eine (intelligente) Tabelle um, wird beim Einfügen einer neuen Zeile das Textformat nicht in die neue Zeile übernommen …
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
* die leeren Spalten gelöscht.
Seltsam.
Ich zeichne mit dem Makrorekorder eine lange Formel auf. Der Makrorekorder liefert am Ende der vorletzten Zeile:
Und am Beginn der nächsten (letzten) Zeile:
Das Hinzuverketten des leeren Textes in einer eigenen neuen Zeile ist überflüssig. Seltsam!
Schade! In PowerPoint kann man „Formen zusammenführen“, also: vereinigen, kombinieren, in Einzelmengen zerlegen, Schnittmengen bilden oder subtrahieren:
So kann man leicht neue Formen erstellen:
In Excel leider nicht …
Das ist mir ja noch nie aufgefallen!
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:
Das ist irgendwie doof!
Boah, was ist denn das? Ich bin sehr irritiert!
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?
Die Formel
=FILTER(Tabelle1!$A$2:$O$12384;Tabelle1!$N$2:$N$12384=A3)tut gute Dienste:
Alle Spalten werden geliefert. Ich benötige aber nur die Namen. Also wird reduziert:
=FILTER(Tabelle1!$D$2:$D$12384;Tabelle1!$N$2:$N$12384=A3)
Allerdings sollen die Daten ja nicht untereinander, sondern in einer Zelle stehen. Also muss man die Texte verketten. Die Funktion TEXTVERKETTEN hilft hierbei:
=TEXTVERKETTEN(ZEICHEN(10);WAHR;FILTER(Tabelle1!$D$2:$D$12384;Tabelle1!$N$2:$N$12384=A3))
Das Ergebnis verblüfft. Klar – man muss noch den Textumbruch einschalten:
Herunterziehen – und wieder ein Erstaunen:
Das müssten doch mehr Namen sein! – Klar: man muss die optimale Zeilenhöhe aktivieren. Doppelklick – dann funktioniert es:
Und so wird der Rest ausgefüllt. Allerdings – bei den Währungen und Datumsangaben muss man sich noch mit der Funktion TEXT behelfen:
=TEXTVERKETTEN(ZEICHEN(10);WAHR;"€ "&TEXT(FILTER(Tabelle1!$H$2:$H$12384;Tabelle1!$N$2:$N$12384=A3);"#.##0,00"))
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 <=
=TEXTVERKETTEN(ZEICHEN(10);WAHR;TEXT(FILTER(Tabelle1!$K$2:$K$12384;(Tabelle1!$N$2:$N$12384=A3)*(Tabelle1!$L$2:$L$12384<=DATUM($L$2;12;31))*(Tabelle1!$L$2:$L$12384>=DATUM($L$2-100;1;1)));"TT.MM.JJJJ"))
Und so kann man auf Basis dieser Tabelle einen Serienbrief erstellen.
Die Bank wird einmal aufgelistet; die Namen alle einzeln untereinander:
Und das Ganze in der Vorschau:
Ein Stückchen Arbeit – aber ein Mensch war glücklich und ich zufrieden.
Übrigens: die Matrixfunktionen FILTER, EINDEUTIG, SEQUENZ & co sind seeeehhhhhr langsam. Beim Herunterziehen meldet Excel:
Und benötigt leider seeeeeehhhhhhhr viel Zeit:
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:
Ich lasse das Makro laufen:
Das Ergebnis verblüfft:
Und noch einmal:
Und wieder:
Und wieder. Und wieder. Und wieder ….
Ich schaue im Code nach:
[...]
With ActiveSheet.PageSetup
.LeftHeader = "&P / &N"
.CenterHeader = "&D / &T"
.RightHeader = "&Z&F / &A"
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.
Scheinbar klappt dies nicht richtig.
Salü
Ernst
Ich verstehe Excel ab und zu nicht.
Seit letzter Woche habe ich in der Kategorie „Text“ die neue Funktion „MATRIXZUTEXT“. Sie wandelt die Werte eines Bereichs in eine Zeichenkette um:
Ich sehe keinen Unterschied zur Funktion TEXTVERKETTEN.
Okay – es gibt einen zweiten Parameter – trägt man beim Parameter Format den Wert 1 ein, erfolgt die Darstellung des Textes als:
{„Nr“.“Länge“.“Name“.“Kontinent“.“Quellgebiet“.“Mündung“.“Einzugsgebiet“.“Mittlerer Abfluss“;1.6852.“Nil“.“Afrika“.“Ruandaberge“.“Mittelmeer“.3254853.2660;2.6448.“Amazonas“.“Südamerika“.“Anden, Peruanische Ostkordillere“.“Atlantischer Ozean“.6112000.206000;3.6380.“Jangtsekiang“.“Asien“.“Tibet“.“Ostchinesisches Meer“.1722155.31900;4.6051.“Mississippi“.“Nordamerika“.“Rocky Mountains“.“Golf von Mexiko“.2981076.18400;5.5540.“Jenissei“.“Asien“.“Sajangebirge“.“Arktischer Ozean“.2554482.19600;6.5410.“Ob“.“Asien“.“Mongolischer Altai“.“Obbusen“.2972497.12500;7.5052.“Amur“.“Asien“.“Chentii-Gebirge“.“Ochotskisches Meer“.2400000.11400;8.4845.“Gelber Fluss“.“Asien“.“Bayan-Har-Gebirge“.“Gelbes Meer“.752000.2570;9.4835.“Kongo“.“Afrika“.“Südlich des Tanganjikasees“.“Atlantischer Ozean“.3730474.41800;10.4500.“Mekong“.“Asien“.“Tibet“.“Südchinesisches Meer“.795000.15000}
Es bleibt die Antwort auf die Frage: „Und wer braucht das?“
Seltsame Ereignisse in der letzten Excelschulung.
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:
=WENN(Datenblatt_Logistikdaten!$B$40="";"";WENN(UND(Datenblatt_Logistikdaten!$B$39="Paket";Datenblatt_Logistikdaten!$B$40="DHL Paket");Datenblatt_Logistikdaten!L16; WENN(UND(Datenblatt_Logistikdaten!$B$39="Paket";Datenblatt_Logistikdaten!$B$40="Hermes Paket");Datenblatt_Logistikdaten!M16; WENN(UND(Datenblatt_Logistikdaten!$B$39="2MH";Datenblatt_Logistikdaten!$B$40="Hermes 2-MH");Datenblatt_Logistikdaten!L47; WENN(UND(Datenblatt_Logistikdaten!$B$39="2MH";Datenblatt_Logistikdaten!$B$40="AO");Datenblatt_Logistikdaten!M47; WENN(UND(Datenblatt_Logistikdaten!$B$39="Spedition";Datenblatt_Logistikdaten!$B$40="DSV");Datenblatt_Logistikdaten!L30; WENN(UND(Datenblatt_Logistikdaten!$B$39="Spedition";Datenblatt_Logistikdaten!$B$40="Hellmann");Datenblatt_Logistikdaten!M30;"")))))))
Ich zeichne mit dem Makrorekorder auf und stutze:
Ein seltsamer Umbruch! Noch erstaunlicher ist er am Ende der Zeile:
Der Makrorekorder bricht die Codezeile nach einer bestimmten Anzahl von Zellen um, was sehr merkwürdig wirkt:
ActiveCell.FormulaR1C1 = _
"=IF(Datenblatt_Logistikdaten!R40C2="""","""",IF(AND(Datenblatt_Logistikdaten!R39C2=""Paket"",Datenblatt_Logistikdaten!R40C2=""DHL Paket""),Datenblatt_Logistikdaten!R[-41]C[8], IF(AND(Datenblatt_Logistikdaten!R39C2=""Paket"",Datenblatt_Logistikdaten!R40C2=""Hermes Paket""),Datenblatt_Logistikdaten!R[-41]C[9], IF(AND(Datenblatt_Logistikdaten!R39C2=""2MH"",Datenblatt_L" & _
"ogistikdaten!R40C2=""Hermes 2-MH""),Datenblatt_Logistikdaten!R[-10]C[8], IF(AND(Datenblatt_Logistikdaten!R39C2=""2MH"",Datenblatt_Logistikdaten!R40C2=""AO""),Datenblatt_Logistikdaten!R[-10]C[9], IF(AND(Datenblatt_Logistikdaten!R39C2=""Spedition"",Datenblatt_Logistikdaten!R40C2=""DSV""),Datenblatt_Logistikdaten!R[-27]C[8], IF(AND(Datenblatt_Logistikdaten!R39C2=""Sped" & _
"ition"",Datenblatt_Logistikdaten!R40C2=""Hellmann""),Datenblatt_Logistikdaten!R[-27]C[9],"""")))))))" & _
""Nun ja – das kann (und sollte) man ja korrigieren!
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:
=WENN(0<=A2<45;“Nord“;WENN(45<=A2<135;“Ost“;WENN(135<=A2<225;“Süd“;WENN(225<=A2<315;“West“;WENN(315<=A2<380;“Nord“;“x“)))))
Wie Ihr auf dem Screenshot sehen könnt, zeigt mir Excel aber nur das „alternative“ x an… Ich kann mir aber nicht erklären warum?
Excel kennt kein „zwischen“. Du darfst nicht schreiben
0<=A2<45
sondern mit UND:
=WENN(UND(0<=A2;A2<45);“Nord“;WENN(UND(45<=A2;A2<135);“Ost“;WENN(UND(135<=A2;A2<225);“Süd“;WENN(UND(225<=A2;A2<315);“West“;WENN(UND(315<=A2;A2<380);“Nord“;“x“)))))
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:
Sehr versteckt!
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.Contentsverwendet; 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:
Ich beginne mit dem Löschen der Kopfzeile.
ActiveDocument.Sections(i).Headers(wdHeaderFooterFirstPage).Range.DeleteObwohl 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 …
Böse! Ganz böse!
Hallo Rene, hier Andre :-)!,
Ich hatte eine Frage zu Excel.
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:
Workbooks.Open FileName:=AblagepfadBanfVorlage + DateinameBanfVorlageJetzt 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:
Application.EnableEvents = False
Workbooks.Open FileName:=AblagepfadBanfVorlage + DateinameBanfVorlage
Application.EnableEvents = True
Aber das funktioniert nicht.
hast Du da vielleicht eine schnelle Idee.
####
Hallo André,
ich würde die MELDUNGEN (nicht die Makros!) unterdrücken mit:
Application.DisplayAlerts = False
und danach wieder die Warnmeldungen einschalten (= True)
Liebe Grüße
René
Hallo Rene,
Hallo Rene,
schöne Grüße an dich. Als ich den Artikel über die negative Null gelesen habe (https://www.excel-nervt.de/entweder-sie-geben-mir-eine-gehalterhoehung-oder-erzaehle-500-kollegen-ich-haette-eine-bekommen/) , ist mir eingefallen, dass ich ebenfalls Versuche zu diesem Thema gemacht habe. Tatsächlich ist es möglich in eine Excelzelle eine negative Null einzutragen
Dazu verwende ich folgende kleine Funktion.
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.
Schöne Grüße
bleib gesund
Ernst
Excel – aber so was von retro …
https://www.youtube.com/watch?v=kOO31qFmi9A&t=5s
my spreadsheet does’t do that!
Lieber René,
kennst Du eine negative Null?
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
Rene
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“)
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:
Excel schwächelt.
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:RDExcel 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
=Tabelle1!Z:DFDanke an Volker Pagel für diesen Hinweis.
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:
Dann funktioniert auch das.
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:
=HÄUFIGKEIT(B2:K11;M2:M7)
Also baue ich nach:
=ZÄHLENWENNS($B$2:$K$11;">"&M1;$B$2:$K$11;"<="&M2)Die erste Zelle wird falsch berechnet – klar – weil in der Zelle Text steht:
Also lösche ich die Überschrift:
Das Ergebnis ändert sich nicht.
Klar, denn der Formelteil
">"&M1wird nicht ausgewertet als >0, sondern nur als >.
Also doch: eine 0 einfügen – dann klappt es:
Das ist mir ja noch gar nicht aufgefallen. Volker hat darauf aufmerksam gemacht:
In einer Excelmappe befindet sich ein Tabellenblatt („Kontinente“) mit sechs intelligenten Tabellen: tbl_Europa, tbl_Antarktis, tbl_Afrika, …
Die Datei wird einmal als Excel-Arbeitsmappe (XLSX) und einmal Excel-Binärarbeitsmappe (XLSB) gespeichert.
Greift man mit PowerQuery auf die XLSX-Mappe zu, ist das Ergebnis bekannt: angezeigt wird das Tabellenblatt und die sechs intelligenten Tabellen:
Beim Zugriff auf die XLSB-Datei dauert der Zugriff nicht nur sehr, sehr lange – angezeigt wird nur das Tabellenblatt:
Danke an Volker Pagel für diesen Hinweis.
Sein Fazit: Don’t use xlsb!
Seine Kollegen kommentieren es:
Andreas:
Vielleicht ist xlsb dem alten Format xls zu ähnlich. Die xls aus 2003 lässt sich auch nicht einlesen.
Jens:
Volle Zustimmung!
Über Excel.Workbook ([Content]) werden xlsb auch nicht erkannt.
Ist echt nen Problem…xlsb ist bei uns relativ beliebt, da xlsm nicht per Mail versendet werden kann. Makro sind BÖSE
Aber xlsb kann auch Makros enthalten.
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.
Hallo zusammen,
hat jemand eine Idee? Warum funktioniert
=WENN(E10="FALSCH"; ...nicht?
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:
=WENN(E10=FALSCH; ...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:
Das kann man über Pivottable-Analyse / Datenquelle ändern herausfinden.
Habe ich etwas vergessen? Sicherlich … Ich fand die Frage sehr interessant …
Ich benötige den Code einer Farbe einer Zelle von Excel. Also schreibe ich:
MsgBox "Color:" & ActiveCell.Interior.Color & vbCr & "ColorIndex: " & ActiveCell.Interior.ColorIndexDas Ergebnis:
Ich teste an einer anderen Zelle:
Und schließlich:
Drei Mal die gleichen Werte ???
Es dämmert mir …
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!
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:
Und DAMIT funktioniert es nun auch bei ihr:
Einfach nicht aufgepasst!
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:
=SUMMENPRODUKT((A2:A40=G9)*1;(B2:B40);(C2:C40);(D2:D40<>"Hinkelstein")*1)/SUMMEWENNS(B2:B40;A2:A40;G9;D2:D40;"<>Hinkelstein")
Gerechnet wird also:
(10 x 31 + 40 x 50) / (10 + 40) = 46,2
Und diese Formel kann man mit dem Makrorekorder aufzeichnen und über alle Zellen „laufen lassen“. Das Ganze wird in der Datei Personal.xlsb gespeichert.
Guten Morgen,
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.
Liebe Grüße
Rene
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:
Einfach übersehen. PowerQuery-Schulung. Wir wollen auf einen Sharepoint-Ordner zugreifen:
Geht aber nicht. Ich frage meinen Freund und Kollegen Hans-Peter Pfister um Rat. Seine Antwort:
Hoi René
Nur kurz, ohne viel drum-herum, bin gerade unter Wasser.
Nimm den SharePoint Ordner Konnektoren, nicht den für SP-Liste.
Das Leben kann manchmal so einfach sein!
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 …
Angelika will’s wissen. Sie möchte gerne die Koordinaten von München
48° 8' 6.45" N 11° 34' 55.132" E durch Formatieren von 48080645 und 113455132 erhalten:
Also verwendet sie das benutzerdefinierte Zahlenformat
##.° ##' ##.##''und erhält leider
Okay – Probleme mit dem Punkt. Also noch einmal:
Also verwendet sie das benutzerdefinierte Zahlenformat
##.° ##' ## . ##''Klappt, aber ist nicht schön, weil Lücke vor und nach dem Punkt:
Die Lösung: sie muss den Punkt entwerten. Der Backslash tut hier gute Dienste:
##° ##' ##\.##''
Und auch die zweite Zahl:
Die beiden Buchstaben „N“ und „E“ hinzuzufügen stellt kein Problem mehr dar.
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. Die Frage kenne ich: kann man eine Dropdownliste, die man per Datenüberprüfung erzeugt hat, sortieren:
Die Antwort lautet: „NEIN“. Dafür habe ich mal ein Werkzeug gebaut, das solche Listen sortiert und mit dessen Hilfe man bequem auswählen kann.
Sie finden es auf meiner Homepage:
https://www.compurem.de/?page_id=559
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.
Hallo zusammen,
ich möchte folgendes in Excel durchführen.
Ich habe verschiedene Spalten in Excel mit Daten.
Spalte A:
Name
Spalte B:
Vorname
Spalte C:
Geburtsdatum
Spalte D:
Organisation
Excel soll mir jetzt die Einträge markieren die doppelt sind, jedoch nur wenn Name und Vorname identisch sind sprich Spalte A UND B.
Wie kann ich das am besten lösen.
Doppelte Werte anzeigen ist ja an sich nicht schwierig jedoch weiss ich nicht wie ich in dem o.g. Fall vorgehen muss.
Habt ihr da zufällig eine Lösung
Hallo Jörg,
hast du XVERGLEICH?
Dann könntest du es mit folgender Formel in der bedingten Formatierung lösen:
=WENNNV(XVERGLEICH($A2&$B2;$A3:$A$27&$B3:$B$27;0);0)+WENNNV(XVERGLEICH($A2&$B2;$A1:$A$1&$B1:$B$1;0);0)
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!