Extraer el primer valor numérico de un rango mixto

Este post viene a colación de una consulta sobre cómo extraer con fórmulas el primer valor numérico en un rango mixto (rango que contiene celdas con texto y celdas con números).

En un post de hace varios años atrás mostramos como encontrar el primer número positivo o negativo en un rango. En el post la suposición era que todos los valores en el rango son numéricos, pero mi lector tenía que resolver el problema para un rango con texto y valores numéricos.

La solución es similar a la propuesta en la nota mencionada: usar INDICE con COINCIDIR en forma matricial, pero con un detalle: usar ESNUMERO para evaluar qué valores en el rango son numéricos y cuáles no.

En este ejemplo

calculamos extraemos el primer valor numérico en cada fila con esta fórmula

=INDICE(D3:K3,COINCIDIR(VERDADERO,ESNUMERO(D3:K3),0))

Tal como indicamos antes la fórmula es matricial, es decir, se introduce apretando simultáneamente Ctrl-Mayúsculas-Enter.

Usando la Evaluar Fórmulas podemos ver cómo funciona esta fórmula

La función ESNUMERO crea un vector de valores VERDADERO o FALSO según los datos del rango evaluado sean numéricos o textuales. COINCIDIR encuentra la posición del primer VERDADERO y el resultado es el argumento de posición en la función INDICE

BUSCARV con dos parámetros

Un lector me consulta si es posible hacer búsquedas en una tabla de acuerdo a dos parámetros. La respuesta es positiva y vamos a mostrar las distintas posibilidades en esta nota.
Aclaremos que el título de la nota es un poco engañoso ya que además de BUSCARV mostraremos soluciones con tablas dinámicas y con las funciones INDICE y COINCIDIR, en forma natural y en forma matricial.

Supongamos una tabla con tres columnas: artículo, fecha y precio. Cada artículo aparece varias veces pudiendo variar la fecha y el precio como en esta tabla:

1 - Solución con tabla dinámica (o "solución rápida").

Creamos una tabla dinámica basada en nuestra tabla de datos

En el área de las filas ponemos los campos Artículo y Fecha y el campo Precio en el área de los datos.
Ordenamos el campo Fecha de más reciente a más antiguo

con lo cual las fechas más reciente aparecerán al principio de cada grupo de artículos.

Ahora agregamos un cuadro de segmentación de datos y ocultamos todas la filas de la tabla excepto la que contiene el primer dato; agregamos algunos formatos para mejorar la presentación y ya tenemos nuestro modelo dinámico que siempre mostrará el último precio de cada artículo

La ventaja de esta solución reside en que no usa fórmulas sino tablas dinámicas por lo que funciona velozmente también con tablas de gran tamaño.

2 - Solución con BUSCARV y columna auxiliar.

Para usar BUSCARV en forma natural (es decir, no matricial) debemos crear una columna auxiliar con valores únicos por lo que combinamos el código del articulo con la fecha del precio )la columna ID)

Para esta solución es indispensable que la tabla esté organizada de menor a mayor de acuerdo al campo ID. Esto se debe a que usaremos BUSCARV con búsqueda aproximada, con esta fórmula

=BUSCARV(G3&MAX(tblPrecios[Fecha]),tblPrecios,4)

Como puede apreciarse, creamos el valor de búsqueda combinando al artículo buscado (en la celda G3) con el valor máximo del campo de las fechas. Como Hacemos una búsqueda aproximada, dejando el cuarto argumento de BUSCARV en blanco, obtenemos el valor más cercano que coincide con el valor buscado. Es por este motivo que la tabla tiene que estar ordenada de menor a mayor según el campo de búsqueda (ID).
Podemos ocultar la columna B de manera que el campo auxiliar no sea visible.

3 -Solución sin campo auxiliar con la función INDICE y COINCIDIR

Para ahorrarnos el campo auxiliar (considerado profano a las buenas prácticas de Excel por algunos puristas, concepto con el cual no concuerdo en absoluto), podemos combinar las funciones INDICE y COINCIDIR para crear esta función matricial (introducir con Ctrl-Mayúsculas-Enter):

