CÓDIGOS BÁSICOS DE FORMATO DE NÚMERO

Para definir el color de una sección del formato, escriba en la sección el nombre del color entre corchetes. El color debe ser el primer elemento de la sección.

[NEGRO] [AZUL][AGUAMARINA] [VERDE][MAGENTA] [ROJO][BLANCO] [AMARILLO]

Además se puede ingresar una condición, por ejemplo, que se visualicen en color azul todos números con 2 decimales cuyos valores mayores o iguales a 10, caso contrario que se visualicen en color rojo. Las condiciones se escribirán así:[Azul][>=10]#,00;[Rojo] #,00. Para separar una condición de otra se usa el separador de listas que se sugiere sea el punto y coma.Ejercicio:Establezca una nueva condición: color rojo valores menores igual a 10; verde para valores entre 10,01 y 20; y azul para valores mayores a 20

FORMATO CONDICIONALEl formato condicional permite modificar el aspecto de determinadas celdas, de acuerdo al cumplimiento o no de reglas lógicas. Soporta hasta 64 condiciones. Por ejemplo: Dar el color de fuente rojo y trama amarilla para las notas menores a 7 de la columna nota1. Para iniciar activar el menú Inicio en el bloque Estilos seleccione Formato Condicional y por último Administrar reglas. Excel ofrece algunas alternativas:

Para resolver el ejercicio, seleccione las celdas que desee resaltar.1. Ir al grupo de Estilos, haga clic en Formato condicional, seleccione Reglas de celdas opción Es menor que

FORMATO CONDICIONAL

Si elige Administrar reglas, se visualiza el siguiente cuadro de diálogo:

Dentro de esta pantalla se pueden colocar las condiciones, para crear una nueva regla de clic en Nueva regla… Seleccionar la opción Aplicar formato únicamente a las celdas que contengan Se tiene la siguiente pantalla:

Seleccione las condiciones que desea aplicar al valor de la celda y para aplicar el formato de clic en el botón Formato…

DESTACAR GRÁFICAMENTE EL VALOR DE CADA CELDA

Seleccione un rango de celdas, y dentro del menú desplegable del botón “Formato Condicional” seleccione la opción “Barras de Datos” y dentro de ella el color que quiere utilizar

VALIDACIÓN DE DATOS

Esta herramienta permite que Excel supervise el ingreso de información en una hoja de cálculo sobre la base de un conjunto de criterios previamente establecidos. Puede crear una lista de entradas que se aceptarán en una celda de la hoja de cálculo y a continuación, restringir la celda para que acepte únicamente las entradas de la lista mediante el comando del menú Datos opción Validación. El usuario que introduzca los datos puede hacer una selección en la lista.

Ejemplo: El usuario que introduzca los datos puede hacer una selección en la lista. Crear una lista de entrada en la misma hoja, por ejemplo:

Para trabajar con Validación de datos los datos deben estar en la misma hoja, debe seleccionar la o las celdas a validar. Para este ejemplo se validará una celda que permita seleccionar el número de RUC.

1. Seleccione la celda a validar.2. Ir a la pestaña Datos3. En el Grupo Herramientas de datos4. Opción Validación de datos5. Se visualiza

7. En la pestaña Configuración en Criterio de validación en la opción Permitir: seleccionar Lista.

9. En Origen indicar el rango que contiene la lista10. Por ejemplo:

MENSAJE ENTRANTE/ERRORPara crear un mensaje entrante haga lo siguiente:1. Marcar la celda en la que desea se cree el Mensaje entrante2. Ir a la pestaña Datos3. En el Grupo Herramientas de datos4. De clic en la opción Validación de datos5. Seleccione la pestaña Mensaje de entrada

Un vez configurado el mensaje entrante se visualiza:

FUNCIONES

Una función como cualquier dato se puede escribir directamente en la celda si conocemos su sintaxis, pero Excel dispone de una ayuda o asistente para utilizarlas, así nos resultará más fácil trabajar con ellas. Si queremos introducir una función en una celda:1. Situarse en la celda donde queremos introducir la función.2. Hacer clic en la pestaña Fórmulas.3. Elegir la opción Insertar función.

Aparecerá el siguiente cuadro de diálogo Insertar función:

Excel tiene más de 327 funciones divididas por categorías (sin contar claro está con las que el usuario puede definir), estas son:

1.- Funciones de complemento y automatización2.- Funciones de cubo3.- Funciones de base de datos4.- Funciones de fecha y hora5.- Funciones de ingeniería6.- Funciones financieras7.- Funciones de información8.- Funciones lógicas9.- Funciones de búsqueda y referencia10.- Funciones matemáticas y trigonométricas11.- Funciones estadísticas12.- Funciones de texto

FUNCIONESLAS MAS COMUNESA estas funciones se puede acceder en forma rápida desde el icono ya que haciendo clic en el triangulito se despliega un menú emergente con botones a todas ellas

SUMA Realiza sumas de forma rápidaPROMEDIO Esta función devuelve el promedio de sus argumentos que pueden ser; números, nombres, referencias a celdas y referencias a rangos de celdas que contengan números, puede tener hasta un máximo de 255 argumentos siendo por lo menos uno obligatorioMAX La función MAX() da el valor máximo de un rango o rangos y/o de referencias a celdas, como se ve en la tabla siguienteMIN La función MIN() da el valor mínimo de un rango o rangos y/o de referecias a celdas, como se cave en la tabla siguienteCONTAR como se ve la función está en la celda C9 de valor 8 que es el resultado de contar los números y fechas del rango C1:D5 y de las referencias C7 Y D7 , se puede apreciar que se ignoran en la cuenta los blancos , los valores lógicos , lo errores y las cadenas de caracteres.Para contar celdas en blanco disponemos de función CONTAR .BLANCO.Para contar celdas que no están vacías CONTARA

FUNCION SUMAR.SILa función SUMAR.SI permite sumar valores de un rango de acuerdo a un criterio o condición.

La función SUMAR.SI tiene 3 parámetros:El primero es la referencia o el rango que contiene los valores sobre los que se evaluará la condición.El segundo es el que contiene el criterio a aplicar con el objeto de determinar que se suma y que noEl tercero es opcional, esto quiere decir que si la condición esta en el mismo rango donde se efectúa la suma, no hace falta el tercer parámetro, pero si el criterio esta en un rango y donde se hace la suma en otro (u otros )rangos, entonces tiene que colocarse el tercer parámetro.

Para aclarar las cosas que mejor que un ejemplo: Supongamos que una inmobiliaria tiene un listado con el valor de las propiedades que se vendieron en Enero y quiere saber la suma de aquellas que superaron los $160.000, para obtener la respuesta se emplea la función SUMAR.SI como se muestra en el gráfico

En este caso con dos parámetros alcanza puesto que el criterio esta en la rango E2:E5, que el mismo rango donde se efectúa la suma con la condición dada y no hace falta poner =SUMA(E2:E5;">160000";E2:E5)..Si en cambio tenemos esta otra tabla

aquí si hace falta el tercer parámetro ya que el rango donde se efectúa el criterio (D2:D5) no es el mismo que el rango donde se efectúa la suma (E2:E5).Dejo como ejercicio averiguar las comisiones que se cobran al vendedor por propiedades cuyo costo es inferior a $ 400.000.

FUNCION SUMAR.SI.CONJUNTO

