Vor einiger Zeit wurde eine interessante Möglichkeit identifiziert, um mittels einer CSV Datei eine Ausführung eines beliebigen Programms mit MS Excel zu bewerkstelligen. Als Basis dafür kann eine beliebige CSV Datei verwendet werden:

UserId,Datum,ProjektName,Beschreibung,Dauer
1,2017-11-01,Test Projekt,Das ist ein Test,180
2,2017-08-17,Hausbau,"Haus1, Haus2, Haus3", 240
3,2017-07-25,Wichtiger Kunde,"=2+2+cmd|' /C calc'!A0", 240

In der Zeile drei und in der Spalte Beschreibung wird eine Formel verwendet:

"=2+2+cmd|' /C calc'!A0"

Schritt 1

Als erstes die Datei mit MS Excel öffnen.

Schritt 2

Danach die entsprechenden Zeilen markieren.

Schritt 3

Daraufhin auf den Reiter “DATEN” wechseln und Button “Text in Spalten” klicken.

Jetzt dem Wizard mit “Weiter” folgen.

Die Option “Komma” wählen und “Fertig stellen” klicken.

Es erfolgt ein Sicherheitshinweis durch Excel.

Schritt 4

Und siehe da, der Taschenrechner wird gestartet.

Quelle

http://georgemauer.net/2017/10/07/csv-injection.html

Beim Arbeiten mit MS Excel hat plötzlich die Aktualisierung der Formeln nicht mehr funktioniert. Folgende Schritte haben Abhilfe verschafft:

1. Auf den Reiter “Formeln” klicken.

2. Den Eintrag “Berechnungsoptionen” wählen und die Option “Automatisch” setzen.

Fertig :)

Vor kurzem wurde eine Aufgabe an mich herangetragen. Mit Hilfe von MS Excel soll eine Statusliste gepflegt werden.

Wenn in der Spalte “Status” der Wert “Erledigt” eingetragen wird,

soll automatisch die entsprechende Zeile (3) ausgeblendet werden.

Für die Umsetzung habe ich ein Makro erstellt, welches unter dem Reiter “Entwicklertools – Visual Basic” eingebunden wird.

Daraufhin öffnet sich der Visual Basic for Application Editor. Für die Einbindung muss die entsprechende Tabelle gewählt

und der folgende Quellcode eingefügt werden:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    Dim i As Long, l_rowCount As Long
    
    'get last filled cell in column D
    l_rowCount = Cells(Rows.Count, 4).End(xlUp).Row
    
    'loop backwards and hide row
    For i = l_rowCount To 1 Step -1
      If LCase(Cells(i, 4).Text) = "erledigt" Then
        Cells(i, 4).EntireRow.Hidden = True
      End If
    Next i
    
End Sub

Bei jeder Aktualisierung/Änderung in der Tabelle 1 wird das Makro automatisch ausgeführt. Wenn in einer Zeile in der Spalte D der Wert “erledigt” eingegeben wird, erfolgt automatisch ein Ausblenden der jeweiligen Zeile.

Die Beispieldatei zum Download.

Mit MS Excel ist es mit der Funktion “SUMME” möglich, die Summe einer Liste von Werten oder einem Wertebereich zu ermitteln. In der Zeile 7 befindet sich ein Beispiel für die Anwendung der Funktion mit einem Wertebereich.

Funktion Summe

=SUMME(B1:B6)

Für die Ermittlung der Summe aller negativen oder positiven Werte wird eine andere Funktion benötigt.

Eine sehr einfache Möglichkeit ist die Verwendung der Funktion “SUMMEWENN”.

Summewenn für negative Werte

=SUMMEWENN(B1:B6;"<0")

Der erste Parameter “B1:B6” legt den Bereich für die Werte, die dem Suchkriterium entsprechen, fest und definiert den Bereich aller Werte, die summiert werden sollen. Der zweite Parameter “<0” bestimmt das Suchkriterium, in diesem Fall, alle Werte kleiner null.

Summewenn für positive Werte

=SUMMEWENN(B1:B6;">0")

Für die Summe aller positiven Werte muss lediglich das Suchkriterium “>0” angepasst werden.

Die Beispieldatei zum Download.

In MS Excel gibt es die Möglichkeit, Auswahllisten in Zellen darzustellen und somit die Eingabe auf den Inhalt der Liste zu beschränken. An folgendem Beispiel soll die Umsetzung veranschaulicht werden. Auf dem Reiter “Daten” werden die Produkte dargestellt, welche noch einen Status benötigen.