=INDICE(tblPrecios4[Precio],COINCIDIR(F3&MAX(tblPrecios4[Fecha]),tblPrecios4[Articulo]&tblPrecios4[Fecha]))

Nótese que también aquí hacemos una búsqueda aproximada en la función COINCIDIR por lo que la tabla debe estar ordenada de menor a mayor según el campo Artículo y también de antiguo a reciente según el campo Fecha.

4 - Solución sin necesidad de ordenar la tabla.

En ciertas situaciones puede ser inconveniente o poco práctico tener que ordenar la tabla. Podemos extraer el valor buscado de acuero a artículo y fecha sin ordenar la tabla con esta fórmula matricial:

=INDICE(tblPrecios46[Precio],COINCIDIR(F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha]),tblPrecios46[Articulo]&tblPrecios46[Fecha],0))

Como puede apreciarse COINCIDIR realiza una búsqueda exacta, con el tercer parámetro puesto a 0, por lo que no hace falta ordenar la tabla.

En la función COINCIDIR, la expresión

F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha])

crea el valor de búsqueda;

la expresión

tblPrecios46[Articulo]&tblPrecios46[Fecha]

dentro de la fórmula matricial crea un vector que contiene todas las combinaciones de artículo/fecha (cono en el campo ID de la primera solución). Esto nos permite hacer una búsqueda exacta eximiéndonos de tener que ordenar la tabla.

El archivo con los ejemplos puede descargarse aquí.

La función INDICE con áreas - segundo episodio

Unos días después de haber publicado el post sobre la función INDICE con areas, entra en mi oficina el mencionado compañero de trabajo:

Compañero - Hola Jorge. Leí el post. Muy bueno...

JLD - Hola. Muy bueno...pero?

Compañero - Mirá, no si será abusar pero quisiera que en el gráfico la columna del mes analizado aparezca con otro color y también sería bueno si hubiera una línea horizontal que muestre el promedio.

JLD - ¡Ah! Vos querés ésto:

Compañero - ¡Si! ¿Cómo se hace?

JLD - Sentate que te explico

El gráfico de la nota anterior contenía una única serie; éste contiene tres:

1. la serie de las ventas sin el mes elegido (las columnas que aparecen en celeste)
2. la serie que contiene el mes elegido (aparece en  verde)
3. la serie que representa el promedio.

Como mostramos en la nota anterior, la serie de las ventas la generamos dinámicamente con un nombre definido. Ahora tenemos que crear columnas auxiliares para definir las tres series requeridas. Esto lo haccemos agregando columnas a las izquierda de la tabla de datos de manera que luego podamos ocultarlas.
Estas son las columnas:

La celda B5 contiene la fórmula =COINCIDIR(F19,E4:E15,0). El resultado, el número de orden  del mes elegido, lo usamos como parámetro en las fórmulas de las columnas A y B.

El rango A4:A15 contiene la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)<>$B$2,INDICE(F4:H4,,$F$18),ND())

El objetivo de esta fórmula es dar las ventas del mes de la tabla de ventas a condición que no sea el mes elegido (mayo, 5, en nuestro ejemplo). Em caso de tratarse del mes elegido el resultado es #N/A, lo que logramos con la función ND().

En modo similar ponemos en el rango B4:B15 la fórmula

=SI(COINCIDIR(E4,$E$4:$E$15,0)=$B$2,INDICE(F4:H4,,$F$18),ND())

Esta complementa la anterior: si no es el mes elegido el resultado es #N/A.

Los valores #N/A son ignorados en los gráficos de Excel (no aparecen).

El rango C4:C15 contiene sencillamente una referencia a la celda F22 donde calculamos el promedio.

La celda B17 contiene la fórmula matricial (se ingresa apretando Ctrl-Mayúsculas-Enter):

=MAX(SI(ESERROR(A4:A15),"",A4:A15))

El objetivo de esta fórmula es garantizar que ambos ejes Y en  el gráfico (el principal y el secundario) tengan la misma escala (el mismo valor máximo) para evitar distorsiones en la presentación de los valores. La técnica está explicada en esta nota.

Una vez que hemos definido las series creamos el gráfico asegurándonos de usar el eje Y principal para las ventas y el eje Y secundario para el mes elegido. y para el valor máximo.

