Presentaci

1
UTILIDAD DE LA HOJA DE CÁLCULO PARA LA EMPRESA
Es una herramienta de software de ayuda a la
decisión que utiliza fundamentalmente información
numérica.

• Utilidad fundamental Modelizar un problema que
  requiere de cálculos numéricos.
• Creación de fórmulas.
• Utilización de fórmulas predefinidas
  financieras, estadísticas, matemáticas, ...
• Aplicación a problemas que requieren cuadros
  interrelacionados presupuestos de capital,
  evaluación de inversiones, ....
• Simulación del tipo que pasaría si.
• Análisis de distintos escenarios de un mismo
  problema.
• Obtención de gráficos.
• Automatización de procesos (macros).

2
CONCEPTOS GENERALES
Una hoja de cálculo es una aplicación informática
estructurada en filas y columnas, en cuyas
intersecciones (celdas) se pueden introducir
textos, números, fórmulas y funciones que
permitirán la resolución de modelos o problemas.
ESTRUCTURA DE UN LIBRO DE TRABAJO EN EXCEL
Introducción de fórmulas o funciones
Celda activa
Pestañas de hojas
3
Introducción de datos

• Texto Por defecto se alinea a la izquierda
• Números Se puede introducir . , y cuando
  corresponda.
• Posteriormente, se puede modificar el aspecto de
  los datos a partir de la opción FORMATO.
• Ej Introducir todos los textos de la PRÁCTICA
  1 y los datos iniciales (Rango C9D12 H9H12).

Creación de fórmulas

• Se comienza introduciendo el signo
• Se utilizan operadores
• Aritméticos , -, , /, , gt, lt, gt, lt.
• Lógicos Y, O, NO, .....
• Ej Práctica 1 Podríamos poner en E9
  600427,92. Inconveniente Sólo sirve para este
  caso.
• Verdadera operatividad Utilizar referencias de
  celdas. Objetivo Poder copiar después esta
  fórmula. Ej En E9 C9D9

4
Copiar fórmulas

• Tipos de referencias
• Relativas Se ajustan a la posición que ocupen
  cada momento.
• Ej Si en E9 ponemos la fórmula C9D9, se
  traduce por multiplica la celda que está dos
  posiciones a la izquierda por la que está una
  posición a la izquierda.
• Al copiar la fórmula a E10, su contenido cambia
  a C10D10
• Cómo copiar

C9D9
arrastrar

• Absolutas La referencia de la celda permanece
  invariable, aún cuando la copiemos a otra celda.
• Ej Cálculo del importe IVA. Si en F9 ponemos
  E9F6, la primera fórmula sería válida, pero al
  copiar ya no funcionaría.
• Fórmula correcta E9F6 Los símbolos de
  dólar dejan invariable la celda F6. Esto es una
  referencia absoluta.

5
Copiar fórmulas

• Tipos de referencias
• Mixtas Cuando sólo interesa dejar invariable
  una de las coordenadas de una celda fila o
  columna.
• Se pone el sólo en la fila o columna.
• Otras consideraciones Si el resultado final de
  una fórmula no da el formato final Utilizar
  FORMATO del menú principal. Ej Fórmula
  porcentaje (I9).
• Práctica 2 Tabla de descuentos

Funciones

• Se trata de fórmulas que ya están predefinidas.
• Tipos (Asistente de funciones)
• Matemáticas y trigonométricas SUMA, SUMAR.SI,
  REDONDEAR, ....
• Lógicas SI, SI anidadas...
• Financieras PAGO, PAGOINT, PAGOPRIN, VNA, TIR,
  ....
• Búsqueda y Referencia BUSCARV, BUSCARH, BUSCAR,
  ....
• Estadísticas CONTAR.SI, CONTAR, PROMEDIO,
  ......
• Estructura de una función NOMBRE(ARGUMENTO
  1ARGUMENTO 2, ......ARGUMENTO N)
