I T A MDivisión Académica de Ingenierias

Centro de Cómputo

Tercer Módulo

Diplomado en Microcomputación

1997-1998

Prof. Alberto Espinosa González

• Aquí va el prefacio

• Aquí va el índice.

Construcción de Documentos con EXCEL

• Identifique las metas del proyecto, los requerimientos de entrada/salida y los cálculos que se deben hacer.

• Construya su hoja, utilizando etiquetas, valores y fórmulas

• Pruebe su hoja de trabajo, asegurandose que se obtengan los resultados correctos.

• De formato a su hoja.

• Documente su trabajo, registrando en la hoja toda la información necesaria para que otros lo entiendan.

EXCELEXCEL es un programa de Hoja Electrónica de Cálculo de la empresa MICROSOFT.

EXCEL utiliza el ambiente Windows como medio de comunicación con el usuario.

Además, permite la importación y exportación de archivos con otros paquetes de MICROSOFT de una forma totalmente transparente.

EXCEL se utiliza en aplicaciones numéricas que son propias para el análisis orientado a renglón y columna.

Ejemplos de estas aplicaciones son los presupuestos, propuestas, modelos financieros y muchas otras aplicaciones como calcular resultados del tipo “¿que pasa si ..?”.

Para facilitar los cálculos, el desarrollo de las fórmulas de trabajo son dinámicas.

Tiene incorporado un grupo de funciones avanzadas.

EXCEL también es un Programa de Gráficas, teniendo una gran capacidad para esto.

EXCEL también es una Herramienta De Administración De Base De Datos, es decir se puede procesar preguntas de búsqueda y generar reportes.

EXCEL ofrece también un ambiente de Desarrollo De Aplicación, sin ser un lenguaje de programación.

La hoja de Cálculo• Es una cuadricula rectangular con 256

columnas y 16,384 filas o renglones.

• La intersección entre cada columna y fila se llama CELDA.

• La celda es la unidad básica de una hoja de cálculo.

• Las columnas aparecen rotuladas con letras y las filas por número.

• En una celda se almacenan datos.

Como entrar a EXCEL

De un clic aquí

Al llamar a este programa, se abrirá la aplicación que es EXCEL.Recuerde que en el ambiente WINDOWS mientras aparezca el cursor en forma de reloj de arena, deberá esperar, ya que está procesando la tarea solicitada.

Antes de utilizar EXCEL, primero debemos conocer los elementos que componen la hoja de Cálculo.

• BARRA DE MENÚS

Posiblemente en su pantalla no se vea exactamente igual a esta pantalla. Esto se debe a que debe configurarla de la siguiente manera

Haga un clic dentro de la barra de herramientas en la opción View.

Aparecerá un submenu, tome la opción Toolbar

Habilite las opciones de Standard y la de Formatting

Oprima el botón de OK

• BARRA DE HERRAMIENTAS

• BARRA DE FORMATOS

BARRA DE FÓRMULAS

• BARRA DE ESTADO

BARRAS DE DESPLAZAMIENTO

• Encabezados de Columnas

• Encabezados de renglón

BARRA DE MENÚS

BARRA DE HERRAMIENTAS

BARRA DE FÓRMULAS

BARRA DE FORMATOS

BARRA DE ESTADO

BARRAS DE DESPLAZAMIENTO

• Señalador de celda

Ventana Activa

• Las referencias de celda se basan en encabezados de columna y fila, ejemplo: A5, C10, F67.....etc.

• Una referencia identifica a una celda.

Referencias

El teclado y sus funciones TECLA FUNCIÓN Alfanuméricas Introduce caracteres de texto en la celda.

Numéricas Introduce valores dentro de la celda.

Teclas F Teclas de función para ejecutar comandos.

Conjunto de Cuando Bloq Num está oprimido (led teclas encendido) introduce números, numéricas Cuando Bloq Num no está oprimido,

(lado derecho) ( led apagado) se pueden usar las funciones que índica cada tecla.

Flechas Mueve el señalador de celda según indique la orientación de la flecha.

PgUp Mueve el señalador de celda una pantalla hacia arriba.

Pg Dn Mueve el señalador de celda una pantalla hacia abajo.

Home Mueve el señalador de celda a la columna A del registro actual.

Ctrl Se utiliza en conjunto con otras teclas P.E. Ctrl/Home ubica el señalador al principio de la hoja, Ctrl/End ubica el señalador en la última celda de inf.

Enter Introduce una entrada dentro de una Celda, para completar solicitudes y seleccionar comandos.

Creación de una hoja de cálculo nueva

• Al entrar al programa EXCEL, esta aplicación lo deja inmediatamente en una hoja de cálculo en blanco predeterminada.

• EXCEL organiza su trabajo en Libros, y estos a su vez contienen hojas.

Nombre del libro– En este momento no hemos dado nombre a nuestro libro

(archivo) por lo que toma el nombre por omisión de Book1.

Nombre de las hojas que componen al libroEn este momento no hemos dado nombre a cada hoja, por lo que

toma los nombres por omisión de Sheet1, Sheet2,....

Guardado de un archivoSe pueden tomar los iconos de la barra de herramientas o bien tomar las opciones de la barras de menús.

De la barra de MENÚS, en la opción File hay 2 formas:•El comando Save As...

– Para guardar el documento por primera vez y asignarle un nombre al libro.

•El comando Save– Para guardar los cambios a un documento ya existente, guardandolo

con el mismo nombre con el que se guardo inicialmente, reemplazando la versión anterior.

Diferencias entre SAVE y SAVE AS...

Inicio

¿Hasguardado elarchivo

antes?¿Quieresconservar elel mismo nombre?

NOSI

SI NO

Usa el comando SAVE

Usa el comandoSAVE AS...

Usa el comandoSAVE AS...

Salir de EXCEL

Después de haber salvado su archivo, se escoge de la barra de MENÚS, la opción FILE y después se escoge la opción EXIT.

Nota: Si no ha salvado su archivo, en este momento le preguntará si desea hacerlo.

FormatosCambiar el ancho de una columna

• El número de letras o números que Excel despliega en una celda depende del tamaño, estilo o tipo de letra ya predeterminado que comúnmente es de 9 dígitos.

