Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 1
Copyright abril de 2015 por TECSUP
Excel avanzado: Frmulas, Funciones Avanzadas
Introduccin
El entorno de trabajo de Excel no tiene que lucir siempreigual.
Si eventualmente debe trabajar en una computadora que noes la suya, puede tener dificultades para encontrar lasherramientas de Excel que necesita debido a unapersonalizacin diferente a la suya.
Por otro lado, Excel ofrece facilidades para elaborar frmulascomplejas, as como para detectar posibles errores oresultados inesperados.
2
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 2
Objetivos
Personalizar el entorno. Elaborar frmulas usando referencias. Utilizar funciones matemticas, estadsticas y lgicas.
3
ndice Personalizacin del entorno
Opciones de Excel Listas personalizadas Barra de acceso rpido Formatos de nmeros
Manejo de ventana Inmovilizar paneles Dividir ventana
Uso avanzado de frmulas Definir nombres Auditora de frmulas Relaciones entre celdas Comprobacin de errores Convertir a valor
Funciones Matemticas Estadsticas Lgicas Trigonomtricas Ingeniera
4
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 3
Personalizar: Opciones de Excel En el office 2010 las opciones generales como Abrir, Guardar, Imprimir,
etc., se encuentran en la ficha Archivo, para poder personalizar lasdiferentes opciones por defecto que tiene el Excel, se seleccionar:Opciones, que permitir configurar por ejemplo la: Combinacin de colores Fuente Idioma Opciones de frmulas Opciones de guardar
5
Opciones de Excel: Listas Personalizadas Para poder personalizar las Listas debemos seleccionar la opcin
Avanzadas, y luego buscar el grupo General y presionar el botn Modificarlistas personalizadas.
6
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 4
Listas personalizadas Entre las listas personalizadas
estn los das de la semana y losmeses del ao.
Es posible crear una lista propiapara darle otro orden o parahacer relleno en serie.
Por ejemplo, una lista con lasreas de una empresa.
7
Ejercicio: Lista personalizada Abrir el archivo Clase 09.xlsx y ubicarse en la hoja Lista. Ir a Modificar Listas Personalizadas, haga clic en el rea de Entradas de lista,
e ingrese su nombre completo pero separado por comas y luego haga clic enAgregar.
Para salir haga clic en Aceptar y cierre las Opciones volviendo a Aceptar. En una celda en blanco escriba su nombre y luego copie de manera
adyacente.
8
Lista Ingresada
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 5
Personalizar: Barra de acceso rpido Por defecto, la Barra de
herramientas de acceso rpidocontiene los botones Guardar,Deshacer y Rehacer.
Con el botn Personalizar podemosactivar (o desactivar) los botones deuso frecuente. Asimismo, con estaopcin es posible mostrar la Barra deacceso rpido debajo de la Cinta deopciones.
Con la opcin Ms comandos sepueden observar los comandosdisponibles, agregar, quitar oreordenar los comandos de estabarra.
9
Personalizar: Ms Comandos
10
Subir
Bajar
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 6
Personalizar: Barra de estado Haciendo clic derecho sobre la
Barra de estado es posible activar /desactivar los diferentesindicadores que puede contener: Modo de celda Zoom Grabacin de macros Etc.
11
Personalizar: Formato de nmeros La forma en que se muestra un
nmero puede influir en su significadoy/o interpretacin.
La categora Personalizada de la fichaNmero permite construir un formatopropio combinando determinadossmbolos (#, 0, ?, @, colores, etc.) demodo que la informacin se muestrecomo el usuario desee.
La idea es que con la estructura delformato del nmero es posible aplicarun mismo formato a un grupo de datosno necesariamente contenidos en unamisma columna, y que en funcin asus valores se le aplique el formatoadecuado.
12
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 7
Personalizar: Formatos de nmeros El formato de una celda puede especificarse hasta con 4 secciones
separadas por punto y coma, cada una de las cuales representa elposible formato que adoptar la celda dependiendo del nmerocontenido: Si es positivo, adoptar el primer formato (color azul, dos decimales,
separacin de miles). Si es negativo, adoptar el segundo formato (color rojo, dos decimales,
separacin de miles). Si es cero, adoptar el tercer formato (tres dgitos). Si es un texto, adoptar el cuarto formato (texto concatenado).
13
Ejemplo de Formato
Personalizado
[Azul]#,###.00;[Rojo](#,###.00);000;"ventas de "@
POSITIVO ; NEGATIVO ; CERO ; TEXTO
Ejercicio: Formato de nmeros Ubicarse en la hoja Formatos. Colocarle al Dato, el formato personalizado que se muestra, y observe el
efecto logrado. Colocar en la columna de Dato, varios valores, positivos, negativos, cero,
texto y aplicar el formato de nmero a ellos. Cambie el color para cada seccin. Indique que cuando es cero se mostrar el texto: Sin valor. Y cuando hay texto, que acompae al mismo el texto: Ventas de:
14
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 8
Personalizar: Formatos de nmeros
15
Smbolo Descripcin Formato Valor Resultado
0 Muestra ceros no
significativos
00.000
000.0
1
0.123
01.000
000.1
# Muestra cifras significativas ##,###.### 1230.1 1,230.1
, (coma) Muestra el separador , #,### 1234567 1,234,567
? Aade espacios al final para
alinear el punto decimal
0.??? 1.2
1.22
1.222
1.2
1.22
1.222
@ Muestra cualquier texto
ingreso en la celda
formateada
Ventas de @ Juan Ventas de Juan
[negro], [azul],
[verde], [amarillo],
[rojo],
[magenta],
[aguamarina]
Muestra la seccin del
formato en el color
especificado
Smbolos de uso frecuente:
Personalizar: Formatos de fecha y hora
16
Descripcin Formato Resultado
Horas
h
hh
[h]
0-23
00-23
0-24,25,26
Minutos
m
mm
[m]
0-59
00-59
0-60,61,62
Segundos
s
ss
[s]
0-59
00-59
0-60,61,62
Das
d
dd
ddd
dddd
0-31
00-31
Lun-Dom
Lunes-Domingo
Meses
m
mm
mmm
mmmm
1-12
01-12
Ene-Dic
Enero-Diciembre
Aosyy
yyyy
Dos dgitos
Cuatro dgitos
Smbolos de uso frecuente:
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 9
Manejo de ventana: Inmovilizar paneles Cuando se trabaja con hojas de clculo con mucha cantidad de
informacin puede ser necesario observar diferentes partes de la hoja ala vez.
Excel permite fijar la visin de las filas o columnas seleccionadas,mientras se observa otra zona de la hoja.
Esta opcin se encuentra en la ficha Vista, grupo Ventana.
17
Manejo de ventana: Inmovilizar paneles Para inmovilizar paneles,
seleccione la celda que estdebajo de la ltima fila que quierafijar, y a la derecha de la ltimacolumna que quiera fijar.
Ejercicio: En la hoja Ipaneles, si se
selecciona la celda B2 seinmovilizan paneles, de la fila 1 ycolumna A, el resto de filas ycolumnas se movilizan..
Una lnea vertical y una lneahorizontal sealarn el panelinmovilizado.
Volver a Movilizar paneles.
18
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 10
Manejo de ventana: Dividir La ficha Vista, grupo Ventana,
incluye la opcin Dividir. La ventana puede ser dividida:
En 2, verticales u horizontales En 4 sub-ventanas que
A estas divisiones, a diferencia delPanel inmovilizado, se les puededesplazar (scrolling) de maneraindependiente.
Ejercicio: En la hoja Ipaneles, se dividir en 2,
seleccione una celda por debajo de la fila,o a la derecha de la columna, del reaelegida.
Para dividir la ventana en 4, seleccioneuna celda que est por debajo de la ltimafila, y a la derecha de la ltima columna,del rea elegida.
19
Frmula en excel
20
Una frmula es una expresin que relaciona el contenidode las celdas de una hoja de clculo para realizar clculosy producir resultados, por ejemplo: Cunto ha sido laganancia? Los gastos estn dentro de lo presupuestado?
Las frmulas contienen operandos (referencias de celda yde rangos, valores constantes, funciones) y operadores(smbolos que relacionan a los operandos)
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 11
Referencias a celdas Las frmulas pueden utilizar valores ubicados en otras celdas, para lo cual
hacen referencia a esas celdas. Utilizar referencias a celdas en lugar de valores constantes permite que los
cambios en el contenido de las celdas ocurran sin necesidad de modificarla frmula.
21
Si varan el Precio o la Cantidad, habr
necesidad de modificar la frmula
Si varan el Precio o la Cantidad, la
frmula calcular el resultado sin ser
modificada
Referencia Relativa La Referencia Relativa consiste simplemente en especificar la fila y la
columna de la celda que se requiere en la frmula. Cuando una frmula es copiada a otra celda, las referencias relativas se
modifican en el nmero de filas y de columnas correspondientes.
22
En D2: =B2*C2 En D3: =B3*C3En D4: =B4*C4
En E2: =C2*D2 En F2: =D2*E2
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 12
Referencia Absoluta La Referencia Absoluta a una celda se especfica anteponiendo el
smbolo $ a la fila y $ a la columna. Cuando una frmula es copiada a otra celda, las referencias absolutas
permanecen sin ninguna alteracin.
23
En D2: =B2*$C$2 En D3: =B3*$C$2En D4: =B4*$C$2
En E2: =C2*$C$2 En F2: =D2*$C$2
Referencia Mixta La Referencia Mixta es aquella que hace referencia absoluta a la fila y
relativa a la columna, o referencia absoluta a la columna y relativa a la fila. Esto se logra anteponiendo el smbolo $ a la fila o a la columna.
24
Mixta a la Fila
Mixta a la Columna
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 13
Funcin en excel
Una funcin es una frmula predefinida por Excel por sufrecuente uso. Tiene un nombre, y entre 0 y 255 argumentos,algunos de los cuales pueden ser opcionales.
Los argumentos pueden ser nmeros, texto, valores lgicoscomo VERDADERO o FALSO, matrices.
25
Funciones Matemticas =ALEATORIO.ENTRE(num.superior,num.inferior)
Genera nmeros aleatorios entre un valor inferior y un valor superior.Ejemplo:Generar montos aleatorios de ventas al ao de una empresa con una base no menor a250,000 y no mayor a 750,000
26
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 14
Funciones Matemticas
=SUMA(a1,a2,a3)Devuelve la suma de todos los argumentos.
Funciones Estadsticas
=PROMEDIO(N1,N2)Devuelve el promedio (media aritmtica) de losargumentos.
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 15
Funciones Estadsticas =CONTAR.SI(Rango;Criterio)
Cuenta las celdas en el rango, que coinciden con la condicindada.
Funcin Lgica SI
Sintaxis=SI(condicional,expresin_verdad,expresin_falso)
Comprueba si se cumple una condicin (1er argumento) y devuelve unvalor (2do argumento) si se evala como VERDADERO y otro valor (3erargumento) si se evala como FALSO. Condicional: Cualquier valor o expresin que pueda evaluarse como
VERDADERO o FALSO. Utiliza operadores de comparacin. Expresin_Verdad: Es el valor que se devolver si la condicin es
verdadera. Expresin_Falso: Es el valor que se devolver si la condicin es falsa.
30
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 16
Funcin lgica SI - Ejemplo
La frmula en F2 es:
=SI(E2>=10.5,Aprob,Desaprob)
Los textos Aprob y Desaprob estn entre comillas porque NO sonvariables. Si se escriben sin comillas Excel asume que son variables y,al no encontrarlas, resultara en el error #NOMBRE.
31
Frmula: Definir nombres
Una frmula puede ser ms fcil de entender si se utilizanombres en vez de referencias de celda. Por ejemplo:
=SI(H21>10000,H21*5%,0)=SI(TOTAL>10000,TOTAL*5%,0)
Para crear un nombre bastar con seleccionar la celda o elrango de celdas y escribir el nombre en al Cuadro denombres y presionar luego ENTER.
Los nombres pueden contener hasta 255 caracteres (letras,nmeros, guin bajo), sin espacios, ni comillas.
32
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 17
Frmula: Definir nombres
Para administrar los nombresdel libro, use la fichaFrmulas, grupo Nombresdefinidos, botnAdministrador de nombres.
Con esta opcin puedemodificar, aadir o eliminarnombres.Ejercicio:
Abrir la hoja Opera Poner el nombre Lote a las celdas
(G3:G10). En la celda G12 realizar la suma
utilizando este nombre de celda.
33
Frmula: Auditora de frmulas Por defecto, Excel realiza automticamente el clculo de las frmulas y
muestra el resultado de stas. Haciendo clic en la opcin Mostrar frmulas, del grupo Auditora de
frmulas de la ficha Frmulas, Excel mostrar la frmula que hay encada celda. Con un nuevo clic en la misma opcin se ocultarn lasfrmulas y se volvern a mostrar los valores resultantes.
34
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 18
Frmula: Relaciones entre celdas
El grupo Auditora de frmulas de la ficha Frmulas,permite: Rastrear precedentes Rastrear dependientes Quitar flechas
35
Frmula: Rastrear precedentes y dependientes
Rastrear precedencia: muestracon flechas las celdas que hanintervenido en el contenido de lacelda seleccionada.
Rastrear dependencia: muestracon flechas las celdas cuyocontenido depende de la celdaseleccionada.
36
Elimina las flechas mostradas por las dos opciones anteriores.
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 19
Ejercicio: En la hoja Opera Mostrar las frmulas Ubicarse en la celda G3 Rastrear precedentes e indicar las
celdas: ________________________________
Rastrear dependientes e indicar lacelda: ________________________________
Quitar flechas
37
Frmula: Comprobacin de errores La comprobacin de
errores del grupoAuditora de frmulas dela ficha Frmulas tienedos pociones: Comprobacin de errores
busca y muestra errores en lasfrmulas
Rastrear error seala lasceldas que intervienen en unafrmula con error.
Ejercicio: En la hoja Opera, modificar el
valor de la celda C6 por lavocal a.
Ubicarse en la celda G12 yelegir rastrear error.
Quitar flechas.38
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 20
Frmula: Convertir a valor
Si alguna celda contiene una frmula cuyo resultado nunca semodificar, usted puede hacer que el valor resultante seconvierta en una constante, lo que evitar que vuelva acalcularse.
Seleccione la celda y presione F2 (o haga doble clic) paraactivar el modo edicin. Luego presione F9 y Enter.
39
Funciones Trigonomtricas Al utilizar cualquiera de las funciones trigonomtricas es
conveniente tener presente lo siguiente: El argumento ngulo es un ngulo expresado en radianes. Si tiene un ngulo en grados, puede convertirlo a radianes multiplicando
el valor por PI()/180, o utilizando la funcin RADIANES. Las funciones trigonomtricas retornan el resultado en radianes. Para
convertir a grados debe multiplicar por 180/PI() o utilizar la funcinGRADOS.
40
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 21
Funciones Trigonomtricas
COS(nmero)Calcula el Coseno de nmero.=COS(A2)
ACOSH(nmero)Devuelve el Coseno HiperblicoInverso del nmero.
=ACOSH(A3)
41
Funciones de Ingeniera: CONVERT La funcin CONVERT, convierte un nmero de un sistema de medidas a
otro. Por ejemplo, millas a kilmetros, o grados Celsius a Fahrenheit o aKlvines.
Utiliza 3 argumentos: el valor a convertir, la unidad de medida original y launidad de medida objetivo.Ejercicio:
Ir a la hoja Convertir Las frmulas siguientes convierten millas a kilmetros y a yardas, respectivamente:
=CONVERT(A2,"mi","m")/1000 CONVERT(A2,"mi", "km")=CONVERT(A2,"mi","yd")
42
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 22
Funciones de Ingeniera: CONVERTIR Las unidades de medida se expresan con abreviaturas, las cuales pueden
consultarse en la Ayuda de Excel. Deben respetarse las letras maysculas y minsculas. Para el sistema mtrico se pueden utilizar mltiplos. Por ejemplo, el prefijo
k, para kilmetros: km.
43
Unidad Abreviatura Unidad Abreviatura
Gramo g Newton N
Libra masa lbm Julio J
Metro m Electronvoltio ev
Milla mi Vatio hora Wh
Pulgada in Vatio W
Pie ft Tesla T
Angstrom ang Grados Celsius C
Pascal Pa Grados Fahrenheit F
Atmsfera atm Kelvin K
Excel AvanzadoFrmulas y Funciones
Entorno Frmulas
OpcionesBarra de acceso
rpido
Botones NmerosColores
Fuente
Personalizable por:
PersonalizaPersonaliza
Tiene un Elabora
Funciones Matemticas
Cinta de opciones
Personaliza:
Pueden utilizar
Dividir ventana
Inmovilizar paneles
Idioma
Clculo de frmulas
Ficha Vista
Autocorreccin
Permite
Formato de celda
Fechas
Pueden contener
Nombres
Se pueden
Auditar
Mostrando
Precedentes
Dependientes
Identifican
Celdas
Rangos
Funciones Lgicas
Funciones Estadsticas
44
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 23
Glosario
Nombre: denominacin alternativa, definida por el usuario, parauna celda o un rango de celdas.
Precedente: celda cuyo valor influye, directa o indirectamente, enel contenido en otra.
Dependiente: celda cuyo contenido est influido, directa oindirectamente, por el contenido de otra.
Referencia circular: cuando una frmula hace referencia a la celdadonde est ubicada.
Registro: conjunto de atributos o caractersticas (campos) de unente (persona, objeto, concepto). Ejemplo: el ente CLIENTE puedetener los atributos Cdigo, Apellido, Direccin, Fecha deNacimiento, etc.
Campo: un atributo o caracterstica con un valor especfico.Ejemplo: Precio, Fecha de venta.
Filtro: opcin para ocultar / mostrar las filas que cumplan unaespecificacin determinada.
45
http://office.microsoft.com/es-es/excel-help/novedades-de-excel-2010-HA010369709.aspx?CTT=1#_Toc274394078
http://www.uv.mx/personal/llopez/files/2013/03/Manual-Microsoft-Office-Excel-2010.pdf
http://www.aulaclic.es/excel2010/index.htm http://www.aulafacil.com/excel-2010/curso/Temario.htm
Enlaces
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 24
Fernando Rosino Alonso. Excel 2010. Grupo AnayaComercial. 2010.
Walkenbach, John. La Biblia de Excel 2010. Grupo AnayaComercial. 2010.
Harvey, Greg. Microsoft Office Excel 2010 for dummies. WileyPublishing Inc. 2010.
Se ha utilizado como consulta la pgina de MicrosoftLatinoamrica: http://office.microsoft.com/es-es/excel-help/informacion-general-
RZ101773335.aspx?CTT=1§ion=1
Referencias Bibliogrficas
Preguntas
1. Si selecciona la celda D9 e inmoviliza paneles la columnaD quedar fija y siempre visible?
2. Dnde se encuentra la opcin para dividir la ventana envarios paneles?
3. Cul es el efecto de la opcin Rastrear error?
48
Informtica Aplicada: Unidad 9 Tecsup
Dpto. de Informtica 25
Respuestas
1. Si selecciona la celda D9 e inmoviliza paneles la columna Dquedar fija y siempre visible? No. Las columnas A hasta C quedarn fijas y siempre visibles.
2. Dnde se encuentra la opcin para dividir la ventana envarios paneles? En la ficha Vista, grupo Ventana.
3. Cul es el efecto de la opcin Rastrear error? Muestra con flechas las celdas precedentes
49