Rangos con Tablas en listas desplegables y comboboxes

No me avergüenzo de decir que soy un fanático de las Tablas. Una de las mejores herramientas de Excel, la mejor, tal vez, después de las tablas dinámicas, el Power Query y el PowerPivot.
Una de las mejores características de las tablas es que crean rangos dinámicos en todo objeto que dependa de ellas. Por ejemplo, si creamos un gráfico basado en una tabla cada cambio se reflejará automáticamente en el gráfico

Al crear una tabla Excel le asigna un nombre, por defecto Tabla1, que podemos cambiar para usar algo más significativo. Por ejemplo, rebautizamos a nuestra tabla de ventas con  "tblVentas"

También veremos que Excel la incluye en administrador de nombres como un nombre definido que se refiere al rango de la tabla

Esto nos lleva a concluir que podemos crear rangos dinámicos, como aquellos que usamos en listas desplegables, sin necesidad de echar mano a fórmulas con las funciones DESREF o INDICE. Pero para poder usar las tablas o las columnas de una tabla como rangos dinámicos tendremos primero que crear nombres definidos que se refieran a esos rangos.

A los efectos del ejemplo supongamos dos tablas de datos. Una contiene nombres de continentes y la otra contiene una columna por cada continente donde se encuentran los países del continente

A la tabla de los continentes le damos el nombre "Continente"; a la segunda tabla le damos el nombre "Paises". Para poder usar la columna de los continentes en una lista desplegable con validación de datos tenemos que crear un nombre definido que se refiera al rango de la columna

Hemos creado el nombre definido "lstContinente" que se refiere a la tabla Continente usando el lenguaje estructural de las tablas: =Continente[Continente] (en este caso el nombre de la tabla y el de la única columna coinciden).
Ahora para definir la lista desplegable con validación de datos en la celda B2 usamos el nombre definido "lstContinente"

Para crear la lista desplegable dependiente tendremos que referirnos a la columna de la tabla Paises que coincide con el continente elegido en B2. Para eso creamos el nombre definido "PaisSelec" que se refiere a esta fórmula

=INDIRECTO("Paises["&valdat!$B$2&"]")

donde "valdat" es el nombre de la hoja; es decir, creamos una cadena de texto con el operador & que la función INDIRECTO convierte en rango.

El archivo se puede descargar aquí.

Si queremos evitar los espacios en blanco al final de algunas de las listas (el rango se determina según el tamaño de la tabla, no de una columna en particular), tendremos que crear una Tabla para cada continente. En este caso sólo necesitamos crear el nombre definido que se refiere al rango de la tabla de continentes.

La lista desplegable en la celda B2 la creamos como en el caso anterior. Para la validación de datos en la celda B3 usamos la fórmula =INDIRECTO(B2).

El ejemplo puede descargarse aquí.

También podemos usar esta técnica para poblar comboboxes y listboxes. En este ejemplo creamos un Userform con dos combobox, una para los continentes y el segundo combobox para los países cuyos valores dependerán del continente elegido. Como base vamos a usar el modelo con tablas separadas por continentes.

Creamos el Userform y agregamos dos comboboxes. La lista de valores del primer combobox  (el que muestra los continentes) lo definimos directamente en el cuadro de propiedades del objeto

Como puede verse, sencillamente ponemos el nombre definido que se refiere a la tabla de continentes.
La lista de valores del segundo combobox debe depender del valor seleccionado en el combobox de continentes para lo cual debemos definir un evento Change del combo de continentes.
Hacemos un doble clic al combobox de los continentes lo que abre el módulo del userform y agrega, por defecto, el evento Change del objeto donde ponemos este código

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Ahora podemos probar el funcionamiento del Userform y las cos comboboxes seleccionando el Userform en el editor de VB y apretando F5



Ahora que vemos que nuestro código funciona vamos a mejorarlo agregando una línea para limpiar el valor del combobox de países si el usuario cambia el continente antes de cerrar el Userform

Private Sub cbxContinentes_Change()
    With Me
        .cbxPaises.Value = ""
        .cbxPaises.RowSource = .cbxContinentes.Value
    End With
End Sub

Descargar el archivo del ejemplo.

Tablas dinámicas - uso de Rangos de Consolidación Múltiples

A partir de la versión 2007 de Excel, la funcionalidad "Rangos de consolidación múltiples" desapareció del asistente de de tablas dinámicas:

Pero el "viejo" asistente no desapareció; se puede acceder a él agregando el icono a la barra de herramientas de acceso rápido

En esta nota voy a mostrar una solución posible a la consulta de un lector y al pasar algunas consideraciones sobre cuál es la mejor manera de organziar datos en Excel.

Empecemos por presentar el problema. Nuestro lector tiene esta matriz de datos

pero para poder consolidar datos y realizar cálculos, necesita organizar los datos de esta manera (más adelante veremos si realmente ésta es la mejor manera)

