Eine Liebeserklärung an MS Excel
Schöne Frage in der Excelschulung.
Erstellt man eine Datenüberprüfung (beispielsweise nur ganze Zahlen) und trägt etwas ein, dass dieser Regel widerspricht (beispielsweise einen Text), wird der Text zwar eingetragen und die Fehlermeldungen in Excel angezeigt, jedoch wird der Wert nicht übernommen. Die Datenüberprüfung muss abgebrochen werden und der Spuk verschwindet wieder:
Martin Weiß (tabellenexperte.de) hat auf unserem letzten Excelstammtisch den Befehl „Änderungen anzeigen“ in Excel online in Excel in Microsoft 365 vorgestellt. Und dabei erklärt, dass beispielsweise angezeigt wird:
* Verschieben
* Sortieren
* Einfügen
* Löschen von Zellen oder Bereichen
Erstaunlicherweise werden nicht angezeigt:
* Erstellen und Änderungen an Diagrammen, Formen oder anderen Objekten
* PivotTable-Vorgänge
* Formatierungsänderungen
* Ausblenden von Zellen oder Bereichen und Filterung
Ob das Absicht ist, fragen wir uns? Oder wird dieses Werkzeug noch erweitert?
Dennoch: interessant und wichtig ist es.
UND ist nicht UND; ODER ist nicht ODER.
Augen auf bei den logischen Konjunktoren in DAX – sie sind nicht konsistent!
Über die Funktion CALCULATE kann ein Filterkontext mitgegeben werden (oder man einen Filter auch aufheben). Allerdings leider nicht konsistent, was UND beziehungsweise ODER betrifft:
Beginnen wir mit ODER:
|| ist das Zeichen für das logische „ODER“ und kann für eine Spalte verwendet werden:
J Oder01 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Mitgliedschaft] = "Platinum"
|| tbl_Kunden[Mitgliedschaft] = "Gold"
)Platinum oder Gold – kein Problem! Auch die Alternative mit der Funktion OR klappt:
J Oder02 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
OR (
tbl_Kunden[Mitgliedschaft] = "Platinum";
tbl_Kunden[Mitgliedschaft] = "Gold"
)
)Filtert man jedoch über zwei verschiedene Spalten, scheitert ||:
J Oder03 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Mitgliedschaft] = "Platinum"
|| tbl_Kunden[Jahresbeitrag] > 180
)aber auch OR liefert einen Fehler:
J Oder04 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
OR ( tbl_Kunden[Mitgliedschaft] = "Platinum"; tbl_Kunden[Jahresbeitrag] > 180 )
)Also muss man die Funktion FILTER verwenden. Kein Problem bei ||:
J Oder05 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
FILTER (
tbl_Kunden;
tbl_Kunden[Mitgliedschaft] = "Platinum"
|| tbl_Kunden[Jahresbeitrag] > 180
)
)Allerdings versagt hier die Funktion OR:
J Oder06:=
CALCULATE(sum(tbl_Kunden[Jahresbeitrag]);FILTER(or(tbl_Kunden;tbl_Kunden[Mitgliedschaft]="Platinum" ; tbl_Kunden[Jahresbeitrag]>180 )))OR wird nicht in der Funktion FILTER akzeptiert!
Und wie sieht bei der Verkettung zweier logischer Bedingungen mit UND aus?
J Und01 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140
&& tbl_Kunden[Jahresbeitrag] < 180
)&& stellt kein Problem dar. Auch die Funktion AND nicht:
J Und02 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
AND ( tbl_Kunden[Jahresbeitrag] > 140; tbl_Kunden[Jahresbeitrag] < 180 )
)Die Funktion CALCULATE bietet die Möglichkeit mehrere getrennte Bedingungen mit einem Semikolon zu trennen. Auch das klappt problemlos:
J Und03 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140;
tbl_Kunden[Jahresbeitrag] < 180
)Und wie sieht es bei zwei verschiedenen Spalten aus? Ähnlich wie bei ODER liefern die beiden Varianten it && und AND einen Fehler:
J Und04 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140
&& tbl_Kunden[Mitgliedschaft] = "Gold"
)Und auch das führt zu einem Fehler:
J Und05 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
AND ( tbl_Kunden[Jahresbeitrag] > 140; tbl_Kunden[Mitgliedschaft] = "Gold" )
)Also bleibt die Variante mit dem Semiloka – sie funktioniert:
J Und06 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
tbl_Kunden[Jahresbeitrag] > 140;
tbl_Kunden[Mitgliedschaft] = "Gold"
)Und die Funktion FILTER? Analog zu ODER klappt &&:
J Und07 :=
CALCULATE (
SUM ( tbl_Kunden[Jahresbeitrag] );
FILTER (
tbl_Kunden;
tbl_Kunden[Jahresbeitrag] > 140
&& tbl_Kunden[Mitgliedschaft] = "Gold"
)
)AND ist im Kontext von FILTER (ebenso wie OR) verboten:
J Und08:=
CALCULATE(sum(tbl_Kunden[Jahresbeitrag]);FILTER(tbl_Kunden;and(tbl_Kunden;tbl_Kunden[Jahresbeitrag] > 140 ; tbl_Kunden[Mitgliedschaft] ="Gold")))Und da FILTER nur ein Filterkriterium akzeptiert, führt auch die letzte Lösung zu einem Fehler:
J Und09:=
CALCULATE(sum(tbl_Kunden[Jahresbeitrag]);FILTER(tbl_Kunden;tbl_Kunden;tbl_Kunden[Jahresbeitrag] > 140 ; tbl_Kunden[Mitgliedschaft] ="Gold"))Fazit:Man muss bei der Und- beziehungsweise Oder-Filterung gut aufpassen, welche der Varianten man verwendet.
Hier noch einmal der Screenshot mit den sechs Oder-FIlterungen und den neun Und-Filtern:
Amüsiert.
Ich erkläre in der Excel-Schulung, dass man mit der Taste [entf] eine Zelle oder mehrere markierte Zellen löschen kann. Eine Teilnehmerin beschwert sich, dass es bei ihr nicht funktioniere:
Klar: sie hat nicht die Taste [entf], sondern [Backspace] ([Rückschritt] gedrückt. Ich erkläre ihr den Unterschied:
Was Excel so alles macht …
Excelschulung. Ich erkläre die AutoKorrektur.
Eine Teilnehmerin legt auf das Kürzel „HS“ den Namen der Gemeinde Höhenkirchen-Siegertsbrunn:
Sie trägt „hs“ in die Tabelle ein – nichts passiert:
Ich bitte sie, die Schreibweise von „HS“ in „hs“ zu ändern:
Nicht passiert.
Man muss in der AutoKorrektur den alten Eintrag löschen, bestätigen, die AutoKorrektur erneut aufrufen und dann noch einmal „lernen lassen“.
Nichts passiert:
Es dämmert mir. Die Teilnehmerin schreibt „hs“ unter die Liste der „hs“. Jetzt greift nicht die AutoKorrektur, sondern das AutoVervollständigen. Ich bitte sie den Text in eine Zelle NEBEN der Liste einzutragen:
Und tatsächlich: DORT und JETZT funktioniert es!
Manchmal verblüffen mich die Teilnehmer von Excelschulungen.
Heue zum Beispiel.
Ich erkläre, wie man in Formeln Zellen fixiert. Erkläre, dass man Absolutbezug mit einem $-Zeichen oder der Funktionstaste [F4] erzeugen kann.
Ein Teilnehmer fragt, warum man in Excel nicht der Zelle selbst das Fixierungszeichen zuweisen kann. Als Eigenschaft der Zelle.
Warum eigentlich nicht?
Meine Antwort lautete: es könnte ja sein, dass eine Zelle von einem relativen Bezug und an anderer Stelle von einem absoluten Bezug verwendet wird.
Andererseits denke ich: das ist halt so. Das wurde halt so festgelegt …
Dennoch: clevere Frage.
Conny hat mich folgende Anomalie aufmerksam gemacht:
Markiert man in einer intelligenten Tabelle eine Zeile
und drückt [Strg] + [+], wird eine Zeile eingefügt:
Markiert man in einer intelligenten Tabelle zwei Zeilen
und drückt [Strg] + [+], werden zwei Zeilen eingefügt:
Markiert man jedoch DREI Zeilen
und drückt [Strg] + [+], dann werden zwei SPALTEN eingefügt.
Dies passiert jedoch nicht, wenn man über das Kontextmenü Zeilen einfügt:
Danke für den guten Tipp an Cornelia.
Hallo ,
Ich sehe Ihre Website excel-nervt.de sie ist beeindruckend. Ich frage mich, ob auf Ihrer Website Werbeoptionen wie Gastbeiträge oder Anzeigeninhalte verfügbar sind?
Was ist der Preis, wenn wir auf Ihrer Website werben möchten?
Hinweis: Artikel darf nicht als gesponsert oder Werbung gekennzeichnet sein.
Danke schön
####
Hallo Frau M.,
meine Seite excel-nervt bleibt werbefrei
Liebe Grüße
Rene Martin
Hallo!
Leider werden im Bereich Kundengeschäftsvolumen die Daten der Kostenstellen im aktuellen Monat nicht eingelesen.
Es hat sich jedoch an der Basis nichts geändert.
Könnten sie da vielleicht einen Blick drauf werfen?
Besten Dank vorab. Liebe Grüße
####
Hallo Frau G.,
doch – an der Basis hat sich etwas geändert:
die beiden Dateien heißen „Bilanzgeschäft Kostenstelllen *“
Da sind DREI „L“ im Namen Kostenstelllen
Peinlich, oder? Wer auch immer das gemacht hat …
Mit zwei „L“ klappt es …
Liebe Grüße
Rene Martin
Aber ich mache doch gar nichts!?!
Was ist passiert?
Ich öffne eine Excelarbeitsmappe. Öffne den Power Query-Editor. Und wähle eine Schritt aus.
Ich hatte eingestellt, dass beim Öffnen der Datei eine Abfrage aktualisiert wird. Und das wurde sie auch noch, während ich im Power Query-Editor arbeite …
Schöne Frage in der letzten Excelschulung. Eine Teilnehmerin wollte Folgendes wissen: Sie erhalten regelmäßig Listen, bei denen die Texte in Großbuchstaben stehen. Wie kann man sie in Groß/Kleinschreibung ändern?
Die Antwort: Die Funktion GROSS2 hilft hierbei.
Denn anders als Word gibt es in Excel weder eine Tastenkombination noch eine Formatieranweisung, um die Buchstaben von Versalien in Groß/Kleinschreibung zu ändern.
Ich habe zwei Mal hinschauen müssen. Wo verbirgt sich in Excel online in Excel in Microsoft 365 der Befehl Inhalte einfügen / Werte?
Schließlich habe ich ihn entdeckt: „Sonderzeichen einfügen“!?! Hätte man ihn nicht genauso nennen können wie in der Desktop-Version? Wer hat denn hier übersetzt?
Wir lernen in der Excelschulung die Taste [F4] oder [Strg] + [Y] kennen. Um den letzten Befehl zu wiederholen.
Eine Teilnehmerin probiert es aus: leider wiederholt Excel keine Formeleingabe.
Word hingegen wiederholt die letzte Eingabe
Wenn ich Power Query unterrichte, erzähle ich, dass es – anders als in Excel – keine Tastenkombinationen gibt, um Befehle aufzurufen.
Ich werde diesen Satz in den nächsten Schulungen modifizieren. In den letzten Tagen bin ich im Internet über mehrere Listen von Shortcuts in Power Query gestolpert. Allerdings: einige der Tastenkombinationen funktionieren nur in der englischen Oberfläche – in der deutschen gibt es andere. Und: einige funktionieren in DAX-Editoren oder in Power Query in Power BI, aber nicht in Power Query in Excel.
Ich habe hier einige der Tastenkombinationen für Power Query in Excel aufgelistet (und werde sicherlich weiter sammeln) und auf unserem Excelstammtisch im Juli vorstellen.
Viel Spaß damit, die Arbeit mit Power Query mit folgenden Kombis zu beschleunigen:
2.) Umgang mit Spalten im Editor
[Strg] + [A] alle Spalten markieren
Pos1/Ende wechsle zur ersten Spalte/letzten Spalte oder zum Anfang/Ende der Tabelle
Pfeiltaste wenn Spalte markiert ist: weitere Spalten auswählen
[Umschalt] + [Pfeiltaste] mehrere nebeneinander liegende Spalten auswählen
[Strg] + [Pfeiltaste] / [Strg] + [Leertaste]
mehrere nicht zusammenhängende Spalten markieren
[Alt] + [Pfeil unten] Filter
[Menütaste] Kontentmenü der Spalte
erste Spalte / [Pfeil links] / [Enter] öffnet Kontextmenü der Tabelle
[Leertaste] verschiebt den Bildschirm, ohne die Cursorposition zu verändern
[Strg] + [Leertaste] wechselt zwischen Zelle markieren und Spalte markieren
Ich bin erstaunt und verblüfft.
In Excel in Microosoft 365 ist es nun möglich ein Kalender-Steuerelement einzufügen. Man findet es in den Add-Ins in der Registerkarte Start:
Ich bitte einen Bekannten, der Excel 2016 hat, dies in seinem Excel zu öffnen. Ich bin erstaunt: es funktioniert! Keine Fehlermeldung!
Letzte Woche auf dem Excelstammtisch habe ich die Ecken und Kanten der Blitzvorschau vorgestellt. Sind Texte zu „chaotisch“ eingetragen, kann die Blitzvorschau nicht die Logik erfassen. Jedoch: mir sind noch weitere „Knackpunkte“ aufgefallen:
* die Blitzvorschau kann nur Texte mit einer Länge unter 255 Zeichen verarbeiten.
* Verwendet man sie in einer intelligenten Tabelle, wird sie beim Erweitern der Tabelle leider nicht automatisch erweitert.
* Früher hat ein mehrmaliges Verwenden und Rückgängig zu einer Fehlermeldung geführt. Dies scheint bereinigt worden zu sein.
* Drückt man jedoch [Strg] + [E] und anschließend [Strg] + [Z], kann man unter den Eintrag keinen zweiten Text schreiben, der die Blitzvorschau aktivieren würde. [Strg] + [E] funktioniert jedenfalls noch.
* Und schließlich etwas Amüsantes zum Schmunzeln:
Am vergangenen Montag habe ich auf dem Excelstammtisch die Blitzvorschau vorgestellt. Oder genauer: ich habe versucht den Algorithmus zu beschreiben, wie die Blitzvorschau die Daten analysiert und nach welchem Muster die Daten geholt werden.
Ein Problem bei „großen“ Tabellen (also vielen Spalten und mehreren Informationen in Zellen) ist: soll eine Information aus einer Zelle links neben der aktuellen herausgeholt werden, greift die Blitzvorschau auf die linkestmögliche Zelle zu und holt DORT den Wert heraus. Das Gleiche passiert auch, wenn in einer Zelle mehrmals der gesuchte Wert steht: Stets wird der linkeste Wert geholt.
Wir haben eine Lösung gefunden: Man muss zwei Werte untereinander eintragen und dann mit [Strg] + [E] die Blitzvorschau aktivieren. Sind diese beiden Werte eindeutig (es müssen nicht die ersten beiden sein), dann funktioniert die Blitzvorschau.
Auch wenn es aufgehübscht ist, es nervt dennoch kolosal: Verknüpfungen in Excel auf andere Arbeitsmappen:
Immerhin: Man kann sie jetzt auch in einem Aufgabenbereich einsehen (und: wenn es funktioniert: dort löschen!)
Oder klassisch: Über Datei in den Informationen:
Schleswig-Holstein will als nach eigenen Angaben als erstes Bundesland bis 2025 komplett auf Büro-Software des US-Giganten Microsoft verzichten und durch freie Software ersetzen. Einen entsprechenden Bericht des SHZ-Verlages vom Mittwoch bestätigte das Digitalisierungsministerium in Kiel. Minister Jan Philipp Albrecht twitterte, damit gehe Schleswig-Holstein voran und verabschiede sich weiter von geschlossenen Sourcecodes. „Damit sorgen wir für digitale Souveränität im Land (…), können die Daten unserer Bürgerinnen noch besser schützen & gewinnen Handlungsspielräume bei der Digitalisierung.“ Laut Ministeriumssprecher werden die auslaufenden Microsoft-Lizenzen etwa für das Programm Word vom Land ab 2021 schrittweise auf „Libre Office“ umgestellt.
Dadurch sollen jedes Jahr Millionenbeträge eingespart werden. Dass 25 000 Landesbedienstete auf ihren PCs Word, Excel und Powerpoint nutzen, koste jährlich 2,5 Millionen Euro. Bereits jetzt habe die Landesverwaltung ihren Rahmenvertrag mit Microsoft reduziert und spare damit in den nächsten fünf Jahren 6,8 Millionen Euro. Nach der Umstellung auf „Libre Office“ würden weitere 1,5 Millionen Euro pro Jahr eingespart.
Minister Albrecht wird zu dem Thema Open-Source-Software am Freitag in Kiel im Landtag einen Regierungsbericht vorstellen. Den Bericht hatten die Jamaika-Koalitionspartner von CDU, Grünen und FDP im Juni 2018 angefordert. Albrecht sagte dem SHZ-Verlag, zentral sei die Wiedergewinnung „digitaler Souveränität“.
„Libre Office“ enthält Programme für Präsentation, Textverarbeitung, und Tabellenkalkulation. Da für die Bedürfnisse vieler Behörden Optimierungen nötig seien, geht es laut Ministerium auch mit der freien Software nicht völlig kostenlos. „Wir haben die Chance, bei jedem Speichern eines Dokumentes Energie zu sparen. Das ist ein sehr wichtiger Nebeneffekt, sagte Albrecht. Der SHZ Verlag berichtete, laut einem Test des Bundesumweltamts soll ein Microsoft-Programm dafür 3,5-mal mehr Strom verbrauchen als Open-Source-Produkte.
„Ich hoffe, dass wir mit unserer ambitionierten Strategie anderen zeigen können, dass eine Verwaltung langfristig ohne die Abhängigkeit von digitalen Großunternehmen auskommen kann“, sagte Albrecht. Ein Schritt zur Vorbereitung ist laut Ministerium bereits getan: Die Schnittstelle zwischen „Libre Office“ und der elektronischen Akte, die das Land absehbar einführen wird, steht.
Als Beispiele für IT-Systeme, die bereits mit einer Open-Source-Struktur laufen, wird nach Angaben der Landtagsverwaltung in dem Regierungsbericht das Landesportal der Regierung sowie das Intranet der Landespolizei genannt. Auch für den vom Land angebotenen Basisdienst KSH (Kommunales Recht Schleswig-Holstein), mit dem Kommunen ihr geltendes Ortsrecht im Internet zur Verfügung stellen können, sei ein unter freier Lizenz stehendes, quelloffenes System verfügbar. Bei dem im Aufbau befindlichen Schulportal Schleswig-Holstein habe sich das Bildungsministerium ebenfalls für ein System auf Open-Source-Basis entschieden.
Die Umstellung der IT-Systeme in der Landesverwaltung, so heißt es im Bericht, könne Schleswig-Holstein nicht im Alleingang leisten. Es erscheine auch nicht sinnvoll, „die Softwareentwicklung für die öffentliche Verwaltung in Schleswig-Holstein vom Vorgehen der anderen Bundesländer und des Bundes zu entkoppeln“. Neben der bundesweiten Koordination werde es entscheidend sein, ein Netzwerk zu etablieren, „um vergleichbare Vorhaben zu analysieren, von deren Erfahrungen zu lernen und eigene Konzepte erfolgreich fortzuentwickeln“.
https://www.sueddeutsche.de/politik/regierung-kiel-albrecht-land-will-bis-2025-auf-microsoft-verzichten-dpa.urn-newsml-dpa-com-20090101-200617-99-457136
Hi, in meiner Postfacheinstellung wird die Größe meines Postfaches nicht angezeigt. Woran liegt das?
Meines Wissens werden bei IMAP/SMTP-Konten die Postfachgröße nicht angezeigt; bei einem Konto, das mit einem Exchange-Server verbunden ist, jedoch schon.
Aufpassen! Immer aufpassen.
Ich erhalte eine Liste. In der Länderspalte befinden sich die drei Abkürzungen AT, DE und CH:
Ich hätte gerne die drei Ländernamen Deutschland, Österreich und Schweiz in der Liste. Nun: bei drei Ländern kann man schnell ersetzen. Ich beginne: ersetze AT durch Österreich:
Dann: ersetze DE durch Deutschland:
Und schließlich: CH durch Schweiz:
Ups – nicht aufgepasst – „ch“ steckt ja auch in Deutschland und Österreich …
Also: sofort zurück und noch einmal ersetzen: Beachte die Groß/Kleinschreibung und vergleiche den gesamten Feldinhalt. Okay – eine der beiden Optionen hätte genügt:
Oder – wenn ich aufgepasst hätte – hätte ich auch zuerst CH durch die Schweiz ersetzen lassen können und anschließend die anderen beiden Länder.
Zu früh gefreut!
In einem größeren Excel-Projekt sollen Daten zusammengefasst werden. Das kann man wunderbar mit einer Pivottabelle erledigen. Und über der Tabelle sollen sich Schaltflächen befinden, über welche man die Tabelle filter kann.
Auch kein Problem, denke ich – da kann man doch einen Datenschnitt verwenden:
Zu früh gefreut: mit einem Klick auf eine Schaltfläche soll sich die Farbe der Pivottabelle ändern. Okay – DAS könnte man noch mit einer bedingten Formatierung abfangen.
Allerdings soll auch jeder Button eine andere Farbe haben. DAS geht leider nicht!
Also überlege ich, Schaltflächen zu verwenden. Und einige wenige Zeilen VBA-Code:
Allerdings: der Kunde möchte die Beschriftung zweizeilig haben – in der unteren Zeile soll eine zweite Information stehen.
Also doch Formen. An diese kann man auch VBA-Code binden. Und somit die Pivottabelle filtern:
Man kann auch hier filtern:
Übrigens: die Farben der Pivottabelle habe ich über die Pivottable-Formate gesteuert: einem benutzerdefinierten Format werden die entsprechenden Farben zugewiesen.
Ein interessanter Kommentar von Josef Feißt zum Thema Forms und Dezimalzahlen. Sehr interessant – vielen Dank für den Hinweis!
Mit Microsoft Forms auf dem Weg zur Weltherrschaft?
Manchmal frage ich mich, wie bei Microsoft eine Weltkarte aussieht … gibts da noch was anderes als die USA? 😉
Ich wollte per Forms Formular ein paar Informationen einsammeln. Dafür ist das Ding ja da, dachte ich.
Dann freue ich mich auch noch, dass sich ein Eingabefeld auf das Format „Nummer“ beschränken lässt. Dass das auf Deutsch besser „Zahl“ heißen sollte, würde ich ja noch großzügig übersehen. Übersetzungen sind nicht unbedingt die Stärke von Microsoft. Aus dem englischen „Number“ wurde „Nummer“. Nicht schön, aber verkraftbar.
Nach einigen fröhlichen Eingaben wundere ich mich doch über die Ergebnisse. Erwartet hätte ich überwiegend Zahlen von 0 bis 10, vielleicht ein paar im Bereich bis 20, aber die Zahlen sind irgendwie viel größer.
5, 10, 15, 45, 65, 75, sogar 125, …
Was war passiert ⁉
Beim Eingeben ist das Formular doch mit dem Komma einverstanden, ich konnte es abschicken!
Microsoft Forms hat ganz frech die Kommas über Bord geworfen! Einfach weg!
So wurde dann aus einer 0,5 eine 5, aus 1,5 eine 15, aus 6,5 eine 65, … ihr wisst schon …
Jetzt wollte ich das genau wissen und hab ein Forms Formular mit 3 einfachen Fragen gebaut:
1️⃣ Textfeld ohne Limit
2️⃣ Textfeld mit Einschränkung auf Nummer
3️⃣ Textfeld mit Einschränkung auf >= 10
Es klappt nur, wenn man beim Eingeben einen Punkt als Dezimaltrennzeichen verwendet.
Hätte ich mir ja denken können! Macht man in Amerika ja so!
Es ist wohl noch niemand auf die Idee gekommen, dass es in anderen Ländern ein Komma als Dezimaltrennzeichen gibt.
Passt auf, wenn ihr Dezimalzahlen per Forms einsammeln wollt!
Schöne Frage in der Excelschulung:
„Ich möchte gerne an eine Zelle zwei Links binden. Wie mache ich das?“
Das geht leider nicht …
Das ist schon irgendwie doof.
Bevor man in PowerBI ein Flächenkartogramm aktivieren kann:
muss man in den Optionen in der Kategorie „Vorschaufeatures“ die Option „Visuelles Formzuordnungselement“ aktivieren:
Auch das Visual Azure-Landkarte muss aktiviert werden:
Allerdings nicht in den Optionen, sondern im Admincenter von Microsoft 365:
Dort in den Mandanteneinstellungen / in den Integrationseinstellungen:
Sonst erhält man die Fehlermeldung: „Ihr Mandantenadministrator hat die Verwendung von Azure Maps leider nicht aktiviert.“
Letzte Woche fand in London das dreitägige Excel summit statt. Viele spannende und lehrreiche Vorträge!
Bob Umlas hat auf folgendes amüsante Phänomen aufmerksam gemacht:
Mit der Kamera kann man einen Screenshot eines Bereiches erstellen, welcher dynamisch geändert wird:
Schiebt man nun den fotografierten Bereich über den Quellbereich, wird der Originalbereich mehrmals gezeigt (Stichwort: Spiegel im Spiegel im Spiegel …)
Ändert man nun allerdings einen Wert, wird er nur ein MAL geändert angezeigt:
Ändert man ihn erneut, wird er erneut einmal weiter gegeben, der alte Wert wandert ebenso eine Ebene tiefer.
Amüsant und erstaunlich!
Diese Woche werde ich nicht nörgeln. Ich verspreche es.
Excel ist schon klasse.
Und: in Excel kommen regelmäßig neue, brauchbare Funktionen hinzu. Wer Microsoft 365 hat, wird sie schon entdeckt haben. Hier Technik V:
In der online-Version von Excel gibt es einen date-Picker.
Toll! Ich hoffe, dass er auch bald in die Desktop-Version einziehen wird.
Diese Woche werde ich nicht nörgeln. Ich verspreche es.
Excel ist schon klasse.
Und: in Excel kommen regelmäßig neue, brauchbare Funktionen hinzu. Wer Microsoft 365 hat, wird sie schon entdeckt haben. Hier Technik III:
Im Kontextmenü befindet sich ein Textfeld für Suchbegriff. Damit gelangt man schnell zu Befehlen, die tief verborgen sind oder von denen man nicht weiß, in welchem Dialog / hinter welchem Symbol sie sich verbergen.
Man kann sie nun direkt aus dem Kontextmenü heraus verwenden:
Diese Woche werde ich nicht nörgeln. Ich verspreche es.
Excel ist schon klasse.
Und: in Excel kommen regelmäßig neue, brauchbare Funktionen hinzu. Wer Microsoft 365 hat, wird sie schon entdeckt haben. Hier Technik I:
Man kann jetzt mit der Tastenkombination [Umschalt] + [Strg] + [V] kopierten Text als Wert einfügen und somit Formeln in Werte verwandeln.
Eine schöne Frage: in einer Datei befindet sich eine Tabelle.
In einer anderen Datei werden Teile dieser Tabelle verknüpft. Dahinter werden zu den entsprechenden Spalten weitere Informationen eingetragen. (Hier: Spalte A:C sind durch Power Query verknüpft; D wurde per Hand als zusätzliche Information eingetragen):
Nun wird in der Quelltabelle gearbeitet: Zeilen werden gelöscht; neue Daten eingetragen; die Liste wird sortiert. Egal ob man die zweite Datei per Formeln verknüpft hat (mit einem Bezug oder einer Formel) oder ob man Power Query verwendet hat – die Reihenfolge zu den per Hand eingegebenen Daten passt nicht mehr …
Die neue Quelltabelle:
Die Zieltabelle ist korrupt:
Ich weiß in Excel keine Lösung zu diesem Dilemma.
Das heißt: man muss alle Daten in EINER Tabelle halten!
Sehr geehrter Herr Martin,
[…]
Das andere Problem ist, dass ich die Tabelle davor schützen muss, dass meine Kollegen aus Versehen Eintragungen löschen können (wir arbeiten gemeinsam über eine NAS). Lediglich das Feld Doku soll geöffnet und genutzt werden können. Den Arbeitsmappenschutz habe ich auch hinbekommen, aber dann lässt sich die Doku nicht mehr öffnen.
Mit herzlichem Dank und freundlichen Grüßen
###
Hallo Frau H.,
Markieren Sie die Zellen, die alle bearbeiten dürfen. Über „Zellen formatieren“ (rechte Maustaste) und dort Registerkarte „Schutz“ heben Sie den Schutz („gesperrt“) auf.
Anschließend schützen Sie das ganze Blatt (Überprüfen / Schützen / Blatt schützen). Das Blatt ist geschützt außer den Zellen, die vorher „entsperrt“ wurden.
Sehr geehrter Herr Martin,
nachdem ich intensiv bei excel-nervt.de und in Ihren Youtube-Videos gesucht habe, aber leider keine Lösung gefunden habe, möchte ich anfragen, ob Sie mir helfen können.
Ich habe mich an eine Excel-Tabelle gewagt, komme jetzt aber nicht weiter. Ich habe Ihnen einen Ausschnitt der Tabelle angehängt. Gelungen ist mir das Einfügen von Bildern mit Mouse-Over-Effekt. Nun scheitere ich aber bei der Doku. Wir müssen aufschreiben, was wir mit den Klienten machen und ich habe ein Doku-Formular entworfen.
Auch das konnte ich in der Tabelle bereits einfügen, bin aber noch nicht zufrieden. Zum einen wäre es sehr hilfereich, wenn das Doku-Formular sich über =EINBETTEN(„Worksheet“;““) aus der Haupttabelle die entsprechenden Informationen wie Name, Vorname etc. „zieht“, so dass wir dies nicht immer per Hand eintragen müssen.
Hallo Frau Hantschick,
ein paar Tipps:
* ich würde das Einfügen folgendermaßen gestalten: Öffnen Sie beide Dateien. Klicken Sie in die Zelle, in welcher der Wert verknüpft ist und verweisen mit „=“ auf die andere Datei und dort auf die Zelle. Wandeln Sie den absoluten Bezug in einen relativen um (F4). Runter- und rüberziehen.
Also:
=[DOKU1.xlsx]Tabelle1!A1
Wenn Sie in einer Zelle eine Verknüpfung haben möchten, würde ich einen Link verwenden. Beispielsweise über das Kontextmenü.
Will ich das? Nein, das will ich nicht! Gestern kam eine neue Aufforderung beim Klicken auf Datei / Speichern unter / Durchsuchen:
Sie können mehr erreichen, indem Sie auf OneDrive speichern.
Zum Glück kann man den Assistenten ausschalten: das heißt: er soll mich nicht mehr fragen!
Guten Tag Herr Martin
Ich hoffe, sie hatten eine schöne Weihnachtszeit und ein guten Start ins neue Jahr.
Immer wieder werde ich darauf angesprochen ob, VBA in den neuen O365 Packeten enthalten wird.
Ich spüre ein Deaktivieren von VBA Beispiel im neuen Outlook. Welches nur mit einem Mausklick oben rechts am Bildschirm aktiviert werden kann.
(ich habe es probiert und alle VBA’s gingen dann nicht mehr. Nach dem Googlen wurde mir klar, dass hier die Funktion «stillschweigend» deaktiviert wurde. )
Im Excel gibt es nun Automatisieren.
Aber damit werde ich nicht ganz schlau. So wie ich das gesehen habe, dann ich nur ein Prozess aufnehmen und eingeschränkt eingreifen. Gestartet muss es mit einem Mausklick oben auf dem Ribbon.
Einige Funktionen fehlen mir, welche VBA anbieten.
Ich hoffe, dass Sie mir hierbei Auskunft geben können.
Freundliche Grüsse
Hallo Herr S.,
ich wünsche auch Ihnen ein gesundes, glückliches und aufregendes Jahr 2024.
Zu Ihrer VBA-Frage.
Inzwischen gibt es in Excel eine Reihe an Programmiersprachen:
Habe ich etwas vergessen?
Der Haken an VBA ist, dass es nicht im Internet läuft. Browser unterstützen kein VBA. Deshalb hat Microsoft JavaScript, beziehungsweise TypeScript ein. Ich habe es vor Kurzem wieder versucht: zeichnet man mit dem Makorekorder etwas auf, das die deutsche Sprache benötigt, also beispielsweise „Formatiere eine Zahl mit 0 „km“ oder formatiere ein Datum als TT-MM-JJJJ“ liefert das Abspielen einen Fehler.
Und ja: es gibt dort (noch) keine Dialoge: Interaktionen mit Anwendern sind mühsam. Auch das globale Verteilen auf mehreren Rechnern ist schwierig. Es gibt eine ganze Reihe an Hürden. Ehrlich: ich habe noch NIE eine Anfrage für TypeScript-Schulung oder -Programmierung erhalten.
Bald wird Python kommen – aber auch hier wird wohl nur der Umfang der Funktionen und Diagramme erweitert werden, wie ich das Ganze überblicke. Python bietet als offene Programmiersprache sehr viele Bibliotheken, die auch erweiterbar sind – MEHR als VBA, aber Python ist eigentlich nicht in erster Linie für MS Office-Produkte erstellt worden.
Schließlich kann man Excel „von außen“ programmieren: mit VS.NET und dort beispielsweise mit den Sprachen C# oder VB.NET. Da haben Sie mehr Möglichkeiten (beispielsweise bei Versionierung, Mehrsprachigkeit, weitere Steuerelemente, …) jedoch: das Debuggen ist sehr mühsam!
Hilft Ihnen das?
Liebe Grüße
Rene Martin
PS: ich habe JavaScript/TypeScript (also „Automatisieren“) übersprungen – ich werde mir aber Python ansehen, wenn ich es in Excel haben werde.
Das hat mir Hanna geschickt. Ich habe geschmunzelt.
Auf dass Excel in Neuen Jahr nicht mehr so sehr nervt.
Ein aufregendes neues Jahr 2024 wünscht
René
Hurra – mein neues Buch ist erschienen:
Schaltzentrale Excel
Wenn Sie viel mit Excel arbeiten, kennen Sie das Problem sicherlich: von einem anderen Menschen oder einem System erhält man Daten, die man beim besten Willen so nicht weiterverarbeiten kann. Um solche Daten zu „putzen“ gibt es nun mehrere Ansatzmöglichkeiten: Man kann sie mit einem der vielen Assistenten in Excel bereinigen, mit einer Funktion oder Formel oder mit Power Query. Natürlich stehen Ihnen auch Programmiersprachen zur Verfügung – aber darum soll es in diesem Buch nicht gehen.
Neben dem zentralen Thema „Daten in Form bringen“, besser: die Daten so aufzubereiten, dass man mit ihnen effektiv arbeiten oder dass ein anderes System sie weiterverarbeiten kann, stehen einige weitere, kleinere Themen im Zentrum:
Das und noch viele weitere Dinge habe ich zusammengefasst in meinem neuen Buch:
ISBN-10 : 3758319862
ISBN-13 : 978-3758319860
Preis: 15 Euro – auch als e-book erhältlich
„In der beigefügten Datei sind einige Weihnachtszellen nicht froh. Die Zeilennummern ergeben das Lösungswort.“
excel-nervt.de/FROHEWEIHNACHTEN.xlsx
Was habe ich gemacht?
Einige Buchstaben sind in UTF-8 nicht nur im lateinischen Alphabet vorhanden, sondern auch im Griechischen oder Kyrillischen:
Und wie findet man diese Texte? Es fällt auf, wenn man einen Autofilter setzt, dass zwei Mal ein „ähnlicher“ Text in jeder Spalte steht:
Folgende Lösungsansätze habe ich gefunden, beziehungsweise wurden mir zugeschickt:
='Frohe Weihnachten'!A1="FROHE WEIHNACHTEN"auf einem zweiten Tabellenblatt kann man die Zellen ermitteln:
3. Die Bedingte Formatierung liefert ein Werkzeug zum Auffinden:
4. Oder auch der Befehl „Zeilenunterschiede“, der im Assistenten Start / Suchen und Auswählen / Inhalte auswählen aktiviert werden kann. In Zeile 87 werden von Zelle B87:Q87 markiert – also unterscheidet sich A87
5. Oder man fragt ChatGPT oder Copilot:
https://chat.openai.com/share/bd7f8888-2560-40a9-8c6f-c0fafb2c89cd
Nun gilt es, die Zeilennummer herauszufinden. Beispielsweise mit XVERGLEICH:
=XVERGLEICH(FALSCH;A1:A123)
Wichtig: die Zeilennummern müssen von links nach rechts – nicht von oben nach unten gelesen werden. Von oben nach unten käme das Lösungswort „Weghikorstuvy“ heraus.
Übrigens habe ich auch als „Lösungswörter“ erhalten:
A87,H101,P103,B104,C105,E107,J107,O110,K111,I114,D115,L115,M116,N117,G118,F121
256117151431010911121384
Adventskranzlicht ???
HijackSoftwareSolution ???
Gänsebraten???
Diese Lösungen kann ich leider nicht gelten lassen.
Wie wandelt man die Zahlen in Buchstaben um? Offensichtlich handelt es sich hierbei um den ASCII-Code oder ANSI-Code von Zeichen. Die Funktion ZEICHEN, aber auch UNIZEICHEN wandeln sie um, TEXTKETTE baut den Text zusammen:
Whiskyverkostung
Christian Neuberger hat mir folgende, sehr clevere Lösung geschickt:
=TEXTKETTE(MTRANS(WENN(A1:Q225<>"FROHE WEIHNACHTEN";ZEICHEN(ZEILE(A1:Q225));"")))Er wandelt alle Zeilennummern der Zellen in die Buchstaben um, wenn in der Zelle nicht „Frohe Weihnachten“ steht oder löscht den Text („“). Das Ergebnis verkettet er und erhält das Lösungswort. Großartig!
Neun richtige Einsendungen habe ich erhalten. Der Gewinner wird am Montag Abend ausgelost.
####
Aus den vielen Mails, die ich erhalten habe, hat sich folgende interessante Diskussion mit Alexander entsponnen.
Ich dachte immer, dass SO Mailadressen oder Internetseiten gefälscht werden. Wenn ich also statt des Buchstaben „x“ das kyrillische „x“ oder das griechische „x“ verwende, erhalte ich so:
eχcel-nervt.de
oder
eхcel-nervt.de
Bei manchen Schriften sieht man den Unterschied deutlich, bei anderen gar nicht.
Versucht man nun diese Seite zu öffnen, wird der Text plötzlich anders codiert. Die Seite kann nicht geöffnet werden (was korrekt und gut ist!):
Daraus hat sich mit Alexander Vogelmann folgende interessante Diskussion entwickelt:
„Das bringt mich auf eine fiese idee um vba und excel menschen zum nachdenken zu bringen. Schau mal in der angehängten datei gibt es 2 sheets mit identischem namen“
Sub lateinNameMitDeutscherTastaturGetippt()
' funzt
Sheets("Weihnachten").Range("C3").Value = "latein"
End Sub
Sub kyrillischNameMitcAufRussischGetippt()
' funzt nicht
Sheets("WeihnaÑhten").Range("C3").Value = "kyrillisch"
End Sub
Sub kyrillischNameAusSheetKopiwert()
' funzt auch nicht
Sheets("Weihna?hten").Range("C3").Value = "kyrillisch"
End Sub„Und per makro kannst du nur das eine ansteuern, das in lateinsichem Zeichensatz benamt wurde, beim andern keine chance, auch nicht wenn man den sheet namen ins makro kopiert,
denn beim makroschreiben sit die Taste C eine andere als die Taste C beim sheet benamen oder werte in eine zelle schreiben verrückt…und witzig“
Danke für den guten und wertvollen Hinweis von Alexander Vogelmann und danke an alle, die mitgeknobelt haben.
Der Assistent „Inhalte auswählen“, den man über [F5] oder Start / Suchen und Auswählen erreicht, bietet die Option „Zeilenunterschiede“ zu markieren.
Das heißt: wählt man zwei oder mehrere Spalten aus, werde die Zellen markiert, die einen anderen Wert beinhalten als die Zellen links daneben.
Praktisch.
Jedoch: auch wenn im Internet anderes behauptet wird: es gibt keine Tastenkombination dafür.
Schöne Frage in der Excelschulung: ob man einen hängenden Einzug in Excel einschalten kann. Wie in Word.
Natürlich nicht!
Guten Abend René,
damit du auch wieder einmal etwas von mir liest,
so sende ich dir eine einfache Frage.
Wie erklärst du in deinen Schulungen „Datei“ (im Ribbon)?
HI Jürgen,
ich spreche von „Registerkarten“. „Datei“ erkläre ich nicht gesondert. Das „Blatt“, das sich über das Programmlegt heißt „Backstagebereich“.
Übrigens: die kleinen Ecken in den Gruppen unten rechts heißen „Schnellstartsymbol für integrierte Dialogfelder“ oder kurz „Schnellstartsymbol“ oder auch „Startprogramme“.
Liebe Grüße
Rene
Hallo Herr Martin,
eine Frage habe ich noch…
Die Artikelliste ist inzwischen ausgewertet und ich muss diese vollständige Auswertung in ein anderes Datenblatt kopieren und noch weiter bearbeiten, was er mir leider nicht macht.
Was muss ich tun, damit das klappt?
Bin morgen ab 7:30 telefonisch erreichbar
Danke schon mal
Hallo Frau P.,
Es gibt zwei Lösungen:
Entweder Sie markieren den Bereich und kopieren ihn und fügen den Inhalt als Werte auf ein anderes Tabellenblatt
Oder Sie verwenden Power Query.
Ich denke, die erste Variante können Sie selbst (hat den Nachteil, dass die Daten nicht aktualisiert werden.)
Die zweite Variante können wir gerne „zusammen“ machen – ich kann Ihnen gerne per teams zeigen.
Liebe Grüße
Rene Martin
Guten Morgen Herr Martin,
mit copy-paste hatte es eben nicht geklappt. Doch ich habe heute morgen einfach mich fürs Tippen entschieden, da ich einen Teil normal kopieren konnte, war es dann doch nicht mehr allzu viel.
Lieben Dank für die Unterstützung und einen schönen Start in den Freitag.
Guten Morgen, Frau P.,
ups – ich dachte, Sie kennen diese Technik.
Wenn Sie den Bereich mit den Formeln kopieren, können Sie mit der rechten Maustaste an eine andere Stelle klicken und dort die Option „Werte (123)“ anklicken:
Dann werden nicht die Formeln, sondern nur die Zahlen übernommen.
Alternativ verwenden Sie „Inhalte einfügen“. Dort können Sie auswählen – beispielsweise „Werte und Zahlenformate“. Oder Sie verwenden den Assistenten zwei Mal: einmal mit „Werte“, einmal mit „Formate“
Liebe Grüße und: ein schönes Wochenende
Rene Martin
Eine Teilnehmerin in der Excelschulung fragt, wie man Dropdownlisten der Datenüberprüfung konfigurieren kann, damit eine Mehrfachauswahl möglich ist.
Ich erkläre ihr, dass das nicht möglich sei – für Mehrfachauswahl benötige sie ein Steuerelement. Und/oder VBA-Programmiercode.
Als ich nachfrage, warum sie mehrere Elemente auswählen möchte und was mit den ausgewählten Daten passiere, kommt sie ins Schleudern. Also vielleicht doch zwei oder drei Spalten mit solchen Datenüberprüfungslisten.
Gestern in der Excelschulung. Warum die Blitzvorschau inaktiv ist, möchte eine Teilnehmerin wissen:
Ich werfe einen Blick auf das Menüband. Andere Symbole sind auch inaktiv:
Mein Blick geht noch weiter nach oben. In der Titelzeile finde ich die Lösung:
Die Teilnehmerin hatte aus Versehen zwei Tabellenblätter ausgewählt:
Warum? Wir hatten den Befehl [Umschalt] + [Strg] + [Pfeil] verwendet. Sie hatte fälschlicherweise auf [Umschalt] + [Strg] + [Bild unten] geklickt – so wird das zweite Blatt mit dem ersten selektiert.
Auch nicht schön.
Ich wollte die Datei nur schließen und nicht speichern. Ich erhalte folgenden Dialog:
Aber wie „verwerfe“ ich die Änderungen? Wie gelange ich zu der unteren Schaltfläche?
Zum Glück gibt es an der rechten Seite einen Rollbalken (Scrollleiste):
Amüsant: die Funktion TEILERGEBNIS liefert die Fehlermeldung #KALK:
Ich dachte, dass #KALK! nur bei den Arrayfunktionen als Fehlermeldung herauskommen kann.
Nein: wenn eine Arrayfunkion den Fehler #KALK! liefert und eine der „alten“ Funktionen damit weiter rechnet, kann dieser Fehler weiter gereicht werden:
Schade eigentlich. Vor einer Woche war in Excel online in Microsoft 365 noch ein Datepicker vorhanden:
Heute ist er weg:
Kommt der wieder? Kommt der dann auch in Excel Desktop? Warum macht Microsoft das? Dürfen die das? – einfach das Internet löschen ….
Fragen über Fragen.
Ich erhalte einige LibreOffice-Calc-Dateien mit der Bitte sie nach Excel zu konvertieren.
Kein Problem, denke ich und öffne die erste Datei in Excel: sechs Tabellenblätter, einige Formatierungen, einige Formeln, einige Dropdownlisten – nichts Aufregendes:
Ich speichere die Datei und bin erstaunt: die Dateigröße wächst von 70 KB auf 43 MB:
Die Ursache ist schnell gefunden: mit der Tastenkombination [Strg] + [Ende] springt Excel in die Zelle IW65538 – also das Ende von LibreOffice …
Heißt: Spalten markieren und löschen. Zeilen markieren und löschen. Und schon hat die neue Datei eine angenehme Dateigröße.
Ich bin begeistert: Formatiert man in Excel online eine Zelle als Datum und trägt dann eine Zahl ein, wird ein Datepicker angezeigt zur bequemen Datumswahl.
Schade nur, dass dieses Feature noch nicht in der Desktop-App vorhanden ist.
Ich wundere mich, warum ich in eine Datei plötzlich keine Eigenschaften mehr eintragen kann:
Auch nicht per VBA.
Mir dämmert es: Wenn ein Arbeitsmappenschutz gesetzt ist, wird dies verhindert!
Ich bin begeistert. Mal nichts zu nörgeln:
Microsoft hat in Excel für Inhalte einfügen als Werte eine Tastenkombination spendiert: [Umschalt] + [Strg] + [V].
Ich bin begeistert!
Ich habe mal nichts zu motzen. Okay: fast nichts. Ich bin sogar ein bisschen begeistert.
Microsoft hat in Excel die Datenüberprüfung geändert:
Trägt man einen Buchstaben ein, erhält man die Liste aller Werte, die mit diesem Buchstaben beginnen.
Duplikate werden entfernt.
Man kann mit der Pfeiltaste nach unten navigieren.
Klasse!
Auf dem letzten Excelstammtisch haben wir uns über die englischsprachigen Parameternamen amüsiert:
Beispielsweise in SPALTENWAHL. Mehr noch: der Parametername lautet row_num – gemeint ist allerdings die Spalte. Also: col_num …
Man gewöhnt sich so schnell an die Vorteile, die ein Programm bietet und vermisst sie dann in einem anderen Programm.
Wenn man in Word auf ein Wort doppelklickt, wird das Wort und das nachfolgende Leerzeichen markiert. Das heißt: Drückt man die [entf]-Taste, steht zwischen beiden Wörtern nur noch ein Leerzeichen:
Wird es gelöscht, bleiben zwei Leerzeichen hintereinander stehen.
Und sollte gelöscht werden.
Ups, was ist denn das? In Excel in Microsoft 365 finde ich folgendes Symbol:
Auch im Kontextmenü des Tabellenblattes ist es vorhanden:
„Schutz pausieren“. Klingt Schweizerdeutsch. Tönt im Excelkontext merkwürdig …
kann mir jemand behilflich sein? da ich den Date Picker nicht mehr aus meiner Excel, Datei entfernen kann.. .-)
###
BÖSE!
Hallo Micha,
Du kannst über Start / Suchen & Auswählen den Aufgabenbereich öffnen. Und dort den DTPicker auswählen. Und dann löschen.
Stellt sich die Frage wie der da reinkommt.
Die Antwort:
Wenn ich das Teilchen markiere (wie oben), liefert VBA:
MsgBox Selection.Nameden Text „DTPicker1“
MsgBox TypeName (Selection)liefert OLEObject
Dim i As Integer
Dim o As OLEObject
For i = 1 To ActiveSheet.OLEObjects.Count
Set o = ActiveSheet.OLEObjects(i)
MsgBox o.Name & ": " & o.Locked
Nextverrät mir, dass DTPicker1 Locked ist.
Das heißt: da hat jemand einen Schutz (per VBA?) auf den Datepicker gelegt …
Liebe Grüße
Wie kann man einen Anwender von Excel aussperren?
Man lege eine Excelmappe auf Sharepoint und verknüpfe diese in eine andere Mappe, die beispielsweise auf der Festplatte liegt.
Man verschicke diese „lokale“ Arbeitsmappe.
Nun wird der Anwender beim Öffnen einen Hinweis erhalten, dass er sich anmelden muss:
Da er aber keinen Zugriff auf Sharepoint hat, wird die Anmeldung verweigert. Abbrechen gibt es nicht! Die einzige Lösung: Excel im Task-Manager abschießen!
Amüsiert. Ich soll ein Spesenformular ausfüllen:
Und beachte dabei nicht, dass bei der Ausgabenbeschreibung eine Datenüberüberprüfung hinterlegt ist:
Ja, ja – Übersetzungen sich schwierig. Man muss ÜBERALL und ALLES übersetzen – sonst lacht der Anwender.
Schade aber auch:
Markiert man zwei Zellen mit Zahlen und zieht herunter – wird nicht weitergezählt:
Drückt man die [Strg]-taste und zieht herunter – wird nicht weitergezählt!
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:
Damit wird der DATENzugriff natürlich obsolet!
Lieber René,
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?
Liebe Grüße
Rene
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?
Auf der Seite
kann man es nachlesen:
Seltsam: ich darf nur Notizen einfügen – keine Kommentare:
Die Ursache ist schnell gefunden: Es handelt sich um eine XLS-Datei:
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?
Sie wollen ja nur helfen. Und auf Neuerungen hinweisen. Und nützliche Funktionen zeigen.
Aber mal ganz ehrlich: diese Tropfen nerven schon, oder?
Hallo Rene,
ChatGPT nervt…
Versuche gerade Folgendes,
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:
Sub PrintWithoutPageBreaks()
Dim ws As Worksheet
Dim lastRow As Long
Dim rng As Range
Dim i As Long
Dim pageBreaks As New Collection
Dim pageBreak As Range
‚ Set worksheet to active worksheet
Set ws = ActiveSheet
‚ Find last row of used range
lastRow = ws.UsedRange.Rows.Count + ws.UsedRange.row – 1
‚ Loop through each row in used range
For i = 1 To lastRow
‚ Check if row is hidden
If ws.Rows(i).Hidden = True Then
‚ If row is hidden, remove page break
If ws.Rows(i).pageBreak <> xlPageBreakNone Then
Set rng = ws.Rows(i).pageBreaks(1)
pageBreaks.Add rng
rng.Delete
End If
End If
Next i
‚ Print worksheet without page breaks
ws.PrintOut Preview:=False
‚ Restore page breaks to hidden rows
For Each pageBreak In pageBreaks
pageBreak.EntireRow.pageBreak = pageBreak.Address
Next pageBreak
End Sub
Gruß
Christian
####
Hallo Christian,
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
strUmbrueche = strUmbrueche & „|“ & ws.HPageBreaks(i).Location.Address
‚ lösche Umbruch
ws.HPageBreaks(i).Delete
End If
Next
‚ drucke
If MsgBox(„Soll jetzt gedruckt werden?“, vbInformation + vbYesNo) = vbYes Then
ws.PrintOut
Else
Exit Sub
End If
‚ falls Umbrüche gelöscht wurden
If strUmbrueche <> „“ Then
For i = 1 To UBound(Split(strUmbrueche, „|“))
strUmbruchsadresse = Split(strUmbrueche, „|“)(i)
‚ Umbruch einfügen
ws.HPageBreaks.Add Before:=ws.Range(strUmbruchsadresse)
Next
End If
End Sub
Liebe Grüße
Rene
Manchmal nervt IntelliSense gewaltig.
Beispielsweise in Access.
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!
Sehr geehrter Herr Martin,
wieder eine Frage:
Mit der Funktion LINKS kann man ja von links her einen bestimmten Text ausgeben.
Beispiel italienische Gerichte mit mehreren Namen, wobei nur der erste Name ausgegeben werden soll:
Der Versuch, mit der Funktion RECHTS, also das jeweils nur letzte Wort anzeigen zu lassen, hat nicht funktioniert. Geht das überhaupt?
####
Mit RECHTS können Sie nur sehr umständlich das letzte Wort ermitteln. Verwenden Sie: TEXTNACH mit dem Parameter -1:
=TEXTNACH(„Rene Bernd Martin“;“ „;-1)
Guten Tag Herr Martin
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
Irgendwo im Internet habe ich vor Kurzem gelesen:
„Vergesst ChatGPT – der Editor hat jetzt Tabs!“
Ich habe gelacht!
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!
Danke für diesen guten Tipp, Christian.
Kennst du diese Fehlermeldung?
Das Bild ist zu groß und wird abgeschnitten.
Ich wollte doch nur die Datei schließen. Ein kleines Bild liegt schon in der Datei. Wirklich klein! Ich schwör’s!
Ach wie doof. Wie genau muss man da hinschauen, beziehungsweise die Maus führen.
Eigentlich wollte ich die Dropdownliste der Datenüberprüfung anklicken, um die Liste zu öffnen:
Stattdessen habe ich den Mauszeiger auf den Rand der Zelle platziert und die Zelle verschoben:
So kam der hässliche Unterbau zum Vorschein.
Zum Glück gibt es die Rückgängig-Funktion.
Ist euch das schon einmal aufgefallen?
In der Registerkarte „Überprüfen“ gibt es in der Gruppe „Schutz“ die beiden Symbole „Blatt schützen“ und „Arbeitsmappe schützen“:
Aktiviert man den Blattschutz, ändert sich der Text:
Aktiviert man den Schutz der Arbeitsmappe, wird das Symbol grau unterlegt – der Text wird nicht geändert:
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.
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“:
Das ging doch früher, oder nicht? In Word funktioniert es. Noch!
Man konnte (und in Word kann man noch) mit der Tastenkombination [Strg] + [Rückschritt] das letzte Wort löschen:
In Word funktioniert es. Noch!
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.
Nun – dann kann man doch alle Objekte markieren:
Und die gefühlten 10.000 Rechtecke löschen.
Die Teilnehmerin hat sich gefreut.
Wo finde ich eine Liste der Links? Und wie kann ich alle Links eines Tabellenblatts löschen?
Das geht in Excel leider nicht – das muss man (mit VBA) programmieren.
Warum sie denn keinen Kommentar erstellen könne, wollte die Teilnehmerin in der Schulung wissen:
Klar: in der Zelle befindet sich bereits eine Notiz. Und diese war markiert.
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.
Was Menschen alles machen (wollen).
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:
Hallo Martin
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.
Seltsam.
Liebe Grüße
René
###
Warum findet Excel „Bielefeld“ nicht?
Ich sehe den Text doch in Zelle B19.
Der Grund: In B19 steht nicht „Bielefeld“, sondern eine Formel:
Stimmt – ich muss die Option „Suchen in“ umschalten von „Formeln“ auf „Werte“. Dann wird auch „Bielefeld“ gefunden:
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:
Oh, nö, Leute, echt jetzt.
Gestern sah die Registerkarte „Formeln“ in meinem Excel so aus:
Der Name Manager*in wurde zurückübersetzt. Allerdings in den Eigenschaften der Datei befindet sich noch ein*e Manager*in.
Dafür beglückt mich Outlook mit folgender Meldung:
Warum machen die das?
Ich entdecke die Seite
https://de.wikipedia.org/wiki/Liste_der_britischen_Premierminister
Ich greife mit Powerquery auf die Seite zu und lasse mir die Namen und die Datumsangaben (absteigend sortiert) zurückgeben:
Mit der Funktion TEXTTEILEN will ich die Datumsangaben trennen:
=TEXTTEILEN(Die_Liste_der_Premierminister__seit_1721__Bearbeiten___Quelltext_bearbeiten[@Amtsantritt];" ")und bin über das Ergebnis verblüfft:
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 …
Irre!
Wenn man mit PowerQuery auf eine „ältere“ XLS-Excelmappe zugreift, werden folgende Spalten angezeigt:
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 …
Eigentlich wollte ich heute zu einem anderen Thema schreiben. Aber gestern hat mir Mark folgendes Foto geschickt:
Ich habe gelacht und gedacht – „oh ja – wie oft ist das richtig!“
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?
Wollen die im Ernst? Ich dachte, das sei ein Scherz …
https://techcommunity.microsoft.com/t5/deutsch/translation-into-german-of-the-designation-name-manager-in-excel/idi-p/3603239?fbclid=IwAR0cFG6sXssV1si1pElUL3PWWOBBGJZEWa7bUQw7zqsoLL58mXquZjUOZko
Danke an Mourad Louha für den Hinweis und danke Mourad, dass du dich GEGEN das Gendern aussprichst.
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 …
Nervig!
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!
Geschmunzelt habe ich gestern schon.
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.
Hallo René,
Kennst Du Exelcat?
https://www.migros.ch/de/brand/exelcat
… fragte mich ein Bekannter.
Ich schaue nach und lache:
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.
Auch die IT-Abteilung einer Firma kann nerven!
Ich starte Excel und erhalte folgende Meldung:
Dieser Dateityp wird in der geschützten Ansicht nicht unterstützt.
Ich habe eine Weile gesucht, bis ich die Ursache gefunden habe. Irgendein Update von Excel kollidiert mit einem der installierten Add-Ins.
Hallo Herr Martin,
Nun muss ich eine weitere Spalten mit berechneten Werten aus anderen Spalten einfügen. Die neue Spalte soll den Barwertfaktor enthalten. Die Formel dazu:
Also =(((1 + [#“Verzinsung/LZ“] / 100) ^ [#“Restlaufzeit“] ) – 1) / (((1 + [#“Verzinsung/LZ“] / 100) ^ [#“Restlaufzeit“] ) *( (1 + [#“Verzinsung/LZ“] / 100) – 1 ))
Wie kann ich in Powerquery eine Potenz berechnen? Mit ^ geht es wohl nicht …
Oder gibt es bereits eine Funktion, die den Barwert berechnet?
Herzliche Grüße,
####
Hallo Frau I.,
Sie berechnen die Potenz mit Number.Power
Also für den Kapitalisierungsfaktor so:
Also = Table.AddColumn(#"Geänderter Typ", "Also", each (Number.Power([#"Verzinsung/LZ"], [Restlaufzeit]) - 1) / ( Number.Power([#"Verzinsung/LZ"], [Restlaufzeit]) * ([#"Verzinsung/LZ"] - 1)))Liebe Grüße Rene Martin
Eine schöne Frage in der letzten PowerQuery-Schulung.
Eine Tabelle soll verändert werden. Im linken Bereich befinden sich Informationen (nennen wir sie „Metadaten“), im rechten Bereich in mehreren Spalten weitere Informationen zu diesen Metadaten.
Jede dieser Gruppen, bestehend aus jeweils drei Spalten, soll neben die anderen Daten geschrieben werden, so dass die Metainformation so oft auftaucht, wie Gruppen vorhanden sind. Dabei können beliebig vieler dieser Gruppen auftauchen.
Das Ziel:
Mein erster Gedanke:
Ich fasse mit dem Befehl „Spalten zusammenführen“ jeweils die einzelnen Spalten einer Gruppe zusammen:
Anschließend kann man diese Spalten entpivotieren
und danach am Trennzeichen (hier: „|“) teilen.
Aber: das Verfahren ist umständlich, weil (hier:) bei 17 Gruppen 17 Mal entpivotiert werden muss. Da die Anzahl der Gruppen variabel ist, ging ich auf die Suche, ob man das mit geschickten M-Befehlen (einer Schleife!) abkürzen und dynamisch halten kann.
Da fiel mit der Artikel von Hildegard Hügemann in die Finger:
https://www.office-kompetenz.de/inhalte-aus-spalten-auf-zeilen-verteilen-mit-power-query/
Und genau DAS ist die Lösung:
Zuerst muss man den „rechten“ Teil entpivotieren:
Anschließend werden die Überschriften benötigt in der Form A – B – C. Leider stehen sie hier als A1 – B1 – C1 – A2 – B2 – C2 – A3 – … Die Zahlen müssen entfernt werden. Man kann sie mit dem Assistenten „Spalte teilen“ und er Option „Nach Wechsel von Nicht-Ziffer zu Ziffer“ herauslösen:
DIESE (spätere Überschriftsspalte) wird nun pivotiert, wobei die Werte (letzte Spalte) natürlich nicht aggregiert werden (verbirgt sich in den „Erweiterten Optionen“):
Der Rest ist „Kosmetik“: Datentypen festlegen, Spalten löschen, leere Spalten entfernen (wegfiltern), Spalten umbenennen, …
Klasse!
Ein großes Dankeschön an Hildegard Hügemann für die Lösung – hier habe ich glatt „in die falsche Richtung gedacht“.
Wer PowerQuery kennt, kennt sicher das Problem von IntelliSense:
Man beginnt etwas zu tippen, wundert sich über die angezeigten Vorschläge:
Man findet den korrekten Vorschlag, klickt darauf oder bestätigt mit der Tabulatortaste und: das Objekt wird zwei Mal genannt:
Ärgerlich!
Wyn Hopkins hat den Grund gefunden:
„The devil is in the dot.“
Man muss beide Befehl ohne Punkt schreiben, wobei Groß- und Kleinschreibung keine Rolle spielen! Dann klappt es. Ich bin begeistert:
Kennt ihr das? Eine Zelle bleibt schwarz. Hat keine Lust … Zum guten Glück verschwindet es sofort wieder …
In meinen Outlook-Schulungen sage ich den Teilnehmerinnen und Teilnehmern immer, dass sie sich Mail, die sie geschrieben haben, noch einmal durchlesen sollen.
Warum? Ich helfe gerne. Aber es ist mühsam, den Inhalt einer solchen Mail herauszufinden. Bin ich überhaupt gemeint? Wer ist Sabine? Worum geht es? Folge Mail erreichte mich vor einigen Tagen:
„Lieber Herr Brockelmann – ich hoffe es geht Ihnen gut?
Ich hätte eine Frage.
Ich habe erst Sabine interviewt.
Dann ergab sich dass Jennys Prozess zeitlich davor anfängt……und dasbekam ich dann nicht hin in der Datei.
Ich interviewe nur 1 Prozess als Muster – damit meine Chefin mal sieht was Prozessmanagement kann (allerdings hier nicht reine Lehre BPMN 2.0.).
Jetzt konnte ich den Vorlagen Prozess Sabine nicht nach rechts schieben (ich habe es nicht hinbekommen) und wissen Sie (sicher) wie man den Rahmen (das Feld nach ganz links wieder zieht) die Schablone? Das sich alles unendlich nach rechts erweitert ist gut.
Ich hoffe ich war verständlich….
Herzlichen Gruß aus xxx aus dem Home Office heute“
Zum Vatertag. Heute mal kein Excel. Sondern: Kochende Männer. Männer mit Partnerin. Flirtende Männer.
Gefunden in den Tiefen des Internets. Geschmunzelt.
Hallo Herr Martin,
Es geht um die Extrahierung von Datums- und Adressinformationen aus einem Bemerkungsfeld im Rahmen der Datenmigration.
Die Ausgangsinformationen stehen einer Spalte.
Diese Felder enthalten Stichtage und Adressen (Straße, Hausnummer, Zusatz).
Zum Stichtag:
Zur Adresse:
Falls zwei Adressen/Straßen vorhanden, keine extrahieren
Meine Antwort: Uff! So etwas können nur Menschen eintragen! Ich hoffe, dass bei der nächsten Dateneingabe die Daten besser werden.
(Randbemerkung: Beim Screenshot handelt es sich um rein fiktive Daten!)
Ich bin gerade völlig perplex. Ich programmiere für eine Schweizer Firma ein Excel-Tool. Ich lasse alle Tabellenblätter, die mit Monatsnamen beschriftet sind, ausblenden. Nur das Blatt MRZ bleibt stehen. In der Schweiz bleibt es stehen.
Ich frage Tanja Kuhn. Sie hat die Schweizer Oberfläche von Excel:
Und ja: sie bestätigt es: seit einigen Versionen lautet die Abkürzung des dritten Monats in der Schweiz nicht Mrz, wie in der ISO 8601 (EN 28601:1992) festgelegt, sondern Mär. Das erkennt man schnell, wenn man den Text „Jan“ einträgt unter herunterzieht:
Und eben deshalb liefert der VBA-Befehl
Format(DateSerial(2022, 3, 1), "MMM")in der Schweiz etwas anderes als in Deutschland (oder Österreich).
Perfide!
Superpraktisch – in bin begeistert. In PowerPoint gibt jetzt eine Funktion „sperren“. Darauf habe ich schon lange gewartet.
Allerdings: warum nicht auch in Excel? Vielleicht kommt es ja noch …
Excelstammtisch letzte Woche.
Frank stellt den Navigator vor, den Excel vor Kurzem in Microsoft 365 eingeführt hat:
Woah, denke ich: endlich nach 20 Jahren hat Microsoft von openOffice Calc und LibreOffice Calc den Navigator abgeschaut – den gibt es dort seit Ewigkeiten!
Danke an Frank Arendt-Theilen für den Hinweis.
In Excel kann man eine Reihe von Elementen einfügen: Bilder, 3D-Grafiken, Diagramme, Formen und auch SmartArts:
Öffnet man diese Datei in Excel online, wo SmartArts nicht unterstützt werden, wird dort ein Rechteck angezeigt:
Ebenso in teams:
Andreas Thehos schreibt:
„habe heute einen schönen Excel-Fehler gefunden.
SmartArts sorgen dafür, dass sämtliche Objekte eines Tabellenblatts entfernt werden.
Die Datei liegt auf einem SharePoint Online. Sobald jemand online per Browser oder Teams darauf zugreift und auch nur das Tabellenblatt wechselt, werden bei der Synchronisation alle Objekte im Blatt des SmartArts entfernt. Anschließend gibt es einen Fehler in Excel in drawingsX.xml
Kennst du das?“
Leider kann ich das nicht nachvollziehen …
Ich soll eine online-Excel-Schulung über teams halten. 1,5 Stunden (sic!) sind angesetzt. Bevor die Schulung stattfindet, bittet mich die Dame, die diese Schulung organisiert, ob ich – wie immer – einen Screenshot der Teilnehmerinnen und Teilnehmer zu machen. Erwartet werden zirka 150 (sic!) Nasen. Am besten – so schiebt sie nach – wären mehrere Screenshots – zu Beginn, in der Mitte und einer am Ende.
Ich überlege: Ein Screenshot aller Teilnehmerinnen und Teilnehmer – das bedeutet, dass ich mehrere Screenshots machen muss, da ich mit einer Aufnahme nicht alle Namen „einfangen“ kann. Aber wie soll ich, während ich rede und meinen Bildschirm teile, einen Screenshot machen? „Moment mal – ich muss Sie mal kurz abfotografieren?“ – Ein absurdes Vorgehen.
Da fällt mir ein, dass teams eine Option „Anwesenheitsbericht“ anbietet:
Ich probiere NACH der Schulung (?!?) aus und: tatsächlich: ich erhalte einen Bericht und eine Übersicht, wer sich wann angemeldet hat und wer wann gegangen ist. Klasse.
Ob das datenschutzrechtlich in Ordnung ist, sei dahin gestellt … Aber die Dame, die diese Schulung organisierte, war sehr zufrieden.
Ein bisschen kniffelig war es. Ein bisschen probieren musste ich schon.
Die Aufgabe: in einer Zeile soll ein „x“ an einer oder mehreren Positionen stehen. Die erste Position (von links) soll ermittelt werden und aus einer anderen Zeile (der Überschriftszeile), der entsprechende Wert hierzu angezeigt werden.
Den Wert zu finden, ist nicht schwierig:
=VERGLEICH("x";E165:W165;0)Den zugehörigen Wert zu ermitteln, auch nicht:
=INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0))Da die leeren Zellen einen Fehler erzeugen würden, kann dieser noch abgefangen werden:
=WENNFEHLER(INDEX($E$162:$W$162;VERGLEICH("x";E165:W165;0));"")Klappt!
Allerdings: In anderen Blöcken werden Zahlen eingetragen. Welche ist die erste Spalte, in der eine Zahl steht?
Die Funktion
=VERGLEICH(">0";E111:S111;0)versagt jedoch. Allerdings … nach einigen Versuchen:
=VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0)funktioniert! Und man kann die Überschrift suchen über:
=INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0))und schließlich:
=WENNFEHLER(INDEX($E$108:$W$108;VERGLEICH(WAHR;INDEX(ISTZAHL(E109:W109);0);0));"")Da diese Formulare dynamisch per VBA erzeugt werden, stellt es nun keine große Herausforderung mehr dar, diese in VBA-Code umzuwandeln:
.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(""x"",RC[-20]:RC[-2],0)),"""")"und analog:
.FormulaR1C1 = "=IFERROR(INDEX(R" & intAktuelleZeilenNummer & "C5:R" & intAktuelleZeilenNummer & "C23,MATCH(TRUE,INDEX(ISNUMBER(RC[-20]:RC[-2]),0),0)),"""")"Die Aufgabe hört sich simpel an – aber ich wüsste keine einfache Lösung.
Frage in einer Excelschulung: „Wie kann ich die Abteilung bequem auswählen?“ Noch bevor ich „Datenüberprüfung“ nachschieben konnte, kam: „ich habe manchmal ein und manchmal mehrere Kriterien.“
Stimmt: mit einer Dropdownliste (Datenüberprüfung) kann nur eine Auswahl getroffen werden. Wenn bereits ein Text in einer Zelle steht, beispielsweise in C6 „Controlling“ kann ich nicht mit einer Formel diesen Text verketten mit einem anderen Text. Das wäre ein Zirkelbezug. VBA und Programmierung schied aus.
Meine Lösung sieht folgendermaßen aus: alle Elemente (hier: Abteilungen) werden aufgelistet. In mehreren Zellen wird eine Einzelauswahl getroffen:
Diese Texte werden verkettet. Wichtig ist der Parameter Leere Zellen ignorieren: WAHR:
=TEXTVERKETTEN(ZEICHEN(10);WAHR;C2:H2)
Und diesen Text könnte man mit Kopieren / Inhalte einfügen in die gewünschte Zelle einfügen.
Die Teilnehmer waren nur mäßig zufrieden. Ich auch. Ich überlege noch nach einer besseren Lösung. Ohne VBA.
Es hat nichts mit Excel zu tun, aber es amüsiert mich: seit einigen Tagen erscheint rechts in der Taskleiste regelmäßig ein Häschen mit Deutschlandflagge:
Kaum versuche ich darauf zu klicken. hoppelt es fort und verschwindet. Und zeigt an, dass es Nacht ist oder regnet. Oder beides:
Ein Dankeschön an Martin Weiß. Er hat auf unserem Excelstammtisch sehr schön die Unterschiede zwischen Excel online (sprich: Excel für das Web) und Excel Desktop herausgearbeitet. Ich habe mir noch nie die Mühe gemacht, die Symbole nebeneinander zu stellen. Dabei ist mir aufgefallen, dass sich einige Beschriftungen unterscheiden:
Zellenformatvorlagen und Formatvorlagen:
Bilder und Grafiken, Link und Hyperlink:
Filter und Filtern, Sortieren und benutzerdefinierte Sortierung:
Tabellenansicht und Arbeitsmappenansicht:
Wer findet weitere Unterschiede?
Ich weiß – DAS sind lediglich Marginalien – spannender sind die Unterschiede der Versionen online und Desktop, die Gemeinsamkeiten und die Frage, was beim Datenaustausch passiert.
Wer Antworten auf diese Fragen sucht, wird fündig auf Martins Blog:
https://www.tabellenexperte.de/excel-voellig-kostenlos-wo-gibts-denn-so-was/
Eine hübsche Frage in der letzten PowerQuery-Schulung. Ich habe einen Moment überlegen müssen.
Die Aufgabe: wir exportieren aus unserer Datenbank regelmäßig eine Liste, die wir weiterverarbeiten müssen. Allerdings benötigen wir nicht alle Spalten. Dummerweise ändern sich die Spaltennamen regelmäßig … Wie kann ich nur die Spalten behalten, die mit „p_“ beginnen?
„Oder“, schob die Kollegin hinterher – alle Spalten, deren Überschrift eine Zahl (oder keine Zahl) enthalten …:
So schwer kann das doch nicht sein, oder? Alle Feldnamen, die mit „p_“ beginnen …:
Ich brauche die Überschrift. Man kann sie „extrahieren“, indem man alle Daten löscht:
Danach die Überschriften als erste Zeile verwendet und diese Zeile vertauscht (transponiert):
Nun kann man die Überschriften mit „p_“ filtern und in eine Liste konvertieren:
Ich nenne diesen Schritt „Selektierte_Ueberschrift“.
Man wäre auch mit einer Zeile M zu diesem Ergebnis gelangt:
Table.ColumnNames
Und wie verwendet man diese Liste als Filter?
Zunächst benötigt man die Liste. Mit fx kann ein Bezug auf die Tabelle hergestellt werden:
Löscht man nun Spalten, lautet der M-Befehl:
= Table.RemoveColumns(Benutzerdefiniert1,{"p_Gender", "p_Name", "p_StreetAddress"})Entfernt man andere Spalten, lautet er:
= Table.SelectColumns(Benutzerdefiniert1,{"p_Gender", "p_Name", "p_StreetAddress"})Und das kann durch den Namen der Liste ersetzt werden:
= Table.SelectColumns(Benutzerdefiniert1,Selektierte_Ueberschrift)
oder analog:
= Table.RemoveColumns(Benutzerdefiniert1,Selektierte_Ueberschrift)
Bleibt noch die Antwort auf die Frage: „und wie entferne ich alle Spalten, die Zahlen (Ziffern) enthalten?“
Ohne M könnte man einen Filter mit zehn Kriterien anlegen:
enthält nicht 1 und enthält nicht 2 und enthält nicht 3 … – ein bisschen Klickarbeit …
Aber durchaus machbar für jede und jeden – auch ohne Programmierkenntnisse. Und mit M? – Nun – die Antwort auf die Frage: „Wie baue ich eine Schleife und lösche alle Zeilen, die nicht 1 und nicht 2 und nicht 3, … enthalten“ überlasse ich der geneigten Leserin und dem geneigten Leser!
Auf alle Fälle waren die Teilnehmerinnen der Schulung zufrieden.
Ich weiß, dass es schwierig ist einen Text von einer Sprache in eine andere zu übersetzen. Auch noch, wenn die Zeit drängt. Dennoch: Hilfetexte sind auch ein Aushängeschild. Für Microsoft. Die ihre Texte automatisch – besser: halbautomatisch – übersetzen. Wohl, ohne dass ein (deutschsprachiger) Mensch darüber schaut. Und so habe ich auch schon einige Male gespottet. Auch Josef reibt sich verwundert die Augen und berichtet:
„Grüß dich Rene!
Gerade hatte ich ein sehr amüsantes Gespräch mit einem Kollegen 🙂
Er wollte eine Excel Formel haben und hat – ganz vorbildlich – die Excel Hilfe konsultiert. Leider war die wenig hilfreich, also rief er mich an.
Die Formel sollte ein Datum berechnen, ausgehend von einem Startdatum + X Monate. So weit so gut…
Kollege ganz stolz: „Guck ich mach genau das, was da steht: EDATE(…“
Ich: Stop! EDATE? Das muss EDATUM heißen. Hast Du etwa eine englische Internetseite gefunden?
Kollege: Neeee! Ich bin doch nicht doof! Ist die deutsche Microsoft Hilfe Seite!
Mit EDATUM(Startdatum;Dauer) hats wunderbar funktioniert.
Erst dachte ich: Naja… maschinell übersetzter Hilfe-Artikel… wird halt der Screenshot englisch sein, schade!
Aber nein! Das Ding ist eine fröhliche Mischung aus Englisch und Deutsch 😛
Wir haben herzlich gelacht! 🙂
Vermutlich hat da jemand versucht zu übersetzen, aber dann war plötzlich Zeit für Feierabend…
Im Text steht (mehrfach) EDATE
Die Beispiel-Formel im Text hat
o Deutsche Datumsschreibweise 15.05.19
o und ein Semikolon als Trenner zwischen den Parametern
o aber als Rückgabewert ein Datum in amerikanischer Schreibweise (4/15/19)
Im Screenshot
o Ist die Formel in der Bearbeitungszeile englisch, inklusive Komma als Trenner
o Die Spaltenbeschriftungen und Monatsnamen sind aber deutsch
Unterhalb des Screenshots wirds dann ganz verrückt:
o In Schritt 3 steht „Geben Sie =EDATE(A2;B2) in Zelle C2 ein,… “
Das wird weder in einem deutschen noch in einem amerikanischen Excel funktionieren.
Entweder EDATE und Komma oder EDATUM und Semikolon
In der Excel Hilfe zur EDATUM() Funktion ist es besser. Da steht nur einmal EDATE statt EDATUM in der Überschrift, aber sonst stimmts!
Viele Grüße und bis zum nächsten (online) Excel Stammtisch!
Josef“
Diese Mischung ist auch zu finden bei:
https://support.microsoft.com/de-de/office/edatum-funktion-3c920eb2-6e66-44e7-a1f5-753ae47ee4f5
Danke, Josef, für den Beitrag!
Fürchterlich!
In einer gespeicherten Datei befindet sich eine Liste von Daten. Der Bereich wurde „Quellnymphen“ genannt.
Auf einem zweiten Tabellenblatt befinden sich Dropdownlisten (Datenüberprüfungen), welche auf die Liste über den Namen zugreifen:
Ich möchte nun beide Tabellenblätter in eine neue, schon gespeicherte (!) Datei kopieren. Da auf beiden Blättern sich eine intelligente Tabelle befindet, kann ich nicht beide Blätter markieren und kopieren:
Also einzeln. Zuerst das Blatt mit den Datenüberprüfungen und anschließend das Tabellenblatt mit den Quelldaten. Die Datenüberprüfung funktioniert und greift auf die Liste zu, die hinter den Namen liegt:
Schließt man allerdings die Quelldatei, wird die Datenüberprüfung noch angezeigt – ja – sie greift sogar noch auf die Namensliste zu:
jedoch: sie lässt sich nicht mehr öffnen!
Okay – noch einmal:
Ich kopiere erneut das Datenblatt in die andere, bereits gespeicherte Datei, anschließend das Blatt mit der Datenüberprüfung:
Der Namensmanager gibt Auskunft. Dadurch, dass zwei Blätter mit Namen (eines besitzt einen Namen, eines verwendet einen Namen) kopiert werden, wird nun zwei Mal ein Name angelegt: ein lokaler, der auf die andere Datei zugreift (?!?) und ein globaler, der aber nicht von der Datenüberprüfung verwendet wird.
Uff!
Ähnlich perfide gestaltet sich das Ganze, wenn Quelldatei und Zieldatei im gleichen Ordner liegen. Kopiert man die Tabellenblätter hinüber, speichert beide Dateien, schließt sie und öffnet die Zieldatei, lässt sich – wie oben beschrieben – die Liste der Datenüberprüfungen nicht mehr öffnen. Schließt man die Zieldatei erneut und löscht die Quelldatei, ist eine Meldung nach der verknüpften Datei die Folge:
Das bedeutet: Das Kopieren von Blättern wird hinfällig, wenn Bezüge auf Namen vorhanden sind!
Seltsam. Manchmal – aber nur manchmal verschwindet das Kästchen zum Herunterziehen, wenn eine Zelle markiert ist:
Immerhin: es erscheint sofort wieder, wenn ich auf eine andere Zelle klicke.
Nachvollziehen kann ich auch nicht folgendes Phänomen: Trage ich in eine Zelle eine Formel ein, wird die Formel grau hinterlegt in der Zelle angezeigt:
Auch dieser Spuk verschwindet bald wieder. Seltsam …
Geht doch. Bei drei Zellen. Okay – man hätte es auch anders lösen können. Mit TEXTVERKETTEN beispielsweise.
Aber ich habe (mal wieder) gelernt, dass man sehr gut aufpassen muss: mit leeren Zellen, der Zahl 0 und keinem Text („“).
Da muss Microsoft wohl noch einmal ran. Ich versuche mich an der neuen Funktion
WURDEAUSGELASSEN
Ich erstelle die Funktion
=LAMBDA(Bereich;MAX(Bereich)-MIN(Bereich))
Und speichere sie im Namensmanager unter dem Namen „Spannweite“:
Ich teste sie – es funktioniert:
Ich ändere die Funktion
=LAMBDA(Bereich;WENN(WURDEAUSGELASSEN(Bereich);"Bitte wählen Sie einen Bereich aus!";MAX(Bereich)-MIN(Bereich)))
Die Funktion ohne Bereich:
Nicht das gewünschte Ergebnis!
Auch im Englischen funktioniert ISOMITTED nicht …
Ohne zu spicken – kennst du die Antwort?
In der letzten Excelschulung wurde ich gefragt, ob man Pivottabellen auf einem geschützten Tabellenblatt erzeugen, ändern und aktualisieren kann?
Ich gebe zu – ich war ein bisschen unsicher.
Und das sind die Antworten:
Eine Aktualisierung ist nicht möglich, wenn das Blatt geschützt ist. Auch nicht, wenn die Zellen nicht gesperrt sind und die Option „PivotTable und PivotChart verwenden“ aktiviert ist.
Wurde beim Blattschutz die Option “ PivotTable und PivotChart verwenden“ aktiviert wurde, kann man die Felder in die Zeilen, Spalten, Filter, … ziehen und von dort wieder entfernen – auch wenn die Zellen gesperrt sind.
Geneigte Leserin, verehrter Leser,
hat jemand von euch eine Idee?
####
Hallo René,
Jetzt habe ich eine Sache, die ich nicht wirklich gebacken bekomme. Ich habe bisher keine Möglichkeit gefunden, eine Frage zu stellen, die dann jemand vorbereitet beantworten kann. Nachdem die Lösung (wenn sie vorhanden ist) wahrscheinlich umfangreicher ist, frage ich mal einfach an, ob ja jemand weiter helfen kann.
Ich hänge an der „AutoFit“-Funktion für Zeilenhöhen. Das Problem ist eigentlich ein alter Bekannter: Man hat einen Text, der länger ist als die Zelle es ermöglicht, stellt die Zelle auf „Zeilenumbruch“, und weist Excel entweder händisch (Doppelklick auf Zeilenhöhe) oder per VBA (AutoFit) an, die passende Zeilenhöhe einzustellen. Jetzt ist Excel aber kein Layoutprogramm, und macht nur WYSI ungefähr WYG. Die Zeilenhöhen sind auf dem Bildschirm nicht immer wirklich passend, manchmal sind es zu große Zeilen (zu kleine eher selten).
Noch schlimmer wird es, wenn ich auf die Druckvorschau (bzw. später den Druck) gehe. Da werden dann die Zeilenumbrüche neu gesetzt (in der Regel passt in die Zeile im Druck mehr als auf dem Bildschirm), und die Zeilenhöhe passt dann noch weniger als vorher.
Zu allem Überfluss scheint das Ergebnis auch vom verwendeten Drucker abzuhängen. Natürlich könnte ich jetzt die Zeilenhöhen manuell nacharbeiten, aber bei längeren automatisch erstellten Dokumenten ist das echt mühsam und nicht gerade geeignet, das einem Kunden zu verkaufen. Vor allem, wenn da die Seitenumbrüche dran hängen.
Kennst du oder jemand dazu eine befriedigende Lösung, mit der sich sicher passende Zeilenhöhen erstellen lassen?
Schöne Grüße
Peter
Hallo Peter,
[…]
Zu „autofit“. Ich kenne das Problem, habe es aber nicht eingrenzen können.
Autofit ist eine Methode – sie macht einmalig und keine Eigenschaft, die man vielleicht mit ein paar Parametern überlisten könnte …
Mir ist auch aufgefallen: Manchmal (in letzter Zeit seltener) ist der Umbruch in der Seitenansicht nicht der gleiche wie in der Normalansicht.
Ich habe ab und zu in VBA-Programmierungen „kleine“ Lösungen gebaut („suche“ die Überschriftszeilen und sorge dafür, dass sie nicht am Ende der Seite stehen oder erhöhe die Zeilenhöhe vor dem Speichern als PDF um 1 pt …)
Ich habe gelacht. Für die nächste Schulung, bei der mehrere Dutzend Teilnehmerinnen und Teilnehmer geschult werden sollen, hat der IT-Leiter eine Namensliste angelegt: wer aus welcher Abteilung sich für welche Schulung eingetragen hat.
Und hier ist die Unterschriftliste, sagt er und schmunzelt: ich habe doch keine Lust die Namen per Hand einzutragen. Deshalb habe ein eine kleine Formel geschrieben. Alles andere würde doch nur nerven.
Recht hat er, denke ich und lache.
Verblüfft. Ich erstelle eine Pivottabelle und möchte die Jahreszahlen gruppieren:
Eine Fehlermeldung ist die Folge:
Kann den markierten Bereich nicht gruppieren.
Okay? – und warum?
Ein Blick in die Daten liefert die Lösung: die Jahreszahlen sich als Text formatiert?!!?!
In Zahlen umwandeln – dann klappt es …
Hallo ich brauch bitte mal Hilfe bei bedingter Formatierung!
Kann man wenn eine Zelle automatisch die Farbe rot erhält über die bedingte Formatierung dann da automatisch einen Buchstaben mit einfügen ?
Vielen Dank für eure Hilfe
=======
Du kannst eine bedingte Formatierung mit Hintergrundfarbe, Schriftfarbe und einem (benutzerdefinierten) Zahlenformat versehen. Beispielsweise „Rot“ – dann wird dieser Text angezeigt, wenn die Bedingung erfüllt ist.
Hallo Herr Martin, im Anhang sende ich Ihnen eine Exeltabelle mit einer „mauell erstellten Kopfzeile“ und einer Zeilenschaltung in der Zelle „Anschrift“. Für einen Serienbrief benötige ich die „Kopzeilen“ ebenso die Zeilenschaltung nicht. Wie entferne ich am schnellsten die „Kopfzeilen“ und die Zeilenschaltung in der Zelle. (Teilenschaltung in einer Zelle zu entfernen, habe ich in Ihren Videos schon gefunden). Ich möchte alle Daten in einer Spalte haben. Ich würde mich freuen, wenn Sie mir dabei helfen würden. Mit freundlichen Grüßen PV (Ein Fan Ihrer Office-Kurse)
Hallo Herr V.,
das habe ich gemacht :
* Mit Suchen und Ersetzen die Zeichenschaltung (Strg + J) durch einen Schrägstrich ersetzt.
* den Verbund aller verbundenen Zellen aufgehoben
* den Textumbruch entfernt
* mit einem AutoFilter in der Namensspalte den Text „Name“ und die leeren Zellen gefiltert und entfernt
* die leeren Spalten gelöscht.
Schade! In PowerPoint kann man „Formen zusammenführen“, also: vereinigen, kombinieren, in Einzelmengen zerlegen, Schnittmengen bilden oder subtrahieren:
So kann man leicht neue Formen erstellen:
In Excel leider nicht …
Das ist mir ja noch nie aufgefallen!
Auf der (Computer-)Tastatur gibt es zwei Tasten für [Enter]. Aber mit der rechten [Enter]-Taste kann man keinen Zeilenumbruch, keine Zeilenschaltung in Formen erzeugen:
Das ist irgendwie doof!
Nennen wir ihn B. B. kann für Björn stehen. Oder für Benno. Für Benjamin oder für Boris. Egal. Wir nennen ihn B.
B. ist Teilnehmer meiner Excelschulung und stellt eine Frage zum Aufbereiten von CSV-Dateien, die er in regelmäßigen Abständen erhält. Er denkt an eine VBA-Lösung – ich schlage PowerQuery vor. Die Datei wird aufgerufen, transformiert und nach Excel zurück geschrieben.
Allerdings: der Pfad, beziehungsweise der Dateiname soll variabel sein. Eigentlich kein Problem, denke ich, und lasse B. Pfad und Dateiname in die Excelmappe schreiben, mit einer Überschrift versehen und in eine (intelligente) Tabelle umwandeln.
Beide Tabellen werden nach PowerQuery gezogen, und dort mit einem Drilldown in einen Text verwandelt. Sie werden in dem Befehl
File.Contentsverwendet; die Sicherheitsstufe dieser Arbeitsmappe wurde ignoriert. Und dann das Erstaunliche:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
Stirnrunzeln.
Probieren. Beispielsweise Pfad und Dateiname in PowerQuery (oder in Excel) zu verketten und diese Zeichenkette zu verwenden. Beides schlägt fehl:
Immer wieder die gleiche Fehlermeldung:
DataFormat.Error: Der angegebene Dateipfad muss ein gültiger absoluter Pfad sein.
In Ruhe, alleine, und ohne B. schaue ich mir die Zeichenkette genau an und probiere. Erstaunt stelle ich fest, dass das erste Zeichen nicht der Laufwerksbuchstabe ist. In Excel kann man das mit der Funktion LINKS oder TEIL ermitteln. Der ASCII-Code lautet 63 – eigentlich ein Fragezeichen.
Ich überlege, probiere und frage B. Er hat eigentlich nur den Namen des Verzeichnisses aus den Dateieigenschaften kopiert. Und ich habe ihm zugesehen.
Ich weiß nicht, wie dieses merkwürdige Zeichen in die Excelzelle gelangt ist. Ich weiß, dass Excel bei einigen Zeichen (geschützte Leerzeichen, bedingte Trennstriche, …), die man über Word, Outlook oder eine Webseite nach Excel kopieren kann, Probleme hat. Aber hier? Keine Ahnung.
Lösung des Problems: Pfad neu tippen – und dann klappt es!?!
Excel – aber so was von retro …
https://www.youtube.com/watch?v=kOO31qFmi9A&t=5s
my spreadsheet does’t do that!
VBA-Schulung. Eine Teilnehmerin fragt mich, warum sie keinen Button mehr einfügen kann:
Meine erste Vermutung: Cursor sitzt in der Zelle. Nein!
Meine zweite Vermutung: ein Makro läuft noch: Nein!
Dann fiel es mir ein: „Drück mal [Strg] + [6]!“
Das war die Lösung!
Mit der Tastenkombination [Strg] + [6] wird die Anzeige von Bildern, Diagrammen, Formen, …. unterdrückt. Und also auch die Anzeige von Buttons.
Wie hat sie das gemacht? Wir haben vorher das Thema „Zahlen Formatieren“ und Aufzeichnen mit dem Makrorekorder behandelt. Ich habe die Tastenkombination [Umschalt] + [Strg] + [6] für das Zahlenformat „Standard“ gezeigt. Wahrscheinlich hat sie [Strg] + [6] gedrückt – damit werden Bilder ausgeblendet.
Man kann diese Einstellung auch über die Optionen deaktivieren:
Gestern habe ich ein Referat über „leere Zellen“ gehalten. Ich habe gezeigt, dass man eine leere Zelle durch einen Wert ersetzen kann – beispielsweise durch 0:
Klappt: der Suchen und Ersetzen-Dialog tut gute Dienste:
Sieben leere Zellen werden mit der Zahl 0 gefüllt.
Auch umgekehrt funktioniert es: Ersetze 0 durch „nichts“, also leere Zellen, in denen die Zahl 0 steht:
Ich stutze: warum werden jetzt zehn Änderungen vorgenommen?
Klar – ersetzt werden nicht die Zellen, in denen die Zahl 0 steht, sondern die Ziffer 0 wird gelöscht. Auch 70 wird 7. Kurz überlegt.
Klar: man muss die Option „Gesamten Zellinhalt vergleichen“ aktivieren:
Dann funktioniert auch das.
Kennen Sie die beiden Symbole „Dezimalstelle hinzufügen“ und „Dezimalstelle entfernen“? Verwechseln Sie diese beiden Symbole auch regelmäßig?
Greg Nash (https://www.dearwatson.net.au/) gibt einen Tipp:
To add or remove decimal places in #Microsoft #Excel first click on the WRONG button several times, then click on the correct button twice as much as you had to.
Unglaublich! Kaum macht man Excel zu und wieder auf, sieht es völlig anders aus. Ohne Vorwarnung! Padautz!
Die meisten Dinge habe ich schnell wieder gefunden. Die meisten. Für den Rückgägigbefehl habe ich drei Mal hinschauen müssen. Suchspiel: wer findet ihn?
Kennt ihr das? Eigentlich sollte es nicht so sein. Aber einer der Kunden bestellt unbedingt darauf. Hat auch einen guten Grund dafür.
Der Kunde ist König!
Nun – gut – soll er seinen Willen haben!
In einem sehr umfangreichen Projekt, das mit VBA realisiert wurde, soll eine Auswahl über eine Auswahlliste getroffen werden. Aber eben ein Kunde möchte Freitext haben. Eigentlich widerspricht dies dem Workflow.
Also füge ich unter der Liste ein Textfeld (!) ein und formatiere es so, dass es aussieht als wäre es ein Bezeichnungsfeld. Man muss einige der Eigenschaften ändern:
Das verraten wir natürlich nur einem Kunden. Damit DER Freitext eingeben kann. Weil er es will. Weil er es braucht. Eben: weil der Kunde König ist:
Excelschulung. Die Frage kenne ich: kann man eine Dropdownliste, die man per Datenüberprüfung erzeugt hat, sortieren:
Die Antwort lautet: „NEIN“. Dafür habe ich mal ein Werkzeug gebaut, das solche Listen sortiert und mit dessen Hilfe man bequem auswählen kann.
Sie finden es auf meiner Homepage:
https://www.compurem.de/?page_id=559
Sehr geehrter Dr. Martin,
Wir haben in unserem Unternehmen Probleme bei der Formatierung unserer Statistikauswertung.
Wir formatieren eine Spalte farblich größer als /kleiner als/ zwischen,
Jedoch ist es nicht möglich dieses Vorgehen auf die anderen spalten zu übertragen und ich or müssen somit jede Spalte seperat formatieren.
Ist es möglich das ganze auf alle spalten zu übernehmen?
Hallo Herr R.,
und so funktioniert es. Beginnen Sie bei einer Zelle, beispielsweise links oben. Liegt der Wert dieser Zelle zwischen der Unter- und Obergrenze, soll er grün werden. Die Formel lautet:
=UND(B6>=B$5;B6<=B$3)UND, weil beide Bedingungen erfüllt sein müssen.
B6 darf kein $-Zeichen haben – diese Zelle ist variabel, soll in der Position geändert werden.
B$5 und B$3 haben ein Dollarzeichen vor der Zeilennummer 3 und 5. Das bedeutet: beim Herunterziehen verändert sich die Zeile nicht – sie bleibt fix. Die Spalte B hat kein $-Zeichen – sie ist wieder variabel oder veränderlich.
Im nächsten Schritt wird der Bereich im Assistenten „Regeln verwalten“ auf den gesamten Bereich ausgedehnt:
Analog beim zweiten Schritt. Man könnte die Werte, die außerhalb liegen, mit zwei bedingten Formatierungen abarbeiten, oder mit einer. Ich entscheide mich für eine Bedingung. Die Formel lautet:
=ODER(B6<B$5;B6>B$3)
Auch hier gibt: B6 ist relativ, in B$3 und B$5 sind die Zeilen 3 und 5 fixiert; jedoch nicht die Spalte B. Und auch hier kann man im zweiten Schritt den Bereich erweitern. Das fertige Ergebnis:
Hallo Renè!
Habe mir wieder Arbeit im Verein aufgehalst.
Eine Tabelle in englischer Sprache enthält in Spalten auch Namen wie Michael, Manuel,
Hueber, Baeuerle, Michaeller ….
Die machen Schwierigkeiten.
Nach dem Filtern der Namen habe ich mir mit Michael = M1beholfen. Mehrarbeit hat es
gegeben, nachdem ich die Namen nicht gleich aufgeschrieben habe :-(((
Gibt es eine einfachere Methode die Namen in der Tabelle nicht zu ändern?
Die Vollständigkeit lässt sich bei der Funktion ‚wenn‘ schwer überprüfen.
Bitte um deine bewährte Hilfe.
Vielen Dank,
Peter
Hi Peter,
so ein Problem hatte ich mal – wie soll ein System erkennen, dass Manuel Bauer korrekt ist, Juergen Boese aber umgewandelt werden muss.
Das muss leider (!) händisch gemacht werden – wir haben damals die 5.000 Namen durchgesehen …
Liebe Grüße
Rene
Amüsant. Muss ich das verstehen?
Ich öffne mein Excel, das auf dem Desktop installiert und darin eine Datei. Ich kopiere einen Teil einer Tabelle nach Excel online, das auf dem SharePoint gespeichert ist.
Allerdings: ich darf nicht über das Kontextmenü einfügen, sondern muss die Tastenkombination(en) verwenden.
Muss ich das verstehen?
Hallo Rene
Ich schätze es ausserordentlich, dass Du mich unterstützt und fühle mich geschmeichelt.
Du hast natürlich vollkommen Recht mit dem Hinweis, dass in einer Spalte keine Zahlen und Texte stehen sollten.
Dieser Umstand ist dadurch entstanden, weil die Tabelle zusätzlich für einen anderen Zweck benutzt wurde. Hierbei wurden die Zeilen insofern erweitert, indem für jeden Kunden eine zusätzliche Zeile eingetragen wurde.
Hatte ein Kunde einen Service, wurde in der entsprechenden Spalte eine 1 reingeschrieben und im Anschluss mit einem Flow in einen ScharePoint Liste übertragen. Anders hätte ich ja die Kunden Records nicht handeln können.
Nun habe ich den Servicekatalog und die Zuweisung der Kunden zum jeweiligen Service getrennt. Somit konnte ich die Tabelle vom Servicekatalog wieder «drehen», womit die Services in den Zeilen stehen und die Spaltenwerte zu den jeweiligen Daten passen.
Eine andere Lösung gibt es nicht, wenn ich Deine Erklärungen richtig verstanden habe und eigentlich scheint es mir auch logisch.
Nun muss ich einige weitere Anpassungen an dem ganzen Konstrukt vornehmen und einen neuen Flow erstellen. Dabei hoffe ich natürlich, dass ich mir keine neue Baustelle geschaffen habe.
Nochmals herzlichen Dank für Deine wertvolle Hilfe und die guten Tipps
Herby
#####
Hallo Herby,
in meinen vielen Jahren Exceltraining und meinen vielen Artikeln auf excel-nervt habe ich gelernt, dass
* Excel ein sehr gutes Programm ist
* Excel manchmal etwas eigenwillig ist
* Anwender und Anwenderinnen oft Wünsche haben, die sich SO nicht direkt umsetzen lassen (ich verstehe oft die Hintergründe)
* dass man sich auf das Denken von Excel einlassen muss (ist halt ein Mann*) und man manchmal seine Daten etwas anders organisieren muss, damit man zum Ziel kommt
Liebe Grüße
Rene
*) Die Frage, ob Excel männlich oder weiblich ist, stelle ich häufig in Schulungen. Und amüsiere mich dann über die Antworten à la: „Excel ist männlich, weil …“ oder „Excel muss eine Frau sein, der nur so kann man sich erklären …“
Kennen Sie das? In einer Excelliste wurde ein Autofilter aktiviert. Ein Kriterium wird gefiltert. Nun wird auf die gefilterte Liste ein Rechteck gelegt, beispielsweise zur Kommentierung:
Wird nun der Filter entfernt wird die Ferm seeeeehhhhhhhr, seeeeehhhhhhhr lang:
Schöne Frage: Wie kann man einen transparenten Hintergrund für Datenschnitte einstellen?
Die Antwort: Ich wüsste nicht wie. Sorry!
Wir haben einen Ordner. Nennen wir ihn „Bilanz“. In diesem Ordner liegen zwei Dateien: August.xlsx und September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
='C:\Users\Rene Martin\Documents\Bilanz\[August.xlsx]Tabelle1'!$D$10Beide Dateien werden geschlossen, der Ordner wird umbenannt, beispiesweise in „Bilanz2021“. Das Öffnen und Aktualisieren der Datei funktioniert problemlos.
Wird haben einen Ordner. Nennen wir ihn „Bilanz“. Darin befinden zwei weitere Ordner: „August“ und „September“. Im Verzeichnis „August“ befindet sich eine Datei August.xlsx, im September-Verzeichnis eine Datei mit Namen September.xlsx. In der Datei „September“ gibt es eine Verknüpfung zu August-Mappe:
='C:\Users\Rene Martin\Documents\Bilanz\August\[August.xlsx]Tabelle1'!$D$10Beide Dateien werden geschlossen, der Ordner „August“ wird umbenannt, beispiesweise in „August2021“. Das Öffnen und Aktualisieren der Datei funktioniert JETZt nicht mehr:
Ärgerlich!
Excel nervt nicht immer. Man kann auch schöne Sachen damit machen. Beispielsweise Folgendes, das ich in den Tiefen des Internet gefunden habe:
Hätten Sie es gewusst? Gestern kam eine Frage in der Excelschulung … ich war mir nicht sicher. Also: schnell ausprobieren!
Kann man in einem geschützten Tabellenblatt Kommentare und Notizen eintragen, lautete die interessante Frage?
Ich probiere: Zellen nicht sperren (über den Dialog „Zellen formatieren“ und anschließend Blatt schützen:
Das Ergebnis:
Man kann Kommentare eintragen – keine Notizen!
Noch ein Versuch: ich schütze das Blatt und aktiviere die Option „Objekte bearbeiten“:
DANN kann man auch Notizen einfügen:
Haben Sie es gewusst? Ich nicht …
Erstaunlich. Das ist mir noch nie aufgefallen. War das schon immer?
Erstellt man in Excel eine Datenüberprüfung mit einer Liste und trägt die einzelnen Werte in das Textfeld als Quelle ein
kann man die Texte gefolgt von einem Leerzeichen eingeben:
Verwendet man dagegen als Liste die Werte einiger Zellen, die ausgewählt werden:
kann man die Texte NICHT gefolgt von einem Leerzeichen eintragen. SO sollte es doch eigentlich sein, oder?
So schwierig kann das wohl nicht sein, dachte ich. Und probierte es. Allerdings: die Lösung des Problems war doch komplizierter als gedacht.
Vor einigen Jahren hatte ich die Aufgabe in einer sehr großen Excelliste (zirka 60.000 Zeilen) die Daten „zu putzen“. Mitarbeiterinnen und Mitarbeiter hatten an unterschiedlichen Stellen in einer Spalte Informationen eingetragen – allerdings mehrere Informationen getrennt durch Trennzeichen. Durch verschiedene Trennzeichen – mal ein „/“, mal ein Semikolon, mal ein „:::“, mal ein „-„:
Ich habe damals einige VBA-Makros geschrieben, um die Daten „zu putzen“. Ich frage mich, ob man sie mit PowerQuery bereinigen kann. Man kann!
Ich erstelle eine Liste der Trennzeichen:
Ich importiere die Daten und trenne die Liste „hart“ an einem Zeichen:
Der Befehl
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"PK.1", "PK.2", "PK.3"})trennt die Spalte. Wie kann man alle Trennzeichen verwenden? Ich importiere die Trennzeichenliste und wandle sie in über Transformieren / In Liste konvertieren in eine Liste um:
Kann SplitTextByDelimiter meine tbl_Trennzeichen verarbeiten? Nein!
Ich gehe auf die Suche:
SplitTextByAnyDelimiter kann die Liste verarbeiten:
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), {"PK.1", "PK.2", "PK.3"})
Jedoch: {„PK.1“, „PK.2“, „PK.3“} legt fest, dass DREI neue Spalten geliefert werden mit den Namen „PK.1“, „PK.2“ und „PK.3“. Ich probiere aus:
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), {"PK.1", "PK.2", "PK.3", "PK.98", "PK.99"})Klappt! Ich erhalte weitere Spalten:
Allerdings: Wie viele Spalten entstehen denn? Ich versuche es ohne den letzten Parameter:
= Table.SplitColumn(#"Geänderter Typ", "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen))Und erhalte EINE neue Spalte:
Schlecht! Ich schaue den Parameter genauer an – er heißt:
columnNamesOrNumber
Also versuche ich eine Zahl. Ich beginne bei 99:
Der linke Teil sieht vielversprechend aus:
– der rechte nicht:
Ich überlege: ich muss berechnen wie viele neue Spalten erzeugt werden. Ich muss berechnen wie oft die Trennzeichen der Liste tbl_Trennzeichen in jedem der Texte vorkommt.
Leider stellt PowerQuery keine Funktion zur Verfügung, mit deren Hilfe man die Anzahl der vorkommenden Zeichen in einer anderen Zeichenkette ermitteln kann. So etwas berechne ich (auch in Excel) immer wie folgt:
Länge(Zeichenkette) - Länge(Zeichenkette ohne gesuchten Zeichen)oder
Länge(Zeichenkette) - Länge(Ersetze(Zeichenkette; gesuchten Zeichen))Eine PowerQuery-Abfrage muss her:
(Text.Length(Text) -
Text.Length(Text.Replace(Text -Ich überlege: minus jedes Element der Liste. Also genauer:
(Text as text) =>
List.Accumulate(
tbl_Trennzeichen,
0,
(state, current) =>
state +
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
) Ich muss die Anzahl kumulieren. Der Befehl List.Accumulate tut gute Dienste. Er möchte eine Liste haben (tbl_Trennzeichen), einen Beginn (0) und eine Funktion. Diese Funktion erhält zwei Teile:
(state, current)Die Variable state „merkt“ sich die Zahl, current greift auf jede Zeile zu. Allerdings darf ich nicht einfach die Differenz aus Länge vorher und Länge nachher bilden:
Text.Length(Text) -
Text.Length(Text.Replace(Text, current, ""))sondern muss durch die Länge teilen. Also wenn ich von der Länge
Hallo:::ich:::bin:::es:::wieder31 die ::: entferne:
Halloichbineswiederbleiben 19 Zeichen. Differenz = 12. Da aber nur vier Mal das ::: auftaucht, muss ich es noch durch die Länge teilen, also:
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
/ Text.Length(current)Und schließlich: Wenn ich in ich-will-das zwei Bindestriche finde, erhalte ich nach dem Trennen DREI Teile.
Die komplette Funktion, die ich fxAnzahlTrennzeichen nenne sieht dann so aus:
(Text as text) =>
List.Accumulate(
tbl_Trennzeichen,
0,
(state, current) =>
state +
(Text.Length(Text) -
Text.Length(Text.Replace(Text, current, "")))
/ Text.Length(current)
) + 1Und kann verwendet werden:
Von dieser Spalte wird das Maximum berechnet und über ein Drilldown als Zahl „gespeichert“ (MAXZeichen):
Ein Klick auf fx und ein Bezug wird zu einem vorhergehenden Schritt hergestellt:
= #"Geänderter Typ"Dort wird getrennt:
= Table.SplitColumn(Benutzerdefiniert1, "PK", Splitter.SplitTextByAnyDelimiter(tbl_Trennzeichen), MAXZeichen )
Und in Excel:
Probe aufs Exempel – ich trage in der Liste den folgenden Text ein:
A-B-C-D-E-F-G-H-Iund aktualisiere:
Klappt!
Witzig – noch während des Schreibens und Zwischenspeichern erhalte ich die Info, dass Oz du Soleil (zeitgleich mit mir) auch eine Lösung gepostet hat:
https://www.youtube.com/watch?v=EiHDsZxJ_EI
Er beschreitet einen ganz anderen Weg …
Hallo Herr Martin, im Anhang sende ich Ihnen eine Exeltabelle mit einer „mauell erstellten Kopfzeile“ und einer Zeilenschaltung in der Zelle „Anschrift“. Für einen Serienbrief benötige ich die „Kopzeilen“ ebenso die Zeilenschaltung nicht. Wie entferne ich am schnellsten die „Kopfzeilen“ und die Zeilenschaltung in der Zelle. (Teilenschaltung in einer Zelle zu entfernen, habe ich in Ihren Videos schon gefunden). Ich möchte alle Daten in einer Spalte haben. Ich würde mich freuen, wenn Sie mir dabei helfen würden. Mit freundlichen Grüßen PV (Ein Fan Ihrer Office-Kurse)
Hallo Herr V.,
das habe ich gemacht :
* Mit Suchen und Ersetzen die Zeichenschaltung (Strg + J) durch einen Schrägstrich ersetzt. (die Zeichenschaltung hat den Code 10 – man kann auch die Funktion WECHSELN verwenden:
=WECHSELN(F4;ZEICHEN(10);"/")
* den Verbund aller verbundenen Zellen aufgehoben
* den Textumbruch entfernt
* mit einem AutoFilter in der Spalte „Card Number“ den Text „Card Number“ und die leeren Zellen gefiltert und entfernt.
* die leeren Spalten gelöscht.
Hallo Herr Martin, ich habe noch etwas vergessen. In der Spalte „Badge Holder Name“ sind viele Namen nicht korrekt geschrieben. Wie kann ich Straßennamen schnell ändern. Ich mache es oft über Suchen + Ersetzen. Aber in vielen Situationen ist das nciht möglich. Gibt es spezielle Formel oder Funktionen die das erleichtern. Nach dem Säubern der Exeltabelle, wie kann ich dann automatisch eine Kopfzeile erstellen, so das man die Mitgliederliste anschließend ausdrucken oder auch ein PDF schreiben kann. Ich freue mich über Ihre Nachricht.
Und zu Ihrer Frage: wenn Sie immer die gleichen Ersetzungen haben, erstellen Sie eine Tabelle und verwenden die Excel-Funktion WECHSELN.
Liebe Grüße
René Martin
Herr L. schickt mir ein Video, das er erstellt hat, damit ich besser erkenne, woran der Fehler liegen könne, der in Excel auftritt. Ich öffne das Video und erhalte folgende Fehlermeldung:
Wiedergabe nicht möglich. Schaffen Sie den Inhalt erneut an.
Das Verb „anschaffen“ amüsiert mich.
Ich bin erstaunt und amüsiert.
Was ist passiert?
Ich erstelle ein Eingabeformular in Excel für eine Firma. Da mit einem Wert, beispielsweise 7,75 €, weitergerechnet wird, lösche ich den Text „7,75 € (nicht enthalten in Frachtraten)“, der in der Zelle stand, ersetze ihn durch die Zahl 7,75 und formatiere die Zelle mit einem benutzerdefinierten Zahlenformat
0,00" € (nicht enthalten in Frachtraten)"Nach einer Weile stelle ich erstaunt fest, dass der Text als Quickinfo angezeigt wird. Was passiert hier? Was habe ich hier gemacht?
Nein – es liegt keine Notiz und kein Kommentar unter der Zelle.
Nein – es liegt keine Datenüberprüfung mit einer Eingabemeldung unter dem Text.
Nein – es wurde auch kein QuickInfo eines Hyperlinks verwendet:
Erstaunt reibe ich die Augen. Das habe ich selbst gemacht! Aber wie?
Schließlich komme ich hinter des Rätsels Lösung:
Trägt man in Excel in eine Zelle eine Zahl ein, formatiert diese Zahl (beispielsweise als Währung) und verkleinert die Spalte so, dass die formatierte Zahl in der Zelle nicht angezeigt werden kann, wird die formatierte Zahl als Quickinfo angezeigt, wenn sich der Mauszeiger darüber bewegt. Aha!
Wenn ich nun mehrere Zellen markiere und die formatierte Zahl „über die Auswahl zentriere“:
wird die Zahl klar lesbar in den Zellen angezeigt. Da die Zahl aber für die Zelle, in der sie sich befindet, zu „breit“ ist, bleibt das QuickInfo stehen:
Eben! Und so kann ich eine Zahl über mehrere Zellen ausrichten …
… und formatieren, beispielsweise (hier:) mit
0" - Die Antwort auf alle Fragen!"
Und dann habe ich gelacht!
Einen schönen Fronleichnams-Feiertag wünsche ich allen Leserinnen und Lesern meines Blogs – egal, ob Sie heute arbeiten oder raus gehen.
Rene Martin
Einzelplatzschulung. Oder „Coaching“, wie es auf Deutsch heißt. Ein älterer Herr möchte die Grundlagen der Anwendungsprogramme lernen.
Wir beginnen mit Outlook. Die Möglichkeit einen Screenshot zu erstellen und in eine Mail einzufügen gefällt ihm. Ich erkläre ihm das Vorgehen:
Es funktioniert auch in anderen Programmen, erläutere ich und zeige ihm Excel. In der Registerkarte „Einfügen“ fällt sein suchender Blick auf die Mitte der Registerkarte, wo „Screenshot“ auch in Outlook zu sehen war. Kein Screenshot!
Ein Blick streift nach recht – kein „Screenshot“-Symbol. Ah – etwas weiter links – DA werden wir fündig.
Und PowerPoint? Dort finden das Symbol noch weiter links:
Immerhin: in allen Anwendungsprogrammen (auch Word) befindet sich dieses Symbol in der Gruppe „Illustrationen“.
Um die Suche zu erleichtern, erläutere ich ihm das Windows-eigene Programm „Snipping Tools“.
Frech. Ordinär. Böse und gemein.
Trotzdem.
Ich habe über den Spruch gelacht, den ich vor einigen Tagen im Internet entdeckt habe:
[Strg] + [Q]
Die Datenüberprüfung in Excel ist hinlänglich bekannt. Ein sehr praktisches Werkzeug, mit dem man eine vordefinierte Liste an Begriffen für Zellen bereitstellt.
Oder sicherstellt, dass nur bestimmte Werte in eine Zelle eingetragen werden.
Allerdings: bei sehr vielen Auswahlmöglichkeiten (sprich: langen Listen) ist die Suche und die Navigation sehr mühsam. Außerdem muss man sich auf feste Listen beschränken und darf keine freien Einträge verwenden. (ich habe auf diesem Blog schon einige Male gelästert und gespottet).
Ich habe ein kleines Add-In geschrieben – Strg + Q (so wird es gestartet), mit dem eine bequeme Auswahl und eine freie Texteingabe möglich ist.
Interessiert? Für private Nutzung stelle ich es kostenlos zur Verfügung; für geschäftliche Verwendung hätte ich gerne 10 Euro. Dann erhältst du auch eine genaue Beschreibung der Installation und Bedienung und eine Rechnung.
Interessiert?
Gerne kannst du es auf meiner Seite compurem.de herunterladen – du findest es hinter dem Menü Programmierung ::: [Strg] + [Q].
Heute mal nichts zu nörgeln. Sondern zum Staunen, Freuen und Begeistern.
Kennen Sie e90e50?
https://sites.google.com/site/e90e50
Großartig.
Danke, Andreas Thehos für diesen Hinweis.
