Posts tagged ‘Excel’

Eine IN Funktion für Excel VBA

Hier basierend auf einem Beispiel von Microsoft eine IN-Funktion. Mit dieser lässt sich prüfen, ob ein String in einem zusammengesetzten String enthalten ist.

Option Explicit
 
Function Contains(needle As String, haystack As String, separator As String) As Boolean
 
Dim rv As Boolean, lb As Long, ub As Long, i As Long, field() As String
 
field = Split(haystack, separator)
 
    lb = LBound(field)
    ub = UBound(field)
    For i = lb To ub
        If field(i) = needle Then
            rv = True
            Exit For
        End If
    Next i
    Contains = rv
End Function

Nachtrag: Möchte man prüfen, ob ein Wert in einer Range vorhanden ist, kann man die folgende User-Defined Function nutzen:

Function InRange(needle As Variant, haystack As Range) As Boolean
Dim rv As Boolean, cell As Range
 
    For Each cell In haystack
        If cell = needle Then
            rv = True
            Exit For
        End If
    Next cell
    InRange = rv
 
End Function

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Zeilen kombinieren mit pandas

Vor einiger Zeit hatte ich eine Excel-Datei zu bearbeiten, in der in einer Spalte die Spaltennamen, in einer anderen die korrespondieren Werte standen. Immer drei Zeilen bildeten den eigentlichen Datensatz. Mit wenigen Zeilen Pandas und cleverer Adressierung der Ergebnis-Zelle.

Spaltenname Wert
ColA Andi
ColB Berni
ColC Cesar
ColA Dorian
ColB Ernest
ColC Frank

 

import pandas as pd
 
# Lade die Daten
daten = pd.read_excel('combine.xlsx')
# Erstelle leeren Dataframe mit den Spaltennamen aus den Excelzeilen 
verarbeitet = pd.DataFrame(columns=['ColA','ColB','ColC'])
 
# Iteriere über die Daten
for i, row in daten.iterrows():
    # ganzzahliges Teilen, um die Zeile zu bestimmen
    # in die die Zelle gehört, Spalte ergibt sich aus dem Wert in 'Spalte'
    verarbeitet.loc[i // 3,row['Spalte']] = row['Wert']
 
print(verarbeitet)
ColA ColB ColC
0 Andi Berni Cesar
1 Dorian Ernest Frank

Nachtrag: Stephan vom Kölner Data Science Meetup hat mir noch einen alternativen Weg gezeigt:

import pandas as pd
 
data = {'A': ["cola", "colb", "colc", "cola", "colb", "colc"], "B": [1, 2, 3, 4, 5, 6]}
data = pd.DataFrame(data)
gb = data.groupby('A')
res = pd.DataFrame()
for key in gb.groups:
    res[key] = gb.get_group(key)['B'].values.flatten()
 
print(res)

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Buchungen auswerten mit Python Pandas

Ich bin ehrenamtlich Schatzmeister der Dingfabrik Köln e.V., als solcher muss ich auch schauen, welches Mitglied denn die Beiträge bezahlt hat. Mit Hilfe von Pandas lassen sich die Buchungsdaten sehr elegant aufbereiten.

Ausgangspunkt sind die folgenden Excel-Dateien

Mitglieder.xlsx

Enthält die Mitgliedsnummer, den Namen sowie die Mitgliedsart (F für Fördermitglied, O für Ordentlich, E für Ehemalig, etc.)

Klasse Name Mitgliedsart
1 Max Mustermann O

Buchungen.xlsx

Die Buchungen wurden aus Quicken 2015 nach Excel exportiert, die Datei muss leider noch manuell bearbeitet werden, da der Excel-Export von Quicken nicht sonderlich schön ist. Die relevante Kategorie ist in diesem Beispiel „Mitgliedsbeitrag“, Klasse enthält die zugewiesene Mitgliedsnummer.

Buchungstag Konto Vorgang Empfänger Verwendungszweck Kategorie Klasse Betrag
04.01.2016 Firmengirokonto Köln 3763   Buchungstext bla, bla Mitgliedsbeitrag 1 23,00

Diese beiden Dateien können wir jetzt mit Pandas verarbeiten.

import pandas as pd
import numpy as np
import time as t
 
# Anpassungen an der Pandas-Ausgabe
pd.set_option('display.float_format', lambda x: '%.2f' % x)
# http://stackoverflow.com/questions/11707586/python-pandas-widen-output-display
pd.set_option('display.height', 1000)
pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 500)
pd.set_option('display.width', 1000)
 
