VBA es una cosa muy útil. Puede consolidar datos de muchos archivos y procesar grandes cantidades de información utilizando algoritmos interesantes. Por ejemplo, la
macro Nodupes . Utiliza el error que ocurre durante la ejecución del código como una verificación de la presencia de un elemento en la colección generada. Hay macros que usan la recursividad para formar todas las combinaciones y permutaciones posibles.
Pero me gustaría mostrar algunos ejemplos del uso de las funciones estándar de la hoja de cálculo de Excel.
Muy a menudo, debe cerrar el jardín de fórmulas para extraer el texto o los números deseados de las celdas. Esto generalmente ocurre debido a una presentación incorrecta de los datos. Los números son números, el texto es texto, no los mezcle. Las fechas (y la hora) son números con los que es posible y necesario hacer cálculos. No confunda el 14 de marzo de 2018 con el 14 de marzo de 2018. El segundo es solo un formato de fecha personalizado [$ -F800], lo que vemos en la celda, y en la barra de fórmulas será el 14 de marzo de 2018. Y lo suficiente sobre eso.
1. Suponga que tiene una tabla con las horas de inicio y finalización de algunos eventos, por ejemplo, conversaciones telefónicas.
En la imagen, parte de la tabla _t2. Es necesario calcular el número máximo simultáneo de eventos. Para esto hay una fórmula:
=(((_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 fórmula es masiva, la entrada se confirma presionando simultáneamente los botones Control, Shift, Enter (CSE). La barra de fórmulas mostrará que la fórmula está entre llaves. MUMNOZH crea una matriz virtual del tamaño del número de filas de la tabla por el número de filas de la tabla, en las filas de las cuales se ingresan los resultados del cálculo, como si la siguiente fórmula se ingresara en las filas de la tabla (para la segunda fila) = SUMPRODUCT (F ((C $ 2: C2> A2))) / = SUMPRODUCT (N ((C $ 2: C2> A2))), y estirar a toda la tabla.
2. Lo mismo, pero más complicado. Además del principio y el final, existe el número de unidades o la potencia dada / recibida. Es necesario determinar la potencia máxima en algún momento.
Parte de la tabla _t1:
Y la fórmula misma:
=((((_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]))
Como el tiempo es una fracción de una unidad, multiplicamos los valores por la cantidad de minutos en un día para obtener enteros. Y el segundo argumento es MÚLTIPLE: una columna de valores de potencia. En el primer caso, se generó una columna de unidad.
3. A la izquierda de la tabla están las cantidades correspondientes a ciertos intervalos. Se deben calcular los montos mensuales. Se supone que las cantidades se distribuyen de manera uniforme dentro de sus intervalos.
La columna H es el primer día del mes, la columna I contiene las siguientes fórmulas:
=(_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)
Las fórmulas son masivas. Puede usar SUMPRODUCT / SUMPRODUCT, pero IF ERROR no funciona sin una entrada masiva. Además, desafortunadamente, MAX y MIN no pueden formar una matriz virtual de valores. Por lo tanto, IF se usa para calcular partes de los intervalos por mes. Como las partes resultantes pueden ser negativas, se produce un error artificialmente (la raíz cuadrada de un número negativo conduce a él). En la tabla de origen, fue posible prescindir de columnas de duración de intervalo y cantidad diaria. La fórmula se alargaría un poco.
4. Un poco divertido. ¿Cuántos viernes felices puede haber en un año?
Numero de viernes:
=(((((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))
Viernes Fechas:
=((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 muy bueno (aquí) que la primera línea de la hoja corresponda al 01/01/1901. Por lo tanto, es bastante fácil formar una serie de fechas del año usando INDEX y STRING y contar solo el 13 de viernes.
5. El tamaño de la tasa de interés, dependiendo de la cantidad, es bastante común. Si los usa incorrectamente, entonces el cronograma de los montos recibidos será como el cuadro rojo a continuación:
Para evitar esto, debe aplicar la tasa solo a una parte de la cantidad por intervalo. Y para intervalos más bajos, tome cantidades fijas.
Para tales datos de origen:
Formula
=(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 fórmula es masiva. En A3, la cantidad por la cual se calcularán los intereses. Datos iniciales –tbl.
Y finalmente, una línea de crédito con una tasa de interés variable. Fórmula para calcular la cantidad de interés dependiendo de las fechas / cantidades de tramos / devoluciones.
Historial de crédito:
Cambiar oferta:
=(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 fórmula es masiva. Cuenta los intereses por días, tiene en cuenta los años bisiestos.
Gracias por su atencion!