Formularios

DESCARGAR FORMULARIO LISTADO

Un control muy similar al Cuadro de lista es la Lista Desplegable, el cual básicamente cumple la misma función ya que nos presenta un listado de opciones de la cual podemos seleccionar la deseada. La diferencia entre el Cuadro de lista y la Lista desplegable es que el primero permite ver varias opciones a la vez desplazándonos con una barra vertical mientras que la Lista desplegable solo muestra una opción y para ver las demás debemos desplegar la lista (idéntico a la Validación de Datos).

Para utilizar este control, nos dirigimos a la ficha DESARROLLADOR, herramientas Controles seleccionamos la opción Insertar y luego Lista Desplegable:

Con esto se activa el cursor del mouse en modo edición para poder dibujar en la hoja Excel el Cuadro de lista:

Al igual que el Cuadro de Lista, debemos indicar a este control cuales son los datos que necesitamos nos presente para nuestro ejercicio, vamos a contar con un listado de los vendedores a los cuales se tienen asociado unos montos de ventas en la Hoja2:

Para que nuestro nos presente el listado de los vendedores damos clic derecho sobre el mismo y seleccionamos la última opción Formato de control; con esto se muestra la ventana de formato para configurar las opciones:

En la ficha Control tenemos 3 opciones:

Rango de entrada: En esta opción indicaremos el rango de celdas del libro de Excel donde están los datos que necesitamos se muestren en el control de lista desplegable. Así que damos clic en el botón de selección  y seleccionamos el rango del listado de vendedores que tenemos en la Hoja2:

Vincular con la celda: Con esta segunda opción, es donde debemos indicar en cual celda deseamos obtener el resultado de la interacción que realicemos con el control Lista Desplegable. La interacción en este control es simplemente seleccionar la opción deseada del listado que se presenta en la lista y el resultado de esta interacción es un valor numérico entero que va desde 1 hasta el número de opciones que se tiene en la lista, así, al seleccionar el primer valor de la lista, el resultado será 1, si seleccionamos la quinta opción de la lista, el resultado será 5 y así sucesivamente. Para indicar en cual celda deseamos obtener el resultado damos clic en el botón de selección de esta opción:

Y vamos a seleccionar la celda $A$5.

Líneas de unión verticales: Con esta opción podemos indicar el número de reglones que deseamos se desplieguen una vez demos clic en la flecha del control Lista Desplegable; por defecto el valor es de 8, es decir que se muestran 8 valores al desplegar la lista y para ver las demás opciones nos desplazamos por la barra vertical. Para nuestro ejercicio (y en general) vamos a dejar el valor por defecto, es decir 8

La configuración de estas opciones quedará de la siguiente manera:

Veamos entonces que al dar clic en la flecha desplegable del control, podemos ver que tenemos todo el listado de vendedores:

Ahora, al seleccionar cualquier vendedor, en la celda A5 veremos el número o posición en la cual se encuentra el vendedor seleccionado:

Vamos a darle un poco más de funcionalidad a la interacción con este control, diseñando un esquema en esta Hoja1 donde podamos ver las ventas de cada mes para el vendedor seleccionado en la lista desplegable:

Con el uso de la función INDICE podemos obtener el valor de las ventas de cada mes para el vendedor seleccionado:

Vamos a adicionar otro control de Lista Desplegable que nos ayude a comparar los resultados entre dos vendedores, así que solo copiamos y pegamos la Lista Desplegable que tenemos y la ubicamos debajo, donde solo debemos ajustar en el formato de este segundo control, la celda con la cual está vinculada ya que no puede ser la misma del primer control (A5), debe quedar vinculado esta segunda lista desplegable a la celda A6:

Por último, agregamos un gráfico sencillo de columnas que nos muestre los valores de los vendedores que seleccionamos y damos formato a la hoja:

PLANTILLA DE AYUDA

Buscar objetivo

¿En qué consiste Buscar objetivo en Excel?, pues en encontrar un resultado deseado mediante la modificación del valor de una determinada celda de la que va a depender el valor deseado.

Empezare con un ejemplo sencillo si bien te comento otros ejemplos en los que puedes usar esta herramienta en Excel:

