Cuando Excel se equivoca al ordenar datos

Hay muchos motivos por los cuales se pueden producir desajustes al ordenar una tabla en Excel. Cuando nos topamos con este problema, nuestra primera reacción es "¡hay un bug en Excel!". Pero los que "hemos fatigado los arduos senderos de Excel por años" (parafraseando al inmortal Borges), sabemos que en casi todos los casos la culpa recae sobre el usuario.

Este artículo de Microsoft contiene una lista de errores que pueden causar desajustes al ordenar datos.

Una vez dicho todo esto, veamos el siguiente caso

En esta tabla, los valores de la columna Porcentaje se obtienen de la tabla en la hoja "clientes", usando la función INDICE combinada con COINCIDIR.

Si observamos la fórmula en la columna "Porcentaje" veremos que Excel incluye la referencia a la hoja "ventas" a pesar de ser esta la hoja activa.

Esto se debe a que al construir la fórmula comenzamos en la hoja activa (Ventas), pasamos a la hoja "clientes" para señalar el rango de la matriz de búsqueda de INDICE y luego volvemos a "Ventas" para completar la fórmula.

Esta referencia a la hoja donde se encuentra la fórmula parece superflua pero inocua. Ahora veamos que pasa al ordenar la tabla por tipo de clientes. Como referencia recordemos que las ventas netas del Cliente 1 son 259,508

Las ventas neto del Cliente 1 ahora son 274,773!

Si nos fijamos en las fórmulas en la tabla ordenada por Tipo, veremos que, a pesar de que las referencias a la columna Tipo son relativas (por ejemplo, ventas!C3 para el Cliente 1), al ordenar la tabla éstas actúan como referencias absolutas.
En la imagen anterior podemos ver que el Cliente 10 se encuentra en la fila 3, pero la formula en la celda E3 se refiere a la celda C12, que era la fila del cliente antes de ordenar la tabla.

Para solucionar o evitar este problema lo que hacemos es eliminar la referencia a la hoja activa ("ventas" en nuestro caso) en las fórmulas. Nuestra fórmula ahora se verá así

=INDICE(clientes!$B$2:$B$4,COINCIDIR(C3,clientes!$A$2:$A$4,0))

Ahora, al ordenar los datos por tipo, no se producirá el desajuste

Power Pivot es la mejor novedad de Excel en 20 años

Si eres un usuario que utiliza fórmulas y características como:

• BUSCARV()
• SUMAR.SI()
• SUMAR.SI.CONJUNTO()
• Tablas dinámicas
• Conexiones a bases de datos u fuentes externas
• Tablas de Excel
• Consolidación de múltiples datos, VBA, etc

entonces este articulo o post te resultará muy interesante pues te ayudará a hacer tus reportes y análisis de una manera nunca antes vista. Power Pivot es una extensión a las cosas que ya conoces sobre Excel.

pero…¿Qué es Power Pivot en realidad?

Power Pivot es la nueva herramienta (complemento de Excel 2010 y 2013) que permite a los usuarios de negocio tomar un papel mucho más importante en la creación de ideas que son relevantes para la toma de decisiones. Es una herramienta de auto-manejo de Inteligencia de Negocio.
Si eres uno de los miles de usuarios de Excel que lucha con la creación de nuevos códigos VBA o tratar de llegar a soluciones que son semi-dinámicas debido a las limitaciones del tradicional Excel, Power Pivot entonces hará tu vida más fácil en casi todos los sentidos.
Para conocer un poco mas el aspecto técnico sobre Power Pivot puedes visitar mi articulo sobre:

¿Qué es Power Pivot? (la pregunta del millón de dólares)

y un video animado explicando más sobre Power Pivot (activar CC)

pero ahora, vamos a la parte que nos interesa.

¿Qué valor puede aportarle Power Pivot a mi trabajo actual?

Antes de comenzar, tengo que decirte algo muy importante... Power Pivot es gratuito!  No tienes que pagar absolutamente nada por el complemento.

Ok, ahora podemos comenzar. Voy a darte algunos puntos que necesitas saber sobre Power Pivot que te ayudarán a decidir si en realidad Power Pivot aporta o no valor a tu trabajo:

