martes, julio 28, 2015

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.

lunes, julio 20, 2015

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


viernes, julio 17, 2015

Tablas Dinámicas La Quinta Dimensión - libro electrónico

Aclaración: este post promueve el libro electrónico Tablas Dinámicas La Quinta Dimensión" de Miguel Caballero Sierra y Fabian Torres Hernandez. Por cada venta originada en este blog recibo una pequeña comisión.

Sin lugar a dudas la funcionalidad más eficiente de Excel para analizar datos son las tablas dinámicas. El dominio de las tablas dinámicas convierte al usuario intermedio en avanzado. Miguel Caballero Sierrra y Fabián Torres Hernández han publicado su manual Tablas Dinámicas La Quinta Dimensión para ayudar a todo usuario novato o intermedio dominar esta herramienta indispensable para el análisis de datos.

He aquí la descripción del libro en las palabras de los autores y una oferta especial al los lectores de JLD Excel

Tablas Dinámicas La Quinta Dimensión

Adquiere una nueva perspectiva sobre la funcionalidad estrella de Excel para analizar datos.

  • ·        Aprende Tablas Dinámicas desde 0 hasta convertirte en un experto.
  • ·        Personaliza tus Tablas Dinámicas de una manera única.
  • ·        Construye aplicaciones y soluciones increíbles utilizando Tablas Dinámicas
  • ·        Descubre Tips, rápidos y fáciles de usar para optimizar tu trabajo.
  • ·        Automatiza y añade características a tus Tablas Dinámicas con Macros
  • ·        Explora Power Pivot, El futuro del Análisis de Datos.
  • ·        Utiliza los Video Tutoriales para facilitar el aprendizaje.
  • ·        Apóyate en los archivos de práctica para afianzar tus conocimientos.


Enfocado a:

Usuarios de Excel, profesionales sin importar su campo de estudio o cualquier persona que esté interesada en aumentar sus conocimientos y habilidades, no es necesario que cuenten con conocimientos previos sobre Excel y en particular Tablas Dinámicas.  

Contenido detallado del Libro

El libro Tablas Dinámicas, La Quinta Dimensión es una obra enfocada en conseguir que cualquier persona interesada aprenda y posteriormente aplique los conocimientos adquiridos en cualquier situación que se requiera, brindamos al usuario:

  • ·        Ejemplos fáciles de entender y aplicados.
  • ·        Archivos de práctica.
  • ·        Video tutoriales.
  • ·        Anexos complementarios.


Este libro tiene como objetivo brindar un conjunto de trucos y macros enfocados a Tablas Dinámicas, trucos que servirán tanto para la estética del reporte, solución de tareas y automatización mediante macros, así como una primera guía a Power Pivot. La finalidad del presente texto es tomar un paso más avanzado si se quiere ir más allá de los cursos convencionales, en el siguiente enlace puedes verificar los trucos, macros, y demás elementos que componen el libro:


Audiencia JLD Excel

Debido a la gran acogida del libro, aplicaremos una promoción para los lectores del blog JLD Excel, consiste en un bono de descuento del 20% para adquirir el libro disponible hasta el 1 de agosto de 2015, usando el código JORGE en el bono.
Si tienes alguna duda de cómo aplicar puedes seguir las siguientes instrucciones, pero no olvides que estará disponible solamente por los próximos 15 días:

https://www.youtube.com/watch?v=oIqTvZxXeok

Filtrar por etiquetas en tablas dinámicas con dos criterios

Los filtros de etiquetas de los campos de las tablas dinámicas



son similares a los filtros de Autofiltro (la imagen de arriba es una tabla dinámica basada en la tabla de la imagen de abajo)

pero con un diferencia. Autofiltro nos permite filtrar el campo combinando dos criterios con O o Y (filtro cuando se cumple por lo menos un criterio o filtro cuando ambos criterios deben cumplirse);


pero el filtro de etiquetas del campo de la tabla dinámica solo permite aplicar un criterio
En nuestro ejemplo tenemos una tabla dinámica que muestra las ventas por ciudades.Supongamos que queremos filtrar la tabla dinámica para que muestre sólo ciudades que empiecen con la letra A o la letra B.
El filtro de etiqueta de las tablas dinámicas sólo nos permite filtrar por una de las letras


