La Herramienta 'Buscar Objetivo' en Excel

Introducción

Esta opción calcula automáticamente el valor de una celda para que se cumpla una determinada condición en otra. Excel buscará qué valor específico debería tomar para conseguir el resultado esperado. A ese valor específico se le denomina Valor Independiente y a la celda que contiene la fórmula se la denomina Valor Dependiente.

Es decir, lo que realiza Excel es resolver ecuaciones del tipo y=f(x), con lo que x es el valor encontrado para un determinado y. La función objetivo nos permite obtener cálculos como: saber la cantidad de equilibrio para una firma, es decir, calcular la cantidad a producir para que la utilidad sea cero o calcular el precio de un producto para el cual una empresa obtenga cierto beneficio.

Cómo Utilizar Función Objetivo

1. Para activar la buscar objetivo primero se debe seleccionar la celda que contiene fórmula o función a modificar.
2. En el menú Herramientas, de debe hacer clic en Buscar Objetivo. 
3. En la casilla Definir la celda, se escoge la celda que contiene la función o fórmula a resolver, en la casilla Con el valor, se establece el valor resultado de la fórmula y en la casilla Para cambiar la celda, se escoge la celda donde se encontrará el valor que da solución a la ecuación. 
4. Finalmente, Aceptar.

Después del salto un par de ejemplos

Leer más...

Ventajas y Desventajas de las Tablas Dinámicas

Las Tablas Dinámicas en Excel deben ser una de las herramientas más usadas de este software y por las que más nos preguntan a la hora de ver cuánto sabemos de Excel.
Esta herramienta tiene muchas ventajas, muy fácil de usar, rápidas, pueden trabajar con muchos datos, permite incorporar gráficos entre otras. Pero también tiene limitaciones que es importante conocer.

Principales Ventajas de las Tablas Dinámicas

1. Fácil de usar: este debe ser su principal atributo, con solo algunos clicks puedo resumir una gran cantidad de datos y mostrarlos de la manera en que los necesite. Independiente del nivel de la consulta que esté haciendo, con muy pocas otras herramientas puedo en dos clicks, mostrar todas las ventas de un determinado vendedor, por poner un ejemplo.
2. Dinámicas: justamente haciendo referencia a su nombre; el hecho de que sea tan fácil agregar y quitar dimensiones hacen que sea la herramienta ideal para, por ejemplo, llevar los datos a una reunión y comenzar a sacar información "on demand" o trabajar con los datos en equipo.
3. Trabajo en varias dimensiones: Siempre se habla de las fórmulas "tridimensionales" cuando se hace referencia a que Excel puede trabajar en fila, columna y hoja. En Tablas Dinámicas podemos trabajar con 3 dimensiones de datos: Fila, Columna y Filtro (o Página como se llamaba hasta Excel 2007). De esta forma es posible agregar un nivel más de "resumen" que cuesta conseguirlo con fórmulas normales.
4. Múltiples vistas y cálculos: Es muy sencillo cambiar las visualizaciones de los datos y los estadísticos resumen, con un par de clicks puedo: cambiar la función de agrupación (SUMA, CONTAR, PROMEDIO) o bien cambiar la forma en que se muestran los datos: como % del total, de la fila o incluso como diferencias porcentuales. Además podemos agregar campos o elementos calculados para complementar el análisis.

Las Desventajas de las Tablas Dinámicas

