Excel Clase 2

Las funciones en Excel

Referencias

Las referencias se basan en los encabezados de las columnas y filas de la hoja de calculo. Permite identificar celdas o grupos de celdas y se pueden incluir en formulas. Una hoja de calculo tiene 256 columnas rotuladas de la A a la IV y 60.536 filas con numeros.

Una celda esta especificada por la interseccion de una fila con una columna y se conoce como estilo de referencia A1.

La referencia de una celda activa aparece en el cuadro de nombres en el extremo izquierdo de la barra de formulas.

Asignacion de nombres a las referencias

Se pueden crear nombres para establecer referencia a una celda, un grupo de celdas o una formula.

Los nombres deben empezar con una letra, los demas pueden ser letras o numeros, fuente caracter subrayado.

Para ello, se selecciona la celda o rango de celdas y en el menu Insertar → Nombre → Definir, escribir el nombre que se le asigna al rango y Aceptar o agregar para definir mas nombres. Tambien se pueden definir en el cuadro de nombres directamente selecciono las celdas, escribo el nombre de referencia en el cuadro de nombres y Enter.

Aplicar nombres

Despues de definir el nombre para la celda o rango, se puede reemplazar con ese nombre las referencias a ese rango.

Ejemplo: si define la celda A5 con el nombre Datos1, se puede reemplazar todas las referencias a A5 con el nombre Datos1.

Para ello;

Se selecciona la celda a la que se desea aplicar los nombres

Se elige el nombre del cuadro de nombres en la barra de formulas o menu Insertar → Nombre →  Aplicar

Se seleccionan los nombres que se desean aplicar y Aceptar.

 

Redefinir un nombre

Se puede redefinir un nombre editando su referencia, formula o valor constante.

 

Crear nombres automaticamente

Se selecciona el rango a definir el nombre y luego elegir: Insertar → Nombre → Crear, se abre cuadro de Crear nombres a partir de Fila superior, Columna izquierda, fila inferior, columna derecha.

En el cuadro de dialogo se deben indicar las filas y/o columnas que se quiere dar nombre.

Ejemplo:

Le asignamos nombres a fila superior y columna izquierda

Se crean los nombres

Nombre         Rango

Enero             B2:B4

Febrero          C2:C4

Marzo             D2:D4

Ventas           B2:D2

Gastos           B3:D3

Ganancias    B4:D4

Funciones

Una funcion es una formula especial ya escrita que acepta un valor o valores, llamados argumentos, realiza una operacion y devuelve un valor o valores.

Se pueden usar solas o como componentes para construir formulas.

Ej: en lugar de escribir: A6+A7+A8+A9

Se puede escribir: SUMA(A6:A9)

Los argumentos deben colocarse entre parentesis y separados por punto y coma y pueden ser valores constantes o formulas y puede contener otras funciones. Cuando pasa esto que una funcion es argumento de otra funcion se dice “funcion anidada”. Excel permite hasta 7 niveles de anidacion.

Categorias de una funcion

Financieras

Fecha y hora

Matematicas y Trigonometricas

Estadisticas

De busqueda y referencia

De base de datos

Texto

Logicas

Informacion

f(x): Asistente para funciones

desde la barra de formulas o en el menu Insertar → Funcion

se puede usar para crear una funcion o para editar una existente.

Insertar funcion:

Seleccionar categoria, seleccionar funcion, se abre el menu de funcion para agregar argumentos que puede ser numeros o rangos donde estan los datos.

Autosuma: å

excel escribe la funcion SUMA y sugiere el rango de celdas a sumar.

Tipos de datos

Numeros

Formato asignado por defecto es el numero general que presenta los valores tal cual fueron introducidos. Los numeros se alinean a la derecha y se puede cambiar.

Si el numero es demasiado largo se muestra como ###, si se aumenta el ancho de la columna el numero aparece en la celda.

Se puede cambiar la forma en que se muestran los numeros cambiando el formato de la celda.