Las series de ventas son gráficos de columnas; el promedio es un gráfico de línea y el valor máximo, para volverlo invisible, es un grafico de línea sin marcadores y sin línea

El último detalle a tener en cuenta es, por defecto, Excel no muestra los datos de series que se encuentras en filas o columnas ocultas. Para volverlos visibles tenemos que assgurarnos de cambiar la definición en "Seleccionar Datos-Celdas Ocultas y Vacías"

El archivo con el ejemplo se puede descargar aquí.

La función INDICE con áreas

Transcripción más o menos real de un diálogo con un compañero de trabajo:

Compañero: Hace unos días atrás leí tu post sobre la función INDICE. Desde entonces la uso todo el tiempo; no me había dado cuenta de las posibilidades que tiene.

JLD: me alegro!

Compañero: pero, ¿qué es esa segunda sintaxis con las áreas, para qué sirve?

JLD: ah!, ¿te referís a esto?:

Aquí va la explicación.

Supongamos este cuadro de ventas por mes y sucursal

Habiendo definido nombres que se refieren a los rangos de ventas de las sucursales (Sucursal_1 se refiere al rango C3:C14, por ejemplo), podemos obtener las ventas de mes de abril de las sucursal 1 con esta fórmula

=INDICE(Sucursal_1,COINCIDIR("abril",B3:B14,0))

Si queremos obtener las ventas de otra sucursal tendríamos que cambiar el rango de referencia en la fórmula.

Si usamos la segunda sintaxis de la función INDICE, podemos crear una fórmula que se refiera a las distintas sucursales dinámicamente. Consideremos este ejemplo

La fórmulas en la celda C20 (Ventas del mes) es la siguiente:

=INDICE((Sucursal_1,Sucursal_2,Sucursal_3),COINCIDIR(C18,$B$3:$B$14,0),,C17)

donde (Sucursal_1,Sucursal_2,Sucursal_3) es la referencia a las distintas áreas, en nuestro caso el rango de las ventas de cada sucursal;

COINCIDIR(C18,$B$3:$B$14,0) calcula el número de fila en el rango correspondiente al mes buscado

y C17 se refiere a la celda que contiene el número de área de donde queremos extraer el dato..

Las áreas no deben tener necesariamente el mismo tamaño o ser adyacentes.

La celda C21 contiene la fórmula

=PROMEDIO(INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17))

La particularidad aquí es que INDICE usa sólo los argumentos "ref" y "num_area". Como hemos explicado, ésto hace que INDICE considere todo el rango de la referencia; así, al combinarla con la función PROMEDIO obtenemos el promedio de las ventas de la sucursal. De esta manera podemos comparar las ventas del mes con el promedio.

Como puede apreciarse, esta técnica es muy útil cuando creamos dashboards y reportes dinámicos.

Como bono adicional, podemos usar INDICE con áreas para crear gráficos dinámicos. Siguiendo con nuestro ejemplo, podemos crear un nombre definido "grfVentas" que se refiera a la fórmula

INDICE((Sucursal_1,Sucursal_2,Sucursal_3),,,C17)

Después de crear un gráfico con una de las sucursales, reemplazamos la referencia en "Modificar serie-valores  de la serie"

por el nombre definido "grfVentas"

A partir de este momento, cada vez que cambiemos la referencia a la sucursal en la celda C17, el gráfico mostrará los datos de la sucursal elegida



El cuaderno con el ejemplo puede descargarse aquí.

Como sumar los ultimos n valores de un rango con celdas vacías

Aún no se ha secado la tinta de mi último post sobre la función INDICE y, por una de esas fantásticas casualidades, recibo esta consulta:

¿cómo puedo sumar los últimos tres valores de un rango que contiene celdas vacías?

La casualidad consiste en que la solución nos permitirá demostrar que INDICE da como resultado referencias a rangos y como ésto nos permite crear rangos dinámicos.

Supongamos estos datos

La idea es sumar los últimos tres valores de cada fila; en H2 debe ser 280, en H3 debe ser 130, etc. En el pasado he publicado un post sobre cálculos con los últimos n valores de un rango, pero esa técnica suponía que en el rango no había celdas vacías.

