Excel viene provisto con un mecanismo para controlar el tipo de datos permitidos en un rango: Validación de datos. La herramienta controla el tipo de datos ingresado, es decir, constantes (números, fechas, texto), pero no evitará que el usuario ingrese una fórmula en el rango validado.
Este problema se me presentó cuando estaba desarrollando un formulario para ingresar datos de gastos
Este tipo de formularios basados en hojas de Excel requieren poco esfuerzo para construirlos. Los valores permitidos para cada uno de los campos están controlados con Validación de datos; los botones en la parte inferior permiten pasar los datos a otra hoja que hace las veces de base de datos; las filas y columnas innecesarias están simplemente ocultas.
El problema es que validación de datos no impide que una fórmula sea ingresada en las celdas de la columna Monto
Para evitar esto tenemos que usar un evento. En el módulo de la hoja correspondiente ponemos este código
PrivateSub Worksheet_Change(ByVal Target As Range)
If Union(Target, Range("rngMonto")).Address = Range("rngMonto").Address Then If Target.HasFormula Then
Target.ClearContents
MsgBox "Solo valores, no formulas!" EndIf EndIf
EndSub
Al introducir un valor en el rango Monto, se dispara el código que evalúa si se trata de una fórmula con la propiedad HasFormula. En caso positivo el contenido de la celda (target) es borrado y aparece el mensaje informando que no está permitido introducir fórmulas.
Este video muestra el funcionamiento de la validación
Una de las funcionalidades más populares de Excel es el Autofiltro. Con esta herramienta podemos filtrar una tabla de acuerdo criterios lógicos aplicados a los valores de una o más columnas de la tabla.
Un lector me consulta cómo se puede hacer para que el criterio aplicado aparezca en una celda por encima del encabezamiento de la columna. Por ejemplo, partiendo de esta lista
ver en las celdas correspondientes de fila 2 los criterios aplicados para filtrar la lista
Como puede verse, estamos usando una función definida por el usuario (UDF).
Esta función fue desarrollada por el guru de Excel Stephen Bullen.
Para poder usar esta función tenemos que poner este código en un módulo común del editor de Vba, preferentemente en el Personal.xlsb (Personal.xls en Excel 97-2003)
Function FilterCriteria(Rng As Range) AsString 'By Stephen Bullen Dim Filter AsString
Application.Volatile True
Filter = "" OnErrorGoTo Finish With Rng.Parent.AutoFilter If Intersect(Rng, .Range) IsNothingThenGoTo Finish With .Filters(Rng.Column - .Range.Column + 1) IfNot .On ThenGoTo Finish
Filter = .Criteria1 SelectCase .Operator Case xlAnd
Filter = Filter & " y " & .Criteria2 Case xlOr
Filter = Filter & " o " & .Criteria2 EndSelect EndWith EndWith
Finish:
FilterCriteria = Filter EndFunction
Esta función utiliza un único argumento, "Rng", que es cualquier celda de la columna sobre se aplica el Autofiltro.
Hay que tener en cuenta que esta función fue desarrollada antes de la aparición de Excel 2007. Por ejemplo, si elegimos más de dos criterios en una misma columna, por ejemplo ver las zonas Norte, Sur y Oeste, la función no podrá mostrar el criterio aplicado
Tip: en Excel 2007, al apuntar con el mouse al icono del autofiltro podemos ver una ventanilla que nos muestra los criterios aplicados (como en la imagen de arriba).
¿Cómo podemos hacer para mostrar u ocultar series de datos en un gráfico de Excel dinámicamente? Con controles de la barra de formularios, que son sencillos de usar y no requieren programación.
Por ejemplo, supongamos este gráfico
Nuestro objetivo es añadir un elemento que nos permita con un clic mostrar u ocultar cada una de las series.
Veamos los pasos a dar:
Agregamos tres casillas de verificación de la barra de formularios, una para cada serie
Reemplazamos el texto de cada casilla por el nombre de la serie.
Definimos la celda vinculada al control
En este caso he elegido una celda visible, para mostrar el funcionamiento del control. En una implementación real, usaremos una celda oculta.
Hacemos lo mismo con los restantes controles.
Cuando la casilla está activada, el valor de la celda vinculada pasa a ser VERDADERO; cuando no lo está el valor será FALSO. Vamos a aprovechar estos valores en nuestro mecanismo.
El próximo paso consiste en crear cuatro nombres definidos, uno para cada serie y uno para los valores de las categorías (el eje de las X, los meses en nuestro ejemplo).
Como puede verse, estos nombres contienen fórmulas condicionales. Si el valor de la casilla de verificación es VERDADERO, la condición se cumple y el rango es el definido en la tabla de datos; si el valor es FALSO, el rango elegido es F3:F14 que no contiene ningún valor (y por lo tanto no tiene representación en el gráfico).
Ahora tenemos que reemplazar los rangos relevantes en la función SERIES de cada una de las series con los nombres definidos. Esto puede hacerse de varias maneras, por ejemplo:
Seleccionamos la serie VENTAS para que en la barra de las fórmulas aparezca la fórmula SERIES
Reemplazamos los rangos relevantes por los nombres tomando en cuenta no eliminar el signo de admiración (!) que aparece a la izquierda del rango
Apretamos Enter. En lugar del nombre de la hoja aparecerá ahora el nombre del cuaderno
También podemos usar el formulario Modificar Serie de la opción Seleccionar datos
La ventaja de esta técnica es que podemos usar Pegar Nombres (F3) en lugar de tener que escribirlos manualmente. La otra ventaja es que podemos reemplazar el rango de la categorías en el formulario de Editar Rótulos de Eje, sin necesidad de tener que reemplazarlo en la fórmula SERIES de cada serie.
Finalmente, ocultamos las celdas vinculadas, agregamos un cuadro de grupo para agrupar los controles (sólo por cuestiones estéticas) y ya hemos completado nuestro modelo