Si nuestra matriz contiene muchos datos la posibilidad de cortar y pegar o arrastrar los rangos puede ser poco práctica. En ese caso podemos echar mano a la funcionalidad Rangos de consolidación múltiples del viejo asistente de tablas dinámicas.

El primer paso es abrir el asistente y seleccionar los rangos a consolidar

Al apretar el botón "Finalizar" obtenemos la tabla

Ahora podemos seleccionar la tabla dinámica y usar Copiar-Pegar Valores para poder usarla como una matriz de datos regular.

Este video muestra el proceso


Los datos quedan organizados como un matriz de datos "plana". En lugar de la forma "plana" de la tabla dinámica, donde cada "Tag" es una columna, podemos organizar los datos en forma tabular con una única columna para los "Tags". Esto reduce el número de columnas de la tabla y hace que sea más fácil de manejar para nuestros cálculos.
Para convertir la tabla dinámica de rangos consolidados en una matriz de datos tabular hacemos un doble clic en el total general

Como resultado se abre una nueva hoja con la matriz de datos

Ahora podemos cambiar el encabezado de las columnas A, B y C y eliminar la columna D.

Longitud, Latitud y ángulos en Excel

Este post se originó en esta consulta:

En una celda tengo datos de latitud y en otra longitud, 194623 y 1012804, respectivamente. ¿Cómo puedo hacer para cada uno de los datos extraerlos a una celda diferente pero ya convertidos a grados, minutos y segundos, es decir 19°46'23'' y 101°28'04''.

Efectivamente, la longitud y la latitud se miden en grados, minutos y segundos, tal como los ángulos. Pero lo que busca nuestro lector no es convertir medidas entre distintos sistemas de unidades (la explicación va más adelante), ya que sus datos muestran las coordenadas pero sin los símbolos correspondientes.
Antes de entrar en el tema de los ángulos en Excel, vamos a resolver el problema de nuestro lector. La "conversión" puede verse en esta imagen

La fórmula en la celda B1 es:

=IZQUIERDA(A1,LARGO(A1)-4)&CARACTER(176)&EXTRAE(A1,LARGO(A1)-3,2)&CARACTER(39)&DERECHA(A1,2)&CARACTER(34)

Usamos el operador "&" para concatenar las distintas partes de la cadena de texto y usamos la función CARACTER para representar el símbolo de los grados, de los minutos y de los segundos. La función LARGO nos permite determinar cuantos dígitos conforman los grados (puede variar de 1 a tres) dinánicamente. La fórmula supone que siempre los minutos y los segundos tienen dos cifras.
El resultado de esta fórmula es un texto, por lo que si bien representa correctamente las medidas de la longitud y la latitud, no nos sirve para realizar cálculos.

Los ángulos, como las fechas y horas, pueden expresarse en el sistema decimal. Así como 20/07/2015  12:30:00 puede expresarse en forma decimal como 42205.52 donde la parte entera es la fecha y la parte decimal las horas, minutos y segundos, lo mismo podemos hacer con ángulos. Así si tenemos por ejemplo el ángulo de 19° 46' 23'' de nuestro ejemplo puede expresarse decimalmente con el número 19.773. Si tenemos el ángulo expresado en forma decimal podemos mostraralo en forma sesxagesimal usando la función TEXTO y dándole formato de hora, dividiendo los grados por 24, como en el sistema horario

Como puede verse, tampoco aquí se trata de una verdadera conversión sino de crear un texto que muestra el ángulo en el sistema sexagesimal.

Y ahora entraremos en el meollo de la cuestión. Si queremos hacer algún tipo de operación con ángulos tendremos que transformarlos a radianes ya que Excel usa el radián como unidad de medida para trabajar con ángulos. Convertir angulos en forma decimal a radianes es fácil, como mostraré más adelante. Pero no contamos con ninguna función o fórmula para convertir ángulos sexagesimales a decimales. Para hacerlo usamos esta función UDF publicada gentilmente por Microsoft

Function Convert_Decimal(Degree_Deg As String) As Double
   ' Declare the variables to be double precision floating-point.
   Dim degrees As Double
   Dim minutes As Double
   Dim seconds As Double
   ' Set degree to value before "°" of Argument Passed.
   degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
   ' Set minutes to the value between the "°" and the "'"
   ' of the text string for the variable Degree_Deg divided by
   ' 60. The Val function converts the text string to a number.
   minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
             InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _
             "°") - 2)) / 60
    ' Set seconds to the number to the right of "'" that is
    ' converted to a value and then divided by 3600.
    seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
            2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) _
            / 3600
   Convert_Decimal = degrees + minutes + seconds
End Function

Para convertir de grados decimales a radianes usamos la función RADIANES() o este algoritmo (ver la explicación aquí)

radianes = ángulo decimal x (Pi / 180)

Para convertir ángulos de radianes a decimales usamos la función GRADOS().

Como indicamos anteriormente, Excel usa radianes para cálculos con ángulos (trigonometría). Por lo tanto si queremos calcular el seno de un ángulo expresado como número decimal, debemos previamente convertirlo a radianes