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

16 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
  8. Y como hacerlo de forma vertical? ya intente varias formas y nada.... ayuda

    ResponderEliminar
    Respuestas
    1. Hola.
      Para hacer el ejemplo de la Hoja2 por columunas, sigue estos pasos:
      1. Abre una hoja nueva, por ejemplo la Hoja 3.
      2. En la Hoja2 selecciona el rango B4:Q10
      3. Copia ese rango al portapapeles, por ejemplo con CONTROL+C
      4. Vas a la nueva, que es la Hoja3 y pegas el rango seleccionado en la celda B2, pero no haces un pegado normal sino que eliges pegado especial transponer.
      5. Edita la celda D3 de la Hoja3, y cambia la palabra COLUMNA por la palabra FILA. La fórmula debe quedar así: =MAX(SI((D4:D15)<>"";FILA(D4:D15)))-3
      6. No valides pulsando ENTER, ya que se trata de una fórmula matricial, debes validar pulsando simultáneamente 3 teclas: CONTROL+SHIFT+ENTER
      7. Copia la celda que has modificado que es la D3 y la copias a las celdas que tiene a su derecha que son las celdas del rango E3:H3
      8. Optativo: oculta la fila 3.
      Y ya está.
      Un saludo.

      Eliminar
  9. Adolfo, Gracias la formula es muy bueno, pero si en ves del valor, quiero que me de la ubicación de del ultimo valor, que cambios debo hacer

    ResponderEliminar
    Respuestas
    1. Hola Manuel.
      Tomemos como ejemplo la Hoja1 del fichero ultimo.xlsx

      En la celda L10 pon la siguiente fórmula matricial:

      ="F"&FILA(B10)&"C"&MAX(SI((B10:H10)<>"";COLUMNA(B10:H10)))

      Ya sabes que para validar una fórmula matricial no se ha de pulsar Enter, sino que se han de pulsar tres teclas a la vez: Control+Shift+Enter

      Copia esa fórmula hacia abajo y te dará la Fila y la Columna del último valor escrito en cada fila.

      En la celda M10 escribe la siguiente fórmula:

      =INDIRECTO(L10;0)

      Y luego la copias hacia abajo. Esto te dará el ULTIMO valor escrito en cada fila.
      Un saludo.

      Eliminar
  10. he realizado el ejemplo pero el lugar de darme el ultimo valor me arroja la sumatoria

    ResponderEliminar
  11. HOLA, COMO OBTENGO EL ULTIMO VALOR DE UNA COLUMNA DEPENDIENDO DE UN RANGO DE FECHAS.
    Columna A: tiene fechas desde el 01/01/2017 hasta el 31/03/2014
    Columna B: tiene importes
    las fechas de la columna A se se repiten, es decir puede tener varias veces la misma fecha.
    De antemano agradezco tu ayuda.
    Saludos. Ricardo

    ResponderEliminar
  12. Hola estimado:

    Para obtener el último valor de una fila, con la fórmula que han dado solo me aparece "#¡VALOR!", alguien me puede dar una mano?

    Saludos cordiales,
    Osvaldo Rocha

    ResponderEliminar
  13. Buenas Tardes,

    Necesito ayuda; tengo un archivo excel de costos, en el cual pongo el nombre del prooveedor en la primera columna en la segunda coloco el articulo y las siguientes tengo la fecha diaria por si por dia llega ese articulo a un nuevo costo quisiera que al final me diera el ultimo costo por articulo con su fecha sin tener que buscarlo por todo el archivo.

    Me ayudas??

    ResponderEliminar
  14. Adolfo, muchas gracias por la explicación, me ha sido de mucha ayuda.

    ResponderEliminar