• Si la celda adyacente esta desocupada, Excel ocupara momentáneamente dicho lugar, pero si esta ocupada, entonces solamente desplegará al ancho del tamaño predeterminado.

• Es importante aclarar que si se está ingresando un texto, este será cortado al tamaño predeterminado, pero si son datos numéricos, este desplegará una serie de caracteres ####.

• En ambos casos es posible cambiar el ancho de columna de la siguiente forma:

– Seleccione el encabezado de columna con un clic, de tal forma que ahora queda sombreado.

– Ubique el cursor del Mouse en el contorno derecho del encabezado de columna y observará que ahora el apuntador cambia de forma a una cruz delgada.

– Oprima el botón izquierdo del Mouse y sin soltarlo arrastre hasta el lugar deseado y suelte el botón del Mouse.

Formato de números y caracter• Formatear es cambiar la apariencia de los datos en las

celdas.• Los formatos solo cambian la apariencia, pero NO el

texto o valor de una celda.• Al introducir los datos a EXCEL, se sigue un formato

general, el cual alinea los números a la derecha y los textos a la izquierda.

• Para cambiar el formato general, se puede utilizar las barras de herramientas

• o el menú principal.

Formatear con el Menú Principal

• Se toma la opción de Format.

• Del submenu se toma la opción de Cells... Ctr+1

• Aparecerá un cuadro de dialogo, y se tomarán las opciones como se desee según las necesidades, siguiendo el mismo formato visto en WORD.

•Para dar formato a una celda– Seleccione la información que desea dar algún

formato– Abrir el menú FORMAT-->CELL– Escoger el tipo de información al que desea dar

formato, número, letra, fecha, hora, etc.– Escoger el tipo de formato – Dar clic en OK.

En ese momento parecerá la información con el formato aplicado a las misma.

Introducción de Datos• Se selecciona la celda o rango de celdas.

• Se escriben lo datos.

• Si desea cancelar, antes de oprimir la tecla <Enter>, oprima la tecla <Esc> o tome la opción de cancelar de la barra de Fórmulas.

• Si desea termina la inserción de datos en la celda, oprima <Enter> o la opción de aceptar de la barra de Fórmulas.

Introducción de datos en un rango de celdas

• Si desea introducir datos solamente en un rango de celdas:– Seleccione el rango de celdas

– Introduzca los datos en la celda y oprima <Enter>

• Si desea repetir los mismos datos en varias celdas:– Seleccione el rango de celdas

– Introduzca los datos en la primera celda

– oprima <Ctr> y <Enter>

El señalador de CeldasDe acuerdo a sus funciones, se modifica el cursor en 3 formas:

•se usa para llevar la celda a otro lugar (Cut y paste).

•Se usa para señalar un rango de valores de la hoja.

•Se usa para llenar un rango de valores (copy)

Uso del cursor de llenado automático

• Se usa cuando se desea llenar un rango de celdas :– Con los mismos valores.– Con un valor seriado aumentado en cada celda.

• O cuando se desea borrar un rango de celdas.

Llenado automáticoEl trabajo de llenar un rango con una serie de valores es un trabajo cotidiano.

• Hay dos formas de hacer un llenado– La primera es escribiendo en dos celdas

adyacentes el primer valor inicial y en la celda siguiente el valor mas el incremento. Después se selecciona las 2 celdas y con el botón de autollenado, se selecciona toda el área que desea se llene con los nuevos valores.

– La segunda forma es, primero se escribe en la celda inicial el primer valor, después se toma la opción Edit-->Fill-->Series y se toma las opciones llenar por columna o renglón según se desea, el incremento y el valor máximo a que puede llegar la serie.

Estas opciones son también útiles cuando mezclamos texto con números y deseamos llenar con el mismo texto pero con valor aumentado en la serie, por ejemplo :

calif1,calif2,calif3,calif4.......etc.

Introducción de datos y fórmulas

En las celdas se introducen valores constantes y fórmulas.

Un valor constante es un dato que se introduce directamente en una celda y puede ser un valor numérico o un texto.

Una fórmula es una secuencia de valores constantes, referencias de celdas, funciones u operadores que producen un nuevo valor.

Las fórmulas siempre comienzan con el signo =

Edición en la barra de FórmulasEn la barra de fórmulas es donde se editan los datos de las celdas de la hoja de cálculo.

La edición es el poder cambiar una entrada de celda en la hoja, escribiendo una nueva entrada sobre una ya existente.

Si se desea editar sólo una parte de la entrada, se debe de seleccionar dicha celda y con el apuntador del Mouse ubicarse en la parte a editar.

– La activación de la barra de fórmulas se obtiene presionando <F2>

Introducción de números como constantes

Se selecciona la celda y se escribe el número

Los números pueden incluir caracteres numéricos y cualquiera de los siguientes caracteres:

+ - ( ) , / $. E e

Cualquier otro carácter aparte de los ya mencionados se interpretará como texto.

Por omisión, EXCEL presenta los números en formato de número estándar, tan preciso como sea posible.

Usando el formato de números enteros (126)

Usando el formato de fracciones decimales (5.68)

O una notación científica (7.89E+08), si el número es más grande que el ancho de la celda.

• Recuerde que en notación científica , el número que sigue después de la E es el desplazamiento que debe hacerse del punto decimal hacia la derecha si es positivo o hacia la izquierda si es negativo, completando con ceros los lugares que van quedando vacíos.

EjemploLa representación en notación científica de:

.00000000056 = 5.6E-10

10,000,000,000 = 1.0E+10

Si el número en notación científica es aún demasiado grande o si la celda tiene algún formato diferente, EXCEL presentará una serie de signos ###### en la celda.

– Recuerde que para ver los datos debe agrandar el ancho de la columna

Asignación de Formato a Números

• Se selecciona la celda.

• Se elige: Format -> Cells...

• En el folder Number en la opción “Category” se elige la categoría de formatos para los datos seleccionados.

• Se elige O.K.– Si hay algún dato, en la parte inferior se verá un

ejemplo del dato con el formato seleccionado.