1. Poco Flexibles: Si bien hay muchas posibilidades, las Tablas Dinámicas no son tan flexibles como uno quisiera. Por ejemplo, no puedo en una misma tabla dinámica agrupar datos y por otro lado generar un elemento calculado. Necesariamente hay que generar una nueva Tabla Dinámica, pero inclusive con un origen de datos nuevos (Ver punto 3).
2. "Monolíticas": En general la Tabla Dinámica genera una especie de "aplicación" a la que cuesta mucho acceder a sacar datos. La opción de trabajar sin las funciones de GETPIVOT permiten de alguna manera poder acceder a los datos, pero al momento de cambiar la tabla dinámica esa referencia se volverá sin sentido. Hay que recurrir a las funciones de Importación de Datos Dinámicos, que deben ser las fórmulas más complejas de entender de todas (según mi parecer).
3. Memoria de Datos: Excel trata siempre de optimizar recursos y hay veces en que esta optimización nos trae repercusiones: en Excel 2003 era común tener dos o más Tablas Dinámicas, modificar una y replicar lo mismo en todo el resto. En Excel 2007, al parecer eso no ocurre, pero sí pasa que las Tablas quedan linkeadas a la fuente, cualquier Informe Dinámico que haga sobre esos datos va a compartir características con otros generados.
4. Procesamiento y Cálculos: Si bien los elementos y campos calculados son una herramienta muy potente, no queda tan claro (por lo menos a primera vista) de como operan y muchas veces pueden llevar a confusión a usuarios no tan expertos. Además hay cálculos que matemáticamente pueden parecer triviales, pero no son soportados por la herramienta.

En conclusión lo importante es conocer como operan las Tablas Dinámicas y sobre todo conocer muy bien sus ventajas y desventajas. Y sobre todo lo más importante es saber que para cada problema existe una mejor herramienta que otra -no única claro- pero que puede hacer el trabajo de una manera mucho más eficiente.

Leer más...

Conociendo la función INDIRECTO

Hoy hablaremos de una función un tanto compleja, pero que seguramente les será de muchísima utilidad cuando logren dominarla. El nombre de esta función es INDIRECTO.

Antes de continuar su lectura recomendamos que abran una hoja de excel y prueben los ejemplos que daremos, ésta será la forma más sencilla de comprender la función.

¿Qué es?
El nombre de la función es INDIRECTO y la idea de ésta es entregarnos referencias insertas dentro de otras referencias. O sea, una celda que dentro de sí misma contiene la dirección de otra celda.

¿Cómo funciona?
Si por ejemplo tenemos en las celdas A1:A5 una escala likert, puedo con la función indirecto buscar el contenido de alguna de esas 5 celdas sin necesariamente hacer referencia a ellas sino que a otra celda, que a su vez haga referencia a alguna de estas:

Como se puede ver en la imagen superior, en la celda C3 se hace referencia a la celda A1, entonces a través de la función indirecto puedo evocar la celda C3 y obtener el valor de la celda a la cual ésta hace referencia.

Esto aún no parece muy útil, puesto que perfectamente podríamos hacer la referencia directamente, o sea con un =A1.

La referencia no tiene que ser exactamente del tipo ”Letra-Número”, sino que podemos concatenar el valor de 2 celdas, una con una letra y otra con un número, y a través de indirecto obtendríamos el valor contenido.

Por ejemplo, en la figura3, tenemos las tablas de multiplicar del 1 al 4 (A1:D4), a su vez tenemos dos celdas que contienen una letra y un número siendo posibles concatenarlas dentro de la misma función indirecto logrando hacer referencia a uno de los valores de A1:D4.

Ahora tenemos una noción de lo que significa esta función y podremos buscarle aplicaciones de mayor complejidad y a su vez, utilidad.

Aplicación al mundo profesional.

Hemos escogido 2 aplicaciones útiles para distintas tareas que puedan emprender. Para verlo, sigan leyendo la entrada:

Leer más...

Filtros Avanzados en Excel: Más allá del Autofiltro

Introducción

Los filtros en general son útiles cuando estamos trabajando con bases de datos con en Excel y deseamos obtener únicamente aquellas filas de datos que cumplen con algún criterio que definamos. Existen dos tipos de filtro: los Autofiltros y los Filtros Avanzados, siendo estos últimos los que pretendemos explicar en esta tarea.

¿Qué es?

El Filtro Avanzado es una herramienta que se usa en general cuando los Autofiltros quedan “cortos” para lo que pretendemos obtener de la base de datos. Su ventaja está en que podemos optar bien por filtrar sobre la misma base de datos, al igual que el Autofiltro, o bien realizar un copiado con los registros filtrados que cumplan las condiciones o criterios dados en otro lugar de la planilla que nosotros seleccionemos.