Aunque se cambia el formato de visualizacion, el numero que almacena excel es el numero de 15 digitos de exactitud y al realizar los calculos, excel usa el numero almacenado y no el que aparece en pantalla.

En el menu Formato → Celda pestaña Numero puedo establecer los formatos:

$          Moneda

%        Porcentual

←        Aumento de decimales

→        Disminucion de decimales

000     Millares

Texto

Caracteres con cualquier combinacion de letras y numeros. Se alinean a la izquierda y se puede cambiar la alineacion.

Fecha y hora

Se muestran fechas con diferentes formatos y Excel los almacena: las fechas como numero de serie y las horas como fracciones de decimales con lo cual al ser considerado como numero se puede sumar, restar o añadir otro calculo.

Valores logicos y de error

Verdadero o Falso: como resultado de formulas que contengan una funcion logica.

Cuando no es posible hacer el calculo correctamente aparece el valor de error y son: #N/A, #¡VALOR!, #¡REF!, #¡NULO!, #¡DIV/O!, #¡NUM!, #¿NOMBRE?

Introducir numeros

Para que no sean considerados caracteres alfanumericos con los que no se pueden hacer operaciones.

·         Deben empezar con

o   un digito del 0 al 9

o   un signo +, -, =

o   una coma

·         Un numero puede terminar con un signo % (este signo divide al nro por 100)

·         Un numero solo puede tener una coma decimal

·         No deben usarse puntos o espacios para separar cantidades. Debe cambiarse el formato de numero.

·         Un numero se puede introducir como notacion cientifica: 2,E+02 = 200.

·         No debe usarse parentesis para numeros negativos

·         No debe usarse punto para separar miles

·         No debe usarse el signo de moneda

 

Introducir fechas y horas

Pueden ser valores constantes o dentro de una formula

Hay que tener en cuenta que:

·         Por defecto la hora se muestra con formato 24 hs. Para que se use el de 12 horas se debe escribir a o p despues de la hora dejando un espacio en blanco en el medio (AM/PM)

·         Se puede escribir fecha y hora en la misma celda separados por un espacio

·         Para fechas se usa / o –

Introducir texto

Se puede introducir hasta 255 caracteres por celda

Formato: tipo de letra, tamaño individualmente a los caracteres de una celda

Para hacer una entrada de texto que solo contenga numeros debe colocarse apostrofe adelante

Para introducir texto en formulas, se debe poner entre comillas dobles.

Ejemplo: “Total para” & “2012” mostrara: Total para 2012

Si el texto no cabe empieza a invadir la celda de la derecha si esta vacia y se recorta si no lo esta. Solo se oculta no se borra, para verla hay que ensanchar la columna.

Introducir formulas

Las formulas comienzan siempre con el signo =.

Ejemplo:

=(B4/25)+100

= Ingresos – Gastos

= suma(A1:a4)

= si (A1>100; A1+0,1*A1; A1+0,05*A1)

Operadores

Especifican una operacion que debe realizarse con los elementos de una formula.

Pueden ser:

·         Aritmeticos

·         De comparacion

·         De texto

·         De referencia

Operadores aritmeticos

Operador                                                      Operacion

+                                                                     Suma

-                                                                      Resta

*                                                                      Multiplicacion

/                                                                      Division

%                                                                    Porcentaje

^                                                                     Potenciacion

Operadores de comparacion

Operador                                                      Significado

=                                                                     Igual

>                                                                     Mayor

<                                                                     Menor

>=                                                                   Mayor o igual

<=                                                                   Menor o igual

<>                                                                   Distinto

Operador de texto

Operador

&                                                                    Permite concatenar 2 o mas cadenas de texto en una sola continua.

Operadores de referencia

Son 3:

Rango: dos puntos. Todas las celdas entre dos referencias incluyendolas. Ej: B2:E2 = B2, C2, D2, E2

