Ermitteln Sie den #Neujahrstag in #VBA

Erstellt am 25. Februar 2016 von katharinakanns

Sie brauchen den #Neujahrstag in #VBA? Und #Silvester? Ein Vorschlag:

JahresErster = DateValue("1.1." & Year(Datum))

Wir basteln also eine Zeichenkette aus 1.1. und dem Jahr des gegebenen Datums. Das Jahr lassen wir von  der VBA-Funktion Year ermitteln, es liefert am heutigen Tag, dem 25.02.2016, einfach die Zahl 2016. Die so gewonnenen Textwurst 1.1.2016 konvertieren wir mit Hilfe von DateValue wieder in ein Datum – die Funktion ist wirklich gut darin, Textwürste als Datum zu interpretieren. Sie kann das, wenn Punkte, Leerzeichen, Bindestriche oder Schrägstriche zwischen den Werten für Tag, Monat und Jahr stehen, und die Werte dürfen Zahlen mit und ohne führende Nullen sein, aber auch Feb oder Mär.

Machen wir auch gleich den Jahresultimo:

JahresLetzter = DateValue("31.12." & Year(Datum))

Sie verstehen, wie’s gemacht wird, oder?

Veröffentlicht unter VBA | Verschlagwortet mit , , , , , , , , , , | Hinterlasse einen Kommentar

Den #Monatsultimo in #VBA ermitteln

Erstellt am 24. Februar 2016 von katharinakanns

Wie berechnen Sie den #aktuellen #Monatsultimo in #VBA? Zum Beispiel so:

MonatsLetzter = DateAdd("m", 1, DateAdd("d", -Day(Datum), Datum))

Sieht etwas kompliziert aus … aber einen Teil davon erkennen Sie hoffentlich von gestern wieder: DateAdd(„d“, –Day(Datum), Datum) lieferte uns da den vergangenen Monatsultimo. Und um von diesem auf den aktuellen Monatsultimo zu gelangen, genügt es, von diesem einen Monat in die Zukunft zu springen. Das machen wir mit dem „m“, das für Monat steht, und dem Intervall 1, die wir beide in der eleganten Funktion DateAdd einsetzen.

Alles klar? Wenn nicht: fragen Sie mich einfach 🙂

Veröffentlicht unter VBA | Verschlagwortet mit , , , , , , , , , | Hinterlasse einen Kommentar

Den #Letzten des #Vormonats mit #VBA ermitteln

Erstellt am 23. Februar 2016 von katharinakanns

Sie brauchen den vergangenen #Monatsultimo per #VBA? Der ist ganz leicht:

VorMonatsletzter = DateAdd("d", -Day(Datum), Datum)

Was geschieht hier? Mit den Funktionen Day und DateAdd, die wir gestern schon zur Berechnung des Monatsersten herangezogen haben, gehen wir einfach vom gegebenen Datum so viele Schritte in die Vergangenheit, wie wir in diesem Monat bereits Tage haben – am 23.02.2016 also 23 Tage. Damit landen wir beim Letzten des Vormonats.

Veröffentlicht unter VBA | Verschlagwortet mit , , , , , , | 1 Kommentar

den #Monatsersten mit #VBA ermitteln

Erstellt am 22. Februar 2016 von katharinakanns

Wie errechnen Sie in #VBA den #Monatsersten zu einem #Datum? Das geht ganz schnell:

Monatserster = DateAdd("d", -Day(Datum) + 1, Datum)