• Ej Práctica 1 Para sumar muchas celdas mejor
  SUMA. En G14 SUMA(G9G12). Para indicar de una
  celda origen a otra de destino se usan los .
  Esto es lo que se llama un RANGO.

6

• Cómo se introduce un rango en una función
• Escribiéndolo.
• Seleccionándolo directamente mediante el ratón.

FUNCIONES LÓGICAS
SI(PRUEBA_LÓGICAVALOR_SI_VERDADEROVALOR_SI_FALS
O)

• Prueba_Lógica Condición o conjunto de
  condiciones que puede tomar un valor verdadero o
  falso.
• Valor_Si_Verdadero Valor que devolverá la
  función cuando la expresión anterior sea
  verdadera. Puede ser un número, texto, fórmula o
  función.
• Valor_Si_Falso Valor que devolverá la función
  cuando la expresión anterior sea falsa. Puede ser
  un número, texto, fórmula o función.
• Ej. (práctica 3) En F9 SI(B9gt5APTONO
  APTO)

Los textos en las fórmulas se deben incluir entre
comillas.
7
OPERADORES LÓGICOS

• Cuando dentro de la prueba lógica se quieren
  enlazar varias condiciones se utilizan operadores
  lógicos.
• Tipos
• Y (CONDICIÓN 1CONDICIÓN 2 .....CONDICIÓN N)
• Se utiliza cuando queremos que todas las
  condiciones sean ciertas para que el resultado de
  la prueba lógica sea verdadero. Si no se cumple
  alguna condición el resultado final será falso.
• Cómo se introduce en la función SI?.
• SI(Y(CONDICIÓN 1CONDICIÓN 2 ...CONDICIÓN
  N)VALOR_SI_VERDADEROVALOR_SI_FALSO)
• Ej Práctica 3 Qué expresión pondría en G9?.
• O (CONDICIÓN 1CONDICIÓN 2 .....CONDICIÓN N)
• En este caso basta con que una de las
  condiciones sea verdadera para que el resultado
  final también lo sea.
• La introducción sigue el mismo formato que con
  el operador Y.
• Ej Práctica3 Qué expresión pondría en H9?.

8
FUNCIONES SI ANIDADAS

• Cuando sea necesario evaluar varias funciones SI
  en una misma expresión podemos enlazarlas entre
  si. Se suele utilizar cuando la preuba_lógica
  puerde tomar más de dos valores.
• SI(PRUEBA_LÓGICAVALOR_SI_VERDADEROSI(PRUEBA_LÓGI
  CAVALOR_SI_VERDADEROSI(PRUEBA_LÓGICAVALOR_SI_VE
  RDADERO ..........VALOR_SI_FALSO)))
• Ej Fórmula I9.
• Se pueden combinar los operadores lógicos con
  las funciones SI anidadas. En este caso la
  expresión sería
• SI(Y(CONDICIÓN1CONDICIÓN2....CONDICIÓNN)VALOR_
  SI_VERDADEROSI(PRUEBA_LÓGICAVALOR_SI_VERDADEROS
  I(PRUEBA_LÓGICAVALOR_SI_VERDADERO
  ..........VALOR_SI_FALSO)))
• Ej Expresión J9.
• Tenga en cuenta que a veces las expresiones se
  pueden reducir comenzando por evaluar la
  condición más excluyente. Siempre interesa
  expresiones más cortas. Ej J9.
• En la PRUEBA_LÓGICA se pueden incluir funciones
  o fórmulas creadas por el usuario. Ej La celda
  K9 en la práctica 2. Se utiliza la función
  PROMEDIO.

Para repasar las direcciones absolutas y
relativas y las funciones lógicas Práctica 4
9
FUNCIONES FINANCIERAS
AMORTIZACIÓN DE PRÉSTAMOS (MÉTODO FRANCÉS)
PAGO(TasaNperVaVfTipo)

• Calcula el pago o anualidad constante de cada
  periodo de amortización.
