utilidad de la hoja de cÁlculo para la empresa es una herramienta de software de ayuda a la...

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).

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.

Introducción de fórmulas o funciones

Celda activa

Pestañas de hojas

ESTRUCTURA DE UN LIBRO DE TRABAJO EN EXCEL

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 C9:D12; H9:H12).

Creación de fórmulas

• Se comienza introduciendo el signo =

• Se utilizan operadores:

• Aritméticos: +, -, *, /, ^, >, <, >=, <=.

• Lógicos: Y, O, NO, .....

• Ej: Práctica 1: Podríamos poner en E9 =600*427,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 =C9*D9

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 =C9*D9, 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 =C10*D10

• Cómo copiar: =C9*D9

+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 =E9*F6, la primera fórmula sería válida, pero al copiar ya no funcionaría.

• Fórmula correcta: =E9*$F$6: Los símbolos de dólar dejan invariable la celda F6. Esto es una referencia absoluta.

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 1;ARGUMENTO 2, ......ARGUMENTO N)

• Ej: Práctica 1: Para sumar muchas celdas mejor: =SUMA. En G14: =SUMA(G9:G12). Para indicar de una celda origen a otra de destino se usan los :. Esto es lo que se llama un RANGO.

• 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ÓGICA;VALOR_SI_VERDADERO;VALOR_SI_FALSO)

• 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(B9>=5;”APTO”;”NO APTO”)

Los textos en las fórmulas se deben incluir entre comillas.

OPERADORES LÓGICOS

• Cuando dentro de la prueba lógica se quieren enlazar varias condiciones se utilizan operadores lógicos.

• Tipos:

• Y (CONDICIÓN 1;CONDICIÓ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 1;CONDICIÓN 2; ...;CONDICIÓN N);VALOR_SI_VERDADERO;VALOR_SI_FALSO)

• Ej: Práctica 3: ¿Qué expresión pondría en G9?.

• O (CONDICIÓN 1;CONDICIÓ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?.

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ÓGICA;VALOR_SI_VERDADERO;SI(PRUEBA_LÓGICA;VALOR_SI_VERDADERO;SI(PRUEBA_LÓGICA;VALOR_SI_VERDADERO; ..........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ÓN1;CONDICIÓN2;....;CONDICIÓNN);VALOR_SI_VERDADERO;SI(PRUEBA_LÓGICA;VALOR_SI_VERDADERO;SI(PRUEBA_LÓGICA;VALOR_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

FUNCIONES FINANCIERAS

AMORTIZACIÓN DE PRÉSTAMOS (MÉTODO FRANCÉS)

=PAGO(Tasa;Nper;Va;Vf;Tipo)

• 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(Tasa;Periodo;Nper;Va;Vf;Tipo)

• 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.


=PAGOPRIN(Tasa;Periodo;Nper;Va;Vf;Tipo)

• 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.


=VNA(Tasa;Valor1;Valor2;.....)

• 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 Buscado;Matriz_buscar_en;Indicador_de_columna;Ordenado)

• 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.


• 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 Buscado;Matriz_buscar_en;Indicador_de_fila;Ordenado)

• 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?.


=BUSCAR(Valor_Buscado;Vector_de_Comparación;Vector_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.

OTRAS FUNCIONES

=REDONDEAR.MAS(Número;Nú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úmero;Número_de_decimales)

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

=REDONDEAR(Número;Número_de_decimales)

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

OTRAS FUNCIONES

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

=CONTAR.SI(Rango;Criterio)

• 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(Rango;Criterio;Rango 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

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

• 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

• 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).

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.

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

1) Introducir datos iniciales.

2) Crear fórmulas de resultados.

3) Preparar tabla

1) Introducir las fórmulas en E5, F5 y G5.

2) Completar la columna de tipos de interés (D5 a D12).

3) Marcar rango G5 a D12

• DATOS, TABLA

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)

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

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

Podríamos mejorar el modelo anterior, si a las celdas cambiantes y de resultados les damos un nombre:

INSERTAR, NOMBRE, DEFINIR

Práctica 20: Escenarios, vínculos y función SI

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?

OPTIMIZACIÓN MEDIANTE SOLVER

• Nos valdremos del ejemplo de la práctica 22

• Proceso a seguir:

1. Preparar la hoja de cálculo con los valores y fórmulas necesarios.

2. Acceder al cuadro de diálogo de Solver e introducir los datos para la celda objetivo, celdas cambiantes y restricciones.

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.


• 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.


• 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:

1. 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.

2. 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.


• 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.

utilidad de la hoja de cÁlculo para la empresa es una herramienta de software de ayuda a la...

Documents