Und funktioniert so: die Funktion Day(Datum) gibt Ihnen den Tag als Zahl – heute, am 22.02.2016 also die Zahl 22. Die ziehen Sie jetzt vom Eingangsdatum ab, und dann nehmen Sie 1 dazu, um auf den Monatsersten zu kommen. Vom 22.02.2016 müssen Sie ja nur 21 Tage in die Vergangenheit gehen, um am 01.02.2016 zu landen. Das Datumsrechnen übernimmt für Sie die Funktion DateAdd(Intervall, Anzahl, Datum), die vom gegebenen Datum eine bestimmte Anzahl von ausgesuchten Intervallen in die Zukunft (bei positiver Anzahl) oder in die Vergangenheit (bei negativer Anzahl) schreitet. Mit „d“ definieren Sie das Intervall Tag. Ja, auch in der deutschen Version – im Gegensatz zu den Excel-Funktionen ist und bleibt in VBA alles englisch.

Angekommen?

Veröffentlicht unter VBA | Verschlagwortet mit , , , , , , | 2 Kommentare

#Excel #Funktionen #Deutsch #Englisch

Erstellt am 19. Februar 2016 von katharinakanns

#Wunschkonzert: die vielen #Funktionen in #Excel sind #sprachabhängig – wie heißen sie alle auf #englisch?

Office 2016 für Windows, Stand 26.02.2016 – es gab neue Funktionen zum Frühstück 🙂