La solución es usar esta fórmula matricial (fórmulas que se ingresan apretando simultáneamente Ctrl-Mayúsuculas-Enter)

=SUMA(G2:INDICE(A2:G2,K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)))

Analizamos la fórmula de adentro hacia afuera:

La expresión COLUMNA(A2:G2)*(A2:G2<>"") produce un vector de valores númericos que pueden ser el número de columna o 0 si la celda del rango está vacía. Podemos mostrar el resultado de esta expresión en forma no matricial, es decir, aplicada a cada celda del rango

El vector de valores creado es el argumento de la expresión

K.ESIMO.MAYOR(COLUMNA(A2:G2)*(A2:G2<>""),3)

que da como resultado el tercer valor en orden decreciente, es decir 3 (los valores del vector son 6,5,3,2,1 y 0).

Nuestra expresión se ha reducido ahora a INDICE(A2:G2,3) y aquí es donde podemos ver que INDICE da como resultado un rango, no un valor. El resultado de esta expresión es $C$2 y por lo tanto nuestra fórmula es ahora =SUMA(G2:$C$2), es decir =SUMA($C$2:$G$2), lo que nos da el resultado deseado.


De la misma manera podemos aplicar esta fórmula a las columnas en lugar de las filas, utilizando la función FILA() en lugar de la COLUMNA() y corrigiendo los rangos consecuentemente

En este video explico el funcionamiento de la fórmula usando la herramienta Auditoría de fórmula

La función INDICE, todo (o casi todo) lo que hay que saber

En la nota Traducción automática en hoja de Excel propuse dar un premio (el Planificador de Tareas JLD) a los primeros tres lectores que respondieron acertádamente a la pregunta qué tiene de particular la forma la forma en que se usa la función INDICE en el ejemplo de la nota.
Si bien hubo tres ganadores, ninguna respuesta fue totalmente exacta, lo que me impulsó a escribir esta nota.

La función INDICE es una de las más importantes y versátiles funciones del arsenal de Excel (y hay quien la corona como la más importante endilgándole superlatívamente el adjetivo "imponente").

La función INDICE permite encontrar el valor de un elemento dentro de una rango. Este rango puede ser un "vector" (un rango unidimensional de una fila como A2:M2 o de una columna como  A2:A10), o una matriz (un rango "rectangular", por ejemplo A2:M10). Para encontrar el elemento buscado debemos incluir uno o dos índices dependiendo se si trata de un vector o una matriz.

En este ejemplo

INDICE crea una referencia a la celda A2, cuyo valor es 40.  En este otro ejemplo

usamos la misma sintaxis a pesar de que el rango es "horizontal". En este caso INDICE crea una referencia a la celda B1.

Podemos concluir que:

• que cuando usamos vectores (rangos unidimensionales) INDICE interpreta el segundo argumento como fila o columna dependiendo de la orientación de vector

• cuando se refiere a un rango, la función INDICE crea una referencia a una celda (o celdas) en el rango.

Cuando usamos INDICE para referirnos a rangos bi-dimensionales, usamos los argumentos de fila y columna para referirnos al elemento buscado en la matriz

En este ejemplo INDICE crea una referencia a la celda B3.

Hasta aquí posiblemente no haya aportado nada dramático a los conocimientos de mis lectores. Ahora los invito a ver esta situación:

Cuando cuando falta el parámetro de fila, o es 0,  INDICE se refiere a toda la columna (en nuestro caso la columna B ya que el parámetro de columna es 2).  Pueden comprobarlo con

34+44+50+26+79+51+57 = 341

De la misma manera, si falta el parámetro columna, INDICE se refiere a toda la fila

47+50+18 = 115

Podemos aprovechar esta propiedad de INDICE para crear rangos dinámicos. La ventaja de usar INDICE en lugar de DESREF o INDIRECTO es que estas dos últimas son volátiles mientras que INDICE no lo es.
Un rango dinámico con INDICE se ve así

=A1:INDICE(A:A,CONTARA(A:A))

donde A1 es la primer celda del rango. Esta fórmula supone que no hay celdas vacías entre A1 y la última celda con valor de la columna A. Para usar esta fórmula como rango dinámica creamos un nombre que se refiera a la fórmula, por ejemplo el nombre "rango_dinamico"