SUMAR.SI.CONJUNTO, que es parecida a SUMAR.SI, que suma celdas teniendo en cuenta un solo criterio, en tanto que SUMAR.SI.CONJUNTO, suma celdas teniendo en cuenta múltiples criterios.SINTAXIS:SUMAR.SI.CONJUNTO(rango de sumas; criterio_rango1; criterio1; criterio rango2; criterio2..)donde:rango de sumas: es un argumento obligatorio, en el que se suman una o mas celdas.criterio_rango1: es un argumento requerido que es el primer rango en el que se evalúa el criterio asociado ( criterio1).criterio1: argumento obligatorio que es un criterio asociado a criterio_rango1 en la forma de número, expresión, referencia de celda o texto, que define en cual celda, en el rango de sumas, se adicionará debido al criterio_rango1.Los otros argumentos son opcionales.Veremos un ejemplo donde se consideran 3 criterios:Un negocio de artículos de computación y electrodomésticos cuenta con la siguiente tabla

y se quiere saber cual fue la suma de las ventas de Juan López, en el sector Electrodomésticos con montos que superaron los 200 $.Como se ve los criterios son:

"Juan López""Electrodomésticos"">200

FUNCION SUMAR.SI.CONJUNTO

entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)

que insertamos en la celda D24, dando el resultado 4200$, como se puede ver

FUNCION CONTAR.SIEsta función es una combinación de las funciones CONTAR y SI , tiene dos argumentos, el primero es el rango cuyas celdas se desean contar y el segundo es el criterio que determina que celda será contada o no

con esta misma tabla podríamos preguntar cuántos hombres hay

FUNCION CONTAR.SI.CONJUNTO

CONTAR.SI.CONJUNTO, que es parecida a CONTAR.SI, que cuenta celdas teniendo en cuenta un solo criterio, en tanto que CONTAR.SI.CONJUNTO, cuenta celdas teniendo en cuenta múltiples criterios.SINTAXIS:CONTAR.SI.CONJUNTO(rango1; criterio1; rango2; criterio2…)pudiendo especificarse mas de 127 rangos/criterios.donde :rango1: es el rango donde se cuentan las celdas, debido al criterio1, criterio2, etc.Los primeros dos argumentos son obligatorios.Veremos un ejemplo donde se consideran 3 criterios:Un negocio de artículos de computación y electrodomésticos cuenta con la siguiente tablay quiere saber cuantas veces el vendedor Juan Lopez aparece en la misma, en el sector Electrodomésticos con ventas que superan los 200 $.Como se ve los criterios son:

"Juan López""Electrodomésticos"">200entonces para responder a lo que el negocio quiere saber , se introduce la tabla en una Hoja de Excel y se usa la fórmula ( en la tabla se resaltan las filas que cumplen los 3 criterios)

que insertamos en la celda D23, dando el resultado 3, como se puede ver

FUNCION CONTAR.SI.CONJUNTO

FUNCION DESREFLa función DESREF es tan útil como difícil de entender al principio.DESREF devuelve una referencia a partir de otra que podemos llamar referencia de partida, vamos a tratar de aclarar esto. Recordemos que una referencia es el código de una celda( A1;F3;H124, etc) o el código de un rango de celdas(A3:G6;H5:K7;etc) y aquí pasan dos cosas distintas según se trate de una celda o un rango de celdas; veamos

Aquí se ve que si se trata de la referencia a una celda Excel devuelve el contenido de esa celda( la fórmula está puesta en el recuadro negro) y en este caso DESREF funciona así

La referencia que devuelve( y por tanto su contenido) es el que resulta de ubicarse en la celda B2 y desplazace x filas y luego x columnas. Concretamente una posibilidad podría ser

y obtengo la referencia a una celda, que en este caso es la D5 y por lo tanto su contenido.Hablando en forma simple: parto de B3 me desplazo 3 celdas hacia abajo, luego 2 celdas hacia la derecha devuelve la referencia a la celda D5 y muestra su contenido.

Una aclaración: si me desplazo hacia arriba o a la izquierda tengo que anteponer el signo menos y cuidar siempre de no salirme de los límites de la hoja porque sino da error, como podemos ver

Cuando nuestra referencia de partida es un rango, la sintaxis de DESREF() cambia un poco

FUNCION DESREF

si dejamos los argumentos para celda en cero, partimos de C2:E7 y ponemos 9 para alto y 4 para ancho

vemos que la referencia de partida, que está resaltada en rojo, se transforma en la referencia C2:F10, resaltada en verde, es como si la referencia de partida se dilatara de C2:E7 a C2:F10, que finalmente es lo que devuelve DESREF, pero como se ve nos da un error y es lógico que así sea pues en este caso Excel no sabe que hacer ¿qué número va a devolver si tiene 3 opciónes?, lo mas coherente es que haga algo con ellos, como sumarlos por ejemplo, pero DESREF por si sola no puede y tiene que anidarse con otras funciónes como SUMA ( que sumaria 42+100+450=592), pero también puede anidarse con PROMEDIO, MAX, MIN, etc y así se elimina el error, como vemos en la siguiente tabla

RANGOS DINAMICOS

Voy a dar un ejemplo sencillo de referencia dinámica, también llamada rango variable.Supónganos que en una familia se anotan los gastos diarios confeccionando la siguiente tabla en Excel

Una forma de calcular los subtotales, por ejemplo hasta el día 4, sería emplear la función SUMA con el rango fijo C2:C5 , pero si al día 5 queremos ingresar otro dato, este no es tomado hasta que no actualicemos el rango a C2:C6, se entiende que es muy poco práctico hacer esto toda vez que queramos ingresar un valor, lo que necesitamos es un rango que varíe en forma automática o sea un rango variable

Para hacer que nuestro rango se actualice usaremos la función CONTAR anidada con DESREF dentro de la función SUMA . Como puede verse, estamos ante el caso particular de una columna donde el rango debe alargarse(cambiar de alto) y por lo tanto al usar DESREF solo nos hacen falta 2 parámetros; el parámetro de partida C2 y alto, en los parámetros de fila y columna( que son obligatorios) se pone cero o ""(blanco) y el parámetro ancho ( que no es obligatorio ) se omite. Todo el truco está en hacer que alto se expanda hacia abajo y para eso lo reemplazamos con la función CONTAR , que cuenta las celdas que no están vacías, por lo tanto siempre nos pondrá el valor correcto en "alto" y finalmente nuestra formula queda

RANGOS DINAMICOS

RANGOS VARIABLES UTILIZANDO NOMBRES

ahora le pondremos nombre a la fórmula DESREF(C2;0;0;CONTAR(C:C)), para esto vamos a la pestaña Fórmulas, luego a la sección Nombres definidos donde pulsamos en Asignar nombre a un rango

en Nombre introducimos, por ejemplo, "GASTOS_ MENSUALES" y en "Hace referencia a" borramos lo que esta escrito, dejando el signo "=", y luego introducimos la formula DESREF(C2;0;0;CONTAR(C:C))

Acordarse que al poner un nombre no deben dejarse espacios en blanco ni incluir los símbolos (+ ;* ; = ; -) ya que Excel interpreta que estamos por introducir una fórmula o función.Si queremos eliminar un nombre, en el panel Nombres definidos pulamos en Administrador de nombres

FUNCIONES LOGICAS

