jueves, 7 de noviembre de 2019
Factura
lunes, 21 de octubre de 2019
Escenarios.
Escenarios
Los escenarios son parte de una serie de comandos a veces denominados
herramientas de análisis Y si. Un escenario es un conjunto de valores que Microsoft Excel guarda y puede
sustituir automáticamente en la hoja de cálculo. Puede utilizar los escenarios
para prever el resultado de un modelo de hoja de cálculo. Puede crear y guardar
diferentes grupos de valores en una hoja de cálculo y, a continuación, pasar a
cualquiera de estos nuevos escenarios para ver distintos resultados.
Crear escenarios
Supongamos, por ejemplo, que desea preparar un presupuesto, pero no sabe con
exactitud sus ingresos. En este caso, podrá definir valores diferentes para
dichos ingresos y, seguidamente, pasar de un escenario a otro para realizar un
análisis Y si.
En el ejemplo anterior, cuyo escenario podría denominarse Peor opción,
establezca el valor de la celda B1 en 50.000 $ y el de la celda B2 en 13.200 $.
Al segundo escenario podría denominarlo Mejor opción y cambiar los valores de B1 a 150.000 $ y de B2 a 26.000 $.
Informes de resumen de escenario
Para comparar varios escenarios, puede crear un informe que los resuma en la
misma página. El informe puede listar los escenarios unos junto a otros o
resumirlos en un informe de tabla dinámica.
Los escenarios nos permiten analizar un resultado
generado en base a un conjunto de celdas variables. Los escenarios
en Excel permiten
un máximo de 32 variables, pero podemos crear tantos escenarios como sea
necesario.
Para este ejemplo utilizaré el caso de un préstamo personal en
donde me interesa conocer la cantidad que debo pagar dependiendo el plazo
elegido. Para ello utilizo la función PAGO y en las celdas superiores he
colocado los argumentos de dicha función de la siguiente manera:
El Administrador
de escenarios en Excel
Ahora me interesa saber cómo cambia la mensualidad si cambio el
plazo de pago. Para iniciar con la creación de escenarios debo pulsar el botón Análisis
Y si que se
encuentra en la ficha Datos y dentro de las opciones mostradas seleccionar Administrador de escenarios.
Al seleccionar esta opción se mostrará el cuadro de diálogo Administrador
de escenarios y
lo primero que debemos hacer es pulsar el botón Agregar para mostrar el cuadro
de diálogo Agregar escenario.
En este cuadro de diálogo comenzaré por asignar un nombre a mi
escenario. Ya que solamente modificaré el plazo del préstamo le pondré como
nombre 12 Meses. En el cuadro de
texto Celdas cambiantes debo seleccionar aquellas celdas que
afectan el resultado de la fórmula PAGO, que en este ejemplo es el rango
$B$1:$B:$3.
Al oprimir el botón Aceptar se mostrará un nuevo cuadro de
diálogo llamado Valores del escenario.
Aquí podré especificar los valores específicos del escenario recién creado. Es
posible cambiar todos los valores, pero para este ejemplo solamente modificaré
el plazo el cual especificaré como 12:
Ya que voy a agregar escenarios adicionales oprimiré el botón
Agregar y Excel me permitirá regresar al cuadro de diálogo Agregar escenario. De
esta misma manera crearé diferentes escenarios para los plazos de 18, 36 y 48
meses. Una vez que he terminado de crear el último escenario y especificar sus
argumentos pulsaré el botón Aceptar para regresar al Administrador de escenarios:
Probar los
escenarios creados
Ya con los escenarios creados será suficiente seleccionar alguno
de ellos y oprimir el botón Mostrar para aplicar los valores del escenario
al resultado de la fórmula PAGO. Observa cómo va cambiando el resultado de la
celda B4 de acuerdo al escenario elegido:
Para este ejemplo solamente he modificado los valores del plazo
para cada escenario, sin embargo se podrían crear escenarios donde cambie tanto
el plazo como la tasa de interés. Las combinaciones posibles son muchas y
solamente estarán restringidas a tus necesidades de análisis.
jueves, 17 de octubre de 2019
Formulario
Formularios en
Excel
Los formularios en Excel son un método para ingresar datos a
nuestras hojas y son de mucha utilidad porque nos ayudan a evitar errores en la
captura de información. Podemos comparar los formularios de Excel con los
formularios impresos en papel.
¿Qué es un formulario en Excel?
Seguramente has llenado un formulario en papel, tal vez cuando has acudido
a alguna oficina de gobierno para realizar un trámite, así que sabes de lo que
estoy hablando. Estos formularios contienen instrucciones precisas de lo que
debes escribir y contienen los recuadros necesarios para ingresar los
datos.
Los formularios en Excel no son tan diferentes de los formularios
impresos ya que de igual manera proveen de los espacios necesarios para
ingresar los datos. Los formularios en Excel utilizan objetos especiales
conocidos como controles de formulario que son precisamente los que nos
permiten agregar campos de texto, listas, botones de opción entre otras cosas
más.
Tipos de formularios en Excel
Cuando hablamos de formularios en Excel, es necesario recordar que podemos
crear tres tipos diferentes de formularios:
- Formulario de datos.
- Hojas de cálculo con controles
de formulario o controles ActiveX.
- Formularios de usuario en VBA.
A continuación describiré brevemente cada uno de ellos.
Formulario de datos
A través de un formulario de datos podemos mostrar al usuario la
información de una sola fila de una tabla. En este formulario se puede
hacer la edición de la información e inclusive crear un nuevo registro para la
tabla.
Excel puede generar automáticamente un formulario de datos para
cualquier tabla. Un detalle importante que debes saber sobre los formularios
de datos es que si una celda contiene una fórmula no podrás modificar dicha
fórmula mediante el formulario sino que solamente se mostrará el resultado del
cálculo sin poder editarlo.
Cómo crear una tabla en Excel
El objetivo de una tabla de Excel es
almacenar la información de una manera consistente para que sea más fácil darle
formato, ordenarla y filtrarla. Tú puedes crear una tabla en Excel que te ayude
a analizar mejor la información.
Cómo crear una tabla en Excel
Para crear una tabla en Excel, lo
primero que debes hacer es ingresar los títulos de columna. Así que haz clic en
la celda en blanco en donde deseas introducir la nueva tabla e ingresa los
títulos de columna como pueden ser: No. de empleado, Departamente, Nombre,
Apellido, etc. Los títulos de columna deben aparecer siempre en una sola fila y
sin celdas vacías entre ellos.
Ahora puedes comenzar a ingresar los datos
justo por debajo de los títulos de columna que acabas de crear:
Ahora asegúrate de que la celda activa sea
cualquier de las celdas que contienen tus datos y haz clic en el comando Tabla
que se encuentra en el grupo Tablas de la ficha Insertar.
Excel detectará el conjunto de celdas que
acabas de introducir y colocará un borde punteado sobre los datos, al mismo
tiempo que muestra el cuadro de diálogo Crear tabla, el cual
automáticamente mostrará el rango de celdas donde se encuentra la información.
Si por alguna razón el rango de celdas desplegado es incorrecto, utiliza el
botón de selección para elegir el rango de celdas adecuado.
Tabla con encabezados
Antes de hacer clic en el botón Aceptar
asegúrate de tener seleccionada la opción La tabla tiene encabezados.
Finalmente haz clic en Aceptar y Excel dará formato a la tabla e insertará
filtros para cada una de las columnas.
Otra forma de insertar una tabla es haciendo
clic en el comando Dar formato como tabla que se encuentra en el grupo
Estilos de la ficha Inicio. Con este comando puedes seleccionar desde un inicio
el estilo que prefieres dar a la tabla.
Agregar filas a una tabla de Excel
Después de que has creado una tabla, se pueden pensar en
varios métodos para agregar nuevas filas a una tabla de Excel. La forma
más directa de agregar una nueva fila es seleccionando la última celda de la
tabla (celda inferior derecha) y oprimir la tecla Tabulador.
Al hacer esto, Excel añadirá una nueva fila a
la tabla en donde podremos introducir la información apropiada para el nuevo
registro:
Observa cómo Excel coloca un borde sólido
alrededor de las celdas que delimitan la nueva fila de la tabla.
Agregar filas a una tabla por Formulario
Existe otra forma de agregar filas a una
tabla de Excel y es utilizando un formulario de captura de datos. El
comando Formulario no está incluido de manera predeterminada en la cinta de
opciones por lo que será necesario agregarlo manualmente. Haz clic en la flecha
que se encuentra al extremo derecho de la barra de acceso rápido y selecciona
la opción Más comandos.
Se mostrará el cuadro de diálogo Opciones de
Excel. En la lista comandos disponibles selecciona la opción Todos los
comandos y dentro de la lista encuentra el comando Formulario.
Haz cic en el botón Agregar y finalmente en el
botón Aceptar para que el botón se muestre en la barra de acceso rápido.
Ahora selecciona cualquiera de las celdas de
la tabla y oprime el botón Formulario. Aparecerá un cuadro de diálogo con los
nombres de las columnas de la tabla del lado izquierdo y con los valores para
cada fila en los cuadros de texto junto a ellos.
Para agregar un nuevo registro debes hacer
clic sobre el botón Nuevo.
Introduce la información del nuevo registro de
la tabla y al finalizar oprime el botón Cerrar y Excel colocará el nuevo
registro al final de la tabla.
Hoja de cálculo con controles de formulario
Ya que las celdas de una hoja de Excel nos
sirven para introducir información, podemos pensar en una hoja como un gran formulario.
De esta manera, si agregamos controles de formulario a nuestra hoja podremos
crear formularios de entrada de datos muy útiles. Por ejemplo, podemos
agregar a nuestra hoja un control de cuadro de lista para facilitar la
selección de los elementos.
De igual manera podemos agregar botones,
cuadros combinados, casillas de verificación y otros controles más que nos
ayudarán a crear formularios avanzados.
Controles de formulario en Excel
Los controles de formulario en Excel
son objetos que podemos colocar dentro de una hoja de nuestro libro, o dentro
de un formulario de usuario en VBA, y nos darán funcionalidad adicional para
interactuar mejor con los usuarios y tener un mejor control sobre la
información.
Podemos utilizar estos controles para ayudar a
los usuarios a seleccionar elementos de una lista predefinida o permitir que el
usuario inicie una macro con tan solo pulsar un botón. Los controles de
formulario en Excel se encuentran dentro de la ficha Programador dentro del
grupo Controles. Solamente pulsa el botón Insertar y observarás cada uno de
ellos:
Justo por debajo de los controles de
formulario podrás observar el grupo de controles ActiveX pero sus
diferencias y similitudes las discutiremos en otro artículo. Por ahora nos
enfocaremos solamente en los controles de formulario.
¿Cómo insertar un control de formulario en
Excel?
Para insertar cualquiera de los controles de
formulario debes seleccionarlo del menú desplegable y hacer clic sobre la hoja
de Excel arrastrando el borde para “dibujar” el contorno del control. Observa
este procedimiento.
Los diferentes controles de formulario
Existen diferentes tipos de controles de
formulario en Excel que ofrecen diversos tipos de funcionalidad e
interacción con el usuario. Desde una simple etiqueta hasta controles que
permiten una selección múltiple de sus opciones. A continuación una breve
descripción de cada uno de ellos.
- Barra de desplazamiento. Al hacer clic en
las flechas se va desplazando la barra dentro de un intervalo predefinido.
- Botón. El botón nos permite ejecutar una
macro al momento de hacer clic sobre él.
- Botón de opción. Nos permite una única
selección dentro de un conjunto de opciones.
- Casilla de verificación. Permite la
selección o no selección de una opción.
- Control de número. Nos ayuda a
aumentar o disminuir un valor numérico.
- Cuadro combinado. Es una combinación de
un cuadro de texto con un cuadro de lista.
- Cuadro de grupo. Agrupa varios controles
dentro de un rectángulo.
- Cuadro de lista. Muestra una lista de
valores de los cuales podemos elegir una sola opción o múltiples
opciones de acuerdo a la configuración del control.
- Etiqueta. Permite especificar un texto o
breves instrucciones en el formulario.
Controles de formulario no disponibles en
Excel 2010
Los controles de formulario han estado
presentes por varias versiones de Excel, sin embargo a partir de Excel 2010
existen algunos controles que ya no pueden ser utilizados dentro de las hojas
como lo son el Campo de texto, el Cuadro combinado de lista y el Cuadro
combinado desplegable, sin embargo podremos alcanzar funcionalidad similar
utilizando controles ActiveX.
Grupos de botones de opción en Excel
Los botones de opción permiten a un usuario de
Excel hacer una selección dentro de una lista de posibilidades y en todo
momento tener un solo elemento seleccionado. Hoy mostraré cómo crear grupos
de botones de opción en Excel independientes a pesar de encontrarse en la
misma hoja.
Ya que podemos agregar botones de opción como
controles de formulario o como controles ActiveX, este artículo tratará ambas
posibilidades. Comenzaremos por la opción de controles de formulario.
Grupos de botones de opción
Supongamos que deseamos crear dos grupos de
botones de opción donde queremos que el usuario seleccione su género así como
su color favorito. Si pusiéramos todos los botones de opción en la misma
hoja Excel solo podríamos elegir una sola opción tanto para el género como para
el color:
De manera predeterminada podemos seleccionar
una sola opción entre todos los botones de opción ubicados en la misma hoja. La
solución para crear dos grupos de botones de opción es agregar el
control llamado Cuadro de grupo:
Debemos agregar un Cuadro de grupo para
cada conjunto de botones de opción que deseamos crear por lo que para nuestro
ejemplo agregaré dos controles de este tipo. Es muy importante que al dibujar
el Cuadro de grupo te asegures de abarcar el área completa de los botones
de opción que deseas asociar de manera que el grupo sea creado
correctamente. Como resultado tendremos dos grupos de botones de opción
totalmente independientes:
Si queremos conocer la opción seleccionada en
cada grupo es suficiente con hacer clic derecho sobre cualquier botón de
opción y seleccionar la opción Formato de control. En la sección
Control debemos colocar la dirección de la celda de nuestra hoja que mostrará
la opción seleccionada.
A partir de ese momento la celda B10 mostrará
la opción seleccionada dentro del primer grupo:
Podemos hacer lo mismo para el otro grupo de
manera que los botones de opción estén vinculados con la celda B11 y de
esta manera poder conocer en todo momento la opción de color seleccionada:
Agrupar botones de opción ActiveX
Los botones de opción ActiveX funcionan
de manera diferente a los controles de formulario que acabamos de utilizar. Si
revisas el botón Insertar de la ficha Programador te darás cuenta de que no
existe un control para agrupar controles.
La manera de agrupar los botones de opción
ActiveX es a través de una de sus propiedades. Una vez que hemos agregado los
controles necesarios a la hoja debes hacer clic derecho sobre cada uno de ellos
(en modo Diseño) y seleccionar la opción Propiedades. Se mostrará una ventana
con las propiedades del control y debemos utilizar la propiedad GroupName:
Puedes observar en la imagen que estoy
consultando las propiedades del botón de opción Femenino y estoy asignando el
nombre de grupo “Genero”. Debo hacer lo mismo con el botón de opción Masculino,
y una vez hecho, los dos botones de opción tendrán el mismo valor en su
propiedad GroupName. De esta manera tendremos dos grupos de botones de
opción ActiveX que funcionan de manera independiente dentro de la misma
hoja:
Para saber si un botón de opción está
seleccionado debemos consultar su propiedad Value y si tiene el valor
True quiere decir que dicho botón de opción está seleccionado. Por ejemplo,
agregaré un botón de comando a la hoja de Excel que tendrá el siguiente código:
|
1
2
3
4
5
6
7
8
9
|
Private Sub
CommandButton1_Click()
If
OptionButton1.Value = True Then
Range("B10").Value
= "Femenino"
Else
Range("B10").Value
= "Masculino"
End If
End Sub
|
Al seleccionar una opción del grupo Genero y
pulsar el botón de comando, la celda B10 mostrará el valor Femenino o Masculino
de acuerdo a la selección realizada.
De esta manera hemos aprendido a crear grupos
de botones de opción en Excel, ya sea con controles de formulario o
controles ActiveX, de manera que se comporten de manera independiente a pesar
de estar ubicados en la misma hoja.
Formulario de usuario en VBA
Los formularios de usuario en VBA,
también conocidos como UserForm, son cuadros de diálogo que hacen uso de
controles de formulario para solicitar información al usuario. Estos
formularios son creados desde el Editor de Visual Basic y administrados
desde código VBA.
Una vez que se ha creado el formulario de
usuario podemos arrastrar y soltar los controles que están disponibles
desde el Cuadro de herramientas:
Si quieres crear un formulario básico en VBA,
sigue los pasos descritos en el artículo Formularios en VBA.
Los diferentes tipos de formularios en
Excel nos evitarán dolores de cabeza ya que podremos tener cierto grado de
control sobre el ingreso de datos que otras personas realizan sobre nuestras
hojas de Excel.
Suscribirse a:
Entradas (Atom)