UNIVERSIDAD TÉCNICA DE AMBATO FACULTAD DE CIENCIAS HUMANAS Y DE LA EDUCACIÓN DOCENCIA DE INFORMÁTICA IV SEMESTRE
FUNCIONES DE EXCEL Guía de funciones Holguer Toalombo
FUNCIONES DE EXCEL
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
Contenido Introducción .................................................................................................................................. 4 VALIDACIÓN DE DATOS ............................................................................................................. 6 ORDENAR Y FILTAR .................................................................................................................... 8 Los tres tipos de filtros .............................................................................................................. 8 Filtrar texto................................................................................................................................ 9 Filtrar un rango de celdas.......................................................................................................... 9 Filtrar una tabla ....................................................................................................................... 10 Seleccionar de una lista de valores de texto ........................................................................... 10 FUNCIÓN SI .............................................................................................................................. 10 Sintaxis de la función SI ........................................................................................................... 10 Ejemplos de la función SI ........................................................................................................ 11 Utilizar una función como prueba lógica ................................................................................ 11 Formato condicional ............................................................................................................... 12 Para aplicar los formatos condicionales se siguen los siguientes pasos. ................................ 12 FUNCIÓN BUSCARV ................................................................................................................. 14 La sintaxis de la función BUSCARV .......................................................................................... 15 Pasos para crear una tabla dinámica ...................................................................................... 16 Mostrar la ficha Programador en Excel 2010 .......................................................................... 20 Linkografía ................................................................................................................................... 25
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
Introducción Esta guía digital está elaborada como material de apoyo a través de la compilación de información clara y necesaria que permitirá ampliar y definir los conocimientos del programa de cálculo: Excel. El objetivo primordial de esta guía es tener a la mano una herramienta a la cual podamos acudir por conceptos o estructuras básicas los cuales nos servirán como informáticos.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
VALIDACIÓN DE DATOS En Excel 2007 y 2010 puedes llevar a cabo la acción denominada Validación, que consiste en fijar unos límites a los datos de una hoja electrónica, bien referidos a una celda, a una fila o columna, bien en relación a datos de tipo número, texto o formula.
Un ejemplo rápido, suponte que tienes una hoja de cálculo para los pagos de salarios de los trabajadores, para evitar realizar una transferencia errónea puedes delimitar el contenido de las celdas de los importes a transferir. De esta forma si indicas que estas no sean mayores a un determinado importe, en caso de error, por ejemplo tecleas un cero de más, el programa te avisará o no te dejará hacerlo en función de cómo hayas definido los datos en el apartado validación.
Esta herramienta está en la ficha Datos, en el grupo Herramientas de datos arriba a la derecha. Para realizar el ejercicio que se presenta anteriormente proponemos los pasos uno a uno: Pasos: 1. Abre una hoja de Excel en blanco. 2. Introduce en A1: Nombre, en B1: Importe y en C1: N Cuenta Banco. 3. Escribe en A2, A3 y A4 los siguientes nombres: Rosa Moreno, Juan Gasca, Pablo Marín. 4. En B2, B3 y B4 escribe los importes de su salario liquido: 1800, 1700, 1800, como ves el tope de pago de salarios es 2000, vas a crear una regla para que en caso de error, teclear una cifra mayor o igual a 2000 y también si es menor a 1000, Excel te avise. 5. En la siguiente columna escribirás los números de cuenta bancaria, si bien en este ejemplo lo puedes dejar en blanco. 6. Selecciona las tres celdas donde están las cifras, esto es desde B2 a B4. FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL 7. Pulsa en el botón Validación de datos, en el grupo Herramientas de datos, dentro de la pestaña Datos. 8. En la ventana que se abre, en la pestaña Configuración, en el primer rectángulo despliega el triángulo negro de la derecha elige Número entero. 9. Debajo de Datos, deja lo que aparece: entre. 10. Después escribe Mínimo 1000 y Máximo 1999. 11. Aceptar.
En el hoja electrónica sitúate en cualquiera de las celdas con cifras y escribe una cifra mayor o igual a 2000, por ejemplo 2022. Aparece una nueva ventana en la que te indica que no es posible introducir ese valor, el dato no pertenece a lo indicado en la Validación. Si pulsas en el botón Reintentar te sitúa en la celda y a menos que escribas una cifra inferior a 2000 te seguirá dando error. Ahora vas a personalizar el mensaje que se muestra al dar de alta una Validación, siguiendo los pasos siguientes: Vuelve a pulsar en Validación de datos, sitúate en la pestaña Mensaje de error. Deja marcada como esta por defecto la casilla: Mostrar mensaje de error si se introducen datos no válidos. Debajo de estilo despliega pulsando en el triángulo negro y escoge por ejemplo Grave, (si escogieses alguno de las otras dos opciones el dato puede ser cambiado, ya que solo te mostrará un mensaje de advertencia o información pero permite introducir datos). En la siguiente opción que se te presenta llamada Título, escribe como quieres que se llame la alerta, por ejemplo Ojo! A continuación debajo de Mensaje de error escribe: No mayor a 1999 y no menor a 1000. Pulsa en Aceptar. Si intentas introducir el dato nuevamente no te deja y además te aparece el mensaje especificado anteriormente.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
ORDENAR Y FILTAR El filtrado de datos es una manera fácil y rápida de buscar un subconjunto de datos en un rango de celdas o una tabla y trabajar en él. Por ejemplo, puede filtrar los datos para ver únicamente los valores que especifique, filtrar los datos para ver el valor máximo y el valor mínimo o filtrar los datos para ver rápidamente valores duplicados. Después de filtrar datos en un rango de celdas o en una tabla, puede volver a aplicar un filtro para obtener resultados actualizados, o borrar un filtro para volver a mostrar todos los datos Para comenzar rápidamente con el filtrado, seleccione por lo menos una celda en un rango o en una tabla de Excel y, a continuación, haga clic en el botón Filtro (grupo Ordenar y filtrar de la ficha Datos).
Los datos filtrados solamente muestran las filas que cumplen los criterios que haya especificado y ocultan las filas que no desea ver. Tras filtrar los datos, puede copiar, buscar, modificar, aplicar formato, representar mediante gráficos e imprimir el subconjunto de datos filtrados sin tener que volver a organizarlo ni moverlo. También puede filtrar por más de una columna. Los filtros son aditivos, lo que significa que cada filtro adicional se basa en el filtro actual y además reduce el subconjunto de datos mostrados. Nota: Cuando se usa el cuadro de diálogo Buscar para buscar datos filtrados, solo se busca en los datos que se muestran; no se busca en los datos que no se muestran. Para buscar en todos los datos, se deben borrar todos los filtros.
Los tres tipos de filtros Con el filtro automático puede crear tres tipos de filtros: por una lista de valores, por un formato o por criterios. Estos tipos de filtro se excluyen mutuamente para cada rango de celdas o tabla de columna. Por ejemplo, puede filtrar por color de celda o por una lista de números, pero no por ambos; puede filtrar por icono o por un filtro personalizado, pero no por ambos. Volver a aplicar un filtro Para determinar si se ha aplicado algún filtro, observe el icono del encabezado de columna: Una flecha desplegable significa que el filtrado está habilitado pero no aplicado. Un botón Filtro significa que se ha aplicado un filtro.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL Se han agregado datos, se han eliminado o se han modificado en el rango de celdas en la columna de tabla. El filtro es un filtro de hora y fecha dinámica, como Hoy, Esta semana o Hasta la fecha. Los valores devueltos por una fórmula han cambiado y la hoja de cálculo se ha actualizado.
Filtrar texto Los métodos más rápidos para filtrar consisten en seleccionar valores en una lista y realizar búsquedas. Cuando hace clic en la flecha de una columna que tiene un filtro habilitado, todos los valores de esa columna aparecen en una lista. La ilustración siguiente muestra tres métodos para filtrar datos rápidamente
Use el cuadro Buscar para escribir el texto o los números que desea usar para realizar la búsqueda. Active y desactive las casillas para mostrar los valores que se encuentran en la columna de datos. Use criterios avanzados para buscar los valores que reúnen condiciones específicas.
Filtrar un rango de celdas 1. Seleccione un rango de celdas que contenga datos alfanuméricos. 2. En el grupo Ordenar y filtrar de la ficha Datos, haga clic en Filtro.
En la lista de valores de texto, active o desactive uno o más valores de texto por los que filtrar. La lista de valores de texto puede llegar a tener hasta un máximo de 10.000 entradas. Si la lista es grande, desactive (Seleccionar todo) en la parte superior y, a continuación, seleccione los valores de texto concretos por los que desea filtrar. FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
Filtrar una tabla 1. Asegúrese de que la celda activa se encuentra en una columna de tabla que contiene datos alfanuméricos. 2. Haga clic en la flecha
del encabezado de columna.
3. Asegúrese de que la celda activa se encuentra en una columna de tabla que contiene datos alfanuméricos. 4. Haga clic en la flecha del encabezado de columna.
Seleccionar de una lista de valores de texto 1. En la lista de valores de texto, active o desactive uno o más valores de texto por los que filtrar. La lista de valores de texto puede llegar a tener hasta un máximo de 10.000 entradas. Si la lista es grande, desactive (Seleccionar todo) en la parte superior y, a continuación, seleccione los valores de texto concretos por los que desea filtrar.
FUNCIÓN SI La función SI en Excel es parte del grupo de funciones Lógicas y nos permite 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 podemos especificar valores a devolver de acuerdo al resultado de la función.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
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 resultado VERDADERO o FALSO. Los argumentos Valor_si_verdadero y Valor_si_falso pueden ser cadenas 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. Utilizando la función SI desplegaré un mensaje de APROBADO si la calificació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")
Utilizar una función como prueba lógica Es posible utilizar el resultado de otra función como la prueba lógica que necesita la función SI siempre y cuando esa otra función regrese como resultado VERDADERO o FALSO. Un ejemplo de este tipo de función es la función ESNUMERO la cual evalúa el contenido de una celda y devuelve el valor VERDADERO en caso de que sea un valor numérico. En este ejemplo quiero desplegar la leyenda “SI” en caso de que la celda de la columna A efectivamente tenga un número, de lo contrario se mostrará la leyenda “NO”.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL =SI(ESNUMERO(A2), "SI", "NO")
Formato condicional El formato condicional se utiliza para aplicar unas características de formato a determinadas celdas de la hoja de cálculo, dependiendo de que se cumplan determinadas condiciones. Por ejemplo, una celda contiene una fórmula que calcula la varianza entre las ventas pronosticadas y las ventas reales y se quiere aplicar un sombreado de color verde a las celdas si las ventas reales sobrepasan a las ventas pronosticadas y un sombreado de color rojo si las ventas reales son menores que las ventas pronosticadas. Los formatos condicionales seguirán aplicándose a las celdas hasta que se quiten, incluso aunque no se cumpla ninguna de las condiciones y no se muestren los formatos de celda especificados.
Para aplicar los formatos condicionales se siguen los siguientes pasos. 1.- Seleccionar las celdas a las que se quiere dar formato. 2.- Pinchar en el menú Formato la opción Formato condicional. Aparecerá la siguiente ventana, donde debemos establecer la condición para aplicar el formato deseado
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL 3.- Para establecer la condición se puede realizar de 2 formas: a.- Valor de la celda. En este caso utilizamos los valores de las celdas seleccionadas como el criterio de formato, elegimos la frase de comparación, que por defecto es entre, e introducimos un valor en el cuadro correspondiente. Puede introducirse un valor constante o una fórmula; debe incluirse un signo igual (=) delante de la misma. b.- Fórmula. Para evaluar datos o una condición que no sean los valores de las celdas seleccionadas. En este caso la figura que aparece es la siguiente
En el cuadro de la derecha escribimos la fórmula que debe evaluarse contra un valor lógico VERDADERO o FALSO. 4.- Una vez establecida la condición, pulsamos el botón Formato y seleccionamos el estilo de fuente, el color, el subrayado, los bordes o la trama que queramos aplicar.
El formato seleccionado solamente se aplicará si el valor de la celda cumple la condición o si la fórmula devuelve un valor VERDADERO. FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL 5.- Para añadir otra condición, pulsamos el botón Agregar y repetimos los pasos del 3 al 5. Pueden especificarse tres condiciones como máximo. Si se especifican varias condiciones y más de una de ellas es verdadera, solamente se aplicará los formatos de la primera condición que sea verdadera. Si ninguna de las condiciones que se han especificado es verdadera, las celdas conservarán los formatos existentes. Pueden utilizarse los formatos existentes para identificar una cuarta condición.
FUNCIÓN BUSCARV La función BUSCARV nos permite hacer una búsqueda de un elemento concreto dentro de una tabla (en nuestro caso buscamos el código de producto 602) y una vez encontrado nos devuelve otros elementos asociados al mismo (en nuestro caso nos dirá que corresponde al producto Tornillo 30 mm y que su stock es de 34 unidades) Veamos la siguiente imagen para comprender un caso típico de uso de BUSCARV
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
La sintaxis de la función BUSCARV La sintaxis (forma de escribir) BUSCARV es sencilla y solo requiere 4 argumentos: = BUSCARV (valor buscado; tabla de búsqueda; columna de búsqueda; ¿tabla ordenada?)
En nuestro ejemplo, la fórmula que usamos en celda G3 para buscar el Producto es:
=BUSCARV(G2;B2:D12;2;FALSO)
Buscar el valor del código indicado en G2 (lo que buscamos puntualmente) En la tabla de B2:D12 (dónde lo buscamos) y devolver el valor de la columna 2 de dicha tabla (columna que contiene lo que buscamos) ¿la tabla está ordenada? FALSO (siempre conviene poner falso). La fórmula que usamos para buscar el Stock es similar, con la única diferencia que en el 3er argumento, la columna que contiene lo que buscamos es la 3 en lugar de la 2
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
TABLAS DINÁMICAS Una tabla dinámica sirve para resumir los datos que hay en una hoja de cálculo. Lo mejor de todo es que puedes cambiarla fácil y rápidamente para ver los datos de una manera diferente, haciendo de ésta una herramienta muy poderosa.
Pasos para crear una tabla dinámica
Partiendo de una hoja de cálculo que contiene las estadísticas de ventas para una empresa ficticia, supongamos que queremos responder a la pregunta: ¿Cuánto es el total de ventas por cada vendedor? Esto puede llevar mucho tiempo porque cada uno de ellos aparece en varias filas al igual que su venta mensual. Aunque podríamos utilizar la función Subtotal todavía tendríamos un montón de datos por analizar. Por suerte, una tabla dinámica puede hacer todas las operaciones matemáticas al instante y resumir los datos de una manera que no sólo es fácil de leer sino también, de manipular.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL Para crear una tabla dinámica que responda a la pregunta, sigue estos pasos: Paso 1: Selecciona la tabla o celdas (incluyendo los encabezados de columna) que contienen los datos que vas a utilizar.
Paso 2: En la ficha Insertar, haz clic en el comando Tabla dinámica. Paso 3: Aparecerá el cuadro de diálogo Crear tabla dinámica. Asegúrate de que la configuración sea correcta y haz clic en Aceptar.
Paso 4: Una tabla dinámica en blanco aparecerá al lado izquierdo y la lista de campos, al derecho.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
MACROS EN EXCEL Las macros Excel es un conjunto de instrucciones programadas en la hoja de cálculo electrónica Excel, las cuales automatizan las operaciones que realiza la aplicación ofimática Excel con el objetivo de eliminar tareas repetitivas o realizar cálculos complejos en un corto espacio de tiempo y con una nula probabilidad de error. Existen 2 maneras de realizar Macros en Excel: Mediante el uso de la grabadora de Macros. Mediante el uso del lenguaje VBA para programar a Excel
La primera de las opciones es la más fácil, dado a que solo requiere apretar al botón grabador de macros y realizar el conjunto de operaciones que queramos que se repitan, una vez finalizado guardamos la macro grabada con el nombre que queramos. Cada vez que queramos ejecutar esta macro tendremos que apretar el botón ejecutar macro y seleccionarla.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
Esta primera opción es la más fácil de usar pero es la que presenta más limitaciones, dado a que solo ejecutará una vez la tarea grabada así como no utilizará toda la potencia del lenguaje VBA.
La segunda opción requiere del conocimiento y práctica del lenguaje de programación VBA, pero es sin duda la opción más potente en cuestión de desarrollo de aplicaciones, cualquier cosa que se nos ocurra puede realizarse mediante el uso de VBA, desde una aplicación compleja hasta la automatización de tareas repetitivas de nuestra hoja de cálculo Excel.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL Si quieres escribir una nueva macro o ejecutar una macro previamente creada, entonces debes habilitar la ficha Programador dentro de la cinta de opciones. Para mostrar esta ficha sigue los siguientes pasos.
Mostrar la ficha Programador en Excel 2010 Haz clic en la ficha Archivo y elige la sección Opciones. Se mostrará el cuadro de diálogo Opciones de Excel donde deberás seleccionar la opción Personalizar cinta de opciones.
En el panel de la derecha deberás asegurarte de seleccionar la ficha Programador.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
Acepta los cambios y la ficha se mostrará en la cinta de opciones.
El grupo Código tienes los comandos necesarios para iniciar el Editor de Visual Basic donde se puede escribir directamente código VBA. También nos permitirá ver la lista de macros disponibles para poder ejecutarlas o eliminarlas. Y no podríamos olvidar mencionar que en FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL este grupo se encuentra el comando Grabar macro el cual nos permite crear una macro sin necesidad de saber sobre programación en VBA.
El grupo Complementos nos permite administrar y habilitar complementos como el Solver. El grupo Controles incluye funcionalidad para agregar controles especiales a las hojas de Excel como los controles de formulario que son botones, casillas de verificación, botones de opción entre otros más que serán de gran utilidad para ampliar la funcionalidad de Excel. El grupo XML permite importar datos de un archivo XML así como opciones útiles para codificar archivos XML. Finalmente el grupo Modificar solamente contiene el comando Panel de documentos. Aunque pueden parecer intimidantes los comandos de la ficha Programador con el paso del tiempo te irás familiarizando poco a poco con cada uno de ellos.
Ejemplos de uso A continuación se citan algunos ejemplos de las cosas que podemos con las Macros Excel:
Desarrollo de Aplicaciones complejas
Automatización de tareas repetitivas Desarrollo de nuevas funciones o aplicaciones dentro de la propia Excel Comunicación entre aplicaciones compatibles con VBA como Word, Access, Outlook, Autocad Creación de juegos dentro de la aplicación Excel
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
CONCLUSION Con este documento se ha podido plasmar de mejor manera el conocimiento obtenido en base al avanzado funcionamiento de Excel, lo recomiendo para su uso práctico dentro del ámbito educativo como empresarial orientado a cualquier rama de las ciencias digitales.
Esta guía permitirá el desarrollo del razonamiento lógico con la para aprender más sobre las diversas funciones que nos brinda a más de aprovechar los recursos tecnológicos que tenemos a nuestro alcance.
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
FUNCIONES DE EXCEL
HOLGUER TOALOMBO
FUNCIONES DE EXCEL
LinkografĂa http://exceltotal.com/la-funcion-si-en-excel/ http://www.queesexcel.net/que-son-las-macros-excel.html http://www.todoexcel.com/la-funcion-excel-buscarv/ http://office.microsoft.com/es-es/excel-help/filtrar-datos-de-un-rango-o-una-tablaHP010342517.aspx http://exceltotal.com/mostrar-la-ficha-programador/ http://www.gcfaprendelibre.org/tecnologia/curso/microsoft_excel_2010/trabajar_con_tablas _y_graficos_dinamicos/1.do
FUNCIONES DE EXCEL
HOLGUER TOALOMBO