Imagen: Aajan/iStock/Getty Images Plus

Nadie quiere saltar a través de aros para hacer el trabajo, pero a veces una solución simple puede eludirnos. Si la respuesta no es rápida y fácil, a menudo recurrimos a funciones de matriz complejas o código pensando que no hay otra manera. Un buen ejemplo es intentar devolver el último valor de una columna, de forma condicional, porque la fila correspondiente no siempre será la última fila. Lanzar esta condición en la mezcla parece hacer las cosas mucho más difíciles. En este artículo, te mostraré algunos métodos simples que funcionan, pero no son soluciones superiores. A continuación, te mostraré una solución más dinámica que solo requiere un poco más de trabajo.

Uso Microsoft 365 en una solución de Windows 10 de 64 bits, pero puede usar versiones anteriores. Puede trabajar con sus propios datos o descargar el archivo de demostración .xlsx y los archivos .xls. Sin embargo, la versión del menú no admite el objeto Tabla necesario en la sección Dinámica. Este artículo asume que tiene conocimientos básicos de Excel, como filtrar e ingresar funciones. La edición del navegador es compatible con todo lo que se incluye en este artículo.

VER: Guía del usuario avanzado de Excel (Premium de TechRepublic)

Índice
  • Define el término ultimo
  • Simple, pero no dinámico.
  • Dinámica
  • Define el término ultimo

    Sabemos cuál es la palabra ultimo significa, pero es importante definirlo en el contexto de los datos. ¿Estamos hablando del último registro coincidente en el conjunto de datos? ¿Queremos decir la última fecha? ¿O nos referimos al valor mínimo? Mirando nuestro conjunto de datos simple, puede ver que la respuesta a las tres preguntas podría ser sí (Figura A).

    Figura A

    En la práctica, podemos eliminar los valores mínimos y las fechas, porque estaría buscando un valor mínimo, y ese no es realmente el mismo que el último valor. Eso nos deja con el último registro que coincide con la condición.

    Simple, pero no dinámico.

    Tenemos dos proveedores, Acme y ABC, Inc., y asumimos que queremos devolver la fecha y el monto de la factura para el último registro en el conjunto de datos de cada empresa. Podrías pensar en BUSCARV(), pero esta función devuelve el primer valor coincidente. No hay forma de forzar a la función a encontrar el último valor coincidente.

    Si controla los datos, tiene una solución simple: invierta los registros y use BUSCARV(), que con un conjunto de datos invertido encontrará el último (primer) registro. La forma más fácil de invertir el orden de los registros es llenar una columna auxiliar con un conjunto de números consecutivos (Figura B).

    Figura B

    A continuación, agreguemos las funciones BUSCARV() para devolver la fecha y la factura (ver Figura B). Hay dos funciones de búsqueda y la única diferencia entre ellas es la columna de retorno:

    G3: =BUSCARV($F3,$B$3:$D$18,3,FALSO)

    H3: =BUSCARV($F3,$B$3:$D$18,2,FALSO)

    Copie las funciones de la línea 3 a la línea 4. Actualmente, estas funciones devuelven el primer registro coincidente para ambas empresas. Para obtener el último, invierta el conjunto de datos realizando una ordenación descendente en la columna auxiliar que agregó. Figura C muestra los resultados; ahora las funciones VOOKUP() devuelven los valores del "último" registro coincidente. Es una solución simple, pero no es dinámica; tienes que pensar en la clasificación, que no es lo ideal.

    Figura C

    Un filtro simple le dará una visión general rápida (Figura D), si eso es todo lo que necesita. Para hacer esto, haga clic en cualquier parte del conjunto de datos, luego haga clic en la pestaña Datos. En el grupo Ordenar y filtrar, haga clic en Filtrar. Luego, use la lista desplegable de la columna Proveedor y seleccione solo una empresa a la vez.

    Figura D

    Dinámica

    Las soluciones de BUSCARV() y filtro son solo soluciones parciales: no es suficiente recordar ordenar o simplemente obtener una pista visual. Podemos combinar las dos soluciones parciales para crear una buena que sea dinámica y no requiera ninguna acción adicional de su parte además de filtrar el conjunto de datos. Con esta solución, no puede obtener ambos conjuntos de datos al mismo tiempo, pero puede encontrar que funciona mejor para usted que las soluciones anteriores.

    Agregué una fila de filtro sobre el conjunto de datos y convertí el conjunto de datos en un objeto Tabla con una fila de Totales (Figura E). Para crear la tabla, haga clic en cualquier celda del rango de datos, presione Ctrl+t y haga clic en Aceptar. Para agregar la fila Totales, haga clic en la cinta contextual Diseño de tabla y marque la opción Fila total en el grupo Opciones de estilo de tabla. Finalmente, haga clic dentro de la celda Columnas de orden total (en la hoja de muestra, es A20) y seleccione Max de la lista desplegable. Esto devuelve el número máximo en esa columna. Recuerde que la versión del menú no admite el objeto Tabla.

    Figura E

    Para crear la línea de filtro, agregue las funciones de la siguiente manera:

    A1:=Tabla2[[#Totals],[Order]]

    B1: =BUSCARV($A$1,Tabla2,2,FALSO)

    C1:=BUSCARV($A$1,Tabla2,3,FALSO)

    D1:=BUSCARV($A$1,Tabla2,4,FALSO)

    Como antes, la función BUSCARV() siempre encuentra el primer valor coincidente en las columnas respectivas. La principal diferencia es que la referencia a la tabla de búsqueda es una referencia a Table2 (la tabla que creamos). La expresión en A1 se refiere al valor en la fila de totales de la columna Orden. Este valor es el resultado de una función SUBTOTAL() especial que devuelve el valor máximo en el filtrado Posición.

    En otras palabras, el valor de búsqueda para la función BUSCARV() es el valor en A20, el valor más grande en el conjunto filtrado. Por tanto, la función devuelve los valores correspondientes de esa fila.

    Sin un filtro en su lugar, la fila del filtro siempre devuelve la última fila de valores. Ahora veamos qué sucede cuando filtramos por empresa. Para hacer esto, elija solo una empresa a la vez de la lista desplegable de la columna de proveedores. Como puedes ver en Figura Fel conjunto filtrado para Acme devuelve los mismos valores que un conjunto sin filtrar porque la última fila del conjunto de datos es un registro de Acme.

    Figura F

    figura g muestra los resultados del filtro para ABC, Inc. Esta vez, los datos cambian para reflejar el último registro de este proveedor.

    figura g

    Todavía necesita filtrar el conjunto para obtener los resultados correctos en la fila A, pero eso es mucho más fácil de recordar que la actividad de clasificación de la solución anterior.