Como contar registros únicos en tablas dinámicas

Eduardo, colega de trabajo, es inteligente, aplicado y no le gusta depender del departamento de informática. Por eso, maneja una serie de pequeñas bases de datos en Excel (para el horror del departamento de IT ya mencionado). Para generar sus reportes usa principalmente tablas dinámicas de las cuales, para ponerlo de alguna manera, está perdídamente enamorado. A tal punto que todos mis intentos de mostrarle las bondades de otras herramientas como, por poner un ejemplo, Filtro Avanzado, siempre fracasan en forma rotunda.
Por eso cuando Eduardo entró ayer en mi oficina, sólo por la mirada, me di cuenta que algo le estaba pasando. No era una visita de cortesía.

- ¿Cómo hago para saber cuántos clientes tengo en mi base de datos?
 - Dado que todo lo hacés con tablas dinámicas, te sugiero que arrastres el campo de clientes al área de los datos usando la función Contar para totalizar.
- Si, es lo que hice; pero cada cliente aparece en más de una fila en la base de datos y entonces me cuenta la cantidad de veces que cada cliente aparece, no la cantidad de clientes.
- Por supuesto, así funcionan las tablas dinámicas.
- Pero, ¿cómo, no hay una función para contar registros únicos?
- Si y no...
- Uf, ya empezaste. ¿Si o no?
- En Excel 2013 hay una función para contar registros únicos en un reporte dinámico; en Excel 2010 y anteriores, no.
- ¡Ah! Yo uso Excel 2010, ¿cómo hago?
- Creando un campo auxiliar en la base de datos.

Supongamos que nuestra base de datos es la tabla de facturas de la base de datos Northwind

Para contar cuántos clientes hay en la base de datos creamos una tabla dinámica arrastrando los campos País y Cliente al área de las filas y nuevamente el campo Cliente al área de datos usando la función Contar (dado que el campo Cliente no contiene valores numéricos, Excel usará esta función en forma automática)

Inmediatamente podemos ver que en Argentina hay 3 clientes, pero la tabla dinámica muestra 11.
En las versiones de Excel anteriores a Excel 2013, tenemos que usar una columna auxiliar.
Insertamos la columna Auxiliar entre los campos Cliente y Dirección; en esta columan ponemos la fórmula =CONTAR.SI($B$3:B3,B3) que copiamos a todas las filas

El campo Auxiliar muestra el número de orden de aparación de cada cliente. Ahora podemos usar este campo como filtro de la tabla dinámica para que muestre sólo los registro donde el valor de Auxiliar es 1

Ahora podemos ver que la cuenta es correcta

Con Excel 2013, las cosas son más sencillas. No necesitamos crear ningún campo auxiliar. Sencillamente creamos la tabla dinámicas a partir de la base de datos. En el asistente de tablas dinámicas nos aseguramos de marcar la opción "Agregar estos datos al Modelo de datos" (esta opción sólo existe en Excel 2013)

Una vez creada la tabla, arrastramos el campo País al área de filas y el campo Cliente al área de los datos; seleccionamos el área de datos y abrimos el menú de configuración del campo. En la casilla de elección del tipo de cálculo tenemos una nueva función: "Recuento distinto"

Elegimos esta función y apretamos aceptar

Podemos ver que el encabezamiento del campo ha cambiado de "Recuento de cliente" a "Recuento distinto de Cliente" y que efectivamente tenemos 84 clientes en la base de datos.

Personalmente pienso que la traducción tendría que haber sido "Recuento único".

Otra forma de crear Hipervínculos en Excel

Los hipervinculos son una excelente herramienta para crear vínculos a celdas u objetos en hojas o cuadernos y también a archivos y páginas Web. Pero también tienen sus bemoles y una visita a los foros de Excel en la red revela que uno de los problemas es que cada tanto los hipervínculos desaparecen o dejan de apuntar adonde deberían.

Pero en esta nota no vamos a hablar de la desaparición de los hipervínculos, sino mostrar otra forma de crearlos, casi instantánea.

Hace unos años atrás mostré una técnica que implica el uso de macros. Hoy vamos a mostrar otra técnica que usa el "drag and drop". Esta técnica es muy útil cuando no tenemos una gran cantidad de hojas.