Auf dem zweiten Reiter “Filter”, wird der mögliche Status aufgeführt.

Im nächsten Schritt wird die Zelle C2 auf dem Reiter “Daten” selektiert. Im Hauptmenü von Excel “Daten” muss dann die Schaltfläche “Datenüberprüfung” gewählt.

Danach öffnet sich ein neues Fenster. Hier im Feld “Zulassen” den Wert “Liste” auswählen.

Jetzt den Wert “Quelle” mit dem Bereich aus dem Reiter “Filter” füllen.

=Filter!$A$1:$A$3

Daraufhin mit “Ok” bestätigen und die Auswahlliste mit den gewünschten Werten wird angezeigt.

Die Beispieldatei zum Download.

In der Spalte A werden verschiedene Unternehmen aufgeführt. Dabei sind die Rechtsformen in Anführungszeichen dargestellt. Für eine Auswertung wird nur die Rechtsform benötigt.

Mit der Funktion TEIL wird eine bestimmte Anzahl Zeichen ab einer festgelegten Position zurückgegeben.

=TEIL(Text; Erstes_Zeichen; Anzahl_Zeichen)

Das Wort “Text” muss mit A1 ersetzt werden, “Erstes_Zeichen” mit dem Wert 9 und “Anzahl_Zeichen” mit dem Wert 4.

=TEIL(A1; 9; 4)

Da es in der Liste nicht nur Rechtsformen mit 4 Zeichen gibt, muss die 9 und die 4 durch weitere Formeln ersetzt werden.

Als erstes wird die Position des Anführungszeichens mit der Funktion “Finden” gesucht .