Una proposición lógica es una afirmación que puede se VERDADERA o FALSA pero no ambas a la vez y justamente los argumentos de las funciones lógicas son proposiciones lógicas. También las funciones lógicas devuelven resultados VERDADERO o FALSO. Esta es una definición que se cumple en forma estricta solamente para las siguientes funciones: Y

ONOFUNCION Y()

La función Y() ,como O() es una función lógica ya que sus argumentos son proposiciones lógicas, la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO ( aclaro que esta función puede tener un solo parámetro sin dar error, aunque no tiene mucho sentido práctico)Su sintaxis es: Y(parámetro1;parámetro2;parámetro3;.....)La función devuelve VERDADERO si la evaluación de todos los parámetros es VERDADERA y dará FALSO si la evaluación al menos uno de sus parámetros es FALSA o si todos son FALSOS.

vemos que si cambiamos una desigualdad, o las dos el resultado es FALSO

FUNCION O()

Como Y() la función O() es una función lógica, porque sus argumentos son proposiciones lógicas o pruebas lógicas la función evalúa los argumentos y devuelve un resultado VERDADERO o FALSO., su sintaxis es O(parámetro1;parámetro2;parámetro3;.....)La función devuelve FALSO si la evaluación de todos los parámetros es FALSO y dará VERDADERO si la evaluación al menos uno de sus parámetros es VERDADERO o si todos son VERDADEROS.

Función SILa función SI sirve para tomar decisiones de acuerdo a una condición, por eso podríamos decir que es una función condicional,s iendo la condición el resultado de la evaluación de una proposición lógica ( VERDADERO o FALSO), es decir; si el resultado es VERDADERO se hace una cosa, y si es FALSO se hace otra..Esta función tiene 3 argumentos

Ejemplo:De acuerdo a un informe volcado a una tabla una empresa quiere saber en que meses tuvo pérdidas o ganancias

para lo cual estos datos se ponen en una Hoja de Excel y se usa la función SI de la siguiente manera

Observaciones: Es posible anidar hasta siete funciones SI como argumento Valor_si_verdadero y Valor_si _ falso para construir formulas mas elaboradas

FUNCIONES DE BÚSQUEDA Y REFERENCIA

La función CONSULTARV busca datos que están en primera columna de una tabla(a esta tabla se la denomina matriz de búsqueda o de datos), si el valor es encontrado devuelve el dato asociado (valor que esta en la misma fila que el dato a buscar) de una columna especificada, la sintaxis es;

Veamos el siguiente ejemplo:Un profesor tiene una tabla con las notas de un alumno puestas en números y quiere completarla poniendo las notas en palabras

para hacer esto cuenta con otra tabla de equivalencias

coloque estos datos en un libro de Excel poniendo en la Hoja1 la tabla a completar y en la Hoja2 la tabla con las equivalencias pero sin los rótulos para tener directamente la matriz de datos

en la que D3 es una referencia donde está el contenido , que en este caso es el valor 2, aunque hay casos en que por la naturaleza del problema, por ejemplo una consulta, la referencia puede al principio estar vacia, dando el error #N/A (no aplicable)

Al digitar el número de RUC, con la función BUSCARV se debe desplegar el nombre del cliente, que debe ser buscado de en la hoja Clientes. El mismo ejercicio lo pues realizar si los datos de la hoja cliente se encuentra en otro libro de MS Excel.

FUNCIONES ANIDADAS

Funciones anidadas dentro de funcionesEn algunos casos, puede que tenga que utilizar una función como uno de los argumentos de otra función. Por ejemplo, la fórmula utiliza una función PROMEDIO anidada y compara el resultado con el valor 50. Las funciones anidadas utilizan una función como uno de los argumentos de otra. Puede anidar hasta 64 niveles de funciones.

Ejemplo: Para el ejercicio anterior aplicar lo siguiente Si la forma de pago es al CONTADO y es MAYORISTA realice el descuento del 20%. Si la forma de pago el al CONTADO y es MINORISTA realice el descuento del 10%. Si la forma de pago el al CREDITO y es MAYORISTA realice el descuento del 15%. Caso contrario sin descuento. Para solucionar aplicaremos el anidamiento de funciones. Dentro de la función lógica SI insertaremos un Y para unir las dos condiciones:

Aprovechamos el Valor_si_Falso para anidar el resto de condiciones. La fórmula resultante es: =SI(Y(F5= "CONTADO";D21="MAYORISTA");F20*20%;SI(Y(F5="CONTADO";D21="MINORISTA");F20*10%;SI(Y(F5="CREDITO";D21="MAYORISTA");F20*15%;0)))

EJERCICIO

Nombre Sexo Edad Peso Talla IMC Categoria

Catalina Durán F 29 54 1,57 22Normal

J. Miguel Contreras M 30 75 1,72 25Sobre Peso

Ingrid Herrera F 35 80 1,61 31Morbido I

Luis Muñoz M 26 82 1,67 29Sobre Peso

Pamela Islas F 39 90 1,56 37Morbido II

bajo peso < 18normal 18 - 25sobre peso 25 - 30morbido I 30 - 35Morbido II 35 - 40Morbido III > 40

SI(F5<18;"Bajo Peso";SI(Y(F5>=18;F5<25);"Normal";SI(Y(F5>=25;F5<30);"Sobre Peso";SI(Y(F5>=30;F5<35);"Morbido I";SI(Y(F5>=35;F5<40);"Morbido II";"Morbido III")))))

TABLAS DINAMICASLas Tablas Dinámicas son una forma alternativa de presentar o resumir los datos de una lista, es decir, una forma de ver los datos desde puntos de vista diferentes.El nombre Tabla Dinámica se debe a que los encabezados de fila y columna de la lista pueden cambiar de posición y también pueden ser filtrados.Con las Tablas Dinámicas también podremos preparar los datos para ser utilizados en la confección de gráficos. La comprensión cabal de este tema se obtiene con la práctica y es así como se verá que es uno de los tópicos mas potentes de Excel, principalmente en las versiones mas recientes.

Una empresa de exportación de máquinas agrícolas tiene la siguiente tabla en una Hoja de Excel.donde figuran los datos del 1º trimestre del año.

a partir de ella se quiere crear una nueva tabla en la que se informe la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.

TABLAS DINAMICASCrear un informe de la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.

Para crear la tabla que nos responda a estas preguntas, nos ubicamos en cualquier celda de la tabla, luego vamos a la pestaña "insertar" panel "Tablas"

en el que pulsamos en "Tabla dinámica", al hacer esto aparece un menú en el que tenemos las opciones de "Gráfico dinámico" y "Tabla dinámica" , pulsaremos en este último como se puede ver

a continuación aparece el panel "Crear tabla dinámica"

TABLAS DINAMICAS1. Una nueva cinta de opciones denominada "Herramienta de tabla dinámica"

2. Un panel llamado "Lista de campos de tabla dinámica" que es una novedad de Excel y que tiene un rectángulo en la parte superior, donde se ubican los campos o rótulos de la tabla de origen, también hay cuatro rectángulos, en la parte inferior, denominados " Filtro de informe", "Rótulos de columna", "Rótulos de fila" y "Valores" donde irán apareciendo los rótulos de la tabla a medida que los seleccionemos en la parte suprior en forma de botones como el que se nuestra

Los botones se pueden arrastrar de un rectángulo a otro aunque los rótulos que tienen valores numéricos, siempre aparecen en rectángulo "Valores".