• Tasa Tipo de interés por periodo del préstamo.
• Nper Número total de pagos del préstamos.
• Va Valor actual del préstamo.
• Vf Valor opcional. Saldo que desea obtener
  después de efectural el último pago. Por defecto
  es cero.
• Tipo Si no indicamos nada se entiende que el
  pago es al final del periodo (0). Si incluimos un
  1 se entiende que es al comienzo del periodo.
• Ej Práctica 5 Cómo calcularía el pago en la
  celda G12?.

PAGOINT(TasaPeriodoNperVaVfTipo)

• Calcula los intereses que se pagarán en cada
  periodo
• Periodo Es el periodo para el que se está
  calculando los intereses.
• Se puede utilizar esta función o pensar en una
  fórmula que calcule los intereses. Hacerlo en
  práctica 5. Celda D12.

10
FUNCIONES FINANCIERAS
PAGOPRIN(TasaPeriodoNperVaVfTipo)

• Calcula el pago correspondiente al principal o
  cuota de amortización financiera.
• También puede calcularse a partir de una
  fórmula. Práctica 5. Celda E12.

Ejercicio CREAR EL RESTO DE FÓRMULAS PARA LA
PÁCTICA 5
AMORTIZACIÓN DE PRÉSTAMOS (CAPITAL AMORTIZADO
CONSTANTE)

• Para esta modalidad de préstamos no existen
  funciones predefinidas. Hemos de hacerlo con
  fórmulas.
• Práctica 6 Crear las fórmulas necesarias para
  este cuadro de amortización
• Práctica 7 Combinar funciones financieras con
  funciones lógicas.

11
FUNCIONES FINANCIERAS
VNA(TasaValor1Valor2.....)

• Devuelve el valor neto actual de una inversión a
  partir de una tasa de actualización y unos flujos
  de caja.
• Tasa tasa de descuento o actualización.
• Valor Flujos de caja que se dan al final de
  cada periodo.
• Práctica 8 Cálculo del VAN de dos formas
• Actualización de los flujos de caja.
• Utilización de la función VNA

FUNCIONES DE BÚSQUEDA Y REFERENCIA
BUSCARV(Valor BuscadoMatriz_buscar_enIndicador_
de_columnaOrdenado)

• Busca un valor en la primera columna de una
  tabla y devuelve el resultado que corresponda al
  número de columna que le indiquemos. La tabla ha
  de estar ordenada de forma ascendente.

12
FUNCIONES DE BÚSQUEDA Y REFERENCIA

• Argumentos de la Función Buscarv
• Valor Buscado Valor que que se ha de buscar en
  la matriz.
• Matriz_buscar_en Se introduce indicando primera
  celdaúltima celda de la matriz, incluyendo los
  valores buscados.
• Indicador_de_columna Se indica el número de
  columna que corresponde al resultado que queremos
  obtener. Las columnas se numeran de izquierda a
  derecha.
• Ordenado Este argumento puede tomar dos
  valores
• Falso Busca en la matriz el valor exacto.
• Verdadero Permite buscar valores aproximados.
  Útil cuando buscamos dentro de intervalos. Para
  que funcione esta opción la tabla debe estar
  ordenada.
• Inconvenientes de esta función No se pueden
  obtener resultados que estén a la izquierda de la
  columna donde están los valores buscados.

• Práctica 9 Cuáles serían las funciones que
  pondrías en las celdas C12 y D12?.

BUSCARH(Valor BuscadoMatriz_buscar_enIndicador_
de_filaOrdenado)

• Funciona de forma similar a Buscarv, sólo que
  busca por filas.

• Práctica 9 Cuáles serían las funciones que
  pondrías en las celdas C20 y D20?.

13
FUNCIONES DE BÚSQUEDA Y REFERENCIA
BUSCAR(Valor_BuscadoVector_de_ComparaciónVector
_Resultado)