Union: punto y coma. Produce una referencia unica resultado de la union de otras dos. Ej: suma(A2:A3;B1:B3)= A1+A2+A3+B1+B2+B3

Interseccion: (espacio) hace referencia a las dos celdas comunes a dos referencias: Ej: B7:D7 C6:C8 hace referencia a la celda C7 resultado de la interseccion de B7,C7,D7 y C6,C7,C8

Orden de evaluacion de los operadores

Operador                                                    Descripcion

: ; espacio                                                   Operador de referencia

-                                                                   Negacion

%                                                                 Porcentaje

^                                                                   Potenciacion

* /                                                                 Multiplicacion/Division

+ -                                                                Suma/Resta

&                                                                  Union de texto

= < > <= >= <>                                           Comparacion

Lo s operadores de la misma prioridad se evaluan de izquierda a derecha. Se puede usar % para alterar el orden de evaluacion del operador.

Valores de error.

#N/A                             Se intenta referir a un valor no disponible

#¡VALOR!                    Se usa un argumento u operador equivocado

#¡REF!                         Se esta refiriendo a una celda no valida

#¡NULO!                      Se especifica interseccion de dos areas invalida

#¡DIV/O!                       Se intenta dividir por 0

#¡NUM!

#¿NOMBRE?             Se usa un nombre que Excel no reconoce

####                             El resultado es demasiado largo para la celda

Excel Clase 1

Excel Clase 1

Una planilla de calculo es una tabla de informacion dispuesta en filas y columnas. Cada dato se encuentra en una celda que puede contener numero o texto. Asi como formulas que permitan calcular un nuevo valor a partir de datos de otras celdas.

Partes de la hoja de calculo

Libro de trabajo: Los documentos en Excel se denominan libros. Un libro se guarda en un solo archivo y esta compuesto por varias hojas de calculo. Igual que los archivos de Word, puede crearse un libro nuevo en Archivo → Nuevo, puede abrirse un libro existente en Archivo → Abrir, puede guardarse Archivo → Guardar o Guardar como... la primera vez o hacer una copia. Cerrar el libro en Archivo → Cerrar o Cerrar Excel en Archivo → Salir.

Hojas: La hoja activa es la que esta en negrita y la celda activa la que esta recuadrada. Cada hoja tiene una etiqueta con el nombre que puede cambiarse, moverse, etc. en el menu contextual que se abre con el boton derecho sobre la etiqueta de la hoja.

Celda activa: es la celda que esta seleccionada en ese momento.

Barra de formulas: le permite introducir datos en las celdas y muestra en contenido de la celda activa. Tiene botones cancelar, validar e insertar funcion para cada una de esas funciones.

Cuadro de nombres: Las hojas de calculo se dividen en filas y columnas que forman la cuadricula. Cada interseccion entre fila y columna se denomina celda y se identifica por la combinacion de la letra de la columna con el numero de la fila. Se indica su posicion en la cuadricula en el cuadro de nombres. Tambien se puede dar un nombre a una celda o a un rango de celdas.

Rango: es el conjunto de celdas contiguas o no, que se selecciona de manera simultánea para realizar una misma operacion, un cálculo o aplicar un formato determinado. Para seleccionar un rango de celdas debemos:

·         pintar las celdas deseadas con el mouse

·         hacer clic en la primera celda y con Mayusculas+flechas del teclado

·         si no son contiguas oprimir CTRL+ clic en cada celda a seleccionar.

El entorno de trabajo

Ingresar datos

Para ingresar datos en una hoja de calculo primero debe crear un libro nuevo de trabajo, a continuacion:

·         Seleccionar la celda en la que se desea ingresar los datos

·         escribir los datos que se desean incluir en la celda en la barra de formulas

·         oprimir Enter

Para escribir los mismos datos en varias celdas:

·         Seleccionar las celdas con el mouse manteniendo la tecla CTRL presionada hacer clic en todas las celdas del rango