a diferencia del Autofiltro


Podemos superar este inconveniente con un pequeño truco. Empezamos por seleccionar el rango que contiene las etiquetas de los campos desde la primer celda libre a la derecha de la tabla dinámica

Ahora aplicamos Autofiltro (del menú Datos-Filtro). Las flechas del Autofiltro se "superponen" a las del filtro de las etiquetas (y además agregan un filtro al área de los datos).
Ahora, al activar la flecha del filtro, veremos las opciones de Autofiltro


El proceso se puede ver en este video:


miércoles, julio 15, 2015

Agregar pestañas personalizadas a la cinta de opciones (Excel 2007 - 2013)

Supongamos que hemos desarrollado un modelo en Excel (2007 en adelante) que contiene macros. Para facilitar el uso queremos agregar una pestaña (ficha) en la cinta de opciones para activar estas macros.

RibbonX


En este ejemplo, la ficha "Mis Macros" contiene tres iconos. Cada uno de ellos activa una macro ("Saludo" saluda al usuario con un "buen día" o "buenas tardes" de acuerdo a la hora del día; "Fecha" muestra la fecha corriente y "Hora" la hora).
Desde la versión 2007 de Excel en adelante no tenemos la posibilidad de programar la cinta de opciones con Vba, tal como podíamos hacer en las versiones anteriores donde podíamos crear barras de herramientas o agregar un nuevo elemento en la barra del menú de Excel programáticamente.

Si bien podemos agregar pestañas (fichas) en la cinta de opciones usando el menú Opciones-Personalizar Cinta de Opciones, agregar iconos a la ficha y ligar macros a los iconos, esta ficha es permanente, es decir, aparecerá en todo cuaderno de Excel que abramos. Lo mismo con los iconos que agreguemos en la barra de heramientas de accedso rápido (QAT).

Sin embargo podemos modificar la cinta de opciones dinámicamente, es decir, ligar la cinta modificada a un cuaderno determinado de manera que cuando cerramos el cuaderno las modificaciones desaparecen. Gracias a los excelente tutoriales de MVP Ron de Bruin la tarea es relativamente sencilla y no requiere dominio de detalles técnicos.

En este post mostraré los pasos para crear la pestaña para activar macros del ejemplo.

La forma más conveniente de editar la cinta de opciones es utilizando la herramienta Custom UI Editor que es gratuita y puede descargarse aquí.

Empezamos por crear el cuaderno que contiene las macros (el cuaderno con los códigos puede descargarse aquí). Una vez creado, guardamos y cerramos el cuaderno.

El próximo paso es activar el Custom UI Editor, abrir el archivo Excel que hemos creado (File - Open, en el editor) y elegir una de las dos opciones Office 2010 u Office 2007 en el menú Insert


Si el cuaderno será usado solamente con Excel 2010-2013 elegimos la opción Office 2010 Custom UI Part; para archivos que serán usados en Excel 2007-2013 usamos la opción Office 2007 Custom UI Part.
En nuestro ejemplo usaremos la segunda opción. En el editor pegamos este código

<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
  <ribbon >
    <tabs >
      <tab id="DbMacros" insertBeforeMso="TabInsert" label="Mis Macros">

        <group id="DbGroup01" imageMso="ReviewEditComment" label="Macros">
          <button id="DbG01B01" label="Saludo" imageMso="HappyFace" size="large"  onAction="Saludos" />
          <button id="DbG01B02" label="Fecha" imageMso="HeaderFooterCurrentDate" size="large"  onAction="la_fecha" />
          <button id="DbG01B03" label="Hora" imageMso="SlideShowRehearseTimings" size="large"  onAction="la_hora" />
        </group >


      </tab >
    </tabs >
  </ribbon >
</customUI >

Un archivo texto con el código puede descargarse aquí.

En la ventana del Custom UI Editor, este código se vera así





