2.- Excel 2007 Intermedio

Embed Size (px)

Citation preview

MS EXCEL 2007 INTERMEDIO

I II

Introduccin ............................................................................................................................. 1 Funciones................................................................................................................................. 2 1 2 Asistente de Funciones ........................................................................2 Funcin SI ANIDADO...........................................................................5 2.1 Operador Y............................................................................... 12 2.2 Operador O: ............................................................................. 13 2.3 Comparaciones: .......................................................................... 14 2.4 Ejercicios de la Unidad.................................................................. 15 3 Otras Funciones ............................................................................... 16 3.1 Sumar.Si .................................................................................... 16 3.2 Funciones del tipo CONTAR ........................................................... 17 3.3 Funciones de Fecha...................................................................... 20 3.4 Funciones Matemticas................................................................. 21 3.5 Funciones de Texto ...................................................................... 23 3.6 Funciones en Matrices .................................................................. 27 3.7 Funciones de Bsqueda ................................................................ 31 4 Manejo de Datos en Excel .................................................................. 33 4.1 Ordenar Datos............................................................................. 33 4.2 Subtotales .................................................................................. 35 4.3 Texto en Columna........................................................................ 38

III

Frmulas y reas ................................................................................................................... 42 1 2 3 4 Definicin de reas ........................................................................... 42 Desplazarse por celdas remotas.......................................................... 45 Modificar y Borrar reas .................................................................... 45 Ejercicio de la Unidad ........................................................................ 46

IV V

Auditoria de datos ................................................................................................................. 47 1 Seccin Auditora.............................................................................. 47

Trabajo con libros ................................................................................................................. 50 1 2 3 Organizacin de los libros abiertos ...................................................... 50 Divisin de ventanas en paneles ......................................................... 51 Inmovilizacin de secciones ............................................................... 53

VI

Pegado Especial .................................................................................................................... 55 1 Pegar Vnculos ................................................................................. 55

ii

MS EXCEL 2007 INTERMEDIO 2 Otras opciones ................................................................................. 56

VII Proteccin .............................................................................................................................. 581 2 3 4 5 Introduccin .................................................................................... 58 Proteccin de hojas........................................................................... 59 Proteccin de celdas.......................................................................... 59 Proteccin de libros........................................................................... 60 Proteccin de archivos....................................................................... 61

VIII Tablas Dinmicas .................................................................................................................. 62 IXSolucin de Ejercicios: ......................................................................................................... 65 1 2 3 Ejercicios de la Unidad Si ................................................................... 65 Ejercicios Otras Funciones.................................................................. 65 Ejercicio BuscarV .............................................................................. 65

X

Anexo...................................................................................................................................... 66 1 Anexo de Funciones .......................................................................... 66 1.1 Funciones matemticas y trigonomtricas ....................................... 66 1.2 Caso Especial SUBTOTALES........................................................... 66 1.3 Funciones lgicas......................................................................... 67 1.4 Funciones de texto....................................................................... 69 1.5 Funciones de bsqueda y referencia ............................................... 69 1.6 Funciones estadsticas .................................................................. 71 1.7 Funciones de fecha y hora............................................................. 71 1.8 Funciones financieras ................................................................... 71 1.9 Frmulas Matriciales .................................................................... 71 2 Anexo de Comandos Rpidos.............................................................. 73 2.1 Teclas para introducir datos .......................................................... 73 2.2 Teclas para introducir datos en una hoja de clculo .......................... 73 2.3 Teclas para trabajar en las celdas o la barra de frmulas ................... 73 2.4 Teclas para modificar datos ........................................................... 74 2.5 Teclas para insertar, eliminar y copiar una seleccin ......................... 74 2.6 Teclas para moverse dentro de una seleccin................................... 74 3 Anexo de Teclas para seleccionar datos y celdas ................................... 75 3.1 Teclas para seleccionar celdas, columnas o filas ............................... 75 3.2 Teclas para ampliar la seleccin con el modo Fin activado.................. 76 3.3 Teclas para seleccionar celdas con caractersticas especiales .............. 76

iii

MS EXCEL 2007 INTERMEDIO INTRODUCCIN

I