Deutsch Englisch
ABS ABS
ARCCOS ACOS
ARCCOSHYP ACOSH
ARCCOT ACOT
ARCCOTHYP ACOTH
ADRESSE ADDRESS
AGGREGAT AGGREGATE
UND AND
ARABISCH ARABIC
BEREICHE AREAS
ASC ASC
ARCSIN ASIN
ARCSINHYP ASINH
ARCTAN ATAN
ARCTAN2 ATAN2
ARCTANHYP ATANH
MITTELABW AVEDEV
MITTELWERT AVERAGE
BASIS BASE
BETA.VERT BETA.DIST
BETA.INV BETA.INV
BETAVERT BETADIST
BETAINV BETAINV
BINOM.VERT BINOM.DIST
BINOM.VERT.BEREICH BINOM.DIST.RANGE
BINOM.INV BINOM.INV
BINOMVERT BINOMDIST
BITUND BITAND
BITLVERSCHIEB BITLSHIFT
BITODER BITOR
BITVERSCHIEB BITRSHIFT
BITXODER BITXOR
OBERGRENZE CEILING
OBERGRENZE.MATHEMATIK CEILING.MATH
ZELLE CELL
ZEICHEN CHAR
CHIVERT CHIDIST
CHIINV CHIINV
CHIQU.VERT CHISQ.DIST
CHIQU.VERT.RE CHISQ.DIST.RT
CHIQU.INV CHISQ.INV
CHIQU.INV.RE CHISQ.INV.RT
CHIQU.TEST CHISQ.TEST
CHITEST CHITEST
WAHL CHOOSE
SÄUBERN CLEAN
CODE CODE
SPALTE COLUMN
SPALTEN COLUMNS
KOMBINATIONEN COMBIN
KOMBINATIONEN2 COMBINA
TEXTKETTE CONCAT
VERKETTEN CONCATENATE
KONFIDENZ CONFIDENCE
KONFIDENZ.NORM CONFIDENCE.NORM
KONFIDENZ.T CONFIDENCE.T
UMWANDELN CONVERT
KORREL CORREL
COS COS
COSHYP COSH
COT COT
COTHYP COTH
ANZAHL COUNT
ANZAHL2 COUNTA
ANZAHLLEEREZELLEN COUNTBLANK
ZÄHLENWENN COUNTIF
KOVAR COVAR
KOVARIANZ.P COVARIANCE.P
KOVARIANZ.S COVARIANCE.S
KRITBINOM CRITBINOM
COSEC CSC
COSECHYP CSCH
DATUM DATE
DATWERT DATEVALUE
DBMITTELWERT DAVERAGE
TAG DAY
TAGE DAYS
TAGE360 DAYS360
GDA2 DB
DBANZAHL DCOUNT
DBANZAHL2 DCOUNTA
GDA DDB
DEZIMAL DECIMAL
GRAD DEGREES
SUMQUADABW DEVSQ
DBAUSZUG DGET
DBMAX DMAX
DBMIN DMIN
DBPRODUKT DPRODUCT
DBSTABW DSTDEV
DBSTDABWN DSTDEVP
DBSUMME DSUM
DBVARIANZEN DVAR
DBVARIANZEN DVARP
URLCODIEREN ENCODEURL
FEHLER.TYP ERROR.TYPE
GERADE EVEN
IDENTISCH EXACT
EXP EXP
EXPON.VERT EXPON.DIST
EXPONVERT EXPONDIST
F.VERT F.DIST
F.VERT.RE F.DIST.RT
F.INV F.INV
F.INV.RE F.INV.RT
F.TEST F.TEST
FAKULTÄT FACT
FALSCH FALSE
FVERT FDIST
XMLFILTERN FILTERXML
FINDEN FIND
FINV FINV
FISHER FISHER
FISHERINV FISHERINV
FEST FIXED
UNTERGRENZE FLOOR
UNTERRENZE.MATHEMATIK FLOOR.MATH
SCHÄTZER FORECAST
FORMELTEXT FORMULATEXT
HÄUFIGKEIT FREQUENCY
FTEST FTEST
ZW FV
GAMMA GAMMA
GAMMA.VERT GAMMA.DIST
GAMMA.INV GAMMA.INV
GAMMAVERT GAMMADIST
GAMMAINV GAMMAINV
GAMMALN GAMMALN
GAUSS GAUSS
GEOMITTEL GEOMEAN
VARIATION GROWTH
HARMITTEL HARMEAN
WVERWEIS HLOOKUP
STUNDE HOUR
HYPGEOM.VERT HYPGEOM.DIST
HYPGEOMVERT HYPGEOMDIST
WENN IF
WENNNV IFNA
WENNS IFS
IMCOSHYP IMCOSH
IMCOTOT IMCOT
IMCOSEC IMCSC
IMCOSECHYP IMCSCH
IMSECHYP IMSEC
IMSECHYP IMSECH
IMSINHYP IMSINH
IMTAN IMTAN
INDEX INDEX
INDIREKT INDIRECT
INFO INFO
GANZZAHL INT
ACHSENABSCHNITT INTERCEPT
ZINSZ IPMT
IKV IRR
ISTLEER ISBLANK
ISTFEHL ISERR
ISTFEHLER ISERROR
ISTFORMEL ISFORMULA
ISTLOG ISLOGICAL
ISTNV ISNA
ISTKTEXT ISNONTEXT
ISTZAHL ISNUMBER
ISOKALENDERWOCHE ISOWEEKNUM
ISPMT ISPMT
ISTBEZUG ISREF
ISTTEXT ISTEXT
KURT KURT
KGRÖSSTE LARGE
LINKS LEFT
LÄNGE LEN
RGP LINEST
LN LN
LOG LOG
LOG10 LOG10
RKP LOGEST
LOGINV LOGINV
LOGNORM.VERT LOGNORM.DIST
LOGNORM.INV LOGNORM.INV
LOGNORMVERT LOGNORMDIST
VERWEIS LOOKUP
KLEIN LOWER
VERGLEICH MATCH
MAX MAX
MAXWENNS MAXIFS
MDET MDETERM
MEDIAN MEDIAN
TEIL MID
MIN MIN
MINWENNS MINIFS
MINUTE MINUTE
MINV MINVERSE
QIKV MIRR
MMULT MMULT
MODALWERT MODE
MODUS.VIELF MODE.MULT
MODUS.EINF MODE.SNGL
MONAT MONTH
VRUNDEN MROUND
MEINHEIT MUNIT
N N
NV NA
NEGBINOM.VERT NEGBINOM.DIST
NEGBINOMVERT NEGBINOMDIST
NETTOARBEITSTAGE.INTL NETWORKDAYS.INTL
NORM.VERT NORM.DIST
NORM.INV NORM.INV
NORM.S.VERT NORM.S.DIST
NORM.S.INV NORM.S.INV
NORMVERT NORMDIST
NORMINV NORMINV
STANDNORMVERT NORMSDIST
STANDNORMINV NORMSINV
NICHT NOT
JETZT NOW
ZZR NPER
NBW NPV
ZAHLENWERt NUMBERVALUE
UNGERADE ODD
BEREICH.VERSCHIEBEN OFFSET
ODER OR
PDURATION PDURATION
PEARSON PEARSON
QUANTIL PERCENTILE
QUANTIL.EXKL PERCENTILE.EXC
QUANTIL.INKL PERCENTILE.INC
QUANTILSRANG PERCENTRANK
QUANTILSRANG.EXKL PERCENTRANK.EXC
QUANTILSRANG.INKL PERCENTRANK.INC
VARIATIONEN PERMUT
VARIATIONEN PERMUTATION
VARIATIONEN2 PERMUTATIONA
PHI PHI
PI PI
RMZ PMT
POISSON POISSON
POISSON.VERT POISSON.DIST
POTENZ POWER
KAPZ PPMT
WAHRSCHBEREICH PROB
PRODUKT PRODUCT
GROSS2 PROPER
BW PV
QUARTILE QUARTILE
QUARTILE.EXKL QUARTILE.EXC
QUARTILE.INKL QUARTILE.INC
RADIANT RADIANS
ZUFALLSZAHL RAND
RANG RANK
RANG.MITTELW RANK.AVG
RANG.GLEICH RANK.EQ
ZINS RATE
ERSETZEN REPLACE
WIEDERHOLEN REPT
RECHTS RIGHT
RÖMISCH ROMAN
RUNDEN ROUND
ABRUNDEN ROUNDDOWN
AUFRUNDEN ROUNDUP
ZEILE ROW
ZEILEN ROWS
ZSATZINVEST RRI
BESTIMMTHEITSMASS RSQ
SUCHEN SEARCH
SEC SEC
ESECHYP SECH
SEKUNDE SECOND
BLATT SHEET
BLÄTTER SHEETS
VORZEICHEN SIGN
SIN SIN
SINHYP SINH
SCHIEFE SKEW
SCHIEFE.P SKEW.P
LIA SLN
STEIGUNG SLOPE
KKLEINSTE SMALL
WURZEL SQRT
STANDARDISIERUNG STANDARDIZE
STABW STDEV
STABW.N STDEV.P
STABW.S STDEV.S
STABWN STDEVP
STFEHLERYX STEYX
WECHSELN SUBSTITUTE
TEILERGEBNIS SUBTOTAL
SUMME SUM
SUMMEWENN SUMIF
SUMMENPRODUKT SUMPRODUCT
QUADRATESUMME SUMSQ
SUMMEX2MY2 SUMX2MY2
SUMMEX2PY2 SUMX2PY2
SUMMEXMY2 SUMXMY2
ERSTERWERT SWITCH
DIA SYD
T T
T.VERT T.DIST
T.VERT.2S T.DIST.2T
T.VERT.RE T.DIST.RT
T.INV T.INV
T.INV.2S T.INV.2T
T.INV.RE T.TEST
TAN TAN
TANHYP TANH
TVERT TDIST
TEXT TEXT
TEXTVERKETTEN TEXTJOIN
ZEIT TIME
ZEITWERT TIMEVALUE
TINV TINV
HEUTE TODAY
MTRANS TRANSPOSE
TREND TREND
GLÄTTEN TRIM
GESTUTZTMITTEL TRIMMEAN
WAHR TRUE
KÜRZEN TRUNC
TTEST TTEST
TYP TYPE
UNIZEICHEN UNICHAR
UNICODE UNICODE
GROSS UPPER
WERT VALUE
VARIANZ VAR
VAR.P VAR.P
VAR.S VAR.S
VARIANZEN VARP
VDB VDB
SVERWEIS VLOOKUP
WEBDIENST WEBSERVICE
WOCHENTAG WEEKDAY
WEIBULL WEIBULL
WEIBULL.VERT WEIBULL.DIST
ARBEITSTAGE.INTL WORKDAY.INTL
XODER XOR
JAHR YEAR
G.TEST Z.TEST
GTEST ZTEST
Veröffentlicht unter english, Excel | Verschlagwortet mit , , , , , , | 1 Kommentar

