Funciones Avanzadas deEXCEL

Informática Aplicada a la Construcción

Universidad Politécnica de CartagenaCurso 2006/2007

Francisco Joaquín Escribá Pérez

2

FUNCIONES AVANZADAS

ORDENACIÓNFILTROSSUBTOTALESFUNCIONESMODULOS VB (MS Visual Basic)BUSCAR OBJETIVOS

3

FILTROS

AUTOFILTROAVANZADOS

4

AUTOFILTRO

En los Autofiltros, EXCEL elige como criterio de filtrado los encabezados de las columnas de las tablas. P.e:

5

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.

6

FILTROS AVANZADOS

7

SUBTOTALESMicrosoft 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.

8

SUBTOTALES

9

SUBTOTALES

10

Funciones EXCEL

Tipos de Funciones• Matemáticas y

Trigonométricas• Estadísticas• Lógicas• Fecha y Hora

11

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.

12

Funciones Lógicas

FALSO si el valor de algún argumento es FALSO.Y(valor1; valor2;…)

Devuelve un valor lógico VERDADERO (valor que también es posible escribir directamente como VERDADERO)

VERDADERO()

Si el primer argumento vale VERDADERO devuelve el valor del segundo, si no, del tercero.

SI(valor; si_verdadero; si_falso)

VERDADERO si el valor de algún argumento es VERDADERO.O(valor1; valor2,…)

Invierte el valor lógico del argumento (si el valor es FALSO pasa a VERDADERO y viceversa)

NO(valor)

Devuelve un valor lógico FALSO (valor que también es posible escribir directamente como FALSO)

FALSO()

13

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”,””)

14

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.

15

Funciones de Fecha y Hora

Devuelve el año correspondiente a una fecha o número serial

AÑO(Fecha o Número serial)

Fecha actualHOY()

Hora actualAHORA()

RESULTADOFUNCIÓN

16

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 funcionEnd Function

17

MODULOS Visual Basic

18

MODULOS Visual Basic

19

Buscar ObjetivosEXCEL 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.

20

Buscar Objetivos

21

Formulas MatricialesSi 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.

22

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.

23

Validación de datos de entrada

Permite asegurarse de que los datos a la entrada son correctos.

Se accede mediante Datos->ValidaciónEl ejemplo típico es la posibilidad de

seleccionar sólo valores de una lista.

24

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...