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