compendio de excel paso a paso 2014
TRANSCRIPT
i
COMPENDIO
MANEJO DE EXCEL PASÓ A PASO
ASIGNATURA, COMPUTACIÓN II
Autor: Ing. Collaguazo Milton Stalin
Machala – El Oro
ii
COMPENDIO
MANEJO DE EXCEL PASÓ A PASO
ASIGNATURA, COMPUTACIÓN II
Autor: Ing. Collaguazo Milton Stalin
iii
ÍNIDICE
1. INTRODUCCIÓN ........................................................................................... 7
2. CONCEPTO EXCEL ...................................................................................... 8
2.1. INICIO DE MICROSOFT EXCEL ............................................................... 8
2.2. ELEMENTOS DEL ÁREA DE TRABAJO DE EXCEL ............................. 7
2.17. LIBRO TRABAJO DE EXCEL .................................................................. 12
2.18. FORMAS DE GUARDAR UN DOCUMENTO O LIBRO DE EXCEL . 12
2.19. FORMAS DE PROTEGER UN DOCUMENTO O LIBRO DE EXCEL12
3. FORMATOS DE EXCEL ............................................................................. 13
3.1. FORMATOS DE FILAS, COLUMNAS Y ETIQUETAS .......................... 13
3.2.FORMATO CONDICIONAL, TABLAS Y ESTILOS DE CELDAS ........ 13
3.2.1. Formatos de tablas ...................................................................................... 14
3.2.2. Pasos a seguir para este ejercicio ................................................................ 15
3.2.3. Formato condicional ..................................................................................... 16
3.2.4. Ejercicios de formato condicional de barra de datos .................................. 17
3.2.5. Ejercicio de formato condicional de conjunto de íconos ............................ 18
3.2.6. Estilos de celda ............................................................................................. 18
3.2.7. Ejercicio de estilos de celdas ........................................................................ 18
3.3. FORMATO DE CELDAS ..................................................................................... 19
3.3.1. Categorías .................................................................................................... 19
3.3.2. En la categoría número tenemos varias pestañas ...................................... 20
3.3.3. Borrar formatos ............................................................................................ 21
4. FÓRMULAS ...................................................................................................... 22
4.1. PARTES DE UNA FÓRMULA. ............................................................................ 22
4.2. OPERADORES ................................................................................................... 23
4.3. OPERADORES DE CÁLCULO EN LAS FÓRMULAS. ............................................ 23
iv
4.3.1. Tipos de operadores ..................................................................................... 24
4.3.2. Operadores aritméticos. .............................................................................. 24
4.3.3. Operadores de comparación ........................................................................ 25
4.3.4. Operadores de referencia ............................................................................ 25
4.4. REFERENCIA DE CELDAS .................................................................................. 26
4.4.1. Referencia de celda relativa ......................................................................... 26
4.4.2. Referencias relativa absoluta. ..................................................................... 27
4.6. UTILIZAR NOMBRES EN LAS FÓRMULAS .......................................... 29
4.6.1. Pasos para dar nombre a los rangos y celdas de una tabla de datos ......... 30
4.7. FÓRMULAS MATRICIALES. ............................................................................... 31
4.7.1. Pasos para realizar el cálculo de una fórmula Matricial ............................. 32
5. FUNCIONES EN EXCEL ..................................................................................... 34
5.1. FUNCIONES MATEMÁTICAS TRIGONOMÉTRICAS .......................................... 36
5.3. FUNCIONES MATEMÁTICAS FINANCIERAS ..................................................... 45
5.4. FUNCIONES LÓGICAS ............................................................................... 49
6. ANÁLISIS DE DATOS EN EXCEL. ....................................................................... 52
6.1. PASOS PARA CARGAR LA HERRAMIENTA PARA ANÁLISIS DE
DATOS ............................................................................................................... 53
6.2. HERRAMIENTAS PARA ANÁLISIS ESTADÍSTICO ............................. 54
6.2.1. Coeficiente de correlación .......................................................................... 54
6.2.2. Propiedades del Coeficiente de correlación .............................................. 54
6.3. HERRAMIENTA DE ANÁLISIS DE LA COVARIANZA Y COVAR. .. 56
6.4. CÁLCULO DE LA VARIANZA .................................................................. 57
6.5. HISTOGRAMA DE FRECUENCIA CON FÓRMULAS ......................... 57
6.5.1. Pasos para crear histograma con fórmula. ............................................... 58
6.5.2. Pasos para crear histograma con la herramienta análisis de datos. ...... 59
6.6. ESTADÍSTICA DESCRIPTIVA CON LA HERRAMIENTA ANÁLISIS
DE DATOS. .................................................................................................... 61
v
6.6.2. Resumen de estadística descriptiva............................................................ 62
6.7. ANÁLISIS DE VARIANZA DE UN FACTOR. .......................................... 62
6.7.1. Pasos para realizar el cálculo del análisis de varianza de un factor. .... 63
6.8. ANÁLISIS DE VARIANZA DE DOS FACTORES CON VARIAS
MUESTRAS POR GRUPO. ......................................................................... 64
6.8.1. Pasos para realizar el análisis de varianza de dos factores con varias
muestras por grupo. ...................................................................................... 65
6.9. ANÁLISIS DE VARIANZA DE DOS FACTORES CON UNA SOLA
MUESTRA POR GRUPO ............................................................................ 66
6.9.1. Pasos para realizar el análisis de varianza de dos factores con una sola
muestra por grupo ......................................................................................... 67
6.10. PRUEBA T PARA DOS MUESTRAS SUPONIENDO VARIANZA
DESIGUALES. .............................................................................................. 68
6.10.1. Pasos para procesar la prueba t para dos muestras suponiendo
varianzas desiguales. ..................................................................................... 69
6.11. ANÁLISIS DE MUESTRA O MUESTREO. ............................................ 70
6.11.1. Pasos para realizar el análisis del muestreo o muestra ......................... 70
6.12. ANÁLISIS MEDIA MÓVIL ....................................................................... 72
6.12.1. Pasos para realizar el ejercicio de Media móvil ..................................... 72
7. FUNCIONES FINANCIERAS. ............................................................................. 74
8. GRÁFICOS ........................................................................................................ 87
9 TABLAS DINÁMICAS ...................................................................................... 103
9.1 Gráficos dinámicos. ........................................................................................ 106
10 SOLVER .......................................................................................................... 108
11 BIBLIOGRAFÍA ............................................................................................... 117
7
1. INTRODUCCIÓN
El presente compendio cubre los temas que son fundamentales para poder
trabajar en Excel, siendo este un programa que es parte de Microsoft
Office que contiene procedimientos recopilados para los usuarios. En este
compendio de Excel paso a paso se puede encontrar conceptos de
comandos, fórmulas, funciones y gráficos para poder realizar sus trabajos.
Este Compendio nos dará a conocer paso a paso de manera clara y precisa
los conocimientos básicos sobre el manejo de Excel convirtiéndose en una
poderosa herramienta de aprendizaje para la comprensión y solución de
problemas por medio de la aplicación y procesamiento de la amplia gama
de funciones Estadísticas, Matemáticas, Financieras y lógicas,, filtros,
graficación de datos, base de datos, regresiones e histogramas y funciones
de análisis, Solver de la cual muchas personas no utilizan o no saben cuál es
su función, trataremos que cada temas sea representado con ejercicios paso a
paso, los mismos que servirá para perfeccionar sus habilidades y estar
siempre actualizado con las nuevas versiones del programa.
A esto tenemos que agregarle una gran cantidad de librerías y recursos para
desarrollar toda clase de ejercicios desde los más sencillos hasta los más
avanzados, aplicaciones de entorno gráfico y como las opciones de análisis
de datos
Esperando que este compendio contribuya a los usuarios de Excel el interés
de profundizar cada día más lo aprendido de este maravilloso programa
para el dominio de fórmulas, funciones y gráficos, esperando al final
obtener el dominio del programa contribuyendo así a la difusión de la nueva
versión de Excel.
8
2. CONCEPTO EXCEL
Excel es un programa que viene incluido en el paquete de Microsoft Office,
el mismo que contiene hojas de cálculo con sus respectivos menús, cintas
de opciones y herramientas de análisis muy útiles para las personas que
trabajan con números y que necesitan realizar cálculos con ellos. La hoja de
cálculo sirve para almacenar y organizar la información, es un área de
trabajo en forma de matriz, como si fuera una hoja de papel cuadriculado,
formada por un número determinado de filas y columnas.
Hoy en día Excel ha incorporado más de 350 funciones desde las
financieras, estadísticas, matemáticas y trigonométricas entre otras, llegar a
dominar todas las funciones de Excel puede resultar una tarea difícil, aunque
en realidad muy pocas personas necesitan utilizar todas las funciones de
Excel de manera cotidiana ya que de acuerdo al área de trabajo será el grupo
de funciones de Excel que utilizarán con mayor frecuencia.
2.1. INICIO DE MICROSOFT EXCEL
El entorno que funciona Excel es con el sistema Operativo Windows 7.
Existiendo varias maneras para iniciar el programa, una de ellas es hacer
clic en el botón inicio de la barra de tares de Windows 7, luego en todos los
programas, carpeta de Microsoft Office y finalmente en el ícono Microsoft
Word, se puede agregar un ícono de Word como acceso directo.
La presentación de la pantalla de Excel nos muestra una serie de elementos
que son necesarios tomarlos en consideración para poder desenvolvernos
con mayor eficiencia en su área de trabajo.
7
ELEMENTOS DEL ÁREA DE TRABAJO DE EXCEL
Barra de
menú
Botón de acceso rápido
Barra del
título
Botones
clásicos
Cinta de opciones agrupada
s
Cuadro de nombres
Insertar función
Celd
a
Etique
tas
Total de filas 1048.576
Barra de estado
Cálculo automático
Rango
Barra de fórmula
Encabezados de columnas
Barra de desplazamiento vertical
Barra de desplazamiento horizontal
Aumenta y disminuye El zoom
Encabezado
de fila
Total columnas 16.384
8
2.2. BOTÓN DE ACCESO RÁPIDO
En esta opción podemos tener acceso a varios comandos entre los principales se
encuentran, más comandos que nos permite ingresar al cuadro de diálogo,
opciones de Excel, en este cuadro se encuentra opciones que nos permiten
personalizar el área de trabajo de Excel. Para ingresar a más comandos también
podemos hacer clic derecho con el mouse en cualquier ficha de las cintas de
opciones.
2.3. BARRA DEL TÍTULO
En esta sección se puede observar el nombre del archivo o de la ventana abierta.
2.4. BARRA DE MENÚ
También llamadas etiquetas o fichas es otra manera de agrupar las barras de
herramientas que al hacer clic se despliegan otras opciones.
Figura 2. Barra de herramientas del menú.
2.5. BOTONES CLÁSICOS
Son los botones (maximizar, minimizar y cerrar) de las ventanas de Excel.
Figura 3. Botones maximizar, minimizar y cerrar.
2.6. CONJUNTO DE HERRAMIENTA O CINTAS DE OPCIONES
AGRUPADAS
9
Son los diferentes íconos agrupados de cada menú, en este caso está activado el
menú Insertar.
Figura 4. Cinta de opciones.
2.7. CUADRO DE NOMBRES
Esta opción se la utiliza para editar los nombres de los rangos y se visualiza la
celda y columna cuando está activa.
Figura 5. Cuadro de nombres.
2.8. INSERTAR FUNCIÓN (FX)
Al hacer clic en fx, se visualiza los argumentos de la función o listado de las
categorías de las funciones, indicando como se debe ingresar los valores, además
se activa la ayuda de cada función.
Figura 6. Insertar función.
2.9. CELDA
10
Es la intersección de una fila y una columna, al hacer clic en una celda esta se
convierte en una celda activa que se visualiza en el cuadro de nombres. Ejemplo
B3 es una celda.
2.10. ETIQUETAS
Son las hojas del libro de trabajo de Excel, un libro de Excel puede tener varias
etiquetas o hojas, hacer dos clic en la etiqueta para cambiar el nombre, clic
derecho en la etiqueta se visualiza otras opciones, para insertar más etiquetas
hacer clic en el ícono después de hoja3, una etiqueta u hoja de Excel a partir de la
versión 2010 tiene 1´048.576 filas.
Figura 7. Etiquetas.
2.11. BARRA DE ESTADO
Se encuentra en la parte inferior de hoja de Excel en ella se visualiza varios
íconos el más importante es el del cálculo automático, ejemplo si queremos
realizar el cálculo del promedio, suma, máximo, mínimo etc. Solo tenemos que
sombrear los valores y el resultado se visualiza en la barra de estado.
Figura 8. Barra de estado.
11
2.12. BARRA DE FÓRMULA
Es una herramienta que tiene que estar siempre activada para poder realizar el
cálculo de cualquier fórmula, para activar y desactivar la barra de fórmulas,
cuadriculas y encabezados de columnas, hacer clic en vista.
Figura 9. Barra fórmula
Una hoja de Excel u etiqueta tiene 16.384 columnas, para visualizar las últimas
filas y columnas, presione la tecla control y flechas del cursor hacia abajo o
derecha, si quiere activar y desactivar los encabezados de columnas (letras a
números) haga un clic derecho en cualquier ícono o cinta y seleccione
personalizar la cinta de opciones y haga clic en fórmulas, desactive o active el
casillero estilo de referencia y aceptar.
2.13. RANGO
Es el conjunto de una o más celdas, se las puede identificar por las referencias
sean estas relativas o absolutas para seleccionar un rango lo podemos hacer con el
mouse o presionando la tecla F8 y luego hacer clic. Ejemplo de rango con
referencias relativas B10:C20,rango con referencias absolutas $B10$:$C$20, más
adelante explicaremos la importancia de las referencias en las fórmulas de Excel.
2.14. BARRA DE DESPLAZAMIENTO VERTICAL
Nos permite desplazarnos de manera rápida por las filas sin dejar de lado el mouse
y las teclas Avance y retroceso de página.
12
2.15. BARRA DESPLAZAMIENTO HORIZONTAL
Esta barra nos permite desplazarnos por las columnas sin dejar de lado el mouse y
las teclas del cursor izquierda y derecha.
2.16. LIBRO TRABAJO DE EXCEL
Cuando creamos un documento en Excel y lo guardamos, este documento se
guarda con la extensión xlsa partir de la versión 2010, está compuesto por varias
hojas de cálculo llamadas etiquetas que se encuentran ubicadas en la parte inferior
izquierda.
2.17. FORMAS DE GUARDAR UN DOCUMENTO O LIBRO DE EXCEL
Haciendo Clic con el mouse en el icono guardar que se encuentra ubicado en la
parte superior izquierdo o presionando las teclas abreviadas control + g, en el
menú archivo se encuentran las opciones de guardar y guardar como.
2.18. FORMAS DE PROTEGER UN DOCUMENTO O LIBRO DE EXCEL
En el menú revisar se encuentran las etiquetas o íconos que nos permite
introducir la clave para proteger una hoja(etiqueta) en forma de lectura o
todo el libro
Si queremos proteger el libro u hoja en forma de apertura, hacer clic en
archivo guardar como, organizar opciones de carpeta y búsqueda.
13
3. FORMATOS DE EXCEL
En un libro u hoja de Excel existen diferentes tipos de formatos entre estos
podemos mencionar.
3.1. FORMATOS DE FILAS, COLUMNAS Y ETIQUETAS
Para acceder a la opción formatos hacer clic con el mouse en el menú inicio,
formatos, al desplegarse el cuadro de dialogo se encuentran los formatos de
ancho y alto de fila y columna, ocultar y mostrar filas y columnas, ocultar hojas,
color de etiquetas etc.
Figura 10. Cuadro de diálogo de formatos.
3.2. FORMATO CONDICIONAL, TABLAS Y ESTILOS DE CELDAS
14
En los formatos de estilos podemos encontrar los formatos de tablas, formatos
condicional y de celdas.
3.2.1. Formatos de tablas
Cuando editamos una tabla con valores numéricos u texto, antes denominada lista
en Excel en el 2010, la misma puede tener bordes o no, sus datos se los puede
administrar y analizar independientemente, la información que haya dentro de la
tabla. Por ejemplo se puede filtrar las columnas de la tabla, agregando filas
totales para sumatorias, realizar cálculo estadístico, matemático, en el siguiente
ejemplo aplicaremos el cálculo de la varianza, promedio, desviación estándar,
máximo, mínimo, logaritmo, tangente, potencia, radianes. Además se puede
añadir las funciones con otros operadores matemáticos etc.
PRODUCCIÒN AVÌCOLA
Figura 12. Cálculo fórmulas, funciones y filtros.
15
Como se puede apreciar en la parte superior de los encabezados de cada columna
de la tabla se han creado unos íconos cuya función es filtrar la información de la
tabla, de igual manera en la parte inferior, los íconos despliegan un cuadro de
diálogo que contienen varias funciones que al hacer clic con el mouse en ellas se
realiza automáticamente el cálculo.
3.2.2. Pasos a seguir para este ejercicio
Editar los datos de la tabla sin bordes caso contario no se habilita la opción.
Hacer clic con el mouse en cualquier celda de la tabla de datos.
Hacer clic con el mouse en el menú insertar tabla y se habilita un
cuadro de diálogo y proceda a seleccionar los datos de la tabla.
Figura 13. Crear Tabla
Se recomienda que los datos no deben estar en tablas, porque las opciones
se deshabilitan.
Habilite esta opción si los
datos tienen
encabezados
16
Finalmente haga clic con el mouse lado derecho en cualquier sector de la
tabla y selecciones las opciones tabla, fila de totales.
Figura 14. Cuadro de diálogo para crear tabla.
A partir de ese momento se puede realizar cualquier cálculo con las diferentes
funciones u fórmulas en cada columna.
3.2.3. Formato condicional
El formato condicional nos permite visualizar de forma rápida datos a través de la
barras de datos, escalas de colores y conjuntos de íconos, el formato condicional
se puede aplicar a una tabla o celda, ejemplo.
¿Cómo ha sido el comportamiento de las ventas de los últimos años?
¿Quién ha vendido más de 20.000 dólares al mes?
¿Cuáles son los estudiantes que tienen mejores notas y cuáles y cuáles son los
que tienen menor rendimiento? Excel trae un nuevo conjunto de íconos en
17
diferentes categorías, para ingresar a estas se hace clic en inicio, formato
condicional.
Figura 15. Formato condicional varias categorías
3.2.4. Ejercicios de formato condicional de barra de datos
Se procede a sombrear los valores de la columna ventas y seleccionamos la
categoría barra de datos y aplicamos la barra de datos roja, en la columna ventas
se puede evidenciar cual es la mayor y menor venta.
Figura 16.Formato condicional de barras de datos.
18
3.2.5. Ejercicio de formato condicional de conjunto de íconos
De igual manera se procede a sombrear los valores de la columna ventas y
seleccionamos la categoría conjunto de íconos y aplicamos cualquier opción.
3.2.6. Estilos de celda
Para ingresar a esta opción hacemos clic en inicio estilos de celda,
seleccionamos la celda o rangos y aplicamos el estilo.
Figura 18. Estilos de celdas
3.2.7. Ejercicio de estilos de celdas
Procedemos editar los datos de las columnas ingresos y egresos y aplicamos dos
estilos de celdas o rangos, en la siguiente figura se aprecia la aplicación de tres
estilos de celdas.
Figura 19. Aplicación de estilos de celda.
Ingresos Egresos
25 78
45 95
78 26
59 46
73 97
85 85
Productos Ventas
Caco $ 25
Soya 32
Manì 42
Tomate 26
Café 26
Frejol 42
Figura 17.Formato Condicional de formas
19
3.3. FORMATO DE CELDAS
Existe una gran cantidad de formas, para desplegar la ficha formato de celdas de
Excel, se puedes seleccionar el grupo de celdas al que deseas aplicar un formato
específico, debes seleccionar el botón derecho del ratón y dentro de las opciones
que se muestran seleccionas formato de celdas.
Figura 20. Formatos celdas
3.3.1. Categorías
En Formato de celdas existen varias categorías, número, alineación, fuente,
bordes, rellenos y proteger, la utilización de estas categorías basta con
Seleccionar la aplicación inmediata.
Figura 21. Formatos – categorías
20
3.3.2. En la categoría número tenemos varias pestañas
General, las celdas con formato general no tienen un formato específico de
número.
Número, para dar formatos a valores monetarios utilice los formatos de
moneda dólar y contabilidad.
Fecha, cuando se quiere escribir valores como texto se utiliza este formato.
Hora, los formatos de hora contienen números que representan fechas y
hora. Excepto en los valores que tengan un.
Porcentaje, los formatos de porcentajes multiplican el valor de la celda por
100 y muestran el resultado con un símbolo porcentual.
Fracción, Aquí se podrá elegir con cuantos dígitos quieres escribir en las
celdas.
Ejemplo, un digito (3/8), dos dígitos (23/12), tres dígitos (234/123) y cuatro
décimas (4/10).
Texto, cuando se activa este formato, los valores que se encuentran en las
celdas están escritos como texto, lado derecho.
Especial, este formato nos permite escribir números de teléfonos, códigos
postales o el número del Seguro Social.
21
Figura 22. Ejemplos de formatos
En la primera columna los valores están escritos normalmente y en la segunda
columna los valores tienen formato de texto, porque están escritos en el lado
izquierdo de la celda. Las siguientes columnas tienen formatos según el diagrama.
3.3.3. Borrar formatos
Para borrar cualquier formato, hacer clic en inicio y en el ícono borrar de va
desplegar varias opciones de borrar los formatos.
Figura 23. Borrar formatos
22
4. FÓRMULAS
Las fórmulas en Excel son expresiones que se utilizan para realizar cálculos o
procesamiento de valores, produciendo un nuevo valor que será asignado a la
celda en la cual se introduce la fórmula, en una fórmula por lo general intervienen
valores que se encuentran en una o más celdas de un libro de trabajo1.
4.1. PARTES DE UNA FÓRMULA.
Para insertar una fórmula en Excel se escribe en la celda o barra de fórmulas el
signo igual (=) o más (+). Una formula consta de cuatro partes.
Figura 24. Partes de una fórmula
Funciones:La función Raíz Cuadrada (rcuad) devuelve el resultado 3
Referencias: B15 devuelve el valor de la celda B15
Operadores: Los operadores matemáticos + y ^ (signo más y acento
circunflejo ), el primeo suma la referencia y el segundo eleva la referencia a
una potencia (3)
Constantes: Número o valor es de texto escrito directamente en una
fórmula.
Las fórmulas se las pueden combinar con operadores matemáticos y funciones,
ejemplo.
1Ramírez, Israel J. Notas de Clases de Microsoft Excel. Universidad de Los Andes, FACES.
Mérida, Venezuela. 2007
=Rcuad(9) + B15 ^ 3
1 2 3
4
23
=Promedio (B4:B10)/Suma (A2:A5)*4-Producto (C2:C5)+500^2-6000+Var
(F4:B10) ……etc.
4.2. OPERADORES
Son las herramientas que le permiten a Excel saber qué tipo de operación
queremos realizar o especificar el tipo de cálculo que se desea ejecutar en los
elementos de una fórmula, existe un orden predeterminado en el que tiene lugar
los cálculos pero estos pueden variar con el uso de paréntesis2.
4.3. OPERADORES DE CÁLCULO EN LAS FÓRMULAS.
Los operadores nos indican el tipo de cálculo que desea procesar y son los
elementos que contiene una fórmula. Existe un orden predeterminado que ejecuta
Excel los cálculos de una fórmula, pero puede cambiar este orden utilizando
paréntesis.
Orden que procesa Excel las fórmulas.
Se procesan las expresiones que se encuentran entre paréntesis.
Multiplicación y división se ejecutan antes que la suma y la resta.
Los operadores consecutivos con el mismo nivel de precedencia se calculan
de izquierda a derecha.
Realizar el cálculo, introduciendo los mismos valores y operadores pero los
paréntesis se ubican en diferente lugar.
2Dorian, Espinoza. Facultad de Ciencias Económicas
24
Fórmula Total
=4+7*10/12-5
34.17
=(4+7)*10/(12-5) 15.71
=4+(7*10)/12-5 4.83
=(4+7*10)/12-5 1.17
=4+(7*10/(12-5)) 14.00
Figura Cuadro 1. Fórmulas con varios operadores aritméticos.
4.3.1. Tipos de operadores
Para realizar el cálculo de operaciones matemáticas básicas como suma, resta,
multiplicación o división en Excel se utilizan cuatro tipos de operadores de
cálculo:
Aritmético.
Comparación.
Concatenación de texto.
Referencia.
4.3.2. Operadores aritméticos.
Operadores Aritméticos Ejemplo Significado
+ Signo más 5+2 Suma
- Signo menos 7-4 Resta
-1 Negación
* Signo asterisco 8*6 Multiplicación
/ Barra oblicua 9/3 División
% Signo porcentaje 13% Porcentaje
^ Signo circunflejo 5^2 Exponenciación
Cuadro 2. Descripción de los operadores aritméticos.
25
4.3.3. Operadores de comparación
Con los operadores de comparación se puede comparar dos valores, su resultado
puede ser un valor lógico, verdadero o falso.
Operador comparación
Números
celdas
Ejemplo
celdas
Significado
>Signo mayor que 15>10 B2>C4 Mayor que
= Signo igual 16= B4 C4=F8 Igual a
<Signo menor que D4<20 A8<C10 Menor que
>= Signo mayor o igual q 16>= C20 A1>=F1 Mayor o igual que
<= Signo menor o igual q C4<=100 A1<=D1 Menor o igual que.
Cuadro 3. Descripción de operadores de comparación
4.3.4. Operadores de referencia
Realizar cálculos en Excel combinando rangos de celdas
Operadores de
referencia Detalle Significado
: Dos puntos Operador de rango, que genera una
referencia a todas las celdas entre
dos referencias.
D4:D13
; Punto y coma Operador de unión, que combina
varias referencias en una sola.
Promedio(C2:C
8;F4:F16)
Espacio Operador de intersección que
genera una referencia a las celdas
comunes a dos referencias
Cuadro 4. Descripción de operadores de referencia
26
4.4. REFERENCIA DE CELDAS
Las celdas de una hoja de cálculo se identifican con una letra relativa a la
columna, y un número referente a la fila en que se halla la celda.
Esta intercesión de fila y columna se utilizan en una fórmula o una función, se las
llama referencia de celda, ejemplo. Cuando realizamos un cálculo =B2*C20, se
hace referencia las celdas involucradas.
El hecho de que la fórmula de una celda pueda copiarse a otras obliga a distinguir
entre referencias de celdas relativas y absolutas:
4.4.1. Referencia de celda relativa
Las referencias relativas se denominan así porque la referencia se basa en
posiciones, desde la celda que contiene el cálculo hasta las celdas involucradas.
Figura 25. Descripción de operadores de referencia
27
4.4.2. Referencias relativa absoluta.
Una referencia de celda absoluta en una fórmula va siempre acompañada del
signo $ la misma que hace referencia a una celda en una ubicación específica,
cuando se quiere copiar la referencia absoluta o cambiar de posición a otra celda
esta permanece estática.
Para convertir una referencia relativa a referencia absoluta se escribe la referencia
relativa en la barra de fórmula o celda y se presiona la tecla F4, ejemplo. =C15 y
se presiona F4, quedando así. $C$15 o también = B2* $C$7, dependiendo el
cálculo que va realizar, en el siguiente ejemplo se procede a sumar las referencias
relativas y absolutas con los mismos valores cuyo resultado es cincuenta para
ambas referencia, luego se procede a copiar los resultados, quedando 70 para la
referencia relativa y 50 para la referencia absoluta.
Figura 26. Descripción de operadores de referencia
28
4.5. ¿CÓMO TRABAJAR CON PORCENTAJES EN EXCEL?
En Excel existes varias maneras para extraer un porciento a ciertos valores, con
los valores de la siguiente tabla calcule el 12%.
Figura 27.Cálculo de porcentajes.
En cada columna de la figura 27 se utilizó diferentes fórmulas, siendo la fórmula
más aconsejable de la segunda columna por la utilización de referencias relativas
y absolutas, la misma que al escribir la fórmula se la puede arrastrar y si quiere
cambiar el porcentaje solo tiene que escribir en la celda B12 el valor y de manera
inmediata se recalculan las celdas.
A continuación otra manera de realizar el cálculo de porcentaje en Excel es
utilizando asterisco y punto (*.),lo que está en paréntesis no se escribe, es solo un
comentario.
=A3*.12 (calcula el 12%)
=A4*.08 (calcula el 8%)
=A5*.35 (calcula el 35%)
Las siguientes fórmulas sacan el porcentaje de las referencias relativas (celdas) y
a la vez lo suma, ejemplo si en la celda B8 hay un valor de 100 y se quiere
sumarle el 10%, la fórmula es = B8*1.10 y esto es igual a 110. Debemos tomar
29
encuentra que en esta fórmulas se utilizan el signo asterisco, el uno y el punto.
(*1.).
=A3*1.10 (suma el 10%)
=A4*1.05 (suma el 5%)
=A5*3.40 (suma el 40%).
Cuando el porcentaje a calcular es menor a diez se debe multiplicar así.
A3*1.03 (suma el 3%) y A3*1.3 (suma el 30%), del 1 al nueve por ciento se pone
el cero a la izquierda.
De igual manera si se quiere sacar el porcentaje a una cantidad y a la vez restarle
el porcentaje se utilizan la siguiente fórmula, no olvidar utilizar asterisco y punto
(*.).
=A3*.80 (resta el 20%)
=A4*.95 (resta el 5%)
=A5*.65 (resta el 35%)
4.6. UTILIZAR NOMBRES EN LAS FÓRMULAS
Son palabras o cadena de caracteres que representa una celda, rango de celdas,
fórmula o valor constante, utilice nombres fáciles de entender, como productos
para referirse a rangos.
Un nombre es una forma abreviada de referirse a una referencia de celda o rango u
una constante, una fórmula o una tabla cuyo propósito a primera vista podría
resultar difícil de comprender, a continuación se muestran
algunos ejemplos de nombres y el modo en que pueden mejorar la claridad
30
y facilitar la comprensión de las fórmulas.3
4.6.1. Pasos para dar nombre a los rangos y celdas de una tabla de datos
Primeramente seleccionamos el rangos de celdas, luego hacer clic en el
cuadro de nombres de la barra de fórmula y escribir el nombre que se le va a
dar al rango y aceptar, ese proceso de repite las veces que sean necesarias.
Figura 28: Fórmulas con nombres.
Para insertar una fórmula con nombre presionamos la tecla F3 y
seleccionamos el nombre del rango o celda.
Figura 29: Pegar nombres a las fórmulas presionando la tecla F3.
3 Microsoft Excel 2010, ayuda
31
Para eliminar algún nombre de una fórmula hacer clic en el menú fórmulas
y en el ícono administrador de nombres.
Figura 30: Administrador de los nombres de las fórmulas.
Si se quiere realizar la sumatoria de una columna utilizando nombre en la
fórmula,= SUMA (banano).
Fórmulas añadidas, son lasque se les puede añadir nombres, funciones,
operadores aritméticos, referencias relativas y absolutas. etc.
=Suma(banano)/100+Promedio(tomate)+Rcuad(B2:C2)-Producto(Soya)+
10^2* Suma(C10:D20).. etc.
4.7. FÓRMULAS MATRICIALES.
Las Fórmulas Matriciales son rangos matriciales o bloques de celda que
comparten una fórmula en forma de matriz, que operan sobre dos conjuntos de
datos denominados argumentos matriciales, para devolver un resultado simple o
múltiple. El cálculo de estas matriz es muy complejo, la misma que permiten
acortar el proceso del cálculo de una fórmula, siendo estas un conjunto de datos
32
organizados en columnas y filas que devuelven varios valores como resultado a la
vez, es decir estas fórmulas operan en forma de rangos en Excel, ejemplos.
=Suma(Promedio(B10:B20; E10:E20), esta fórmula nos dará como
resultado la sumatoria y multiplicación de dos rangos.
= A2:E2+A3:E3, en este caso se está sumando dos rangos, el cálculo de las
fórmulas matriciales puede ser horizontal o vertical.
4.7.1. Pasos para realizar el cálculo de una fórmula Matricial
Primeramente se debe sombrear el lugar donde se va insertar el resultado
(rango o celda).
Proceder a escribir la fórmula en la barra de fórmula y presionar las teclas
Control + Shit – Enter, estas teclas permiten realizar el cálculo.
Cuando el procedimiento se ha realizado de manera correcta la fórmula
matricial queda,={Suma(Promedio(B10:B20;E10:E20))}, la fórmula queda
entre dos corchetes.
En algunos casos cuando el procedimiento no es el correcto sale un mensaje
¡valor!, vuelva a repetir el procedimiento, con los datos de la siguiente tabla,
proceda a realizar la multiplicación Matricial.
Figura 31: Ejercicio con fórmula matricial
33
Primero se procede a sombrear el rango C4:C9, y luego a editar la fórmula
Matricial, = A4:A9*B4:B9, y presionamos las teclas Control + Shift y
Enter.
Con los datos de la misma tabla queremos saber la edad promedio de las
personas, en B11 insertaremos el resultado e editamos la fórmula
Matricial.
=Suma(A4:A9*C4:C9)/Suma(C4:C9)),presionamos las teclas Control +
Shift y Enter, dando un resultado de 32 años de promedio de las personas.
34
5. FUNCIONES EN EXCEL
Las funciones en Excel es una herramienta poderosa incluida en Excel conocidas
como fórmulas predeterminadas que nos permiten realizar cálculos simples y
avanzados, las misma que al ser ejecutadas nos devuelven un resultado, las
funciones están categorizadas en matemáticas, estadísticas, financieras, lógicas,
texto, fechas, base de datos etc.
Estas funciones nos acortan el proceso de cálculo que en algunas fórmulas serian
imposible realizarlas, a partir de Excel versión 2010 algunas funciones han sido
cambiadas el nombre o suprimidas.
Toda función tiene su Sintaxis y argumento, ejemplo la sintaxis y argumentos de
la función =Promedio ( ).
Sintaxis = Promedio (nùmero1; nùmero2; ……).
Los argumentos pueden ser números o nombres, rangos o referencias de celdas
que contengan números. Si desea incluir valores lógicos y representaciones
textuales de números en una referencia como parte del cálculo, utilice la función
promedio.
Si desea calcular el promedio de valores que cumplan ciertos criterios, use la
función Promedio.Si o la función Promedio.Si.Conjunto.
Debemos tener en cuenta que la función promedio calcula la media aritmética y
se calcula sumando un grupo de números y dividiendo o utilizando la función
promedio, no confundir la función promedio con la función mediana que es el
número intermedio de un grupo de números, es decir, la mitad de los números son
35
superiores a la mediana y la mitad de los números tienen valores menores que la
mediana.
Figura 32: Cálculo realizado con funciones y fórmulas.
Para entender como es una función, ver un sencillo ejemplo de la figura 32 con
una de las funciones más utilizadas, la función promedio, en el rango A6:A11
tenemos una serie de números y queremos calcular el promedio, se podría hacer
celda por celda como se muestra en la columna B.
El resultado tanto para la columna A y B, pero vemos que es más cómodo usar la
función promedio que nos permite seleccionar todo el rango A6:A11 en vez de
tener que calcular el promedio celda + celda (valor) y dividirlo para 6.
36
5.1. FUNCIONES MATEMÁTICAS TRIGONOMÉTRICAS4
FUNCIONES DESCRIPCIÒN
ABS Devuelve el valor absoluto de un número
ALEATORIO Devuelve un número aleatorio entre 0 y 1
ALEATORIO.ENTRE Devuelve un número aleatorio entre los números
que especifique
COCIENTE Devuelve la parte entera de una división
COS Devuelve el coseno de un número
ENTERO Redondea un número hacia abajo hasta el entero
más próximo
EXP Devuelve e elevado a la potencia de un número
dado
GRADOS Convierte radianes en grados
LN Devuelve el logaritmo natural (neperiano) de un
número
LOG Devuelve el logaritmo de un número en una base
especificada
LOG10 Devuelve el logaritmo en base 10 de un número
NUMERO.ROMANO Convierte un número arábigo en número
romano, con formato de texto
PI Devuelve el valor de pi
POTENCIA Devuelve el resultado de elevar un número a una
potencia
PRODUCTO Multiplica sus argumentos
RADIANES Convierte grados en radianes
4 Microsoft Excel 2010, Funciones Matemáticas Trigonométricas
37
RAIZ Devuelve la raíz cuadrada positiva de un número
RAIZ2PI Devuelve la raíz cuadrada de un número
multiplicado por PI (número * pi)
REDONDEA.PAR Redondea un número hasta el entero par más
próximo
REDONDEAR Redondea un número al número de decimales
especificado
REDONDEAR.MAS Redondea un número hacia arriba, en dirección
contraria a cero
REDONDEAR.MENOS Redondea un número hacia abajo, en dirección
hacia cero
SENO Devuelve el seno de un ángulo determinado
SUMA Suma sus argumentos
SUMA.CUADRADOS Devuelve la suma de los cuadrados de los
argumentos
SUMAPRODUCTO Devuelve la suma de los productos de los
correspondientes componentes de matriz
SUMAR.SI Suma las celdas especificadas que cumplen unos
criterios determinados
SUMAR.SI.CONJUNTO Suma las celdas de un rango que cumplen varios
criterios
SUMAX2MASY2 Devuelve la suma de la suma de los cuadrados
de los valores correspondientes de dos matrices
SUMAX2MENOSY2
Devuelve la suma de la diferencia de los
cuadrados de los valores correspondientes de
dos matrices
SUMAXMENOSY2 Devuelve la suma de los cuadrados de las
diferencias de los valores correspondientes de
38
dos matrices
TAN Devuelve la tangente de un número
DECIMAL Da formato a un número como texto con un
número fijo de decimales
CONCATENAR Concatena varios elementos de texto en uno solo
TRUNCAR Elimina los decimales de un númeroi
5.2. FUNCIONES MATEMÁTICAS ESTADÍSTICAS5
FUNCIONES DESCRIPCIÒN
COEF.DE.CORREL Devuelve el coeficiente de correlación entre
dos conjuntos de datos
COEFICIENTE.ASIMET
RIA
Devuelve la asimetría de una distribución
COEFICIENTE.R2 Devuelve el cuadrado del coeficiente de
momento de correlación de producto Pearson
CONTAR Cuenta cuántos números hay en la lista de
argumentos
CONTAR.BLANCO Cuenta el número de celdas en blanco de
un rango
CONTAR.SI.CONJUNT
O
Cuenta el número de celdas, dentro del
rango, que cumplen varios criterios
CONTARA Cuenta cuántos valores hay en la lista de
argumentos
5 Microsoft Excel 2010, Funciones Matemáticas Estadísticas
39
COVARIANCE.P
Devuelve la covarianza, que es el promedio
de los productos de las desviaciones para
cada pareja de puntos de datos
COVARIANZA.M
Devuelve la covarianza de ejemplo, que es
el promedio de los productos de las
desviaciones para cada pareja de puntos de
datos en dos conjuntos de datos
CRECIMIENTO Devuelve valores en una tendencia
exponencial
CUARTIL.EXC
Devuelve el cuartil del conjunto de datos,
basado en los valores percentiles de 0..1,
exclusivo
CUARTIL.INC Devuelve el cuartil de un conjunto de datos
CURTOSIS Devuelve la curtosis de un conjunto de
datos
DESVEST.M Calcula la desviación estándar a partir de
una muestra
DESVEST.P Calcula la desviación estándar en función
de toda la población
DESVESTA
Calcula la desviación estándar a partir de
una muestra, incluidos números, texto y
valores lógicos
DESVESTPA Calcula la desviación estándar en función
40
de toda la población, incluidos números,
texto y valores lógicos
DESVIA2 Devuelve la suma de los cuadrados de las
desviaciones
DESVPROM
Devuelve el promedio de las desviaciones
absolutas de la media de los puntos de
datos
DISTR.BINOM
Devuelve la probabilidad de una variable
aleatoria discreta siguiendo una
distribución binomial
DISTR.CHICUAD.CD
Devuelve la probabilidad de una variable
aleatoria continua siguiendo una
distribución chi cuadrado de una sola cola
DISTR.EXP.N Devuelve la distribución exponencial
DISTR.F Devuelve la distribución de probabilidad F
DISTR.F.CD Devuelve la distribución de probabilidad F
DISTR.T
Devuelve los puntos porcentuales
(probabilidad) de la distribución t de
Student
DISTR.T.2C
Devuelve los puntos porcentuales
(probabilidad) de la distribución t de
Student
DISTR.T.CD Devuelve la distribución de t de Student
41
ERROR.TIPICO.XY Devuelve el error estándar del valor de "y"
previsto para cada "x" de la regresión
ESTIMACION.LINEAL Devuelve los parámetros de una tendencia
lineal
ESTIMACION.LOGAR
ITMICA
Devuelve los parámetros de una tendencia
exponencial
FISHER Devuelve la transformación Fisher
FRECUENCIA Devuelve una distribución de frecuencia
como una matriz vertical
GAMMA.LN Devuelve el logaritmo natural de la función
gamma, Γ(x)
GAMMA.LN.EXACTO Devuelve el logaritmo natural de la función
gamma, Γ(x)
INTERSECCION.EJE Devuelve la intersección de la línea de
regresión lineal
INTERVALO.CONFIA
NZA.NORM
Devuelve el intervalo de confianza de la
media de una población
INTERVALO.CONFIA
NZA.T
Devuelve el intervalo de confianza para la
media de una población, usando una
distribución t de Student
INV.BINOM
Devuelve el menor valor cuya distribución
binomial acumulativa es menor o igual a
un valor de criterio
42
INV.CHICUAD Devuelve la función de densidad de
probabilidad beta acumulativa
INV.CHICUAD.CD
Devuelve la función inversa de la
probabilidad de una variable aleatoria
continua siguiendo una distribución chi
cuadrado de una sola cola
INV.T
Devuelve el valor t de la distribución t de
Student en función de la probabilidad y los
grados de libertad
INV.T.2C Devuelve la función inversa de la
distribución de t de Student
K.ESIMO.MAYOR Devuelve el k-ésimo mayor valor de un
conjunto de datos
K.ESIMO.MENOR Devuelve el k-ésimo menor valor de un
conjunto de datos
MAX Devuelve el mayor valor de una lista de
argumentos
MAXA
Devuelve el valor máximo de una lista de
argumentos, incluidos números, texto y
valores lógicos
MEDIA.ACOTADA Devuelve la media del interior de un
conjunto de datos
MEDIA.ARMO Devuelve la media armónica
43
MEDIA.GEOM Devuelve la media geométrica
MEDIANA Devuelve la mediana de los números dados
MIN Devuelve el valor mínimo de una lista de
argumentos
MINA
Devuelve el valor mínimo de una lista de
argumentos, incluidos números, texto y
valores lógicos
MODA.UNO Devuelve el valor más común de un
conjunto de datos
MODA.VARIOS
Devuelve una matriz vertical de los valores
que se repiten con más frecuencia en una
matriz o rango de datos
PEARSON Devuelve el coeficiente de momento de
correlación de producto Pearson
PENDIENTE Devuelve la pendiente de la línea de
regresión lineal
PERCENTIL.EXC
Devuelve el k-ésimo percentil de los
valores de un rango, donde k está en el
rango 0..1, exclusivo
PERCENTIL.INC Devuelve el k-ésimo percentil de los
valores de un rango
PROBABILIDAD Devuelve la probabilidad de que los
valores de un rango se encuentren entre dos
44
límites
PROMEDIO Devuelve el promedio de sus argumentos
PROMEDIO.SI
Devuelve el promedio (media aritmética)
de todas las celdas de un rango que
cumplen unos criterios determinados
PROMEDIO.SI.CONJU
NTO
Devuelve el promedio (media aritmética)
de todas las celdas que cumplen múltiples
criterios
PROMEDIOA Devuelve el promedio de sus argumentos,
incluidos números, texto y valores lógicos
PRONOSTICO Devuelve un valor en una tendencia lineal
PRUEBA.CHICUAD Devuelve la prueba de independencia
PRUEBA.F Devuelve el resultado de una prueba F
PRUEBA.FISHER.INV Devuelve la función inversa de la
transformación Fisher
PRUEBA.T Devuelve la probabilidad asociada a una
prueba t de Student
PRUEBA.Z Devuelve el valor de una probabilidad de
una cola de una prueba z
RANGO.PERCENTIL.E
XC
Devuelve el rango de un valor en un
conjunto de datos como un porcentaje
(0..1, exclusivo) del conjunto de datos
45
RANGO.PERCENTIL.I
NC
Devuelve el rango porcentual de un valor
de un conjunto de datos
SUMAR.SI
Cuenta el número de celdas, dentro del
rango, que cumplen el criterio
especificado.
TENDENCIA Devuelve valores en una tendencia lineal
VAR.P Calcula la varianza en función de toda la
población
VAR.S Calcula la varianza de una muestra
VARA Calcula la varianza a partir de una muestra,
incluidos números, texto y valores lógicos
VARPA
Calcula la varianza en función de toda la
población, incluidos números, texto y
valores lógicosii
5.3. FUNCIONES MATEMÁTICAS FINANCIERAS6
AMORTIZ.LIN Devuelve la amortización de cada uno de
los períodos contables
AMORTIZ.PROGRE
Devuelve la amortización de cada período
contable mediante el uso de un coeficiente
de amortización
6 Microsoft Excel 2010, Funciones Matemáticas Financieras
46
CANTIDAD.RECIBIDA
Devuelve la cantidad recibida al
vencimiento de un valor bursátil
completamente invertido
DB
Devuelve la amortización de un bien
durante un período específico a través del
método de amortización de saldo fijo
DDB
Devuelve la amortización de un bien
durante un período específico a través del
método de amortización por doble
disminución de saldo u otro método que se
especifique
DURACION Devuelve la duración anual de un valor
bursátil con pagos de interés periódico
INT.ACUM Devuelve el interés acumulado de un valor
bursátil con pagos de interés periódicos
INT.ACUM.V Devuelve el interés acumulado de un valor
bursátil con pagos de interés al vencimiento
INT.EFECTIVO Devuelve la tasa de interés anual efectiva
INT.PAGO.DIR Calcula el interés pagado durante un
período específico de una inversión
NPER Devuelve el número de períodos de una
inversión
PAGO Devuelve el pago periódico de una
47
anualidad
PAGO.INT.ENTRE Devuelve el interés acumulado pagado
entre dos períodos
PAGO.PRINC.ENTRE Devuelve el capital acumulado pagado de
un préstamo entre dos períodos
PAGOINT Devuelve el pago de intereses de una
inversión durante un período determinado
PAGOPRIN Devuelve el pago de capital de una
inversión durante un período determinado
PRECIO
Devuelve el precio por un valor nominal de
100 $ de un valor bursátil que paga una tasa
de interés periódico
PRECIO.DESCUENTO Devuelve el precio por un valor nominal de
100 $ de un valor bursátil con descuento
RENDTO Devuelve el rendimiento de un valor
bursátil que paga intereses periódicos
RENDTO.VENCTO Devuelve el rendimiento anual de un valor
bursátil que paga intereses al vencimiento
SLN Devuelve la amortización por método
directo de un bien en un período dado
SYD
Devuelve la amortización por suma de
dígitos de los años de un bien durante un
período especificado
48
TASA Devuelve la tasa de interés por período de
una anualidad
TASA.DESC
Devuelve la tasa de descuento de un valor
bursátil
TASA.INT
Devuelve la tasa de interés para la inversión
total de un valor bursátil
TASA.NOMINAL Devuelve la tasa nominal de interés anual
TIR
Devuelve la tasa interna de retorno para una
serie de flujos de efectivo periódicos
TIR.NO.PER
Devuelve la tasa interna de retorno para un
flujo de efectivo que no es necesariamente
periódico
TIRM
Devuelve la tasa interna de retorno donde
se financian flujos de efectivo positivos y
negativos a tasas diferentes
VA Devuelve el valor actual de una inversión
VF Devuelve el valor futuro de una inversión
VF.PLAN Devuelve el valor futuro de un capital
inicial después de aplicar una serie de tasas
de interés compuesto
49
5.4. FUNCIONES LÓGICAS7
FUNCIONES DESCRIPCIÒN
FALSO Devuelve el valor lógico FALSO
NO Invierte el valor lógico del argumento
O Devuelve VERDADERO si cualquier argumento es
VERDADERO
SI Especifica una prueba lógica que realizar
Ejercicio con fórmulas, funciones matemáticas y estadísticas
Figura 32: Aplicación de funciones, porcentajes y fórmulas
7 Microsoft Excel 2010, Funciones Matemáticas Lógicas
50
Figura 33: Aplicar rastrear precedentes (flechas azules)
En el ejemplo de la tabla 33 el porcentaje del 13 % para cada mes, se calcula
utilizando la fórmula = Producto(C5:D5)*$F$3, remplazando con valores la
fórmula quedaría así. =(160*6)/13%.
En la columna % de producción mensual se procede a calcular el porcentaje
mensual del total de venta, que al final del ejercicio, la sumatoria de los
porcentajes tienen que dar 100 %, la fórmula es = Producto (C5:D5)*100/$E1$3,
remplazando los valores la fórmula quedaría = (160*6)*100/6967.8.
En la columna H se calcula más el 17% de comisión de las columnas C*D, la
fórmula es =(C5:D5)*$H$4+ Producto(C5*D5), remplazando la fórmula con
valores = (160*6)*17%+(160*5). Se recomienda utilizar fórmulas con celdas, en
el caso de corregir un valor, estas se recalculan automáticamente y de igual
manera utilizar referencias absolutas en los porcentajes (signo dólar), lo mismo
que facilita arrastrar la fórmula.
51
En cuanto al cálculo de las funciones promedio, Rcuad (Raíz cuadrada), grados,
Desvest.P (desviación estándar de una población), Var (Varianza), siempre se
debe escribir primero el signo igual y después la función.
El cálculo de la fórmula = Suma(C5:C12) + Promedio(D5:D12) /200-
Sumaproducto(C5:D7)*D5/Log(D5:D8;8)+Pi()-45% /(50^2)+F5*46. Cuyo
resultado es 164743.7, se la define como una fórmula combinada que contiene
diferentes operadores matemáticos y funciones, demostrando que en Excel se
puede realizar cualquier cálculo.
52
6. ANÁLISIS DE DATOS EN EXCEL.
El comando Análisis de datos de Excel es una herramienta que nos permite
desarrollar análisis estadísticos o técnicos complejos, puede ahorrar pasos y
tiempo si utiliza las herramientas para análisis, cuando utilice una de estas
herramientas deberá proporcionar los datos y parámetros para cada análisis y la
herramienta utilizará las funciones de macros estadísticas o técnicas
correspondientes para realizar los cálculos y mostrar los resultados en una tabla de
resultados, algunas herramientas generan gráficos además de tablas de resultados.
Las herramientas para análisis incluyen las herramientas que se describen a
continuación, para tener acceso a estas herramientas, haga clic en el menú datos
análisis de datos inmediatamente se despliega un menú de funciones.
Si el comando análisis de datos no está disponible, deberá cargar el programa de
complemento de herramientas para análisis.
Figura 35. Funciones para análisis de datos
53
6.1. PASOS PARA CARGAR LA HERRAMIENTA PARA ANÁLISIS DE
DATOS
Haga clic lado derecho en cualquier pestaña e ícono de las cintas de
opciones y active la opción personalizar la cinta de opciones.
En el casillero comandos disponibles seleccione la opción todos los
comandos o (complementos y la opción ir).
Del grupo de opciones seleccione complementos, hacer clic en nuevo grupo
o ficha, finalmente hacer clic en agregar y aceptar.
Seleccionar complementos en la cinta de opciones y active las opciones
Herramientas para Análisis, Solver, y aceptar inmediatamente comienzan a
instalarse la ficha Análisis de Datos, estas dos opciones son las más
utilizadas por alumnos y profesionales.
Figura 36: Complementos de las Herramientas para Análisis y Solver
54
6.2. HERRAMIENTAS PARA ANÁLISIS ESTADÍSTICO
6.2.1. Coeficiente de correlación
El Coeficiente de correlación lineal es el cociente entre la Covarianza y el
producto de las Desviaciones típicas de ambas variables, el Coeficiente de
correlación lineal se expresa mediante la letra r.
6.2.2. Propiedades del Coeficiente de correlación
Cuando se realiza una escala de medición, el coeficiente de correlación no
varía. Es decir, si expresamos la altura en metros o en centímetros el
coeficiente de correlación es el mismo.
El signo del coeficiente de correlación lineal es el mismo que el de la
covarianza.
Si la covarianza es positiva, la correlación es directa.
Si la covarianza es negativa, la correlación es inversa.
Si la covarianza es nula, no existe correlación.
El coeficiente de correlación lineal es un número real comprendido entre −1
y 1.
Si el coeficiente de correlación lineal toma valores cercanos a −1 la
correlación es fuerte e inversa, y será tanto más fuerte cuanto más se
aproxime r a −1.
Si el coeficiente de correlación lineal toma valores cercanos a 1 la
correlación es fuerte y directa, y será tanto más fuerte cuanto más se
aproxime r a 1.
55
Si el coeficiente de correlación lineal toma valores cercanos a 0, la
correlación es débil.
Si r = 1 ó −1, los puntos de la nube están sobre la recta creciente o
decreciente.
Entre ambas variables hay dependencia funcional, ejemplo con las notas de primer
ciclo de las asignaturas de informática I y botánica calcular en Excel el
coeficiente de correlación lineal simple con fórmula y la función Coef.De.Correl
(coeficiente de correlación lineal).
COEFICIENTE DE CORRELACION LINEAL
CON FÒRMULA Y FUNCIÒN EN EXCEL
Figura 37:Coeficiente de correlación lineal con fórmula.
El resultado del ejercicio es de 0,98 con el cálculo de las fórmulas y la función
Coef.De.Correl, nos demuestra que la utilización de las funciones en Excel nos
acorta el proceso de cálculo en ciertas fórmulas
56
6.3. HERRAMIENTA DE ANÁLISIS DE LA COVARIANZA Y COVAR.
La herramienta de análisis covarianza calcula el grado de asociación lineal entre
dos variables cuantitativas, esta se encuentra en la opción análisis de datos y su
fórmula devuelve la covarianza de los promedio de los productos de las
desviaciones de puntos de las medias respectivas de dos conjuntos de datos, tomar
en cuenta en Excel tres funciones adicionales que realizan el mismo cálculo.
Función Covariancem nos permite realizar el cálculo de la covarianza de los
promedios de los productos de las desviaciones de dos conjuntos de datos.
Función Covariancep devuelve la covarianza de la población, el promedio
de los productos de las desviaciones para cada pareja de puntos de datos en
dos conjuntos de datos, por ejemplo, puede investigar si unos ingresos más
elevados se corresponden con niveles de estudios más altos
Función Covar tiene la misma relación de la función Covarriancep, es decir
Covar pertenece a las funciones antiguas de Excel. Ejercicio de la
Covarianza con dos variables utilizando la herramienta análisis de datos y
las funciones.
Como apoyo podemos utilizar la opción de ayuda de Excel.
Figura 38. Cuadro de dialogo para el cálculo de la covarianza.
57
La función covarianza es una fórmula matricial, una manera de seleccionar los
datos de la matriz es seleccionar el primer rango y tener presionado la tecla
control para seleccionar el segundo rango y aceptar, en el ejemplo se puede ver
tres maneras de calcular la covarianza con las funciones Covariancep y Covar,
con la herramienta de análisis covarianza, nos da un mismo resultado.
Figura 39. Ejercicio de la covarianza
En la figura 39 las fórmulas de la funciones covarianza es una fórmula matricial,
una manera de seleccionar los datos de la matriz es seleccionar el primer rango y
tener presionado la tecla control para seleccionar el segundo rango y aceptar.
6.4. CÁLCULO DE LA VARIANZA
Hacer clic en el menú datos, ficha análisis de datos y seleccionar la herramienta de
análisis covarianza, e ingresar los datos en rango de entrada y seleccionar el rango
de salida y aceptar.
6.5. HISTOGRAMA DE FRECUENCIA CON FÓRMULAS
Se define como histograma de frecuencia a los diagramas de barras que sirven
para ilustrar la variación que se presentan en un conjunto de datos con el fin de
investigar cómo se solucionar un problema o mejorar un proceso, en un
histograma no debe haber espacio entre sus barras, ejercicio de histograma con
fórmula con los siguientes datos
58
HISTOGRAMA CON FÓRMULAS
Figura 40. Ejercicio de histograma con fórmulas.
6.5.1. Pasos para crear histograma con fórmula.
Calcular con función el mínimo = Mín. (A3:C15) es igual a 218.
El máximo = Máx. (A3:A15) es igual 265.
Tercer paso es calcular los rango de clase, en la celda H4 va el mínimo
(218), en la celda H5 edite la siguiente fórmula=H4+($F$4-$F$3)/9 enter y
se procede arrastrar la fórmula hasta la celda H7. Indicando que el
histograma será de 4 rangos de clase.
Calcular la frecuencia, se sombrea el rango I4:I7 y se inserta la siguiente
fórmula. = Frecuencia (A3:C15; H4:H7) y se presionan las teclas Control +
Shift + enter, por ser la frecuencia una fórmula matricial.
Finalmente seleccionamos los resultados de la frecuencia y creamos un
gráfico de columnas agrupadas. Para unir las barras del gráfico hacer clic
59
con el mouse lado derecho en cualquier barra y seleccionar formato de serie
de datos y manipular el ancho del intervalo.
Figura 41. Histograma con fórmulas.
6.5.2. Pasos para crear histograma con la herramienta análisis de datos.
Se procede a hacer clic en el menú datos, ficha análisis de datos y
seleccionamos la opción histograma, abriéndose un cuadro de diálogo.
En el primer casillero de rango de entrada ingresar los datos de la tabla cuyo
rango es = $B$3:C$1$5, notándose que la información ingresa como
referencia absoluta.
Habilitar el casillero crear gráfico, en caso que se requiera un histograma
con porcentajes habilite el otro casillero.
1
20
14
4
0
5
10
15
20
25
218,00 233,67 249,33 y mayor...
Fre
cue
nci
a
Rango de Clases
Histograma con fórmulas
60
Finalmente seleccionar el lugar de salida de la información y aceptar
Figura 42.Ingreso de datos para realizar un histograma.
Una vez ingresados los datos según el procedimiento del cuadro de diálogo de las
herramientas análisis de datos e histograma obtenemos el siguiente resultado.
Figura 43. Resultado del histograma
Figura 44. Histograma con la herramienta análisis de datos.
Clase Frecuencia
218,00 1
233,67 20
249,33 14
y mayor... 4
1
20
14
4
0
5
10
15
20
25
218,00 233,67 249,33 ymayor...
Fre
cue
nci
a
Rango de Clases
Histograma
61
6.6. ESTADÍSTICA DESCRIPTIVA CON LA HERRAMIENTA ANÁLISIS
DE DATOS.
La herramienta de Análisis de Estadística Descriptiva produce un informe
estadístico por cada variable, las funciones que generan el resumen estadístico
son:
Media, Error típico, Mediana, Moda, Desviación estándar, Varianza de la
muestra, Curtosis, Coeficiente de asimetría, Rango, Mínimo, Máximo, Suma,
Cuenta, k-èsimo mayor, k–ésimo menor, Nivel de confianza (95,0%).
6.6.1. Pasos para realizar el resumen de estadística descriptiva
Hacer clic en la herramienta análisis de datos y seleccionar la opción
estadística descriptiva y abre un cuadro de diálogo.
Seleccionar los datos en el cuadro rango de entrada, en este caso $B3:C11 y
habilitar la opción rótulos en la primera fila.
Habilitar los casilleros de los siguientes argumentos, resumen de estadística,
nivel de confianza para la media del 95 %, k–èsimo mayor, k–èsimo menor
y aceptar.
Figura 45. Estadística descriptiva.
62
6.6.2. Resumen de estadística descriptiva
En la siguiente grafica se puede apreciar el resumen de estadística de dos
variables de datos, tratamiento I y tratamiento II.
Figura 46. Resumen estadístico
6.7. ANÁLISIS DE VARIANZA DE UN FACTOR.
Esta opción realiza un análisis de varianza simple con datos de dos o más
muestras, el análisis de varianza nos da una prueba de hipótesis de cada muestra
se extrae de la misma distribución subyacente de probabilidades frente a la
hipótesis alternativa de que las distribuciones subyacentes de probabilidades no
son las mismas para todas las muestras
Si se quiere saber el grado de contaminación del rio jubones se toman muestra de
cinco lugares diferentes de su nacimiento 15, 40, 90, 130 y 169 km, con los
datos de las muestras se elabora una tabla, se quiere saber si existe diferencia
significativa en el nivel medio de contaminación en los diferentes puntos que se
obtuvo las muestras.
63
6.7.1. Pasos para realizar el cálculo del análisis de varianza de un factor.
Hacer clic en menú datos y en la ficha análisis de datos y seleccionar la
opción análisis de varianza de un factor.
Figura 48. Análisis de varianza de un factor.
Figura 47. Datos para realizar el cálculo de
análisis de varianza con un factor
64
Seleccionar las muestras en el caso del ejemplo, el rango es $B$2:$F$7 y
señalar el rango de salida celda $B$9 y hacer clic en aceptar nos da el
siguiente resultado.
Figura 49. Análisis de varianza de un factor.
6.8. ANÁLISIS DE VARIANZA DE DOS FACTORES CON VARIAS
MUESTRAS POR GRUPO.
Esta herramienta de análisis es útil cuando los datos se pueden clasificar de
acuerdo con dos dimensiones diferentes, por ejemplo, en un experimento para
determinar el efecto de dos niveles de energía de harina de pescado en la
alimentación de 5 cerdos, el problema se resolvería haciendo un análisis de
varianza de dos factores con varias muestras por grupo. En este caso consta de dos
grupos con 15 muestras cada uno.
65
Figura 50. Análisis de varianza de dos factores con varias muestras por
grupo.
6.8.1. Pasos para realizar el análisis de varianza de dos factores con
varias muestras por grupo.
Hacer clic en análisis de datos, seleccionar la opción análisis de
varianza de dos factores con varias muestras por grupo.
Figura 51. Análisis de varianza de dos factores con varias muestras por grupo
66
En el casillero de rango de entrada seleccionar las muestras que se encuentra
en la gráfica anterior $G$64: $J$74, en el casillero fila por muestra editar el
5 que equivale a 5 muestra, finalmente señalar la celda donde se insertar el
resultado. $G$77 y aceptar obteniendo el siguiente resultado.
Figura 52. Resultado del análisis de varianza de dos factores
con varias muestras por grupo.
6.9. ANÁLISIS DE VARIANZA DE DOS FACTORES CON UNA SOLA
MUESTRA POR GRUPO
Esta herramienta de análisis es útil cuando los datos se clasifican en dos
dimensiones diferentes, con los datos de la siguiente tabla realizar la
experimentación de su efecto del rendimiento sobre la aplicación de 4 abonos
orgánicos, el experimento se realizara en cinco parcelas de cultivos, para analizar
los datos de este experimento se utilizará la opción análisis de varianza de dos
factores con una sola muestra por grupo, con lo cual se podrá sacar las
conclusiones adecuadas.
67
6.9.1. Pasos para realizar el análisis de varianza de dos factores con una sola
muestra por grupo
Seleccionar el menú datos, análisis de datos y escoger la opción análisis de
varianza de dos factores con una sola muestra por grupo
Figura 53. Análisis de varianza de dos factores con una sola muestra por
grupo
Figura 54. Ingreso de datos
68
Ingresar los datos en el casillero rango de entrada $B3:$G$7, en el rango de
salida indicar la celda donde se insertara el resultado $B$36 y aceptar,
obteniendo el siguiente resultado.
Figura 55. Resultado del análisis de varianza de dos factores con una sola muestra
por grupo.
6.10. PRUEBA T PARA DOS MUESTRAS SUPONIENDO VARIANZA
DESIGUALES.
Esta herramienta de análisis ejecuta una prueba t de Student con dos conjuntos de
datos que proceden de varianzas desiguales, con los siguientes datos resolver el
ejercicio de la prueba t. con el fin de saber si existe diferencia entre las dos dietas
y son estadísticamente significativas.
69
Figura 56. Prueba t para dos muestras suponiendo varianzas desiguales.
6.10.1. Pasos para procesar la prueba t para dos muestras suponiendo
varianzas desiguales.
Hacer clic con el mouse en análisis de datos y seleccionar la opción prueba t para
dos muestras suponiendo varianzas desiguales, además se debe tomar en cuenta
que existe dos opciones de prueba t adicional. Prueba t para medias de dos
muestras emparejada y prueba t para dos muestras suponiendo varianzas iguales,
el procedimiento para el cálculo es el mismo, más su relación estadística
diferente.
Ingresar los datos de las variables uno $B$4:$B:14, variable dos $C$4:$C$C14,
señalar la celda del rango de salida y aceptar .
Figura 57. Ingreso de datos para calcular la prueba t para dos muestras.
70
Resultado de las dos variables de la prueba t para dos muestras suponiendo
varianzas desiguales.
Figura 58. Resultado de la prueba t para dos muestras.
6.11. ANÁLISIS DE MUESTRA O MUESTREO.
Esta herramienta de análisis se la puede utilizar cuando la población de un
estudio poblacional es demasiado grande para procesarla o representarla
gráficamente se puede extraer de la población general una muestra representativa
o si la población es periódica de un año, se puede extraer los datos de que
corresponde a un trimestre, ejemplo de una población de datos extraer una
muestra de 10.
6.11.1. Pasos para realizar el análisis del muestreo o muestra
Hacer clic en el menú datos, ficha análisis de datos y seleccionar la
herramienta muestra.
71
Figura 59. Ingreso de datos para el cálculo de la muestrea
Seleccionar los datos en el rango de entrada $B$4:$E$10.
En la opción periódico se utiliza los datos de la población de un año y se
quiere extraer la muestra de un trimestre se edita en el casillero el 4 y el
resultado corresponderá a un trimestre.
En la opción aleatoria o número de muestra se edita el 10 que corresponde
a las muestras que se quiere obtener del ejercicio.
Señalar el rango de salida o celda $G$4 y aceptar y se obtiene el siguiente
resultado.
Figura 60. Resultado del cálculo de la muestrea o muestreo.
72
6.12. ANÁLISIS MEDIA MÓVIL
La Media móvil es una herramienta de análisis de pronóstico de ventas e
inventarios u otras tendencias basada en datos históricos que al ser utilizada esta
herramienta nos da como resultado información de tendencia sobre ciertos
períodos, en el siguiente ejemplo se quiere saber cuál es el pronóstico de
producción mensual de banano de agosto a diciembre del presente año, tomando
como datos históricos la producción del año 2010.
6.12.1. Pasos para realizar el ejercicio de Media móvil
Hacer clic en el menú datos, análisis de datos y seleccionar la herramienta
de análisis media móvil.
Figura 61. Ingreso de datos para el cálculo de la media móvil.
En el cuadro de diálogo, hacer clic en rango de entrada y seleccionar el rango de
datos $C$4:$C15, en casillero Intervalos editar el 8 que corresponden a los datos
históricos de los meses enero hasta agosto.
El casillero rango de salida, señalar la celda para insertar el resultado, habilitar la
opción crear gráfico y aceptar se obtiene el siguiente resultado.
73
Figura 62. Resultado de la media móvil.
Figura 63. Proyección de la producción de banano en los próximos seis meses.
En el gráfico de líneas se aprecia los valores del pronóstico de producción y
valores históricos que nos da como resultado la herramienta de análisis media
móvil.
1200
1600
1100 900
1450 1300
850
980
1350
1050
960
1300
1173
1191
1123
1105
1155
0
200
400
600
800
1000
1200
1400
1600
1800
Ene. Feb Mar Abr May Jun Jul Ago Sep Oct Nov Dic
Val
or
Pronòstico de venta mensual
Real Pronóstico
74
7. FUNCIONES FINANCIERAS.
En este segmento de las funciones financieras de Excel nos permitiremos analizar
las funciones más frecuentes con sus respectivos argumentos teóricos y prácticos
sin tener que recurrir a fórmulas largas y complejas, a continuación resolveremos
algunos ejercicios de las siguientes funciones.
7.1. FUNCIÓN PAGO:
Esta función calcula el pago de un préstamo basándose en pagos constantes y una
tasa de interés constante, la sintaxis de esta función es la siguiente.
Sintaxis:Pago (tasa; nper;va;vf;tipo).
Tasa: Obligatorio. Es el tipo de interés del préstamo.
Nper: Obligatorio. Es el número total de pagos del préstamo.
Va: Obligatorio. Es el valor actual, o la cantidad total de una serie de
futuros pagos. También se conoce como valor bursátil.
Vf: Opcional. Es el valor futuro o un saldo en efectivo que se desea lograr
después de efectuar el último pago. Si el argumento vf se omite, se supone
que el valor es 0 (es decir, el valor futuro de un préstamo es 0).
Tipo: Opcional, es el número 0 (cero) o 1 e indica cuándo vencen los pagos.
Es decir 0 u omitido al final del período y 1 al inicio del período.
75
Ejercicio. Un banco nos concede un préstamo de $ 20000 a 2 años plazo con una
tasa de interés anual de 12%, con el fin de realizar mejoras en
nuestra bananera.
A B
1 Préstamo $ 20.000,00
2 Tasa anual 12%
3 Cantidad de cuotas (meses) 15
4 Vf 0
5 Tipo 0
6 Cuota a pagar es de -$ 941,47
Cuadro 5. Planteamiento del problema cuyo pago se realizara al final del período.
El cálculo se realizó con las celdas. B6=Pago(B2/12;B3;B1;B4;B5), el resultado
incluye capital e interés cuyo pago se realizará al final del período.
En el caso de producirse el pago al inicio del período el planteamiento del
problema es el siguiente.
A B
1 Préstamo $ 20.000
2 Tasa anual 12 %
3 Cantidad de cuotas (meses) 15
4 Vf 0
5 Tipo 1
6 Cuota -$ 932,15
Cuadro 6. Planteamiento del problema cuyo pago se realizará al comienzo del
período.
El cálculo se realizó con las celdas. B6=Pago(B2/12;B3;B1;B4;B5), el resultado
incluye capital e interés cuyo pago se realizara al final del período.
76
Debe notarse que el resultado es negativo y está en rojo, cuando se da estos casos
se debe agregar a la fórmula después del signo igual la función ABS, cuya
finalidad es convertir los valores negativos en positivos. B6=ABS (Pago
(B2/12;B3;B1;B4;B5).
Además la función pago se la puede utilizar en cálculos de ahorro en un tiempo
determinado. Ejemplo, determinar cuánto se tiene que pagar mensualmente para
tener un ahorro de $ 30000 en 15 años a una tasa anual de interés del 12% en una
libreta de ahorro.
A B
1 Ahorro (Capital) $ 30000
2 Tasa anual 9.5%
3 Años 15
6 Pagos Mensuales $ 310,81
Cuadro 7. Planteamiento del problema para determinar un ahorro a futuro.
El proceso de cálculo se realizó de la siguiente manera.
=ABS(Pago(B2/12;B3*12;B1)),si se depositan $ 310.81 mensuales en una cuenta
de ahorro, que paga el 9,5% de interés, al final de 15 años se abra ahorrado $
30.000.
7.2. FUNCIÓN PAGOINT.
La función pagoInt calcula el interés pagado en un período especificado por una
inversión, basándose en una tasa de interés y pagos en períodos constantes.
Sintaxis de la función PagoInt (tasa;período;nper;va;vf;tipo)
77
Tasa: Es la tasa de interés del período
Período: Es el período para el que se desea calcular el interés y deben estar entre
1 y el argumento Nper.
Nper: Es número total de pagos del préstamo
Va: Es el valor actual de una serie de pagos futuros
Vf: Es el valor futuro de una serie de pagos futuros. Si se omite se calcula como
cero.
Tipo: Es un numero 0 o 1 e indica el vencimiento de pagos
Tipo: 0 al final del periodo
Tipo: 1 al inicio del período.
Ejemplo, una institución financiera nos ha concedido un préstamo de $ 10000, con
una tasa anual del 11%, debiéndose pagar en 24 cuotas, con estos datos se quiere
saber ¿cuánto se va a pagar de interés por cuota.
Cuadro 8. Planteamiento del problema para el
cálculo del interés en el primer mes.
A B
1 Préstamo $ 25000
2 Tasa anual 12%
3 Calculo interés en cuota n° 1
4 Cantidad de cuotas (meses) 24
5 Vf 0
6 Pago de interés mensual $ 270.83
El cálculo se realizó con la siguiente fórmula= ABS (PagoInt=
(B2/12;B3;B4;B1;B5)).
78
En este caso se puede apreciar en la celda B6 que el valor a pagar es $ 270.83
Cuadro 9. Planteamiento del problema para el cálculo de interés en el último mes.
En este caso se calcula en la celda B6 los intereses que integra la cuota de pago en
el último mes 24.
7.3. FUNCIÓN PAGOPRIN.
Calcula el pago sobre el capital de una inversión durante un período determinado,
basándose en una tasa de interés constante y pagos periódicos constantes
Sintaxis PagoInt (tasa; período;nper;va;vf;tipo)
Tasa: Es la tasa de interés del período
Período: Es el período para el que se desea calcular la amortización y deben
estar entre 1 y el argumento Nper.
Nper: Es número total de pagos del préstamo
Va: Es el valor actual de una serie de pagos futuros
Vf : Es el valor futuro de una serie de pagos futuros. Si se omite se calcula como
cero
A B
1 Préstamo $ 25000
2 Tasa anual 12%
3 Calculo interés en cuota n° 24
4 Cantidad de cuotas (meses) 24
5 Vf 0
6 Interés $ 12.74
79
Tipo: Es un numero 0 o 1 e indica el vencimiento de pagos
Tipo:0 al final del período
Tipo : 1 al inicio del período.
A B
1 Préstamo $ 25000
2 Tasa anual 12%
3 Calculo interés en cuota 1
4 Cantidad de cuotas (meses) 24
5 Vf 0
6 Amortización $ 917,71
Cuadro 10. Datos para el cálculo de la función Pagoprin en el primer mes.
Celda B6= ABS(Pagoprint=(B2/12;B3;B4;B1;B5)).
En este caso se puede apreciar en la celda B6 se calcula la parte correspondiente a
amortización desde el primer mes.
A B
1 Préstamo $ 25000
2 Tasa anual 12%
3 Calculo interés en No. cuota 24
4 Cantidad de cuotas (meses) 24
5 Vf 0
6 Amortización $ 1175.81
Cuadro 11. Función Pagoprin en el último mes.
El resultado de la celda B6 la amortización que integra la cuota de pago en el
último mes 24.
Al realizar la sumatoria de la amortización e interés en ambos casos se obtiene el
valor de la cuota a pagar.
$270,83+$917.72=$ 1188.55
80
$12.74+$1175.81= $ 1188.55
El sistema desarrollado para calcular el préstamo es según el sistema francés
donde el valor de la cuota es constante.
7.4. FUNCIÓN INT.EFECTIVO.
Esta función calcula la tasa efectiva del interés anual, si se conocen la tasa de
interés anual nominal y él número de periodos de interés compuesto por año.
Sintaxis Int.Efectivo (Int_nominal; núm_por_año).
Int_nominal: Es la tasa de interés nominal.
Nùm por año: Es él número de pagos de interés compuesto por año.
A B
1 Préstamo $ 25000
2 Tasa anual 12%
3 Cantidad de cuotas (meses) 24
4 Periodicidad anual 12
5 Intereses Real 0.13%
Cuadro 12. Cálculo de la función Int.efectivo.
En la celda B5= Int.efectivo(B2; B4), nos calcula el interés efectivo para una
periodicidad de 12 pagos anuales dando como resultado de un 0.13% por mes.
7.5. FUNCIÓN TASA.NOMINAL.
Calcula la tasa de interés nominal anual, si se conocen la tasa efectiva y él número
de períodos de interés compuesto por año
Sintaxis Tasa.Nominal(tasa_efectiva;num_per)
81
Tasa efectiva: Es la tasa de interés efectiva anual
Núm_per: Es él número de pagos de interés por año
A B
1 Préstamo $ 25000
2 Interés efectivo 12%
3 Cantidad de cuotas
(meses)
24
4 Periodicidad anual 12
5 Tasa nominal 0.11%
Cuadro 13. Cálculo de la función Tasa nominal.
La celda B5 calcula la tasa nominal anual del préstamo, tomando el interés
efectivo en un período de 12 pagos anuales, la fórmula de la función es
=Tasa.Nominal (B2; B4), dando un resultado de 0.11 %.
7.6. FUNCIÓN TASA
Calcula la tasa de interés por período de una anualidad.
Sintaxis Tasa(Nper; pago;va;vf;tipo;estimar).
Nper: En este casillero va el número total de períodos de pago de una anualidad
Pago: Representa el pago que se efectúa en cada período y que no puede cambiar
durante la vida de anualidad, generalmente el argumento pago incluye el capital y
el interés pero no incluye ningún otro arancel o impuesto.
Va: Es el valor actual de la cantidad total de una serie de pagos futuros.
Vf: Es el valor futuro o saldo en efectivo que desea lograr después de efectuar el
último pago, si el argumento vf se omite, se asume que el valor es cero, por
ejemplo el valor futuro de un préstamo es cero.
82
Tipo: Es el valor debe ser 0 o 1 e indica el vencimiento de los pagos.
Tipo:0 al final del período
Tipo: 1 al inicio del periodo.
Estimar: Es la estimación de la tasa de interés, si el argumento estimar se omite
se supone que es 10%
Cuadro 14. Cálculo de la función Tasa.
En la celda B7 la función tasa calcula el interés mensual, la celda B8 al
multiplicarla por 12 periodicidad anual devuelve la tasa anual, el resultado de la
celda B7=Tasa (B1;B2;B3;B4;B5;B6), se realizó sobre el mismo ejemplo anterior
para verificar que los valores obtenidos son los mismos.
7.7. FUNCIÓN NPER
Esta función procesa el número de pagos de un préstamo, basado en pagos
constantes, periódicos y a una tasa de interés constante.
Sintaxis Nper (tasa; pago;va;vf;tipo)
Tasa: Es la tasa de interés por período
Pago: Es el pago efectuado en cada período, debe permanecer constante durante la
vida de la anualidad.
A B
1 Cantidad de cuotas 24
2 Cuota $ 941.47
3 Préstamo $ 25000
4 Vf 0
5 Tipo 0
6 Estimar
7 Tasa Mensual 12%
8 Tasa Anual (B7*12) 10,50%
83
Va: Es el valor actual o la suma total de una serie de futuros pagos
Vf: Es el valor futuro o saldo en efectivo que desea lograr después de efectuar el
último pago. Si el argumento vf se omite, se asume que el valor es cero(por
ejemplo el valor futuro de un préstamo es cero)
Tipo: Es el valor debe ser 0 o 1 e indica el vencimiento de los pagos
Tipo: 0 al final del periodo
Tipo: 1 al inicio del periodo
A B
1 Tasa Anual 12%
2 Cuota $ 941,47
3 Préstamo $ 20000
4 Vf
5 Tipo
6 Cantidad de cuotas 15
Cuadro 15. Datos del primer ejercicio para el cálculo de la función Nper.
La celda B6 calcula la cantidad de cuotas necesarias para saldar el préstamo según
las características del mismo (Interés, cuota, monto). Se mantiene el mismo
ejemplo para verificar los datos, de las celdas B6= Nper(B1/12; B2;B3;B4;B5)
7.8. FUNCIÓN SI
Devuelve un valor si la condición especificada es verdadera y otro valor si dicho
argumento es falso, esta función se utiliza para realizar pruebas condicionales en
valores y fórmulas.
Sintaxis SI (prueba_logica;valor_si_verdadero;valor_si_falso).
Prueba lógica: Es cualquier valor o expresión que puede evaluarse como
verdadero o falso.
Valor si verdadero: Es el valor que se devolverá si prueba lógica es verdadero.
84
Valor si falso: Es el valor que se devolverá si prueba lógica es falso.
Observaciones: Es posible añadir hasta siete funciones SI como argumento,
Valor si verdadero y Valor si V falso, ejercicio, el departamento de ventas
agrícolas pagara comisiones a los vendedores que tengan un determinado monto
de ventas realizadas, y en algunos casos no se pagaran comisiones, los datos son
los siguientes.
Ventas inferiores a $ 30.000 no se paga comisión
Ventas superiores a $30.000, e inferiores a $ 40.000, ganan un 2,8% de
comisión
Ventas de $40.000 en adelante ganan un 5% de comisión.
Cuadro 16. Cálculo de la función Sí.
La fórmula a introducir en la celda B2 para ser trasladada al resto es la siguiente.
=SI(A2<30000;"No comisión”;
SI(A2<40000;A2*2,8%;SI(A2>=40000;A2*5%))).
A B
1 Ventas Comisión
2 31.000 868
3 25000 No comisión
4 45000 2250
5 22.000 No comisión
6 50000 2500
7 35000 980
85
7.9. FUNCIÓN TIR:
SintaxisTir (valores; estimar).
Valores: Es una matriz o referencia a celda que contiene números para calcular la
tasa interna de retorno, el argumento valores debe contener al menos un valor
positivo y uno negativo.
Tir: Interpreta el orden de los flujos de caja siguiendo el orden del argumento
valores, deben introducirse valores de los pagos e ingresos en el orden correcto.
Estimar: Es un número que se estima que se aproxima al resultado de Tir.
En la mayoría de los casos no se necesita proporcionar el argumento estimar, se
supone que es 0,1 (10%)
Proyectos de Inversión
A B C D E F G H
1 INGRESOS
2 Inversión
Inicial Año 1 Año 2 Año 3 Año 4 Año 5
TIR
Año 5
TIR
Año 4
3 ($ 89.000) $ 16.300 $ 24.500 $ 26.500 $ 25.600 $ 27.700 10,27% 2%
4 ($ 74.000) $ 13.600 $ 15.800 $ 19.100 $ 23.000 $ 23.525 8,10% -1%
5 ($ 80.000) $ 17.000 $ 19.000 $ 20.000 $ 22.000 $ 26.000 8,78% -1%
Cuadro 17. Cálculo de la inversión de 3 proyectos con la función TIR.
En este ejercicio podemos interpretar que el primer proyecto, el de mejor
inversión es el primero por tener una tasa interna producida del10.27% en los
cinco años de inversión. El cálculo del Tir también se realizó para los cuatro años
de inversión. A continuación la fórmulas del Tir. En las respectivas celdas.
86
CELDA G3=TIR (A3:F3), CELDA H3= TIR (A3:E3)
CELDA G4=TIR (A4:F4), CELDA H4= TIR (A4:E4)
CELDA G5=TIR (A5:F5), CELDA H5= TIR (A5:E5)
7.10. FUNCIÓN VF.PLAN
Calcula el valor futuro de un capital inicial al aplicar una serie de tasas de interés
compuesto, para calcular el valor futuro de una inversión con tasa variable o
ajustable.
Sintaxis Vf.Plan(capital;plan_serie_de_tasas)
Capital: Es el valor presente
Plan_serie_de_tasas: Es una matriz con las tasas de interés que se aplican.
En el siguiente ejemplo realizaremos el cálculo con la función. Vf.Plan.
Se realiza una inversión de $ 120000, durante el 1er
año la tasa será de 8,40%, en
el 2do
de 7,20%, en el 3ro
de 11,25% y durante el 4to
de 6,55%.
A B
1 Capital Inicial $ 90000
2 Tasa
3 Primer año 8,10%
4 Segundo año 11,40%
5 Tercer año 0 9,20%
6 Cuarto año 0 12,30%
7 VF.PLAN $ 132909.43
Cuadro 18. Cálculo total de varias tasas de interés a futuro de un capital
con la función VF.PLA, en la celda B7, se aplicó la fórmula = VF.PLA (B1;
B3:B6).
87
8. GRÁFICOS
En este capítulo nos permitirá aprender a graficar los datos en una hoja de cálculo.
Para ello Excel dispone de una variedad de categorías y además usted puede
personalizar la graficación de los datos.
Los diferentes tipos de gráficos sirven para representar e interpretar información
según la necesidad de los usuarios, los mismos que sirven para la enseñanza
aprendizaje de los alumnos, la última versión de Excel ha mejorado la calidad
visual de su presentación y las diferentes opciones de formato.
Los gráficos de Excel se vinculan con los datos a partir de los que se crean y se
actualizan cuando se cambian éstos, se puede crear gráficos a partir de celdas o
rangos no contiguos. También puede crear gráficos a partir de tablas dinámicas,
para crear un gráfico seleccionar las celdas que contienen los datos que desea
presentar en el gráfico.
Si desea que los rótulos de fila o columna aparezcan en el gráfico, incluya en la
selección las celdas que los contienen. Haga clic en asistente para gráficos. Siga
sus instrucciones.
8.1 ¿QUÉ ES UN GRAFICO?
Un gráfico lo podemos definir como la representación gráfica de datos en una hoja
de cálculo. Al seleccionar los datos estos aparecen según el tipo de gráfico
seleccionado, los puntos de datos se agrupan en series de datos y se distinguen por
el diseño aplicado según la opción seleccionada.
Los gráficos pueden ser creados en la misma hoja de cálculo como si
fueran objetos o en una hoja diferente independiente del libro de trabajo.
88
Pero en ambos casos los datos están vinculados automáticamente.
8.1.1 Partes de un gráfico8:
Un gráfico de Excel está formado por diferentes partes que incluyen el área del
gráfico, las series de datos, ejes, leyendas, rótulos del eje, entre otros. El siguiente
gráfico muestra las partes de un gráfico en Excel.
Área del gráfico. Esta es el área que se encuentra definida por el marco del
gráfico y que incluye todas sus partes.
Título del gráfico. Texto descriptivo del gráfico que se coloca en la parte
superior.
Puntos de datos. Es un símbolo dentro del gráfico (barra, área, punto, línea)
que representa un solo valor dentro de la hoja de Excel, es decir que su
valor viene de una celda (etiquetas).
Series de datos. Son los puntos de datos relacionados entre sí trazados en
un gráfico, cada serie de datos tiene un color exclusivo. Un gráfico puede
tener una o más series de datos a excepción de los gráficos circulares que
solamente pueden tener una serie de datos.
Ejes. Un eje es la línea que sirve como referencia de medida. El eje Y es
conocido como el eje vertical y generalmente contiene datos. El eje X es
conocido también como el eje horizontal y suele contener las categorías
del gráfico.
Área de trazado. Es el área delimitada por los ejes e incluye todas las series
de datos.
8http://exceltotal.com/partes-de-un-grafico-de-excel/, Libro de Excel Total
89
Líneas de división. Son líneas opcionales que extienden los valores de los
ejes de manera que faciliten su lectura e interpretación.
Título de eje: Texto descriptivo que se alinea automáticamente al eje
correspondiente.
Leyenda. Un cuadro que ayuda a identificar los colores asignados a las
series de datos.
90
PARTES DE UN GRÁFICO.
4,3
2,5
3,5
4,5
2,4
4,4
1,8
2,8
0
0,5
1
1,5
2
2,5
3
3,5
4
4,5
5
Cacao Tomate Pimiento Banano
Producciòn agrìcola
Enero Febrero
PARTES DE UN GRAFICO
Leyenda
Eje de categorías ( X)
Eje de valores Y
Título del
gráfico
Etiquetas datos
Área del gráfico
Serie de datos
Línea de división
Área de trazado
Figura 64. Partes de un gráfico
91
8.2 TIPOS DE GRÁFICOS
Elegir el tipo de gráfico adecuado para mostrar la información es de suma
importancia, cada tipo de gráfico desplegará la información de una manera
diferente así que utilizar el gráfico adecuado ayudará a dar la interpretación
correcta a los datos, estos son los tipos de gráficos más utilizados en la enseñanza
aprendizaje.
8.2.1 Gráficos de columna Los gráficos de columnas hacen un énfasis especial en las variaciones de los
datos a través del tiempo, Las categorías de datos son visualizados en los ejes
horizontal (X) y la escala o valores en el vertical (Y), además tiene la posibilidad
de graficar datos con un segundo eje (Z). Frecuentemente se compara este tipo de
gráfico con los gráficos de barra, donde la diferencia principal es que en los
gráficos de barra las categorías aparecen en el eje vertical, el gráfico de columnas
agrupadas es bastante utilizado en graficación de datos de histogramas.
Figura 65. Diferentes tipos de gráficos de columnas.
Con los datos de la siguiente tabla, graficaremos los meses de enero y marzo con
un gráfico de columna agrupada y el mes de febrero un gráfico de líneas
agrupadas con un eje secundario.
92
Cuadro 19. Datos a graficar
GRAFICACIÓN DE DATOS
Figura 66. Gráficos de columnas agrupadas, líneas con
marcadores y un eje secundario
Cuando existen valores demasiados pequeños como los datos de febrero es
necesario representarlos con un gráfico de líneas y un eje secundario para una
mejor interpretación y visualización.
15
5
10 8
10 12
23
47
25
6 2
0
3
4
8
0
1
2
3
4
5
6
7
8
9
0
5
10
15
20
25
30
35
40
45
50
Banano Cacao Tomate Soya Pepino
Enero Marzo Febrero
Productos Producción agrícola
Enero Febrero Marzo
Banano 15 2 12
Cacao 5 0.5 23
Tomate 10 3 47
Soya 8 4 25
Pepino 10 8 6
Eje Secun
dario
93
8.2.1.1 Los pasos a seguir son los siguientes.
Seleccionamos los datos de enero, febrero, marzo con sus respectivos
productos y hacer clic en el menú insertar y seleccionar el primer
gráfico de columna agrupada.
Una vez creado el gráfico, seleccionamos las columnas que corresponden a
febrero y hacer clic en el menú insertar, seleccionar un gráfico de líneas con
marcadores y aceptar.
Para crear el eje secundario del gráfico de líneas, hacer clic lado derecho
del gráfico de líneas y seleccionamos la opción dar formato serie de datos y
eje secundario.
Tomar en cuenta que para realizar cualquier cambio en un gráfico solo basta
hacer clic lado derecho y seleccionar la opción que se requiera cambiaren y
listo. Puede ser la escala del gráfico, eje, color, tamaño de la letra, líneas,
leyenda, categorías etc.
94
GRAFICACIÓN DE UN HISTOGRAMA
Figura 67. Histograma
8.2.1.2 Pasos a seguir para crear un histograma.
Seleccionar los datos de marzo, hacer clic en menú insertar y seleccionar el
gráfico de columnas agrupadas y aceptar.
Una vez creado el gráfico de columnas hacer clic lado derecho en cualquier
columna y seleccionar la opción dar formato serie de datos.
Figura 68. Formato serie de datos
12
23
47
25
6
0
5
10
15
20
25
30
35
40
45
50
Banano Cacao Tomate Soya Pepino
Producción agrícola
Marzo
Opción ancho del intervalo.
95
Presionar el puntero del mouse en la opción ancho del intervalo hacia la
izquierda y aceptar.
8.2.2 Gráficos de línea.
Un gráfico de línea muestra las relaciones de los cambios en los datos en un
período de tiempo. Este gráfico es comparado con los gráficos de área, pero los
gráficos de línea hacen un énfasis especial en las tendencias de los datos más que
en las cantidades de cambio como lo hacen los gráficos de área.
Figura 69. Diferentes tipos de gráficos de líneas.
Estos gráficos son muy populares y se usan cuando se desea representar el cambio
de una magnitud a lo largo del tiempo o de cualquier proceso, su construcción es
similar a la de otros tipos, se seleccionan los datos y se pide insertar el gráfico
determinado, por ejemplo a representar linealmente los datos que ya hemos
usado en el caso de columnas elige como tipo línea y como subtipo línea con
marcadores.
Un complemento interesante para este tipo de gráfico es la inserción de líneas de
promedio y de error, que te permiten valorar la situación de un elemento dentro
del grupo, lo más sencillo de comprender es que dibuja la desviación típica, para
insertarla selecciona la serie de datos y busca en la ficha de herramientas de
gráficos el grupo presentación, y dentro de él análisis y botón barras de error.
96
Productos Ventas
Banano 12
Cacao 23
Tomate 30
Soya 25
Pepino 6
Cuadro 20. Datos a graficar
Para la selección de las líneas de error es necesario tener conocimiento
estadístico, por lo que se ha seleccionado barras de error con desviación estándar,
obteniendo la línea de los promedios complementada por unas barras que indican
un alejamiento de la misma de una desviación típica. Así se destacarán los datos
que se alejan de la media de forma significativa.
Lo importante de es que en la cinta de análisis se encuentran varias opciones,
como las líneas de tendencia, líneas de unión, líneas de los máximos y mínimos,
barras ascendentes y descendentes y barras de error con sus respectivos
subgrupos.
Ventas; Banano; 12
Ventas; Cacao; 23
Ventas; Tomate; 30
Ventas; Soya; 25
Ventas; Pepino; 60
0
20
40
60
80
Banano Cacao Tomate Soya Pepino
Gráfico de líneas con marcadores
Ventas Lineal (Ventas)
Figura 70. Gráfico de líneas con marcadores y barra de error con Desviación Standar
97
Figura 71. Herramientas de análisis en la graficación de datos
8.2.3 Gráficos circulares.
También conocidos como gráficos de pie o gráficos de pastel, se pueden
representar datos contenidos en una columna o una fila de acuerdo a su proporción
puede ser en su total o porcentajes, siendo más utilizados en la graficación de
datos sobre ventas.
Figura 72. Diferentes tipos de gráficos circulares.
Cuadro 21. Datos a graficar
Productos Ventas %
Banano 12
Cacao 23
Tomate 30
Soya 25
Pepino 6
98
Figura 73. Graficación datos.
8.2.4 Gráficos de barra.
Un gráfico de barra hace énfasis en la comparación entre elementos de un período
de forma individual de tiempo específico, los datos se organizan en columnas y
filas.
Figura 74. Diferentes tipos de gráficos de barra.
Ventas %; Banano; 12;
13% Ventas %; Cacao; 23;
24%
Ventas %; Tomate; 30;
31%
Ventas %; Soya; 25;
26%
Ventas %; Pepino; 6;
6%
Ventas %
Banano Cacao Tomate Soya Pepino
99
Productos Producción agrícola
Enero Febrero Marzo
Banano 15 2 12
Cacao 5 0.5 23
Tomate 10 3 47
Soya 8 4 25
Pepino 10 8 6
Cuadro 22. Datos a graficar
Figura 75. Graficación de datos de barra
8.2.5 Gráficos radiales
Para realizar la graficación de datos en un gráfico radial es necesario tener
organizados los datos en columnas o filas, Los gráficos radiales comparan los
valores agregados de varias series de datos, cada serie de datos de un gráfico tiene
una trama o color exclusivo y se representa en la leyenda del gráfico se puede
trazar una o más series de datos en un gráfico.
12
23
47
25
6
36
55
41
0 50 100 150 200
Enero
Febrero
Marzo
Abril
Mayo
Junio
Julio
Agosto
Grafico 3D apilada
Semillas Bulbos Flores
100
Cuadro 23. Datos a graficar.
Meses Ventas plantas
Semilla Bulbos Flores Arboles
Enero 78 45 12 34
Febrero 120 28 85 58
Marzo 56 92 41 95
Abril 87 46 65 26
Mayo 36 56 95 35
Sigue……
Figura 76. Graficación de datos
8.2.6 Gráficos XY (dispersión).
Un gráfico de dispersión tiene dos ejes y son útiles para mostrar y comparar un
conjunto de datos numéricos, por ejemplo datos científicos, estadísticos e
Ingeniería o como las proyecciones de venta de una empresa.
Figura 77. Diferentes tipos de gráficos dispersión.
101
A continuación graficaremos los datos de la siguiente tabla con dos variables.
Cuadro 24. Datos a graficar.
Figura 78. Gráfico de líneas con marcadores y
Línea de tendencia y ecuación del gráfico
Para realizar los gráficos, se seleccionó los datos de las variables y hacer clic en
el menú insertar, tipo de gráfico de dispersión elegir el grafico correspondiente,
para agregar la ecuación del gráfico y la línea de tendencia exponencial, hacer clic
en el menú presentación y herramientas de análisis y líneas de tendencias y
habilitar las opciones necesarias.
Ejercicio. Crear gráfico de dispersión con la ecuación Y = x2 +3
8 9
6 7 6
4 3 3 3
y = 10,623e-0,219x R² = 0,9155
0
2
4
6
8
10
0 2 4 6 8
Gráfico de dispersión con línea suavizada
Valores Y Exponencial (Valores Y)
X 1 1 2 3 3 4 5 6 6
Y 8 9 6 7 6 4 3 3 3
102
Cuadro 25. Datos a graficar y fórmulas.
Filas B C C
X Y Fórmula
3 -2 7 = B3^2+3
4 -1.5 5.25 = B4^2+3
5 -1 4 = B5^2+3
6 -0.5 3.25 = B6^2+3
7 0 3 = B7^2+3
8 0.5 3.25 = B8^2+3
9 1 4 = B9^2+3
10 1.5 5.25 = B10^2+3
11 2 7 = B3112+3
Figura 80: Graficación de una parábola con la ecuación antes descrita.
En cuanto al gráfico de la parábola se insertó los valores de X, Y se resolvió la
ecuación Y = x2 +3, según las fórmulas detalladas en el cuadro 25, luego se
procedió a realizar el mismo procedimiento del gráfico anterior.
7
5,2
5
4
3,2
5 3
3,2
5
4
5,2
5
7
0
1
2
3
4
5
6
7
8
-3 -2 -1 0 1 2 3
y
103
9 TABLAS DINÁMICAS
Una tabla dinámica es una herramienta potente que realiza resúmenes e informes
de manera ordenada y añade filtros de la información en diferentes perspectivas
de una la lista de campos o de un archivo que contenga una base de datos.
Con los siguientes datos realizaremos un ejercicio de tabla dinámica.
PRODUCCIÓN BANANO
Figura 81:Datos para crear una tabla dinámica.
Con los datos de las personas, producción de cajas y las ventas, nos plantearemos
las siguientes preguntas.
104
¿Cuántas cajas ha vendido el señor Milton Stalin Collaguazo?, ¿Cuánto es el total
de venta del señor Luis Felipe Peña?, ¿Cuál es el promedio de ventas de los
bananeros?
¿Cuántas cajas ha vendido el señor Milton Collaguazo?
Lo primero que haremos será hacer clic en el menú insertar, tabla dinámica,
seleccionar los datos que queremos usar para crear la tabla dinámica, en este
caso sería el rango B1:D21. Una vez seleccionado el rango, en nuestro caso, las
columnas B, C, y D. Nos vamos al menú insertar, opción tabla dinámica.
Figura 82:Crear tabla dinámica.
En el casillero tabla de rango, ingresar el rango de datos de la tabla y seleccionar
el rango de salida (seleccionar una celda), es decir usted tiene que señalar la celda
donde podrá la respuesta.
Como resultado de los pasos antes mencionados se inserta un cuadro de diálogo
con dos cuerpos, el primero es para habilitar y deshabilitar la tabla dinámica y el
segundo nos muestra un listado de campos de la tabla dinámica, los mismos que
pueden ser seleccionados y arrastrados hacia la parte inferior (filtro de informes,
etiquetas de columnas, etiquetas de fila y sumatoria de valores.
105
Figura 83: El primer cuadro habilita y deshabilita la tabla dinámica y el segundo
listados de campos.
En el segundo cuadro se ha seleccionado los campos nombres/bananeros y ventas
los mismos que han sido arrastrados hacia la parte inferior dando como resultado
los nombres y cuantas ventas han realizado cada bananero. Si queremos saber el
promedio de venta de cada bananero, presionamos en la pestaña suma de ventas y
habilitamos la opción configuración del campo de valor, presionamos promedio,
nos da el resultado que deseamos obtener.
Figura 87: Campo arrastrado a los cuadros de etiquetas de filas y sumatoria de
valores y proceder a calcular el promedio.
106
9.1 Gráficos dinámicos.
Una vez creada la tabla dinámica, es fácil crear un gráfico dinámico, debemos
habilitar la tabla y hacer clic en el menú opciones, ícono grafico dinámico y
procede insertar el listado de gráficos seleccionamos el de nuestro interés y
finalmente seleccionamos cualquier campo y listo tenemos nuestro grafico
dinámico.
También se puede hacer clic en cualquiera celda de los resultados de la tabla
dinámica y seguimos los pasos 2 y 3 del esquema anterior, ejercicio
Habilitar
Tabla
Dinámica
Menú
opciones
Habilitar cualquier campo
para crear el grafico
dinámico.
2
3
1
4
Figura 88. Pasos para crear gráfico dinámico.
107
Figura 89: Tabla dinámica con los respectivos campos.
Como resultado tenemos el siguiente gráfico con los respectivos campos.
Figura 90: Gráfico dinámico
La graficación dinámica de los productos puede variar si realizamos un filtro en el
campo producto. Además podemos mover los demás campos, haciendo clic lado
derecho encima del campo y selecciona mover al filtro del informe. De esa
manera puede variar la graficación de los datos según su necesidad, y a propósito,
los datos de la base de datos y tabla dinámica pueden ser filtrados de igual
manera.
108
10 SOLVER
SOLVER es un paquete agregado a Excel, que sirve para optimizar modelos
matemáticos, sujeto a restricciones. Los problemas a resolver pueden ser
problemas lineales, no lineales y enteros, siendo muy usado en las áreas de la
ingeniería.
La herramienta Solver nos permite resolver problemas que dependan de
numerosas celdas, permitiendo la combinación de variables que maximizan o
minimizan una celda objetivo la misma que puede estar sometida a restricciones.
Si la dependencia es lineal, es en realidad el problema matemático de
Programación Lineal. Esta opción de Solver no viene instalada en Microsoft
Excel, por lo cual es necesario habilitarla.
La posibilidad que tiene Solver de resolver problemas que tengan hasta 200
variables de decisión, 100 restricciones explícitas y 400 simples.
10.1 Instalar Solver:
a) Primer paso:
Para instalar la opción Solver es necesario hacer clic en cualquier ícono de la cinta
de opciones del menú de Excel, lado derecho y hacer clic en personalizar la cinta
de opciones.
Clic lado derecho Mouse
109
Figura 91: Personalizar la cinta de opciones para habilitar Solver. b) Segundo paso: c) Tercer paso:
Seleccionar el casillero Solver, aceptar e inmediatamente se instala la
aplicación. Figura 93: Habilitar Solver.
Para verificar si se instaló la aplicación, hacer clic en datos y Solver.
Figura 94. Opciones del menú para habilitar Solver.
Figura 92: Habilitar la opción complementos, luego en ir.
1
2
1 2
110
Ventana principal de Solver:
Figura 95: Ventana principal de Solver.
La celda objetivo: Siempre debe contener una fórmula que corresponde al
problema en cuestión. Si desea hallar el máximo o el mínimo, seleccione los
respectivos casilleros.
Casilla Valores: Está seleccionada, Solver tratará de hallar un valor de la celda
igual al valor del campo que se encuentra a la derecha de la selección.
Cambiando las Celdas: contendrá la ubicación de las variables de decisión para
el problema, en cuanto a las restricciones se deben especificar en el campo.
El botón Cambiar: Permite modificar las restricciones recién introducidas y
eliminar sirve para borrar las restricciones precedentes.
111
Restablecer todo: Borra el problema en curso y restablece todos los
Parámetros a sus valores por defecto.
El botón Opciones: Se accede a las opciones de Solver (ver más adelante).
A continuación, y para mayor claridad, se señalan las partes más importantes del
cuadro de diálogo de los parámetros de Solver
Figura 96: Agregar restricciones.
Referencia de la Celda: Aquí se especifica la ubicación de una celda (fórmula).
Se introduce el tipo de restricción haciendo clic en la flecha del campo central
desplegable (<=, >=, =, int, donde int se refiere a un número entero, o bin, donde
bin se refiere a binario).
El casillero Restricción: Puede llevar una fórmula de celdas, una simple
referencia a una celda o un valor numérico.
El botón Agregar: Añade la restricción especificada al modelo existente y
vuelve a la ventana Agregar Restricción.
112
El botón Aceptar: Añade la restricción al modelo y vuelve a la ventana
Parámetros de Solver. Vale mencionar que en la ventana opciones se especifica
que las variables no pueden ser negativas. Si el modelo es un programa lineal o
un programa entero lineal, se aconseja seleccionar la casilla Adoptar Modelo
Lineal.
Seleccione la casilla Asumir No Negativo si desea que todos los valores de las
celdas cambiantes sean ≥ 0. Seleccione Mostrar Resultados de Iteraciones si le
interesa ver la información iteración por iteración tiempo de procesamiento. Usar
Escala Automática es útil si el modelo que utiliza tiene una escala defectuosa.
Es importante recordar seleccionar Adoptar Modelo Lineal si se trata de un
programa lineal o un programa lineal entero, seleccione la casilla asumir no
negativos si desea que las celdas cambiantes adopten sólo valores no negativos.
Ejercicio:
Se requiere realizar la compra de ganado según la tabla.
Figura 97: Este caso la celda objetivo es el total del presupuesto.
La inversión tiene las siguientes restricciones:
Producciòn Precio Cantidades TotalCerdos 300
Bovinos 250
Caprino 120
Total Presupuesto
Celda Objetivo
113
Figura 98: restricciones del ejercicio
En este ejercicio sencillo, la compra de ganado tiene algunas restricciones como
la inversión de solo $ 15000.
Pasos para realizar el ejercicio:
a) Como primer paso se debe ingresar las fórmulas en las celdas de las columnas
total y objetivo de la figura 97.
Figura 99: Ingreso de fórmulas en la columna total y celda objetivo.
En la figura 99, se insertó en la columna cantidades valores supuestos y se
procede ingresar las fórmulas en la columna total y de igual manera en la celda
objetivo, luego se borra los datos de la columna cantidad.
b) Hacer clic en el menú Datos, Solver, se inserta una ventana para ingresar los
parámetros de Solver.
* Solo podemos invertir en la compra de productos $ 15000
* El mayorista nos vende el ganado en pie
* Màximo tengo que comprar
Cantidades
Cerdos 12
Bovinos 11
Caprino 7
Restricciones
Producciòn Precio Cantidades TotalCerdos 300 0
Bovinos 250 0
Caprino 120 0
Total Presupuesto 0
Celda Objetivo
Ejemplo
=+D5*E5
=+D6*E6
=+D7*E7
=SUMA(F5:F8)
114
Figura 100: Ingreso de parámetros Solver
c) Haciendo clic en agregar le indicamos a Solver la referencias de celdas, de los
cerdos, bovinos, caprinos es entero, ingresando una por una correspondiente a la
columna cantidades de la figura 99,
Figura 101:Agregando restricciones donde va ir el resultado.
d) La siguiente restricción se refiere a cuantos cerdos, bovinos y caprinos, como
mínimo se va adquirir. Seleccionamos celda por celda de la columna cantidades
de la figura 97 y celda de la figura 98. Le indicamos que la restricción de cerdos
es >= 12, bovinos >=11, caprinos >= 7, y aceptar.
Celda objetivo Presupuesto
Inversión 15000
Rango celdas,se indica donde se quiere que Solver me devuelva los valores.
Agregar restricciones
1
2
3
4
Referencia de celda de cerdos de la columna cantidad.
La restricción es entero por que la unidad de compra es entero.
Entero (int.)
Restricción ingresar una por una
Referencia celda de la columna cantidades.
1
2
3
115
Figura 102: Agregando las restricciones una por una
e) Debiendo quedar la ventana principal de Solver así.
Figura 103: Ventana principal de Solver donde se visualiza el ingreso de las restricciones y variables.
f) Después hacer clic en la opción resolver nos da el resultado en la columna
cantidades, donde nos indica que si nosotros queremos comprar 12 cerdos Solver
nos indica 26 etc.
Figura 104: Resultado del ejercicio realizado por Solver.
g) Casos para resolver con Solver.
Una empresa proveedora de alimentos de balanceados generadoras de beneficios
ha obtenido una orden de compra para producir un compuesto con, por lo menos
100 gramos de fibra, 300 gramos de proteína y 70 gramos de minerales. En el
mercado puede obtener los siguientes productos con las siguientes características.
Producciòn Precio Cantidades Total
Cerdos 300 26 7800
Bovinos 250 12 3000
Caprino 120 35 4200
Total Presupuesto 15000
Celda Objetivo
Maximiza
ción de la
compra
por Solver
116
CONTENIDO PRODUCTOS
Prod1. Prod2. Prod3.
Fibras 20 % 30 % 5 %
Proteínas 60 % 50 % 38 %
Minerales 9 % 8 % 8 %
Precio por Kg. $ 10 $ 15 $ 8
Se necesita que Solver determine.
a) ¿Cuál será la proporción de cada producto en el compuesto óptimo?
b) ¿A cuánto ascenderá el precio sobra (por gramo) de: fibras, proteínas y
minerales.
117
11 BIBLIOGRAFÍA
Batanero, C., Estepa, A. y Godino, J. D. (1991). Análisis exploratorio de datos:
Sus posibilidades en la enseñanza secundaria. Suma, 9, pp. 25-31.
Berenson, Mark L. y Levine, David M. (1996). Estadística Básica en
Administración. Edit. Prentice Hall.México.
Brent, E. y Mirielli, E. (1991). Statisticalnavigatorprofessional. Columbia, MO:
The Idea Works, Inc.
Godino, Juan D. (1995) . ¿Qué aportan los ordenadores a la enseñanza y
aprendizaje de la estadística?. Versión revisada del artículo publicado en
UNO, 5, pp.45-56
Pérez, César (2002). Estadística Aplicada a través de Excel. Edit. Prentice Hall.
Madrid.
Microsoft Office Excel 2010 SP3.Microsoft Corporation. 2010
Ramírez, Israel J.Notas de Clases de Microsoft Excel. Universidad de Los Andes,
FACES. Mérida, Venezuela. 2007.
Fernández, Gonzalo H. Excel Fianaciero. Pcia Bs. As Argentina