Último valor de la fila

Descargar el fichero: ultimo.xlsx

Puede llegar a se muy útil determinar cuál es el último valor de una fila o columna. Para lograrlo utilizaremos funciones matriciales. En esta ocasión veremos dos hojas en las que planteamos dos casos en los que se emplean estas fórmulas matriciales que calculan el último valor escrito en una fila.

Hoja1

Veamos cómo se calcula con una fórmula matricial el último valor de una fila.

La celda J16 es:

{=INDICE((B16:H16);;MAX(SI((B16:H16)<>"";COLUMNA(B16:H16)))-1}

Recuerde que las llaves {} que rodean la fórmula indican que se trata de una función matricial y el usuario no ha de escribirlas, ya que de ello se encarga Excel al validar la fórmula pulsando CONTROL+MAYUSCULAS+INTRO.

Puede ampliar información sobre las funciones matriciales en el siguiente enlace: Fórmulas Matriciales.

También puede consultar el penúltimo o anteúltimo valor de la fila.

Para ello en la celda K16 empleamos la fórmula matricial siguiente:

{=INDICE((B16:H16);K.ESIMO.MAYOR(((B16:H16)<>"")*(COLUMNA(B16:H16));2)-1)}

La función K.ESIMO.MAYOR y la función K.ESIMO.MENOR se utilizan mucho en este tipo de cálculos.

Hoja2


Veamos una aplicación práctica de las ideas recogidas en la Hoja1. Disponemos de las ventas mensuales durante los últimos años. Actualmente nos encontramos en el año 2011 y el último mes del que tenemos información es Julio. Esto se puede observar viendo la tabla de datos.

Con el objeto de poder comparar adecuadamente los datos acumulados de los meses que llevamos de 2011 con los de años anteriores, lo que deseamos es crear la columna Q en la que obtengamos el acumulado de las ventas de todos los años pero únicamente hasta el mes de julio, que es el último mes del que tenemos información en el año 2011.

Lo importante es que deseamos que esto se recalcule de forma automática y que cuando sea agosto el último mes del que tengamos información automáticamente sea éste el mes hasta el que se acumulen en la columna Q los valores de ventas de todos los años.

Para conseguir esto hemos tenido que dar una serie de pasos.

Paso 1

En el rango D4:O4 hemos creado la serie de números del 1 al 12. Para que no se vean hemos utilizado un truco que consiste en dar formato a esas celdas del tipo formato número, personalizado y en tipo indicar "". Esto lo que hace es evitar que el valor de esa celda se vea.

Paso 2

La columna C se encuentra oculta. Esta es una columna auxiliar necesaria. En ella lo que calculamos es el último valor escrito en la fila. Así en la celda C8 obtenemos el valor 7 ya que el último valor escrito en el rango D8:O8 es el del mes de julio. La fórmula matricial de la celda C8 es la siguiente.

{=MAX(SI((D8:O8)<>"";COLUMNA(D8:O8)))-3}

Paso 3

En la celda Q4 escribimos la siguiente fórmula matricial.

{=+MIN(SI((C6:C10)>0;(C6:C10)))}

Esta fórmula calcula el último mes escrito que en nuestro caso es el mes de julio. En la celda figura el valor 7, que se corresponde con julio. El número 7 no se ve ya que hemos utilizado en la celda un formato de número personalizado indicando en tipo "

Paso 4

En la celda Q5 deseamos que aparezca una indicación del mes hasta el que se acumula. En este caso vemos que pone "Hasta julio", esto se consigue con la siguiente fórmula.

=FECHA(2011;Q4;1)

Paso 5

En la celda Q6 tenemos la fórmula matricial siguiente.

{=SUMA((D6:O6)*(--(($D$4:$O$4)<=$Q$4)))}

De esta forma, conseguimos que, de forma automática, al ir escribiendo los datos de los sucesivos meses, los acumulados se adapten y muestren la cifra de ventas, justo hasta el mes último del que tenemos datos en el años actual.


Actualización (a 4 sep. 2011)

Para evitar el paso 1, evitando escribir en la fila 4 los números de 1 al 12, y luego ocultarlos, vamos a proponer un cambio en la fórmula de la celda Q6.

{=SUMA((D6:O6)*({1;2;3;4;5;6;7;8;9;10;11;12}<=$Q$4))}