· Imagina que quieres comprar un nuevo mobiliario para tu oficina que vale 1.200 dólares y lo vas a pagar a plazos, el numero de plazo de pago que estableces con el vendedor es de 24 meses, a un tipo de interés del 10% te sale una cuota a pagar de 55 dólares. Pero resulta que no puedes pagar esa cantidad mensual, solo puedes hacer frente al pago de 30 dólares al mes, esto alargara los pagos a realizar, con Buscar objetivo encontraras rápidamente la respuesta acerca de en cuantos plazos terminaras de pagar la compra.

· Otro ejemplo: Recibes un sueldo bruto al año de 20.000 dólares y el descuento para los impuestos es del 15% (obviando otro tipo de descuentos). Imagina que puedes pedir una reducción del impuesto para que aumente la cantidad liquida a percibir, si quisieras recibir 18.500 dólares líquidos al mes con Buscar Objetivo en Excel podrás calcular que tipo de retención se aplicaría.

Uso de Buscar Objetivo.

Ejemplo sencillo: Vendes una determinada mercancía que cuesta 20 dólares, la cantidad vendida en el último año ha sido de 500 unidades, con ello has tenido unas ventas de 10.000 dólares, que es el resultado de multiplicar 20x500=10.000.

Ahora quieres saber cuántas unidades has de vender para obtener unas ventas de 15.000 dólares.

Pasos:

· Abre una hoja de cálculo Excel en blanco.

· Escribe en la columna A lo siguiente:

En A1: Precio

En A2: Cantidad

En A3: Ventas

· Escribe en la columna B:

En B1: 20

En B2: 500

En B3: La formula B1xB2, te tiene que aparecer el resultado que es 10.000

· A continuación has de hacer clic en la pestaña Datos.

· Después en el grupo de trabajo llamado Herramientas de análisis, haz clic en el triangulo negro al lado de Análisis Y si.

· Se despliegan una serie de opciones, pulsa en Buscar objetivo.

· Se abre una ventana en la que vas a colocar los datos de la siguiente manera:

En Definir la celda: haz clic sobre la celda B3 que es la que queremos que se modifique y alcance la cifra de 15.000 dólares de ventas.

En Con el valor: Escribe 15.000.

En Para cambiar la celda: Haz clic en B2, que es donde está el dato sobre el número de unidades a vender.

Aceptar.

La ventana cambia de apariencia al igual que los datos de la hoja de cálculo ahora puedes comprobar que para obtener 15.000 dólares de ventas has de vender 750 unidades, manteniendo constante el precio en 20 dólares la unidad.

Ejercicio

Nos tocan 20 Millones de Euros en la lotería. La alegría fue tan grande que compramos una casa por valor de 1 Millón.

Hicimos una plantilla con Excel destinando el dinero del premio a varias cosas.

Al ir a cobrar el premio, comprobamos que nos han tocado solo 10.345.612 Euros. con lo que tenemos que redistribuir el dinero del premio.

Utilizando la herramienta de Búsqueda de Objetivo, tenemos que determinar que parte del premio de 10 millones podemos destinar ahora a juerga y ocio.

Escenarios

El "Administrador de escenarios" de Excel es una buena herramienta de análisis de datos con multiples aplicaciones en el ámbito de la Dirección Financiera o Comercial. Es aplicable a cuentas de resultados, presupuestos y en general, cualquier contexto en el que haya que operar con varios parámetros.

Para conocer su funcionamiento, vamos a descargar una plantilla sobre la que practicaremos:

DESCARGAR

Si vamos a la hoja "Escenarios", podemos ver una tabla similar a la de la imagen de abajo, con la cuenta de resultados previsional a 5 años (simplificada para el ejercicio) de una empresa. También podemos ver más arriba un cuadro con 3 parámetros supuestos referidos al crecimiento que se espera experimente la empresa y a la evolución del Índice de Precios al Consumo:

Las celdas del año 2 contienen fórmulas que calculan el crecimiento respecto de los valores de las celdas del año 1. Las celdas del año 3 contienen fórmulas que hacen lo propio respecto de los valores de las celdas del año 2..... y así sucesivamente. Por lo tanto las únicas celdas de la tabla de abajo que contienen valores (no fórmulas) son las del año 1.

