Consolidar Datos en Excel (Data Consolidate)

Los ejemplos en esta nota fueron desarrollados usando la versión inglesa de Excel. Por favor, lean esta nota.

You can read this post in english here

A pesar de lo prometido, todavía no he instalado el castellano en mi PC. Así que tendrán que seguir viendo los ejemplos en inglés. Pero como lo prometido es deuda, espero que antes del fin de semana ya tenga el castellano instalado.
Ayer vino a verme uno de los muchachos del departamento de Ingeniería con el siguiente problema. Ellos llevan un registro mensual de la producción de piezas en un libro Excel. Para cada mes llevan una hoja dónde hay dos columnas: una muestra el número de catálogo de la pieza y la otra la cantidad de unidades producidas.

El problema es que quieren consolidar todos los datos en una sola hoja, con una columna para cada mes. Sucede que no todos los meses se producen los mismas piezas, por lo tanto las planillas no son idénticas.

La solución que habían pensado era producir una lista de valores únicos, como habían visto en mi nota, y luego usar la función VLOOKUP para traer los datos de cada mes a la columna correspondiente. El problema es que el libro se vuelve muy "pesado" con tanta fórmula (hay varios cientos de productos por mes) y el tiempo de recálculo es muy largo.

Mi propuesta fue que usaran la consolidación de datos (Data--->Consolidate), con lo cual resuelven los dos problemas de un golpe.

Se hace así:

Primer paso: en cada hoja mensual reemplazamos el encabezamiento "Qty" por el nombre del mes correspondiente

Segundo paso: abrimos una hoja en blanco y seleccionamos la celda A1. Abrimos el menú Data--->Consolidate

En la ventanilla "Function" elegimos "sum", activamos la hoja "Enero", seleccionamos las columnas A y B (pulsando la letra de la columna con el mouse) y pulsamos "Add"

Hacemos lo mismo con las hojas "Febrero" y "Marzo". En la ventanilla "Use labels in" señalamos "Top row" y "Left column"

Resistir toda tentación de marcar "Create links to source data"!!!

Pulsar OK y... voila!!

Excel produce en la hoja en blanco un informe dónde cada pieza aparece una sola vez, con una columna para cada vez. Si quisiéramos recibir los totales en una sola columna, basta con dejar el mismo encabezamiento para cada columna B en cada hoja (recordemos que nosotros reemplazamos esos encabezamientos con los nombres de los meses).

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: VLOOKUP, Data Consolidate, Consolidar datos en Excel

Categorías: Manejo de Datos_

Valor del argumento range_lookup en la función VLOOKUP

Los ejemplos en esta nota fueron desarrollados usando la versión inglesa de Excel. Por favor, lean esta nota.

En mi anterior nota sobre el uso de la función VLOOUKP, sugería utilizar siempre FALSE como valor del argumento range_lookup.



Utilizando FALSE (o cero), VLOOKUP buscará una correspondencia exacta. En caso de no encontrarla, el resultado de la función será un valor #N/A (error). Esto, que parece ser un inconveniente, es en realidad una ventaja ya que Excel nos está diciendo que no pudo encontrar el valor que estamos buscando. Cuando usamos VLOOKUP con range_lookup TRUE (o omitimos este argumento), en la mayor parte de los casos la función dará algún resultado, pero no podremos saber si éste corresponde exactamente a nuestra búsqueda, o si se trata de un resultado aproximado.
Sin embargo, hay ciertos cálculos en los cuales es necesario utilizar range_lookup con valor TRUE.
Como ejemplo he preparado esta hoja que nos permite calcular el porcentaje de comisión que le corresponde a un agente de ventas, de acuerdo al volumen de ventas que haya logrado en el período.

En nuestro ejemplo hemos dado al argumento range_lookup el valor TRUE, para obtener una búsqueda aproximado.

También es posible escribir esta función omitiendo el último argumento, en lugar de escribir TRUE.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: LOOKUP, VLOOKUP y HLOOKUP, range_lookup

Categorías: Funciones&Formulas_, LOOKUPS_

LOOKUP en Excel, VLOOKUP, HLOOKUP

Los ejemplos en esta nota fueron desarrollados usando la versión inglesa de Excel. Por favor, lean esta nota.

Últimamente he recibido varias consultas sobre el uso de la función VLOOKUP (BUSCARV). Gran parte de las preguntas provienen de mis compañeros de trabajo en mi compañía, pero no pocas llegan a mí a través de búsquedas en la Internet.
En mi blog en inglés ya he escrito sobre este tema, y bien vale que lo haga también en mi lengua materna.

Empecemos por decir que HLOOKUP funciona exactamente como VLOOKUP, con la diferencia que la primera realiza una búsqueda en forma horizontal, y de ahí su nombre, mientras la segunda lo hace en forma vertical. La explicación será en torno a VLOOKUP, pero es válida en todo sentido para HLOOKUP.

Usar VLOOKUP es como usar una guía de teléfonos. La guía de teléfonos es, básicamente, una tabla, una base de datos tabular. En la primer columna tenemos, por lo general, el nombre del abonado; en la segunda su dirección y en la tercera su número de teléfono. Cuando queremos averiguar el número de teléfono de alguien, buscamos su nombre el la primer columna y cuando lo hemos encontrado nos fijamos en la tercer columna.
La función VLOOKUP funciona exactamente así. Veamos su sintaxis:

• El argumento Lookup_Value es el nombre del abonado
• El argumento Table_Array es la página de la guía telefónica
• El argumento Col_index_num es el número de columna en la página de la guía teléfonica donde se encuentra el número de teléfono del abonado.

El cuarto argumento, Range_lookup, a pesar de ser opcional es muy importante. Si omitimos este argumento, o le damos un valor de 1, VLOOKUP dará como resultado el valor correspondiente al valor más aproximado en la lista de búsqueda, y esto a condición de que la lista esté ordenado en orden creciente. En castellano más sencillo: siempre poner a este argumento el valor 0 (cero) o FALSE.

Los remito al ejemplo que aparece en mi blog en inglés.

Un punto importante de recordar es que VLOOKUP siempre funciona de izquierda a derecha, y la primer columna en el rango señalado recibe el número 1, no importa si el rango comienza en la columna A o en la columna C.

Si te gustó esta nota anotala en del.icio.us

Technorati Tags: LOOKUP, VLOOKUP y HLOOKUP

Categorías: Funciones&Formulas_, LOOKUPS_