Ahora podemos ver que a medida que agregamos valores en la columna a, el resultado en la celda C1 se va actualizando

Hojas Excel con traducción automática

Recientemente tuve que crear una hoja que incluyera la posibilidad de presentar el contenido en distintos idiomas.
La idea era usar una única hoja, de hecho un dashboard, pero que el usuario pudiera elegir en qué idioma presentar los textos de las celdas y demás objetos en la hoja/dashboard.

Este ejemplo muestra el funcionamiento

Como puede apreciarse, cuando el usuario elige un idioma apretando alguno de los botones de opción, los titulas de los cuadros de datos y del gráfico cambian.

La traducción se puede hacer fácilmente usando la función INDICE, pero la técnica varía si se trata de una celda o de un objeto. Esto es lo que mostraré en esta nota.

Para hacerlo empezamos por crear un glosario, una tabla con los textos de las palabras que queremos traducir en los distintos idiomas. En nuestro caso creamos un glosario con Castellano, Inglés e Italiano

En la hoja del dashboard agregamos los botones de opción para la elección de los idiomas. En las definiciones del botón lo asociamos a una celda. Es una buena práctica usar las columnas de la izquierda como columnas auxiliares, de manera que podamos ocultarlas. En nuestro caso vinculamos los botones a la celda A3

Otra buena práctica en Excel es definir un nombre que se refiera a la celda; en nuestro caso definimos el nombre "cel_Idioma" que se refiere a la celda A3

También es recomendable enmarcar los botones de opción en un cuadro de grupos.

Cuando el usuario elige el Castellano, el valor de "cel_Idioma" es 1; si elige English, será 2 y si elige Italiano será 3.

En la celda F3 de la hoja dashboard ponemos esta fórmula:

=INDICE(glosario!B6:D6,cel_Idioma)

Esta fórmula usa el valor de "cel_Idioma" para extraer el texto en el idioma correspondiente en la tabla Glosario.

Pregunta con premio (copia gratuita totalmente funcional del Planificador de Proyectos) a las primeras tres respuestas correctas:

¿Que tiene de particular la fórmula en la celda F3 del ejemplo?

 La misma técnica empleamos para todas las celda cuyo contenido queremos traducir.

Si nos fijamos en el título de la lista desplegable del gráfico, veremos que se trata de un cuadro de texto "montado" sobre una celda.

No podemos ligar un cuadro de texto, o cualquier otra forma gráfica, a una fórmula pero si podemos hacerlo a una referencia. El rodeo para lograr el efecto de traducción consiste en poner la fórmula con la función INDICE que extrae la traducción del Glosario en una celda de la columna A (de manera que luego podamos ocultarla).

Creamos un cuadro de texto (o cualquier otra forma) y la superponemos en la celda adecuada (K2 en nuestro ejemplo). Luego seleccionamos el cuadro de texto y en la barra de las fórmulas ponemos =A9 (o ponemos = y luego hacemos un clic en la celda A9)

La celda A9 contiene la fórmula

=INDICE(glosario!B14:D14,cel_Idioma)

Otra situación es si usamos tablas dinámmicas. A pesar que las tablas ocupan celdas, no podemos introducir fórmulas en los encabezamientos de las áreas (si podemos cambiar el texto).
Para superar este problema usamos nuevamente cuadros de texto ligados a celdas ocultas que contienen la fórmula con la función INDICE. Para mimetizar los cuadros de texto con el resto de la tabla le damos un relleno sólido con el mismo color que la tabla

En este video podemos ver el dashboard en funcionamiento

Evitar constantes en BUSCARV.

Si hay una línea en Excel que marca el paso de principiante a usuario intermedio, es el uso de BUSCARV. Esta función, como tantas otras funciones de búsqueda en Excel, extraer valores de una tabla de datos basándonos en un criterio de búsqueda.

Sin lugar a dudas, es una de las funciones más usadas en Excel. Sin embargo y desde el punto de vista de las buenas prácticas, tiene un problema al que pocos usuarios prestan atención: el tercer argumento de la función, el indicador_columna (la columna en la tabla de la cual se quiere extraer el dato). Veamos este ejemplo sencillo (que me vino a colación de una nota sobre normas de diseño en Excel que estoy escribiendo)