Analicemos las distintas partes del código XML que define la pestaña que agregamos:

<tab id="DbMacros" insertBeforeMso="TabInsert" label="Mis Macros">

En esta orden definimos un identificador, definimos dónde debe aparecer la pestaña en la cinta (a la izquierda de la pestaña Insertar) y la descripción (label).

<group id="DbGroup01" imageMso="ReviewEditComment" label="Macros">
          <button id="DbG01B01" label="Saludo" imageMso="HappyFace" size="large"  onAction="Saludos" />
          <button id="DbG01B02" label="Fecha" imageMso="HeaderFooterCurrentDate" size="large"  onAction="la_fecha" />
          <button id="DbG01B03" label="Hora" imageMso="SlideShowRehearseTimings" size="large"  onAction="la_hora" />
        </group >

Definimos el grupo y los botones que aparecerán dentro del grupo. Para cada botón definimos el identificador, la etiqueta, la imagen, el tamaño y con onAction la macro que será activada al pulsar el icono.

Las imágenes de los iconos están definidas por el valor de imageMso. Una lista de los iconos con sus nombre puede verse en esta página.

Después de introducir el código xml en el Custom UI Editor lo guardamos con el menú Save As

A partir de este momento cada vez que abramos el cuaderno la pestaña con el grupo de iconos "Mis Macros" aparecerá en la cinta de opciones.

El archivo con las macros puede descargarse aquí.

miércoles, julio 08, 2015

No, Excel no puede ayudarte a ganar la lotería

A lo largo de la vida de este blog he recibido no pocas consultas relacionadas con todo tipo de cálculos para acertar los números de la lotería. Digamoslo claramente: no, no se puede predecir el resultado de los sorteos de la lotería.
No sólo no se pueden predecir los resultados, sino que también las posibilidades ganar el primer premio son prácticamente inexistentes. Supongamos, por ejemplo, una loteria donde hay que acertar 6 de un conjunto de 49 números. Para calcular la probabilildad de acertar la cambinación ganadora, si n es el conjunto de 49 números que participan en el sorteo y k es cada una de las combinaciones posibles de 6 números, sin repeticiones, la expresión
n!
-----------------
k! * (n-k)!
donde el símbolo ! indica la operación factorial. Excel nos permite realizar el cálculo con facilidad usando la función COMBINAT(). La expresión =1/COMBINAT(n,k) nos da la probabilidad de que una combinación dada de seis números salga en el sorteo



Como puede apreciarse hay casi 14 millones de combinaciones posibles, es decir una probabilidad de 0.00000007 que nuestros números formen la combinación ganadora.

Sin embargo cada tanto alguién gana y se convierte enn un nuevo millonario. Por lo que seguiremos jugando y soñando qué hacer cuando la diosa fortuna golpee a nuestra puerta.
Y si bien Excel no nos puede ayudar a ganar la lotería, si puede ayudarnos a controlar si hemos obtenido algún premio.
Supongamos que nuestra lotería premia combinaciones de por lo menos 3 números ganadores. Supongamos también que siempre apostamos a la misma combinación y supongamos también que hemos jugado esta combinación durante diez sorteos y ahora queremos comprobar si hemos tenido suerte. Para hacerlo usaremos Formato Condicional y también SUMAPRODUCTO. La hoja Excel que aparece aquí abajo es interactiva y hay que desplazar la hoja hacia la derecha para ver las columnas K y L (también puede descargarse el archivo usando el icono en el ángulo inferior derecho).



Para señalar con un color de fondo los números sorteados que coinciden con "Mis_números" usamos format condicional con la opción Fórmula


Usamos ESNUMERO combinada con COINCIDIR para obtener resultado VERDADERO si el número evaluado coincide con alguno de los números del rango "Mis_numeros" lo hace que se aplique el formato.

En la columna L contamos la cantidad de aciertos logrados en cada sorteo con la fórmula

=SUMAPRODUCTO(--(E3:J3=Mis_numeros))



Usamos el doble guión para que Excel evalue los valores VERDADERO como 1 y los FALSO como 0.
La regla del formato condicional en la columna L es