Funciones Avanzadas de EXCEL Informática Aplicada a la Construcción Universidad Politécnica de Cartagena Curso 2006/2007 Francisco Joaquín Escribá Pérez
FUNCIONES AVANZADAS ORDENACIÓN FILTROS SUBTOTALES FUNCIONES MODULOS VB (MS Visual Basic) BUSCAR OBJETIVOS 2
FILTROS AUTOFILTRO AVANZADOS
3
AUTOFILTRO En los Autofiltros, EXCEL elige como criterio de filtrado los encabezados de las columnas de las tablas. P.e:
4
FILTROS AVANZADOS • El usuario es el encargado de realizar los filtrados de forma manual estableciendo los criterios de filtro y los valores aplicados. • Debe prepararse un zona de la hoja de EXCEL para los criterios del filtro para seguidamente utilizar llamar al diálogo de Filtro Avanzado. 5
FILTROS AVANZADOS
6
SUBTOTALES Microsoft Excel puede calcular de forma automática valores de subtotales y de totales generales en una lista. Cuando se insertan subtotales automáticos, Excel esquematiza la lista para que se puedan mostrar y ocultar las filas de detalle de cada subtotal. Para insertar subtotales, primero se ordena la lista para agrupar las filas cuyos subtotales se desea calcular. Después pueden calcularse los subtotales de cualquier columna que contenga números. 7
SUBTOTALES
8
SUBTOTALES
9
Funciones EXCEL Tipos de Funciones • Matemáticas y Trigonométricas • Estadísticas • Lógicas • Fecha y Hora
10
Funciones Lógicas La función lógica más importante es la función “SI” condicional. Las funciones lógicas suelen combinarse con las restantes funciones para obtener resultados complejos. El uso de las funciones “NO”,”O” y “SI” permiten escribir expresiones lógicas complejas partiendo de otras más simples.
11
Funciones Lógicas FALSO()
Devuelve un valor lógico FALSO (valor que también es posible escribir directamente como FALSO)
NO(valor)
Invierte el valor lógico del argumento (si el valor es FALSO pasa a VERDADERO y viceversa)
O(valor1; valor2,…)
VERDADERO si el valor de algún argumento es VERDADERO.
el primer argumento vale SI(valor; si_verdadero; si_falso) Si VERDADERO devuelve el valor del segundo, si no, del tercero.
VERDADERO()
Devuelve un valor lógico VERDADERO (valor que también es posible escribir directamente como VERDADERO)
Y(valor1; valor2;…)
FALSO si el valor de algún argumento es FALSO. 12
Funciones Lógicas Ejemplos: • =3+SI(A2>4;3;4) • =SI(B1<60;”El producto es barato”; “El producto es caro”) • =SI(Y(A3>=4;A3<5);”Revisar Examen”,””) 13
Funciones de Fecha y Hora MS EXCEL emplea un sistema de representación de fechas desde el 1900, en el cual los números de una serie corresponden a fechas comprendidas entre el 1/1/1900 y el 31/12/9999. EXCEL almacena las fechas como números secuenciales, de forma que se puede operar con ellas. 14
Funciones de Fecha y Hora FUNCIÓN
RESULTADO
AHORA()
Hora actual
HOY()
Fecha actual
AÑO(Fecha o Número serial)
Devuelve el año correspondiente a una fecha o número serial
15
MODULOS Visual Basic Macrofunciones: Funciones definidas por el usuario, se guardan en módulos y están escritas en VB. El usuario a de teclear la definición de las macrofunciones en un módulo. Estructura: Function Nombre Función (Args) …Cuerpo de la funcion End Function 16
MODULOS Visual Basic
17
MODULOS Visual Basic
18
Buscar Objetivos EXCEL ofrece la posibilidad de calcular el valor que debe tomar una variable para (en nuestro caso una celda) para que el resultado final de ciertos cálculos sea un valor deseado. Buscar objetivo es parte de una serie de comandos a veces denominados herramientas de análisis Y si. En el caso de que conozca el resultado deseado de una fórmula sencilla, pero no la variable que determina el resultado, podrá utilizar la función Buscar objetivo haciendo clic en Buscar objetivo en el menú Herramientas. Al realizar una búsqueda de objetivo, Microsoft Excel varía el valor de celda específica hasta que una fórmula dependiente de dicha celda devuelve el resultado deseado. 19
Buscar Objetivos
20
Formulas Matriciales Si deseamos realizar cálculos de tipo matricial, debemos seleccionar los rangos correspondientes a las matrices donde operar. Estos rangos deben tener las dimensiones oportunas. Para conseguir que la formula sea interpretada como MATRICIAL debemos pulsar CTRL+MAYS+INTRO para validarla. Para eliminarla, debemos seleccionar el rango completo donde se aplica. Las funciones de Excel que operan sobre matrices son MINVERSA, MMULT y MDETERM. 21
Informe de Tablas dinámicos Permite realizar resúmenes y analizar fácilmente datos. Puede agrupar hasta por tres campos distintos. No se actuliza de forma automática al cambiar los datos de entrada.
22
Validación de datos de entrada Permite asegurarse de que los datos a la entrada son correctos. Se accede mediante Datos->Validación El ejemplo típico es la posibilidad de seleccionar sólo valores de una lista.
23
Controles de programación Permiten realizar mini-aplicaciones usando cualquier objeto disponible en Visual Basic Lo habitual es usarlo para colocar botones u otros controles... Por ejemplo, una lista de valores de una tabla, mediante un Combobox. Al seleccionar uno, cambia el valor de una celda asociada... Para ver los controles, active su barra de herramientas: Ver -> Barra de herramientas -> Cuadro de controles. Para modificarlo, con el botón derecho haga click en propiedades de ese control. Para poder seleccionarlo haga click en el icono con aspecto de escuadra que aparece en la barra de herramientas de cuadro de controles para activar el modo diseño. En caso contrario se ejecutará la macro asignada... 24