• Con está función es indiferente la columna o
  fila donde estén colocados los valores buscados.
• Vector_de_Comparación Es el rango que indica la
  fila o columna de la matriz donde están los
  valores buscados.
• Vector_Resultado Rango que indica la fila o
  columna de la matriz donde se encuentran los
  resultados.
• Ventajas sobre Buscarv
• Se puede obtener resultados que estén a la
  izquierda de los valores buscados.
• Inconvenientes Para que funcione la búsqueda
  aproximada los valores que introduzcamos en la
  tabla deben estar ordenados.
• Práctica 10 Búsqueda de valores exactos
  Función Buscar.
• Práctica 11 Búsqueda de valores aproximados.
• Práctica 12 Combinar fórmulas con funciones de
  búsqueda.

14
OTRAS FUNCIONES
REDONDEAR.MAS(NúmeroNúmero_de_decimales)

• Redondea un número por exceso, esto es, en
  dirección contraria a cero.
• Número valor que se quiere redondear. El número
  puede provenir de una función o fórmula.
• Número de decimales Si se omite este valor o se
  pone cero, se redondea al número entero
  inmediatamente superior.
• Práctica 12b Sólo varia en relación a la 12 en
  el redondeo del valor de las dietas.
• Otras variantes

REDONDEAR.MENOS(NúmeroNúmero_de_decimales)

• Redondea a un número por defecto, esto es, en
  dirección a cero.

REDONDEAR(NúmeroNúmero_de_decimales)

• Redondeo lógico al número de decimales que
  indiquemos .

15
OTRAS FUNCIONES

• Práctica 13 Funciones de búsqueda y direcciones
  mixtas

CONTAR.SI(RangoCriterio)

• Cuenta dentro de un rango de celdas aquellas que
  cumplan con una condición o criterio.
• Rango Conjunto de celdas en las que se quiere
  contar.
• Criterio Condición que puede aparecer en forma
  de número, texto o expresión y que determina qué
  celdas deben contarse.

SUMAR.SI(RangoCriterioRango suma)

• Suma las celdas que cumplen con una determinada
  condición o criterio.
• Rango Conjunto de celdas para las que se evalúa
  el criterio.
• Criterio Tiene el mismo significado que en la
  función anterior.
• Rango_suma Corresponde a las celdas que se van
  a sumar. Si no se indica nada se suman las celdas
  incluidas en el rango.

• Práctica 14 Funciones CONTAR.SI y SUMAR.SI

16
UTILIZACIÓN DE VÍNCULOS

• Consiste en conectar (vincular) distintas hojas
  entre si a través de fórmulas de referencia
  externa, esto es, fórmulas que hacen referencia a
  celdas situadas en otra hoja de cálculo.
• Utilidad dividir un modelo grande o complicado
  en otros más sencillos que realicen cada uno su
  propia tarea.
• Cuando se utilizan vínculos es conveniente
  cambiar el nombre de las distintas hojas que
  intervienen en el modelo. (FORMATO, HOJA, CAMBIAR
  NOMBRE)

NOMBRE DE LA HOJA!CELDA

• Práctica 15 Utilización de vínculos
• Poner a las cuatro primeras hojas los siguiente
  nombres Cataluña, Andalucía, Madrid y
  Trimestre1.
• Introducir los datos iniciales en las hojas
  Cataluña, Andalucía y Madrid.
• Crear en la hoja Trimestre1 la fórmula para las
  ventas del primer trimestre y del artículo A de
  Cataluña y arrastrar la fórmula para obtener los
  resultados correspondientes a los artículos B, C
  y D. Hacer lo mismo para las regiones de
  Andalucía y Madrid.

NOTA Si para llevarnos los datos de la hoja
Cataluña a la hoja Trimestre1 utilizamos la
opción COPIAR y PEGAR, cuando cambiemos los datos
de la hoja Cataluña no se actualizarán en la hoja
Trimestre1
17
(No Transcript)
18