erster Jänner eines Jahres

Erstellt am 18. Februar 2016 von katharinakanns

Wie bekommen Sie in #Excel den #Neujahrstag zu einem #Datum?

=DATWERT(„1.1.„&JAHR(A1))

Hier verwenden wir zwei Funktionen: JAHR kratzt aus einem Datum (für das Beispiel nehme ich an, das heutige Datum, also der 18.2.2016, steht in A1) die Jahreszahl heraus, liefert also 2016. Da häng ich vorn den Text „1.1.“ dran und lass die Funktion DATWERT drüberlaufen. Die versucht nämlich, einen Text als Datum zu interpretieren, und liefert den entsprechenden Datumswert. Bei 1.1.2016 fällt ihr das leicht 🙂

Veröffentlicht unter Excel, Uncategorized | Verschlagwortet mit , , , , , , , | Hinterlasse einen Kommentar

#Monatserster #in einem halben Jahr

Erstellt am 17. Februar 2016 von katharinakanns

Sie brauchen nun den #Ersten des #Monats, der in einem #halben #Jahr kommt? Erinnern Sie sich an gestern – da haben Sie eine #Funktion kennen gelernt, mit der Sie jeden beliebigen Monatsletzten ermitteln konnten … der Erste des Folgemonats kommt ja gleich darauf, also finden Sie mit

