Validación de datos dependiente

Como es sabido, la validación de datos nos permite establecer reglas que determinan lo que puede y lo que no puede ser ingresado en una celda. Podemos especificar un mensaje de entrada y un mensaje de error (y el tipo de este mensaje, es decir, de información, de advertencia o de límite).

Un usuario me envía la siguiente pregunta: "... lo que necesito hacer es que de acuerdo a la selección que hagan de un campo, en el siguiente solo me den las opciones referentes a ese campo y no todas,... un ejemplo es cuando abres una cuenta de correo y te piden tu país, le das "México" y en el siguiente campo te aparecen solo los estados de México..."

Desde luego podemos intentar con un par de controles y un código VBA más o menos sencillo aunque, en realidad, es posible lograrlo sin necesidad de utilizar macros. Al igual que sucede con el formato condicional, al utilizar criterios personalizados (formulados), la validación de datos se vuelve una herramienta muy potente.

El primer paso es organizar nuestros datos. En la primera columna ponemos los valores independientes y, a la derecha, los dependientes. Es necesario que la lista esté ordenada por la primera columna. En otra columna, pongamos la D, escribimos una lista de los elemento únicos de la primer columna:

Por comodidad definimos los siguientes nombres:

La celda A1 con el nombre "inicio"; la columna A con el nombre "independiente"; la columna B como "dependiente" y la lista de la columna D como "lista". Opcionalmente, en otra celda en blanco, escribimos "Seleccione un valor en la columna A" y la definimos con el nombre "mensaje_error"

En otra hoja, creamos un tabla sencilla para crear las listas de validación:


Seleccionamos el rango A2:A10 y vamos a Datos - Validación... Como valor Permitir seleccionamos Lista. En el campo Origen escribimos:

=SI(O(B2="",B2="Seleccione un Ramo"), lista, INDICE(independiente, COINCIDIR(B2, dependiente, 0)))


Esto sirve para que, si no hay ningún valor en la columna B, o bien, el valor en la columna B sea "Seleccione un ramo", en la lista de la columna A aparezcan todos los valores. Por el contrario, si ya tenemos un valor establecido en la columna B (la dependiente), en la lista de validación de la columna A solo aparecerá su correspondiente valor, y no todos.

Solo nos falta crear la validación de la columna B. Seleccionamos el rango B2:B10, vamos a Datos - Validación... en Permitir seleccionamos Lista y, en Origen, escribimos la fórmula:

=SI(A2="",mensaje_error,DESREF(inicio,COINCIDIR(A2,independiente,0)-1,1,
CONTAR.SI(independiente,A2),1))

Con esta fórmula, si el usuario pretende seleccionar un valor en la columna B, sin haber seleccionado primero el correspondiente valor de la columna A, la lista solo mostrará la opción "seleccione un valor en la columna A".

De otra forma, la lista mostrará los valores adecuados.

Convertir números a letras

Algo que los usuarios preguntan con cierta frecuencia, es si Excel cuenta con alguna función que convierta un número (40), a su forma "verbal" o textual ("cuarenta"). Principalmente, para elaborar facturas.

Bien, la respuesta es no. Las única formas son utilizar una macro o descargar algún complemento que pueda hacerlo. Hay varios sitios que proveen dichas macros. La que utilizo es la siguiente:

Option Explicit

Dim cTexto As String 'Variable para las funciones

Public Function NumLetras(ByVal Numero As Double, ByVal Mayusculas As Integer) As String

Dim NumTmp As String
Dim c01 As Integer
Dim c02 As Integer
Dim pos As Integer
Dim dig As Integer
Dim cen As Integer
Dim dec As Integer
Dim uni As Integer
Dim letra1 As String
Dim letra2 As String
Dim letra3 As String
Dim Leyenda As String
Dim Leyenda1 As String
Dim TFNumero As String

If Numero < 0 Then numero = Abs(Numero)
numtmp =" Format(Numero,"00000000000000.00")
c01 = 1
pos = 1
tfnumero = ""
Do While C01 <=5
C02 = 1
Do While c02 <= 3 'Extrae un digito cada vez de izquierda a derecha
dig = Val(Mid(NumTmp, pos, 1))
Select Case c02
Case 1: cen = dig
Case 2: dec = dig
Case 3: uni = dig
End Select
c02 = c02 + 1
pos = pos + 1
Loop
letra3 = Centena(uni, dec, cen)
letra2 = Decena(uni, dec)
letra1 = Unidad(uni, dec)

