Excel (VBA): Formeln aktualisieren nicht

2016-07-08 von Mario

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.

excel_formel_01

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

excel_formel_02

Fertig :)

Kategorie: IT Schlagwörter: , , , , , ,

Excel (VBA): Zeilen automatisch ausblenden

2016-06-06 von Mario

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

ausblenden01

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

ausblenden02

soll automatisch die entsprechende Zeile (3) ausgeblendet werden.

ausblenden03

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

ausblenden03a

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

ausblenden04

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.

Kategorie: IT Schlagwörter: , , , , ,

Excel (VBA): Summe nur negativer oder positiver Werte

2014-10-06 von Mario

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.

Summe_neg_pos

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.

Kategorie: IT Schlagwörter: , , , , ,

Excel (VBA): Auswahllisten in Zellen

2014-07-29 von Mario

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.

Excel_Daten01

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

Excel_Daten02

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.

Excel_Daten03

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

Excel_Daten04

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

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

Excel_Daten05

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

Excel_Daten06

 

Die Beispieldatei zum Download.

 

Kategorie: IT Schlagwörter: , , , , , , ,

Excel (VBA): Zelleninhalt finden und kopieren

2014-05-15 von Mario

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.

Zelleninhalt01

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.

Zelleninhalt02

Die vollständige Formel lautet wie folgt:

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

 

Hier das Beispiel zum Download.

Kategorie: IT Schlagwörter: , , , , ,

Excel (VBA): Summewenns mit Datum

2014-03-02 von Mario

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:

Datenbasis

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

SummeWenn

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.

Hilfsspalte

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

=MONAT(B2)

Danach kann es dann los gehen.

SummeWenns

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.

 

Kategorie: IT Schlagwörter: , , , , , , , ,

Excel (VBA): Zellen in Excel runterziehen funktioniert nicht

2014-03-01 von Mario

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  ZA010077102 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.

Excel_Optionen

 

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

 

Deaktiviert Aktiviert
Excel_Optionen1 Excel_Optionen2
Kategorie: IT Schlagwörter: , , , , , ,

Excel (VBA): Wenn Abfrage mit Uhrzeiten

2013-10-28 von Mario

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.

 

Excel_Uhrzeit

 

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”.

 

Kategorie: IT Schlagwörter: , , , , , ,

Excel (VBA): Summe bis aktueller Monat

2012-05-07 von Mario

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. :)

 

Kategorie: IT Schlagwörter: , , , , , ,