VBA ist eine sehr nützliche Sache. Sie können Daten aus vielen Dateien konsolidieren und große Informationsmengen mithilfe interessanter Algorithmen verarbeiten. Zum Beispiel das
Nodupes-Makro . Der während der Codeausführung auftretende Fehler wird verwendet, um das Vorhandensein eines Elements in der generierten Auflistung zu überprüfen. Es gibt Makros, die Rekursion verwenden, um alle möglichen Kombinationen und Permutationen zu bilden.
Ich möchte jedoch einige Beispiele für die Verwendung der Standardfunktionen des Excel-Arbeitsblatts zeigen.
Sehr oft müssen Sie den Garten aus Formeln herauszäunen, um den gewünschten Text oder die gewünschten Zahlen aus den Zellen zu extrahieren. Dies geschieht normalerweise aufgrund einer falschen Darstellung der Daten. Zahlen sind Zahlen, Text ist Text, mischen Sie sie nicht. Daten (und Zeiten) sind Zahlen, mit denen Berechnungen möglich und notwendig sind. Verwechseln Sie nicht den 14. März 2018 vom 14. März 2018. Das zweite ist nur ein benutzerdefiniertes Datumsformat [$ -F800], das wir in der Zelle sehen, und in der Formelleiste wird der 14. März 2018 angezeigt. Und genug davon.
1. Angenommen, Sie haben eine Tabelle mit den Start- und Endzeiten einiger Ereignisse, z. B. Telefongespräche.
Im Bild ein Teil der Tabelle _t2. Es ist notwendig, die maximale gleichzeitige Anzahl von Ereignissen zu berechnen. Dafür gibt es eine Formel:
=(((_t2[start]>=(_t2[start]))*(_t2[start]<=(_t2[end]));(_t2[start])^0)) =MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0))
Die Formel ist massiv, die Eingabe wird durch gleichzeitiges Drücken der Tasten Control, Shift, Enter (CSE) bestätigt. Die Formelleiste zeigt an, dass die Formel in geschweiften Klammern steht. MUMNOZH erstellt eine virtuelle Matrix mit der Größe der Anzahl der Zeilen der Tabelle und der Anzahl der Zeilen der Tabelle, in deren Zeilen die Berechnungsergebnisse eingegeben werden, als ob die folgende Formel in die Zeilen der Tabelle eingegeben worden wäre (für die zweite Zeile) = SUMPRODUCT (F ((C $ 2: C2> A2)) / = SUMPRODUCT (N ((C $ 2: C2> A2))) und strecken Sie sich bis zum gesamten Tisch.
2. Das gleiche, aber komplizierter. Zusätzlich zu Anfang und Ende gibt es die Anzahl der Einheiten oder die gegebene / empfangene Leistung. Es ist notwendig, die maximale Leistung zu einem bestimmten Zeitpunkt zu bestimmen.
Teil der Tabelle _t1:
Und die Formel selbst:
=((((_t1[start]*1440)<=(1:1436)-1)*((_t1[stop]*1440)>=(1:1436)-1);_t1[power])) =MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power]))
Da die Zeit ein Bruchteil einer Einheit ist, multiplizieren wir die Werte mit der Anzahl der Minuten pro Tag, um ganze Zahlen zu erhalten. Und das zweite Argument ist MULTIPLE - eine Spalte mit Leistungswerten. Im ersten Fall wurde eine Einheitenspalte generiert.
3. Links neben der Tabelle sind die Beträge aufgeführt, die bestimmten Intervallen entsprechen. Monatliche Beträge müssen berechnet werden. Es wird davon ausgegangen, dass die Beträge innerhalb ihrer Intervalle gleichmäßig verteilt sind.
Spalte H ist der erste Tag des Monats, Spalte I enthält die folgenden Formeln:
=(_tis[sum per day]*((((H3;0)>_tis[end];_tis[end];(H3;0))-(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2) =SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2)
Die Formeln sind massiv. Sie könnten SUMPRODUCT / SUMPRODUCT verwenden, aber IF ERROR funktioniert nicht ohne massive Eingabe. Leider können MAX und MIN auch kein virtuelles Wertearray bilden. Daher wird IF verwendet, um Teile der Intervalle pro Monat zu berechnen. Da die resultierenden Teile negativ sein können, wird ein Fehler künstlich verursacht (die Quadratwurzel einer negativen Zahl führt dazu). In der Quellentabelle konnte auf Spalten mit Intervalldauer und Tagesmenge verzichtet werden. Die Formel würde etwas länger werden.
4. Ein bisschen Spaß. Wie viele schöne Freitage kann es in einem Jahr geben?
Anzahl der Freitage:
=(((((A:A;B2):(A:A;C2)))=13)*((((A:A;B2):(A:A;C2));2)=5)) =SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5))
Freitags Termine:
=((15;6;(($A:$A;$B2):($A:$A;$C2))/(((($A:$A;$B2):(A:A;$C2)))=13)/(((($A:$A;$B2):($A:$A;$C2));2)=5);(A$2));"") =IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"")
Es ist sehr gut (hier), dass die erste Zeile des Blattes dem 01.01.1901 entspricht. Daher ist es einfach genug, mit INDEX und STRING eine Reihe von Daten des Jahres zu erstellen und nur den dreizehnten Freitag zu zählen.
5. Die Höhe des Zinssatzes ist je nach Höhe durchaus üblich. Wenn Sie sie falsch verwenden, sieht der Zeitplan für die erhaltenen Beträge wie in der roten Tabelle unten aus:
Um dies zu vermeiden, müssen Sie den Satz nur auf einen Teil des Betrags pro Intervall anwenden. Und für niedrigere Intervalle nehmen Sie feste Mengen.
Für solche Quelldaten:
Formel
=(A3;tbl;2)*(A3-(A3;tbl;1))+(((tbl[limit]<=A3)*(tbl[limit]-(tbl[limit];-1;))*(tbl[rate];-1;);)) =VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),))
Die Formel ist massiv. In A3 der Betrag, für den Zinsen berechnet werden sollen. Anfangsdaten –tbl.
Und schließlich eine Kreditlinie mit variablem Zinssatz. Formel zur Berechnung des Zinsbetrags in Abhängigkeit von den Daten / Beträgen der Tranchen / Renditen.
Kredit-Geschichte:
Gebot ändern:
=(B1=0;(((_d;_s[[date]:[rate]]);)*(B5:B16)*((_d>(A5:A16))*(_d<=A2);;)*1/(365+(((_d);4)=0)))-(((_d;_s[[date]:[rate]]);)*(D5:D16)*((_d>(C5:C16))*(_d<=A2);;)*1/(365+(((_d);4)=0)));) =IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),)
Die Formel ist massiv. Zählt die Zinsen nach Tagen, berücksichtigt Schaltjahre.
Vielen Dank für Ihre Aufmerksamkeit!