Es una imagen muy frecuente en el departamento financiero de cualquier pyme o bien en el plan de viabilidad económica de cualquier emprendedor, pero imaginemos que el director financiero o el gerente de la empresa necesita valorar distintas situaciones que se pudieran dar debido a la coyuntura económica y/o a la propia empresa, variando los valores de algunos parámetros... ¿cómo se podría hacer esta simulación?.

Supongamos que las tablas que presentamos anteriormente conforman un "escenario moderado" y el gerente necesita estudiar otros posibles escenarios (uno optimista y otropesimista) para poder prever una toma de decisiones en todos los casos:

1) Hagamos click en la "Ficha Datos", después en el botón "Análisis Y si" (que hay dentro del grupo"Herramientas de datos") y después en "Administrador de escenarios...". Obtendremos el cuadro de diálogo:

... donde tendremos que agregar uno a uno, los "escenarios" que queremos estudiar, empezando por el  moderado (que es el que aparece de partida en las tablas) y terminando con el pesimista y optimista.

2) Hacemos click en "Agregar" e introducimos en el siguiente cuadro de diálogo que emerge, los siguientes datos y referencias:

 

En el "Nombre de escenario" podemos poner libremente un nombre con el que queremos identificar el escenario.

En "Celdas cambiantes", una vez borramos la referencia que aparece por defecto, presionamos CTRL y con el ratón seleccionamos el rango de celdas H3:H5 y, sin haber dejado de hacer presión sobre CTRL, selesccionamos el rango de celdas D9;D10. El resultado que obtenemos son 2 rangos separados por punto y coma (Excel pone automáticamente el signo punto y coma y los signos de $).

3) Aceptamos y obtenemos el siguiente cuadro:

4) Vemos que lo que aparece son los datos que había en las celdas seleccionadas y que deseamos queden memorizadas como "Escenario moderado". Así pues, no tenemos que cambiar los datos y damos a "Agregar", obteniendo de nuevo el cuadro de diálogo donde se definen los escenarios (el nombre y las celdas cambiantes).

5) Repetimos exactamente el paso 2), pero en el "Nombre de escenario" , ponemos "Escenario pesimista" y aceptamos (las celdas cambiantes las pone Excel ya por defecto esta vez. Obteniendo de nuevo el pequeño cuadro de diálogo "Valores de escenario".

6) Esta vez, cambiamos los valores que nos presenta por defecto Excel, por los valores del "Escenario pesimista" de nuestra tabla de supuestos. Es decir:

... y damos a "Agregar".

7) Repetimos los pasos 5) y 6), pero esta vez poniendo como nombre de escenario "Escenario optimista" y en el cuadro de diálogo "Valores del escenario" ponemos los valores del escenario optimista de nuestros supuestos. Es decir:

...y esta vez, puesto que no tenemos que agregar más escenarios,hacemos click en "Aceptar" y obtenemos:

Ahora ya tenemos todos los escenarios configurados, sólo nos queda seleccionar el deseado en el panel "Escenarios" y hacer click en el botón "Mostrar". Podremos ver cómo se introducen automáticamente en las 5 celdas los datos de nuestro supuesto y todo queda recalculado según el escenario elegido.

Debemos apreciar que cuando hacemos clik en "Mostrar" el cuadro de diálogo no desaparece. Esto es para que podamos cambiar entre escenarios más rápidamente y poder apreciar los cambios con más facilidad.

En el cuadro de diálogo anterior ("Administrador de escenarios"), vemos que existe un botón llamado "Resumen..." que al hacer click sobre él obtenemos el siguiente cuadro, donde debemos seleccionar en el campo "Celdas de resultado:", el rango de celdas que consideramos como resultados importantes para nosotros y cuyo resultado para cada escenario, nos gustaría saber (vemos abajo que se han seleccionado arbitrariamente como rango de celdas importantes, las que reflejan el beneficio en porcentaje respecto de las ventas):

Si hacemos click en "Aceptar", obtenemos un cuadro - resumen que es totalmente modificable (y no se actualiza al volver a cambiar los datos en la tabla sobre la que hemos aplicado los escenarios). Una vez cambiamos las celdas en las que obtenemos resultados irrelevantes para nosotros, podemos obtener un interesante resumen como este: