Excel et formules longues

VBA est une chose très utile. Vous pouvez consolider les données de nombreux fichiers et traiter de grandes quantités d'informations à l'aide d'algorithmes intéressants. Par exemple, la macro Nodupes . Il utilise l'erreur qui se produit lors de l'exécution du code pour vérifier la présence d'un élément dans la collection générée. Il existe des macros qui utilisent la récursivité pour former toutes les combinaisons et permutations possibles.

Mais je voudrais montrer quelques exemples d'utilisation des fonctions de feuille de calcul Excel standard. Assez souvent, vous devez clôturer le jardin de formules afin d'extraire le texte ou les nombres souhaités des cellules. Cela se produit généralement en raison d'une présentation incorrecte des données. Les nombres sont des nombres, le texte est du texte, ne les mélangez pas. Les dates (et l'heure) sont des nombres avec lesquels il est possible et nécessaire de faire des calculs. Ne confondez pas le 14 mars 2018 et le 14 mars 2018. Le second n'est qu'un format de date personnalisé [$ -F800], ce que nous voyons dans la cellule, et sur la barre de formule sera le 14 mars 2018. Et assez à ce sujet.

1. Supposons que vous ayez une table avec les heures de début et de fin de certains événements, par exemple des conversations téléphoniques.


Dans l'image, une partie du tableau _t2. Il est nécessaire de calculer le nombre maximum simultané d'événements. Pour cela, il existe une formule:

=(((_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)) 

La formule est massive, l'entrée est confirmée en appuyant simultanément sur les boutons Control, Shift, Enter (CSE). La barre de formule montrera que la formule est entre accolades. MUMNOZH crée une matrice virtuelle de la taille du nombre de lignes du tableau par le nombre de lignes du tableau, dans les lignes desquelles les résultats du calcul sont entrés, comme si la formule suivante était entrée dans les lignes du tableau (pour la deuxième ligne) = SOMMEPROD (F ((C $ 2: C2> A2))) / = SOMMAIRE (N ((2 $ C: C2> A2))), et étirez-vous sur l'ensemble du tableau.

2. La même chose, mais plus compliquée. En plus du début et de la fin, il y a le nombre d'unités ou la puissance donnée / reçue. Il est nécessaire de déterminer la puissance maximale à un moment donné.

Partie du tableau _t1:


Et la formule elle-même:

 =((((_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])) 

Puisque le temps est une fraction d'une unité, nous multiplions les valeurs par le nombre de minutes dans une journée pour obtenir des entiers. Et le deuxième argument est MULTIPLE - une colonne de valeurs de puissance. Dans le premier cas, une colonne unitaire a été générée.

3. À gauche du tableau se trouvent les montants correspondant à certains intervalles. Les montants mensuels doivent être calculés. On suppose que les montants sont répartis également dans leurs intervalles.


La colonne H est le premier jour du mois, la colonne I contient les formules suivantes:

 =(_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) 

Les formules sont massives. Vous pouvez utiliser SUMPRODUCT / SUMPRODUCT, mais IF ERROR ne fonctionne pas sans entrée massive. De plus, malheureusement, MAX et MIN ne peuvent pas former un tableau virtuel de valeurs. Par conséquent, IF est utilisé pour calculer des parties des intervalles par mois. Étant donné que les parties résultantes peuvent être négatives, une erreur est provoquée artificiellement (la racine carrée d'un nombre négatif y conduit). Dans le tableau source, il était possible de se passer de colonnes de durée d'intervalle et de montant journalier. La formule deviendrait un peu plus longue.

4. Un peu de plaisir. Combien de vendredis heureux peut-il y avoir en un an?


Nombre de vendredis:

 =(((((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)) 

Dates du vendredi:

 =((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)),"") 

Il est très bon (ici) que la première ligne de la feuille corresponde au 01/01/1901. Par conséquent, il est assez facile de former un tableau de dates de l'année en utilisant INDEX et STRING et de ne compter que le treizième vendredi.

5. La taille du taux d'intérêt, en fonction du montant, est assez courante. Si vous les utilisez incorrectement, le calendrier des montants reçus sera tel que le tableau rouge ci-dessous:


Pour éviter cela, vous devez appliquer le taux uniquement à une partie du montant par intervalle. Et pour des intervalles inférieurs, prenez des montants fixes.

Pour ces données sources:


Formule

 =(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,),)) 

La formule est massive. En A3, le montant pour lequel les intérêts doivent être calculés. Données initiales –tbl.
Et enfin, une ligne de crédit à taux d'intérêt variable. Formule de calcul du montant des intérêts en fonction des dates / montants des tranches / rendements.

Antécédents de crédit:


Modifier l'enchère:

 =(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))),) 

La formule est massive. Compte les intérêts par jours, prend en compte les années bissextiles.

Merci de votre attention!