IntroduccinEl objetivo de un curso de Excel Intermedio principalmente es lograr entregar las herramientas que a Ud le permitan el manejo de conceptos y tcnicas para personalizar los libros de trabajo y utilizar funciones que faciliten el manejo complejo de datos. Adems, se pretende que, una vez finalizado el curso, usted sea capaz de realizar clculos que dependan de ciertas condiciones especficas, para lo cual se revisarn las funciones de clculo condicional. Se dar especial nfasis al manejo de extensas listas de datos en Excel, las que tcnicamente denominamos bases de datos. Para ello se revisarn herramientas y funciones incorporadas en Excel tales como Ordenar, Subtotales, Filtros, funciones de bsqueda de datos y funciones de clculos en bases de datos. Esperamos que una vez revisados estos temas, y realizados los ejercicios propuestos en este manual y por el relator, usted pueda sacar ms provecho a Excel y solucionar problemas que, a travs de las herramientas convencionales, demandaran mayor tiempo y, generalmente, provocaran resultados poco precisos.

1

MS EXCEL 2007 INTERMEDIO FUNCIONES

II

Funciones1 Asistente de Funciones El Asistente de Funciones permite al usuario de Excel utilizar en forma sencilla la amplia gama de funciones que ste trae incorporadas, a travs de cuadros de dilogo que facilitan la entrada de datos y la comprensin de la funcin. Bsicamente, el Asistente de Funciones trabaja con datos ingresados en rangos o en celdas que contienen la informacin puntual que utilizar la funcin. Ejemplo con Funcin PROMEDIO En el ejemplo siguiente, se ver la forma en que trabaja el asistente de funciones.

a) Construya la siguiente tabla:tem MTB MPC SIMM DD TCP KMM T-VD 1 Sem. 55.000 50.000 50.000 105.000 14.000 185.000 180.000 2 Sem. 64.900 59.000 59.000 123.900 16.520 218.300 754.020 Promedio

b) Ubquese en la celda donde desea que aparezca el resultado de la funcin (en este caso, la celda que contendr el promedio del primer y segundo semestre del tem MTB). c) Presione el botn del Asistente de Funciones Esto har aparecer el siguiente cuadro de dilogo, en el cual deber seleccionar la categora Estadsticas y la funcin Promedio

2

MS EXCEL 2007 INTERMEDIO FUNCIONES

Este cuadro nos muestra las categoras1 en las cuales estn clasificadas las funciones de Excel, el nombre de la funcin y, en la parte inferior, la sintaxis o forma de escribir la funcin. d) Al presionar el botn Aceptar aparecer el siguiente cuadro:

1

De no saber a qu categora pertenece una determinada funcin, se debe seleccionar la categora Todas, que muestra todas las funciones ordenadas alfabticamente

3

MS EXCEL 2007 INTERMEDIO FUNCIONES e) En el cual debe sealar el rango que aportar los datos para calcular el promedio. Tambin es posible presionar la flecha de color rojo que aparece junto a la casilla Nmero 1, lo que har desaparecer este ltimo cuadro para as marcar el rango con el Mouse. Luego podemos presionar [Enter], con lo que obtendremos nuestro promedio, apareciendo lo siguiente en la celda que tenamos seleccionada: =PROMEDIO(B2:C2) Ejemplo con funcin PAGO Otra forma que tiene el Asistente de solicitar datos es a travs de celdas puntuales que contengan informacin necesaria para la funcin. As ocurre en el siguiente cuadro:

Esta funcin (PAGO, en la categora Financieras) solicita las celdas en las que se encuentra la Tasa de Inters Mensual, el Nmero de Cuotas y el Valor de un prstamo, para calcular el monto de la cuota. Para esto se debe ingresar, en cada casilla, la referencia de la celda correspondiente, quedando de la siguiente forma: =PAGO(A1;A2;A3) Suponiendo que es en esas celdas donde se encuentra la informacin. Los punto y coma entre cada trmino de la funcin indican que no son rangos, sino datos independientes.

4

MS EXCEL 2007 INTERMEDIO FUNCIONES 2 Funcin SI ANIDADO Para comenzar debe entender que la funcin SI es la manera de poder tener ms de una solucin ante algn problema. La sintaxis de esta funcin es: = SI ( PRUEBA LOGICA ; VALOR VERDADERO ; VALOR FALSO ) Esta es la base de la funcin donde: Prueba lgica : es el parmetro seccin donde realiza una aseveracin que de ser cierta arrojar el resultado que se encuentra en el VALOR VERDADERO. En cambio si la prueba lgica no se cumple, entonces entregar el resultado que aparece en el VALOR FALSO. Valor Verdadero : en este parmetro se puede asignar un texto, un nmero, una celda, una frmula o una funcin. Valor Falso : al igual que en el parmetro anterior, puede contener un texto, un nmero, una celda, una frmula o una funcin. El ingreso de parmetros tiene el mismo orden. A continuacin se presenta la funcin Si a modo de diagrama de posibles resultados a partir de la condicin:

V a lor S i C ond ic = V erda de ro V S i C on dic in F V alor S i C on dic = F als o

