Fórmulas con resultados múltiples

Hay situaciones en las que una fórmula puede dar más de un resultado. El caso clásico es con las funciones de búsqueda como BUSCARV o INDICE.


La alternativa más práctica en estos casos es usar Autofiltro (o Filtro Avanzado). Pero hay situaciones, por ejemplo cuando construimos un tablero de comandos (dashboard), donde queremos que los resultados aparezcan en un rango determinado de la hoja o en otra hoja.

Supongamos que tenemos esta tabla (la misma que usamos en la nota sobre el uso de BUSCARV en listas con valores repetidos):

En otra hoja queremos poner el nombre de un producto y que se desplieguen todas las órdenes de compra del producto.

Si usamos BUSCARV (o INDICE con COINCIDIR) para obtener todas las órdenes de compra de tornillos, sólo podemos obtener un resultado

Sólo podemos obtener un resultado por celda. No hay ninguna forma de escribir resultados de una fórmula en otra celda que no contenga la fórmula, al igual que no podemos cambiar la estructura de la hoja con fórmulas.
Si queremos poner en una hoja de Excel todos los resultados posibles de una fórmula de búsqueda tendremos que usar otras técnicas.

En esta nota mostraré dos técnicas para obtener todos los resultados. Una estrategia posible es usar múltiples fórmulas.


Establecemos por adelantado cuál pueda ser el número máximo de resultados a obtener y en un rango de celdas contiguas introducimos esta fórmula matricial:

=INDICE(od_compra;K.ESIMO.MENOR(SI(DESREF(od_compra;0;0;FILAS(od_compra);1)=$B$2;FILA(DESREF(od_compra;0;0;FILAS(od_compra);1))-FILA(DESREF(od_compra;0;0;1;1))+1;FILA(DESREF(od_compra;FILAS(od_compra)-1;0;1;1))+1);FILA()-3);2)

donde el nombre "od_compras" define el rango A2:B10 de nuestro ejemplo

Por ejemplo, en una nueva hoja ("formulas") introducimos la fórmula en la celda A4 y la copiamos en todo el rango hasta la celda A9 (estamos suponiendo que el número máximo de órdenes posibles es seis)

Dado que sólo hay tres órdenes de compras para tornillos, el resultado en las celdas A18:A20 es #¡REF!

Para ocultar estos resultados podemos usar formato condicional

Esta técnica tiene varias desventajas: el uso de fórmulas matriciales, el uso de DESREF que es volátil, y además es complicada.

Una técnica alternativa es usar la cámara junto con Autofiltro.

Activamos el Autofiltro en la tabla de datos (en la hoja "datos"). Luego seleccionamos todo el rango de la tabla sin incluir los encabezamientos y activamos la cámara; creamos una nueva hoja ("cámara") y pegamos la imagen en el lugar deseado

En la hoja Datos filtramos la tabla de acuerdo al producto deseado

La imagen en la hoja "cámara" reflejará el cambio instantáneamente

El inconveniente con este modelo es que debemos ir a la hoja Datos para hacer el filtrado. Si queremos manejar los resultados desde la hoja "cámara", tendremos que utilizar una macro para manejar el filtrado por "control remoto".

Empezamos por agregar una lista desplegables, con validación de datos, en la celda B2 de la hoja "cámara" para poder controlar el filtrado

Luego ponemos este código en un módulo común del editor de Vb

Sub filtrado_ordenes()
    Dim strCrit As String
    Dim rngTablaDatos As Range
  
    strCrit = Sheets("camara").Range("B2")
  
    Set rngTablaDatos = Sheets("datos").Range("A1").CurrentRegion
  
    If strCrit <> "Todos" Then
        rngTablaDatos.AutoFilter Field:=1, Criteria1:=strCrit
    Else
        rngTablaDatos.AutoFilter
    End If
  
End Sub

Para activar el código cuando elegimos el producto programamos este evento en el módulo de la hoja "cámara"

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Address = "$B$2" Then Call filtrado_ordenes

End Sub

Con esto hemos completado nuestro modelo. Podemos ocultar la hoja "datos" de manera que nuestro usuario sólo vea la hoja "cámara" desde la cual puede manejar las que órdenes serán exhibidas en la hoja.

El archivo con el ejemplo se puede descargar gratuitamente aquí.

Más sobre del comando Ir A - Especial de Excel

El blog oficial del equipo de desarrollo de Excel está publicando una serie de notas sobre los atajos de teclado favoritos de sus miembros. Esto me trajo a la memoria una nota que escribí en los albores de este blog sobre el comando Ir A.

En esa nota me limitaba a mostrar cómo seleccionar fácilmente todas las celdas en blanco de un rango. Esto es muy útil cuando tenemos que llenar los vacíos en una columna de una tabla.

Por ejemplo, al importar datos de una base de datos o copiar los valores de una tabla dinámica es muy común tener esta situación (los datos son la base de datos Northwind incluida en las distintas versiones de Office)

