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

Como dejé (casi) de usar BUSCARV, también con Excel 2010

En la nota Como casi dejé usar VLOOKUP mostré como crear reportes dinámicos a partir de dos o más matrices de datos usando la nueva funcionalidad de Excel 2013 Relaciones (Datos-Herramientas de Datos-Relaciones).

Esta funcionalidad no existe en forma nativa en Excel 2010, pero podemos agregarla instalando el complemento PowerPivot (en la nota del enlace se señalan todos los requisitos para la instalación).
No me voy a extender aquí sobre las bondades del PowerPivot (lo que muestro a continuación es la puntita de la punta del iceberg), pero a todos mis lectores que usen Excel 2010 o Excel 2013 les recomiendo ver este tutorial (incluye la descarga del archivo de datos para las prácticas).

Volviendo a nuestro tema, veamos cómo usamos el PowerPivot para crear un reporte dinámico con más de una fuente de datos.

Una vez instalado el complemento, veremos una nueva pestaña en la cinta

Como en el ejemplo de la nota anterior, nuestro cuaderno incluye dos hojas:

Ventas: las ventas de los productos (fecha, cliente, producto y suma)

• Producto: la categoría de cada producto (producto, categoría).
• Nuestro objetivo es crear un reporte de ventas por categoría.

Seleccionamos una celda de una de las tablas del cuaderno y en la pestaña del PowePivot apretamos "Datos de Excel-Crear Tabla Vinculada"

Excel abre la ventana del PowerPivot donde podemos ver que ha agregado la tabla seleccionada.

Hacemos lo mismo con la segunda tabla. Ahora ambas tablas aparecen en la ventana del PowerPivot

Ahora tenemos que crear una relación entre ambas tablas. El campo en común aquí es Producto. Una de las formas de hacerlo tenemos que cambiar la vista de la ventana a "Vista de diagrama"

Con el mouse conectamos el campo Producto de la tabla Categorías (donde los productos son valores únicos) con el campo Producto de la tabla de ventas

Otra forma de hacerlo es usando "Crear relaciones" en la pestaña "Diseñar"

Una vez definidas las relaciones, abrimos la pestaña Inicio del PowerPivot y usamos Tabla dinámica para general el informe

Excel genera una tabla dinámica a partir de ambas tablas como podemos ver en la ventana de la lista de campos