Ilustracin 1 Diagrama para la confeccin de la funcin SI

El diagrama anterior corresponde a la construccin de la siguiente funcin SI: = SI (Condicin ; Valor Si Condic = Verdadero ; Valor Si Condic = Falso)

5

MS EXCEL 2007 INTERMEDIO FUNCIONES Ejemplo 1 Debe construir una funcin que de acuerdo al monto de la celda A3, debe calcular la fraccin de esta. Para el caso: Si la cifra de la celda A3 es superior a 100 entonces deber calcular el 5% de la misma celda para cualquier otra cifra se deber calcular el 3% de esa celda.

Usando el diagrama quedara lo siguiente:

A 3 * 5%

S i A 3 > 100

A 3 * 3%

Ilustracin 2 Diagrama aplicado de la funcin Si

= SI ( A3 > 100 ; A3 * 5%; A3 * 3% )Prueba lgica: Compara la cifra que est en A3 con 100 Valor Verdadero: Multiplica el valor de la celda A3 con 5% Valor Falso: Multiplica el valor de la celda A3 con 3%

200 x 5% = 10

10 x 3% = 0,3

Luego, al comprender el funcionamiento que tiene la Funcin Si podr darse cuenta que se puede incluir otra funcin SI en el VALOR VERDADERO tanto como en el VALOR FALSO, naciendo de esta forma el termino de SI ANIDADO.

6

MS EXCEL 2007 INTERMEDIO FUNCIONES

Es decir un SI dentro de otro SI, teniendo una sintaxis de esta forma:VALOR FALSO

= SI ( P. L. ; VALOR VERDADERO ; SI ( P. L. ; V. V. ; V. F. ) )

Resultado 1

Resultado 2

Resultado 3

Grficamente es:

V a lor S i C on d ic = V e rda d e ro V S i C o n d ic i n F S i 2da C on d ic i n V V a lo r S i 2d a C o n d ic = V e rd a de ro

F V a lo r S i 2d a C o n dic = F a ls o

Ilustracin 3 Diagrama Construccin Funcin Si Anidado

El anidar la Funcin SI Anidado le da la opcin de tener no solamente 2 alternativas de salida como en el caso anterior, sino que puede tener 3 (como en la grfica), y ms an. El S Anidado se utiliza bsicamente cuando lo que manda el resultado (Prueba lgica) esta parcelada en ms de una posibilidad.Como Regla, si se tienen 3 resultados se requieren 2 funciones Si. Si se requieren 5, se requieren 4, y as.

Ejemplo 2 Debe confeccionar una funcin que de acuerdo al monto de la celda A3, puede arrojar tres posibles clculos diferentes: si el monto de la celda A3 es inferior a 100 entonces deber calcular el 5% de la misma celda,

7

MS EXCEL 2007 INTERMEDIO FUNCIONES si el monto est entre 100 y 200 calcular el 3% de esa celda para el resto de los casos calcular el 1% de la celda.

A 3 * 5% V S i A 3 < 10 0 F S i A 3< = 200 F A 3 * 1% V A 3 * 3%

Ilustracin 4 Diagrama Funcin Si aplicada=SI( A3.

63

MS EXCEL 2007 INTERMEDIO TABLAS DINMICAS

Panel de Tabla Dinmica

Vista de Dinmica

Tabla

Para disear la tabla dinmica de toman los campos de la tabla y se van ubicando en los distintos sectores fila, columna, datos o valores los elementos que se usarn, La seccin PAGINA har que ese campo, por ejemplo Ao, sea convertido en una lista desplegable, la que nos muestra los aos disponibles. Al seleccionar alguno de ellos, el contenido de la tabla cambiar para mostrarnos slo los datos correspondientes a ese Ao. Tambin se incluye, en la lista desplegable, la opcin (Todos), que nos mostrar toda la informacin, independiente del Ao. La seccin COLUMNA nos permite ubicar los campos que deseamos que nuestra tabla muestre ordenados en columnas. Puede ser ubicado ms de un campo en esta seccin. De ubicar ms de un campo, el primer campo ubicado aparecer subdividido por el segundo. De ubicar un tercer campo, ste subdividir, a su vez, al segundo. La seccin FILA funciona exactamente igual que la seccin COLUMNA, con la diferencia que los datos puestos all estarn ordenados en filas. La seccin DATOS sirve para ubicar los campos que deseamos que la tabla muestre como resultado o valor.

64

MS EXCEL 2007 INTERMEDIO SOLUCIN DE EJERCICIOS:

IX

Solucin de Ejercicios:1 Ejercicios de la Unidad Si 1.=PROMEDIO(B2:D2) =SI(E2