sábado, 28 de noviembre de 2009

Definición de nombres en Excel

Cuando hacemos referencia a una celda usualmente utilizamos la nomenclatura A1, A2, A3, etc donde la primera letra corresponde a la columna y el número corresponde a la fila. Adicionalmente Excel posee la función para otorgarle nombres definidos por el usuario a una celda o a un rango de celdas.

Esta herramienta se encuentra en la ficha Formulas, en la opción definir nombres dentro de la sección Nombres definidos.


Una vez seleccionada esa opción aparecerá un cuadro de dialogo para definir el nombre, donde ingresamos el nombre del rango y la celda o celdas a las que hace referencia.



A continuación veremos algunos ejemplos sencillos sobre como definir fórmulas utilizando nombres definidos por el usuario y cuales son sus ventajas.

Si tenemos una lista de precios y deseamos devolver el precio tomando como parámetro el código podemos definir el rango A1:B5 con el nombre Datos y utilizar la siguiente formula para la búsqueda =BUSCARV("Uva",Datos,2,Falso).


Si tenemos dicha fórmula en varios lugares en nuestra planilla y tenemos que cambiar el rango Datos o agrandarlo solo debemos hacerlo en un solo lugar y esto afectara a todas las formulas facilitando la administración y mantenimiento de dicha hoja de cálculos.

Otra aplicación es cuando tenemos un valor fijo que es aplicado en varias formulas en la planilla y para facilitar le lectura de la formula definimos un nombre para ese rango, si tenemos un nombre Descuento que tiene el valor 0.15 entonces podemos definir una formula =A1 * Descuento para obtener el resultado.


Cuando deseamos cambiar el valor del descuento solo modificamos el valor asignado al nombre y esto actualiza todas las fórmulas sin tener que hacerlo una por una.

lunes, 9 de noviembre de 2009

Búsquedas con múltiples parámetros

Nuevamente abordamos el tema de las búsquedas de datos en listas en Excel y si hablamos de buscar datos casi siempre utilizamos la función BUSCARV (o su hermana menos conocida BUSCARH) pero esta función tiene algunas limitaciones. En otras notas anteriores he explicado alternativas utilizando la formulas INDICE Y COINCIDIR

En esta nota explicaré como realizar una búsqueda utilizando más de un parámetro o celda para realizar la búsqueda.

La forma más sencilla de lograr esto es realizar una columna auxiliar donde concatenamos los datos de las dos columnas que forman nuestro criterio de búsqueda. De esta forma realizamos la búsqueda normal con un solo parámetro pero que contiene dos criterios o valores en su contenido

Si no deseamos o no podemos crear esta columna podemos recurrir nuevamente a INDICE Y COINCIDIR en una función matricial o array.

Para lograr el resultado, definimos cada condición o criterio de búsqueda que nos devolverá una matriz con VERDADERO O FALSO, ya que estamos utilizando una función matricial. Este array tendrá tantos elementos como filas tenga la lista de datos y dependiendo si esa fila cumple el criterio tendrá el valor VERDADERO y si no tendrá FALSO.

Luego para juntar todos los criterios multiplicamos las matrices y obtendremos una matriz con un valor verdadero si y solo si todas las matrices tenían un verdadero en esa posición. Aquí es donde realizamos la operación para asegurarnos que se cumplen todos los criterios de búsqueda y aquellas líneas que no cumplen con todos los criterios queda excluidas.

Una vez ubicado la posición devuelta por coincidir utilizamos la función INDICE para devolver el dato ubicado en esa posición.



Veamos un ejemplo


Tenemos una lista de precios como la que se muestra a continuación


Si deseamos ubicar el precio para Naranja y Minorista podemos utilizar la siguiente formula :
{=INDICE(C2:C11,COINCIDIR(1,(A12:A11="Naranja")*(B12:B11="Minorista"),0))}

Para ver como se resuelven las distintas condiciones pueden utilizar la herramienta Evaluar Fórmula de Excel que muestra paso a paso como se procesa todas estas fórmulas.

Recuerden que es una fórmula matricial por lo tanto se debe presionar Ctrl + Shift + Enter.

Para acceder al archivo del ejemplo pueden hacer clic aquí.