Eine Liebeserklärung an MS Excel
Frage in der Excelschulung: mein Suchen-Dialog sieht aber ganz anders aus als Ihrer.
Mein Dialog:
Ihr Dialog:
Der Unterschied war zum Glück schnell gefunden – man muss nur auf „Optionen“ klicken …
PowerPoint nervt auch. Ich soll mehrere Präsentationen überarbeiten. Und dort nachsehen, ob eine bestimmte Schrift vorhanden ist – sie mache manchmal bei bestimmten Kunden Schwierigkeiten.
Leider kann man in PowerPoint nicht nach der Formatierung suchen:
Also muss ich per Hand die einzelnen Textblöcke selektieren und den Namen der Schriftart prüfen. Mühsam!
Immerhin: mit der [Tab]-Taste kann man bequem jeden Block erreichen.
Amüsant:
Trägt man in einer List in der Spalte der Ort beim Autofilter in das Suchen-Feld den Text Ulm ein, werden auch Orte wie Kulmbach oder Neckarsulm gefunden:
Abhilfe schafft ein Anführungszeichen am Anfang und am Ende: „Ulm“
Allerdings: beim Suchen wird „Ulm“ nicht gefunden:
Hier muss man die Option „Gesamten Zellinhalt“ bemühen.
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.
Ich soll ein Excel-Formular (per VBA) auswerten. Schnell stelle ich fest, dass einige Zellen gesperrt sind. Das ist nicht sehr geschickt, da diese Zellen vom Anwender und von der Anwenderin ausgefüllt werden sollen:
Wie finde ich diese Zellen? Es gibt leider keine Suchoption dafür, so dass alle gesperrten oder nicht gesperrten Zellen markiert werden.
Also anders: mit der Ersetzenfunktion gelingt es: Öffnet man die Optionen, kann man in Excel nach Formaten suchen. Eben: beispielsweise nicht gesperrte Zellen. Es erweist sich geschickter, die offenen Zellen zu finden, als die gesperrten, da alle Zellen in den 1.048.567 x 16.384 Zeilen und Spalten gesperrt sind.
Und diese werden durch eine Hintergrundfarbe ersetzt. So findet man schnell die nicht gesperrten Zellen:
Ich erstelle für einen Kunden in Excel mit VBA ein mächtiges Eingabeformular. Schnell sind wir uns einig darüber, der der Anwender und die Anwenderin nicht mit dem Befehl Suchen-Ersetzen Texte auf einem Tabellenblatt austauschen sollen. Also nehmen wir in diesem Formular dieses Symbol (genau: die ganze Gruppe) aus dem Menüband:
Dazu sind ein paar Zeilen in der XML-Datei nötig, in der das Menüband beschrieben wird:
Der Code:
<?xml version="1.0" encoding="utf-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso="TabHome">
<group idMso="GroupEditingExcel" visible="false" >
</group>
</tab>Und wie wird ersetzt? Natürlich über ein eigenes Werkzeug:
Ich überlege: dem Anwender und der Anwenderin bleiben immer noch die Möglichkeit mit den Tastenkombinationen [Strg] + [F], beziehungsweise [Strg] + [H] den Suche-Dialog zu öffnen. Also raus damit:
Private Sub Workbook_Open()
On Error Resume Next
Application.OnKey "^f", "BitteNicht" ' suchen
Application.OnKey "^h", "BitteNicht" ' ersetzen
End SubBeim Öffnen der Datei werden diese beide Tastenkombinationen „verbogen“, indem das Makro „BitteNicht“ aufgerufen wird. Es erscheint ein Meldungsfenster. Diese Prozedur wird auch beim Aktivieren der Mappe gestartet:
Private Sub Workbook_Activate()Schnell merke ich, dass das Makro nach Schließen der Datei (oder auch Wechseln in eine andere Datei) noch aktiv ist. Also: raus damit:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f", "" ' suchen
Application.OnKey "^h", "" ' ersetzen
End SubEbenso beim Ereignis Workbook_Deactivate.
Und was passiert? Richtig – wenn ich jetzt [Strg] + [F] drücke, passiert: NICHTS. Warum? Genau – ich muss natürlich schreiben:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "^f" ' suchen
Application.OnKey "^h" ' ersetzen
End SubDANN ist das Ereignis wiederhergestellt!
Erstaunlich! Aber erklärbar!
Ich erstelle eine Reihe an Werten, mit denen ich weiterrechne – beispielsweise „+“:
Ich lösche zwei Zeilen. Der Fehler #BEZUG! ist die Folge:
Ich kopiere diese fehlerhafte Zeile an einen andere Stelle und füge sie als Inhalt ein:
Ich lösche mit dem Ersetzen Befehl, indem ich #BEZUG! durch „nichts“ ersetze:
Klappt – alles weg. Sowohl in den Formeln (=#BEZUG!+#BEZUG!+D1) als auch in den Zellen
Ich zeichne diese Aktion mit dem Makrorekorder auf:
Der Code:
Cells.Replace What:="#BEZUG!", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2Das Ergebnis:
Nichts wird gelöscht! Also doch per Hand. Beispielsweise so:
Sub Bezug_Loeschen_02()
Dim xlZelle As Range
For Each xlZelle In ActiveSheet.UsedRange
If xlZelle.Text = "#BEZUG!" Then
xlZelle.ClearContents
End If
Next
End SubDer Grund ist verständlich: Der Suchen- und Ersetzen-Befehl ist sehr mächtig. So wird auch in „26.10.2020“ der Wert „2020“ gefunden, obwohl in der Zelle eigentlich der Wert 44.130 steht. Eben: #BEZUG! ist eigentlich ein Fehlerwert (und kein Text). Er steht ja auch in der Mitte der Zelle. Suchen und ersetzen „übergeht“ diesen Datentyp; der VBA-Befehl Replace sucht (und ersetzt) einen Text (was nicht in der Zelle steht).
Und wer nun fragt: „Wer braucht denn so etwas?“ Letzte Woche habe ich eine Datei mit fehlerhaften Bezügen bekommen. Der Anwender hat ein Blatt von einer Datei in eine andere kopiert. Ich wollte diese nichtssagenden Fehler mit einem Makro „rausputzen“ – Klick auf Button sollte die Datei „bereinigen“.
Und so habe ich festgestellt, dass der Replace-Befehle nicht geeignet ist.
Seit ein paar Tagen habe ich in Microsoft 365 die neue Funktion LET. Damit kann man Formeln unter einem Namen ablegen, um sie (mehrmals) wieder zu verwenden. Beispielsweise so:
=ZELLE(„dateiname“)
oder besser:
=ZELLE(„filename“)
liefert den Namen und Speicherort der aktuellen Datei:
Benötigt man nun den Pfad, kann man das Zeichen „[“ suchen (oder finden):
=SUCHEN(„[„;D3)
liefert in unserem Beispiel 19. Bis ein Zeichen vor diesem Zeichen kann von links der Text herausgelöst werden:
=LINKS(D3;D4-1)
liefert hier: D:\Eigene Dateien\
Oder zusammengefasst:
=LINKS(ZELLE(„dateiname“);SUCHEN(„[„;ZELLE(„dateiname“))-1)
Darin wird nun zwei Mal die Funktion ZELLE(„dateiname“) verwendet. Man kann sie an einen Namen in der Funktion LET übergeben, beispielsweise an „Dateiname“:
=LET(Dateiname;ZELLE(„dateiname“);LINKS(Dateiname;SUCHEN(„[„;Dateiname)-1))
Allerdings: Das Ergebnis ist länger als die vorherige Funktion. Also ein kürzerer Funktionsname:
=LET(DN;ZELLE(„dateiname“);LINKS(DN;SUCHEN(„[„;DN)-1))
Das erhöht allerdings nicht gerade die Lesbarkeit!
Fazit: LET ist sicherlich eine nützliche und praktische Funktion. Allerdings kommt sie wohl erst bei sehr langen Formeln zum Einsatz. Und auch nur dann wenn Formelteile sich mehrmals wiederholen. Legt man diese Teil unter einem (langen) sprechenden Namen ab, wird das Ergebnis weder kürzer noch gut lesbar. Ich fürchte, sie wird wohl in den wenigsten Berechnungen in Excel in Microsoft 365 Einzug finden …
Erstaunlich: Warum kann man übe die Registerkarte „Start“ zwar Kommentare und Notizen löschen:
jedoch nur nach Notizen suchen? Haben die da etwas vergessen?
Man muss einfach immer genau hinschauen. Immer!
Ich habe eine Excelliste, in der trage ich Informationen zu meinen Schulungen ein. Ich habe eine Spalte „Stunden außerhalb“, in der ich bislang Texte eingetragen habe wie „2 x 24“ oder „3 x 10“. Nun hätte ich gerne die Summe der Tage ermittelt, also die Zahlen, die vor dem „x“ stehen summiert. Nichts leichter als das:
=LINKS(I351;SUCHEN(„x“;I351)-2)
liefert die Zahlen. Allerdings: steht kein Text in der Zelle, so liefert SUCHEN(„x“;I351) einen Fehler. Der kann bequem mit WENNFEHLER abgefangen werden:
=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);““)
Das Ergebnis:
Darauf setzte ich eine Pivottabelle auf und ändere den Vorschlag „ANZAHL“ in „SUMME“:
Eine Fehlermeldung ist die Folge. Vielleicht hängt es mit dem „“ der Funktion WENNFEHLER zusammen? Ich ändere die Formel in:
=WENNFEHLER(LINKS(I351;SUCHEN(„x“;I351)-2);0)
Die leeren Zellen werden mit einer „0“ aufgefüllt. Sieht nicht schön aus. Also ändere ich das Zahlenformat in ein benutzerdefiniertes:
0;-0;;
Und wundere mich erneut. Alles ist weg!
Langsam dämmert es mir: klar – die Funktion LINKS liefert einen Text. Das heißt: der Text „2“ wird durch das benutzerdefinierte Zahlenformat weggeblendet. Und: Excel erkennt keine Zahlen für die Pivottabelle, die summiert werden könnten. Also noch einmal die Formel anpassen. Der Text muss in eine Zahl konvertiert werden: mit WERT, mit „–“ oder mit „*1“
=WENNFEHLER(WERT(LINKS(I351;SUCHEN(„x“;I351)-2));0)
Und dann kann die Pivottabelle auch summieren:
Aktualisieren nicht vergessen!
Und schon wieder hat man mich gezwungen libreOffice Calc zu unterrichten. Sehr ärgerlich finde ich dort, dass viele Dialoge erweiterte Optionen haben, beispielsweise der Dialog Suchen/Ersetzen:
Klappt man ihn auf stehen weitere Optionen zur Verfügung. Dort kann etwas eintragen:
Das Ärgerliche ist nun Folgendes: schließt man die Dialog und ruft ihn erneut auf, so ist er wieder zugeklappt. Allerdings: die Einstellungen, die in den „weiteren Optionen“ vorgenommen wurden, stehen immer noch drin und sind aber auf den ersten Blick nicht sichtbar. Ärgerlich und verwirrend! Ebenso beim Standardfilter:
Und bei den Pivottabellen/Datenpilot:
Immerhin: die Stadt München steigt nun wieder auf Microsoft Office um: Nachdem die gesamte Stadtverwaltung mit openSource ausgestattet wurde: Millionen an Kosten für neue Hardware, Anpassung, Programmierung, … wurde dann festgestellt, dass der Austausch mit der übrigen Welt nicht so gut funktioniert. Ja, dass es für bestimmte Anwendungen (beispielsweise CAD-Programme) keine openSource auf Linux gibt.
In dem Jahr, in dem Microsoft den Firmensitz von Unterschleißheim nach München verlegte, beschloss die Stadt München zurück zu Microsoft zu kehren. Ab November 2019 sollen die ersten Referate migriert werden – bis nächstes Jahr soll Microsoft als Standard wiederhergestellt werden – sehr rasch (überstürzt?) – schließlich sind nächstes Jahr Kommunalwahlen.
Und nun werde ich gefragt, ob ich Umsteigertrainings für Word, Excel und PowerPoint durchführen kann und möchte. Wir sind gespannt.
Seltsam: Die Funktion SUCHEN kann Platzhalter („*“ und „?“) verarbeiten – FINDEN nicht!
Danke an Martin Weiß (der Tabellenexperte) für diesen Hinweis.
Was passieren kann, wenn man einen Text als Text formatiert – darauf habe ich schon hingewiesen. Auch was passiert, wenn man einen (langen) Text als Buchhaltung formatiert:
Amüsant ist dagegen auch folgendes Phänomen: Wenn man einen Text als Datum, Prozentwert oder Währung formatiert:
und dann mit der Funktion LÄNGE weiter rechnet – allerdings mit mindestens zwei Rechenoperationen (beispielsweise LÄNGE – 1 oder LÄNGE x 1), dann wird das Zahlenformat übernommen:
Erstaunlicherweise: FINDEN und SUCHEN liefern auch Zahlen – sie übernehmen jedoch nicht das Zahlenformat.
gestern in der Excel-Schulung wollte eine Teilnehmerin wissen, warum „ersetzen“ (von suchen und ersetzen) als Funktion „WECHSELN“ heißt und nicht „ERSETZEN“. Denn ERSETZEN macht ja etwas anderes:
Die Antwort: Ich weiß es nicht. Auch im Englischen heißen die Funktionen SUBSTITUTE und REPLACE. Irgendwie doof gemacht …
Hilfe!
Ich habe eine Exceltabelle, in der mein Kollege häufig Zeilenumbrüche – also [ALT] + [Return] eingefügt hat. Wie kann ich die ganz schnell löschen?
Viele Zeilenumbrüche
Die Antwort: Sie können im Ersetzen-Dialog (Registerkarte Start / Bearbeiten / Suchen und Auswählen oder: [STRG] + [H]) auch nach Formatierungen suchen. Erstaunlicherweise findet Excel die Zeilenumbrüche, wenn Sie die Formatierung in der Registerkarte „Ausrichtung“ aktivieren. Und löscht sie, wenn Sie bei „Ersetzen“ explizit den Zeilenumbruch ausschalten UND die Option „An Zellgröße anpassen“ einschalten.
Hallo – und ich würde gerne Formeln finden. Ich weiß, dass auf der Tabelle Formeln liegen – beispielsweise in den Zellen J6:J12. Aber Excel findet sie nicht!
Vom Suchen und Finden
Die Antwort: Sie dürfen nur eine Zelle auswählen! In Ihrem Beispiel haben Sie die Zellen A1:F1 ausgewählt (was man aufgrund der grünen Farbe nicht sehr deutlich sieht). Und DORT findet Excel KEINE Formeln. Also: Markierung auflösen – dann wird die Suche mit Erfolg gekrönt.
Ich habe in der letzten Excelschulung gelernt, dass man mit Textfunktionen Texte „manipulieren“ kann. Das wollte ich ausprobieren.
Ich habe eine Liste mit Vor- und Zunamen. Die Vornamen löse ich mit:
=LINKS(A2;FINDEN(“ „;A2)-1)
heraus. Klappt prima. Bei den Nachnamen bei der Formel
=RECHTS(A2;FINDEN(“ „;A2)-1)
macht er aber bei einigen Namen Blödsinn. Warum?
RECHTS klappt nicht!
Das Problem: Die Funktion FINDEN (oder auch SUCHEN) findet einen Text innerhalb eines anderen VON LINKS. Das bedeutet: Sie schneiden aus dem Text VON RECHTS so viele Buchstaben heraus wie der Vorname lang ist. Das kann zufälligerweise funktionieren, normalerweise aber nicht. Sie lösen das Problem entweder mit der Gesamtanzahl der Buchstaben LÄNGE:
=RECHTS(A2;LÄNGE(A2)-FINDEN(“ „;A2))
oder mit der Funktion TEIL, die ab einem bestimmten Zeichen herausschneidet:
=TEIL(A2;FINDEN(“ „;A2)+1;999)
(zugegeben: die Zahl 999 ist „geschummelt“ – Sie schneiden nach dem Leerzeichen 999 (oder eine noch höhere Anzahl Buchstaben heraus) – aber es funktioniert)
Oder Sie löschen die ersten Buchstaben weg:
=ERSETZEN(A2;1;FINDEN(“ „;A2);““)
All das funktioniert.
So funktioniert es – drei Varianten
Ich werde noch wahnsinnig. Ich weiß, wenn ich nichts markiere, durchsucht Excel das gesamt Tabellenblatt. Wenn ich etwas markiere, durchsucht er nur den markierten Bereich. Aber leider nicht immer.
Beispiel: Ich markiere die Spalte D (Vorname) und suche dort den Text „Martin“. Er springt allerdings nach F71, wo er korrekt, aber nicht gewünscht, die „Martinsgasse“ findet. Woran liegt das?
Ich markiere und suche …
… aber Excel findet nicht (da wo er finden soll)
Die Antwort: Öffnen Sie im Suchendialog die Optionen. Sicherlich haben Sie (von der letzten Suche) noch eingestellt, dass er nicht auf dem (aktuellen) Blatt suchen soll, sondern in der (gesamten) Arbeitsmappe, das heißt: in allen Blättern. Deshalb ignoriert er Ihre Markierung. Klar – einer hat Vorrang – hier ist es die Arbeitsmappe. Schalten Sie dort wieder auf „Blatt“ um. Dann klappt die Suche im ausgewählten Bereich.
Die Suche – Nicht lokal, sondern global