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_

Paste Special-Values, de fórmulas a valores estáticos

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

Como ya he dicho alguna vez, casi todo el material que aparece en este blog está basado en preguntas que recibo a diario de mis compañeros de trabajo.
La de hoy es una cuestión básica, pero supongo que será útil para muchos de los usuarios de Excel no tan experimentados.
Supongamos la siguiente situación: hemos importado del mainframe una lista de personas dónde el nombre propio aparece en una columna y el apellido en otra. Por algún motivo necesitamos unificar ambos datos en una sola columna.

Para lograr esto usaremos el operador "&". Escribimos en la celda C2 la siguiente fórmula:

=A2&" "&B2

y la copiamos al resto del rango correspondiente en la comuna C

Ahora yo no tenemos necesidad de la columnas A y B. Pero antes de borrarlas debemos convertir las fórmulas en la columna C en valores. Para hacer esto seleccionamos el rango C2:C6, pulsamos Ctrl+C para copiar, y luego usamos el menú Edit--->Paste Special--->Values

Una vez que las funciones en la columna C han sido convertidas a valores estáticos, podemos eliminar las columnas A y B.

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

Technorati Tags: paste special valores, unir nombres en Excel, operador &

Categorías: Varios_

Seleccionando celdas con valor #N/A en Excel

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

Lo prometido es deuda, decía mi abuelita. En una nota anterior prometí mostrar otros usos del Go To Special (y otra vez mil disculpas por los menúes en inglés)

Veamos qué uso interesante se le puede dar a "Formulas --->Errors", combinada con la función MATCH.

Uno de los métodos para comparar dos listas, distinto del que ya he mostrado en otra nota, es utilizar la función MATCH. Esta función busca si un miembro de una lista se encuentra entre los miembros de otra lista o rango determinado. En caso de no encontrarlo, la función muestra como resultado el valor #N/A.

Supongamos que deseamos encontrar qué nuevos clientes hay en un informe de ventas

en comparación con esta lista:

Como podemos ver claramente, los clientes Customer 3, Customer 6 y Customer 9, son nuevos.

En la columna C del informe de ventas copiamos la siguiente formula:

=MATCH(A3,Customers_List,FALSE)

como pueden ver estoy usando nombres /names (Customers_List) como argumento en la función. Éste es otro tema sobre el cual prometo escribir en el futuro.

El resultado será el siguiente

Todas la celdas que aparecen con el valor #N/A indican la presencia de nuevos clientes. Para reemplazar de un golpe todo los valores #N/A lo que hacemos es

1. seleccionamos la columna C, o el rango específico en la columna C
2. abrimos el menú Go To Special (pulsar F5 o Ctrl+G)
3. marcamos las opciones "Formulas" y "Errors"
4. y apretamos OK

Todas las celdas con valor #N/A serán seleccionadas. Escribimos la frase "cliente nuevo" y apretamos Ctrl+Enter, para introducir este valor en todas las celdas selccionados de una vez.

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

Tags: celdas con valor #N/A, comparar dos listas, función MATCH

Categorías: Varios_

Promedio ponderado usando SUMPRODUCT

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

En mi nota de ayer mostré cómo calcular un promedio ponderado usando fórmulas matriciales (array formulas). Existen otros métodos para hacer este cálculo, uno de ellos es usando la función SUMPRODUCT.
La función SUMPRODUCT multiplica entre si los miembros correspondientes de dos o más matrices (vectores) y suma el resultado de estas multiplicaciones.
En nuestro ejemplo de ayer queríamos calcular la edad promedio de los miembros de esta lista:

Si usamos la función SUMPRODUCT, el primer vector será el rango A2:B12 (la edad) y el segundo vector rl rango B2:B12 (el número de miembros para cada edad). La suma del producto de los vectores lo dividimos por el número total de miembros en la lista. La fórmula será:

=SUMPRODUCT(A2:A12,B2:B12)/SUM(B2:B12)

La función SUMPRODUCT permite realizar cálculos complicados con gran facilidad, tema sobre el cual escribiré en un futuro cercano.

Si te gustó esta nota agregala a del.icio.us

Technorati Tags: promedio ponderado, SUMPRODUCT

Categorías: Funciones&Formulas_, Formulas Matriciales_

Fórmulas matriciales (array functions) en Excel

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

En una nota en mi blog en inglés he mencionado el uso de funciones matriciales (array formulas). La mejor forma de comprender el concepto es con un ejemplo.
Supongamos que tenemos una lista que muestra el numero de miembros de una institución por edad:

Si queremos saber la edad promedio de los miembros (es decir, un promedio ponderado) y no conocemos las fórmulas matriciales, tendremos que usar cálculos intermedios para llegar al resultado. Lo que seguramente haremos es multiplicar cada edad por el número de miembros en la columna B, luego sumar los productos y dividirlos por la suma de la column B:

Utilizando fórmulas matriciales podemos hacer todos estos cálculos en un solo paso. En nuestro ejemplo el rango A2:A12 será la matriz1 y el rango B2:B12 la matriz2. La fórmula matricial nos permitirá multiplicar cada miembro de la matriz1 por el correspondiente miembro de la matriz2, y luego dividir la suma delproducto por la suma de la columna B.

La fórmula es la siguiente:

{=SUM(A2:A12*B2:B12)/SUM(B2:B12)}

Los signos "{" al principio de la fórmula y "}" al final no se deben introducir manualmente! Para generar una fórmula matricial debemos mantener apretadas las teclas Ctrl y Shift (mayúsculas) y pulsar entonces Enter (de aquí que tambien son conocidas como fórmulas CSE).

Fijense en la fórmula en la barra de las fórmulas:

Las fórmulas matriciales son una de las herramientas más poderosas de Excel y adquirir dominio del concepto nos permitirá realizar cálculos complejos con facilidad.

En el futuro estaré escribiendo sobre otros usos de estas fórmulas.

(22/04/2006): he agregado una entrada sobre fórmulas matriciales multicelulares y constantes matriciales.

Si te gustó esta nota agregala a del.icio.us

Technorati Tags: promedio ponderado, fórmulas matriciales

Categorías: Funciones&Formulas_, Formulas Matriciales_

Cómo comparar dos listas en Excel

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

Otra tarea bastante común en Excel es comparar entre dos listas. El objetivo es encontrar qué diferencias hay entre dos listas (que personas de una lista no aparecen en la otra, por ejemplo). Este tipo de comparación se puede hacer de varias maneras. En esta nota mostraré cómo hacerlo usando Conditional Formatting (Data ---> Conditional Formatting).
Supongamos esta hoja

Para señalar los faltantes en cada lista, daremos un fondo de color azul a los nombre de la lista 1 que no aparecen en la lista 2 y un fondo de color rosa a los nombre de la lista 2 que no aparecen en la lista 1.

Procedemos de la siguiente manera:

1. seleccionamos el rango de los nombres en la lista 1 (A2:A10)
2. en la barra del menú pulsamos Format ---> Conditional Formating
3. seleccionamos Formula Is
4. en la ventanilla escribimos la formula =COUNTIF($B$2:$B$10,A2)=0 (prestar atención al signo $ en la fórmula)
5. apretar el botón Format, seleccionar Pattern y elegir el color azul.

Ahora hacemos lo mismo con la segunda lista (el rango será B2:B10) y utilizamos la fórmula

=COUNTIF($A$2:$A$10,B2)=0

Apretamos OK

Ahora podemos ver claramente quien falta en cada lista.

Otra técnica es utilizar la fórmula =MATCH, lo que mostraré en alguna futura nota.

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

Tags: comparing two lists, comparar dos listas, conditional formatting

Categorías: Manejo de Datos_

Como construir una lista de valores únicos en Excel

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

Un uso frecuente es importar reportes del mainframe a Excel para luego elaborar allí los datos. Excel tiene muchas ventajas en este terreno sobre todos los sistemas centrales y esta es una de las razones principales de su éxito.
Supogamos que importamos a Excel un reporte de las ventas del período. Este informe tiene cuatro columnas: número de factura, nombre del cliente, fecha y monto. El informe tiene, supongamos, 12.000 líneas. Queremos saber cuáles son los clientes qué aparecen en el informe, es decir obtener una lista donde el nombre de cada cliente aparezca una sola vez.
Una solución es utilizar una tabla pivot. Pero digamos que no estamos familliarizados con esta técnica. Otro camino de obtener esta lista es utilizar el menú Data--->Filter--->Advanced Filter.
Nuestro informe una vez importado a Excel se ve así

Abrimos el menú Data--->Filter--->Advanced Filter

Ahora hay que hacer lo siguiente:

1. Marcamos la opción "copy to another location"
2. Seleccionamos el rango relevante en la ventanilla "list range", en nuestro ejemplo B1:B21
3. Marcamos "unique records only"
4. En la ventanilla "copy to" seleccionamos una celda en la misma hoja (no se puede copiar la lista directamente a otra hoja) por ejemplo F1

Apretamos OK y en la columna F aparecerá la lista con los nombres

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

Technorati Tags: valores únicos, advanced filter

Categorías: Manejo de Datos_, Varios_

Como seleccionar celdas en blanco solamente

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