Para poder usar estos datos en una tabla dinámica o totalizar con fórmulas u otras tareas por el estilo, tenemos que llenar las celdas en blanco con el nombre del país correspondiente. Lo hacemos fácilmente con Ir a (atajo: F5 o Ctrl+I). Primero seleccionamos todo el rango a rellenar (en nuestro caso A1:A24), luego apretamos F5 (o Ctrl+I) y apretamos el botón Especial

Elegimos la opción Celdas en Blanco y apretamos Aceptar

La celda activa ahora es A3. Apretamos "=" y seleccionamos A2 con el mouse

Finalmente apretamos simultáneamente Ctrl y Enter, obteniendo este resultado

Es recomendable seleccionar nuevamente el rango y convertir todas las referencias en constantes (Copiar-Pegado Especial Valores).

Pero, como podemos ver, hay en Ir A-Especial mucho más. Básicamente, Ir A-Especial nos permite seleccionar rangos de acuerdo a criterios. Veamos algunos usos:

1 – Eliminar todos los comentarios en un rango



En el rango A1:B7 tenemos cuatro comentarios como podemos apreciar por las señales rojas. Seleccionamos todo el rango y usamos Ir a Especial-Comentarios

Abrimos el menú contextual (botón derecho del mouse) y con Eliminar Comentario, quitamos todos los comentarios de una vez.

2 – Constantes/Celdas con fórmulas

En este ejemplo, Ir a Especial-Celdas con fórmulas-Errores, nos permite seleccionar todas las celdas del rango con resultado #N/A. Luego podemos reemplazar el valor de estas celdas con otro (vacío, cero, etc.) de una vez usando Ctrl+Enter.

3 –Diferencias entre filas/Diferencias entre columnas.

Estos comandos nos permiten seleccionar todas las celdas de una columna/fila cuyos valores son distintos de los de las celdas de referencia. La celda de referencia es la ceda activa del rango seleccionado. Veamos estos ejemplos aplicado al rango A1:C3

Después de seleccionar, la celda activa es A1, aplicamos Ir a Especial-Diferencias entre filas

En la fila 1 la celda seleccionada es B1, la única que contiene un valor distinto a la celda de referencia (A1).

En la fila 2 la celda de referencia es A2 y por lo tanto la celda seleccionada es C2.

En la fila 3 las celdas seleccionadas con B3 y C3, que contienen valores distintos a A3.

De la misma manera funciona Diferencias entre columnas

Al principio de la nota vimos como seleccionar todas las celdas en blanco en un rango. Con estos comandos podemos hacer lo opuesto, seleccionar todas las celdas no en blanco del rango. El truco consiste en que la celda de referencia este en blanco. Por ejemplo podemos agregar una fila y una columna en blanco al rango y usar Diferencias entre filas

Gráfico dinámico que muestra los últimos n puntos de la serie

El origen de esta nota es la consulta de un lector que me pregunta cómo hacer un gráfico que muestre siempre los 12 últimos puntos de una serie.

Como en casi todo gráfico dinámico, el truco está en usar rangos dinámicos definidos en nombres. Por lo general, y en esta nota, lo haremos con la ayuda de la función DESREF.

Supongamos esta tabla de ventas que muestra los 12 meses de año 2009 y que hemos representado con un gráfico.

Queremos que el gráfico muestre siempre los últimos n meses, siendo n un número que el usuario defina. Por ejemplo, si agregamos los enero y febrero del 2010 queremos ver los datos desde marzo del 2009 a febrero del 2010.

Como ya hemos mostrado, vamos a reemplazar los rangos estáticos de la serie de datos y de los valores del eje de las categorías, por rangos dinámicos

Antes de definir los rangos dinámicos, definimos un nombre que cuente cuántos períodos (datos) hay en la tabla. Lo hacemos basándonos en la columna de los períodos (la columna B en nuestro ejemplo) ya que puede haber períodos sin datos

cntPeriodos = =CONTARA(dinamico!$B:$B)-1

Restamos 1 del resultado de CONTARA para eliminar la cuenta del título de la columna.

El próximo paso es elegir una celda donde ponemos el número de meses que queremos mostrar (en nuestro ejemplo H2, y no por capricho como ser verá luego). También a H2 le ponemos nombre, pmtMeses

Ahora definimos nombres dinámicos para el eje de la Y, grfY y para el eje de la X, grfX

grfX =DESREF(dinamico!$B$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

grfY =DESREF(dinamico!$C$3;cntPeriodos-pmtMeses;0;pmtMeses;1)

Una vez definidos los nombres reemplazamos los rangos estáticos en la fórmula SERIES, cuidándonos de dejar el nombre del cuaderno incluido el símbolo !

La explicación de estas fórmulas es la siguiente:

dinamico!$B$3;cntPeriodos-pmtMeses calcula desde donde comienza el rango. Por ejemplo si tenemos 14 períodos en la tabla (calculados por cntPeriodos) y queremos mostrar sólo los últimos 12 (pmtMeses), cntPeriodos – pmtMeses da 2; por lo tanto nuestro rango comienza en B5 y se entiende por 12 celdas hasta B16.

Ahora podemos aprovechar la ubicación de H2 para crear un título dinámico para el grafico

Cuando reemplazamos el valor de la celda H2, el título cambia de acuerdo al contenido del gráfico.

El archivo se puede descargar aquí.