Edición de celda• Cut. Elimina los caracteres seleccionados en la entrada de la

celda y los coloca en el Clipboard, reemplazando el contenido del mismo.

• Copy. Se usa para hacer una copia de los caracteres seleccionados y colocarlos en el Clipboard, reemplazando el contenido del mismo.

• Paste. Con este comando se coloca el contenido del Clipboard en el punto de inserción

• Clear. Se usa para eliminar los caracteres seleccionados. Los caracteres no se guardan en el Clipboard.

• Undo. Para deshacer o repetir la mayoría de los comandos, inmediatamente después de realizar una acción si es que hay algún valor a rescatar.

Introducción de FórmulasUna fórmula puede incluir•operadores•referencias de celda•constantes•funciones

Una fórmula combina valores con operadores, para producir un nuevo valor a partir de los valores existentes.

Una fórmula puede incluir referencias de celda en lugar de valores constantes.

Operadores que utiliza EXCEL

• Operador Aritmético

+ Suma

- Resta (o negación si se usa con un solo operando)

/ División

* Multiplicación

% Porcentaje

^ Operación exponencial

Operadores (continuación)

• Operadores de Comparación

= Igual

> Mayor que

< Menor que

>= Mayor o igual que

<= Menor o igual que

<> diferente

Operadores (Continuación)

• Operadores de Referencia

: Rango - Produce una referencia para todas las celdas que se encuentran entre 2celdas indicadas, incluyendolas. P.E. A1:B2 hace referencia a las celdas A1, A2, B1 y B2.

, Unión - Produce una referencia que incluye las 2 referencias. P.E. A1,B2 hace referencia a las celdas A1 y B2 únicamente.

Orden de evaluación de los operadoresSi se combinan varios operadores en una celda, las operaciones se efectúan en el siguiente orden:

( ) Paréntesis

: Rango

% Porcentaje

^ Función exponencial

* / Multiplicación y división

+ - Suma y resta

=<><=>=<> Comparación

Si la fórmula contiene operadores con la misma prioridad, EXCEL los evaluará de izquierda a derecha.

P.E. 50*5/10+10=35

Si se desea alterar el orden de evaluación se utiliza paréntesis para agrupar expresiones. EXCEL calcula primero las expresiones en paréntesis.

P.E.

50+30/10*2 (50+30)/10*2 50+30/(10*2)

50+3*2 80/10*2 50+30/20

50+6 8*2 50+1.5

56 16 51.5

EjemploEl calculo del las siguientes expresiones, da como resultado:

-5*10+2^3*10/40-30 -5*((10+2^3*10)/40-30)

-5*10+8*10/40-30 -5*((10+8*10)/40-30)

-50+8*10/40-30 -5*((10+80)/40-30)

-50+80/40-30 -5*(90/40-30)

-50+2-30 -5*(2.25-30)

-48-30 -5*-27.75

-78 138.75

Ejercicio (continuación)

• Con papel y lápiz calcule lo siguiente:• 800/10-(5*2^2*2)• 4+(2*(1+2)+56-34)• 5^2/4*(5*(1+2)+10)• 5+(2*(4/2+5)-3)• Ahora compruebe sus resultados, escribiendo

las fórmulas anteriores en EXCEL.

Definir referencias• Para una mayor claridad en la organización de los

datos, es recomendable distribuir los datos a través de varias hojas de cálculo , llamadas Sheet#.

• Para darle nombres personalizados a estas hojas se da lo siguiente:– Del menú Format -->Sheet -->Rename ...

– Por omisión se tiene 16 hojas y se tiene una barra de desplazamiento exclusivo para ello.

– Para ubicarse en cada una de las hojas, se da un clic en el botón donde aparece el nombre de la hoja.

El uso de referencias• Una formula puede utilizar datos de diferentes rangos

utilizando referencias a diferentes hojas del libro.

• Hay dos formas de hacerlo:– Si se introduce directamente escribiendo en la barra de

fórmulas, se escribe el nombre de la hoja donde se encuentran los datos, siguiendo la nomenclatura:

= nom_hoja!celda

P.E. =sheet2!c1:c5

– Indicando por medio del Mouse , estando en la celda que va a contener la formula, tanto la hoja como la(s) celda(s) correspondiente(s).

Crear vínculos con libros de trabajo.Así como hacemos referencia a las distintas hojas de nuestro libro de trabajo, también es posible hacer referencia a los distintos libros de trabajo con que se cuenta, teniendo la gran ventaja de que si se modifica el valor referenciado del libro origen, también se modifica automáticamente en el libro destino. A este proceso se le llama vincular libros de trabajo.

Entendiendo como libro origen, el nombre del archivo en EXCEL, donde se encuentra el contenido de la celda.

– Hay 2 formas de hacerlo• Si se conoce exactamente las direcciones de las celdas del

libro origen, se introduce directamente en la barra de fórmulas el nombre del libro, nombre de la hoja y la celda, siguiendo las nomenclatura:

– [nom_archivo]nom_hoja!Celda

– Ejemplo [VENTAS.XLS]Enero!C10

• La segunda es : Teniendo abierto el libro destino, ahora se abre el archivo origen:

– File -->Open -->nom_arch.

– Se selecciona la celda que se desea hacer referencia.

– Se hace un Copy

– Se da en Windows-->nom_arch. destino

– Se ubica en el lugar deseado

– Se da Edit -->Paste_Special -->Paste Link

Asignación de Nombres a las celdasUn nombre es un identificador creado por el usuario para referirse a una celda o a un rango de celdas.

Para crear el nombre hacer lo siguiente:

Se selecciona el rango de datos a darle nombre.

Se escoge Insert-->Name -->CreateAparecerá un cuadro de dialogo, donde le indicaremos las opciones deseadas.

La lista de nombres a rangos aparece en la parte izquierda de la barra de fórmulas, si se oprime el icono de flecha nos muestra la lista de todos los nombres o identificares sin importarle en que hoja de nuestro libro actual se encuentra.

• Si deseamos saber la información de cada referencia o si deseamos eliminar alguna de ellas, se da:

• Insert-->Name-->Define...

• En el cuadro de dialogo aparece todas las características de cada nombre de rango

