Buscar la fila / columna de un valor en una matriz.
En esta semana estuve trabajando con un líder de logística armando un cotizador único para todas sus operaciones, mismo que buscaba un código postal en una tabla y debía definir la tarifa según los tabuladores de los proveedores de envió.
La dificultad con la que nos topamos es que casi todos manejan un diseño similar de tablas, teniendo por un lado sus tabuladores de zona y por otro los códigos postales correspondientes a cada zona, algo así como lo de la imagen.
El punto es encontrar la tarifa solo teniendo el código postal, por lo que las búsquedas directas no funcionan del todo bien y no quisimos entrar a temas de macros. ¿Cómo lo solucionamos? Con el siempre infalible SUMAPRODUCTO.
La lógica.
SUMAPRODUCTO es por si misma una formula matricial, por lo que es capaz de encontrar valores en tablas de dos dimensiones sin la necesidad de apretar CTRL+MAYUS+ENTER para que funcione de esa manera. Para entender un poco su funcionamiento supongamos los siguientes datos:
A B
1 4
2 5
3 6
Si aplicamos SUMAPRODUCTO(A:A,B:B) obtendremos como resultado 1*4+2*5+3*6 = 32, por lo que la función sirve para obtener la suma de la multiplicación de los valores
Con datos de texto
Al igual que SUMAR.SI y otras de la misa familia, SUMAPRODUCTO puede realizar la operación de condicionantes añadiendo una matriz de VERDADERO/FALSO y convirtiéndola a valores con el operador –
A B
Norte 6
Sur 4
Este 5
Oeste 7
Lo que necesitamos es conocer solo el valor de Norte, por lo que la formula seria SUMAPRODUCTO((–(A:A=”Norte”)),B:B) convirtiendo la primera parte (A:A=”Norte”) en
A B
VERDADERO 6
FALSO 4
FALSO 5
FALSO 7
Y usando la dualidad de valor binario con los –:
A B
1 6
0 4
0 5
0 7
Y sabiendo que el resultado será una multiplicación de valores tenemos 1*6+0*4+0*5+0*7 = 6
Adicional, podríamos aplicar otras funciones adicionales a los valores de la columna A. Por ejemplo, si quisiera saber cual es el total de los valores que terminan con “e” podríamos hacer lo siguiente:
=SUMAPRODUCTO((–(DERECHA(A:A,1)=”e”)),B:B)
Con lo cual evalariamos la ultima letra de los valores en la columna A y, siguiendo la lógica anterior obtendríamos 1*6+0*4+1*5+1*7 = 18
A la búsqueda de valores.
Ahora que ya sabemos como funciona, es hora de aplicar SUMAPRODUCTO a los códigos postales. Como ya vimos, tendremos que hacer la aplicación para saber cual valor corresponde a VERDADERO al momento de usar la función, pero eso no nos dirá nada sin un complemento. En este caso nos interesa saber la columna que corresponde a esos códigos postales, por lo cual usaremos la función COLUMNA que nos dirá a qué numero de columna corresponde el VERDADERO.
Suponiendo que el rango de los códigos postales está en la hoja “EST semanal” y en el rango L9:R4113, la función queda así:
=SUMAPRODUCTO((‘EST semanal’!$L$9:$R$4113=codigo_postal)*(COLUMNA(‘EST semanal’!$L$9:$R$4113)-11))
Donde:
La primera parte ‘EST semanal’!$L$9:$R$4113=codigo_postal va a buscar en todos los códigos postales de la tabla y solo dará VERDADERO cuando el código sea igual al código postal buscado, todo lo demás será FALSO.
La segunda parte (COLUMNA(‘EST semanal’!$L$9:$R$4113)-11) será una matriz con los números de columna de cada valor en el rango, y le restamos 11 para reducir ese numero once columnas y pegarlo a nuestra tabla de tarifas.
A buscar la tarifa.
Ahora que tenemos el número de columna de la zona, bastara con anidar nuestra formula a un INDICE que nos diga cual es la tarifa por cobrar, como tenemos un valor adicional que es el peso volumétrico lo usaremos para crear las coordenadas de la tabla.
Suponiendo que nuestros valores tarifarios están en C9:I78, la formula queda así:
=INDICE(‘EST semanal’!$C$9:$I$78,REDONDEAR.MAS(peso_vol,0),SUMAPRODUCTO((‘EST semanal’!$L$9:$R$4113=codigo_postal)*(COLUMNA(‘EST semanal’!$L$9:$R$4113)-11)))
En el ejemplo, el valor coincide con el peso de 9kg por redondeo y a la zona 4, por lo que la función resuelve el asunto, algo enredado, pero super efectivo. Inténtalo por partes la primera vez, no te vaya a pasar lo que me paso a mi jeje (descripción grafica abajo).