Select Case c01
Case 1
If cen + dec + uni = 1 Then
Leyenda = "Billon "
ElseIf cen + dec + uni > 1 Then
Leyenda = "Billones "
End If
Case 2
If cen + dec + uni >= 1 And Val(Mid(NumTmp, 7, 3)) = 0 Then
Leyenda = "Mil Millones "
ElseIf cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 3
If cen + dec = 0 And uni = 1 Then
Leyenda = "Millon "
ElseIf cen > 0 Or dec > 0 Or uni > 1 Then
Leyenda = "Millones "
End If
Case 4
If cen + dec + uni >= 1 Then
Leyenda = "Mil "
End If
Case 5
If cen + dec + uni >= 1 Then
Leyenda = ""
End If
End Select

c01 = c01 + 1

TFNumero = TFNumero + letra3 + letra2 + letra1 + Leyenda

Leyenda = ""
letra1 = ""
letra2 = ""
letra3 = ""

Loop

If Val(NumTmp) = 0 Or Val(NumTmp) <1 Then
leyenda1 = "Cero Pesos "
ElseIf Val(NumTmp) = 1 Or Val(NumTmp) <2 Then
leyenda1 = "Peso "
ElseIf Val(NumTmp) = 0 Or Val(Mid(NumTmp, 10, 6)) = 0 Then
Leyenda1 = "de Pesos "
Else
Leyenda1 = "Pesos "
End If

TFNumero = TFNumero & Leyenda1 & Mid(NumTmp, 17) & "/100 M.N."

If Mayusculas = 1 Then
TFNumero = UCase(TFNumero)
Else
TFNumero = LCase(TFNumero)
End If

NumLetras = TFNumero

End Function


Private Function Centena(ByVal uni As Integer, ByVal dec As Integer, _
ByVal cen As Integer) As String

Select Case cen
Case 1
If dec + uni = 0 Then
cTexto = "cien "
Else
cTexto = "ciento "
End If
Case 2: cTexto = "doscientos "
Case 3: cTexto = "trescientos "
Case 4: cTexto = "cuatroscientos "
Case 5: cTexto = "quinientos "
Case 6: cTexto = "seiscientos "
Case 7: cTexto = "setescientos "
Case 8: cTexto = "ochoscientos "
Case 9: cTexto = "novescientos "
Case Else: cTexto = ""
End Select

Centena = cTexto
cTexto = ""

End Function


Private Function Decena(ByVal uni As Integer, ByVal dec As Integer) As String

Select Case dec
Case 1
Select Case uni
Case 0: cTexto = "diez "
Case 1: cTexto = "once "
Case 2: cTexto = "doce "
Case 3: cTexto = "trece "
Case 4: cTexto = "catorce "
Case 5: cTexto = "quince "
Case 6 To 9: cTexto = "dieci"
End Select
Case 2
If uni = 0 Then
cTexto = "veinte "
ElseIf uni > 0 Then
cTexto = "veinti"
End If
Case 3: cTexto = "treinta "
Case 4: cTexto = "cuarenta "
Case 5: cTexto = "cincuenta "
Case 6: cTexto = "sesenta "
Case 7: cTexto = "setenta "
Case 8: cTexto = "ochenta "
Case 9: cTexto = "noventa "
Case Else: cTexto = ""
End Select

If uni > 0 And dec > 2 Then cTexto = cTexto + "y "

Decena = cTexto
cTexto = ""

End Function


Private Function Unidad(ByVal uni As Integer, ByVal dec As Integer) As String

If dec <> 1 Then
Select Case uni
Case 1: cTexto = "un "
Case 2: cTexto = "dos "
Case 3: cTexto = "tres "
Case 4: cTexto = "cuatro "
Case 5: cTexto = "cinco "
End Select
End If

Select Case uni
Case 6: cTexto = "seis "
Case 7: cTexto = "siete "
Case 8: cTexto = "ocho "
Case 9: cTexto = "nueve "
End Select

Unidad = cTexto
cTexto = ""

End Function

En realidad, son cuatro funciones las que se utilizan para lograr el cometido.

Para usarla, simplemete escribimos en alguna celda:

=NUMLETRAS(A2,1)

Esto, claro, si hemos guardado la función en el mismo libro en el que la vamos a usar. Si la guardamos en el libro de macros personal (de forma que esté disponible en todos los libros), entonces tendremos que escribir:

=Personal.xls!NUMLETRAS(A2,1)

Para mayor seguridad, utilicen el asistente de funciones (el pequeño botón fx situado a la izquierda de la barra de fórmulas). La función estará en la categoría Definidas por el usuario.