• Naturalmente podemos crear todo tipo de fórmulas
  que incluyan datos que están en hojas distintas.
• Ej Práctica 16 Creación de un modelo para
  gestionar un videoclub (Combinación de vínculos
  con las funciones Buscarv, Contar.si y Sumar.si

ANÁLISIS HIPOTÉTICO

• Una de las utilidades fundamentales de la hoja de
  cálculo es hacer análisis del tipo qué pasaría
  si.....?. Este tipo de análisis se puede hacer de
  varias formas
• Manual Se introducen los valores en las celdas
  de entrada y se observan los resultados de las
  celdas que contienen fórmulas.
• Tabla de datos Consiste en crear una tabla que
  contenga tanto las celdas cambiantes como los
  resultados. Posibilidades
• Tabla de datos de una entrada Se muestran los
  resultados de varias fórmulas en función de los
  valores de una celda.
• Tabla de datos de dos entradas Permite observar
  la variación en una fórmula a partir de los
  cambios en dos celdas de entrada.

• Escenario Permite obtener informes en los que
  se pueda observar la variación en distintos
  resultados a partir de varias celdas de entrada
  (hasta 23).

19
Tabla de datos con una sola entrada
Práctica 17 Cambios en pago total, intereses y
pago mensual ante variaciones en el tipo de
interés nominal

• Introducir datos iniciales.
• Crear fórmulas de resultados.
• Preparar tabla
• Introducir las fórmulas en E5, F5 y G5.
• Completar la columna de tipos de interés (D5 a
  D12).
• Marcar rango G5 a D12
• DATOS, TABLA

20
Tabla de datos con dos entradas

• Supongamos que para el mismo ejemplo anterior
  quisiéramos obtener una tabla que nos muestre
  cómo variarían los pagos, ante cambios en el tipo
  de interés y en el número de pagos anuales
  (Práctica 18)

21
Escenarios
Ahora bien, si queremos obtener un informe en el
que se nos muestre lo que pagaríamos por período,
los pagos totales y los intereses totales, ante
variaciones en el importe del préstamo, tipo
anual, número de pagos y número de años, no
podríamos utilizar las tablas
Solución Crear escenarios para cada caso.

• Vamos a tomar la práctica 5 como plantilla para
  diseñar la práctica 19.
• 1) Crear el escenario HERRAMIENTAS, ESCENARIO,
  AGREGAR

22
2) Introducir valores para cada celda cambiante
3) Obtener un resumen de cada escenario Para
este punto te pedirá cuáles son las celdas
resultantes
23
(No Transcript)
24
Podríamos mejorar el modelo anterior, si a las
celdas cambiantes y de resultados les damos un
nombre
INSERTAR, NOMBRE, DEFINIR
25
Práctica 20 Escenarios, vínculos y función SI
26
ANÁLISIS HIPOTÉTICO INVERSO

• Consiste en encontrar el valor de una o más
  celdas de entrada que hacen que se de un
  determinado resultado en las celdas dependientes.
• Herramientas posibles
• Buscar objetivo sirve para determinar el valor
  que se requiere en una celda de entrada para
  obtener el resultado deseado en una celda
  dependiente (fórmula).
• Solver en este caso se determinan los valores
  requeridos en varias celdas de entrada para
  obtener el resultado deseado, además permite
  añadir restricciones.

BUSCAR OBJETIVO
Práctica 21 A partir de los datos iniciales del
préstamo, se obtienen las fórmulas de pago. A
continuación, suponga que el usuario se plantea
lo siguiente si pudiera pagar 400 euros al mes,
qué capital podía pedir prestado?
27
(No Transcript)
28
OPTIMIZACIÓN MEDIANTE SOLVER

• Nos valdremos del ejemplo de la práctica 22
• Proceso a seguir
• Preparar la hoja de cálculo con los valores y
  fórmulas necesarios.
• Acceder al cuadro de diálogo de Solver e
  introducir los datos para la celda objetivo,
  celdas cambiantes y restricciones.