=MONATSENDE(A1;6)+1

den 1.9.2016 (ausgehend davon, dass in A1 das heutige Datum, also der 17.02.2016, steht). Die Funktion liefert den 31.8.2016 (also den letzten des sechstfolgenden Monats), und der nächste Erste ist dann schnell ausgerechnet.

Veröffentlicht unter Excel | Verschlagwortet mit , , , , , , , | Hinterlasse einen Kommentar

#Monatsletzter in #Excel

Erstellt am 16. Februar 2016 von katharinakanns

Sie brauchen einen #Monatsultimo in #Excel?

Den vergangenen Monatsultimo finden Sie mit der Formel von gestern – und einer klitzekleinen Änderung:

=A1–TAG(A1)

Steht in A1 der 16.02.2016, so erhalten Sie damit den 31.01.2016.

Den aktuellen Monatsultimo ermitteln wir mit einer anderen Funktion:

=MONATSENDE(A1;0)

MONATSENDE(Datum; Anzahl Monate) liefert Ihnen den Monatsletzten des Monats, den Sie erreichen, wenn Sie vom angegebenen Datum aus die angegebene Anzahl Monate in die Zukunft (bei negativer Anzahl in die Vergangenheit) schreiten. Und da wir mit 0 gar nirgends hin schreiten, kommt eben der Letzte des aktuellen Monats, der 29.02.2016, heraus.

Veröffentlicht unter Excel | Verschlagwortet mit , , , , , , | 1 Kommentar

#Monatserster in #Excel

Erstellt am 15. Februar 2016 von katharinakanns

Sie brauchen in #Excel den #Monatsersten zu einem #Ausgangsdatum? Dann benützen Sie einfach die Formel

=A1–TAG(A1)+1

(wenn Ihr Datum in A1 steht).

Die Funktion TAG(Datum) liefert Ihnen die Tageszahl, also – am heutigen Beispiel – 15, weil eben der 15.2.2016 ist.