Uno de los usos más prácticos de los hipervínculos en Excel es crear un índice del contenido del cuaderno. Supongamos que tenemos un cuadernos con datos de cuatro sucursales de un empresa y queremos crear una hoja con hipervínculos que apunten a cada una de las hojas.

En la hoja "indice" queremos crear los hipervínculos a cada una de las hoja de las sucursales.

Seleccionamos la hoja Norte, por ejemplo, y en ella la celda B3 que contiene el texto "Sucursal Norte"

Ahora arrastramos la celda usando el botón derecho del mouse y apretando simultáneamente la tecla Alt. Arrastramos la celda hasta apuntar a la pestaña de la hoja "indice", lo que la activará.
Una vez en la hoja "indice", dejamos de apretar la tecla Alt y posicionamos el mouse en la celda indicada. Al soltar el botón del mouse aparecerá en menú contextual donde elgimos la opción "Crear hipervínculo aquí"

Excel crea automáticamente el hipervínculo, ahorrándonos la molestia de tener que definir el texto.

Esta técnica funciona solamente en cuadernos que han sido previamente guardados.

Este video demuestra la técnica

La última actualización de Excel deshabilita los controles ActiveX

A los usuarios de Excel que hayan instalado la actualización del Office del 09/dec/2014 les espera una desagradable sorpresa: Excel no permite incrustar controles ActiveX en las hojas

Al intentarlo recibimos este aviso: No se puede insertar el objeto

Para quien, como yo, use estos controles en sus modelos (por ejemplo, en gráficos animados o en dashboards), esta situación es un verdadero dolor de cabeza.

Para nuestra fortuna el MVP RoryA publicó esta solución en su sitio :

• Cerrar todos los programas del Office;
• usando el Windows Explorer o cualquier otra aplicación (personalmente prefiero el Total Commander) buscar todos los archivos *.exd (no confundir con *.exe) y borrarlos o, preferentemente, cambiarles el nombre.

• Volver a abrir Excel y probar si todo funciona ahora normalmente. También se puede realizar reboot del computador.

En mi caso, ésto solucionó el problema, pero de acuerdo a las conversaciones el foro Technet en ciertos casos el problema persiste. De acuerdo a Rory, los técnicos de Microsoft conocen el problema y es de esperar una corrección en breve.

Señalemos que el problema no se limita a la incapacidad de incrustar controles ActiveX en una hoja de Excel. Controles existentes dejar de funcionar y son convertidos en imágenes.

Distribución normal de pagos

Hace ya algún tiempo una lectora publicó este comentario en el post sobre como crear una distribución normal con Excel

Estoy tratando de hacer un flujo de caja donde tengo montos de dinero que debo distribuir en el tiempo y que se debe distribuir en forma normal (Gauss). Los datos que tengo son: el monto y el número de meses. Entonces, lo que quiero obtener es, el monto parcial para cada mes con una distribución normal. Es decir, el mes 1 y el mes final tendrán montos bajos y el mes del medio tendrá el mayor valor. La suma total debe los montos parciales debe ser igual al monto total.

Es decir, tenemos que crear primero una distribución normal (Gauss) y luego distribuir el monto total del dinero de acuerdo a esta distribución. Las variables son el monto y el número de meses.

Empecemos por crear la distribución normal para lo cual necesitamos una serie de  12 valores (los doce meses que requiere nuestra lectora) distribuidos normalmente.

Ponemos los datos tal como figuran en la imagen abajo

Seleccionamos la celda A5 (más adelante explicaré por qué su valor es -11) y abrimos el menú Inicio-Modificar-Rellenar-Series; marcamos la opción Columnas, Lineal, en Incremento ponemos el valor 2 (será explicado más adelante) y en límite 11 (ídem)

Apretamos "Aceptar" y Excel crea esta serie

La regla que usamos para crear esta serie simétrica de datos es:

• valor inicial: número de cuotas menos 1 por -1 (en nuestro ejemplo: (12 - 1) x (-1) = 11)
• incremento: 2
• valor final (límite): valor inicial positivo

Ahora usamos la función DIST.NORM para calcular la distribución normal de acuerdo a la media y a la desviación estándar. En la celda B5 ponemos la fórmula =DISTR.NORM(A5,$B$1,$B$2,FALSO) y la copiamos al resto del rango