1. Limitante de filas/columnas en Excel ya no es un problema: recuerdas que solamente tienes ~1M de filas para trabajar en el Excel tradicional? y que con tan solo 100K ya tienes problemas con el Excel? pues ya no - es un problema del pasado con Power Pivot. Con Power Pivot puedes importar virtualmente una cantidad casi infinita de filas y columnas y seguir trabajando a una velocidad deslumbrante. Mira la imagen de abajo como prueba de una tabla cargada en Power Pivot con un poco mas de 161M de filas :)                 Imagen tomada de Powerpivotpro (mi buen amigo Rob Collie)
2. Con Power Pivot puedes trabajar con múltiples tablas: correcto! Power Pivot trabaja con tablas dinámicas y/o funciones de cubo y en vez de utilizar BUSCARV() o VLOOKUP() para "aplanar" las tablas, pues solamente puedes tomar 1 tabla para "pivotear", puedes utilizar múltiples tablas para trabajar y relacionar los datos. Puedes leer un ejemplo de Jorge aquí
3. Mejores y más flexibles fórmulas: alguna vez has intentado agregar un conteo distintivo en una tabla dinámica? es un problema muy serio y nunca se logra hacer algo dinámico o flexible, pero con DAX, el nuevo lenguaje de fórmulas para Power Pivot, puedes crear cualquier fórmula que te puedas imaginar. Te muestro un ejemplo aquí
4. Creando una aplicación de BI en la web (Con Excel!): Gracias a la integración de Power Pivot con SharePoint, es posible crear aplicaciones en la Web a partir de Excel que se auto refresquen (actualicen)  con los datos necesarios de acuerdo a un horario. En corto, el reporte que creas en Excel ahora puede ser expuesto mediante un explorador en la web sin que hagas ningún cambio. Te dejo un ejemplo abajo de algo que he creado en Excel con Power Pivot y lo he colgado en mi sito de SharePoint para demostraciones. Más adelante te comentare sobre Power BI que es la nueva oferta de Office365 para crear reportes en la Web (que también puedes consumir desde tu iPad, iPhone, Android y como App de W8)  

(puedes darle clic en la imagen para poder ver el reporte desde tu explorador o visitando mi sito demo aquí )

Con estos 4 puntos creo que tenemos muchos que asimilar pero falta MUCHO mas...(a revisar en próximos artículos a publicar en mi blog y en este blog)

Pero vamos a revisar otros puntos y dejaré algo sumamente interesante para el final que te va a dejar boquiabierto.

¿Cómo consigo Power Pivot?

Si tienes Excel 2010 (cualquier versión) puedes descargar el complemento desde la siguiente pagina y luego seleccionando la versión correcta de Power Pivot (32 o 64 bits)

Power Pivot para Excel 2010

Mientras que para Excel 2013 el complemento viene integrado en ciertas versiones de la aplicación. Para conocer más al respecto te invito a leer la siguiente entrada en mi blog:

Como obtener Power Pivot en Excel 2013

Power BI: tu futuro como profesional de Excel es prometedor :)

(dar clic en las imágenes para ver mi articulo completo sobre cada uno en mi blog)

Power BI se centra en Power Pivot con Office 365 (SharePoint) y las demás herramientas de BI que obtienes en Excel 2013 como:

Power View: reporte creado netamente con Excel 2013

Power View nuevamente: todo esto con Excel 2013. Increíble, no?

Mapa de Calor creado con Power Map dentro de Excel 2013 (Población de Panamá)

Sígueme en twitter o en mi blog para conocer más y mantenerte al día sobre Power BI:

@EscobarMiguel90

Blog 

PowerPivot – Bienvenida a un nuevo colaborador

Es para mí un placer anunciar que a partir de hoy Miguel Ángel Escobar comenzará a colaborar en este blog.

Miguel Ángel es, además de experto en Excel, un apasionado profesional del área del BI (Business Intelligence – Inteligencia de Negocios) y en particular del PowerPivot.

En su primer nota, Miguel Ángel nos introduce al nuevo mundo del PowerPivot. En las futuras notas, tratará distintos temas y aspectos de esta nueva y poderosa herramienta.

Miguel Ángel publica este blog bajo el lema "Power Pivot, Power BI, Excel y BI para las masas!" y es el propietario del sitio Powered Solutions.

Cuando las flechas de validación de datos en Excel no aparecen

Cuando definimos en una celda validación de datos con la opción Lista, al seleccionarla aparece una flecha que permite desplegar la lista

¿Qué hacer cuando a pesar de haber definido todo correctamente la flecha no aparece al seleccionar la celda, como le sucedió a una de mis lectoras?

El "primer sospechoso" es que hayamos quitado la marca en "Celda con lista desplegable" en la definición de la validación de datos

