Usar Excel BUSCAR (LOOKUP) para encontrar el último valor en un rango

Usar Excel BUSCAR (LOOKUP) para encontrar el último valor en un rango

Últimamente he estado participando en el foro Excel de http://www.exceluciones.com

Una de las preguntas que han surgido allí, y que aparecen a menudo en varios foros, es qué fórmula nos permitiría encontrar el último valor de un rango. La idea es que tenemos, por ejemplo, un rango en una columna donde vamos agregando valores, por ejemplo el rango A1:A20. Si agregamos un valor en la celda A21, queremos una fórmula que de cómo resultado el valor de A21.
La solución "clásica" es utilizar la función BUSCAR (LOOKUP), poniendo como argumento el número más elevado que Excel acepta en una celda (al valor 1E+307) y como segundo argumento el rango donde debemos encontrar el último valor (hay que prestar atención que estamos buscando el último valor en el sentido de orden y no de tamaño).
El funcionamiento de la función BUSCAR (LOOKUP) es tal que si el valor buscado es mas alto que cualquier valor disponible en el rango de la búsqueda esta función da como resultado el ultimo valor que encuentren.

En este ejemplo vemos que cuando el valor del primer argumento (1000) es mayor que el valor del último valor en el rango (172), la fórmula da como resultado 172. Si el valor del primer argumento de BUSCAR (LOOKUP) es, por ejemplo 126, el resultado de la fórmula será 118.

LOOKUPS_

Technorati Tags: BUSCAR, LOOKUP

Función JERARQUIA (RANK) en Excel – Nota adicional

En una entrada anterior he mencionado como usar la función JERARQUIA (RANK en la versión inglesa). Allí dimos la siguiente definición:

JERARQUIA (RANK): Devuelve la jerarquía de un número en una lista de números. La
jerarquía de un número es su tamaño en comparación con otros valores de la
lista. (Si ordenara la lista, la jerarquía del número sería su posición).

Pero a veces hay situaciones en las cuales dos miembros de la lista reciben el mismo ranking, como en este caso




Como podemos ver hay "empate" entre las celdas C5 y C6. Ambas reciben el ranking 14, y el 15 no existe.

Si queremos que en caso de "empate" el ranking se establezca por orden de aparición, podemos utilizar la siguiente fórmula:

=JERARQUIA(B4,sales)+CONTAR.SI($C$4:C4,C4)-1

Si copiamos esta fórmula en todo el rango de la columna D, veremos este resultado




En nuestra fórmula, la función CONTAR.SI (COUNTIF) evalúa cuantas veces aparece el número de ranking de la celda en el rango. Si aparece sólo una vez, el resultado de CONTAR.SI será 0 y el resultado será idéntico al de la fórmula original. Si el número de ranking ya figura en el rango, el resultado de la fórmula será incrementado en 1 por CONTAR.SI

El cuaderno con el ejemplo se puede descargar aquí


Categorías: Funciones&Formulas_,

Technorati Tags: RANK, JERARQUIA

Consolidación de datos con Tablas Dinámicas (Pivot Tables).

En anteriores entradas ya he tratado el tema de consolidación de datos en Excel:
1 – Consolidar Datos con Excel
2 – Consolidar datos con Subtotales
3 - Comparar listas con consolidación de datos

Otro método de consolidar datos en Excel es usando tablas dinámicas (pivot tables).
Consideremos el siguiente ejemplo (para descargar el archivo con el ejemplo apretar aquí). Tenemos un cuaderno Excel con una hoja por cada agente de ventas. En cada una de estas hojas anotamos las ventas por mes y por producto de cada agente.

Para consolidar las ventas de todos los agentes (en nuestro caso habrá sólo dos) en una sola hoja de cálculo usando tablas dinámicas, procedemos de la siguiente manera:
1 – Abrimos una hoja en blanco (en nuestro ejemplo será "consolidado") y activamos el menú Datos--->Informe de Gráficos y Tablas Dinámicos. En el diálogo que se abre elegimos la opción "rangos de consolidación múltiples"



2 – En el siguiente paso debemos instruir a Excel cuantos campos de página queremos. Para nuestro ejemplo elegimos "crear un solo campo de página"



3 – En el tercer paso debemos informar a Excel dónde se encuentran los rangos que contienen los datos. Hacemos esto apunto a los rangos en las hojas adecuadas (agente1 y agente2) y apretando el botón "agregar"



4 – Finalmente debemos decirle a Excel dónde ubicar la tabla dinámica (que consolida los datos)- En nuestro ejemplo la ubicamos en la celda A6



Apretamos "finalizar" y obtenemos la tabla dinámica.

Esta tabla muestra los totales para cada producto y por cada mes de todos los agentes de ventas. Si queremos ver los datos de un agente en particular apretamos el botón "Página 1" y elegimos entre "elemento 1" (agente 1) y "elemento 2" (agente 2).

Dado que los encabezamientos de las columnas tienen un formato de fecha, podemos agrupar los datos en trimestres en forma automática. Para hacer esto damos los siguientes pasos:
1 – Hacemos clic con el botón derecho del mouse en el encabezamiento del campo "columna", y apretamos "agrupar"



2 – Seleccionamos "trimestres" y cancelamos la selección de "meses" (con un clic del mouse).



3 – Apretamos "aceptar" y la tabla se convierte en un reporte trimestral




Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Consolidación de Datos, Tablas Dinámicas