El filtrado oculta temporalmente las filas que no se desea mostrar. Cuando Excel filtra filas, le permite modificar, aplicar formato, representar en gráficos e imprimir el subconjunto del rango sin necesidad de reorganizarlo ni ordenarlo. Entonces, en los filtros avanzados se utilizan criterios lógicos para filtrar las filas, en este caso, se debe especificar el rango de celdas donde se ubican los mismos.

¿Cómo usarlo?

En la barra de operaciones debemos ir a la pestaña “Datos”, luego al panel “Ordenar y filtrar” donde debemos hacer clic donde dice “Avanzadas”. Así aparece la ventana “Filtro Avanzado”:

¿Qué significa cada entrada?

1. Filtrar la lista sin moverla a otro lugar: se filtran los datos en el mismo lugar donde se encuentra la tabla.
2. Copiar a otro lugar: la tabla filtrada puede aparecer en un lugar especificado de la misma Hoja o en otra Hoja de cálculo, donde sea que queramos. Al elegir esta acción, se activará la opción de “Copiar a”. (Es importante mencionar que esta opción es más recomendable ya que se mantiene la tabla original y se obtiene una tabla aparte con la información que requerimos.)
3. Rango de la lista: seleccionar el rango que deseamos filtrar.
4. Rango de criterios: es el rango elegido por el usuario para ubicar los criterios de filtrado. Se explica con más detalle a continuación.
5. Copiar a: esta opción queda habilitada cuando se marca la casilla del punto 2, en cuyo caso deberemos especificar el lugar sonde queremos que aparezca la tabla filtrada, para esto sólo es necesario especificar donde se ubicará la tabla “filtrada”.
6. Existen 2 opciones para señalar esta ubicación: una es simplemente señalando una celda donde a partir de ésta quedará la tabla “filtrada” y tendrá la misma cantidad de columnas (variables) del rango de lista. Y la otra es seleccionando un rango, previamente construido, conformado únicamente por aquellas variables que nos interesará mostrar en la tabla “filtrada”.
7. Sólo registros únicos: en el caso de haber registros duplicados (filas que son exactamente iguales en todas sus celdas), mostrar sólo uno de ellos.

Leer más...

Función DESREF: Uso en la práctica de Excel

En el excelente artículo que desarrolló el grupo de Francisco Droguett, Josefina Silva y Josefina Vodanovic, vimos como utilizar la función DESREF; función, para la gran mayoría de nosotros desconocida. Una gran duda que queda luego de leer el post es ¿para qué sirve está función en la práctica?

En el post quedaba muy claro algunas aplicaciones sobre todo cuando va como argumento de una función (en el ejemplo SUMA). Pero existen múltiples usos que se le puede dar a esta función, en este artículo les muestro otros dos posibles usos:

Búsqueda
Si bien esta no es la única forma de resolver esta problemática, DESREF podría ayudarnos en la siguiente situación. Supongamos que tenemos una tabla con múltiples filas y columnas. Podríamos querer devolver un valor de dicha tabla, una forma sería hacerlo con BUSCARV por ejemplo, pero también podríamos usar DESREF. En el ejemplo se muestra una tabla que contiene todos los valores para la UF del año 2010 (en las columnas los meses, en las filas los días). Si tenemos como entrada el día y el mes y queremos que nos devuelva el valor de la UF, una solución muy simple sería usar: =DESREF(A1, [número del mes]; [número del día]). Así se ira moviendo desde A1, hasta el valor de la UF que se busca. En el ejemplo queda más claro:

Relleno contra sentido
Este uso es muy específico, pero más de alguna vez escuché la pregunta de como poder hacerlo. Supongamos que lo que necesitamos es Poder autocompletar una fórmula en el sentido contrario al de Excel. ¿Cómo es esto? Si yo autocompleto una fórmula de arriba hacia abajo, las referencias se iran moviendo hacia abajo, es decir si parto en A1 con mi fórmula y autocompleto, entonces la próxima referencia será a A2 y luego a A3 y así. Pero que pasa si queremos autocompletar, de arriba a abajo, pero que vaya moviéndose de izquierda a derecha. Lo más fácil sería disponer los datos de otra forma, pero también se podría usar DESREF. La explicación queda más clara en el ejemplo.

Aquí pueden descargar el archivo con el ejemplo.

Leer más...