# lies die Stammdaten aus Excel
mitglieder = pd.read_excel('Mitglieder.xlsx', 'Tabelle1')
# konvertier die Mitgliedsnummer in einen String
mitglieder['Klasse'] = mitglieder['Klasse'].apply(int).apply(str)
# Ehemalige Mitglieder interessieren nicht
mitglieder = mitglieder[mitglieder.Mitgliedsart.str.contains('A|F|O')]
 
# lies die Buchungen ein
buchungen = pd.read_excel('Buchungen.xlsx', 'Sheet')
# entferne alle Zeilen, die keinen 'Mitgliedbeitrag' enthalten
buchungen = buchungen[buchungen.Kategorie.str.contains('Mitgliedsbeitrag')]
 
# entferne Zeilen ohne Betrag oder Klasse
buchungen = buchungen[np.isfinite(buchungen['Betrag'])]
buchungen = buchungen[np.isfinite(buchungen['Klasse'])]
 
# Konvertiere den Buchungstag in ein Pandas-Datum
buchungen['Buchungstag'] = pd.to_datetime(buchungen['Buchungstag'],dayfirst=True,format='%d.%m.%Y') 
 
# Füge neue Spalten für Quartal und Monat hinzu
buchungen['Quartal'] = buchungen['Buchungstag'].dt.quarter
buchungen['Monat'] = buchungen['Buchungstag'].dt.month
 
# Wandle 'Klasse' von Float => Integer => String 
buchungen['Klasse'] = buchungen['Klasse'].apply(int).apply(str) 
 
# Führe einen right join durch, wir wollen auch die Datensätze von Mitgliedern haben, die noch aktiv sind, aber noch nichts bezahlt haben
buchungen = pd.merge(buchungen,mitglieder, how='right', on=['Klasse', 'Klasse'])
 
# Durch den right Join haben wir jetzt einige NaN (Not a Number) Zellen, die wir durch 0.0 ersetzen
# Dadurch erzeugen wir Dummy Datensätze, die aber nicht stören
buchungen['Betrag'].fillna(value=0,inplace=True)
buchungen['Buchungstag'].fillna(value=pd.Timestamp('20160101'),inplace=True)
# Einen Eintrag für die Monatsspalte nehmen wir auch vor, darüber wird pivotisiert
buchungen['Monat'] = buchungen['Buchungstag'].dt.month
 
# Erstellung der Pivot-Table
pivotTable = pd.pivot_table(buchungen,dropna=False,margins=True,index=['Name'],values=['Betrag'],columns=['Monat'],aggfunc=np.sum)
 
print(pivotTable)
 
# Optional: Rausschreiben der Tabelle nach Excel
#pivotTable.to_excel('pivotisiert.xlsx')

Das Ergebnis, hier anonymisiert und bei Betrag nur jeweils 1.0 eingetragen, sieht dann so aus:

pivot

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

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 mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

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 mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

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 mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

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 mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

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 mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

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 mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website

Namen zerlegen mit Excel

Nachdem mich am Freitag mal wieder die Frage erreichte, wie man mit Excel Namen der Form „Nachname Vorname“ in Vor- und Nachnamen zerlegen kann, hier ein kleines Tutorial.

MustermannMax.xlsx

Uwe

Uwe Ziegenhagen mag LaTeX und Python, auch gern in Kombination.

Hat Dir dieser Beitrag geholfen und möchtest Du Dich dafür bedanken? Dann unterstütze doch vielleicht die Dingfabrik Köln e.V. mit einem kleinen Beitrag. Details zur Bezahlung findest Du unter Spenden für die Dingfabrik.

More Posts - Website