Ich habe gelacht. Eine Teilnehmerin erzählte mir, dass sie für ihre Kollegen eine Tabelle eingerichtet hat. Darin befindet sich eine Spalte „Erledigt“, über welche gekennzeichnet wird, ob dieser Prozess bereits erledigt ist:
Diese Datei wird nun mit Hilfe von PowerQuery weiter verarbeitet.
Nun gibt es einige sehr „clevere“ Kollegen, die den Erledigt-Status kennzeichnen, indem sie die Inhalte der Zellen durchstreichen:
Eine Teilnehmerin in der Excelschulung fragte mich, warum auf einem Rechner folgendes funktioniert, auf einem anderen nicht:
Normalerweise bewirkt ein Klick auf eine Zelle in der Pivottabelle, dass die Formel
=PIVOTDATENZUORDNEN
erzeugt wird:
Bei ihr jedoch nicht:
Auch das Eintippen der Formel hilft nicht – Intellisense versagt:
Des Rätsels Lösung war schnell gefunden: sie hatte in den Optionen / Formeln die Option „GetPivotData-Funktionen für PivotTable-Bezüge verwenden“ ausgeschaltet.
Das hatten wir doch schon einmal. PowerBI unterscheidet beim Zugriff auf Access-Datenbanken zwischen der 32-Bit und 64-Bit-Version.
Nicht nur dort – auch bei XLS-Dateien wird unterschieden. So kann ich auf einem Rechner diese Datei nicht ins Datenmodell laden ohne das Add-In zu installieren:
Auf dem anderen Rechner funktioniert es problemlos:
Kennst ihr DPQ-Dateien? Das sind Textdateien, in denen ein Query auf eine Datenbank gespeichert sind. Diese Data-Query kann man in Excel einbinden.
Soweit so gut.
Nun wollte ich so eine Datei in Power BI einbinden.
„Einen direkten Konnektor über diese Datenverbindung gibt es zur Zeit in Power BI Desktop nicht.“ schreibt Frank Arendt-Theilen. Hans-Peter Pfister hat es bestätigt. Schade.
Ach wie doof! Wenn man in PowerBi in das Visual Tabelle oder Matrix Werte (oder besser: ein Measure) in die Werte zieht, kann man mit einem Klick auf den Spaltenkopf danach sortieren:
Verwendet man jedoch eine Kategorie in den Spalten, kann man damit nicht sortieren – auch nicht mit gedrückter [Strg]-Taste:
Mit einem Klick wird die Spalte selektiert (und damit die anderen Visuals gefiltert).
Hallo Hr. Martin. Wie kann ich in VBA prüfen, ob ein Textfeld als Formularsteuerelement das mit einer Textmarke versehen ist (z Bsp.“TMThemaPos16″) leer ist. Laut Lokal-Fenster soll es ein String mit 5 Leerzeichen sein. Diese Prüfung wird aber ignoriert. Auch auf Empty prüfen bringt keinen Erfolg. Ich muss den Inhalt der Textmarken in eine Excel überführen, die stetig erweitert wird. Es können 30 Themen ins Formular eingetragen werden. Ab einer leeren Textmarke der Kategorie „Thema“, soll die Prozedur beendet werden. Vielen Dank für ihre Unterstützung.
###
Hallo Herr W.,
Wenn ich Ihr Problem richtig verstanden habe, würde ich das folgendermaßen lösen: Formularsteuerelemente haben keinen Namen und können deshalb nicht direkt angesprochen werden. Hinterlegen Sie bei der Eigenschaft „Tag“ einen Text. Durchlaufen Sie mit einer Schleife alle ContentControls und überprüfen Sie, ob das ContentControls(i) den Tag = „yxz“ hat. Wenn ja, dann können Sie den Text auslesen: ActiveDocument.ContentControls(1).Range.Text Ist das die Antwort auf Ihre Frage?
LG und ein schönes Wochenende
Rene Martin
###
Hallo Hr. Martin.
Ich hab das jetzt über die ASC-Funktion gelöst. Frage somit ab, welches ASCII Zeichen die Textmarke hat und wenn diese (in meinem) Fall 32 ist, dann ist sie leer.
Schöne Frage in der letzten Outlook-Schulung zum Thema Kontakte und Adressbuch: die „erweitere Suche“ war bekannt. Darüber kann man im Adressbuch nach Kolleginnen und Kollegen suchen, wenn man nur den Vornamen weiß. Oder die Abteilung. Oder den Standort:
Wie man aber eine Kontaktgruppe (Verteilerliste) nach Standort durchsuchen kann, will eine Teilnehmerin wissen. Besser noch: Alle Kontaktgruppen.
Peter schickt mir eine Mail und fragt mich, warum die Sortierung in Excel nicht funktioniere.
Natürlich funktioniert sie, lautet meine Antwort. Er solle mir die Datei zusenden; ich würde sie mir ansehen. Ich vermutete eine leere Spalte oder Zeile zwischen den Daten.
Er erklärt mir:
Sortiert man die Liste nach dem Namen ist alles prima:
Sortiert man sie nach dem Ort, funktioniert es auch:
Jedoch bei der Sortierung nach der ID stimmt die Zuordnung zum Länderkennzeichen und dem Land nicht mehr:
Ich sehe mir die Liste genau an – die Lösung ist bald gefunden: Auf halber Strecke wurde ein Teil der Liste nach unten verschoben, das heißt: der linke Teil befindet sich eine Zeile tiefer als der rechte. So kann es gehen:
Hallo Herr Martin, seit einigen Jahren schaue ich mir regelmäßig Ihre Videos an und muss sagen, ich habe durch Sie wirklich viel gelernt. Summenprodukte, XVerweise und weitere Formeln gehören zu den Formeln, die ich jede Woche – wenn nicht jeden Tag – nutze. Ich habe derzeit ein Problem eine Excel-Datei zu speichern. Darin stecken 10 Stunden Arbeit, die verloren gehen, wenn ich die Datei nicht gespeichert bekomme. Excel schlägt mir immer vor, dass die Datei repariert werden könnte. Ich weiß allerdings schon, dass die Reparatur fehlschlagen wird und diverse Teile meiner Inhalte gelöscht oder umformatiert werden, wenn ich die Reparatur durchführen würde. Haben Sie einen Tipp, wie man jede Datei speichern kann, auch wenn Excel nicht clever genug dazu ist? Noch habe ich meine Datei geöffnet, aber über Nacht wird mein Firmen-Notebook neustarten und dann ist der Inhalt leider verloren 🙁 Viele Grüße aus Berlin!
####
uh, das hört sich schrecklich an! Hallo Herr S., können Sie die Datei in einem anderen Format (XLSB oder XLS) speichern? Können Sie die Datei auf einen anderen Datenträger speichern? USB-Stick? Können Sie alle Tabellenblätter markieren (rechte Maustaste) und alle in eine andere Datei kopieren und diese Daten dann speichern? (Achtung: möglicherweise haben Sie dann Bezüge auf die erste Datei, die sich nicht speichern lässt) Ist denn die Autospeichern-Funktion aktiviert? Im schlimmsten Fall zumachen – meistens (leider nicht immer) wird die Datei ziemlich gut geöffnet. (Stoßgebet zum Himmel nicht vergessen!)
Uff! – Sonst fällt mir nichts ein … Ich drücke die Daumen LG :: Rene Martin
####
Hallo Herr Martin, ich habe es über ein VBA-Makro gelöst, welches die (vielen) Worksheets in eine neue Datei kopiert hat und die Datei anschließend speichern konnte. Die Referenzen waren dann defekt (es wurde auf Tabellenblätter der defekten Datei verwiesen), aber das habe ich dann manuell mit Suchen+Ersetzen korrigiert.
Vielen Dank für den Tipp!!
####
Hier das Makro:
Sub CreateBackupWorkbook()
On Error Resume Next
Dim BackupName As String
BackupName = ThisWorkbook.FullName & " backup.xlsb"
Dim NewWorkbook As Workbook
Set NewWorkbook = Workbooks.Add
Dim CurrentSheet As Worksheet
For Each CurrentSheet In ThisWorkbook.Worksheets
CurrentSheet.Copy After:=NewWorkbook.Sheets(NewWorkbook.Sheets.Count)
Next CurrentSheet
NewWorkbook.SaveAs BackupName, FileFormat:=xlExcel12
NewWorkbook.Close False
On Error GoTo 0
End Sub
Klasse, dass Sie es retten konnten. Und ja – so etwas Ähnliches hatte ich mal bei Visio (leider tragisch): Eine gespeicherte Datei ließ sich nicht mehr öffnen! Keiner der Versuche war von Erfolg gekrönt – ein Arbeitstag war weg! Wie sagte mal ein Freund: „beim zweiten Mal geht es immer schneller“. Aber Sie hatten ja Glück. LG :: Rene Martin
ich hoffe, es geht dir gut und du bist schon auf dem Weg ins Wochenende. 😊
Ich schreibe dir weil ich verzweifelt bin, da mich diese Sache heute einiges an Zeit gekostet hat.
in Word eingebettete Excel Tabellen lassen sich nicht mehr öffnen.
Excel ist installiert, wir haben verschiedene Add-Ons deaktivieren, etc. Auch das Konvertieren des Objekts in verschiedene Versionen bringt nichts.
Die eingebettete Tabelle lässt sich nicht öffnen.
Auch wenn ich eine neue Word-Datei mit einer neuen Tabelle erstelle, ist diese nur solange bearbeitbar bis ich die Word-Datei schließe.
Danach läuft im Hintergrund wohl eine sogenannte OLE-Aktion weiter. Denn ich erhalte permanent die Fehlermeldung, dass Excel auf das Ende der OLE-Aktion wartet.
Es ist nicht dringend, da du aber schon mal eine schnelle Lösung parat hattest, wo alle anderen ratlos waren, dachte ich, ich frag dich gleich. Bitte verwende aber keine großen Bemühungen darauf.
Ich habe meine Anfrage nun an unseren IT Provider für das DMS weitergegeben, nachdem auch mithilfe unserer IT für Windows/Office nicht weitergekommen bin. Auch googeln hat nicht geholfen. Es gibt zwar mehrere Einträge dazu aber die Lösungen dort haben bei uns nichts gebracht.
Jetzt wollte ich mal anfragen, ob dir das schon einmal untergekommen ist und du vielleicht gleich eine Info parat hast.
Wenn nicht, warte ich erstmal, was das DMS Team zu sagen hat.
LG und großes Dankeschön
Janet
###
Hallo Janet,
ich kann die Datei öffnen:
Noch einmal zum Mitschreiben:
* Wo liegt die Datei? Sharepoint? OneDrive? Teams?
* Macht ihr sie in Desktop-Word auf oder Word online?
Wenn nur zwischen der Anzeige des Inhaltverzeichnistextes und der TOC-Feldfunktion geschaltet werden soll, muss die Alt-F9-Taste verwendet werden.
Wird die Shift-F9-Taste verwendet, kommt es darauf an was im Inhaltverzeichnis markiert ist oder wo der Cursor steht. Ist alles markiert wird auch zwischen Textanzeige und TOC-Feldfunktion umgeschaltet.
Steht der Cursor am Anfang einer Zeile, wird zwischen der aktuellen Anzeige (das betrifft auch die Anzeige einer Hyperlink Feldfunktion) und der TOC-Feldfunktion geschaltet.
Steht der Cursor aber in einer Zeile, wird bei Shift-F9 zwischen der Textanzeige der Zeile und der Anzeige der Hyperlink-Feldfunktion geschaltet. Wobei es einen kleinen aber wichtigen Unterschied gibt. Bei der Anzeige der Feldfunktion bleibt der Cursor in der Zeile stehen. Bei der Textanzeige springt der Cursor zum Zeilenanfang.
Ein sofortiges drücken der Shift-F9-Taste zeigt deshalb ein unterschiedliches Verhalten.
Bei meinen Versuchen ist mir dabei eine Besonderheit aufgefallen. Vielleicht kennst du dieses Verhalten schon.
Wenn die erste Feldfunktion in einem markierten Text gegen Aktualisierung gesperrt ist (Strg-F11), werden auch alle anderen Feldfunktion bei Betätigung der F9-Taste nicht aktualisiert. Ist die erste Feldfunktion eines Dokument gesperrt, kann dies zu folgenden Problem führen. Nach einer vollständigen Markierung dieses Dokumentes (Strg-a) und anschließendes Drücken der F9-Taste, wird keine Feldfunktion aktualisiert.
In der Vergangenheit habe ich in Outlook regeln festgelegt die dann aber verschwunden waren als ich einen neuen PC bekommen habe etc.
Wie kann ich Regeln in Outlook PC übergreifend einrichten sodass diese auch funktionieren wenn ich einen neuen PC bekommen?
Gruß Martin
####
Sorry, Martin, aber das geht meines Wissens nicht. Einige Dinge werden in Outlook an den Rechner gebunden. Ich denke nicht, dass man sie exportieren kann oder rechnerübergreifend festlegen kann. LG :: Rene
Eigentlich wollte ich nur einen Befehl mit Office Skript aufzeichnen: formatiere den Text einer Zelle fett:
Geht nicht:
„Leider ist ein Problem aufgetreten. Wir können Ihr Skript nicht speichern. Unerwarteter Fehler beim Generieren und Speichern des Skripts. Klicken Sie auf „Aktionen aufzeichnen“, um erneut aufzuzeichnen.
Erstaunt starre ich auf den Bildschirm. Ich habe keine Ahnung, warum und woher das Problem rührt.
ich habe mal wieder eine Aufgabe (für Sie bestimmt eine Kleinigkeit), die ich als PDF-Formular nicht vollständig gelöst bekomme, aber auch als EXCEL-„Formular“ möglicherweise zu lösen wäre:
„Ich sende an unsere Partner ein Formular mit 6 Dekor-Beispielen. Jedes der 6 Dekor-Beispiele soll mit einer Note von 1 – 6 bewertet werden.
Dabei soll jede Note nur einmal vorkommen, sodass eine eindeutige Bewertungs-Reihenfolge entsteht. Bis jetzt habe ich 6 kleine Zahlengruppen (von 1 – 6) jeweils mit Optionsfeldern belegt.
Wie kann ich verhindern, dass z.B. das Dekor 2 und das Dekor 5 versehentlich beide mit der Note 1 bewertet werden? …oder anders herum, versehentlich 2 Bewertungen in einer Zahlengruppe eingegeben werden
Ich würde mich riesig freuen, wenn Sie mir hierzu einen Lösungstipp auch in EXCEL geben könnten.
Ganz herzlichen Dank vorab
Christoph
####
Hallo Christoph,
ich würde in jeder Zelle mit der Datenüberprüfung die Werte 1 … 6 zulassen. Dann würde ich mit einer bedingten Formatierung die Zellen rechts neben jeder Zelle überprüfen, ob einer der Werte dem Wert der Zelle entspricht. Beispielsweise mit: =UND(ODER(A1=B1:F1);A1<>““)
Ich öffne eine uralte Datei, die noch im XLS-Format vorliegt:
Ich speichere sie als XLSX.
Und erstelle eine Pivottabelle:
Aber irgendwie sieht die komisch aus.
Auch als ich weiterarbeite, „fühlt“ sich das Ganze sehr seltsam an:
Ups – und meine Diagramme – wo sind meine Diagramme?
Dann dämmert es mir: Nach dem Konvertieren ins XLSX-Format muss ich die Datei schließen und erneut öffnen – sonst verbleibt sie im Kompatibilitätsmodus:
Werden in PowerQuery Spalten mit einem Verkettungsoperator „&“ zusammengefügt und befindet sich in einer der Zellen der Wert null, dann ergibt Inhalt & null -> null:
Nur wenn alle Zellen mit Text gefüllt waren, wird das Ergebnis angezeigt.
Anders dagegen der Assistent „Spalten zusammenführen“.
Trägt man in Excel ein zehnstellige Zahl ein, beispielsweise 1234567890 oder 5432167890 und zieht diese mit gedrückter [Strg]-Taste herunter wird weitergezählt:
Erstellt man allerdings ein Text-Zahl-Gemisch, wird die Zahl weitergezählt, wenn sie mit 1, 2, 3 oder 4 beginnt. Ab 5 nicht mehr:
Microsoft listet auf der folgenden Seite die Beschränkungen von Excel auf:
Amüsant, was manchen Teilnehmerinnen und Teilnehmern in Schulungen auffällt. Dinge, die ich noch nie beachtet habe oder denen ich keine Beachtung beigemessen habe.
Beispielsweise ist mir noch nie aufgefallen, dass Zahlen in PowerQuery kursiv stehen, während Texte immer „aufrecht“, also nicht kursiv im Editor dargestellt werden:
Excelschulung. Wir üben rechnen. Zuerst eine einfache Subtraktion; Formel herunterziehen:
Wir lernen, wie man mit dem AutoSummen-Symbol eine Summe erzeugt:
Und jetzt machen Sie es mal alleine, lautet die Aufgabe. Ein Teilnehmer beschwert sich, dass eine Summe falsch rechnet:
Wir gehen der Sache auf den Grund. Klar – er hat die erste Summe nicht nach rechts gezogen, sondern drei Mal eine Summe erstellt. Und bei der dritten Summe hat er nicht aufgepasst und den Vorschlag von Excel übernommen:
Amüsant. In der Excelschulung fragt mich ein Teilnehmer, warum manchmal der Filter bei der Auswahl „Textfilter“ / „Ist gleich“ die letzte Filterung anzeigt und manchmal nicht:
Oder so:
Nun – das hängt damit zusammen, wie man filtert. Wählt man den Befehl „Textfilter“ / „Ist gleich“ aus
und trägt dort mehrere Varianten ein, beispielsweise M?nchen oder M??nchen:
wird dies als Auswahl unter „Benutzerdefinierter Filter“ angezeigt. Der grüne Haken gibt Auskunft darüber.
Wählt man nun die Option „Ist gleich“, wird der vorher eingegeben Text gelöscht.
Allerdings: wählt man als Filterkriterium München oder Muenchen, so wird gar nichts angezeigt:
Amüsante Frage in der Excelschulung. Wir üben das Formatieren per Hand. Folgende Tabelle ist das Ziel:
Warum denn seine Angaben so „seltsam“ in der Zelle stehen, will der Teilnehmer wissen:
Die Antwort ist leicht: er hatte auch die Ausrichtung geändert. Ein zweiter Klick auf das Symbol „Text nach oben drehen“ lässt den Text wieder von links nach rechts laufen.
Volker Pagel ist sehr irritiert. Er hat lange gesucht, um die Ursache für folgende Inkonsequenz zu finden.
In einer Liste befinden sich Kostenstellennummer, beispielsweise 010, 0010 oder 000010.
Die Funktionen der Klasse ZÄHLENWENN, SUMMEWENN & co unterscheiden nicht zwischen 010, 0010 oder 00001, obwohl diese als Text vorliegen. Wahrscheinlich werden diese Texte intern als Zahlen behandelt.
Anders dagegen die Funktionen WENN, SVERWEIS, XVERWEIS, SUMMENPRODUKT & co. HIER wird klar zwischen 010 und 00010 unterschieden!
Ich möchte mit Power BI auf eine Access-Datenbank zugreifen. Leider erhalte ich folgende Meldung:
Beim Herstellen einer Verbindung ist ein Fehler aufgetreten.
Schade!
Auf der genannten Seite von Microsoft lese ich:
Ich soll mein Office deinstallieren und in der gleichen Version wie Power BI zu installieren, finde ich nun keine wertvolle Hilfe. Ich scrolle nach unten und lese dort:
Ah, okay – zwei Versionen von Access.
Gut – ich warte bis nächste Woche und teste alles auf meiner 64-Bit-Maschine.
Ich habe ein Add-In für einen Kunden erstellt. Per Knopfdruck sollen Daten (eine Liste der Debitoren) in eine bestehende Datei importiert werden.
Ich entscheide mich für VBA, weil ich so die Datei und die Daten prüfen kann.
Klappt:
Ich erhalte die Rückmeldung:
„Wir haben nun das File beim ersten Kunden getestet und dabei ist uns aufgefallen, dass aus den Debitor Stammdaten leider nur Einträge mit Kürzel exportiert werden und jene ohne nicht.“
Ich wundere mich. Ich importiere doch alle Daten:
xlBlattDebitoren.Range("A1").CurrentRegion.Copy Destination:=ThisWorkbook.Worksheets("Debitoren").Range("A1") ' -- kopiere die Daten
Ich ahne wo das Problem ist.
Wenn die ursprüngliche Liste gefiltert war, werden nur die sichtbaren Daten kopiert.
Also muss ich vorher prüfen, ob ein Filter eingeschaltet war. Falls ja, wird er entfernt:
' -- schalte mögliche gesetzten Filter aus
If xlBlattDebitoren.FilterMode = True Then
xlBlattDebitoren.Range("A1").AutoFilter
End If
Erstaunt schaue ich einem Kollegen über die Schulter. In „seinem“ Power BI werden nur zwei Symbole angezeigt – die Datenansicht fehlt:
Des Rätsels Lösung ist schnell gefunden: Dieser Bericht greift auf eine SQL-Datenbank zu – allerdings nur per DirectQuery. Erst wenn man irgendeine weitere Datenquelle direkt einbindet, erscheint das Symbol. Eigentlich klar, oder?
Ich amüsiere mich jedes Mal, wenn ich (auf einer Internetseite) anklicken muss, dass ich kein Roboter, sondern ein Mensch bin. Was machen denn die armen Roboter – schwindeln sie oder bleiben sie beleidigt draußen?
Ich erhalte die Frage, wie man Datumsangaben, die sich in Textfeldern in Word befinden, berechnen kann, beispielsweise den folgenden Tag berechnen kann. Meine Antwort:
Hallo Herr R.,
Während ein
{ = { PAGE} + 1}
funktioniert, klappt leider eine Berechnung
{ = { REF Datum} + 1}
nicht. Ich wüsste auch nicht, wie man den Wert der Textmarke in eine Zahl verwandeln kann.
Wenn Sie das unbedingt brauchen und wenn das System (ja, ich erinnere mich) dies nicht liefert, sehe ich auch nur eine Lösung Programmieren.
Und ja: RTF kann keine Makros speichern; also extern (Tipp: nicht in Normal.dotm, sondern in eine andere Vorlage). Ja – so wie Ihr Beispiel (ohne es getestet zu haben)
Gegenfrage: kann man nicht etwas einschieben?
System liefert Daten. Kann man die nicht abfangen, in Excel schreiben und dort rechnen und dann (mit einem Serienbrief) auf diese Daten zugreifen?
Sorry, Felder, VBA – Word ist seeeeehr schlecht im Rechnen …
Liebe Grüße und trotzdem ein schönes Wochenende – es soll ja regnen – ich kann mal ein bisschen grübeln
Rene Martin
PS: Formularfelder können ja auch rechnen – aber auch das scheitert:
Ich habe in einem Beitrag beschrieben, dass man Lücken im Liniendiagramm erzeugen kann, indem man #NV statt 0 verwendet und die entsprechenden Optionen einstellt:
„Hallo, Diese Lösung hört sich super an und man findet sie vielfach im Internet, nur leider funktioniert sie wohl nicht mehr. Ich arbeite mit Microsoft Excel for M365 (Version 2022) und egal was ich mache, mein Liniendiagramm rauscht bei Nullwerten oder #NV oder #DIV/0 auf die Nulllinie und ich verzweifle langsam. Man kann bei den Diagrammoptionen anklicken was man will ,es ändert sich absolut nichts. Irgendeine Idee?“
Ausdruck eines gefilterten Tabellenblattes durchführen mit folgenden Bedingungen:
Seitenumbrüchen aus den rausgefilterten (ausgeblendeten) Zeilen entfernen, Ausdruck starten, im Anschluss Seitenumbruch an den gleichen Stellen wieder einfügen.
Alternativ: Seitenumbrüche der rausgefilterten (ausgeblendeten) Zeilen ignorieren (nicht löschen, bei nächster Filterung könnten ja entsprechende Seite eingeblendet sein…).
Hintergrund: ohne Makro werden leere Seiten durch die vorhandenen Seitenumbrüche generiert.
Ich habe schon mal ChatGPT befragt, da meine VBA Kenntnisse hierfür nicht ausreichen, dieser hat mir folgenden Code vorgeschlagen:
ja – ich habe einige Versuche mit ChatGPT gestartet. Das Problem: bekannte Dinge weiß er auch; mir unbekannte Probleme kennt er auch nicht und liefert falsche Lösungen … Also googeln …
Die Fehler:
* nicht die Zeile hat einen Umbruch, sondern das Blatt
* die Eigenschaft heißt HPageBreaks (mit «H» am Anfang)
* ich darf nicht alle Zellen durchlaufen und prüfen, ob eine Zelle/Zeile einen Umbruch hat (und ausgeblendet ist), sondern ich muss die Liste der Umbrüche durchlaufen und prüfen, ob die Zelle/Zeile ausgeblendet ist.
* man muss die Liste der ausgeblendeten Zellen «einsammeln» – gerne in der Collection, wie ChatGPT vorschlägt, aber man muss es tun. (in meinem Beispiel sammle ich sie in einem Textstring ein, getrennt mit «|» – so «sehe» ich die Liste besser.
ChatGPT muss noch viel lernen! Von uns?
Dieser Code funktioniert. Zum Test: Das Teilchen fragt, ob du drucken willst. Wenn du «nein» anklickst, stoppt es und du kannst kontrollieren. Bei «ja» erfolgt Ausdruck und die Umbrüche werden danach eingeschaltet.
Wenn du die Zeile «Exit sub» löschst, werden die Umbrüche gelöscht und dann wieder gesetzt (zur Kontrolle).
Sub PrintWithoutPageBreaks_Rene()
Dim ws As Worksheet
Dim lngAnzahlUmbrueche As Long
Dim strUmbrueche As String
Dim strUmbruchsadresse As String
Dim i As Long
‚ Set worksheet to active worksheet
Set ws = ActiveSheet
‚ ermittle die Anzahl der Umbrüche:
lngAnzahlUmbrueche = ws.HPageBreaks.Count
‚ Loop through each pagebreak
For i = lngAnzahlUmbrueche To 1 Step -1
‚ falls ausgeblendet
If ws.Range(ws.HPageBreaks(i).Location.Address).RowHeight = 0 Then
‚ merke die Adresse des Umbruchs, beispielseise $A$17
Ich habe eine Liste, bestehend aus zwei Spalten, in den sich Buchstaben befinden.
Ich möchte wissen, ob der erste Teil einer Buchstabenkombination („XY“), beispielsweise „X“ in der ersten Spalte steht und der zweite Teil, beispielsweise „Y“ in der zweiten.
Mit der Formel
=A2:A39&B2:B39
verkette ich die beiden Spalten. Nun kann ich zählen:
=ZÄHLENWENN(D7#;D3)
Will ich allerdings die beiden Formeln zusammenbauen, versagt Excel:
=ZÄHLENWENN((A2:A39&B2:B39);D3)
Und ja – natürlich kann man das Problem (anders) lösen – beispielsweise so:
Ich möchte in einer Abfrage den Text „Warengruppe“ filtern.
Okay – ich habe ihn ohne Anführungszeichen eingetragen – diese fügt Access automatisch hinzu.
Leider werden bei der Eingabe alle benutzerdefinierten Funktionen aufgelistet -DAS möchte ich nun nicht.
Und nun stellt sich die Frage: wie beende ich die Eingabe, so dass „Warengruppe“ in der Zelle steht und nicht diese benutzerdefinierte Funktion. [Enter]? [Tab]? [Pfeiltaste]?
Die Lösung: Pfeiltaste oder mit der Maus in ein anderes Feld klicken.
Natürlich habe ich mehrmals [Enter] gedrückt!
Beim nächsten Mal werde ich den Text, den ich filtern möchte, in Anführungszeichen setzen. Ich verspreche es!
Vor der Excelschulung bat mich der Teilnehmer einen Blick auf seine Exceldatei zu werfen. Er könne seit einer Weile keinen Datenschnitt mehr einfügen. Das Symbol sei inaktiv.
„Zu viele Formeln?“ schoss es mir durch den Kopf? „Zu viele bedingte Formatierungen?“ „Oder andere Formate?“
Als ich die Datei erhielt, fiel mein erster Blick auf die Dateigröße – sie war nicht einmal ein MByte groß. Also wohl kein „zu viel an“.
Die Pivottabellen habe ich schnell in der Datei gefunden – und wirklich: warum kann man keinen Datenschnitt einfügen?
An den „Filterverbindungen“ kann es nicht liegen – dort wird festgelegt, welche Pivottabelle welchen Datenschnitt verwendet.
Eine Weile habe ich gegrübelt.
Des Rätsels Lösung habe ich auf der Registerkarte „Einfügen“ gefunden:
DORT kann man auch den Datenschnitt aktivieren. Und natürlich Bilder, SmartArts, Diagramme einfügen. Eben nicht – all diese Symbole sind inaktiv. DORT war natürlich auch der Datenschnitt inaktiv.
Mir dämmerte es: mit der Tastenkombination [Strg] + [6] kann man Bilder (und Diagramme) ausblenden. Eben: und auch Datenschnitte. Diese Option findet man auch in den Exceloptionen in der Kategorie „Erweitert“ / „Optionen für diese Arbeitsmappe anzeigen“:
Werden nun SO oder mit der Tastenkombination [Strg] + [6] die Objekte wieder angezeigt, erscheint auch der Datenschnitt. Und sogar ein Diagramm!
Der Teilnehmer war sehr froh über diese Information.
Hallo Hr Martin, ich habe heute einen Post von Ihnen gesehen und finde in leider nicht mehr ;-( wie formatiert man mit Mausklick? Vielen lieben Dank
###
Hallo Herr L.,
die Frage ist zu kurz: Sie meinen Excel? Zellformatierung? Die Tastenkombination Strg + 1? Oder die Minisymbolleiste?
Könnten Sie die Frage bitte etwas genauer formulieren? Ich weiß auch nicht, auf welchen Post Sie sich beziehen?
lG :: Rene Martin
####
Sorry Ecxel, Mausklick auf eine Zelle, Zeile und Spalte hervorheben über bedingte Formatierung
####
Hallo Herr L., den Trick habe ich auch irgendwo gelesen – er war nicht von mir. Wenn sich der Cursor IN der Zelle befindet, kann man das mit =ODER(ZELLE(„ZEILE“)=ZEILE();ZELLE(„SPALTE“)=SPALTE()) hervorheben. Man sollte allerdings die Eingabe mit dem grünen Haken beenden. Liebe Grüße Rene Martin
Hurra – ich habe die Funktion (genauer: die Hilfe) auch!
Während man früher einen Teil einer Formel markieren und dann mit [F9] sich das Ergebnis anzeigen lassen konnte, ist es jetzt möglich lediglich einen Formelteil zu markieren – ein Quickinfo zeigt den berechneten Wert an:
Schöne Sache. Aber diesmal bin nicht ich es, der nörgelt, sondern Bill Jelen (MrExcel) weist darauf hin, dass ein Datum leider nicht formatiert dargestellt wird:
Außerdem, so meint er, kann die Anzeige ein fehlerhaftes Ergebnis anzeigen, wenn falsch markiert wurde (das konnte bei [F9] allerdings auch schon passieren):
Korrekt wäre (hätte man die 31 und nicht die 1 markiert):
Hallo Herr Martin, ich begleite Sie so gut wie täglich – immer wieder erfrischend und gottseidank manchmal „Ja klar“: das tut ja auch mal gut. Heute passiert mir folgendes: Eine Tabelle mit Temperaturaufzeichnungen im 10-Minuten-Abstand über ca. 1 Monat, also ca 4000 Datensätze. Ich will das Datum auf der X-Achse und die Temperatur auf Y haben, Überraschung: der „Kerl“ denkt mit und macht mir bei Diagrammtyp Linie (xy brauch ich nicht aufgrund der äquidistanten Messzeiten) ein abgehacktes/stufiges Diagramm mit konstanten Tageswerten und offenbar Min/Max Strichen wie Börsendiagramme oder so. Nehme ich die Zeiten raus, funktioniert alles wie vermutet. Welche Einstellung bleibt mir hier verborgen? Danke und VG
###
Hallo Herr H.,
Steht in einer Spalte eine Datumsangabe und daneben eine Zahl wird das Datum als Achsenbeschriftung erkannt – obwohl es sich hierbei eigentlich auch um eine Zahl handelt.
Tragen Sie jedoch Datum + Uhrzeit in eine Zelle, wird es nicht erkannt.
Der Grund: Excel erkennt die erste Spalte als Datum, hat jedoch als kleinste Einheit nur Tage zur Verfügung (deshalb bei Ihnen auch die Tagessprünge).
Eine Lösung: wandeln Sie das Datum in einen Text um, beispielsweise mit =TEXT(A2;“TT.MM.JJJJ hh:mm“)
Dann klappt es.
Liebe Grüße
Rene Martin
Nachtrag: wenn Sie mehrere Tage verwenden, wird jeder Tag nur mit „einem Strich“ dargestellt
Ich habe mal eine Funktion gesehen, ich meinte, diese haben sie mir sogar gezeigt, wie man so ein Fenster anzeigen kann, wo diese Informationen ohne VBA ersichtlich sind.
Ich prüfe aktuell ein Excel mit gefühlt 10 Mio Formeln 😉 und wollte die Grösse ermitteln, damit ich danach sagen kann wie viel ich kontrollieren konnte.
Freundliche Grüsse
####
Hallo Herr Schönenberger,
Sie haben nach einer „Funktion“ gefragt.
Haben Sie den Inquire? Schauen Sie mal in Com-Add-Ins nach.
Damit ist die Analyse schnell möglich:
Danke an XLarium – oder natürlich über die Arbeitsmappenstatistik
Könne Sie mir nochmals sagen, wo ich die Funktion finde wie gross eine Excel-Datei ist
Anzahl Register
Benötigte Zellen
Anzahl Formeln
Danke
Freundliche Grüsse
####
Hallo Herr S.,
Sie meinen die VBA-Befehle, mit denen man das herausbekommt?
– Anzahl der Tabellenblätter (Register):
ThisWorkbook.Sheets.Count
– Benötigte Zellen:
UsedRange
oder:
Range(„A1“).SpecialCells(xlCellTypeLastCell)
ist die letzte Zeile. Sie hat die Eigenschaften Row und Column – also die Nummer der Zeile und die Nummer der Spalte. Noch weiter rechts, bzw. unten gibt es nichts.
– Anzahl Formeln
dafür gibt es keinen einfachen Befehl. Entweder Sie durchlaufen alle verwendeten Zellen in UsedRange
Option Explicit
Sub AnzFormeln()
Dim xlBlatt As Worksheet
Dim xlZelle As Range
Dim lngAnzahl As Long
Set xlBlatt = ActiveSheet
For Each xlZelle In xlBlatt.UsedRange
If xlZelle.HasFormula Then
lngAnzahl = lngAnzahl + 1
End If
Next
MsgBox „Anzahl der Zellen mit Formeln: “ & lngAnzahl
Erstaunlich – man kann eine Zahl mit mehreren Dezimalstellen mit einem Tausendertrennzeichen im Zahlenformat trennen, beispielsweise (für Deutschland und Österreich)
#.##0
oder (für die USA)
#,##0
oder für die Schweiz
#'##0
Allerdings versagt das Leerzeichen:
# ##0
Es funktioniert erst dann, wenn man in den Optionen / Erweitert das Leerzeichen als Tausendertrennzeichen definiert und dann die Zellen als Zahl mit Tausendertrennzeichen festlegt.
Ich habe an dem Beispiel die ersten drei Monate von 2023 genommen und die jeweilige Kalenderwoche eingefügt.
Ich habe aber keine Idee, wie ich auch das Datum links neben Montag rot formatieren kann.
Hätten Sie eine Idee? Vielleicht eine solche, wo man alle drei Zellen: Datum, Wochentag (hier der Montag) und KW-Zahl zugleich fett und rot formatieren könnte.
Hallo Herr F.,
mit der Funktion WOCHENTAG können Sie das Problem lösen.
Verwenden Sie bei WOCHENTAG den Parameter 2, dann ist Wochentag(Datum;2) = 1 bei Montag.
Für einen SAP-Upload soll eine Textdatei erzeugt werden. Mehrere Zeilen mit unterschiedlichen Informationen sollen zusammengebaut werden und in eine Textdatei geschrieben werden. Jede Zeile wird mit einem Zeilenumbruch beendet. Ich verwende vbCrLf.
Da nun die letzte Zeile auch einen Zeilenumbruch hat, überlege ich ihn zu löschen. Ich überprüfe das letzte Zeichen – falls es nicht um vbCrLf handelt, wird es gelöscht. Jedoch:
' -- entferne den letzten Zeilenumbruch
If Right(strExportString, 1) = vbCrLf Then
strExportString = Left(strExportString, Len(strExportString) - 1)
End If
funktioniert nicht! Klar doch: vbCrLf sind ZWEI Zeichen: Wagenrücklauf + Zeilenschaltung. Klar: ich muss überprüfen, ob die letzten BEIDEN Zeichen vbCrLf entsprechen. Und dann löschen.
Folgender Befehl funktioniert dann:
' -- entferne den letzten Zeilenumbruch
If Right(strExportString, 2) = vbCrLf Then
strExportString = Left(strExportString, Len(strExportString) - 2)
End If
Manchmal bin ich erstaunt, was Firmen (beziehungsweise Menschen, die dort arbeiten), sich so alles wünschen. Beispielsweise folgender Wunsch:
Firmenweit existiert ein Excel-Add-In:
Der XML-Code des Ribbons sieht folgendermaßen aus:
Nun – so lautete der Wunsch – soll ich ein zweites Add-In erstellen. Allerdings soll es sich in das erste einklinken. Also die gemeinsame Registerkarte „Schneewittchen und die sieben Zwerge“ verwenden.
Wie das?
Ich dachte nicht, dass das funktioniert.
Ein Dankeschön an Markus Hahner (www.hahner.de) – er hat mir die Lösung geliefert.
Das „Haupt-Addin“ muss mit einem Namespace im Element <customUI> versehen werden – hier:xmlns:x=“Zeichentrick“. Der Name (Zeichentrick) ist beliebig. Dieser Namespace (hier: „x“) wird im Element <tab> verwendet: idQ=“x:tabZwerge“. Dabei ist „tabZwerge“ natürlich auch beliebig.
Die andere Datei, also das andere Add-In, sieht dann folgendermaßen aus:
Also: Namespace in customUI und idQ sind identisch. Das Label, also die Beschriftung der Registerkarte, muss natürlich nicht wiederholt werden.
Und schon klinkt es sich ein:
Nachdem ich das Problem gelöst hatte, hatte ich doch glatt im Internet eine weitere Beschreibung gefunden:
Boah – das hat mich einige Stunden Arbeit gekostet! Beinahe hätte es excel-nervt nicht mehr gegeben. Warum?
Vor einigen Tagen erhielt ich eine Rechnung von meinem Internetanbieter strato, Sie berechnen 7,81 Euro für PHP 7.4 Extended Support:. Warum das, denke ich und logge mich ein.
Tatsächlich:
„Wir informieren Sie rechtzeitig per E-Mail, wenn Sie noch eine veraltete Version von PHP verwenden. Auf diese Weise haben Sie noch Zeit, die Version zu wechseln, bevor wir den kostenpflichtigen PHP Extended Support aktivieren.“
Haben sie mich informiert? Ich kann mich nicht erinnern.
Egal – das will ich nicht – selbstverständlich deaktiviere ich den Dienst.
Und aktiviere php Version 8.0
Was passiert? Alle meine Interauftritte funktionieren nicht mehr. Die Seite „excel-nervt“ sieht nun so aus:
Es gab einen kritischen Fehler auf deiner Website. Bitte überprüfe den Posteingang deiner Website-Administrator-E-Mail-Adresse für weitere Anweisungen.
Und dann fing der Horror an. Zum Glück hatte ich noch eine Woche Zeit, um auf die alte php-Version zurückzuschalten:
Was tun? Unter Werkzeuge / Website-Zustand findet man nur die Versionsnummer, aber nicht, wie man die Seite auf Version 8.0 aktualisieren kann.
Gibt es ein Plug-In, das mir behilflich ist? Kann mir eine Firma helfen? Ein Mensch? An welcher Schraube muss ich drehen?
Auf vielen Seiten und Tutorial erhalten ich den Hinweis zu prüfen, ob die Plug-Ins auch für die aktuelle Version geeignet sind. Das Problem: ich kann nicht php 8.0 aktivieren und dann prüfen, weil dann die Seite nicht mehr funktioniert.
Aber eben das ist der Ansatz: einige der Plugins (auf einigen Seiten auch die Themes) sind nicht für php Version 8.0 ausgelegt. Werden sie deaktiviert (man muss probieren!), dann läuft die Seite wieder:
Vor allem WP Permalauts war einer der Übeltäter. Inzwischen habe ich ihn gelöscht und zur Hölle gewünscht)!
Das hat mich einige Stunden (Such-)Arbeit gekostet. Und das Design ist nun etwas anders.
Wenn ich Zeit und wieder viele Nerven habe, werde ich es ändern. Versprochen.
Ich erstelle eine VBA-Funktion, bei der eine Zahlenreihe (normalerweise vier Ziffern) als Text gespeichert wird und mit Nullen davor aufgefüllt wird (meistens mit zwei Nullen). Der Funktion füge ich in VBA einen Kommentar hinzu, damit ich weiß, dass es sich um sechs Ziffern handelt:
' _ _ _ _ _ _
Die Funktion sieht folgendermaßen aus:
Ich teste und bin irritiert, dass das Ergebnis falsch ist.
Tatsächlich: Else ist grün! Der Unterstrich bewirkt einen Umbruch! Obwohl er im Kommentar steht!
Also verwende ich ein anderes Zeichen, um diese Leerzeichen zu versinnbildlichen:
Ich programmiere wirklich gerne. Aber ich habe immer meine liebe Not mit ungenauen Arbeitsanweisungen.
Ich soll per Programmierung ein Tabellenblatt als PDF ablegen.
Kein Problem. Allerdings muss ich nachfragen, wie das PDF-Dokument heißen soll.
BatchRecord-Filename-Datum-Uhrzeit
lautet die Antwort.
Ich vermute, dass er nicht Filename, sondern Blattname meint.
Und: in welcher Form das Datum und die Uhrzeit geschrieben wird, erklärt er mir auch nicht. Nun, er wird es mir sicherlich noch sagen.
Ich probiere:
strBlattname = "BatchRecord-" & ThisWorkbook.Name & Format(Date, "DD.MM.YYYY") & "-" & Format(Now, "HH:MM") & ".pdf"
' -- Export als PDF - in den gleichen Ordner, in dem sich die Vorlagendatei befindet
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "\" & strBlattname, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Eine Fehlermeldung ist die Folge:
Das Dokument wurde nicht gespeichert.
Ein bisschen suchen und ich werden fündig: Natürlich darf kein Doppelpunkt im Dateinamen auftauchen. Also raus damit (bei der Uhrzeit) und warten, wie denn der Dateiname korrekt aussehen soll:
Die Kamera ist sicherlich bekannt. Mit ihrer Hilfe kann man einen dynamischen Snapshot auf einen Teil der Excelmappe herstellen.
Christian Gröblacher hat mich darauf hingewiesen, dass zu viele Fotografien eine Excelmappe jedoch enorm verlangsamen können. Also: Vorsicht ist geboten!
Microsoft sollte uns fragen, was sie noch an den Programmen verbessern können.
In regelmäßigen Abständen erhalte ich Anfragen, wie man so etwas machen könne. Und muss abwehren und gestehen, dass das nicht geht. Beispielsweise folgende Anfrage:
Sehr geehrter Herr Martin
Mit viel Interesse und Freude schaue ich auf LinkedIn-Learning Ihre Kurse.
Eine Frage hätte ich zu Outlook: Wie ist es möglich, dass E-Mailadressen, denen man antwortet, automatisch ins Adressbuch übernommen werden?
Denn ich möchte nicht bei jeder E-Mailadresse dies manuell tun müssen.
Wenn Sie mir das beantworten könnten, wäre ich Ihnen auf ewig dankbar. 😉
Freundliche Grüsse aus der Schweiz
####
Hallo Herr R.,
danke für das Lob.
Und: schöne Frage – aber ich fürchte, hierfür gibt es keine Lösung.
Ich verschiebe immer die Mail ins Adressbuch – dadurch wird ein neuer Kontakt generiert, der bereits die Mailadresse, manchmal auch den Namen, enthält. Die Telefonnummer und Anschrift ziehe ich mit der Maus in die entsprechenden Felder. In einem der Filme habe ich das gezeigt.
Wenn du beispielsweise eine Zahl eingibst, die mit einem Nullenprefix beginnt (z.B. „01234“), würde Excel sie automatisch als Zahl formatieren und die Nullen entfernen. Wenn du die Formatierung „;;;“ verwendest, wird die Zahl als Text behandelt und die Nullen bleiben erhalten.
Um die Zellformatierung „;;;“ in Excel zu verwenden, kannst du die Zelle auswählen und dann auf den „Zahlenformatierungs-Typ“ klicken, der sich in der Registerkarte „Start“ im Bereich „Zahlenformatierung“ befindet. Du kannst auch die Tastenkombination „STRG + 1“ verwenden, um die Zahlenformatierung aufzurufen, und dann „Text“ auswählen.
Nein – das kann ich so nicht durchgehen lassen.
Nächste Frage:
In Microsoft Excel kannst du den Nachnamen aus einem vollen Namen extrahieren, indem du eine Kombination aus Textfunktionen verwendest. Hier sind einige Möglichkeiten, wie das funktionieren kann:
Die Funktion "RECHTS": Die Funktion "RECHTS" kann verwendet werden, um eine bestimmte Anzahl von Zeichen von der rechten Seite einer Zeichenfolge zu extrahieren. Hier ist ein Beispiel:
=RECHTS(A2,LEN(A2)-FIND(“ „,A2))
Diese Formel sucht den Leerraum im Namen und berechnet die Anzahl der Zeichen bis zum Leerraum. Die Funktion „RECHTS“ extrahiert dann die gleiche Anzahl von Zeichen vom Ende des Namens.
Die Funktion "TEIL": Die Funktion "TEIL" kann verwendet werden, um einen Teil einer Zeichenfolge anhand eines bestimmten Zeichens zu extrahieren. Hier ist ein Beispiel:
=TEIL(A2,FIND(“ „,A2)+1,LEN(A2))
Diese Formel sucht den Leerraum im Namen und extrahiert alles nach dem Leerraum als Nachnamen.
Die Funktion "TEXTVOR": Die Funktion "TEXTVOR" kann verwendet werden, um eine bestimmte Anzahl von Zeichen von einer Zeichenfolge zu entfernen. Hier ist ein Beispiel:
=TEXTVOR(A2,“ „)
Diese Formel sucht den Leerraum im Namen und entfernt alles nach dem Leerraum als Vornamen.
Okay, schon ganz gut, denke ich. Aber: bitte alles auf Deutsch (nicht LEN) und bitte Semikolon statt Komma!
wie ermittle ich die Anzahl der Sonntage eines Monats in Excel?
will ich wissen. Und erhalte die Antwort:
In Microsoft Excel kannst du die Anzahl der Sonntage eines Monats mit einer Kombination aus Funktionen berechnen. Hier ist ein Beispiel, wie das geht:
Verwende die Funktion "NETZWERKDIENSTAG" für das erste Datum des Monats:
=NETZWERKDIENSTAG(DATE(Jahr,Monat,1))
In dieser Formel:
"Jahr" ist das Jahr, für das du die Anzahl der Sonntage berechnen möchtest
"Monat" ist der Monat, für den du die Anzahl der Sonntage berechnen möchtest
Verwende eine Schleife, um die Anzahl der Sonntage für jeden Tag des Monats zu berechnen:
Die Funktion "WENN" überprüft, ob jeder Tag ein Sonntag ist
Die Funktion "SUMME" addiert schließlich die Anzahl der Sonntage zusammen.
Uff – was bitte ist NETZWERKDIENSTAG ?
Auch die Antwort auf die Frage, wie man eine Quersumme berechnen kann, liefert ein kurioses Ergebnis:
In Microsoft Excel kannst du die Quersumme einer Zahl berechnen, indem du die Zahl in eine Zeichenfolge umwandelst und dann jede Ziffer einzeln addierst. Hier ist ein Beispiel, wie du das mit einer Formel tun kannst:
"Zahl" ist die Zahl, deren Quersumme berechnet werden soll
Die Funktion "LÄNGE" bestimmt die Anzahl der Zeichen in der Zahl
Die Funktion "INDIREKT" und "ROW" erstellen eine Zeichenfolge mit der Anzahl der Zeichen in der Zahl
Die Funktion "WENNTEIL" teilt die Zahl in einzelne Zeichen auf
Die Funktion "SUMME" addiert schließlich alle Zeichen zusammen, um die Quersumme zu berechnen.
WENNTEIL würde ein Schmunzeln hervorrufen – ein Anwender wird sicherlich verärgert sein, weil er überhaupt nicht weiß, wie man diese Formel umbauen kann/muss, damit sie ein korrektes Ergebnis liefert.
ChatGPT irritiert schon sehr. Haben Sie es schon ausprobiert. Hier einige Stilblüten. Ich will wissen
Wie kann ich mit VBA überprüfen, ob eine Zelle eine Datenüberprüfung besitzt?
Die Antwort:
Sie können in VBA überprüfen, ob eine Zelle eine Datenüberprüfung besitzt, indem Sie folgenden Befehl verwenden:
If Range(„A1“).Validation.Type <> 0 Then ‚ Code ausführen, wenn die Zelle eine Datenüberprüfung hat End If
In diesem Beispiel wird die Eigenschaft „Validation.Type“ verwendet, um zu überprüfen, ob die Zelle „A1“ eine Datenüberprüfung besitzt. Wenn die Überprüfung vorhanden ist, wird die Eigenschaft „Validation.Type“ ungleich 0 sein, und der Code innerhalb der If-Abfrage wird ausgeführt.
Nein, liebes ChatGPT, das ist falsch.
Wenn auf einer Zelle keine Datenüberprüfung liegt, liefert Range(„A1“).Validation.Type einen Fehler. Diesen kann man abfangen (On Error Resume Next) und dann den Wert der Err-Variable ermitteln.
Würde jemand dein Beispiel ausprobieren, wäre er oder sie frustriert und verärgert, weil es einen Fehler liefert.
Auch folgende Antwort erstaunt:
Gib mir fünf Lösungen, um die Summe der Zahlen in Spalte F zu berechnen
Hier sind fünf Lösungen, um die Summe der Zahlen in Spalte F in Microsoft Excel zu berechnen:
Verwenden Sie die SUM-Funktion:
=SUM(F1:F10)
Verwenden Sie die AutoSum-Funktion:
Klicken Sie auf die Zelle unter der letzten Zelle in Spalte F und drücken Sie die Tastenkombination "Strg + Alt +".
Verwenden Sie die Formel SUMME:
=SUMME(F1:F10)
Verwenden Sie die SUM-Formel mit einer festen Zellenbereichsadresse:
=SUM(F1,F2,F3,F4,F5,F6,F7,F8,F9,F10)
Verwenden Sie die SUM-Formel mit einer bedingten Formatierung:
Wählen Sie die Zellen in Spalte F aus und verwenden Sie die Funktion "Bedingte Formatierung". Wählen Sie dann die Option "Formel" und geben Sie "=ISTZAHL(F1:F10)" ein. Die Zellen, die Zahlen enthalten, werden automatisch markiert. Verwenden Sie dann die SUM-Formel und beschränken Sie die Zellenbereichsadresse auf die markierten Zellen.
=SUM(F1:F10)
Hinweis: In diesen Beispielen wird davon ausgegangen, dass die Daten in Spalte F in den Zellen F1 bis F10 enthalten sind. Stellen Sie sicher, dass Sie die Zellenadressen entsprechend anpassen, wenn Ihre Daten anders angeordnet sind.
Okay – das sind fünf Antworten (auch wenn die automatische Nummerierung versagt), aber sehr ähnliche. Ich hatte auf TEILERGEBNIS, AGGREGAT, … gehofft. Tja …
Und: einmal SUM, einmal SUMME zu schreiben sind eigentlich keine zwei Lösungen. Schließlich: liebes ChatGPT – bitte verwende doch Semikolon als Trennzeichen.
Vielleicht sollte ich die Antwort regenerieren lassen.
ChatGPT polarisiert: es begeistert, verblüfft und irritiert. Es irritiert durch falsche Antworten. Ein Beispiel gefällig?
Ich frage CHatGPT:
Mit welchem VBA-Befehl kann ich den Autor der Arbeitsmappe ermitteln?
Und erhalte folgende korrekte Antwort:
Sie können den Autor einer Arbeitsmappe in VBA mit folgendem Befehl ermitteln:
Dim strAuthor As String strAuthor = ThisWorkbook.BuiltinDocumentProperties(„Author“)
In diesem Beispiel wird die Eigenschaft „Author“ aus den „BuiltinDocumentProperties“ des aktuellen Workbooks abgerufen und in der Variablen „strAuthor“ gespeichert. Die „BuiltinDocumentProperties“ enthält alle standardmäßigen Dokumenteigenschaften wie Autor, Betreff, Schlagworte, etc.
Auch die Frage
Mit welchem VBA-Befehl kann ich ein Bild (beispielsweise C:\Test.jpg) in die rechte Kopfzeile einfügen?
wird korrekt beantwortet:
Sie können ein Bild in die rechte Kopfzeile einer Arbeitsmappe in VBA mit folgendem Befehl einfügen:
In diesem Beispiel wird die aktive Arbeitsblattseite ausgewählt und die Eigenschaft „RightHeaderPicture.Filename“ verwendet, um das Bild „C:\Test.jpg“ in die rechte Kopfzeile einzufügen. Wenn das Bild bereits eingefügt wurde, wird es durch das neue ersetzt.
Jedoch:
Meine Frage
Mit welchem VBA-Befehl kann ich den Mauszeiger ein- und wieder ausblenden?
wird nicht verstanden:
Sie können den Mauszeiger in VBA ein- und wieder ausblenden, indem Sie folgenden Befehl verwenden:
In diesem Beispiel wird die Eigenschaft „Cursor“ des Application-Objekts auf den Wert „xlWait“ gesetzt, um den Mauszeiger auszublenden. Nach der Ausführung des Codes wird die Eigenschaft „Cursor“ auf den Wert „xlDefault“ zurückgesetzt, um den Mauszeiger wieder einzublenden.
Bemerkung: xlWait und xlDefault sind Konstanten in der Microsoft Excel-Object Library, die Teil von VBA sind.
Nein, liebes ChatGPT – das ist nicht richtig. Mit der Eigenschaft Cursor kann man den Mauszeiger verändern, aber nicht ein- und ausblenden.
In letzter Zeit wird viel über ChatGPT diskutiert. Ich habe es mir angesehen und bin nicht sehr glücklich über die Antworten, die ich dort erhalte.
Ich will Folgendes wissen:
was ist der Unterschied zwischen SVERWEIS und XVERWEIS?
Und erhalte die Antwort:
Der Unterschied zwischen „SVERWEIS“ und „XVERWEIS“ liegt darin, wie sie mit fehlenden oder mehreren Übereinstimmungen umgehen.
„SVERWEIS“ gibt immer den ersten Wert zurück, der dem Suchkriterium entspricht, auch wenn es mehrere Übereinstimmungen gibt. Wenn kein Wert gefunden wird, gibt „SVERWEIS“ den Fehlerwert „#NV“ (Nicht verfügbar) zurück.
„XVERWEIS“ kann jedoch so konfiguriert werden, dass es bei mehreren Übereinstimmungen den ersten, den letzten oder einen bestimmten Wert zurückgibt. Wenn kein Wert gefunden wird, kann „XVERWEIS“ auch einen alternativen Wert zurückgeben, den Sie angeben, anstatt einen Fehler auszulösen.
Zusammenfassend ist „SVERWEIS“ schneller und einfacher zu verwenden, während „XVERWEIS“ mehr Flexibilität und Kontrolle bei der Verarbeitung von Suchergebnissen bietet.
Okay – diese Antwort ist richtig, aber XVERWEIS weist noch weitere Fähigkeiten auf, die ich bin SVERWEIS vermisse: Platzhaltersuche, matrixfähig, Standard die exakte Suche, die Spalte, in der die Werte stehen, die zurückgegeben werden, kann auch VOR der Suchespalte stehen, …)
Der Kommentar, dass „SVERWEIS“ schneller und einfacher zu verwenden,“ ist, halte ich für bedenkenswert.
ist der Prototyp eines Chatbots, also eines textbasierten Dialogsystems als Benutzerschnittstelle, der auf maschinellem Lernen beruht. Den Chatbot entwickelte das US-amerikanische Unternehmen OpenAI, das ihn im November 2022 veröffentlichte.
Und weiter:
Der Chatbot wurde mithilfe zweier Arten des sogenannten bestärkenden Lernens trainiert: RLHF (Reinforcement Learning from Human Feedback) und PPO (Proximal Policy Optimization[4][5]). Er nutzt zur Textein- und -ausgabe das Sprachmodell GPT-3.5 (GPT steht für Generative Pre-trained Transformer), eine verbesserte Version von GPT-3, die ebenfalls von OpenAI stammt. GPT basiert auf Transformern, ein von Google Brain vorgestelltes Maschinenlernmodell.
Klingt spannend. Ich melde mich an – das ist kostenlos.
Ich probiere aus – die Seite soll ja sehr gut Antworten zu Excel liefern – auf Englisch und auf Deutsch. Erster Versuch:
How to use xlookup in Excel?
Die Antwort:
XLOOKUP is a function in Microsoft Excel that allows you to search for a value in a table and return a corresponding value from a specified column. The syntax for XLOOKUP is:
lookup_value: The value you want to search for in the lookup_array.
lookup_array: The range of cells that contains the data you want to search.
return_array: The range of cells that contains the data you want to return.
default_value: The value to be returned if the function does not find a match (optional).
For example, if you have a table of data in columns A and B, with the names in column A and the corresponding ages in column B, you can use XLOOKUP to find the age of a specific person by using the following formula:
=XLOOKUP(„John“, A1:A5, B1:B5)
This will search for „John“ in column A, and return the corresponding value in column B.
Note: XLOOKUP function is available starting with Excel version 365, if you are working with an older version of Excel you may consider using INDEX and MATCH function or VLOOKUP. rene.martin@compurem.de how can I find an information in Column A in Excel with VBA?
Ich versuche es auf Deutsch:
Erkläre mit, was XVERWEIS in Excel macht
Die Antwort:
„XVERWEIS“ ist eine Funktion in Microsoft Excel, die eine Verweisoperation durchführt. Es ermöglicht es Ihnen, Werte aus einem anderen Tabellenbereich oder einer anderen Arbeitsmappe zu suchen und in Ihre aktuelle Tabelle einzufügen. Die Syntax von XVERWEIS lautet wie folgt: XVERWEIS(Suchkriterium, Bereich, [Spalte], [exakt]). „Suchkriterium“ ist der Wert, nach dem Sie suchen möchten. „Bereich“ ist der Bereich, in dem Sie suchen möchten. „Spalte“ ist optional und gibt an, in welcher Spalte Sie suchen möchten. „Exakt“ ist auch optional und legt fest, ob eine exakte Übereinstimmung gefunden werden soll oder nicht.
Ich reibe verwundert die Augen. Ich frage nach XVERWEIS und erhalte eine Erklärung zum SVERWEIS. Nein, liebes ChatGPT – DA musst du noch einmal ran!
Hallo Herr Martin, ich habe letzte Woche mich mit ihrem Excel Kurs beschäftigt und einige neue Funktionen kennen gelernt. Heute habe eine bereits bestehendes Excel welches ich oft offen habe geöffnet und kann bei der Seite „Einfügen“ nichts mehr machen (alles vergraut) und die alle Formen welche ich eingefügt habe kann ich nicht verschieben, editieren oder bearbeiten. Ich kann mir vorstellen, dass das bei dem Kapitel „Hilfreiche Assistenten“ bei Finden von Formen etwas verstellt habe. Kann das sein? Wenn ich auf die Formen fahre kommt ein weißer kreis mit einem schwarzen Ring und der einfach ( \ ) durchgestrichen ist.
ich hoffe sie können mir helfen.
mit freundlichen Grüßen
Hallo Herr A., es gibt eine Tastenkombination, welches Bilder ausblendet und keine neuen mehr einfügen lässt. Diese haben Sie aber nicht gedrückt, weil Sie sonst keine Bilder mehr sehen würden. Vielleicht haben Sie aus Versehen den Gruppierungsmodus aktiviert; also mehrere Tabellenblätter markiert. Falls ja, dann steht in der Titelleiste oben, dass eine Gruppe aktiviert ist. Unten sind mehrere Tabellenblätter ausgewählt. Über das Kontextmenü (rechte Maustaste) kann man auf einen Tabellenreiter das wieder ausschalten. War DAS die Ursache? Liebe Grüße Rene Martin
vielen vielen dank für die Problemlösung. Es waren tatsächlich mehrere Tabellenblätter markiert.
In Word soll per VBA ein Bild in eine Kopfzeile eingefügt werden. Blöde nur, dass es nach dem Einfügen „hinter den Text“ eingefügt werden soll. Ich habe lange gesucht, bis ich eine Lösung dafür gefunden habe:
Man muss das Bild in eine Form verwandeln (ConvertToShape). Dann funktioniert es:
Boah, wie schrecklich: eine Tabelle in Word (in der Kopfzeile) soll bearbeitet werden: in einer Zelle sitzt ein Bild – das soll gelöscht werden.
Mit dem VBA-Befehl Cells kann man in der Form Cells(1,1) auf die einzelnen Zellen zugreifen. Jedoch: sind Zellen verbunden, dann liefert beispielsweise Cells(2,2) ein Fehler, weil diese Zelle nicht vorhanden ist!
Die Lösung ist nicht elegant, aber sie funktioniert: Man durchläuft einfach alle Zellen – mit On Error Resume Next wird die fehlerhafte Zelle übergangen …
On Error Resume Next
Set wdDatei = wdApp.Documents.Open(Datei)
For i = 1 To wdDatei.Sections.Count
For j = 1 To wdKopf.Range.Tables.Count
Set wdTabelle = wdKopf.Range.Tables(j)
intZeilenAnzahl = wdTabelle.Rows.Count
intSpaltenAnzahl = wdTabelle.Columns.Count
For intZeile = 1 To intZeilenAnzahl
For intSpalte = 1 To intSpaltenAnzahl
If wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.Count > 0 Then
Set wdMyRange = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Range
wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Delete
End If
Next intSpalte
Next intZeile
Next j
Der Kunde will nicht nur, dass in einer Reihe von Excelvorlagen das Logo automatisiert, das heißt: mit VBA, ausgetauscht wird, sondern auch in Wordvorlagen.
Und damit beginnt wieder die Fummelei: liegt das Logo in der Kopfzeile oder in einer Tabelle in der Kopfzeile?
Eine Kopfzeile kann zur ersten Seite gehören, zum Objekt „gerade und ungerade Seiten unterschiedlich“ oder „gerade und ungerade Seiten nicht unterschiedlich“. Und diese wiederum an verschiedenen Abschnitten. Also sind eine Reihe von Schleifen nötig, um das alte Logo zu finden und zu löschen:
For i = 1 To wdDatei.Sections.Count
Set wdKopf = wdDatei.Sections(i).Headers(wdHeaderFooterPrimary)
If wdKopf.Range.InlineShapes.Count > 0 Then
Set wdMyRange = wdKopf.Range.InlineShapes(1).Range
wdKopf.Range.InlineShapes(1).Delete
wdKopf.Range.InlineShapes.AddPicture Bildpfad, , , wdMyRange ' Kopf ohne Tabelle
End If
For j = 1 To wdKopf.Range.Tables.Count
Set wdTabelle = wdKopf.Range.Tables(j)
intZeilenAnzahl = wdTabelle.Rows.Count
intSpaltenAnzahl = wdTabelle.Columns.Count
For intZeile = 1 To intZeilenAnzahl
For intSpalte = 1 To intSpaltenAnzahl
If wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.Count > 0 Then
wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes(1).Delete
Set wdMyInShape = wdTabelle.Cell(intZeile, intSpalte).Range.InlineShapes.AddPicture(Bildpfad, , , wdMyRange)
End If
Next intSpalte
Next intZeile
Next j
Der Kunde ist noch immer nicht zufrieden. Die Kopfzeile soll um zwei Zeilenschaltungen nach unten verschoben werden. Also nicht so:
sondern so:
Der Befehl ist schnell gefunden. Allerdings amüsiert mich, dass die Kopfzeile vbCrLf als zwei Zeilenschaltungen interpretiert. Nun ja – ist ja okay so:
With Datei.Worksheets(j).PageSetup
.LeftHeader = vbCrLf & .LeftHeader
Gefunden in den Tiefen des Internets. Geschmunzelt: Endlich ein Rezept zum Keksbacken, das auch für Techniker und Mathematiker geeignet ist:
Und damit verabschiede ich mich in die Tiefen der Weihnachtsferien und wünsche allen Leserinnen und Lesern entspannte Weihnachtstage und einen guten Rutsch ins neue Jahr.
Dummerweise hat ein Teilnehmer eine intelligente Tabelle über das gesamte Tabellenblatt erstellt.
Ich möchte den Bereich auf die benötigte Größe verkleinern. Hierfür tut das Symbol „Tabellengröße ändern“ in der Registerkarte „Tabellenentwurf“ gute Dienste. Schneller als das grüne Eck nach oben zu ziehen ist sicherlich das Eintragen des Bereichs:
Allerdings: wir wundern uns, dass das Ergebnis nicht das gewünschte ist – die intelligente Tabelle ist verschwunden. Genauer: sie „hängt“ irgendwo oben:
Des Rätsels Lösung: der Bildschirm war so verschoben, dass die erste Ziffer der Zeilennummer nicht sichtbar war. Also auf ein Neues – beim zweiten Mal klappt es auch!
In ein Tabellenblatt wird eine Überschrift eingetragen. Sie wird formatiert:
Sie wird in eine (leere) intelligente Tabelle (mit einer Zeile) umgewandelt:
Fügt man nun eine Zeile ein, wird die Farbe der Überschrift übernommen:
Man darf also nicht, wenn man in einer intelligenten Tabelle die Überschrift per Hand formatiert (beispielsweise, wenn man unterschiedliche Farben für verschiedene Bereiche verwenden möchte) vor der ersten Datenzeile eine Zeile einfügen.
Für die leere Tabelle heißt das: man muss sie so anlegen, dass mehrere leere Zeilen vorhanden sind.
Die Teilnehmerin in der Excelschulung möchte wissen, wie man eine Formel wieder im Funktionsassistenten anzeigen lassen kann. Beispielsweise den XVERWEIS:
Erstaunlicherweise liefert ein Klick auf f(x) nicht den Dialog, sondern das Ergebnis, was nicht gewollt ist:
Die Ursache ich schnell gefunden: die Formel besteht nicht nur aus einer (ineinandergeschachtelten) Funktion, sondern aus einer Funktion und einer Berechnung. DAS kann nicht im Funktionsassistenten angezeigt werden.
Aber man kann die Funktion innerhalb der Formel markieren und dann mit f(x) im Funktionsassistenten anzeigen lassen:
Ein bisschen überlegen musste ich schon bei der Antwort auf die Frage, was der Unterschied zwischen „Gleich“ und „Textinhalt“ (in der bedingten Formatierung):
Im Englischen ist es etwas besser erklärt, finde ich: die eine Option bedeutet „exakt“, die andere „enthält“:
Katharina hat auf eine Differenz zwischen scheinbar gleichen, aber auf unterschiedlichen Rechner installierte Excel 2016-Version aufmerksam gemacht. Power Query hat bei der Abfrage auf einen Ordner nicht nachvollziehbare Fehlermeldungen:
Das Dialogfeld beim Zugriff auf Ordner wird gar nicht angezeigt.
Mark hat uns für eine mögliche Lösung den folgenden Link genannt:
Eigentlich dürfte das doch nicht so schwierig sein, denke ich. Weit gefehlt!
Die Aufgabe: In einer Liste sollen alle Zellen mit einem bestimmten Begriff, beispielsweise „Fehler“ ans Ende der Liste sortiert werden:
Nun – im benutzerdefinierten Sortieren gibt es die Möglichkeit eine benutzerdefinierte Liste zum Sortieren zu definieren. Diese Liste kann auch nur aus einem Wort bestehen:
Für diese Liste gibt es nun zwei Varianten: an den Anfang oder ans Ende:
Wählt man die untere Option – also: ans Ende – dann stehen die anderen Werte auch in umgekehrter Sortierreihenfolge in der Liste:
DAS ist allerdings nicht gewünscht – die oberen Werte sollen in alphabetisch aufsteigender Reihenfolge stehen und DANN der letzte Text „Fehler“. Und: zwei Mal sortieren verbietet Excel:
Also sortieren wir den gewünschten Text „Fehler“ doch nach oben … DAS funktioniert.
Trägt man in einer List in der Spalte der Ort beim Autofilter in das Suchen-Feld den Text Ulm ein, werden auch Orte wie Kulmbach oder Neckarsulm gefunden:
Abhilfe schafft ein Anführungszeichen am Anfang und am Ende: „Ulm“
Allerdings: beim Suchen wird „Ulm“ nicht gefunden:
Hier muss man die Option „Gesamten Zellinhalt“ bemühen.
Max fragt mich, ob ich ihm helfe könne, per VBA einen PowerBI-Bericht anzuzeigen.
Er möchte in Excel über eine Schaltfläche ein Makro aufrufen, das einen Bericht öffnet, so dass die Anwenderinnen und Anwender den Bericht sehen können:
Er hat den Befehl „Shell“ im Internet gefunden. Richtig: Mit Shell rufe ich Programme auf, die ich nicht direkt über einen eingebundenen Verweis starten kann. Wir machen uns auf die Suche. Wie denn der Pfad auf seinem SharePoint lautet, will ich wissen. Den finden wir heraus. Er hat etwa die Form:
Microsoft Excel können keine neuen Zellen einfügen, weil in dem Ende des Arbeits Blatts Push-Zellen nicht leer sind. Diese nicht-leeren Zellen werden möglich erweise leer angezeigt, aber leere Werte, einige Formatierungen oder Formeln. Löschen Sie genügend Zeilen oder Spalten, um Speicher Platz zu schaffen, was Sie einfügen möchten, und versuchen Sie es dann erneut.
Zugegeben: diese Fehlermeldung verstehe ich nicht. Ich schaue nach: Die Zellen sind leer:
Dann entdecke ich die Ursache der Fehlermeldung und den Grund, warum Excel keine Spalten einfügen kann: in der Firma wurde ein Filter von der ersten bis zur letzten Spalte eingeschaltet!
Mein Kollege Philip hat mir heute eine Excel-Frage gestellt, die ich nicht beantworten konnte, wohl wieder so ein „bei mir ist es aber anders“-Phänomen.
Er hat eine Excel-Datei erstellt, in der Prozentzahlen stehen. Das Zahlenformat der Zellen ist benutzerdefiniert und heißt 0,0000%;-0,0000%
Bei jedem anderen werden beim Öffnen der Datei diese Zahlen so angezeigt, wie sie sollen:
Egal welche Version, von Excel 2010 über Excel Online bis Microsoft 365 war alles dabei.
Nur bei Philip nicht – hier werden die Zahlen immer z.B. als 0,435130 angezeigt.
Ich würde daraus schließen, dass es in seinem Excel irgendeine Einstellung gibt, die anders ist bzw. in der mal irgendwas verstellt wurde.
Seine Version ist Home & Business 2019, das dürfte aber keine Rolle spielen.
Weißt du das?
Viele Grüße, Dominic
####
Hi Dominic,
nein – DAS kenne ich nicht – ich verwende bei Prozent immer das Zahlenformat „Prozent“ und keine benutzerdefinierten Formate.
ich möchte (mit [Strg] + [+]) eine weitere Zeilen einer intelligenten Tabelle einfügen. Und erhalte folgende Fehlermeldung:
Hierdurch wird ein gefilterter Bereich in Ihrem Arbeitsblatt geändert. Um diesen Vorgang abzuschließen, entfernen Sie bitte die AutoFilter.
Seltsam, denke ich: in meiner Tabelle habe ich noch nichts gefiltert. Auch das Entfernen des Autofilters bringt kein Licht ins Dunkel. Aber dann entdecke ich UNTER der Tabelle eine weitere Liste mit einer Filterung:
DAS ist des Rätsels Lösung. Obwohl noch Platz für eine weitere Zeile wäre, kann Excel, aufgrund des gesetzten Filters oben keine weitere Zeile einfügen.
Wie man denn Links auf einem Tabellenblatt schnell löschen können, möchte eine Teilnehmerin meiner Excelschulung wissen. Ich schaue mir die Datei an:
Seltsam, denke ich, der Link ist nicht an eine Zelle gebunden. Ich klicke auf den Link und stelle fest, dass er an ein Rechteck (ohne Füllfarbe und ohne Rahmenfarbe) gebunden ist, das auf dem Tabellenblatt liegt.
Schöne Frage in der Excelschulung. Wir haben verschiedene Farben festgelegt, mit denen wir bestimmte Aufgaben kennzeichnen:
Nun passiert es manchmal, dass einige Kolleginnen und Kollegen ANDERE Farben verwenden als die Farben, die wir festgelegt haben. Das macht das Sortieren und Filtern mühsam. Kann man Farben schützen – in dem Sinne, dass nur bestimmte Farben verwendet werden können?
Die Antwort: leider nein. Mit VBA ist das möglich, aber nicht mit den Excel-Bordmitteln.
Umgekehrt: ich würde ein Auswahlfeld für die Aufgaben anbieten und auf Basis dieses Textes mit einer bedingten Formatierung die Zeilen enfärben.
Ein bisschen peinlich ist es schon. Ich habe für meine Excelschulungen viele Beispieltabellen mit Dummy-Daten. Einige davon sind hervorragend, um Dinge zu zeigen. In einer Schulung, in der ich die Pivottabellen erläutere, formatiert eine Teilnehmerin die Umsatzzahlen mit einem Klick auf das Symbol „Buchhaltungszahlenformat“ die Zahlen als Währung.
Und fragt mich, warum Excel „DM“ als Währung verwendet.
Die Antwort: weil ich diese Datei seit mehr als 20 Jahren verwende, zwar die Jahreszahlen austausche, aber immer noch die gleiche Datei.
Okay: ich sollte zumindest die Werte in eine neue, leere Datei kopieren, in der Euro in der Standardzellformatvorlage verwendet wird …
Da Leute dies unterschiedlich aussprechen, wiederhole ich den Namen „Pivot“ in verschiedenen Aussprachevarianten. Auch in der „französischen“ – ohne „t“.
Eine Teilnehmerin aus der Slowakei schaut irritiert auf. Ihr Nachbar kommentiert: „Du denkst jetzt an Bier, oder?“
Schöne Frage in der Excelschulung. In einer Excelmappe sind mehrere Tabellenblätter vorhanden, die alle den gleichen Aufbau aufweisen. Von allen soll nur ein Teil (jeweils der gleiche) ausgedruckt werden.
Leider versagt der Gruppierungsmodus (also mehrere Tabellenblätter markieren) UND „Druckbereich festlegen“:
Wir überlegen zusammen. Wenn mehrere Blätter markiert sind, wird die Markierung eines Bereichs auf allen Blättern übernommen.
Wählt man nun beim Drucken die Option „Auswahl drucken“ wird von jedem Blatt der selektierte Bereich gedruckt.
Nach der Excelschulung zeigt mir ein Teilnehmer eine Pivottabelle. Er möchte dort eine Berechnung durchführen. Eigentlich recht einfach: einen Wert durch 1.000 teilen. Kein Problem, oder?
Allerdings erhalten wir eine Fehlermeldung:
Es dauert eine Weile, bis ich dahinter komme, dass ich mir die Liste einmal anschauen sollte. Und tatsächlich: dort finde ich eine Spaltenüberschrift
in der sich nicht nur Leerzeichen und Sonderzeichen ([ ]) befinden, sondern auch noch Zeilenumbrüche ([Alt] + [Enter]).
Also noch ein Versuch: Da ich die unterste Zeile nicht sehe, drücke ich so lange die Taste [Pfeil unten] und [Pfeil rechts], bis ich glaube, das Ende erreicht zu haben. Dann füge ich die Rechnung (hier: / 1000) ein.
Speichert man in Outlook eine Anlage ab und öffnet sie und versucht nun in Outlook diese Anlage erneut zu speichern, erhält man folgende amüsante Meldung:
„Warum gleich zwei Mal?“, fragt sich Dominic, dem ich diesen Hinweis verdanke.
Hinweis: Microsoft war diesmal schnell. Inzwischen ist diese Meldung vereinfacht worden.
„Mit welcher Funktion“, will eine Teilnehmerin wissen, kann man die Änderung einer Zelle protokollieren. Das heißt: „wie kann man ermitteln, um welchen Wert eine Zelle geändert wurde.“
„Schöne Frage“, denke ich und antworte: „Das geht mit Excel-Formeln leider nicht. Das müsse man per Programmierung ermitteln. Beim Ändern einer Zelle wird der alte Wert mit dem neuen Wert verglichen.“
In der Excelschulung zeige ich, dass man mit [Strg] + [Pfeil unten] an das untere Ende (auf die letzte gefüllte Zelle) in einem Bereich springen kann. Mit [Strg] + [Pfeil oben] an das obere Ende.
Die Teilnehmerin fragt, wie man in die Mitte der Liste springen kann. Ich lache und sage ihr, dass es dafür keine Tastenkombination gibt. Aber: man kann den Zellnamen, beispielsweise A16, in das Namensfeld eintragen, [Enter] drücken – dann steht der Cursor in der Mitte der Liste.
Hallo Herr Martin, können Sie mir als Excel-Virtuose beim Umgang mit intelligenten Tabellen helfen? Es handelt sich um folgende Sache:
gegeben: ein Bereich mit insgesamt 14 Spalten und beliebig vielen Zeilen
Ziel: eine intelligente Tabelle bei der Spalte 1, Spalte 2-5, Spalte 6-10; Spalte 11-14 jeweils ein eigenes coloriertes Stripset besitzen (siehe Abbildung = 1. Versuch)
meine Versuche + aufgetretene Problematik:
Versuch: mehrere intelligente Tabellen mit dem jeweiligen Stripset (für Zeilen und Spalten) aneinanderfügen –> beim Hinzufügen einer neuen Zeile in Spalte 1 (und ff. neu alphabetisch sortieren lassen), sortieren sich die Zeilen der anderen Tabellen nicht automatisch mit –> Chaos Idee: Verknüpfung der aneinanderliegenden Tabellen, damit jede auf die Veränderung bspw. in der ersten Spalte (bzw. Tabelle) reagiert
Versuch: eine 14 Spalten umfassende intelligente Tabelle mit benutzerdefinierter Tabellenformatierung –> da die Tabellenabschnitte aus einer unterschiedlichen Anzahl aus Spalten bestehen, lässt sich nicht mal ansatzweise mein Ziel über das Formatieren des Stripsets „erste Spalte“ / „zweite Spalte“ verfolgen Idee: Hinzufügen der Auswahlfelder Stripset „dritte Spalte“, „vierte Spalte“, usw.
Versuch: eine 14 Spalten umfassende intelligente Tabelle mit „überdeckender“ händisch eingestellten Formatierung –> händisch eingestellte Formatierung ist nicht intelligent, sodass bei Filterfunktion die farbliche Unterscheidung zwischen den Zeilen nicht mehr gegeben war (Bsp. Zeile 1 gelb, Zeile 2 grün, Zeile 3 gelb; Filter Z.2 ; Zeile 1 gelb, Zeile 3 gelb)
Idee: Anwenden der bedingten Formatierung. Hierbei habe ich schnell festgestellt, dass mir der Ansatz gänzlich fehlt –> an welche „Variable“ od. „Konstante“ binde ich die Formatierung, damit ich einfach eine normale intelligente Tabelle erhalte, bei der sich die Hintergrundfarben unterschiedlich vieler Spalten abschnittsweise unterscheiden, die sich zusätzlich mitverändern, sollte man filtern oder neue Zeilen an unterschiedlicher Stelle hinzufügen
Ich hoffe, ich konnte es einigermaßen verständlich machen, woran es bei mir scheitert. Weiterhin hoffe ich, dass Sie Zeit und Lust haben, sich mit dieser Problematik zu befassen. Mit freundlichen Grüßen Marcel
PS: Ihr Forum „Excel nervt …“ ist mega unterhaltsam und hilfreich und befasst sich verständlich mit atypischen Fragen im Umgang mit dem Programm. Einfach genial. Hat mir sehr gefallen!
#####
Hallo Herr Gröschel,
1.) ich würde nicht mehrere intelligente Tabellen verwenden. Das widerspricht dem Gedanken der Tabellen.
2.) Ich habe nachgeschaut: es geht nicht mit dem Stripset. DAS ist recht einfach aufgebaut und erlaubt nicht so viele Varianten, wie Sie es gerne hätten.
3.) Warum nicht bedingte Formatierung? Setze ich auch gerne ein. Beispielsweise um eine Zeile farblich zu hinterlegen.
Die Formel (bspw.):
=UND(SPALTE()>=11;SPALTE()<=14)
Hilft das?
Liebe Grüße
Rene Martin
####
Guten Abend Herr Martin,
vielen Dank, dass Sie so schnell geantwortet haben! Ihre Formel hat mir grundsätzlich weitergeholfen. Damit konnte ich das Problem der verschiedenen Spaltenfarben in meiner Tabelle lösen. Es war simpel und genial.
Dennoch blieb das Problem der farblichen Abgrenzung zwei aufeinanderfolgender Zeilen bestehen. Zuerst probierte ich es mit:
=REST(ZEILE();2)
Hat soweit gut funktioniert, bis ich dann die Filterfunktion der intelligenten Tabelle verwendet habe. Im Ergebnis unterschied das Programm nicht zwischen eingeblendeten und ausgeblendeten Zeilen.
Basierend darauf kam ich durch weitere Recherche zur Ziellösung:
=REST(TEILERGEBNIS(3;$B$5:$B5);2)=0
(wobei die erste Spalte meiner Tabelle in Blattspalte B beginnt und ab Zeile 5 stets einen Wert beinhaltet)
Hierbei ist zu beachten, dass der gewünschte Effekt bzw. konkret die gewünschte farbliche Formatierung in der gesamten Tabelle nur dann funktioniert, wenn in Spalte B, in jeder Zelle der Tabelle auch ein beliebiger Wert steht. In meinem Fall befinden sich hier jeweils die Bezeichnungen.
Das wiederum bedeutet, dass beim Hinzufügen einer neuen Zeile vorerst nicht der gewünschte Effekt eintritt, sondern erst nach befüllen der neuen Zelle in Spalte B. Es ist unterm Strich noch nicht perfekt, aber es lässt sich damit arbeiten.
Ich unterrichte PowerQuery in einer internationalen Firma. Einige haben die englische Oberfläche eingestellt und damit auch das Dezimaltrennzeichen „.“ und die Datumsschreibweise „MM/TT/JJJJ“, einige Teilnehmer und Teilnehmerinnen „sprechen“ deutschen, also das Komma als Dezimaltrennzeichen und Datum in der Form „TT.MM.JJJJ“.
Beim Festlegen des Datentyps müssen nun einige auf „Dezimalzahl“ klicken, andere das Gebietsschema festlegen. Da ich verschiedene Übungsbeispiele mitgebracht habe, muss man entweder die eine oder andere Variante wählen:
Ich überlege: Wenn nun eine solche Datei mit einem PowerQuery-Zugriff ausgetauscht wird, wäre es doch sinnvoll IMMER das Gebietsschema der Quelle festzulegen, da es ansonsten zu Fehlern kommen kann:
Eine Teilnehmerin in der Schulung fragte, wie man geöffnete Dateien sortiert.
Der Gedanke: sie öffnet mehrere Dateien – allerdings leider nicht in der von ihr gewünschten Reihenfolge – und sucht dann eine Datei. Das kann bei sehr vielen Dateien recht mühsam sein, wenn man mit [Alt] + [TAB] sich durch die Dateien bewegt:
Meines Wissens kann man nicht die Reihenfolge ändern.
Auch nicht die Reihenfolge, wenn man auf das Excelsymbol in der Taskleiste klickt:
Aber: Da in der Registerkarte Ansicht im Symbol „Fenster wechseln“ die Dateinamen alphabetisch sortiert sind, fand sie diese Lösung sehr attraktiv:
Warum sie eine intelligente Tabelle nicht umbenennen dürfe, fragt eine Teilnehmerin in der Excelschulung.
Seltsam, denke ich: ein Tabellenblatt, eine intelligente Tabelle – Excel behauptet, dass dieser Name bereits vorhanden sei.
Ich werfe einen Blick in den Namensmanager:
Dort finde ich eine intelligente Tabelle und einen Namen. Was hat die Teilnehmerin gemacht?
Schritt 1: Wandle den Bereich in eine Tabelle um. Sie heißt nun Tabelle1:
Markiere die Liste und vergebe ihr einen Namen. Also nicht der Tabelle, sondern dem Bereich (hier: A1:F25)
Und so sieht man den Namen der Tabelle, aber nicht, dass ein anderer Bereich bereits mit dem Namen belegt ist, den man selbst gerne vergeben möchte … Perfide!
VBA-Schulung. Ein Teilnehmer wollte JSON-Datein in Excel haben. Kein Problem, sagte ich und zeigte ihm PowerQuery.
Allerdings – nach einigen fragenden Blicken – stellten wir fest, dass in seinem Excel 2016 (anders als meines in Microsoft 365) noch kein JSON-Zugriff vorhanden ist.- Ärgerlich!
und füge einige Zeilen ein – padautz: es erscheinen einige Zellen in schwarzer Farbe:
Ich habe die Formatvorlagen durchgesehen, die bedingten Formatierungen, es gibt keine schwarze Farbe auf dem Tabellenblatt, er rührt auch nicht von der Formatierung der intelligenten Tabelle.
Erst wenn ich alle Formatierungen lösche und neu einschalte, verschwindet der Spuk.
Ich habe keine Ahnung, woher und warum sich Excel auf dem SharePoint in diesen Zellen die Hintergrundfarbe zieht …
Mit PowerQuery wurde eine Abfrage erstellt. Diese enthält eine Liste. Wie kann man eine Dropdownliste mit einer Datenüberprüfung auf diese Liste erstellen, ohne dass die Daten nach Excel geladen werden?
Etwas irritiert bin ich schon. In einer PowerBI-Schulung erzählt mir ein Teilnehmer, dass er die Daten gerne in Excel hätte. Und dass er eigentlich gerne mit PowerQuery in Excel darauf zugreifen würde. Und nicht mit PowerBI. Aber die IT hätte ihm gesagt, dass es nicht gut ist, wenn mit PowerQuery so viele Abfragen auf die Datenbank abgesetzt werden. Besser wäre es, mit PowerBI auf die Datenbank zuzugreifen.
Ich bin mir nicht sicher, ob die Damen und Herren von der IT nicht wissen, dass PowerBI auch PowerQuery verwendet.
Schöne Frage in der Excelschulung: Wir haben auf SharePoint mehrere Excelmappen, in denen der Autofilter eingeschaltet ist. Kolleginnen und Kollegen laden die Dateien runter, filtern, vergessen den Filter auszuschalten und – nun ist für den nächsten Kollegen der „alte“ Filter aktiviert. Ob man das einstellen könne, dass beim Schließen der Datei zwar der Autofilter gesetzt bleibt, jedoch nicht eine mögliche Filterung.
Leider gibt es dafür keine Einstellung, lautete meine Antwort – das müsse man programmieren. Ein langes Gesicht war die Folge.
Warum rechnet der XVERWEIS bei mir nicht, möchte ein Teilnehmer in meiner Excelschulung wissen:
Ich lasse die korrekt berechnete Zelle editieren:
Ich sehe den Fehler nicht. Er als ich den Teilnehmer bitte, eine andere, fehlerhafte Zelle mit einem Doppelklick zu editieren, sehe ich, dass von den acht Dollarzeichen eines fehlt. Und somit sind die beiden Spalten nicht gleich groß.
Ich schmunzle, wenn Teilnehmerinnen oder Teilnehmer in meiner Excelschulung mich darauf aufmerksam machen, dass sie einen Fehler haben, ihn aber nicht finden. Und das, obwohl sie es GANZ GENAU SO GEMACHT HABEN, wie ich. Eben nicht, denke ich mir.
Wir üben die Funktionen der Kategorie „Nachschlagen und verweisen“: INDEX. Ich habe auf dem ersten Tabellenblatt „Entfernungen“ einen Entfernungsliste (von A nach B). Ei Wert soll Zeilen und ein anderer Spaltenweise gesucht werden. Auf einem zweiten Blatt beginne ich zu tippen:
=INDEX
und wechsle auf das erste Tabellenblatt, wo ich den Bereich markiere und fixiere ([F4]):
Dann drücke ich das Semikolon, um danach auf dem zweiten Blatt den zweiten Wert einzugeben. Allerdings vergisst die Teilnehmerin das Semikolon, klickt auf das andere Blatt, was Excel interpretiert als: „Sie möchte sicherlich einen anderen Blattnamen haben …“ Eben – das führt zu einem Fehler:
Mir ist es gerade passiert, dass mein erstelltes Liniendiagramm plötzlich nicht mehr sichtbar war.
Dann habe ich erst versucht, ein neues Diagramm zu erstellen und da war mein ursprüngliches Diagramm für kurze Zeit wieder sichtbar. Sobald ich jedoch bei dem neuen Diagramm auf OK drückte, war das alte wieder weg.
Ich bin dann mal kurz auf den Menüpunkt „Ansicht“ und dort auf „Umbruchvorschau“ gegangen und das ursprüngliche Diagramm war wieder da. Bei Rückkehr in die Normalansicht blieb es dann auch sichtbar und ich konnte das neue Diagramm löschen.
Excel scheint da irgendeinen Bug zu haben …
####
Ich kenne diese Anomalie nicht – sorry, Elke – dazu kann ich leider nichts sagen …
Hat heute ein Kollege rumgeschickt mit der Bitte um Hilfe, ich selbst hab das aber auch zum ersten Mal gesehen:
Meine These lautet, dass der Gegenüber (also der Verfasser der Kommentare) Microsoft 365 nutzt, mein Kollege hingegen Home&Business 2019, also eine etwas ältere Kauflizenz und dass die Ursache der Fehlermeldung in Kompatibilitätsproblemen der beiden Versionen liegt.
Die Kommentare konnten auch wirklich nicht angezeigt werden.
Meine Lösung war, die Datei auf OneDrive zu speichern und mit der Web-Version von PowerPoint zu öffnen, was dann auch funktioniert hat.
Nur irgendwie ist das schon doof, wenn bei sowas Simplen wie der Kommentarfunktion schon Probleme zwischen den beiden Versionen entstehen (Absicht von Microsoft um Menschen zur Abo-Version zu drängen?)-
Danke dir und viele Grüße, Dominic
###
Hi Dominic,
es bleibt spannend: NEIN – DAS habe ich noch nicht gesehen.
Zugegeben: ich arbeite selbst wenig mit PowerPoint und schule es auch sehr selten.
nach langer Zeit melde ich mich mal wieder mit einem kleinen Rätsel bzw. mit so einem kleinen „Bin ich doof?“-Moment. Es geht um die Schriftfarbe in PowerPoint
Das erste Problemchen war, dass ich daran gescheitert bin, mittels .TextFrame.TextRange.Font.Color.RGB die Schriftfarbe eines (bzw. natürlich mehrerer) Textfelder auszulesen – es wurde immer der anscheinend unsinnige Wert –2147483648 ausgegeben.
Farbenblind? Naja, irgendwie war das schon aber richtig (Beispielfolie im Anhang) – sieht grün aus.
Ich tippe in das Textfeld und klicke unter Start à Gruppe „Schriftart“ à Schriftfarbe à weitere Farben à Benutzerdefiniert und schaue mir den RGB-Code an: (0, 176, 80). Soweit so gut.
So, jetzt klicke ich aber auf den Rahmen des Textfeldes, da erscheint auch eine schöne Schaltfläche, die „Schriftfarbe“ heißt:
Hier ist jetzt ein RGB Wert von (0,0,0) zu sehen:
Ich würde jetzt mal spekulieren, dass dieses schwarz auch hinter der Ausgabe von –2147483648 steckt, die .TextFrame.TextRange.Font.Color.RGB ausspuckt.
Ist das normal, dass die eine Schriftfarbe nichts mehr der anderen zu tun hat? Ich bin verwirrt.
Viele Grüße, Dominic
###
Hi Dominic,
hübsch! Ich erkläre in PP-Schulungen immer, dass es drei Status bei Objekten gibt: entweder du bist „drauf“ (hast das Objekt markiert), oder „drin“ (im Text, in der Tabelle, im Diagramm, …) oder gar nicht markiert – also auf der Folie.
Richtig: wenn du den Rahmen markierst, ist die Schriftfarbe schwarz:
Wenn du dagegen IN dem Text bist, ist die Schriftfarbe „dein“ Grün:
Das heißt: du darfst nicht die Farbe des Rahmens auslesen (schreiben schon, bspw.: ActivePresentation.Slides(1).Shapes(1).TextFrame.TextRange.Font.Color = RGB(255, 0, 0) ), sondern eines Zeichens:
Bei mir erscheint seit geraumer Zeit auch diese ominöse Meldung mit dem „Das wird nicht funktionieren bla bla bal“ Allerdings erhalte ich die Meldung wenn ich versuche in einer Tabelle eine Spalte einzufügen und es ist egal ob links, oder rechts.
Perfide ist, dass das Ganze ab der Spalte BQ mit allen Tabellen funktioniert.
Da dachte ich, da muss irgendwo in einer „Zombie Zelle“ etwas stehen, was diese Funktion ab der Spalte A bis BQ stört. Also habe ich versucht die Tabellen in den betroffenen Spalten einfach nach rechts, > Spalte BQ zu verschieben. Dies mit der Absicht danach alle Spalten A bis BQ löschen zu können:
Mit einigen Tabellen hat das funktioniert, doch plötzlich geht das Einfügen von Spalten in einer Tabelle welche ab der Spalte BQ steht, auch nicht mehr und mir gehen langsam aber sicher die Ideen aus, was die Ursache sein könnte und bin schon ziemlich genervt.
Hast Du vielleicht eine Idee, was die Ursache sein könnte?
###
Hallo Herby,
nein – DAS kenne ich nicht. Ab und zu friert mein Excel ein; ich klicke wild drauf rum – aber meistens ist der „Schaden“ behoben, wenn ich Excel schließe und wieder öffne.
Lieber Herr Martin, ich habe eine „für mich“ komplexe Frage und finde keine Antworten im Internet. Kann xls 2016 bedingte Formatierungen für relative Bezüge herstellen? Ich möchte dass mein Wert heute abhängig vom Wert gestern, als bedingte Formatierung rote oder grüne Pfeile einsetzen. Wenn Wert höher als gestern -> grüner Pfeil. Wenn Wert niedriger als gestern -> roter Pfeil. Über die Wenn-Funktion kann ich ja keine bedingte Formatierung einbauen. Wissen Sie was ich tun kann ausser die Werte einzufärben, was ich schon tat? Ich würde zusätzlich gerne eben die Pfeile einbauen. Viele liebe und sonnige Grüsse
###
Hallo Frau Pap, die bedingte Formatierung kann bei Symbolen leider keine Formeln mit relativen Bezüge verwenden. Vor einigen Jahren hat mich ein Freund darauf aufmerksam gemacht; ich habe ihn nicht geglaubt und es bestätigt. Ich habe einen Artikel darüber geschrieben:
Hallo Herr Martin, ich verzweifle an einer wahrscheinlich simplen Excel Aufgae im Zuge meines Onboardings.
1) Fill in the question mark: 2,7,17,?,41,53,67,79
Das ist die Aufgabe. Ich gehe davon aus, dass es eine Art Logiktest ist, und excel die möglichkeit besitzt das ? durch die korrekte Zahl zu ersetzen?
Haben Sie hier ggf. einen Tipp für mich? Ich wäre Ihnen sehr dankbar und Sie würden qusi meine Woche retten. 😀 Alles Gute für Sie. Viele Grüße aus Hamburg
#
Hallo Frau F., die Lösung lautet 29. Was bekomme ich für die Erklärung?
#
Hahahaha 😀 meine Hochachtung!
Und wie ist die Excel Formel dazu? Dann wären Sie mein Held des Tages!!!
#
keine Excel-Formel. Werfen Sie mal einen Blick auf die ersten Primzahlen: 2, 3, 5, 7, 11, 13, 17, 19, 23, 29, 31, 37, 41, 43, 47, 53, 59, 61, 67, 71, 73, 79, 83, 89, 97.
Und nun streichen Sie jeweils zwei weg …
#
Verrückt! Mit Zahlen habe ich es leider nicht so, aber das sieht sehr logisch aus!
Da das nun aber eine >Excel Skill Aufgabe ist. Frage ich mich, was möchte der Prüfer hier von mir. Irgendwas mit Excel muss die Frage doch zu tun haben.- Zumal Sie in einem Excel Sheet gestellt ist
#
Hallo Frau F., erzeugen Sie zu den Werten ein Diagramm und lassen sich eine Trendlinie anzeigen. Der polynomische Trend approximiert am besten. Auch hier lautet die Lösung ungefähr 29.
Ich erstelle ein Add-In für eine Firma. Es soll eine große Liste per Knopfdruck in Einzelteile zerlegen und diese an bestimmten Stellen auf der Festplatte speichern.
Dazu benötige ich eine eindeutige Liste der Kategorien:
Ich überlege: da die Firma Excel 2016 einsetzt, hat sie noch nicht die Funktion EINDEUTIG. Also erzeuge ich per Programmierung eine Pivottabelle und erhalte so eine (sogar sortierte) Liste der einzelnen Kategorien:
Sub MachePivot()
Dim xlBlattAktiv As Worksheet
Dim xlBlattHilf As Worksheet
Dim xlPivotCache As PivotCache
Dim xlPivotTabelle As PivotTable
Dim lngZeilen As Long
Dim lngSpalten As Long
Set xlBlattAktiv = ActiveSheet
Set xlBlattHilf = ThisWorkbook.Worksheets.Add
lngZeilen = xlBlattAktiv.Range("A1").CurrentRegion.Rows.Count
lngSpalten = xlBlattAktiv.Range("A1").CurrentRegion.Columns.Count
Set xlPivotCache = ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=xlBlattAktiv.Name & "!R1C1:R" & lngZeilen & "C" & lngSpalten, _
Version:=8)
Set xlPivotTabelle = xlPivotCache.CreatePivotTable( _
TableDestination:=xlBlattHilf.Range("A1"), _
TableName:="RenesPivot2", _
DefaultVersion:=8)
xlPivotTabelle.ColumnGrand = False
xlPivotTabelle.RowGrand = False
With xlPivotTabelle.PivotFields("Kategorie")
.Orientation = xlRowField
.Position = 1
End With
End Sub
Ich teste – und: padautz: Fehler!
Ich brauche eine Weile, bis ich die Ursache finde. In der Liste gab es zwei Mal eine Spalte mit der Bezeichnung „Kommentar“. Excel 2016 schafft es nicht, die Spalten für die Pivottabelle umzubenennen (wie neuere Excelversionen):
Tag und Monat werden nicht getrennt. Ich ahne es. Ich ermittle den Code des dritten Zeichens der Zelle C2:
=CODE(TEIL(C2;3;1))
Das Ergebnis lautet 160. Anders dagegen das zweite Leerzeichen, also Zeichen Nummer 8:
=CODE(TEIL(C2;8;1))
Hier lautet das Ergebnis 32, also Leerzeichen, während es sich beim ersten Blank um ein geschütztes Leerzeichen handelt. Warum das? Soll beim Verschieben der Seite Tag und Monat nicht getrennt werden? Was vielleicht auf der Internetseite „schön“ aussieht, erweist sich als Ärgernis bei der Weiterverarbeitung der Daten. Oder will wikipedia nicht, dass man seine Daten weiter benutzt?
Übrigens: Wählt man in PowerQuery den Befehl „Spalte teilen“, so schlägt PowerQuery #(00A0) als Trennzeichen vor …
Wenn man mit PowerQuery auf eine „ältere“ XLS-Excelmappe zugreift, werden folgende Spalten angezeigt:
Beim aktuellen Dateiformat XLSX dagegen drei weitere:
Die drei Spalten (mit ihren Informationen) Item, Kind und Hidden fehlen.
Würde man eine XLS-Datei in XLSX umbenennen, wäre das Ergebnis das Gleiche wie bei XLS:
Wer macht denn so etwas? Und: DAS würde man in Excel doch sofort bemerken.
Ich erhalte vorgestern die Frage, warum das PowerQuery-Tool, das ich für die Firma erstellt habe bei einer Datei nicht läuft. Ich stelle fest – obwohl die Datei vom Format XLSX ist, werden nur die beiden Spalte Name und Date angezeigt, nicht jedoch Item, Kind und Hidden. Warum?
Ich gehe auf die Suche.
Ich stelle fest, dass die im openXML-Format verwendete interne Datei app.xml (im Ordner docProps) folgendermaßen aussieht:
Wenn ich die Datei, die von Apache POI erstellt wurde, öffne, speichere und schließe, sieht diese XML-Datei so aus:
SO jetzt jede XLSX-Datei aus, die von Excel erzeugt und in Excel gespeichert wurde.
Das bedeutet: das (umstrittene) Werkzeug Apache POI produziert XLSX-Dateien, die nicht genau der Spezifikation von Microsoft entsprechen. Ist das schlimm?
Ja, weil mein Werkzeug auf die Spalte „Kind“ zugreift und diese nicht findet …
Nicht aufgepasst. In einer Liste existiert eine Datenüberprüfung. Die Quelle der Liste ist jedoch keine „feste Liste“, sondern liegt in einem Zellbereich:
Per VBA kopiere ich diese Liste auf ein anderes Tabellenblatt und wundere mich, warum die Datenüberprüfung nicht mehr funktioniert. Die Antwort: der Verweis wird auf das neue Blatt gesetzt …
Irgendwie doof. Warum nennt Microsoft die Parameter xlToRight und xlToLeft, aber xlDown und xlUp in der Funktion End? Das führt leicht zu Tippfehlern, wenn man nicht genau bei IntelliSense hinschaut:
Ein Dankeschön an Mark Risner. Er hat die Fehlermeldung „Wir haben alle Daten neben Ihrer Auswahl untersucht und konnten kein Muster zum Ausfüllen mit Werten erkennen.“ erhalten.
Was hat er gemacht: mehrmals hintereinander das Ergebnis der Blitzvorschau gelöscht und erneut die Blitzvorschau aktiviert. Irgendwann funktionierte sie nicht mehr.
Ich konnte diesen Fehler und diese Fehlermeldung leider nicht reproduzieren …
Erstellt man in Pivottabellen ein Stripset, wird es nur angezeigt, wenn die Option „Gebänderte Zeilen“ aktiviert ist.
Erstaunlicherweise verschwinden die Farben auch dann, wenn keine Werte vorhanden ist – das heißt: man kann mit den Stripsets keine gruppierte Liste formatieren:
Die neuen Arrayfunktionen, die dynamisch in mehrere Zellen die Ergebnisse schreiben und die „starren“ (intelligenten) Tabellen, arbeiten nicht zusammen. Will man die Funktion TEXTTEILEN auf eine Zelle (also auf eine Spalte) anwenden, um die Inhalte zu trennen:
lautet das Ergebnis #Überlauf, da diese Funktion keine neuen Spalten generieren kann:
Microsoft hat es nun doch wahrgemacht: die Begriffe werden gegendert. Wird es auch bald männliche Zellen, Spalten und Zeilen geben? Und einen weiblichen Nachfolger, Autor und Thesaurus?
In Excel erstellt man einen Zeilenumbruch in einer Zelle mit der Tastenkombination [ALT] + [Enter]. Der dahinterliegende Code hat die Nummer 10. Importiert man eine Liste mit Zellen mit Zeilenumbrüchen nach PowerQuery, sieht man schnell, dass #(lf), also Linefeed diesem Zeichen entspricht.
Aha, denke ich mir: es wäre doch schön, wenn eine Liste nicht mit Semikola getrennt wären:
Ich öffne den Dialog und trage statt Semikolon in der Kategorie „benutzerderfiniert“ #(lf) ein;
Das Ergebnis irritiert:
PowerQuery hat den Code geändert in:
= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), "#(#)(lf)"), type text})
Natürlich könnte man es per Hand ändern in „#(lf)“
Oder durch die Funktion
Character.FromNumber(10)
ersetzen:
= Table.TransformColumns(#"Grouped Rows", {"Anzahl", each Text.Combine(List.Transform(_, Text.From), Character.FromNumber(10)), type text})
Schließlich entdecke ich, dass in der Kategorie „benutzerdefiniert“, dass man die Texte „mithilfe von Sonderzeichen verketten“ kann. Und dort findet sich auch der Zeilenvorschub. Und dieses #(lf) wird von PowerQuery auch nicht geändert.
Lieber Rene, Du hast mir vor einiger Zeit mit einem Tipp zu Snagit geholfen. Jetzt habe ich eine Frage, die evtl. blöd erscheint..aber ic bekomme es nicht hin. Vielleicht hättest Du 2 Min. Zeit mir zu helfen?
Unter der Registerkarte Daten habe ich im Menüband auf der rechten Seite einfach zu viel leere von leerer Fläche und gleichzeitig wird sieht man z.B. das Symbol für Blitzvorschau ohne Bezeichnung.
Ich habe Dir einen Screenshot gesendet. Kann man irgendwie diesen leeren Bereich in der Registerkarte Daten loswerden?
Ich wäre Dir wirklich dankbar für einen Tipp
Liebe Grüße Max
Hallo Maximilian, die Antwort kurz und knapp: NEIN. Wenn du in die Definition der Symbole reinschaust – dort gibt es nur die Größen small und normal. Wenn ich beispielsweise einen oder zwei small-Symbole verwende, ist noch Platz für eine drittes nach unten. Das hatte ich beispielsweise mal in folgender Gruppe für Programme, die ich für eine Sparkasse gebaut habe:
Die Breite der Symbole richtet sich nach dem Text. Ist er zu lang, werden die Gruppen zusammengefasst. Wenn noch Platz nach rechts übrig ist … Tja – keine Chance. Auf einem anderen Bildschirm wird das dann anders berechnet …
so wie du es geschrieben hast, habe ich das Makro aufgezeichnet: ändere die Skalierung der y-Achse. Allerdings erhalte ich einen Fehler, wenn ich das Makro laufen lasse:
Hallo Nadine,
wahrscheinlich liegt es daran, dass du das Diagramm nicht markiert hast? (ActiveChart)
Ich erhalte eine Excelmappe von einem Teilnehmer zugeschickt – er möchte, dass wir darüber reden.
Ich öffne die Datei und werde aufgefordert, mich anzumelden. Eine Verweigerung bewirkt, dass die Datei nicht geöffnet wird. Also erneut: Datei öffnen, meine Mailadresse eingeben:
Ich werde auf das Firmenportal weitergeleitet, wo ich mich erneut authentifizieren soll.
Kann ich nicht – ich breche ab. Erneut muss ich mich anmelden. Das Spiel wird vier Mal wiederholt. Dann bin ich drin.
Ich entdecke, dass in der Datei zwei Namen auf andere Dateien verweisen, die auf dem SharePoint der Firma liegen.
Diese Namen werden nicht verwendet. Ich lösche sie. Schließe die Datei, öffne sie wieder und: erneut muss ich mich mehrmals „anmelden“. Ich wühle weiter und werde fündig. In den Informationen der Datei befinden sich weitere Verknüpfungen:
Zum Glück kann ich sie löschen und speichern. Beim nächsten Öffnen entdecke ich: der Spuk ist vorbei!
VBA-Schulung. Wir üben den Makrorekorder. Ändert man die Schriftart über das Dropdownsymbol in der Registerkarte „Start“,
zeichnet der Makrorekorder auf:
Sub Schriftart()
'
' Schriftart Makro
'
'
With Selection.Font
.Name = "Algerian"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
End Sub
Also: der ganze Dialog (oder genauer: die Registerkarte „Schrift“ des Dialogs „Zeichen formatieren“) wird aufgezeichnet.
Ein Teilnehmer fragt, was denn aufgezeichnet wird, wenn man im Dialog „Zeichen formatieren“ die Schrift ändert und dies aufzeichnet. Peinlich: ich weiß es nicht. Wir zeichnen auf. Das gleiche Ergebnis – auch hier wird nicht der gesamte Dialog (wie beispielsweise beim „Seite einrichten“) aufgezeichnet, aber auch nicht nur der eine Befehl, der geändert wurde, sondern sämtliche Befehle des Registers:
Die neuen Textfunktionen sind noch nicht ganz bei mir angekommen:
Sie sprechen noch ein bisschen Englisch; aber – was schlimmer ist – sie arbeiten noch nicht korrekt. TEXTTEILEN sollte alle Textteile liefern.
Okay, okay, verstanden – diese Arrayfunktion darf nicht (anders als TEXTVERKETTEN) auf den ganzen Bereich angewendet werden, sondern nur auf eine Zelle. Und dann runterziehen!
Eine Schulung. Ein Grafikprogramm. Und es kommt – wie so häufig – die Frage auf: „Ach, wo ich mich gerade mit Ihnen unterhalte – ich habe da mal eine Excelfrage. Wir haben vom Kunden eine Datei erhalten, da ist ein Schutz drauf. Können Sie den wegmachen?“
Ich lasse mir die Datei zeigen:
Und dann: sehen Sie, wenn ich nun den Tippfehler in Zelle B11 ändern will, dann darf ich das nicht. Da ist ein Schutz drauf. Sie zeigt es mir:
Ich lache und bitte sie in der Datenüberprüfung nachzusehen. Wir werden fündig: die Werte werden von einem ausgeblendeten Tabellenblatt „geholt“:
Das Tabellenblatt „Daten“ kann man einblenden – tatsächlich: dort finden sich in A2:A4 die Daten:
Wir ändern den Tippfehler, blenden das Tabellenblatt wieder aus. Natürlich ist sie traurig, dass die Fehler auf dem anderen Blatt nicht sofort geändert wurden. Aber mit dem Befehl „Ersetzen“ funktioniert das schnell.
Was mich DANN sehr amüsiert hat, war ihre Erzählung (sie zeigte mit den Chatverlauf in teams), dass sie bei der IT Ihrer Firma nachfragte, ob diese nicht die Ursache des Problems kenne. Sie hatte gebeten, man möge die Originaldatei ohne Schutz kommen lassen. Oder: die Datei noch einmal erstellen!?! Ich war schon sehr irritiert und habe einen Grundkurs Excel in Ihrer Firma angeboten.
VBA-Schulung. Ich lasse während der Schulung kleine „Lösungen“ programmieren. Ich schaue den Teilnehmern und Teilnehmerinnen über die Schulter. Bei einem der Teilnehmer finde ich die Codezeilen:
i = 3
MsgBox ActiveCell(i)
Die ActiveCell ist B1, ActiveCell(3) liefert den Inhalt der Zelle B3, hier: „Peking“:
Das heißt: ActiveCell(1) meint die Zelle selbst, ebenso wie ActiveCell(1, 1). Dies ist also keine Kurzschreibweise für ActiveCell.Offset(1, 1), denn damit wäre C2 gemeint. Auch nicht für Cells(1, 1) – das wäre A1.
Diese Schreibweise gehört dann zur Kategorie ActiveCell.Range(„A1“), womit B1, also die aktive Zelle gemeint ist (A1 ist hier ein relativer Bezug).
Ich würde schreiben:
Cells(3, 2)
oder
Range(„B1“).Offset(2, 0)
wenn ich B3 meine.
Und: die Eigenschaft „Value“ nicht vergessen!
Und: noch angeben, auf welchen Tabellenblatt die Zelle liegt.
Erstaunlich. In einem Bereich stehen Datumsangaben. In einer Zelle steht eine andere Datumsangabe. Sie soll „gesucht“ werden. Weder ZÄHLENWENN noch VERGLEICH haben ein Problem damit:
Wahrscheinlich ist die Ursache folgende: Excel kennt nur die Datentypen Text und Zahl, während VBA verschiedene Datentypen für Zahlen kennt (Integer, Long, Double, …), aber auch für Datum (Date).
Also: Flux den Wert der Variablen datDatum in eine Long-Zahl konvertieren (CLng(datDatum) ) – und schon klappt es wieder:
Manchmal nervt Excel aufgrund merkwürdiger Einstellungen.
Manchmal nerven Anwenderinnen und Anwender, die merkwürdige Dinge machen.
Es gibt noch andere nervige Quellen.
Excelschulung. Eine Teilnehmerin möchte einen Zeitstempel haben. Sie möchte in eine Spalte ein Namenskürzel eintragen, mit der Folge, dass in der Spalte daneben die aktuelle Uhrzeit eingetragen wird. Allerdings soll diese nicht verändert werden. Das heißt: HEUTE() wird beim ersten Mal eingetragen; steht bereits ein Datum in der Zelle, wird das Datum mit sich selbst überschrieben:
WENN(B2="";HEUTE();B2)
Das Ganze wird „ummantelt“ von der Prüfung des Namenskürzels:
=WENN(A2<>"";WENN(B2="";HEUTE();B2);"")
Also so:
Damit dies funktioniert, müssen die Iterationen (in den Optionen) aktiviert sein – eine Iterationszahl von 1 genügt:
„Genau so habe ich gemacht“, insistiert die Teilnehmerin – aber am nächsten Tag geht es nicht mehr.
Wir schauen nach: Excel beenden und erneut öffnen: Klappt!
Rechner herunterfahren und neu starten: die Option „Iterative Berechnung aktivieren“ wurde deaktiviert. Wahrscheinlich von einem Script von der IT, das beim Starten des Rechners Einstellungen ändert.
Klickt man in Excel auf Datei / Speichern unter, wählt einen Dateinamen einer anderen Datei, die bereits existiert, wird man gefragt, ob man die Datei ersetzen möchte. In VBA liefert der Befehl SaveAs die gleiche Meldung:
Jedoch nicht die Methode SaveCopyAs. Dort wird die vorhandene Datei (sofern nicht geöffnet) kommentarlos überschrieben.
Randbemerkung: Klickt man im Meldungsfenster auf die Buttons „Nein“ oder „Abbrechen“ ist eine Fehlermeldung die Folge …
Will man Formeln in Werte verwandeln, sind mehrere Schritte nötig:
Die Zellen mit den Formeln kopieren
Über das Kontextmenü auf das Symbol [123] klicken
Hierfür sind mehrere Klicks nötig. Etwas schneller geht es mit der Tastenkombination [Strg] + [Alt] + [V] – damit wird der Dialog „Inhalte einfügen“ aufgerufen. [Alt] + [W] (für Werte) und mit [Enter] bestätigen.
Damit Excel etwas weniger nervt.
Randbemerkung: Man kann auch den Bereich mit der rechten Maustaste verschieben und wieder an gleicher Stelle fallen lassen und anschließend die Option „Hierhin nur als Werte kopieren“ auswählen:
Schöne Frage in der Excelschulung als wir (intelligente) Tabellen anschauen:
Wenn man hinter (oder unter) der Tabelle eine neue Spalte einfügt, wird diese in die Tabelle übernommen:
Wird jedoch VOR der Tabelle eine Spalte eingefügt, ist sie nicht Teil der Tabelle. Abhilfe schafft das Symbol „Tabellengröße ändern“, mit dessen Hilfe der Bereich der Tabelle erweitert werden kann:
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.
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-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 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 Barwert
Ist 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?
Nun muss ich eine weitere Spalten mit berechneten Werten aus anderen Spalten einfügen. Die neue Spalte soll den Barwertfaktor enthalten. Die Formel dazu:
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 …
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] )
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
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.
In PowerQuery-Schulungen empfehle ich die Option „Spaltentypen und -überschriften für unstrukturierte Quellen niemals erkennen.“ einzuschalten. Warum? Beim Import von Textdateien und CSV-Dateien werden Datumsinformationen in Zahlen konvertiert, wie folgende Screenshots zeigen:
Diese Option steht in Microsoft 365 – jedoch nicht in Excel 2016 zur Verfügung:
Allerdings: in einem Ordner befinden sich eine Reihe gleichförmig aufgebauter Excelmappen:
Greift man mit PowerQuery auf den Ordner zu und lässt sich den Content anzeigen, dann wird die erste Zeile nicht als Überschrift erkannt und in die Liste eingefügt:
Natürlich kann man die erste Zeile zur Überschrift machen und die übrigen Zwischenzeilen löschen. Ist aber nervig. Was tun?
Ich weiß es nicht?
Sich an der Variante orientieren, die man häufiger verwendet: Zugriff auf Ordner oder Zugriff auf Text/CSV-Dateien?
Vor dem Zugriff die entsprechende, geeignete Variante einschalten, beziehungsweise ausschalten?
Folgender Beitrag hat mich vor einigen Tagen erreicht, den ich gerne weitergeben möchte:
„
Guten Tag, Excel mag zwar Vieles können, aber MS kann es nicht erklären. Nach einigen Stunden Probierens bleibt folgendes Problem: Ich habe ein Liniendiagramm mit Temperaturwerten für die Jahre 10800 bis 1600 vuZ, also -10800 bis -1600.
Fehler in Excel: man muss ihm erst über eine Box „sagen“, dass er die negativen (mit Minuszeichen versehenen) Werte aufsteigend anzeigt. Leider schickt aber Excel dann entgegen normaler Verfahren auch unerwünscht die vertikale Beschriftung der Temperaturdaten nach rechts, ohne dass man das irgendwo separat einstellen kann. Unmöglich! Jedenfalls steht eine Anleitung, wenn vorhanden, nicht da wo sie hingehört, nämlich Achsenbeschriftung. 2.1. Ungelöst: Ich möchte, dass nur die Tausender (und vielleicht die Hunderter, je nach späterer Größe) angezeigt werden. Excel erlaubt aber, soweit ich sehe, nur die Abstände zu bestimmen und zeigt nun entgegen dem Userwunsch die Daten ab 1600 rückwärts in Tausendern an. Unmöglich. Man wird nach tausend Dingen gefragt. Nur, wie bringe ich Excel bei, von -10’000 an die Tausender (wertemäßig) aufsteigen zu lassen? (Merke: -1’000 ist ein höherer Wert als -2’000 und sollte daher rechts erscheinen) 2.2. Senkrechte Achsen erscheinen – egal mit welcher Einstellung, nur bei 1600, 5600 und 9600, also in 4000 Jahren Abstand. Hab ich nirgends eingestellt. Das Programm ist zum Verzweifeln, im Gegensatz zu meinem wunderbaren Atari-Spreadsheet-Programm BSTAT seligen Angedenkens. 2.3. Von statistischen Glättungsverfahren will ich gar nicht erst anfangen. Mit genervten Grüßen und Bewunderung für die Hilfsbereitschaft, Hans J. Holm“
Schöne Frage in der letzten PowerQuery-Schulung: wo befindet sich das (auch Excel bekannte) Symbol, das erlaubt nicht nur eine Spalte zu sortieren, sondern nach mehreren:
Die Antwort: ein SYMBOL hierfür gibt es nicht – man muss die Spalten in der gewünschten Sortierreihenfolge anklicken und sortieren, beispielsweise zuerst Ort; innerhalb eines Ortes (Aachen) nach der PLZ, innerhalb einer PLZ (beispielsweise 52062) nach der Straße, …
PowerQuery quittiert die Mehrfachsortierung mit dem Befehl
Wie kann ich denn in einer Wordtabelle zwei Uhrzeiten berechnen? Beispiel: A2 und B2 sind als Feld mit Datum HH:mm definiert. Bei C2 kommt aber leider nie 01:30 als Ergebnis raus. A2 = 12:00 B2 = 13:30 C2 = Hier soll das Ergebnis stehen von der Rechnung B2 – A2
Hallo Herr D.,
Sie haben recht, Herr Gauger, sowohl bei Berechnungen in Feldfunktionen als auch in Tabellen kann Word nicht mit Uhrzeiten rechnen. Das Ergebnis ist ein krudes Gemisch aus Stunden und Minuten, also unbrauchbar. Schade!
Hallo Rene.
Mir ist folgende Methode bekannt um in Word mit Uhrzeiten zu rechnen.
Dazu muss in den beiden Textformatfeldern welche die Uhrzeiten enthalten bei „Optionen für Textformatfelder“ eine Textmarke eingetragen werden.
Dann wird in die Zelle in der das Ergebnis der Uhrzeitberechnung angezeigt werden soll, folgendes FELD eingetragen.
Wenn in den Textformularfeldern die Box „beim Verlassen berechnen“ angekreuzt wird, kommt es zu einer Aktualisierung auch des REF-Feldes. Scheinbar werden dann alle Felder im aktiven Dokument (außer denen in den Kopf- oder Fußzeilen) aktualisiert.
Salü
Ernst
Hallo Rene
Die Darstellung als HH:mm hat mir keine Ruhe gelassen., so dass ich doch noch eine (wohl sehr umständliche) Methode gefunden habe.
Die zusammengesetzte Feldfunktion zur Berechnung der Zeitdifferenz ist sehr umständlich. Allerdings ist die Funktion immer die gleiche, so dass sie als Schnellbaustein abgespeichert werden kann. Einzige die angesprochenen Textmarken müssen jeweils geändert werden.
warum findet Excel Costa Rica nicht? Es gibt Costa Rica!
Genauer: ich habe mit einer Suchformel
=INDEX($F$2:$F$114;VERGLEICH(A9;$G$2:$G$114;0))
einen Wert gesucht. Alle Werte werden gefunden – nur nicht Costa Rica:
Zuerst vermutete ich ein Leerzeichen hinter einer der beiden Wörter. Die Schreibweise ist korrekt. Dann vermute ich das Leerzeichen als Übeltäter. Und so ist es auch: die Funktion
=CODE(TEIL(G23;6;1))
liefert den Wert 160 (geschütztes Leerzeichen) und nicht 32 (Leerzeichen), wie erwartet:
Auf mein Nachfragen erfahre ich: „… ich habe die Liste doch aus wikipedia kopiert. Und ich habe sie explizit als Wert eingefügt – OHNE Formatierung!“ Das genügt leider nicht … wikipedia verwendet an einigen Stellen geschützte Leerzeichen und bedingte Trennstriche. Das macht das Weiterverrbeiten solcher Listen in Excel manchmal mühsam. Böses Excel …