La fórmula en la celda F2 usa BUSCARV para extraer el número de teléfono de acuerdo al nombre que ponemos en E2.

Ahora, supongamos que insertamos en la tabla de datos una columna (Zona) entre los campos "Nombre" y "Teléfono"

Si bien los rangos de la función se han adaptado al cambio, el tercer argumento de la función es una constante. Y por lo tanto, ahora el resultado es la zona y no el teléfono.

En ciertos casos el error no es fácilmente identificable y puede ser crítico.

Las buenas prácticas en Excel nos indican que debemos evitar usar constantes en las fórmulas. En este caso podemos superar el problema usando la función COINCIDIR para crear una referencia dinámica.

Esto lo podemos hacer usando COINCIDIR junto con BUSCARV o, mejor en mi opinión, con INDICE.

Con BUSCARV usamos

=BUSCARV(F2;$A$2:$C$5;COINCIDIR(G$1;$A$1:$C$1;0);0)

con INDICE la fórmula es más corta

=INDICE($C$2:$C$5;COINCIDIR(F2;$A$2:$A$5;0))

Como Excel adapta los rangos dinámicamente al insertar, eliminar o mover filas y columnas, estas fórmulas mantienen siempre la referencia exacta y evitan errores inadvertidos.

Nótese que con la combinación de BUSCARV y COINCIDIR en nuestro ejemplo, podemos cambiar el data extraído con solo cambiar el texto en la celda G1.

BUSCARV (VLOOKUP) con texto dentro de texto

Uno de mis clientes implementó una aplicación para la administración de la planta de producción. En la planta hay una cierta cantidad de máquinas, que fueron asignadas a "centros". Cada centro puede tener una o más máquinas. Para crear la base de datos el ingeniero de producción preparó una tabla con las máquinas asignadas a cada centro

El departamento de computación pidió recibir una tabla con una línea para cada máquina

por lo que hubo que transformar la tabla de nuestro buen ingeniero en la tabla requerida por el departamento de computación (en la realidad hay en la planta 75 máquinas asignadas a 15 centros).

No podemos usar BUSCARV o INDICE con COINCIDIR ya que en una misma celda de la primer tabla hay más de una máquina. De hecho tenemos que extraer el valor correspondiente a un "sub-texto" dentro del texto.

Podemos hacerlo usando funciones nativas de Excel o con una UDF (función definida por el usuario). Con funciones nativas de Excel usamos esta fórmula matricial

 =INDICE(Hoja1!$A$4:$A$9;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(E4;Hoja1!$B$4:$B$9));0))

Esta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

La función HALLAR busca el número de máquina (E4) en el rango de máquinas de la tabla de ingeniero (B4:B9). De hecho, HALLAR busca el "texto" 251 dentro del texto de las celdas del rango B4:B9. Si lo halla, el resultado es un número (la posición donde comienza el texto buscado dentro del texto de la celda de búsqueda).
La expresión ESNUMERO(HALLAR… genera un vector de resultados VERDADERO o FALSO. COINCIDIR calcula la posición del primer valor VERDADERO dentro de ese vector. Este resultado, a su vez, es utilizado en la función INDICE para calcular el centro correspondiente.

También podemos crear esta UDF, usando la función InStr de Vba. La función calcula la posición de la máquina en la lista, es decir, reemplaza la expresión

COINCIDIR(VERDADERO;ESNUMERO(HALLAR…

en la fórmula anterior. El resultado de la función lo usamos como argumento en la función INDICE, sin necesidad de usarla en forma matricial

 El código de la función match_sub_string es el siguiente:

 Function match_sub_string(SearchValue, rngSearchRange As Range)

    Dim lRangeRows As Long 'numero de filas en el rango
    Dim iR As Long
  
    lRangeRows = rngSearchRange.Cells.Count
  
    For iR = 1 To lRangeRows
        If InStr(rngSearchRange.Item(iR), SearchValue) > 0 Then
            match_sub_string = iR
            Exit Function
        Else
            'do nothing
        End If
    Next iR
  
       
End Function