Otra posibilidad es que hayamos activado la opción de ocultar objetos. Para comprobar esto podemos activar el "Panel de selección" en Diseño de página—Activar. En el panel podemos ver el estado del objeto (en esta animación "Drop Down 2)

También podemos revisar las definiciones de Excel en Archivo—Avanzadas

Si se ha seleccionado la opción Nada, todos los objetos serán invisibles, incluidas las flechas de validación de datos.

Finalmente, como con algunos gobiernos, existe la posibilidad de que se trate de un archivo corrupto. En este caso después de cerrar el archivo podemos abrirlo usando la opción "Abrir y reparar"

Truco para consolidar datos de varias hojas Excel

Excel permite la creación de rangos tridimensionales, tal como mostré en el pasado (apretar el enlace para ver la nota). Un rango tridimensional es aquel que se extiende a más de una hoja. En este ejemplo tenemos un cuaderno con cinco hojas (ya he señalado que separa datos de esta manera es una mala práctica pero a los efectos del ejemplo ignoraré mis propias recomendaciones).

Las hojas "Ventas…" contienen los totales de ventas de cada sucursal para cada año; en la hoja "Totales" queremos consolidar el valor de la celda B6 de todas las hoja de ventas

En lugar de la técnica que mostré en la nota mencionada más arriba, podemos usar este truco:

En la celda C3 de la hoja Totales introducimos =SUMA('*'!B6)

Al cerrar el paréntesis y apretar Enter, Excel lo transforma en

=SUMA('Ventas 2009:Ventas 2012'!B6)

Como puede apreciarse, todas las hojas del cuaderno, excepto la activa, son incluidas en el rango tridimensional.

Ahora supongamos que el cuaderno contiene también hojas para los costos de ventas, con la misma estructura (los totales en la celda B6). Para que estas hojas no aparezcan en la fórmula podemos condicionar el comodín con un criterio, por ejemplo

=SUMA('Ventas*'!B6

Esta técnica no está limitada a una única celda. Podemos usar, por ejemplo,

=SUMA('Ventas*'!B2:B5

Tampoco está limitada a la función SUMA (la lista de funciones puede verse en la nota ya mencionada).

Convertir fórmulas en valores según criterio.

En ciertas situaciones en Excel tenemos que convertir fórmulas en constantes. Por ejemplo, si extraemos valores de un cuaderno remoto usando BUSCARV, y queremos eliminar las fórmulas para evitar errores involuntarios, reducir el peso del archivo o mejorar la velocidad de cálculo.

Ya hemos mostrado como usar Ir A-Especial para convertir seleccionar todas las celdas que contengan fórmulas. Una vez seleccionadas, usamos Copiar-Pegado Especial-Valores.

Pero, ¿qué hacemos cuando queremos convertir sólo las celdas que contienen una función específica? Por ejemplo, todas las celdas que contienen BUSCARV en este cuadro de ganancias. Los datos los extraemos de un archivo remoto usando BUSCARV.

Luego de extraer los valores queremos eliminar las fórmulas BUSCARV pero no las fórmulas con las que calculamos las ganancias en las filas 6, 9 ,11 y 13.

Una posibilidad sería usar buscar y reemplazar (Ctrl+L) – buscar todos, seleccionar todos los resultados y apretar cerrar. Como ya mostramos, Excel selecciona todos los valores; luego podemos usar Copiar, pero si intentamos usar Pegado Especial-Valores esto es lo que veremos

La solución es usar una macro.

Sub formula_to_number_with_criteria()
    Dim strStringCriteria As String
    Dim rngCell As Range
    Dim lCounter As Long
   
    If Selection.Count < 2 Then
        MsgBox "Debe seleccionar por lo menos dos celdas", vbInformation
        Exit Sub
    End If
   
    'criterio de busqueda
    strStringCriteria = Application.InputBox(prompt:="Enter formula identifier", _
                                                Title:="Indentifier", Type:=2)
                                               
    'si no se ingresa criterio se cierra la rutina
    If Len(strStringCriteria) = 0 Then
        MsgBox "No se ingreso ningun criterio - no se puede realizar la operacion", vbCritical
        Exit Sub
    End If
   
   
    lCounter = 0
    For Each rngCell In Selection
        If rngCell.HasFormula Then
            If InStr(1, rngCell.FormulaLocal, strStringCriteria) > 0 Then
                rngCell = rngCell.Value
                lCounter = lCounter + 1
            End If
        End If
    Next rngCell
   
    If lCounter = 0 Then
        MsgBox "No se encontro ninguna celda con el criterio", vbInformation
    Else
        MsgBox lCounter & " celdas fueron modificada", vbInformation
    End If
     
End Sub


Esta macro hace lo siguiente:

• Comprueba que se hayan elegido por lo menos dos celdas
• Abre un formulario para que el usuario ingrese el criterio de búsqueda
• Busca todas las celdas en el rango seleccionado que cumplen con el criterio y reemplaza la fórmula por el valor
• Al terminar las operaciones produce un mensaje con el número de celdas que se han modificado.

Un detalle a tomar en cuenta es el uso de la propiedad FormulaLocal. Esto es necesario para que el código vea la versión local de la función (BUSCARV en nuestro caso) y no la versión nativa (inglés, VLOOKUP).
Podemos comprobar esto usando la ventana Inmediate del editor de Vba