·         Escribir los datos

·         Oprimir CTRL+Enter

Seleccionar datos

Para seleccionar

Una celda	·         clic en la celda
El contenido de una celda	·         Doble clic en la celda ·         Seleccionarlo en la barra de formulas
Rango contiguo de celdas	·         seleccionar la primera celda y sin soltar la tecla Mayus hacer clic en la ultima celda para seleccionar todo el rango. ·         Manteniendo oprimido el boton del mouse desplazarse desde la primera hasta la ultima celda del rango
Rangos no contiguos	·         seleccionar la primera celda y sin soltar la tecla CTRL, hacer clic en todas las celdas del rango
Toda la hoja	·         clic en el cuadro sobre la fila 1 y a la izquierda de la columna A

Desplazarse por la hoja

Puede moverse:

·         con la flechas del teclado avanzo de a una celda hacia arriba, abajo, derecha e izquierda.

·         con las teclas AvPag y RePag avanzo por pantalla hacia abajo o arriba.

·         Con las teclas Alt+AvPag y Alt+RePag avanzo una pantalla a la derecha o izquierda.

·         Con la tecla Inicio se desplaza hasta la columna A de la fila activa

·         CRTL+Inicio: vuelve a la celda A1

·         Con las barras de desplazamiento a la derecha de la hoja hacia arriba y abajo y al pie de la cuadricula para desplazarse a derecha e izquierda.

·         Clic en el cuadro de nombres y escribir la referencia ejemplo: F123 (o nombre) de la celda y luego Enter.

Trabajar con celdas, filas y columnas

Insertar celda, fila o columna

·         seleccionar la fila o columna a la que quiere anteponer una nueva, hacer clic en su etiqueta para seleccionar fila, luego boton derecho en cualquier parte de esa fila se abre menu contextual y elegir Insertar

·         Desde el menu Insertar → Celda, Insertar →Fila o Insertar → Columna

Eliminar celda, fila o columna

Seleccionar la celda, fila o columna a eliminar

·         en el menu Edicion → Eliminar y se eliminan automaticamente las celdas, filas o columnas seleccionadas el resto de las celdas se desplazan llenando el lugar vacio.

·         Para borrar el contenido de una celda o rango de celdas, lo selecciono y oprimo la tecla SUPR

Copiar celda, fila o columna (duplica el contenido)

Seleccionar celda o la fila o la columna a copiar y copiar al portapapeles con alguno de los siguientes pasos:

·         Menu Edicion → Copiar

·         Con el boton dela barra de herramientes copiar

·         Oprimiendo las teclas CTRL+C

·         Seleccionar el rango, en el extremo inferior derecho y pulsando la tecla CTRL el puntero del mouse cambia a la forma+ + y manteniendo presionada CTRL y el boton izquierdo del mouse arrastrar hasta donde quiere copiar la celda o rango y soltar.

Seleccionar la celda destino y pegar del portapapeles con:

·         Menu Edicion → Pegar

·         Con el boton de la barra de herrmientas pegar

·         Oprimiendo las teclas CTRL+V

Cortar celda (mueve el contenido)

Desplaza el contenido de la celda a otra.

·         Con el menu Edicion → Cortar y luego ir a la celda destina y menu Edicion → Pegar.

·         Seleccionar el rango, en el extremo inferior derecho del rango el puntero del mouse cambia a la forma+ , con el boton izquierdo del mouse arrastrar hasta donde quiere mover la celda o rango y soltar

Autorelleno

Para rellenar un rango de celdas seleccionado con una serie de datos. El controlador de relleno es la cruz + que aparece cuando se cambia el puntero del mouse al posicionarse a la derecha y abajo de la celda.

Se ingresar el primer dato en una celda o mas

Se arrastra el controlador de relleno hacia otras celdas, excel hace presuposicion cuando se ingresan los valores iniciales para las series con las que se desea rellenar las celdas. Ejemplo: se ingresa un dia de la semana lunes, y rellena con martes, miercoles, etc.