Crear un informe de la cantidad de maquinarias exportadas y el detalle de cuantas se vendieron de cada una.

si nos interesara saber solamente el dato de cuantas fertilizadora y sembradora se exportaron, junto con su total, tenemos un comando de filtrado en la parte superior y así obtenemos la siguiente tabla

TABLAS DINAMICAS

TABLAS DINAMICASPodemos querer saber el detalle de las máquinas que fueron exportadas y por cual vendedor. En este caso tendremos que seleccionar la casilla del rótulo VENDEDOR y en la nueva Hoja aparece una tabla y el panel "Lista de campos de tabla dinámica"

La tabla responde a lo que queremos saber, pero le podemos dar otro aspecto arrastrando el botón VENDEDOR al rectángulo "Rótulo de columna"

TABLAS DINAMICAS

Sería interesante saber el número de maquinarias exportadas a que país y por cual vendedor.

Hasta ahora nuestra tabla dinámica efectúa sumas, pero puede hacer otras operaciones tales como porcentajes, máximos, mínimo y otras mas que iremos viendo.Podemos preguntarnos cual fue la máxima cantidad de maquinarias que vendió Peña. Para hacer esto nos ubicamos en una celda cualquiera de la tabla de arriba y apretando el botón derecho del mouse aparece el siguiente menú emergente

TABLAS DINAMICAS

"Resumen por" están las opciones de resumen en las que elegiremos Máx y luego de aceptar, la tabla se transforma en

TABLAS DINAMICAS

TABLAS DINAMICAS CON RANGOS DINAMICOSUna aplicación de los RANGOS VARIABLES, es cuando trabajamos con TABLAS DINAMICAS, ya que podemos agregar o quitar elementos de la tabla origen de datos (tabla base)sin necesidad de actualizar la referencia al rango en forma manual, o sea que se hace en forma automática. Para hacer esto vamos a utilizas NOMBRES , pero no le vamos a dar un nombre a un rango, le daremos un nombre a una fórmula ( Excel considera a las fórmulas como si fueran rangos)dicha fórmula será el ANIDAMIENTO entre las funciones DESREF Y CONTARAcomo ya se vio, la sintaxis de DESREF es DESREF(referencia ;filas;columnas;alto;ancho)

referencia: la celda en el ángulo superior izquierdo de la tabla base filas:para este caso es 0columnas: para este caso es 0alto: la cantidad de filas en nuestra tabla baseancho: la cantidad de columnas en nuestra tabla baseEsta fórmula se anidara con CONTARA para que DEREF se transforme en dinámica quedando=DESREF(Hoja1!$A$1,0,0,CONTARA(Hoja1!$A:$A),CONTARA(Hoja1!$1:$1)) a esta fórmula le daremos el nombre tabla_base_dinamica

TABLAS DINAMICAS CON RANGOS DINAMICOS

con este nombre creamos la tabla dinámica

en este momento ya estamos en condiciones de agregar datos a la tabla base original

si con ella hubiéramos confeccionado la siguiente tabla dinámica

y agregamos otro país, por ejemplo Italia

luego de ir al panel "DATOS" de la pestaña "OPCIONES" y pulsar en actualizar

TABLAS DINAMICAS CON RANGOS DINAMICOS

TABLAS DINAMICAS

Código Nombre Apellido Departamento Cargo Delegación Sueldo1Cristina Martínez Comercial Comercial Norte $ 1.262,13 2Jorge Rico Administración Director Sur $ 2.404,05 3Luis Guerrero Márketing Jefe producto Centro $ 1.502,53 4Oscar Cortina Márketing Jefe producto Sur $ 1.803,04 5Lourdes Merino Administración Administrativo Centro $ 781,32 6Jaime Sánchez Márketing Assistant Centro $ 721,21 7José Bonaparte Administración Administrativo Norte $ 781,32 8Eva Esteve Comercial Comercial Sur $ 1.202,02 9Federico García Márketing Director Centro $ 2.404,05

10Merche Torres Comercial Assistant Sur $ 661,11 11Jordi Fontana Comercial Director Norte $ 1.502,53 12Ana Antón Administración Administrativo Norte $ 811,37 13Sergio Galindo Márketing Jefe producto Centro $ 1.352,28 14Elena Casado Comercial Director Sur $ 2.554,30 15Nuria Pérez Comercial Comercial Centro $ 901,52 16Diego Martín Administración Administrativo Norte $ 841,42

Construir a partir de los siguientes datos, las tablas dinámicas que muestren la siguiente información:Tabla dinámica 1: Cantidad de personas por departamento.Tabla dinámica 2: Cantidad de personas por departamento y delegaciónTabla dinámica 3: Suma y promedio de sueldo por departamento.Tabla dinámica 4: Sueldo más alto por departamento y cargo.Realizar un gráfico por cada tablaLas cuatro tablas dinámicas deben estar una debajo de la otra y en la misma hoja.

EJERCICIO TABLAS DINAMICAS

País Deportista Prueba PuntosFrancia Pierre Carrera 8Francia Phillipe Carrera 7España Ramón Carrera 6España Juan Carrera 5España Alberto Carrera 4Inglaterra John Carrera 3Inglaterra Tom Carrera 6Francia Pierre Natación 4Francia Phillipe Natación 5España Ramón Natación 2España Juan Natación 7España Alberto Natación 6Inglaterra John Natación 3Inglaterra Tom Natación 5Francia Pierre Bicicleta 3Francia Phillipe Bicicleta 4España Ramón Bicicleta 8España Juan Bicicleta 8España Alberto Bicicleta 9Inglaterra John Bicicleta 4Inglaterra Tom Bicicleta 4

Construir a partir de los siguientes datos, cuatro tablas dinámicas que muestren la siguiente información:Tabla dinámica 1: Suma de puntos por deportista y prueba.Tabla dinámica 2: Suma de puntos por país y prueba.Tabla dinámica 3: Suma de puntos por país, deportista, y prueba.Tabla dinámica 4: Media de puntos por país y prueba.Las cuatro tablas dinámicas deben estar una debajo de la otra y en la misma hojaRealizar un gráfico por tabla.

FILTROS AVANZADOS

En los filtros avanzados se utilizan criterios lógicos para filtrar las filas, en este caso, se debe especificar el rango de celdas donde se ubican los mismos, veamos como se procede.En la cinta de opciones debemos ir a la pestaña "Datos" y luego al panel "Ordenar y filtrar" donde oprimimos el botón "Avanzadas" luego aparece el panel "Filtro avanzado"

1. Filtrar la lista sin moverla a otro lugar: se filtran los datos en el mismo lugar donde se encuentra la tabla.

2. Copiar a otro lugar: la tabla filtrada puede aparecer en un lugar especificado de la misma Hoja o en otra Hoja de cálculo.

3. Rango de la lista : automáticamente Excel coloca el rango done esta la lista

4. Rango de criterios: es el rango elegido por el usuario para ubicar los criterios de filtrado.

5. Copiar a: esta opción queda habilitada cuando se marca la casilla del punto 2, en cuyo caso deberemos especificar el lugar sonde queremos que aparezca la tabla filtrada, para esto solo es necesario especificar donde estarán los rótulos.

6. Sólo registros únicos: en el caso de haber registros duplicados, mostrar solo uno de ellos.

en este caso las filas se han ocultado como en el caso de autofiltros, para solucionar el problema debemos copiar la tabla filtrada a otro lugar, por ejemplo al rango $E$1:$F$1, quedando el panel emergente como se ve

