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"

 

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

weiterführende Links:

office-loesung.de

Kalender im Excelvormat als Vorlage

Matrix füllen

Blattschutz bei vergessenem Passwort entfernen

Text Funktion

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

Kartendiagramme mit Excel 365

Visualisieren Sie Daten aus Excel auf einer Deutschlandkarte

Dateiname über Funktion in Zelle