– Nota:

• Si se borra un nombre a un rango, se borrará el nombre, pero no los datos

• En las fórmulas que utilizan estos nombres de rangos, ahora estarán indefinidos, por lo que marcará errores en las fórmulas que hagan referencia a ellas.

• Si se borran los datos, no se borra automáticamente el nombre del rango, ya que esta asignada a las celdas no a los valores.

• Si elimina las celdas, entonces si se borrara también el nombre al rango.

Aplicando los nombres a las celdas• Dado que se recuerda y se entiende claramente un nombre

en lugar de referencias, es posible aplicar el nombre a una celda o fórmula ya definida.

• Para hacer lo anterior :

• Se ubica en la celda o formula ya definida

• Se da Insert -->Names -->Apply...

– Se eligen los nombres de los rangos que deseamos sustituir

– y automáticamente se substituyen las referencias, por los nombres ya especificados.

Insertar notas a las CeldasPara dar una mayor claridad en la documentación de nuestro trabajo, es recomendable agregar notas a las celdas.

Para hacer lo anterior se da las opciones

Insert-->Notes... y se da la el texto aclaratorio correspondiente.

– Es importante aclarar que el texto no influye en el valor de la celda.

– Para saber si hay una nota se marcaran con un punto rojo en el extremo superior si esta habilitado esa opción en Tools-->Option...-->View

– Para ver la nota hay que ubicarse en la celda con nota y dar Insert--Note..

Agregar comentarios• A diferencia de la nota, un comentario

aparecerá en el texto y no en el contenido de la celda y podrá moverse dentro de la hoja de cálculo.

• Para crear el comentario – dar un clic en el botón de la barra de dibujos, el apuntador

cambiara por un signo +.

– Posicione el apuntador donde desea colocar el comentario

– Arrastre el cursor hasta la posición deseada

– Escriba el comentario

Las Funciones• La función es una fórmula especial escrita

con anticipación que acepta un valor o un conjunto de valores.

• Realiza una operación y devuelve un valor o o conjunto de valores.

• Se pueden utilizar solas o como componentes para construir otras fórmulas.

• Su uso simplifica y acorta las fórmulas.

• Los valores sobre los cuales la función efectúa las operaciones se llaman argumentos de la función.

• Los valores que las funciones devuelven se llaman resultados de la función.

• La secuencia de caracteres que se utiliza en una función se llama sintaxis, si no se observa esa sintaxis, se presentara un mensaje de error.

• Las funciones siempre llevan paréntesis, las cuales indican donde empiezan y donde terminan los argumentos.

• Se muestra el paréntesis izquierdo correspondiente al momento de introducir un paréntesis derecho en la barra de fórmulas para asegurarse de tener un número correcto de paréntesis en la fórmula.

• Los argumentos de las funciones pueden ser constantes, referencias a celdas, nombres de celdas o fórmulas. Las fórmulas en sí pueden contener otra función (función anidada).

• Se pueden anidar hasta 7 niveles de funciones en una fórmula.

• Las funciones pueden tener hasta 1024 caracteres de longitud.

Pegado de una función en una fórmula utilizando el asistente

• Se selecciona la celda donde se desea la fórmula.

• Se elige Insert-->function

– Se presenta una lista de las categorías de las funciones para la hoja de cálculo disponibles.

– La primera línea indica las funciones mas usadas por el usuario.

– El tipo “ALL” incluye todas las funciones disponibles.

• Se selecciona el tipo de función que se desea

– En la parte inferior se da una pequeña explicación de la función seleccionada.

•En la parte inferior del cuadro de dialogo se elige la opción NEXT.

•En el siguiente recuadro se da el argumento de la función.

•En la esquina superior derecha, aparece el valor de la función evaluada según los argumentos.

•Se da O.K.

Otra forma de insertar una función es oprimiendo el botón de la barra de fórmulas.

Categoría de las funciones• Financieras

• Fecha y hora

• Matemáticas y trigonometricas

• Estadísticas

• Búsqueda y referencia

• Base de Datos

• Texto

• Lógicas

• Información

El uso de las bibliotecas de funciones• Las reglas para usar las funciones de biblioteca son :

• Todas las funciones llevan paréntesis para escribir dentro los argumentos.

• El argumento de una función puede ser otra función.

• El numero de paréntesis que se abre debe ser igual al numero de paréntesis que cierra.– Cuando cierra un paréntesis, EXCEL le indica a que paréntesis abierto se

esta refiriendo.

• Es recomendable escribir los nombres de las funciones en letra minúscula, ya que si por error se escribió mal, o la función no existe, al oprimir la tecla <Enter>, el nombre de la función seguirá en minúscula y si está correcta, el nombre de la función se convertirá en mayúscula.

Funciones Matemáticas•ABS

Devuelve el valor absoluto de un número

Su sintaxis es: ABS(número)

Ejemplos

ABS( 100) es igual a 100

ABS (-100) es igual a 100

Si en la celda A15 contiene el valor -25, entonces ABS(A15) es igual a 25

ABS(26-A15*2) es igual a 2

•SUMDevuelve la suma de todos los números en la lista de argumentos.

Su sintaxis es: SUM(número1,número2,....)Donde número puede ser constantes, referencias a celdas, rangos, nombres a rangos, fórmulas o funciones.

Ejemplos:

SUM(3,2) es igual a 5

SUM(A1,B1,2) es igual a 2 mas la suma del contenido de las celdas A1 y B1.

SUM(A1:B2) es igual a la suma del contenido de las celdas A1,A2,B1 y B2.

•Función INT

Redondea un número real por defecto hasta el entero (de la izquierda de la recta numérica) más cercano

Su sintaxis es: INT(número)

Ejemplo:

INT(8.9) es igual a 8

INT(-8.9) es igual a -9

•Función Product

Multiplica todos los números que se dan como argumentos y devuelve el producto.

Su sintaxis es: PRODUCT(número1,número2,...)

Ejemplos:

PRODUCT (A2:C2) es igual al producto del contenido de las celdas A2, B2 y C2

PRODUCT(A1,B2:B4,2) devuelve el producto de 2 por A1, B2, B3 y B4