Esta pregunta pertenece a la familia de problemas que es posible resolver con facilidad si sabemos utilizar la opción Go To Special (ya alguno de mis lectores me dirá como lo han traducido al castellano). Esta opción se hace visible apretando la tecla F5 o la combinacion Ctrl+G

Si apretamos el botón "special" se abre el siguiente menú

Como puede verse hay aquí varias opciones. Supongamos que tenemos la siguiente tabla

Digamos que queremos utilizar esta lista como base para una tabla pivot. Si éste es el caso tenemos que llenar las celdas en blanco de la primer column con los nombres correspondientes. Es decir, A3 y A4 tiene que contener el nombre "Peter", A6 y A7 "Paul" y así sucesivamente. Supongamos también que la lista tiene 6000 líneas, lo cual convierte la tarea de hacerlo manualmente en un muy dudoso placer.

Aquí es dónde GoTo Special viene en nuestra ayuda. En primer lugar hay que seleccionar el rango relevante, en nuestro caso de A2 hasta A10. Luego hay que pulsar F5 (o Ctrl+G)

apretamos el botón "special" y seleccionamos "blanks"

apretamos OK

podemos ver que sólo las celdas en blanco han sido seleccionadas. Ahora anotamos el signo "=" (sin las comillas) en la celda A3, apuntamos a la celda inmediata superior (A2 en nuestro ejemplo) de manera que en la barra de fórmulas veremos "=A2", y seguidamente apretamos Ctrl+Enter. Esto hace que el valor en cada celda en blanco sea el de la celda que se encuentra inmediatamente por encima de ella.

GoTo Special tiene muchos otros usos que iremos describiendo en futuras notas.

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

Tags: celdas en blanco, pivot table

Categorías: Varios_

Sumas de tiempo con Excel

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

Hace unos días atrás se aparece en mi oficina Mario con una extraña sonrisa en los labios. Ché, me dice (a pesar de estar en el Medio Oriente, Mario sigue siendo porteño), vos que sabés tanto de Excel explicame ésto. Me muestra un tabla donde calculaba las horas que había trabajado (los ejemplos son en inglés ya que están tomados de mi blog en ese idioma):

Excel muestra un total de 12 horas y 15 minutos cuando en realidad tendría que ser 36 horas y quince minutos!

Paso a explicar. Si no se le dice lo contrario, Excel muestra los totales de sumas de tiempo como partes del día. Lo que quiere decir que cada vez que la suma llega a 24, Excel empieza a sumar nuevamente de cero. Para "obligar" a Excel a sumar en forma correcta hay que cambiar el formato de las celdas a la forma [h]:mm.

Esto se hace de la siguiente forma (nuevamente me disculpo por el inglés):

En nuestro ejemplo seleccionamos la celda D7, abrimos el menú de formado, elegimos la opción "custom" y en la ventanilla correspondiente escribimos la fórmula [h]:mm (este formato estará disponible ahora para todas las celdas de la planilla).

Todo lo que queda por hacer es apretar OK

Si te gustó este post anotalo en del.icio.us

Technorati Tags: suma de tiempo en excel, calculos de tiempo

Categorías: Varios_

Cómo encontrar la intersección de dos líneas en un gráfico en Excel

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

Esta es una de las preguntas más interesantes que me han hecho mis colegas del trabajo en los últimos tiempos. Dadas dos series de datos es muy fácil construir un gráfico en Excel. Supongamos que el gráfico nos permite ver que las dos líneas se cruzan en un punto determinado, que está fuera del rango del gráfico.
Excel nos permite encontrar este tipo de soluciones con facilidad usando el Solver. En esta nota en mi blog en inglés pueden ver un ejemplo del uso del Solver. Si esta opción no aparece en el menú hay que instalar el Add In correspondiente. El uso de esta herramienta supone que conocemos la forma de las ecuaciones, es decir por ejemplo, y1 = 3X +14 / y2 = 4.5X-22.
El problema se presenta cuando no conocemos la forma de las ecuaciones. Supongamos este ejemplo (tomado de mi blog en inglés, de ahi que las tablas de Excel aparecen en ese idioma):



Procedemos de la siguiente manera: seleccionamos una de las curvas y aplicamos Trendline apuntando con el mouse a la curva y apretando el botón derecho

en el menú que se abre selccionamos en Type --> Linear y en Options -->display equation in chart para hacer visible la ecuacion. Apretamos OK


Procedemos de la misma manera con la segunda curva


Para encontrar el punto de intersección usamos el solver. Dejando la celda A13 en blanco escribimos las siguientes fórmulas en las celdas B13 y C13


Usando el solver podermos resolver



If you enjoyed this post add to del.icio.us

Technorati Tags: excel graficos, excel chart, excel solver, excel ecuaciones, trendline


Categorías: Funciones&Formulas_, Graficos_