Archive for the ‘MS Office & VBA’ Category.

Excel: Formel nur auf die x-te Zeile anwenden

Hier ein kurzes Beispiel für die Nutzung der Rest() Funktion, um Formeln in Excel nur auf jede x-te Zeile anzuwenden.

  1. Mit zeile() erhält man die Zeilennummer des aktuellen Bezugs
  2. Rest() gibt den Rest bei der ganzzahligen Teilung zurück
  3. Wenn() prüft einfach die Bedingung, ob Rest() einen bestimmten Wert hat

Man kann nicht nur gerade/ungerade prüfen (oberes Beispiel), auch bei anderen Zeilensprüngen klappt das.

excel

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Excel VBA: Blatt in Liste von Excel Dateien kopieren

Eine Aufgabe für zwischendurch: Wie kann man ein bestehendes Excel-Blatt in eine Anzahl von anderen Excel-Dateien kopieren?

  • Definiere eine benannte Zell-Range, hier „Workbooks“ genannt
  • In dieser Liste trage alle Excel-Dateien ein, in die das Muster-Blatt (hier „Template“ genannt) kopiert werden soll.
    Hinweis: Ich habe diese Liste mit dir /b *.xlsx erzeugt.
  • Setze einen Button in das Sheet und hinterlege als Code das folgende
  • Wichtig: Die aktuelle Arbeitsmappe mit dem Button und der Liste liegt im selben Verzeichnis wie die Ziel-Dateien. Wenn nicht, dann muss der Pfad angepasst werden.
Sub Schaltfläche1_Klicken()
 
Dim c As Range
For Each c In Range("Workbooks")
    MsgBox (c.Value)
    Set kopiereWas = ThisWorkbook.Sheets("Template")
    Set kopiereWohin = Workbooks.Open(Application.ActiveWorkbook.Path + "\\" + c.Value)
    kopiereWas.Copy kopiereWohin.Sheets(1)
Next c
End Sub

Hinweis: Die Ziel-Arbeitsmappe wird hier nicht geschlossen, werde ich zusammen mit Screenshots nachliefern.

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Excel Keyboard Shortcuts

A PDF with the most important Excel shortcuts: http://www.thecompanyrocks.com/wp-content/uploads/2011/02/CR-Updated-Chart-of-Popular-Excel-Keyboard-Shortcuts.pdf

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Excel Funktion zum Zerlegen eines Strings

Die folgende Excel-Funktion ist nützlich, um innerhalb von Excel Strings in ihre Bestandteile zu zerlegen.


Function SplitteString(zeichenkette, separator, vorkommen) As String
Dim feld() As String
feld = Split(zeichenkette, separator)
SplitteString = feld(vorkommen - 1)
End Function

split

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Using Document Properties in Microsoft Word 2007

What I really like about LaTeX is the way one can easily define own commands that get expanded in the text whenever needed. Word has a similar feature, called document variables. Unfortunately these can only be set by VBA macros, there is (according to my knowledge) to way to manage them via GUI. Fortunately Word has another feature which provides a more or less similar functionality with the name „Document properties“.

Here’s a brief tutorial how to define and use a new variable:

1) Open the properties of the document

01

2) Open the extended properties

02

3) Create a new variable, here I used „Projekt“ with the value „MeinProjekt“

03

4) To use this new variable simply insert a new field:

04

05

5) and select the corresponding variable

06

6) The internal syntax is the following (when you press Alt-F9 you get this „internal“ view)

07

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

MS Excel: Komplexe Wenn()-Formeln durch Vergleich ersetzen

Vor kurzem hatte ich eine kleine Herausforderung in Excel: Beträge in Spalte C mussten mit negativem Vorzeichen versehen werden, in Abhängigkeit einer Zahl in Spalte B (gelb markiert im Bild). Lösen lässt sich dies per verschachtelter WENN() Funktion, bei vielen Verschachtelungen wird die Formel aber schnell unübersichtlich und fehleranfällig. Deutlich einfacher und übersichtlicher geht es über eine Mischung aus VERGLEICH() und ISTNV(), die ich bei stackexchange gefunden hatte.

=WENN(ISTNV(VERGLEICH([@Unterkonto];unterkonten;0));[@Amount];-1*[@Amount])

  • für die Zahlen, die den Vorzeichenwechsel auslösen, habe ich einen benannten Bereich ‚unterkonten‘ festgelegt
  • VERGLEICH() schaut, ob ein Wert (der erste Parameter) in einer Liste (zweiter Parameter) vorhanden ist. Wird ein Wert nicht gefunden, wird #NV zurückgeliefert. Der zweite Parameter kann auch ein benannter Bereich sein.
  • Um die #NV Werte zu behandeln, prüfen wir mit ISTNV() die Rückgabe der VERGLEICH() Funktion
  • Die Rückgabe von ISTNV() wird dann per WENN() geprüft. Wenn ISTNV() WAHR zurückliefert, dann hat VERGLEICH() das Konto nicht in der Liste gefunden, sonst ist der Werte aus Spalte „Amount“ mit -1 zu multiplizieren.

Download der Excel-Datei

Excel Vergleich ISTNV

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Mit Excel führende Leerzeichen entfernen und hinzufügen

Hier ein kurzes Beispiel, wie man mit Excel führende Leerzeichen entfernen und auch wieder hinzufügen kann.

excel_zehn

Sei in B2 die Zahl im Format „drei Ziffern“-„10 Ziffern“:

Dann ermittelt

=WERT(LINKS(B2;FINDEN("-";B2)-1)) die Zahl vor dem Bindestrich.

=WERT(RECHTS(B2;LÄNGE(B2)-FINDEN("-";B2))) die Zahl hinter dem Bindestrich

und

=TEXT(C2;"000") & "-" & TEXT(D2;"0000000000") fügt die Zahl aus beiden Teilen wieder im ursprünglichen Format zusammen.

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

MS Excel: Werte auf Basis einer Werteliste extrahieren

Hier ein Beispiel, wie man anhand einer einfachen Liste und einer recht komplexen Formel Werte aus einer Spalte ziehen kann.

Gegeben ist eine Liste von Firmennamen, die als letzten Bestandteil (getrennt vom Namen durch ein Leerzeichen) die Rechtsform enthält. Nur durch das Hinzufügen einer neuen Rechtsform zur Liste sollen alle entsprechenden Rechtsformen extrahiert werden.

excel_20131013

Excel_lookup

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Excel-Kommentare per Funktion auslesen

Ich erhalte manchmal Excel-Dateien, in deren Zell-Kommentaren oft unzählige, nicht unwichtige Anmerkungen stehen.

Mit der folgenden benutzer-definierten Funktion lassen sich diese Kommentare auslesen.

Function GetComment(commentcell) As String
    On Error Resume Next
    GetComment = commentcell.Comment.Text
End Function

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website

Importing events into Google Calendar

Google Calendar is able to import events from CSV files. The format however is a bit different from the locale used here in Germany, so I prepared a Excel sheet. It allows you to enter the dates and then uses Excel’s TEXT() function to build the correct format.

gcal

GCal_20130601

Uwe

Uwe Ziegenhagen has been working with LaTeX for more than a decade. Besides TeX/LaTeX he likes to work with Python, Rasberry/Arduino and his digital camera.

More Posts - Website