consulta avanzado - eduktvirtual · función si anidada para poder decidir el rango de la tabla...
TRANSCRIPT
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
Consulta Avanzado
Objetivo• Aprender a usar las funciones consultaV, consulta H, Si y es
Error.
Contenido
• Combinación de funciones: CONSULTAV, CONSULTAH, SI, ESERROR
• Búsqueda Múltiple.
Sesión 1
�
Consulta avanzado
Microsoft Excel Avanzado 2010
1. Combinación de funciones: CONSULTAV, CONSULTAH, SI, ESERRORFunción CONSULTAV
La función CONSULTAV es la manera en que Excel te permite encontrar “una aguja en un pajar”. Lo explicaremos mejor con un ejemplo. Su-pongamos que tenemos una lista de empleados de nuestra compañía en donde la primer columna indica el número de empleado y la segunda columna su nombre.
IMPORTANTE: Si ya has aplicado el SP1 de Office 2010, la función CONSULTAV regresa su nombre a BUSCARV.
Ejemplo de la función CONSULTAV
Ahora necesitas encontrar el nombre de la persona que tiene el núme-ro de empleado 45362 ¿Cómo lo haces? Lo adivinaste bien, utilizas la función CONSULTAV para encontrar el nombre del empleado. Observa la siguiente imagen:
Mi lista de empleados (que es muy pequeña para este ejemplo) se en-cuentra en el rango A2:B10 y en la celda D1 he colocado el número de empleado de quien deseo conocer el nombre.
Parámetros de la función CONSULTAV
El primer parámetro de la función CONSULTAV es el valor que estoy buscando, que en este caso es el valor de la celda D1 que contiene el número de empleado a encontrar. El segundo parámetro de la función es el rango de datos que es A2:B10.
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
El tercer parámetro es muy importante, porque indica la columna que Excel regresará como resultado. Para este ejemplo el rango de datos tie-ne dos columnas: la columna 1 es el número de empleado y la columna 2 es el nombre. Lo que yo necesito es que una vez que Excel encuentre el número de empleado me regrese el nombre, por lo que le pido que me regrese la columna 2.
Finalmente el cuarto parámetro FALSO indica que quiero una coinciden-cia exacta al buscar el número de empleado especificado. Para encontrar el nombre de otro empleado es suficiente con cambiar el valor de la cel-da D1 con el nuevo número de empleado y Excel mostrará el nombre:
Función CONSULTAH
Busca un valor dentro de una fila y regresa el valor en la misma posición de una segunda fila. Siempre busca en la primera fila del rango especi-ficado.
Sintaxis
CONSULTAH(valor_buscado, rango, valor_regresado, [aproximado])
valor_buscado (obligatorio): Valor que se buscará en el rango
rango (obligatorio): El rango de celdas que contiene la fila de valores y la fila de resultados.
valor_regresado (obligatorio): Número de fila (dentro del rango) que contiene los resultados.
aproximado (opcional): Indica si será una coincidencia aproximada. Si se omite se toma como verdadero.
Sesión 1
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Ejemplos:
CONSULTAH(“Excel”, A1:J2, 2) = Encuentra “Excel” en la fila 1 y regresa el valor que le corresponde de la fila 2
Función ESERROR
La función ESERROR en Excel nos ayuda a comprobar si un valor es un error y nos devuelve el valor VERDADERO o FALSO. El valor evaluado puede ser una celda o una fórmula cuyo resultado será evaluado.
Sintaxis de la función ESERROR
Valor (obligatorio): Es el valor que se desea evaluar.
Ejemplos de la función ESERROR
La función ESERROR evalúa el valor de una celda para saber si contiene alguno de los errores de Excel. Observa la siguiente imagen que con-tiene todos los errores posibles en Excel y a su lado el resultado de la función ESERROR.
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
Sólo en el caso en donde la celda tenga un valor de error la función ESE-RROR devolverá el valor VERDADERO. En el ejemplo anterior las celdas A9, A10 y A11 no contienen un error y por lo tanto la función ESERROR devuelve el valor FALSO.
Función SI
La función SI en Excel es parte del grupo de funciones Lógicas y nos per-mite evaluar una condición para determinar si es falsa o verdadera. La función SI es de gran ayuda para tomar decisiones en base al resultado obtenido en la prueba lógica.
Sintaxis de la función SI
Además de especificar la prueba lógica para la función SI también po-demos especificar valores a devolver de acuerdo al resultado de la fun-ción.
. Prueba_lógica (obligatorio): Expresión lógica que será evaluada para conocer si el resultado es VERDADERO o FALSO.
. Valor_si_verdadero (opcional): El valor que se devolverá en caso de que el resultado de la Prueba_lógica sea VERDADERO.
. Valor_si_falso (opcional): El valor que se devolverá si el resultado de la evaluación es FALSO.
La Prueba_lógica puede ser una expresión que utilice cualquier operador lógico o también puede ser una función de Excel que regrese como re-sultado VERDADERO o FALSO.
Sesión 1
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cade-nas de texto, números, referencias a otra celda o inclusive otra función de Excel que se ejecutará de acuerdo al resultado de la Prueba_lógica.
Ejemplos de la función SI
Probaremos la función SI con el siguiente ejemplo. Tengo una lista de alumnos con sus calificaciones correspondientes en la columna B. Utili-zando la función SI desplegaré un mensaje de APROBADO si la califica-ción del alumno es superior o igual a 60 y un mensaje de REPROBADO si la calificación es menor a 60. La función que utilizaré será la siguiente:
=SI(B2>=60,”APROBADO”,”REPROBADO”)
Observa el resultado al aplicar esta fórmula en todas las celdas de la columna C.
Ejemplo de combinación de las funciones descri-tas anteriormente:
La función ESERROR sirve para saber si el resultado de una fórmula o función es un error.
Cuando utilizamos la función CONSULTAV y el valor buscado no se en-cuentra en la matriz, nos devuelve un error #N/A.
La limitante de que nos devuelva este error, es que, si queremos hacer una sumatoria no nos lo permite, y lo que regularmente hacemos es borrar de una por una.
La función SI anidada con ESERROR nos ayudará a eliminar el error y cambiarlo por un texto, valor, función, etc.
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
La imagen anterior muestra una búsqueda de valores con CONSULTAV, algunos tienen error, si queremos hacer una sumatoria esta se anula por los errores.
En la celda B19 se encuentra la función SUMA y no hace la sumatoria debido a los errores derivados de BUSCARV.
Para evitar este tipo de errores utilizamos la función ESERROR. Lo que vamos a hacer con ESERROR es evaluar la función BUSCARV, si esta es un error, como se puede ver en las diferentes celdas, entonces devolverá VERDADERO si no lo es devolverá FALSO, veamos la siguiente imagen.
Sesión 1
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Para terminar debemos anidar la función SI para saber que, si es un error BUSCARV entonces que devuelva 0 cero, si no lo es, entonces que devuelva el importe, para devolver el importe escribimos nuevamente BUSCARV. Veamos la siguiente figura.
Podemos ver que la función BUSCARV se repite al final, esto significa que la primera función solo nos sirve para saber si es un error o no, si no lo es entonces se aplica la segunda.
�
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
2. Búsquedas múltiplesFunción CONSULTAV sobre dos tablas de búsqueda.
La función CONSULTAV nos permite buscar un valor en una tabla, pero en ocasiones los datos los tenemos distribuidos en más de una tabla. En esta ocasión te mostraré cómo utilizar la función CONSULTAV sobre dos tablas de búsqueda.
En nuestro ejemplo analizaremos el caso de una institución financiera que ha establecido una nueva regla para otorgar créditos a sus clientes. La empresa otorgará dos tipos de créditos de acuerdo al plazo ya sea de 24 meses o de 36 meses. Dependiendo del plazo acordado y el monto del mismo se cobrará una comisión al cliente.
El plazo del crédito es la variable que determina la tabla de comisiones que debemos utilizar. En base a dicho valor es que tomaremos la decisión sobre cuál tabla utilizar y para ello utilizaremos la función SI.
La función SI nos ayudará a obtener la tabla adecuada de acuerdo al valor del plazo. En el siguiente ejemplo, puedes observar los datos de un cliente que desea solicitar un crédito a 24 meses y un monto de $25000.00:
Sesión 1
�0
Consulta avanzado
Microsoft Excel Avanzado 2010
Para obtener el valor de la comisión debemos utilizar la tabla ubicada en el rango A3:A7. Considera la siguiente fórmula de Excel:
=SI(B11=24, $A$3:$B$7, $D$3:$E$7)
Esta fórmula evaluará el valor de la celda B11 y en caso de ser igual a 24 regresará el rango de la tabla de 24 meses, de lo contrario regresará el rango de la tabla de 36 meses. Ahora incluyamos esta fórmula como el segundo argumento de la función CONSULTAV:
=CONSULTAV(C11, SI(B11=24, $A$3:$B$7, $D$3:$E$7), 2)
La función SI proveerá el rango de la tabla adecuada donde se deberá buscar el valor de la celda C11 por la función CONSULTAV. Observa que esta función obtiene el valor adecuado:
De igual manera obtendremos un resultado adecuado si agregamos otro cliente que solicite un crédito con un plazo a 36 meses:
��
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
Con solo copiar la celda hacia abajo tenemos el resultado adecuado para el plazo y los montos indicados para el nuevo cliente.
CONSULTAV sobre múltiples tablas
Ahora bien, ¿qué pasa si tengo más de una tabla donde necesito utilizar la función CONSULTAV? Podríamos sentirnos tentados a utilizar una función SI anidada para poder decidir el rango de la tabla adecuado, pero te recomiendo otra solución y es crear una tabla de equivalencias intermedia.
Supongamos que ahora la empresa decide agregar dos nuevos plazos en los créditos otorgados para 12 meses y 48 meses. Para saber la tabla adecuada a utilizar crearemos una tabla de equivalencias entre los plazos en meses y los rangos de cada tabla.
Utilizaremos también la fórmula CONSULTAV para decidir el rango adecuado de la tabla que debemos utilizar para obtener el valor de la comisión. La fórmula es la siguiente:
Sesión 1
��
Consulta avanzado
Microsoft Excel Avanzado 2010
=CONSULTAV(C25, INDIRECTO(CONSULTAV(B25, $A$18:$B$21, 2)), 2)
Explicación de la FUNCIÓN INDIRECTO
La función INDIRECTO en Excel nos ayuda a obtener una referencia a una celda o a un rango de celdas. Puedes utilizar esta función para crear una referencia que no cambiará aun cuando se inserten filas o columnas a la hoja de Excel.
La función INDIRECTO también puede ser utilizada para crear una referencia a partir de una letra y un número dando forma a la dirección de la celda de la cual deseamos obtener la referencia.
Sintaxis de la función INDIRECTO
La función INDIRECTO tiene dos argumentos.
• Ref (obligatorio): Es la referencia especificada como una cadena de texto.
• A1 (opcional): Valor lógico que indica el tipo de referencia especificada: A1 (verdadero) o F1C1 (falso).
En caso de omitir el argumento A1, la función INDIRECTO supondrá que la referencia es de tipo A1.
Bloquear referencia a una celda
En el siguiente ejemplo puedes observar en la celda C1 la fórmula =A5 y en la celda C2 la fórmula =INDIRECTO(“A5″).
��
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
Ambas celdas muestran el valor de la celda A5, pero ahora observa lo que sucede al insertar una nueva fila por arriba de la fila 5.
El valor 500 ahora se encuentra en la celda A6 y Excel actualizó automáticamente la fórmula de la celda C1 que ahora es =A6. Sin embargo, la celda C2 muestra el valor 0 (cero) porque la función INDIRECTO sigue regresando la referencia a la celda A5 que ahora está vacía.
Si necesitas hacer referencia siempre a la misma celda sin importar que se inserten o eliminen filas o columnas, entonces la función INDIRECTO será adecuada para dicho propósito.
Siguiendo con el ejemplo anterior:
Observa que también utilizo la función INDIRECTO para transformar el texto devuelto por la función CONSULTAV en una referencia “real” que pueda ser utilizada por la otra función CONSULTAV. En la siguiente imagen puedes observar el resultado correcto de esta fórmula:
Sesión 1
��
Consulta avanzado
Microsoft Excel Avanzado 2010
��
Consulta avanzado
Microsoft Excel Avanzado 2010
Sesión 1
Resumen
Función CONSULTAV
La función CONSULTAV es la manera en que Excel te permite encontrar “una aguja en un pajar”.
Sintaxis:
CONSULTAV(valor_buscado, rango, valor_regresado,ordenado)
Función CONSULTAH
Busca un valor dentro de una fila y regresa el valor en la misma posición de una segunda fila. Siempre busca en la primera fila del rango especificado.
Sintaxis
CONSULTAH(valor_buscado, rango, valor_regresado, [aproximado])
Función ESERROR
La función ESERROR en Excel nos ayuda a comprobar si un valor es un error y nos devuelve el valor VERDADERO o FALSO. El valor evaluado puede ser una celda o una fórmula cuyo resultado será evaluado.