viernes, 2 de septiembre de 2011

Ú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))}

7 comentarios:

  1. Excelente amigos!!, nada mas que si usan la versión de excel en ingles el separador logico es ' no ; por ejemplo: {=MAX(IF((D8:O8)<>"",COLUMN(D8:O8)))-3}, Mil gracias por sus buenos aportes.

    ResponderEliminar
  2. En el paso 1, yo preferiría ocultar los numeros del 1 al 12 en el rango D5:O5 (donde están los meses) y ocultarlos escribiendo en Formato Personalizado el mes que corresponde.

    ResponderEliminar
  3. Hola Shinseiki.

    He añadido al final del post una actualización para evitar el paso 1 del proceso.

    Lo de escribir los meses a mano es una posible solución, aunque tiene el inconveniente de tener que escribir los 12 meses a mano, uno a uno.

    Gracias por tu comentario.

    Un saludo.

    ResponderEliminar
  4. Buenas Adolfo: En primer lugar felicitarte por tu gran trabajo; por fin he logrado suscribirme a tu magnífico blog.

    Me gustaría me dieras una solución a este mismo caso, pero si fuesen sumas acumuladas de columnas alternas (por ejemplo si entre las columnas de los meses hubiese una columna de % de crecimiento) y quiero sumar parcialmente el importe de los meses.

    He intentado adaptar la fórmula {=SUMA((D3:O3)*(($D$1:$O$1)<=$Q$1))}, sustituyendo la matriz D3:O3 y $D$1:$O$1 por las celdas alternas, pero no me ha funcionado.

    Gracias de antemano por tu ayuda. Saludos.

    ResponderEliminar
  5. Buenas de nuevo:

    Tengo otro pequeño problema; si aplico la fórmula para sumar valores vinculados de otras hojas que actualmente estén en blanco (es decir que aún no tienen cantidades)me da error de #¡VALOR!. Entiendo que será porque no reconoce como número la vinculación de la celda con la otra hoja.

    ¿Como puedo solucionarlo?

    Gracias por anticipado por vuestra ayuda.

    ResponderEliminar
  6. Hola.
    Prueba a usar nombres de rango.
    Un saludo.

    ResponderEliminar
  7. Una consulta. Para extraer los dos últimos valores de una fila, en el caso de que pueda haber celdas en blanco, ¿cómo sería?. La obtención del último me sale ok, pero el penúltimo, me saca un cero si hay celdas en blanco por el medio.

    Un abrazo y muchas gracias por su gran labor.
    Carlos

    ResponderEliminar