FILTROS AVANZADOS

y la tabla en su nuevo lugar se ve como en la figura

aquí se puede ver que las filas están en forma correlativa y por lo tanto se pueden aplicar funciones, por ejemplo si quisiéremos contar el número de alumnos con un porcentaje de asistencia del 70%, utilizaríamos la función CONTAR y como se puede ver se obtiene el resultado correcto que es 8

PROGRAMACION LINEAL

OPTIMIZACIÓN RESTRINGIDA

Un problema de optimización consiste en encontrar aquellos valores de ciertas variables que optimizan (es decir, hacen máxima o mínima, según el caso), una función de estas variables. A las variables las llamaremos variables controlables o variables de decisión.

Matemáticamente, significa encontrar los valores de x1, x2,..., xn, tales que hacen máxima (o mínima) a la función

f (x1, x2,..., xn).no

síSolución

Inicial¿Es

óptima?

Fin

NuevaSolución

El método más conocido para encontrar el óptimo de una función es a través del los métodos numéricos, que parten de una solución inicial, y mediante algún algoritmo iterativo, mejoran sucesivamente la solución

METODOS DE OPTIMIZACION RESTRINGIDA

El nombre se debe a que podemos ponerle restricciones a las variables, de modo que cumplan una o más condiciones.

La restricción más común que se da en la práctica es que las variables deben ser no negativas. No tiene ningún sentido una "solución" que implique producir cantidades negativas, o sembrar un número negativo de hectáreas, o llevar un número negativo de paquetes, por ejemplo.

Pero, además, surgen naturalmente otras restricciones en el mundo real, debido a limitaciones de horas de trabajo, capital, tiempo, insumos, o a que quizás deseamos imponer ciertos mínimos o máximos de calidad, riesgo, etc.. Estas restricciones pueden ser funciones de las variables controlables.

Se llama programación lineal al conjunto de técnicas matemáticas que pretenden resolver la situación siguiente:

Optimizar (maximizar o minimizar) una función objetivo, función lineal de varias variables, sujeta a:

una serie de restricciones, expresadas por inecuaciones lineales.

Podríamos resumir diciendo que en un problema de optimización restringida buscamos los valores de ciertas variables que optimizan una función objetivo, sujetas a restricciones, dadas también en términos de funciones.

Matemáticamente, significa encontrar los valores de x1, x2, ..., xn, tales que hacen máxima (o

mínima) a f (x1, x2, ..., xn), sujeto a restricciones de tipo gj (x1, x2, ..., xn) , = ó cj , donde cj es

una constante.

Un problema de programación lineal en dos variables, tiene la siguiente formulación estándar:

pudiendo cambiarse maximizar por minimizar, y el sentido de las desigualdades.

• La función f(x,y) = ax + by + c llamada función objetivo y que es necesario optimizar. En esa expresión x e y son las variables de decisión, mientras que a, b y c son constantes.

• Las restricciones que deben ser inecuaciones lineales. Su número depende del problema en cuestión. El carácter de desigualdad viene impuesto por las limitaciones, disponibilidades o necesidades, que son: inferiores a ... ( menores: < o ); como mínimo de ... (mayores: > o ) . Tanto si se trata de maximizar como de minimizar, las desigualdades pueden darse en cualquiera de los dos sentidos.

• Al conjunto de valores de x e y que verifican todas y cada una de las restricciones se lo denomina conjunto (o región ) factible. Todo punto de ese conjunto puede ser solución del problema; todo punto no perteneciente a ese conjunto no puede ser solución.

• La solución óptima del problema será un par de valores (x0, y0) del conjunto factible que haga que f(x,y) tome el valor

máximo o mínimo.

En una urbanización se van a construir casas de dos tipos: A y B. La empresa constructora dispone para ello de un máximo de 180 millones de dólares, siendo el coste de cada tipo de casa de 30 y 20 mil dólares, respectivamente. El MIDUVI exige que el número total de casas que no sea superior a 80.Sabiendo que el beneficio obtenido por la venta de una casa de tipo A es 4 mil y de 3 mil por una de tipo B, ¿cuántas casas deben construirse de cada tipo para obtener el máximo beneficio?

Variables: x = nº de casas tipo A ; y = nº de casas tipo B Función objetivo: Maximizar Z = f(x,y) = 4x + 3y Conjunto de restricciones: El coste total 30x + 20y ≤ 180 . El MIDUVI impone x + y ≤ 80 . De no negatividad: x ≥ 0 , y ≥ 0.

Unos grandes almacenes encargan a un fabricante pantalones y chaquetas deportivas. El fabricante dispone para la confección de 750 m de tejido de algodón y 1000 m de tejido de poliéster. Cada pantalón precisa 1 m de algodón y 2 m de poliéster. Para cada chaqueta se necesitan 1.5 m de algodón y 1 m de poliéster.El precio del pantalón se fija en 50 dólares y el de la chaqueta en 40. ¿Qué número de pantalones y chaquetas debe suministrar el fabricante a los almacenes para que éstos consigan una venta máxima?1 Elección de las incógnitas.x = número de pantalonesy = número de chaquetas2 Función objetivof(x,y)= 50x + 40y3 RestriccionesPara escribir las restricciones vamos a ayudarnos de una tabla: pantalones chaquetas disponible algodón 1 1,5 750 poliéster 2 1 1000

x + 1.5y ≤ 750 2x + y ≤ 1000Como el número de pantalones y chaquetas son números naturales, tendremos dos restricciones más:x ≥ 0y ≥ 04 Hallar el conjunto de soluciones factibles

HERRAMIENTA SOLVER

Solver es una herramienta para resolver y optimizar ecuaciones mediante el uso de métodos numéricos.

Con Solver, se puede buscar el valor óptimo para una celda, denominada celda objetivo, en donde se escribe

la fórmula de la función objetivo f (x1, x2, ..., xn).

Solver cambia los valores de un grupo de celdas, denominadas celdas cambiantes, y que estén relacionadas, directa o indirectamente, con la fórmula de la celda objetivo. En estas celdas se encuentran los valores de las

variables controlables x1, x2, ..., xn.

Puede agregar restricciones a Solver, escribiendo una fórmula gj (x1, x2, ..., xn) en una celda, y

especificando que la celda deberá ser mayor o igual, igual, o menor o igual que otra celda que contiene la

constante cj.

También puede especificar que los valores sean enteros, para evitar dar resultados absurdos de algunos problemas, tales como que se necesitan 3,5 empleados.Solver ajustará los valores de las celdas cambiantes, para generar el resultado especificado en la fórmula de la celda objetivo.

INSTALAR SOLVER

En el menú Herramientas, fíjese si aparece el comando Solver. Si no aparece, deberá instalar el complemento o macro automática Solver.

Si tiene Excel 97

•En el menú Herramientas, elija Complementos.Si Solver no aparece en la lista del cuadro de diálogo Complementos, haga clic en Examinar y localice la unidad, la carpeta y el nombre de archivo Solver.xla que, normalmente, está ubicado en la carpeta Macros/Solver, o ejecute el programa de instalación si no puede localizar el archivo.

•En el cuadro de diálogo Complementos, seleccione la casilla de verificación Solver.

Si tiene Excel 5.0