• Función RAND

Devuelve un número real mayor o igual a cero y menor a 1 tomado al azar.

Los números se toman desde una lista de números aleatorios y se devuelven a la lista cada vez que se calcula la hoja de cálculo (es decir se pueden repetir)

Su sintaxis es RAND()

Ejemplos:

RAND()*100 genera un número real aleatorio mayor o igual a 0 pero menor que 100

RAND()*(b-a)+a genera un número real aleatorio entre a y b

INT(RAND()*(b-a+1)+a genera un número entero aleatorio entre a y b (inclusive)

•Función RANDBETWEEN

Esta función da un número entero aleatorio entre dos números inclusive.

Su sintaxis es:

RANDBETWEEN(num_inf, num_sup)

Donde num_inf es el número inferior deseado y num_sup es el número superior deseado.

Generación de Números Aleatorios

Después de haber puesto la fórmula que genere un número aleatorio, es posible generar otro número aleatorio, oprimiendo la tecla función F9.

Cuando se oprime F9, se recalculan todas las operaciones aritméticas en la hoja, y si se tiene en la hoja de cálculo tiene varias funciones random, todas volverán a generar otro número, pero dado que son números aleatorios, esto no afectará la lógica de su trabajo.

•Función ROUND

Redondea un número a una cifra determinada de decimales.

Su sintaxis : ROUND(número, num de decimales)

Si “num de decimales” es mayor que 0, “número” se redondeará a la cifra determinada de decimales, siguiendo las siguientes reglas:

si “num de decimales” es 1 le sumara .05

si “num de decimales” es 2 le sumara .005

y así sucesivamente

Si “num de decimales” es 0, “número” se redondeará al entero mas próximo, pero sumando .5

Ejemplos

ROUND(3.99,0) = 3.99+.5=4.49, con 0 decimales = 4

ROUND(2.15,1) es igual a 2.2

ROUND(8.926,2) es igual a 8.93

ROUND(2.149,1) es igual a 2.1

•Función SQRT

Devuelve la raíz cuadrada del argumento

Su sintaxis es: SQRT(número)

Si número es negativo, SQRT devolverá el valor error #!NUM!

Ejemplos:

SQRT(16) es igual a 4

SQRT(-16) es igual a #!NUM!

SQRT(ABS(-16)) es igual a 4

•Función TRUNCTrunca un número a un entero, suprimiendo su parte decimal

Su sintaxis es: TRUNC(número,num_de_decimal)

Donde número es el número a truncar.

“num_de_decimal” es un número que especifica la precisión del truncado.

El valor predeterminado de “num_de_decimal” es cero.

Ejemplo:

TRUNC(8.9) es igual a 8

TRUNC(-8.9) es igual a -8

•Función MOD

Esta función regresa el residuo o el resto de la división de 2 números.

Su sintaxis es MOD(número,num_divisor)

Ejemplo:

MOD(10,4) da como resultado 2

MOD(10,3) da como resultado 1

Funciones Estadísticas•AverageDevuelve el promedio de los argumentos.

Su Sintaxis es: AVERAGE(num1,num2,...)

Donde “num1,num2,...” son argumentos numéricos de los cuales se desea su promedio.

Ejemplo:

Si A1:A5 contiene los números 10,7,9,27 y 2

Entonces AVERAGE(A1:A5) es igual a 11

También AVERAGE(A1:A5,5) es igual a 10

•Función COUNTCuenta los valores que no están en blanco de la lista de argumentos.

Su sintaxis es: COUNT(val1,val2,...)

Donde “val1,val2,...” son los argumentos que representan los valores que se desean contar.

Ejemplo:

Si A1=5, A2=3, A3 no tiene nada y A4=0

Entonces COUNT(A1:A4) es igual a 3

•Función MAXDevuelve el número mayor de la lista de argumentos.

Su sintaxis es: MAX(num1,num2,...)

Donde “num1,num2,...” son números para los que se desea encontrar el valor máximo.

Ejemplo:

Si A1:A5 contiene los números 10,7,9,27,2

Entonces MAX(A1:A5) es igual 27

También MAX(A1:A5,30) es igual a 30

También MAX(A2,A5,5) es igual a 7

•Función MINDevuelve el número menor de la lista de argumentos.

Su sintaxis es MIN(num1,num2,...)

Donde “num1,num2,...” son números para los que se desea encontrar el valor mínimo.

Ejemplo:

Si A1:A5 contiene los números 10,7,9,27 y 2

Entonces MIN(A1:A5) es igual a 2

También MIN(A1:A5,0) es igual a 0

También MIN(A2,A5,5) es igual a 2

Funciones LógicasFunción IF

Devuelve un valor si una prueba lógica, al evaluarla es “verdadera” y otra diferente si es “falsa”.

Pruebalógica

verdadera falsa

proceso 1 proceso 2

•Se usa para efectuar pruebas condicionales sobre valores y fórmulas, y para causar bifurcaciones en base al resultado de la condición.•El resultado de la prueba determina el valor que devolverá la función IF

Su Sintaxis es:

IF(prue_logica,valor_si_verdad,valor_si_falso)

Donde “prue_logica” es cualquier valor o expresión que pueda evaluarse como verdadero o falso.

“valor_si_verdad” es el valor que se devolverá si “prue_logica” es VERDADERA

“valor_si_falso “ es el valor que se devolverá si prue_logica es FALSO

Se pueden anidar hasta 7 funciones IF

Tomando los datos de la tabla anterior, las fórmulas:

IF(B2>C2,”Sobre presupuesto”, “Aceptar”)

da como resultado el letrero de “Sobre presupuesto”

IF(C4-B4>0,C4-B4,B4)

da como resultado 425

IF(Sum(B2:B5)>Sum(C2:C5),B1,C1)

da como resultado el letrero “GASTOS PRONOS”

A B C12

3

4

5

GASTOS ACTUALES GASTOS PRONOSENEROFEBRERO

MARZO

ABRIL

1500

500

500

600

900

900

925

925

Ejemplo:

• Ejemplo de Anidación• Escriba en la celda A3 una fórmula para saber si en la

celda A1 está un valor POSITIVO,NEGATIVO O CERO (que se escriba el texto)

A1=0

A1<0

SI NO

SI NO cero

negativo positivo

En Excel se representaría el diagrama anterior con

la siguiente fórmula escrita en la celda A3

=IF(A1=0,”CERO”,IF(A1<0,”NEGATIVO”,”POSITIVO”))

\___/ \____/ \___________________________/ Condic1 Val. si Verdad1 Valor si Condic1 falsa

\____/ \_________/ \_______/ Condic2 Val. si Verdad1 valor si Condic2 falsa

Algunas ocasiones es necesario hacer consultas o comparaciones que involucren 2 o más condiciones para las cuales se cumplen ciertos eventos, si el evento se cumple tendríamos como resultado TRUE y si no se cumple se tendría un FALSE.•Función OR

Esta función regresa el valor de TRUE, si alguna condición se cumple, pero si ninguna condición se cumple, tendrá el valor de FALSE.

Su sintaxis es OR(condición1,condición2,...)

Ejemplo:

OR(2=3,3>5)

El resultado es FALSE

También si A5=10 y B20=50

OR(9<A5,100=B20)

Da como resultado TRUE

También si A1=1,A2=3,A3=5

OR(sum(A1:A3)<>0,A3>1)

Da como resultado TRUE

•Función AND

Esta función regresa el valor de TRUE si todas las condiciones se cumplen, pero si alguna no se cumple, regresara el valor de FALSE.

Su Sintaxis es la siguiente:

AND(condición1,condición2,....)

Ejemplo:

AND(4+3=10,5>1)

Da el resultado de False

También si C4=15 y B34=34

AND(C4+50>B34,100-25>b34,10<C4)

Da como resultado TRUE

También si D1=3,D2=-1,D3=10

AND(D1>0,D2>0,D3>0)

Da como resultado FALSE

•Función NOT

Esta función, regresa el valor opuesto a la condición que evalúa.

Su sintaxis es la siguiente:

NOT(condición)

Ejemplo:

NOT(2+3>10)

El resultado es TRUE

También si A10=50

NOT(34+A10< 100)

Da como resultado FALSE

•Estas 3 últimas funciones, normalmente no se usan aisladamente, si no por el contrario, se utilizan dentro de otras funciones. Ejemplos:

SI a1=10,B4=12 Y C3=5

IF(OR(A1<0,B4<12,C3<10),”UNO”,”DOS”)

El resultado es “UNO”

Pero

IF(And(A1<0,B4<12,C3<10),”UNO”,”DOS”)

El resultado es “DOS”

•Escriba la fórmula para saber si en la celda A9 esta escrita una vocal o una consonante

La fórmula será:IF(OR(A9=“A”,A9=“E”,A9=“I”,A9=“0”,A9=“U”),”VOCAL”,”CONSONANTE”)

Referencias absolutas, relativas y circulares.

• Uno de los conceptos importantes que utilizan las hojas de cálculos son las referencias.

• Una referencia es el punto de partida de nuestros cálculos.

• Por lo anterior es importante conocer las diferentes referencias que EXCEL maneja.

Referencias Relativas • Una Referencia Relativa es similar a dirigir

hacia alguna parte desde un lugar, a otra persona, por ejemplo “Del puesto de periódicos, de vuelta a la derecha y camine 2 cuadras...”

• La referencia Relativa le indica a EXCEL como buscar otra celda a partir de otra celda que contiene la fórmula. Las referencias C4 y F7 son referencias relativas.

• Una referencia Absoluta es similar a dar a alguien una dirección exacta, ejemplo : “Río Hondo #1”.

• Una referencia Absoluta le indica a EXCEL como encontrar una celda en base a la posición exacta de esa celda.

• Dicha referencia se designan así:

– Agregando un signo de $ en la definición de la fórmula antes de cada columna si deseamos mantener constante esa columna ejemplo $A5

– Agregando un signo de $ en el número de fila si deseamos mantener constante esa fila, ejemplo A$5

– Es valido especificar constante la columna y la fila, ejemplo: $D$9 y $F$16 .

Referencia Absoluta

• Para cambiar una dirección relativa a dirección absoluta en una fórmula– Edite la celda donde se encuentra la fórmula con

direccionamiento relativo– Ubique el cursor donde desea cambiar la

dirección relativa a absoluta.– Oprima la tecla función F4.

• Cada vez que oprima esta tecla hará fija la columna, la fila, ambas o ninguna.

– También puede escribir el signo de $ directamente en la fórmula.

Ejercicio• Haga las tablas de multiplicar, por medio de

referencias absolutas (escriba una sola fórmula y copiela en todas las celdas)

tabla del

2 3 4 5 6 7 8 9

1

2

:

9

Referencias Circulares• Cuando se crean fórmulas, se hace referencia a

celdas. Si una fórmula hace referencia a la propia celda donde se desplegará el resultado, se dice que es una referencia circular.

• Por ejemplo, si en la celda B3 se pone la siguiente fórmula =B1+B2+B3, se está haciendo una referencia circular.

• EXCEL no puede resolver las fórmulas con referencias circulares usando un calculo normal.

• Cuando el usuario hace una referencia circular, EXCEL lo indica en la barra de estado, indicando la dirección de la celda donde se declaro esta referencia.

• Si dicha referencia es accidental, se tiene que volver a escribir la fórmula o cambiar la lógica para romper esa referencia circular.

• Si la referencia es intencional, deberá utilizarla así:

• Tools -->Options..-->Calculation

• Y seleccione la casilla de iteration.

• Observe que cuando se hace la suma, en la celda donde se calcula el nuevo valor, toma el valor de si misma pero con el valor anterior que contenía la propia celda.

Recomendación:Trate de evitar las referencias

circulares, salvo que este usted seguro que eso es lo que desea.

Auditoria VisualPor medio de la auditoria visual, es fácil encontrar la relación entre las celdas y encontrar los errores.

Para hacer uso de la auditoria es necesario usar la barra de herramientas de auditoria.

Para hacer lo anterior :Tools --> Auditing -->Show Auditing toolbar

Los primeros 6 botones se utilizan para insertar o borrar flechas de rastreo entre las dependencias que existan entre las celdas.

• Rastrear un nivel precedente.

• Quitar un nivel precedente

• Rastrear un nivel dependiente

• Quitar un nivel dependiente

• Quitar todas las flechas

• Rastrear este error

• Mostrar notas

• Mostrar ventanas de información

Elementos de la barra de auditoria

• El botón “Rastrear un nivel precedente” es para conocer las celdas a que se refiere una fórmula. Cada vez que se oprima va apareciendo una flecha en color azul, mostrando un nivel mas de precedencia.

• El botón “quitar un nivel precedente” es para eliminar un nivel de precedencia. Cada vez que lo oprima se va quitando un nivel de precedencia.

• El botón de “Rastrear un nivel dependientes” es para ver las celdas dependientes. Cada vez que oprima va mostrando una flecha en color azul, mostrando un nivel mas de dependencia.

• El botón “Quitar un nivel dependientes” es para eliminar un nivel de dependientes. Cada vez que lo oprima va quitando un nivel de dependientes.

• El botón “Quitar todas las flechas” borra todas las flechas tanto las actuales como las anteriores.

• El botón “Rastrear el error” es para que nos indique el origen del error en la celda . EXCEL lo indicará con una flecha en color rojo.

Inserción de filas y columnas• Para insertar una o mas filas

– seleccione con el Mouse los números de las filas donde desea insertar las nuevas filas.

– Oprima el botón derecho del Mouse y elija del menú la opción Insert.

• Si lo que desea es insertar una o mas columnas– utilice la misma técnica y cambia en lugar de

filas, ahora por columnas.

Eliminación de Filas o columnas

• Para eliminar una o mas filas:– seleccionelas con el Mouse.– Con el botón derecho del Mouse, ahora elija la

opción de eliminar.• Observe que automáticamente se desplazan las filas de

abajo para cubrir esta área.

• Si desea eliminar una o mas columnas: – utilice la misma técnica pero cambie de

filas a columnas.

• Es importante aclarar que cuando eliminamos o insertamos filas o columnas, si alguna formula hace referencia a algún valor de esa fila o columna, ahora nos marcara un error de referencia.

• Y las filas o columnas que se desplacen hacia el nuevo lugar, actualizaran sus fórmulas.

Eliminar o borrar celdas• El caso de una celda es diferente al de fila o

columnas, ya que es posible que la celda tenga información adyacente alrededor de ella, por lo que cuando seleccione una celda y desea eliminarla, al elegir con el botón derecho del Mouse la opción de eliminar, ahora tendrá otros requerimientos, como son: – si deseamos desplazar las celdas hacia la izquierda o

hacia arriba.

– insertar una fila o columna.

Borrado de celdas• Como observó, al eliminar una celda, esta es

ocupada inmediatamente por las celdas adyacentes, pero si desea conservar las celdas en blanco, lo que desea es el borrado.

• Para borrar una o varias celdas:– primero seleccionelas la(s) celda(s).

– después oprima el botón derecho del Mouse y elija la opción de borrar, al aparecer el cuadro de dialogo, elija la opción que usted desee.

Protección de archivos• Excel tiene forma de proteger su trabajo contra

modificaciones que se quieran hacer en su archivo, utilizando palabras reservadas, en distintos niveles, las cuales son :

• Protección para consultar a nivel archivo

• Protección contra escritura en todo el archivo

• Protección para áreas especificas en el archivo

Protección para consultar

Para proteger la apertura del archivo, dé los siguientes comandos :

• File -->Save As ... -->Options

• Ingrese una contraseña en la caja de texto Protection Password.

• De clic en O.K.

• Nota: Si olvida la contraseña No podrá volver a abrir el archivo.

Protección contra escritura• Este método permite ver el archivo, pero no

modificarlo.

• File -->Save As.. -->Options

• Ingrese una contraseña en la caja de texto Write-Reservation Password.

• De O.K.

• Nota: Si olvida la contraseña, tendrá que guardar bajo otro nombre el archivo la próxima vez que necesite hacerle cambios.

Protección en rangos• Este método es útil cuando se trata de llenar

esqueletos o plantillas, ya que solamente va permitir llenar las áreas autorizadas y permitir navegar sólo entre celdas cambiables, con solamente oprimir la tecla Tab, permitiendo hacer el llenado rápidamente y únicamente en las zonas permitidas.

• Por omisión todas las celdas están susceptibles a estar protegidas.

• Seleccione el o las áreas que permita que se puedan modificar.

• Format -->Cells..

• De clic en la oreja Protection

• quite la marca de Locked

• Con la opción de Hidden además de no permitir la modificación, no desplegara la(s) formula(s), pero la fórmula seguirá activa.

• De O.K.

Ahora hay que activar la protección

• Tool --> Protection -->Protect Sheet..– (o Protect WorkBook si desea todo el libro)

• Ingrese si desea un password de no mas de 255 caracteres (es sensible a mayúsculas y minúsculas).

• Elija las opciones de proteger Contents, Objects o Scenarios, según lo requiera.

• De O.K.

Editar un grupo de hojas• Cuando las hojas de su libro tienen el mismo

diseño es posible editarlas en grupo.• Es importante aclarar que la edición afectará a

todas las hojas seleccionadas, por lo que se recomienda solamente editar en grupo cuando se desea tener el mismo estilo en el texto, insertar los mismo datos o cuando se desean tener fórmulas iguales en la misma celda de todas las hojas.

Edición en grupo

• Para editar:– Primero se selecciona las hojas, teniendo

oprimida la tecla de mayúsculas el nombre de las hojas

• En este momento lo que edite afectara a las hojas seleccionadas, es decir puede modificar el tipo de letra, insertar nuevos datos o declarar fórmulas.

Quitar la edición en grupo

• Para quitar la edición en grupo:– se selecciona alguna hoja de nuestro libro

donde deseamos trabajar.– Se oprime el botón derecho del Mouse.– en el cuadro de dialogo que aparece, se elige la

opción de desagrupar hojas.

Ventanas• Cuando estamos introduciendo valores en la

hoja de calculo, esta se va recorriendo para mostrarnos nuevas celdas, pero si deseamos mantener una área sin mover (por ejemplos títulos), entonces lo que deseamos es crear ventanas, es decir dividir nuestra área de trabajo en pequeñas ventanas (solamente se puede tener 2 o 4 ventanas).

Ventanas (continuación)• Para dividir en ventanas:

– observe que al inicio o final de las barras de desplazamiento vertical u horizontal, se encuentra una pequeña raya mas obscura, ubiquese el cursor en ella (observe que ahora la forma del puntero del Mouse cambia a un par de líneas paralelas).

– oprima el botón izquierdo del Mouse.– arrastre la línea que ahora aparece en la

pantalla, hasta donde sea su gusto.

Ventanas (continuación)

• Si desea quitar las ventanas:– Ubique el cursor en la línea divisoria (observe

que de nuevo cambia la forma del puntero del Mouse)

– Teniendo oprimido el botón izquierdo del Mouse, regrese la línea divisoria al principio o final de la barras desplazadora, donde se encontraban originalmente.

Inmovilizar secciones

• Cuando tiene dividida la hoja en ventanas, es posible inmovilizar una sección de ella.

• Para hacer lo anterior:– ubique el apuntador del Mouse en la ventana

que desea inmovilizar.– de la barra de menús elija la opción de

Windows -->Freeze Panes.

Quitar la inmovilización de ventanas.

• Para quitar esta opción:– De la barra de menús elija la opción – Windows -->Unfreeze Panes.

Archivos de Inicio

• Todo archivo que se guarde en el subdirectorio XLSTART de Excel, será el(los) archivo(s) que se carguen cuando se inicie Excel.

Funciones de Búsqueda y referencia

•Función CHOOSEUtiliza un índice para devolver de una lista de argumentos de valores, un valor especifico. Se pueden seleccionar uno de los valores basados en el índice.

Su Sintaxis es: CHOOSE(índice,val1,val2,...)

donde “índice” especifica qué valor de argumento se selecciona. Debe ser un número entre 1 y 29, o bien una fórmula o referencia a una celda.

Si índice es 1, CHOOSE devolverá val1, si es 2, CHOOSE devolverá val2 y así sucesivamente.

• Si índice es menor que uno o mayor que el número del último valor de la lista, CHOOSE devolverá el valor de error #!VALOR!

• Ejemplos:

CHOOSE(4,”I”,”II”,”III”,”IV”)

la función da como resultado IV

SUM(A1:CHOOSE(3,A10,A20,A30))

la función es igual a SUM(A1:A30) y se evalúa la función suma.

Si A10 contiene el número 7

CHOOSE(A10-4,”NORTE”,”SUR”,”ORIENTE”)

la función da como resultado ORIENTE

• Examina la columna extrema izquierda de una matriz en busca de un valor específico y devuelve el valor en la celda indicada.

• Su Sintaxis es:VLOOKUP(val_busca,mat_comp,ind_colum,opc)

• Donde “val_busca” Es el valor a buscar en la primera columna de la matriz. Puede ser un valor, una referencia o una cadena de texto entre comillas.

Función VLOOKUP

•“mat_comp” Es la matriz de información donde se buscan los datos. La búsqueda siempre se efectúa en base a la primer columna de esta matriz.

“ind_colum” Es el número de columna en “mat_compara” desde la cual deberá devolver el valor coincidente. Si “ind_colum” es 1, devolverá el valor de la primer columna de “mat_comp”, si es 2 devolverá el valor de la segunda columna y así sucesivamente.

El argumento OPC es opcional y se refiere a lo siguiente:

Si esta puesta como FALSE , significa que la búsqueda en la matriz va a efectuarse con valores coincidentes (iguales).– Por lo anterior puede estar desordenado la primer columna de la

matriz, pero el valor coincidente debe de existir, de lo contrario marcará un error.

• Si no esta puesto el argumento OPC o si esta puesto como TRUE, significa que la búsqueda se va a efectuar por rangos, por lo que debe estar ordenada la primer columna de la matriz de comparación.– Nota: Si VLOOKUP no encuentra el valor buscado,

utilizará el mayor valor que sea menor que “val_busca”

Suponga que la celda F3= Giga y G3=Tera

Vlookup(F3,A5:C17,2)

Da como resultado G

Vlookup(F3,A5:C17,3)

Da como resultado 10^9

Vlookup(G3,A5:C17,3)

Da como resultado 10^12

• Explora la fila superior de una matriz en busca de un valor particular y devuelve el valor a la celda indicada.

• Se usa HLOOKUP cuando los valores de comparación se encuentran en una fila en la parte superior de una tabla de datos y se desea encontrar información que se encuentra dentro de un número especificado de filas.

Función HLOOKUP

• Su sintaxis es:Hlookup(val_busca,mat_comp,ind_fila,opc)

“val_busca” Es el valor que se encuentra en la primera fila de la tabla. Puede ser un valor, una referencia o una cadena de texto entre comillas.

“mat_comp” Es una tabla de información en la que se busca los datos en la primer fila.

“ind_fila” Es el número de fila en “mat_comp” desde el cual se deberá devolver el valor coincidente. Si “ind_fila” es 1, devolverá el valor de la primera fila de “mat_comp”, si “ind_fila” es 2, devolverá el valor de la segunda fila y así sucesivamente.

El argumento OPC es opcional y se refiere a lo siguiente:

Si esta puesto como FALSE, significa que la búsqueda en la matriz va a efectuarse con valores coincidentes.– Por lo anterior puede estar desordenado la primer fila de la matriz,

pero el valor coincidente debe de existir, de lo contrario marcará un error.

• Si no esta puesto el argumento OPC o si esta puesto como TRUE significa que la búsqueda se va a efectuar por rangos, por lo que debe estar ordenada la primer fila de la matriz de comparación.

– Nota: Si Hlookup no puede encontrar el valor que busca, utilizará el mayor valor que sea menor que “val_busca”

Hlookup(H3,A3:F8,5) Da como resultado 10

Hlookup(I3,A3:F8,2) Da como resultado 8

Hlookup(J3,A3:F8,4) Da como resultado 7