FUNCIONES AVANZADAS EXCEL, Ejercicios de Informática

¡Descarga FUNCIONES AVANZADAS EXCEL y más Ejercicios en PDF de Informática solo en Docsity! 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres 11 FUNCIONES AVANZADAS DE EXCEL QUE TE HARÁN LA VIDA MÁS FÁCIL Escrito por Daniel Palacios Tenemos muy claro que una de las herramientas fundamentales de cualquier empresa son las hojas de cálculo. Estas nos facilitan horas de trabajo en el manejo de datos de contacto, administración de recursos, pagos a proveedores y más. Requiere práctica aprender a usarlas, pero son verdaderas maravillas que están a nuestro favor cuando las conocemos a profundidad. Por ello que queremos compartir contigo algunas funciones avanzadas que te ayudarán a agilizar tus tareas relacionadas con el Excel. Aquí encontrarás casos concretos en los que puedes aplicar las fórmulas en la gestión empresarial y mejorar así tu rendimiento en el trabajo.     11 fórmulas avanzadas de Excel más útiles  1. Insertar y quitar columnas rápido. 2. Añadir y eliminar filas velozmente. 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres 3. Lista desplegable. 4. INDICE. 5. Búsqueda avanzada. 6. Fijar casilla en operaciones. 7. Transponer datos. 8. SI. ERROR. 9. Texto en columna. 10. Unir cadenas. 11. Quitar duplicados. 1. Insertar y quitar columnas rápido Veamos el siguiente caso, en esta tabla es necesario agregar dos columnas entre las letras A y B. Para ello nos colocamos en la columna B y pulsamos las teclas Ctrl + Barra espaciadora del teclado; se va a seleccionar toda la columna. Luego tenemos dos opciones: 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Esta es una de las funciones avanzadas de Excel más prácticas para encontrar un valor específico en una columna. Es como un pequeño buscador donde escribes el número o la palabra y te dice si está en la lista o no. Está función nos puede servir para verificar un producto en el inventario, por ejemplo.  Supongamos que uno de tus clientes te pregunta si tienes un producto en verde oscuro; en lugar de checar casilla por casilla utilizas la Lista desplegable. Para ello selecciona una casilla fuera de la columna de colores y en la pestaña superior de «Datos» da clic en «Validación de datos» y saldrá este recuadro. En la parte de «Permitir» en la flechita negra vamos a indicar «Lista» y en la parte de «Origen» en la flechita roja seleccionamos los datos de la columna de colores. Luego le damos en «Aceptar».  18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Ya tenemos nuestra lista desplegable donde podemos consultar si el verde oscuro está en nuestro inventario al escribirlo y dar clic en la flechita negra de al lado de la casilla. En caso de no encontrarlo o teclearlo mal, el mismo programa nos lo hace saber con un anuncio de error. 4. INDICE 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Índice es una función que soluciona las limitaciones de búsqueda de BUSCARV, porque en ella solo podemos buscar colores que se encuentran en la primera columna de la izquierda. En el caso de INDICE no importa en qué columna o fila se encuentre el valor, podríamos decir que convierte una tabla en un mapa cartesiano donde indicamos las coordenadas de valor que queremos.  Supongamos que quieres consultar las comisiones que generaron cada uno de los vendedores de tu empresa a lo largo de cada mes. Bien, vamos a colocarlos en una casilla, poner = y a continuación buscamos INDICE(matriz; núm_fila; [núm_columna]) donde:  matriz: es el rango de filas y columnas donde se va a buscar, en este caso de la tabla de datos.  núm_fila: corresponde al número de fila donde se encuentra el valor que buscamos dentro de la matriz.  núm_columna: es el número de columna con el valor que buscamos dentro de la matriz. Ahora lo que nosotros queremos saber es cuánto ganó por comisión Carlos Altamirano en enero. Cuando nos pide el valor de «Matriz» vamos a seleccionar toda la tabla con el mouse y ponemos una coma.  Luego indicamos la fila con el número 4, coma otra vez, colocamos el número 2 para la columna, cerramos el paréntesis y listo: ya tenemos el valor que buscamos. Recuerda que los valores de fila y columna corresponden a lo que está dentro de la tabla, como nos lo indican los números rojos de la imagen; no son los de toda la hoja directamente. 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres así =INDICE(matriz;COINCIDIR(valor_buscado;matriz_buscada; [tipo_de_coincidencia]); COINCIDIR(valor_buscado;matriz_buscada; [tipo_de_coincidencia])). Para el COINCIDIR de fila es así:  matriz: son todos los valores contenidos en la tabla.  valor buscado: es la casilla de la lista desplegable de Vendedores.  matriz buscada: es la selección de los valores de la fila de Vendedores.  tipo de coincidencia: es el número 0 para que nos arroje valores exactos. En el COINCIDIR de columna queda así:  matriz: son todos los valores contenidos en la tabla.  valor buscado: es la casilla de la lista desplegable de Mes.  matriz buscada: es la selección de los valores de la columna de Mes.  tipo de coincidencia: es el número 0 para que nos arroje valores exactos. Luego de poner =INDICE nos va a pedir los valores de la «Matriz»; seleccionamos toda la tabla (color azul). Luego ponemos coma, escribimos la función COINCIDIR; en «Valor buscado» seleccionamos la casilla de la lista desplegable de Vendedor (color rojo); en «Matriz buscada» seleccionamos la columna de 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Vendedores (color morado); en «Tipo de coincidencia» colocamos 0 y cerramos paréntesis. Después escribimos otra coma, volvemos a insertar la función COINCIDIR; en «Valor buscado» tomamos la casilla de la Lista desplegable de Mes (color verde); en «Matriz buscada» seleccionamos la fila de los meses (color rosa); en «Tipo de coincidencia» ponemos 0 y colocamos dos paréntesis para cerrar toda la operación. 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Ahora ya puedes mover las listas desplegables de acuerdo con los datos que quieres conocer. Selecciona el vendedor y el mes en el que deseas consultar su comisión. Esta fórmula puede aplicarse para cualquier base de datos y seguramente te ahorrará horas de búsqueda. 6. Fijar casilla en operaciones Uno de los errores frecuentes que realizamos en Excel es que cuando necesitamos que se haga una operación con un mismo valor a lo largo de toda una columna, el resultado no sea correcto. Nos explicaremos mejor con el siguiente ejemplo. Supongamos que tenemos la lista de precios de nuestros productos y necesitamos obtener el precio total, que incluye el precio más un impuesto fiscal del 15 %. Entonces aplicamos la siguiente operación =precio del producto x (1+15%), que en Excel queda así. 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Esta fórmula nos sirve para detectar errores en los resultados de las fórmulas que se aplican en la hoja de cálculo. Nos indica cuando la operación no es posible, ya sea porque hay un número dividido por cero o cuando se están juntando dos valores de diferente tipo, es decir, una mezcla de números con letras. Dentro de los inventarios de un negocio esta fórmula es realmente eficaz para localizar los errores en los resultados e insertar una palabra o frase que nos ayude a ejecutar una acción al respecto. Por ejemplo, en el siguiente caso tenemos un reporte de ventas que indica que el producto set 4 se ha agotado; por lo tanto, no arroja el neto de las ventas.  Así que vamos a poner =SI.ERROR(valor:valor_si_error), donde:  valor: es la operación que queremos que revise.  valor_si_error: es la palabra o signo que queremos que ponga para distinguir el error, la cual hay que poner entre comillas. En este ejemplo, indicamos la multiplicación de la columna B por la C y colocamos la palabra “REVISAR” para que nos indique dónde está el error. 9. Texto en columna 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Texto en columna es una de las funciones de Excel que nos puede ayudar mucho a segmentar la base de datos de leads de nuestra marca. Por ejemplo, si quieres lanzar una campaña de mailing solo a contactos de ciertas regiones. En estos casos las funciones de EXTRAER, IZQUIERDA, MED y DERECHA no nos pueden ayudar, porque cada palabra tiene diferentes caracteres. Lo primero que debemos hacer es seleccionar nuestra tabla con las direcciones, luego vamos a la pestaña de «Datos» y en la barra superior damos clic en «Texto en columnas» y nos aparece el recuadro que está a continuación. Lo dejamos marcado en «Delimitados» y le damos clic a «Siguiente». En esta parte vamos a especificar qué queremos que se separe por la «Coma» que existe en la dirección de cada uno de nuestros leads. Luego damos en «Siguiente» y en el último recuadro elegimos «Finalizar». 18 INADEH-HOJA DE CÁLCULO AVANZADO Suministrado por: Ing. Omar De Gracia Torres Al final así quedarán los datos, divididos en municipio, estado o región, código postal y país.  10. Unir cadenas Se podría decir que es la función contraria de Texto en columna. Unir cadenas nos sirve para juntar los caracteres y valores de diferentes columnas. Por ejemplo, tienes las direcciones de tus leads separadas y tienes que enviarles un paquete a su domicilio, por lo que necesitarás su dirección completa para agregarla en la etiqueta. Sería más útil que estuviera junta para copiar y pegar rápido, en lugar de ir casilla por casilla.