•Elija el Programa de Instalación de Microsoft Excel.•Elija Agregar / Eliminar componentes.•Seleccione Macros Automáticas, y haga clic en el botón Modificar Opción.•Seleccione la casilla Solver, y luego haga clic en Aceptar.•Haga clic en Aceptar.

ALGORITMOS Y MÉTODOS UTILIZADOS POR SOLVER

Microsoft Excel Solver utiliza diversos métodos de solución, dependiendo de las opciones que seleccione.

•Para los problemas de Programación Lineal utiliza el método Simplex.

•Para problemas lineales enteros utiliza el método de ramificación y límite, implantado por John Watson y Dan Fylstra de Frontline Systems, Inc.

•Para problemas no lineales utiliza el código de optimización no lineal (GRG2) desarrollado por la Universidad Leon Lasdon de Austin (Texas) y la Universidad Allan Waren (Cleveland).

SOLVER Y PROGRAMACIÓN LINEAL

La Protrac Inc., fabrica dos tipos de productos químicos, E y F, cuya utilidad neta es de $5000 y $4000 por tonelada respectivamente.

•Ambos pasan por operaciones de 2 departamentos de producción, que tienen una disponibilidad limitada.

•El departamento A dispone de 150 horas mensuales; cada tonelada de E utiliza 10 horas de este departamento, y cada tonelada de F, 15 horas.

•El departamento B tiene una disponibilidad de 160 horas mensuales. Cada tonelada de E precisa de 20 horas, y cada tonelada de F precisa de 10 horas para su producción.

•Para la producción global de E y F, se deberán utilizar al menos 135 horas de verificación en el próximo mes; el producto E precisa de 30 horas y F de 10 horas por tonelada de verificación .

•La alta gerencia ha decretado que es necesario producir al menos una tonelada de F por cada 3 de E .

•Un cliente ha solicitado 5 toneladas, cualquiera sea su tipo, de E o F.

•Por otro lado, es evidente que no pueden producirse cantidades negativas de E ni de F.

Se trata de decidir, para el mes próximo, las cantidades a producir de cada uno de los productos para maximizar la utilidad global.

El Modelo

Variables controlables

E : toneladas de tipo E a producir;F: toneladas de tipo F a producir;

Modelo

Max 5000 E + 4000 F {Función objetivo: maximizar la utilidad global}sujeto a {escribimos ahora las restricciones o requerimientos}10 E + 15 F 150 {horas del departamento A}20 E + 10 F 160 {horas del departamento B}30 E + 10 F 135 {horas de verificación}E - 3 F 0 {al menos una de F cada 3 E significa E 3 F}E + F 5 {al menos 5 toneladas}E 0, F 0 {no negatividad}

Productos: E F

Utilidad marginal: 5000 4000

Restricciones

Departamento A: 10 15 150

Departamento B: 20 10 160

Verificación: 30 10 135

Al menos un E cada 3F: 1 -3 0

Al menos 5: 1 1 5

Antes de introducir este modelo en la planilla, conviene preparar una tabla con los coeficientes de las variables:

Abra una nueva planilla de cálculo. Antes de introducir los datos en la planilla, conviene aumentar el ancho de la columna A para que aparezcan completos los rótulos de esta columna. Las demás columnas pueden quedar sin alterar.Comenzaremos suponiendo que no producimos nada de E ni de F, por lo que escribiremos 0 (cero) en las celdas B5 y C5.

CONSTRUCCION DE LA HOJA DE CALCULO

Ingrese:

=B4*B5+C4*C5

Ingrese:

=sumaproducto(B$5:C$5,B7:C7)

Copie la fórmula de la celda D7

Una vez introducidos estos datos, podemos probar con distintas cantidades a producir de E y de F, y ver fácilmente si se cumplen las restricciones, y cuál será la utilidad global. Así, por ejemplo, poniendo 6 en la celda B5 y 2 en la celda C5, se respetan todas las restricciones y se obtiene una utilidad global de $38000. Pruebe con éstos y otros valores.

OPTIMIZACION

Observe que en la planilla hemos introducido la función objetivo en la celda A2; el lado izquierdo de las restricciones en el rango D7:D11, y el lado derecho de las restricciones en el rango F7:F11.

Seleccione del menú Herramientas / Solver...

Aparecerá el cuadro de diálogo Parámetros de Solver, en la que ingresaremos los datos.

Cuando el dato sea una celda o un bloque de celdas, puede seleccionarlas haciendo clic en la hoja de cálculo y arrastrando el mouse.

•Con el cuadro de diálogo abierto, haga clic en la celda A2 de la planilla. En la caja debajo de Celda objetivo se borra el contenido anterior y se muestra $A$2. •Haga clic en la opción Máximo.•Haga clic en la caja debajo de Cambiando las celdas. Haga clic en la celda B5, y arrastre el mouse sin soltarlo para seleccionar también la celda C5.•Haga clic en el botón Agregar..., debajo de Sujetas a las siguientes restricciones.Aparece el cuadro de diálogo Agregar restricción.

•Use la caja debajo de Referencia de la celda: para poner el lado izquierdo de la restricción.•Use la lista desplegable del centro para elegir un símbolo. •Use la caja debajo de Restricción para agregar el lado derecho de la restricción.Haga clic en el botón Agregar para agregar más restricciones, o en el botón Aceptar para finalizar.

•El cuadro de diálogo Parámetros de Solver debe quedar:

Haga clic en el botón Opciones, con lo que aparecerá el cuadro de diálogo Opciones de Solver.

Como nuestro modelo es lineal, seleccione la casilla de verificación Adoptar modelo lineal, y luego haga clic en el botón Aceptar.

RESOLUCIÓN

Una vez introducidos estos datos, seleccione Resolver, y Solver, si todo anduvo bien, mostrará un mensaje con:

Utilizar solución de Solver {cambia los valores de las variables en la planilla}

Restaurar valores originales {deja los valores iniciales de las variables}

Guardar escenario {guarda los valores de las variables como escenario}

Informes {hasta 3 tipos de informes, en hojas separadas}Seleccione:

Utilizar solución de Solver y elija los 3 informes. Para esto, seleccione el primero y mantenga apretada la tecla del mouse, hasta seleccionar los 3, o ubíquese en el primero y mantenga apretada la tecla Alt.Después de unos segundos, Solver habrá agregado 3 hojas de cálculo en su libro, una por cada informe. Éstos son: El Informe de Respuestas, el Informe de Sensibilidad y el Informe de Límites.

LOS DATOS DE LA PLANILLA

Si no ha cometido errores, Solver ha encontrado los valores óptimos de las variables controlables, y, por tanto, en las celdas B5 y C5 se muestra la solución óptima: Producir 4.5 toneladas de E y 7 de F. La utilidad máxima del mes próximo será $50500.

INFORME DE RESPUESTASLos informes de Solver son claros. La razón principal de su claridad se debe a que bajo cada columna Nombre, pone la intersección de fila y columna de rótulos. Así, por ejemplo, observe que en Celdas Cambiantes, debajo de Nombre, el informe puso: Producción: E; "Producción" es el rótulo de la fila y "E" el de la columna de la planilla.

