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

Fechas en combobox

Ya hemos tratado en este blog sobre la posibilidad de incrustar controles directamente en hojas de Excel. En particular hemos mostrado las bondades de usar cuadros combinados de la colección de controles ActiveX (combobox) para crear listas desplegables.

Ciertos problemas surgen cuando queremos usar una combobox incrustada en la hoja para desplegar fechas. Veamos este ejemplo: en la hoja tenemos un rango con fechas al que le hemos asignado un nombre (fechas); hemos incrustado un cuadro combinado (combobox) para que el usuario elija una de esas fechas y ésta aparezca en la celda E5. También nos hemos preocupado de darle a E5 el formato de fecha

Al desplegar las fechas esto es lo que veremos

pero al elegir la fecha las cosas se complican

El formato de fecha se ha perdido tanto en la celda ligada como en el cuadro combinado. Lo que vemos ahora es el número de serie que representa la fecha.

Pero si miramos con un poco más de atención veremos que hay un segundo problema. El valor aparece alineado a la izquierda, lo que nos sugiere que se trata de un valor de texto, no numérico. Efectivamente, el valor que pasa de la combobox a la celda es textual. Para remediar esta situación tendremos que programar un evento de la combobox.

El código del evento debe ir en el módulo de la hoja que contiene el cuadro combinado. Podemos acceder al módulo desde el editor de Vba seleccionando el objeto Sheet correspondiente

o seleccionando la combobox en la hoja y seleccionando Ver Código en el menú contextual (para poder seleccionar el objeto debemos activar la opción Modo Diseño en Programador-Controles)

En el módulo ponemos este código

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
End Sub

Ahora veremos el formato adecuado en la celda y en el cuadro combinado. Pero si prestamos atención veremos que el valor sigue siendo texto.

Si no queremos realizar ninguna operación con el valor que pasamos a la celda ligada, podemos terminar aquí nuestra tarea. Pero en caso contrario tendremos que convertir el texto en valor numérico. Recordemos que la celda ligada ya tiene formato de fecha.

Para que esto suceda agregamos una línea de código en el evento

Private Sub ComboBox1_Change()
    ComboBox1.Value = CDate(ComboBox1.Text)
    Range(ComboBox1.LinkedCell).FormulaR1C1 = CDate(ComboBox1.Text)
End Sub

Con este código el valor en la celda ligada será numérico.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.