En una hoja de Excel es muy importante coger los datos correctos para trabajar con las fórmulas diseñadas. Por eso existe una agrupación de funciones específicas para realizar búsquedas de datos.
Comprendamos qué es en sí una búsqueda, cuando queremos encontrar alguna información de algo no buscamos directamente por lo que buscamos pues lo desconocemos, realizamos una búsqueda de una propiedad o algo similar que conocemos que puede tener lo que
buscamos el nº de teléfono de un restaurante, buscamos en la guía de teléfonos por el nombre del restaurante. Normalmente el dato que queremos encontrar no lo conocemos por eso buscamos por otros datos que sí conocemos.
Entre algunas de las funciones de búsqueda de Excel encontramos:
Función
Descripción
AREAS Devuelve el número de rangos de celdas contiguas BUSCAR Busca valores de un rango de una columna o una fila BUSCARH Busca en la primera fila de la tabla o matriz de valores BUSCARV Busca un valor en la primera columna de la izquierda COINCIDIR Devuelve la posición relativa de un elemento
COLUMNA Devuelve el número de columna de una referencia
COLUMNAS Devuelve el número de columnas que componen la matriz DESREF Devuelve una referencia a un rango
DIRECCION Crea una referencia de celda en forma de texto ELEGIR Elige un valor o una acción de una lista de valores FILA Devuelve el número de fila
FILAS Devuelve el número de filas
Hipervínculo Crea un acceso directo a un documento
IMPORTARDATOSDINAMICOS Extrae datos almacenados en una tabla dinámica
INDICE Devuelve el valor de una celda en la intersección de una fila y una columna
INDIRECTO Devuelve una referencia especificada
BUSCAR: La función BUSCAR devuelve un valor de un rango de una fila o una columna o de una matriz. La función BUSCAR tiene dos formas de sintaxis: la forma vectorial y la matricial. Para que la función BUSCAR funcione correctamente, los datos en los que se realiza la búsqueda se deben colocar en orden ascendente. También se puede usar la función BUSCAR como alternativa para la función SI para crear pruebas o pruebas que excedan el límite de funciones anidadas. Vea los ejemplos de la forma matricial.
=BUSCAR(valor_buscado, vector_de_comparación, [vector_resultado])
En donde;Valor_buscado: Valor que busca la función BUSCAR en el primer vector. Valor_buscado puede ser un número, texto, un valor lógico o un nombre de referencia que se refiere a un valor.
Vector_de_Comparacion: Rango que sólo contiene una fila o una columna. Los valores del vector_de_comparación pueden ser texto, números o valores lógicos. Los valores del vector_de_comparación se deben colocar en orden ascendente: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSO, VERDADERO; de lo contrario, BUSCAR puede devolver un valor incorrecto. El texto en mayúsculas y en minúsculas es equivalente.
vector_resultado: Rango que solo incluye una fila o una columna. El argumento vector_resultado debe tener el mismo tamaño que vector_de_comparación.
Por ejemplo, de una base de datos con cientos de productos es deseable buscar el precio de un producto a partir del código del mismo.
CODIGO PRECIO AC111 C$ 1,083.00 AX121 C$ 1,093.00 BUSCAR("BX131",A2:A6,B2:B6) BX131 C$ 904.00 EF001 C$ 1,426.00 GR452 C$ 1,918.00
BUSCARV: Puede usar la función BUSCARV para buscar la primera columna de un rango de celdas y, a continuación, devolver un valor de cualquier celda de la misma fila del rango.
NOTA: En Excel 2007 y versiones anteriores la función es conocida como BUSCARV, en Excel 2010 esta función fue renombrada a CONSULTAV, sin embargo debido a confusiones entre los usuarios del mismo, la función sufrió un cambio de nombre con la primera actualización de Office y nuevamente es conocida como BUSCARV.
La V de CONSULTAV significa vertical. Use BUSCARV en lugar de BUSCARH si los valores de comparación se encuentran en una columna situada a la izquierda de los datos que desea buscar:
=BUSCARV(valor_buscado,matriz_de_busqueda,indicador_de_columna_ordenado)
Valor_buscado: Valor que se va a buscar en la primera columna de la tabla o rango. El argumento valor_buscado puede ser un valor o una referencia. Si el valor que proporcione para el argumento valor_buscado es inferior al menor valor de la primera columna del argumento matriz_buscar_en, BUSCARV devuelve al valor de error #N/A.
Matriz_buscar_en: El rango de celdas que contiene los datos. Puede usar una referencia a un rango (por ejemplo, A2:D8) o un nombre de rango. Los valores de la primera columna de matriz_buscar_en son los valores que busca valor_buscado. Estos valores pueden ser texto, números o valores lógicos. Las mayúsculas y minúsculas del texto son equivalentes.
Indicador_de_columna: Un número de columna del argumento matriz_buscar_en desde la cual debe devolverse el valor coincidente. Si el argumento indicador_columnas es igual a 1, la función devuelve el valor de la primera columna del argumento matriz_buscar_en; si el argumento indicador_columnas es igual a 2, devuelve el valor de la segunda columna de matriz_buscar_en y así sucesivamente.
Ordenado: Un valor lógico que especifica si BUSCARV va a buscar una coincidencia exacta o aproximada. Si ordenado se omite o es VERDADERO, se devolverá una coincidencia exacta o aproximada. Si no encuentra ninguna coincidencia exacta, devolverá el siguiente valor más alto inferior a valor_buscado.
Si ordenado es FALSO, no es necesario ordenar los valores de la primera columna de matriz_buscar_en. Si hay dos o más valores en la primera columna de matriz_buscar_en que coinciden con el argumento valor_búsqueda, se usará el primer valor encontrado. Si no se encuentra una coincidencia exacta, se devolverá el valor de error #N/A.
Basado en el mismo ejemplo anterior, utilizando ahora la función BUSCARV, buscar el precio de un producto a partir del código del mismo.
CODIGO PRECIO AC111 C$ 1,083.00 AX121 C$ 1,093.00 BUSCARV("BX131",A2:B6,3,FALSO) BX131 C$ 904.00 EF001 C$ 1,426.00 GR452 C$ 1,918.00
Al buscar valores de texto en la primera columna de matriz_buscar_en, asegúrese de que los datos de la primera columna de matriz_buscar_en no tienen espacios al principio ni al final,
de que no hay un uso incoherente de las comillas rectas ( ' o " ) ni tipográficas ( ‘ o “) y de que no hay caracteres no imprimibles. En estos casos, BUSCARV puede devolver un valor inesperado o incorrecto.
Si ordenado es FALSO y valor_buscado es un valor de texto, se pueden usar los caracteres comodín de signo de interrogación (?) y asterisco (*) en el argumento valor_buscado. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres. Si lo que desea buscar es un signo de interrogación o un asterisco, escriba una tilde (~) antes del carácter.
INDICE Y COINCIDIR: Puede utilizar juntas las funciones INDICE y COINCIDIR para obtener los mismos resultados que si utiliza BUSCAR o BUSCARV. A continuación se muestra un ejemplo de la sintaxis que combina INDICE y COINCIDIR para producir los mismos resultados que BUSCAR y BUSCARV en los ejemplos anteriores:
=INDICE(Matriz_buscar_en,COINCIDIR(Valor_buscado,Matriz_buscada,0),Indicador_columnas)
Por ejemplo, la fórmula presentada en la tabla siguiente busca la edad de María:
NOMBRE DEPARTAMENTO EDAD
Diego Managua 28
Antonio Rivas 19 =INDICE(A2:C5,COINCIDIR(“Maria”,A2:A5,0),3)
María Jinotega 22
Arturo Ocotal 29
DESREF Y COINCIDIR: Puede utilizar juntas las funciones DESREF y COINCIDIR para producir los mismos resultados que con las funciones del ejemplo anterior. A continuación se muestra un ejemplo de la sintaxis que combina DESREF y COINCIDIR para producir los mismos resultados que BUSCAR y BUSCARV:
=DESREF(celdaSuperior,COINCIDIR(Valor_buscado,Matriz_buscada,0),columnaDesplazamiento)
Por ejemplo, la fórmula presentada en la tabla siguiente busca la edad de Diego:
NOMBRE DEPARTAMENTO EDAD
Diego Managua 28
Antonio Rivas 19 =DESREF(A1;COINCIDIR("DIEGO";A2:A5;0);2)
María Jinotega 22
4.6 Auditoria de Formulas
Las fórmulas en ocasiones, pueden producir valores de error además de devolver resultados inesperados. A continuación, se muestran algunas herramientas que puede usar para buscar e investigar las causas de estos errores y determinar soluciones.
Rastrear precedentes Precedentes son celdas que afectan el valor de la celda inspeccionada. Celdas a las que se hace referencia mediante una fórmula en otra celda. Por ejemplo, si la celda D10 contiene la fórmula =B5, la celda B5 es la celda precedente a D10. Celdas dependientes Las celdas dependientes contienen fórmulas que hacen referencia a otras celdas. Por ejemplo, si la celda D10 contiene la fórmula = B5, la celda D10 es dependiente de la celda B5.
Las flechas azules muestran celdas sin errores. Las flechas rojas muestran celdas que causan errores. Si se hace referencia a la celda seleccionada desde una celda de otra hoja de cálculo o libro, una flecha negra señalará desde la celda seleccionada a un icono de una hoja de cálculo. El otro libro deberá estar abierto antes de que Excel pueda rastrear estas dependencias.
Mostrar Formulas En numerosas ocasiones hemos tenido la necesidad de ver la fórmula o función con la que estamos trabajando para tener una visión más clara del calculo que estemos ejecutando.
Comprobación de errores Al igual que un corrector ortográfico, que comprueba los datos que se escriben en las celdas para ver si contienen errores, se pueden implementar determinadas reglas para comprobar si hay errores en las fórmulas. Estas reglas no garantizan que la hoja de cálculo no tenga ningún error, pero ayudan en gran medida a encontrar los errores más comunes. Todas ellas se pueden activar y desactivar individualmente. Los errores se pueden señalar y corregir de dos formas: de uno en uno, como con el corrector ortográfico, o inmediatamente a medida que se produzcan en la hoja de cálculo en la que trabaja. En ambos casos, cuando se detecta un error, aparece un triángulo en la esquina superior izquierda de la celda.
#¡N/A! Este error se produce cuando una fórmula de búsqueda o referencia no encuentra ninguna coincidencia exacta en la correspondiente matriz de búsqueda. Significa que el valor buscado no existe en la matriz de búsqueda.
#¡REF! Este tipo de error surge cuando tenemos una referencia de celda inválida en la fórmula. Por ejemplo, en la fórmula: =BUSCARV("mi_string",A2:B8,3,FALSO), obtenemos #¡REF! ya que no podemos buscar en la tercera columna de una matriz que solo tiene dos columnas.
#¡NUMERO! Este se produce cuando ingresamos algún valor no numérico como un argumento de función que Excel espera que sea argumento numérico (o una referencia a un valor numérico). Otra posibilidad es ingresar un número inválido, como uno negativo cuando se espera uno positivo, o un 2 cuando el argumento solo admite 0 ó 1. La fórmula =COINCIDIR(123, B1:B10,3) devuelve #NUM!, ya que el último argumento de COINCIDIR solo puede ser -1, 0 ó 1.
#¡NOMBRE! Este error lo obtenemos cuando escribimos mal el nombre de alguna función. También puede surgir cuando utilizamos alguna función personalizada y tenemos deshabilitadas las macros o el complemento correspondiente. Otra situación que dispara este error es el escribir mal el nombre de algún rango nombrado. La fórmula =SUMAR.SI(A2:A10,"criterio",C2:C10) devuelve #¡NOMBRE! porque la función SUMARSI no existe (más bien está mal escrita. Lo correcto es SUMAR.SI). Finalmente puede suceder también que no utilizamos comillas al ingresar un argumento de texto.
#¡DIV/0! Se produce al hacer una división por cero, o bien, por una referencia a un cero.
#¡VALOR! Similar a #¡NUMERO!, lo obtenemos cuando el tipo de argumento solicitado por la función, es distinto al ingresado por el usuario. Por ejemplo, al ingresar un argumento lógico cuando la función requiere un rango, o un número cuando la función espera texto.
#¡NULO! Este es muy poco frecuente. Una fórmula devolverá #¡NULO! cuando la celda de intersección de dos rangos, no existe. En Excel, el operador de intersección
que los rangos A2:D2 y J1:J10 no se intersectan en ningún punto. En cambio, =A2:D2 C1:C10 devuelve C2, celda común a ambos rangos.
A menudo sucede que una celda de error está correctamente escrita pero, al hacer referencia a un resultado de error, refleja este resultado. Para saber cuál es la celda exacta que está generando el error, podemos ejecutar (previa selección de la celda con error) Herramientas - Auditoría de fórmulas - Rastrear error. Excel señalará con una línea roja la celda que está produciendo el error.
Otro error común es cuando la celda aparece llena de símbolos #. Esto se debe a que la celda no es lo suficientemente ancha para mostrar el resultado o bien, cuando contiene una fecha inválida.
Evaluar Formulas Permite calcular el total de una fórmula obteniendo el resultado de cada una de sus partes paso a paso. Esto nos ayudará a detectar fácilmente cualquier problema en nuestras fórmulas. Lo primero que debes hacer es seleccionar la celda que contiene la fórmula que deseas evaluar y posteriormente hacer clic sobre el botón Evaluar fórmula.
4.7 Ejercicios Propuestos
A. Conociendo las ventajas que brinda Excel con el uso de formulas y funciones, la librería y biblioteca “El Búho Lector” requiere obtener cierta información de su base de libros con el uso de formulas y funciones.
i. Haciendo uso de funciones matriciales calcule:
El precio de alquiler de un libro, tomando en cuenta que dicho precio equivale al 15% del Costo.
El Precio de Venta de cada libro, el cual equivale a 25% más del costo del libro.
Un descuento de 5% sobre el precio. ii. Compruebe el uso de las formulas SI.
Si un libro cuesta C$ 250°° o mas, imprima “CARO”, si el costo es menor imprima “BARATO”.
Si un libro se encuentra en la Categoría “CUENTOS” o “RELATOS FANTASTICOS”, imprima “INFANTILES”, sino imprima “MAYORES DE 12”.
Si un Autor a escrito menos de 3 libros, imprima “NOVATO”, si ha escrito menos de 5 imprime “INTERMEDIO”, si ha escrito mas de 5 imprime “EXPERTO”.
iii. Combinando funciones matriciales y función SI. Calcule el precio Promedio de costo de los libros de Ciencia Ficción.
iv. Compruebe el uso de las funciones de Bases de Datos.
Busque el libro mas reciente escrito por “STEPHEN KING”. Muestre el libro de PROSA, mas barato de la librería.
Cuente la cantidad de libros escritos en USA, ARGENTINA y ESPAÑA. v. Compruebe el uso de las funciones de Búsqueda.
El año de publicación del libro: CEREBRO.
El costo asignado al libro: LA MUJER ENMASCARADA.
En una nueva columna copie la editorial asociada a cada país, de acuerdo a la tabla de Editoriales.
B. GMM esta utilizando al máximo la hoja de Microsoft Excel para la gestión de sus deudores y quieren realizar lo siguiente: i. En una nueva columna aplique y refleje un descuento de
20% a las facturas de las personas con una deuda inferior a C$1000 y cuyo mes de deuda sea Junio.
ii. Calcule el nuevo total de la deuda utilizando Matrices (SALDO – DESCUENTO). iii. Suponiendo que se recupera todo el monto de la deuda, calcule el monto
recuperado por cada Gestor.
iv. Realice una hoja de búsqueda de datos que ayuda a encontrar a los clientes deudores y su información con solo digitar el numero de teléfono
C. Doodle Co. Y su equipo de trabajo a depurado un total de 200 encuestas de opinión, sobre la satisfacción del cliente en cuanto a una empresa de servicios, ahora es momento de que los datos sean analizados, para lo cual será necesario utilizar funciones y formulas:
ii. Realice un cruce de variables para poder conocer las respuestas de diferentes sectores de la población (apóyese en la Hoja 3).
iii. Haga uso de Formato Condicional para poder identificar incoherencias en las respuestas (es necesario utilizar la sexta regla del formato condicional).
Capitulo 5:
GRAFICOS Y SUBTOTALES
Un gráfico es la representación visual de los datos. Al usar elementos como columnas (en un gráfico de columnas) o líneas (en un gráfico de líneas), el gráfico muestra series de datos numéricos en un formato gráfico.
El formato gráfico de un gráfico facilita la comprensión de grandes cantidades de datos y la relación entre diferentes series de datos. Un gráfico también puede mostrar las ideas generales para que pueda analizar sus datos y buscar tendencias importantes.
Como insertar un Gráfico
1. Es recomendable que tengas seleccionado el rango de celdas que quieres que participen en el gráfico, de esta forma, Excel podrá generarlo automáticamente. En caso contrario, el gráfico se mostrará en blanco o no se creará debido a un tipo de error en los datos que solicita.
2. En la pestaña Insertar, en el grupo Gráficos, haga clic en el tipo de gráfico que desea usar y luego haga clic en un subtipo de gráfico.
Para ver todos los tipos de gráfico disponibles, haga clic en para iniciar el cuadro de diálogo Insertar gráfico y, a continuación, haga clic en las flechas para desplazarse por los tipos de gráfico
Cuando coloca el puntero del mouse sobre algún tipo de gráfico, aparece la información en pantalla con el nombre
3. Use las Herramientas de gráficos para agregar elementos de gráficos como títulos y etiquetas de datos, y para modificar el diseño, la presentación o el formato del gráfico.
Cada Grafico esta compuesto de diferentes partes, las cuales son editables de manera independiente o conjunta en dependencia del tipo de Grafico y el Formato que posee el mismo. La imagen siguiente ilustra las partes del grafico:
5.1 Tipos de Gráficos
A. Gráficos de Columnas
En un gráfico de columnas se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo. Este tipo de gráfico es útil para mostrar cambios de datos en un período de tiempo o para ilustrar comparaciones entre elementos. En los gráficos de columnas, las categorías normalmente se organizan en el eje horizontal y los valores en el eje vertical. Los gráficos de columnas tienen los siguientes subtipos de gráfico:
Titulo del Gráfico Eje Vertical
Primario
Eje Vertical Secundario
Eje Horizontal
Área del Grafico
Imagen
Columnas agrupadas: Los gráficos de columnas agrupadas comparan valores entre categorías. Un gráfico de columnas agrupadas muestra valores en rectángulos verticales en 2D. Puede utilizar un tipo de gráfico de columna agrupada cuando tiene categorías que representan:
Rangos de valores (por ejemplo, recuentos de elementos).
Disposiciones de escala específicas (por ejemplo, una escala de Likert con entradas, como totalmente de acuerdo, de acuerdo, neutral, en desacuerdo, totalmente en desacuerdo).
Nombres que no se encuentran en ningún
orden específico (por ejemplo, nombres de artículos, nombres geográficos o los nombres de personas).
Columnas apiladas y columnas apiladas en 3-D: Los gráficos de columnas apiladas muestran la relación de elementos individuales con el conjunto, comparando la contribución de cada valor con un total entre categorías. Un gráfico de columnas apiladas muestra los valores en rectángulos apilados verticales en 2D. Un gráfico de columnas apiladas en 3D simplemente muestra los datos con perspectiva 3D; no se usa un tercer eje de valores (eje de profundidad).
Columnas 100% apiladas y columnas 100% apiladas en 3D: Los gráficos de columnas 100% apiladas y columnas 100% apiladas en 3D comparan el porcentaje con que contribuye cada valor a un total de categorías. Un gráfico de columnas 100% apiladas muestra valores en rectángulos verticales 100% apilados en 2D. Un gráfico de columnas 100% apiladas en 3D
más series de datos y desee destacar las contribuciones al conjunto, especialmente si el total es el mismo para cada categoría.
Columnas 3D: Los gráficos de columnas 3D utilizan tres ejes que se pueden modificar (un eje horizontal, un eje vertical y un eje de profundidad) y comparan puntos de datos en los ejes horizontal y de profundidad. Puede utilizar un gráfico de columnas 3D cuando desee