Ahora podemos usar los valores de la columna B para distribuir los pagos; en la celda C5 ponemos esta fórmula

=$B$3*(B5/SUMA($B$5:$B$16))

que copiamos al resto del rango

Como podemos ver, hemos dividido el monto a pagar en cuotas distribuidas aproximadamente según una distribución normal

Para variar la distribución de las cuotas, podemos cambiar el valor de la desviación estándar (la celda B2) o generar una serie de índices (el rango A6:A17) con un incremento más pequeño.

En esta animación podemos ver como va cambiando la distribución de los pagos a medida que cambiamos el valor del desvío estándar

Para generar una serie de índices con un incremento de 1, empezamos del valor -5.5 (la mitad del número de cuotas menos 1)

La función FORMULATEXTO y Excel 2010

Una de las nuevas funciones en Excel 2013 es FORMULATEXTO. Esta función, disponible solamente en Excel 2013 y Excel 365, transforma la fórmula de una celda en texto.

¿Para que sirve?, me preguntarán Básicamente, para documentar fórmulas, como la que aparece en la imagen. La fórmula calcula la fecha del tercer lunes de un mes determinado (la nota sobre el tema la publicaré próximamente).

En algunas de mis notas suelo incluir el texto de la fórmula. Para obternerlo suelo copiar la fórmula directamente de la barra de las fórmulas y pegarla en una celda previamente formada como Texto o pegarla quitando el símbolo "=" para que Excel no la interprete como fórmula.

Dado que la mayor parte de mis notas las desarrollo con Excel 2010, decidí que podría crear una UDF (función definida por el usuario) que imite el funcionamiento de FORMULATEXTO para poder usarla en versiones de Excel anteriores a Excel 2013.

El código de la función es el siguiente


Function formulaText(rcell As Range, vType As Boolean) As String

    If rcell.HasFormula = False Then
        formulaText = "#NA!"
        Exit Function
    End If
  
    Select Case vType
        Case Is = False
            formulaText = rcell.FormulaLocal
        Case Is = True
            formulaText = Mid(rcell.FormulaLocal, 2, Len(rcell.FormulaLocal) - 1)
    End Select
    
End Function


La función tiene dos argumentos:

• rcell: es la celda que contiene la fórmula

• vType: que puede ser 0 o FALSO (incluye el símbolo "=" al principio de la fórmula) o 1  o VERDADERO (el texto no muestra el símbolo "=")

Totalizar datos en Excel con Datos-Consolidar

Uno de los métodos con los que cuenta Excel para consolidar datos de distintas hojas o cuadernos es Datos-Consolidar (pueden ver esta prehistórica nota en el blog o apretar el enlace Consolidar Datos en la nube de etiquetas).

También podemos usar este método para consolidar rápidamente los datos de una tabla. Supongamos que tenemos una tabla de 1000 filas que detalla las ventas del mes de noviembre. Dado que cada venta se anota por separado, cada fecha del mes aparece varias veces. Nuestro objetivo es totalizar las ventas por día (si, por supuesto que podemos hacerlo con una tabla dinámica; pero aquí mostraremos como hacerlo con Datos-Consolidar),

Supongamos que las ventas están en el rango A1:B1001; el primer paso será definir un nombre que se refiera a este rango. Seleccionamos el rango y introducimos el nombre en la cuadro de nombres

El próximo paso es seleccionar una celda, en la misma hoja o en la hoja donde queremos que aparezcan los datos totalizados, y activar el menú Datos-Consolidar

Nos aseguramos que la función sea "Suma", en la referencia pegamos el nombre que se refiere al rango de los datos (podemos usar F3 para pegar el nombre) y marcamos las opciones "Fila superior" y "Columna izquierda" en la opción "Usar rótulos". Finalmente apretamos Aceptar

Excel crea instantáneamente una tabla totalizando las ventas por fechas.

Detalles a tener en cuenta:

• Excel no pone el encabezamiento en la primer columna (la celda D2 en nuestro ejemplo), por lo que debemos agregarla manualmente;
• en la tabla de totales las fechas aparecerán con formato General, por lo que debemos aseugrarnos de pre-formar el rango de las celdas o hacerlo después de crear la tabla.