Búsqueda horizontal con COINCIDIR

Lo más memoriosos de mis lectores seguramente recordarán la épica nota sobre búsqueda con la función COINCIDIR en columnas múltiples. En esa nota señalábamos que  la función COINCIDIR nos permite calcular la posición que ocupa un elemento determinado en un rango, pero que si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.
En la nota mostrábamos dos formas de realizar la búsqueda con COINCIDIR a lo largo de varias columnas: con fórmulas y con macros.

Ahora consideremos este problema: dada esta matriz de valores numéricos

queremos encontrar en qué fila aparece por primera vez el número 8. Como puede apreciarse el 8 aparece por primera vez en la fila 2.

Si aplicamos la fórmula que postulamos en la nota anterior, veremos que recibimos un resultado erróneo: ka fórmula da el resultado 3 cuando debería ser 2.

=SI.ERROR(COINCIDIR(H4,C4:C13,0),SI.ERROR(COINCIDIR(H4,D4:D13,0),SI.ERROR(COINCIDIR(H4,E4:E13,0),"ND")))

La fórmula realiza la búsqueda en el primer vector y si no encuentra una coincidencia, realiza la búsqueda en el segundo vector y así sucesivamente. Es decir, la búsuqeda en la matriz es "vertical", cuando lo que queremos hacer es una búsqueda "horizontal".

La solución obvia es usar vectores "horizontales", por ejemplo =SI.ERROR(COINCIDIR(H4,C4:E4,0),SI.ERROR(COINCIDIR(H4,C5:E5,0)....etc. El inconveniente de esta fórmula es obvio: en lugar de tres funciones COINCIDIR, tendríamos que combinar 10 funciones, tantas como las filas de la tabla. Imaginemos ahora una tabla con 1000 filas!

La solución en este caso es usar una función definida por el usuario que llamaremos "fila_celda"

Function fila_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
  
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            fila_celda = rngCell.Row - Matriz_Busqueda(1).Row + 1
            Exit Function
        End If
    Next rngCell
   
    fila_celda = "inexistente"

End Function

Esta función usa dos argumentos: Valor_buscado y Matriz_Busqueda

La función realiza la búsqueda a lo largo de las filas como puede verse en el vector del argumento Matriz?Busqueda: 11,1,11;9,8,3;...(nótese el uso de la coma y el punto y coma).

El código de la función debe guardarse en un módulo común del editor de Vb. Para que estpe disponibles para todos los cuadernos abiertos, podemos guardarlo en el libro Personal.

Una vez guardada, la función aparecerá en el asistente de las funciones bajo la categoría "Definida por el usuario"

Promedio ponderado en tablas con filas ocultas

Ya hemos tocado el tema de cómo calcular el promedio ponderado con Excel, si bien en la prehistoria de este blog, allá por el año 2006.

A diferencia del promedio normal, el promedio ponderado toma en cuenta el "peso" relativo de cada uno de los datos en relación al resto de los datos.

Por ejemplo, en este caso

El precio promedio, sin tomar en cuenta las cantidades vendidas por cada sucursal, es 15.50. Pero si tomamos en cuenta el "peso" de cada sucursal, vemos que en la mayor parte de las ventas el precio está por debajo del promedio, por lo cual el promedio ponderado noos da 12.84.
El promedio ponderado lo calculamos con la fórmula

=SUMAPRODUCTO(C3:C6,D3:D6)/SUMA(C3:C6)

Alternativamente podríamos agregar totales a la tabla y calcular el promedio ponderado dividiendo el total de venta (cantidad x precio) por el total de piezas vendidas

Ahora vamos a complicar las cosas y ver cómo podemos calcular el promedio ponderado en tablas filtradas (tablas en las que hemos aplicado Autofiltro u ocultado filas).

Veamos este ejemplo

El promedio sencillo del precio lo podemos calcular con SUBTOTALES, que ignora las celdas ocultas

Para calcular el promedio ponderado se nos presenta el problema el problema que, a diferencia de la función SUBTOTALES, SUMPRODUCTO toma en cuenta también las celdas ocultas en el rango.

Una forma de calcular el promedio ponderado en listas filtradas es usar columnas auxiliares (en este caso una única columna  auxiliar), usando la función SUBTOTALES con la opción 103 (CONTARA)

Al referirse a una única celda la función dará un valor 0 si la celda está en una fila oculta y 1 si la fila es visible. Luego podemos usar esta columna para calcular el promedio ponderado usando SUMPRODUCTO

=SUMAPRODUCTO(D4:D15*E4:E15*F4:F15)/SUMAPRODUCTO(D4:D15*F4:F15)

Si queremos evitar el uso de columnas auxiliares podemos usar esta fórmula que combina SUMAPRODUCTO con SUBTOTALES y DESREF

=SUMAPRODUCTO(SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1)),D4:D15,E4:E15)/SUBTOTALES(9,D4:D15)

La expresión

SUBTOTALES(3,DESREF(B4:B15,FILA(B4:B15)-FILA(B4),,1))

da un vector de 1 (fila visible) o 0 (fila oculta). Para comprobarlo podemos seleccionar la expresión en la barra de las fórmulas y apretar F9 para ver el resultado

Esta fórmula de autoría incierta (posiblemente Bob Philips), aparece en varios foros de Excel.

Criterios aplicados en Autofiltro - tip

Hemos recibido una hoja de Excel con una tabla  que contiene miles de filas y filtrada con Autofiltro. ¿Cómo hacemos para ver qué criterios han sido aplicados?

A partir de Excel 2007 podemos hacerlo con facilidad. Todo lo que hay que hacer es apuntar con el mouse al icono del filtro (el "embudo") y esperar unos segundos