Si queremos el resultado en minúsculas, escribimos 0 (pero no FALSO) como segundo argumento. Al final, la función agrega el texto " pesos 00/100 m.n.", el cual puede ser ajustado en el código.

La fórmula =NOMPROPIO(NUMLETRAS(1425300,1))devuelve:

Un Millon Cuatroscientos Veinticinco Mil Trescientos Pesos 00/100 M.N.

Generar un número aleatorio - Tip

Realizando una inspección de rutina de Excel, encuentro este tip en la ayuda de la función ALEATORIO:

"Si desea usar ALEATORIO para generar un número aleatorio pero no desea que los números cambien cada vez que se calcule la celda, puede escribir =ALEATORIO() en la barra de fórmulas y después presionar la tecla F9 para cambiar la fórmula a un número aleatorio."

Obviamente, después de presionar F9 hay que presionar Enter para aceptar el número.

Muy útil cuando estamos probando fórmulas.

Representar escalas en Excel

En el dibujo técnico o arquitectónico, es un "principio generalmente aceptado" el uso de los dos puntos (:) para especificar la escala en la que está hecho determinado dibujo. Por ejemplo, 1:125, lo cual quiere decir que una pulgada en el dibujo representa 125 pulgadas del modelo real.

¿Cómo representar esto en Excel? Supongamos la siguiente tabla de medidas originales y las utilizadas en el dibujo:

Primeramente, dividimos la cantidad final entre la original, resultando un número decimal, lo cual no nos sirve.

¿Cuestión de formato? Intentemos dar formato de fracciones. Seleccionamos el rango, Ctrl + 1, ficha Número, Fracciones. Seleccionamos la opción Hasta tres décimas. El resultado:

Se acerca, pero sigue sin ser lo que buscamos. Intenté elaborar algún formato personalizado y tampoco. No obtuve el resultado deseado.

La única vía que veo es la utilización de fórmulas, como la siguiente:

=B2/M.C.D(B2,A2) & ":" & A2/M.C.D(B2,A2)


M.C.D devuelve el máximo común divisor de dos hasta 29 números. Está disponible en el complemento Herramientas para análisis (en las versiones 2003 o anteriores; es función nativa en 2007).

La importancia de Excel en el mundo

Microsoft Office Excel (Excel) es la hoja de cálculo líder en el mercado. Es además, el software más potente, más flexible y más utilizado del mundo. Ningún otro programa puede competir con él en cuanto a funciones o flexibilidad. Su ámbito de aplicabilidad va de la economía a la sicología, de la biología al dibujo, de las matemáticas aplicadas a la administración de los recursos humanos.

En el mundo, miles de millones de dólares se mueven gracias a este programa. Miles de decisiones se toman apoyadas en en él. Millones de empresas de todo el mundo simplemente no podrían operar si no tuvieran Excel en sus equipos de cómputo. Gran parte de los programas a la medida o "independientes" que existen, en realidad utilizan a Excel como motor de cálculo. Casi todos muestran sus resultados en una hoja Excel. Cuando el usuario tiene un nivel avanzado del mismo, tareas que a un usuario con un nivel normal de Excel le tomaría varias horas terminar, es posible formularlas, optimizarlas y en el último de los casos, programarlas en lenguaje VBA (Visual Basic for Applications o, más exactamente, Visual Basic for Excel) de forma que puedan realizarse en unos pocos segundos. Al dominar plenamente la programación en Excel, el lenguaje VBA, es posible elaborar en minutos el trabajo que anteriormente llevaba días enteros.

Imaginemos, por un momento, que el día de mañana Excel desapareciera. Seguramente habría pérdidas económicas. La primera acción que nos vendría a la mente sería migrar a otra hoja de cálculo, pero ¿a cuál? ¿Lotus? ¿Multiplan? ¿habría suficientes copias para distribuir a todos los equipos del mundo? ¿entonces, Google Spreadsheets, on-line y por lo tanto más lenta...? Iniciaría una nueva guerra por establecer un nuevo estándar de hoja de cálculo, con los previsibles problemas de compatibilidad entre los usuarios. ¿Cuánto tiempo llevaría capacitar a los nuevos usuarios? ¿cuánto tiempo llevaría convertir los archivos al nuevo formato? ¿soportarían las macros Excel o los gráficos al menos, podrían interactuar con el resto de programas de oficina? probablemente no. ¿Cuánto tiempo llevaría integrar todos los programas que actualmente utilizan Excel como motor de cálculo al nuevo programa?

Pero no nos preocupemos, esto nunca va a ocurrir (espero). En cambio, si Lotus desapareciera, dudo mucho que hubiera algún efecto perceptible.