·         Si es numero el dato de la celda y arrastro el controlador de relleno duplicara la celda con el mismo numero

·         Si es numero y quiero incrementar el valor cuando rellene, ej. 1, 2, 3, etc selecciono el valor inicial y con la tecla CTRL presionada el controlador agrega un signo mas y arrastro hasta el valor deseado.

Nombre de celda

Es el nombre que identifica una celda mediante un termino especifico que se le asigna, en lugar de identificarla con la fila y columna. Puede aplicarse a un rango de celdas. El nombre aparece en el cuadro de nombres cuando la celda es seleccionada.

Buscar, Reemplazar e Ir a

Para buscar datos de celda:

Menu Edicion → Buscar

En el campo buscar escribir el valor que se desea encontrar

Para Reemplazar datos de celda

Menu Edicion → Reemplazar

En el campo buscar escribir el valor que se desea encontrar

En el campo Reemplazar con, escribir el nuevo valor.

Para ir a una celda especifica

mediante referencia de celda como por ej: A2.

En el menu Edicion → Ir a

Oprimir CTRL+I

Ejercicio

Confeccionar el horario usando los distintos metodos vistos de copiar, duplicar, autorelleno y formato de fuente y bordes y sombreado.

Hojas

Puedo desplazarme entre las hojas de un libro seleccionando la etiqueda de nombre de la hoja o a traves de las barras de desplazamiento entre hojas a la izquierda de los nombres.

Insertar hojas

La cantidad de hojas a insertar esta limitada a la cantidad de memoria disponible.

Para insertar hojas:

·         En el menu Insertar → Hoja de calculo

·         En el menu contextual de las etiquetas de las hojas seleccionar la hoja a la cual se va a anteponer la nueva y con el boton derecho se abre menu contextual, elegir Insertar y luego hoja de calculo

Eliminar hojas

Pueden eliminarse las hojas de un libro de alguna de las siguientes formas:

·         Menu Edicion → Eliminar hoja

·         Boton derecho en la etiqueta de la hoja, menu contextual: Eliminar...

Cambiar nombre

Una hoja puede tener un nombre de hasta 31 caracteres. Para cambiar el nombre:

·         Doble clic sobre la etiqueta

·         Boton derecho sobre la etiqueta y menu contextual Cambiar nombre

Encabezado y pie de pagina

Menu Ver → Encabezado y pie de pagina puedo elegir un encabezado y un pie con el nombre de la hoja, numero de pagina, fecha, etc.

Formato de la hoja de calculo

Se puede dar formato a las celdas antes o despues de introducir los datos y en varias hojas simultaneamente.

Cambiar ancho de columnas

·         Arrastrando el borde derecho del encabezado de la columna cambia el puntero del mouse y se arrastra eligiendo el ancho

·         Si se hace doble clic en el borde del ancho se modifica a la entrada mas larga de la columna.

·         Menu Formato → Columna → Ancho

Modificar altura de filas

·         Arrastrar el borde inferior del encabezado de fila

·         Doble clic sobre el borde inferior del encabezado de fila se acomoda a la entrada mas alta

·         Menu Formato → Fila → Alto

Formato de la celda

En el menu Formato → Celda se abre cuadro de Formato de celdas con las siguientes pestañas:

Alineacion

Para establecer la alineacion y orientacion del texto o ajustar el texto a una celda

Fuente

Para cambiar el tipo de letra, el estilo, tamaño, subrayado, efectos y color del texto

Bordes

Añadir bordes y cambiar color, estilo de linea

Tramas (Relleno)

Se puede cambiar el color del fondo de la celda

Ejercicio

1.    Cambiar nombre de la hoja a Horario

2.    Personalizar encabezado de pagina en el centro texto “Horario de clases”

3.    Personalizar pie de pagina con fecha y numero de pagina.