Und da jedes Datum in Wirklichkeit eine Zahl ist, die jeden Tag um 1 größer als am Vortag ist und nur mit START > Zahl > Zahlenformat als Datum dargestellt wird, müssen wir für den Monatsersten nur die Anzahl der Tage seit Monatsbeginn abziehen und dann noch eins dazuzählen. Damit erreichen wir den 1.2.2016.

Veröffentlicht unter Excel | Verschlagwortet mit , , , | 1 Kommentar

#Summe berechnen – aber nur, wenn …

Erstellt am 13. Februar 2016 von katharinakanns

Ein Weg, um die #Summe von #ausgewählten #Werten zu berechnen, wenn #SUMMEWENN nicht ausreicht (Prüfung auf leere Zellen, numerische Inhalte, Teilstrings, …): mit Hilfe von #SUMMENPRODUKT.

Was wir wissen müssen:

  • SUMMENPRODUKT kann ich verwenden, um mehrere Matrizen Zeile für Zeile zu verarbeiten, und die einzelnen Ergebnisse zu summieren.
  • ISTZAHL, ISTFEHLER, aber auch jeglicher Vergleich liefern als Ergebnis WAHR oder FALSCH – und WAHR repräsentiert in Wirklichkeit 1, FALSCH ist dasselbe wie 0.

Damit kann es losgehen. Was wir machen ist Folgendes:

Wir multiplizieren Wahrheitswerte mit den Zahlen. Ist ein Wahrheitswert 0, dann ist das Ergebnis für diese Zeile der Matrix 0, sie wirkt sich also auf die Summe nicht aus. Sind alle Wahrheitswerte 1, dann fließt die Zahl in die Summe ein.

Drei Beispiele zum besseren Verständnis:

XTipp Summenprodukt Wahrheit

=SUMMENPRODUKT(ISTLEER(B2:B11)*C2:C11)
B2 ist nicht leer, daher ergibt ISTLEER(B2) 0, 0*300=0
B3 ist nicht leer, daher ergibt ISTLEER(B3) 0, 0*100=0
B4 ist leer, daher ergibt ISTLEER(B4) 1, 1*200=200
…
B8 ist leer, daher ergibt ISTLEER(B8) 1, 1*600=600
…
200+600=800 Das ist das gesuchte Ergebnis.

=SUMMENPRODUKT((A2:A11=“Haushalt„)*ISTLEER(B2:B11)*C2:C11)
siehe oben, nur ergänzt um die Prüfung, ob in A2, A3, … „Haushalt“ steht. Nur in einer einzigen Zeile – Zeile 3 – ergibt sich das Bild A3=“Haushalt“ ist WAHR, ISTLEER(B3) ist WAHR, daher 1*1*200=200. In allen anderen Zeilen ist entweder der erste oder der zweite Faktor 0, sie fließen daher in das Ergebnis nicht ein.

=SUMMENPRODUKT((LINKS(B2:B11;1)=“E„)*C2:C11)
Hier ist die Prüfung, ob der erste Buchstabe des Wertes in Spalte B ein E ist. LINKS(B2;1) ist zum Beispiel WAHR, also 1, daher sind die 300 aus C2 im Ergebnis dabei; LINKS(B3;1) ist FALSCH, also 0, und die 200 aus C3 spielen nicht mit.

Die eigentliche Produktfunktion von SUMMENPRODUKT ist hier übrigens nicht zur Anwendung gekommen – ich hab die erzeugte Teilsummenmatrix mit nichts mehr multipliziert; die ganze Funktion hat nur ein einziges Argument. Dies nur der Vollständigkeit halber, damit niemand motzt. Lassen Sie sich von dieser Detailklauberei nicht verwirren 🙂

„Das braucht kein Mensch,“ hör‘ ich Sie murmeln – aber, glauben Sie mir, ich bin schon einigen Situationen begegnet, die sich nicht anders lösen ließen.

 

 

Veröffentlicht unter Excel | Verschlagwortet mit , , , , , , , , | Hinterlasse einen Kommentar