Comparación rápida de tablas con Datos-Consolidar

Una tarea común en Excel es comparar tablas (o listas) para encontrar diferencias o cambios. En varias notas de este blog hemos mostrado distintas técnicas para hacerlo. Existen varios complementos (Add Ins), algunos gratuitos, para este tipo de tareas.

Hoy mostraremos un pequeño truco.

Cuando queremos comparar qué datos han sido agregados, quitados o cambiados, una posibilidad es usar funciones de búsqueda como BUSCARV o COINCIDIR. Pero esta técnica tiene el inconveniente que sólo puede encontrar lo que existe en la lista de búsqueda o informarnos que lo que buscamos no se encuentra (o no coincide) con un resultado #NA.

Con Datos-Consolidar podemos ir más lejos. Supongamos que recibimos un reporte de ventas y unos días más tarde una segunda versión.




















Como podemos apreciar, en el informe 1 faltan las sucursales 3, 4 y 5; también los datos de las sucursales 1, 2 y 9 son distintos.

El primer paso que damos es cambiar el encabezamiento de la columna B en ambas hojas. En lugar de Ventas pondremos Ventas1 y Ventas2 respectivamente.

El segundo paso es agregar una tercer hoja, “comparación” (podemos darle cualquier nombre que queramos). Seleccionamos la celda A1 de la nueva hoja y abrimos el menú Datos-Consolidar

En el formulario que se abre marcamos las opciones “Fila Superior” y “Columna Izquierda” de “Usar Rótulos en”; en la ventanilla “Examinar” seleccionamos el rango relevante de la hoja Informe1



Apretamos el botón Agregar y seleccionamos el rango relevante en la hoja Informe2

Volvemos a apretar Agregar y luego Aceptar. Excel crea en forma automática esta tabla

Podemos ver con facilidad con facilidad todos los cambios entre ambas listas. Con unas fórmulas sencillas tenemos en segundos un informe detallado de las diferencias

Una complicación puede surgir cuando tenemos más de una columna descriptiva a la izquierda de los datos que queremos comparar. Por ejemplo, si en nuestras tablas además de las sucursales también existe un campo (columna) con la zona



Como Excel consolida en referencia a la columna izquierda, el detalle de las sucursales no es tomado en cuenta. La solución es crear una columna auxiliar concatenando los valores de ambos campos



Enseguida explicaremos por qué separamos los valores con una coma. Ahora volvemos al proceso de consolidar partiendo de la columna auxiliar



El resultado es el siguiente



La coma que hemos agregado en la concatenación nos ayuda a separar la columna auxiliar en las dos originales usando Datos-Texto en columnas.

Primero insertamos una columna en blanco entre las columnas A y B

Luego seleccionamos el rango con valores en la columna A y usamos el menú Datos-Texto en columnas con la opción de separadores “coma”

El resultado:




Todo lo que nos queda por hacer es agregar las fórmulas y los formatos que queramos.

Technorati Tags: MS Excel

Agrupar y desagrupar filas en hojas protegidas

Ya en el pasado hemos hablado de la funcionalidad Datos--Subtotales en Excel. Esta funcionalidad tiene un serio problema: no funciona si la hoja está protegida.
Supongamos esta tabla de ventas por mes y sucursal

Después de aplicar Datos-Subtotales, la tabla se ve así

Apretamos el botón del nivel 2 y veremos la tabla de esta manera

Ahora protegemos la hoja marcando todas las posibilidades de permiso

Si intentamos desplegar algún mes para ver el detalle o cambiar el nivel de agrupamiento, veremos esta nota



Es decir, Excel no tiene una opción para permitir agrupar o desagrupar líneas en una hoja protegida.

Una solución obvia es desproteger la hoja antes de efectuar el cambio. Pero esto puede contradecir nuestra intención (que el usuario no pueda modificar los datos en la hoja).

La solución consiste en proteger la hoja programáticamente, es decir, con una macro. En este caso se trata de un evento WorkBook_Open y el código lo ponemos en el módulo del objeto ThisWorkbook

Private Sub Workbook_Open()
    With Worksheets("Hoja1")
        .EnableOutlining = True
        .Protect Password:="", _
        Contents:=True, UserInterfaceOnly:=True
    End With
End Sub

En este código hemos dejado la contraseña en blanco (.Protect Password:=""), lo que permite al usuario quitar la protección. Si queremos agregar la contraseña la pondremos entre las comillas.
Como hemos usado el evento Workbook_Open, cada vez que se abre el cuaderno la protección entra en efecto.

De esta manera hemos bloqueado las celdas protegidas, pero permitimos a nuestros usuarios agrupar y desagrupar las líneas.

Technorati Tags: MS Excel

Promedios con SUBTOTALES sin incluir ceros.

Un lector me consulta si se puede usar la función SUBTOTALES para calcular un promedio sin tomar en cuenta los ceros.

Recordemos que la función SUBTOTALES tiene la propiedad de realizar cálculos sin tomar en cuenta valores ocultos al aplicar Autofiltro (o con Ocultar filas usando el argumento 109 en lugar de 9 para SUMA, 101 en lugar 1 para PROMEDIO, etc).

Supongamos esta tabla de dos columnas: Categorías y Valores

La fórmula en la celda B12 es

=SUBTOTALES(1,B2:B10)

donde el argumento “1” indica que queremos calcular el promedio.

Ahora filtramos la lista para dejar visible sólo los valores de la categoría C

Podemos ver que el resultado es 3.67. Si no tomamos en cuenta los ceros, el resultado tendría que ser 5.50 (aclaremos que debemos tener un buen motivo para no tomar en cuenta los ceros, a diferencia de celdas vacías que no son tomadas en cuenta por la función).

Una solución es crear una columna auxiliar con la fórmula =SI(B2<>0,B2,"") en la columna C

En la celda C12 ponemos la fórmula con la función SUBTOTALES, que al filtrar dará el resultado esperado

Si no queremos usar una columna auxiliar, podemos crear esta fórmula, adaptada de una propuesta por Bob Phillips

=SUMAPRODUCTO(SUBTOTALES(9,DESREF(B2,FILA($B$2:$B$10)-2,,1))*($B$2:$B$10<>0))/
SUMAPRODUCTO(SUBTOTALES(3,DESREF(B2,FILA($B$2:$B$10)-2,,1))*($B$2:$B$10<>0))

Technorati Tags: MS Excel