Excel – Formeln & VBA
Hilfreiche Formeln
Zum Austauschen von einzelnen Textteilen hilft die Funktion WECHSELN
=WECHSELN(A1;ALTER_TEXT;NEUER_TEXT)
Mit der Funktion WENNFEHLER kann man in schneller, übersichtlicherer Schreibweise den Fehlerfall wie beispielsweise geteilt durch 0 abfangen und durch einen sinnvollen Wert oder Text ersetzen.
Als Fehler gelten: #NV“, „#WERT!“, „#BEZUG!“, „#DIV/0“, „#ZAHL!“, „#NAME!“ und „#NULL!“
=WENNFEHLER((A1/A2);"nicht berechenbar")
Den Fehler „#NV“ bei SVERWEIS() oder WVERWEIS() kann man ab Excel 2013 auch mit der Funktion WENNNV beheben
=WENNNV(SVERWEIS(C1;A1:B10; 1);"Kein Wert vorhanden")
Texte können mit den folgenden Formeln vereinheitlicht werden
A1: max mustermann =GROSS2(A1) --> Max Mustermann A2: Max@Mustermann.de =klein(A2) --> max@mustermann.de
Datum und Uhrzeit
Rechnen mit Datum, Zeit und Zeitdifferenzen
in A1 „11:23“ mit Format „[h]:mm“
in B1 „=A1“ mit Format „Standard“–>0,474305556
in C1 „=B1*24“ –>11,38333333
Uhrzeiten auf viertelstunde runden:
=RUNDEN((A1*24*4;0)/(24*4))
Ein schönes Beispiel für die Kombination aus Datum und Text ist die folgende
Formel, die ich persönlich sehr häufig verwende:
="Stand: " & TEXT(JETZT();"TT.MM.JJJJ hh:mm:ss") & " Uhr"
Gantt Diagramm als Zeitplan
Startdatum in F5
Enddatum in G5
fortlaufendes Datum ab H5 (dd.mm.yyyy)
dann funktioniert ab ab H6 die folgende Formel in der bedingten Formatierung:
=UND(H$4>=$F5;H$4<=$G5)
wenn das fortlaufende Datum ab H5 als Kalenderwoche (KWxx) verwendet werden soll
funktioniert die bedingte Formatierung mit folgender Formel:
=UND(ZAHLENWERT(RECHTS(H$4;2))>=KALENDERWOCHE($F5);ZAHLENWERT(RECHTS(H$4;2))<=KALENDERWOCHE($G5))
Registername in Funktion verwenden
=TEIL(ZELLE("Dateiname";$A$1);FINDEN("]"; ZELLE("Dateiname";$A$1))+1;31)
Grafik als druckbaren Hintergrund
über die Funktion der „Kopf- und Fußzeilen“ im Ribbon „Einfügen“ kann eine Grafik eingefügt und auch formatiert werden. Diese wird dann auch gedruckt und liegt hinter den einzelnen Zellen und deren Inhalt.
Menüfunktionen
Dropdownfelder erstellen/ändern über:
Register „Daten“ | Ribbon „Datentools“ | „Datenüberprüfung…“
VBA
Springen auf ein bestimmtes Tabellenblatt
Worksheets("Tabelle1").Select
Name des aktuellen Tabellenblattes auslesen
variable = ActiveWorkbook.ActiveSheet.Name
Name der aktuellen Datei auslesen
variable = ActiveWorkbook.Name
Wert einer Zelle auslesen (Zeile, Spalte)
MyVariable = Cells(1, 1).Value
Wert in eine Zelle schreiben (Zeile, Spalte)
Cells(1, 1) = VariableValue
Aktuelles Datum und Uhrzeit in eine Zelle schreiben
Cells(1, 1) = Format(Now, "yyyy-mm-dd\/hh:mm")
aktive Pausen in den Ablauf kann man mit folgendem Befehl erreichen
Application.Wait (Now + TimeValue("0:00:10"))
Wenn geprüft werden soll, ob eine Variable leer ist, funktioniert dies sehr einfach mit dieser IF Bedingung
If IsEmpty(MyVariable) = True Then 'hier kann z.B. eine Fehlerbehandlung stattfinden, wenn die Variable leer ist. End If
Geschwindigkeit kann erreicht werden, wenn bei vielen Registerblattwechseln
durch den VBA Code die Bildschirmaktualisierung aus- und später wieder eingeschalten wird:
' Abschalten der Bildschirmaktualisierung
Application.ScreenUpdating = False
' eigener VBA Code
Prozessvariable = "hier den Prozessschritt zuweisen"
Application.StatusBar = "aktuell passiert: " & Prozessvariable
' Bildschirmaktualisierung wieder einschalten
Application.ScreenUpdating = True
Application.StatusBar = False
Dateieigenschaften lesen
varDateipfad = "C:\Test.pdf" 'nur als Muster
Set fso = CreateObject("Scripting.FileSystemObject")
If Dir(varDateipfad) <> "" Then 'Test ob Datei existiert
With fso.GetFile(varDateipfad) 'Pfadname auslesen
Cells(1, 5) = .DateCreated 'speichert das Erstellungsdatum in die Zelle
'.DateLastModified
'hier sind weitere Eigenschaften möglich
End With
Else
MsgBox "Datei existiert nicht" 'wenn der Dir Befehl nichts zurück gibt.
End If
Batch-Datei erstellen und ausführen
Dim retVal
Open "C:\temp\test.bat" For Output As #1
Print #1, "@echo off"
Print #1, "Echo Datei wurde erstellt und 2 Zeilen hinzu gefügt"
Print #1, "Pause"
Close #1 ' Datei schließen.
retVal = Shell("c:\temp\test.bat", 1)
Email über Outlook versenden
'Setzt das Outlook APPLICATION OBJECT. Dim objOutlook As Object Set objOutlook = CreateObject("Outlook.Application") 'Erzeugt ein Email Objekt Dim objMail As Object Set objMail = objOutlook.CreateItem(0) With objMail .To = "deinname@deinedomain.de; zweiteEmailadresse@domain.de" .Subject = "Betreff" .Body = "Ihre Nachricht." .Send 'Sendet die Email automatisch .Attachments.Add "C:/Beispiel_1.xlsx" .Attachments.Add "C:/Beispiel_2.xlsx" End With Set objMail = Nothing: Set objOutlook = Nothing
Wenn mit dem öffnen einer Exceldatei ein Makro gestartet werden soll, muss der Code in folgenden Bereich unter „Diese Arbeitsmappe“:
Option Explicit Private Sub Workbook_Open() 'AutoExec im VBA Excel End Sub
Wenn beim Verpassen eines Formularfeldes eine Funktion direkt ausgeführt werden soll, muss der Code in folgende Bereich:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'hier kommt der Code beim Verlassen des Feldes TextBox1
End Sub
Auf folgender Seite ist ein generischen Beispiel aufgeführt, wie eine
beliebige Tabelle eingelesen und verarbeitet werden kann:
Matrix per VBA generisch auslesen
kurze Tipps per Video:
- Fortschrittsbalken mit Hilfe von Formel REPT und Schriftart Playbill
- Fristen berechnen und automatisch einfärben
- dynamischer Kalender mit Formeln und bedingter Formatierung
- automatisches importieren von Logos über Formel
weiterführende Links:
Kalender im Excelformat als Vorlage
Blattschutz bei vergessenem Passwort entfernen
c’t 10/2017, S. 128 – Tipps für den effizienten Umgang mit Excel (kostenloser Artikel)
c’t 17/2018, S. 158 Tipps für den Umgang mit bedingten Formatierungen in Excel
c’t 24/2018, S. 174 – Excels Zahlenumwandlungswut mit benutzerdefinierten Formaten besänftigen
c’t 02/2019, S. 140 – Tipps zu Steuerelementen und Formeln in Excel
c’t 08/2019, S. 152 – Komplexe Daten mit Excels Pivot-Tabellen bändigen
c’t 10/2019, S. 144 – Riesige Datenmengen in Excel mit Power Pivot auswerten
Visualisieren Sie Daten aus Excel auf einer Deutschlandkarte