En Celda Objetivo aparece la celda de la función objetivo, el Nombre, el valor inicial antes de optimizar y el valor óptimo (valor final).En Celdas Cambiantes aparecen las celdas de las variables controlables, el nombre, la solución inicial o valores iniciales de las variables y la solución óptima (valor final).En Restricciones se tiene:Valor de la celda: es el valor que toma el lado izquierdo de cada restricción en la solución óptima. Así, por ejemplo, en la primera restricción, de horas del departamento A, se tiene, al remplazar: 10*E+15*F = 10*4.5 + 15*7 = 150 horas utilizadas en el departamento A.Fórmula: nos recuerda las restricciones que hemos introducido, incluyendo si es de , = o .Estado: Nos indica si la restricción se cumple exactamente, con una igualdad, y no hay un margen. En otras palabras, nos indica si la restricción es activa.Divergencia: es el margen que tiene cada restricción. Si la desigualdad es , entonces es el lado derecho de la restricción (la constante) menos el lado izquierdo. Si la desigualdad es , es el lado izquierdo menos el lado derecho (la constante). Si la restricción es activa, desde luego el margen será cero.

En una pastelería se hacen dos tipos de tartas: Vienesa y Real. Cada tarta Vienesa necesita un cuarto de relleno por cada Kg. de bizcocho y produce un beneficio de 250 Pts, mientras que una tarta Real necesita medio Kg. de relleno por cada Kg. de bizcocho y produce 400 Ptas. de beneficio. En la pastelería se pueden hacer diariamente hasta 150 Kg. de bizcocho y 50 Kg. de relleno, aunque por problemas de maquinaria no pueden hacer mas de 125 tartas de cada tipo. ¿Cuántas tartas Vienesas y cuantas Reales deben vender al día para que sea máximo el beneficio? SoluciónEn primer lugar hacemos una tabla para organizar los datos:

Tipo Nº Bizcocho Relleno Beneficio

T. Vienesa x 1.x 0,250x 250x

T. Real y 1.y 0,500y 400y 150 50

Función objetivo (hay que obtener su máximo):  f(x, y)=250x+ 400y Sujeta a las siguientes condiciones (restricciones del problema): 

Función objetivo (hay que obtener su máximo): f(x, y)=250x+ 400y Sujeta a las siguientes condiciones (restricciones del problema):

Para recorrer un determinado trayecto, una compañía aérea desea ofertar, a lo sumo, 5000 plazas de dos tipos: T(turista) y P(primera). La ganancia correspondiente a cada plaza de tipo T es de 30 euros, mientras que la ganancia del tipo P es de 40 euros.El número de plazas tipo T no puede exceder de 4500 y el del tipo P, debe ser, como máximo, la tercera parte de las del tipo T que se oferten.Calcular cuántas tienen que ofertarse de cada clase para que las ganancias sean máximas.Solución

  nº GananciaTurista x 30xPrimera y 40y

Total 5000 30x +40y

La función objetivo es: f(x, y)=30x +40y

ANALISIS Y SI

Existe una herramienta no muy conocida en Excel que se llama “Análisis y si”, presentada de esta manera no dice mucho pero paro si la llamamos “¿Que pasaría… si…?” me parece que aclara mas las cosas pues nos induce a pensar en muchas cosas que nos podemos preguntar en nuestra vida cotidiana como: ¿Que pasaría si el dólar aumenta? o ¿Que pasaría en mi presupuesto familiar si disminuyo los gastos de diversión? o ¿Que pasaría con la cuota que tengo que pagar al banco si este me disminuye el interés en un punto? etc. Excel nos puede ayudar a responder a estas preguntas y a otras mucho mas complejas , pues considera los “gastos de diversión”, “la cotización del dólar” y “el interés en un préstamo” como variables y responde con un informe de acuerdo al cambio que se produzca en estas.

ANALISIS Y SISupongamos que todos los meses hacemos un presupuesto familiar y queremos saber que variables debemos ajustar para el porcentaje remanente sea de un 20% sobre los ingresos, para esto hacemos un modelo de gastos familiares

como se puede ver en este primer "escenario" tenemos un remanente de un 70% , por lo que podemos gastar mas. Supongamos que combinamos los datos de la siguiente manera (teniendo en cuenta que hay gastos que no se pueden alterar, como la hipoteca y los impuestos).

ANALISIS Y SI

O sea que tenemos 4 escenarios o dicho de otra manera, ¿Que pasaría con mi remanente si mi combinación de variables fueran esta?. Para que Excel nos haga un informe, debemos introducir las variables de los distintos escenarios.Como ya mencionamos la herramienta "Analisis y si" es la adecuada. Para acceder a ella , vamos a la pestaña Datos y de allí al panel "Herramientas de datos" , pulsamos en el ícono

ANALISIS Y SIluego aparece un menu desplegable en el que pulsamos en "administrador de escenarios"

Aparece el panel "Administración de escenarios", donde podremos agregar, eliminar modificar, combinar escenarios y finalmente obtener un resumen con todas las variables y como afectan el resultado final, que en nuestro caso es el porcentaje remanente sobre los ingresos. En este panel no se ha definido ningún escenario aun.

Vamos a introducir el Escenario1 pulsando en agregar, en el panel que aparece, le ponemos el nombre, establecemos las celdas cambiantes y finalmente podemos hacer un comentario

ANALISIS Y SIluego de aceptar , introducimos los datos que han cambiado en "Valores del escenario"( los que están con un cuadro rojo)

Finalmente al aceptar se presenta nuevamente el panel "Administración de escenarios" pero esta vez con el Escenario1 definido

Este mismo proceso se repite hasta que terminemos de ingresar los datos de todos los escenariosFinalmente el "Administrador de escenarios" queda como se muestra,

ANALISIS Y SIEs aquí cuando podemos apretar el botón Mostrar. Esto hará que los resultados en el modelo cambien de acuerdo al escenario elegido. Por ejemplo, si elegimos el Escenario3 y apretamos Mostrar, veremos en la hoja que el porcentaje remanente respecto de los ingresos disminuye en a un 36%. Así podemos elegir cada vez un escenario distinto y ver el resultado directamente en la hoja, por ejemplo el Escenario4 nos da justo un remanente del 20%.Sin embargo la verdadera utilidad del "Análisis y si", es cuando apretamos el botón "Resumen", al hacer esto se presenta la siguiente pantalla

Donde en "Celdas de resultado" deberemos poner la o las celdas donde queremos que aparezcan los resultados, en nuestro modelo es la celda D15.Al aceptar se crea automáticamente una nueva hoja rotulada "Resumen de escenarios" donde podemos ver una tabla que compara los valores de los escenarios con los valores que tiene nuestro modelo al momento de hacer el resumen (en este caso particular es el escenario4)

ANALISIS Y SI

Observar que si se oprime el signo + señalado por la flecha, aparecen los comentarios, esto es lo que se llama Agrupación y Esquema para ocultar ciertas filas. .Para que la tabla quede mas clara, es conveniente ponerles nombre tanto a las celdas cambiantes como a las cedas de resultado..Una forma de dar nombres a muchas celdas en forma simultánea es, en nuestro modelo, seleccionar el rango :A4:B12 y a continuación ir a la pestaña "Fórmulas" y en el panel "Nombres definidos" pulsar en "Crear desde selección"

luego aparece el panel "Crear nombres a partir de selección"

En el que marcamos "columna izquierda", el nombre de la celda de resultados se da como en el tutorial PONER NOMBRES A RANGOSFinalmente la tabla queda como se muestra

Supongamos una empresa cuyos ingresos son en euros (todas sus ventas son en Europa), los materiales son adquiridos en dólares y el resto de los ingresos y gastos son en pesos. El cuadro de pérdidas y ganancias de semejante empresa será afectado por los cambios en las cotizaciones de las monedas, aún si no hay cambios en la actividad real de la empresa.