=FINDEN("""";A1)

Das Anführungszeichen wird in der Spalte A1 an 8. Stelle gefunden. Als nächstes wird die gesamte Länge aller Zeichen in der Zelle benötigt.

=LÄNGE(A1)

Für die Zelle A1 erhalten wir den Wert 13. Da die Anführungszeichen nicht relevant sind, wird nur der Text von Position 9 bis 12 benötigt, welcher dem Wert “GmbH” entspricht.

Die 9 kann wie folgt berechnet werden

=FINDEN("""";A1)+1

und das zurückgelieferte Ergebnis beträgt 9, denn 8+1=9. Die 4 kann durch

=LÄNGE(A1)-FINDEN("""";A1)-1

berechnet werden und das zurückgelieferte Ergebnis beträgt 4, denn die Länge 13 – Finden 8 -1 = 4.

Die vollständige Formel lautet wie folgt:

=TEIL(A1;FINDEN("""";A1)+1;LÄNGE(A1)-FINDEN("""";A1)-1)

Hier das Beispiel zum Download.

Mit der Formel “Summewenn” ist es möglich, Werte in einem Bereich zu addieren, wenn diese den angegebenen Kriterien entsprechen. Folgende Tabelle soll zur Veranschaulichung dienen:

Interessant wäre jetzt herauszufinden, welcher Betrag je Kategorie angefallen ist.

Hierfür kann die Formel “Summewenn” verwendet werden.

SUMMEWENN(Bereich; Suchkriterium; Bereich für die Summe)

Das erste Attribut “Bereich” legt fest, wo nach dem “Suchkriterium” gesucht werden soll. Das “Suchkriterium” ist die Einschränkung für die Summenberechnung (Bereich für die Summe). In diesem Beispiel sieht die Formel für die Summe der Beschaffung wie folgt aus:

=SUMMEWENN(A2:A10;E20;C2:C10)

Jetzt wäre noch eine Aufschlüsselung je Monat ganz nützlich. Ein weiteres Suchkriterium ist zwar möglich, aber gestaltet sich etwas umständlich. Deshalb gibt es seit MS Office 2007 die Formel “Summewenns“. Hier wurden die Suchkriterien erweitert und es sind mehrere Bedingungen für eine Summenbildung einstellbar.

Als erstes wird eine Hilfsspalte (D) benötigt, in der aus dem Datum der aktuelle Monat dargestellt wird.

Mit der Formel Monat lässt sich das schnell bewerkstelligen.

=MONAT(B2)

Danach kann es dann los gehen.

Die Reihenfolge der Attribute ist leicht verändert. Das erste Attribut wird für die Summenberechnung benötigt, danach folgt das erste Suchkriterium und der dazugehörige Bereich mit der Option weitere Attribute anzugeben.

SUMMEWENNS(Bereich für die Summe; Suchkriterium 1; Bereich 1, Suchkriterium 2; Bereich 2)

Für die Berechnung der Summe aller Werte der Beschaffung für den Monat Januar ergibt sich folgende Formel:

=SUMMEWENNS(C2:C10;A2:A10;H20;D2:D10;I19)

Zum besseren Nachvollziehen gibt es die Beispieldatei zum Download.

Heute hatte ich den Fall, dass in MS Excel 2007 das ziehen von Formel und Format übertragen nicht mehr funktioniert hat.

Nach kurzer Recherche war die Lösung gefunden.

1. Auf die Microsoft Office-Schaltfläche  Klicken.
2. Dann auf Excel-Optionen Klicken.
3. Daraufhin auf Erweitert.
4. Und zu guter Letzt, im Bereich “Optionen bearbeiten” das Kontrollkästchen “Ausfüllkästchen und Drag-and-Drop von Zellen aktivieren” setzen.

Danach funktioniert alles wieder wie gewohnt. Der Unterschied ist auch optisch sichtbar.

Deaktiviert	Aktiviert

Aus der Reihe kleine MS Excel Aufgaben für zwischendurch. Ich habe eine Spalte (A) mit verschiedenen Uhrzeiten. Ich wollte herausfinden, welche Werte davon kleiner als 07:00 Uhr sind.

Folgende Formel habe ich erstellt:

=WENN(TEXT(A2;"HH:MM")<TEXT("07:00";"HH:MM"); "Ja"; "Nein")

Hier eine Erläuterung der einzelnen Funktionen.

Die Funktion TEXT wandelt einen numerischer Wert in Text um. Dabei wird das Format der Uhrzeit mit HH:MM angegeben.

TEXT(A2;"HH:MM")

Dieser Zwischenschritt ist wichtig, da ein direkter Vergleich über die WENN Funktion zu einem falschen Ergebnis führt. Durch die Anpassung des Formats ist ein Vergleich möglich.

Die Funktion WENN vergleicht zwei Werte und gibt einen definierten Wert zurück.

=WENN(1<2; "Ja"; "Nein")

Wenn eins kleiner zwei, dann wird der Wert “Ja” angezeigt, andernfalls der Wert “Nein”.

Vor ein paar Tagen hatte ich eine nette Aufgabe mit MS Excel zu lösen. Folgende Ausgangssituation, ich benötige die Summe der Werte in der Spalte B. Alle Monate die noch nicht begonnen haben, beinhalten einen negativen Wert. Diese möchte ich explizit aus der Summe ausschließen.

Kurz gesagt, ich benötige die Summe der Monate die kleiner gleich dem aktuellem Monat sind.

Folgende Formel kam dabei zustande:

=SUMME(B1:INDIREKT(ADRESSE(MONAT(HEUTE());2)))

Hier eine Erläuterung der einzelnen Funktionen.

Die Funktion Heute gibt den aktuellen Tag aus.

HEUTE()

Die Funktion Monat gibt den aktuellen Monat aus. Als Übergabeparameter wird ein Zahl (1-12) oder ein Datum benötigt.

MONAT(HEUTE())

Die Funktion Adresse gibt die Adresse der Spalte/Zeile aus.

ADRESSE(MONAT(HEUTE());2))

Die 2 ist die Spalte B.

Es wird der Wert “$B$5”  geliefert.  Eigentlich wären wir hier fertig. Folgende Funktion

Summe(B1; ADRESSE(MONAT(HEUTE());2)))

funktioniert so aber nicht. Die Funktion Summe benötigt immer einen von und bis Wert und versteht den variablen bis Wert nicht.

Mit der Formel Indirekt lässt sich dies umgehen. Hiermit lässt sich der Bezug ändern, ohne die Formel zu ändern.

INDIREKT(ADRESSE(MONAT(HEUTE());2))

Jetzt noch die Summenformel dazu und wir sind fertig. :)

Für ein VBA-Script habe ich die aktuelle Bildschirmauflösung benötigt. Die implementierte Funktion sieht wie folgt aus.

Option Explicit
Private Const SM_CXSCREEN = 0
Private Const SM_CYSCREEN = 1

Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long

Public Function ScreenResolution()

    Dim x As Integer
    Dim y As Integer

    x = GetSystemMetrics(SM_CXSCREEN)
    y = GetSystemMetrics(SM_CYSCREEN)

   ScreenResolution = x & " : " & y

End Function

Hier eine Beispieldatei zum Download: Example File