29
OTRAS UTILIDADES DE LA HOJA DE CÁLCULO

• CREACIÓN DE GRÁFICOS

• La hoja de cálculo también es una potente
  herramienta de creación de gráficos, los cuales
  son creados a partir de los datos introducidos en
  las hojas.
• Ejemplo Creación de un gráfico a partir de los
  datos de amortización de un préstamo. (Práctica
  5).

• Abrir la práctica 5 y marcar los datos a
  representar en la hoja de cálculo.
• INSERTAR GRÁFICO.
• Accedemos a un asistente de gráficos en el que
  tendremos que elegir tipo de gráfico, si
  queremos que se representen por filas o por
  columnas y títulos del gráfico.
• Finalmente, elegimos si lo queremos en la misma
  hoja o en una hoja aparte.
• Una vez que el gráfico se incrusta en la hoja,
  podemos modificar sus distintas opciones para
  mejorar su presentación.

Práctica 23 Crear un gráfico a partir de los
datos de la práctica 5.
30
OTRAS UTILIDADES DE LA HOJA DE CÁLCULO

• PROTECCIÓN DE LA HOJA DE CÁLCULO

• Una vez que hemos creado un modelo de hoja de
  cálculo sería conveniente bloquear aquellas
  celdas que contienen fórmulas y que, por tanto,
  no queremos que sean modificadas.
• Procedimiento
• Marcar aquellos datos que tienen que variar,
  esto es, datos iniciales.
• Entrar en FORMATO, CELDAS, PROTEGER.
• Desactivar el atributo de bloqueado.
• Proteger toda la hoja HERRAMIENTAS, PROTEGER,
  HOJA (la contraseña es opcional).
• Probar que la protección es correcta, esto es,
  que los datos iniciales se pueden modificar
  mientras que las fórmulas no.
• Práctica Proteger la hoja de la práctica 5.

• MACROS

• Son programas que permiten definir una serie de
  instrucciones sobre la hoja de cálculo.

31
OTRAS UTILIDADES DE LA HOJA DE CÁLCULO

• Utilidad automatizar la ejecución de
  operaciones en la hoja de cálculo
• En Excel se puede llegar a programar macros
  utilizando el lenguaje Visual Basic, pero existe
  otro procedimiento más sencillo
• Pasos a seguir
• Definición de la macro
• Plantear que queremos que haga la macro. Ej
  Sobre la práctica 16 (videoclub) vamos a crear
  una macro que borre los datos iniciales de la
  hoja RETIRADA.
• Definir la macro HERRAMIENTAS, MACRO, GRABAR
  NUEVA MACRO. En esta ventana de diálogo dar un
  nombre a la macro y elegir si se grabará en este
  libro o en uno nuevo.
• Una vez introducidos los valores anteriores
  aparecerá un botón para indicarnos que estamos en
  modo grabación. Realizar manualmente las acciones
  que queremos que queden grabadas en la macro.
• A continuación elegir en el menú de HERRAMIENTAS
  MACROS, la opción DETENER GRABACIÓN.
• Ejecución de la macro. Elegir HERRAMIENTAS,
  MACRO, MACROS y seleccionar la macro grabada.
• Para evitar que cada vez que se quiera ejecutar
  la macro tengamos que entrar en las opciones
  anteriores, se puede crear un botón y asignarlo a
  la macro anterior.

32
OTRAS UTILIDADES DE LA HOJA DE CÁLCULO

• Proceso VER, BARRA DE HERRAMIENTAS, CUADRO DE
  CONTROLES.
• Elegir botón.
• Dibujar el botón y asignarle la macro que le
  corresponda.
• Modificar el texto que queremos que contenga el
  botón.
• Ej Práctica 24 Crear para la hoja RETIRADA un
  botón que ejecute la macro anterior y que
  contenga el texto LIMPIAR DATOS DEL MES.