Agrupar controles botones de opción en hojas Excel.

En la entrada sobre Agregando controles en hojas de cálculo mostré como agregar controles, tal como botones de opción o barras de desplazamiento, en una hoja de Excel.

Últimamente he recibido varias consultas sobre como crear grupos de botones de opción.
Cuando agregamos varios botones de opción en una hoja, Excel acepta una sola celda vinculada para todos los controles. Es decir, Excel agrupa todos los botones de opción en un solo grupo, no importa dónde o como los ubiquemos. Inclusive si definimos para cada botón una celda vinculada distinta, veremos que en todos los controles aparece como celda vinculada la última que hemos definido.

La técnica para superar este inconveniente depende de qué tipo de control estemos usando. Excel permite agregar dos tipos de controles directamente en la hoja: los controles de la barra de Formularios y los del cuadro de controles (controles ActiveX).




Supongamos que queremos crear un formulario para definir nuevos clientes. En este formulario definimos el nombre del cliente, el tipo (mayorista o minorista) y el tipo de crédito (15, 30 o 45 días). Nuestro formulario se verá así


Si usamos los controles de la barra de formularios, el truco consiste en poner cada uno de los grupos de botones de opción dentro de controles "cuadro de grupo" distintos. Para crear el formulario seguimos estos pasos:

1 – Ponemos un fondo gris al rango B2:F20
2 – Seleccionamos la celda C4 y ponemos "Nombre"
3 – Combinamos y centramos el rango D4:E4, y le quitamos el fondo gris
4 – Abrimos la barra de formularios y arrastramos dos cuadros de grupo



5 - Dentro de uno de los cuadro de grupo ponemos los botones de tipo de cliente. En el otro ponemos los botones de tipo de crédito. Cambiamos los encabezamientos directamente en el control.
6 – Los botones de tipo de cliente están ligados a la celda H3; los de tipo de crédito a la celda H4.
7 – Agregamos un control "botón" que nos servirá para activar una macro que agregue el nuevo cliente a nuestra base de datos (la hoja "Clientes")

En la celda I3 ponemos la fórmula =ELEGIR(H3;"Mayorista";"Minorista"); en la celda I4 la fórmula =ELEGIR(H4;"15 días";"30 días";"45 días")

Si usamos los controles ActiveX de la barra Cuadro de Controles, el truco consiste en cambiar la definición del Groupname en la propiedades del botón y darle a los botones del grupo el mismo Groupname.



En nuestro ejemplo los botones de tipo de cliente tienen como Groupname "Tipo" y los del tipo de crédito "Crédito".
Otro punto importante es que a cada control le definimos una celda vinculada distinta, a diferencia de los controles de la barra de formularios.

Sobre cómo definir los distintos controles, pueden consultar mi nota mencionada al principio.

En general es más fácil trabajar con los controles de la barra de formularios que con los controles ActiveX de la barra de cuadro de controles. La contrapartida es que los controles ActiveX tienen más posibilidades y son más flexibles a nuestras necesidades.

La hoja con los ejemplos y las macros se pueden descargar aquí.

Promedio en Excel con más de una condición

Dos lectores me consultan sobre cómo calcular con Excel el promedio de una serie de valores, sujeto a más de una condición. Uno de ellos me pide que lo muestre sin utilizar funciones Base de Datos como BDPROMEDIO. Cosa entendible ya que el uso de estas funciones es un tanto engorroso.

La técnica que sugiero es la que ya había mostrado en mi nota Sumar y contar con varias condiciones en Excel:SUMAR.SI, CONTAR.SI y SUMAPRODUCTO.

Definimos un nombre que contenga una lista de valores de un rango en una hoja de cálculo. Llamamos a este nombre "valores". En una celda ponemos el valor que será la cota inferior (nombre = "cota_inferior) y en otra la cota superior ("cota_superior").




Para sumar todos los valores comprendidos entre la cota superior y la inferior usamos la fórmula

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior)*valores)

Para contar la cantidad de valores comprendidos entre las cotas usamos

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior))

Para calcular el promedio podemos dividir entre las celdas que contienen los resultados o combinar ambas fórmulas en una sola

=SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior)*valores)/SUMAPRODUCTO((valores<=cota_superior)*(valores>=cota_inferior))

Luego podemos usar Formato Condicional para señalar los valores de la lista usados en el cálculo



Nuestra hoja se vería así




El archivo con el ejemplo de promedio con más de una condición se puede descargar apretando el enlace.

Technorati Tags: MS Excel

Función números a letras en Excel en español – nueva versión

La función Excel Números a Letras en Español que publiqué en la última entrada sobre el tema, estaba orientada a usos financieros. Es decir, presuponía que siempre queríamos expresar números en forma literal acompañados por la correspondiente moneda.
Debido a esto todos los argumentos de la función eran obligatorios. Si no introducíamos algún valor para la variable "Sep" (separador de decimales) o para "Mon" (moneda), el resultado de la función era el error #VALOR!.

La versión que publico en esta entrada esta basada en la función que Bernie Deitrick presentó en uno de los foros Excel hace varios años atrás. Esta función es mucho más compacta y sus argumentos son opcionales, exceptuando el valor que se quiere convertir, por supuesto.

Para poder adaptar la función a nuestra querida lengua castellana he tenido que trabajar duro. Las cosas son más sencillas en ingles:

100 es one hundred en inglés pero sólo cien en castellana y no "uno cien"
500 es five hundred, respetando el patrón, pero en castellano diremos quinientos
1000 será one thousend, pero en castellano simplemente mil y no "uno mil"

Pero finalmente creo haber superado todas las dificultades, hasta que mis lectores empiecen a encontrar las fallas!

La función, que pueden descargar aquí, tiene cuatro argumentos:





N: el número que queremos convertir a letras (mandatario)

Ccy: el nombre de la moneda (opcional)

Cents: el nombre de las fracciones de la moneda (opcional)

Join: por defecto es "con", pero podemos sustituirla con "y" por ejemplo

Fraction: acepta dos valores FALSE (0) o TRUE (1). Cuando ponemos TRUE o 1 los centavos son expresados como fracción (67/100 centavos). Por defecto es FALSE




Technorati Tags: MS Excel