Queremos investigar cuáles serán los resultados del año para este empresa de acuerdo a distintos escenarios para las tasa de cambio. El modelo que construimos será éste:

En las celdas B2 y B3 tenemos las tasas de cambio actuales del euro y el dólar. Estas celdas serán las variables en nuestro modelo.En el cuadro de pérdidas y ganancias proyectado tenemos los montos en la moneda original en la columna C y los montos convertidos a pesos en la columna D.

Con las tasas de cambio actuales, predecimos un porcentaje de beneficio bruto del 42%. ¿Que pasaría si esta tasa cambiaran? Supongamos tres posibilidades (escenarios) posibles:

1 - euro = 4.50; dólar = 3.302 - euro = 4.10; dólar = 3.103 - euro = 4.10; dólar = 3.30

Para crear los escenarios con la herramientas abrimos el menú Herramientas-Escenarios

Apretamos el botón Crear e ingresamos los datos. Al primer escenario lo llamaremos Devaluación, al segundo Revaluación y al tercero Mixto.

Al apretar el botón Aceptar se abre este formulario donde ingresamos los valores de las variables correspondientes al escenario

Apretamos aceptar y en el formulario que se abre volvemos a apretar Agregar para insertar el próximo escenario (Devaluación). Volvemos a realizar el proceso para agregar Mixto.

Cuando terminamos de ingresar los datos de todos los escenarios podemos apretar el botón Mostrar. Esto hará que los resultados en el modelo cambien de acuerdo al escenario elegido. Por ejemplo, si elegimos el escenario Revaluación y apretamos Mostrar, veremos en la hoja que el porcentaje de la ganancia bruto cae al 39%. Así podemos elegir cada vez un escenario distinto y ver el resultado directamente en la hoja.Hay que tener en cuenta que al cerrar el formulario, las celdas variables conservan el valor del último escenario elegido.Pero la real utilidad de usar Escenarios se evidencia al apretar el botón Resumen. Se abre este formulario

En la ventanilla Celdas de Resultado ponemos las celdas que queremos que aparezcan en el resumen. Si queremos que aparezca todo el total de ingresos, el total de gastos y el beneficio bruto, elegimos las celdas D10, D16 y D17

Al apretar Aceptar Excel crea una nueva hoja donde podemos ver un cuadro que compara los tres escenarios con la situación actual

Uno de los inconvenientes es que Excel usa la dirección de las celdas como nombre de la fila, lo que hace que la tabla sea prácticamente ilegible, es especial si se trata de un modelo con muchas líneas.La técnica para superar este inconveniente es poner nombres a las celdas que estamos interesados en mostrar

Beneficio_bruto = Hoja1!$D$17Dólar = Hoja1!$B$3Euro = Hoja1!$B$2Total_de_Gastos = Hoja1!$D$16Total_de_Ingresos = Hoja1!$D$10

ANALISIS DE DATOS EN EXCEL

Nos introduciremos hoy en el mundo del análisis de datos utilizando la Estadística descriptiva, es decir, empleando una parte de la estadística que se dedica a analizar y representar los datos.

Con esta herramienta, básicamente, realizaremos un estudio calculando una serie de medidas de tendencia central, para ver en qué medida los datos se agrupan o dispersan en torno a un valor central. Hablamos de conceptos tales como la Media, Moda, Mediana, Error típico, Desviación Estándar, etc.

ANALISIS DE DATOS EN EXCELPara comenzar a trabajar necesitamos en primer lugar una muestra de datos, por ejemplo, una muestra de 30 empleados de una empresa con un total de 200 trabajadores, de los cuales se disponen de datos de sexo y edad:

Una vez disponemos de los datos viene el estudio, si bien podríamos ir calculando con distintas funciones de Excel cada uno de lo estadísticos, optaremos por utilizar el Análisis de datos, y en concreto la opción de Estadística descriptiva; para la versión Excel 2003, desde el Menú Herramientas> Análisis de datos... para la versión Excel 2007, desde el menú Datos > Análisis (cuadro de opciones)>Análisis de datos:

ANALISIS DE DATOS EN EXCEL

• Rango de entrada: será la referencia de celda correspondiente al rango de datos que deseemos analizar. Al menos contendrá dos o más rangos adyacentes organizados en columnas o filas.

• Agrupado por: Seleccionaremos 'Filas' o 'Columnas' para indicar si los datos del rango de entrada están organizados en filas o en columnas.

• Rótulos en la primera fila y rótulos en la primera columna: Si la primera fila del rango de entrada contiene rótulos, activamos la casilla de verificación Rótulos en la primera fila. Si los rótulos están en la primera columna del rango de entrada, la casilla de verificación Rótulos en la primera columna.

• Rango de salida: Seleccionamos la referencia correspondiente a la celda superior izquierda de la tabla de resultados. Esta herramienta genera dos columnas de información por cada conjunto de datos. La columna de la izquierda contiene los rótulos de estadística y la columna de la derecha contiene las estadísticas. Excel escribirá una tabla de estadísticas de dos columnas por cada columna o fila del rango de entrada, dependiendo de la opción que se haya seleccionado en el cuadro 'Agrupado por'.

• .

• En una hoja nueva: Si queremos insertar una Hoja nueva en el libro actual y pegar los resultados comenzando por la celda A1 de la nueva hoja de cálculo. tenemos la posibilidad de darle un nombre personalizado a esta nueva hoja de cálculo

• En un libro nuevo: Lo mismo que el punto anterior, pero para crear un nuevo libro y pegar los resultados en una hoja nueva del libro creado.

• Resumen de estadísticas: Quizá el más interesante de todos los parámetros necesarios para configurar esta herramienta; cuando deseemos que Excel genere un campo en la tabla de resultados por cada una de las siguientes variables estadísticas: Media, Error típico (de la media), Mediana, Moda, Desviación estándar, Varianza, Curtosis, Coeficiente de asimetría, Rango, Mínimo, Máximo, Suma, Cuenta, Mayor (#), Menor (#) y Nivel de confianza.

• Nivel de confianza para la media: Si queremos incluir una fila correspondiente al nivel de confianza de la media en la tabla de resultados. Por ejemplo, un valor de 95 % calculará el nivel de confianza de la media con un nivel de importancia del 5 %.

• Késimo mayor: Cuando necesitemos incluir una fila correspondiente al valor k-ésimo mayor de cada rango de datos en la tabla de resultados. Si, por ejemplo, escribimos 1, esta fila contendrá el máximo del conjunto de datos.

• Késimo menor: Cuando necesitemos incluir una fila correspondiente al valor k-ésimo menor de cada rango de datos en la tabla de resultados. Si escribimos 1, esta fila contendrá el mínimo del conjunto de datos.

ANALISIS DE DATOS EN EXCEL

Aplicamos entonces la herramienta a nuestra muestra de empleados:

ANALISIS DE DATOS EN EXCEL

Vemos que como rango de entrada hemos seleccionado sólo las columnas con los datos del estudio. Tras Aceptar Excel nos devuelve el siguiente cuadro:

Tan sólo nos queda entonces interpretar los datos extraídos, pero esa labor es más de estadísticos de profesión...Como siempre Excel es sólo nuestra herramienta, no nos será útil si no sabemos interpretar los resultados.